diff options
Diffstat (limited to 'connectivity/qa/complex/connectivity')
12 files changed, 2533 insertions, 0 deletions
diff --git a/connectivity/qa/complex/connectivity/DBaseDriverTest.java b/connectivity/qa/complex/connectivity/DBaseDriverTest.java new file mode 100755 index 000000000000..5b9e456ab367 --- /dev/null +++ b/connectivity/qa/complex/connectivity/DBaseDriverTest.java @@ -0,0 +1,78 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity; + +import complex.connectivity.dbase.DBaseDateFunctions; +import complex.connectivity.dbase.DBaseStringFunctions; +import complex.connectivity.dbase.DBaseSqlTests; +import complex.connectivity.dbase.DBaseNumericFunctions; +import com.sun.star.lang.XMultiServiceFactory; +import complexlib.ComplexTestCase; +import share.LogWriter; + +public class DBaseDriverTest extends ComplexTestCase implements TestCase +{ + public String[] getTestMethodNames() + { + return new String[] + { + "Functions" + }; + } + + @Override + public String getTestObjectName() + { + return "DBaseDriverTest"; + } + + @Override + public void assure( final String i_message, final boolean i_condition ) + { + super.assure( i_message, i_condition ); + } + + public LogWriter getLog() + { + return ComplexTestCase.log; + } + + public void Functions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + DBaseStringFunctions aStringTest = new DBaseStringFunctions(((XMultiServiceFactory) param.getMSF()), this); + aStringTest.testFunctions(); + + DBaseNumericFunctions aNumericTest = new DBaseNumericFunctions(((XMultiServiceFactory) param.getMSF()), this); + aNumericTest.testFunctions(); + + DBaseDateFunctions aDateTest = new DBaseDateFunctions(((XMultiServiceFactory) param.getMSF()), this); + aDateTest.testFunctions(); + + DBaseSqlTests aSqlTest = new DBaseSqlTests(((XMultiServiceFactory) param.getMSF()), this); + aSqlTest.testFunctions(); + } +} diff --git a/connectivity/qa/complex/connectivity/FlatFileAccess.java b/connectivity/qa/complex/connectivity/FlatFileAccess.java new file mode 100755 index 000000000000..3f0481684827 --- /dev/null +++ b/connectivity/qa/complex/connectivity/FlatFileAccess.java @@ -0,0 +1,237 @@ +package complex.connectivity; + +import com.sun.star.beans.XPropertySet; +import com.sun.star.lang.XMultiServiceFactory; +import com.sun.star.sdb.CommandType; +import com.sun.star.sdbc.SQLException; +import com.sun.star.util.Date; +import complexlib.ComplexTestCase; +import connectivity.tools.CsvDatabase; +import connectivity.tools.RowSet; +import java.io.File; +import java.io.FileOutputStream; +import java.io.PrintWriter; +import java.util.ArrayList; +import java.util.List; + +public class FlatFileAccess extends ComplexTestCase +{ + public FlatFileAccess() + { + super(); + } + + @Override + public String[] getTestMethodNames() + { + return new String[] { + "testBasicAccess", + "testCalendarFunctions", + "testSortingByFunction" + }; + } + + @Override + public String getTestObjectName() + { + return "FlatFileAccess"; + } + + public void before() throws Exception + { + m_database = new CsvDatabase( (XMultiServiceFactory)param.getMSF() ); + + // proper settings + final XPropertySet dataSourceSettings = m_database.getDataSource().geSettings(); + dataSourceSettings.setPropertyValue( "Extension", "csv" ); + dataSourceSettings.setPropertyValue( "HeaderLine", Boolean.TRUE ); + dataSourceSettings.setPropertyValue( "FieldDelimiter", " " ); + m_database.store(); + + // write the table(s) for our test + final String tableLocation = m_database.getTableFileLocation().getAbsolutePath(); + final PrintWriter tableWriter = new PrintWriter( new FileOutputStream( tableLocation + File.separatorChar + "dates.csv", false ) ); + tableWriter.println( "ID date" ); + tableWriter.println( "1 2013-01-01" ); + tableWriter.println( "2 2012-02-02" ); + tableWriter.println( "3 2011-03-03" ); + tableWriter.close(); + } + + public void after() + { + } + + private class EqualityDate extends Date + { + EqualityDate( short i_day, short i_month, short i_year ) + { + super( i_day, i_month, i_year ); + } + + EqualityDate( Date i_date ) + { + super( i_date.Day, i_date.Month, i_date.Year ); + } + + @Override + public boolean equals( Object i_compare ) + { + if ( !( i_compare instanceof Date ) ) + return false; + return Day == ((Date)i_compare).Day + && Month == ((Date)i_compare).Month + && Year == ((Date)i_compare).Year; + } + } + + /** + * ensures simple SELECTs from our table(s) work, and deliver the expected results + */ + public void testBasicAccess() + { + testRowSetResults( + "SELECT * FROM \"dates\"", + new RowSetIntGetter(1), + new Integer[] { 1, 2, 3 }, + "simple select", "wrong IDs" + ); + + testRowSetResults( + "SELECT * FROM \"dates\"", + new RowSetDateGetter( 2 ), + new EqualityDate[] { new EqualityDate( (short)1, (short)1, (short)2013 ), + new EqualityDate( (short)2, (short)2, (short)2012 ), + new EqualityDate( (short)3, (short)3, (short)2011 ) + }, + "simple select", "wrong dates" + ); + testRowSetResults( + "SELECT \"date\", \"ID\" FROM \"dates\" ORDER BY \"ID\" DESC", + new RowSetIntGetter( 2 ), + new Integer[] { 3, 2, 1 }, + "explicit column selection, sorted by IDs", "wrong IDs" + ); + testRowSetResults( + "SELECT * FROM \"dates\" ORDER BY \"date\"", + new RowSetIntGetter( 1 ), + new Integer[] { 3, 2, 1 }, + "sorted by date", "wrong IDs" + ); + } + + /** + * ensures the basic functionality for selecting calendar functions from a CSV table - this is a prerequisite for + * later tests. + */ + public void testCalendarFunctions() + { + // simple check for proper results of the calendar functions (DATE/MONTH) + // The * at the first position is crucial here - there was code which wrongly calculated + // column positions of function columns when * was present in the statement + testRowSetResults( + "SELECT \"dates\".*, YEAR( \"date\" ) FROM \"dates\"", + new RowSetIntGetter( 3 ), + new Integer[] { 2013, 2012, 2011 }, + "YEAR function", "wrong calculated years" + ); + testRowSetResults( + "SELECT \"dates\".*, MONTH( \"date\" ) FROM \"dates\"", + new RowSetIntGetter( 3 ), + new Integer[] { 1, 2, 3 }, + "MONTH function", "wrong calculated months" + ); + } + + /** + * ensures that sorting by a function column works + */ + public void testSortingByFunction() + { + // most simple case: select a function, and sort by it + testRowSetResults( + "SELECT YEAR( \"date\" ) AS \"year\" FROM \"dates\" ORDER BY \"year\"", + new RowSetIntGetter(1), + new Integer[] { 2011, 2012, 2013 }, + "single YEAR selection, sorted by years", "wrong calculated years" + ); + // somewhat more "difficult" (this used to crash): Select all columns, plus a function, so the calculated + // column has a position greater than column count + testRowSetResults( + "SELECT \"dates\".*, YEAR( \"date\" ) AS \"year\" FROM \"dates\" ORDER BY \"year\" DESC", + new RowSetIntGetter(3), + new Integer[] { 2013, 2012, 2011 }, + "extended YEAR selection, sorted by years", "wrong calculated years" + ); + } + + private interface RowSetValueGetter + { + public Object getValue( final RowSet i_rowSet ) throws SQLException; + } + + private abstract class RowSetColumnValueGetter implements RowSetValueGetter + { + RowSetColumnValueGetter( final int i_columnIndex ) + { + m_columnIndex = i_columnIndex; + } + + protected final int m_columnIndex; + } + + private class RowSetIntGetter extends RowSetColumnValueGetter + { + RowSetIntGetter( final int i_columnIndex ) + { + super( i_columnIndex ); + } + + public Object getValue( final RowSet i_rowSet ) throws SQLException + { + return i_rowSet.getInt( m_columnIndex ); + } + } + + private class RowSetDateGetter extends RowSetColumnValueGetter + { + RowSetDateGetter( final int i_columnIndex ) + { + super( i_columnIndex ); + } + + public Object getValue( final RowSet i_rowSet ) throws SQLException + { + return i_rowSet.getDate( m_columnIndex ); + } + } + + private <T> void testRowSetResults( String i_command, RowSetValueGetter i_getter, + T[] i_expectedValues, String i_context, String i_failureDesc ) + { + RowSet rowSet = null; + try + { + rowSet = m_database.createRowSet( CommandType.COMMAND, i_command ); + rowSet.execute(); + + List< T > values = new ArrayList< T >(); + while ( rowSet.next() ) + { + values.add( (T)i_getter.getValue( rowSet ) ); + } + assureEquals( i_context + ": " + i_failureDesc, i_expectedValues, values.toArray(), true ); + } + catch( final SQLException e ) + { + failed( i_context + ": caught an exception: " + e.toString(), false ); + } + finally + { + if ( rowSet != null ) + rowSet.dispose(); + } + } + + private CsvDatabase m_database = null; +} diff --git a/connectivity/qa/complex/connectivity/HsqlDriverTest.java b/connectivity/qa/complex/connectivity/HsqlDriverTest.java new file mode 100755 index 000000000000..28ad862c4a76 --- /dev/null +++ b/connectivity/qa/complex/connectivity/HsqlDriverTest.java @@ -0,0 +1,156 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity; + +import complex.connectivity.hsqldb.TestCacheSize; +import com.sun.star.frame.XModel; +import com.sun.star.frame.XStorable; + +import com.sun.star.lang.*; +import com.sun.star.document.XDocumentSubStorageSupplier; +import complexlib.ComplexTestCase; + + +import org.hsqldb.lib.StopWatch; +import com.sun.star.uno.UnoRuntime; +import com.sun.star.beans.PropertyState; +import com.sun.star.lang.XMultiServiceFactory; +import com.sun.star.embed.XStorage; +import com.sun.star.sdbc.XDataSource; +import com.sun.star.sdbc.XDriver; +import connectivity.tools.HsqlDatabase; + +public class HsqlDriverTest extends ComplexTestCase { + + + public String[] getTestMethodNames() { + return new String[] { "test" }; + } + + @Override + public String getTestObjectName() { + return "DriverTest"; + } + + public void assurePublic(String sMessage,boolean check){ + super.assure(sMessage,check); + } + + public void test(){ + XDataSource ds = null; + System.gc(); + try { + HsqlDatabase database = new HsqlDatabase( (XMultiServiceFactory)param.getMSF() ); + ds = database.getDataSource().getXDataSource(); + } catch(Exception ex) { + throw new RuntimeException("factory: unable to construct data source" ); + } + + try{ + XDocumentSubStorageSupplier doc = (XDocumentSubStorageSupplier)UnoRuntime.queryInterface(XDocumentSubStorageSupplier.class,ds); + XStorage stor = doc.getDocumentSubStorage("database",4); + try{ + if ( stor.isStreamElement("db.log") ) + stor.removeElement("db.log"); + } catch(Exception e){} + try{ + if ( stor.isStreamElement("db.properties") ) + stor.removeElement("db.properties"); + } catch(Exception e){} + try{ + if ( stor.isStreamElement("db.script") ) + stor.removeElement("db.script"); + } catch(Exception e){} + try{ + if ( stor.isStreamElement("db.script.new") ) + stor.removeElement("db.script.new"); + } catch(Exception e){} + XStorable mod = (XStorable)UnoRuntime.queryInterface(XStorable.class,ds); + mod.store(); + XComponent xComp = (XComponent)UnoRuntime.queryInterface(XComponent.class,stor); + if ( xComp != null ) + xComp.dispose(); + } catch(Exception e){} + + com.sun.star.beans.PropertyValue[] info = null; + XDriver drv = null; + try{ + XDocumentSubStorageSupplier doc = (XDocumentSubStorageSupplier)UnoRuntime.queryInterface(XDocumentSubStorageSupplier.class,ds); + XModel mod = (XModel)UnoRuntime.queryInterface(XModel.class,ds); + XStorage stor = doc.getDocumentSubStorage("database",4); + info = new com.sun.star.beans.PropertyValue[]{ + new com.sun.star.beans.PropertyValue("Storage",0,stor,PropertyState.DIRECT_VALUE) + ,new com.sun.star.beans.PropertyValue("URL",0,mod.getURL(),PropertyState.DIRECT_VALUE) + }; + drv = (XDriver)UnoRuntime.queryInterface(XDriver.class,((XMultiServiceFactory)param.getMSF()).createInstance("com.sun.star.sdbcx.comp.hsqldb.Driver")); + + + TestCacheSize test = new TestCacheSize(((XMultiServiceFactory)param.getMSF()),info,drv); + + StopWatch sw = new StopWatch(); + + try{ + test.setUp(); + test.testFillUp(); + test.checkResults(); + test.tearDown(); + System.out.println("Total Test Time: " + sw.elapsedTime()); + } catch(Exception e){} + + try{ + XStorable mod2 = (XStorable)UnoRuntime.queryInterface(XStorable.class,ds); + mod2.store(); + } catch(Exception e){} + }catch(Exception e){} + } + public void test2(){ + System.gc(); + + com.sun.star.beans.PropertyValue[] info = null; + XDriver drv = null; + try{ + info = new com.sun.star.beans.PropertyValue[]{ + new com.sun.star.beans.PropertyValue("JavaDriverClass",0,"org.hsqldb.jdbcDriver",PropertyState.DIRECT_VALUE) + ,new com.sun.star.beans.PropertyValue("ParameterNameSubstitution",0, false,PropertyState.DIRECT_VALUE) + }; + drv = (XDriver)UnoRuntime.queryInterface(XDriver.class,((XMultiServiceFactory)param.getMSF()).createInstance("com.sun.star.comp.sdbc.JDBCDriver")); + TestCacheSize test = new TestCacheSize(((XMultiServiceFactory)param.getMSF()),info,drv); + test.setURL("jdbc:hsqldb:g:\\hsql\\db"); + + + StopWatch sw = new StopWatch(); + + try{ + test.setUp(); + test.testFillUp(); + test.checkResults(); + test.tearDown(); + System.out.println("Total Test Time: " + sw.elapsedTime()); + } catch(Exception e){} + }catch(Exception e){} + } +} diff --git a/connectivity/qa/complex/connectivity/JdbcLongVarCharTest.java b/connectivity/qa/complex/connectivity/JdbcLongVarCharTest.java new file mode 100755 index 000000000000..7b4418a82ab5 --- /dev/null +++ b/connectivity/qa/complex/connectivity/JdbcLongVarCharTest.java @@ -0,0 +1,132 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity; + +import com.sun.star.beans.PropertyState; +import com.sun.star.beans.PropertyValue; +import com.sun.star.lang.XMultiServiceFactory; +import com.sun.star.sdbc.XResultSet; +import com.sun.star.sdbc.XClob; +import com.sun.star.sdbc.XDriverAccess; +import com.sun.star.sdbc.XParameters; +import com.sun.star.sdbc.XPreparedStatement; +import com.sun.star.sdbc.XResultSetMetaData; +import com.sun.star.sdbc.XResultSetMetaDataSupplier; +import com.sun.star.sdbc.XRow; +import com.sun.star.uno.UnoRuntime; +import complexlib.ComplexTestCase; + +public class JdbcLongVarCharTest extends ComplexTestCase +{ + + public String[] getTestMethodNames() + { + return new String[] + { + "testLongVarChar" + }; + } + + @Override + public String getTestObjectName() + { + return "LongVarCharTest"; + } + + public void testLongVarChar() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + + try + { + System.out.println("== Start testing =="); + + String url = "jdbc:mysql://localhost:3306/mysql?user=root"; + //String url = "jdbc:ingres://localhost:II7/demodb;AUTO=multi"; + com.sun.star.sdbc.XConnection xConnection = null; + com.sun.star.beans.PropertyValue prop[] = new PropertyValue[1]; + prop[0] = new PropertyValue("JavaDriverClass", 0, "com.mysql.jdbc.Driver", PropertyState.DIRECT_VALUE); + //prop[0] = new PropertyValue("JavaDriverClass", 0, "com.ingres.jdbc.IngresDriver", PropertyState.DIRECT_VALUE); + + // get the remote office component context + XMultiServiceFactory xServiceManager = (XMultiServiceFactory) param.getMSF(); + Object x = xServiceManager.createInstance("com.sun.star.sdbc.DriverManager"); + com.sun.star.sdbc.XDriverAccess xDriverAccess = (XDriverAccess) UnoRuntime.queryInterface(XDriverAccess.class, x); + com.sun.star.sdbc.XDriver xDriver = xDriverAccess.getDriverByURL(url); + xConnection = xDriver.connect(url, prop); + + //Object prepStmnt = xConnection.prepareStatement("SELECT * FROM t1 WHERE t1.c1 = ?"); + Object prepStmnt = xConnection.prepareStatement("SELECT * FROM i90114 WHERE i90114.c1 = ?"); + ((XParameters) UnoRuntime.queryInterface(XParameters.class, prepStmnt)).clearParameters(); + ((XParameters) UnoRuntime.queryInterface(XParameters.class, prepStmnt)).setInt(1, 1); + XResultSet xResultSet = ((XPreparedStatement) prepStmnt).executeQuery(); + XRow xRow = (XRow) UnoRuntime.queryInterface(XRow.class, xResultSet); + + XResultSetMetaDataSupplier xRsMetaSup = (XResultSetMetaDataSupplier) UnoRuntime.queryInterface(XResultSetMetaDataSupplier.class, xResultSet); + XResultSetMetaData xRsMetaData = xRsMetaSup.getMetaData(); + int nColumnCount = xRsMetaData.getColumnCount(); + + System.out.println("== MetaData =="); + for (int i = 1; i <= nColumnCount; ++i) + { + System.out.println("Name: " + xRsMetaData.getColumnName(i) + " Type: " + + xRsMetaData.getColumnType(i)); + } + + System.out.println("== Result =="); + while (xResultSet.next()) + { + String str = "not set"; + + XClob xClob = null; + xClob = xRow.getClob(2); + if (xClob != null) + { + System.out.println("xClob != null"); + int len = (int) xClob.length(); + str = xClob.getSubString(1, len); + } + else + { + System.out.println("xClob == null"); + } + + System.out.println("c1 (Int): " + xRow.getInt(1) + " c2 (String): " + xRow.getString(2) + " c3 (Clob): " + str); + } + + xConnection.close(); + } + catch (java.lang.Exception e) + { + System.out.println("== Exception occurred while testing =="); + e.printStackTrace(); + } finally + { + System.out.println("== End testing =="); + System.exit(0); + } + } +}
\ No newline at end of file diff --git a/connectivity/qa/complex/connectivity/SubTestCase.java b/connectivity/qa/complex/connectivity/SubTestCase.java new file mode 100755 index 000000000000..1c2e685c5943 --- /dev/null +++ b/connectivity/qa/complex/connectivity/SubTestCase.java @@ -0,0 +1,23 @@ +package complex.connectivity; + +import share.LogWriter; + +public class SubTestCase implements TestCase +{ + protected SubTestCase( final TestCase i_parentTestCase ) + { + m_parentTestCase = i_parentTestCase; + } + + public void assure( String i_message, boolean i_condition ) + { + m_parentTestCase.assure( i_message, i_condition ); + } + + public LogWriter getLog() + { + return m_parentTestCase.getLog(); + } + + private final TestCase m_parentTestCase; +} diff --git a/connectivity/qa/complex/connectivity/TestCase.java b/connectivity/qa/complex/connectivity/TestCase.java new file mode 100755 index 000000000000..bae5fcdcb4c4 --- /dev/null +++ b/connectivity/qa/complex/connectivity/TestCase.java @@ -0,0 +1,35 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity; + +import share.LogWriter; + +public interface TestCase +{ + public void assure( final String i_message, final boolean i_condition ); + public LogWriter getLog(); +} diff --git a/connectivity/qa/complex/connectivity/dbase/DBaseDateFunctions.java b/connectivity/qa/complex/connectivity/dbase/DBaseDateFunctions.java new file mode 100755 index 000000000000..9cfc6aaaf590 --- /dev/null +++ b/connectivity/qa/complex/connectivity/dbase/DBaseDateFunctions.java @@ -0,0 +1,305 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity.dbase; + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.sdbc.*; +import com.sun.star.beans.XPropertySet; + +import com.sun.star.lang.XMultiServiceFactory; +import complex.connectivity.TestCase; +import complex.connectivity.SubTestCase; + +public class DBaseDateFunctions extends SubTestCase +{ + + private final String where = "FROM \"biblio\" \"biblio\" where \"Identifier\" = 'BOR00'"; + private final XMultiServiceFactory m_xORB; + + public DBaseDateFunctions(final XMultiServiceFactory _xORB, final TestCase i_testCase) + { + super( i_testCase ); + m_xORB = _xORB; + } + + public void testFunctions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRowSet xRowRes = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, + m_xORB.createInstance("com.sun.star.sdb.RowSet")); + + getLog().println("starting DateTime function test!"); + // set the properties needed to connect to a database + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("DataSourceName", "Bibliography"); + + xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.COMMAND)); + + try + { + curdate(xRowRes); + } + catch (SQLException ex) + { + assure("upper " + ex.getMessage(), false); + throw ex; + } + try + { + curtime(xRowRes); + } + catch (SQLException ex) + { + assure("lower " + ex.getMessage(), false); + throw ex; + } + try + { + dayname(xRowRes); + } + catch (SQLException ex) + { + assure("ascii " + ex.getMessage(), false); + throw ex; + } + try + { + dayofmonth(xRowRes); + } + catch (SQLException ex) + { + assure("char_len " + ex.getMessage(), false); + throw ex; + } + try + { + dayofweek(xRowRes); + } + catch (SQLException ex) + { + assure("concat " + ex.getMessage(), false); + throw ex; + } + try + { + dayofyear(xRowRes); + } + catch (SQLException ex) + { + assure("locate " + ex.getMessage(), false); + throw ex; + } + try + { + hour(xRowRes); + } + catch (SQLException ex) + { + assure("substr " + ex.getMessage(), false); + throw ex; + } + try + { + minute(xRowRes); + } + catch (SQLException ex) + { + assure("ltrim " + ex.getMessage(), false); + throw ex; + } + try + { + month(xRowRes); + } + catch (SQLException ex) + { + assure("rtrim " + ex.getMessage(), false); + throw ex; + } + try + { + monthname(xRowRes); + } + catch (SQLException ex) + { + assure("space " + ex.getMessage(), false); + throw ex; + } + try + { + now(xRowRes); + } + catch (SQLException ex) + { + assure("replace " + ex.getMessage(), false); + throw ex; + } + try + { + quarter(xRowRes); + } + catch (SQLException ex) + { + assure("repeat " + ex.getMessage(), false); + throw ex; + } + try + { + second(xRowRes); + } + catch (SQLException ex) + { + assure("insert " + ex.getMessage(), false); + throw ex; + } + try + { + week(xRowRes); + } + catch (SQLException ex) + { + assure("left " + ex.getMessage(), false); + throw ex; + } + try + { + year(xRowRes); + } + catch (SQLException ex) + { + assure("right " + ex.getMessage(), false); + throw ex; + } + } + + private XRow execute(final XRowSet xRowRes, final String sql) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("Command", "SELECT " + sql + where); + xRowRes.execute(); + final XResultSet xRes = (XResultSet) UnoRuntime.queryInterface(XResultSet.class, xRowRes); + assure("No valid row! ", xRes.next()); + + return (XRow) UnoRuntime.queryInterface(XRow.class, xRes); + } + + private void dayofweek(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "DAYOFWEEK('1998-02-03') "); + assure("DAYOFWEEK('1998-02-03') failed!", row.getInt(1) == 3); + } + + private void dayofmonth(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "DAYOFMONTH('1998-02-03') "); + assure("DAYOFMONTH('1998-02-03') failed!", row.getInt(1) == 3); + } + + private void dayofyear(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "DAYOFYEAR('1998-02-03') "); + assure("DAYOFYEAR('1998-02-03') failed!", row.getInt(1) == 34); + } + + private void month(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "month('1998-02-03') "); + assure("month('1998-02-03') failed!", row.getInt(1) == 2); + } + + private void dayname(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "DAYNAME('1998-02-05') "); + assure("DAYNAME('1998-02-05') failed!", row.getString(1).equals("Thursday")); + } + + private void monthname(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "MONTHNAME('1998-02-05') "); + assure("MONTHNAME('1998-02-05') failed!", row.getString(1).equals("February")); + } + + private void quarter(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "QUARTER('98-01-01'),QUARTER('98-04-01'),QUARTER('98-07-01'),QUARTER('98-10-01') "); + assure("QUARTER('98-01-01') failed!", row.getInt(1) == 1); + assure("QUARTER('98-04-01') failed!", row.getInt(2) == 2); + assure("QUARTER('98-07-01') failed!", row.getInt(3) == 3); + assure("QUARTER('98-10-01') failed!", row.getInt(4) == 4); + } + + private void week(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "WEEK('1998-02-20') "); + assure("WEEK('1998-02-20') failed!", row.getInt(1) == 7); + } + + private void year(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "YEAR('98-02-03') "); + assure("YEAR('98-02-03') failed!", row.getInt(1) == 98); + } + + private void hour(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "HOUR('10:05:03') "); + assure("HOUR('10:05:03') failed!", row.getInt(1) == 10); + } + + private void minute(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "MINUTE('98-02-03 10:05:03') "); + assure("MINUTE('98-02-03 10:05:03') failed!", row.getInt(1) == 5); + } + + private void second(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "SECOND('10:05:03') "); + assure("SECOND('10:05:03') failed!", row.getInt(1) == 3); + } + + private void curdate(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "CURDATE() "); + final com.sun.star.util.Date aDate = row.getDate(1); + getLog().println("CURDATE() is '" + aDate.Year + "-" + aDate.Month + "-" + aDate.Day + "'"); + } + + private void curtime(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "CURTIME() "); + final com.sun.star.util.Time aTime = row.getTime(1); + getLog().println("CURTIME() is '" + aTime.Hours + ":" + aTime.Minutes + ":" + aTime.Seconds + "'"); + } + + private void now(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "NOW() "); + final com.sun.star.util.DateTime aTime = row.getTimestamp(1); + getLog().println("NOW() is '" + aTime.Year + "-" + aTime.Month + "-" + aTime.Day + "'"); + getLog().println("'" + aTime.Hours + ":" + aTime.Minutes + ":" + aTime.Seconds + "'"); + } +} diff --git a/connectivity/qa/complex/connectivity/dbase/DBaseNumericFunctions.java b/connectivity/qa/complex/connectivity/dbase/DBaseNumericFunctions.java new file mode 100755 index 000000000000..f5aec94a3c23 --- /dev/null +++ b/connectivity/qa/complex/connectivity/dbase/DBaseNumericFunctions.java @@ -0,0 +1,397 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity.dbase; + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.sdbc.*; +import com.sun.star.beans.XPropertySet; +import com.sun.star.lang.XMultiServiceFactory; +import complex.connectivity.SubTestCase; +import complex.connectivity.TestCase; + + +public class DBaseNumericFunctions extends SubTestCase +{ + private final String where = "FROM \"biblio\" \"biblio\" where \"Identifier\" = 'BOR00'"; + private final XMultiServiceFactory m_xORB; + + public DBaseNumericFunctions(final XMultiServiceFactory _xORB, final TestCase i_testCase) + { + super( i_testCase ); + m_xORB = _xORB; + } + + public void testFunctions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRowSet xRowRes = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, + m_xORB.createInstance("com.sun.star.sdb.RowSet")); + + getLog().println("starting Numeric function test"); + // set the properties needed to connect to a database + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("DataSourceName", "Bibliography"); + + xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.COMMAND)); + + try + { + abs(xRowRes); + } + catch (SQLException ex) + { + assure("abs " + ex.getMessage(), false); + throw ex; + } + try + { + acos(xRowRes); + } + catch (SQLException ex) + { + assure("acos " + ex.getMessage(), false); + throw ex; + } + try + { + asin(xRowRes); + } + catch (SQLException ex) + { + assure("asin " + ex.getMessage(), false); + throw ex; + } + try + { + atan(xRowRes); + } + catch (SQLException ex) + { + assure("atan " + ex.getMessage(), false); + throw ex; + } + try + { + atan2(xRowRes); + } + catch (SQLException ex) + { + assure("atan2 " + ex.getMessage(), false); + throw ex; + } + try + { + ceiling(xRowRes); + } + catch (SQLException ex) + { + assure("ceiling " + ex.getMessage(), false); + throw ex; + } + try + { + cos(xRowRes); + } + catch (SQLException ex) + { + assure("cos " + ex.getMessage(), false); + throw ex; + } + try + { + degrees(xRowRes); + } + catch (SQLException ex) + { + assure("degrees " + ex.getMessage(), false); + throw ex; + } + try + { + exp(xRowRes); + } + catch (SQLException ex) + { + assure("exp " + ex.getMessage(), false); + throw ex; + } + try + { + floor(xRowRes); + } + catch (SQLException ex) + { + assure("floor " + ex.getMessage(), false); + throw ex; + } + try + { + log(xRowRes); + } + catch (SQLException ex) + { + assure("log " + ex.getMessage(), false); + throw ex; + } + try + { + log10(xRowRes); + } + catch (SQLException ex) + { + assure("log10 " + ex.getMessage(), false); + throw ex; + } + try + { + mod(xRowRes); + } + catch (SQLException ex) + { + assure("mod " + ex.getMessage(), false); + throw ex; + } + try + { + pi(xRowRes); + } + catch (SQLException ex) + { + assure("pi " + ex.getMessage(), false); + throw ex; + } + try + { + pow(xRowRes); + } + catch (SQLException ex) + { + assure("pow " + ex.getMessage(), false); + throw ex; + } + try + { + radians(xRowRes); + } + catch (SQLException ex) + { + assure("radians " + ex.getMessage(), false); + throw ex; + } + try + { + round(xRowRes); + } + catch (SQLException ex) + { + assure("round " + ex.getMessage(), false); + throw ex; + } + try + { + sign(xRowRes); + } + catch (SQLException ex) + { + assure("sign " + ex.getMessage(), false); + throw ex; + } + try + { + sin(xRowRes); + } + catch (SQLException ex) + { + assure("sin " + ex.getMessage(), false); + throw ex; + } + try + { + sqrt(xRowRes); + } + catch (SQLException ex) + { + assure("sqrt " + ex.getMessage(), false); + throw ex; + } + try + { + tan(xRowRes); + } + catch (SQLException ex) + { + assure("tan " + ex.getMessage(), false); + throw ex; + } + + } + + private XRow execute(final XRowSet xRowRes,final String sql) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("Command", "SELECT " + sql + where); + xRowRes.execute(); + final XResultSet xRes = (XResultSet) UnoRuntime.queryInterface(XResultSet.class, xRowRes); + assure("No valid row! ", xRes.next()); + + return (XRow) UnoRuntime.queryInterface(XRow.class, xRes); + } + + private void abs(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ABS(2),ABS(-32) "); + assure("ABS(2) failed!", row.getInt(1) == 2); + assure("ABS(-32) failed!", row.getInt(2) == 32); + } + + private void sign(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "SIGN(-32),SIGN(0),SIGN(234) "); + assure("SIGN(-32)failed!", row.getInt(1) == -1); + assure("SIGN(0) failed!", row.getInt(2) == 0); + assure("SIGN(234) failed!", row.getInt(3) == 1); + } + + private void mod(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "MOD(234, 10) "); + assure("MOD(234, 10) failed!", row.getInt(1) == 4); + } + + private void floor(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "FLOOR(1.23),FLOOR(-1.23) "); + assure("FLOOR(1.23) failed!", row.getInt(1) == 1); + assure("FLOOR(-1.23) failed!", row.getInt(2) == -2); + } + + private void ceiling(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "CEILING(1.23),CEILING(-1.23) "); + assure("CEILING(1.23) failed!", row.getInt(1) == 2); + assure("CEILING(-1.23) failed!", row.getInt(2) == -1); + } + + private void round(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ROUND(-1.23),ROUND(1.298, 1) "); + assure("ROUND(-1.23) failed!", row.getInt(1) == -1); + assure("ROUND(1.298, 1) failed!", row.getDouble(2) == 1.3); + } + + private void exp(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "EXP(2),EXP(-2) "); + assure("EXP(2) failed!", (float) row.getDouble(1) == (float) java.lang.Math.exp(2)); + assure("EXP(-2) failed!", (float) row.getDouble(2) == (float) java.lang.Math.exp(-2)); + } + + private void log(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "LOG(2),LOG(-2) "); + assure("LOG(2) failed!", (float) row.getDouble(1) == (float) java.lang.Math.log(2)); + row.getDouble(2); + assure("LOG(-2) failed!", row.wasNull()); + } + + private void log10(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "LOG10(100) "); + assure("LOG10(100) failed!", row.getDouble(1) == 2.0); + } + + private void pow(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "POWER(2,2) "); + assure("POWER(2,2) failed!", row.getDouble(1) == 4.0); + } + + private void sqrt(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "SQRT(4) "); + assure("SQRT(4) failed!", row.getDouble(1) == 2.0); + } + + private void pi(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "PI() "); + assure("PI() failed!", (float) row.getDouble(1) == (float) java.lang.Math.PI); + } + + private void cos(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "COS(PI()) "); + assure("COS(PI()) failed!", row.getDouble(1) == -1.0); + } + + private void sin(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "SIN(2) "); + assure("SIN(PI()) failed!", (float) row.getDouble(1) == (float) java.lang.Math.sin(2)); + } + + private void tan(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "TAN(PI()+1) "); + assure("TAN(PI()+1) failed!", (float) row.getDouble(1) == (float) java.lang.Math.tan(java.lang.Math.PI + 1.0)); + } + + private void acos(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ACOS(1) "); + assure("ACOS(1) failed!", (float) row.getDouble(1) == 0.0); + } + + private void asin(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ASIN(0) "); + assure("ASIN(0) failed!", (float) row.getDouble(1) == (float) java.lang.Math.asin(0.0)); + } + + private void atan(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ATAN(0) "); + assure("ATAN(0) failed!", row.getDouble(1) == 0.0); + } + + private void atan2(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ATAN2(0,2) "); + assure("ATAN2(0,2) failed!", (float) row.getDouble(1) == 0.0); + } + + private void degrees(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "DEGREES(PI()) "); + assure("DEGREES(PI()) failed!", row.getDouble(1) == 180.0); + } + + private void radians(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "RADIANS(90) "); + assure("RADIANS(90) failed!", (float) row.getDouble(1) == (float) (java.lang.Math.PI / 2.0)); + } +} diff --git a/connectivity/qa/complex/connectivity/dbase/DBaseSqlTests.java b/connectivity/qa/complex/connectivity/dbase/DBaseSqlTests.java new file mode 100755 index 000000000000..1265e05e7bef --- /dev/null +++ b/connectivity/qa/complex/connectivity/dbase/DBaseSqlTests.java @@ -0,0 +1,92 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity.dbase; + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.sdbc.*; +import com.sun.star.beans.XPropertySet; +import com.sun.star.lang.XMultiServiceFactory; +import complex.connectivity.TestCase; +import complex.connectivity.SubTestCase; + +public class DBaseSqlTests extends SubTestCase +{ + private final XMultiServiceFactory m_xORB; + + public DBaseSqlTests(final XMultiServiceFactory _xORB,final TestCase i_testCase) + { + super( i_testCase ); + m_xORB = _xORB; + } + + public void testFunctions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRowSet xRowRes = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, + m_xORB.createInstance("com.sun.star.sdb.RowSet")); + + getLog().println("starting SQL test"); + // set the properties needed to connect to a database + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("DataSourceName", "Bibliography"); + xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.COMMAND)); + + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where \"Identifier\" like 'B%'"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where not \"Identifier\" like 'B%'"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where not \"Identifier\" not like 'B%'"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where not(0 = 1)"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where 0 = 0"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where (0 = 0)"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where 0 <> 1"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where 0 < 1"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where 2 > 1"); + execute(xRowRes,"1,1+1,'a' + 'b' FROM \"biblio\" \"biblio\" where 2 > 1"); + // execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where (0 = 0) is true"); + // execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where not (0 = 0) is not true"); + // execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where 1 between 0 and 2"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where not \"Identifier\" is NULL"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where \"Identifier\" is not NULL"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where \"Identifier\" = \"Identifier\""); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where not(not(\"Identifier\" = \"Identifier\"))"); + execute(xRowRes,"1 FROM \"biblio\" \"biblio\" where (1 = 1 and 2 = 1) or 3 = 33 or 4 = 44 or ('a' = 'a' and 'b' = 'b')"); + } + + private void execute(final XRowSet xRowRes, String sql) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + try + { + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("Command", "SELECT " + sql); + xRowRes.execute(); + } + catch(SQLException e) + { + getLog().println(sql + " Error: " + e.getMessage()); + } + } + + +} diff --git a/connectivity/qa/complex/connectivity/dbase/DBaseStringFunctions.java b/connectivity/qa/complex/connectivity/dbase/DBaseStringFunctions.java new file mode 100755 index 000000000000..30b94484d53d --- /dev/null +++ b/connectivity/qa/complex/connectivity/dbase/DBaseStringFunctions.java @@ -0,0 +1,319 @@ +/************************************************************************* + * + * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * + * OpenOffice.org - a multi-platform office productivity suite + * + * This file is part of OpenOffice.org. + * + * OpenOffice.org is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License version 3 + * only, as published by the Free Software Foundation. + * + * OpenOffice.org is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Lesser General Public License version 3 for more details + * (a copy is included in the LICENSE file that accompanied this code). + * + * You should have received a copy of the GNU Lesser General Public License + * version 3 along with OpenOffice.org. If not, see + * <http://www.openoffice.org/license.html> + * for a copy of the LGPLv3 License. + * + ************************************************************************/ +package complex.connectivity.dbase; + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.sdbc.*; +import com.sun.star.beans.XPropertySet; +import com.sun.star.lang.XMultiServiceFactory; +import complex.connectivity.SubTestCase; +import complex.connectivity.TestCase; + +public class DBaseStringFunctions extends SubTestCase +{ + private String where = "FROM \"biblio\" \"biblio\" where \"Identifier\" = 'BOR00'"; + private final XMultiServiceFactory m_xORB; + + public DBaseStringFunctions(final XMultiServiceFactory _xORB,final TestCase i_testCase) + { + super( i_testCase ); + m_xORB = _xORB; + } + + public void testFunctions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRowSet xRowRes = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, + m_xORB.createInstance("com.sun.star.sdb.RowSet")); + + getLog().println("starting String function test"); + // set the properties needed to connect to a database + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("DataSourceName", "Bibliography"); + + xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.COMMAND)); + + try + { + upper(xRowRes); + } + catch (SQLException ex) + { + assure("upper " + ex.getMessage(), false); + throw ex; + } + try + { + lower(xRowRes); + } + catch (SQLException ex) + { + assure("lower " + ex.getMessage(), false); + throw ex; + } + try + { + acsii(xRowRes); + } + catch (SQLException ex) + { + assure("ascii " + ex.getMessage(), false); + throw ex; + } + try + { + char_length(xRowRes); + } + catch (SQLException ex) + { + assure("char_len " + ex.getMessage(), false); + throw ex; + } + try + { + concat(xRowRes); + } + catch (SQLException ex) + { + assure("concat " + ex.getMessage(), false); + throw ex; + } + try + { + chartest(xRowRes); + } + catch (SQLException ex) + { + assure("char " + ex.getMessage(), false); + throw ex; + } + try + { + locate(xRowRes); + } + catch (SQLException ex) + { + assure("locate " + ex.getMessage(), false); + throw ex; + } + try + { + substring(xRowRes); + } + catch (SQLException ex) + { + assure("substr " + ex.getMessage(), false); + throw ex; + } + try + { + ltrim(xRowRes); + } + catch (SQLException ex) + { + assure("ltrim " + ex.getMessage(), false); + throw ex; + } + try + { + rtrim(xRowRes); + } + catch (SQLException ex) + { + assure("rtrim " + ex.getMessage(), false); + throw ex; + } + try + { + space(xRowRes); + } + catch (SQLException ex) + { + assure("space " + ex.getMessage(), false); + throw ex; + } + try + { + replace(xRowRes); + } + catch (SQLException ex) + { + assure("replace " + ex.getMessage(), false); + throw ex; + } + try + { + repeat(xRowRes); + } + catch (SQLException ex) + { + assure("repeat " + ex.getMessage(), false); + throw ex; + } + try + { + insert(xRowRes); + } + catch (SQLException ex) + { + assure("insert " + ex.getMessage(), false); + throw ex; + } + try + { + left(xRowRes); + } + catch (SQLException ex) + { + assure("left " + ex.getMessage(), false); + throw ex; + } + try + { + right(xRowRes); + } + catch (SQLException ex) + { + assure("right " + ex.getMessage(), false); + throw ex; + } + } + + private XRow execute(final XRowSet xRowRes, String sql) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); + xProp.setPropertyValue("Command", "SELECT " + sql + where); + xRowRes.execute(); + final XResultSet xRes = (XResultSet) UnoRuntime.queryInterface(XResultSet.class, xRowRes); + assure("No valid row! ", xRes.next()); + + return (XRow) UnoRuntime.queryInterface(XRow.class, xRes); + } + + private void upper(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "upper('test'),UCASE('test') "); + assure("upper('test') failed!", row.getString(1).equals("TEST")); + assure("ucase('test') failed!", row.getString(2).equals("TEST")); + } + + private void lower(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "lower('TEST'),LCASE('TEST') "); + assure("lower('TEST') failed!", row.getString(1).equals("test")); + assure("lcase('TEST') failed!", row.getString(2).equals("test")); + final String temp = where; + where = "FROM \"biblio\" \"biblio\" where LOWER(\"Identifier\") like 'bor%'"; + execute(xRowRes, "lower('TEST'),LCASE('TEST') "); + where = temp; + } + + private void acsii(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "ASCII('2') "); + assure("acsii('2') failed!", row.getInt(1) == 50); + } + + private void char_length(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "char_length('test'),character_length('test'),OCTET_LENGTH('test') "); + assure("char_length('test') failed!", row.getInt(1) == 4); + assure("character_length('test') failed!", row.getInt(2) == 4); + assure("OCTET_LENGTH('test') failed!", row.getInt(3) == 4); + } + + private void concat(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "CONCAT('Hello',' ','World') "); + assure("CONCAT('Hello',' ',,'World') failed!", row.getString(1).equals("Hello World")); + } + + private void locate(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "LOCATE('bar', 'foobarbar') "); + assure("LOCATE('bar', 'foobarbar') failed!", row.getInt(1) == 4); + } + + private void substring(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "SUBSTRING('Quadratically',5) "); + assure("SUBSTRING('Quadratically',5) failed!", row.getString(1).equals("ratically")); + } + + private void ltrim(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "LTRIM(' barbar') "); + assure("LTRIM(' barbar') failed!", row.getString(1).equals("barbar")); + } + + private void rtrim(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "RTRIM('barbar ') "); + assure("RTRIM('barbar ') failed!", row.getString(1).equals("barbar")); + } + + private void space(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "space(6) "); + assure("space(6) failed!", row.getString(1).equals(" ")); + } + + private void replace(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "REPLACE('www.OOo.com', 'w', 'Ww') "); + assure("REPLACE('www.OOo.com', 'w', 'Ww') failed!", row.getString(1).equals("WwWwWw.OOo.com")); + } + + private void repeat(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "REPEAT('OOo', 3) "); + assure("REPEAT('OOo', 3) failed!", row.getString(1).equals("OOoOOoOOo")); + } + + private void insert(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "INSERT('Quadratic', 3, 4, 'What') "); + assure("INSERT('Quadratic', 3, 4, 'What') failed!", row.getString(1).equals("QuWhattic")); + } + + private void left(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "LEFT('foobarbar', 5) "); + assure("LEFT('foobarbar', 5) failed!", row.getString(1).equals("fooba")); + } + + private void right(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "RIGHT('foobarbar', 4) "); + assure("RIGHT('foobarbar', 4) failed!", row.getString(1).equals("rbar")); + } + + private void chartest(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException + { + final XRow row = execute(xRowRes, "CHAR(ascii('t'),ascii('e'),ascii('s'),ascii('t')) "); + assure("CHAR(ascii('t'),ascii('e'),ascii('s'),ascii('t')) failed!", row.getString(1).equals("test")); + } +} diff --git a/connectivity/qa/complex/connectivity/hsqldb/DatabaseMetaData.java b/connectivity/qa/complex/connectivity/hsqldb/DatabaseMetaData.java new file mode 100755 index 000000000000..d5fa680f06c9 --- /dev/null +++ b/connectivity/qa/complex/connectivity/hsqldb/DatabaseMetaData.java @@ -0,0 +1,150 @@ +/* + * Class.java + * + * Created on June 23, 2003, 3:19 PM + */ + +/** + * + * @author oj93728 + */ +package complex.connectivity.hsqldb; +import complex.connectivity.HsqlDriverTest; +import java.sql.*; +import java.lang.reflect.Method; + + +public class DatabaseMetaData { + + private java.sql.DatabaseMetaData m_xMD; + private HsqlDriverTest m_TestCase; + + /** Creates a new instance of DatabaseMetaData */ + public DatabaseMetaData(HsqlDriverTest _testCase,java.sql.DatabaseMetaData _xmd) { + m_TestCase = _testCase; + m_xMD = _xmd; + } + + protected void assure(String _sText,boolean btest){ + m_TestCase.assurePublic(_sText,btest); + } + + protected void testMethod(String sName,Class[] params,Object[] objParams,int nCount){ + try { + System.out.println("test method " + sName); + + Method aGet = ((Object)m_xMD).getClass().getDeclaredMethod(sName, params); + if ( aGet != null ){ + ResultSet rs = (ResultSet)aGet.invoke(m_xMD, objParams); + ResultSetMetaData rsMD = rs.getMetaData(); + + assure( sName + " returns wrong column count" , rsMD.getColumnCount() == nCount); + } + else + assure( sName + " returns wrong column count" , false); + } catch( java.lang.NoSuchMethodException ex ) { + assure("Method " + sName + " could not be found!",false); + } catch( java.lang.IllegalAccessException ex ) { + assure("IllegalAccessException!",false); + } catch( SQLException ex ) { + assure("SQLException occurred: " + ex.getMessage() ,false); + } catch( java.lang.reflect.InvocationTargetException ex ) { + assure("IllegalAccessException!",false); + } finally { + + } + } + + public void test(){ + + // try { + + try{ + ResultSet rs = m_xMD.getTables(null,null,"TESTCASE",null); + while ( rs.next() ) + { + String catalog = rs.getString( 1 ); + if ( rs.wasNull() ) + catalog = null; + + String schema = rs.getString( 2 ); + if ( rs.wasNull() ) + schema = null; + + String table = rs.getString( 3 ); + String type = rs.getString( 4 ); + System.out.println("Catalog: " + catalog + " Schema: " + schema + " Table: " + table + " Type: " + type); + System.out.println("------------------ Columns ------------------"); + ResultSet rsColumns = m_xMD.getColumns(catalog,schema,table,"%"); + while ( rsColumns.next() ) + { + System.out.println("Column: " + rsColumns.getString( 4 ) + " Type: " + rsColumns.getInt( 5 ) + " TypeName: " + rsColumns.getString( 6 ) ); + } + + } + } catch(Exception e){ + + } + //testMethod("getTypeInfo", zclass,empty,17); +/* + Class[] zclass = new Class[]{}; + Object[] empty = new Object[]{}; + testMethod("getCatalogs", zclass,empty,1); + + testMethod("getSchemas", zclass,empty,2); + testMethod("getTableTypes", zclass,empty,1); + Class[] a4 = new Class[4]; + Object[] o4 = new Object[4]; + a4[0] = Class.forName("java.lang.Object"); + a4[1] = Class.forName("java.lang.String"); + a4[2] = Class.forName("java.lang.String"); + a4[3] = Class.forName("java.lang.String"); + + o4[0] = null; + o4[1] = null; + o4[2] = null; + o4[3] = "%"; + testMethod("getColumnPrivileges", a4,o4,7); + testMethod("getColumns", a4,o4,18); + testMethod("getProcedureColumns", a4,o4,13); + testMethod("getColumns", a4,o4,18); + + Class[] a3 = new Class[3]; + Object[] o3 = new Object[3]; + a3[0] = Class.forName("java.lang.Object"); + a3[1] = Class.forName("java.lang.String"); + a3[2] = Class.forName("java.lang.String"); + + o3[0] = null; + o3[1] = null; + o3[2] = "%"; + + testMethod("getExportedKeys", a3,o3,14); + testMethod("getImportedKeys", a3,o3,14); + testMethod("getPrimaryKeys", a3,o3,14); + testMethod("getProcedures", a3,o3,5); + testMethod("getTablePrivileges", a3,o3,6); + testMethod("getVersionColumns", a3,o3,7); + // testMethod("getCrossReference", a3,o3,14); + + } catch( java.lang.ClassNotFoundException ex) { + assure("ClassNotFoundException: " + ex.getMessage() ,false); + } + */ + } +/* + public com.sun.star.sdbc.XResultSet getCrossReference(Object obj, String str, String str2, Object obj3, String str4, String str5) { + } + public com.sun.star.sdbc.XResultSet getIndexInfo(Object obj, String str, String str2, boolean param, boolean param4) { + } + + public com.sun.star.sdbc.XResultSet getTables(Object obj, String str, String str2, String[] str3) { + } + + public com.sun.star.sdbc.XResultSet getUDTs(Object obj, String str, String str2, int[] values) { + } + + public com.sun.star.sdbc.XResultSet getBestRowIdentifier(Object obj, String str, String str2, int param, boolean param4) throws com.sun.star.sdbc.SQLException { + } + */ +} diff --git a/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java b/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java new file mode 100755 index 000000000000..6c4db8bdeee7 --- /dev/null +++ b/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java @@ -0,0 +1,609 @@ +/* Copyright (c) 2001-2004, The HSQL Development Group + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * + * Redistributions of source code must retain the above copyright notice, this + * list of conditions and the following disclaimer. + * + * Redistributions in binary form must reproduce the above copyright notice, + * this list of conditions and the following disclaimer in the documentation + * and/or other materials provided with the distribution. + * + * Neither the name of the HSQL Development Group nor the names of its + * contributors may be used to endorse or promote products derived from this + * software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" + * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE + * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE + * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, + * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, + * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, + * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + + +package complex.connectivity.hsqldb; + + + +import org.hsqldb.lib.StopWatch; + +import java.util.Random; +import com.sun.star.lang.*; +import com.sun.star.uno.UnoRuntime; +import com.sun.star.sdbc.*; + +/** + * Test large cached tables by setting up a cached table of 100000 records + * or more and a much smaller memory table with about 1/100th rows used. + * Populate both tables so that an indexed column of the cached table has a + * foreign key reference to the main table. + * + * This database can be used to demonstrate efficient queries to retrieve + * the data from the cached table. + * + * 1.7.1 insert timings for 100000 rows, cache scale 12: + * simple table, no extra index: 52 s + * with index on lastname only: 56 s + * with index on zip only: 211 s + * foreign key, referential_integrity true: 216 s + * + * The above have improved a lot in 1.7.2 + * + * This test now incorporates the defunct TestTextTables + * + * @author fredt@users + * @version 1.7.2 + * @since 1.7.0 + */ +public class TestCacheSize { + + // program can edit the *.properties file to set cache_size + protected boolean filedb = true; + + // shutdown performed mid operation - not for mem: or hsql: URL's + protected boolean shutdown = true; + + // fixed + protected String url = "sdbc:embedded:hsqldb"; + + // frequent reporting of progress + boolean reportProgress = false; + + XMultiServiceFactory servicefactory = null; + + // type of the big table {MEMORY | CACHED | TEXT} + String tableType = "CACHED"; + int cacheScale = 17; + int cacheSizeScale = 8; + + // script format {TEXT, BINARY, COMPRESSED} + String logType = "TEXT"; + int writeDelay = 60; + boolean indexZip = true; + boolean indexLastName = false; + boolean addForeignKey = false; + boolean refIntegrity = true; + + // speeds up inserts when tableType=="CACHED" + boolean createTempTable = false; + + // introduces fragmentation to the .data file during insert + boolean deleteWhileInsert = false; + int deleteWhileInsertInterval = 10000; + + // size of the tables used in test + int bigrows = 10000; + int smallrows = 0xfff; + + // if the extra table needs to be created and filled up + boolean multikeytable = false; + + // + String user; + String password; + XStatement sStatement; + XConnection cConnection; + XDataSource ds; + XDriver drv; + com.sun.star.beans.PropertyValue[] info; + + public TestCacheSize(XMultiServiceFactory _xmulti,com.sun.star.beans.PropertyValue[] _info,XDriver _drv){ + servicefactory = _xmulti; + drv = _drv; + info = _info; + } + + public void setURL(String _url){ + url = _url; + } + + public void setUp() { + + user = "sa"; + password = ""; + + try { + sStatement = null; + cConnection = null; + + //Class.forName("org.hsqldb.jdbcDriver"); + + if (filedb) { + + cConnection = drv.connect(url,info); + sStatement = cConnection.createStatement(); + + sStatement.execute("SET SCRIPTFORMAT " + logType); + sStatement.execute("SET LOGSIZE " + 0); + sStatement.execute("SHUTDOWN"); + cConnection.close(); +// props.setProperty("hsqldb.cache_scale", "" + cacheScale); +// props.setProperty("hsqldb.cache_size_scale", + //"" + cacheSizeScale); + } + } catch (Exception e) { + e.printStackTrace(); + System.out.println("TestSql.setUp() error: " + e.getMessage()); + } + } + + /** + * Fill up the cache + * + * + */ + public void testFillUp() { + + StopWatch sw = new StopWatch(); + String ddl1 = "DROP TABLE test IF EXISTS;" + + "DROP TABLE zip IF EXISTS;"; + String ddl2 = "CREATE CACHED TABLE zip( zip INT IDENTITY );"; + String ddl3 = "CREATE " + tableType + " TABLE test( id INT IDENTITY," + + " firstname VARCHAR, " + " lastname VARCHAR, " + + " zip INTEGER, " + " filler VARCHAR); "; + String ddl31 = "SET TABLE test SOURCE \"test.csv;cache_scale=" + + cacheScale + "\";"; + + // adding extra index will slow down inserts a bit + String ddl4 = "CREATE INDEX idx1 ON TEST (lastname);"; + + // adding this index will slow down inserts a lot + String ddl5 = "CREATE INDEX idx2 ON TEST (zip);"; + + // referential integrity checks will slow down inserts a bit + String ddl6 = + "ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip);"; + String ddl7 = "CREATE TEMP TABLE temptest( id INT," + + " firstname VARCHAR, " + " lastname VARCHAR, " + + " zip INTEGER, " + " filler VARCHAR); "; + String filler = + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ" + + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"; + String mddl1 = "DROP TABLE test2 IF EXISTS;"; + String mddl2 = "CREATE " + tableType + + " TABLE test2( id1 INT, id2 INT," + + " firstname VARCHAR, " + " lastname VARCHAR, " + + " zip INTEGER, " + " filler VARCHAR, " + + " PRIMARY KEY (id1,id2) ); "; + String mdd13 = "SET TABLE test2 SOURCE \"test2.csv;cache_scale=" + + cacheScale + "\";"; + + try { + System.out.println("Connecting"); + sw.zero(); + + cConnection = null; + sStatement = null; + cConnection = drv.connect(url,info); + + System.out.println("connected: " + sw.elapsedTime()); + sw.zero(); + + sStatement = cConnection.createStatement(); + + java.util.Random randomgen = new java.util.Random(); + + sStatement.execute("SET WRITE_DELAY " + writeDelay); + sStatement.execute(ddl1); + sStatement.execute(ddl2); + sStatement.execute(ddl3); + + if (tableType.equals("TEXT")) { + sStatement.execute(ddl31); + } + + System.out.println("test table with no index"); + + if (indexLastName) { + sStatement.execute(ddl4); + System.out.println("create index on lastname"); + } + + if (indexZip) { + sStatement.execute(ddl5); + System.out.println("create index on zip"); + } + + if (addForeignKey) { + sStatement.execute(ddl6); + System.out.println("add foreign key"); + } + + if (createTempTable) { + sStatement.execute(ddl7); + System.out.println("temp table"); + } + + if (multikeytable) { + sStatement.execute(mddl1); + sStatement.execute(mddl2); + + if (tableType.equals("TEXT")) { + sStatement.execute(mdd13); + } + + System.out.println("multi key table"); + } + +// sStatement.execute("CREATE INDEX idx3 ON tempTEST (zip);"); + System.out.println("Setup time: " + sw.elapsedTime()); + fillUpBigTable(filler, randomgen); + + if (multikeytable) { + fillUpMultiTable(filler, randomgen); + } + + sw.zero(); + + if (shutdown) { + sStatement.execute("SHUTDOWN"); + System.out.println("Shutdown Time: " + sw.elapsedTime()); + } + + cConnection.close(); + } catch (SQLException e) { + System.out.println(e.getMessage()); + } + } + + private void fillUpBigTable(String filler, + Random randomgen) throws SQLException { + + StopWatch sw = new StopWatch(); + int i; + + for (i = 0; i <= smallrows; i++) { + sStatement.execute("INSERT INTO zip VALUES(null);"); + } + + sStatement.execute("SET REFERENTIAL_INTEGRITY " + this.refIntegrity + + ";"); + + XPreparedStatement ps = cConnection.prepareStatement( + "INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)"); + + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + para.setString(1, "Julia"); + para.setString(2, "Clancy"); + + for (i = 0; i < bigrows; i++) { + para.setInt(3, randomgen.nextInt(smallrows)); + + long nextrandom = randomgen.nextLong(); + int randomlength = (int) nextrandom & 0x7f; + + if (randomlength > filler.length()) { + randomlength = filler.length(); + } + + String varfiller = filler.substring(0, randomlength); + + para.setString(4, nextrandom + varfiller); + ps.execute(); + + if (reportProgress && (i + 1) % 10000 == 0) { + System.out.println("Insert " + (i + 1) + " : " + + sw.elapsedTime()); + } + + // delete and add 4000 rows to introduce fragmentation + if (deleteWhileInsert && i != 0 + && i % deleteWhileInsertInterval == 0) { + sStatement.execute("CALL IDENTITY();"); + + XMultipleResults mrs = (XMultipleResults)UnoRuntime.queryInterface(XMultipleResults.class,sStatement); + XResultSet rs = mrs.getResultSet(); + + rs.next(); + + XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs); + int lastId = row.getInt(1); + + sStatement.execute( + "SELECT * INTO TEMP tempt FROM test WHERE id > " + + (lastId - 4000) + " ;"); + sStatement.execute("DELETE FROM test WHERE id > " + + (lastId - 4000) + " ;"); + sStatement.execute("INSERT INTO test SELECT * FROM tempt;"); + sStatement.execute("DROP TABLE tempt;"); + } + } + +// sStatement.execute("INSERT INTO test SELECT * FROM temptest;"); +// sStatement.execute("DROP TABLE temptest;"); +// sStatement.execute(ddl7); + System.out.println("Total insert: " + i); + System.out.println("Insert time: " + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + } + + private void fillUpMultiTable(String filler, + Random randomgen) throws SQLException { + + StopWatch sw = new StopWatch(); + int i; + XPreparedStatement ps = cConnection.prepareStatement( + "INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)"); + + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + para.setString(3, "Julia"); + para.setString(4, "Clancy"); + + int id1 = 0; + + for (i = 0; i < bigrows; i++) { + int id2 = randomgen.nextInt(Integer.MAX_VALUE); + + if (i % 1000 == 0) { + id1 = randomgen.nextInt(Integer.MAX_VALUE); + } + + para.setInt(1, id1); + para.setInt(2, id2); + para.setInt(5, randomgen.nextInt(smallrows)); + + long nextrandom = randomgen.nextLong(); + int randomlength = (int) nextrandom & 0x7f; + + if (randomlength > filler.length()) { + randomlength = filler.length(); + } + + String varfiller = filler.substring(0, randomlength); + + para.setString(6, nextrandom + varfiller); + + try { + ps.execute(); + } catch (SQLException e) { + e.printStackTrace(); + } + + if (reportProgress && (i + 1) % 10000 == 0) { + System.out.println("Insert " + (i + 1) + " : " + + sw.elapsedTime()); + } + } + + System.out.println("Multi Key Total insert: " + i); + System.out.println("Insert time: " + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + } + + public void tearDown() {} + + public void checkResults() { + + try { + StopWatch sw = new StopWatch(); + XResultSet rs; + + cConnection = drv.connect(url,info); + + System.out.println("Reopened database: " + sw.elapsedTime()); + sw.zero(); + + sStatement = cConnection.createStatement(); + + sStatement.execute("SET WRITE_DELAY " + writeDelay); + + // the tests use different indexes + // use primary index + sStatement.execute("SELECT count(*) from TEST"); + + XMultipleResults mrs = (XMultipleResults)UnoRuntime.queryInterface(XMultipleResults.class,sStatement); + rs = mrs.getResultSet(); + XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs); + + rs.next(); + System.out.println("Row Count: " + row.getInt(1)); + System.out.println("Time to count: " + sw.elapsedTime()); + + // use index on zip + sw.zero(); + sStatement.execute("SELECT count(*) from TEST where zip > -1"); + + rs = mrs.getResultSet(); + + rs.next(); + System.out.println("Row Count: " + row.getInt(1)); + System.out.println("Time to count: " + sw.elapsedTime()); + checkSelects(); + checkUpdates(); + checkSelects(); + sw.zero(); + sStatement.execute("SELECT count(*) from TEST where zip > -1"); + + rs = mrs.getResultSet(); + + rs.next(); + System.out.println("Row Count: " + row.getInt(1)); + System.out.println("Time to count: " + sw.elapsedTime()); + sw.zero(); + + if (shutdown) { + sStatement.execute("SHUTDOWN"); + System.out.println("Shutdown Time: " + sw.elapsedTime()); + } + + cConnection.close(); + System.out.println("Closed database: " + sw.elapsedTime()); + } catch (SQLException e) { + e.printStackTrace(); + } + } + + private void checkSelects() { + + StopWatch sw = new StopWatch(); + java.util.Random randomgen = new java.util.Random(); + int i = 0; + boolean slow = false; + + try { + XPreparedStatement ps = cConnection.prepareStatement( + "SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?"); + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + + for (; i < bigrows; i++) { + para.setInt(1, randomgen.nextInt(smallrows)); + ps.execute(); + + if ((i + 1) == 100 && sw.elapsedTime() > 5000) { + slow = true; + } + + if (reportProgress && (i + 1) % 10000 == 0 + || (slow && (i + 1) % 100 == 0)) { + System.out.println("Select " + (i + 1) + " : " + + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + } + } + } catch (SQLException e) { + e.printStackTrace(); + } + + System.out.println("Select random zip " + i + " rows : " + + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + sw.zero(); + + try { + XPreparedStatement ps = cConnection.prepareStatement( + "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?"); + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + + for (i = 0; i < bigrows; i++) { + para.setInt(1, randomgen.nextInt(bigrows - 1)); + ps.execute(); + + if (reportProgress && (i + 1) % 10000 == 0 + || (slow && (i + 1) % 100 == 0)) { + System.out.println("Select " + (i + 1) + " : " + + (sw.elapsedTime() + 1)); + } + } + } catch (SQLException e) { + e.printStackTrace(); + } + + System.out.println("Select random id " + i + " rows : " + + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + sw.zero(); + + try { + XPreparedStatement ps = cConnection.prepareStatement( + "SELECT zip FROM zip WHERE zip = ?"); + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + + for (i = 0; i < bigrows; i++) { + para.setInt(1, randomgen.nextInt(smallrows - 1)); + ps.execute(); + + if (reportProgress && (i + 1) % 10000 == 0 + || (slow && (i + 1) % 100 == 0)) { + System.out.println("Select " + (i + 1) + " : " + + (sw.elapsedTime() + 1)); + } + } + } catch (SQLException e) { + e.printStackTrace(); + } + + System.out.println("Select random zip from zip table " + i + + " rows : " + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + } + + private void checkUpdates() { + + StopWatch sw = new StopWatch(); + java.util.Random randomgen = new java.util.Random(); + int i = 0; + boolean slow = false; + int count = 0; + + try { + XPreparedStatement ps = cConnection.prepareStatement( + "UPDATE test SET filler = filler || zip WHERE zip = ?"); + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + + for (; i < smallrows; i++) { + int random = randomgen.nextInt(smallrows - 1); + + para.setInt(1, random); + + count += ps.executeUpdate(); + + if (reportProgress && count % 10000 < 20) { + System.out.println("Update " + count + " : " + + (sw.elapsedTime() + 1)); + } + } + } catch (SQLException e) { + e.printStackTrace(); + } + + System.out.println("Update with random zip " + i + + " UPDATE commands, " + count + " rows : " + + sw.elapsedTime() + " rps: " + + (count * 1000 / (sw.elapsedTime() + 1))); + sw.zero(); + + try { + XPreparedStatement ps = cConnection.prepareStatement( + "UPDATE test SET zip = zip + 1 WHERE id = ?"); + XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps); + + for (i = 0; i < bigrows; i++) { + int random = randomgen.nextInt(bigrows - 1); + + para.setInt(1, random); + ps.execute(); + + if (reportProgress && (i + 1) % 10000 == 0 + || (slow && (i + 1) % 100 == 0)) { + System.out.println("Update " + (i + 1) + " : " + + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + } + } + } catch (SQLException e) { + e.printStackTrace(); + } + + System.out.println("Update with random id " + i + " rows : " + + sw.elapsedTime() + " rps: " + + (i * 1000 / (sw.elapsedTime() + 1))); + } +} |