1 //  Copyright © 2019 650 Industries. All rights reserved.
2 
3 // swiftlint:disable identifier_name
4 
5 // sqlite db opening OpaquePointer doesn't work well with nullability
6 // swiftlint:disable force_unwrapping
7 
8 import Foundation
9 import sqlite3
10 
11 enum UpdatesDatabaseInitializationError: Error {
12   case migrateAndRemoveOldDatabaseFailure
13   case moveExistingCorruptedDatabaseFailure
14   case openAfterMovingCorruptedDatabaseFailure
15   case openInitialDatabaseOtherFailure
16   case openDatabaseFalure
17   case databseSchemaInitializationFailure
18 }
19 
20 /**
21  * Utility class that handles database initialization and migration.
22  */
23 internal final class UpdatesDatabaseInitialization {
24   private static let LatestFilename = "expo-v10.db"
25   private static let LatestSchema = """
26     CREATE TABLE "updates" (
27       "id"  BLOB UNIQUE,
28       "scope_key"  TEXT NOT NULL,
29       "commit_time"  INTEGER NOT NULL,
30       "runtime_version"  TEXT NOT NULL,
31       "launch_asset_id" INTEGER,
32       "manifest"  TEXT NOT NULL,
33       "status"  INTEGER NOT NULL,
34       "keep"  INTEGER NOT NULL,
35       "last_accessed"  INTEGER NOT NULL,
36       "successful_launch_count"  INTEGER NOT NULL DEFAULT 0,
37       "failed_launch_count"  INTEGER NOT NULL DEFAULT 0,
38       PRIMARY KEY("id"),
39       FOREIGN KEY("launch_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE
40     );
41     CREATE TABLE "assets" (
42       "id"  INTEGER PRIMARY KEY AUTOINCREMENT,
43       "url"  TEXT,
44       "key"  TEXT UNIQUE,
45       "headers"  TEXT,
46       "expected_hash"  TEXT,
47       "extra_request_headers"  TEXT,
48       "type"  TEXT NOT NULL,
49       "metadata"  TEXT,
50       "download_time"  INTEGER NOT NULL,
51       "relative_path"  TEXT NOT NULL,
52       "hash"  BLOB NOT NULL,
53       "hash_type"  INTEGER NOT NULL,
54       "marked_for_deletion"  INTEGER NOT NULL
55     );
56     CREATE TABLE "updates_assets" (
57       "update_id"  BLOB NOT NULL,
58       "asset_id" INTEGER NOT NULL,
59       FOREIGN KEY("update_id") REFERENCES "updates"("id") ON DELETE CASCADE,
60       FOREIGN KEY("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE
61     );
62     CREATE TABLE "json_data" (
63       "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
64       "key" TEXT NOT NULL,
65       "value" TEXT NOT NULL,
66       "last_updated" INTEGER NOT NULL,
67       "scope_key" TEXT NOT NULL
68     );
69     CREATE UNIQUE INDEX "index_updates_scope_key_commit_time" ON "updates" ("scope_key", "commit_time");
70     CREATE INDEX "index_updates_launch_asset_id" ON "updates" ("launch_asset_id");
71     CREATE INDEX "index_json_data_scope_key" ON "json_data" ("scope_key");
72   """
73 
initializeDatabaseWithLatestSchemanull74   static func initializeDatabaseWithLatestSchema(inDirectory directory: URL) throws -> OpaquePointer {
75     return try initializeDatabaseWithLatestSchema(
76       inDirectory: directory,
77       migrations: UpdatesDatabaseMigrationRegistry.migrations()
78     )
79   }
80 
initializeDatabaseWithLatestSchemanull81   static func initializeDatabaseWithLatestSchema(inDirectory directory: URL, migrations: [UpdatesDatabaseMigration]) throws -> OpaquePointer {
82     return try initializeDatabase(
83       withSchema: LatestSchema,
84       filename: LatestFilename,
85       inDirectory: directory,
86       shouldMigrate: true,
87       migrations: migrations
88     )
89   }
90 
91   static func initializeDatabase(
92     withSchema schema: String,
93     filename: String,
94     inDirectory directory: URL,
95     shouldMigrate: Bool,
96     migrations: [UpdatesDatabaseMigration]
97   ) throws -> OpaquePointer {
98     let dbUrl = directory.appendingPathComponent(filename)
99     var shouldInitializeDatabaseSchema = !FileManager.default.fileExists(atPath: dbUrl.path)
100 
101     let success = migrateDatabase(inDirectory: directory, migrations: migrations)
102     if !success {
103       if FileManager.default.fileExists(atPath: dbUrl.path) {
104         do {
105           try FileManager.default.removeItem(atPath: dbUrl.path)
106         } catch {
107           throw UpdatesDatabaseInitializationError.migrateAndRemoveOldDatabaseFailure
108         }
109       }
110       shouldInitializeDatabaseSchema = true
111     } else {
112       shouldInitializeDatabaseSchema = false
113     }
114 
115     var dbInit: OpaquePointer?
116     let resultCode = sqlite3_open(dbUrl.path, &dbInit)
117 
118     guard var db = dbInit else {
119       throw UpdatesDatabaseInitializationError.openDatabaseFalure
120     }
121 
122     if resultCode != SQLITE_OK {
123       NSLog("Error opening SQLite db: %@", [UpdatesDatabaseUtils.errorCodesAndMessage(fromSqlite: db).message])
124       sqlite3_close(db)
125 
126       if resultCode == SQLITE_CORRUPT || resultCode == SQLITE_NOTADB {
127         let archivedDbFilename = String(format: "%f-%@", Date().timeIntervalSince1970, filename)
128         let destinationUrl = directory.appendingPathComponent(archivedDbFilename)
129         do {
130           try FileManager.default.moveItem(at: dbUrl, to: destinationUrl)
131         } catch {
132           throw UpdatesDatabaseInitializationError.moveExistingCorruptedDatabaseFailure
133         }
134 
135         NSLog("Moved corrupt SQLite db to %@", archivedDbFilename)
136         var dbInit2: OpaquePointer?
137         guard sqlite3_open(dbUrl.path, &dbInit2) == SQLITE_OK else {
138           throw UpdatesDatabaseInitializationError.openAfterMovingCorruptedDatabaseFailure
139         }
140 
141         guard let db2 = dbInit2 else {
142           throw UpdatesDatabaseInitializationError.openDatabaseFalure
143         }
144         db = db2
145 
146         shouldInitializeDatabaseSchema = true
147       } else {
148         throw UpdatesDatabaseInitializationError.openInitialDatabaseOtherFailure
149       }
150     }
151 
152     // foreign keys must be turned on explicitly for each database connection
153     do {
154       _ = try UpdatesDatabaseUtils.execute(sql: "PRAGMA foreign_keys=ON;", withArgs: nil, onDatabase: db)
155     } catch {
156       NSLog("Error turning on foreign key constraint: %@", [error.localizedDescription])
157     }
158 
159     if shouldInitializeDatabaseSchema {
160       guard sqlite3_exec(db, String(schema.utf8), nil, nil, nil) == SQLITE_OK else {
161         throw UpdatesDatabaseInitializationError.databseSchemaInitializationFailure
162       }
163     }
164 
165     return db
166   }
167 
migrateDatabasenull168   private static func migrateDatabase(inDirectory directory: URL, migrations: [UpdatesDatabaseMigration]) -> Bool {
169     let latestURL = directory.appendingPathComponent(LatestFilename)
170     if FileManager.default.fileExists(atPath: latestURL.path) {
171       return true
172     }
173 
174     // find the newest database version that exists and try to migrate that file (ignore any older ones)
175     var existingURL: URL?
176     var startingMigrationIndex: Int = 0
177     for (idx, migration) in migrations.enumerated() {
178       let possibleURL = directory.appendingPathComponent(migration.filename)
179       if FileManager.default.fileExists(atPath: possibleURL.path) {
180         existingURL = possibleURL
181         startingMigrationIndex = idx
182         break
183       }
184     }
185 
186     guard let existingURL = existingURL else {
187       return false
188     }
189 
190     do {
191       try FileManager.default.moveItem(atPath: existingURL.path, toPath: latestURL.path)
192     } catch {
193       NSLog("Migration failed: failed to rename database file")
194       return false
195     }
196 
197     var db: OpaquePointer?
198     if sqlite3_open(latestURL.path, &db) != SQLITE_OK {
199       NSLog("Error opening migrated SQLite db: %@", [UpdatesDatabaseUtils.errorCodesAndMessage(fromSqlite: db!).message])
200       sqlite3_close(db)
201       return false
202     }
203 
204     // turn on foreign keys for database before migration in case the first migration being executed depends on them being on
205     do {
206       _ = try UpdatesDatabaseUtils.execute(sql: "PRAGMA foreign_keys=ON;", withArgs: nil, onDatabase: db!)
207     } catch {
208       NSLog("Error turning on foreign key constraint: %@", [error.localizedDescription])
209     }
210 
211     for index in startingMigrationIndex..<migrations.count {
212       let migration = migrations[index]
213       do {
214         try migration.runMigration(onDatabase: db!)
215       } catch {
216         NSLog("Error migrating SQLite db: %@", [UpdatesDatabaseUtils.errorCodesAndMessage(fromSqlite: db!).message])
217         sqlite3_close(db)
218         return false
219       }
220     }
221 
222     // migration was successful
223     sqlite3_close(db)
224     return true
225   }
226 }
227