SVNManager Postgresql DB Init.sql

CREATE TABLE usertickets (
    email varchar(128) NOT NULL default '',
    ticket varchar(32) NOT NULL default '',
    repositorygrants integer NOT NULL default '0'
);
CREATE TABLE usersgroups (
    userid integer default '0',
    groupid integer default '0'
);
CREATE TABLE users (
    id serial primary key,
    name varchar(32) NOT NULL default '',
    password varchar(32) NOT NULL default '',
    email varchar(128) NOT NULL default '',
    admin integer NOT NULL default '0',
    repositorygrants integer NOT NULL default '0',
    svnserve_password varchar(32) NOT NULL default ''
);
CREATE TABLE userprivileges (
    id serial primary key,
    userid integer NOT NULL default '0',
    repositoryid integer NOT NULL default '0',
    access integer NOT NULL default '0',
    path varchar(255) NOT NULL default ''                     
); 
CREATE TABLE svnserve_pwd (
    ownerid serial primary key,                 
    password varchar(32) NOT NULL default ''
);            
CREATE TABLE groups (
    id serial primary key,
    name varchar(32) NOT NULL default '',
    adminid integer NOT NULL default '0'
);
CREATE TABLE groupprivileges (
    id serial PRIMARY KEY,
    groupid INTEGER NOT NULL DEFAULT '0',
    repositoryid INTEGER NOT NULL DEFAULT '0',
    access integer NOT NULL DEFAULT '0',
    path VARCHAR(255) NOT NULL DEFAULT ''
);
CREATE TABLE repositories (
    id serial  primary key,
    name varchar(32) NOT NULL default '',
    ownerid integer NOT NULL default '0',
    description varchar(128) NOT NULL default ''
);            
CREATE TABLE repo_descriptions (
    id serial  primary key,
    repo_id INTEGER NOT NULL DEFAULT '0',
    description varchar(128) NOT NULL default ''
);            




DROP FUNCTION IF EXISTS trgfn_ins_users_id() CASCADE;
DROP FUNCTION IF EXISTS trgfn_ins_userprivileges_id() CASCADE;
DROP FUNCTION IF EXISTS trgfn_ins_svnserve_pwd_id() CASCADE;
DROP FUNCTION IF EXISTS trgfn_ins_groups_id() CASCADE;
DROP FUNCTION IF EXISTS trgfn_ins_groupprivileges_id() CASCADE;
DROP FUNCTION IF EXISTS trgfn_ins_repositories_id() CASCADE;
DROP FUNCTION IF EXISTS trgfn_ins_repo_descriptions_id() CASCADE;

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_users_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('users_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_users_id
BEFORE INSERT ON users
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_users_id();

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_userprivileges_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('userprivileges_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_userprivileges_id
BEFORE INSERT ON userprivileges
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_userprivileges_id();

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_svnserve_pwd_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('svnserve_pwd_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_svnserve_pwd_id
BEFORE INSERT ON svnserve_pwd
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_svnserve_pwd_id();

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_groups_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('groups_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_groups_id
BEFORE INSERT ON groups
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_groups_id();

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_groupprivileges_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('groupprivileges_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_groupprivileges_id
BEFORE INSERT ON groupprivileges
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_groupprivileges_id();

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_repositories_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('repositories_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_repositories_id
BEFORE INSERT ON repositories
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_repositories_id();

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION trgfn_ins_repo_descriptions_id() RETURNS TRIGGER AS $EOF$
BEGIN
    IF NEW.id IS NULL THEN 
        NEW.id = nextval('repo_descriptions_id_seq');
    END IF;
    RETURN NEW;
END;
$EOF$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_repo_descriptions_id
BEFORE INSERT ON repo_descriptions
FOR EACH ROW 
EXECUTE PROCEDURE trgfn_ins_repo_descriptions_id();

------------------------------------------------------------------------------------------

-- ##!! you will need to modify this: /prado-2.0.3/framework/Data/adodb/drivers/adodb-postgres64.inc.php
-- ##!! function _query($sql,$inputarr)
-- ##!! add this line to line #668
-- ##!!   $sql = str_replace("''", "'0'", $sql);

pear install channel://pear.php.net/VersionControl_SVN-0.5.1