Revision 2519
Added by sgarg almost 19 years ago
xmltables-postgres.sql | ||
---|---|---|
79 | 79 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
80 | 80 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
81 | 81 |
|
82 |
|
|
82 | 83 |
/* |
84 |
* Table for storing the nodes for the old revisions of the document and the deleted documents |
|
85 |
*/ |
|
86 |
CREATE TABLE xml_nodes_revisions ( |
|
87 |
nodeid INT8, -- the unique node id (pk) |
|
88 |
nodeindex INT8, -- order of nodes within parent |
|
89 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI, |
|
90 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
91 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
92 |
nodeprefix VARCHAR(50), -- the namespace prefix of the node |
|
93 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
94 |
-- for TEXT it is the content) |
|
95 |
parentnodeid INT8, -- index of the parent of this node |
|
96 |
rootnodeid INT8, -- index of the root node of this tree |
|
97 |
docid VARCHAR(250), -- index to the document id |
|
98 |
date_created DATE, |
|
99 |
date_updated DATE, |
|
100 |
nodedatanumerical FLOAT8, -- the data for this node if |
|
101 |
-- if it is a number |
|
102 |
CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid), |
|
103 |
CONSTRAINT xml_nodes_revisions_root_fk |
|
104 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions, |
|
105 |
CONSTRAINT xml_nodes_revisions_parent_fk |
|
106 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions |
|
107 |
); |
|
108 |
|
|
109 |
/* |
|
110 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revisions |
|
111 |
*/ |
|
112 |
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid); |
|
113 |
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid); |
|
114 |
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename); |
|
115 |
|
|
116 |
|
|
117 |
|
|
118 |
/* |
|
83 | 119 |
* XML Catalog -- table to store all external sources for XML documents |
84 | 120 |
*/ |
85 | 121 |
CREATE SEQUENCE xml_catalog_id_seq; |
... | ... | |
320 | 356 |
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata); |
321 | 357 |
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical); |
322 | 358 |
|
323 |
|
|
324 | 359 |
/* |
325 |
* Table for storing the nodes for the old revisions of the document and the deleted documents |
|
326 |
*/ |
|
327 |
CREATE TABLE xml_nodes_revisions ( |
|
328 |
nodeid INT8, -- the unique node id (pk) |
|
329 |
nodeindex INT8, -- order of nodes within parent |
|
330 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI, |
|
331 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
332 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
333 |
nodeprefix VARCHAR(50), -- the namespace prefix of the node |
|
334 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
335 |
-- for TEXT it is the content) |
|
336 |
parentnodeid INT8, -- index of the parent of this node |
|
337 |
rootnodeid INT8, -- index of the root node of this tree |
|
338 |
docid VARCHAR(250), -- index to the document id |
|
339 |
date_created DATE, |
|
340 |
date_updated DATE, |
|
341 |
nodedatanumerical FLOAT8, -- the data for this node if |
|
342 |
-- if it is a number |
|
343 |
CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid), |
|
344 |
CONSTRAINT xml_nodes_revisions_root_fk |
|
345 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions, |
|
346 |
CONSTRAINT xml_nodes_revisions_parent_fk |
|
347 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions |
|
348 |
); |
|
349 |
|
|
350 |
/* |
|
351 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revisions |
|
352 |
*/ |
|
353 |
CREATE INDEX xml_nodes_revision_idx1 ON xml_nodes_revisions (rootnodeid); |
|
354 |
CREATE INDEX xml_nodes_revision_idx2 ON xml_nodes_revisions (parentnodeid); |
|
355 |
CREATE INDEX xml_nodes_revision_idx3 ON xml_nodes_revisions (nodename); |
|
356 |
|
|
357 |
|
|
358 |
|
|
359 |
|
|
360 |
/* |
|
361 | 360 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
362 | 361 |
*/ |
363 | 362 |
CREATE TABLE harvest_site_schedule ( |
Also available in: Unified diff
Added code to create new tables xml_path_index and xml_nodes_revisions...