0001from .main import SQLObject
0002from .sqlbuilder import AND, Alias, ColumnAS, LEFTJOINOn,       NoDefault, SQLCall, SQLConstant, SQLObjectField, SQLObjectTable, SQLOp,       Select, sqlrepr
0005
0006
0007class ViewSQLObjectField(SQLObjectField):
0008    def __init__(self, alias, *arg):
0009        SQLObjectField.__init__(self, *arg)
0010        self.alias = alias
0011
0012    def __sqlrepr__(self, db):
0013        return self.alias + "." + self.fieldName
0014
0015    def tablesUsedImmediate(self):
0016        return [self.tableName]
0017
0018
0019class ViewSQLObjectTable(SQLObjectTable):
0020    FieldClass = ViewSQLObjectField
0021
0022    def __getattr__(self, attr):
0023        if attr == 'sqlmeta':
0024            raise AttributeError
0025        return SQLObjectTable.__getattr__(self, attr)
0026
0027    def _getattrFromID(self, attr):
0028        return self.FieldClass(self.soClass.sqlmeta.alias, self.tableName,
0029                               'id', attr, self.soClass, None)
0030
0031    def _getattrFromColumn(self, column, attr):
0032        return self.FieldClass(self.soClass.sqlmeta.alias, self.tableName,
0033                               column.name, attr, self.soClass, column)
0034
0035
0036class ViewSQLObject(SQLObject):
0037    """
0038    A SQLObject class that derives all it's values from other SQLObject
0039    classes. Columns on subclasses should use SQLBuilder constructs for dbName,
0040    and sqlmeta should specify:
0041
0042    * idName as a SQLBuilder construction
0043    * clause as SQLBuilder clause for specifying join conditions
0044      or other restrictions
0045    * table as an optional alternate name for the class alias
0046
0047    See test_views.py for simple examples.
0048    """
0049
0050    def __classinit__(cls, new_attrs):
0051        SQLObject.__classinit__(cls, new_attrs)
0052        # like is_base
0053        if cls.__name__ != 'ViewSQLObject':
0054            dbName = hasattr(cls, '_connection') and                   (cls._connection and cls._connection.dbName) or None
0056
0057            if getattr(cls.sqlmeta, 'table', None):
0058                cls.sqlmeta.alias = cls.sqlmeta.table
0059            else:
0060                cls.sqlmeta.alias =                       cls.sqlmeta.style.pythonClassToDBTable(cls.__name__)
0062            alias = cls.sqlmeta.alias
0063            columns = [ColumnAS(cls.sqlmeta.idName, 'id')]
0064            # {sqlrepr-key: [restriction, *aggregate-column]}
0065            aggregates = {'': [None]}
0066            inverseColumns = dict(
0067                [(y, x) for x, y in cls.sqlmeta.columns.items()])
0068            for col in cls.sqlmeta.columnList:
0069                n = inverseColumns[col]
0070                ascol = ColumnAS(col.dbName, n)
0071                if isAggregate(col.dbName):
0072                    restriction = getattr(col, 'aggregateClause', None)
0073                    if restriction:
0074                        restrictkey = sqlrepr(restriction, dbName)
0075                        aggregates[restrictkey] =                               aggregates.get(restrictkey, [restriction]) +                               [ascol]
0078                    else:
0079                        aggregates[''].append(ascol)
0080                else:
0081                    columns.append(ascol)
0082
0083            metajoin = getattr(cls.sqlmeta, 'join', NoDefault)
0084            clause = getattr(cls.sqlmeta, 'clause', NoDefault)
0085            select = Select(columns,
0086                            distinct=True,
0087                            # @@ LDO check if this really mattered
0088                            # for performance
0089                            # @@ Postgres (and MySQL?) extension!
0090                            # distinctOn=cls.sqlmeta.idName,
0091                            join=metajoin,
0092                            clause=clause)
0093
0094            aggregates = aggregates.values()
0095
0096            if aggregates != [[None]]:
0097                join = []
0098                last_alias = "%s_base" % alias
0099                last_id = "id"
0100                last = Alias(select, last_alias)
0101                columns = [
0102                    ColumnAS(SQLConstant("%s.%s" % (last_alias, x.expr2)),
0103                             x.expr2) for x in columns]
0104
0105                for i, agg in enumerate(aggregates):
0106                    restriction = agg[0]
0107                    if restriction is None:
0108                        restriction = clause
0109                    else:
0110                        restriction = AND(clause, restriction)
0111                    agg = agg[1:]
0112                    agg_alias = "%s_%s" % (alias, i)
0113                    agg_id = '%s_id' % agg_alias
0114                    if not last.q.alias.endswith('base'):
0115                        last = None
0116                    new_alias = Alias(Select(
0117                        [ColumnAS(cls.sqlmeta.idName, agg_id)] + agg,
0118                        groupBy=cls.sqlmeta.idName,
0119                        join=metajoin,
0120                        clause=restriction),
0121                        agg_alias)
0122                    agg_join = LEFTJOINOn(last, new_alias,
0123                                          "%s.%s = %s.%s" % (
0124                                              last_alias, last_id,
0125                                              agg_alias, agg_id))
0126
0127                    join.append(agg_join)
0128                    for col in agg:
0129                        columns.append(
0130                            ColumnAS(SQLConstant(
0131                                "%s.%s" % (agg_alias, col.expr2)),
0132                                col.expr2))
0133
0134                    last = new_alias
0135                    last_alias = agg_alias
0136                    last_id = agg_id
0137                select = Select(columns,
0138                                join=join)
0139
0140            cls.sqlmeta.table = Alias(select, alias)
0141            cls.q = ViewSQLObjectTable(cls)
0142            for n, col in cls.sqlmeta.columns.items():
0143                col.dbName = n
0144
0145
0146def isAggregate(expr):
0147    if isinstance(expr, SQLCall):
0148        return True
0149    if isinstance(expr, SQLOp):
0150        return isAggregate(expr.expr1) or isAggregate(expr.expr2)
0151    return False