# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 248 def primary_keys(table) pks = query(<<-end_sql, 'SCHEMA') 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 = any(cons.conkey) WHERE cons.contype = 'p' AND dep.refobjid = '#{quote_table_name(table)}'::regclass end_sql pks.present? ? pks[0] : pks end
module ActiveRecord::ConnectionAdapters::Redshift::SchemaStatements
Public Instance Methods
Changes the column of a table.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 277 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 = "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 293 def change_column_default(table_name, column_name, default_or_changes) 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_value(default, column)}" end end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 309 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
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 180 def collation end
Returns the list of all column definitions for a table.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 152 def columns(table_name) column_definitions(table_name.to_s).map do |column_name, type, default, notnull, oid, fmod| default_value = extract_value_from_default(default) type_metadata = fetch_type_metadata(column_name, type, oid, fmod) default_function = extract_default_function(default_value, default) new_column(column_name, default_value, type_metadata, notnull == 'f', table_name, default_function) end end
Create a new Redshift
database. Options include :owner
, :template
, :encoding
(defaults to utf8), :collation
, :ctype
, :tablespace
, and :connection_limit
(note that MySQL uses :charset
while Redshift
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 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}\"" 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 198 def create_schema schema_name execute "CREATE SCHEMA #{quote_schema_name(schema_name)}" end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 183 def ctype end
Returns the current database name.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 166 def current_database select_value('select current_database()', 'SCHEMA') end
Returns the current schema name.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 171 def current_schema select_value('SELECT current_schema', 'SCHEMA') end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 95 def data_source_exists?(name) name = Utils.extract_schema_qualified_name(name.to_s) return false unless name.identifier select_value(<<-SQL, 'SCHEMA').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
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 72 def data_sources # :nodoc select_values(<<-SQL, 'SCHEMA') 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)) SQL end
Drops the schema for the given schema name.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 203 def drop_schema(schema_name, options = {}) execute "DROP SCHEMA#{' IF EXISTS' if options[:if_exists]} #{quote_schema_name(schema_name)} CASCADE" end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 133 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 176 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/redshift/schema_statements.rb, line 405 def fetch_type_metadata(column_name, sql_type, oid, fmod) cast_type = get_oid_type(oid.to_i, fmod.to_i, 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, ) TypeMetadata.new(simple_type, oid: oid, fmod: fmod) end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 333 def foreign_keys(table_name) fk_info = select_all(<<-SQL.strip_heredoc, 'SCHEMA') SELECT t2.relname 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 142 def index_name_exists?(table_name, index_name, default) false end
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 370 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 147 def indexes(table_name, name = nil) [] end
Returns just a table's primary key
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 330 def rename_index(table_name, old_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/redshift/schema_statements.rb, line 266 def rename_table(table_name, new_name) clear_cache! execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}" end
Returns true if schema exists.
# File lib/active_record/connection_adapters/redshift/schema_statements.rb, line 138 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/redshift/schema_statements.rb, line 187 def schema_names select_value(<<-SQL, 'SCHEMA') 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 220 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/redshift/schema_statements.rb, line 212 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 233 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/redshift/schema_statements.rb, line 85 def table_exists?(name) ActiveSupport::Deprecation.warn(<<-MSG.squish) #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. MSG data_source_exists?(name) 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 62 def tables(name = nil) if name ActiveSupport::Deprecation.warn(<<-MSG.squish) Passing arguments to #tables is deprecated without replacement. MSG 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/redshift/schema_statements.rb, line 375 def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_s when 'integer' return 'integer' unless limit 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 precision 0 instead.") end else super end end