diff options
Diffstat (limited to 'lib/sqlalchemy/dialects')
| -rw-r--r-- | lib/sqlalchemy/dialects/firebird/base.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 106 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 31 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/array.py | 235 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 25 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/ext.py (renamed from lib/sqlalchemy/dialects/postgresql/constraints.py) | 76 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/json.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 53 |
10 files changed, 233 insertions, 311 deletions
diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py index c34829cd3..acd419e85 100644 --- a/lib/sqlalchemy/dialects/firebird/base.py +++ b/lib/sqlalchemy/dialects/firebird/base.py @@ -648,7 +648,7 @@ class FBDialect(default.DefaultDialect): 'type': coltype, 'nullable': not bool(row['null_flag']), 'default': defvalue, - 'autoincrement': defvalue is None + 'autoincrement': 'auto', } if orig_colname.lower() == orig_colname: diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 6670a28bd..37e798014 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -166,56 +166,6 @@ how SQLAlchemy handles this: This is an auxilliary use case suitable for testing and bulk insert scenarios. -.. _legacy_schema_rendering: - -Rendering of SQL statements that include schema qualifiers ---------------------------------------------------------- - -When using :class:`.Table` metadata that includes a "schema" qualifier, -such as:: - - account_table = Table( - 'account', metadata, - Column('id', Integer, primary_key=True), - Column('info', String(100)), - schema="customer_schema" - ) - -The SQL Server dialect has a long-standing behavior that it will attempt -to turn a schema-qualified table name into an alias, such as:: - - >>> eng = create_engine("mssql+pymssql://mydsn") - >>> print(account_table.select().compile(eng)) - SELECT account_1.id, account_1.info - FROM customer_schema.account AS account_1 - -This behavior is legacy, does not function correctly for many forms -of SQL statements, and will be disabled by default in the 1.1 series -of SQLAlchemy. As of 1.0.5, the above statement will produce the following -warning:: - - SAWarning: legacy_schema_aliasing flag is defaulted to True; - some schema-qualified queries may not function correctly. - Consider setting this flag to False for modern SQL Server versions; - this flag will default to False in version 1.1 - -This warning encourages the :class:`.Engine` to be created as follows:: - - >>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=False) - -Where the above SELECT statement will produce:: - - >>> print(account_table.select().compile(eng)) - SELECT customer_schema.account.id, customer_schema.account.info - FROM customer_schema.account - -The warning will not emit if the ``legacy_schema_aliasing`` flag is set -to either True or False. - -.. versionadded:: 1.0.5 - Added the ``legacy_schema_aliasing`` flag to disable - the SQL Server dialect's legacy behavior with schema-qualified table - names. This flag will default to False in version 1.1. - Collation Support ----------------- @@ -236,7 +186,7 @@ CREATE TABLE statement for this column will yield:: LIMIT/OFFSET Support -------------------- -MSSQL has no support for the LIMIT or OFFSET keysowrds. LIMIT is +MSSQL has no support for the LIMIT or OFFSET keywords. LIMIT is supported directly through the ``TOP`` Transact SQL keyword:: select.limit @@ -322,6 +272,41 @@ behavior of this flag is as follows: .. versionadded:: 1.0.0 +.. _legacy_schema_rendering: + +Legacy Schema Mode +------------------ + +Very old versions of the MSSQL dialect introduced the behavior such that a +schema-qualified table would be auto-aliased when used in a +SELECT statement; given a table:: + + account_table = Table( + 'account', metadata, + Column('id', Integer, primary_key=True), + Column('info', String(100)), + schema="customer_schema" + ) + +this legacy mode of rendering would assume that "customer_schema.account" +would not be accepted by all parts of the SQL statement, as illustrated +below:: + + >>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True) + >>> print(account_table.select().compile(eng)) + SELECT account_1.id, account_1.info + FROM customer_schema.account AS account_1 + +This mode of behavior is now off by default, as it appears to have served +no purpose; however in the case that legacy applications rely upon it, +it is available using the ``legacy_schema_aliasing`` argument to +:func:`.create_engine` as illustrated above. + +.. versionchanged:: 1.1 the ``legacy_schema_aliasing`` flag introduced + in version 1.0.5 to allow disabling of legacy mode for schemas now + defaults to False. + + .. _mssql_indexes: Clustered Index Support @@ -1156,15 +1141,6 @@ class MSSQLCompiler(compiler.SQLCompiler): def _schema_aliased_table(self, table): if getattr(table, 'schema', None) is not None: - if self.dialect._warn_schema_aliasing and \ - table.schema.lower() != 'information_schema': - util.warn( - "legacy_schema_aliasing flag is defaulted to True; " - "some schema-qualified queries may not function " - "correctly. Consider setting this flag to False for " - "modern SQL Server versions; this flag will default to " - "False in version 1.1") - if table not in self.tablealiases: self.tablealiases[table] = table.alias() return self.tablealiases[table] @@ -1530,7 +1506,7 @@ class MSDialect(default.DefaultDialect): max_identifier_length=None, schema_name="dbo", deprecate_large_types=None, - legacy_schema_aliasing=None, **opts): + legacy_schema_aliasing=False, **opts): self.query_timeout = int(query_timeout or 0) self.schema_name = schema_name @@ -1538,13 +1514,7 @@ class MSDialect(default.DefaultDialect): self.max_identifier_length = int(max_identifier_length or 0) or \ self.max_identifier_length self.deprecate_large_types = deprecate_large_types - - if legacy_schema_aliasing is None: - self.legacy_schema_aliasing = True - self._warn_schema_aliasing = True - else: - self.legacy_schema_aliasing = legacy_schema_aliasing - self._warn_schema_aliasing = False + self.legacy_schema_aliasing = legacy_schema_aliasing super(MSDialect, self).__init__(**opts) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 4b3e5bcd1..2c78de2fc 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1916,38 +1916,7 @@ class MySQLCompiler(compiler.SQLCompiler): return None -# ug. "InnoDB needs indexes on foreign keys and referenced keys [...]. -# Starting with MySQL 4.1.2, these indexes are created automatically. -# In older versions, the indexes must be created explicitly or the -# creation of foreign key constraints fails." - class MySQLDDLCompiler(compiler.DDLCompiler): - def create_table_constraints(self, table, **kw): - """Get table constraints.""" - constraint_string = super( - MySQLDDLCompiler, self).create_table_constraints(table, **kw) - - # why self.dialect.name and not 'mysql'? because of drizzle - is_innodb = 'engine' in table.dialect_options[self.dialect.name] and \ - table.dialect_options[self.dialect.name][ - 'engine'].lower() == 'innodb' - - auto_inc_column = table._autoincrement_column - - if is_innodb and \ - auto_inc_column is not None and \ - auto_inc_column is not list(table.primary_key)[0]: - if constraint_string: - constraint_string += ", \n\t" - constraint_string += "KEY %s (%s)" % ( - self.preparer.quote( - "idx_autoinc_%s" % auto_inc_column.name - ), - self.preparer.format_column(auto_inc_column) - ) - - return constraint_string - def get_column_specification(self, column, **kw): """Builds column DDL.""" diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index c605bd510..82ec72f2b 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -287,6 +287,7 @@ from sqlalchemy import util, sql from sqlalchemy.engine import default, reflection from sqlalchemy.sql import compiler, visitors, expression from sqlalchemy.sql import operators as sql_operators +from sqlalchemy.sql.elements import quoted_name from sqlalchemy import types as sqltypes, schema as sa_schema from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, \ BLOB, CLOB, TIMESTAMP, FLOAT @@ -1032,6 +1033,8 @@ class OracleDialect(default.DefaultDialect): if name.upper() == name and not \ self.identifier_preparer._requires_quotes(name.lower()): return name.lower() + elif name.lower() == name: + return quoted_name(name, quote=True) else: return name @@ -1280,7 +1283,7 @@ class OracleDialect(default.DefaultDialect): 'type': coltype, 'nullable': nullable, 'default': default, - 'autoincrement': default is None + 'autoincrement': 'auto', } if orig_colname.lower() == orig_colname: cdict['quote'] = True diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 46f45a340..d67f2a07e 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -14,10 +14,10 @@ from .base import \ INET, CIDR, UUID, BIT, MACADDR, OID, DOUBLE_PRECISION, TIMESTAMP, TIME, \ DATE, BYTEA, BOOLEAN, INTERVAL, ENUM, dialect, TSVECTOR, DropEnumType, \ CreateEnumType -from .constraints import ExcludeConstraint from .hstore import HSTORE, hstore from .json import JSON, JSONB from .array import array, ARRAY, Any, All +from .ext import aggregate_order_by, ExcludeConstraint, array_agg from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \ TSTZRANGE @@ -26,8 +26,9 @@ __all__ = ( 'INTEGER', 'BIGINT', 'SMALLINT', 'VARCHAR', 'CHAR', 'TEXT', 'NUMERIC', 'FLOAT', 'REAL', 'INET', 'CIDR', 'UUID', 'BIT', 'MACADDR', 'OID', 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN', - 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE', + 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array', 'HSTORE', 'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE', - 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', - 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint' + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'Any', 'All', + 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint', + 'aggregate_order_by', 'array_agg' ) diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py index 84bd0ba92..b88f139de 100644 --- a/lib/sqlalchemy/dialects/postgresql/array.py +++ b/lib/sqlalchemy/dialects/postgresql/array.py @@ -7,6 +7,7 @@ from .base import ischema_names from ...sql import expression, operators +from ...sql.base import SchemaEventTarget from ... import types as sqltypes try: @@ -15,46 +16,32 @@ except ImportError: _python_UUID = None -class Any(expression.ColumnElement): +def Any(other, arrexpr, operator=operators.eq): + """A synonym for the :meth:`.ARRAY.Comparator.any` method. - """Represent the clause ``left operator ANY (right)``. ``right`` must be - an array expression. + This method is legacy and is here for backwards-compatiblity. .. seealso:: - :class:`.postgresql.ARRAY` - - :meth:`.postgresql.ARRAY.Comparator.any` - ARRAY-bound method + :func:`.expression.any_` """ - __visit_name__ = 'any' - def __init__(self, left, right, operator=operators.eq): - self.type = sqltypes.Boolean() - self.left = expression._literal_as_binds(left) - self.right = right - self.operator = operator + return arrexpr.any(other, operator) -class All(expression.ColumnElement): +def All(other, arrexpr, operator=operators.eq): + """A synonym for the :meth:`.ARRAY.Comparator.all` method. - """Represent the clause ``left operator ALL (right)``. ``right`` must be - an array expression. + This method is legacy and is here for backwards-compatiblity. .. seealso:: - :class:`.postgresql.ARRAY` - - :meth:`.postgresql.ARRAY.Comparator.all` - ARRAY-bound method + :func:`.expression.all_` """ - __visit_name__ = 'all' - def __init__(self, left, right, operator=operators.eq): - self.type = sqltypes.Boolean() - self.left = expression._literal_as_binds(left) - self.right = right - self.operator = operator + return arrexpr.all(other, operator) class array(expression.Tuple): @@ -105,7 +92,11 @@ class array(expression.Tuple): ]) def self_group(self, against=None): - return self + if (against in ( + operators.any_op, operators.all_op, operators.getitem)): + return expression.Grouping(self) + else: + return self CONTAINS = operators.custom_op("@>", precedence=5) @@ -115,180 +106,60 @@ CONTAINED_BY = operators.custom_op("<@", precedence=5) OVERLAP = operators.custom_op("&&", precedence=5) -class ARRAY(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): +class ARRAY(SchemaEventTarget, sqltypes.Array): """Postgresql ARRAY type. - Represents values as Python lists. - - An :class:`.ARRAY` type is constructed given the "type" - of element:: - - mytable = Table("mytable", metadata, - Column("data", ARRAY(Integer)) - ) - - The above type represents an N-dimensional array, - meaning Postgresql will interpret values with any number - of dimensions automatically. To produce an INSERT - construct that passes in a 1-dimensional array of integers:: + .. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now + a subclass of the core :class:`.Array` type. - connection.execute( - mytable.insert(), - data=[1,2,3] - ) + The :class:`.postgresql.ARRAY` type is constructed in the same way + as the core :class:`.Array` type; a member type is required, and a + number of dimensions is recommended if the type is to be used for more + than one dimension:: - The :class:`.ARRAY` type can be constructed given a fixed number - of dimensions:: + from sqlalchemy.dialects import postgresql mytable = Table("mytable", metadata, - Column("data", ARRAY(Integer, dimensions=2)) + Column("data", postgresql.ARRAY(Integer, dimensions=2)) ) - This has the effect of the :class:`.ARRAY` type - specifying that number of bracketed blocks when a :class:`.Table` - is used in a CREATE TABLE statement, or when the type is used - within a :func:`.expression.cast` construct; it also causes - the bind parameter and result set processing of the type - to optimize itself to expect exactly that number of dimensions. - Note that Postgresql itself still allows N dimensions with such a type. - - SQL expressions of type :class:`.ARRAY` have support for "index" and - "slice" behavior. The Python ``[]`` operator works normally here, given - integer indexes or slices. Note that Postgresql arrays default - to 1-based indexing. The operator produces binary expression - constructs which will produce the appropriate SQL, both for - SELECT statements:: - - select([mytable.c.data[5], mytable.c.data[2:7]]) - - as well as UPDATE statements when the :meth:`.Update.values` method - is used:: - - mytable.update().values({ - mytable.c.data[5]: 7, - mytable.c.data[2:7]: [1, 2, 3] - }) - - Multi-dimensional array index support is provided automatically based on - either the value specified for the :paramref:`.ARRAY.dimensions` parameter. - E.g. an :class:`.ARRAY` with dimensions set to 2 would return an expression - of type :class:`.ARRAY` for a single index operation:: - - type = ARRAY(Integer, dimensions=2) - - expr = column('x', type) # expr is of type ARRAY(Integer, dimensions=2) - - expr = column('x', type)[5] # expr is of type ARRAY(Integer, dimensions=1) - - An index expression from ``expr`` above would then return an expression - of type Integer:: - - sub_expr = expr[10] # expr is of type Integer - - .. versionadded:: 1.1 support for index operations on multi-dimensional - :class:`.postgresql.ARRAY` objects is added. - - :class:`.ARRAY` provides special methods for containment operations, - e.g.:: + The :class:`.postgresql.ARRAY` type provides all operations defined on the + core :class:`.Array` type, including support for "dimensions", indexed + access, and simple matching such as :meth:`.Array.Comparator.any` + and :meth:`.Array.Comparator.all`. :class:`.postgresql.ARRAY` class also + provides PostgreSQL-specific methods for containment operations, including + :meth:`.postgresql.ARRAY.Comparator.contains` + :meth:`.postgresql.ARRAY.Comparator.contained_by`, + and :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.:: mytable.c.data.contains([1, 2]) - For a full list of special methods see :class:`.ARRAY.Comparator`. + The :class:`.postgresql.ARRAY` type may not be supported on all + PostgreSQL DBAPIs; it is currently known to work on psycopg2 only. - .. versionadded:: 0.8 Added support for index and slice operations - to the :class:`.ARRAY` type, including support for UPDATE - statements, and special array containment operations. - - The :class:`.ARRAY` type may not be supported on all DBAPIs. - It is known to work on psycopg2 and not pg8000. - - Additionally, the :class:`.ARRAY` type does not work directly in + Additionally, the :class:`.postgresql.ARRAY` type does not work directly in conjunction with the :class:`.ENUM` type. For a workaround, see the special type at :ref:`postgresql_array_of_enum`. - See also: - - :class:`.postgresql.array` - produce a literal array value. - - """ - __visit_name__ = 'ARRAY' - - class Comparator( - sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator): - - """Define comparison operations for :class:`.ARRAY`.""" - - def _setup_getitem(self, index): - if isinstance(index, slice): - return_type = self.type - elif self.type.dimensions is None or self.type.dimensions == 1: - return_type = self.type.item_type - else: - adapt_kw = {'dimensions': self.type.dimensions - 1} - return_type = self.type.adapt(self.type.__class__, **adapt_kw) - - return operators.getitem, index, return_type - - def any(self, other, operator=operators.eq): - """Return ``other operator ANY (array)`` clause. - - Argument places are switched, because ANY requires array - expression to be on the right hand-side. - - E.g.:: - - from sqlalchemy.sql import operators - - conn.execute( - select([table.c.data]).where( - table.c.data.any(7, operator=operators.lt) - ) - ) - - :param other: expression to be compared - :param operator: an operator object from the - :mod:`sqlalchemy.sql.operators` - package, defaults to :func:`.operators.eq`. - - .. seealso:: - - :class:`.postgresql.Any` - - :meth:`.postgresql.ARRAY.Comparator.all` - - """ - return Any(other, self.expr, operator=operator) - - def all(self, other, operator=operators.eq): - """Return ``other operator ALL (array)`` clause. - - Argument places are switched, because ALL requires array - expression to be on the right hand-side. - - E.g.:: + .. seealso:: - from sqlalchemy.sql import operators + :class:`.types.Array` - base array type - conn.execute( - select([table.c.data]).where( - table.c.data.all(7, operator=operators.lt) - ) - ) + :class:`.postgresql.array` - produces a literal array value. - :param other: expression to be compared - :param operator: an operator object from the - :mod:`sqlalchemy.sql.operators` - package, defaults to :func:`.operators.eq`. + """ - .. seealso:: + class Comparator(sqltypes.Array.Comparator): - :class:`.postgresql.All` + """Define comparison operations for :class:`.ARRAY`. - :meth:`.postgresql.ARRAY.Comparator.any` + Note that these operations are in addition to those provided + by the base :class:`.types.Array.Comparator` class, including + :meth:`.types.Array.Comparator.any` and + :meth:`.types.Array.Comparator.all`. - """ - return All(other, self.expr, operator=operator) + """ def contains(self, other, **kwargs): """Boolean expression. Test if elements are a superset of the @@ -369,6 +240,18 @@ class ARRAY(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): def compare_values(self, x, y): return x == y + def _set_parent(self, column): + """Support SchemaEentTarget""" + + if isinstance(self.item_type, SchemaEventTarget): + self.item_type._set_parent(column) + + def _set_parent_with_dispatch(self, parent): + """Support SchemaEentTarget""" + + if isinstance(self.item_type, SchemaEventTarget): + self.item_type._set_parent_with_dispatch(parent) + def _proc_array(self, arr, itemproc, dim, collection): if dim is None: arr = list(arr) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ace366284..ec12e1145 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -518,6 +518,11 @@ as well as array literals: * :class:`.postgresql.array` - array literal +* :func:`.postgresql.array_agg` - ARRAY_AGG SQL function + +* :class:`.postgresql.aggregate_order_by` - helper for PG's ORDER BY aggregate + function syntax. + JSON Types ---------- @@ -1045,26 +1050,18 @@ class PGCompiler(compiler.SQLCompiler): self.process(element.stop, **kw), ) - def visit_any(self, element, **kw): - return "%s%sANY (%s)" % ( - self.process(element.left, **kw), - compiler.OPERATORS[element.operator], - self.process(element.right, **kw) - ) - - def visit_all(self, element, **kw): - return "%s%sALL (%s)" % ( - self.process(element.left, **kw), - compiler.OPERATORS[element.operator], - self.process(element.right, **kw) - ) - def visit_getitem_binary(self, binary, operator, **kw): return "%s[%s]" % ( self.process(binary.left, **kw), self.process(binary.right, **kw) ) + def visit_aggregate_order_by(self, element, **kw): + return "%s ORDER BY %s" % ( + self.process(element.target, **kw), + self.process(element.order_by, **kw) + ) + def visit_match_op_binary(self, binary, operator, **kw): if "postgresql_regconfig" in binary.modifiers: regconfig = self.render_literal_value( diff --git a/lib/sqlalchemy/dialects/postgresql/constraints.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 4cfc050de..9b2e3fd73 100644 --- a/lib/sqlalchemy/dialects/postgresql/constraints.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -1,11 +1,69 @@ -# Copyright (C) 2013-2015 the SQLAlchemy authors and contributors +# postgresql/ext.py +# Copyright (C) 2005-2015 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -from ...sql.schema import ColumnCollectionConstraint + from ...sql import expression -from ... import util +from ...sql import elements +from ...sql import functions +from ...sql.schema import ColumnCollectionConstraint +from .array import ARRAY + + +class aggregate_order_by(expression.ColumnElement): + """Represent a Postgresql aggregate order by expression. + + E.g.:: + + from sqlalchemy.dialects.postgresql import aggregate_order_by + expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) + stmt = select([expr]) + + would represent the expression:: + + SELECT array_agg(a ORDER BY b DESC) FROM table; + + Similarly:: + + expr = func.string_agg( + table.c.a, + aggregate_order_by(literal_column("','"), table.c.a) + ) + stmt = select([expr]) + + Would represent:: + + SELECT string_agg(a, ',' ORDER BY a) FROM table; + + .. versionadded:: 1.1 + + .. seealso:: + + :class:`.array_agg` + + """ + + __visit_name__ = 'aggregate_order_by' + + def __init__(self, target, order_by): + self.target = elements._literal_as_binds(target) + self.order_by = elements._literal_as_binds(order_by) + + def self_group(self, against=None): + return self + + def get_children(self, **kwargs): + return self.target, self.order_by + + def _copy_internals(self, clone=elements._clone, **kw): + self.target = clone(self.target, **kw) + self.order_by = clone(self.order_by, **kw) + + @property + def _from_objects(self): + return self.target._from_objects + self.order_by._from_objects class ExcludeConstraint(ColumnCollectionConstraint): @@ -96,3 +154,15 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE initially=self.initially) c.dispatch._update(self.dispatch) return c + + +def array_agg(*arg, **kw): + """Postgresql-specific form of :class:`.array_agg`, ensures + return type is :class:`.postgresql.ARRAY` and not + the plain :class:`.types.Array`. + + .. versionadded:: 1.1 + + """ + kw['type_'] = ARRAY(functions._type_from_args(arg)) + return functions.func.array_agg(*arg, **kw) diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 2a56649db..8a50270f5 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -258,7 +258,7 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): comparator_factory = Comparator @property - def evaluates_none(self): + def should_evaluate_none(self): return not self.none_as_null def bind_processor(self, dialect): diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index e19047b76..a1786d16c 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -853,12 +853,20 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): if not column.nullable: colspec += " NOT NULL" - if (column.primary_key and - column.table.dialect_options['sqlite']['autoincrement'] and - len(column.table.primary_key.columns) == 1 and - issubclass(column.type._type_affinity, sqltypes.Integer) and - not column.foreign_keys): - colspec += " PRIMARY KEY AUTOINCREMENT" + if column.primary_key: + if ( + column.autoincrement is True and + len(column.table.primary_key.columns) != 1 + ): + raise exc.CompileError( + "SQLite does not support autoincrement for " + "composite primary keys") + + if (column.table.dialect_options['sqlite']['autoincrement'] and + len(column.table.primary_key.columns) == 1 and + issubclass(column.type._type_affinity, sqltypes.Integer) and + not column.foreign_keys): + colspec += " PRIMARY KEY AUTOINCREMENT" return colspec @@ -894,11 +902,25 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): return preparer.format_table(table, use_schema=False) - def visit_create_index(self, create): + def visit_create_index(self, create, include_schema=False, + include_table_schema=True): index = create.element - - text = super(SQLiteDDLCompiler, self).visit_create_index( - create, include_table_schema=False) + self._verify_index_table(index) + preparer = self.preparer + text = "CREATE " + if index.unique: + text += "UNIQUE " + text += "INDEX %s ON %s (%s)" \ + % ( + self._prepared_index_name(index, + include_schema=True), + preparer.format_table(index.table, + use_schema=False), + ', '.join( + self.sql_compiler.process( + expr, include_table=False, literal_binds=True) for + expr in index.expressions) + ) whereclause = index.dialect_options["sqlite"]["where"] if whereclause is not None: @@ -1095,6 +1117,13 @@ class SQLiteDialect(default.DefaultDialect): return None @reflection.cache + def get_schema_names(self, connection, **kw): + s = "PRAGMA database_list" + dl = connection.execute(s) + + return [db[1] for db in dl if db[1] != "temp"] + + @reflection.cache def get_table_names(self, connection, schema=None, **kw): if schema is not None: qschema = self.identifier_preparer.quote_identifier(schema) @@ -1190,7 +1219,7 @@ class SQLiteDialect(default.DefaultDialect): 'type': coltype, 'nullable': nullable, 'default': default, - 'autoincrement': default is None, + 'autoincrement': 'auto', 'primary_key': primary_key, } @@ -1283,7 +1312,7 @@ class SQLiteDialect(default.DefaultDialect): fk = fks[numerical_id] = { 'name': None, 'constrained_columns': [], - 'referred_schema': None, + 'referred_schema': schema, 'referred_table': rtbl, 'referred_columns': [], } |