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. -
:allow_concurrency
- If true, use async query methods so Ruby threads don’t deadlock; otherwise, use blocking query methods.
Constant Summary collapse
- ADAPTER_NAME =
'PostgreSQL'.freeze
- NATIVE_DATABASE_TYPES =
{ :primary_key => "serial primary key".freeze, :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" } }
Instance Attribute Summary
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.
-
#add_order_by_for_association_limiting!(sql, options) ⇒ Object
Returns an ORDER BY clause for the passed order option.
-
#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
-
#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.
-
#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(&block) ⇒ Object
:nodoc:.
-
#disconnect! ⇒ Object
Close the connection.
-
#distinct(columns, order_by) ⇒ 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.
-
#execute(sql, name = nil) ⇒ Object
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
-
#indexes(table_name, name = nil) ⇒ Object
Returns the list of all indexes for a table.
-
#initialize(connection, logger, connection_parameters, config) ⇒ PostgreSQLAdapter
constructor
Initializes and connects a PostgreSQL adapter.
-
#insert(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
The ruby-pg driver supports inspecting the transaction status, while the ruby-postgres driver does not.
-
#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 in the postgres driver for better performance.
-
#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) ⇒ Object
SCHEMA STATEMENTS ========================================.
- #release_savepoint ⇒ Object
-
#remove_index(table_name, options = {}) ⇒ Object
Drops an index from a table.
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
Renames a column in a table.
-
#rename_table(name, new_name) ⇒ Object
Renames a table.
-
#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_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.
- #supports_ddl_transactions? ⇒ Boolean
-
#supports_disable_referential_integrity? ⇒ Boolean
REFERENTIAL INTEGRITY ====================================.
- #supports_insert_with_returning? ⇒ Boolean
-
#supports_migrations? ⇒ Boolean
Does PostgreSQL support migrations?.
-
#supports_primary_key? ⇒ Boolean
Does PostgreSQL support finding primary key on non-ActiveRecord tables?.
- #supports_savepoints? ⇒ Boolean
-
#supports_standard_conforming_strings? ⇒ Boolean
Does PostgreSQL support standard conforming strings?.
-
#table_alias_length ⇒ Object
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
-
#tables(name = nil) ⇒ Object
Returns the list of all tables in the schema search path or a specified schema.
-
#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
#current_savepoint_name, #decrement_open_transactions, #increment_open_transactions, #log_info, #open_transactions, #prefetch_primary_key?, #raw_connection, #requires_reloading?, #reset!, #reset_runtime, #supports_count_distinct?, #transaction_joinable=, #verify!
Methods included from QueryCache
#cache, #clear_query_cache, #columns_with_query_cache, dirties_query_cache, included, #select_all_with_query_cache, #uncached
Methods included from Quoting
#quoted_false, #quoted_string_prefix, #quoted_true
Methods included from DatabaseStatements
#add_limit!, #add_limit_offset!, #add_lock!, #case_sensitive_equality_operator, #delete, #empty_insert_statement, #insert_fixture, #limited_update_conditions, #reset_sequence!, #select_all, #select_one, #select_value, #select_values, #transaction, #update
Methods included from SchemaStatements
#add_column_options!, #add_index, #add_timestamps, #assume_migrated_upto_version, #change_table, #create_table, #drop_table, #dump_schema_information, #index_name, #initialize_schema_migrations_table, #remove_column, #remove_timestamps, #structure_dump, #table_alias_for, #table_exists?
Constructor Details
#initialize(connection, logger, connection_parameters, config) ⇒ PostgreSQLAdapter
Initializes and connects a PostgreSQL adapter.
213 214 215 216 217 218 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 213 def initialize(connection, logger, connection_parameters, config) super(connection, logger) @connection_parameters, @config = connection_parameters, config connect end |
Instance Method Details
#active? ⇒ Boolean
Is this connection alive and ready for queries?
221 222 223 224 225 226 227 228 229 230 231 232 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 221 def active? if @connection.respond_to?(:status) @connection.status == PGconn::CONNECTION_OK else # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query @connection.query 'SELECT 1' true end # postgres-pr raises a NoMethodError when querying if no connection is available. rescue PGError, NoMethodError false end |
#adapter_name ⇒ Object
Returns ‘PostgreSQL’ as adapter name for identification purposes.
208 209 210 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 208 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.
838 839 840 841 842 843 844 845 846 847 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 838 def add_column(table_name, column_name, type, = {}) default = [:default] notnull = [:null] == false # Add the column. execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, [:limit], [:precision], [:scale])}") change_column_default(table_name, column_name, default) if () change_column_null(table_name, column_name, false, default) if notnull end |
#add_order_by_for_association_limiting!(sql, options) ⇒ Object
Returns an 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
string as a sub-select and ordering in that query.
934 935 936 937 938 939 940 941 942 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 934 def add_order_by_for_association_limiting!(sql, ) #:nodoc: return sql if [:order].blank? order = [:order].split(',').collect { |s| s.strip }.reject(&:blank?) order.map! { |s| 'DESC' if s =~ /\bdesc$/i } 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 |
#begin_db_transaction ⇒ Object
Begins a transaction.
565 566 567 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 565 def begin_db_transaction execute "BEGIN" end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
Changes the column of a table.
850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 850 def change_column(table_name, column_name, type, = {}) quoted_table_name = quote_table_name(table_name) begin execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, [:limit], [:precision], [:scale])}" rescue ActiveRecord::StatementInvalid => e raise e if postgresql_version > 80000 # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it. begin begin_db_transaction tmp_column_name = "#{column_name}_ar_tmp" add_column(table_name, tmp_column_name, type, ) execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, [:limit], [:precision], [:scale])})" 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, [: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.
876 877 878 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 876 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) ⇒ Object
880 881 882 883 884 885 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 880 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 |
#client_min_messages ⇒ Object
Returns the current client message level.
741 742 743 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 741 def query('SHOW client_min_messages')[0][0] end |
#client_min_messages=(level) ⇒ Object
Set the client message level.
746 747 748 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 746 def (level) execute("SET client_min_messages TO '#{level}'") end |
#columns(table_name, name = nil) ⇒ Object
Returns the list of all column definitions for a table.
703 704 705 706 707 708 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 703 def columns(table_name, name = nil) # Limit, precision, and scale are all handled by the superclass. column_definitions(table_name).collect do |name, type, default, notnull| PostgreSQLColumn.new(name, default, type, notnull == 'f') end end |
#commit_db_transaction ⇒ Object
Commits a transaction.
570 571 572 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 570 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'
613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 613 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
587 588 589 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 587 def create_savepoint execute("SAVEPOINT #{current_savepoint_name}") end |
#current_database ⇒ Object
Returns the current database name.
711 712 713 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 711 def current_database query('select current_database()')[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.
751 752 753 754 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 751 def default_sequence_name(table_name, pk = nil) #:nodoc: default_pk, default_seq = pk_and_sequence_for(table_name) default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq" end |
#disable_referential_integrity(&block) ⇒ Object
:nodoc:
462 463 464 465 466 467 468 469 470 471 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 462 def disable_referential_integrity(&block) #: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
Close the connection.
246 247 248 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 246 def disconnect! @connection.close rescue nil end |
#distinct(columns, order_by) ⇒ 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")
915 916 917 918 919 920 921 922 923 924 925 926 927 928 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 915 def distinct(columns, order_by) #:nodoc: return "DISTINCT #{columns}" if order_by.blank? # Construct a clean list of column names from the ORDER BY clause, removing # any ASC/DESC modifiers order_columns = order_by.split(',').collect { |s| s.split.first } order_columns.delete_if &:blank? order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" } # Return a DISTINCT ON() clause that's distinct on the columns we want but includes # all the required columns for the ORDER BY to work properly. sql = "DISTINCT ON (#{columns}) #{columns}, " sql << order_columns * ', ' end |
#drop_database(name) ⇒ Object
Drops a PostgreSQL database
Example:
drop_database 'matt_development'
640 641 642 643 644 645 646 647 648 649 650 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 640 def drop_database(name) #:nodoc: if postgresql_version >= 80200 execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}" else begin execute "DROP DATABASE #{quote_table_name(name)}" rescue ActiveRecord::StatementInvalid @logger.warn "#{name} database doesn't exist." if @logger end end end |
#encoding ⇒ Object
Returns the current database encoding format.
716 717 718 719 720 721 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 716 def encoding query(<<-end_sql)[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.
301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 301 def escape_bytea(value) if @connection.respond_to?(:escape_bytea) self.class.instance_eval do define_method(:escape_bytea) do |value| @connection.escape_bytea(value) if value end end elsif PGconn.respond_to?(:escape_bytea) self.class.instance_eval do define_method(:escape_bytea) do |value| PGconn.escape_bytea(value) if value end end else self.class.instance_eval do define_method(:escape_bytea) do |value| if value result = '' value.each_byte { |c| result << sprintf('\\\\%03o', c) } result end end end end escape_bytea(value) end |
#execute(sql, name = nil) ⇒ Object
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
549 550 551 552 553 554 555 556 557 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 549 def execute(sql, name = nil) log(sql, name) do if @async @connection.async_exec(sql) else @connection.exec(sql) end end end |
#indexes(table_name, name = nil) ⇒ Object
Returns the list of all indexes for a table.
664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 664 def indexes(table_name, name = nil) schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') result = query(<<-SQL, name) SELECT distinct i.relname, d.indisunique, d.indkey, t.oid FROM pg_class t, pg_class i, pg_index d WHERE i.relkind = 'i' AND d.indexrelid = i.oid AND d.indisprimary = 'f' AND t.oid = d.indrelid AND t.relname = '#{table_name}' AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (#{schemas}) ) ORDER BY i.relname SQL indexes = [] indexes = result.map do |row| index_name = row[0] unique = row[1] == 't' indkey = row[2].split(" ") oid = row[3] columns = query(<<-SQL, "Columns for index #{row[0]} on #{table_name}").inject({}) {|attlist, r| attlist[r[1]] = r[0]; attlist} SELECT a.attname, a.attnum FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}) SQL column_names = indkey.map {|attnum| columns[attnum] } IndexDefinition.new(table_name, index_name, unique, column_names) end indexes end |
#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
Executes an INSERT query and returns the new record’s ID
482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 482 def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) # Extract the table from the insert sql. Yuck. table = sql.split(" ", 4)[2].gsub('"', '') # Try an insert with 'returning id' if available (PG >= 8.2) if supports_insert_with_returning? pk, sequence_name = *pk_and_sequence_for(table) unless pk if pk id = select_value("#{sql} RETURNING #{quote_column_name(pk)}") clear_query_cache return id end end # Otherwise, insert then grab last_insert_id. if insert_id = super insert_id else # If neither pk nor sequence name is given, look them up. unless pk || sequence_name pk, sequence_name = *pk_and_sequence_for(table) end # If a pk is given, fallback to default sequence name. # Don't fetch last insert id for a table without a pk. if pk && sequence_name ||= default_sequence_name(table, pk) last_insert_id(table, sequence_name) end end end |
#native_database_types ⇒ Object
:nodoc:
250 251 252 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 250 def native_database_types #:nodoc: NATIVE_DATABASE_TYPES end |
#outside_transaction? ⇒ Boolean
The ruby-pg driver supports inspecting the transaction status, while the ruby-postgres driver does not.
582 583 584 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 582 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.
777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 777 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, 'PK and serial sequence')[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? # If that fails, try parsing the primary key's default value. # Support the 7.x and 8.0 nextval('foo'::text) as well as # the 8.1+ nextval('foo'::regclass). result = query(<<-end_sql, 'PK and custom sequence')[0] SELECT attr.attname, CASE WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN substr(split_part(def.adsrc, '''', 2), strpos(split_part(def.adsrc, '''', 2), '.')+1) ELSE split_part(def.adsrc, '''', 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 def.adsrc ~* 'nextval' end_sql end # [primary_key, sequence] [result.first, result.last] rescue nil end |
#primary_key(table) ⇒ Object
Returns just a table’s primary key
826 827 828 829 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 826 def primary_key(table) pk_and_sequence = pk_and_sequence_for(table) pk_and_sequence && pk_and_sequence.first end |
#query(sql, name = nil) ⇒ Object
Queries the database and returns the results in an Array-like object
536 537 538 539 540 541 542 543 544 545 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 536 def query(sql, name = nil) #:nodoc: log(sql, name) do if @async res = @connection.async_exec(sql) else res = @connection.exec(sql) end return result_as_array(res) end end |
#quote(value, column = nil) ⇒ Object
Quotes PostgreSQL-specific data types for SQL input.
375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 375 def quote(value, column = nil) #:nodoc: if value.kind_of?(String) && column && column.type == :binary "#{quoted_string_prefix}'#{escape_bytea(value)}'" elsif value.kind_of?(String) && column && column.sql_type =~ /^xml$/ "xml E'#{quote_string(value)}'" elsif value.kind_of?(Numeric) && column && column.sql_type =~ /^money$/ # Not truly string input, so doesn't require (or allow) escape string syntax. "'#{value.to_s}'" elsif value.kind_of?(String) && column && column.sql_type =~ /^bit/ case value when /^[01]*$/ "B'#{value}'" # Bit-string notation when /^[0-9A-F]*$/i "X'#{value}'" # Hexadecimal notation end else super end end |
#quote_column_name(name) ⇒ Object
Quotes column names for use in SQL queries.
439 440 441 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 439 def quote_column_name(name) #:nodoc: PGconn.quote_ident(name.to_s) end |
#quote_string(s) ⇒ Object
Quotes strings for use in SQL input in the postgres driver for better performance.
396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 396 def quote_string(s) #:nodoc: if @connection.respond_to?(:escape) self.class.instance_eval do define_method(:quote_string) do |s| @connection.escape(s) end end elsif PGconn.respond_to?(:escape) self.class.instance_eval do define_method(:quote_string) do |s| PGconn.escape(s) end end else # There are some incorrectly compiled postgres drivers out there # that don't define PGconn.escape. self.class.instance_eval do remove_method(:quote_string) end end quote_string(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”
427 428 429 430 431 432 433 434 435 436 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 427 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.
445 446 447 448 449 450 451 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 445 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.
235 236 237 238 239 240 241 242 243 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 235 def reconnect! if @connection.respond_to?(:reset) @connection.reset configure_connection else disconnect! connect end end |
#recreate_database(name) ⇒ Object
SCHEMA STATEMENTS ========================================
601 602 603 604 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 601 def recreate_database(name) #:nodoc: drop_database(name) create_database(name) end |
#release_savepoint ⇒ Object
595 596 597 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 595 def release_savepoint execute("RELEASE SAVEPOINT #{current_savepoint_name}") end |
#remove_index(table_name, options = {}) ⇒ Object
Drops an index from a table.
893 894 895 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 893 def remove_index(table_name, = {}) execute "DROP INDEX #{quote_table_name(index_name(table_name, ))}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
Renames a column in a table.
888 889 890 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 888 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_table(name, new_name) ⇒ Object
Renames a table.
832 833 834 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 832 def rename_table(name, new_name) execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" end |
#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object
Resets the sequence of a table’s primary key to the maximum value.
757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 757 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 pk if sequence quoted_sequence = quote_column_name(sequence) select_value <<-end_sql, 'Reset sequence' 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 else @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger end end end |
#result_as_array(res) ⇒ Object
create a 2D array representing the result set
514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 514 def result_as_array(res) #:nodoc: # check if we have any binary column and if they need escaping unescape_col = [] for j in 0...res.nfields do # unescape string passed BYTEA field (OID == 17) unescape_col << ( res.ftype(j)==17 ) end ary = [] for i in 0...res.ntuples do ary << [] for j in 0...res.nfields do data = res.getvalue(i,j) data = unescape_bytea(data) if unescape_col[j] and data.is_a?(String) ary[i] << data end end return ary end |
#rollback_db_transaction ⇒ Object
Aborts a transaction.
575 576 577 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 575 def rollback_db_transaction execute "ROLLBACK" end |
#rollback_to_savepoint ⇒ Object
591 592 593 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 591 def rollback_to_savepoint execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}") end |
#schema_search_path ⇒ Object
Returns the active schema search path.
736 737 738 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 736 def schema_search_path @schema_search_path ||= query('SHOW search_path')[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.
728 729 730 731 732 733 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 728 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) ⇒ Object
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
477 478 479 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 477 def select_rows(sql, name = nil) select_raw(sql, name).last end |
#supports_ddl_transactions? ⇒ Boolean
284 285 286 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 284 def supports_ddl_transactions? true end |
#supports_disable_referential_integrity? ⇒ Boolean
REFERENTIAL INTEGRITY ====================================
455 456 457 458 459 460 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 455 def supports_disable_referential_integrity?() #:nodoc: version = query("SHOW server_version")[0][0].split('.') (version[0].to_i >= 8 && version[1].to_i >= 1) ? true : false rescue return false end |
#supports_insert_with_returning? ⇒ Boolean
280 281 282 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 280 def supports_insert_with_returning? postgresql_version >= 80200 end |
#supports_migrations? ⇒ Boolean
Does PostgreSQL support migrations?
255 256 257 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 255 def supports_migrations? true end |
#supports_primary_key? ⇒ Boolean
Does PostgreSQL support finding primary key on non-ActiveRecord tables?
260 261 262 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 260 def supports_primary_key? #:nodoc: true end |
#supports_savepoints? ⇒ Boolean
288 289 290 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 288 def supports_savepoints? true end |
#supports_standard_conforming_strings? ⇒ Boolean
Does PostgreSQL support standard conforming strings?
265 266 267 268 269 270 271 272 273 274 275 276 277 278 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 265 def supports_standard_conforming_strings? # Temporarily set the client message level above error to prevent unintentional # error messages in the logs when working on a PostgreSQL database server that # does not support standard conforming strings. = self. = 'panic' # postgres-pr does not raise an exception when client_min_messages is set higher # than error and "SHOW standard_conforming_strings" fails, but returns an empty # PGresult instead. has_support = query('SHOW standard_conforming_strings')[0][0] rescue false self. = has_support end |
#table_alias_length ⇒ Object
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
294 295 296 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 294 def table_alias_length @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63) end |
#tables(name = nil) ⇒ Object
Returns the list of all tables in the schema search path or a specified schema.
654 655 656 657 658 659 660 661 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 654 def tables(name = nil) schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') query(<<-SQL, name).map { |row| row[0] } SELECT tablename FROM pg_tables WHERE schemaname IN (#{schemas}) SQL end |
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
Maps logical Rails types to PostgreSQL-specific data types.
898 899 900 901 902 903 904 905 906 907 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 898 def type_to_sql(type, limit = nil, precision = nil, scale = nil) return super unless type.to_s == 'integer' case limit when 1..2; 'smallint' when 3..4, nil; '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) ⇒ 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.
331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 331 def unescape_bytea(value) # In each case, check if the value actually is escaped PostgreSQL bytea output # or an unescaped Active Record attribute that was just written. if PGconn.respond_to?(:unescape_bytea) self.class.instance_eval do define_method(:unescape_bytea) do |value| if value =~ /\\\d{3}/ PGconn.unescape_bytea(value) else value end end end else self.class.instance_eval do define_method(:unescape_bytea) do |value| if value =~ /\\\d{3}/ result = '' i, max = 0, value.size while i < max char = value[i] if char == ?\\ if value[i+1] == ?\\ char = ?\\ i += 1 else char = value[i+1..i+3].oct i += 3 end end result << char i += 1 end result else value end end end end unescape_bytea(value) end |
#update_sql(sql, name = nil) ⇒ Object
Executes an UPDATE query and returns the number of affected tuples.
560 561 562 |
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 560 def update_sql(sql, name = nil) super.cmd_tuples end |