--
--  Copyright (C) 2004-2008 by CERN/IT/GD/CT
--  All rights reserved
--
--       @(#)$RCSfile: dpm_postgresql_tbl.sql,v $ $Revision: 1.1 $ $Date: 2008/09/24 11:25:00 $ CERN IT-GD/CT Jean-Philippe Baud
 
--     Create Disk Pool Manager PostgreSQL tables.

CREATE DATABASE dpm_db;
CREATE TABLE dpm_pool (
       rowid BIGSERIAL PRIMARY KEY,
       poolname VARCHAR(15),
       defsize BIGINT,
       gc_start_thresh INTEGER,
       gc_stop_thresh INTEGER,
       def_lifetime INTEGER,
       defpintime INTEGER,
       max_lifetime INTEGER,
       maxpintime INTEGER,
       fss_policy VARCHAR(15),
       gc_policy VARCHAR(15),
       mig_policy VARCHAR(15),
       rs_policy VARCHAR(15),
       groups VARCHAR(255),
       ret_policy CHAR(1),
       s_type CHAR(1));

CREATE TABLE dpm_fs (
       rowid BIGSERIAL PRIMARY KEY,
       poolname VARCHAR(15),
       server VARCHAR(63),
       fs VARCHAR(79),
       status INTEGER);

CREATE TABLE dpm_pending_req (
       rowid BIGSERIAL PRIMARY KEY,
       r_ordinal INTEGER,
       r_token VARCHAR(36),
       r_uid INTEGER,
       r_gid INTEGER,
       client_dn VARCHAR(255),
       clienthost VARCHAR(63),
       r_type CHAR(1),
       u_token VARCHAR(255),
       flags INTEGER,
       retrytime INTEGER,
       nbreqfiles INTEGER,
       ctime INTEGER,
       stime INTEGER,
       etime INTEGER,
       status INTEGER,
       errstring VARCHAR(255),
       groups VARCHAR(255));

CREATE TABLE dpm_req (
       rowid BIGSERIAL PRIMARY KEY,
       r_ordinal INTEGER,
       r_token VARCHAR(36),
       r_uid INTEGER,
       r_gid INTEGER,
       client_dn VARCHAR(255),
       clienthost VARCHAR(63),
       r_type CHAR(1),
       u_token VARCHAR(255),
       flags INTEGER,
       retrytime INTEGER,
       nbreqfiles INTEGER,
       ctime INTEGER,
       stime INTEGER,
       etime INTEGER,
       status INTEGER,
       errstring VARCHAR(255),
       groups VARCHAR(255));

CREATE TABLE dpm_get_filereq (
       rowid BIGSERIAL PRIMARY KEY,
       r_token VARCHAR(36),
       f_ordinal INTEGER,
       r_uid INTEGER,
       from_surl VARCHAR(1103),
       protocol VARCHAR(7),
       lifetime INTEGER,
       f_type CHAR(1),
       s_token CHAR(36),
       ret_policy CHAR(1),
       flags INTEGER,
       server VARCHAR(63),
       pfn VARCHAR(1103),
       actual_size BIGINT,
       status INTEGER,
       errstring VARCHAR(255));

CREATE TABLE dpm_put_filereq (
       rowid BIGSERIAL PRIMARY KEY,
       r_token VARCHAR(36),
       f_ordinal INTEGER,
       to_surl VARCHAR(1103),
       protocol VARCHAR(7),
       lifetime INTEGER,
       f_lifetime INTEGER,
       f_type CHAR(1),
       s_token CHAR(36),
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       requested_size BIGINT,
       server VARCHAR(63),
       pfn VARCHAR(1103),
       actual_size BIGINT,
       status INTEGER,
       errstring VARCHAR(255));

CREATE TABLE dpm_copy_filereq (
       rowid BIGSERIAL PRIMARY KEY,
       r_token VARCHAR(36),
       f_ordinal INTEGER,
       from_surl VARCHAR(1103),
       to_surl VARCHAR(1103),
       f_lifetime INTEGER,
       f_type CHAR(1),
       s_token CHAR(36),
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       flags INTEGER,
       actual_size BIGINT,
       status INTEGER,
       errstring VARCHAR(255));

CREATE TABLE dpm_space_reserv (
       rowid BIGSERIAL PRIMARY KEY,
       s_token CHAR(36),
       client_dn VARCHAR(255),
       s_uid INTEGER,
       s_gid INTEGER,
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       s_type CHAR(1),
       u_token VARCHAR(255),
       t_space BIGINT,
       g_space BIGINT,
       u_space BIGINT,
       poolname VARCHAR(15),
       assign_time INTEGER,
       expire_time INTEGER,
       groups VARCHAR(255));

CREATE SEQUENCE dpm_unique_id START 1;

ALTER TABLE dpm_pool
       ADD CONSTRAINT pk_poolname UNIQUE (poolname);
ALTER TABLE dpm_fs
       ADD CONSTRAINT pk_fs UNIQUE (poolname, server, fs);
ALTER TABLE dpm_pending_req
       ADD CONSTRAINT pk_p_token UNIQUE (r_token);
ALTER TABLE dpm_req
       ADD CONSTRAINT pk_token UNIQUE (r_token);
ALTER TABLE dpm_get_filereq
       ADD CONSTRAINT pk_g_fullid UNIQUE (r_token, f_ordinal);
ALTER TABLE dpm_put_filereq
       ADD CONSTRAINT pk_p_fullid UNIQUE (r_token, f_ordinal);
ALTER TABLE dpm_copy_filereq
       ADD CONSTRAINT pk_c_fullid UNIQUE (r_token, f_ordinal);
ALTER TABLE dpm_space_reserv
       ADD CONSTRAINT pk_s_token UNIQUE (s_token);

ALTER TABLE dpm_fs
       ADD CONSTRAINT fk_fs FOREIGN KEY (poolname) REFERENCES dpm_pool(poolname);

CREATE INDEX P_U_DESC_IDX ON dpm_pending_req (u_token);
CREATE INDEX U_DESC_IDX ON dpm_req (u_token);
CREATE INDEX G_SURL_IDX ON dpm_get_filereq (FROM_SURL);
CREATE INDEX P_SURL_IDX ON dpm_put_filereq (TO_SURL);
CREATE INDEX CF_SURL_IDX ON dpm_copy_filereq (FROM_SURL);    
CREATE INDEX CT_SURL_IDX ON dpm_copy_filereq (TO_SURL);
CREATE INDEX G_PFN_IDX ON dpm_get_filereq (pfn);
CREATE INDEX P_PFN_IDX ON dpm_put_filereq (pfn);

-- Create the "schema_version" table

CREATE TABLE schema_version_dpm (
       major INTEGER NOT NULL,
       minor INTEGER NOT NULL,
       patch INTEGER NOT NULL);

INSERT INTO schema_version_dpm (major, minor, patch) VALUES (3, 2, 0);
