qadevOOo/runner/convwatch/DB.java

   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 }