Module: ArJdbc::PostgreSQL

Defined in:
lib/arjdbc/postgresql/adapter.rb

Defined Under Namespace

Modules: Column

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.column_selector ⇒ Object 



13
14
15
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 13

def self.column_selector
  [/postgre/i, lambda {|cfg,col| col.extend(::ArJdbc::PostgreSQL::Column)}]
end

.extended(mod) ⇒ Object 



7
8
9
10
11
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 7

def self.extended(mod)
  mod.class.class_eval do
    alias_chained_method :insert, :query_dirty, :insert
  end
end

.jdbc_connection_class ⇒ Object 



17
18
19
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 17

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::PostgresJdbcConnection
end

Instance Method Details

#adapter_name ⇒ Object

:nodoc:



80
81
82
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 80

def adapter_name #:nodoc:
  'PostgreSQL'
end

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



463
464
465
466
467
468
469
470
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 463

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

#add_order_by_for_association_limiting!(sql, options) ⇒ Object

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.



389
390
391
392
393
394
395
396
397
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 389

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

#all_schemas ⇒ Object 



327
328
329
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 327

def all_schemas
  select('select nspname from pg_namespace').map {|r| r["nspname"] }
end

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

:nodoc:



472
473
474
475
476
477
478
479
480
481
482
483
484
485
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 472

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  begin
    execute "ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  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], options[:precision], options[:scale])})"
    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:



487
488
489
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 487

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

#change_column_null(table_name, column_name, null, default = nil) ⇒ Object 



491
492
493
494
495
496
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 491

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

#columns(table_name, name = nil) ⇒ Object 



258
259
260
261
262
263
264
265
266
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 258

def columns(table_name, name=nil)
  schema_name = @config[:schema_search_path]
  if table_name =~ /\./
    parts = table_name.split(/\./)
    table_name = parts.pop
    schema_name = parts.join(".")
  end
  @connection.columns_internal(table_name, name, schema_name)
end

#create_database(name, options = {}) ⇒ Object 



311
312
313
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 311

def create_database(name, options = {})
  execute "CREATE DATABASE \"#{name}\" ENCODING='#{options[:encoding] || 'utf8'}'"
end

#create_savepoint ⇒ Object 



133
134
135
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 133

def create_savepoint
  execute("SAVEPOINT #{current_savepoint_name}")
end

#create_schema(schema_name, pg_username) ⇒ Object 



319
320
321
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 319

def create_schema(schema_name, pg_username)
  execute("CREATE SCHEMA \"#{schema_name}\" AUTHORIZATION \"#{pg_username}\"")
end

#default_sequence_name(table_name, pk = nil) ⇒ Object 



151
152
153
154
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 151

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

#disable_referential_integrity(&block) ⇒ Object

:nodoc:



452
453
454
455
456
457
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 452

def disable_referential_integrity(&block) #:nodoc:
  execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
  yield
ensure
  execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))
end

#distinct(columns, order_by) ⇒ Object

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")



370
371
372
373
374
375
376
377
378
379
380
381
382
383
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 370

def distinct(columns, order_by)
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a clean list of column names from the ORDER BY clause, removing
  # any asc/desc modifiers
  order_columns = order_by.split(',').collect { |s| s.split.first }
  order_columns.delete_if(&:blank?)
  order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }

  # return a DISTINCT ON() clause that's distinct on the columns we want but includes
  # all the required columns for the ORDER BY to work properly
  sql = "DISTINCT ON (#{columns}) #{columns}, "
  sql << order_columns * ', '
end

#drop_database(name) ⇒ Object 



315
316
317
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 315

def drop_database(name)
  execute "DROP DATABASE IF EXISTS \"#{name}\""
end

#drop_schema(schema_name) ⇒ Object 



323
324
325
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 323

def drop_schema(schema_name)
  execute("DROP SCHEMA \"#{schema_name}\"")
end

#escape_bytea(s) ⇒ Object 



421
422
423
424
425
426
427
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 421

def escape_bytea(s)
  if s
    result = ''
    s.each_byte { |c| result << sprintf('\\\\%03o', c) }
    result
  end
end

#indexes(table_name, name = nil) ⇒ Object

From postgresql_adapter.rb



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
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 269

def indexes(table_name, name = nil)
  result = select_rows("SELECT i.relname, d.indisunique, a.attname\nFROM pg_class t, pg_class i, pg_index d, pg_attribute a\nWHERE i.relkind = 'i'\nAND d.indexrelid = i.oid\nAND d.indisprimary = 'f'\nAND t.oid = d.indrelid\nAND t.relname = '\#{table_name}'\nAND a.attrelid = t.oid\nAND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum\nOR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum\nOR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum\nOR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum\nOR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )\nORDER BY i.relname\n", name)

  current_index = nil
  indexes = []

  result.each do |row|
    if current_index != row[0]
      indexes << ::ActiveRecord::ConnectionAdapters::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 



224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 224

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
  # Extract the table from the insert sql. Yuck.
  table = sql.split(" ", 4)[2].gsub('"', '')

  # Try an insert with 'returning id' if available (PG >= 8.2)
  if supports_insert_with_returning? && id_value.nil?
    pk, sequence_name = *pk_and_sequence_for(table) unless pk
    if pk
      id_value = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
      clear_query_cache #FIXME: Why now?
      return id_value
    end
  end

  # Otherwise, plain insert
  execute(sql, name)

  # Don't need to look up id_value if we already have it.
  # (and can't in case of non-sequence PK)
  unless id_value
    # If neither pk nor sequence name is given, look them up.
    unless pk || sequence_name
      pk, sequence_name = *pk_and_sequence_for(table)
    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, pk)
      id_value = last_insert_id(table, sequence_name)
    end
  end
  id_value
end

#last_insert_id(table, sequence_name) ⇒ Object 



302
303
304
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 302

def last_insert_id(table, sequence_name)
  Integer(select_value("SELECT currval('#{sequence_name}')"))
end

#modify_types(tp) ⇒ Object 



70
71
72
73
74
75
76
77
78
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 70

def modify_types(tp)
  tp[:primary_key] = "serial primary key"
  tp[:string][:limit] = 255
  tp[:integer][:limit] = nil
  tp[:boolean][:limit] = nil
  tp[:float] = { :name => "float" }
  tp[:decimal] = { :name => "decimal" }
  tp
end

#pk_and_sequence_for(table) ⇒ Object

Find a table’s primary key and sequence.



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
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 177

def pk_and_sequence_for(table) #:nodoc:
  # First try looking for a sequence with a dependency on the
  # given table's primary key.
  result = select("SELECT attr.attname, seq.relname\nFROM pg_class      seq,\npg_attribute  attr,\npg_depend     dep,\npg_namespace  name,\npg_constraint cons\nWHERE seq.oid           = dep.objid\nAND seq.relkind       = 'S'\nAND attr.attrelid     = dep.refobjid\nAND attr.attnum       = dep.refobjsubid\nAND attr.attrelid     = cons.conrelid\nAND attr.attnum       = cons.conkey[1]\nAND cons.contype      = 'p'\nAND dep.refobjid      = '\#{quote_table_name(table)}'::regclass\n", 'PK and serial sequence')[0]

  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).
    result = select("SELECT attr.attname,\nCASE\nWHEN split_part(def.adsrc, '''', 2) ~ '.' THEN\nsubstr(split_part(def.adsrc, '''', 2),\nstrpos(split_part(def.adsrc, '''', 2), '.')+1)\nELSE split_part(def.adsrc, '''', 2)\nEND as relname\nFROM pg_class       t\nJOIN pg_attribute   attr ON (t.oid = attrelid)\nJOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)\nJOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])\nWHERE t.oid = '\#{quote_table_name(table)}'::regclass\nAND cons.contype = 'p'\nAND def.adsrc ~* 'nextval'\n", 'PK and custom sequence')[0]
  end

  [result["attname"], result["relname"]]
rescue
  nil
end

#postgresql_version ⇒ Object 



84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 84

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

#primary_key(table) ⇒ Object 



331
332
333
334
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 331

def primary_key(table)
  pk_and_sequence = pk_and_sequence_for(table)
  pk_and_sequence && pk_and_sequence.first
end

#quote(value, column = nil) ⇒ Object

:nodoc:



399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 399

def quote(value, column = nil) #:nodoc:
  return super unless column

  if value.kind_of?(String) && column.type == :binary
    "'#{escape_bytea(value)}'"
  elsif value.kind_of?(String) && column.sql_type == 'xml'
    "xml '#{quote_string(value)}'"
  elsif value.kind_of?(Numeric) && column.sql_type == 'money'
    # Not truly string input, so doesn't require (or allow) escape string syntax.
    "'#{value}'"
  elsif value.kind_of?(String) && column.sql_type =~ /^bit/
    case value
    when /^[01]*$/
      "B'#{value}'" # Bit-string notation
    when /^[0-9A-F]*$/i
      "X'#{value}'" # Hexadecimal notation
    end
  else
    super
  end
end

#quote_column_name(name) ⇒ Object 



440
441
442
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 440

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

#quote_table_name(name) ⇒ Object 



429
430
431
432
433
434
435
436
437
438
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 429

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

#quoted_date(value) ⇒ Object

:nodoc:



444
445
446
447
448
449
450
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 444

def quoted_date(value) #:nodoc:
  if value.acts_like?(:time) && value.respond_to?(:usec)
    "#{super}.#{sprintf("%06d", value.usec)}"
  else
    super
  end
end

#recreate_database(name) ⇒ Object 



306
307
308
309
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 306

def recreate_database(name)
  drop_database(name)
  create_database(name)
end

#release_savepoint ⇒ Object 



141
142
143
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 141

def release_savepoint
  execute("RELEASE SAVEPOINT #{current_savepoint_name}")
end

#remove_index(table_name, options) ⇒ Object

:nodoc:



502
503
504
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 502

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:



498
499
500
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 498

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

#rename_table(name, new_name) ⇒ Object 



459
460
461
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 459

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.



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 157

def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:
  unless pk and sequence
    default_pk, default_sequence = pk_and_sequence_for(table)
    pk ||= default_pk
    sequence ||= default_sequence
  end
  if pk
    if sequence
      quoted_sequence = quote_column_name(sequence)

      select_value "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)\n", 'Reset sequence'
    else
      @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger
    end
  end
end

#rollback_to_savepoint ⇒ Object 



137
138
139
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 137

def rollback_to_savepoint
  execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
end

#structure_dump ⇒ Object 



336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 336

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 = @config[:schema_search_path]
  search_path = "--schema=#{search_path}" if 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

#supports_count_distinct? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


129
130
131
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 129

def supports_count_distinct? #:nodoc:
  false
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


121
122
123
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 121

def supports_ddl_transactions?
  true
end

#supports_insert_with_returning? ⇒ Boolean

Returns:

  • (Boolean) 


117
118
119
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 117

def supports_insert_with_returning?
  postgresql_version >= 80200
end

#supports_migrations? ⇒ Boolean

Does PostgreSQL support migrations?

Returns:

  • (Boolean) 


97
98
99
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 97

def supports_migrations?
  true
end

#supports_savepoints? ⇒ Boolean

Returns:

  • (Boolean) 


125
126
127
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 125

def supports_savepoints?
  true
end

#supports_standard_conforming_strings? ⇒ Boolean

Does PostgreSQL support standard conforming strings?

Returns:

  • (Boolean) 


102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 102

def supports_standard_conforming_strings?
  # Temporarily set the client message level above error to prevent unintentional
  # error messages in the logs when working on a PostgreSQL database server that
  # does not support standard conforming strings.
  client_min_messages_old = client_min_messages
  self.client_min_messages = 'panic'

  # postgres-pr does not raise an exception when client_min_messages is set higher
  # than error and "SHOW standard_conforming_strings" fails, but returns an empty
  # PGresult instead.
  has_support = select('SHOW standard_conforming_strings').to_a[0][0] rescue false
  self.client_min_messages = client_min_messages_old
  has_support
end

#table_alias_length ⇒ Object

Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.



147
148
149
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 147

def table_alias_length
  @table_alias_length ||= (postgresql_version >= 80000 ? select_one('SHOW max_identifier_length')['max_identifier_length'].to_i : 63)
end

#tables ⇒ Object 



518
519
520
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 518

def tables
  @connection.tables(database_name, nil, nil, ["TABLE"])
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

:nodoc:



506
507
508
509
510
511
512
513
514
515
516
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 506

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  return super unless type.to_s == 'integer'

  if limit.nil? || limit == 4
    'integer'
  elsif limit < 4
    'smallint'
  else
    'bigint'
  end
end