Class: Sequel::Database

Inherits:
Object show all
Includes:
Schema::SQL
Defined in:
lib/sequel/database.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

SQL_BEGIN = 
'BEGIN'.freeze
SQL_COMMIT = 
'COMMIT'.freeze
SQL_ROLLBACK = 
'ROLLBACK'.freeze
@@adapters = 
Hash.new
@@single_threaded = 
false

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::PRIMARY_KEY, Schema::SQL::RESTRICT, Schema::SQL::SET_DEFAULT, Schema::SQL::SET_NULL, Schema::SQL::TYPES, Schema::SQL::UNDERSCORE, Schema::SQL::UNIQUE

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, #create_table_sql_list, #default_index_name, #drop_table_sql, #index_definition_sql, #index_list_sql_list, #literal, #on_delete_clause, #rename_table_sql, #schema_utility_dataset

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.



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# File 'lib/sequel/database.rb', line 15

def initialize(opts = {}, &block)
  Model.database_opened(self)
  @opts = opts
  
  # Determine if the DB is single threaded or multi threaded
  @single_threaded = opts[:single_threaded] || @@single_threaded
  # Construct pool
  if @single_threaded
    @pool = SingleThreadedPool.new(&block)
  else
    @pool = ConnectionPool.new(opts[:max_connections] || 4, &block)
  end
  @pool.connection_proc = block || proc {connect}

  @logger = opts[:logger]
end

Instance Attribute Details

#logger ⇒ Object

Returns the value of attribute logger.



9
10
11
 
# File 'lib/sequel/database.rb', line 9

def logger
  @logger
end

#opts ⇒ Object (readonly)

Returns the value of attribute opts.



8
9
10
 
# File 'lib/sequel/database.rb', line 8

def opts
  @opts
end

#pool ⇒ Object (readonly)

Returns the value of attribute pool.



8
9
10
 
# File 'lib/sequel/database.rb', line 8

def pool
  @pool
end

Class Method Details

.adapter_class(scheme) ⇒ Object

Raises:



373
374
375
376
377
378
379
380
381
 
# File 'lib/sequel/database.rb', line 373

def self.adapter_class(scheme)
  scheme = scheme.to_s =~ /\-/ ? scheme.to_s.gsub('-', '_').to_sym : scheme.to_sym
  unless c = @@adapters[scheme.to_sym]
    require File.join(File.dirname(__FILE__), "adapters/#{scheme}")
    c = @@adapters[scheme.to_sym]
  end
  raise Error::InvalidDatabaseScheme, "Invalid database scheme" unless c
  c
end

.adapter_scheme ⇒ Object

Returns the scheme for the Database class.



357
358
359
 
# File 'lib/sequel/database.rb', line 357

def self.adapter_scheme
  @scheme
end

.connect(conn_string, opts = nil) ⇒ Object

call-seq:

Sequel::Database.connect(conn_string)
Sequel::Database.connect(opts)
Sequel.connect(conn_string)
Sequel.connect(opts)
Sequel.open(conn_string)
Sequel.open(opts)

Creates a new database object based on the supplied connection string and or options. If a URI is used, the URI scheme determines the database class used, and the rest of the string specifies the connection options. For example:

DB = Sequel.open 'sqlite:///blog.db'

The second form of this method takes an options:

DB = Sequel.open :adapter => :sqlite, :database => 'blog.db'



401
402
403
404
405
406
407
408
409
410
411
412
413
 
# File 'lib/sequel/database.rb', line 401

def self.connect(conn_string, opts = nil)
  if conn_string.is_a?(String)
    uri = URI.parse(conn_string)
    scheme = uri.scheme
    scheme = :dbi if scheme =~ /^dbi-(.+)/
    c = adapter_class(scheme)
    c.new(c.uri_to_options(uri).merge(opts || {}))
  else
    opts = conn_string.merge(opts || {})
    c = adapter_class(opts[:adapter])
    c.new(opts)
  end
end

.set_adapter_scheme(scheme) ⇒ Object

Sets the adapter scheme for the Database class. Call this method in descendnants of Database to allow connection using a URL. For example the following:

class DB2::Database < Sequel::Database
  set_adapter_scheme :db2
  ...
end

would allow connection using:

Sequel.open('db2://user:password@dbserver/mydb')



351
352
353
354
 
# File 'lib/sequel/database.rb', line 351

def self.set_adapter_scheme(scheme)
  @scheme = scheme
  @@adapters[scheme.to_sym] = self
end

.single_threaded=(value) ⇒ Object

Sets the default single_threaded mode for new databases.



418
419
420
 
# File 'lib/sequel/database.rb', line 418

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.



363
364
365
366
367
368
369
370
371
 
# File 'lib/sequel/database.rb', line 363

def self.uri_to_options(uri)
  {
    :user => uri.user,
    :password => uri.password,
    :host => uri.host,
    :port => uri.port,
    :database => (uri.path =~ /\/(.*)/) && ($1)
  }
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 give, comments and excessive white space are removed. See also Array#to_sql.



146
 
# File 'lib/sequel/database.rb', line 146

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

#[](*args) ⇒ 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, the dataset returned has its from option set to the given arguments:

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



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

def [](*args)
  (String === args.first) ? fetch(*args) : from(*args)
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'



226
227
228
 
# File 'lib/sequel/database.rb', line 226

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



263
264
265
 
# File 'lib/sequel/database.rb', line 263

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.



216
217
218
219
 
# File 'lib/sequel/database.rb', line 216

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 overriden by descendants.

Raises:

  • (NotImplementedError) 


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

def connect
  raise NotImplementedError, "#connect should be overriden 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'))



299
300
301
302
 
# File 'lib/sequel/database.rb', line 299

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. The easiest way to use this method is to provide a block:

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



175
176
177
178
 
# File 'lib/sequel/database.rb', line 175

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.



181
182
183
184
 
# File 'lib/sequel/database.rb', line 181

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'))



290
291
292
293
 
# File 'lib/sequel/database.rb', line 290

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



73
74
75
 
# File 'lib/sequel/database.rb', line 73

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

#disconnect ⇒ Object

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

Raises:

  • (NotImplementedError) 


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

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

#drop_column(table, *args) ⇒ Object

Removes a column from the specified table:

DB.drop_column :items, :category



233
234
235
 
# File 'lib/sequel/database.rb', line 233

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

#drop_index(table, *args) ⇒ Object

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

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



271
272
273
 
# File 'lib/sequel/database.rb', line 271

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

#drop_table(*names) ⇒ Object

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



187
188
189
 
# File 'lib/sequel/database.rb', line 187

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)



307
308
309
 
# File 'lib/sequel/database.rb', line 307

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

#execute(sql) ⇒ Object

Raises a Sequel::Error::NotImplemented. This method is overriden in descendants.

Raises:

  • (NotImplementedError) 


139
140
141
 
# File 'lib/sequel/database.rb', line 139

def execute(sql)
  raise NotImplementedError, "#execute should be overriden 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}

If a block is not given, 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

A short-hand form for Database#fetch is Database#[]:

DB['SELECT * FROM items'].each {|r| p r}



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

def fetch(sql, *args, &block)
  ds = dataset
  sql = sql.gsub('?') {|m|  ds.literal(args.shift)}
  if block
    ds.fetch_rows(sql, &block)
  else
    ds.opts[:sql] = sql
    ds
  end
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.



115
116
117
118
 
# File 'lib/sequel/database.rb', line 115

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

#multi_threaded? ⇒ Boolean

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



44
45
46
 
# File 'lib/sequel/database.rb', line 44

def multi_threaded?
  !@single_threaded
end

#query(&block) ⇒ Object

Converts a query block into a dataset. For more information see Dataset#query.



109
110
111
 
# File 'lib/sequel/database.rb', line 109

def query(&block)
  dataset.query(&block)
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



241
242
243
 
# File 'lib/sequel/database.rb', line 241

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]



196
197
198
 
# File 'lib/sequel/database.rb', line 196

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

#select(*args) ⇒ Object

Returns a new dataset with the select method invoked.



121
 
# File 'lib/sequel/database.rb', line 121

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

#serial_primary_key_options ⇒ Object

default serial primary key definition. this should be overriden for each adapter.



163
164
165
 
# File 'lib/sequel/database.rb', line 163

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!'



255
256
257
 
# File 'lib/sequel/database.rb', line 255

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



248
249
250
 
# File 'lib/sequel/database.rb', line 248

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.



49
50
51
 
# File 'lib/sequel/database.rb', line 49

def single_threaded?
  @single_threaded
end

#synchronize(&block) ⇒ Object

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



149
150
151
 
# File 'lib/sequel/database.rb', line 149

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

#table_exists?(name) ⇒ Boolean

Returns true if the given table exists.



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

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

#test_connection ⇒ Object

Returns true if there is a database connection



154
155
156
157
 
# File 'lib/sequel/database.rb', line 154

def test_connection
  @pool.hold {|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. May be overridden for databases that support nested transactions.



319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
 
# File 'lib/sequel/database.rb', line 319

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

#uri ⇒ Object Also known as: url

Returns the URI identifying the database.



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/sequel/database.rb', line 54

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