ensembl-hive
2.6
|
Public Member Functions | |
public Instance | new () |
public Bio::EnsEMBL::DBSQL::DBConnection | db_connection () |
public ArrayRef | execute () |
public ArrayRef | execute_simple () |
public void | execute_no_return () |
public HashRef | execute_into_hash () |
public Scalar | execute_single_result () |
public Return | transaction () |
public Int | execute_update () |
public Anything | execute_with_sth () |
public Int | batch () |
protected | _mappers () |
protected | _perform_transaction_code () |
protected | _enable_transaction () |
protected | _disable_transaction () |
protected | _bind_params () |
protected | _execute () |
protected | _base_execute () |
protected | _finish_sth () |
protected | _callback_batch () |
protected | _data_batch () |
Easier database interaction
Definition at line 55 of file SqlHelper.pm.
protected Bio::EnsEMBL::Utils::SqlHelper::_base_execute | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_bind_params | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_callback_batch | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_data_batch | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_disable_transaction | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_enable_transaction | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_execute | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_finish_sth | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_mappers | ( | ) |
Undocumented method
protected Bio::EnsEMBL::Utils::SqlHelper::_perform_transaction_code | ( | ) |
Undocumented method
public Int Bio::EnsEMBL::Utils::SqlHelper::batch | ( | ) |
Arg [SQL] : string $sql Arg [CALLBACK] : CodeRef The callback to use for working with the statement handle once returned; specify this or -DATA Arg [DATA] : ArrayRef The data to insert; specify this or -CALLBACK Arg [COMMIT_EVERY] : Integer defines the rate at which to issue commits to the DB handle. This is important when working with InnoDB databases since it affects the speed of rollback (larger gaps inbetween commits means more to rollback).
Ignored if using the callback version. Arg [PREPARE_PARAMS] : ArrayRef Used to pass parameters to the statement handle prepare method Returntype : integer rows updated Exceptions : If errors occur in the execution of the SQL Status : Stable Example :
Description: Takes in a sql statement and a code reference. Your SQL is converted into a prepared statement and then given as the first parameter to the closure. The second parameter is the DBH which created the statement. This is intended to let you do mass insertion into a database without the need to re-preparing the same statement.
This can be combined with the transaction() code to provide a construct which does batch insertion and is transactionally aware.
We can also use data based batch insertions i.e.
#Needs to be like: # [ [1,2], [3,4] ] #Or if using the DBI types: # [ [ [ 1, SQL_INTEGER ], [ 2, SQL_INTEGER ] ], # [ [ 3, SQL_INTEGER ], [ 4, SQL_INTEGER ] ] ];
my $alotofdata = getitfromsomewhere(); $helper->batch( -SQL => 'insert into table (one,two) values(?,?)', -DATA => $alotofdata );
This does exactly the same as the previous example.
All batch statements will return the value the callback computes. If you are using the previous example with a data array then the code will return the number affected rows by the query.
public Bio::EnsEMBL::DBSQL::DBConnection Bio::EnsEMBL::Utils::SqlHelper::db_connection | ( | ) |
Arg [1] : Bio::EnsEMBL::DBSQL::DBConnection $db_connection Description : Sets and retrieves the DBConnection Returntype : Bio::EnsEMBL::DBSQL::DBConnection Exceptions : If the object given as a DBConnection is not one or if an attempt is made to set the value more than once Status : Stable
public ArrayRef Bio::EnsEMBL::Utils::SqlHelper::execute | ( | ) |
Arg [SQL] : string SQL to execute Arg [CALLBACK] : CodeRef; The callback to use for mapping a row to a data point; leave blank for a default mapping to a 2D array Arg [USE_HASHREFS] : boolean If set to true will cause HashRefs to be returned to the callback & not ArrayRefs Arg [PARAMS] : ArrayRef The binding parameters to the SQL statement Arg [PREPARE_PARAMS] : boolean Parameters to be passed onto the Statement Handle prepare call Arg [ITERATOR] : boolean Request a Bio::EnsEMBL::Utils::Iterator rather than a 2D array Returntype : ArrayRef or Bio::EnsEMBL::Utils::Iterator Exceptions : If errors occur in the execution of the SQL Status : Stable Example :
Description: Uses a callback defined by the sub decalaration. Here we specify how the calling code will deal with each row of a database's result set. The sub can return any type of Object/hash/data structure you require.
Should you not specify a callback then a basic one will be assigned to you which will return a 2D array structure e.g.
my $arr_ref = $helper->execute( -SQL => 'select a,b,c from tab where col =?', -PARAMS => ['A'] );
This is equivalent to DBI's selectall_arrayref() subroutine.
As an extension to this method you can write a closure subroutine which takes in two parameters. The first is the array/hash reference & the second is the statement handle used to execute. 99% of the time you will not need it but there are occasions where you do need it. An example of usage would be:
my $conn = get_conn(); #From somwewhere my $arr_ref = $conn->execute( -SQL => 'select a,b,c from tab where col =?', -USE_HASHREFS => 1, -CALLBACK => sub { my ( $row, $sth ) = @_; #Then do something with sth return { A => $row->[0], B => $row->[1], C => $row->[2] }; }, -PARAMS => ['A'] );
Any arguments to bind to the incoming statement. This can be a set of scalars or a 2D array if you need to specify any kind of types of sql objects i.e.
use DBI qw(:sql_types);
my $conn = get_conn(); my $arr_ref = $conn->execute( -SQL => 'select a,b,c from tab where col =? and num_col=? and other=?', -USE_HASHREFS => 1, -CALLBACK => sub { my @row = shift @_ }; return { A => $row[0], B => $row[1], C => $row[2] }; }, -PARAMS => [ '1', SQL_VARCHAR ], [ 2, SQL_INTEGER ], 'hello' );
Here we import DBI's sql types into our package and then pass in multiple anonymous array references as parameters. Each param is tested in the input and if it is detected to be an ARRAY reference we dereference the array and run DBI's bind_param method. In fact you can see each part of the incoming paramaters array as the contents to call bind_param with. The only difference is the package tracks the bind position for you.
We can get back a Bio::EnsEMBL::Utils::Iterator object which can be used to iterate over the results set without first materializing the data into memory. An example would be:
my $iterator = $helper->execute( -SQL => 'select a,b,c from tab where col =?', -PARAMS => ['A'] -ITERATOR => 1); while($iterator->has_next()) { my $row = $iterator->next(); #Do something }
This is very useful for very large datasets.
public HashRef Bio::EnsEMBL::Utils::SqlHelper::execute_into_hash | ( | ) |
Arg [SQL] : string $sql Arg [CALLBACK] : CodeRef The callback to use for mapping to a value in a hash keyed by the first element in your result set; leave blank for a default mapping to a scalar value of the second element Arg [PARAMS] : The binding parameters to the SQL statement Returntype : HashRef keyed by column 1 & value is the return of callback Exceptions : If errors occur in the execution of the SQL Status : Stable Description: A variant of the execute methods but rather than returning a list of mapped results, this will assume the first column of a returning map and the calling subroutine will map the remainder of your return as the hash's key.
This code can handle simple queries to hashes, complex value mappings and repeated mappings for the same key.
Example:
my $sql = 'select key, one, two from table where something =?'; my $mapper = sub { # Argument one is a row from the result, argument two is any previously seen value for the first column of the row my ( $row, $value ) = @_; #Ignore field 0 as that is being used for the key my $obj = Some::Obj->new( one => $row->[1], two => $row->[2] ); return $obj; };
my $hash = $helper->execute_into_hash( -SQL => $sql, -CALLBACK => $mapper, -PARAMS => ['val'] );
#Or the default simplistic invocation my $sql = 'select biotype, count(gene_id) from gene group by biotype'; my $biotype_hash = $conn->execute_into_hash( -SQL => $sql ); print $biotype_hash->{protein_coding} || 0, "\n";
# More complicated mapping, result hash will be keyed on "meta_key" # Hash will contain lists of values linked to their meta_key my args = ( -SQL => 'select meta_key, meta_value from meta ' . 'where meta_key =? order by meta_id', -PARAMS => ['species.classification'] );
my $hash = $helper->execute_into_hash( args, -CALLBACK => sub { my ( $row, $value ) = @_; $value = [] if !defined $value; push($value}, $row->[1] ); return $value; } );
# Add new values to an already seen existing meta_key $hash = $helper->execute_into_hash( args, -CALLBACK => sub { my ( $row, $value ) = @_; if ( defined $value ) { # Calling code is dealing with $row->[0], so we only have to handle the remaining columns push($value}, $row->[1] ); return; } my $new_value = [ $row->[1] ]; return $new_value; } );
public void Bio::EnsEMBL::Utils::SqlHelper::execute_no_return | ( | ) |
Arg [SQL] : string sql Arg [CALLBACK] : CodeRef The callback to use for mapping a row to a data point; we assume you are assigning into a data structure which has requirements other than simple translation into an array Arg [USE_HASHREFS] : boolean If set to true will cause HashRefs to be returned to the callback and not ArrayRefs Arg [PARAMS] : ArrayRef The binding parameters to the SQL statement Returntype : None Exceptions : If errors occur in the execution of the SQL Status : Stable Description: Whilst all other execute methods will return something; this assumes that the given mapper subroutine will be performing the business of placing values somewhere or doing something with them.
There is a huge temptation to nest queries using this method; do not! Execute the values into an array using one of the other methods then run your subqueries on them; or make a better first query. SQL is flexible; so use it.
public ArrayRef Bio::EnsEMBL::Utils::SqlHelper::execute_simple | ( | ) |
Arg [SQL] : string $sql Arg [PARAMS] : ArrayRef $params Arg [CALLBACK] : CodeRef $callback Returntype : ArrayRef of 1D elements Exceptions : If errors occur in the execution of the SQL Status : Stable Example :
Description: Similar to execute() but without a sub-routine reference. Using this code assumes you want an array of single scalar values as returned by the given SQL statement.
public Scalar Bio::EnsEMBL::Utils::SqlHelper::execute_single_result | ( | ) |
Arg [SQL] : string $sql Arg [CALLBACK] : CodeRef The callback to use for mapping a row to a data point; leave blank for a default scalar mapping Arg [USE_HASHREFS] : boolean If set to true will cause HashRefs to be returned to the callback & not ArrayRefs Arg [PARAMS] : ArrayRef The binding parameters to the SQL statement Arg [NO_ERROR] : Boolean Flag to indicate that the code should not throw an error when row counts are not equal to 1 Returntype : Scalar Exceptions : If errors occur in the execution of the SQL, if the query returned more than 1 row and if we found no rows. Status : Stable Example :
Description : Very similar to execute() except it will raise an exception if we have more or less than one row returned
public Int Bio::EnsEMBL::Utils::SqlHelper::execute_update | ( | ) |
Arg [SQL] : string $sql Arg [CALLBACK] : CodeRef The callback to use for calling methods on the DBI statement handle or DBConnection object after an update command Arg [PARAMS] : ArrayRef The binding parameters to the SQL statement Arg [PREPARE_PARAMS] : ArrayRef Parameters to bind to the prepare() StatementHandle call Returntype : integer - Number of rows affected Exceptions : If errors occur in the execution of the SQL Status : Stable Description: Used for performing updates but conforms to the normal execute statement subroutines. Example :
public Anything Bio::EnsEMBL::Utils::SqlHelper::execute_with_sth | ( | ) |
Arg [SQL] : string $sql Arg [CALLBACK] : CodeRef The callback to use for working with the statement handle once returned. This is not a mapper. Arg [PARAMS] : ArrayRef The binding parameters to the SQL statement Arg [PREPARE_PARAMS] : ArrayRef Used to pass parameters to the statement handle prepare method Description : Run query without worrying statement handles and such. Very similar to execute() except this gives you full control over the lifecycle of the statement handle and how you wish to proceed with working with a statement handle. This is for situations where you believe going through the mappers causes too much of a slow-down (since we have to execute a subroutine for every row in order to map it correctly).
However please benchmark before adopting this method as it increases the complexity of your code and the mapper slowness only becomes apparent when working with very large numbers of rows. Returntype : Anything you wish to return from the callback Exceptions : If errors occur in the execution of the SQL Status : Stable Example :
public Instance Bio::EnsEMBL::Utils::SqlHelper::new | ( | ) |
Arg [DB_CONNECTION] : Bio::EnsEMBL::DBSQL::DBConnection $db_connection Returntype : Instance of helper Exceptions : If the object given as a DBConnection is not one or it was undefined Status : Stable Description : Creates a new instance of this object. Example :
public Return Bio::EnsEMBL::Utils::SqlHelper::transaction | ( | ) |
Arg [CALLBACK] : CodeRef The callback used for transaction isolation; once the subroutine exists the code will decide on rollback or commit. Required Arg [RETRY] : integer the number of retries to attempt with this transactional block. Defaults to 0. Arg [PAUSE] : integer the time in seconds to pause in-between retries. Defaults to 1. Fractions are allowed as use delegate to Time::HiRes' sleep function Arg [CONDITION] : CodeRef allows you to inspect the exception raised and should your callback return true then the retry will be attempted. If not given then all exceptions mean attempt a retry (if specified) Returntype : Return of the callback Exceptions : If errors occur in the execution of the SQL Status : Stable Example :
return ( $error =~ /deadlock/ ) ? 1 : 0; } );
Description : Creates a transactional block which will ensure that the connection is committed when your submmited subroutine has finished or will rollback in the event of an error occuring in your block.
The code will always force AutoCommit off but will restore it to its previous setting. If your DBI/DBD driver does not support manual commits then this code will break. The code will turn off the disconnect_when_idle() method to allow transactions to work as expected.
An effect of using REPEATABLE READ transaction isolation (InnoDB's default) is that your data is as fresh as when you started your current transaction. To ensure the freshest data use SELECT ... FROM ... LOCK IN SHARE MODE> or SELECT ... FROM ... LOCK FOR UPDATE if you are going to issue updates.
Creating a transaction within a transaction results in the commit rollback statements occuring in the top level transaction. That way any block of code which is meant to to be transaction can be wrapped in this block ( assuming the same instance of SQLHelper is passed around and used).
You can also request the -RETRY of a transactional block of code which is causing problems. This can indicate your programming model is broken, so use with care.
The -RETRY argument indicates the number of times we attempt the transaction and -PAUSE indicates the time in-between attempts. These retries will only occur in the root transaction block i.e. you cannot influence the retry system in a sub-transaction. You can influence if the retry is done with the -CONDITION argument which accepts a Code reference (same as the -CALLBACK parameter). This allows you to inspect the error thrown to retry only in some situations e.g.