ManifestTable.cpp (20372B)
1 // Copyright (c) Microsoft Corporation. 2 // Licensed under the MIT License. 3 #include "pch.h" 4 #include "ManifestTable.h" 5 #include <winget/SQLiteStatementBuilder.h> 6 #include "OneToManyTable.h" 7 8 9 namespace AppInstaller::Repository::Microsoft::Schema::V1_0 10 { 11 using namespace std::string_view_literals; 12 static constexpr std::string_view s_ManifestTable_Table_Name = "manifest"sv; 13 static constexpr std::string_view s_ManifestTable_Index_Separator = "_"sv; 14 static constexpr std::string_view s_ManifestTable_Index_Suffix = "_index"sv; 15 16 namespace details 17 { 18 std::optional<SQLite::rowid_t> ManifestTableSelectByValueIds( 19 const SQLite::Connection& connection, 20 std::initializer_list<std::string_view> values, 21 std::initializer_list<SQLite::rowid_t> ids) 22 { 23 THROW_HR_IF(E_INVALIDARG, values.size() != ids.size()); 24 25 SQLite::Builder::StatementBuilder builder; 26 builder.Select(SQLite::RowIDName).From(s_ManifestTable_Table_Name); 27 28 bool isFirst = true; 29 30 for (const auto& value : values) 31 { 32 if (isFirst) 33 { 34 builder.Where(value).Equals(SQLite::Builder::Unbound); 35 isFirst = false; 36 } 37 else 38 { 39 builder.And(value).Equals(SQLite::Builder::Unbound); 40 } 41 } 42 43 builder.Limit(1); 44 45 SQLite::Statement select = builder.Prepare(connection); 46 47 int bindIndex = 0; 48 for (const auto& id : ids) 49 { 50 select.Bind(++bindIndex, id); 51 } 52 53 if (select.Step()) 54 { 55 return select.GetColumn<SQLite::rowid_t>(0); 56 } 57 else 58 { 59 return {}; 60 } 61 } 62 63 SQLite::Statement ManifestTableGetIdsById_Statement( 64 const SQLite::Connection& connection, 65 SQLite::rowid_t id, 66 std::initializer_list<std::string_view> values, 67 bool stepAndVerify) 68 { 69 SQLite::Builder::StatementBuilder builder; 70 builder.Select(values).From(s_ManifestTable_Table_Name).Where(SQLite::RowIDName).Equals(id); 71 72 SQLite::Statement result = builder.Prepare(connection); 73 74 if (stepAndVerify) 75 { 76 THROW_HR_IF(E_NOT_SET, !result.Step()); 77 } 78 79 return result; 80 } 81 82 // Creates a statement and executes it, select the actual values for a given manifest id. 83 // Ex. 84 // SELECT [ids].[id] FROM [manifest] 85 // JOIN [ids] ON [manifest].[id] = [ids].[rowid] 86 // WHERE [manifest].[rowid] = 1 87 SQLite::Statement ManifestTableGetValuesById_Statement( 88 const SQLite::Connection& connection, 89 SQLite::rowid_t id, 90 std::initializer_list<SQLite::Builder::QualifiedColumn> columns, 91 std::initializer_list<std::string_view> manifestColumnNames, 92 bool stepAndVerify) 93 { 94 THROW_HR_IF(E_UNEXPECTED, manifestColumnNames.size() != columns.size()); 95 96 using QCol = SQLite::Builder::QualifiedColumn; 97 98 SQLite::Builder::StatementBuilder builder; 99 builder.Select(columns).From(s_ManifestTable_Table_Name); 100 101 // join tables 102 auto columnItr = columns.begin(); 103 auto manifestColumnNameItr = manifestColumnNames.begin(); 104 while (columnItr != columns.end()) 105 { 106 builder.Join(columnItr->Table).On(QCol{ s_ManifestTable_Table_Name, *manifestColumnNameItr }, QCol{ columnItr->Table, SQLite::RowIDName }); 107 108 columnItr++; 109 manifestColumnNameItr++; 110 } 111 112 builder.Where(QCol{ s_ManifestTable_Table_Name, SQLite::RowIDName }).Equals(id); 113 114 SQLite::Statement result = builder.Prepare(connection); 115 116 if (stepAndVerify) 117 { 118 THROW_HR_IF(E_NOT_SET, !result.Step()); 119 } 120 121 return result; 122 } 123 124 SQLite::Statement ManifestTableGetAllValuesByIds_Statement( 125 const SQLite::Connection& connection, 126 std::initializer_list<SQLite::Builder::QualifiedColumn> valueColumns, 127 std::initializer_list<SQLite::Builder::QualifiedColumn> joinColumns, 128 std::initializer_list<std::string_view> idColumns, 129 std::initializer_list<SQLite::rowid_t> ids) 130 { 131 using QCol = SQLite::Builder::QualifiedColumn; 132 133 THROW_HR_IF(E_INVALIDARG, idColumns.size() != ids.size()); 134 135 SQLite::Builder::StatementBuilder builder; 136 builder.Select(valueColumns).From(s_ManifestTable_Table_Name); 137 138 for (const auto& joinColumn : joinColumns) 139 { 140 builder.Join(joinColumn.Table).On(QCol{ s_ManifestTable_Table_Name, joinColumn.Column }, QCol{ joinColumn.Table, SQLite::RowIDName }); 141 } 142 143 bool isFirst = true; 144 145 for (const auto& idColumn : idColumns) 146 { 147 if (isFirst) 148 { 149 builder.Where(idColumn).Equals(SQLite::Builder::Unbound); 150 isFirst = false; 151 } 152 else 153 { 154 builder.And(idColumn).Equals(SQLite::Builder::Unbound); 155 } 156 } 157 158 SQLite::Statement select = builder.Prepare(connection); 159 160 int bindIndex = 0; 161 for (const auto& id : ids) 162 { 163 select.Bind(++bindIndex, id); 164 } 165 166 return select; 167 } 168 169 std::vector<std::pair<SQLite::rowid_t, std::string>> ManifestTableGetAllValuesByIds( 170 const SQLite::Connection& connection, 171 std::initializer_list<SQLite::Builder::QualifiedColumn> valueColumns, 172 std::initializer_list<SQLite::Builder::QualifiedColumn> joinColumns, 173 std::initializer_list<std::string_view> idColumns, 174 std::initializer_list<SQLite::rowid_t> ids) 175 { 176 auto select = ManifestTableGetAllValuesByIds_Statement(connection, valueColumns, joinColumns, idColumns, ids); 177 178 std::vector<std::pair<SQLite::rowid_t, std::string>> result; 179 while (select.Step()) 180 { 181 result.emplace_back(select.GetColumn<SQLite::rowid_t>(0), select.GetColumn<std::string>(1)); 182 } 183 return result; 184 } 185 186 std::vector<int> ManifestTableBuildSearchStatement( 187 SQLite::Builder::StatementBuilder& builder, 188 std::initializer_list<SQLite::Builder::QualifiedColumn> columns, 189 std::initializer_list<bool> isOneToOnes, 190 std::string_view manifestAlias, 191 std::string_view valueAlias, 192 bool useLike) 193 { 194 using QCol = SQLite::Builder::QualifiedColumn; 195 196 // Build a statement like: 197 // SELECT manifest.rowid as m, ids.id as v from manifest 198 // join ids on manifest.id = ids.rowid 199 // where ids.id = <value> 200 // OR 201 // SELECT manifest.rowid as m, tags.tag as v from manifest 202 // join tags_map on manifest.rowid = tags_map.manifest 203 // join tags on tags_map.tag = tags.rowid 204 // where tags.tag = <value> 205 // Where the joins and where portions are repeated for each table in question. 206 builder.Select(). 207 Column(QCol(s_ManifestTable_Table_Name, SQLite::RowIDName)).As(manifestAlias); 208 209 // Value will be captured for single tables references, and left empty for multi-tables 210 if (columns.size() == 1) 211 { 212 builder.Column(*columns.begin()); 213 } 214 else 215 { 216 builder.Value(std::string_view{}); 217 } 218 219 builder.As(valueAlias).From(s_ManifestTable_Table_Name); 220 221 // Create join clauses 222 THROW_HR_IF(E_INVALIDARG, columns.size() != isOneToOnes.size()); 223 auto columnItr = columns.begin(); 224 auto isOneToOneItr = isOneToOnes.begin(); 225 226 for (; columnItr != columns.end(); ++columnItr, ++isOneToOneItr) 227 { 228 const SQLite::Builder::QualifiedColumn& column = *columnItr; 229 230 if (*isOneToOneItr) 231 { 232 builder. 233 Join(column.Table).On(QCol(s_ManifestTable_Table_Name, column.Column), QCol(column.Table, SQLite::RowIDName)); 234 } 235 else 236 { 237 std::string mapTableName = details::OneToManyTableGetMapTableName(column.Table); 238 builder. 239 Join(mapTableName).On(QCol(s_ManifestTable_Table_Name, SQLite::RowIDName), QCol(mapTableName, details::OneToManyTableGetManifestColumnName())). 240 Join(column.Table).On(QCol(mapTableName, column.Column), QCol(column.Table, SQLite::RowIDName)); 241 } 242 } 243 244 std::vector<int> result; 245 246 // Create where clause 247 for (const SQLite::Builder::QualifiedColumn& column : columns) 248 { 249 if (result.empty()) 250 { 251 builder.Where(column); 252 } 253 else 254 { 255 builder.And(column); 256 } 257 258 if (useLike) 259 { 260 builder.Like(SQLite::Builder::Unbound); 261 result.push_back(builder.GetLastBindIndex()); 262 builder.Escape(SQLite::EscapeCharForLike); 263 } 264 else 265 { 266 builder.Equals(SQLite::Builder::Unbound); 267 result.push_back(builder.GetLastBindIndex()); 268 } 269 } 270 271 return result; 272 } 273 274 SQLite::Statement ManifestTableUpdateValueIdById_Statement(SQLite::Connection& connection, std::string_view valueName) 275 { 276 SQLite::Builder::StatementBuilder builder; 277 builder.Update(s_ManifestTable_Table_Name).Set().Column(valueName).Equals(SQLite::Builder::Unbound).Where(SQLite::RowIDName).Equals(SQLite::Builder::Unbound); 278 279 return builder.Prepare(connection); 280 } 281 282 bool ManifestTableCheckConsistency(const SQLite::Connection& connection, const SQLite::Builder::QualifiedColumn& target, std::string_view manifestColumnName, bool log) 283 { 284 using QCol = SQLite::Builder::QualifiedColumn; 285 286 // Build a select statement to find manifest rows containing references to 1:1 tables with nonexistent rowids 287 // Such as: 288 // Select manifest.rowid, manifest.id, ids.id from manifest left outer join ids on manifest.id = ids.rowid where ids.id is NULL 289 SQLite::Builder::StatementBuilder builder; 290 builder. 291 Select({ QCol(s_ManifestTable_Table_Name, SQLite::RowIDName), QCol(s_ManifestTable_Table_Name, manifestColumnName) }). 292 From(s_ManifestTable_Table_Name). 293 LeftOuterJoin(target.Table).On(QCol(s_ManifestTable_Table_Name, manifestColumnName), QCol(target.Table, SQLite::RowIDName)). 294 Where(target).IsNull(); 295 296 SQLite::Statement select = builder.Prepare(connection); 297 bool result = true; 298 299 while (select.Step()) 300 { 301 result = false; 302 303 if (!log) 304 { 305 break; 306 } 307 308 AICLI_LOG(Repo, Info, << " [INVALID] manifest [" << select.GetColumn<SQLite::rowid_t>(0) << "] refers to " << target.Table << " [" << select.GetColumn<SQLite::rowid_t>(1) << "]"); 309 } 310 311 return result; 312 } 313 } 314 315 std::string_view ManifestTable::TableName() 316 { 317 return s_ManifestTable_Table_Name; 318 } 319 320 // Starting in V1.1, all code should be going this route of creating named indices rather than using primary or unique keys on columns. 321 // The resulting database will function the same, but give us control to drop the indices to reduce space. 322 void ManifestTable::Create(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values) 323 { 324 using namespace SQLite::Builder; 325 326 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createManifestTable_v1_1"); 327 328 StatementBuilder createTableBuilder; 329 createTableBuilder.CreateTable(s_ManifestTable_Table_Name).BeginColumns(); 330 331 // Add an integer primary key to keep the manifest rowid consistent 332 createTableBuilder.Column(IntegerPrimaryKey()); 333 334 for (const ManifestColumnInfo& value : values) 335 { 336 createTableBuilder.Column(ColumnBuilder(value.Name, Type::Int64).NotNull()); 337 } 338 339 createTableBuilder.EndColumns(); 340 341 createTableBuilder.Execute(connection); 342 343 // Create a unique index with the primary key values 344 StatementBuilder pkIndexBuilder; 345 346 pkIndexBuilder.CreateUniqueIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Suffix }).On(s_ManifestTable_Table_Name).BeginColumns(); 347 348 for (const ManifestColumnInfo& value : values) 349 { 350 if (value.PrimaryKey) 351 { 352 pkIndexBuilder.Column(value.Name); 353 } 354 } 355 356 pkIndexBuilder.EndColumns(); 357 358 pkIndexBuilder.Execute(connection); 359 360 // Create an index on every value to improve performance 361 for (const ManifestColumnInfo& value : values) 362 { 363 StatementBuilder createIndexBuilder; 364 365 if (value.Unique) 366 { 367 createIndexBuilder.CreateUniqueIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Separator, value.Name, s_ManifestTable_Index_Suffix }); 368 } 369 else 370 { 371 createIndexBuilder.CreateIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Separator, value.Name, s_ManifestTable_Index_Suffix }); 372 } 373 374 createIndexBuilder.On(s_ManifestTable_Table_Name).Columns(value.Name); 375 376 createIndexBuilder.Execute(connection); 377 } 378 379 savepoint.Commit(); 380 } 381 382 void ManifestTable::AddColumn(SQLite::Connection& connection, AddedColumnInfo value) 383 { 384 using namespace SQLite::Builder; 385 386 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "addColumnManifestTable_v1_3"); 387 388 StatementBuilder alterTableBuilder; 389 alterTableBuilder.AlterTable(s_ManifestTable_Table_Name).Add(value.Name, value.Type); 390 391 alterTableBuilder.Execute(connection); 392 393 savepoint.Commit(); 394 } 395 396 void ManifestTable::Create_deprecated(SQLite::Connection& connection, std::initializer_list<ManifestColumnInfo> values) 397 { 398 using namespace SQLite::Builder; 399 400 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "createManifestTable_v1_0"); 401 402 StatementBuilder createTableBuilder; 403 createTableBuilder.CreateTable(s_ManifestTable_Table_Name).BeginColumns(); 404 405 for (const ManifestColumnInfo& value : values) 406 { 407 createTableBuilder.Column(ColumnBuilder(value.Name, Type::Int64).NotNull().Unique(value.Unique)); 408 } 409 410 PrimaryKeyBuilder pkBuilder; 411 for (const ManifestColumnInfo& value : values) 412 { 413 if (value.PrimaryKey) 414 { 415 pkBuilder.Column(value.Name); 416 } 417 } 418 419 createTableBuilder.Column(pkBuilder).EndColumns(); 420 421 createTableBuilder.Execute(connection); 422 423 // Create an index on every value to improve performance 424 for (const ManifestColumnInfo& value : values) 425 { 426 StatementBuilder createIndexBuilder; 427 createIndexBuilder.CreateIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Separator, value.Name, s_ManifestTable_Index_Suffix }). 428 On(s_ManifestTable_Table_Name).Columns(value.Name); 429 430 createIndexBuilder.Execute(connection); 431 } 432 433 savepoint.Commit(); 434 } 435 436 void ManifestTable::Drop(SQLite::Connection& connection) 437 { 438 SQLite::Builder::StatementBuilder dropTableBuilder; 439 dropTableBuilder.DropTable(s_ManifestTable_Table_Name); 440 441 dropTableBuilder.Execute(connection); 442 } 443 444 SQLite::rowid_t ManifestTable::Insert(SQLite::Connection& connection, std::initializer_list<ManifestOneToOneValue> values) 445 { 446 SQLite::Builder::StatementBuilder builder; 447 builder.InsertInto(s_ManifestTable_Table_Name).BeginColumns(); 448 449 for (const ManifestOneToOneValue& value : values) 450 { 451 builder.Column(value.Name); 452 } 453 454 builder.EndColumns().BeginValues(); 455 456 for (const ManifestOneToOneValue& value : values) 457 { 458 builder.Value(value.Value); 459 } 460 461 builder.EndValues(); 462 463 builder.Execute(connection); 464 465 return connection.GetLastInsertRowID(); 466 } 467 468 bool ManifestTable::ExistsById(const SQLite::Connection& connection, SQLite::rowid_t id) 469 { 470 SQLite::Builder::StatementBuilder builder; 471 builder.Select(SQLite::Builder::RowCount).From(s_ManifestTable_Table_Name).Where(SQLite::RowIDName).Equals(id); 472 473 SQLite::Statement countStatement = builder.Prepare(connection); 474 475 THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); 476 477 return (countStatement.GetColumn<int>(0) != 0); 478 } 479 480 void ManifestTable::DeleteById(SQLite::Connection& connection, SQLite::rowid_t id) 481 { 482 SQLite::Builder::StatementBuilder builder; 483 builder.DeleteFrom(s_ManifestTable_Table_Name).Where(SQLite::RowIDName).Equals(id); 484 485 builder.Execute(connection); 486 } 487 488 void ManifestTable::PrepareForPackaging(SQLite::Connection& connection, std::initializer_list<std::string_view> values) 489 { 490 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "pfpManifestTable_v1_1"); 491 492 PrepareForPackaging_deprecated(connection, values); 493 494 SQLite::Builder::StatementBuilder dropPKIndexBuilder; 495 dropPKIndexBuilder.DropIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Suffix }); 496 dropPKIndexBuilder.Execute(connection); 497 498 savepoint.Commit(); 499 } 500 501 void ManifestTable::PrepareForPackaging_deprecated(SQLite::Connection& connection, std::initializer_list<std::string_view> values) 502 { 503 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, "pfpManifestTable_v1_0"); 504 505 // Drop the index on the requested values 506 for (std::string_view value : values) 507 { 508 SQLite::Builder::StatementBuilder dropIndexBuilder; 509 dropIndexBuilder.DropIndex({ s_ManifestTable_Table_Name, s_ManifestTable_Index_Separator, value, s_ManifestTable_Index_Suffix }); 510 511 dropIndexBuilder.Execute(connection); 512 } 513 514 savepoint.Commit(); 515 } 516 517 bool ManifestTable::IsValueReferenced(const SQLite::Connection& connection, std::string_view valueName, SQLite::rowid_t valueRowId) 518 { 519 return details::ManifestTableSelectByValueIds(connection, { valueName }, { valueRowId }).has_value(); 520 } 521 522 bool ManifestTable::IsEmpty(SQLite::Connection& connection) 523 { 524 SQLite::Builder::StatementBuilder builder; 525 builder.Select(SQLite::Builder::RowCount).From(s_ManifestTable_Table_Name); 526 527 SQLite::Statement countStatement = builder.Prepare(connection); 528 529 THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); 530 531 return (countStatement.GetColumn<int>(0) == 0); 532 } 533 }