Project

General

Profile

« Previous | Next » 

Revision 72

Added by bojilova about 24 years ago

Included new features about writing XML documents into db
This includes writing data into db XML Catalog, document data into xml_documents,
comments into xml_nodes, added nodeindex for the order of elements by given parent element.

View differences:

xmltables.sql
1 
/*
1 
source_doctype, public_id, /*
2 2 
 * xmltables.sql -- Create or replace tables for storing XML in the db
3 3 
 *
4 4 
 *      Purpose: creates tables needed for XML database
......
13 13 
/*
14 14 
 * Drop all of the objects in proper order
15 15 
 */
16 
set echo off
17 

  		




  16
  18
  
    
DROP SEQUENCE xml_nodes_id_seq;


  		




  
  19
  
    
DROP SEQUENCE xml_entity_id_seq;


  		




  
  20
  
    
DROP SEQUENCE xml_documents_id_seq;


  		




  
  21
  
    

  		




  17
  22
  
    
DROP TRIGGER xml_nodes_before_insert;


  		




  
  23
  
    
DROP TRIGGER xml_documents_before_insert;


  		




  
  24
  
    
DROP TRIGGER xml_cat_entities_before_insert;


  		




  
  25
  
    

  		




  
  26
  
    
DROP TABLE xml_catalog_entities;


  		




  
  27
  
    
DROP TABLE xml_catalog;


  		




  18
  28
  
    
DROP TABLE xml_documents;


  		




  19
  29
  
    
DROP TABLE xml_nodes;


  		




  20
  30
  
    

  		




  ......




  24
  34
  
    
CREATE TABLE xml_nodes (


  		




  25
  35
  
    
	nodeid		NUMBER(20),


  		




  26
  36
  
    
	parentnodeid	NUMBER(20),


  		




  27
  
  
    
	nodetype	VARCHAR2(2000),


  		




  28
  
  
    
	nodename	VARCHAR2(2000),


  		




  29
  
  
    
	nodedata	VARCHAR2(2000),


  		




  
  37
  
    
	nodeindex	NUMBER(10),


  		




  
  38
  
    
	nodetype	VARCHAR2(20),


  		




  
  39
  
    
	nodename	VARCHAR2(100),


  		




  
  40
  
    
	nodedata	LONG,


  		




  30
  41
  
    
	date_created	DATE,


  		




  31
  42
  
    
	date_updated	DATE,


  		




  32
  43
  
    
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),


  		




  ......




  46
  57
  
    
/


  		




  47
  58
  
    

  		




  48
  59
  
    
/* 


  		




  49
  
  
    
 * Documents -- table to store XML document catalog


  		




  
  60
  
    
 * Documents -- table to store XML documents


  		




  50
  61
  
    
 */


  		




  51
  62
  
    
CREATE TABLE xml_documents (


  		




  52
  63
  
    
	docid		NUMBER(20),


  		




  53
  64
  
    
	rootnodeid	NUMBER(20),


  		




  54
  
  
    
	docname		VARCHAR2(1000),


  		




  55
  
  
    
	doctype		VARCHAR2(1000),


  		




  
  65
  
    
	docname		VARCHAR2(100),


  		




  
  66
  
    
	doctype		VARCHAR2(100),


  		




  56
  67
  
    
	date_created	DATE,


  		




  57
  68
  
    
	date_updated	DATE,


  		




  58
  69
  
    
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),


  		




  ......




  60
  71
  
    
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes


  		




  61
  72
  
    
);


  		




  62
  73
  
    

  		




  
  74
  
    
CREATE SEQUENCE xml_documents_id_seq;


  		




  
  75
  
    

  		




  
  76
  
    
CREATE TRIGGER xml_documents_before_insert


  		




  
  77
  
    
BEFORE INSERT ON xml_documents FOR EACH ROW


  		




  
  78
  
    
BEGIN


  		




  
  79
  
    
  SELECT xml_documents_id_seq.nextval


  		




  
  80
  
    
    INTO :new.docid


  		




  
  81
  
    
    FROM dual;


  		




  
  82
  
    
END;


  		




  
  83
  
    
/


  		




  
  84
  
    

  		




  
  85
  
    
/* 


  		




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


  		




  
  87
  
    
 */


  		




  
  88
  
    
CREATE TABLE xml_catalog (


  		




  
  89
  
    
	doctype		VARCHAR2(100),


  		




  
  90
  
    
	date_created	DATE,


  		




  
  91
  
    
	date_updated	DATE,


  		




  
  92
  
    
   CONSTRAINT xml_catalog_pk PRIMARY KEY (doctype)


  		




  
  93
  
    
);


  		




  
  94
  
    

  		




  
  95
  
    
CREATE TABLE xml_catalog_entities (


  		




  
  96
  
    
	entity_id	NUMBER(20),


  		




  
  97
  
    
	entity_name	VARCHAR2(100),


  		




  
  98
  
    
	entity_type	VARCHAR2(20),


  		




  
  99
  
    
	source_doctype	VARCHAR2(100),


  		




  
  100
  
    
	target_doctype	VARCHAR2(100),


  		




  
  101
  
    
	public_id	VARCHAR2(100),


  		




  
  102
  
    
	system_id	VARCHAR2(1000),


  		




  
  103
  
    
	date_created	DATE,


  		




  
  104
  
    
	date_updated	DATE,


  		




  
  105
  
    
   CONSTRAINT xml_catalog_entities_pk PRIMARY KEY (entity_id),


  		




  
  106
  
    
   CONSTRAINT xml_catalog_entities_uk UNIQUE (entity_type, source_doctype, target_doctype, public_id),


  		




  
  107
  
    
   CONSTRAINT xml_source_catalog_fk FOREIGN KEY (source_doctype) REFERENCES xml_catalog,


  		




  
  108
  
    
   CONSTRAINT xml_target_catalog_fk FOREIGN KEY (target_doctype) REFERENCES xml_catalog


  		




  
  109
  
    
);


  		




  
  110
  
    

  		




  
  111
  
    
CREATE SEQUENCE xml_entity_id_seq;


  		




  
  112
  
    

  		




  
  113
  
    
CREATE TRIGGER xml_cat_entities_before_insert


  		




  
  114
  
    
BEFORE INSERT ON xml_catalog_entities FOR EACH ROW


  		




  
  115
  
    
BEGIN


  		




  
  116
  
    
  SELECT xml_entity_id_seq.nextval


  		




  
  117
  
    
    INTO :new.entity_id


  		




  
  118
  
    
    FROM dual;


  		




  
  119
  
    
END;


  		




  
  120
  
    
/


  		












Also available in:  Unified diff