diff options
| -rw-r--r-- | activerecord/CHANGELOG | 2 | ||||
| -rwxr-xr-x | activerecord/Rakefile | 6 | ||||
| -rw-r--r-- | activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb | 112 | ||||
| -rw-r--r-- | activerecord/test/fixtures/db_definitions/postgresql.sql | 2 | ||||
| -rw-r--r-- | activerecord/test/schema_test_postgresql.rb | 63 |
5 files changed, 130 insertions, 55 deletions
diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 84d46062df..9615e78d6a 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,7 @@ *SVN* +* Comprehensive PostgreSQL schema support. Use the optional schema_search_path directive in database.yml to give a comma-separated list of schemas to search for your tables. This allows you, for example, to have tables in a shared schema without having to use a custom table name. See http://www.postgresql.org/docs/8.0/interactive/ddl-schemas.html to learn more. #827 [dave@cherryville.org] + * Corrected @@configurations typo #1410 [david@ruppconsulting.com] * Return PostgreSQL columns in the order they were declared #1374 [perlguy@gmail.com] diff --git a/activerecord/Rakefile b/activerecord/Rakefile index 62c33e44e2..6ceefb5522 100755 --- a/activerecord/Rakefile +++ b/activerecord/Rakefile @@ -28,20 +28,20 @@ task :default => [ :test_ruby_mysql, :test_mysql_ruby, :test_sqlite, :test_sqlit Rake::TestTask.new("test_ruby_mysql") { |t| t.libs << "test" << "test/connections/native_mysql" - t.pattern = 'test/*_test.rb' + t.pattern = 'test/*_test{,_mysql}.rb' t.verbose = true } Rake::TestTask.new("test_mysql_ruby") { |t| t.libs << "test" << "test/connections/native_mysql" - t.pattern = 'test/*_test.rb' + t.pattern = 'test/*_test{,_mysql}.rb' t.verbose = true } for adapter in %w( postgresql sqlite sqlite3 sqlserver db2 oci ) Rake::TestTask.new("test_#{adapter}") { |t| t.libs << "test" << "test/connections/native_#{adapter}" - t.pattern = 'test/*_test.rb' + t.pattern = "test/*_test{,_#{adapter}}.rb" t.verbose = true } end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 39990db8fc..66cbe3a58b 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -24,7 +24,6 @@ module ActiveRecord username = config[:username].to_s password = config[:password].to_s - schema_order = config[:schema_order] encoding = config[:encoding] min_messages = config[:min_messages] @@ -38,7 +37,7 @@ module ActiveRecord PGconn.connect(host, port, "", "", database, username, password), logger ) - pga.execute("SET search_path TO #{schema_order}") if schema_order + pga.schema_search_path = config[:schema_search_path] || config[:schema_order] pga.execute("SET client_encoding TO '#{encoding}'") if encoding pga.execute("SET client_min_messages TO '#{min_messages}'") if min_messages @@ -57,7 +56,7 @@ module ActiveRecord # * <tt>:username</tt> -- Defaults to nothing # * <tt>:password</tt> -- Defaults to nothing # * <tt>:database</tt> -- The name of the database. No default, must be provided. - # * <tt>:schema_order</tt> -- An optional schema order string that is using in a SET search_path TO <schema_order> call on connection. + # * <tt>:schema_search_path</tt> -- An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the :schema_order option. # * <tt>:encoding</tt> -- An optional client encoding that is using in a SET client_encoding TO <encoding> call on connection. # * <tt>:min_messages</tt> -- An optional client min messages that is using in a SET client_min_messages TO <min_messages> call on connection. class PostgreSQLAdapter < AbstractAdapter @@ -71,9 +70,8 @@ module ActiveRecord end def columns(table_name, name = nil) - table_structure(table_name).inject([]) do |columns, field| - columns << Column.new(field[0], field[2], field[1]) - columns + column_definitions(table_name).collect do |name, type, default| + Column.new(name, default_value(default), translate_field_type(type)) end end @@ -110,14 +108,32 @@ module ActiveRecord end def quote_column_name(name) - return "\"#{name}\"" + %("#{name}") end def adapter_name() 'PostgreSQL' end + + # Set the schema search path to a string of comma-separated schema names. + # Names beginning with $ are quoted (e.g. $user => '$user') + # See http://www.postgresql.org/docs/8.0/interactive/ddl-schemas.html + def schema_search_path=(schema_csv) + if schema_csv + execute "SET search_path TO #{schema_csv}" + @schema_search_path = nil + end + end + + def schema_search_path + @schema_search_path ||= query('SHOW search_path')[0][0] + end + + private + BYTEA_COLUMN_TYPE_OID = 17 + def last_insert_id(table, column = "id") sequence_name = "#{table}_#{column || 'id'}_seq" @connection.exec("SELECT currval('#{sequence_name}')")[0][0].to_i @@ -133,7 +149,7 @@ module ActiveRecord hashed_row = {} row.each_index do |cel_index| column = row[cel_index] - if res.type(cel_index) == 17 # type oid for bytea + if res.type(cel_index) == BYTEA_COLUMN_TYPE_OID column = unescape_bytea(column) end hashed_row[fields[cel_index]] = column @@ -156,53 +172,49 @@ module ActiveRecord s.gsub(/\\([0-9][0-9][0-9])/) { $1.oct.chr }.gsub(/\\\\/) { '\\' } unless s.nil? end - def split_table_schema(table_name) - schema_split = table_name.split('.') - schema_name = "public" - if schema_split.length > 1 - schema_name = schema_split.first.strip - table_name = schema_split.last.strip - end - return [schema_name, table_name] + # Query a table's column names, default values, and types. + # + # The underlying query is roughly: + # SELECT column.name, column.type, default.value + # FROM column LEFT JOIN default + # ON column.table_id = default.table_id + # AND column.num = default.column_num + # WHERE column.table_id = get_table_id('table_name') + # AND column.num > 0 + # AND NOT column.is_dropped + # ORDER BY column.num + # + # If the table name is not prefixed with a schema, the database will + # take the first match from the schema search path. + # + # Query implementation notes: + # - format_type includes the column size constraint, e.g. varchar(50) + # - ::regclass is a function that gives the id for a table name + def column_definitions(table_name) + query <<-end_sql + SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc + FROM pg_attribute a LEFT JOIN pg_attrdef d + ON a.attrelid = d.adrelid AND a.attnum = d.adnum + WHERE a.attrelid = '#{table_name}'::regclass + AND a.attnum > 0 AND NOT a.attisdropped + ORDER BY a.attnum + end_sql end - def table_structure(table_name) - database_name = @connection.db - schema_name, table_name = split_table_schema(table_name) - - # Grab a list of all the default values for the columns. - sql = "SELECT column_name, column_default, character_maximum_length, data_type " - sql << " FROM information_schema.columns " - sql << " WHERE table_catalog = '#{database_name}' " - sql << " AND table_schema = '#{schema_name}' " - sql << " AND table_name = '#{table_name}'" - sql << " ORDER BY ordinal_position" - - query(sql).collect do |row| - field = row[0] - type = type_as_string(row[3], row[2]) - default = default_value(row[1]) - length = row[2] - - [field, type, default, length] + # Translate PostgreSQL-specific types into simplified SQL types. + # These are special cases; standard types are handled by + # ConnectionAdapters::Column#simplified_type. + def translate_field_type(field_type) + # Match the beginning of field_type since it may have a size constraint on the end. + case field_type + when /^timestamp/i then 'datetime' + when /^real|^money/i then 'float' + when /^interval/i then 'string' + when /^bytea/i then 'binary' + else field_type # Pass through standard types. end end - def type_as_string(field_type, field_length) - type = case field_type - when 'numeric', 'real', 'money' then 'float' - when 'character varying', 'interval' then 'string' - when 'timestamp without time zone' then 'datetime' - when 'timestamp with time zone' then 'datetime' - when 'bytea' then 'binary' - else field_type - end - - size = field_length.nil? ? "" : "(#{field_length})" - - return type + size - end - def default_value(value) # Boolean types return "t" if value =~ /true/i diff --git a/activerecord/test/fixtures/db_definitions/postgresql.sql b/activerecord/test/fixtures/db_definitions/postgresql.sql index 6eb2304957..134f030f13 100644 --- a/activerecord/test/fixtures/db_definitions/postgresql.sql +++ b/activerecord/test/fixtures/db_definitions/postgresql.sql @@ -1,5 +1,3 @@ -SET search_path = public, pg_catalog; - CREATE TABLE accounts ( id serial, firm_id integer, diff --git a/activerecord/test/schema_test_postgresql.rb b/activerecord/test/schema_test_postgresql.rb new file mode 100644 index 0000000000..7f7d85ae43 --- /dev/null +++ b/activerecord/test/schema_test_postgresql.rb @@ -0,0 +1,63 @@ +require 'abstract_unit' + +class SchemaTest < Test::Unit::TestCase + SCHEMA_NAME = 'test_schema' + TABLE_NAME = 'things' + COLUMNS = [ + 'id integer', + 'name character varying(50)', + 'moment timestamp without time zone default now()' + ] + + def setup + @connection = ActiveRecord::Base.connection + @connection.execute "CREATE SCHEMA #{SCHEMA_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" + end + + def teardown + @connection.execute "DROP TABLE #{SCHEMA_NAME}.#{TABLE_NAME}" + @connection.execute "DROP SCHEMA #{SCHEMA_NAME}" + end + + def test_with_schema_prefixed_table_name + assert_nothing_raised do + assert_equal COLUMNS, columns("#{SCHEMA_NAME}.#{TABLE_NAME}") + end + end + + def test_with_schema_search_path + assert_nothing_raised do + with_schema_search_path(SCHEMA_NAME) do + assert_equal COLUMNS, columns(TABLE_NAME) + end + end + end + + def test_raise_on_unquoted_schema_name + assert_raise(ActiveRecord::StatementInvalid) do + with_schema_search_path '$user,public' + end + end + + def test_without_schema_search_path + assert_raise(ActiveRecord::StatementInvalid) { columns(TABLE_NAME) } + end + + def test_ignore_nil_schema_search_path + assert_nothing_raised { with_schema_search_path nil } + end + + private + def columns(table_name) + @connection.send(:column_definitions, table_name).map do |name, type, default| + "#{name} #{type}" + (default ? " default #{default}" : '') + end + end + + def with_schema_search_path(schema_search_path) + @connection.schema_search_path = schema_search_path + yield if block_given? + ensure + @connection.schema_search_path = "'$user', public" + end +end |