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

Included in:
ActiveRecord::ConnectionAdapters::RedshiftAdapter
Defined in:
lib/active_record/connection_adapters/redshift/schema_statements.rb

Instance Method Summary collapse

Instance Method Details

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

:nodoc:



292
293
294
295
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 292

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

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

:nodoc:



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

def add_index(table_name, column_name, **options) #:nodoc:
end

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

Changes the column of a table.



298
299
300
301
302
303
304
305
306
307
308
309
310
311
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 298

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.



314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 314

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 



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

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

#collation ⇒ Object 



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

def collation
end

#columns(table_name) ⇒ Object

Returns the list of all column definitions for a table.



173
174
175
176
177
178
179
180
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 173

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

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



414
415
416
417
418
419
420
421
422
423
424
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 414

def columns_for_distinct(columns, orders) #:nodoc:
  order_columns = orders.reject(&:blank?).map{ |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, '')
    }.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'



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

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

#create_schema(schema_name) ⇒ Object

Creates a schema for the given schema name.



219
220
221
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 219

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

#ctype ⇒ Object 



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

def ctype
end

#current_database ⇒ Object

Returns the current database name.



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

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

#current_schema ⇒ Object

Returns the current schema name.



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

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

#data_source_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


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

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



93
94
95
96
97
98
99
100
101
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 93

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

#default_sequence_name(table_name, pk = nil) ⇒ Object

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



246
247
248
249
250
251
252
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 246

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'



78
79
80
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 78

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.



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

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 



154
155
156
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 154

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.



197
198
199
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 197

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

:nodoc:



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

def extract_foreign_key_action(specifier) # :nodoc:
  case specifier
  when 'c'; :cascade
  when 'n'; :nullify
  when 'r'; :restrict
  end
end

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



426
427
428
429
430
431
432
433
434
435
436
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 426

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 



354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 354

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) 


163
164
165
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 163

def index_name_exists?(table_name, index_name, default)
  false
end

#index_name_length ⇒ Object 



391
392
393
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 391

def index_name_length
  63
end

#indexes(table_name, name = nil) ⇒ Object

Returns an array of indexes for the given table.



168
169
170
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 168

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:



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

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:



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

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

#primary_keys(table) ⇒ Object

Returns just a table’s primary key



269
270
271
272
273
274
275
276
277
278
279
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 269

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.



46
47
48
49
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 46

def recreate_database(name, **options) #:nodoc:
  drop_database(name)
  create_database(name, options)
end

#remove_index!(table_name, index_name) ⇒ Object

:nodoc:



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

def remove_index!(table_name, index_name) #:nodoc:
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Renames a column in a table.



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

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 



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

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



287
288
289
290
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 287

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

:nodoc:



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

def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:
end

#schema_exists?(name) ⇒ Boolean

Returns true if schema exists.

Returns:

  • (Boolean) 


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

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.



208
209
210
211
212
213
214
215
216
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 208

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.



241
242
243
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 241

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.



233
234
235
236
237
238
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 233

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

#serial_sequence(table, column) ⇒ Object 



254
255
256
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 254

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

#set_pk_sequence!(table, value) ⇒ Object

:nodoc:



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

def set_pk_sequence!(table, value) #:nodoc:
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) 


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

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.



83
84
85
86
87
88
89
90
91
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 83

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.



396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 396

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

#view_exists?(view_name) ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 140

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:



130
131
132
133
134
135
136
137
138
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 130

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