Project

General

Profile

« Previous | Next » 

Revision 163

Added by bojilova over 24 years ago

docid is varchar2(55)
new table xml_acc_numbers
clear out db trigger xml_documents_before_insert

View differences:

src/xmltables.sql
30 30 
DROP TABLE xml_catalog;
31 31 
DROP TABLE xml_documents;
32 32 
DROP TABLE xml_nodes;
33 
DROP TABLE xml_acc_numbers;
33 34 

  		




  34
  35
  
    
/* 


  		




  35
  36
  
    
 * Nodes -- table to store XML Nodes (both elements and attributes)


  		




  ......




  44
  45
  
    
					-- for TEXT it is the content)


  		




  45
  46
  
    
	parentnodeid	NUMBER(20),	-- index of the parent of this node


  		




  46
  47
  
    
	rootnodeid	NUMBER(20),	-- index of the root node of this tree


  		




  47
  
  
    
	docid		NUMBER(20),	-- index to the document id


  		




  
  48
  
    
	docid		VARCHAR2(55),	-- index to the document id


  		




  48
  49
  
    
	date_created	DATE,


  		




  49
  50
  
    
	date_updated	DATE,


  		




  50
  51
  
    
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),


  		




  ......




  69
  70
  
    
 * Documents -- table to store XML documents


  		




  70
  71
  
    
 */


  		




  71
  72
  
    
CREATE TABLE xml_documents (


  		




  72
  
  
    
	docid		NUMBER(20),	-- the document id #


  		




  
  73
  
    
	docid		VARCHAR2(55),	-- the document id #


  		




  73
  74
  
    
	rootnodeid	NUMBER(20),	-- refernce to the root node of the DOM


  		




  74
  75
  
    
	docname		VARCHAR2(100),	-- usually the root element name


  		




  75
  76
  
    
	doctype		VARCHAR2(100),	-- public id indicating document type


  		




  ......




  81
  82
  
    
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes


  		




  82
  83
  
    
);


  		




  83
  84
  
    

  		




  84
  
  
    
CREATE SEQUENCE xml_documents_id_seq;


  		




  85
  
  
    

  		




  86
  
  
    
CREATE TRIGGER xml_documents_before_insert


  		




  87
  
  
    
BEFORE INSERT ON xml_documents FOR EACH ROW


  		




  88
  
  
    
BEGIN


  		




  89
  
  
    
  SELECT xml_documents_id_seq.nextval


  		




  90
  
  
    
    INTO :new.docid


  		




  91
  
  
    
    FROM dual;


  		




  92
  
  
    
END;


  		




  93
  
  
    
/


  		




  94
  
  
    

  		




  95
  85
  
    
/* 


  		




  96
  86
  
    
 * XML Catalog -- table to store all external sources for XML documents


  		




  97
  87
  
    
 */


  		




  ......




  128
  118
  
    
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),


  		




  129
  119
  
    
   CONSTRAINT xml_nodes_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes


  		




  130
  120
  
    
);


  		




  
  121
  
    

  		




  
  122
  
    
/* 


  		




  
  123
  
    
 * table to store unique Accession # for every document in 2 parts


  		




  
  124
  
    
 */


  		




  
  125
  
    
CREATE TABLE xml_acc_numbers (


  		




  
  126
  
    
	global_name	VARCHAR2(32),	-- first part of acc #


  		




  
  127
  
    
	local_id	NUMBER(20),	-- second part - unique inside global name


  		




  
  128
  
    
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)


  		




  
  129
  
    
);


  		












Also available in:  Unified diff