Class: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/active_record/connection_adapters/postgresql_adapter.rb

Overview

The PostgreSQL adapter works both with the C-based (www.postgresql.jp/interfaces/ruby/) and the Ruby-base (available both as gem and from rubyforge.org/frs/?group_id=234&release_id=1145) drivers.

Options:

  • :host – Defaults to localhost

  • :port – Defaults to 5432

  • :username – Defaults to nothing

  • :password – Defaults to nothing

  • :database – The name of the database. No default, must be provided.

  • :schema_search_path – An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the :schema_order option.

  • :encoding – An optional client encoding that is using in a SET client_encoding TO <encoding> call on connection.

  • :min_messages – An optional client min messages that is using in a SET client_min_messages TO <min_messages> call on connection.

Instance Method Summary collapse

Methods inherited from AbstractAdapter

#prefetch_primary_key?, #raw_connection, #reset_runtime, #supports_count_distinct?, #verify!

Methods included from Quoting

#quote_string, #quoted_date, #quoted_false, #quoted_true

Methods included from DatabaseStatements

#add_limit!, #add_limit_offset!, #reset_sequence!, #select_value, #select_values, #transaction

Methods included from SchemaStatements

#add_column_options!, #add_index, #create_table, #drop_table, #dump_schema_information, #index_name, #initialize_schema_information, #remove_column, #structure_dump, #table_alias_for, #type_to_sql

Constructor Details

#initialize(connection, logger, config = {}) ⇒ PostgreSQLAdapter

Returns a new instance of PostgreSQLAdapter.



54
55
56
57
58
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 54

def initialize(connection, logger, config = {})
  super(connection, logger)
  @config = config
  configure_connection
end

Instance Method Details

#active? ⇒ Boolean

Is this connection alive and ready for queries?

Returns:

  • (Boolean) 


61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 61

def active?
  if @connection.respond_to?(:status)
    @connection.status == PGconn::CONNECTION_OK
  else
    @connection.query 'SELECT 1'
    true
  end
# postgres-pr raises a NoMethodError when querying if no conn is available
rescue PGError, NoMethodError
  false      
end

#adapter_name ⇒ Object 



50
51
52
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 50

def adapter_name
  'PostgreSQL'
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object 



309
310
311
312
313
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 309

def add_column(table_name, column_name, type, options = {})
  execute("ALTER TABLE #{table_name} ADD #{column_name} #{type_to_sql(type, options[:limit])}")
  execute("ALTER TABLE #{table_name} ALTER #{column_name} SET NOT NULL") if options[:null] == false
  change_column_default(table_name, column_name, options[:default]) unless options[:default].nil?
end

#begin_db_transaction ⇒ Object

:nodoc:



158
159
160
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 158

def begin_db_transaction #:nodoc:
  execute "BEGIN"
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



315
316
317
318
319
320
321
322
323
324
325
326
327
328
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 315

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  begin
    execute "ALTER TABLE #{table_name} ALTER  #{column_name} TYPE #{type_to_sql(type, options[:limit])}"
  rescue ActiveRecord::StatementInvalid
    # This is PG7, so we use a more arcane way of doing it.
    begin_db_transaction
    add_column(table_name, "#{column_name}_ar_tmp", type, options)
    execute "UPDATE #{table_name} SET #{column_name}_ar_tmp = CAST(#{column_name} AS #{type_to_sql(type, options[:limit])})"
    remove_column(table_name, column_name)
    rename_column(table_name, "#{column_name}_ar_tmp", column_name)
    commit_db_transaction
  end
  change_column_default(table_name, column_name, options[:default]) unless options[:default].nil?
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



330
331
332
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 330

def change_column_default(table_name, column_name, default) #:nodoc:
  execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DEFAULT '#{default}'"
end

#columns(table_name, name = nil) ⇒ Object

:nodoc:



216
217
218
219
220
221
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 216

def columns(table_name, name = nil) #:nodoc:
  column_definitions(table_name).collect do |name, type, default, notnull|
    Column.new(name, default_value(default), translate_field_type(type),
      notnull == "f")
  end
end

#commit_db_transaction ⇒ Object

:nodoc:



162
163
164
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 162

def commit_db_transaction #:nodoc:
  execute "COMMIT"
end

#default_sequence_name(table_name, pk = nil) ⇒ Object 



237
238
239
240
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 237

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

#disconnect! ⇒ Object 



82
83
84
85
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 82

def disconnect!
  # Both postgres and postgres-pr respond to :close
  @connection.close rescue nil
end

#execute(sql, name = nil) ⇒ Object

:nodoc:



147
148
149
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 147

def execute(sql, name = nil) #:nodoc:
  log(sql, name) { @connection.exec(sql) }
end

#indexes(table_name, name = nil) ⇒ Object

:nodoc:



183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 183

def indexes(table_name, name = nil) #:nodoc:
  result = query(<<-SQL, name)
    SELECT i.relname, d.indisunique, a.attname
      FROM pg_class t, pg_class i, pg_index d, pg_attribute a
     WHERE i.relkind = 'i'
       AND d.indexrelid = i.oid
       AND d.indisprimary = 'f'
       AND t.oid = d.indrelid
       AND t.relname = '#{table_name}'
       AND a.attrelid = t.oid
       AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
          OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
          OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
          OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
          OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
    ORDER BY i.relname
  SQL

  current_index = nil
  indexes = []

  result.each do |row|
    if current_index != row[0]
      indexes << IndexDefinition.new(table_name, row[0], row[1] == "t", [])
      current_index = row[0]
    end

    indexes.last.columns << row[2]
  end

  indexes
end

#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object

:nodoc:



137
138
139
140
141
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 137

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  execute(sql, name)
  table = sql.split(" ", 4)[2]
  id_value || last_insert_id(table, sequence_name || default_sequence_name(table, pk))
end

#native_database_types ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 87

def native_database_types
  {
    :primary_key => "serial primary key",
    :string      => { :name => "character varying", :limit => 255 },
    :text        => { :name => "text" },
    :integer     => { :name => "integer" },
    :float       => { :name => "float" },
    :datetime    => { :name => "timestamp" },
    :timestamp   => { :name => "timestamp" },
    :time        => { :name => "time" },
    :date        => { :name => "date" },
    :binary      => { :name => "bytea" },
    :boolean     => { :name => "boolean" }
  }
end

#pk_and_sequence_for(table) ⇒ Object

Find a table’s primary key and sequence.



261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 261

def pk_and_sequence_for(table)
  # First try looking for a sequence with a dependency on the
  # given table's primary key.
  result = execute(<<-end_sql, 'PK and serial sequence')[0]
    SELECT attr.attname, name.nspname, seq.relname
    FROM pg_class      seq,
         pg_attribute  attr,
         pg_depend     dep,
         pg_namespace  name,
         pg_constraint cons
    WHERE seq.oid           = dep.objid
      AND seq.relnamespace  = name.oid
      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      = '#{table}'::regclass
  end_sql

  if result.nil? or result.empty?
    # If that fails, try parsing the primary key's default value.
    # Support the 7.x and 8.0 nextval('foo'::text) as well as
    # the 8.1+ nextval('foo'::regclass).
    # TODO: assumes sequence is in same schema as table.
    result = execute(<<-end_sql, 'PK and custom sequence')[0]
      SELECT attr.attname, name.nspname, split_part(def.adsrc, '\\\'', 2)
      FROM pg_class       t
      JOIN pg_namespace   name ON (t.relnamespace = name.oid)
      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 = '#{table}'::regclass
        AND cons.contype = 'p'
        AND def.adsrc ~* 'nextval'
    end_sql
  end
  # check for existence of . in sequence name as in public.foo_sequence.  if it does not exist, join the current namespace
  result.last['.'] ? [result.first, result.last] : [result.first, "#{result[1]}.#{result[2]}"]
rescue
  nil
end

#query(sql, name = nil) ⇒ Object

:nodoc:



143
144
145
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 143

def query(sql, name = nil) #:nodoc:
  log(sql, name) { @connection.query(sql) }
end

#quote(value, column = nil) ⇒ Object

QUOTING ==================================================



113
114
115
116
117
118
119
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 113

def quote(value, column = nil)
  if value.kind_of?(String) && column && column.type == :binary
    "'#{escape_bytea(value)}'"
  else
    super
  end
end

#quote_column_name(name) ⇒ Object 



121
122
123
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 121

def quote_column_name(name)
  %("#{name}")
end

#reconnect! ⇒ Object

Close then reopen the connection.



74
75
76
77
78
79
80
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 74

def reconnect!
  # TODO: postgres-pr doesn't have PGconn#reset.
  if @connection.respond_to?(:reset)
    @connection.reset
    configure_connection
  end
end

#remove_index(table_name, options) ⇒ Object

:nodoc:



338
339
340
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 338

def remove_index(table_name, options) #:nodoc:
  execute "DROP INDEX #{index_name(table_name, options)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



334
335
336
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 334

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  execute "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} TO #{new_column_name}"
end

#rename_table(name, new_name) ⇒ Object 



305
306
307
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 305

def rename_table(name, new_name)
  execute "ALTER TABLE #{name} RENAME TO #{new_name}"
end

#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object

Resets sequence to the max value of the table’s pk if present.



243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 243

def reset_pk_sequence!(table, pk = nil, sequence = nil)
  unless pk and sequence
    default_pk, default_sequence = pk_and_sequence_for(table)
    pk ||= default_pk
    sequence ||= default_sequence
  end
  if pk
    if sequence
      select_value <<-end_sql, 'Reset sequence'
        SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence})) FROM #{table}), false)
      end_sql
    else
      @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger
    end
  end
end

#rollback_db_transaction ⇒ Object

:nodoc:



166
167
168
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 166

def rollback_db_transaction #:nodoc:
  execute "ROLLBACK"
end

#schema_search_path ⇒ Object

:nodoc:



233
234
235
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 233

def schema_search_path #:nodoc:
  @schema_search_path ||= query('SHOW search_path')[0][0]
end

#schema_search_path=(schema_csv) ⇒ Object

Set the schema search path to a string of comma-separated schema names. Names beginning with $ are quoted (e.g. $user => ‘$user’) See www.postgresql.org/docs/8.0/interactive/ddl-schemas.html



226
227
228
229
230
231
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 226

def schema_search_path=(schema_csv) #:nodoc:
  if schema_csv
    execute "SET search_path TO #{schema_csv}"
    @schema_search_path = nil
  end
end

#select_all(sql, name = nil) ⇒ Object

DATABASE STATEMENTS ======================================



128
129
130
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 128

def select_all(sql, name = nil) #:nodoc:
  select(sql, name)
end

#select_one(sql, name = nil) ⇒ Object

:nodoc:



132
133
134
135
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 132

def select_one(sql, name = nil) #:nodoc:
  result = select(sql, name)
  result.first if result
end

#supports_migrations? ⇒ Boolean

Returns:

  • (Boolean) 


103
104
105
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 103

def supports_migrations?
  true
end

#table_alias_length ⇒ Object 



107
108
109
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 107

def table_alias_length
  63
end

#tables(name = nil) ⇒ Object

Return the list of all tables in the schema search path.



174
175
176
177
178
179
180
181
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 174

def tables(name = nil) #:nodoc:
  schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
  query(<<-SQL, name).map { |row| row[0] }
    SELECT tablename
      FROM pg_tables
     WHERE schemaname IN (#{schemas})
  SQL
end

#update(sql, name = nil) ⇒ Object Also known as: delete

:nodoc:



151
152
153
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 151

def update(sql, name = nil) #:nodoc:
  execute(sql, name).cmdtuples
end