A tree data structure can prove useful for various applications (eg. Classification, Memory structures, etc). Here is a basic application demonstrating how Postgres ltree can integrate with a Flask website. It moreover adds the advantage of showing how notes, even images and documents, can be associated with each label or node in the tree.
Prerequisites:
1. To have an Ubuntu/Debian server up and running - as per this presentation: Virtual Server
2. To have been familiar with the set-up of a 3-Page Flask Website; its PC host reflected together on the server - see 3-Page Flask Website
3. To have read Pat Shaughnessy's 3-part tutorial, starting with:
Installing the Postgres LTREE extension.
Part A: Creating the Database
It is highly recommended to use Webmin (or PgAdmin) go to: Webmin->Servers->Postgres Database Server. Then create the user 'tester' and the database 'testdb' by which, once created, one can enter SQL commands. That said, here are the commands directly entered by the terminal: Step One: Create the Extension and the Database
sammy@imega:$ sudo su postgres
postgres@imega:~/home/sammy$ cd ~
postgres@imega:$ psql
postgres#=:$ psql
postgres#=:$ CREATE USER tester WITH PASSWORD "a123s";
postgres#=:$ CREATE DATABASE testdb OWNER tester;
postgres#=:$ exit
postgres@imega:$ psql testdb
postgres@imega:$ testdb=# CREATE EXTENSION ltree;
postgres@imega:$ testdb=# create table tree(
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
path ltree
);
create table treenotes(
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
descrip varchar(96),
notes text,
tree_id INT NOT NULL,
FOREIGN KEY (tree_id) REFERENCES tree(id) ON DELETE CASCADE
);
CREATE INDEX path_gist_idx ON tree USING GIST (path gist_ltree_ops(siglen=100));
postgres@imega:$ testdb=# GRANT ALL ON TABLE tree TO "tester";
GRANT ALL ON TABLE treenotes TO "tester";
postgres@imega:$ testdb=# exit
postgres@imega:$ exit
Note: Whatever name one chooses for a user (eg. like that of 'sql-ledger') it needs a password. Secondly, this user is required to be set at the top of the 'Allowed Host' list with the entry 'local all sql-ledger trust' in the /etc/postgresql/*/main/pg_hba.conf (remember to restart Postgres). For an example, see Step Two in: Ledger-Home