dbaccess/qa/extras/hsql_schema_import.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  */
   9
  10 #include <fbcreateparser.hxx>
  11 #include <columndef.hxx>
  12 #include <cppunit/plugin/TestPlugIn.h>
  13 #include <com/sun/star/sdbc/DataType.hpp>
  14 #include <cppunit/TestFixture.h>
  15 #include <cppunit/extensions/HelperMacros.h>
  16
  17 using namespace dbahsql;
  18
  19 namespace
  20 {
  21 constexpr std::size_t operator"" _z(unsigned long long n) { return n; }
  22
  23 const ColumnDefinition* lcl_findByType(const std::vector<ColumnDefinition>& columns,
  24                                        sal_Int32 nType)
  25 {
  26     for (const auto& column : columns)
  27     {
  28         if (column.getDataType() == nType)
  29             return &column;
  30     }
  31     return nullptr;
  32 }
  33 }
  34
  35 class HsqlSchemaImportTest : public CppUnit::TestFixture
  36 {
  37 public:
  38     void testIntegerPrimaryKeyNotNull();
  39     void testVarcharWithParam();
  40     void testVarcharWithoutParam();
  41     void testNumericWithTwoParam();
  42     void testIntegerAutoincremental();
  43     void testTimestampWithParam();
  44     void testDefaultValueNow();
  45     void testEvilNullColumnName();
  46     // TODO testForeign, testDecomposer
  47
  48     CPPUNIT_TEST_SUITE(HsqlSchemaImportTest);
  49
  50     CPPUNIT_TEST(testIntegerPrimaryKeyNotNull);
  51     CPPUNIT_TEST(testVarcharWithParam);
  52     CPPUNIT_TEST(testVarcharWithoutParam);
  53     CPPUNIT_TEST(testNumericWithTwoParam);
  54     CPPUNIT_TEST(testIntegerAutoincremental);
  55     CPPUNIT_TEST(testTimestampWithParam);
  56     CPPUNIT_TEST(testDefaultValueNow);
  57     CPPUNIT_TEST(testEvilNullColumnName);
  58
  59     CPPUNIT_TEST_SUITE_END();
  60 };
  61
  62 void HsqlSchemaImportTest::testIntegerPrimaryKeyNotNull()
  63 {
  64     const OUString sql{ "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY)" };
  65
  66     FbCreateStmtParser aCreateParser;
  67     aCreateParser.parse(sql);
  68
  69     CPPUNIT_ASSERT_EQUAL(OUString{ "\"myTable\"" }, aCreateParser.getTableName());
  70     const auto& columns = aCreateParser.getColumnDef();
  71     CPPUNIT_ASSERT_EQUAL(1_z, columns.size());
  72     const auto& column = columns.at(0);
  73     CPPUNIT_ASSERT_EQUAL(OUString{ "\"id\"" }, column.getName());
  74     CPPUNIT_ASSERT_EQUAL(css::sdbc::DataType::INTEGER, column.getDataType());
  75     CPPUNIT_ASSERT(column.isPrimaryKey());
  76     CPPUNIT_ASSERT(!column.isNullable());
  77     CPPUNIT_ASSERT(!column.isAutoIncremental());
  78 }
  79
  80 void HsqlSchemaImportTest::testVarcharWithParam()
  81 {
  82     const OUString sql{
  83         "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY, \"myText\" "
  84         "VARCHAR(50))"
  85     };
  86
  87     FbCreateStmtParser aCreateParser;
  88     aCreateParser.parse(sql);
  89
  90     const auto& columns = aCreateParser.getColumnDef();
  91     CPPUNIT_ASSERT_EQUAL(2_z, columns.size());
  92     const ColumnDefinition* colVarchar = lcl_findByType(columns, css::sdbc::DataType::VARCHAR);
  93     CPPUNIT_ASSERT(colVarchar != nullptr);
  94     const auto& params = colVarchar->getParams();
  95     CPPUNIT_ASSERT_EQUAL(1_z, params.size());
  96     constexpr sal_Int32 nParamExpected = 50;
  97     CPPUNIT_ASSERT_EQUAL(nParamExpected, params.at(0)); // VARCHAR(50)
  98 }
  99
 100 /**
 101  * Special case:
 102  * HSQLDB might define a column VARCHAR without parameter. With Firebird
 103  * dialect, this is forbidden, so a default parameter has to be appended:
 104  **/
 105 void HsqlSchemaImportTest::testVarcharWithoutParam()
 106 {
 107     const OUString sql{
 108         "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY, \"myText\" "
 109         "VARCHAR)"
 110     };
 111
 112     FbCreateStmtParser aCreateParser;
 113     aCreateParser.parse(sql);
 114
 115     const auto& columns = aCreateParser.getColumnDef();
 116     CPPUNIT_ASSERT_EQUAL(2_z, columns.size());
 117     const ColumnDefinition* colVarchar = lcl_findByType(columns, css::sdbc::DataType::VARCHAR);
 118     CPPUNIT_ASSERT(colVarchar != nullptr);
 119     const auto& params = colVarchar->getParams();
 120     CPPUNIT_ASSERT_EQUAL(1_z, params.size()); // parameter generated
 121 }
 122
 123 void HsqlSchemaImportTest::testNumericWithTwoParam()
 124 {
 125     const OUString sql{
 126         "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY, \"Betrag\" "
 127         "NUMERIC(8,2))"
 128     };
 129
 130     FbCreateStmtParser aCreateParser;
 131     aCreateParser.parse(sql);
 132
 133     const auto& columns = aCreateParser.getColumnDef();
 134     CPPUNIT_ASSERT_EQUAL(2_z, columns.size());
 135
 136     const ColumnDefinition* colNumeric = lcl_findByType(columns, css::sdbc::DataType::NUMERIC);
 137     CPPUNIT_ASSERT(colNumeric != nullptr);
 138     const auto& params = colNumeric->getParams();
 139     CPPUNIT_ASSERT_EQUAL(2_z, params.size());
 140
 141     constexpr sal_Int32 nPrecision = 8;
 142     constexpr sal_Int32 nScale = 2;
 143     CPPUNIT_ASSERT_EQUAL(nPrecision, params.at(0));
 144     CPPUNIT_ASSERT_EQUAL(nScale, params.at(1));
 145 }
 146
 147 void HsqlSchemaImportTest::testIntegerAutoincremental()
 148 {
 149     const OUString sql{
 150         "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY GENERATED "
 151         "BY DEFAULT AS IDENTITY(START WITH 0), \"myText\" VARCHAR(50))"
 152     };
 153
 154     FbCreateStmtParser aCreateParser;
 155     aCreateParser.parse(sql);
 156
 157     const auto& columns = aCreateParser.getColumnDef();
 158     const auto column = columns.at(0);
 159     CPPUNIT_ASSERT_EQUAL(css::sdbc::DataType::INTEGER, column.getDataType());
 160     CPPUNIT_ASSERT(column.isAutoIncremental());
 161     CPPUNIT_ASSERT(column.isPrimaryKey());
 162     CPPUNIT_ASSERT(!column.isNullable());
 163 }
 164
 165 /**
 166  * Special case:
 167  * Hsqldb might use one parameter for defining column with type TIMESTAMP.
 168  * With Firebird this is illegal.
 169  */
 170 void HsqlSchemaImportTest::testTimestampWithParam()
 171 {
 172     const OUString sql{
 173         "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY, \"myText\" "
 174         "TIMESTAMP(0))"
 175     };
 176
 177     FbCreateStmtParser aCreateParser;
 178     aCreateParser.parse(sql);
 179
 180     const auto& columns = aCreateParser.getColumnDef();
 181     const ColumnDefinition* colTimeStamp = lcl_findByType(columns, css::sdbc::DataType::TIMESTAMP);
 182
 183     CPPUNIT_ASSERT(colTimeStamp != nullptr);
 184
 185     // instead of asserting parameter size, look at the deparsed string,
 186     // because it's Firebird specific
 187     OUString fbSql = aCreateParser.compose();
 188     CPPUNIT_ASSERT(fbSql.indexOf("0") < 0); //does not contain
 189 }
 190
 191 /**
 192  * Special case:
 193  * HSQLDB uses keyword NOW without quotes. Firebird uses single quotes 'NOW'
 194  */
 195 void HsqlSchemaImportTest::testDefaultValueNow()
 196 {
 197     const OUString sql{
 198         "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY, \"myDate\" "
 199         "TIMESTAMP DEFAULT NOW)"
 200     };
 201
 202     FbCreateStmtParser aCreateParser;
 203     aCreateParser.parse(sql);
 204
 205     const auto& columns = aCreateParser.getColumnDef();
 206     const ColumnDefinition* colTimeStamp = lcl_findByType(columns, css::sdbc::DataType::TIMESTAMP);
 207
 208     CPPUNIT_ASSERT(colTimeStamp != nullptr);
 209     CPPUNIT_ASSERT_EQUAL(OUString{ "NOW" }, colTimeStamp->getDefault()); // parsed NOW
 210     OUString fbSql = aCreateParser.compose();
 211     CPPUNIT_ASSERT(fbSql.indexOf("\'NOW\'") > 0); // composed 'NOW'
 212 }
 213
 214 void HsqlSchemaImportTest::testEvilNullColumnName()
 215 {
 216     const OUString sql{ "CREATE CACHED TABLE \"myTable\"(\"id\" INTEGER NOT NULL PRIMARY KEY, "
 217                         "\"myEvilNOT NULLName\" "
 218                         "VARCHAR(20))" };
 219
 220     FbCreateStmtParser aCreateParser;
 221     aCreateParser.parse(sql);
 222
 223     const auto& columns = aCreateParser.getColumnDef();
 224     CPPUNIT_ASSERT_EQUAL(2_z, columns.size());
 225     const ColumnDefinition* colVarchar = lcl_findByType(columns, css::sdbc::DataType::VARCHAR);
 226     CPPUNIT_ASSERT(colVarchar != nullptr);
 227     CPPUNIT_ASSERT(colVarchar->isNullable());
 228 }
 229
 230 CPPUNIT_TEST_SUITE_REGISTRATION(HsqlSchemaImportTest);
 231
 232 CPPUNIT_PLUGIN_IMPLEMENT();