SQLite (or any other SQL db) "comparison" tools

< Next Topic | Back to topic list | Previous Topic >

Posted by 22111
Mar 20, 2023 at 03:15 PM

Well, for most of your use cases, just forget those tools, since they all (? - well, then, I have analyzed dozens of them…) rely on your records’ “ID”, or then, alternatively and within / for SQLITE DB’s, upon your records’ “rowid” - this applies to (quite expensive) Navicat (which I own, and of which I am, otherwise, quite, satisfied with), and to ALL of the “compare”, “synch”, or whatever “diff” tools, paid or free, of my knowledge.

Why this here? Since most of the “serious” outlining tools we’re discussing here, rely on some db, be it SQLite, or then, some more “exotic” db format, and whenever you transfer some “sub-tree” from one db to another, those (free or paid-for) “compare” tools will NOT help you in any way then, for checking if everything has been processed as expected = hoped for, since, see above…

THUS: You will have to write your own scripts, in order to compare the integrity of shoveld sub-trees of any scale, and unfortunately, you will then encounter the sqlite.org (e.g.) “CSV problem”, i.e. the problem that any such csv output, to be then processed by further processing-by-further-scripting, in order to be then, finally, compared, vs. the respective sub trees (source, target, whatever) within other DBs of the same kind, will bear lots of unwanted, additional, “” (yeah: those double quotes which come with original CSV).

Thus, you might write several pre-scripts, in order to get just those “columns” which then will not be flooded by additional “”, and some for getting your columns which will inevitable be flooded by those, by the sqlite.exe command line tool (e.g. - try to process with other software, and it will all be even much more of a chaos!)...

before then, finally, bring together, your lot-to-then-being compared…

(Which then implies to write your real compare script…)

Here again, it’s MORE than just “surprising” that none of the (free or paid-for) “tools” developers had thought of, well, quite day-to-day, no?, “use cases”, allowing for db records’ comparison - and not even speaking of the “blob” columns here -, and thus making all their tools, free or paid-for, unusable for our day-to-day use cases indeed…

And the same remark applies to any “translation” of any information body, from one outliner to another, and whenever neither the developer of the “source” tool nor the developer of the “target” tool will have provided a (functional) export (for the former that is) or import (which would have been the task for the latter, in case) functionality… and that even applies to developers who have decided to back up their “outliners” by the same, i.e. SQLite, db standard…

And that’s obviously a “marking” fail, from those developers’ perspective, but a real problem from the users’, right?

And if there hadn’t been the tightbeams of this world, together with their followers: people who “think constructively by heart” might even have published their corresponding, respective script codes here, or then might have linked to those codes here, published on the respective tool sites’ fora… and then not, thanks again to tightbeams-&-discipleship…

That being said, at least you get the respective warnings, hints, advice to pay pay attention, and that’s not nothing, given current state of affairs between real contributors and just-ad-hominems…

Posted by 22111
Mar 20, 2023 at 05:07 PM

(To clarify: When you copy / move records (i.e. a single record, or sets of records) from one db to another one, the records get new IDs (and new rowIDs, in the case of SQLite), which then breaks any possibility for db “compare tools” / “differs” / frontends’ “compare” functionality) to CHECK for correct copy/move of the records from one db to another.

Also, the are / might be additional codepage problems…

Thus, you have to first get the correct data out of both of your DBs, then apply a compare script (to the resulting csv) which is capable to just compare the relevant “columns” a:b, AND which will be able to compare records (i.e. csv rows) “in disorder” - something the oh-so-much-applauded “Beyond Compare” has not been able to do after about 20 years of so-called “development” though, btw.)

Back to topic list