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
