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/.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_sql(name) ⇒ Object

Always CASCADE the table drop



107
108
109
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 107

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

#index_definition_sql(table_name, index) ⇒ Object

PostgreSQL specific index SQL.



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 112

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 = index[:language] ? "#{literal(index[:language])}, " : ""
    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

#insert_result(conn, table, values) ⇒ Object

The result of the insert for the given table and values. Uses last insert id the primary key for the table if it exists, otherwise determines the primary key for the table and uses the value of the hash key. If values is an array, assume the first value is the primary key value and return that.



136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 136

def insert_result(conn, table, values)
  begin
    result = conn.last_insert_id(table)
    return result if result
  rescue Exception => e
    convert_pgerror(e) unless RE_CURRVAL_ERROR.match(e.message)
  end
  
  case values
  when Hash
    values[primary_key_for_table(conn, table)]
  when Array
    values.first
  else
    nil
  end
end

#locks ⇒ Object

Dataset containing all current database locks



155
156
157
158
159
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 155

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

#primary_key_for_table(conn, table) ⇒ Object

Returns primary key for the given table. This information is cached, and if the primary key for a table is changed, the



164
165
166
167
 
# File 'lib/sequel_core/adapters/shared/postgres.rb', line 164

def primary_key_for_table(conn, table)
  @primary_keys ||= {}
  @primary_keys[table] ||= conn.primary_key(table)
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
      raise convert_pgerror(e) unless Error::Rollback === e
    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 convert_pgerror(e)
        end
      end
      conn.transaction_depth -= 1
    end
  end
end