1# This file is part of python-sql.  The COPYRIGHT file at the top level of
2# this repository contains the full copyright notices and license terms.
3import string
4import numbers
5import warnings
6from threading import local, currentThread
7from collections import defaultdict
8from itertools import chain
9
10__version__ = '1.3.0'
11__all__ = ['Flavor', 'Table', 'Values', 'Literal', 'Column', 'Join',
12    'Asc', 'Desc', 'NullsFirst', 'NullsLast', 'format2numeric']
13
14
15def _escape_identifier(name):
16    return '"%s"' % name.replace('"', '""')
17
18
19def alias(i, letters=string.ascii_lowercase):
20    '''
21    Generate a unique alias based on integer
22
23    >>> [alias(n) for n in range(6)]
24    ['a', 'b', 'c', 'd', 'e', 'f']
25    >>> [alias(n) for n in range(26, 30)]
26    ['ba', 'bb', 'bc', 'bd']
27    >>> [alias(26**n) for n in range(5)]
28    ['b', 'ba', 'baa', 'baaa', 'baaaa']
29    '''
30    s = ''
31    length = len(letters)
32    while True:
33        r = i % length
34        s = letters[r] + s
35        i //= length
36        if i == 0:
37            break
38    return s
39
40
41class Flavor(object):
42    '''
43    Contains the flavor of SQL
44
45    Contains:
46        limitstyle - state the type of pagination
47        max_limit - limit to use if there is no limit but an offset
48        paramstyle - state the type of parameter marker formatting
49        ilike - support ilike extension
50        no_as - doesn't support AS keyword for column and table
51        null_ordering - support NULL ordering
52        function_mapping - dictionary with Function to replace
53    '''
54
55    def __init__(self, limitstyle='limit', max_limit=None, paramstyle='format',
56            ilike=False, no_as=False, no_boolean=False, null_ordering=True,
57            function_mapping=None, filter_=False):
58        assert limitstyle in ['fetch', 'limit', 'rownum']
59        self.limitstyle = limitstyle
60        self.max_limit = max_limit
61        self.paramstyle = paramstyle
62        self.ilike = ilike
63        self.no_as = no_as
64        self.no_boolean = no_boolean
65        self.null_ordering = null_ordering
66        self.function_mapping = function_mapping or {}
67        self.filter_ = filter_
68
69    @property
70    def param(self):
71        if self.paramstyle == 'format':
72            return '%s'
73        elif self.paramstyle == 'qmark':
74            return '?'
75
76    @staticmethod
77    def set(flavor):
78        '''Set this thread's flavor to flavor.'''
79        currentThread().__sql_flavor__ = flavor
80
81    @staticmethod
82    def get():
83        '''
84        Return this thread's flavor.
85
86        If this thread does not yet have a flavor, returns a new flavor and
87        sets this thread's flavor.
88        '''
89        try:
90            return currentThread().__sql_flavor__
91        except AttributeError:
92            flavor = Flavor()
93            currentThread().__sql_flavor__ = flavor
94            return flavor
95
96
97class AliasManager(object):
98    '''
99    Context Manager for unique alias generation
100    '''
101    __slots__ = ()
102
103    local = local()
104    local.alias = None
105    local.nested = 0
106    local.exclude = None
107
108    def __init__(self, exclude=None):
109        if exclude:
110            if getattr(self.local, 'exclude', None) is None:
111                self.local.exclude = []
112            self.local.exclude.extend(exclude)
113
114    @classmethod
115    def __enter__(cls):
116        if getattr(cls.local, 'alias', None) is None:
117            cls.local.alias = defaultdict(cls.alias_factory)
118            cls.local.nested = 0
119        if getattr(cls.local, 'exclude', None) is None:
120            cls.local.exclude = []
121        cls.local.nested += 1
122
123    @classmethod
124    def __exit__(cls, type, value, traceback):
125        cls.local.nested -= 1
126        if not cls.local.nested:
127            cls.local.alias = None
128            cls.local.exclude = None
129
130    @classmethod
131    def get(cls, from_):
132        if getattr(cls.local, 'alias', None) is None:
133            return ''
134        if from_ in cls.local.exclude:
135            return ''
136        return cls.local.alias[id(from_)]
137
138    @classmethod
139    def contains(cls, from_):
140        if getattr(cls.local, 'alias', None) is None:
141            return False
142        if from_ in cls.local.exclude:
143            return False
144        return id(from_) in cls.local.alias
145
146    @classmethod
147    def set(cls, from_, alias):
148        assert cls.local.alias.get(from_) is None
149        cls.local.alias[id(from_)] = alias
150
151    @classmethod
152    def alias_factory(cls):
153        i = len(cls.local.alias)
154        return alias(i)
155
156
157def format2numeric(query, params):
158    '''
159    Convert format paramstyle query to numeric paramstyle
160
161    >>> format2numeric('SELECT * FROM table WHERE col = %s', ('foo',))
162    ('SELECT * FROM table WHERE col = :0', ('foo',))
163    >>> format2numeric('SELECT * FROM table WHERE col1 = %s AND col2 = %s',
164    ...     ('foo', 'bar'))
165    ('SELECT * FROM table WHERE col1 = :0 AND col2 = :1', ('foo', 'bar'))
166    '''
167    return (query % tuple(':%i' % i for i, _ in enumerate(params)), params)
168
169
170class Query(object):
171    __slots__ = ()
172
173    @property
174    def params(self):
175        return ()
176
177    def __iter__(self):
178        yield str(self)
179        yield self.params
180
181    def __or__(self, other):
182        return Union(self, other)
183
184    def __and__(self, other):
185        return Intersect(self, other)
186
187    def __sub__(self, other):
188        return Except(self, other)
189
190
191class WithQuery(Query):
192    __slots__ = ('_with',)
193
194    def __init__(self, **kwargs):
195        self._with = None
196        self.with_ = kwargs.pop('with_', None)
197        super(Query, self).__init__(**kwargs)
198
199    @property
200    def with_(self):
201        return self._with
202
203    @with_.setter
204    def with_(self, value):
205        if value is not None:
206            if isinstance(value, With):
207                value = [value]
208            assert all(isinstance(w, With) for w in value)
209        self._with = value
210
211    def _with_str(self):
212        if not self.with_:
213            return ''
214        recursive = (' RECURSIVE' if any(w.recursive for w in self.with_)
215            else '')
216        with_ = ('WITH%s ' % recursive
217            + ', '.join(w.statement() for w in self.with_)
218            + ' ')
219        return with_
220
221    def _with_params(self):
222        if not self.with_:
223            return ()
224        params = []
225        for w in self.with_:
226            params.extend(w.statement_params())
227        return tuple(params)
228
229
230class FromItem(object):
231    __slots__ = ()
232
233    @property
234    def alias(self):
235        return AliasManager.get(self)
236
237    @property
238    def has_alias(self):
239        return AliasManager.contains(self)
240
241    def __getattr__(self, name):
242        if name.startswith('__'):
243            raise AttributeError
244        return Column(self, name)
245
246    def __add__(self, other):
247        assert isinstance(other, FromItem)
248        return From((self, other))
249
250    def select(self, *args, **kwargs):
251        return From((self,)).select(*args, **kwargs)
252
253    def join(self, right, type_='INNER', condition=None):
254        return Join(self, right, type_=type_, condition=condition)
255
256    def left_join(self, right, condition=None):
257        return self.join(right, type_='LEFT', condition=condition)
258
259    def left_outer_join(self, right, condition=None):
260        return self.join(right, type_='LEFT OUTER', condition=condition)
261
262    def right_join(self, right, condition=None):
263        return self.join(right, type_='RIGHT', condition=condition)
264
265    def right_outer_join(self, right, condition=None):
266        return self.join(right, type_='RIGHT OUTER', condition=condition)
267
268    def full_join(self, right, condition=None):
269        return self.join(right, type_='FULL', condition=condition)
270
271    def full_outer_join(self, right, condition=None):
272        return self.join(right, type_='FULL OUTER', condition=condition)
273
274    def cross_join(self, right, condition=None):
275        return self.join(right, type_='CROSS', condition=condition)
276
277    def lateral(self):
278        return Lateral(self)
279
280
281class Lateral(FromItem):
282    __slots__ = ('_from_item',)
283
284    def __init__(self, from_item):
285        self._from_item = from_item
286
287    def __str__(self):
288        template = '%s'
289        if isinstance(self._from_item, Query):
290            template = '(%s)'
291        return 'LATERAL ' + template % self._from_item
292
293    def __getattr__(self, name):
294        return getattr(self._from_item, name)
295
296
297class With(FromItem):
298    __slots__ = ('columns', 'query', 'recursive')
299
300    def __init__(self, *columns, **kwargs):
301        self.recursive = kwargs.pop('recursive', False)
302        self.columns = columns
303        self.query = kwargs.pop('query', None)
304        super(With, self).__init__(**kwargs)
305
306    def statement(self):
307        columns = (' (%s)' % ', '.join('"%s"' % c for c in self.columns)
308            if self.columns else '')
309        return '"%s"%s AS (%s)' % (self.alias, columns, self.query)
310
311    def statement_params(self):
312        return self.query.params
313
314    def __str__(self):
315        return '"%s"' % self.alias
316
317    @property
318    def params(self):
319        return tuple()
320
321
322class SelectQuery(WithQuery):
323    __slots__ = ('_order_by', '_limit', '_offset')
324
325    def __init__(self, *args, **kwargs):
326        self._order_by = None
327        self._limit = None
328        self._offset = None
329        self.order_by = kwargs.pop('order_by', None)
330        self.limit = kwargs.pop('limit', None)
331        self.offset = kwargs.pop('offset', None)
332        super(SelectQuery, self).__init__(*args, **kwargs)
333
334    @property
335    def order_by(self):
336        return self._order_by
337
338    @order_by.setter
339    def order_by(self, value):
340        if value is not None:
341            if isinstance(value, Expression):
342                value = [value]
343            assert all(isinstance(col, Expression) for col in value)
344        self._order_by = value
345
346    @property
347    def _order_by_str(self):
348        order_by = ''
349        if self.order_by:
350            order_by = ' ORDER BY ' + ', '.join(map(str, self.order_by))
351        return order_by
352
353    @property
354    def limit(self):
355        return self._limit
356
357    @limit.setter
358    def limit(self, value):
359        if value is not None:
360            assert isinstance(value, numbers.Integral)
361        self._limit = value
362
363    @property
364    def offset(self):
365        return self._offset
366
367    @offset.setter
368    def offset(self, value):
369        if value is not None:
370            assert isinstance(value, numbers.Integral)
371        self._offset = value
372
373    @property
374    def _limit_offset_str(self):
375        if Flavor.get().limitstyle == 'limit':
376            offset = ''
377            if self.offset:
378                offset = ' OFFSET %s' % self.offset
379            limit = ''
380            if self.limit is not None:
381                limit = ' LIMIT %s' % self.limit
382            elif self.offset:
383                max_limit = Flavor.get().max_limit
384                if max_limit:
385                    limit = ' LIMIT %s' % max_limit
386            return limit + offset
387        else:
388            offset = ''
389            if self.offset:
390                offset = ' OFFSET (%s) ROWS' % self.offset
391            fetch = ''
392            if self.limit is not None:
393                fetch = ' FETCH FIRST (%s) ROWS ONLY' % self.limit
394            return offset + fetch
395
396    def as_(self, output_name):
397        return As(self, output_name)
398
399
400class Select(FromItem, SelectQuery):
401    __slots__ = ('_columns', '_where', '_group_by', '_having', '_for_',
402        'from_', '_distinct', '_distinct_on', '_windows')
403
404    def __init__(self, columns, from_=None, where=None, group_by=None,
405            having=None, for_=None, distinct=False, distinct_on=None,
406            windows=None, **kwargs):
407        self._distinct = False
408        self._distinct_on = []
409        self._columns = None
410        self._where = None
411        self._group_by = None
412        self._having = None
413        self._for_ = None
414        self._windows = []
415        super(Select, self).__init__(**kwargs)
416        self.distinct = distinct
417        self.distinct_on = distinct_on
418        self.columns = columns
419        self.from_ = from_
420        self.where = where
421        self.group_by = group_by
422        self.having = having
423        self.for_ = for_
424        self.windows = windows
425
426    @property
427    def distinct(self):
428        return bool(self._distinct or self._distinct_on)
429
430    @distinct.setter
431    def distinct(self, value):
432        self._distinct = bool(value)
433
434    @property
435    def distinct_on(self):
436        return self._distinct_on
437
438    @distinct_on.setter
439    def distinct_on(self, value):
440        if value is not None:
441            if isinstance(value, Expression):
442                value = [value]
443            assert all(isinstance(col, Expression) for col in value)
444        self._distinct_on = value
445
446    @property
447    def columns(self):
448        return self._columns
449
450    @columns.setter
451    def columns(self, value):
452        assert all(isinstance(col, (Expression, SelectQuery)) for col in value)
453        self._columns = tuple(value)
454
455    @property
456    def where(self):
457        return self._where
458
459    @where.setter
460    def where(self, value):
461        from sql.operators import And, Or
462        if value is not None:
463            assert isinstance(value, (Expression, And, Or))
464        self._where = value
465
466    @property
467    def group_by(self):
468        return self._group_by
469
470    @group_by.setter
471    def group_by(self, value):
472        if value is not None:
473            if isinstance(value, Expression):
474                value = [value]
475            assert all(isinstance(col, Expression) for col in value)
476        self._group_by = value
477
478    @property
479    def having(self):
480        return self._having
481
482    @having.setter
483    def having(self, value):
484        from sql.operators import And, Or
485        if value is not None:
486            assert isinstance(value, (Expression, And, Or))
487        self._having = value
488
489    @property
490    def for_(self):
491        return self._for_
492
493    @for_.setter
494    def for_(self, value):
495        if value is not None:
496            if isinstance(value, For):
497                value = [value]
498            assert all(isinstance(f, For) for f in value)
499        self._for_ = value
500
501    @property
502    def windows(self):
503        from sql.functions import WindowFunction
504        from sql.aggregate import Aggregate
505        windows = set()
506        if self._windows is not None:
507            for window in self._windows:
508                windows.add(window)
509                yield window
510        for column in self.columns:
511            window_function = None
512            if isinstance(column, (WindowFunction, Aggregate)):
513                window_function = column
514            elif (isinstance(column, As)
515                    and isinstance(column.expression,
516                        (WindowFunction, Aggregate))):
517                window_function = column.expression
518            if (window_function and window_function.window
519                    and window_function.window not in windows):
520                windows.add(window_function.window)
521                yield window_function.window
522
523    @windows.setter
524    def windows(self, value):
525        if value is not None:
526            assert all(isinstance(w, Window) for w in value)
527        self._windows = value
528
529    @staticmethod
530    def _format_column(column):
531        if isinstance(column, As):
532            if isinstance(column.expression, Select):
533                expression = '(%s)' % column.expression
534            else:
535                expression = column.expression
536            if Flavor.get().no_as:
537                return '%s %s' % (expression, column)
538            else:
539                return '%s AS %s' % (expression, column)
540        else:
541            if isinstance(column, Select):
542                return '(%s)' % column
543            else:
544                return str(column)
545
546    def _rownum(self, func):
547        aliases = [c.output_name if isinstance(c, As) else None
548            for c in self.columns]
549
550        def columns(table):
551            if aliases and all(aliases):
552                return [Column(table, alias) for alias in aliases]
553            else:
554                return [Column(table, '*')]
555
556        limitselect = self.select(*columns(self))
557        if self.limit is not None:
558            max_row = self.limit
559            if self.offset is not None:
560                max_row += self.offset
561            limitselect.where = _rownum <= max_row
562        if self.offset is not None:
563            rnum = _rownum.as_('rnum')
564            limitselect.columns += (rnum,)
565            offsetselect = limitselect.select(*columns(limitselect),
566                where=rnum > self.offset)
567            query = offsetselect
568        else:
569            query = limitselect
570
571        self.limit, limit = None, self.limit
572        self.offset, offset = None, self.offset
573        query.for_, self.for_ = self.for_, None
574
575        try:
576            value = func(query)
577        finally:
578            self.limit = limit
579            self.offset = offset
580            self.for_ = query.for_
581        return value
582
583    def __str__(self):
584        if (Flavor.get().limitstyle == 'rownum'
585                and (self.limit is not None or self.offset is not None)):
586            return self._rownum(str)
587
588        with AliasManager():
589            if self.from_ is not None:
590                from_ = ' FROM %s' % self.from_
591            else:
592                from_ = ''
593
594            # format window before expressions to set alias
595            window = ', '.join(
596                '"%s" AS (%s)' % (w.alias, w) for w in self.windows)
597            if window:
598                window = ' WINDOW ' + window
599
600            if self.distinct:
601                distinct = 'DISTINCT '
602                if self.distinct_on:
603                    distinct += ('ON (%s) '
604                        % ', '.join(map(str, self.distinct_on)))
605            else:
606                distinct = ''
607            if self.columns:
608                columns = ', '.join(map(self._format_column, self.columns))
609            else:
610                columns = '*'
611            where = ''
612            if self.where:
613                where = ' WHERE ' + str(self.where)
614            group_by = ''
615            if self.group_by:
616                group_by = ' GROUP BY ' + ', '.join(map(str, self.group_by))
617            having = ''
618            if self.having:
619                having = ' HAVING ' + str(self.having)
620            for_ = ''
621            if self.for_ is not None:
622                for_ = ' ' + ' '.join(map(str, self.for_))
623            return (self._with_str()
624                + 'SELECT %s%s%s' % (distinct, columns, from_)
625                + where + group_by + having + window + self._order_by_str
626                + self._limit_offset_str + for_)
627
628    @property
629    def params(self):
630        if (Flavor.get().limitstyle == 'rownum'
631                and (self.limit is not None or self.offset is not None)):
632            return self._rownum(lambda q: q.params)
633        p = []
634        with AliasManager():
635            # Set alias to window function to skip their params
636            for window in self.windows:
637                window.alias
638
639            p.extend(self._with_params())
640            for column in chain(self.distinct_on or (), self.columns):
641                if isinstance(column, As):
642                    p.extend(column.expression.params)
643                p.extend(column.params)
644            if self.from_ is not None:
645                p.extend(self.from_.params)
646            if self.where:
647                p.extend(self.where.params)
648            if self.group_by:
649                for expression in self.group_by:
650                    p.extend(expression.params)
651            if self.order_by:
652                for expression in self.order_by:
653                    p.extend(expression.params)
654            if self.having:
655                p.extend(self.having.params)
656            for window in self.windows:
657                p.extend(window.params)
658        return tuple(p)
659
660
661class Insert(WithQuery):
662    __slots__ = ('_table', '_columns', '_values', '_returning')
663
664    def __init__(self, table, columns=None, values=None, returning=None,
665            **kwargs):
666        self._table = None
667        self._columns = None
668        self._values = None
669        self._returning = None
670        self.table = table
671        self.columns = columns
672        self.values = values
673        self.returning = returning
674        super(Insert, self).__init__(**kwargs)
675
676    @property
677    def table(self):
678        return self._table
679
680    @table.setter
681    def table(self, value):
682        assert isinstance(value, Table)
683        self._table = value
684
685    @property
686    def columns(self):
687        return self._columns
688
689    @columns.setter
690    def columns(self, value):
691        if value is not None:
692            assert all(isinstance(col, Column) for col in value)
693            assert all(col.table == self.table for col in value)
694        self._columns = value
695
696    @property
697    def values(self):
698        return self._values
699
700    @values.setter
701    def values(self, value):
702        if value is not None:
703            assert isinstance(value, (list, Select))
704        if isinstance(value, list):
705            value = Values(value)
706        self._values = value
707
708    @property
709    def returning(self):
710        return self._returning
711
712    @returning.setter
713    def returning(self, value):
714        if value is not None:
715            assert isinstance(value, list)
716        self._returning = value
717
718    @staticmethod
719    def _format(value, param=None):
720        if param is None:
721            param = Flavor.get().param
722        if isinstance(value, Expression):
723            return str(value)
724        elif isinstance(value, Select):
725            return '(%s)' % value
726        else:
727            return param
728
729    def __str__(self):
730        columns = ''
731        if self.columns:
732            assert all(col.table == self.table for col in self.columns)
733            columns = ' (' + ', '.join(map(str, self.columns)) + ')'
734        with AliasManager():
735            if isinstance(self.values, Query):
736                values = ' %s' % str(self.values)
737                # TODO manage DEFAULT
738            elif self.values is None:
739                values = ' DEFAULT VALUES'
740            returning = ''
741            if self.returning:
742                returning = ' RETURNING ' + ', '.join(
743                    map(self._format, self.returning))
744            return (self._with_str()
745                + 'INSERT INTO %s AS "%s"' % (self.table, self.table.alias)
746                + columns + values + returning)
747
748    @property
749    def params(self):
750        p = []
751        p.extend(self._with_params())
752        if isinstance(self.values, Query):
753            p.extend(self.values.params)
754        if self.returning:
755            for exp in self.returning:
756                p.extend(exp.params)
757        return tuple(p)
758
759
760class Update(Insert):
761    __slots__ = ('_where', '_values', 'from_')
762
763    def __init__(self, table, columns, values, from_=None, where=None,
764            returning=None, **kwargs):
765        super(Update, self).__init__(table, columns=columns, values=values,
766            returning=returning, **kwargs)
767        self._where = None
768        self.from_ = From(from_) if from_ else None
769        self.where = where
770
771    @property
772    def values(self):
773        return self._values
774
775    @values.setter
776    def values(self, value):
777        if isinstance(value, Select):
778            value = [value]
779        assert isinstance(value, list)
780        self._values = value
781
782    @property
783    def where(self):
784        return self._where
785
786    @where.setter
787    def where(self, value):
788        from sql.operators import And, Or
789        if value is not None:
790            assert isinstance(value, (Expression, And, Or))
791        self._where = value
792
793    def __str__(self):
794        assert all(col.table == self.table for col in self.columns)
795        # Get columns without alias
796        columns = list(map(str, self.columns))
797
798        with AliasManager():
799            from_ = ''
800            if self.from_:
801                from_ = ' FROM %s' % str(self.from_)
802            values = ', '.join('%s = %s' % (c, self._format(v))
803                for c, v in zip(columns, self.values))
804            where = ''
805            if self.where:
806                where = ' WHERE ' + str(self.where)
807            returning = ''
808            if self.returning:
809                returning = ' RETURNING ' + ', '.join(
810                    map(self._format, self.returning))
811            return (self._with_str()
812                + 'UPDATE %s AS "%s" SET ' % (self.table, self.table.alias)
813                + values + from_ + where + returning)
814
815    @property
816    def params(self):
817        p = []
818        p.extend(self._with_params())
819        for value in self.values:
820            if isinstance(value, (Expression, Select)):
821                p.extend(value.params)
822            else:
823                p.append(value)
824        if self.from_:
825            p.extend(self.from_.params)
826        if self.where:
827            p.extend(self.where.params)
828        if self.returning:
829            for exp in self.returning:
830                p.extend(exp.params)
831        return tuple(p)
832
833
834class Delete(WithQuery):
835    __slots__ = ('_table', '_where', '_returning', 'only')
836
837    def __init__(self, table, only=False, using=None, where=None,
838            returning=None, **kwargs):
839        self._table = None
840        self._where = None
841        self._returning = None
842        self.table = table
843        self.only = only
844        # TODO using (not standard)
845        self.where = where
846        self.returning = returning
847        super(Delete, self).__init__(**kwargs)
848
849    @property
850    def table(self):
851        return self._table
852
853    @table.setter
854    def table(self, value):
855        assert isinstance(value, Table)
856        self._table = value
857
858    @property
859    def where(self):
860        return self._where
861
862    @where.setter
863    def where(self, value):
864        from sql.operators import And, Or
865        if value is not None:
866            assert isinstance(value, (Expression, And, Or))
867        self._where = value
868
869    @property
870    def returning(self):
871        return self._returning
872
873    @returning.setter
874    def returning(self, value):
875        if value is not None:
876            assert isinstance(value, list)
877        self._returning = value
878
879    def __str__(self):
880        with AliasManager(exclude=[self.table]):
881            only = ' ONLY' if self.only else ''
882            where = ''
883            if self.where:
884                where = ' WHERE ' + str(self.where)
885            returning = ''
886            if self.returning:
887                returning = ' RETURNING ' + ', '.join(map(str, self.returning))
888            return (self._with_str()
889                + 'DELETE FROM%s %s' % (only, self.table)
890                + where + returning)
891
892    @property
893    def params(self):
894        p = []
895        p.extend(self._with_params())
896        if self.where:
897            p.extend(self.where.params)
898        if self.returning:
899            for exp in self.returning:
900                p.extend(exp.params)
901        return tuple(p)
902
903
904class CombiningQuery(FromItem, SelectQuery):
905    __slots__ = ('queries', 'all_')
906    _operator = ''
907
908    def __init__(self, *queries, **kwargs):
909        assert all(isinstance(q, Query) for q in queries)
910        self.queries = queries
911        self.all_ = kwargs.pop('all_', False)
912        super(CombiningQuery, self).__init__(**kwargs)
913
914    def __str__(self):
915        with AliasManager():
916            operator = ' %s %s' % (self._operator, 'ALL ' if self.all_ else '')
917            return (operator.join(map(str, self.queries)) + self._order_by_str
918                + self._limit_offset_str)
919
920    @property
921    def params(self):
922        p = []
923        for q in self.queries:
924            p.extend(q.params)
925        if self.order_by:
926            for expression in self.order_by:
927                p.extend(expression.params)
928        return tuple(p)
929
930
931class Union(CombiningQuery):
932    __slots__ = ()
933    _operator = 'UNION'
934
935
936class Intersect(CombiningQuery):
937    __slots__ = ()
938    _operator = 'INTERSECT'
939
940
941class Interesect(Intersect):
942    def __init__(self, *args, **kwargs):
943        warnings.warn('Interesect query is deprecated, use Intersect',
944            DeprecationWarning, stacklevel=2)
945        super(Interesect, self).__init__(*args, **kwargs)
946
947
948class Except(CombiningQuery):
949    __slots__ = ()
950    _operator = 'EXCEPT'
951
952
953class Table(FromItem):
954    __slots__ = ('_name', '_schema', '_database')
955
956    def __init__(self, name, schema=None, database=None):
957        super(Table, self).__init__()
958        self._name = name
959        self._schema = schema
960        self._database = database
961
962    def __str__(self):
963        return '.'.join(map(_escape_identifier, filter(None,
964                    (self._database, self._schema, self._name))))
965
966    @property
967    def params(self):
968        return ()
969
970    def insert(self, columns=None, values=None, returning=None, with_=None):
971        return Insert(self, columns=columns, values=values,
972            returning=returning, with_=with_)
973
974    def update(self, columns, values, from_=None, where=None, returning=None,
975            with_=None):
976        return Update(self, columns=columns, values=values, from_=from_,
977            where=where, returning=returning, with_=with_)
978
979    def delete(self, only=False, using=None, where=None, returning=None,
980            with_=None):
981        return Delete(self, only=only, using=using, where=where,
982            returning=returning, with_=with_)
983
984
985class Join(FromItem):
986    __slots__ = ('_left', '_right', '_condition', '_type_')
987
988    def __init__(self, left, right, type_='INNER', condition=None):
989        super(Join, self).__init__()
990        self._left, self._right = None, None
991        self._condition = None
992        self._type_ = None
993        self.left = left
994        self.right = right
995        self.condition = condition
996        self.type_ = type_
997
998    @property
999    def left(self):
1000        return self._left
1001
1002    @left.setter
1003    def left(self, value):
1004        assert isinstance(value, FromItem)
1005        self._left = value
1006
1007    @property
1008    def right(self):
1009        return self._right
1010
1011    @right.setter
1012    def right(self, value):
1013        assert isinstance(value, FromItem)
1014        self._right = value
1015
1016    @property
1017    def condition(self):
1018        return self._condition
1019
1020    @condition.setter
1021    def condition(self, value):
1022        from sql.operators import And, Or
1023        if value is not None:
1024            assert isinstance(value, (Expression, And, Or))
1025        self._condition = value
1026
1027    @property
1028    def type_(self):
1029        return self._type_
1030
1031    @type_.setter
1032    def type_(self, value):
1033        value = value.upper()
1034        assert value in ('INNER', 'LEFT', 'LEFT OUTER',
1035            'RIGHT', 'RIGHT OUTER', 'FULL', 'FULL OUTER', 'CROSS')
1036        self._type_ = value
1037
1038    def __str__(self):
1039        join = '%s %s JOIN %s' % (From([self.left]), self.type_,
1040            From([self.right]))
1041        if self.condition:
1042            condition = ' ON %s' % self.condition
1043        else:
1044            condition = ''
1045        return join + condition
1046
1047    @property
1048    def params(self):
1049        p = []
1050        for item in (self.left, self.right):
1051            if hasattr(item, 'params'):
1052                p.extend(item.params)
1053        if hasattr(self.condition, 'params'):
1054            p.extend(self.condition.params)
1055        return tuple(p)
1056
1057    @property
1058    def alias(self):
1059        raise AttributeError
1060
1061    @property
1062    def has_alias(self):
1063        raise AttributeError
1064
1065    def __getattr__(self, name):
1066        raise AttributeError
1067
1068    def select(self, *args, **kwargs):
1069        return super(Join, self).select(*args, **kwargs)
1070
1071
1072class From(list):
1073    __slots__ = ()
1074
1075    def select(self, *args, **kwargs):
1076        return Select(args, from_=self, **kwargs)
1077
1078    def __str__(self):
1079        def format(from_):
1080            template = '%s'
1081            if isinstance(from_, Query):
1082                template = '(%s)'
1083            alias = getattr(from_, 'alias', None)
1084            # TODO column_alias
1085            columns_definitions = getattr(from_, 'columns_definitions',
1086                None)
1087            if Flavor.get().no_as:
1088                alias_template = ' "%s"'
1089            else:
1090                alias_template = ' AS "%s"'
1091            # XXX find a better test for __getattr__ which returns Column
1092            if (alias and columns_definitions
1093                    and not isinstance(columns_definitions, Column)):
1094                return (template + alias_template + ' (%s)') % (from_, alias,
1095                    columns_definitions)
1096            elif alias:
1097                return (template + alias_template) % (from_, alias)
1098            else:
1099                return template % from_
1100        return ', '.join(map(format, self))
1101
1102    @property
1103    def params(self):
1104        p = []
1105        for from_ in self:
1106            p.extend(from_.params)
1107        return tuple(p)
1108
1109    def __add__(self, other):
1110        assert isinstance(other, FromItem)
1111        assert not isinstance(other, CombiningQuery)
1112        return From(super(From, self).__add__([other]))
1113
1114
1115class Values(list, Query, FromItem):
1116    __slots__ = ()
1117
1118    # TODO order, fetch
1119
1120    def __str__(self):
1121        param = Flavor.get().param
1122
1123        def format_(value):
1124            if isinstance(value, Expression):
1125                return str(value)
1126            else:
1127                return param
1128        return 'VALUES ' + ', '.join(
1129            '(%s)' % ', '.join(map(format_, v))
1130            for v in self)
1131
1132    @property
1133    def params(self):
1134        p = []
1135        for values in self:
1136            for value in values:
1137                if isinstance(value, Expression):
1138                    p.extend(value.params)
1139                else:
1140                    p.append(value)
1141        return tuple(p)
1142
1143
1144class Expression(object):
1145    __slots__ = ()
1146
1147    def __str__(self):
1148        raise NotImplementedError
1149
1150    @property
1151    def params(self):
1152        raise NotImplementedError
1153
1154    def __and__(self, other):
1155        from sql.operators import And
1156        return And((self, other))
1157
1158    def __or__(self, other):
1159        from sql.operators import Or
1160        return Or((self, other))
1161
1162    def __invert__(self):
1163        from sql.operators import Not
1164        return Not(self)
1165
1166    def __add__(self, other):
1167        from sql.operators import Add
1168        return Add(self, other)
1169
1170    def __sub__(self, other):
1171        from sql.operators import Sub
1172        return Sub(self, other)
1173
1174    def __mul__(self, other):
1175        from sql.operators import Mul
1176        return Mul(self, other)
1177
1178    def __div__(self, other):
1179        from sql.operators import Div
1180        return Div(self, other)
1181
1182    __truediv__ = __div__
1183
1184    def __floordiv__(self, other):
1185        from sql.functions import Div
1186        return Div(self, other)
1187
1188    def __mod__(self, other):
1189        from sql.operators import Mod
1190        return Mod(self, other)
1191
1192    def __pow__(self, other):
1193        from sql.operators import Pow
1194        return Pow(self, other)
1195
1196    def __neg__(self):
1197        from sql.operators import Neg
1198        return Neg(self)
1199
1200    def __pos__(self):
1201        from sql.operators import Pos
1202        return Pos(self)
1203
1204    def __abs__(self):
1205        from sql.operators import Abs
1206        return Abs(self)
1207
1208    def __lshift__(self, other):
1209        from sql.operators import LShift
1210        return LShift(self, other)
1211
1212    def __rshift__(self, other):
1213        from sql.operators import RShift
1214        return RShift(self, other)
1215
1216    def __lt__(self, other):
1217        from sql.operators import Less
1218        return Less(self, other)
1219
1220    def __le__(self, other):
1221        from sql.operators import LessEqual
1222        return LessEqual(self, other)
1223
1224    def __eq__(self, other):
1225        from sql.operators import Equal
1226        return Equal(self, other)
1227
1228    # When overriding __eq__, __hash__ is implicitly set to None
1229    __hash__ = object.__hash__
1230
1231    def __ne__(self, other):
1232        from sql.operators import NotEqual
1233        return NotEqual(self, other)
1234
1235    def __gt__(self, other):
1236        from sql.operators import Greater
1237        return Greater(self, other)
1238
1239    def __ge__(self, other):
1240        from sql.operators import GreaterEqual
1241        return GreaterEqual(self, other)
1242
1243    def in_(self, values):
1244        from sql.operators import In
1245        return In(self, values)
1246
1247    def like(self, test):
1248        from sql.operators import Like
1249        return Like(self, test)
1250
1251    def ilike(self, test):
1252        from sql.operators import ILike
1253        return ILike(self, test)
1254
1255    def as_(self, output_name):
1256        return As(self, output_name)
1257
1258    def cast(self, typename):
1259        return Cast(self, typename)
1260
1261    def collate(self, collation):
1262        return Collate(self, collation)
1263
1264    @property
1265    def asc(self):
1266        return Asc(self)
1267
1268    @property
1269    def desc(self):
1270        return Desc(self)
1271
1272    @property
1273    def nulls_first(self):
1274        return NullsFirst(self)
1275
1276    @property
1277    def nulls_last(self):
1278        return NullsLast(self)
1279
1280
1281class Literal(Expression):
1282    __slots__ = ('_value')
1283
1284    def __init__(self, value):
1285        super(Literal, self).__init__()
1286        self._value = value
1287
1288    @property
1289    def value(self):
1290        return self._value
1291
1292    def __str__(self):
1293        flavor = Flavor.get()
1294        if flavor.no_boolean:
1295            if self._value is True:
1296                return '(1 = 1)'
1297            elif self._value is False:
1298                return '(1 != 1)'
1299        return flavor.param
1300
1301    @property
1302    def params(self):
1303        if Flavor.get().no_boolean:
1304            if self._value is True or self._value is False:
1305                return ()
1306        return (self._value,)
1307
1308
1309Null = None
1310
1311
1312class _Rownum(Expression):
1313
1314    def __str__(self):
1315        return 'ROWNUM'
1316
1317    @property
1318    def params(self):
1319        return ()
1320
1321
1322_rownum = _Rownum()
1323
1324
1325class Column(Expression):
1326    __slots__ = ('_from', '_name')
1327
1328    def __init__(self, from_, name):
1329        super(Column, self).__init__()
1330        self._from = from_
1331        self._name = name
1332
1333    @property
1334    def table(self):
1335        return self._from
1336
1337    @property
1338    def name(self):
1339        return self._name
1340
1341    def __str__(self):
1342        name = (self._name if self._name == '*'
1343            else _escape_identifier(self._name))
1344        alias = self._from.alias
1345        if alias:
1346            return '%s.%s' % (_escape_identifier(alias), name)
1347        else:
1348            return name
1349
1350    @property
1351    def params(self):
1352        return ()
1353
1354
1355class As(Expression):
1356    __slots__ = ('expression', 'output_name')
1357
1358    def __init__(self, expression, output_name):
1359        super(As, self).__init__()
1360        self.expression = expression
1361        self.output_name = output_name
1362
1363    def __str__(self):
1364        return '%s' % _escape_identifier(self.output_name)
1365
1366    @property
1367    def params(self):
1368        return ()
1369
1370
1371class Cast(Expression):
1372    __slots__ = ('expression', 'typename')
1373
1374    def __init__(self, expression, typename):
1375        super(Cast, self).__init__()
1376        self.expression = expression
1377        self.typename = typename
1378
1379    def __str__(self):
1380        if isinstance(self.expression, Expression):
1381            value = self.expression
1382        else:
1383            value = Flavor.get().param
1384        return 'CAST(%s AS %s)' % (value, self.typename)
1385
1386    @property
1387    def params(self):
1388        if isinstance(self.expression, Expression):
1389            return self.expression.params
1390        else:
1391            return (self.expression,)
1392
1393
1394class Collate(Expression):
1395    __slots__ = ('expression', 'collation')
1396
1397    def __init__(self, expression, collation):
1398        super(Collate, self).__init__()
1399        self.expression = expression
1400        self.collation = collation
1401
1402    def __str__(self):
1403        if isinstance(self.expression, Expression):
1404            value = self.expression
1405        else:
1406            value = Flavor.get().param
1407        if '"' in self.collation:
1408            raise ValueError("Wrong collation %s" % self.collation)
1409        return '%s COLLATE "%s"' % (value, self.collation)
1410
1411    @property
1412    def params(self):
1413        if isinstance(self.expression, Expression):
1414            return self.expression.params
1415        else:
1416            return (self.expression,)
1417
1418
1419class Window(object):
1420    __slots__ = (
1421        '_partition', '_order_by', '_frame', '_start', '_end', '_exclude')
1422
1423    def __init__(self, partition, order_by=None,
1424            frame=None, start=None, end=0, exclude=None):
1425        super(Window, self).__init__()
1426        self._partition = None
1427        self._order_by = None
1428        self._frame = None
1429        self._start = None
1430        self._end = None
1431        self.partition = partition
1432        self.order_by = order_by
1433        self.frame = frame
1434        self.start = start
1435        self.end = end
1436        self.exclude = exclude
1437
1438    @property
1439    def partition(self):
1440        return self._partition
1441
1442    @partition.setter
1443    def partition(self, value):
1444        assert all(isinstance(e, Expression) for e in value)
1445        self._partition = value
1446
1447    @property
1448    def order_by(self):
1449        return self._order_by
1450
1451    @order_by.setter
1452    def order_by(self, value):
1453        if value is not None:
1454            if isinstance(value, Expression):
1455                value = [value]
1456            assert all(isinstance(col, Expression) for col in value)
1457        self._order_by = value
1458
1459    @property
1460    def frame(self):
1461        return self._frame
1462
1463    @frame.setter
1464    def frame(self, value):
1465        if value:
1466            assert value in ['RANGE', 'ROWS', 'GROUPS']
1467        self._frame = value
1468
1469    @property
1470    def start(self):
1471        return self._start
1472
1473    @start.setter
1474    def start(self, value):
1475        if value:
1476            assert isinstance(value, numbers.Integral)
1477        self._start = value
1478
1479    @property
1480    def end(self):
1481        return self._end
1482
1483    @end.setter
1484    def end(self, value):
1485        if value:
1486            assert isinstance(value, numbers.Integral)
1487        self._end = value
1488
1489    @property
1490    def exclude(self):
1491        return self._exclude
1492
1493    @exclude.setter
1494    def exclude(self, value):
1495        if value:
1496            assert value in ['CURRENT ROW', 'GROUP', 'TIES']
1497        self._exclude = value
1498
1499    @property
1500    def alias(self):
1501        return AliasManager.get(self)
1502
1503    @property
1504    def has_alias(self):
1505        return AliasManager.contains(self)
1506
1507    def __str__(self):
1508        partition = ''
1509        if self.partition:
1510            partition = 'PARTITION BY ' + ', '.join(map(str, self.partition))
1511        order_by = ''
1512        if self.order_by:
1513            order_by = ' ORDER BY ' + ', '.join(map(str, self.order_by))
1514
1515        def format(frame, direction):
1516            if frame is None:
1517                return 'UNBOUNDED %s' % direction
1518            elif not frame:
1519                return 'CURRENT ROW'
1520            elif frame < 0:
1521                return '%s PRECEDING' % -frame
1522            elif frame > 0:
1523                return '%s FOLLOWING' % frame
1524
1525        frame = ''
1526        if self.frame:
1527            start = format(self.start, 'PRECEDING')
1528            end = format(self.end, 'FOLLOWING')
1529            frame = ' %s BETWEEN %s AND %s' % (self.frame, start, end)
1530        exclude = ''
1531        if self.exclude:
1532            exclude = ' EXCLUDE %s' % self.exclude
1533        return partition + order_by + frame + exclude
1534
1535    @property
1536    def params(self):
1537        p = []
1538        if self.partition:
1539            for expression in self.partition:
1540                p.extend(expression.params)
1541        if self.order_by:
1542            for expression in self.order_by:
1543                p.extend(expression.params)
1544        return tuple(p)
1545
1546
1547class Order(Expression):
1548    __slots__ = ('_expression')
1549    _sql = ''
1550
1551    def __init__(self, expression):
1552        super(Order, self).__init__()
1553        self._expression = None
1554        self.expression = expression
1555        # TODO USING
1556
1557    @property
1558    def expression(self):
1559        return self._expression
1560
1561    @expression.setter
1562    def expression(self, value):
1563        assert isinstance(value, (Expression, SelectQuery))
1564        self._expression = value
1565
1566    def __str__(self):
1567        if isinstance(self.expression, SelectQuery):
1568            return '(%s) %s' % (self.expression, self._sql)
1569        return '%s %s' % (self.expression, self._sql)
1570
1571    @property
1572    def params(self):
1573        return self.expression.params
1574
1575
1576class Asc(Order):
1577    __slots__ = ()
1578    _sql = 'ASC'
1579
1580
1581class Desc(Order):
1582    __slots__ = ()
1583    _sql = 'DESC'
1584
1585
1586class NullOrder(Expression):
1587    __slots__ = ('expression')
1588    _sql = ''
1589
1590    def __init__(self, expression):
1591        super(NullOrder, self).__init__()
1592        self.expression = expression
1593
1594    def __str__(self):
1595        if not Flavor.get().null_ordering:
1596            return '%s, %s' % (self._case, self.expression)
1597        return '%s NULLS %s' % (self.expression, self._sql)
1598
1599    @property
1600    def params(self):
1601        p = []
1602        if not Flavor.get().null_ordering:
1603            p.extend(self.expression.params)
1604            p.extend(self._case_values())
1605        p.extend(self.expression.params)
1606        return tuple(p)
1607
1608    @property
1609    def _case(self):
1610        from .conditionals import Case
1611        values = self._case_values()
1612        if isinstance(self.expression, Order):
1613            expression = self.expression.expression
1614        else:
1615            expression = self.expression
1616        return Asc(Case((expression == Null, values[0]), else_=values[1]))
1617
1618    def _case_values(self):
1619        raise NotImplementedError
1620
1621
1622class NullsFirst(NullOrder):
1623    __slots__ = ()
1624    _sql = 'FIRST'
1625
1626    def _case_values(self):
1627        return (0, 1)
1628
1629
1630class NullsLast(NullOrder):
1631    __slots__ = ()
1632    _sql = 'LAST'
1633
1634    def _case_values(self):
1635        return (1, 0)
1636
1637
1638class For(object):
1639    __slots__ = ('_tables', '_type_', 'nowait')
1640
1641    def __init__(self, type_, *tables, **kwargs):
1642        self._tables = None
1643        self._type_ = None
1644        self.tables = list(tables)
1645        self.type_ = type_
1646        self.nowait = kwargs.get('nowait')
1647
1648    @property
1649    def tables(self):
1650        return self._tables
1651
1652    @tables.setter
1653    def tables(self, value):
1654        if not isinstance(value, list):
1655            value = [value]
1656        all(isinstance(table, Table) for table in value)
1657        self._tables = value
1658
1659    @property
1660    def type_(self):
1661        return self._type_
1662
1663    @type_.setter
1664    def type_(self, value):
1665        value = value.upper()
1666        assert value in ('UPDATE', 'SHARE')
1667        self._type_ = value
1668
1669    def __str__(self):
1670        tables = ''
1671        if self.tables:
1672            tables = ' OF ' + ', '.join(map(str, self.tables))
1673        nowait = ''
1674        if self.nowait:
1675            nowait = ' NOWAIT'
1676        return ('FOR %s' % self.type_) + tables + nowait
1677