Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
On 25 May 2016, at 7:23pm, Michael Hariwrote: > What about if we shard the tables in the database across multiple databases? > Is that possible to do? Does SQLite support the concept of sharding? No. Each table has to be entirely contained in a specific database file. You can do things in your software, or create a temporary VIEW which consult all the tables at once (using UNION) but you have to have your software set it up after opening all the database files. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
What about if we shard the tables in the database across multiple databases? Is that possible to do? Does SQLite support the concept of sharding? Thanks, Michael On 5/24/16, 10:20 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of Simon Slavin"wrote: > >On 24 May 2016, at 6:04pm, Michael Hari 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 >https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwIGaQ=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=ahSUBK1T7j-1qWdH9sKUO09_FAWT0Eeb1s32vYabAg0=9lhqAP5mAfvcMjMuMHs6L0HxVgtTW0uXHq79XI8KeYg= > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
This should read: In our apps we required a full db download, in case the db schemes did not match. Am 25.05.16, 19:49 schrieb "sqlite-users-boun...@mailinglists.sqlite.org on behalf of Stadin, Benjamin" unter: >In our apps we required a >full db download instead of a diff. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
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: > >On 24 May 2016, at 6:04pm, Michael Hari 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
Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
On 24 May 2016, at 6:04pm, Michael Hariwrote: > 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
Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
Hi Simon, We don’t want to zip up the entire db unfortunately. It would be around 250 MB zipped versus having Sparkle update with a 38 MB patch. (Bandwidth costs are important for us) 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? Thanks, Michael On 5/23/16, 1:46 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of Simon Slavin"wrote: > >On 23 May 2016, at 9:08pm, Piyush Shah wrote: > >> We know about sqldiff >> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_sqldiff.html=CwICAg=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=H8MM4zcxx8e1CF2D98PKVDGp8SvCTyw-dv7XoQ5KPw4=8qlz1JEuXvYSnt4FvMj1V_UVQ2pQqaCG78j0tbSf3pU= >>and would use it to generate diffs and patch them but we were hoping >> there was a way to do do a binary diff of the database because otherwise we >> will have to figure out a way for the client to run the sqldiff utility to >> patch the database. > >There's no easy way to integrate Sparkle and sqldiff so that they work >together like that. The amount of work it would take would be equivalent to >writing your own tool which updated the database across the internet. > >If the database file is small, just let Sparkle update it with the other >components of the application. If the database file is big, it may be worth >doing work on it. > >Does the database file compress well as a ZIP file ? Then distribute it as a >.zip. On startup, detect if the decompressed version is not present, or has a >datestamp before the .zip version. > >Do you have roughly equal parts of the file taken up with tables which never >change and tables which change with each update ? Then split the file up into >two database files and use ATTACH to access them both via one connection. > >Simon. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwICAg=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=H8MM4zcxx8e1CF2D98PKVDGp8SvCTyw-dv7XoQ5KPw4=deDylKqCP4ECaSOv3XoqH0xfznS3YHz3ZlsPDbzxbXE= > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
On 23 May 2016, at 9:08pm, Piyush Shah wrote: > We know about sqldiff https://www.sqlite.org/sqldiff.html and would use it > to generate diffs and patch them but we were hoping there was a way to do do > a binary diff of the database because otherwise we will have to figure out a > way for the client to run the sqldiff utility to patch the database. There's no easy way to integrate Sparkle and sqldiff so that they work together like that. The amount of work it would take would be equivalent to writing your own tool which updated the database across the internet. If the database file is small, just let Sparkle update it with the other components of the application. If the database file is big, it may be worth doing work on it. Does the database file compress well as a ZIP file ? Then distribute it as a .zip. On startup, detect if the decompressed version is not present, or has a datestamp before the .zip version. Do you have roughly equal parts of the file taken up with tables which never change and tables which change with each update ? Then split the file up into two database files and use ATTACH to access them both via one connection. Simon.
[sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
Hi, We are using the Sparkle Project https://sparkle-project.org/ to manage a Mac OS X application updates that has an embedded sqlite database. We were hoping that the Sparkle project would be able to detect differences in the SQLite database file and include that in the Delta it generates with bsdiff. We are observing that it doesn?t see the binary difference in SQLite and sends the entire database with the update. We know about sqldiff https://www.sqlite.org/sqldiff.html and would use it to generate diffs and patch them but we were hoping there was a way to do do a binary diff of the database because otherwise we will have to figure out a way for the client to run the sqldiff utility to patch the database. Appreciate any suggestions.