[RFC] Mysql::DBLink

Discussion in 'Perl Misc' started by Joseph Norris, Dec 8, 2013.

  1. NAME
    Mysql::DBLink

    SYNOPSIS
    use Mysql::DBLink;

    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::DBLinker

    my $dblinker = new Mysql::DBLinker($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);
     
    Joseph Norris, Dec 8, 2013
    #1
    1. Advertisements

  2. JN> DESCRIPTION Allows linking of two tables in a mysql database for
    JN> one-to-many relationships.

    Congratulations. You've just reinvented foreign key constraints.

    Here's a quarter. Go buy yourself a real database and play a game of
    Pac-Man with the change.

    Charlton
     
    Charlton Wilbur, Dec 9, 2013
    #2
    1. Advertisements

  3. Joseph Norris

    gamo Guest

    El 09/12/13 20:06, Charlton Wilbur escribió:
    Oh! Is Postgres a real database then?

    Thanks
     
    gamo, Dec 9, 2013
    #3
  4. Generalizing this such that it also support many-to-many would be an
    IMHO useful extension.
    The idea to use a single top-level method which implements its own
    internal call dispatching based on string arguments seems rather awkward
    to me. Instead of passing an 'action' argument, the individual actions
    should be methods of their own so that perl dispatches the calls.
    If duplicates are supposed to be disallowed, it might make sense to use
    a database constraint for that because the constraint will be enforced
    regardless of the method being used to manipulate the data.
     
    Rainer Weikusat, Dec 10, 2013
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.