Fun with Databases

[LilBudyWizer]

I extracted the database definitions and put them in JSON files. Since I was pulling data out of the databases I decided I might as well put the data in a database. I pull the definitions using pragmas, but you can't use a pragma as a table or view. So I loaded the data from the pragmas back into tables you can query. The general structure is:

Schemas
    SchemaStats
    Tables
        TableStats
        TableTags
        Columns
            ColumnTags
        Indexes
            IndexColumns
        ForeignKeys

The tables are generally keyed as SchID, TblID, ColID. I provide a Hier view for each table to pick up the names. With Schemas and Tables I provide a WithStats view that combines the information from the two tables. I also provide additional tables:

1. Children
2. Orphans
3. Parents
4. Childless
5. ChildlessOrphans

These all come ForeignKeys. Children is a list of all tables with foreign keys and the tables those foreign keys refer to. Orphans are the tables with no foreign keys. Parents are the tables referred to by foreign keys in other tables and which tables those are. Childless are tables for which no other table refers to it in a foreign key. ChildlessOrphans are the tables with no foreign keys and which are not referred to by a foreign key of any other table. Orphans and Childless does not include any of the ChildlessOrphans. The JSON files include FamilyTree and Ancestors. That is parents and children expanded to a full tree. There are circular references. An example is Leaders has a foreign key to itself. That likely creates a hierarchical relationship among leaders.

Tags are the name of the table/column split into words. There's nothing you couldn't get from the base tables: Schemas, Tables, Columns, Indexes, IndexColumns and ForeignKeys. The rest is to simplify accessing the data. Queries can get complex and basics covered can make those queries far less tedious. Everything is also in JSON files. You could pull the data off the database and stick in some structure or just load the JSON.

 

[LilBudyWizer]

Updated for V1.2.0

 

[LilBudyWizer]

Diff from V1.1.1 to V1.2.0