Revision 2769
Added by Matt Jones about 19 years ago
| 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
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.