Revision 6136
Added by ben leinfelder over 13 years ago
| src/xmltables-oracle.sql | ||
|---|---|---|
| 80 | 80 |
last_checked DATE, |
| 81 | 81 |
replicate NUMBER(1), |
| 82 | 82 |
datareplicate NUMBER(1), |
| 83 |
systemmetadatareplicate NUMBER(1), |
|
| 83 | 84 |
hub NUMBER(1), |
| 84 | 85 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
| 85 | 86 |
); |
| ... | ... | |
| 94 | 95 |
END; |
| 95 | 96 |
/ |
| 96 | 97 |
|
| 97 |
INSERT INTO xml_replication (server, replicate, datareplicate, hub) |
|
| 98 |
VALUES ('localhost', '0', '0', '0');
|
|
| 98 |
INSERT INTO xml_replication (server, replicate, datareplicate, systemmetadatareplicate, hub)
|
|
| 99 |
VALUES ('localhost', '0', '0', '0', '0');
|
|
| 99 | 100 |
|
| 100 | 101 |
/* |
| 101 | 102 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
| src/upgrade-db-to-1.10.0-postgres.sql | ||
|---|---|---|
| 1 | 1 |
CREATE TABLE systemMetadata ( |
| 2 |
guid text, -- the globally unique string identifier
|
|
| 3 |
docid VARCHAR(250), -- the local document id #
|
|
| 4 |
rev INT8, -- the revision part of the local identifier
|
|
| 5 |
date_uploaded TIMESTAMP, -- the date/time the document was first submitted
|
|
| 6 |
rights_holder VARCHAR(250), --the user who has rights to the document, usually the first persons to upload it
|
|
| 7 |
checksum VARCHAR(512), --the checksum of the doc using the given algorithm (see below)
|
|
| 8 |
checksum_algorithm VARCHAR(250), --the algorithm used to calculate the checksum
|
|
| 9 |
origin_member_node VARCHAR(250), --the member node where the document was first uploaded
|
|
| 10 |
authoritive_member_node VARCHAR(250), --the member node that currently controls the document
|
|
| 11 |
date_modified TIMESTAMP, -- the last date/time that the file was changed
|
|
| 12 |
submitter VARCHAR(256), -- the user who originally submitted the doc
|
|
| 13 |
object_format VARCHAR(256), --the format of the object
|
|
| 14 |
size VARCHAR(256), --the size of the object
|
|
| 15 |
CONSTRAINT systemMetadata_pk PRIMARY KEY (guid)
|
|
| 2 |
guid text, -- the globally unique string identifier of the object that the system metadata describes
|
|
| 3 |
date_uploaded TIMESTAMP, -- the date/time the document was first submitted
|
|
| 4 |
rights_holder VARCHAR(250), --the user who has rights to the document, usually the first persons to upload it
|
|
| 5 |
checksum VARCHAR(512), --the checksum of the doc using the given algorithm (see below)
|
|
| 6 |
checksum_algorithm VARCHAR(250), --the algorithm used to calculate the checksum
|
|
| 7 |
origin_member_node VARCHAR(250), --the member node where the document was first uploaded
|
|
| 8 |
authoritive_member_node VARCHAR(250), --the member node that currently controls the document
|
|
| 9 |
date_modified TIMESTAMP, -- the last date/time that the file was changed
|
|
| 10 |
submitter VARCHAR(256), -- the user who originally submitted the doc
|
|
| 11 |
object_format VARCHAR(256), --the format of the object
|
|
| 12 |
size VARCHAR(256), --the size of the object
|
|
| 13 |
replication_allowed boolean, -- replication allowed
|
|
| 14 |
number_replicas INT8, -- the number of replicas allowed
|
|
| 15 |
CONSTRAINT systemMetadata_pk PRIMARY KEY (guid)
|
|
| 16 | 16 |
); |
| 17 |
/* |
|
| 18 |
* Table used to store system metadata provenance information |
|
| 19 |
*/ |
|
| 20 |
CREATE TABLE systemMetadataProvenance ( |
|
| 21 |
guid text, -- the globally unique string identifier of the object that the system metadata describes |
|
| 22 |
relationship VARCHAR(250), -- the provenance relationship defined between objects |
|
| 23 |
target_guid text, -- the globally unique string identifier of the other object |
|
| 24 |
CONSTRAINT systemMetadataProvenance_fk |
|
| 25 |
FOREIGN KEY (guid) REFERENCES systemMetadata |
|
| 26 |
); |
|
| 17 | 27 |
|
| 28 |
CREATE TABLE systemMetadataReplicationPolicy ( |
|
| 29 |
guid text, -- the globally unique string identifier of the object that the system metadata describes |
|
| 30 |
member_node VARCHAR(250), -- replication member node |
|
| 31 |
policy text, -- the policy (preferred, blocked, etc...TBD) |
|
| 32 |
CONSTRAINT systemMetadataReplicationPolicy_fk |
|
| 33 |
FOREIGN KEY (guid) REFERENCES systemMetadata |
|
| 34 |
); |
|
| 35 |
|
|
| 36 |
CREATE TABLE systemMetadataReplicationStatus ( |
|
| 37 |
guid text, -- the globally unique string identifier of the object that the system metadata describes |
|
| 38 |
member_node VARCHAR(250), -- replication member node |
|
| 39 |
status VARCHAR(250), -- replication status |
|
| 40 |
date_verified TIMESTAMP, -- the date replication was verified |
|
| 41 |
CONSTRAINT systemMetadataReplicationStatus_fk |
|
| 42 |
FOREIGN KEY (guid) REFERENCES systemMetadata |
|
| 43 |
); |
|
| 44 |
|
|
| 18 | 45 |
CREATE TABLE identifier ( |
| 19 | 46 |
guid text, -- the globally unique string identifier |
| 20 | 47 |
docid VARCHAR(250), -- the local document id # |
| ... | ... | |
| 31 | 58 |
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate); |
| 32 | 59 |
|
| 33 | 60 |
/* |
| 61 |
* Replication changes to support DataONE System Metadata replication |
|
| 62 |
*/ |
|
| 63 |
ALTER TABLE xml_replication ADD COLUMN systemmetadatareplicate INT8; |
|
| 64 |
/* |
|
| 65 |
* Allow guid in xml_access table (for system metadata) |
|
| 66 |
*/ |
|
| 67 |
ALTER TABLE xml_access ADD COLUMN guid text; |
|
| 68 |
|
|
| 69 |
/* |
|
| 34 | 70 |
* Register the DataONE schemas |
| 35 | 71 |
*/ |
| 36 | 72 |
|
| src/xmltables-postgres.sql | ||
|---|---|---|
| 39 | 39 |
last_checked DATE, |
| 40 | 40 |
replicate INT8, |
| 41 | 41 |
datareplicate INT8, |
| 42 |
systemmetadatareplicate INT8, |
|
| 42 | 43 |
hub INT8, |
| 43 | 44 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
| 44 | 45 |
); |
| 45 | 46 |
|
| 46 |
INSERT INTO xml_replication (server, replicate, datareplicate, hub) VALUES ('localhost', '0', '0', '0');
|
|
| 47 |
INSERT INTO xml_replication (server, replicate, datareplicate, systemmetadatareplicate, hub) VALUES ('localhost', '0', '0', '0' '0');
|
|
| 48 |
/* |
|
| 49 |
* ALTER TABLE xml_replication ADD COLUMN systemmetadatareplicate INT8; |
|
| 50 |
*/ |
|
| 47 | 51 |
|
| 48 | 52 |
/* |
| 49 | 53 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
| src/upgrade-db-to-1.10.0-oracle.sql | ||
|---|---|---|
| 1 | 1 |
CREATE TABLE systemMetadata ( |
| 2 | 2 |
guid VARCHAR2(2000), -- the globally unique string identifier |
| 3 |
docid VARCHAR(250), -- the local document id # |
|
| 4 |
rev NUMBER(8), -- the revision part of the local identifier |
|
| 5 | 3 |
date_uploaded DATE, -- the date/time the document was first submitted |
| 6 | 4 |
rights_holder VARCHAR2(250), --the user who has rights to the document, usually the first persons to upload it |
| 7 | 5 |
checksum VARCHAR2(512), --the checksum of the doc using the given algorithm (see below) |
| ... | ... | |
| 11 | 9 |
date_modified DATE, -- the last date/time that the file was changed |
| 12 | 10 |
submitter VARCHAR2(256), -- the user who originally submitted the doc |
| 13 | 11 |
object_format VARCHAR2(256), --the format of the object |
| 14 |
size VARCHAR2(256) --the size of the object |
|
| 12 |
size VARCHAR2(256), --the size of the object |
|
| 13 |
replication_allowed boolean, -- replication allowed |
|
| 14 |
number_replicas NUMBER(8), -- the number of replicas allowed |
|
| 15 |
CONSTRAINT systemMetadata_pk |
|
| 16 |
PRIMARY KEY (guid) |
|
| 15 | 17 |
) |
| 16 | 18 |
|
| 19 |
/* |
|
| 20 |
* Table used to store system metadata provenance information |
|
| 21 |
*/ |
|
| 22 |
CREATE TABLE systemMetadataProvenance ( |
|
| 23 |
guid VARCHAR2(2000), -- the globally unique string identifier of the object that the system metadata describes |
|
| 24 |
relationship VARCHAR(250), -- the provenance relationship defined between objects |
|
| 25 |
target_guid VARCHAR2(2000), -- the globally unique string identifier of the other object |
|
| 26 |
CONSTRAINT systemMetadataProvenance_fk |
|
| 27 |
FOREIGN KEY (guid) REFERENCES systemMetadata |
|
| 28 |
); |
|
| 29 |
|
|
| 30 |
CREATE TABLE systemMetadataReplicationPolicy ( |
|
| 31 |
guid VARCHAR2(2000), -- the globally unique string identifier of the object that the system metadata describes |
|
| 32 |
member_node VARCHAR(250), -- replication member node |
|
| 33 |
policy VARCHAR2(2000), -- the policy (preferred, blocked, etc...TBD) |
|
| 34 |
CONSTRAINT systemMetadataReplicationPolicy_fk |
|
| 35 |
FOREIGN KEY (guid) REFERENCES systemMetadata |
|
| 36 |
); |
|
| 37 |
|
|
| 38 |
CREATE TABLE systemMetadataReplicationStatus ( |
|
| 39 |
guid VARCHAR2(2000), -- the globally unique string identifier of the object that the system metadata describes |
|
| 40 |
member_node VARCHAR(250), -- replication member node |
|
| 41 |
status VARCHAR(250), -- replication status |
|
| 42 |
date_verified DATE, -- the date replication was verified |
|
| 43 |
CONSTRAINT systemMetadataReplicationStatus_fk |
|
| 44 |
FOREIGN KEY (guid) REFERENCES systemMetadata |
|
| 45 |
); |
|
| 46 |
|
|
| 17 | 47 |
CREATE TABLE identifier ( |
| 18 | 48 |
guid VARCHAR2(2000), -- the globally unique string identifier |
| 19 | 49 |
docid VARCHAR2(250), -- the local document id # |
| ... | ... | |
| 21 | 51 |
); |
| 22 | 52 |
|
| 23 | 53 |
/* |
| 54 |
* Replication changes to support DataONE System Metadata replication |
|
| 55 |
*/ |
|
| 56 |
ALTER TABLE xml_replication ADD COLUMN systemmetadatareplicate NUMBER(1); |
|
| 57 |
/* |
|
| 58 |
* Allow guid in xml_access table (for system metadata) |
|
| 59 |
*/ |
|
| 60 |
ALTER TABLE xml_access ADD COLUMN guid VARCHAR2(2000); |
|
| 61 |
|
|
| 62 |
/* |
|
| 24 | 63 |
* add the nodedatadate column to xml_nodes |
| 25 | 64 |
* TODO: load the data into it (java?) |
| 26 | 65 |
*/ |
Also available in: Unified diff
add option for replicating system metadata (dataone)
https://redmine.dataone.org/issues/1626
also make sure the latest table changes are included in upgrade scripts