TracMultipleProjects/MultipleEnvironmentsSingleDatabase: trac_ddl.sql

CREATE TABLE master_attachment (
    dbuser text NOT NULL,
    "type" text NOT NULL,
    id text NOT NULL,
    filename text NOT NULL,
    size integer,
    "time" integer,
    description text,
    author text,
    ipnr text
);

CREATE TABLE master_auth_cookie (
    dbuser text NOT NULL,
    cookie text NOT NULL,
    name text NOT NULL,
    ipnr text NOT NULL,
    "time" integer
);

CREATE TABLE master_component (
    dbuser text NOT NULL,
    name text NOT NULL,
    "owner" text,
    description text
);

CREATE TABLE master_enum (
    dbuser text NOT NULL,
    "type" text NOT NULL,
    name text NOT NULL,
    value text
);

CREATE TABLE master_milestone (
    dbuser text NOT NULL,
    name text NOT NULL,
    due integer,
    completed integer,
    description text
);

CREATE TABLE master_node_change (
    dbuser text NOT NULL,
    rev text NOT NULL,
    path text NOT NULL,
    kind text,
    change text NOT NULL,
    base_path text,
    base_rev text
);

CREATE TABLE master_permission (
    dbuser text NOT NULL,
    username text NOT NULL,
    "action" text NOT NULL
);

-- the sequence must be created manually to be named correctly
CREATE SEQUENCE report_id_seq;
CREATE TABLE master_report (
    dbuser text NOT NULL,
    id integer DEFAULT nextval('report_id_seq') NOT NULL,
    author text,
    title text,
    sql text,
    description text
);

CREATE TABLE master_revision (
    dbuser text NOT NULL,
    rev text NOT NULL,
    "time" integer,
    author text,
    message text
);

CREATE TABLE "master_session" (
    dbuser text NOT NULL,
    sid text NOT NULL,
    authenticated integer NOT NULL,
    var_name text NOT NULL,
    var_value text
);

CREATE TABLE "master_system" (
    dbuser text NOT NULL,
    name text NOT NULL,
    value text
);

-- create manually to ensure correct naming
CREATE SEQUENCE ticket_id_seq;
CREATE TABLE master_ticket (
    dbuser text NOT NULL,
    id integer DEFAULT nextval('ticket_id_seq') NOT NULL,
    "type" text,
    "time" integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    "owner" text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);

CREATE TABLE master_ticket_change (
    dbuser text NOT NULL,
    ticket integer NOT NULL,
    "time" integer NOT NULL,
    author text,
    field text NOT NULL,
    oldvalue text,
    newvalue text
);

CREATE TABLE master_ticket_custom (
    dbuser text NOT NULL,
    ticket integer NOT NULL,
    name text NOT NULL,
    value text
);

CREATE TABLE master_version (
    dbuser text NOT NULL,
    name text NOT NULL,
    "time" integer,
    description text
);

CREATE TABLE master_wiki (
    dbuser text NOT NULL,
    name text NOT NULL,
    version integer NOT NULL,
    "time" integer,
    author text,
    ipnr text,
    text text,
    "comment" text,
    readonly integer
);

ALTER TABLE ONLY master_attachment
    ADD CONSTRAINT attachment_pk PRIMARY KEY (dbuser, "type", id, filename);

ALTER TABLE ONLY master_auth_cookie
    ADD CONSTRAINT auth_cookie_pk PRIMARY KEY (dbuser, cookie, ipnr, name);

ALTER TABLE ONLY master_component
    ADD CONSTRAINT component_pkey PRIMARY KEY (dbuser, name);

ALTER TABLE ONLY master_enum
    ADD CONSTRAINT enum_pk PRIMARY KEY (dbuser, "type", name);

ALTER TABLE ONLY master_milestone
    ADD CONSTRAINT milestone_pkey PRIMARY KEY (dbuser, name);

ALTER TABLE ONLY master_node_change
    ADD CONSTRAINT node_change_pk PRIMARY KEY (dbuser, rev, path, change);

ALTER TABLE ONLY master_permission
    ADD CONSTRAINT permission_pk PRIMARY KEY (dbuser, username, "action");

ALTER TABLE ONLY master_report
    ADD CONSTRAINT report_pkey PRIMARY KEY (dbuser, id);

ALTER TABLE ONLY master_revision
    ADD CONSTRAINT revision_pkey PRIMARY KEY (dbuser, rev);

ALTER TABLE ONLY "master_session"
    ADD CONSTRAINT session_pk PRIMARY KEY (dbuser, sid, authenticated, var_name);

ALTER TABLE ONLY "master_system"
    ADD CONSTRAINT system_pkey PRIMARY KEY (dbuser, name);

ALTER TABLE ONLY master_ticket_change
    ADD CONSTRAINT ticket_change_pk PRIMARY KEY (dbuser, ticket, "time", field);

ALTER TABLE ONLY master_ticket_custom
    ADD CONSTRAINT ticket_custom_pk PRIMARY KEY (dbuser, ticket, name);

ALTER TABLE ONLY master_ticket
    ADD CONSTRAINT ticket_pkey PRIMARY KEY (dbuser, id);

ALTER TABLE ONLY master_version
    ADD CONSTRAINT version_pkey PRIMARY KEY (dbuser, name);

ALTER TABLE ONLY master_wiki
    ADD CONSTRAINT wiki_pk PRIMARY KEY (dbuser, name, version);

CREATE INDEX node_change_rev_idx ON master_node_change USING btree (dbuser, rev);

CREATE INDEX revision_time_idx ON master_revision USING btree (dbuser, "time");

CREATE INDEX ticket_change_ticket_time_idx ON master_ticket_change USING btree (dbuser, ticket, "time");

CREATE INDEX ticket_status_idx ON master_ticket USING btree (dbuser, status);

CREATE INDEX ticket_time_idx ON master_ticket USING btree (dbuser, "time");

CREATE INDEX wiki_time_idx ON master_wiki USING btree (dbuser, "time");

-- attachment view
CREATE VIEW attachment AS
    SELECT
        "type",
        id,
        filename,
        size,
        "time",
        description,
        author,
        ipnr
    FROM master_attachment
    WHERE
        dbuser = session_user;

CREATE RULE attachment_ins AS ON INSERT TO attachment
    DO INSTEAD
    INSERT INTO master_attachment VALUES (
        session_user,
        NEW."type",
        NEW.id,
        NEW.filename,
        NEW.size,
        NEW."time",
        NEW.description,
        NEW.author,
        NEW.ipnr
    );

CREATE RULE attachment_upd AS ON UPDATE TO attachment
    DO INSTEAD
    UPDATE master_attachment
    SET
        "type" = NEW."type",
        id = NEW.id,
        filename = NEW.filename,
        size = NEW.size,
        "time" = NEW."time",
        description = NEW.description,
        author = NEW.author,
        ipnr = NEW.ipnr
    WHERE
        dbuser = session_user AND
        "type" = OLD."type" AND
        id = OLD.id AND
        filename = OLD.filename;

CREATE RULE attachment_del AS ON DELETE TO attachment
    DO INSTEAD
    DELETE FROM master_attachment
    WHERE 
        dbuser = session_user AND
        "type" = OLD."type" AND
        id = OLD.id AND
        filename = OLD.filename;


-- auth_cookie view
CREATE VIEW auth_cookie AS
    SELECT
        cookie,
        name,
        ipnr,
        "time"
    FROM master_auth_cookie
    WHERE
        dbuser = session_user;

CREATE RULE auth_cookie_ins AS ON INSERT TO auth_cookie
    DO INSTEAD
    INSERT INTO master_auth_cookie VALUES (
        session_user,
        NEW.cookie,
        NEW.name,
        NEW.ipnr,
        NEW."time"
    );

CREATE RULE auth_cookie_upd AS ON UPDATE TO auth_cookie
    DO INSTEAD
    UPDATE master_auth_cookie
    SET
        cookie = NEW.cookie,
        name = NEW.name,
        ipnr = NEW.ipnr,
        "time" = NEW."time"
    WHERE
        dbuser = session_user AND
        cookie = OLD.cookie AND
        ipnr = OLD.ipnr AND
        name = OLD.name;

CREATE RULE auth_cookie_del AS ON DELETE TO auth_cookie
    DO INSTEAD
    DELETE FROM master_auth_cookie
    WHERE
        dbuser = session_user AND
        cookie = OLD.cookie AND
        ipnr = OLD.ipnr AND
        name = OLD.name;
        

-- component view
CREATE VIEW component AS
    SELECT
        name,
        "owner",
        description
    FROM master_component
    WHERE
        dbuser = session_user;

CREATE RULE component_ins AS ON INSERT TO component
    DO INSTEAD
    INSERT INTO master_component VALUES (
        session_user,
        NEW.name,
        NEW."owner",
        NEW.description
    );

CREATE RULE component_upd AS ON UPDATE TO component
    DO INSTEAD
    UPDATE master_component
    SET
        name = NEW.name,
        "owner" = NEW."owner",
        description = NEW.description
    WHERE
        dbuser = session_user AND
        name = OLD.name;

CREATE RULE component_del AS ON DELETE TO component
    DO INSTEAD
    DELETE FROM master_component
    WHERE
        dbuser = session_user AND
        name = OLD.name;
        
        
-- enum view
CREATE VIEW enum AS
    SELECT
        "type",
        name,
        value
    FROM master_enum
    WHERE
        dbuser = session_user;

CREATE RULE enum_ins AS ON INSERT TO enum
    DO INSTEAD
    INSERT INTO master_enum VALUES (
        session_user,
        NEW."type",
        NEW.name,
        NEW.value
    );

CREATE RULE enum_upd AS ON UPDATE TO enum
    DO INSTEAD
    UPDATE master_enum
    SET
        "type" = NEW."type",
        name = NEW.name,
        value = NEW.value
    WHERE
        dbuser = session_user AND
        "type" = OLD."type" AND
        name = OLD.name;

CREATE RULE enum_del AS ON DELETE TO enum
    DO INSTEAD
    DELETE FROM master_enum
    WHERE
        dbuser = session_user AND
        "type" = OLD."type" AND
        name = OLD.name;
        

-- milestone view
CREATE VIEW milestone AS
    SELECT
        name,
        due,
        completed,
        description
    FROM master_milestone
    WHERE
        dbuser = session_user;

CREATE RULE milestone_ins AS ON INSERT TO milestone
    DO INSTEAD
    INSERT INTO master_milestone VALUES (
        session_user,
        NEW.name,
        NEW.due,
        NEW.completed,
        NEW.description
    );

CREATE RULE milestone_upd AS ON UPDATE TO milestone
    DO INSTEAD
    UPDATE master_milestone
    SET
        name = NEW.name,
        due = NEW.due,
        completed = NEW.completed,
        description = NEW.description
    WHERE
        dbuser = session_user AND
        name = OLD.name;

CREATE RULE milestone_del AS ON DELETE TO milestone
    DO INSTEAD
    DELETE FROM master_milestone
    WHERE
        dbuser = session_user AND
        name = OLD.name;
        

-- node_change view
CREATE VIEW node_change AS
    SELECT
        rev,
        path,
        kind,
        change,
        base_path,
        base_rev
    FROM master_node_change
    WHERE
        dbuser = session_user;

CREATE RULE node_change_ins AS ON INSERT TO node_change
    DO INSTEAD
    INSERT INTO master_node_change VALUES (
        session_user,
        NEW.rev,
        NEW.path,
        NEW.kind,
        NEW.change,
        NEW.base_path,
        NEW.base_rev
    );

CREATE RULE node_change_upd AS ON UPDATE TO node_change
    DO INSTEAD
    UPDATE master_node_change
    SET
        rev = NEW.rev,
        path = NEW.path,
        kind = NEW.kind,
        change = NEW.change,
        base_path = NEW.base_path,
        base_rev = NEW.base_rev
    WHERE
        dbuser = session_user AND
        rev = OLD.rev AND
        path = OLD.path AND
        change = OLD.change;

CREATE RULE node_change_del AS ON DELETE TO node_change
    DO INSTEAD
    DELETE FROM master_node_change
    WHERE
        dbuser = session_user AND
        rev = OLD.rev AND
        path = OLD.path AND
        change = OLD.change;
        

-- permission view
CREATE VIEW permission AS
    SELECT
        username,
        "action"
    FROM master_permission
    WHERE
        dbuser = session_user;

CREATE RULE permission_ins AS ON INSERT TO permission
    DO INSTEAD
    INSERT INTO master_permission VALUES (
        session_user,
        NEW.username,
        NEW."action"
    );

CREATE RULE permission_upd AS ON UPDATE TO permission
    DO INSTEAD
    UPDATE master_permission
    SET
        username = NEW.username,
        "action" = NEW."action"
    WHERE
        dbuser = session_user AND
        username = OLD.username AND
        "action" = OLD."action";

CREATE RULE permission_del AS ON DELETE TO permission
    DO INSTEAD
    DELETE FROM master_permission
    WHERE
        dbuser = session_user AND
        username = OLD.username AND
        "action" = OLD."action";


-- report view
CREATE VIEW report AS
    SELECT
        id,
        author,
        title,
        sql,
        description
    FROM master_report
    WHERE
        dbuser = session_user;

CREATE RULE report_ins AS ON INSERT TO report
    DO INSTEAD
    INSERT INTO master_report VALUES (
        session_user,
        DEFAULT,
        NEW.author,
        NEW.title,
        NEW.sql,
        NEW.description
    );

CREATE RULE report_upd AS ON UPDATE TO report
    DO INSTEAD
    UPDATE master_report
    SET
        id = NEW.id,
        author = NEW.author,
        title = NEW.title,
        sql = NEW.sql,
        description = NEW.description
    WHERE
        dbuser = session_user AND
        id = OLD.id;

CREATE RULE report_del AS ON DELETE TO report
    DO INSTEAD
    DELETE FROM master_report
    WHERE
        dbuser = session_user AND
        id = OLD.id;


-- revision view
CREATE VIEW revision AS
    SELECT
        rev,
        "time",
        author,
        message
    FROM master_revision
    WHERE
        dbuser = session_user;

CREATE RULE revision_ins AS ON INSERT TO revision
    DO INSTEAD
    INSERT INTO master_revision VALUES (
        session_user,
        NEW.rev,
        NEW."time",
        NEW.author,
        NEW.message
    );

CREATE RULE revision_upd AS ON UPDATE TO revision
    DO INSTEAD
    UPDATE master_revision
    SET
        rev = NEW.rev,
        "time" = NEW."time",
        author = NEW.author,
        message = NEW.message
    WHERE
        dbuser = session_user AND
        rev = OLD.rev;

CREATE RULE revision_del AS ON DELETE TO revision
    DO INSTEAD
    DELETE FROM master_revision
    WHERE
        dbuser = session_user AND
        rev = OLD.rev;


-- session view
CREATE VIEW "session" AS
    SELECT
        sid,
        authenticated,
        var_name,
        var_value
    FROM master_session
    WHERE
        dbuser = session_user;

CREATE RULE session_ins AS ON INSERT TO "session"
    DO INSTEAD
    INSERT INTO master_session VALUES (
        session_user,
        NEW.sid,
        NEW.authenticated,
        NEW.var_name,
        NEW.var_value
    );

CREATE RULE session_upd AS ON UPDATE TO "session"
    DO INSTEAD
    UPDATE master_session
    SET
        sid = NEW.sid,
        authenticated = NEW.authenticated,
        var_name = NEW.var_name,
        var_value = NEW.var_value
    WHERE
        dbuser = session_user AND
        sid = OLD.sid AND
        authenticated = OLD.authenticated AND
        var_name = OLD.var_name;

CREATE RULE session_del AS ON DELETE TO "session"
    DO INSTEAD
    DELETE FROM master_session
    WHERE
        dbuser = session_user AND
        sid = OLD.sid AND
        authenticated = OLD.authenticated AND
        var_name = OLD.var_name;


-- system view
CREATE VIEW "system" AS
    SELECT
        name,
        value
    FROM master_system
    WHERE
        dbuser = session_user;

CREATE RULE system_ins AS ON INSERT TO "system"
    DO INSTEAD
    INSERT INTO master_system VALUES (
        session_user,
        NEW.name,
        NEW.value
    );

CREATE RULE system_upd AS ON UPDATE TO "system"
    DO INSTEAD
    UPDATE master_system
    SET
        name = NEW.name,
        value = NEW.value
    WHERE
        dbuser = session_user AND
        name = OLD.name;

CREATE RULE system_del AS ON DELETE TO "system"
    DO INSTEAD
    DELETE FROM master_system
    WHERE
        dbuser = session_user AND
        name = OLD.name;


-- ticket view
CREATE VIEW ticket AS
    SELECT
        id,
        "type",
        "time",
        changetime,
        component,
        severity,
        priority,
        "owner",
        reporter,
        cc,
        version,
        milestone,
        status,
        resolution,
        summary,
        description,
        keywords
    FROM master_ticket
    WHERE
        dbuser = session_user;

CREATE RULE ticket_ins AS ON INSERT TO ticket
    DO INSTEAD
    INSERT INTO master_ticket VALUES (
        session_user,
        DEFAULT,
        NEW."type",
        NEW."time",
        NEW.changetime,
        NEW.component,
        NEW.severity,
        NEW.priority,
        NEW."owner",
        NEW.reporter,
        NEW.cc,
        NEW.version,
        NEW.milestone,
        NEW.status,
        NEW.resolution,
        NEW.summary,
        NEW.description,
        NEW.keywords
    );

CREATE RULE ticket_upd AS ON UPDATE TO ticket
    DO INSTEAD
    UPDATE master_ticket
    SET
        id = NEW.id,
        "type" = NEW."type",
        "time" = NEW."time",
        changetime = NEW.changetime,
        component = NEW.component,
        severity = NEW.severity,
        priority = NEW.priority,
        "owner" = NEW."owner",
        reporter = NEW.reporter,
        cc = NEW.cc,
        version = NEW.version,
        milestone = NEW.milestone,
        status = NEW.status,
        resolution = NEW.resolution,
        summary = NEW.summary,
        description = NEW.description,
        keywords = NEW.keywords
    WHERE
        dbuser = session_user AND
        id = OLD.id;

CREATE RULE ticket_del AS ON DELETE TO ticket
    DO INSTEAD
    DELETE FROM master_ticket
    WHERE
        dbuser = session_user AND
        id = OLD.id;


-- ticket_change view
CREATE VIEW ticket_change AS
    SELECT
        ticket,
        "time",
        author,
        field,
        oldvalue,
        newvalue
    FROM master_ticket_change
    WHERE
        dbuser = session_user;

CREATE RULE ticket_change_ins AS ON INSERT TO ticket_change
    DO INSTEAD
    INSERT INTO master_ticket_change VALUES (
        session_user,
        NEW.ticket,
        NEW."time",
        NEW.author,
        NEW.field,
        NEW.oldvalue,
        NEW.newvalue
    );

CREATE RULE ticket_change_upd AS ON UPDATE TO ticket_change
    DO INSTEAD
    UPDATE master_ticket_change
    SET
        ticket = NEW.ticket,
        "time" = NEW."time",
        author = NEW.author,
        field = NEW.field,
        oldvalue = NEW.oldvalue,
        newvalue = NEW.newvalue
    WHERE
        dbuser = session_user AND
        ticket = OLD.ticket AND
        "time" = OLD."time" AND
        field = OLD.field;

CREATE RULE ticket_change_del AS ON DELETE TO ticket_change
    DO INSTEAD
    DELETE FROM master_ticket_change
    WHERE
        dbuser = session_user AND
        ticket = OLD.ticket AND
        "time" = OLD."time" AND
        field = OLD.field;


-- ticket_custom view
CREATE VIEW ticket_custom AS
    SELECT
        ticket,
        name,
        value
    FROM master_ticket_custom
    WHERE
        dbuser = session_user;

CREATE RULE ticket_custom_ins AS ON INSERT TO ticket_custom
    DO INSTEAD
    INSERT INTO master_ticket_custom VALUES (
        session_user,
        NEW.ticket,
        NEW.name,
        NEW.value
    );

CREATE RULE ticket_custom_upd AS ON UPDATE TO ticket_custom
    DO INSTEAD
    UPDATE master_ticket_custom
    SET
        ticket = NEW.ticket,
        name = NEW.name,
        value = NEW.value
    WHERE
        dbuser = session_user AND
        ticket = OLD.ticket AND
        name = OLD.name;

CREATE RULE ticket_custom_del AS ON DELETE TO ticket_custom
    DO INSTEAD
    DELETE FROM master_ticket_custom
    WHERE
        dbuser = session_user AND
        ticket = OLD.ticket AND
        name = OLD.name;


-- version view
CREATE VIEW version AS
    SELECT
        name,
        "time",
        description
    FROM master_version
    WHERE
        dbuser = session_user;

CREATE RULE version_ins AS ON INSERT TO version
    DO INSTEAD
    INSERT INTO master_version VALUES (
        session_user,
        NEW.name,
        NEW."time",
        NEW.description
    );