2
module ConnectionAdapters # :nodoc:
3
module DatabaseStatements
4
# Returns an array of record hashes with the column names as keys and
5
# column values as values.
6
def select_all(sql, name = nil)
10
# Returns a record hash with the column names as keys and column values
12
def select_one(sql, name = nil)
13
result = select_all(sql, name)
14
result.first if result
17
# Returns a single value from a record
18
def select_value(sql, name = nil)
19
if result = select_one(sql, name)
24
# Returns an array of the values of the first column in a select:
25
# select_values("SELECT id FROM companies LIMIT 3") => [1,2,3]
26
def select_values(sql, name = nil)
27
result = select_rows(sql, name)
28
result.map { |v| v[0] }
31
# Returns an array of arrays containing the field values.
32
# Order is the same as that returned by +columns+.
33
def select_rows(sql, name = nil)
35
undef_method :select_rows
37
# Executes the SQL statement in the context of this connection.
38
def execute(sql, name = nil, skip_logging = false)
42
# Returns the last auto-generated ID from the affected table.
43
def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
44
insert_sql(sql, name, pk, id_value, sequence_name)
47
# Executes the update statement and returns the number of rows affected.
48
def update(sql, name = nil)
52
# Executes the delete statement and returns the number of rows affected.
53
def delete(sql, name = nil)
57
# Checks whether there is currently no transaction active. This is done
58
# by querying the database driver, and does not use the transaction
59
# house-keeping information recorded by #increment_open_transactions and
62
# Returns true if there is no transaction active, false if there is a
63
# transaction active, and nil if this information is unknown.
65
# Not all adapters supports transaction state introspection. Currently,
66
# only the PostgreSQL adapter supports this.
67
def outside_transaction?
71
# Runs the given block in a database transaction, and returns the result
74
# == Nested transactions support
76
# Most databases don't support true nested transactions. At the time of
77
# writing, the only database that supports true nested transactions that
78
# we're aware of, is MS-SQL.
80
# In order to get around this problem, #transaction will emulate the effect
81
# of nested transactions, by using savepoints:
82
# http://dev.mysql.com/doc/refman/5.0/en/savepoints.html
83
# Savepoints are supported by MySQL and PostgreSQL, but not SQLite3.
85
# It is safe to call this method if a database transaction is already open,
86
# i.e. if #transaction is called within another #transaction block. In case
87
# of a nested call, #transaction will behave as follows:
89
# - The block will be run without doing anything. All database statements
90
# that happen within the block are effectively appended to the already
91
# open database transaction.
92
# - However, if +:requires_new+ is set, the block will be wrapped in a
93
# database savepoint acting as a sub-transaction.
97
# MySQL doesn't support DDL transactions. If you perform a DDL operation,
98
# then any created savepoints will be automatically released. For example,
99
# if you've created a savepoint, then you execute a CREATE TABLE statement,
100
# then the savepoint that was created will be automatically released.
102
# This means that, on MySQL, you shouldn't execute DDL operations inside
103
# a #transaction call that you know might create a savepoint. Otherwise,
104
# #transaction will raise exceptions when it tries to release the
105
# already-automatically-released savepoints:
107
# Model.connection.transaction do # BEGIN
108
# Model.connection.transaction(:requires_new => true) do # CREATE SAVEPOINT active_record_1
109
# Model.connection.create_table(...)
110
# # active_record_1 now automatically released
111
# end # RELEASE SAVEPOINT active_record_1 <--- BOOM! database error!
113
def transaction(options = {})
114
options.assert_valid_keys :requires_new, :joinable
116
last_transaction_joinable = @transaction_joinable
117
if options.has_key?(:joinable)
118
@transaction_joinable = options[:joinable]
120
@transaction_joinable = true
122
requires_new = options[:requires_new] || !last_transaction_joinable
124
transaction_open = false
127
if requires_new || open_transactions == 0
128
if open_transactions == 0
133
increment_open_transactions
134
transaction_open = true
138
rescue Exception => database_transaction_rollback
139
if transaction_open && !outside_transaction?
140
transaction_open = false
141
decrement_open_transactions
142
if open_transactions == 0
143
rollback_db_transaction
145
rollback_to_savepoint
148
raise unless database_transaction_rollback.is_a?(ActiveRecord::Rollback)
151
@transaction_joinable = last_transaction_joinable
153
if outside_transaction?
154
@open_transactions = 0
155
elsif transaction_open
156
decrement_open_transactions
158
if open_transactions == 0
159
commit_db_transaction
163
rescue Exception => database_transaction_rollback
164
if open_transactions == 0
165
rollback_db_transaction
167
rollback_to_savepoint
174
# Begins the transaction (and turns off auto-committing).
175
def begin_db_transaction() end
177
# Commits the transaction (and turns on auto-committing).
178
def commit_db_transaction() end
180
# Rolls back the transaction (and turns on auto-committing). Must be
181
# done if the transaction block raises an exception or returns false.
182
def rollback_db_transaction() end
184
# Alias for <tt>add_limit_offset!</tt>.
185
def add_limit!(sql, options)
186
add_limit_offset!(sql, options) if options
189
# Appends +LIMIT+ and +OFFSET+ options to an SQL statement, or some SQL
190
# fragment that has the same semantics as LIMIT and OFFSET.
192
# +options+ must be a Hash which contains a +:limit+ option (required)
193
# and an +:offset+ option (optional).
195
# This method *modifies* the +sql+ parameter.
198
# add_limit_offset!('SELECT * FROM suppliers', {:limit => 10, :offset => 50})
200
# SELECT * FROM suppliers LIMIT 10 OFFSET 50
201
def add_limit_offset!(sql, options)
202
if limit = options[:limit]
203
sql << " LIMIT #{sanitize_limit(limit)}"
204
if offset = options[:offset]
205
sql << " OFFSET #{offset.to_i}"
211
# Appends a locking clause to an SQL statement.
212
# This method *modifies* the +sql+ parameter.
213
# # SELECT * FROM suppliers FOR UPDATE
214
# add_lock! 'SELECT * FROM suppliers', :lock => true
215
# add_lock! 'SELECT * FROM suppliers', :lock => ' FOR UPDATE'
216
def add_lock!(sql, options)
217
case lock = options[:lock]
218
when true; sql << ' FOR UPDATE'
219
when String; sql << " #{lock}"
223
def default_sequence_name(table, column)
227
# Set the sequence to the max value of the table's column.
228
def reset_sequence!(table, column, sequence = nil)
229
# Do nothing by default. Implement for PostgreSQL, Oracle, ...
232
# Inserts the given fixture into the table. Overridden in adapters that require
233
# something beyond a simple insert (eg. Oracle).
234
def insert_fixture(fixture, table_name)
235
execute "INSERT INTO #{quote_table_name(table_name)} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
238
def empty_insert_statement(table_name)
239
"INSERT INTO #{quote_table_name(table_name)} VALUES(DEFAULT)"
242
def case_sensitive_equality_operator
246
def limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key)
247
"WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{quoted_table_name} #{where_sql})"
251
# Returns an array of record hashes with the column names as keys and
252
# column values as values.
253
def select(sql, name = nil)
257
# Returns the last auto-generated ID from the affected table.
258
def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
263
# Executes the update statement and returns the number of rows affected.
264
def update_sql(sql, name = nil)
268
# Executes the delete statement and returns the number of rows affected.
269
def delete_sql(sql, name = nil)
270
update_sql(sql, name)
273
# Sanitizes the given LIMIT parameter in order to prevent SQL injection.
275
# +limit+ may be anything that can evaluate to a string via #to_s. It
276
# should look like an integer, or a comma-delimited list of integers.
278
# Returns the sanitized limit parameter, either as an integer, or as a
279
# string which contains a comma-delimited list of integers.
280
def sanitize_limit(limit)
282
limit.to_s.split(',').map{ |i| i.to_i }.join(',')