0001"""
0002sqlobject.sqlbuilder
0003--------------------
0004
0005:author: Ian Bicking <ianb@colorstudy.com>
0006
0007Builds SQL expressions from normal Python expressions.
0008
0009Disclaimer
0010----------
0011
0012This program is free software; you can redistribute it and/or modify
0013it under the terms of the GNU Lesser General Public License as
0014published by the Free Software Foundation; either version 2.1 of the
0015License, or (at your option any later version.
0016
0017This program is distributed in the hope that it will be useful,
0018but WITHOUT ANY WARRANTY; without even the implied warranty of
0019MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
0020GNU General Public License for more details.
0021
0022You should have received a copy of the GNU Lesser General Public
0023License along with this program; if not, write to the Free Software
0024Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301,
0025USA.
0026
0027Instructions
0028------------
0029
0030To begin a SQL expression, you must use some sort of SQL object -- a
0031field, table, or SQL statement (``SELECT``, ``INSERT``, etc.)  You can
0032then use normal operators, with the exception of: `and`, `or`, `not`,
0033and `in`.  You can use the `AND`, `OR`, `NOT`, and `IN` functions
0034instead, or you can also use `&`, `|`, and `~` for `and`, `or`, and
0035`not` respectively (however -- the precidence for these operators
0036doesn't work as you would want, so you must use many parenthesis).
0037
0038To create a sql field, table, or constant/function, use the namespaces
0039`table`, `const`, and `func`.  For instance, ``table.address`` refers
0040to the ``address`` table, and ``table.address.state`` refers to the
0041``state`` field in the address table.  ``const.NULL`` is the ``NULL``
0042SQL constant, and ``func.NOW()`` is the ``NOW()`` function call
0043(`const` and `func` are actually identicle, but the two names are
0044provided for clarity).  Once you create this object, expressions
0045formed with it will produce SQL statements.
0046
0047The ``sqlrepr(obj)`` function gets the SQL representation of these
0048objects, as well as the proper SQL representation of basic Python
0049types (None==NULL).
0050
0051There are a number of DB-specific SQL features that this does not
0052implement.  There are a bunch of normal ANSI features also not present.
0053
0054See the bottom of this module for some examples, and run it (i.e.
0055``python sql.py``) to see the results of those examples.
0056
0057"""
0058
0059########################################
0060# Constants
0061########################################
0062
0063import fnmatch
0064import operator
0065import re
0066import threading
0067import types
0068import weakref
0069import sys
0070
0071from . import classregistry
0072from .converters import registerConverter, sqlrepr, quote_str, unquote_str
0073from .compat import string_type
0074
0075
0076class VersionError(Exception):
0077    pass
0078
0079
0080class NoDefault:
0081    pass
0082
0083
0084class SQLObjectState(object):
0085    def __init__(self, soObject, connection=None):
0086        self.soObject = weakref.proxy(soObject)
0087        self.connection = connection
0088
0089
0090safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
0091
0092
0093def sqlIdentifier(obj):
0094    # some db drivers return unicode column names
0095    return isinstance(obj, string_type) and bool(safeSQLRE.search(obj.strip()))
0096
0097
0098def execute(expr, executor):
0099    if hasattr(expr, 'execute'):
0100        return expr.execute(executor)
0101    else:
0102        return expr
0103
0104
0105def _str_or_sqlrepr(expr, db):
0106    if isinstance(expr, string_type):
0107        return expr
0108    return sqlrepr(expr, db)
0109
0110
0111########################################
0112# Expression generation
0113########################################
0114
0115
0116class SQLExpression:
0117    def __add__(self, other):
0118        return SQLOp("+", self, other)
0119
0120    def __radd__(self, other):
0121        return SQLOp("+", other, self)
0122
0123    def __sub__(self, other):
0124        return SQLOp("-", self, other)
0125
0126    def __rsub__(self, other):
0127        return SQLOp("-", other, self)
0128
0129    def __mul__(self, other):
0130        return SQLOp("*", self, other)
0131
0132    def __rmul__(self, other):
0133        return SQLOp("*", other, self)
0134
0135    def __div__(self, other):
0136        return SQLOp("/", self, other)
0137
0138    def __rdiv__(self, other):
0139        return SQLOp("/", other, self)
0140
0141    def __pos__(self):
0142        return SQLPrefix("+", self)
0143
0144    def __neg__(self):
0145        return SQLPrefix("-", self)
0146
0147    def __pow__(self, other):
0148        return SQLConstant("POW")(self, other)
0149
0150    def __rpow__(self, other):
0151        return SQLConstant("POW")(other, self)
0152
0153    def __abs__(self):
0154        return SQLConstant("ABS")(self)
0155
0156    def __mod__(self, other):
0157        return SQLModulo(self, other)
0158
0159    def __rmod__(self, other):
0160        return SQLConstant("MOD")(other, self)
0161
0162    def __lt__(self, other):
0163        return SQLOp("<", self, other)
0164
0165    def __le__(self, other):
0166        return SQLOp("<=", self, other)
0167
0168    def __gt__(self, other):
0169        return SQLOp(">", self, other)
0170
0171    def __ge__(self, other):
0172        return SQLOp(">=", self, other)
0173
0174    def __eq__(self, other):
0175        if other is None:
0176            return ISNULL(self)
0177        else:
0178            return SQLOp("=", self, other)
0179
0180    def __ne__(self, other):
0181        if other is None:
0182            return ISNOTNULL(self)
0183        else:
0184            return SQLOp("<>", self, other)
0185
0186    def __and__(self, other):
0187        return SQLOp("AND", self, other)
0188
0189    def __rand__(self, other):
0190        return SQLOp("AND", other, self)
0191
0192    def __or__(self, other):
0193        return SQLOp("OR", self, other)
0194
0195    def __ror__(self, other):
0196        return SQLOp("OR", other, self)
0197
0198    def __invert__(self):
0199        return SQLPrefix("NOT", self)
0200
0201    def __call__(self, *args):
0202        return SQLCall(self, args)
0203
0204    def __repr__(self):
0205        try:
0206            return self.__sqlrepr__(None)
0207        except AssertionError:
0208            return '<%s %s>' % (
0209                self.__class__.__name__, hex(id(self))[2:])
0210
0211    def __str__(self):
0212        return repr(self)
0213
0214    def __cmp__(self, other):
0215        raise VersionError("Python 2.1+ required")
0216
0217    def __rcmp__(self, other):
0218        raise VersionError("Python 2.1+ required")
0219
0220    def startswith(self, s):
0221        return STARTSWITH(self, s)
0222
0223    def endswith(self, s):
0224        return ENDSWITH(self, s)
0225
0226    def contains(self, s):
0227        return CONTAINSSTRING(self, s)
0228
0229    def components(self):
0230        return []
0231
0232    def tablesUsed(self, db):
0233        return self.tablesUsedSet(db)
0234
0235    def tablesUsedSet(self, db):
0236        tables = set()
0237        for table in self.tablesUsedImmediate():
0238            if hasattr(table, '__sqlrepr__'):
0239                table = sqlrepr(table, db)
0240            tables.add(table)
0241        for component in self.components():
0242            tables.update(tablesUsedSet(component, db))
0243        return tables
0244
0245    def tablesUsedImmediate(self):
0246        return []
0247
0248
0249#######################################
0250# Converter for SQLExpression instances
0251#######################################
0252
0253
0254def SQLExprConverter(value, db):
0255    return value.__sqlrepr__()
0256
0257registerConverter(SQLExpression, SQLExprConverter)
0258
0259
0260def tablesUsedSet(obj, db):
0261    if hasattr(obj, "tablesUsedSet"):
0262        return obj.tablesUsedSet(db)
0263    else:
0264        return {}
0265
0266
0267if sys.version_info[0] < 3:
0268    div = operator.div
0269else:
0270    div = operator.truediv
0271
0272
0273operatorMap = {
0274    "+": operator.add,
0275    "/": div,
0276    "-": operator.sub,
0277    "*": operator.mul,
0278    "<": operator.lt,
0279    "<=": operator.le,
0280    "=": operator.eq,
0281    "!=": operator.ne,
0282    ">=": operator.ge,
0283    ">": operator.gt,
0284    "IN": operator.contains,
0285    "IS": operator.eq,
0286}
0287
0288
0289class SQLOp(SQLExpression):
0290    def __init__(self, op, expr1, expr2):
0291        self.op = op.upper()
0292        self.expr1 = expr1
0293        self.expr2 = expr2
0294
0295    def __sqlrepr__(self, db):
0296        s1 = sqlrepr(self.expr1, db)
0297        s2 = sqlrepr(self.expr2, db)
0298        if s1[0] != '(' and s1 != 'NULL':
0299            s1 = '(' + s1 + ')'
0300        if s2[0] != '(' and s2 != 'NULL':
0301            s2 = '(' + s2 + ')'
0302        return "(%s %s %s)" % (s1, self.op, s2)
0303
0304    def components(self):
0305        return [self.expr1, self.expr2]
0306
0307    def execute(self, executor):
0308        if self.op == "AND":
0309            return execute(self.expr1, executor)                   and execute(self.expr2, executor)
0311        elif self.op == "OR":
0312            return execute(self.expr1, executor)                   or execute(self.expr2, executor)
0314        else:
0315            return operatorMap[self.op.upper()](execute(self.expr1, executor),
0316                                                execute(self.expr2, executor))
0317
0318registerConverter(SQLOp, SQLExprConverter)
0319
0320
0321class SQLModulo(SQLOp):
0322    def __init__(self, expr1, expr2):
0323        SQLOp.__init__(self, '%', expr1, expr2)
0324
0325    def __sqlrepr__(self, db):
0326        if db == 'sqlite':
0327            return SQLOp.__sqlrepr__(self, db)
0328        s1 = sqlrepr(self.expr1, db)
0329        s2 = sqlrepr(self.expr2, db)
0330        return "MOD(%s, %s)" % (s1, s2)
0331
0332registerConverter(SQLModulo, SQLExprConverter)
0333
0334
0335class SQLCall(SQLExpression):
0336    def __init__(self, expr, args):
0337        self.expr = expr
0338        self.args = args
0339
0340    def __sqlrepr__(self, db):
0341        return "%s%s" % (sqlrepr(self.expr, db), sqlrepr(self.args, db))
0342
0343    def components(self):
0344        return [self.expr] + list(self.args)
0345
0346    def execute(self, executor):
0347        raise ValueError("I don't yet know how to locally execute functions")
0348
0349registerConverter(SQLCall, SQLExprConverter)
0350
0351
0352class SQLPrefix(SQLExpression):
0353    def __init__(self, prefix, expr):
0354        self.prefix = prefix
0355        self.expr = expr
0356
0357    def __sqlrepr__(self, db):
0358        return "%s %s" % (self.prefix, sqlrepr(self.expr, db))
0359
0360    def components(self):
0361        return [self.expr]
0362
0363    def execute(self, executor):
0364        prefix = self.prefix
0365        expr = execute(self.expr, executor)
0366        if prefix == "+":
0367            return expr
0368        elif prefix == "-":
0369            return -expr
0370        elif prefix.upper() == "NOT":
0371            return not expr
0372
0373registerConverter(SQLPrefix, SQLExprConverter)
0374
0375
0376class SQLConstant(SQLExpression):
0377    def __init__(self, const):
0378        self.const = const
0379
0380    def __sqlrepr__(self, db):
0381        return self.const
0382
0383    def execute(self, executor):
0384        raise ValueError("I don't yet know how to execute SQL constants")
0385
0386registerConverter(SQLConstant, SQLExprConverter)
0387
0388
0389class SQLTrueClauseClass(SQLExpression):
0390    def __sqlrepr__(self, db):
0391        return "1 = 1"
0392
0393    def execute(self, executor):
0394        return 1
0395
0396SQLTrueClause = SQLTrueClauseClass()
0397
0398registerConverter(SQLTrueClauseClass, SQLExprConverter)
0399
0400########################################
0401# Namespaces
0402########################################
0403
0404
0405class Field(SQLExpression):
0406    def __init__(self, tableName, fieldName):
0407        self.tableName = tableName
0408        self.fieldName = fieldName
0409
0410    def __sqlrepr__(self, db):
0411        return self.tableName + "." + self.fieldName
0412
0413    def tablesUsedImmediate(self):
0414        return [self.tableName]
0415
0416    def execute(self, executor):
0417        return executor.field(self.tableName, self.fieldName)
0418
0419
0420class SQLObjectField(Field):
0421    def __init__(self, tableName, fieldName, original, soClass, column):
0422        Field.__init__(self, tableName, fieldName)
0423        self.original = original
0424        self.soClass = soClass
0425        self.column = column
0426
0427    def _from_python(self, value):
0428        column = self.column
0429        if not isinstance(value, SQLExpression) and                   column and column.from_python:
0431            value = column.from_python(value, SQLObjectState(self.soClass))
0432        return value
0433
0434    def __eq__(self, other):
0435        if other is None:
0436            return ISNULL(self)
0437        other = self._from_python(other)
0438        return SQLOp('=', self, other)
0439
0440    def __ne__(self, other):
0441        if other is None:
0442            return ISNOTNULL(self)
0443        other = self._from_python(other)
0444        return SQLOp('<>', self, other)
0445
0446    def startswith(self, s):
0447        s = self._from_python(s)
0448        return STARTSWITH(self, s)
0449
0450    def endswith(self, s):
0451        s = self._from_python(s)
0452        return ENDSWITH(self, s)
0453
0454    def contains(self, s):
0455        s = self._from_python(s)
0456        return CONTAINSSTRING(self, s)
0457
0458registerConverter(SQLObjectField, SQLExprConverter)
0459
0460
0461class Table(SQLExpression):
0462    FieldClass = Field
0463
0464    def __init__(self, tableName):
0465        self.tableName = tableName
0466
0467    def __getattr__(self, attr):
0468        if attr.startswith('__'):
0469            raise AttributeError
0470        return self.FieldClass(self.tableName, attr)
0471
0472    def __sqlrepr__(self, db):
0473        return _str_or_sqlrepr(self.tableName, db)
0474
0475    def execute(self, executor):
0476        raise ValueError("Tables don't have values")
0477
0478
0479class SQLObjectTable(Table):
0480    FieldClass = SQLObjectField
0481
0482    def __init__(self, soClass):
0483        self.soClass = soClass
0484        assert soClass.sqlmeta.table, (
0485            "Bad table name in class %r: %r"
0486            % (soClass, soClass.sqlmeta.table))
0487        Table.__init__(self, soClass.sqlmeta.table)
0488
0489    def __getattr__(self, attr):
0490        if attr.startswith('__'):
0491            raise AttributeError
0492        if attr == 'id':
0493            return self._getattrFromID(attr)
0494        elif attr in self.soClass.sqlmeta.columns:
0495            column = self.soClass.sqlmeta.columns[attr]
0496            return self._getattrFromColumn(column, attr)
0497        elif attr + 'ID' in               [k for (k, v) in self.soClass.sqlmeta.columns.items()
0499                if v.foreignKey]:
0500            attr += 'ID'
0501            column = self.soClass.sqlmeta.columns[attr]
0502            return self._getattrFromColumn(column, attr)
0503        else:
0504            raise AttributeError(
0505                "%s instance has no attribute '%s'" % (self.soClass.__name__,
0506                                                       attr))
0507
0508    def _getattrFromID(self, attr):
0509        return self.FieldClass(self.tableName, self.soClass.sqlmeta.idName,
0510                               attr, self.soClass, None)
0511
0512    def _getattrFromColumn(self, column, attr):
0513        return self.FieldClass(self.tableName, column.dbName, attr,
0514                               self.soClass, column)
0515
0516
0517class SQLObjectTableWithJoins(SQLObjectTable):
0518
0519    def __getattr__(self, attr):
0520        if attr + 'ID' in               [k for (k, v) in self.soClass.sqlmeta.columns.items()
0522                if v.foreignKey]:
0523            column = self.soClass.sqlmeta.columns[attr + 'ID']
0524            return self._getattrFromForeignKey(column, attr)
0525        elif attr in [x.joinMethodName for x in self.soClass.sqlmeta.joins]:
0526            join = [x for x in self.soClass.sqlmeta.joins
0527                    if x.joinMethodName == attr][0]
0528            return self._getattrFromJoin(join, attr)
0529        else:
0530            return SQLObjectTable.__getattr__(self, attr)
0531
0532    def _getattrFromForeignKey(self, column, attr):
0533        ret = getattr(self, column.name) ==               getattr(self.soClass, '_SO_class_' + column.foreignKey).q.id
0535        return ret
0536
0537    def _getattrFromJoin(self, join, attr):
0538        if hasattr(join, 'otherColumn'):
0539            return AND(
0540                join.otherClass.q.id == Field(join.intermediateTable,
0541                                              join.otherColumn),
0542                Field(join.intermediateTable,
0543                      join.joinColumn) == self.soClass.q.id)
0544        else:
0545            return getattr(join.otherClass.q, join.joinColumn) ==                   self.soClass.q.id
0547
0548
0549class TableSpace:
0550    TableClass = Table
0551
0552    def __getattr__(self, attr):
0553        if attr.startswith('__'):
0554            raise AttributeError
0555        return self.TableClass(attr)
0556
0557
0558class ConstantSpace:
0559    def __getattr__(self, attr):
0560        if attr.startswith('__'):
0561            raise AttributeError
0562        return SQLConstant(attr)
0563
0564
0565########################################
0566# Table aliases
0567########################################
0568
0569class AliasField(Field):
0570    def __init__(self, tableName, fieldName, alias, aliasTable):
0571        Field.__init__(self, tableName, fieldName)
0572        self.alias = alias
0573        self.aliasTable = aliasTable
0574
0575    def __sqlrepr__(self, db):
0576        fieldName = self.fieldName
0577        if isinstance(fieldName, SQLExpression):
0578            fieldName = sqlrepr(fieldName, db)
0579        return self.alias + "." + fieldName
0580
0581    def tablesUsedImmediate(self):
0582        return [self.aliasTable]
0583
0584
0585class AliasTable(Table):
0586    as_string = ''  # set it to "AS" if your database requires it
0587    FieldClass = AliasField
0588
0589    _alias_lock = threading.Lock()
0590    _alias_counter = 0
0591
0592    def __init__(self, table, alias=None):
0593        if hasattr(table, "sqlmeta"):
0594            tableName = SQLConstant(table.sqlmeta.table)
0595        elif isinstance(table, (Select, Union)):
0596            assert alias is not None,                   "Alias name cannot be constructed from Select instances, "                   "please provide an 'alias' keyword."
0599            tableName = Subquery('', table)
0600            table = None
0601        else:
0602            tableName = SQLConstant(table)
0603            table = None
0604        Table.__init__(self, tableName)
0605        self.table = table
0606        if alias is None:
0607            self._alias_lock.acquire()
0608            try:
0609                AliasTable._alias_counter += 1
0610                alias = "%s_alias%d" % (tableName, AliasTable._alias_counter)
0611            finally:
0612                self._alias_lock.release()
0613        self.alias = alias
0614
0615    def __getattr__(self, attr):
0616        if attr.startswith('__'):
0617            raise AttributeError
0618        if self.table:
0619            attr = getattr(self.table.q, attr).fieldName
0620        return self.FieldClass(self.tableName, attr, self.alias, self)
0621
0622    def __sqlrepr__(self, db):
0623        return "%s %s %s" % (sqlrepr(self.tableName, db), self.as_string,
0624                             self.alias)
0625
0626
0627class Alias(SQLExpression):
0628    def __init__(self, table, alias=None):
0629        self.q = AliasTable(table, alias)
0630
0631    def __sqlrepr__(self, db):
0632        return sqlrepr(self.q, db)
0633
0634    def components(self):
0635        return [self.q]
0636
0637
0638class Union(SQLExpression):
0639    def __init__(self, *tables):
0640        tabs = []
0641        for t in tables:
0642            if not isinstance(t, SQLExpression) and hasattr(t, 'sqlmeta'):
0643                t = t.sqlmeta.table
0644                if isinstance(t, Alias):
0645                    t = t.q
0646                if isinstance(t, Table):
0647                    t = t.tableName
0648                if not isinstance(t, SQLExpression):
0649                    t = SQLConstant(t)
0650            tabs.append(t)
0651        self.tables = tabs
0652
0653    def __sqlrepr__(self, db):
0654        return " UNION ".join([str(sqlrepr(t, db)) for t in self.tables])
0655
0656########################################
0657# SQL Statements
0658########################################
0659
0660
0661class Select(SQLExpression):
0662    def __init__(self, items=NoDefault, where=NoDefault, groupBy=NoDefault,
0663                 having=NoDefault, orderBy=NoDefault, limit=NoDefault,
0664                 join=NoDefault, lazyColumns=False, distinct=False,
0665                 start=0, end=None, reversed=False, forUpdate=False,
0666                 clause=NoDefault, staticTables=NoDefault,
0667                 distinctOn=NoDefault):
0668        self.ops = {}
0669        if not isinstance(items, (list, tuple, types.GeneratorType)):
0670            items = [items]
0671        if clause is NoDefault and where is not NoDefault:
0672            clause = where
0673        if staticTables is NoDefault:
0674            staticTables = []
0675        self.ops['items'] = items
0676        self.ops['clause'] = clause
0677        self.ops['groupBy'] = groupBy
0678        self.ops['having'] = having
0679        self.ops['orderBy'] = orderBy
0680        self.ops['limit'] = limit
0681        self.ops['join'] = join
0682        self.ops['lazyColumns'] = lazyColumns
0683        self.ops['distinct'] = distinct
0684        self.ops['distinctOn'] = distinctOn
0685        self.ops['start'] = start
0686        self.ops['end'] = end
0687        self.ops['reversed'] = reversed
0688        self.ops['forUpdate'] = forUpdate
0689        self.ops['staticTables'] = staticTables
0690
0691    def clone(self, **newOps):
0692        ops = self.ops.copy()
0693        ops.update(newOps)
0694        return self.__class__(**ops)
0695
0696    def newItems(self, items):
0697        return self.clone(items=items)
0698
0699    def newClause(self, new_clause):
0700        return self.clone(clause=new_clause)
0701
0702    def orderBy(self, orderBy):
0703        return self.clone(orderBy=orderBy)
0704
0705    def unlimited(self):
0706        return self.clone(limit=NoDefault, start=0, end=None)
0707
0708    def limit(self, limit):
0709        self.clone(limit=limit)
0710
0711    def lazyColumns(self, value):
0712        return self.clone(lazyColumns=value)
0713
0714    def reversed(self):
0715        return self.clone(reversed=not self.ops.get('reversed', False))
0716
0717    def distinct(self):
0718        return self.clone(distinct=True)
0719
0720    def filter(self, filter_clause):
0721        if filter_clause is None:
0722            # None doesn't filter anything, it's just a no-op:
0723            return self
0724        clause = self.ops['clause']
0725        if isinstance(clause, string_type):
0726            clause = SQLConstant('(%s)' % clause)
0727        return self.newClause(AND(clause, filter_clause))
0728
0729    def __sqlrepr__(self, db):
0730
0731        select = "SELECT"
0732        if self.ops['distinct']:
0733            select += " DISTINCT"
0734            if self.ops['distinctOn'] is not NoDefault:
0735                select += " ON(%s)" % _str_or_sqlrepr(
0736                    self.ops['distinctOn'], db)
0737        if not self.ops['lazyColumns']:
0738            select += " %s" % ", ".join(
0739                [str(_str_or_sqlrepr(v, db)) for v in self.ops['items']])
0740        else:
0741            select += " %s" % _str_or_sqlrepr(self.ops['items'][0], db)
0742
0743        join = []
0744        join_str = ''
0745        if self.ops['join'] is not NoDefault and self.ops['join'] is not None:
0746            _join = self.ops['join']
0747            if isinstance(_join, str):
0748                join_str = " " + _join
0749            elif isinstance(_join, SQLJoin):
0750                join.append(_join)
0751            else:
0752                join.extend(_join)
0753        tables = set()
0754        for x in self.ops['staticTables']:
0755            if isinstance(x, SQLExpression):
0756                x = sqlrepr(x, db)
0757            tables.add(x)
0758        things = list(self.ops['items']) + join
0759        if self.ops['clause'] is not NoDefault:
0760            things.append(self.ops['clause'])
0761        for thing in things:
0762            if isinstance(thing, SQLExpression):
0763                tables.update(tablesUsedSet(thing, db))
0764        for j in join:
0765            t1 = _str_or_sqlrepr(j.table1, db)
0766            if t1 in tables:
0767                tables.remove(t1)
0768            t2 = _str_or_sqlrepr(j.table2, db)
0769            if t2 in tables:
0770                tables.remove(t2)
0771        if tables:
0772            select += " FROM %s" % ", ".join(sorted(tables))
0773        elif join:
0774            select += " FROM"
0775        tablesYet = tables
0776        for j in join:
0777            if tablesYet and j.table1:
0778                sep = ", "
0779            else:
0780                sep = " "
0781            select += sep + sqlrepr(j, db)
0782            tablesYet = True
0783
0784        if join_str:
0785            select += join_str
0786
0787        if self.ops['clause'] is not NoDefault:
0788            select += " WHERE %s" % _str_or_sqlrepr(self.ops['clause'], db)
0789        if self.ops['groupBy'] is not NoDefault:
0790            groupBy = _str_or_sqlrepr(self.ops['groupBy'], db)
0791            if isinstance(self.ops['groupBy'], (list, tuple)):
0792                groupBy = groupBy[1:-1]  # Remove parens
0793            select += " GROUP BY %s" % groupBy
0794        if self.ops['having'] is not NoDefault:
0795            select += " HAVING %s" % _str_or_sqlrepr(self.ops['having'], db)
0796        if self.ops['orderBy'] is not NoDefault and              self.ops['orderBy'] is not None:
0798            orderBy = self.ops['orderBy']
0799            if self.ops['reversed']:
0800                reverser = DESC
0801            else:
0802                def reverser(x):
0803                    return x
0804            if isinstance(orderBy, (list, tuple)):
0805                select += " ORDER BY %s" % ", ".join(
0806                    [_str_or_sqlrepr(reverser(_x), db) for _x in orderBy])
0807            else:
0808                select += " ORDER BY %s" % _str_or_sqlrepr(
0809                    reverser(orderBy), db)
0810        start, end = self.ops['start'], self.ops['end']
0811        if self.ops['limit'] is not NoDefault:
0812            end = start + self.ops['limit']
0813        if start or end:
0814            from .dbconnection import dbConnectionForScheme
0815            select = dbConnectionForScheme(db)._queryAddLimitOffset(select,
0816                                                                    start, end)
0817        if self.ops['forUpdate']:
0818            select += " FOR UPDATE"
0819        return select
0820
0821registerConverter(Select, SQLExprConverter)
0822
0823
0824class Insert(SQLExpression):
0825    def __init__(self, table, valueList=None, values=None, template=NoDefault):
0826        self.template = template
0827        self.table = table
0828        if valueList:
0829            if values:
0830                raise TypeError("You may only give valueList *or* values")
0831            self.valueList = valueList
0832        else:
0833            self.valueList = [values]
0834
0835    def __sqlrepr__(self, db):
0836        if not self.valueList:
0837            return ''
0838        insert = "INSERT INTO %s" % self.table
0839        allowNonDict = True
0840        template = self.template
0841        if (template is NoDefault) and isinstance(self.valueList[0], dict):
0842            template = list(sorted(self.valueList[0].keys()))
0843            allowNonDict = False
0844        if template is not NoDefault:
0845            insert += " (%s)" % ", ".join(template)
0846        insert += " VALUES "
0847        listToJoin = []
0848        listToJoin_app = listToJoin.append
0849        for value in self.valueList:
0850            if isinstance(value, dict):
0851                if template is NoDefault:
0852                    raise TypeError(
0853                        "You can't mix non-dictionaries with dictionaries "
0854                        "in an INSERT if you don't provide a template (%s)" %
0855                        repr(value))
0856                value = dictToList(template, value)
0857            elif not allowNonDict:
0858                raise TypeError(
0859                    "You can't mix non-dictionaries with dictionaries "
0860                    "in an INSERT if you don't provide a template (%s)" %
0861                    repr(value))
0862            listToJoin_app("(%s)" % ", ".join([sqlrepr(v, db) for v in value]))
0863        insert = "%s%s" % (insert, ", ".join(listToJoin))
0864        return insert
0865
0866registerConverter(Insert, SQLExprConverter)
0867
0868
0869def dictToList(template, dict):
0870    list = []
0871    for key in template:
0872        list.append(dict[key])
0873    if len(dict.keys()) > len(template):
0874        raise TypeError(
0875            "Extra entries in dictionary that aren't asked for in template "
0876            "(template=%s, dict=%s)" % (repr(template), repr(dict)))
0877    return list
0878
0879
0880class Update(SQLExpression):
0881    def __init__(self, table, values, template=NoDefault, where=NoDefault):
0882        self.table = table
0883        self.values = values
0884        self.template = template
0885        self.whereClause = where
0886
0887    def __sqlrepr__(self, db):
0888        update = "%s %s" % (self.sqlName(), self.table)
0889        update += " SET"
0890        first = True
0891        if self.template is not NoDefault:
0892            for i in range(len(self.template)):
0893                if first:
0894                    first = False
0895                else:
0896                    update += ","
0897                update += " %s=%s" % (self.template[i],
0898                                      sqlrepr(self.values[i], db))
0899        else:
0900            for key, value in sorted(self.values.items()):
0901                if first:
0902                    first = False
0903                else:
0904                    update += ","
0905                update += " %s=%s" % (key, sqlrepr(value, db))
0906        if self.whereClause is not NoDefault:
0907            update += " WHERE %s" % _str_or_sqlrepr(self.whereClause, db)
0908        return update
0909
0910    def sqlName(self):
0911        return "UPDATE"
0912
0913registerConverter(Update, SQLExprConverter)
0914
0915
0916class Delete(SQLExpression):
0917    """To be safe, this will signal an error if there is no where clause,
0918    unless you pass in where=None to the constructor."""
0919    def __init__(self, table, where=NoDefault):
0920        self.table = table
0921        if where is NoDefault:
0922            raise TypeError(
0923                "You must give a where clause or pass in None "
0924                "to indicate no where clause")
0925        self.whereClause = where
0926
0927    def __sqlrepr__(self, db):
0928        whereClause = self.whereClause
0929        if whereClause is None:
0930            return "DELETE FROM %s" % self.table
0931        whereClause = _str_or_sqlrepr(whereClause, db)
0932        return "DELETE FROM %s WHERE %s" % (self.table, whereClause)
0933
0934registerConverter(Delete, SQLExprConverter)
0935
0936
0937class Replace(Update):
0938    def sqlName(self):
0939        return "REPLACE"
0940
0941registerConverter(Replace, SQLExprConverter)
0942
0943########################################
0944# SQL Builtins
0945########################################
0946
0947
0948class DESC(SQLExpression):
0949
0950    def __init__(self, expr):
0951        self.expr = expr
0952
0953    def __sqlrepr__(self, db):
0954        if isinstance(self.expr, DESC):
0955            return sqlrepr(self.expr.expr, db)
0956        return '%s DESC' % sqlrepr(self.expr, db)
0957
0958
0959def AND(*ops):
0960    if not ops:
0961        return None
0962    op1 = ops[0]
0963    ops = ops[1:]
0964    if ops:
0965        return SQLOp("AND", op1, AND(*ops))
0966    else:
0967        return op1
0968
0969
0970def OR(*ops):
0971    if not ops:
0972        return None
0973    op1 = ops[0]
0974    ops = ops[1:]
0975    if ops:
0976        return SQLOp("OR", op1, OR(*ops))
0977    else:
0978        return op1
0979
0980
0981def NOT(op):
0982    return SQLPrefix("NOT", op)
0983
0984
0985def _IN(item, list):
0986    return SQLOp("IN", item, list)
0987
0988
0989def IN(item, list):
0990    from .sresults import SelectResults  # Import here to avoid circular import
0991    if isinstance(list, SelectResults):
0992        query = list.queryForSelect()
0993        query.ops['items'] = [list.sourceClass.q.id]
0994        list = query
0995    if isinstance(list, Select):
0996        return INSubquery(item, list)
0997    else:
0998        return _IN(item, list)
0999
1000
1001def NOTIN(item, list):
1002    if isinstance(list, Select):
1003        return NOTINSubquery(item, list)
1004    else:
1005        return NOT(_IN(item, list))
1006
1007
1008def STARTSWITH(expr, pattern):
1009    return LIKE(expr, _LikeQuoted(pattern) + '%', escape='\\')
1010
1011
1012def ENDSWITH(expr, pattern):
1013    return LIKE(expr, '%' + _LikeQuoted(pattern), escape='\\')
1014
1015
1016def CONTAINSSTRING(expr, pattern):
1017    return LIKE(expr, '%' + _LikeQuoted(pattern) + '%', escape='\\')
1018
1019
1020def ISNULL(expr):
1021    return SQLOp("IS", expr, None)
1022
1023
1024def ISNOTNULL(expr):
1025    return SQLOp("IS NOT", expr, None)
1026
1027
1028class ColumnAS(SQLOp):
1029    ''' Just like SQLOp('AS', expr, name) except without the parentheses '''
1030    def __init__(self, expr, name):
1031        if isinstance(name, string_type):
1032            name = SQLConstant(name)
1033        SQLOp.__init__(self, 'AS', expr, name)
1034
1035    def __sqlrepr__(self, db):
1036        return "%s %s %s" % (sqlrepr(self.expr1, db), self.op,
1037                             sqlrepr(self.expr2, db))
1038
1039
1040class _LikeQuoted:
1041    # It assumes prefix and postfix are strings; usually just a percent sign.
1042
1043    # @@: I'm not sure what the quoting rules really are for all the
1044    # databases
1045
1046    def __init__(self, expr):
1047        self.expr = expr
1048        self.prefix = ''
1049        self.postfix = ''
1050
1051    def __radd__(self, s):
1052        self.prefix = s + self.prefix
1053        return self
1054
1055    def __add__(self, s):
1056        self.postfix += s
1057        return self
1058
1059    def __sqlrepr__(self, db):
1060        s = self.expr
1061        if isinstance(s, SQLExpression):
1062            values = []
1063            if self.prefix:
1064                values.append(quote_str(self.prefix, db))
1065            s = _quote_like_special(sqlrepr(s, db), db)
1066            values.append(s)
1067            if self.postfix:
1068                values.append(quote_str(self.postfix, db))
1069            if db == "mysql":
1070                return "CONCAT(%s)" % ", ".join(values)
1071            else:
1072                return " || ".join(values)
1073        elif isinstance(s, string_type):
1074            s = _quote_like_special(unquote_str(sqlrepr(s, db)), db)
1075            return quote_str("%s%s%s" % (self.prefix, s, self.postfix), db)
1076        else:
1077            raise TypeError(
1078                "expected str, unicode or SQLExpression, got %s" % type(s))
1079
1080
1081def _quote_like_special(s, db):
1082    if db in ('postgres', 'rdbhost'):
1083        escape = r'\\'
1084    else:
1085        escape = '\\'
1086    s = s.replace('\\', r'\\').          replace('%', escape + '%').          replace('_', escape + '_')
1089    return s
1090
1091
1092########################################
1093# SQL JOINs
1094########################################
1095
1096
1097class SQLJoin(SQLExpression):
1098    def __init__(self, table1, table2, op=','):
1099        if hasattr(table1, 'sqlmeta'):
1100            table1 = table1.sqlmeta.table
1101        if hasattr(table2, 'sqlmeta'):
1102            table2 = table2.sqlmeta.table
1103        if isinstance(table1, str):
1104            table1 = SQLConstant(table1)
1105        if isinstance(table2, str):
1106            table2 = SQLConstant(table2)
1107        self.table1 = table1
1108        self.table2 = table2
1109        self.op = op
1110
1111    def __sqlrepr__(self, db):
1112        if self.table1:
1113            return "%s%s %s" % (sqlrepr(self.table1, db), self.op,
1114                                sqlrepr(self.table2, db))
1115        else:
1116            return "%s %s" % (self.op, sqlrepr(self.table2, db))
1117
1118registerConverter(SQLJoin, SQLExprConverter)
1119
1120
1121def JOIN(table1, table2):
1122    return SQLJoin(table1, table2, " JOIN")
1123
1124
1125def INNERJOIN(table1, table2):
1126    return SQLJoin(table1, table2, " INNER JOIN")
1127
1128
1129def CROSSJOIN(table1, table2):
1130    return SQLJoin(table1, table2, " CROSS JOIN")
1131
1132
1133def STRAIGHTJOIN(table1, table2):
1134    return SQLJoin(table1, table2, " STRAIGHT JOIN")
1135
1136
1137def LEFTJOIN(table1, table2):
1138    return SQLJoin(table1, table2, " LEFT JOIN")
1139
1140
1141def LEFTOUTERJOIN(table1, table2):
1142    return SQLJoin(table1, table2, " LEFT OUTER JOIN")
1143
1144
1145def NATURALJOIN(table1, table2):
1146    return SQLJoin(table1, table2, " NATURAL JOIN")
1147
1148
1149def NATURALLEFTJOIN(table1, table2):
1150    return SQLJoin(table1, table2, " NATURAL LEFT JOIN")
1151
1152
1153def NATURALLEFTOUTERJOIN(table1, table2):
1154    return SQLJoin(table1, table2, " NATURAL LEFT OUTER JOIN")
1155
1156
1157def RIGHTJOIN(table1, table2):
1158    return SQLJoin(table1, table2, " RIGHT JOIN")
1159
1160
1161def RIGHTOUTERJOIN(table1, table2):
1162    return SQLJoin(table1, table2, " RIGHT OUTER JOIN")
1163
1164
1165def NATURALRIGHTJOIN(table1, table2):
1166    return SQLJoin(table1, table2, " NATURAL RIGHT JOIN")
1167
1168
1169def NATURALRIGHTOUTERJOIN(table1, table2):
1170    return SQLJoin(table1, table2, " NATURAL RIGHT OUTER JOIN")
1171
1172
1173def FULLJOIN(table1, table2):
1174    return SQLJoin(table1, table2, " FULL JOIN")
1175
1176
1177def FULLOUTERJOIN(table1, table2):
1178    return SQLJoin(table1, table2, " FULL OUTER JOIN")
1179
1180
1181def NATURALFULLJOIN(table1, table2):
1182    return SQLJoin(table1, table2, " NATURAL FULL JOIN")
1183
1184
1185def NATURALFULLOUTERJOIN(table1, table2):
1186    return SQLJoin(table1, table2, " NATURAL FULL OUTER JOIN")
1187
1188
1189class SQLJoinConditional(SQLJoin):
1190    """Conditional JOIN"""
1191    def __init__(self, table1, table2, op,
1192                 on_condition=None, using_columns=None):
1193        """For condition you must give on_condition or using_columns
1194        but not both
1195
1196            on_condition can be a string or SQLExpression, for example
1197                Table1.q.col1 == Table2.q.col2
1198            using_columns can be a string or a list of columns, e.g.
1199                (Table1.q.col1, Table2.q.col2)
1200        """
1201        if not on_condition and not using_columns:
1202            raise TypeError("You must give ON condition or USING columns")
1203        if on_condition and using_columns:
1204            raise TypeError(
1205                "You must give ON condition or USING columns but not both")
1206        SQLJoin.__init__(self, table1, table2, op)
1207        self.on_condition = on_condition
1208        self.using_columns = using_columns
1209
1210    def __sqlrepr__(self, db):
1211        if self.on_condition:
1212            on_condition = self.on_condition
1213            if hasattr(on_condition, "__sqlrepr__"):
1214                on_condition = sqlrepr(on_condition, db)
1215            join = "%s %s ON %s" % (self.op, sqlrepr(self.table2, db),
1216                                    on_condition)
1217            if self.table1:
1218                join = "%s %s" % (sqlrepr(self.table1, db), join)
1219            return join
1220        elif self.using_columns:
1221            using_columns = []
1222            for col in self.using_columns:
1223                if hasattr(col, "__sqlrepr__"):
1224                    col = sqlrepr(col, db)
1225                using_columns.append(col)
1226            using_columns = ", ".join(using_columns)
1227            join = "%s %s USING (%s)" % (self.op, sqlrepr(self.table2, db),
1228                                         using_columns)
1229            if self.table1:
1230                join = "%s %s" % (sqlrepr(self.table1, db), join)
1231            return join
1232        else:
1233            RuntimeError, "Impossible error"
1234
1235registerConverter(SQLJoinConditional, SQLExprConverter)
1236
1237
1238def INNERJOINConditional(table1, table2,
1239                         on_condition=None, using_columns=None):
1240    return SQLJoinConditional(table1, table2, "INNER JOIN",
1241                              on_condition, using_columns)
1242
1243
1244def LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None):
1245    return SQLJoinConditional(table1, table2, "LEFT JOIN",
1246                              on_condition, using_columns)
1247
1248
1249def LEFTOUTERJOINConditional(table1, table2,
1250                             on_condition=None, using_columns=None):
1251    return SQLJoinConditional(table1, table2, "LEFT OUTER JOIN",
1252                              on_condition, using_columns)
1253
1254
1255def RIGHTJOINConditional(table1, table2,
1256                         on_condition=None, using_columns=None):
1257    return SQLJoinConditional(table1, table2, "RIGHT JOIN",
1258                              on_condition, using_columns)
1259
1260
1261def RIGHTOUTERJOINConditional(table1, table2,
1262                              on_condition=None, using_columns=None):
1263    return SQLJoinConditional(table1, table2, "RIGHT OUTER JOIN",
1264                              on_condition, using_columns)
1265
1266
1267def FULLJOINConditional(table1, table2, on_condition=None, using_columns=None):
1268    return SQLJoinConditional(table1, table2, "FULL JOIN",
1269                              on_condition, using_columns)
1270
1271
1272def FULLOUTERJOINConditional(table1, table2,
1273                             on_condition=None, using_columns=None):
1274    return SQLJoinConditional(table1, table2, "FULL OUTER JOIN",
1275                              on_condition, using_columns)
1276
1277
1278class SQLJoinOn(SQLJoinConditional):
1279    """Conditional JOIN ON"""
1280    def __init__(self, table1, table2, op, on_condition):
1281        SQLJoinConditional.__init__(self, table1, table2, op, on_condition)
1282
1283registerConverter(SQLJoinOn, SQLExprConverter)
1284
1285
1286class SQLJoinUsing(SQLJoinConditional):
1287    """Conditional JOIN USING"""
1288    def __init__(self, table1, table2, op, using_columns):
1289        SQLJoinConditional.__init__(self, table1, table2,
1290                                    op, None, using_columns)
1291
1292registerConverter(SQLJoinUsing, SQLExprConverter)
1293
1294
1295def INNERJOINOn(table1, table2, on_condition):
1296    return SQLJoinOn(table1, table2, "INNER JOIN", on_condition)
1297
1298
1299def LEFTJOINOn(table1, table2, on_condition):
1300    return SQLJoinOn(table1, table2, "LEFT JOIN", on_condition)
1301
1302
1303def LEFTOUTERJOINOn(table1, table2, on_condition):
1304    return SQLJoinOn(table1, table2, "LEFT OUTER JOIN", on_condition)
1305
1306
1307def RIGHTJOINOn(table1, table2, on_condition):
1308    return SQLJoinOn(table1, table2, "RIGHT JOIN", on_condition)
1309
1310
1311def RIGHTOUTERJOINOn(table1, table2, on_condition):
1312    return SQLJoinOn(table1, table2, "RIGHT OUTER JOIN", on_condition)
1313
1314
1315def FULLJOINOn(table1, table2, on_condition):
1316    return SQLJoinOn(table1, table2, "FULL JOIN", on_condition)
1317
1318
1319def FULLOUTERJOINOn(table1, table2, on_condition):
1320    return SQLJoinOn(table1, table2, "FULL OUTER JOIN", on_condition)
1321
1322
1323def INNERJOINUsing(table1, table2, using_columns):
1324    return SQLJoinUsing(table1, table2, "INNER JOIN", using_columns)
1325
1326
1327def LEFTJOINUsing(table1, table2, using_columns):
1328    return SQLJoinUsing(table1, table2, "LEFT JOIN", using_columns)
1329
1330
1331def LEFTOUTERJOINUsing(table1, table2, using_columns):
1332    return SQLJoinUsing(table1, table2, "LEFT OUTER JOIN", using_columns)
1333
1334
1335def RIGHTJOINUsing(table1, table2, using_columns):
1336    return SQLJoinUsing(table1, table2, "RIGHT JOIN", using_columns)
1337
1338
1339def RIGHTOUTERJOINUsing(table1, table2, using_columns):
1340    return SQLJoinUsing(table1, table2, "RIGHT OUTER JOIN", using_columns)
1341
1342
1343def FULLJOINUsing(table1, table2, using_columns):
1344    return SQLJoinUsing(table1, table2, "FULL JOIN", using_columns)
1345
1346
1347def FULLOUTERJOINUsing(table1, table2, using_columns):
1348    return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
1349
1350
1351########################################
1352# Subqueries (subselects)
1353########################################
1354
1355class OuterField(SQLObjectField):
1356    def tablesUsedImmediate(self):
1357        return []
1358
1359
1360class OuterTable(SQLObjectTable):
1361    FieldClass = OuterField
1362
1363
1364class Outer:
1365    def __init__(self, table):
1366        self.q = OuterTable(table)
1367
1368
1369class LIKE(SQLExpression):
1370    op = "LIKE"
1371
1372    def __init__(self, expr, string, escape=None):
1373        self.expr = expr
1374        self.string = string
1375        self.escape = escape
1376
1377    def __sqlrepr__(self, db):
1378        escape = self.escape
1379        like = "%s %s (%s)" % (sqlrepr(self.expr, db),
1380                               self.op, sqlrepr(self.string, db))
1381        if escape is None:
1382            return "(%s)" % like
1383        else:
1384            return "(%s ESCAPE %s)" % (like, sqlrepr(escape, db))
1385
1386    def components(self):
1387        return [self.expr, self.string]
1388
1389    def execute(self, executor):
1390        if not hasattr(self, '_regex'):
1391            # @@: Crude, not entirely accurate
1392            dest = self.string
1393            dest = dest.replace("%%", "\001")
1394            dest = dest.replace("*", "\002")
1395            dest = dest.replace("%", "*")
1396            dest = dest.replace("\001", "%")
1397            dest = dest.replace("\002", "[*]")
1398            self._regex = re.compile(fnmatch.translate(dest), re.I)
1399        return self._regex.search(execute(self.expr, executor))
1400
1401
1402class RLIKE(LIKE):
1403    op = "RLIKE"
1404
1405    op_db = {
1406        'firebird': 'RLIKE',
1407        'maxdb': 'RLIKE',
1408        'mysql': 'RLIKE',
1409        'postgres': '~',
1410        'rdbhost': '~',
1411        'sqlite': 'REGEXP'
1412    }
1413
1414    def _get_op(self, db):
1415        return self.op_db.get(db, 'LIKE')
1416
1417    def __sqlrepr__(self, db):
1418        return "(%s %s (%s))" % (
1419            sqlrepr(self.expr, db), self._get_op(db), sqlrepr(self.string, db)
1420        )
1421
1422    def execute(self, executor):
1423        self.op = self._get_op(self.db)
1424        return LIKE.execute(self, executor)
1425
1426
1427class INSubquery(SQLExpression):
1428    op = "IN"
1429
1430    def __init__(self, item, subquery):
1431        self.item = item
1432        self.subquery = subquery
1433
1434    def components(self):
1435        return [self.item]
1436
1437    def __sqlrepr__(self, db):
1438        return "%s %s (%s)" % (sqlrepr(self.item, db),
1439                               self.op, sqlrepr(self.subquery, db))
1440
1441
1442class NOTINSubquery(INSubquery):
1443    op = "NOT IN"
1444
1445
1446class Subquery(SQLExpression):
1447    def __init__(self, op, subquery):
1448        self.op = op
1449        self.subquery = subquery
1450
1451    def __sqlrepr__(self, db):
1452        return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
1453
1454
1455def EXISTS(subquery):
1456    return Subquery("EXISTS", subquery)
1457
1458
1459def NOTEXISTS(subquery):
1460    return Subquery("NOT EXISTS", subquery)
1461
1462
1463def SOME(subquery):
1464    return Subquery("SOME", subquery)
1465
1466
1467def ANY(subquery):
1468    return Subquery("ANY", subquery)
1469
1470
1471def ALL(subquery):
1472    return Subquery("ALL", subquery)
1473
1474
1475####
1476
1477
1478class ImportProxyField(SQLObjectField):
1479    def tablesUsedImmediate(self):
1480        return [str(self.tableName)]
1481
1482
1483class ImportProxy(SQLExpression):
1484    '''Class to be used in column definitions that rely on other tables that might
1485        not yet be in a classregistry.
1486    '''
1487    FieldClass = ImportProxyField
1488
1489    def __init__(self, clsName, registry=None):
1490        self.tableName = _DelayClass(self, clsName)
1491        self.sqlmeta = _Delay_proxy(table=_DelayClass(self, clsName))
1492        self.q = self
1493        self.soClass = None
1494        classregistry.registry(registry).addClassCallback(
1495            clsName, lambda foreign, me: setattr(me, 'soClass', foreign), self)
1496
1497    def __nonzero__(self):
1498        return True
1499
1500    def __getattr__(self, attr):
1501        if self.soClass is None:
1502            return _Delay(self, attr)
1503        return getattr(self.soClass.q, attr)
1504
1505
1506class _Delay(SQLExpression):
1507    def __init__(self, proxy, attr):
1508        self.attr = attr
1509        self.proxy = proxy
1510
1511    def __sqlrepr__(self, db):
1512        if self.proxy.soClass is None:
1513            return '_DELAYED_' + self.attr
1514        val = self._resolve()
1515        if isinstance(val, SQLExpression):
1516            val = sqlrepr(val, db)
1517        return val
1518
1519    def tablesUsedImmediate(self):
1520        return getattr(self._resolve(), 'tablesUsedImmediate', lambda: [])()
1521
1522    def components(self):
1523        return getattr(self._resolve(), 'components', lambda: [])()
1524
1525    def _resolve(self):
1526        return getattr(self.proxy, self.attr)
1527
1528    # For AliasTable etc
1529    def fieldName(self):
1530        class _aliasFieldName(SQLExpression):
1531            def __init__(self, proxy):
1532                self.proxy = proxy
1533
1534            def __sqlrepr__(self, db):
1535                return self.proxy._resolve().fieldName
1536        return _aliasFieldName(self)
1537    fieldName = property(fieldName)
1538
1539
1540class _DelayClass(_Delay):
1541    def _resolve(self):
1542        return self.proxy.soClass.sqlmeta.table
1543
1544
1545class _Delay_proxy(object):
1546    def __init__(self, **kw):
1547        self.__dict__.update(kw)
1548
1549######
1550
1551
1552########################################
1553# Global initializations
1554########################################
1555
1556table = TableSpace()
1557const = ConstantSpace()
1558func = const
1559
1560########################################
1561# Testing
1562########################################
1563
1564if __name__ == "__main__":
1565    tests = """
1566>>> AND(table.address.name == "Ian Bicking", table.address.zip > 30000)
1567>>> table.address.name
1568>>> AND(LIKE(table.address.name, "this"), IN(table.address.zip, [100, 200, 300]))
1569>>> Select([table.address.name, table.address.state], where=LIKE(table.address.name, "%ian%"))
1570>>> Select([table.user.name], where=AND(table.user.state == table.states.abbrev))
1571>>> Insert(table.address, [{"name": "BOB", "address": "3049 N. 18th St."}, {"name": "TIM", "address": "409 S. 10th St."}])
1572>>> Insert(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1573>>> Delete(table.address, where="BOB"==table.address.name)
1574>>> Update(table.address, {"lastModified": const.NOW()})
1575>>> Replace(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1576"""  # noqa: allow long (> 79) lines
1577    for expr in tests.split('\n'):
1578        if not expr.strip():
1579            continue
1580        if expr.startswith('>>> '):
1581            expr = expr[4:]