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?, #reset_runtime

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, #type_to_sql

Constructor Details

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

Returns a new instance of PostgreSQLAdapter.



52
53
54
55
56
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 52

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) 


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

def active?
  if @connection.respond_to?(:status)
    @connection.status == PGconn::CONNECTION_OK
  else
    @connection.query 'SELECT 1'
    true
  end
rescue PGError
  false
end

#adapter_name ⇒ Object 



48
49
50
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 48

def adapter_name
  'PostgreSQL'
end

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



298
299
300
301
302
303
304
305
306
307
308
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 298

def add_column(table_name, column_name, type, options = {})
  native_type = native_database_types[type]
  sql_commands = ["ALTER TABLE #{table_name} ADD #{column_name} #{type_to_sql(type, options[:limit])}"]
  if options[:default]
    sql_commands << "ALTER TABLE #{table_name} ALTER #{column_name} SET DEFAULT '#{options[:default]}'"
  end
  if options[:null] == false
    sql_commands << "ALTER TABLE #{table_name} ALTER #{column_name} SET NOT NULL"
  end
  sql_commands.each { |cmd| execute(cmd) }
end

#begin_db_transaction ⇒ Object

:nodoc:



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

def begin_db_transaction #:nodoc:
  execute "BEGIN"
end

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

:nodoc:



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

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  execute = "ALTER TABLE #{table_name} ALTER  #{column_name} TYPE #{type}"
  change_column_default(table_name, column_name, options[:default]) unless options[:default].nil?
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



315
316
317
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 315

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:



205
206
207
208
209
210
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 205

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:



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

def commit_db_transaction #:nodoc:
  execute "COMMIT"
end

#default_sequence_name(table_name, pk = nil) ⇒ Object 



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

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

#execute(sql, name = nil) ⇒ Object

:nodoc:



136
137
138
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 136

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

#indexes(table_name, name = nil) ⇒ Object

:nodoc:



172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 172

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:



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

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 



79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 79

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.



250
251
252
253
254
255
256
257
258
259
260
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
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 250

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:



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

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

#quote(value, column = nil) ⇒ Object

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



102
103
104
105
106
107
108
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 102

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 



110
111
112
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 110

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

#reconnect! ⇒ Object

Close then reopen the connection.



71
72
73
74
75
76
77
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 71

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:



323
324
325
326
327
328
329
330
331
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 323

def remove_index(table_name, options) #:nodoc:
  if Hash === options
    index_name = options[:name]
  else
    index_name = "#{table_name}_#{options}_index"
  end

  execute "DROP INDEX #{index_name}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



319
320
321
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 319

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 



294
295
296
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 294

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.



232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 232

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:



155
156
157
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 155

def rollback_db_transaction #:nodoc:
  execute "ROLLBACK"
end

#schema_search_path ⇒ Object

:nodoc:



222
223
224
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 222

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



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

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 ======================================



117
118
119
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 117

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

#select_one(sql, name = nil) ⇒ Object

:nodoc:



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

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

#supports_migrations? ⇒ Boolean

Returns:

  • (Boolean) 


95
96
97
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 95

def supports_migrations?
  true
end

#tables(name = nil) ⇒ Object

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



163
164
165
166
167
168
169
170
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 163

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:



140
141
142
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 140

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