Class: Sequel::Database

Inherits:
Object show all
Extended by:
Metaprogramming
Includes:
Metaprogramming
Defined in:
lib/sequel/database.rb,
lib/sequel/deprecated.rb,
lib/sequel/extensions/query.rb,
lib/sequel/database/schema_sql.rb,
lib/sequel/adapters/shared/mysql.rb,
lib/sequel/database/schema_methods.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 do firebird informix jdbc mysql odbc openbase oracle postgres sqlite'.collect{|x| x.to_sym}
SQL_BEGIN = 
'BEGIN'.freeze
SQL_COMMIT = 
'COMMIT'.freeze
SQL_ROLLBACK = 
'ROLLBACK'.freeze
AUTOINCREMENT = 
'AUTOINCREMENT'.freeze
CASCADE = 
'CASCADE'.freeze
COMMA_SEPARATOR = 
', '.freeze
NO_ACTION = 
'NO ACTION'.freeze
NOT_NULL = 
' NOT NULL'.freeze
NULL = 
' NULL'.freeze
PRIMARY_KEY = 
' PRIMARY KEY'.freeze
RESTRICT = 
'RESTRICT'.freeze
SET_DEFAULT = 
'SET DEFAULT'.freeze
SET_NULL = 
'SET NULL'.freeze
TYPES = 
Hash.new {|h, k| k}
UNDERSCORE = 
'_'.freeze
UNIQUE = 
' UNIQUE'.freeze
UNSIGNED = 
' UNSIGNED'.freeze
@@adapters =

Hash of adapters that have been used

Hash.new
@@identifier_input_method =

The identifier input method to use by default

nil
@@identifier_output_method =

The identifier output method to use by default

nil
@@single_threaded =

Whether to use the single threaded connection pool by default

false
@@quote_identifiers =

Whether to quote identifiers (columns and tables) by default

nil

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Metaprogramming

meta_def

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.

Takes the following options:

  • :default_schema : The default schema to use, should generally be nil

  • :disconnection_proc: A proc used to disconnect the connection.

  • :identifier_input_method: A string method symbol to call on identifiers going into the database

  • :identifier_output_method: A string method symbol to call on identifiers coming from the database

  • :loggers : An array of loggers to use.

  • :quote_identifiers : Whether to quote identifiers

  • :single_threaded : Whether to use a single-threaded connection pool

All options given are also passed to the ConnectionPool. If a block is given, it is used as the connection_proc for the ConnectionPool.



68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/sequel/database.rb', line 68

def initialize(opts = {}, &block)
  @opts ||= opts
  
  @single_threaded = opts.include?(:single_threaded) ? opts[:single_threaded] : @@single_threaded
  @schemas = nil
  @default_schema = opts.include?(:default_schema) ? opts[:default_schema] : default_schema_default
  @prepared_statements = {}
  @transactions = []
  @identifier_input_method = nil
  @identifier_output_method = nil
  @quote_identifiers = nil
  if opts.include?(:upcase_identifiers)
    Deprecation.deprecate('The :upcase_identifiers Database option', 'Use the :identifier_input_method => :upcase option instead')
    @identifier_input_method = opts[:upcase_identifiers] ? :upcase : ""
  end
  @pool = (@single_threaded ? SingleThreadedPool : ConnectionPool).new(connection_pool_default_options.merge(opts), &block)
  @pool.connection_proc = proc{|server| connect(server)} unless block
  @pool.disconnection_proc = proc{|conn| disconnect_connection(conn)} unless opts[:disconnection_proc]

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

Instance Attribute Details

#default_schema ⇒ Object

The default schema to use



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

def default_schema
  @default_schema
end

#loggers ⇒ Object

Array of SQL loggers to use for this database



41
42
43
 
# File 'lib/sequel/database.rb', line 41

def loggers
  @loggers
end

#opts ⇒ Object (readonly)

The options for this database



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

def opts
  @opts
end

#pool ⇒ Object (readonly)

The connection pool for this database



47
48
49
 
# File 'lib/sequel/database.rb', line 47

def pool
  @pool
end

#prepared_statements ⇒ Object (readonly)

The prepared statement objects for this database, keyed by name



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

def prepared_statements
  @prepared_statements
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.



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/sequel/database.rb', line 96

def self.adapter_class(scheme)
  scheme = scheme.to_s.gsub('-', '_').to_sym
  
  if (klass = @@adapters[scheme]).nil?
    # attempt to load the adapter file
    begin
      Sequel.require "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.



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

def self.adapter_scheme
  @scheme
end

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

Connects to a database. See Sequel.connect.



121
122
123
124
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
 
# File 'lib/sequel/database.rb', line 121

def self.connect(conn_string, opts = {}, &block)
  if conn_string.is_a?(String)
    if conn_string =~ /\Ajdbc:/
      c = adapter_class(:jdbc)
      opts = {:uri=>conn_string}.merge(opts)
    elsif conn_string =~ /\Ado:/
      c = adapter_class(:do)
      opts = {:uri=>conn_string}.merge(opts)
    else
      uri = URI.parse(conn_string)
      scheme = uri.scheme
      scheme = :dbi if scheme =~ /\Adbi-/
      c = adapter_class(scheme)
      uri_options = {}
      uri.query.split('&').collect{|s| s.split('=')}.each{|k,v| uri_options[k.to_sym] = v} unless uri.query.to_s.strip.empty?
      opts = c.send(:uri_to_options, uri).merge(uri_options).merge(opts)
    end
  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

.identifier_input_method ⇒ Object

The method to call on identifiers going into the database



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

def self.identifier_input_method
  @@identifier_input_method
end

.identifier_input_method=(v) ⇒ Object

Set the method to call on identifiers going into the database



167
168
169
 
# File 'lib/sequel/database.rb', line 167

def self.identifier_input_method=(v)
  @@identifier_input_method = v || ""
end

.identifier_output_method ⇒ Object

The method to call on identifiers coming from the database



172
173
174
 
# File 'lib/sequel/database.rb', line 172

def self.identifier_output_method
  @@identifier_output_method
end

.identifier_output_method=(v) ⇒ Object

Set the method to call on identifiers coming from the database



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

def self.identifier_output_method=(v)
  @@identifier_output_method = v || ""
end

.quote_identifiers=(value) ⇒ Object

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



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

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=.



189
190
191
 
# File 'lib/sequel/database.rb', line 189

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

.upcase_identifiers=(value) ⇒ Object 



60
61
62
63
 
# File 'lib/sequel/deprecated.rb', line 60

def self.upcase_identifiers=(value)
  Deprecation.deprecate('Sequel::Database.upcase_identifiers=', 'Use Sequel::Database.identifier_input_method = :upcase or nil')
  self.identifier_input_method = value ? :upcase : nil
end

Instance Method Details

#<<(sql) ⇒ Object

Executes the supplied SQL statement string.



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

def <<(sql)
  Deprecation.deprecate('Passing an array argument to Database#<<', 'Use array.each{|x| database << x}') if Array === sql
  execute_ddl((Array === sql) ? sql.to_sql : sql)
end

#>>(*args, &block) ⇒ Object 



75
76
77
78
 
# File 'lib/sequel/deprecated.rb', line 75

def >>(*args, &block)
  Deprecation.deprecate('Sequel::Database#>>', 'Use Sequel::Database#fetch')
  fetch(*args, &block)
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"



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

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/database/schema_methods.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/database/schema_methods.rb', line 20

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

#alter_table(name, generator = nil, &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
46
 
# File 'lib/sequel/database/schema_methods.rb', line 42

def alter_table(name, generator=nil, &block)
  remove_cached_schema(name)
  generator ||= Schema::AlterTableGenerator.new(self, &block)
  alter_table_sql_list(name, generator.operations).flatten.each {|sql| execute_ddl(sql)}
end

#alter_table_sql(table, op) ⇒ Object

The SQL to execute to modify the DDL for the given table name. op should be one of the operations returned by the AlterTableGenerator.



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/sequel/database/schema_sql.rb', line 25

def alter_table_sql(table, op)
  quoted_name = quote_identifier(op[:name]) if op[:name]
  alter_table_op = case op[:op]
  when :add_column
    "ADD COLUMN #{column_definition_sql(op)}"
  when :drop_column
    "DROP COLUMN #{quoted_name}"
  when :rename_column
    "RENAME COLUMN #{quoted_name} TO #{quote_identifier(op[:new_name])}"
  when :set_column_type
    "ALTER COLUMN #{quoted_name} TYPE #{type_literal(op)}"
  when :set_column_default
    "ALTER COLUMN #{quoted_name} SET DEFAULT #{literal(op[:default])}"
  when :set_column_null
    "ALTER COLUMN #{quoted_name} #{op[:null] ? 'DROP' : 'SET'} NOT NULL"
  when :add_index
    return index_definition_sql(table, op)
  when :drop_index
    return drop_index_sql(table, op)
  when :add_constraint
    "ADD #{constraint_definition_sql(op)}"
  when :drop_constraint
    "DROP CONSTRAINT #{quoted_name}"
  else
    raise Error, "Unsupported ALTER TABLE operation"
  end
  "ALTER TABLE #{quote_schema_table(table)} #{alter_table_op}"
end

#alter_table_sql_list(table, operations) ⇒ Object

Array of SQL DDL modification statements for the given table, corresponding to the DDL changes specified by the operations.



56
57
58
 
# File 'lib/sequel/database/schema_sql.rb', line 56

def alter_table_sql_list(table, operations)
  operations.map{|op| alter_table_sql(table, op)}
end

#auto_increment_sql ⇒ Object

The SQL string specify the autoincrement property, generally used by primary keys.



62
63
64
 
# File 'lib/sequel/database/schema_sql.rb', line 62

def auto_increment_sql
  AUTOINCREMENT
end

#call(ps_name, hash = {}) ⇒ Object

Call the prepared statement with the given name with the given hash of arguments.



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

def call(ps_name, hash={})
  prepared_statements[ps_name].call(hash)
end

#column_definition_sql(column) ⇒ Object

SQL DDL fragment containing the column creation SQL for the given column.



67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/sequel/database/schema_sql.rb', line 67

def column_definition_sql(column)
  return constraint_definition_sql(column) if column[:type] == :check
  sql = "#{quote_identifier(column[:name])} #{type_literal(column)}"
  sql << UNIQUE if column[:unique]
  sql << NOT_NULL if column[:null] == false
  sql << NULL if column[:null] == true
  sql << " DEFAULT #{literal(column[:default])}" if column.include?(:default)
  sql << PRIMARY_KEY if column[:primary_key]
  sql << " #{auto_increment_sql}" if column[:auto_increment]
  sql << column_references_sql(column) if column[:table]
  sql
end

#column_list_sql(columns) ⇒ Object

SQL DDL fragment containing the column creation SQL for all given columns, used instead a CREATE TABLE block.



82
83
84
 
# File 'lib/sequel/database/schema_sql.rb', line 82

def column_list_sql(columns)
  columns.map{|c| column_definition_sql(c)}.join(COMMA_SEPARATOR)
end

#column_references_sql(column) ⇒ Object Also known as: default_column_references_sql

SQL DDL fragment for column foreign key references



87
88
89
90
91
92
93
 
# File 'lib/sequel/database/schema_sql.rb', line 87

def column_references_sql(column)
  sql = " REFERENCES #{quote_schema_table(column[:table])}"
  sql << "(#{Array(column[:key]).map{|x| quote_identifier(x)}.join(COMMA_SEPARATOR)})" if column[:key]
  sql << " ON DELETE #{on_delete_clause(column[:on_delete])}" if column[:on_delete]
  sql << " ON UPDATE #{on_delete_clause(column[:on_update])}" if column[:on_update]
  sql
end

#connect ⇒ Object

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

Raises:

  • (NotImplementedError) 


253
254
255
 
# File 'lib/sequel/database.rb', line 253

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

#constraint_definition_sql(constraint) ⇒ Object

SQL DDL fragment specifying a constraint on a table.



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/sequel/database/schema_sql.rb', line 96

def constraint_definition_sql(constraint)
  sql = constraint[:name] ? "CONSTRAINT #{quote_identifier(constraint[:name])} " : ""
  case constraint[:constraint_type]
  when :primary_key
    sql << "PRIMARY KEY #{literal(constraint[:columns])}"
  when :foreign_key
    sql << "FOREIGN KEY #{literal(constraint[:columns])}"
    sql << column_references_sql(constraint)
  when :unique
    sql << "UNIQUE #{literal(constraint[:columns])}"
  else
    check = constraint[:check]
    sql << "CHECK #{filter_expr((check.is_a?(Array) && check.length == 1) ? check.first : check)}"
  end
  sql
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'))



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

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

#create_table(name, options = {}, &block) ⇒ Object

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

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

See Schema::Generator.



58
59
60
61
 
# File 'lib/sequel/database/schema_methods.rb', line 58

def create_table(name, options={}, &block)
  options = {:generator=>options} if options.is_a?(Schema::Generator)
  create_table_sql_list(name, *((options[:generator] || Schema::Generator.new(self, &block)).create_info << options)).flatten.each {|sql| execute_ddl(sql)}
end

#create_table!(name, options = {}, &block) ⇒ Object

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



64
65
66
67
 
# File 'lib/sequel/database/schema_methods.rb', line 64

def create_table!(name, options={}, &block)
  drop_table(name) rescue nil
  create_table(name, options, &block)
end

#create_table_sql_list(name, columns, indexes = nil, options = {}) ⇒ Object

Array of SQL DDL statements, the first for creating a table with the given name and column specifications, and the others for specifying indexes on the table.



116
117
118
119
120
 
# File 'lib/sequel/database/schema_sql.rb', line 116

def create_table_sql_list(name, columns, indexes = nil, options = {})
  sql = ["CREATE TABLE #{quote_schema_table(name)} (#{column_list_sql(columns)})"]
  sql.concat(index_list_sql_list(name, indexes)) if indexes && !indexes.empty?
  sql
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'))



83
84
85
86
 
# File 'lib/sequel/database/schema_methods.rb', line 83

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

#dataset ⇒ Object

Returns a blank dataset



258
259
260
 
# File 'lib/sequel/database.rb', line 258

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

#default_index_name(table_name, columns) ⇒ Object

Default index name for the table and columns, may be too long for certain databases.



124
125
126
127
 
# File 'lib/sequel/database/schema_sql.rb', line 124

def default_index_name(table_name, columns)
  schema, table = schema_and_table(table_name)
  "#{"#{schema}_" if schema and schema != default_schema}#{table}_#{columns.map{|c| [String, Symbol].any?{|cl| c.is_a?(cl)} ? c : literal(c).gsub(/\W/, '_')}.join(UNDERSCORE)}_index"
end

#disconnect ⇒ Object

Disconnects all available connections from the connection pool. If any connections are currently in use, they will not be disconnected.



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

def disconnect
  pool.disconnect
end

#drop_column(table, *args) ⇒ Object

Removes a column from the specified table:

DB.drop_column :items, :category

See alter_table.



93
94
95
 
# File 'lib/sequel/database/schema_methods.rb', line 93

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.



103
104
105
 
# File 'lib/sequel/database/schema_methods.rb', line 103

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

#drop_index_sql(table, op) ⇒ Object

The SQL to drop an index for the table.



130
131
132
 
# File 'lib/sequel/database/schema_sql.rb', line 130

def drop_index_sql(table, op)
  "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))}"
end

#drop_table(*names) ⇒ Object

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

DB.drop_table(:posts, :comments)



110
111
112
113
114
115
 
# File 'lib/sequel/database/schema_methods.rb', line 110

def drop_table(*names)
  names.each do |n|
    remove_cached_schema(n)
    execute_ddl(drop_table_sql(n))
  end
end

#drop_table_sql(name) ⇒ Object

SQL DDL statement to drop the table with the given name.



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

def drop_table_sql(name)
  "DROP TABLE #{quote_schema_table(name)}"
end

#drop_view(*names) ⇒ Object

Drops a view:

DB.drop_view(:cheap_items)



120
121
122
123
124
125
 
# File 'lib/sequel/database/schema_methods.rb', line 120

def drop_view(*names)
  names.each do |n|
    remove_cached_schema(n)
    execute_ddl("DROP VIEW #{quote_schema_table(n)}")
  end
end

#execute(sql, opts = {}) ⇒ Object

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

Raises:

  • (NotImplementedError) 


269
270
271
 
# File 'lib/sequel/database.rb', line 269

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

#execute_ddl(sql, opts = {}, &block) ⇒ Object

Method that should be used when submitting any DDL (Data Definition Language) SQL. By default, calls execute_dui.



275
276
277
 
# File 'lib/sequel/database.rb', line 275

def execute_ddl(sql, opts={}, &block)
  execute_dui(sql, opts, &block)
end

#execute_dui(sql, opts = {}, &block) ⇒ Object

Method that should be used when issuing a DELETE, UPDATE, or INSERT statement. By default, calls execute.



281
282
283
 
# File 'lib/sequel/database.rb', line 281

def execute_dui(sql, opts={}, &block)
  execute(sql, opts, &block)
end

#execute_insert(sql, opts = {}, &block) ⇒ Object

Method that should be used when issuing a INSERT statement. By default, calls execute_dui.



287
288
289
 
# File 'lib/sequel/database.rb', line 287

def execute_insert(sql, opts={}, &block)
  execute_dui(sql, opts, &block)
end

#fetch(sql, *args, &block) ⇒ Object

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



304
305
306
307
308
 
# File 'lib/sequel/database.rb', line 304

def fetch(sql, *args, &block)
  ds = dataset.with_sql(sql, *args)
  ds.each(&block) if block
  ds
end

#filter_expr(*args, &block) ⇒ Object

Proxy the filter_expr call to the dataset, used for creating constraints.



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

def filter_expr(*args, &block)
  schema_utility_dataset.literal(schema_utility_dataset.send(:filter_expr, *args, &block))
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.



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

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

#get(*args, &block) ⇒ Object

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

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

# SELECT version()
DB.get(:version.sql_function) #=> ...



324
325
326
 
# File 'lib/sequel/database.rb', line 324

def get(*args, &block)
  dataset.get(*args, &block)
end

#identifier_input_method ⇒ Object

The method to call on identifiers going into the database



329
330
331
332
333
334
335
336
337
338
339
 
# File 'lib/sequel/database.rb', line 329

def identifier_input_method
  case @identifier_input_method
  when nil
    @identifier_input_method = @opts.include?(:identifier_input_method) ? @opts[:identifier_input_method] : (@@identifier_input_method.nil? ? identifier_input_method_default : @@identifier_input_method)
    @identifier_input_method == "" ? nil : @identifier_input_method
  when ""
    nil
  else
    @identifier_input_method
  end
end

#identifier_input_method=(v) ⇒ Object

Set the method to call on identifiers going into the database



342
343
344
345
 
# File 'lib/sequel/database.rb', line 342

def identifier_input_method=(v)
  reset_schema_utility_dataset
  @identifier_input_method = v || ""
end

#identifier_output_method ⇒ Object

The method to call on identifiers coming from the database



348
349
350
351
352
353
354
355
356
357
358
 
# File 'lib/sequel/database.rb', line 348

def identifier_output_method
  case @identifier_output_method
  when nil
    @identifier_output_method = @opts.include?(:identifier_output_method) ? @opts[:identifier_output_method] : (@@identifier_output_method.nil? ? identifier_output_method_default : @@identifier_output_method)
    @identifier_output_method == "" ? nil : @identifier_output_method
  when ""
    nil
  else
    @identifier_output_method
  end
end

#identifier_output_method=(v) ⇒ Object

Set the method to call on identifiers coming from the database



361
362
363
364
 
# File 'lib/sequel/database.rb', line 361

def identifier_output_method=(v)
  reset_schema_utility_dataset
  @identifier_output_method = v || ""
end

#index_definition_sql(table_name, index) ⇒ Object

SQL DDL statement for creating an index for the table with the given name and index specifications.



146
147
148
149
150
151
152
153
154
155
 
# File 'lib/sequel/database/schema_sql.rb', line 146

def index_definition_sql(table_name, index)
  index_name = index[:name] || default_index_name(table_name, index[:columns])
  if index[:type]
    raise Error, "Index types are not supported for this database"
  elsif index[:where]
    raise Error, "Partial indexes are not supported for this database"
  else
    "CREATE #{'UNIQUE ' if index[:unique]}INDEX #{quote_identifier(index_name)} ON #{quote_identifier(table_name)} #{literal(index[:columns])}"
  end
end

#index_list_sql_list(table_name, indexes) ⇒ Object

Array of SQL DDL statements, one for each index specification, for the given table.



159
160
161
 
# File 'lib/sequel/database/schema_sql.rb', line 159

def index_list_sql_list(table_name, indexes)
  indexes.map{|i| index_definition_sql(table_name, i)}
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).



369
370
371
 
# File 'lib/sequel/database.rb', line 369

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

#literal(v) ⇒ Object

Proxy the literal call to the dataset, used for default values.



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

def literal(v)
  schema_utility_dataset.literal(v)
end

#log_info(message, args = nil) ⇒ Object

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



375
376
377
378
 
# File 'lib/sequel/database.rb', line 375

def log_info(message, args=nil)
  message = "#{message}; #{args.inspect}" if args
  @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.



382
383
384
 
# File 'lib/sequel/database.rb', line 382

def logger
  @loggers.first
end

#logger=(logger) ⇒ Object

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



387
388
389
 
# File 'lib/sequel/database.rb', line 387

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

#multi_threaded? ⇒ Boolean

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

Returns:

  • (Boolean) 


392
393
394
 
# File 'lib/sequel/database.rb', line 392

def multi_threaded?
  !@single_threaded
end

#on_delete_clause(action) ⇒ Object

SQL DDL ON DELETE fragment to use, based on the given action. The following actions are recognized:

  • :cascade - Delete rows referencing this row.

  • :no_action (default) - Raise an error if other rows reference this row, allow deferring of the integrity check.

  • :restrict - Raise an error if other rows reference this row, but do not allow deferring the integrity check.

  • :set_default - Set columns referencing this row to their default value.

  • :set_null - Set columns referencing this row to NULL.



178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'lib/sequel/database/schema_sql.rb', line 178

def on_delete_clause(action)
  case action
  when :restrict
    RESTRICT
  when :cascade
    CASCADE
  when :set_null
    SET_NULL
  when :set_default
    SET_DEFAULT
  else
    NO_ACTION
  end
end

#query(&block) ⇒ Object

Return a dataset modified by the query block



4
5
6
7
 
# File 'lib/sequel/extensions/query.rb', line 4

def query(&block)
  Deprecation.deprecate('Sequel::Database#query', 'require "sequel/extensions/query" first')
  dataset.query(&block)
end

#quote_identifier(v) ⇒ Object

Proxy the quote_identifier method to the dataset, used for quoting tables and columns.



199
200
201
 
# File 'lib/sequel/database/schema_sql.rb', line 199

def quote_identifier(v)
  schema_utility_dataset.quote_identifier(v)
end

#quote_identifiers=(v) ⇒ Object

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



397
398
399
400
 
# File 'lib/sequel/database.rb', line 397

def quote_identifiers=(v)
  reset_schema_utility_dataset
  @quote_identifiers = v
end

#quote_identifiers? ⇒ Boolean

Returns true if the database quotes identifiers.

Returns:

  • (Boolean) 


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

def quote_identifiers?
  return @quote_identifiers unless @quote_identifiers.nil?
  @quote_identifiers = @opts.include?(:quote_identifiers) ? @opts[:quote_identifiers] : (@@quote_identifiers.nil? ? quote_identifiers_default : @@quote_identifiers)
end

#quote_schema_table(table) ⇒ Object

Proxy the quote_schema_table method to the dataset



194
195
196
 
# File 'lib/sequel/database/schema_sql.rb', line 194

def quote_schema_table(table)
  schema_utility_dataset.quote_schema_table(table)
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.



142
143
144
 
# File 'lib/sequel/database/schema_methods.rb', line 142

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]



132
133
134
 
# File 'lib/sequel/database/schema_methods.rb', line 132

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

#rename_table_sql(name, new_name) ⇒ Object

SQL DDL statement for renaming a table.



204
205
206
 
# File 'lib/sequel/database/schema_sql.rb', line 204

def rename_table_sql(name, new_name)
  "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_schema_table(new_name)}"
end

#schema(table = nil, opts = {}) ⇒ Object

Parse the schema from the database. If the table_name is not given, returns the schema for all tables as a hash. If the table_name is given, returns the schema for a single table as an array with all members being arrays of length 2. Available options are:

  • :reload - Get fresh information from the database, instead of using cached information. If table_name is blank, :reload should be used unless you are sure that schema has not been called before with a table_name, otherwise you may only getting the schemas for tables that have been requested explicitly.

  • :schema - An explicit schema to use. It may also be implicitly provided via the table name.

Raises:



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
 
# File 'lib/sequel/database/schema_sql.rb', line 220

def schema(table = nil, opts={})
  Deprecation.deprecate('Calling Database#schema without a table argument', 'Use database.tables.inject({}){|h, m| h[m] = database.schema(m); h}') unless table
  raise(Error, 'schema parsing is not implemented on this database') unless respond_to?(:schema_parse_table, true)

  if table
    sch, table_name = schema_and_table(table)
    quoted_name = quote_schema_table(table)
  end
  opts = opts.merge(:schema=>sch) if sch && !opts.include?(:schema)
  if opts[:reload] && @schemas
    if table_name
      @schemas.delete(quoted_name)
    else
      @schemas = nil
    end
  end

  if @schemas
    if table_name
      return @schemas[quoted_name] if @schemas[quoted_name]
    else
      return @schemas
    end
  end
  
  raise(Error, '#tables does not exist, you must provide a specific table to #schema') if table.nil? && !respond_to?(:tables, true)

  @schemas ||= Hash.new do |h,k|
    quote_name = quote_schema_table(k)
    h[quote_name] if h.include?(quote_name)
  end

  if table_name
    cols = schema_parse_table(table_name, opts)
    raise(Error, 'schema parsing returned no columns, table probably doesn\'t exist') if cols.nil? || cols.empty?
    @schemas[quoted_name] = cols
  else
    tables.each{|t| @schemas[quote_schema_table(t)] = schema_parse_table(t.to_s, opts)}
    @schemas
  end
end

#schema_utility_dataset ⇒ Object

The dataset to use for proxying certain schema methods.



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

def schema_utility_dataset
  @schema_utility_dataset ||= dataset
end

#select(*args, &block) ⇒ Object

Returns a new dataset with the select method invoked.



409
410
411
 
# File 'lib/sequel/database.rb', line 409

def select(*args, &block)
  dataset.select(*args, &block)
end

#serial_primary_key_options ⇒ Object

Default serial primary key options.



414
415
416
 
# File 'lib/sequel/database.rb', line 414

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.



151
152
153
 
# File 'lib/sequel/database/schema_methods.rb', line 151

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.



160
161
162
 
# File 'lib/sequel/database/schema_methods.rb', line 160

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) 


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

def single_threaded?
  @single_threaded
end

#synchronize(server = nil, &block) ⇒ Object

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



424
425
426
 
# File 'lib/sequel/database.rb', line 424

def synchronize(server=nil, &block)
  @pool.hold(server || :default, &block)
end

#table_exists?(name) ⇒ Boolean

Returns true if a table with the given name exists. This requires a query to the database unless this database object already has the schema for the given table name.

Returns:

  • (Boolean) 


431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'lib/sequel/database.rb', line 431

def table_exists?(name)
  if @schemas && @schemas[name]
    true
  else
    begin 
      from(name).first
      true
    rescue
      false
    end
  end
end

#test_connection(server = nil) ⇒ Object

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



446
447
448
449
 
# File 'lib/sequel/database.rb', line 446

def test_connection(server=nil)
  synchronize(server){|conn|}
  true
end

#transaction(opts = {}) ⇒ 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.



455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
 
# File 'lib/sequel/database.rb', line 455

def transaction(opts={})
  unless opts.is_a?(Hash)
    Deprecation.deprecate('Passing an argument other than a Hash to Database#transaction', "Use DB.transaction(:server=>#{opts.inspect})") 
    opts = {:server=>opts}
  end
  synchronize(opts[:server]) do |conn|
    return yield(conn) if @transactions.include?(Thread.current)
    log_info(begin_transaction_sql)
    conn.execute(begin_transaction_sql)
    begin
      @transactions << Thread.current
      yield(conn)
    rescue Exception => e
      log_info(rollback_transaction_sql)
      conn.execute(rollback_transaction_sql)
      transaction_error(e)
    ensure
      unless e
        log_info(commit_transaction_sql)
        conn.execute(commit_transaction_sql)
      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. This method should raise Sequel::Error::InvalidValue if assigned value is invalid.



485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
 
# File 'lib/sequel/database.rb', line 485

def typecast_value(column_type, value)
  return nil if value.nil?
  begin
    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 Sequel::Error::InvalidValue, "invalid value for BigDecimal: #{value.inspect}"
      end
    when :boolean
      case value
      when false, 0, "0", /\Af(alse)?\z/i
        false
      else
        blank_object?(value) ? nil : true
      end
    when :date
      case value
      when Date
        value
      when DateTime, Time
        Date.new(value.year, value.month, value.day)
      when String
        Sequel.string_to_date(value)
      else
        raise Sequel::Error::InvalidValue, "invalid value for Date: #{value.inspect}"
      end
    when :time
      case value
      when Time
        value
      when String
        Sequel.string_to_time(value)
      else
        raise Sequel::Error::InvalidValue, "invalid value for Time: #{value.inspect}"
      end
    when :datetime
      raise(Sequel::Error::InvalidValue, "invalid value for Datetime: #{value.inspect}") unless [DateTime, Date, Time, String].any?{|c| value.is_a?(c)}
      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.
        Sequel.string_to_datetime(Time === value ? value.iso8601 : value.to_s)
      end
    when :blob
      ::Sequel::SQL::Blob.new(value)
    else
      value
    end
  rescue ArgumentError => exp
    e = Sequel::Error::InvalidValue.new("#{exp.class} #{exp.message}")
    e.set_backtrace(exp.backtrace)
    raise e
  end
end

#upcase_identifiers=(v) ⇒ Object 



65
66
67
68
 
# File 'lib/sequel/deprecated.rb', line 65

def upcase_identifiers=(v)
  Deprecation.deprecate('Sequel::Database#upcase_identifiers=', 'Use Sequel::Database#identifier_input_method = :upcase or nil')
  self.identifier_input_method = v ? :upcase : nil
end

#upcase_identifiers? ⇒ Boolean

Returns:

  • (Boolean) 


70
71
72
73
 
# File 'lib/sequel/deprecated.rb', line 70

def upcase_identifiers?
  Deprecation.deprecate('Sequel::Database#upcase_identifiers?', 'Use Sequel::Database#identifier_input_method == :upcase')
  identifier_input_method == :upcase
end

#uri ⇒ Object

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



558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
 
# File 'lib/sequel/database.rb', line 558

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

#url ⇒ Object

Explicit alias of uri for easier subclassing.



576
577
578
 
# File 'lib/sequel/database.rb', line 576

def url
  uri
end