0001from itertools import count
0002import classregistry
0003import events
0004import styles
0005import sqlbuilder
0006from styles import capword
0007
0008__all__ = ['MultipleJoin', 'SQLMultipleJoin', 'RelatedJoin', 'SQLRelatedJoin',
0009           'SingleJoin', 'ManyToMany', 'OneToMany']
0010
0011creationOrder = count()
0012NoDefault = sqlbuilder.NoDefault
0013
0014def getID(obj):
0015    try:
0016        return obj.id
0017    except AttributeError:
0018        return int(obj)
0019
0020class Join(object):
0021
0022    def __init__(self, otherClass=None, **kw):
0023        kw['otherClass'] = otherClass
0024        self.kw = kw
0025        self._joinMethodName = self.kw.pop('joinMethodName', None)
0026        self.creationOrder = creationOrder.next()
0027
0028    def _set_joinMethodName(self, value):
0029        assert self._joinMethodName == value or self._joinMethodName is None, "You have already given an explicit joinMethodName (%s), and you are now setting it to %s" % (self._joinMethodName, value)
0030        self._joinMethodName = value
0031
0032    def _get_joinMethodName(self):
0033        return self._joinMethodName
0034
0035    joinMethodName = property(_get_joinMethodName, _set_joinMethodName)
0036    name = joinMethodName
0037
0038    def withClass(self, soClass):
0039        if 'joinMethodName' in self.kw:
0040            self._joinMethodName = self.kw['joinMethodName']
0041            del self.kw['joinMethodName']
0042        return self.baseClass(creationOrder=self.creationOrder,
0043                              soClass=soClass,
0044                              joinDef=self,
0045                              joinMethodName=self._joinMethodName,
0046                              **self.kw)
0047
0048# A join is separate from a foreign key, i.e., it is
0049# many-to-many, or one-to-many where the *other* class
0050# has the foreign key.
0051class SOJoin(object):
0052
0053    def __init__(self,
0054                 creationOrder,
0055                 soClass=None,
0056                 otherClass=None,
0057                 joinColumn=None,
0058                 joinMethodName=None,
0059                 orderBy=NoDefault,
0060                 joinDef=None):
0061        self.creationOrder = creationOrder
0062        self.soClass = soClass
0063        self.joinDef = joinDef
0064        self.otherClassName = otherClass
0065        classregistry.registry(soClass.sqlmeta.registry).addClassCallback(
0066            otherClass, self._setOtherClass)
0067        self.joinColumn = joinColumn
0068        self.joinMethodName = joinMethodName
0069        self._orderBy = orderBy
0070        if not self.joinColumn:
0071            # Here we set up the basic join, which is
0072            # one-to-many, where the other class points to
0073            # us.
0074            self.joinColumn = styles.getStyle(
0075                self.soClass).tableReference(self.soClass.sqlmeta.table)
0076
0077    def orderBy(self):
0078        if self._orderBy is NoDefault:
0079            self._orderBy = self.otherClass.sqlmeta.defaultOrder
0080        return self._orderBy
0081    orderBy = property(orderBy)
0082
0083    def _setOtherClass(self, cls):
0084        self.otherClass = cls
0085
0086    def hasIntermediateTable(self):
0087        return False
0088
0089    def _applyOrderBy(self, results, defaultSortClass):
0090        if self.orderBy is not None:
0091            results.sort(sorter(self.orderBy))
0092        return results
0093
0094def sorter(orderBy):
0095    if isinstance(orderBy, (tuple, list)):
0096        if len(orderBy) == 1:
0097            orderBy = orderBy[0]
0098        else:
0099            fhead = sorter(orderBy[0])
0100            frest = sorter(orderBy[1:])
0101            return lambda a, b, fhead=fhead, frest=frest: fhead(a, b) or frest(a, b)
0102    if isinstance(orderBy, sqlbuilder.DESC)          and isinstance(orderBy.expr, sqlbuilder.SQLObjectField):
0104        orderBy = '-' + orderBy.expr.original
0105    elif isinstance(orderBy, sqlbuilder.SQLObjectField):
0106        orderBy = orderBy.original
0107    # @@: but we don't handle more complex expressions for orderings
0108    if orderBy.startswith('-'):
0109        orderBy = orderBy[1:]
0110        reverse = True
0111    else:
0112        reverse = False
0113
0114    def cmper(a, b, attr=orderBy, rev=reverse):
0115        a = getattr(a, attr)
0116        b = getattr(b, attr)
0117        if rev:
0118            a, b = b, a
0119        if a is None:
0120            if b is None:
0121                return 0
0122            return -1
0123        if b is None:
0124            return 1
0125        return cmp(a, b)
0126    return cmper
0127
0128# This is a one-to-many
0129class SOMultipleJoin(SOJoin):
0130
0131    def __init__(self, addRemoveName=None, **kw):
0132        # addRemovePrefix is something like @@
0133        SOJoin.__init__(self, **kw)
0134
0135        # Here we generate the method names
0136        if not self.joinMethodName:
0137            name = self.otherClassName[0].lower() + self.otherClassName[1:]
0138            if name.endswith('s'):
0139                name = name + "es"
0140            else:
0141                name = name + "s"
0142            self.joinMethodName = name
0143        if addRemoveName:
0144            self.addRemoveName = addRemoveName
0145        else:
0146            self.addRemoveName = capword(self.otherClassName)
0147
0148    def performJoin(self, inst):
0149        ids = inst._connection._SO_selectJoin(
0150            self.otherClass,
0151            self.joinColumn,
0152            inst.id)
0153        if inst.sqlmeta._perConnection:
0154            conn = inst._connection
0155        else:
0156            conn = None
0157        return self._applyOrderBy([self.otherClass.get(id, conn) for (id,) in ids if id is not None], self.otherClass)
0158
0159    def _dbNameToPythonName(self):
0160        for column in self.otherClass.sqlmeta.columns.values():
0161            if column.dbName == self.joinColumn:
0162                return column.name
0163        return self.soClass.sqlmeta.style.dbColumnToPythonAttr(self.joinColumn)
0164
0165class MultipleJoin(Join):
0166    baseClass = SOMultipleJoin
0167
0168class SOSQLMultipleJoin(SOMultipleJoin):
0169
0170    def performJoin(self, inst):
0171        if inst.sqlmeta._perConnection:
0172            conn = inst._connection
0173        else:
0174            conn = None
0175        pythonColumn = self._dbNameToPythonName()
0176        results = self.otherClass.select(getattr(self.otherClass.q, pythonColumn) == inst.id, connection=conn)
0177        return results.orderBy(self.orderBy)
0178
0179class SQLMultipleJoin(Join):
0180    baseClass = SOSQLMultipleJoin
0181
0182# This is a many-to-many join, with an intermediary table
0183class SORelatedJoin(SOMultipleJoin):
0184
0185    def __init__(self,
0186                 otherColumn=None,
0187                 intermediateTable=None,
0188                 createRelatedTable=True,
0189                 **kw):
0190        self.intermediateTable = intermediateTable
0191        self.otherColumn = otherColumn
0192        self.createRelatedTable = createRelatedTable
0193        SOMultipleJoin.__init__(self, **kw)
0194        classregistry.registry(
0195            self.soClass.sqlmeta.registry).addClassCallback(
0196            self.otherClassName, self._setOtherRelatedClass)
0197
0198    def _setOtherRelatedClass(self, otherClass):
0199        if not self.intermediateTable:
0200            names = [self.soClass.sqlmeta.table,
0201                     otherClass.sqlmeta.table]
0202            names.sort()
0203            self.intermediateTable = '%s_%s' % (names[0], names[1])
0204        if not self.otherColumn:
0205            self.otherColumn = self.soClass.sqlmeta.style.tableReference(
0206                otherClass.sqlmeta.table)
0207
0208
0209    def hasIntermediateTable(self):
0210        return True
0211
0212    def performJoin(self, inst):
0213        ids = inst._connection._SO_intermediateJoin(
0214            self.intermediateTable,
0215            self.otherColumn,
0216            self.joinColumn,
0217            inst.id)
0218        if inst.sqlmeta._perConnection:
0219            conn = inst._connection
0220        else:
0221            conn = None
0222        return self._applyOrderBy([self.otherClass.get(id, conn) for (id,) in ids if id is not None], self.otherClass)
0223
0224    def remove(self, inst, other):
0225        inst._connection._SO_intermediateDelete(
0226            self.intermediateTable,
0227            self.joinColumn,
0228            getID(inst),
0229            self.otherColumn,
0230            getID(other))
0231
0232    def add(self, inst, other):
0233        inst._connection._SO_intermediateInsert(
0234            self.intermediateTable,
0235            self.joinColumn,
0236            getID(inst),
0237            self.otherColumn,
0238            getID(other))
0239
0240class RelatedJoin(MultipleJoin):
0241    baseClass = SORelatedJoin
0242
0243# helper classes to SQLRelatedJoin
0244class OtherTableToJoin(sqlbuilder.SQLExpression):
0245    def __init__(self, otherTable, otherIdName, interTable, joinColumn):
0246        self.otherTable = otherTable
0247        self.otherIdName = otherIdName
0248        self.interTable = interTable
0249        self.joinColumn = joinColumn
0250
0251    def tablesUsedImmediate(self):
0252        return [self.otherTable, self.interTable]
0253
0254    def __sqlrepr__(self, db):
0255        return '%s.%s = %s.%s' % (self.otherTable, self.otherIdName, self.interTable, self.joinColumn)
0256
0257class JoinToTable(sqlbuilder.SQLExpression):
0258    def __init__(self, table, idName, interTable, joinColumn):
0259        self.table = table
0260        self.idName = idName
0261        self.interTable = interTable
0262        self.joinColumn = joinColumn
0263
0264    def tablesUsedImmediate(self):
0265        return [self.table, self.interTable]
0266
0267    def __sqlrepr__(self, db):
0268        return '%s.%s = %s.%s' % (self.interTable, self.joinColumn, self.table, self.idName)
0269
0270class TableToId(sqlbuilder.SQLExpression):
0271    def __init__(self, table, idName, idValue):
0272        self.table = table
0273        self.idName = idName
0274        self.idValue = idValue
0275
0276    def tablesUsedImmediate(self):
0277        return [self.table]
0278
0279    def __sqlrepr__(self, db):
0280        return '%s.%s = %s' % (self.table, self.idName, self.idValue)
0281
0282class SOSQLRelatedJoin(SORelatedJoin):
0283    def performJoin(self, inst):
0284        if inst.sqlmeta._perConnection:
0285            conn = inst._connection
0286        else:
0287            conn = None
0288        results = self.otherClass.select(sqlbuilder.AND(
0289            OtherTableToJoin(
0290                self.otherClass.sqlmeta.table, self.otherClass.sqlmeta.idName,
0291                self.intermediateTable, self.otherColumn
0292            ),
0293            JoinToTable(
0294                self.soClass.sqlmeta.table, self.soClass.sqlmeta.idName,
0295                self.intermediateTable, self.joinColumn
0296            ),
0297            TableToId(self.soClass.sqlmeta.table, self.soClass.sqlmeta.idName, inst.id),
0298        ), clauseTables=(self.soClass.sqlmeta.table, self.otherClass.sqlmeta.table, self.intermediateTable),
0299        connection=conn)
0300        return results.orderBy(self.orderBy)
0301
0302class SQLRelatedJoin(RelatedJoin):
0303    baseClass = SOSQLRelatedJoin
0304
0305class SOSingleJoin(SOMultipleJoin):
0306
0307    def __init__(self, **kw):
0308        self.makeDefault = kw.pop('makeDefault', False)
0309        SOMultipleJoin.__init__(self, **kw)
0310
0311    def performJoin(self, inst):
0312        if inst.sqlmeta._perConnection:
0313            conn = inst._connection
0314        else:
0315            conn = None
0316        pythonColumn = self._dbNameToPythonName()
0317        results = self.otherClass.select(
0318            getattr(self.otherClass.q, pythonColumn) == inst.id,
0319            connection=conn
0320        )
0321        if results.count() == 0:
0322            if not self.makeDefault:
0323                return None
0324            else:
0325                kw = {self.soClass.sqlmeta.style.instanceIDAttrToAttr(pythonColumn): inst}
0326                return self.otherClass(**kw) # instanciating the otherClass with all
0327        else:
0328            return results[0]
0329
0330class SingleJoin(Join):
0331    baseClass = SOSingleJoin
0332
0333
0334
0335import boundattributes
0336
0337class SOManyToMany(object):
0338
0339    def __init__(self, soClass, name, join,
0340                 intermediateTable, joinColumn, otherColumn,
0341                 createJoinTable, **attrs):
0342        self.name = name
0343        self.intermediateTable = intermediateTable
0344        self.joinColumn = joinColumn
0345        self.otherColumn = otherColumn
0346        self.createJoinTable = createJoinTable
0347        self.soClass = self.otherClass = None
0348        for name, value in attrs.items():
0349            setattr(self, name, value)
0350        classregistry.registry(
0351            soClass.sqlmeta.registry).addClassCallback(
0352            join, self._setOtherClass)
0353        classregistry.registry(
0354            soClass.sqlmeta.registry).addClassCallback(
0355            soClass.__name__, self._setThisClass)
0356
0357    def _setThisClass(self, soClass):
0358        self.soClass = soClass
0359        if self.soClass and self.otherClass:
0360            self._finishSet()
0361
0362    def _setOtherClass(self, otherClass):
0363        self.otherClass = otherClass
0364        if self.soClass and self.otherClass:
0365            self._finishSet()
0366
0367    def _finishSet(self):
0368        if self.intermediateTable is None:
0369            names = [self.soClass.sqlmeta.table,
0370                     self.otherClass.sqlmeta.table]
0371            names.sort()
0372            self.intermediateTable = '%s_%s' % (names[0], names[1])
0373        if not self.otherColumn:
0374            self.otherColumn = self.soClass.sqlmeta.style.tableReference(
0375                self.otherClass.sqlmeta.table)
0376        if not self.joinColumn:
0377            self.joinColumn = styles.getStyle(
0378                self.soClass).tableReference(self.soClass.sqlmeta.table)
0379        events.listen(self.event_CreateTableSignal,
0380                      self.soClass, events.CreateTableSignal)
0381        events.listen(self.event_CreateTableSignal,
0382                      self.otherClass, events.CreateTableSignal)
0383        self.clause = (
0384            (self.otherClass.q.id ==
0385             sqlbuilder.Field(self.intermediateTable, self.otherColumn))
0386            & (sqlbuilder.Field(self.intermediateTable, self.joinColumn)
0387               == self.soClass.q.id))
0388
0389    def __get__(self, obj, type):
0390        if obj is None:
0391            return self
0392        query = (
0393            (self.otherClass.q.id ==
0394             sqlbuilder.Field(self.intermediateTable, self.otherColumn))
0395            & (sqlbuilder.Field(self.intermediateTable, self.joinColumn)
0396               == obj.id))
0397        select = self.otherClass.select(query)
0398        return _ManyToManySelectWrapper(obj, self, select)
0399
0400    def event_CreateTableSignal(self, soClass, connection, extra_sql,
0401                                post_funcs):
0402        if self.createJoinTable:
0403            post_funcs.append(self.event_CreateTableSignalPost)
0404
0405    def event_CreateTableSignalPost(self, soClass, connection):
0406        if connection.tableExists(self.intermediateTable):
0407            return
0408        connection._SO_createJoinTable(self)
0409
0410class ManyToMany(boundattributes.BoundFactory):
0411    factory_class = SOManyToMany
0412    __restrict_attributes__ = (
0413        'join', 'intermediateTable',
0414        'joinColumn', 'otherColumn', 'createJoinTable')
0415    __unpackargs__ = ('join',)
0416
0417    # Default values:
0418    intermediateTable = None
0419    joinColumn = None
0420    otherColumn = None
0421    createJoinTable = True
0422
0423class _ManyToManySelectWrapper(object):
0424
0425    def __init__(self, forObject, join, select):
0426        self.forObject = forObject
0427        self.join = join
0428        self.select = select
0429
0430    def __getattr__(self, attr):
0431        # @@: This passes through private variable access too... should it?
0432        # Also magic methods, like __str__
0433        return getattr(self.select, attr)
0434
0435    def __repr__(self):
0436        return '<%s for: %s>' % (self.__class__.__name__, repr(self.select))
0437
0438    def __str__(self):
0439        return str(self.select)
0440
0441    def __iter__(self):
0442        return iter(self.select)
0443
0444    def __getitem__(self, key):
0445        return self.select[key]
0446
0447    def add(self, obj):
0448        obj._connection._SO_intermediateInsert(
0449            self.join.intermediateTable,
0450            self.join.joinColumn,
0451            getID(self.forObject),
0452            self.join.otherColumn,
0453            getID(obj))
0454
0455    def remove(self, obj):
0456        obj._connection._SO_intermediateDelete(
0457            self.join.intermediateTable,
0458            self.join.joinColumn,
0459            getID(self.forObject),
0460            self.join.otherColumn,
0461            getID(obj))
0462
0463    def create(self, **kw):
0464        obj = self.join.otherClass(**kw)
0465        self.add(obj)
0466        return obj
0467
0468class SOOneToMany(object):
0469
0470    def __init__(self, soClass, name, join, joinColumn, **attrs):
0471        self.soClass = soClass
0472        self.name = name
0473        self.joinColumn = joinColumn
0474        for name, value in attrs.items():
0475            setattr(self, name, value)
0476        classregistry.registry(
0477            soClass.sqlmeta.registry).addClassCallback(
0478            join, self._setOtherClass)
0479
0480    def _setOtherClass(self, otherClass):
0481        self.otherClass = otherClass
0482        if not self.joinColumn:
0483            self.joinColumn = styles.getStyle(
0484                self.soClass).tableReference(self.soClass.sqlmeta.table)
0485        self.clause = (
0486            sqlbuilder.Field(self.otherClass.sqlmeta.table, self.joinColumn)
0487            == self.soClass.q.id)
0488
0489    def __get__(self, obj, type):
0490        if obj is None:
0491            return self
0492        query = (
0493            sqlbuilder.Field(self.otherClass.sqlmeta.table, self.joinColumn)
0494            == obj.id)
0495        select = self.otherClass.select(query)
0496        return _OneToManySelectWrapper(obj, self, select)
0497
0498class OneToMany(boundattributes.BoundFactory):
0499    factory_class = SOOneToMany
0500    __restrict_attributes__ = (
0501        'join', 'joinColumn')
0502    __unpackargs__ = ('join',)
0503
0504    # Default values:
0505    joinColumn = None
0506
0507class _OneToManySelectWrapper(object):
0508
0509    def __init__(self, forObject, join, select):
0510        self.forObject = forObject
0511        self.join = join
0512        self.select = select
0513
0514    def __getattr__(self, attr):
0515        # @@: This passes through private variable access too... should it?
0516        # Also magic methods, like __str__
0517        return getattr(self.select, attr)
0518
0519    def __repr__(self):
0520        return '<%s for: %s>' % (self.__class__.__name__, repr(self.select))
0521
0522    def __str__(self):
0523        return str(self.select)
0524
0525    def __iter__(self):
0526        return iter(self.select)
0527
0528    def __getitem__(self, key):
0529        return self.select[key]
0530
0531    def create(self, **kw):
0532        kw[self.join.joinColumn] = self.forObject.id
0533        return self.join.otherClass(**kw)