ArJdbc::PostgreSQL

    # File lib/arjdbc/postgresql/adapter.rb, line 13
13:     def self.column_selector
14:       [/postgre/, lambda {|cfg,col| col.extend(::ArJdbc::PostgreSQL::Column)}]
15:     end

    # File lib/arjdbc/postgresql/adapter.rb, line 7
 7:     def self.extended(mod)
 8:       mod.class.class_eval do
 9:         alias_chained_method :insert, :query_dirty, :insert
10:       end
11:     end

    # File lib/arjdbc/postgresql/adapter.rb, line 17
17:     def self.jdbc_connection_class
18:       ::ActiveRecord::ConnectionAdapters::PostgresJdbcConnection
19:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 476
476:     def add_column(table_name, column_name, type, options = {})
477:       execute("ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}")
478:       change_column_default(table_name, column_name, options[:default]) unless options[:default].nil?
479:       if options[:null] == false
480:         execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)} = '#{options[:default]}'") if options[:default]
481:         execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} SET NOT NULL")
482:       end
483:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 402
402:     def add_order_by_for_association_limiting!(sql, options)
403:       return sql if options[:order].blank?
404: 
405:       order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
406:       order.map! { |s| 'DESC' if s =~ /\bdesc$/ }
407:       order = order.zip((0...order.size).to_a).map { |s,i| "id_list.alias_#{i} #{s}" }.join(', ')
408: 
409:       sql.replace "SELECT * FROM (#{sql}) AS id_list ORDER BY #{order}"
410:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 340
340:     def all_schemas
341:       select('select nspname from pg_namespace').map {|r| r["nspname"] }
342:     end

    # File lib/arjdbc/postgresql/adapter.rb, line 93
93:     def arel2_visitors
94:       {'jdbcpostgresql' => ::Arel::Visitors::PostgreSQL}
95:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 504
504:     def change_column_null(table_name, column_name, null, default = nil)
505:       unless null || default.nil?
506:         execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
507:       end
508:       execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
509:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 271
271:     def columns(table_name, name=nil)
272:       schema_name = @config[:schema_search_path]
273:       if table_name =~ /\./
274:         parts = table_name.split(/\./)
275:         table_name = parts.pop
276:         schema_name = parts.join(".")
277:       end
278:       @connection.columns_internal(table_name, name, schema_name)
279:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 324
324:     def create_database(name, options = {})
325:       execute "CREATE DATABASE \"#{name}\" ENCODING='#{options[:encoding] || 'utf8'}'"
326:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 146
146:     def create_savepoint
147:       execute("SAVEPOINT #{current_savepoint_name}")
148:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 332
332:     def create_schema(schema_name, pg_username)
333:       execute("CREATE SCHEMA \"#{schema_name}\" AUTHORIZATION \"#{pg_username}\"")
334:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 164
164:     def default_sequence_name(table_name, pk = nil)
165:       default_pk, default_seq = pk_and_sequence_for(table_name)
166:       default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq"
167:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 383
383:     def distinct(columns, order_by)
384:       return "DISTINCT #{columns}" if order_by.blank?
385: 
386:       # construct a clean list of column names from the ORDER BY clause, removing
387:       # any asc/desc modifiers
388:       order_columns = order_by.split(',').collect { |s| s.split.first }
389:       order_columns.delete_if(&:blank?)
390:       order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }
391: 
392:       # return a DISTINCT ON() clause that's distinct on the columns we want but includes
393:       # all the required columns for the ORDER BY to work properly
394:       sql = "DISTINCT ON (#{columns}) #{columns}, "
395:       sql << order_columns * ', '
396:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 328
328:     def drop_database(name)
329:       execute "DROP DATABASE IF EXISTS \"#{name}\""
330:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 336
336:     def drop_schema(schema_name)
337:       execute("DROP SCHEMA \"#{schema_name}\"")
338:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 434
434:     def escape_bytea(s)
435:       if s
436:         result = ''
437:         s.each_byte { |c| result << sprintf('\\%03o', c) }
438:         result
439:       end
440:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 282
282:     def indexes(table_name, name = nil)
283:       result = select_rows(        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, name)
284: 
285:       current_index = nil
286:       indexes = []
287: 
288:       result.each do |row|
289:         if current_index != row[0]
290:           indexes << ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(table_name, row[0], row[1] == "t", [])
291:           current_index = row[0]
292:         end
293: 
294:         indexes.last.columns << row[2]
295:       end
296: 
297:       indexes
298:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 237
237:     def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
238:       # Extract the table from the insert sql. Yuck.
239:       table = sql.split(" ", 4)[2].gsub('"', '')
240: 
241:       # Try an insert with 'returning id' if available (PG >= 8.2)
242:       if supports_insert_with_returning? && id_value.nil?
243:         pk, sequence_name = *pk_and_sequence_for(table) unless pk
244:         if pk
245:           id_value = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
246:           clear_query_cache #FIXME: Why now?
247:           return id_value
248:         end
249:       end
250: 
251:       # Otherwise, plain insert
252:       execute(sql, name)
253: 
254:       # Don't need to look up id_value if we already have it.
255:       # (and can't in case of non-sequence PK)
256:       unless id_value
257:         # If neither pk nor sequence name is given, look them up.
258:         unless pk || sequence_name
259:           pk, sequence_name = *pk_and_sequence_for(table)
260:         end
261: 
262:         # If a pk is given, fallback to default sequence name.
263:         # Don't fetch last insert id for a table without a pk.
264:         if pk && sequence_name ||= default_sequence_name(table, pk)
265:           id_value = last_insert_id(table, sequence_name)
266:         end
267:       end
268:       id_value
269:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 315
315:     def last_insert_id(table, sequence_name)
316:       Integer(select_value("SELECT currval('#{sequence_name}')"))
317:     end

    # File lib/arjdbc/postgresql/adapter.rb, line 79
79:     def modify_types(tp)
80:       tp[:primary_key] = "serial primary key"
81:       tp[:string][:limit] = 255
82:       tp[:integer][:limit] = nil
83:       tp[:boolean] = { :name => "boolean" }
84:       tp[:float] = { :name => "float" }
85:       tp[:decimal] = { :name => "decimal" }
86:       tp
87:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 97
 97:     def postgresql_version
 98:       @postgresql_version ||=
 99:         begin
100:           value = select_value('SELECT version()')
101:           if value =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
102:             ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
103:           else
104:             0
105:           end
106:         end
107:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 344
344:     def primary_key(table)
345:       pk_and_sequence = pk_and_sequence_for(table)
346:       pk_and_sequence && pk_and_sequence.first
347:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 453
453:     def quote_column_name(name)
454:       %("#{name}")
455:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 442
442:     def quote_table_name(name)
443:       schema, name_part = extract_pg_identifier_from_name(name.to_s)
444: 
445:       unless name_part
446:         quote_column_name(schema)
447:       else
448:         table_name, name_part = extract_pg_identifier_from_name(name_part)
449:         "#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
450:       end
451:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 319
319:     def recreate_database(name)
320:       drop_database(name)
321:       create_database(name)
322:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 154
154:     def release_savepoint
155:       execute("RELEASE SAVEPOINT #{current_savepoint_name}")
156:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 472
472:     def rename_table(name, new_name)
473:       execute "ALTER TABLE #{name} RENAME TO #{new_name}"
474:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 150
150:     def rollback_to_savepoint
151:       execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
152:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 349
349:     def structure_dump
350:       database = @config[:database]
351:       if database.nil?
352:         if @config[:url] =~ /\/([^\/]*)$/
353:           database = $1
354:         else
355:           raise "Could not figure out what database this url is for #{@config["url"]}"
356:         end
357:       end
358: 
359:       ENV['PGHOST']     = @config[:host] if @config[:host]
360:       ENV['PGPORT']     = @config[:port].to_s if @config[:port]
361:       ENV['PGPASSWORD'] = @config[:password].to_s if @config[:password]
362:       search_path = @config[:schema_search_path]
363:       search_path = "--schema=#{search_path}" if search_path
364: 
365:       @connection.connection.close
366:       begin
367:         definition = `pg_dump -i -U "#{@config[:username]}" -s -x -O #{search_path} #{database}`
368:         raise "Error dumping database" if $?.exitstatus == 1
369: 
370:         # need to patch away any references to SQL_ASCII as it breaks the JDBC driver
371:         definition.gsub(/SQL_ASCII/, 'UNICODE')
372:       ensure
373:         reconnect!
374:       end
375:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 134
134:     def supports_ddl_transactions?
135:       true
136:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 130
130:     def supports_insert_with_returning?
131:       postgresql_version >= 80200
132:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 110
110:     def supports_migrations?
111:       true
112:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 138
138:     def supports_savepoints?
139:       true
140:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 115
115:     def supports_standard_conforming_strings?
116:       # Temporarily set the client message level above error to prevent unintentional
117:       # error messages in the logs when working on a PostgreSQL database server that
118:       # does not support standard conforming strings.
119:       client_min_messages_old = client_min_messages
120:       self.client_min_messages = 'panic'
121: 
122:       # postgres-pr does not raise an exception when client_min_messages is set higher
123:       # than error and "SHOW standard_conforming_strings" fails, but returns an empty
124:       # PGresult instead.
125:       has_support = select('SHOW standard_conforming_strings').to_a[0][0] rescue false
126:       self.client_min_messages = client_min_messages_old
127:       has_support
128:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 160
160:     def table_alias_length
161:       @table_alias_length ||= (postgresql_version >= 80000 ? select_one('SHOW max_identifier_length')['max_identifier_length'].to_i : 63)
162:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 531
531:     def tables
532:       @connection.tables(database_name, nil, nil, ["TABLE"])
533:     end

     # File lib/arjdbc/postgresql/adapter.rb, line 536
536:     def extract_pg_identifier_from_name(name)
537:       match_data = name[0,1] == '"' ? name.match(/\"([^\"]+)\"/) : name.match(/([^\.]+)/)
538: 
539:       if match_data
540:         rest = name[match_data[0].length..1]
541:         rest = rest[1..1] if rest[0,1] == "."
542:         [match_data[1], (rest.length > 0 ? rest : nil)]
543:       end
544:     end