Module: ActiveRecord::ConnectionAdapters::Redshift::SchemaStatements

Included in:
ActiveRecord::ConnectionAdapters::RedshiftAdapter
Defined in:
lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb,
lib/active_record/connection_adapters/redshift_7_1/schema_statements.rb,
lib/active_record/connection_adapters/redshift_7_2/schema_statements.rb

Constant Summary collapse

FOREIGN_KEY_ACTIONS = 
{
  'c' => :cascade,
  'n' => :nullify,
  'r' => :restrict
}.freeze

Instance Method Summary collapse

Instance Method Details

#add_column(table_name, column_name, type, **options) ⇒ Object

:nodoc:



268
269
270
271
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 268

def add_column(table_name, column_name, type, **options) # :nodoc:
  clear_cache!
  super
end

#add_index(table_name, column_name, **options) ⇒ Object 



326
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 326

def add_index(table_name, column_name, **options); end

#change_column(table_name, column_name, type, **options) ⇒ Object

Changes the column of a table.



274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 274

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, limit: options[:limit], precision: options[:precision], scale: 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],
                                                                           limit: options[:limit], precision: options[:precision], scale: 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_or_changes) ⇒ Object

Changes the default value of a table column.



291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 291

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

#change_column_null(table_name, column_name, null, default = nil) ⇒ Object 



307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 307

def change_column_null(table_name, column_name, null, default = nil)
  clear_cache!
  unless null || default.nil?
    column = column_for(table_name, column_name)
    if column
      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")
    end
  end
  execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
end

#collation ⇒ Object 



180
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 180

def collation; end

#columns(table_name) ⇒ Object

Returns the list of all column definitions for a table.



150
151
152
153
154
155
156
157
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 150

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, table_name, default_function)
  end
end

#columns_for_distinct(columns, orders) ⇒ Object

PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column.



395
396
397
398
399
400
401
402
403
404
405
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 395

def columns_for_distinct(columns, orders) # :nodoc:
  order_columns = orders.compact_blank.map { |s|
    # Convert Arel node to string
    s = visitor.compile(s) unless s.is_a?(String)
    # Remove any ASC/DESC modifiers
    s.gsub(/\s+(?:ASC|DESC)\b/i, "")
     .gsub(/\s+NULLS\s+(?:FIRST|LAST)\b/i, "")
  }.compact_blank.map.with_index { |column, i| "#{column} AS alias_#{i}" }

  (order_columns << super).join(", ")
end

#create_database(name, options = {}) ⇒ Object

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'



40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 40

def create_database(name, options = {})
  options = { encoding: 'utf8' }.merge!(options.symbolize_keys)

  option_string = options.inject('') do |memo, (key, value)|
    next memo unless key == :owner

    memo + " OWNER = \"#{value}\""
  end

  execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
end

#create_schema(schema_name) ⇒ Object

Creates a schema for the given schema name.



196
197
198
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 196

def create_schema(schema_name)
  execute "CREATE SCHEMA #{quote_schema_name(schema_name)}"
end

#ctype ⇒ Object 



182
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 182

def ctype; end

#current_database ⇒ Object

Returns the current database name.



164
165
166
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 164

def current_database
  select_value('select current_database()', 'SCHEMA')
end

#current_schema ⇒ Object

Returns the current schema name.



169
170
171
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 169

def current_schema
  select_value('SELECT current_schema', 'SCHEMA')
end

#data_source_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 93

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

#data_sources ⇒ Object

:nodoc



66
67
68
69
70
71
72
73
74
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 66

def data_sources
  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

#default_sequence_name(table_name, pk = nil) ⇒ Object

Returns the sequence name for a table’s primary key or some other specified key.



223
224
225
226
227
228
229
230
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 223

def default_sequence_name(table_name, pk = nil) # :nodoc:
  result = serial_sequence(table_name, pk || 'id')
  return nil unless result

  Utils.extract_schema_qualified_name(result).to_s
rescue ActiveRecord::StatementInvalid
  Redshift::Name.new(nil, "#{table_name}_#{pk || 'id'}_seq").to_s
end

#drop_database(name) ⇒ Object

Drops a Redshift database.

Example:

drop_database 'matt_development'



56
57
58
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 56

def drop_database(name) # :nodoc:
  execute "DROP DATABASE #{quote_table_name(name)}"
end

#drop_schema(schema_name, **options) ⇒ Object

Drops the schema for the given schema name.



201
202
203
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 201

def drop_schema(schema_name, **options)
  execute "DROP SCHEMA#{' IF EXISTS' if options[:if_exists]} #{quote_schema_name(schema_name)} CASCADE"
end

#drop_table(table_name, **options) ⇒ Object 



131
132
133
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 131

def drop_table(table_name, **options)
  execute "DROP TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_table_name(table_name)}#{' CASCADE' if options[:force] == :cascade}"
end

#encoding ⇒ Object

Returns the current database encoding format.



174
175
176
177
178
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 174

def encoding
  select_value(
    "SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA'
  )
end

#extract_foreign_key_action(specifier) ⇒ Object 



367
368
369
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 367

def extract_foreign_key_action(specifier)
  FOREIGN_KEY_ACTIONS[specifier]
end

#fetch_type_metadata(column_name, sql_type, oid, fmod) ⇒ Object 



407
408
409
410
411
412
413
414
415
416
417
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 407

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

#foreign_keys(table_name) ⇒ Object 



332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 332

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

#index_name_exists?(_table_name, _index_name) ⇒ Boolean

Returns:

  • (Boolean) 


140
141
142
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 140

def index_name_exists?(_table_name, _index_name, _default)
  false
end

#index_name_length ⇒ Object 



371
372
373
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 371

def index_name_length
  63
end

#indexes(_table_name) ⇒ Object

Returns an array of indexes for the given table.



145
146
147
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 145

def indexes(_table_name, _name = nil)
  []
end

#new_column(name, default, sql_type_metadata = nil, null = true, _table_name = nil, default_function = nil) ⇒ Object

:nodoc:



159
160
161
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 159

def new_column(name, default, sql_type_metadata = nil, null = true, _table_name = nil, default_function = nil) # :nodoc:
  RedshiftColumn.new(name, default, sql_type_metadata, null, default_function)
end

#pk_and_sequence_for(_table) ⇒ Object

:nodoc:



240
241
242
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 240

def pk_and_sequence_for(_table) # :nodoc:
  [nil, nil]
end

#primary_keys(table) ⇒ Object

Returns just a table’s primary key



245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 245

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

#recreate_database(name, options = {}) ⇒ Object

Drops the database specified on the name attribute and creates it again using the provided options.



27
28
29
30
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 27

def recreate_database(name, options = {}) # :nodoc:
  drop_database(name)
  create_database(name, options)
end

#remove_index!(table_name, index_name) ⇒ Object 



328
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 328

def remove_index!(table_name, index_name); end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Renames a column in a table.



321
322
323
324
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 321

def rename_column(table_name, column_name, new_column_name) # :nodoc:
  clear_cache!
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
end

#rename_index(table_name, old_name, new_name) ⇒ Object 



330
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 330

def rename_index(table_name, old_name, new_name); end

#rename_table(table_name, new_name, **options) ⇒ Object

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')



263
264
265
266
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 263

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

#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object 



238
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 238

def reset_pk_sequence!(table, pk = nil, sequence = nil); end

#schema_exists?(name) ⇒ Boolean

Returns true if schema exists.

Returns:

  • (Boolean) 


136
137
138
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 136

def schema_exists?(name)
  select_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = '#{name}'", 'SCHEMA').to_i > 0
end

#schema_names ⇒ Object

Returns an array of schema names.



185
186
187
188
189
190
191
192
193
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 185

def schema_names
  select_values(<<-SQL, 'SCHEMA')
    SELECT nspname
      FROM pg_namespace
     WHERE nspname !~ '^pg_.*'
       AND nspname NOT IN ('information_schema')
     ORDER by nspname;
  SQL
end

#schema_search_path ⇒ Object

Returns the active schema search path.



218
219
220
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 218

def schema_search_path
  @schema_search_path ||= select_value('SHOW search_path', 'SCHEMA')
end

#schema_search_path=(schema_csv) ⇒ Object

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.



210
211
212
213
214
215
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 210

def schema_search_path=(schema_csv)
  return unless schema_csv

  execute("SET search_path TO #{schema_csv}", 'SCHEMA')
  @schema_search_path = schema_csv
end

#serial_sequence(table, column) ⇒ Object 



232
233
234
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 232

def serial_sequence(table, column)
  select_value("SELECT pg_get_serial_sequence('#{table}', '#{column}')", 'SCHEMA')
end

#set_pk_sequence!(table, value) ⇒ Object 



236
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 236

def set_pk_sequence!(table, value); end

#table_exists?(name) ⇒ Boolean

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)

Returns:

  • (Boolean) 


79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 79

def table_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 = 'r' -- (r)elation/table
      AND c.relname = '#{name.identifier}'
      AND n.nspname = #{name.schema ? "'#{name.schema}'" : 'ANY (current_schemas(false))'}
  SQL
end

#tables ⇒ Object

Returns an array of table names defined in the database.



61
62
63
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 61

def tables
  select_values('SELECT tablename FROM pg_tables WHERE schemaname = ANY(current_schemas(false))', 'SCHEMA')
end

#type_to_sql(type, limit: nil, precision: nil, scale: nil) ⇒ Object

Maps logical Rails types to PostgreSQL-specific data types.



376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 376

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 then 'smallint'
    when nil, 3, 4 then 'integer'
    when 5..8 then 'bigint'
    else raise(ActiveRecordError,
               "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
    end
  else
    super
  end
end

#view_exists?(view_name) ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 117

def view_exists?(view_name) # :nodoc:
  name = Utils.extract_schema_qualified_name(view_name.to_s)
  return false unless name.identifier

  select_values(<<-SQL, 'SCHEMA').any?
    SELECT c.relname
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('v','m') -- (v)iew, (m)aterialized view
    AND c.relname = '#{name.identifier}'
    AND n.nspname = #{name.schema ? "'#{name.schema}'" : 'ANY (current_schemas(false))'}
  SQL
end

#views ⇒ Object

:nodoc:



107
108
109
110
111
112
113
114
115
 
# File 'lib/active_record/connection_adapters/redshift_7_0/schema_statements.rb', line 107

def views # :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 ('v','m') -- (v)iew, (m)aterialized view
    AND n.nspname = ANY (current_schemas(false))
  SQL
end