diff options
Diffstat (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb')
| -rw-r--r-- | activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb | 296 |
1 files changed, 207 insertions, 89 deletions
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb index cb45d7ba6b..7999434ad8 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -1,4 +1,4 @@ -require "active_support/core_ext/string/strip" +# frozen_string_literal: true module ActiveRecord module ConnectionAdapters @@ -38,7 +38,7 @@ module ActiveRecord " TABLESPACE = \"#{value}\"" when :connection_limit " CONNECTION LIMIT = #{value}" - else + else "" end end @@ -60,20 +60,15 @@ module ActiveRecord # Returns true if schema exists. def schema_exists?(name) - select_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = #{quote(name)}", "SCHEMA").to_i > 0 + query_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = #{quote(name)}", "SCHEMA").to_i > 0 end # Verifies existence of an index with a given name. - def index_name_exists?(table_name, index_name, default = nil) - unless default.nil? - ActiveSupport::Deprecation.warn(<<-MSG.squish) - Passing default to #index_name_exists? is deprecated without replacement. - MSG - end + def index_name_exists?(table_name, index_name) table = quoted_scope(table_name) index = quoted_scope(index_name) - select_value(<<-SQL, "SCHEMA").to_i > 0 + query_value(<<-SQL, "SCHEMA").to_i > 0 SELECT COUNT(*) FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid @@ -87,21 +82,12 @@ module ActiveRecord end # Returns an array of indexes for the given table. - def indexes(table_name, name = nil) # :nodoc: - if name - ActiveSupport::Deprecation.warn(<<-MSG.squish) - Passing name to #indexes is deprecated without replacement. - MSG - end - + def indexes(table_name) # :nodoc: scope = quoted_scope(table_name) result = query(<<-SQL, "SCHEMA") SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid, - pg_catalog.obj_description(i.oid, 'pg_class') AS comment, - (SELECT COUNT(*) FROM pg_opclass o - JOIN (SELECT unnest(string_to_array(d.indclass::text, ' '))::int oid) c - ON o.oid = c.oid WHERE o.opcdefault = 'f') + pg_catalog.obj_description(i.oid, 'pg_class') AS comment FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid @@ -120,11 +106,13 @@ module ActiveRecord inddef = row[3] oid = row[4] comment = row[5] - opclass = row[6] using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten - if indkey.include?(0) || opclass > 0 + orders = {} + opclasses = {} + + if indkey.include?(0) columns = expressions else columns = Hash[query(<<-SQL.strip_heredoc, "SCHEMA")].values_at(*indkey).compact @@ -134,10 +122,16 @@ module ActiveRecord AND a.attnum IN (#{indkey.join(",")}) SQL - # add info on sort order for columns (only desc order is explicitly specified, asc is the default) - orders = Hash[ - expressions.scan(/(\w+) DESC/).flatten.map { |order_column| [order_column, :desc] } - ] + # add info on sort order (only desc order is explicitly specified, asc is the default) + # and non-default opclasses + expressions.scan(/(?<column>\w+)\s?(?<opclass>\w+_ops)?\s?(?<desc>DESC)?\s?(?<nulls>NULLS (?:FIRST|LAST))?/).each do |column, opclass, desc, nulls| + opclasses[column] = opclass.to_sym if opclass + if nulls + orders[column] = [desc, nulls].compact.join(" ") + else + orders[column] = :desc if desc + end + end end IndexDefinition.new( @@ -146,6 +140,7 @@ module ActiveRecord unique, columns, orders: orders, + opclasses: opclasses, where: where, using: using.to_sym, comment: comment.presence @@ -163,7 +158,7 @@ module ActiveRecord def table_comment(table_name) # :nodoc: scope = quoted_scope(table_name, type: "BASE TABLE") if scope[:name] - select_value(<<-SQL.strip_heredoc, "SCHEMA") + query_value(<<-SQL.strip_heredoc, "SCHEMA") SELECT pg_catalog.obj_description(c.oid, 'pg_class') FROM pg_catalog.pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace @@ -176,32 +171,32 @@ module ActiveRecord # Returns the current database name. def current_database - select_value("SELECT current_database()", "SCHEMA") + query_value("SELECT current_database()", "SCHEMA") end # Returns the current schema name. def current_schema - select_value("SELECT current_schema", "SCHEMA") + query_value("SELECT current_schema", "SCHEMA") end # Returns the current database encoding format. def encoding - select_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()", "SCHEMA") + query_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()", "SCHEMA") end # Returns the current database collation. def collation - select_value("SELECT datcollate FROM pg_database WHERE datname = current_database()", "SCHEMA") + query_value("SELECT datcollate FROM pg_database WHERE datname = current_database()", "SCHEMA") end # Returns the current database ctype. def ctype - select_value("SELECT datctype FROM pg_database WHERE datname = current_database()", "SCHEMA") + query_value("SELECT datctype FROM pg_database WHERE datname = current_database()", "SCHEMA") end # Returns an array of schema names. def schema_names - select_values(<<-SQL, "SCHEMA") + query_values(<<-SQL, "SCHEMA") SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' @@ -222,7 +217,7 @@ module ActiveRecord # Sets the schema search path to a string of comma-separated schema names. # Names beginning with $ have to be quoted (e.g. $user => '$user'). - # See: http://www.postgresql.org/docs/current/static/ddl-schemas.html + # See: https://www.postgresql.org/docs/current/static/ddl-schemas.html # # This should be not be called manually but set in database.yml. def schema_search_path=(schema_csv) @@ -234,12 +229,12 @@ module ActiveRecord # Returns the active schema search path. def schema_search_path - @schema_search_path ||= select_value("SHOW search_path", "SCHEMA") + @schema_search_path ||= query_value("SHOW search_path", "SCHEMA") end # Returns the current client message level. def client_min_messages - select_value("SHOW client_min_messages", "SCHEMA") + query_value("SHOW client_min_messages", "SCHEMA") end # Set the client message level. @@ -257,7 +252,7 @@ module ActiveRecord end def serial_sequence(table, column) - select_value("SELECT pg_get_serial_sequence('#{table}', '#{column}')", "SCHEMA") + query_value("SELECT pg_get_serial_sequence(#{quote(table)}, #{quote(column)})", "SCHEMA") end # Sets the sequence of a table's primary key to the specified value. @@ -268,7 +263,7 @@ module ActiveRecord if sequence quoted_sequence = quote_table_name(sequence) - select_value("SELECT setval('#{quoted_sequence}', #{value})", "SCHEMA") + query_value("SELECT setval(#{quote(quoted_sequence)}, #{value})", "SCHEMA") else @logger.warn "#{table} has primary key #{pk} with no default sequence." if @logger end @@ -290,18 +285,16 @@ module ActiveRecord if pk && sequence quoted_sequence = quote_table_name(sequence) - max_pk = select_value("select MAX(#{quote_column_name pk}) from #{quote_table_name(table)}") + max_pk = query_value("SELECT MAX(#{quote_column_name pk}) FROM #{quote_table_name(table)}", "SCHEMA") if max_pk.nil? if postgresql_version >= 100000 - minvalue = select_value("SELECT seqmin from pg_sequence where seqrelid = '#{quoted_sequence}'::regclass") + minvalue = query_value("SELECT seqmin FROM pg_sequence WHERE seqrelid = #{quote(quoted_sequence)}::regclass", "SCHEMA") else - minvalue = select_value("SELECT min_value FROM #{quoted_sequence}") + minvalue = query_value("SELECT min_value FROM #{quoted_sequence}", "SCHEMA") end end - select_value(<<-end_sql, "SCHEMA") - SELECT setval('#{quoted_sequence}', #{max_pk ? max_pk : minvalue}, #{max_pk ? true : false}) - end_sql + query_value("SELECT setval(#{quote(quoted_sequence)}, #{max_pk ? max_pk : minvalue}, #{max_pk ? true : false})", "SCHEMA") end end @@ -325,7 +318,7 @@ module ActiveRecord AND seq.relnamespace = nsp.oid AND cons.contype = 'p' AND dep.classid = 'pg_class'::regclass - AND dep.refobjid = '#{quote_table_name(table)}'::regclass + AND dep.refobjid = #{quote(quote_table_name(table))}::regclass end_sql if result.nil? || result.empty? @@ -343,7 +336,7 @@ module ActiveRecord JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) JOIN pg_namespace nsp ON (t.relnamespace = nsp.oid) - WHERE t.oid = '#{quote_table_name(table)}'::regclass + WHERE t.oid = #{quote(quote_table_name(table))}::regclass AND cons.contype = 'p' AND pg_get_expr(def.adbin, def.adrelid) ~* 'nextval|uuid_generate' end_sql @@ -360,7 +353,7 @@ module ActiveRecord end def primary_keys(table_name) # :nodoc: - select_values(<<-SQL.strip_heredoc, "SCHEMA") + query_values(<<-SQL.strip_heredoc, "SCHEMA") SELECT a.attname FROM ( SELECT indrelid, indkey, generate_subscripts(indkey, 1) idx @@ -375,6 +368,31 @@ module ActiveRecord SQL end + def bulk_change_table(table_name, operations) + sql_fragments = [] + non_combinable_operations = [] + + operations.each do |command, args| + table, arguments = args.shift, args + method = :"#{command}_for_alter" + + if respond_to?(method, true) + sqls, procs = Array(send(method, table, *arguments)).partition { |v| v.is_a?(String) } + sql_fragments << sqls + non_combinable_operations.concat(procs) + else + execute "ALTER TABLE #{quote_table_name(table_name)} #{sql_fragments.join(", ")}" unless sql_fragments.empty? + non_combinable_operations.each(&:call) + sql_fragments = [] + non_combinable_operations = [] + send(command, table, *arguments) + end + end + + execute "ALTER TABLE #{quote_table_name(table_name)} #{sql_fragments.join(", ")}" unless sql_fragments.empty? + non_combinable_operations.each(&:call) + end + # Renames a table. # Also renames a table's primary key sequence if the sequence name exists and # matches the Active Record default. @@ -405,50 +423,23 @@ module ActiveRecord def change_column(table_name, column_name, type, options = {}) #:nodoc: clear_cache! - quoted_table_name = quote_table_name(table_name) - quoted_column_name = quote_column_name(column_name) - sql_type = type_to_sql(type, options) - sql = "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}" - if options[:collation] - sql << " COLLATE \"#{options[:collation]}\"" - end - if options[:using] - sql << " USING #{options[:using]}" - elsif options[:cast_as] - cast_as_type = type_to_sql(options[:cast_as], options) - sql << " USING CAST(#{quoted_column_name} AS #{cast_as_type})" - end - execute sql - - change_column_default(table_name, column_name, options[:default]) if options.key?(:default) - change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) - change_column_comment(table_name, column_name, options[:comment]) if options.key?(:comment) + sqls, procs = Array(change_column_for_alter(table_name, column_name, type, options)).partition { |v| v.is_a?(String) } + execute "ALTER TABLE #{quote_table_name(table_name)} #{sqls.join(", ")}" + procs.each(&:call) end # Changes the default value of a table column. def change_column_default(table_name, column_name, default_or_changes) # :nodoc: - clear_cache! - column = column_for(table_name, column_name) - return unless column - - default = extract_new_default_value(default_or_changes) - alter_column_query = "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} %s" - if default.nil? - # <tt>DEFAULT NULL</tt> results in the same behavior as <tt>DROP DEFAULT</tt>. However, PostgreSQL will - # cast the default to the columns type, which leaves us with a default like "default NULL::character varying". - execute alter_column_query % "DROP DEFAULT" - else - execute alter_column_query % "SET DEFAULT #{quote_default_expression(default, column)}" - end + execute "ALTER TABLE #{quote_table_name(table_name)} #{change_column_default_for_alter(table_name, column_name, default_or_changes)}" end def change_column_null(table_name, column_name, null, default = nil) #:nodoc: clear_cache! unless null || default.nil? column = column_for(table_name, column_name) - execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote_default_expression(default, column)} WHERE #{quote_column_name(column_name)} IS NULL") if column + execute "UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote_default_expression(default, column)} WHERE #{quote_column_name(column_name)} IS NULL" if column end - execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") + execute "ALTER TABLE #{quote_table_name(table_name)} #{change_column_null_for_alter(table_name, column_name, null, default)}" end # Adds comment for given table column or drops it if +comment+ is a +nil+ @@ -471,8 +462,8 @@ module ActiveRecord end def add_index(table_name, column_name, options = {}) #:nodoc: - index_name, index_type, index_columns, index_options, index_algorithm, index_using, comment = add_index_options(table_name, column_name, options) - execute("CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns})#{index_options}").tap do + index_name, index_type, index_columns_and_opclasses, index_options, index_algorithm, index_using, comment = add_index_options(table_name, column_name, options) + execute("CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns_and_opclasses})#{index_options}").tap do execute "COMMENT ON INDEX #{quote_column_name(index_name)} IS #{quote(comment)}" if comment end end @@ -511,8 +502,8 @@ module ActiveRecord def foreign_keys(table_name) scope = quoted_scope(table_name) - fk_info = select_all(<<-SQL.strip_heredoc, "SCHEMA") - SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete + fk_info = exec_query(<<-SQL.strip_heredoc, "SCHEMA") + SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete, c.convalidated AS valid FROM pg_constraint c JOIN pg_class t1 ON c.conrelid = t1.oid JOIN pg_class t2 ON c.confrelid = t2.oid @@ -534,11 +525,20 @@ module ActiveRecord options[:on_delete] = extract_foreign_key_action(row["on_delete"]) options[:on_update] = extract_foreign_key_action(row["on_update"]) + options[:validate] = row["valid"] ForeignKeyDefinition.new(table_name, row["to_table"], options) end end + def foreign_tables + query_values(data_source_sql(type: "FOREIGN TABLE"), "SCHEMA") + end + + def foreign_table_exists?(table_name) + query_values(data_source_sql(table_name, type: "FOREIGN TABLE"), "SCHEMA").any? if table_name.present? + end + # Maps logical Rails types to PostgreSQL-specific data types. def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **) # :nodoc: sql = \ @@ -568,7 +568,7 @@ module ActiveRecord super end - sql << "[]" if array && type != :primary_key + sql = "#{sql}[]" if array && type != :primary_key sql end @@ -586,6 +586,51 @@ module ActiveRecord [super, *order_columns].join(", ") end + def update_table_definition(table_name, base) # :nodoc: + PostgreSQL::Table.new(table_name, base) + end + + def create_schema_dumper(options) # :nodoc: + PostgreSQL::SchemaDumper.create(self, options) + end + + # Validates the given constraint. + # + # Validates the constraint named +constraint_name+ on +accounts+. + # + # validate_constraint :accounts, :constraint_name + def validate_constraint(table_name, constraint_name) + return unless supports_validate_constraints? + + at = create_alter_table table_name + at.validate_constraint constraint_name + + execute schema_creation.accept(at) + end + + # Validates the given foreign key. + # + # Validates the foreign key on +accounts.branch_id+. + # + # validate_foreign_key :accounts, :branches + # + # Validates the foreign key on +accounts.owner_id+. + # + # validate_foreign_key :accounts, column: :owner_id + # + # Validates the foreign key named +special_fk_name+ on the +accounts+ table. + # + # validate_foreign_key :accounts, name: :special_fk_name + # + # The +options+ hash accepts the same keys as SchemaStatements#add_foreign_key. + def validate_foreign_key(from_table, options_or_to_table = {}) + return unless supports_validate_constraints? + + fk_name_to_validate = foreign_key_for!(from_table, options_or_to_table).name + + validate_constraint from_table, fk_name_to_validate + end + private def schema_creation PostgreSQL::SchemaCreation.new(self) @@ -595,6 +640,10 @@ module ActiveRecord PostgreSQL::TableDefinition.new(*args) end + def create_alter_table(name) + PostgreSQL::AlterTable.new create_table_definition(name) + end + def new_column_from_field(table_name, field) column_name, type, default, notnull, oid, fmod, collation, comment = field type_metadata = fetch_type_metadata(column_name, type, oid.to_i, fmod.to_i) @@ -609,7 +658,8 @@ module ActiveRecord table_name, default_function, collation, - comment: comment.presence + comment: comment.presence, + max_identifier_length: max_identifier_length ) end @@ -633,11 +683,77 @@ module ActiveRecord end end + def change_column_sql(table_name, column_name, type, options = {}) + quoted_column_name = quote_column_name(column_name) + sql_type = type_to_sql(type, options) + sql = "ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}".dup + if options[:collation] + sql << " COLLATE \"#{options[:collation]}\"" + end + if options[:using] + sql << " USING #{options[:using]}" + elsif options[:cast_as] + cast_as_type = type_to_sql(options[:cast_as], options) + sql << " USING CAST(#{quoted_column_name} AS #{cast_as_type})" + end + + sql + end + + def change_column_for_alter(table_name, column_name, type, options = {}) + sqls = [change_column_sql(table_name, column_name, type, options)] + sqls << change_column_default_for_alter(table_name, column_name, options[:default]) if options.key?(:default) + sqls << change_column_null_for_alter(table_name, column_name, options[:null], options[:default]) if options.key?(:null) + sqls << Proc.new { change_column_comment(table_name, column_name, options[:comment]) } if options.key?(:comment) + sqls + end + + + # Changes the default value of a table column. + def change_column_default_for_alter(table_name, column_name, default_or_changes) # :nodoc: + column = column_for(table_name, column_name) + return unless column + + default = extract_new_default_value(default_or_changes) + alter_column_query = "ALTER COLUMN #{quote_column_name(column_name)} %s" + if default.nil? + # <tt>DEFAULT NULL</tt> results in the same behavior as <tt>DROP DEFAULT</tt>. However, PostgreSQL will + # cast the default to the columns type, which leaves us with a default like "default NULL::character varying". + alter_column_query % "DROP DEFAULT" + else + alter_column_query % "SET DEFAULT #{quote_default_expression(default, column)}" + end + end + + def change_column_null_for_alter(table_name, column_name, null, default = nil) #:nodoc: + "ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL" + end + + def add_timestamps_for_alter(table_name, options = {}) + [add_column_for_alter(table_name, :created_at, :datetime, options), add_column_for_alter(table_name, :updated_at, :datetime, options)] + end + + def remove_timestamps_for_alter(table_name, options = {}) + [remove_column_for_alter(table_name, :updated_at), remove_column_for_alter(table_name, :created_at)] + end + + def add_index_opclass(quoted_columns, **options) + opclasses = options_for_index_columns(options[:opclass]) + quoted_columns.each do |name, column| + column << " #{opclasses[name]}" if opclasses[name].present? + end + end + + def add_options_for_index_columns(quoted_columns, **options) + quoted_columns = add_index_opclass(quoted_columns, options) + super + end + def data_source_sql(name = nil, type: nil) scope = quoted_scope(name, type: type) - scope[:type] ||= "'r','v','m'" # (r)elation/table, (v)iew, (m)aterialized view + scope[:type] ||= "'r','v','m','f'" # (r)elation/table, (v)iew, (m)aterialized view, (f)oreign table - sql = "SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace" + sql = "SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace".dup sql << " WHERE n.nspname = #{scope[:schema]}" sql << " AND c.relname = #{scope[:name]}" if scope[:name] sql << " AND c.relkind IN (#{scope[:type]})" @@ -652,6 +768,8 @@ module ActiveRecord "'r'" when "VIEW" "'v','m'" + when "FOREIGN TABLE" + "'f'" end scope = {} scope[:schema] = schema ? quote(schema) : "ANY (current_schemas(false))" |