Project

General

Profile

Revision 6020

use the jaxb date parser for ISO 8601 formats. the numeric and date node values are now calculated after the document has been successfully inserted in the db so any sql exceptions do not prevent the raw node data from being saved.
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=2084

View differences:

src/edu/ucsb/nceas/metacat/DocumentImpl.java
45 45
import java.sql.SQLException;
46 46
import java.sql.Statement;
47 47
import java.sql.Timestamp;
48
import java.util.Calendar;
48 49
import java.util.Hashtable;
49 50
import java.util.HashMap;
50 51
import java.util.Iterator;
......
55 56
import java.util.regex.Matcher;
56 57
import java.util.regex.Pattern;
57 58

  
59
import javax.xml.bind.DatatypeConverter;
60

  
58 61
import edu.ucsb.nceas.metacat.accesscontrol.AccessControlInterface;
59 62
import edu.ucsb.nceas.metacat.accesscontrol.AccessControlList;
60 63
import edu.ucsb.nceas.metacat.client.InsufficientKarmaException;
......
2692 2695
                    conn.commit();
2693 2696
                    conn.setAutoCommit(true);
2694 2697
                    
2698
                    // update the node data to include numeric and date values
2699
                    updateNodeValues(conn, docid);
2700
                    
2695 2701
                    //write the file to disk
2696 2702
                    logMetacat.debug("DocumentImpl.write - Writing xml to file system");                    
2697 2703
                	writeToFileSystem(xmlString, accnum, encoding);
......
2777 2783
            conn.commit();
2778 2784
            conn.setAutoCommit(true);
2779 2785
            
2786
            //update nodes
2787
            updateNodeValues(conn, docid);
2788
            
2780 2789
            //write the file to disk
2781

  
2782 2790
        	writeToFileSystem(xmlString, accnum, encoding);
2783 2791

  
2784 2792
            addDocidToIndexingQueue(docid, rev);
......
3703 3711
       
3704 3712
    }
3705 3713
    
3714
    private static void updateNodeValues(DBConnection dbConnection, String docid) throws SQLException {
3715
    	PreparedStatement sqlStatement = null;
3716
        PreparedStatement pstmt = null;
3717
        ResultSet rset = null;
3718

  
3719
        sqlStatement = dbConnection.prepareStatement(
3720
        		"SELECT DISTINCT NODEID, NODEDATA "
3721
                + "FROM xml_nodes "
3722
                + "WHERE nodedata IS NOT NULL "
3723
                + "AND docid = ?");
3724
        sqlStatement.setString(1, docid);
3725
        rset = sqlStatement.executeQuery();
3726

  
3727
        int count = 0;
3728
        while (rset.next()) {
3729

  
3730
            String nodeid = rset.getString(1);
3731
            String nodedata = rset.getString(2);
3732

  
3733
            try {
3734
                if (!nodedata.trim().equals("")) {
3735
                	System.out.println(nodedata);
3736
                	
3737
                	try {
3738
                		double dataNumeric = Double.parseDouble(nodedata);
3739
	                    pstmt = dbConnection.prepareStatement(
3740
	                        "UPDATE xml_nodes " +
3741
	                        " SET nodedatanumerical = ?" +
3742
	                        " WHERE nodeid = " + nodeid);
3743
	                    pstmt.setDouble(1, dataNumeric);
3744
	                    pstmt.execute();
3745
	                    pstmt.close();
3746
                	} catch (Exception e) {
3747
                		// try a date
3748
                		try {
3749
	                		Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata);
3750
		                    Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis());
3751
		                    pstmt = dbConnection.prepareStatement(
3752
		                        "UPDATE xml_nodes " +
3753
		                        " SET nodedatadate = ?" +
3754
		                        " WHERE nodeid = " + nodeid);
3755
		                    pstmt.setTimestamp(1, dataTimestamp);
3756
		                    pstmt.execute();
3757
		                    pstmt.close();
3758
                		} catch (Exception e2) {
3759
							// we are done with this node
3760
						} 
3761
					}
3762

  
3763
                    count++;
3764
                    if (count%5 == 0) {
3765
                        System.out.println(count + "...");
3766
                    }
3767
            		
3768
                }
3769
            } catch (Exception e) {
3770
            	// do nothing, was not a valid date
3771
            	e.printStackTrace();
3772
            } 
3773
        }
3774

  
3775
        rset.close();
3776
        sqlStatement.close();
3777
    }
3778
    
3706 3779
    private static void moveNodesToNodesRevision(DBConnection dbconn,
3707 3780
                                       long rootNodeId) throws Exception
3708 3781
    {
src/edu/ucsb/nceas/metacat/DBSAXNode.java
26 26

  
27 27
package edu.ucsb.nceas.metacat;
28 28

  
29
import java.sql.*;
30
import java.text.ParseException;
31
import java.text.SimpleDateFormat;
32
import java.util.Date;
29
import java.sql.PreparedStatement;
30
import java.sql.ResultSet;
31
import java.sql.SQLException;
32
import java.sql.Statement;
33
import java.util.Enumeration;
33 34
import java.util.Hashtable;
34
import java.util.Enumeration;
35

  
35 36
import org.apache.log4j.Logger;
36 37
import org.xml.sax.SAXException;
37 38

  
......
176 177
    {
177 178

  
178 179
      PreparedStatement pstmt;
179
      Timestamp timestampData = null;
180 180
      
181 181
      if (nodetype == "DOCUMENT") {
182 182
        pstmt = connection.prepareStatement(
......
184 184
            "(nodetype, nodename, nodeprefix, docid) " +
185 185
            "VALUES (?, ?, ?, ?)");
186 186

  
187
        // Increase DBConnection usage count
188
        connection.increaseUsageCount(1);
189 187
        logMetacat.debug("DBSAXNode.writeChildNodeToDBDataLimited - inserting doc name: " + nodename);
190 188
      } else {
191
          if(data != null && !data.trim().equals("")
192
             && !data.trim().equals("NaN") && !data.trim().equalsIgnoreCase("Infinity")){
193
        	  try{
194
        		  // try some common ISO 8601 formats
195
        		  SimpleDateFormat sdf = null;
196
        		  if (data.length() == 10) {
197
        			  sdf = new SimpleDateFormat("yyyy-MM-dd");
198
        		  } else if (data.length() == 19) {
199
        			  sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
200
        		  } else {
201
        			  // throw this so we continue with parsing as numeric or string.
202
        			  throw new ParseException("String length will not match date/dateTime patterns", -1);
203
        		  }
204
        		  Date dateData = sdf.parse(data);
205
        		  timestampData = new Timestamp(dateData.getTime());
206
                  pstmt = connection.prepareStatement(
207
                      "INSERT INTO xml_nodes " +
208
                      "(nodetype, nodename, nodeprefix, docid, " +
209
                      "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatadate) " +
210
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
211
              } catch (ParseException pe) {
212
	        	  try{
213
	                  double numberData = Double.parseDouble(data);
214
	                  pstmt = connection.prepareStatement(
215
	                      "INSERT INTO xml_nodes " +
216
	                      "(nodetype, nodename, nodeprefix, docid, " +
217
	                      "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatanumerical) " +
218
	                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, "+ numberData +")");
219
	              } catch (NumberFormatException nfe) {
220
	                  pstmt = connection.prepareStatement(
221
	                      "INSERT INTO xml_nodes " +
222
	                      "(nodetype, nodename, nodeprefix, docid, " +
223
	                      "rootnodeid, parentnodeid, nodedata, nodeindex) " +
224
	                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
225
	              }
226
              }
227
          } else {
228
              pstmt = connection.prepareStatement(
229
                  "INSERT INTO xml_nodes " +
230
                  "(nodetype, nodename, nodeprefix, docid, " +
231
                  "rootnodeid, parentnodeid, nodedata, nodeindex) " +
232
                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
233
          }
234
        // Increase DBConnection usage count
235
        connection.increaseUsageCount(1);
189
          pstmt = connection.prepareStatement(
190
              "INSERT INTO xml_nodes " +
191
              "(nodetype, nodename, nodeprefix, docid, " +
192
              "rootnodeid, parentnodeid, nodedata, nodeindex) " +
193
              "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
236 194
      }
237

  
195
      
196
      // Increase DBConnection usage count
197
      connection.increaseUsageCount(1);
198
      
238 199
      // Bind the values to the query
239 200
      pstmt.setString(1, nodetype);
240 201
      int idx;
......
259 220
          pstmt.setInt(8, incChildNum());
260 221
        }
261 222
      }
262
      if (timestampData != null) {
263
    	  pstmt.setTimestamp(9, timestampData);
264
      }
265 223
      // Do the insertion
266 224
      logMetacat.debug("DBSAXNode.writeChildNodeToDBDataLimited - SQL insert: " + pstmt.toString());
267 225
      pstmt.execute();
......
271 229
      nid = DatabaseService.getInstance().getDBAdapter().getUniqueID(connection.getConnections(), "xml_nodes");
272 230
      //should increase connection usage!!!!!!
273 231

  
274

  
275 232
      if (nodetype.equals("DOCUMENT")) {
276 233
        // Record the root node id that was generated from the database
277 234
        setRootNodeID(nid);
......
356 313
    {
357 314

  
358 315
      PreparedStatement pstmt;
359
      Timestamp timestampData = null;
360 316
      logMetacat.info("DBSAXNode.writeDTDNodeToDB - Insert dtd into db: "+nodename +" "+data);
361
      if(data != null && !data.trim().equals("")){
362
    	  try{
363
    		  // try some common ISO 8601 formats
364
    		  SimpleDateFormat sdf = null;
365
    		  if (data.length() == 10) {
366
    			  sdf = new SimpleDateFormat("yyyy-MM-dd");
367
    		  } else if (data.length() == 19) {
368
    			  sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
369
    		  }
370
    		  Date dateData = sdf.parse(data);
371
    		  timestampData = new Timestamp(dateData.getTime());
372
              pstmt = connection.prepareStatement(
373
                  "INSERT INTO xml_nodes " +
374
                  "(nodetype, nodename, docid, " +
375
                  "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatadate) " +
376
                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
377
          } catch (ParseException pe) {
378
    	  	try{
379
                double numberData = Double.parseDouble(data);
380
                pstmt = connection.prepareStatement(
381
                    "INSERT INTO xml_nodes " +
382
                    "(nodetype, nodename, docid, " +
383
                    "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatanumerical) " +
384
                    "VALUES (?, ?, ?, ?, ?, ?, ?, "+ numberData +")");
385
            } catch (NumberFormatException nfe) {
386
                pstmt = connection.prepareStatement(
387
                    "INSERT INTO xml_nodes " +
388
                    "(nodetype, nodename,  docid, " +
389
                    "rootnodeid, parentnodeid, nodedata, nodeindex) " +
390
                    "VALUES (?, ?, ?, ?, ?, ?, ?)");
391
            }
392
          }
393
        } else {
394
            pstmt = connection.prepareStatement(
395
                  "INSERT INTO xml_nodes " +
396
                  "(nodetype, nodename, docid, " +
397
                  "rootnodeid, parentnodeid, nodedata, nodeindex) " +
398
                  "VALUES (?, ?, ?, ?, ?, ?, ?)");
399
        }
317
  
318
      pstmt = connection.prepareStatement(
319
              "INSERT INTO xml_nodes " +
320
              "(nodetype, nodename, docid, " +
321
              "rootnodeid, parentnodeid, nodedata, nodeindex) " +
322
              "VALUES (?, ?, ?, ?, ?, ?, ?)");
400 323

  
401 324
       // Increase DBConnection usage count
402 325
      connection.increaseUsageCount(1);
......
409 332
      pstmt.setLong(5, getParentID());
410 333
      pstmt.setString(6, data);
411 334
      pstmt.setInt(7, getNodeIndex());
412
      if (timestampData != null) {
413
    	  pstmt.setTimestamp(8, timestampData);
414
      }
415 335

  
416 336
      // Do the insertion
417 337
      pstmt.execute();
......
419 339

  
420 340
      // get the generated unique id afterward
421 341
      nid = DatabaseService.getInstance().getDBAdapter().getUniqueID(connection.getConnections(), "xml_nodes");
422
      //should incease connection usage!!!!!!
423 342

  
424 343
    } catch (SQLException e) {
425 344
      System.out.println("Error in DBSaxNode.writeDTDNodeToDB");
src/edu/ucsb/nceas/metacat/QueryTerm.java
29 29

  
30 30
package edu.ucsb.nceas.metacat;
31 31

  
32
import java.text.ParseException;
33
import java.text.SimpleDateFormat;
34
import java.util.Date;
32
import java.util.Calendar;
35 33
import java.util.Vector;
34

  
35
import javax.xml.bind.DatatypeConverter;
36

  
36 37
import org.apache.log4j.Logger;
37 38

  
38 39
import edu.ucsb.nceas.metacat.shared.MetacatUtilException;
39
import edu.ucsb.nceas.metacat.util.MetacatUtil;
40 40
import edu.ucsb.nceas.metacat.util.SystemUtil;
41 41

  
42 42
/** a utility class that represents a single term in a query */
......
212 212
                return null;
213 213
            }
214 214
            
215
            
215 216
            try {
216
            	// try some common ISO 8601 formats
217
        		SimpleDateFormat sdf = null;
218
        		if (casevalue.length() == 10) {
219
        			sdf = new SimpleDateFormat("yyyy-MM-dd");
220
        		} else if (casevalue.length() == 19) {
221
        			sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
222
        		}
223
        		Date dataDateValue = sdf.parse(casevalue);
224
                nodedataterm = "nodedatadate";
225
        		searchexpr = 
226
        			nodedataterm + " " + oper + " '"
227
        			+ casevalue + "' ";
228
        	} catch (ParseException pe) {
229
	            try {
230
	                // it is number; numeric comparison
231
	                // but we need to make sure there is no string in node data
232
	                searchexpr = nodedataterm + " " + oper + " "
233
	                        + new Double(casevalue) + " ";
234
	            } catch (NumberFormatException nfe) {
235
	                // these are characters; character comparison
236
	                searchexpr = nodedataterm + " " + oper + " '" + casevalue
237
	                        + "' ";
238
	            }
239
        	}
240
            
217
                // it is number; numeric comparison
218
                searchexpr = nodedataterm + " " + oper + " " + new Double(casevalue) + " ";
219
            } catch (NumberFormatException nfe) {
220
            	// is it a date?
221
            	try {
222
                	// try ISO 8601 formats
223
                	Calendar dataDateValue = DatatypeConverter.parseDateTime(casevalue);
224
                	String lexicalString = DatatypeConverter.printDateTime(dataDateValue);
225
                    nodedataterm = "nodedatadate";
226
            		searchexpr = 
227
            			nodedataterm + " " + oper + " '" + lexicalString + "' ";
228
            	} catch (Exception pe) {
229
            		// these are characters; character comparison
230
                    searchexpr = nodedataterm + " " + oper + " '" + casevalue + "' ";
231
            	}
232
            }
241 233
        }
242 234

  
243 235

  
src/edu/ucsb/nceas/metacat/admin/upgrade/Upgrade1_10_0.java
33 33
import java.sql.Statement;
34 34
import java.sql.DriverManager;
35 35
import java.sql.Timestamp;
36
import java.text.ParseException;
37
import java.text.SimpleDateFormat;
38
import java.util.Date;
36
import java.util.Calendar;
39 37

  
38
import javax.xml.bind.DatatypeConverter;
39

  
40 40
import edu.ucsb.nceas.metacat.admin.AdminException;
41 41
import edu.ucsb.nceas.metacat.properties.PropertyService;
42 42
import edu.ucsb.nceas.utilities.SortedProperties;
......
84 84
		
85 85
		            try {
86 86
		                if (!nodedata.trim().equals("")) {
87
		                	boolean skip = false;
88
		                	SimpleDateFormat sdf = null;
89
		            		if (nodedata.length() == 10) {
90
		            			sdf = new SimpleDateFormat("yyyy-MM-dd");
91
		            		} else if (nodedata.length() == 19) {
92
		            			sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
93
		            		} else {
94
		            			skip = true;
95
		            		}
96
		            		if (!skip) {
97
			            		Date dataDateValue = sdf.parse(nodedata);
98
			                    Timestamp dataTimestamp = new Timestamp(dataDateValue.getTime());
99
			
100
			                    pstmt = sqlca.prepareStatement(
101
			                        "update " + tableName +
102
			                        " set nodedatadate = ?" +
103
			                        " where nodeid = " + nodeid);
104
			                    pstmt.setTimestamp(1, dataTimestamp);
105
			                    pstmt.execute();
106
			                    pstmt.close();
107
			
108
			                    count++;
109
			                    if (count%5 == 0) {
110
			                        System.out.println(count + "...");
111
			                    }
112
		            		}
87
		                	
88
		                	System.out.println(nodedata);
89
		                	
90
		            		Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata);
91
		                    Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis());
92
		
93
		                    pstmt = sqlca.prepareStatement(
94
		                        "update " + tableName +
95
		                        " set nodedatadate = ?" +
96
		                        " where nodeid = " + nodeid);
97
		                    pstmt.setTimestamp(1, dataTimestamp);
98
		                    pstmt.execute();
99
		                    pstmt.close();
100
		
101
		                    count++;
102
		                    if (count%5 == 0) {
103
		                        System.out.println(count + "...");
104
		                    }
105
		            		
113 106
		                }
114
		            } catch (ParseException pe) {
115
		            	// do nothing, was not a date
107
		            } catch (Exception e) {
108
		            	// do nothing, was not a valid date
109
		            	e.printStackTrace();
116 110
		            } 
117 111
		        }
118 112
		        System.out.println("Table: " + tableName + " complete. " + count + " values converted.");
......
141 135
			// now run it
142 136
            Upgrade1_10_0 upgrader = new Upgrade1_10_0();
143 137
            upgrader.upgrade();
138
            
139
            Calendar date = DatatypeConverter.parseDate("2011-03-17");
140
            System.out.println("date:" + DatatypeConverter.printDate(date));
141
            
144 142
        } catch (Exception ex) {
145 143
            System.out.println("Exception:" + ex.getMessage());
146 144
            ex.printStackTrace();

Also available in: Unified diff