NAME

Tree::Numbered::Tools - Perl module to create tree objects using different sources.

SYNOPSIS

Example 1: Using a text file as a source:

  Value          LastName  FirstName
# -----          --------  ---------
  Grandfather    Smith     Abraham
    Son1         Smith     Bert
    Son2         Smith     'Clumsy Carl'
      Grandson1  Jones     Dennis
      Grandson2  Jones     Eric
    Son3         Smith     'Fatty Fred'
      Grandson3  Smith     Greg
      Grandson4  Smith     Huey
  Grandmother    Smith     Anna
    Daughter1    Smith     Berta
    Daughter2    Smith     Celine

  use Tree::Numbered::Tools;

  # Reads a text file, returns a tree object
  my $tree = Tree::Numbered::Tools->readFile(
	      filename         => $filename,
	      use_column_names => 1,
  );

Example 2: Using an array as a source:

  use Tree::Numbered::Tools;

  my $arrayref = [
	  	  [qw(serial parent name url)],
		  [1, 0, 'ROOT', 'ROOT'],
		  [2, 1, 'File', 'file.pl'],
		  [3, 2, 'New', 'file-new.pl'],
		  [4, 3, 'Window', 'file-new-window.pl'],
                 ];
  my $tree = Tree::Numbered::Tools->readArray(
	      arrayref         => $arrayref,
	      use_column_names => 1,
  );

Example 3: Using a database table as a source, use the SQL 'AS' statement for easy column mapping:

use Tree::Numbered::Tools;

my $sql = 'SELECT serial AS "Serial", parent AS "Parent", name AS "Name", url AS "URL" FROM mytable ORDER BY Serial';
my $tree = Tree::Numbered::Tools->readSQL(
            dbh              => $dbh,
            sql              => $sql,
);

Example 4: Display a tree object in the same format as the text file in example 1:

my $output = Tree::Numbered::Tools->outputFile();

Example 5: Display a tree object as an array reference, to be used for cut 'n paste in a Perl program.

my $output = Tree::Numbered::Tools->outputArray();

Example 6: Convert a text file to a database table.

  my $sql = Tree::Numbered::Tools->convertFile2DB(
	      filename         => $filename,
	      use_column_names => 1,
              dbh              => $dbh,
              table            => $table,
  );

Example 7: Convert a text file to SQL 'INSERT INTO' statements.

  my $sql = Tree::Numbered::Tools->convertFile2SQL(
	      filename         => $filename,
	      use_column_names => 1,
  );

DESCRIPTION

Tree::Numbered::Tools is a child class of Tree::Numbered. Its purpouse is to easily create a tree object from different sources.

The most useful source is probably a text file (see SYNOPSIS, example 1). The text file visualizes the tree structure as well as node names in the first column. Any other columns represent each node's properties. The format is easy to read and understand, even for a non-programmer. Besides, editing a text file is normally far more easy than editing records in a database table. Anyhow, at run time, reading from/writing to a database outperformances a text file. This module is intented to be used as a tool to create database tables using text files, not to replace tables with text files (even if the module permits you to use the text file as a source without dealing with a database). The format of the first column in the text file only requires that each tree level should be indented using one or more spaces (or tabs). It is recommended to be consistent and use the same number of spaces to indent all tree levels, even if the readFile() method tries to determine each node's level even if the indenting isn't consistent. To get each node's properties, the readFile() method parses each line in the text file using the Text::ParseWords module, so any property value containg a space must be quoted. If the last column or columns in the text file for a node are omitted, the corresponding property value is assigned the empty string.

Programmers who prefer not using an external source when creating a tree may use an array reference. Being a programmer, it is probably easier to edit an array than database records. See SYNOPSIS, example 2.

The purpouse of the SQL statement as a source for the tree object is the more straightforward way to map column names using Tree::Numbered::Tools->readSQL() than the Tree::Numbered::DB->read() method. See SYNOPSIS, example 3.

NOTES ABOUT THE ROOT NODE

Using a text file as a source, the text file does not contain the root node itself. This is on purpouse. In daily life, describing a tree, frequently there is not one single root node, but two or many 'top level nodes' as the 'Grandfather' and 'Grandmother' nodes in SYNOPSIS, example 1. To manage all the nodes as a single tree, a single root node named 'ROOT' will always be created. In tree terminology, a 'top level node' is the same as a root child. Anyway, using any other source, the 'ROOT' node should be included. See SYNOPSIS, example 2, how to create the 'ROOT' node with an array.

NOTES ABOUT FIELDS AND COLUMNS

A Tree::Numbered object uses the term 'fields' for each node's properties. A Tree::Numbered::Tools object uses the term 'columns'. Shortly, 'columns' are 'fields' in a specified order. The Tree::Numbered->getFieldNames() method uses a hash internally to get field names. This means there is no way to guarantee a specific order in obtaining the field names. The field order doesn't matter for an abstract tree object, but it does when printing a tree structure, for example. The Tree::Numbered::Tools->getColumnNames method uses an array internally to guarantee the specified order.

The column order is only an issue when working with a tree object created by a source not specifying columns, for example creating a new tree using the Tree::Numbered->new() method. When creating a tree using the readSQL() method, the column names will be obtained from the DBI::$sth->{NAME} method, i.e. the SQL statement, and thus listed in a known order. When creating a tree using the readFile()/readArray() method, the column names can be obtained using the getColumnNames() method, if the source file/array was specified with column names on its first line/row, and use_column_names is set to true.

There is no way to 'map' column names from a file/SQL/array to field names in the tree object using distinct names, as it is in Tree::Numbered::DB, for example. Instead of mapping, modify the column names in your text file or array row, or use the SQL 'AS' statement, depending on which method you use to create the tree.

METHODS SUMMARY

Methods to create a tree object reading from a source: readFile() - read from a text file readArray() - read from an array readSQL() - read from an SQL statement readDB() - read from a database table

Methods to output the contents of a tree object: outputFile() - output in text file format outputArray() - output in array format (Perl code) outputSQL() - output as SQL statements outputDB() - output to (creates) a database table

Methods to convert from one source format to another: convertFile2Array() convertFile2SQL() convertFile2DB() convertArray2File() convertArray2SQL() convertArray2DB() convertSQL2File() convertSQL2Array() convertSQL2DB() convertDB2File() convertDB2Array() convertDB2SQL()

Using convertX2Y() is practically the same as readX() followed by outputY().

Other Methods: getColumnNames - see NOTES ABOUT FIELDS AND COLUMNS getSourceType - File, Array, SQL, DB getSourceName - file name, database table name

METHODS

readFile()

readFile(
         filename         => $filename,
         use_column_names => $use_column_names,
        );

Reads $filename, returns a tree object. $use_column_names is a boolean, if set (default), assumes that the first (non-comment, non-blank) line contains column names.

readArray()

readArray(
          arrayref         => $arrayref,
          use_column_names => $use_column_names,
         );

Reads $arrayref, returns a Tree::Numbered object. $use_column_names is a boolean, if set (default), assumes that the first array row contains column names.

readSQL()

readSQL(
        dbh => $dbh,
        sql => $sql,
       );

Fetches an array using the database handle $dbh and the SQL statement $sql, returns a tree object. Uses readArray() internally to create the tree object. To map column names in the database table to tree column names, use the SQL 'AS' statement. Always get used to double quote the alias name, to make the SQL statement database type independent. Without alias quotes, reserved SQL words such as 'AS' will work as an alias on MySQL but not on PgSQL (PgSQL returns lower case aliases unless double quoted). Remember that aliases cannot contain spaces, as they reflect the column names, which in turn are used for methods getting a column's value. For example, to obtain a value for a column created from an alias called 'MyColumn', the method getMyColumn() will be used. An alias called 'My Column' will try to call the method getMy Column(), which of course will cause a run-time syntax error.

Example 1:
# GOOD, works on both MySQL and PgSQL
my $sql = 'SELECT serial AS "Serial", parent AS "Parent", name AS "Name", url AS "URL" FROM mytable ORDER BY Serial';

Example 2:
# BAD, works on MySQL but not on PgSQL
my $sql = 'SELECT serial AS Serial, parent AS Parent, name AS Name, url AS URL FROM mytable ORDER BY Serial';

Example 3:
# BAD, single quotes will not do on PgSQL
my $sql = "SELECT serial AS 'Serial', parent AS 'Parent', name AS 'Name', url AS 'URL' FROM mytable ORDER BY Serial";

Well, if you forgot to quote the aliases, readSQL() adds the quotes for you. You should just be aware of that unquoted aliases doesn't always work as expected in your daily SQL life. :-)

readDB()

readDB(
       dbh   => $dbh,
       table => $table,
       );

Fetches an array using the database handle $dbh from the table $table, returns a Tree::Numbered object. This is a wrapper for the readSQL() mehod using the SQL statement 'SELECT * from $table'. It is recommended to use the more flexible readSQL() instead, as you can map names using the 'AS' statement.

outputFile()

outputFile(
           first_indent     => $first_indent,
           level_indent     => $level_indent,
           column_indent    => $column_indent,
          );

The ouputFile() method returns the tree structure as used in the file format. The purpouse of this method is to display/create an overview of a tree object, both the tree hierarchy and each node's properties, which easily can be modified with a text editor to create a new tree object using the readFile() method.

All arguments are optional. Formatting arguments: $first_indent decides the position of the first column. $level_indent decides the indenting for each node level. $column_indent decides the number of spaces to separate columns.

outputArray()

outputArray();

The outputArray() method returns a Perl code snippet for creating a new tree object based on the current tree object, using an array reference and the readArray() method. The purpouse of this method is to easily create Perl code from whatever tree source, possibly modify/add/delete elements (nodes) in the array reference, and then use the readArray() method to create a new tree object.

outputSQL()

outputSQL(
          table => $table,
          dbs   => $dbs,
          drop  => $drop,
         );

The outputSQL() method returns SQL statements for creating records in the database table $table. The purpouse of this method is to create SQL statements for later use. If you want to create the records instead of the SQL stataments, use the outputDB() method instead.

The $dbs argument is optional, sets the database server type, defaults to 'mysql'. Currently supported database server types are MySQL and PostgreSQL. Due to inconsistent naming convention for PostgreSQL ($dbh->{Driver}->{Name} returns 'Pg' while $dbh->get_info( SQL_DBMS_NAME ) returns 'PostgreSQL'), valid 'dbs' values when using PostgreSQL are: 'postgres', 'PostgreSQL', 'PgSQL', and 'Pg'. The 'dbs' argument is case-insensitive. The generated SQL code has been tested on MySQL 3.23.54 and PostgreSQL 7.4.1, but may need modification for use with other database servers/versions.

The $drop argument is optional, if true (false by default), inserts a DROP TABLE statement before the CREATE TABLE statement. If false, the DROP TABLE statement will be left outcommented.

outputDB()

outputDB(
         dbh   => $dbh,
         table => $table,
         drop  => $drop,
        );

The outputDB() method creates a database table $table using the database handle $dbh, and insert tree nodes as table records. The purpouse of this method is to store a tree in a table. The tree object can be recreated by using one of the readSQL() or readDB methods. This method uses outputSQL() internally to get the SQL statements, and executes them. If you want to tie a tree object to a database table in "real time", first use this method with an existing tree object to create the database table. Then create a tree object using the Tree::Numbered::DB module by Yosef Meller, which will reflect changes in the database table as you modify the tree nodes.

The $dbh is a database handle. The $table and $drop arguments are the same as for outputSQL(). There is no $dbs argument, as the database server type is determined by the $dbh argument ($dbh->{Driver}->{Name} more exactly).

convertFile2Array()

convertFile2Array(
                  filename         => $filename,
                  use_column_names => $use_column_names,
                 );

Calls readFile() followed by outputArray().

convertFile2SQL()

convertFile2SQL(
                filename         => $filename,
                use_column_names => $use_column_names,
                table            => $table,
                dbs              => $dbs,
                drop             => $drop,
               );

Calls readFile() followed by outputSQL().

convertFile2DB()

convertFile2DB(
               filename         => $filename,
               use_column_names => $use_column_names,
               dbh              => $dbh,
               table            => $table,
               drop             => $drop,
               );

Calls readFile() followed by outputDB().

convertArray2File()

convertArray2File(
                  arrayref         => $arrayref,
                  use_column_names => $use_column_names,
                  first_indent     => $first_indent,
                  level_indent     => $level_indent,
                  column_indent    => $column_indent,
                 );

Calls readArray() followed by outputFile().

convertArray2SQL()

convertArray2SQL(
                 arrayref         => $arrayref,
                 use_column_names => $use_column_names,
                 table            => $table,
                 dbs              => $dbs,
                 drop             => $drop,
                );

Calls readArray() followed by outputSQL().

convertArray2DB()

  convertArray2DB(
                  arrayref         => $arrayref,
                  use_column_names => $use_column_names,
	          dbh              => $dbh,
	          table            => $table,
	          drop             => $drop,
                 );

Calls readArray() followed by outputDB().

convertSQL2File()

  convertSQL2File(
   	          dbh           => $dbh,
	          sql           => $sql,
	          first_indent  => $first_indent,
	          level_indent  => $level_indent,
	          column_indent => $column_indent,
                 );

Calls readSQL() followed by outputFile().

convertSQL2Array()

  convertSQL2Array(
   	          dbh           => $dbh,
	          sql           => $sql,
                 );

Calls readSQL() followed by outputArray().

convertSQL2DB()

  convertSQL2DB(
   	        dbh           => $dbh,
	        sql           => $sql,
	        dbh_dest      => $dbh_dest,
	        table         => $table,
	        drop          => $drop,
               );

Calls readSQL() followed by outputDB().

NOTE: There are two database handles, $dbh and $dbh_dest, in case you use one database as a source and another as destination. The argument $dbh_dest is optional, defaults to $dbh, assumes using the same database handle for both source and destination. Using different database handles, this method can be useful to migrate a tree table from MySQL to PostgreSQL, for example.

convertDB2File()

  convertDB2File(
  	         dbh           => $dbh,
		 table         => $table,
	         first_indent  => $first_indent,
	         level_indent  => $level_indent,
	         column_indent => $column_indent,
                );

Calls readDB() followed by outputFile().

convertDB2Array()

  convertDB2Array(
  	          dbh           => $dbh,
		  table         => $table,
                 );

Calls readDB() followed by outputArray().

convertDB2SQL()

  convertDB2SQL(
   	        dbh           => $dbh,
	        sql           => $sql,
	        table         => $table,
	        table_dest    => $table_dest,
	        dbs           => $dbs,
	        drop          => $drop,
               );

Calls readDB() followed by outputSQL(). NOTE: $table is the source table, $table_dest is the table name used in the generated SQL statements.

getColumnNames()

Returns a list (in array context) or a ref to a list (in scalar context) of the column names.
The list corresponds to:
  Using a file           - the words on the first non-comment or blank line.
  Using an array         - the first array row.
  Using an SQL statement - the SQL field names
  Using a database table - the table column names

Using this method on a tree created using with use_column_names set to false returns undef;

getSourceType()

Returns one of the strings 'File', 'Array', 'SQL', 'DB' depending on which source was used to create the tree object.

getSourceName()

Returns the file name if the source type is 'File', or the database table name if the source type is 'DB'.
Returns undef if source type is 'Array' or 'SQL'.

BUGS AND OTHER ISSUES

There may be bugs in the code.
The code was written more to be useful as a tool, rather than to be compact, fast and clean.
Please report through CPAN:
http://rt.cpan.org/NoAuth/Bugs.html?Dist=Tree-Numbered-Tools
or send mail to bug-Tree-Numbered-Tools@rt.cpan.org 

Incorrectly using $use_column_names=1 together with a source where column names are *not* specified will cause unpredictable results, probably a run-time error.
The same is true for incorrect usage of $use_column_names=0 together with a source where column names *are* specified.
This module doesn't try to determine incorrect usage as described above.
The possible incorrect usage applies to files and arrays, which may or may not use column names.
SQL expressions and DB tables always use column names by nature.
Always use $use_column_names=1 (set by default using any method) and always specify column names in the source text file or array.

For suggestions, questions and such, email me directly.

EXAMPLES

To see working examples, see the 'examples' directory in the distribution.

SEE ALSO

Tree::Numbered, Tree::Numbered::DB by Yosef Meller

AUTHOR

Johan Kuuse, <johan@kuu.se>

COPYRIGHT AND LICENSE

Copyright 2004 by Johan Kuuse

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

cpanm Tree::Numbered::Tools

perl -MCPAN -e shell
install Tree::Numbered::Tools