Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

[Simon Slavin]

On 25 May 2016, at 7:23pm, Michael Hari  wrote:

> 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 ?

[Michael Hari]

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 ?

[Stadin, Benjamin]

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 ?

[Stadin, Benjamin]

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 ?

[Simon Slavin]

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

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

[Michael Hari]

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 ?

[Simon Slavin]

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 ?

[Piyush Shah]

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.