Project

General

Profile

« Previous | Next » 

Revision 20

Added by Matt Jones over 24 years ago

consolidated table structure to eliminate xml_attributes and xml_elements in favor of a single, unified xml_nodes table

View differences:

xmltables.sql
13 13 
/*
14 14 
 * Drop all of the objects in proper order
15 15 
 */
16 
DROP SEQUENCE xml_attributes_id_seq;
17 
DROP TRIGGER xml_attributes_before_insert;
18 
DROP SEQUENCE xml_elements_id_seq;
19 
DROP TRIGGER xml_elements_before_insert;
16 
DROP SEQUENCE xml_nodes_id_seq;
17 
DROP TRIGGER xml_nodes_before_insert;
20 18 
DROP TABLE xml_documents;
21 
DROP TABLE xml_attributes;
22 
DROP TABLE xml_elements;
19 
DROP TABLE xml_nodes;
23 20 

  		




  24
  21
  
    
/* 


  		




  25
  
  
    
 * Elements -- table to store XML Elements


  		




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


  		




  26
  23
  
    
 */


  		




  27
  
  
    
CREATE TABLE xml_elements (


  		




  
  24
  
    
CREATE TABLE xml_nodes (


  		




  28
  25
  
    
	nodeid		NUMBER(20),


  		




  29
  26
  
    
	parentnodeid	NUMBER(20),


  		




  
  27
  
    
	nodetype	VARCHAR2(2000),


  		




  30
  28
  
    
	nodename	VARCHAR2(2000),


  		




  31
  29
  
    
	nodedata	VARCHAR2(2000),


  		




  32
  30
  
    
	date_created	DATE,


  		




  33
  31
  
    
	date_updated	DATE,


  		




  34
  
  
    
   CONSTRAINT xml_elements_pk PRIMARY KEY (nodeid),


  		




  35
  
  
    
   CONSTRAINT xml_elements_parent_fk 


  		




  36
  
  
    
		FOREIGN KEY (parentnodeid) REFERENCES xml_elements


  		




  
  32
  
    
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),


  		




  
  33
  
    
   CONSTRAINT xml_nodes_parent_fk 


  		




  
  34
  
    
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes


  		




  37
  35
  
    
);


  		




  38
  36
  
    

  		




  39
  
  
    
CREATE SEQUENCE xml_elements_id_seq;


  		




  
  37
  
    
CREATE SEQUENCE xml_nodes_id_seq;


  		




  40
  38
  
    

  		




  41
  
  
    
CREATE TRIGGER xml_elements_before_insert


  		




  42
  
  
    
BEFORE INSERT ON xml_elements FOR EACH ROW


  		




  
  39
  
    
CREATE TRIGGER xml_nodes_before_insert


  		




  
  40
  
    
BEFORE INSERT ON xml_nodes FOR EACH ROW


  		




  43
  41
  
    
BEGIN


  		




  44
  
  
    
  SELECT xml_elements_id_seq.nextval


  		




  
  42
  
    
  SELECT xml_nodes_id_seq.nextval


  		




  45
  43
  
    
    INTO :new.nodeid


  		




  46
  44
  
    
    FROM dual;


  		




  47
  45
  
    
END;


  		




  ......




  59
  57
  
    
	date_updated	DATE,


  		




  60
  58
  
    
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),


  		




  61
  59
  
    
   CONSTRAINT xml_documents_root_fk 


  		




  62
  
  
    
		FOREIGN KEY (rootnodeid) REFERENCES xml_elements


  		




  
  60
  
    
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes


  		




  63
  61
  
    
);


  		




  64
  62
  
    

  		




  65
  
  
    
/* 


  		




  66
  
  
    
 * Attributes -- table to store XML Attributes


  		




  67
  
  
    
 */


  		




  68
  
  
    
CREATE TABLE xml_attributes (


  		




  69
  
  
    
	attributeid	NUMBER(20),


  		




  70
  
  
    
	nodeid		NUMBER(20),


  		




  71
  
  
    
	attributenumber	NUMBER(20),


  		




  72
  
  
    
	attributename	VARCHAR2(256),


  		




  73
  
  
    
	attributevalue	VARCHAR2(2000),


  		




  74
  
  
    
	date_created	DATE,


  		




  75
  
  
    
	date_updated	DATE,


  		




  76
  
  
    
   CONSTRAINT xml_attributes_pk PRIMARY KEY (attributeid),


  		




  77
  
  
    
   CONSTRAINT xml_attributes_parent_node FOREIGN KEY (nodeid) 


  		




  78
  
  
    
		REFERENCES xml_elements,


  		




  79
  
  
    
   CONSTRAINT xml_attributes_uniq_attribute UNIQUE (nodeid,attributename) 


  		




  80
  
  
    
);


  		




  81
  
  
    

  		




  82
  
  
    
CREATE SEQUENCE xml_attributes_id_seq;


  		




  83
  
  
    

  		




  84
  
  
    
CREATE TRIGGER xml_attributes_before_insert


  		




  85
  
  
    
BEFORE INSERT ON xml_attributes FOR EACH ROW


  		




  86
  
  
    
BEGIN


  		




  87
  
  
    
  SELECT xml_attributes_id_seq.nextval


  		




  88
  
  
    
    INTO :new.attributeid


  		




  89
  
  
    
    FROM dual;


  		




  90
  
  
    
END;


  		




  91
  
  
    
/


  		




  92
  
  
    

  		












Also available in:  Unified diff