patch_2016-10-12.sqlite 4.02 KB
Newer Older
1
-- Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
nwillhoft's avatar
nwillhoft committed
2
-- Copyright [2016-2021] EMBL-European Bioinformatics Institute
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
-- 
--      http://www.apache.org/licenses/LICENSE-2.0
-- 
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

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

-- ---------------------------------------------------------------------------------------------------

CREATE TEMPORARY VIEW exp_ver AS SELECT "83" AS expected_version;

.bail ON

SELECT ('The patch only applies to schema version '
    || expected_version
    || ', but the current schema version is '
    || meta_value
    || ', skipping the rest.') AS ''
    FROM hive_meta JOIN exp_ver WHERE meta_key='hive_sql_schema_version' AND meta_value<>expected_version;

INSERT INTO hive_meta (meta_key, meta_value)
    SELECT hm.* FROM hive_meta AS hm JOIN exp_ver WHERE meta_key='hive_sql_schema_version' AND meta_value<>expected_version;

SELECT ('The patch seems to be compatible with schema version '
    || expected_version
    || ', applying the patch...') AS '' FROM exp_ver;


-- ----------------------------------<actual_patch> -------------------------------------------------

ALTER TABLE analysis_stats ADD COLUMN is_excluded SMALLINT NOT NULL DEFAULT 0;

ALTER TABLE log_message RENAME TO old_log_message__;
43
44
45
DROP INDEX log_message_worker_idx;
DROP INDEX log_message_job_idx;
DROP INDEX log_message_beekeeper_idx;
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
CREATE TABLE log_message (
    log_message_id          INTEGER     NOT NULL PRIMARY KEY AUTOINCREMENT,
    job_id                  INTEGER              DEFAULT NULL,
    role_id                 INTEGER              DEFAULT NULL,
    worker_id               INTEGER              DEFAULT NULL,
    beekeeper_id            INTEGER              DEFAULT NULL,
    when_logged             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    retry                   INTEGER              DEFAULT NULL,
    status                  TEXT        NOT NULL DEFAULT 'UNKNOWN', /* enum('UNKNOWN','SPECIALIZATION','COMPILATION','CLAIMED','READY','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','PASSED_ON') DEFAULT 'UNKNOWN' */
    msg                     TEXT,
    message_class           TEXT        NOT NULL DEFAULT 'INFO' /* enum('INFO', 'PIPELINE_CAUTION', 'PIPELINE_ERROR', 'WORKER_CAUTION', 'WORKER_ERROR') */
);
CREATE        INDEX log_message_worker_idx ON log_message (worker_id);
CREATE        INDEX log_message_job_idx ON log_message (job_id);
CREATE        INDEX log_message_beekeeper_idx ON log_message (beekeeper_id);
CREATE        INDEX message_class_idx ON log_message (message_class);

INSERT INTO log_message (log_message_id, job_id, role_id, worker_id, beekeeper_id, when_logged, retry, status, msg)
Matthieu Muffato's avatar
Matthieu Muffato committed
64
SELECT log_message_id, job_id, role_id, worker_id, beekeeper_id, when_logged, retry, status, msg
65
66
67
68
FROM old_log_message__
WHERE is_error = 0;

INSERT INTO log_message (log_message_id, job_id, role_id, worker_id, beekeeper_id, when_logged, retry, status, msg, message_class)
Matthieu Muffato's avatar
Matthieu Muffato committed
69
SELECT log_message_id, job_id, role_id, worker_id, beekeeper_id, when_logged, retry, status, msg, 'PIPELINE_ERROR'
70
71
72
73
74
75
76
77
78
79
80
81
FROM old_log_message__
WHERE is_error = 1;

DROP TABLE old_log_message__;

ALTER TABLE analysis_stats_monitor ADD COLUMN is_excluded SMALLINT NOT NULL DEFAULT 0;

-- ----------------------------------</actual_patch> -------------------------------------------------


    -- increase the schema version by one:
UPDATE hive_meta SET meta_value=meta_value+1 WHERE meta_key='hive_sql_schema_version';
82
INSERT INTO hive_meta (meta_key, meta_value) SELECT 'patched_to_' || meta_value, CURRENT_TIMESTAMP FROM hive_meta WHERE meta_key = 'hive_sql_schema_version';