Module: Sequel::Postgres::DatabaseMethods

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

Overview

Methods shared by Database instances that connect to PostgreSQL.

Constant Summary collapse

PREPARED_ARG_PLACEHOLDER = 
'$'.lit.freeze
RE_CURRVAL_ERROR = 
/currval of sequence "(.*)" is not yet defined in this session|relation "(.*)" does not exist/.freeze
SQL_BEGIN = 
'BEGIN'.freeze
SQL_SAVEPOINT = 
'SAVEPOINT autopoint_%d'.freeze
SQL_COMMIT = 
'COMMIT'.freeze
SQL_ROLLBACK_TO_SAVEPOINT = 
'ROLLBACK TO SAVEPOINT autopoint_%d'.freeze
SQL_ROLLBACK = 
'ROLLBACK'.freeze
SQL_RELEASE_SAVEPOINT = 
'RELEASE SAVEPOINT autopoint_%d'.freeze
SYSTEM_TABLE_REGEXP = 
/^pg|sql/.freeze

Instance Method Summary collapse

Instance Method Details

#default_schema ⇒ Object

The default schema to use if none is specified (default: public)



132
133
134
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 132

def default_schema
  @default_schema ||= :public
end

#drop_table(*names) ⇒ Object

Remove the cached entries for primary keys and sequences when dropping a table.



137
138
139
140
141
142
143
144
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 137

def drop_table(*names)
  names.each do |name|
    name = quote_schema_table(name)
    @primary_keys.delete(name)
    @primary_key_sequences.delete(name)
  end
  super
end

#drop_table_sql(name) ⇒ Object

Always CASCADE the table drop



147
148
149
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 147

def drop_table_sql(name)
  "DROP TABLE #{quote_schema_table(name)} CASCADE"
end

#index_definition_sql(table_name, index) ⇒ Object

PostgreSQL specific index SQL.



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 152

def index_definition_sql(table_name, index)
  index_name = index[:name] || default_index_name(table_name, index[:columns])
  expr = literal(Array(index[:columns]))
  unique = "UNIQUE " if index[:unique]
  index_type = index[:type]
  filter = index[:where] || index[:filter]
  filter = " WHERE #{filter_expr(filter)}" if filter
  case index_type
  when :full_text
    cols = Array(index[:columns]).map{|x| :COALESCE[x, '']}.sql_string_join(' ')
    expr = "(to_tsvector(#{literal(index[:language] || 'simple')}, #{literal(cols)}))"
    index_type = :gin
  when :spatial
    index_type = :gist
  end
  "CREATE #{unique}INDEX #{index_name} ON #{table_name} #{"USING #{index_type} " if index_type}#{expr}#{filter}"
end

#locks ⇒ Object

Dataset containing all current database locks



171
172
173
174
175
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 171

def locks
  dataset.from(:pg_class, :pg_locks).
    select(:pg_class__relname, :pg_locks.*).
    filter(:pg_class__relfilenode=>:pg_locks__relation)
end

#primary_key(table, server = nil) ⇒ Object

Return primary key for the given table.



178
179
180
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 178

def primary_key(table, server=nil)
  synchronize(server){|conn| primary_key_for_table(conn, table)}
end

#rename_table_sql(name, new_name) ⇒ Object

SQL DDL statement for renaming a table. PostgreSQL doesn’t allow you to change a table’s schema in a rename table operation, so speciying a new schema in new_name will not have an effect.



184
185
186
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 184

def rename_table_sql(name, new_name)
  "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
end

#serial_primary_key_options ⇒ Object

PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.



190
191
192
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 190

def serial_primary_key_options
  {:primary_key => true, :type => :serial}
end

#server_version(server = nil) ⇒ Object

The version of the PostgreSQL server, used for determining capability.



195
196
197
198
199
200
201
202
203
204
205
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 195

def server_version(server=nil)
  return @server_version if @server_version
  @server_version = synchronize(server) do |conn|
    (conn.server_version rescue nil) if conn.respond_to?(:server_version)
  end
  unless @server_version
    m = /PostgreSQL (\d+)\.(\d+)\.(\d+)/.match(get(:version[]))
    @server_version = (m[1].to_i * 10000) + (m[2].to_i * 100) + m[3].to_i
  end
  @server_version
end

#table_exists?(table, opts = {}) ⇒ Boolean

Whether the given table exists in the database

Options:

  • :schema - The schema to search (default_schema by default)

  • :server - The server to use

Returns:

  • (Boolean) 


212
213
214
215
216
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 212

def table_exists?(table, opts={})
  schema, table = schema_and_table(table)
  opts[:schema] ||= schema
  tables(opts){|ds| !ds.first(:relname=>table.to_s).nil?}
end

#tables(opts = {}) ⇒ Object

Array of symbols specifying table names in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of table names is returned.

Options:

  • :schema - The schema to search (default_schema by default)

  • :server - The server to use



225
226
227
228
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 225

def tables(opts={})
  ds = self[:pg_class].join(:pg_namespace, :oid=>:relnamespace, 'r'=>:relkind, :nspname=>(opts[:schema]||default_schema).to_s).select(:relname).exclude(:relname.like(SYSTEM_TABLE_REGEXP)).server(opts[:server])
  block_given? ? yield(ds) : ds.map{|r| r[:relname].to_sym}
end

#transaction(server = nil) ⇒ Object

PostgreSQL supports multi-level transactions using save points.



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
262
263
264
265
266
267
268
269
270
271
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 231

def transaction(server=nil)
  synchronize(server) do |conn|
    conn.transaction_depth = 0 if conn.transaction_depth.nil?
    if conn.transaction_depth > 0
      log_info(SQL_SAVEPOINT % conn.transaction_depth)
      conn.execute(SQL_SAVEPOINT % conn.transaction_depth)
    else
      log_info(SQL_BEGIN)
      conn.execute(SQL_BEGIN)
    end
    begin
      conn.transaction_depth += 1
      yield conn
    rescue ::Exception => e
      if conn.transaction_depth > 1
        log_info(SQL_ROLLBACK_TO_SAVEPOINT % [conn.transaction_depth - 1])
        conn.execute(SQL_ROLLBACK_TO_SAVEPOINT % [conn.transaction_depth - 1])
      else
        log_info(SQL_ROLLBACK)
        conn.execute(SQL_ROLLBACK) rescue nil
      end
      transaction_error(e, *CONVERTED_EXCEPTIONS)
    ensure
      unless e
        begin
          if conn.transaction_depth < 2
            log_info(SQL_COMMIT)
            conn.execute(SQL_COMMIT)
          else
            log_info(SQL_RELEASE_SAVEPOINT % [conn.transaction_depth - 1])
            conn.execute(SQL_RELEASE_SAVEPOINT % [conn.transaction_depth - 1])
          end
        rescue => e
          log_info(e.message)
          raise_error(e, :classes=>CONVERTED_EXCEPTIONS)
        end
      end
      conn.transaction_depth -= 1
    end
  end
end