DBIx::Declare - declare the database access object, specify the sql/stored proc names, arguments and return and have the code generated for you |
DBIx::Declare - declare the database access object, specify the sql/stored proc names, arguments and return and have the code generated for you
Version 0.01.006
use DBIx::Declare MyDB => { data_source => "dbi:ODBC:Driver=SQL Server;Server=Some;Database=MyDB", type => 'MSSQL', # may be MSSQL, MySQL, SQLite, Pg/PostgreSQL # - this is case insensitive and may be used to allow database specific options/implementation. # Normaly taken from the data_source, but needed for ODBC or DBD::Proxy user => '...', pass => '....', attr => { 'RaiseError' => 0, 'AutoCommit' => 1, LongReadLen => 65536 }, generate => 'now', # or 'when connected' or 'as needed'. The last one is the default. # The 'now' may not work if the data_source is incomplete and you ask the module to infer the arguments for stored procedures. In such cases some methods trigger a warning. case => 'sensitive', infer => ['VerbNamingConvention'], methods => { set_people_name_by_id => { sql => "UPDATE people SET name = ? WHERE id = ?", args => [ qw(name id) ], return => '$', }, get_site_entry_by_id => { sql => "SELECT * FROM sites WHERE SiteId = ?", args => [ qw(id) ], return => '%', # only one row }, get_people_entry_by_last_name => { sql => "SELECT * FROM people WHERE last_name = ?", args => [ qw(name) ], return => '@%', # multiple rows }, # Although not really recommended, you can also change the database schema drop_table => { sql => "DROP TABLE ?", args => [ qw(table) ], return => '$', noprepare => 1, # For non-prepareable queries noquote => 1, # For non-quoteable arguments (like table names) },
GetATSName => { return => '$', }, GetSiteName => { defaults => { SiteID => 3, }, # return => '$$', # inferred }, }, };
my $db = MyDB->new();
my $SiteName = $db->GetSiteName( 457 ); my $OtherSiteName = $db->GetSiteName( SiteID => 123 );
my %SiteInfoHash = $db->get_site_entry_by_id( 123 ); my $SiteInfoHref = $db->get_site_entry_by_id( 123 );
Specify the connection and type, a few options and the SQL (or - for MSSQL only so far - the stored procedures) and have the database access object generated for you.
DBIx::Declare uses AUTOLOAD to create methods and statement handles based on the data in the hashref supplied in the argument 'methods'. Statement handles are persistent in the lifetime of the instance or till the instance gets disconnected from the database. The generated code of the methods is persistent until your program exits. It is an easy way to set up accessor methods for a fixed (in the sense of database and table layout) schema.
When a method defined in the 'methods' hashref is invoked, it is verified that the arguments in 'args' are provided. The arguments are then applied to the persistent statement handle that is created from the value 'sql' statement.
The use DBIx::Declare
statement is used to declare and build the class of your
database access objects. The first parameter is the name of the class to build (or
an undefined variable that will hold a generated unique class name), the second
parameter is a hash reference containing the options and methods.
The options are
data_source
STRINGThe data source to be fed to DBI->connect. May be overwritten or appended to by the constructor of the generated class. Optional.
type
STRINGThe type of database to connect to. This may allow some database specific functionality. If you do not specify the type, the module takes the DBD name from the data_source. The type will be lowercased and the generated class will inherit either from DBIx::Declare::DB::<the type> (if it exists!) or from DBIx::Declare::DB::unknown.
The '++' (get the generated ID of the last inserted row) is available only for
MySQL, Pg (Postgres), MSSQL and SQLite. The stored procedure call automatic
SQL generation is only available for MSSQL. (To add support for that create
package DBIx::Declare::DB::<your type>, set its @ISA to qw(DBIx::Declare::DB::unknown)
and define a method $db-
_InferSQL( $method_name, $method_options).
This souboutine will be called whenever you attempt to use a declared method
with no sql=> specified. It should return the generated SQL and set the $method_options->{args}
and $method_options->{out_args} accordingly.
Optional.
user
STRINGThe default database connection username. Optional.
pass
STRINGThe default database connection password. Optional.
attr
HASHThe default database connection attributes. Leave blank for DBI defaults.
The attributes specified later when calling the constructor are MERGED with this hash! Only the individual attributes are overwritten, not the whole hash.
Optional.
methods
HASHThe methods hash reference. Also see the KEYS IN METHODS DEFINITION description.
Required.
case
STRINGSpecifies whether the names of the parameters passed to the methods are case sensitive or not and whether the arguments discovered for the stored procedures are lowercased or left intact.
sensitive - arguments are case sensitive, no case mangling for discovered arguments insensitive - arguments are case insensitive (very slightly slower) lowercase - the discovered arguments are lowercased, apart from that all handling is case sensitive
Optional. Default is "sensitive".
warn_useless
BOOLEANSpecifies whether to warn if you include a named parameter that's not expected by a method in its call. By default true.
disconnected
BOOLEANIf set to true, the object doesn't connect to the database until you either call $obj->_Connect or a generated method.
infer
CODEREF|STRING|ARRAY of (CODEREF|STRING)If you use a consistent naming convention, your return type can be infered from the SQL or want to use some kind of templating you can specify one or several subroutines to be called for each method specification and set some options automatically or convert one generic method specification to several.
Please see the Automation
section.
format_errors
CODEREFformat_errors => sub { my ($self, $msg, $errstr) = @_; ... format_errors => sub { my ($self, $msg, $errstr, $sql, \%params, $method) = @_; ...
This subroutine gets called for each error and may be used to reformat it. It gets two to sixarguments. The first is the database access object, the second the error message from DBIx::Declare (things like "Method Foo failed to execute", "bind_param failed for parameter Bar for method Foo" etc.), the third (if applicable) is the errstr from DBI.
If the error_details
option is true you also receive the SQL statement being evaluated, the hash of parameters
(from $sth->{ParamValues}, please see this property in DBI
!) and the full name of the method including
the classname. You probably want only the part after the last doublecolon.
It can use the $self->_format_error_details($sql, \%params, $method)
method to get a formatted version of
the statement plus parameters. This way this works depends on the database type!
The subroutine should return a formatted message.
error_details
BOOLEANIf set to a true value, DBIx::Declare includes the executed SQL in the error message.
on_errors
This may be either a reference to a procedure to be called to report the errors or one of the strings 'carp', 'croak' or 'die' or an error message template.
If you specify a code reference then DBIx::Declare calls your subroutine passing the error message (after formatting and with the error details added) as the first argument and returns whatever that subroutine returns.
If you specify 'carp', 'croak' or 'die' them DBIx::Declare calls the respective function with the error message.
If you specify some other string then DBIx::Declare replaces the token _ERROR_ in the string by the actuall error message and then either die()s (if the error template ends with a newline) or carp()s.
code_cache
code_cache => '/path/to/cache/directory' code_cache => \%tied_hash code_cache => \&cacher
The code generated for the methods (including the SQL and argument lists infered for the stored procedures) may be stored in a code cache. The cache may either be a directory on the disk, a (tied) hash or a subroutine, functioning both as a getter and setter.
In the first case each generated method will be written into a separate file in the specified directory. The file will be named using the method name and the MD5 hash of the SQL (or nothing if not specified), the argument list, the return type and the values of noquote and noprepare options in this format: <MethodName>-<MD5_hash>-generated.pl
You may specify the maximal age of the cached code using the code_cache_max_age option. The value of this option may be either in minutes ( /($number)\s*m/), in hours ( /($number)\s*h/ ) or days ( /($number)\s*d?/ ). You can use floats.
In the second case the key will be $MethodName . $; . $MD5_hash
and the value will be the code (as string of course).
In the last case the subroutine will either be called with a single parameter, the $MethodName . $; . $MD5_hash
to get the code if available or with two parameters to add the generated code to the cache.
It only makes sense to cache the code using this mechanism BETWEEN invocations of the script(s), not within one run. Within one run the code is eval""ed just once and reused ever since. The only exception may be multithreaded scripts where it may make sense to have a cache shared between the threads because the code eval""ed in one thread is not made available to others. The caching makes even more sense for stored procedures that you call using the _InferSQL functionality (without explicitely specifying the number, names and types of parameters, because the _InferSQL has to query the database catalog to find out the info about the stored procedure.
If you make changes to the sql, args or return options, a new version will be generated even if there is an old version in the cache, but if you change the signature of a stored procedure you have to clear the cache to force the scripts started after the change to requery the database and rediscover the arguments!
new
my $db = MyDB->new(); my $db = MyDB->new( $data_source); my $db = MyDB->new( $data_source, {attributes}); my $db = MyDB->new( $data_source, $username, $password); my $db = MyDB->new( $data_source, $username, $password, {attributes});
If you call the constructor with no arguments, then the data_source, user, password and attributes are used. If the data_source doesn't start with 'dbi:' it's considerent a "partial data source" and is appended to the "dbi:Something:" part of the data_source specified when declaring the class.
The attributes are merged with the ones specified in the class declaration and overwrite the individual DBI options, not the whole hash.
_Connect
Connects the object to the database if not connected already. Returns whether the object is connected.
_Disconnect
Disconnects the object from the database.
_Connected
Returns whether the object is connected. (Or rather if the object is connected returns the DBI object.)
_Is_error
Returns whether the last method call resulted in an error.
_Session
my $session_hash = $db->_Session(); my $value = $db->_Session( "argument name"); $db->_Session( "argument name", "value");
Returns or sets a session variable. The session is something like global defaults. Whenever you omit an argument when calling a method it's first looked up in the session and then in the method-specific default values. Only if found in neither place, the method complains about missing required argument. If you name your arguments consistently you can use this to pass things like UserId or UserType behind the scenes.
Commit (make permanent) the most recent series of database changes if the database supports transactions and AutoCommit is off.
If AutoCommit is on, then calling commit will issue a "commit ineffective with AutoCommit" warning. Please see the transactions section of the DBI
docs.
Rollback (undo) the most recent series of uncommitted database changes if the database supports transactions and AutoCommit is off.
If AutoCommit is on, then calling rollback will issue a "rollback ineffective with AutoCommit" warning.
Enable transactions (by turning AutoCommit off) until the next call to commit or rollback. After the next commit or rollback, AutoCommit will automatically be turned on again.
If AutoCommit is already off when begin_work is called then it does nothing except return an error. If the driver does not support transactions then when begin_work attempts to set AutoCommit off the driver will trigger a fatal error.
The generated methods may be called either using named parameters or positional ones. You use the named parameters like this
my $result = $db->SomeMethod( -ArgName => $value, -OtherArg => $something);
You may skip the optional parameters (those that have a default value specified in the method specification or that have a value stored in the session. You can also skip the OUTPUT parameters that you are not interested in.
If a parameter is declared as output, you have to use a variable (or rather ... a lvalue), but you should not pass a reference:
my $result = $db->GetSiteName( -SiteId => 13, -SiteName => $name);
if you use a constant as the value of an OUTPUT parameter you receive an error.
If you use the positional parameters (in the order specified in the args=> option in the method specification) you can either do it like this
my $result = $db->SomeMethod( 12, 45, $whatever);
or like this
my $result = $db->SomeMethod( _ => $variable, 45, $whatever);
You should use the second format whenever it is not guaranteed that the first argument will not look like a named parameter (match /^-\w+$/) or be an underscore.
Thanks to the various Automation
features, there are no really mandatory options.
As long as at least the 'sql' and 'return' can be infered, you should be fine. Without the 'args'
option you will not be able to use the named parameters style, specify defaults or use the session.
The value of 'args' is an array of key names. The keys must be in the same order as the matching SQL placeholders ("?"). All elements of this array MUST match /^\w+$/! You can specify the same name several times if you want several placeholders to always be assigned the same value.
The 'sql' key holds the query or statement to execute. For MSSQL, if you do not specify this option DBIx::Declare assumes you want to call the stored procedure named as this method or the one specified by the 'call' option. In this case DBIx::Declare asks the database for the list of parameters of that procedure and sets the 'sql', 'args' and 'out_args' accordingly.
This option is used only if you do not specify the 'sql' and specifies the stored procedure to call.
The value of 'return' (return value) can be:
execute()
return value (NON-SELECT)
fetchxxx_xxx()
methods you need then (SELECT)
This doesn't work together with output parameters in MSSQL!
fetchrow_arrayref()
in the DBI docs.
If you specify this option, the return status of the stored procedure will be returned as if it was an output parameter named according to the value of this option.
The 'noprepare' key indicates that the method should not use a prepared statement handle for execution. This is really just slower. It should be used when executing queries that cannot be prepared. (Like 'DROP TABLE ?'). It only works with non-SELECT statements. So setting 'return' to anything else than WANT_RETURN_VALUE will cause an error. See the 'bind_param' section of the 'Statement Handle Methods' in the DBI documentation for more information.
The 'noquote' key indicates that the arguments listed should not be quoted. This is for dealing with table names (Like 'DROP TABLE ?'). It's really a hack. The 'noquote' key has no effect unless used in collaboration with the 'noprepare' key on a method.
You should think at least twice before using this option! It's your duty to ensure the values passed to the methods created with this option are safe!!!
This is a hash in format
{ arg_name => [ $max_len, $dbi_type, $type_string], ... }
the first two are then passed to DBI's bind_param_inout as the third argument and as the TYPE attribute, the third is used in error details. You have to use a variable as the argument for the output parameters, but unlike some other modules you DO NOT have to pass a reference.
This is usually only used with stored procedures and it populated automatically.
This parameter overrides the global on_errors
parameter with one exception. If you specify an error template
and the global on_errors
is a subroutine reference then DBIx::Declare first fills in the template and then calls that subroutine.
You may define several methods at once if you separate their names by commas and you may define the method in several steps like this:
'FetchSomethingA,FetchSomethingH' => { sql => 'SELECT ...', args => [...], }, FetchSomethingA => {return => '@@'}, FetchSomethingH => {return => '@%'},
Please keep in mind that if you use a hashref for the methods then the order of the definitions is NOT defined! This means that if you specify some option twice for a method, then it's not clear which of the values will be in effect. Use the arrayref to ensure the order and the last one wins.
There are two stages in which some of the method definition options may be set automatically. First there is the infer
option that's evaluared during the class construction and then the database type specific _InferSQL()
that gets called just before
the method is first prepare()d in case you do not specify the sel
option.
The _InferSQL()
is so far defined only for MSSQL and it assumes that if you do not specify the SQL, you want to call
a stored procedure with the same name as your method. The _InferSQL()
queries the database for the parameters of
the stored procedure and sets the sql
, the args
and the out_args
accordingly.
For all other types of databases the _InferSQL()
only reports that you did not specify the sql
. You can define your own
database type by creating a package DBIx::Declare::DB::<yourname>, setting it's @ISA to qw(DBIx::Declare::DB::unknown)
and implementing the _InferSQL()
. And whatever other methods you want to make available to all declared classes with
the type
attribute set to this type.
I would love to include versions of _InferSQL for all widely used databases supporting stored procedures, please contact me if you can help me with this!
The infer=> option may be either a string containing the name of one of the builtin infer subroutines (or another subroutine in the DBIx::Declare::infer package) or a subroutine reference or an array containing several strings or subroutine references. If you specify an array, then all the infer rules are evaluated in the specified order.
The builtin rules are:
This rule infers the return type for the methods for which you did not specify it. It expects that the names of the methods start with a verb and that you use the LeadingCaps convention. It assumes that methods starting with 'Get' have some output parameters and return no resultset (return => '$$'), those starting with 'Fetch', 'Search' or 'Export' return a recordset (return => '@%'), those starting with 'Check' or 'Hash' are stored procedures that return the result as the procedure's return value (MSSQL only) (return => '$R'), those starting with 'Insert' insert a row and you want the ID of the inserted row (return => '++') and those starting with 'Update', 'Set', 'Import' or 'Delete' do not return any data and you want the execute()'s return value (return => '$').
You will probably want to use this just as an example. It's implemented like this:
sub VerbNamingConvention { my ($name,$opt) = @_; return if $opt->{return};
if ($name =~ /^Get[A-Z]/) { $opt->{return} = '$$'; } elsif ($name =~ /^(Fetch|Search|Export)[A-Z]/) { $opt->{return} = '@%'; } elsif ($name =~ /^(Check|Has)[A-Z]/) { $opt->{return} = '$R'; } elsif ($name =~ /^(Insert)[A-Z]/) { $opt->{return} = '++'; } elsif ($name =~ /^(Update|Set|Import|Delete)[A-Z]/) { $opt->{return} = '$'; } }
This rule infers the return type based on the SQL. If the SQL starts by 'SELECT' the return=> will be set to '@%', if it starts with 'INSERT', 'UPDATE' or 'DELETE' it's assumed you want the execute()'s return value ('$').
This rule allows you to define several methods as one using very simple templating for example if you want to declare a SelectById method for several tables you can do it like this:
'Fetch${Table}ById} => { sql => 'SELECT * FROM ${Table} WHERE Id = ?', args => [ 'id' ], return => '%', # we assume just one row vars => { Table => [qw(Foo Bar Baz)] }, }
This will create three methods, FetchFooById, FetchBarById and FetchBazById.
You can specify as many variables as you want, but you have to include them all in the name of the method! You can reference the template variables in the method name, the sql and the args:
'Fetch${Table}ById} => { sql => 'SELECT * FROM ${Table} WHERE ${Table}Id = ?', args => [ '${Table}Id' ], return => '%', # we assume just one row vars => { Table => [qw(Foo Bar Baz)] }, }
Please notice the single quotes and the curlies around the template variables! Both are required.
You can specify a subroutine reference in the use DBIx::Declare ...
statement or define a subroutine in the DBIx::Declare::infer
package. In both cases the subroutine gets called for each method specification and will receive the method name as the first and
the options as the second parameter. It can modify the options in the second parameter, it even modify the values in @_
(eg. it can rename the method by modifying $_[0]) and it can even push more method specifications onto @_.
This means that if (like the SimpleTemplate above) you want to duplicate the method specification, you can add another methodname and options hash to @_ and it will be added to the list of methods to be defined.
By default all methods carp()
in case there is an error. You can specify that they should die()
or croak()
instead or that a function you specify should be called.
You may also provide a formatting subroutine that will be called on the error messages before the carp()
, croak()
, die()
or the callback function.
Apart from this you may ask DBIx::Declare to include the call details in the error message. If you do so then the error message will
(if possible) contain a snippet of SQL that is being executed, including the values. This is usualy NOT exactly the thing being executed
against the database though, DBIx::Declare prepares the statements and uses placeholders.
You may then change the way errors are reported when defining the methods or when calling them.
Whenever an error occures (and is not forced to be ignored by the format_errors subroutine) the module sets an internal flag in the object
that may be queried by method is_error()
and stores the error message in $db->{errormessage};
.
Copyright (c) 2009 Jenda Krynicky <Jenda@Krynicky.cz>. All rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the Artistic License for more details.
Jenda Krynicky, <Jenda at Krynicky.cz>