From 031ef0807838842a827135dbace760da7aec215e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 27 Aug 2013 20:43:22 -0400 Subject: - A rework to the way that "quoted" identifiers are handled, in that instead of relying upon various ``quote=True`` flags being passed around, these flags are converted into rich string objects with quoting information included at the point at which they are passed to common schema constructs like :class:`.Table`, :class:`.Column`, etc. This solves the issue of various methods that don't correctly honor the "quote" flag such as :meth:`.Engine.has_table` and related methods. The :class:`.quoted_name` object is a string subclass that can also be used explicitly if needed; the object will hold onto the quoting preferences passed and will also bypass the "name normalization" performed by dialects that standardize on uppercase symbols, such as Oracle, Firebird and DB2. The upshot is that the "uppercase" backends can now work with force-quoted names, such as lowercase-quoted names and new reserved words. [ticket:2812] --- lib/sqlalchemy/dialects/postgresql/base.py | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 6ccf7190e..8938b3193 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1094,7 +1094,7 @@ class PGDDLCompiler(compiler.DDLCompiler): if 'postgresql_using' in index.kwargs: using = index.kwargs['postgresql_using'] - text += "USING %s " % preparer.quote(using, index.quote) + text += "USING %s " % preparer.quote(using) ops = index.kwargs.get('postgresql_ops', {}) text += "(%s)" \ @@ -1128,7 +1128,7 @@ class PGDDLCompiler(compiler.DDLCompiler): elements = [] for c in constraint.columns: op = constraint.operators[c.name] - elements.append(self.preparer.quote(c.name, c.quote)+' WITH '+op) + elements.append(self.preparer.quote(c.name) + ' WITH '+op) text += "EXCLUDE USING %s (%s)" % (constraint.using, ', '.join(elements)) if constraint.where is not None: sqltext = sql_util.expression_as_ddl(constraint.where) @@ -1250,9 +1250,9 @@ class PGIdentifierPreparer(compiler.IdentifierPreparer): if not type_.name: raise exc.CompileError("Postgresql ENUM type requires a name.") - name = self.quote(type_.name, type_.quote) + name = self.quote(type_.name) if not self.omit_schema and use_schema and type_.schema is not None: - name = self.quote_schema(type_.schema, type_.quote) + "." + name + name = self.quote_schema(type_.schema) + "." + name return name -- cgit v1.2.1 From f7504bdbad5dc0d2685ada57128ece4ae7690ed3 Mon Sep 17 00:00:00 2001 From: Scott Schaefer Date: Fri, 6 Sep 2013 18:14:26 -0600 Subject: re.match to re.search Convert to re.search to eliminate the restriction on only matching the beginning of the string --- lib/sqlalchemy/dialects/postgresql/base.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 8938b3193..01bf6a829 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1543,7 +1543,7 @@ class PGDialect(default.DefaultDialect): def _get_server_version_info(self, connection): v = connection.execute("select version()").scalar() - m = re.match( + m = re.search( '(?:PostgreSQL|EnterpriseDB) ' '(\d+)\.(\d+)(?:\.(\d+))?(?:\.\d+)?(?:devel)?', v) -- cgit v1.2.1 From 94d421ca2f2d9f45b5feb4419a34b97a50b8d90b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 29 Sep 2013 17:24:29 -0400 Subject: - put exact version string in the test - use match with a .* preceding instead of search --- lib/sqlalchemy/dialects/postgresql/base.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 01bf6a829..ecbbf1674 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1543,8 +1543,8 @@ class PGDialect(default.DefaultDialect): def _get_server_version_info(self, connection): v = connection.execute("select version()").scalar() - m = re.search( - '(?:PostgreSQL|EnterpriseDB) ' + m = re.match( + '.*(?:PostgreSQL|EnterpriseDB) ' '(\d+)\.(\d+)(?:\.(\d+))?(?:\.\d+)?(?:devel)?', v) if not m: -- cgit v1.2.1 From 9d952e0a11709fe35ada2635a79043ca0fc7ffbf Mon Sep 17 00:00:00 2001 From: ijl Date: Fri, 11 Oct 2013 15:01:14 -0400 Subject: PostgreSQL foreign key inspection includes options --- lib/sqlalchemy/dialects/postgresql/base.py | 29 ++++++++++++++++++++++++----- 1 file changed, 24 insertions(+), 5 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ecbbf1674..71d9dc2f6 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1883,6 +1883,15 @@ class PGDialect(default.DefaultDialect): n.oid = c.relnamespace ORDER BY 1 """ + # http://www.postgresql.org/docs/9.0/static/sql-createtable.html + FK_REGEX = re.compile( + r'FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)' + r'[\s]?(MATCH (FULL|PARTIAL|SIMPLE)+)?' + r'[\s]?(ON UPDATE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?' + r'[\s]?(ON DELETE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?' + r'[\s]?(DEFERRABLE|NOT DEFERRABLE)?' + r'[\s]?(INITIALLY (DEFERRED|IMMEDIATE)+)?' + ) t = sql.text(FK_SQL, typemap={ 'conname': sqltypes.Unicode, @@ -1890,15 +1899,18 @@ class PGDialect(default.DefaultDialect): c = connection.execute(t, table=table_oid) fkeys = [] for conname, condef, conschema in c.fetchall(): - m = re.search('FOREIGN KEY \((.*?)\) REFERENCES ' - '(?:(.*?)\.)?(.*?)\((.*?)\)', condef).groups() + m = re.search(FK_REGEX, condef).groups() constrained_columns, referred_schema, \ - referred_table, referred_columns = m + referred_table, referred_columns, \ + _, match, _, onupdate, _, ondelete, \ + deferrable, _, initially = m + if deferrable is not None: + deferrable = True if deferrable == 'DEFERRABLE' else False constrained_columns = [preparer._unquote_identifier(x) for x in re.split(r'\s*,\s*', constrained_columns)] if referred_schema: - referred_schema =\ + referred_schema = \ preparer._unquote_identifier(referred_schema) elif schema is not None and schema == conschema: # no schema was returned by pg_get_constraintdef(). This @@ -1916,7 +1928,14 @@ class PGDialect(default.DefaultDialect): 'constrained_columns': constrained_columns, 'referred_schema': referred_schema, 'referred_table': referred_table, - 'referred_columns': referred_columns + 'referred_columns': referred_columns, + 'options': { + 'onupdate': onupdate, + 'ondelete': ondelete, + 'deferrable': deferrable, + 'initially': initially, + 'match': match + } } fkeys.append(fkey_d) return fkeys -- cgit v1.2.1 From 9bc9d5c1068be878118202259add3c2e1bcec0cb Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 12 Oct 2013 20:04:55 -0400 Subject: - Fixed bug in default compiler plus those of postgresql, mysql, and mssql to ensure that any literal SQL expression values are rendered directly as literals, instead of as bound parameters, within a CREATE INDEX statement. [ticket:2742] - don't need expression_as_ddl(); literal_binds and include_table take care of this functionality. --- lib/sqlalchemy/dialects/postgresql/base.py | 17 ++++++++--------- 1 file changed, 8 insertions(+), 9 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ecbbf1674..83ad1d46f 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1100,12 +1100,9 @@ class PGDDLCompiler(compiler.DDLCompiler): text += "(%s)" \ % ( ', '.join([ - self.sql_compiler.process(expr, include_table=False) + - - + self.sql_compiler.process( + expr, include_table=False, literal_binds=True) + (c.key in ops and (' ' + ops[c.key]) or '') - - for expr, c in zip(index.expressions, index.columns)]) ) @@ -1115,8 +1112,9 @@ class PGDDLCompiler(compiler.DDLCompiler): whereclause = None if whereclause is not None: - whereclause = sql_util.expression_as_ddl(whereclause) - where_compiled = self.sql_compiler.process(whereclause) + where_compiled = self.sql_compiler.process( + whereclause, include_table=False, + literal_binds=True) text += " WHERE " + where_compiled return text @@ -1131,8 +1129,9 @@ class PGDDLCompiler(compiler.DDLCompiler): elements.append(self.preparer.quote(c.name) + ' WITH '+op) text += "EXCLUDE USING %s (%s)" % (constraint.using, ', '.join(elements)) if constraint.where is not None: - sqltext = sql_util.expression_as_ddl(constraint.where) - text += ' WHERE (%s)' % self.sql_compiler.process(sqltext) + text += ' WHERE (%s)' % self.sql_compiler.process( + constraint.where, + literal_binds=True) text += self.define_constraint_deferrability(constraint) return text -- cgit v1.2.1 From a2cce1bf43552e699f2babe7e4750354f2d580fe Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 12 Oct 2013 20:21:18 -0400 Subject: Parenthesis will be applied to a compound SQL expression as rendered in the column list of a CREATE INDEX statement. [ticket:2742] --- lib/sqlalchemy/dialects/postgresql/base.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 83ad1d46f..06ee8c3a2 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1101,7 +1101,10 @@ class PGDDLCompiler(compiler.DDLCompiler): % ( ', '.join([ self.sql_compiler.process( - expr, include_table=False, literal_binds=True) + + expr.self_group() + if not isinstance(expr, expression.ColumnClause) + else expr, + include_table=False, literal_binds=True) + (c.key in ops and (' ' + ops[c.key]) or '') for expr, c in zip(index.expressions, index.columns)]) ) -- cgit v1.2.1 From a5dc173ea6735c2b0877c771d2cb0693ac8dca82 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 15 Oct 2013 19:06:21 -0400 Subject: - Added support for rendering ``SMALLSERIAL`` when a :class:`.SmallInteger` type is used on a primary key autoincrement column, based on server version detection of Postgresql version 9.2 or greater. [ticket:2840] --- lib/sqlalchemy/dialects/postgresql/base.py | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 06ee8c3a2..5efa2e983 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1039,12 +1039,15 @@ class PGCompiler(compiler.SQLCompiler): class PGDDLCompiler(compiler.DDLCompiler): def get_column_specification(self, column, **kwargs): + colspec = self.preparer.format_column(column) impl_type = column.type.dialect_impl(self.dialect) if column.primary_key and \ column is column.table._autoincrement_column and \ - not isinstance(impl_type, sqltypes.SmallInteger) and \ ( + self.dialect.supports_smallserial or + not isinstance(impl_type, sqltypes.SmallInteger) + ) and ( column.default is None or ( isinstance(column.default, schema.Sequence) and @@ -1052,6 +1055,8 @@ class PGDDLCompiler(compiler.DDLCompiler): )): if isinstance(impl_type, sqltypes.BigInteger): colspec += " BIGSERIAL" + elif isinstance(impl_type, sqltypes.SmallInteger): + colspec += " SMALLSERIAL" else: colspec += " SERIAL" else: @@ -1330,6 +1335,7 @@ class PGDialect(default.DefaultDialect): supports_native_enum = True supports_native_boolean = True + supports_smallserial = True supports_sequences = True sequences_optional = True @@ -1370,6 +1376,10 @@ class PGDialect(default.DefaultDialect): # psycopg2, others may have placed ENUM here as well self.colspecs.pop(ENUM, None) + # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689 + self.supports_smallserial = self.server_version_info >= (9, 2) + + def on_connect(self): if self.isolation_level is not None: def connect(conn): -- cgit v1.2.1 From fff90799098bd14ea68c158c787585f6dec130d4 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 18 Oct 2013 19:00:05 -0400 Subject: - Removed a 128-character truncation from the reflection of the server default for a column; this code was original from PG system views which truncated the string for readability. [ticket:2844] --- lib/sqlalchemy/dialects/postgresql/base.py | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 5efa2e983..fdb6e3b4a 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1687,8 +1687,7 @@ class PGDialect(default.DefaultDialect): SQL_COLS = """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), - (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) - for 128) + (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) -- cgit v1.2.1 From 4663ec98b226a7d495846f0d89c646110705bb30 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 20 Oct 2013 16:59:56 -0400 Subject: - The typing system now handles the task of rendering "literal bind" values, e.g. values that are normally bound parameters but due to context must be rendered as strings, typically within DDL constructs such as CHECK constraints and indexes (note that "literal bind" values become used by DDL as of :ticket:`2742`). A new method :meth:`.TypeEngine.literal_processor` serves as the base, and :meth:`.TypeDecorator.process_literal_param` is added to allow wrapping of a native literal rendering method. [ticket:2838] - enhance _get_colparams so that we can send flags like literal_binds into INSERT statements - add support in PG for inspecting standard_conforming_strings - add a new series of roundtrip tests based on INSERT of literal plus SELECT for basic literal rendering in dialect suite --- lib/sqlalchemy/dialects/postgresql/base.py | 25 ++++++++++++++++--------- 1 file changed, 16 insertions(+), 9 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index fdb6e3b4a..55c6b315a 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -210,7 +210,7 @@ import re from ... import sql, schema, exc, util from ...engine import default, reflection -from ...sql import compiler, expression, util as sql_util, operators +from ...sql import compiler, expression, operators from ... import types as sqltypes try: @@ -954,25 +954,30 @@ class PGCompiler(compiler.SQLCompiler): def visit_ilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) + return '%s ILIKE %s' % \ (self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and - (' ESCAPE ' + self.render_literal_value(escape, None)) - or '') + + ( + ' ESCAPE ' + + self.render_literal_value(escape, sqltypes.STRINGTYPE) + if escape else '' + ) def visit_notilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) return '%s NOT ILIKE %s' % \ (self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and - (' ESCAPE ' + self.render_literal_value(escape, None)) - or '') + + ( + ' ESCAPE ' + + self.render_literal_value(escape, sqltypes.STRINGTYPE) + if escape else '' + ) def render_literal_value(self, value, type_): value = super(PGCompiler, self).render_literal_value(value, type_) - # TODO: need to inspect "standard_conforming_strings" + if self.dialect._backslash_escapes: value = value.replace('\\', '\\\\') return value @@ -1357,7 +1362,6 @@ class PGDialect(default.DefaultDialect): inspector = PGInspector isolation_level = None - # TODO: need to inspect "standard_conforming_strings" _backslash_escapes = True def __init__(self, isolation_level=None, **kwargs): @@ -1379,6 +1383,9 @@ class PGDialect(default.DefaultDialect): # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689 self.supports_smallserial = self.server_version_info >= (9, 2) + self._backslash_escapes = connection.scalar( + "show standard_conforming_strings" + ) == 'off' def on_connect(self): if self.isolation_level is not None: -- cgit v1.2.1 From 1e2945d26c126f0aad0dbe1c06fdcb62d3172597 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 27 Oct 2013 18:14:44 -0400 Subject: - Fixed bug where index reflection would mis-interpret indkey values when using the pypostgresql adapter, which returns these values as lists vs. psycopg2's return type of string. [ticket:2855] --- lib/sqlalchemy/dialects/postgresql/base.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 5c88ee023..e1dc4af71 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1963,11 +1963,14 @@ class PGDialect(default.DefaultDialect): table_oid = self.get_table_oid(connection, table_name, schema, info_cache=kw.get('info_cache')) + # cast indkey as varchar since it's an int2vector, + # returned as a list by some drivers such as pypostgresql + IDX_SQL = """ SELECT i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, - a.attname, a.attnum, ix.indkey + a.attname, a.attnum, ix.indkey::varchar FROM pg_class t join pg_index ix on t.oid = ix.indrelid -- cgit v1.2.1 From 71c45937f9adbb64482fffcda75f8fe4d063e027 Mon Sep 17 00:00:00 2001 From: Mario Lassnig Date: Tue, 12 Nov 2013 23:08:51 +0100 Subject: add psql FOR UPDATE OF functionality --- lib/sqlalchemy/dialects/postgresql/base.py | 2 ++ 1 file changed, 2 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index e1dc4af71..19d7c81fa 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1015,6 +1015,8 @@ class PGCompiler(compiler.SQLCompiler): def for_update_clause(self, select): if select.for_update == 'nowait': + if select.for_update_of is not None: + return " FOR UPDATE OF " + select.for_update_of + " NOWAIT" return " FOR UPDATE NOWAIT" elif select.for_update == 'read': return " FOR SHARE" -- cgit v1.2.1 From 741da873841012d893ec08bd77a5ecc9237eaab8 Mon Sep 17 00:00:00 2001 From: Mario Lassnig Date: Thu, 14 Nov 2013 20:18:52 +0100 Subject: added ORM support --- lib/sqlalchemy/dialects/postgresql/base.py | 24 ++++++++++++++++-------- 1 file changed, 16 insertions(+), 8 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 19d7c81fa..ec22e8633 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -230,7 +230,7 @@ RESERVED_WORDS = set( "default", "deferrable", "desc", "distinct", "do", "else", "end", "except", "false", "fetch", "for", "foreign", "from", "grant", "group", "having", "in", "initially", "intersect", "into", "leading", "limit", - "localtime", "localtimestamp", "new", "not", "null", "off", "offset", + "localtime", "localtimestamp", "new", "not", "null", "of", "off", "offset", "old", "on", "only", "or", "order", "placing", "primary", "references", "returning", "select", "session_user", "some", "symmetric", "table", "then", "to", "trailing", "true", "union", "unique", "user", "using", @@ -1014,14 +1014,22 @@ class PGCompiler(compiler.SQLCompiler): return "" def for_update_clause(self, select): - if select.for_update == 'nowait': - if select.for_update_of is not None: - return " FOR UPDATE OF " + select.for_update_of + " NOWAIT" - return " FOR UPDATE NOWAIT" - elif select.for_update == 'read': - return " FOR SHARE" + + if select.for_update == 'read': + return ' FOR SHARE' elif select.for_update == 'read_nowait': - return " FOR SHARE NOWAIT" + return ' FOR SHARE NOWAIT' + + tmp = ' FOR UPDATE' + if isinstance(select.for_update_of, list): + tmp += ' OF ' + ', '.join([of[0] for of in select.for_update_of]) + elif isinstance(select.for_update_of, tuple): + tmp += ' OF ' + select.for_update_of[0] + + if select.for_update == 'nowait': + return tmp + ' NOWAIT' + elif select.for_update: + return tmp else: return super(PGCompiler, self).for_update_clause(select) -- cgit v1.2.1 From e9aaf8eb66343f247b1ec2189707f820e20a0629 Mon Sep 17 00:00:00 2001 From: Mario Lassnig Date: Thu, 28 Nov 2013 14:50:41 +0100 Subject: added LockmodeArgs --- lib/sqlalchemy/dialects/postgresql/base.py | 30 +++++++++++++++++++++--------- 1 file changed, 21 insertions(+), 9 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ec22e8633..089769975 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1015,20 +1015,32 @@ class PGCompiler(compiler.SQLCompiler): def for_update_clause(self, select): - if select.for_update == 'read': + tmp = ' FOR UPDATE' + + # backwards compatibility + if isinstance(select.for_update, bool): + return tmp + elif isinstance(select.for_update, str): + if select.for_update == 'nowait': + return tmp + ' NOWAIT' + elif select.for_update == 'read': + return ' FOR SHARE' + elif select.for_update == 'read_nowait': + return ' FOR SHARE NOWAIT' + + if select.for_update.mode == 'read': return ' FOR SHARE' - elif select.for_update == 'read_nowait': + elif select.for_update.mode == 'read_nowait': return ' FOR SHARE NOWAIT' - tmp = ' FOR UPDATE' - if isinstance(select.for_update_of, list): - tmp += ' OF ' + ', '.join([of[0] for of in select.for_update_of]) - elif isinstance(select.for_update_of, tuple): - tmp += ' OF ' + select.for_update_of[0] + if isinstance(select.for_update.of, list): + tmp += ' OF ' + ', '.join([of[0] for of in select.for_update.of]) + elif isinstance(select.for_update.of, tuple): + tmp += ' OF ' + select.for_update.of[0] - if select.for_update == 'nowait': + if select.for_update.mode == 'update_nowait': return tmp + ' NOWAIT' - elif select.for_update: + elif select.for_update.mode == 'update': return tmp else: return super(PGCompiler, self).for_update_clause(select) -- cgit v1.2.1 From bb60a8ad946dd331f546f06a156b7ebb87d1709d Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 28 Nov 2013 12:37:15 -0500 Subject: - work in progress, will squash --- lib/sqlalchemy/dialects/postgresql/base.py | 44 +++++++++++------------------- 1 file changed, 16 insertions(+), 28 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 089769975..091fdeda2 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1015,35 +1015,23 @@ class PGCompiler(compiler.SQLCompiler): def for_update_clause(self, select): - tmp = ' FOR UPDATE' - - # backwards compatibility - if isinstance(select.for_update, bool): - return tmp - elif isinstance(select.for_update, str): - if select.for_update == 'nowait': - return tmp + ' NOWAIT' - elif select.for_update == 'read': - return ' FOR SHARE' - elif select.for_update == 'read_nowait': - return ' FOR SHARE NOWAIT' - - if select.for_update.mode == 'read': - return ' FOR SHARE' - elif select.for_update.mode == 'read_nowait': - return ' FOR SHARE NOWAIT' - - if isinstance(select.for_update.of, list): - tmp += ' OF ' + ', '.join([of[0] for of in select.for_update.of]) - elif isinstance(select.for_update.of, tuple): - tmp += ' OF ' + select.for_update.of[0] - - if select.for_update.mode == 'update_nowait': - return tmp + ' NOWAIT' - elif select.for_update.mode == 'update': - return tmp + if select._for_update_arg.read: + tmp = " FOR SHARE" else: - return super(PGCompiler, self).for_update_clause(select) + tmp = " FOR UPDATE" + + if select._for_update_arg.nowait: + tmp += " NOWAIT" + + if select._for_update_arg.of: + # TODO: assuming simplistic c.table here + tables = set(c.table for c in select._for_update_arg.of) + tmp += " OF " + ", ".join( + self.process(table, asfrom=True) + for table in tables + ) + + return tmp def returning_clause(self, stmt, returning_cols): -- cgit v1.2.1 From 4aaf3753d75c68050c136e734c29aae5ff9504b4 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 28 Nov 2013 22:25:09 -0500 Subject: - fix up rendering of "of" - move out tests, dialect specific out of compiler, compiler tests use new API, legacy API tests in test_selecatble - add support for adaptation of ForUpdateArg, alias support in compilers --- lib/sqlalchemy/dialects/postgresql/base.py | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 091fdeda2..69b0fb040 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1020,17 +1020,17 @@ class PGCompiler(compiler.SQLCompiler): else: tmp = " FOR UPDATE" - if select._for_update_arg.nowait: - tmp += " NOWAIT" - if select._for_update_arg.of: # TODO: assuming simplistic c.table here tables = set(c.table for c in select._for_update_arg.of) tmp += " OF " + ", ".join( - self.process(table, asfrom=True) + self.process(table, ashint=True) for table in tables ) + if select._for_update_arg.nowait: + tmp += " NOWAIT" + return tmp def returning_clause(self, stmt, returning_cols): -- cgit v1.2.1 From 31cecebd4831fbf58310509c1486244a532d96b9 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 28 Nov 2013 23:23:27 -0500 Subject: - add support for specifying tables or entities for "of" - implement Query with_for_update() - rework docs and tests --- lib/sqlalchemy/dialects/postgresql/base.py | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 69b0fb040..7ad266b58 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1021,8 +1021,9 @@ class PGCompiler(compiler.SQLCompiler): tmp = " FOR UPDATE" if select._for_update_arg.of: - # TODO: assuming simplistic c.table here - tables = set(c.table for c in select._for_update_arg.of) + tables = util.OrderedSet( + c.table if isinstance(c, expression.ColumnClause) + else c for c in select._for_update_arg.of) tmp += " OF " + ", ".join( self.process(table, ashint=True) for table in tables -- cgit v1.2.1 From 207fafe7e35b945a41e139b5507a8d73c7b019db Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 29 Nov 2013 19:06:33 -0500 Subject: - add support for bindparam() called from AsFromText - get PG dialect to work around "no nonexistent binds" rule for now, though we might want to reconsider this behavior --- lib/sqlalchemy/dialects/postgresql/base.py | 29 ++++++++++++++++------------- 1 file changed, 16 insertions(+), 13 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 7ad266b58..8bcfcbf7c 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1545,12 +1545,6 @@ class PGDialect(default.DefaultDialect): return bool(cursor.first()) def has_type(self, connection, type_name, schema=None): - bindparams = [ - sql.bindparam('typname', - util.text_type(type_name), type_=sqltypes.Unicode), - sql.bindparam('nspname', - util.text_type(schema), type_=sqltypes.Unicode), - ] if schema is not None: query = """ SELECT EXISTS ( @@ -1560,6 +1554,7 @@ class PGDialect(default.DefaultDialect): AND n.nspname = :nspname ) """ + query = sql.text(query) else: query = """ SELECT EXISTS ( @@ -1568,7 +1563,17 @@ class PGDialect(default.DefaultDialect): AND pg_type_is_visible(t.oid) ) """ - cursor = connection.execute(sql.text(query, bindparams=bindparams)) + query = sql.text(query) + query = query.bindparams( + sql.bindparam('typname', + util.text_type(type_name), type_=sqltypes.Unicode), + ) + if schema is not None: + query = query.bindparams( + sql.bindparam('nspname', + util.text_type(schema), type_=sqltypes.Unicode), + ) + cursor = connection.execute(query) return bool(cursor.scalar()) def _get_server_version_info(self, connection): @@ -1608,12 +1613,10 @@ class PGDialect(default.DefaultDialect): table_name = util.text_type(table_name) if schema is not None: schema = util.text_type(schema) - s = sql.text(query, bindparams=[ - sql.bindparam('table_name', type_=sqltypes.Unicode), - sql.bindparam('schema', type_=sqltypes.Unicode) - ], - typemap={'oid': sqltypes.Integer} - ) + s = sql.text(query).bindparams(table_name=sqltypes.Unicode) + s = s.columns(oid=sqltypes.Integer) + if schema: + s = s.bindparams(sql.bindparam('schema', type_=sqltypes.Unicode)) c = connection.execute(s, table_name=table_name, schema=schema) table_oid = c.scalar() if table_oid is None: -- cgit v1.2.1 From 66773a8801a584d36b514e22a03d92d66fb2931b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 30 Nov 2013 13:53:26 -0500 Subject: - Fixed bug where values within an ENUM weren't escaped for single quote signs. Note that this is backwards-incompatible for existing workarounds that manually escape the single quotes. [ticket:2878] --- lib/sqlalchemy/dialects/postgresql/base.py | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 8bcfcbf7c..b80f269c1 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1090,7 +1090,9 @@ class PGDDLCompiler(compiler.DDLCompiler): return "CREATE TYPE %s AS ENUM (%s)" % ( self.preparer.format_type(type_), - ",".join("'%s'" % e for e in type_.enums) + ", ".join( + self.sql_compiler.process(sql.literal(e), literal_binds=True) + for e in type_.enums) ) def visit_drop_enum_type(self, drop): -- cgit v1.2.1 From 64288c7d6ffc021e2388aa764e9a3b921506c7a0 Mon Sep 17 00:00:00 2001 From: nathan Date: Mon, 9 Dec 2013 11:46:36 -0500 Subject: sqlalchemy/dialects/postgresql/__init__.py: - Added import references to JSON class sqlalchemy/dialects/postgresql/base.py: - Added visitor method for JSON class sqlalchemy/dialects/postgresql/pgjson (new): - JSON class, supports automatic serialization and deserialization of json data, as well as basic json operators. --- lib/sqlalchemy/dialects/postgresql/base.py | 3 +++ 1 file changed, 3 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index b80f269c1..6469f3b70 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1187,6 +1187,9 @@ class PGTypeCompiler(compiler.GenericTypeCompiler): def visit_HSTORE(self, type_): return "HSTORE" + def visit_JSON(self, type_): + return "JSON" + def visit_INT4RANGE(self, type_): return "INT4RANGE" -- cgit v1.2.1 From 96359c6f3e81f55275666e94d9b9cadb7a1d923e Mon Sep 17 00:00:00 2001 From: Noufal Ibrahim Date: Thu, 5 Dec 2013 17:36:57 +0530 Subject: Implements TSVECTOR type for postgresql. Signed-off-by: Noufal Ibrahim --- lib/sqlalchemy/dialects/postgresql/base.py | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index b80f269c1..5ef16c412 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -368,6 +368,10 @@ class UUID(sqltypes.TypeEngine): PGUuid = UUID +class TSVECTOR(sqltypes.TypeEngine): + __visit_name__ = 'TSVECTOR' + + class _Slice(expression.ColumnElement): __visit_name__ = 'slice' @@ -1163,6 +1167,9 @@ class PGDDLCompiler(compiler.DDLCompiler): class PGTypeCompiler(compiler.GenericTypeCompiler): + def visit_TSVECTOR(self, type): + return "TSVECTOR" + def visit_INET(self, type_): return "INET" -- cgit v1.2.1 From 4eb8437f61139179a18f63168ff9987acfd0f3ca Mon Sep 17 00:00:00 2001 From: Noufal Ibrahim Date: Thu, 5 Dec 2013 18:21:49 +0530 Subject: Updates documentation for tsvector type. Signed-off-by: Noufal Ibrahim --- lib/sqlalchemy/dialects/postgresql/base.py | 27 ++++++++++++++++++++++++++- 1 file changed, 26 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 5ef16c412..ed8a8c90f 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -369,6 +369,31 @@ class UUID(sqltypes.TypeEngine): PGUuid = UUID class TSVECTOR(sqltypes.TypeEngine): + """The TSVECTOR type implements the Postgresql text search type + TSVECTOR. + + It can be used to do full text queries on natural language + *documents*. + + Search queries are performed using the ``@@`` operator in + postgresql. This is made available with the ``match`` method + available on the column. + + This means that if you have a table ``Example`` with a column + ``text`` of type ``TSVECTOR``, you can create a search clause like + so + + :: + + Example.text.match("search string") + + which will be compiled to + + :: + + text @@ to_tsquery('search string') + + """ __visit_name__ = 'TSVECTOR' @@ -1169,7 +1194,7 @@ class PGDDLCompiler(compiler.DDLCompiler): class PGTypeCompiler(compiler.GenericTypeCompiler): def visit_TSVECTOR(self, type): return "TSVECTOR" - + def visit_INET(self, type_): return "INET" -- cgit v1.2.1 From 892c8762e681502fa0c5f5740a619f17f7848ea7 Mon Sep 17 00:00:00 2001 From: Noufal Ibrahim Date: Tue, 10 Dec 2013 00:46:39 +0530 Subject: Adds tsvector to ischema_names for reflection to work. Signed-off-by: Noufal Ibrahim --- lib/sqlalchemy/dialects/postgresql/base.py | 1 + 1 file changed, 1 insertion(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ed8a8c90f..7602304f8 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -942,6 +942,7 @@ ischema_names = { 'interval': INTERVAL, 'interval year to month': INTERVAL, 'interval day to second': INTERVAL, + 'tsvector' : TSVECTOR } -- cgit v1.2.1 From 164bff07496c345c3c57a8b26439aa6a0fbce3b8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 9 Dec 2013 21:27:14 -0500 Subject: - round trip test - changelog - some doc rearrangement --- lib/sqlalchemy/dialects/postgresql/base.py | 62 +++++++++++++++++++++--------- 1 file changed, 44 insertions(+), 18 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 7602304f8..d3380afdd 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -131,6 +131,44 @@ use the :meth:`._UpdateBase.returning` method on a per-statement basis:: where(table.c.name=='foo') print result.fetchall() +.. _postgresql_match: + +Full Text Search +---------------- + +SQLAlchemy makes available the Postgresql ``@@`` operator via the +:meth:`.ColumnElement.match` method on any textual column expression. +On a Postgresql dialect, an expression like the following:: + + select([sometable.c.text.match("search string")]) + +will emit to the database:: + + SELECT text @@ to_tsquery('search string') FROM table + +The Postgresql text search functions such as ``to_tsquery()`` +and ``to_tsvector()`` are available +explicitly using the standard :attr:`.func` construct. For example:: + + select([ + func.to_tsvector('fat cats ate rats').match('cat & rat') + ]) + +Emits the equivalent of:: + + SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') + +The :class:`.postgresql.TSVECTOR` type can provide for explicit CAST:: + + from sqlalchemy.dialects.postgresql import TSVECTOR + from sqlalchemy import select, cast + select([cast("some text", TSVECTOR)]) + +produces a statement equivalent to:: + + SELECT CAST('some text' AS TSVECTOR) AS anon_1 + + FROM ONLY ... ------------------------ @@ -369,29 +407,17 @@ class UUID(sqltypes.TypeEngine): PGUuid = UUID class TSVECTOR(sqltypes.TypeEngine): - """The TSVECTOR type implements the Postgresql text search type - TSVECTOR. + """The :class:`.postgresql.TSVECTOR` type implements the Postgresql + text search type TSVECTOR. It can be used to do full text queries on natural language - *documents*. + documents. - Search queries are performed using the ``@@`` operator in - postgresql. This is made available with the ``match`` method - available on the column. + .. versionadded:: 0.9.0 - This means that if you have a table ``Example`` with a column - ``text`` of type ``TSVECTOR``, you can create a search clause like - so - - :: - - Example.text.match("search string") - - which will be compiled to - - :: + .. seealso:: - text @@ to_tsquery('search string') + :ref:`postgresql_match` """ __visit_name__ = 'TSVECTOR' -- cgit v1.2.1 From fec03c88d659bf9a0b102dd328afac1ba3dc7f23 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 17 Dec 2013 17:46:09 -0500 Subject: - make the json serializer and deserializer per-dialect, so that we are compatible with psycopg2's per-connection/cursor approach. add round trip tests for both native and non-native. --- lib/sqlalchemy/dialects/postgresql/base.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index a7f838009..3edc28fed 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1439,9 +1439,12 @@ class PGDialect(default.DefaultDialect): _backslash_escapes = True - def __init__(self, isolation_level=None, **kwargs): + def __init__(self, isolation_level=None, json_serializer=None, + json_deserializer=None, **kwargs): default.DefaultDialect.__init__(self, **kwargs) self.isolation_level = isolation_level + self._json_deserializer = json_deserializer + self._json_serializer = json_serializer def initialize(self, connection): super(PGDialect, self).initialize(connection) -- cgit v1.2.1 From f89d4d216bd7605c920b7b8a10ecde6bfea2238c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 5 Jan 2014 16:57:05 -0500 Subject: - happy new year --- lib/sqlalchemy/dialects/postgresql/base.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 3edc28fed..b7979a3e5 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1,5 +1,5 @@ # postgresql/base.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -- cgit v1.2.1 From 1af8e2491dcbed723d2cdafd44fd37f1a6908e91 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 18 Jan 2014 19:26:56 -0500 Subject: - implement kwarg validation and type system for dialect-specific arguments; [ticket:2866] - add dialect specific kwarg functionality to ForeignKeyConstraint, ForeignKey --- lib/sqlalchemy/dialects/postgresql/base.py | 19 ++++++++++++------- 1 file changed, 12 insertions(+), 7 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index b7979a3e5..11bd3830d 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1171,11 +1171,11 @@ class PGDDLCompiler(compiler.DDLCompiler): preparer.format_table(index.table) ) - if 'postgresql_using' in index.kwargs: - using = index.kwargs['postgresql_using'] + using = index.dialect_options['postgresql']['using'] + if using: text += "USING %s " % preparer.quote(using) - ops = index.kwargs.get('postgresql_ops', {}) + ops = index.dialect_options["postgresql"]["ops"] text += "(%s)" \ % ( ', '.join([ @@ -1188,10 +1188,7 @@ class PGDDLCompiler(compiler.DDLCompiler): for expr, c in zip(index.expressions, index.columns)]) ) - if 'postgresql_where' in index.kwargs: - whereclause = index.kwargs['postgresql_where'] - else: - whereclause = None + whereclause = index.dialect_options["postgresql"]["where"] if whereclause is not None: where_compiled = self.sql_compiler.process( @@ -1437,6 +1434,14 @@ class PGDialect(default.DefaultDialect): inspector = PGInspector isolation_level = None + construct_arguments = [ + (schema.Index, { + "using": False, + "where": None, + "ops": {} + }) + ] + _backslash_escapes = True def __init__(self, isolation_level=None, json_serializer=None, -- cgit v1.2.1