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