How To Shrink Sqlite Database?

Once done storing all of my data into sqlite, it reach 14 MB already. That's why I'm afraid that some people cannot download my application from connection slow area. Is there any

Solution 1:

Have you tried VACUUM?

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

• Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.
• Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further...

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space...

Solution 2:

a few hints:

• SQLite is somewhat lazy to reclaim unused space; use the VACUUM command or auto vacuum mode.

• the datbase format trades space efficiency for speedy access. if you just dump the data contents, either as SQL dump or simply a CVS file for each database table, you'll likely get smaller files.

• either using databse files or plain data, try compressing them.

Solution 3:

Putting @Tono Nam's comment in an answer. An example with full syntax is always very helpful.

Run the following on command line to use the vacuum command:

sqlite3 /path/to/your/db/foo.db 'VACUUM;'

Solution 4:

Try to zip it and use zipinput stream while unpacking your database from assets. Anyway, android will zip your 14mb database when you create your apk anyway (which will be unzipped during install), so I guess your apk will be around 5-6mb in size.

Solution 5:

14mb is very large for an Android app still, it's better if you can arrange for it to be downloaded from a server and integrated into the app.

You might instead bundle the app with the database file compressed (i.e. a .zip file) and uncompress it when the app is first run (remembering to delete the .zip file). Best to do all of this on an SD card.