NAME

TableMap - Maps relational tables into hashes

SYNOPSIS

use DBI;
$dbh=DBI->connect(...);

use TableMap;

$database=new TableMap::DB(
  dbh         =>   $dbh,
    # DBI Database handler
  seq_mode =>   "ora",
    # Sequence handling mode
    # "ora": "select seqence.currval from dual";
    # "pg" : "select sequence.last_value";
    # db systems, which doesn't support sequences currently
    # doesn't supported (insert won't work)
  prepare_cached => 0,
    # Can use "prepare_cached" method of the DBI?
    # This causes problems for me, and that's why the
    # default is now 0. This param is not mandatory.
);

# You can use connect hash to specify connect parameters directly.
# In this case you doesn't need to specify "dbh" parameter:
# $database=new TableMap::DB( 
#   connect=> [$data_source, $username, $auth, \%attr],
#   seq_currval => ...
# );
                        
$company=$database->new_table (
  table => "company",    # Table name, mandatory
  key   => "id",         # Primary Key for the table
  seq   => "seq_company",# Sequence name for key field generation.
                         # Mandatory only if "insert" is in use
);

# $database->new_table(...)
#   is the same as
# new TableMap ( db => $database, ... )

$user  =$database->new_table (
  table => "users",
  key   => "id",
  seq   => "seq_users",
  ref   => { company_id => [ $company, "user" ] },
    # This can be used for connecting tables.
    # This is similar to the SQL phrase:
    # 
    # .. company_id  int references company (id),
    #
    # only the key field can be referenced.
);

%company_14_users= % {$company->{14}->user };

# All user IDs
keys %$user;

# Sets Company #14 Data:
$company_14 = $company->{14};
$company_14->{tax_num} = "123456";
$company_14->{phone1} = "+42456245546";
$company_14->write;

# Wrong example:
# $company->{14}->{tax_num} = "123456"
# $company->{14}->write;
# This doesn't work, because it always create a new Row object, 
#   and the cache is stored per object.

# Select a sub-relation
$table=$user->select("company_id = ?",$id);

# Select with constraint
$user->constraint( company_id => $id );

# New record
$id=$company->new(
  { name=>"abc", 
    phone1=>"30/123567", 
    mobile=>"20/1234" } );
if ($id) { print "Id: $id\n"; } else { print "Insert failed: "; };

# Delete record
delete $company->{13};
%{ $company->{13} }=();

DESCRIPTION

This class is designed for mapping a table into a perl hash, which has keys (these are the primary keys of the table), and the values are the rows, represented by a hash.

Basic Usage

You can create TableMap objects for tables. You must specify a parameter hash to the constructor, which has the following keys:

db

This is a reference to a TableMap::DB module. Normally you create a new TableMap object by the method of the "new_table" of a TableMap::DB instance, then you may not specify this.

table

Specifies the table name

key

Specifies the primary key. This must be specified, so if you don't have primary keys in your table, you cannot use the TableMap (for the whole table. You can use it for a subset of rows specified by the "constraint" param).

seq

If you want to use "insert" with self-incremental keys, you must specify this. Database servers, which doesn't implement sequences (mySQL) currently not supported.

ref

Creating a 1:N reference. The value is a hash reference, where the keys are database fields, and the values contains the reference information in an array reference:

ref => { field1 => [ $table1, "function1" ], field2 => [ $table2, "function2" ], };

In the example above you can use the reference field (company_id) from the "user" table to query the company in which the user work: $company_name = $user->{17}->company_id->{name}.

function1 is the name of the function, which can be used for the back-reference, eg. can be used to determine the user-ids in one company: @user_ids= keys %{ $company->{45}->users }. "users" is the function name in this example.

where

Specifies a "where" condition for selecting id-s, so you can select a subset of keys with this. Also available with the "search" function:

@user_ids= keys %{ $table->search("age > 25") };

or

$table=new TableMap (table => "table", where => "age > 25"); @user_ids=keys %$table;

constraint

This is similar to "select", but it can be used only for equality test. The main advantage is that it can be used for deletion and insertion. If you insert something into a table, which has constraint parameter, all the values in the constraint hash is set in the new record. This constraint is used internally, when somebody creates a back reference by a back-reference function.

References

There is two kind of reference in this module. All two are set up by "ref" parameter in the table. If you use a "ref" parameter, then the "back_ref" is automatically created in the other table (if not already exists).

ref

$user->company_id gives a TableMap::Row record, which is a ROW in the company table. Each hash keys are field names.

back_ref

$company->users gives a TableMap object, which is a COLLECTION of rows (represented by a hash), which gives back the employees of the companies. (you can use "keys ..." expression for the ids).

Caching

All the sql queries are cached in this module. This must be rethink, because sometimes it is not the best solution. I want some extra parameter for caching in the newer versions. Now all the query results are cached for 10 seconds.

The Cache object is $TableMap::cache, and it can be invalidated by the $TableMap::cache->invalidate_cache call.

The cache is hierarchical (it is stored in tree structure).

For more information on the cache you can see the source code.

Performance

This module is NOT the most efficient method for getting data from the database. It is written to avoid endless sql-query-typing with minimal performance loss.

The module uses two kind of sql queries:

select key from table

This is used for querying all the keys from a table. This can be affected by the "constraint" and the "where" parameter.

select * from table where id=1234

This is used for querying all the fields of one row. This can be affected by the "constraint" parameter, but not thw "where".

Sometimes querying the whole table is more effective, (when you have enough memory), but currently it is only a planned parameter.

TODO

• This module is now usable for one purpose. I have profiled it, and I've found that the "read_data" function is the most time-consuming. This must be handled by re-organizing the cache.

• I want to add a parameter for setting for the caching mode. Sometimes no caching is allowed, but sometimes te full table must be cached for good performance.

• Cahce reorganization is needed, because reaching a cached data is quite slow.

COPYRIGHT

Copyrigh (c) 2000 Balázs Szabó (dLux) All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

AUTHOR

dLux <dlux@kapu.hu>