3

My app uses a SQLite database with FTS3 to do product search. I am trying to encrypt the database using SQLCipher but it causes the database size to balloon (7mb --> ~20mb).

It doesn't look like there is a great way to compress an encrypted FTS SQLite database. Does anyone have a suggestion on this? I have to shrink it in order to keep the app under the 20mb 3g download limit.

Thanks!

Rangers4me
  • 177
  • 2
  • 9

1 Answers1

1

There is no reason that a database using SQLCipher would be that large in comparison to one without SQLCipher. Each page of the SQLCipher database only uses 48 bytes of the page for IV and HMAC. Thus with a 1024 byte page size, if you had a 7mb unencrypted database, the SQLCipher encrypted version should be only 7.34 mb.

A far more likely explanation is that your database has grown as a result of deletes and inserts. If auto vacuum is not enabled on the database, deleted rows can continue to take up space. Try running VACUUM on the database to reclaim space.

If vacuuming the database doesn't take care of it, here are a few follow-up questions;

  1. Are you sure that the FTS3 indexes are not simply responsible for the the file size?
  2. Have you tried creating the same exact schema and table structure with FTS3 in a non-encrypted database to compare file sizes to make sure that this is actually a problem with SQLCipher? Is the non-encrypted database with FTS3 7 MB?
  3. If the answers to 1 and 2 are both yes, can you please provide exact steps for how you are converting from the unencrypted to the encrypted database?

To answer your general question, a SQLCipher database is probably not going to compress well. If the file size growth is actually a result of FTS3 and not SQLCipher, then you could consider shipping the data on the app and then building the FTS3 virtual table on the device.

Stephen Lombardo
  • 1,503
  • 8
  • 7
  • Do you think its because I am using FTS in combination with SQLCipher? – Rangers4me Mar 06 '12 at 00:28
  • Posting from the mailing list followup: – Stephen Lombardo Mar 06 '12 at 21:57
  • Hey Stephen -- Thanks for all the advice. A few breakdowns on the app sizes: 1. Unencrypted / Uncompressed with FTS: 21mb 2. Unencrypted / Compressed with FTS: 7mb 3. Encrypted (SQLCipher) / Compressed with FTS: 22mb I don't think the problem is with SQLCipher -- I just don't know of a way to both encrypt and compress to a size that is usable within the app. – Rangers4me Mar 07 '12 at 22:19
  • Ah, now I understand, the 7mb was the compressed size of the database. Thanks for clarifying. I don' think there is a good solution at this time for compression. What do you think about shipping the content and then building the FTS indexes on device? – Stephen Lombardo Mar 08 '12 at 21:17