3.1to3.2.sql 13.5 KB
Newer Older
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
--liquibase formatted sql

--changeset ccaffy:1 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=3;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=2;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';

--changeset ccaffy:2 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
CREATE SEQUENCE FILE_RECYCLE_LOG_ID_SEQ
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;
--rollback DROP SEQUENCE FILE_RECYCLE_LOG_ID_SEQ;

--changeset ccaffy:3 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
CREATE TABLE FILE_RECYCLE_LOG(
  FILE_RECYCLE_LOG_ID        NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_ID_NN NOT NULL,
  VID                        VARCHAR2(100)        CONSTRAINT FILE_RECYCLE_LOG_VID_NN NOT NULL,
  FSEQ                       NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_FSEQ_NN NOT NULL,
  BLOCK_ID                   NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_BID_NN NOT NULL,
  COPY_NB                    NUMERIC(3, 0)           CONSTRAINT FILE_RECYCLE_LOG_COPY_NB_NN NOT NULL,
  TAPE_FILE_CREATION_TIME    NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_TFCT_NN NOT NULL,
  ARCHIVE_FILE_ID            NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_AFI_NN NOT NULL,
  DISK_INSTANCE_NAME         VARCHAR2(100)        CONSTRAINT FILE_RECYCLE_LOG_DIN_NN NOT NULL,
  DISK_FILE_ID               VARCHAR2(100)        CONSTRAINT FILE_RECYCLE_LOG_DFI_NN NOT NULL,
  DISK_FILE_ID_WHEN_DELETED  VARCHAR2(100)        CONSTRAINT FILE_RECYCLE_LOG_DFIWD_NN NOT NULL,
  DISK_FILE_UID              NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_DFU_NN NOT NULL,
  DISK_FILE_GID              NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_DFG_NN NOT NULL,
  SIZE_IN_BYTES              NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_SIB_NN NOT NULL,
  CHECKSUM_BLOB              RAW(200),
  CHECKSUM_ADLER32           NUMERIC(10, 0)          CONSTRAINT FILE_RECYCLE_LOG_CA_NN NOT NULL,
  STORAGE_CLASS_ID           NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_SCI_NN NOT NULL,
  ARCHIVE_FILE_CREATION_TIME NUMERIC(20, 0)          CONSTRAINT FILE_RECYLE_LOG_CT_NN NOT NULL,
  RECONCILIATION_TIME        NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_RT_NN NOT NULL,
  COLLOCATION_HINT           VARCHAR2(100),
  DISK_FILE_PATH             VARCHAR2(2000),
  REASON_LOG                 VARCHAR2(1000)       CONSTRAINT FILE_RECYCLE_LOG_RL_NN NOT NULL, 
  RECYCLE_LOG_TIME           NUMERIC(20, 0)          CONSTRAINT FILE_RECYCLE_LOG_RLT_NN NOT NULL,
  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)
);
--rollback DROP TABLE FILE_RECYCLE_LOG;

--changeset ccaffy:4 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
CREATE INDEX FILE_RECYCLE_LOG_DFI_IDX ON FILE_RECYCLE_LOG(DISK_FILE_ID);
--rollback DROP INDEX FILE_RECYCLE_LOG_DFI_IDX;

64
--changeset ccaffy:5 failOnError:true dbms:oracle runAlways:true
65
--preconditions onFail:HALT onError:HALT
66
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
67
68
69
70
71
INSERT INTO FILE_RECYCLE_LOG (
  FILE_RECYCLE_LOG_ID,
  VID,
  FSEQ,
  COPY_NB,
72
  BLOCK_ID,
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
  TAPE_FILE_CREATION_TIME,
  ARCHIVE_FILE_ID,
  DISK_INSTANCE_NAME,
  DISK_FILE_ID,
  DISK_FILE_ID_WHEN_DELETED,
  DISK_FILE_UID,
  DISK_FILE_GID,
  SIZE_IN_BYTES,
  CHECKSUM_BLOB,
  CHECKSUM_ADLER32,
  STORAGE_CLASS_ID,
  ARCHIVE_FILE_CREATION_TIME,
  RECONCILIATION_TIME,
  COLLOCATION_HINT,
  REASON_LOG, 
  RECYCLE_LOG_TIME
) SELECT
    FILE_RECYCLE_LOG_ID_SEQ.NEXTVAL,
    TAPE_FILE.VID,
    TAPE_FILE.FSEQ,
    TAPE_FILE.BLOCK_ID,
    TAPE_FILE.COPY_NB,
    TAPE_FILE.CREATION_TIME,
    TAPE_FILE.ARCHIVE_FILE_ID,
    ARCHIVE_FILE.DISK_INSTANCE_NAME,
    ARCHIVE_FILE.DISK_FILE_ID,
    ARCHIVE_FILE.DISK_FILE_ID,
    ARCHIVE_FILE.DISK_FILE_UID,
    ARCHIVE_FILE.DISK_FILE_GID,
    ARCHIVE_FILE.SIZE_IN_BYTES,
    ARCHIVE_FILE.CHECKSUM_BLOB,
    ARCHIVE_FILE.CHECKSUM_ADLER32,
    ARCHIVE_FILE.STORAGE_CLASS_ID,
    ARCHIVE_FILE.CREATION_TIME,
    ARCHIVE_FILE.RECONCILIATION_TIME,
    ARCHIVE_FILE.COLLOCATION_HINT,
    'REPACK',
    (cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400
  FROM
    TAPE_FILE JOIN
    ARCHIVE_FILE ON TAPE_FILE.ARCHIVE_FILE_ID = ARCHIVE_FILE.ARCHIVE_FILE_ID
  WHERE TAPE_FILE.SUPERSEDED_BY_VID IS NOT NULL;

116
--changeset ccaffy:6 failOnError:true dbms:oracle runAlways:true
117
--preconditions onFail:HALT onError:HALT
118
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
119
120
DELETE FROM TAPE_FILE WHERE SUPERSEDED_BY_VID IS NOT NULL;

121
--changeset ccaffy:7 failOnError:true dbms:oracle runAlways:true
122
--preconditions onFail:HALT onError:HALT
123
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
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
INSERT INTO FILE_RECYCLE_LOG (
  FILE_RECYCLE_LOG_ID,
  VID,
  FSEQ,
  BLOCK_ID,
  COPY_NB,
  TAPE_FILE_CREATION_TIME,
  ARCHIVE_FILE_ID,
  DISK_INSTANCE_NAME,
  DISK_FILE_ID,
  DISK_FILE_ID_WHEN_DELETED,
  DISK_FILE_UID,
  DISK_FILE_GID,
  SIZE_IN_BYTES,
  CHECKSUM_BLOB,
  CHECKSUM_ADLER32,
  STORAGE_CLASS_ID,
  ARCHIVE_FILE_CREATION_TIME,
  RECONCILIATION_TIME,
  COLLOCATION_HINT,
  DISK_FILE_PATH,
  REASON_LOG, 
  RECYCLE_LOG_TIME
) SELECT
    FILE_RECYCLE_LOG_ID_SEQ.NEXTVAL,
    TAPE_FILE_RECYCLE_BIN.VID,
    TAPE_FILE_RECYCLE_BIN.FSEQ,
    TAPE_FILE_RECYCLE_BIN.BLOCK_ID,
    TAPE_FILE_RECYCLE_BIN.COPY_NB,
    TAPE_FILE_RECYCLE_BIN.CREATION_TIME,
    TAPE_FILE_RECYCLE_BIN.ARCHIVE_FILE_ID,
    ARCHIVE_FILE_RECYCLE_BIN.DISK_INSTANCE_NAME,
    ARCHIVE_FILE_RECYCLE_BIN.DISK_FILE_ID,
    ARCHIVE_FILE_RECYCLE_BIN.DISK_FILE_ID_WHEN_DELETED,
    ARCHIVE_FILE_RECYCLE_BIN.DISK_FILE_UID,
    ARCHIVE_FILE_RECYCLE_BIN.DISK_FILE_GID,
    ARCHIVE_FILE_RECYCLE_BIN.SIZE_IN_BYTES,
    ARCHIVE_FILE_RECYCLE_BIN.CHECKSUM_BLOB,
    ARCHIVE_FILE_RECYCLE_BIN.CHECKSUM_ADLER32,
    ARCHIVE_FILE_RECYCLE_BIN.STORAGE_CLASS_ID,
    ARCHIVE_FILE_RECYCLE_BIN.CREATION_TIME,
    ARCHIVE_FILE_RECYCLE_BIN.RECONCILIATION_TIME,
    ARCHIVE_FILE_RECYCLE_BIN.COLLOCATION_HINT,
    ARCHIVE_FILE_RECYCLE_BIN.DISK_FILE_PATH,
    'Deleted file imported from the old recycle-bin',
    ARCHIVE_FILE_RECYCLE_BIN.DELETION_TIME
  FROM
    TAPE_FILE_RECYCLE_BIN JOIN
    ARCHIVE_FILE_RECYCLE_BIN ON TAPE_FILE_RECYCLE_BIN.ARCHIVE_FILE_ID = ARCHIVE_FILE_RECYCLE_BIN.ARCHIVE_FILE_ID;

174
--changeset ccaffy:8 failOnError:true dbms:oracle runAlways:true
175
--preconditions onFail:HALT onError:HALT
176
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
177
178
DELETE FROM TAPE_FILE_RECYCLE_BIN;

179
--changeset ccaffy:9 failOnError:true dbms:oracle runAlways:true
180
--preconditions onFail:HALT onError:HALT
181
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
182
183
DELETE FROM ARCHIVE_FILE_RECYCLE_BIN;

184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
--changeset ccaffy:11 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE TAPE ADD (
  TAPE_STATE VARCHAR2(100),
  STATE_REASON VARCHAR2(1000),
  STATE_UPDATE_TIME NUMERIC(20, 0),
  STATE_MODIFIED_BY VARCHAR2(100),
  CONSTRAINT TAPE_STATE_CK CHECK(TAPE_STATE IN ('ACTIVE', 'DISABLED', 'BROKEN'))
);

--changeset ccaffy:12 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
CREATE INDEX TAPE_STATE_IDX ON TAPE(TAPE_STATE);

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
228
229
--changeset ccaffy:13 failOnError:true dbms:oracle runAlways:true
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
UPDATE TAPE 
SET TAPE_STATE='ACTIVE',
STATE_UPDATE_TIME=(cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400,
STATE_MODIFIED_BY='Migration from CTA 3.1 to 4.0'
WHERE TAPE.IS_DISABLED = '0' AND (TAPE.TAPE_STATE IS NULL OR TAPE.TAPE_STATE <> 'ACTIVE');

--changeset ccaffy:14 failOnError:true dbms:oracle runAlways:true
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
UPDATE TAPE
SET TAPE_STATE='DISABLED',
STATE_REASON=TAPE.USER_COMMENT,
STATE_UPDATE_TIME=(cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400,
STATE_MODIFIED_BY='Migration from CTA 3.1 to 4.0'
WHERE TAPE.IS_DISABLED = '1' AND (TAPE.TAPE_STATE IS NULL OR TAPE.TAPE_STATE != 'DISABLED') AND TAPE.USER_COMMENT NOT LIKE '%- BROKEN -%';

--changeset ccaffy:15 failOnError:true dbms:oracle runAlways:true
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1" SELECT COUNT(*) FROM CTA_CATALOGUE WHERE SCHEMA_VERSION_MAJOR = 3 AND (SCHEMA_VERSION_MINOR = 1 OR SCHEMA_VERSION_MINOR = 2);
UPDATE TAPE
SET TAPE_STATE='BROKEN',
STATE_REASON=TAPE.USER_COMMENT,
STATE_UPDATE_TIME=(cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400,
STATE_MODIFIED_BY='Migration from CTA 3.1 to 4.0'
WHERE TAPE.IS_DISABLED = '1' AND (TAPE.TAPE_STATE IS NULL OR TAPE.TAPE_STATE != 'BROKEN') AND TAPE.USER_COMMENT LIKE '%- BROKEN -%';

--changeset ccaffy:16 failOnError:true dbms:oracle
230
231
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
232
233
234
ALTER TABLE TAPE DROP COLUMN IS_ARCHIVED;
ALTER TABLE TAPE DROP COLUMN IS_EXPORTED;

235
--changeset ccaffy:17 failOnError:true dbms:oracle
236
237
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
238
239
240
241
242
ALTER TABLE TAPE DROP CONSTRAINT TAPE_ID_NN;

--changeset ccaffy:18 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
243
244
245
246
247
ALTER TABLE TAPE DROP CONSTRAINT TAPE_IRO_NN;

--changeset ccaffy:19 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
248
249
250
251
252
253
254
255
ALTER TABLE VIRTUAL_ORGANIZATION ADD (
  READ_MAX_DRIVES NUMERIC(20,0),
  WRITE_MAX_DRIVES NUMERIC(20,0)
);

--changeset ccaffy:20 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
256
257
ALTER TABLE MOUNT_POLICY DROP CONSTRAINT MOUNT_POLICY_MDA_NN;

258
--changeset ccaffy:21 failOnError:true dbms:oracle
259
260
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"3.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
261
262
263
264
265
266
267
268
269
270
UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MAJOR=3;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=2;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MAJOR=3;
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=1;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=3;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=2;