1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
--
-- Copyright (C) 1999-2007 by CERN/IT/PDP/DM
-- All rights reserved
--
-- @(#)$RCSfile: Cns_oracle_tbl.sql,v $ $Revision: 1.17 $ $Date: 2007/03/26 15:41:24 $ CERN IT-PDP/DM Jean-Philippe Baud
-- Create name server Oracle tables.
CREATE TABLE Cns_class_metadata (
classid NUMBER(5),
name VARCHAR2(15),
owner_uid NUMBER(6),
gid NUMBER(6),
min_filesize NUMBER,
max_filesize NUMBER,
flags NUMBER(2),
maxdrives NUMBER(3),
max_segsize NUMBER,
migr_time_interval NUMBER,
mintime_beforemigr NUMBER,
nbcopies NUMBER(1),
nbdirs_using_class NUMBER,
retenp_on_disk NUMBER);
CREATE TABLE Cns_file_metadata (
fileid NUMBER,
parent_fileid NUMBER,
guid CHAR(36),
name VARCHAR2(255),
filemode NUMBER(6),
nlink NUMBER(6),
owner_uid NUMBER(6),
gid NUMBER(6),
filesize NUMBER,
atime NUMBER(10),
mtime NUMBER(10),
ctime NUMBER(10),
fileclass NUMBER(5),
status CHAR(1),
csumtype VARCHAR2(2),
csumvalue VARCHAR2(32),
acl VARCHAR2(3900))
STORAGE (INITIAL 5M NEXT 5M PCTINCREASE 0);
CREATE TABLE Cns_user_metadata (
u_fileid NUMBER,
comments VARCHAR2(255));
CREATE TABLE Cns_symlinks (
fileid NUMBER,
linkname VARCHAR2(1023));
CREATE TABLE Cns_file_replica (
fileid NUMBER,
nbaccesses NUMBER,
ctime NUMBER(10),
atime NUMBER(10),
ptime NUMBER(10),
ltime NUMBER(10),
r_type CHAR(1),
status CHAR(1),
f_type CHAR(1),
setname VARCHAR2(36),
poolname VARCHAR2(15),
host VARCHAR2(63),
fs VARCHAR2(79),
sfn VARCHAR2(1103));
CREATE TABLE Cns_groupinfo (
gid NUMBER(10),
groupname VARCHAR2(255));
CREATE TABLE Cns_userinfo (
userid NUMBER(10),
username VARCHAR2(255));
CREATE SEQUENCE Cns_unique_id START WITH 3 INCREMENT BY 1;
CREATE TABLE Cns_unique_gid (
id NUMBER(10));
CREATE TABLE Cns_unique_uid (
id NUMBER(10));
ALTER TABLE Cns_class_metadata
ADD CONSTRAINT pk_classid PRIMARY KEY (classid)
ADD CONSTRAINT classname UNIQUE (name);
ALTER TABLE Cns_file_metadata
ADD CONSTRAINT pk_fileid PRIMARY KEY (fileid)
ADD CONSTRAINT file_full_id UNIQUE (parent_fileid, name)
ADD CONSTRAINT file_guid UNIQUE (guid)
USING INDEX STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0);
ALTER TABLE Cns_user_metadata
ADD CONSTRAINT pk_u_fileid PRIMARY KEY (u_fileid);
ALTER TABLE Cns_symlinks
ADD CONSTRAINT pk_l_fileid PRIMARY KEY (fileid);
ALTER TABLE Cns_file_replica
ADD CONSTRAINT pk_repl_sfn PRIMARY KEY (sfn);
ALTER TABLE Cns_groupinfo
ADD CONSTRAINT pk_map_groupname PRIMARY KEY (groupname);
ALTER TABLE Cns_userinfo
ADD CONSTRAINT pk_map_username PRIMARY KEY (username);
CREATE INDEX replica_id ON Cns_file_replica(fileid);
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 the "schema_version" table
CREATE TABLE schema_version (major NUMBER(1), minor NUMBER(1), patch NUMBER(1));
INSERT INTO schema_version VALUES (3, 0, 0);
|
