· 4 years ago · Mar 19, 2021, 06:54 AM
1airck-0.6.1/lib/active_record/connection_adapters/postgresql_adapter.rb
2
3require 'active_record/connection_adapters/abstract_adapter'
4require 'active_support/core_ext/object/blank'
5require 'active_record/connection_adapters/statement_pool'
6require 'arel/visitors/bind_visitor'
7
8# Make sure we're using pg high enough for PGResult#values
9gem 'pg', '~> 0.11'
10require 'pg'
11
12module ActiveRecord
13 class Base
14 # Establishes a connection to the database that's used by all Active Record objects
15 def self.postgresql_connection(config) # :nodoc:
16 config = config.symbolize_keys
17 host = config[:host]
18 port = config[:port] || 5432
19 username = config[:username].to_s if config[:username]
20 password = config[:password].to_s if config[:password]
21
22 if config.key?(:database)
23 database = config[:database]
24 else
25 raise ArgumentError, "No database specified. Missing argument: database."
26 end
27
28 # The postgres drivers don't allow the creation of an unconnected PGconn object,
29 # so just pass a nil connection object for the time being.
30 ConnectionAdapters::PostgreSQLAdapter.new(nil, logger, [host, port, nil, nil, database, username, password], config)
31 end
32 end
33
34 module ConnectionAdapters
35 # PostgreSQL-specific extensions to column definitions in a table.
36 class PostgreSQLColumn < Column #:nodoc:
37 # Instantiates a new PostgreSQL column definition in a table.
38 def initialize(name, default, sql_type = nil, null = true)
39 super(name, self.class.extract_value_from_default(default), sql_type, null)
40 end
41
42 # :stopdoc:
43 class << self
44 attr_accessor :money_precision
45 def string_to_time(string)
46 return string unless String === string
47
48 case string
49 when 'infinity' then 1.0 / 0.0
50 when '-infinity' then -1.0 / 0.0
51 else
52 super
53 end
54 end
55 end
56 # :startdoc:
57
58 private
59 def extract_limit(sql_type)
60 case sql_type
61 when /^bigint/i; 8
62 when /^smallint/i; 2
63 else super
64 end
65 end
66
67 # Extracts the scale from PostgreSQL-specific data types.
68 def extract_scale(sql_type)
69 # Money type has a fixed scale of 2.
70 sql_type =~ /^money/ ? 2 : super
71 end
72
73 # Extracts the precision from PostgreSQL-specific data types.
74 def extract_precision(sql_type)
75 if sql_type == 'money'
76 self.class.money_precision
77 else
78 super
79 end
80 end
81
82 # Maps PostgreSQL-specific data types to logical Rails types.
83 def simplified_type(field_type)
84 case field_type
85 # Numeric and monetary types
86 when /^(?:real|double precision)$/
87 :float
88 # Monetary types
89 when 'money'
90 :decimal
91 # Character types
92 when /^(?:character varying|bpchar)(?:\(\d+\))?$/
93 :string
94 # Binary data types
95 when 'bytea'
96 :binary
97 # Date/time types
98 when /^timestamp with(?:out)? time zone$/
99 :datetime
100 when 'interval'
101 :string
102 # Geometric types
103 when /^(?:point|line|lseg|box|"?path"?|polygon|circle)$/
104 :string
105 # Network address types
106 when /^(?:cidr|inet|macaddr)$/
107 :string
108 # Bit strings
109 when /^bit(?: varying)?(?:\(\d+\))?$/
110 :string
111 # XML type
112 when 'xml'
113 :xml
114 # tsvector type
115 when 'tsvector'
116 :tsvector
117 # Arrays
118 when /^\D+\[\]$/
119 :string
120 # Object identifier types
121 when 'oid'
122 :integer
123 # UUID type
124 when 'uuid'
125 :string
126 # Small and big integer types
127 when /^(?:small|big)int$/
128 :integer
129 # Pass through all types that are not specific to PostgreSQL.
130 else
131 super
132 end
133 end
134
135 # Extracts the value from a PostgreSQL column default definition.
136 def self.extract_value_from_default(default)
137 case default
138 # This is a performance optimization for Ruby 1.9.2 in development.
139 # If the value is nil, we return nil straight away without checking
140 # the regular expressions. If we check each regular expression,
141 # Regexp#=== will call NilClass#to_str, which will trigger
142 # method_missing (defined by whiny nil in ActiveSupport) which
143 # makes this method very very slow.
144 when NilClass
145 nil
146 # Numeric types
147 when /\A\(?(-?\d+(\.\d*)?\)?)\z/
148 $1
149 # Character types
150 when /\A'(.*)'::(?:character varying|bpchar|text)\z/m
151 $1
152 # Character types (8.1 formatting)
153 when /\AE'(.*)'::(?:character varying|bpchar|text)\z/m
154 $1.gsub(/\\(\d\d\d)/) { $1.oct.chr }
155 # Binary data types
156 when /\A'(.*)'::bytea\z/m
157 $1
158 # Date/time types
159 when /\A'(.+)'::(?:time(?:stamp)? with(?:out)? time zone|date)\z/
160 $1
161 when /\A'(.*)'::interval\z/
162 $1
163 # Boolean type
164 when 'true'
165 true
166 when 'false'
167 false
168 # Geometric types
169 when /\A'(.*)'::(?:point|line|lseg|box|"?path"?|polygon|circle)\z/
170 $1
171 # Network address types
172 when /\A'(.*)'::(?:cidr|inet|macaddr)\z/
173 $1
174 # Bit string types
175 when /\AB'(.*)'::"?bit(?: varying)?"?\z/
176 $1
177 # XML type
178 when /\A'(.*)'::xml\z/m
179 $1
180 # Arrays
181 when /\A'(.*)'::"?\D+"?\[\]\z/
182 $1
183 # Object identifier types
184 when /\A-?\d+\z/
185 $1
186 else
187 # Anything else is blank, some user type, or some function
188 # and we can't know the value of that, so return nil.
189 nil
190 end
191 end
192 end
193
194 # The PostgreSQL adapter works both with the native C (http://ruby.scripting.ca/postgres/) and the pure
195 # Ruby (available both as gem and from http://rubyforge.org/frs/?group_id=234&release_id=1944) drivers.
196 #
197 # Options:
198 #
199 # * <tt>:host</tt> - Defaults to "localhost".
200 # * <tt>:port</tt> - Defaults to 5432.
201 # * <tt>:username</tt> - Defaults to nothing.
202 # * <tt>:password</tt> - Defaults to nothing.
203 # * <tt>:database</tt> - The name of the database. No default, must be provided.
204 # * <tt>:schema_search_path</tt> - An optional schema search path for the connection given
205 # as a string of comma-separated schema names. This is backward-compatible with the <tt>:schema_order</tt> option.
206 # * <tt>:encoding</tt> - An optional client encoding that is used in a <tt>SET client_encoding TO
207 # <encoding></tt> call on the connection.
208 # * <tt>:min_messages</tt> - An optional client min messages that is used in a
209 # <tt>SET client_min_messages TO <min_messages></tt> call on the connection.
210 class PostgreSQLAdapter < AbstractAdapter
211 class TableDefinition < ActiveRecord::ConnectionAdapters::TableDefinition
212 def xml(*args)
213 options = args.extract_options!
214 column(args[0], 'xml', options)
215 end
216
217 def tsvector(*args)
218 options = args.extract_options!
219 column(args[0], 'tsvector', options)
220 end
221 end
222
223 ADAPTER_NAME = 'PostgreSQL'
224
225 NATIVE_DATABASE_TYPES = {
226 :primary_key => "serial primary key",
227 :string => { :name => "character varying", :limit => 255 },
228 :text => { :name => "text" },
229 :integer => { :name => "integer" },
230 :float => { :name => "float" },
231 :decimal => { :name => "decimal" },
232 :datetime => { :name => "timestamp" },
233 :timestamp => { :name => "timestamp" },
234 :time => { :name => "time" },
235 :date => { :name => "date" },
236 :binary => { :name => "bytea" },
237 :boolean => { :name => "boolean" },
238 :xml => { :name => "xml" },
239 :tsvector => { :name => "tsvector" }
240 }
241
242 # Returns 'PostgreSQL' as adapter name for identification purposes.
243 def adapter_name
244 ADAPTER_NAME
245 end
246
247 # Returns +true+, since this connection adapter supports prepared statement
248 # caching.
249 def supports_statement_cache?
250 true
251 end
252
253 def supports_index_sort_order?
254 true
255 end
256
257 class StatementPool < ConnectionAdapters::StatementPool
258 def initialize(connection, max)
259 super
260 @counter = 0
261 @cache = Hash.new { |h,pid| h[pid] = {} }
262 end
263
264 def each(&block); cache.each(&block); end
265 def key?(key); cache.key?(key); end
266 def [](key); cache[key]; end
267 def length; cache.length; end
268
269 def next_key
270 "a#{@counter + 1}"
271 end
272
273 def []=(sql, key)
274 while @max <= cache.size
275 dealloc(cache.shift.last)
276 end
277 @counter += 1
278 cache[sql] = key
279 end
280
281 def clear
282 cache.each_value do |stmt_key|
283 dealloc stmt_key
284 end
285 cache.clear
286 end
287
288 def delete(sql_key)
289 dealloc cache[sql_key]
290 cache.delete sql_key
291 end
292
293 private
294 def cache
295 @cache[$$]
296 end
297
298 def dealloc(key)
299 @connection.query "DEALLOCATE #{key}" if connection_active?
300 end
301
302 def connection_active?
303 @connection.status == PGconn::CONNECTION_OK
304 rescue PGError
305 false
306 end
307 end
308
309 class BindSubstitution < Arel::Visitors::PostgreSQL # :nodoc:
310 include Arel::Visitors::BindVisitor
311 end
312
313 # Initializes and connects a PostgreSQL adapter.
314 def initialize(connection, logger, connection_parameters, config)
315 super(connection, logger)
316
317 if config.fetch(:prepared_statements) { true }
318 @visitor = Arel::Visitors::PostgreSQL.new self
319 else
320 @visitor = BindSubstitution.new self
321 end
322
323 connection_parameters.delete :prepared_statements
324
325 @connection_parameters, @config = connection_parameters, config
326
327 # @local_tz is initialized as nil to avoid warnings when connect tries to use it
328 @local_tz = nil
329 @table_alias_length = nil
330
331 connect
332 @statements = StatementPool.new @connection,
333 config.fetch(:statement_limit) { 1000 }
334
335 if postgresql_version < 80200
336 raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!"
337 end
338
339 @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"]
340 end
341
342 # Clears the prepared statements cache.
343 def clear_cache!
344 @statements.clear
345 end
346
347 # Is this connection alive and ready for queries?
348 def active?
349 @connection.query 'SELECT 1'
350 true
351 rescue PGError
352 false
353 end
354
355 # Close then reopen the connection.
356 def reconnect!
357 clear_cache!
358 @connection.reset
359 configure_connection
360 end
361
362 def reset!
363 clear_cache!
364 super
365 end
366
367 # Disconnects from the database if already connected. Otherwise, this
368 # method does nothing.
369 def disconnect!
370 clear_cache!
371 @connection.close rescue nil
372 end
373
374 def native_database_types #:nodoc:
375 NATIVE_DATABASE_TYPES
376 end
377
378 # Returns true, since this connection adapter supports migrations.
379 def supports_migrations?
380 true
381 end
382
383 # Does PostgreSQL support finding primary key on non-Active Record tables?
384 def supports_primary_key? #:nodoc:
385 true
386 end
387
388 # Enable standard-conforming strings if available.
389 def set_standard_conforming_strings
390 old, self.client_min_messages = client_min_messages, 'warning'
391 execute('SET standard_conforming_strings = on', 'SCHEMA') rescue nil
392 ensure
393 self.client_min_messages = old
394 end
395
396 def supports_insert_with_returning?
397 true
398 end
399
400 def supports_ddl_transactions?
401 true
402 end
403
404 # Returns true, since this connection adapter supports savepoints.
405 def supports_savepoints?
406 true
407 end
408
409 # Returns true.
410 def supports_explain?
411 true
412 end
413
414 # Returns the configured supported identifier length supported by PostgreSQL
415 def table_alias_length
416 @table_alias_length ||= query('SHOW max_identifier_length')[0][0].to_i
417 end
418
419 # QUOTING ==================================================
420
421 # Escapes binary strings for bytea input to the database.
422 def escape_bytea(value)
423 @connection.escape_bytea(value) if value
424 end
425
426 # Unescapes bytea output from a database to the binary string it represents.
427 # NOTE: This is NOT an inverse of escape_bytea! This is only to be used
428 # on escaped binary output from database drive.
429 def unescape_bytea(value)
430 @connection.unescape_bytea(value) if value
431 end
432
433 # Quotes PostgreSQL-specific data types for SQL input.
434 def quote(value, column = nil) #:nodoc:
435 return super unless column
436
437 case value
438 when Float
439 return super unless value.infinite? && column.type == :datetime
440 "'#{value.to_s.downcase}'"
441 when Numeric
442 return super unless column.sql_type == 'money'
443 # Not truly string input, so doesn't require (or allow) escape string syntax.
444 "'#{value}'"
445 when String
446 case column.sql_type
447 when 'bytea' then "'#{escape_bytea(value)}'"
448 when 'xml' then "xml '#{quote_string(value)}'"
449 when /^bit/
450 case value
451 when /^[01]*$/ then "B'#{value}'" # Bit-string notation
452 when /^[0-9A-F]*$/i then "X'#{value}'" # Hexadecimal notation
453 end
454 else
455 super
456 end
457 else
458 super
459 end
460 end
461
462 def type_cast(value, column)
463 return super unless column
464
465 case value
466 when String
467 return super unless 'bytea' == column.sql_type
468 { :value => value, :format => 1 }
469 else
470 super
471 end
472 end
473
474 # Quotes strings for use in SQL input.
475 def quote_string(s) #:nodoc:
476 @connection.escape(s)
477 end
478
479 # Checks the following cases:
480 #
481 # - table_name
482 # - "table.name"
483 # - schema_name.table_name
484 # - schema_name."table.name"
485 # - "schema.name".table_name
486 # - "schema.name"."table.name"
487 def quote_table_name(name)
488 schema, name_part = extract_pg_identifier_from_name(name.to_s)
489
490 unless name_part
491 quote_column_name(schema)
492 else
493 table_name, name_part = extract_pg_identifier_from_name(name_part)
494 "#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
495 end
496 end
497
498 # Quotes column names for use in SQL queries.
499 def quote_column_name(name) #:nodoc:
500 PGconn.quote_ident(name.to_s)
501 end
502
503 # Quote date/time values for use in SQL input. Includes microseconds
504 # if the value is a Time responding to usec.
505 def quoted_date(value) #:nodoc:
506 if value.acts_like?(:time) && value.respond_to?(:usec)
507 "#{super}.#{sprintf("%06d", value.usec)}"
508 else
509 super
510 end
511 end
512
513 # Set the authorized user for this session
514 def session_auth=(user)
515 clear_cache!
516 exec_query "SET SESSION AUTHORIZATION #{user}"
517 end
518
519 # REFERENTIAL INTEGRITY ====================================
520
521 def supports_disable_referential_integrity? #:nodoc:
522 true
523 end
524
525 def disable_referential_integrity #:nodoc:
526 if supports_disable_referential_integrity? then
527 execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
528 end
529 yield
530 ensure
531 if supports_disable_referential_integrity? then
532 execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))
533 end
534 end
535
536 # DATABASE STATEMENTS ======================================
537
538 def explain(arel, binds = [])
539 sql = "EXPLAIN #{to_sql(arel, binds)}"
540 ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds))
541 end
542
543 class ExplainPrettyPrinter # :nodoc:
544 # Pretty prints the result of a EXPLAIN in a way that resembles the output of the
545 # PostgreSQL shell:
546 #
547 # QUERY PLAN
548 # ------------------------------------------------------------------------------
549 # Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0)
550 # Join Filter: (posts.user_id = users.id)
551 # -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4)
552 # Index Cond: (id = 1)
553 # -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4)
554 # Filter: (posts.user_id = 1)
555 # (6 rows)
556 #
557 def pp(result)
558 header = result.columns.first
559 lines = result.rows.map(&:first)
560
561 # We add 2 because there's one char of padding at both sides, note
562 # the extra hyphens in the example above.
563 width = [header, *lines].map(&:length).max + 2
564
565 pp = []
566
567 pp << header.center(width).rstrip
568 pp << '-' * width
569
570 pp += lines.map {|line| " #{line}"}
571
572 nrows = result.rows.length
573 rows_label = nrows == 1 ? 'row' : 'rows'
574 pp << "(#{nrows} #{rows_label})"
575
576 pp.join("\n") + "\n"
577 end
578 end
579
580 # Executes a SELECT query and returns an array of rows. Each row is an
581 # array of field values.
582 def select_rows(sql, name = nil)
583 select_raw(sql, name).last
584 end
585
586 # Executes an INSERT query and returns the new record's ID
587 def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
588 unless pk
589 # Extract the table from the insert sql. Yuck.
590 table_ref = extract_table_ref_from_insert_sql(sql)
591 pk = primary_key(table_ref) if table_ref
592 end
593
594 if pk
595 select_value("#{sql} RETURNING #{quote_column_name(pk)}")
596 else
597 super
598 end
599 end
600 alias :create :insert
601
602 # create a 2D array representing the result set
603 def result_as_array(res) #:nodoc:
604 # check if we have any binary column and if they need escaping
605 ftypes = Array.new(res.nfields) do |i|
606 [i, res.ftype(i)]
607 end
608
609 rows = res.values
610 return rows unless ftypes.any? { |_, x|
611 x == BYTEA_COLUMN_TYPE_OID || x == MONEY_COLUMN_TYPE_OID
612 }
613
614 typehash = ftypes.group_by { |_, type| type }
615 binaries = typehash[BYTEA_COLUMN_TYPE_OID] || []
616 monies = typehash[MONEY_COLUMN_TYPE_OID] || []
617
618 rows.each do |row|
619 # unescape string passed BYTEA field (OID == 17)
620 binaries.each do |index, _|
621 row[index] = unescape_bytea(row[index])
622 end
623
624 # If this is a money type column and there are any currency symbols,
625 # then strip them off. Indeed it would be prettier to do this in
626 # PostgreSQLColumn.string_to_decimal but would break form input
627 # fields that call value_before_type_cast.
628 monies.each do |index, _|
629 data = row[index]
630 # Because money output is formatted according to the locale, there are two
631 # cases to consider (note the decimal separators):
632 # (1) $12,345,678.12
633 # (2) $12.345.678,12
634 case data
635 when /^-?\D+[\d,]+\.\d{2}$/ # (1)
636 data.gsub!(/[^-\d.]/, '')
637 when /^-?\D+[\d.]+,\d{2}$/ # (2)
638 data.gsub!(/[^-\d,]/, '').sub!(/,/, '.')
639 end
640 end
641 end
642 end
643
644
645 # Queries the database and returns the results in an Array-like object
646 def query(sql, name = nil) #:nodoc:
647 log(sql, name) do
648 result_as_array @connection.async_exec(sql)
649 end
650 end
651
652 # Executes an SQL statement, returning a PGresult object on success
653 # or raising a PGError exception otherwise.
654 def execute(sql, name = nil)
655 log(sql, name) do
656 @connection.async_exec(sql)
657 end
658 end
659
660 def substitute_at(column, index)
661 Arel::Nodes::BindParam.new "$#{index + 1}"
662 end
663
664 def exec_query(sql, name = 'SQL', binds = [])
665 log(sql, name, binds) do
666 result = binds.empty? ? exec_no_cache(sql, binds) :
667 exec_cache(sql, binds)
668
669 ret = ActiveRecord::Result.new(result.fields, result_as_array(result))
670 result.clear
671 return ret
672 end
673 end
674
675 def exec_delete(sql, name = 'SQL', binds = [])
676 log(sql, name, binds) do
677 result = binds.empty? ? exec_no_cache(sql, binds) :
678 exec_cache(sql, binds)
679 affected = result.cmd_tuples
680 result.clear
681 affected
682 end
683 end
684 alias :exec_update :exec_delete
685
686 def sql_for_insert(sql, pk, id_value, sequence_name, binds)
687 unless pk
688 # Extract the table from the insert sql. Yuck.
689 table_ref = extract_table_ref_from_insert_sql(sql)
690 pk = primary_key(table_ref) if table_ref
691 end
692
693 sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk
694
695 [sql, binds]
696 end
697
698 # Executes an UPDATE query and returns the number of affected tuples.
699 def update_sql(sql, name = nil)
700 super.cmd_tuples
701 end
702
703 # Begins a transaction.
704 def begin_db_transaction
705 execute "BEGIN"
706 end
707
708 # Commits a transaction.
709 def commit_db_transaction
710 execute "COMMIT"
711 end
712
713 # Aborts a transaction.
714 def rollback_db_transaction
715 execute "ROLLBACK"
716 end
717
718 def outside_transaction?
719 @connection.transaction_status == PGconn::PQTRANS_IDLE
720 end
721
722 def create_savepoint
723 execute("SAVEPOINT #{current_savepoint_name}")
724 end
725
726 def rollback_to_savepoint
727 execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
728 end
729
730 def release_savepoint
731 execute("RELEASE SAVEPOINT #{current_savepoint_name}")
732 end
733
734 # SCHEMA STATEMENTS ========================================
735
736 # Drops the database specified on the +name+ attribute
737 # and creates it again using the provided +options+.
738 def recreate_database(name, options = {}) #:nodoc:
739 drop_database(name)
740 create_database(name, options)
741 end
742
743 # Create a new PostgreSQL database. Options include <tt>:owner</tt>, <tt>:template</tt>,
744 # <tt>:encoding</tt>, <tt>:tablespace</tt>, and <tt>:connection_limit</tt> (note that MySQL uses
745 # <tt>:charset</tt> while PostgreSQL uses <tt>:encoding</tt>).
746 #
747 # Example:
748 # create_database config[:database], config
749 # create_database 'foo_development', :encoding => 'unicode'
750 def create_database(name, options = {})
751 options = options.reverse_merge(:encoding => "utf8")
752
753 option_string = options.symbolize_keys.sum do |key, value|
754 case key
755 when :owner
756 " OWNER = \"#{value}\""
757 when :template
758 " TEMPLATE = \"#{value}\""
759 when :encoding
760 " ENCODING = '#{value}'"
761 when :tablespace
762 " TABLESPACE = \"#{value}\""
763 when :connection_limit
764 " CONNECTION LIMIT = #{value}"
765 else
766 ""
767 end
768 end
769
770 execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
771 end
772
773 # Drops a PostgreSQL database.
774 #
775 # Example:
776 # drop_database 'matt_development'
777 def drop_database(name) #:nodoc:
778 execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
779 end
780
781 # Returns the list of all tables in the schema search path or a specified schema.
782 def tables(name = nil)
783 query(<<-SQL, 'SCHEMA').map { |row| row[0] }
784 SELECT tablename
785 FROM pg_tables
786 WHERE schemaname = ANY (current_schemas(false))
787 SQL
788 end
789
790 # Returns true if table exists.
791 # If the schema is not specified as part of +name+ then it will only find tables within
792 # the current schema search path (regardless of permissions to access tables in other schemas)
793 def table_exists?(name)
794 schema, table = Utils.extract_schema_and_table(name.to_s)
795 return false unless table
796
797 binds = [[nil, table]]
798 binds << [nil, schema] if schema
799
800 exec_query(<<-SQL, 'SCHEMA', binds).rows.first[0].to_i > 0
801 SELECT COUNT(*)
802 FROM pg_class c
803 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
804 WHERE c.relkind in ('v','r')
805 AND c.relname = $1
806 AND n.nspname = #{schema ? '$2' : 'ANY (current_schemas(false))'}
807 SQL
808 end
809
810 # Returns true if schema exists.
811 def schema_exists?(name)
812 exec_query(<<-SQL, 'SCHEMA', [[nil, name]]).rows.first[0].to_i > 0
813 SELECT COUNT(*)
814 FROM pg_namespace
815 WHERE nspname = $1
816 SQL
817 end
818
819 # Returns an array of indexes for the given table.
820 def indexes(table_name, name = nil)
821 result = query(<<-SQL, name)
822 SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
823 FROM pg_class t
824 INNER JOIN pg_index d ON t.oid = d.indrelid
825 INNER JOIN pg_class i ON d.indexrelid = i.oid
826 WHERE i.relkind = 'i'
827 AND d.indisprimary = 'f'
828 AND t.relname = '#{table_name}'
829 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
830 ORDER BY i.relname
831 SQL
832
833
834 result.map do |row|
835 index_name = row[0]
836 unique = row[1] == 't'
837 indkey = row[2].split(" ")
838 inddef = row[3]
839 oid = row[4]
840
841 columns = Hash[query(<<-SQL, "Columns for index #{row[0]} on #{table_name}")]
842 SELECT a.attnum, a.attname
843 FROM pg_attribute a
844 WHERE a.attrelid = #{oid}
845 AND a.attnum IN (#{indkey.join(",")})
846 SQL
847
848 column_names = columns.values_at(*indkey).compact
849
850 # add info on sort order for columns (only desc order is explicitly specified, asc is the default)
851 desc_order_columns = inddef.scan(/(\w+) DESC/).flatten
852 orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {}
853
854 column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders)
855 end.compact
856 end
857
858 # Returns the list of all column definitions for a table.
859 def columns(table_name, name = nil)
860 # Limit, precision, and scale are all handled by the superclass.
861 column_definitions(table_name).collect do |column_name, type, default, notnull|
862 PostgreSQLColumn.new(column_name, default, type, notnull == 'f')
863 end
864 end
865
866 # Returns the current database name.
867 def current_database
868 query('select current_database()')[0][0]
869 end
870
871 # Returns the current schema name.
872 def current_schema
873 query('SELECT current_schema', 'SCHEMA')[0][0]
874 end
875
876 # Returns the current database encoding format.
877 def encoding
878 query(<<-end_sql)[0][0]
879 SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database
880 WHERE pg_database.datname LIKE '#{current_database}'
881 end_sql
882 end
883
884 # Sets the schema search path to a string of comma-separated schema names.
885 # Names beginning with $ have to be quoted (e.g. $user => '$user').
886 # See: http://www.postgresql.org/docs/current/static/ddl-schemas.html
887 #
888 # This should be not be called manually but set in database.yml.
889 def schema_search_path=(schema_csv)
890 if schema_csv
891 execute("SET search_path TO #{schema_csv}", 'SCHEMA')
892 @schema_search_path = schema_csv
893 end
894 end
895
896 # Returns the active schema search path.
897 def schema_search_path
898 @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
899 end
900
901 # Returns the current client message level.
902 def client_min_messages
903 query('SHOW client_min_messages', 'SCHEMA')[0][0]
904 end
905
906 # Set the client message level.
907 def client_min_messages=(level)
908 execute("SET client_min_messages TO '#{level}'", 'SCHEMA')
909 end
910
911 # Returns the sequence name for a table's primary key or some other specified key.
912 def default_sequence_name(table_name, pk = nil) #:nodoc:
913 serial_sequence(table_name, pk || 'id').split('.').last
914 rescue ActiveRecord::StatementInvalid
915 "#{table_name}_#{pk || 'id'}_seq"
916 end
917
918 def serial_sequence(table, column)
919 result = exec_query(<<-eosql, 'SCHEMA', [[nil, table], [nil, column]])
920 SELECT pg_get_serial_sequence($1, $2)
921 eosql
922 result.rows.first.first
923 end
924
925 # Resets the sequence of a table's primary key to the maximum value.
926 def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:
927 unless pk and sequence
928 default_pk, default_sequence = pk_and_sequence_for(table)
929
930 pk ||= default_pk
931 sequence ||= default_sequence
932 end
933
934 if @logger && pk && !sequence
935 @logger.warn "#{table} has primary key #{pk} with no default sequence"
936 end
937
938 if pk && sequence
939 quoted_sequence = quote_table_name(sequence)
940
941 select_value <<-end_sql, 'Reset sequence'
942 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)
943 end_sql
944 end
945 end
946
947 # Returns a table's primary key and belonging sequence.
948 def pk_and_sequence_for(table) #:nodoc:
949 # First try looking for a sequence with a dependency on the
950 # given table's primary key.
951 result = query(<<-end_sql, 'PK and serial sequence')[0]
952 SELECT attr.attname, seq.relname
953 FROM pg_class seq,
954 pg_attribute attr,
955 pg_depend dep,
956 pg_namespace name,
957 pg_constraint cons
958 WHERE seq.oid = dep.objid
959 AND seq.relkind = 'S'
960 AND attr.attrelid = dep.refobjid
961 AND attr.attnum = dep.refobjsubid
962 AND attr.attrelid = cons.conrelid
963 AND attr.attnum = cons.conkey[1]
964 AND cons.contype = 'p'
965 AND dep.refobjid = '#{quote_table_name(table)}'::regclass
966 end_sql
967
968 if result.nil? or result.empty?
969 # If that fails, try parsing the primary key's default value.
970 # Support the 7.x and 8.0 nextval('foo'::text) as well as
971 # the 8.1+ nextval('foo'::regclass).
972 result = query(<<-end_sql, 'PK and custom sequence')[0]
973 SELECT attr.attname,
974 CASE
975 WHEN split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2) ~ '.' THEN
976 substr(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2),
977 strpos(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2), '.')+1)
978 ELSE split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2)
979 END
980 FROM pg_class t
981 JOIN pg_attribute attr ON (t.oid = attrelid)
982 JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum)
983 JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1])
984 WHERE t.oid = '#{quote_table_name(table)}'::regclass
985 AND cons.contype = 'p'
986 AND pg_get_expr(def.adbin, def.adrelid) ~* 'nextval'
987 end_sql
988 end
989
990 [result.first, result.last]
991 rescue
992 nil
993 end
994
995 # Returns just a table's primary key
996 def primary_key(table)
997 row = exec_query(<<-end_sql, 'SCHEMA', [[nil, table]]).rows.first
998 SELECT DISTINCT(attr.attname)
999 FROM pg_attribute attr
1000 INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid
1001 INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1]
1002 WHERE cons.contype = 'p'
1003 AND dep.refobjid = $1::regclass
1004 end_sql
1005
1006 row && row.first
1007 end
1008
1009 # Renames a table.
1010 # Also renames a table's primary key sequence if the sequence name matches the
1011 # Active Record default.
1012 #
1013 # Example:
1014 # rename_table('octopuses', 'octopi')
1015 def rename_table(name, new_name)
1016 clear_cache!
1017 execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}"
1018 pk, seq = pk_and_sequence_for(new_name)
1019 if seq == "#{name}_#{pk}_seq"
1020 new_seq = "#{new_name}_#{pk}_seq"
1021 execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}"
1022 end
1023 end
1024
1025 # Adds a new column to the named table.
1026 # See TableDefinition#column for details of the options you can use.
1027 def add_column(table_name, column_name, type, options = {})
1028 clear_cache!
1029 add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
1030 add_column_options!(add_column_sql, options)
1031
1032 execute add_column_sql
1033 end
1034
1035 # Changes the column of a table.
1036 def change_column(table_name, column_name, type, options = {})
1037 clear_cache!
1038 quoted_table_name = quote_table_name(table_name)
1039
1040 execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
1041
1042 change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
1043 change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
1044 end
1045
1046 # Changes the default value of a table column.
1047 def change_column_default(table_name, column_name, default)
1048 clear_cache!
1049 execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
1050 end
1051
1052 def change_column_null(table_name, column_name, null, default = nil)
1053 clear_cache!
1054 unless null || default.nil?
1055 execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
1056 end
1057 execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
1058 end
1059
1060 # Renames a column in a table.
1061 def rename_column(table_name, column_name, new_column_name)
1062 clear_cache!
1063 execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
1064 end
1065
1066 def remove_index!(table_name, index_name) #:nodoc:
1067 execute "DROP INDEX #{quote_table_name(index_name)}"
1068 end
1069
1070 def rename_index(table_name, old_name, new_name)
1071 execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}"
1072 end
1073
1074 def index_name_length
1075 63
1076 end
1077
1078 # Maps logical Rails types to PostgreSQL-specific data types.
1079 def type_to_sql(type, limit = nil, precision = nil, scale = nil)
1080 case type.to_s
1081 when 'binary'
1082 # PostgreSQL doesn't support limits on binary (bytea) columns.
1083 # The hard limit is 1Gb, because of a 32-bit size field, and TOAST.
1084 case limit
1085 when nil, 0..0x3fffffff; super(type)
1086 else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
1087 end
1088 when 'integer'
1089 return 'integer' unless limit
1090
1091 case limit
1092 when 1, 2; 'smallint'
1093 when 3, 4; 'integer'
1094 when 5..8; 'bigint'
1095 else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
1096 end
1097 else
1098 super
1099 end
1100 end
1101
1102 # Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.
1103 #
1104 # PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and
1105 # requires that the ORDER BY include the distinct column.
1106 #
1107 # distinct("posts.id", "posts.created_at desc")
1108 def distinct(columns, orders) #:nodoc:
1109 return "DISTINCT #{columns}" if orders.empty?
1110
1111 # Construct a clean list of column names from the ORDER BY clause, removing
1112 # any ASC/DESC modifiers
1113 order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s*/i, '') }
1114 order_columns.delete_if { |c| c.blank? }
1115 order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }
1116
1117 "DISTINCT #{columns}, #{order_columns * ', '}"
1118 end
1119
1120 module Utils
1121 extend self
1122
1123 # Returns an array of <tt>[schema_name, table_name]</tt> extracted from +name+.
1124 # +schema_name+ is nil if not specified in +name+.
1125 # +schema_name+ and +table_name+ exclude surrounding quotes (regardless of whether provided in +name+)
1126 # +name+ supports the range of schema/table references understood by PostgreSQL, for example:
1127 #
1128 # * <tt>table_name</tt>
1129 # * <tt>"table.name"</tt>
1130 # * <tt>schema_name.table_name</tt>
1131 # * <tt>schema_name."table.name"</tt>
1132 # * <tt>"schema.name"."table name"</tt>
1133 def extract_schema_and_table(name)
1134 table, schema = name.scan(/[^".\s]+|"[^"]*"/)[0..1].collect{|m| m.gsub(/(^"|"$)/,'') }.reverse
1135 [schema, table]
1136 end
1137 end
1138
1139 protected
1140 # Returns the version of the connected PostgreSQL server.
1141 def postgresql_version
1142 @connection.server_version
1143 end
1144
1145 def translate_exception(exception, message)
1146 case exception.message
1147 when /duplicate key value violates unique constraint/
1148 RecordNotUnique.new(message, exception)
1149 when /violates foreign key constraint/
1150 InvalidForeignKey.new(message, exception)
1151 else
1152 super
1153 end
1154 end
1155
1156 private
1157 FEATURE_NOT_SUPPORTED = "0A000" # :nodoc:
1158
1159 def exec_no_cache(sql, binds)
1160 @connection.async_exec(sql)
1161 end
1162
1163 def exec_cache(sql, binds)
1164 begin
1165 stmt_key = prepare_statement sql
1166
1167 # Clear the queue
1168 @connection.get_last_result
1169 @connection.send_query_prepared(stmt_key, binds.map { |col, val|
1170 type_cast(val, col)
1171 })
1172 @connection.block
1173 @connection.get_last_result
1174 rescue PGError => e
1175 # Get the PG code for the failure. Annoyingly, the code for
1176 # prepared statements whose return value may have changed is
1177 # FEATURE_NOT_SUPPORTED. Check here for more details:
1178 # http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/cache/plancache.c#l573
1179 code = e.result.result_error_field(PGresult::PG_DIAG_SQLSTATE)
1180 if FEATURE_NOT_SUPPORTED == code
1181 @statements.delete sql_key(sql)
1182 retry
1183 else
1184 raise e
1185 end
1186 end
1187 end
1188
1189 # Returns the statement identifier for the client side cache
1190 # of statements
1191 def sql_key(sql)
1192 "#{schema_search_path}-#{sql}"
1193 end
1194
1195 # Prepare the statement if it hasn't been prepared, return
1196 # the statement key.
1197 def prepare_statement(sql)
1198 sql_key = sql_key(sql)
1199 unless @statements.key? sql_key
1200 nextkey = @statements.next_key
1201 @connection.prepare nextkey, sql
1202 @statements[sql_key] = nextkey
1203 end
1204 @statements[sql_key]
1205 end
1206
1207 # The internal PostgreSQL identifier of the money data type.
1208 MONEY_COLUMN_TYPE_OID = 790 #:nodoc:
1209 # The internal PostgreSQL identifier of the BYTEA data type.
1210 BYTEA_COLUMN_TYPE_OID = 17 #:nodoc:
1211
1212 # Connects to a PostgreSQL server and sets up the adapter depending on the
1213 # connected server's characteristics.
1214 def connect
1215 @connection = PGconn.connect(*@connection_parameters)
1216
1217 # Money type has a fixed precision of 10 in PostgreSQL 8.2 and below, and as of
1218 # PostgreSQL 8.3 it has a fixed precision of 19. PostgreSQLColumn.extract_precision
1219 # should know about this but can't detect it there, so deal with it here.
1220 PostgreSQLColumn.money_precision = (postgresql_version >= 80300) ? 19 : 10
1221
1222 configure_connection
1223 end
1224
1225 # Configures the encoding, verbosity, schema search path, and time zone of the connection.
1226 # This is called by #connect and should not be called manually.
1227 def configure_connection
1228 if @config[:encoding]
1229 @connection.set_client_encoding(@config[:encoding])
1230 end
1231 self.client_min_messages = @config[:min_messages] if @config[:min_messages]
1232 self.schema_search_path = @config[:schema_search_path] || @config[:schema_order]
1233
1234 # Use standard-conforming strings if available so we don't have to do the E'...' dance.
1235 set_standard_conforming_strings
1236
1237 # If using Active Record's time zone support configure the connection to return
1238 # TIMESTAMP WITH ZONE types in UTC.
1239 if ActiveRecord::Base.default_timezone == :utc
1240 execute("SET time zone 'UTC'", 'SCHEMA')
1241 elsif @local_tz
1242 execute("SET time zone '#{@local_tz}'", 'SCHEMA')
1243 end
1244 end
1245
1246 # Returns the current ID of a table's sequence.
1247 def last_insert_id(sequence_name) #:nodoc:
1248 r = exec_query("SELECT currval($1)", 'SQL', [[nil, sequence_name]])
1249 Integer(r.rows.first.first)
1250 end
1251
1252 # Executes a SELECT query and returns the results, performing any data type
1253 # conversions that are required to be performed here instead of in PostgreSQLColumn.
1254 def select(sql, name = nil, binds = [])
1255 exec_query(sql, name, binds).to_a
1256 end
1257
1258 def select_raw(sql, name = nil)
1259 res = execute(sql, name)
1260 results = result_as_array(res)
1261 fields = res.fields
1262 res.clear
1263 return fields, results
1264 end
1265
1266 # Returns the list of a table's column names, data types, and default values.
1267 #
1268 # The underlying query is roughly:
1269 # SELECT column.name, column.type, default.value
1270 # FROM column LEFT JOIN default
1271 # ON column.table_id = default.table_id
1272 # AND column.num = default.column_num
1273 # WHERE column.table_id = get_table_id('table_name')
1274 # AND column.num > 0
1275 # AND NOT column.is_dropped
1276 # ORDER BY column.num
1277 #
1278 # If the table name is not prefixed with a schema, the database will
1279 # take the first match from the schema search path.
1280 #
1281 # Query implementation notes:
1282 # - format_type includes the column size constraint, e.g. varchar(50)
1283 # - ::regclass is a function that gives the id for a table name
1284 def column_definitions(table_name) #:nodoc:
1285 exec_query(<<-end_sql, 'SCHEMA').rows
1286 SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull
1287 FROM pg_attribute a LEFT JOIN pg_attrdef d
1288 ON a.attrelid = d.adrelid AND a.attnum = d.adnum
1289 WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass
1290 AND a.attnum > 0 AND NOT a.attisdropped
1291 ORDER BY a.attnum
1292 end_sql
1293 end
1294
1295 def extract_pg_identifier_from_name(name)
1296 match_data = name.start_with?('"') ? name.match(/\"([^\"]+)\"/) : name.match(/([^\.]+)/)
1297
1298 if match_data
1299 rest = name[match_data[0].length, name.length]
1300 rest = rest[1, rest.length] if rest.start_with? "."
1301 [match_data[1], (rest.length > 0 ? rest : nil)]
1302 end
1303 end
1304
1305 def extract_table_ref_from_insert_sql(sql)
1306 sql[/into\s+([^\(]*).*values\s*\(/i]
1307 $1.strip if $1
1308 end
1309
1310 def table_definition
1311 TableDefinition.new(self)
1312 end
1313 end
1314 end
1315end
1316