[Everydevel-opers] SQL schema

[<pau...@us...> <pau...@us...>]

Hello anyone who's there,

Here's a proposed SQL schema for Everydevel.  It's aims are to make 
nodes more light-weight and make it easier for the platform to be extended.

It's postgresql because I knew that using views and rules I could make 
it transparent to the current code, I'm not sure that mysql and sqlite 
would allow this flexibility.

Other points:

Yes it's slow, especially packing and unpacking those permissions, but a 
new front end model will fix that.

Yes, it's complicated, but higher levels of normalisation always involve 
  more tables.

Constructive thoughts?

Paul

CREATE TABLE "node_relation_type" (
    name varchar(125) UNIQUE NOT NULL,
    description text,
    node_relation_type_pk serial,
    PRIMARY KEY ( "node_relation_type_pk" )
)
;
  CREATE INDEX index_node_relation_type ON node_relation_type ( name ) ;

  CREATE AGGREGATE textcat_all(
       basetype    = text,
       sfunc       = textcat,
       stype       = text,
       initcond    = ''
   );


CREATE TABLE "node_basic" (
node_id  bigserial UNIQUE NOT NULL,
type_nodetype bigint,
createtime timestamp NOT NULL,
PRIMARY KEY (node_id)
);

  CREATE TABLE "node_relationship" (
    node bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
    hasa_node bigint REFERENCES node_basic(node_id) ON DELETE RESTRICT,
    relation_type int REFERENCES node_relation_type( 
node_relation_type_pk) ON DELETE RESTRICT,
    PRIMARY KEY ("node", "hasa_node", "relation_type")
)
;
CREATE TABLE "sqltable_type" (
   sqltable_type varchar(255) NOT NULL,
   description varchar(255),
   sqltable_type_pk serial,
   PRIMARY KEY ( "sqltable_type_pk" )
)
;
CREATE TABLE "sqltable" (
     sqltable_name varchar(255) NOT NULL UNIQUE,
     type int REFERENCES sqltable_type(sqltable_type_pk) ON DELETE RESTRICT,
     sqltable_pk bigserial NOT NULL,
     PRIMARY KEY ( sqltable_pk )
)
;

CREATE TABLE "node_sqltable" (
      sqltable bigint REFERENCES sqltable(sqltable_pk),
      nodetype_id bigint REFERENCES node_basic(node_id),
      node_sqltable_pk serial NOT NULL,
      UNIQUE (sqltable, nodetype_id),
      PRIMARY KEY ( node_sqltable_pk )
)
;
  CREATE TABLE "nodegroup_restrict_type"(
    group_nodetype_id bigint REFERENCES node_basic(node_id) ON DELETE 
CASCADE,
    only_nodetype bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
    PRIMARY KEY( group_nodetype_id, only_nodetype )
)
;

CREATE TABLE "nodebase_restrictdupes" (
      nodetype_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
      PRIMARY KEY (nodetype_id)
)
;

CREATE TABLE "nodebase_node_workspace" (
      node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
      enable boolean NOT NULL,
      PRIMARY KEY (node_id)
)
;

CREATE TABLE "nodebase_node_revisions" (
      node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
      maxrevisions int DEFAULT 0 NOT NULL,
      PRIMARY KEY (node_id)
)
;


CREATE TABLE "node_modified" (
modified timestamp,
node_id bigint NOT NULL REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (node_id)
)

;


CREATE TABLE "node_title" (
title varchar(240),
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (node_id)
)

;


CREATE TABLE "node_statistics_hits" (
hits bigint NOT NULL DEFAULT 0,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (node_id)

)

;


CREATE TABLE "node_location" (
loc_location bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (node_id)
)

;


CREATE TABLE "node_statistics_reputation" (
reputation bigint NOT NULL DEFAULT 0,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (node_id)
)

;
  CREATE TABLE "node_lock" (
locktime timestamp NOT NULL,
lockby_user bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (node_id)
)
;
  CREATE TABLE "user_type" (
     user_type_pk serial NOT NULL,
     usertype varchar(32) NOT NULL,
     description varchar(255),
     PRIMARY KEY ("user_type_pk")
)
;

    CREATE TABLE "permission_type" (
    permission_type_pk serial NOT NULL,
    permission  varchar(127) NOT NULL,
    description varchar(255),
    PRIMARY KEY("permission_type_pk")
)
;

    CREATE TABLE "permission_behaviour" (
    permission_behaviour_pk serial NOT NULL,
    behaviour  varchar(10) NOT NULL,
    description varchar(255),
    PRIMARY KEY ("permission_behaviour_pk")
)
;

CREATE TABLE "node_authorisation" (
permission_type int REFERENCES permission_type(permission_type_pk) ON 
DELETE RESTRICT NOT NULL,
permission_behaviour int REFERENCES 
permission_behaviour(permission_behaviour_pk) ON DELETE RESTRICT NOT NULL,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (permission_type, node_id, permission_behaviour)
)
;

CREATE TABLE "node_access" (
user_type int REFERENCES user_type(user_type_pk) ON DELETE RESTRICT NOT 
NULL,
permission_type int REFERENCES permission_type(permission_type_pk) ON 
DELETE RESTRICT NOT NULL,
permission_behaviour int REFERENCES 
permission_behaviour(permission_behaviour_pk) ON DELETE RESTRICT NOT NULL,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (user_type, permission_type, node_id, permission_behaviour)
)
;

CREATE TABLE "node_dynamicpermission" (
type int REFERENCES user_type (user_type_pk) ON DELETE RESTRICT,
permission bigint REFERENCES node_basic(node_id) ON DELETE RESTRICT,
node_id bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
PRIMARY KEY (type, node_id)
)
;

CREATE TABLE "node_relationship_usertype" (

    node bigint REFERENCES node_basic(node_id) ON DELETE CASCADE,
    with_node bigint REFERENCES node_basic(node_id) ON DELETE RESTRICT,
    relation_type int REFERENCES node_relation_type( 
node_relation_type_pk) ON DELETE RESTRICT,
    user_type int REFERENCES user_type(user_type_pk) ON DELETE RESTRICT,
    PRIMARY KEY ("node", "with_node", "relation_type", "user_type")

)
;

CREATE FUNCTION dynamicpermission ( bigint, text ) RETURNS bigint AS $$

SELECT permission FROM node_dynamicpermission WHERE type = ( SELECT 
user_type.user_type_pk FROM user_type WHERE user_type.usertype = $2) AND 
node_id = $1;

$$ LANGUAGE SQL;
;

CREATE OR REPLACE FUNCTION nodepermissions(bigint, varchar) RETURNS text 
AS $$

SELECT CASE ( SELECT permission_behaviour.behaviour from node_access, 
user_type, permission_type, permission_behaviour where 
permission_type.permission = 'read' AND node_access.node_id = $1 AND 
user_type.usertype = $2 AND node_access.user_type = 
user_type.user_type_pk AND node_access.permission_behaviour = 
permission_behaviour.permission_behaviour_pk  AND 
node_access.permission_type = permission_type.permission_type_pk) WHEN 
'enable' THEN 'r' WHEN 'disable' THEN '-' WHEN 'inherit' THEN 'i' END
||
CASE (  SELECT permission_behaviour.behaviour from node_access, 
user_type, permission_type, permission_behaviour where 
permission_type.permission = 'write' AND node_access.node_id = $1 AND 
user_type.usertype = $2 AND node_access.user_type = 
user_type.user_type_pk AND node_access.permission_behaviour = 
permission_behaviour.permission_behaviour_pk  AND 
node_access.permission_type = permission_type.permission_type_pk)  WHEN 
'enable' THEN 'w' WHEN 'disable' THEN '-' WHEN 'inherit' THEN 'i' END
||
CASE ( SELECT permission_behaviour.behaviour from node_access, 
user_type, permission_type, permission_behaviour where 
permission_type.permission = 'execute' AND node_access.node_id = $1 AND 
user_type.usertype = $2 AND node_access.user_type = 
user_type.user_type_pk AND node_access.permission_behaviour = 
permission_behaviour.permission_behaviour_pk  AND 
node_access.permission_type = permission_type.permission_type_pk)  WHEN 
'enable' THEN 'x' WHEN 'disable' THEN '-' WHEN 'inherit' THEN 'i' END
||
CASE (  SELECT permission_behaviour.behaviour from node_access, 
user_type, permission_type, permission_behaviour where 
permission_type.permission = 'delete' AND node_access.node_id = $1 AND 
user_type.usertype = $2 AND node_access.user_type = 
user_type.user_type_pk AND node_access.permission_behaviour = 
permission_behaviour.permission_behaviour_pk AND 
node_access.permission_type = permission_type.permission_type_pk)  WHEN 
'enable' THEN 'd' WHEN 'disable' THEN '-' WHEN 'inherit' THEN 'i' END
||
CASE WHEN $2 = 'author' OR $2 = 'defaultauthor' THEN '' ELSE ( CASE ( 
SELECT permission_behaviour.behaviour from node_access, user_type, 
permission_type, permission_behaviour where permission_type.permission = 
'create' AND node_access.node_id = $1 AND user_type.usertype = $2 AND 
node_access.user_type = user_type.user_type_pk AND 
node_access.permission_behaviour = 
permission_behaviour.permission_behaviour_pk  AND 
node_access.permission_type = permission_type.permission_type_pk)  WHEN 
'enable' THEN 'c' WHEN 'disable' THEN '-' WHEN 'inherit' THEN 'i' END)
END;

$$ LANGUAGE SQL;
;

CREATE FUNCTION "related_node_id" ( bigint, varchar ) RETURNS bigint AS $$

SELECT node_relationship.hasa_node
        FROM node_relationship, node_relation_type
        WHERE node_relationship.node = $1
        AND node_relationship.relation_type = node_relation_type_pk
        AND node_relation_type.name = $2

$$ LANGUAGE SQL;
;

CREATE TABLE "attribute_type" (
    name varchar(125) UNIQUE NOT NULL,
    description varchar(255),
    attribute_type_pk serial,
    PRIMARY KEY ("attribute_type_pk")
)
;

CREATE TABLE "timestamp_attribute" (
     time timestamp NOT NULL,
     node bigint REFERENCES node_basic( node_id) ON DELETE CASCADE,
     PRIMARY KEY ("node")
)
;

CREATE VIEW "node" AS
   SELECT node_basic.node_id AS node_id, node_basic.type_nodetype, 
node_basic.createtime,
          related_node_id( node_basic.node_id, 'author_user') AS 
author_user,
          node_title.title,
          node_modified.modified,
          node_statistics_hits.hits,
          related_node_id( node_basic.node_id, 'loc_location') AS 
loc_location,
          node_statistics_reputation.reputation,
          node_lock.locktime,
          node_lock.lockby_user,
          nodepermissions( node_basic.node_id, 'author') AS authoraccess,
          nodepermissions( node_basic.node_id, 'group') AS groupaccess,
          nodepermissions( node_basic.node_id, 'other') AS otheraccess,
          nodepermissions( node_basic.node_id, 'guest') AS guestaccess,
          dynamicpermission( node_basic.node_id, 'author' ) as 
dynamicauthor_permission,
          dynamicpermission( node_basic.node_id, 'group' ) as 
dynamicgroup_permission,
          dynamicpermission( node_basic.node_id, 'other' ) as 
dynamicother_permission,
          dynamicpermission( node_basic.node_id, 'guest' ) as 
dynamicguest_permission,
          related_node_id( node_basic.node_id, 'group_usergroup') AS 
group_usergroup
   FROM
     node_basic
   LEFT JOIN
     node_modified ON node_modified.node_id = node_basic.node_id
   LEFT JOIN
     node_title ON node_basic.node_id = node_title.node_id
   LEFT JOIN
     node_statistics_hits ON node_basic.node_id = 
node_statistics_hits.node_id
   LEFT JOIN
     node_statistics_reputation ON node_basic.node_id = 
node_statistics_reputation.node_id
   LEFT JOIN
     node_lock ON node_basic.node_id = node_lock.node_id
;

;

CREATE FUNCTION select_sqltables_nodetype( bigint, text ) RETURNS 
varchar AS $$
   DECLARE
    node_id ALIAS FOR $1;
    tabletype ALIAS FOR $2;
    tablename varchar;
    tablelist varchar := '';
   BEGIN
    FOR tablename IN  SELECT sqltable.sqltable_name FROM sqltable, 
sqltable_type, node_sqltable WHERE node_sqltable.nodetype_id = node_id 
AND node_sqltable.sqltable = sqltable.sqltable_pk AND sqltable.type = 
sqltable_type_pk AND sqltable_type.sqltable_type = tabletype  LOOP

     IF tablelist <> '' THEN
        tablelist := tablelist || ',' || tablename;
     ELSE
        tablelist := tablename;
     END IF;

    END LOOP;
   RETURN tablelist;
   END;
$$ LANGUAGE plpgsql


;

  CREATE VIEW "nodetype" AS
       SELECT node_basic.node_id AS nodetype_id,
              related_node_id( node_basic.node_id, 'restrict_nodetype' ) 
  AS restrict_nodetype,
              related_node_id( node_basic.node_id, 'extends_nodetype' ) 
  AS extends_nodetype,

              ( SELECT (CASE ( SELECT b.behaviour FROM 
node_authorisation a, permission_type t, permission_behaviour b WHERE 
a.node_id = node_basic.node_id AND a.permission_type = 
t.permission_type_pk AND t.permission = 'restrictdupes' AND 
a.permission_behaviour = b.permission_behaviour_pk ) WHEN 'enable' THEN 
1 WHEN 'disable' THEN 0 WHEN 'inherit' THEN -1 END ) ) AS restrictdupes,

              ( SELECT  select_sqltables_nodetype( node_basic.node_id, 
'attributetable' ) ) AS sqltable,

              (  SELECT  select_sqltables_nodetype( node_basic.node_id, 
'grouptable' ) ) AS grouptable,

              nodepermissions( node_basic.node_id, 'defaultauthor' ) AS 
defaultauthoraccess,
              nodepermissions( node_basic.node_id, 'defaultgroup' ) AS 
defaultgroupaccess,
              nodepermissions( node_basic.node_id, 'defaultother' ) AS 
defaultotheraccess,
              nodepermissions( node_basic.node_id, 'defaultguest' ) AS 
defaultguestaccess,
              related_node_id( node_basic.node_id, 
'defaultgroup_usergroup') AS defaultgroup_usergroup,

              dynamicpermission ( node_basic.node_id, 'defaultauthor') 
AS defaultauthor_permission,
              dynamicpermission ( node_basic.node_id, 'defaultgroup') AS 
defaultgroup_permission,
              dynamicpermission ( node_basic.node_id, 'defaultother') AS 
defaultother_permission,
              dynamicpermission ( node_basic.node_id, 'defaultguest') AS 
defaultguest_permission,
              ( SELECT maxrevisions FROM nodebase_node_revisions r WHERE 
r.node_id = node_basic.node_id ) AS maxrevisions,
              ( SELECT (CASE ( SELECT b.behaviour FROM 
node_authorisation a, permission_type t, permission_behaviour b WHERE 
a.node_id = node_basic.node_id AND a.permission_type = 
t.permission_type_pk AND t.permission = 'canworkspace' AND 
a.permission_behaviour = b.permission_behaviour_pk ) WHEN 'enable' THEN 
1 WHEN 'disable' THEN 0 WHEN 'inherit' THEN -1 END ) ) AS canworkspace

       FROM node_basic
        LEFT JOIN nodegroup_restrict_type
        ON nodegroup_restrict_type.group_nodetype_id = node_basic.node_id
        LEFT JOIN nodebase_restrictdupes
        ON  nodebase_restrictdupes.nodetype_id = node_basic.node_id

      WHERE type_nodetype = 1;
;

CREATE OR REPLACE FUNCTION insert_permissions (bigint, text, text) 
RETURNS VOID AS $$
INSERT INTO node_access ( node_id, user_type, permission_type, 
permission_behaviour )
SELECT
$1,
user_type.user_type_pk,
permission_type.permission_type_pk,
permission_behaviour.permission_behaviour_pk
FROM user_type, permission_type, permission_behaviour
WHERE permission_type.permission = 'read'
AND   user_type.usertype = $2
AND   permission_behaviour.behaviour = CASE substring( $3 from 1 for 1 ) 
WHEN 'r' THEN 'enable' WHEN '-' THEN 'disable' WHEN 'i' THEN 'inherit' 
ELSE NULL END;


INSERT INTO node_access ( node_id, user_type, permission_type, 
permission_behaviour )
SELECT
$1,
user_type.user_type_pk,
permission_type.permission_type_pk,
permission_behaviour.permission_behaviour_pk
FROM user_type, permission_type, permission_behaviour
WHERE permission_type.permission = 'write'
AND   user_type.usertype = $2
AND   permission_behaviour.behaviour = CASE substring( $3 from 2 for 1 ) 
WHEN 'w' THEN 'enable' WHEN '-' THEN 'disable' WHEN 'i' THEN 'inherit' 
ELSE NULL END;

INSERT INTO node_access ( node_id, user_type, permission_type, 
permission_behaviour )
SELECT
$1,
user_type.user_type_pk,
permission_type.permission_type_pk,
permission_behaviour.permission_behaviour_pk
FROM user_type, permission_type, permission_behaviour
WHERE permission_type.permission = 'execute'
AND   user_type.usertype = $2
AND   permission_behaviour.behaviour = CASE substring( $3 from 3 for 1 ) 
WHEN 'x' THEN 'enable' WHEN '-' THEN 'disable' WHEN 'i' THEN 'inherit' 
ELSE NULL END;


INSERT INTO node_access ( node_id, user_type, permission_type, 
permission_behaviour )
SELECT
$1,
user_type.user_type_pk,
permission_type.permission_type_pk,
permission_behaviour.permission_behaviour_pk
FROM user_type, permission_type, permission_behaviour
WHERE permission_type.permission = 'delete'
AND   user_type.usertype = $2
AND   permission_behaviour.behaviour = CASE substring( $3 from 4 for 1 ) 
WHEN 'd' THEN 'enable' WHEN '-' THEN 'disable' WHEN 'i' THEN 'inherit' 
ELSE NULL END;


INSERT INTO node_access ( node_id, user_type, permission_type, 
permission_behaviour )
SELECT
$1,
user_type.user_type_pk,
permission_type.permission_type_pk,
permission_behaviour.permission_behaviour_pk
FROM user_type, permission_type, permission_behaviour
WHERE permission_type.permission = 'create'
AND   user_type.usertype = $2
AND   substring( $3 from 5 for 1 ) <> ''
AND   permission_behaviour.behaviour = CASE substring( $3 from 5 for 1 ) 
WHEN 'c' THEN 'enable' WHEN '-' THEN 'disable' WHEN 'i' THEN 'inherit' 
ELSE NULL END;



$$ LANGUAGE SQL;

;

CREATE OR REPLACE FUNCTION update_permissions (bigint, text, text) 
RETURNS VOID AS $$

DELETE FROM node_access WHERE node_access.node_id = $1 AND user_type = ( 
SELECT user_type.user_type_pk FROM user_type where user_type.usertype = 
$2 );

SELECT insert_permissions( $1, $2, $3 );

$$ LANGUAGE SQL;
;

CREATE FUNCTION insert_node_relation (bigint, bigint, varchar) RETURNS 
VOID AS $$

INSERT INTO node_relationship (hasa_node, node, relation_type )
   SELECT $2, $1, node_relation_type.node_relation_type_pk
   FROM node_relation_type WHERE node_relation_type.name = $3 AND $2 IS 
NOT NULL;

$$ LANGUAGE SQL;
;

CREATE FUNCTION insert_dynamic_permission ( bigint, bigint, varchar ) 
RETURNS VOID AS $$
--node_id, permission_id, user_type

INSERT INTO node_relationship_usertype (node, with_node, relation_type, 
user_type ) SELECT  $1, $2, ( SELECT node_relation_type_pk FROM 
node_relation_type WHERE name = 'dynamic permission' ), ( SELECT 
user_type_pk FROM user_type WHERE user_type.usertype = $3) WHERE $2 IS 
NOT NULL;


$$ LANGUAGE SQL;
;

CREATE RULE _insert_node AS ON INSERT TO node
DO INSTEAD (
INSERT INTO node_basic ( type_nodetype, createtime ) VALUES ( 
NEW.type_nodetype, now() );

SELECT insert_node_relation( currval('node_basic_node_id_seq'), 
NEW.author_user, 'author_user' );

INSERT INTO node_title (title, node_id ) SELECT NEW.title, 
currval('node_basic_node_id_seq') WHERE NEW.title IS NOT NULL;

INSERT INTO node_statistics_hits (hits, node_id )  SELECT NEW.hits, 
currval('node_basic_node_id_seq') WHERE NEW.hits IS NOT NULL;

SELECT insert_node_relation ( currval('node_basic_node_id_seq'), 
NEW.loc_location, 'loc_location' );

INSERT INTO node_statistics_reputation (reputation, node_id ) SELECT 
NEW.reputation, currval('node_basic_node_id_seq') WHERE NEW.reputation 
IS NOT NULL;

INSERT INTO node_lock (locktime, lockby_user, node_id ) SELECT 
NEW.locktime, NEW.lockby_user, currval('node_basic_node_id_seq') WHERE 
NEW.locktime IS NOT NULL;

SELECT insert_node_relation( currval('node_basic_node_id_seq'), 
NEW.group_usergroup, 'group_usergroup' );

SELECT insert_permissions(  currval('node_basic_node_id_seq'), 'author', 
NEW.authoraccess );

SELECT insert_permissions(  currval('node_basic_node_id_seq'), 'group', 
NEW.groupaccess );

SELECT insert_permissions(  currval('node_basic_node_id_seq'), 'other', 
NEW.otheraccess );

SELECT insert_permissions(  currval('node_basic_node_id_seq'), 'guest', 
NEW.guestaccess );

SELECT insert_dynamic_permission( NEW.node_id, 
NEW.dynamicauthor_permission, 'author' );
SELECT insert_dynamic_permission( NEW.node_id, 
NEW.dynamicauthor_permission, 'group' );
SELECT insert_dynamic_permission( NEW.node_id, 
NEW.dynamicauthor_permission, 'other' );
SELECT insert_dynamic_permission( NEW.node_id, 
NEW.dynamicauthor_permission, 'guest' );

)
;

CREATE FUNCTION update_dynamicpermission (bigint, text, bigint ) RETURNS 
VOID AS $$
-- node_id, author type, permission_id
DELETE FROM node_relationship_usertype WHERE node = $1 AND relation_type 
= (SELECT node_relation_type_pk FROM node_relation_type WHERE 
node_relation_type.name = 'dynamic permission' ) AND user_type = ( 
SELECT user_type_pk FROM user_type WHERE usertype = $2 ) AND $3 IS NULL;
UPDATE node_relationship_usertype SET with_node = $3 WHERE node = $1 AND 
user_type = ( SELECT user_type_pk FROM user_type WHERE 
user_type.usertype = $2 ) AND relation_type =  ( SELECT 
node_relation_type_pk FROM node_relation_type WHERE 
node_relation_type.name = 'dynamic permission' ) AND $3 IS NOT NULL AND 
$3 <> 0;
INSERT INTO node_relationship_usertype (with_node, user_type, node, 
relation_type) SELECT $3, ( SELECT user_type_pk FROM user_type WHERE 
usertype = $2 ), $1, ( SELECT node_relation_type_pk FROM 
node_relation_type where name = 'dynamic permission') WHERE $3 IS NOT 
NULL AND $3 <> 0 AND NOT EXISTS ( SELECT * FROM 
node_relationship_usertype, user_type, node_relation_type WHERE 
node_relationship_usertype.node = $1 AND 
node_relationship_usertype.user_type = ( SELECT user_type.user_type_pk 
FROM user_type WHERE usertype = $2 ) AND 
node_relationship_usertype.relation_type = ( SELECT 
node_relation_type_pk from node_relation_type where name = 'dynamic 
permission'));


$$ LANGUAGE SQL;

;

-- First arg is the node_id from.  The second is the 'has-a' node.  The 
third is the relationship type.

CREATE FUNCTION update_node_relation (bigint, bigint, varchar ) RETURNS 
VOID AS $$

DELETE FROM node_relationship WHERE $1 = node_relationship.node AND 
node_relationship.relation_type = ( SELECT 
node_relation_type.node_relation_type_pk FROM node_relation_type WHERE 
node_relation_type.name = $3 ) AND $2 IS NULL;

  UPDATE node_relationship SET hasa_node = $2
   WHERE
        node_relationship.node =  $1
     AND $2 IS NOT NULL
     AND node_relationship.relation_type = ( SELECT node_relation_type_pk
            FROM node_relation_type
            WHERE node_relation_type.name = $3);


INSERT INTO node_relationship (hasa_node, node, relation_type )
   SELECT $2, $1, node_relation_type.node_relation_type_pk
     FROM node_relation_type
     WHERE node_relation_type.name = $3
      AND $2 IS NOT NULL
      AND
       NOT EXISTS (
        SELECT *
          FROM node_relationship, node_relation_type
          WHERE node_relationship.node = $1
           AND node_relationship.relation_type = 
node_relation_type.node_relation_type_pk
           AND node_relation_type.name = $3
        );


$$ LANGUAGE SQL;
;

CREATE RULE _update_node AS ON UPDATE TO node
DO INSTEAD (
UPDATE node_basic SET  type_nodetype =  NEW.type_nodetype WHERE 
NEW.type_nodetype <> OLD.type_nodetype AND NEW.node_id = node_basic.node_id;

SELECT update_node_relation( NEW.node_id, NEW.author_user, 'author_user' );

DELETE FROM node_title WHERE NEW.node_id = node_title.node_id AND 
NEW.title IS NULL;
UPDATE node_title SET title = NEW.title WHERE NEW.node_id = 
node_title.node_id AND NEW.title IS NOT NULL;
INSERT INTO node_title (title, node_id) SELECT NEW.title, NEW.node_id 
WHERE NEW.title IS NOT NULL AND NOT EXISTS ( SELECT * FROM node_title 
WHERE node_title.node_id = NEW.node_id );

DELETE FROM node_statistics_hits WHERE NEW.node_id = 
node_statistics_hits.node_id AND NEW.hits IS NULL;
UPDATE node_statistics_hits  SET hits =  NEW.hits  WHERE NEW.node_id = 
node_statistics_hits.node_id;
INSERT INTO node_statistics_hits (hits, node_id) SELECT NEW.hits, 
NEW.node_id WHERE NEW.hits IS NOT NULL AND NOT EXISTS ( SELECT * FROM 
node_statistics_hits WHERE node_statistics_hits.node_id = NEW.node_id );

SELECT update_node_relation( NEW.node_id, NEW.loc_location, 
'loc_location' );

DELETE FROM node_statistics_reputation WHERE NEW.node_id = 
node_statistics_reputation.node_id AND NEW.reputation IS NULL;
UPDATE node_statistics_reputation SET reputation =  NEW.reputation WHERE 
NEW.node_id = node_statistics_reputation.node_id AND NEW.reputation IS 
NOT NULL;
INSERT INTO node_statistics_reputation (reputation, node_id) SELECT 
NEW.reputation, NEW.node_id WHERE NEW.reputation IS NOT NULL AND NOT 
EXISTS ( SELECT * FROM node_statistics_reputation WHERE 
node_statistics_reputation.node_id = NEW.node_id );

DELETE FROM node_lock WHERE NEW.node_id = node_lock.node_id AND ( 
NEW.locktime IS NULL OR NEW.lockby_user IS NULL );
UPDATE node_lock SET locktime = NEW.locktime, lockby_user = 
NEW.lockby_user WHERE NEW.node_id = node_lock.node_id AND NEW.locktime 
IS NOT NULL AND NEW.lockby_user IS NOT NULL;
INSERT INTO node_lock (locktime, lockby_user, node_id) SELECT 
NEW.locktime, NEW.lockby_user, NEW.node_id WHERE NEW.locktime IS NOT 
NULL AND NEW.lockby_user IS NOT NULL AND NOT EXISTS ( SELECT * FROM 
node_lock WHERE node_lock.node_id = NEW.node_id );


SELECT update_node_relation( NEW.node_id, NEW.group_usergroup, 
'group_usergroup');

SELECT update_dynamicpermission( NEW.node_id, 'author', 
NEW.dynamicauthor_permission );
SELECT update_dynamicpermission( NEW.node_id, 'group', 
NEW.dynamicgroup_permission );
SELECT update_dynamicpermission( NEW.node_id, 'other', 
NEW.dynamicother_permission );
SELECT update_dynamicpermission( NEW.node_id, 'author', 
NEW.dynamicguest_permission );

SELECT update_permissions( NEW.node_id, 'author', NEW.authoraccess );
SELECT update_permissions( NEW.node_id, 'group', NEW.groupaccess );
SELECT update_permissions( NEW.node_id, 'other', NEW.otheraccess );
SELECT update_permissions( NEW.node_id, 'guest', NEW.guestaccess );

)
;

CREATE RULE _delete_node AS ON DELETE TO node
DO INSTEAD (

DELETE FROM node_relationship WHERE node_relationship.node = OLD.node_id;
DELETE FROM node_title WHERE node_id = OLD.node_id;
DELETE FROM node_statistics_hits WHERE node_id = OLD.node_id;
DELETE FROM node_location WHERE node_id = OLD.node_id;
DELETE FROM node_statistics_reputation WHERE node_id = OLD.node_id;
DELETE FROM node_lock WHERE node_id = OLD.node_id;
DELETE FROM node_access WHERE node_id = OLD.node_id;
DELETE FROM node_dynamicpermission WHERE node_id = OLD.node_id;
DELETE FROM node_basic WHERE node_id = OLD.node_id;

)
;

CREATE OR REPLACE FUNCTION split_on_comma(text) RETURNS SETOF varchar AS $$
DECLARE
  index INTEGER := 1;
  tablename varchar;
BEGIN

   IF $1 IS NULL THEN
     RETURN;
   END IF;

   LOOP
     tablename := split_part( $1, ',',index );
     IF tablename = '' THEN
        EXIT;
     END IF;
     index := index + 1;

     RETURN NEXT tablename;
   END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql

;

CREATE FUNCTION insert_sqltable_data( text, text, bigint ) RETURNS VOID 
AS $$
   DECLARE
    tablelist ALIAS FOR $1;
    tabletype ALIAS FOR $2;
    node_id ALIAS FOR $3;
    tablename varchar;
   BEGIN
    FOR tablename IN SELECT * FROM split_on_comma( tablelist ) LOOP
     INSERT INTO sqltable (sqltable_name, type ) SELECT tablename, 
sqltable_type_pk FROM sqltable_type WHERE sqltable_type.sqltable_type = 
tabletype AND NOT EXISTS ( SELECT * FROM sqltable WHERE 
sqltable.sqltable_name = tablename );

     INSERT INTO node_sqltable (nodetype_id, sqltable) SELECT node_id, 
sqltable_pk FROM sqltable WHERE sqltable_name = tablename AND NOT EXISTS 
( SELECT * FROM node_sqltable, sqltable_type, sqltable WHERE 
node_sqltable.nodetype_id = node_id AND node_sqltable.sqltable = 
sqltable.sqltable_pk and sqltable.type = sqltable_type.sqltable_type_pk 
and sqltable_type.sqltable_type = tabletype AND sqltable.sqltable_name = 
tablename);

    END LOOP;
   RETURN;
   END;
$$ LANGUAGE plpgsql
;

CREATE RULE _insert_nodetype AS ON INSERT TO nodetype DO INSTEAD (

   SELECT insert_node_relation( NEW.nodetype_id, NEW.restrict_nodetype, 
'restrict_nodetype');

   SELECT insert_node_relation( NEW.nodetype_id, NEW.extends_nodetype, 
'extends_nodetype') WHERE NEW.extends_nodetype <> 0;


   INSERT INTO node_authorisation ( node_id, permission_type, 
permission_behaviour )
    SELECT
     NEW.nodetype_id,
     permission_type.permission_type_pk,
     permission_behaviour.permission_behaviour_pk
     FROM permission_type, permission_behaviour
     WHERE permission_type.permission = 'restrictdupes'
     AND   permission_behaviour.behaviour = CASE NEW.restrictdupes WHEN 
'1' THEN 'enable' WHEN '0' THEN 'disable' WHEN '-1' THEN 'inherit' ELSE 
NULL END;

   SELECT insert_sqltable_data( NEW.sqltable, 'attributetable', 
NEW.nodetype_id );
   SELECT insert_sqltable_data( NEW.grouptable, 'grouptable', 
NEW.nodetype_id );

   SELECT insert_permissions( NEW.nodetype_id, 'defaultauthor', 
NEW.defaultauthoraccess );
   SELECT insert_permissions( NEW.nodetype_id, 'defaultgroup', 
NEW.defaultgroupaccess );
   SELECT insert_permissions( NEW.nodetype_id, 'defaultother', 
NEW.defaultotheraccess );
   SELECT insert_permissions( NEW.nodetype_id, 'defaultguest', 
NEW.defaultguestaccess );

SELECT insert_node_relation( NEW.nodetype_id, 
NEW.defaultgroup_usergroup, 'defaultgroup_usergroup' );

   SELECT insert_dynamic_permission ( NEW.nodetype_id, 
NEW.defaultauthor_permission, 'defaultauthor' );
   SELECT insert_dynamic_permission ( NEW.nodetype_id, 
NEW.defaultgroup_permission, 'defaultgroup' );
   SELECT insert_dynamic_permission ( NEW.nodetype_id, 
NEW.defaultother_permission, 'defaultother' );
   SELECT insert_dynamic_permission ( NEW.nodetype_id, 
NEW.defaultguest_permission, 'defaultguest' );

   INSERT INTO nodebase_node_revisions (node_id, maxrevisions) SELECT 
NEW.nodetype_id, NEW.maxrevisions WHERE NEW.maxrevisions IS NOT NULL;

   INSERT INTO node_authorisation ( node_id, permission_type, 
permission_behaviour )
    SELECT
     NEW.nodetype_id,
     permission_type.permission_type_pk,
     permission_behaviour.permission_behaviour_pk
     FROM permission_type, permission_behaviour
     WHERE permission_type.permission = 'canworkspace'
     AND   permission_behaviour.behaviour = CASE NEW.canworkspace WHEN 
'1' THEN 'enable' WHEN '0' THEN 'disable' WHEN '-1' THEN 'inherit' ELSE 
NULL END;

)
;

CREATE FUNCTION update_sqltable_data( text, text, bigint ) RETURNS VOID 
AS $$
   DECLARE
    tablelist ALIAS FOR $1;
    tabletype ALIAS FOR $2;
    node_id ALIAS FOR $3;
    tablename varchar;
   BEGIN

    DELETE FROM node_sqltable WHERE nodetype_id = node_id AND sqltable 
in ( SELECT sqltable_pk FROM sqltable, sqltable_type WHERE 
sqltable.type = sqltable_type_pk AND sqltable_type.sqltable_type = 
tabletype);
    PERFORM insert_sqltable_data( tablelist, tabletype, node_id );

   RETURN;

   END;
$$ LANGUAGE plpgsql
;

CREATE FUNCTION update_node_authorisation (bigint, integer, varchar ) 
RETURNS VOID AS $$

-- nodetype_id, authorisation, authorisation_type

DELETE FROM node_authorisation WHERE node_authorisation.node_id = $1 AND 
  node_authorisation.permission_type = ( SELECT 
permission_type.permission_type_pk FROM permission_type WHERE 
permission_type.permission = $3) AND $2 IS NULL;

UPDATE node_authorisation SET permission_behaviour = ( SELECT 
permission_behaviour_pk FROM permission_behaviour WHERE 
permission_behaviour.behaviour = CASE $2 WHEN '1' THEN 'enable' WHEN '0' 
THEN 'disable' WHEN '-1' THEN 'inherit' ELSE NULL END ) WHERE 
node_authorisation.node_id = $1  AND node_authorisation.permission_type 
= ( SELECT permission_type_pk FROM permission_type where 
permission_type.permission = $3 );

   INSERT INTO node_authorisation ( node_id, permission_type, 
permission_behaviour )
    SELECT
     $1,
     permission_type.permission_type_pk,
     permission_behaviour.permission_behaviour_pk
     FROM permission_type, permission_behaviour
     WHERE permission_type.permission = $3
     AND NOT EXISTS ( SELECT * FROM node_authorisation, permission_type 
WHERE node_authorisation.node_id = $1 AND 
node_authorisation.permission_type = permission_type.permission_type_pk 
AND permission_type.permission = $3)
     AND   permission_behaviour.behaviour = ( SELECT CASE $2 WHEN '1' 
THEN 'enable' WHEN '0' THEN 'disable' WHEN '-1' THEN 'inherit' ELSE NULL 
END );

$$ LANGUAGE SQL;
;

CREATE RULE _update_nodetype AS ON UPDATE to nodetype DO INSTEAD (

   SELECT update_node_relation( NEW.nodetype_id, NEW.restrict_nodetype, 
'restrict_nodetype') WHERE NEW.restrict_nodetype <> 0;

   SELECT update_node_relation( NEW.nodetype_id, NEW.extends_nodetype, 
'extends_nodetype') WHERE NEW.extends_nodetype <> 0;


   SELECT update_node_authorisation( NEW.nodetype_id, NEW.restrictdupes, 
'restrictdupes');

   SELECT update_sqltable_data( NEW.sqltable, 'attributetable', 
NEW.nodetype_id );
   SELECT update_sqltable_data( NEW.grouptable, 'grouptable', 
NEW.nodetype_id );


   SELECT update_permissions( NEW.nodetype_id, 'defaultauthor', 
NEW.defaultauthoraccess );
   SELECT update_permissions( NEW.nodetype_id, 'defaultgroup', 
NEW.defaultgroupaccess );
   SELECT update_permissions( NEW.nodetype_id, 'defaultother', 
NEW.defaultotheraccess );
   SELECT update_permissions( NEW.nodetype_id, 'defaultguest', 
NEW.defaultguestaccess );

SELECT update_node_relation( NEW.nodetype_id, 
NEW.defaultgroup_usergroup, 'defaultgroup_usergroup' ) WHERE 
NEW.defaultgroup_usergroup <> 0;

   SELECT update_dynamicpermission ( NEW.nodetype_id,  'defaultauthor', 
NEW.defaultauthor_permission );
   SELECT update_dynamicpermission ( NEW.nodetype_id, 'defaultgroup', 
NEW.defaultgroup_permission );
   SELECT update_dynamicpermission ( NEW.nodetype_id, 'defaultother', 
NEW.defaultother_permission );
   SELECT update_dynamicpermission ( NEW.nodetype_id, 'defaultguest', 
NEW.defaultguest_permission );

   DELETE FROM nodebase_node_revisions WHERE 
nodebase_node_revisions.node_id = NEW.nodetype_id AND NEW.maxrevisions 
IS NULL;
   UPDATE nodebase_node_revisions SET maxrevisions=NEW.maxrevisions 
WHERE NEW.maxrevisions IS NOT NULL AND nodebase_node_revisions.node_id = 
NEW.nodetype_id AND EXISTS (SELECT * FROM nodebase_node_revisions where 
nodebase_node_revisions.node_id = NEW.nodetype_id );
   INSERT INTO nodebase_node_revisions (node_id, maxrevisions ) SELECT 
NEW.nodetype_id, NEW.maxrevisions WHERE NEW.maxrevisions IS NOT NULL AND 
NOT EXISTS (SELECT * FROM nodebase_node_revisions WHERE 
nodebase_node_revisions.node_id = NEW.nodetype_id);

    SELECT update_node_authorisation ( NEW.nodetype_id, 
NEW.canworkspace, 'canworkspace' );

)
;
CREATE TABLE version (
   version_id INTEGER  PRIMARY KEY DEFAULT '0' NOT NULL,
   version INTEGER DEFAULT '1' NOT NULL
);
INSERT INTO sqltable_type ( sqltable_type, description) VALUES 
('attributetable', 'A table of this type contains node attribute data.') ;
INSERT INTO sqltable_type ( sqltable_type, description) VALUES 
('grouptable', 'A table of this type contains a list of nodes contained 
in a type.') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('author_user', 'the user node identified as author of a node') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('group_usergroup', 'the usergroup node associated with a node') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('defaultgroup_usergroup', 'the default usergroup node associated with a 
node') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('extends_nodetype', 'the nodetype that this node extends') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('type_nodetype', 'the nodetype node of a node') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('loc_location', 'the location node of a node') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('restrict_nodetype', 'for group nodes: restrict the nodes contained in 
this group to this type.') ;
INSERT INTO node_relation_type ( name, description ) VALUES ('dynamic 
permission', 'where access to a node is determined by a perimssion node.') ;
INSERT INTO node_relation_type ( name, description ) VALUES 
('permission', 'a dynamic permission node for the node') ;
INSERT INTO user_type (usertype, description) VALUES ('author', 'an 
author of a node');
INSERT INTO user_type (usertype, description) VALUES ('group', 'whether 
an author is a member of a group');
INSERT INTO user_type (usertype, description) VALUES ('other', 'a user 
is logged in but is neither in the relevant group not an author');
INSERT INTO user_type (usertype, description) VALUES ('guest', 'usually 
used to refer to an anonymous user or one that is not in any other 
category');
INSERT INTO user_type (usertype, description) VALUES ('defaultauthor', 
'what an author type defaults to.');
INSERT INTO user_type (usertype, description) VALUES ('defaultgroup', 
'what members of a group default to.');
INSERT INTO user_type (usertype, description) VALUES ('defaultother', 
'what other users default to.');
INSERT INTO user_type (usertype, description) VALUES ('defaultguest', 
'what guests default to.');
INSERT INTO permission_type (permission, description) VALUES ('read', 
'the permission to read an object');
INSERT INTO permission_type (permission, description) VALUES ('write', 
'the permission to write to an object');
INSERT INTO permission_type (permission, description) VALUES ('execute', 
'the permission to "execute" an object, that is, run any code in it');
INSERT INTO permission_type (permission, description) VALUES ('delete', 
'the permission to delete an object');
INSERT INTO permission_type (permission, description) VALUES ('create', 
'the permission to create an object');
INSERT INTO permission_type (permission, description) VALUES 
('canworkspace', 'whether the node can be workspaced');
INSERT INTO permission_type (permission, description) VALUES 
('restrictdupes', 'whether the nodebase allows creation of nodes with a 
similar name');
INSERT INTO permission_behaviour (behaviour, description) VALUES 
('enable', 'the permission for this object is enabled');
INSERT INTO permission_behaviour (behaviour, description) VALUES 
('disable', 'the permission for this object is disabled');
INSERT INTO permission_behaviour (behaviour, description) VALUES 
('inherit', 'the permission for this object is inherited according to 
some algorithm');
INSERT INTO node (node_id, type_nodetype, title, createtime, 
authoraccess, groupaccess, otheraccess, guestaccess ) VALUES 
(1,1,'nodetype',now(), 'iiii','rwxdc','-----','-----' );
INSERT INTO node (node_id, type_nodetype, title, createtime, 
authoraccess, groupaccess, otheraccess, guestaccess ) VALUES 
(2,1,'node',now(),'rwxd','-----','-----','-----');
INSERT INTO node (node_id, type_nodetype, title, createtime, 
authoraccess, groupaccess, otheraccess, guestaccess ) VALUES 
(3,1,'setting', now(),'rwxd','-----','-----','-----');
INSERT INTO nodetype (nodetype_id, restrict_nodetype, extends_nodetype, 
restrictdupes, sqltable, grouptable, defaultauthoraccess, 
defaultgroupaccess, defaultotheraccess, defaultguestaccess, 
defaultgroup_usergroup, defaultauthor_permission, 
defaultgroup_permission, defaultother_permission, 
defaultguest_permission, maxrevisions, canworkspace ) VALUES 
(1,NULL,2,1,'nodetype','','rwxd','rwxdc','-----','-----',NULL,NULL,NULL,NULL,NULL,-1,0);
INSERT INTO nodetype (nodetype_id, restrict_nodetype, extends_nodetype, 
restrictdupes, sqltable, grouptable, defaultauthoraccess, 
defaultgroupaccess, defaultotheraccess, defaultguestaccess, 
defaultgroup_usergroup, defaultauthor_permission, 
defaultgroup_permission, defaultother_permission, 
defaultguest_permission, maxrevisions, canworkspace ) VALUES 
(2,NULL,NULL,1,'','','rwxd','r----','-----','-----',NULL,NULL,NULL,NULL,NULL,1000,1);
INSERT INTO nodetype (nodetype_id, restrict_nodetype, extends_nodetype, 
restrictdupes, sqltable, grouptable, defaultauthoraccess, 
defaultgroupaccess, defaultotheraccess, defaultguestaccess, 
defaultgroup_usergroup, defaultauthor_permission, 
defaultgroup_permission, defaultother_permission, 
defaultguest_permission, maxrevisions, canworkspace ) VALUES 
(3,NULL,2,1,'setting','','rwxd','-----','-----','-----',NULL,NULL,NULL,NULL,NULL,-1,-1)