Class: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
- Inherits:
-
AbstractAdapter
- Object
- AbstractAdapter
- ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
- Defined in:
- lib/active_record/connection_adapters/postgresql_adapter.rb
Overview
The PostgreSQL adapter works both with the native C (ruby.scripting.ca/postgres/) and the pure Ruby (available both as gem and from rubyforge.org/frs/?group_id=234&release_id=1944) drivers.
Options:
-
:host
- Defaults to “localhost”. -
:port
- Defaults to 5432. -
:username
- Defaults to nothing. -
:password
- Defaults to nothing. -
:database
- The name of the database. No default, must be provided. -
:schema_search_path
- An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the:schema_order
option. -
:encoding
- An optional client encoding that is used in aSET client_encoding TO <encoding>
call on the connection. -
:min_messages
- An optional client min messages that is used in aSET client_min_messages TO <min_messages>
call on the connection.
Defined Under Namespace
Modules: Utils Classes: BindSubstitution, ExplainPrettyPrinter, StatementPool, TableDefinition
Constant Summary collapse
- ADAPTER_NAME =
'PostgreSQL'
- NATIVE_DATABASE_TYPES =
{ :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :decimal => { :name => "decimal" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" }, :xml => { :name => "xml" }, :tsvector => { :name => "tsvector" } }
Instance Attribute Summary
Attributes inherited from AbstractAdapter
#in_use, #last_use, #logger, #open_transactions, #pool, #schema_cache, #visitor
Attributes included from QueryCache
#query_cache, #query_cache_enabled
Instance Method Summary collapse
-
#active? ⇒ Boolean
Is this connection alive and ready for queries?.
-
#adapter_name ⇒ Object
Returns ‘PostgreSQL’ as adapter name for identification purposes.
-
#add_column(table_name, column_name, type, options = {}) ⇒ Object
Adds a new column to the named table.
-
#begin_db_transaction ⇒ Object
Begins a transaction.
-
#change_column(table_name, column_name, type, options = {}) ⇒ Object
Changes the column of a table.
-
#change_column_default(table_name, column_name, default) ⇒ Object
Changes the default value of a table column.
- #change_column_null(table_name, column_name, null, default = nil) ⇒ Object
-
#clear_cache! ⇒ Object
Clears the prepared statements cache.
-
#client_min_messages ⇒ Object
Returns the current client message level.
-
#client_min_messages=(level) ⇒ Object
Set the client message level.
-
#columns(table_name, name = nil) ⇒ Object
Returns the list of all column definitions for a table.
-
#commit_db_transaction ⇒ Object
Commits a transaction.
-
#create_database(name, options = {}) ⇒ Object
Create a new PostgreSQL database.
- #create_savepoint ⇒ Object
-
#current_database ⇒ Object
Returns the current database name.
-
#current_schema ⇒ Object
Returns the current schema name.
-
#default_sequence_name(table_name, pk = nil) ⇒ Object
Returns the sequence name for a table’s primary key or some other specified key.
-
#disable_referential_integrity ⇒ Object
:nodoc:.
-
#disconnect! ⇒ Object
Disconnects from the database if already connected.
-
#distinct(columns, orders) ⇒ Object
Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.
-
#drop_database(name) ⇒ Object
Drops a PostgreSQL database.
-
#encoding ⇒ Object
Returns the current database encoding format.
-
#escape_bytea(value) ⇒ Object
Escapes binary strings for bytea input to the database.
- #exec_delete(sql, name = 'SQL', binds = []) ⇒ Object (also: #exec_update)
- #exec_query(sql, name = 'SQL', binds = []) ⇒ Object
-
#execute(sql, name = nil) ⇒ Object
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
-
#explain(arel, binds = []) ⇒ Object
DATABASE STATEMENTS ======================================.
- #index_name_length ⇒ Object
-
#indexes(table_name, name = nil) ⇒ Object
Returns an array of indexes for the given table.
-
#initialize(connection, logger, connection_parameters, config) ⇒ PostgreSQLAdapter
constructor
Initializes and connects a PostgreSQL adapter.
-
#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
Executes an INSERT query and returns the new record’s ID.
-
#native_database_types ⇒ Object
:nodoc:.
- #outside_transaction? ⇒ Boolean
-
#pk_and_sequence_for(table) ⇒ Object
Returns a table’s primary key and belonging sequence.
-
#primary_key(table) ⇒ Object
Returns just a table’s primary key.
-
#query(sql, name = nil) ⇒ Object
Queries the database and returns the results in an Array-like object.
-
#quote(value, column = nil) ⇒ Object
Quotes PostgreSQL-specific data types for SQL input.
-
#quote_column_name(name) ⇒ Object
Quotes column names for use in SQL queries.
-
#quote_string(s) ⇒ Object
Quotes strings for use in SQL input.
-
#quote_table_name(name) ⇒ Object
Checks the following cases:.
-
#quoted_date(value) ⇒ Object
Quote date/time values for use in SQL input.
-
#reconnect! ⇒ Object
Close then reopen the connection.
-
#recreate_database(name, options = {}) ⇒ Object
Drops the database specified on the
name
attribute and creates it again using the providedoptions
. - #release_savepoint ⇒ Object
-
#remove_index!(table_name, index_name) ⇒ Object
:nodoc:.
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
Renames a column in a table.
- #rename_index(table_name, old_name, new_name) ⇒ Object
-
#rename_table(name, new_name) ⇒ Object
Renames a table.
- #reset! ⇒ Object
-
#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object
Resets the sequence of a table’s primary key to the maximum value.
-
#result_as_array(res) ⇒ Object
create a 2D array representing the result set.
-
#rollback_db_transaction ⇒ Object
Aborts a transaction.
- #rollback_to_savepoint ⇒ Object
-
#schema_exists?(name) ⇒ Boolean
Returns true if schema exists.
-
#schema_search_path ⇒ Object
Returns the active schema search path.
-
#schema_search_path=(schema_csv) ⇒ Object
Sets the schema search path to a string of comma-separated schema names.
-
#select_rows(sql, name = nil) ⇒ Object
Executes a SELECT query and returns an array of rows.
- #serial_sequence(table, column) ⇒ Object
-
#session_auth=(user) ⇒ Object
Set the authorized user for this session.
-
#set_standard_conforming_strings ⇒ Object
Enable standard-conforming strings if available.
- #sql_for_insert(sql, pk, id_value, sequence_name, binds) ⇒ Object
- #substitute_at(column, index) ⇒ Object
- #supports_ddl_transactions? ⇒ Boolean
-
#supports_disable_referential_integrity? ⇒ Boolean
REFERENTIAL INTEGRITY ====================================.
-
#supports_explain? ⇒ Boolean
Returns true.
- #supports_index_sort_order? ⇒ Boolean
- #supports_insert_with_returning? ⇒ Boolean
-
#supports_migrations? ⇒ Boolean
Returns true, since this connection adapter supports migrations.
-
#supports_primary_key? ⇒ Boolean
Does PostgreSQL support finding primary key on non-Active Record tables?.
-
#supports_savepoints? ⇒ Boolean
Returns true, since this connection adapter supports savepoints.
-
#supports_statement_cache? ⇒ Boolean
Returns
true
, since this connection adapter supports prepared statement caching. -
#table_alias_length ⇒ Object
Returns the configured supported identifier length supported by PostgreSQL.
-
#table_exists?(name) ⇒ Boolean
Returns true if table exists.
-
#tables(name = nil) ⇒ Object
Returns the list of all tables in the schema search path or a specified schema.
- #type_cast(value, column) ⇒ Object
-
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
Maps logical Rails types to PostgreSQL-specific data types.
-
#unescape_bytea(value) ⇒ Object
Unescapes bytea output from a database to the binary string it represents.
-
#update_sql(sql, name = nil) ⇒ Object
Executes an UPDATE query and returns the number of affected tuples.
Methods inherited from AbstractAdapter
#case_insensitive_comparison, #case_sensitive_modifier, #close, #current_savepoint_name, #decrement_open_transactions, #expire, #increment_open_transactions, #lease, #prefetch_primary_key?, #raw_connection, #requires_reloading?, #supports_bulk_alter?, #supports_count_distinct?, #transaction_joinable=, #verify!
Methods included from QueryCache
#cache, #clear_query_cache, dirties_query_cache, #disable_query_cache!, #enable_query_cache!, included, #select_all, #uncached
Methods included from DatabaseLimits
#column_name_length, #columns_per_multicolumn_index, #columns_per_table, #in_clause_length, #indexes_per_table, #joins_per_query, #sql_query_length, #table_name_length
Methods included from Quoting
Methods included from DatabaseStatements
#add_transaction_record, #case_sensitive_equality_operator, #delete, #empty_insert_statement_value, #exec_insert, #insert, #insert_fixture, #join_to_update, #limited_update_conditions, #reset_sequence!, #sanitize_limit, #select_all, #select_one, #select_value, #select_values, #to_sql, #transaction, #update
Methods included from SchemaStatements
#add_column_options!, #add_index, #add_timestamps, #assume_migrated_upto_version, #change_table, #column_exists?, #create_table, #drop_table, #dump_schema_information, #index_exists?, #index_name, #index_name_exists?, #initialize_schema_migrations_table, #remove_column, #remove_index, #remove_timestamps, #structure_dump, #table_alias_for
Constructor Details
#initialize(connection, logger, connection_parameters, config) ⇒ PostgreSQLAdapter
Initializes and connects a PostgreSQL adapter.
309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 309 def initialize(connection, logger, connection_parameters, config) super(connection, logger) if config.fetch(:prepared_statements) { true } @visitor = Arel::Visitors::PostgreSQL.new self else @visitor = BindSubstitution.new self end connection_parameters.delete :prepared_statements @connection_parameters, @config = connection_parameters, config # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil @table_alias_length = nil connect @statements = StatementPool.new @connection, config.fetch(:statement_limit) { 1000 } if postgresql_version < 80200 raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!" end @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] end |
Instance Method Details
#active? ⇒ Boolean
Is this connection alive and ready for queries?
343 344 345 346 347 348 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 343 def active? @connection.query 'SELECT 1' true rescue PGError false end |
#adapter_name ⇒ Object
Returns ‘PostgreSQL’ as adapter name for identification purposes.
238 239 240 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 238 def adapter_name ADAPTER_NAME end |
#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.
1020 1021 1022 1023 1024 1025 1026 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1020 def add_column(table_name, column_name, type, = {}) clear_cache! add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, [:limit], [:precision], [:scale])}" (add_column_sql, ) execute add_column_sql end |
#begin_db_transaction ⇒ Object
Begins a transaction.
700 701 702 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 700 def begin_db_transaction execute "BEGIN" end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
Changes the column of a table.
1029 1030 1031 1032 1033 1034 1035 1036 1037 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1029 def change_column(table_name, column_name, type, = {}) 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, [:limit], [:precision], [:scale])}" change_column_default(table_name, column_name, [:default]) if () change_column_null(table_name, column_name, [:null], [:default]) if .key?(:null) end |
#change_column_default(table_name, column_name, default) ⇒ Object
Changes the default value of a table column.
1040 1041 1042 1043 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1040 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
1045 1046 1047 1048 1049 1050 1051 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1045 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 |
#clear_cache! ⇒ Object
Clears the prepared statements cache.
338 339 340 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 338 def clear_cache! @statements.clear end |
#client_min_messages ⇒ Object
Returns the current client message level.
898 899 900 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 898 def query('SHOW client_min_messages', 'SCHEMA')[0][0] end |
#client_min_messages=(level) ⇒ Object
Set the client message level.
903 904 905 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 903 def (level) execute("SET client_min_messages TO '#{level}'", 'SCHEMA') end |
#columns(table_name, name = nil) ⇒ Object
Returns the list of all column definitions for a table.
855 856 857 858 859 860 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 855 def columns(table_name, name = nil) # Limit, precision, and scale are all handled by the superclass. column_definitions(table_name).collect do |column_name, type, default, notnull| PostgreSQLColumn.new(column_name, default, type, notnull == 'f') end end |
#commit_db_transaction ⇒ Object
Commits a transaction.
705 706 707 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 705 def commit_db_transaction execute "COMMIT" end |
#create_database(name, options = {}) ⇒ Object
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'
746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 746 def create_database(name, = {}) = .reverse_merge(:encoding => "utf8") option_string = .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 ⇒ Object
718 719 720 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 718 def create_savepoint execute("SAVEPOINT #{current_savepoint_name}") end |
#current_database ⇒ Object
Returns the current database name.
863 864 865 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 863 def current_database query('select current_database()', 'SCHEMA')[0][0] end |
#current_schema ⇒ Object
Returns the current schema name.
868 869 870 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 868 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.
908 909 910 911 912 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 908 def default_sequence_name(table_name, pk = nil) #:nodoc: serial_sequence(table_name, pk || 'id').split('.').last rescue ActiveRecord::StatementInvalid "#{table_name}_#{pk || 'id'}_seq" end |
#disable_referential_integrity ⇒ Object
:nodoc:
521 522 523 524 525 526 527 528 529 530 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 521 def disable_referential_integrity #:nodoc: if supports_disable_referential_integrity? then execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";")) end yield ensure if supports_disable_referential_integrity? then execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";")) end end |
#disconnect! ⇒ Object
Disconnects from the database if already connected. Otherwise, this method does nothing.
365 366 367 368 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 365 def disconnect! clear_cache! @connection.close rescue nil 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")
1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1101 def distinct(columns, orders) #:nodoc: return "DISTINCT #{columns}" if orders.empty? # Construct a clean list of column names from the ORDER BY clause, removing # any ASC/DESC modifiers order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '') } order_columns.delete_if { |c| c.blank? } order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" } "DISTINCT #{columns}, #{order_columns * ', '}" end |
#drop_database(name) ⇒ Object
Drops a PostgreSQL database.
Example:
drop_database 'matt_development'
773 774 775 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 773 def drop_database(name) #:nodoc: execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}" end |
#encoding ⇒ Object
Returns the current database encoding format.
873 874 875 876 877 878 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 873 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 |
#escape_bytea(value) ⇒ Object
Escapes binary strings for bytea input to the database.
418 419 420 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 418 def escape_bytea(value) @connection.escape_bytea(value) if value end |
#exec_delete(sql, name = 'SQL', binds = []) ⇒ Object Also known as: exec_update
671 672 673 674 675 676 677 678 679 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 671 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 = []) ⇒ Object
660 661 662 663 664 665 666 667 668 669 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 660 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 |
#execute(sql, name = nil) ⇒ Object
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
650 651 652 653 654 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 650 def execute(sql, name = nil) log(sql, name) do @connection.async_exec(sql) end end |
#explain(arel, binds = []) ⇒ Object
DATABASE STATEMENTS ======================================
534 535 536 537 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 534 def explain(arel, binds = []) sql = "EXPLAIN #{to_sql(arel, binds)}" ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds)) end |
#index_name_length ⇒ Object
1067 1068 1069 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1067 def index_name_length 63 end |
#indexes(table_name, name = nil) ⇒ Object
Returns an array of indexes for the given table.
816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 816 def indexes(table_name, name = nil) result = query(<<-SQL, 'SCHEMA') 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 SQL 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(<<-SQL, "SCHEMA")] SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}) SQL column_names = columns.values_at(*indkey).compact # add info on sort order for columns (only desc order is explicitly specified, asc is the default) desc_order_columns = inddef.scan(/(\w+) DESC/).flatten orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {} column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders) end.compact end |
#insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
Executes an INSERT query and returns the new record’s ID
583 584 585 586 587 588 589 590 591 592 593 594 595 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 583 def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) unless pk # Extract the table from the insert sql. Yuck. table_ref = extract_table_ref_from_insert_sql(sql) pk = primary_key(table_ref) if table_ref end if pk select_value("#{sql} RETURNING #{quote_column_name(pk)}") else super end end |
#native_database_types ⇒ Object
:nodoc:
370 371 372 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 370 def native_database_types #:nodoc: NATIVE_DATABASE_TYPES end |
#outside_transaction? ⇒ Boolean
714 715 716 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 714 def outside_transaction? @connection.transaction_status == PGconn::PQTRANS_IDLE end |
#pk_and_sequence_for(table) ⇒ Object
Returns a table’s primary key and belonging sequence.
944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 944 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_namespace name, 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
989 990 991 992 993 994 995 996 997 998 999 1000 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 989 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 |
#query(sql, name = nil) ⇒ Object
Queries the database and returns the results in an Array-like object
642 643 644 645 646 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 642 def query(sql, name = nil) #:nodoc: log(sql, name) do result_as_array @connection.async_exec(sql) end end |
#quote(value, column = nil) ⇒ Object
Quotes PostgreSQL-specific data types for SQL input.
430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 430 def quote(value, column = nil) #:nodoc: return super unless column case value when Float return super unless value.infinite? && column.type == :datetime "'#{value.to_s.downcase}'" when Numeric return super unless column.sql_type == 'money' # Not truly string input, so doesn't require (or allow) escape string syntax. "'#{value}'" when String case column.sql_type when 'bytea' then "'#{escape_bytea(value)}'" when 'xml' then "xml '#{quote_string(value)}'" when /^bit/ case value when /^[01]*$/ then "B'#{value}'" # Bit-string notation when /^[0-9A-F]*$/i then "X'#{value}'" # Hexadecimal notation end else super end else super end end |
#quote_column_name(name) ⇒ Object
Quotes column names for use in SQL queries.
495 496 497 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 495 def quote_column_name(name) #:nodoc: PGconn.quote_ident(name.to_s) end |
#quote_string(s) ⇒ Object
Quotes strings for use in SQL input.
471 472 473 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 471 def quote_string(s) #:nodoc: @connection.escape(s) end |
#quote_table_name(name) ⇒ Object
Checks the following cases:
-
table_name
-
“table.name”
-
schema_name.table_name
-
schema_name.“table.name”
-
“schema.name”.table_name
-
“schema.name”.“table.name”
483 484 485 486 487 488 489 490 491 492 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 483 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 |
#quoted_date(value) ⇒ Object
Quote date/time values for use in SQL input. Includes microseconds if the value is a Time responding to usec.
501 502 503 504 505 506 507 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 501 def quoted_date(value) #:nodoc: if value.acts_like?(:time) && value.respond_to?(:usec) "#{super}.#{sprintf("%06d", value.usec)}" else super end end |
#reconnect! ⇒ Object
Close then reopen the connection.
351 352 353 354 355 356 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 351 def reconnect! clear_cache! @connection.reset @open_transactions = 0 configure_connection end |
#recreate_database(name, options = {}) ⇒ Object
Drops the database specified on the name
attribute and creates it again using the provided options
.
734 735 736 737 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 734 def recreate_database(name, = {}) #:nodoc: drop_database(name) create_database(name, ) end |
#release_savepoint ⇒ Object
726 727 728 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 726 def release_savepoint execute("RELEASE SAVEPOINT #{current_savepoint_name}") end |
#remove_index!(table_name, index_name) ⇒ Object
:nodoc:
1059 1060 1061 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1059 def remove_index!(table_name, index_name) #:nodoc: execute "DROP INDEX #{quote_table_name(index_name)}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
Renames a column in a table.
1054 1055 1056 1057 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1054 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)}" end |
#rename_index(table_name, old_name, new_name) ⇒ Object
1063 1064 1065 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1063 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) ⇒ 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')
1008 1009 1010 1011 1012 1013 1014 1015 1016 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1008 def rename_table(name, new_name) clear_cache! execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" pk, seq = pk_and_sequence_for(new_name) if seq == "#{name}_#{pk}_seq" new_seq = "#{new_name}_#{pk}_seq" execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}" end end |
#reset! ⇒ Object
358 359 360 361 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 358 def reset! clear_cache! super end |
#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object
Resets the sequence of a table’s primary key to the maximum value.
922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 922 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 |
#result_as_array(res) ⇒ Object
create a 2D array representing the result set
599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 599 def result_as_array(res) #:nodoc: # check if we have any binary column and if they need escaping ftypes = Array.new(res.nfields) do |i| [i, res.ftype(i)] end rows = res.values return rows unless ftypes.any? { |_, x| x == BYTEA_COLUMN_TYPE_OID || x == MONEY_COLUMN_TYPE_OID } typehash = ftypes.group_by { |_, type| type } binaries = typehash[BYTEA_COLUMN_TYPE_OID] || [] monies = typehash[MONEY_COLUMN_TYPE_OID] || [] rows.each do |row| # unescape string passed BYTEA field (OID == 17) binaries.each do |index, _| row[index] = unescape_bytea(row[index]) end # If this is a money type column and there are any currency symbols, # then strip them off. Indeed it would be prettier to do this in # PostgreSQLColumn.string_to_decimal but would break form input # fields that call value_before_type_cast. monies.each do |index, _| data = row[index] # Because money output is formatted according to the locale, there are two # cases to consider (note the decimal separators): # (1) $12,345,678.12 # (2) $12.345.678,12 case data when /^-?\D+[\d,]+\.\d{2}$/ # (1) data.gsub!(/[^-\d.]/, '') when /^-?\D+[\d.]+,\d{2}$/ # (2) data.gsub!(/[^-\d,]/, '').sub!(/,/, '.') end end end end |
#rollback_db_transaction ⇒ Object
Aborts a transaction.
710 711 712 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 710 def rollback_db_transaction execute "ROLLBACK" end |
#rollback_to_savepoint ⇒ Object
722 723 724 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 722 def rollback_to_savepoint execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}") end |
#schema_exists?(name) ⇒ Boolean
Returns true if schema exists.
807 808 809 810 811 812 813 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 807 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_search_path ⇒ Object
Returns the active schema search path.
893 894 895 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 893 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.
885 886 887 888 889 890 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 885 def schema_search_path=(schema_csv) if schema_csv execute("SET search_path TO #{schema_csv}", 'SCHEMA') @schema_search_path = schema_csv end end |
#select_rows(sql, name = nil) ⇒ Object
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
578 579 580 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 578 def select_rows(sql, name = nil) select_raw(sql, name).last end |
#serial_sequence(table, column) ⇒ Object
914 915 916 917 918 919 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 914 def serial_sequence(table, column) result = exec_query(<<-eosql, 'SCHEMA') SELECT pg_get_serial_sequence('#{table}', '#{column}') eosql result.rows.first.first end |
#session_auth=(user) ⇒ Object
Set the authorized user for this session
510 511 512 513 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 510 def session_auth=(user) clear_cache! exec_query "SET SESSION AUTHORIZATION #{user}" end |
#set_standard_conforming_strings ⇒ Object
Enable standard-conforming strings if available.
385 386 387 388 389 390 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 385 def set_standard_conforming_strings old, self. = , 'panic' execute('SET standard_conforming_strings = on', 'SCHEMA') rescue nil ensure self. = old end |
#sql_for_insert(sql, pk, id_value, sequence_name, binds) ⇒ Object
682 683 684 685 686 687 688 689 690 691 692 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 682 def sql_for_insert(sql, pk, id_value, sequence_name, binds) unless pk # Extract the table from the insert sql. Yuck. table_ref = extract_table_ref_from_insert_sql(sql) pk = primary_key(table_ref) if table_ref end sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk [sql, binds] end |
#substitute_at(column, index) ⇒ Object
656 657 658 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 656 def substitute_at(column, index) Arel::Nodes::BindParam.new "$#{index + 1}" end |
#supports_ddl_transactions? ⇒ Boolean
396 397 398 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 396 def supports_ddl_transactions? true end |
#supports_disable_referential_integrity? ⇒ Boolean
REFERENTIAL INTEGRITY ====================================
517 518 519 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 517 def supports_disable_referential_integrity? #:nodoc: true end |
#supports_explain? ⇒ Boolean
Returns true.
406 407 408 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 406 def supports_explain? true end |
#supports_index_sort_order? ⇒ Boolean
248 249 250 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 248 def supports_index_sort_order? true end |
#supports_insert_with_returning? ⇒ Boolean
392 393 394 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 392 def supports_insert_with_returning? true end |
#supports_migrations? ⇒ Boolean
Returns true, since this connection adapter supports migrations.
375 376 377 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 375 def supports_migrations? true end |
#supports_primary_key? ⇒ Boolean
Does PostgreSQL support finding primary key on non-Active Record tables?
380 381 382 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 380 def supports_primary_key? #:nodoc: true end |
#supports_savepoints? ⇒ Boolean
Returns true, since this connection adapter supports savepoints.
401 402 403 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 401 def supports_savepoints? true end |
#supports_statement_cache? ⇒ Boolean
Returns true
, since this connection adapter supports prepared statement caching.
244 245 246 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 244 def supports_statement_cache? true end |
#table_alias_length ⇒ Object
Returns the configured supported identifier length supported by PostgreSQL
411 412 413 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 411 def table_alias_length @table_alias_length ||= query('SHOW max_identifier_length')[0][0].to_i 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)
789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 789 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.
778 779 780 781 782 783 784 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 778 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_cast(value, column) ⇒ Object
458 459 460 461 462 463 464 465 466 467 468 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 458 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) ⇒ Object
Maps logical Rails types to PostgreSQL-specific data types.
1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 1072 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 else super end end |
#unescape_bytea(value) ⇒ Object
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.
425 426 427 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 425 def unescape_bytea(value) @connection.unescape_bytea(value) if value end |
#update_sql(sql, name = nil) ⇒ Object
Executes an UPDATE query and returns the number of affected tuples.
695 696 697 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 695 def update_sql(sql, name = nil) super.cmd_tuples end |