1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
/* -*- 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 <test/bootstrapfixture.hxx>

#include <com/sun/star/sdb/XOfficeDatabaseDocument.hpp>
#include <com/sun/star/sdbc/XColumnLocate.hpp>
#include <com/sun/star/sdbc/XConnection.hpp>
#include <com/sun/star/sdbc/XResultSet.hpp>
#include <com/sun/star/sdbc/XResultSetMetaData.hpp>
#include <com/sun/star/sdbc/XResultSetMetaDataSupplier.hpp>
#include <com/sun/star/sdbc/XRow.hpp>
#include <com/sun/star/sdbc/SQLException.hpp>
#include <com/sun/star/sdbc/XParameters.hpp>
#include <com/sun/star/sdbc/XStatement.hpp>
#include <com/sun/star/sdbc/XDriver.hpp>

#include <com/sun/star/util/DateTime.hpp>
#include <svtools/miscopt.hxx>
#include <osl/process.h>

using namespace ::com::sun::star;
using namespace ::com::sun::star::sdb;
using namespace ::com::sun::star::sdbc;
using namespace ::com::sun::star::uno;
using namespace ::com::sun::star::beans;

class MysqlTestDriver : public test::BootstrapFixture
{
private:
    OUString m_sUrl;
    Reference<XInterface> m_xMysqlcComponent;
    Reference<XDriver> m_xDriver;
    Sequence<PropertyValue> m_infos;

public:
    MysqlTestDriver()
        : test::BootstrapFixture(false, false)
    {
    }
    virtual void setUp() override;
    virtual void tearDown() override;
    void testDBConnection();
    void testCreateAndDropTable();
    void testIntegerInsertAndQuery();
    void testDBPositionChange();
    void testMultipleResultsets();
    void testDBMetaData();
    void testTimestampField();
    void testNumericConversionPrepared();

    CPPUNIT_TEST_SUITE(MysqlTestDriver);
    CPPUNIT_TEST(testDBConnection);
    CPPUNIT_TEST(testCreateAndDropTable);
    CPPUNIT_TEST(testIntegerInsertAndQuery);
    CPPUNIT_TEST(testMultipleResultsets);
    CPPUNIT_TEST(testDBMetaData);
    CPPUNIT_TEST(testTimestampField);
    CPPUNIT_TEST(testNumericConversionPrepared);
    CPPUNIT_TEST_SUITE_END();
};

void MysqlTestDriver::tearDown()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
    {
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    }
    uno::Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");
    xStatement->executeUpdate("DROP TABLE IF EXISTS otherTable");
    test::BootstrapFixture::tearDown();
}

void MysqlTestDriver::setUp()
{
    test::BootstrapFixture::setUp();

    /* Get URL from environment variable. This test suite should run only when
     * there is a URL given. This is because it can be used for testing connection to
     * external databases as well.
     *
     * Example URL:
     * username/password@sdbc:mysql:mysqlc:localhost:3306/testdatabase
     */
    osl_getEnvironment(OUString("CONNECTIVITY_TEST_MYSQL_DRIVER").pData, &m_sUrl.pData);
    m_xMysqlcComponent
        = getMultiServiceFactory()->createInstance("com.sun.star.comp.sdbc.mysqlc.MysqlCDriver");
    CPPUNIT_ASSERT_MESSAGE("no mysqlc component!", m_xMysqlcComponent.is());

    // set user name and password
    m_infos = Sequence<PropertyValue>{ 2 };
    m_infos[0].Name = OUString{ "user" };
    sal_Int32 nPer = m_sUrl.indexOf("/");
    m_infos[0].Value = makeAny(m_sUrl.copy(0, nPer));
    m_sUrl = m_sUrl.copy(nPer + 1);
    m_infos[1].Name = OUString{ "password" };
    sal_Int32 nAt = m_sUrl.indexOf("@");
    m_infos[1].Value = makeAny(m_sUrl.copy(0, nAt));
    m_sUrl = m_sUrl.copy(nAt + 1);

    m_xDriver.set(m_xMysqlcComponent, UNO_QUERY);
    if (!m_xDriver.is())
    {
        CPPUNIT_ASSERT_MESSAGE("cannot connect to mysqlc driver!", m_xDriver.is());
    }
}

/**
 * Test database connection. It is assumed that the given URL is correct and
 * there is a server running at the location.
 */
void MysqlTestDriver::testDBConnection()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
    {
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    }

    uno::Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());

    Reference<XResultSet> xResultSet = xStatement->executeQuery("SELECT 1");
    CPPUNIT_ASSERT(xResultSet.is());
    Reference<XRow> xRow(xResultSet, UNO_QUERY);
    CPPUNIT_ASSERT_MESSAGE("cannot extract row from result set!", xRow.is());

    sal_Bool result = xResultSet->first();
    CPPUNIT_ASSERT_MESSAGE("fetch first row failed!", result);
}

/**
 * Test creation and removal of a table
 */
void MysqlTestDriver::testCreateAndDropTable()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
    {
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    }

    uno::Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");

    auto nUpdateCount
        = xStatement->executeUpdate("CREATE TABLE myTestTable (id INTEGER PRIMARY KEY)");
    CPPUNIT_ASSERT_EQUAL(0, nUpdateCount); // it's a DDL statement

    // we can use the same xStatement instance here
    nUpdateCount = xStatement->executeUpdate("DROP TABLE myTestTable");
    CPPUNIT_ASSERT_EQUAL(0, nUpdateCount); // it's a DDL statement
}

void MysqlTestDriver::testIntegerInsertAndQuery()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
    {
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    }

    Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");

    auto nUpdateCount
        = xStatement->executeUpdate("CREATE TABLE myTestTable (id INTEGER PRIMARY KEY)");
    CPPUNIT_ASSERT_EQUAL(0, nUpdateCount); // it's a DDL statement

    Reference<XPreparedStatement> xPrepared
        = xConnection->prepareStatement(OUString{ "INSERT INTO myTestTable VALUES (?)" });
    Reference<XParameters> xParams(xPrepared, UNO_QUERY);
    constexpr int ROW_COUNT = 3;
    for (int i = 0; i < ROW_COUNT; ++i)
    {
        xParams->setLong(1, i); // first and only column
        nUpdateCount = xPrepared->executeUpdate();
        CPPUNIT_ASSERT_EQUAL(1, nUpdateCount); // one row is inserted at a time
    }

    // now let's query the existing data
    Reference<XResultSet> xResultSet = xStatement->executeQuery("SELECT id from myTestTable");
    CPPUNIT_ASSERT_MESSAGE("result set cannot be instantiated after query", xResultSet.is());
    Reference<XRow> xRow(xResultSet, UNO_QUERY);
    Reference<XColumnLocate> xColumnLocate(xResultSet, UNO_QUERY);
    CPPUNIT_ASSERT_MESSAGE("cannot extract row from result set!", xRow.is());

    for (long i = 0; i < ROW_COUNT; ++i)
    {
        bool hasRow = xResultSet->next();
        CPPUNIT_ASSERT_MESSAGE("not enough result after query", hasRow);
        CPPUNIT_ASSERT_EQUAL(i, xRow->getLong(1)); // first and only column
        CPPUNIT_ASSERT_EQUAL(i, xRow->getLong(xColumnLocate->findColumn("id"))); // test findColumn
    }
    CPPUNIT_ASSERT_MESSAGE("Cursor is not on last position.",
                           xResultSet->isLast()); // cursor is on last position
    CPPUNIT_ASSERT_EQUAL(ROW_COUNT, xResultSet->getRow()); // which is the last position

    bool hasRow = xResultSet->next(); // go to afterlast
    // no more rows, next should return false
    CPPUNIT_ASSERT_MESSAGE("next returns true after last row", !hasRow);
    // cursor should be in afterlast position
    CPPUNIT_ASSERT_EQUAL(ROW_COUNT + 1, xResultSet->getRow());
    CPPUNIT_ASSERT_MESSAGE("Cursor is not on after-last position.", xResultSet->isAfterLast());

    nUpdateCount = xStatement->executeUpdate("DROP TABLE myTestTable");
    CPPUNIT_ASSERT_EQUAL(0, nUpdateCount); // it's a DDL statement
}

void MysqlTestDriver::testDBPositionChange()<--- The function 'testDBPositionChange' is never used.
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
    {
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    }

    Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");

    auto nUpdateCount
        = xStatement->executeUpdate("CREATE TABLE myTestTable (id INTEGER PRIMARY KEY)");
    CPPUNIT_ASSERT_EQUAL(0, nUpdateCount); // it's a DDL statement
    Reference<XPreparedStatement> xPrepared
        = xConnection->prepareStatement(OUString{ "INSERT INTO myTestTable VALUES (?)" });
    Reference<XParameters> xParams(xPrepared, UNO_QUERY);
    constexpr int ROW_COUNT = 3;
    for (int i = 1; i <= ROW_COUNT; ++i)
    {
        xParams->setLong(1, i); // first and only column
        nUpdateCount = xPrepared->executeUpdate();
        CPPUNIT_ASSERT_EQUAL(1, nUpdateCount); // one row is inserted at a time
    }
    Reference<XResultSet> xResultSet = xStatement->executeQuery("SELECT id from myTestTable");
    CPPUNIT_ASSERT_MESSAGE("result set cannot be instantiated after query", xResultSet.is());
    Reference<XRow> xRow(xResultSet, UNO_QUERY);
    CPPUNIT_ASSERT_MESSAGE("cannot extract row from result set!", xRow.is());

    xResultSet->afterLast();
    CPPUNIT_ASSERT_EQUAL(ROW_COUNT + 1, xResultSet->getRow());
    xResultSet->last();
    CPPUNIT_ASSERT_EQUAL(ROW_COUNT, nUpdateCount);
    CPPUNIT_ASSERT_EQUAL(ROW_COUNT, xResultSet->getRow());
    bool successPrevious = xResultSet->previous();
    CPPUNIT_ASSERT(successPrevious);
    CPPUNIT_ASSERT_EQUAL(ROW_COUNT - 1, nUpdateCount);
    xResultSet->beforeFirst();
    xResultSet->next();
    CPPUNIT_ASSERT_EQUAL(1, xResultSet->getRow());
    xResultSet->first();
    CPPUNIT_ASSERT_EQUAL(1, xResultSet->getRow());

    // Now previous should put the cursor to before-first position, but it
    // should return with false.
    successPrevious = xResultSet->previous();
    CPPUNIT_ASSERT(!successPrevious);
    CPPUNIT_ASSERT_EQUAL(0, xResultSet->getRow());

    nUpdateCount = xStatement->executeUpdate("DROP TABLE myTestTable");
    CPPUNIT_ASSERT_EQUAL(0, nUpdateCount); // it's a DDL statement
}

void MysqlTestDriver::testMultipleResultsets()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    CPPUNIT_ASSERT(xConnection.is());
    Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    // create two tables
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");
    xStatement->executeUpdate("DROP TABLE IF EXISTS otherTable");
    xStatement->executeUpdate("CREATE TABLE myTestTable (id INTEGER PRIMARY KEY)");
    xStatement->executeUpdate("INSERT INTO myTestTable VALUES (1)");
    xStatement->executeUpdate("CREATE TABLE otherTable (id INTEGER PRIMARY KEY)");
    xStatement->executeUpdate("INSERT INTO otherTable VALUES (2)");

    // create first result set
    Reference<XResultSet> xResultSet = xStatement->executeQuery("SELECT id from myTestTable");
    CPPUNIT_ASSERT_MESSAGE("result set cannot be instantiated after query", xResultSet.is());
    // use it
    xResultSet->next();
    Reference<XRow> xRowFirst(xResultSet, UNO_QUERY);
    CPPUNIT_ASSERT_EQUAL(1l, xRowFirst->getLong(1));
    // create second result set
    Reference<XResultSet> xResultSet2 = xStatement->executeQuery("SELECT id from otherTable");
    // use second result set
    xResultSet2->next();
    Reference<XRow> xRowSecond(xResultSet2, UNO_QUERY);
    CPPUNIT_ASSERT_EQUAL(2l, xRowSecond->getLong(1));
    // now use the first result set again
    CPPUNIT_ASSERT_EQUAL(1l, xRowFirst->getLong(1));

    xStatement->executeUpdate("DROP TABLE myTestTable");
    xStatement->executeUpdate("DROP TABLE otherTable");
}

void MysqlTestDriver::testDBMetaData()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    uno::Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");

    auto nUpdateCount = xStatement->executeUpdate(<--- Variable 'nUpdateCount' is reassigned a value before the old one has been used.
        "CREATE TABLE myTestTable (id INTEGER PRIMARY KEY, name VARCHAR(20))");
    Reference<XPreparedStatement> xPrepared
        = xConnection->prepareStatement(OUString{ "INSERT INTO myTestTable VALUES (?, ?)" });
    Reference<XParameters> xParams(xPrepared, UNO_QUERY);
    constexpr int ROW_COUNT = 3;
    for (int i = 0; i < ROW_COUNT; ++i)
    {
        xParams->setLong(1, i);
        xParams->setString(2, "lorem");
        xPrepared->executeUpdate();
    }

    Reference<XResultSet> xResultSet = xStatement->executeQuery("SELECT * from myTestTable");
    Reference<XResultSetMetaDataSupplier> xMetaDataSupplier(xResultSet, UNO_QUERY);
    Reference<XResultSetMetaData> xMetaData = xMetaDataSupplier->getMetaData();
    CPPUNIT_ASSERT_EQUAL(OUString{ "id" }, xMetaData->getColumnName(1));
    CPPUNIT_ASSERT_EQUAL(OUString{ "name" }, xMetaData->getColumnName(2));
    CPPUNIT_ASSERT(!xMetaData->isAutoIncrement(1));
    CPPUNIT_ASSERT(!xMetaData->isCaseSensitive(2)); // default collation should be case insensitive
    xResultSet->next(); // use it
    // test that meta data is usable even after fetching result set
    CPPUNIT_ASSERT_EQUAL(OUString{ "name" }, xMetaData->getColumnName(2));
    CPPUNIT_ASSERT_THROW_MESSAGE("exception expected when indexing out of range",
                                 xMetaData->getColumnName(3), sdbc::SQLException);
    nUpdateCount = xStatement->executeUpdate("DROP TABLE myTestTable");<--- Variable 'nUpdateCount' is reassigned a value before the old one has been used.<--- Variable 'nUpdateCount' is assigned a value that is never used.
}

void MysqlTestDriver::testTimestampField()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    uno::Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");

    xStatement->executeUpdate(
        "CREATE TABLE myTestTable (id INTEGER PRIMARY KEY, mytimestamp timestamp)");
    xStatement->executeUpdate("INSERT INTO myTestTable VALUES (1, '2008-02-16 20:15:03')");

    // now let's query
    Reference<XResultSet> xResultSet
        = xStatement->executeQuery("SELECT mytimestamp from myTestTable");

    xResultSet->next(); // use it
    Reference<XRow> xRow(xResultSet, UNO_QUERY);
    CPPUNIT_ASSERT_MESSAGE("cannot extract row from result set!", xRow.is());
    util::DateTime dt = xRow->getTimestamp(1);
    CPPUNIT_ASSERT_EQUAL(static_cast<short>(2008), dt.Year);
    CPPUNIT_ASSERT_EQUAL(static_cast<unsigned short>(2), dt.Month);
    CPPUNIT_ASSERT_EQUAL(static_cast<unsigned short>(16), dt.Day);

    CPPUNIT_ASSERT_EQUAL(static_cast<unsigned short>(20), dt.Hours);
    CPPUNIT_ASSERT_EQUAL(static_cast<unsigned short>(15), dt.Minutes);
    CPPUNIT_ASSERT_EQUAL(static_cast<unsigned short>(3), dt.Seconds);

    xStatement->executeUpdate("DROP TABLE myTestTable");
}

/**
 * Test getting value from a decimal type column from a result set of a
 * prepared statement, getting as a tinyint, string, short, int, long.
 */
void MysqlTestDriver::testNumericConversionPrepared()
{
    Reference<XConnection> xConnection = m_xDriver->connect(m_sUrl, m_infos);
    if (!xConnection.is())
        CPPUNIT_ASSERT_MESSAGE("cannot connect to data source!", xConnection.is());
    uno::Reference<XStatement> xStatement = xConnection->createStatement();
    CPPUNIT_ASSERT(xStatement.is());
    xStatement->executeUpdate("DROP TABLE IF EXISTS myTestTable");

    xStatement->executeUpdate("CREATE TABLE myTestTable (myDecimal DECIMAL(4,2))");
    xStatement->executeUpdate("INSERT INTO myTestTable VALUES (11.22)");
    Reference<XPreparedStatement> xPrepared
        = xConnection->prepareStatement("SELECT * from myTestTable");
    Reference<XResultSet> xResultSet = xPrepared->executeQuery();
    xResultSet->next(); // use it
    Reference<XRow> xRow(xResultSet, UNO_QUERY);
    CPPUNIT_ASSERT_EQUAL(OUString("11.22"), xRow->getString(1));
    // converting to integer types results in rounding down the number
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int8>(11), xRow->getByte(1));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int16>(11), xRow->getShort(1));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int32>(11), xRow->getInt(1));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int64>(11), xRow->getLong(1));

    xStatement->executeUpdate("DROP TABLE myTestTable");
}

CPPUNIT_TEST_SUITE_REGISTRATION(MysqlTestDriver);

CPPUNIT_PLUGIN_IMPLEMENT();

/* vim:set shiftwidth=4 softtabstop=4 expandtab: */