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

Included in:
ActiveRecord::ConnectionAdapters::RedshiftAdapter
Defined in:
lib/active_record/connection_adapters/redshift/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:



280
281
282
283
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 280

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

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



338
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 338

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

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

Changes the column of a table.



286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 286

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.



303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 303

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 



319
320
321
322
323
324
325
326
327
328
329
330
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 319

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 



192
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 192

def collation; end

#columns(table_name) ⇒ Object

Returns the list of all column definitions for a table.



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# 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|
    oid          = oid.to_i
    fmod         = fmod.to_i
    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,
      get_oid_type(oid, fmod, column_name, type),
      default_value,
      type_metadata,
      notnull == 'f',
      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.



407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 407

def columns_for_distinct(columns, orders) # :nodoc:
  order_columns = orders.reject(&:blank?).map  do |s|
    # Convert Arel node to string
    s = s.to_sql 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, '')
  end

  order_columns = order_columns
    .reject(&:blank?)
    .map.with_index { |column, i| "#{column} AS alias_#{i}" }

  [super, *order_columns].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/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.



208
209
210
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 208

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

#ctype ⇒ Object 



194
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 194

def ctype; end

#current_database ⇒ Object

Returns the current database name.



176
177
178
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 176

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

#current_schema ⇒ Object

Returns the current schema name.



181
182
183
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 181

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

#data_source_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


95
96
97
98
99
100
101
102
103
104
105
106
107
 
# 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

#data_sources ⇒ Object

:nodoc



72
73
74
75
76
77
78
79
80
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 72

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.



235
236
237
238
239
240
241
242
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 235

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/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.



213
214
215
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 213

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 



133
134
135
 
# 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

#encoding ⇒ Object

Returns the current database encoding format.



186
187
188
189
190
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 186

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 



379
380
381
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 379

def extract_foreign_key_action(specifier)
  FOREIGN_KEY_ACTIONS[specifier]
end

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



423
424
425
426
427
428
429
430
431
432
433
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 423

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 



344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 344

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, _default) ⇒ Boolean

Returns:

  • (Boolean) 


142
143
144
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 142

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

#index_name_length ⇒ Object 



383
384
385
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 383

def index_name_length
  63
end

#indexes(_table_name, _name = nil) ⇒ Object

Returns an array of indexes for the given table.



147
148
149
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 147

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

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

:nodoc:



171
172
173
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 171

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

#pk_and_sequence_for(_table) ⇒ Object

:nodoc:



252
253
254
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 252

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

#primary_keys(table) ⇒ Object

Returns just a table’s primary key



257
258
259
260
261
262
263
264
265
266
267
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 257

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/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 



340
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 340

def remove_index!(table_name, index_name); end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Renames a column in a table.



333
334
335
336
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 333

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 



342
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 342

def rename_index(table_name, old_name, new_name); end

#rename_table(table_name, new_name) ⇒ 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')



275
276
277
278
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 275

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 



250
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 250

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

#schema_exists?(name) ⇒ Boolean

Returns true if schema exists.

Returns:

  • (Boolean) 


138
139
140
 
# 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

#schema_names ⇒ Object

Returns an array of schema names.



197
198
199
200
201
202
203
204
205
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 197

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

#schema_search_path ⇒ Object

Returns the active schema search path.



230
231
232
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 230

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.



222
223
224
225
226
227
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 222

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 



244
245
246
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 244

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

#set_pk_sequence!(table, value) ⇒ Object 



248
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 248

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) 


85
86
87
88
89
90
91
92
93
 
# 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

#tables(name = nil) ⇒ Object

Returns the list of all tables in the schema search path or a specified schema.



61
62
63
64
65
66
67
68
69
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 61

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

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

Maps logical Rails types to PostgreSQL-specific data types.



388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 388

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) 


119
120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 119

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:



109
110
111
112
113
114
115
116
117
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 109

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