Project

General

Profile

Revision 2769

Added by Matt Jones about 15 years ago

New SQL tables and db upgrade scripts that define a new table stucture for identifiers and delete the previously unused accession_number table. This is in anticipation of supporting LSID identifiers. Currently these tables are unused by the servlet but are in place for discussion.

View differences:

src/upgrade-db-to-1.6.sql
22 22
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
23 23
 */
24 24
 
25
/*
26
 * Changes to the tables for handling identifiers.  Old table no longer needed,
27
 * new identifier table to be used to support LSIDs.
28
 */
29
DROP TABLE accession_number;
30
DROP SEQUENCE accession_number_id_seq;
25 31

  
26 32
/*
33
 * Table used to store all document identifiers in metacat.  Each identifier
34
 * consists of 4 subparts, an authority, namespace, object, and revision as
35
 * defined in the LSID specification.
36
 */
37
CREATE SEQUENCE identifier_id_seq;
38
CREATE TABLE identifier (
39
   id        NUMBER(20) PRIMARY KEY, -- primary key
40
   authority VARCHAR2(255),  -- the authority issuing the identifier
41
   namespace VARCHAR2(255),  -- the namespace qualifying the identifier
42
   object    VARCHAR2(255),  -- the local part of the identifier for a particular object
43
   revision  VARCHAR2(255)   -- the revision part of the identifier
44
);
45
CREATE TRIGGER identifier_before_insert
46
BEFORE INSERT ON identifier FOR EACH ROW
47
BEGIN
48
  SELECT identifier_id_seq.nextval
49
    INTO :new.id
50
    FROM dual;
51
END;
52
/
53

  
54
/*
27 55
 * Index of Paths - table to store nodes with paths specified by userst in metacat.properties
28 56
 */
29 57
CREATE TABLE xml_path_index (
src/drop-postgres.sql
39 39
DROP SEQUENCE xml_relation_id_seq;
40 40
DROP SEQUENCE xml_replication_id_seq;
41 41
DROP SEQUENCE xml_documents_id_seq;
42
DROP SEQUENCE accession_number_id_seq;
42
DROP SEQUENCE identifier_id_seq;
43 43
DROP SEQUENCE access_log_id_seq;
44 44
DROP SEQUENCE xml_queryresult_id_seq;
45 45
DROP SEQUENCE xml_returnfield_id_seq;
......
55 55
DROP TABLE xml_nodes_revisions;
56 56
DROP TABLE xml_replication;
57 57
DROP TABLE xml_catalog;
58
DROP TABLE accession_number;
58
DROP TABLE identifier;
59 59
DROP TABLE access_log;
60 60
DROP TABLE harvest_site_schedule;
61 61
DROP TABLE harvest_detail_log;
src/xmltables.sql
32 32
DROP SEQUENCE xml_catalog_id_seq;
33 33
DROP SEQUENCE xml_relation_id_seq;
34 34
DROP SEQUENCE xml_replication_id_seq;
35
DROP SEQUENCE accession_number_id_seq;
35
DROP SEQUENCE identifier_id_seq;
36 36
DROP SEQUENCE access_log_id_seq;
37 37
DROP SEQUENCE xml_returnfield_id_seq;
38 38
DROP SEQUENCE xml_queryresult_id_seq;
......
44 44
DROP TRIGGER xml_catalog_before_insert;
45 45
DROP TRIGGER xml_relation_before_insert;
46 46
DROP TRIGGER xml_replication_before_insert;
47
DROP TRIGGER accession_number_before_insert;
47
DROP TRIGGER identifier_before_insert;
48 48
DROP TRIGGER access_log_before_insert;
49 49
DROP TRIGGER xml_returnfield_before_insert;
50 50
DROP TRIGGER xml_queryresult_before_insert;
......
60 60
DROP TABLE xml_nodes_revisions;
61 61
DROP TABLE xml_replication;
62 62
DROP TABLE xml_catalog;
63
DROP TABLE accession_number;
63
DROP TABLE identifier;
64 64
DROP TABLE access_log;
65 65
DROP TABLE harvest_site_schedule;
66 66
DROP TABLE harvest_detail_log;
......
383 383
/
384 384

  
385 385
/*
386
 * Table used as Unique ID generator for the uniqueid part of Accession#
386
 * Table used to store all document identifiers in metacat.  Each identifier
387
 * consists of 4 subparts, an authority, namespace, object, and revision as
388
 * defined in the LSID specification.
387 389
 */
388
CREATE SEQUENCE accession_number_id_seq;
389
CREATE TABLE accession_number (
390
	uniqueid	NUMBER(20) PRIMARY KEY,
391
	site_code	VARCHAR2(100),
392
	date_created	DATE
390
CREATE SEQUENCE identifier_id_seq;
391
CREATE TABLE identifier (
392
   id        NUMBER(20) PRIMARY KEY, -- primary key
393
   authority VARCHAR2(255),  -- the authority issuing the identifier
394
   namespace VARCHAR2(255),  -- the namespace qualifying the identifier
395
   object    VARCHAR2(255),  -- the local part of the identifier for a particular object
396
   revision  VARCHAR2(255)   -- the revision part of the identifier
393 397
);
394
CREATE TRIGGER accession_number_before_insert
395
BEFORE INSERT ON accession_number FOR EACH ROW
398
CREATE TRIGGER identifier_before_insert
399
BEFORE INSERT ON identifier FOR EACH ROW
396 400
BEGIN
397
  SELECT accession_number_id_seq.nextval
398
    INTO :new.uniqueid
401
  SELECT identifier_id_seq.nextval
402
    INTO :new.id
399 403
    FROM dual;
400 404
END;
401 405
/
src/xmltables-postgres.sql
258 258
);
259 259

  
260 260
/*
261
 * Table used as Unique ID generator for the uniqueid part of Accession#
261
 * Table used to store all document identifiers in metacat.  Each identifier
262
 * consists of 4 subparts, an authority, namespace, object, and revision as
263
 * defined in the LSID specification.
262 264
 */
263
CREATE SEQUENCE accession_number_id_seq;
264
CREATE TABLE accession_number (
265
   uniqueid INT8 default nextval('accession_number_id_seq') PRIMARY KEY,
266
   site_code VARCHAR(100),
267
   date_created DATE
265
CREATE SEQUENCE identifier_id_seq;
266
CREATE TABLE identifier (
267
   id INT8 default nextval('identifier_id_seq') PRIMARY KEY, -- primary id
268
   authority VARCHAR(255),  -- the authority issuing the identifier
269
   namespace VARCHAR(255),  -- the namespace qualifying the identifier
270
   object    VARCHAR(255),  -- the local part of the identifier for a particular object
271
   revision  VARCHAR(255)   -- the revision part of the identifier
268 272
);
269 273

  
270 274
/*
src/upgrade-db-to-1.6-postgres.sql
24 24
 
25 25

  
26 26
/*
27
 * Changes to the tables for handling identifiers.  Old table no longer needed,
28
 * new identifier table to be used to support LSIDs.
29
 */
30
DROP TABLE accession_number;
31
DROP SEQUENCE accession_number_id_seq;
32

  
33
/*
34
 * Table used to store all document identifiers in metacat.  Each identifier
35
 * consists of 4 subparts, an authority, namespace, object, and revision as
36
 * defined in the LSID specification.
37
 */
38
CREATE SEQUENCE identifier_id_seq;
39
CREATE TABLE identifier (
40
   id INT8 default nextval('identifier_id_seq') PRIMARY KEY, -- primary id
41
   authority VARCHAR(255),  -- the authority issuing the identifier
42
   namespace VARCHAR(255),  -- the namespace qualifying the identifier
43
   object    VARCHAR(255),  -- the local part of the identifier for a particular object
44
   revision  VARCHAR(255)   -- the revision part of the identifier
45
);
46

  
47
/*
27 48
 * Table for indexing the paths specified the administrator in metacat.properties
28 49
 */
29 50
                                                                                                                                                             

Also available in: Unified diff