Module: JdbcSpec::PostgreSQL
- Defined in:
- lib/jdbc_adapter/jdbc_postgre.rb
Defined Under Namespace
Modules: Column
Class Method Summary collapse
- .adapter_matcher(name) ⇒ Object
- .column_selector ⇒ Object
- .extended(mod) ⇒ Object
- .jdbc_connection_class ⇒ Object
Instance Method Summary collapse
- #_execute(sql, name = nil) ⇒ Object
- #add_column(table_name, column_name, type, options = {}) ⇒ Object
-
#add_order_by_for_association_limiting!(sql, options) ⇒ Object
ORDER BY clause for the passed order option.
-
#change_column(table_name, column_name, type, options = {}) ⇒ Object
:nodoc:.
-
#change_column_default(table_name, column_name, default) ⇒ Object
:nodoc:.
- #change_column_null(table_name, column_name, null, default = nil) ⇒ Object
- #columns(table_name, name = nil) ⇒ Object
- #create_database(name, options = {}) ⇒ Object
- #default_sequence_name(table_name, pk = nil) ⇒ Object
-
#disable_referential_integrity(&block) ⇒ Object
:nodoc:.
-
#distinct(columns, order_by) ⇒ Object
SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.
- #drop_database(name) ⇒ Object
- #escape_bytea(s) ⇒ Object
-
#indexes(table_name, name = nil) ⇒ Object
From postgresql_adapter.rb.
- #insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
- #last_insert_id(table, sequence_name) ⇒ Object
- #modify_types(tp) ⇒ Object
-
#pk_and_sequence_for(table) ⇒ Object
Find a table’s primary key and sequence.
- #postgresql_version ⇒ Object
- #quote(value, column = nil) ⇒ Object
- #quote_column_name(name) ⇒ Object
- #quote_regclass(table_name) ⇒ Object
-
#quoted_date(value) ⇒ Object
:nodoc:.
- #recreate_database(name) ⇒ Object
-
#remove_index(table_name, options) ⇒ Object
:nodoc:.
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
:nodoc:.
- #rename_table(name, new_name) ⇒ Object
-
#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object
Resets sequence to the max value of the table’s pk if present.
- #structure_dump ⇒ Object
- #supports_ddl_transactions? ⇒ Boolean
- #supports_insert_with_returning? ⇒ Boolean
-
#supports_migrations? ⇒ Boolean
Does PostgreSQL support migrations?.
- #supports_savepoints? ⇒ Boolean
-
#supports_standard_conforming_strings? ⇒ Boolean
Does PostgreSQL support standard conforming strings?.
-
#table_alias_length ⇒ Object
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
- #tables ⇒ Object
-
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
:nodoc:.
Class Method Details
.adapter_matcher(name) ⇒ Object
27 28 29 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 27 def self.adapter_matcher(name, *) name =~ /postgre/i ? self : false end |
.column_selector ⇒ Object
31 32 33 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 31 def self.column_selector [/postgre/i, lambda {|cfg,col| col.extend(::JdbcSpec::PostgreSQL::Column)}] end |
.extended(mod) ⇒ Object
21 22 23 24 25 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 21 def self.extended(mod) mod.class_eval do alias_chained_method :insert, :query_dirty, :insert end end |
.jdbc_connection_class ⇒ Object
35 36 37 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 35 def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::PostgresJdbcConnection end |
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
352 353 354 355 356 357 358 359 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 352 def _execute(sql, name = nil) case sql.strip when /\A\(?\s*(select|show)/i then @connection.execute_query(sql) else @connection.execute_update(sql) end end |
#add_column(table_name, column_name, type, options = {}) ⇒ Object
439 440 441 442 443 444 445 446 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 439 def add_column(table_name, column_name, type, = {}) execute("ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, [:limit])}") change_column_default(table_name, column_name, [:default]) unless [:default].nil? if [:null] == false execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)} = '#{[:default]}'") if [: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.
386 387 388 389 390 391 392 393 394 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 386 def add_order_by_for_association_limiting!(sql, ) return sql if [:order].blank? order = [: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 |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
:nodoc:
448 449 450 451 452 453 454 455 456 457 458 459 460 461 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 448 def change_column(table_name, column_name, type, = {}) #:nodoc: begin execute "ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} TYPE #{type_to_sql(type, [: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, ) execute "UPDATE #{table_name} SET #{column_name}_ar_tmp = CAST(#{column_name} AS #{type_to_sql(type, [: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, [:default]) unless [:default].nil? end |
#change_column_default(table_name, column_name, default) ⇒ Object
:nodoc:
463 464 465 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 463 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
467 468 469 470 471 472 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 467 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
263 264 265 266 267 268 269 270 271 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 263 def columns(table_name, name=nil) schema_name = "public" 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
316 317 318 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 316 def create_database(name, = {}) execute "CREATE DATABASE \"#{name}\" ENCODING='#{[:encoding] || 'utf8'}'" end |
#default_sequence_name(table_name, pk = nil) ⇒ Object
147 148 149 150 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 147 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:
428 429 430 431 432 433 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 428 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")
367 368 369 370 371 372 373 374 375 376 377 378 379 380 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 367 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
320 321 322 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 320 def drop_database(name) execute "DROP DATABASE \"#{name}\"" end |
#escape_bytea(s) ⇒ Object
408 409 410 411 412 413 414 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 408 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
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 304 305 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 274 def indexes(table_name, name = nil) result = select_rows(<<-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 << ::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
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 257 258 259 260 261 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 226 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? && false # FIXME: # Disabled, as it causes: # ActiveRecord::ActiveRecordError: A result was returned when none was expected # This was previously disabled because postgresql_version returned 0 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
307 308 309 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 307 def last_insert_id(table, sequence_name) Integer(select_value("SELECT currval('#{sequence_name}')")) end |
#modify_types(tp) ⇒ Object
88 89 90 91 92 93 94 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 88 def modify_types(tp) tp[:primary_key] = "serial primary key" tp[:string][:limit] = 255 tp[:integer][:limit] = nil tp[:boolean][:limit] = nil tp end |
#pk_and_sequence_for(table) ⇒ Object
Find a table’s primary key and sequence.
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 223 224 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 179 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(<<-end_sql, 'PK and serial sequence')[0] SELECT attr.attname, 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.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). result = query(<<-end_sql, 'PK and custom sequence')[0] SELECT attr.attname, CASE WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN substr(split_part(def.adsrc, '''', 2), strpos(split_part(def.adsrc, '''', 2), '.')+1) ELSE split_part(def.adsrc, '''', 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 = '#{table}'::regclass AND cons.contype = 'p' AND def.adsrc ~* 'nextval' end_sql end [result["attname"], result["relname"]] rescue nil end |
#postgresql_version ⇒ Object
96 97 98 99 100 101 102 103 104 105 106 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 96 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 |
#quote(value, column = nil) ⇒ Object
396 397 398 399 400 401 402 403 404 405 406 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 396 def quote(value, column = nil) return value.quoted_id if value.respond_to?(:quoted_id) if value.kind_of?(String) && column && column.type == :binary "'#{escape_bytea(value)}'" elsif column && column.type == :primary_key return value.to_s else super end end |
#quote_column_name(name) ⇒ Object
416 417 418 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 416 def quote_column_name(name) %("#{name}") end |
#quote_regclass(table_name) ⇒ Object
172 173 174 175 176 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 172 def quote_regclass(table_name) table_name.to_s.split('.').map do |part| part =~ /".*"/i ? part : quote_table_name(part) end.join('.') end |
#quoted_date(value) ⇒ Object
:nodoc:
420 421 422 423 424 425 426 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 420 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
311 312 313 314 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 311 def recreate_database(name) drop_database(name) create_database(name) end |
#remove_index(table_name, options) ⇒ Object
:nodoc:
478 479 480 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 478 def remove_index(table_name, ) #:nodoc: execute "DROP INDEX #{index_name(table_name, )}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
:nodoc:
474 475 476 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 474 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
435 436 437 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 435 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.
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 153 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 <<-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 else @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger end end end |
#structure_dump ⇒ Object
324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 324 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_ddl_transactions? ⇒ Boolean
133 134 135 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 133 def supports_ddl_transactions? true end |
#supports_insert_with_returning? ⇒ Boolean
129 130 131 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 129 def supports_insert_with_returning? postgresql_version >= 80200 end |
#supports_migrations? ⇒ Boolean
Does PostgreSQL support migrations?
109 110 111 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 109 def supports_migrations? true end |
#supports_savepoints? ⇒ Boolean
137 138 139 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 137 def supports_savepoints? true end |
#supports_standard_conforming_strings? ⇒ Boolean
Does PostgreSQL support standard conforming strings?
114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 114 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. = self. = '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. = 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.
143 144 145 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 143 def table_alias_length @table_alias_length ||= (postgresql_version >= 80000 ? select('SHOW max_identifier_length').to_a[0][0].to_i : 63) end |
#tables ⇒ Object
494 495 496 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 494 def tables @connection.tables(database_name, nil, nil, ["TABLE"]) end |
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
:nodoc:
482 483 484 485 486 487 488 489 490 491 492 |
# File 'lib/jdbc_adapter/jdbc_postgre.rb', line 482 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 |