OneToManyTableWithMap.cpp (19966B)
1 // Copyright (c) Microsoft Corporation. 2 // Licensed under the MIT License. 3 #include "pch.h" 4 #include "Microsoft/Schema/2_0/OneToManyTableWithMap.h" 5 #include "Microsoft/Schema/2_0/PackagesTable.h" 6 #include <winget/SQLiteStatementBuilder.h> 7 8 9 namespace AppInstaller::Repository::Microsoft::Schema::V2_0 10 { 11 namespace details 12 { 13 using PrimaryTable = PackagesTable; 14 15 using namespace std::string_view_literals; 16 static constexpr std::string_view s_OneToManyTableWithMap_MapTable_PrimaryName = "package"sv; 17 static constexpr std::string_view s_OneToManyTableWithMap_MapTable_Suffix = "_map"sv; 18 static constexpr std::string_view s_OneToManyTableWithMap_MapTable_IndexSuffix = "_index"sv; 19 static constexpr std::string_view s_OneToManyTableWithMap_PrimaryKeyIndexSuffix = "_pkindex"sv; 20 21 namespace anon 22 { 23 // Create the mapping table insert statement for multiple use. 24 // Bind the rowid of the value to 2. 25 SQLite::Statement CreateMappingInsertStatementForPrimaryId(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t manifestId) 26 { 27 SQLite::Builder::StatementBuilder insertMappingBuilder; 28 insertMappingBuilder.InsertOrIgnore({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }). 29 Columns({ s_OneToManyTableWithMap_MapTable_PrimaryName, 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 primary id. 35 std::vector<SQLite::rowid_t> GetValueIdsByPrimaryId(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_OneToManyTableWithMap_MapTable_Suffix }).Where(s_OneToManyTableWithMap_MapTable_PrimaryName).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 void CreateDataTable(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName) 53 { 54 using namespace SQLite::Builder; 55 56 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_create_v2_0"); 57 58 StatementBuilder createTableBuilder; 59 60 createTableBuilder.CreateTable(tableName).Columns({ 61 IntegerPrimaryKey(), 62 ColumnBuilder(valueName, Type::Text).NotNull() 63 }); 64 65 createTableBuilder.Execute(connection); 66 67 StatementBuilder indexBuilder; 68 indexBuilder.CreateUniqueIndex({ tableName, s_OneToManyTableWithMap_PrimaryKeyIndexSuffix }).On(tableName).Columns(valueName); 69 indexBuilder.Execute(connection); 70 71 savepoint.Commit(); 72 } 73 74 void DropDataTable(SQLite::Connection& connection, std::string_view tableName) 75 { 76 SQLite::Builder::StatementBuilder dropTableBuilder; 77 dropTableBuilder.DropTable(tableName); 78 79 dropTableBuilder.Execute(connection); 80 } 81 82 std::optional<SQLite::rowid_t> DataTableSelectIdByValue(const SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, std::string_view value, bool useLike) 83 { 84 SQLite::Builder::StatementBuilder selectBuilder; 85 selectBuilder.Select(SQLite::RowIDName).From(tableName).Where(valueName); 86 87 if (useLike) 88 { 89 selectBuilder.LikeWithEscape(value); 90 } 91 else 92 { 93 selectBuilder.Equals(value); 94 } 95 96 SQLite::Statement select = selectBuilder.Prepare(connection); 97 98 if (select.Step()) 99 { 100 return select.GetColumn<SQLite::rowid_t>(0); 101 } 102 else 103 { 104 return {}; 105 } 106 } 107 108 std::optional<std::string> DataTableSelectValueById(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, SQLite::rowid_t rowid) 109 { 110 SQLite::Builder::StatementBuilder selectBuilder; 111 selectBuilder.Select(valueName).From(tableName).Where(SQLite::RowIDName).Equals(rowid); 112 113 SQLite::Statement select = selectBuilder.Prepare(connection); 114 115 if (select.Step()) 116 { 117 return select.GetColumn<std::string>(0); 118 } 119 else 120 { 121 return {}; 122 } 123 } 124 125 SQLite::rowid_t DataTableEnsureExists(SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, std::string_view value, bool overwriteLikeMatch = false) 126 { 127 auto selectResult = DataTableSelectIdByValue(connection, tableName, valueName, value, overwriteLikeMatch); 128 if (selectResult) 129 { 130 if (overwriteLikeMatch) 131 { 132 // If the value in the table is not an exact match, overwrite it with the incoming value 133 auto tableValue = DataTableSelectValueById(connection, tableName, valueName, selectResult.value()); 134 if (tableValue.value() != value) 135 { 136 SQLite::Builder::StatementBuilder updateBuilder; 137 updateBuilder.Update(tableName).Set().Column(valueName).Equals(value).Where(SQLite::RowIDName).Equals(selectResult); 138 139 updateBuilder.Execute(connection); 140 } 141 } 142 143 return selectResult.value(); 144 } 145 146 SQLite::Builder::StatementBuilder insertBuilder; 147 insertBuilder.InsertInto(tableName).Columns(valueName).Values(value); 148 149 insertBuilder.Execute(connection); 150 151 return connection.GetLastInsertRowID(); 152 } 153 154 void DataTablePrepareForPackaging(SQLite::Connection& connection, std::string_view tableName) 155 { 156 SQLite::Builder::StatementBuilder dropIndexBuilder; 157 dropIndexBuilder.DropIndex({ tableName, s_OneToManyTableWithMap_PrimaryKeyIndexSuffix }); 158 dropIndexBuilder.Execute(connection); 159 } 160 161 bool DataTableCheckConsistency(const SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, bool log) 162 { 163 // Build a select statement to find values that contain an embedded null character 164 // Such as: 165 // Select count(*) from table where instr(value,char(0))>0 166 SQLite::Builder::StatementBuilder builder; 167 builder. 168 Select({ SQLite::RowIDName, valueName }). 169 From(tableName). 170 WhereValueContainsEmbeddedNullCharacter(valueName); 171 172 SQLite::Statement select = builder.Prepare(connection); 173 bool result = true; 174 175 while (select.Step()) 176 { 177 result = false; 178 179 if (!log) 180 { 181 break; 182 } 183 184 AICLI_LOG(Repo, Info, << " [INVALID] value in table [" << tableName << "] at row [" << select.GetColumn<SQLite::rowid_t>(0) << "] contains an embedded null character and starts with [" << select.GetColumn<std::string>(1) << "]"); 185 } 186 187 return result; 188 } 189 } 190 191 std::string OneToManyTableWithMapGetMapTableName(std::string_view tableName) 192 { 193 std::string result(tableName); 194 result += s_OneToManyTableWithMap_MapTable_Suffix; 195 return result; 196 } 197 198 std::string_view OneToManyTableWithMapGetManifestColumnName() 199 { 200 return s_OneToManyTableWithMap_MapTable_PrimaryName; 201 } 202 203 void CreateOneToManyTableWithMap(SQLite::Connection& connection, OneToManyTableSchema schemaVersion, std::string_view tableName, std::string_view valueName) 204 { 205 using namespace SQLite::Builder; 206 207 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_create_v2_0"); 208 209 // Create the data table as a 1:1 210 anon::CreateDataTable(connection, tableName, valueName); 211 212 switch (schemaVersion) 213 { 214 case OneToManyTableSchema::Version_2_0: 215 { 216 // Create the mapping table 217 StatementBuilder createMapTableBuilder; 218 createMapTableBuilder.CreateTable({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }).Columns({ 219 ColumnBuilder(valueName, Type::Int64).NotNull(), 220 ColumnBuilder(s_OneToManyTableWithMap_MapTable_PrimaryName, Type::Int64).NotNull(), 221 PrimaryKeyBuilder({ valueName, s_OneToManyTableWithMap_MapTable_PrimaryName }) 222 }).WithoutRowID(); 223 224 createMapTableBuilder.Execute(connection); 225 } 226 break; 227 default: 228 THROW_HR(E_UNEXPECTED); 229 } 230 231 StatementBuilder createMapTableIndexBuilder; 232 createMapTableIndexBuilder.CreateIndex({ tableName, s_OneToManyTableWithMap_MapTable_Suffix, s_OneToManyTableWithMap_MapTable_IndexSuffix }). 233 On({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }).Columns({ s_OneToManyTableWithMap_MapTable_PrimaryName, valueName }); 234 235 createMapTableIndexBuilder.Execute(connection); 236 237 savepoint.Commit(); 238 } 239 240 void DropOneToManyTableWithMap(SQLite::Connection& connection, std::string_view tableName) 241 { 242 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_drop_v2_0"); 243 244 anon::DropDataTable(connection, tableName); 245 246 SQLite::Builder::StatementBuilder dropTableBuilder; 247 dropTableBuilder.DropTable({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }); 248 249 dropTableBuilder.Execute(connection); 250 251 savepoint.Commit(); 252 } 253 254 std::vector<std::string> OneToManyTableWithMapGetValuesByPrimaryId( 255 const SQLite::Connection& connection, 256 std::string_view tableName, 257 std::string_view valueName, 258 SQLite::rowid_t manifestId) 259 { 260 using QCol = SQLite::Builder::QualifiedColumn; 261 262 std::vector<std::string> result; 263 264 SQLite::Builder::StatementBuilder builder; 265 builder.Select(QCol(tableName, valueName)). 266 From({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }).As("map").Join(tableName). 267 On(QCol("map", valueName), QCol(tableName, SQLite::RowIDName)).Where(QCol("map", s_OneToManyTableWithMap_MapTable_PrimaryName)).Equals(manifestId); 268 269 SQLite::Statement statement = builder.Prepare(connection); 270 271 while (statement.Step()) 272 { 273 result.emplace_back(statement.GetColumn<std::string>(0)); 274 } 275 276 return result; 277 } 278 279 void OneToManyTableWithMapEnsureExistsAndInsert(SQLite::Connection& connection, 280 std::string_view tableName, std::string_view valueName, 281 const std::vector<std::string>& values, SQLite::rowid_t manifestId) 282 { 283 SQLite::Savepoint savepoint = SQLite::Savepoint::Create(connection, std::string{ tableName } + "_ensureandinsert_v2_0"); 284 285 SQLite::Statement insertMapping = anon::CreateMappingInsertStatementForPrimaryId(connection, tableName, valueName, manifestId); 286 287 for (const std::string& value : values) 288 { 289 // First, ensure that the data exists 290 SQLite::rowid_t dataId = anon::DataTableEnsureExists(connection, tableName, valueName, value); 291 292 // Second, insert into the mapping table 293 insertMapping.Reset(); 294 insertMapping.Bind(2, dataId); 295 296 insertMapping.Execute(); 297 } 298 299 savepoint.Commit(); 300 } 301 302 void OneToManyTableWithMapPrepareForPackaging(SQLite::Connection& connection, std::string_view tableName) 303 { 304 SQLite::Builder::StatementBuilder dropMapTableIndexBuilder; 305 dropMapTableIndexBuilder.DropIndex({ tableName, s_OneToManyTableWithMap_MapTable_Suffix, s_OneToManyTableWithMap_MapTable_IndexSuffix }); 306 307 dropMapTableIndexBuilder.Execute(connection); 308 309 anon::DataTablePrepareForPackaging(connection, tableName); 310 } 311 312 bool OneToManyTableWithMapCheckConsistency(const SQLite::Connection& connection, std::string_view tableName, std::string_view valueName, bool log) 313 { 314 using QCol = SQLite::Builder::QualifiedColumn; 315 constexpr std::string_view s_map = "map"sv; 316 317 bool result = true; 318 319 { 320 // Build a select statement to find map rows containing references to primaries with nonexistent rowids 321 // Such as: 322 // Select map.rowid, map.primary from tags_map as map left outer join primary on map.primary = primary.rowid where primary.id is null 323 324 SQLite::Builder::StatementBuilder builder; 325 builder. 326 Select({ QCol(s_map, s_OneToManyTableWithMap_MapTable_PrimaryName), QCol(s_map, valueName) }). 327 From({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }).As(s_map). 328 LeftOuterJoin(details::PrimaryTable::TableName()).On(QCol(s_map, s_OneToManyTableWithMap_MapTable_PrimaryName), QCol(details::PrimaryTable::TableName(), SQLite::RowIDName)). 329 Where(QCol(details::PrimaryTable::TableName(), SQLite::RowIDName)).IsNull(); 330 331 SQLite::Statement select = builder.Prepare(connection); 332 333 while (select.Step()) 334 { 335 result = false; 336 337 if (!log) 338 { 339 break; 340 } 341 342 AICLI_LOG(Repo, Info, << " [INVALID] " << tableName << s_OneToManyTableWithMap_MapTable_Suffix << " [" << select.GetColumn<SQLite::rowid_t>(0) << 343 ", " << select.GetColumn<SQLite::rowid_t>(1) << "] refers to invalid " << details::PrimaryTable::TableName()); 344 } 345 } 346 347 if (!result && !log) 348 { 349 return result; 350 } 351 352 { 353 // Build a select statement to find map rows containing references to 1:1 tables with nonexistent rowids 354 // Such as: 355 // Select map.rowid, map.tag from tags_map as map left outer join tags on map.tag = tags.rowid where tags.tag is null 356 SQLite::Builder::StatementBuilder builder; 357 builder. 358 Select({ QCol(s_map, s_OneToManyTableWithMap_MapTable_PrimaryName), QCol(s_map, valueName) }). 359 From({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }).As(s_map). 360 LeftOuterJoin(tableName).On(QCol(s_map, valueName), QCol(tableName, SQLite::RowIDName)). 361 Where(QCol(tableName, valueName)).IsNull(); 362 363 SQLite::Statement select = builder.Prepare(connection); 364 bool secondaryResult = true; 365 366 while (select.Step()) 367 { 368 secondaryResult = false; 369 370 if (!log) 371 { 372 break; 373 } 374 375 AICLI_LOG(Repo, Info, << " [INVALID] " << tableName << s_OneToManyTableWithMap_MapTable_Suffix << " [" << select.GetColumn<SQLite::rowid_t>(0) << 376 ", " << select.GetColumn<SQLite::rowid_t>(1) << "] refers to invalid " << tableName); 377 } 378 379 result = result && secondaryResult; 380 } 381 382 if (!result && !log) 383 { 384 return result; 385 } 386 387 result = anon::DataTableCheckConsistency(connection, tableName, valueName, log) && result; 388 389 return result; 390 } 391 392 bool OneToManyTableWithMapIsEmpty(SQLite::Connection& connection, std::string_view tableName) 393 { 394 SQLite::Builder::StatementBuilder countBuilder; 395 countBuilder.Select(SQLite::Builder::RowCount).From(tableName); 396 397 SQLite::Statement countStatement = countBuilder.Prepare(connection); 398 399 THROW_HR_IF(E_UNEXPECTED, !countStatement.Step()); 400 401 SQLite::Builder::StatementBuilder countMapBuilder; 402 countMapBuilder.Select(SQLite::Builder::RowCount).From({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }); 403 404 SQLite::Statement countMapStatement = countMapBuilder.Prepare(connection); 405 406 THROW_HR_IF(E_UNEXPECTED, !countMapStatement.Step()); 407 408 return ((countStatement.GetColumn<int>(0) == 0) && (countMapStatement.GetColumn<int>(0) == 0)); 409 } 410 411 int OneToManyTableWithMapBuildSearchStatement( 412 SQLite::Builder::StatementBuilder& builder, 413 std::string_view tableName, 414 std::string_view valueName, 415 std::string_view primaryAlias, 416 std::string_view valueAlias, 417 bool useLike) 418 { 419 using QCol = SQLite::Builder::QualifiedColumn; 420 constexpr std::string_view s_map = "map"sv; 421 422 // Build a statement like: 423 // SELECT map.package as p, table.value as v from table 424 // join map on table.rowid = map.value 425 // where table.value = <value> 426 builder.Select(). 427 Column(QCol(s_map, s_OneToManyTableWithMap_MapTable_PrimaryName)).As(primaryAlias). 428 Column(QCol(tableName, valueName)).As(valueAlias). 429 From(tableName). 430 Join({ tableName, s_OneToManyTableWithMap_MapTable_Suffix }).As(s_map).On(QCol(tableName, SQLite::RowIDName), QCol(s_map, valueName)). 431 Where(QCol(tableName, valueName)); 432 433 int result = -1; 434 435 if (useLike) 436 { 437 builder.Like(SQLite::Builder::Unbound); 438 result = builder.GetLastBindIndex(); 439 builder.Escape(SQLite::EscapeCharForLike); 440 } 441 else 442 { 443 builder.Equals(SQLite::Builder::Unbound); 444 result = builder.GetLastBindIndex(); 445 } 446 447 return result; 448 } 449 } 450 }