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:



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

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

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

:nodoc:



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

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.



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

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_type << "[]" if options[:array]
  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) ⇒ Object

Changes the default value of a table column.



272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 272

def change_column_default(table_name, column_name, default)
  clear_cache!
  column = column_for(table_name, column_name)
  return unless column

  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 



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

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 



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

def collation
end

#columns(table_name) ⇒ Object

Returns the list of all column definitions for a table.



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

def columns(table_name)
  # Limit, precision, and scale are all handled by the superclass.
  column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod|
    oid = get_oid_type(oid.to_i, fmod.to_i, column_name, type)
    default_value = extract_value_from_default(oid, default)
    default_function = extract_default_function(default_value, default)
    new_column(column_name, default_value, oid, type, notnull == 'f', 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.



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

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 PostgreSQL database. Options include :owner, :template, :encoding (defaults to utf8), :collation, :ctype, :tablespace, and :connection_limit (note that MySQL uses :charset while PostgreSQL uses :encoding).

Example:

create_database config[:database], config
create_database 'foo_development', encoding: 'unicode'



50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 50

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.



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

def create_schema schema_name
  execute "CREATE SCHEMA #{schema_name}"
end

#ctype ⇒ Object 



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

def ctype
end

#current_database ⇒ Object

Returns the current database name.



137
138
139
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 137

def current_database
  query('select current_database()', 'SCHEMA')[0][0]
end

#current_schema ⇒ Object

Returns the current schema name.



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

def current_schema
  query('SELECT current_schema', 'SCHEMA')[0][0]
end

#default_sequence_name(table_name, pk = nil) ⇒ Object

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



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

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 PostgreSQL database.

Example:

drop_database 'matt_development'



69
70
71
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 69

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

#drop_schema(schema_name) ⇒ Object

Drops the schema for the given schema name.



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

def drop_schema schema_name
  execute "DROP SCHEMA #{schema_name} CASCADE"
end

#drop_table(table_name, options = {}) ⇒ Object 



99
100
101
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 99

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.



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

def encoding
  query(<<-end_sql, 'SCHEMA')[0][0]
    SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database
    WHERE pg_database.datname LIKE '#{current_database}'
  end_sql
end

#extract_foreign_key_action(specifier) ⇒ Object

:nodoc:



341
342
343
344
345
346
347
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 341

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

#foreign_keys(table_name) ⇒ Object 



312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 312

def foreign_keys(table_name)
  fk_info = select_all <<-SQL.strip_heredoc
    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) 


112
113
114
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 112

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

#index_name_length ⇒ Object 



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

def index_name_length
  63
end

#indexes(table_name, name = nil) ⇒ Object

Returns an array of indexes for the given table.



117
118
119
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 117

def indexes(table_name, name = nil)
  []
end

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

:nodoc:



132
133
134
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 132

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

#pk_and_sequence_for(table) ⇒ Object

:nodoc:



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

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

#primary_key(table) ⇒ Object

Returns just a table’s primary key



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

def primary_key(table)
  pks = exec_query(<<-end_sql, 'SCHEMA').rows
    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
  return nil unless pks.count == 1
  pks[0][0]
end

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

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



37
38
39
40
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 37

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

#remove_index!(table_name, index_name) ⇒ Object

:nodoc:



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

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.



297
298
299
300
301
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 297

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)}"
  rename_column_indexes(table_name, column_name, new_column_name)
end

#rename_index(table_name, old_name, new_name) ⇒ Object 



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

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



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

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:



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

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

#schema_exists?(name) ⇒ Boolean

Returns true if schema exists.

Returns:

  • (Boolean) 


104
105
106
107
108
109
110
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 104

def schema_exists?(name)
  exec_query(<<-SQL, 'SCHEMA').rows.first[0].to_i > 0
    SELECT COUNT(*)
    FROM pg_namespace
    WHERE nspname = '#{name}'
  SQL
end

#schema_names ⇒ Object

Returns an array of schema names.



161
162
163
164
165
166
167
168
169
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 161

def schema_names
  query(<<-SQL, 'SCHEMA').flatten
    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.



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

def schema_search_path
  @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
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.



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

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 



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

def serial_sequence(table, column)
  result = exec_query(<<-eosql, 'SCHEMA')
    SELECT pg_get_serial_sequence('#{table}', '#{column}')
  eosql
  result.rows.first.first
end

#set_pk_sequence!(table, value) ⇒ Object

:nodoc:



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

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
94
95
96
97
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 85

def table_exists?(name)
  name = Utils.extract_schema_qualified_name(name.to_s)
  return false unless name.identifier

  exec_query(<<-SQL, 'SCHEMA').rows.first[0].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

#tables(name = nil) ⇒ Object

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



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

def tables(name = nil)
  query(<<-SQL, 'SCHEMA').map { |row| row[0] }
    SELECT tablename
    FROM pg_tables
    WHERE schemaname = ANY (current_schemas(false))
  SQL
end

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

Maps logical Rails types to PostgreSQL-specific data types.



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

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 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
  when 'datetime'
    return super unless precision

    case precision
      when 0..6; "timestamp(#{precision})"
      else raise(ActiveRecordError, "No timestamp type has precision of #{precision}. The allowed range of precision is from 0 to 6")
    end
  else
    super
  end
end