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

CREATE TABLE dpm_pool (
       poolname VARCHAR2(15),
       defsize NUMBER,
       gc_start_thresh NUMBER(3),
       gc_stop_thresh NUMBER(3),
       def_lifetime NUMBER(10),
       defpintime NUMBER(10),
       max_lifetime NUMBER(10),
       maxpintime NUMBER(10),
       fss_policy VARCHAR2(15),
       gc_policy VARCHAR2(15),
       mig_policy VARCHAR2(15),
       rs_policy VARCHAR2(15),
       groups VARCHAR2(255),
       ret_policy CHAR(1),
       s_type CHAR(1));

CREATE TABLE dpm_fs (
       poolname VARCHAR2(15),
       server VARCHAR2(63),
       fs VARCHAR2(79),
       status NUMBER(10));

CREATE TABLE dpm_pending_req (
       r_ordinal NUMBER(10),
       r_token VARCHAR2(36),
       r_uid NUMBER(10),
       r_gid NUMBER(10),
       client_dn VARCHAR2(255),
       clienthost VARCHAR2(63),
       r_type CHAR(1),
       u_token VARCHAR2(255),
       flags NUMBER,
       retrytime NUMBER(10),
       nbreqfiles NUMBER(10),
       ctime NUMBER(10),
       stime NUMBER(10),
       etime NUMBER(10),
       status NUMBER(10),
       errstring VARCHAR2(255),
       groups VARCHAR2(255));

CREATE TABLE dpm_req (
       r_ordinal NUMBER(10),
       r_token VARCHAR2(36),
       r_uid NUMBER(10),
       r_gid NUMBER(10),
       client_dn VARCHAR2(255),
       clienthost VARCHAR2(63),
       r_type CHAR(1),
       u_token VARCHAR2(255),
       flags NUMBER,
       retrytime NUMBER(10),
       nbreqfiles NUMBER(10),
       ctime NUMBER(10),
       stime NUMBER(10),
       etime NUMBER(10),
       status NUMBER(10),
       errstring VARCHAR2(255),
       groups VARCHAR2(255));

CREATE TABLE dpm_get_filereq (
       r_token VARCHAR2(36),
       f_ordinal NUMBER(10),
       r_uid NUMBER(10),
       from_surl VARCHAR2(1103),
       protocol VARCHAR2(7),
       lifetime NUMBER(10),
       f_type CHAR(1),
       s_token CHAR(36),
       ret_policy CHAR(1),
       flags NUMBER,
       server VARCHAR2(63),
       pfn VARCHAR2(1103),
       actual_size NUMBER,
       status NUMBER(10),
       errstring VARCHAR2(255));

CREATE TABLE dpm_put_filereq (
       r_token VARCHAR2(36),
       f_ordinal NUMBER(10),
       to_surl VARCHAR2(1103),
       protocol VARCHAR2(7),
       lifetime NUMBER(10),
       f_lifetime NUMBER(10),
       f_type CHAR(1),
       s_token CHAR(36),
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       requested_size NUMBER,
       server VARCHAR2(63),
       pfn VARCHAR2(1103),
       actual_size NUMBER,
       status NUMBER(10),
       errstring VARCHAR2(255));

CREATE TABLE dpm_copy_filereq (
       r_token VARCHAR2(36),
       f_ordinal NUMBER(10),
       from_surl VARCHAR2(1103),
       to_surl VARCHAR2(1103),
       f_lifetime NUMBER(10),
       f_type CHAR(1),
       s_token CHAR(36),
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       flags NUMBER,
       actual_size NUMBER,
       status NUMBER(10),
       errstring VARCHAR2(255));

CREATE TABLE dpm_space_reserv (
       s_token CHAR(36),
       client_dn VARCHAR2(255),
       s_uid NUMBER(10),
       s_gid NUMBER(10),
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       s_type CHAR(1),
       u_token VARCHAR2(255),
       t_space NUMBER,
       g_space NUMBER,
       u_space NUMBER,
       poolname VARCHAR2(15),
       assign_time NUMBER(10),
       expire_time NUMBER(10),
       groups VARCHAR2(255));

CREATE SEQUENCE dpm_unique_id START WITH 1 INCREMENT BY 1;

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

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 NUMBER(1), minor NUMBER(1), patch NUMBER(1));
INSERT INTO schema_version_dpm VALUES (3, 2, 0);
