1"""
2sqlobject.sqlbuilder
3--------------------
4
5:author: Ian Bicking <ianb@colorstudy.com>
6
7Builds SQL expressions from normal Python expressions.
8
9Disclaimer
10----------
11
12This program is free software; you can redistribute it and/or modify
13it under the terms of the GNU Lesser General Public License as
14published by the Free Software Foundation; either version 2.1 of the
15License, or (at your option any later version.
16
17This program is distributed in the hope that it will be useful,
18but WITHOUT ANY WARRANTY; without even the implied warranty of
19MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
20GNU General Public License for more details.
21
22You should have received a copy of the GNU Lesser General Public
23License along with this program; if not, write to the Free Software
24Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301,
25USA.
26
27Instructions
28------------
29
30To begin a SQL expression, you must use some sort of SQL object -- a
31field, table, or SQL statement (``SELECT``, ``INSERT``, etc.)  You can
32then use normal operators, with the exception of: `and`, `or`, `not`,
33and `in`.  You can use the `AND`, `OR`, `NOT`, and `IN` functions
34instead, or you can also use `&`, `|`, and `~` for `and`, `or`, and
35`not` respectively (however -- the precidence for these operators
36doesn't work as you would want, so you must use many parenthesis).
37
38To create a sql field, table, or constant/function, use the namespaces
39`table`, `const`, and `func`.  For instance, ``table.address`` refers
40to the ``address`` table, and ``table.address.state`` refers to the
41``state`` field in the address table.  ``const.NULL`` is the ``NULL``
42SQL constant, and ``func.NOW()`` is the ``NOW()`` function call
43(`const` and `func` are actually identicle, but the two names are
44provided for clarity).  Once you create this object, expressions
45formed with it will produce SQL statements.
46
47The ``sqlrepr(obj)`` function gets the SQL representation of these
48objects, as well as the proper SQL representation of basic Python
49types (None==NULL).
50
51There are a number of DB-specific SQL features that this does not
52implement.  There are a bunch of normal ANSI features also not present.
53
54See the bottom of this module for some examples, and run it (i.e.
55``python sql.py``) to see the results of those examples.
56
57"""
58
59########################################
60## Constants
61########################################
62
63import fnmatch
64import operator
65import re
66import threading
67import types
68import weakref
69
70import classregistry
71from converters import registerConverter, sqlrepr, quote_str, unquote_str
72
73
74class VersionError(Exception):
75    pass
76class NoDefault:
77    pass
78
79
80class SQLObjectState(object):
81    def __init__(self, soObject, connection=None):
82        self.soObject = weakref.proxy(soObject)
83        self.connection = connection
84
85
86safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
87def sqlIdentifier(obj):
88    # some db drivers return unicode column names
89    return isinstance(obj, basestring) and bool(safeSQLRE.search(obj.strip()))
90
91
92def execute(expr, executor):
93    if hasattr(expr, 'execute'):
94        return expr.execute(executor)
95    else:
96        return expr
97
98
99def _str_or_sqlrepr(expr, db):
100    if isinstance(expr, basestring):
101        return expr
102    return sqlrepr(expr, db)
103
104########################################
105## Expression generation
106########################################
107
108class SQLExpression:
109    def __add__(self, other):
110        return SQLOp("+", self, other)
111    def __radd__(self, other):
112        return SQLOp("+", other, self)
113    def __sub__(self, other):
114        return SQLOp("-", self, other)
115    def __rsub__(self, other):
116        return SQLOp("-", other, self)
117    def __mul__(self, other):
118        return SQLOp("*", self, other)
119    def __rmul__(self, other):
120        return SQLOp("*", other, self)
121    def __div__(self, other):
122        return SQLOp("/", self, other)
123    def __rdiv__(self, other):
124        return SQLOp("/", other, self)
125    def __pos__(self):
126        return SQLPrefix("+", self)
127    def __neg__(self):
128        return SQLPrefix("-", self)
129    def __pow__(self, other):
130        return SQLConstant("POW")(self, other)
131    def __rpow__(self, other):
132        return SQLConstant("POW")(other, self)
133    def __abs__(self):
134        return SQLConstant("ABS")(self)
135    def __mod__(self, other):
136        return SQLModulo(self, other)
137    def __rmod__(self, other):
138        return SQLConstant("MOD")(other, self)
139
140    def __lt__(self, other):
141        return SQLOp("<", self, other)
142    def __le__(self, other):
143        return SQLOp("<=", self, other)
144    def __gt__(self, other):
145        return SQLOp(">", self, other)
146    def __ge__(self, other):
147        return SQLOp(">=", self, other)
148    def __eq__(self, other):
149        if other is None:
150            return ISNULL(self)
151        else:
152            return SQLOp("=", self, other)
153    def __ne__(self, other):
154        if other is None:
155            return ISNOTNULL(self)
156        else:
157            return SQLOp("<>", self, other)
158
159    def __and__(self, other):
160        return SQLOp("AND", self, other)
161    def __rand__(self, other):
162        return SQLOp("AND", other, self)
163    def __or__(self, other):
164        return SQLOp("OR", self, other)
165    def __ror__(self, other):
166        return SQLOp("OR", other, self)
167    def __invert__(self):
168        return SQLPrefix("NOT", self)
169
170    def __call__(self, *args):
171        return SQLCall(self, args)
172
173    def __repr__(self):
174        try:
175            return self.__sqlrepr__(None)
176        except AssertionError:
177            return '<%s %s>' % (
178                self.__class__.__name__, hex(id(self))[2:])
179
180    def __str__(self):
181        return repr(self)
182
183    def __cmp__(self, other):
184        raise VersionError, "Python 2.1+ required"
185    def __rcmp__(self, other):
186        raise VersionError, "Python 2.1+ required"
187
188    def startswith(self, s):
189        return STARTSWITH(self, s)
190    def endswith(self, s):
191        return ENDSWITH(self, s)
192    def contains(self, s):
193        return CONTAINSSTRING(self, s)
194
195    def components(self):
196        return []
197
198    def tablesUsed(self, db):
199        return self.tablesUsedSet(db)
200    def tablesUsedSet(self, db):
201        tables = set()
202        for table in self.tablesUsedImmediate():
203            if hasattr(table, '__sqlrepr__'):
204                table = sqlrepr(table, db)
205            tables.add(table)
206        for component in self.components():
207            tables.update(tablesUsedSet(component, db))
208        return tables
209    def tablesUsedImmediate(self):
210        return []
211
212#######################################
213# Converter for SQLExpression instances
214#######################################
215
216def SQLExprConverter(value, db):
217    return value.__sqlrepr__()
218
219registerConverter(SQLExpression, SQLExprConverter)
220
221def tablesUsedSet(obj, db):
222    if hasattr(obj, "tablesUsedSet"):
223        return obj.tablesUsedSet(db)
224    else:
225        return {}
226
227operatorMap = {
228    "+": operator.add,
229    "/": operator.div,
230    "-": operator.sub,
231    "*": operator.mul,
232    "<": operator.lt,
233    "<=": operator.le,
234    "=": operator.eq,
235    "!=": operator.ne,
236    ">=": operator.ge,
237    ">": operator.gt,
238    "IN": operator.contains,
239    "IS": operator.eq,
240    }
241
242class SQLOp(SQLExpression):
243    def __init__(self, op, expr1, expr2):
244        self.op = op.upper()
245        self.expr1 = expr1
246        self.expr2 = expr2
247    def __sqlrepr__(self, db):
248        s1 = sqlrepr(self.expr1, db)
249        s2 = sqlrepr(self.expr2, db)
250        if s1[0] != '(' and s1 != 'NULL':
251            s1 = '(' + s1 + ')'
252        if s2[0] != '(' and s2 != 'NULL':
253            s2 = '(' + s2 + ')'
254        return "(%s %s %s)" % (s1, self.op, s2)
255    def components(self):
256        return [self.expr1, self.expr2]
257    def execute(self, executor):
258        if self.op == "AND":
259            return execute(self.expr1, executor) \
260                   and execute(self.expr2, executor)
261        elif self.op == "OR":
262            return execute(self.expr1, executor) \
263                   or execute(self.expr2, executor)
264        else:
265            return operatorMap[self.op.upper()](execute(self.expr1, executor),
266                                                execute(self.expr2, executor))
267
268class SQLModulo(SQLOp):
269    def __init__(self, expr1, expr2):
270        SQLOp.__init__(self, '%', expr1, expr2)
271    def __sqlrepr__(self, db):
272        if db == 'sqlite':
273            return SQLOp.__sqlrepr__(self, db)
274        s1 = sqlrepr(self.expr1, db)
275        s2 = sqlrepr(self.expr2, db)
276        return "MOD(%s, %s)" % (s1, s2)
277
278registerConverter(SQLOp, SQLExprConverter)
279registerConverter(SQLModulo, SQLExprConverter)
280
281class SQLCall(SQLExpression):
282    def __init__(self, expr, args):
283        self.expr = expr
284        self.args = args
285    def __sqlrepr__(self, db):
286        return "%s%s" % (sqlrepr(self.expr, db), sqlrepr(self.args, db))
287    def components(self):
288        return [self.expr] + list(self.args)
289    def execute(self, executor):
290        raise ValueError, "I don't yet know how to locally execute functions"
291
292registerConverter(SQLCall, SQLExprConverter)
293
294class SQLPrefix(SQLExpression):
295    def __init__(self, prefix, expr):
296        self.prefix = prefix
297        self.expr = expr
298    def __sqlrepr__(self, db):
299        return "%s %s" % (self.prefix, sqlrepr(self.expr, db))
300    def components(self):
301        return [self.expr]
302    def execute(self, executor):
303        expr = execute(self.expr, executor)
304        if prefix == "+":
305            return expr
306        elif prefix == "-":
307            return -expr
308        elif prefix.upper() == "NOT":
309            return not expr
310
311registerConverter(SQLPrefix, SQLExprConverter)
312
313class SQLConstant(SQLExpression):
314    def __init__(self, const):
315        self.const = const
316    def __sqlrepr__(self, db):
317        return self.const
318    def execute(self, executor):
319        raise ValueError, "I don't yet know how to execute SQL constants"
320
321registerConverter(SQLConstant, SQLExprConverter)
322
323class SQLTrueClauseClass(SQLExpression):
324    def __sqlrepr__(self, db):
325        return "1 = 1"
326    def execute(self, executor):
327        return 1
328
329SQLTrueClause = SQLTrueClauseClass()
330
331registerConverter(SQLTrueClauseClass, SQLExprConverter)
332
333########################################
334## Namespaces
335########################################
336
337class Field(SQLExpression):
338    def __init__(self, tableName, fieldName):
339        self.tableName = tableName
340        self.fieldName = fieldName
341    def __sqlrepr__(self, db):
342        return self.tableName + "." + self.fieldName
343    def tablesUsedImmediate(self):
344        return [self.tableName]
345    def execute(self, executor):
346        return executor.field(self.tableName, self.fieldName)
347
348class SQLObjectField(Field):
349    def __init__(self, tableName, fieldName, original, soClass, column):
350        Field.__init__(self, tableName, fieldName)
351        self.original = original
352        self.soClass = soClass
353        self.column = column
354    def _from_python(self, value):
355        column = self.column
356        if not isinstance(value, SQLExpression) and column and column.from_python:
357            value = column.from_python(value, SQLObjectState(self.soClass))
358        return value
359    def __eq__(self, other):
360        if other is None:
361            return ISNULL(self)
362        other = self._from_python(other)
363        return SQLOp('=', self, other)
364    def __ne__(self, other):
365        if other is None:
366            return ISNOTNULL(self)
367        other = self._from_python(other)
368        return SQLOp('<>', self, other)
369    def startswith(self, s):
370        s = self._from_python(s)
371        return STARTSWITH(self, s)
372    def endswith(self, s):
373        s = self._from_python(s)
374        return ENDSWITH(self, s)
375    def contains(self, s):
376        s = self._from_python(s)
377        return CONTAINSSTRING(self, s)
378
379registerConverter(SQLObjectField, SQLExprConverter)
380
381
382class Table(SQLExpression):
383    FieldClass = Field
384
385    def __init__(self, tableName):
386        self.tableName = tableName
387    def __getattr__(self, attr):
388        if attr.startswith('__'):
389            raise AttributeError
390        return self.FieldClass(self.tableName, attr)
391    def __sqlrepr__(self, db):
392        return _str_or_sqlrepr(self.tableName, db)
393    def execute(self, executor):
394        raise ValueError, "Tables don't have values"
395
396class SQLObjectTable(Table):
397    FieldClass = SQLObjectField
398
399    def __init__(self, soClass):
400        self.soClass = soClass
401        assert soClass.sqlmeta.table, (
402            "Bad table name in class %r: %r"
403            % (soClass, soClass.sqlmeta.table))
404        Table.__init__(self, soClass.sqlmeta.table)
405
406    def __getattr__(self, attr):
407        if attr.startswith('__'):
408            raise AttributeError
409        if attr == 'id':
410            return self._getattrFromID(attr)
411        elif attr in self.soClass.sqlmeta.columns:
412            column = self.soClass.sqlmeta.columns[attr]
413            return self._getattrFromColumn(column, attr)
414        elif attr+'ID' in [k for (k, v) in self.soClass.sqlmeta.columns.items() if v.foreignKey]:
415            attr += 'ID'
416            column = self.soClass.sqlmeta.columns[attr]
417            return self._getattrFromColumn(column, attr)
418        else:
419            raise AttributeError("%s instance has no attribute '%s'" % (self.soClass.__name__, attr))
420
421    def _getattrFromID(self, attr):
422        return self.FieldClass(self.tableName, self.soClass.sqlmeta.idName, attr, self.soClass, None)
423
424    def _getattrFromColumn(self, column, attr):
425        return self.FieldClass(self.tableName, column.dbName, attr, self.soClass, column)
426
427class SQLObjectTableWithJoins(SQLObjectTable):
428
429    def __getattr__(self, attr):
430        if attr+'ID' in [k for (k, v) in self.soClass.sqlmeta.columns.items() if v.foreignKey]:
431            column = self.soClass.sqlmeta.columns[attr+'ID']
432            return self._getattrFromForeignKey(column, attr)
433        elif attr in [x.joinMethodName for x in self.soClass.sqlmeta.joins]:
434            join = [x for x in self.soClass.sqlmeta.joins if x.joinMethodName == attr][0]
435            return self._getattrFromJoin(join, attr)
436        else:
437            return SQLObjectTable.__getattr__(self, attr)
438
439    def _getattrFromForeignKey(self, column, attr):
440        ret =  getattr(self, column.name) == \
441              getattr(self.soClass, '_SO_class_'+column.foreignKey).q.id
442        return ret
443
444    def _getattrFromJoin(self, join, attr):
445        if hasattr(join, 'otherColumn'):
446            return AND(join.otherClass.q.id == Field(join.intermediateTable, join.otherColumn),
447                            Field(join.intermediateTable, join.joinColumn) == self.soClass.q.id)
448        else:
449            return getattr(join.otherClass.q, join.joinColumn)==self.soClass.q.id
450
451class TableSpace:
452    TableClass = Table
453
454    def __getattr__(self, attr):
455        if attr.startswith('__'):
456            raise AttributeError
457        return self.TableClass(attr)
458
459class ConstantSpace:
460    def __getattr__(self, attr):
461        if attr.startswith('__'):
462            raise AttributeError
463        return SQLConstant(attr)
464
465
466########################################
467## Table aliases
468########################################
469
470class AliasField(Field):
471    def __init__(self, tableName, fieldName, alias, aliasTable):
472        Field.__init__(self, tableName, fieldName)
473        self.alias = alias
474        self.aliasTable = aliasTable
475
476    def __sqlrepr__(self, db):
477        fieldName = self.fieldName
478        if isinstance(fieldName, SQLExpression):
479            fieldName = sqlrepr(fieldName, db)
480        return self.alias + "." + fieldName
481
482    def tablesUsedImmediate(self):
483        return [self.aliasTable]
484
485class AliasTable(Table):
486    as_string = '' # set it to "AS" if your database requires it
487    FieldClass = AliasField
488
489    _alias_lock = threading.Lock()
490    _alias_counter = 0
491
492    def __init__(self, table, alias=None):
493        if hasattr(table, "sqlmeta"):
494            tableName = SQLConstant(table.sqlmeta.table)
495        elif isinstance(table, (Select, Union)):
496            assert alias is not None, "Alias name cannot be constructed from Select instances, please provide 'alias' kw."
497            tableName = Subquery('', table)
498            table = None
499        else:
500            tableName = SQLConstant(table)
501            table = None
502        Table.__init__(self, tableName)
503        self.table = table
504        if alias is None:
505            self._alias_lock.acquire()
506            try:
507                AliasTable._alias_counter += 1
508                alias = "%s_alias%d" % (tableName, AliasTable._alias_counter)
509            finally:
510                self._alias_lock.release()
511        self.alias = alias
512
513    def __getattr__(self, attr):
514        if attr.startswith('__'):
515            raise AttributeError
516        if self.table:
517            attr = getattr(self.table.q, attr).fieldName
518        return self.FieldClass(self.tableName, attr, self.alias, self)
519
520    def __sqlrepr__(self, db):
521        return "%s %s %s" % (sqlrepr(self.tableName, db), self.as_string, self.alias)
522
523class Alias(SQLExpression):
524    def __init__(self, table, alias=None):
525        self.q = AliasTable(table, alias)
526
527    def __sqlrepr__(self, db):
528        return sqlrepr(self.q, db)
529
530    def components(self):
531        return [self.q]
532
533
534class Union(SQLExpression):
535    def __init__(self, *tables):
536        tabs = []
537        for t in tables:
538            if not isinstance(t, SQLExpression) and hasattr(t, 'sqlmeta'):
539                t = t.sqlmeta.table
540                if isinstance(t, Alias):
541                    t = t.q
542                if isinstance(t, Table):
543                    t = t.tableName
544                if not isinstance(t, SQLExpression):
545                    t = SQLConstant(t)
546            tabs.append(t)
547        self.tables = tabs
548
549    def __sqlrepr__(self, db):
550        return " UNION ".join([str(sqlrepr(t, db)) for t in self.tables])
551
552########################################
553## SQL Statements
554########################################
555
556class Select(SQLExpression):
557    def __init__(self, items=NoDefault, where=NoDefault, groupBy=NoDefault,
558                 having=NoDefault, orderBy=NoDefault, limit=NoDefault,
559                 join=NoDefault, lazyColumns=False, distinct=False,
560                 start=0, end=None, reversed=False, forUpdate=False,
561                 clause=NoDefault, staticTables=NoDefault, distinctOn=NoDefault):
562        self.ops = {}
563        if not isinstance(items, (list, tuple, types.GeneratorType)):
564            items = [items]
565        if clause is NoDefault and where is not NoDefault:
566            clause = where
567        if staticTables is NoDefault:
568            staticTables = []
569        self.ops['items'] = items
570        self.ops['clause'] = clause
571        self.ops['groupBy'] = groupBy
572        self.ops['having'] = having
573        self.ops['orderBy'] = orderBy
574        self.ops['limit'] = limit
575        self.ops['join']  = join
576        self.ops['lazyColumns'] = lazyColumns
577        self.ops['distinct'] = distinct
578        self.ops['distinctOn'] = distinctOn
579        self.ops['start'] = start
580        self.ops['end'] = end
581        self.ops['reversed'] = reversed
582        self.ops['forUpdate'] = forUpdate
583        self.ops['staticTables'] = staticTables
584
585    def clone(self, **newOps):
586        ops = self.ops.copy()
587        ops.update(newOps)
588        return self.__class__(**ops)
589
590    def newItems(self, items):
591        return self.clone(items=items)
592
593    def newClause(self, new_clause):
594        return self.clone(clause=new_clause)
595
596    def orderBy(self, orderBy):
597        return self.clone(orderBy=orderBy)
598
599    def unlimited(self):
600        return self.clone(limit=NoDefault, start=0, end=None)
601
602    def limit(self, limit):
603        self.clone(limit=limit)
604
605    def lazyColumns(self, value):
606        return self.clone(lazyColumns=value)
607
608    def reversed(self):
609        return self.clone(reversed=not self.ops.get('reversed', False))
610
611    def distinct(self):
612        return self.clone(distinct=True)
613
614    def filter(self, filter_clause):
615        if filter_clause is None:
616            # None doesn't filter anything, it's just a no-op:
617            return self
618        clause = self.ops['clause']
619        if isinstance(clause, basestring):
620            clause = SQLConstant('(%s)' % clause)
621        return self.newClause(AND(clause, filter_clause))
622
623    def __sqlrepr__(self, db):
624
625        select = "SELECT"
626        if self.ops['distinct']:
627            select += " DISTINCT"
628            if self.ops['distinctOn'] is not NoDefault:
629                select += " ON(%s)" % _str_or_sqlrepr(self.ops['distinctOn'], db)
630        if not self.ops['lazyColumns']:
631            select += " %s" % ", ".join([str(_str_or_sqlrepr(v, db)) for v in self.ops['items']])
632        else:
633            select += " %s" % _str_or_sqlrepr(self.ops['items'][0], db)
634
635        join = []
636        join_str = ''
637        if self.ops['join'] is not NoDefault and self.ops['join'] is not None:
638            _join = self.ops['join']
639            if isinstance(_join, str):
640                join_str = " " + _join
641            elif isinstance(_join, SQLJoin):
642                join.append(_join)
643            else:
644                join.extend(_join)
645        tables = set()
646        for x in self.ops['staticTables']:
647            if isinstance(x, SQLExpression):
648                x = sqlrepr(x, db)
649            tables.add(x)
650        things = list(self.ops['items']) + join
651        if self.ops['clause'] is not NoDefault:
652            things.append(self.ops['clause'])
653        for thing in things:
654            if isinstance(thing, SQLExpression):
655                tables.update(tablesUsedSet(thing, db))
656        for j in join:
657            t1 = _str_or_sqlrepr(j.table1, db)
658            if t1 in tables: tables.remove(t1)
659            t2 = _str_or_sqlrepr(j.table2, db)
660            if t2 in tables: tables.remove(t2)
661        if tables:
662            select += " FROM %s" % ", ".join(tables)
663        elif join:
664            select += " FROM"
665        tablesYet = tables
666        for j in join:
667            if tablesYet and j.table1:
668                sep = ", "
669            else:
670                sep = " "
671            select += sep + sqlrepr(j, db)
672            tablesYet = True
673
674        if join_str:
675            select += join_str
676
677        if self.ops['clause'] is not NoDefault:
678            select += " WHERE %s" % _str_or_sqlrepr(self.ops['clause'], db)
679        if self.ops['groupBy'] is not NoDefault:
680            groupBy = _str_or_sqlrepr(self.ops['groupBy'], db)
681            if isinstance(self.ops['groupBy'], (list, tuple)):
682                groupBy = groupBy[1:-1] # Remove parens
683            select += " GROUP BY %s" % groupBy
684        if self.ops['having'] is not NoDefault:
685            select += " HAVING %s" % _str_or_sqlrepr(self.ops['having'], db)
686        if self.ops['orderBy'] is not NoDefault and self.ops['orderBy'] is not None:
687            orderBy = self.ops['orderBy']
688            if self.ops['reversed']:
689                reverser = DESC
690            else:
691                reverser = lambda x: x
692            if isinstance(orderBy, (list, tuple)):
693                select += " ORDER BY %s" % ", ".join([_str_or_sqlrepr(reverser(x), db) for x in orderBy])
694            else:
695                select += " ORDER BY %s" % _str_or_sqlrepr(reverser(orderBy), db)
696        start, end = self.ops['start'], self.ops['end']
697        if self.ops['limit'] is not NoDefault:
698            end = start + self.ops['limit']
699        if start or end:
700            from dbconnection import dbConnectionForScheme
701            select = dbConnectionForScheme(db)._queryAddLimitOffset(select, start, end)
702        if self.ops['forUpdate']:
703            select += " FOR UPDATE"
704        return select
705
706registerConverter(Select, SQLExprConverter)
707
708class Insert(SQLExpression):
709    def __init__(self, table, valueList=None, values=None, template=NoDefault):
710        self.template = template
711        self.table = table
712        if valueList:
713            if values:
714                raise TypeError, "You may only give valueList *or* values"
715            self.valueList = valueList
716        else:
717            self.valueList = [values]
718    def __sqlrepr__(self, db):
719        if not self.valueList:
720            return ''
721        insert = "INSERT INTO %s" % self.table
722        allowNonDict = True
723        template = self.template
724        if (template is NoDefault) and isinstance(self.valueList[0], dict):
725            template = self.valueList[0].keys()
726            allowNonDict = False
727        if template is not NoDefault:
728            insert += " (%s)" % ", ".join(template)
729        insert += " VALUES "
730        listToJoin = []
731        listToJoin_app = listToJoin.append
732        for value in self.valueList:
733            if isinstance(value, dict):
734                if template is NoDefault:
735                    raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value)
736                value = dictToList(template, value)
737            elif not allowNonDict:
738                raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value)
739            listToJoin_app("(%s)" % ", ".join([sqlrepr(v, db) for v in value]))
740        insert = "%s%s" % (insert, ", ".join(listToJoin))
741        return insert
742
743registerConverter(Insert, SQLExprConverter)
744
745def dictToList(template, dict):
746    list = []
747    for key in template:
748        list.append(dict[key])
749    if len(dict.keys()) > len(template):
750        raise TypeError, "Extra entries in dictionary that aren't asked for in template (template=%s, dict=%s)" % (repr(template), repr(dict))
751    return list
752
753class Update(SQLExpression):
754    def __init__(self, table, values, template=NoDefault, where=NoDefault):
755        self.table = table
756        self.values = values
757        self.template = template
758        self.whereClause = where
759    def __sqlrepr__(self, db):
760        update = "%s %s" % (self.sqlName(), self.table)
761        update += " SET"
762        first = True
763        if self.template is not NoDefault:
764            for i in range(len(self.template)):
765                if first:
766                    first = False
767                else:
768                    update += ","
769                update += " %s=%s" % (self.template[i], sqlrepr(self.values[i], db))
770        else:
771            for key, value in self.values.items():
772                if first:
773                    first = False
774                else:
775                    update += ","
776                update += " %s=%s" % (key, sqlrepr(value, db))
777        if self.whereClause is not NoDefault:
778            update += " WHERE %s" % _str_or_sqlrepr(self.whereClause, db)
779        return update
780    def sqlName(self):
781        return "UPDATE"
782
783registerConverter(Update, SQLExprConverter)
784
785class Delete(SQLExpression):
786    """To be safe, this will signal an error if there is no where clause,
787    unless you pass in where=None to the constructor."""
788    def __init__(self, table, where=NoDefault):
789        self.table = table
790        if where is NoDefault:
791            raise TypeError, "You must give a where clause or pass in None to indicate no where clause"
792        self.whereClause = where
793    def __sqlrepr__(self, db):
794        whereClause = self.whereClause
795        if whereClause is None:
796            return "DELETE FROM %s" % self.table
797        whereClause = _str_or_sqlrepr(whereClause, db)
798        return "DELETE FROM %s WHERE %s" % (self.table, whereClause)
799
800registerConverter(Delete, SQLExprConverter)
801
802class Replace(Update):
803    def sqlName(self):
804        return "REPLACE"
805
806registerConverter(Replace, SQLExprConverter)
807
808########################################
809## SQL Builtins
810########################################
811
812class DESC(SQLExpression):
813
814    def __init__(self, expr):
815        self.expr = expr
816
817    def __sqlrepr__(self, db):
818        if isinstance(self.expr, DESC):
819            return sqlrepr(self.expr.expr, db)
820        return '%s DESC' % sqlrepr(self.expr, db)
821
822def AND(*ops):
823    if not ops:
824        return None
825    op1 = ops[0]
826    ops = ops[1:]
827    if ops:
828        return SQLOp("AND", op1, AND(*ops))
829    else:
830        return op1
831
832def OR(*ops):
833    if not ops:
834        return None
835    op1 = ops[0]
836    ops = ops[1:]
837    if ops:
838        return SQLOp("OR", op1, OR(*ops))
839    else:
840        return op1
841
842def NOT(op):
843    return SQLPrefix("NOT", op)
844
845def _IN(item, list):
846    return SQLOp("IN", item, list)
847
848def IN(item, list):
849    from sresults import SelectResults # Import here to avoid circular import
850    if isinstance(list, SelectResults):
851        query = list.queryForSelect()
852        query.ops['items'] = [list.sourceClass.q.id]
853        list = query
854    if isinstance(list, Select):
855        return INSubquery(item, list)
856    else:
857        return _IN(item, list)
858
859def NOTIN(item, list):
860    if isinstance(list, Select):
861        return NOTINSubquery(item, list)
862    else:
863        return NOT(_IN(item, list))
864
865def STARTSWITH(expr, pattern):
866    return LIKE(expr, _LikeQuoted(pattern) + '%', escape='\\')
867
868def ENDSWITH(expr, pattern):
869    return LIKE(expr, '%' + _LikeQuoted(pattern), escape='\\')
870
871def CONTAINSSTRING(expr, pattern):
872    return LIKE(expr, '%' + _LikeQuoted(pattern) + '%', escape='\\')
873
874def ISNULL(expr):
875    return SQLOp("IS", expr, None)
876
877def ISNOTNULL(expr):
878    return SQLOp("IS NOT", expr, None)
879
880class ColumnAS(SQLOp):
881    ''' Just like SQLOp('AS', expr, name) except without the parentheses '''
882    def __init__(self, expr, name):
883        if isinstance(name, basestring):
884            name = SQLConstant(name)
885        SQLOp.__init__(self, 'AS', expr, name)
886    def __sqlrepr__(self, db):
887        return "%s %s %s" % (sqlrepr(self.expr1, db), self.op, sqlrepr(self.expr2, db))
888
889class _LikeQuoted:
890    # It assumes prefix and postfix are strings; usually just a percent sign.
891
892    # @@: I'm not sure what the quoting rules really are for all the
893    # databases
894
895    def __init__(self, expr):
896        self.expr = expr
897        self.prefix = ''
898        self.postfix = ''
899
900    def __radd__(self, s):
901        self.prefix = s + self.prefix
902        return self
903
904    def __add__(self, s):
905        self.postfix += s
906        return self
907
908    def __sqlrepr__(self, db):
909        s = self.expr
910        if isinstance(s, SQLExpression):
911            values = []
912            if self.prefix:
913                values.append(quote_str(self.prefix, db))
914            s = _quote_like_special(sqlrepr(s, db), db)
915            values.append(s)
916            if self.postfix:
917                values.append(quote_str(self.postfix, db))
918            if db == "mysql":
919                return "CONCAT(%s)" % ", ".join(values)
920            else:
921                return " || ".join(values)
922        elif isinstance(s, basestring):
923            s = _quote_like_special(unquote_str(sqlrepr(s, db)), db)
924            return quote_str("%s%s%s" % (self.prefix, s, self.postfix), db)
925        else:
926           raise TypeError, "expected str, unicode or SQLExpression, got %s" % type(s)
927
928def _quote_like_special(s, db):
929    if db in ('postgres', 'rdbhost'):
930        escape = r'\\'
931    else:
932        escape = '\\'
933    s = s.replace('\\', r'\\').replace('%', escape+'%').replace('_', escape+'_')
934    return s
935
936########################################
937## SQL JOINs
938########################################
939
940class SQLJoin(SQLExpression):
941    def __init__(self, table1, table2, op=','):
942        if hasattr(table1, 'sqlmeta'):
943            table1 = table1.sqlmeta.table
944        if hasattr(table2, 'sqlmeta'):
945            table2 = table2.sqlmeta.table
946        if isinstance(table1, str):
947            table1 = SQLConstant(table1)
948        if isinstance(table2, str):
949            table2 = SQLConstant(table2)
950        self.table1 = table1
951        self.table2 = table2
952        self.op = op
953
954    def __sqlrepr__(self, db):
955        if self.table1:
956            return "%s%s %s" % (sqlrepr(self.table1, db), self.op, sqlrepr(self.table2, db))
957        else:
958            return "%s %s" % (self.op, sqlrepr(self.table2, db))
959
960registerConverter(SQLJoin, SQLExprConverter)
961
962def JOIN(table1, table2):
963    return SQLJoin(table1, table2, " JOIN")
964
965def INNERJOIN(table1, table2):
966    return SQLJoin(table1, table2, " INNER JOIN")
967
968def CROSSJOIN(table1, table2):
969    return SQLJoin(table1, table2, " CROSS JOIN")
970
971def STRAIGHTJOIN(table1, table2):
972    return SQLJoin(table1, table2, " STRAIGHT JOIN")
973
974def LEFTJOIN(table1, table2):
975    return SQLJoin(table1, table2, " LEFT JOIN")
976
977def LEFTOUTERJOIN(table1, table2):
978    return SQLJoin(table1, table2, " LEFT OUTER JOIN")
979
980def NATURALJOIN(table1, table2):
981    return SQLJoin(table1, table2, " NATURAL JOIN")
982
983def NATURALLEFTJOIN(table1, table2):
984    return SQLJoin(table1, table2, " NATURAL LEFT JOIN")
985
986def NATURALLEFTOUTERJOIN(table1, table2):
987    return SQLJoin(table1, table2, " NATURAL LEFT OUTER JOIN")
988
989def RIGHTJOIN(table1, table2):
990    return SQLJoin(table1, table2, " RIGHT JOIN")
991
992def RIGHTOUTERJOIN(table1, table2):
993    return SQLJoin(table1, table2, " RIGHT OUTER JOIN")
994
995def NATURALRIGHTJOIN(table1, table2):
996    return SQLJoin(table1, table2, " NATURAL RIGHT JOIN")
997
998def NATURALRIGHTOUTERJOIN(table1, table2):
999    return SQLJoin(table1, table2, " NATURAL RIGHT OUTER JOIN")
1000
1001def FULLJOIN(table1, table2):
1002    return SQLJoin(table1, table2, " FULL JOIN")
1003
1004def FULLOUTERJOIN(table1, table2):
1005    return SQLJoin(table1, table2, " FULL OUTER JOIN")
1006
1007def NATURALFULLJOIN(table1, table2):
1008    return SQLJoin(table1, table2, " NATURAL FULL JOIN")
1009
1010def NATURALFULLOUTERJOIN(table1, table2):
1011    return SQLJoin(table1, table2, " NATURAL FULL OUTER JOIN")
1012
1013class SQLJoinConditional(SQLJoin):
1014    """Conditional JOIN"""
1015    def __init__(self, table1, table2, op, on_condition=None, using_columns=None):
1016        """For condition you must give on_condition or using_columns but not both
1017
1018            on_condition can be a string or SQLExpression, for example
1019                Table1.q.col1 == Table2.q.col2
1020            using_columns can be a string or a list of columns, e.g.
1021                (Table1.q.col1, Table2.q.col2)
1022        """
1023        if not on_condition and not using_columns:
1024            raise TypeError, "You must give ON condition or USING columns"
1025        if on_condition and using_columns:
1026            raise TypeError, "You must give ON condition or USING columns but not both"
1027        SQLJoin.__init__(self, table1, table2, op)
1028        self.on_condition = on_condition
1029        self.using_columns = using_columns
1030
1031    def __sqlrepr__(self, db):
1032        if self.on_condition:
1033            on_condition = self.on_condition
1034            if hasattr(on_condition, "__sqlrepr__"):
1035                on_condition = sqlrepr(on_condition, db)
1036            join = "%s %s ON %s" % (self.op, sqlrepr(self.table2, db), on_condition)
1037            if self.table1:
1038                join = "%s %s" % (sqlrepr(self.table1, db), join)
1039            return join
1040        elif self.using_columns:
1041            using_columns = []
1042            for col in self.using_columns:
1043                if hasattr(col, "__sqlrepr__"):
1044                    col = sqlrepr(col, db)
1045                using_columns.append(col)
1046            using_columns = ", ".join(using_columns)
1047            join = "%s %s USING (%s)" % (self.op, sqlrepr(self.table2, db), using_columns)
1048            if self.table1:
1049                join = "%s %s" % (sqlrepr(self.table1, db), join)
1050            return join
1051        else:
1052            RuntimeError, "Impossible error"
1053
1054registerConverter(SQLJoinConditional, SQLExprConverter)
1055
1056def INNERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1057    return SQLJoinConditional(table1, table2, "INNER JOIN", on_condition, using_columns)
1058
1059def LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None):
1060    return SQLJoinConditional(table1, table2, "LEFT JOIN", on_condition, using_columns)
1061
1062def LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1063    return SQLJoinConditional(table1, table2, "LEFT OUTER JOIN", on_condition, using_columns)
1064
1065def RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None):
1066    return SQLJoinConditional(table1, table2, "RIGHT JOIN", on_condition, using_columns)
1067
1068def RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1069    return SQLJoinConditional(table1, table2, "RIGHT OUTER JOIN", on_condition, using_columns)
1070
1071def FULLJOINConditional(table1, table2, on_condition=None, using_columns=None):
1072    return SQLJoinConditional(table1, table2, "FULL JOIN", on_condition, using_columns)
1073
1074def FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
1075    return SQLJoinConditional(table1, table2, "FULL OUTER JOIN", on_condition, using_columns)
1076
1077class SQLJoinOn(SQLJoinConditional):
1078    """Conditional JOIN ON"""
1079    def __init__(self, table1, table2, op, on_condition):
1080        SQLJoinConditional.__init__(self, table1, table2, op, on_condition)
1081
1082registerConverter(SQLJoinOn, SQLExprConverter)
1083
1084class SQLJoinUsing(SQLJoinConditional):
1085    """Conditional JOIN USING"""
1086    def __init__(self, table1, table2, op, using_columns):
1087        SQLJoinConditional.__init__(self, table1, table2, op, None, using_columns)
1088
1089registerConverter(SQLJoinUsing, SQLExprConverter)
1090
1091def INNERJOINOn(table1, table2, on_condition):
1092    return SQLJoinOn(table1, table2, "INNER JOIN", on_condition)
1093
1094def LEFTJOINOn(table1, table2, on_condition):
1095    return SQLJoinOn(table1, table2, "LEFT JOIN", on_condition)
1096
1097def LEFTOUTERJOINOn(table1, table2, on_condition):
1098    return SQLJoinOn(table1, table2, "LEFT OUTER JOIN", on_condition)
1099
1100def RIGHTJOINOn(table1, table2, on_condition):
1101    return SQLJoinOn(table1, table2, "RIGHT JOIN", on_condition)
1102
1103def RIGHTOUTERJOINOn(table1, table2, on_condition):
1104    return SQLJoinOn(table1, table2, "RIGHT OUTER JOIN", on_condition)
1105
1106def FULLJOINOn(table1, table2, on_condition):
1107    return SQLJoinOn(table1, table2, "FULL JOIN", on_condition)
1108
1109def FULLOUTERJOINOn(table1, table2, on_condition):
1110    return SQLJoinOn(table1, table2, "FULL OUTER JOIN", on_condition)
1111
1112def INNERJOINUsing(table1, table2, using_columns):
1113    return SQLJoinUsing(table1, table2, "INNER JOIN", using_columns)
1114
1115def LEFTJOINUsing(table1, table2, using_columns):
1116    return SQLJoinUsing(table1, table2, "LEFT JOIN", using_columns)
1117
1118def LEFTOUTERJOINUsing(table1, table2, using_columns):
1119    return SQLJoinUsing(table1, table2, "LEFT OUTER JOIN", using_columns)
1120
1121def RIGHTJOINUsing(table1, table2, using_columns):
1122    return SQLJoinUsing(table1, table2, "RIGHT JOIN", using_columns)
1123
1124def RIGHTOUTERJOINUsing(table1, table2, using_columns):
1125    return SQLJoinUsing(table1, table2, "RIGHT OUTER JOIN", using_columns)
1126
1127def FULLJOINUsing(table1, table2, using_columns):
1128    return SQLJoinUsing(table1, table2, "FULL JOIN", using_columns)
1129
1130def FULLOUTERJOINUsing(table1, table2, using_columns):
1131    return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
1132
1133
1134########################################
1135## Subqueries (subselects)
1136########################################
1137
1138class OuterField(SQLObjectField):
1139    def tablesUsedImmediate(self):
1140        return []
1141
1142class OuterTable(SQLObjectTable):
1143    FieldClass = OuterField
1144
1145class Outer:
1146    def __init__(self, table):
1147        self.q = OuterTable(table)
1148
1149
1150class LIKE(SQLExpression):
1151    op = "LIKE"
1152
1153    def __init__(self, expr, string, escape=None):
1154        self.expr = expr
1155        self.string = string
1156        self.escape = escape
1157    def __sqlrepr__(self, db):
1158        escape = self.escape
1159        like = "%s %s (%s)" % (sqlrepr(self.expr, db), self.op, sqlrepr(self.string, db))
1160        if escape is None:
1161            return "(%s)" % like
1162        else:
1163            return "(%s ESCAPE %s)" % (like, sqlrepr(escape, db))
1164    def components(self):
1165        return [self.expr, self.string]
1166    def execute(self, executor):
1167        if not hasattr(self, '_regex'):
1168            # @@: Crude, not entirely accurate
1169            dest = self.string
1170            dest = dest.replace("%%", "\001")
1171            dest = dest.replace("*", "\002")
1172            dest = dest.replace("%", "*")
1173            dest = dest.replace("\001", "%")
1174            dest = dest.replace("\002", "[*]")
1175            self._regex = re.compile(fnmatch.translate(dest), re.I)
1176        return self._regex.search(execute(self.expr, executor))
1177
1178class RLIKE(LIKE):
1179    op = "RLIKE"
1180
1181    op_db = {
1182        'firebird': 'RLIKE',
1183        'maxdb': 'RLIKE',
1184        'mysql': 'RLIKE',
1185        'postgres': '~',
1186        'rdbhost': '~',
1187        'sqlite': 'REGEXP'
1188    }
1189
1190    def _get_op(self, db):
1191        return self.op_db.get(db, 'LIKE')
1192    def __sqlrepr__(self, db):
1193        return "(%s %s (%s))" % (
1194            sqlrepr(self.expr, db), self._get_op(db), sqlrepr(self.string, db)
1195        )
1196    def execute(self, executor):
1197        self.op = self._get_op(self.db)
1198        return LIKE.execute(self, executor)
1199
1200
1201class INSubquery(SQLExpression):
1202    op = "IN"
1203
1204    def __init__(self, item, subquery):
1205        self.item = item
1206        self.subquery = subquery
1207    def components(self):
1208        return [self.item]
1209    def __sqlrepr__(self, db):
1210        return "%s %s (%s)" % (sqlrepr(self.item, db), self.op, sqlrepr(self.subquery, db))
1211
1212class NOTINSubquery(INSubquery):
1213    op = "NOT IN"
1214
1215
1216class Subquery(SQLExpression):
1217    def __init__(self, op, subquery):
1218        self.op = op
1219        self.subquery = subquery
1220
1221    def __sqlrepr__(self, db):
1222        return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
1223
1224def EXISTS(subquery):
1225    return Subquery("EXISTS", subquery)
1226
1227def NOTEXISTS(subquery):
1228    return Subquery("NOT EXISTS", subquery)
1229
1230def SOME(subquery):
1231    return Subquery("SOME", subquery)
1232
1233def ANY(subquery):
1234    return Subquery("ANY", subquery)
1235
1236def ALL(subquery):
1237    return Subquery("ALL", subquery)
1238
1239####
1240
1241class ImportProxyField(SQLObjectField):
1242    def tablesUsedImmediate(self):
1243        return [str(self.tableName)]
1244
1245class ImportProxy(SQLExpression):
1246    '''Class to be used in column definitions that rely on other tables that might
1247        not yet be in a classregistry.
1248    '''
1249    FieldClass = ImportProxyField
1250    def __init__(self, clsName, registry=None):
1251        self.tableName = _DelayClass(self, clsName)
1252        self.sqlmeta = _Delay_proxy(table=_DelayClass(self, clsName))
1253        self.q = self
1254        self.soClass = None
1255        classregistry.registry(registry).addClassCallback(clsName, lambda foreign, me: setattr(me, 'soClass', foreign), self)
1256
1257    def __nonzero__(self):
1258        return True
1259
1260    def __getattr__(self, attr):
1261        if self.soClass is None:
1262            return _Delay(self, attr)
1263        return getattr(self.soClass.q, attr)
1264
1265class _Delay(SQLExpression):
1266    def __init__(self, proxy, attr):
1267        self.attr = attr
1268        self.proxy = proxy
1269
1270    def __sqlrepr__(self, db):
1271        if self.proxy.soClass is None:
1272            return '_DELAYED_' + self.attr
1273        val = self._resolve()
1274        if isinstance(val, SQLExpression):
1275            val = sqlrepr(val, db)
1276        return val
1277
1278    def tablesUsedImmediate(self):
1279        return getattr(self._resolve(), 'tablesUsedImmediate', lambda: [])()
1280
1281    def components(self):
1282        return getattr(self._resolve(), 'components', lambda: [])()
1283
1284    def _resolve(self):
1285        return getattr(self.proxy, self.attr)
1286
1287    # For AliasTable etc
1288    def fieldName(self):
1289        class _aliasFieldName(SQLExpression):
1290            def __init__(self, proxy):
1291                self.proxy = proxy
1292            def __sqlrepr__(self, db):
1293                return self.proxy._resolve().fieldName
1294        return _aliasFieldName(self)
1295    fieldName = property(fieldName)
1296
1297class _DelayClass(_Delay):
1298    def _resolve(self):
1299        return self.proxy.soClass.sqlmeta.table
1300
1301class _Delay_proxy(object):
1302    def __init__(self, **kw):
1303        self.__dict__.update(kw)
1304
1305######
1306
1307
1308########################################
1309## Global initializations
1310########################################
1311
1312table = TableSpace()
1313const = ConstantSpace()
1314func = const
1315
1316########################################
1317## Testing
1318########################################
1319
1320if __name__ == "__main__":
1321    tests = """
1322>>> AND(table.address.name == "Ian Bicking", table.address.zip > 30000)
1323>>> table.address.name
1324>>> AND(LIKE(table.address.name, "this"), IN(table.address.zip, [100, 200, 300]))
1325>>> Select([table.address.name, table.address.state], where=LIKE(table.address.name, "%ian%"))
1326>>> Select([table.user.name], where=AND(table.user.state == table.states.abbrev))
1327>>> Insert(table.address, [{"name": "BOB", "address": "3049 N. 18th St."}, {"name": "TIM", "address": "409 S. 10th St."}])
1328>>> Insert(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1329>>> Delete(table.address, where="BOB"==table.address.name)
1330>>> Update(table.address, {"lastModified": const.NOW()})
1331>>> Replace(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))
1332"""
1333    for expr in tests.split('\n'):
1334        if not expr.strip(): continue
1335        if expr.startswith('>>> '):
1336            expr = expr[4:]
1337