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
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
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
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
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
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
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 = ''
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
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
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]
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
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
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
1041
1042
1043
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
1091class CONCAT(SQLExpression):
1092 def __init__(self, *expressions):
1093 self.expressions = expressions
1094
1095 def __sqlrepr__(self, db):
1096 values = [sqlrepr(expr, db) for expr in self.expressions]
1097 if db == "mysql":
1098 return "CONCAT(%s)" % ", ".join(values)
1099 else:
1100 return " || ".join(values)
1101
1102
1103
1104
1105
1106
1107class SQLJoin(SQLExpression):
1108 def __init__(self, table1, table2, op=','):
1109 if hasattr(table1, 'sqlmeta'):
1110 table1 = table1.sqlmeta.table
1111 if hasattr(table2, 'sqlmeta'):
1112 table2 = table2.sqlmeta.table
1113 if isinstance(table1, str):
1114 table1 = SQLConstant(table1)
1115 if isinstance(table2, str):
1116 table2 = SQLConstant(table2)
1117 self.table1 = table1
1118 self.table2 = table2
1119 self.op = op
1120
1121 def __sqlrepr__(self, db):
1122 if self.table1:
1123 return "%s%s %s" % (sqlrepr(self.table1, db), self.op,
1124 sqlrepr(self.table2, db))
1125 else:
1126 return "%s %s" % (self.op, sqlrepr(self.table2, db))
1127
1128registerConverter(SQLJoin, SQLExprConverter)
1129
1130
1131def JOIN(table1, table2):
1132 return SQLJoin(table1, table2, " JOIN")
1133
1134
1135def INNERJOIN(table1, table2):
1136 return SQLJoin(table1, table2, " INNER JOIN")
1137
1138
1139def CROSSJOIN(table1, table2):
1140 return SQLJoin(table1, table2, " CROSS JOIN")
1141
1142
1143def STRAIGHTJOIN(table1, table2):
1144 return SQLJoin(table1, table2, " STRAIGHT JOIN")
1145
1146
1147def LEFTJOIN(table1, table2):
1148 return SQLJoin(table1, table2, " LEFT JOIN")
1149
1150
1151def LEFTOUTERJOIN(table1, table2):
1152 return SQLJoin(table1, table2, " LEFT OUTER JOIN")
1153
1154
1155def NATURALJOIN(table1, table2):
1156 return SQLJoin(table1, table2, " NATURAL JOIN")
1157
1158
1159def NATURALLEFTJOIN(table1, table2):
1160 return SQLJoin(table1, table2, " NATURAL LEFT JOIN")
1161
1162
1163def NATURALLEFTOUTERJOIN(table1, table2):
1164 return SQLJoin(table1, table2, " NATURAL LEFT OUTER JOIN")
1165
1166
1167def RIGHTJOIN(table1, table2):
1168 return SQLJoin(table1, table2, " RIGHT JOIN")
1169
1170
1171def RIGHTOUTERJOIN(table1, table2):
1172 return SQLJoin(table1, table2, " RIGHT OUTER JOIN")
1173
1174
1175def NATURALRIGHTJOIN(table1, table2):
1176 return SQLJoin(table1, table2, " NATURAL RIGHT JOIN")
1177
1178
1179def NATURALRIGHTOUTERJOIN(table1, table2):
1180 return SQLJoin(table1, table2, " NATURAL RIGHT OUTER JOIN")
1181
1182
1183def FULLJOIN(table1, table2):
1184 return SQLJoin(table1, table2, " FULL JOIN")
1185
1186
1187def FULLOUTERJOIN(table1, table2):
1188 return SQLJoin(table1, table2, " FULL OUTER JOIN")
1189
1190
1191def NATURALFULLJOIN(table1, table2):
1192 return SQLJoin(table1, table2, " NATURAL FULL JOIN")
1193
1194
1195def NATURALFULLOUTERJOIN(table1, table2):
1196 return SQLJoin(table1, table2, " NATURAL FULL OUTER JOIN")
1197
1198
1199class SQLJoinConditional(SQLJoin):
1200 """Conditional JOIN"""
1201 def __init__(self, table1, table2, op,
1202 on_condition=None, using_columns=None):
1203 """For condition you must give on_condition or using_columns
1204 but not both
1205
1206 on_condition can be a string or SQLExpression, for example
1207 Table1.q.col1 == Table2.q.col2
1208 using_columns can be a string or a list of columns, e.g.
1209 (Table1.q.col1, Table2.q.col2)
1210 """
1211 if not on_condition and not using_columns:
1212 raise TypeError("You must give ON condition or USING columns")
1213 if on_condition and using_columns:
1214 raise TypeError(
1215 "You must give ON condition or USING columns but not both")
1216 SQLJoin.__init__(self, table1, table2, op)
1217 self.on_condition = on_condition
1218 self.using_columns = using_columns
1219
1220 def __sqlrepr__(self, db):
1221 if self.on_condition:
1222 on_condition = self.on_condition
1223 if hasattr(on_condition, "__sqlrepr__"):
1224 on_condition = sqlrepr(on_condition, db)
1225 join = "%s %s ON %s" % (self.op, sqlrepr(self.table2, db),
1226 on_condition)
1227 if self.table1:
1228 join = "%s %s" % (sqlrepr(self.table1, db), join)
1229 return join
1230 elif self.using_columns:
1231 using_columns = []
1232 for col in self.using_columns:
1233 if hasattr(col, "__sqlrepr__"):
1234 col = sqlrepr(col, db)
1235 using_columns.append(col)
1236 using_columns = ", ".join(using_columns)
1237 join = "%s %s USING (%s)" % (self.op, sqlrepr(self.table2, db),
1238 using_columns)
1239 if self.table1:
1240 join = "%s %s" % (sqlrepr(self.table1, db), join)
1241 return join
1242 else:
1243 RuntimeError, "Impossible error"
1244
1245registerConverter(SQLJoinConditional, SQLExprConverter)
1246
1247
1248def INNERJOINConditional(table1, table2,
1249 on_condition=None, using_columns=None):
1250 return SQLJoinConditional(table1, table2, "INNER JOIN",
1251 on_condition, using_columns)
1252
1253
1254def LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None):
1255 return SQLJoinConditional(table1, table2, "LEFT JOIN",
1256 on_condition, using_columns)
1257
1258
1259def LEFTOUTERJOINConditional(table1, table2,
1260 on_condition=None, using_columns=None):
1261 return SQLJoinConditional(table1, table2, "LEFT OUTER JOIN",
1262 on_condition, using_columns)
1263
1264
1265def RIGHTJOINConditional(table1, table2,
1266 on_condition=None, using_columns=None):
1267 return SQLJoinConditional(table1, table2, "RIGHT JOIN",
1268 on_condition, using_columns)
1269
1270
1271def RIGHTOUTERJOINConditional(table1, table2,
1272 on_condition=None, using_columns=None):
1273 return SQLJoinConditional(table1, table2, "RIGHT OUTER JOIN",
1274 on_condition, using_columns)
1275
1276
1277def FULLJOINConditional(table1, table2, on_condition=None, using_columns=None):
1278 return SQLJoinConditional(table1, table2, "FULL JOIN",
1279 on_condition, using_columns)
1280
1281
1282def FULLOUTERJOINConditional(table1, table2,
1283 on_condition=None, using_columns=None):
1284 return SQLJoinConditional(table1, table2, "FULL OUTER JOIN",
1285 on_condition, using_columns)
1286
1287
1288class SQLJoinOn(SQLJoinConditional):
1289 """Conditional JOIN ON"""
1290 def __init__(self, table1, table2, op, on_condition):
1291 SQLJoinConditional.__init__(self, table1, table2, op, on_condition)
1292
1293registerConverter(SQLJoinOn, SQLExprConverter)
1294
1295
1296class SQLJoinUsing(SQLJoinConditional):
1297 """Conditional JOIN USING"""
1298 def __init__(self, table1, table2, op, using_columns):
1299 SQLJoinConditional.__init__(self, table1, table2,
1300 op, None, using_columns)
1301
1302registerConverter(SQLJoinUsing, SQLExprConverter)
1303
1304
1305def INNERJOINOn(table1, table2, on_condition):
1306 return SQLJoinOn(table1, table2, "INNER JOIN", on_condition)
1307
1308
1309def LEFTJOINOn(table1, table2, on_condition):
1310 return SQLJoinOn(table1, table2, "LEFT JOIN", on_condition)
1311
1312
1313def LEFTOUTERJOINOn(table1, table2, on_condition):
1314 return SQLJoinOn(table1, table2, "LEFT OUTER JOIN", on_condition)
1315
1316
1317def RIGHTJOINOn(table1, table2, on_condition):
1318 return SQLJoinOn(table1, table2, "RIGHT JOIN", on_condition)
1319
1320
1321def RIGHTOUTERJOINOn(table1, table2, on_condition):
1322 return SQLJoinOn(table1, table2, "RIGHT OUTER JOIN", on_condition)
1323
1324
1325def FULLJOINOn(table1, table2, on_condition):
1326 return SQLJoinOn(table1, table2, "FULL JOIN", on_condition)
1327
1328
1329def FULLOUTERJOINOn(table1, table2, on_condition):
1330 return SQLJoinOn(table1, table2, "FULL OUTER JOIN", on_condition)
1331
1332
1333def INNERJOINUsing(table1, table2, using_columns):
1334 return SQLJoinUsing(table1, table2, "INNER JOIN", using_columns)
1335
1336
1337def LEFTJOINUsing(table1, table2, using_columns):
1338 return SQLJoinUsing(table1, table2, "LEFT JOIN", using_columns)
1339
1340
1341def LEFTOUTERJOINUsing(table1, table2, using_columns):
1342 return SQLJoinUsing(table1, table2, "LEFT OUTER JOIN", using_columns)
1343
1344
1345def RIGHTJOINUsing(table1, table2, using_columns):
1346 return SQLJoinUsing(table1, table2, "RIGHT JOIN", using_columns)
1347
1348
1349def RIGHTOUTERJOINUsing(table1, table2, using_columns):
1350 return SQLJoinUsing(table1, table2, "RIGHT OUTER JOIN", using_columns)
1351
1352
1353def FULLJOINUsing(table1, table2, using_columns):
1354 return SQLJoinUsing(table1, table2, "FULL JOIN", using_columns)
1355
1356
1357def FULLOUTERJOINUsing(table1, table2, using_columns):
1358 return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
1359
1360
1361
1362
1363
1364
1365class OuterField(SQLObjectField):
1366 def tablesUsedImmediate(self):
1367 return []
1368
1369
1370class OuterTable(SQLObjectTable):
1371 FieldClass = OuterField
1372
1373
1374class Outer:
1375 def __init__(self, table):
1376 self.q = OuterTable(table)
1377
1378
1379class LIKE(SQLExpression):
1380 op = "LIKE"
1381
1382 def __init__(self, expr, string, escape=None):
1383 self.expr = expr
1384 self.string = string
1385 self.escape = escape
1386
1387 def __sqlrepr__(self, db):
1388 escape = self.escape
1389 like = "%s %s (%s)" % (sqlrepr(self.expr, db),
1390 self.op, sqlrepr(self.string, db))
1391 if escape is None:
1392 return "(%s)" % like
1393 else:
1394 return "(%s ESCAPE %s)" % (like, sqlrepr(escape, db))
1395
1396 def components(self):
1397 return [self.expr, self.string]
1398
1399 def execute(self, executor):
1400 if not hasattr(self, '_regex'):
1401
1402 dest = self.string
1403 dest = dest.replace("%%", "\001")
1404 dest = dest.replace("*", "\002")
1405 dest = dest.replace("%", "*")
1406 dest = dest.replace("\001", "%")
1407 dest = dest.replace("\002", "[*]")
1408 self._regex = re.compile(fnmatch.translate(dest), re.I)
1409 return self._regex.search(execute(self.expr, executor))
1410
1411
1412class RLIKE(LIKE):
1413 op = "RLIKE"
1414
1415 op_db = {
1416 'firebird': 'RLIKE',
1417 'maxdb': 'RLIKE',
1418 'mysql': 'RLIKE',
1419 'postgres': '~',
1420 'rdbhost': '~',
1421 'sqlite': 'REGEXP'
1422 }
1423
1424 def _get_op(self, db):
1425 return self.op_db.get(db, 'LIKE')
1426
1427 def __sqlrepr__(self, db):
1428 return "(%s %s (%s))" % (
1429 sqlrepr(self.expr, db), self._get_op(db), sqlrepr(self.string, db)
1430 )
1431
1432 def execute(self, executor):
1433 self.op = self._get_op(self.db)
1434 return LIKE.execute(self, executor)
1435
1436
1437class INSubquery(SQLExpression):
1438 op = "IN"
1439
1440 def __init__(self, item, subquery):
1441 self.item = item
1442 self.subquery = subquery
1443
1444 def components(self):
1445 return [self.item]
1446
1447 def __sqlrepr__(self, db):
1448 return "%s %s (%s)" % (sqlrepr(self.item, db),
1449 self.op, sqlrepr(self.subquery, db))
1450
1451
1452class NOTINSubquery(INSubquery):
1453 op = "NOT IN"
1454
1455
1456class Subquery(SQLExpression):
1457 def __init__(self, op, subquery):
1458 self.op = op
1459 self.subquery = subquery
1460
1461 def __sqlrepr__(self, db):
1462 return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
1463
1464
1465def EXISTS(subquery):
1466 return Subquery("EXISTS", subquery)
1467
1468
1469def NOTEXISTS(subquery):
1470 return Subquery("NOT EXISTS", subquery)
1471
1472
1473def SOME(subquery):
1474 return Subquery("SOME", subquery)
1475
1476
1477def ANY(subquery):
1478 return Subquery("ANY", subquery)
1479
1480
1481def ALL(subquery):
1482 return Subquery("ALL", subquery)
1483
1484
1485
1486
1487
1488class ImportProxyField(SQLObjectField):
1489 def tablesUsedImmediate(self):
1490 return [str(self.tableName)]
1491
1492
1493class ImportProxy(SQLExpression):
1494 '''Class to be used in column definitions that rely on other tables that might
1495 not yet be in a classregistry.
1496 '''
1497 FieldClass = ImportProxyField
1498
1499 def __init__(self, clsName, registry=None):
1500 self.tableName = _DelayClass(self, clsName)
1501 self.sqlmeta = _Delay_proxy(table=_DelayClass(self, clsName))
1502 self.q = self
1503 self.soClass = None
1504 classregistry.registry(registry).addClassCallback(
1505 clsName, lambda foreign, me: setattr(me, 'soClass', foreign), self)
1506
1507 def __nonzero__(self):
1508 return True
1509
1510 def __getattr__(self, attr):
1511 if self.soClass is None:
1512 return _Delay(self, attr)
1513 return getattr(self.soClass.q, attr)
1514
1515
1516class _Delay(SQLExpression):
1517 def __init__(self, proxy, attr):
1518 self.attr = attr
1519 self.proxy = proxy
1520
1521 def __sqlrepr__(self, db):
1522 if self.proxy.soClass is None:
1523 return '_DELAYED_' + self.attr
1524 val = self._resolve()
1525 if isinstance(val, SQLExpression):
1526 val = sqlrepr(val, db)
1527 return val
1528
1529 def tablesUsedImmediate(self):
1530 return getattr(self._resolve(), 'tablesUsedImmediate', lambda: [])()
1531
1532 def components(self):
1533 return getattr(self._resolve(), 'components', lambda: [])()
1534
1535 def _resolve(self):
1536 return getattr(self.proxy, self.attr)
1537
1538
1539 def fieldName(self):
1540 class _aliasFieldName(SQLExpression):
1541 def __init__(self, proxy):
1542 self.proxy = proxy
1543
1544 def __sqlrepr__(self, db):
1545 return self.proxy._resolve().fieldName
1546 return _aliasFieldName(self)
1547 fieldName = property(fieldName)
1548
1549
1550class _DelayClass(_Delay):
1551 def _resolve(self):
1552 return self.proxy.soClass.sqlmeta.table
1553
1554
1555class _Delay_proxy(object):
1556 def __init__(self, **kw):
1557 self.__dict__.update(kw)
1558
1559
1560
1561
1562
1563
1564
1565
1566table = TableSpace()
1567const = ConstantSpace()
1568func = const
1569
1570
1571
1572
1573
1574if __name__ == "__main__":
1575 tests = """
1576>>> AND(table.address.name == "Ian Bicking", table.address.zip > 30000)
1577>>> table.address.name
1578>>> AND(LIKE(table.address.name, "this"), IN(table.address.zip, [100, 200, 300]))
1579>>> Select([table.address.name, table.address.state], where=LIKE(table.address.name, "%ian%"))
1580>>> Select([table.user.name], where=AND(table.user.state == table.states.abbrev))
1581>>> Insert(table.address, [{"name": "BOB", "address": "3049 N. 18th St."}, {"name": "TIM", "address": "409 S. 10th St."}])
1582>>> Insert(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1583>>> Delete(table.address, where="BOB"==table.address.name)
1584>>> Update(table.address, {"lastModified": const.NOW()})
1585>>> Replace(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1586"""
1587 for expr in tests.split('\n'):
1588 if not expr.strip():
1589 continue
1590 if expr.startswith('>>> '):
1591 expr = expr[4:]