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