On 2018/07/10 8:27 PM, Randall Smith wrote:
> One follow-up: Do you know if the dump output is "deterministic" over
> time? That is, if I diff two dumps taken at different times, will the
> unchanged material be in the same order and so on? Or is the ordering
> effectively random?
> My underlying question is "can text-comparing two DB dumps be used to
> determine what has changed?"
On 2018/07/11 Simon wrote:
I am not sure if it is 100% deterministic - it probably is, however, I would
like to point out that while parsing a dump (supposing it IS deterministic) is
possible, writing some code to check congruence between two DBs at the business
end of the SQLite API is significantly better, much easier and always 100%
deterministic.
Not only that, but the sqldiff command-line utility (download page) does it
already (though you may require a more specific result, but at a minimum its a
good start).
Is there perhaps a specific difficulty which makes you think that parsing the
dump would provide a better/easier insight into which data changed?
Fundamental problems with SQLite or other binary representations are (a) the
information represented is opaque unless one spends time and money creating
bespoke tools to allow viewing and technical reviews of the content, and (b)
there is no simple way to allow concurrent development of info by several
people and to reconcile independent changes into a coherent whole ("merging").
These are both mission critical for a team effort of any size (even size=2!).
The software industry has historically avoided these problems by storing
everything in the form of text files, and has developed elaborate tools and
procedures for viewing, reviewing, storing, and merging information in this
form and as a result large teams can collaborate on a rapidly evolving body of
digital information easily and well.
Binary file formats like SQLite, while having many compelling advantages, have
a hard time penetrating into areas where multiple people need to collaborate on
an evolving body of information because of the limitations described above.
IMO this is an urgent problem and one that has not been solved very well for
SQLite. I don't have the wherewithal to solve it generally, but I am trying to
see if there are ways to bridge the gap between SQLite DBs and existing
team-capable workflows built around text files.
My wishlist is:
(o) Allow humans to view the contents of a DB without custom tools.
(o) Have a way to see what has changed between V1 and V2 of a database, e.g.,
for a "change review."
(o) Have a way to merge two independent sets of database changes into a single
result in an understandable way.
(o) Have a way to make changes (update, insert, delete) to the DB data in a
pinch without specialized tools.
I'm thinking the dump approach you described previously has promise provided
certain criteria are met. Interestingly, the text representation produced by
dump is about the same size as the "normal" binary form, and it will compress
to about 1/8 the size of the binary form. So it's not a bad archival format.
Randall.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
