# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 350 def primary_key(table) # Passing the env variable REDSHIFT_SCHEMA will allow the correct generation of id # fields that are not of integer/uuid/serial type return if ENV['REDSHIFT_SCHEMA'] pks = exec_query(<<-end_sql, 'SCHEMA').rows SELECT DISTINCT attr.attname FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND dep.refobjid = '#{quote_table_name(table)}'::regclass end_sql return nil unless pks.count == 1 pks[0][0] end
module ActiveRecord::ConnectionAdapters::Redshift::SchemaStatements
Public Instance Methods
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 390 def add_column(table_name, column_name, type, options = {}) clear_cache! super end
Changes the column of a table.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 396 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
Changes the default value of a table column.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 413 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
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 428 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 lib/active_record/connection_adapters/redshift/schema_statements.rb, line 230 def client_min_messages query('SHOW client_min_messages', 'SCHEMA')[0][0] end
Set the client message level.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 235 def client_min_messages=(level) # can't set client_min_messages in redshift # execute("SET client_min_messages TO '#{level}'", 'SCHEMA') end
Returns the current database collation.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 178 def collation query(<<-end_sql, 'SCHEMA')[0][0] SELECT pg_database.datcollate FROM pg_database WHERE pg_database.datname LIKE '#{current_database}' end_sql end
Returns the list of all column definitions for a table.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 145 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
, :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/redshift/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 :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/redshift/schema_statements.rb, line 203 def create_schema schema_name execute "CREATE SCHEMA #{schema_name}" end
Returns the current database ctype.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 185 def ctype query(<<-end_sql, 'SCHEMA')[0][0] SELECT pg_database.datctype FROM pg_database WHERE pg_database.datname LIKE '#{current_database}' end_sql end
Returns the current database name.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 160 def current_database query('select current_database()', 'SCHEMA')[0][0] end
Returns the current schema name.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 165 def current_schema query('SELECT current_schema', 'SCHEMA')[0][0] end
Drops the schema for the given schema name.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 208 def drop_schema schema_name execute "DROP SCHEMA #{schema_name} CASCADE" end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 113 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 lib/active_record/connection_adapters/redshift/schema_statements.rb, line 170 def encoding query(<<-end_sql, 'SCHEMA')[0][0] SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database WHERE pg_database.datname LIKE '#{current_database}' end_sql end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 459 def foreign_keys(table_name) fk_info = select_all <<-SQL.strip_heredoc SELECT t2.oid::regclass 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 SQL 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
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 126 def index_name_exists?(table_name, index_name, default) exec_query(<<-SQL, 'SCHEMA').rows.first[0].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 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)) ) SQL end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 496 def index_name_length 63 end
Returns an array of indexes for the given table.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 140 def indexes(table_name, name = nil) [] end
Returns just a table's primary key
Renames a column in a table.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 438 def rename_column(table_name, column_name, new_column_name) clear_cache! execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" rename_column_indexes(table_name, column_name, new_column_name) end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 452 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)}" # ignore 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/redshift/schema_statements.rb, line 373 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 lib/active_record/connection_adapters/redshift/schema_statements.rb, line 118 def schema_exists?(name) exec_query(<<-SQL, 'SCHEMA').rows.first[0].to_i > 0 SELECT COUNT(*) FROM pg_namespace WHERE nspname = '#{name}' SQL end
Returns an array of schema names.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 192 def schema_names query(<<-SQL, 'SCHEMA').flatten SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' AND nspname NOT IN ('information_schema') ORDER by nspname; SQL end
Returns the active schema search path.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 225 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 lib/active_record/connection_adapters/redshift/schema_statements.rb, line 217 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/redshift/schema_statements.rb, line 249 def serial_sequence(table, column) result = exec_query(<<-eosql, 'SCHEMA') SELECT pg_get_serial_sequence('#{table}', '#{column}') eosql 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 lib/active_record/connection_adapters/redshift/schema_statements.rb, line 99 def table_exists?(name) name = Utils.extract_schema_qualified_name(name.to_s) return false unless name.identifier exec_query(<<-SQL, 'SCHEMA').rows.first[0].to_i > 0 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))'} SQL end
Returns the list of all tables in the schema search path or a specified schema.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 88 def tables(name = nil) query(<<-SQL, 'SCHEMA').map { |row| "#{row[0]}.#{row[1]}" } SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false)) SQL end
Maps logical Rails types to PostgreSQL-specific data types.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 501 def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_s when 'boolean' super(type) 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 precision 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