active?()
click to toggle source
Is this connection alive and ready for queries?
def active?
@connection.status == PGconn::CONNECTION_OK
rescue PGError
false
end
adapter_name()
click to toggle source
Returns ‘PostgreSQL’ as adapter name for identification purposes.
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 = {})
add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
add_column_options!(add_column_sql, options)
execute add_column_sql
end
begin_db_transaction()
click to toggle source
Begins a transaction.
def begin_db_transaction
execute "BEGIN"
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)
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)
click to toggle source
Changes the default value of a table column.
def change_column_default(table_name, column_name, default)
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)
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
clear_cache!()
click to toggle source
Clears the prepared statements cache.
def clear_cache!
@statements.clear
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
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)
column_definitions(table_name).collect do |column_name, type, default, notnull|
PostgreSQLColumn.new(column_name, default, type, notnull == 'f')
end
end
commit_db_transaction()
click to toggle source
Commits a transaction.
def commit_db_transaction
execute "COMMIT"
end
create_database(name, options = {})
click to toggle source
Create a new PostgreSQL database. Options include :owner,
:template, :encoding, :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 = options.reverse_merge(:encoding => "utf8")
option_string = options.symbolize_keys.sum do |key, value|
case key
when :owner
" OWNER = \"#{value}\""
when :template
" TEMPLATE = \"#{value}\""
when :encoding
" ENCODING = '#{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()
click to toggle source
def create_savepoint
execute("SAVEPOINT #{current_savepoint_name}")
end
current_database()
click to toggle source
Returns the current database name.
def current_database
query('select current_database()')[0][0]
end
disconnect!()
click to toggle source
Disconnects from the database if already connected. Otherwise, this method
does nothing.
def disconnect!
clear_cache!
@connection.close rescue nil
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}')[0][0]
end
escape_bytea(value)
click to toggle source
Escapes binary strings for bytea input to the database.
def escape_bytea(value)
@connection.escape_bytea(value) if value
end
exec_delete(sql, name = 'SQL', binds = [])
click to toggle source
def exec_delete(sql, name = 'SQL', binds = [])
log(sql, name, binds) do
result = binds.empty? ? exec_no_cache(sql, binds) :
exec_cache(sql, binds)
affected = result.cmd_tuples
result.clear
affected
end
end
exec_query(sql, name = 'SQL', binds = [])
click to toggle source
def exec_query(sql, name = 'SQL', binds = [])
log(sql, name, binds) do
result = binds.empty? ? exec_no_cache(sql, binds) :
exec_cache(sql, binds)
ret = ActiveRecord::Result.new(result.fields, result_as_array(result))
result.clear
return ret
end
end
exec_update(sql, name = 'SQL', binds = [])
click to toggle source
execute(sql, name = nil)
click to toggle source
Executes an SQL statement, returning a PGresult object on success or
raising a PGError exception otherwise.
def execute(sql, name = nil)
log(sql, name) do
@connection.async_exec(sql)
end
end
extract_schema_and_table(name)
click to toggle source
Extracts the table and schema name from name
def extract_schema_and_table(name)
schema, table = name.split('.', 2)
unless table
table = schema
schema = nil
end
if name =~ /^"/
table = name
schema = nil
end
[schema, table]
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)
schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
result = query( SELECT distinct i.relname, d.indisunique, d.indkey, 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 IN (#{schemas}) ) ORDER BY i.relname, name)
result.map do |row|
index_name = row[0]
unique = row[1] == 't'
indkey = row[2].split(" ")
oid = row[3]
columns = Hash[query( SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}), "Columns for index #{row[0]} on #{table_name}")]
column_names = columns.values_at(*indkey).compact
column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names)
end.compact
end
insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
click to toggle source
Executes an INSERT query and returns the new record’s ID
def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
_, table = extract_schema_and_table(sql.split(" ", 4)[2])
pk ||= primary_key(table)
if pk
select_value("#{sql} RETURNING #{quote_column_name(pk)}")
else
super
end
end
outside_transaction?()
click to toggle source
def outside_transaction?
@connection.transaction_status == PGconn::PQTRANS_IDLE
end
primary_key(table)
click to toggle source
Returns just a table’s primary key
def primary_key(table)
row = exec_query( 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 = $1::regclass, 'SCHEMA', [[nil, table]]).rows.first
row && row.first
end
quote_table_name(name)
click to toggle source
Checks the following cases:
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
reconnect!()
click to toggle source
Close then reopen the connection.
def reconnect!
clear_cache!
@connection.reset
configure_connection
end
release_savepoint()
click to toggle source
def release_savepoint
execute("RELEASE SAVEPOINT #{current_savepoint_name}")
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)
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)
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(name, new_name)
click to toggle source
Renames a table.
Example:
rename_table('octopuses', 'octopi')
def rename_table(name, new_name)
execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}"
end
reset!()
click to toggle source
def reset!
clear_cache!
super
end
rollback_db_transaction()
click to toggle source
Aborts a transaction.
def rollback_db_transaction
execute "ROLLBACK"
end
rollback_to_savepoint()
click to toggle source
def rollback_to_savepoint
execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
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')[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_search_path = schema_csv
end
end
select_rows(sql, name = nil)
click to toggle source
Executes a SELECT query and returns an array of rows. Each row is an array
of field values.
def select_rows(sql, name = nil)
select_raw(sql, name).last
end
serial_sequence(table, column)
click to toggle source
def serial_sequence(table, column)
result = exec_query( SELECT pg_get_serial_sequence($1, $2), 'SCHEMA', [[nil, table], [nil, column]])
result.rows.first.first
end
session_auth=(user)
click to toggle source
Set the authorized user for this session
def session_auth=(user)
clear_cache!
exec_query "SET SESSION AUTHORIZATION #{user}"
end
sql_for_insert(sql, pk, id_value, sequence_name, binds)
click to toggle source
def sql_for_insert(sql, pk, id_value, sequence_name, binds)
unless pk
_, table = extract_schema_and_table(sql.split(" ", 4)[2])
pk = primary_key(table)
end
sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk
[sql, binds]
end
substitute_at(column, index)
click to toggle source
def substitute_at(column, index)
Arel.sql("$#{index + 1}")
end
supports_ddl_transactions?()
click to toggle source
def supports_ddl_transactions?
true
end
supports_insert_with_returning?()
click to toggle source
def supports_insert_with_returning?
true
end
supports_migrations?()
click to toggle source
Returns true, since this connection adapter supports migrations.
def supports_migrations?
true
end
supports_savepoints?()
click to toggle source
Returns true, since this connection adapter supports savepoints.
def supports_savepoints?
true
end
supports_statement_cache?()
click to toggle source
Returns true, since this connection adapter supports prepared
statement caching.
def supports_statement_cache?
true
end
table_alias_length()
click to toggle source
Returns the configured supported identifier length supported by PostgreSQL
def table_alias_length
@table_alias_length ||= query('SHOW max_identifier_length')[0][0].to_i
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
exec_query( SELECT COUNT(*) FROM pg_tables WHERE tablename = $1 AND schemaname = #{schema ? "$2" : "ANY (current_schemas(false))"}, 'SCHEMA', binds).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_cast(value, column)
click to toggle source
def type_cast(value, column)
return super unless column
case value
when String
return super unless 'bytea' == column.sql_type
{ :value => value, :format => 1 }
else
super
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)
return super unless type.to_s == '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
end
unescape_bytea(value)
click to toggle source
Unescapes bytea output from a database to the binary string it represents.
NOTE: This is NOT an inverse of escape_bytea! This
is only to be used
on escaped binary output from database drive.
def unescape_bytea(value)
@connection.unescape_bytea(value) if value
end
update_sql(sql, name = nil)
click to toggle source
Executes an UPDATE query and returns the number of affected tuples.
def update_sql(sql, name = nil)
super.cmd_tuples
end