--
--  Copyright (C) 2003-2008 by CERN/IT/DS/HSM
--  All rights reserved
--
--       @(#)$RCSfile: Cns_postgresql_tbl.sql,v $ $Revision: 1.1 $ $Date: 2008/09/24 11:25:00 $ CERN IT-DS/HSM Jean-Philippe Baud
 
--     Create name server PostgreSQL tables.

CREATE DATABASE cns_db;
CREATE TABLE Cns_class_metadata (
       rowid BIGSERIAL PRIMARY KEY,
       classid INTEGER,
       name VARCHAR(15),
       owner_uid INTEGER,
       gid INTEGER,
       min_filesize INTEGER,
       max_filesize INTEGER,
       flags INTEGER,
       maxdrives INTEGER,
       max_segsize INTEGER,
       migr_time_interval INTEGER,
       mintime_beforemigr INTEGER,
       nbcopies INTEGER,
       nbdirs_using_class INTEGER,
       retenp_on_disk INTEGER);

CREATE TABLE Cns_file_metadata (
       rowid BIGSERIAL PRIMARY KEY,
       fileid BIGINT,
       parent_fileid BIGINT,
       guid CHAR(36),
       name VARCHAR(255),
       filemode INTEGER,
       nlink INTEGER,
       owner_uid INTEGER,
       gid INTEGER,
       filesize BIGINT,
       atime INTEGER,
       mtime INTEGER,
       ctime INTEGER,
       fileclass SMALLINT,
       status CHAR(1),
       csumtype VARCHAR(2),
       csumvalue VARCHAR(32),
       acl VARCHAR(3900));

CREATE TABLE Cns_user_metadata (
       rowid BIGSERIAL PRIMARY KEY,
       u_fileid BIGINT,
       comments VARCHAR(255));

CREATE TABLE Cns_symlinks (
       rowid BIGSERIAL PRIMARY KEY,
       fileid BIGINT,
       linkname VARCHAR(1023));

CREATE TABLE Cns_file_replica (
       rowid BIGSERIAL PRIMARY KEY,
       fileid BIGINT,
       nbaccesses BIGINT,
       ctime INTEGER,
       atime INTEGER,
       ptime INTEGER,
       ltime INTEGER,
       r_type CHAR(1),
       status CHAR(1),
       f_type CHAR(1),
       setname VARCHAR(36),
       poolname VARCHAR(15),
       host VARCHAR(63),
       fs VARCHAR(79),
       sfn VARCHAR(1103));

CREATE TABLE Cns_groupinfo (
       rowid BIGSERIAL PRIMARY KEY,
       gid INTEGER,
       groupname VARCHAR(255));

CREATE TABLE Cns_userinfo (
       rowid BIGSERIAL PRIMARY KEY,
       userid INTEGER,
       username VARCHAR(255));

CREATE SEQUENCE Cns_unique_id START 3;

CREATE TABLE Cns_unique_gid (
       id INTEGER);

CREATE TABLE Cns_unique_uid (
       id INTEGER);

ALTER TABLE Cns_class_metadata
       ADD CONSTRAINT pk_classid UNIQUE (classid);
ALTER TABLE Cns_class_metadata
       ADD CONSTRAINT classname UNIQUE (name);
ALTER TABLE Cns_file_metadata
       ADD CONSTRAINT pk_fileid UNIQUE (fileid);
ALTER TABLE Cns_file_metadata
       ADD CONSTRAINT file_full_id UNIQUE (parent_fileid, name);
ALTER TABLE Cns_file_metadata
       ADD CONSTRAINT file_guid UNIQUE (guid);
ALTER TABLE Cns_user_metadata
       ADD CONSTRAINT pk_u_fileid UNIQUE (u_fileid);
ALTER TABLE Cns_symlinks
       ADD CONSTRAINT pk_l_fileid UNIQUE (fileid);
ALTER TABLE Cns_file_replica
       ADD CONSTRAINT pk_repl_sfn UNIQUE (sfn);
ALTER TABLE Cns_groupinfo
       ADD CONSTRAINT pk_map_groupname UNIQUE (groupname);
ALTER TABLE Cns_userinfo
       ADD CONSTRAINT pk_map_username UNIQUE (username);

ALTER TABLE Cns_user_metadata
       ADD CONSTRAINT fk_u_fileid FOREIGN KEY (u_fileid) REFERENCES Cns_file_metadata(fileid);
ALTER TABLE Cns_symlinks
       ADD CONSTRAINT fk_l_fileid FOREIGN KEY (fileid) REFERENCES Cns_file_metadata(fileid);
ALTER TABLE Cns_file_replica
       ADD CONSTRAINT fk_r_fileid FOREIGN KEY (fileid) REFERENCES Cns_file_metadata(fileid);

CREATE INDEX parent_fileid_idx ON Cns_file_metadata (parent_fileid);
CREATE INDEX replica_host ON Cns_file_replica (host);
CREATE INDEX replica_id ON Cns_file_replica (fileid);

-- Create the "schema_version" table

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

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