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