The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-Tree-0.04
River stage zero No dependents
1 ++
/ sqltree

NAME

sqltree - hierarchical data (tree) implementation in SQL

SYNOPSIS

  sqltree OPTIONS

DESCRIPTION

sqltree generates the SQL for a herarchical data (tree) implementation using triggers, as described here:

    http://www.depesz.com/index.php/2008/04/11/my-take-on-trees-in-sql/

This implementation relies on a previously-defined table containing:

  • a single primary key column

  • a parent column that references the primary key

  • a column to hold path data [optional]

Several triggers are added to this previously-defined table, which update a new table holding in-depth tree information.

Output from sqltree can usually be piped directly to the "sqlite3" or "psql" command line tools.

OPTIONS

--dbtype

Must be 'SQLite' or 'Pg'. Patches for other database systems are welcome.

--drop

[optional] Generate DROP TABLE/TRIGGER statements preceeding the rest of the output.

--table

The name of the (existing) table holding the hierarchical data. The additional tree table will be called "table_tree".

--pk

The primary key of the (existing) table holding the hierarchical data.

--parent

The parent column of the (existing) table holding the hierarchical data.

--pktype

The SQL column type of the (existing) primary key and parent columns.

--path

[optional] The (existing) column into which the tree path will be automatically calculated. This column should be defined as TEXT or VARCHAR, and should be UNIQUE.

--path_from

[optional] When --path is given this option identifies the (existing) column from which path names will be built.

SEE ALSO

SQL::Tree(3p), The SQLite "foreign_key" pragma.

BUGS

At the moment global ordering is not implemented, but is in the works.

AUTHOR

Mark Lawrence <nomad@null.net>

COPYRIGHT AND LICENSE

Copyright (C) 2010 Mark Lawrence <nomad@null.net>

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version.