Class: DBI::DBD::Pg::Database

Inherits:
BaseDatabase show all
Defined in:
lib/dbd/pg/database.rb

Overview

See DBI::BaseDatabase.

Constant Summary collapse

POSTGRESQL_to_XOPEN =

type map

{
      "boolean"                   => [DBI::SQL_CHAR, 1, nil],
      "character"                 => [DBI::SQL_CHAR, 1, nil],
      "char"                      => [DBI::SQL_CHAR, 1, nil],
      "real"                      => [DBI::SQL_REAL, 4, 6],
      "double precision"          => [DBI::SQL_DOUBLE, 8, 15],
      "smallint"                  => [DBI::SQL_SMALLINT, 2],
      "integer"                   => [DBI::SQL_INTEGER, 4],
      "bigint"                    => [DBI::SQL_BIGINT, 8],
      "numeric"                   => [DBI::SQL_NUMERIC, nil, nil],
      "time with time zone"       => [DBI::SQL_TIME, nil, nil],
      "timestamp with time zone"  => [DBI::SQL_TIMESTAMP, nil, nil],
      "bit varying"               => [DBI::SQL_BINARY, nil, nil], #huh??
      "character varying"         => [DBI::SQL_VARCHAR, nil, nil],
      "bit"                       => [DBI::SQL_TINYINT, nil, nil],
      "text"                      => [DBI::SQL_VARCHAR, nil, nil],
      nil                         => [DBI::SQL_OTHER, nil, nil]
}

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from BaseDatabase

#do, #execute

Constructor Details

#initialize(dbname, user, auth, attr) ⇒ Database

See DBI::BaseDatabase#new. These attributes are also supported:

  • pg_async: boolean or strings ‘true’ or ‘false’. Indicates if we’re to use PostgreSQL’s asyncrohonous support. ‘NonBlocking’ is a synonym for this.

  • AutoCommit: ‘unchained’ mode in PostgreSQL. Commits after each statement execution.

  • pg_client_encoding: set the encoding for the client.

  • pg_native_binding: Boolean. Indicates whether to use libpq native binding or DBI’s inline binding. Defaults to true.



40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/dbd/pg/database.rb', line 40

def initialize(dbname, user, auth, attr)
    hash = DBI::Utils.parse_params(dbname)

    if hash['dbname'].nil? and hash['database'].nil?
        raise DBI::InterfaceError, "must specify database"
    end

    hash['options'] ||= nil
    hash['tty'] = nil
    hash['host'] ||= 'localhost'
    hash['port'] = hash['port'].to_i unless hash['port'].nil? 

    @connection = PG::Connection.new(hash['host'], hash['port'], hash['options'], hash['tty'],
                             hash['dbname'] || hash['database'], user, auth)

    @exec_method = :exec
    @in_transaction = false

    # set attribute defaults, and look for pg_* attrs in the DSN
    @attr = { 'AutoCommit' => true, 'pg_async' => false }
    hash.each do |key, value|
        @attr[key] = value if key =~ /^pg_./
    end
    @attr.merge!(attr || {})
    if @attr['pg_async'].is_a?(String)
        case @attr['pg_async'].downcase
        when 'true'
            @attr['pg_async'] = true
        when 'false'
            @attr['pg_async'] = false
        else
            raise InterfaceError, %q{'pg_async' must be 'true' or 'false'}
        end
    end

    @attr.each { |k,v| self[k] = v} 
    @attr["pg_native_binding"] = true unless @attr.has_key? "pg_native_binding"

    load_type_map

    self['AutoCommit'] = true    # Postgres starts in unchained mode (AutoCommit=on) by default 

rescue PG::Error => err
    raise DBI::OperationalError.new(err.message)
end

Instance Attribute Details

#type_map ⇒ Object (readonly)

Returns the value of attribute type_map.



26
27
28
 
# File 'lib/dbd/pg/database.rb', line 26

def type_map
  @type_map
end

Instance Method Details

#[](attr) ⇒ Object 



236
237
238
239
240
241
242
243
244
245
 
# File 'lib/dbd/pg/database.rb', line 236

def [](attr)
    case attr
    when 'pg_client_encoding'
        @connection.client_encoding
    when 'NonBlocking'
        @attr['pg_async']
    else
        @attr[attr]
    end
end

#[]=(attr, value) ⇒ Object 



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
272
273
274
275
276
277
278
279
 
# File 'lib/dbd/pg/database.rb', line 247

def []=(attr, value)
    case attr
    when 'AutoCommit'
        if @attr['AutoCommit'] != value then
            if value    # turn AutoCommit ON
                if @in_transaction
                    # TODO: commit outstanding transactions?
                    _exec("COMMIT")
                    @in_transaction = false
                end
            else        # turn AutoCommit OFF
                @in_transaction = false
            end
        end
    # value is assigned below
    when 'NonBlocking', 'pg_async'
        # booleanize input
        value = value ? true : false
        @pgexec = (value ? DBI::DBD::Pg::PgExecutorAsync : DBI::DBD::Pg::PgExecutor).new(@connection)
        # value is assigned to @attr below
    when 'pg_client_encoding'
        @connection.set_client_encoding(value)
    when 'pg_native_binding'
        @attr[attr] = value
    else
        if attr =~ /^pg_/ or attr != /_/
            raise DBI::NotSupportedError, "Option '#{attr}' not supported"
        else # option for some other driver - quitly ignore
            return
        end
    end
    @attr[attr] = value
end

#__blob_create(mode = PG::Connection::INV_READ) ⇒ Object

Create a BLOB.



438
439
440
441
442
443
 
# File 'lib/dbd/pg/database.rb', line 438

def __blob_create(mode=PG::Connection::INV_READ)
    start_transaction unless @in_transaction
    @connection.lo_creat(mode)
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_export(oid, file) ⇒ Object

Export a BLOB to a file.



428
429
430
431
432
433
 
# File 'lib/dbd/pg/database.rb', line 428

def __blob_export(oid, file)
    start_transaction unless @in_transaction
    @connection.lo_export(oid.to_i, file)
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_import(file) ⇒ Object

Import a BLOB from a file.



418
419
420
421
422
423
 
# File 'lib/dbd/pg/database.rb', line 418

def __blob_import(file)
    start_transaction unless @in_transaction
    @connection.lo_import(file)
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_open(oid, mode = PG::Connection::INV_READ) ⇒ Object

Open a BLOB.



448
449
450
451
452
453
 
# File 'lib/dbd/pg/database.rb', line 448

def __blob_open(oid, mode=PG::Connection::INV_READ)
    start_transaction unless @in_transaction
    @connection.lo_open(oid.to_i, mode)
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_read(oid, length) ⇒ Object

Read a BLOB and return the data.



468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
 
# File 'lib/dbd/pg/database.rb', line 468

def __blob_read(oid, length)
    blob = @connection.lo_open(oid.to_i, PG::Connection::INV_READ)

    if length.nil?
        data = @connection.lo_read(blob)
    else
        data = @connection.lo_read(blob, length)
    end

    # FIXME it doesn't like to close here either.
    # @connection.lo_close(blob)
    data
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_unlink(oid) ⇒ Object

Remove a BLOB.



458
459
460
461
462
463
 
# File 'lib/dbd/pg/database.rb', line 458

def __blob_unlink(oid)
    start_transaction unless @in_transaction
    @connection.lo_unlink(oid.to_i)
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_write(oid, value) ⇒ Object

Write the value to the BLOB.



487
488
489
490
491
492
493
494
495
496
497
498
 
# File 'lib/dbd/pg/database.rb', line 487

def __blob_write(oid, value)
    start_transaction unless @in_transaction
    blob = @connection.lo_open(oid.to_i, PG::Connection::INV_WRITE)
    res = @connection.lo_write(blob, value)
    # FIXME not sure why PG doesn't like to close here -- seems to be
    # working but we should make sure it's not eating file descriptors
    # up before release.
    # @connection.lo_close(blob)
    return res
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message)
end

#__set_notice_processor(proc) ⇒ Object

FIXME DOCUMENT



503
504
505
506
507
 
# File 'lib/dbd/pg/database.rb', line 503

def __set_notice_processor(proc)
    @connection.set_notice_processor proc
rescue PG::Error => err
    raise DBI::DatabaseError.new(err.message) 
end

#__types(force = nil) ⇒ Object

return the postgresql types for this session. returns an oid -> type name mapping.



399
400
401
402
 
# File 'lib/dbd/pg/database.rb', line 399

def __types(force=nil)
    load_type_map if (!@type_map or force)
    @type_map
end

#__types_old ⇒ Object

deprecated.



405
406
407
408
409
410
411
412
413
 
# File 'lib/dbd/pg/database.rb', line 405

def __types_old
    h = { } 

    _exec('select oid, typname from pg_type').each do |row|
        h[row["oid"].to_i] = row["typname"]
    end

    return h
end

#_exec(sql, *parameters) ⇒ Object 



314
315
316
 
# File 'lib/dbd/pg/database.rb', line 314

def _exec(sql, *parameters)
    @pgexec.exec(sql, parameters)
end

#_exec_prepared(stmt_name, *parameters) ⇒ Object 



318
319
320
 
# File 'lib/dbd/pg/database.rb', line 318

def _exec_prepared(stmt_name, *parameters)
    @pgexec.exec_prepared(stmt_name, parameters)
end

#_prepare(stmt_name, sql) ⇒ Object 



322
323
324
 
# File 'lib/dbd/pg/database.rb', line 322

def _prepare(stmt_name, sql)
    @pgexec.prepare(stmt_name, sql)
end

#columns(table) ⇒ Object

See DBI::BaseDatabase.

These additional attributes are also supported:

  • nullable: true if NULL values are allowed in this column.

  • indexed: true if this column is a part of an index.

  • primary: true if this column is a part of a primary key.

  • unique: true if this column is a part of a unique key.

  • default: what will be insert if this column is left out of an insert query.

  • array_of_type: true if this is actually an array of this type. dbi_type will be the type authority if this is the case.



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
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
 
# File 'lib/dbd/pg/database.rb', line 130

def columns(table)
    sql1 = %[
        select a.attname, i.indisprimary, i.indisunique
        from pg_class bc inner join pg_index i 
            on bc.oid = i.indrelid 
            inner join pg_class c 
                on c.oid = i.indexrelid 
                inner join pg_attribute a
                    on c.oid = a.attrelid
        where bc.relname = ?
            and bc.relkind in ('r', 'v')
            and pg_catalog.pg_table_is_visible(bc.oid);
    ]

    sql2 = %[
        SELECT a.attname, a.atttypid, a.attnotnull, a.attlen, format_type(a.atttypid, a.atttypmod) 
        FROM pg_catalog.pg_class c, pg_attribute a, pg_type t 
        WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.relname = ?
            AND c.relkind IN ('r','v')
        AND pg_catalog.pg_table_is_visible(c.oid)
    ]

    # by Michael Neumann (get default value)
    # corrected by Joseph McDonald
    # from https://www.postgresql.org/docs/12/release-12.html
    # Remove obsolete pg_attrdef.adsrc column (Peter Eisentraut)
    # This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The # # recommended way to get a text version of a default-value expression from pg_attrdef is pg_get_expr(adbin, adrelid).

    sql3 = %[
        SELECT pg_get_expr(pg_attrdef.adbin, pg_attrdef.adrelid), pg_attribute.attname
        FROM pg_attribute, pg_attrdef, pg_catalog.pg_class
        WHERE pg_catalog.pg_class.relname = ? AND
        pg_attribute.attrelid = pg_catalog.pg_class.oid AND
                      pg_attrdef.adrelid = pg_catalog.pg_class.oid AND
                      pg_attrdef.adnum = pg_attribute.attnum
                      AND pg_catalog.pg_class.relkind IN ('r','v')
                      AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid)
    ]

    dbh = DBI::DatabaseHandle.new(self)
    dbh.driver_name = DBI::DBD::Pg.driver_name
    indices = {}
    default_values = {}

    dbh.select_all(sql3, table) do |default, name|
        default_values[name] = default
    end

    dbh.select_all(sql1, table) do |name, primary, unique|
        indices[name] = [primary, unique]
    end

    ########## 

    ret = []
    dbh.execute(sql2, table) do |sth|
        ret = sth.collect do |row|
            name, pg_type, notnullable, len, ftype = row
            #name = row[2]
            indexed = false
            primary = nil
            unique = nil
            if indices.has_key?(name)
                indexed = true
                primary, unique = indices[name]
            end

            typeinfo = DBI::DBD::Pg.parse_type(ftype)
            typeinfo[:size] ||= len

            if POSTGRESQL_to_XOPEN.has_key?(typeinfo[:type])
                sql_type = POSTGRESQL_to_XOPEN[typeinfo[:type]][0]
            else
                sql_type = POSTGRESQL_to_XOPEN[nil][0]
            end

            row = {}
            row['name']           = name
            row['sql_type']       = sql_type
            row['type_name']      = typeinfo[:type]
            row['nullable']       = ! notnullable
            row['indexed']        = indexed
            row['primary']        = primary
            row['unique']         = unique
            row['precision']      = typeinfo[:size]
            row['scale']          = typeinfo[:decimal]
            row['default']        = default_values[name]
            row['array_of_type']  = typeinfo[:array]

            if typeinfo[:array]
                row['dbi_type'] = 
                    DBI::DBD::Pg::Type::Array.new(
                        DBI::TypeUtil.type_name_to_module(typeinfo[:type])
                )
            end
            row
        end # collect
    end # execute

    return ret
end

#commit ⇒ Object 



281
282
283
284
285
286
287
288
 
# File 'lib/dbd/pg/database.rb', line 281

def commit
    if @in_transaction
        _exec("COMMIT")
        @in_transaction = false
    else
        # TODO: Warn?
    end
end

#database_name ⇒ Object 



106
107
108
 
# File 'lib/dbd/pg/database.rb', line 106

def database_name
    @connection.db
end

#disconnect ⇒ Object 



86
87
88
89
90
91
 
# File 'lib/dbd/pg/database.rb', line 86

def disconnect
    if not @attr['AutoCommit'] and @in_transaction
        _exec("ROLLBACK")   # rollback outstanding transactions
    end
    @connection.close
end

#in_transaction? ⇒ Boolean

Are we in an transaction?

Returns:

  • (Boolean) 


302
303
304
 
# File 'lib/dbd/pg/database.rb', line 302

def in_transaction?
    @in_transaction
end

#ping ⇒ Object 



93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/dbd/pg/database.rb', line 93

def ping
    answer = _exec("SELECT 1")
    if answer
        return answer.num_tuples == 1
    else
        return false
    end
rescue PG::Error
    return false
ensure
    answer.clear if answer
end

#prepare(statement) ⇒ Object 



232
233
234
 
# File 'lib/dbd/pg/database.rb', line 232

def prepare(statement)
    DBI::DBD::Pg::Statement.new(self, statement)
end

#rollback ⇒ Object 



290
291
292
293
294
295
296
297
 
# File 'lib/dbd/pg/database.rb', line 290

def rollback
    if @in_transaction
        _exec("ROLLBACK")
        @in_transaction = false
    else
        # TODO: Warn?
    end
end

#start_transaction ⇒ Object

Forcibly initializes a new transaction.



309
310
311
312
 
# File 'lib/dbd/pg/database.rb', line 309

def start_transaction
    _exec("BEGIN")
    @in_transaction = true
end

#tables ⇒ Object 



110
111
112
113
114
115
 
# File 'lib/dbd/pg/database.rb', line 110

def tables
    stmt = execute("SELECT c.relname FROM pg_catalog.pg_class c WHERE c.relkind IN ('r','v') and pg_catalog.pg_table_is_visible(c.oid)")
    res = stmt.fetch_all.collect {|row| row[0]} 
    stmt.finish
    res
end