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., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
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-- particularly left joins, among others. You may wish to only use
0054this to generate ``WHERE`` clauses.
0055
0056See the bottom of this module for some examples, and run it (i.e.
0057``python sql.py``) to see the results of those examples.
0058
0059"""
0060
0061
0062
0063
0064
0065class VersionError(Exception):
0066 pass
0067class NoDefault:
0068 pass
0069
0070try:
0071 set
0072except NameError:
0073 from sets import Set, ImmutableSet
0074 set, frozenset = Set, ImmutableSet
0075
0076import re, fnmatch
0077import operator
0078import threading
0079import types
0080import classregistry
0081
0082from converters import sqlrepr, registerConverter
0083
0084safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
0085def sqlIdentifier(obj):
0086
0087 return isinstance(obj, basestring) and bool(safeSQLRE.search(obj.strip()))
0088
0089
0090def execute(expr, executor):
0091 if hasattr(expr, 'execute'):
0092 return expr.execute(executor)
0093 else:
0094 return expr
0095
0096
0097
0098
0099
0100class SQLExpression:
0101 def __add__(self, other):
0102 return SQLOp("+", self, other)
0103 def __radd__(self, other):
0104 return SQLOp("+", other, self)
0105 def __sub__(self, other):
0106 return SQLOp("-", self, other)
0107 def __rsub__(self, other):
0108 return SQLOp("-", other, self)
0109 def __mul__(self, other):
0110 return SQLOp("*", self, other)
0111 def __rmul__(self, other):
0112 return SQLOp("*", other, self)
0113 def __div__(self, other):
0114 return SQLOp("/", self, other)
0115 def __rdiv__(self, other):
0116 return SQLOp("/", other, self)
0117 def __pos__(self):
0118 return SQLPrefix("+", self)
0119 def __neg__(self):
0120 return SQLPrefix("-", self)
0121 def __pow__(self, other):
0122 return SQLConstant("POW")(self, other)
0123 def __rpow__(self, other):
0124 return SQLConstant("POW")(other, self)
0125 def __abs__(self):
0126 return SQLConstant("ABS")(self)
0127 def __mod__(self, other):
0128 return SQLConstant("MOD")(self, other)
0129 def __rmod__(self, other):
0130 return SQLConstant("MOD")(other, self)
0131
0132 def __lt__(self, other):
0133 return SQLOp("<", self, other)
0134 def __le__(self, other):
0135 return SQLOp("<=", self, other)
0136 def __gt__(self, other):
0137 return SQLOp(">", self, other)
0138 def __ge__(self, other):
0139 return SQLOp(">=", self, other)
0140 def __eq__(self, other):
0141 if other is None:
0142 return ISNULL(self)
0143 else:
0144 return SQLOp("=", self, other)
0145 def __ne__(self, other):
0146 if other is None:
0147 return ISNOTNULL(self)
0148 else:
0149 return SQLOp("<>", self, other)
0150
0151 def __and__(self, other):
0152 return SQLOp("AND", self, other)
0153 def __rand__(self, other):
0154 return SQLOp("AND", other, self)
0155 def __or__(self, other):
0156 return SQLOp("OR", self, other)
0157 def __ror__(self, other):
0158 return SQLOp("OR", other, self)
0159 def __invert__(self):
0160 return SQLPrefix("NOT", self)
0161
0162 def __call__(self, *args):
0163 return SQLCall(self, args)
0164
0165 def __repr__(self):
0166 try:
0167 return self.__sqlrepr__(None)
0168 except AssertionError:
0169 return '<%s %s>' % (
0170 self.__class__.__name__, hex(id(self))[2:])
0171
0172 def __str__(self):
0173 return repr(self)
0174
0175 def __cmp__(self, other):
0176 raise VersionError, "Python 2.1+ required"
0177 def __rcmp__(self, other):
0178 raise VersionError, "Python 2.1+ required"
0179
0180 def startswith(self, s):
0181 return STARTSWITH(self, s)
0182 def endswith(self, s):
0183 return ENDSWITH(self, s)
0184 def contains(self, s):
0185 return CONTAINSSTRING(self, s)
0186
0187 def components(self):
0188 return []
0189
0190 def tablesUsed(self, db):
0191 return self.tablesUsedSet(db)
0192 def tablesUsedSet(self, db):
0193 tables = set()
0194 for table in self.tablesUsedImmediate():
0195 if hasattr(table, '__sqlrepr__'):
0196 table = sqlrepr(table, db)
0197 tables.add(table)
0198 for component in self.components():
0199 tables.update(tablesUsedSet(component, db))
0200 return tables
0201 def tablesUsedImmediate(self):
0202 return []
0203
0204
0205
0206
0207
0208def SQLExprConverter(value, db):
0209 return value.__sqlrepr__()
0210
0211registerConverter(SQLExpression, SQLExprConverter)
0212
0213def tablesUsedSet(obj, db):
0214 if hasattr(obj, "tablesUsedSet"):
0215 return obj.tablesUsedSet(db)
0216 else:
0217 return {}
0218
0219operatorMap = {
0220 "+": operator.add,
0221 "/": operator.div,
0222 "-": operator.sub,
0223 "*": operator.mul,
0224 "<": operator.lt,
0225 "<=": operator.le,
0226 "=": operator.eq,
0227 "!=": operator.ne,
0228 ">=": operator.ge,
0229 ">": operator.gt,
0230 "IN": operator.contains,
0231 "IS": operator.eq,
0232 }
0233
0234class SQLOp(SQLExpression):
0235 def __init__(self, op, expr1, expr2):
0236 self.op = op.upper()
0237 self.expr1 = expr1
0238 self.expr2 = expr2
0239 def __sqlrepr__(self, db):
0240 s1 = sqlrepr(self.expr1, db)
0241 s2 = sqlrepr(self.expr2, db)
0242 if s1[0] != '(' and s1 != 'NULL':
0243 s1 = '(' + s1 + ')'
0244 if s2[0] != '(' and s2 != 'NULL':
0245 s2 = '(' + s2 + ')'
0246 return "(%s %s %s)" % (s1, self.op, s2)
0247 def components(self):
0248 return [self.expr1, self.expr2]
0249 def execute(self, executor):
0250 if self.op == "AND":
0251 return execute(self.expr1, executor) and execute(self.expr2, executor)
0253 elif self.op == "OR":
0254 return execute(self.expr1, executor) or execute(self.expr2, executor)
0256 else:
0257 return operatorMap[self.op.upper()](execute(self.expr1, executor),
0258 execute(self.expr2, executor))
0259
0260registerConverter(SQLOp, SQLExprConverter)
0261
0262class SQLCall(SQLExpression):
0263 def __init__(self, expr, args):
0264 self.expr = expr
0265 self.args = args
0266 def __sqlrepr__(self, db):
0267 return "%s%s" % (sqlrepr(self.expr, db), sqlrepr(self.args, db))
0268 def components(self):
0269 return [self.expr] + list(self.args)
0270 def execute(self, executor):
0271 raise ValueError, "I don't yet know how to locally execute functions"
0272
0273registerConverter(SQLCall, SQLExprConverter)
0274
0275class SQLPrefix(SQLExpression):
0276 def __init__(self, prefix, expr):
0277 self.prefix = prefix
0278 self.expr = expr
0279 def __sqlrepr__(self, db):
0280 return "%s %s" % (self.prefix, sqlrepr(self.expr, db))
0281 def components(self):
0282 return [self.expr]
0283 def execute(self, executor):
0284 expr = execute(self.expr, executor)
0285 if prefix == "+":
0286 return expr
0287 elif prefix == "-":
0288 return -expr
0289 elif prefix.upper() == "NOT":
0290 return not expr
0291
0292registerConverter(SQLPrefix, SQLExprConverter)
0293
0294class SQLConstant(SQLExpression):
0295 def __init__(self, const):
0296 self.const = const
0297 def __sqlrepr__(self, db):
0298 return self.const
0299 def execute(self, executor):
0300 raise ValueError, "I don't yet know how to execute SQL constants"
0301
0302registerConverter(SQLConstant, SQLExprConverter)
0303
0304class SQLTrueClauseClass(SQLExpression):
0305 def __sqlrepr__(self, db):
0306 return "1 = 1"
0307 def execute(self, executor):
0308 return 1
0309
0310SQLTrueClause = SQLTrueClauseClass()
0311
0312registerConverter(SQLTrueClauseClass, SQLExprConverter)
0313
0314
0315
0316
0317
0318class Field(SQLExpression):
0319 def __init__(self, tableName, fieldName):
0320 self.tableName = tableName
0321 self.fieldName = fieldName
0322 def __sqlrepr__(self, db):
0323 return self.tableName + "." + self.fieldName
0324 def tablesUsedImmediate(self):
0325 return [self.tableName]
0326 def execute(self, executor):
0327 return executor.field(self.tableName, self.fieldName)
0328
0329class SQLObjectField(Field):
0330 def __init__(self, tableName, fieldName, original):
0331 self.original = original
0332 Field.__init__(self, tableName, fieldName)
0333
0334registerConverter(SQLObjectField, SQLExprConverter)
0335
0336
0337class UnicodeField(SQLObjectField):
0338 def __init__(self, tableName, fieldName, original, column):
0339 SQLObjectField.__init__(self, tableName, fieldName, original)
0340 self.column = column
0341 def __eq__(self, other):
0342 if other is None:
0343 return ISNULL(self)
0344 if isinstance(other, unicode):
0345 other = other.encode(self.column.dbEncoding)
0346 return SQLOp('=', self, other)
0347 def __ne__(self, other):
0348 if other is None:
0349 return ISNOTNULL(self)
0350 if isinstance(other, unicode):
0351 other = other.encode(self.column.dbEncoding)
0352 return SQLOp('<>', self, other)
0353 def startswith(self, s):
0354 if isinstance(s, unicode):
0355 s = s.encode(self.column.dbEncoding)
0356 return STARTSWITH(self, s)
0357 def endswith(self, s):
0358 if isinstance(s, unicode):
0359 s = s.encode(self.column.dbEncoding)
0360 return ENDSWITH(self, s)
0361 def contains(self, s):
0362 if isinstance(s, unicode):
0363 s = s.encode(self.column.dbEncoding)
0364 return CONTAINSSTRING(self, s)
0365
0366
0367registerConverter(UnicodeField, SQLExprConverter)
0368
0369class Table(SQLExpression):
0370 FieldClass = Field
0371
0372 def __init__(self, tableName):
0373 self.tableName = tableName
0374 def __getattr__(self, attr):
0375 if attr.startswith('__'):
0376 raise AttributeError
0377 return self.FieldClass(self.tableName, attr)
0378 def __sqlrepr__(self, db):
0379 if isinstance(self.tableName, str):
0380 return self.tableName
0381 return sqlrepr(self.tableName, db)
0382 def execute(self, executor):
0383 raise ValueError, "Tables don't have values"
0384
0385class SQLObjectTable(Table):
0386 FieldClass = SQLObjectField
0387 UnicodeFieldClass = UnicodeField
0388
0389 def __init__(self, soClass):
0390 self.soClass = soClass
0391 assert soClass.sqlmeta.table, (
0392 "Bad table name in class %r: %r"
0393 % (soClass, soClass.sqlmeta.table))
0394 Table.__init__(self, soClass.sqlmeta.table)
0395
0396 def __getattr__(self, attr):
0397 if attr.startswith('__'):
0398 raise AttributeError
0399 if attr == 'id':
0400 return self._getattrFromID(attr)
0401 elif attr in self.soClass.sqlmeta.columns:
0402 column = self.soClass.sqlmeta.columns[attr]
0403 if hasattr(column, "dbEncoding"):
0404 return self._getattrFromUnicodeColumn(column, attr)
0405 else:
0406 return self._getattrFromColumn(column, attr)
0407 elif attr+'ID' in [k for (k, v) in self.soClass.sqlmeta.columns.items() if v.foreignKey]:
0408 attr += 'ID'
0409 column = self.soClass.sqlmeta.columns[attr]
0410 return self._getattrFromColumn(column, attr)
0411 else:
0412 raise AttributeError("%s instance has no attribute '%s'" % (self.soClass.__name__, attr))
0413
0414 def _getattrFromID(self, attr):
0415 return self.FieldClass(self.tableName, self.soClass.sqlmeta.idName, attr)
0416
0417 def _getattrFromColumn(self, column, attr):
0418 return self.FieldClass(self.tableName, column.dbName, attr)
0419
0420 def _getattrFromUnicodeColumn(self, column, attr):
0421 return self.UnicodeFieldClass(self.tableName, column.dbName, attr, column)
0422
0423class TableSpace:
0424 TableClass = Table
0425
0426 def __getattr__(self, attr):
0427 if attr.startswith('__'):
0428 raise AttributeError
0429 return self.TableClass(attr)
0430
0431class ConstantSpace:
0432 def __getattr__(self, attr):
0433 if attr.startswith('__'):
0434 raise AttributeError
0435 return SQLConstant(attr)
0436
0437
0438
0439
0440
0441
0442class AliasField(Field):
0443 def __init__(self, tableName, fieldName, alias, aliasTable):
0444 Field.__init__(self, tableName, fieldName)
0445 self.alias = alias
0446 self.aliasTable = aliasTable
0447
0448 def __sqlrepr__(self, db):
0449 fieldName = self.fieldName
0450 if isinstance(fieldName, SQLExpression):
0451 fieldName = sqlrepr(fieldName, db)
0452 return self.alias + "." + fieldName
0453
0454 def tablesUsedImmediate(self):
0455 return [self.aliasTable]
0456
0457class AliasTable(Table):
0458 as_string = ''
0459 FieldClass = AliasField
0460
0461 _alias_lock = threading.Lock()
0462 _alias_counter = 0
0463
0464 def __init__(self, table, alias=None):
0465 if hasattr(table, "sqlmeta"):
0466 tableName = SQLConstant(table.sqlmeta.table)
0467 elif isinstance(table, (Select,Union)):
0468 assert alias is not None, "Alias name cannot be constructed from Select instances, please provide 'alias' kw."
0469 tableName = Subquery('', table)
0470 table = None
0471 else:
0472 tableName = SQLConstant(table)
0473 table = None
0474 Table.__init__(self, tableName)
0475 self.table = table
0476 if alias is None:
0477 self._alias_lock.acquire()
0478 try:
0479 AliasTable._alias_counter += 1
0480 alias = "%s_alias%d" % (tableName, AliasTable._alias_counter)
0481 finally:
0482 self._alias_lock.release()
0483 self.alias = alias
0484
0485 def __getattr__(self, attr):
0486 if attr.startswith('__'):
0487 raise AttributeError
0488 if self.table:
0489 attr = getattr