adapter_name()
click to toggle source
def adapter_name
ADAPTER_NAME
end
add_column(table_name, column_name, type, options = {})
click to toggle source
Adds a new column to the named table. See TableDefinition#column for
details of the options you can use.
def add_column(table_name, column_name, type, options = {})
default = options[:default]
notnull = options[:null] == false
sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale])
sql_type << "[]" if options[:array]
execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{sql_type}")
change_column_default(table_name, column_name, default) if options_include_default?(options)
change_column_null(table_name, column_name, false, default) if notnull
end
add_index(table_name, column_name, options = {})
click to toggle source
def add_index(table_name, column_name, options = {})
index_name, index_type, index_columns, index_options, index_algorithm, index_using = add_index_options(table_name, column_name, options)
execute "CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns})#{index_options}"
end
add_order_by_for_association_limiting!(sql, options)
click to toggle source
ORDER BY clause for the passed order option.
PostgreSQL does not allow arbitrary ordering
when using DISTINCT ON, so we work around this by wrapping the SQL as a
sub-select and ordering in that query.
def add_order_by_for_association_limiting!(sql, options)
return sql if options[:order].blank?
order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
order.map! { |s| 'DESC' if s =~ /\bdesc$/ }
order = order.zip((0...order.size).to_a).map { |s,i| "id_list.alias_#{i} #{s}" }.join(', ')
sql.replace "SELECT * FROM (#{sql}) AS id_list ORDER BY #{order}"
end
all_schemas()
click to toggle source
def all_schemas
select('SELECT nspname FROM pg_namespace').map { |row| row["nspname"] }
end
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 = {})
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]
begin
execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{sql_type}"
rescue ActiveRecord::StatementInvalid => e
raise e if postgresql_version > 80000
begin
begin_db_transaction
tmp_column_name = "#{column_name}_ar_tmp"
add_column(table_name, tmp_column_name, type, options)
execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{sql_type})"
remove_column(table_name, column_name)
rename_column(table_name, tmp_column_name, column_name)
commit_db_transaction
rescue
rollback_db_transaction
end
end
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)
if column = column_for(table_name, column_name)
execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote_default_value(default, column)}"
else
execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
end
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)
unless null || default.nil?
if 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"
else
execute "UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL"
end
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
select_value('SHOW client_min_messages', 'SCHEMA')
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
select_value(
"SELECT pg_database.datcollate" <<
" FROM pg_database" <<
" WHERE pg_database.datname LIKE '#{current_database}'",
'SCHEMA')
end
column_for(table_name, column_name)
click to toggle source
@private
def column_for(table_name, column_name)
columns(table_name).detect { |c| c.name == column_name.to_s }
end
columns(table_name, name = nil)
click to toggle source
Returns the list of all column definitions for a table.
def columns(table_name, name = nil)
klass = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_definitions(table_name).map do |row|
name = row[0]; type = row[1]; default = row[2]
notnull = row[3]; oid = row[4]; fmod = row[5]
notnull = notnull == 't' if notnull.is_a?(String)
if default =~ /^nextval\(.*?\:\:regclass\)$/
default = nil
elsif default =~ /^\(([-+]?[\d\.]+)\)$/
default = $1
end
klass.new(name, default, oid, type, ! notnull, fmod, self)
end
end
columns_for_distinct(columns, orders)
click to toggle source
PostgreSQL requires the ORDER BY columns in
the select list for distinct queries, and requires that the ORDER BY
include the distinct column. @override Since AR 4.0 (on 4.1 {distinct} is gone and won't be
called).
def columns_for_distinct(columns, orders)
if orders.is_a?(String)
orders = orders.split(','); orders.each(&:strip!)
end
order_columns = orders.reject(&:blank?).map! do |column|
column = column.is_a?(String) ? column.dup : column.to_sql
column.gsub!(/\s+(?:ASC|DESC)\s*/, '')
column.gsub!(/\s*NULLS\s+(?:FIRST|LAST)?\s*/, '')
column
end
order_columns.reject!(&:empty?)
i = -1; order_columns.map! { |column| "#{column} AS alias_#{i += 1}" }
columns = [ columns ]; columns.flatten!
columns.push( *order_columns ).join(', ')
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, 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_savepoint(name = current_savepoint_name(true))
click to toggle source
@override
def create_savepoint(name = current_savepoint_name(true))
log("SAVEPOINT #{name}", 'Savepoint') { super }
end
create_schema(schema_name, pg_username = nil)
click to toggle source
Creates a schema for the given schema name.
def create_schema(schema_name, pg_username = nil)
if pg_username.nil?
execute "CREATE SCHEMA #{schema_name}"
else
execute("CREATE SCHEMA \"#{schema_name}\" AUTHORIZATION \"#{pg_username}\"")
end
end
ctype()
click to toggle source
Returns the current database ctype.
def ctype
select_value(
"SELECT pg_database.datctype FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'",
'SCHEMA')
end
current_database()
click to toggle source
current database name
def current_database
select_value('SELECT current_database()', 'SCHEMA')
end
current_schema()
click to toggle source
Returns the current schema name.
def current_schema
select_value('SELECT current_schema', 'SCHEMA')
end
default_sequence_name(table_name, pk = nil)
click to toggle source
def default_sequence_name(table_name, pk = nil)
default_pk, default_seq = pk_and_sequence_for(table_name)
default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq"
end
disable_extension(name)
click to toggle source
def disable_extension(name)
execute("DROP EXTENSION IF EXISTS \"#{name}\" CASCADE")
end
disable_referential_integrity()
click to toggle source
def disable_referential_integrity
if supports_disable_referential_integrity?
begin
execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
rescue
execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER USER" }.join(";"))
end
end
yield
ensure
if supports_disable_referential_integrity?
begin
execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))
rescue
execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER USER" }.join(";"))
end
end
end
distinct(columns, orders)
click to toggle source
@override
def distinct(columns, orders)
"DISTINCT #{columns_for_distinct(columns, orders)}"
end
drop_database(name)
click to toggle source
def drop_database(name)
execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
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
enable_extension(name)
click to toggle source
def enable_extension(name)
execute("CREATE EXTENSION IF NOT EXISTS \"#{name}\"")
end
encoding()
click to toggle source
Returns the current database encoding format.
def encoding
select_value(
"SELECT pg_encoding_to_char(pg_database.encoding)" <<
" FROM pg_database" <<
" WHERE pg_database.datname LIKE '#{current_database}'",
'SCHEMA')
end
escape_bytea(string)
click to toggle source
def escape_bytea(string)
return unless string
if supports_hex_escaped_bytea?
"\\\\x#{string.unpack("H*")[0]}"
else
result = ''
string.each_byte { |c| result << sprintf('\\%03o', c) }
result
end
end
exec_insert(sql, name, binds, pk = nil, sequence_name = nil)
click to toggle source
@override due RETURNING clause
def exec_insert(sql, name, binds, pk = nil, sequence_name = nil)
if use_insert_returning? && ( pk || (sql.is_a?(String) && sql =~ /RETURNING "?\S+"?$/) )
exec_query(sql, name, binds)
else
result = super
if pk
unless sequence_name
table_ref = extract_table_ref_from_insert_sql(sql)
sequence_name = default_sequence_name(table_ref, pk)
return result unless sequence_name
end
last_insert_id_result(sequence_name)
else
result
end
end
end
extension_enabled?(name)
click to toggle source
def extension_enabled?(name)
if supports_extensions?
rows = select_rows("SELECT EXISTS(SELECT * FROM pg_available_extensions WHERE name = '#{name}' AND installed_version IS NOT NULL)", 'SCHEMA')
available = rows.first.first
available == true || available == 't'
end
end
extensions()
click to toggle source
def extensions
if supports_extensions?
rows = select_rows "SELECT extname from pg_extension", "SCHEMA"
rows.map { |row| row.first }
else
[]
end
end
index_algorithms()
click to toggle source
def index_algorithms
{ :concurrently => 'CONCURRENTLY' }
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 = select_rows( 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].is_a?(String) ? row[1] == 't' : row[1]
indkey = row[2].is_a?(Java::OrgPostgresqlUtil::PGobject) ? row[2].value : row[2]
indkey = indkey.split(" ")
inddef = row[3]
oid = row[4]
columns = select_rows( SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}), "SCHEMA")
columns = Hash[ columns.each { |column| column[0] = column[0].to_s } ]
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 { |column| [column, :desc] } ] : {}
if ActiveRecord::VERSION::MAJOR > 3
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)
else
IndexDefinition.new(table_name, index_name, unique, column_names, [], orders)
end
end
end
result.compact!
result
end
init_connection(jdbc_connection)
click to toggle source
@private
def init_connection(jdbc_connection)
meta = jdbc_connection.meta_data
if meta.driver_version.index('JDBC3')
config[:connection_alive_sql] ||= 'SELECT 1'
else
PostgreSQL.send(:remove_method, :init_connection) rescue nil
end
end
insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = [])
click to toggle source
def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = [])
unless pk
table_ref = extract_table_ref_from_insert_sql(sql)
pk = primary_key(table_ref) if table_ref
end
if pk && use_insert_returning?
select_value("#{to_sql(sql, binds)} RETURNING #{quote_column_name(pk)}")
else
execute(sql, name, binds)
unless id_value
table_ref ||= extract_table_ref_from_insert_sql(sql)
if table_ref && ! ( pk ||= primary_key(table_ref) ) && ! sequence_name
pk, sequence_name = pk_and_sequence_for(table_ref)
end
if pk && sequence_name ||= default_sequence_name(table_ref, pk)
id_value = last_insert_id(table_ref, sequence_name)
end
end
id_value
end
end
last_insert_id(table, sequence_name = nil)
click to toggle source
def last_insert_id(table, sequence_name = nil)
sequence_name = table if sequence_name.nil?
last_insert_id_result(sequence_name)
end
last_insert_id_result(sequence_name)
click to toggle source
def last_insert_id_result(sequence_name)
select_value("SELECT currval('#{sequence_name}')", 'SQL')
end
last_inserted_id(result)
click to toggle source
Take an id from the result of an INSERT query. @return [Integer, NilClass]
def last_inserted_id(result)
return nil if result.nil?
return result if result.is_a? Integer
result.first.first[1]
end
migration_keys()
click to toggle source
Adds `:array` as a valid migration key.
def migration_keys
super + [:array]
end
multi_column_index_limit()
click to toggle source
Gets the maximum number columns postgres has, default 32
def multi_column_index_limit
defined?(@multi_column_index_limit) && @multi_column_index_limit || 32
end
multi_column_index_limit=(limit)
click to toggle source
Sets the maximum number columns postgres has, default 32
def multi_column_index_limit=(limit)
@multi_column_index_limit = limit
end
native_database_types()
click to toggle source
def native_database_types
NATIVE_DATABASE_TYPES
end
pk_and_sequence_for(table)
click to toggle source
Find a table’s primary key and sequence.
def pk_and_sequence_for(table)
result = select( 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, 'PK and Serial Sequence')[0]
if result.nil? || result.empty?
result = select( SELECT attr.attname, CASE WHEN pg_get_expr(def.adbin, def.adrelid) !~* 'nextval' THEN NULL 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 as relname 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|uuid_generate', 'PK and Custom Sequence')[0]
end
[ result['attname'], result['relname'] ]
rescue
nil
end
postgresql_version()
click to toggle source
def postgresql_version
@postgresql_version ||=
begin
value = select_value('SELECT version()')
if value =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
else
0
end
end
end
prepare_column_options(column, types)
click to toggle source
Adds `:array` option to the default set provided by the `AbstractAdapter`.
def prepare_column_options(column, types)
spec = super
spec[:array] = 'true' if column.respond_to?(:array) && column.array
spec[:default] = "\"#{column.default_function}\"" if column.default_function
spec
end
primary_key(table)
click to toggle source
def primary_key(table)
result = select( 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').first
result && result['attname']
end
query(sql, name = nil)
click to toggle source
@note Only for “better” AR 4.0 compatibility. @private
def query(sql, name = nil)
log(sql, name) do
result = []
@connection.execute_query_raw(sql, nil) do |*values|
result << values
end
result
end
end
quote(value, column = nil)
click to toggle source
@return [String] @override
def quote(value, column = nil)
return super unless column && column.type
return value if sql_literal?(value)
case value
when Float
if value.infinite? && ( column.type == :datetime || column.type == :timestamp )
"'#{value.to_s.downcase}'"
elsif value.infinite? || value.nan?
"'#{value.to_s}'"
else super
end
when Numeric
if column.respond_to?(:sql_type) && column.sql_type == 'money'
"'#{value}'"
elsif column.type == :string || column.type == :text
"'#{value}'"
else super
end
when String
return "E'#{escape_bytea(value)}'::bytea" if column.type == :binary
return "xml '#{quote_string(value)}'" if column.type == :xml
sql_type = column.respond_to?(:sql_type) && column.sql_type
sql_type && sql_type[0, 3] == 'bit' ? quote_bit(value) : super
when Array
if AR4_COMPAT && column.array?
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
"'#{column_class.array_to_string(value, column, self).gsub(/'/, "''")}'"
elsif column.type == :json
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
super(column_class.json_to_string(value), column)
elsif column.type == :point
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
super(column_class.point_to_string(value), column)
else super
end
when Hash
if column.type == :hstore
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
super(column_class.hstore_to_string(value), column)
elsif column.type == :json
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
super(column_class.json_to_string(value), column)
else super
end
when Range
sql_type = column.respond_to?(:sql_type) && column.sql_type
if sql_type && sql_type[-5, 5] == 'range' && AR4_COMPAT
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
escaped = quote_string(column_class.range_to_string(value))
"'#{escaped}'::#{sql_type}"
else super
end
when IPAddr
if column.type == :inet || column.type == :cidr
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
super(column_class.cidr_to_string(value), column)
else super
end
else
super
end
end
quote_bit(value)
click to toggle source
@return [String]
def quote_bit(value)
case value
when /\A[01]*\Z/ then "B'#{value}'"
when /\A[0-9A-F]*\Z/ then "X'#{value}'"
end
end
quote_column_name(name)
click to toggle source
@override
def quote_column_name(name)
%("#{name.to_s.gsub("\"", "\"\"")}")
end
quote_default_value(value, column)
click to toggle source
@private
def quote_default_value(value, column)
if column.type == :uuid && value =~ /\(\)/
value
else
quote(value, column)
end
end
quote_string(string)
click to toggle source
Quotes a string, escaping any ‘ (single quote) and \ (backslash) chars.
@return [String] @override
def quote_string(string)
quoted = string.gsub("'", "''")
unless standard_conforming_strings?
quoted.gsub!(/\\/, '\&\&')
end
quoted
end
quote_table_name(name)
click to toggle source
@override
def quote_table_name(name)
schema, name_part = extract_pg_identifier_from_name(name.to_s)
unless name_part
quote_column_name(schema)
else
table_name, name_part = extract_pg_identifier_from_name(name_part)
"#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
end
end
quote_table_name_for_assignment(table, attr)
click to toggle source
@override
def quote_table_name_for_assignment(table, attr)
quote_column_name(attr)
end
quoted_date(value)
click to toggle source
Quote date/time values for use in SQL input. Includes microseconds if the
value is a Time responding to `usec`. @override
def quoted_date(value)
result = super
if value.acts_like?(:time) && value.respond_to?(:usec)
result = "#{result}.#{sprintf("%06d", value.usec)}"
end
result = "#{result.sub(/^-/, '')} BC" if value.year < 0
result
end
recreate_database(name, options = {})
click to toggle source
def recreate_database(name, options = {})
drop_database(name)
create_database(name, options)
end
release_savepoint(name = current_savepoint_name)
click to toggle source
@override
def release_savepoint(name = current_savepoint_name)
log("RELEASE SAVEPOINT #{name}", 'Savepoint') { super }
end
remove_index!(table_name, index_name)
click to toggle source
def remove_index!(table_name, index_name)
execute "DROP INDEX #{quote_table_name(index_name)}"
end
rename_column(table_name, column_name, new_column_name)
click to toggle source
def rename_column(table_name, column_name, new_column_name)
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) if respond_to?(:rename_column_indexes)
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
def rename_table(table_name, new_name)
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) if respond_to?(:rename_table_indexes)
end
reset_pk_sequence!(table, pk = nil, sequence = nil)
click to toggle source
Resets sequence to the max value of the table’s primary key if present.
def reset_pk_sequence!(table, pk = nil, sequence = nil)
if ! pk || ! sequence
default_pk, default_sequence = pk_and_sequence_for(table)
pk ||= default_pk; sequence ||= default_sequence
end
if pk && sequence
quoted_sequence = quote_column_name(sequence)
select_value 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), 'Reset Sequence'
end
end
rollback_to_savepoint(name = current_savepoint_name)
click to toggle source
@override
def rollback_to_savepoint(name = current_savepoint_name)
log("ROLLBACK TO SAVEPOINT #{name}", 'Savepoint') { super }
end
schema_creation()
click to toggle source
def schema_creation
SchemaCreation.new self
end
schema_exists?(name)
click to toggle source
Returns true if schema exists.
def schema_exists?(name)
select_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = '#{name}'", 'SCHEMA').to_i > 0
end
schema_names()
click to toggle source
Returns an array of schema names.
def schema_names
select_values(
"SELECT nspname FROM pg_namespace" <<
" WHERE nspname !~ '^pg_.*' AND nspname NOT IN ('information_schema')" <<
" ORDER by nspname;",
'SCHEMA')
end
schema_search_path()
click to toggle source
Returns the active schema search path.
def schema_search_path
@schema_search_path ||= select_value('SHOW search_path', 'SCHEMA')
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_search_path = schema_csv
end
end
session_auth=(user)
click to toggle source
Set the authorized user for this session.
def session_auth=(user)
execute "SET SESSION AUTHORIZATION #{user}"
end
set_client_encoding(encoding)
click to toggle source
def set_client_encoding(encoding)
ActiveRecord::Base.logger.warn "client_encoding is set by the driver and should not be altered, ('#{encoding}' ignored)"
ActiveRecord::Base.logger.debug "Set the 'allowEncodingChanges' driver property (e.g. using config[:properties]) if you need to override the client encoding when doing a copy."
end
sql_for_insert(sql, pk, id_value, sequence_name, binds)
click to toggle source
@override
def sql_for_insert(sql, pk, id_value, sequence_name, binds)
unless pk
table_ref = extract_table_ref_from_insert_sql(sql)
pk = primary_key(table_ref) if table_ref
end
if pk && use_insert_returning?
sql = "#{sql} RETURNING #{quote_column_name(pk)}"
end
[ sql, binds ]
end
structure_dump()
click to toggle source
@deprecated no longer used - handled with (AR built-in) Rake tasks
def structure_dump
database = @config[:database]
if database.nil?
if @config[:url] =~ /\/([^\/]*)$/
database = $1
else
raise "Could not figure out what database this url is for #{@config["url"]}"
end
end
ENV['PGHOST'] = @config[:host] if @config[:host]
ENV['PGPORT'] = @config[:port].to_s if @config[:port]
ENV['PGPASSWORD'] = @config[:password].to_s if @config[:password]
search_path = "--schema=#{@config[:schema_search_path]}" if @config[:schema_search_path]
@connection.connection.close
begin
definition = `pg_dump -i -U "#{@config[:username]}" -s -x -O #{search_path} #{database}`
raise "Error dumping database" if $?.exitstatus == 1
definition.gsub(/SQL_ASCII/, 'UNICODE')
ensure
reconnect!
end
end
supports_ddl_transactions?()
click to toggle source
def supports_ddl_transactions?
true
end
supports_disable_referential_integrity?()
click to toggle source
@override
def supports_disable_referential_integrity?
true
end
supports_extensions?()
click to toggle source
def supports_extensions?
postgresql_version >= 90200
end
supports_hex_escaped_bytea?()
click to toggle source
def supports_hex_escaped_bytea?
postgresql_version >= 90000
end
supports_index_sort_order?()
click to toggle source
def supports_index_sort_order?
true
end
supports_insert_with_returning?()
click to toggle source
def supports_insert_with_returning?
postgresql_version >= 80200
end
supports_migrations?()
click to toggle source
Does PostgreSQL support migrations?
def supports_migrations?
true
end
supports_partial_index?()
click to toggle source
def supports_partial_index?
true
end
supports_primary_key?()
click to toggle source
Does PostgreSQL support finding primary key
on non-Active Record tables?
def supports_primary_key?
true
end
supports_ranges?()
click to toggle source
Range data-types weren’t introduced until PostgreSQL 9.2.
def supports_ranges?
postgresql_version >= 90200
end
supports_savepoints?()
click to toggle source
@override
def supports_savepoints?
true
end
supports_transaction_isolation?()
click to toggle source
def supports_transaction_isolation?
true
end
table_alias_length()
click to toggle source
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
def table_alias_length
@table_alias_length ||= (
postgresql_version >= 80000 ?
select_one('SHOW max_identifier_length')['max_identifier_length'].to_i :
63
)
end
table_exists?(name)
click to toggle source
def table_exists?(name)
schema, table = extract_schema_and_table(name.to_s)
return false unless table
binds = [[ nil, table.gsub(/(^"|"$)/,'') ]]
binds << [ nil, schema ] if schema
sql = SELECT COUNT(*) as table_count FROM pg_tables WHERE tablename = ? AND schemaname = #{schema ? "?" : "ANY (current_schemas(false))"}
log(sql, 'SCHEMA', binds) do
@connection.execute_query_raw(sql, binds).first["table_count"] > 0
end
end
tables(name = nil)
click to toggle source
def tables(name = nil)
select_values( SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false)), 'SCHEMA')
end
type_cast(value, column, array_member = false)
click to toggle source
def type_cast(value, column, array_member = false)
return super(value, nil) unless column
case value
when String
return super(value, column) unless 'bytea' == column.sql_type
value
when Array
case column.sql_type
when 'point'
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.point_to_string(value)
when 'json'
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.json_to_string(value)
else
return super(value, column) unless column.array?
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.array_to_string(value, column, self)
end
when NilClass
if column.array? && array_member
'NULL'
elsif column.array?
value
else
super(value, column)
end
when Hash
case column.sql_type
when 'hstore'
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.hstore_to_string(value)
when 'json'
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.json_to_string(value)
else super(value, column)
end
when IPAddr
return super unless column.sql_type == 'inet' || column.sql_type == 'cidr'
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.cidr_to_string(value)
when Range
return super(value, column) unless /range$/ =~ column.sql_type
column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn
column_class.range_to_string(value)
else
super(value, column)
end
end
type_to_sql(type, limit = nil, precision = nil, scale = nil)
click to toggle source
Maps logical Rails types to PostgreSQL-specific data types.
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
case type.to_sym
when :'binary'
case limit
when nil, 0..0x3fffffff; super(type, nil, nil, nil)
else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
end
when :'text'
case limit
when nil, 0..0x3fffffff; super(type, nil, nil, nil)
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
use_insert_returning?()
click to toggle source
def use_insert_returning?
if @use_insert_returning.nil?
@use_insert_returning = supports_insert_with_returning?
end
@use_insert_returning
end