add_column(table_name, column_name, type, options = {})
click to toggle source
change_column(table_name, column_name, type, options = {})
click to toggle source
Changes the column of a table.
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]
execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{sql_type}"
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)
click to toggle source
Changes the default value of a table column.
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)
click to toggle source
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
client_min_messages()
click to toggle source
Returns the current client message level.
def client_min_messages
query('SHOW client_min_messages', 'SCHEMA')[0][0]
end
client_min_messages=(level)
click to toggle source
Set the client message level.
def client_min_messages=(level)
execute("SET client_min_messages TO '#{level}'", 'SCHEMA')
end
collation()
click to toggle source
Returns the current database collation.
def collation
query(" SELECT pg_database.datcollate FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
end
columns(table_name)
click to toggle source
Returns the list of all column definitions for a table.
def columns(table_name)
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
}
PostgreSQLColumn.new(column_name, default, oid, type, notnull == 'f')
end
end
create_database(name, options = {})
click to toggle source
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'
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)
click to toggle source
Creates a schema for the given schema name.
def create_schema schema_name
execute "CREATE SCHEMA #{schema_name}"
end
ctype()
click to toggle source
Returns the current database ctype.
def ctype
query(" SELECT pg_database.datctype FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
end
current_database()
click to toggle source
Returns the current database name.
def current_database
query('select current_database()', 'SCHEMA')[0][0]
end
current_schema()
click to toggle source
Returns the current schema name.
def current_schema
query('SELECT current_schema', 'SCHEMA')[0][0]
end
drop_schema(schema_name)
click to toggle source
Drops the schema for the given schema name.
def drop_schema schema_name
execute "DROP SCHEMA #{schema_name} CASCADE"
end
encoding()
click to toggle source
Returns the current database encoding format.
def encoding
query(" SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database
WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
end
index_name_length()
click to toggle source
def index_name_length
63
end
indexes(table_name, name = nil)
click to toggle source
Returns an array of indexes for the given table.
def indexes(table_name, name = nil)
result = query(" SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
FROM pg_class t
INNER JOIN pg_index d ON t.oid = d.indrelid
INNER JOIN pg_class i ON d.indexrelid = i.oid
WHERE i.relkind = 'i'
AND d.indisprimary = 'f'
AND t.relname = '#{table_name}'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
ORDER BY i.relname
", 'SCHEMA')
result.map do |row|
index_name = row[0]
unique = row[1] == 't'
indkey = row[2].split(" ")
inddef = row[3]
oid = row[4]
columns = Hash[query(" SELECT a.attnum, a.attname
FROM pg_attribute a
WHERE a.attrelid = #{oid}
AND a.attnum IN (#{indkey.join(",")})
", "SCHEMA")]
column_names = columns.values_at(*indkey).compact
unless column_names.empty?
desc_order_columns = inddef.scan(/(\w+) DESC/).flatten
orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {}
where = inddef.scan(/WHERE (.+)$/).flatten[0]
using = inddef.scan(/USING (.+?) /).flatten[0].to_sym
IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using)
end
end.compact
end
primary_key(table)
click to toggle source
Returns just a table's primary key
def primary_key(table)
row = exec_query(" SELECT attr.attname
FROM pg_attribute attr
INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1]
WHERE cons.contype = 'p'
AND cons.conrelid = '#{quote_table_name(table)}'::regclass
", 'SCHEMA').rows.first
row && row.first
end
rename_column(table_name, column_name, new_column_name)
click to toggle source
Renames a column in a table.
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)
click to toggle source
def rename_index(table_name, old_name, new_name)
execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}"
end
rename_table(table_name, new_name)
click to toggle source
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')
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
schema_exists?(name)
click to toggle source
Returns true if schema exists.
def schema_exists?(name)
exec_query(" SELECT COUNT(*)
FROM pg_namespace
WHERE nspname = '#{name}'
", 'SCHEMA').rows.first[0].to_i > 0
end
schema_names()
click to toggle source
Returns an array of schema names.
def schema_names
query(" SELECT nspname
FROM pg_namespace
WHERE nspname !~ '^pg_.*'
AND nspname NOT IN ('information_schema')
ORDER by nspname;
", 'SCHEMA').flatten
end
schema_search_path()
click to toggle source
Returns the active schema search path.
def schema_search_path
@schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
end
schema_search_path=(schema_csv)
click to toggle source
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.
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)
click to toggle source
def serial_sequence(table, column)
result = exec_query(" SELECT pg_get_serial_sequence('#{table}', '#{column}')
", 'SCHEMA')
result.rows.first.first
end
table_exists?(name)
click to toggle source
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)
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(" 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))'}
", 'SCHEMA').rows.first[0].to_i > 0
end
tables(name = nil)
click to toggle source
Returns the list of all tables in the schema search path or a specified
schema.
def tables(name = nil)
query(" SELECT tablename
FROM pg_tables
WHERE schemaname = ANY (current_schemas(false))
", 'SCHEMA').map { |row| row[0] }
end
type_to_sql(type, limit = nil, precision = nil, scale = nil)
click to toggle source
Maps logical Rails types to PostgreSQL-specific data types.
Calls superclass method
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
case type.to_s
when 'binary'
case limit
when nil, 0..0x3fffffff; super(type)
else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
end
when 'text'
case limit
when nil, 0..0x3fffffff; super(type)
else raise(ActiveRecordError, "The limit on text can be at most 1GB - 1byte.")
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