Project

General

Profile

metacat / src / xmltables.sql @ 175

1
/*
2
 * xmltables.sql -- Create or replace tables for storing XML in the db
3
 *
4
 *      Purpose: creates tables needed for XML database
5
 * 
6
 *      Created: 12 September 1999
7
 *       Author: Matt Jones
8
 * Organization: National Center for Ecological Analysis and Synthesis
9
 *    Copyright: 2000 Regents of the University of California and the
10
 *               National Center for Ecological Analysis and Synthesis
11
 *  For Details: http://www.nceas.ucsb.edu/
12
 *    File Info: '$Id: xmltables.sql 175 2000-06-19 21:24:10Z bojilova $'
13
 *
14
 */
15

    
16
/*
17
 * Drop all of the objects in proper order
18
 */
19
set echo off
20

    
21
DROP SEQUENCE xml_nodes_id_seq;
22
DROP SEQUENCE xml_catalog_id_seq;
23

    
24
DROP TRIGGER xml_nodes_before_insert;
25
DROP TRIGGER xml_catalog_before_insert;
26

    
27
DROP TABLE xml_index;
28
DROP TABLE xml_catalog;
29
DROP TABLE xml_documents;
30
DROP TABLE xml_nodes;
31
DROP TABLE xml_acc_numbers;
32

    
33
/* 
34
 * Nodes -- table to store XML Nodes (both elements and attributes)
35
 */
36
CREATE TABLE xml_nodes (
37
        nodeid                NUMBER(20),        -- the unique node id (pk)
38
        nodeindex        NUMBER(10),        -- order of nodes within parent
39
        nodetype        VARCHAR2(20),        -- type (DOCUMENT, COMMENT, PI,
40
                                        -- ELEMENT, ATTRIBUTE, TEXT)
41
        nodename        VARCHAR2(250),        -- the name of an element or attribute
42
        nodedata        VARCHAR2(4000), -- the data for this node (e.g., 
43
                                        -- for TEXT it is the content)
44
        parentnodeid        NUMBER(20),        -- index of the parent of this node
45
        rootnodeid        NUMBER(20),        -- index of the root node of this tree
46
        docid                VARCHAR2(55),        -- index to the document id
47
        date_created        DATE,
48
        date_updated        DATE,
49
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
50
   CONSTRAINT xml_nodes_root_fk 
51
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
52
   CONSTRAINT xml_nodes_parent_fk 
53
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
54
);
55

    
56
CREATE SEQUENCE xml_nodes_id_seq;
57

    
58
/* 
59
 * Documents -- table to store XML documents
60
 */
61
CREATE TABLE xml_documents (
62
        docid                VARCHAR2(250),        -- the document id #
63
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
64
        docname                VARCHAR2(100),        -- usually the root element name
65
        doctype                VARCHAR2(100),        -- public id indicating document type
66
        doctitle        VARCHAR2(1000),        -- title of document if exists
67
        date_created        DATE,
68
        date_updated        DATE,
69
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
70
   CONSTRAINT xml_documents_root_fk 
71
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
72
);
73

    
74
/* 
75
 * XML Catalog -- table to store all external sources for XML documents
76
 */
77
CREATE TABLE xml_catalog (
78
        catalog_id        NUMBER(20),        -- the id for this catalog entry
79
        entry_type        VARCHAR2(500),        -- the type of this catalog entry
80
                                        -- (e.g., DTD, XSD, XSL)
81
        source_doctype        VARCHAR2(500),        -- the source public_id for transforms
82
        target_doctype        VARCHAR2(500),        -- the target public_id for transforms
83
        public_id        VARCHAR2(500),        -- the unique id for this type
84
        system_id        VARCHAR2(1000),        -- the local location of the object
85
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id)
86
   -- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
87
);
88

    
89
CREATE SEQUENCE xml_catalog_id_seq;
90

    
91
CREATE TRIGGER xml_catalog_before_insert
92
BEFORE INSERT ON xml_catalog FOR EACH ROW
93
BEGIN
94
  SELECT xml_catalog_id_seq.nextval
95
    INTO :new.catalog_id
96
    FROM dual;
97
END;
98
/
99

    
100
/* 
101
 * Index of Nodes -- table to store precomputed paths through tree for 
102
 * quick searching in structured searches
103
 */
104
CREATE TABLE xml_index (
105
        nodeid                NUMBER(20),        -- the unique node id
106
        path                VARCHAR2(20),        -- precomputed path through tree
107
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
108
   CONSTRAINT xml_nodes_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes
109
);
110

    
111
/* 
112
 * table to store unique Accession # for every document in 2 parts
113
 */
114
CREATE TABLE xml_acc_numbers (
115
        global_name        VARCHAR2(32),        -- first part of acc #
116
        local_id        NUMBER(20),        -- second part - unique in global name
117
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)
118
);