NAME "DBIx::Table2Hash" - Read a database table into a hash Synopsis #!/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' ) -> select_tree(); Description "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_tree()" returns a nested hash. "select_tree()" will call "select_hashref()" if necessary, ie if you have not called "select_hashref()" first. 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::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. * dbh A database handle. This parameter is mandatory. * table_name The name of the table to select from. This parameter is mandatory. * 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)" This parameter is mandatory. * 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. This parameter is mandatory if you call "select_tree()", and ignored if you call "select()" or "select_hashref()". * 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. Method: new(...) Returns a object of type "DBIx::Table2Hash". See above, in the section called 'Constructor and initialization'. Method: select(%parameter) 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()". Method: select_hashref(%parameter) Returns a hash ref. Each key in the hash points to a hashref. Named parameters, as documented above, can be passed in to this method. 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()". Method: select_tree(%parameter) Returns a hash ref. Each key in the hash points to a hashref. Named parameters, as documented above, can be passed in to this method. 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()". DBIx::Table2Hash and CGI::Explorer 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}); Required Modules Only those shipped with Perl. Changes See Changes.txt. FAQ 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? 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. Author "DBIx::Table2Hash" was written by Ron Savage ** 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