Separate SQL Generator Class

[Stiivi]

The class should be useable by SQLAlchemy applications in two ways: as a statement generator or as a statement consumer. Python applications should be able to provide statements into the query generator as ‘tables’ or should be able to get the generated statement (also influence statement generation) and use the generated star/snowflake for whatever they want. For example for materialization, use in other advanced, more complex customized queries, schema auditing or validation, value auditing, searching, indexing... In other words, output from the query generator should be useable denormalized star/snowflake with well defined interface.

Proposal:

class StarSchema(object):

Represents a star/snowflake table schema. Attributes:

• name – user specific name of the star schema, used for the schemaidentification, debug purposes and logging. Has no effect on the execution or statement composition.
• metadata is a SQLAlchemy metadata object where the snowflake tables are described.
• attributes is a dictionary of snowflake attributes. The keys are attribute names, the values can be strings, dictionaries or objects with specific attributes (see below)
• joins is a list of join specification (see below)
• tables are SQL Alchemy selectables (tables or statements) that are referenced in the attributes. This dictionary is looked-up first before the actual metadata. Only table name has to be specified and database schema should not be used in this case.
• schema – default database schema containing tables

The columns can be specified as:

• a string with format: column, table.column or schema.table.column. When no table is specified, then the fact table is considered.
• as a list of arguments [[schema,] table,] column
• StarColumn or any object with attributes schema, table, column, extract, unary can be used.
• a dictionary with keys same as the attributes of StarColumn object

Non-object arguments will be stored as a StarColumn objects internally.

The joins can be specified as a list of:

• tuples of column specification in form of (master, detail)
• a dictionary with keys or object with attributes: master, detail,
  alias and method.

master is a specification of a column in the master table (fact) and detail is a specification of a column in the detail table (usually a dimension). alias is an alternative name for the detail table to be
joined.

The method can be: match – LEFT INNER JOIN, master – LEFT OUTER JOIN or detail – RIGHT OUTER JOIN.

Note: The class should be cubes-independent, despite residing in the cubes repository. It should not use Cubes model objects. Reason is ability to separate it in the future when complexity of the SQL backend grows into extend where it would not be maintainable within the Cubes library.

[Stiivi]

Some pointers into the code (cubes/backends/sql/query.py) (notes from the mailing list post, to keep them here for the context):

• SnowflakeSchema – should be analyzed Cube and it’s tables (selectable source statements), once initialized it remains pretty-much static as a source of information for subsequent queries to the cube the schema represents. This should also represent the drill-across composition as a whole (in the future)
• QueryBuilder methods should probably be dissolved into the SnowflakeSchema and work in a functional way, returning something like SnowflakeQuery object containing alchemy statement + bunch of metadata

Note: Yes, the whole SQL backend with the query generator is going to be slightly bigger. However for library usability reasons I want it to be incubated within the package for the time being. Users not using SQL backends would have to apologize it and deal with it. It still has soft dependency on alchemy, therefore if it is not used, SQLAlchemy is not needed to be installed at all.

[Stiivi]

Have #118 (pre-aggregated tables) in mind.

[Stiivi]

Status update: no commit yet, still untangling the original code, dissolving the QueryBuilder between the StarSchema and the browser. Good news: so far looks more understandable, less going back-and-forth between layers (like functions fetching cube objects through browser).

[Stiivi]

Very early (incomplete) draft of the idea: f55575b

[Stiivi]

Closing this one, as the StarSchema was implemented and contains all major features it should contain. The rest is to be implemented (moved) into the Browser class. See #266 for more information about the progress.