sqltree - hierarchical data (tree) implementation in SQL


  sqltree OPTIONS


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

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.



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


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


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


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


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


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


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


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


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


Mark Lawrence <>


Copyright (C) 2010 Mark Lawrence <>

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.