DB2::Table - Framework wrapper around tables using DBD::DB2
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);
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.
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.
length
for CHAR, VARCHAR, etc.
opts
optional stuff - NOT NULL, PRIMARY KEY, etc. (Should use primary rather than opts => 'PRIMARY KEY'.)
NOT NULL
PRIMARY KEY
primary
opts => 'PRIMARY KEY'
default
default value
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.
(START WITH 0, INCREMENT BY 1, NO CACHE)
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)
My::Table
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.
prepare
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.
Deletes a row based on its ID. To delete multiple IDs simultaneously, simply pass in an array ref.
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:
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.
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.
Set the query up as a "FOR READ ONLY" statement (potential performance enhancement).
This is either a string with the table names, or an array ref of table names. Used in joins.
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.
Statement Attributes
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.
To install DB2::db, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DB2::db
CPAN shell
perl -MCPAN -e shell install DB2::db
For more information on module installation, please visit the detailed CPAN module installation guide.