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.
Defined Under Namespace
Modules: PreparedStatementMethods
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
-
#analyze(opts = nil) ⇒ Object
Return the results of an ANALYZE query as a string.
-
#explain(opts = nil) ⇒ Object
Return the results of an EXPLAIN query as a string.
-
#for_share ⇒ Object
Return a cloned dataset with a :share lock type.
-
#for_update ⇒ Object
Return a cloned dataset with a :update lock type.
-
#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).
-
#insert(*values) ⇒ Object
Insert given values into the database.
-
#insert_returning_sql(returning, *values) ⇒ Object
Use the RETURNING clause to return the columns listed in returning.
-
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted.
-
#literal(v) ⇒ Object
Handle microseconds for Time and DateTime values, as well as PostgreSQL specific boolean values and string escaping.
-
#lock(mode, server = nil) ⇒ Object
Locks the table with the specified mode.
-
#multi_insert_sql(columns, values) ⇒ Object
For PostgreSQL version > 8.2, allow inserting multiple rows at once.
-
#quoted_identifier(c) ⇒ Object
PostgreSQL assumes unquoted identifiers are lower case by default, so do not upcase the identifier when quoting it.
-
#select_sql(opts = nil) ⇒ Object
Support lock mode, allowing FOR SHARE and FOR UPDATE queries.
Instance Method Details
#analyze(opts = nil) ⇒ Object
Return the results of an ANALYZE query as a string
374 375 376 377 378 379 380 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 374 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
383 384 385 386 387 388 389 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 383 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.
392 393 394 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 392 def for_share clone(:lock => :share) end |
#for_update ⇒ Object
Return a cloned dataset with a :update lock type.
397 398 399 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 397 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).
403 404 405 406 407 408 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 403 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.
411 412 413 414 415 416 417 418 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 411 def insert(*values) if !@opts[:sql] and server_version >= 80200 single_value(:sql=>insert_returning_pk_sql(*values)) else execute_insert(insert_sql(*values), :table=>opts[:from].first, :values=>values.size == 1 ? values.first : values) end end |
#insert_returning_sql(returning, *values) ⇒ Object
Use the RETURNING clause to return the columns listed in returning.
421 422 423 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 421 def insert_returning_sql(returning, *values) "#{insert_sql(*values)} RETURNING #{column_list(Array(returning))}" end |
#insert_select(*values) ⇒ Object
Insert a record returning the record inserted
426 427 428 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 426 def insert_select(*values) single_record(:naked=>true, :sql=>insert_returning_sql(nil, *values)) if server_version >= 80200 end |
#literal(v) ⇒ Object
Handle microseconds for Time and DateTime values, as well as PostgreSQL specific boolean values and string escaping.
432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 432 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.
452 453 454 455 456 457 458 459 460 461 462 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 452 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.
465 466 467 468 469 470 471 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 465 def multi_insert_sql(columns, values) return super if server_version < 80200 # postgresql 8.2 introduces support for multi-row insert values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR) ["INSERT INTO #{source_list(@opts[:from])} (#{identifier_list(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.
475 476 477 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 475 def quoted_identifier(c) "\"#{c}\"" end |
#select_sql(opts = nil) ⇒ Object
Support lock mode, allowing FOR SHARE and FOR UPDATE queries.
480 481 482 483 484 485 486 487 488 489 490 |
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 480 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 |