Revision 20
Added by Matt Jones almost 25 years ago
| src/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 |
|
|
| src/edu/ucsb/nceas/metacat/DBSAXNode.java | ||
|---|---|---|
| 42 | 42 |
PreparedStatement pstmt; |
| 43 | 43 |
if (parent_id != 0) {
|
| 44 | 44 |
pstmt = conn.prepareStatement( |
| 45 |
"INSERT INTO xml_elements(nodeid, nodename, parentnodeid) " +
|
|
| 46 |
"VALUES (null, ?, ?)");
|
|
| 45 |
"INSERT INTO xml_nodes (nodeid, nodetype, " +
|
|
| 46 |
"nodename, parentnodeid) VALUES (null, ?, ?, ?)");
|
|
| 47 | 47 |
} else {
|
| 48 | 48 |
pstmt = conn.prepareStatement( |
| 49 |
"INSERT INTO xml_elements(nodeid, nodename) " +
|
|
| 50 |
"VALUES (null, ?)"); |
|
| 49 |
"INSERT INTO xml_nodes (nodeid, nodetype, nodename) " +
|
|
| 50 |
"VALUES (null, ?, ?)");
|
|
| 51 | 51 |
} |
| 52 | 52 |
|
| 53 | 53 |
// Bind the values to the query |
| 54 |
pstmt.setString(1, getTagName());// The second ? is for NODENAME |
|
| 54 |
pstmt.setString(1, "ELEMENT"); |
|
| 55 |
pstmt.setString(2, getTagName()); |
|
| 55 | 56 |
if (parent_id != 0) {
|
| 56 |
pstmt.setLong(2, parent_id);
|
|
| 57 |
pstmt.setLong(3, parent_id);
|
|
| 57 | 58 |
} |
| 58 | 59 |
// Do the insertion |
| 59 | 60 |
pstmt.execute(); |
| ... | ... | |
| 72 | 73 |
Statement stmt; |
| 73 | 74 |
try {
|
| 74 | 75 |
stmt = conn.createStatement(); |
| 75 |
stmt.execute("SELECT xml_elements_id_seq.currval FROM dual");
|
|
| 76 |
stmt.execute("SELECT xml_nodes_id_seq.currval FROM dual");
|
|
| 76 | 77 |
try {
|
| 77 | 78 |
ResultSet rs = stmt.getResultSet(); |
| 78 | 79 |
try {
|
| ... | ... | |
| 140 | 141 |
// And enter the attribute in the database |
| 141 | 142 |
try {
|
| 142 | 143 |
PreparedStatement pstmt = conn.prepareStatement( |
| 143 |
"INSERT INTO xml_attributes(attributeid, nodeid, " + |
|
| 144 |
"attributename, attributevalue) " + |
|
| 145 |
"VALUES (null, ?, ?, ?)"); |
|
| 144 |
"INSERT INTO xml_nodes (nodeid, nodetype, " + |
|
| 145 |
"nodename, nodedata, parentnodeid) VALUES (null, ?, ?, ?, ?)"); |
|
| 146 | 146 |
|
| 147 | 147 |
// Bind the values to the query |
| 148 |
pstmt.setLong(1, getElementID());
|
|
| 148 |
pstmt.setString(1, "ATTRIBUTE");
|
|
| 149 | 149 |
pstmt.setString(2, attName); |
| 150 | 150 |
pstmt.setString(3, attValue); |
| 151 |
pstmt.setLong(4, getElementID()); |
|
| 151 | 152 |
|
| 152 | 153 |
// Do the insertion |
| 153 | 154 |
pstmt.execute(); |
| ... | ... | |
| 185 | 186 |
public void writeContentToDB() {
|
| 186 | 187 |
try {
|
| 187 | 188 |
PreparedStatement pstmt = conn.prepareStatement( |
| 188 |
"UPDATE xml_elements SET nodedata = ? WHERE nodeid = ?");
|
|
| 189 |
"UPDATE xml_nodes SET nodedata = ? WHERE nodeid = ?");
|
|
| 189 | 190 |
|
| 190 | 191 |
// Bind the values to the query |
| 191 | 192 |
pstmt.setString(1, getContent());// The first ? is for NODEDATA |
| src/edu/ucsb/nceas/metacat/DBSAXElement.java | ||
|---|---|---|
| 42 | 42 |
PreparedStatement pstmt; |
| 43 | 43 |
if (parent_id != 0) {
|
| 44 | 44 |
pstmt = conn.prepareStatement( |
| 45 |
"INSERT INTO xml_elements(nodeid, nodename, parentnodeid) " +
|
|
| 46 |
"VALUES (null, ?, ?)");
|
|
| 45 |
"INSERT INTO xml_nodes (nodeid, nodetype, " +
|
|
| 46 |
"nodename, parentnodeid) VALUES (null, ?, ?, ?)");
|
|
| 47 | 47 |
} else {
|
| 48 | 48 |
pstmt = conn.prepareStatement( |
| 49 |
"INSERT INTO xml_elements(nodeid, nodename) " +
|
|
| 50 |
"VALUES (null, ?)"); |
|
| 49 |
"INSERT INTO xml_nodes (nodeid, nodetype, nodename) " +
|
|
| 50 |
"VALUES (null, ?, ?)");
|
|
| 51 | 51 |
} |
| 52 | 52 |
|
| 53 | 53 |
// Bind the values to the query |
| 54 |
pstmt.setString(1, getTagName());// The second ? is for NODENAME |
|
| 54 |
pstmt.setString(1, "ELEMENT"); |
|
| 55 |
pstmt.setString(2, getTagName()); |
|
| 55 | 56 |
if (parent_id != 0) {
|
| 56 |
pstmt.setLong(2, parent_id);
|
|
| 57 |
pstmt.setLong(3, parent_id);
|
|
| 57 | 58 |
} |
| 58 | 59 |
// Do the insertion |
| 59 | 60 |
pstmt.execute(); |
| ... | ... | |
| 72 | 73 |
Statement stmt; |
| 73 | 74 |
try {
|
| 74 | 75 |
stmt = conn.createStatement(); |
| 75 |
stmt.execute("SELECT xml_elements_id_seq.currval FROM dual");
|
|
| 76 |
stmt.execute("SELECT xml_nodes_id_seq.currval FROM dual");
|
|
| 76 | 77 |
try {
|
| 77 | 78 |
ResultSet rs = stmt.getResultSet(); |
| 78 | 79 |
try {
|
| ... | ... | |
| 140 | 141 |
// And enter the attribute in the database |
| 141 | 142 |
try {
|
| 142 | 143 |
PreparedStatement pstmt = conn.prepareStatement( |
| 143 |
"INSERT INTO xml_attributes(attributeid, nodeid, " + |
|
| 144 |
"attributename, attributevalue) " + |
|
| 145 |
"VALUES (null, ?, ?, ?)"); |
|
| 144 |
"INSERT INTO xml_nodes (nodeid, nodetype, " + |
|
| 145 |
"nodename, nodedata, parentnodeid) VALUES (null, ?, ?, ?, ?)"); |
|
| 146 | 146 |
|
| 147 | 147 |
// Bind the values to the query |
| 148 |
pstmt.setLong(1, getElementID());
|
|
| 148 |
pstmt.setString(1, "ATTRIBUTE");
|
|
| 149 | 149 |
pstmt.setString(2, attName); |
| 150 | 150 |
pstmt.setString(3, attValue); |
| 151 |
pstmt.setLong(4, getElementID()); |
|
| 151 | 152 |
|
| 152 | 153 |
// Do the insertion |
| 153 | 154 |
pstmt.execute(); |
| ... | ... | |
| 185 | 186 |
public void writeContentToDB() {
|
| 186 | 187 |
try {
|
| 187 | 188 |
PreparedStatement pstmt = conn.prepareStatement( |
| 188 |
"UPDATE xml_elements SET nodedata = ? WHERE nodeid = ?");
|
|
| 189 |
"UPDATE xml_nodes SET nodedata = ? WHERE nodeid = ?");
|
|
| 189 | 190 |
|
| 190 | 191 |
// Bind the values to the query |
| 191 | 192 |
pstmt.setString(1, getContent());// The first ? is for NODEDATA |
| 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 |
|
|
| DBSAXElement.java | ||
|---|---|---|
| 42 | 42 |
PreparedStatement pstmt; |
| 43 | 43 |
if (parent_id != 0) {
|
| 44 | 44 |
pstmt = conn.prepareStatement( |
| 45 |
"INSERT INTO xml_elements(nodeid, nodename, parentnodeid) " +
|
|
| 46 |
"VALUES (null, ?, ?)");
|
|
| 45 |
"INSERT INTO xml_nodes (nodeid, nodetype, " +
|
|
| 46 |
"nodename, parentnodeid) VALUES (null, ?, ?, ?)");
|
|
| 47 | 47 |
} else {
|
| 48 | 48 |
pstmt = conn.prepareStatement( |
| 49 |
"INSERT INTO xml_elements(nodeid, nodename) " +
|
|
| 50 |
"VALUES (null, ?)"); |
|
| 49 |
"INSERT INTO xml_nodes (nodeid, nodetype, nodename) " +
|
|
| 50 |
"VALUES (null, ?, ?)");
|
|
| 51 | 51 |
} |
| 52 | 52 |
|
| 53 | 53 |
// Bind the values to the query |
| 54 |
pstmt.setString(1, getTagName());// The second ? is for NODENAME |
|
| 54 |
pstmt.setString(1, "ELEMENT"); |
|
| 55 |
pstmt.setString(2, getTagName()); |
|
| 55 | 56 |
if (parent_id != 0) {
|
| 56 |
pstmt.setLong(2, parent_id);
|
|
| 57 |
pstmt.setLong(3, parent_id);
|
|
| 57 | 58 |
} |
| 58 | 59 |
// Do the insertion |
| 59 | 60 |
pstmt.execute(); |
| ... | ... | |
| 72 | 73 |
Statement stmt; |
| 73 | 74 |
try {
|
| 74 | 75 |
stmt = conn.createStatement(); |
| 75 |
stmt.execute("SELECT xml_elements_id_seq.currval FROM dual");
|
|
| 76 |
stmt.execute("SELECT xml_nodes_id_seq.currval FROM dual");
|
|
| 76 | 77 |
try {
|
| 77 | 78 |
ResultSet rs = stmt.getResultSet(); |
| 78 | 79 |
try {
|
| ... | ... | |
| 140 | 141 |
// And enter the attribute in the database |
| 141 | 142 |
try {
|
| 142 | 143 |
PreparedStatement pstmt = conn.prepareStatement( |
| 143 |
"INSERT INTO xml_attributes(attributeid, nodeid, " + |
|
| 144 |
"attributename, attributevalue) " + |
|
| 145 |
"VALUES (null, ?, ?, ?)"); |
|
| 144 |
"INSERT INTO xml_nodes (nodeid, nodetype, " + |
|
| 145 |
"nodename, nodedata, parentnodeid) VALUES (null, ?, ?, ?, ?)"); |
|
| 146 | 146 |
|
| 147 | 147 |
// Bind the values to the query |
| 148 |
pstmt.setLong(1, getElementID());
|
|
| 148 |
pstmt.setString(1, "ATTRIBUTE");
|
|
| 149 | 149 |
pstmt.setString(2, attName); |
| 150 | 150 |
pstmt.setString(3, attValue); |
| 151 |
pstmt.setLong(4, getElementID()); |
|
| 151 | 152 |
|
| 152 | 153 |
// Do the insertion |
| 153 | 154 |
pstmt.execute(); |
| ... | ... | |
| 185 | 186 |
public void writeContentToDB() {
|
| 186 | 187 |
try {
|
| 187 | 188 |
PreparedStatement pstmt = conn.prepareStatement( |
| 188 |
"UPDATE xml_elements SET nodedata = ? WHERE nodeid = ?");
|
|
| 189 |
"UPDATE xml_nodes SET nodedata = ? WHERE nodeid = ?");
|
|
| 189 | 190 |
|
| 190 | 191 |
// Bind the values to the query |
| 191 | 192 |
pstmt.setString(1, getContent());// The first ? is for NODEDATA |
Also available in: Unified diff
consolidated table structure to eliminate xml_attributes and xml_elements in favor of a single, unified xml_nodes table