xref: /aoo41x/main/qadevOOo/runner/util/DBTools.java (revision e6b649b5)
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 
23 
24 package util;
25 
26 import com.sun.star.uno.Exception;
27 import java.io.PrintWriter ;
28 
29 // access the implementations via names
30 import com.sun.star.uno.XInterface;
31 import com.sun.star.lang.XMultiServiceFactory;
32 import com.sun.star.uno.UnoRuntime;
33 
34 import com.sun.star.beans.PropertyValue;
35 import com.sun.star.beans.XPropertySet;
36 import com.sun.star.sdbc.XConnection ;
37 import com.sun.star.sdbc.XResultSet ;
38 import com.sun.star.sdbc.XResultSetUpdate ;
39 import com.sun.star.sdbc.XStatement ;
40 import com.sun.star.sdbc.XRowUpdate ;
41 import com.sun.star.util.Date ;
42 import com.sun.star.uno.XNamingService ;
43 import com.sun.star.task.XInteractionHandler ;
44 import com.sun.star.sdb.XCompletedConnection ;
45 import com.sun.star.container.XEnumeration ;
46 import com.sun.star.container.XEnumerationAccess ;
47 import com.sun.star.io.XInputStream ;
48 import com.sun.star.io.XTextInputStream ;
49 import com.sun.star.io.XDataInputStream ;
50 import com.sun.star.container.XNameAccess ;
51 import com.sun.star.frame.XStorable;
52 import com.sun.star.sdb.XDocumentDataSource;
53 import com.sun.star.sdbc.XCloseable ;
54 import java.sql.Statement;
55 import java.sql.Connection;
56 import java.sql.DriverManager;
57 
58 /**
59 * Provides useful methods for working with SOffice databases.
60 * Database creation, data transfering, outputting infromation.
61 */
62 public class DBTools {
63 
64     private XMultiServiceFactory xMSF = null ;
65     private XNamingService dbContext = null ;
66     private PrintWriter m_log = null;
67 
68     //JDBC driver
69     public final static String TST_JDBC_DRIVER = "org.gjt.mm.mysql.Driver";
70 
71     // constants for TestDB table column indexes
72     public final static int TST_STRING = 1 ;
73     public final static int TST_INT = 2 ;
74     public final static int TST_DOUBLE = 5 ;
75     public final static int TST_DATE = 6 ;
76     public final static int TST_BOOLEAN = 10 ;
77     public final static int TST_CHARACTER_STREAM = 11 ;
78     public final static int TST_BINARY_STREAM = 12 ;
79 
80     // constants for TestDB columns names
81     public final static String TST_STRING_F = "_TEXT" ;
82     public final static String TST_INT_F = "_INT" ;
83     public final static String TST_DOUBLE_F = "_DOUBLE" ;
84     public final static String TST_DATE_F = "_DATE" ;
85     public final static String TST_BOOLEAN_F = "_BOOL" ;
86     public final static String TST_CHARACTER_STREAM_F = "_MEMO1" ;
87     public final static String TST_BINARY_STREAM_F = "_MEMO2" ;
88 
89     /**
90     * Values for filling test table.
91     */
92     public final static Object[][] TST_TABLE_VALUES = new Object[][] {
93         {"String1", new Integer(1), null, null, new Double(1.1),
94          new Date((short) 1,(short) 1, (short) 2001), null, null, null,
95          Boolean.TRUE, null, null},
96         {"String2", new Integer(2), null, null, new Double(1.2),
97          new Date((short) 2, (short) 1,(short)  2001), null, null, null,
98          Boolean.FALSE, null, null},
99         {null, null, null, null, null,
100          null, null, null, null,
101          null, null, null}
102     } ;
103 
104     /**
105     * Array of lengths of streams for each row in of the
106     * <code>TST_TABLE_VALUES</code> constants.
107     */
108     public final static int[] TST_STREAM_LENGTHS = {0, 0, 0} ;
109 
110     /**
111     * It's just a structure with some useful methods for representing
112     * <code>com.sun.star.sdb.DataSource</code> service. All this
113     * service's properties are stored in appropriate class fields.
114     * Class also allows to construct its instances using service
115     * information, and create new service instance upon class
116     * fields.
117     * @see com.sun.star.sdb.DataSource
118     */
119     public class DataSourceInfo {
120         /**
121         * Representation of <code>'Name'</code> property.
122         */
123         public String Name = null ;
124         /**
125         * Representation of <code>'URL'</code> property.
126         */
127         public String URL = null ;
128         /**
129         * Representation of <code>'Info'</code> property.
130         */
131         public PropertyValue[] Info = null ;
132         /**
133         * Representation of <code>'User'</code> property.
134         */
135         public String User = null ;
136         /**
137         * Representation of <code>'Password'</code> property.
138         */
139         public String Password = null ;
140         /**
141         * Representation of <code>'IsPasswordRequired'</code> property.
142         */
143         public Boolean IsPasswordRequired = null ;
144         /**
145         * Representation of <code>'SuppressVersionColumns'</code> property.
146         */
147         public Boolean SuppressVersionColumns = null ;
148         /**
149         * Representation of <code>'IsReadOnly'</code> property.
150         */
151         public Boolean IsReadOnly = null ;
152         /**
153         * Representation of <code>'TableFilter'</code> property.
154         */
155         public String[] TableFilter = null ;
156         /**
157         * Representation of <code>'TableTypeFilter'</code> property.
158         */
159         public String[] TableTypeFilter = null ;
160 
161         /**
162         * Creates an empty instance.
163         */
DataSourceInfo()164         public DataSourceInfo()
165         {
166         }
167 
168         /**
169         * Creates an instance laying upon specified DataSource.
170         * @param dataSource All source properties are copied into
171         * class fields.
172         */
DataSourceInfo(Object dataSource)173         public DataSourceInfo(Object dataSource) {
174             XPropertySet xProps = (XPropertySet)
175                 UnoRuntime.queryInterface(XPropertySet.class, dataSource) ;
176 
177             try {
178                 Name = (String)xProps.getPropertyValue("Name") ;
179                 URL = (String)xProps.getPropertyValue("URL") ;
180                 Info = (PropertyValue[])xProps.getPropertyValue("Info") ;
181                 User = (String)xProps.getPropertyValue("User") ;
182                 Password = (String)xProps.getPropertyValue("Password") ;
183                 IsPasswordRequired = (Boolean)xProps.getPropertyValue("IsPasswordRequired") ;
184                 SuppressVersionColumns = (Boolean)
185                     xProps.getPropertyValue("SuppressVersionColumns") ;
186                 IsReadOnly = (Boolean)xProps.getPropertyValue("IsReadOnly") ;
187                 TableFilter = (String[])xProps.getPropertyValue("TableFilter") ;
188                 TableTypeFilter = (String[])xProps.getPropertyValue("TableTypeFilter") ;
189             } catch (com.sun.star.beans.UnknownPropertyException e) {
190                 System.err.println("util.DBTools.DataSourceInfo: Error retrieving property") ;
191                 e.printStackTrace(System.err) ;
192             } catch (com.sun.star.lang.WrappedTargetException e) {
193                 System.err.println("util.DBTools.DataSourceInfo: Error retrieving property") ;
194                 e.printStackTrace(System.err) ;
195             }
196         }
197 
198         /**
199         * Prints datasource info.
200         * @param out Stream to which information is printed.
201         */
printInfo(PrintWriter out)202         public void printInfo(PrintWriter out) {
203             out.println("Name = '" + Name + "'") ;
204             out.println("  URL = '" + URL + "'") ;
205             out.print("  Info = ") ;
206             if (Info == null) out.println("null") ;
207             else {
208                 out.print("{") ;
209                 for (int i = 0; i < Info.length; i++) {
210                     out.print(Info[i].Name + " = '" + Info[i].Value + "'") ;
211                     if (i + 1 < Info.length) out.print("; ") ;
212                 }
213                 out.println("}") ;
214             }
215             out.println("  User = '" + User + "'") ;
216             out.println("  Password = '" + Password + "'") ;
217             out.println("  IsPasswordRequired = '" + IsPasswordRequired + "'") ;
218             out.println("  SuppressVersionColumns = '" + SuppressVersionColumns + "'") ;
219             out.println("  IsReadOnly = '" + IsReadOnly + "'") ;
220             out.print("  TableFilter = ") ;
221             if (TableFilter == null) out.println("null") ;
222             else {
223                 out.print("{") ;
224                 for (int i = 0; i < TableFilter.length; i++) {
225                     out.print("'" + TableFilter[i] + "'") ;
226                     if (i+1 < TableFilter.length) out.print("; ");
227                 }
228                 out.println("}") ;
229             }
230             out.print("  TableTypeFilter = ") ;
231             if (TableTypeFilter == null) out.println("null") ;
232             else {
233                 out.print("{") ;
234                 for (int i = 0; i < TableTypeFilter.length; i++) {
235                     out.print("'" + TableTypeFilter[i] + "'") ;
236                     if (i+1 < TableTypeFilter.length) out.print("; ");
237                 }
238                 out.println("}") ;
239             }
240         }
241 
242         /**
243         * Creates new <code>com.sun.star.sdb.DataSource</code> service
244         * instance and copies all fields (which are not null) to
245         * appropriate service properties.
246         * @return <code>com.sun.star.sdb.DataSource</code> service.
247         */
getDataSourceService()248         public Object getDataSourceService() throws Exception
249         {
250             Object src = src = xMSF.createInstance("com.sun.star.sdb.DataSource") ;
251 
252             XPropertySet props = (XPropertySet) UnoRuntime.queryInterface
253                 (XPropertySet.class, src) ;
254 
255             if (Name != null) props.setPropertyValue("Name", Name) ;
256             if (URL != null) props.setPropertyValue("URL", URL) ;
257             if (Info != null) props.setPropertyValue("Info", Info) ;
258             if (User != null) props.setPropertyValue("User", User) ;
259             if (Password != null) props.setPropertyValue("Password", Password) ;
260             if (IsPasswordRequired != null) props.setPropertyValue("IsPasswordRequired", IsPasswordRequired) ;
261             if (SuppressVersionColumns != null) props.setPropertyValue("SuppressVersionColumns", SuppressVersionColumns) ;
262             if (IsReadOnly != null) props.setPropertyValue("IsReadOnly", IsReadOnly) ;
263             if (TableFilter != null) props.setPropertyValue("TableFilter", TableFilter) ;
264             if (TableTypeFilter != null) props.setPropertyValue("TableTypeFilter", TableTypeFilter) ;
265 
266             return src ;
267         }
268     }
269 
270     /**
271     * Creates class instance.
272     * @param xMSF <code>XMultiServiceFactory</code>.
273     */
DBTools(XMultiServiceFactory xMSF, PrintWriter _logger )274     public DBTools(XMultiServiceFactory xMSF, PrintWriter _logger )
275     {
276         this.xMSF = xMSF ;
277         this.m_log = _logger;
278 
279         try {
280             Object cont = xMSF.createInstance("com.sun.star.sdb.DatabaseContext") ;
281 
282             dbContext = (XNamingService) UnoRuntime.queryInterface
283                 (XNamingService.class, cont) ;
284 
285         } catch (com.sun.star.uno.Exception e) {}
286     }
287 
288     /**
289     * Returns new instance of <code>DataSourceInfo</code> class.
290     */
newDataSourceInfo()291     public DataSourceInfo newDataSourceInfo() { return new DataSourceInfo() ;}
292 
293     /**
294     * Returns new instance of <code>DataSourceInfo</code> class.
295     */
newDataSourceInfo(Object dataSource)296     public DataSourceInfo newDataSourceInfo(Object dataSource) {
297         return new DataSourceInfo(dataSource);
298     }
299 
300     /**
301     * Registers the datasource on the specified name in
302     * <code>DatabaseContext</code> service.
303     * @param name Name which dataSource will have in global context.
304     * @param dataSource <code>DataSource</code> object which is to
305     * be registered.
306     */
registerDB(String name, Object dataSource)307     public void registerDB(String name, Object dataSource)
308         throws com.sun.star.uno.Exception {
309 
310         dbContext.registerObject(name, dataSource) ;
311     }
312 
313 
314     /**
315     * First tries to revoke the datasource with the specified
316     * name and then registers a new one.
317     * @param name Name which dataSource will have in global context.
318     * @param dataSource <code>DataSource</code> object which is to
319     * be registered.
320     */
reRegisterDB(String name, Object dataSource)321     public void reRegisterDB(String name, Object dataSource)
322         throws com.sun.star.uno.Exception {
323 
324         try {
325             revokeDB(name) ;
326         } catch (com.sun.star.uno.Exception e) {}
327 
328         XDocumentDataSource xDDS = (XDocumentDataSource)
329         UnoRuntime.queryInterface(XDocumentDataSource.class, dataSource);
330         XStorable store = (XStorable) UnoRuntime.queryInterface(XStorable.class,
331                 xDDS.getDatabaseDocument());
332         String aFile = utils.getOfficeTemp(xMSF) + name + ".odb";
333         store.storeAsURL(aFile, new PropertyValue[] {  });
334 
335         registerDB(name, dataSource) ;
336     }
337 
338     /**
339     * RESERVED. Not used.
340     */
connectToTextDB(String contextName, String dbDir, String fileExtension)341     public XConnection connectToTextDB(String contextName,
342         String dbDir, String fileExtension)
343                             throws com.sun.star.uno.Exception {
344 
345         try {
346             XInterface newSource = (XInterface) xMSF.createInstance
347                 ("com.sun.star.sdb.DataSource") ;
348 
349             XPropertySet xSrcProp = (XPropertySet)
350                 UnoRuntime.queryInterface(XPropertySet.class, newSource);
351 
352             xSrcProp.setPropertyValue("URL", "sdbc:text:" + dirToUrl(dbDir));
353 
354             PropertyValue extParam = new PropertyValue() ;
355             extParam.Name = "EXT" ;
356             extParam.Value = fileExtension ;
357 
358             xSrcProp.setPropertyValue("Info", new PropertyValue[] {extParam}) ;
359 
360             dbContext.registerObject(contextName, newSource) ;
361 
362             Object handler = xMSF.createInstance("com.sun.star.sdb.InteractionHandler");
363             XInteractionHandler xHandler = (XInteractionHandler)
364                 UnoRuntime.queryInterface(XInteractionHandler.class, handler) ;
365 
366             XCompletedConnection xSrcCon = (XCompletedConnection)
367                 UnoRuntime.queryInterface(XCompletedConnection.class, newSource) ;
368 
369             XConnection con = xSrcCon.connectWithCompletion(xHandler) ;
370 
371             return con ;
372         } finally {
373             try {
374                 dbContext.revokeObject(contextName) ;
375             } catch (Exception e) {}
376         }
377     }
378 
379     /**
380     * Registers DBase database (directory with DBF files) in the
381     * global DB context, then connects to it.
382     * @param contextName Name under which DB will be registered.
383     * @param dbDir The directory with DBF tables.
384     * @return Connection to the DB.
385     */
connectToDBase(String contextName, String dbDir)386     public XConnection connectToDBase(String contextName,
387         String dbDir)
388         throws com.sun.star.uno.Exception {
389 
390         try {
391             XInterface newSource = (XInterface) xMSF.createInstance
392                 ("com.sun.star.sdb.DataSource") ;
393 
394             XPropertySet xSrcProp = (XPropertySet)
395                 UnoRuntime.queryInterface(XPropertySet.class, newSource);
396             xSrcProp.setPropertyValue("URL", "sdbc:dbase:" + dirToUrl(dbDir));
397 
398             dbContext.registerObject(contextName, newSource) ;
399 
400             XConnection con = connectToSource(newSource) ;
401 
402             return con ;
403         } catch(com.sun.star.uno.Exception e) {
404             try {
405                 dbContext.revokeObject(contextName) ;
406             } catch (Exception ex) {}
407 
408             throw e ;
409         }
410     }
411 
412     /**
413     * Performs connection to DataSource specified.
414     * @param dbSource <code>com.sun.star.sdb.DataSource</code> service
415     *     specified data source which must be already registered in the
416     *     <code>DatabaseContext</code> service.
417     * @return Connection to the data source.
418     */
connectToSource(Object dbSource)419     public XConnection connectToSource(Object dbSource)
420         throws com.sun.star.uno.Exception {
421 
422         Object handler = xMSF.createInstance("com.sun.star.sdb.InteractionHandler");
423         XInteractionHandler xHandler = (XInteractionHandler)
424             UnoRuntime.queryInterface(XInteractionHandler.class, handler) ;
425 
426         XCompletedConnection xSrcCon = (XCompletedConnection)
427             UnoRuntime.queryInterface(XCompletedConnection.class, dbSource) ;
428 
429         return xSrcCon.connectWithCompletion(xHandler) ;
430     }
431 
432     /**
433     * Registers Test data source in the <code>DatabaseContext</code> service.
434     * This source always has name <code>'APITestDatabase'</code> and it
435     * is registered in subdirectory <code>TestDB</code> of directory
436     * <code>docPath</code> which is supposed to be a directory with test
437     * documents, but can be any other (it must have subdirectory with DBF
438     * tables). If such data source doesn't exists or exists with
439     * different URL it is recreated and reregistered.
440     * @param docPath Path to database <code>TestDB</code> directory.
441     * @return <code>com.sun.star.sdb.DataSource</code> service
442     * implementation which represents TestDB.
443     */
registerTestDB(String docPath)444     public Object registerTestDB(String docPath)
445         throws com.sun.star.uno.Exception {
446 
447         String testURL = null ;
448         if (docPath.endsWith("/") || docPath.endsWith("\\"))
449             testURL = dirToUrl(docPath + "TestDB") ;
450         else
451             testURL = dirToUrl(docPath + "/" + "TestDB") ;
452         testURL = "sdbc:dbase:" + testURL ;
453 
454         String existURL = null ;
455 
456         XNameAccess na = (XNameAccess) UnoRuntime.queryInterface
457             (XNameAccess.class, dbContext) ;
458 
459         Object src = null ;
460         if (na.hasByName("APITestDatabase")) {
461             src = dbContext.getRegisteredObject("APITestDatabase") ;
462 
463             XPropertySet srcPs = (XPropertySet) UnoRuntime.queryInterface
464                 (XPropertySet.class, src) ;
465 
466             existURL = (String) srcPs.getPropertyValue("URL") ;
467         }
468 
469         if (src == null || !testURL.equals(existURL)) {
470             // test data source must be reregistered.
471             DataSourceInfo info = new DataSourceInfo() ;
472             info.URL = testURL ;
473             src = info.getDataSourceService() ;
474             reRegisterDB("APITestDatabase", src) ;
475             src = dbContext.getRegisteredObject("APITestDatabase") ;
476         }
477 
478         return src ;
479     }
480 
481     /**
482     * Connects to <code>DataSource</code> specially created for testing.
483     * This source always has name <code>'APITestDatabase'</code> and it
484     * is registered in subdirectory <code>TestDB</code> of directory
485     * <code>docPath</code> which is supposed to be a directory with test
486     * documents, but can be any other (it must have subdirectory with DBF
487     * tables). If such data source doesn't exists or exists with
488     * different URL it is recreated and reregistered. Finally connection
489     * performed.
490     * @param docPath Path to database <code>TestDB</code> directory.
491     * @return Connection to test database.
492     */
connectToTestDB(String docPath)493     public XConnection connectToTestDB(String docPath)
494         throws com.sun.star.uno.Exception {
495 
496         return connectToSource(registerTestDB(docPath)) ;
497     }
498 
499     /**
500     * Empties the table in the specified source.
501     * @param con Connection to the DataSource where appropriate
502     * table exists.
503     * @param table The name of the table where all rows will be deleted.
504     * @return Number of rows deleted.
505     */
506 
507     // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
508     // Currently doesn't work because of bugs 85509, 85510
509 
deleteAllRows(XConnection con, String table)510     public int deleteAllRows(XConnection con, String table)
511         throws com.sun.star.sdbc.SQLException {
512 
513         XStatement stat = con.createStatement() ;
514 
515         XResultSet set = stat.executeQuery("SELECT * FROM " + table) ;
516 
517         XResultSetUpdate updt = (XResultSetUpdate) UnoRuntime.queryInterface
518             (XResultSetUpdate.class, set) ;
519 
520         int count = 0 ;
521         set.last() ;
522         int rowNum = set.getRow() ;
523         set.first() ;
524 
525         for (int i = 0; i < rowNum; i++) {
526             updt.deleteRow() ;
527             set.next() ;
528             count ++ ;
529         }
530 
531         XCloseable xClose = (XCloseable) UnoRuntime.queryInterface
532             (XCloseable.class, set) ;
533         xClose.close() ;
534 
535         return count ;
536     }
537 
538     /**
539     * Inserts row into test table of the specified connection.
540     * Test table has some predefined format which includes as much
541     * field types as possible. For every column type constants
542     * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc.
543     * are declared for column index fast find.
544     * @param con Connection to data source where test table exists.
545     * @param table Test table name.
546     * @param values Values to be inserted into test table. Values of
547     * this array inserted into appropriate fields depending on their
548     * types. So <code>String</code> value of the array is inserted
549     * into the field of <code>CHARACTER</code> type, etc.
550     * @param streamLength Is optional. It is used only if in values
551     * list <code>XCharacterInputStream</code> or <code>XBinaryInputStream
552     * </code> types specified. In this case the parameter specifies
553     * the length of the stream for inserting.
554     */
addRowToTestTable(XConnection con, String table, Object[] values, int streamLength)555     public void addRowToTestTable(XConnection con, String table, Object[] values,
556         int streamLength)
557         throws com.sun.star.sdbc.SQLException {
558 
559         XStatement stat = con.createStatement() ;
560 
561         XResultSet set = stat.executeQuery("SELECT * FROM " + table) ;
562 
563         XResultSetUpdate updt = (XResultSetUpdate) UnoRuntime.queryInterface
564             (XResultSetUpdate.class, set) ;
565 
566         XRowUpdate rowUpdt = (XRowUpdate) UnoRuntime.queryInterface
567             (XRowUpdate.class, set) ;
568 
569         updt.moveToInsertRow() ;
570 
571         for (int i = 0; i < values.length; i++) {
572             if (values[i] instanceof String) {
573                 rowUpdt.updateString(TST_STRING, (String) values[i]) ;
574             } else
575             if (values[i] instanceof Integer) {
576                 rowUpdt.updateInt(TST_INT, ((Integer) values[i]).intValue()) ;
577             } else
578             if (values[i] instanceof Double) {
579                 rowUpdt.updateDouble(TST_DOUBLE, ((Double) values[i]).doubleValue()) ;
580             } else
581             if (values[i] instanceof Date) {
582                 rowUpdt.updateDate(TST_DATE, (Date) values[i]) ;
583             } else
584             if (values[i] instanceof Boolean) {
585                 rowUpdt.updateBoolean(TST_BOOLEAN, ((Boolean) values[i]).booleanValue()) ;
586             } else
587             if (values[i] instanceof XTextInputStream) {
588                 rowUpdt.updateCharacterStream(TST_CHARACTER_STREAM, (XInputStream) values[i],
589                     streamLength) ;
590             } else
591             if (values[i] instanceof XDataInputStream) {
592                 rowUpdt.updateBinaryStream(TST_BINARY_STREAM, (XInputStream) values[i],
593                     streamLength) ;
594             }
595         }
596 
597         updt.insertRow() ;
598 
599         XCloseable xClose = (XCloseable) UnoRuntime.queryInterface
600             (XCloseable.class, set) ;
601         xClose.close() ;
602     }
603 
604     /**
605     * Initializes test table specified of the connection specified.
606     * Deletes all record from table, and then inserts data from
607     * <code>TST_TABLE_VALUES</code> constant array. <p>
608     * Test table has some predefined format which includes as much
609     * field types as possible. For every column type constants
610     * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc.
611     * are declared for column index fast find.
612     * @param con Connection to data source where test table exists.
613     * @param table Test table name.
614     */
initializeTestTable(XConnection con, String table)615     public void initializeTestTable(XConnection con, String table)
616         throws com.sun.star.sdbc.SQLException {
617 
618         deleteAllRows(con, table) ;
619 
620         for (int i = 0; i < TST_TABLE_VALUES.length; i++) {
621             addRowToTestTable(con, table, TST_TABLE_VALUES[i], TST_STREAM_LENGTHS[i]) ;
622         }
623     }
624 
625     /**
626     * Prints full info about currently registered DataSource's.
627     */
printRegisteredDatabasesInfo(PrintWriter out)628     public void printRegisteredDatabasesInfo(PrintWriter out) {
629         XEnumerationAccess dbContEA = (XEnumerationAccess)
630             UnoRuntime.queryInterface(XEnumerationAccess.class, dbContext) ;
631 
632         XEnumeration xEnum = dbContEA.createEnumeration() ;
633 
634         out.println("DatabaseContext registered DataSource's :") ;
635         while (xEnum.hasMoreElements()) {
636             try {
637                 DataSourceInfo inf = new DataSourceInfo(xEnum.nextElement()) ;
638                 inf.printInfo(out) ;
639             } catch (com.sun.star.container.NoSuchElementException e) {}
640             catch (com.sun.star.lang.WrappedTargetException e) {}
641         }
642     }
643 
644     /**
645     * Convert system pathname to SOffice URL string
646     * (for example 'C:\Temp\DBDir\' -> 'file:///C|/Temp/DBDir/').
647     * (for example '\\server\Temp\DBDir\' -> 'file://server/Temp/DBDir/').
648     * Already converted string retured unchanged.
649     */
dirToUrl(String dir)650     public static String dirToUrl(String dir) {
651         String retVal = null;
652         if (dir.startsWith("file:/")) retVal = dir;
653         else {
654             retVal = dir.replace(':', '|').replace('\\', '/');
655 
656             if (dir.startsWith("\\\\")) {
657                 retVal = "file:" + retVal;
658             }
659 
660             else retVal = "file:///" + retVal ;
661         }
662         return retVal;
663     }
664 
665     /**
666     * Revokes datasource from global DB context.
667     * @param name DataSource name to be revoked.
668     */
revokeDB(String name)669     public void revokeDB(String name) throws com.sun.star.uno.Exception
670     {
671         dbContext.revokeObject(name) ;
672     }
673 
674     /**
675     * Initializes test table specified of the connection specified
676     * using JDBC driver. Drops table with the name <code>tbl_name</code>,
677     * creates new table with this name and then inserts data from
678     * <code>TST_TABLE_VALUES</code> constant array. <p>
679     * Test table has some predefined format which includes as much
680     * field types as possible. For every column type constants
681     * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc.
682     * are declared for column index fast find.
683     * @param tbl_name Test table name.
684     */
initTestTableUsingJDBC(String tbl_name, DataSourceInfo dsi)685     public void initTestTableUsingJDBC(String tbl_name, DataSourceInfo dsi)
686         throws java.sql.SQLException,
687                ClassNotFoundException {
688         //register jdbc driver
689         if ( dsi.Info[0].Name.equals("JavaDriverClass") ) {
690             Class.forName((String)dsi.Info[0].Value);
691         } else {
692             Class.forName(TST_JDBC_DRIVER);
693         }
694 
695         //getting connection
696         Connection connection = null;
697 
698         connection = DriverManager.getConnection(
699             dsi.URL, dsi.User, dsi.Password);
700         Statement statement = connection.createStatement();
701 
702         //drop table
703         dropMySQLTable(statement, tbl_name);
704 
705         //create table
706         createMySQLTable(statement, tbl_name);
707 
708         //insert some content
709         insertContentMySQLTable(statement, tbl_name);
710     }
711 
712     /**
713     * Inserts data from <code>TST_TABLE_VALUES</code> constant array
714     * to test table <code>tbl_name</code>.
715     * @param statement object used for executing a static SQL
716     * statement and obtaining the results produced by it.
717     * @param tbl_name Test table name.
718     */
insertContentMySQLTable(Statement statement, String tbl_name)719     protected void insertContentMySQLTable(Statement statement, String tbl_name)
720         throws java.sql.SQLException {
721 
722 
723         for(int i = 0; i < DBTools.TST_TABLE_VALUES.length; i++) {
724             String query = "insert into " + tbl_name + " values (";
725             int j = 0;
726             while(j < DBTools.TST_TABLE_VALUES[i].length) {
727                 if (j > 0) {
728                     query += ", ";
729                 }
730                 Object value = DBTools.TST_TABLE_VALUES[i][j];
731                 if (value instanceof String ||
732                     value instanceof Date) {
733                     query += "'";
734                 }
735                 if (value instanceof Date) {
736                     Date date = (Date)value;
737                     query += date.Year + "-" + date.Month +
738                         "-" + date.Day;
739                 } else if (value instanceof Boolean) {
740                     query += (((Boolean)value).booleanValue())
741                         ? "1" : "0";
742                 } else {
743                     query += value;
744                 }
745 
746                 if (value instanceof String ||
747                     value instanceof Date) {
748                     query += "'";
749                 }
750                 j++;
751             }
752             query += ")";
753             statement.executeUpdate(query);
754         }
755     }
756 
757     /**
758      * Creates test table specified.
759      * Test table has some predefined format which includes as much
760      * field types as possible. For every column type constants
761      * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc.
762      * are declared for column index fast find.
763      * @param statement object used for executing a static SQL
764      * statement and obtaining the results produced by it.
765      * @param tbl_name Test table name.
766      */
createMySQLTable(Statement statement, String tbl_name)767     protected void createMySQLTable(Statement statement, String tbl_name)
768         throws java.sql.SQLException {
769 
770         final String empty_col_name = "Column";
771         int c = 0;
772         String query = "create table " + tbl_name + " (";
773         for (int i = 0; i < TST_TABLE_VALUES[0].length; i++) {
774             if (i > 0) query += ",";
775 
776             switch(i + 1) {
777                 case TST_BINARY_STREAM:
778                     query += TST_BINARY_STREAM_F + " BLOB";
779                     break;
780                 case TST_BOOLEAN:
781                     query += TST_BOOLEAN_F + " TINYINT";
782                     break;
783                 case TST_CHARACTER_STREAM:
784                     query += TST_CHARACTER_STREAM_F + " TEXT";
785                     break;
786                 case TST_DATE:
787                     query += TST_DATE_F + " DATE";
788                     break;
789                 case TST_DOUBLE:
790                     query += TST_DOUBLE_F + " DOUBLE";
791                     break;
792                 case TST_INT:
793                     query += TST_INT_F + " INT";
794                     break;
795                 case TST_STRING:
796                     query += TST_STRING_F + " TEXT";
797                     break;
798                 default: query += empty_col_name + (c++) + " INT";
799                          if (c == 1) {
800                             query += " NOT NULL AUTO_INCREMENT";
801                          }
802             }
803         }
804         query += ", PRIMARY KEY (" + empty_col_name + "0)";
805         query += ")";
806         statement.execute(query);
807     }
808 
809     /**
810      * Drops table.
811      * @param statement object used for executing a static SQL
812      * statement and obtaining the results produced by it.
813      * @param tbl_name Test table name.
814      */
dropMySQLTable(Statement statement, String tbl_name)815     protected void dropMySQLTable(Statement statement, String tbl_name)
816         throws java.sql.SQLException {
817         statement.executeUpdate("drop table if exists " + tbl_name);
818     }
819 }
820