- add_limit!
- add_limit_offset!
- add_lock!
- begin_db_transaction
- case_sensitive_equality_operator
- commit_db_transaction
- default_sequence_name
- delete
- delete_sql
- empty_insert_statement
- execute
- insert
- insert_fixture
- insert_sql
- limited_update_conditions
- outside_transaction?
- reset_sequence!
- rollback_db_transaction
- sanitize_limit
- select
- select_all
- select_one
- select_rows
- select_value
- select_values
- transaction
- update
- update_sql
Alias for add_limit_offset!.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 185 185: def add_limit!(sql, options) 186: add_limit_offset!(sql, options) if options 187: end
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 (required) and an +:offset+ option (optional).
This method modifies the sql parameter.
Examples
add_limit_offset!('SELECT * FROM suppliers', {:limit => 10, :offset => 50})
generates
SELECT * FROM suppliers LIMIT 10 OFFSET 50
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 201 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}" 206: end 207: end 208: sql 209: end
Appends a locking clause to an SQL statement. This method modifies the sql parameter.
# SELECT * FROM suppliers FOR UPDATE add_lock! 'SELECT * FROM suppliers', :lock => true add_lock! 'SELECT * FROM suppliers', :lock => ' FOR UPDATE'
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 216 216: def add_lock!(sql, options) 217: case lock = options[:lock] 218: when true; sql << ' FOR UPDATE' 219: when String; sql << " #{lock}" 220: end 221: end
Begins the transaction (and turns off auto-committing).
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 175 175: def begin_db_transaction() end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 242 242: def case_sensitive_equality_operator 243: "=" 244: end
Commits the transaction (and turns on auto-committing).
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 178 178: def commit_db_transaction() end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 223 223: def default_sequence_name(table, column) 224: nil 225: end
Executes the delete statement and returns the number of rows affected.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 53 53: def delete(sql, name = nil) 54: delete_sql(sql, name) 55: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 238 238: def empty_insert_statement(table_name) 239: "INSERT INTO #{quote_table_name(table_name)} VALUES(DEFAULT)" 240: end
Executes the SQL statement in the context of this connection.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 38 38: def execute(sql, name = nil, skip_logging = false) 39: end
Returns the last auto-generated ID from the affected table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 43 43: def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 44: insert_sql(sql, name, pk, id_value, sequence_name) 45: end
Inserts the given fixture into the table. Overridden in adapters that require something beyond a simple insert (eg. Oracle).
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 234 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' 236: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 246 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})" 248: end
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.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 67 67: def outside_transaction? 68: nil 69: end
Set the sequence to the max value of the table‘s column.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 228 228: def reset_sequence!(table, column, sequence = nil) 229: # Do nothing by default. Implement for PostgreSQL, Oracle, ... 230: end
Rolls back the transaction (and turns on auto-committing). Must be done if the transaction block raises an exception or returns false.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 182 182: def rollback_db_transaction() end
Returns an array of record hashes with the column names as keys and column values as values.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 6 6: def select_all(sql, name = nil) 7: select(sql, name) 8: end
Returns a record hash with the column names as keys and column values as values.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 12 12: def select_one(sql, name = nil) 13: result = select_all(sql, name) 14: result.first if result 15: end
Returns an array of arrays containing the field values. Order is the same as that returned by columns.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 33 33: def select_rows(sql, name = nil) 34: end
Returns a single value from a record
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 18 18: def select_value(sql, name = nil) 19: if result = select_one(sql, name) 20: result.values.first 21: end 22: end
Returns an array of the values of the first column in a select:
select_values("SELECT id FROM companies LIMIT 3") => [1,2,3]
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 26 26: def select_values(sql, name = nil) 27: result = select_rows(sql, name) 28: result.map { |v| v[0] } 29: end
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/savepoints.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
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 113 113: def transaction(options = {}) 114: options.assert_valid_keys :requires_new, :joinable 115: 116: last_transaction_joinable = @transaction_joinable 117: if options.has_key?(:joinable) 118: @transaction_joinable = options[:joinable] 119: else 120: @transaction_joinable = true 121: end 122: requires_new = options[:requires_new] || !last_transaction_joinable 123: 124: transaction_open = false 125: begin 126: if block_given? 127: if requires_new || open_transactions == 0 128: if open_transactions == 0 129: begin_db_transaction 130: elsif requires_new 131: create_savepoint 132: end 133: increment_open_transactions 134: transaction_open = true 135: end 136: yield 137: end 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 144: else 145: rollback_to_savepoint 146: end 147: end 148: raise unless database_transaction_rollback.is_a?(ActiveRecord::Rollback) 149: end 150: ensure 151: @transaction_joinable = last_transaction_joinable 152: 153: if outside_transaction? 154: @open_transactions = 0 155: elsif transaction_open 156: decrement_open_transactions 157: begin 158: if open_transactions == 0 159: commit_db_transaction 160: else 161: release_savepoint 162: end 163: rescue Exception => database_transaction_rollback 164: if open_transactions == 0 165: rollback_db_transaction 166: else 167: rollback_to_savepoint 168: end 169: raise 170: end 171: end 172: end
Executes the update statement and returns the number of rows affected.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 48 48: def update(sql, name = nil) 49: update_sql(sql, name) 50: end
Executes the delete statement and returns the number of rows affected.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 269 269: def delete_sql(sql, name = nil) 270: update_sql(sql, name) 271: end
Returns the last auto-generated ID from the affected table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 258 258: def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 259: execute(sql, name) 260: id_value 261: end
Sanitizes the given LIMIT parameter in order to prevent SQL injection.
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.
Returns the sanitized limit parameter, either as an integer, or as a string which contains a comma-delimited list of integers.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 280 280: def sanitize_limit(limit) 281: if limit.to_s =~ /,/ 282: limit.to_s.split(',').map{ |i| i.to_i }.join(',') 283: else 284: limit.to_i 285: end 286: end
Returns an array of record hashes with the column names as keys and column values as values.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 253 253: def select(sql, name = nil) 254: end
Executes the update statement and returns the number of rows affected.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb, line 264 264: def update_sql(sql, name = nil) 265: execute(sql, name) 266: end