ArJdbc.load_java_part :PostgreSQL require 'ipaddr' module ArJdbc # Strives to provide Rails built-in PostgreSQL adapter (API) compatibility. module PostgreSQL # @private AR4_COMPAT = ::ActiveRecord::VERSION::MAJOR > 3 unless const_defined?(:AR4_COMPAT) require 'arjdbc/postgresql/column' require 'arjdbc/postgresql/explain_support' # @see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_connection_class def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::PostgreSQLJdbcConnection end # @private def init_connection(jdbc_connection) meta = jdbc_connection.meta_data if meta.driver_version.index('JDBC3') # e.g. 'PostgreSQL 9.2 JDBC4 (build 1002)' config[:connection_alive_sql] ||= 'SELECT 1' else # NOTE: since the loaded Java driver class can't change : PostgreSQL.send(:remove_method, :init_connection) rescue nil end end def self.arel_visitor_type(config = nil) ::Arel::Visitors::PostgreSQL end # @see ActiveRecord::ConnectionAdapters::JdbcAdapter#bind_substitution # @private class BindSubstitution < ::Arel::Visitors::PostgreSQL include ::Arel::Visitors::BindVisitor end if defined? ::Arel::Visitors::BindVisitor ADAPTER_NAME = 'PostgreSQL'.freeze def adapter_name ADAPTER_NAME end def postgresql_version @postgresql_version ||= begin value = select_value('SELECT version()') if value =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/ ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i else 0 end end end def use_insert_returning? if ( @use_insert_returning ||= nil ).nil? @use_insert_returning = supports_insert_with_returning? end @use_insert_returning end def set_client_encoding(encoding) ActiveRecord::Base.logger.warn "client_encoding is set by the driver and should not be altered, ('#{encoding}' ignored)" ActiveRecord::Base.logger.debug "Set the 'allowEncodingChanges' driver property (e.g. using config[:properties]) if you need to override the client encoding when doing a copy." end # Configures the encoding, verbosity, schema search path, and time zone of the connection. # This is called on `connection.connect` and should not be called manually. def configure_connection #if encoding = config[:encoding] # The client_encoding setting is set by the driver and should not be altered. # If the driver detects a change it will abort the connection. # see http://jdbc.postgresql.org/documentation/91/connect.html # self.set_client_encoding(encoding) #end self.client_min_messages = config[:min_messages] || 'warning' self.schema_search_path = config[:schema_search_path] || config[:schema_order] # Use standard-conforming strings if available so we don't have to do the E'...' dance. set_standard_conforming_strings # If using Active Record's time zone support configure the connection to return # TIMESTAMP WITH ZONE types in UTC. # (SET TIME ZONE does not use an equals sign like other SET variables) if ActiveRecord::Base.default_timezone == :utc execute("SET time zone 'UTC'", 'SCHEMA') elsif tz = local_tz execute("SET time zone '#{tz}'", 'SCHEMA') end # if defined? ActiveRecord::Base.default_timezone # SET statements from :variables config hash # http://www.postgresql.org/docs/8.3/static/sql-set.html (config[:variables] || {}).map do |k, v| if v == ':default' || v == :default # Sets the value to the global or compile default execute("SET SESSION #{k.to_s} TO DEFAULT", 'SCHEMA') elsif ! v.nil? execute("SET SESSION #{k.to_s} TO #{quote(v)}", 'SCHEMA') end end end # @private ActiveRecordError = ::ActiveRecord::ActiveRecordError # Maps logical Rails types to PostgreSQL-specific data types. def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_sym when :'binary' # PostgreSQL doesn't support limits on binary (bytea) columns. # The hard limit is 1Gb, because of a 32-bit size field, and TOAST. case limit when nil, 0..0x3fffffff; super(type, nil, nil, nil) else raise(ActiveRecordError, "No binary type has byte size #{limit}.") end when :'text' # PostgreSQL doesn't support limits on text columns. # The hard limit is 1Gb, according to section 8.3 in the manual. case limit when nil, 0..0x3fffffff; super(type, nil, nil, nil) else raise(ActiveRecordError, "The limit on text can be at most 1GB - 1byte.") end when :'integer' return 'integer' unless limit case limit when 1, 2; 'smallint' when 3, 4; 'integer' when 5..8; 'bigint' else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") end when :'datetime' return super unless precision case precision when 0..6; "timestamp(#{precision})" else raise(ActiveRecordError, "No timestamp type has precision of #{precision}. The allowed range of precision is from 0 to 6") end else super end end def type_cast(value, column, array_member = false) return super unless column case value when String return super(value, column) unless 'bytea' == column.sql_type value # { :value => value, :format => 1 } when Array case column.sql_type when 'point' column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_class.point_to_string(value) else return super(value, column) unless column.array? column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_class.array_to_string(value, column, self) end when NilClass if column.array? && array_member 'NULL' elsif column.array? value else super(value, column) end when Hash case column.sql_type when 'hstore' column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_class.hstore_to_string(value) when 'json' column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_class.json_to_string(value) else super(value, column) end when IPAddr return super unless column.sql_type == 'inet' || column.sql_type == 'cidr' column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_class.cidr_to_string(value) when Range return super(value, column) unless /range$/ =~ column.sql_type column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_class.range_to_string(value) else super(value, column) end end if AR4_COMPAT NATIVE_DATABASE_TYPES = { :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :numeric => { :name => "numeric" }, :decimal => { :name => "decimal" }, # :limit => 1000 :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" }, :xml => { :name => "xml" }, # AR-JDBC added : #:timestamptz => { :name => "timestamptz" }, #:timetz => { :name => "timetz" }, :money => { :name=>"money" }, :char => { :name => "char" }, :serial => { :name => "serial" }, # auto-inc integer, bigserial, smallserial } NATIVE_DATABASE_TYPES.update({ :tsvector => { :name => "tsvector" }, :hstore => { :name => "hstore" }, :inet => { :name => "inet" }, :cidr => { :name => "cidr" }, :macaddr => { :name => "macaddr" }, :uuid => { :name => "uuid" }, :json => { :name => "json" }, :ltree => { :name => "ltree" }, # ranges : :daterange => { :name => "daterange" }, :numrange => { :name => "numrange" }, :tsrange => { :name => "tsrange" }, :tstzrange => { :name => "tstzrange" }, :int4range => { :name => "int4range" }, :int8range => { :name => "int8range" }, }) if AR4_COMPAT def native_database_types NATIVE_DATABASE_TYPES end # Adds `:array` option to the default set provided by the `AbstractAdapter`. def prepare_column_options(column, types) spec = super spec[:array] = 'true' if column.respond_to?(:array) && column.array spec end if AR4_COMPAT # Adds `:array` as a valid migration key. def migration_keys super + [:array] end if AR4_COMPAT if ActiveRecord::VERSION::MAJOR > 3 require 'arjdbc/postgresql/schema_creation' def schema_creation; SchemaCreation.new(self); end end # Enable standard-conforming strings if available. def set_standard_conforming_strings self.standard_conforming_strings=(true) end # Enable standard-conforming strings if available. def standard_conforming_strings=(enable) client_min_messages = self.client_min_messages begin self.client_min_messages = 'panic' value = enable ? "on" : "off" execute("SET standard_conforming_strings = #{value}", 'SCHEMA') @standard_conforming_strings = ( value == "on" ) rescue @standard_conforming_strings = :unsupported ensure self.client_min_messages = client_min_messages end end def standard_conforming_strings? if @standard_conforming_strings.nil? client_min_messages = self.client_min_messages begin self.client_min_messages = 'panic' value = select_one('SHOW standard_conforming_strings', 'SCHEMA')['standard_conforming_strings'] @standard_conforming_strings = ( value == "on" ) rescue @standard_conforming_strings = :unsupported ensure self.client_min_messages = client_min_messages end end @standard_conforming_strings == true # return false if :unsupported end # Does PostgreSQL support migrations? def supports_migrations? true end # Does PostgreSQL support finding primary key on non-Active Record tables? def supports_primary_key? true end # Does PostgreSQL support standard conforming strings? def supports_standard_conforming_strings? standard_conforming_strings? @standard_conforming_strings != :unsupported end def supports_hex_escaped_bytea? postgresql_version >= 90000 end def supports_insert_with_returning? postgresql_version >= 80200 end def supports_ddl_transactions? true end def supports_transaction_isolation? true end def supports_index_sort_order? true end def supports_partial_index? true end if AR4_COMPAT # Range data-types weren't introduced until PostgreSQL 9.2. def supports_ranges? postgresql_version >= 90200 end if AR4_COMPAT def supports_transaction_isolation?(level = nil) true end # NOTE: handled by JdbcAdapter we override only to have save-point in logs : # @override def supports_savepoints? true end # @override def create_savepoint(name = current_savepoint_name(true)) log("SAVEPOINT #{name}", 'Savepoint') { super } end # @override def rollback_to_savepoint(name = current_savepoint_name) log("ROLLBACK TO SAVEPOINT #{name}", 'Savepoint') { super } end # @override def release_savepoint(name = current_savepoint_name) log("RELEASE SAVEPOINT #{name}", 'Savepoint') { super } end def supports_extensions? postgresql_version >= 90200 end # NOTE: only since AR-4.0 but should not hurt on other versions def enable_extension(name) execute("CREATE EXTENSION IF NOT EXISTS \"#{name}\"") end def disable_extension(name) execute("DROP EXTENSION IF EXISTS \"#{name}\" CASCADE") end def extension_enabled?(name) if supports_extensions? rows = select_rows("SELECT EXISTS(SELECT * FROM pg_available_extensions WHERE name = '#{name}' AND installed_version IS NOT NULL)", 'SCHEMA') available = rows.first.first # true/false or 't'/'f' available == true || available == 't' end end def extensions if supports_extensions? rows = select_rows "SELECT extname from pg_extension", "SCHEMA" rows.map { |row| row.first } else [] end end def index_algorithms { :concurrently => 'CONCURRENTLY' } end # Set the authorized user for this session. def session_auth=(user) execute "SET SESSION AUTHORIZATION #{user}" end # Returns the configured supported identifier length supported by PostgreSQL, # or report the default of 63 on PostgreSQL 7.x. def table_alias_length @table_alias_length ||= ( postgresql_version >= 80000 ? select_one('SHOW max_identifier_length')['max_identifier_length'].to_i : 63 ) end def default_sequence_name(table_name, pk = nil) default_pk, default_seq = pk_and_sequence_for(table_name) default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq" end # Resets sequence to the max value of the table's primary key if present. def reset_pk_sequence!(table, pk = nil, sequence = nil) if ! pk || ! sequence default_pk, default_sequence = pk_and_sequence_for(table) pk ||= default_pk; sequence ||= default_sequence end if pk && 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 end end # Find a table's primary key and sequence. def pk_and_sequence_for(table) # try looking for a seq with a dependency on the table's primary key : result = select(<<-end_sql, 'PK and Serial Sequence')[0] SELECT attr.attname, seq.relname FROM pg_class seq, pg_attribute attr, pg_depend dep, 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? || result.empty? # if that fails, try parsing the primary key's default value : result = select(<<-end_sql, 'PK and Custom Sequence')[0] SELECT attr.attname, CASE WHEN pg_get_expr(def.adbin, def.adrelid) !~* 'nextval' THEN NULL WHEN split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2) ~ '.' THEN substr(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2), strpos(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2), '.')+1) ELSE split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2) END as relname FROM pg_class t JOIN pg_attribute attr ON (t.oid = attrelid) JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) WHERE t.oid = '#{quote_table_name(table)}'::regclass AND cons.contype = 'p' AND pg_get_expr(def.adbin, def.adrelid) ~* 'nextval|uuid_generate' end_sql end [ result['attname'], result['relname'] ] rescue nil end def primary_key(table) result = select(<<-end_sql, 'SCHEMA').first SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelid = '#{quote_table_name(table)}'::regclass end_sql result && result['attname'] # pk_and_sequence = pk_and_sequence_for(table) # pk_and_sequence && pk_and_sequence.first end def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) unless pk # Extract the table from the insert sql. Yuck. table_ref = extract_table_ref_from_insert_sql(sql) pk = primary_key(table_ref) if table_ref end if pk && use_insert_returning? # && id_value.nil? select_value("#{to_sql(sql, binds)} RETURNING #{quote_column_name(pk)}") else execute(sql, name, binds) # super unless id_value table_ref ||= extract_table_ref_from_insert_sql(sql) # If neither PK nor sequence name is given, look them up. if table_ref && ! ( pk ||= primary_key(table_ref) ) && ! sequence_name pk, sequence_name = pk_and_sequence_for(table_ref) 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_ref, pk) id_value = last_insert_id(table_ref, sequence_name) end end id_value end end # @override def sql_for_insert(sql, pk, id_value, sequence_name, binds) unless pk # Extract the table from the insert sql. Yuck. table_ref = extract_table_ref_from_insert_sql(sql) pk = primary_key(table_ref) if table_ref end if pk && use_insert_returning? sql = "#{sql} RETURNING #{quote_column_name(pk)}" end [ sql, binds ] end # @override due RETURNING clause def exec_insert(sql, name, binds, pk = nil, sequence_name = nil) # NOTE: 3.2 does not pass the PK on #insert (passed only into #sql_for_insert) : # sql, binds = sql_for_insert(to_sql(arel, binds), pk, id_value, sequence_name, binds) # 3.2 : # value = exec_insert(sql, name, binds) # 4.x : # value = exec_insert(sql, name, binds, pk, sequence_name) if use_insert_returning? && ( pk || (sql.is_a?(String) && sql =~ /RETURNING "?\S+"?$/) ) exec_query(sql, name, binds) # due RETURNING clause returns a result set else result = super if pk unless sequence_name table_ref = extract_table_ref_from_insert_sql(sql) sequence_name = default_sequence_name(table_ref, pk) return result unless sequence_name end last_insert_id_result(sequence_name) else result end end end # Returns an array of schema names. def schema_names select_values( "SELECT nspname FROM pg_namespace" << " WHERE nspname !~ '^pg_.*' AND nspname NOT IN ('information_schema')" << " ORDER by nspname;", 'SCHEMA') end # Returns true if schema exists. def schema_exists?(name) select_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = '#{name}'", 'SCHEMA').to_i > 0 end # Returns the current schema name. def current_schema select_value('SELECT current_schema', 'SCHEMA') end # current database name def current_database select_value('SELECT current_database()', 'SCHEMA') end # Returns the current database encoding format. def encoding select_value( "SELECT pg_encoding_to_char(pg_database.encoding)" << " FROM pg_database" << " WHERE pg_database.datname LIKE '#{current_database}'", 'SCHEMA') end # Returns the current database collation. def collation select_value( "SELECT pg_database.datcollate" << " FROM pg_database" << " WHERE pg_database.datname LIKE '#{current_database}'", 'SCHEMA') end # Returns the current database ctype. def ctype select_value( "SELECT pg_database.datctype FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'", 'SCHEMA') end # Returns the active schema search path. def schema_search_path @schema_search_path ||= select_value('SHOW search_path', 'SCHEMA') 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: http://www.postgresql.org/docs/current/static/ddl-schemas.html # # This should be not be called manually but set in database.yml. def schema_search_path=(schema_csv) if schema_csv execute "SET search_path TO #{schema_csv}" @schema_search_path = schema_csv end end # Take an id from the result of an INSERT query. # @return [Integer, NilClass] def last_inserted_id(result) return nil if result.nil? return result if result.is_a? Integer # # but it will work with [{ 'id' => 1 }] Hash wrapped results as well result.first.first[1] # .first = { "id"=>1 } .first = [ "id", 1 ] end def last_insert_id(table, sequence_name = nil) sequence_name = table if sequence_name.nil? # AR-4.0 1 argument last_insert_id_result(sequence_name) end def last_insert_id_result(sequence_name) select_value("SELECT currval('#{sequence_name}')", 'SQL') end def recreate_database(name, options = {}) drop_database(name) create_database(name, options) end # Create a new PostgreSQL database. Options include :owner, :template, # :encoding, :collation, :ctype, # :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' def create_database(name, options = {}) options = { :encoding => 'utf8' }.merge!(options.symbolize_keys) option_string = options.sum do |key, value| case key when :owner " OWNER = \"#{value}\"" when :template " TEMPLATE = \"#{value}\"" when :encoding " ENCODING = '#{value}'" when :collation " LC_COLLATE = '#{value}'" when :ctype " LC_CTYPE = '#{value}'" when :tablespace " TABLESPACE = \"#{value}\"" when :connection_limit " CONNECTION LIMIT = #{value}" else "" end end execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" end def drop_database(name) execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}" end # Creates a schema for the given schema name. def create_schema(schema_name, pg_username = nil) if pg_username.nil? # AR 4.0 compatibility - accepts only single argument execute "CREATE SCHEMA #{schema_name}" else execute("CREATE SCHEMA \"#{schema_name}\" AUTHORIZATION \"#{pg_username}\"") end end # Drops the schema for the given schema name. def drop_schema schema_name execute "DROP SCHEMA #{schema_name} CASCADE" end def all_schemas select('SELECT nspname FROM pg_namespace').map { |row| row["nspname"] } end # @deprecated no longer used - handled with (AR built-in) Rake tasks def structure_dump database = @config[:database] if database.nil? if @config[:url] =~ /\/([^\/]*)$/ database = $1 else raise "Could not figure out what database this url is for #{@config["url"]}" end end ENV['PGHOST'] = @config[:host] if @config[:host] ENV['PGPORT'] = @config[:port].to_s if @config[:port] ENV['PGPASSWORD'] = @config[:password].to_s if @config[:password] search_path = "--schema=#{@config[:schema_search_path]}" if @config[:schema_search_path] @connection.connection.close begin definition = `pg_dump -i -U "#{@config[:username]}" -s -x -O #{search_path} #{database}` raise "Error dumping database" if $?.exitstatus == 1 # need to patch away any references to SQL_ASCII as it breaks the JDBC driver definition.gsub(/SQL_ASCII/, 'UNICODE') ensure reconnect! end end # Returns the current client message level. def client_min_messages select_value('SHOW client_min_messages', 'SCHEMA') end # Set the client message level. def client_min_messages=(level) execute("SET client_min_messages TO '#{level}'", 'SCHEMA') end # Gets the maximum number columns postgres has, default 32 def multi_column_index_limit defined?(@multi_column_index_limit) && @multi_column_index_limit || 32 end # Sets the maximum number columns postgres has, default 32 def multi_column_index_limit=(limit) @multi_column_index_limit = limit end # 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"]) # # => "DISTINCT posts.id, posts.created_at AS alias_0" def distinct(columns, orders) if orders.is_a?(String) orders = orders.split(','); orders.each(&:strip!) end order_columns = orders.map do |column| column = column.to_sql unless column.is_a?(String) # handle AREL node column.gsub(/\s+(ASC|DESC)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '') # remove ASC/DESC end.reject(&:blank?) return "DISTINCT #{columns}" if order_columns.empty? i = -1; order_columns.map! { |c| "#{c} AS alias_#{i += 1}" } "DISTINCT #{columns}, #{order_columns.join(', ')}" end # 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 as a sub-select and ordering # in that query. def add_order_by_for_association_limiting!(sql, options) return sql if options[:order].blank? order = options[: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 # @return [String] # @override def quote(value, column = nil) return super unless column return value if sql_literal?(value) case value when Float if value.infinite? && ( column.type == :datetime || column.type == :timestamp ) "'#{value.to_s.downcase}'" elsif value.infinite? || value.nan? "'#{value.to_s}'" else super end when Numeric if column.respond_to?(:sql_type) && column.sql_type == 'money' # not truly string input, so doesn't require (or allow) escape syntax : ( column.type == :string || column.type == :text ) ? "'#{value}'" : super else super end when String return "E'#{escape_bytea(value)}'::bytea" if column.type == :binary return "xml '#{quote_string(value)}'" if column.type == :xml if column.respond_to?(:sql_type) && column.sql_type[0, 3] == 'bit' quote_bit(value) else super end when Array if AR4_COMPAT && column.array? # will be always falsy in AR < 4.0 column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn "'#{column_class.array_to_string(value, column, self).gsub(/'/, "''")}'" else super end when Hash if column.type == :hstore # only in AR-4.0 column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn super(column_class.hstore_to_string(value), column) elsif column.type == :json # only in AR-4.0 column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn super(column_class.json_to_string(value), column) else super end when Range if column.type.to_s[-5..-1] == 'range' # :'xxxrange' only in AR-4.0 column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn "'#{column_class.range_to_string(value)}'::#{column.sql_type}" else super end when IPAddr if column.type == :inet || column.type == :cidr # only in AR-4.0 column_class = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn super(column_class.cidr_to_string(value), column) else super end else super end end # Quotes a string, escaping any ' (single quote) and \ (backslash) chars. # @return [String] # @override def quote_string(string) quoted = string.gsub("'", "''") unless standard_conforming_strings? quoted.gsub!(/\\/, '\&\&') end quoted end # @return [String] def quote_bit(value) case value # NOTE: as reported with #60 this is not quite "right" : # "0103" will be treated as hexadecimal string # "0102" will be treated as hexadecimal string # "0101" will be treated as binary string # "0100" will be treated as binary string # ... but is kept due Rails compatibility when /^[01]*$/ then "B'#{value}'" # Bit-string notation when /^[0-9A-F]*$/i then "X'#{value}'" # Hexadecimal notation end end def quote_bit(value) "B'#{value}'" end if PostgreSQL::AR4_COMPAT def escape_bytea(string) if string if supports_hex_escaped_bytea? "\\\\x#{string.unpack("H*")[0]}" else result = '' string.each_byte { |c| result << sprintf('\\\\%03o', c) } result end end end # @override 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 # @override def quote_table_name_for_assignment(table, attr) quote_column_name(attr) end if ::ActiveRecord::VERSION::MAJOR > 3 # @override def quote_column_name(name) %("#{name.to_s.gsub("\"", "\"\"")}") end # Quote date/time values for use in SQL input. # Includes microseconds if the value is a Time responding to `usec`. # @override def quoted_date(value) result = super if value.acts_like?(:time) && value.respond_to?(:usec) result = "#{result}.#{sprintf("%06d", value.usec)}" end result = "#{result.sub(/^-/, '')} BC" if value.year < 0 result end if ::ActiveRecord::VERSION::MAJOR >= 3 # @override def supports_disable_referential_integrity? true end def disable_referential_integrity if supports_disable_referential_integrity? begin execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";")) rescue execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER USER" }.join(";")) end end yield ensure if supports_disable_referential_integrity? begin execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";")) rescue execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER USER" }.join(";")) end end end def rename_table(table_name, new_name) execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}" pk, seq = pk_and_sequence_for(new_name) if seq == "#{table_name}_#{pk}_seq" new_seq = "#{new_name}_#{pk}_seq" execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}" end rename_table_indexes(table_name, new_name) if respond_to?(:rename_table_indexes) # AR-4.0 SchemaStatements end # Adds a new column to the named table. # See TableDefinition#column for details of the options you can use. def add_column(table_name, column_name, type, options = {}) default = options[:default] notnull = options[:null] == false sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale]) sql_type << "[]" if options[:array] # Add the column. execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{sql_type}") change_column_default(table_name, column_name, default) if options_include_default?(options) change_column_null(table_name, column_name, false, default) if notnull end # Changes the column of a table. def change_column(table_name, column_name, type, options = {}) quoted_table_name = quote_table_name(table_name) sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale]) sql_type << "[]" if options[:array] begin execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{sql_type}" 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, options) execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{sql_type})" 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, options[:default]) if options_include_default?(options) change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) end # Changes the default value of a table column. 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 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 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)}" rename_column_indexes(table_name, column_name, new_column_name) if respond_to?(:rename_column_indexes) # AR-4.0 SchemaStatements end def add_index(table_name, column_name, options = {}) index_name, index_type, index_columns, index_options, index_algorithm, index_using = add_index_options(table_name, column_name, options) execute "CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns})#{index_options}" end if AR4_COMPAT def remove_index!(table_name, index_name) execute "DROP INDEX #{quote_table_name(index_name)}" end def index_name_length 63 end # Returns the list of all column definitions for a table. def columns(table_name, name = nil) klass = ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn column_definitions(table_name).map do |row| # name, type, default, notnull, oid, fmod name = row[0]; type = row[1]; default = row[2] notnull = row[3]; oid = row[4]; fmod = row[5] # oid = OID::TYPE_MAP.fetch(oid.to_i, fmod.to_i) { OID::Identity.new } notnull = notnull == 't' if notnull.is_a?(String) # JDBC gets true/false # for ID columns we get a bit of non-sense default : # e.g. "nextval('mixed_cases_id_seq'::regclass" if default =~ /^nextval\(.*?\:\:regclass\)$/ default = nil elsif default =~ /^\(([-+]?[\d\.]+)\)$/ # e.g. "(-1)" for a negative default default = $1 end klass.new(name, default, oid, type, ! notnull) end end # Returns the list of a table's column names, data types, and default values. # # If the table name is not prefixed with a schema, the database will # take the first match from the schema search path. # # Query implementation notes: # - format_type includes the column size constraint, e.g. varchar(50) # - ::regclass is a function that gives the id for a table name def column_definitions(table_name) select_rows(<<-end_sql, 'SCHEMA') SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum end_sql end private :column_definitions def tables(name = nil) select_values(<<-SQL, 'SCHEMA') SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false)) SQL end def table_exists?(name) schema, table = extract_schema_and_table(name.to_s) return false unless table # abstract classes - nil table name binds = [[ nil, table.gsub(/(^"|"$)/,'') ]] binds << [ nil, schema ] if schema sql = <<-SQL SELECT COUNT(*) as table_count FROM pg_tables WHERE tablename = ? AND schemaname = #{schema ? "?" : "ANY (current_schemas(false))"} SQL log(sql, 'SCHEMA', binds) do @connection.execute_query_raw(sql, binds).first["table_count"] > 0 end end # @private IndexDefinition = ::ActiveRecord::ConnectionAdapters::IndexDefinition if ActiveRecord::VERSION::MAJOR < 3 || ( ActiveRecord::VERSION::MAJOR == 3 && ActiveRecord::VERSION::MINOR <= 1 ) # NOTE: make sure we accept 6 arguments (>= 3.2) as well as 5 (<= 3.1) : # allow 6 on 3.1 : Struct.new(:table, :name, :unique, :columns, :lengths) IndexDefinition.class_eval do def initialize(table, name, unique = nil, columns = nil, lengths = nil, orders = nil) super(table, name, unique, columns, lengths) # @see {#indexes} end end end # Returns an array of indexes for the given table. def indexes(table_name, name = nil) # NOTE: maybe it's better to leave things of to the JDBC API ?! result = select_rows(<<-SQL, 'SCHEMA') SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid WHERE i.relkind = 'i' AND d.indisprimary = 'f' AND t.relname = '#{table_name}' AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) ) ORDER BY i.relname SQL result.map! do |row| index_name = row[0] unique = row[1].is_a?(String) ? row[1] == 't' : row[1] # JDBC gets us a boolean indkey = row[2].is_a?(Java::OrgPostgresqlUtil::PGobject) ? row[2].value : row[2] indkey = indkey.split(" ") inddef = row[3] oid = row[4] columns = select_rows(<<-SQL, "SCHEMA") SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}) SQL columns = Hash[ columns.each { |column| column[0] = column[0].to_s } ] column_names = columns.values_at(*indkey).compact unless column_names.empty? # add info on sort order for columns (only desc order is explicitly specified, asc is the default) desc_order_columns = inddef.scan(/(\w+) DESC/).flatten orders = desc_order_columns.any? ? Hash[ desc_order_columns.map { |column| [column, :desc] } ] : {} if ActiveRecord::VERSION::MAJOR > 3 # AR4 supports `where` and `using` index options where = inddef.scan(/WHERE (.+)$/).flatten[0] using = inddef.scan(/USING (.+?) /).flatten[0].to_sym IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using) else IndexDefinition.new(table_name, index_name, unique, column_names, [], orders) end end end result.compact! result end private def translate_exception(exception, message) case exception.message when /duplicate key value violates unique constraint/ ::ActiveRecord::RecordNotUnique.new(message, exception) when /violates foreign key constraint/ ::ActiveRecord::InvalidForeignKey.new(message, exception) else super end end # Extracts the table and schema name from +name+ def extract_schema_and_table(name) schema, table = name.split('.', 2) unless table # A table was provided without a schema table = schema schema = nil end if name =~ /^"/ # Handle quoted table names table = name schema = nil end [schema, table] end def extract_pg_identifier_from_name(name) match_data = name[0, 1] == '"' ? name.match(/\"([^\"]+)\"/) : name.match(/([^\.]+)/) if match_data rest = name[match_data[0].length..-1] rest = rest[1..-1] if rest[0, 1] == "." [match_data[1], (rest.length > 0 ? rest : nil)] end end def extract_table_ref_from_insert_sql(sql) sql[/into\s+([^\(]*).*values\s*\(/i] $1.strip if $1 end def local_tz @local_tz ||= execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] end end end require 'arjdbc/util/quoted_cache' module ActiveRecord::ConnectionAdapters remove_const(:PostgreSQLColumn) if const_defined?(:PostgreSQLColumn) class PostgreSQLColumn < JdbcColumn include ::ArJdbc::PostgreSQL::Column def initialize(name, default, oid_type = nil, sql_type = nil, null = true) # NOTE: we support AR <= 3.2 : (name, default, sql_type = nil, null = true) null, sql_type, oid_type = !! sql_type, oid_type, nil unless oid_type.is_a?(Integer) @oid_type = oid_type if sql_type =~ /\[\]$/ @array = true if respond_to?(:array) super(name, default, sql_type[0..sql_type.length - 3], null) else @array = false if respond_to?(:array) super(name, default, sql_type, null) end end end remove_const(:PostgreSQLAdapter) if const_defined?(:PostgreSQLAdapter) class PostgreSQLAdapter < JdbcAdapter include ::ArJdbc::PostgreSQL include ::ArJdbc::PostgreSQL::ExplainSupport include ::ArJdbc::Util::QuotedCache def initialize(*args) # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil super # configure_connection happens in super @table_alias_length = nil @use_insert_returning = config.key?(:insert_returning) ? self.class.type_cast_config_to_boolean(config[:insert_returning]) : nil end class ColumnDefinition < ActiveRecord::ConnectionAdapters::ColumnDefinition attr_accessor :array end module ColumnMethods def xml(*args) options = args.extract_options! column(args[0], 'xml', options) end def tsvector(*args) options = args.extract_options! column(args[0], 'tsvector', options) end def int4range(name, options = {}) column(name, 'int4range', options) end def int8range(name, options = {}) column(name, 'int8range', options) end def tsrange(name, options = {}) column(name, 'tsrange', options) end def tstzrange(name, options = {}) column(name, 'tstzrange', options) end def numrange(name, options = {}) column(name, 'numrange', options) end def daterange(name, options = {}) column(name, 'daterange', options) end def hstore(name, options = {}) column(name, 'hstore', options) end def ltree(name, options = {}) column(name, 'ltree', options) end def inet(name, options = {}) column(name, 'inet', options) end def cidr(name, options = {}) column(name, 'cidr', options) end def macaddr(name, options = {}) column(name, 'macaddr', options) end def uuid(name, options = {}) column(name, 'uuid', options) end def json(name, options = {}) column(name, 'json', options) end end class TableDefinition < ActiveRecord::ConnectionAdapters::TableDefinition include ColumnMethods def primary_key(name, type = :primary_key, options = {}) return super unless type == :uuid options[:default] ||= 'uuid_generate_v4()' options[:primary_key] = true column name, type, options end if ActiveRecord::VERSION::MAJOR > 3 # 3.2 super expects (name) def column(name, type = nil, options = {}) super column = self[name] # NOTE: <= 3.1 no #new_column_definition hard-coded ColumnDef.new : # column = self[name] || ColumnDefinition.new(@base, name, type) # thus we simply do not support array column definitions on <= 3.1 column.array = options[:array] if column.is_a?(ColumnDefinition) self end private if ActiveRecord::VERSION::MAJOR > 3 def create_column_definition(name, type) ColumnDefinition.new name, type end else # no #create_column_definition on 3.2 def new_column_definition(base, name, type) definition = ColumnDefinition.new base, name, type @columns << definition @columns_hash[name] = definition definition end end end def table_definition(*args) new_table_definition(TableDefinition, *args) end class Table < ActiveRecord::ConnectionAdapters::Table include ColumnMethods end def update_table_definition(table_name, base) Table.new(table_name, base) end if ActiveRecord::VERSION::MAJOR > 3 def jdbc_connection_class(spec) ::ArJdbc::PostgreSQL.jdbc_connection_class end # @see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_column_class def jdbc_column_class ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn end end end