0001fromsqlbuilderimport*0002frommainimportSQLObject,sqlmeta0003importtypes,threading00040005####00060007classViewSQLObjectField(SQLObjectField):0008def__init__(self,alias,*arg):0009SQLObjectField.__init__(self,*arg)0010self.alias=alias0011def__sqlrepr__(self,db):0012returnself.alias+"."+self.fieldName0013deftablesUsedImmediate(self):0014return[self.tableName]00150016classViewSQLObjectTable(SQLObjectTable):0017FieldClass=ViewSQLObjectField00180019def__getattr__(self,attr):0020ifattr=='sqlmeta':0021raiseAttributeError0022returnSQLObjectTable.__getattr__(self,attr)00230024def_getattrFromID(self,attr):0025returnself.FieldClass(self.soClass.sqlmeta.alias,self.tableName,'id',attr,self.soClass,None)00260027def_getattrFromColumn(self,column,attr):0028returnself.FieldClass(self.soClass.sqlmeta.alias,self.tableName,column.name,attr,self.soClass,column)002900300031classViewSQLObject(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:00360037 * idName as a SQLBuilder construction0038 * clause as SQLBuilder clause for specifying join conditions or other restrictions0039 * table as an optional alternate name for the class alias00400041 See test_views.py for simple examples.0042 """00430044def__classinit__(cls,new_attrs):0045SQLObject.__classinit__(cls,new_attrs)0046# like is_base0047ifcls.__name__!='ViewSQLObject':0048dbName=hasattr(cls,'_connection')and(cls._connectionandcls._connection.dbName)orNone00490050ifgetattr(cls.sqlmeta,'table',None):0051cls.sqlmeta.alias=cls.sqlmeta.table0052else:0053cls.sqlmeta.alias=cls.sqlmeta.style.pythonClassToDBTable(cls.__name__)0054alias=cls.sqlmeta.alias0055columns=[ColumnAS(cls.sqlmeta.idName,'id')]0056# {sqlrepr-key: [restriction, *aggregate-column]}0057aggregates={'':[None]}0058inverseColumns=dict([(y,x)forx,yincls.sqlmeta.columns.iteritems()])0059forcolincls.sqlmeta.columnList:0060n=inverseColumns[col]0061ascol=ColumnAS(col.dbName,n)0062ifisAggregate(col.dbName):0063restriction=getattr(col,'aggregateClause',None)0064ifrestriction:0065restrictkey=sqlrepr(restriction,dbName)0066aggregates[restrictkey]=aggregates.get(restrictkey,[restriction])+[ascol]0067else:0068aggregates[''].append(ascol)0069else:0070columns.append(ascol)00710072metajoin=getattr(cls.sqlmeta,'join',NoDefault)0073clause=getattr(cls.sqlmeta,'clause',NoDefault)0074select=Select(columns,0075distinct=True,0076# @@ LDO check if this really mattered for performance0077# @@ Postgres (and MySQL?) extension!0078#distinctOn=cls.sqlmeta.idName,0079join=metajoin,0080clause=clause)00810082aggregates=aggregates.values()0083#print cls.__name__, sqlrepr(aggregates, dbName)00840085ifaggregates!=[[None]]:0086join=[]0087last_alias="%s_base"%alias0088last_id="id"0089last=Alias(select,last_alias)0090columns=[ColumnAS(SQLConstant("%s.%s"%(last_alias,x.expr2)),x.expr2)forxincolumns]00910092fori,agginenumerate(aggregates):0093restriction=agg[0]0094ifrestrictionisNone:0095restriction=clause0096else:0097restriction=AND(clause,restriction)0098agg=agg[1:]0099agg_alias="%s_%s"%(alias,i)0100agg_id='%s_id'%agg_alias0101ifnotlast.q.alias.endswith('base'):0102last=None0103new_alias=Alias(Select([ColumnAS(cls.sqlmeta.idName,agg_id)]+agg,0104groupBy=cls.sqlmeta.idName,0105join=metajoin,0106clause=restriction),0107agg_alias)0108agg_join=LEFTJOINOn(last,0109new_alias,0110"%s.%s = %s.%s"%(last_alias,last_id,agg_alias,agg_id))01110112join.append(agg_join)0113forcolinagg:0114columns.append(ColumnAS(SQLConstant("%s.%s"%(agg_alias,col.expr2)),col.expr2))01150116last=new_alias0117last_alias=agg_alias0118last_id=agg_id0119select=Select(columns,0120join=join)01210122cls.sqlmeta.table=Alias(select,alias)0123cls.q=ViewSQLObjectTable(cls)0124forn,colincls.sqlmeta.columns.iteritems():0125col.dbName=n01260127defisAggregate(expr):0128ifisinstance(expr,SQLCall):0129returnTrue0130ifisinstance(expr,SQLOp):0131returnisAggregate(expr.expr1)orisAggregate(expr.expr2)0132returnFalse01330134######0135