commit 1a1c9e7d3c5557b8ac8bb628b87639c10039adfb parent a446cd2f22ac04ff718e616246ac11cc0aa17afa Author: JohnMcPMS <johnmcp@microsoft.com> Date: Fri, 31 Jan 2020 16:33:06 -0800 Create SQL statement builder to make use more readable (#27) Diffstat:
13 files changed, 531 insertions(+), 139 deletions(-)
diff --git a/src/AppInstallerCLITests/SQLiteWrapper.cpp b/src/AppInstallerCLITests/SQLiteWrapper.cpp @@ -3,9 +3,14 @@ #include "pch.h" #include "TestCommon.h" #include <SQLiteWrapper.h> +#include <SQLiteStatementBuilder.h> using namespace AppInstaller::Repository::SQLite; +static const char* s_firstColumn = "first"; +static const char* s_secondColumn = "second"; +static const char* s_tableName = "simpletest"; + static const char* s_CreateSimpleTestTableSQL = R"( CREATE TABLE [main].[simpletest]( [first] INT, @@ -38,9 +43,22 @@ void InsertIntoSimpleTestTable(Connection& connection, int firstVal, const std:: REQUIRE(insert.GetState() == Statement::State::Completed); } +void InsertIntoSimpleTestTableWithNull(Connection& connection, int firstVal) +{ + Statement insert = Statement::Create(connection, s_insertToSimpleTestTableSQL); + + insert.Bind(1, firstVal); + + REQUIRE_FALSE(insert.Step()); + REQUIRE(insert.GetState() == Statement::State::Completed); +} + void SelectFromSimpleTestTableOnlyOneRow(Connection& connection, int firstVal, const std::string& secondVal) { - Statement select = Statement::Create(connection, s_selectFromSimpleTestTableSQL); + Builder::StatementBuilder builder; + builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName); + Statement select = builder.Prepare(connection); + REQUIRE(select.Step()); REQUIRE(select.GetState() == Statement::State::HasRow); @@ -163,3 +181,121 @@ TEST_CASE("SQLiteWrapperSavepointCommit", "[sqlitewrapper]") SelectFromSimpleTestTableOnlyOneRow(connection, firstVal, secondVal); } + +TEST_CASE("SQLBuilder_SimpleSelectBind", "[sqlbuilder]") +{ + Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); + + CreateSimpleTestTable(connection); + + InsertIntoSimpleTestTable(connection, 1, "1"); + InsertIntoSimpleTestTable(connection, 2, "2"); + InsertIntoSimpleTestTable(connection, 3, "3"); + + Builder::StatementBuilder builder; + builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_firstColumn).Equals(2); + + auto statement = builder.Prepare(connection); + + REQUIRE(statement.Step()); + REQUIRE(statement.GetColumn<int>(0) == 2); + REQUIRE(statement.GetColumn<std::string>(0) == "2"); + + REQUIRE(!statement.Step()); + + Builder::StatementBuilder buildCount; + buildCount.Select(Builder::RowCount).From(s_tableName); + + auto rows = buildCount.Prepare(connection); + + REQUIRE(rows.Step()); + REQUIRE(rows.GetColumn<int>(0) == 3); + + REQUIRE(!rows.Step()); +} + +TEST_CASE("SQLBuilder_SimpleSelectUnbound", "[sqlbuilder]") +{ + Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); + + CreateSimpleTestTable(connection); + + InsertIntoSimpleTestTable(connection, 1, "1"); + InsertIntoSimpleTestTable(connection, 2, "2"); + InsertIntoSimpleTestTable(connection, 3, "3"); + + Builder::StatementBuilder builder; + builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_firstColumn).Equals(Builder::Unbound); + + auto statement = builder.Prepare(connection); + + statement.Bind(1, 2); + + REQUIRE(statement.Step()); + REQUIRE(statement.GetColumn<int>(0) == 2); + REQUIRE(statement.GetColumn<std::string>(0) == "2"); + + REQUIRE(!statement.Step()); +} + +TEST_CASE("SQLBuilder_SimpleSelectNull", "[sqlbuilder]") +{ + Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); + + CreateSimpleTestTable(connection); + + InsertIntoSimpleTestTable(connection, 1, "1"); + InsertIntoSimpleTestTable(connection, 2, "2"); + InsertIntoSimpleTestTableWithNull(connection, 3); + + Builder::StatementBuilder builder; + builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_secondColumn).IsNull(); + + auto statement = builder.Prepare(connection); + + REQUIRE(statement.Step()); + REQUIRE(statement.GetColumn<int>(0) == 3); + REQUIRE(statement.GetColumnIsNull(1)); + + REQUIRE(!statement.Step()); +} + +TEST_CASE("SQLBuilder_SimpleSelectOptional", "[sqlbuilder]") +{ + Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); + + CreateSimpleTestTable(connection); + + InsertIntoSimpleTestTable(connection, 1, "1"); + InsertIntoSimpleTestTable(connection, 2, "2"); + InsertIntoSimpleTestTableWithNull(connection, 3); + + std::optional<std::string> secondValue; + + { + Builder::StatementBuilder builder; + builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_secondColumn).Equals(secondValue); + + auto statement = builder.Prepare(connection); + + REQUIRE(statement.Step()); + REQUIRE(statement.GetColumn<int>(0) == 3); + REQUIRE(statement.GetColumnIsNull(1)); + + REQUIRE(!statement.Step()); + } + + { + secondValue = "2"; + Builder::StatementBuilder builder; + builder.Select({ s_firstColumn, s_secondColumn }).From(s_tableName).Where(s_secondColumn).Equals(secondValue); + + auto statement = builder.Prepare(connection); + + REQUIRE(statement.Step()); + REQUIRE(statement.GetColumn<int>(0) == 2); + REQUIRE(statement.GetColumn<std::string>(1) == "2"); + + REQUIRE(!statement.Step()); + } +} diff --git a/src/AppInstallerCommonCore/AppInstallerTelemetry.cpp b/src/AppInstallerCommonCore/AppInstallerTelemetry.cpp @@ -117,7 +117,7 @@ namespace AppInstaller::Logging "CommandFound", GetActivityId(), nullptr, - TraceLoggingCountedString(commandName.data(), commandName.size(), "Command"), + TraceLoggingCountedString(commandName.data(), static_cast<ULONG>(commandName.size()), "Command"), TelemetryPrivacyDataTag(PDT_ProductAndServicePerformance), TraceLoggingKeyword(MICROSOFT_KEYWORD_MEASURES)); } @@ -133,7 +133,7 @@ namespace AppInstaller::Logging "CommandSuccess", GetActivityId(), nullptr, - TraceLoggingCountedString(commandName.data(), commandName.size(), "Command"), + TraceLoggingCountedString(commandName.data(), static_cast<ULONG>(commandName.size()), "Command"), TelemetryPrivacyDataTag(PDT_ProductAndServicePerformance), TraceLoggingKeyword(MICROSOFT_KEYWORD_MEASURES)); } @@ -149,8 +149,8 @@ namespace AppInstaller::Logging "ManifestFields", GetActivityId(), nullptr, - TraceLoggingCountedString(name.c_str(), name.size(),"Name"), - TraceLoggingCountedString(version.c_str(), version.size(), "Version"), + TraceLoggingCountedString(name.c_str(), static_cast<ULONG>(name.size()),"Name"), + TraceLoggingCountedString(version.c_str(), static_cast<ULONG>(version.size()), "Version"), TelemetryPrivacyDataTag(PDT_ProductAndServicePerformance), TraceLoggingKeyword(MICROSOFT_KEYWORD_MEASURES)); } diff --git a/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj b/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj @@ -187,6 +187,7 @@ <ClInclude Include="Microsoft\Schema\Version.h" /> <ClInclude Include="Microsoft\SQLiteIndex.h" /> <ClInclude Include="pch.h" /> + <ClInclude Include="SQLiteStatementBuilder.h" /> <ClInclude Include="Public\AppInstallerRepositorySearch.h" /> <ClInclude Include="Public\AppInstallerRepositorySource.h" /> <ClInclude Include="SQLiteWrapper.h" /> @@ -207,6 +208,7 @@ <ClCompile Include="pch.cpp"> <PrecompiledHeader>Create</PrecompiledHeader> </ClCompile> + <ClCompile Include="SQLiteStatementBuilder.cpp" /> <ClCompile Include="SQLiteWrapper.cpp" /> </ItemGroup> <ItemGroup> diff --git a/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj.filters b/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj.filters @@ -102,6 +102,9 @@ <ClInclude Include="Microsoft\Schema\1_0\PathPartTable.h"> <Filter>Microsoft\Schema\1_0</Filter> </ClInclude> + <ClInclude Include="SQLiteStatementBuilder.h"> + <Filter>Header Files</Filter> + </ClInclude> <ClInclude Include="Public\AppInstallerRepositorySource.h"> <Filter>Public</Filter> </ClInclude> @@ -152,6 +155,9 @@ <ClCompile Include="Microsoft\Schema\1_0\PathPartTable.cpp"> <Filter>Microsoft\Schema\1_0</Filter> </ClCompile> + <ClCompile Include="SQLiteStatementBuilder.cpp"> + <Filter>Source Files</Filter> + </ClCompile> </ItemGroup> <ItemGroup> <None Include="PropertySheet.props" /> diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.cpp @@ -2,6 +2,7 @@ // Licensed under the MIT License. #include "pch.h" #include "ManifestTable.h" +#include "SQLiteStatementBuilder.h" namespace AppInstaller::Repository::Microsoft::Schema::V1_0 @@ -13,12 +14,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { std::optional<SQLite::rowid_t> ManifestTableSelectByValueId(SQLite::Connection& connection, std::string_view valueName, SQLite::rowid_t id) { - std::ostringstream selectSQL; - selectSQL << "SELECT [" << SQLite::RowIDName << "] FROM [" << s_ManifestTable_Table_Name << "] WHERE [" << valueName << "] = ? LIMIT 1"; + SQLite::Builder::StatementBuilder builder; + builder.Select(SQLite::RowIDName).From(s_ManifestTable_Table_Name).Where(valueName).Equals(id).Limit(1); - SQLite::Statement select = SQLite::Statement::Create(connection, selectSQL.str()); - - select.Bind(1, id); + SQLite::Statement select = builder.Prepare(connection); if (select.Step()) { @@ -35,22 +34,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 SQLite::rowid_t id, std::initializer_list<std::string_view> values) { - std::ostringstream selectSQL; - selectSQL << "SELECT "; - - // add columns to select - bool isFirst = true; - for (const std::string_view& value : values) - { - selectSQL << (isFirst ? "[" : ", [") << value << ']'; - isFirst = false; - } - - selectSQL << " FROM [" << s_ManifestTable_Table_Name << "] WHERE [" << SQLite::RowIDName << "] = ?"; + SQLite::Builder::StatementBuilder builder; + builder.Select(values).From(s_ManifestTable_Table_Name).Where(SQLite::RowIDName).Equals(id); - SQLite::Statement result = SQLite::Statement::Create(connection, selectSQL.str()); - - result.Bind(1, id); + SQLite::Statement result = builder.Prepare(connection); THROW_HR_IF(E_NOT_SET, !result.Step()); @@ -65,33 +52,22 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 SQLite::Statement ManifestTableGetValuesById_Statement( SQLite::Connection& connection, SQLite::rowid_t id, - std::initializer_list<ManifestOneToOneTableInfo> tableInfos) + std::initializer_list<SQLite::Builder::QualifiedColumn> columns) { - std::ostringstream selectSQL; - selectSQL << "SELECT "; + using QCol = SQLite::Builder::QualifiedColumn; - // add columns to select - bool isFirst = true; - for (const ManifestOneToOneTableInfo& tableInfo : tableInfos) - { - selectSQL << (isFirst ? "[" : ", [") << tableInfo.Table << "].[" << tableInfo.Value << ']'; - isFirst = false; - } - - selectSQL << " FROM [" << s_ManifestTable_Table_Name << "] "; + SQLite::Builder::StatementBuilder builder; + builder.Select(columns).From(s_ManifestTable_Table_Name); // join tables - for (const ManifestOneToOneTableInfo& tableInfo : tableInfos) + for (const QCol& column : columns) { - selectSQL << "JOIN [" << tableInfo.Table << "] ON " << - '[' << s_ManifestTable_Table_Name << "].[" << tableInfo.Value << "] = [" << tableInfo.Table << "].[" << SQLite::RowIDName << "] "; + builder.Join(column.Table).On(QCol{ s_ManifestTable_Table_Name, column.Column }, QCol{ column.Table, SQLite::RowIDName }); } - selectSQL << " WHERE [" << s_ManifestTable_Table_Name << "].[" << SQLite::RowIDName << "] = ?"; - - SQLite::Statement result = SQLite::Statement::Create(connection, selectSQL.str()); + builder.Where(QCol{ s_ManifestTable_Table_Name, SQLite::RowIDName }).Equals(id); - result.Bind(1, id); + SQLite::Statement result = builder.Prepare(connection); THROW_HR_IF(E_NOT_SET, !result.Step()); @@ -193,10 +169,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 bool ManifestTable::IsEmpty(SQLite::Connection& connection) { - std::ostringstream countSQL; - countSQL << "SELECT COUNT(*) FROM [" << s_ManifestTable_Table_Name << ']'; + SQLite::Builder::StatementBuilder builder; + builder.Select(SQLite::Builder::RowCount).From(s_ManifestTable_Table_Name); - SQLite::Statement countStatement = SQLite::Statement::Create(connection, countSQL.str()); + SQLite::Statement countStatement = builder.Prepare(connection); THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.h @@ -2,6 +2,7 @@ // Licensed under the MIT License. #pragma once #include "SQLiteWrapper.h" +#include "SQLiteStatementBuilder.h" #include <initializer_list> #include <optional> #include <string_view> @@ -11,13 +12,6 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { namespace details { - // Table info that is 1:1 with the manifest. - struct ManifestOneToOneTableInfo - { - std::string_view Table; - std::string_view Value; - }; - // Selects a manifest by the given value id. std::optional<SQLite::rowid_t> ManifestTableSelectByValueId(SQLite::Connection& connection, std::string_view valueName, SQLite::rowid_t id); @@ -31,7 +25,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 SQLite::Statement ManifestTableGetValuesById_Statement( SQLite::Connection& connection, SQLite::rowid_t id, - std::initializer_list<ManifestOneToOneTableInfo> tableInfos); + std::initializer_list<SQLite::Builder::QualifiedColumn> columns); } // Info on the manifest columns. @@ -76,7 +70,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 template <typename... Tables> static auto GetValuesById(SQLite::Connection& connection, SQLite::rowid_t id) { - return details::ManifestTableGetValuesById_Statement(connection, id, { details::ManifestOneToOneTableInfo{ Tables::TableName(), Tables::ValueName() }... }).GetRow<Tables::value_t...>(); + return details::ManifestTableGetValuesById_Statement(connection, id, { SQLite::Builder::QualifiedColumn{ Tables::TableName(), Tables::ValueName() }... }).GetRow<Tables::value_t...>(); } // Deletes the manifest row with the given rowid. diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.cpp @@ -3,6 +3,7 @@ #include "pch.h" #include "Microsoft/Schema/1_0/OneToManyTable.h" #include "Microsoft/Schema/1_0/OneToOneTable.h" +#include "SQLiteStatementBuilder.h" namespace AppInstaller::Repository::Microsoft::Schema::V1_0 @@ -70,12 +71,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Get values referenced by the manifest id. std::vector<SQLite::rowid_t> values; - std::ostringstream selectMappingSQL; - selectMappingSQL << "SELECT [" << valueName << "] FROM [" << tableName << s_OneToManyTable_MapTable_Suffix << "] WHERE [" << s_OneToManyTable_MapTable_ManifestName << "] = ?"; + SQLite::Builder::StatementBuilder selectMappingBuilder; + selectMappingBuilder.Select(valueName).From({ tableName, s_OneToManyTable_MapTable_Suffix }).Where(s_OneToManyTable_MapTable_ManifestName).Equals(manifestId); - SQLite::Statement selectMappingStatement = SQLite::Statement::Create(connection, selectMappingSQL.str()); - - selectMappingStatement.Bind(1, manifestId); + SQLite::Statement selectMappingStatement = selectMappingBuilder.Prepare(connection); while (selectMappingStatement.Step()) { @@ -93,11 +92,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 deleteStatement.Execute(); // For each value, see if any references exist - std::ostringstream selectValueMappingSQL; - selectValueMappingSQL << "SELECT [" << s_OneToManyTable_MapTable_ManifestName << "] " - << "FROM [" << tableName << s_OneToManyTable_MapTable_Suffix << "] WHERE [" << valueName << "] = ? LIMIT 1"; + SQLite::Builder::StatementBuilder selectValueMappingBuilder; + selectValueMappingBuilder.Select(s_OneToManyTable_MapTable_ManifestName).From({ tableName, s_OneToManyTable_MapTable_Suffix }).Where(valueName).Equals(SQLite::Builder::Unbound).Limit(1); - SQLite::Statement selectValueMappingStatement = SQLite::Statement::Create(connection, selectValueMappingSQL.str()); + SQLite::Statement selectValueMappingStatement = selectValueMappingBuilder.Prepare(connection); std::ostringstream deleteValueSQL; deleteValueSQL << "DELETE FROM [" << tableName << "] WHERE [" << SQLite::RowIDName << "] = ?"; @@ -124,17 +122,17 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 bool OneToManyTableIsEmpty(SQLite::Connection& connection, std::string_view tableName) { - std::ostringstream countSQL; - countSQL << "SELECT COUNT(*) FROM [" << tableName << ']'; + SQLite::Builder::StatementBuilder countBuilder; + countBuilder.Select(SQLite::Builder::RowCount).From(tableName); - SQLite::Statement countStatement = SQLite::Statement::Create(connection, countSQL.str()); + SQLite::Statement countStatement = countBuilder.Prepare(connection); THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); - std::ostringstream countMapSQL; - countMapSQL << "SELECT COUNT(*) FROM [" << tableName << s_OneToManyTable_MapTable_Suffix << ']'; + SQLite::Builder::StatementBuilder countMapBuilder; + countMapBuilder.Select(SQLite::Builder::RowCount).From({ tableName, s_OneToManyTable_MapTable_Suffix }); - SQLite::Statement countMapStatement = SQLite::Statement::Create(connection, countMapSQL.str()); + SQLite::Statement countMapStatement = countMapBuilder.Prepare(connection); THROW_HR_IF(E_UNEXPECTED, !countMapStatement.Step()); diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.cpp @@ -3,6 +3,7 @@ #include "pch.h" #include "Microsoft/Schema/1_0/OneToOneTable.h" #include "Microsoft/Schema/1_0/ManifestTable.h" +#include "SQLiteStatementBuilder.h" namespace AppInstaller::Repository::Microsoft::Schema::V1_0 @@ -23,12 +24,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 SQLite::rowid_t OneToOneTableEnsureExists(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, std::string_view value) { { - std::ostringstream selectSQL; - selectSQL << "SELECT [" << SQLite::RowIDName << "] FROM [" << tableName << "] WHERE [" << valueName << "] = ?"; + SQLite::Builder::StatementBuilder selectBuilder; + selectBuilder.Select(SQLite::RowIDName).From(tableName).Where(valueName).Equals(value); - SQLite::Statement select = SQLite::Statement::Create(connection, selectSQL.str()); - - select.Bind(1, value); + SQLite::Statement select = selectBuilder.Prepare(connection); if (select.Step()) { @@ -68,10 +67,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 bool OneToOneTableIsEmpty(SQLite::Connection& connection, std::string_view tableName) { - std::ostringstream countSQL; - countSQL << "SELECT COUNT(*) FROM [" << tableName << ']'; + SQLite::Builder::StatementBuilder builder; + builder.Select(SQLite::Builder::RowCount).From(tableName); - SQLite::Statement countStatement = SQLite::Statement::Create(connection, countSQL.str()); + SQLite::Statement countStatement = builder.Prepare(connection); THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/PathPartTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/PathPartTable.cpp @@ -2,6 +2,7 @@ // Licensed under the MIT License. #include "pch.h" #include "PathPartTable.h" +#include "SQLiteStatementBuilder.h" namespace AppInstaller::Repository::Microsoft::Schema::V1_0 @@ -18,23 +19,11 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Returns an no value if none exists, or the rowid of the part if it is found. std::optional<SQLite::rowid_t> SelectPathPart(SQLite::Connection& connection, std::optional<SQLite::rowid_t> parent, std::string_view part) { - std::ostringstream selectPartSQL; - selectPartSQL << "SELECT [" << SQLite::RowIDName << "] " - << "FROM [" << s_PathPartTable_Table_Name << "] WHERE " - << '[' << s_PathPartTable_ParentValue_Name << "] " << (parent ? "= ?" : "IS NULL") << " AND " - << '[' << s_PathPartTable_PartValue_Name << "] = ?"; + SQLite::Builder::StatementBuilder builder; + builder.Select(SQLite::RowIDName).From(s_PathPartTable_Table_Name). + Where(s_PathPartTable_ParentValue_Name).Equals(parent).And(s_PathPartTable_PartValue_Name).Equals(part); - SQLite::Statement select = SQLite::Statement::Create(connection, selectPartSQL.str()); - - if (parent) - { - select.Bind(1, parent.value()); - select.Bind(2, part); - } - else - { - select.Bind(1, part); - } + SQLite::Statement select = builder.Prepare(connection); if (select.Step()) { @@ -78,13 +67,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // This should only be called when the part must exist, as it will throw if not found. std::optional<SQLite::rowid_t> GetParentById(SQLite::Connection& connection, SQLite::rowid_t id) { - std::ostringstream selectPartSQL; - selectPartSQL << "SELECT [" << s_PathPartTable_ParentValue_Name << "] FROM [" << s_PathPartTable_Table_Name << "] WHERE " - << '[' << SQLite::RowIDName << "] = ?"; + SQLite::Builder::StatementBuilder builder; + builder.Select(s_PathPartTable_ParentValue_Name).From(s_PathPartTable_Table_Name).Where(SQLite::RowIDName).Equals(id); - SQLite::Statement select = SQLite::Statement::Create(connection, selectPartSQL.str()); - - select.Bind(1, id); + SQLite::Statement select = builder.Prepare(connection); THROW_HR_IF(APPINSTALLER_CLI_ERROR_INDEX_INTEGRITY_COMPROMISED, !select.Step()); @@ -101,13 +87,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Determines if any part references this one as their parent. bool IsLeafPart(SQLite::Connection& connection, SQLite::rowid_t id) { - std::ostringstream selectPartSQL; - selectPartSQL << "SELECT COUNT(*) FROM [" << s_PathPartTable_Table_Name << "] WHERE " - << '[' << s_PathPartTable_ParentValue_Name << "] = ?"; - - SQLite::Statement select = SQLite::Statement::Create(connection, selectPartSQL.str()); + SQLite::Builder::StatementBuilder builder; + builder.Select(SQLite::Builder::RowCount).From(s_PathPartTable_Table_Name).Where(s_PathPartTable_ParentValue_Name).Equals(id); - select.Bind(1, id); + SQLite::Statement select = builder.Prepare(connection); THROW_HR_IF(E_UNEXPECTED, !select.Step()); @@ -235,10 +218,10 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 bool PathPartTable::IsEmpty(SQLite::Connection& connection) { - std::ostringstream countSQL; - countSQL << "SELECT COUNT(*) FROM [" << s_PathPartTable_Table_Name << ']'; + SQLite::Builder::StatementBuilder builder; + builder.Select(SQLite::Builder::RowCount).From(s_PathPartTable_Table_Name); - SQLite::Statement countStatement = SQLite::Statement::Create(connection, countSQL.str()); + SQLite::Statement countStatement = builder.Prepare(connection); THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); diff --git a/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.cpp b/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.cpp @@ -0,0 +1,175 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "pch.h" +#include "SQLiteStatementBuilder.h" + +namespace AppInstaller::Repository::SQLite::Builder +{ + std::ostream& operator<<(std::ostream& out, const QualifiedColumn& column) + { + out << '[' << column.Table << "].[" << column.Column << ']'; + return out; + } + + StatementBuilder& StatementBuilder::Select(std::string_view column) + { + m_stream << "SELECT [" << column << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::Select(std::initializer_list<std::string_view> columns) + { + m_stream << "SELECT"; + bool isFirst = true; + for (const auto& c : columns) + { + m_stream << (isFirst ? " [" : ", [") << c << ']'; + isFirst = false; + } + return *this; + } + + StatementBuilder& StatementBuilder::Select(QualifiedColumn column) + { + m_stream << "SELECT " << column; + return *this; + } + + StatementBuilder& StatementBuilder::Select(std::initializer_list<QualifiedColumn> columns) + { + m_stream << "SELECT"; + bool isFirst = true; + for (const auto& c : columns) + { + m_stream << (isFirst ? " " : ", ") << c; + isFirst = false; + } + return *this; + } + + StatementBuilder& StatementBuilder::Select(details::rowcount_t) + { + m_stream << "SELECT COUNT(*)"; + return *this; + } + + StatementBuilder& StatementBuilder::From(std::string_view table) + { + m_stream << " FROM [" << table << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::From(std::initializer_list<std::string_view> table) + { + m_stream << " FROM ["; + for (std::string_view t : table) + { + m_stream << t; + } + m_stream << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::Where(std::string_view column) + { + m_stream << " WHERE [" << column << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::Where(QualifiedColumn column) + { + m_stream << " WHERE " << column; + return *this; + } + + StatementBuilder& StatementBuilder::Equals(details::unbound_t) + { + AppendOpAndBinder(Op::Equals); + return *this; + } + + StatementBuilder& StatementBuilder::Equals(std::nullptr_t) + { + // This is almost certainly not what you want. + // In SQL, value = NULL is always false. + // Use StatementBuilder::IsNull instead. + THROW_HR(E_NOTIMPL); + } + + StatementBuilder& StatementBuilder::IsNull() + { + m_stream << " IS NULL"; + return *this; + } + + StatementBuilder& StatementBuilder::And(std::string_view column) + { + m_stream << " AND [" << column << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::And(QualifiedColumn column) + { + m_stream << " AND " << column; + return *this; + } + + StatementBuilder& StatementBuilder::Join(std::string_view table) + { + m_stream << " JOIN [" << table << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::Join(std::initializer_list<std::string_view> table) + { + m_stream << " JOIN ["; + for (std::string_view t : table) + { + m_stream << t; + } + m_stream << ']'; + return *this; + } + + StatementBuilder& StatementBuilder::On(QualifiedColumn column1, QualifiedColumn column2) + { + m_stream << " ON " << column1 << " = " << column2; + return *this; + } + + StatementBuilder& StatementBuilder::Limit(size_t rowCount) + { + m_stream << " LIMIT " << rowCount; + return *this; + } + + Statement StatementBuilder::Prepare(Connection& connection, bool persistent) + { + m_statement = std::make_unique<Statement>(Statement::Create(connection, m_stream.str(), persistent)); + for (const auto& f : m_binders) + { + f(); + } + return std::move(*(m_statement.release())); + } + + void StatementBuilder::Execute(Connection& connection) + { + Prepare(connection).Execute(); + } + + int StatementBuilder::AppendOpAndBinder(Op op) + { + switch (op) + { + case Op::Equals: + m_stream << " = ?"; + break; + default: + THROW_HR(E_UNEXPECTED); + } + + return m_bindIndex++; + } +} diff --git a/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.h b/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.h @@ -0,0 +1,134 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "SQLiteWrapper.h" + +#include <functional> +#include <initializer_list> +#include <memory> +#include <optional> +#include <sstream> +#include <string_view> +#include <vector> + +namespace AppInstaller::Repository::SQLite::Builder +{ + namespace details + { + // Sentinel types to indicate special cases to the builder. + struct unbound_t {}; + struct rowcount_t {}; + } + + // Pass this value to indicate that the caller will bind the value later. + __declspec_selectany_ details::unbound_t Unbound; + + // Pass this value to indicate that the number of rows is to be selected. + __declspec_selectany_ details::rowcount_t RowCount; + + // A qualified column reference. + struct QualifiedColumn + { + std::string_view Table; + std::string_view Column; + + explicit QualifiedColumn(std::string_view column) : Column(column) {} + explicit QualifiedColumn(std::string_view table, std::string_view column) : Table(table), Column(column) {} + }; + + // A class that aids in building SQL statements in a more expressive manner than simple strings. + struct StatementBuilder + { + StatementBuilder() = default; + + StatementBuilder(const StatementBuilder&) = default; + StatementBuilder& operator=(const StatementBuilder&) = default; + + StatementBuilder(StatementBuilder&&) = default; + StatementBuilder& operator=(StatementBuilder&&) = default; + + // Begin a select statement for the given columns. + StatementBuilder& Select(std::string_view column); + StatementBuilder& Select(std::initializer_list<std::string_view> columns); + StatementBuilder& Select(QualifiedColumn column); + StatementBuilder& Select(std::initializer_list<QualifiedColumn> columns); + StatementBuilder& Select(details::rowcount_t); + + // Indicate the table that the statement will be operating on. + // The initializer_list form enables the table name to be constructed from multiple parts. + StatementBuilder& From(std::string_view table); + StatementBuilder& From(std::initializer_list<std::string_view> table); + + // Begin a filter clause on the given column. + StatementBuilder& Where(std::string_view column); + StatementBuilder& Where(QualifiedColumn column); + + // Indicate the operation of the filter clause. + template <typename ValueType> + StatementBuilder& Equals(const ValueType& value) + { + AddBindFunctor(AppendOpAndBinder(Op::Equals), value); + return *this; + } + template <typename ValueType> + StatementBuilder& Equals(const std::optional<ValueType>& value) + { + if (value) + { + AddBindFunctor(AppendOpAndBinder(Op::Equals), value.value()); + return *this; + } + else + { + return IsNull(); + } + } + StatementBuilder& Equals(details::unbound_t); + StatementBuilder& Equals(std::nullptr_t); + + StatementBuilder& IsNull(); + + // Operators for combining filter clauses. + StatementBuilder& And(std::string_view column); + StatementBuilder& And(QualifiedColumn column); + + // Begin a join clause. + // The initializer_list form enables the table name to be constructed from multiple parts. + StatementBuilder& Join(std::string_view table); + StatementBuilder& Join(std::initializer_list<std::string_view> table); + + // Set the join constraint. + StatementBuilder& On(QualifiedColumn column1, QualifiedColumn column2); + + // Limits the result set to the given number of rows. + StatementBuilder& Limit(size_t rowCount); + + // Prepares and returns the statement, applying any bindings that were requested. + Statement Prepare(Connection& connection, bool persistent = false); + + // A convenience function that prepares, binds, and then executes a statement that does not return rows. + void Execute(Connection& connection); + + private: + enum class Op + { + Equals + }; + + // Appends given the operation. + int AppendOpAndBinder(Op op); + + // Adds a functor to our list that will bind the given value. + template <typename ValueType> + void AddBindFunctor(int binderIndex, const ValueType& value) + { + m_binders.emplace_back([this, binderIndex, &value]() { this->m_statement->Bind(binderIndex, value); }); + } + + std::ostringstream m_stream; + std::unique_ptr<Statement> m_statement; + // Because binding values starts at 1 + int m_bindIndex = 1; + std::vector<std::function<void()>> m_binders; + }; +} diff --git a/src/AppInstallerRepositoryCore/SQLiteWrapper.cpp b/src/AppInstallerRepositoryCore/SQLiteWrapper.cpp @@ -90,19 +90,14 @@ namespace AppInstaller::Repository::SQLite { Connection result{ target, disposition, flags }; - THROW_IF_SQLITE_FAILED(sqlite3_extended_result_codes(result.m_dbconn, 1)); + THROW_IF_SQLITE_FAILED(sqlite3_extended_result_codes(result.m_dbconn.get(), 1)); return result; } - Connection::~Connection() - { - sqlite3_close_v2(m_dbconn); - } - int64_t Connection::GetLastInsertRowID() { - return sqlite3_last_insert_rowid(m_dbconn); + return sqlite3_last_insert_rowid(m_dbconn.get()); } Statement::Statement(Connection& connection, std::string_view sql, bool persistent) @@ -130,15 +125,10 @@ namespace AppInstaller::Repository::SQLite return { connection, sql, persistent }; } - Statement::~Statement() - { - sqlite3_finalize(m_stmt); - } - bool Statement::Step(bool failFastOnError) { AICLI_LOG(SQL, Verbose, << "Stepping statement #" << m_id); - int result = sqlite3_step(m_stmt); + int result = sqlite3_step(m_stmt.get()); if (result == SQLITE_ROW) { @@ -173,7 +163,7 @@ namespace AppInstaller::Repository::SQLite bool Statement::GetColumnIsNull(int column) { - int type = sqlite3_column_type(m_stmt, column); + int type = sqlite3_column_type(m_stmt.get(), column); return type == SQLITE_NULL; } @@ -181,7 +171,7 @@ namespace AppInstaller::Repository::SQLite { AICLI_LOG(SQL, Verbose, << "Reset statement #" << m_id); // Ignore return value from reset, as if it is an error, it was the error from the last call to step. - sqlite3_reset(m_stmt); + sqlite3_reset(m_stmt.get()); m_state = State::Prepared; } diff --git a/src/AppInstallerRepositoryCore/SQLiteWrapper.h b/src/AppInstallerRepositoryCore/SQLiteWrapper.h @@ -2,6 +2,7 @@ // Licensed under the MIT License. #pragma once #include <wil/result_macros.h> +#include <wil/resource.h> #include <winsqlite/winsqlite3.h> #include <AppInstallerLogging.h> @@ -109,19 +110,19 @@ namespace AppInstaller::Repository::SQLite Connection(const Connection&) = delete; Connection& operator=(const Connection&) = delete; - Connection(Connection&& other) noexcept { std::swap(m_dbconn, other.m_dbconn); } - Connection& operator=(Connection&& other) noexcept { std::swap(m_dbconn, other.m_dbconn); return *this; } + Connection(Connection&& other) = default; + Connection& operator=(Connection&& other) = default; - ~Connection(); + ~Connection() = default; int64_t GetLastInsertRowID(); - operator sqlite3* () const { return m_dbconn; } + operator sqlite3* () const { return m_dbconn.get(); } private: Connection(const std::string& target, OpenDisposition disposition, OpenFlags flags); - sqlite3* m_dbconn = nullptr; + wil::unique_any<sqlite3*, decltype(sqlite3_close_v2), sqlite3_close_v2> m_dbconn; }; // A SQL statement. @@ -136,12 +137,10 @@ namespace AppInstaller::Repository::SQLite Statement(const Statement&) = delete; Statement& operator=(const Statement&) = delete; - Statement(Statement&& other) noexcept { std::swap(m_stmt, other.m_stmt); std::swap(m_state, other.m_state); } - Statement& operator=(Statement&& other) noexcept { std::swap(m_stmt, other.m_stmt); std::swap(m_state, other.m_state); return *this; } + Statement(Statement&& other) = default; + Statement& operator=(Statement&& other) = default; - ~Statement(); - - operator sqlite3_stmt* () const { return m_stmt; } + operator sqlite3_stmt* () const { return m_stmt.get(); } // The state of the statement. enum class State @@ -165,7 +164,7 @@ namespace AppInstaller::Repository::SQLite void Bind(int index, Value&& v) { AICLI_LOG(SQL, Verbose, << "Binding statement #" << m_id << ": " << index << " => " << std::forward<Value>(v)); - details::ParameterSpecifics<Value>::Bind(m_stmt, index, std::forward<Value>(v)); + details::ParameterSpecifics<Value>::Bind(m_stmt.get(), index, std::forward<Value>(v)); } // Evaluate the statement; either retrieving the next row or executing some action. @@ -186,7 +185,7 @@ namespace AppInstaller::Repository::SQLite Value GetColumn(int column) { THROW_HR_IF(E_BOUNDS, m_state != State::HasRow); - return details::ParameterSpecifics<Value>::GetColumn(m_stmt, column); + return details::ParameterSpecifics<Value>::GetColumn(m_stmt.get(), column); } // Gets the entire row of values from the current row. @@ -213,11 +212,11 @@ namespace AppInstaller::Repository::SQLite std::tuple<Values...> GetRowImpl(std::integer_sequence<int, I...>) { THROW_HR_IF(E_BOUNDS, m_state != State::HasRow); - return std::make_tuple(details::ParameterSpecifics<Values>::GetColumn(m_stmt, I)...); + return std::make_tuple(details::ParameterSpecifics<Values>::GetColumn(m_stmt.get(), I)...); } size_t m_id = 0; - sqlite3_stmt* m_stmt = nullptr; + wil::unique_any<sqlite3_stmt*, decltype(sqlite3_finalize), sqlite3_finalize> m_stmt; State m_state = State::Prepared; };