The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DB2-db-0.25
River stage zero No dependents
++
/ DB2::Table

NAME

DB2::Table - Framework wrapper around tables using DBD::DB2

SYNOPSIS

    package myTable;
    use DB2::Table;
    our @ISA = qw( DB2::Table );
    
    ...
    
    use myDB;
    use myTable;
    
    my $db = myDB->new;
    my $tbl = $db->get_table('myTable');
    my $row = $tbl->find($id);

FUNCTIONS

new

Do not call this - you should get your table through your database object.

data_order

the key sub to override! The data must be a reference to an array of hashes. Each element (hash) in the array must contain certain keys, others are optional.

Required:
column

Column Name (must be upper case)

type

SQL type or one of:

BOOL

This will be represented by a NOT NULL CHAR that is limited to 'Y' or 'N'. In perl, this will be auto-converted to perlish true/false values. An undef will be treated as expected in perl: as false.

NULLBOOL

As above, but NULLs will be permitted. In this case, an 'N' in the database will become a false, but defined, value. Only a NULL in the database will translate to undef in perl.

Optional:
length

for CHAR, VARCHAR, etc.

opts

optional stuff - NOT NULL, PRIMARY KEY, etc. (Should use primary rather than opts => 'PRIMARY KEY'.)

default

default value

primary

true for the primary key

constraint

stuff that is placed in the table create independantly

foreignkey

For this column, will create a FOREIGN KEY statement. The value here is used during creation of the table, and should begin with the foreign table name and include any "ON DELETE", "ON UPDATE", etc., portions. This may change in the future where foreignkey will be itself another hashref with all these fields.

generatedidentity

For this column, will create as a generated identity. If this is undef or the word 'default', the option will be (START WITH 0, INCREMENT BY 1, NO CACHE), otherwise it will use whatever you provide here.

This is somewhat based on a single column for a primary key, which is not necessarily the "right" thing to do in relational design, but sure as heck simplifies coding! NOTE: Other columns may be present, but would only be used by the subclass.

get_base_row_type

When allowing the framework to create your row type object because there is no backing module, we need to know what to derive it from. If you have a generic row type that is derived from DB2::Row that you want all your rows to be derived from, you can override this.

If all your empty Row types are derived from a single type that is not DB2::Row, you should create a single Table type and have all your tables derived from that. That is, to create a derivation tree for your row such as:

    DB2::Row -> My::Row -> My::UserR

your derivation tree for your tables should look like:

    DB2::Table -> My::Table -> My::User

And then My::Table can override get_base_row_type to return q(My::Row)

getDB

Gets the DB2::db object that contains this table

schema_name

You need to override this. Must return the DB2 Schema to use for this table. Generally, you may want to derive a single "schema" class from DB2::Table which only overrides this method, and then derive each table in that schema from that class.

create_row

Creates a new DB2::Row object for this table. Called instead of the constructor for the DB2::Row object. Sets up defaults, etc. NOTE: this will not generate any identity column! We leave that up to the database, so we will retrieve that during the save before committing.

count

Should be obvious - a full count of all the rows in this table

count_where

Similar to count, except that the first parameter will be the SQL WHERE condition while the rest of the parameters will be the bind values for that WHERE condition.

find_id

Finds all rows with the primary column matching any of the parameters. For example, $tbl->find_id(1, 2, 10) will return an array of DB2::Row derived objects with all the data from 0-3 rows from this table, if the primary column for that row is either 1, 2, or 10.

find_where

Similar to find_id, the first parameter is the SQL WHERE condition while the rest of the parameters are the bind values for the WHERE condition.

In array context, will return the array of DB2::Row derived objects returned, whether empty or not.

In scalar context, will return undef if no rows are found, will return the single Row object if only one row is found, or an array ref if more than one row is found.

find_join

Similar to find_where, the first parameter is the tables to join and how they are joined (any '!!!' found will be replaced with the current table's full name), the second parameter is the where condition, if any, and the rest are bind values.

_prepare_attributes

Internally used to set any prepare attributes. Parameter says what type of prepare this is, although the list is not finalised yet.

_prepare

Internally used to cache statements. This may change to prepare if it is found to be useful.

dbi_err
dbi_errstr
dbi_state

Shortcuts to get the DBI err, errstr, and state's, respectively.

save

The table is what saves a row. If you've made changes to a row, this function will save it. Not really needed since the Row's destructor will save, but doesn't hurt.

commit

Commits all current actions

delete

Deletes the given row from the database.

delete_id

Deletes a row based on its ID. To delete multiple IDs simultaneously, simply pass in an array ref.

delete_where

Deletes rows based on the given WHERE clause. Further parameters are then bound to the DELETE statement.

SELECT

Wrapper around performing an SQL SELECT statement.

Parameters:

  • Optional: Hashref of options. Options may include:

    with

    This is the WITH clause tacked on to the front of the SELECT statement, if any. (!!! replacement as per SELECT_join is done on this.)

    Or, this can be a hashref:

        with => {
        temp2 => {
            fields => [ qw/empno firstnme/ ],
            as => q[SELECT EMPNO, FIRSTNAME FROM !!!,!XYZ! WHERE ...],
        },
        temp1 => {
            as => q[...],
        },
    },

    This will create a WITH clause like this:

        WITH temp1 AS (...), temp2 (empno,firstname) AS (SELECT EMPNO, FIRSTNAME
    FROM !!!,!XYZ! WHERE ...

    (except that !!! and !XYZ! will be expanded in the context of the current table) which will then go in the front of the rest of the SELECT statement.

    distinct

    If true, the DISTINCT keyword will be added prior to the column names resulting in a return set where each row is unique. Somewhat useless if the columns are all columns or include UNIQUE columns.

    forreadonly

    Set the query up as a "FOR READ ONLY" statement (potential performance enhancement).

    tables

    This is either a string with the table names, or an array ref of table names. Used in joins.

    prepare_attributes

    This is used in the prepare statement as extra options - see DBD::DB2 under the heading Statement Attributes. The value here must be a hashref ready to be passed in to the prepare function.

  • Arrayref of columns or string of columns, seperated by commas. For example:

        [ qw(col1 col2 col3) ]

    or 'col1,col2,col3'

  • Optional: Where-clause for SQL query.

  • Optional: Bind values for the where-clause - this is not an arrayref but the actual elements.

For example:

    $table-E<gt>SELECT({distinct=>1},[qw(col1 col2)],
                       'col3 in (?,?,?)', 'blah', 'burg', 'frob');

This will result in an SQL statement of:

    SELECT DISTINCT col1, col2 FROM myschema.mytable WHERE col3 in (?,?,?)

And ('blah', 'burg', 'frob') will be bound to the ?'s.

SELECT_distinct

Wrapper around performing an SQL SELECT statement with distinct rows only returned. Otherwise, it's exactly the same as SELECT above

SELECT_join

Wrapper around performing an SQL SELECT statement where you may be joining with other tables. The first argument is the columns you want, the second is the tables, and how they are to be joined, while the third is the WHERE condition. Further parameters are bind values. Any text matching '!!!' in the columns text will be replaced with this table's full table name. Any text matching '!(\S+?)!' will be replaced with $1's full table name.

table_name

The name of this table, excluding schema. This will default to the part of the current package after the last double-colon. For example, if your table is in package "myDB2::foo", then the table name will be "foo".

full_table_name

Shortcut to schema.table_name

column_list

Returns an array of all the column names, in order

all_data

Returns a hash ref which is all the data from data_order, but in no particular order (it's a hash, right?).

get_column

Gets information about a column or its data. First parameter is the column. Second parameter is the key (NAME, type, etc.). If the key is not given, a hash ref is returned with all the data for this column. If the key is given, only that scalar is returned.

primaryColumn

Find the primary column. First time it is called, it will determine the primary column, and then it will cache this for later calls. If you want a table with no primary column, you must override this method to return undef.

If no column has the primary attribute, then the last column is defaulted to be the primary column.

generatedIdentityColumn

Determine the generated identity column, if any. This is determined by looking for the string 'GENERATED ALWAYS AS IDENTITY' in the opts of the column. Again, this is cached on first use.

table_exists

Check if the table already exists. Normally only called by create_table.

create_table

Creates the current table. Normally only called by DB2::db::create_table.

create_table_initialise

A hook that will allow you to initialise the table immediately after its creation. If the table is newly created, the only parameter will be 'CREATE'. If the table is being altered, the first parameter will be 'ALTER' while the rest of the parameters will be the list of columns added.

The default action is mildly dangerous. It grants full select, insert, update, and delete authority to the user 'nobody'. This is the user that many daemons, including the default Apache http daemon, run under. If you override this, you can do whatever you want, including nothing. This default was put in primarily because many perl DBI scripts are expected to also be CGI scripts, so this may make certain things easier. This does not change the fact that when this grant is executed you will need some admin authority on the database.