From 5c7f8c929b228063b224eaa17360dcc105788296 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Thu, 9 Jun 2011 09:47:01 +0800 Subject: Improve PostgreSQL adapter schema-awareness * table_exists? scoped by schema search path unless schema is explicitly named. Added tests and doc to clarify the behaviour * extract_schema_and_table tests and implementation extended to cover all cases * primary_key does not ignore schema information * add current_schema and schema_exists? methods * more robust table referencing in insert_sql and sql_for_insert methods --- .../connection_adapters/postgresql_adapter.rb | 76 +++++++++++++++------- 1 file changed, 51 insertions(+), 25 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters') diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 3e390ba994..2a3ee33e3e 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -466,10 +466,11 @@ module ActiveRecord # Executes an INSERT query and returns the new record's ID def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) - # Extract the table from the insert sql. Yuck. - _, table = extract_schema_and_table(sql.split(" ", 4)[2]) - - pk ||= primary_key(table) + unless pk + # Extract the table from the insert sql. Yuck. + table_ref = extract_table_ref_from_insert_sql(sql) + pk = primary_key(table_ref) if table_ref + end if pk select_value("#{sql} RETURNING #{quote_column_name(pk)}") @@ -565,9 +566,9 @@ module ActiveRecord def sql_for_insert(sql, pk, id_value, sequence_name, binds) unless pk - _, table = extract_schema_and_table(sql.split(" ", 4)[2]) - - pk = primary_key(table) + # Extract the table from the insert sql. Yuck. + table_ref = extract_table_ref_from_insert_sql(sql) + pk = primary_key(table_ref) if table_ref end sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk @@ -665,33 +666,48 @@ module ActiveRecord SQL end + # Returns true of table exists. + # If the schema is not specified as part of +name+ then it will only find tables within + # the current schema search path (regardless of permissions to access tables in other schemas) def table_exists?(name) schema, table = extract_schema_and_table(name.to_s) + return false unless table binds = [[nil, table.gsub(/(^"|"$)/,'')]] - binds << [nil, schema] if schema + binds << [nil, schema.gsub(/(^"|"$)/,'')] if schema exec_query(<<-SQL, 'SCHEMA', binds).rows.first[0].to_i > 0 - SELECT COUNT(*) - FROM pg_tables - WHERE tablename = $1 - #{schema ? "AND schemaname = $2" : ''} + SELECT COUNT(*) + FROM pg_tables + WHERE tablename = $1 + AND schemaname = #{schema ? '$2' : 'ANY (current_schemas(false))'} SQL end - # Extracts the table and schema name from +name+ - def extract_schema_and_table(name) - schema, table = name.split('.', 2) - - unless table # A table was provided without a schema - table = schema - schema = nil - end + # Returns true if schema exists. + def schema_exists?(name) + exec_query(<<-SQL, 'SCHEMA', [[nil, name]]).rows.first[0].to_i > 0 + SELECT COUNT(*) + FROM pg_namespace + WHERE nspname = $1 + SQL + end - if name =~ /^"/ # Handle quoted table names - table = name - schema = nil - end + # Returns an array of [schema_name, table_name] extracted from +name+. + # The schema_name will be nil if not provided in +name+. + # Quotes are preserved in the schema and table name components if provided. + # Valid combinations for quoting the schema and table names: + # + # - table_name + # - "table.name" + # - schema_name.table_name + # - schema_name."table.name" + # - "schema.name".table_name + # - "schema.name"."table_name" + # - "schema.name"."table.name" + def extract_schema_and_table(name) + name[/([^"\.\s]+|"[^"]+")(?:\.([^"\.\s]+|"[^"]*"))?/] + table, schema = [$1,$2].compact.reverse [schema, table] end @@ -742,6 +758,11 @@ module ActiveRecord query('select current_database()')[0][0] end + # Returns the current schema name. + def current_schema + query('SELECT current_schema', 'SCHEMA')[0][0] + end + # Returns the current database encoding format. def encoding query(<<-end_sql)[0][0] @@ -843,7 +864,7 @@ module ActiveRecord # Returns just a table's primary key def primary_key(table) - row = exec_query(<<-end_sql, 'SCHEMA', [[nil, table]]).rows.first + row = exec_query(<<-end_sql, 'SCHEMA', [[nil, quote_table_name(table)]]).rows.first SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid @@ -1080,6 +1101,11 @@ module ActiveRecord end end + def extract_table_ref_from_insert_sql(sql) + sql[/into\s+([^\(]*).*values\s*\(/i] + $1.strip if $1 + end + def table_definition TableDefinition.new(self) end -- cgit v1.2.3 From 019c263633242f4329f44f863705435f58e1d884 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Fri, 10 Jun 2011 22:48:58 +0800 Subject: apply private method indentation convention * tidy test code and fix my typo --- .../connection_adapters/postgresql_adapter.rb | 44 +++++++++++----------- 1 file changed, 22 insertions(+), 22 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters') diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 2a3ee33e3e..0ef871749f 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -666,7 +666,7 @@ module ActiveRecord SQL end - # Returns true of table exists. + # Returns true if table exists. # If the schema is not specified as part of +name+ then it will only find tables within # the current schema search path (regardless of permissions to access tables in other schemas) def table_exists?(name) @@ -981,27 +981,27 @@ module ActiveRecord end private - def exec_no_cache(sql, binds) - @connection.async_exec(sql) - end - - def exec_cache(sql, binds) - unless @statements.key? sql - nextkey = "a#{@statements.length + 1}" - @connection.prepare nextkey, sql - @statements[sql] = nextkey + def exec_no_cache(sql, binds) + @connection.async_exec(sql) end - key = @statements[sql] + def exec_cache(sql, binds) + unless @statements.key? sql + nextkey = "a#{@statements.length + 1}" + @connection.prepare nextkey, sql + @statements[sql] = nextkey + end - # Clear the queue - @connection.get_last_result - @connection.send_query_prepared(key, binds.map { |col, val| - type_cast(val, col) - }) - @connection.block - @connection.get_last_result - end + key = @statements[sql] + + # Clear the queue + @connection.get_last_result + @connection.send_query_prepared(key, binds.map { |col, val| + type_cast(val, col) + }) + @connection.block + @connection.get_last_result + end # The internal PostgreSQL identifier of the money data type. MONEY_COLUMN_TYPE_OID = 790 #:nodoc: @@ -1106,9 +1106,9 @@ module ActiveRecord $1.strip if $1 end - def table_definition - TableDefinition.new(self) - end + def table_definition + TableDefinition.new(self) + end end end end -- cgit v1.2.3 From 1d7c751bf703c729887e2d8a9ae104a8e6aef010 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Sat, 11 Jun 2011 02:07:08 +0800 Subject: remove table quoting in primary_key method * add/cleanup tests --- .../lib/active_record/connection_adapters/postgresql_adapter.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'activerecord/lib/active_record/connection_adapters') diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 0ef871749f..03a9624357 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -864,7 +864,7 @@ module ActiveRecord # Returns just a table's primary key def primary_key(table) - row = exec_query(<<-end_sql, 'SCHEMA', [[nil, quote_table_name(table)]]).rows.first + row = exec_query(<<-end_sql, 'SCHEMA', [[nil, table]]).rows.first SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid -- cgit v1.2.3 From f8c4b374c831d6f37efb7b7685fe1d8fe101e096 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Sat, 11 Jun 2011 18:34:09 +0800 Subject: make extract_schema_and_table a private method --- .../connection_adapters/postgresql_adapter.rb | 37 ++++++++++------------ 1 file changed, 17 insertions(+), 20 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters') diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 03a9624357..b6e7ddfc5b 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -673,8 +673,8 @@ module ActiveRecord schema, table = extract_schema_and_table(name.to_s) return false unless table - binds = [[nil, table.gsub(/(^"|"$)/,'')]] - binds << [nil, schema.gsub(/(^"|"$)/,'')] if schema + binds = [[nil, table]] + binds << [nil, schema] if schema exec_query(<<-SQL, 'SCHEMA', binds).rows.first[0].to_i > 0 SELECT COUNT(*) @@ -693,24 +693,6 @@ module ActiveRecord SQL end - # Returns an array of [schema_name, table_name] extracted from +name+. - # The schema_name will be nil if not provided in +name+. - # Quotes are preserved in the schema and table name components if provided. - # Valid combinations for quoting the schema and table names: - # - # - table_name - # - "table.name" - # - schema_name.table_name - # - schema_name."table.name" - # - "schema.name".table_name - # - "schema.name"."table_name" - # - "schema.name"."table.name" - def extract_schema_and_table(name) - name[/([^"\.\s]+|"[^"]+")(?:\.([^"\.\s]+|"[^"]*"))?/] - table, schema = [$1,$2].compact.reverse - [schema, table] - end - # Returns an array of indexes for the given table. def indexes(table_name, name = nil) schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') @@ -1101,6 +1083,21 @@ module ActiveRecord end end + # Returns an array of [schema_name, table_name] extracted from +name+. + # +schema_name+ is nil if not specified in +name+. + # +schema_name+ and +table_name+ exclude surrounding quotes (regardless of whether provided in +name+) + # +name+ supports the range of schema/table references understood by PostgreSQL, for example: + # + # * table_name + # * "table.name" + # * schema_name.table_name + # * schema_name."table.name" + # * "schema.name"."table name" + def extract_schema_and_table(name) + table, schema = name.scan(/[^".\s]+|"[^"]*"/)[0..1].collect{|m| m.gsub(/(^"|"$)/,'') }.reverse + [schema, table] + end + def extract_table_ref_from_insert_sql(sql) sql[/into\s+([^\(]*).*values\s*\(/i] $1.strip if $1 -- cgit v1.2.3 From b0d59907f733841280095b16c98a95574b3a0938 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Fri, 17 Jun 2011 21:57:47 +0800 Subject: Make PostgreSQL adapter view-compatible * amongst other things, allows meta_search to run against view-backed models --- .../lib/active_record/connection_adapters/postgresql_adapter.rb | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters') diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index b6e7ddfc5b..03c31c2394 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -678,9 +678,11 @@ module ActiveRecord exec_query(<<-SQL, 'SCHEMA', binds).rows.first[0].to_i > 0 SELECT COUNT(*) - FROM pg_tables - WHERE tablename = $1 - AND schemaname = #{schema ? '$2' : 'ANY (current_schemas(false))'} + FROM pg_class c + LEFT JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE c.relkind in ('v','r') + AND c.relname = $1 + AND n.nspname = #{schema ? '$2' : 'ANY (current_schemas(false))'} SQL end -- cgit v1.2.3