1 package convwatch; 2 3 import java.sql.Connection; 4 import java.util.StringTokenizer; 5 import java.util.ArrayList; 6 import helper.OSHelper; 7 8 // import convwatch.DBHelper; 9 10 public class DB extends DBHelper 11 { 12 private static DB m_aDB = null; 13 14 // private ctor 15 private DB() 16 { 17 } 18 19 private static synchronized DB getDB() 20 { 21 if (m_aDB == null) 22 { 23 m_aDB = new DB(); 24 } 25 return m_aDB; 26 } 27 28 private String m_sSourceVersion; 29 private String m_sDestinationVersion; 30 private String m_sDocumentPool; 31 private String m_sEnvironment; 32 private String m_sDocID; 33 private String m_sDBDistinct; 34 35 public static void init(String _sDBInfoString) 36 { 37 if (_sDBInfoString == null) return; 38 getDB().fillVariables(_sDBInfoString); 39 getDB().updatestate_status("source started"); 40 } 41 42 public static void test() 43 { 44 getDB().sql_test(); 45 } 46 47 public static void source_start() 48 { 49 getDB().updatestate_status("source started"); 50 } 51 52 public static void source_finished() 53 { 54 getDB().updatestate_status( "source finished"); 55 } 56 57 public static void source_failed(String _sMessage) 58 { 59 getDB().updatestate_status("source failed"); 60 getDB().updateinfo_status(_sMessage); 61 } 62 63 public static void destination_start() 64 { 65 getDB().updatestate_status("destination started"); 66 } 67 68 public static void destination_finished() 69 { 70 getDB().updatestate_status("PASSED-OK"); 71 } 72 73 public static void destination_failed(String _sStatus, String _sMessage) 74 { 75 getDB().updatestate_status(_sStatus); 76 getDB().updateinfo_status(_sMessage); 77 } 78 public static void writeNumberOfPages(int _nPages) 79 { 80 getDB().updatepagecount_documents(_nPages); 81 } 82 public static void writeErrorFile(String _sErrorFile) 83 { 84 getDB().updateerrorfile_status(_sErrorFile); 85 } 86 public static void writeHTMLFile(String _sHTMLFile) 87 { 88 getDB().updatehtmlfile_status(_sHTMLFile); 89 } 90 91 public static void writeToDB(String _sFilename, 92 String _sBasename, 93 String _sFileFormat, 94 String _sBuildID, 95 String _sSourceType, 96 int _nResolution ) 97 { 98 GlobalLogWriter.get().println("DB: Filename:" + _sFilename); 99 GlobalLogWriter.get().println("DB: Basename:" + _sBasename); 100 GlobalLogWriter.get().println("DB: FileFormat:" + _sFileFormat); 101 GlobalLogWriter.get().println("DB: BuildID:" + _sBuildID); 102 GlobalLogWriter.get().println("DB: SourceType:" + _sSourceType); 103 GlobalLogWriter.get().println("DB: Resolution:" + _nResolution); 104 } 105 106 private String getEnvironment() 107 { 108 if (OSHelper.isWindows()) 109 { 110 return "wntmsci"; 111 } 112 else if ( OSHelper.isSolarisIntel()) 113 { 114 return "unxsoli"; 115 } 116 else if ( OSHelper.isSolarisSparc()) 117 { 118 return "unxsols"; 119 } 120 else if ( OSHelper.isLinuxIntel()) 121 { 122 return "unxlngi"; 123 } 124 else 125 { 126 GlobalLogWriter.get().println("DB: Unknown environment."); 127 GlobalLogWriter.get().println("DB: os.name := " + System.getProperty("os.name").toLowerCase()); 128 GlobalLogWriter.get().println("DB: os.arch := " + System.getProperty("os.arch")); 129 return ""; 130 } 131 } 132 133 // fill some db access important variables with values given out of a simple string 134 // DOC_COMPARATOR_DB_INFO_STRING=p:m220,c:m224,d:demo_lla,src:m220,dest:m224,doc:demo_lla,id:294,distinct:81 135 136 private void fillVariables(String _sInfo) 137 { 138 fillDBConnection(_sInfo); 139 m_sEnvironment = getEnvironment(); 140 141 StringTokenizer aTokenizer = new StringTokenizer(_sInfo,",",false); 142 while (aTokenizer.hasMoreTokens()) 143 { 144 String sPart = aTokenizer.nextToken(); 145 if (sPart.startsWith("p:")) 146 { 147 m_sSourceVersion = sPart.substring(2); 148 GlobalLogWriter.get().println("DB: source version: " + m_sSourceVersion); 149 } 150 else if (sPart.startsWith("src:")) 151 { 152 m_sSourceVersion = sPart.substring(4); 153 GlobalLogWriter.get().println("DB: source version: " + m_sSourceVersion); 154 } 155 else if (sPart.startsWith("c:")) 156 { 157 m_sDestinationVersion = sPart.substring(2); 158 GlobalLogWriter.get().println("DB: destination version: " + m_sDestinationVersion); 159 } 160 else if (sPart.startsWith("dest:")) 161 { 162 m_sDestinationVersion = sPart.substring(5); 163 GlobalLogWriter.get().println("DB: destination version: " + m_sDestinationVersion); 164 } 165 else if (sPart.startsWith("d:")) 166 { 167 m_sDocumentPool = sPart.substring(2); 168 GlobalLogWriter.get().println("DB: documentpool version: " + m_sDocumentPool); 169 } 170 else if (sPart.startsWith("doc:")) 171 { 172 m_sDocumentPool = sPart.substring(4); 173 GlobalLogWriter.get().println("DB: documentpool version: " + m_sDocumentPool); 174 } 175 else if (sPart.startsWith("id:")) 176 { 177 m_sDocID = sPart.substring(3); 178 GlobalLogWriter.get().println("DB: docid: " + m_sDocID); 179 } 180 else if (sPart.startsWith("distinct:")) 181 { 182 m_sDBDistinct = sPart.substring(9); 183 GlobalLogWriter.get().println("DB: distinct: " + m_sDBDistinct); 184 } 185 else 186 { 187 } 188 } 189 } 190 191 // public static void insertinto_file(String _sFilename, 192 // String _sBasename, 193 // String _sFileFormat, 194 // String _sBuildID, 195 // String _sSourceType, 196 // int _nResolution ) 197 // { 198 // Connection aCon = new ShareConnection().getConnection(); 199 // 200 // String sFilename = _sFilename.replace('\\', '/'); 201 // 202 // String sDeleteOld = "DELETE FROM file WHERE filename = " + Quote(sFilename); 203 // ExecSQL(aCon, sDeleteOld); 204 // 205 // String sValueLine = "type, filename, basename, fileformat, buildid, resolution, date"; 206 // StringBuffer aDataLine = new StringBuffer(); 207 // aDataLine.append( Quote(_sSourceType) ) . append( sComma ) . 208 // append( Quote( sFilename) ) . append( sComma ) . 209 // append( Quote( _sBasename) ) . append( sComma ) . 210 // append( Quote( _sFileFormat) ) . append( sComma ) . 211 // append( Quote( _sBuildID) ) . append( sComma ) . 212 // append( _nResolution) . append( sComma ) . 213 // append( Quote( today() ) ); 214 // 215 // SQLinsertValues(aCon, "file", sValueLine, aDataLine.toString()); 216 // } 217 218 // public static void updatestate_currentdocs(String _sFilename, 219 // String _sState) 220 // { 221 // Connection aCon = new ShareConnection().getConnection(); 222 // 223 // String sFilename = _sFilename.replace('\\', '/'); 224 // 225 // // String sDeleteOld = "DELETE FROM file WHERE filename = " + Quote(sFilename); 226 // // ExecSQL(aCon, sDeleteOld); 227 // 228 // String sSet = "state=" + Quote(_sState); 229 // String sWhere = getWhereClause() + sAND + "name=" + Quote(sFilename); 230 // SQLupdateValue( aCon, "currentdocs", sSet, sWhere ); 231 // } 232 233 private void sql_test() 234 { 235 String sUUID = getDBDistinct(); 236 System.out.println("UUID: " + sUUID); 237 } 238 239 public ArrayList QuerySQL(Connection _aCon, String _sSQL) 240 { 241 java.sql.Statement oStmt = null; 242 Connection oCon = null; 243 ArrayList aResultList = new ArrayList(); 244 try 245 { 246 oStmt = _aCon.createStatement(); 247 248 java.sql.ResultSet aResultSet = oStmt.executeQuery(_sSQL); 249 java.sql.ResultSetMetaData aResultSetMetaData = aResultSet.getMetaData(); 250 251 int nColumnCount = aResultSetMetaData.getColumnCount(); // java sql starts with '1' 252 // String[] aColumnName = new String[nColumnCount]; 253 // for(int i=1;i<nColumnCount;i++) 254 // { 255 // String aColumnName[i - 1] = aResultSetMetaData.getColumnName(i); 256 // } 257 258 while( aResultSet.next() ) 259 { 260 StringBuffer aResult = new StringBuffer(); 261 try 262 { 263 Object aObj = null; 264 265 266 aResult.append("sqlresult: "); 267 for (int i=1;i<=nColumnCount;i++) 268 { 269 String sColumnName = aResultSetMetaData.getColumnName(i); 270 aResult.append(sColumnName).append("="); 271 String sValue; 272 int nSQLType = aResultSetMetaData.getColumnType(i); 273 switch(nSQLType) 274 { 275 case java.sql.Types.VARCHAR: 276 sValue = "'" + aResultSet.getString(i) + "'"; 277 break; 278 case java.sql.Types.INTEGER: 279 { 280 int nValue = aResultSet.getInt(i); 281 sValue = String.valueOf(nValue); 282 break; 283 } 284 285 default: 286 sValue = "UNSUPPORTED TYPE"; 287 } 288 aResult.append(sValue).append(", "); 289 // String sName = aObj.getClass().getName(); 290 // System.out.println("sqlresult: Class name: " + sName); 291 } 292 String sResult = aResult.toString(); 293 aResultList.add(sResult); 294 // System.out.println(sResult); 295 } 296 catch (java.sql.SQLException e) 297 { 298 } 299 } 300 } 301 catch (java.sql.SQLException e) 302 { 303 String sError = e.getMessage(); 304 GlobalLogWriter.get().println("DB: Original SQL error: " + sError); 305 // throw new ValueNotFoundException("Cant execute SQL: " + _sSQL); 306 } 307 return aResultList; 308 } 309 310 private void updatestate_status(String _sStatus) 311 { 312 Connection aCon = new ShareConnection().getConnection(); 313 314 // String sInfo = _sInfo.replace('\\', '/'); 315 316 // String sDeleteOld = "DELETE FROM file WHERE filename = " + Quote(sFilename); 317 // ExecSQL(aCon, sDeleteOld); 318 319 String sSet = "state=" + Quote(_sStatus); 320 String sWhere = getWhereClause(); 321 if (sWhere.length() > 0) 322 { 323 SQLupdateValue( aCon, "status", sSet, sWhere ); 324 } 325 } 326 private void updateinfo_status(String _sInfo) 327 { 328 Connection aCon = new ShareConnection().getConnection(); 329 330 // String sInfo = _sInfo.replace('\\', '/'); 331 332 // String sDeleteOld = "DELETE FROM file WHERE filename = " + Quote(sFilename); 333 // ExecSQL(aCon, sDeleteOld); 334 335 String sSet = "info=" + Quote(_sInfo); 336 String sWhere = getWhereClause(); 337 SQLupdateValue( aCon, "status", sSet, sWhere ); 338 } 339 private void updateerrorfile_status(String _sErrorFile) 340 { 341 Connection aCon = new ShareConnection().getConnection(); 342 343 String sErrorFile = _sErrorFile.replace('\\', '/'); 344 345 String sSet = "errorfile=" + Quote(sErrorFile); 346 String sWhere = getWhereClause(); 347 SQLupdateValue( aCon, "status", sSet, sWhere ); 348 } 349 private void updatehtmlfile_status(String _sHtmlFile) 350 { 351 Connection aCon = new ShareConnection().getConnection(); 352 353 String sHtmlFile = _sHtmlFile.replace('\\', '/'); 354 355 String sSet = "htmlfile=" + Quote(sHtmlFile); 356 String sWhere = getWhereClause(); 357 SQLupdateValue( aCon, "status", sSet, sWhere ); 358 } 359 private void updatepagecount_documents(int _nPageCount) 360 { 361 Connection aCon = new ShareConnection().getConnection(); 362 363 String sSet = "pagecount=" + _nPageCount; 364 String sWhere = getWhereClause(); 365 SQLupdateValue( aCon, "documents", sSet, sWhere ); 366 367 } 368 369 370 private String getWhereClause() 371 { 372 StringBuffer aWhereClause = new StringBuffer(); 373 // WHERE environment='' and referenceversion='' and currentversion='' and documentpool='' 374 // aWhere.append( "environment" ). append(sEqual) . append(Quote(m_sEnvironment)) . 375 // append(sAND) . 376 // append( "referenceversion" ). append(sEqual) . append(Quote(m_sSourceVersion)) . 377 // append(sAND) . 378 // append( "currentversion" ). append(sEqual) . append(Quote(m_sDestinationVersion)) . 379 // append(sAND) . 380 // append( "documentpool" ). append(sEqual) . append(Quote(m_sDocumentPool)); 381 boolean bAND = false; 382 if (m_sDocID != null) 383 { 384 aWhereClause.append( "docid" ). append(sEqual) . append(m_sDocID); 385 bAND = true; 386 } 387 if (bAND) 388 { 389 aWhereClause.append(sAND); 390 } 391 if (m_sDBDistinct != null) 392 { 393 aWhereClause.append( "dbdistinct2" ). append(sEqual) . append(Quote(m_sDBDistinct)); 394 } 395 return aWhereClause.toString(); 396 } 397 398 private String getDBDistinct() 399 { 400 Connection aCon = new ShareConnection().getConnection(); 401 402 String sSQL = "SELECT uuid()"; 403 ArrayList aResultList = QuerySQL(aCon, sSQL); 404 405 for (int i=0;i<aResultList.size();i++) 406 { 407 String sResult = (String)aResultList.get(i); 408 409 StringTokenizer aTokenizer = new StringTokenizer(sResult,",",false); 410 while (aTokenizer.hasMoreTokens()) 411 { 412 String sToken = aTokenizer.nextToken(); 413 // System.out.println("PART: " + sToken); 414 int nIndex = sToken.indexOf("uuid()="); 415 // System.out.println("Index " + nIndex); 416 int nIndexTuettel = sToken.indexOf("'", nIndex); 417 // System.out.println("IndexTuettel " + nIndexTuettel); 418 int nIndexTuettel2 = sToken.lastIndexOf("'"); 419 // System.out.println("IndexTuettel2 " + nIndexTuettel2); 420 String sUuid = sToken.substring(nIndexTuettel + 1, nIndexTuettel2); 421 // if (sPart.startsWith("p:")) 422 // { 423 // m_sSourceVersion = sPart.substring(2); 424 // GlobalLogWriter.get().println("DB: source version: " + m_sSourceVersion); 425 // } 426 return sUuid; 427 } 428 // System.out.println(sResult); 429 } 430 431 return "0"; 432 } 433 434 public static void insertinto_documentcompare(String _sSourceVersion, String _sSourceName, String _sSourceCreatorType, 435 String _sDestinationVersion, String _sDestinationName, String _sDestinationCreatorType, 436 String _sDocumentPoolDir, String _sDocumentPoolName, String _sMailAddress, 437 String _sSpecial, String _sParentDistinct) 438 { 439 getDB().insertinto_documentcompare_impl( _sSourceVersion, _sSourceName, _sSourceCreatorType, 440 _sDestinationVersion, _sDestinationName, _sDestinationCreatorType, 441 _sDocumentPoolDir, _sDocumentPoolName, _sMailAddress, 442 _sSpecial, _sParentDistinct); 443 } 444 445 private void insertinto_documentcompare_impl(String _sSourceVersion, String _sSourceName, String _sSourceCreatorType, 446 String _sDestinationVersion, String _sDestinationName, String _sDestinationCreatorType, 447 String _sDocumentPoolDir, String _sDocumentPoolName, String _sMailAddress, 448 String _sSpecial, String _sParentDistinct) 449 { 450 // $sSQLInsert = "INSERT INTO documentcompare 451 if (_sParentDistinct == null) 452 { 453 _sParentDistinct = ""; 454 } 455 456 Connection aCon = new ShareConnection().getConnection(); 457 458 String sValueLine="dbdistinct2, environment, sourceversion, sourcename, sourcecreatortype, destinationversion, destinationname, destinationcreatortype, documentpoolpath, documentpool, mailfeedback, state, special, parentdistinct, startdate"; 459 String sDocumentPoolDir = _sDocumentPoolDir.replace('\\', '/'); 460 StringBuffer aDataLine = new StringBuffer(); 461 aDataLine.append( Quote(getDBDistinct()) ) . append( sComma ) . 462 append( Quote( getEnvironment()) ) . append( sComma ) . 463 append( Quote( _sSourceVersion) ) . append( sComma ) . 464 append( Quote( _sSourceName) ) . append( sComma ) . 465 append( Quote( _sSourceCreatorType ) ) . append( sComma ) . 466 append( Quote( _sDestinationVersion) ) . append( sComma ) . 467 append( Quote( _sDestinationName) ) . append( sComma ) . 468 append( Quote( _sDestinationCreatorType ) ) . append( sComma ) . 469 append( Quote( sDocumentPoolDir) ) . append( sComma ) . 470 append( Quote( _sDocumentPoolName) ) . append( sComma ) . 471 append( Quote( _sMailAddress) ) . append( sComma ) . 472 append( Quote( "new" )) . append ( sComma ) . 473 append( Quote( _sSpecial ) ) . append( sComma ) . 474 append( Quote( _sParentDistinct ) ) . append( sComma ) . 475 append( Quote( today() )); 476 477 SQLinsertValues(aCon, "documentcompare", sValueLine, aDataLine.toString()); 478 } 479 480 // public static void filesRemove(String _sDBInfoString) 481 // { 482 // if (_sDBInfoString == null) return; 483 // fillVariables(_sDBInfoString); 484 // 485 // Connection aCon = new ShareConnection().getConnection(); 486 // 487 // String sDeleteSQL = "DELETE FROM currentdocs WHERE " + getWhereClause(); 488 // 489 // ExecSQL(aCon, sDeleteSQL); 490 // } 491 // 492 // public static void fileInsert(String _sDBInfoString, 493 // String _sFilename, 494 // String _sRemovePath) 495 // { 496 // if (_sDBInfoString == null) return; 497 // fillVariables(_sDBInfoString); 498 // 499 // String sFilename = _sFilename.replace('\\', '/'); 500 // 501 // Connection aCon = new ShareConnection().getConnection(); 502 // 503 // String sValueLine = "environment, referenceversion, currentversion, documentpool, name, state"; 504 // StringBuffer aDataLine = new StringBuffer(); 505 // aDataLine.append( Quote(m_sEnvironment) ) . append( sComma ) . 506 // append( Quote( m_sSourceVersion) ) . append( sComma ) . 507 // append( Quote( m_sDestinationVersion) ) . append( sComma ) . 508 // append( Quote( m_sDocumentPool) ) . append( sComma ) . 509 // append( Quote( sFilename) ) . append( sComma ) . 510 // append( Quote( "undone")); 511 // 512 // SQLinsertValues(aCon, "currentdocs", sValueLine, aDataLine.toString()); 513 // } 514 515 516 // public static void insertinto_file(String _sFilename, String _sFileFormat, String _sBuildID) 517 // { 518 // Connection aCon = new ShareConnection().getConnection(); 519 // 520 // String sValueLine = "type, filename, fileformat, buildid, date"; 521 // StringBuffer aDataLine = new StringBuffer(); 522 // aDataLine.append( "1" ) . append( sComma ) . 523 // append( Quote( _sFilename) ) . append( sComma ) . 524 // append( Quote( _sFileFormat) ) . append( sComma ) . 525 // append( Quote( _sBuildID) ) . append( sComma ) . 526 // append( Quote( today() ) ); 527 // 528 // SQLinsertValues(aCon, "file", sValueLine, aDataLine.toString()); 529 // } 530 531 // public static void main( String[] args ) 532 // { 533 // 534 // String _sFilename = ""; 535 // String _sFileFormat = ""; 536 // String _sBuildID = ""; 537 // 538 // // insertinto_file("c:\temp\test.txt", "test", "txt", "nix", "", 0); 539 // fillVariables("p:m128,c:m134,d:demo"); 540 // } 541 } 542