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" } | ||
PQTRANS_IDLE | = | defined?(PGconn::PQTRANS_IDLE) ? PGconn::PQTRANS_IDLE : 0 | ruby-pg defines Ruby constants for transaction status, ruby-postgres does not. |
Initializes and connects a PostgreSQL adapter.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 206 206: def initialize(connection, logger, connection_parameters, config) 207: super(connection, logger) 208: @connection_parameters, @config = connection_parameters, config 209: 210: connect 211: end
Is this connection alive and ready for queries?
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 214 214: def active? 215: if @connection.respond_to?(:status) 216: @connection.status == PGconn::CONNECTION_OK 217: else 218: # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query 219: @connection.query 'SELECT 1' 220: true 221: end 222: # postgres-pr raises a NoMethodError when querying if no connection is available. 223: rescue PGError, NoMethodError 224: false 225: end
Returns ‘PostgreSQL’ as adapter name for identification purposes.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 201 201: def adapter_name 202: ADAPTER_NAME 203: 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 807 807: def add_column(table_name, column_name, type, options = {}) 808: default = options[:default] 809: notnull = options[:null] == false 810: 811: # Add the column. 812: 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])}") 813: 814: change_column_default(table_name, column_name, default) if options_include_default?(options) 815: change_column_null(table_name, column_name, false, default) if notnull 816: end
Begins a transaction.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 518 518: def begin_db_transaction 519: execute "BEGIN" 520: end
Changes the column of a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 819 819: def change_column(table_name, column_name, type, options = {}) 820: quoted_table_name = quote_table_name(table_name) 821: 822: begin 823: execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" 824: rescue ActiveRecord::StatementInvalid => e 825: raise e if postgresql_version > 80000 826: # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it. 827: begin 828: begin_db_transaction 829: tmp_column_name = "#{column_name}_ar_tmp" 830: add_column(table_name, tmp_column_name, type, options) 831: 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])})" 832: remove_column(table_name, column_name) 833: rename_column(table_name, tmp_column_name, column_name) 834: commit_db_transaction 835: rescue 836: rollback_db_transaction 837: end 838: end 839: 840: change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) 841: change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) 842: end
Changes the default value of a table column.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 845 845: def change_column_default(table_name, column_name, default) 846: execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" 847: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 849 849: def change_column_null(table_name, column_name, null, default = nil) 850: unless null || default.nil? 851: execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") 852: end 853: execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") 854: end
Returns the current client message level.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 713 713: def client_min_messages 714: query('SHOW client_min_messages')[0][0] 715: end
Set the client message level.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 718 718: def client_min_messages=(level) 719: execute("SET client_min_messages TO '#{level}'") 720: end
Returns the list of all column definitions for a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 674 674: def columns(table_name, name = nil) 675: # Limit, precision, and scale are all handled by the superclass. 676: column_definitions(table_name).collect do |name, type, default, notnull| 677: PostgreSQLColumn.new(name, default, type, notnull == 'f') 678: end 679: end
Commits a transaction.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 523 523: def commit_db_transaction 524: execute "COMMIT" 525: 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 585 585: def create_database(name, options = {}) 586: options = options.reverse_merge(:encoding => "utf8") 587: 588: option_string = options.symbolize_keys.sum do |key, value| 589: case key 590: when :owner 591: " OWNER = \"#{value}\"" 592: when :template 593: " TEMPLATE = \"#{value}\"" 594: when :encoding 595: " ENCODING = '#{value}'" 596: when :tablespace 597: " TABLESPACE = \"#{value}\"" 598: when :connection_limit 599: " CONNECTION LIMIT = #{value}" 600: else 601: "" 602: end 603: end 604: 605: execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" 606: end
Returns the current database name.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 682 682: def current_database 683: query('select current_database()')[0][0] 684: end
Close the connection.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 239 239: def disconnect! 240: @connection.close rescue nil 241: end
Returns the current database encoding format.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 687 687: def encoding 688: query("SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database\nWHERE pg_database.datname LIKE '\#{current_database}'\n")[0][0] 689: end
Escapes binary strings for bytea input to the database.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 285 285: def escape_bytea(value) 286: if PGconn.respond_to?(:escape_bytea) 287: self.class.instance_eval do 288: define_method(:escape_bytea) do |value| 289: PGconn.escape_bytea(value) if value 290: end 291: end 292: else 293: self.class.instance_eval do 294: define_method(:escape_bytea) do |value| 295: if value 296: result = '' 297: value.each_byte { |c| result << sprintf('\\\\%03o', c) } 298: result 299: end 300: end 301: end 302: end 303: escape_bytea(value) 304: 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 502 502: def execute(sql, name = nil) 503: log(sql, name) do 504: if @async 505: @connection.async_exec(sql) 506: else 507: @connection.exec(sql) 508: end 509: end 510: end
Returns the list of all indexes for a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 637 637: def indexes(table_name, name = nil) 638: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') 639: result = query("SELECT distinct i.relname, d.indisunique, a.attname\nFROM pg_class t, pg_class i, pg_index d, pg_attribute a\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}) )\nAND a.attrelid = t.oid\nAND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum\nOR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum\nOR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum\nOR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum\nOR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )\nORDER BY i.relname\n", name) 640: 641: current_index = nil 642: indexes = [] 643: 644: result.each do |row| 645: if current_index != row[0] 646: indexes << IndexDefinition.new(table_name, row[0], row[1] == "t", []) 647: current_index = row[0] 648: end 649: 650: indexes.last.columns << row[2] 651: end 652: 653: indexes 654: 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 435 435: def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 436: # Extract the table from the insert sql. Yuck. 437: table = sql.split(" ", 4)[2].gsub('"', '') 438: 439: # Try an insert with 'returning id' if available (PG >= 8.2) 440: if supports_insert_with_returning? 441: pk, sequence_name = *pk_and_sequence_for(table) unless pk 442: if pk 443: id = select_value("#{sql} RETURNING #{quote_column_name(pk)}") 444: clear_query_cache 445: return id 446: end 447: end 448: 449: # Otherwise, insert then grab last_insert_id. 450: if insert_id = super 451: insert_id 452: else 453: # If neither pk nor sequence name is given, look them up. 454: unless pk || sequence_name 455: pk, sequence_name = *pk_and_sequence_for(table) 456: end 457: 458: # If a pk is given, fallback to default sequence name. 459: # Don't fetch last insert id for a table without a pk. 460: if pk && sequence_name ||= default_sequence_name(table, pk) 461: last_insert_id(table, sequence_name) 462: end 463: end 464: end
Close then reopen the connection.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 228 228: def reconnect! 229: if @connection.respond_to?(:reset) 230: @connection.reset 231: configure_connection 232: else 233: disconnect! 234: connect 235: end 236: end
Drops an index from a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 862 862: def remove_index(table_name, options = {}) 863: execute "DROP INDEX #{index_name(table_name, options)}" 864: end
Renames a column in a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 857 857: def rename_column(table_name, column_name, new_column_name) 858: execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" 859: end
Renames a table.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 801 801: def rename_table(name, new_name) 802: execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" 803: end
Aborts a transaction.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 528 528: def rollback_db_transaction 529: execute "ROLLBACK" 530: end
Returns the active schema search path.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 708 708: def schema_search_path 709: @schema_search_path ||= query('SHOW search_path')[0][0] 710: 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 700 700: def schema_search_path=(schema_csv) 701: if schema_csv 702: execute "SET search_path TO #{schema_csv}" 703: @schema_search_path = schema_csv 704: end 705: 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 430 430: def select_rows(sql, name = nil) 431: select_raw(sql, name).last 432: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 272 272: def supports_ddl_transactions? 273: true 274: end
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 268 268: def supports_insert_with_returning? 269: postgresql_version >= 80200 270: end
Does PostgreSQL support migrations?
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 248 248: def supports_migrations? 249: true 250: end
Does PostgreSQL support standard conforming strings?
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 253 253: def supports_standard_conforming_strings? 254: # Temporarily set the client message level above error to prevent unintentional 255: # error messages in the logs when working on a PostgreSQL database server that 256: # does not support standard conforming strings. 257: client_min_messages_old = client_min_messages 258: self.client_min_messages = 'panic' 259: 260: # postgres-pr does not raise an exception when client_min_messages is set higher 261: # than error and "SHOW standard_conforming_strings" fails, but returns an empty 262: # PGresult instead. 263: has_support = query('SHOW standard_conforming_strings')[0][0] rescue false 264: self.client_min_messages = client_min_messages_old 265: has_support 266: 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 278 278: def table_alias_length 279: @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63) 280: 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 626 626: def tables(name = nil) 627: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') 628: query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] } 629: end
Wrap a block in a transaction. Returns result of block.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 542 542: def transaction(start_db_transaction = true) 543: transaction_open = false 544: begin 545: if block_given? 546: if start_db_transaction 547: begin_db_transaction 548: transaction_open = true 549: end 550: yield 551: end 552: rescue Exception => database_transaction_rollback 553: if transaction_open && transaction_active? 554: transaction_open = false 555: rollback_db_transaction 556: end 557: raise unless database_transaction_rollback.is_a? ActiveRecord::Rollback 558: end 559: ensure 560: if transaction_open && transaction_active? 561: begin 562: commit_db_transaction 563: rescue Exception => database_transaction_rollback 564: rollback_db_transaction 565: raise 566: end 567: end 568: end
Check whether a transaction is active.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 537 537: def transaction_active? 538: @connection.transaction_status != PQTRANS_IDLE 539: end
Maps logical Rails types to PostgreSQL-specific data types.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 867 867: def type_to_sql(type, limit = nil, precision = nil, scale = nil) 868: return super unless type.to_s == 'integer' 869: 870: case limit 871: when 1..2; 'smallint' 872: when 3..4, nil; 'integer' 873: when 5..8; 'bigint' 874: else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") 875: end 876: 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 309 309: def unescape_bytea(value) 310: # In each case, check if the value actually is escaped PostgreSQL bytea output 311: # or an unescaped Active Record attribute that was just written. 312: if PGconn.respond_to?(:unescape_bytea) 313: self.class.instance_eval do 314: define_method(:unescape_bytea) do |value| 315: if value =~ /\\\d{3}/ 316: PGconn.unescape_bytea(value) 317: else 318: value 319: end 320: end 321: end 322: else 323: self.class.instance_eval do 324: define_method(:unescape_bytea) do |value| 325: if value =~ /\\\d{3}/ 326: result = '' 327: i, max = 0, value.size 328: while i < max 329: char = value[i] 330: if char == ?\\ 331: if value[i+1] == ?\\ 332: char = ?\\ 333: i += 1 334: else 335: char = value[i+1..i+3].oct 336: i += 3 337: end 338: end 339: result << char 340: i += 1 341: end 342: result 343: else 344: value 345: end 346: end 347: end 348: end 349: unescape_bytea(value) 350: 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 513 513: def update_sql(sql, name = nil) 514: super.cmd_tuples 515: end
Returns the version of the connected PostgreSQL version.
# File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 915 915: def postgresql_version 916: @postgresql_version ||= 917: if @connection.respond_to?(:server_version) 918: @connection.server_version 919: else 920: # Mimic PGconn.server_version behavior 921: begin 922: query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/ 923: ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i 924: rescue 925: 0 926: end 927: end 928: end