Class | ActiveRecord::ConnectionAdapters::PostgreSQLAdapter |
In: |
vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
|
Parent: | AbstractAdapter |
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:
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" } |
Initializes and connects a PostgreSQL adapter.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 213 213: def initialize(connection, logger, connection_parameters, config) 214: super(connection, logger) 215: @connection_parameters, @config = connection_parameters, config 216: 217: connect 218: end
Is this connection alive and ready for queries?
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 221 221: def active? 222: if @connection.respond_to?(:status) 223: @connection.status == PGconn::CONNECTION_OK 224: else 225: # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query 226: @connection.query 'SELECT 1' 227: true 228: end 229: # postgres-pr raises a NoMethodError when querying if no connection is available. 230: rescue PGError, NoMethodError 231: false 232: end
Returns ‘PostgreSQL’ as adapter name for identification purposes.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 208 208: def adapter_name 209: ADAPTER_NAME 210: end
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 845 845: def add_column(table_name, column_name, type, options = {}) 846: default = options[:default] 847: notnull = options[:null] == false 848: 849: # Add the column. 850: execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}") 851: 852: change_column_default(table_name, column_name, default) if options_include_default?(options) 853: change_column_null(table_name, column_name, false, default) if notnull 854: end
Begins a transaction.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 565 565: def begin_db_transaction 566: execute "BEGIN" 567: end
Changes the column of a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 857 857: def change_column(table_name, column_name, type, options = {}) 858: quoted_table_name = quote_table_name(table_name) 859: 860: begin 861: execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" 862: rescue ActiveRecord::StatementInvalid => e 863: raise e if postgresql_version > 80000 864: # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it. 865: begin 866: begin_db_transaction 867: tmp_column_name = "#{column_name}_ar_tmp" 868: add_column(table_name, tmp_column_name, type, options) 869: execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})" 870: remove_column(table_name, column_name) 871: rename_column(table_name, tmp_column_name, column_name) 872: commit_db_transaction 873: rescue 874: rollback_db_transaction 875: end 876: end 877: 878: change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) 879: change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) 880: end
Changes the default value of a table column.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 883 883: def change_column_default(table_name, column_name, default) 884: execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" 885: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 887 887: def change_column_null(table_name, column_name, null, default = nil) 888: unless null || default.nil? 889: execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") 890: end 891: execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") 892: end
Returns the current client message level.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 745 745: def client_min_messages 746: query('SHOW client_min_messages')[0][0] 747: end
Set the client message level.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 750 750: def client_min_messages=(level) 751: execute("SET client_min_messages TO '#{level}'") 752: end
Returns the list of all column definitions for a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 706 706: def columns(table_name, name = nil) 707: # Limit, precision, and scale are all handled by the superclass. 708: column_definitions(table_name).collect do |name, type, default, notnull| 709: PostgreSQLColumn.new(name, default, type, notnull == 'f') 710: end 711: end
Commits a transaction.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 570 570: def commit_db_transaction 571: execute "COMMIT" 572: end
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'
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 613 613: def create_database(name, options = {}) 614: options = options.reverse_merge(:encoding => "utf8") 615: 616: option_string = options.symbolize_keys.sum do |key, value| 617: case key 618: when :owner 619: " OWNER = \"#{value}\"" 620: when :template 621: " TEMPLATE = \"#{value}\"" 622: when :encoding 623: " ENCODING = '#{value}'" 624: when :tablespace 625: " TABLESPACE = \"#{value}\"" 626: when :connection_limit 627: " CONNECTION LIMIT = #{value}" 628: else 629: "" 630: end 631: end 632: 633: execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" 634: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 587 587: def create_savepoint 588: execute("SAVEPOINT #{current_savepoint_name}") 589: end
Returns the current database name.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 714 714: def current_database 715: query('select current_database()')[0][0] 716: end
Close the connection.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 246 246: def disconnect! 247: @connection.close rescue nil 248: end
Returns the current database encoding format.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 719 719: def encoding 720: query("SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database\nWHERE pg_database.datname LIKE '\#{current_database}'\n")[0][0] 721: end
Escapes binary strings for bytea input to the database.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 301 301: def escape_bytea(value) 302: if @connection.respond_to?(:escape_bytea) 303: self.class.instance_eval do 304: define_method(:escape_bytea) do |value| 305: @connection.escape_bytea(value) if value 306: end 307: end 308: elsif PGconn.respond_to?(:escape_bytea) 309: self.class.instance_eval do 310: define_method(:escape_bytea) do |value| 311: PGconn.escape_bytea(value) if value 312: end 313: end 314: else 315: self.class.instance_eval do 316: define_method(:escape_bytea) do |value| 317: if value 318: result = '' 319: value.each_byte { |c| result << sprintf('\\\\%03o', c) } 320: result 321: end 322: end 323: end 324: end 325: escape_bytea(value) 326: end
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 549 549: def execute(sql, name = nil) 550: log(sql, name) do 551: if @async 552: @connection.async_exec(sql) 553: else 554: @connection.exec(sql) 555: end 556: end 557: end
Returns the list of all indexes for a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 665 665: def indexes(table_name, name = nil) 666: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') 667: result = query("SELECT distinct i.relname, d.indisunique, d.indkey, t.oid\nFROM pg_class t, pg_class i, pg_index d\nWHERE i.relkind = 'i'\nAND d.indexrelid = i.oid\nAND d.indisprimary = 'f'\nAND t.oid = d.indrelid\nAND t.relname = '\#{table_name}'\nAND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (\#{schemas}) )\nORDER BY i.relname\n", name) 668: 669: 670: indexes = [] 671: 672: indexes = result.map do |row| 673: index_name = row[0] 674: unique = row[1] == 't' 675: indkey = row[2].split(" ") 676: oid = row[3] 677: 678: columns = query("SELECT a.attname, a.attnum\nFROM pg_attribute a\nWHERE a.attrelid = \#{oid}\nAND a.attnum IN (\#{indkey.join(\",\")})\n", "Columns for index #{row[0]} on #{table_name}").inject({}) {|attlist, r| attlist[r[1]] = r[0]; attlist} 679: 680: column_names = indkey.map {|attnum| columns[attnum] } 681: IndexDefinition.new(table_name, index_name, unique, column_names) 682: 683: end 684: 685: indexes 686: end
Executes an INSERT query and returns the new record‘s ID
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 482 482: def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 483: # Extract the table from the insert sql. Yuck. 484: table = sql.split(" ", 4)[2].gsub('"', '') 485: 486: # Try an insert with 'returning id' if available (PG >= 8.2) 487: if supports_insert_with_returning? 488: pk, sequence_name = *pk_and_sequence_for(table) unless pk 489: if pk 490: id = select_value("#{sql} RETURNING #{quote_column_name(pk)}") 491: clear_query_cache 492: return id 493: end 494: end 495: 496: # Otherwise, insert then grab last_insert_id. 497: if insert_id = super 498: insert_id 499: else 500: # If neither pk nor sequence name is given, look them up. 501: unless pk || sequence_name 502: pk, sequence_name = *pk_and_sequence_for(table) 503: end 504: 505: # If a pk is given, fallback to default sequence name. 506: # Don't fetch last insert id for a table without a pk. 507: if pk && sequence_name ||= default_sequence_name(table, pk) 508: last_insert_id(table, sequence_name) 509: end 510: end 511: end
The ruby-pg driver supports inspecting the transaction status, while the ruby-postgres driver does not.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 582 582: def outside_transaction? 583: @connection.transaction_status == PGconn::PQTRANS_IDLE 584: end
Returns just a table‘s primary key
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 833 833: def primary_key(table) 834: pk_and_sequence = pk_and_sequence_for(table) 835: pk_and_sequence && pk_and_sequence.first 836: end
Checks the following cases:
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 427 427: def quote_table_name(name) 428: schema, name_part = extract_pg_identifier_from_name(name.to_s) 429: 430: unless name_part 431: quote_column_name(schema) 432: else 433: table_name, name_part = extract_pg_identifier_from_name(name_part) 434: "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" 435: end 436: end
Close then reopen the connection.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 235 235: def reconnect! 236: if @connection.respond_to?(:reset) 237: @connection.reset 238: configure_connection 239: else 240: disconnect! 241: connect 242: end 243: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 595 595: def release_savepoint 596: execute("RELEASE SAVEPOINT #{current_savepoint_name}") 597: end
Drops an index from a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 900 900: def remove_index(table_name, options = {}) 901: execute "DROP INDEX #{quote_table_name(index_name(table_name, options))}" 902: end
Renames a column in a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 895 895: def rename_column(table_name, column_name, new_column_name) 896: execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" 897: end
Renames a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 839 839: def rename_table(name, new_name) 840: execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" 841: end
Aborts a transaction.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 575 575: def rollback_db_transaction 576: execute "ROLLBACK" 577: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 591 591: def rollback_to_savepoint 592: execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}") 593: end
Returns the active schema search path.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 740 740: def schema_search_path 741: @schema_search_path ||= query('SHOW search_path')[0][0] 742: end
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.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 732 732: def schema_search_path=(schema_csv) 733: if schema_csv 734: execute "SET search_path TO #{schema_csv}" 735: @schema_search_path = schema_csv 736: end 737: end
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 477 477: def select_rows(sql, name = nil) 478: select_raw(sql, name).last 479: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 284 284: def supports_ddl_transactions? 285: true 286: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 280 280: def supports_insert_with_returning? 281: postgresql_version >= 80200 282: end
Does PostgreSQL support migrations?
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 255 255: def supports_migrations? 256: true 257: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 288 288: def supports_savepoints? 289: true 290: end
Does PostgreSQL support standard conforming strings?
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 265 265: def supports_standard_conforming_strings? 266: # Temporarily set the client message level above error to prevent unintentional 267: # error messages in the logs when working on a PostgreSQL database server that 268: # does not support standard conforming strings. 269: client_min_messages_old = client_min_messages 270: self.client_min_messages = 'panic' 271: 272: # postgres-pr does not raise an exception when client_min_messages is set higher 273: # than error and "SHOW standard_conforming_strings" fails, but returns an empty 274: # PGresult instead. 275: has_support = query('SHOW standard_conforming_strings')[0][0] rescue false 276: self.client_min_messages = client_min_messages_old 277: has_support 278: end
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 294 294: def table_alias_length 295: @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63) 296: end
Returns the list of all tables in the schema search path or a specified schema.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 654 654: def tables(name = nil) 655: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') 656: query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] } 657: end
Maps logical Rails types to PostgreSQL-specific data types.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 905 905: def type_to_sql(type, limit = nil, precision = nil, scale = nil) 906: return super unless type.to_s == 'integer' 907: 908: case limit 909: when 1..2; 'smallint' 910: when 3..4, nil; 'integer' 911: when 5..8; 'bigint' 912: else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") 913: end 914: end
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.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 331 331: def unescape_bytea(value) 332: # In each case, check if the value actually is escaped PostgreSQL bytea output 333: # or an unescaped Active Record attribute that was just written. 334: if PGconn.respond_to?(:unescape_bytea) 335: self.class.instance_eval do 336: define_method(:unescape_bytea) do |value| 337: if value =~ /\\\d{3}/ 338: PGconn.unescape_bytea(value) 339: else 340: value 341: end 342: end 343: end 344: else 345: self.class.instance_eval do 346: define_method(:unescape_bytea) do |value| 347: if value =~ /\\\d{3}/ 348: result = '' 349: i, max = 0, value.size 350: while i < max 351: char = value[i] 352: if char == ?\\ 353: if value[i+1] == ?\\ 354: char = ?\\ 355: i += 1 356: else 357: char = value[i+1..i+3].oct 358: i += 3 359: end 360: end 361: result << char 362: i += 1 363: end 364: result 365: else 366: value 367: end 368: end 369: end 370: end 371: unescape_bytea(value) 372: end
Executes an UPDATE query and returns the number of affected tuples.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 560 560: def update_sql(sql, name = nil) 561: super.cmd_tuples 562: end
Returns the version of the connected PostgreSQL version.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 953 953: def postgresql_version 954: @postgresql_version ||= 955: if @connection.respond_to?(:server_version) 956: @connection.server_version 957: else 958: # Mimic PGconn.server_version behavior 959: begin 960: query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/ 961: ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i 962: rescue 963: 0 964: end 965: end 966: end