Class: Sequel::Database

Inherits:
Object show all
Includes:
Schema::SQL
Defined in:
lib/sequel_core/database.rb,
lib/sequel_core/database/schema.rb

Overview

A Database object represents a virtual connection to a database. The Database class is meant to be subclassed by database adapters in order to provide the functionality needed for executing queries.

Constant Summary collapse

ADAPTERS =

Array of supported database adapters

%w'ado db2 dbi informix jdbc mysql odbc odbc_mssql openbase oracle postgres sqlite'.collect{|x| x.to_sym}
SQL_BEGIN = 
'BEGIN'.freeze
SQL_COMMIT = 
'COMMIT'.freeze
SQL_ROLLBACK = 
'ROLLBACK'.freeze
@@adapters =

Hash of adapters that have been used

Hash.new
@@single_threaded =

Whether to use the single threaded connection pool by default

false
@@quote_identifiers =

Whether to quote identifiers (columns and tables) by default

true

Constants included from Schema::SQL

Schema::SQL::AUTOINCREMENT, Schema::SQL::CASCADE, Schema::SQL::COMMA_SEPARATOR, Schema::SQL::NOT_NULL, Schema::SQL::NO_ACTION, Schema::SQL::NULL, Schema::SQL::PRIMARY_KEY, Schema::SQL::RESTRICT, Schema::SQL::SET_DEFAULT, Schema::SQL::SET_NULL, Schema::SQL::TYPES, Schema::SQL::UNDERSCORE, Schema::SQL::UNIQUE, Schema::SQL::UNSIGNED

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Schema::SQL

#alter_table_sql, #alter_table_sql_list, #auto_increment_sql, #column_definition_sql, #column_list_sql, #constraint_definition_sql, #create_table_sql_list, #default_index_name, #drop_table_sql, #filter_expr, #index_definition_sql, #index_list_sql_list, #literal, #on_delete_clause, #quote_identifier, #rename_table_sql, #schema, #schema_utility_dataset, #type_literal

Constructor Details

#initialize(opts = {}, &block) ⇒ Database

Constructs a new instance of a database connection with the specified options hash.

Sequel::Database is an abstract class that is not useful by itself.



48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/sequel_core/database.rb', line 48

def initialize(opts = {}, &block)
  @opts = opts
  
  @quote_identifiers = opts.include?(:quote_identifiers) ? opts[:quote_identifiers] : @@quote_identifiers
  @single_threaded = opts.include?(:single_threaded) ? opts[:single_threaded] : @@single_threaded
  @schemas = nil
  @pool = (@single_threaded ? SingleThreadedPool : ConnectionPool).new(connection_pool_default_options.merge(opts), &block)
  @pool.connection_proc = proc {connect} unless block

  @loggers = Array(opts[:logger]) + Array(opts[:loggers])
  ::Sequel::DATABASES.push(self)
end

Instance Attribute Details

#loggers ⇒ Object

Array of SQL loggers to use for this database



33
34
35
 
# File 'lib/sequel_core/database.rb', line 33

def loggers
  @loggers
end

#opts ⇒ Object (readonly)

The options for this database



36
37
38
 
# File 'lib/sequel_core/database.rb', line 36

def opts
  @opts
end

#pool ⇒ Object (readonly)

The connection pool for this database



39
40
41
 
# File 'lib/sequel_core/database.rb', line 39

def pool
  @pool
end

#quote_identifiers=(value) ⇒ Object (writeonly)

Whether to quote identifiers (columns and tables) for this database



42
43
44
 
# File 'lib/sequel_core/database.rb', line 42

def quote_identifiers=(value)
  @quote_identifiers = value
end

Class Method Details

.adapter_class(scheme) ⇒ Object

The Database subclass for the given adapter scheme. Raises Sequel::Error::AdapterNotFound if the adapter could not be loaded.



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/sequel_core/database.rb', line 66

def self.adapter_class(scheme)
  scheme = scheme.to_s.gsub('-', '_').to_sym
  
  if (klass = @@adapters[scheme]).nil?
    # attempt to load the adapter file
    begin
      require "sequel_core/adapters/#{scheme}"
    rescue LoadError => e
      raise Error::AdapterNotFound, "Could not load #{scheme} adapter:\n  #{e.message}"
    end
    
    # make sure we actually loaded the adapter
    if (klass = @@adapters[scheme]).nil?
      raise Error::AdapterNotFound, "Could not load #{scheme} adapter"
    end
  end
  return klass
end

.adapter_scheme ⇒ Object

Returns the scheme for the Database class.



86
87
88
 
# File 'lib/sequel_core/database.rb', line 86

def self.adapter_scheme
  @scheme
end

.connect(conn_string, opts = nil, &block) ⇒ Object

Connects to a database. See Sequel.connect.



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/sequel_core/database.rb', line 91

def self.connect(conn_string, opts = nil, &block)
  if conn_string.is_a?(String)
    uri = URI.parse(conn_string)
    scheme = uri.scheme
    scheme = :dbi if scheme =~ /^dbi-(.+)/
    c = adapter_class(scheme)
    opts = c.uri_to_options(uri).merge(opts || {})
  else
    opts = conn_string.merge(opts || {})
    c = adapter_class(opts[:adapter] || opts['adapter'])
  end
  # process opts a bit
  opts = opts.inject({}) do |m, kv| k, v = *kv
    k = :user if k.to_s == 'username'
    m[k.to_sym] = v
    m
  end
  if block
    begin
      yield(db = c.new(opts))
    ensure
      db.disconnect if db
      ::Sequel::DATABASES.delete(db)
    end
    nil
  else
    c.new(opts)
  end
end

.quote_identifiers=(value) ⇒ Object

Sets the default quote_identifiers mode for new databases. See Sequel.quote_identifiers=.



123
124
125
 
# File 'lib/sequel_core/database.rb', line 123

def self.quote_identifiers=(value)
  @@quote_identifiers = value
end

.single_threaded=(value) ⇒ Object

Sets the default single_threaded mode for new databases. See Sequel.single_threaded=.



129
130
131
 
# File 'lib/sequel_core/database.rb', line 129

def self.single_threaded=(value)
  @@single_threaded = value
end

.uri_to_options(uri) ⇒ Object

Converts a uri to an options hash. These options are then passed to a newly created database object.



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

def self.uri_to_options(uri)
  uri = URI.parse(uri) if uri.is_a?(String)
  # special case for sqlite
  opts = if uri.scheme == 'sqlite'
    { :user => uri.user,
      :password => uri.password,
      :database => (uri.host.nil? && uri.path == '/') ? nil : "#{uri.host}#{uri.path}" }
  else
    { :user => uri.user,
      :password => uri.password,
      :host => uri.host,
      :port => uri.port,
      :database => (m = /\/(.*)/.match(uri.path)) && (m[1]) }
  end
  uri.query.split('&').collect{|s| s.split('=')}.each{|k,v| opts[k.to_sym] = v} unless uri.query.blank?
  opts
end

Instance Method Details

#<<(sql) ⇒ Object

Executes the supplied SQL statement. The SQL can be supplied as a string or as an array of strings. If an array is given, comments and excessive white space are removed. See also Array#to_sql.



179
180
181
 
# File 'lib/sequel_core/database.rb', line 179

def <<(sql)
  execute((Array === sql) ? sql.to_sql : sql)
end

#[](*args, &block) ⇒ Object

Returns a dataset from the database. If the first argument is a string, the method acts as an alias for Database#fetch, returning a dataset for arbitrary SQL:

DB['SELECT * FROM items WHERE name = ?', my_name].print

Otherwise, acts as an alias for Database#from, setting the primary table for the dataset:

DB[:items].sql #=> "SELECT * FROM items"



193
194
195
 
# File 'lib/sequel_core/database.rb', line 193

def [](*args, &block)
  (String === args.first) ? fetch(*args, &block) : from(*args, &block)
end

#add_column(table, *args) ⇒ Object

Adds a column to the specified table. This method expects a column name, a datatype and optionally a hash with additional constraints and options:

DB.add_column :items, :name, :text, :unique => true, :null => false
DB.add_column :items, :category, :text, :default => 'ruby'

See alter_table.



10
11
12
 
# File 'lib/sequel_core/database/schema.rb', line 10

def add_column(table, *args)
  alter_table(table) {add_column(*args)}
end

#add_index(table, *args) ⇒ Object

Adds an index to a table for the given columns:

DB.add_index :posts, :title
DB.add_index :posts, [:author, :title], :unique => true

See alter_table.



20
21
22
 
# File 'lib/sequel_core/database/schema.rb', line 20

def add_index(table, *args)
  alter_table(table) {add_index(*args)}
end

#alter_table(name, &block) ⇒ Object

Alters the given table with the specified block. Here are the currently available operations:

DB.alter_table :items do
  add_column :category, :text, :default => 'ruby'
  drop_column :category
  rename_column :cntr, :counter
  set_column_type :value, :float
  set_column_default :value, :float
  add_index [:group, :category]
  drop_index [:group, :category]
end

Note that #add_column accepts all the options available for column definitions using create_table, and #add_index accepts all the options available for index definition.

See Schema::AlterTableGenerator.



42
43
44
45
 
# File 'lib/sequel_core/database/schema.rb', line 42

def alter_table(name, &block)
  g = Schema::AlterTableGenerator.new(self, &block)
  alter_table_sql_list(name, g.operations).each {|sql| execute(sql)}
end

#connect ⇒ Object

Connects to the database. This method should be overridden by descendants.

Raises:

  • (NotImplementedError) 


198
199
200
 
# File 'lib/sequel_core/database.rb', line 198

def connect
  raise NotImplementedError, "#connect should be overridden by adapters"
end

#create_or_replace_view(name, source) ⇒ Object

Creates a view, replacing it if it already exists:

DB.create_or_replace_view(:cheap_items, "SELECT * FROM items WHERE price < 100")
DB.create_or_replace_view(:ruby_items, DB[:items].filter(:category => 'ruby'))



72
73
74
75
 
# File 'lib/sequel_core/database/schema.rb', line 72

def create_or_replace_view(name, source)
  source = source.sql if source.is_a?(Dataset)
  execute("CREATE OR REPLACE VIEW #{name} AS #{source}")
end

#create_table(name, &block) ⇒ Object

Creates a table with the columns given in the provided block:

DB.create_table :posts do
  primary_key :id, :serial
  column :title, :text
  column :content, :text
  index :title
end

See Schema::Generator.



57
58
59
60
 
# File 'lib/sequel_core/database/schema.rb', line 57

def create_table(name, &block)
  g = Schema::Generator.new(self, &block)
  create_table_sql_list(name, *g.create_info).each {|sql| execute(sql)}
end

#create_table!(name, &block) ⇒ Object

Forcibly creates a table. If the table already exists it is dropped.



63
64
65
66
 
# File 'lib/sequel_core/database/schema.rb', line 63

def create_table!(name, &block)
  drop_table(name) rescue nil
  create_table(name, &block)
end

#create_view(name, source) ⇒ Object

Creates a view based on a dataset or an SQL string:

DB.create_view(:cheap_items, "SELECT * FROM items WHERE price < 100")
DB.create_view(:ruby_items, DB[:items].filter(:category => 'ruby'))



81
82
83
84
 
# File 'lib/sequel_core/database/schema.rb', line 81

def create_view(name, source)
  source = source.sql if source.is_a?(Dataset)
  execute("CREATE VIEW #{name} AS #{source}")
end

#dataset ⇒ Object

Returns a blank dataset



203
204
205
 
# File 'lib/sequel_core/database.rb', line 203

def dataset
  ds = Sequel::Dataset.new(self)
end

#disconnect ⇒ Object

Disconnects from the database. This method should be overridden by descendants.

Raises:

  • (NotImplementedError) 


209
210
211
 
# File 'lib/sequel_core/database.rb', line 209

def disconnect
  raise NotImplementedError, "#disconnect should be overridden by adapters"
end

#drop_column(table, *args) ⇒ Object

Removes a column from the specified table:

DB.drop_column :items, :category

See alter_table.



91
92
93
 
# File 'lib/sequel_core/database/schema.rb', line 91

def drop_column(table, *args)
  alter_table(table) {drop_column(*args)}
end

#drop_index(table, columns) ⇒ Object

Removes an index for the given table and column/s:

DB.drop_index :posts, :title
DB.drop_index :posts, [:author, :title]

See alter_table.



101
102
103
 
# File 'lib/sequel_core/database/schema.rb', line 101

def drop_index(table, columns)
  alter_table(table) {drop_index(columns)}
end

#drop_table(*names) ⇒ Object

Drops one or more tables corresponding to the given table names:

DB.drop_table(:posts, :comments)



108
109
110
 
# File 'lib/sequel_core/database/schema.rb', line 108

def drop_table(*names)
  names.each {|n| execute(drop_table_sql(n))}
end

#drop_view(name) ⇒ Object

Drops a view:

DB.drop_view(:cheap_items)



115
116
117
 
# File 'lib/sequel_core/database/schema.rb', line 115

def drop_view(name)
  execute("DROP VIEW #{name}")
end

#execute(sql) ⇒ Object

Executes the given SQL. This method should be overridden in descendants.

Raises:

  • (NotImplementedError) 


214
215
216
 
# File 'lib/sequel_core/database.rb', line 214

def execute(sql)
  raise NotImplementedError, "#execute should be overridden by adapters"
end

#fetch(sql, *args, &block) ⇒ Object Also known as: >>

Fetches records for an arbitrary SQL statement. If a block is given, it is used to iterate over the records:

DB.fetch('SELECT * FROM items'){|r| p r}

The method returns a dataset instance:

DB.fetch('SELECT * FROM items').print

Fetch can also perform parameterized queries for protection against SQL injection:

DB.fetch('SELECT * FROM items WHERE name = ?', my_name).print



231
232
233
234
235
236
237
 
# File 'lib/sequel_core/database.rb', line 231

def fetch(sql, *args, &block)
  ds = dataset
  sql = sql.gsub('?') {|m|  ds.literal(args.shift)}
  ds.opts[:sql] = sql
  ds.fetch_rows(sql, &block) if block
  ds
end

#from(*args, &block) ⇒ Object

Returns a new dataset with the from method invoked. If a block is given, it is used as a filter on the dataset.



242
243
244
245
 
# File 'lib/sequel_core/database.rb', line 242

def from(*args, &block)
  ds = dataset.from(*args)
  block ? ds.filter(&block) : ds
end

#get(expr) ⇒ Object

Returns a single value from the database, e.g.:

# SELECT 1
DB.get(1) #=> 1 

# SELECT version()
DB.get(:version[]) #=> ...



254
255
256
 
# File 'lib/sequel_core/database.rb', line 254

def get(expr)
  dataset.get(expr)
end

#inspect ⇒ Object

Returns a string representation of the database object including the class name and the connection URI (or the opts if the URI cannot be constructed).



261
262
263
 
# File 'lib/sequel_core/database.rb', line 261

def inspect
  "#<#{self.class}: #{(uri rescue opts).inspect}>" 
end

#log_info(message) ⇒ Object

Log a message at level info to all loggers. All SQL logging goes through this method.



267
268
269
 
# File 'lib/sequel_core/database.rb', line 267

def log_info(message)
  @loggers.each{|logger| logger.info(message)}
end

#logger ⇒ Object

Return the first logger or nil if no loggers are being used. Should only be used for backwards compatibility.



273
274
275
 
# File 'lib/sequel_core/database.rb', line 273

def logger
  @loggers.first
end

#logger=(logger) ⇒ Object

Replace the array of loggers with the given logger(s).



278
279
280
 
# File 'lib/sequel_core/database.rb', line 278

def logger=(logger)
  @loggers = Array(logger)
end

#multi_threaded? ⇒ Boolean

Returns true unless the database is using a single-threaded connection pool.

Returns:

  • (Boolean) 


283
284
285
 
# File 'lib/sequel_core/database.rb', line 283

def multi_threaded?
  !@single_threaded
end

#query(&block) ⇒ Object

Returns a dataset modified by the given query block. See Dataset#query.



288
289
290
 
# File 'lib/sequel_core/database.rb', line 288

def query(&block)
  dataset.query(&block)
end

#quote_identifiers? ⇒ Boolean

Returns true if the database quotes identifiers.

Returns:

  • (Boolean) 


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

def quote_identifiers?
  @quote_identifiers
end

#rename_column(table, *args) ⇒ Object

Renames a column in the specified table. This method expects the current column name and the new column name:

DB.rename_column :items, :cntr, :counter

See alter_table.



134
135
136
 
# File 'lib/sequel_core/database/schema.rb', line 134

def rename_column(table, *args)
  alter_table(table) {rename_column(*args)}
end

#rename_table(*args) ⇒ Object

Renames a table:

DB.tables #=> [:items]
DB.rename_table :items, :old_items
DB.tables #=> [:old_items]



124
125
126
 
# File 'lib/sequel_core/database/schema.rb', line 124

def rename_table(*args)
  execute(rename_table_sql(*args))
end

#select(*args) ⇒ Object

Returns a new dataset with the select method invoked.



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

def select(*args)
  dataset.select(*args)
end

#serial_primary_key_options ⇒ Object

Default serial primary key options.



303
304
305
 
# File 'lib/sequel_core/database.rb', line 303

def serial_primary_key_options
  {:primary_key => true, :type => :integer, :auto_increment => true}
end

#set_column_default(table, *args) ⇒ Object

Sets the default value for the given column in the given table:

DB.set_column_default :items, :category, 'perl!'

See alter_table.



143
144
145
 
# File 'lib/sequel_core/database/schema.rb', line 143

def set_column_default(table, *args)
  alter_table(table) {set_column_default(*args)}
end

#set_column_type(table, *args) ⇒ Object

Set the data type for the given column in the given table:

DB.set_column_type :items, :price, :float

See alter_table.



152
153
154
 
# File 'lib/sequel_core/database/schema.rb', line 152

def set_column_type(table, *args)
  alter_table(table) {set_column_type(*args)}
end

#single_threaded? ⇒ Boolean

Returns true if the database is using a single-threaded connection pool.

Returns:

  • (Boolean) 


308
309
310
 
# File 'lib/sequel_core/database.rb', line 308

def single_threaded?
  @single_threaded
end

#synchronize(&block) ⇒ Object

Acquires a database connection, yielding it to the passed block.



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

def synchronize(&block)
  @pool.hold(&block)
end

#table_exists?(name) ⇒ Boolean

Returns true if a table with the given name exists.

Returns:

  • (Boolean) 


318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/sequel_core/database.rb', line 318

def table_exists?(name)
  begin 
    if respond_to?(:tables)
      tables.include?(name.to_sym)
    else
      from(name).first
      true
    end
  rescue
    false
  end
end

#test_connection ⇒ Object

Attempts to acquire a database connection. Returns true if successful. Will probably raise an error if unsuccessful.



333
334
335
336
 
# File 'lib/sequel_core/database.rb', line 333

def test_connection
  synchronize{|conn|}
  true
end

#transaction ⇒ Object

A simple implementation of SQL transactions. Nested transactions are not supported - calling #transaction within a transaction will reuse the current transaction. Should be overridden for databases that support nested transactions.



342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
 
# File 'lib/sequel_core/database.rb', line 342

def transaction
  @pool.hold do |conn|
    @transactions ||= []
    if @transactions.include? Thread.current
      return yield(conn)
    end
    log_info(SQL_BEGIN)
    conn.execute(SQL_BEGIN)
    begin
      @transactions << Thread.current
      yield(conn)
    rescue Exception => e
      log_info(SQL_ROLLBACK)
      conn.execute(SQL_ROLLBACK)
      raise e unless Error::Rollback === e
    ensure
      unless e
        log_info(SQL_COMMIT)
        conn.execute(SQL_COMMIT)
      end
      @transactions.delete(Thread.current)
    end
  end
end

#typecast_value(column_type, value) ⇒ Object

Typecast the value to the given column_type. Can be overridden in adapters to support database specific column types.



369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
 
# File 'lib/sequel_core/database.rb', line 369

def typecast_value(column_type, value)
  return nil if value.nil?
  case column_type
  when :integer
    Integer(value)
  when :string
    value.to_s
  when :float
    Float(value)
  when :decimal
    case value
    when BigDecimal
      value
    when String, Float
      value.to_d
    when Integer
      value.to_s.to_d
    else
      raise ArgumentError, "invalid value for BigDecimal: #{value.inspect}"
    end
  when :boolean
    case value
    when false, 0, "0", /\Af(alse)?\z/i
      false
    else
      value.blank? ? nil : true
    end
  when :date
    case value
    when Date
      value
    when DateTime, Time
      Date.new(value.year, value.month, value.day)
    when String
      value.to_date
    else
      raise ArgumentError, "invalid value for Date: #{value.inspect}"
    end
  when :time
    case value
    when Time
      value
    when String
      value.to_time
    else
      raise ArgumentError, "invalid value for Time: #{value.inspect}"
    end
  when :datetime
    raise(ArgumentError, "invalid value for #{tc}: #{value.inspect}") unless value.is_one_of?(DateTime, Date, Time, String)
    if Sequel.datetime_class === value
      # Already the correct class, no need to convert
      value
    else
      # First convert it to standard ISO 8601 time, then
      # parse that string using the time class.
      (Time === value ? value.iso8601 : value.to_s).to_sequel_time
    end
  when :blob
    value.to_blob
  else
    value
  end
end

#uri ⇒ Object Also known as: url

Returns the URI identifying the database. This method can raise an error if the database used options instead of a connection string.



436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
 
# File 'lib/sequel_core/database.rb', line 436

def uri
  uri = URI::Generic.new(
    self.class.adapter_scheme.to_s,
    nil,
    @opts[:host],
    @opts[:port],
    nil,
    "/#{@opts[:database]}",
    nil,
    nil,
    nil
  )
  uri.user = @opts[:user]
  uri.password = @opts[:password] if uri.user
  uri.to_s
end