NAME

DBIx::Declare - declare the database access object, specify the sql/stored proc names, arguments and return and have the code generated for you

VERSION

Version 0.01.006

SYNOPSIS

  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 );

DESCRIPTION

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.

What does that mean?

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.

CLASS DECLARATION

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 STRING

The data source to be fed to DBI->connect. May be overwritten or appended to by the constructor of the generated class. Optional.

type STRING

The 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 STRING

The default database connection username. Optional.

pass STRING

The default database connection password. Optional.

attr HASH

The 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 HASH

The methods hash reference. Also see the KEYS IN METHODS DEFINITION description.

Required.

case STRING

Specifies 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 BOOLEAN

Specifies whether to warn if you include a named parameter that's not expected by a method in its call. By default true.

disconnected BOOLEAN

If 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 CODEREF

        format_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 BOOLEAN

If 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!

STATIC METHODS

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

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

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.

_BeginTrans

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.

GENERATED METHODS

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.

KEYS IN METHODS DEFINITION

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.

args

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.

sql

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.

call

This option is used only if you do not specify the 'sql' and specifies the stored procedure to call.

return

The value of 'return' (return value) can be:

'@' - returns a list (or array ref in scalar context) containing all the rows concatenated together. This is especially handy for queries returning just one column. (SELECT)
'_@' - returns a list (or array ref in scalar context) containing the data of the first row. Other rows are not returned! The statement handle is finish()ed. (SELECT)
'%' - returns a list containing the name and value pairs (or a hash ref in scalar context) containing the data of the first row. Other rows are not returned! The statement handle is finish()ed. (SELECT)
'@%' - returns an array/arrayref of hashrefs (SELECT)
'@%lc' - returns an array/arrayref of hashrefs. The column names are lowercased (SELECT)
'@%uc' - returns an array/arrayref of hashrefs. The column names are uppercased (SELECT)
'@@' - returns an array of arrayrefs (SELECT)
'$' - returns the ->execute() return value (NON-SELECT)
'$R' - returns the stored procedure return value (MSSQL only - EXEC @RETURN_VALUE = dbo.ProcName ...)
'$$' - returns the OUTPUT parameters (will return a list or arrayref containing the values of the arguments specified in the 'out_args' option. This option is built automaticaly for stored procedures for MSSQL)
'++' - returns the new auto_increment value of an INSERT (MySQL/Pg/MSSQL/SQLite specific. Please send me an email if you know how to implement this for other databases).
'<>' - returns the DBI statement handle. You may call whatever fetchxxx_xxx() methods you need then (SELECT) This doesn't work together with output parameters in MSSQL!
'&@' - call the subroutine specified in the "sub" option for each row fetched, pass each row as a list of items, return a list of return values of the subroutine applications. (SELECT)
'&\@' - call the subroutine specified in the "sub" option for each row fetched, pass each row as an array reference, return a list of return values of the subroutine applications. (SELECT) Please note that the refererence will point to the same array for all subroutine calls! See fetchrow_arrayref() in the DBI docs.
'&%' - call the subroutine specified in the "sub" option for each row fetched, pass row as a list of column names and items (to assign to hash). Return a list of return values of the subroutine applications. (SELECT)
'&\%' - call the subroutine specified in the "sub" option for each row fetched, pass row as a hash ref. Return a list of return values of the subroutine applications. (SELECT)
'.@' / '->@' - call the constructor of the class specified in the "class" option for each row fetched, pass the row as a list of items, return a list of the created objects (SELECT)
'.\@' / '->\@' - call the constructor of the class specified in the "class" option for each row fetched, pass the row as an arrayref, return a list of the created objects (SELECT)
'.%' / '->%' - call the constructor of the class specified in the "class" option for each row fetched, pass the row as a list of column names and items, return a list of the created objects (SELECT)
'.\%' / '->\%' - call the constructor of the class specified in the "class" option for each row fetched, pass the row as a hash ref, return a list of the created objects (SELECT)
\&subroutine - equivalent to '&@' with this subroutine in the 'sub' option (SELECT)
'Class::Name' - equivalent to '.@' with this class in the 'class' option (SELECT)
'CDF' - this will produce XML in the TIBCO General Interface CDF format. By default the jsxid attribute is the ordinary number of the record. You can use the, 'jsxid' option to specify the column to use as the id or a subroutine that computes the id and 'jsxmap' to specify the mapping from the columns to record attributes. The jsxmap may be either an array ref or a hash ref. (NOT IMPLEMENTED YET!)

return_value

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.

noprepare

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.

noquote

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!!!

out_args

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.

on_errors

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.

Automation

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.

_InferSQL

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!

infer

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:

VerbNamingConvention

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} = '$';
                }
        }

ReturnByStatement

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 ('$').

SimpleTemplate

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.

Custom infer rules

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.

ERROR HANDLING

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

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.

AUTHOR

Jenda Krynicky, <Jenda at Krynicky.cz>

BUGS

Please report any bugs or feature requests to bug-dbix-declare at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-Declare. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

TODO

More DBD specific functions (Oracle/Pg).
Better documentation.
More "failure" tests.
Testing expired statement handles.

ACKNOWLEDGEMENTS

Thanks to Casper Warming <cwg@usr.bin.dk> for his DBIx::LazyMethod.

COPYRIGHT & LICENSE