module ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements
Public Instance Methods
Returns the current client message level.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 320 def client_min_messages select_value('SHOW client_min_messages', 'SCHEMA') end
Set the client message level.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 325 def client_min_messages=(level) execute("SET client_min_messages TO '#{level}'", 'SCHEMA') end
Returns the current database collation.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 272 def collation select_value("SELECT datcollate FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA') 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 38 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 293 def create_schema schema_name execute "CREATE SCHEMA #{quote_schema_name(schema_name)}" end
Returns the current database ctype.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 277 def ctype select_value("SELECT datctype FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA') end
Returns the current database name.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 257 def current_database select_value('select current_database()', 'SCHEMA') end
Returns the current schema name.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 262 def current_schema select_value('SELECT current_schema', 'SCHEMA') end
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 107 def data_source_exists?(name) name = Utils.extract_schema_qualified_name(name.to_s) return false unless name.identifier select_value(" 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').to_i > 0 end
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 84 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 298 def drop_schema(schema_name, options = {}) execute "DROP SCHEMA#{' IF EXISTS' if options[:if_exists]} #{quote_schema_name(schema_name)} CASCADE" end
Returns the current database encoding format.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 267 def encoding select_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA') end
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 668 def fetch_type_metadata(column_name, sql_type, oid, fmod) cast_type = get_oid_type(oid, fmod, column_name, sql_type) simple_type = SqlTypeMetadata.new( sql_type: sql_type, type: cast_type.type, limit: cast_type.limit, precision: cast_type.precision, scale: cast_type.scale, ) PostgreSQLTypeMetadata.new(simple_type, oid: oid, fmod: fmod) end
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 581 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
Verifies existence of an index with a given name.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 155 def index_name_exists?(table_name, index_name, default) table = Utils.extract_schema_qualified_name(table_name.to_s) index = Utils.extract_schema_qualified_name(index_name.to_s) select_value(" 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 = i.relnamespace WHERE i.relkind = 'i' AND i.relname = '#{index.identifier}' AND t.relname = '#{table.identifier}' AND n.nspname = #{index.schema ? "'#{index.schema}'" : 'ANY (current_schemas(false))'} ", 'SCHEMA').to_i > 0 end
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 618 def index_name_length 63 end
Returns an array of indexes for the given table.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 173 def indexes(table_name, name = nil) table = Utils.extract_schema_qualified_name(table_name.to_s) result = query(" 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') 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 = i.relnamespace WHERE i.relkind = 'i' AND d.indisprimary = 'f' AND t.relname = '#{table.identifier}' AND n.nspname = #{table.schema ? "'#{table.schema}'" : 'ANY (current_schemas(false))'} ORDER BY i.relname ", 'SCHEMA') result.map do |row| index_name = row[0] unique = row[1] indkey = row[2].split(" ").map(&:to_i) 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 columns = expressions else columns = Hash[query(" SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}) ".strip_heredoc, "SCHEMA")].values_at(*indkey).compact # 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] } ] end IndexDefinition.new(table_name, index_name, unique, columns, [], orders, where, nil, using.to_sym, comment.presence) end.compact end
Renames an index of a table. Raises error if length of new index name is greater than allowed limit.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 575 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 454 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 #{seq.quoted} 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 150 def schema_exists?(name) select_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = '#{name}'", 'SCHEMA').to_i > 0 end
Returns an array of schema names.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 282 def schema_names select_values(" SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' AND nspname NOT IN ('information_schema') ORDER by nspname; ", 'SCHEMA') end
Returns the active schema search path.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 315 def schema_search_path @schema_search_path ||= select_value('SHOW search_path', 'SCHEMA') 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 307 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 338 def serial_sequence(table, column) select_value("SELECT pg_get_serial_sequence('#{table}', '#{column}')", 'SCHEMA') 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 lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 97 def table_exists?(name) ActiveSupport::Deprecation.warn(" #table_exists? currently checks both tables and views. This behavior is deprecated and will be changed with Rails 5.1 to only check tables. Use #data_source_exists? instead. ".squish) data_source_exists?(name) end
Returns the list of all tables in the schema search path.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 74 def tables(name = nil) if name ActiveSupport::Deprecation.warn(" Passing arguments to #tables is deprecated without replacement. ".squish) end select_values("SELECT tablename FROM pg_tables WHERE schemaname = ANY(current_schemas(false))", 'SCHEMA') end
Maps logical Rails types to PostgreSQL-specific data types.
# File lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 623 def type_to_sql(type, limit = nil, precision = nil, scale = nil, array = nil) sql = 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' case limit when 1, 2; 'smallint' when nil, 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 else super(type, limit, precision, scale) end sql << '[]' if array && type != :primary_key sql end