def __init__(self, cube, connectable=None, locale=None, metadata=None, debug=False, **options): """SnowflakeBrowser is a SQL-based AggregationBrowser implementation that can aggregate star and snowflake schemas without need of having explicit view or physical denormalized table. Attributes: * `cube` - browsed cube * `connectable` - SQLAlchemy connectable object (engine or connection) * `locale` - locale used for browsing * `metadata` - SQLAlchemy MetaData object * `debug` - output SQL to the logger at INFO level * `options` - passed to the mapper and context (see their respective documentation) Limitations: * only one locale can be used for browsing at a time * locale is implemented as denormalized: one column for each language """ super(SnowflakeBrowser, self).__init__(cube) if cube == None: raise ArgumentError("Cube for browser should not be None.") self.logger = get_logger() self.cube = cube self.locale = locale or cube.model.locale self.debug = debug if connectable is not None: self.connectable = connectable self.metadata = metadata or sqlalchemy.MetaData(bind=self.connectable) # Mapper is responsible for finding corresponding physical columns to # dimension attributes and fact measures. It also provides information # about relevant joins to be able to retrieve certain attributes. if options.get("use_denormalization"): mapper_class = DenormalizedMapper else: mapper_class = SnowflakeMapper self.logger.debug("using mapper %s for cube '%s' (locale: %s)" % \ (str(mapper_class.__name__), cube.name, locale)) self.mapper = mapper_class(cube, locale=self.locale, **options) self.logger.debug("mapper schema: %s" % self.mapper.schema) # QueryContext is creating SQL statements (using SQLAlchemy). It # also caches information about tables retrieved from metadata. self.context = QueryContext(self.cube, self.mapper, metadata=self.metadata)
def __init__(self, cube, connectable=None, locale=None, metadata=None, debug=False, **options): """AggregatedCubeBrowser is a SQL-based AggregationBrowser implementation that uses pre-aggregated table. Attributes: * `cube` - browsed cube * `connectable` - SQLAlchemy connectable object (engine or connection) * `locale` - locale used for browsing * `metadata` - SQLAlchemy MetaData object * `debug` - output SQL to the logger at INFO level * `options` - passed to the mapper and context (see their respective documentation) """ super(AggregatedCubeBrowser, self).__init__(cube) if cube == None: raise ArgumentError("Cube for browser should not be None.") self.logger = get_logger() self.cube = cube self.locale = locale or cube.model.locale self.debug = debug if connectable is not None: self.connectable = connectable self.metadata = metadata or sqlalchemy.MetaData(bind=self.connectable) # Mapper is responsible for finding corresponding physical columns to # dimension attributes and fact measures. It also provides information # about relevant joins to be able to retrieve certain attributes. if options.get("use_denormalization"): mapper_class = DenormalizedMapper else: mapper_class = SnowflakeMapper self.logger.debug("using mapper %s for cube '%s' (locale: %s)" % \ (str(mapper_class.__name__), cube.name, locale)) self.mapper = mapper_class(cube, locale=self.locale, **options) self.logger.debug("mapper schema: %s" % self.mapper.schema) # QueryContext is creating SQL statements (using SQLAlchemy). It # also caches information about tables retrieved from metadata. self.context = QueryContext(self.cube, self.mapper, metadata=self.metadata) # Map: logical attribute --> self.attribute_columns = {} self.alias_columns
def __init__(self, model, engine, **options): """Create a workspace. For description of options see `create_workspace()` """ super(SQLStarWorkspace, self).__init__(model) self.logger = get_logger() self.engine = engine self.schema = options.get("schema") self.metadata = sqlalchemy.MetaData(bind=self.engine,schema=self.schema) self.options = options
def __init__(self, cube, locale=None, schema=None, fact_name=None, **options): """Abstract class for mappers which maps logical references to physical references (tables and columns). Attributes: * `cube` - mapped cube * `simplify_dimension_references` – references for flat dimensions (with one level and no details) will be just dimension names, no attribute name. Might be useful when using single-table schema, for example, with couple of one-column dimensions. * `fact_name` – fact name, if not specified then `cube.name` is used * `schema` – default database schema """ super(Mapper, self).__init__() if cube == None: raise Exception("Cube for mapper should not be None.") self.logger = get_logger() self.cube = cube self.locale = locale fact_prefix = options.get("fact_prefix") or "" self.fact_name = fact_name or self.cube.fact or fact_prefix + self.cube.name self.schema = schema if "simplify_dimension_references" in options: self.simplify_dimension_references = options[ "simplify_dimension_references"] else: self.simplify_dimension_references = True if self.schema: schemastr = "'%s'" % self.schema else: schemastr = "(default)" self.logger.debug( "mapper options: fact:'%s', schema:%s, " "simplify: %s" % (self.fact_name, schemastr, self.simplify_dimension_references)) self._collect_attributes()
def __init__(self, model, engine, **options): """Create a workspace. For description of options see `create_workspace()` """ super(SQLStarWorkspace, self).__init__() self.logger = get_logger() self.model = model self.engine = engine self.schema = options.get("schema") self.metadata = sqlalchemy.MetaData(bind=self.engine, schema=self.schema) self.options = options
def __init__(self, cube, locale=None, schema=None, fact_name=None, **options): """Abstract class for mappers which maps logical references to physical references (tables and columns). Attributes: * `cube` - mapped cube * `simplify_dimension_references` – references for flat dimensions (with one level and no details) will be just dimension names, no attribute name. Might be useful when using single-table schema, for example, with couple of one-column dimensions. * `fact_name` – fact name, if not specified then `cube.name` is used * `schema` – default database schema """ super(Mapper, self).__init__() if cube == None: raise Exception("Cube for mapper should not be None.") self.logger = get_logger() self.cube = cube self.locale = locale fact_prefix = options.get("fact_prefix") or "" self.fact_name = fact_name or self.cube.fact or fact_prefix+self.cube.name self.schema=schema if "simplify_dimension_references" in options: self.simplify_dimension_references = options["simplify_dimension_references"] else: self.simplify_dimension_references = True if self.schema: schemastr = "'%s'" % self.schema else: schemastr = "(default)" self.logger.debug("mapper options: fact:'%s', schema:%s, " "simplify: %s" % (self.fact_name, schemastr, self.simplify_dimension_references)) self._collect_attributes()
def __init__(self, cube, mapper, metadata, **options): """Object providing context for constructing queries. Puts together the mapper and physical structure. `mapper` - which is used for mapping logical to physical attributes and performing joins. `metadata` is a `sqlalchemy.MetaData` instance for getting physical table representations. Object attributes: * `fact_table` – the physical fact table - `sqlalchemy.Table` instance * `tables` – a dictionary where keys are table references (schema, table) or (shchema, alias) to real tables - `sqlalchemy.Table` instances .. note:: To get results as a dictionary, you should ``zip()`` the returned rows after statement execution with: labels = [column.name for column in statement.columns] ... record = dict(zip(labels, row)) This is little overhead for a workaround for SQLAlchemy behaviour in SQLite database. SQLite engine does not respect dots in column names which results in "duplicate column name" error. """ super(QueryContext, self).__init__() self.logger = get_logger() self.cube = cube self.mapper = mapper self.schema = mapper.schema self.metadata = metadata # Prepare physical fact table - fetch from metadata # self.fact_key = self.cube.key or DEFAULT_KEY_FIELD self.fact_name = mapper.fact_name self.fact_table = sqlalchemy.Table(self.fact_name, self.metadata, autoload=True, schema=self.schema) self.tables = { (self.schema, self.fact_name): self.fact_table }
def __init__(self, cube, mapper, metadata, **options): """Object providing context for constructing queries. Puts together the mapper and physical structure. `mapper` - which is used for mapping logical to physical attributes and performing joins. `metadata` is a `sqlalchemy.MetaData` instance for getting physical table representations. Object attributes: * `fact_table` – the physical fact table - `sqlalchemy.Table` instance * `tables` – a dictionary where keys are table references (schema, table) or (shchema, alias) to real tables - `sqlalchemy.Table` instances .. note:: To get results as a dictionary, you should ``zip()`` the returned rows after statement execution with: labels = [column.name for column in statement.columns] ... record = dict(zip(labels, row)) This is little overhead for a workaround for SQLAlchemy behaviour in SQLite database. SQLite engine does not respect dots in column names which results in "duplicate column name" error. """ super(QueryContext, self).__init__() self.logger = get_logger() self.cube = cube self.mapper = mapper self.schema = mapper.schema self.metadata = metadata # Prepare physical fact table - fetch from metadata # self.fact_key = self.cube.key or DEFAULT_KEY_FIELD self.fact_name = mapper.fact_name self.fact_table = sqlalchemy.Table(self.fact_name, self.metadata, autoload=True, schema=self.schema) self.tables = {(self.schema, self.fact_name): self.fact_table}
import unittest import os import model import browser import combinations import default_sql_backend import sql_star_browser from cubes.common import get_logger import logging logger = get_logger() logger.setLevel(logging.DEBUG) def suite(): suite = unittest.TestSuite() suite.addTest(model.suite()) suite.addTest(browser.suite()) suite.addTest(combinations.suite()) suite.addTest(default_sql_backend.suite()) suite.addTest(sql_star_browser.suite()) return suite
def __init__(self, cube, mapper, metadata, **options): """Object providing context for constructing queries. Puts together the mapper and physical structure. `mapper` - which is used for mapping logical to physical attributes and performing joins. `metadata` is a `sqlalchemy.MetaData` instance for getting physical table representations. Object attributes: * `fact_table` – the physical fact table - `sqlalchemy.Table` instance * `tables` – a dictionary where keys are table references (schema, table) or (shchema, alias) to real tables - `sqlalchemy.Table` instances .. note:: To get results as a dictionary, you should ``zip()`` the returned rows after statement execution with: labels = [column.name for column in statement.columns] ... record = dict(zip(labels, row)) This is little overhead for a workaround for SQLAlchemy behaviour in SQLite database. SQLite engine does not respect dots in column names which results in "duplicate column name" error. """ super(QueryContext, self).__init__() self.logger = get_logger() self.cube = cube self.mapper = mapper self.schema = mapper.schema self.metadata = metadata # Prepare physical fact table - fetch from metadata # self.fact_key = self.cube.key or DEFAULT_KEY_FIELD self.fact_name = mapper.fact_name self.fact_table = sqlalchemy.Table(self.fact_name, self.metadata, autoload=True, schema=self.schema) self.tables = { (self.schema, self.fact_name): self.fact_table } # Collect all tables and their aliases. # # table_aliases contains mapping between aliased table name and real # table name with alias: # # (schema, aliased_name) --> (schema, real_name, alias) # self.table_aliases = { (self.schema, self.fact_name): (self.schema, self.fact_name, None) } # Collect all table aliases from joins detail tables for join in self.mapper.joins: # just ask for the table table = AliasedTable(join.detail.schema, join.detail.table, join.alias) table_alias = (join.detail.schema, join.alias or join.detail.table) self.table_aliases[table_alias] = table # Mapping where keys are attributes and values are columns self.logical_to_column = {} # Mapping where keys are column labels and values are attributes self.column_to_logical = {} self.safe_labels = options.get("safe_labels", False) self.label_counter = 1
def __init__(self, cube, mapper, metadata, **options): """Object providing context for constructing queries. Puts together the mapper and physical structure. `mapper` - which is used for mapping logical to physical attributes and performing joins. `metadata` is a `sqlalchemy.MetaData` instance for getting physical table representations. Object attributes: * `fact_table` – the physical fact table - `sqlalchemy.Table` instance * `tables` – a dictionary where keys are table references (schema, table) or (shchema, alias) to real tables - `sqlalchemy.Table` instances .. note:: To get results as a dictionary, you should ``zip()`` the returned rows after statement execution with: labels = [column.name for column in statement.columns] ... record = dict(zip(labels, row)) This is little overhead for a workaround for SQLAlchemy behaviour in SQLite database. SQLite engine does not respect dots in column names which results in "duplicate column name" error. """ super(QueryContext, self).__init__() self.logger = get_logger() self.cube = cube self.mapper = mapper self.schema = mapper.schema self.metadata = metadata # Prepare physical fact table - fetch from metadata # self.fact_key = self.cube.key or DEFAULT_KEY_FIELD self.fact_name = mapper.fact_name try: self.fact_table = sqlalchemy.Table(self.fact_name, self.metadata, autoload=True, schema=self.schema) except sqlalchemy.exc.NoSuchTableError: in_schema = " in schema '%s'" if self.schema else "" msg = "No such fact table '%s'%s." % (self.fact_name, in_schema) raise WorkspaceError(msg) self.tables = {(self.schema, self.fact_name): self.fact_table} # Collect all tables and their aliases. # # table_aliases contains mapping between aliased table name and real # table name with alias: # # (schema, aliased_name) --> (schema, real_name, alias) # self.table_aliases = { (self.schema, self.fact_name): (self.schema, self.fact_name, None) } # Collect all table aliases from joins detail tables for join in self.mapper.joins: # just ask for the table table = AliasedTable(join.detail.schema, join.detail.table, join.alias) table_alias = (join.detail.schema, join.alias or join.detail.table) self.table_aliases[table_alias] = table # Mapping where keys are attributes and values are columns self.logical_to_column = {} # Mapping where keys are column labels and values are attributes self.column_to_logical = {} self.safe_labels = options.get("safe_labels", False) self.label_counter = 1
def __init__(self, cube, connectable=None, locale=None, metadata=None, debug=False, **options): """SnowflakeBrowser is a SQL-based AggregationBrowser implementation that can aggregate star and snowflake schemas without need of having explicit view or physical denormalized table. Attributes: * `cube` - browsed cube * `connectable` - SQLAlchemy connectable object (engine or connection) * `locale` - locale used for browsing * `metadata` - SQLAlchemy MetaData object * `debug` - output SQL to the logger at INFO level * `options` - passed to the mapper and context (see their respective documentation) Tuning: * `include_summary` - it ``True`` then summary is included in aggregation result. Turned on by default. * `include_cell_count` – if ``True`` then total cell count is included in aggregation result. Turned on by default, might be turned off for performance reasons Limitations: * only one locale can be used for browsing at a time * locale is implemented as denormalized: one column for each language """ super(SnowflakeBrowser, self).__init__(cube) if cube == None: raise ArgumentError("Cube for browser should not be None.") self.logger = get_logger() self.cube = cube self.locale = locale or cube.model.locale self.debug = debug if connectable is not None: self.connectable = connectable self.metadata = metadata or sqlalchemy.MetaData( bind=self.connectable) self.include_summary = options.get("include_summary", True) self.include_cell_count = options.get("include_cell_count", True) # Mapper is responsible for finding corresponding physical columns to # dimension attributes and fact measures. It also provides information # about relevant joins to be able to retrieve certain attributes. if options.get("use_denormalization"): mapper_class = DenormalizedMapper else: mapper_class = SnowflakeMapper self.logger.debug("using mapper %s for cube '%s' (locale: %s)" % \ (str(mapper_class.__name__), cube.name, locale)) self.mapper = mapper_class(cube, locale=self.locale, **options) self.logger.debug("mapper schema: %s" % self.mapper.schema) # QueryContext is creating SQL statements (using SQLAlchemy). It # also caches information about tables retrieved from metadata. # FIXME: new context is created also when locale changes in set_locale self.options = options self.context = QueryContext(self.cube, self.mapper, metadata=self.metadata, **self.options)