Module: Sequel::Postgres::DatasetMethods

Included in:
Dataset
Defined in:
lib/sequel_core/adapters/shared/postgres.rb

Overview

Instance methods for datasets that connect to a PostgreSQL database.

Constant Summary collapse

ACCESS_SHARE = 
'ACCESS SHARE'.freeze
ACCESS_EXCLUSIVE = 
'ACCESS EXCLUSIVE'.freeze
BOOL_FALSE = 
'false'.freeze
BOOL_TRUE = 
'true'.freeze
COMMA_SEPARATOR = 
', '.freeze
EXCLUSIVE = 
'EXCLUSIVE'.freeze
EXPLAIN = 
'EXPLAIN '.freeze
EXPLAIN_ANALYZE = 
'EXPLAIN ANALYZE '.freeze
FOR_SHARE = 
' FOR SHARE'.freeze
FOR_UPDATE = 
' FOR UPDATE'.freeze
LOCK = 
'LOCK TABLE %s IN %s MODE'.freeze
PG_TIMESTAMP_FORMAT = 
"TIMESTAMP '%Y-%m-%d %H:%M:%S".freeze
QUERY_PLAN = 
'QUERY PLAN'.to_sym
ROW_EXCLUSIVE = 
'ROW EXCLUSIVE'.freeze
ROW_SHARE = 
'ROW SHARE'.freeze
SHARE = 
'SHARE'.freeze
SHARE_ROW_EXCLUSIVE = 
'SHARE ROW EXCLUSIVE'.freeze
SHARE_UPDATE_EXCLUSIVE = 
'SHARE UPDATE EXCLUSIVE'.freeze

Instance Method Summary collapse

Instance Method Details

#analyze(opts = nil) ⇒ Object

Return the results of an ANALYZE query as a string



282
283
284
285
286
287
288
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 282

def analyze(opts = nil)
  analysis = []
  fetch_rows(EXPLAIN_ANALYZE + select_sql(opts)) do |r|
    analysis << r[QUERY_PLAN]
  end
  analysis.join("\r\n")
end

#explain(opts = nil) ⇒ Object

Return the results of an EXPLAIN query as a string



291
292
293
294
295
296
297
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 291

def explain(opts = nil)
  analysis = []
  fetch_rows(EXPLAIN + select_sql(opts)) do |r|
    analysis << r[QUERY_PLAN]
  end
  analysis.join("\r\n")
end

#for_share ⇒ Object

Return a cloned dataset with a :share lock type.



300
301
302
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 300

def for_share
  clone(:lock => :share)
end

#for_update ⇒ Object

Return a cloned dataset with a :update lock type.



305
306
307
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 305

def for_update
  clone(:lock => :update)
end

#full_text_search(cols, terms, opts = {}) ⇒ Object

PostgreSQL specific full text search syntax, using tsearch2 (included in 8.3 by default, and available for earlier versions as an add-on).



311
312
313
314
315
316
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 311

def full_text_search(cols, terms, opts = {})
  lang = opts[:language] ? "#{literal(opts[:language])}, " : ""
  cols = cols.is_a?(Array) ? cols.map {|c| literal(c)}.join(" || ") : literal(cols)
  terms = terms.is_a?(Array) ? literal(terms.join(" | ")) : literal(terms)
  filter("to_tsvector(#{lang}#{cols}) @@ to_tsquery(#{lang}#{terms})")
end

#insert(*values) ⇒ Object

Insert given values into the database.



319
320
321
322
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 319

def insert(*values)
  execute_insert(insert_sql(*values), :table=>source_list(@opts[:from]),
    :values=>values.size == 1 ? values.first : values)
end

#literal(v) ⇒ Object

Handle microseconds for Time and DateTime values, as well as PostgreSQL specific boolean values and string escaping.



326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 326

def literal(v)
  case v
  when LiteralString
    v
  when String
    db.synchronize{|c| "'#{SQL::Blob === v ? c.escape_bytea(v) : c.escape_string(v)}'"}
  when Time
    "#{v.strftime(PG_TIMESTAMP_FORMAT)}.#{sprintf("%06d",v.usec)}'"
  when DateTime
    "#{v.strftime(PG_TIMESTAMP_FORMAT)}.#{sprintf("%06d", (v.sec_fraction * 86400000000).to_i)}'"
  when TrueClass
    BOOL_TRUE
  when FalseClass
    BOOL_FALSE
  else
    super
  end
end

#lock(mode, server = nil) ⇒ Object

Locks the table with the specified mode.



346
347
348
349
350
351
352
353
354
355
356
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 346

def lock(mode, server=nil)
  sql = LOCK % [source_list(@opts[:from]), mode]
  @db.synchronize(server) do
    if block_given? # perform locking inside a transaction and yield to block
      @db.transaction(server){@db.execute(sql, :server=>server); yield}
    else
      @db.execute(sql, :server=>server) # lock without a transaction
      self
    end
  end
end

#multi_insert_sql(columns, values) ⇒ Object

For PostgreSQL version > 8.2, allow inserting multiple rows at once.



359
360
361
362
363
364
365
366
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 359

def multi_insert_sql(columns, values)
  return super if @db.server_version < 80200
  
  # postgresql 8.2 introduces support for multi-row insert
  columns = column_list(columns)
  values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)
  ["INSERT INTO #{source_list(@opts[:from])} (#{columns}) VALUES #{values}"]
end

#quoted_identifier(c) ⇒ Object

PostgreSQL assumes unquoted identifiers are lower case by default, so do not upcase the identifier when quoting it.



370
371
372
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 370

def quoted_identifier(c)
  "\"#{c}\""
end

#select_sql(opts = nil) ⇒ Object

Support lock mode, allowing FOR SHARE and FOR UPDATE queries.



375
376
377
378
379
380
381
382
383
384
385
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 375

def select_sql(opts = nil)
  row_lock_mode = opts ? opts[:lock] : @opts[:lock]
  sql = super
  case row_lock_mode
  when :update
    sql << FOR_UPDATE
  when :share
    sql << FOR_SHARE
  end
  sql
end