5 ms·

Jailer: A truly relational database client

85 points by wisser 5y ago

civilized 5y ago
I would love to understand what this is for, but the README went over my head. The subsetting and such that this software does is definitely very consistent, topological and hierarchically structured, I've determined that much. It's got referential integrity too!
- SomeCallMeTim 5y ago
  Roughly: Say you have a relational database with a dozen tables that all connect to each other using foreign keys: A user table has a user id, a message table refers to a user, a log table refers to the user and a message, a comment table refers to a message and a user, and so forth. 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.
  - rejectedandsad 5y ago
    > 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?)
    - rzzzt 5y ago
      "Referential integrity" is when you don't have any dangling foreign keys that can not be resolved to an entity/document/row. "Data subsetting" is the process of extracting the relevant parts out of a larger data set.
    - helderoliozuzl 5y ago
      What do you use instead of SQL DB?
      - rejectedandsad 5y ago
        DynamoDB. The data model is relational, despite the fact that it shouldn't be.
    - rbobby 5y ago
      I wrote my own for this, specific for my $DAYJOB application. 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.
- politician 5y ago
  Suppose you want to look at a couple of rows from a particular table in your database. 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.
anigbrowl 5y ago
I dislike working with RDBs but this seems like will take a lot of the pain out of it.
tompazourek 5y ago
The "relations" in relational databases actually correspond to tables, not relationships between tables. Which makes the "truly relational" sound a bit silly... See: https://en.wikipedia.org/wiki/Relation_(database) https://en.wikipedia.org/wiki/Relation_(database)
- helderoliozuzl 5y ago
  This is truly true.
foolmeonce 5y ago
Has anyone seen anything similar but for browsers? (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.)
- webmaven 5y ago
  Something like CSV Reader?: https://chrome.google.com/webstore/detail/csv-reader/dnioinfbhmclclfdbcnlfgbojdpdicde https://chrome.google.com/webstore/detail/csv-reader/dnioinf...
  - foolmeonce 5y ago
    Not really, I want to scrape a relationship from the current page into a table to be used in joins, etc, with tables from other pages I have previously scraped. The current page being CSV is only an example of a clear relational table. 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.
johannes1234321 5y ago
> The data in one table would not be related to the data in the other tables. It would not be referentially intact. 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. https://en.m.wikipedia.org/wiki/Relation_(database) https://en.m.wikipedia.org/wiki/Relation_(database)
- yyybcbcbc 5y ago
  How would you phrase it correctly? After all, tables in a relational database are also related to each other.
  - johannes1234321 5y ago
    You may "refer" to a different relation and have referential integrity between relations. Maybe "joinable" is also acceptable. You might also explicitly mention "foreign key" constraints. 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.

85 points by wisser 5y ago

I would love to understand what this is for, but the README went over my head. The subsetting and such that this software does is definitely very consistent, topological and hierarchically structured, I've determined that much. It's got referential integrity too!

SomeCallMeTim 5y ago
Roughly: Say you have a relational database with a dozen tables that all connect to each other using foreign keys: A user table has a user id, a message table refers to a user, a log table refers to the user and a message, a comment table refers to a message and a user, and so forth. 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.
- rejectedandsad 5y ago
  > 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?)
  - rzzzt 5y ago
    "Referential integrity" is when you don't have any dangling foreign keys that can not be resolved to an entity/document/row. "Data subsetting" is the process of extracting the relevant parts out of a larger data set.
  - helderoliozuzl 5y ago
    What do you use instead of SQL DB?
    - rejectedandsad 5y ago
      DynamoDB. The data model is relational, despite the fact that it shouldn't be.
  - rbobby 5y ago
    I wrote my own for this, specific for my $DAYJOB application. 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.
politician 5y ago
Suppose you want to look at a couple of rows from a particular table in your database. 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.

anigbrowl 5y ago

I dislike working with RDBs but this seems like will take a lot of the pain out of it.

tompazourek 5y ago

The "relations" in relational databases actually correspond to tables, not relationships between tables. Which makes the "truly relational" sound a bit silly... See: https://en.wikipedia.org/wiki/Relation_(database) https://en.wikipedia.org/wiki/Relation_(database)

helderoliozuzl 5y ago
This is truly true.

foolmeonce 5y ago

Has anyone seen anything similar but for browsers? (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.)

webmaven 5y ago
Something like CSV Reader?: https://chrome.google.com/webstore/detail/csv-reader/dnioinfbhmclclfdbcnlfgbojdpdicde https://chrome.google.com/webstore/detail/csv-reader/dnioinf...
- foolmeonce 5y ago
  Not really, I want to scrape a relationship from the current page into a table to be used in joins, etc, with tables from other pages I have previously scraped. The current page being CSV is only an example of a clear relational table. 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.

johannes1234321 5y ago

> The data in one table would not be related to the data in the other tables. It would not be referentially intact. 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. https://en.m.wikipedia.org/wiki/Relation_(database) https://en.m.wikipedia.org/wiki/Relation_(database)

yyybcbcbc 5y ago
How would you phrase it correctly? After all, tables in a relational database are also related to each other.
- johannes1234321 5y ago
  You may "refer" to a different relation and have referential integrity between relations. Maybe "joinable" is also acceptable. You might also explicitly mention "foreign key" constraints. 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.