Project

General

Profile

1
/**
2
 *  '$RCSfile$'
3
 *  Copyright: 2004 Regents of the University of California and the
4
 *             National Center for Ecological Analysis and Synthesis
5
 *
6
 *   '$Author: sgarg $'
7
 *     '$Date: 2005-07-25 11:45:45 -0700 (Mon, 25 Jul 2005) $'
8
 * '$Revision: 2520 $'
9
 *
10
 * This program is free software; you can redistribute it and/or modify
11
 * it under the terms of the GNU General Public License as published by
12
 * the Free Software Foundation; either version 2 of the License, or
13
 * (at your option) any later version.
14
 *
15
 * This program is distributed in the hope that it will be useful,
16
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18
 * GNU General Public License for more details.
19
 *
20
 * You should have received a copy of the GNU General Public License
21
 * along with this program; if not, write to the Free Software
22
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
23
 */
24
 
25

    
26
/**
27
 * Create the xml_path_index table
28
 */
29

    
30
CREATE TABLE xml_path_index (
31
        nodeid          NUMBER(20),     -- the unique node id
32
        docid           VARCHAR2(250),  -- index to the document id
33
        path            VARCHAR2(1000), -- precomputed path through tree
34
	nodedata        VARCHAR2(4000), -- the data for this node (e.g.,
35
                                        -- for TEXT it is the content)
36
        nodedatanumerical NUMBER,       -- the data for this node if
37
                                        -- it is a number
38
        parentnodeid    NUMBER(20),     -- index of the parent of this node
39

    
40
   CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
41
   CONSTRAINT xml_path_index_docid_fk
42
                FOREIGN KEY (docid) REFERENCES xml_documents
43
);
44
                                                                                                                                                             
45
CREATE SEQUENCE xml_path_index_id_seq;
46
                                                                                                                                                             
47
CREATE TRIGGER xml_path_index_before_insert
48
BEFORE INSERT ON xml_path_indexn FOR EACH ROW
49
BEGIN
50
  SELECT xml_path_index_id_seq.nextval
51
    INTO :new.relationid
52
    FROM dual;
53
END;
54
/
55

    
56

    
57
/*
58
 * Index of the path, nodedata, nodedatanumerical in xml_path_index
59
 */
60
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
61
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
62
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
63

    
64

    
65

    
66
/**
67
 * Create the xml_nodes_revisions table
68
 * to store nodes from xml_nodes which 
69
 * are of old revisions and deleted document
70
 */
71

    
72
CREATE TABLE xml_nodes_revisions (
73
        nodeid          NUMBER(20),     -- the unique node id (pk)
74
        nodeindex       NUMBER(10),     -- order of nodes within parent
75
        nodetype        VARCHAR2(20),   -- type (DOCUMENT, COMMENT, PI,
76
                                        -- ELEMENT, ATTRIBUTE, TEXT)
77
        nodename        VARCHAR2(250),  -- the name of an element or attribute
78
        nodeprefix      VARCHAR2(50),   -- the namespace prefix of an element
79
                                        -- or attribute
80
        nodedata        VARCHAR2(4000), -- the data for this node (e.g.,
81
                                        -- for TEXT it is the content)
82
        parentnodeid    NUMBER(20),     -- index of the parent of this node
83
        rootnodeid      NUMBER(20),     -- index of the root node of this tree
84
        docid           VARCHAR2(250),  -- index to the document id
85
        date_created    DATE,
86
        date_updated    DATE,
87
        nodedatanumerical NUMBER,       -- the data for this node if
88
                                        -- it is a number
89
   CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
90
   CONSTRAINT xml_nodes_revisions_root_fk
91
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
92
   CONSTRAINT xml_nodes_revisions_parent_fk
93
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
94
);
95

    
96

    
97
/**
98
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revision
99
 */
100
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid);
101
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);
102
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);
103

    
104

    
105
/**
106
 * Drop the constraint from xml_revisions which points to xml_nodes
107
 */
108
ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;
109

    
110

    
111
/**
112
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
113
 * of the documents and deleted documents
114
 */
115
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype,
116
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid,
117
docid, date_created, date_updated, nodedatanumerical)
118
SELECT * FROM xml_nodes WHERE rootnodeid NOT IN
119
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
120

    
121

    
122
/**
123
 * Create the key constraint in xml_revisions which points to 
124
 * xml_nodes_revisions
125
 */
126
ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk
127
 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions (nodeid);
128
 
129

    
130
/**
131
 * Delete the records from xml_index table which point to old revisions in xml_index
132
 * This is possible for documents for which the indexing thread failed during UPDATE
133
 */
134

    
135
DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes WHERE
136
rootnodeid NOT IN (SELECT rootnodeid FROM xml_documents WHERE rootnodeid IS NOT NULL)); 
137

    
138
/**
139
 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 
140
 */
141
DELETE FROM xml_nodes WHERE rootnodeid NOT IN
142
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
(23-23/28)