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
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'] ||= ''
    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 



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

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

#[]=(attr, value) ⇒ Object 



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

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.



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

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.



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

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.



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

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.



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

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.



464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
 
# File 'lib/dbd/pg/database.rb', line 464

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.



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

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.



483
484
485
486
487
488
489
490
491
492
493
494
 
# File 'lib/dbd/pg/database.rb', line 483

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



499
500
501
502
503
 
# File 'lib/dbd/pg/database.rb', line 499

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.



395
396
397
398
 
# File 'lib/dbd/pg/database.rb', line 395

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

#__types_old ⇒ Object

deprecated.



401
402
403
404
405
406
407
408
409
 
# File 'lib/dbd/pg/database.rb', line 401

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 



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

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

#_exec_prepared(stmt_name, *parameters) ⇒ Object 



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

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

#_prepare(stmt_name, sql) ⇒ Object 



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

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
 
# 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
    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 



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

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) 


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

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 



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

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

#rollback ⇒ Object 



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

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

#start_transaction ⇒ Object

Forcibly initializes a new transaction.



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

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