// Copyright (c) 2020 650 Industries, Inc. All rights reserved. import ExpoModulesTestCore import sqlite3 @testable import EXUpdates let UpdatesDatabaseV4Schema = """ CREATE TABLE "updates" ( "id" BLOB UNIQUE, "scope_key" TEXT NOT NULL, "commit_time" INTEGER NOT NULL, "runtime_version" TEXT NOT NULL, "launch_asset_id" INTEGER, "metadata" TEXT, "status" INTEGER NOT NULL, "keep" INTEGER NOT NULL, PRIMARY KEY("id"), FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "assets" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT, "key" TEXT NOT NULL UNIQUE, "headers" TEXT, "type" TEXT NOT NULL, "metadata" TEXT, "download_time" INTEGER NOT NULL, "relative_path" TEXT NOT NULL, "hash" BLOB NOT NULL, "hash_type" INTEGER NOT NULL, "marked_for_deletion" INTEGER NOT NULL ); CREATE TABLE "updates_assets" ( "update_id" BLOB NOT NULL, "asset_id" INTEGER NOT NULL, FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "json_data" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "key" TEXT NOT NULL, "value" TEXT NOT NULL, "last_updated" INTEGER NOT NULL, "scope_key" TEXT NOT NULL ); CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); """ let UpdatesDatabaseV5Schema = """ CREATE TABLE "updates" ( "id" BLOB UNIQUE, "scope_key" TEXT NOT NULL, "commit_time" INTEGER NOT NULL, "runtime_version" TEXT NOT NULL, "launch_asset_id" INTEGER, "metadata" TEXT, "status" INTEGER NOT NULL, "keep" INTEGER NOT NULL, PRIMARY KEY("id"), FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "assets" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT, "key" TEXT UNIQUE, "headers" TEXT, "type" TEXT NOT NULL, "metadata" TEXT, "download_time" INTEGER NOT NULL, "relative_path" TEXT NOT NULL, "hash" BLOB NOT NULL, "hash_type" INTEGER NOT NULL, "marked_for_deletion" INTEGER NOT NULL ); CREATE TABLE "updates_assets" ( "update_id" BLOB NOT NULL, "asset_id" INTEGER NOT NULL, FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "json_data" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "key" TEXT NOT NULL, "value" TEXT NOT NULL, "last_updated" INTEGER NOT NULL, "scope_key" TEXT NOT NULL ); CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); """ let UpdatesDatabaseV6Schema = """ CREATE TABLE "updates" ( "id" BLOB UNIQUE, "scope_key" TEXT NOT NULL, "commit_time" INTEGER NOT NULL, "runtime_version" TEXT NOT NULL, "launch_asset_id" INTEGER, "manifest" TEXT, "status" INTEGER NOT NULL, "keep" INTEGER NOT NULL, "last_accessed" INTEGER NOT NULL, PRIMARY KEY("id"), FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "assets" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT, "key" TEXT UNIQUE, "headers" TEXT, "type" TEXT NOT NULL, "metadata" TEXT, "download_time" INTEGER NOT NULL, "relative_path" TEXT NOT NULL, "hash" BLOB NOT NULL, "hash_type" INTEGER NOT NULL, "marked_for_deletion" INTEGER NOT NULL ); CREATE TABLE "updates_assets" ( "update_id" BLOB NOT NULL, "asset_id" INTEGER NOT NULL, FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "json_data" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "key" TEXT NOT NULL, "value" TEXT NOT NULL, "last_updated" INTEGER NOT NULL, "scope_key" TEXT NOT NULL ); CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); """ let UpdatesDatabaseV7Schema = """ CREATE TABLE "updates" ( "id" BLOB UNIQUE, "scope_key" TEXT NOT NULL, "commit_time" INTEGER NOT NULL, "runtime_version" TEXT NOT NULL, "launch_asset_id" INTEGER, "manifest" TEXT, "status" INTEGER NOT NULL, "keep" INTEGER NOT NULL, "last_accessed" INTEGER NOT NULL, "successful_launch_count" INTEGER NOT NULL DEFAULT 0, "failed_launch_count" INTEGER NOT NULL DEFAULT 0, PRIMARY KEY("id"), FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "assets" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT, "key" TEXT UNIQUE, "headers" TEXT, "type" TEXT NOT NULL, "metadata" TEXT, "download_time" INTEGER NOT NULL, "relative_path" TEXT NOT NULL, "hash" BLOB NOT NULL, "hash_type" INTEGER NOT NULL, "marked_for_deletion" INTEGER NOT NULL ); CREATE TABLE "updates_assets" ( "update_id" BLOB NOT NULL, "asset_id" INTEGER NOT NULL, FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "json_data" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "key" TEXT NOT NULL, "value" TEXT NOT NULL, "last_updated" INTEGER NOT NULL, "scope_key" TEXT NOT NULL ); CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); """ let UpdatesDatabaseV8Schema = """ CREATE TABLE "updates" ( "id" BLOB UNIQUE, "scope_key" TEXT NOT NULL, "commit_time" INTEGER NOT NULL, "runtime_version" TEXT NOT NULL, "launch_asset_id" INTEGER, "manifest" TEXT, "status" INTEGER NOT NULL, "keep" INTEGER NOT NULL, "last_accessed" INTEGER NOT NULL, "successful_launch_count" INTEGER NOT NULL DEFAULT 0, "failed_launch_count" INTEGER NOT NULL DEFAULT 0, PRIMARY KEY("id"), FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "assets" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT, "key" TEXT UNIQUE, "headers" TEXT, "extra_request_headers" TEXT, "type" TEXT NOT NULL, "metadata" TEXT, "download_time" INTEGER NOT NULL, "relative_path" TEXT NOT NULL, "hash" BLOB NOT NULL, "hash_type" INTEGER NOT NULL, "marked_for_deletion" INTEGER NOT NULL ); CREATE TABLE "updates_assets" ( "update_id" BLOB NOT NULL, "asset_id" INTEGER NOT NULL, FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "json_data" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "key" TEXT NOT NULL, "value" TEXT NOT NULL, "last_updated" INTEGER NOT NULL, "scope_key" TEXT NOT NULL ); CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); """ let UpdatesDatabaseV9Schema = """ CREATE TABLE "updates" ( "id" BLOB UNIQUE, "scope_key" TEXT NOT NULL, "commit_time" INTEGER NOT NULL, "runtime_version" TEXT NOT NULL, "launch_asset_id" INTEGER, "manifest" TEXT, "status" INTEGER NOT NULL, "keep" INTEGER NOT NULL, "last_accessed" INTEGER NOT NULL, "successful_launch_count" INTEGER NOT NULL DEFAULT 0, "failed_launch_count" INTEGER NOT NULL DEFAULT 0, PRIMARY KEY("id"), FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "assets" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT, "key" TEXT UNIQUE, "headers" TEXT, "expected_hash" TEXT, "extra_request_headers" TEXT, "type" TEXT NOT NULL, "metadata" TEXT, "download_time" INTEGER NOT NULL, "relative_path" TEXT NOT NULL, "hash" BLOB NOT NULL, "hash_type" INTEGER NOT NULL, "marked_for_deletion" INTEGER NOT NULL ); CREATE TABLE "updates_assets" ( "update_id" BLOB NOT NULL, "asset_id" INTEGER NOT NULL, FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE, FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ); CREATE TABLE "json_data" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "key" TEXT NOT NULL, "value" TEXT NOT NULL, "last_updated" INTEGER NOT NULL, "scope_key" TEXT NOT NULL ); CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time"); CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id"); CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key"); """ class UpdatesDatabaseInitializationSpec : ExpoSpec { override func spec() { var testDatabaseDir: URL! beforeEach { let applicationSupportDir = FileManager.default.urls(for: .applicationSupportDirectory, in: .userDomainMask).last testDatabaseDir = applicationSupportDir!.appendingPathComponent("UpdatesDatabaseTests") try? FileManager.default.removeItem(atPath: testDatabaseDir.path) if !FileManager.default.fileExists(atPath: testDatabaseDir.path) { try! FileManager.default.createDirectory(atPath: testDatabaseDir.path, withIntermediateDirectories: true) } } describe("database persistence") { it("persists") { let db = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir) // insert some test data let insertSql = """ INSERT INTO "assets" ("url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (NULL,'bundle-1614137401950',NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ _ = try! UpdatesDatabaseUtils.execute(sql: insertSql, withArgs: nil, onDatabase: db) // mimic the app closing and reopening sqlite3_close(db) let newDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir) // ensure the data is still there 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" let rows = try! UpdatesDatabaseUtils.execute(sql: selectSql, withArgs: nil, onDatabase: newDb) expect(rows.count) == 1 expect((rows[0]["id"] as? NSNumber)?.intValue) == 1 } } describe("migrations") { it("migrates 4 to latest") { // this test just does some simple data validation to make sure the database persists across all migrations // individual migrations are tested in more detail individually let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV4Schema, filename: "expo-v4.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), (4,NULL,'bundle-1614137401950',NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ let insertUpdatesSql = """ INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","metadata","status","keep") VALUES (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{"metadata":{"updateGroup":"34993d39-57e6-46cf-8fa2-eba836f40828","branchName":"rollout"}}',1,1), (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1); """ let insertUpdatesAssetsSql = """ INSERT INTO "updates_assets" ("update_id","asset_id") VALUES (X'8C263F9DE3FF48888496E3244C788661',2), (X'8C263F9DE3FF48888496E3244C788661',3), (X'594100ea066e4804b5c7c907c773f980',4); """ _ = try! UpdatesDatabaseUtils.execute(sql: insertAssetsSql, withArgs: nil, onDatabase: db) _ = try! UpdatesDatabaseUtils.execute(sql: insertUpdatesSql, withArgs: nil, onDatabase: db) _ = try! UpdatesDatabaseUtils.execute(sql: insertUpdatesAssetsSql, withArgs: nil, onDatabase: db) sqlite3_close(db) // initialize without specifying migrations in order to run them all let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir) // verify data integrity let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 // expect the second update to have been deleted since it didn't have a manifest let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 // this asset should be deleted by foreign key constraint since its update had a null manifest and was deleted let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 // make sure multiple migrations are running let lastAccessedSql = "SELECT DISTINCT `last_accessed` FROM `updates` WHERE `last_accessed` IS NOT NULL" expect(try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql, withArgs:nil, onDatabase:migratedDb).count) == 1 let successfulLaunchCountSql = "SELECT * FROM `updates` WHERE `successful_launch_count` = 1" expect(try! UpdatesDatabaseUtils.execute(sql:successfulLaunchCountSql, withArgs:nil, onDatabase:migratedDb).count) == 1 } it("migrates 4 to 5") { let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV4Schema, filename: "expo-v4.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), (4,NULL,'bundle-1614137401950',NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ let insertUpdatesSql = """ INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","metadata","status","keep") VALUES (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{"metadata":{"updateGroup":"34993d39-57e6-46cf-8fa2-eba836f40828","branchName":"rollout"}}',1,1), (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1); """ let insertUpdatesAssetsSql = """ INSERT INTO "updates_assets" ("update_id","asset_id") VALUES (X'8C263F9DE3FF48888496E3244C788661',2), (X'8C263F9DE3FF48888496E3244C788661',3), (X'594100ea066e4804b5c7c907c773f980',4); """ _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) sqlite3_close(db) let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema(inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration4To5()]) // verify data integrity let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure we can insert multiple assets with null keys let nullInsertSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (5,NULL,NULL,NULL,'js',NULL,1614137406589,'bundle-1614137401951','1234',0,0),\ (6,NULL,NULL,NULL,'js',NULL,1614137406580,'bundle-1614137401952','5678',0,0); """ _ = try! UpdatesDatabaseUtils.execute(sql:nullInsertSql, withArgs:nil, onDatabase:migratedDb) // make sure foreign key constraint still works let foreignKeyInsertSql = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'594100ea066e4804b5c7c907c773f980', 5)" let foreignKeySelectSql = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 5" _ = try! UpdatesDatabaseUtils.execute(sql:foreignKeyInsertSql, withArgs:nil, onDatabase:migratedDb) expect(try! UpdatesDatabaseUtils.execute(sql:foreignKeySelectSql, withArgs:nil, onDatabase:migratedDb).count) == 1 let foreignKeyInsertBadSql = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'594100ea066e4804b5c7c907c773f980', 13)" expect { try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql, withArgs:nil, onDatabase:migratedDb) }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in expect(error.info?.extendedCode) == 787 }) // test on delete cascade let deleteSql = "DELETE FROM `assets` WHERE `id` = 5" let selectDeletedSql = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 5" _ = try! UpdatesDatabaseUtils.execute(sql:deleteSql, withArgs:nil, onDatabase:migratedDb) expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql, withArgs:nil, onDatabase:migratedDb).count) == 0 } it("migrates 5 to 6") { let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV5Schema, filename: "expo-v5.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ let insertUpdatesSql = """ INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","metadata","status","keep") VALUES (X'8C263F9DE3FF48888496E3244C788661','http://192.168.4.44:3000',1614137308871,'40.0.0',3,'{\\"metadata\\":{\\"updateGroup\\":\\"34993d39-57e6-46cf-8fa2-eba836f40828\\",\\"branchName\\":\\"rollout\\"}}',1,1), (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1); """ let insertUpdatesAssetsSql = """ INSERT INTO "updates_assets" ("update_id","asset_id") VALUES (X'8C263F9DE3FF48888496E3244C788661',2), (X'8C263F9DE3FF48888496E3244C788661',3), (X'594100ea066e4804b5c7c907c773f980',4); """ _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) sqlite3_close(db) // initialize a new database object the normal way and run migrations let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration5To6()] ) // verify data integrity 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" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure metadata -> manifest column rename worked let updatesNonNullManifestSql = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661' AND `manifest` = '{\\\"metadata\\\":{\\\"updateGroup\\\":\\\"34993d39-57e6-46cf-8fa2-eba836f40828\\\",\\\"branchName\\\":\\\"rollout\\\"}}'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesNonNullManifestSql, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure last_accessed column was filled in appropriately (all existing updates have the same last_accessed time) let lastAccessedSql1 = "SELECT DISTINCT last_accessed FROM `updates`" expect(try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure we can add new updates with different last_accessed times let lastAccessedSql2 = """ INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES (X'8F06A50E5A68499F986CCA31EE159F81','https://exp.host/@esamelson/sdk41updates',1619133262732,'41.0.0',NULL,NULL,1,1,1619647642456) """ _ = try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql2, withArgs:nil, onDatabase:migratedDb) let lastAccessedSql3 = "SELECT DISTINCT last_accessed FROM `updates`" expect(try! UpdatesDatabaseUtils.execute(sql:lastAccessedSql3, withArgs:nil, onDatabase:migratedDb).count) == 2 // make sure foreign key constraints still work // try to insert an update with a non-existent launch asset id (47) let foreignKeyInsertBadSql1 = """ 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) """ expect { try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql1, withArgs:nil, onDatabase:migratedDb) }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in expect(error.info?.extendedCode) == 787 }) // try to insert an entry in updates_assets that references a nonexistent update let foreignKeyInsertBadSql2 = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'3CF0A835CB3A4A5D9C14DFA3D55DE44D', 3)" expect { try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql2, withArgs:nil, onDatabase:migratedDb) }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in expect(error.info?.extendedCode) == 787 }) // test updates on delete cascade let deleteSql = "DELETE FROM `assets` WHERE `id` = 3" let selectDeletedSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" _ = try! UpdatesDatabaseUtils.execute(sql:deleteSql, withArgs:nil, onDatabase:migratedDb) expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql1, withArgs:nil, onDatabase:migratedDb).count) == 0 // test updates_assets on delete cascade // previous deletion should have deleted the update, which then should have deleted both entries in updates_assets let selectDeletedSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 let selectDeletedSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 } it("migrates 6 to 7") { let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV6Schema, filename: "expo-v6.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ let insertUpdatesSql = """ INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES (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), (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1,1619647642457); """ let insertUpdatesAssetsSql = """ INSERT INTO "updates_assets" ("update_id","asset_id") VALUES (X'8C263F9DE3FF48888496E3244C788661',2), (X'8C263F9DE3FF48888496E3244C788661',3), (X'594100ea066e4804b5c7c907c773f980',4); """ _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) sqlite3_close(db) // initialize a new database object the normal way and run migrations let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration6To7()] ) // verify data integrity 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" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure successful_launch_count and failed_launch_count columns were filled in properly let successfulLaunchCountSql = "SELECT DISTINCT `successful_launch_count` FROM `updates`" expect(try! UpdatesDatabaseUtils.execute(sql:successfulLaunchCountSql, withArgs:nil, onDatabase:migratedDb).count) == 1 let failedLaunchCount = "SELECT DISTINCT `failed_launch_count` FROM `updates`" expect(try! UpdatesDatabaseUtils.execute(sql:failedLaunchCount, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure we can modify successful and failed launch counts let modifySuccessfulLaunchCountSql = "UPDATE `updates` SET `successful_launch_count` = 2 WHERE `id` = X'8C263F9DE3FF48888496E3244C788661';" let modifyFailedLaunchCountSql = "UPDATE `updates` SET `failed_launch_count` = 1 WHERE `id` = X'594100ea066e4804b5c7c907c773f980';" _ = try! UpdatesDatabaseUtils.execute(sql:modifySuccessfulLaunchCountSql, withArgs:nil, onDatabase:migratedDb) _ = try! UpdatesDatabaseUtils.execute(sql:modifyFailedLaunchCountSql, withArgs:nil, onDatabase:migratedDb) let checkFailedLaunchCountSql = "SELECT `id` FROM `updates` WHERE `failed_launch_count` > 0" expect(try! UpdatesDatabaseUtils.execute(sql:checkFailedLaunchCountSql, withArgs:nil, onDatabase:migratedDb).count) == 1 // make sure foreign key constraints still work // try to insert an update with a non-existent launch asset id (47) let foreignKeyInsertBadSql1 = """ 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) """ expect { try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql1, withArgs:nil, onDatabase:migratedDb) }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in expect(error.info?.extendedCode) == 787 }) // try to insert an entry in updates_assets that references a nonexistent update let foreignKeyInsertBadSql2 = "INSERT INTO `updates_assets` (`update_id`, `asset_id`) VALUES (X'3CF0A835CB3A4A5D9C14DFA3D55DE44D', 3)" expect { try UpdatesDatabaseUtils.execute(sql:foreignKeyInsertBadSql2, withArgs:nil, onDatabase:migratedDb) }.to(throwError(errorType: UpdatesDatabaseUtilsError.self) { error in expect(error.info?.extendedCode) == 787 }) // test updates on delete cascade let deleteSql = "DELETE FROM `assets` WHERE `id` = 3" _ = try! UpdatesDatabaseUtils.execute(sql:deleteSql, withArgs:nil, onDatabase:migratedDb) let selectDeletedSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql1, withArgs:nil, onDatabase:migratedDb).count) == 0 // test updates_assets on delete cascade // previous deletion should have deleted the update, which then should have deleted both entries in updates_assets let selectDeletedSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 let selectDeletedSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:selectDeletedSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 } it("migrates 7 to 8") { let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV7Schema, filename: "expo-v7.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) sqlite3_close(db) // initialize a new database object the normal way and run migrations let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration7To8()] ) 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 } it("migrates 8 to 9") { let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV8Schema, filename: "expo-v8.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion","extra_request_headers") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0,NULL), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0,NULL), (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0,NULL); """ _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) sqlite3_close(db) // initialize a new database object the normal way and run migrations let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration8To9()] ) 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 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" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 } it("migrates 9 to 10") { let db = try! UpdatesDatabaseInitialization.initializeDatabase( withSchema: UpdatesDatabaseV9Schema, filename: "expo-v9.db", inDirectory: testDatabaseDir, shouldMigrate: false, migrations: [] ) // insert test data let insertAssetsSql = """ INSERT INTO "assets" ("id","url","key","headers","type","metadata","download_time","relative_path","hash","hash_type","marked_for_deletion") VALUES (2,'https://url.to/b56cf690e0afa93bd4dc7756d01edd3e','b56cf690e0afa93bd4dc7756d01edd3e.png',NULL,'image/png',NULL,1614137309295,'b56cf690e0afa93bd4dc7756d01edd3e.png','c4fdfc2ec388025067a0f755bda7731a0a868a2be79c84509f4de4e40d23161b',0,0), (3,'https://url.to/bundle-1614137308871','bundle-1614137308871',NULL,'application/javascript',NULL,1614137309513,'bundle-1614137308871','e4d658861e85e301fb89bcfc49c42738ebcc0f9d5c979e037556435f44a27aa2',0,0), (4,NULL,NULL,NULL,'js',NULL,1614137406588,'bundle-1614137401950','6ff4ee75b48a21c7a9ed98015ff6bfd0a47b94cd087c5e2258262e65af239952',0,0); """ let insertUpdatesSql = """ INSERT INTO "updates" ("id","scope_key","commit_time","runtime_version","launch_asset_id","manifest","status","keep", "last_accessed") VALUES (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), (X'594100ea066e4804b5c7c907c773f980','http://192.168.4.44:3000',1614137401950,'40.0.0',4,NULL,1,1,1619647642457); """ let insertUpdatesAssetsSql = """ INSERT INTO "updates_assets" ("update_id","asset_id") VALUES (X'8C263F9DE3FF48888496E3244C788661',2), (X'8C263F9DE3FF48888496E3244C788661',3), (X'594100ea066e4804b5c7c907c773f980',4); """ _ = try! UpdatesDatabaseUtils.execute(sql:insertAssetsSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesSql, withArgs:nil, onDatabase:db) _ = try! UpdatesDatabaseUtils.execute(sql:insertUpdatesAssetsSql, withArgs:nil, onDatabase:db) sqlite3_close(db) // initialize a new database object the normal way and run migrations let migratedDb = try! UpdatesDatabaseInitialization.initializeDatabaseWithLatestSchema( inDirectory: testDatabaseDir, migrations: [UpdatesDatabaseMigration9To10()] ) // verify data integrity let updatesSql1 = "SELECT * FROM `updates` WHERE `id` = X'8C263F9DE3FF48888496E3244C788661'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 // expect the second update to have been deleted since it didn't have a manifest let updatesSql2 = "SELECT * FROM `updates` WHERE `id` = X'594100ea066e4804b5c7c907c773f980'" expect(try! UpdatesDatabaseUtils.execute(sql:updatesSql2, withArgs:nil, onDatabase:migratedDb).count) == 0 let assetsSql1 = "SELECT * FROM `assets` WHERE `id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let assetsSql2 = "SELECT * FROM `assets` WHERE `id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 let assetsSql3 = "SELECT * FROM `assets` WHERE `id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:assetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql1 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 2" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql1, withArgs:nil, onDatabase:migratedDb).count) == 1 let updatesAssetsSql2 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'8C263F9DE3FF48888496E3244C788661' AND `asset_id` = 3" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql2, withArgs:nil, onDatabase:migratedDb).count) == 1 // this asset should be deleted by foreign key constraint since its update had a null manifest and was deleted let updatesAssetsSql3 = "SELECT * FROM `updates_assets` WHERE `update_id` = X'594100ea066e4804b5c7c907c773f980' AND `asset_id` = 4" expect(try! UpdatesDatabaseUtils.execute(sql:updatesAssetsSql3, withArgs:nil, onDatabase:migratedDb).count) == 0 } } } }