Revision 2434
Added by sgarg over 19 years ago
| src/upgrade_db_to_1_5.java | ||
|---|---|---|
| 122 | 122 |
} |
| 123 | 123 |
|
| 124 | 124 |
|
| 125 |
public static void upgrade_xml_index(Connection sqlca) throws SQLException{
|
|
| 126 |
Statement sqlStatement, sqlStatement1 = null; |
|
| 127 |
PreparedStatement pstmt = null; |
|
| 128 |
ResultSet rset, rset1 = null; |
|
| 129 |
|
|
| 130 |
// Delete old nodedata column from xml_index if one exsists |
|
| 131 |
try {
|
|
| 132 |
System.out.println( |
|
| 133 |
"Deleting old nodedata column from xml_index..."); |
|
| 134 |
|
|
| 135 |
pstmt = sqlca.prepareStatement( |
|
| 136 |
"ALTER TABLE xml_index DROP COLUMN nodedata"); |
|
| 137 |
pstmt.execute(); |
|
| 138 |
pstmt.close(); |
|
| 139 |
|
|
| 140 |
System.out.println("Done.");
|
|
| 141 |
} catch (Exception e) {
|
|
| 142 |
System.out.println(" column not found.");
|
|
| 143 |
} |
|
| 144 |
|
|
| 145 |
// Create nodedata column in xml_index |
|
| 146 |
System.out.println( |
|
| 147 |
"Creating new nodedata column in xml_index..."); |
|
| 148 |
|
|
| 149 |
if (database.equals("oracle")) {
|
|
| 150 |
pstmt = sqlca.prepareStatement( |
|
| 151 |
"ALTER TABLE xml_index ADD nodedata VARCHAR2(4000)"); |
|
| 152 |
} else {
|
|
| 153 |
pstmt = sqlca.prepareStatement( |
|
| 154 |
"ALTER TABLE xml_index ADD nodedata VARCHAR(4000)"); |
|
| 155 |
} |
|
| 156 |
pstmt.execute(); |
|
| 157 |
pstmt.close(); |
|
| 158 |
|
|
| 159 |
System.out.println("Done.");
|
|
| 160 |
|
|
| 161 |
// Copy numerical values from nodedata to |
|
| 162 |
// nodedatanumerical column in xml_index |
|
| 163 |
System.out.println( |
|
| 164 |
"Copy nodedata from xml_nodes to nodedata in xml_index..."); |
|
| 165 |
|
|
| 166 |
sqlStatement = sqlca.createStatement(); |
|
| 167 |
rset = sqlStatement.executeQuery( |
|
| 168 |
"select docid, nodeid, path from xml_index"); |
|
| 169 |
int j = 0; |
|
| 170 |
|
|
| 171 |
while (rset.next()) {
|
|
| 172 |
String docid = rset.getString(1); |
|
| 173 |
String nodeid = rset.getString(2); |
|
| 174 |
String path = rset.getString(3); |
|
| 175 |
|
|
| 176 |
sqlStatement1 = sqlca.createStatement(); |
|
| 177 |
rset1 = sqlStatement1.executeQuery( |
|
| 178 |
"select nodedata from xml_nodes where parentnodeid = " + nodeid |
|
| 179 |
+ " and docid = '" + docid |
|
| 180 |
+ "' and (nodetype = 'TEXT' or nodetype ='ATTRIBUTE')"); |
|
| 181 |
|
|
| 182 |
while (rset1.next()) {
|
|
| 183 |
String nodedata = rset1.getString(1); |
|
| 184 |
if (nodedata != null && !nodedata.trim().equals("")) {
|
|
| 185 |
j++; |
|
| 186 |
if (j % 100 == 0) {
|
|
| 187 |
System.out.println("Number of records modified:" + j);
|
|
| 188 |
} |
|
| 189 |
|
|
| 190 |
pstmt = sqlca.prepareStatement( |
|
| 191 |
"update xml_index set nodedata = ? where nodeid = " + |
|
| 192 |
nodeid + " and path = ? and docid = ? and nodedata is null"); |
|
| 193 |
pstmt.setString(1, nodedata); |
|
| 194 |
pstmt.setString(2, path); |
|
| 195 |
pstmt.setString(3, docid); |
|
| 196 |
pstmt.execute(); |
|
| 197 |
pstmt.close(); |
|
| 198 |
|
|
| 199 |
continue; |
|
| 200 |
} |
|
| 201 |
} |
|
| 202 |
rset1.close(); |
|
| 203 |
sqlStatement1.close(); |
|
| 204 |
|
|
| 205 |
} |
|
| 206 |
rset.close(); |
|
| 207 |
sqlStatement.close(); |
|
| 208 |
|
|
| 209 |
// Create index on xml_index.docid |
|
| 210 |
System.out.println("Creating index on docid column in xml_index...");
|
|
| 211 |
pstmt = sqlca.prepareStatement( |
|
| 212 |
"CREATE INDEX xml_index_idx2 ON xml_index (docid)"); |
|
| 213 |
|
|
| 214 |
pstmt.execute(); |
|
| 215 |
pstmt.close(); |
|
| 216 |
} |
|
| 217 |
|
|
| 218 |
|
|
| 219 | 125 |
public static void main(String [] ags){
|
| 220 | 126 |
|
| 221 | 127 |
try {
|
| ... | ... | |
| 228 | 134 |
// Upgrade the xml_nodes table |
| 229 | 135 |
upgrade_xml_nodes(sqlca); |
| 230 | 136 |
|
| 231 |
// Upgrade the xml_index table |
|
| 232 |
upgrade_xml_index(sqlca); |
|
| 233 |
|
|
| 234 | 137 |
// Close the connection... |
| 235 | 138 |
sqlca.close(); |
| 236 | 139 |
} catch (SQLException ex) {
|
| src/upgrade-db-to-1.5-postgres.sql | ||
|---|---|---|
| 115 | 115 |
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield |
| 116 | 116 |
); |
| 117 | 117 |
|
| 118 |
CREATE SEQUENCE xml_queryresult_id_seq; |
|
| 118 | 119 |
|
| 120 |
|
|
| src/edu/ucsb/nceas/metacat/QuerySpecification.java | ||
|---|---|---|
| 909 | 909 |
Hashtable unaccessableNodePair) |
| 910 | 910 |
{
|
| 911 | 911 |
StringBuffer self = new StringBuffer(); |
| 912 |
self.append("select docid, path, nodedata, ");
|
|
| 913 |
self.append("nodeid ");
|
|
| 914 |
self.append("from xml_index where (path like '");
|
|
| 912 |
self.append("select xml_nodes.docid, xml_index.path, xml_nodes.nodedata, ");
|
|
| 913 |
self.append("xml_nodes.parentnodeid ");
|
|
| 914 |
self.append("from xml_index, xml_nodes where xml_index.nodeid=");
|
|
| 915 |
self.append("xml_nodes.parentnodeid and (xml_index.path like '");
|
|
| 916 |
|
|
| 915 | 917 |
boolean firstfield = true; |
| 916 | 918 |
//put the returnfields into the query |
| 917 | 919 |
//the for loop allows for multiple fields |
| ... | ... | |
| 921 | 923 |
self.append((String) returnFieldList.elementAt(i)); |
| 922 | 924 |
self.append("' ");
|
| 923 | 925 |
} else {
|
| 924 |
self.append("or path like '");
|
|
| 926 |
self.append("or xml_index.path like '");
|
|
| 925 | 927 |
self.append((String) returnFieldList.elementAt(i)); |
| 926 | 928 |
self.append("' ");
|
| 927 | 929 |
} |
| 928 | 930 |
} |
| 929 |
self.append(") AND docid in (");
|
|
| 931 |
self.append(") AND xml_nodes.docid in (");
|
|
| 930 | 932 |
self.append(doclist); |
| 931 |
self.append(")");
|
|
| 932 |
//self.append(") AND xml_nodes.nodetype = 'TEXT'");
|
|
| 933 |
self.append(") AND xml_nodes.nodetype = 'TEXT'");
|
|
| 933 | 934 |
|
| 934 | 935 |
addAccessRestrictionSQL(unaccessableNodePair, self); |
| 935 | 936 |
|
Also available in: Unified diff
Modified code such that nodedata column in xml_index is not created and used. So now we are using the same logic for using xml_index which was used for metacat release 1.4