I’m discussing with my company if I’m allowed to open source our "option
D“. It was written for a certain use-case (to provide updates of large dbs
on mobile phones for our own apps), therefore it is not entirely generic.

It works like this:
- Every table in the db needs a column „md5checksum“, and this column
needs an index. This is because we cannot rely on updating using row IDs
as SQLiteDiff does.
- Run the tool once over the db, and it generates all checksums of all
rows in all tables.
- Given any two input databases, it creates a diff file. The limitation is
that the db scheme must be the same (though this could be combined with
SQLite’s own diff tool to update the scheme in future). Therefore the tool
also generates a checksum for the db scheme. In our apps we required a
full db download instead of a diff.
- A small diff lib (written in C) then updates the original sqlite DB by
just providing input db path, and diff db path. It’s tuned to do this very
fast, for example to use „match“ instead of = for FTS tables and file
system sync is turned off.

As I said, this may not be entirely generic. For example, it should be
possible to change this tool so that it doesn’t need the md5checksum
column and instead does the checksum calculation on the fly - at a
performance penalty of course. Though performance was never an issue for
us, it creates all checksums for a 100MB db containing content for a venue
app within a few seconds.

Regards
Ben


Am 24.05.16, 19:20 schrieb "sqlite-users-boun...@mailinglists.sqlite.org
on behalf of Simon Slavin" unter
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of
slav...@bigfraud.org>:

>
>On 24 May 2016, at 6:04pm, Michael Hari <michael.h...@slalom.com> wrote:
>
>> I’ve come to the conclusion that I would need to “split the file into
>>two databases” as well. The challenge here is that the larger db will
>>have to have a limit of 2 GB and will need to make a new db when it hits
>>that limit. It would have to split it into multiple Dbs as the data
>>grows (so db1.sqlite, db2.sqlite, etc.). Would you happen to know any
>>strategies to accomplish this in sqlite? Either through SQL or scripting?
>
>Create a versioning system for your database which is not the same as the
>version of your application.  Continue to use Sparkle for your
>application but not for the database file.
>
>Upgrading your database from one version to another can be done in three
>ways:
>
>A) Distribute an additional .sqlite file.  To update the local database
>have the application read the data from this file and add it to the local
>copy of the database.
>
>B) Distribute a .zipped text file containing the SQL commands needed to
>update the local copy of the database.  To update the local database have
>the application read the commands from this file and execute them.
>
>C) Distribute a .zip containing a .csv file for each table.  To update
>the local database have the application read the text from this file and
>convert it to INSERT commands.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to