Jailer: A truly relational database client
github.com
github.com
The use of "related" in the docs and here in the title of the posting seems to make the typical wrong assumption, that "relational database" refers to the "relation" between "related" tables. However the theory refers to the relation between the tuples aka rows. Thus that the relation is the single table.
Purely mathematically I believe. In relational algebra there is no term, as from a mathematical point you can join any relations. Whether that makes sense is a different question.
Jailer can, say, grab a few comments from the comment table and at the same time grab any users and messages that were referenced. Then all of those records spread across all of the tables can be exported for use by, for instance, tests.
Imagine you found a bug, say, in your app logic, and you wanted to grab an image of the exact data that triggers the bug to repro that bug and use it in a test. This tool makes that easy.
This is actually a fairly common usecase in my $DAYJOB, what's the typical name for a tool that does this (that isn't the above, since I don't use a SQLdb?)
It primarily focused on configuration tables, and optionally a slice of the main data tables.
The tricky parts were:
1. Write code to write out DDL. This is easier than it sounds because not every DDL feature is actually used by the application.
2. Write code to dump out DML. Again easier than it sounds because not every datatype is used by the application. The tricky parts where automatic identity columns and large binary table.
3. The code from #1 and #2 has to be done in topological order (least dependent scheme objects to most dependent). After the DDL is write, write the DML.
4. A bit fancy streaming to ensure the webserver doesn't build everything up in memory before zip filing it and beaming it down the wire.
5. Some care needs to be taken for confidential information. User passwords (even bcrypted/scrypted) shouldn't come along. Nor should thinks like outgoing email server settings. Or incoming email server settings. I'm lucky as no PII or credit card details exist in $DAYJOB application.
If done carefully the code should be insensitive to simple schema changes (new fields, new configuration tables).
The goal behind it all is to produce a single (big) .SQL file that will recreate a working database.
So worth the time and effort to create.
This tool will make those rows available locally along with all of the rows from all of the tables that might have a dependency on those initial rows, so that you can explore the entire space.
Equivalently, it's doing for your data what a package manager does for dependency management.
Equivalently, it's building a subgraph from a set of root nodes in the graph that is your database.
(I.e. I would like an extension popping CSV and other relational formats while surfing into the browser's DBs to then work with in a GUI like this.)
One can do similar things ~manually popping data into the domains Key-value DBs while scraping, then injecting a GUI to work with all the data, but a tool for doing this with relational database primitives would be more powerful as jailer demonstrates.