Project

General

Profile

metacat / src / upgrade-db-to-1.9.2-oracle.sql @ 8535

1
/*
2
 * scheduled_job -- table to store scheduled jobs
3
 */
4
CREATE TABLE scheduled_job (
5
  id NUMBER(20) NOT NULL default ,
6
  date_created TIMESTAMP NOT NULL,
7
  date_updated TIMESTAMP NOT NULL,
8
  status VARCHAR2(64) NOT NULL,
9
  name VARCHAR2(512) NOT NULL,
10
  trigger_name VARCHAR2(512) NOT NULL,
11
  group_name VARCHAR2(512) NOT NULL,
12
  class_name VARCHAR2(1024) NOT NULL,
13
  start_time TIMESTAMP NOT NULL,
14
  interval_value NUMBER NOT NULL,
15
  interval_unit VARCHAR2(8) NOT NULL,
16
  CONSTRAINT scheduled_job_pk PRIMARY KEY (id),
17
  CONSTRAINT scheduled_job_uk UNIQUE (name)
18
);
19

    
20
CREATE SEQUENCE scheduled_job_id_seq;
21
CREATE TRIGGER scheduled_job_before_insert
22
BEFORE INSERT ON scheduled_job FOR EACH ROW
23
BEGIN
24
  SELECT scheduled_job_id_seq.nextval
25
    INTO :new.id
26
    FROM dual;
27
END;
28
/
29

    
30
/*
31
 * scheduled_job_params -- table to store scheduled job parameters
32
 */
33
CREATE TABLE scheduled_job_params (
34
  id NUMBER(20)  NOT NULL ,
35
  date_created TIMESTAMP NOT NULL,
36
  date_updated TIMESTAMP  NOT NULL,
37
  status VARCHAR2(64)  NOT NULL,
38
  job_id NUMBER(20) NOT NULL,
39
  key VARCHAR2(64) NOT NULL,
40
  value VARCHAR2(1024) NOT NULL,
41
  CONSTRAINT scheduled_job_params_pk PRIMARY KEY (id),
42
  CONSTRAINT scheduled_job_params_fk
43
        FOREIGN KEY (job_id) REFERENCES scheduled_job(id)
44
);
45

    
46
CREATE SEQUENCE scheduled_job_params_id_seq;
47
CREATE TRIGGER scheduled_job_params_bef_ins
48
BEFORE INSERT ON scheduled_job_params FOR EACH ROW
49
BEGIN
50
  SELECT scheduled_job_id_params_seq.nextval
51
    INTO :new.id
52
    FROM dual;
53
END;
54
/
55

    
56
/*
57
 * update the database version
58
 */
59
UPDATE db_version SET status=0;
60

    
61
INSERT INTO db_version (version, status, date_created) 
62
  VALUES ('1.9.2', 1, CURRENT_DATE);