module ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements
change_column(table_name, column_name, type, options = {}) Show source Changes the column of a table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 438
def change_column(table_name, column_name, type, options = {})
clear_cache!
quoted_table_name = quote_table_name(table_name)
sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale])
sql_type << "[]" if options[:array]
sql = "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{sql_type}"
sql << " USING #{options[:using]}" if options[:using]
if options[:cast_as]
sql << " USING CAST(#{quote_column_name(column_name)} AS #{type_to_sql(options[:cast_as], options[:limit], options[:precision], options[:scale])})"
end
execute sql
change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
end
change_column_default(table_name, column_name, default) Show source Changes the default value of a table column.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 455
def change_column_default(table_name, column_name, default)
clear_cache!
column = column_for(table_name, column_name)
return unless column
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_value(default, column)}"
end
end
change_column_null(table_name, column_name, null, default = nil) Show source # File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 470
def change_column_null(table_name, column_name, null, default = nil)
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_value(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")
end
Returns the current client message level.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 280
def client_min_messages
query('SHOW client_min_messages', 'SCHEMA')[0][0]
end
Set the client message level.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 285
def client_min_messages=(level)
execute("SET client_min_messages TO '#{level}'", 'SCHEMA')
end
Returns the current database collation.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 228
def collation
query(" SELECT pg_database.datcollate FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
end
Returns the list of all column definitions for a table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 195
def columns(table_name)
# Limit, precision, and scale are all handled by the superclass.
column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod|
oid = get_oid_type(oid.to_i, fmod.to_i, column_name, type)
default_value = extract_value_from_default(oid, default)
default_function = extract_default_function(default_value, default)
new_column(column_name, default_value, oid, type, notnull == 'f', default_function)
end
end
Create a new PostgreSQL database. Options include :owner
, :template
, :encoding
(defaults to utf8), :collation
, :ctype
, :tablespace
, and :connection_limit
(note that MySQL uses :charset
while 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 54
def create_database(name, options = {})
options = { encoding: 'utf8' }.merge!(options.symbolize_keys)
option_string = options.inject("") do |memo, (key, value)|
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
Creates a schema for the given schema name.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 253
def create_schema schema_name
execute "CREATE SCHEMA #{schema_name}"
end
Returns the current database ctype.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 235
def ctype
query(" SELECT pg_database.datctype FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
end
Returns the current database name.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 210
def current_database
query('select current_database()', 'SCHEMA')[0][0]
end
Returns the current schema name.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 215
def current_schema
query('SELECT current_schema', 'SCHEMA')[0][0]
end
data_source_exists?(name)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 98
def data_sources # :nodoc
select_values(" SELECT c.relname
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v','m') -- (r)elation/table, (v)iew, (m)aterialized view
AND n.nspname = ANY (current_schemas(false))
", 'SCHEMA')
end
Drops the schema for the given schema name.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 258
def drop_schema schema_name
execute "DROP SCHEMA #{schema_name} CASCADE"
end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 126
def drop_table(table_name, options = {})
execute "DROP TABLE #{quote_table_name(table_name)}#{' CASCADE' if options[:force] == :cascade}"
end
Returns the current database encoding format.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 220
def encoding
query(" SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database
WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 501
def foreign_keys(table_name)
fk_info = select_all " 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
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 = #{quote(table_name)}
AND t3.nspname = ANY (current_schemas(false))
ORDER BY c.conname
".strip_heredoc
fk_info.map do |row|
options = {
column: row['column'],
name: row['name'],
primary_key: row['primary_key']
}
options[:on_delete] = extract_foreign_key_action(row['on_delete'])
options[:on_update] = extract_foreign_key_action(row['on_update'])
ForeignKeyDefinition.new(table_name, row['to_table'], options)
end
end
index_name_exists?(table_name, index_name, default) Show source # File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 139
def index_name_exists?(table_name, index_name, default)
exec_query(" 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
WHERE i.relkind = 'i'
AND i.relname = '#{index_name}'
AND t.relname = '#{table_name}'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
", 'SCHEMA').rows.first[0].to_i > 0
end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 538
def index_name_length
63
end
Returns an array of indexes for the given table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 153
def indexes(table_name, name = nil)
result = query(" SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
FROM pg_class t
INNER JOIN pg_index d ON t.oid = d.indrelid
INNER JOIN pg_class i ON d.indexrelid = i.oid
WHERE i.relkind = 'i'
AND d.indisprimary = 'f'
AND t.relname = '#{table_name}'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
ORDER BY i.relname
", 'SCHEMA')
result.map do |row|
index_name = row[0]
unique = row[1] == 't'
indkey = row[2].split(" ")
inddef = row[3]
oid = row[4]
columns = Hash[query(" SELECT a.attnum, a.attname
FROM pg_attribute a
WHERE a.attrelid = #{oid}
AND a.attnum IN (#{indkey.join(",")})
", "SCHEMA")]
column_names = columns.values_at(*indkey).compact
unless column_names.empty?
# add info on sort order for columns (only desc order is explicitly specified, asc is the default)
desc_order_columns = inddef.scan(/(\w+) DESC/).flatten
orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {}
where = inddef.scan(/WHERE (.+)$/).flatten[0]
using = inddef.scan(/USING (.+?) /).flatten[0].to_sym
IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using)
end
end.compact
end
Returns just a table's primary key
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 399
def primary_key(table)
pks = exec_query(" SELECT attr.attname
FROM pg_attribute attr
INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = any(cons.conkey)
WHERE cons.contype = 'p'
AND cons.conrelid = '#{quote_table_name(table)}'::regclass
", 'SCHEMA').rows
return nil unless pks.count == 1
pks[0][0]
end
rename_index(table_name, old_name, new_name) Show source # File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 495
def rename_index(table_name, old_name, new_name)
validate_index_length!(table_name, new_name)
execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}"
end
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 417
def rename_table(table_name, new_name)
clear_cache!
execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
pk, seq = pk_and_sequence_for(new_name)
if seq && seq.identifier == "#{table_name}_#{pk}_seq"
new_seq = "#{new_name}_#{pk}_seq"
idx = "#{table_name}_pkey"
new_idx = "#{new_name}_pkey"
execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}"
execute "ALTER INDEX #{quote_table_name(idx)} RENAME TO #{quote_table_name(new_idx)}"
end
rename_table_indexes(table_name, new_name)
end
Returns true if schema exists.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 131
def schema_exists?(name)
exec_query(" SELECT COUNT(*)
FROM pg_namespace
WHERE nspname = '#{name}'
", 'SCHEMA').rows.first[0].to_i > 0
end
Returns an array of schema names.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 242
def schema_names
query(" SELECT nspname
FROM pg_namespace
WHERE nspname !~ '^pg_.*'
AND nspname NOT IN ('information_schema')
ORDER by nspname;
", 'SCHEMA').flatten
end
Returns the active schema search path.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 275
def schema_search_path
@schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
end
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 267
def schema_search_path=(schema_csv)
if schema_csv
execute("SET search_path TO #{schema_csv}", 'SCHEMA')
@schema_search_path = schema_csv
end
end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 298
def serial_sequence(table, column)
result = exec_query(" SELECT pg_get_serial_sequence('#{table}', '#{column}')
", 'SCHEMA')
result.rows.first.first
end
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)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 111
def table_exists?(name)
name = Utils.extract_schema_qualified_name(name.to_s)
return false unless name.identifier
exec_query(" SELECT COUNT(*)
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m') -- (r)elation/table, (v)iew, (m)aterialized view
AND c.relname = '#{name.identifier}'
AND n.nspname = #{name.schema ? "'#{name.schema}'" : 'ANY (current_schemas(false))'}
", 'SCHEMA').rows.first[0].to_i > 0
end
Returns the list of all tables in the schema search path.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 90
def tables(name = nil)
query(" SELECT tablename
FROM pg_tables
WHERE schemaname = ANY (current_schemas(false))
", 'SCHEMA').map { |row| row[0] }
end
type_to_sql(type, limit = nil, precision = nil, scale = nil) Show source Maps logical Rails types to PostgreSQL-specific data types.
Calls superclass method
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 543
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
case type.to_s
when 'binary'
# PostgreSQL doesn't support limits on binary (bytea) columns.
# The hard limit is 1Gb, because of a 32-bit size field, and TOAST.
case limit
when nil, 0..0x3fffffff; super(type)
else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
end
when 'text'
# PostgreSQL doesn't support limits on text columns.
# The hard limit is 1Gb, according to section 8.3 in the manual.
case limit
when nil, 0..0x3fffffff; super(type)
else raise(ActiveRecordError, "The limit on text can be at most 1GB - 1byte.")
end
when 'integer'
return 'integer' unless limit
case limit
when 1, 2; 'smallint'
when 3, 4; 'integer'
when 5..8; 'bigint'
else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with scale 0 instead.")
end
when 'datetime'
return super unless precision
case precision
when 0..6; "timestamp(#{precision})"
else raise(ActiveRecordError, "No timestamp type has precision of #{precision}. The allowed range of precision is from 0 to 6")
end
else
super
end
end
© 2004–2017 David Heinemeier Hansson
Licensed under the MIT License.