Project

General

Profile

« Previous | Next » 

Revision 4975

Added by daigle over 14 years ago

Add upgrade scripts for 1.9.2 (scheduler tables)

View differences:

lib/metacat.properties
68 68
database.upgradeVersion.1.8.0=upgrade-db-to-1.8
69 69
database.upgradeVersion.1.9.0=upgrade-db-to-1.9
70 70
database.upgradeVersion.1.9.1=upgrade-db-to-1.9.1
71
database.upgradeVersion.1.9.2=upgrade-db-to-1.9.2
71 72
database.initialConnections=5
72 73
database.incrementConnections=5
73 74
database.maximumConnections=200
src/xmltables-oracle.sql
576 576
END;
577 577
/
578 578

  
579
/*
580
 * scheduled_job -- table to store scheduled jobs
581
 */
582
CREATE TABLE scheduled_job (
583
  id NUMBER(20) NOT NULL default nextval('scheduled_job_id_seq'),
584
  date_created TIMESTAMP NOT NULL,
585
  date_updated TIMESTAMP NOT NULL,
586
  status VARCHAR2(64) NOT NULL,
587
  name VARCHAR2(512) NOT NULL,
588
  trigger_name VARCHAR2(512) NOT NULL,
589
  group_name VARCHAR2(512) NOT NULL,
590
  class_name VARCHAR2(1024) NOT NULL,
591
  start_time TIMESTAMP NOT NULL,
592
  interval_value NUMBER NOT NULL,
593
  interval_unit VARCHAR2(8) NOT NULL,
594
  CONSTRAINT scheduled_job_pk PRIMARY KEY (id),
595
  CONSTRAINT scheduled_job_uk UNIQUE (name)
596
);
597

  
598
CREATE SEQUENCE scheduled_job_id_seq;
599
CREATE TRIGGER scheduled_job_before_insert
600
BEFORE INSERT ON scheduled_job FOR EACH ROW
601
BEGIN
602
  SELECT scheduled_job_id_seq.nextval
603
    INTO :new.id
604
    FROM dual;
605
END;
606
/
607

  
608
/*
609
 * scheduled_job_params -- table to store scheduled job parameters
610
 */
611
CREATE TABLE scheduled_job_params (
612
  id NUMBER(20)  NOT NULL default nextval('scheduled_job_params_id_seq'),
613
  date_created TIMESTAMP NOT NULL,
614
  date_updated TIMESTAMP  NOT NULL,
615
  status VARCHAR2(64)  NOT NULL,
616
  job_id NUMBER(20) NOT NULL,
617
  key VARCHAR2(64) NOT NULL,
618
  value VARCHAR2(1024) NOT NULL,
619
  CONSTRAINT scheduled_job_params_pk PRIMARY KEY (id),
620
  CONSTRAINT scheduled_job_params_fk
621
        FOREIGN KEY (job_id) REFERENCES scheduled_job(id)
622
);
623

  
624
CREATE SEQUENCE scheduled_job_params_id_seq;
625
CREATE TRIGGER scheduled_job_params_before_insert
626
BEFORE INSERT ON scheduled_job_params FOR EACH ROW
627
BEGIN
628
  SELECT scheduled_job_id_params_seq.nextval
629
    INTO :new.id
630
    FROM dual;
631
END;
632
/
633

  
src/loaddtdschema-oracle.sql
51 51
  VALUES ('Schema', '/schema/RegistryService/RegistryEntryType.xsd', '/schema/RegistryService/RegistryEntryType.xsd');
52 52

  
53 53
INSERT INTO db_version (version, status, date_created) 
54
  VALUES ('1.9.1',1,CURRENT_DATE);
54
  VALUES ('1.9.2',1,CURRENT_DATE);
src/upgrade-db-to-1.9.2-oracle.sql
1
/*
2
 * scheduled_job -- table to store scheduled jobs
3
 */
4
CREATE TABLE scheduled_job (
5
  id NUMBER(20) NOT NULL default nextval('scheduled_job_id_seq'),
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 default nextval('scheduled_job_params_id_seq'),
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_before_insert
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);
63

  
64
UPDATE db_version SET status=0; 
src/xmltables-postgres.sql
427 427
  date_created    TIMESTAMP,        -- the datetime on which the version was created
428 428
  CONSTRAINT db_version_pk PRIMARY KEY (db_version_id)
429 429
);
430

  
431
/*
432
 * scheduled_job -- table to store scheduled jobs
433
 */
434
CREATE SEQUENCE scheduled_job_id_seq;
435
CREATE TABLE scheduled_job (
436
  id INT8 NOT NULL default nextval('scheduled_job_id_seq'),
437
  date_created TIMESTAMP NOT NULL,
438
  date_updated TIMESTAMP NOT NULL,
439
  status VARCHAR(64) NOT NULL,
440
  name VARCHAR(512) NOT NULL,
441
  trigger_name VARCHAR(512) NOT NULL,
442
  group_name VARCHAR(512) NOT NULL,
443
  class_name VARCHAR(1024) NOT NULL,
444
  start_time TIMESTAMP NOT NULL,
445
  interval_value INT NOT NULL,
446
  interval_unit VARCHAR(8) NOT NULL,
447
  CONSTRAINT scheduled_job_pk PRIMARY KEY (id),
448
  CONSTRAINT scheduled_job_uk UNIQUE (name)
449
);
450

  
451
/*
452
 * scheduled_job_params -- table to store scheduled jobs
453
 */
454
CREATE SEQUENCE scheduled_job_params_id_seq;
455
CREATE TABLE scheduled_job_params (
456
  id INT8  NOT NULL default nextval('scheduled_job_params_id_seq'),
457
  date_created TIMESTAMP NOT NULL,
458
  date_updated TIMESTAMP  NOT NULL,
459
  status VARCHAR(64)  NOT NULL,
460
  job_id INT8 NOT NULL,
461
  key VARCHAR(64) NOT NULL,
462
  value VARCHAR(1024) NOT NULL,
463
  CONSTRAINT scheduled_job_params_pk PRIMARY KEY (id),
464
  CONSTRAINT scheduled_job_params_fk
465
        FOREIGN KEY (job_id) REFERENCES scheduled_job(id)
466
);
src/loaddtdschema-postgres.sql
51 51
  VALUES ('Schema', 'http://ecoinformatics.org/registryentry-1.0.0', '/schema/RegistryService/RegistryEntryType.xsd');
52 52

  
53 53
INSERT INTO db_version (version, status, date_created) 
54
  VALUES ('1.9.1',1,CURRENT_DATE);
54
  VALUES ('1.9.2',1,CURRENT_DATE);
src/upgrade-db-to-1.9.2-postgres.sql
1
/*
2
 * scheduled_job -- table to store scheduled jobs
3
 */
4
CREATE SEQUENCE scheduled_job_id_seq;
5
CREATE TABLE scheduled_job (
6
  id INT8 NOT NULL default nextval('scheduled_job_id_seq'),
7
  date_created TIMESTAMP NOT NULL,
8
  date_updated TIMESTAMP NOT NULL,
9
  status VARCHAR(64) NOT NULL,
10
  name VARCHAR(512) NOT NULL,
11
  trigger_name VARCHAR(512) NOT NULL,
12
  group_name VARCHAR(512) NOT NULL,
13
  class_name VARCHAR(1024) NOT NULL,
14
  start_time TIMESTAMP NOT NULL,
15
  interval_value INT NOT NULL,
16
  interval_unit VARCHAR(8) NOT NULL,
17
  CONSTRAINT scheduled_job_pk PRIMARY KEY (id),
18
  CONSTRAINT scheduled_job_uk UNIQUE (name)
19
);
20

  
21
/*
22
 * scheduled_job_params -- table to store scheduled job parameters
23
 */
24
CREATE SEQUENCE scheduled_job_params_id_seq;
25
CREATE TABLE scheduled_job_params (
26
  id INT8  NOT NULL default nextval('scheduled_job_params_id_seq'),
27
  date_created TIMESTAMP NOT NULL,
28
  date_updated TIMESTAMP  NOT NULL,
29
  status VARCHAR(64)  NOT NULL,
30
  job_id INT8 NOT NULL,
31
  key VARCHAR(64) NOT NULL,
32
  value VARCHAR(1024) NOT NULL,
33
  CONSTRAINT scheduled_job_params_pk PRIMARY KEY (id),
34
  CONSTRAINT scheduled_job_params_fk
35
        FOREIGN KEY (job_id) REFERENCES scheduled_job(id)
36
);
37

  
38

  
39
/*
40
 * update the database version
41
 */
42
UPDATE db_version SET status=0;
43

  
44
INSERT INTO db_version (version, status, date_created) 
45
  VALUES ('1.9.2', 1, CURRENT_DATE);
src/edu/ucsb/nceas/metacat/admin/DBAdmin.java
415 415

  
416 416
			String dbVersionString = null;
417 417

  
418
			if (is1_9_0(connection)) {
418
			if (is1_9_1(connection)) {
419
				dbVersionString = "1.9.1";
420
			} else if (is1_9_0(connection)) {
419 421
				dbVersionString = "1.9.0";
420 422
			} else if (is1_8_0(connection)) {
421 423
				dbVersionString = "1.8.0";
......
538 540
	private boolean is1_9_0(Connection connection) throws SQLException {
539 541
		return DBUtil.tableExists(connection, "db_version");
540 542
	}
543
	
544
	/**
545
	 * Checks to see if this is a 1.9.1 database schema by looking for the
546
	 * scheduled_job table which was created for 1.9.0. Note, there is no guarantee
547
	 * that this table will not be removed in subsequent versions. You should
548
	 * search for db versions from newest to oldest, only getting to this
549
	 * function when newer versions have not been matched.
550
	 * 
551
	 * @param dbMetaData
552
	 *            the meta data for this database.
553
	 * @returns boolean which is true if table is found, false otherwise
554
	 */
555
	private boolean is1_9_1(Connection connection) throws SQLException {
556
		return DBUtil.tableExists(connection, "db_version");
557
	}
541 558

  
542 559
	/**
543 560
	 * Checks to see if this is a 1.8.0 database schema by looking for the
build.properties
2 2

  
3 3
#Version of this build.  This needs to be a dotted numeric version.  For
4 4
#instance 1.9.1 is okay.  1.9.1_rc1 is not.
5
metacat.version=1.9.1
5
metacat.version=1.9.2
6 6

  
7 7
#This is for packaging purposes.  leave it blank for final production release.
8 8
metacat.releaseCandidate=-6

Also available in: Unified diff