def __init__(self, *args, **kwargs):
# store the Aldjemy tables in a "bridge" object, for legacy reasons
self.bridge = get_tables()
self.use_cache = True
super(SqlAlchemyResource, self).__init__(*args, **kwargs)
def build_sqlalchemy_columns(
self, fields, base_query_tables=None, custom_columns=None):
'''
Returns an ordered dict of sqlalchemy.sql.schema.Column objects, associated
with the sqlalchemy.sql.schema.Table definitions, which are bound to
the sqlalchemy.engine.Engine:
"Connects a Pool and Dialect together to provide a source of database
connectivity and behavior."
@param fields - field definitions, from the resource schema
@param bridge - a reports.utils.sqlalchemy_bridge.Bridge
@param base_query_tables - if specified, the fields for these tables
will be available as part of the base query, so the column definitions
become simpler, and do not need to be joined in.
@param manual_includes - columns to include even if the field
visibility is not set
'''
DEBUG_BUILD_COLUMNS = False or logger.isEnabledFor(logging.DEBUG)
base_query_tables = base_query_tables or []
custom_columns = custom_columns or []
try:
columns = OrderedDict()
for field in fields:
key = field['key']
if key in custom_columns:
if DEBUG_BUILD_COLUMNS:
logger.info(
'custom field: %r, %r', key,custom_columns[key])
columns[key] = custom_columns[key].label(key)
continue
if DEBUG_BUILD_COLUMNS:
logger.info('build column: %r, %r', field['key'], field)
field_name = field.get('field', None)
if not field_name:
field_name = field['key']
field_table = field.get('table', None)
if not field_table and DEBUG_BUILD_COLUMNS:
logger.info(
'field: %r, val: %r, skipping field because there is no '
'"field_table" value set',key,field)
continue
if DEBUG_BUILD_COLUMNS:
logger.info(
'field: %r, field_table: %r', field['key'], field_table )
if field_table in base_query_tables:
# simple case: table.fields already selected in the base query:
# just need to specify them
if field_name in get_tables()[field_table].c:
col = get_tables()[field_table].c[field_name]
else:
raise Exception(
'field: %r, not found in table: %r'
% (field_name, field_table))
col = col.label(key)
columns[key] = col
elif field.get('linked_field_value_field', None):
link_table = field['table']
link_table_def = get_tables()[link_table]
linked_field_parent = field['linked_field_parent']
link_field = linked_field_parent + '_id'
join_args = {
'link_table': link_table, 'link_field': link_field,
'parent_table': linked_field_parent
}
if field['linked_field_type'] != 'fields.ListField':
join_stmt = select([link_table_def.c[field_name]]).\
where(text('{link_table}.{link_field}='
'{parent_table}.{link_field}'.format(**join_args)))
if field.get('linked_field_meta_field', None):
# TODO: test - the linked meta field is the "datacolumn type"
linked_field_meta_field = field['linked_field_meta_field']
meta_field_obj = MetaHash.objects.get(
key=field['key'], scope=field['scope'])
meta_table_def = get_tables()['metahash']
join_stmt.join(meta_table_def,
link_table_def.c[linked_field_meta_field]==
getattr(meta_field_obj,'pk') )
join_stmt = join_stmt.label(key)
columns[key] = join_stmt
elif field['linked_field_type'] == 'fields.ListField':
join_stmt = select([link_table_def.c[field_name]]).\
where(text('{link_table}.{link_field}='
'{parent_table}.{link_field}'.format(**join_args)))
if field.get('linked_field_meta_field', None):
# TODO: test - the linked meta field is the "datacolumn type"
linked_field_meta_field = field['linked_field_meta_field']
meta_field_obj = MetaHash.objects.get(
key=field['key'], scope=field['scope'])
meta_table_def = get_tables()['metahash']
join_stmt.join(meta_table_def,
link_table_def.c[linked_field_meta_field]==
getattr(meta_field_obj,'pk') )
ordinal_field = field.get('ordinal_field', None)
if ordinal_field:
join_stmt = join_stmt.order_by(link_table_def.c[ordinal_field])
join_stmt = join_stmt.alias('a')
stmt2 = select([func.array_to_string(
func.array_agg(column(field_name)),
LIST_DELIMITER_SQL_ARRAY)])
stmt2 = stmt2.select_from(join_stmt).label(key)
columns[key] = stmt2
else:
if DEBUG_BUILD_COLUMNS:
logger.info(
'field is not in the base tables %r, nor in a linked field, '
'and is not custom: %s', base_query_tables, key)
if DEBUG_BUILD_COLUMNS:
logger.info('columns: %r', columns.keys())
return columns
except Exception, e:
logger.exception('on build sqlalchemy columns')
raise e
