NAME
DBIx::Hash2Table - Save a hash into a database table
Synopsis
#!/usr/bin/perl
my(%entity) = create_a_hash(...);
my($dbh) = DBI -> connect(...);
my($table_name) = 'entity';
# Cope with MySQL-specific SQL.
eval{$dbh -> do("drop table if exists $table_name")};
# Cope with non-existant table.
eval{$dbh -> do("drop table $table_name")};
my($sql) = "create table $table_name (id int, parent_id int, " .
"code char(5), name varchar(255) )";
$dbh -> do($sql);
DBIx::Hash2Table -> new
(
hash_ref => \%entity,
dbh => $dbh,
table_name => $table_name,
# columns => ['id', 'parent_id', 'name'] # or
# columns => ['id', 'parent_id', 'name', 'code']
columns => ['id', 'parent_id', 'name', '_url', 'code']
) -> insert();Description
DBIx::Hash2Table is a pure Perl module.
This module saves a hash into an existing database table of at least 3 columns.
I suggest you display the script examples/test-hash2table.pl in another window while reading the following.
In fact, you are strongly recommended to run the demo now, and examine the resultant database table, before reading further. Then, move the comment # up from line 72 to 71 and run it again.
Hash keys normally point to hash refs. This nested structure is preserved when the data is written to the table.
That is, when a hash key points to a hash ref, the hash key is written to the table. The module keeps track of the row in the table containing the parent of the 'current' hash key, and the parent's id (row number) is written to the 'current' row of the table.
Inside the hash ref you can have hash keys which are column names, or you can have hash keys which are 'normal' hash keys.
If the nested hash key is a column name, then it should point to a non-ref, ie a number or a string. In that case, you can optionally have the value it points to written to the table. In the example code, such a nested hash key is called code, and at lines 71 .. 72 you can control whether or not the code values are written to the table.
If the nested hash key is not a column name, then it should point to a hash ref, and when its turn comes, it too will be written to the table.
So, each row in the table will consist of these 3 columns, at least: id (row number), parent's id, and hash key.
In more detail, the 3 columns are:
An id column
This column is for the unique id of the row containing the hash key.
You do not have to define this column of the table to be unique, or to be a database key, but that might be a Good Idea.
DBIx::Hash2Tablecounts an integer up from 1, to generate the values for this column.The parent's id column
This column is for the non-unique id of the row of the parent of the hash key.
A name column
This column is for the hash key itself.
Distributions
This module is available both as a Unix-style distro (*.tgz) and an ActiveState-style distro (*.ppd). The latter is shipped in a *.zip file.
See http://savage.net.au/Perl-modules.html for details.
See http://savage.net.au/Perl-modules/html/installing-a-module.html for help on unpacking and installing each type of distro.
Constructor and initialization
new(...) returns a DBIx::Hash2Table object.
This is the class's contructor.
Parameters:
hash_ref
A reference to the hash to be inserted in the table.
dbh
This is a database handle.
table_name
This is the name of the table to populate.
columns
This is an array ref of column names.
Warning: The order of entries in this array ref is of great significance. Of course, just because the column names must be in this order does not mean the table itself must be declared with the columns in the same order.
Index 0
This is the column name used for the id column.
Index 1
This is the column name used for the id of the parent of the hash key.
The root key(s) of the hash will have a parent id of 0.
Index 2
This is the column name used for the hash key itself.
Indexes 3 .. N (optional)
Any other column names must be hash keys which satisfy these conditions:
The column names 3 .. N are sorted alphabetically
See below.
It's a column name
The hash key is actually the name of a column in the table.
It's a child
The hash key is a child of the 'current' hash key.
It's value is a constant
It's value is a non-ref, ie a number or a string.
Save me!
You want the hash key's value to be written to the 'current' row of the table.
In other words, even though this hash key (which is a column name!) is a child of the 'current' hash key, the value pointed to by this hash key is written to the same row has the 'current' hash key.
The program examples/test-hash2table.pl shows exactly what this means.
Warning: The names of these columns 3 .. N must be sorted alphabetically. That's the only way the module can know how to bind the values associated with these columns into their correct places in the SQL insert statement.
Method: new(...)
Returns an object of type DBIx::Hash2Table.
See above, in the section called 'Constructor and initialization'.
Method: insert()
Returns nothing.
Calling insert() actually executes the SQL insert statement, and recursively writes all hash keys to the table.
Required Modules
DBI, since you must provide a database handle.
Changes
See Changes.txt.
FAQ
Q: What is the point of this module?
A: To be able to save a hash to permanent storage via a database rather than via a file.
Q: Can your other module DBIx::Table2Hash reconstruct a hash written by this module?
A: No. Sorry. Perhaps one day.
Author
DBIx::Hash2Table was written by Ron Savage <ron@savage.net.au> in 2003.
Home page: http://savage.net.au/index.html
Copyright
Australian copyright (c) 2003, Ron Savage. All rights reserved.
All Programs of mine are 'OSI Certified Open Source Software';
you can redistribute them and/or modify them under the terms of
The Artistic License, a copy of which is available at:
http://www.opensource.org/licenses/index.html