commit 412f14412e1d1c4ea1a4f5fa7169af8e72e512b4 parent 54a3eb82482a106f50906b5af35bf7f3efb6672c Author: JohnMcPMS <johnmcp@microsoft.com> Date: Tue, 15 Sep 2020 14:14:24 -0700 Create DB 1.1 schema for system reference strings (#564) The primary motivation of this change was to add new fields for use in the list and upgrade commands; system reference strings that can be used to associate a winget package with software currently installed on a machine. This required creation of schema 1.1, as well as some refactoring to make inheritance and behavior changes easier between versions. The two types of strings added are PackageFamilyName for MSIX packages, and ProductCode for packages that register through ARP (Add/Remove Programs). These are added with the option of having multiple of each in the event that a package's installers have unique values. The values are stored with their casing folded to allow for ordinal comparisons, a performance requirement due to future work on list and upgrade. Additional noteworthy changes: 1. Changed from implicit indices to explicit ones in 1.1, and drop more of them during preparation for packaging. This should result in a ~35% reduction in page count in the database, with no impact to performance. 2. Added a dev debug tool in SQLiteWrapper.cpp; set WINGET_SQLITE_EXPLAIN_QUERY_PLAN_ENABLED to 1 to have the query plan for every SQLite prepared statement output to logging. Diffstat:
37 files changed, 2041 insertions(+), 871 deletions(-)
diff --git a/src/AppInstallerCLITests/SQLiteIndex.cpp b/src/AppInstallerCLITests/SQLiteIndex.cpp @@ -25,9 +25,42 @@ using namespace AppInstaller::Repository::Microsoft; using namespace AppInstaller::Repository::SQLite; using namespace AppInstaller::Utility; -SQLiteIndex SimpleTestSetup(const std::string& filePath, Manifest& manifest, std::string& relativePath) +SQLiteIndex CreateTestIndex(const std::string& filePath, std::optional<Schema::Version> version = {}) { - SQLiteIndex index = SQLiteIndex::CreateNew(filePath, Schema::Version::Latest()); + // If no specific version requested, then use generator to run against all versions. + if (!version) + { + version = GENERATE(Schema::Version{ 1, 0 }, Schema::Version::Latest()); + } + + return SQLiteIndex::CreateNew(filePath, version.value()); +} + +Schema::Version TestPrepareForRead(SQLiteIndex& index) +{ + // This will only be called for tests that want to support cross version checks. + // Based on the version of the incoming, we only want to generate versions less or equal to it. + if (index.GetVersion() == Schema::Version{ 1, 0 }) + { + // Nothing to do here + } + else if (index.GetVersion() == Schema::Version{ 1, 1 }) + { + auto changeVersion = GENERATE(false, true); + + if (changeVersion) + { + index.ForceVersion(Schema::Version{ 1, 0 }); + return { 1, 0 }; + } + } + + return index.GetVersion(); +} + +SQLiteIndex SimpleTestSetup(const std::string& filePath, Manifest& manifest, std::string& relativePath, std::optional<Schema::Version> version = {}) +{ + SQLiteIndex index = CreateTestIndex(filePath, version); manifest.Id = "Test.Id"; manifest.Name = "Test Name"; @@ -46,6 +79,50 @@ SQLiteIndex SimpleTestSetup(const std::string& filePath, Manifest& manifest, std struct IndexFields { + IndexFields( + std::string id, + std::string name, + std::string moniker, + std::string version, + std::string channel, + std::vector<NormalizedString> tags, + std::vector<NormalizedString> commands, + std::string path + ) : + Id(std::move(id)), + Name(std::move(name)), + Moniker(std::move(moniker)), + Version(std::move(version)), + Channel(std::move(channel)), + Tags(std::move(tags)), + Commands(std::move(commands)), + Path(std::move(path)) + {} + + IndexFields( + std::string id, + std::string name, + std::string moniker, + std::string version, + std::string channel, + std::vector<NormalizedString> tags, + std::vector<NormalizedString> commands, + std::string path, + std::vector<NormalizedString> packageFamilyNames, + std::vector<NormalizedString> productCodes + ) : + Id(std::move(id)), + Name(std::move(name)), + Moniker(std::move(moniker)), + Version(std::move(version)), + Channel(std::move(channel)), + Tags(std::move(tags)), + Commands(std::move(commands)), + Path(std::move(path)), + PackageFamilyNames(std::move(packageFamilyNames)), + ProductCodes(std::move(productCodes)) + {} + std::string Id; std::string Name; std::string Moniker; @@ -54,11 +131,13 @@ struct IndexFields std::vector<NormalizedString> Tags; std::vector<NormalizedString> Commands; std::string Path; + std::vector<NormalizedString> PackageFamilyNames; + std::vector<NormalizedString> ProductCodes; }; -SQLiteIndex SearchTestSetup(const std::string& filePath, std::initializer_list<IndexFields> data = {}, Schema::Version version = Schema::Version::Latest()) +SQLiteIndex SearchTestSetup(const std::string& filePath, std::initializer_list<IndexFields> data = {}, std::optional<Schema::Version> version = {}) { - SQLiteIndex index = SQLiteIndex::CreateNew(filePath, version); + SQLiteIndex index = CreateTestIndex(filePath, version); Manifest manifest; @@ -71,6 +150,18 @@ SQLiteIndex SearchTestSetup(const std::string& filePath, std::initializer_list<I manifest.Tags = d.Tags; manifest.Commands = d.Commands; + manifest.Installers.resize(std::max(d.PackageFamilyNames.size(), d.ProductCodes.size())); + + for (size_t i = 0; i < d.PackageFamilyNames.size(); ++i) + { + manifest.Installers[i].PackageFamilyName = d.PackageFamilyNames[i]; + } + + for (size_t i = 0; i < d.ProductCodes.size(); ++i) + { + manifest.Installers[i].ProductCode = d.ProductCodes[i]; + } + index.AddManifest(manifest, d.Path); }; @@ -82,6 +173,12 @@ SQLiteIndex SearchTestSetup(const std::string& filePath, std::initializer_list<I return index; } +bool ArePackageFamilyNameAndProductCodeSupported(const SQLiteIndex& index, const Schema::Version& testVersion) +{ + UNSCOPED_INFO("Index " << index.GetVersion() << " | Test " << testVersion); + return (index.GetVersion() >= Schema::Version{ 1, 1 } && testVersion >= Schema::Version{ 1, 1 }); +} + TEST_CASE("SQLiteIndexCreateLatestAndReopen", "[sqliteindex]") { TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; @@ -136,7 +233,7 @@ TEST_CASE("SQLiteIndexCreateAndAddManifestFile", "[sqliteindex]") TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; INFO("Using temporary file named: " << tempFile.GetPath()); - SQLiteIndex index = SQLiteIndex::CreateNew(tempFile, Schema::Version::Latest()); + SQLiteIndex index = CreateTestIndex(tempFile); TestDataFile manifestFile{ "Manifest-Good.yaml" }; std::filesystem::path manifestPath{ "microsoft/msixsdk/microsoft.msixsdk-1.7.32.yaml" }; @@ -168,7 +265,7 @@ TEST_CASE("SQLiteIndexCreateAndAddManifestDuplicate", "[sqliteindex]") TEST_CASE("SQLiteIndex_RemoveManifestFile_NotPresent", "[sqliteindex]") { - SQLiteIndex index = SQLiteIndex::CreateNew(SQLITE_MEMORY_DB_CONNECTION_TARGET, Schema::Version::Latest()); + SQLiteIndex index = CreateTestIndex(SQLITE_MEMORY_DB_CONNECTION_TARGET); TestDataFile manifestFile{ "Manifest-Good.yaml" }; std::filesystem::path manifestPath{ "microsoft/msixsdk/microsoft.msixsdk-1.7.32.yaml" }; @@ -687,7 +784,7 @@ TEST_CASE("SQLiteIndex_PrepareForPackaging", "[sqliteindex]") TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; INFO("Using temporary file named: " << tempFile.GetPath()); - SQLiteIndex index = SQLiteIndex::CreateNew(tempFile, Schema::Version::Latest()); + SQLiteIndex index = CreateTestIndex(tempFile); TestDataFile manifestFile{ "Manifest-Good.yaml" }; std::filesystem::path manifestPath{ "microsoft/msixsdk/microsoft.msixsdk-1.7.32.yaml" }; @@ -706,6 +803,8 @@ TEST_CASE("SQLiteIndex_Search_IdExactMatch", "[sqliteindex]") std::string relativePath; SQLiteIndex index = SimpleTestSetup(tempFile, manifest, relativePath); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, manifest.Id); @@ -728,6 +827,8 @@ TEST_CASE("SQLiteIndex_Search_MultipleMatch", "[sqliteindex]") manifest.Version = "2.0.0"; index.AddManifest(manifest, relativePath + "2"); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, manifest.Id); @@ -747,6 +848,8 @@ TEST_CASE("SQLiteIndex_Search_NoMatch", "[sqliteindex]") std::string relativePath; SQLiteIndex index = SimpleTestSetup(tempFile, manifest, relativePath); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, "THIS DOES NOT MATCH ANYTHING!"); @@ -763,6 +866,8 @@ TEST_CASE("SQLiteIndex_IdString", "[sqliteindex]") std::string relativePath; SQLiteIndex index = SimpleTestSetup(tempFile, manifest, relativePath); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, manifest.Id); @@ -783,6 +888,8 @@ TEST_CASE("SQLiteIndex_NameString", "[sqliteindex]") std::string relativePath; SQLiteIndex index = SimpleTestSetup(tempFile, manifest, relativePath); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, manifest.Id); @@ -803,6 +910,8 @@ TEST_CASE("SQLiteIndex_PathString", "[sqliteindex]") std::string relativePath; SQLiteIndex index = SimpleTestSetup(tempFile, manifest, relativePath); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, manifest.Id); @@ -827,6 +936,8 @@ TEST_CASE("SQLiteIndex_Versions", "[sqliteindex]") std::string relativePath; SQLiteIndex index = SimpleTestSetup(tempFile, manifest, relativePath); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, manifest.Id); @@ -867,6 +978,8 @@ TEST_CASE("SQLiteIndex_Search_VersionSorting", "[sqliteindex]") { "Id", "Name", "Moniker", "13.0.0", "", { "foo" }, { "com3" }, "Path8" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Filters.emplace_back(ApplicationMatchField::Id, MatchType::Exact, "Id"); @@ -915,6 +1028,8 @@ TEST_CASE("SQLiteIndex_PathString_VersionSorting", "[sqliteindex]") { "Id", "Name", "Moniker", "13.0.0", "", { "foo" }, { "com3" }, "Path8" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Filters.emplace_back(ApplicationMatchField::Id, MatchType::Exact, "Id"); @@ -953,6 +1068,8 @@ TEST_CASE("SQLiteIndex_PathString_CaseInsensitive", "[sqliteindex]") { "Id", "Name", "Moniker", "13.0.0", "", { "foo" }, { "com3" }, "Path8" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Filters.emplace_back(ApplicationMatchField::Id, MatchType::Exact, "Id"); @@ -977,7 +1094,7 @@ TEST_CASE("SQLiteIndex_SearchResultsTableSearches", "[sqliteindex][V1_0]") Manifest manifest; std::string relativePath; { - (void)SimpleTestSetup(tempFile, manifest, relativePath); + (void)SimpleTestSetup(tempFile, manifest, relativePath, Schema::Version{ 1, 0 }); } Connection connection = Connection::Create(tempFile, Connection::OpenDisposition::ReadOnly); @@ -1007,6 +1124,8 @@ TEST_CASE("SQLiteIndex_Search_EmptySearch", "[sqliteindex]") { "Id3", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path4" }, }); + TestPrepareForRead(index); + SearchRequest request; auto results = index.Search(request); @@ -1023,6 +1142,8 @@ TEST_CASE("SQLiteIndex_Search_Exact", "[sqliteindex]") { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Exact, "Id"); @@ -1040,6 +1161,8 @@ TEST_CASE("SQLiteIndex_Search_Substring", "[sqliteindex]") { "Id2", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Substring, "Id"); @@ -1057,6 +1180,8 @@ TEST_CASE("SQLiteIndex_Search_ExactBeforeSubstring", "[sqliteindex]") { "Id", "Name", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path2" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Substring, "Id"); @@ -1078,6 +1203,8 @@ TEST_CASE("SQLiteIndex_Search_SingleFilter", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Filters.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "a"); @@ -1105,6 +1232,8 @@ TEST_CASE("SQLiteIndex_Search_Multimatch", "[sqliteindex]") { "Id3", "Name", "Moniker", "Version3", "", { "Tag" }, { "Command" }, "Path7" }, }); + TestPrepareForRead(index); + SearchRequest request; // An empty string should match all substrings request.Query = RequestMatch(MatchType::Substring, ""); @@ -1124,6 +1253,8 @@ TEST_CASE("SQLiteIndex_Search_QueryAndFilter", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Substring, "Id"); request.Filters.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "Na"); @@ -1151,6 +1282,8 @@ TEST_CASE("SQLiteIndex_Search_QueryAndMultipleFilters", "[sqliteindex]") { "Id3", "Tagit", "new", "Version3", "", { "foo" }, { "com3" }, "Path7" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Substring, "tag"); request.Filters.emplace_back(ApplicationMatchField::Command, MatchType::Exact, "com3"); @@ -1176,6 +1309,8 @@ TEST_CASE("SQLiteIndex_Search_SimpleICULike", "[sqliteindex]") { u8"AwesomeApp", "Nope", "Moniker", "Version", "Channel", { "foot" }, { "com34" }, "Path2" }, }); + TestPrepareForRead(index); + SearchRequest request; // Search for anything containing: [lower] a + umlaut request.Filters.emplace_back(ApplicationMatchField::Id, MatchType::Substring, u8"\xE4"); @@ -1199,6 +1334,8 @@ TEST_CASE("SQLiteIndex_Search_MaximumResults_Equal", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.MaximumResults = 3; @@ -1218,6 +1355,8 @@ TEST_CASE("SQLiteIndex_Search_MaximumResults_Less", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.MaximumResults = 2; @@ -1237,6 +1376,8 @@ TEST_CASE("SQLiteIndex_Search_MaximumResults_Greater", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.MaximumResults = 4; @@ -1256,6 +1397,8 @@ TEST_CASE("SQLiteIndex_Search_QueryAndInclusion", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::CaseInsensitive, "id3"); request.Inclusions.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "Na"); @@ -1275,6 +1418,8 @@ TEST_CASE("SQLiteIndex_Search_InclusionOnly", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Inclusions.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "Na"); @@ -1293,6 +1438,8 @@ TEST_CASE("SQLiteIndex_Search_InclusionAndFilter", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Inclusions.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "Na"); request.Filters.emplace_back(ApplicationMatchField::Name, MatchType::CaseInsensitive, "name"); @@ -1316,6 +1463,8 @@ TEST_CASE("SQLiteIndex_Search_QueryInclusionAndFilter", "[sqliteindex]") { "Id3", "No", "moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::Substring, "id3"); request.Inclusions.emplace_back(ApplicationMatchField::Name, MatchType::Substring, "na"); @@ -1336,6 +1485,8 @@ TEST_CASE("SQLiteIndex_Search_CaseInsensitive", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Query = RequestMatch(MatchType::CaseInsensitive, "id3"); @@ -1354,9 +1505,219 @@ TEST_CASE("SQLiteIndex_Search_StartsWith", "[sqliteindex]") { "Id3", "No", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3" }, }); + TestPrepareForRead(index); + SearchRequest request; request.Inclusions.push_back(ApplicationMatchFilter(ApplicationMatchField::Id, MatchType::StartsWith, "id")); auto results = index.Search(request); REQUIRE(results.Matches.size() == 2); } + +TEST_CASE("SQLiteIndex_Search_Query_PackageFamilyNameSubstring", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Substring, "PFN"); + + auto results = index.Search(request); + REQUIRE(results.Matches.size() == 0); +} + +TEST_CASE("SQLiteIndex_Search_Query_ProductCodeSubstring", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Substring, "PC"); + + auto results = index.Search(request); + REQUIRE(results.Matches.size() == 0); +} + +TEST_CASE("SQLiteIndex_Search_Query_PackageFamilyNameMatch", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Substring, "pfn1"); + + auto results = index.Search(request); + + if (ArePackageFamilyNameAndProductCodeSupported(index, testVersion)) + { + REQUIRE(results.Matches.size() == 1); + } + else + { + REQUIRE(results.Matches.size() == 0); + } +} + +TEST_CASE("SQLiteIndex_Search_Query_ProductCodeMatch", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Query = RequestMatch(MatchType::Substring, "pc2"); + + auto results = index.Search(request); + + if (ArePackageFamilyNameAndProductCodeSupported(index, testVersion)) + { + REQUIRE(results.Matches.size() == 1); + } + else + { + REQUIRE(results.Matches.size() == 0); + } +} + +TEST_CASE("SQLiteIndex_Search_PackageFamilyNameSubstring", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Inclusions.emplace_back(ApplicationMatchField::PackageFamilyName, MatchType::Substring, "PFN"); + + auto results = index.Search(request); + + if (ArePackageFamilyNameAndProductCodeSupported(index, testVersion)) + { + REQUIRE(results.Matches.size() == 3); + } + else + { + REQUIRE(results.Matches.size() == 0); + } +} + +TEST_CASE("SQLiteIndex_Search_ProductCodeSubstring", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Inclusions.emplace_back(ApplicationMatchField::ProductCode, MatchType::Substring, "PC"); + + auto results = index.Search(request); + + if (ArePackageFamilyNameAndProductCodeSupported(index, testVersion)) + { + REQUIRE(results.Matches.size() == 3); + } + else + { + REQUIRE(results.Matches.size() == 0); + } +} + +TEST_CASE("SQLiteIndex_Search_PackageFamilyNameMatch", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Inclusions.emplace_back(ApplicationMatchField::PackageFamilyName, MatchType::Exact, "pfn1"); + + auto results = index.Search(request); + + if (ArePackageFamilyNameAndProductCodeSupported(index, testVersion)) + { + REQUIRE(results.Matches.size() == 1); + } + else + { + REQUIRE(results.Matches.size() == 0); + } +} + +TEST_CASE("SQLiteIndex_Search_ProductCodeMatch", "[sqliteindex]") +{ + TempFile tempFile{ "repolibtest_tempdb"s, ".db"s }; + INFO("Using temporary file named: " << tempFile.GetPath()); + + SQLiteIndex index = SearchTestSetup(tempFile, { + { "Id1", "Name1", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path1", { "PFN1" }, { "PC1" } }, + { "Id2", "Name2", "Moniker", "Version", "Channel", { "ID3" }, { "Command" }, "Path2", { "PFN2" }, { "PC2" } }, + { "Id3", "Name3", "Moniker", "Version", "Channel", { "Tag" }, { "Command" }, "Path3", { "PFN3" }, { "PC3" } }, + }); + + Schema::Version testVersion = TestPrepareForRead(index); + + SearchRequest request; + request.Inclusions.emplace_back(ApplicationMatchField::ProductCode, MatchType::Exact, "pc2"); + + auto results = index.Search(request); + + if (ArePackageFamilyNameAndProductCodeSupported(index, testVersion)) + { + REQUIRE(results.Matches.size() == 1); + } + else + { + REQUIRE(results.Matches.size() == 0); + } +} diff --git a/src/AppInstallerCLITests/Strings.cpp b/src/AppInstallerCLITests/Strings.cpp @@ -4,6 +4,7 @@ #include "TestCommon.h" #include <AppInstallerStrings.h> +using namespace std::string_view_literals; using namespace AppInstaller::Utility; @@ -124,3 +125,11 @@ TEST_CASE("CaseInsensitiveStartsWith", "[strings]") REQUIRE(!CaseInsensitiveStartsWith("withstarts", "starts")); REQUIRE(!CaseInsensitiveStartsWith(" starts", "starts")); } + +TEST_CASE("FoldCase", "[strings]") +{ + REQUIRE(FoldCase(""sv) == FoldCase(""sv)); + REQUIRE(FoldCase("foldcase"sv) == FoldCase("FOLDCASE"sv)); + REQUIRE(FoldCase(u8"f\xF6ldcase"sv) == FoldCase(u8"F\xD6LDCASE"sv)); + REQUIRE(FoldCase(u8"foldc\x430se"sv) == FoldCase(u8"FOLDC\x410SE"sv)); +} diff --git a/src/AppInstallerCommonCore/AppInstallerStrings.cpp b/src/AppInstallerCommonCore/AppInstallerStrings.cpp @@ -295,6 +295,55 @@ namespace AppInstaller::Utility return result; } + std::string FoldCase(std::string_view input) + { + if (input.empty()) + { + return {}; + } + + wil::unique_any<UCaseMap*, decltype(ucasemap_close), &ucasemap_close> caseMap; + UErrorCode errorCode = UErrorCode::U_ZERO_ERROR; + caseMap.reset(ucasemap_open(nullptr, U_FOLD_CASE_DEFAULT, &errorCode)); + + if (U_FAILURE(errorCode)) + { + AICLI_LOG(Core, Error, << "ucasemap_open returned " << errorCode); + THROW_HR(E_UNEXPECTED); + } + + int32_t cch = ucasemap_utf8FoldCase(caseMap.get(), nullptr, 0, input.data(), static_cast<int32_t>(input.size()), &errorCode); + if (errorCode != U_BUFFER_OVERFLOW_ERROR) + { + AICLI_LOG(Core, Error, << "ucasemap_utf8FoldCase returned " << errorCode); + THROW_HR(E_UNEXPECTED); + } + + errorCode = UErrorCode::U_ZERO_ERROR; + + std::string result(cch, '\0'); + cch = ucasemap_utf8FoldCase(caseMap.get(), &result[0], cch, input.data(), static_cast<int32_t>(input.size()), &errorCode); + if (U_FAILURE(errorCode)) + { + AICLI_LOG(Core, Error, << "ucasemap_utf8FoldCase returned " << errorCode); + THROW_HR(E_UNEXPECTED); + } + + while (result.back() == '\0') + { + result.pop_back(); + } + + return result; + } + + NormalizedString FoldCase(const NormalizedString& input) + { + NormalizedString result; + result.assign(FoldCase(static_cast<std::string_view>(input))); + return result; + } + bool IsEmptyOrWhitespace(std::wstring_view str) { if (str.empty()) diff --git a/src/AppInstallerCommonCore/Public/AppInstallerStrings.h b/src/AppInstallerCommonCore/Public/AppInstallerStrings.h @@ -96,6 +96,14 @@ namespace AppInstaller::Utility // Get the lower case version of the given std::wstring std::wstring ToLower(std::wstring_view in); + // Folds the case of the given std::string + // See https://unicode-org.github.io/icu/userguide/transforms/casemappings.html#case-folding + std::string FoldCase(std::string_view input); + + // Folds the case of the given NormalizedString, returning it as also Normalized + // See https://unicode-org.github.io/icu/userguide/transforms/casemappings.html#case-folding + NormalizedString FoldCase(const NormalizedString& input); + // Checks if the input string is empty or whitespace bool IsEmptyOrWhitespace(std::wstring_view str); @@ -106,9 +114,6 @@ namespace AppInstaller::Utility // Removes whitespace from the beginning and end of the string. std::string& Trim(std::string& str); - // Gets the number of bytes remaining in the stream. - std::streamsize GetRemainingByteCount(std::istream& stream); - // Reads the entire stream into a string. std::string ReadEntireStream(std::istream& stream); } diff --git a/src/AppInstallerCommonCore/Public/winget/ManifestInstaller.h b/src/AppInstallerCommonCore/Public/winget/ManifestInstaller.h @@ -66,6 +66,12 @@ namespace AppInstaller::Manifest // Store Product Id string_t ProductId; + // Package family name for MSIX packaged installers. + string_t PackageFamilyName; + + // Product code for ARP (Add/Remove Programs) installers. + string_t ProductCode; + // If present, has more precedence than root InstallerTypeEnum InstallerType; diff --git a/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj b/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj @@ -188,6 +188,10 @@ <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\1_1\Interface.h" /> + <ClInclude Include="Microsoft\Schema\1_1\PackageFamilyNameTable.h" /> + <ClInclude Include="Microsoft\Schema\1_1\ProductCodeTable.h" /> + <ClInclude Include="Microsoft\Schema\1_1\SearchResultsTable.h" /> <ClInclude Include="Microsoft\Schema\ISQLiteIndex.h" /> <ClInclude Include="Microsoft\Schema\MetadataTable.h" /> <ClInclude Include="Microsoft\Schema\Version.h" /> @@ -214,12 +218,14 @@ <PrecompiledHeader Condition="'$(Configuration)|$(Platform)'=='Release|x64'">NotUsing</PrecompiledHeader> </ClCompile> <ClCompile Include="Microsoft\PreIndexedPackageSourceFactory.cpp" /> - <ClCompile Include="Microsoft\Schema\1_0\Interface.cpp" /> + <ClCompile Include="Microsoft\Schema\1_0\Interface_1_0.cpp" /> <ClCompile Include="Microsoft\Schema\1_0\ManifestTable.cpp" /> <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\1_0\SearchResultsTable_1_0.cpp" /> + <ClCompile Include="Microsoft\Schema\1_1\Interface_1_1.cpp" /> + <ClCompile Include="Microsoft\Schema\1_1\SearchResultsTable_1_1.cpp" /> <ClCompile Include="Microsoft\Schema\MetadataTable.cpp" /> <ClCompile Include="Microsoft\Schema\Version.cpp" /> <ClCompile Include="Microsoft\SQLiteIndex.cpp" /> diff --git a/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj.filters b/src/AppInstallerRepositoryCore/AppInstallerRepositoryCore.vcxproj.filters @@ -28,6 +28,9 @@ <Filter Include="ICU"> <UniqueIdentifier>{dac1a359-45ac-4456-8a83-f7df10058197}</UniqueIdentifier> </Filter> + <Filter Include="Microsoft\Schema\1_1"> + <UniqueIdentifier>{aef8989c-44fd-4848-ae2c-c81d3f2e2c72}</UniqueIdentifier> + </Filter> </ItemGroup> <ItemGroup> <ClInclude Include="pch.h"> @@ -114,6 +117,18 @@ <ClInclude Include="AggregatedSource.h"> <Filter>Header Files</Filter> </ClInclude> + <ClInclude Include="Microsoft\Schema\1_1\Interface.h"> + <Filter>Microsoft\Schema\1_1</Filter> + </ClInclude> + <ClInclude Include="Microsoft\Schema\1_1\PackageFamilyNameTable.h"> + <Filter>Microsoft\Schema\1_1</Filter> + </ClInclude> + <ClInclude Include="Microsoft\Schema\1_1\ProductCodeTable.h"> + <Filter>Microsoft\Schema\1_1</Filter> + </ClInclude> + <ClInclude Include="Microsoft\Schema\1_1\SearchResultsTable.h"> + <Filter>Microsoft\Schema\1_1</Filter> + </ClInclude> </ItemGroup> <ItemGroup> <ClCompile Include="pch.cpp"> @@ -131,9 +146,6 @@ <ClCompile Include="Microsoft\Schema\Version.cpp"> <Filter>Microsoft\Schema</Filter> </ClCompile> - <ClCompile Include="Microsoft\Schema\1_0\Interface.cpp"> - <Filter>Microsoft\Schema\1_0</Filter> - </ClCompile> <ClCompile Include="Microsoft\Schema\1_0\OneToOneTable.cpp"> <Filter>Microsoft\Schema\1_0</Filter> </ClCompile> @@ -161,15 +173,24 @@ <ClCompile Include="SQLiteTempTable.cpp"> <Filter>Source Files</Filter> </ClCompile> - <ClCompile Include="Microsoft\Schema\1_0\SearchResultsTable.cpp"> - <Filter>Microsoft\Schema\1_0</Filter> - </ClCompile> <ClCompile Include="ICU\SQLiteICU.c"> <Filter>ICU</Filter> </ClCompile> <ClCompile Include="AggregatedSource.cpp"> <Filter>Source Files</Filter> </ClCompile> + <ClCompile Include="Microsoft\Schema\1_0\Interface_1_0.cpp"> + <Filter>Microsoft\Schema\1_0</Filter> + </ClCompile> + <ClCompile Include="Microsoft\Schema\1_1\Interface_1_1.cpp"> + <Filter>Microsoft\Schema\1_1</Filter> + </ClCompile> + <ClCompile Include="Microsoft\Schema\1_0\SearchResultsTable_1_0.cpp"> + <Filter>Microsoft\Schema\1_0</Filter> + </ClCompile> + <ClCompile Include="Microsoft\Schema\1_1\SearchResultsTable_1_1.cpp"> + <Filter>Microsoft\Schema\1_1</Filter> + </ClCompile> </ItemGroup> <ItemGroup> <None Include="PropertySheet.props" /> diff --git a/src/AppInstallerRepositoryCore/Microsoft/SQLiteIndex.cpp b/src/AppInstallerRepositoryCore/Microsoft/SQLiteIndex.cpp @@ -124,6 +124,13 @@ namespace AppInstaller::Repository::Microsoft m_version = m_interface->GetVersion(); } +#ifndef AICLI_DISABLE_TEST_HOOKS + void SQLiteIndex::ForceVersion(const Schema::Version& version) + { + m_interface = version.CreateISQLiteIndex(); + } +#endif + void SQLiteIndex::AddManifest(const std::filesystem::path& manifestPath, const std::filesystem::path& relativePath) { AICLI_LOG(Repo, Info, << "Adding manifest from file [" << manifestPath << "]"); @@ -159,7 +166,7 @@ namespace AppInstaller::Repository::Microsoft SQLite::Savepoint savepoint = SQLite::Savepoint::Create(m_dbconn, "sqliteindex_updatemanifest"); - bool result = m_interface->UpdateManifest(m_dbconn, manifest, relativePath); + bool result = m_interface->UpdateManifest(m_dbconn, manifest, relativePath).first; if (result) { diff --git a/src/AppInstallerRepositoryCore/Microsoft/SQLiteIndex.h b/src/AppInstallerRepositoryCore/Microsoft/SQLiteIndex.h @@ -51,6 +51,12 @@ namespace AppInstaller::Repository::Microsoft // Gets the schema version of the index. Schema::Version GetVersion() const { return m_version; } +#ifndef AICLI_DISABLE_TEST_HOOKS + // Changes the version of the interface being used to operate on the database. + // Should only be used for testing. + void ForceVersion(const Schema::Version& version); +#endif + // Gets the last write time for the index. std::chrono::system_clock::time_point GetLastWriteTime(); diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface.cpp @@ -1,513 +0,0 @@ -// Copyright (c) Microsoft Corporation. -// Licensed under the MIT License. -#include "pch.h" -#include "Microsoft/Schema/1_0/Interface.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/VersionTable.h" -#include "Microsoft/Schema/1_0/ChannelTable.h" - -#include "Microsoft/Schema/1_0/PathPartTable.h" - -#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" - - -namespace AppInstaller::Repository::Microsoft::Schema::V1_0 -{ - namespace - { - // Gets an existing manifest by its rowid., if it exists. - std::optional<SQLite::rowid_t> GetExistingManifestId(SQLite::Connection& connection, const Manifest::Manifest& manifest) - { - std::optional<SQLite::rowid_t> idId = IdTable::SelectIdByValue(connection, manifest.Id, true); - if (!idId) - { - AICLI_LOG(Repo, Info, << "Did not find an Id { " << manifest.Id << " }"); - return {}; - } - - std::optional<SQLite::rowid_t> versionId = VersionTable::SelectIdByValue(connection, manifest.Version, true); - if (!versionId) - { - AICLI_LOG(Repo, Info, << "Did not find a Version { " << manifest.Version << " }"); - return {}; - } - - std::optional<SQLite::rowid_t> channelId = ChannelTable::SelectIdByValue(connection, manifest.Channel, true); - if (!channelId) - { - AICLI_LOG(Repo, Info, << "Did not find a Channel { " << manifest.Channel << " }"); - return {}; - } - - auto result = ManifestTable::SelectByValueIds<IdTable, VersionTable, ChannelTable>(connection, { idId.value(), versionId.value(), channelId.value() }); - - if (!result) - { - AICLI_LOG(Repo, Info, << "Did not find a manifest row for { " << manifest.Id << ", " << manifest.Version << ", " << manifest.Channel << " }"); - } - - return result; - } - - // Gets a manifest id by the given key values. - std::optional<SQLite::rowid_t> GetManifestIdByKey(SQLite::Connection& connection, SQLite::rowid_t id, std::string_view version = "", std::string_view channel = "") - { - std::optional<SQLite::rowid_t> channelIdOpt = ChannelTable::SelectIdByValue(connection, channel, true); - if (!channelIdOpt && !channel.empty()) - { - // If an empty channel was given but none was found, we will just not filter on channel. - AICLI_LOG(Repo, Info, << "Did not find a Channel { " << channel << " }"); - return {}; - } - - std::optional<SQLite::rowid_t> versionIdOpt; - - if (version.empty()) - { - std::vector<std::string> versionStrings; - - if (channelIdOpt) - { - versionStrings = ManifestTable::GetAllValuesByIds<VersionTable, IdTable, ChannelTable>(connection, { id, channelIdOpt.value() }); - } - else - { - versionStrings = ManifestTable::GetAllValuesByIds<VersionTable, IdTable>(connection, { id }); - } - - if (versionStrings.empty()) - { - AICLI_LOG(Repo, Info, << "Did not find any Versions { " << id << ", " << channel << " }"); - return {}; - } - - // Convert the strings to Versions and sort them - std::vector<Utility::Version> versions; - for (std::string& v : versionStrings) - { - versions.emplace_back(std::move(v)); - } - - std::sort(versions.begin(), versions.end()); - - // Get the last version in the list (the highest version) and its rowid - const std::string& latestVersion = versions.back().ToString(); - versionIdOpt = VersionTable::SelectIdByValue(connection, latestVersion); - } - else - { - versionIdOpt = VersionTable::SelectIdByValue(connection, version, true); - } - - if (!versionIdOpt) - { - AICLI_LOG(Repo, Info, << "Did not find a Version { " << version << " }"); - return {}; - } - - if (channelIdOpt) - { - return ManifestTable::SelectByValueIds<IdTable, VersionTable, ChannelTable>(connection, { id, versionIdOpt.value(), channelIdOpt.value() }); - } - else - { - return ManifestTable::SelectByValueIds<IdTable, VersionTable>(connection, { id, versionIdOpt.value() }); - } - } - - // Updates the manifest column and related table based on the given value. - template <typename Table> - void UpdateManifestValueById(SQLite::Connection& connection, const typename Table::value_t& value, SQLite::rowid_t manifestId, bool overwriteLikeMatch = false) - { - auto [oldValueId] = ManifestTable::GetIdsById<Table>(connection, manifestId); - - SQLite::rowid_t newValueId = Table::EnsureExists(connection, value, overwriteLikeMatch); - - ManifestTable::UpdateValueIdById<Table>(connection, manifestId, newValueId); - - 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::CaseInsensitive: - return { MatchType::Exact, MatchType::CaseInsensitive }; - case MatchType::StartsWith: - return { MatchType::CaseInsensitive, MatchType::StartsWith }; - case MatchType::Substring: - return { MatchType::Exact, MatchType::CaseInsensitive, MatchType::Substring }; - case MatchType::Wildcard: - return { MatchType::Wildcard }; - case MatchType::Fuzzy: - return { MatchType::Exact, MatchType::CaseInsensitive, MatchType::Fuzzy }; - case MatchType::FuzzySubstring: - return { MatchType::Exact, MatchType::CaseInsensitive, MatchType::Fuzzy, MatchType::Substring, MatchType::FuzzySubstring }; - default: - THROW_HR(E_UNEXPECTED); - } - } - } - - Schema::Version Interface::GetVersion() const - { - return { 1, 0 }; - } - - void Interface::CreateTables(SQLite::Connection& connection) - { - SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createtables_v1_0"); - - IdTable::Create(connection); - NameTable::Create(connection); - MonikerTable::Create(connection); - VersionTable::Create(connection); - ChannelTable::Create(connection); - - PathPartTable::Create(connection); - - ManifestTable::Create(connection, { - { IdTable::ValueName(), true, false }, - { NameTable::ValueName(), false, false }, - { MonikerTable::ValueName(), false, false }, - { VersionTable::ValueName(), true, false }, - { ChannelTable::ValueName(), true, false }, - { PathPartTable::ValueName(), false, true } - }); - - TagsTable::Create(connection); - CommandsTable::Create(connection); - - savepoint.Commit(); - } - - void Interface::AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) - { - auto manifestResult = GetExistingManifestId(connection, manifest); - - // If this manifest is already present, we can't add it. - THROW_HR_IF(HRESULT_FROM_WIN32(ERROR_ALREADY_EXISTS), manifestResult.has_value()); - - SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "addmanifest_v1_0"); - - auto [pathAdded, pathLeafId] = PathPartTable::EnsurePathExists(connection, relativePath, true); - - // If we get false from the function, this manifest path already exists in the index. - THROW_HR_IF(HRESULT_FROM_WIN32(ERROR_ALREADY_EXISTS), !pathAdded); - - // Ensure that all of the 1:1 data exists. - SQLite::rowid_t idId = IdTable::EnsureExists(connection, manifest.Id, true); - SQLite::rowid_t nameId = NameTable::EnsureExists(connection, manifest.Name); - SQLite::rowid_t monikerId = MonikerTable::EnsureExists(connection, manifest.AppMoniker); - SQLite::rowid_t versionId = VersionTable::EnsureExists(connection, manifest.Version); - SQLite::rowid_t channelId = ChannelTable::EnsureExists(connection, manifest.Channel); - - // Insert the manifest entry. - SQLite::rowid_t manifestId = ManifestTable::Insert(connection, { - { IdTable::ValueName(), idId}, - { NameTable::ValueName(), nameId }, - { MonikerTable::ValueName(), monikerId }, - { VersionTable::ValueName(), versionId }, - { ChannelTable::ValueName(), channelId }, - { PathPartTable::ValueName(), pathLeafId } - }); - - // Add all of the 1:N data. - TagsTable::EnsureExistsAndInsert(connection, manifest.Tags, manifestId); - CommandsTable::EnsureExistsAndInsert(connection, manifest.Commands, manifestId); - - savepoint.Commit(); - } - - bool Interface::UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) - { - auto manifestResult = GetExistingManifestId(connection, manifest); - - // If the manifest doesn't actually exist, fail the update. - THROW_HR_IF(E_NOT_SET, !manifestResult); - - SQLite::rowid_t manifestId = manifestResult.value(); - - auto [idInIndex, nameInIndex, monikerInIndex, versionInIndex, channelInIndex] = - ManifestTable::GetValuesById<IdTable, NameTable, MonikerTable, VersionTable, ChannelTable>(connection, manifestId); - - SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "updatemanifest_v1_0"); - bool indexModified = false; - - // Id, Version, and Channel may have changed casing. If so, they too need to be updated. - if (idInIndex != manifest.Id) - { - UpdateManifestValueById<IdTable>(connection, manifest.Id, manifestId, true); - indexModified = true; - } - - if (versionInIndex != manifest.Version) - { - UpdateManifestValueById<VersionTable>(connection, manifest.Version, manifestId); - indexModified = true; - } - - if (channelInIndex != manifest.Channel) - { - UpdateManifestValueById<ChannelTable>(connection, manifest.Channel, manifestId); - indexModified = true; - } - - if (nameInIndex != manifest.Name) - { - UpdateManifestValueById<NameTable>(connection, manifest.Name, manifestId); - indexModified = true; - } - - if (monikerInIndex != manifest.AppMoniker) - { - UpdateManifestValueById<MonikerTable>(connection, manifest.AppMoniker, manifestId); - indexModified = true; - } - - // Update path table if necessary - auto [existingPathLeafId] = ManifestTable::GetIdsById<PathPartTable>(connection, manifestId); - auto [pathAdded, newPathLeafId] = PathPartTable::EnsurePathExists(connection, relativePath, true); - - if (pathAdded) - { - // Path was added, so we need to update the manifest table and delete the old path - ManifestTable::UpdateValueIdById<PathPartTable>(connection, manifestId, newPathLeafId); - PathPartTable::RemovePathById(connection, existingPathLeafId); - indexModified = true; - } - else - { - // The path already existed, so it must either match the existing manifest path or it is an error - THROW_HR_IF(HRESULT_FROM_WIN32(ERROR_ALREADY_EXISTS), existingPathLeafId != newPathLeafId); - } - - // Update all 1:N tables as necessary - indexModified = TagsTable::UpdateIfNeededByManifestId(connection, manifest.Tags, manifestId) || indexModified; - indexModified = CommandsTable::UpdateIfNeededByManifestId(connection, manifest.Commands, manifestId) || indexModified; - - savepoint.Commit(); - - return indexModified; - } - - void Interface::RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path&) - { - auto manifestResult = GetExistingManifestId(connection, manifest); - - // If the manifest doesn't actually exist, fail the remove. - THROW_HR_IF(E_NOT_SET, !manifestResult); - - SQLite::rowid_t manifestId = manifestResult.value(); - - // Get the ids of the values from the manifest table - auto [idId, nameId, monikerId, versionId, channelId, pathLeafId] = - ManifestTable::GetIdsById<IdTable, NameTable, MonikerTable, VersionTable, ChannelTable, PathPartTable>(connection, manifestId); - - SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "removemanifest_v1_0"); - - // Remove the manifest row - ManifestTable::DeleteById(connection, manifestId); - - // Remove all of the 1:1 data that is no longer referenced. - IdTable::DeleteIfNotNeededById(connection, idId); - NameTable::DeleteIfNotNeededById(connection, nameId); - MonikerTable::DeleteIfNotNeededById(connection, monikerId); - VersionTable::DeleteIfNotNeededById(connection, versionId); - ChannelTable::DeleteIfNotNeededById(connection, channelId); - - // Remove the path - PathPartTable::RemovePathById(connection, pathLeafId); - - // Remove all of the 1:N data that is no longer referenced. - TagsTable::DeleteIfNotNeededByManifestId(connection, manifestId); - CommandsTable::DeleteIfNotNeededByManifestId(connection, manifestId); - - savepoint.Commit(); - } - - void Interface::PrepareForPackaging(SQLite::Connection& connection) - { - SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "prepareforpackaging_v1_0"); - - IdTable::PrepareForPackaging(connection); - NameTable::PrepareForPackaging(connection); - MonikerTable::PrepareForPackaging(connection); - VersionTable::PrepareForPackaging(connection); - ChannelTable::PrepareForPackaging(connection); - - PathPartTable::PrepareForPackaging(connection); - - ManifestTable::PrepareForPackaging(connection, { - VersionTable::ValueName(), - ChannelTable::ValueName(), - PathPartTable::ValueName(), - }); - - TagsTable::PrepareForPackaging(connection); - CommandsTable::PrepareForPackaging(connection); - - savepoint.Commit(); - - // Force the database to actually shrink the file size. - // This *must* be done outside of an active transaction. - SQLite::Builder::StatementBuilder builder; - builder.Vacuum(); - builder.Execute(connection); - } - - ISQLiteIndex::SearchResult Interface::Search(SQLite::Connection& connection, const SearchRequest& request) - { - // If an empty request, get everything - if (!request.Query && request.Inclusions.empty() && request.Filters.empty()) - { - std::vector<SQLite::rowid_t> ids = IdTable::GetAllRowIds(connection, request.MaximumResults); - - SearchResult result; - for (SQLite::rowid_t id : ids) - { - result.Matches.emplace_back(std::make_pair(id, ApplicationMatchFilter(ApplicationMatchField::Id, MatchType::Wildcard, {}))); - } - - result.Truncated = (request.MaximumResults && IdTable::GetCount(connection) > request.MaximumResults); - - 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 Inclusions has fields, we add these to the data. - // If neither is defined, we take the first filter and use it as the initial results search. - SearchResultsTable resultsTable(connection); - bool inclusionsAttempted = false; - - if (request.Query) - { - // Perform searches across multiple tables to populate the initial results. - const RequestMatch& query = request.Query.value(); - - for (MatchType match : GetMatchTypeOrder(query.Type)) - { - 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); - } - - inclusionsAttempted = true; - } - - if (!request.Inclusions.empty()) - { - for (const auto& include : request.Inclusions) - { - for (MatchType match : GetMatchTypeOrder(include.Type)) - { - resultsTable.SearchOnField(include.Field, match, include.Value); - } - } - - inclusionsAttempted = true; - } - - size_t filterIndex = 0; - if (!inclusionsAttempted) - { - THROW_HR_IF(E_UNEXPECTED, request.Filters.empty()); - - // Perform search for just the field matching the first filter - const ApplicationMatchFilter& filter = request.Filters[0]; - - for (MatchType match : GetMatchTypeOrder(filter.Type)) - { - resultsTable.SearchOnField(filter.Field, match, filter.Value); - } - - // Skip the filter as we already know everything matches - filterIndex = 1; - } - - // 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) - { - const ApplicationMatchFilter& filter = request.Filters[i]; - - resultsTable.PrepareToFilter(); - - for (MatchType match : GetMatchTypeOrder(filter.Type)) - { - resultsTable.FilterOnField(filter.Field, match, filter.Value); - } - - resultsTable.CompleteFilter(); - } - - return resultsTable.GetSearchResults(request.MaximumResults); - } - - std::optional<std::string> Interface::GetIdStringById(SQLite::Connection& connection, SQLite::rowid_t id) - { - return IdTable::SelectValueById(connection, id); - } - - std::optional<std::string> Interface::GetNameStringById(SQLite::Connection& connection, SQLite::rowid_t id) - { - std::optional<SQLite::rowid_t> manifestIdOpt = GetManifestIdByKey(connection, id); - - if (!manifestIdOpt) - { - AICLI_LOG(Repo, Info, << "Did not find manifest by Id id: " << id); - return {}; - } - - auto [name] = ManifestTable::GetValuesById<NameTable>(connection, manifestIdOpt.value()); - return name; - } - - std::optional<std::string> Interface::GetPathStringByKey(SQLite::Connection& connection, SQLite::rowid_t id, std::string_view version, std::string_view channel) - { - std::optional<SQLite::rowid_t> manifestIdOpt = GetManifestIdByKey(connection, id, version, channel); - - if (!manifestIdOpt) - { - AICLI_LOG(Repo, Info, << "Did not find manifest for: " << id << ", " << version << ", " << channel); - return {}; - } - - auto [pathPartId] = ManifestTable::GetIdsById<PathPartTable>(connection, manifestIdOpt.value()); - - return PathPartTable::GetPathById(connection, pathPartId); - } - - std::vector<Utility::VersionAndChannel> Interface::GetVersionsById(SQLite::Connection& connection, SQLite::rowid_t id) - { - auto versionsAndChannels = ManifestTable::GetAllValuesById<IdTable, VersionTable, ChannelTable>(connection, id); - - std::vector<Utility::VersionAndChannel> result; - result.reserve(versionsAndChannels.size()); - for (auto&& vac : versionsAndChannels) - { - result.emplace_back(Utility::Version{ std::move(std::get<0>(vac)) }, Utility::Channel{ std::move(std::get<1>(vac)) }); - } - - std::sort(result.begin(), result.end()); - - return result; - } -} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface.h @@ -2,24 +2,38 @@ // Licensed under the MIT License. #pragma once #include "Microsoft/Schema/ISQLiteIndex.h" +#include "Microsoft/Schema/1_0/SearchResultsTable.h" + +#include <memory> +#include <vector> namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { // Interface to this schema version exposed through ISQLiteIndex. - struct Interface : public SQLiteIndexBase + struct Interface : public ISQLiteIndex { // Version 1.0 Schema::Version GetVersion() const override; void CreateTables(SQLite::Connection& connection) override; - void AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; - bool UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; - void RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; + SQLite::rowid_t AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; + std::pair<bool, SQLite::rowid_t> UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; + SQLite::rowid_t RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; void PrepareForPackaging(SQLite::Connection& connection) override; SearchResult Search(SQLite::Connection& connection, const SearchRequest& request) override; std::optional<std::string> GetIdStringById(SQLite::Connection& connection, SQLite::rowid_t id) override; std::optional<std::string> GetNameStringById(SQLite::Connection& connection, SQLite::rowid_t id) override; std::optional<std::string> GetPathStringByKey(SQLite::Connection& connection, SQLite::rowid_t id, std::string_view version, std::string_view channel) override; std::vector<Utility::VersionAndChannel> GetVersionsById(SQLite::Connection& connection, SQLite::rowid_t id) override; + + protected: + // Creates the search results table. + virtual std::unique_ptr<SearchResultsTable> CreateSearchResultsTable(SQLite::Connection& connection) const; + + // Gets the ordering of matches to execute, with more specific matches coming first. + virtual std::vector<MatchType> GetMatchTypeOrder(MatchType type) const; + + // Executes all relevant searchs for the query. + virtual void PerformQuerySearch(SearchResultsTable& resultsTable, const RequestMatch& query) const; }; } diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface_1_0.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/Interface_1_0.cpp @@ -0,0 +1,524 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#include "pch.h" +#include "Microsoft/Schema/1_0/Interface.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/VersionTable.h" +#include "Microsoft/Schema/1_0/ChannelTable.h" + +#include "Microsoft/Schema/1_0/PathPartTable.h" + +#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" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_0 +{ + namespace + { + // Gets an existing manifest by its rowid., if it exists. + std::optional<SQLite::rowid_t> GetExistingManifestId(SQLite::Connection& connection, const Manifest::Manifest& manifest) + { + std::optional<SQLite::rowid_t> idId = IdTable::SelectIdByValue(connection, manifest.Id, true); + if (!idId) + { + AICLI_LOG(Repo, Info, << "Did not find an Id { " << manifest.Id << " }"); + return {}; + } + + std::optional<SQLite::rowid_t> versionId = VersionTable::SelectIdByValue(connection, manifest.Version, true); + if (!versionId) + { + AICLI_LOG(Repo, Info, << "Did not find a Version { " << manifest.Version << " }"); + return {}; + } + + std::optional<SQLite::rowid_t> channelId = ChannelTable::SelectIdByValue(connection, manifest.Channel, true); + if (!channelId) + { + AICLI_LOG(Repo, Info, << "Did not find a Channel { " << manifest.Channel << " }"); + return {}; + } + + auto result = ManifestTable::SelectByValueIds<IdTable, VersionTable, ChannelTable>(connection, { idId.value(), versionId.value(), channelId.value() }); + + if (!result) + { + AICLI_LOG(Repo, Info, << "Did not find a manifest row for { " << manifest.Id << ", " << manifest.Version << ", " << manifest.Channel << " }"); + } + + return result; + } + + // Gets a manifest id by the given key values. + std::optional<SQLite::rowid_t> GetManifestIdByKey(SQLite::Connection& connection, SQLite::rowid_t id, std::string_view version = "", std::string_view channel = "") + { + std::optional<SQLite::rowid_t> channelIdOpt = ChannelTable::SelectIdByValue(connection, channel, true); + if (!channelIdOpt && !channel.empty()) + { + // If an empty channel was given but none was found, we will just not filter on channel. + AICLI_LOG(Repo, Info, << "Did not find a Channel { " << channel << " }"); + return {}; + } + + std::optional<SQLite::rowid_t> versionIdOpt; + + if (version.empty()) + { + std::vector<std::string> versionStrings; + + if (channelIdOpt) + { + versionStrings = ManifestTable::GetAllValuesByIds<VersionTable, IdTable, ChannelTable>(connection, { id, channelIdOpt.value() }); + } + else + { + versionStrings = ManifestTable::GetAllValuesByIds<VersionTable, IdTable>(connection, { id }); + } + + if (versionStrings.empty()) + { + AICLI_LOG(Repo, Info, << "Did not find any Versions { " << id << ", " << channel << " }"); + return {}; + } + + // Convert the strings to Versions and sort them + std::vector<Utility::Version> versions; + for (std::string& v : versionStrings) + { + versions.emplace_back(std::move(v)); + } + + std::sort(versions.begin(), versions.end()); + + // Get the last version in the list (the highest version) and its rowid + const std::string& latestVersion = versions.back().ToString(); + versionIdOpt = VersionTable::SelectIdByValue(connection, latestVersion); + } + else + { + versionIdOpt = VersionTable::SelectIdByValue(connection, version, true); + } + + if (!versionIdOpt) + { + AICLI_LOG(Repo, Info, << "Did not find a Version { " << version << " }"); + return {}; + } + + if (channelIdOpt) + { + return ManifestTable::SelectByValueIds<IdTable, VersionTable, ChannelTable>(connection, { id, versionIdOpt.value(), channelIdOpt.value() }); + } + else + { + return ManifestTable::SelectByValueIds<IdTable, VersionTable>(connection, { id, versionIdOpt.value() }); + } + } + + // Updates the manifest column and related table based on the given value. + template <typename Table> + void UpdateManifestValueById(SQLite::Connection& connection, const typename Table::value_t& value, SQLite::rowid_t manifestId, bool overwriteLikeMatch = false) + { + auto [oldValueId] = ManifestTable::GetIdsById<Table>(connection, manifestId); + + SQLite::rowid_t newValueId = Table::EnsureExists(connection, value, overwriteLikeMatch); + + ManifestTable::UpdateValueIdById<Table>(connection, manifestId, newValueId); + + Table::DeleteIfNotNeededById(connection, oldValueId); + } + } + + Schema::Version Interface::GetVersion() const + { + return { 1, 0 }; + } + + void Interface::CreateTables(SQLite::Connection& connection) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createtables_v1_0"); + + IdTable::Create_deprecated(connection); + NameTable::Create_deprecated(connection); + MonikerTable::Create_deprecated(connection); + VersionTable::Create_deprecated(connection); + ChannelTable::Create_deprecated(connection); + + PathPartTable::Create_deprecated(connection); + + ManifestTable::Create_deprecated(connection, { + { IdTable::ValueName(), true, false }, + { NameTable::ValueName(), false, false }, + { MonikerTable::ValueName(), false, false }, + { VersionTable::ValueName(), true, false }, + { ChannelTable::ValueName(), true, false }, + { PathPartTable::ValueName(), false, true } + }); + + TagsTable::Create_deprecated(connection); + CommandsTable::Create_deprecated(connection); + + savepoint.Commit(); + } + + SQLite::rowid_t Interface::AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) + { + auto manifestResult = GetExistingManifestId(connection, manifest); + + // If this manifest is already present, we can't add it. + THROW_HR_IF(HRESULT_FROM_WIN32(ERROR_ALREADY_EXISTS), manifestResult.has_value()); + + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "addmanifest_v1_0"); + + auto [pathAdded, pathLeafId] = PathPartTable::EnsurePathExists(connection, relativePath, true); + + // If we get false from the function, this manifest path already exists in the index. + THROW_HR_IF(HRESULT_FROM_WIN32(ERROR_ALREADY_EXISTS), !pathAdded); + + // Ensure that all of the 1:1 data exists. + SQLite::rowid_t idId = IdTable::EnsureExists(connection, manifest.Id, true); + SQLite::rowid_t nameId = NameTable::EnsureExists(connection, manifest.Name); + SQLite::rowid_t monikerId = MonikerTable::EnsureExists(connection, manifest.AppMoniker); + SQLite::rowid_t versionId = VersionTable::EnsureExists(connection, manifest.Version); + SQLite::rowid_t channelId = ChannelTable::EnsureExists(connection, manifest.Channel); + + // Insert the manifest entry. + SQLite::rowid_t manifestId = ManifestTable::Insert(connection, { + { IdTable::ValueName(), idId}, + { NameTable::ValueName(), nameId }, + { MonikerTable::ValueName(), monikerId }, + { VersionTable::ValueName(), versionId }, + { ChannelTable::ValueName(), channelId }, + { PathPartTable::ValueName(), pathLeafId } + }); + + // Add all of the 1:N data. + TagsTable::EnsureExistsAndInsert(connection, manifest.Tags, manifestId); + CommandsTable::EnsureExistsAndInsert(connection, manifest.Commands, manifestId); + + savepoint.Commit(); + + return manifestId; + } + + std::pair<bool, SQLite::rowid_t> Interface::UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) + { + auto manifestResult = GetExistingManifestId(connection, manifest); + + // If the manifest doesn't actually exist, fail the update. + THROW_HR_IF(E_NOT_SET, !manifestResult); + + SQLite::rowid_t manifestId = manifestResult.value(); + + auto [idInIndex, nameInIndex, monikerInIndex, versionInIndex, channelInIndex] = + ManifestTable::GetValuesById<IdTable, NameTable, MonikerTable, VersionTable, ChannelTable>(connection, manifestId); + + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "updatemanifest_v1_0"); + bool indexModified = false; + + // Id, Version, and Channel may have changed casing. If so, they too need to be updated. + if (idInIndex != manifest.Id) + { + UpdateManifestValueById<IdTable>(connection, manifest.Id, manifestId, true); + indexModified = true; + } + + if (versionInIndex != manifest.Version) + { + UpdateManifestValueById<VersionTable>(connection, manifest.Version, manifestId); + indexModified = true; + } + + if (channelInIndex != manifest.Channel) + { + UpdateManifestValueById<ChannelTable>(connection, manifest.Channel, manifestId); + indexModified = true; + } + + if (nameInIndex != manifest.Name) + { + UpdateManifestValueById<NameTable>(connection, manifest.Name, manifestId); + indexModified = true; + } + + if (monikerInIndex != manifest.AppMoniker) + { + UpdateManifestValueById<MonikerTable>(connection, manifest.AppMoniker, manifestId); + indexModified = true; + } + + // Update path table if necessary + auto [existingPathLeafId] = ManifestTable::GetIdsById<PathPartTable>(connection, manifestId); + auto [pathAdded, newPathLeafId] = PathPartTable::EnsurePathExists(connection, relativePath, true); + + if (pathAdded) + { + // Path was added, so we need to update the manifest table and delete the old path + ManifestTable::UpdateValueIdById<PathPartTable>(connection, manifestId, newPathLeafId); + PathPartTable::RemovePathById(connection, existingPathLeafId); + indexModified = true; + } + else + { + // The path already existed, so it must either match the existing manifest path or it is an error + THROW_HR_IF(HRESULT_FROM_WIN32(ERROR_ALREADY_EXISTS), existingPathLeafId != newPathLeafId); + } + + // Update all 1:N tables as necessary + indexModified = TagsTable::UpdateIfNeededByManifestId(connection, manifest.Tags, manifestId) || indexModified; + indexModified = CommandsTable::UpdateIfNeededByManifestId(connection, manifest.Commands, manifestId) || indexModified; + + savepoint.Commit(); + + return { indexModified, manifestId }; + } + + SQLite::rowid_t Interface::RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path&) + { + auto manifestResult = GetExistingManifestId(connection, manifest); + + // If the manifest doesn't actually exist, fail the remove. + THROW_HR_IF(E_NOT_SET, !manifestResult); + + SQLite::rowid_t manifestId = manifestResult.value(); + + // Get the ids of the values from the manifest table + auto [idId, nameId, monikerId, versionId, channelId, pathLeafId] = + ManifestTable::GetIdsById<IdTable, NameTable, MonikerTable, VersionTable, ChannelTable, PathPartTable>(connection, manifestId); + + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "removemanifest_v1_0"); + + // Remove the manifest row + ManifestTable::DeleteById(connection, manifestId); + + // Remove all of the 1:1 data that is no longer referenced. + IdTable::DeleteIfNotNeededById(connection, idId); + NameTable::DeleteIfNotNeededById(connection, nameId); + MonikerTable::DeleteIfNotNeededById(connection, monikerId); + VersionTable::DeleteIfNotNeededById(connection, versionId); + ChannelTable::DeleteIfNotNeededById(connection, channelId); + + // Remove the path + PathPartTable::RemovePathById(connection, pathLeafId); + + // Remove all of the 1:N data that is no longer referenced. + TagsTable::DeleteIfNotNeededByManifestId(connection, manifestId); + CommandsTable::DeleteIfNotNeededByManifestId(connection, manifestId); + + savepoint.Commit(); + + return manifestId; + } + + void Interface::PrepareForPackaging(SQLite::Connection& connection) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "prepareforpackaging_v1_0"); + + IdTable::PrepareForPackaging_deprecated(connection); + NameTable::PrepareForPackaging_deprecated(connection); + MonikerTable::PrepareForPackaging_deprecated(connection); + VersionTable::PrepareForPackaging_deprecated(connection); + ChannelTable::PrepareForPackaging_deprecated(connection); + + PathPartTable::PrepareForPackaging_deprecated(connection); + + ManifestTable::PrepareForPackaging_deprecated(connection, { + VersionTable::ValueName(), + ChannelTable::ValueName(), + PathPartTable::ValueName(), + }); + + TagsTable::PrepareForPackaging_deprecated(connection); + CommandsTable::PrepareForPackaging_deprecated(connection); + + savepoint.Commit(); + + // Force the database to actually shrink the file size. + // This *must* be done outside of an active transaction. + SQLite::Builder::StatementBuilder builder; + builder.Vacuum(); + builder.Execute(connection); + } + + ISQLiteIndex::SearchResult Interface::Search(SQLite::Connection& connection, const SearchRequest& request) + { + // If an empty request, get everything + if (!request.Query && request.Inclusions.empty() && request.Filters.empty()) + { + std::vector<SQLite::rowid_t> ids = IdTable::GetAllRowIds(connection, request.MaximumResults); + + SearchResult result; + for (SQLite::rowid_t id : ids) + { + result.Matches.emplace_back(std::make_pair(id, ApplicationMatchFilter(ApplicationMatchField::Id, MatchType::Wildcard, {}))); + } + + result.Truncated = (request.MaximumResults && IdTable::GetCount(connection) > request.MaximumResults); + + 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 Inclusions has fields, we add these to the data. + // If neither is defined, we take the first filter and use it as the initial results search. + std::unique_ptr<SearchResultsTable> resultsTable = CreateSearchResultsTable(connection); + bool inclusionsAttempted = false; + + if (request.Query) + { + // Perform searches across multiple tables to populate the initial results. + PerformQuerySearch(*resultsTable.get(), request.Query.value()); + + inclusionsAttempted = true; + } + + if (!request.Inclusions.empty()) + { + for (const auto& include : request.Inclusions) + { + for (MatchType match : GetMatchTypeOrder(include.Type)) + { + resultsTable->SearchOnField(include.Field, match, include.Value); + } + } + + inclusionsAttempted = true; + } + + size_t filterIndex = 0; + if (!inclusionsAttempted) + { + THROW_HR_IF(E_UNEXPECTED, request.Filters.empty()); + + // Perform search for just the field matching the first filter + const ApplicationMatchFilter& filter = request.Filters[0]; + + for (MatchType match : GetMatchTypeOrder(filter.Type)) + { + resultsTable->SearchOnField(filter.Field, match, filter.Value); + } + + // Skip the filter as we already know everything matches + filterIndex = 1; + } + + // 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) + { + const ApplicationMatchFilter& filter = request.Filters[i]; + + resultsTable->PrepareToFilter(); + + for (MatchType match : GetMatchTypeOrder(filter.Type)) + { + resultsTable->FilterOnField(filter.Field, match, filter.Value); + } + + resultsTable->CompleteFilter(); + } + + return resultsTable->GetSearchResults(request.MaximumResults); + } + + std::optional<std::string> Interface::GetIdStringById(SQLite::Connection& connection, SQLite::rowid_t id) + { + return IdTable::SelectValueById(connection, id); + } + + std::optional<std::string> Interface::GetNameStringById(SQLite::Connection& connection, SQLite::rowid_t id) + { + std::optional<SQLite::rowid_t> manifestIdOpt = GetManifestIdByKey(connection, id); + + if (!manifestIdOpt) + { + AICLI_LOG(Repo, Info, << "Did not find manifest by Id id: " << id); + return {}; + } + + auto [name] = ManifestTable::GetValuesById<NameTable>(connection, manifestIdOpt.value()); + return name; + } + + std::optional<std::string> Interface::GetPathStringByKey(SQLite::Connection& connection, SQLite::rowid_t id, std::string_view version, std::string_view channel) + { + std::optional<SQLite::rowid_t> manifestIdOpt = GetManifestIdByKey(connection, id, version, channel); + + if (!manifestIdOpt) + { + AICLI_LOG(Repo, Info, << "Did not find manifest for: " << id << ", " << version << ", " << channel); + return {}; + } + + auto [pathPartId] = ManifestTable::GetIdsById<PathPartTable>(connection, manifestIdOpt.value()); + + return PathPartTable::GetPathById(connection, pathPartId); + } + + std::vector<Utility::VersionAndChannel> Interface::GetVersionsById(SQLite::Connection& connection, SQLite::rowid_t id) + { + auto versionsAndChannels = ManifestTable::GetAllValuesById<IdTable, VersionTable, ChannelTable>(connection, id); + + std::vector<Utility::VersionAndChannel> result; + result.reserve(versionsAndChannels.size()); + for (auto&& vac : versionsAndChannels) + { + result.emplace_back(Utility::Version{ std::move(std::get<0>(vac)) }, Utility::Channel{ std::move(std::get<1>(vac)) }); + } + + std::sort(result.begin(), result.end()); + + return result; + } + + std::unique_ptr<SearchResultsTable> Interface::CreateSearchResultsTable(SQLite::Connection& connection) const + { + return std::make_unique<SearchResultsTable>(connection); + } + + std::vector<MatchType> Interface::GetMatchTypeOrder(MatchType type) const + { + switch (type) + { + case MatchType::Exact: + return { MatchType::Exact }; + case MatchType::CaseInsensitive: + return { MatchType::CaseInsensitive }; + case MatchType::StartsWith: + return { MatchType::CaseInsensitive, MatchType::StartsWith }; + case MatchType::Substring: + return { MatchType::CaseInsensitive, MatchType::Substring }; + case MatchType::Wildcard: + return { MatchType::Wildcard }; + case MatchType::Fuzzy: + return { MatchType::CaseInsensitive, MatchType::Fuzzy }; + case MatchType::FuzzySubstring: + return { MatchType::CaseInsensitive, MatchType::Fuzzy, MatchType::Substring, MatchType::FuzzySubstring }; + default: + THROW_HR(E_UNEXPECTED); + } + } + + void Interface::PerformQuerySearch(SearchResultsTable& resultsTable, const RequestMatch& query) const + { + for (MatchType match : GetMatchTypeOrder(query.Type)) + { + 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); + } + } +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.cpp @@ -229,10 +229,69 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 return s_ManifestTable_Table_Name; } + // Starting in V1.1, all code should be going this route of creating named indeces rather than using primary or unique keys on columns. + // The resulting database will function the same, but give us control to drop the indeces to reduce space. void ManifestTable::Create(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values) { using namespace SQLite::Builder; + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createManifestTable_v1_1"); + + StatementBuilder createTableBuilder; + createTableBuilder.CreateTable(s_ManifestTable_Table_Name).BeginColumns(); + + for (const ManifestColumnInfo& value : values) + { + createTableBuilder.Column(ColumnBuilder(value.Name, Type::Int64).NotNull()); + } + + createTableBuilder.EndColumns(); + + createTableBuilder.Execute(connection); + + // Create a unique index with the primary key values + StatementBuilder pkIndexBuilder; + + pkIndexBuilder.CreateUniqueIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Suffix }).On(s_ManifestTable_Table_Name).BeginColumns(); + + for (const ManifestColumnInfo& value : values) + { + if (value.PrimaryKey) + { + pkIndexBuilder.Column(value.Name); + } + } + + pkIndexBuilder.EndColumns(); + + pkIndexBuilder.Execute(connection); + + // Create an index on every value to improve performance + for (const ManifestColumnInfo& value : values) + { + StatementBuilder createIndexBuilder; + + if (value.Unique) + { + createIndexBuilder.CreateUniqueIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Separator, value.Name, s_ManifestTable_Index_Suffix }); + } + else + { + createIndexBuilder.CreateIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Separator, value.Name, s_ManifestTable_Index_Suffix }); + } + + createIndexBuilder.On(s_ManifestTable_Table_Name).Columns(value.Name); + + createIndexBuilder.Execute(connection); + } + + savepoint.Commit(); + } + + void ManifestTable::Create_deprecated(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values) + { + using namespace SQLite::Builder; + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createManifestTable_v1_0"); StatementBuilder createTableBuilder; @@ -303,6 +362,19 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 void ManifestTable::PrepareForPackaging(SQLite::Connection& connection, std::initializer_list<std::string_view> values) { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "pfpManifestTable_v1_1"); + + PrepareForPackaging_deprecated(connection, values); + + SQLite::Builder::StatementBuilder dropPKIndexBuilder; + dropPKIndexBuilder.DropIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Suffix }); + dropPKIndexBuilder.Execute(connection); + + savepoint.Commit(); + } + + void ManifestTable::PrepareForPackaging_deprecated(SQLite::Connection& connection, std::initializer_list<std::string_view> values) + { SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "pfpManifestTable_v1_0"); // Drop the index on the requested values diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/ManifestTable.h @@ -79,9 +79,12 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Get the table name. static std::string_view TableName(); - // Creates the table. + // Creates the table with named indeces. static void Create(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values); + // Creates the table with standard primary keys. + static void Create_deprecated(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values); + // Insert the given values into the table. static SQLite::rowid_t Insert(SQLite::Connection& connection, std::initializer_list<ManifestOneToOneValue> values); @@ -147,6 +150,9 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Removes data that is no longer needed for an index that is to be published. static void PrepareForPackaging(SQLite::Connection& connection, std::initializer_list<std::string_view> values); + // Removes data that is no longer needed for an index that is to be published. + static void PrepareForPackaging_deprecated(SQLite::Connection& connection, std::initializer_list<std::string_view> values); + // Determines if the table is empty. static bool IsEmpty(SQLite::Connection& connection); }; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.cpp @@ -13,6 +13,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 using namespace std::string_view_literals; static constexpr std::string_view s_OneToManyTable_MapTable_ManifestName = "manifest"sv; static constexpr std::string_view s_OneToManyTable_MapTable_Suffix = "_map"sv; + static constexpr std::string_view s_OneToManyTable_MapTable_PrimaryKeyIndexSuffix = "_pkindex"sv; static constexpr std::string_view s_OneToManyTable_MapTable_IndexSuffix = "_index"sv; namespace @@ -96,24 +97,43 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 return s_OneToManyTable_MapTable_ManifestName; } - void CreateOneToManyTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName) + void CreateOneToManyTable(SQLite::Connection& connection, bool useNamedIndeces, std::string_view tableName, std::string_view valueName) { using namespace SQLite::Builder; SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_create_v1_0"); // Create the data table as a 1:1 - CreateOneToOneTable(connection, tableName, valueName); + CreateOneToOneTable(connection, tableName, valueName, useNamedIndeces); - // Create the mapping table - StatementBuilder createMapTableBuilder; - createMapTableBuilder.CreateTable({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ - ColumnBuilder(s_OneToManyTable_MapTable_ManifestName, Type::Int64).NotNull(), - ColumnBuilder(valueName, Type::Int64).NotNull(), - PrimaryKeyBuilder({ valueName, s_OneToManyTable_MapTable_ManifestName }) - }); - - createMapTableBuilder.Execute(connection); + if (useNamedIndeces) + { + // Create the mapping table + StatementBuilder createMapTableBuilder; + createMapTableBuilder.CreateTable({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ + ColumnBuilder(s_OneToManyTable_MapTable_ManifestName, Type::Int64).NotNull(), + ColumnBuilder(valueName, Type::Int64).NotNull() + }); + + createMapTableBuilder.Execute(connection); + + StatementBuilder pkIndexBuilder; + pkIndexBuilder.CreateUniqueIndex({ tableName, s_OneToManyTable_MapTable_Suffix, s_OneToManyTable_MapTable_PrimaryKeyIndexSuffix }). + On({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ valueName, s_OneToManyTable_MapTable_ManifestName }); + pkIndexBuilder.Execute(connection); + } + else + { + // Create the mapping table + StatementBuilder createMapTableBuilder; + createMapTableBuilder.CreateTable({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ + ColumnBuilder(s_OneToManyTable_MapTable_ManifestName, Type::Int64).NotNull(), + ColumnBuilder(valueName, Type::Int64).NotNull(), + PrimaryKeyBuilder({ valueName, s_OneToManyTable_MapTable_ManifestName }) + }); + + createMapTableBuilder.Execute(connection); + } StatementBuilder createMapTableIndexBuilder; createMapTableIndexBuilder.CreateIndex({ tableName, s_OneToManyTable_MapTable_Suffix, s_OneToManyTable_MapTable_IndexSuffix }). @@ -226,12 +246,14 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 savepoint.Commit(); } - void OneToManyTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName) + void OneToManyTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName, bool useNamedIndeces, bool preserveValuesIndex) { SQLite::Builder::StatementBuilder dropMapTableIndexBuilder; dropMapTableIndexBuilder.DropIndex({ tableName, s_OneToManyTable_MapTable_Suffix, s_OneToManyTable_MapTable_IndexSuffix }); dropMapTableIndexBuilder.Execute(connection); + + OneToOneTablePrepareForPackaging(connection, tableName, useNamedIndeces, preserveValuesIndex); } bool OneToManyTableIsEmpty(SQLite::Connection& connection, std::string_view tableName) diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToManyTable.h @@ -18,7 +18,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 std::string_view OneToManyTableGetManifestColumnName(); // Create the tables. - void CreateOneToManyTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName); + void CreateOneToManyTable(SQLite::Connection& connection, bool useNamedIndeces, std::string_view tableName, std::string_view valueName); // Ensures that the value exists and inserts mapping entries. void OneToManyTableEnsureExistsAndInsert(SQLite::Connection& connection, @@ -34,7 +34,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 void OneToManyTableDeleteIfNotNeededByManifestId(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t manifestId); // Removes data that is no longer needed for an index that is to be published. - void OneToManyTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName); + void OneToManyTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName, bool useNamedIndeces, bool preserveValuesIndex); // Determines if the table is empty. bool OneToManyTableIsEmpty(SQLite::Connection& connection, std::string_view tableName); @@ -62,10 +62,16 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 return false; } - // Creates the table. + // Creates the table with named indeces. static void Create(SQLite::Connection& connection) { - details::CreateOneToManyTable(connection, TableInfo::TableName(), TableInfo::ValueName()); + details::CreateOneToManyTable(connection, true, TableInfo::TableName(), TableInfo::ValueName()); + } + + // Creates the table with standard primary keys. + static void Create_deprecated(SQLite::Connection& connection) + { + details::CreateOneToManyTable(connection, false, TableInfo::TableName(), TableInfo::ValueName()); } // Ensures that all values exist in the data table, and inserts into the mapping table for the given manifest id. @@ -87,9 +93,15 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 } // Removes data that is no longer needed for an index that is to be published. - static void PrepareForPackaging(SQLite::Connection& connection) + static void PrepareForPackaging(SQLite::Connection& connection, bool preserveValuesIndex = false) + { + details::OneToManyTablePrepareForPackaging(connection, TableInfo::TableName(), true, preserveValuesIndex); + } + + // Removes data that is no longer needed for an index that is to be published. + static void PrepareForPackaging_deprecated(SQLite::Connection& connection) { - details::OneToManyTablePrepareForPackaging(connection, TableInfo::TableName()); + details::OneToManyTablePrepareForPackaging(connection, TableInfo::TableName(), false, false); } // Determines if the table is empty. diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.cpp @@ -10,16 +10,43 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { namespace details { - void CreateOneToOneTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName) + using namespace std::string_view_literals; + static constexpr std::string_view s_OneToOneTable_IndexSuffix = "_pkindex"sv; + + void CreateOneToOneTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, bool useNamedIndeces) { using namespace SQLite::Builder; - StatementBuilder createTableBuilder; - createTableBuilder.CreateTable(tableName).Columns({ - ColumnBuilder(valueName, Type::Text).NotNull().PrimaryKey() - }); + // Starting in V1.1, all code should be going this route of creating named indeces rather than using primary or unique keys on columns. + // The resulting database will function the same, but give us control to drop the indeces to reduce space. + if (useNamedIndeces) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_create_v1_1"); + + StatementBuilder createTableBuilder; + + createTableBuilder.CreateTable(tableName).Columns({ + ColumnBuilder(valueName, Type::Text).NotNull() + }); + + createTableBuilder.Execute(connection); - createTableBuilder.Execute(connection); + StatementBuilder indexBuilder; + indexBuilder.CreateUniqueIndex({ tableName, s_OneToOneTable_IndexSuffix }).On(tableName).Columns(valueName); + indexBuilder.Execute(connection); + + savepoint.Commit(); + } + else + { + StatementBuilder createTableBuilder; + + createTableBuilder.CreateTable(tableName).Columns({ + ColumnBuilder(valueName, Type::Text).NotNull().PrimaryKey() + }); + + createTableBuilder.Execute(connection); + } } std::optional<SQLite::rowid_t> OneToOneTableSelectIdByValue(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, std::string_view value, bool useLike) @@ -128,6 +155,16 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 builder.Execute(connection); } + void OneToOneTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName, bool useNamedIndeces, bool preserveValuesIndex) + { + if (useNamedIndeces && !preserveValuesIndex) + { + SQLite::Builder::StatementBuilder dropIndexBuilder; + dropIndexBuilder.DropIndex({ tableName, s_OneToOneTable_IndexSuffix }); + dropIndexBuilder.Execute(connection); + } + } + uint64_t OneToOneTableGetCount(SQLite::Connection& connection, std::string_view tableName) { SQLite::Builder::StatementBuilder builder; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/OneToOneTable.h @@ -13,7 +13,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 namespace details { // Creates the table. - void CreateOneToOneTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName); + void CreateOneToOneTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, bool useNamedIndeces); // Selects the value from the table, returning the rowid if it exists. std::optional<SQLite::rowid_t> OneToOneTableSelectIdByValue(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, std::string_view value, bool useLike = false); @@ -30,6 +30,9 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Removes the given row by its rowid if it is no longer referenced. void OneToOneTableDeleteIfNotNeededById(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t id); + // Removes data that is no longer needed for an index that is to be published. + void OneToOneTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName, bool useNamedIndeces, bool preserveValuesIndex); + // Gets the total number of rows in the table. uint64_t OneToOneTableGetCount(SQLite::Connection& connection, std::string_view tableName); @@ -47,10 +50,16 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // The id type using id_t = SQLite::rowid_t; - // Creates the table. + // Creates the table with named indeces. static void Create(SQLite::Connection& connection) { - details::CreateOneToOneTable(connection, TableInfo::TableName(), TableInfo::ValueName()); + details::CreateOneToOneTable(connection, TableInfo::TableName(), TableInfo::ValueName(), true); + } + + // Creates the table with standard primary keys. + static void Create_deprecated(SQLite::Connection& connection) + { + details::CreateOneToOneTable(connection, TableInfo::TableName(), TableInfo::ValueName(), false); } // The name of the table. @@ -102,9 +111,15 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 } // Removes data that is no longer needed for an index that is to be published. - static void PrepareForPackaging(SQLite::Connection&) + static void PrepareForPackaging(SQLite::Connection& connection, bool preserveValuesIndex = false) + { + details::OneToOneTablePrepareForPackaging(connection, TableInfo::TableName(), true, preserveValuesIndex); + } + + // Removes data that is no longer needed for an index that is to be published. + static void PrepareForPackaging_deprecated(SQLite::Connection& connection) { - // There is currently nothing to do for these tables. + details::OneToOneTablePrepareForPackaging(connection, TableInfo::TableName(), false, false); } // Gets the total number of rows in the table. diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/PathPartTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/PathPartTable.cpp @@ -9,6 +9,7 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 { using namespace std::string_view_literals; static constexpr std::string_view s_PathPartTable_Table_Name = "pathparts"sv; + static constexpr std::string_view s_PathPartTable_PrimaryKeyIndex_Name = "pathparts_pkindex"sv; static constexpr std::string_view s_PathPartTable_ParentIndex_Name = "pathparts_parentidx"sv; static constexpr std::string_view s_PathPartTable_ParentValue_Name = "parent"sv; static constexpr std::string_view s_PathPartTable_PartValue_Name = "pathpart"sv; @@ -93,10 +94,37 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 } } + // Starting in V1.1, all code should be going this route of creating named indeces rather than using primary or unique keys on columns. + // The resulting database will function the same, but give us control to drop the indeces to reduce space. void PathPartTable::Create(SQLite::Connection& connection) { using namespace SQLite::Builder; + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createPathParts_v1_1"); + + StatementBuilder createTableBuilder; + createTableBuilder.CreateTable(s_PathPartTable_Table_Name).Columns({ + ColumnBuilder(s_PathPartTable_ParentValue_Name, Type::Int64), + ColumnBuilder(s_PathPartTable_PartValue_Name, Type::Text).NotNull() + }); + + createTableBuilder.Execute(connection); + + StatementBuilder createPKIndexBuilder; + createPKIndexBuilder.CreateUniqueIndex(s_PathPartTable_PrimaryKeyIndex_Name).On(s_PathPartTable_Table_Name).Columns({ s_PathPartTable_PartValue_Name, s_PathPartTable_ParentValue_Name }); + createPKIndexBuilder.Execute(connection); + + StatementBuilder createIndexBuilder; + createIndexBuilder.CreateIndex(s_PathPartTable_ParentIndex_Name).On(s_PathPartTable_Table_Name).Columns(s_PathPartTable_ParentValue_Name); + createIndexBuilder.Execute(connection); + + savepoint.Commit(); + } + + void PathPartTable::Create_deprecated(SQLite::Connection& connection) + { + using namespace SQLite::Builder; + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createPathParts_v1_0"); StatementBuilder createTableBuilder; @@ -249,9 +277,21 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 void PathPartTable::PrepareForPackaging(SQLite::Connection& connection) { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "pfpPathParts_v1_1"); + + PrepareForPackaging_deprecated(connection); + + SQLite::Builder::StatementBuilder dropPKIndexBuilder; + dropPKIndexBuilder.DropIndex(s_PathPartTable_PrimaryKeyIndex_Name); + dropPKIndexBuilder.Execute(connection); + + savepoint.Commit(); + } + + void PathPartTable::PrepareForPackaging_deprecated(SQLite::Connection& connection) + { SQLite::Builder::StatementBuilder dropIndexBuilder; dropIndexBuilder.DropIndex(s_PathPartTable_ParentIndex_Name); - dropIndexBuilder.Execute(connection); } diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/PathPartTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/PathPartTable.h @@ -17,9 +17,12 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // The id type using id_t = SQLite::rowid_t; - // Creates the table. + // Creates the table with named indeces. static void Create(SQLite::Connection& connection); + // Creates the table with standard primary keys. + static void Create_deprecated(SQLite::Connection& connection); + // Gets the value name. static std::string_view ValueName(); @@ -43,6 +46,9 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Removes data that is no longer needed for an index that is to be published. static void PrepareForPackaging(SQLite::Connection& connection); + // Removes data that is no longer needed for an index that is to be published. + static void PrepareForPackaging_deprecated(SQLite::Connection& connection); + // Determines if the table is empty. static bool IsEmpty(SQLite::Connection& connection); }; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.cpp @@ -1,278 +0,0 @@ -// 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_Index_Suffix = "_i_m"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; - - bool MatchUsesLike(MatchType match) - { - return (match != MatchType::Exact); - } - - int BuildSearchStatement(SQLite::Builder::StatementBuilder& builder, ApplicationMatchField field, MatchType match) - { - bool useLike = MatchUsesLike(match); - - switch (field) - { - case ApplicationMatchField::Id: - return ManifestTable::BuildSearchStatement<IdTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); - case ApplicationMatchField::Name: - return ManifestTable::BuildSearchStatement<NameTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); - case ApplicationMatchField::Moniker: - return ManifestTable::BuildSearchStatement<MonikerTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); - case ApplicationMatchField::Tag: - return ManifestTable::BuildSearchStatement<TagsTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); - case ApplicationMatchField::Command: - return ManifestTable::BuildSearchStatement<CommandsTable>(builder, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, useLike); - default: - THROW_HR(E_UNEXPECTED); - } - } - - 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 valueToUse; - - if (escapeValueForLike) - { - valueToUse = SQLite::EscapeStringForLike(value); - } - else - { - valueToUse = value; - } - - switch (match) - { - case AppInstaller::Repository::MatchType::StartsWith: - valueToUse += '%'; - break; - case AppInstaller::Repository::MatchType::Substring: - valueToUse = "%"s + valueToUse + '%'; - break; - default: - // No changes required for others. - break; - } - - 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); - - { - SQLite::Builder::QualifiedTable index = GetQualifiedName(); - std::string indexName(index.Table); - indexName += s_SearchResultsTable_Index_Suffix; - index.Table = indexName; - - StatementBuilder builder; - builder.CreateIndex(indexName).On(GetQualifiedName().Table).Columns(s_SearchResultsTable_Manifest); - - builder.Execute(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(); - - // Add the field specific portion - int bindIndex = BuildSearchStatement(builder, field, match); - - builder.EndParenthetical().As(s_SearchResultsTable_SubSelect_TableAlias); - - SQLite::Statement statement = builder.Prepare(m_connection); - ExecuteStatementForMatchType(statement, match, bindIndex, MatchUsesLike(match), value); - AICLI_LOG(Repo, Verbose, << "Search found " << m_connection.GetChanges() << " rows"); - } - - void SearchResultsTable::RemoveDuplicateManifestRows() - { - using namespace SQLite::Builder; - - // Create a delete statement to leave only one row with a given manifest. - // This will arbitrarily choose one of the rows if multiple have the same lowest sort order. - // The goal is a statement like this: - // DELETE from <temp> where rowid not in ( - // SELECT rowid from ( - // SELECT rowid, min(sort) from <temp> group by manifest - // ) - // ) - StatementBuilder builder; - builder.DeleteFrom(GetQualifiedName()).Where(SQLite::RowIDName).Not().In().BeginParenthetical(). - Select(SQLite::RowIDName).From().BeginParenthetical(). - Select().Column(SQLite::RowIDName).Column(Aggregate::Min, s_SearchResultsTable_SortValue).From(GetQualifiedName()).GroupBy(s_SearchResultsTable_Manifest). - EndParenthetical(). - EndParenthetical(); - - builder.Execute(m_connection); - AICLI_LOG(Repo, Verbose, << "Removed " << m_connection.GetChanges() << " duplicate rows"); - } - - void SearchResultsTable::PrepareToFilter() - { - // Reset all filter values to unselected - SQLite::Builder::StatementBuilder builder; - builder.Update(GetQualifiedName()).Set().Column(s_SearchResultsTable_Filter).Equals(false); - - builder.Execute(m_connection); - } - - void SearchResultsTable::FilterOnField(ApplicationMatchField field, MatchType match, std::string_view value) - { - using namespace SQLite::Builder; - - // Create an update statement to mark rows that are found by the search. - // This will arbitrarily choose one of the rows if multiple have the same lowest sort order. - // The goal is a statement like this: - // UPDATE <temp> set filter = 1 where manifest in ( - // SELECT m from ( - // SELECT manifest.rowid as m, manifest.id as v from manifest join ids on manifest.id = ids.rowid where ids.id = <value> - // ) - // ) - StatementBuilder builder; - builder.Update(GetQualifiedName()).Set().Column(s_SearchResultsTable_Filter).Equals(true).Where(s_SearchResultsTable_Manifest).In().BeginParenthetical(). - Select(s_SearchResultsTable_SubSelect_ManifestAlias).From().BeginParenthetical(); - - // Add the field specific portion - int bindIndex = BuildSearchStatement(builder, field, match); - - builder.EndParenthetical().EndParenthetical(); - - SQLite::Statement statement = builder.Prepare(m_connection); - ExecuteStatementForMatchType(statement, match, bindIndex, MatchUsesLike(match), value); - AICLI_LOG(Repo, Verbose, << "Filter kept " << m_connection.GetChanges() << " rows"); - } - - void SearchResultsTable::CompleteFilter() - { - // Delete all unselected values - SQLite::Builder::StatementBuilder builder; - builder.DeleteFrom(GetQualifiedName()).Where(s_SearchResultsTable_Filter).Equals(false); - - builder.Execute(m_connection); - AICLI_LOG(Repo, Verbose, << "Filter deleted " << m_connection.GetChanges() << " rows"); - } - - ISQLiteIndex::SearchResult 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)); - - SQLite::Statement select = builder.Prepare(m_connection); - - ISQLiteIndex::SearchResult result; - while (select.Step()) - { - if (limit && result.Matches.size() >= limit) - { - break; - } - - result.Matches.emplace_back(select.GetColumn<SQLite::rowid_t>(0), - ApplicationMatchFilter(select.GetColumn<ApplicationMatchField>(1), select.GetColumn<MatchType>(2), select.GetColumn<std::string>(3))); - } - - result.Truncated = (select.GetState() != SQLite::Statement::State::Completed); - - return result; - } -} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable.h @@ -6,6 +6,7 @@ #include "Microsoft/Schema/ISQLiteIndex.h" #include "AppInstallerRepositorySearch.h" +#include <optional> #include <utility> #include <vector> @@ -41,6 +42,17 @@ namespace AppInstaller::Repository::Microsoft::Schema::V1_0 // Gets the results from the table. ISQLiteIndex::SearchResult GetSearchResults(size_t limit = 0); + protected: + // Builds the search statement for the specified field and match type. + std::optional<int> BuildSearchStatement(SQLite::Builder::StatementBuilder& builder, ApplicationMatchField field, MatchType match) const; + + virtual std::optional<int> BuildSearchStatement( + SQLite::Builder::StatementBuilder& builder, + ApplicationMatchField field, + std::string_view manifestAlias, + std::string_view valueAlias, + bool useLike) const; + private: SQLite::Connection& m_connection; int m_sortOrdinalValue = 0; diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable_1_0.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_0/SearchResultsTable_1_0.cpp @@ -0,0 +1,298 @@ +// 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_Index_Suffix = "_i_m"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; + + bool MatchUsesLike(MatchType match) + { + return (match != MatchType::Exact); + } + + 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 valueToUse; + + if (escapeValueForLike) + { + valueToUse = SQLite::EscapeStringForLike(value); + } + else + { + valueToUse = value; + } + + switch (match) + { + case AppInstaller::Repository::MatchType::StartsWith: + valueToUse += '%'; + break; + case AppInstaller::Repository::MatchType::Substring: + valueToUse = "%"s + valueToUse + '%'; + break; + default: + // No changes required for others. + break; + } + + 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); + + { + SQLite::Builder::QualifiedTable index = GetQualifiedName(); + std::string indexName(index.Table); + indexName += s_SearchResultsTable_Index_Suffix; + index.Table = indexName; + + StatementBuilder builder; + builder.CreateIndex(indexName).On(GetQualifiedName().Table).Columns(s_SearchResultsTable_Manifest); + + builder.Execute(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(); + + // Add the field specific portion + std::optional<int> bindIndex = BuildSearchStatement(builder, field, match); + + if (!bindIndex) + { + AICLI_LOG(Repo, Verbose, << "ApplicationMatchField not supported in this version: " << ApplicationMatchFieldToString(field)); + return; + } + + builder.EndParenthetical().As(s_SearchResultsTable_SubSelect_TableAlias); + + SQLite::Statement statement = builder.Prepare(m_connection); + ExecuteStatementForMatchType(statement, match, bindIndex.value(), MatchUsesLike(match), value); + AICLI_LOG(Repo, Verbose, << "Search found " << m_connection.GetChanges() << " rows"); + } + + void SearchResultsTable::RemoveDuplicateManifestRows() + { + using namespace SQLite::Builder; + + // Create a delete statement to leave only one row with a given manifest. + // This will arbitrarily choose one of the rows if multiple have the same lowest sort order. + // The goal is a statement like this: + // DELETE from <temp> where rowid not in ( + // SELECT rowid from ( + // SELECT rowid, min(sort) from <temp> group by manifest + // ) + // ) + StatementBuilder builder; + builder.DeleteFrom(GetQualifiedName()).Where(SQLite::RowIDName).Not().In().BeginParenthetical(). + Select(SQLite::RowIDName).From().BeginParenthetical(). + Select().Column(SQLite::RowIDName).Column(Aggregate::Min, s_SearchResultsTable_SortValue).From(GetQualifiedName()).GroupBy(s_SearchResultsTable_Manifest). + EndParenthetical(). + EndParenthetical(); + + builder.Execute(m_connection); + AICLI_LOG(Repo, Verbose, << "Removed " << m_connection.GetChanges() << " duplicate rows"); + } + + void SearchResultsTable::PrepareToFilter() + { + // Reset all filter values to unselected + SQLite::Builder::StatementBuilder builder; + builder.Update(GetQualifiedName()).Set().Column(s_SearchResultsTable_Filter).Equals(false); + + builder.Execute(m_connection); + } + + void SearchResultsTable::FilterOnField(ApplicationMatchField field, MatchType match, std::string_view value) + { + using namespace SQLite::Builder; + + // Create an update statement to mark rows that are found by the search. + // This will arbitrarily choose one of the rows if multiple have the same lowest sort order. + // The goal is a statement like this: + // UPDATE <temp> set filter = 1 where manifest in ( + // SELECT m from ( + // SELECT manifest.rowid as m, manifest.id as v from manifest join ids on manifest.id = ids.rowid where ids.id = <value> + // ) + // ) + StatementBuilder builder; + builder.Update(GetQualifiedName()).Set().Column(s_SearchResultsTable_Filter).Equals(true).Where(s_SearchResultsTable_Manifest).In().BeginParenthetical(). + Select(s_SearchResultsTable_SubSelect_ManifestAlias).From().BeginParenthetical(); + + // Add the field specific portion + std::optional<int> bindIndex = BuildSearchStatement(builder, field, match); + + if (!bindIndex) + { + AICLI_LOG(Repo, Verbose, << "ApplicationMatchField not supported in this version: " << ApplicationMatchFieldToString(field)); + return; + } + + builder.EndParenthetical().EndParenthetical(); + + SQLite::Statement statement = builder.Prepare(m_connection); + ExecuteStatementForMatchType(statement, match, bindIndex.value(), MatchUsesLike(match), value); + AICLI_LOG(Repo, Verbose, << "Filter kept " << m_connection.GetChanges() << " rows"); + } + + void SearchResultsTable::CompleteFilter() + { + // Delete all unselected values + SQLite::Builder::StatementBuilder builder; + builder.DeleteFrom(GetQualifiedName()).Where(s_SearchResultsTable_Filter).Equals(false); + + builder.Execute(m_connection); + AICLI_LOG(Repo, Verbose, << "Filter deleted " << m_connection.GetChanges() << " rows"); + } + + ISQLiteIndex::SearchResult 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)); + + SQLite::Statement select = builder.Prepare(m_connection); + + ISQLiteIndex::SearchResult result; + while (select.Step()) + { + if (limit && result.Matches.size() >= limit) + { + break; + } + + result.Matches.emplace_back(select.GetColumn<SQLite::rowid_t>(0), + ApplicationMatchFilter(select.GetColumn<ApplicationMatchField>(1), select.GetColumn<MatchType>(2), select.GetColumn<std::string>(3))); + } + + result.Truncated = (select.GetState() != SQLite::Statement::State::Completed); + + return result; + } + + std::optional<int> SearchResultsTable::BuildSearchStatement(SQLite::Builder::StatementBuilder& builder, ApplicationMatchField field, MatchType match) const + { + return BuildSearchStatement(builder, field, s_SearchResultsTable_SubSelect_ManifestAlias, s_SearchResultsTable_SubSelect_ValueAlias, MatchUsesLike(match)); + } + + std::optional<int> SearchResultsTable::BuildSearchStatement( + SQLite::Builder::StatementBuilder& builder, + ApplicationMatchField field, + std::string_view manifestAlias, + std::string_view valueAlias, + bool useLike) const + { + switch (field) + { + case ApplicationMatchField::Id: + return ManifestTable::BuildSearchStatement<IdTable>(builder, manifestAlias, valueAlias, useLike); + case ApplicationMatchField::Name: + return ManifestTable::BuildSearchStatement<NameTable>(builder, manifestAlias, valueAlias, useLike); + case ApplicationMatchField::Moniker: + return ManifestTable::BuildSearchStatement<MonikerTable>(builder, manifestAlias, valueAlias, useLike); + case ApplicationMatchField::Tag: + return ManifestTable::BuildSearchStatement<TagsTable>(builder, manifestAlias, valueAlias, useLike); + case ApplicationMatchField::Command: + return ManifestTable::BuildSearchStatement<CommandsTable>(builder, manifestAlias, valueAlias, useLike); + default: + return {}; + } + } +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/Interface.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/Interface.h @@ -0,0 +1,26 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "Microsoft/Schema/ISQLiteIndex.h" +#include "Microsoft/Schema/1_0/Interface.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_1 +{ + // Interface to this schema version exposed through ISQLiteIndex. + struct Interface : public V1_0::Interface + { + // Version 1.0 + Schema::Version GetVersion() const override; + void CreateTables(SQLite::Connection& connection) override; + SQLite::rowid_t AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; + std::pair<bool, SQLite::rowid_t> UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; + SQLite::rowid_t RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) override; + void PrepareForPackaging(SQLite::Connection& connection) override; + SearchResult Search(SQLite::Connection& connection, const SearchRequest& request) override; + + protected: + std::unique_ptr<V1_0::SearchResultsTable> CreateSearchResultsTable(SQLite::Connection& connection) const override; + void PerformQuerySearch(V1_0::SearchResultsTable& resultsTable, const RequestMatch& query) const override; + }; +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/Interface_1_1.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/Interface_1_1.cpp @@ -0,0 +1,219 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#include "pch.h" +#include "Microsoft/Schema/1_1/Interface.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/VersionTable.h" +#include "Microsoft/Schema/1_0/ChannelTable.h" + +#include "Microsoft/Schema/1_0/PathPartTable.h" + +#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_1/PackageFamilyNameTable.h" +#include "Microsoft/Schema/1_1/ProductCodeTable.h" + +#include "Microsoft/Schema/1_1/SearchResultsTable.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_1 +{ + namespace + { + std::vector<Utility::NormalizedString> GetSystemReferenceStrings( + const Manifest::Manifest& manifest, + std::function<const Utility::NormalizedString&(const Manifest::ManifestInstaller&)> func) + { + std::set<Utility::NormalizedString> set; + + for (const auto& installer : manifest.Installers) + { + const Utility::NormalizedString& string = func(installer); + if (!string.empty()) + { + set.emplace(Utility::FoldCase(string)); + } + } + + std::vector<Utility::NormalizedString> result; + for (auto&& string : set) + { + result.emplace_back(string); + } + + return result; + } + + std::vector<Utility::NormalizedString> GetPackageFamilyNames(const Manifest::Manifest& manifest) + { + return GetSystemReferenceStrings(manifest, [](const Manifest::ManifestInstaller& i) -> const Utility::NormalizedString& { return i.PackageFamilyName; }); + } + + std::vector<Utility::NormalizedString> GetProductCodes(const Manifest::Manifest& manifest) + { + return GetSystemReferenceStrings(manifest, [](const Manifest::ManifestInstaller& i) -> const Utility::NormalizedString& { return i.ProductCode; }); + } + } + + Schema::Version Interface::GetVersion() const + { + return { 1, 1 }; + } + + void Interface::CreateTables(SQLite::Connection& connection) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createtables_v1_1"); + + V1_0::IdTable::Create(connection); + V1_0::NameTable::Create(connection); + V1_0::MonikerTable::Create(connection); + V1_0::VersionTable::Create(connection); + V1_0::ChannelTable::Create(connection); + + V1_0::PathPartTable::Create(connection); + + V1_0::ManifestTable::Create(connection, { + { V1_0::IdTable::ValueName(), true, false }, + { V1_0::NameTable::ValueName(), false, false }, + { V1_0::MonikerTable::ValueName(), false, false }, + { V1_0::VersionTable::ValueName(), true, false }, + { V1_0::ChannelTable::ValueName(), true, false }, + { V1_0::PathPartTable::ValueName(), false, true } + }); + + V1_0::TagsTable::Create(connection); + V1_0::CommandsTable::Create(connection); + PackageFamilyNameTable::Create(connection); + ProductCodeTable::Create(connection); + + savepoint.Commit(); + } + + SQLite::rowid_t Interface::AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "addmanifest_v1_1"); + + SQLite::rowid_t manifestId = V1_0::Interface::AddManifest(connection, manifest, relativePath); + + // Add the new 1.1 data + // These system reference strings are all stored with their cases folded so that they can be + // looked up ordinally; enabling the index to provide efficient searches. + PackageFamilyNameTable::EnsureExistsAndInsert(connection, GetPackageFamilyNames(manifest), manifestId); + ProductCodeTable::EnsureExistsAndInsert(connection, GetProductCodes(manifest), manifestId); + + savepoint.Commit(); + + return manifestId; + } + + std::pair<bool, SQLite::rowid_t> Interface::UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "updatemanifest_v1_1"); + + auto [indexModified, manifestId] = V1_0::Interface::UpdateManifest(connection, manifest, relativePath); + + // Update new 1:N tables as necessary + indexModified = PackageFamilyNameTable::UpdateIfNeededByManifestId(connection, GetPackageFamilyNames(manifest), manifestId) || indexModified; + indexModified = ProductCodeTable::UpdateIfNeededByManifestId(connection, GetProductCodes(manifest), manifestId) || indexModified; + + savepoint.Commit(); + + return { indexModified, manifestId }; + } + + SQLite::rowid_t Interface::RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "removemanifest_v1_1"); + + SQLite::rowid_t manifestId = V1_0::Interface::RemoveManifest(connection, manifest, relativePath); + + // Remove all of the new 1:N data that is no longer referenced. + PackageFamilyNameTable::DeleteIfNotNeededByManifestId(connection, manifestId); + ProductCodeTable::DeleteIfNotNeededByManifestId(connection, manifestId); + + savepoint.Commit(); + + return manifestId; + } + + void Interface::PrepareForPackaging(SQLite::Connection& connection) + { + SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "prepareforpackaging_v1_1"); + + V1_0::IdTable::PrepareForPackaging(connection); + V1_0::NameTable::PrepareForPackaging(connection); + V1_0::MonikerTable::PrepareForPackaging(connection); + V1_0::VersionTable::PrepareForPackaging(connection); + V1_0::ChannelTable::PrepareForPackaging(connection); + + V1_0::PathPartTable::PrepareForPackaging(connection); + + V1_0::ManifestTable::PrepareForPackaging(connection, { + V1_0::VersionTable::ValueName(), + V1_0::ChannelTable::ValueName(), + V1_0::PathPartTable::ValueName(), + }); + + V1_0::TagsTable::PrepareForPackaging(connection); + V1_0::CommandsTable::PrepareForPackaging(connection); + PackageFamilyNameTable::PrepareForPackaging(connection, true); + ProductCodeTable::PrepareForPackaging(connection, true); + + savepoint.Commit(); + + // Force the database to actually shrink the file size. + // This *must* be done outside of an active transaction. + SQLite::Builder::StatementBuilder builder; + builder.Vacuum(); + builder.Execute(connection); + } + + ISQLiteIndex::SearchResult Interface::Search(SQLite::Connection& connection, const SearchRequest& request) + { + // Update any system reference strings to be folded + SearchRequest foldedRequest = request; + + auto foldIfNeeded = [](ApplicationMatchFilter& filter) + { + if ((filter.Field == ApplicationMatchField::PackageFamilyName || filter.Field == ApplicationMatchField::ProductCode) && + filter.Type == MatchType::Exact) + { + filter.Value = Utility::FoldCase(filter.Value); + } + }; + + for (auto& inclusion : foldedRequest.Inclusions) + { + foldIfNeeded(inclusion); + } + + for (auto& filter : foldedRequest.Filters) + { + foldIfNeeded(filter); + } + + return V1_0::Interface::Search(connection, foldedRequest); + } + + std::unique_ptr<V1_0::SearchResultsTable> Interface::CreateSearchResultsTable(SQLite::Connection& connection) const + { + return std::make_unique<SearchResultsTable>(connection); + } + + void Interface::PerformQuerySearch(V1_0::SearchResultsTable& resultsTable, const RequestMatch& query) const + { + // First, do an exact match search for the folded system reference strings + // We do this first because it is exact, and likely won't match anything else if it matches this. + std::string foldedQuery = Utility::FoldCase(query.Value); + resultsTable.SearchOnField(ApplicationMatchField::PackageFamilyName, MatchType::Exact, foldedQuery); + resultsTable.SearchOnField(ApplicationMatchField::ProductCode, MatchType::Exact, foldedQuery); + + // Then do the 1.0 search + V1_0::Interface::PerformQuerySearch(resultsTable, query); + } +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/PackageFamilyNameTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/PackageFamilyNameTable.h @@ -0,0 +1,22 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "Microsoft/Schema/1_0/OneToManyTable.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_1 +{ + namespace details + { + using namespace std::string_view_literals; + + struct PackageFamilyNameTableInfo + { + inline static constexpr std::string_view TableName() { return "pfns"sv; } + inline static constexpr std::string_view ValueName() { return "pfn"sv; } + }; + } + + // The table for PackageFamilyName. + using PackageFamilyNameTable = V1_0::OneToManyTable<details::PackageFamilyNameTableInfo>; +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/ProductCodeTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/ProductCodeTable.h @@ -0,0 +1,22 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "Microsoft/Schema/1_0/OneToManyTable.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_1 +{ + namespace details + { + using namespace std::string_view_literals; + + struct ProductCodeTableInfo + { + inline static constexpr std::string_view TableName() { return "productcodes"sv; } + inline static constexpr std::string_view ValueName() { return "productcode"sv; } + }; + } + + // The table for ProductCode. + using ProductCodeTable = V1_0::OneToManyTable<details::ProductCodeTableInfo>; +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/SearchResultsTable.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/SearchResultsTable.h @@ -0,0 +1,28 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "Microsoft/Schema/1_0/SearchResultsTable.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_1 +{ + // Table for holding temporary search results. + struct SearchResultsTable : public V1_0::SearchResultsTable + { + SearchResultsTable(SQLite::Connection& connection) : V1_0::SearchResultsTable(connection) {} + + SearchResultsTable(const SearchResultsTable&) = delete; + SearchResultsTable& operator=(const SearchResultsTable&) = delete; + + SearchResultsTable(SearchResultsTable&&) = default; + SearchResultsTable& operator=(SearchResultsTable&&) = default; + + protected: + std::optional<int> BuildSearchStatement( + SQLite::Builder::StatementBuilder& builder, + ApplicationMatchField field, + std::string_view manifestAlias, + std::string_view valueAlias, + bool useLike) const override; + }; +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/SearchResultsTable_1_1.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/1_1/SearchResultsTable_1_1.cpp @@ -0,0 +1,31 @@ +// Copyright (c) Microsoft Corporation. +// Licensed under the MIT License. +#pragma once +#include "pch.h" +#include "SearchResultsTable.h" + +#include "Microsoft/Schema/1_0/ManifestTable.h" +#include "Microsoft/Schema/1_1/PackageFamilyNameTable.h" +#include "Microsoft/Schema/1_1/ProductCodeTable.h" + + +namespace AppInstaller::Repository::Microsoft::Schema::V1_1 +{ + std::optional<int> SearchResultsTable::BuildSearchStatement( + SQLite::Builder::StatementBuilder& builder, + ApplicationMatchField field, + std::string_view manifestAlias, + std::string_view valueAlias, + bool useLike) const + { + switch (field) + { + case ApplicationMatchField::PackageFamilyName: + return V1_0::ManifestTable::BuildSearchStatement<PackageFamilyNameTable>(builder, manifestAlias, valueAlias, useLike); + case ApplicationMatchField::ProductCode: + return V1_0::ManifestTable::BuildSearchStatement<ProductCodeTable>(builder, manifestAlias, valueAlias, useLike); + default: + return V1_0::SearchResultsTable::BuildSearchStatement(builder, field, manifestAlias, valueAlias, useLike); + } + } +} diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/ISQLiteIndex.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/ISQLiteIndex.h @@ -37,15 +37,15 @@ namespace AppInstaller::Repository::Microsoft::Schema virtual void CreateTables(SQLite::Connection& connection) = 0; // Adds the manifest at the repository relative path to the index. - virtual void AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) = 0; + virtual SQLite::rowid_t AddManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) = 0; // Updates the manifest with matching { Id, Version, Channel } in the index. // The return value indicates whether the index was modified by the function. - virtual bool UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) = 0; + virtual std::pair<bool, SQLite::rowid_t> UpdateManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) = 0; // Removes the manifest with matching { Id, Version, Channel } from the index. // Path is currently ignored. - virtual void RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) = 0; + virtual SQLite::rowid_t RemoveManifest(SQLite::Connection& connection, const Manifest::Manifest& manifest, const std::filesystem::path& relativePath) = 0; // Removes data that is no longer needed for an index that is to be published. virtual void PrepareForPackaging(SQLite::Connection& connection) = 0; @@ -66,10 +66,4 @@ namespace AppInstaller::Repository::Microsoft::Schema // Gets all versions and channels for the given id. virtual std::vector<Utility::VersionAndChannel> GetVersionsById(SQLite::Connection& connection, SQLite::rowid_t id) = 0; }; - - - // Common base class used by all schema versions. - struct SQLiteIndexBase : public ISQLiteIndex - { - }; } diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/Version.cpp b/src/AppInstallerRepositoryCore/Microsoft/Schema/Version.cpp @@ -5,6 +5,7 @@ #include "MetadataTable.h" #include "1_0/Interface.h" +#include "1_1/Interface.h" namespace AppInstaller::Repository::Microsoft::Schema { @@ -27,21 +28,25 @@ namespace AppInstaller::Repository::Microsoft::Schema } // Creates the interface object for this version. - std::unique_ptr<ISQLiteIndex> Version::CreateISQLiteIndex() + std::unique_ptr<ISQLiteIndex> Version::CreateISQLiteIndex() const { - if (*this == Version{ 1, 0 } || + if (*this == Version{ 1, 0 }) + { + return std::make_unique<V1_0::Interface>(); + } + else if (*this == Version{ 1, 1 } || this->MajorVersion == 1 || this->IsLatest()) { - return std::make_unique<V1_0::Interface>(); + return std::make_unique<V1_1::Interface>(); } // We do not have the capacity to operate on this schema version THROW_HR(HRESULT_FROM_WIN32(ERROR_NOT_SUPPORTED)); } -} -std::ostream& operator<<(std::ostream& out, const AppInstaller::Repository::Microsoft::Schema::Version& version) -{ - return (out << version.MajorVersion << '.' << version.MinorVersion); + std::ostream& operator<<(std::ostream& out, const Version& version) + { + return (out << version.MajorVersion << '.' << version.MinorVersion); + } } diff --git a/src/AppInstallerRepositoryCore/Microsoft/Schema/Version.h b/src/AppInstallerRepositoryCore/Microsoft/Schema/Version.h @@ -22,16 +22,23 @@ namespace AppInstaller::Repository::Microsoft::Schema // All changes to the schema warrant a change to the minor version. uint32_t MinorVersion{}; - bool operator==(Version other) const + bool operator==(const Version& other) const { return (MajorVersion == other.MajorVersion && MinorVersion == other.MinorVersion); } - bool operator!=(Version other) const + bool operator!=(const Version& other) const { return !operator==(other); } + bool operator>=(const Version& other) const + { + if (MajorVersion > other.MajorVersion) return true; + if (MajorVersion < other.MajorVersion) return false; + return MinorVersion >= other.MinorVersion; + } + // Gets a version that represents the latest schema known to the implementation. static constexpr Version Latest() { return { std::numeric_limits<uint32_t>::max(), std::numeric_limits<uint32_t>::max() }; } @@ -51,9 +58,9 @@ namespace AppInstaller::Repository::Microsoft::Schema void SetSchemaVersion(SQLite::Connection& connection); // Creates the interface object for this version. - std::unique_ptr<ISQLiteIndex> CreateISQLiteIndex(); + std::unique_ptr<ISQLiteIndex> CreateISQLiteIndex() const; }; -} -// Output the version -std::ostream& operator<<(std::ostream& out, const AppInstaller::Repository::Microsoft::Schema::Version& version); + // Output the version + std::ostream& operator<<(std::ostream& out, const Version& version); +} diff --git a/src/AppInstallerRepositoryCore/Public/AppInstallerRepositorySearch.h b/src/AppInstallerRepositoryCore/Public/AppInstallerRepositorySearch.h @@ -37,6 +37,8 @@ namespace AppInstaller::Repository Moniker, Command, Tag, + PackageFamilyName, + ProductCode, }; // A single match to be performed during a search. @@ -170,6 +172,10 @@ namespace AppInstaller::Repository return "Name"sv; case ApplicationMatchField::Tag: return "Tag"sv; + case ApplicationMatchField::PackageFamilyName: + return "PackageFamilyName"sv; + case ApplicationMatchField::ProductCode: + return "ProductCode"sv; } return "UnknownMatchField"sv; diff --git a/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.cpp b/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.cpp @@ -598,6 +598,24 @@ namespace AppInstaller::Repository::SQLite::Builder return *this; } + StatementBuilder& StatementBuilder::CreateUniqueIndex(std::string_view table) + { + OutputOperationAndTable(m_stream, "CREATE UNIQUE INDEX", table); + return *this; + } + + StatementBuilder& StatementBuilder::CreateUniqueIndex(QualifiedTable table) + { + OutputOperationAndTable(m_stream, "CREATE UNIQUE INDEX", table); + return *this; + } + + StatementBuilder& StatementBuilder::CreateUniqueIndex(std::initializer_list<std::string_view> table) + { + OutputOperationAndTable(m_stream, "CREATE UNIQUE INDEX", table); + return *this; + } + StatementBuilder& StatementBuilder::DropIndex(std::string_view table) { OutputOperationAndTable(m_stream, "DROP INDEX", table); diff --git a/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.h b/src/AppInstallerRepositoryCore/SQLiteStatementBuilder.h @@ -291,11 +291,17 @@ namespace AppInstaller::Repository::SQLite::Builder 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. + // The initializer_list form enables the index name to be constructed from multiple parts. StatementBuilder& CreateIndex(std::string_view table); StatementBuilder& CreateIndex(QualifiedTable table); StatementBuilder& CreateIndex(std::initializer_list<std::string_view> table); + // Begin an unique index creation statement. + // The initializer_list form enables the index name to be constructed from multiple parts. + StatementBuilder& CreateUniqueIndex(std::string_view table); + StatementBuilder& CreateUniqueIndex(QualifiedTable table); + StatementBuilder& CreateUniqueIndex(std::initializer_list<std::string_view> table); + // Begin an index deletion statement. // The initializer_list form enables the table name to be constructed from multiple parts. StatementBuilder& DropIndex(std::string_view table); diff --git a/src/AppInstallerRepositoryCore/SQLiteWrapper.cpp b/src/AppInstallerRepositoryCore/SQLiteWrapper.cpp @@ -8,7 +8,13 @@ using namespace std::string_view_literals; -// TODO: Invoke the wil error handling callback to log the error +// Enable this to have all Statement constructions output the associated query plan. +#define WINGET_SQLITE_EXPLAIN_QUERY_PLAN_ENABLED 0 + +#if WINGET_SQLITE_EXPLAIN_QUERY_PLAN_ENABLED +#include <stack> +#endif + #define THROW_SQLITE(_error_) \ do { \ int _ts_sqliteReturnValue = _error_; \ @@ -131,19 +137,61 @@ namespace AppInstaller::Repository::SQLite THROW_IF_SQLITE_FAILED(sqlite3_prepare_v2(connection, sql.data(), static_cast<int>(sql.size() + 1), &m_stmt, nullptr)); } +#if WINGET_SQLITE_EXPLAIN_QUERY_PLAN_ENABLED +#define WINGET_SQLITE_EXPLAIN_QUERY_PLAN(_connection_,_sql_) \ + std::string _explainStatementSQL_ = "EXPLAIN QUERY PLAN "; \ + _explainStatementSQL_.append(_sql_); \ + try { \ + Statement _explainStatement_(_connection_,_explainStatementSQL_); \ + LogExplainQueryPlanResult(_sql_, _explainStatement_); \ + } catch(...) {} + + void LogExplainQueryPlanResult(std::string_view sql, Statement& plan) + { + bool outputHeader = true; + std::stack<int> parents; + + while (plan.Step()) + { + if (outputHeader) + { + AICLI_LOG(SQL, Info, << "Query plan for: " << sql); + outputHeader = false; + } + + int id = plan.GetColumn<int>(0); + int parent = plan.GetColumn<int>(1); + + while (!parents.empty() && parents.top() != parent) + { + parents.pop(); + } + + AICLI_LOG(SQL, Info, << "|-" << std::string(parents.size() * 2, '-') << ' ' << plan.GetColumn<std::string>(3)); + + parents.push(id); + } + } +#else +#define WINGET_SQLITE_EXPLAIN_QUERY_PLAN(_connection_,_sql_) +#endif + Statement Statement::Create(Connection& connection, const std::string& sql) { + WINGET_SQLITE_EXPLAIN_QUERY_PLAN(connection, sql); return { connection, { sql.c_str(), sql.size() } }; } Statement Statement::Create(Connection& connection, std::string_view sql) { + WINGET_SQLITE_EXPLAIN_QUERY_PLAN(connection, sql); // We need the statement to be null terminated, and the only way to guarantee that with a string_view is to construct a string copy. return Create(connection, std::string(sql)); } Statement Statement::Create(Connection& connection, char const* const sql) { + WINGET_SQLITE_EXPLAIN_QUERY_PLAN(connection, sql); return { connection, sql }; } diff --git a/src/AppInstallerRepositoryCore/pch.h b/src/AppInstallerRepositoryCore/pch.h @@ -40,6 +40,7 @@ #include <initializer_list> #include <iomanip> #include <optional> +#include <set> #include <string> #include <string_view> #include <sstream>