Android Question Pre-populated search table or DB

[leongcc]

My app uses some keys to search a large file (about 100MB) to retrieve some results.
This file content (keys/values) are known, read-only and to be distribution with the apk.
I need some advice:

1) I plan to use DButils but since it is read-only should I consider RandomAccessFile ?
Would it be faster ?

2) If I use RandomAccessFile, how do I populate during development and then deploy it ?

3) If I use DButils, can I load it using PC-based mySQL then export the SQL file to Android ?

Thanks.

 

[udg]

Hi leongcc,

if I understand it correctly, tour DB is made up of records which consists of key/value pairs.

About #1 I don't know whether RAF could be a better option so I let others reply on it.
Point #2 depends on what you have available now: if data are already stored in some form (say an Excell sheet, a DB table, a txt file..) then just write a conversion routine so to output the file format chosen for your app (RAF, MySql or SQLite).
Point #3, DBUtils can be used on many different DB types, being MySQL one of those. You could access a MySQL DB and convert all data to SQLite form than publish the resulting DB along with your app. I would use B4J for this one-time conversion.

udg

 

[leongcc]

Thanks for responding.
My data (key+value) is in CSV formatted file.
I need to convert it to an indexed access method (randomfile or DBMS) before packaging it with the codes binary.
The app cannot do the conversion (or indexing) at runtime because the data is huge.
What procedural method should the app use to access a ready-populated DB?

 

[udg]

Hi,

I wold use a one-time B4J conversion from CSV to SQLite and then have my B4A app bundled with the resulting SQLite DB.
Maybe there are tools that let you import a CSV in a SQLite DB;that way you will skip the one-time conversion written with B4J. Presumably an Internet search will serve a ready-made solution.

 

[inakigarm]

Great tool for editing/import-export sqlite DB (including CSV import-export): http://sqlitebrowser.org/

 

[eurojam]

You also can consider a key-value store - which is a sqlite db - and do the conversion with a small b4j program, like proposed by @udg

 

[leongcc]

I appreciate all for taking time to assist.
I just found out Android (before version 6) limits SQLite database to 1MB. So it's a no-go for me.

 

[mangojack]

I appreciate all for taking time to assist.
I just found out Android (before version 6) limits SQLite database to 1MB. So it's a no-go for me.

Possibly you might be confusing the DB size limit with actual query / cursor limit ??
http://stackoverflow.com/questions/...the-database-size-is-greater-than-2-gigabytes

 

[leongcc]

Possibly you might be confusing the DB size limit with actual query / cursor limit ??
http://stackoverflow.com/questions/...the-database-size-is-greater-than-2-gigabytes

You are right, I am confused.

 

[udg]

May I suggest a simple test?

1. Download SqliteBrowser as suggested by inakigarm in post #5 above (there's evena a Portable version)
2. Use SqLite Browser to import your CSV data in a sqlite DB
3. Add the resulting DB in an very basic B4A app (use the Files tab)
4. Open the DB (and eventualy run a SELECT statement ).

This way you'll know for sure if you hit any size limit and whether SQLite is the solution for you.

 

[leongcc]

SqliteBrowser is a good recommendation, much better than myphpadmin, able to import 2GB without trouble.

I successfully converted csv file (16MB, 350k rows) to SQLlite database, packaged the database with everything else into apk and all rows of DB are accessible.

Just one more thing, the DB is placed in the file-directory (DirAsset) for compilation. At run-time, it is copied to DirInternal. Since my DB is read-only. Is there a trick I can use to avoid duplicating the DB ?

Thanks.

 

[DonManfred]

At run-time, it is copied to DirInternal. Since my DB is read-only. Is there a trick I can use to avoid duplicating the DB ?

Check if the dbfile already exist. Only copy if not.

 

[leongcc]

DB exists in DirAssert but SQL reports 'cannot open exception'.

 

[LucaMs]

Just one more thing, the DB is placed in the file-directory (DirAsset) for compilation. At run-time, it is copied to DirInternal. Since my DB is read-only. Is there a trick I can use to avoid duplicating the DB ?

This is a good question (note that any DB in dirAssets is not accessible for writing, like any other file, not only your DB).

I fear you cannot avoid duplicating it; to avoid this, the only way is to download the DB from some server.