SQLiteWrapper.cpp (28015B)
1 // Copyright (c) Microsoft Corporation. 2 // Licensed under the MIT License. 3 #include "pch.h" 4 #include "TestCommon.h" 5 #include <AppInstallerErrors.h> 6 #include <winget/SQLiteWrapper.h> 7 #include <winget/SQLiteStatementBuilder.h> 8 9 using namespace AppInstaller::SQLite; 10 using namespace std::string_literals; 11 12 static const char* s_firstColumn = "first"; 13 static const char* s_secondColumn = "second"; 14 static const char* s_tableName = "simpletest"; 15 static const char* s_savepoint = "simplesave"; 16 17 static const char* s_CreateSimpleTestTableSQL = R"( 18 CREATE TABLE [main].[simpletest]( 19 [first] INT, 20 [second] TEXT); 21 )"; 22 23 static const char* s_insertToSimpleTestTableSQL = R"( 24 insert into simpletest (first, second) values (?, ?) 25 )"; 26 27 static const char* s_selectFromSimpleTestTableSQL = R"( 28 select first, second from simpletest 29 )"; 30 31 void CreateSimpleTestTable(Connection& connection) 32 { 33 Builder::StatementBuilder builder; 34 builder.CreateTable(s_tableName).Columns({ 35 Builder::ColumnBuilder(s_firstColumn, Builder::Type::Int), 36 Builder::ColumnBuilder(s_secondColumn, Builder::Type::Text), 37 }); 38 39 Statement createTable = builder.Prepare(connection); 40 REQUIRE_FALSE(createTable.Step()); 41 REQUIRE(createTable.GetState() == Statement::State::Completed); 42 } 43 44 void InsertIntoSimpleTestTable(Connection& connection, int firstVal, const std::string& secondVal) 45 { 46 Builder::StatementBuilder builder; 47 builder.InsertInto(s_tableName).Columns({ s_firstColumn, s_secondColumn }).Values(firstVal, secondVal); 48 Statement insert = builder.Prepare(connection); 49 50 REQUIRE_FALSE(insert.Step()); 51 REQUIRE(insert.GetState() == Statement::State::Completed); 52 } 53 54 void UpdateSimpleTestTable(Connection& connection, int firstVal, const std::string& secondVal) 55 { 56 Builder::StatementBuilder update; 57 update.Update(s_tableName).Set().Column(s_firstColumn).Equals(firstVal).Column(s_secondColumn).Equals(secondVal); 58 update.Execute(connection); 59 } 60 61 void InsertIntoSimpleTestTableWithNull(Connection& connection, int firstVal) 62 { 63 Builder::StatementBuilder builder; 64 builder.InsertInto(s_tableName).Columns({ s_firstColumn, s_secondColumn }).Values(firstVal, nullptr); 65 Statement insert = builder.Prepare(connection); 66 67 REQUIRE_FALSE(insert.Step()); 68 REQUIRE(insert.GetState() == Statement::State::Completed); 69 } 70 71 void SelectFromSimpleTestTableOnlyOneRow(Connection& connection, int firstVal, const std::string& secondVal) 72 { 73 Builder::StatementBuilder builder; 74 builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName); 75 Statement select = builder.Prepare(connection); 76 77 REQUIRE(select.Step()); 78 REQUIRE(select.GetState() == Statement::State::HasRow); 79 80 int firstRead = select.GetColumn<int>(0); 81 std::string secondRead = select.GetColumn<std::string>(1); 82 83 REQUIRE(firstVal == firstRead); 84 REQUIRE(secondVal == secondRead); 85 86 auto tuple = select.GetRow<int, std::string>(); 87 88 REQUIRE(firstVal == std::get<0>(tuple)); 89 REQUIRE(secondVal == std::get<1>(tuple)); 90 91 REQUIRE_FALSE(select.Step()); 92 REQUIRE(select.GetState() == Statement::State::Completed); 93 94 select.Reset(); 95 REQUIRE(select.GetState() == Statement::State::Prepared); 96 97 REQUIRE(select.Step()); 98 REQUIRE(select.GetState() == Statement::State::HasRow); 99 } 100 101 TEST_CASE("SQLiteWrapperMemoryCreate", "[sqlitewrapper]") 102 { 103 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 104 105 CreateSimpleTestTable(connection); 106 107 int firstVal = 1; 108 std::string secondVal = "test"; 109 110 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 111 112 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 113 } 114 115 TEST_CASE("SQLiteWrapperFileCreateAndReopen", "[sqlitewrapper]") 116 { 117 TestCommon::TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; 118 INFO("Using temporary file named: " << tempFile.GetPath()); 119 120 int firstVal = 1; 121 std::string secondVal = "test"; 122 123 // Create the DB and some data 124 { 125 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::Create); 126 127 CreateSimpleTestTable(connection); 128 129 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 130 } 131 132 // Reopen the DB and read data 133 { 134 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 135 136 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 137 } 138 } 139 140 TEST_CASE("SQLiteWrapperSavepointRollback", "[sqlitewrapper]") 141 { 142 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 143 144 int firstVal = 1; 145 std::string secondVal = "test"; 146 147 CreateSimpleTestTable(connection); 148 149 Savepoint savepoint = Savepoint::Create(connection, "test_savepoint"); 150 151 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 152 153 savepoint.Rollback(); 154 155 Statement select = Statement::Create(connection, s_selectFromSimpleTestTableSQL); 156 REQUIRE(!select.Step()); 157 REQUIRE(select.GetState() == Statement::State::Completed); 158 } 159 160 TEST_CASE("SQLiteWrapperSavepointRollbackOnDestruct", "[sqlitewrapper]") 161 { 162 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 163 164 int firstVal = 1; 165 std::string secondVal = "test"; 166 167 CreateSimpleTestTable(connection); 168 169 { 170 Savepoint savepoint = Savepoint::Create(connection, "test_savepoint"); 171 172 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 173 } 174 175 Statement select = Statement::Create(connection, s_selectFromSimpleTestTableSQL); 176 REQUIRE(!select.Step()); 177 REQUIRE(select.GetState() == Statement::State::Completed); 178 } 179 180 TEST_CASE("SQLiteWrapperSavepointCommit", "[sqlitewrapper]") 181 { 182 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 183 184 int firstVal = 1; 185 std::string secondVal = "test"; 186 187 CreateSimpleTestTable(connection); 188 189 { 190 Savepoint savepoint = Savepoint::Create(connection, "test_savepoint"); 191 192 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 193 194 savepoint.Commit(); 195 } 196 197 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 198 } 199 200 TEST_CASE("SQLiteWrapperSavepointReuse", "[sqlitewrapper]") 201 { 202 TestCommon::TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; 203 INFO("Using temporary file named: " << tempFile.GetPath()); 204 205 int firstVal = 1; 206 std::string secondVal = "test"; 207 208 // Create the DB and some data 209 { 210 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::Create); 211 212 CreateSimpleTestTable(connection); 213 214 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 215 } 216 217 // Reopen the DB and update with a single savepoint 218 { 219 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 220 221 Savepoint savepoint = Savepoint::Create(connection, s_savepoint); 222 223 firstVal = 2; 224 secondVal = "test2"; 225 UpdateSimpleTestTable(connection, firstVal, secondVal); 226 227 savepoint.Commit(); 228 } 229 230 { 231 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 232 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 233 } 234 235 // Reopen the DB and update with a multiple savepoint 236 { 237 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 238 239 { 240 Savepoint savepoint = Savepoint::Create(connection, s_savepoint); 241 242 firstVal = 3; 243 secondVal = "test3"; 244 UpdateSimpleTestTable(connection, firstVal, secondVal); 245 } 246 247 { 248 Savepoint savepoint = Savepoint::Create(connection, s_savepoint); 249 250 firstVal = 4; 251 secondVal = "test4"; 252 UpdateSimpleTestTable(connection, firstVal, secondVal); 253 254 savepoint.Commit(); 255 } 256 } 257 258 { 259 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 260 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 261 } 262 } 263 264 TEST_CASE("SQLiteWrapper_EscapeStringForLike", "[sqlitewrapper]") 265 { 266 std::string escape(EscapeCharForLike); 267 268 std::string input = "test"; 269 std::string output = EscapeStringForLike(input); 270 REQUIRE(input == output); 271 272 input = EscapeCharForLike; 273 output = EscapeStringForLike(input); 274 REQUIRE((input + input) == output); 275 276 input = "%"; 277 output = EscapeStringForLike(input); 278 REQUIRE((escape + input) == output); 279 280 input = "_"; 281 output = EscapeStringForLike(input); 282 REQUIRE((escape + input) == output); 283 284 input = "%_A_%"; 285 std::string expected = escape + "%" + escape + "_A" + escape + "_" + escape + "%"; 286 output = EscapeStringForLike(input); 287 REQUIRE(expected == output); 288 } 289 290 TEST_CASE("SQLiteWrapper_BindWithEmbeddedNull", "[sqlitewrapper]") 291 { 292 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 293 294 CreateSimpleTestTable(connection); 295 296 int firstVal = 1; 297 std::string secondVal = "test"; 298 secondVal[1] = '\0'; 299 300 REQUIRE_THROWS_HR(InsertIntoSimpleTestTable(connection, firstVal, secondVal), APPINSTALLER_CLI_ERROR_BIND_WITH_EMBEDDED_NULL); 301 } 302 303 TEST_CASE("SQLiteWrapper_PrepareFailure", "[sqlitewrapper]") 304 { 305 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 306 307 CreateSimpleTestTable(connection); 308 309 Builder::StatementBuilder builder; 310 builder.Select({ s_firstColumn, s_secondColumn }).From(std::string{ s_tableName } + "2").Where(s_firstColumn).Equals(2); 311 312 REQUIRE_THROWS_HR(builder.Prepare(connection), MAKE_HRESULT(SEVERITY_ERROR, FACILITY_SQLITE, SQLITE_ERROR)); 313 } 314 315 TEST_CASE("SQLiteWrapper_BusyTimeout_None", "[sqlitewrapper]") 316 { 317 TestCommon::TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; 318 INFO("Using temporary file named: " << tempFile.GetPath()); 319 320 wil::unique_event busy, done; 321 busy.create(); 322 done.create(); 323 324 std::thread busyThread([&]() 325 { 326 Connection threadConnection = Connection::Create(tempFile, Connection::OpenDisposition::Create); 327 Statement threadStatement = Statement::Create(threadConnection, "BEGIN EXCLUSIVE TRANSACTION"); 328 threadStatement.Execute(); 329 busy.SetEvent(); 330 done.wait(500); 331 }); 332 busyThread.detach(); 333 334 busy.wait(500); 335 336 Connection testConnection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 337 testConnection.SetBusyTimeout(0ms); 338 Statement testStatement = Statement::Create(testConnection, "BEGIN EXCLUSIVE TRANSACTION"); 339 REQUIRE_THROWS_HR(testStatement.Execute(), MAKE_HRESULT(SEVERITY_ERROR, FACILITY_SQLITE, SQLITE_BUSY)); 340 341 done.SetEvent(); 342 } 343 344 TEST_CASE("SQLiteWrapper_BusyTimeout_Some", "[sqlitewrapper]") 345 { 346 TestCommon::TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; 347 INFO("Using temporary file named: " << tempFile.GetPath()); 348 349 wil::unique_event busy, ready, done; 350 busy.create(); 351 ready.create(); 352 done.create(); 353 354 std::thread busyThread([&]() 355 { 356 Connection threadConnection = Connection::Create(tempFile, Connection::OpenDisposition::Create); 357 Statement threadBeginStatement = Statement::Create(threadConnection, "BEGIN EXCLUSIVE TRANSACTION"); 358 Statement threadCommitStatement = Statement::Create(threadConnection, "COMMIT"); 359 threadBeginStatement.Execute(); 360 busy.SetEvent(); 361 ready.wait(500); 362 done.wait(100); 363 threadCommitStatement.Execute(); 364 }); 365 busyThread.detach(); 366 367 busy.wait(500); 368 369 Connection testConnection = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 370 testConnection.SetBusyTimeout(500ms); 371 Statement testStatement = Statement::Create(testConnection, "BEGIN EXCLUSIVE TRANSACTION"); 372 ready.SetEvent(); 373 testStatement.Execute(); 374 375 done.SetEvent(); 376 } 377 378 TEST_CASE("SQLiteWrapper_CloseConnectionOnError", "[sqlitewrapper]") 379 { 380 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 381 382 Builder::StatementBuilder builder; 383 builder.CreateTable(s_tableName).Columns({ 384 Builder::ColumnBuilder(s_firstColumn, Builder::Type::Int), 385 Builder::ColumnBuilder(s_secondColumn, Builder::Type::Text), 386 }); 387 388 Statement createTable = builder.Prepare(connection); 389 REQUIRE_FALSE(createTable.Step()); 390 REQUIRE(createTable.GetState() == Statement::State::Completed); 391 392 createTable.Reset(); 393 REQUIRE_THROWS(createTable.Step(true)); 394 395 // Do anything that needs the connection 396 REQUIRE_THROWS_HR(connection.GetLastInsertRowID(), APPINSTALLER_CLI_ERROR_SQLITE_CONNECTION_TERMINATED); 397 } 398 399 TEST_CASE("SQLBuilder_SimpleSelectBind", "[sqlbuilder]") 400 { 401 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 402 403 CreateSimpleTestTable(connection); 404 405 InsertIntoSimpleTestTable(connection, 1, "1"); 406 InsertIntoSimpleTestTable(connection, 2, "2"); 407 InsertIntoSimpleTestTable(connection, 3, "3"); 408 409 Builder::StatementBuilder builder; 410 builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_firstColumn).Equals(2); 411 412 auto statement = builder.Prepare(connection); 413 414 REQUIRE(statement.Step()); 415 REQUIRE(statement.GetColumn<int>(0) == 2); 416 REQUIRE(statement.GetColumn<std::string>(0) == "2"); 417 418 REQUIRE(!statement.Step()); 419 420 Builder::StatementBuilder buildCount; 421 buildCount.Select(Builder::RowCount).From(s_tableName); 422 423 auto rows = buildCount.Prepare(connection); 424 425 REQUIRE(rows.Step()); 426 REQUIRE(rows.GetColumn<int>(0) == 3); 427 428 REQUIRE(!rows.Step()); 429 } 430 431 TEST_CASE("SQLBuilder_SimpleSelectUnbound", "[sqlbuilder]") 432 { 433 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 434 435 CreateSimpleTestTable(connection); 436 437 InsertIntoSimpleTestTable(connection, 1, "1"); 438 InsertIntoSimpleTestTable(connection, 2, "2"); 439 InsertIntoSimpleTestTable(connection, 3, "3"); 440 441 Builder::StatementBuilder builder; 442 builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_firstColumn).Equals(Builder::Unbound); 443 444 auto statement = builder.Prepare(connection); 445 446 statement.Bind(1, 2); 447 448 REQUIRE(statement.Step()); 449 REQUIRE(statement.GetColumn<int>(0) == 2); 450 REQUIRE(statement.GetColumn<std::string>(0) == "2"); 451 452 REQUIRE(!statement.Step()); 453 } 454 455 TEST_CASE("SQLBuilder_SimpleSelectNull", "[sqlbuilder]") 456 { 457 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 458 459 CreateSimpleTestTable(connection); 460 461 InsertIntoSimpleTestTable(connection, 1, "1"); 462 InsertIntoSimpleTestTable(connection, 2, "2"); 463 InsertIntoSimpleTestTableWithNull(connection, 3); 464 465 Builder::StatementBuilder builder; 466 builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_secondColumn).IsNull(); 467 468 auto statement = builder.Prepare(connection); 469 470 REQUIRE(statement.Step()); 471 REQUIRE(statement.GetColumn<int>(0) == 3); 472 REQUIRE(statement.GetColumnIsNull(1)); 473 474 REQUIRE(!statement.Step()); 475 } 476 477 TEST_CASE("SQLBuilder_SimpleSelectOptional", "[sqlbuilder]") 478 { 479 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 480 481 CreateSimpleTestTable(connection); 482 483 InsertIntoSimpleTestTable(connection, 1, "1"); 484 InsertIntoSimpleTestTable(connection, 2, "2"); 485 InsertIntoSimpleTestTableWithNull(connection, 3); 486 487 std::optional<std::string> secondValue; 488 489 { 490 Builder::StatementBuilder builder; 491 builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_secondColumn).Equals(secondValue); 492 493 auto statement = builder.Prepare(connection); 494 495 REQUIRE(statement.Step()); 496 REQUIRE(statement.GetColumn<int>(0) == 3); 497 REQUIRE(statement.GetColumnIsNull(1)); 498 499 REQUIRE(!statement.Step()); 500 } 501 502 { 503 secondValue = "2"; 504 Builder::StatementBuilder builder; 505 builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_secondColumn).Equals(secondValue); 506 507 auto statement = builder.Prepare(connection); 508 509 REQUIRE(statement.Step()); 510 REQUIRE(statement.GetColumn<int>(0) == 2); 511 REQUIRE(statement.GetColumn<std::string>(1) == "2"); 512 513 REQUIRE(!statement.Step()); 514 } 515 } 516 517 TEST_CASE("SQLBuilder_Update", "[sqlbuilder]") 518 { 519 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 520 521 CreateSimpleTestTable(connection); 522 523 int firstVal = 1; 524 std::string secondVal = "test"; 525 526 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 527 528 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 529 530 firstVal = 2; 531 secondVal = "testing"; 532 533 UpdateSimpleTestTable(connection, firstVal, secondVal); 534 535 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 536 } 537 538 TEST_CASE("SQLBuilder_CaseInsensitive", "[sqlbuilder]") 539 { 540 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 541 542 Builder::StatementBuilder createTable; 543 createTable.CreateTable(s_tableName).Columns({ 544 Builder::ColumnBuilder(s_firstColumn, Builder::Type::Text).CollateNoCase() 545 }); 546 547 createTable.Execute(connection); 548 549 std::string upperCaseVal = "TEST"; 550 std::string lowerCaseVal = "test"; 551 552 { 553 INFO("Insert initial value"); 554 Builder::StatementBuilder builder; 555 builder.InsertInto(s_tableName) 556 .Columns({ s_firstColumn }) 557 .Values(upperCaseVal); 558 559 builder.Execute(connection); 560 } 561 562 { 563 INFO("Retrieve using case-insensitive value"); 564 Builder::StatementBuilder builder; 565 builder.Select({ s_firstColumn }).From(s_tableName).Where(s_firstColumn).Equals(lowerCaseVal); 566 567 auto statement = builder.Prepare(connection); 568 REQUIRE(statement.Step()); 569 } 570 } 571 572 TEST_CASE("SQLBuilder_CreateTable", "[sqlbuilder]") 573 { 574 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 575 576 int testRun = GENERATE(0, 1, 2, 3, 4, 5, 6, 7); 577 578 bool notNull = ((testRun & 1) != 0); 579 bool unique = ((testRun & 2) != 0); 580 bool pk = ((testRun & 4) != 0); 581 CAPTURE(notNull, unique, pk); 582 583 Builder::StatementBuilder createTable; 584 createTable.CreateTable(s_tableName).Columns({ 585 Builder::ColumnBuilder(s_firstColumn, Builder::Type::Int).NotNull(notNull).Unique(unique).PrimaryKey(pk) 586 }); 587 588 createTable.Execute(connection); 589 590 Builder::StatementBuilder insertBuilder; 591 insertBuilder.InsertInto(s_tableName).Columns(s_firstColumn).Values(Builder::Unbound); 592 593 Statement insertStatement = insertBuilder.Prepare(connection); 594 595 { 596 INFO("Insert NULL"); 597 insertStatement.Bind(1, nullptr); 598 599 if (notNull) 600 { 601 REQUIRE_THROWS_HR(insertStatement.Execute(), MAKE_HRESULT(SEVERITY_ERROR, FACILITY_SQLITE, SQLITE_CONSTRAINT_NOTNULL)); 602 } 603 else 604 { 605 insertStatement.Execute(); 606 } 607 } 608 609 { 610 INFO("Insert unique values"); 611 insertStatement.Reset(); 612 insertStatement.Bind(1, 1); 613 insertStatement.Execute(); 614 615 insertStatement.Reset(); 616 insertStatement.Bind(1, 2); 617 insertStatement.Execute(); 618 } 619 620 { 621 INFO("Insert duplicate values"); 622 insertStatement.Reset(); 623 insertStatement.Bind(1, 1); 624 625 if (unique || pk) 626 { 627 HRESULT expectedHR = S_OK; 628 if (pk) 629 { 630 expectedHR = MAKE_HRESULT(SEVERITY_ERROR, FACILITY_SQLITE, SQLITE_CONSTRAINT_PRIMARYKEY); 631 } 632 else 633 { 634 expectedHR = MAKE_HRESULT(SEVERITY_ERROR, FACILITY_SQLITE, SQLITE_CONSTRAINT_UNIQUE); 635 } 636 REQUIRE_THROWS_HR(insertStatement.Execute(), expectedHR); 637 } 638 else 639 { 640 insertStatement.Execute(); 641 } 642 } 643 } 644 645 TEST_CASE("SQLBuilder_InsertValueBinding", "[sqlbuilder]") 646 { 647 char const* const columns[] = { "a", "b", "c", "d", "e", "f" }; 648 649 TestCommon::TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; 650 INFO("Using temporary file named: " << tempFile.GetPath()); 651 652 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::Create); 653 654 { 655 INFO("Create table"); 656 Builder::StatementBuilder createTable; 657 createTable.CreateTable(s_tableName).BeginColumns(); 658 for (const auto c : columns) 659 { 660 createTable.Column(Builder::ColumnBuilder(c, Builder::Type::Int)); 661 } 662 createTable.EndColumns(); 663 createTable.Execute(connection); 664 } 665 666 { 667 INFO("Insert values"); 668 Builder::StatementBuilder insertBuilder; 669 insertBuilder.InsertInto(s_tableName).BeginColumns(); 670 for (const auto c : columns) 671 { 672 insertBuilder.Column(c); 673 } 674 insertBuilder.EndColumns().Values(0, 1, 2, 3, 4, 5); 675 insertBuilder.Execute(connection); 676 } 677 678 { 679 INFO("Insert values"); 680 Builder::StatementBuilder insertBuilder; 681 insertBuilder.InsertInto(s_tableName).BeginColumns(); 682 for (const auto c : columns) 683 { 684 insertBuilder.Column(c); 685 } 686 insertBuilder.EndColumns().BeginValues(); 687 insertBuilder.Value(5); 688 insertBuilder.Value(nullptr); 689 insertBuilder.Value(3); 690 insertBuilder.Value(std::optional<int>{}); 691 insertBuilder.Value(std::optional<int>{ 1 }); 692 insertBuilder.Value(Builder::Unbound); 693 insertBuilder.EndValues(); 694 insertBuilder.Execute(connection); 695 } 696 697 { 698 INFO("Select values"); 699 Builder::StatementBuilder selectBuilder; 700 selectBuilder.Select(); 701 for (const auto c : columns) 702 { 703 selectBuilder.Column(c); 704 } 705 selectBuilder.From(s_tableName); 706 707 Statement select = selectBuilder.Prepare(connection); 708 REQUIRE(select.Step()); 709 710 for (int i = 0; i < ARRAYSIZE(columns); ++i) 711 { 712 REQUIRE(i == select.GetColumn<int>(i)); 713 } 714 715 REQUIRE(select.Step()); 716 717 for (int i = 0; i < ARRAYSIZE(columns); ++i) 718 { 719 if (i & 1) 720 { 721 REQUIRE(select.GetColumnIsNull(i)); 722 } 723 else 724 { 725 REQUIRE((5 - i) == select.GetColumn<int>(i)); 726 } 727 } 728 729 REQUIRE(!select.Step()); 730 } 731 } 732 733 TEST_CASE("SQLiteWrapperTransactionRollback", "[sqlitewrapper]") 734 { 735 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 736 737 int firstVal = 1; 738 std::string secondVal = "test"; 739 740 CreateSimpleTestTable(connection); 741 742 Transaction transaction = Transaction::Create(connection, "test_transaction", false); 743 744 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 745 746 transaction.Rollback(); 747 748 Statement select = Statement::Create(connection, s_selectFromSimpleTestTableSQL); 749 REQUIRE(!select.Step()); 750 REQUIRE(select.GetState() == Statement::State::Completed); 751 } 752 753 TEST_CASE("SQLiteWrapperTransactionRollbackOnDestruct", "[sqlitewrapper]") 754 { 755 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 756 757 int firstVal = 1; 758 std::string secondVal = "test"; 759 760 CreateSimpleTestTable(connection); 761 762 { 763 Transaction transaction = Transaction::Create(connection, "test_transaction", false); 764 765 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 766 } 767 768 Statement select = Statement::Create(connection, s_selectFromSimpleTestTableSQL); 769 REQUIRE(!select.Step()); 770 REQUIRE(select.GetState() == Statement::State::Completed); 771 } 772 773 TEST_CASE("SQLiteWrapperTransactionCommit", "[sqlitewrapper]") 774 { 775 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 776 777 int firstVal = 1; 778 std::string secondVal = "test"; 779 780 CreateSimpleTestTable(connection); 781 782 { 783 Transaction transaction = Transaction::Create(connection, "test_transaction", false); 784 785 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 786 787 transaction.Commit(); 788 } 789 790 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 791 } 792 793 TEST_CASE("SQLiteWrapperTransactionImmediate", "[sqlitewrapper]") 794 { 795 Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); 796 797 int firstVal = 1; 798 std::string secondVal = "test"; 799 800 CreateSimpleTestTable(connection); 801 802 { 803 Transaction transaction = Transaction::Create(connection, "test_transaction", true); 804 805 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 806 807 transaction.Commit(); 808 } 809 810 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 811 } 812 813 TEST_CASE("SQLiteWrapperTransactionWriteConflict", "[sqlitewrapper]") 814 { 815 TestCommon::TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; 816 INFO("Using temporary file named: " << tempFile.GetPath()); 817 818 Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::Create); 819 connection.SetJournalMode("WAL"); 820 821 int firstVal = 1; 822 std::string secondVal = "test"; 823 824 CreateSimpleTestTable(connection); 825 826 Connection connection2 = Connection::Create(tempFile, Connection::OpenDisposition::ReadWrite); 827 std::chrono::milliseconds busyWait = 250ms; 828 connection2.SetBusyTimeout(busyWait); 829 830 { 831 Transaction transaction = Transaction::Create(connection, "test_transaction", true); 832 InsertIntoSimpleTestTable(connection, firstVal, secondVal); 833 834 // Start second transaction 835 std::chrono::system_clock::time_point start = std::chrono::system_clock::now(); 836 std::chrono::system_clock::time_point end = start; 837 try 838 { 839 Transaction transaction2 = Transaction::Create(connection2, "test_transaction2", true); 840 } 841 catch (...) 842 { 843 end = std::chrono::system_clock::now(); 844 } 845 846 std::chrono::milliseconds duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start); 847 REQUIRE(duration >= busyWait); 848 849 transaction.Commit(); 850 851 Transaction transaction2 = Transaction::Create(connection2, "test_transaction2", true); 852 InsertIntoSimpleTestTable(connection2, firstVal, secondVal); 853 } 854 855 SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); 856 }