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
RELATION_QUERY = 
{:from => [:pg_class], :select => [:relname]}.freeze
RELATION_FILTER = 
"(relkind = 'r') AND (relname !~ '^pg|sql')".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

#drop_table(*names) ⇒ Object

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



107
108
109
110
111
112
113
114
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 107

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

#drop_table_sql(name) ⇒ Object

Always CASCADE the table drop



117
118
119
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 117

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

#index_definition_sql(table_name, index) ⇒ Object

PostgreSQL specific index SQL.



122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 122

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
    lang = "#{literal(index[:language] || 'simple')}, "
    cols = index[:columns].map {|c| literal(c)}.join(" || ")
    expr = "(to_tsvector(#{lang}#{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



142
143
144
145
146
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 142

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.



149
150
151
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 149

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

#schema(table_name = nil, opts = {}) ⇒ Object

Support :schema__table format for table



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

def schema(table_name=nil, opts={})
  case table_name
  when Symbol
    t, c, a = dataset.send(:split_symbol, table_name)
    opts[:schema] ||= t
    table_name = c
  when SQL::QualifiedIdentifier
    opts[:schema] ||= table_name.table
    table_name = table_name.column
  when SQL::Identifier
    table_name = table_name.value
  end
  super(table_name, opts)
end

#serial_primary_key_options ⇒ Object

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



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

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.



176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 176

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

#tables ⇒ Object

Array of symbols specifying table names in the current database.



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

def tables
  dataset(RELATION_QUERY).filter(RELATION_FILTER).map{|r| r[:relname].to_sym}
end

#transaction(server = nil) ⇒ Object

PostgreSQL supports multi-level transactions using save points.



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
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 194

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