commit ebca6192efcb117aee92ab1ab21dd76db1076ce5 parent c62098a12571bea5fdc2eda465f62df325bedcb1 Author: JohnMcPMS <johnmcp@microsoft.com> Date: Tue, 10 Mar 2020 08:48:27 -0700 Implement search across all fields (#48) Diffstat:
21 files changed, 996 insertions(+), 24 deletions(-)
diff --git a/src/AppInstallerCLITests/SQLiteIndex.cpp b/src/AppInstallerCLITests/SQLiteIndex.cpp @@ -15,6 +15,7 @@ #include <Microsoft/Schema/1_0/ManifestTable.h> #include <Microsoft/Schema/1_0/TagsTable.h> #include <Microsoft/Schema/1_0/CommandsTable.h> +#include <Microsoft/Schema/1_0/SearchResultsTable.h> using namespace std::string_literals; using namespace TestCommon; @@ -42,6 +43,44 @@ SQLiteIndex SimpleTestSetup(const std::string& filePath, Manifest& manifest, std return index; } +struct IndexFields +{ + std::string Id; + std::string Name; + std::string Moniker; + std::string Version; + std::string Channel; + std::vector<std::string> Tags; + std::vector<std::string> Commands; + std::string Path; +}; + +SQLiteIndex SearchTestSetup(const std::string& filePath, std::initializer_list<IndexFields> data = {}, Schema::Version version = Schema::Version::Latest()) +{ + SQLiteIndex index = SQLiteIndex::CreateNew(filePath, version); + + Manifest manifest; + + auto addFunc = [&](const IndexFields& d) { + manifest.Id = d.Id; + manifest.Name = d.Name; + manifest.AppMoniker = d.Moniker; + manifest.Version = d.Version; + manifest.Channel = d.Channel; + manifest.Tags = d.Tags; + manifest.Commands = d.Commands; + + index.AddManifest(manifest, d.Path); + }; + + for (const auto& d : data) + { + addFunc(d); + } + + return index; +} + TEST_CASE("SQLiteIndexCreateLatestAndReopen", "[sqliteindex]") { TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; @@ -617,3 +656,147 @@ TEST_CASE("SQLiteIndex_Versions", "[sqliteindex]") REQUIRE(result[0].first == manifest.Version); REQUIRE(result[0].second == manifest.Channel); } + +TEST_CASE("SQLiteIndex_SearchResultsTableSearches", "[sqliteindex][V1_0]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + Manifest manifest; + std::string relativePath; + { + (void)SimpleTestSetup(tempFile, manifest, relativePath); + } + + Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadOnly); + Schema::V1_0::SearchResultsTable search(connection); + + std::string value = "test"; + + // Perform every type of field and match search + for (auto field : { ApplicationMatchField::Id, ApplicationMatchField::Name, ApplicationMatchField::Moniker, ApplicationMatchField::Tag, ApplicationMatchField::Command }) + { + for (auto match : { MatchType::Exact, MatchType::Fuzzy, MatchType::FuzzySubstring, MatchType::Substring, MatchType::Wildcard }) + { + search.SearchOnField(field, match, value); + } + } +} + +TEST_CASE("SQLiteIndex_Search_EmptySearch", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile,{ + { "Id1", "Name", "Moniker", "Version1", "Channel", { "Tag" }, { "Command" }, "Path1" }, + { "Id1", "Name", "Moniker", "Version2", "Channel", { "Tag" }, { "Command" }, "Path2" }, + { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, + { "Id3", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path4" }, + }); + + SearchRequest request; + + auto results = index.Search(request); + REQUIRE(results.size() == 3); +} + +TEST_CASE("SQLiteIndex_Search_Exact", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1" }, + { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, + }); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Exact, "Id"); + + auto results = index.Search(request); + REQUIRE(results.size() == 1); +} + +TEST_CASE("SQLiteIndex_Search_Substring", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1" }, + { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, + }); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Substring, "Id"); + + auto results = index.Search(request); + REQUIRE(results.size() == 2); +} + +TEST_CASE("SQLiteIndex_Search_ExactBeforeSubstring", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1" }, + { "Id", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, + }); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Substring, "Id"); + + auto results = index.Search(request); + REQUIRE(results.size() == 2); + + REQUIRE(index.GetIdStringById(results[0].first) == "Id"); + REQUIRE(index.GetIdStringById(results[1].first) == "Id2"); +} + +TEST_CASE("SQLiteIndex_Search_Filter", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1" }, + { "Id2", "Na", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, + { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, + }); + + SearchRequest request; + request.Filters.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "a"); + + auto results = index.Search(request); + REQUIRE(results.size() == 2); + + request.Filters[0].Value = "e"; + + results = index.Search(request); + REQUIRE(results.size() == 1); +} + +TEST_CASE("SQLiteIndex_Search_Multimatch", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1" }, + { "Id1", "Name1", "Moniker", "Version1", "Channel", { "Tag" }, { "Command" }, "Path2" }, + { "Id2", "Name", "Moniker", "Version", "", { "Tag" }, { "Command" }, "Path3" }, + { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path4" }, + { "Id3", "Name", "Moniker", "Version1", "", { "Tag" }, { "Command" }, "Path5" }, + { "Id3", "Name", "Moniker", "Version2", "", { "Tag" }, { "Command" }, "Path6" }, + { "Id3", "Name", "Moniker", "Version3", "", { "Tag" }, { "Command" }, "Path7" }, + }); + + SearchRequest request; + // An empty string should match all substrings + request.Query = RequestMatch(MatchType::Substring, ""); + + auto results = index.Search(request); + REQUIRE(results.size() == 3); +} diff --git a/src/AppInstallerCLITests/SQLiteWrapper.cpp b/src/AppInstallerCLITests/SQLiteWrapper.cpp @@ -260,6 +260,32 @@ TEST_CASE("SQLiteWrapperSavepointReuse", "[sqlitewrapper]") } } +TEST_CASE("SQLiteWrapper_EscapeStringForLike", "[sqlitewrapper]") +{ + std::string escape(EscapeCharForLike); + + std::string input = "test"; + std::string output = EscapeStringForLike(input); + REQUIRE(input == output); + + input = EscapeCharForLike; + output = EscapeStringForLike(input); + REQUIRE((input + input) == output); + + input = "%"; + output = EscapeStringForLike(input); + REQUIRE((escape + input) == output); + + input = "_"; + output = EscapeStringForLike(input); + REQUIRE((escape + input) == output); + + input = "%_A_%"; + std::string expected = escape + "%" + escape + "_A" + escape + "_" + escape + "%"; + output = EscapeStringForLike(input); + REQUIRE(expected == output); +} + TEST_CASE("SQLBuilder_SimpleSelectBind", "[sqlbuilder]") { Connection connection = Connection::Create(SQLITE_MEMORY_DB_CONNECTION_TARGET, Connection::OpenDisposition::Create); diff --git a/src/AppInstallerCommonCore/Public/AppInstallerLanguageUtilities.h b/src/AppInstallerCommonCore/Public/AppInstallerLanguageUtilities.h @@ -52,4 +52,25 @@ namespace AppInstaller template <typename T> FoldHelper& operator,(T&&) { return *this; } }; + + // Get the integral value for an enum. + template <typename E> + inline std::enable_if_t<std::is_enum_v<E>, std::underlying_type_t<E>> ToIntegral(E e) + { + return static_cast<std::underlying_type_t<E>>(e); + } + + // Get the enum value for an integral. + template <typename E> + inline std::enable_if_t<std::is_enum_v<E>, E> ToEnum(std::underlying_type_t<E> ut) + { + return static_cast<E>(ut); + } +} + +// Enable enums to be output generically (as their integral value). +template <typename E> +std::enable_if_t<std::is_enum_v<E>, std::ostream&> operator<<(std::ostream& out, E e) +{ + return out << AppInstaller::ToIntegral(e); } diff --git a/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj b/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj @@ -182,6 +182,7 @@ <ClInclude Include="Microsoft\Schema\1_0\OneToManyTable.h" /> <ClInclude Include="Microsoft\Schema\1_0\OneToOneTable.h" /> <ClInclude Include="Microsoft\Schema\1_0\PathPartTable.h" /> + <ClInclude Include="Microsoft\Schema\1_0\SearchResultsTable.h" /> <ClInclude Include="Microsoft\Schema\1_0\TagsTable.h" /> <ClInclude Include="Microsoft\Schema\1_0\VersionTable.h" /> <ClInclude Include="Microsoft\Schema\ISQLiteIndex.h" /> @@ -194,6 +195,7 @@ <ClInclude Include="SQLiteStatementBuilder.h" /> <ClInclude Include="Public\AppInstallerRepositorySearch.h" /> <ClInclude Include="Public\AppInstallerRepositorySource.h" /> + <ClInclude Include="SQLiteTempTable.h" /> <ClInclude Include="SQLiteWrapper.h" /> </ItemGroup> <ItemGroup> @@ -206,6 +208,7 @@ <ClCompile Include="Microsoft\Schema\1_0\OneToManyTable.cpp" /> <ClCompile Include="Microsoft\Schema\1_0\OneToOneTable.cpp" /> <ClCompile Include="Microsoft\Schema\1_0\PathPartTable.cpp" /> + <ClCompile Include="Microsoft\Schema\1_0\SearchResultsTable.cpp" /> <ClCompile Include="Microsoft\Schema\MetadataTable.cpp" /> <ClCompile Include="Microsoft\Schema\Version.cpp" /> <ClCompile Include="Microsoft\SQLiteIndex.cpp" /> @@ -215,6 +218,7 @@ </ClCompile> <ClCompile Include="RepositorySource.cpp" /> <ClCompile Include="SQLiteStatementBuilder.cpp" /> + <ClCompile Include="SQLiteTempTable.cpp" /> <ClCompile Include="SQLiteWrapper.cpp" /> </ItemGroup> <ItemGroup> diff --git a/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj.filters b/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj.filters @@ -111,6 +111,12 @@ <ClInclude Include="Microsoft\SQLiteIndexSource.h"> <Filter>Microsoft</Filter> </ClInclude> + <ClInclude Include="SQLiteTempTable.h"> + <Filter>Header Files</Filter> + </ClInclude> + <ClInclude Include="Microsoft\Schema\1_0\SearchResultsTable.h"> + <Filter>Microsoft\Schema\1_0</Filter> + </ClInclude> </ItemGroup> <ItemGroup> <ClCompile Include="pch.cpp"> @@ -164,6 +170,12 @@ <ClCompile Include="Microsoft\SQLiteIndexSource.cpp"> <Filter>Microsoft</Filter> </ClCompile> + <ClCompile Include="SQLiteTempTable.cpp"> + <Filter>Source Files</Filter> + </ClCompile> + <ClCompile Include="Microsoft\Schema\1_0\SearchResultsTable.cpp"> + <Filter>Microsoft\Schema\1_0</Filter> + </ClCompile> </ItemGroup> <ItemGroup> <None Include="PropertySheet.props" /> diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface.cpp @@ -16,6 +16,9 @@ #include "Microsoft/Schema/1_0/TagsTable.h" #include "Microsoft/Schema/1_0/CommandsTable.h" +#include "Microsoft/Schema/1_0/SearchResultsTable.h" + + namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { namespace @@ -125,6 +128,26 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 Table::DeleteIfNotNeededById(connection, oldValueId); } + + // Gets the ordering of matches to execute, with more specific matches coming first. + std::vector<MatchType> GetMatchTypeOrder(MatchType type) + { + switch (type) + { + case MatchType::Exact: + return { MatchType::Exact }; + case MatchType::Substring: + return { MatchType::Exact, MatchType::Substring }; + case MatchType::Wildcard: + return { MatchType::Wildcard }; + case MatchType::Fuzzy: + return { MatchType::Exact, MatchType::Substring, MatchType::Fuzzy }; + case MatchType::FuzzySubstring: + return { MatchType::Exact, MatchType::Substring, MatchType::Fuzzy, MatchType::FuzzySubstring }; + default: + THROW_HR(E_UNEXPECTED); + } + } } Schema::Version Interface::GetVersion() const @@ -319,36 +342,74 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 std::vector<std::pair<SQLite::rowid_t, ApplicationMatchFilter>> Interface::Search(SQLite::Connection& connection, const SearchRequest& request) { - // Initial implementation handles only exact match on id, future change will implement more. - // TODO: Handle more MatchTypes - // TODO: Handle more query fields - // TODO: Handle filters - // TODO: Handle maximum count + // If no query or filters, get everything + if (!request.Query && request.Filters.empty()) + { + std::vector<SQLite::rowid_t> ids = IdTable::GetAllRowIds(connection, request.MaximumResults); + + std::vector<std::pair<SQLite::rowid_t, ApplicationMatchFilter>> result; + for (SQLite::rowid_t id : ids) + { + result.emplace_back(std::make_pair(id, ApplicationMatchFilter(ApplicationMatchField::Id, MatchType::Wildcard, {}))); + } + return result; + } + + // First phase, create the search results table and populate it with the initial results. + // If the Query is provided, we search across many fields and put results in together. + // If not, we take the first filter and use it as the initial results search. + SearchResultsTable resultsTable(connection); + size_t filterIndex = 0; if (request.Query) { - std::optional<SQLite::rowid_t> id = IdTable::SelectIdByValue(connection, request.Query->Value); - if (id) + // Perform searches across multiple tables to populate the initial results. + const RequestMatch& query = request.Query.value(); + + for (MatchType match : GetMatchTypeOrder(query.Type)) { - return { { id.value(), ApplicationMatchFilter(ApplicationMatchField::Id, MatchType::Exact, request.Query->Value) } }; + resultsTable.SearchOnField(ApplicationMatchField::Id, match, query.Value); + resultsTable.SearchOnField(ApplicationMatchField::Name, match, query.Value); + resultsTable.SearchOnField(ApplicationMatchField::Moniker, match, query.Value); + resultsTable.SearchOnField(ApplicationMatchField::Command, match, query.Value); + resultsTable.SearchOnField(ApplicationMatchField::Tag, match, query.Value); } - else + } + else + { + THROW_HR_IF(E_UNEXPECTED, request.Filters.empty()); + + // Perform search for just the field matching this filter + const ApplicationMatchFilter& filter = request.Filters[0]; + + for (MatchType match : GetMatchTypeOrder(filter.Type)) { - return {}; + resultsTable.SearchOnField(filter.Field, match, filter.Value); } + + // Skip the filter as we already know everything matches + filterIndex = 1; } - else + + // Remove any duplicate manifest entries + resultsTable.RemoveDuplicateManifestRows(); + + // Second phase, for remaining filters, flag matching search results, then remove unflagged values. + for (size_t i = filterIndex; i < request.Filters.size(); ++i) { - // No query, get everything - std::vector<SQLite::rowid_t> ids = IdTable::GetAllRowIds(connection); + const ApplicationMatchFilter& filter = request.Filters[i]; - std::vector<std::pair<SQLite::rowid_t, ApplicationMatchFilter>> result; - for (SQLite::rowid_t id : ids) + resultsTable.PrepareToFilter(); + + for (MatchType match : GetMatchTypeOrder(filter.Type)) { - result.emplace_back(std::make_pair(id, ApplicationMatchFilter(ApplicationMatchField::Id, MatchType::Wildcard, ""))); + resultsTable.FilterOnField(filter.Field, match, filter.Value); } - return result; + + resultsTable.CompleteFilter(); } + + return resultsTable.GetSearchResults(request.MaximumResults); } std::optional<std::string> Interface::GetIdStringById(SQLite::Connection& connection, SQLite::rowid_t id) diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.cpp @@ -3,6 +3,7 @@ #include "pch.h" #include "ManifestTable.h" #include "SQLiteStatementBuilder.h" +#include "OneToManyTable.h" namespace AppInstaller::Repository::Microsoft::Schema::V1_0 @@ -164,6 +165,56 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 return result; } + int ManifestTableBuildSearchStatement( + SQLite::Builder::StatementBuilder& builder, + const SQLite::Builder::QualifiedColumn& column, + bool isOneToOne, + std::string_view manifestAlias, + std::string_view valueAlias, + bool useLike) + { + using QCol = SQLite::Builder::QualifiedColumn; + + // Build a statement like: + // SELECT manifest.rowid as m, ids.id as v from manifest join ids on manifest.id = ids.rowid where ids.id = <value> + // OR + // SELECT manifest.rowid as m, tags.tag as v from manifest join tags_map on manifest.rowid = tags_map.manifest + // join tags on tags_map.tag = tags.rowid where tags.tag = <value> + builder.Select(). + Column(QCol(s_ManifestTable_Table_Name, SQLite::RowIDName)).As(manifestAlias). + Column(column).As(valueAlias); + + if (isOneToOne) + { + builder.From(s_ManifestTable_Table_Name). + Join(column.Table).On(QCol(s_ManifestTable_Table_Name, column.Column), QCol(column.Table, SQLite::RowIDName)). + Where(column); + } + else + { + std::string mapTableName = details::OneToManyTableGetMapTableName(column.Table); + builder.From(s_ManifestTable_Table_Name). + Join(mapTableName).On(QCol(s_ManifestTable_Table_Name, SQLite::RowIDName), QCol(mapTableName, details::OneToManyTableGetManifestColumnName())). + Join(column.Table).On(QCol(mapTableName, column.Column), QCol(column.Table, SQLite::RowIDName)). + Where(column); + } + + int result = 0; + if (useLike) + { + builder.Like(SQLite::Builder::Unbound); + result = builder.GetLastBindIndex(); + builder.Escape(SQLite::EscapeCharForLike); + } + else + { + builder.Equals(SQLite::Builder::Unbound); + result = builder.GetLastBindIndex(); + } + + return result; + } + void ManifestTableUpdateValueIdById(SQLite::Connection& connection, std::string_view valueName, SQLite::rowid_t value, SQLite::rowid_t id) { SQLite::Builder::StatementBuilder builder; @@ -173,6 +224,11 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 } } + std::string_view ManifestTable::TableName() + { + return s_ManifestTable_Table_Name; + } + void ManifestTable::Create(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values) { using namespace SQLite::Builder; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.h @@ -45,6 +45,15 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 std::initializer_list<std::string_view> idColumns, std::initializer_list<SQLite::rowid_t> ids); + // Builds the search select statement base on the given values. + int ManifestTableBuildSearchStatement( + SQLite::Builder::StatementBuilder& builder, + const SQLite::Builder::QualifiedColumn& column, + bool isOneToOne, + std::string_view manifestAlias, + std::string_view valueAlias, + bool useLike); + // Update the value of a single column for the manifest with the given rowid. void ManifestTableUpdateValueIdById(SQLite::Connection& connection, std::string_view valueName, SQLite::rowid_t value, SQLite::rowid_t id); } @@ -67,6 +76,9 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // A table that represents a single manifest struct ManifestTable { + // Get the table name. + static std::string_view TableName(); + // Creates the table. static void Create(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values); @@ -115,6 +127,13 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 return result; } + // Builds the search select statement base on the given values. + template <typename Table> + static int BuildSearchStatement(SQLite::Builder::StatementBuilder& builder, std::string_view manifestAlias, std::string_view valueAlias, bool useLike) + { + return details::ManifestTableBuildSearchStatement(builder, SQLite::Builder::QualifiedColumn{ Table::TableName(), Table::ValueName() }, Table::IsOneToOne(), manifestAlias, valueAlias, useLike); + } + // Update the value of a single column for the manifest with the given rowid. template <typename Table> static void UpdateValueIdById(SQLite::Connection& connection, SQLite::rowid_t id, SQLite::rowid_t value) diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.cpp @@ -84,6 +84,18 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 }; } + std::string OneToManyTableGetMapTableName(std::string_view tableName) + { + std::string result(tableName); + result += s_OneToManyTable_MapTable_Suffix; + return result; + } + + std::string_view OneToManyTableGetManifestColumnName() + { + return s_OneToManyTable_MapTable_ManifestName; + } + void CreateOneToManyTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName) { using namespace SQLite::Builder; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.h @@ -11,6 +11,12 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { namespace details { + // Returns the map table name for a given table. + std::string OneToManyTableGetMapTableName(std::string_view tableName); + + // Returns the manifest column name. + std::string_view OneToManyTableGetManifestColumnName(); + // Create the tables. void CreateOneToManyTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName); @@ -38,6 +44,24 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 template <typename TableInfo> struct OneToManyTable { + // The name of the table. + static constexpr std::string_view TableName() + { + return TableInfo::TableName(); + } + + // The value name of the table. + static constexpr std::string_view ValueName() + { + return TableInfo::ValueName(); + } + + // Value indicating type. + static constexpr bool IsOneToOne() + { + return false; + } + // Creates the table. static void Create(SQLite::Connection& connection) { diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.cpp @@ -56,11 +56,16 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 } } - std::vector<SQLite::rowid_t> OneToOneTableGetAllRowIds(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName) + std::vector<SQLite::rowid_t> OneToOneTableGetAllRowIds(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, size_t limit) { SQLite::Builder::StatementBuilder selectBuilder; selectBuilder.Select(SQLite::RowIDName).From(tableName).OrderBy(valueName); + if (limit) + { + selectBuilder.Limit(limit); + } + SQLite::Statement select = selectBuilder.Prepare(connection); std::vector<SQLite::rowid_t> result; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.h @@ -22,7 +22,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 std::optional<std::string> OneToOneTableSelectValueById(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t id); // Gets all row ids from the table. - std::vector<SQLite::rowid_t> OneToOneTableGetAllRowIds(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName); + std::vector<SQLite::rowid_t> OneToOneTableGetAllRowIds(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, size_t limit); // Ensures that the values exists in the table. SQLite::rowid_t OneToOneTableEnsureExists(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, std::string_view value); @@ -62,6 +62,12 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 return TableInfo::ValueName(); } + // Value indicating type. + static constexpr bool IsOneToOne() + { + return true; + } + // Selects the value from the table, returning the rowid if it exists. static std::optional<SQLite::rowid_t> SelectIdByValue(SQLite::Connection& connection, std::string_view value) { @@ -75,9 +81,9 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 } // Gets all row ids from the table. - static std::vector<SQLite::rowid_t> GetAllRowIds(SQLite::Connection& connection) + static std::vector<SQLite::rowid_t> GetAllRowIds(SQLite::Connection& connection, size_t limit = 0) { - return details::OneToOneTableGetAllRowIds(connection, TableInfo::TableName(), TableInfo::ValueName()); + return details::OneToOneTableGetAllRowIds(connection, TableInfo::TableName(), TableInfo::ValueName(), limit); } // Ensures that the given value exists in the table, returning the rowid. diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.cpp @@ -0,0 +1,197 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "pch.h" +#include "SearchResultsTable.h" +#include "SQLiteStatementBuilder.h" + +#include "Microsoft/Schema/1_0/IdTable.h" +#include "Microsoft/Schema/1_0/NameTable.h" +#include "Microsoft/Schema/1_0/MonikerTable.h" +#include "Microsoft/Schema/1_0/ManifestTable.h" +#include "Microsoft/Schema/1_0/TagsTable.h" +#include "Microsoft/Schema/1_0/CommandsTable.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_0 +{ + namespace + { + using namespace std::string_literals; + using namespace std::string_view_literals; + + constexpr std::string_view s_SearchResultsTable_Manifest = "manifest"sv; + constexpr std::string_view s_SearchResultsTable_MatchField = "field"sv; + constexpr std::string_view s_SearchResultsTable_MatchType = "match"sv; + constexpr std::string_view s_SearchResultsTable_MatchValue = "value"sv; + constexpr std::string_view s_SearchResultsTable_SortValue = "sort"sv; + constexpr std::string_view s_SearchResultsTable_Filter = "filter"sv; + + constexpr std::string_view s_SearchResultsTable_SubSelect_TableAlias = "valueTable"sv; + constexpr std::string_view s_SearchResultsTable_SubSelect_ManifestAlias = "m"sv; + constexpr std::string_view s_SearchResultsTable_SubSelect_ValueAlias = "v"sv; + + void ExecuteStatementForMatchType(SQLite::Statement& statement, MatchType match, int bindIndex, bool escapeValueForLike, std::string_view value) + { + // TODO: Implement these more complex match types + if (match == MatchType::Wildcard || match == MatchType::Fuzzy || match == MatchType::FuzzySubstring) + { + AICLI_LOG(Repo, Verbose, << "Specific match type not implemented, skipping: " << MatchTypeToString(match)); + return; + } + + std::string_view valueToUse = value; + std::string escapedValue; + if (escapeValueForLike) + { + escapedValue = SQLite::EscapeStringForLike(value); + valueToUse = escapedValue; + } + + if (match == MatchType::Substring) + { + escapedValue = "%"s + std::string(valueToUse) + '%'; + valueToUse = escapedValue; + } + + statement.Bind(bindIndex, valueToUse); + + statement.Execute(); + } + } + + SearchResultsTable::SearchResultsTable(SQLite::Connection& connection) : + m_connection(connection) + { + using namespace SQLite::Builder; + + StatementBuilder builder; + builder.CreateTable(GetQualifiedName()).BeginColumns(); + + builder.Column(ColumnBuilder(s_SearchResultsTable_Manifest, Type::RowId).NotNull()); + builder.Column(ColumnBuilder(s_SearchResultsTable_MatchField, Type::Int).NotNull()); + builder.Column(ColumnBuilder(s_SearchResultsTable_MatchType, Type::Int).NotNull()); + builder.Column(ColumnBuilder(s_SearchResultsTable_MatchValue, Type::Text).NotNull()); + builder.Column(ColumnBuilder(s_SearchResultsTable_SortValue, Type::Int).NotNull()); + builder.Column(ColumnBuilder(s_SearchResultsTable_Filter, Type::Bool).NotNull()); + + builder.EndColumns(); + + builder.Execute(m_connection); + + InitDropStatement(m_connection); + } + + void SearchResultsTable::SearchOnField(ApplicationMatchField field, MatchType match, std::string_view value) + { + using namespace SQLite::Builder; + + int sortOrdinal = m_sortOrdinalValue++; + + // Create an insert statement to select values into the table as requested. + // The goal is a statement like this: + // INSERT INTO <tempTable> + // SELECT valueTable.m, <field>, <match>, valueTable.v, <sort>, <filter> FROM + // (SELECT manifest.rowid as m, manifest.id as v from manifest join ids on manifest.id = ids.rowid where ids.id = <value>) AS valueTable + // Where the subselect is built by the owning table. + StatementBuilder builder; + builder.InsertInto(GetQualifiedName()).Select(). + Column(QualifiedColumn(s_SearchResultsTable_SubSelect_TableAlias, s_SearchResultsTable_SubSelect_ManifestAlias)). + Value(field). + Value(match). + Column(QualifiedColumn(s_SearchResultsTable_SubSelect_TableAlias, s_SearchResultsTable_SubSelect_ValueAlias)). + Value(sortOrdinal). + Value(false). + From().BeginParenthetical(); + + bool useLike = (match != MatchType::Exact); + int bindIndex = 0; + + switch (field) + { + case ApplicationMatchField::Id: + bindIndex = ManifestTable::BuildSearchStatement<IdTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); + break; + case ApplicationMatchField::Name: + bindIndex = ManifestTable::BuildSearchStatement<NameTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); + break; + case ApplicationMatchField::Moniker: + bindIndex = ManifestTable::BuildSearchStatement<MonikerTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); + break; + case ApplicationMatchField::Tag: + bindIndex = ManifestTable::BuildSearchStatement<TagsTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); + break; + case ApplicationMatchField::Command: + bindIndex = ManifestTable::BuildSearchStatement<CommandsTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); + break; + default: + THROW_HR(E_UNEXPECTED); + } + + builder.EndParenthetical().As(s_SearchResultsTable_SubSelect_TableAlias); + + SQLite::Statement statement = builder.Prepare(m_connection); + ExecuteStatementForMatchType(statement, match, bindIndex, useLike, value); + } + + void SearchResultsTable::RemoveDuplicateManifestRows() + { + + } + + void SearchResultsTable::PrepareToFilter() + { + + } + + void SearchResultsTable::FilterOnField(ApplicationMatchField field, MatchType match, std::string_view value) + { + UNREFERENCED_PARAMETER(field); + UNREFERENCED_PARAMETER(match); + UNREFERENCED_PARAMETER(value); + } + + void SearchResultsTable::CompleteFilter() + { + + } + + std::vector<std::pair<SQLite::rowid_t, ApplicationMatchFilter>> SearchResultsTable::GetSearchResults(size_t limit) + { + constexpr std::string_view tempTableAlias = "t"sv; + + using namespace SQLite::Builder; + using QCol = QualifiedColumn; + + // Select all unique ids from the results table, and their highest ordered match. + // The goal is a statement like this: + // SELECT m.id, field, match, value, min(sort) from <temp> join manifest on rowid = manifest group by m.id order by t.sort + // Through the "group by m.id", we will only ever have one row per id, and the "min(sort)" returns us one of the rows that matched + // through the earliest search. We also order by the sort value to have the earliest search matches first in the list + StatementBuilder builder; + builder.Select(). + Column(QCol(ManifestTable::TableName(), IdTable::ValueName())). + Column(QCol(tempTableAlias, s_SearchResultsTable_MatchField)). + Column(QCol(tempTableAlias, s_SearchResultsTable_MatchType)). + Column(QCol(tempTableAlias, s_SearchResultsTable_MatchValue)). + Column(Aggregate::Min, QCol(tempTableAlias, s_SearchResultsTable_SortValue)). + From(GetQualifiedName()).As(tempTableAlias). + Join(ManifestTable::TableName()).On(QCol(tempTableAlias, s_SearchResultsTable_Manifest), QCol(ManifestTable::TableName(), SQLite::RowIDName)). + GroupBy(QCol(ManifestTable::TableName(), IdTable::ValueName())).OrderBy(QCol(tempTableAlias, s_SearchResultsTable_SortValue)); + + if (limit) + { + builder.Limit(limit); + } + + SQLite::Statement select = builder.Prepare(m_connection); + + std::vector<std::pair<SQLite::rowid_t, ApplicationMatchFilter>> result; + while (select.Step()) + { + result.emplace_back(select.GetColumn<SQLite::rowid_t>(0), + ApplicationMatchFilter(select.GetColumn<ApplicationMatchField>(1), select.GetColumn<MatchType>(2), select.GetColumn<std::string>(3))); + } + return result; + } +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.h @@ -0,0 +1,47 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "SQLiteWrapper.h" +#include "SQLiteTempTable.h" +#include "AppInstallerRepositorySearch.h" + +#include <utility> +#include <vector> + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_0 +{ + // Table for holding temporary search results. + struct SearchResultsTable : public SQLite::TempTable + { + SearchResultsTable(SQLite::Connection& connection); + + SearchResultsTable(const SearchResultsTable&) = delete; + SearchResultsTable& operator=(const SearchResultsTable&) = delete; + + SearchResultsTable(SearchResultsTable&&) = default; + SearchResultsTable& operator=(SearchResultsTable&&) = default; + + // Performs the requested search type on the requested field. + void SearchOnField(ApplicationMatchField field, MatchType match, std::string_view value); + + // Removes rows with manifest ids whose sort order is below the highest one. + void RemoveDuplicateManifestRows(); + + // Prepares the table for a filtering pass. + void PrepareToFilter(); + + // Performs the requested filter type on the requested field. + void FilterOnField(ApplicationMatchField field, MatchType match, std::string_view value); + + // Completes a filtering pass, removing filtered rows. + void CompleteFilter(); + + // Gets the results from the table. + std::vector<std::pair<SQLite::rowid_t, ApplicationMatchFilter>> GetSearchResults(size_t limit = 0); + + private: + SQLite::Connection& m_connection; + int m_sortOrdinalValue = 0; + }; +} diff --git a/src/AppInstallerRepositoryCore/Public/AppInstallerRepositorySearch.h b/src/AppInstallerRepositoryCore/Public/AppInstallerRepositorySearch.h @@ -19,6 +19,7 @@ namespace AppInstaller::Repository Substring, Wildcard, Fuzzy, + FuzzySubstring, }; // The field to match on. @@ -120,6 +121,8 @@ namespace AppInstaller::Repository return "Wildcard"sv; case MatchType::Fuzzy: return "Fuzzy"sv; + case MatchType::FuzzySubstring: + return "FuzzySubstring"sv; } return "UnknownMatchType"sv; diff --git a/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.cpp b/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.cpp @@ -63,6 +63,31 @@ namespace AppInstaller::Repository::SQLite::Builder } } + void OutputAggregate(std::ostream& out, Aggregate op) + { + out << ' '; + switch (op) + { + case Aggregate::Min: + out << "MIN"; + break; + default: + THROW_HR(E_UNEXPECTED); + } + } + + void OutputColumns(std::ostream& out, Aggregate op, std::string_view column) + { + OutputAggregate(out, op); + out << "([" << column << "])"; + } + + void OutputColumns(std::ostream& out, Aggregate op, const QualifiedColumn& column) + { + OutputAggregate(out, op); + out << '(' << column << ')'; + } + // Use to output operation and table name, such as " FROM [table]" void OutputOperationAndTable(std::ostream& out, std::string_view op, std::string_view table) { @@ -114,6 +139,12 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + ColumnBuilder& ColumnBuilder::Default(int64_t value) + { + m_stream << " DEFAULT " << value; + return *this; + } + ColumnBuilder& ColumnBuilder::Unique(bool isTrue) { if (isTrue) @@ -171,6 +202,7 @@ namespace AppInstaller::Repository::SQLite::Builder StatementBuilder& StatementBuilder::Select() { m_stream << "SELECT "; + m_needsComma = false; return *this; } @@ -204,6 +236,12 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::From() + { + m_stream << " FROM "; + return *this; + } + StatementBuilder& StatementBuilder::From(std::string_view table) { OutputOperationAndTable(m_stream, " FROM", table); @@ -234,6 +272,19 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::Like(details::unbound_t) + { + AppendOpAndBinder(Op::Like); + return *this; + } + + StatementBuilder& StatementBuilder::Escape(std::string_view escapeChar) + { + THROW_HR_IF(E_INVALIDARG, escapeChar.length() != 1); + AddBindFunctor(AppendOpAndBinder(Op::Escape), escapeChar); + return *this; + } + StatementBuilder& StatementBuilder::Equals(std::nullptr_t) { // This is almost certainly not what you want. @@ -284,6 +335,18 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::GroupBy(std::string_view column) + { + OutputColumns(m_stream, " GROUP BY ", column); + return *this; + } + + StatementBuilder& StatementBuilder::GroupBy(const QualifiedColumn& column) + { + OutputColumns(m_stream, " GROUP BY ", column); + return *this; + } + StatementBuilder& StatementBuilder::OrderBy(std::string_view column) { OutputColumns(m_stream, " ORDER BY ", column); @@ -372,6 +435,28 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::Column(Aggregate aggOp, std::string_view column) + { + if (m_needsComma) + { + m_stream << ", "; + } + OutputColumns(m_stream, aggOp, column); + m_needsComma = true; + return *this; + } + + StatementBuilder& StatementBuilder::Column(Aggregate aggOp, const QualifiedColumn& column) + { + if (m_needsComma) + { + m_stream << ", "; + } + OutputColumns(m_stream, aggOp, column); + m_needsComma = true; + return *this; + } + StatementBuilder& StatementBuilder::Column(const details::SubBuilder& column) { if (m_needsComma) @@ -416,6 +501,18 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::DropTable(std::string_view table) + { + OutputOperationAndTable(m_stream, "DROP TABLE", table); + return *this; + } + + StatementBuilder& StatementBuilder::DropTable(std::initializer_list<std::string_view> table) + { + OutputOperationAndTable(m_stream, "DROP TABLE", table); + return *this; + } + StatementBuilder& StatementBuilder::CreateIndex(std::string_view table) { OutputOperationAndTable(m_stream, "CREATE INDEX", table); @@ -489,6 +586,24 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::BeginParenthetical() + { + m_stream << '('; + return *this; + } + + StatementBuilder& StatementBuilder::EndParenthetical() + { + m_stream << ')'; + return *this; + } + + StatementBuilder& StatementBuilder::As(std::string_view alias) + { + OutputOperationAndTable(m_stream, " AS", alias); + return *this; + } + Statement StatementBuilder::Prepare(Connection& connection, bool persistent) { Statement result = Statement::Create(connection, m_stream.str(), persistent); @@ -511,6 +626,12 @@ namespace AppInstaller::Repository::SQLite::Builder case Op::Equals: m_stream << " = ?"; break; + case Op::Like: + m_stream << " LIKE ?"; + break; + case Op::Escape: + m_stream << " ESCAPE ?"; + break; default: THROW_HR(E_UNEXPECTED); } diff --git a/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.h b/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.h @@ -75,10 +75,18 @@ namespace AppInstaller::Repository::SQLite::Builder enum class Type { Int, + Bool = Int, Int64, + RowId = Int64, Text, }; + // Aggregate functions. + enum class Aggregate + { + Min + }; + // Helper used when creating a table. struct ColumnBuilder : public details::SubBuilderBase { @@ -95,6 +103,11 @@ namespace AppInstaller::Repository::SQLite::Builder // Allow for data driven construction with input value. ColumnBuilder& NotNull(bool isTrue = true); + // Indicate the default value for the column. + // Note that a default value is not considered constant if it is bound, + // so this function directly places the incoming value into the SQL statement. + ColumnBuilder& Default(int64_t value); + // Indicate that the column is unique. // Allow for data driven construction with input value. ColumnBuilder& Unique(bool isTrue = true); @@ -147,6 +160,7 @@ namespace AppInstaller::Repository::SQLite::Builder // 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(); StatementBuilder& From(std::string_view table); StatementBuilder& From(std::initializer_list<std::string_view> table); @@ -177,6 +191,9 @@ namespace AppInstaller::Repository::SQLite::Builder StatementBuilder& Equals(details::unbound_t); StatementBuilder& Equals(std::nullptr_t); + StatementBuilder& Like(details::unbound_t); + StatementBuilder& Escape(std::string_view escapeChar); + StatementBuilder& IsNull(); // Operators for combining filter clauses. @@ -191,6 +208,10 @@ namespace AppInstaller::Repository::SQLite::Builder // Set the join constraint. StatementBuilder& On(const QualifiedColumn& column1, const QualifiedColumn& column2); + // Specify the grouping to use. + StatementBuilder& GroupBy(std::string_view column); + StatementBuilder& GroupBy(const QualifiedColumn& column); + // Specify the ordering to use. StatementBuilder& OrderBy(std::string_view column); StatementBuilder& OrderBy(const QualifiedColumn& column); @@ -209,11 +230,13 @@ namespace AppInstaller::Repository::SQLite::Builder StatementBuilder& Columns(const QualifiedColumn& column); StatementBuilder& Columns(std::initializer_list<QualifiedColumn> columns); - // Set the columns for a create table statement. + // Set the columns for a select or create table statement. StatementBuilder& Columns(std::initializer_list<details::SubBuilder> columns); StatementBuilder& BeginColumns(); StatementBuilder& Column(std::string_view column); StatementBuilder& Column(const QualifiedColumn& column); + StatementBuilder& Column(Aggregate aggOp, std::string_view column); + StatementBuilder& Column(Aggregate aggOp, const QualifiedColumn& column); StatementBuilder& Column(const details::SubBuilder& column); StatementBuilder& EndColumns(); @@ -242,6 +265,11 @@ namespace AppInstaller::Repository::SQLite::Builder StatementBuilder& CreateTable(std::string_view table); StatementBuilder& CreateTable(std::initializer_list<std::string_view> table); + // Begin an table deletion statement. + // The initializer_list form enables the table name to be constructed from multiple parts. + StatementBuilder& DropTable(std::string_view table); + StatementBuilder& DropTable(std::initializer_list<std::string_view> table); + // Begin an index creation statement. // The initializer_list form enables the table name to be constructed from multiple parts. StatementBuilder& CreateIndex(std::string_view table); @@ -272,6 +300,17 @@ namespace AppInstaller::Repository::SQLite::Builder // Output the set portion of an update statement. StatementBuilder& Vacuum(); + // General purpose functions to begin and end a parenthetical expression. + StatementBuilder& BeginParenthetical(); + StatementBuilder& EndParenthetical(); + + // Assign an alias to the previous item. + StatementBuilder& As(std::string_view alias); + + // Gets the last bound index. + // A value of zero indicates that nothing has been bound. + int GetLastBindIndex() const { return m_bindIndex - 1; } + // Prepares and returns the statement, applying any bindings that were requested. Statement Prepare(Connection& connection, bool persistent = false); @@ -281,7 +320,9 @@ namespace AppInstaller::Repository::SQLite::Builder private: enum class Op { - Equals + Equals, + Like, + Escape }; // Appends given the operation. diff --git a/src/AppInstallerRepositoryCore/SQLiteTempTable.cpp b/src/AppInstallerRepositoryCore/SQLiteTempTable.cpp @@ -0,0 +1,38 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "pch.h" +#include "SQLiteTempTable.h" +#include "SQLiteStatementBuilder.h" + + +namespace AppInstaller::Repository::SQLite +{ + TempTable::TempTable() + { + GUID tempName; + THROW_IF_FAILED(CoCreateGuid(&tempName)); + + wchar_t guidAsString[MAX_PATH]; + THROW_HR_IF(E_UNEXPECTED, StringFromGUID2(tempName, guidAsString, MAX_PATH) == 0); + + m_name = "temp].["; + m_name += Utility::ConvertToUTF8(guidAsString); + } + + TempTable::~TempTable() + { + if (m_dropTableStatement) + { + m_dropTableStatement.Execute(); + } + } + + void TempTable::InitDropStatement(Connection& connection) + { + Builder::StatementBuilder builder; + builder.DropTable(m_name); + + m_dropTableStatement = builder.Prepare(connection); + } +} diff --git a/src/AppInstallerRepositoryCore/SQLiteTempTable.h b/src/AppInstallerRepositoryCore/SQLiteTempTable.h @@ -0,0 +1,34 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "SQLiteWrapper.h" + + +namespace AppInstaller::Repository::SQLite +{ + // The base for a class that represents a temp table. + struct TempTable + { + TempTable(); + + ~TempTable(); + + TempTable(const TempTable&) = delete; + TempTable& operator=(const TempTable&) = delete; + + TempTable(TempTable&&) = default; + TempTable& operator=(TempTable&&) = default; + + protected: + // Gets the qualified name of the temp table. + const std::string& GetQualifiedName() const { return m_name; } + + // Prepares the drop table statement for use in destructor. + // It needs to be run by the derived class after the table is actually created. + void InitDropStatement(Connection& connection); + + private: + std::string m_name; + Statement m_dropTableStatement; + }; +} diff --git a/src/AppInstallerRepositoryCore/SQLiteWrapper.cpp b/src/AppInstallerRepositoryCore/SQLiteWrapper.cpp @@ -77,6 +77,16 @@ namespace AppInstaller::Repository::SQLite { return sqlite3_column_int64(stmt, column); } + + void ParameterSpecificsImpl<bool>::Bind(sqlite3_stmt* stmt, int index, bool v) + { + THROW_IF_SQLITE_FAILED(sqlite3_bind_int(stmt, index, (v ? 1 : 0))); + } + + bool ParameterSpecificsImpl<bool>::GetColumn(sqlite3_stmt* stmt, int column) + { + return (sqlite3_column_int(stmt, column) != 0); + } } Connection::Connection(const std::string& target, OpenDisposition disposition, OpenFlags flags) @@ -221,4 +231,27 @@ namespace AppInstaller::Repository::SQLite m_inProgress = false; } } + + std::string_view EscapeCharForLike = "'"sv; + + std::string EscapeStringForLike(std::string_view value) + { + constexpr char singleChar = '_'; + constexpr char multiChar = '%'; + char escapeChar = EscapeCharForLike[0]; + + std::string result; + result.reserve(value.length()); + + for (char c : value) + { + if (c == singleChar || c == multiChar || c == escapeChar) + { + result.append(1, escapeChar); + } + result.append(1, c); + } + + return result; + } } diff --git a/src/AppInstallerRepositoryCore/SQLiteWrapper.h b/src/AppInstallerRepositoryCore/SQLiteWrapper.h @@ -24,7 +24,7 @@ namespace AppInstaller::Repository::SQLite namespace details { - template <typename T> + template <typename T, typename = void> struct ParameterSpecificsImpl { static void Bind(sqlite3_stmt*, int, T&&) @@ -70,6 +70,26 @@ namespace AppInstaller::Repository::SQLite static int64_t GetColumn(sqlite3_stmt* stmt, int column); }; + template <> + struct ParameterSpecificsImpl<bool> + { + static void Bind(sqlite3_stmt* stmt, int index, bool v); + static bool GetColumn(sqlite3_stmt* stmt, int column); + }; + + template <typename E> + struct ParameterSpecificsImpl<E, typename std::enable_if_t<std::is_enum_v<E>>> + { + static void Bind(sqlite3_stmt* stmt, int index, E v) + { + ParameterSpecificsImpl<std::underlying_type_t<E>>::Bind(stmt, index, ToIntegral(v)); + } + static E GetColumn(sqlite3_stmt* stmt, int column) + { + return ToEnum<E>(ParameterSpecificsImpl<std::underlying_type_t<E>>::GetColumn(stmt, column)); + } + }; + template <typename T> using ParameterSpecifics = ParameterSpecificsImpl<std::decay_t<T>>; } @@ -200,6 +220,9 @@ namespace AppInstaller::Repository::SQLite // Note that this does not clear data bindings. void Reset(); + // Determines if the statement owns an underlying object. + operator bool() const { return static_cast<bool>(m_stmt); } + private: Statement(Connection& connection, std::string_view sql, bool persistent); @@ -248,4 +271,10 @@ namespace AppInstaller::Repository::SQLite Statement m_rollbackTo; Statement m_release; }; + + // The escape character used in the EscapeStringForLike function. + extern std::string_view EscapeCharForLike; + + // Escapes the given input string for passing to a like operation. + std::string EscapeStringForLike(std::string_view value); }