dbahsql: Unit test for binary import

Also fix bugs shown by the unit test

Use boost date/time instead of std, because std::tm cannot handle dates
before 1970.

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

7 files changed, 253 insertions, 18 deletions

diff --git a/dbaccess/CppunitTest_dbaccess_hsqlbinary_import.mk b/dbaccess/CppunitTest_dbaccess_hsqlbinary_import.mk
new file mode 100644
index 000000000000..b23e4801f234
--- /dev/null
+++ b/dbaccess/CppunitTest_dbaccess_hsqlbinary_import.mk
@@ -0,0 +1,106 @@
+# -*- Mode: makefile-gmake; tab-width: 4; indent-tabs-mode: t -*-
+#
+# 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/.
+#
+
+$(eval $(call gb_CppunitTest_CppunitTest,dbaccess_hsql_binary_import))
+
+$(eval $(call gb_CppunitTest_add_exception_objects,dbaccess_hsql_binary_import, \
+    dbaccess/qa/unit/hsql_binary_import \
+))
+
+$(eval $(call gb_CppunitTest_use_libraries,dbaccess_hsql_binary_import, \
+    basegfx \
+    comphelper \
+    cppu \
+    cppuhelper \
+    dba \
+	dbahsql \
+    dbu \
+    sdbt \
+    drawinglayer \
+    editeng \
+    for \
+    forui \
+    i18nlangtag \
+    msfilter \
+    oox \
+    sal \
+    salhelper \
+    sax \
+    sb \
+    sfx \
+    sot \
+    svl \
+    svt \
+    svx \
+    svxcore \
+    test \
+    subsequenttest \
+    tl \
+    tk \
+    ucbhelper \
+	unotest \
+    utl \
+    vbahelper \
+    vcl \
+    xo \
+))
+
+$(eval $(call gb_CppunitTest_use_ure,dbaccess_hsql_binary_import))
+$(eval $(call gb_CppunitTest_use_vcl,dbaccess_hsql_binary_import))
+
+$(eval $(call gb_CppunitTest_set_include,dbaccess_hsql_binary_import,\
+	-I$(SRCDIR)/dbaccess/source/filter/hsqldb \
+    $$(INCLUDE) \
+))
+
+$(eval $(call gb_CppunitTest_use_api,dbaccess_hsql_binary_import,\
+    offapi \
+    oovbaapi \
+    udkapi \
+))
+
+$(eval $(call gb_CppunitTest_use_components,dbaccess_hsql_binary_import,\
+    basic/util/sb \
+    comphelper/util/comphelp \
+    configmgr/source/configmgr \
+    connectivity/source/drivers/firebird/firebird_sdbc \
+    connectivity/source/manager/sdbc2 \
+    dbaccess/util/dba \
+    dbaccess/util/dbu \
+    dbaccess/util/sdbt \
+    dbaccess/source/filter/xml/dbaxml \
+    filter/source/config/cache/filterconfig1 \
+    forms/util/frm \
+    framework/util/fwk \
+    i18npool/util/i18npool \
+    linguistic/source/lng \
+    oox/util/oox \
+    package/source/xstor/xstor \
+    package/util/package2 \
+    sax/source/expatwrap/expwrap \
+    scripting/source/basprov/basprov \
+    scripting/util/scriptframe \
+    sfx2/util/sfx \
+    sot/util/sot \
+    svl/source/fsstor/fsstorage \
+    svl/util/svl \
+    toolkit/util/tk \
+    ucb/source/core/ucb1 \
+    ucb/source/ucp/file/ucpfile1 \
+    ucb/source/ucp/tdoc/ucptdoc1 \
+    unotools/util/utl \
+    unoxml/source/rdf/unordf \
+    unoxml/source/service/unoxml \
+    uui/util/uui \
+    xmloff/util/xo \
+))
+
+$(eval $(call gb_CppunitTest_use_configuration,dbaccess_hsql_binary_import))
+
+# vim: set noet sw=4 ts=4:
diff --git a/dbaccess/Library_dbahsql.mk b/dbaccess/Library_dbahsql.mk
index bbcdea138a3e..ec684b32136b 100644
--- a/dbaccess/Library_dbahsql.mk
+++ b/dbaccess/Library_dbahsql.mk
@@ -14,7 +14,10 @@ $(eval $(call gb_Library_set_include,dbahsql,\
     -I$(WORKDIR)/YaccTarget/connectivity/source/parse \
 ))
 
-$(eval $(call gb_Library_use_external,dbahsql,boost_headers))
+$(eval $(call gb_Library_use_externals,dbahsql,\
+	boost_headers \
+	boost_date_time \
+))
 
 $(eval $(call gb_Library_set_precompiled_header,dbahsql,$(SRCDIR)/dbaccess/inc/pch/precompiled_dbahsql))
 
diff --git a/dbaccess/Module_dbaccess.mk b/dbaccess/Module_dbaccess.mk
index a8ecb1060c43..0eb7bf97c349 100644
--- a/dbaccess/Module_dbaccess.mk
+++ b/dbaccess/Module_dbaccess.mk
@@ -45,6 +45,7 @@ $(eval $(call gb_Module_add_check_targets,dbaccess,\
 	CppunitTest_dbaccess_nolib_save \
 	CppunitTest_dbaccess_macros_test \
 	CppunitTest_dbaccess_hsqlschema_import \
+	CppunitTest_dbaccess_hsqlbinary_import \
 ))
 
 ifeq ($(ENABLE_JAVA),TRUE)
diff --git a/dbaccess/qa/unit/data/hsqldb_migration_test.odb b/dbaccess/qa/unit/data/hsqldb_migration_test.odb
new file mode 100644
index 000000000000..99b6b5d9a69d
--- /dev/null
+++ b/dbaccess/qa/unit/data/hsqldb_migration_test.odb
diff --git a/dbaccess/qa/unit/hsql_binary_import.cxx b/dbaccess/qa/unit/hsql_binary_import.cxx
new file mode 100644
index 000000000000..3afe7bd55396
--- /dev/null
+++ b/dbaccess/qa/unit/hsql_binary_import.cxx
@@ -0,0 +1,94 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * 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/.
+ */
+
+#include "dbtest_base.cxx"
+
+#include <hsqlimport.hxx>
+
+#include <osl/process.h>
+#include <cppunit/plugin/TestPlugIn.h>
+#include <com/sun/star/sdbc/DataType.hpp>
+#include <com/sun/star/sdbc/XRow.hpp>
+#include <cppunit/TestFixture.h>
+#include <cppunit/extensions/HelperMacros.h>
+#include <test/unoapi_test.hxx>
+#include <svtools/miscopt.hxx>
+
+using namespace dbahsql;
+
+class HsqlBinaryImportTest : public DBTestBase
+{
+public:
+    void testBinaryImport();
+
+    virtual void setUp() override;
+
+    CPPUNIT_TEST_SUITE(HsqlBinaryImportTest);
+
+    CPPUNIT_TEST(testBinaryImport);
+
+    CPPUNIT_TEST_SUITE_END();
+};
+
+void HsqlBinaryImportTest::setUp()
+{
+    DBTestBase::setUp();
+    SvtMiscOptions aMiscOptions;
+    aMiscOptions.SetExperimentalMode(true);
+    osl_setEnvironment(OUString{ "DBACCESS_HSQL_MIGRATION" }.pData, OUString{ "1" }.pData);
+}
+
+void HsqlBinaryImportTest::testBinaryImport()
+{
+    uno::Reference<XOfficeDatabaseDocument> xDocument
+        = getDocumentForFileName("hsqldb_migration_test.odb");
+
+    uno::Reference<XConnection> xConnection = getConnectionForDocument(xDocument);
+    // at this point migration is already done
+
+    uno::Reference<XStatement> statement = xConnection->createStatement();
+    OUString sql{ "SELECT \"ID\", \"Power_value\", \"Power_name\", \"Retired\", "
+                  "\"Birth_date\" FROM \"TestTable\" ORDER BY \"ID\"" };
+
+    uno::Reference<XResultSet> xRes = statement->executeQuery(sql);
+    uno::Reference<XRow> xRow(xRes, UNO_QUERY_THROW);
+
+    // assert first row
+    xRes->next();
+    constexpr sal_Int16 idExpected = 1;
+    CPPUNIT_ASSERT_EQUAL(idExpected, xRow->getShort(1));
+    CPPUNIT_ASSERT_EQUAL(OUString{ "45.32" }, xRow->getString(2)); // numeric
+    CPPUNIT_ASSERT_EQUAL(OUString{ "laser eye" }, xRow->getString(3)); // varchar
+    CPPUNIT_ASSERT(xRow->getBoolean(4)); // boolean
+
+    css::util::Date date = xRow->getDate(5);
+
+    CPPUNIT_ASSERT_EQUAL(sal_uInt16{ 15 }, date.Day);
+    CPPUNIT_ASSERT_EQUAL(sal_uInt16{ 1 }, date.Month);
+    CPPUNIT_ASSERT_EQUAL(sal_Int16{ 1996 }, date.Year);
+
+    // assert second row
+    xRes->next();
+    constexpr sal_Int16 secondIdExpected = 2;
+    CPPUNIT_ASSERT_EQUAL(secondIdExpected, xRow->getShort(1)); // ID
+    CPPUNIT_ASSERT_EQUAL(OUString{ "54.12" }, xRow->getString(2)); // numeric
+    CPPUNIT_ASSERT_EQUAL(OUString{ "telekinesis" }, xRow->getString(3)); // varchar
+    CPPUNIT_ASSERT(!xRow->getBoolean(4)); // boolean
+
+    date = xRow->getDate(5);
+    CPPUNIT_ASSERT_EQUAL(sal_uInt16{ 26 }, date.Day);
+    CPPUNIT_ASSERT_EQUAL(sal_uInt16{ 2 }, date.Month);
+    CPPUNIT_ASSERT_EQUAL(sal_Int16{ 1998 }, date.Year);
+
+    closeDocument(uno::Reference<lang::XComponent>(xDocument, uno::UNO_QUERY));
+}
+
+CPPUNIT_TEST_SUITE_REGISTRATION(HsqlBinaryImportTest);
+
+CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/dbaccess/source/filter/hsqldb/fbcreateparser.cxx b/dbaccess/source/filter/hsqldb/fbcreateparser.cxx
index 64f51e6ca6d6..a20321dc8af5 100644
--- a/dbaccess/source/filter/hsqldb/fbcreateparser.cxx
+++ b/dbaccess/source/filter/hsqldb/fbcreateparser.cxx
@@ -112,7 +112,15 @@ OUString FbCreateStmtParser::compose() const
         lcl_appendWithSpace(sSql, columnIter->getName());
         lcl_appendWithSpace(sSql, lcl_DataTypetoFbTypeName(columnIter->getDataType()));
 
-        const std::vector<sal_Int32> params = columnIter->getParams();
+        std::vector<sal_Int32> params{ columnIter->getParams() };
+
+        if (columnIter->getDataType() == DataType::NUMERIC
+            || columnIter->getDataType() == DataType::DECIMAL)
+        {
+            // max precision is 18 here
+            if (params.at(0) > 18)
+                params[0] = 18;
+        }
 
         // Firebird SQL dialect does not like parameters for TIMESTAMP
         if (params.size() > 0 && columnIter->getDataType() != DataType::TIMESTAMP)
diff --git a/dbaccess/source/filter/hsqldb/rowinputbinary.cxx b/dbaccess/source/filter/hsqldb/rowinputbinary.cxx
index a69d696bf1b2..492f3c4ac6d5 100644
--- a/dbaccess/source/filter/hsqldb/rowinputbinary.cxx
+++ b/dbaccess/source/filter/hsqldb/rowinputbinary.cxx
@@ -28,7 +28,8 @@
 #include <unotools/ucbstreamhelper.hxx>
 #include <tools/stream.hxx>
 #include <rtl/ustrbuf.hxx>
-#include <ctime>
+
+#include <boost/date_time/posix_time/posix_time.hpp>
 
 namespace
 {
@@ -75,7 +76,7 @@ OUString lcl_double_dabble(const std::vector<sal_uInt8>& bytes)
 
     auto it = scratch.begin();
     /* Remove leading zeros from the scratch space. */
-    while (*it != 0)
+    while (*it == 0)
     {
         it = scratch.erase(it);
     }
@@ -113,6 +114,20 @@ OUString lcl_makeStringFromBigint(const std::vector<sal_uInt8> bytes)
     sRet.append(sNum);
     return sRet.makeStringAndClear();
 }
+
+OUString lcl_putDot(const OUString& sNum, sal_Int32 nScale)
+{
+    OUStringBuffer sBuf{ sNum };
+    if (nScale >= sNum.getLength())
+    {
+        sal_Int32 nNullsToAppend = nScale - sNum.getLength();
+        for (sal_Int32 i = 0; i < nNullsToAppend; ++i)
+            sBuf.insert(0, "0");
+    }
+    if (nScale > 0)
+        sBuf.insert(sBuf.getLength() - 1 - nScale, ".");
+    return sBuf.makeStringAndClear();
+}
 }
 
 namespace dbahsql
@@ -120,6 +135,8 @@ namespace dbahsql
 using namespace css::uno;
 using namespace css::sdbc;
 using namespace css::io;
+using namespace boost::posix_time;
+using namespace boost::gregorian;
 
 typedef std::vector<sal_Int32> ColumnTypeVector;
 
@@ -296,7 +313,8 @@ std::vector<Any> HsqlRowInputStream::readOneRow(const ColumnTypeVector& nColType
                 m_pStream->ReadInt32(nScale);
 
                 Sequence<Any> result(2);
-                result[0] <<= lcl_makeStringFromBigint(aBytes);
+                OUString sNum = lcl_makeStringFromBigint(aBytes);
+                result[0] <<= lcl_putDot(sNum, nScale);
                 result[1] <<= nSize;
                 aData.push_back(makeAny(result));
             }
@@ -305,11 +323,13 @@ std::vector<Any> HsqlRowInputStream::readOneRow(const ColumnTypeVector& nColType
             {
                 sal_Int64 value = 0;
                 m_pStream->ReadInt64(value); // in millisec, from 1970
-                std::time_t nEpochSec = value / 1000;
-                std::tm* tm = std::gmtime(&nEpochSec);
-                css::util::Date date(tm->tm_mday, tm->tm_mon + 1,
-                                     tm->tm_year + 1900); // day, month, year
-                aData.push_back(makeAny(date));
+                ptime epoch = time_from_string("1970-01-01 00:00:00.000");
+                ptime time = epoch + milliseconds(value);
+                date asDate = time.date();
+
+                css::util::Date loDate(asDate.day(), asDate.month(),
+                                       asDate.year()); // day, month, year
+                aData.push_back(makeAny(loDate));
             }
             break;
             case DataType::TIME:
@@ -324,19 +344,22 @@ std::vector<Any> HsqlRowInputStream::readOneRow(const ColumnTypeVector& nColType
             {
                 sal_Int64 nEpochMillis = 0;
                 m_pStream->ReadInt64(nEpochMillis);
-                std::time_t nEpochSec = nEpochMillis / 1000;
-                std::tm* tm = std::gmtime(&nEpochSec);
+                ptime epoch = time_from_string("1970-01-01 00:00:00.000");
+                ptime time = epoch + milliseconds(nEpochMillis);
+                date asDate = time.date();
 
                 sal_Int32 nNanos = 0;
                 m_pStream->ReadInt32(nNanos);
+
+                // convert into LO internal representation of dateTime
                 css::util::DateTime dateTime;
                 dateTime.NanoSeconds = nNanos;
-                dateTime.Seconds = tm->tm_sec;
-                dateTime.Minutes = tm->tm_min;
-                dateTime.Hours = tm->tm_hour;
-                dateTime.Day = tm->tm_mday;
-                dateTime.Month = tm->tm_mon + 1; // indexed from 0
-                dateTime.Year = 1900 + tm->tm_year;
+                dateTime.Seconds = time.time_of_day().seconds();
+                dateTime.Minutes = time.time_of_day().minutes();
+                dateTime.Hours = time.time_of_day().hours();
+                dateTime.Day = asDate.day();
+                dateTime.Month = asDate.month();
+                dateTime.Year = asDate.year();
                 aData.push_back(makeAny(dateTime));
             }
             break;