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:



271
272
273
274
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 271

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

#add_index(table_name, column_name, options = {}) ⇒ Object

:nodoc:



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

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.



277
278
279
280
281
282
283
284
285
286
287
288
289
290
 
# 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

#change_column_default(table_name, column_name, default_or_changes) ⇒ Object

Changes the default value of a table column.



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# 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

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



309
310
311
312
313
314
315
316
 
# 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

#collation ⇒ Object 



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

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

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



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

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'



38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
# 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

#create_schema(schema_name) ⇒ Object

Creates a schema for the given schema name.



198
199
200
 
# 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

#ctype ⇒ Object 



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

def ctype
end

#current_database ⇒ Object

Returns the current database name.



166
167
168
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 166

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

#current_schema ⇒ Object

Returns the current schema name.



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

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



225
226
227
228
229
230
231
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 225

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'



57
58
59
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 57

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.



203
204
205
 
# 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

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



176
177
178
 
# 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

#extract_foreign_key_action(specifier) ⇒ Object

:nodoc:



362
363
364
365
366
367
368
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 362

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 



405
406
407
408
409
410
411
412
413
414
415
 
# 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

#foreign_keys(table_name) ⇒ Object 



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

#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 



370
371
372
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 370

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, default, sql_type_metadata = nil, null = true, table_name = nil, default_function = nil) ⇒ Object

:nodoc:



161
162
163
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 161

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:



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

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

#primary_keys(table) ⇒ Object

Returns just a table’s primary key



248
249
250
251
252
253
254
255
256
257
258
 
# 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

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

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



25
26
27
28
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 25

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

#remove_index!(table_name, index_name) ⇒ Object

:nodoc:



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

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.



319
320
321
322
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 319

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
331
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 330

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



266
267
268
269
 
# 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

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

:nodoc:



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

def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:
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.



187
188
189
190
191
192
193
194
195
 
# 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

#schema_search_path ⇒ Object

Returns the active schema search path.



220
221
222
 
# 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

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



212
213
214
215
216
217
 
# 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

#serial_sequence(table, column) ⇒ Object 



233
234
235
 
# 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

#set_pk_sequence!(table, value) ⇒ Object

:nodoc:



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

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) 


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.



62
63
64
65
66
67
68
69
70
 
# 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

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

Maps logical Rails types to PostgreSQL-specific data types.



375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
 
# 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

#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