Module: ActiveRecord::ConnectionAdapters::RedshiftAdapter::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

Adds a new column to the named table. See TableDefinition#column for details of the options you can use.



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

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

#add_index(*args) ⇒ Object 



368
369
370
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 368

def add_index(*args)
  # ignore
end

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

Changes the column of a table.



337
338
339
340
341
342
343
344
345
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 337

def change_column(table_name, column_name, type, options = {})
  clear_cache!
  quoted_table_name = quote_table_name(table_name)

  execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"

  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.



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

def change_column_default(table_name, column_name, default)
  clear_cache!
  execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
end

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



353
354
355
356
357
358
359
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 353

def change_column_null(table_name, column_name, null, default = nil)
  clear_cache!
  unless null || default.nil?
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end
  execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
end

#collation ⇒ Object

Returns the current database collation.



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

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

#columns(table_name) ⇒ Object

Returns the list of all column definitions for a table.



135
136
137
138
139
140
141
142
143
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 135

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 = OID::TYPE_MAP.fetch(oid.to_i, fmod.to_i) {
      OID::Identity.new
    }
    RedshiftColumn.new(column_name, default, oid, type, notnull == 'f')
  end
end

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

Create a new PostgreSQL database. Options include :owner, :template, :encoding, :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'



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 56

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

  option_string = options.sum do |key, value|
    case key
    when :owner
      " OWNER = \"#{value}\""
    when :template
      " TEMPLATE = \"#{value}\""
    when :encoding
      " ENCODING = '#{value}'"
    when :collation
      " LC_COLLATE = '#{value}'"
    when :ctype
      " LC_CTYPE = '#{value}'"
    when :tablespace
      " TABLESPACE = \"#{value}\""
    when :connection_limit
      " CONNECTION LIMIT = #{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.



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

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

#ctype ⇒ Object

Returns the current database ctype.



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

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

#current_database ⇒ Object

Returns the current database name.



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

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

#current_schema ⇒ Object

Returns the current schema name.



151
152
153
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 151

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.



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

def default_sequence_name(table_name, pk = nil) #:nodoc:
  result = serial_sequence(table_name, pk || 'id')
  return nil unless result
  result.split('.').last
rescue ActiveRecord::StatementInvalid
  "#{table_name}_#{pk || 'id'}_seq"
end

#distinct(columns, orders) ⇒ Object

Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

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

distinct("posts.id", ["posts.created_at desc"])
# => "DISTINCT posts.id, posts.created_at AS alias_0"



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

def distinct(columns, orders) #:nodoc:
  order_columns = orders.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)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '')
    }.reject(&:blank?).map.with_index { |column, i| "#{column} AS alias_#{i}" }

  [super].concat(order_columns).join(', ')
end

#drop_database(name) ⇒ Object

Drops a PostgreSQL database.

Example:

drop_database 'matt_development'



87
88
89
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 87

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

#drop_schema(schema_name) ⇒ Object

Drops the schema for the given schema name.



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

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

#encoding ⇒ Object

Returns the current database encoding format.



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

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

#index_name_length ⇒ Object 



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

def index_name_length
  63
end

#indexes(table_name, name = nil) ⇒ Object

Returns an array of indexes for the given table.



130
131
132
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 130

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

#pk_and_sequence_for(table) ⇒ Object

Returns a table’s primary key and belonging sequence.



254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 254

def pk_and_sequence_for(table) #:nodoc:
  # First try looking for a sequence with a dependency on the
  # given table's primary key.
  result = query(<<-end_sql, 'SCHEMA')[0]
    SELECT attr.attname, seq.relname
    FROM pg_class      seq,
         pg_attribute  attr,
         pg_depend     dep,
         pg_constraint cons
    WHERE seq.oid           = dep.objid
      AND seq.relkind       = 'S'
      AND attr.attrelid     = dep.refobjid
      AND attr.attnum       = dep.refobjsubid
      AND attr.attrelid     = cons.conrelid
      AND attr.attnum       = cons.conkey[1]
      AND cons.contype      = 'p'
      AND dep.refobjid      = '#{quote_table_name(table)}'::regclass
  end_sql

  if result.nil? or result.empty?
    result = query(<<-end_sql, 'SCHEMA')[0]
      SELECT attr.attname,
        CASE
          WHEN split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2) ~ '.' THEN
            substr(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2),
                   strpos(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2), '.')+1)
          ELSE split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2)
        END
      FROM pg_class       t
      JOIN pg_attribute   attr ON (t.oid = attrelid)
      JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
      JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
      WHERE t.oid = '#{quote_table_name(table)}'::regclass
        AND cons.contype = 'p'
        AND pg_get_expr(def.adbin, def.adrelid) ~* 'nextval'
    end_sql
  end

  [result.first, result.last]
rescue
  nil
end

#primary_key(table) ⇒ Object

Returns just a table’s primary key



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

def primary_key(table)
  row = exec_query(<<-end_sql, 'SCHEMA').rows.first
    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 = cons.conkey[1]
    WHERE cons.contype = 'p'
      AND dep.refobjid = '#{quote_table_name(table)}'::regclass
  end_sql

  row && row.first
end

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

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



43
44
45
46
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 43

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

#remove_index!(table_name, index_name) ⇒ Object

:nodoc:



372
373
374
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 372

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

#rename_column(table_name, column_name, new_column_name) ⇒ Object

Renames a column in a table.



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

def rename_column(table_name, column_name, new_column_name)
  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 



376
377
378
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 376

def rename_index(table_name, old_name, new_name)
  # ignore
end

#rename_table(table_name, new_name) ⇒ Object

Renames a table. Also renames a table’s primary key sequence if the sequence name matches the Active Record default.

Example:

rename_table('octopuses', 'octopi')



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

def rename_table(table_name, new_name)
  clear_cache!
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
  pk, seq = pk_and_sequence_for(new_name)
  if seq == "#{table_name}_#{pk}_seq"
    new_seq = "#{new_name}_#{pk}_seq"
    execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}"
  end

  rename_table_indexes(table_name, new_name)
end

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

Resets the sequence of a table’s primary key to the maximum value.



232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 232

def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:
  unless pk and sequence
    default_pk, default_sequence = pk_and_sequence_for(table)

    pk ||= default_pk
    sequence ||= default_sequence
  end

  if @logger && pk && !sequence
    @logger.warn "#{table} has primary key #{pk} with no default sequence"
  end

  if pk && sequence
    quoted_sequence = quote_table_name(sequence)

    select_value <<-end_sql, 'SCHEMA'
      SELECT setval('#{quoted_sequence}', (SELECT COALESCE(MAX(#{quote_column_name pk})+(SELECT increment_by FROM #{quoted_sequence}), (SELECT min_value FROM #{quoted_sequence})) FROM #{quote_table_name(table)}), false)
    end_sql
  end
end

#schema_exists?(name) ⇒ Boolean

Returns true if schema exists.

Returns:

  • (Boolean) 


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

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.



178
179
180
181
182
183
184
185
186
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 178

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.



211
212
213
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 211

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.



203
204
205
206
207
208
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 203

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 



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

def serial_sequence(table, column)
  result = exec_query(<<-eosql, 'SCHEMA')
    SELECT pg_get_serial_sequence('#{table}', '#{column}')
  eosql
  result.rows.first.first
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) 


103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 103

def table_exists?(name)
  schema, table = Utils.extract_schema_and_table(name.to_s)
  return false unless table

  binds = [[nil, table]]
  binds << [nil, schema] if schema

  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 ('v','r')
      AND c.relname = '#{table.gsub(/(^"|"$)/,'')}'
      AND n.nspname = #{schema ? "'#{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.



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

def tables(name = nil)
  query(<<-SQL, 'SCHEMA').map { |row| "#{row[0]}.#{row[1]}" }
    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.



385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
 
# File 'lib/active_record/connection_adapters/redshift/schema_statements.rb', line 385

def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  case type.to_s
  when 'binary'
    # PostgreSQL doesn't support limits on binary (bytea) columns.
    # The hard limit is 1Gb, because of a 32-bit size field, and TOAST.
    case limit
    when nil, 0..0x3fffffff; super(type)
    else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
    end
  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