Module: ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements
Relationships & Source Files | |
Extension / Inclusion / Inheritance Descendants | |
Included In:
| |
Defined in: | activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb |
Instance Attribute Summary
-
#client_min_messages
rw
Returns the current client message level.
-
#client_min_messages=(level)
rw
Set the client message level.
-
#schema_search_path
rw
Returns the active schema search path.
-
#schema_search_path=(schema_csv)
rw
Sets the schema search path to a string of comma-separated schema names.
Instance Method Summary
-
#add_exclusion_constraint(table_name, expression, **options)
Adds a new exclusion constraint to the table.
- #add_foreign_key(from_table, to_table, **options)
-
#add_unique_constraint(table_name, column_name = nil, **options)
Adds a new unique constraint to the table.
-
#collation
Returns the current database collation.
-
#create_database(name, options = {})
Create a new
::ActiveRecord::ConnectionAdapters::PostgreSQL
database. -
#create_schema(schema_name)
Creates a schema for the given schema name.
-
#ctype
Returns the current database ctype.
-
#current_database
Returns the current database name.
-
#current_schema
Returns the current schema name.
-
#drop_schema(schema_name, **options)
Drops the schema for the given schema name.
-
#encoding
Returns the current database encoding format.
-
#exclusion_constraints(table_name)
Returns an array of exclusion constraints for the given table.
- #foreign_keys(table_name)
- #foreign_table_exists?(table_name) ⇒ Boolean
- #foreign_tables
-
#index_name_exists?(table_name, index_name) ⇒ Boolean
Verifies existence of an index with a given name.
-
#remove_exclusion_constraint(table_name, expression = nil, **options)
Removes the given exclusion constraint from the table.
-
#remove_unique_constraint(table_name, column_name = nil, **options)
Removes the given unique constraint from the table.
-
#rename_index(table_name, old_name, new_name)
Renames an index of a table.
-
#rename_table(table_name, new_name, **options)
Renames a table.
-
#schema_exists?(name) ⇒ Boolean
Returns true if schema exists.
-
#schema_names
Returns an array of schema names.
- #serial_sequence(table, column)
-
#unique_constraints(table_name)
Returns an array of unique constraints for the given table.
-
#validate_check_constraint(table_name, **options)
Validates the given check constraint.
-
#validate_constraint(table_name, constraint_name)
Validates the given constraint.
-
#validate_foreign_key(from_table, to_table = nil, **options)
Validates the given foreign key.
Instance Attribute Details
#client_min_messages (rw)
Returns the current client message level.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 239
def query_value("SHOW client_min_messages", "SCHEMA") end
#client_min_messages=(level) (rw)
Set the client message level.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 244
def (level) internal_execute("SET client_min_messages TO '#{level}'") end
#schema_search_path (rw)
Returns the active schema search path.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 234
def schema_search_path @schema_search_path ||= query_value("SHOW search_path", "SCHEMA") end
#schema_search_path=(schema_csv) (rw)
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: www.postgresql.org/docs/current/static/ddl-schemas.html
This should be not be called manually but set in database.yml.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 226
def schema_search_path=(schema_csv) if schema_csv internal_execute("SET search_path TO #{schema_csv}") @schema_search_path = schema_csv end end
Instance Method Details
#add_exclusion_constraint(table_name, expression, **options)
Adds a new exclusion constraint to the table. expression
is a ::String
representation of a list of exclusion elements and operators.
add_exclusion_constraint :products, "price WITH =, availability_range WITH &&", using: :gist, name: "price_check"
generates:
ALTER TABLE "products" ADD CONSTRAINT price_check EXCLUDE USING gist (price WITH =, availability_range WITH &&)
The options
hash can include the following keys:
:name
-
The constraint name. Defaults to
excl_rails_<identifier>
. :deferrable
-
Specify whether or not the exclusion constraint should be deferrable. Valid values are
false
or:immediate
or:deferred
to specify the default behavior. Defaults tofalse
.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 695
def add_exclusion_constraint(table_name, expression, ** ) = (table_name, expression, ) at = create_alter_table(table_name) at.add_exclusion_constraint(expression, ) execute schema_creation.accept(at) end
#add_foreign_key(from_table, to_table, **options)
[ GitHub ]# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 526
def add_foreign_key(from_table, to_table, ** ) if [:deferrable] == true ActiveRecord.deprecator.warn(<<~MSG) `deferrable: true` is deprecated in favor of `deferrable: :immediate`, and will be removed in Rails 7.2. MSG [:deferrable] = :immediate end assert_valid_deferrable( [:deferrable]) super end
#add_unique_constraint(table_name, column_name = nil, **options)
Adds a new unique constraint to the table.
add_unique_constraint :sections, [:position], deferrable: :deferred, name: "unique_position"
generates:
ALTER TABLE "sections" ADD CONSTRAINT unique_position UNIQUE (position) DEFERRABLE INITIALLY DEFERRED
If you want to change an existing unique index to deferrable, you can use :using_index
to create deferrable unique constraints.
add_unique_constraint :sections, deferrable: :deferred, name: "unique_position", using_index: "index_sections_on_position"
The options
hash can include the following keys:
:name
-
The constraint name. Defaults to
uniq_rails_<identifier>
. :deferrable
-
Specify whether or not the unique constraint should be deferrable. Valid values are
false
or:immediate
or:deferred
to specify the default behavior. Defaults tofalse
. :using_index
-
To specify an existing unique index name. Defaults to
nil
.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 746
def add_unique_constraint(table_name, column_name = nil, ** ) = (table_name, column_name, ) at = create_alter_table(table_name) at.add_unique_constraint(column_name, ) execute schema_creation.accept(at) end
#collation
Returns the current database collation.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 191
def collation query_value("SELECT datcollate FROM pg_database WHERE datname = current_database()", "SCHEMA") end
#create_database(name, options = {})
Create a new ::ActiveRecord::ConnectionAdapters::PostgreSQL
database. Options include :owner
, :template
, :encoding
(defaults to utf8), :collation
, :ctype
, :tablespace
, and :connection_limit
(note that ::ActiveRecord::ConnectionAdapters::MySQL
uses :charset
while ::ActiveRecord::ConnectionAdapters::PostgreSQL
uses :encoding
).
Example:
create_database config[:database], config
create_database 'foo_development', encoding: 'unicode'
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 22
def create_database(name, = {}) = { encoding: "utf8" }.merge!( .symbolize_keys) option_string = .each_with_object(+"") do |(key, value), memo| memo << case key when :owner " OWNER = \"#{value}\"" when :template " TEMPLATE = \"#{value}\"" when :encoding " ENCODING = '#{value}'" when :collation " LC_COLLATE = '#{value}'" when :ctype " LC_CTYPE = '#{value}'" when :tablespace " TABLESPACE = \"#{value}\"" when :connection_limit " CONNECTION LIMIT = #{value}" else "" end end execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" end
#create_schema(schema_name)
Creates a schema for the given schema name.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 212
def create_schema(schema_name) execute "CREATE SCHEMA #{quote_schema_name(schema_name)}" end
#ctype
Returns the current database ctype.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 196
def ctype query_value("SELECT datctype FROM pg_database WHERE datname = current_database()", "SCHEMA") end
#current_database
Returns the current database name.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 176
def current_database query_value("SELECT current_database()", "SCHEMA") end
#current_schema
Returns the current schema name.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 181
def current_schema query_value("SELECT current_schema", "SCHEMA") end
#drop_schema(schema_name, **options)
Drops the schema for the given schema name.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 217
def drop_schema(schema_name, ** ) execute "DROP SCHEMA#{' IF EXISTS' if [:if_exists]} #{quote_schema_name(schema_name)} CASCADE" end
#encoding
Returns the current database encoding format.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 186
def encoding query_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()", "SCHEMA") end
#exclusion_constraints(table_name)
Returns an array of exclusion constraints for the given table. The exclusion constraints are represented as ExclusionConstraintDefinition
objects.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 619
def exclusion_constraints(table_name) scope = quoted_scope(table_name) exclusion_info = internal_exec_query(<<-SQL, "SCHEMA") SELECT conname, pg_get_constraintdef(c.oid) AS constraintdef, c.condeferrable, c.condeferred FROM pg_constraint c JOIN pg_class t ON c.conrelid = t.oid JOIN pg_namespace n ON n.oid = c.connamespace WHERE c.contype = 'x' AND t.relname = #{scope[:name]} AND n.nspname = #{scope[:schema]} SQL exclusion_info.map do |row| method_and_elements, predicate = row["constraintdef"].split(" WHERE ") method_and_elements_parts = method_and_elements.match(/EXCLUDE(?: USING (?<using>\S+))? \((?<expression>.+)\)/) predicate.remove!(/ DEFERRABLE(?: INITIALLY (?:IMMEDIATE|DEFERRED))?/) if predicate predicate = predicate.from(2).to(-3) if predicate # strip 2 opening and closing parentheses deferrable = extract_constraint_deferrable(row["condeferrable"], row["condeferred"]) = { name: row["conname"], using: method_and_elements_parts["using"].to_sym, where: predicate, deferrable: deferrable } ExclusionConstraintDefinition.new(table_name, method_and_elements_parts["expression"], ) end end
#foreign_keys(table_name)
[ GitHub ]# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 540
def foreign_keys(table_name) scope = quoted_scope(table_name) fk_info = internal_exec_query(<<~SQL, "SCHEMA", allow_retry: true, materialize_transactions: false) 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, c.condeferrable AS deferrable, c.condeferred AS deferred, c.conkey, c.confkey, c.conrelid, c.confrelid FROM pg_constraint c JOIN pg_class t1 ON c.conrelid = t1.oid JOIN pg_class t2 ON c.confrelid = t2.oid JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid JOIN pg_namespace t3 ON c.connamespace = t3.oid WHERE c.contype = 'f' AND t1.relname = #{scope[:name]} AND t3.nspname = #{scope[:schema]} ORDER BY c.conname SQL fk_info.map do |row| to_table = Utils.unquote_identifier(row["to_table"]) conkey = row["conkey"].scan(/\d+/).map(&:to_i) confkey = row["confkey"].scan(/\d+/).map(&:to_i) if conkey.size > 1 column = column_names_from_column_numbers(row["conrelid"], conkey) primary_key = column_names_from_column_numbers(row["confrelid"], confkey) else column = Utils.unquote_identifier(row["column"]) primary_key = row["primary_key"] end = { column: column, name: row["name"], primary_key: primary_key } [:on_delete] = extract_foreign_key_action(row["on_delete"]) [:on_update] = extract_foreign_key_action(row["on_update"]) [:deferrable] = extract_constraint_deferrable(row["deferrable"], row["deferred"]) [:validate] = row["valid"] ForeignKeyDefinition.new(table_name, to_table, ) end end
#foreign_table_exists?(table_name) ⇒ Boolean
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 589
def foreign_table_exists?(table_name) query_values(data_source_sql(table_name, type: "FOREIGN TABLE"), "SCHEMA").any? if table_name.present? end
#foreign_tables
[ GitHub ]# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 585
def foreign_tables query_values(data_source_sql(type: "FOREIGN TABLE"), "SCHEMA") end
#index_name_exists?(table_name, index_name) ⇒ Boolean
Verifies existence of an index with a given name.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 68
def index_name_exists?(table_name, index_name) table = quoted_scope(table_name) index = quoted_scope(index_name) query_value(<<~SQL, "SCHEMA").to_i > 0 SELECT COUNT(*) FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid LEFT JOIN pg_namespace n ON n.oid = t.relnamespace WHERE i.relkind IN ('i', 'I') AND i.relname = #{index[:name]} AND t.relname = #{table[:name]} AND n.nspname = #{table[:schema]} SQL end
#remove_exclusion_constraint(table_name, expression = nil, **options)
Removes the given exclusion constraint from the table.
remove_exclusion_constraint :products, name: "price_check"
The expression
parameter will be ignored if present. It can be helpful to provide this in a migration’s change
method so it can be reverted. In that case, expression
will be used by #add_exclusion_constraint.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 718
def remove_exclusion_constraint(table_name, expression = nil, ** ) excl_name_to_delete = exclusion_constraint_for!(table_name, expression: expression, ** ).name at = create_alter_table(table_name) at.drop_exclusion_constraint(excl_name_to_delete) execute schema_creation.accept(at) end
#remove_unique_constraint(table_name, column_name = nil, **options)
Removes the given unique constraint from the table.
remove_unique_constraint :sections, name: "unique_position"
The column_name
parameter will be ignored if present. It can be helpful to provide this in a migration’s change
method so it can be reverted. In that case, column_name
will be used by #add_unique_constraint.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 773
def remove_unique_constraint(table_name, column_name = nil, ** ) unique_name_to_delete = unique_constraint_for!(table_name, column: column_name, ** ).name at = create_alter_table(table_name) at.drop_unique_constraint(unique_name_to_delete) execute schema_creation.accept(at) end
#rename_index(table_name, old_name, new_name)
Renames an index of a table. Raises error if length of new index name is greater than allowed limit.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 514
def rename_index(table_name, old_name, new_name) validate_index_length!(table_name, new_name) schema, = extract_schema_qualified_name(table_name) execute "ALTER INDEX #{quote_table_name(schema) + '.' if schema}#{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}" end
#rename_table(table_name, new_name, **options)
Renames a table. Also renames a table’s primary key sequence if the sequence name exists and matches the Active Record default.
Example:
rename_table('octopuses', 'octopi')
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 382
def rename_table(table_name, new_name, ** ) validate_table_length!(new_name) unless [:_uses_legacy_table_name] clear_cache! schema_cache.clear_data_source_cache!(table_name.to_s) schema_cache.clear_data_source_cache!(new_name.to_s) execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}" pk, seq = pk_and_sequence_for(new_name) if pk # PostgreSQL automatically creates an index for PRIMARY KEY with name consisting of # truncated table name and "_pkey" suffix fitting into max_identifier_length number of characters. max_pkey_prefix = max_identifier_length - "_pkey".size idx = "#{table_name[0, max_pkey_prefix]}_pkey" new_idx = "#{new_name[0, max_pkey_prefix]}_pkey" execute "ALTER INDEX #{quote_table_name(idx)} RENAME TO #{quote_table_name(new_idx)}" # PostgreSQL automatically creates a sequence for PRIMARY KEY with name consisting of # truncated table name and "#{primary_key}_seq" suffix fitting into max_identifier_length number of characters. max_seq_prefix = max_identifier_length - "_#{pk}_seq".size if seq && seq.identifier == "#{table_name[0, max_seq_prefix]}_#{pk}_seq" new_seq = "#{new_name[0, max_seq_prefix]}_#{pk}_seq" execute "ALTER TABLE #{seq.quoted} RENAME TO #{quote_table_name(new_seq)}" end end rename_table_indexes(table_name, new_name, ** ) end
#schema_exists?(name) ⇒ Boolean
Returns true if schema exists.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 63
def schema_exists?(name) query_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = #{quote(name)}", "SCHEMA").to_i > 0 end
#schema_names
Returns an array of schema names.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 201
def schema_names query_values(<<~SQL, "SCHEMA") SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' AND nspname NOT IN ('information_schema') ORDER by nspname; SQL end
#serial_sequence(table, column)
[ GitHub ]# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 259
def serial_sequence(table, column) query_value("SELECT pg_get_serial_sequence(#{quote(table)}, #{quote(column)})", "SCHEMA") end
#unique_constraints(table_name)
Returns an array of unique constraints for the given table. The unique constraints are represented as UniqueConstraintDefinition
objects.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 653
def unique_constraints(table_name) scope = quoted_scope(table_name) unique_info = internal_exec_query(<<~SQL, "SCHEMA", allow_retry: true, materialize_transactions: false) SELECT c.conname, c.conrelid, c.conkey, c.condeferrable, c.condeferred FROM pg_constraint c JOIN pg_class t ON c.conrelid = t.oid JOIN pg_namespace n ON n.oid = c.connamespace WHERE c.contype = 'u' AND t.relname = #{scope[:name]} AND n.nspname = #{scope[:schema]} SQL unique_info.map do |row| conkey = row["conkey"].delete("{}").split(",").map(&:to_i) columns = column_names_from_column_numbers(row["conrelid"], conkey) deferrable = extract_constraint_deferrable(row["condeferrable"], row["condeferred"]) = { name: row["conname"], deferrable: deferrable } UniqueConstraintDefinition.new(table_name, columns, ) end end
#validate_check_constraint(table_name, **options)
Validates the given check constraint.
validate_check_constraint :products, name: "price_check"
The options
hash accepts the same keys as add_check_constraint.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 879
def validate_check_constraint(table_name, ** ) chk_name_to_validate = check_constraint_for!(table_name, ** ).name validate_constraint table_name, chk_name_to_validate end
#validate_constraint(table_name, constraint_name)
Validates the given constraint.
Validates the constraint named constraint_name
on accounts
.
validate_constraint :accounts, :constraint_name
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 846
def validate_constraint(table_name, constraint_name) at = create_alter_table table_name at.validate_constraint constraint_name execute schema_creation.accept(at) end
#validate_foreign_key(from_table, to_table = nil, **options)
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 #add_foreign_key.
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 868
def validate_foreign_key(from_table, to_table = nil, ** ) fk_name_to_validate = foreign_key_for!(from_table, to_table: to_table, ** ).name validate_constraint from_table, fk_name_to_validate end