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