[metacat-cvs] metacat/src upgrade-db-to-1.6-postgres.sql xmltables-postgres.sql drop-postgres.sql xmltables.sql upgrade-db-to-1.6.sql
Matthew Jones
jones at ecoinformatics.org
Fri Nov 18 17:10:41 PST 2005
jones 05/11/18 17:10:41
Modified: src upgrade-db-to-1.6-postgres.sql
xmltables-postgres.sql drop-postgres.sql
xmltables.sql upgrade-db-to-1.6.sql
Log:
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.
Revision Changes Path
1.4 +23 -2 metacat/src/upgrade-db-to-1.6-postgres.sql
Index: upgrade-db-to-1.6-postgres.sql
===================================================================
RCS file: /cvs/metacat/src/upgrade-db-to-1.6-postgres.sql,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- upgrade-db-to-1.6-postgres.sql 1 Aug 2005 17:28:32 -0000 1.3
+++ upgrade-db-to-1.6-postgres.sql 19 Nov 2005 01:10:41 -0000 1.4
@@ -3,9 +3,9 @@
* Copyright: 2004 Regents of the University of California and the
* National Center for Ecological Analysis and Synthesis
*
- * '$Author: sgarg $'
- * '$Date: 2005/08/01 17:28:32 $'
- * '$Revision: 1.3 $'
+ * '$Author: jones $'
+ * '$Date: 2005/11/19 01:10:41 $'
+ * '$Revision: 1.4 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -22,6 +22,27 @@
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
+
+/*
+ * Changes to the tables for handling identifiers. Old table no longer needed,
+ * new identifier table to be used to support LSIDs.
+ */
+DROP TABLE accession_number;
+DROP SEQUENCE accession_number_id_seq;
+
+/*
+ * Table used to store all document identifiers in metacat. Each identifier
+ * consists of 4 subparts, an authority, namespace, object, and revision as
+ * defined in the LSID specification.
+ */
+CREATE SEQUENCE identifier_id_seq;
+CREATE TABLE identifier (
+ id INT8 default nextval('identifier_id_seq') PRIMARY KEY, -- primary id
+ authority VARCHAR(255), -- the authority issuing the identifier
+ namespace VARCHAR(255), -- the namespace qualifying the identifier
+ object VARCHAR(255), -- the local part of the identifier for a particular object
+ revision VARCHAR(255) -- the revision part of the identifier
+);
/*
* Table for indexing the paths specified the administrator in metacat.properties
1.10 +15 -11 metacat/src/xmltables-postgres.sql
Index: xmltables-postgres.sql
===================================================================
RCS file: /cvs/metacat/src/xmltables-postgres.sql,v
retrieving revision 1.9
retrieving revision 1.10
diff -u -r1.9 -r1.10
--- xmltables-postgres.sql 27 Sep 2005 23:48:07 -0000 1.9
+++ xmltables-postgres.sql 19 Nov 2005 01:10:41 -0000 1.10
@@ -4,8 +4,8 @@
* National Center for Ecological Analysis and Synthesis
*
* '$Author: jones $'
- * '$Date: 2005/09/27 23:48:07 $'
- * '$Revision: 1.9 $'
+ * '$Date: 2005/11/19 01:10:41 $'
+ * '$Revision: 1.10 $'
*
* This program is free software, you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -258,13 +258,17 @@
);
/*
- * Table used as Unique ID generator for the uniqueid part of Accession#
- */
-CREATE SEQUENCE accession_number_id_seq;
-CREATE TABLE accession_number (
- uniqueid INT8 default nextval('accession_number_id_seq') PRIMARY KEY,
- site_code VARCHAR(100),
- date_created DATE
+ * Table used to store all document identifiers in metacat. Each identifier
+ * consists of 4 subparts, an authority, namespace, object, and revision as
+ * defined in the LSID specification.
+ */
+CREATE SEQUENCE identifier_id_seq;
+CREATE TABLE identifier (
+ id INT8 default nextval('identifier_id_seq') PRIMARY KEY, -- primary id
+ authority VARCHAR(255), -- the authority issuing the identifier
+ namespace VARCHAR(255), -- the namespace qualifying the identifier
+ object VARCHAR(255), -- the local part of the identifier for a particular object
+ revision VARCHAR(255) -- the revision part of the identifier
);
/*
1.5 +4 -4 metacat/src/drop-postgres.sql
Index: drop-postgres.sql
===================================================================
RCS file: /cvs/metacat/src/drop-postgres.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- drop-postgres.sql 27 Sep 2005 23:48:07 -0000 1.4
+++ drop-postgres.sql 19 Nov 2005 01:10:41 -0000 1.5
@@ -4,8 +4,8 @@
* National Center for Ecological Analysis and Synthesis
*
* '$Author: jones $'
- * '$Date: 2005/09/27 23:48:07 $'
- * '$Revision: 1.4 $'
+ * '$Date: 2005/11/19 01:10:41 $'
+ * '$Revision: 1.5 $'
*
* This program is free software, you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -39,7 +39,7 @@
DROP SEQUENCE xml_relation_id_seq;
DROP SEQUENCE xml_replication_id_seq;
DROP SEQUENCE xml_documents_id_seq;
-DROP SEQUENCE accession_number_id_seq;
+DROP SEQUENCE identifier_id_seq;
DROP SEQUENCE access_log_id_seq;
DROP SEQUENCE xml_queryresult_id_seq;
DROP SEQUENCE xml_returnfield_id_seq;
@@ -55,7 +55,7 @@
DROP TABLE xml_nodes_revisions;
DROP TABLE xml_replication;
DROP TABLE xml_catalog;
-DROP TABLE accession_number;
+DROP TABLE identifier;
DROP TABLE access_log;
DROP TABLE harvest_site_schedule;
DROP TABLE harvest_detail_log;
1.71 +20 -16 metacat/src/xmltables.sql
Index: xmltables.sql
===================================================================
RCS file: /cvs/metacat/src/xmltables.sql,v
retrieving revision 1.70
retrieving revision 1.71
diff -u -r1.70 -r1.71
--- xmltables.sql 2 Oct 2005 23:14:01 -0000 1.70
+++ xmltables.sql 19 Nov 2005 01:10:41 -0000 1.71
@@ -3,9 +3,9 @@
* Copyright: 2004 Regents of the University of California and the
* National Center for Ecological Analysis and Synthesis
*
- * '$Author: tao $'
- * '$Date: 2005/10/02 23:14:01 $'
- * '$Revision: 1.70 $'
+ * '$Author: jones $'
+ * '$Date: 2005/11/19 01:10:41 $'
+ * '$Revision: 1.71 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -32,7 +32,7 @@
DROP SEQUENCE xml_catalog_id_seq;
DROP SEQUENCE xml_relation_id_seq;
DROP SEQUENCE xml_replication_id_seq;
-DROP SEQUENCE accession_number_id_seq;
+DROP SEQUENCE identifier_id_seq;
DROP SEQUENCE access_log_id_seq;
DROP SEQUENCE xml_returnfield_id_seq;
DROP SEQUENCE xml_queryresult_id_seq;
@@ -44,7 +44,7 @@
DROP TRIGGER xml_catalog_before_insert;
DROP TRIGGER xml_relation_before_insert;
DROP TRIGGER xml_replication_before_insert;
-DROP TRIGGER accession_number_before_insert;
+DROP TRIGGER identifier_before_insert;
DROP TRIGGER access_log_before_insert;
DROP TRIGGER xml_returnfield_before_insert;
DROP TRIGGER xml_queryresult_before_insert;
@@ -60,7 +60,7 @@
DROP TABLE xml_nodes_revisions;
DROP TABLE xml_replication;
DROP TABLE xml_catalog;
-DROP TABLE accession_number;
+DROP TABLE identifier;
DROP TABLE access_log;
DROP TABLE harvest_site_schedule;
DROP TABLE harvest_detail_log;
@@ -383,19 +383,23 @@
/
/*
- * Table used as Unique ID generator for the uniqueid part of Accession#
- */
-CREATE SEQUENCE accession_number_id_seq;
-CREATE TABLE accession_number (
- uniqueid NUMBER(20) PRIMARY KEY,
- site_code VARCHAR2(100),
- date_created DATE
+ * Table used to store all document identifiers in metacat. Each identifier
+ * consists of 4 subparts, an authority, namespace, object, and revision as
+ * defined in the LSID specification.
+ */
+CREATE SEQUENCE identifier_id_seq;
+CREATE TABLE identifier (
+ id NUMBER(20) PRIMARY KEY, -- primary key
+ authority VARCHAR2(255), -- the authority issuing the identifier
+ namespace VARCHAR2(255), -- the namespace qualifying the identifier
+ object VARCHAR2(255), -- the local part of the identifier for a particular object
+ revision VARCHAR2(255) -- the revision part of the identifier
);
-CREATE TRIGGER accession_number_before_insert
-BEFORE INSERT ON accession_number FOR EACH ROW
+CREATE TRIGGER identifier_before_insert
+BEFORE INSERT ON identifier FOR EACH ROW
BEGIN
- SELECT accession_number_id_seq.nextval
- INTO :new.uniqueid
+ SELECT identifier_id_seq.nextval
+ INTO :new.id
FROM dual;
END;
/
1.5 +31 -3 metacat/src/upgrade-db-to-1.6.sql
Index: upgrade-db-to-1.6.sql
===================================================================
RCS file: /cvs/metacat/src/upgrade-db-to-1.6.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- upgrade-db-to-1.6.sql 1 Nov 2005 19:31:31 -0000 1.4
+++ upgrade-db-to-1.6.sql 19 Nov 2005 01:10:41 -0000 1.5
@@ -3,9 +3,9 @@
* Copyright: 2004 Regents of the University of California and the
* National Center for Ecological Analysis and Synthesis
*
- * '$Author: sgarg $'
- * '$Date: 2005/11/01 19:31:31 $'
- * '$Revision: 1.4 $'
+ * '$Author: jones $'
+ * '$Date: 2005/11/19 01:10:41 $'
+ * '$Revision: 1.5 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -22,6 +22,34 @@
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
+/*
+ * Changes to the tables for handling identifiers. Old table no longer needed,
+ * new identifier table to be used to support LSIDs.
+ */
+DROP TABLE accession_number;
+DROP SEQUENCE accession_number_id_seq;
+
+/*
+ * Table used to store all document identifiers in metacat. Each identifier
+ * consists of 4 subparts, an authority, namespace, object, and revision as
+ * defined in the LSID specification.
+ */
+CREATE SEQUENCE identifier_id_seq;
+CREATE TABLE identifier (
+ id NUMBER(20) PRIMARY KEY, -- primary key
+ authority VARCHAR2(255), -- the authority issuing the identifier
+ namespace VARCHAR2(255), -- the namespace qualifying the identifier
+ object VARCHAR2(255), -- the local part of the identifier for a particular object
+ revision VARCHAR2(255) -- the revision part of the identifier
+);
+CREATE TRIGGER identifier_before_insert
+BEFORE INSERT ON identifier FOR EACH ROW
+BEGIN
+ SELECT identifier_id_seq.nextval
+ INTO :new.id
+ FROM dual;
+END;
+/
/*
* Index of Paths - table to store nodes with paths specified by userst in metacat.properties
More information about the Metacat-cvs
mailing list