oracleCommon.schema.sql 68.6 KB
Newer Older
1
2
3
4
5
6
7
/*******************************************************************
 *
 * This file contains all schema definitions which are not generated automatically.
 *
 * @author Castor Dev team, castor-dev@cern.ch
 *******************************************************************/

8
/* SQL statement to populate the intial schema version */
9
10
UPDATE UpgradeLog SET schemaVersion = '2_1_14_2'
 WHERE startDate = (SELECT max(startDate) FROM UpgradeLog);
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
/* Sequence for indices */
CREATE SEQUENCE ids_seq CACHE 300;

/* Custom type to handle int arrays */
CREATE OR REPLACE TYPE "numList" IS TABLE OF INTEGER;
/

/* Custom type to handle float arrays */
CREATE OR REPLACE TYPE floatList IS TABLE OF NUMBER;
/

/* Custom type to handle strings returned by pipelined functions */
CREATE OR REPLACE TYPE strListTable AS TABLE OF VARCHAR2(2048);
/

/* Function to tokenize a string using a specified delimiter. If no delimiter
 * is specified the default is ','. The results are returned as a table e.g.
 * SELECT * FROM TABLE (strTokenizer(inputValue, delimiter))
 */
CREATE OR REPLACE FUNCTION strTokenizer(p_list VARCHAR2, p_del VARCHAR2 := ',')
  RETURN strListTable pipelined IS
  l_idx   INTEGER;
  l_list  VARCHAR2(32767) := p_list;
  l_value VARCHAR2(32767);
BEGIN
  LOOP
    l_idx := instr(l_list, p_del);
    IF l_idx > 0 THEN
      PIPE ROW(ltrim(rtrim(substr(l_list, 1, l_idx - 1))));
      l_list := substr(l_list, l_idx + length(p_del));
    ELSE
      IF l_list IS NOT NULL THEN
        PIPE ROW(ltrim(rtrim(l_list)));
      END IF;
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END;
/

/* Get current time as a time_t. Not that easy in ORACLE */
CREATE OR REPLACE FUNCTION getTime RETURN NUMBER IS
  epoch            TIMESTAMP WITH TIME ZONE;
  now              TIMESTAMP WITH TIME ZONE;
  interval         INTERVAL DAY(9) TO SECOND;
  interval_days    NUMBER;
  interval_hours   NUMBER;
  interval_minutes NUMBER;
  interval_seconds NUMBER;
BEGIN
  epoch := TO_TIMESTAMP_TZ('01-JAN-1970 00:00:00 00:00',
    'DD-MON-YYYY HH24:MI:SS TZH:TZM');
  now := SYSTIMESTAMP AT TIME ZONE '00:00';
  interval         := now - epoch;
  interval_days    := EXTRACT(DAY    FROM (interval));
  interval_hours   := EXTRACT(HOUR   FROM (interval));
  interval_minutes := EXTRACT(MINUTE FROM (interval));
  interval_seconds := EXTRACT(SECOND FROM (interval));

  RETURN interval_days * 24 * 60 * 60 + interval_hours * 60 * 60 +
    interval_minutes * 60 + interval_seconds;
END;
/


/****************/
/* CastorConfig */
/****************/

82
83
/* Define a table for some configuration key-value pairs and populate it */
CREATE TABLE CastorConfig
84
85
86
  (class VARCHAR2(2048) CONSTRAINT NN_CastorConfig_class NOT NULL,
   key VARCHAR2(2048) CONSTRAINT NN_CastorConfig_key NOT NULL,
   value VARCHAR2(2048) CONSTRAINT NN_CastorConfig_value NOT NULL,
87
88
89
90
91
92
   description VARCHAR2(2048));

ALTER TABLE CastorConfig ADD CONSTRAINT UN_CastorConfig_class_key UNIQUE (class, key);

/* Prompt for the value of the general/instance option */
UNDEF instanceName
93
ACCEPT instanceName CHAR DEFAULT castor_stager PROMPT 'Enter the castor instance name (default: castor_stager, example: castoratlas): '
94
95
96
97
98
99
SET VER OFF
INSERT INTO CastorConfig
  VALUES ('general', 'instance', '&instanceName', 'Name of this Castor instance');

/* Prompt for the value of the stager/nsHost option */
UNDEF stagerNsHost
100
101
102
ACCEPT stagerNsHost CHAR PROMPT 'Enter the name of the nameserver host (example: castorns; this value is mandatory): '
INSERT INTO CastorConfig
  VALUES ('stager', 'nsHost', '&stagerNsHost', 'The name of the name server host to set in the CastorFile table overriding the CNS/HOST option defined in castor.conf');
103

104
105
106
107
108
109
110
111
112
113
/* DB link to the nameserver db */
PROMPT Configuration of the database link to the CASTOR name space
UNDEF cnsUser
ACCEPT cnsUser CHAR DEFAULT 'castor' PROMPT 'Enter the nameserver db username (default castor): ';
UNDEF cnsPasswd
ACCEPT cnsPasswd CHAR PROMPT 'Enter the nameserver db password: ';
UNDEF cnsDbName
ACCEPT cnsDbName CHAR PROMPT 'Enter the nameserver db TNS name: ';
CREATE DATABASE LINK remotens
  CONNECT TO &cnsUser IDENTIFIED BY &cnsPasswd USING '&cnsDbName';
114

115
/* Insert other default values */
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
INSERT INTO CastorConfig
  VALUES ('general', 'owner', sys_context('USERENV', 'CURRENT_USER'), 'The database owner of the schema');
INSERT INTO CastorConfig
  VALUES ('cleaning', 'terminatedRequestsTimeout', '120', 'Maximum timeout for successful and failed requests in hours');
INSERT INTO CastorConfig
  VALUES ('cleaning', 'outOfDateStageOutDCsTimeout', '72', 'Timeout for STAGEOUT diskCopies in hours');
INSERT INTO CastorConfig
  VALUES ('cleaning', 'failedDCsTimeout', '72', 'Timeout for failed diskCopies in hours');
INSERT INTO CastorConfig
  VALUES ('Repack', 'Protocol', 'rfio', 'The protocol that repack should use for writing files to disk');
INSERT INTO CastorConfig
  VALUES ('Recall', 'MaxNbRetriesWithinMount', '2', 'The maximum number of retries for recalling a file within the same tape mount. When exceeded, the recall may still be retried in another mount. See Recall/MaxNbMount entry');
INSERT INTO CastorConfig
  VALUES ('Recall', 'MaxNbMounts', '2', 'The maximum number of mounts for recalling a given file. When exceeded, the recall will be failed if no other tapecopy can be used. See also Recall/MaxNbRetriesWithinMount entry');
INSERT INTO CastorConfig
  VALUES ('Migration', 'SizeThreshold', '300000000', 'The threshold to consider a file "small" or "large" when routing it to tape');
INSERT INTO CastorConfig
133
  VALUES ('D2dCopy', 'MaxNbRetries', '2', 'The maximum number of retries for disk to disk copies before it is considered failed. Here 2 means we will do in total 3 attempts.');
134
135
INSERT INTO CastorConfig
  VALUES ('DiskServer', 'HeartbeatTimeout', '180', 'The maximum amount of time in seconds that a diskserver can spend without sending any hearbeat before it is automatically set to offline.');
136
137
INSERT INTO CastorConfig
  VALUES ('Draining', 'MaxNbSchedD2dPerDrain', '1000', 'The maximum number of disk to disk copies that each draining job should send to the scheduler concurrently.');
138
139
INSERT INTO CastorConfig
  VALUES ('Rebalancing', 'Sensibility', '5', 'The rebalancing sensibility (in percent) : if a fileSystem is at least this percentage fuller than the average of the diskpool where is lives, rebalancing will fire.');
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192

/* Create the AdminUsers table */
CREATE TABLE AdminUsers (euid NUMBER, egid NUMBER);
ALTER TABLE AdminUsers ADD CONSTRAINT UN_AdminUsers_euid_egid UNIQUE (euid, egid);
INSERT INTO AdminUsers VALUES (0, 0);   -- root/root, to be removed
INSERT INTO AdminUsers VALUES (-1, -1); -- internal requests

/* Prompt for stage:st account */
PROMPT Configuration of the admin part of the B/W list
UNDEF stageUid
ACCEPT stageUid NUMBER PROMPT 'Enter the stage user id: ';
UNDEF stageGid
ACCEPT stageGid NUMBER PROMPT 'Enter the st group id: ';
INSERT INTO AdminUsers VALUES (&stageUid, &stageGid);

/* Prompt for additional administrators */
PROMPT In order to define admins that will be exempt of B/W list checks,
PROMPT (e.g. c3 group at CERN), please give a space separated list of
PROMPT <userid>:<groupid> pairs. userid can be empty, meaning any user
PROMPT in the specified group.
UNDEF adminList
ACCEPT adminList CHAR PROMPT 'List of admins: ';
DECLARE
  adminUserId NUMBER;
  adminGroupId NUMBER;
  ind NUMBER;
  errmsg VARCHAR(2048);
BEGIN
  -- If the adminList is empty do nothing
  IF '&adminList' IS NULL THEN
    RETURN;
  END IF;
  -- Loop over the adminList
  FOR admin IN (SELECT column_value AS s
                  FROM TABLE(strTokenizer('&adminList',' '))) LOOP
    BEGIN
      ind := INSTR(admin.s, ':');
      IF ind = 0 THEN
        errMsg := 'Invalid <userid>:<groupid> ' || admin.s || ', ignoring';
        RAISE INVALID_NUMBER;
      END IF;
      errMsg := 'Invalid userid ' || SUBSTR(admin.s, 1, ind - 1) || ', ignoring';
      adminUserId := TO_NUMBER(SUBSTR(admin.s, 1, ind - 1));
      errMsg := 'Invalid groupid ' || SUBSTR(admin.s, ind) || ', ignoring';
      adminGroupId := TO_NUMBER(SUBSTR(admin.s, ind+1));
      INSERT INTO AdminUsers (euid, egid) VALUES (adminUserId, adminGroupId);
    EXCEPTION WHEN INVALID_NUMBER THEN
      dbms_output.put_line(errMsg);
    END;
  END LOOP;
END;
/

193

194
195
196
/************************************/
/* Garbage collection related table */
/************************************/
197

198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
/* A table storing the Gc policies and detailing there configuration
 * For each policy, identified by a name, parameters are :
 *   - userWeight : the name of the PL/SQL function to be called to
 *     precompute the GC weight when a file is written by the user.
 *   - recallWeight : the name of the PL/SQL function to be called to
 *     precompute the GC weight when a file is recalled
 *   - copyWeight : the name of the PL/SQL function to be called to
 *     precompute the GC weight when a file is disk to disk copied
 *   - firstAccessHook : the name of the PL/SQL function to be called
 *     when the file is accessed for the first time. Can be NULL.
 *   - accessHook : the name of the PL/SQL function to be called
 *     when the file is accessed (except for the first time). Can be NULL.
 *   - userSetGCWeight : the name of the PL/SQL function to be called
 *     when a setFileGcWeight user request is processed can be NULL.
 * All functions return a number that is the new gcWeight.
 * In general, here are the signatures :
 *   userWeight(fileSize NUMBER, DiskCopyStatus NUMBER)
 *   recallWeight(fileSize NUMBER)
 *   copyWeight(fileSize NUMBER, DiskCopyStatus NUMBER, sourceWeight NUMBER))
 *   firstAccessHook(oldGcWeight NUMBER, creationTime NUMBER)
 *   accessHook(oldGcWeight NUMBER, creationTime NUMBER, nbAccesses NUMBER)
 *   userSetGCWeight(oldGcWeight NUMBER, userDelta NUMBER)
 */
CREATE TABLE GcPolicy (name VARCHAR2(2048) CONSTRAINT NN_GcPolicy_Name NOT NULL CONSTRAINT PK_GcPolicy_Name PRIMARY KEY,
                       userWeight VARCHAR2(2048) CONSTRAINT NN_GcPolicy_UserWeight NOT NULL,
                       recallWeight VARCHAR2(2048) CONSTRAINT NN_GcPolicy_RecallWeight NOT NULL,
                       copyWeight VARCHAR2(2048) CONSTRAINT NN_GcPolicy_CopyWeight NOT NULL,
                       firstAccessHook VARCHAR2(2048) DEFAULT NULL,
                       accessHook VARCHAR2(2048) DEFAULT NULL,
                       userSetGCWeight VARCHAR2(2048) DEFAULT NULL);
228

229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
/* Default policy, mainly based on file sizes */
INSERT INTO GcPolicy VALUES ('default',
                             'castorGC.sizeRelatedUserWeight',
                             'castorGC.sizeRelatedRecallWeight',
                             'castorGC.sizeRelatedCopyWeight',
                             'castorGC.dayBonusFirstAccessHook',
                             'castorGC.halfHourBonusAccessHook',
                             'castorGC.cappedUserSetGCWeight');
INSERT INTO GcPolicy VALUES ('FIFO',
                             'castorGC.creationTimeUserWeight',
                             'castorGC.creationTimeRecallWeight',
                             'castorGC.creationTimeCopyWeight',
                             NULL,
                             NULL,
                             NULL);
INSERT INTO GcPolicy VALUES ('LRU',
                             'castorGC.creationTimeUserWeight',
                             'castorGC.creationTimeRecallWeight',
                             'castorGC.creationTimeCopyWeight',
                             'castorGC.LRUFirstAccessHook',
                             'castorGC.LRUAccessHook',
                             NULL);
INSERT INTO GcPolicy VALUES ('LRUpin',
                             'castorGC.creationTimeUserWeight',
                             'castorGC.creationTimeRecallWeight',
                             'castorGC.creationTimeCopyWeight',
                             'castorGC.LRUFirstAccessHook',
                             'castorGC.LRUAccessHook',
                             'castorGC.LRUpinUserSetGCWeight');


/* SQL statements for type SvcClass */
CREATE TABLE SvcClass (name VARCHAR2(2048) CONSTRAINT NN_SvcClass_Name NOT NULL,
                       defaultFileSize INTEGER,
                       maxReplicaNb NUMBER,
                       gcPolicy VARCHAR2(2048) DEFAULT 'default' CONSTRAINT NN_SvcClass_GcPolicy NOT NULL,
                       disk1Behavior NUMBER,
                       replicateOnClose NUMBER,
                       failJobsWhenNoSpace NUMBER,
                       lastEditor VARCHAR2(2048) CONSTRAINT NN_SvcClass_LastEditor NOT NULL,
                       lastEditionTime INTEGER CONSTRAINT NN_SvcClass_LastEditionTime NOT NULL,
                       id INTEGER CONSTRAINT PK_SvcClass_Id PRIMARY KEY,
                       forcedFileClass INTEGER CONSTRAINT NN_SvcClass_ForcedFileClass NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
ALTER TABLE SvcClass ADD CONSTRAINT UN_SvcClass_Name UNIQUE (name);
ALTER TABLE SvcClass ADD CONSTRAINT FK_SvcClass_GCPolicy
  FOREIGN KEY (gcPolicy) REFERENCES GcPolicy (name);
CREATE INDEX I_SvcClass_GcPolicy ON SvcClass (gcPolicy);
277

278
279
/* SQL statements for requests status */
/* Partitioning enables faster response (more than indexing) for the most frequent queries - credits to Nilo Segura */
280
CREATE TABLE newRequests (type NUMBER(38) CONSTRAINT NN_NewRequests_Type NOT NULL, id NUMBER(38) CONSTRAINT NN_NewRequests_Id NOT NULL, creation DATE CONSTRAINT NN_NewRequests_Creation NOT NULL, CONSTRAINT PK_NewRequests_Type_Id PRIMARY KEY (type, id))
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
ORGANIZATION INDEX
COMPRESS
PARTITION BY LIST (type)
 (
  PARTITION type_16 VALUES (16)  TABLESPACE stager_data,
  PARTITION type_21 VALUES (21)  TABLESPACE stager_data,
  PARTITION type_33 VALUES (33)  TABLESPACE stager_data,
  PARTITION type_34 VALUES (34)  TABLESPACE stager_data,
  PARTITION type_35 VALUES (35)  TABLESPACE stager_data,
  PARTITION type_36 VALUES (36)  TABLESPACE stager_data,
  PARTITION type_37 VALUES (37)  TABLESPACE stager_data,
  PARTITION type_38 VALUES (38)  TABLESPACE stager_data,
  PARTITION type_39 VALUES (39)  TABLESPACE stager_data,
  PARTITION type_40 VALUES (40)  TABLESPACE stager_data,
  PARTITION type_41 VALUES (41)  TABLESPACE stager_data,
  PARTITION type_42 VALUES (42)  TABLESPACE stager_data,
  PARTITION type_43 VALUES (43)  TABLESPACE stager_data,
  PARTITION type_44 VALUES (44)  TABLESPACE stager_data,
  PARTITION type_45 VALUES (45)  TABLESPACE stager_data,
  PARTITION type_46 VALUES (46)  TABLESPACE stager_data,
  PARTITION type_48 VALUES (48)  TABLESPACE stager_data,
  PARTITION type_49 VALUES (49)  TABLESPACE stager_data,
  PARTITION type_50 VALUES (50)  TABLESPACE stager_data,
  PARTITION type_51 VALUES (51)  TABLESPACE stager_data,
  PARTITION type_60 VALUES (60)  TABLESPACE stager_data,
  PARTITION type_64 VALUES (64)  TABLESPACE stager_data,
  PARTITION type_65 VALUES (65)  TABLESPACE stager_data,
  PARTITION type_66 VALUES (66)  TABLESPACE stager_data,
  PARTITION type_67 VALUES (67)  TABLESPACE stager_data,
  PARTITION type_78 VALUES (78)  TABLESPACE stager_data,
  PARTITION type_79 VALUES (79)  TABLESPACE stager_data,
  PARTITION type_80 VALUES (80)  TABLESPACE stager_data,
  PARTITION type_84 VALUES (84)  TABLESPACE stager_data,
  PARTITION type_90 VALUES (90)  TABLESPACE stager_data,
  PARTITION type_142 VALUES (142)  TABLESPACE stager_data,
  PARTITION type_144 VALUES (144)  TABLESPACE stager_data,
  PARTITION type_147 VALUES (147)  TABLESPACE stager_data,
  PARTITION type_149 VALUES (149)  TABLESPACE stager_data,
  PARTITION notlisted VALUES (default) TABLESPACE stager_data
 );

322

323
/* SQL statements for type CastorFile */
324
325
326
327
328
329
330
331
CREATE TABLE CastorFile (fileId INTEGER,
                         nsHost VARCHAR2(2048),
                         fileSize INTEGER,
                         creationTime INTEGER,
                         lastAccessTime INTEGER,
                         lastKnownFileName VARCHAR2(2048) CONSTRAINT NN_CastorFile_LKFileName NOT NULL,
                         lastUpdateTime INTEGER,
                         id INTEGER CONSTRAINT PK_CastorFile_Id PRIMARY KEY,
332
                         fileClass INTEGER,
333
                         tapeStatus INTEGER, -- can be ONTAPE, NOTONTAPE, DISKONLY or NULL
334
                         nsOpenTime NUMBER CONSTRAINT NN_CastorFile_NsOpenTime NOT NULL)  -- timestamp given by the Nameserver at Cns_openx()
335
336
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
ALTER TABLE CastorFile ADD CONSTRAINT FK_CastorFile_FileClass
337
  FOREIGN KEY (fileClass) REFERENCES FileClass (id);
338
CREATE UNIQUE INDEX I_CastorFile_LastKnownFileName ON CastorFile (lastKnownFileName);
339
340
341
ALTER TABLE CastorFile ADD CONSTRAINT UN_CastorFile_LKFileName UNIQUE (lastKnownFileName);
CREATE INDEX I_CastorFile_FileClass ON CastorFile(FileClass);
CREATE UNIQUE INDEX I_CastorFile_FileIdNsHost ON CastorFile (fileId, nsHost);
342
343
344
ALTER TABLE CastorFile
  ADD CONSTRAINT CK_CastorFile_TapeStatus
  CHECK (tapeStatus IN (0, 1, 2));
345
CREATE INDEX I_CastorFile_tapeStatus ON CastorFile(tapeStatus);
346

Sebastien Ponce's avatar
Sebastien Ponce committed
347
/* SQL statement for table SubRequest */
348
349
350
CREATE TABLE SubRequest
  (retryCounter NUMBER, fileName VARCHAR2(2048), protocol VARCHAR2(2048),
   xsize INTEGER, priority NUMBER, subreqId VARCHAR2(2048), flags NUMBER,
351
352
353
   modeBits NUMBER, creationTime INTEGER CONSTRAINT NN_SubRequest_CreationTime 
   NOT NULL, lastModificationTime INTEGER, answered NUMBER, errorCode NUMBER, 
   errorMessage VARCHAR2(2048), id NUMBER CONSTRAINT NN_SubRequest_Id NOT NULL,
354
   diskcopy INTEGER, castorFile INTEGER, status INTEGER,
355
   request INTEGER, getNextStatus INTEGER, requestedFileSystems VARCHAR2(2048),
356
357
   svcHandler VARCHAR2(2048) CONSTRAINT NN_SubRequest_SvcHandler NOT NULL,
   reqType INTEGER CONSTRAINT NN_SubRequest_reqType NOT NULL
358
359
360
361
362
  )
  PCTFREE 50 PCTUSED 40 INITRANS 50
  ENABLE ROW MOVEMENT
  PARTITION BY LIST (STATUS)
   (
363
364
365
366
367
368
369
370
371
372
373
374
    PARTITION P_STATUS_0_1_2 VALUES (0, 1, 2),      -- *START
    PARTITION P_STATUS_3     VALUES (3),
    PARTITION P_STATUS_4     VALUES (4),
    PARTITION P_STATUS_5     VALUES (5),
    PARTITION P_STATUS_6     VALUES (6),
    PARTITION P_STATUS_7     VALUES (7),
    PARTITION P_STATUS_8     VALUES (8),
    PARTITION P_STATUS_9_10  VALUES (9, 10),        -- FAILED_*
    PARTITION P_STATUS_11    VALUES (11),
    PARTITION P_STATUS_12    VALUES (12),
    PARTITION P_STATUS_13_14 VALUES (13, 14),       -- *SCHED
    PARTITION P_STATUS_OTHER VALUES (DEFAULT)
375
376
377
378
379
   );

/* SQL statements for constraints on the SubRequest table */
ALTER TABLE SubRequest
  ADD CONSTRAINT PK_SubRequest_Id PRIMARY KEY (ID);
Dennis Waldron's avatar
Dennis Waldron committed
380
CREATE INDEX I_SubRequest_RT_CT_ID ON SubRequest(svcHandler, creationTime, id) LOCAL
381
 (PARTITION P_STATUS_0_1_2,
382
  PARTITION P_STATUS_3,
383
384
385
386
387
388
389
390
  PARTITION P_STATUS_4,
  PARTITION P_STATUS_5,
  PARTITION P_STATUS_6,
  PARTITION P_STATUS_7,
  PARTITION P_STATUS_8,
  PARTITION P_STATUS_9_10,
  PARTITION P_STATUS_11,
  PARTITION P_STATUS_12,
391
392
  PARTITION P_STATUS_13_14,
  PARTITION P_STATUS_OTHER);
393

394
395
396
/* this index is dedicated to archivesubreq */
CREATE INDEX I_SubRequest_Req_Stat_no89 ON SubRequest (request, decode(status,8,NULL,9,NULL,status));

Sebastien Ponce's avatar
Sebastien Ponce committed
397
398
399
400
401
CREATE INDEX I_SubRequest_Castorfile ON SubRequest (castorFile);
CREATE INDEX I_SubRequest_DiskCopy ON SubRequest (diskCopy);
CREATE INDEX I_SubRequest_Request ON SubRequest (request);
CREATE INDEX I_SubRequest_SubReqId ON SubRequest (subReqId);
CREATE INDEX I_SubRequest_LastModTime ON SubRequest (lastModificationTime) LOCAL;
402
403
404
ALTER TABLE SubRequest
  ADD CONSTRAINT CK_SubRequest_Status
  CHECK (status IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13));
Sebastien Ponce's avatar
Sebastien Ponce committed
405
406

BEGIN
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_START, 'SUBREQUEST_START');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_RESTART, 'SUBREQUEST_RESTART');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_RETRY, 'SUBREQUEST_RETRY');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_WAITSCHED, 'SUBREQUEST_WAITSCHED');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_WAITTAPERECALL, 'SUBREQUEST_WAITTAPERECALL');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_WAITSUBREQ, 'SUBREQUEST_WAITSUBREQ');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_READY, 'SUBREQUEST_READY');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_FAILED, 'SUBREQUEST_FAILED');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_FINISHED, 'SUBREQUEST_FINISHED');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_FAILED_FINISHED, 'SUBREQUEST_FAILED_FINISHED');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_ARCHIVED, 'SUBREQUEST_ARCHIVED');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_REPACK, 'SUBREQUEST_REPACK');
  setObjStatusName('SubRequest', 'status', dconst.SUBREQUEST_READYFORSCHED, 'SUBREQUEST_READYFORSCHED');
  setObjStatusName('SubRequest', 'getNextStatus', dconst.GETNEXTSTATUS_NOTAPPLICABLE, 'GETNEXTSTATUS_NOTAPPLICABLE');
  setObjStatusName('SubRequest', 'getNextStatus', dconst.GETNEXTSTATUS_FILESTAGED, 'GETNEXTSTATUS_FILESTAGED');
  setObjStatusName('SubRequest', 'getNextStatus', dconst.GETNEXTSTATUS_NOTIFIED, 'GETNEXTSTATUS_NOTIFIED');
Sebastien Ponce's avatar
Sebastien Ponce committed
423
424
425
426
END;
/


427
428
429
430
/**********************************/
/* Recall/Migration related table */
/**********************************/

431
/* Definition of the RecallGroup table
432
 *   id : unique id of the RecallGroup
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
 *   name : the name of the RecallGroup
 *   nbDrives : maximum number of drives that may be concurrently used across all users of this RecallGroup
 *   minAmountDataForMount : the minimum amount of data needed to trigger a new mount, in bytes
 *   minNbFilesForMount : the minimum number of files needed to trigger a new mount
 *   maxFileAgeBeforeMount : the maximum file age before a tape in mounted, in seconds
 *   vdqmPriority : the priority that should be used for VDQM requests
 *   lastEditor : the login from which the tapepool was last modified
 *   lastEditionTime : the time at which the tapepool was last modified
 * Note that a mount is attempted as soon as one of the three criterias is reached.
 */
CREATE TABLE RecallGroup(id INTEGER CONSTRAINT PK_RecallGroup_Id PRIMARY KEY CONSTRAINT NN_RecallGroup_Id NOT NULL, 
                         name VARCHAR2(2048) CONSTRAINT NN_RecallGroup_Name NOT NULL
                                             CONSTRAINT UN_RecallGroup_Name UNIQUE USING INDEX,
                         nbDrives INTEGER CONSTRAINT NN_RecallGroup_NbDrives NOT NULL,
                         minAmountDataForMount INTEGER CONSTRAINT NN_RecallGroup_MinAmountData NOT NULL,
                         minNbFilesForMount INTEGER CONSTRAINT NN_RecallGroup_MinNbFiles NOT NULL,
                         maxFileAgeBeforeMount INTEGER CONSTRAINT NN_RecallGroup_MaxFileAge NOT NULL,
                         vdqmPriority INTEGER DEFAULT 0 CONSTRAINT NN_RecallGroup_VdqmPriority NOT NULL,
                         lastEditor VARCHAR2(2048) CONSTRAINT NN_RecallGroup_LastEditor NOT NULL,
                         lastEditionTime NUMBER CONSTRAINT NN_RecallGroup_LastEdTime NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;

455
456
457
458
459
/* Insert the bare minimum to get a working recall:
 * create the default recall group to have a default recall mount traffic shaping.
 */
INSERT INTO RecallGroup (id, name, nbDrives, minAmountDataForMount, minNbFilesForMount,
                         maxFileAgeBeforeMount, vdqmPriority, lastEditor, lastEditionTime)
460
  VALUES (ids_seq.nextval, 'default', 20, 10*1024*1024*1024, 10, 30*3600, 0, 'Castor 2.1.13 or above installation script', getTime());
461
462


463
/* Definition of the RecallUser table
464
465
466
467
468
469
470
 *   euid : uid of the recall user
 *   egid : gid of the recall user
 *   recallGroup : the recall group to which this user belongs
 *   lastEditor : the login from which the tapepool was last modified
 *   lastEditionTime : the time at which the tapepool was last modified
 * Note that a mount is attempted as soon as one of the three criterias is reached.
 */
471
CREATE TABLE RecallUser(euid INTEGER,
472
473
474
475
476
                        egid INTEGER CONSTRAINT NN_RecallUser_Egid NOT NULL,
                        recallGroup INTEGER CONSTRAINT NN_RecallUser_RecallGroup NOT NULL,
                        lastEditor VARCHAR2(2048) CONSTRAINT NN_RecallUser_LastEditor NOT NULL,
                        lastEditionTime NUMBER CONSTRAINT NN_RecallUser_LastEdTime NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
477
-- see comment in the RecallMount table about why we need this index
478
479
480
481
CREATE INDEX I_RecallUser_RecallGroup ON RecallUser(recallGroup); 
ALTER TABLE RecallUser ADD CONSTRAINT FK_RecallUser_RecallGroup FOREIGN KEY (recallGroup) REFERENCES RecallGroup(id);

/* Definition of the RecallMount table
482
483
 *   id : unique id of the RecallGroup
 *   mountTransactionId : the VDQM transaction that this mount is dealing with
484
 *   vid : the tape mounted or to be mounted
485
486
487
488
489
490
491
 *   label : the label of the mounted tape
 *   density : the density of the mounted tape
 *   recallGroup : the recall group to which this mount belongs
 *   startTime : the time at which this mount started
 *   status : current status of the RecallMount (NEW, WAITDRIVE or RECALLING)
 *   lastVDQMPingTime : last time we have checked VDQM for this mount
 *   lastProcessedFseq : last fseq that was processed by this mount (-1 if none)
492
493
494
495
496
497
498
499
500
501
502
503
504
 */
CREATE TABLE RecallMount(id INTEGER CONSTRAINT PK_RecallMount_Id PRIMARY KEY CONSTRAINT NN_RecallMount_Id NOT NULL, 
                         mountTransactionId INTEGER CONSTRAINT UN_RecallMount_TransId UNIQUE USING INDEX,
                         VID VARCHAR2(2048) CONSTRAINT NN_RecallMount_VID NOT NULL
                                            CONSTRAINT UN_RecallMount_VID UNIQUE USING INDEX,
                         label VARCHAR2(2048),
                         density VARCHAR2(2048),
                         recallGroup INTEGER CONSTRAINT NN_RecallMount_RecallGroup NOT NULL,
                         startTime NUMBER CONSTRAINT NN_RecallMount_startTime NOT NULL,
                         status INTEGER CONSTRAINT NN_RecallMount_Status NOT NULL,
                         lastVDQMPingTime NUMBER DEFAULT 0 CONSTRAINT NN_RecallMount_lastVDQMPing NOT NULL,
                         lastProcessedFseq INTEGER DEFAULT -1 CONSTRAINT NN_RecallMount_Fseq NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
505
-- this index may sound counter productive as we have very few rows and a full table scan will always be faster
506
507
508
509
-- However, it is needed to avoid a table lock on RecallGroup when taking a row lock on RecallMount,
-- via the existing foreign key. On top, this table lock is also taken in case of an update that does not
-- touch any row while with the index, no row lock is taken at all, as one may expect
CREATE INDEX I_RecallMount_RecallGroup ON RecallMount(recallGroup); 
510
ALTER TABLE RecallMount ADD CONSTRAINT FK_RecallMount_RecallGroup FOREIGN KEY (recallGroup) REFERENCES RecallGroup(id);
511
BEGIN
512
513
514
  setObjStatusName('RecallMount', 'status', tconst.RECALLMOUNT_NEW, 'RECALLMOUNT_NEW');
  setObjStatusName('RecallMount', 'status', tconst.RECALLMOUNT_WAITDRIVE, 'RECALLMOUNT_WAITDRIVE');
  setObjStatusName('RecallMount', 'status', tconst.RECALLMOUNT_RECALLING, 'RECALLMOUNT_RECALLING');
515
516
END;
/
517
518
519
ALTER TABLE RecallMount
  ADD CONSTRAINT CK_RecallMount_Status
  CHECK (status IN (0, 1, 2));
520
521
522
523
524
525
526
527
528
529
530
531

/* Definition of the RecallJob table
 * id unique identifer of this RecallJob
 * castorFile the file to be recalled
 * copyNb the copy number of the segment that this recalljob is targetting
 * recallGroup the recallGroup that triggered the recall
 * svcClass the service class used when triggering the recall. Will be used to place the file on disk
 * euid the user that triggered the recall
 * egid the group that triggered the recall
 * vid the tape on which the targetted segment resides
 * fseq the file sequence number of the targetted segment on its tape
 * status status of the recallJob
532
 * filesize size of the segment to be recalled
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
 * creationTime time when this job was created
 * nbRetriesWithinMount number of times we have tried to read the file within the current tape mount
 * nbMounts number of times we have mounted a tape for this RecallJob
 * blockId blockId of the file
 * fileTransactionId
 */
CREATE TABLE RecallJob(id INTEGER CONSTRAINT PK_RecallJob_Id PRIMARY KEY CONSTRAINT NN_RecallJob_Id NOT NULL, 
                       castorFile INTEGER CONSTRAINT NN_RecallJob_CastorFile NOT NULL,
                       copyNb INTEGER CONSTRAINT NN_RecallJob_CopyNb NOT NULL,
                       recallGroup INTEGER CONSTRAINT NN_RecallJob_RecallGroup NOT NULL,
                       svcClass INTEGER CONSTRAINT NN_RecallJob_SvcClass NOT NULL,
                       euid INTEGER CONSTRAINT NN_RecallJob_Euid NOT NULL,
                       egid INTEGER CONSTRAINT NN_RecallJob_Egid NOT NULL,
                       vid VARCHAR2(2048) CONSTRAINT NN_RecallJob_VID NOT NULL,
                       fseq INTEGER CONSTRAINT NN_RecallJob_Fseq NOT NULL,
                       status INTEGER CONSTRAINT NN_RecallJob_Status NOT NULL,
                       fileSize INTEGER CONSTRAINT NN_RecallJob_FileSize NOT NULL,
                       creationTime INTEGER CONSTRAINT NN_RecallJob_CreationTime NOT NULL,
                       nbRetriesWithinMount NUMBER DEFAULT 0 CONSTRAINT NN_RecallJob_nbRetriesWM NOT NULL,
                       nbMounts NUMBER DEFAULT 0 CONSTRAINT NN_RecallJob_nbMounts NOT NULL,
                       blockId RAW(4) CONSTRAINT NN_RecallJob_blockId NOT NULL,
                       fileTransactionId INTEGER CONSTRAINT UN_RecallJob_FileTrId UNIQUE USING INDEX)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;

557
558
559
-- see comment in the RecallMount table about why we need the next 3 indices (although here,
-- the size of the table by itself is asking for one)
CREATE INDEX I_RecallJob_SvcClass ON RecallJob (svcClass);
560
561
CREATE INDEX I_RecallJob_RecallGroup ON RecallJob (recallGroup);
CREATE INDEX I_RecallJob_Castorfile_VID ON RecallJob (castorFile, VID);
562
CREATE INDEX I_RecallJob_VIDFseq ON RecallJob (VID, fseq);
563
564
565
566
567

ALTER TABLE RecallJob ADD CONSTRAINT FK_RecallJob_SvcClass FOREIGN KEY (svcClass) REFERENCES SvcClass(id);
ALTER TABLE RecallJob ADD CONSTRAINT FK_RecallJob_RecallGroup FOREIGN KEY (recallGroup) REFERENCES RecallGroup(id);
ALTER TABLE RecallJob ADD CONSTRAINT FK_RecallJob_CastorFile FOREIGN KEY (castorFile) REFERENCES CastorFile(id);

568
569
570
BEGIN
  -- PENDING status is when a RecallJob is created
  -- It is immediately candidate for being recalled by an ongoing recallMount
571
  setObjStatusName('RecallJob', 'status', tconst.RECALLJOB_PENDING, 'RECALLJOB_PENDING');
572
573
574
575
  -- SELECTED status is when the file is currently being recalled.
  -- Note all recallJobs of a given file will have this state while the file is being recalled,
  -- even if another copy is being recalled. The recallJob that is effectively used can be identified
  -- by its non NULL fileTransactionId
576
  setObjStatusName('RecallJob', 'status', tconst.RECALLJOB_SELECTED, 'RECALLJOB_SELECTED');
577
  -- RETRYMOUNT status is when the file recall has failed and should be retried after remounting the tape
Sebastien Ponce's avatar
Sebastien Ponce committed
578
  -- These will be reset to PENDING on RecallMount deletion
579
  setObjStatusName('RecallJob', 'status', tconst.RECALLJOB_RETRYMOUNT, 'RECALLJOB_RETRYMOUNT');
580
581
END;
/
582
583
ALTER TABLE RecallJob
  ADD CONSTRAINT CK_RecallJob_Status
584
  CHECK (status IN (1, 2, 3, 4));
585

586
587
/* Definition of the TapePool table
 *   name : the name of the TapePool
588
 *   nbDrives : maximum number of drives that may be concurrently used across all users of this TapePool
589
590
591
592
593
594
595
596
597
598
599
600
601
 *   minAmountDataForMount : the minimum amount of data needed to trigger a new mount, in bytes
 *   minNbFilesForMount : the minimum number of files needed to trigger a new mount
 *   maxFileAgeBeforeMount : the maximum file age before a tape in mounted, in seconds
 *   lastEditor : the login from which the tapepool was last modified
 *   lastEditionTime : the time at which the tapepool was last modified
 * Note that a mount is attempted as soon as one of the three criterias is reached.
 */
CREATE TABLE TapePool (name VARCHAR2(2048) CONSTRAINT NN_TapePool_Name NOT NULL,
                       nbDrives INTEGER CONSTRAINT NN_TapePool_NbDrives NOT NULL,
                       minAmountDataForMount INTEGER CONSTRAINT NN_TapePool_MinAmountData NOT NULL,
                       minNbFilesForMount INTEGER CONSTRAINT NN_TapePool_MinNbFiles NOT NULL,
                       maxFileAgeBeforeMount INTEGER CONSTRAINT NN_TapePool_MaxFileAge NOT NULL,
                       lastEditor VARCHAR2(2048) CONSTRAINT NN_TapePool_LastEditor NOT NULL,
602
                       lastEditionTime NUMBER CONSTRAINT NN_TapePool_LastEdTime NOT NULL,
603
604
605
606
                       id INTEGER CONSTRAINT PK_TapePool_Id PRIMARY KEY CONSTRAINT NN_TapePool_Id NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;

/* Definition of the MigrationMount table
607
 *   mountTransactionId : the unique identifier of the mount transaction
608
609
610
611
612
613
614
615
616
 *   tapeGatewayRequestId : 
 *   VID : tape currently mounted (when applicable)
 *   label : label (i.e. format) of the currently mounted tape (when applicable)
 *   density : density of the currently mounted tape (when applicable)
 *   lastFseq : position of the last file written on the tape
 *   lastVDQMPingTime : last time we've pinged VDQM
 *   tapePool : tapepool used by this migration
 *   status : current status of the migration
 */
617
CREATE TABLE MigrationMount (mountTransactionId INTEGER CONSTRAINT UN_MigrationMount_VDQM UNIQUE USING INDEX,
618
619
                             id INTEGER CONSTRAINT PK_MigrationMount_Id PRIMARY KEY
                                        CONSTRAINT NN_MigrationMount_Id NOT NULL,
620
                             startTime NUMBER CONSTRAINT NN_MigrationMount_startTime NOT NULL,
621
                             VID VARCHAR2(2048) CONSTRAINT UN_MigrationMount_VID UNIQUE USING INDEX,
622
623
624
                             label VARCHAR2(2048),
                             density VARCHAR2(2048),
                             lastFseq INTEGER,
625
                             full INTEGER,
626
627
628
629
630
631
                             lastVDQMPingTime NUMBER CONSTRAINT NN_MigrationMount_lastVDQMPing NOT NULL,
                             tapePool INTEGER CONSTRAINT NN_MigrationMount_TapePool NOT NULL,
                             status INTEGER CONSTRAINT NN_MigrationMount_Status NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
CREATE INDEX I_MigrationMount_TapePool ON MigrationMount(tapePool); 
ALTER TABLE MigrationMount ADD CONSTRAINT FK_MigrationMount_TapePool
632
  FOREIGN KEY (tapePool) REFERENCES TapePool(id);
633
BEGIN
634
635
636
637
  setObjStatusName('MigrationMount', 'status', tconst.MIGRATIONMOUNT_WAITTAPE, 'MIGRATIONMOUNT_WAITTAPE');
  setObjStatusName('MigrationMount', 'status', tconst.MIGRATIONMOUNT_SEND_TO_VDQM, 'MIGRATIONMOUNT_SEND_TO_VDQM');
  setObjStatusName('MigrationMount', 'status', tconst.MIGRATIONMOUNT_WAITDRIVE, 'MIGRATIONMOUNT_WAITDRIVE');
  setObjStatusName('MigrationMount', 'status', tconst.MIGRATIONMOUNT_MIGRATING, 'MIGRATIONMOUNT_MIGRATING');
638
639
END;
/
640
641
642
ALTER TABLE MigrationMount
  ADD CONSTRAINT CK_MigrationMount_Status
  CHECK (status IN (0, 1, 2, 3));
643

644
645
646
647
648
649
650
651
652
653
654
655
/* Definition of the MigratedSegment table
 * This table lists segments existing on tape for the files being
 * migrating. This allows to avoid putting two copies of a given
 * file on the same tape.
 *   castorFile : the file concerned
 *   copyNb : the copy number of this segment
 *   VID : the tape on which this segment resides
 */
CREATE TABLE MigratedSegment(castorFile INTEGER CONSTRAINT NN_MigratedSegment_CastorFile NOT NULL,
                             copyNb INTEGER CONSTRAINT NN_MigratedSegment_CopyNb NOT NULL,
                             VID VARCHAR2(2048) CONSTRAINT NN_MigratedSegment_VID NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
656
CREATE UNIQUE INDEX I_MigratedSegment_CFCopyNbVID ON MigratedSegment(CastorFile, copyNb, VID);
657
ALTER TABLE MigratedSegment ADD CONSTRAINT FK_MigratedSegment_CastorFile
658
  FOREIGN KEY (castorFile) REFERENCES CastorFile(id);
659

660
661
662
/* Definition of the MigrationJob table
 *   fileSize : size of the file to be migrated, in bytes
 *   VID : tape on which the file is being migrated (when applicable)
663
664
665
 *   creationTime : time of creation of this MigrationJob, in seconds since the epoch.
 *                  In case the MigrationJob went through a "WAITINGONRECALL" status,
 *                  time when it (re)entered the "PENDING" state
666
 *   castorFile : the file to migrate
667
668
669
 *   originalVID :  in case of repack, the VID of the tape where the original copy is leaving
 *   originalCopyNb : in case of repack, the number of the original copy being replaced
 *   destCopyNb : the number of the new copy of the file to migrate to tape
670
671
672
 *   tapePool : the tape pool where to migrate
 *   nbRetry : the number of retries we already went through
 *   errorcode : the error we got on last try (if any)
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
673
 *   mountTransactionId : an identifier for the migration session that is handling this job (when applicable)
674
675
676
677
678
679
 *   fileTransactionId : an identifier for this migration job
 *   fSeq : the file sequence of the copy created on tape for this job (when applicable)
 *   status : the status of the migration job
 */
CREATE TABLE MigrationJob (fileSize INTEGER CONSTRAINT NN_MigrationJob_FileSize NOT NULL,
                           VID VARCHAR2(2048),
680
681
                           creationTime NUMBER CONSTRAINT NN_MigrationJob_CreationTime NOT NULL,
                           castorFile INTEGER CONSTRAINT NN_MigrationJob_CastorFile NOT NULL,
682
683
684
                           originalVID VARCHAR2(20),
                           originalCopyNb INTEGER,
                           destCopyNb INTEGER CONSTRAINT NN_MigrationJob_destcopyNb NOT NULL,
685
                           tapePool INTEGER CONSTRAINT NN_MigrationJob_TapePool NOT NULL,
686
                           nbRetries INTEGER DEFAULT 0 CONSTRAINT NN_MigrationJob_nbRetries NOT NULL,
687
                           mountTransactionId INTEGER,   -- this is NULL at the beginning
688
                           fileTransactionId INTEGER CONSTRAINT UN_MigrationJob_FileTrId UNIQUE USING INDEX,
689
                           fSeq INTEGER,
690
691
692
                           status INTEGER CONSTRAINT NN_MigrationJob_Status NOT NULL,
                           id INTEGER CONSTRAINT PK_MigrationJob_Id PRIMARY KEY 
                                      CONSTRAINT NN_MigrationJob_Id NOT NULL)
693
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
694
695
-- see comment in the RecallMount table about why we need this index
CREATE INDEX I_MigrationJob_MountTransId ON MigrationJob(mountTransactionId);
696
CREATE INDEX I_MigrationJob_CFVID ON MigrationJob(castorFile, VID);
697
CREATE INDEX I_MigrationJob_TapePoolSize ON MigrationJob(tapePool, fileSize);
698
CREATE UNIQUE INDEX I_MigrationJob_TPStatusId ON MigrationJob(tapePool, status, id);
699
CREATE UNIQUE INDEX I_MigrationJob_CFCopyNb ON MigrationJob(castorFile, destCopyNb);
700
701
ALTER TABLE MigrationJob ADD CONSTRAINT UN_MigrationJob_CopyNb
  UNIQUE (castorFile, destCopyNb) USING INDEX I_MigrationJob_CFCopyNb;
702
ALTER TABLE MigrationJob ADD CONSTRAINT FK_MigrationJob_CastorFile
703
  FOREIGN KEY (castorFile) REFERENCES CastorFile(id);
704
ALTER TABLE MigrationJob ADD CONSTRAINT FK_MigrationJob_TapePool
705
706
707
  FOREIGN KEY (tapePool) REFERENCES TapePool(id);
ALTER TABLE MigrationJob ADD CONSTRAINT FK_MigrationJob_MigrationMount
  FOREIGN KEY (mountTransactionId) REFERENCES MigrationMount(mountTransactionId);
708
ALTER TABLE MigrationJob ADD CONSTRAINT CK_MigrationJob_FS_Positive CHECK (fileSize > 0);
709
BEGIN
710
711
712
  setObjStatusName('MigrationJob', 'status', tconst.MIGRATIONJOB_PENDING, 'MIGRATIONJOB_PENDING');
  setObjStatusName('MigrationJob', 'status', tconst.MIGRATIONJOB_SELECTED, 'MIGRATIONJOB_SELECTED');
  setObjStatusName('MigrationJob', 'status', tconst.MIGRATIONJOB_WAITINGONRECALL, 'MIGRATIONJOB_WAITINGONRECALL');
713
714
END;
/
715
716
717
ALTER TABLE MigrationJob
  ADD CONSTRAINT CK_MigrationJob_Status
  CHECK (status IN (0, 1, 3));
718
719

/* Definition of the MigrationRouting table. Each line is a routing rule for migration jobs
720
 *   isSmallFile : whether this routing rule applies to small files. Null means it applies to all files
721
722
723
724
725
726
 *   copyNb : the copy number the routing rule applies to
 *   fileClass : the file class the routing rule applies to
 *   lastEditor : name of the last one that modified this routing rule.
 *   lastEditionTime : last time this routing rule was edited, in seconds since the epoch
 *   tapePool : the tape pool where to migrate files matching the above criteria
 */
727
CREATE TABLE MigrationRouting (isSmallFile INTEGER,
728
729
730
                               copyNb INTEGER CONSTRAINT NN_MigrationRouting_CopyNb NOT NULL,
                               fileClass INTEGER CONSTRAINT NN_MigrationRouting_FileClass NOT NULL,
                               lastEditor VARCHAR2(2048) CONSTRAINT NN_MigrationRouting_LastEditor NOT NULL,
731
                               lastEditionTime NUMBER CONSTRAINT NN_MigrationRouting_LastEdTime NOT NULL,
732
733
                               tapePool INTEGER CONSTRAINT NN_MigrationRouting_TapePool NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
734
735
-- see comment in the RecallMount table about why we need thess indexes
CREATE INDEX I_MigrationRouting_TapePool ON MigrationRouting(tapePool);
736
CREATE INDEX I_MigrationRouting_Rules ON MigrationRouting(fileClass, copyNb, isSmallFile);
737
738
ALTER TABLE MigrationRouting ADD CONSTRAINT UN_MigrationRouting_Rules
  UNIQUE (fileClass, copyNb, isSmallFile) USING INDEX I_MigrationRouting_Rules;
739
ALTER TABLE MigrationRouting ADD CONSTRAINT FK_MigrationRouting_FileClass
740
  FOREIGN KEY (fileClass) REFERENCES FileClass(id);
741
ALTER TABLE MigrationRouting ADD CONSTRAINT FK_MigrationRouting_TapePool
742
743
  FOREIGN KEY (tapePool) REFERENCES TapePool(id);

744
745
746
/* Temporary table used to bulk select next candidates for recall and migration */
CREATE GLOBAL TEMPORARY TABLE FilesToRecallHelper
 (fileId NUMBER, nsHost VARCHAR2(100), fileTransactionId NUMBER,
747
748
749
  filePath VARCHAR2(2048), blockId RAW(4), fSeq INTEGER, copyNb INTEGER,
  euid NUMBER, egid NUMBER, VID VARCHAR2(10), fileSize INTEGER, creationTime INTEGER,
  nbRetriesInMount INTEGER, nbMounts INTEGER)
750
751
 ON COMMIT DELETE ROWS;

752
CREATE GLOBAL TEMPORARY TABLE FilesToMigrateHelper
753
754
 (fileId NUMBER CONSTRAINT UN_FilesToMigrateHelper_fileId UNIQUE,
  nsHost VARCHAR2(100), lastKnownFileName VARCHAR2(2048), filePath VARCHAR2(2048),
755
756
  fileTransactionId NUMBER, fileSize NUMBER, fSeq INTEGER)
 ON COMMIT DELETE ROWS;
757

758
759
760
761
762
763
/* The following would be a temporary table, except that as it is used through a distributed
   transaction and Oracle does not support temporary tables in such context, it is defined as
   a normal table. See ns_setOrReplaceSegments for more details */
CREATE TABLE FileMigrationResultsHelper
 (reqId VARCHAR2(36), fileId NUMBER, lastModTime NUMBER, copyNo NUMBER, oldCopyNo NUMBER, transfSize NUMBER,
  comprSize NUMBER, vid VARCHAR2(6), fSeq NUMBER, blockId RAW(4), checksumType VARCHAR2(16), checksum NUMBER);
764
CREATE INDEX I_FileMigResultsHelper_ReqId ON FileMigrationResultsHelper(ReqId);
765

Sebastien Ponce's avatar
Sebastien Ponce committed
766
/* SQL statements for type DiskServer */
767
CREATE TABLE DiskServer (name VARCHAR2(2048), lastHeartbeatTime NUMBER, id INTEGER CONSTRAINT PK_DiskServer_Id PRIMARY KEY, status INTEGER, hwOnline INTEGER DEFAULT 0) INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
768
CREATE UNIQUE INDEX I_DiskServer_name ON DiskServer (name);
Sebastien Ponce's avatar
Sebastien Ponce committed
769
770
ALTER TABLE DiskServer MODIFY
  (status CONSTRAINT NN_DiskServer_Status NOT NULL,
771
772
   name CONSTRAINT NN_DiskServer_Name NOT NULL,
   hwOnline CONSTRAINT NN_DiskServer_hwOnline NOT NULL);
Sebastien Ponce's avatar
Sebastien Ponce committed
773
774
775
ALTER TABLE DiskServer ADD CONSTRAINT UN_DiskServer_Name UNIQUE (name);

BEGIN
776
777
778
779
  setObjStatusName('DiskServer', 'status', dconst.DISKSERVER_PRODUCTION, 'DISKSERVER_PRODUCTION');
  setObjStatusName('DiskServer', 'status', dconst.DISKSERVER_DRAINING, 'DISKSERVER_DRAINING');
  setObjStatusName('DiskServer', 'status', dconst.DISKSERVER_DISABLED, 'DISKSERVER_DISABLED');
  setObjStatusName('DiskServer', 'status', dconst.DISKSERVER_READONLY, 'DISKSERVER_READONLY');
Sebastien Ponce's avatar
Sebastien Ponce committed
780
781
END;
/
782
783
784
ALTER TABLE DiskServer
  ADD CONSTRAINT CK_DiskServer_Status
  CHECK (status IN (0, 1, 2, 3));
Sebastien Ponce's avatar
Sebastien Ponce committed
785
786

/* SQL statements for type FileSystem */
Sebastien Ponce's avatar
Sebastien Ponce committed
787
CREATE TABLE FileSystem (free INTEGER, mountPoint VARCHAR2(2048), minAllowedFreeSpace NUMBER, maxFreeSpace NUMBER, totalSize INTEGER, nbReadStreams NUMBER, nbWriteStreams NUMBER, nbMigratorStreams NUMBER, nbRecallerStreams NUMBER, id INTEGER CONSTRAINT PK_FileSystem_Id PRIMARY KEY, diskPool INTEGER, diskserver INTEGER, status INTEGER) INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
Sebastien Ponce's avatar
Sebastien Ponce committed
788
789
790
791
792
793
794
795
796
797
798
799
ALTER TABLE FileSystem ADD CONSTRAINT FK_FileSystem_DiskServer 
  FOREIGN KEY (diskServer) REFERENCES DiskServer(id);
ALTER TABLE FileSystem MODIFY
  (status     CONSTRAINT NN_FileSystem_Status NOT NULL,
   diskServer CONSTRAINT NN_FileSystem_DiskServer NOT NULL,
   mountPoint CONSTRAINT NN_FileSystem_MountPoint NOT NULL);
ALTER TABLE FileSystem ADD CONSTRAINT UN_FileSystem_DSMountPoint
  UNIQUE (diskServer, mountPoint);
CREATE INDEX I_FileSystem_DiskPool ON FileSystem (diskPool);
CREATE INDEX I_FileSystem_DiskServer ON FileSystem (diskServer);

BEGIN
800
801
802
803
  setObjStatusName('FileSystem', 'status', dconst.FILESYSTEM_PRODUCTION, 'FILESYSTEM_PRODUCTION');
  setObjStatusName('FileSystem', 'status', dconst.FILESYSTEM_DRAINING, 'FILESYSTEM_DRAINING');
  setObjStatusName('FileSystem', 'status', dconst.FILESYSTEM_DISABLED, 'FILESYSTEM_DISABLED');
  setObjStatusName('FileSystem', 'status', dconst.FILESYSTEM_READONLY, 'FILESYSTEM_READONLY');
Sebastien Ponce's avatar
Sebastien Ponce committed
804
805
END;
/
806
807
808
ALTER TABLE FileSystem
  ADD CONSTRAINT CK_FileSystem_Status
  CHECK (status IN (0, 1, 2, 3));
Sebastien Ponce's avatar
Sebastien Ponce committed
809
810
811
812
813
814

/* SQL statements for type DiskPool */
CREATE TABLE DiskPool (name VARCHAR2(2048), id INTEGER CONSTRAINT PK_DiskPool_Id PRIMARY KEY) INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
CREATE TABLE DiskPool2SvcClass (Parent INTEGER, Child INTEGER) INITRANS 50 PCTFREE 50;
CREATE INDEX I_DiskPool2SvcClass_C on DiskPool2SvcClass (child);
CREATE INDEX I_DiskPool2SvcClass_P on DiskPool2SvcClass (parent);
815
816
817
ALTER TABLE DiskPool2SvcClass
  ADD CONSTRAINT FK_DiskPool2SvcClass_P FOREIGN KEY (Parent) REFERENCES DiskPool (id)
  ADD CONSTRAINT FK_DiskPool2SvcClass_C FOREIGN KEY (Child) REFERENCES SvcClass (id);
Sebastien Ponce's avatar
Sebastien Ponce committed
818

819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
/* DiskCopy Table
   - importance : the importance of this DiskCopy. The importance is always negative and the
     algorithm to compute it is -nb_disk_copies-100*at_least_a_tape_copy_exists
*/
CREATE TABLE DiskCopy
 (path VARCHAR2(2048),
  gcWeight NUMBER,
  creationTime INTEGER,
  lastAccessTime INTEGER,
  diskCopySize INTEGER,
  nbCopyAccesses NUMBER,
  owneruid NUMBER,
  ownergid NUMBER,
  id INTEGER CONSTRAINT PK_DiskCopy_Id PRIMARY KEY,
  gcType INTEGER,
  fileSystem INTEGER,
  castorFile INTEGER,
  status INTEGER,
  importance INTEGER CONSTRAINT NN_DiskCopy_Importance NOT NULL)
INITRANS 50 PCTFREE 50 ENABLE ROW MOVEMENT;
839

840
841
CREATE INDEX I_DiskCopy_Castorfile ON DiskCopy (castorFile);
CREATE INDEX I_DiskCopy_FileSystem ON DiskCopy (fileSystem);
842
CREATE INDEX I_DiskCopy_FS_GCW ON DiskCopy (fileSystem, gcWeight);
843
844
-- for queries on active statuses
CREATE INDEX I_DiskCopy_Status_6 ON DiskCopy (decode(status,6,status,NULL));
845
CREATE INDEX I_DiskCopy_Status_7_FS ON DiskCopy (decode(status,7,status,NULL), fileSystem);
846
CREATE INDEX I_DiskCopy_Status_9 ON DiskCopy (decode(status,9,status,NULL));
847
848
-- to speed up deleteOutOfDateStageOutDCs
CREATE INDEX I_DiskCopy_Status_Open ON DiskCopy (decode(status,6,status,decode(status,5,status,decode(status,11,status,NULL))));
849
-- to speed up draining manager job
Sebastien Ponce's avatar
Sebastien Ponce committed
850
CREATE INDEX I_DiskCopy_FS_ST_Impor_ID_CF_S ON DiskCopy (filesystem, status, importance, id, castorFile, diskCopySize);
851

852
853
854
855
/* DiskCopy constraints */
ALTER TABLE DiskCopy MODIFY (nbCopyAccesses DEFAULT 0);
ALTER TABLE DiskCopy MODIFY (gcType DEFAULT NULL);
ALTER TABLE DiskCopy ADD CONSTRAINT FK_DiskCopy_CastorFile
856
  FOREIGN KEY (castorFile) REFERENCES CastorFile (id);
857
858
859
ALTER TABLE DiskCopy
  MODIFY (status CONSTRAINT NN_DiskCopy_Status NOT NULL);

Sebastien Ponce's avatar
Sebastien Ponce committed
860
BEGIN
861
862
863
864
865
866
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_AUTO, 'GCTYPE_AUTO');
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_USER, 'GCTYPE_USER');
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_TOOMANYREPLICAS, 'GCTYPE_TOOMANYREPLICAS');
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_DRAINING, 'GCTYPE_DRAINING');
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_NSSYNCH, 'GCTYPE_NSSYNCH');
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_OVERWRITTEN, 'GCTYPE_OVERWRITTEN');
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
867
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_ADMIN, 'GCTYPE_ADMIN');
868
869
870
871
872
873
874
875
  setObjStatusName('DiskCopy', 'gcType', dconst.GCTYPE_FAILEDD2D, 'GCTYPE_FAILEDD2D');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_VALID, 'DISKCOPY_VALID');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_FAILED, 'DISKCOPY_FAILED');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_WAITFS, 'DISKCOPY_WAITFS');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_STAGEOUT, 'DISKCOPY_STAGEOUT');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_INVALID, 'DISKCOPY_INVALID');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_BEINGDELETED, 'DISKCOPY_BEINGDELETED');
  setObjStatusName('DiskCopy', 'status', dconst.DISKCOPY_WAITFS_SCHEDULING, 'DISKCOPY_WAITFS_SCHEDULING');
Sebastien Ponce's avatar
Sebastien Ponce committed
876
877
END;
/
878
879
880
881
882
ALTER TABLE DiskCopy
  ADD CONSTRAINT CK_DiskCopy_Status
  CHECK (status IN (0, 4, 5, 6, 7, 9, 10, 11));
ALTER TABLE DiskCopy
  ADD CONSTRAINT CK_DiskCopy_GcType
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
883
  CHECK (gcType IN (0, 1, 2, 3, 4, 5, 6, 7));
884
885
886
887
888
889
890

CREATE INDEX I_StagePTGRequest_ReqId ON StagePrepareToGetRequest (reqId);
CREATE INDEX I_StagePTPRequest_ReqId ON StagePrepareToPutRequest (reqId);
CREATE INDEX I_StagePTURequest_ReqId ON StagePrepareToUpdateRequest (reqId);
CREATE INDEX I_StageGetRequest_ReqId ON StageGetRequest (reqId);
CREATE INDEX I_StagePutRequest_ReqId ON StagePutRequest (reqId);

891
892
893
/* Improve query execution in the checkFailJobsWhenNoSpace function */
CREATE INDEX I_StagePutRequest_SvcClass ON StagePutRequest (svcClass);

894
/* Indexing GCFile by Request */
895
896
897
898
899
900
901
CREATE INDEX I_GCFile_Request ON GCFile (request);

/* An index to speed up queries in FileQueryRequest, FindRequestRequest, RequestQueryRequest */
CREATE INDEX I_QueryParameter_Query ON QueryParameter (query);

/* Constraint on FileClass name */
ALTER TABLE FileClass ADD CONSTRAINT UN_FileClass_Name UNIQUE (name);
902
903
ALTER TABLE FileClass MODIFY (name CONSTRAINT NN_FileClass_Name NOT NULL);
ALTER TABLE FileClass MODIFY (classId CONSTRAINT NN_FileClass_ClassId NOT NULL);
904

905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
/* Custom type to handle int arrays */
CREATE OR REPLACE TYPE "numList" IS TABLE OF INTEGER;
/

/* Custom type to handle float arrays */
CREATE OR REPLACE TYPE floatList IS TABLE OF NUMBER;
/

/* Custom type to handle strings returned by pipelined functions */
CREATE OR REPLACE TYPE strListTable AS TABLE OF VARCHAR2(2048);
/

/* SvcClass constraints */
ALTER TABLE SvcClass MODIFY (gcPolicy DEFAULT 'default');

/* DiskCopy constraints */
ALTER TABLE DiskCopy MODIFY (nbCopyAccesses DEFAULT 0);

ALTER TABLE DiskCopy MODIFY (gcType DEFAULT NULL);

925
926
927
928
/* DiskPool2SvcClass constraints */
ALTER TABLE DiskPool2SvcClass ADD CONSTRAINT PK_DiskPool2SvcClass_PC
  PRIMARY KEY (parent, child);

929
930
/* Global temporary table to handle output of the filesDeletedProc procedure */
CREATE GLOBAL TEMPORARY TABLE FilesDeletedProcOutput
931
  (fileId NUMBER, nsHost VARCHAR2(2048))
932
933
  ON COMMIT PRESERVE ROWS;

934
/* Global temporary table to store castorfile and diskcopy ids temporarily in the filesDeletedProc procedure */
935
CREATE GLOBAL TEMPORARY TABLE FilesDeletedProcHelper
936
  (cfId NUMBER, dcId NUMBER)
937
938
939
940
941
942
943
944
945
946
947
948
  ON COMMIT DELETE ROWS;

/* Global temporary table to handle output of the nsFilesDeletedProc procedure */
CREATE GLOBAL TEMPORARY TABLE NsFilesDeletedOrphans
  (fileid NUMBER)
  ON COMMIT DELETE ROWS;

/* Global temporary table to handle output of the stgFilesDeletedProc procedure */
CREATE GLOBAL TEMPORARY TABLE StgFilesDeletedOrphans
  (diskCopyId NUMBER)
  ON COMMIT DELETE ROWS;

949
/* Global temporary table to handle output of the processBulkAbortForGet procedure */
950
CREATE GLOBAL TEMPORARY TABLE ProcessBulkAbortFileReqsHelper
951
  (srId NUMBER, cfId NUMBER, fileId NUMBER, nsHost VARCHAR2(2048), uuid VARCHAR(2048))
952
953
954
  ON COMMIT PRESERVE ROWS;
ALTER TABLE ProcessBulkAbortFileReqsHelper
  ADD CONSTRAINT PK_ProcessBulkAbortFileRe_SrId PRIMARY KEY (srId);
955
956
957
958
959
960

/* Global temporary table to handle output of the processBulkRequest procedure */
CREATE GLOBAL TEMPORARY TABLE ProcessBulkRequestHelper
  (fileId NUMBER, nsHost VARCHAR2(2048), errorCode NUMBER, errorMessage VARCHAR2(2048))
  ON COMMIT PRESERVE ROWS;

961
962
963
964
965
/* Global temporary table to handle bulk update of subrequests in processBulkAbortForRepack */
CREATE GLOBAL TEMPORARY TABLE ProcessRepackAbortHelperSR (srId NUMBER) ON COMMIT DELETE ROWS;
/* Global temporary table to handle bulk update of diskCopies in processBulkAbortForRepack */
CREATE GLOBAL TEMPORARY TABLE ProcessRepackAbortHelperDCmigr (cfId NUMBER) ON COMMIT DELETE ROWS;

966
/* Tables to log the DB activity */
967
968
969
970
971
CREATE TABLE DLFLogs
  (timeinfo NUMBER,
   uuid VARCHAR2(2048),
   priority INTEGER CONSTRAINT NN_DLFLogs_Priority NOT NULL,
   msg VARCHAR2(2048) CONSTRAINT NN_DLFLogs_Msg NOT NULL,
972
973
974
   fileId NUMBER,
   nsHost VARCHAR2(2048),
   source VARCHAR2(2048),
975
   params VARCHAR2(2048));
976
977
978
979
980
981
982
983
984
985
CREATE GLOBAL TEMPORARY TABLE DLFLogsHelper
  (timeinfo NUMBER,
   uuid VARCHAR2(2048),
   priority INTEGER,
   msg VARCHAR2(2048),
   fileId NUMBER,
   nsHost VARCHAR2(2048),
   source VARCHAR2(2048),
   params VARCHAR2(2048))
ON COMMIT DELETE ROWS;
986
CREATE INDEX I_DLFLogs_Msg ON DLFLogs(msg);
987

988
989
990
991
992
993
994
995
996
997
998
999
1000
/* Temporary table to handle removing of priviledges */
CREATE GLOBAL TEMPORARY TABLE RemovePrivilegeTmpTable
  (svcClass VARCHAR2(2048),
   euid NUMBER,
   egid NUMBER,
   reqType NUMBER)
  ON COMMIT DELETE ROWS;

/* Global temporary table to store ids temporarily in the bulkCreateObj procedures */
CREATE GLOBAL TEMPORARY TABLE BulkSelectHelper
  (objId NUMBER)
  ON COMMIT DELETE ROWS;

For faster browsing, not all history is shown. View entire blame