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## Constants
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    # some db drivers return unicode column names
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## Expression generation
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# Converter for SQLExpression instances
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## Namespaces
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## Table aliases
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