OneToManyTable.cpp (21776B)
1 // Copyright (c) Microsoft Corporation. 2 // Licensed under the MIT License. 3 #include "pch.h" 4 #include "Microsoft/Schema/1_0/OneToManyTable.h" 5 #include "Microsoft/Schema/1_0/OneToOneTable.h" 6 #include "Microsoft/Schema/1_0/ManifestTable.h" 7 #include "Microsoft/Schema/1_0/IdTable.h" 8 #include <winget/SQLiteStatementBuilder.h> 9 10 11 namespace AppInstaller::Repository::Microsoft::Schema::V1_0 12 { 13 namespace details 14 { 15 using namespace std::string_view_literals; 16 static constexpr std::string_view s_OneToManyTable_MapTable_ManifestName = "manifest"sv; 17 static constexpr std::string_view s_OneToManyTable_MapTable_Suffix = "_map"sv; 18 static constexpr std::string_view s_OneToManyTable_MapTable_PrimaryKeyIndexSuffix = "_pkindex"sv; 19 static constexpr std::string_view s_OneToManyTable_MapTable_IndexSuffix = "_index"sv; 20 21 namespace 22 { 23 // Create the mapping table insert statement for multiple use. 24 // Bind the rowid of the value to 2. 25 SQLite::Statement CreateMappingInsertStatementForManifestId(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t manifestId) 26 { 27 SQLite::Builder::StatementBuilder insertMappingBuilder; 28 insertMappingBuilder.InsertInto({ tableName, s_OneToManyTable_MapTable_Suffix }). 29 Columns({ s_OneToManyTable_MapTable_ManifestName, valueName }).Values(manifestId, SQLite::Builder::Unbound); 30 31 return insertMappingBuilder.Prepare(connection); 32 } 33 34 // Get a collection of the value ids associated with the given manifest id. 35 std::vector<SQLite::rowid_t> GetValueIdsByManifestId(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t manifestId) 36 { 37 std::vector<SQLite::rowid_t> result; 38 39 SQLite::Builder::StatementBuilder selectMappingBuilder; 40 selectMappingBuilder.Select(valueName).From({ tableName, s_OneToManyTable_MapTable_Suffix }).Where(s_OneToManyTable_MapTable_ManifestName).Equals(manifestId); 41 42 SQLite::Statement selectMappingStatement = selectMappingBuilder.Prepare(connection); 43 44 while (selectMappingStatement.Step()) 45 { 46 result.push_back(selectMappingStatement.GetColumn<SQLite::rowid_t>(0)); 47 } 48 49 return result; 50 } 51 52 struct DeleteValueIfNotNeededStatements 53 { 54 DeleteValueIfNotNeededStatements(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName) 55 { 56 SQLite::Builder::StatementBuilder selectValueMappingBuilder; 57 selectValueMappingBuilder.Select(s_OneToManyTable_MapTable_ManifestName).From({ tableName, s_OneToManyTable_MapTable_Suffix }).Where(valueName).Equals(SQLite::Builder::Unbound).Limit(1); 58 59 SelectIfAnyMappingsByValueId = selectValueMappingBuilder.Prepare(connection); 60 61 SQLite::Builder::StatementBuilder deleteValueBuilder; 62 deleteValueBuilder.DeleteFrom(tableName).Where(SQLite::RowIDName).Equals(SQLite::Builder::Unbound); 63 64 DeleteValueById = deleteValueBuilder.Prepare(connection); 65 } 66 67 void Execute(SQLite::rowid_t valueId) 68 { 69 SelectIfAnyMappingsByValueId.Reset(); 70 SelectIfAnyMappingsByValueId.Bind(1, valueId); 71 72 // If no rows are found, we can delete the data. 73 if (!SelectIfAnyMappingsByValueId.Step()) 74 { 75 DeleteValueById.Reset(); 76 DeleteValueById.Bind(1, valueId); 77 78 DeleteValueById.Execute(); 79 } 80 } 81 82 private: 83 // Bind valid rowid to 1. 84 SQLite::Statement SelectIfAnyMappingsByValueId; 85 // Bind valid rowid to 1. 86 SQLite::Statement DeleteValueById; 87 }; 88 89 bool SchemaVersionUsesNamedIndices(OneToManyTableSchema schemaVersion) 90 { 91 return schemaVersion != OneToManyTableSchema::Version_1_0; 92 } 93 } 94 95 std::string OneToManyTableGetMapTableName(std::string_view tableName) 96 { 97 std::string result(tableName); 98 result += s_OneToManyTable_MapTable_Suffix; 99 return result; 100 } 101 102 std::string_view OneToManyTableGetManifestColumnName() 103 { 104 return s_OneToManyTable_MapTable_ManifestName; 105 } 106 107 void CreateOneToManyTable(SQLite::Connection& connection, OneToManyTableSchema schemaVersion, std::string_view tableName, std::string_view valueName) 108 { 109 using namespace SQLite::Builder; 110 111 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_create_v1_0"); 112 113 // Create the data table as a 1:1 114 CreateOneToOneTable(connection, tableName, valueName, SchemaVersionUsesNamedIndices(schemaVersion)); 115 116 switch (schemaVersion) 117 { 118 case OneToManyTableSchema::Version_1_0: 119 { 120 // Create the mapping table 121 StatementBuilder createMapTableBuilder; 122 createMapTableBuilder.CreateTable({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ 123 ColumnBuilder(s_OneToManyTable_MapTable_ManifestName, Type::Int64).NotNull(), 124 ColumnBuilder(valueName, Type::Int64).NotNull(), 125 PrimaryKeyBuilder({ valueName, s_OneToManyTable_MapTable_ManifestName }) 126 }); 127 128 createMapTableBuilder.Execute(connection); 129 } 130 break; 131 case OneToManyTableSchema::Version_1_1: 132 { 133 // Create the mapping table 134 StatementBuilder createMapTableBuilder; 135 createMapTableBuilder.CreateTable({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ 136 ColumnBuilder(s_OneToManyTable_MapTable_ManifestName, Type::Int64).NotNull(), 137 ColumnBuilder(valueName, Type::Int64).NotNull() 138 }); 139 140 createMapTableBuilder.Execute(connection); 141 142 StatementBuilder pkIndexBuilder; 143 pkIndexBuilder.CreateUniqueIndex({ tableName, s_OneToManyTable_MapTable_Suffix, s_OneToManyTable_MapTable_PrimaryKeyIndexSuffix }). 144 On({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ valueName, s_OneToManyTable_MapTable_ManifestName }); 145 pkIndexBuilder.Execute(connection); 146 } 147 break; 148 case OneToManyTableSchema::Version_1_7: 149 { 150 // Create the mapping table 151 StatementBuilder createMapTableBuilder; 152 createMapTableBuilder.CreateTable({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ 153 ColumnBuilder(s_OneToManyTable_MapTable_ManifestName, Type::Int64).NotNull(), 154 ColumnBuilder(valueName, Type::Int64).NotNull(), 155 PrimaryKeyBuilder({ valueName, s_OneToManyTable_MapTable_ManifestName }) 156 }).WithoutRowID(); 157 158 createMapTableBuilder.Execute(connection); 159 } 160 break; 161 default: 162 THROW_HR(E_UNEXPECTED); 163 } 164 165 StatementBuilder createMapTableIndexBuilder; 166 createMapTableIndexBuilder.CreateIndex({ tableName, s_OneToManyTable_MapTable_Suffix, s_OneToManyTable_MapTable_IndexSuffix }). 167 On({ tableName, s_OneToManyTable_MapTable_Suffix }).Columns({ s_OneToManyTable_MapTable_ManifestName, valueName }); 168 169 createMapTableIndexBuilder.Execute(connection); 170 171 savepoint.Commit(); 172 } 173 174 void DropOneToManyTable(SQLite::Connection& connection, std::string_view tableName) 175 { 176 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_drop_v1_0"); 177 178 DropOneToOneTable(connection, tableName); 179 180 SQLite::Builder::StatementBuilder dropTableBuilder; 181 dropTableBuilder.DropTable({ tableName, s_OneToManyTable_MapTable_Suffix }); 182 183 dropTableBuilder.Execute(connection); 184 185 savepoint.Commit(); 186 } 187 188 std::vector<std::string> OneToManyTableGetValuesByManifestId( 189 const SQLite::Connection& connection, 190 std::string_view tableName, 191 std::string_view valueName, 192 SQLite::rowid_t manifestId) 193 { 194 using QCol = SQLite::Builder::QualifiedColumn; 195 196 std::vector<std::string> result; 197 198 SQLite::Builder::StatementBuilder builder; 199 builder.Select(QCol(tableName, valueName)). 200 From({ tableName, s_OneToManyTable_MapTable_Suffix }).As("map").Join(tableName). 201 On(QCol("map", valueName), QCol(tableName, SQLite::RowIDName)).Where(QCol("map", s_OneToManyTable_MapTable_ManifestName)).Equals(manifestId); 202 203 SQLite::Statement statement = builder.Prepare(connection); 204 205 while (statement.Step()) 206 { 207 result.emplace_back(statement.GetColumn<std::string>(0)); 208 } 209 210 return result; 211 } 212 213 void OneToManyTableEnsureExistsAndInsert(SQLite::Connection& connection, 214 std::string_view tableName, std::string_view valueName, 215 const std::vector<Utility::NormalizedString>& values, SQLite::rowid_t manifestId) 216 { 217 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_ensureandinsert_v1_0"); 218 219 SQLite::Statement insertMapping = CreateMappingInsertStatementForManifestId(connection, tableName, valueName, manifestId); 220 221 for (const std::string& value : values) 222 { 223 // First, ensure that the data exists 224 SQLite::rowid_t dataId = OneToOneTableEnsureExists(connection, tableName, valueName, value); 225 226 // Second, insert into the mapping table 227 insertMapping.Reset(); 228 insertMapping.Bind(2, dataId); 229 230 insertMapping.Execute(); 231 } 232 233 savepoint.Commit(); 234 } 235 236 bool OneToManyTableUpdateIfNeededByManifestId(SQLite::Connection& connection, 237 std::string_view tableName, std::string_view valueName, 238 const std::vector<Utility::NormalizedString>& values, SQLite::rowid_t manifestId) 239 { 240 std::vector<SQLite::rowid_t> oldValueIds = GetValueIdsByManifestId(connection, tableName, valueName, manifestId); 241 bool modificationNeeded = false; 242 243 SQLite::Statement insertMapping = CreateMappingInsertStatementForManifestId(connection, tableName, valueName, manifestId); 244 245 for (const std::string& value : values) 246 { 247 SQLite::rowid_t valueId = OneToOneTableEnsureExists(connection, tableName, valueName, value); 248 249 auto itr = std::find(oldValueIds.begin(), oldValueIds.end(), valueId); 250 if (itr != oldValueIds.end()) 251 { 252 oldValueIds.erase(itr); 253 } 254 else 255 { 256 modificationNeeded = true; 257 258 insertMapping.Reset(); 259 insertMapping.Bind(2, valueId); 260 261 insertMapping.Execute(); 262 } 263 } 264 265 // All incoming values are now present, we just need to delete the remaining old ones. 266 SQLite::Builder::StatementBuilder deleteBuilder; 267 deleteBuilder.DeleteFrom({ tableName, s_OneToManyTable_MapTable_Suffix }). 268 Where(s_OneToManyTable_MapTable_ManifestName).Equals(manifestId).And(valueName).Equals(SQLite::Builder::Unbound); 269 270 SQLite::Statement deleteStatement = deleteBuilder.Prepare(connection); 271 272 DeleteValueIfNotNeededStatements dvinns(connection, tableName, valueName); 273 274 for (SQLite::rowid_t valueId : oldValueIds) 275 { 276 modificationNeeded = true; 277 278 // First, delete the mapping 279 deleteStatement.Reset(); 280 deleteStatement.Bind(2, valueId); 281 282 deleteStatement.Execute(); 283 284 // Second, delete the value itself if not needed 285 dvinns.Execute(valueId); 286 } 287 288 return modificationNeeded; 289 } 290 291 void OneToManyTableDeleteIfNotNeededByManifestId(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t manifestId) 292 { 293 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_deleteifnotneeded_v1_0"); 294 295 // Get values referenced by the manifest id. 296 std::vector<SQLite::rowid_t> values = GetValueIdsByManifestId(connection, tableName, valueName, manifestId); 297 298 // Delete the mapping table rows with the manifest id. 299 SQLite::Builder::StatementBuilder deleteBuilder; 300 deleteBuilder.DeleteFrom({ tableName, s_OneToManyTable_MapTable_Suffix }).Where(s_OneToManyTable_MapTable_ManifestName).Equals(manifestId); 301 302 deleteBuilder.Execute(connection); 303 304 // For each value, see if any references exist 305 DeleteValueIfNotNeededStatements dvinns(connection, tableName, valueName); 306 307 for (SQLite::rowid_t value : values) 308 { 309 dvinns.Execute(value); 310 } 311 312 savepoint.Commit(); 313 } 314 315 void OneToManyTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName, OneToManyTableSchema schemaVersion, bool preserveManifestIndex, bool preserveValuesIndex) 316 { 317 if (!preserveManifestIndex) 318 { 319 SQLite::Builder::StatementBuilder dropMapTableIndexBuilder; 320 dropMapTableIndexBuilder.DropIndex({ tableName, s_OneToManyTable_MapTable_Suffix, s_OneToManyTable_MapTable_IndexSuffix }); 321 322 dropMapTableIndexBuilder.Execute(connection); 323 } 324 325 OneToOneTablePrepareForPackaging(connection, tableName, SchemaVersionUsesNamedIndices(schemaVersion), preserveValuesIndex); 326 } 327 328 bool OneToManyTableCheckConsistency(const SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, bool log) 329 { 330 using QCol = SQLite::Builder::QualifiedColumn; 331 constexpr std::string_view s_map = "map"sv; 332 333 bool result = true; 334 335 { 336 // Build a select statement to find map rows containing references to manifests with nonexistent rowids 337 // Such as: 338 // Select map.rowid, map.manifest from tags_map as map left outer join manifest on map.manifest = manifest.rowid where manifest.id is null 339 340 SQLite::Builder::StatementBuilder builder; 341 builder. 342 Select({ QCol(s_map, s_OneToManyTable_MapTable_ManifestName), QCol(s_map, valueName) }). 343 From({ tableName, s_OneToManyTable_MapTable_Suffix }).As(s_map). 344 LeftOuterJoin(ManifestTable::TableName()).On(QCol(s_map, s_OneToManyTable_MapTable_ManifestName), QCol(ManifestTable::TableName(), SQLite::RowIDName)). 345 Where(QCol(ManifestTable::TableName(), SQLite::RowIDName)).IsNull(); 346 347 SQLite::Statement select = builder.Prepare(connection); 348 349 while (select.Step()) 350 { 351 result = false; 352 353 if (!log) 354 { 355 break; 356 } 357 358 AICLI_LOG(Repo, Info, << " [INVALID] " << tableName << s_OneToManyTable_MapTable_Suffix << " [" << select.GetColumn<SQLite::rowid_t>(0) << 359 ", " << select.GetColumn<SQLite::rowid_t>(1) << "] refers to invalid " << ManifestTable::TableName()); 360 } 361 } 362 363 if (!result && !log) 364 { 365 return result; 366 } 367 368 { 369 // Build a select statement to find map rows containing references to 1:1 tables with nonexistent rowids 370 // Such as: 371 // Select map.rowid, map.tag from tags_map as map left outer join tags on map.tag = tags.rowid where tags.tag is null 372 SQLite::Builder::StatementBuilder builder; 373 builder. 374 Select({ QCol(s_map, s_OneToManyTable_MapTable_ManifestName), QCol(s_map, valueName) }). 375 From({ tableName, s_OneToManyTable_MapTable_Suffix }).As(s_map). 376 LeftOuterJoin(tableName).On(QCol(s_map, valueName), QCol(tableName, SQLite::RowIDName)). 377 Where(QCol(tableName, valueName)).IsNull(); 378 379 SQLite::Statement select = builder.Prepare(connection); 380 bool secondaryResult = true; 381 382 while (select.Step()) 383 { 384 secondaryResult = false; 385 386 if (!log) 387 { 388 break; 389 } 390 391 AICLI_LOG(Repo, Info, << " [INVALID] " << tableName << s_OneToManyTable_MapTable_Suffix << " [" << select.GetColumn<SQLite::rowid_t>(0) << 392 ", " << select.GetColumn<SQLite::rowid_t>(1) << "] refers to invalid " << tableName); 393 } 394 395 result = result && secondaryResult; 396 } 397 398 if (!result && !log) 399 { 400 return result; 401 } 402 403 result = OneToOneTableCheckConsistency(connection, tableName, valueName, log) && result; 404 405 return result; 406 } 407 408 bool OneToManyTableIsEmpty(SQLite::Connection& connection, std::string_view tableName) 409 { 410 SQLite::Builder::StatementBuilder countBuilder; 411 countBuilder.Select(SQLite::Builder::RowCount).From(tableName); 412 413 SQLite::Statement countStatement = countBuilder.Prepare(connection); 414 415 THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); 416 417 SQLite::Builder::StatementBuilder countMapBuilder; 418 countMapBuilder.Select(SQLite::Builder::RowCount).From({ tableName, s_OneToManyTable_MapTable_Suffix }); 419 420 SQLite::Statement countMapStatement = countMapBuilder.Prepare(connection); 421 422 THROW_HR_IF(E_UNEXPECTED, !countMapStatement.Step()); 423 424 return ((countStatement.GetColumn<int>(0) == 0) && (countMapStatement.GetColumn<int>(0) == 0)); 425 } 426 427 SQLite::Statement OneToManyTablePrepareMapDataFoldingStatement(const SQLite::Connection& connection, std::string_view tableName) 428 { 429 using namespace SQLite::Builder; 430 StatementBuilder builder; 431 432 // Create a statement that will collapse (and dedupe) all rows in the map to the latest (max) manifest for a given id, like: 433 // UPDATE OR REPLACE map SET manifest = (SELECT MAX(rowid) FROM manifest_table WHERE id = ?1) WHERE manifest IN (SELECT rowid FROM manifest_table WHERE id = ?1) 434 builder.UpdateOrReplace({ tableName, s_OneToManyTable_MapTable_Suffix }).Set().Column(s_OneToManyTable_MapTable_ManifestName).Equals() 435 .BeginParenthetical() 436 .Select().Column(Aggregate::Max, SQLite::RowIDName).From(ManifestTable::TableName()).Where(IdTable::ValueName()).Equals(Unbound, 1) 437 .EndParenthetical() 438 .Where(s_OneToManyTable_MapTable_ManifestName).In() 439 .BeginParenthetical() 440 .Select(SQLite::RowIDName).From(ManifestTable::TableName()).Where(IdTable::ValueName()).Equals(Unbound, 1) 441 .EndParenthetical(); 442 443 return builder.Prepare(connection); 444 } 445 } 446 447 std::optional<SQLite::rowid_t> OneToManyTableGetMapDataFoldingManifestTargetId(const SQLite::Connection& connection, SQLite::rowid_t manifestId) 448 { 449 using namespace SQLite::Builder; 450 StatementBuilder builder; 451 452 // Select the maximum manifest rowid from the manifests whose id is the same as the row with the given manifest rowid, like: 453 // SELECT MAX(rowid) FROM manifest_table WHERE id = (SELECT id FROM manifest_table WHERE rowid = ?) 454 builder.Select().Column(Aggregate::Max, SQLite::RowIDName).From(ManifestTable::TableName()).Where(IdTable::ValueName()).Equals() 455 .BeginParenthetical() 456 .Select(IdTable::ValueName()).From(ManifestTable::TableName()).Where(SQLite::RowIDName).Equals(manifestId) 457 .EndParenthetical(); 458 459 SQLite::Statement statement = builder.Prepare(connection); 460 461 if (statement.Step() && !statement.GetColumnIsNull(0)) 462 { 463 return statement.GetColumn<SQLite::rowid_t>(0); 464 } 465 466 return std::nullopt; 467 } 468 }