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