NAME

Tie::Table - Maps relational tables into hashes

SYNOPSIS

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

use Tie::Table;

$database=new Tie::Table::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 Tie::Table::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 Tie::Table ( 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 );

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

# Inserting or replacing a record with a specified id;
$company->{1456} = { name => "abc", phone1=>"30/123456" };

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

# Select and constraint with returning only one row (the first):

$row = $user->select1("age > ? and parent_id = ? ",18,175);

$user_row_by_name = $user->constraint1( name => "Ms. Jackson" );
$user_row_by_name = $user->by( name => "Ms. Jackson" ); # by == constraint1

# Changing key order

@keys = keys %{ $table->order("group desc") };

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 Tie::Table objects for tables. You must specify a parameter hash to the constructor, which has the following keys:

db

This is a reference to a Tie::Table::DB module. Normally you create a new Tie::Table object by the method of the "new_table" of a Tie::Table::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 Tie::Table (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 Tie::Table (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.

order

This parameter describes the key-retrieval order. The value of the parameter is appended to an "order by" parameter to the sql query, which retrieves the keys from the database.

Tie::Table methods

There are predefined methods, which can be called on table objects:

select $sql, @parameters

Creates a new table object with "select" parameter appended to the existing one, for example:

$selected_table = $table->select("age > ?", 18);

The result is also a Tie::Table object.

constraint

Creates a new table object with "constraint" parameters set. This is similar to the select method,but this only usable for equality relations:

$selected_table = $table->constraint( age => 18 );

If you insert into the $selected_table, then the "age" parameter automatically set to "18".

select1, constraint1 and by

These are variations of "select" and "constraint". The only difference is that you will return only the first row of the result if more than one row matched.

These syntax are good if you know that at most 1 row is returned by the select, for example when you have more than one unique indices on the table.

"by" is a short version of "constraint1", only a syntactic sugar:

$ms_jackson_row = $user->by( name => "Ms. Jackson" );

order $name

Sets the "order" parameter on the table and returns it as a new object, e.g:

my $ordered_table = $table->order("group_name desc");

If you call keys on %$ordered_table, then the key order will appropriate. If the $table already has an order parameter, then it will be overwritten.

key $key

Sets the "key" parameter on the table and returns it as a new object. Useful for tables, which are used as an N:N relation, e.g., the table is the following:

create table rel_user_day (
    id      int primary key serial,
    user_id int not null references users (id),
    day_id  int not null references day (id)
);

The tablemap table-declaration is the following:

$database->new_table(
    table => "rel_user_day",
    key   => "id",
    ref   => {
        user_id => [ $tables->{user}, "days" ],
        day_id  => [ $tables->{day},  "users" ],
    }
);

Then your key is "id", but you can temporarily change the keys if you want to get the day_id-s for a user by the following command:

$user_day_hash = $tables->{user}->{$user_id}->days->key("day_id");

then you will get the day_id-s by keys %$user_day_hash

Tie::Table::Row methods

write

This method must be called when the user is finished modifying the record, e.g:

my $record = $table->{$id};

$record->{name} = "Blah";
$record->{street} = "Headway";
$record->write;

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 Tie::Table::Row record, which is a ROW in the company table. Each hash keys are field names.

back_ref

$company->users gives a Tie::Table 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 rethought, 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. This value can be tuned by setting the Tie::Table::CACHE_SECS variable.

The global cache object is $Tie::Table::cache, and it can be invalidated by the $Tie::Table::cache->invalidate_cache call.

The cache is hierarchical (it is stored in tree structure). If you want to invalidate the whole cache, you can call:

$Tie::Table::cache->invalidate_cache([])

If you want to invalidate only one table, you can call:

$Tie::Table::cache->invalidate_cache(["table_name"])

No other syntax currently supported.

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.

BUGS AND LIMITATIONS

• The current implementation cannot handle tables, which is used to express a relationship between two data. These tables normally have two foreign key fields. If you want to use them with that module, then you need to add a unique identifier for each row. For examply by using postgresql and if your table looks like this:

  You can write the following definition for this table (assumed that users and day tables are already defined):

• 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.

• Caching can be set globally right now (by $Tie::Table::CACHE_SECS) but it must be more fine-grained in the future.

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>

cpanm Tie::Table::DB

perl -MCPAN -e shell
install Tie::Table::DB