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

Inherits:
BaseDatabase
  • Object
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

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
 
# 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'] ||= ''
    hash['port'] = hash['port'].to_i unless hash['port'].nil? 

    @connection = PGconn.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 PGError => 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 



227
228
229
230
231
232
233
234
235
236
 
# File 'lib/dbd/pg/database.rb', line 227

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

#[]=(attr, value) ⇒ Object 



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
 
# File 'lib/dbd/pg/database.rb', line 238

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 = PGconn::INV_READ) ⇒ Object

Create a BLOB.



442
443
444
445
446
447
 
# File 'lib/dbd/pg/database.rb', line 442

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

#__blob_export(oid, file) ⇒ Object

Export a BLOB to a file.



432
433
434
435
436
437
 
# File 'lib/dbd/pg/database.rb', line 432

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

#__blob_import(file) ⇒ Object

Import a BLOB from a file.



422
423
424
425
426
427
 
# File 'lib/dbd/pg/database.rb', line 422

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

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

Open a BLOB.



452
453
454
455
456
457
 
# File 'lib/dbd/pg/database.rb', line 452

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

#__blob_read(oid, length) ⇒ Object

Read a BLOB and return the data.



472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
 
# File 'lib/dbd/pg/database.rb', line 472

def __blob_read(oid, length)
    blob = @connection.lo_open(oid.to_i, PGconn::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 PGError => err
    raise DBI::DatabaseError.new(err.message) 
end

#__blob_unlink(oid) ⇒ Object

Remove a BLOB.



462
463
464
465
466
467
 
# File 'lib/dbd/pg/database.rb', line 462

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

#__blob_write(oid, value) ⇒ Object

Write the value to the BLOB.



491
492
493
494
495
496
497
498
499
500
501
502
 
# File 'lib/dbd/pg/database.rb', line 491

def __blob_write(oid, value)
    start_transaction unless @in_transaction
    blob = @connection.lo_open(oid.to_i, PGconn::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 PGError => err
    raise DBI::DatabaseError.new(err.message)
end

#__set_notice_processor(proc) ⇒ Object

FIXME DOCUMENT



507
508
509
510
511
 
# File 'lib/dbd/pg/database.rb', line 507

def __set_notice_processor(proc)
    @connection.set_notice_processor proc
rescue PGError => 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.



403
404
405
406
 
# File 'lib/dbd/pg/database.rb', line 403

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

#__types_old ⇒ Object

deprecated.



409
410
411
412
413
414
415
416
417
 
# File 'lib/dbd/pg/database.rb', line 409

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 



305
306
307
 
# File 'lib/dbd/pg/database.rb', line 305

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

#_exec_prepared(stmt_name, *parameters) ⇒ Object 



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

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

#_prepare(stmt_name, sql) ⇒ Object 



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

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.



125
126
127
128
129
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
 
# File 'lib/dbd/pg/database.rb', line 125

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
    sql3 = %[
        SELECT pg_attrdef.adsrc, 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 



272
273
274
275
276
277
278
279
 
# File 'lib/dbd/pg/database.rb', line 272

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

#disconnect ⇒ Object 



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

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) 


293
294
295
 
# File 'lib/dbd/pg/database.rb', line 293

def in_transaction?
    @in_transaction
end

#ping ⇒ Object 



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

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

#prepare(statement) ⇒ Object 



223
224
225
 
# File 'lib/dbd/pg/database.rb', line 223

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

#rollback ⇒ Object 



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

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

#start_transaction ⇒ Object

Forcibly initializes a new transaction.



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

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

#tables ⇒ Object 



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

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