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