tests/db/test_orm.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 #include <dpl/test/test_runner.h>
  17 #include <dpl/foreach.h>
  18 #include <dpl/db/thread_database_support.h>
  19 #include <generator_dpl_orm_test.h>
  20 #include <sstream>
  21
  22 const char* PATH_DB = "/opt/share/wrt/wrt-commons/tests/db/dpl_orm_test.db";
  23
  24 //utils
  25
  26 #define TEST_REPETITION 16
  27
  28 class SmartAttach
  29 {
  30   public:
  31
  32     SmartAttach(bool autoattach = true) :
  33         m_interface(PATH_DB,
  34                     DPL::DB::SqlConnection::Flag::UseLucene),
  35         m_autoattach(autoattach)
  36     {
  37         if (m_autoattach) {
  38             m_interface.AttachToThread(DPL::DB::SqlConnection::Flag::RW);
  39         }
  40     }
  41
  42     ~SmartAttach()
  43     {
  44         if (m_autoattach) {
  45             m_interface.DetachFromThread();
  46         }
  47     }
  48
  49     DPL::DB::ThreadDatabaseSupport* get()
  50     {
  51         return &m_interface;
  52     }
  53
  54   private:
  55     DPL::DB::ThreadDatabaseSupport m_interface;
  56     bool m_autoattach;
  57 };
  58
  59 template<typename ContainerType1, typename ContainerType2>
  60 bool ContainerContentsEqual(const ContainerType1& container1,
  61                             const ContainerType2& container2)
  62 {
  63     using namespace DPL::DB::ORM::dpl_orm_test::TestTableInsert;
  64     typedef std::set<typename ContainerType1::value_type> Set1;
  65     typedef std::set<typename ContainerType2::value_type> Set2;
  66     Set1 set1(container1.begin(), container1.end());
  67     Set2 set2(container2.begin(), container2.end());
  68
  69     for (typename Set1::iterator it = set1.begin();
  70          it != set1.end();
  71          it++)
  72     {
  73         LogDebug("Set1 element: " << *it);
  74     }
  75
  76     for (typename Set2::iterator it = set2.begin(); it != set2.end(); it++) {
  77         LogDebug("Set2 element: " << *it);
  78     }
  79
  80     return set1 == set2;
  81 }
  82
  83 template<typename T>
  84 std::list<T> makeList(const T& a, const T& b)
  85 {
  86     std::list<T> list;
  87     list.push_back(a);
  88     list.push_back(b);
  89     return list;
  90 }
  91
  92 //tests
  93
  94 RUNNER_TEST_GROUP_INIT(DPL)
  95
  96 /*
  97 Name: ORM_SelectSingleValue
  98 Description: tests quering single value of single row from database
  99 Expected: Values should match those which were prepared
 100 */
 101 RUNNER_TEST(ORM_SelectSingleValue)
 102 {
 103     SmartAttach interface;
 104     using namespace DPL::DB::ORM;
 105     using namespace DPL::DB::ORM::dpl_orm_test;
 106     //Getting each column
 107     {
 108         TestTable::Select select(interface.get());
 109         select.Where(Equals<TestTable::ColumnInt>(8));
 110         int result;
 111         RUNNER_ASSERT_MSG((result =
 112                                *select.GetSingleValue<TestTable::ColumnOptInt>())
 113                           == 6, "Got " <<
 114                           result);
 115     }
 116     {
 117         TestTable::Select select(interface.get());
 118         select.Where(Equals<TestTable::ColumnInt>(8));
 119         DPL::String result;
 120         RUNNER_ASSERT_MSG((result =
 121                                *select.GetSingleValue<TestTable::ColumnOptText>(
 122                                    )) == L"seven",
 123                           "Got " << result);
 124     }
 125     {
 126         TestTable::Select select(interface.get());
 127         select.Where(Equals<TestTable::ColumnInt>(8));
 128         int result;
 129         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt>(
 130                                    )) == 8, "Got " << result);
 131     }
 132     {
 133         TestTable::Select select(interface.get());
 134         select.Where(Equals<TestTable::ColumnInt>(8));
 135         int result;
 136         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 137                                    )) == 9, "Got " << result);
 138     }
 139     {
 140         TestTable::Select select(interface.get());
 141         select.Where(Equals<TestTable::ColumnInt>(8));
 142         DPL::String result;
 143         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnText>(
 144                                    )) == L"ten", "Got " << result);
 145     }
 146
 147     //Where on each column
 148     {
 149         TestTable::Select select(interface.get());
 150         select.Where(Equals<TestTable::ColumnOptInt>(6));
 151         int result;
 152         RUNNER_ASSERT_MSG((result =
 153                                *select.GetSingleValue<TestTable::ColumnOptInt>())
 154                           == 6, "Got " <<
 155                           result);
 156     }
 157     {
 158         TestTable::Select select(interface.get());
 159         select.Where(Equals<TestTable::ColumnOptText>(DPL::String(L"seven")));
 160         DPL::String result;
 161         RUNNER_ASSERT_MSG((result =
 162                                *select.GetSingleValue<TestTable::ColumnOptText>(
 163                                    )) == L"seven",
 164                           "Got " << result);
 165     }
 166     {
 167         TestTable::Select select(interface.get());
 168         select.Where(Equals<TestTable::ColumnInt>(8));
 169         int result;
 170         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt>(
 171                                    )) == 8, "Got " << result);
 172     }
 173     {
 174         TestTable::Select select(interface.get());
 175         select.Where(Equals<TestTable::ColumnInt2>(9));
 176         int result;
 177         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 178                                    )) == 9, "Got " << result);
 179     }
 180     {
 181         TestTable::Select select(interface.get());
 182         select.Where(Equals<TestTable::ColumnText>(L"ten"));
 183         DPL::String result;
 184         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnText>(
 185                                    )) == L"ten", "Got " << result);
 186     }
 187 }
 188
 189 /*
 190 Name: ORM_SelectSingleRow
 191 Description: tests quering single row from database
 192 Expected: Values should match those which were prepared
 193 */
 194 RUNNER_TEST(ORM_SelectSingleRow)
 195 {
 196     SmartAttach interface;
 197     using namespace DPL::DB::ORM;
 198     using namespace DPL::DB::ORM::dpl_orm_test;
 199     {
 200         TestTable::Select select(interface.get());
 201         select.Where(Equals<TestTable::ColumnInt>(3));
 202         TestTable::Row result = select.GetSingleRow();
 203         TestTable::Row expected;
 204         expected.Set_ColumnOptInt(1);
 205         expected.Set_ColumnOptText(DPL::String(L"two"));
 206         expected.Set_ColumnInt(3);
 207         expected.Set_ColumnInt2(4);
 208         expected.Set_ColumnText(L"five");
 209         RUNNER_ASSERT_MSG(result == expected, "Got " << result);
 210     }
 211
 212     {
 213         TestTable::Select select(interface.get());
 214         select.Where(Equals<TestTable::ColumnOptText>(DPL::String(L"seven")));
 215         TestTable::Row result = select.GetSingleRow();
 216         TestTable::Row expected;
 217         expected.Set_ColumnOptInt(6);
 218         expected.Set_ColumnOptText(DPL::String(L"seven"));
 219         expected.Set_ColumnInt(8);
 220         expected.Set_ColumnInt2(9);
 221         expected.Set_ColumnText(L"ten");
 222         RUNNER_ASSERT_MSG(result == expected, "Got " << result);
 223     }
 224 }
 225
 226 /*
 227 Name: ORM_SelectRowList
 228 Description: tests quering multiple row from database
 229 Expected: Values should match those which were prepared
 230 */
 231 RUNNER_TEST(ORM_SelectRowList)
 232 {
 233     SmartAttach interface;
 234     using namespace DPL::DB::ORM;
 235     using namespace DPL::DB::ORM::dpl_orm_test;
 236     {
 237         TestTable::Select select(interface.get());
 238         select.Where(Equals<TestTable::ColumnInt>(3));
 239         std::list<TestTable::Row> result = select.GetRowList();
 240         RUNNER_ASSERT_MSG(result.size() == 1, "Got " << result.size());
 241
 242         TestTable::Row expected;
 243         expected.Set_ColumnOptInt(1);
 244         expected.Set_ColumnOptText(DPL::String(L"two"));
 245         expected.Set_ColumnInt(3);
 246         expected.Set_ColumnInt2(4);
 247         expected.Set_ColumnText(L"five");
 248         RUNNER_ASSERT_MSG(*(result.begin()) == expected, "Got " <<
 249                           *(result.begin()) );
 250     }
 251
 252     {
 253         TestTable::Select select(interface.get());
 254         select.Where(Equals<TestTable::ColumnOptText>(DPL::String(L"seven")));
 255         std::list<TestTable::Row> result = select.GetRowList();
 256         RUNNER_ASSERT_MSG(result.size() == 1, "Got " << result.size());
 257
 258         TestTable::Row expected;
 259         expected.Set_ColumnOptInt(6);
 260         expected.Set_ColumnOptText(DPL::String(L"seven"));
 261         expected.Set_ColumnInt(8);
 262         expected.Set_ColumnInt2(9);
 263         expected.Set_ColumnText(L"ten");
 264         RUNNER_ASSERT_MSG(*(result.begin()) == expected, "Got " <<
 265                           *(result.begin()) );
 266     }
 267
 268     {
 269         TestTable::Select select(interface.get());
 270         select.Where(Equals<TestTable::ColumnInt>(99));
 271         std::list<TestTable::Row> result = select.GetRowList();
 272
 273         TestTable::Row expected1;
 274         expected1.Set_ColumnInt(99);
 275         expected1.Set_ColumnInt2(11);
 276         expected1.Set_ColumnText(L"twelve");
 277
 278         TestTable::Row expected2;
 279         expected2.Set_ColumnInt(99);
 280         expected2.Set_ColumnInt2(13);
 281         expected2.Set_ColumnText(L"fourteen");
 282
 283         RUNNER_ASSERT(ContainerContentsEqual(makeList(expected1,
 284                                                       expected2), result));
 285     }
 286 }
 287
 288 /*
 289 Name: ORM_SelectValueList
 290 Description: tests quering single column from multiple row from database
 291 Expected: Values should match those which were prepared
 292 */
 293 RUNNER_TEST(ORM_SelectValueList)
 294 {
 295     SmartAttach interface;
 296     using namespace DPL::DB::ORM;
 297     using namespace DPL::DB::ORM::dpl_orm_test;
 298     //Getting each column
 299     {
 300         TestTable::Select select(interface.get());
 301         select.Where(Is<TestTable::ColumnOptInt>(DPL::Optional<int>::Null));
 302         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 303                                                                      ColumnInt>(),
 304                                              makeList(99, 99)));
 305     }
 306     {
 307         TestTable::Select select(interface.get());
 308         select.Where(Is<TestTable::ColumnOptInt>(DPL::Optional<int>::Null));
 309         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 310                                                                      ColumnInt2>(),
 311                                              makeList(11, 13)));
 312     }
 313     {
 314         TestTable::Select select(interface.get());
 315         select.Where(Is<TestTable::ColumnOptInt>(DPL::Optional<int>::Null));
 316         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 317                                                                      ColumnText>(),
 318                                              makeList(DPL::String(L"twelve"),
 319                                                       DPL::String(L"fourteen"))));
 320     }
 321     {
 322         TestTable::Select select(interface.get());
 323         select.Where(Is<TestTable::ColumnOptInt>(DPL::Optional<int>::Null));
 324         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 325                                                                      ColumnOptText>(),
 326                                              makeList(DPL::Optional<DPL::String>
 327                                                           ::Null,
 328                                                       DPL::Optional<DPL::String>
 329                                                           ::Null)));
 330     }
 331
 332     //Where on each column
 333     {
 334         TestTable::Select select(interface.get());
 335         select.Where(Is<TestTable::ColumnOptInt>(DPL::Optional<int>::Null));
 336         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 337                                                                      ColumnInt2>(),
 338                                              makeList(11, 13)));
 339     }
 340     {
 341         TestTable::Select select(interface.get());
 342         select.Where(Is<TestTable::ColumnOptText>(DPL::Optional<DPL::String>::
 343                                                       Null));
 344         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 345                                                                      ColumnInt2>(),
 346                                              makeList(11, 13)));
 347     }
 348     {
 349         TestTable::Select select(interface.get());
 350         select.Where(Is<TestTable::ColumnInt>(99));
 351         RUNNER_ASSERT(ContainerContentsEqual(select.GetValueList<TestTable::
 352                                                                      ColumnInt2>(),
 353                                              makeList(11, 13)));
 354     }
 355 }
 356
 357 /*
 358 Name: ORM_MultipleCalls
 359 Description: tests sequnece of different queries
 360 Expected: Values should match those which were prepared
 361 */
 362 RUNNER_TEST(ORM_MultipleCalls)
 363 {
 364     for (int j = 0; j < TEST_REPETITION; j++) {
 365         for (int i = 0; i < TEST_REPETITION; i++) {
 366             ORM_SelectSingleValue();
 367         }
 368
 369         for (int i = 0; i < TEST_REPETITION; i++) {
 370             ORM_SelectSingleRow();
 371         }
 372
 373         for (int i = 0; i < TEST_REPETITION; i++) {
 374             ORM_SelectRowList();
 375         }
 376
 377         for (int i = 0; i < TEST_REPETITION; i++) {
 378             ORM_SelectValueList();
 379         }
 380     }
 381 }
 382
 383 /*
 384 Name: ORM_Insert
 385 Description: tests insering rows into database
 386 Expected: Values should be inserted
 387 */
 388 RUNNER_TEST(ORM_Insert)
 389 {
 390     SmartAttach interface;
 391     using namespace DPL::DB::ORM;
 392     using namespace DPL::DB::ORM::dpl_orm_test;
 393
 394     TestTableInsert::Select select1(interface.get());
 395     std::list<int> resultList = select1.GetValueList<TestTableInsert::ColumnInt>();
 396     RUNNER_ASSERT_MSG(resultList.empty(),
 397                       "Returned list has wrong size: " << resultList.size());
 398
 399     std::list<TestTableInsert::Row> list;
 400
 401     TestTableInsert::Insert insert(interface.get());
 402     TestTableInsert::Row row;
 403     row.Set_ColumnOptInt(1);
 404     row.Set_ColumnInt2(2);
 405     row.Set_ColumnText(L"three");
 406     insert.Values(row);
 407     insert.Execute();
 408
 409     row.Set_ColumnInt(99);
 410     list.push_back(row);
 411     {
 412         TestTableInsert::Select select2(interface.get());
 413         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 414                               select2.GetRowList(),
 415                               list), "Returned list doesn't match.");
 416     }
 417
 418     TestTableInsert::Insert insert2(interface.get());
 419     TestTableInsert::Row row2;
 420     row2.Set_ColumnInt(4);
 421     row2.Set_ColumnInt2(5);
 422     row2.Set_ColumnText(L"six");
 423     insert2.Values(row2);
 424     insert2.Execute();
 425
 426     list.push_back(row2);
 427     {
 428         TestTableInsert::Select select(interface.get());
 429         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 430                               select.GetRowList(),
 431                               list), "Returned list doesn't match.");
 432     }
 433
 434     TestTableInsert::Insert insert3(interface.get());
 435     TestTableInsert::Row row3;
 436     row3.Set_ColumnOptInt(1);
 437     row3.Set_ColumnInt2(7);
 438     row3.Set_ColumnText(L"eight");
 439     insert3.Values(row3);
 440     insert3.Execute();
 441
 442     row3.Set_ColumnInt(99);
 443     list.push_back(row3);
 444     {
 445         TestTableInsert::Select select3(interface.get());
 446         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 447                               select3.GetRowList(),
 448                               list), "Returned list doesn't match.");
 449     }
 450
 451     TestTableInsert::Insert insert4(interface.get());
 452     TestTableInsert::Row row4;
 453     row4.Set_ColumnOptInt(9);
 454     row4.Set_ColumnInt2(10);
 455     row4.Set_ColumnText(L"eleven");
 456     insert4.Values(row4);
 457     insert4.Execute();
 458
 459     row4.Set_ColumnInt(99);
 460     list.push_back(row4);
 461     {
 462         TestTableInsert::Select select4(interface.get());
 463         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 464                               select4.GetRowList(),
 465                               list), "Returned list doesn't match.");
 466     }
 467
 468     // restore original table state
 469     {
 470         TestTableInsert::Delete del(interface.get());
 471         del.Execute();
 472
 473         TestTableInsert::Select select(interface.get());
 474         RUNNER_ASSERT(select.GetRowList().size() == 0);
 475     }
 476 }
 477
 478 /*
 479 Name: ORM_MultipleBindInsert
 480 Description: repeats ORM_Insert test several times
 481 Expected: Values should be inserted
 482 */
 483 RUNNER_TEST(ORM_MultipleBindInsert)
 484 {
 485     for (int i = 0; i < TEST_REPETITION; i++) {
 486         ORM_Insert();
 487     }
 488 }
 489
 490 /*
 491 Name: ORM_Delete
 492 Description: tests deleting rows from database
 493 Expected: deleted rows should not exist
 494 */
 495 RUNNER_TEST(ORM_Delete)
 496 {
 497     SmartAttach interface;
 498     using namespace DPL::DB::ORM;
 499     using namespace DPL::DB::ORM::dpl_orm_test;
 500     TestTableDelete::Select selectStart(interface.get());
 501     selectStart.OrderBy(DPL::TypeListDecl<OrderingAscending<TestTableDelete::
 502                                                                 ColumnInt2> >());
 503     std::list<TestTableDelete::Row> list = selectStart.GetRowList();
 504     std::list<TestTableDelete::Row> originalList = list;
 505
 506     std::vector<TestTableDelete::Row> vector(list.begin(), list.end());
 507     RUNNER_ASSERT_MSG(
 508         list.size() == 4, "Returned list has wrong size: " << list.size());
 509
 510     typedef DPL::String S;
 511
 512     //no-act deletes
 513     {
 514         TestTableDelete::Delete del(interface.get());
 515         del.Where(And(Equals<TestTableDelete::ColumnOptInt>(1),
 516                       Equals<TestTableDelete::ColumnOptText>(S(L"seven"))));
 517         del.Execute();
 518
 519         TestTableDelete::Select select(interface.get());
 520         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 521                               select.GetRowList(),
 522                               list), "Returned list doesn't match.");
 523     }
 524
 525     {
 526         TestTableDelete::Delete del(interface.get());
 527         del.Where(And(Equals<TestTableDelete::ColumnOptInt>(6),
 528                       Equals<TestTableDelete::ColumnOptText>(S(L"two"))));
 529         del.Execute();
 530
 531         TestTableDelete::Select select(interface.get());
 532         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 533                               select.GetRowList(),
 534                               list), "Returned list doesn't match.");
 535     }
 536
 537     {
 538         TestTableDelete::Delete del(interface.get());
 539         del.Where(Equals<TestTableDelete::ColumnInt2>(10));
 540         del.Execute();
 541
 542         TestTableDelete::Select select(interface.get());
 543         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 544                               select.GetRowList(),
 545                               list), "Returned list doesn't match.");
 546     }
 547
 548     //act deletes
 549     {
 550         list.remove(vector[1]);
 551
 552         TestTableDelete::Delete del(interface.get());
 553         del.Where(And(Equals<TestTableDelete::ColumnOptInt>(6),
 554                       Equals<TestTableDelete::ColumnText>(L"ten")));
 555         del.Execute();
 556
 557         TestTableDelete::Select select(interface.get());
 558         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 559                               select.GetRowList(),
 560                               list), "Returned list doesn't match.");
 561     }
 562
 563     {
 564         list.remove(vector[2]);
 565         list.remove(vector[3]);
 566
 567         TestTableDelete::Delete del(interface.get());
 568         del.Where(Is<TestTableDelete::ColumnOptText>(DPL::Optional<DPL::String>
 569                                                          ::Null));
 570         del.Execute();
 571
 572         TestTableDelete::Select select(interface.get());
 573         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 574                               select.GetRowList(),
 575                               list), "Returned list doesn't match.");
 576     }
 577
 578     {
 579         TestTableDelete::Delete del(interface.get());
 580         del.Execute();
 581
 582         TestTableDelete::Select select(interface.get());
 583         RUNNER_ASSERT_MSG(
 584             select.GetRowList().size() == 0, "Returned list is not empty");
 585     }
 586
 587     // Restore original table state
 588     // This also tests if multiple different binds for Insert are working
 589     // properly
 590     for (std::list<TestTableDelete::Row>::iterator i = originalList.begin();
 591          i != originalList.end();
 592          ++i)
 593     {
 594         TestTableDelete::Insert insert(interface.get());
 595         insert.Values(*i);
 596         insert.Execute();
 597     }
 598
 599     {
 600         TestTableDelete::Select select(interface.get());
 601         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 602                               select.GetRowList(),
 603                               originalList),
 604                           "Returned list doesn't match.");
 605     }
 606 }
 607
 608 /*
 609 Name: ORM_MultipleBindDelete
 610 Description: repeats ORM_Delete test several times
 611 Expected: Values should be deleted
 612 */
 613 RUNNER_TEST(ORM_MultipleBindDelete)
 614 {
 615     for (int i = 0; i < TEST_REPETITION; i++) {
 616         ORM_Delete();
 617     }
 618 }
 619
 620 /*
 621 Name: ORM_MultipleBindWhere
 622 Description: tests if multiple bind of same query obejct works
 623 Expected: Each bind and execution of query should be correct
 624 */
 625 RUNNER_TEST(ORM_MultipleBindWhere)
 626 {
 627     SmartAttach interface;
 628     using namespace DPL::DB::ORM;
 629     using namespace DPL::DB::ORM::dpl_orm_test;
 630     {
 631         TestTable::Select select(interface.get());
 632         int result;
 633         select.Where(Equals<TestTable::ColumnInt>(8));
 634         RUNNER_ASSERT_MSG((result =
 635                                *select.GetSingleValue<TestTable::ColumnOptInt>())
 636                           == 6, "Got " <<
 637                           result);
 638
 639         select.Where(Equals<TestTable::ColumnInt>(3));
 640         RUNNER_ASSERT_MSG((result =
 641                                *select.GetSingleValue<TestTable::ColumnOptInt>())
 642                           == 1, "Got " <<
 643                           result);
 644
 645         select.Where(Equals<TestTable::ColumnInt>(8));
 646         RUNNER_ASSERT_MSG((result =
 647                                *select.GetSingleValue<TestTable::ColumnOptInt>())
 648                           == 6, "Got " <<
 649                           result);
 650
 651         select.Where(Equals<TestTable::ColumnInt>(3));
 652         RUNNER_ASSERT_MSG((result =
 653                                *select.GetSingleValue<TestTable::ColumnOptInt>())
 654                           == 1, "Got " <<
 655                           result);
 656     }
 657
 658     {
 659         TestTable::Select select(interface.get());
 660         int result;
 661         select.Where(And(Equals<TestTable::ColumnInt>(99),
 662                          Equals<TestTable::ColumnText>(L"fourteen")));
 663         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 664                                    )) == 13, "Got " << result);
 665
 666         select.Where(And(Equals<TestTable::ColumnInt>(99),
 667                          Equals<TestTable::ColumnText>(L"twelve")));
 668         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 669                                    )) == 11, "Got " << result);
 670
 671         select.Where(And(Equals<TestTable::ColumnInt>(99),
 672                          Equals<TestTable::ColumnText>(L"fourteen")));
 673         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 674                                    )) == 13, "Got " << result);
 675
 676         select.Where(And(Equals<TestTable::ColumnInt>(99),
 677                          Equals<TestTable::ColumnText>(L"twelve")));
 678         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 679                                    )) == 11, "Got " << result);
 680     }
 681
 682     {
 683         TestTable::Select select(interface.get());
 684         int result;
 685         select.Where(And(Equals<TestTable::ColumnText>(L"fourteen"),
 686                          Equals<TestTable::ColumnInt>(99)));
 687         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 688                                    )) == 13, "Got " << result);
 689
 690         select.Where(And(Equals<TestTable::ColumnText>(L"twelve"),
 691                          Equals<TestTable::ColumnInt>(99)));
 692         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 693                                    )) == 11, "Got " << result);
 694
 695         select.Where(And(Equals<TestTable::ColumnText>(L"fourteen"),
 696                          Equals<TestTable::ColumnInt>(99)));
 697         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 698                                    )) == 13, "Got " << result);
 699
 700         select.Where(And(Equals<TestTable::ColumnText>(L"twelve"),
 701                          Equals<TestTable::ColumnInt>(99)));
 702         RUNNER_ASSERT_MSG((result = select.GetSingleValue<TestTable::ColumnInt2>(
 703                                    )) == 11, "Got " << result);
 704     }
 705 }
 706
 707 /*
 708 Name: ORM_Update
 709 Description: tests rows update in database
 710 Expected: Successful update
 711 */
 712 RUNNER_TEST(ORM_Update)
 713 {
 714     SmartAttach interface;
 715     using namespace DPL::DB::ORM;
 716     using namespace DPL::DB::ORM::dpl_orm_test;
 717
 718     std::list<TestTableInsert::Row> list;
 719
 720     TestTableInsert::Delete del(interface.get());
 721     del.Execute();
 722
 723     // INSERT
 724     {
 725         TestTableInsert::Insert insert(interface.get());
 726         TestTableInsert::Row row;
 727         row.Set_ColumnOptInt(5);
 728         row.Set_ColumnInt2(2);
 729         row.Set_ColumnText(L"two");
 730         insert.Values(row);
 731         insert.Execute();
 732
 733         row.Set_ColumnInt(99);
 734         list.push_back(row);
 735     }
 736     {
 737         TestTableInsert::Insert insert(interface.get());
 738         TestTableInsert::Row row;
 739         row.Set_ColumnOptInt(1);
 740         row.Set_ColumnInt2(2);
 741         row.Set_ColumnText(L"three");
 742         insert.Values(row);
 743         insert.Execute();
 744
 745         row.Set_ColumnInt(99);
 746         list.push_back(row);
 747     }
 748     {
 749         TestTableInsert::Insert insert(interface.get());
 750         TestTableInsert::Row row;
 751         row.Set_ColumnOptInt(2);
 752         row.Set_ColumnInt2(3);
 753         row.Set_ColumnText(L"three");
 754         insert.Values(row);
 755         insert.Execute();
 756
 757         row.Set_ColumnInt(99);
 758         list.push_back(row);
 759
 760         // CHECK
 761         TestTableInsert::Select select(interface.get());
 762         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 763                               select.GetRowList(),
 764                               list), "Returned list doesn't match.");
 765     }
 766     {
 767         // UPDATE - no rows
 768         TestTableInsert::Update update(interface.get());
 769         TestTableInsert::Row row;
 770         row.Set_ColumnInt2(4);
 771         row.Set_ColumnText(L"four");
 772         update.Values(row);
 773         update.Where(Equals<TestTableInsert::ColumnInt2>(12));
 774         update.Execute();
 775
 776         // CHECK
 777         TestTableInsert::Select select(interface.get());
 778         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 779                               select.GetRowList(),
 780                               list), "Returned list doesn't match.");
 781     }
 782     {
 783         // UPDATE - one row
 784         TestTableInsert::Update update(interface.get());
 785         TestTableInsert::Row row;
 786         row.Set_ColumnInt2(2);
 787         row.Set_ColumnText(L"four");
 788         update.Values(row);
 789         update.Where(Equals<TestTableInsert::ColumnInt2>(3));
 790         update.Execute();
 791
 792         list.back().Set_ColumnInt2(2);
 793         list.back().Set_ColumnText(L"four");
 794
 795         // CHECK
 796         TestTableInsert::Select select(interface.get());
 797         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 798                               select.GetRowList(),
 799                               list), "Returned list doesn't match.");
 800     }
 801
 802     {
 803         // UPDATE - multiple rows
 804         TestTableInsert::Update update(interface.get());
 805         TestTableInsert::Row row;
 806         row.Set_ColumnText(L"dup");
 807         update.Values(row);
 808         update.Where(Equals<TestTableInsert::ColumnInt2>(2));
 809         update.Execute();
 810
 811         FOREACH(it, list)
 812         {
 813             it->Set_ColumnText(L"dup");
 814         }
 815
 816         // CHECK
 817         TestTableInsert::Select select(interface.get());
 818         RUNNER_ASSERT_MSG(ContainerContentsEqual(
 819                               select.GetRowList(),
 820                               list), "Returned list doesn't match.");
 821     }
 822
 823     // restore original table state
 824     {
 825         TestTableInsert::Delete del2(interface.get());
 826         del2.Execute();
 827
 828         TestTableInsert::Select select(interface.get());
 829         RUNNER_ASSERT(select.GetRowList().size() == 0);
 830     }
 831 }
 832
 833 /*
 834 Name: ORM_MultipleBindUpdate
 835 Description: repeats ORM_Update severl times
 836 Expected: Successful update
 837 */
 838 RUNNER_TEST(ORM_MultipleBindUpdate)
 839 {
 840     for (int i = 0; i < TEST_REPETITION; i++) {
 841         ORM_Update();
 842     }
 843 }
 844
 845 /*
 846 Name: ORM_transactions
 847 Description: checks creation of transation object
 848 Expected: Successful creation of transaction object
 849 */
 850 RUNNER_TEST(ORM_transactions)
 851 {
 852     SmartAttach interface;
 853     DPL::DB::ORM::dpl_orm_test::ScopedTransaction transaction(interface.get());
 854 }
 855
 856 /*
 857 Name: ORM_MultiAttach
 858 Description: checks correct behaviou in case of multiple tries to attach to database
 859 Expected: Methods attaching/dettaching should be prepared for multiple calling
 860 */
 861 RUNNER_TEST(ORM_MultiAttach)
 862 {
 863     SmartAttach interface(false);
 864     RUNNER_ASSERT_MSG(
 865         !interface.get()->IsAttached(), "Is attached, but shouldn't be.");
 866     interface.get()->AttachToThread();
 867     RUNNER_ASSERT_MSG(
 868         interface.get()->IsAttached(), "Isn't attached, but should be.");
 869     interface.get()->AttachToThread();
 870     RUNNER_ASSERT_MSG(
 871         interface.get()->IsAttached(), "Isn't attached, but should be.");
 872     interface.get()->DetachFromThread();
 873     RUNNER_ASSERT_MSG(
 874         interface.get()->IsAttached(), "Isn't attached, but should be.");
 875     interface.get()->DetachFromThread();
 876     RUNNER_ASSERT_MSG(
 877         !interface.get()->IsAttached(), "Is attached, but shouldn't be.");
 878 }
 879
 880 /*
 881 Name: ORM_Join
 882 Description: tests ORM's join operation
 883 Expected: values should insist correct join operation
 884 */
 885 RUNNER_TEST(ORM_Join)
 886 {
 887     SmartAttach interface;
 888     using namespace DPL::DB::ORM;
 889     using namespace DPL::DB::ORM::dpl_orm_test;
 890
 891     typedef DPL::TypeListDecl<TestTableJoin1::TestText,
 892                               TestTableJoin2::TestText2,
 893                               DPL::TypeListGuard>::Type JoinColumns;
 894
 895     /* Test for correct join:
 896      * 5 ids from first table matches 5 ids from second table thus join result
 897      * contains 5 rows */
 898     TestTableJoin1::Select select(interface.get());
 899     select.Join<JoinColumns>(Equal<TestTableJoin1::TestID,
 900                                    TestTableJoin2::TestID>());
 901     std::list<CustomRow<JoinColumns> > rowlist =
 902         select.GetCustomRowList<JoinColumns, CustomRow<JoinColumns> >();
 903
 904     RUNNER_ASSERT_MSG(
 905         rowlist.size() == 5, "Invalid number of rows fetched: " << rowlist.size());
 906
 907     std::string text;
 908     std::ostringstream oss;
 909     int cnt = 0;
 910     FOREACH(rowit, rowlist)
 911     {
 912         cnt++;
 913
 914         text =
 915             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin1::TestText>());
 916         oss << "text val " << cnt;
 917         RUNNER_ASSERT_MSG(text.compare(
 918                               oss.str()) == 0,
 919                           "Invalid value from first column: "
 920                           << text << " expected: " << oss.str());
 921         oss.str(std::string());
 922
 923         text =
 924             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin2::TestText2>());
 925         oss << "text2 " << cnt;
 926         RUNNER_ASSERT_MSG(text.compare(
 927                               oss.str()) == 0,
 928                           "Invalid value from second column: "
 929                           << text << " expected: " << oss.str());
 930         oss.str(std::string());
 931     }
 932     /* Test for empty join:
 933      * None of number values from first table matches ids from second table
 934      * - join result should be empty */
 935     TestTableJoin1::Select select2(interface.get());
 936     select2.Join<JoinColumns>(Equal<TestTableJoin1::TestNumber,
 937                                     TestTableJoin2::TestID>());
 938     rowlist = select2.GetCustomRowList<JoinColumns, CustomRow<JoinColumns> >();
 939
 940     RUNNER_ASSERT_MSG(rowlist.empty(), "Result should be empty but it is not!");
 941
 942     /* Test for "converted" join:
 943      * - join made with int column and text column as keys
 944      * - expected 5 matching rows (one row of 6 should not be matched)*/
 945     TestTableJoin1::Select select3(interface.get());
 946     select3.Join<JoinColumns>(Equal<TestTableJoin1::TestID,
 947                                     TestTableJoin2::TestText1>());
 948     rowlist = select3.GetCustomRowList<JoinColumns, CustomRow<JoinColumns> >();
 949     RUNNER_ASSERT_MSG(
 950         rowlist.size() == 5, "Expected 5 rows while received: " << rowlist.size());
 951     cnt = 0;
 952     FOREACH(rowit, rowlist)
 953     {
 954         cnt++;
 955         // look at last two insertions into TestTableJoin2
 956         // for this skip understanding
 957         if (cnt == 5) {
 958             cnt = 6;
 959         }
 960         text =
 961             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin1::TestText>());
 962         oss << "text val " << cnt;
 963         RUNNER_ASSERT_MSG(text.compare(
 964                               oss.str()) == 0,
 965                           "Invalid value from first column: "
 966                           << text << " expected: " << oss.str() <<
 967                           " iteration: " << cnt);
 968         oss.str(std::string());
 969
 970         text =
 971             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin2::TestText2>());
 972         oss << "text2 " << cnt;
 973         RUNNER_ASSERT_MSG(text.compare(
 974                               oss.str()) == 0,
 975                           "Invalid value from second column: "
 976                           << text << " expected: " << oss.str() <<
 977                           " iteration: " << cnt);
 978         oss.str(std::string());
 979     }
 980
 981     /* Test for join with non-unique nullable columns given as keys*/
 982     typedef DPL::TypeListDecl<TestTableJoin1::TestText,
 983                               TestTableJoin3::TestText33,
 984                               DPL::TypeListGuard>::Type JoinTables2;
 985     TestTableJoin1::Select select4(interface.get());
 986     select4.Join<JoinTables2>(Equal<TestTableJoin1::TestNumber,
 987                                     TestTableJoin3::Value3>());
 988     std::list<CustomRow<JoinTables2> > rowlist2 =
 989         select4.GetCustomRowList<JoinTables2, CustomRow<JoinTables2> >();
 990     RUNNER_ASSERT_MSG(
 991         rowlist2.size() == 4, "Expected 4 rows while received: " <<
 992         rowlist.size());
 993     cnt = 0;
 994     DPL::Optional<DPL::String> optext;
 995     FOREACH(rowit, rowlist2)
 996     {
 997         cnt++;
 998
 999         text =
1000             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin1::TestText>());
1001         // values expected in subsequent (1,2,3,4) iterations: 1 1 2 2
1002         oss << "text val " << (1 + (int)(cnt / 3));
1003         RUNNER_ASSERT_MSG(text.compare(
1004                               oss.str()) == 0,
1005                           "Invalid value from first column: "
1006                           << text << " expected: " << oss.str() <<
1007                           " iteration: " << cnt);
1008         oss.str(std::string());
1009
1010         optext = (*rowit).GetColumnData<TestTableJoin3::TestText33>();
1011         text = DPL::ToUTF8String(*optext);
1012         oss << "test " << cnt;
1013         RUNNER_ASSERT_MSG(text.compare(
1014                               oss.str()) == 0,
1015                           "Invalid value from second column: "
1016                           << text << " expected: " << oss.str() <<
1017                           " iteration: " << cnt);
1018         oss.str(std::string());
1019     }
1020
1021     /* Test for join made on three tables:
1022      * - 3 text columns selected for join
1023      * - Equal made for TestID of (table1 and table2) and (table1 and table3) */
1024     typedef DPL::TypeListDecl<TestTableJoin1::TestText,
1025                               TestTableJoin2::TestText2,
1026                               TestTableJoin3::TestText33,
1027                               DPL::TypeListGuard>::Type Join3Tables;
1028     TestTableJoin1::Select select5(interface.get());
1029     select5.Join<Join3Tables>(Equal<TestTableJoin1::TestID,
1030                                     TestTableJoin2::TestID>());
1031     select5.Join<Join3Tables>(Equal<TestTableJoin1::TestID,
1032                                     TestTableJoin3::TestID>());
1033     std::list<CustomRow<Join3Tables> > rowlist3tab =
1034         select5.GetCustomRowList<Join3Tables, CustomRow<Join3Tables> >();
1035     RUNNER_ASSERT_MSG(
1036         rowlist3tab.size() == 3, "Expected 3 rows while received: " <<
1037         rowlist3tab.size());
1038     cnt = 0;
1039     FOREACH(rowit, rowlist3tab)
1040     {
1041         cnt++;
1042
1043         text =
1044             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin1::TestText>());
1045         oss << "text val " << cnt;
1046         RUNNER_ASSERT_MSG(text.compare(
1047                               oss.str()) == 0,
1048                           "Invalid value from first column: "
1049                           << text << " expected: " << oss.str() <<
1050                           " iteration: " << cnt);
1051         oss.str(std::string());
1052
1053         text =
1054             DPL::ToUTF8String((*rowit).GetColumnData<TestTableJoin2::TestText2>());
1055         oss << "text2 " << cnt;
1056         RUNNER_ASSERT_MSG(text.compare(
1057                               oss.str()) == 0,
1058                           "Invalid value from first column: "
1059                           << text << " expected: " << oss.str() <<
1060                           " iteration: " << cnt);
1061         oss.str(std::string());
1062
1063         optext = (*rowit).GetColumnData<TestTableJoin3::TestText33>();
1064         text = DPL::ToUTF8String(*optext);
1065         oss << "test " << cnt;
1066         RUNNER_ASSERT_MSG(text.compare(
1067                               oss.str()) == 0,
1068                           "Invalid value from second column: "
1069                           << text << " expected: " << oss.str() <<
1070                           " iteration: " << cnt);
1071         oss.str(std::string());
1072     }
1073 }
1074
1075 RUNNER_TEST(ORM_SelectOrderByMultipleColumns)
1076 {
1077     SmartAttach interface;
1078     using namespace DPL::DB::ORM;
1079     using namespace DPL::DB::ORM::dpl_orm_test;
1080     {
1081         TestTableJoin3::Select select(interface.get());
1082
1083         // testing: " ORDER BY Value3 ASC, TestID DESC, TestID ASC"
1084         select.OrderBy(DPL::TypeListDecl<OrderingAscending<TestTableJoin3::
1085                                                                Value3>,
1086                                          OrderingDescending<TestTableJoin3::
1087                                                                 TestText33>,
1088                                          OrderingAscending<TestTableJoin3::
1089                                                                TestID> >());
1090
1091         std::list<TestTableJoin3::Row> result = select.GetRowList();
1092         std::list<TestTableJoin3::Row>::const_iterator iter = result.begin();
1093         { //1 row
1094             RUNNER_ASSERT_MSG(*iter->Get_TestText33() ==
1095                               DPL::FromASCIIString(
1096                                   "test 6"), "Wrong row 1 order");
1097             RUNNER_ASSERT_MSG(iter->Get_TestID() == 10, "Wrong row 1 order");
1098             ++iter;
1099         }
1100         { //2 row
1101             RUNNER_ASSERT_MSG(*iter->Get_TestText33() ==
1102                               DPL::FromASCIIString(
1103                                   "test 5"), "Wrong row 2 order");
1104             RUNNER_ASSERT_MSG(iter->Get_TestID() == 7, "Wrong row 2 order");
1105             ++iter;
1106         }
1107         { //3 row
1108             RUNNER_ASSERT_MSG(iter->Get_Value3() == 111, "Wrong row 3 order");
1109             RUNNER_ASSERT_MSG(*iter->Get_TestText33() ==
1110                               DPL::FromASCIIString(
1111                                   "test 2"), "Wrong row 3 order");
1112             RUNNER_ASSERT_MSG(iter->Get_TestID() == 2, "Wrong row 3 order");
1113             ++iter;
1114         }
1115         { //4 row
1116             RUNNER_ASSERT_MSG(iter->Get_Value3() == 111, "Wrong row 4 order");
1117             RUNNER_ASSERT_MSG(*iter->Get_TestText33() ==
1118                               DPL::FromASCIIString(
1119                                   "test 1"), "Wrong row 4 order");
1120             RUNNER_ASSERT_MSG(iter->Get_TestID() == 1, "Wrong row 4 order");
1121             ++iter;
1122         }
1123         { //5 row
1124             RUNNER_ASSERT_MSG(iter->Get_Value3() == 222, "Wrong row 5 order");
1125             RUNNER_ASSERT_MSG(*iter->Get_TestText33() ==
1126                               DPL::FromASCIIString(
1127                                   "test 4"), "Wrong row 5 order");
1128             RUNNER_ASSERT_MSG(iter->Get_TestID() == 6, "Wrong row 5 order");
1129             ++iter;
1130         }
1131         { //6 row
1132             RUNNER_ASSERT_MSG(iter->Get_Value3() == 222, "Wrong row 6 order");
1133             RUNNER_ASSERT_MSG(*iter->Get_TestText33() ==
1134                               DPL::FromASCIIString(
1135                                   "test 3"), "Wrong row 6 order");
1136             RUNNER_ASSERT_MSG(iter->Get_TestID() == 3, "Wrong row 6 order");
1137             ++iter;
1138         }
1139     }
1140 }