common_catalogue_schema.sql 25.5 KB
Newer Older
1
CREATE TABLE CTA_CATALOGUE(
2
  SCHEMA_VERSION_MAJOR    UINT64TYPE      CONSTRAINT CTA_CATALOGUE_SVM1_NN NOT NULL,
3
4
5
  SCHEMA_VERSION_MINOR    UINT64TYPE      CONSTRAINT CTA_CATALOGUE_SVM2_NN NOT NULL,
  NEXT_SCHEMA_VERSION_MAJOR UINT64TYPE,
  NEXT_SCHEMA_VERSION_MINOR UINT64TYPE,
6
7
8
  STATUS                  VARCHAR(100),
  IS_PRODUCTION           CHAR(1)         DEFAULT '0' CONSTRAINT CTA_CATALOGUE_IP_NN NOT NULL,
  CONSTRAINT CTA_CATALOGUE_IP_BOOL_CK     CHECK(IS_PRODUCTION IN ('0','1'))
9
);
10
CREATE TABLE ADMIN_USER(
11
12
13
14
  ADMIN_USER_NAME         VARCHAR(100)    CONSTRAINT ADMIN_USER_AUN_NN  NOT NULL,
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT ADMIN_USER_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT ADMIN_USER_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT ADMIN_USER_CLHN_NN NOT NULL,
15
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT ADMIN_USER_CLT_NN  NOT NULL,
16
17
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT ADMIN_USER_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT ADMIN_USER_LUHN_NN NOT NULL,
18
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT ADMIN_USER_LUT_NN  NOT NULL,
Steven Murray's avatar
Steven Murray committed
19
  CONSTRAINT ADMIN_USER_PK PRIMARY KEY(ADMIN_USER_NAME)
20
);
21
22
23
24
CREATE TABLE DISK_SYSTEM(
  DISK_SYSTEM_NAME        VARCHAR(100)    CONSTRAINT DISK_SYSTEM_DSNM_NN NOT NULL,
  FILE_REGEXP             VARCHAR(100)    CONSTRAINT DISK_SYSTEM_FR_NN   NOT NULL,
  FREE_SPACE_QUERY_URL    VARCHAR(1000)   CONSTRAINT DISK_SYSTEM_FSQU_NN NOT NULL,
25
26
27
  REFRESH_INTERVAL        UINT64TYPE      CONSTRAINT DISK_SYSTEM_RI_NN   NOT NULL,
  TARGETED_FREE_SPACE     UINT64TYPE      CONSTRAINT DISK_SYSTEM_TFS_NN  NOT NULL,
  SLEEP_TIME              UINT64TYPE      CONSTRAINT DISK_SYSTEM_ST_NN   NOT NULL,
28
29
30
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT DISK_SYSTEM_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT DISK_SYSTEM_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT DISK_SYSTEM_CLHN_NN NOT NULL,
31
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT DISK_SYSTEM_CLT_NN  NOT NULL,
32
33
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT DISK_SYSTEM_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT DISK_SYSTEM_LUHN_NN NOT NULL,
34
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT DISK_SYSTEM_LUT_NN  NOT NULL,
35
36
  CONSTRAINT NAME_PK PRIMARY KEY(DISK_SYSTEM_NAME)
);
37
38
39
CREATE TABLE VIRTUAL_ORGANIZATION(
  VIRTUAL_ORGANIZATION_ID UINT64TYPE      CONSTRAINT VIRTUAL_ORGANIZATION_VOI_NN  NOT NULL,
  VIRTUAL_ORGANIZATION_NAME VARCHAR(100)  CONSTRAINT VIRTUAL_ORGANIZATION_VON_NN  NOT NULL,
40
41
42
43
44
45
46
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT VIRTUAL_ORGANIZATION_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT VIRTUAL_ORGANIZATION_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT VIRTUAL_ORGANIZATION_CLHN_NN NOT NULL,
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT VIRTUAL_ORGANIZATION_CLT_NN  NOT NULL,
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT VIRTUAL_ORGANIZATION_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT VIRTUAL_ORGANIZATION_LUHN_NN NOT NULL,
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT VIRTUAL_ORGANIZATION_LUT_NN  NOT NULL,
47
48
49
  CONSTRAINT VIRTUAL_ORGANIZATION_PK PRIMARY KEY(VIRTUAL_ORGANIZATION_ID),
  CONSTRAINT VIRTUAL_ORGANIZATION_VON_UN UNIQUE(VIRTUAL_ORGANIZATION_NAME)
);
50
CREATE TABLE STORAGE_CLASS(
51
  STORAGE_CLASS_ID        UINT64TYPE      CONSTRAINT STORAGE_CLASS_SCI_NN  NOT NULL,
52
  STORAGE_CLASS_NAME      VARCHAR(100)    CONSTRAINT STORAGE_CLASS_SCN_NN  NOT NULL,
53
  NB_COPIES               UINT8TYPE       CONSTRAINT STORAGE_CLASS_NC_NN   NOT NULL,
54
  VIRTUAL_ORGANIZATION_ID UINT64TYPE      CONSTRAINT STORAGE_CLASS_VOI_NN  NOT NULL,  
55
56
57
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT STORAGE_CLASS_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT STORAGE_CLASS_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT STORAGE_CLASS_CLHN_NN NOT NULL,
58
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT STORAGE_CLASS_CLT_NN  NOT NULL,
59
60
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT STORAGE_CLASS_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT STORAGE_CLASS_LUHN_NN NOT NULL,
61
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT STORAGE_CLASS_LUT_NN  NOT NULL,
62
  CONSTRAINT STORAGE_CLASS_PK PRIMARY KEY(STORAGE_CLASS_ID),
63
64
  CONSTRAINT STORAGE_CLASS_SCN_UN UNIQUE(STORAGE_CLASS_NAME),
  CONSTRAINT STORAGE_CLASS_VOI_FK FOREIGN KEY(VIRTUAL_ORGANIZATION_ID) REFERENCES VIRTUAL_ORGANIZATION(VIRTUAL_ORGANIZATION_ID)
65
66
);
CREATE TABLE TAPE_POOL(
67
  TAPE_POOL_ID            UINT64TYPE      CONSTRAINT TAPE_POOL_TPI_NN  NOT NULL,
68
  TAPE_POOL_NAME          VARCHAR(100)    CONSTRAINT TAPE_POOL_TPN_NN  NOT NULL,
69
  VIRTUAL_ORGANIZATION_ID UINT64TYPE      CONSTRAINT TAPE_POOL_VOI_NN  NOT NULL,
70
  NB_PARTIAL_TAPES        UINT64TYPE      CONSTRAINT TAPE_POOL_NPT_NN  NOT NULL,
71
  IS_ENCRYPTED            CHAR(1)         CONSTRAINT TAPE_POOL_IE_NN   NOT NULL,
72
  SUPPLY                  VARCHAR(100),
73
74
75
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT TAPE_POOL_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT TAPE_POOL_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT TAPE_POOL_CLHN_NN NOT NULL,
76
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT TAPE_POOL_CLT_NN  NOT NULL,
77
78
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT TAPE_POOL_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT TAPE_POOL_LUHN_NN NOT NULL,
79
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT TAPE_POOL_LUT_NN  NOT NULL,
80
81
  CONSTRAINT TAPE_POOL_PK PRIMARY KEY(TAPE_POOL_ID),
  CONSTRAINT TAPE_POOL_TPN_UN UNIQUE(TAPE_POOL_NAME),
82
83
  CONSTRAINT TAPE_POOL_IS_ENCRYPTED_BOOL_CK CHECK(IS_ENCRYPTED IN ('0', '1')),
  CONSTRAINT TAPE_POOL_VO_FK FOREIGN KEY(VIRTUAL_ORGANIZATION_ID) REFERENCES VIRTUAL_ORGANIZATION(VIRTUAL_ORGANIZATION_ID)
84
85
);
CREATE TABLE ARCHIVE_ROUTE(
86
  STORAGE_CLASS_ID        UINT64TYPE      CONSTRAINT ARCHIVE_ROUTE_SCI_NN  NOT NULL,
87
  COPY_NB                 UINT8TYPE       CONSTRAINT ARCHIVE_ROUTE_CN_NN   NOT NULL,
88
  TAPE_POOL_ID            UINT64TYPE      CONSTRAINT ARCHIVE_ROUTE_TPI_NN  NOT NULL,
89
90
91
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT ARCHIVE_ROUTE_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT ARCHIVE_ROUTE_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT ARCHIVE_ROUTE_CLHN_NN NOT NULL,
92
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT ARCHIVE_ROUTE_CLT_NN  NOT NULL,
93
94
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT ARCHIVE_ROUTE_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT ARCHIVE_ROUTE_LUHN_NN NOT NULL,
95
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT ARCHIVE_ROUTE_LUT_NN  NOT NULL,
96
  CONSTRAINT ARCHIVE_ROUTE_PK PRIMARY KEY(STORAGE_CLASS_ID, COPY_NB),
97
  CONSTRAINT ARCHIVE_ROUTE_STORAGE_CLASS_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID),
98
  CONSTRAINT ARCHIVE_ROUTE_TAPE_POOL_FK FOREIGN KEY(TAPE_POOL_ID) REFERENCES TAPE_POOL(TAPE_POOL_ID),
99
  CONSTRAINT ARCHIVE_ROUTE_COPY_NB_GT_0_CK CHECK(COPY_NB > 0),
100
  CONSTRAINT ARCHIVE_ROUTE_SCI_TPI_UN UNIQUE(STORAGE_CLASS_ID, TAPE_POOL_ID)
101
);
102
103
104
105
106
CREATE TABLE MEDIA_TYPE(
  MEDIA_TYPE_ID          UINT64TYPE    CONSTRAINT MEDIA_TYPE_MTI_NN  NOT NULL,
  MEDIA_TYPE_NAME        VARCHAR(100)  CONSTRAINT MEDIA_TYPE_MTN_NN  NOT NULL,
  CARTRIDGE              VARCHAR(100)  CONSTRAINT MEDIA_TYPE_C_NN    NOT NULL,
  CAPACITY_IN_BYTES      UINT64TYPE    CONSTRAINT MEDIA_TYPE_CIB_NN  NOT NULL,
107
108
  PRIMARY_DENSITY_CODE   UINT8TYPE,
  SECONDARY_DENSITY_CODE UINT8TYPE,
109
110
111
112
113
114
115
116
117
118
119
120
121
  NB_WRAPS               UINT32TYPE,
  MIN_LPOS               UINT64TYPE,
  MAX_LPOS               UINT64TYPE,
  USER_COMMENT           VARCHAR(1000) CONSTRAINT MEDIA_TYPE_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME VARCHAR(100)  CONSTRAINT MEDIA_TYPE_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME VARCHAR(100)  CONSTRAINT MEDIA_TYPE_CLHN_NN NOT NULL,
  CREATION_LOG_TIME      UINT64TYPE    CONSTRAINT MEDIA_TYPE_CLT_NN  NOT NULL,
  LAST_UPDATE_USER_NAME  VARCHAR(100)  CONSTRAINT MEDIA_TYPE_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME  VARCHAR(100)  CONSTRAINT MEDIA_TYPE_LUHN_NN NOT NULL,
  LAST_UPDATE_TIME       UINT64TYPE    CONSTRAINT MEDIA_TYPE_LUT_NN  NOT NULL,
  CONSTRAINT MEDIA_TYPE_PK PRIMARY KEY(MEDIA_TYPE_ID),
  CONSTRAINT MEDIA_TYPE_MTN_UN UNIQUE(MEDIA_TYPE_NAME)
);
122
CREATE TABLE LOGICAL_LIBRARY(
123
  LOGICAL_LIBRARY_ID      UINT64TYPE      CONSTRAINT LOGICAL_LIBRARY_LLI_NN  NOT NULL,
124
  LOGICAL_LIBRARY_NAME    VARCHAR(100)    CONSTRAINT LOGICAL_LIBRARY_LLN_NN  NOT NULL,
125
  IS_DISABLED             CHAR(1)         DEFAULT '0' CONSTRAINT LOGICAL_LIBRARY_ID_NN NOT NULL,
126
127
128
  USER_COMMENT            VARCHAR(1000)   CONSTRAINT LOGICAL_LIBRARY_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT LOGICAL_LIBRARY_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT LOGICAL_LIBRARY_CLHN_NN NOT NULL,
129
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT LOGICAL_LIBRARY_CLT_NN  NOT NULL,
130
131
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT LOGICAL_LIBRARY_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT LOGICAL_LIBRARY_LUHN_NN NOT NULL,
132
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT LOGICAL_LIBRARY_LUT_NN  NOT NULL,
133
  CONSTRAINT LOGICAL_LIBRARY_PK PRIMARY KEY(LOGICAL_LIBRARY_ID),
134
  CONSTRAINT LOGICAL_LIBRARY_LLN_UN UNIQUE(LOGICAL_LIBRARY_NAME),
135
  CONSTRAINT LOGICAL_LIBRARY_ID_BOOL_CK CHECK(IS_DISABLED IN ('0', '1'))
136
137
);
CREATE TABLE TAPE(
138
  VID                     VARCHAR(100)    CONSTRAINT TAPE_V_NN    NOT NULL,
139
  MEDIA_TYPE_ID           UINT64TYPE      CONSTRAINT TAPE_MTID_NN   NOT NULL,
140
  VENDOR                  VARCHAR(100)    CONSTRAINT TAPE_V2_NN   NOT NULL,
141
  LOGICAL_LIBRARY_ID      UINT64TYPE      CONSTRAINT TAPE_LLI_NN  NOT NULL,
142
  TAPE_POOL_ID            UINT64TYPE      CONSTRAINT TAPE_TPI_NN  NOT NULL,
143
  ENCRYPTION_KEY_NAME     VARCHAR(100),
144
145
  DATA_IN_BYTES           UINT64TYPE      CONSTRAINT TAPE_DIB_NN  NOT NULL,
  LAST_FSEQ               UINT64TYPE      CONSTRAINT TAPE_LF_NN   NOT NULL,
146
147
  NB_MASTER_FILES         UINT64TYPE      DEFAULT 0 CONSTRAINT TAPE_NB_MASTER_FILES_NN NOT NULL,
  MASTER_DATA_IN_BYTES    UINT64TYPE      DEFAULT 0 CONSTRAINT TAPE_MASTER_DATA_IN_BYTES_NN NOT NULL,
148
149
  IS_DISABLED             CHAR(1)         CONSTRAINT TAPE_ID_NN   NOT NULL,
  IS_FULL                 CHAR(1)         CONSTRAINT TAPE_IF_NN   NOT NULL,
150
  IS_READ_ONLY            CHAR(1)         CONSTRAINT TAPE_IRO_NN  NOT NULL,
151
  IS_FROM_CASTOR          CHAR(1)         CONSTRAINT TAPE_IFC_NN  NOT NULL,
152
153
  IS_ARCHIVED             CHAR(1)         DEFAULT '0' CONSTRAINT TAPE_IA_NN   NOT NULL,
  IS_EXPORTED             CHAR(1)         DEFAULT '0' CONSTRAINT TAPE_IE_NN   NOT NULL,
154
  DIRTY                   CHAR(1)         DEFAULT '1' CONSTRAINT TAPE_DIRTY_NN NOT NULL,
155
156
157
158
  NB_COPY_NB_1            UINT64TYPE     DEFAULT 0 CONSTRAINT TAPE_NB_COPY_NB_1_NN NOT NULL,
  COPY_NB_1_IN_BYTES      UINT64TYPE     DEFAULT 0 CONSTRAINT TAPE_COPY_NB_1_IN_BYTES_NN NOT NULL,
  NB_COPY_NB_GT_1         UINT64TYPE     DEFAULT 0 CONSTRAINT TAPE_NB_COPY_NB_GT_1_NN NOT NULL,
  COPY_NB_GT_1_IN_BYTES   UINT64TYPE     DEFAULT 0 CONSTRAINT TAPE_COPY_NB_GT_1_IN_BYTES_NN NOT NULL,
159
  LABEL_DRIVE             VARCHAR(100),
160
  LABEL_TIME              UINT64TYPE    ,
161
  LAST_READ_DRIVE         VARCHAR(100),
162
  LAST_READ_TIME          UINT64TYPE    ,
163
  LAST_WRITE_DRIVE        VARCHAR(100),
164
165
166
  LAST_WRITE_TIME         UINT64TYPE    ,
  READ_MOUNT_COUNT        UINT64TYPE      DEFAULT 0 CONSTRAINT TAPE_RMC_NN NOT NULL,
  WRITE_MOUNT_COUNT       UINT64TYPE      DEFAULT 0 CONSTRAINT TAPE_WMC_NN NOT NULL,
167
  USER_COMMENT            VARCHAR(1000),
168
169
170
171
  TAPE_STATE              VARCHAR(100),
  STATE_REASON            VARCHAR(1000),
  STATE_UPDATE_TIME       UINT64TYPE,
  STATE_MODIFIED_BY       VARCHAR(100),
172
173
  CREATION_LOG_USER_NAME  VARCHAR(100)    CONSTRAINT TAPE_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME  VARCHAR(100)    CONSTRAINT TAPE_CLHN_NN NOT NULL,
174
  CREATION_LOG_TIME       UINT64TYPE      CONSTRAINT TAPE_CLT_NN  NOT NULL,
175
176
  LAST_UPDATE_USER_NAME   VARCHAR(100)    CONSTRAINT TAPE_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME   VARCHAR(100)    CONSTRAINT TAPE_LUHN_NN NOT NULL,
177
  LAST_UPDATE_TIME        UINT64TYPE      CONSTRAINT TAPE_LUT_NN  NOT NULL,
Steven Murray's avatar
Steven Murray committed
178
  CONSTRAINT TAPE_PK PRIMARY KEY(VID),
179
  CONSTRAINT TAPE_LOGICAL_LIBRARY_FK FOREIGN KEY(LOGICAL_LIBRARY_ID) REFERENCES LOGICAL_LIBRARY(LOGICAL_LIBRARY_ID),
180
  CONSTRAINT TAPE_TAPE_POOL_FK FOREIGN KEY(TAPE_POOL_ID) REFERENCES TAPE_POOL(TAPE_POOL_ID),
181
  CONSTRAINT TAPE_IS_DISABLED_BOOL_CK CHECK(IS_DISABLED IN ('0', '1')),
182
  CONSTRAINT TAPE_IS_FULL_BOOL_CK CHECK(IS_FULL IN ('0', '1')),
183
  CONSTRAINT TAPE_IS_READ_ONLY_BOOL_CK CHECK(IS_READ_ONLY IN ('0', '1')),
184
185
  CONSTRAINT TAPE_IS_FROM_CASTOR_BOOL_CK CHECK(IS_FROM_CASTOR IN ('0', '1')),
  CONSTRAINT TAPE_IS_ARCHVIED_BOOL_CK CHECK(IS_ARCHIVED IN ('0', '1')),
186
  CONSTRAINT TAPE_IS_EXPORTED_BOOL_CK CHECK(IS_EXPORTED IN ('0', '1')),
187
  CONSTRAINT TAPE_DIRTY_BOOL_CK CHECK(DIRTY IN ('0','1')),
188
  CONSTRAINT TAPE_STATE_CK CHECK(TAPE_STATE IN ('ACTIVE', 'DISABLED', 'BROKEN')),
189
  CONSTRAINT TAPE_MEDIA_TYPE_FK FOREIGN KEY(MEDIA_TYPE_ID) REFERENCES MEDIA_TYPE(MEDIA_TYPE_ID)
190
);
191
CREATE INDEX TAPE_TAPE_POOL_ID_IDX ON TAPE(TAPE_POOL_ID);
192
CREATE INDEX TAPE_IS_DISABLED_IDX ON TAPE(IS_DISABLED);
193
CREATE TABLE MOUNT_POLICY(
194
  MOUNT_POLICY_NAME        VARCHAR(100)    CONSTRAINT MOUNT_POLICY_MPN_NN  NOT NULL,
195
196
197
198
199
  ARCHIVE_PRIORITY         UINT64TYPE      CONSTRAINT MOUNT_POLICY_AP_NN   NOT NULL,
  ARCHIVE_MIN_REQUEST_AGE  UINT64TYPE      CONSTRAINT MOUNT_POLICY_AMRA_NN NOT NULL,
  RETRIEVE_PRIORITY        UINT64TYPE      CONSTRAINT MOUNT_POLICY_RP_NN   NOT NULL,
  RETRIEVE_MIN_REQUEST_AGE UINT64TYPE      CONSTRAINT MOUNT_POLICY_RMRA_NN NOT NULL,
  MAX_DRIVES_ALLOWED       UINT64TYPE      CONSTRAINT MOUNT_POLICY_MDA_NN  NOT NULL,
200
201
202
  USER_COMMENT             VARCHAR(1000)   CONSTRAINT MOUNT_POLICY_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME   VARCHAR(100)    CONSTRAINT MOUNT_POLICY_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME   VARCHAR(100)    CONSTRAINT MOUNT_POLICY_CLHN_NN NOT NULL,
203
  CREATION_LOG_TIME        UINT64TYPE      CONSTRAINT MOUNT_POLICY_CLT_NN  NOT NULL,
204
205
  LAST_UPDATE_USER_NAME    VARCHAR(100)    CONSTRAINT MOUNT_POLICY_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME    VARCHAR(100)    CONSTRAINT MOUNT_POLICY_LUHN_NN NOT NULL,
206
  LAST_UPDATE_TIME         UINT64TYPE      CONSTRAINT MOUNT_POLICY_LUT_NN  NOT NULL,
207
  CONSTRAINT MOUNT_POLICY_PK PRIMARY KEY(MOUNT_POLICY_NAME)
208
);
209
CREATE TABLE REQUESTER_MOUNT_RULE(
210
211
212
213
214
215
  DISK_INSTANCE_NAME     VARCHAR(100)    CONSTRAINT RQSTER_RULE_DIN_NN  NOT NULL,
  REQUESTER_NAME         VARCHAR(100)    CONSTRAINT RQSTER_RULE_RN_NN   NOT NULL,
  MOUNT_POLICY_NAME      VARCHAR(100)    CONSTRAINT RQSTER_RULE_MPN_NN  NOT NULL,
  USER_COMMENT           VARCHAR(1000)   CONSTRAINT RQSTER_RULE_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME VARCHAR(100)    CONSTRAINT RQSTER_RULE_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME VARCHAR(100)    CONSTRAINT RQSTER_RULE_CLHN_NN NOT NULL,
216
  CREATION_LOG_TIME      UINT64TYPE      CONSTRAINT RQSTER_RULE_CLT_NN  NOT NULL,
217
218
  LAST_UPDATE_USER_NAME  VARCHAR(100)    CONSTRAINT RQSTER_RULE_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME  VARCHAR(100)    CONSTRAINT RQSTER_RULE_LUHN_NN NOT NULL,
219
  LAST_UPDATE_TIME       UINT64TYPE      CONSTRAINT RQSTER_RULE_LUT_NN  NOT NULL,
220
  CONSTRAINT RQSTER_RULE_PK PRIMARY KEY(DISK_INSTANCE_NAME, REQUESTER_NAME),
221
  CONSTRAINT RQSTER_RULE_MNT_PLC_FK FOREIGN KEY(MOUNT_POLICY_NAME)
222
    REFERENCES MOUNT_POLICY(MOUNT_POLICY_NAME)
Steven Murray's avatar
Steven Murray committed
223
);
224
CREATE TABLE REQUESTER_GROUP_MOUNT_RULE(
225
226
227
228
229
230
  DISK_INSTANCE_NAME     VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_DIN_NN  NOT NULL,
  REQUESTER_GROUP_NAME   VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_RGN_NN  NOT NULL,
  MOUNT_POLICY_NAME      VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_MPN_NN  NOT NULL,
  USER_COMMENT           VARCHAR(1000)   CONSTRAINT RQSTER_GRP_RULE_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_CLHN_NN NOT NULL,
231
  CREATION_LOG_TIME      UINT64TYPE      CONSTRAINT RQSTER_GRP_RULE_CLT_NN  NOT NULL,
232
233
  LAST_UPDATE_USER_NAME  VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME  VARCHAR(100)    CONSTRAINT RQSTER_GRP_RULE_LUHN_NN NOT NULL,
234
  LAST_UPDATE_TIME       UINT64TYPE      CONSTRAINT RQSTER_GRP_RULE_LUT_NN  NOT NULL,
235
  CONSTRAINT RQSTER_GRP_RULE_PK PRIMARY KEY(DISK_INSTANCE_NAME, REQUESTER_GROUP_NAME),
236
  CONSTRAINT RQSTER_GRP_RULE_MNT_PLC_FK FOREIGN KEY(MOUNT_POLICY_NAME)
237
    REFERENCES MOUNT_POLICY(MOUNT_POLICY_NAME)
238
239
);
CREATE TABLE ARCHIVE_FILE(
240
  ARCHIVE_FILE_ID         UINT64TYPE      CONSTRAINT ARCHIVE_FILE_AFI_NN  NOT NULL,
241
242
  DISK_INSTANCE_NAME      VARCHAR(100)    CONSTRAINT ARCHIVE_FILE_DIN_NN  NOT NULL,
  DISK_FILE_ID            VARCHAR(100)    CONSTRAINT ARCHIVE_FILE_DFI_NN  NOT NULL,
243
244
  DISK_FILE_UID           UINT32TYPE      CONSTRAINT ARCHIVE_FILE_DFUID_NN  NOT NULL,
  DISK_FILE_GID           UINT32TYPE      CONSTRAINT ARCHIVE_FILE_DFGID_NN  NOT NULL,
245
  SIZE_IN_BYTES           UINT64TYPE      CONSTRAINT ARCHIVE_FILE_SIB_NN  NOT NULL,
246
  CHECKSUM_BLOB           CHECKSUM_BLOB_TYPE,
247
  CHECKSUM_ADLER32        UINT32TYPE      CONSTRAINT ARCHIVE_FILE_CB2_NN  NOT NULL,
248
249
250
  STORAGE_CLASS_ID        UINT64TYPE      CONSTRAINT ARCHIVE_FILE_SCI_NN  NOT NULL,
  CREATION_TIME           UINT64TYPE      CONSTRAINT ARCHIVE_FILE_CT2_NN  NOT NULL,
  RECONCILIATION_TIME     UINT64TYPE      CONSTRAINT ARCHIVE_FILE_RT_NN   NOT NULL,
251
  IS_DELETED              CHAR(1)         DEFAULT '0' CONSTRAINT ARCHIVE_FILE_ID_NN NOT NULL,
252
  COLLOCATION_HINT        VARCHAR(100),
Steven Murray's avatar
Steven Murray committed
253
  CONSTRAINT ARCHIVE_FILE_PK PRIMARY KEY(ARCHIVE_FILE_ID),
254
  CONSTRAINT ARCHIVE_FILE_STORAGE_CLASS_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID),
255
256
  CONSTRAINT ARCHIVE_FILE_DIN_DFI_UN UNIQUE(DISK_INSTANCE_NAME, DISK_FILE_ID),
  CONSTRAINT ARCHIVE_FILE_ID_BOOL_CK CHECK(IS_DELETED IN ('0', '1'))
257
);
258
CREATE INDEX ARCHIVE_FILE_DIN_IDX ON ARCHIVE_FILE(DISK_INSTANCE_NAME);
259
CREATE INDEX ARCHIVE_FILE_DFI_IDX ON ARCHIVE_FILE(DISK_FILE_ID);
260
CREATE TABLE TAPE_FILE(
261
  VID                      VARCHAR(100)   CONSTRAINT TAPE_FILE_V_NN    NOT NULL,
262
263
264
  FSEQ                     UINT64TYPE     CONSTRAINT TAPE_FILE_F_NN    NOT NULL,
  BLOCK_ID                 UINT64TYPE     CONSTRAINT TAPE_FILE_BI_NN   NOT NULL,
  LOGICAL_SIZE_IN_BYTES    UINT64TYPE     CONSTRAINT TAPE_FILE_CSIB_NN NOT NULL,
265
  COPY_NB                  UINT8TYPE      CONSTRAINT TAPE_FILE_CN_NN   NOT NULL,
266
267
  CREATION_TIME            UINT64TYPE     CONSTRAINT TAPE_FILE_CT_NN   NOT NULL,
  ARCHIVE_FILE_ID          UINT64TYPE     CONSTRAINT TAPE_FILE_AFI_NN  NOT NULL,
Cedric CAFFY's avatar
Cedric CAFFY committed
268
  SUPERSEDED_BY_VID        VARCHAR(100),
269
  SUPERSEDED_BY_FSEQ       UINT64TYPE    ,
270
271
272
273
274
275
276
277
  WRITE_START_WRAP         UINT16TYPE,
  WRITE_START_LPOS         UINT32TYPE,
  WRITE_END_WRAP           UINT16TYPE,
  WRITE_END_LPOS           UINT32TYPE,
  READ_START_WRAP          UINT16TYPE,
  READ_START_LPOS          UINT32TYPE,
  READ_END_WRAP            UINT16TYPE,
  READ_END_LPOS            UINT32TYPE,
Steven Murray's avatar
Steven Murray committed
278
  CONSTRAINT TAPE_FILE_PK PRIMARY KEY(VID, FSEQ),
279
280
  CONSTRAINT TAPE_FILE_TAPE_FK FOREIGN KEY(VID)
    REFERENCES TAPE(VID),
Steven Murray's avatar
Steven Murray committed
281
  CONSTRAINT TAPE_FILE_ARCHIVE_FILE_FK FOREIGN KEY(ARCHIVE_FILE_ID)
282
    REFERENCES ARCHIVE_FILE(ARCHIVE_FILE_ID),
283
  CONSTRAINT TAPE_FILE_VID_BLOCK_ID_UN UNIQUE(VID, BLOCK_ID),
284
  CONSTRAINT TAPE_FILE_COPY_NB_GT_0_CK CHECK(COPY_NB > 0),
285
  CONSTRAINT TAPE_FILE_SS_VID_FSEQ_FK FOREIGN KEY(SUPERSEDED_BY_VID, SUPERSEDED_BY_FSEQ)
Cedric CAFFY's avatar
Cedric CAFFY committed
286
    REFERENCES TAPE_FILE(VID, FSEQ)
287
);
288
CREATE INDEX TAPE_FILE_VID_IDX ON TAPE_FILE(VID);
289
CREATE INDEX TAPE_FILE_ARCHIVE_FILE_ID_IDX ON TAPE_FILE(ARCHIVE_FILE_ID);
290
CREATE INDEX TAPE_FILE_SBV_SBF_IDX ON TAPE_FILE(SUPERSEDED_BY_VID, SUPERSEDED_BY_FSEQ);
291
292
293
294
295
296
297
CREATE TABLE ACTIVITIES_WEIGHTS (
  DISK_INSTANCE_NAME       VARCHAR(100),
  ACTIVITY                 VARCHAR(100),
  WEIGHT                   VARCHAR(100),
  USER_COMMENT             VARCHAR(1000)   CONSTRAINT ACTIV_WEIGHTS_UC_NN   NOT NULL,
  CREATION_LOG_USER_NAME   VARCHAR(100)    CONSTRAINT ACTIV_WEIGHTS_CLUN_NN NOT NULL,
  CREATION_LOG_HOST_NAME   VARCHAR(100)    CONSTRAINT ACTIV_WEIGHTS_CLHN_NN NOT NULL,
298
  CREATION_LOG_TIME        UINT64TYPE      CONSTRAINT ACTIV_WEIGHTS_CLT_NN  NOT NULL,
299
300
  LAST_UPDATE_USER_NAME    VARCHAR(100)    CONSTRAINT ACTIV_WEIGHTS_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME    VARCHAR(100)    CONSTRAINT ACTIV_WEIGHTS_LUHN_NN NOT NULL,
301
  LAST_UPDATE_TIME         UINT64TYPE      CONSTRAINT ACTIV_WEIGHTS_LUT_NN  NOT NULL
302
);
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
CREATE TABLE ARCHIVE_FILE_RECYCLE_BIN(
  ARCHIVE_FILE_ID         UINT64TYPE      CONSTRAINT ARCHIVE_FILE_RB_AFI_NN  NOT NULL,
  DISK_INSTANCE_NAME      VARCHAR(100)    CONSTRAINT ARCHIVE_FILE_RB_DIN_NN  NOT NULL,
  DISK_FILE_ID            VARCHAR(100)    CONSTRAINT ARCHIVE_FILE_RB_DFI_NN  NOT NULL,
  DISK_FILE_ID_WHEN_DELETED VARCHAR(100)  CONSTRAINT ARCHIVE_FILE_RB_DFIWD_NN NOT NULL,
  DISK_FILE_UID           UINT32TYPE      CONSTRAINT ARCHIVE_FILE_RB_DFUID_NN  NOT NULL,
  DISK_FILE_GID           UINT32TYPE      CONSTRAINT ARCHIVE_FILE_RB_DFGID_NN  NOT NULL,
  SIZE_IN_BYTES           UINT64TYPE      CONSTRAINT ARCHIVE_FILE_RB_SIB_NN  NOT NULL,
  CHECKSUM_BLOB           CHECKSUM_BLOB_TYPE,
  CHECKSUM_ADLER32        UINT32TYPE      CONSTRAINT ARCHIVE_FILE_RB_CB2_NN  NOT NULL,
  STORAGE_CLASS_ID        UINT64TYPE      CONSTRAINT ARCHIVE_FILE_RB_SCI_NN  NOT NULL,
  CREATION_TIME           UINT64TYPE      CONSTRAINT ARCHIVE_FILE_RB_CT2_NN  NOT NULL,
  RECONCILIATION_TIME     UINT64TYPE      CONSTRAINT ARCHIVE_FILE_RB_RT_NN   NOT NULL,
  COLLOCATION_HINT        VARCHAR(100),
  DISK_FILE_PATH          VARCHAR(2000) CONSTRAINT ARCHIVE_FILE_RB_DFP_NN NOT NULL,
  DELETION_TIME           UINT64TYPE    CONSTRAINT ARCHIVE_FILE_RB_DT_NN NOT NULL,
  CONSTRAINT ARCHIVE_FILE_RB_PK PRIMARY KEY(ARCHIVE_FILE_ID),
  CONSTRAINT ARCHIVE_FILE_RB_SC_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID),
  CONSTRAINT ARCHIVE_FILE_RB_DIN_DFI_UN UNIQUE(DISK_INSTANCE_NAME, DISK_FILE_ID)
);
CREATE TABLE TAPE_FILE_RECYCLE_BIN(
  VID                      VARCHAR(100)   CONSTRAINT TAPE_FILE_RB_V_NN    NOT NULL,
  FSEQ                     UINT64TYPE     CONSTRAINT TAPE_FILE_RB_F_NN    NOT NULL,
  BLOCK_ID                 UINT64TYPE     CONSTRAINT TAPE_FILE_RB_BI_NN   NOT NULL,
  LOGICAL_SIZE_IN_BYTES    UINT64TYPE     CONSTRAINT TAPE_FILE_RB_CSIB_NN NOT NULL,
  COPY_NB                  UINT8TYPE      CONSTRAINT TAPE_FILE_RB_CN_NN   NOT NULL,
  CREATION_TIME            UINT64TYPE     CONSTRAINT TAPE_FILE_RB_CT_NN   NOT NULL,
  ARCHIVE_FILE_ID          UINT64TYPE     CONSTRAINT TAPE_FILE_RB_AFI_NN  NOT NULL,
  SUPERSEDED_BY_VID        VARCHAR(100),
  SUPERSEDED_BY_FSEQ       UINT64TYPE,
  WRITE_START_WRAP         UINT16TYPE,
  WRITE_START_LPOS         UINT32TYPE,
  WRITE_END_WRAP           UINT16TYPE,
  WRITE_END_LPOS           UINT32TYPE,
  READ_START_WRAP          UINT16TYPE,
  READ_START_LPOS          UINT32TYPE,
  READ_END_WRAP            UINT16TYPE,
  READ_END_LPOS            UINT32TYPE,
  CONSTRAINT TAPE_FILE_RB_PK PRIMARY KEY(VID, FSEQ),
  CONSTRAINT TAPE_FILE_RB_TAPE_FK FOREIGN KEY(VID)
    REFERENCES TAPE(VID),
  CONSTRAINT TAPE_FILE_RB_ARCHIVE_FILE_FK FOREIGN KEY(ARCHIVE_FILE_ID)
    REFERENCES ARCHIVE_FILE_RECYCLE_BIN(ARCHIVE_FILE_ID),
  CONSTRAINT TAPE_FILE_RB_VID_BLOCK_ID_UN UNIQUE(VID, BLOCK_ID),
  CONSTRAINT TAPE_FILE_RB_COPY_NB_GT_0_CK CHECK(COPY_NB > 0),
  CONSTRAINT TAPE_FILE_RB_SS_VID_FSEQ_FK FOREIGN KEY(SUPERSEDED_BY_VID, SUPERSEDED_BY_FSEQ)
    REFERENCES TAPE_FILE_RECYCLE_BIN(VID, FSEQ)
350
);
Cedric Caffy's avatar
Cedric Caffy committed
351
CREATE TABLE FILE_RECYCLE_LOG(
352
353
354
355
356
357
358
359
  FILE_RECYCLE_LOG_ID        UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_ID_NN NOT NULL,
  VID                        VARCHAR(100)        CONSTRAINT FILE_RECYCLE_LOG_VID_NN NOT NULL,
  FSEQ                       UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_FSEQ_NN NOT NULL,
  BLOCK_ID                   UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_BID_NN NOT NULL,
  COPY_NB                    UINT8TYPE           CONSTRAINT FILE_RECYCLE_LOG_COPY_NB_NN NOT NULL,
  TAPE_FILE_CREATION_TIME    UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_TFCT_NN NOT NULL,
  ARCHIVE_FILE_ID            UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_AFI_NN NOT NULL,
  DISK_INSTANCE_NAME         VARCHAR(100)        CONSTRAINT FILE_RECYCLE_LOG_DIN_NN NOT NULL,
360
361
  DISK_FILE_ID               VARCHAR(100)        CONSTRAINT FILE_RECYCLE_LOG_DFI_NN NOT NULL,
  DISK_FILE_ID_WHEN_DELETED  VARCHAR(100)        CONSTRAINT FILE_RECYCLE_LOG_DFIWD_NN NOT NULL,
362
363
364
365
366
367
368
369
370
371
372
373
  DISK_FILE_UID              UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_DFU_NN NOT NULL,
  DISK_FILE_GID              UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_DFG_NN NOT NULL,
  SIZE_IN_BYTES              UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_SIB_NN NOT NULL,
  CHECKSUM_BLOB              CHECKSUM_BLOB_TYPE,
  CHECKSUM_ADLER32           UINT32TYPE          CONSTRAINT FILE_RECYCLE_LOG_CA_NN NOT NULL,
  STORAGE_CLASS_ID           UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_SCI_NN NOT NULL,
  ARCHIVE_FILE_CREATION_TIME UINT64TYPE          CONSTRAINT FILE_RECYLE_LOG_CT_NN NOT NULL,
  RECONCILIATION_TIME        UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_RT_NN NOT NULL,
  COLLOCATION_HINT           VARCHAR(100),
  DISK_FILE_PATH             VARCHAR(2000),
  REASON_LOG                 VARCHAR(1000)       CONSTRAINT FILE_RECYCLE_LOG_RL_NN NOT NULL, 
  RECYCLE_LOG_TIME           UINT64TYPE          CONSTRAINT FILE_RECYCLE_LOG_RLT_NN NOT NULL,
Cedric Caffy's avatar
Cedric Caffy committed
374
375
376
  CONSTRAINT FILE_RECYCLE_LOG_PK PRIMARY KEY(FILE_RECYCLE_LOG_ID),
  CONSTRAINT FILE_RECYCLE_LOG_VID_FK FOREIGN KEY(VID) REFERENCES TAPE(VID),
  CONSTRAINT FILE_RECYCLE_LOG_SC_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID)
377
378
);
CREATE INDEX FILE_RECYCLE_LOG_DFI_IDX ON FILE_RECYCLE_LOG(DISK_FILE_ID);