ok, just started this today, and im not yet around to implimenting the INSERT, UPDATE, DELETE or any movement as yet, but here is my nested sets implimentation. im really just looking to get some feedback on the data structure as i only started playing around with custom types this morning, they seem great, but i might have gone in with a bit of overkill. they just seem to completely do away with the need for joins, but im sure im abusing them.
i know, i know, theres no php in here, but i figured its related. im planning on using something like this as the data structure for my cms. im really looking for any postgres experts out here to have a good dig at me, its the first thing ive done really, not even real clued up on the datatypes. anyway, its all working, hoep you can see whats going on. i'll probably post the insert stuff tommorow if i get around to it. ideas?
/* {{{ CUSTOM DATA TYPES */
-- holds the minimal tree structure.
CREATE TYPE treestruct AS (
id INTEGER,
parent INTEGER,
lbound INTEGER,
rbound INTEGER,
lvl INTEGER
);
-- holds node permissions.
CREATE TYPE nodepermissions AS (
owner INTEGER,
clan INTEGER,
perms CHAR(10)
);
-- holds node data.
CREATE TYPE nodedata AS (
name VARCHAR(80),
content TEXT
);
-- holds node extra node properties.
CREATE TYPE nodeproperties AS (
created TIMESTAMP,
moded TIMESTAMP,
active INTEGER,
rank INTEGER,
kind INTEGER
);
-- holds node id and name.
CREATE TYPE nodeminimal AS (
id INTEGER,
name VARCHAR(80)
);
-- holds node in detail.
CREATE TYPE nodedetail AS (
id INTEGER,
parent INTEGER,
lbound INTEGER,
rbound INTEGER,
lvl INTEGER,
name VARCHAR(80),
content TEXT,
owner INTEGER,
clan INTEGER,
perms CHAR(10),
created TIMESTAMP,
moded TIMESTAMP,
active INTEGER,
rank INTEGER,
kind INTEGER
);
/* }}} */
/* {{{ SEQUENCES */
CREATE SEQUENCE tree_id_seq;
/* }}} */
/* {{{ TABLE STRUCTURES */
-- stores the actual tree structure.
CREATE TABLE tree (
id INTEGER DEFAULT nextval('tree_id_seq'),
parent INTEGER NOT NULL,
lbound INTEGER NOT NULL,
rbound INTEGER NOT NULL,
lvl INTEGER NOT NULL,
data nodedata NOT NULL,
permissions nodepermissions NOT NULL,
properties nodeproperties NOT NULL
);
/* }}} */
/* {{{ DUMMY DATA */
INSERT INTO tree (parent,lbound,rbound,lvl,data,permissions,properties)
VALUES (0,1,10,0,('root',NULL),(1,1,'-rw-rw-rw-'),(NOW(),NOW(),1,1,0));
INSERT INTO tree (parent,lbound,rbound,lvl,data,permissions,properties)
VALUES (1,2,5,1,('home','this is home'),(1,1,'-rw-rw-rw-'),(NOW(),NOW(),1,1,1));
INSERT INTO tree (parent,lbound,rbound,lvl,data,permissions,properties)
VALUES (1,6,7,1,('about','this is about'),(1,1,'-rw-rw-rw-'),(NOW(),NOW(),1,2,1));
INSERT INTO tree (parent,lbound,rbound,lvl,data,permissions,properties)
VALUES (1,8,9,1,('contact','this is conact'),(1,1,'-rw-rw-rw-'),(NOW(),NOW(),1,3,1));
INSERT INTO tree (parent,lbound,rbound,lvl,data,permissions,properties)
VALUES (2,3,4,2,('article','an article attched to home'),(1,1,'-rw-rw-rw-'),(NOW(),NOW(),1,1,2));
/* }}} */
/* {{{ CUSTOM FUNCTIONS */
-- get raw tree structure.
-- usage : SELECT * FROM treestructure();
CREATE OR REPLACE FUNCTION treestructure() RETURNS SETOF treestruct AS $$
SELECT id,parent,lbound,rbound,lvl FROM tree ORDER BY id;
$$ LANGUAGE SQL;
-- get all nodes in detail.
-- usage : SELECT * FROM getnodes();
CREATE OR REPLACE FUNCTION getnodes() RETURNS SETOF nodedetail AS $$
SELECT id,parent,lbound,rbound,lvl,
(data).name,(data).content,
(permissions).owner,(permissions).clan,(permissions).perms,
(properties).created,(properties).moded,(properties).active,(properties).rank,(properties).kind
FROM tree
ORDER BY lbound;
$$ LANGUAGE SQL;
-- get a node in detail.
-- usage : SELECT * FROM getnode(id);
CREATE OR REPLACE FUNCTION getnode(int) RETURNS nodedetail AS $$
SELECT id,parent,lbound,rbound,lvl,
(data).name,(data).content,
(permissions).owner,(permissions).clan,(permissions).perms,
(properties).created,(properties).moded,(properties).active,(properties).rank,(properties).kind
FROM tree
WHERE id = $1;
$$ LANGUAGE SQL;
-- get child id's and names.
-- usage : SELECT * FROM getchildren(id);
CREATE OR REPLACE FUNCTION getchildren(int) RETURNS SETOF nodeminimal AS $$
SELECT id,(data).name
FROM tree
WHERE parent = $1
ORDER BY lbound;
$$ LANGUAGE SQL;
-- get descendant id's and names.
-- usage : SELECT * FROM getdescendants(id);
CREATE OR REPLACE FUNCTION getdescendents(int) RETURNS SETOF nodeminimal AS $$
SELECT id,(data).name
FROM tree
WHERE lbound >
(
SELECT lbound
FROM tree
WHERE id = $1
)
AND rbound <
(
SELECT rbound
FROM tree
WHERE id = $1
)
ORDER BY lbound;
$$ LANGUAGE SQL;
-- does a node have children? (returns false or count of children)
-- usage : SELECT nodehaschildren(id);
CREATE OR REPLACE FUNCTION nodehaschildren(int) RETURNS bigint AS $$
SELECT COUNT(*) FROM tree WHERE parent = $1;
$$ LANGUAGE SQL;
-- get the path to a node.
-- usage : SELECT * FROM getpathto(id);
CREATE OR REPLACE FUNCTION getpathto(int) RETURNS SETOF nodeminimal AS $$
SELECT id,(data).name
FROM tree WHERE lbound <
(
SELECT lbound
FROM tree
WHERE id = $1
)
AND rbound >
(
SELECT rbound
FROM tree
WHERE id = $1
)
ORDER by lvl;
$$ LANGUAGE SQL;
-- get the path to a node including itself.
-- usage : SELECT * FROM getpathtoincself(id);
CREATE OR REPLACE FUNCTION getpathtoincself(int) RETURNS SETOF nodeminimal AS $$
SELECT id,(data).name
FROM tree WHERE lbound <=
(
SELECT lbound
FROM tree
WHERE id = $1
)
AND rbound >=
(
SELECT rbound
FROM tree
WHERE id = $1
)
ORDER by lvl;
$$ LANGUAGE SQL;
-- is one node a descendeant of another?
-- usage : SELECT isdescendantof(descendant_id,ancestor_id);
CREATE OR REPLACE FUNCTION isdescendantof(int,int) RETURNS bigint AS $$
SELECT COUNT(*)
FROM tree
WHERE id = $1
AND lbound >
(
SELECT lbound
FROM tree
WHERE id = $2
)
AND rbound <
(
SELECT rbound
FROM tree
WHERE id = $2
);
$$ LANGUAGE SQL;
-- is node a child of another?
-- usage : ischildof(child_id,parent_id);
CREATE OR REPLACE FUNCTION ischildof(int,int) RETURNS bigint AS $$
SELECT COUNT(*) FROM tree WHERE id = $1 AND parent = $2;
$$ LANGUAGE SQL;
-- number of children.
-- usage : SELECT numberofchildren(id);
CREATE OR REPLACE FUNCTION numberofchildren(int) RETURNS bigint AS $$
SELECT COUNT(*) FROM tree WHERE parent = $1;
$$ LANGUAGE SQL;
-- the number of descendants.
-- usage : SELECT numberofdescendants(id);
CREATE OR REPLACE FUNCTION numberofdescendants(int) RETURNS integer AS $$
SELECT ((SELECT rbound FROM tree WHERE id = $1) - (SELECT lbound FROM tree WHERE id = $1) - 1) / 2;
$$ LANGUAGE SQL;
/* }}} */