Support multiple schemas in table names for postgresql [#390 state:resolved]

Signed-off-by: Pratik Naik <pratiknaik@gmail.com>

2 files changed, 74 insertions, 1 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 913bb521ca..ec204d0f03 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -392,9 +392,28 @@ module ActiveRecord
         quote_string(s)
       end
 
+      # Checks the following cases:
+      #
+      # - table_name
+      # - "table.name"
+      # - schema_name.table_name
+      # - schema_name."table.name"
+      # - "schema.name".table_name
+      # - "schema.name"."table.name"
+      def quote_table_name(name)
+        schema, name_part = extract_pg_identifier_from_name(name.to_s)
+
+        unless name_part
+          quote_column_name(schema)
+        else
+          table_name, name_part = extract_pg_identifier_from_name(name_part)
+          "#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
+        end
+      end
+
       # Quotes column names for use in SQL queries.
       def quote_column_name(name) #:nodoc:
-        %("#{name}")
+        PGconn.quote_ident(name.to_s)
       end
 
       # Quote date/time values for use in SQL input. Includes microseconds
@@ -1045,6 +1064,16 @@ module ActiveRecord
              ORDER BY a.attnum
           end_sql
         end
+
+        def extract_pg_identifier_from_name(name)
+          match_data = name[0,1] == '"' ? name.match(/\"([^\"]+)\"/) : name.match(/([^\.]+)/)
+
+          if match_data
+            rest = name[match_data[0].length..-1]
+            rest = rest[1..-1] if rest[0,1] == "."
+            [match_data[1], (rest.length > 0 ? rest : nil)]
+          end
+        end
     end
   end
 end
diff --git a/activerecord/test/cases/schema_test_postgresql.rb b/activerecord/test/cases/schema_test_postgresql.rb
index 336a38765c..2d36bd0b22 100644
--- a/activerecord/test/cases/schema_test_postgresql.rb
+++ b/activerecord/test/cases/schema_test_postgresql.rb
@@ -18,9 +18,22 @@ class SchemaTest < ActiveRecord::TestCase
     'moment timestamp without time zone default now()'
   ]
 
+  class Thing1 < ActiveRecord::Base
+    set_table_name "test_schema.things"
+  end
+
+  class Thing2 < ActiveRecord::Base
+    set_table_name "test_schema2.things"
+  end
+
+  class Thing3 < ActiveRecord::Base
+    set_table_name 'test_schema."things.table"'
+  end
+
   def setup
     @connection = ActiveRecord::Base.connection
     @connection.execute "CREATE SCHEMA #{SCHEMA_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})"
+    @connection.execute "CREATE TABLE #{SCHEMA_NAME}.\"#{TABLE_NAME}.table\" (#{COLUMNS.join(',')})"
     @connection.execute "CREATE SCHEMA #{SCHEMA2_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})"
     @connection.execute "CREATE INDEX #{INDEX_A_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_A_COLUMN});"
     @connection.execute "CREATE INDEX #{INDEX_A_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_A_COLUMN});"
@@ -47,6 +60,37 @@ class SchemaTest < ActiveRecord::TestCase
     end
   end
 
+
+  def test_proper_encoding_of_table_name
+    assert_equal '"table_name"', @connection.quote_table_name('table_name')
+    assert_equal '"table.name"', @connection.quote_table_name('"table.name"')
+    assert_equal '"schema_name"."table_name"', @connection.quote_table_name('schema_name.table_name')
+    assert_equal '"schema_name"."table.name"', @connection.quote_table_name('schema_name."table.name"')
+    assert_equal '"schema.name"."table_name"', @connection.quote_table_name('"schema.name".table_name')
+    assert_equal '"schema.name"."table.name"', @connection.quote_table_name('"schema.name"."table.name"')
+  end
+
+  def test_classes_with_qualified_schema_name
+    assert_equal 0, Thing1.count
+    assert_equal 0, Thing2.count
+    assert_equal 0, Thing3.count
+
+    Thing1.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now)
+    assert_equal 1, Thing1.count
+    assert_equal 0, Thing2.count
+    assert_equal 0, Thing3.count
+
+    Thing2.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now)
+    assert_equal 1, Thing1.count
+    assert_equal 1, Thing2.count
+    assert_equal 0, Thing3.count
+
+    Thing3.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now)
+    assert_equal 1, Thing1.count
+    assert_equal 1, Thing2.count
+    assert_equal 1, Thing3.count
+  end
+
   def test_raise_on_unquoted_schema_name
     assert_raise(ActiveRecord::StatementInvalid) do
       with_schema_search_path '$user,public'