DBIx::Table2Hash - Read a database table into a hash
DBIx::Table2Hash
#!/usr/bin/perl use DBIx::Table2Hash; my($key2value) = DBIx::Table2Hash -> new ( dbh => $dbh, table_name => $table_name, key_column => 'name', value_column => 'id' ) -> select(); # or my($key2hashref) = DBIx::Table2Hash -> new ( dbh => $dbh, table_name => $table_name, key_column => 'name', ) -> select_hashref(); # or my($key2tree) = DBIx::Table2Hash -> new ( dbh => $dbh, table_name => $table_name, key_column => 'name', child_column => 'id', parent_column => 'parent_id', skip_columns => ['code'] ) -> select_tree();
DBIx::Table2Hash is a pure Perl module.
This module reads a database table and stores keys and values in a hash.
The aim is to create a hash which is a simple look-up table. To this end, the module allows the key_column to point to an SQL expression.
select() and select_hashref() do not nest the hash in any way.
select()
select_hashref()
select_tree() returns a nested hash. select_tree() will call select_hashref() if necessary, ie if you have not called select_hashref() first.
select_tree()
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.
new(...) returns a DBIx::Table2Hash object.
This is the class's contructor.
Parameters:
Note: These parameters are not checked until you call select_*(), which means the parameters can be passed in to new(), select_*(), or both.
select_*()
new()
dbh
A database handle.
This parameter is mandatory.
table_name
The name of the table to select from.
key_column
When calling select(), select_hashref() and select_tree(), this is the name of the database column, or the SQL expression, to use for hash keys.
Say you have 2 columns, called col_a and col_b. Then you can concatenate them with:
key_column => 'concat(col_a, col_b)'
or, even fancier,
key_column => "concat(col_a, '-', col_b)"
child_column
When calling select_tree(), this is the name of the database column which, combined with the parent_column column, defines the relationship between nodes and their children.
This parameter is mandatory if you call select_tree(), and ignored if you call select() or select_hashref().
parent_column
When calling select_tree(), this is the name of the database column which, combined with the child_column column, defines the relationship between nodes and their children.
value_column
The name of the database column to use for hash values.
This parameter is mandatory if you call select(), and ignored if you call select_hashref() or select_tree().
where
The optional where clause, including the word 'where', to add to the select.
skip_columns
An array ref of column names to ignore when reading the database table.
It defaults to [].
This parameter is optional.
Returns a object of type DBIx::Table2Hash.
See above, in the section called 'Constructor and initialization'.
Returns a hash ref.
Each key in the hash points to a single value.
Named parameters, as documented above, can be passed in to this method.
Calling select() actually executes the SQL select statement, and builds the hash.
The demo program test-table2hash.pl, in the examples/ directory, calls select().
Each key in the hash points to a hashref.
Calling select_hashref() actually executes the SQL select statement, and builds the hash.
The demo program test-table2hash.pl, in the examples/ directory, calls select_hashref().
Calling select_tree() automatically calls select_hashref(), if you have not already called select_hashref().
The demo program test-table2tree.pl, in the examples/ directory, calls select_tree().
The method select_tree() can obviously return a hash with multiple keys at the root level, depending on the contents of the database table.
Such a hash cannot be passed in to CGI::Explorer V 2.00+. Here's a way around this restriction: Create, on the fly, a hash key which is The Mother of All Roots. Eg:
my($t2h) = DBIx::Table2Hash -> new(...); my($tree) = $t2h -> select_tree(...); my($exp) = CGI::Explorer -> new(...) -> from_hash(hashref => {OneAndOnly => $tree});
Q: What is the point of this module?
A 1: To be able to restore a hash from a database rather than from a file.
A 2: To be able to construct, from a database table, a hash suitable for passing in to CGI::Explorer V 2.00.
Q: Can your other module DBIx::Hash2Table be used to save the hash back to the database?
DBIx::Hash2Table
A: Sure.
Q: Do you ship demos for the 3 methods select(), select_hashref() and select_tree()?
A: Yes. See the examples/ directory.
If you installed this module locally via ppm, look in the x86/ directory for the file to unpack.
If you installed this module remotely via ppm, you need to download and unpack the distro itself.
Q: Are there any other modules with similar capabilities?
A: Yes:
DBIx::Lookup::Field
Quite similar.
DBIx::TableHash
This module takes a very long set of parameters, but unfortunately does not take a database handle.
It does mean the module, being extremely complex, can read in more than one column as the value of a hash key, and it has caching abilities too.
It works by tieing a hash to an MySQL table, and hence supports writing to the table. It uses MySQL-specific code, for example, when it locks tables.
Unfortunately, it does not use data binding, so it cannot handle data which contains single quotes!
Further, it uses /^\w+$/ to 'validate' column names, so it cannot accept an SQL expression instead of a column name.
Lastly, it also uses /^\w+$/ to 'validate' table names, so it cannot accept table names and views containing spaces and other 'funny' characters, eg '&' (both of which I have to deal with under MS Access).
DBIx::Tree
This module was the inspiration for select_tree().
As it reads the database table it calls a call-back sub, which you use to process the rows of the table.
https://github.com/ronsavage/DBIx-Table2Hash
Bugs should be reported via the CPAN bug tracker at
https://github.com/ronsavage/DBIx-Table2Hash/issues
DBIx::Table2Hash was written by Ron Savage <ron@savage.net.au> in 2003.
Home page: http://savage.net.au/index.html
Australian copyright (c) 2003, Ron Savage. 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
To install DBIx::Table2Hash, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Table2Hash
CPAN shell
perl -MCPAN -e shell install DBIx::Table2Hash
For more information on module installation, please visit the detailed CPAN module installation guide.