tests/db/test_sql_connection.cpp

   1 /*
   2  * Copyright (c) 2011 Samsung Electronics Co., Ltd All Rights Reserved
   3  *
   4  *    Licensed under the Apache License, Version 2.0 (the "License");
   5  *    you may not use this file except in compliance with the License.
   6  *    You may obtain a copy of the License at
   7  *
   8  *        http://www.apache.org/licenses/LICENSE-2.0
   9  *
  10  *    Unless required by applicable law or agreed to in writing, software
  11  *    distributed under the License is distributed on an "AS IS" BASIS,
  12  *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13  *    See the License for the specific language governing permissions and
  14  *    limitations under the License.
  15  */
  16 /*
  17  * @file        test_sql_connection.cpp
  18  * @author      Przemyslaw Dobrowolski (p.dobrowolsk@samsung.com)
  19  * @version     1.0
  20  * @brief       This file is the implementation file of sql connection tests
  21  */
  22 #include <dpl/test/test_runner.h>
  23 #include <dpl/db/sql_connection.h>
  24 #include <dpl/db/naive_synchronization_object.h>
  25 #include <memory>
  26 #include <boost/optional.hpp>
  27 #include <sstream>
  28 #include <string>
  29 #include <cstdlib>
  30 #include <ctime>
  31
  32 extern const char* PATH_DB;
  33
  34 RUNNER_TEST_GROUP_INIT(DPL)
  35
  36 class AbstractSynchronizationObjectGenerator
  37 {
  38   public:
  39     virtual ~AbstractSynchronizationObjectGenerator() {}
  40
  41     virtual DPL::DB::SqlConnection::SynchronizationObject *Create() = 0;
  42 };
  43
  44 class NaiveSynchronizationObjectGenerator :
  45     public AbstractSynchronizationObjectGenerator
  46 {
  47   public:
  48     virtual DPL::DB::SqlConnection::SynchronizationObject *Create()
  49     {
  50         return new DPL::DB::NaiveSynchronizationObject();
  51     }
  52 };
  53
  54 void MassiveReadWriteTest(AbstractSynchronizationObjectGenerator *generator);
  55
  56 class StressGenerator :
  57     public DPL::Thread
  58 {
  59   private:
  60     size_t m_prefix;
  61     std::string m_dbFileName;
  62     AbstractSynchronizationObjectGenerator *m_generator;
  63
  64   protected:
  65     virtual int ThreadEntry()
  66     {
  67         DPL::DB::SqlConnection connection(
  68             m_dbFileName,
  69             DPL::DB::SqlConnection::Flag::NotDefined,
  70             DPL::DB::SqlConnection::Flag::RW,
  71             m_generator->Create());
  72
  73         DPL::DB::SqlConnection::DataCommandAutoPtr countCommand =
  74             connection.PrepareDataCommand(
  75                 "SELECT COUNT(*) FROM test WHERE value=?");
  76
  77         for (size_t i = 0; i < 10; ++i) {
  78             std::ostringstream valueStream;
  79
  80             valueStream << "value_";
  81             valueStream << static_cast<unsigned long>(m_prefix);
  82             valueStream << "_";
  83             valueStream << static_cast<unsigned long>(i);
  84
  85             std::string value = valueStream.str();
  86
  87             connection.ExecCommand(
  88                 "INSERT INTO test VALUES ('%s');",
  89                 value.c_str());
  90
  91             countCommand->BindString(1, value.c_str());
  92
  93             RUNNER_ASSERT(countCommand->Step());
  94
  95             RUNNER_ASSERT(countCommand->GetColumnString(0) == "1");
  96
  97             countCommand->Reset();
  98         }
  99
 100         countCommand.reset();
 101
 102         return 0;
 103     }
 104
 105   public:
 106     StressGenerator(size_t prefix,
 107                     const std::string &dbFileName,
 108                     AbstractSynchronizationObjectGenerator *generator) :
 109         m_prefix(prefix),
 110         m_dbFileName(dbFileName),
 111         m_generator(generator)
 112     {}
 113 };
 114
 115 typedef std::shared_ptr<DPL::Thread> ThreadPtr;
 116
 117 void MassiveReadWriteTest(AbstractSynchronizationObjectGenerator *generator)
 118 {
 119     DPL::DB::SqlConnection connection(PATH_DB,
 120                                       DPL::DB::SqlConnection::Flag::UseLucene,
 121                                       DPL::DB::SqlConnection::Flag::RW);
 122
 123     connection.ExecCommand("CREATE TABLE test(value TEXT);");
 124
 125     const size_t STRESS_GENERATOR_COUNT = 5;
 126     ThreadPtr stressGenerators[STRESS_GENERATOR_COUNT];
 127
 128     for (size_t i = 0; i < STRESS_GENERATOR_COUNT; ++i) {
 129         stressGenerators[i].reset(
 130             new StressGenerator(i, PATH_DB, generator));
 131
 132         stressGenerators[i]->Run();
 133     }
 134
 135     for (size_t i = 0; i < STRESS_GENERATOR_COUNT; ++i) {
 136         stressGenerators[i]->Quit();
 137     }
 138
 139     connection.ExecCommand("DROP TABLE test;");
 140 }
 141
 142 /*
 143 Name: SqlConnection_MassiveReadWrite_NaiveSynchronization
 144 Description: tests massive multiple quiries from many threads
 145 Expected: no ORM/db failures
 146 */
 147 RUNNER_TEST(SqlConnection_MassiveReadWrite_NaiveSynchronization)
 148 {
 149     srand(time(NULL));
 150
 151     NaiveSynchronizationObjectGenerator m_generator;
 152     MassiveReadWriteTest(&m_generator);
 153 }
 154
 155 /*
 156 Name: SqlConnection_Not_Connected_Lucene
 157 Description: tests connection to not existing database with Lucene option
 158 Expected: exception throw
 159 */
 160 RUNNER_TEST(SqlConnection_Not_Connected_Lucene)
 161 {
 162     Try {
 163         DPL::DB::SqlConnection connection(
 164             "/notexisitingdirectiory/foo",
 165             DPL::DB::SqlConnection::Flag::
 166                 UseLucene,
 167             DPL::DB::SqlConnection::Flag::RW);
 168         RUNNER_ASSERT_MSG(false,
 169                           "connection should throw on accessing "
 170                           "nonexistent file as a database");
 171     }
 172     Catch(DPL::DB::SqlConnection::Exception::ConnectionBroken)
 173     {
 174         RUNNER_ASSERT(true);
 175     } catch (DPL::Exception) {
 176         RUNNER_ASSERT_MSG(false, "Wrong exception found");
 177     }
 178 }
 179
 180 /*
 181 Name: SqlConnection_Not_Connected
 182 Description: tests connection to not existing database without Lucene option
 183 Expected: exception throw
 184 */
 185 RUNNER_TEST(SqlConnection_Not_Connected)
 186 {
 187     Try {
 188         DPL::DB::SqlConnection connection("/notexisitingdirectiory/foo",
 189                                           DPL::DB::SqlConnection::Flag::NotDefined,
 190                                           DPL::DB::SqlConnection::Flag::RW);
 191         RUNNER_ASSERT_MSG(false,
 192                           "connection should throw on accessing "
 193                           "nonexistent file as a database");
 194     }
 195     Catch(DPL::DB::SqlConnection::Exception::ConnectionBroken)
 196     {
 197         RUNNER_ASSERT(true);
 198     } catch (DPL::Exception) {
 199         RUNNER_ASSERT_MSG(false, "Wrong exception found");
 200     }
 201 }
 202
 203 /*
 204 Name: SqlConnection_Null_Query
 205 Description: tests resistance to passing NULL as query in ExecCommand
 206 Expected: exception throw
 207 */
 208 RUNNER_TEST(SqlConnection_Null_Query)
 209 {
 210     DPL::DB::SqlConnection connection(PATH_DB,
 211                                       DPL::DB::SqlConnection::Flag::UseLucene,
 212                                       DPL::DB::SqlConnection::Flag::RW);
 213     Try
 214     {
 215         connection.ExecCommand(NULL);
 216         RUNNER_ASSERT_MSG(false,
 217                           "Null pointer should not be accepted");
 218     }
 219     Catch(DPL::DB::SqlConnection::Exception::SyntaxError)
 220     {
 221         RUNNER_ASSERT(true);
 222     } catch (DPL::Exception) {
 223         RUNNER_ASSERT_MSG(false, "Wrong exception found");
 224     }
 225 }
 226
 227 /*
 228 Name: SqlConnection_Bad_Query
 229 Description: tests resistance to passing trash as query in ExecCommand
 230 Expected: exception throw
 231 */
 232 RUNNER_TEST(SqlConnection_Bad_Query)
 233 {
 234     DPL::DB::SqlConnection connection(PATH_DB,
 235                                       DPL::DB::SqlConnection::Flag::UseLucene,
 236                                       DPL::DB::SqlConnection::Flag::RW);
 237     Try
 238     {
 239         connection.ExecCommand("Some stupid string");
 240         RUNNER_ASSERT_MSG(false, "This string should not be accepted");
 241     }
 242     Catch(DPL::DB::SqlConnection::Exception::SyntaxError)
 243     {
 244         RUNNER_ASSERT(true);
 245     } catch (DPL::Exception) {
 246         RUNNER_ASSERT_MSG(false, "Wrong exception found");
 247     }
 248 }
 249
 250 /*
 251 Name: SqlConnection_IsNull
 252 Description: tests IsColumnNull function
 253 Expected: Function returns correct values
 254 */
 255 RUNNER_TEST(SqlConnection_IsNull)
 256 {
 257     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 258             DPL::DB::SqlConnection::Flag::RW);
 259
 260     connection.ExecCommand("CREATE TABLE testNull(value INT8);");
 261
 262     connection.ExecCommand("INSERT INTO testNull VALUES (NULL);");
 263     connection.ExecCommand("INSERT INTO testNull VALUES (0);");
 264
 265     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 266             "SELECT value FROM testNull");
 267
 268     RUNNER_ASSERT(selectCommand->Step());
 269     RUNNER_ASSERT(selectCommand->IsColumnNull(0));
 270     RUNNER_ASSERT(selectCommand->Step());
 271     RUNNER_ASSERT(!selectCommand->IsColumnNull(0));
 272     selectCommand->Reset();
 273
 274     connection.ExecCommand("DROP TABLE testNull;");
 275 }
 276
 277 /*
 278 Name: SqlConnection_Int8
 279 Description: tests bind and getColumn functions for Int8
 280 Expected: Functions returns correct values
 281 */
 282 RUNNER_TEST(SqlConnection_Int8)
 283 {
 284     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 285             DPL::DB::SqlConnection::Flag::RW);
 286
 287     connection.ExecCommand("CREATE TABLE testInt8(value INT8);");
 288
 289     DPL::DB::SqlConnection::DataCommandAutoPtr insertCommand = connection.PrepareDataCommand(
 290             "INSERT INTO testInt8 VALUES (?)");
 291
 292     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 293             "SELECT value FROM testInt8");
 294
 295     insertCommand->BindInt8(1, 127);
 296     RUNNER_ASSERT(!insertCommand->Step());
 297     insertCommand->Reset();
 298
 299     insertCommand->BindInt8(1, boost::optional<int8_t>(-127));
 300     RUNNER_ASSERT(!insertCommand->Step());
 301     insertCommand->Reset();
 302
 303     RUNNER_ASSERT(selectCommand->Step());
 304     RUNNER_ASSERT(selectCommand->GetColumnInt8(0) == 127);
 305     RUNNER_ASSERT(selectCommand->Step());
 306     RUNNER_ASSERT(selectCommand->GetColumnOptionalInt8(0) == static_cast<int8_t>(-127));
 307     selectCommand->Reset();
 308
 309     connection.ExecCommand("DROP TABLE testInt8;");
 310 }
 311
 312 /*
 313 Name: SqlConnection_Int16
 314 Description: tests bind and getColumn functions for Int16
 315 Expected: Functions returns correct values
 316 */
 317 RUNNER_TEST(SqlConnection_Int16)
 318 {
 319     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 320             DPL::DB::SqlConnection::Flag::RW);
 321
 322     connection.ExecCommand("CREATE TABLE testInt16(value INT16);");
 323
 324     DPL::DB::SqlConnection::DataCommandAutoPtr insertCommand = connection.PrepareDataCommand(
 325             "INSERT INTO testInt16 VALUES (?)");
 326
 327     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 328             "SELECT value FROM testInt16");
 329
 330     insertCommand->BindInt16(1, (int16_t)0xFFFF);
 331     RUNNER_ASSERT(!insertCommand->Step());
 332     insertCommand->Reset();
 333
 334     insertCommand->BindInt16(1, boost::optional<int16_t>((int16_t)0x8000));
 335     RUNNER_ASSERT(!insertCommand->Step());
 336     insertCommand->Reset();
 337
 338     RUNNER_ASSERT(selectCommand->Step());
 339     RUNNER_ASSERT(selectCommand->GetColumnInt16(0) == (int16_t)0xFFFF);
 340     RUNNER_ASSERT(selectCommand->Step());
 341     RUNNER_ASSERT(selectCommand->GetColumnOptionalInt16(0) == (int16_t)0x8000);
 342     selectCommand->Reset();
 343
 344     connection.ExecCommand("DROP TABLE testInt16;");
 345 }
 346
 347 /*
 348 Name: SqlConnection_Int32
 349 Description: tests bind and getColumn functions for Int32
 350 Expected: Functions returns correct values
 351 */
 352 RUNNER_TEST(SqlConnection_Int32)
 353 {
 354     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 355             DPL::DB::SqlConnection::Flag::RW);
 356
 357     connection.ExecCommand("CREATE TABLE testInt32(value INT32);");
 358
 359     DPL::DB::SqlConnection::DataCommandAutoPtr insertCommand = connection.PrepareDataCommand(
 360             "INSERT INTO testInt32 VALUES (?)");
 361
 362     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 363             "SELECT value FROM testInt32");
 364
 365     insertCommand->BindInt32(1, 0xFFFFFFFF);
 366     RUNNER_ASSERT(!insertCommand->Step());
 367     insertCommand->Reset();
 368
 369     insertCommand->BindInt32(1, boost::optional<int32_t>(0x80000000));
 370     RUNNER_ASSERT(!insertCommand->Step());
 371     insertCommand->Reset();
 372
 373     RUNNER_ASSERT(selectCommand->Step());
 374     RUNNER_ASSERT(selectCommand->GetColumnInt32(0) == (int32_t)0xFFFFFFFF);
 375     RUNNER_ASSERT(selectCommand->Step());
 376     RUNNER_ASSERT(selectCommand->GetColumnOptionalInt32(0) == static_cast<int32_t>(0x80000000));
 377     selectCommand->Reset();
 378
 379     connection.ExecCommand("DROP TABLE testInt32;");
 380 }
 381
 382 /*
 383 Name: SqlConnection_Int64
 384 Description: tests bind and getColumn functions for Int64
 385 Expected: Functions returns correct values
 386 */
 387 RUNNER_TEST(SqlConnection_Int64)
 388 {
 389     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 390             DPL::DB::SqlConnection::Flag::RW);
 391
 392     connection.ExecCommand("CREATE TABLE testInt64(value INT64);");
 393
 394     DPL::DB::SqlConnection::DataCommandAutoPtr insertCommand = connection.PrepareDataCommand(
 395             "INSERT INTO testInt64 VALUES (?)");
 396
 397     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 398             "SELECT value FROM testInt64");
 399
 400     insertCommand->BindInt64(1, 0xFFFFFFFFFFFFFFFF);
 401     RUNNER_ASSERT(!insertCommand->Step());
 402     insertCommand->Reset();
 403
 404     insertCommand->BindInt64(1, boost::optional<int64_t>(0x8000000000000000));
 405     RUNNER_ASSERT(!insertCommand->Step());
 406     insertCommand->Reset();
 407
 408
 409     RUNNER_ASSERT(selectCommand->Step());
 410     RUNNER_ASSERT(selectCommand->GetColumnInt64(0) == (int64_t)0xFFFFFFFFFFFFFFFF);
 411     RUNNER_ASSERT(selectCommand->Step());
 412     RUNNER_ASSERT(selectCommand->GetColumnOptionalInt64(0) == static_cast<int64_t>(0x8000000000000000));
 413     selectCommand->Reset();
 414
 415     connection.ExecCommand("DROP TABLE testInt64;");
 416 }
 417
 418 /*
 419 Name: SqlConnection_Float
 420 Description: tests bind and getColumn functions for float
 421 Expected: Functions returns correct values
 422 */
 423 RUNNER_TEST(SqlConnection_Float)
 424 {
 425     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 426             DPL::DB::SqlConnection::Flag::RW);
 427
 428     connection.ExecCommand("CREATE TABLE testFloat(value FLOAT);");
 429
 430     DPL::DB::SqlConnection::DataCommandAutoPtr insertCommand = connection.PrepareDataCommand(
 431             "INSERT INTO testFloat VALUES (?)");
 432
 433     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 434             "SELECT value FROM testFloat");
 435
 436     insertCommand->BindFloat(1, 10.2545f);
 437     RUNNER_ASSERT(!insertCommand->Step());
 438     insertCommand->Reset();
 439
 440     insertCommand->BindFloat(1, boost::optional<float>(-90.6788f));
 441     RUNNER_ASSERT(!insertCommand->Step());
 442     insertCommand->Reset();
 443
 444     RUNNER_ASSERT(selectCommand->Step());
 445     float value = selectCommand->GetColumnFloat(0);
 446     RUNNER_ASSERT(value > 10.2544 && value < 10.2546);
 447     RUNNER_ASSERT(selectCommand->Step());
 448     value = *selectCommand->GetColumnOptionalFloat(0);
 449     RUNNER_ASSERT(value > -90.6789 && value < -90.6787);
 450     selectCommand->Reset();
 451
 452     connection.ExecCommand("DROP TABLE testFloat;");
 453 }
 454
 455 /*
 456 Name: SqlConnection_Double
 457 Description: tests bind and getColumn functions for double
 458 Expected: Functions returns correct values
 459 */
 460 RUNNER_TEST(SqlConnection_Double)
 461 {
 462     DPL::DB::SqlConnection connection(PATH_DB, DPL::DB::SqlConnection::Flag::UseLucene,
 463             DPL::DB::SqlConnection::Flag::RW);
 464
 465     connection.ExecCommand("CREATE TABLE testDouble(value DOUBLE);");
 466
 467     DPL::DB::SqlConnection::DataCommandAutoPtr insertCommand = connection.PrepareDataCommand(
 468             "INSERT INTO testDouble VALUES (?)");
 469
 470     DPL::DB::SqlConnection::DataCommandAutoPtr selectCommand = connection.PrepareDataCommand(
 471             "SELECT value FROM testDouble");
 472
 473     insertCommand->BindDouble(1, 10.2545);
 474     RUNNER_ASSERT(!insertCommand->Step());
 475     insertCommand->Reset();
 476
 477     insertCommand->BindDouble(1, boost::optional<double>(-90.6788));
 478     RUNNER_ASSERT(!insertCommand->Step());
 479     insertCommand->Reset();
 480
 481     RUNNER_ASSERT(selectCommand->Step());
 482     double value = selectCommand->GetColumnDouble(0);
 483     RUNNER_ASSERT(value > 10.2544 && value < 10.2546);
 484     RUNNER_ASSERT(selectCommand->Step());
 485     value = *selectCommand->GetColumnOptionalDouble(0);
 486     RUNNER_ASSERT(value > -90.6789 && value < -90.6787);
 487     selectCommand->Reset();
 488
 489     connection.ExecCommand("DROP TABLE testDouble;");
 490 }