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>

9 files changed, 79 insertions, 50 deletions

diff --git a/dbaccess/source/filter/hsqldb/fbcreateparser.cxx b/dbaccess/source/filter/hsqldb/fbcreateparser.cxx
index a20321dc8af5..764ccaca2d69 100644
--- a/dbaccess/source/filter/hsqldb/fbcreateparser.cxx
+++ b/dbaccess/source/filter/hsqldb/fbcreateparser.cxx
@@ -144,7 +144,7 @@ OUString FbCreateStmtParser::compose() const
 
         // TODO autoincremental default value with "START WITH"
         if (columnIter->isAutoIncremental())
-            lcl_appendWithSpace(sSql, "GENERATED BY DEFAULT AS IDENTITY");
+            lcl_appendWithSpace(sSql, "GENERATED BY DEFAULT AS IDENTITY (START WITH 0)");
         else if (!columnIter->isNullable())
             lcl_appendWithSpace(sSql, "NOT NULL");
 
diff --git a/dbaccess/source/filter/hsqldb/hsqlbinarynode.cxx b/dbaccess/source/filter/hsqldb/hsqlbinarynode.cxx
index 8a8cf1a9a20b..2511ef5ec3ea 100644
--- a/dbaccess/source/filter/hsqldb/hsqlbinarynode.cxx
+++ b/dbaccess/source/filter/hsqldb/hsqlbinarynode.cxx
@@ -25,7 +25,7 @@
 
 namespace dbahsql
 {
-using ColumnTypeVector = std::vector<sal_Int32>;
+using ColumnTypeVector = std::vector<ColumnDefinition>;
 
 HsqlBinaryNode::HsqlBinaryNode(sal_Int32 nPos)
     : m_nPos(nPos)
diff --git a/dbaccess/source/filter/hsqldb/hsqlbinarynode.hxx b/dbaccess/source/filter/hsqldb/hsqlbinarynode.hxx
index 7c3631a6c8b2..71e0fd799171 100644
--- a/dbaccess/source/filter/hsqldb/hsqlbinarynode.hxx
+++ b/dbaccess/source/filter/hsqldb/hsqlbinarynode.hxx
@@ -14,6 +14,7 @@
 #include <cppuhelper/implbase.hxx>
 
 #include "rowinputbinary.hxx"
+#include "columndef.hxx"
 
 namespace dbahsql
 {
@@ -30,7 +31,7 @@ public:
     sal_Int32 getLeft() const;
     sal_Int32 getRight() const;
     std::vector<css::uno::Any> readRow(HsqlRowInputStream& rInput,
-                                       const std::vector<sal_Int32>& aColTypes);
+                                       const std::vector<ColumnDefinition>& aColTypes);
 };
 }
 
diff --git a/dbaccess/source/filter/hsqldb/hsqlimport.cxx b/dbaccess/source/filter/hsqldb/hsqlimport.cxx
index 0775fa580063..b7f854777011 100644
--- a/dbaccess/source/filter/hsqldb/hsqlimport.cxx
+++ b/dbaccess/source/filter/hsqldb/hsqlimport.cxx
@@ -40,7 +40,7 @@ using namespace css::io;
 using namespace css::uno;
 using namespace css::sdbc;
 
-using ColumnTypeVector = std::vector<sal_Int32>;
+using ColumnTypeVector = std::vector<dbahsql::ColumnDefinition>;
 using RowVector = std::vector<Any>;
 using IndexVector = std::vector<sal_Int32>;
 
@@ -48,9 +48,13 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                    const ColumnTypeVector& rColTypes)
 {
     assert(row.size() == rColTypes.size());
+    size_t nColIndex = 0;
     for (size_t i = 0; i < rColTypes.size(); ++i)
     {
-        switch (rColTypes.at(i))
+        if (!row.at(i).hasValue())
+            xParam->setNull(i + 1, rColTypes.at(i).getDataType());
+
+        switch (rColTypes.at(i).getDataType())
         {
             case DataType::CHAR:
             case DataType::VARCHAR:
@@ -59,7 +63,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 OUString sVal;
                 if (row.at(i) >>= sVal)
                 {
-                    xParam->setString(i + 1, sVal);
+                    xParam->setString(nColIndex + 1, sVal);
                 }
             }
             break;
@@ -69,7 +73,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 sal_Int16 nVal;
                 if (row.at(i) >>= nVal)
                 {
-                    xParam->setShort(i + 1, nVal);
+                    xParam->setShort(nColIndex + 1, nVal);
                 }
             }
             break;
@@ -78,7 +82,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 sal_Int32 nVal;
                 if (row.at(i) >>= nVal)
                 {
-                    xParam->setInt(i + 1, nVal);
+                    xParam->setInt(nColIndex + 1, nVal);
                 }
             }
             break;
@@ -87,7 +91,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 sal_Int64 nVal;
                 if (row.at(i) >>= nVal)
                 {
-                    xParam->setLong(i + 1, nVal);
+                    xParam->setLong(nColIndex + 1, nVal);
                 }
             }
             break;
@@ -98,7 +102,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 double nVal;
                 if (row.at(i) >>= nVal)
                 {
-                    xParam->setDouble(i + 1, nVal);
+                    xParam->setDouble(nColIndex + 1, nVal);
                 }
             }
             break;
@@ -110,7 +114,8 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 {
                     sal_Int32 nScale = 0;
                     if (aNumeric[1] >>= nScale)
-                        xParam->setObjectWithInfo(i + 1, aNumeric[0], rColTypes.at(i), nScale);
+                        xParam->setObjectWithInfo(nColIndex + 1, aNumeric[0],
+                                                  rColTypes.at(i).getDataType(), nScale);
                 }
             }
             break;
@@ -119,7 +124,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 css::util::Date date;
                 if (row.at(i) >>= date)
                 {
-                    xParam->setDate(i + 1, date);
+                    xParam->setDate(nColIndex + 1, date);
                 }
             }
             break;
@@ -128,7 +133,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 css::util::Time time;
                 if (row.at(i) >>= time)
                 {
-                    xParam->setTime(i + 1, time);
+                    xParam->setTime(nColIndex, time);
                 }
             }
             break;
@@ -137,7 +142,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 css::util::DateTime dateTime;
                 if (row.at(i) >>= dateTime)
                 {
-                    xParam->setTimestamp(i + 1, dateTime);
+                    xParam->setTimestamp(nColIndex + 1, dateTime);
                 }
             }
             break;
@@ -145,7 +150,7 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
             {
                 bool bVal = false;
                 if (row.at(i) >>= bVal)
-                    xParam->setBoolean(i + 1, bVal);
+                    xParam->setBoolean(nColIndex + 1, bVal);
             }
             break;
             case DataType::OTHER:
@@ -158,27 +163,41 @@ void lcl_setParams(const RowVector& row, Reference<XParameters>& xParam,
                 Sequence<sal_Int8> nVal;
                 if (row.at(i) >>= nVal)
                 {
-                    xParam->setBytes(i + 1, nVal);
+                    xParam->setBytes(nColIndex + 1, nVal);
                 }
                 break;
             }
             default:
                 throw WrongFormatException();
         }
+        ++nColIndex;
     }
 }
 
-OUString lcl_createInsertStatement(const OUString& sTableName, sal_Int32 nColumnCount)
+OUString lcl_createInsertStatement(const OUString& sTableName, const ColumnTypeVector& rColTypes)
 {
-    assert(nColumnCount > 0);
+    assert(rColTypes.size() > 0);
     OUStringBuffer sql("INSERT INTO ");
     sql.append(sTableName);
+    sql.append(" (");
+
+    // column names
+    for (size_t i = 0; i < rColTypes.size(); ++i)
+    {
+        sql.append(rColTypes.at(i).getName());
+        if (i < rColTypes.size() - 1)
+            sql.append(", ");
+    }
+    sql.append(")");
+
     sql.append(" VALUES (");
-    for (int i = 0; i < nColumnCount - 1; ++i)
+    for (size_t i = 0; i < rColTypes.size(); ++i)
     {
-        sql.append("?,");
+        sql.append("?");
+        if (i < rColTypes.size() - 1)
+            sql.append(", ");
     }
-    sql.append("?)");
+    sql.append(")");
     return sql.makeStringAndClear();
 }
 
@@ -198,7 +217,7 @@ HsqlImporter::HsqlImporter(Reference<XConnection>& rConnection, const Reference<
 void HsqlImporter::insertRow(const RowVector& xRows, const OUString& sTableName,
                              const ColumnTypeVector& rColTypes)
 {
-    OUString sStatement = lcl_createInsertStatement(sTableName, xRows.size());
+    OUString sStatement = lcl_createInsertStatement(sTableName, rColTypes);
     Reference<XPreparedStatement> xStatement = m_rConnection->prepareStatement(sStatement);
 
     Reference<XParameters> xParameter(xStatement, UNO_QUERY);
@@ -213,15 +232,15 @@ void HsqlImporter::processTree(HsqlBinaryNode& rNode, HsqlRowInputStream& rStrea
                                const ColumnTypeVector& rColTypes, const OUString& sTableName)
 {
     rNode.readChildren(rStream);
-    std::vector<Any> row = rNode.readRow(rStream, rColTypes);
-    insertRow(row, sTableName, rColTypes);
-
     sal_Int32 nNext = rNode.getLeft();
     if (nNext > 0)
     {
         HsqlBinaryNode aLeft{ nNext };
         processTree(aLeft, rStream, rColTypes, sTableName);
     }
+    std::vector<Any> row = rNode.readRow(rStream, rColTypes);
+    insertRow(row, sTableName, rColTypes);
+
     nNext = rNode.getRight();
     if (nNext > 0)
     {
@@ -241,7 +260,7 @@ void HsqlImporter::processTree(HsqlBinaryNode& rNode, HsqlRowInputStream& rStrea
  * Left/Right/Parent: File position of the Left/Right/Parent child
  */
 void HsqlImporter::parseTableRows(const IndexVector& rIndexes,
-                                  const std::vector<sal_Int32>& rColTypes,
+                                  const std::vector<ColumnDefinition>& rColTypes,
                                   const OUString& sTableName)
 {
     constexpr char BINARY_FILENAME[] = "data";
@@ -258,12 +277,10 @@ void HsqlImporter::parseTableRows(const IndexVector& rIndexes,
     HsqlRowInputStream rowInput;
     Reference<XInputStream> xInput = xStream->getInputStream();
     rowInput.setInputStream(xInput);
-    for (const auto& index : rIndexes)
-    {
-        if (index <= 0)
-            break;
 
-        HsqlBinaryNode aNode{ index };
+    for (const auto& rIndex : rIndexes)
+    {
+        HsqlBinaryNode aNode{ rIndex };
         processTree(aNode, rowInput, rColTypes, sTableName);
     }
     xInput->closeInput();
@@ -276,6 +293,12 @@ void HsqlImporter::importHsqlDatabase()
     SchemaParser parser(m_xStorage);
     SqlStatementVector statements = parser.parseSchema();
 
+    if (statements.size() < 1)
+    {
+        SAL_WARN("dbaccess", "dbashql: there is nothing to import");
+        return; // there is nothing to import
+    }
+
     // schema
     for (auto& sSql : statements)
     {
@@ -286,7 +309,7 @@ void HsqlImporter::importHsqlDatabase()
     // data
     for (const auto& tableIndex : parser.getTableIndexes())
     {
-        std::vector<sal_Int32> aColTypes = parser.getTableColumnTypes(tableIndex.first);
+        std::vector<ColumnDefinition> aColTypes = parser.getTableColumnTypes(tableIndex.first);
         parseTableRows(tableIndex.second, aColTypes, tableIndex.first);
     }
 }
diff --git a/dbaccess/source/filter/hsqldb/hsqlimport.hxx b/dbaccess/source/filter/hsqldb/hsqlimport.hxx
index b40f73079a7e..d6975ce80c8b 100644
--- a/dbaccess/source/filter/hsqldb/hsqlimport.hxx
+++ b/dbaccess/source/filter/hsqldb/hsqlimport.hxx
@@ -15,6 +15,7 @@
 
 #include "rowinputbinary.hxx"
 #include "hsqlbinarynode.hxx"
+#include "columndef.hxx"
 
 namespace dbahsql
 {
@@ -26,11 +27,11 @@ private:
 
 protected:
     void insertRow(const std::vector<css::uno::Any>& xRows, const OUString& sTable,
-                   const std::vector<sal_Int32>& rColTypes);
+                   const std::vector<ColumnDefinition>& rColTypes);
     void processTree(HsqlBinaryNode& rNode, HsqlRowInputStream& rStream,
-                     const std::vector<sal_Int32>& rColTypes, const OUString& sTableName);
+                     const std::vector<ColumnDefinition>& rColTypes, const OUString& sTableName);
     void parseTableRows(const std::vector<sal_Int32>& rIndexes,
-                        const std::vector<sal_Int32>& rColTypes, const OUString& sTableName);
+                        const std::vector<ColumnDefinition>& rColTypes, const OUString& sTableName);
 
 public:
     HsqlImporter(css::uno::Reference<css::sdbc::XConnection>& rConnection,
diff --git a/dbaccess/source/filter/hsqldb/parseschema.cxx b/dbaccess/source/filter/hsqldb/parseschema.cxx
index be62293467b4..660b193f21d7 100644
--- a/dbaccess/source/filter/hsqldb/parseschema.cxx
+++ b/dbaccess/source/filter/hsqldb/parseschema.cxx
@@ -61,6 +61,11 @@ public:
         for (const auto& sIndex : sIndexes)
             indexes.push_back(sIndex.toInt32());
 
+        // ignore last element
+        // TODO this is an identity peek, which indicates the value of the next
+        // identity. At the current state all migrated identities start with 0.
+        indexes.pop_back();
+
         return indexes;
     }
 
@@ -78,7 +83,7 @@ using namespace css::io;
 using namespace css::uno;
 using namespace css::embed;
 
-typedef std::vector<sal_Int32> ColumnTypeVector;
+typedef std::vector<ColumnDefinition> ColumnTypeVector;
 
 SchemaParser::SchemaParser(Reference<XStorage>& rStorage)
     : m_rStorage(rStorage)
@@ -93,7 +98,7 @@ SqlStatementVector SchemaParser::parseSchema()
     if (!m_rStorage->hasByName(SCHEMA_FILENAME))
     {
         SAL_WARN("dbaccess", "script file does not exist in storage during hsqldb import");
-        assert(false); // TODO throw error
+        return SqlStatementVector{};
     }
 
     Reference<XStream> xStream(m_rStorage->openStreamElement(SCHEMA_FILENAME, ElementModes::READ));
@@ -124,13 +129,8 @@ SqlStatementVector SchemaParser::parseSchema()
 
             sSql = aCreateParser.compose();
 
-            // Store columns for each table
-            ColumnTypeVector colTypes;
-            std::vector<ColumnDefinition> colDefs = aCreateParser.getColumnDef();
-            for (const auto& colDef : colDefs)
-                colTypes.push_back(colDef.getDataType());
-
-            m_ColumnTypes[aCreateParser.getTableName()] = colTypes;
+            // save column definitions
+            m_ColumnTypes[aCreateParser.getTableName()] = aCreateParser.getColumnDef();
             parsedStatements.push_back(sSql);
         }
     }
diff --git a/dbaccess/source/filter/hsqldb/parseschema.hxx b/dbaccess/source/filter/hsqldb/parseschema.hxx
index c0c31a3c6884..b22f5e12fe90 100644
--- a/dbaccess/source/filter/hsqldb/parseschema.hxx
+++ b/dbaccess/source/filter/hsqldb/parseschema.hxx
@@ -15,6 +15,8 @@
 #include <vector>
 #include <map>
 
+#include "columndef.hxx"
+
 namespace dbahsql
 {
 using SqlStatementVector = std::vector<OUString>;
@@ -25,7 +27,7 @@ private:
     css::uno::Reference<css::embed::XStorage>& m_rStorage;
 
     // column type for each table. It is filled after parsing schema.
-    std::map<OUString, std::vector<sal_Int32>> m_ColumnTypes;
+    std::map<OUString, std::vector<ColumnDefinition>> m_ColumnTypes;
 
     // root element's position of data for each table
     std::map<OUString, std::vector<sal_Int32>> m_Indexes;
@@ -35,7 +37,7 @@ public:
 
     SqlStatementVector parseSchema();
 
-    std::vector<sal_Int32> getTableColumnTypes(const OUString& sTableName) const;
+    std::vector<ColumnDefinition> getTableColumnTypes(const OUString& sTableName) const;
 
     const std::map<OUString, std::vector<sal_Int32>>& getTableIndexes() const;
 };
diff --git a/dbaccess/source/filter/hsqldb/rowinputbinary.cxx b/dbaccess/source/filter/hsqldb/rowinputbinary.cxx
index 492f3c4ac6d5..277b5c176b20 100644
--- a/dbaccess/source/filter/hsqldb/rowinputbinary.cxx
+++ b/dbaccess/source/filter/hsqldb/rowinputbinary.cxx
@@ -138,7 +138,7 @@ using namespace css::io;
 using namespace boost::posix_time;
 using namespace boost::gregorian;
 
-typedef std::vector<sal_Int32> ColumnTypeVector;
+typedef std::vector<ColumnDefinition> ColumnTypeVector;
 
 HsqlRowInputStream::HsqlRowInputStream() {}
 
@@ -256,7 +256,7 @@ std::vector<Any> HsqlRowInputStream::readOneRow(const ColumnTypeVector& nColType
             continue;
         }
 
-        sal_Int32 nType = nColTypes[i];
+        sal_Int32 nType = nColTypes[i].getDataType();
 
         // TODO throw error on EoF
 
@@ -315,7 +315,7 @@ std::vector<Any> HsqlRowInputStream::readOneRow(const ColumnTypeVector& nColType
                 Sequence<Any> result(2);
                 OUString sNum = lcl_makeStringFromBigint(aBytes);
                 result[0] <<= lcl_putDot(sNum, nScale);
-                result[1] <<= nSize;
+                result[1] <<= nScale;
                 aData.push_back(makeAny(result));
             }
             break;
diff --git a/dbaccess/source/filter/hsqldb/rowinputbinary.hxx b/dbaccess/source/filter/hsqldb/rowinputbinary.hxx
index 19fb24e28b89..8fcdf1ec0cd5 100644
--- a/dbaccess/source/filter/hsqldb/rowinputbinary.hxx
+++ b/dbaccess/source/filter/hsqldb/rowinputbinary.hxx
@@ -16,6 +16,8 @@
 
 #include <com/sun/star/io/XInputStream.hpp>
 
+#include "columndef.hxx"
+
 namespace dbahsql
 {
 class HsqlRowInputStream
@@ -32,7 +34,7 @@ protected:
 
 public:
     HsqlRowInputStream();
-    std::vector<css::uno::Any> readOneRow(const std::vector<sal_Int32>& colTypes);
+    std::vector<css::uno::Any> readOneRow(const std::vector<ColumnDefinition>& colTypes);
     void seek(sal_Int32 nPos);
     void setInputStream(css::uno::Reference<css::io::XInputStream>& rStream);
     SvStream* getInputStream() const;