J
Joseph Norris
NAME
Mysql:
BLink
SYNOPSIS
use Mysql:
BLink;
DESCRIPTION
Allows linking of two tables in a mysql database for one-to-many
relationships. This module creates the link if it does not exist and
then addes to to the link. It also allows retrival of data from linked
table by key or by field and value. Please Note: Accepts database handle
of already opened database as a parameter of new. This Package requires
least one key in each table that will be linked with the following
configuration:
id int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
Also included is a simple routine updateAdd which allows simple
add/update to passed table.
EXPORT
None by default.
Modules in this package
1) Method: new - you must provide a valid database handle.
Example: use Mysql:
BLinker
my $dblinker = new Mysql:
BLinker($db);
2) Method: bldLinker
Method: bldLinker - to administer link tables between to tables for one
to many relationships
Example:
my $args = {
from_table => 'from_table_name',
to_table => 'to_table_name',
action => 'action to be done'
};
$dblinker->bldLinker($args); # action=>'create' creates frmt_tot_lnk table
$dblinker->bldLinker($args); # action=>'drop' drops frmt_tot_lnk table
my $name = $dblinker->bldLinker($args); # action=>'get_name' returns the name of the lnk table
3) Module: handleLinker
Module: handleLinker - allows creation, deletion of linked records in
the link table passed to it
for example:
my $args = {
link_table => 'link_table_name',
from_id => from_id,
to_id => to_id,
action => 'action to be done'
};
$dbliner->handleLinker(action=>'add',link_table=>$name,from_id=>20,to_id=>35);
will add a record with from pointer 20 and to pointer 35
$dblinker->handleLinker(action=>'delete',link_table=>$name,from_id=>20,to_id=>35);
will delete the same record
NOTE: on add the code will not allow duplicate entries
in addition returns an array of hash records from the to table given the from table id
for example:
given this link table record
+----+--------+-------+
| id | frm_id | to_id |
+----+--------+-------+
| 4 | 20 | 35 |
+----+--------+-------+
my $args = {
from_table => 'frmt',
to_table => 'tot',
action => 'get_name'
};
my $name = $dblinker->admLinker($args); returns the name of the lnk table
$args = {
action => 'get_lnk_records',
link_table => "$name",
from_id => 20,
sfield => 'firstname',
svalue = 'joseph'
}
my $array_ptr = $dblinker->handleLinker($args);
the code will go and get all records that have a frm_id = 20 then take the to_id and read all records in the to_table
with and id of 35, stuff them into an array of hashes and return the pointer to this array
if sfield has a valid field name in the to table the svalue is search for in that field
then code may be written in the calling script:
for my $v (@{$array_ptr}){
print $v->{firstname}; # if firstname is a field in the to table of course
}
4) updateAdd
Method: updateAdd
Example: my $action = 'update' or 'add'; my $id = the id of record to be
updated (pr_id, p_id); my $id_field = the field name of the id of record
to be updated); my $values = $d or pointer to record hash. my
($db_record_id) =
$dblinker->updateAdd(action=>"$action",table=>"$table",
update_id=>"$id",values="$d",id_field=$id_field);
Mysql:
SYNOPSIS
use Mysql:
DESCRIPTION
Allows linking of two tables in a mysql database for one-to-many
relationships. This module creates the link if it does not exist and
then addes to to the link. It also allows retrival of data from linked
table by key or by field and value. Please Note: Accepts database handle
of already opened database as a parameter of new. This Package requires
least one key in each table that will be linked with the following
configuration:
id int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
Also included is a simple routine updateAdd which allows simple
add/update to passed table.
EXPORT
None by default.
Modules in this package
1) Method: new - you must provide a valid database handle.
Example: use Mysql:
my $dblinker = new Mysql:
2) Method: bldLinker
Method: bldLinker - to administer link tables between to tables for one
to many relationships
Example:
my $args = {
from_table => 'from_table_name',
to_table => 'to_table_name',
action => 'action to be done'
};
$dblinker->bldLinker($args); # action=>'create' creates frmt_tot_lnk table
$dblinker->bldLinker($args); # action=>'drop' drops frmt_tot_lnk table
my $name = $dblinker->bldLinker($args); # action=>'get_name' returns the name of the lnk table
3) Module: handleLinker
Module: handleLinker - allows creation, deletion of linked records in
the link table passed to it
for example:
my $args = {
link_table => 'link_table_name',
from_id => from_id,
to_id => to_id,
action => 'action to be done'
};
$dbliner->handleLinker(action=>'add',link_table=>$name,from_id=>20,to_id=>35);
will add a record with from pointer 20 and to pointer 35
$dblinker->handleLinker(action=>'delete',link_table=>$name,from_id=>20,to_id=>35);
will delete the same record
NOTE: on add the code will not allow duplicate entries
in addition returns an array of hash records from the to table given the from table id
for example:
given this link table record
+----+--------+-------+
| id | frm_id | to_id |
+----+--------+-------+
| 4 | 20 | 35 |
+----+--------+-------+
my $args = {
from_table => 'frmt',
to_table => 'tot',
action => 'get_name'
};
my $name = $dblinker->admLinker($args); returns the name of the lnk table
$args = {
action => 'get_lnk_records',
link_table => "$name",
from_id => 20,
sfield => 'firstname',
svalue = 'joseph'
}
my $array_ptr = $dblinker->handleLinker($args);
the code will go and get all records that have a frm_id = 20 then take the to_id and read all records in the to_table
with and id of 35, stuff them into an array of hashes and return the pointer to this array
if sfield has a valid field name in the to table the svalue is search for in that field
then code may be written in the calling script:
for my $v (@{$array_ptr}){
print $v->{firstname}; # if firstname is a field in the to table of course
}
4) updateAdd
Method: updateAdd
Example: my $action = 'update' or 'add'; my $id = the id of record to be
updated (pr_id, p_id); my $id_field = the field name of the id of record
to be updated); my $values = $d or pointer to record hash. my
($db_record_id) =
$dblinker->updateAdd(action=>"$action",table=>"$table",
update_id=>"$id",values="$d",id_field=$id_field);