Module: ActiveRecord::ConnectionAdapters::DatabaseStatements
- Included in:
- AbstractAdapter
- Defined in:
- lib/active_record/connection_adapters/abstract/database_statements.rb
Instance Method Summary collapse
-
#add_limit_offset!(sql, options) ⇒ Object
Appends
LIMIT
andOFFSET
options to an SQL statement, or some SQL fragment that has the same semantics as LIMIT and OFFSET. -
#add_transaction_record(record) ⇒ Object
Register a record with the current transaction so that its after_commit and after_rollback callbacks can be called.
-
#begin_db_transaction ⇒ Object
Begins the transaction (and turns off auto-committing).
- #case_sensitive_equality_operator ⇒ Object
-
#commit_db_transaction ⇒ Object
Commits the transaction (and turns on auto-committing).
- #default_sequence_name(table, column) ⇒ Object
-
#delete(arel, name = nil, binds = []) ⇒ Object
Executes the delete statement and returns the number of rows affected.
- #empty_insert_statement_value ⇒ Object
-
#exec_delete(sql, name, binds) ⇒ Object
Executes delete
sql
statement in the context of this connection usingbinds
as the bind substitutes. -
#exec_insert(sql, name, binds) ⇒ Object
Executes insert
sql
statement in the context of this connection usingbinds
as the bind substitutes. -
#exec_query(sql, name = 'SQL', binds = []) ⇒ Object
Executes
sql
statement in the context of this connection usingbinds
as the bind substitutes. -
#exec_update(sql, name, binds) ⇒ Object
Executes update
sql
statement in the context of this connection usingbinds
as the bind substitutes. -
#execute(sql, name = nil) ⇒ Object
Executes the SQL statement in the context of this connection.
-
#insert(arel, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) ⇒ Object
Returns the last auto-generated ID from the affected table.
-
#insert_fixture(fixture, table_name) ⇒ Object
Inserts the given fixture into the table.
-
#join_to_update(update, select) ⇒ Object
The default strategy for an UPDATE with joins is to use a subquery.
- #limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key) ⇒ Object
-
#outside_transaction? ⇒ Boolean
Checks whether there is currently no transaction active.
-
#reset_sequence!(table, column, sequence = nil) ⇒ Object
Set the sequence to the max value of the table’s column.
-
#rollback_db_transaction ⇒ Object
Rolls back the transaction (and turns on auto-committing).
-
#sanitize_limit(limit) ⇒ Object
Sanitizes the given LIMIT parameter in order to prevent SQL injection.
-
#select_all(arel, name = nil, binds = []) ⇒ Object
Returns an array of record hashes with the column names as keys and column values as values.
-
#select_one(arel, name = nil) ⇒ Object
Returns a record hash with the column names as keys and column values as values.
-
#select_rows(sql, name = nil) ⇒ Object
Returns an array of arrays containing the field values.
-
#select_value(arel, name = nil) ⇒ Object
Returns a single value from a record.
-
#select_values(arel, name = nil) ⇒ Object
Returns an array of the values of the first column in a select: select_values(“SELECT id FROM companies LIMIT 3”) => [1,2,3].
-
#supports_statement_cache? ⇒ Boolean
Returns
true
when the connection adapter supports prepared statement caching, otherwise returnsfalse
. -
#to_sql(arel, binds = []) ⇒ Object
Converts an arel AST to SQL.
-
#transaction(options = {}) ⇒ Object
Runs the given block in a database transaction, and returns the result of the block.
-
#update(arel, name = nil, binds = []) ⇒ Object
Executes the update statement and returns the number of rows affected.
Instance Method Details
#add_limit_offset!(sql, options) ⇒ Object
Appends LIMIT
and OFFSET
options to an SQL statement, or some SQL fragment that has the same semantics as LIMIT and OFFSET.
options
must be a Hash which contains a :limit
option and an :offset
option.
This method modifies the sql
parameter.
This method is deprecated!! Stop using it!
Examples
add_limit_offset!('SELECT * FROM suppliers', {:limit => 10, :offset => 50})
generates
SELECT * FROM suppliers LIMIT 10 OFFSET 50
273 274 275 276 277 278 279 280 281 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 273 def add_limit_offset!(sql, ) if limit = [:limit] sql << " LIMIT #{sanitize_limit(limit)}" end if offset = [:offset] sql << " OFFSET #{offset.to_i}" end sql end |
#add_transaction_record(record) ⇒ Object
Register a record with the current transaction so that its after_commit and after_rollback callbacks can be called.
244 245 246 247 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 244 def add_transaction_record(record) last_batch = @_current_transaction_records.last last_batch << record if last_batch end |
#begin_db_transaction ⇒ Object
Begins the transaction (and turns off auto-committing).
250 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 250 def begin_db_transaction() end |
#case_sensitive_equality_operator ⇒ Object
311 312 313 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 311 def case_sensitive_equality_operator "=" end |
#commit_db_transaction ⇒ Object
Commits the transaction (and turns on auto-committing).
253 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 253 def commit_db_transaction() end |
#default_sequence_name(table, column) ⇒ Object
284 285 286 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 284 def default_sequence_name(table, column) nil end |
#delete(arel, name = nil, binds = []) ⇒ Object
Executes the delete statement and returns the number of rows affected.
102 103 104 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 102 def delete(arel, name = nil, binds = []) exec_delete(to_sql(arel, binds), name, binds) end |
#empty_insert_statement_value ⇒ Object
307 308 309 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 307 def empty_insert_statement_value "VALUES(DEFAULT)" end |
#exec_delete(sql, name, binds) ⇒ Object
Executes delete sql
statement in the context of this connection using binds
as the bind substitutes. name
is the logged along with the executed sql
statement.
71 72 73 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 71 def exec_delete(sql, name, binds) exec_query(sql, name, binds) end |
#exec_insert(sql, name, binds) ⇒ Object
Executes insert sql
statement in the context of this connection using binds
as the bind substitutes. name
is the logged along with the executed sql
statement.
64 65 66 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 64 def exec_insert(sql, name, binds) exec_query(sql, name, binds) end |
#exec_query(sql, name = 'SQL', binds = []) ⇒ Object
Executes sql
statement in the context of this connection using binds
as the bind substitutes. name
is logged along with the executed sql
statement.
58 59 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 58 def exec_query(sql, name = 'SQL', binds = []) end |
#exec_update(sql, name, binds) ⇒ Object
Executes update sql
statement in the context of this connection using binds
as the bind substitutes. name
is the logged along with the executed sql
statement.
78 79 80 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 78 def exec_update(sql, name, binds) exec_query(sql, name, binds) end |
#execute(sql, name = nil) ⇒ Object
Executes the SQL statement in the context of this connection.
51 52 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 51 def execute(sql, name = nil) end |
#insert(arel, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) ⇒ Object
Returns the last auto-generated ID from the affected table.
id_value
will be returned unless the value is nil, in which case the database will attempt to calculate the last inserted id and return that value.
If the next id was calculated in advance (as in Oracle), it should be passed in as id_value
.
90 91 92 93 94 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 90 def insert(arel, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) sql, binds = sql_for_insert(to_sql(arel, binds), pk, id_value, sequence_name, binds) value = exec_insert(sql, name, binds) id_value || last_inserted_id(value) end |
#insert_fixture(fixture, table_name) ⇒ Object
Inserts the given fixture into the table. Overridden in adapters that require something beyond a simple insert (eg. Oracle).
295 296 297 298 299 300 301 302 303 304 305 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 295 def insert_fixture(fixture, table_name) columns = Hash[columns(table_name).map { |c| [c.name, c] }] key_list = [] value_list = fixture.map do |name, value| key_list << quote_column_name(name) quote(value, columns[name]) end execute "INSERT INTO #{quote_table_name(table_name)} (#{key_list.join(', ')}) VALUES (#{value_list.join(', ')})", 'Fixture Insert' end |
#join_to_update(update, select) ⇒ Object
The default strategy for an UPDATE with joins is to use a subquery. This doesn’t work on mysql (even when aliasing the tables), but mysql allows using JOIN directly in an UPDATE statement, so in the mysql adapters we redefine this to do that.
341 342 343 344 345 346 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 341 def join_to_update(update, select) #:nodoc: subselect = select.clone subselect.projections = [update.key] update.where update.key.in(subselect) end |
#limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key) ⇒ Object
315 316 317 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 315 def limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key) "WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{quoted_table_name} #{where_sql})" end |
#outside_transaction? ⇒ Boolean
Checks whether there is currently no transaction active. This is done by querying the database driver, and does not use the transaction house-keeping information recorded by #increment_open_transactions and friends.
Returns true if there is no transaction active, false if there is a transaction active, and nil if this information is unknown.
Not all adapters supports transaction state introspection. Currently, only the PostgreSQL adapter supports this.
116 117 118 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 116 def outside_transaction? nil end |
#reset_sequence!(table, column, sequence = nil) ⇒ Object
Set the sequence to the max value of the table’s column.
289 290 291 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 289 def reset_sequence!(table, column, sequence = nil) # Do nothing by default. Implement for PostgreSQL, Oracle, ... end |
#rollback_db_transaction ⇒ Object
Rolls back the transaction (and turns on auto-committing). Must be done if the transaction block raises an exception or returns false.
257 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 257 def rollback_db_transaction() end |
#sanitize_limit(limit) ⇒ Object
Sanitizes the given LIMIT parameter in order to prevent SQL injection.
The limit
may be anything that can evaluate to a string via #to_s. It should look like an integer, or a comma-delimited list of integers, or an Arel SQL literal.
Returns Integer and Arel::Nodes::SqlLiteral limits as is. Returns the sanitized limit parameter, either as an integer, or as a string which contains a comma-delimited list of integers.
328 329 330 331 332 333 334 335 336 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 328 def sanitize_limit(limit) if limit.is_a?(Integer) || limit.is_a?(Arel::Nodes::SqlLiteral) limit elsif limit.to_s =~ /,/ Arel.sql limit.to_s.split(',').map{ |i| Integer(i) }.join(',') else Integer(limit) end end |
#select_all(arel, name = nil, binds = []) ⇒ Object
Returns an array of record hashes with the column names as keys and column values as values.
19 20 21 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 19 def select_all(arel, name = nil, binds = []) select(to_sql(arel, binds), name, binds) end |
#select_one(arel, name = nil) ⇒ Object
Returns a record hash with the column names as keys and column values as values.
25 26 27 28 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 25 def select_one(arel, name = nil) result = select_all(arel, name) result.first if result end |
#select_rows(sql, name = nil) ⇒ Object
Returns an array of arrays containing the field values. Order is the same as that returned by columns
.
46 47 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 46 def select_rows(sql, name = nil) end |
#select_value(arel, name = nil) ⇒ Object
Returns a single value from a record
31 32 33 34 35 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 31 def select_value(arel, name = nil) if result = select_one(arel, name) result.values.first end end |
#select_values(arel, name = nil) ⇒ Object
Returns an array of the values of the first column in a select:
select_values("SELECT id FROM companies LIMIT 3") => [1,2,3]
39 40 41 42 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 39 def select_values(arel, name = nil) result = select_rows(to_sql(arel, []), name) result.map { |v| v[0] } end |
#supports_statement_cache? ⇒ Boolean
Returns true
when the connection adapter supports prepared statement caching, otherwise returns false
122 123 124 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 122 def supports_statement_cache? false end |
#to_sql(arel, binds = []) ⇒ Object
Converts an arel AST to SQL
7 8 9 10 11 12 13 14 15 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 7 def to_sql(arel, binds = []) if arel.respond_to?(:ast) visitor.accept(arel.ast) do quote(*binds.shift.reverse) end else arel end end |
#transaction(options = {}) ⇒ Object
Runs the given block in a database transaction, and returns the result of the block.
Nested transactions support
Most databases don’t support true nested transactions. At the time of writing, the only database that supports true nested transactions that we’re aware of, is MS-SQL.
In order to get around this problem, #transaction will emulate the effect of nested transactions, by using savepoints: dev.mysql.com/doc/refman/5.0/en/savepoint.html Savepoints are supported by MySQL and PostgreSQL, but not SQLite3.
It is safe to call this method if a database transaction is already open, i.e. if #transaction is called within another #transaction block. In case of a nested call, #transaction will behave as follows:
-
The block will be run without doing anything. All database statements that happen within the block are effectively appended to the already open database transaction.
-
However, if
:requires_new
is set, the block will be wrapped in a database savepoint acting as a sub-transaction.
Caveats
MySQL doesn’t support DDL transactions. If you perform a DDL operation, then any created savepoints will be automatically released. For example, if you’ve created a savepoint, then you execute a CREATE TABLE statement, then the savepoint that was created will be automatically released.
This means that, on MySQL, you shouldn’t execute DDL operations inside a #transaction call that you know might create a savepoint. Otherwise, #transaction will raise exceptions when it tries to release the already-automatically-released savepoints:
Model.connection.transaction do # BEGIN
Model.connection.transaction(:requires_new => true) do # CREATE SAVEPOINT active_record_1
Model.connection.create_table(...)
# active_record_1 now automatically released
end # RELEASE SAVEPOINT active_record_1 <--- BOOM! database error!
end
168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 168 def transaction( = {}) .assert_valid_keys :requires_new, :joinable last_transaction_joinable = defined?(@transaction_joinable) ? @transaction_joinable : nil if .has_key?(:joinable) @transaction_joinable = [:joinable] else @transaction_joinable = true end requires_new = [:requires_new] || !last_transaction_joinable transaction_open = false @_current_transaction_records ||= [] begin if block_given? if requires_new || open_transactions == 0 if open_transactions == 0 begin_db_transaction elsif requires_new create_savepoint end increment_open_transactions transaction_open = true @_current_transaction_records.push([]) end yield end rescue Exception => database_transaction_rollback if transaction_open && !outside_transaction? transaction_open = false decrement_open_transactions if open_transactions == 0 rollback_db_transaction rollback_transaction_records(true) else rollback_to_savepoint rollback_transaction_records(false) end end raise unless database_transaction_rollback.is_a?(ActiveRecord::Rollback) end ensure @transaction_joinable = last_transaction_joinable if outside_transaction? @open_transactions = 0 elsif transaction_open decrement_open_transactions begin if open_transactions == 0 commit_db_transaction commit_transaction_records else release_savepoint save_point_records = @_current_transaction_records.pop unless save_point_records.blank? @_current_transaction_records.push([]) if @_current_transaction_records.empty? @_current_transaction_records.last.concat(save_point_records) end end rescue Exception => database_transaction_rollback if open_transactions == 0 rollback_db_transaction rollback_transaction_records(true) else rollback_to_savepoint rollback_transaction_records(false) end raise end end end |
#update(arel, name = nil, binds = []) ⇒ Object
Executes the update statement and returns the number of rows affected.
97 98 99 |
# File 'lib/active_record/connection_adapters/abstract/database_statements.rb', line 97 def update(arel, name = nil, binds = []) exec_update(to_sql(arel, binds), name, binds) end |