[SQL] pobierz, plaintext 
CREATE SEQUENCE contents_increment
  START 1
  INCREMENT 1
  MAXVALUE 9223372036854775807
  MINVALUE 1
  CACHE 1;
CREATE SEQUENCE nodes_increment
  START 1
  INCREMENT 1
  MAXVALUE 9223372036854775807
  MINVALUE 1
  CACHE 1;
CREATE TABLE contents (
  id bigint DEFAULT NEXTVAL('contents_increment'::text) NOT NULL,
  content text
);
CREATE TABLE nodes (
  id bigint DEFAULT NEXTVAL('nodes_increment'::text) NOT NULL,
  parent bigint,
  name character varying(100)
);
CREATE TABLE asociations (
  first_id bigint DEFAULT 0 NOT NULL,
  second_id bigint DEFAULT 0 NOT NULL,
  depth bigint DEFAULT 0 NOT NULL
);
CREATE TABLE binds (
  node_id bigint DEFAULT 0 NOT NULL,
  content_id bigint DEFAULT 0 NOT NULL
);
CREATE INDEX nodes_parent_ind ON nodes USING btree (parent);
CREATE INDEX asociations_second_id_depth_ind ON asociations USING btree (second_id, depth);
CREATE INDEX asociations_first_id_depth_ind ON asociations USING btree (first_id, depth);
CREATE INDEX asociations_first_id_ind ON asociations USING btree (first_id);
CREATE INDEX asociations_second_id_ind ON asociations USING btree (second_id);
ALTER TABLE ONLY nodes
  ADD CONSTRAINT nodes_parent_name_key UNIQUE (parent, name);
ALTER TABLE ONLY nodes
  ADD CONSTRAINT nodes_pkey PRIMARY KEY (id);
ALTER TABLE ONLY nodes
  ADD CONSTRAINT "$1" FOREIGN KEY (parent) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE ONLY contents
  ADD CONSTRAINT contents_pkey PRIMARY KEY (id);
ALTER TABLE ONLY binds
  ADD CONSTRAINT binds_node_id_content_id_key UNIQUE (node_id, content_id);
ALTER TABLE ONLY binds
  ADD CONSTRAINT binds_pkey PRIMARY KEY (node_id, content_id);
ALTER TABLE ONLY binds
  ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES contents(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY binds
  ADD CONSTRAINT "$2" FOREIGN KEY (node_id) REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY asociations
  ADD CONSTRAINT asociations_pkey PRIMARY KEY (first_id, second_id);
ALTER TABLE ONLY asociations
  ADD CONSTRAINT "$1" FOREIGN KEY (second_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE ONLY asociations
  ADD CONSTRAINT "$2" FOREIGN KEY (first_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
SELECT pg_catalog.SETVAL ('nodes_increment', 1, true);
SELECT pg_catalog.SETVAL ('contents_increment', 1, true);
INSERT INTO nodes (id, parent, name) VALUES (1, NULL, 'main');
INSERT INTO asociations (first_id, second_id, depth) VALUES (1, 1, 0);
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
-- get_path
DROP FUNCTION get_path (bigint);
CREATE FUNCTION get_path (bigint) RETURNS text
  AS '
DECLARE
 
  nid ALIAS FOR $1;
  path TEXT;
  item RECORD;
 
BEGIN path := ''/''::TEXT;
 
  FOR item IN SELECT n.name FROM nodes n, asociations a WHERE n.id = a.first_id AND a.second_id = nid ORDER BY a.depth DESC LOOP
 
    path := path || item.name || ''/''::TEXT;
 
  END LOOP;
 
  RETURN path;
 
END;
'
  LANGUAGE plpgsql;
 
-- mkdir
DROP FUNCTION mkdir (character varying, bigint);
CREATE FUNCTION mkdir (character varying, bigint) RETURNS BOOLEAN
  AS '
DECLARE
 
  nn ALIAS FOR $1;
  np ALIAS FOR $2;
 
BEGIN INSERT INTO nodes (name, parent) VALUES (nn, np);
 
  INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, (SELECT id FROM nodes WHERE name = nn AND parent = np), depth + 1 FROM asociations WHERE second_id = np;
 
  INSERT INTO asociations (first_id, second_id, depth) VALUES((SELECT id FROM nodes WHERE name = nn AND parent = np), (SELECT id FROM nodes WHERE name = nn AND parent = np), 0);
 
  RETURN true;
 
END;
'
  LANGUAGE plpgsql;
 
-- mv
DROP FUNCTION mv (bigint, bigint);
CREATE FUNCTION mv (bigint, bigint) RETURNS BOOLEAN
  AS '
 
DECLARE
 
  nid ALIAS FOR $1;
  np ALIAS FOR $2;
  temp1 RECORD;
  temp2 RECORD;
 
BEGIN FOR temp1 IN SELECT MAX(depth) AS max, second_id FROM asociations WHERE first_id = nid GROUP BY second_id LOOP
 
    DELETE FROM asociations WHERE second_id = temp1.second_id AND depth > temp1.max;
 
  END LOOP;
 
  UPDATE nodes SET parent = np WHERE id = nid;
 
  INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, nid, depth + 1 FROM asociations WHERE second_id = np;
 
  FOR temp2 IN SELECT second_id, depth FROM asociations WHERE first_id = nid AND depth <> 0 LOOP
 
    INSERT INTO asociations (first_id, second_id, depth)
    SELECT first_id, temp2.second_id, depth + temp2.depth
    FROM asociations WHERE second_id = nid AND depth <> 0;
 
  END LOOP;
 
  RETURN true;
 
END;
'
  LANGUAGE plpgsql;
 
-- mknode
DROP FUNCTION mknode (character varying, bigint);
CREATE FUNCTION mknode (character varying, bigint) RETURNS BOOLEAN
  AS '
 
DECLARE
 
  nn ALIAS FOR $1;
  np ALIAS FOR $2;
 
BEGIN INSERT INTO nodes (name, parent) VALUES (nn, np);
 
  INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, (SELECT id FROM nodes WHERE name = nn AND parent = np), depth + 1 FROM asociations WHERE second_id = np;
 
  INSERT INTO asociations (first_id, second_id, depth) VALUES((SELECT id FROM nodes WHERE name = nn AND parent = np), (SELECT id FROM nodes WHERE name = nn AND parent = np), 0);
 
  RETURN true;
 
END;
'
  LANGUAGE plpgsql;
 
-- mkcontent
DROP FUNCTION mkcontent (bigint);
CREATE FUNCTION mkcontent (bigint) RETURNS bigint
  AS '
 
DECLARE
 
  id ALIAS FOR $1;
  record RECORD;
 
BEGIN SELECT lastvalue AS id INTO record FROM contents_increment; INSERT INTO contents ( id, content ) VALUES ( record.id, cnt );
  INSERT INTO binds ( node_id, content_id ) VALUES ( id, record.id );
  RETURN true;
 
END;
'
  LANGUAGE plpgsql;
 
-- link
DROP FUNCTION link (bigint, bigint);
CREATE FUNCTION link (bigint, bigint) RETURNS BOOLEAN
  AS '
INSERT INTO binds (content_id, node_id) VALUES ($1, $2);
SELECT TRUE;
'
  LANGUAGE sql;
 
-- rm
DROP FUNCTION rm (bigint);
CREATE FUNCTION rm (bigint) RETURNS BOOLEAN
  AS '
DELETE FROM nodes WHERE id = $1;
SELECT TRUE;
'
  LANGUAGE sql;
[SQL] pobierz, plaintext