dbaccess: Enable hsql migration by default

Also make Firebird driver not experimental anymore.

With hsql migration enabled, the hsqldb related unit tests can be
reused, because the underlying DBMS is transparent. To achieve that, I
added firebird_sdbc component to hsqldb CppunitTest_* files.

This commit also contains fixes for upcoming bugs while migrating from
hsqldb to firebird, shown by hsqldb related unit tests:

- null values: in case of null values, the setNull method should be used
  instead of nothing. (malformed string otherwise)

Remove DBACCESS_HSQL_MIGRATION environment variable, since migration is
default from now on.

JunitTest_dbaccess_complex was based on HSQLDB. This commit replaces
"if exists" hsql specific solution with firebird specific. Also disable
test for queries with named parameters.

Change-Id: Ieb68f5ad3a11389599c4f268ea4df82a83643b82
Reviewed-on: https://gerrit.libreoffice.org/52008
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Tamás Bunth <btomi96@gmail.com>

6 files changed, 219 insertions, 13 deletions

diff --git a/connectivity/Jar_ConnectivityTools.mk b/connectivity/Jar_ConnectivityTools.mk
index fc8a861f2b8a..3920a49bac12 100644
--- a/connectivity/Jar_ConnectivityTools.mk
+++ b/connectivity/Jar_ConnectivityTools.mk
@@ -32,6 +32,7 @@ $(eval $(call gb_Jar_add_sourcefiles,ConnectivityTools,\
 	connectivity/qa/connectivity/tools/DatabaseAccess \
 	connectivity/qa/connectivity/tools/DataSource \
 	connectivity/qa/connectivity/tools/DbaseDatabase \
+	connectivity/qa/connectivity/tools/FirebirdDatabase \
 	connectivity/qa/connectivity/tools/FlatFileDatabase \
 	connectivity/qa/connectivity/tools/HsqlColumnDescriptor \
 	connectivity/qa/connectivity/tools/HsqlDatabase \
diff --git a/connectivity/qa/connectivity/tools/CRMDatabase.java b/connectivity/qa/connectivity/tools/CRMDatabase.java
index 3d33926280c3..a4ee92c3a057 100644
--- a/connectivity/qa/connectivity/tools/CRMDatabase.java
+++ b/connectivity/qa/connectivity/tools/CRMDatabase.java
@@ -43,7 +43,7 @@ public class CRMDatabase
     private static final String INTEGER = "INTEGER";
     private static final String VARCHAR50 = "VARCHAR(50)";
     private final XMultiServiceFactory        m_orb;
-    private final HsqlDatabase                m_database;
+    private final FirebirdDatabase            m_database;
     private final Connection                  m_connection;
 
     /** constructs the CRM database
@@ -52,7 +52,7 @@ public class CRMDatabase
     {
         m_orb = _orb;
 
-        m_database = new HsqlDatabase( m_orb );
+        m_database = new FirebirdDatabase( m_orb );
 
         if ( _withUI )
         {
@@ -81,14 +81,14 @@ public class CRMDatabase
     {
         m_orb = _orb;
 
-        m_database = new HsqlDatabase( m_orb, _existingDocumentURL );
+        m_database = new FirebirdDatabase( m_orb, _existingDocumentURL );
         m_connection = m_database.defaultConnection();
     }
 
 
     /** returns the database document underlying the CRM database
      */
-    public final HsqlDatabase getDatabase()
+    public final FirebirdDatabase getDatabase()
     {
         return m_database;
     }
@@ -141,7 +141,7 @@ public class CRMDatabase
                 new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ),
                 new HsqlColumnDescriptor( "Name",VARCHAR50),
                 new HsqlColumnDescriptor( "Description", "VARCHAR(1024)" ),
-                new HsqlColumnDescriptor( "Image", "LONGVARBINARY" ) } );
+                new HsqlColumnDescriptor( "Image", "BLOB SUB_TYPE -9546" ) } );
         m_database.createTable( table, true );
 
         m_database.executeSQL( "INSERT INTO \"categories\" ( \"ID\", \"Name\" ) VALUES ( 1, 'Food' )" );
@@ -166,7 +166,7 @@ public class CRMDatabase
                 new HsqlColumnDescriptor( "Address",VARCHAR50),
                 new HsqlColumnDescriptor( "City",VARCHAR50),
                 new HsqlColumnDescriptor( "Postal",VARCHAR50),
-                new HsqlColumnDescriptor( "Comment","LONGVARCHAR")} );
+                new HsqlColumnDescriptor( "Comment","BLOB SUB_TYPE 1")} );
         m_database.createTable( table, true );
 
         m_database.executeSQL( "INSERT INTO \"customers\" VALUES(1,'Food, Inc.','Down Under','Melbourne','509','Preferred') " );
@@ -182,8 +182,8 @@ public class CRMDatabase
                 new HsqlColumnDescriptor( "ShipDate", "DATE" ) } );
         m_database.createTable( table, true );
 
-        m_database.executeSQL( "INSERT INTO \"orders\" (\"ID\", \"CustomerID\", \"OrderDate\") VALUES(1, 1, {D '2009-01-01'})" );
-        m_database.executeSQL( "INSERT INTO \"orders\" VALUES(2, 2, {D '2009-01-01'}, {D '2009-01-23'})" );
+        m_database.executeSQL( "INSERT INTO \"orders\" (\"ID\", \"CustomerID\", \"OrderDate\") VALUES(1, 1, DATE '2009-01-01')" );
+        m_database.executeSQL( "INSERT INTO \"orders\" VALUES(2, 2, DATE '2009-01-01', DATE '2009-01-23')" );
 
         table = new HsqlTableDescriptor( "orders_details",
             new HsqlColumnDescriptor[] {
diff --git a/connectivity/qa/connectivity/tools/FirebirdDatabase.java b/connectivity/qa/connectivity/tools/FirebirdDatabase.java
new file mode 100644
index 000000000000..46ad4709a812
--- /dev/null
+++ b/connectivity/qa/connectivity/tools/FirebirdDatabase.java
@@ -0,0 +1,205 @@
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ *
+ * This file incorporates work covered by the following license notice:
+ *
+ *   Licensed to the Apache Software Foundation (ASF) under one or more
+ *   contributor license agreements. See the NOTICE file distributed
+ *   with this work for additional information regarding copyright
+ *   ownership. The ASF licenses this file to you under the Apache
+ *   License, Version 2.0 (the "License"); you may not use this file
+ *   except in compliance with the License. You may obtain a copy of
+ *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
+ */
+package connectivity.tools;
+
+import com.sun.star.beans.PropertyValue;
+import com.sun.star.beans.PropertyState;
+import com.sun.star.beans.XPropertySet;
+import com.sun.star.container.ElementExistException;
+import com.sun.star.frame.XStorable;
+import com.sun.star.lang.XMultiServiceFactory;
+import com.sun.star.sdb.XOfficeDatabaseDocument;
+import com.sun.star.sdbc.SQLException;
+import com.sun.star.sdbcx.XAppend;
+import com.sun.star.sdbcx.XTablesSupplier;
+import com.sun.star.uno.UnoRuntime;
+
+import helper.URLHelper;
+import java.io.File;
+import java.util.HashMap;
+import java.util.Map;
+import org.junit.Assert;
+
+public class FirebirdDatabase extends AbstractDatabase
+{
+
+
+    public FirebirdDatabase(final XMultiServiceFactory orb) throws Exception
+    {
+        super(orb);
+        createDBDocument();
+    }
+
+
+    public FirebirdDatabase(final XMultiServiceFactory orb, final String _existingDocumentURL) throws Exception
+    {
+        super(orb, _existingDocumentURL);
+    }
+
+    /** creates an empty database document in a temporary location
+     */
+    private void createDBDocument() throws Exception
+    {
+        Assert.assertNull(m_documentFile);
+        m_documentFile = File.createTempFile("testdb", ".odb");
+        if ( m_documentFile.exists() )
+            m_documentFile.delete();
+        m_databaseDocumentFile = URLHelper.getFileURLFromSystemPath(m_documentFile);
+
+        m_databaseDocument = UnoRuntime.queryInterface(
+                XOfficeDatabaseDocument.class, m_orb.createInstance("com.sun.star.sdb.OfficeDatabaseDocument"));
+        m_dataSource = new DataSource(m_databaseDocument.getDataSource());
+
+        final XPropertySet dsProperties = UnoRuntime.queryInterface(XPropertySet.class, m_databaseDocument.getDataSource());
+        dsProperties.setPropertyValue("URL", "sdbc:embedded:hsqldb");
+
+        final XStorable storable = UnoRuntime.queryInterface(XStorable.class, m_databaseDocument);
+        storable.storeAsURL( m_databaseDocumentFile, new PropertyValue[]
+            {   new PropertyValue( "PickListEntry", 0, false, PropertyState.DIRECT_VALUE )
+            } );
+    }
+
+    @Override protected final void delete() {
+        if (m_documentFile != null) {
+            boolean ok = m_documentFile.delete();
+            //TODO: fails on Windows: Assert.assertTrue("delete " + m_documentFile.getPath(), ok);
+        }
+    }
+
+    /** drops the table with a given name
+
+    @param _name
+    the name of the table to drop
+     */
+    private void dropTable(final String _name) throws SQLException
+    {
+        final StringBuffer dropStatement = new StringBuffer("EXECUTE BLOCK AS BEGIN"
+               + " if (exists(select 1 from rdb$relations where rdb$relation_name ='");
+        dropStatement.append(_name);
+        dropStatement.append("')) then execute statement 'DROP TABLE ");
+        dropStatement.append(_name);
+        dropStatement.append("';  END");
+        executeSQL(dropStatement.toString());
+    }
+
+    public void createTable(final HsqlTableDescriptor _tableDesc, final boolean _dropIfExists) throws SQLException
+    {
+        if (_dropIfExists)
+        {
+            dropTable(_tableDesc.getName());
+        }
+        createTable(_tableDesc);
+    }
+
+    /** creates a table
+     */
+    public void createTable(final HsqlTableDescriptor _tableDesc) throws SQLException
+    {
+        StringBuffer createStatement = new StringBuffer("CREATE TABLE \"");
+        createStatement.append(_tableDesc.getName());
+        createStatement.append("\" ( ");
+
+        String primaryKeyList = "";
+
+        final HashMap<String, String> foreignKeys = new HashMap<String, String>();
+        final HashMap<String, String> foreignKeyRefs = new HashMap<String, String>();
+
+        final HsqlColumnDescriptor[] columns = _tableDesc.getColumns();
+        for (int i = 0; i < columns.length; ++i)
+        {
+            if (i > 0)
+            {
+                createStatement.append(", ");
+            }
+
+            createStatement.append("\"").append(columns[i].getName());
+            createStatement.append("\" ").append(columns[i].getTypeName());
+
+            if (columns[i].isRequired())
+            {
+                createStatement.append(" NOT NULL");
+            }
+
+            if (columns[i].isPrimaryKey())
+            {
+                if (primaryKeyList.length() > 0)
+                {
+                    primaryKeyList += ", ";
+                }
+                primaryKeyList += "\"" + columns[i].getName() + "\"";
+            }
+
+            if (columns[i].isForeignKey())
+            {
+                final String foreignTable = columns[i].getForeignTable();
+
+                String foreignKeysForTable = foreignKeys.containsKey(foreignTable) ? foreignKeys.get(foreignTable) : "";
+                if (foreignKeysForTable.length() > 0)
+                {
+                    foreignKeysForTable += ", ";
+                }
+                foreignKeysForTable += "\"" + columns[i].getName() + "\"";
+                foreignKeys.put(foreignTable, foreignKeysForTable);
+
+                final StringBuffer foreignKeyRefsForTable = new StringBuffer(foreignKeyRefs.containsKey(foreignTable) ? foreignKeyRefs.get(foreignTable) : "");
+                if (foreignKeyRefsForTable.length() > 0)
+                {
+                    foreignKeyRefsForTable.append(", ");
+                }
+                foreignKeyRefsForTable.append("\"").append(columns[i].getForeignColumn()).append("\"");
+                foreignKeyRefs.put(foreignTable, foreignKeyRefsForTable.toString());
+            }
+        }
+
+        if (primaryKeyList.length() > 0)
+        {
+            createStatement.append(", PRIMARY KEY (");
+            createStatement.append(primaryKeyList);
+            createStatement.append(')');
+        }
+
+        for (Map.Entry<String, String> foreignKey : foreignKeys.entrySet())
+        {
+            final String foreignTable = foreignKey.getKey();
+
+            createStatement.append(", FOREIGN KEY (");
+            createStatement.append(foreignKey.getValue());
+            createStatement.append(") REFERENCES \"");
+            createStatement.append(foreignTable);
+            createStatement.append("\"(");
+            createStatement.append(foreignKeyRefs.get(foreignTable));
+            createStatement.append(')');
+        }
+
+        createStatement.append(')');
+
+        executeSQL(createStatement.toString());
+    }
+
+    /** creates a table in the database. using the SDBCX-API
+     */
+    public void createTableInSDBCX(final HsqlTableDescriptor _tableDesc) throws SQLException, ElementExistException
+    {
+        final XPropertySet sdbcxDescriptor = _tableDesc.createSdbcxDescriptor(defaultConnection());
+        final XTablesSupplier suppTables = UnoRuntime.queryInterface( XTablesSupplier.class, defaultConnection().getXConnection() );
+        final XAppend appendTable = UnoRuntime.queryInterface( XAppend.class, suppTables.getTables() );
+        appendTable.appendByDescriptor(sdbcxDescriptor);
+    }
+
+    private File m_documentFile;
+}
diff --git a/connectivity/source/drivers/firebird/Driver.cxx b/connectivity/source/drivers/firebird/Driver.cxx
index c93b4876875d..c7561b51e845 100644
--- a/connectivity/source/drivers/firebird/Driver.cxx
+++ b/connectivity/source/drivers/firebird/Driver.cxx
@@ -29,7 +29,6 @@
 #include <osl/file.hxx>
 #include <osl/process.h>
 #include <rtl/bootstrap.hxx>
-#include <svtools/miscopt.hxx>
 #include <unotools/localfilehelper.hxx>
 
 using namespace com::sun::star;
@@ -191,10 +190,7 @@ Reference< XConnection > SAL_CALL FirebirdDriver::connect(
 
 sal_Bool SAL_CALL FirebirdDriver::acceptsURL( const OUString& url )
 {
-    SvtMiscOptions aMiscOptions;
-
-    return aMiscOptions.IsExperimentalMode() &&
-        (url == "sdbc:embedded:firebird" || url.startsWith("sdbc:firebird:"));
+    return (url == "sdbc:embedded:firebird" || url.startsWith("sdbc:firebird:"));
 }
 
 Sequence< DriverPropertyInfo > SAL_CALL FirebirdDriver::getPropertyInfo(
diff --git a/connectivity/source/drivers/firebird/PreparedStatement.cxx b/connectivity/source/drivers/firebird/PreparedStatement.cxx
index 2f48c63c746e..0a43d5c93851 100644
--- a/connectivity/source/drivers/firebird/PreparedStatement.cxx
+++ b/connectivity/source/drivers/firebird/PreparedStatement.cxx
@@ -834,6 +834,7 @@ void SAL_CALL OPreparedStatement::setBytes(sal_Int32 nParameterIndex,
     }
     else if( dType == SQL_VARYING )
     {
+            setParameterNull(nParameterIndex, false);
             const sal_Int32 nMaxSize = 0xFFFF;
             Sequence<sal_Int8> xBytesCopy(xBytes);
             // First 2 bytes indicate string size
@@ -848,6 +849,7 @@ void SAL_CALL OPreparedStatement::setBytes(sal_Int32 nParameterIndex,
     }
     else if( dType == SQL_TEXT )
     {
+            setParameterNull(nParameterIndex, false);
             memcpy(pVar->sqldata, xBytes.getConstArray(), xBytes.getLength() );
             // Fill remainder with spaces
             memset(pVar->sqldata + xBytes.getLength(), 0, pVar->sqllen - xBytes.getLength());
diff --git a/connectivity/source/drivers/firebird/StatementCommonBase.cxx b/connectivity/source/drivers/firebird/StatementCommonBase.cxx
index dafc9e3d1972..157070c7fdb0 100644
--- a/connectivity/source/drivers/firebird/StatementCommonBase.cxx
+++ b/connectivity/source/drivers/firebird/StatementCommonBase.cxx
@@ -433,6 +433,8 @@ sal_Int32 OStatementCommonBase::getStatementChangeCount()
         case isc_info_sql_stmt_delete:
             aDesiredInfoType = isc_info_req_delete_count;
             break;
+        case isc_info_sql_stmt_exec_procedure:
+            return 0; // cannot determine
         default:
             throw SQLException(); // TODO: better error message?
     }