10

I have a sqlite database in my offline app that has reached 32mb. Its created from asset files on launch which are 6mb in size.

Is there any built in compression function that permenantly causes the database to become smaller, I dont mind the performance hit while reading. It would then use less of the users phone memory.

Any ideas?

The DB has two tables which are mostly long text fields. The size is the end result of 7000+ inserts upon first application launch.

sprocket12
  • 5,368
  • 18
  • 64
  • 133
  • 2
    SQLite's FTS4 extension provides a compression option: http://www.sqlite.org/fts3.html#section_6_1. However, that extension is apparently only present in Android since API level 11 (see here: http://stackoverflow.com/questions/6339022/sqlite3-fts4-match-and-android). Alternatively you could do compression / decompression in Java. However, that way you won't be able to use SQLite for searching your texts. – tiguchi Nov 14 '13 at 15:34
  • @NobuGames might be worth reading up on as I am targeting API 11+. Thank you. – sprocket12 Nov 14 '13 at 15:38
  • 2
    @NobuGames just an update, its not possible to use that option to compress the FTS tables, as android does not support custom functions. – sprocket12 Dec 16 '13 at 12:55

2 Answers2

3

You could try using VACUUM on your database. See the SQLite doc here : http://www.sqlite.org/lang_vacuum.html

SolarBear
  • 4,534
  • 4
  • 37
  • 53
  • 1
    Yes I saw a post about that, but for me it doesnt apply as that is my size after 7000+ inserts. The db will not be really written to afterwards apart from things like bookmarks. So its unlikely it will be fragmented enough to be affected by VACUUM – sprocket12 Nov 14 '13 at 15:24
2

CEVFS: Compression & Encryption VFS for SQLite 3

CEVFS is a SQLite Virtual File System which uses its own pager and is inserted between the pager used by the b-tree (herein referred to as the upper pager) and the OS interface. This allows it to intercept the read/write operations to the database and seamlessly compress/decompress and encrypt/decrypt the data.

Dennis V
  • 574
  • 7
  • 19