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