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 



231
232
233
234
235
236
237
238
239
240
 
# File 'lib/dbd/pg/database.rb', line 231

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

#[]=(attr, value) ⇒ Object 



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

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.



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

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.



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

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.



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

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.



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

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.



476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
 
# File 'lib/dbd/pg/database.rb', line 476

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.



466
467
468
469
470
471
 
# File 'lib/dbd/pg/database.rb', line 466

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.



495
496
497
498
499
500
501
502
503
504
505
506
 
# File 'lib/dbd/pg/database.rb', line 495

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



511
512
513
514
515
 
# File 'lib/dbd/pg/database.rb', line 511

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.



407
408
409
410
 
# File 'lib/dbd/pg/database.rb', line 407

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

#__types_old ⇒ Object

deprecated.



413
414
415
416
417
418
419
420
421
 
# File 'lib/dbd/pg/database.rb', line 413

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 



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

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

#_exec_prepared(stmt_name, *parameters) ⇒ Object 



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

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

#_prepare(stmt_name, sql) ⇒ Object 



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

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.



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
222
223
224
225
 
# File 'lib/dbd/pg/database.rb', line 129

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 



276
277
278
279
280
281
282
283
 
# File 'lib/dbd/pg/database.rb', line 276

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

#database_name ⇒ Object 



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

def database_name
    @connection.db
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) 


297
298
299
 
# File 'lib/dbd/pg/database.rb', line 297

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 



227
228
229
 
# File 'lib/dbd/pg/database.rb', line 227

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

#rollback ⇒ Object 



285
286
287
288
289
290
291
292
 
# File 'lib/dbd/pg/database.rb', line 285

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

#start_transaction ⇒ Object

Forcibly initializes a new transaction.



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

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

#tables ⇒ Object 



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

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