Generate SQL schema based on model

[Stiivi]

Create a SQL schema from logical model. Options:

• generate star schema with same naming as model attributes, one table per dimension
• use the mappings and reversse-generate physical schema

Suggestion by gauthier (#databrewery IRC)

[smoothdeveloper]

From end user perspective, it could be used this way:

slicer model generatepseudoschema slicer.ini

another option

slicer model generatepseudoschema BrowserType model.json

both would output to the console

[Stiivi]

added as:

slicer ddl postgresql:// model.json

Usage:

usage: slicer ddl [-h] [--dimension-prefix DIMENSION_PREFIX]
                  [--fact-prefix FACT_PREFIX]
                  url model

positional arguments:
  url                   SQL database connection URL
  model                 model reference - can be a local file path or URL

optional arguments:
  -h, --help            show this help message and exit
  --dimension-prefix DIMENSION_PREFIX
                        prefix for dimension tables
  --fact-prefix FACT_PREFIX
                        prefix for fact tables

Mapping is not considered yet, neither localisation.

[tlevine]

Relatedly, I've been toying with generating models from SQLAlchemy subclasses in order to define both my database schema and my cubes model at the same time.

I make subclasses of the Fact and Dimension classes in this file to specify the different tables in an approximately snowflake shape. Then I call model from this file to generate the model JSON file. Or I call database to get the SQLAlchemy session so I can add things to the database. Here's an example.

Things wind up being a bit inefficient in the schema so that I don't have to specify things twice. For example, I need to create multiple dimension tables in order to have different hierarchies for the same conceptual dimension.

I still haven't settled on how this model generation would work, but perhaps this could make it into cubes if I come up with something I like and I figure out how cubes works.