1 // Copyright (c) 2020 650 Industries, Inc. All rights reserved. 2 3 import ExpoModulesTestCore 4 import sqlite3 5 6 @testable import EXUpdates 7 8 let UpdatesDatabaseV4Schema = """ 9 CREATE TABLE "updates" ( 10 "id" BLOB UNIQUE, 11 "scope_key" TEXT NOT NULL, 12 "commit_time" INTEGER NOT NULL, 13 "runtime_version" TEXT NOT NULL, 14 "launch_asset_id" INTEGER, 15 "metadata" TEXT, 16 "status" INTEGER NOT NULL, 17 "keep" INTEGER NOT NULL, 18 PRIMARY KEY("id"), 19 FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 20 ); 21 CREATE TABLE "assets" ( 22 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 23 "url" TEXT, 24 "key" TEXT NOT NULL UNIQUE, 25 "headers" TEXT, 26 "type" TEXT NOT NULL, 27 "metadata" TEXT, 28 "download_time" INTEGER NOT NULL, 29 "relative_path" TEXT NOT NULL, 30 "hash" BLOB NOT NULL, 31 "hash_type" INTEGER NOT NULL, 32 "marked_for_deletion" INTEGER NOT NULL 33 ); 34 CREATE TABLE "updates_assets" ( 35 "update_id" BLOB NOT NULL, 36 "asset_id" INTEGER NOT NULL, 37 FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, 38 FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 39 ); 40 CREATE TABLE "json_data" ( 41 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 42 "key" TEXT NOT NULL, 43 "value" TEXT NOT NULL, 44 "last_updated" INTEGER NOT NULL, 45 "scope_key" TEXT NOT NULL 46 ); 47 CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); 48 CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); 49 CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); 50 """ 51 52 let UpdatesDatabaseV5Schema = """ 53 CREATE TABLE "updates" ( 54 "id" BLOB UNIQUE, 55 "scope_key" TEXT NOT NULL, 56 "commit_time" INTEGER NOT NULL, 57 "runtime_version" TEXT NOT NULL, 58 "launch_asset_id" INTEGER, 59 "metadata" TEXT, 60 "status" INTEGER NOT NULL, 61 "keep" INTEGER NOT NULL, 62 PRIMARY KEY("id"), 63 FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 64 ); 65 CREATE TABLE "assets" ( 66 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 67 "url" TEXT, 68 "key" TEXT UNIQUE, 69 "headers" TEXT, 70 "type" TEXT NOT NULL, 71 "metadata" TEXT, 72 "download_time" INTEGER NOT NULL, 73 "relative_path" TEXT NOT NULL, 74 "hash" BLOB NOT NULL, 75 "hash_type" INTEGER NOT NULL, 76 "marked_for_deletion" INTEGER NOT NULL 77 ); 78 CREATE TABLE "updates_assets" ( 79 "update_id" BLOB NOT NULL, 80 "asset_id" INTEGER NOT NULL, 81 FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, 82 FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 83 ); 84 CREATE TABLE "json_data" ( 85 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 86 "key" TEXT NOT NULL, 87 "value" TEXT NOT NULL, 88 "last_updated" INTEGER NOT NULL, 89 "scope_key" TEXT NOT NULL 90 ); 91 CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); 92 CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); 93 CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); 94 """ 95 96 let UpdatesDatabaseV6Schema = """ 97 CREATE TABLE "updates" ( 98 "id" BLOB UNIQUE, 99 "scope_key" TEXT NOT NULL, 100 "commit_time" INTEGER NOT NULL, 101 "runtime_version" TEXT NOT NULL, 102 "launch_asset_id" INTEGER, 103 "manifest" TEXT, 104 "status" INTEGER NOT NULL, 105 "keep" INTEGER NOT NULL, 106 "last_accessed" INTEGER NOT NULL, 107 PRIMARY KEY("id"), 108 FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 109 ); 110 CREATE TABLE "assets" ( 111 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 112 "url" TEXT, 113 "key" TEXT UNIQUE, 114 "headers" TEXT, 115 "type" TEXT NOT NULL, 116 "metadata" TEXT, 117 "download_time" INTEGER NOT NULL, 118 "relative_path" TEXT NOT NULL, 119 "hash" BLOB NOT NULL, 120 "hash_type" INTEGER NOT NULL, 121 "marked_for_deletion" INTEGER NOT NULL 122 ); 123 CREATE TABLE "updates_assets" ( 124 "update_id" BLOB NOT NULL, 125 "asset_id" INTEGER NOT NULL, 126 FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, 127 FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 128 ); 129 CREATE TABLE "json_data" ( 130 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 131 "key" TEXT NOT NULL, 132 "value" TEXT NOT NULL, 133 "last_updated" INTEGER NOT NULL, 134 "scope_key" TEXT NOT NULL 135 ); 136 CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); 137 CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); 138 CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); 139 """ 140 141 let UpdatesDatabaseV7Schema = """ 142 CREATE TABLE "updates" ( 143 "id" BLOB UNIQUE, 144 "scope_key" TEXT NOT NULL, 145 "commit_time" INTEGER NOT NULL, 146 "runtime_version" TEXT NOT NULL, 147 "launch_asset_id" INTEGER, 148 "manifest" TEXT, 149 "status" INTEGER NOT NULL, 150 "keep" INTEGER NOT NULL, 151 "last_accessed" INTEGER NOT NULL, 152 "successful_launch_count" INTEGER NOT NULL DEFAULT 0, 153 "failed_launch_count" INTEGER NOT NULL DEFAULT 0, 154 PRIMARY KEY("id"), 155 FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 156 ); 157 CREATE TABLE "assets" ( 158 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 159 "url" TEXT, 160 "key" TEXT UNIQUE, 161 "headers" TEXT, 162 "type" TEXT NOT NULL, 163 "metadata" TEXT, 164 "download_time" INTEGER NOT NULL, 165 "relative_path" TEXT NOT NULL, 166 "hash" BLOB NOT NULL, 167 "hash_type" INTEGER NOT NULL, 168 "marked_for_deletion" INTEGER NOT NULL 169 ); 170 CREATE TABLE "updates_assets" ( 171 "update_id" BLOB NOT NULL, 172 "asset_id" INTEGER NOT NULL, 173 FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, 174 FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 175 ); 176 CREATE TABLE "json_data" ( 177 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 178 "key" TEXT NOT NULL, 179 "value" TEXT NOT NULL, 180 "last_updated" INTEGER NOT NULL, 181 "scope_key" TEXT NOT NULL 182 ); 183 CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); 184 CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); 185 CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); 186 """ 187 188 let UpdatesDatabaseV8Schema = """ 189 CREATE TABLE "updates" ( 190 "id" BLOB UNIQUE, 191 "scope_key" TEXT NOT NULL, 192 "commit_time" INTEGER NOT NULL, 193 "runtime_version" TEXT NOT NULL, 194 "launch_asset_id" INTEGER, 195 "manifest" TEXT, 196 "status" INTEGER NOT NULL, 197 "keep" INTEGER NOT NULL, 198 "last_accessed" INTEGER NOT NULL, 199 "successful_launch_count" INTEGER NOT NULL DEFAULT 0, 200 "failed_launch_count" INTEGER NOT NULL DEFAULT 0, 201 PRIMARY KEY("id"), 202 FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 203 ); 204 CREATE TABLE "assets" ( 205 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 206 "url" TEXT, 207 "key" TEXT UNIQUE, 208 "headers" TEXT, 209 "extra_request_headers" TEXT, 210 "type" TEXT NOT NULL, 211 "metadata" TEXT, 212 "download_time" INTEGER NOT NULL, 213 "relative_path" TEXT NOT NULL, 214 "hash" BLOB NOT NULL, 215 "hash_type" INTEGER NOT NULL, 216 "marked_for_deletion" INTEGER NOT NULL 217 ); 218 CREATE TABLE "updates_assets" ( 219 "update_id" BLOB NOT NULL, 220 "asset_id" INTEGER NOT NULL, 221 FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, 222 FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 223 ); 224 CREATE TABLE "json_data" ( 225 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 226 "key" TEXT NOT NULL, 227 "value" TEXT NOT NULL, 228 "last_updated" INTEGER NOT NULL, 229 "scope_key" TEXT NOT NULL 230 ); 231 CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); 232 CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); 233 CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); 234 """ 235 236 let UpdatesDatabaseV9Schema = """ 237 CREATE TABLE "updates" ( 238 "id" BLOB UNIQUE, 239 "scope_key" TEXT NOT NULL, 240 "commit_time" INTEGER NOT NULL, 241 "runtime_version" TEXT NOT NULL, 242 "launch_asset_id" INTEGER, 243 "manifest" TEXT, 244 "status" INTEGER NOT NULL, 245 "keep" INTEGER NOT NULL, 246 "last_accessed" INTEGER NOT NULL, 247 "successful_launch_count" INTEGER NOT NULL DEFAULT 0, 248 "failed_launch_count" INTEGER NOT NULL DEFAULT 0, 249 PRIMARY KEY("id"), 250 FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 251 ); 252 CREATE TABLE "assets" ( 253 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 254 "url" TEXT, 255 "key" TEXT UNIQUE, 256 "headers" TEXT, 257 "expected_hash" TEXT, 258 "extra_request_headers" TEXT, 259 "type" TEXT NOT NULL, 260 "metadata" TEXT, 261 "download_time" INTEGER NOT NULL, 262 "relative_path" TEXT NOT NULL, 263 "hash" BLOB NOT NULL, 264 "hash_type" INTEGER NOT NULL, 265 "marked_for_deletion" INTEGER NOT NULL 266 ); 267 CREATE TABLE "updates_assets" ( 268 "update_id" BLOB NOT NULL, 269 "asset_id" INTEGER NOT NULL, 270 FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, 271 FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE 272 ); 273 CREATE TABLE "json_data" ( 274 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 275 "key" TEXT NOT NULL, 276 "value" TEXT NOT NULL, 277 "last_updated" INTEGER NOT NULL, 278 "scope_key" TEXT NOT NULL 279 ); 280 CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); 281 CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); 282 CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); 283 """ 284 285 class UpdatesDatabaseInitializationSpec : ExpoSpec { specnull286 override func spec() { 287 var testDatabaseDir: URL! 288 289 beforeEach { 290 let applicationSupportDir = FileManager.default.urls(for: .applicationSupportDirectory, in: .userDomainMask).last 291 testDatabaseDir = applicationSupportDir!.appendingPathComponent("UpdatesDatabaseTests") 292 293 try? FileManager.default.removeItem(atPath: testDatabaseDir.path) 294 295 if !FileManager.default.fileExists(atPath: testDatabaseDir.path) { 296 try! FileManager.default.createDirectory(atPath: testDatabaseDir.path, withIntermediateDirectories: true) 297 } 298 } 299 300 describe("database persistence") { 301 it("persists") { 302 let db = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir) 303 304 // insert some test data 305 let insertSql = """ 306 INSERT INTO "assets" ("url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") 307 VALUES (NULL,'bundle-1614137401950',NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 308 """ 309 _ = try! UpdatesDatabaseUtils.execute(sql: insertSql, withArgs: nil, onDatabase: db) 310 311 // mimic the app closing and reopening 312 sqlite3_close(db) 313 let newDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir) 314 315 // ensure the data is still there 316 let selectSql = "SELECT * FROM `assets` WHERE `url` IS NULL AND `key` = 'bundle-1614137401950' AND `headers` IS NULL AND `type` = 'js' AND `metadata` IS NULL AND `download_time` = 1614137406588 AND `relative_path` = 'bundle-1614137401950' AND `hash` = '6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 317 let rows = try! UpdatesDatabaseUtils.execute(sql: selectSql, withArgs: nil, onDatabase: newDb) 318 expect(rows.count) == 1 319 expect((rows[0]["id"] as? NSNumber)?.intValue) == 1 320 } 321 } 322 323 describe("migrations") { 324 it("migrates 4 to latest") { 325 // this test just does some simple data validation to make sure the database persists across all migrations 326 // individual migrations are tested in more detail individually 327 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 328 withSchema: UpdatesDatabaseV4Schema, 329 filename: "expo-v4.db", 330 inDirectory: testDatabaseDir, 331 shouldMigrate: false, 332 migrations: [] 333 ) 334 335 // insert test data 336 let insertAssetsSql = """ 337 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 338 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), 339 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), 340 (4,NULL,'bundle-1614137401950',NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 341 """ 342 let insertUpdatesSql = """ 343 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","metadata","status","keep") VALUES 344 (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{"metadata":{"updateGroup":"34993d39-57e6-46cf-8fa2-eba836f40828","branchName":"rollout"}}',1,1), 345 (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1); 346 """ 347 let insertUpdatesAssetsSql = """ 348 INSERT INTO "updates_assets" ("update_id","asset_id") VALUES 349 (X'8C263F9DE3FF48888496E3244C788661',2), 350 (X'8C263F9DE3FF48888496E3244C788661',3), 351 (X'594100ea066e4804b5c7c907c773f980',4); 352 """ 353 354 _ = try! UpdatesDatabaseUtils.execute(sql: insertAssetsSql, withArgs: nil, onDatabase: db) 355 _ = try! UpdatesDatabaseUtils.execute(sql: insertUpdatesSql, withArgs: nil, onDatabase: db) 356 _ = try! UpdatesDatabaseUtils.execute(sql: insertUpdatesAssetsSql, withArgs: nil, onDatabase: db) 357 sqlite3_close(db) 358 359 // initialize without specifying migrations in order to run them all 360 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir) 361 362 // verify data integrity 363 let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" 364 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 365 366 // expect the second update to have been deleted since it didn't have a manifest 367 let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980'" 368 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 369 370 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2" 371 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 372 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3" 373 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 374 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4" 375 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 376 377 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 378 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 379 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 380 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 381 382 // this asset should be deleted by foreign key constraint since its update had a null manifest and was deleted 383 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" 384 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 385 386 // make sure multiple migrations are running 387 let lastAccessedSql = "SELECT DISTINCT `last_accessed` FROM `updates` WHERE `last_accessed` IS NOT NULL" 388 expect(try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql, withArgs:nil, onDatabase:migratedDb).count) == 1 389 let successfulLaunchCountSql = "SELECT * FROM `updates` WHERE `successful_launch_count` = 1" 390 expect(try! UpdatesDatabaseUtils.execute(sql:successfulLaunchCountSql, withArgs:nil, onDatabase:migratedDb).count) == 1 391 } 392 393 it("migrates 4 to 5") { 394 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 395 withSchema: UpdatesDatabaseV4Schema, 396 filename: "expo-v4.db", 397 inDirectory: testDatabaseDir, 398 shouldMigrate: false, 399 migrations: [] 400 ) 401 402 // insert test data 403 let insertAssetsSql = """ 404 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 405 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), 406 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), 407 (4,NULL,'bundle-1614137401950',NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 408 """ 409 let insertUpdatesSql = """ 410 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","metadata","status","keep") VALUES 411 (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{"metadata":{"updateGroup":"34993d39-57e6-46cf-8fa2-eba836f40828","branchName":"rollout"}}',1,1), 412 (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1); 413 """ 414 let insertUpdatesAssetsSql = """ 415 INSERT INTO "updates_assets" ("update_id","asset_id") VALUES 416 (X'8C263F9DE3FF48888496E3244C788661',2), 417 (X'8C263F9DE3FF48888496E3244C788661',3), 418 (X'594100ea066e4804b5c7c907c773f980',4); 419 """ 420 _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) 421 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) 422 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) 423 424 sqlite3_close(db) 425 426 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration4To5()]) 427 428 // verify data integrity 429 let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" 430 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 431 let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980'" 432 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 433 434 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2 AND `url` = 'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e' AND `key` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `headers` IS NULL AND `type` = 'image/png' AND `metadata` IS NULL AND `download_time` = 1614137309295 AND `relative_path` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `hash` = 'c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 435 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 436 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3 AND `url` = 'https://url.to/bundle-1614137308871' AND `key` = 'bundle-1614137308871' AND `headers` IS NULL AND `type` = 'application/javascript' AND `metadata` IS NULL AND `download_time` = 1614137309513 AND `relative_path` = 'bundle-1614137308871' AND `hash` = 'e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 437 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 438 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4 AND `url` IS NULL AND `key` = 'bundle-1614137401950' AND `headers` IS NULL AND `type` = 'js' AND `metadata` IS NULL AND `download_time` = 1614137406588 AND `relative_path` = 'bundle-1614137401950' AND `hash` = '6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 439 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 440 441 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 442 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 443 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 444 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 445 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" 446 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 447 448 // make sure we can insert multiple assets with null keys 449 let nullInsertSql = """ 450 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 451 (5,NULL,NULL,NULL,'js',NULL,1614137406589,'bundle-1614137401951','1234',0,0),\ 452 (6,NULL,NULL,NULL,'js',NULL,1614137406580,'bundle-1614137401952','5678',0,0); 453 """ 454 _ = try! UpdatesDatabaseUtils.execute(sql:nullInsertSql, withArgs:nil, onDatabase:migratedDb) 455 456 // make sure foreign key constraint still works 457 let foreignKeyInsertSql = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'594100ea066e4804b5c7c907c773f980', 5)" 458 let foreignKeySelectSql = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 5" 459 _ = try! UpdatesDatabaseUtils.execute(sql:foreignKeyInsertSql, withArgs:nil, onDatabase:migratedDb) 460 expect(try! UpdatesDatabaseUtils.execute(sql:foreignKeySelectSql, withArgs:nil, onDatabase:migratedDb).count) == 1 461 462 let foreignKeyInsertBadSql = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'594100ea066e4804b5c7c907c773f980', 13)" 463 464 expect { 465 try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql, withArgs:nil, onDatabase:migratedDb) 466 }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in 467 expect(error.info?.extendedCode) == 787 468 }) 469 470 // test on delete cascade 471 let deleteSql = "DELETE FROM `assets` WHERE `id` = 5" 472 let selectDeletedSql = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 5" 473 _ = try! UpdatesDatabaseUtils.execute(sql:deleteSql, withArgs:nil, onDatabase:migratedDb) 474 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql, withArgs:nil, onDatabase:migratedDb).count) == 0 475 } 476 477 478 it("migrates 5 to 6") { 479 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 480 withSchema: UpdatesDatabaseV5Schema, 481 filename: "expo-v5.db", 482 inDirectory: testDatabaseDir, 483 shouldMigrate: false, 484 migrations: [] 485 ) 486 487 // insert test data 488 let insertAssetsSql = """ 489 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 490 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), 491 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), 492 (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 493 """ 494 let insertUpdatesSql = """ 495 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","metadata","status","keep") VALUES 496 (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{\\"metadata\\":{\\"updateGroup\\":\\"34993d39-57e6-46cf-8fa2-eba836f40828\\",\\"branchName\\":\\"rollout\\"}}',1,1), 497 (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1); 498 """ 499 let insertUpdatesAssetsSql = """ 500 INSERT INTO "updates_assets" ("update_id","asset_id") VALUES 501 (X'8C263F9DE3FF48888496E3244C788661',2), 502 (X'8C263F9DE3FF48888496E3244C788661',3), 503 (X'594100ea066e4804b5c7c907c773f980',4); 504 """ 505 _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) 506 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) 507 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) 508 509 sqlite3_close(db) 510 511 // initialize a new database object the normal way and run migrations 512 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( 513 inDirectory: testDatabaseDir, 514 migrations: [UpdatesDatabaseMigration5To6()] 515 ) 516 517 // verify data integrity 518 let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661' AND `scope_key` = 'http://192.168.4.44:3000' AND `commit_time` = 1614137308871 AND `runtime_version` = '40.0.0' AND `launch_asset_id` = 3 AND `manifest` IS NOT NULL AND `status` = 1 AND `keep` = 1" 519 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 520 let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980' AND `scope_key` = 'http://192.168.4.44:3000' AND `commit_time` = 1614137401950 AND `runtime_version` = '40.0.0' AND `launch_asset_id` = 4 AND `manifest` IS NULL AND `status` = 1 AND `keep` = 1" 521 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 522 523 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2 AND `url` = 'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e' AND `key` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `headers` IS NULL AND `type` = 'image/png' AND `metadata` IS NULL AND `download_time` = 1614137309295 AND `relative_path` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `hash` = 'c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 524 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 525 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3 AND `url` = 'https://url.to/bundle-1614137308871' AND `key` = 'bundle-1614137308871' AND `headers` IS NULL AND `type` = 'application/javascript' AND `metadata` IS NULL AND `download_time` = 1614137309513 AND `relative_path` = 'bundle-1614137308871' AND `hash` = 'e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 526 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 527 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4 AND `url` IS NULL AND `key` IS NULL AND `headers` IS NULL AND `type` = 'js' AND `metadata` IS NULL AND `download_time` = 1614137406588 AND `relative_path` = 'bundle-1614137401950' AND `hash` = '6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 528 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 529 530 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 531 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 532 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 533 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 534 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" 535 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 536 537 // make sure metadata -> manifest column rename worked 538 let updatesNonNullManifestSql = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661' AND `manifest` = '{\\\"metadata\\\":{\\\"updateGroup\\\":\\\"34993d39-57e6-46cf-8fa2-eba836f40828\\\",\\\"branchName\\\":\\\"rollout\\\"}}'" 539 expect(try! UpdatesDatabaseUtils.execute(sql:updatesNonNullManifestSql, withArgs:nil, onDatabase:migratedDb).count) == 1 540 541 // make sure last_accessed column was filled in appropriately (all existing updates have the same last_accessed time) 542 let lastAccessedSql1 = "SELECT DISTINCT last_accessed FROM `updates`" 543 expect(try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 544 545 // make sure we can add new updates with different last_accessed times 546 let lastAccessedSql2 = """ 547 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES 548 (X'8F06A50E5A68499F986CCA31EE159F81','https://exp.host/@esamelson/sdk41updates',1619133262732,'41.0.0',NULL,NULL,1,1,1619647642456) 549 """ 550 _ = try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql2, withArgs:nil, onDatabase:migratedDb) 551 let lastAccessedSql3 = "SELECT DISTINCT last_accessed FROM `updates`" 552 expect(try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql3, withArgs:nil, onDatabase:migratedDb).count) == 2 553 554 // make sure foreign key constraints still work 555 556 // try to insert an update with a non-existent launch asset id (47) 557 let foreignKeyInsertBadSql1 = """ 558 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES (X'E1AC9D5F55E041BBA5A9193DD1C1123A','https://exp.host/@esamelson/sdk41updates',1620168547318,'41.0.0',47,NULL,1,1,1619647642456) 559 """ 560 expect { 561 try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql1, withArgs:nil, onDatabase:migratedDb) 562 }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in 563 expect(error.info?.extendedCode) == 787 564 }) 565 566 // try to insert an entry in updates_assets that references a nonexistent update 567 let foreignKeyInsertBadSql2 = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'3CF0A835CB3A4A5D9C14DFA3D55DE44D', 3)" 568 expect { 569 try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql2, withArgs:nil, onDatabase:migratedDb) 570 }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in 571 expect(error.info?.extendedCode) == 787 572 }) 573 574 // test updates on delete cascade 575 let deleteSql = "DELETE FROM `assets` WHERE `id` = 3" 576 let selectDeletedSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" 577 _ = try! UpdatesDatabaseUtils.execute(sql:deleteSql, withArgs:nil, onDatabase:migratedDb) 578 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql1, withArgs:nil, onDatabase:migratedDb).count) == 0 579 580 // test updates_assets on delete cascade 581 // previous deletion should have deleted the update, which then should have deleted both entries in updates_assets 582 let selectDeletedSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 583 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 584 let selectDeletedSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 585 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 586 } 587 588 it("migrates 6 to 7") { 589 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 590 withSchema: UpdatesDatabaseV6Schema, 591 filename: "expo-v6.db", 592 inDirectory: testDatabaseDir, 593 shouldMigrate: false, 594 migrations: [] 595 ) 596 597 // insert test data 598 let insertAssetsSql = """ 599 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 600 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), 601 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), 602 (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 603 """ 604 let insertUpdatesSql = """ 605 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES 606 (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{\\"metadata\\":{\\"updateGroup\\":\\"34993d39-57e6-46cf-8fa2-eba836f40828\\",\\"branchName\\":\\"rollout\\"}}',1,1,1619647642456), 607 (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1,1619647642457); 608 """ 609 let insertUpdatesAssetsSql = """ 610 INSERT INTO "updates_assets" ("update_id","asset_id") VALUES 611 (X'8C263F9DE3FF48888496E3244C788661',2), 612 (X'8C263F9DE3FF48888496E3244C788661',3), 613 (X'594100ea066e4804b5c7c907c773f980',4); 614 """ 615 _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) 616 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) 617 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) 618 619 sqlite3_close(db) 620 621 // initialize a new database object the normal way and run migrations 622 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( 623 inDirectory: testDatabaseDir, 624 migrations: [UpdatesDatabaseMigration6To7()] 625 ) 626 627 // verify data integrity 628 let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661' AND `scope_key` = 'http://192.168.4.44:3000' AND `commit_time` = 1614137308871 AND `runtime_version` = '40.0.0' AND `launch_asset_id` = 3 AND `manifest` IS NOT NULL AND `status` = 1 AND `keep` = 1 AND `last_accessed` = 1619647642456 AND `successful_launch_count` = 1 AND `failed_launch_count` = 0" 629 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 630 let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980' AND `scope_key` = 'http://192.168.4.44:3000' AND `commit_time` = 1614137401950 AND `runtime_version` = '40.0.0' AND `launch_asset_id` = 4 AND `manifest` IS NULL AND `status` = 1 AND `keep` = 1 AND `last_accessed` = 1619647642457 AND `successful_launch_count` = 1 AND `failed_launch_count` = 0" 631 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 632 633 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2 AND `url` = 'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e' AND `key` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `headers` IS NULL AND `type` = 'image/png' AND `metadata` IS NULL AND `download_time` = 1614137309295 AND `relative_path` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `hash` = 'c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 634 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 635 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3 AND `url` = 'https://url.to/bundle-1614137308871' AND `key` = 'bundle-1614137308871' AND `headers` IS NULL AND `type` = 'application/javascript' AND `metadata` IS NULL AND `download_time` = 1614137309513 AND `relative_path` = 'bundle-1614137308871' AND `hash` = 'e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 636 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 637 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4 AND `url` IS NULL AND `key` IS NULL AND `headers` IS NULL AND `type` = 'js' AND `metadata` IS NULL AND `download_time` = 1614137406588 AND `relative_path` = 'bundle-1614137401950' AND `hash` = '6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952' AND `hash_type` = 0 AND `marked_for_deletion` = 0" 638 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 639 640 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 641 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 642 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 643 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 644 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" 645 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 646 647 // make sure successful_launch_count and failed_launch_count columns were filled in properly 648 let successfulLaunchCountSql = "SELECT DISTINCT `successful_launch_count` FROM `updates`" 649 expect(try! UpdatesDatabaseUtils.execute(sql:successfulLaunchCountSql, withArgs:nil, onDatabase:migratedDb).count) == 1 650 let failedLaunchCount = "SELECT DISTINCT `failed_launch_count` FROM `updates`" 651 expect(try! UpdatesDatabaseUtils.execute(sql:failedLaunchCount, withArgs:nil, onDatabase:migratedDb).count) == 1 652 653 // make sure we can modify successful and failed launch counts 654 let modifySuccessfulLaunchCountSql = "UPDATE `updates` SET `successful_launch_count` = 2 WHERE `id` = X'8C263F9DE3FF48888496E3244C788661';" 655 let modifyFailedLaunchCountSql = "UPDATE `updates` SET `failed_launch_count` = 1 WHERE `id` = X'594100ea066e4804b5c7c907c773f980';" 656 _ = try! UpdatesDatabaseUtils.execute(sql:modifySuccessfulLaunchCountSql, withArgs:nil, onDatabase:migratedDb) 657 _ = try! UpdatesDatabaseUtils.execute(sql:modifyFailedLaunchCountSql, withArgs:nil, onDatabase:migratedDb) 658 let checkFailedLaunchCountSql = "SELECT `id` FROM `updates` WHERE `failed_launch_count` > 0" 659 expect(try! UpdatesDatabaseUtils.execute(sql:checkFailedLaunchCountSql, withArgs:nil, onDatabase:migratedDb).count) == 1 660 661 // make sure foreign key constraints still work 662 663 // try to insert an update with a non-existent launch asset id (47) 664 let foreignKeyInsertBadSql1 = """ 665 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed","successful_launch_count","failed_launch_count") VALUES (X'E1AC9D5F55E041BBA5A9193DD1C1123A','https://exp.host/@esamelson/sdk41updates',1620168547318,'41.0.0',47,NULL,1,1,1619647642456,0,0) 666 """ 667 expect { 668 try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql1, withArgs:nil, onDatabase:migratedDb) 669 }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in 670 expect(error.info?.extendedCode) == 787 671 }) 672 673 // try to insert an entry in updates_assets that references a nonexistent update 674 let foreignKeyInsertBadSql2 = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'3CF0A835CB3A4A5D9C14DFA3D55DE44D', 3)" 675 expect { 676 try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql2, withArgs:nil, onDatabase:migratedDb) 677 }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in 678 expect(error.info?.extendedCode) == 787 679 }) 680 681 // test updates on delete cascade 682 let deleteSql = "DELETE FROM `assets` WHERE `id` = 3" 683 _ = try! UpdatesDatabaseUtils.execute(sql:deleteSql, withArgs:nil, onDatabase:migratedDb) 684 let selectDeletedSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" 685 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql1, withArgs:nil, onDatabase:migratedDb).count) == 0 686 687 // test updates_assets on delete cascade 688 // previous deletion should have deleted the update, which then should have deleted both entries in updates_assets 689 let selectDeletedSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 690 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 691 let selectDeletedSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 692 expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 693 } 694 695 it("migrates 7 to 8") { 696 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 697 withSchema: UpdatesDatabaseV7Schema, 698 filename: "expo-v7.db", 699 inDirectory: testDatabaseDir, 700 shouldMigrate: false, 701 migrations: [] 702 ) 703 704 // insert test data 705 let insertAssetsSql = """ 706 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 707 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), 708 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), 709 (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 710 """ 711 712 _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) 713 714 715 sqlite3_close(db) 716 717 // initialize a new database object the normal way and run migrations 718 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( 719 inDirectory: testDatabaseDir, 720 migrations: [UpdatesDatabaseMigration7To8()] 721 ) 722 723 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2 AND `url` = 'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e' AND `key` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `headers` IS NULL AND `type` = 'image/png' AND `metadata` IS NULL AND `download_time` = 1614137309295 AND `relative_path` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `hash` = 'c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b' AND `hash_type` = 0 AND `marked_for_deletion` = 0 AND `extra_request_headers` IS NULL" 724 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 725 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3 AND `url` = 'https://url.to/bundle-1614137308871' AND `key` = 'bundle-1614137308871' AND `headers` IS NULL AND `type` = 'application/javascript' AND `metadata` IS NULL AND `download_time` = 1614137309513 AND `relative_path` = 'bundle-1614137308871' AND `hash` = 'e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2' AND `hash_type` = 0 AND `marked_for_deletion` = 0 AND `extra_request_headers` IS NULL" 726 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 727 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4 AND `url` IS NULL AND `key` IS NULL AND `headers` IS NULL AND `type` = 'js' AND `metadata` IS NULL AND `download_time` = 1614137406588 AND `relative_path` = 'bundle-1614137401950' AND `hash` = '6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952' AND `hash_type` = 0 AND `marked_for_deletion` = 0 AND `extra_request_headers` IS NULL" 728 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 729 } 730 731 it("migrates 8 to 9") { 732 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 733 withSchema: UpdatesDatabaseV8Schema, 734 filename: "expo-v8.db", 735 inDirectory: testDatabaseDir, 736 shouldMigrate: false, 737 migrations: [] 738 ) 739 740 // insert test data 741 let insertAssetsSql = """ 742 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion","extra_request_headers") VALUES 743 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0,NULL), 744 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0,NULL), 745 (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0,NULL); 746 """ 747 748 _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) 749 750 751 sqlite3_close(db) 752 753 // initialize a new database object the normal way and run migrations 754 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( 755 inDirectory: testDatabaseDir, 756 migrations: [UpdatesDatabaseMigration8To9()] 757 ) 758 759 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2 AND `url` = 'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e' AND `key` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `headers` IS NULL AND `type` = 'image/png' AND `metadata` IS NULL AND `download_time` = 1614137309295 AND `relative_path` = 'b56cf690e0afa93bd4dc7756d01edd3e.png' AND `hash` = 'c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b' AND `hash_type` = 0 AND `marked_for_deletion` = 0 AND `extra_request_headers` IS NULL AND `expected_hash` IS NULL" 760 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 761 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3 AND `url` = 'https://url.to/bundle-1614137308871' AND `key` = 'bundle-1614137308871' AND `headers` IS NULL AND `type` = 'application/javascript' AND `metadata` IS NULL AND `download_time` = 1614137309513 AND `relative_path` = 'bundle-1614137308871' AND `hash` = 'e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2' AND `hash_type` = 0 AND `marked_for_deletion` = 0 AND `extra_request_headers` IS NULL AND `expected_hash` IS NULL" 762 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 763 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4 AND `url` IS NULL AND `key` IS NULL AND `headers` IS NULL AND `type` = 'js' AND `metadata` IS NULL AND `download_time` = 1614137406588 AND `relative_path` = 'bundle-1614137401950' AND `hash` = '6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952' AND `hash_type` = 0 AND `marked_for_deletion` = 0 AND `extra_request_headers` IS NULL AND `expected_hash` IS NULL" 764 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 765 } 766 767 it("migrates 9 to 10") { 768 let db = try! UpdatesDatabaseInitialization.initializeDatabase( 769 withSchema: UpdatesDatabaseV9Schema, 770 filename: "expo-v9.db", 771 inDirectory: testDatabaseDir, 772 shouldMigrate: false, 773 migrations: [] 774 ) 775 776 // insert test data 777 let insertAssetsSql = """ 778 INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES 779 (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), 780 (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), 781 (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); 782 """ 783 let insertUpdatesSql = """ 784 INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES 785 (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{\\"metadata\\":{\\"updateGroup\\":\\"34993d39-57e6-46cf-8fa2-eba836f40828\\",\\"branchName\\":\\"rollout\\"}}',1,1,1619647642456), 786 (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1,1619647642457); 787 """ 788 let insertUpdatesAssetsSql = """ 789 INSERT INTO "updates_assets" ("update_id","asset_id") VALUES 790 (X'8C263F9DE3FF48888496E3244C788661',2), 791 (X'8C263F9DE3FF48888496E3244C788661',3), 792 (X'594100ea066e4804b5c7c907c773f980',4); 793 """ 794 _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) 795 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) 796 _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) 797 798 sqlite3_close(db) 799 800 // initialize a new database object the normal way and run migrations 801 let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( 802 inDirectory: testDatabaseDir, 803 migrations: [UpdatesDatabaseMigration9To10()] 804 ) 805 806 // verify data integrity 807 let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" 808 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 809 810 // expect the second update to have been deleted since it didn't have a manifest 811 let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980'" 812 expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 813 814 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2" 815 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 816 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3" 817 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 818 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4" 819 expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 820 821 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" 822 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 823 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" 824 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 825 826 // this asset should be deleted by foreign key constraint since its update had a null manifest and was deleted 827 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" 828 expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 829 } 830 } 831 } 832 } 833
,){kind=link}
,){kind=link}