Project

General

Profile

« Previous | Next » 

Revision 759

Added by bojilova about 22 years ago

several changes were needed related to the change of the unique id generation and use

View differences:

xmltables_postgres.sql
30 30 
DROP SEQUENCE xml_replication_id_seq;
31 31 
DROP SEQUENCE accnum_uniqueid_seq;
32 32 
DROP SEQUENCE xml_documents_id_seq;
33 
DROP SEQUENCE accession_number_id_seq;
33 34 

  		




  34
  35
  
    
DROP TABLE xml_index;


  		




  35
  36
  
    
DROP TABLE xml_access;


  		




  ......




  39
  40
  
    
DROP TABLE xml_nodes;


  		




  40
  41
  
    
DROP TABLE xml_replication;


  		




  41
  42
  
    
DROP TABLE xml_catalog;


  		




  
  43
  
    
DROP TABLE accession_number;


  		




  42
  44
  
    

  		




  43
  45
  
    
/*


  		




  44
  46
  
    
 *Replication -- table to store servers that metacat is replicated to


  		




  45
  47
  
    
 */


  		




  46
  48
  
    
CREATE SEQUENCE xml_replication_id_seq;


  		




  47
  49
  
    
CREATE TABLE xml_replication (


  		




  48
  
  
    
  serverid     INT8  default nextval('xml_replication_id_seq'), 


  		




  49
  
  
    
  server        VARCHAR(512),


  		




  
  50
  
    
  serverid	INT8  default nextval('xml_replication_id_seq'), 


  		




  
  51
  
    
  server	VARCHAR(512),


  		




  50
  52
  
    
  last_checked  DATE,


  		




  51
  53
  
    
  replicate     INT8, 


  		




  52
  54
  
    
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)


  		




  ......




  57
  59
  
    
/* 


  		




  58
  60
  
    
 * Nodes -- table to store XML Nodes (both elements and attributes)


  		




  59
  61
  
    
 */


  		




  
  62
  
    
CREATE SEQUENCE xml_nodes_id_seq;


  		




  60
  63
  
    
CREATE TABLE xml_nodes (


  		




  61
  
  
    
	nodeid		INT8, 		-- the unique node id (pk)


  		




  
  64
  
    
	nodeid		INT8 default nextval('xml_nodes_id_seq'),


  		




  
  65
  
    
					-- the unique node id (pk)


  		




  62
  66
  
    
	nodeindex	INT8,		-- order of nodes within parent


  		




  63
  67
  
    
	nodetype	VARCHAR(20),	-- type (DOCUMENT, COMMENT, PI,


  		




  64
  68
  
    
					-- ELEMENT, ATTRIBUTE, TEXT)


  		




  ......




  76
  80
  
    
   CONSTRAINT xml_nodes_parent_fk 


  		




  77
  81
  
    
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes


  		




  78
  82
  
    
);


  		




  79
  
  
    

  		




  80
  83
  
    
/* 


  		




  81
  
  
    
 * Indexes of rootnodeid & parentnodeid in xml_nodes


  		




  
  84
  
    
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes


  		




  82
  85
  
    
 */


  		




  83
  86
  
    
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);


  		




  84
  87
  
    
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);


  		




  85
  88
  
    
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);


  		




  86
  
  
    
CREATE SEQUENCE xml_nodes_id_seq;


  		




  87
  89
  
    

  		




  88
  
  
    

  		




  89
  90
  
    
/* 


  		




  90
  91
  
    
 * XML Catalog -- table to store all external sources for XML documents


  		




  91
  92
  
    
 */


  		




  92
  93
  
    
CREATE SEQUENCE xml_catalog_id_seq;


  		




  93
  94
  
    
CREATE TABLE xml_catalog (


  		




  94
  
  
    
	catalog_id INT8 default nextval('xml_catalog_id_seq'), -- the id for this catalog entry


  		




  
  95
  
    
	catalog_id	INT8 default nextval('xml_catalog_id_seq'),


  		




  
  96
  
    
                                        -- the id for this catalog entry


  		




  95
  97
  
    
	entry_type	VARCHAR(500),	-- the type of this catalog entry


  		




  96
  98
  
    
					-- (e.g., DTD, XSD, XSL)


  		




  97
  99
  
    
	source_doctype	VARCHAR(500),	-- the source public_id for transforms


  		




  ......




  99
  101
  
    
	public_id	VARCHAR(500),	-- the unique id for this type


  		




  100
  102
  
    
	system_id	VARCHAR(1000),	-- the local location of the object


  		




  101
  103
  
    
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),


  		




  102
  
  
    
   CONSTRAINT xml_catalog_uk UNIQUE  (entry_type, source_doctype, target_doctype, public_id)


  		




  
  104
  
    
   CONSTRAINT xml_catalog_uk UNIQUE  


  		




  
  105
  
    
              (entry_type, source_doctype, target_doctype, public_id)


  		




  103
  106
  
    
);


  		




  104
  107
  
    

  		




  105
  
  
    

  		




  106
  108
  
    
/* 


  		




  107
  109
  
    
 * Sequence to get uniqueID for Accession #


  		




  108
  110
  
    
 */


  		




  ......




  112
  114
  
    
 */


  		




  113
  115
  
    
CREATE TABLE xml_documents (


  		




  114
  116
  
    
	docid		VARCHAR(250),	-- the document id #


  		




  115
  
  
    
	rootnodeid	INT8,	-- reference to root node of the DOM


  		




  
  117
  
    
	rootnodeid	INT8,		-- reference to root node of the DOM


  		




  116
  118
  
    
	docname		VARCHAR(100),	-- usually the root element name


  		




  117
  119
  
    
	doctype		VARCHAR(100),	-- public id indicating document type


  		




  118
  120
  
    
	user_owner	VARCHAR(100),	-- the user owned the document


  		




  119
  121
  
    
	user_updated	VARCHAR(100),	-- the user updated the document


  		




  120
  122
  
    
	server_location INT8,	-- the server on which this document resides


  		




  121
  
  
    
	rev 		INT8,   --the revision number of the document


  		




  
  123
  
    
	rev 		INT8,   -- the revision number of the document


  		




  122
  124
  
    
	date_created	DATE,


  		




  123
  125
  
    
	date_updated	DATE,


  		




  124
  126
  
    
	public_access	INT8,	-- flag for public access


  		




  ......




  143
  145
  
    
 */


  		




  144
  146
  
    
CREATE SEQUENCE xml_revisions_id_seq;


  		




  145
  147
  
    
CREATE TABLE xml_revisions (


  		




  146
  
  
    
	revisionid	INT8  default nextval('xml_revisions_id_seq'), -- the revision number we are saving


  		




  
  148
  
    
	revisionid	INT8  default nextval('xml_revisions_id_seq'),


  		




  
  149
  
    
                                        -- the revision number we are saving


  		




  147
  150
  
    
	docid		VARCHAR(250),	-- the document id #


  		




  148
  
  
    
	rootnodeid	INT8,	-- reference to root node of the DOM


  		




  
  151
  
    
	rootnodeid	INT8,		-- reference to root node of the DOM


  		




  149
  152
  
    
	docname		VARCHAR(100),	-- usually the root element name


  		




  150
  153
  
    
	doctype		VARCHAR(100),	-- public id indicating document type


  		




  151
  154
  
    
	user_owner	VARCHAR(100),


  		




  ......




  209
  212
  
    

  		




  210
  213
  
    
CREATE SEQUENCE xml_relation_id_seq;


  		




  211
  214
  
    
CREATE TABLE xml_relation (


  		




  212
  
  
    
	relationid    INT8 default nextval('xml_relation_id_seq')  PRIMARY KEY, -- unique id


  		




  
  215
  
    
	relationid    INT8 default nextval('xml_relation_id_seq') PRIMARY KEY,


  		




  
  216
  
    
					     -- unique id


  		




  213
  217
  
    
	docid         VARCHAR(250) ,         -- the docid of the package file


  		




  214
  
  
    
	                                      -- that this relation came from


  		




  
  218
  
    
	                                     -- that this relation came from


  		




  215
  219
  
    
        packagetype   VARCHAR(250),          -- the type of the package


  		




  216
  220
  
    
	subject       VARCHAR(512) NOT NULL, -- the subject of the relation


  		




  217
  221
  
    
	subdoctype    VARCHAR(128),         	-- the doctype of the subject


  		




  ......




  223
  227
  
    
		FOREIGN KEY (docid) REFERENCES xml_documents


  		




  224
  228
  
    
  );


  		




  225
  229
  
    

  		




  
  230
  
    
/* 


  		




  
  231
  
    
 * Table used as Unique ID generator for the uniqueid part of Accession#


  		




  
  232
  
    
 */


  		




  
  233
  
    
CREATE SEQUENCE accession_number_id_seq;


  		




  
  234
  
    
CREATE TABLE accession_number (


  		




  
  235
  
    
   uniqueid	INT8    default nextval('accession_number_id_seq') PRIMARY KEY,


  		




  
  236
  
    
   site_code	VARCHAR(100),


  		




  
  237
  
    
   date_created	DATE,


  		




  
  238
  
    
);


  		




  226
  239
  
    

  		




  
  240
  
    

  		




  
  241
  
    

  		












Also available in:  Unified diff