SQLite : optimal method to work with

sorex said:

Hello,

I'm going to take my first steps with SQLite on Android.

Erel's example shows how to create all tables by code.

But if you will use databases with several tables, views etc wouldn't it be better to design the database schema on a pc and add it to the /files folder
and copy it at runtime to DirDefaultExternal to make it writeable?

the sqlite file will be a few hundred kilobytes but since most of it is zero byte runlenghts the overhead to the APK file will be just a few Kb while all that code to generate the same thing on the Android side will probably make the APK larger than just including the sql file.

How do you guys/girls work this out?

Click to expand...

I completely agree with you. I don't create my DB by code but by replicating a template stored in the assets folder. The DB template is created with a SQL editor on my PC.

Just fyi:
there's a neat program called DB Designer Fork that's open source: DB Designer Fork | Free Development software downloads at SourceForge.net
You can design your database in this program visually, and then tell it to spit out a ddl in sqlite style, then import that .sql into sql browser (another open source database browser for the pc) and create your sql file.
It has a reverse engineer function so you can use it to connect to oracle/mssql/mysql databases and load in the structures that exist, then export out a sqlite ddl. I haven't found any free tools that can do that.
Though because sqlcipher's (encryption of a sqlite database) already built dlls are at a cost, but the android libraries are free, i usually take the ddl .sql file that creates the android database and run it on android to create a sqlcipher compatible database. Works pretty well.
Though, it hasn't been updated since 2009. So it's not perfect. The sqlite version it supports directly is v2, which since i only use v3 doesn't help. It's supposed to have the capability of connecting directly to a sqlite database and create the tables/pump data into them, but because it hasn't been updated to v3 it's sort of useless for that at the moment. Then again it is open source and the source code is available.

as Hypergreatthing stated ..
This is the link for SQLite DB Browser ,which I found very useful ,creating db's and also checking data while testing / debugging. (still learning myself)

SQLite Database Browser is a freeware, public domain, open source visual tool used to create, design and edit database files compatible with SQLite.

Click to expand...

SQLite DB Browser

I'm a bit confused why you need the first utility mentioned above (DB Designer Fork) but I'm still a noob with SQlite so maybe I'm missing something

Cheers mj

mangojack said:

I'm a bit confused why you need the first utility mentioned above (DB Designer Fork) but I'm still a noob with SQlite so maybe I'm missing something

Click to expand...

If you have a database with data, but not in sqlite, it's great to port over into a sqlite database. Or if you have tables with relations/etc, or want to design a database, then sql browser kind of fails in that aspect. Visually planning out data flows, optimizing/normalizing, etc etc is much easier to do when you play around with a erd and can make changes visually
If it's for a simple table or two don't worry about it. SQL Browser is the way to go.

hyper .. thanks for the clarification, certainly here we are only utilizing a few large but fairly straightforward tables.

Cheers mj

mangojack said:

as Hypergreatthing stated ..
This is the link for SQLite DB Browser ,which I found very useful ,creating db's and also checking data while testing / debugging. (still learning myself)

SQLite DB Browser

I'm a bit confused why you need the first utility mentioned above (DB Designer Fork) but I'm still a noob with SQlite so maybe I'm missing something

Cheers mj

Click to expand...

My own preference goes to SQLite Expert Personal.

I just installed the FireFox SQLite addon which seems to do all I need (tables, views and manual queries) the only odd part is that it doesn't ask for an nvarsize.

My own preference goes to SQLite Expert Personal.

Click to expand...

Informatix .. Thanks for that , very nice.

Cheers mj