Analytics/SQLite/SFSQLite.m

   1 /*
   2  * Copyright (c) 2017 Apple Inc. All Rights Reserved.
   3  *
   4  * @APPLE_LICENSE_HEADER_START@
   5  *
   6  * This file contains Original Code and/or Modifications of Original Code
   7  * as defined in and that are subject to the Apple Public Source License
   8  * Version 2.0 (the 'License'). You may not use this file except in
   9  * compliance with the License. Please obtain a copy of the License at
  10  * http://www.opensource.apple.com/apsl/ and read it before using this
  11  * file.
  12  *
  13  * The Original Code and all software distributed under the License are
  14  * distributed on an 'AS IS' basis, WITHOUT WARRANTY OF ANY KIND, EITHER
  15  * EXPRESS OR IMPLIED, AND APPLE HEREBY DISCLAIMS ALL SUCH WARRANTIES,
  16  * INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY,
  17  * FITNESS FOR A PARTICULAR PURPOSE, QUIET ENJOYMENT OR NON-INFRINGEMENT.
  18  * Please see the License for the specific language governing rights and
  19  * limitations under the License.
  20  *
  21  * @APPLE_LICENSE_HEADER_END@
  22  */
  23
  24 #import "SFSQLite.h"
  25 #import "SFSQLiteStatement.h"
  26 #include <sqlite3.h>
  27 #include <CommonCrypto/CommonDigest.h>
  28
  29
  30 #define kSFSQLiteBusyTimeout       (5*60*1000)
  31
  32 // Vaccuum our databases approximately once a week
  33 #define kCKSQLVacuumInterval       ((60*60*24)*7)
  34 #define kSFSQLiteLastVacuumKey     @"LastVacuum"
  35
  36 #define kSFSQLiteSchemaVersionKey  @"SchemaVersion"
  37 #define kSFSQLiteCreatedDateKey    @"Created"
  38
  39 static NSString *const kSFSQLiteCreatePropertiesTableSQL =
  40     @"create table if not exists Properties (\n"
  41     @"    key    text primary key,\n"
  42     @"    value  text\n"
  43     @");\n";
  44
  45 @interface SFSQLiteError : NSObject
  46 + (void)raise:(NSString *)reason code:(int)code extended:(int)extended;
  47 @end
  48
  49
  50 NSArray *SFSQLiteJournalSuffixes() {
  51     return @[@"-journal", @"-wal", @"-shm"];
  52 }
  53
  54 @interface NSObject (SFSQLiteAdditions)
  55 + (NSString *)SFSQLiteClassName;
  56 @end
  57
  58 @implementation NSObject (SFSQLiteAdditions)
  59 + (NSString *)SFSQLiteClassName {
  60     return NSStringFromClass(self);
  61 }
  62 @end
  63
  64 @interface SFSQLite ()
  65
  66 @property (nonatomic, assign)            sqlite3                *db;
  67 @property (nonatomic, assign)            NSUInteger              openCount;
  68 @property (nonatomic, assign)            BOOL                    corrupt;
  69 @property (nonatomic, readonly, strong)  NSMutableDictionary    *statementsBySQL;
  70 @property (nonatomic, strong)            NSDateFormatter        *dateFormatter;
  71
  72 @end
  73
  74 static char intToHexChar(uint8_t i)
  75 {
  76     return i >= 10 ? 'a' + i - 10 : '0' + i;
  77 }
  78
  79 static char *SecHexCharFromBytes(const uint8_t *bytes, NSUInteger length, NSUInteger *outlen) {
  80     // Fudge the math a bit on the assert because we don't want a 1GB string anyway
  81     if (length > (NSUIntegerMax / 3)) {
  82         return nil;
  83     }
  84     char *hex = calloc(1, length * 2 * 9 / 8); // 9/8 so we can inline ' ' between every 8 character sequence
  85     char *destPtr = hex;
  86
  87     NSUInteger i;
  88
  89     for (i = 0; length > 4; i += 4, length -= 4) {
  90         for (NSUInteger offset = 0; offset < 4; offset++) {
  91             *destPtr++ = intToHexChar((bytes[i+offset] & 0xF0) >> 4);
  92             *destPtr++ = intToHexChar(bytes[i+offset] & 0x0F);
  93         }
  94         *destPtr++ = ' ';
  95     }
  96
  97     /* Using the same i from the above loop */
  98     for (; length > 0; i++, length--) {
  99         *destPtr++ = intToHexChar((bytes[i] & 0xF0) >> 4);
 100         *destPtr++ = intToHexChar(bytes[i] & 0x0F);
 101     }
 102
 103     if (outlen) *outlen = destPtr - hex;
 104
 105     return hex;
 106 }
 107
 108 static BOOL SecCreateDirectoryAtPath(NSString *path, NSError **error) {
 109     BOOL success = YES;
 110     NSError *localError;
 111     NSFileManager *fileManager = [NSFileManager defaultManager];
 112
 113     if (![fileManager createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:&localError]) {
 114         if (![localError.domain isEqualToString:NSCocoaErrorDomain] || localError.code != NSFileWriteFileExistsError) {
 115             success = NO;
 116         }
 117     }
 118
 119 #if TARGET_OS_IPHONE
 120     if (success) {
 121         NSDictionary *attributes = [fileManager attributesOfItemAtPath:path error:&localError];
 122         if (![attributes[NSFileProtectionKey] isEqualToString:NSFileProtectionCompleteUntilFirstUserAuthentication]) {
 123             [fileManager setAttributes:@{ NSFileProtectionKey: NSFileProtectionCompleteUntilFirstUserAuthentication }
 124                           ofItemAtPath:path error:nil];
 125         }
 126     }
 127 #endif
 128     if (!success) {
 129         if (error) *error = localError;
 130     }
 131     return success;
 132 }
 133
 134 @implementation NSData (CKUtilsAdditions)
 135
 136 - (NSString *)CKHexString {
 137     NSUInteger hexLen = 0;
 138     NS_VALID_UNTIL_END_OF_SCOPE NSData *arcSafeSelf = self;
 139     char *hex = SecHexCharFromBytes([arcSafeSelf bytes], [arcSafeSelf length], &hexLen);
 140     return [[NSString alloc] initWithBytesNoCopy:hex length:hexLen encoding:NSASCIIStringEncoding freeWhenDone:YES];
 141 }
 142
 143 - (NSString *)CKLowercaseHexStringWithoutSpaces {
 144     NSMutableString *retVal = [[self CKHexString] mutableCopy];
 145     [retVal replaceOccurrencesOfString:@" " withString:@"" options:0 range:NSMakeRange(0, [retVal length])];
 146     return retVal;
 147 }
 148
 149 - (NSString *)CKUppercaseHexStringWithoutSpaces {
 150     NSMutableString *retVal = [[[self CKHexString] uppercaseString] mutableCopy];
 151     [retVal replaceOccurrencesOfString:@" " withString:@"" options:0 range:NSMakeRange(0, [retVal length])];
 152     return retVal;
 153 }
 154
 155 + (NSData *)CKDataWithHexString:(NSString *)hexString stringIsUppercase:(BOOL)stringIsUppercase {
 156     NSMutableData *retVal = [[NSMutableData alloc] init];
 157     NSCharacterSet *hexCharacterSet = nil;
 158     char aChar;
 159     if (stringIsUppercase) {
 160         hexCharacterSet = [NSCharacterSet characterSetWithCharactersInString:@"0123456789ABCDEF"];
 161         aChar = 'A';
 162     } else {
 163         hexCharacterSet = [NSCharacterSet characterSetWithCharactersInString:@"0123456789abcdef"];
 164         aChar = 'a';
 165     }
 166
 167     unsigned int i;
 168     for (i = 0; i < [hexString length] ; ) {
 169         BOOL validFirstByte = NO;
 170         BOOL validSecondByte = NO;
 171         unichar firstByte = 0;
 172         unichar secondByte = 0;
 173
 174         for ( ; i < [hexString length]; i++) {
 175             firstByte = [hexString characterAtIndex:i];
 176             if ([hexCharacterSet characterIsMember:firstByte]) {
 177                 i++;
 178                 validFirstByte = YES;
 179                 break;
 180             }
 181         }
 182         for ( ; i < [hexString length]; i++) {
 183             secondByte = [hexString characterAtIndex:i];
 184             if ([hexCharacterSet characterIsMember:secondByte]) {
 185                 i++;
 186                 validSecondByte = YES;
 187                 break;
 188             }
 189         }
 190         if (!validFirstByte || !validSecondByte) {
 191             goto allDone;
 192         }
 193         if ((firstByte >= '0') && (firstByte <= '9')) {
 194             firstByte -= '0';
 195         } else {
 196             firstByte = firstByte - aChar + 10;
 197         }
 198         if ((secondByte >= '0') && (secondByte <= '9')) {
 199             secondByte -= '0';
 200         } else {
 201             secondByte = secondByte - aChar + 10;
 202         }
 203         char totalByteValue = (char)((firstByte << 4) + secondByte);
 204
 205         [retVal appendBytes:&totalByteValue length:1];
 206     }
 207 allDone:
 208     return retVal;
 209 }
 210
 211 + (NSData *)CKDataWithHexString:(NSString *)hexString {
 212     return [self CKDataWithHexString:hexString stringIsUppercase:NO];
 213 }
 214
 215 @end
 216
 217 @implementation SFSQLite
 218
 219 @synthesize delegate = _delegate;
 220 @synthesize path = _path;
 221 @synthesize schema = _schema;
 222 @synthesize schemaVersion = _schemaVersion;
 223 @synthesize objectClassPrefix = _objectClassPrefix;
 224 @synthesize userVersion = _userVersion;
 225 @synthesize synchronousMode = _synchronousMode;
 226 @synthesize hasMigrated = _hasMigrated;
 227 @synthesize shouldVacuum = _shouldVacuum;
 228 @synthesize traced = _traced;
 229 @synthesize db = _db;
 230 @synthesize openCount = _openCount;
 231 @synthesize corrupt = _corrupt;
 232 @synthesize statementsBySQL = _statementsBySQL;
 233 @synthesize dateFormatter = _dateFormatter;
 234 #if DEBUG
 235 @synthesize unitTestOverrides = _unitTestOverrides;
 236 #endif
 237
 238 - (instancetype)initWithPath:(NSString *)path schema:(NSString *)schema {
 239     if ((self = [super init])) {
 240         NSAssert([path length], @"Can't init a database with a zero-length path");
 241         _path = path;
 242         _schema = schema;
 243         _schemaVersion = [self _createSchemaHash];
 244         _statementsBySQL = [[NSMutableDictionary alloc] init];
 245         _objectClassPrefix = @"CK";
 246         _synchronousMode = SFSQLiteSynchronousModeNormal;
 247         _hasMigrated = NO;
 248         _shouldVacuum = YES;
 249     }
 250     return self;
 251 }
 252
 253 - (void)dealloc {
 254     @autoreleasepool {
 255         [self close];
 256     }
 257 }
 258
 259 - (SInt32)userVersion {
 260     if (self.delegate) {
 261         return self.delegate.userVersion;
 262     }
 263     return _userVersion;
 264 }
 265
 266 - (NSString *)_synchronousModeString {
 267     switch (self.synchronousMode) {
 268         case SFSQLiteSynchronousModeOff:
 269             return @"off";
 270         case SFSQLiteSynchronousModeFull:
 271             return @"full";
 272         case SFSQLiteSynchronousModeNormal:
 273             break;
 274         default:
 275             assert(0 && "Unknown synchronous mode");
 276     }
 277     return @"normal";
 278 }
 279
 280 - (NSString *)_createSchemaHash {
 281     unsigned char hashBuffer[CC_SHA256_DIGEST_LENGTH] = {0};
 282     NSData *hashData = [NSData dataWithBytesNoCopy:hashBuffer length:CC_SHA256_DIGEST_LENGTH freeWhenDone:NO];
 283     NS_VALID_UNTIL_END_OF_SCOPE NSData *schemaData = [self.schema dataUsingEncoding:NSUTF8StringEncoding];
 284     CC_SHA256([schemaData bytes], (CC_LONG)[schemaData length], hashBuffer);
 285     return [hashData CKUppercaseHexStringWithoutSpaces];
 286 }
 287
 288 - (BOOL)isOpen {
 289     return _db != NULL;
 290 }
 291
 292 - (void)_periodicVacuum {
 293     // "When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit.
 294     // Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does.
 295     // In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse."
 296     // https://sqlite.org/pragma.html#pragma_auto_vacuum
 297     NSDate *lastVacuumDate = [NSDate dateWithTimeIntervalSinceReferenceDate:[[self propertyForKey:kSFSQLiteLastVacuumKey] floatValue]];
 298     if ([lastVacuumDate timeIntervalSinceNow] < -(kCKSQLVacuumInterval)) {
 299         [self executeSQL:@"VACUUM"];
 300
 301         NSString *vacuumDateString = [NSString stringWithFormat:@"%f", [[NSDate date] timeIntervalSinceReferenceDate]];
 302         [self setProperty:vacuumDateString forKey:kSFSQLiteLastVacuumKey];
 303     }
 304 }
 305
 306 - (BOOL)openWithError:(NSError **)error {
 307     BOOL success = NO;
 308     NSError *localError;
 309     NSString *dbSchemaVersion, *dir;
 310     NSArray *results;
 311     NS_VALID_UNTIL_END_OF_SCOPE NSString *arcSafePath = _path;
 312
 313     if (_openCount > 0) {
 314         NSAssert(_db != NULL, @"Missing handle for open cache db");
 315         _openCount += 1;
 316         success = YES;
 317         goto done;
 318     }
 319
 320     // Create the directory for the cache.
 321     dir = [_path stringByDeletingLastPathComponent];
 322     if (!SecCreateDirectoryAtPath(dir, &localError)) {
 323         goto done;
 324     }
 325
 326     int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
 327 #if TARGET_OS_IPHONE
 328     flags |= SQLITE_OPEN_FILEPROTECTION_COMPLETEUNTILFIRSTUSERAUTHENTICATION;
 329 #endif
 330     int rc = sqlite3_open_v2([arcSafePath fileSystemRepresentation], &_db, flags, NULL);
 331     if (rc != SQLITE_OK) {
 332         localError = [NSError errorWithDomain:NSCocoaErrorDomain code:0 userInfo:@{NSLocalizedDescriptionKey : [NSString stringWithFormat:@"Error opening db at %@, rc=%d(0x%x)", _path, rc, rc]}];
 333         goto done;
 334     }
 335     sqlite3_extended_result_codes(_db, 1);
 336     rc = sqlite3_busy_timeout(_db, kSFSQLiteBusyTimeout);
 337     if (rc != SQLITE_OK) {
 338         goto done;
 339     }
 340
 341     // You don't argue with the Ben: rdar://12685305
 342     [self executeSQL:@"pragma journal_mode = WAL"];
 343     [self executeSQL:@"pragma synchronous = %@", [self _synchronousModeString]];
 344     [self executeSQL:@"pragma auto_vacuum = FULL"];
 345
 346     // rdar://problem/32168789
 347     // [self executeSQL:@"pragma foreign_keys = 1"];
 348
 349     // Initialize the db within a transaction in case there is a crash between creating the schema and setting the
 350     // schema version, and to avoid multiple threads trying to re-create the db at once.
 351     [self begin];
 352
 353     // Create the Properties table before trying to read the schema version from it. If the Properties table doesn't
 354     // exist we can't prepare a statement to access it.
 355     results = [self select:@[@"name"] from:@"sqlite_master" where:@"type = ? AND name = ?" bindings:@[@"table", @"Properties"]];
 356     if (!results.count) {
 357         [self executeSQL:kSFSQLiteCreatePropertiesTableSQL];
 358     }
 359
 360     // Check the schema version and create or re-create the db if needed.
 361     BOOL create = NO;
 362     dbSchemaVersion = [self propertyForKey:kSFSQLiteSchemaVersionKey];
 363     SInt32 dbUserVersion = [self dbUserVersion];
 364
 365     if (!dbSchemaVersion) {
 366         // The schema version isn't set so the db was just created or we failed to initialize it previously.
 367         create = YES;
 368     } else if (![dbSchemaVersion isEqualToString:self.schemaVersion]
 369                || (self.userVersion && dbUserVersion != self.userVersion)) {
 370
 371         if (self.delegate && [self.delegate migrateDatabase:self fromVersion:dbUserVersion]) {
 372             _hasMigrated = YES;
 373         }
 374
 375         if (!_hasMigrated) {
 376             // The schema version doesn't match and we haven't migrated to the new version. Give up and throw away the db and re-create it instead of trying to migrate.
 377             [self removeAllStatements];
 378             [self dropAllTables];
 379             create = YES;
 380             _hasMigrated = YES;
 381         }
 382     }
 383     if (create) {
 384         [self executeSQL:kSFSQLiteCreatePropertiesTableSQL];
 385         [self executeSQL:@"%@", self.schema];
 386         NSString *createdDateString = [NSString stringWithFormat:@"%f", [[NSDate date] timeIntervalSinceReferenceDate]];
 387         [self setProperty:createdDateString forKey:kSFSQLiteCreatedDateKey];
 388     }
 389
 390     [self end];
 391
 392 #if DEBUG
 393     // TODO: <rdar://problem/33115830> Resolve Race Condition When Setting 'userVersion/schemaVersion' in SFSQLite
 394     if ([self.unitTestOverrides[@"RacyUserVersionUpdate"] isEqual:@YES]) {
 395         success = YES;
 396         goto done;
 397     }
 398 #endif
 399
 400     if (self.shouldVacuum) [self _periodicVacuum];
 401
 402     if (create || _hasMigrated) {
 403         [self setProperty:self.schemaVersion forKey:kSFSQLiteSchemaVersionKey];
 404         if (self.userVersion) {
 405             [self executeSQL:@"pragma user_version = %ld", (long)self.userVersion];
 406         }
 407     }
 408
 409     _openCount += 1;
 410     success = YES;
 411
 412 done:
 413     if (!success && error) {
 414         *error = localError;
 415     }
 416     return success;
 417 }
 418
 419 - (void)open {
 420     NSError *error;
 421     if (![self openWithError:&error]) {
 422         [self raise:@"Error opening db at %@: %@", self.path, error];
 423     }
 424 }
 425
 426
 427 - (void)close {
 428     if (_openCount > 0) {
 429         if (_openCount == 1) {
 430             NSAssert(_db != NULL, @"Missing handle for open cache db");
 431
 432             [self removeAllStatements];
 433
 434             if (sqlite3_close(_db)) {
 435                 [self raise:@"Error closing database"];
 436             }
 437             _db = NULL;
 438         }
 439         _openCount -= 1;
 440     }
 441 }
 442
 443 - (void)remove {
 444     NSAssert(_openCount == 0, @"Trying to remove db at: %@ while it is open", _path);
 445     [[NSFileManager defaultManager] removeItemAtPath:_path error:nil];
 446     for (NSString *suffix in SFSQLiteJournalSuffixes()) {
 447         [[NSFileManager defaultManager] removeItemAtPath:[_path stringByAppendingString:suffix] error:nil];
 448     }
 449 }
 450
 451 - (void)begin {
 452     [self executeSQL:@"begin exclusive"];
 453 }
 454
 455 - (void)end {
 456     [self executeSQL:@"end"];
 457 }
 458
 459 - (void)rollback {
 460     [self executeSQL:@"rollback"];
 461 }
 462
 463 - (void)analyze {
 464     [self executeSQL:@"analyze"];
 465 }
 466
 467 - (void)vacuum {
 468     [self executeSQL:@"vacuum"];
 469 }
 470
 471 - (void)raise:(NSString *)format, ... {
 472     va_list args;
 473     va_start(args, format);
 474
 475     NSString *reason = [[NSString alloc] initWithFormat:format arguments:args];
 476
 477     int code = 0;
 478     int extendedCode = 0;
 479     if (_db) {
 480         code = sqlite3_errcode(_db) & 0xFF;
 481         extendedCode = sqlite3_extended_errcode(_db);
 482         const char *errmsg = sqlite3_errmsg(_db);
 483
 484         NSDictionary *dbAttrs = [[NSFileManager defaultManager] attributesOfItemAtPath:self.path error:NULL];
 485         NSDictionary *fsAttrs = [[NSFileManager defaultManager] attributesOfFileSystemForPath:self.path error:NULL];
 486         reason = [reason stringByAppendingFormat:@" - errcode:%04x, msg:\"%s\", size: %@, path:%@, fs:%@/%@", extendedCode, errmsg, dbAttrs[NSFileSize], _path, fsAttrs[NSFileSystemFreeSize], fsAttrs[NSFileSystemSize]];
 487
 488         if (!_corrupt && (code == SQLITE_CORRUPT || code == SQLITE_NOTADB)) {
 489             _corrupt = YES;
 490
 491             @try {
 492                 [self close];
 493             } @catch (NSException *x) {
 494                 NSLog(@"Warn: Error closing corrupt db: %@", x);
 495             }
 496
 497             [self remove];
 498         }
 499     }
 500
 501     va_end(args);
 502
 503     [SFSQLiteError raise:reason code:code extended:extendedCode];
 504 }
 505
 506 - (SFSQLiteRowID)lastInsertRowID {
 507     if (!_db) {
 508         [self raise:@"Database is closed"];
 509     }
 510
 511     return sqlite3_last_insert_rowid(_db);
 512 }
 513
 514 - (int)changes
 515 {
 516     if (!_db) {
 517         [self raise:@"Database is closed"];
 518     }
 519
 520     return sqlite3_changes(_db);
 521 }
 522
 523 - (void)executeSQL:(NSString *)format, ... {
 524     va_list args;
 525     va_start(args, format);
 526     [self executeSQL:format arguments:args];
 527     va_end(args);
 528 }
 529
 530 - (void)executeSQL:(NSString *)format arguments:(va_list)args {
 531     NS_VALID_UNTIL_END_OF_SCOPE NSString *SQL = [[NSString alloc] initWithFormat:format arguments:args];
 532     if (!_db) {
 533         [self raise:@"Database is closed"];
 534     }
 535     int execRet = sqlite3_exec(_db, [SQL UTF8String], NULL, NULL, NULL);
 536     if (execRet != SQLITE_OK) {
 537         [self raise:@"Error executing SQL: \"%@\" (%d)", SQL, execRet];
 538     }
 539 }
 540
 541 - (SFSQLiteStatement *)statementForSQL:(NSString *)SQL {
 542     if (!_db) {
 543         [self raise:@"Database is closed"];
 544     }
 545
 546     SFSQLiteStatement *statement = _statementsBySQL[SQL];
 547     if (statement) {
 548         NSAssert(statement.isReset, @"Statement not reset after last use: \"%@\"", SQL);
 549     } else {
 550         sqlite3_stmt *handle = NULL;
 551         NS_VALID_UNTIL_END_OF_SCOPE NSString *arcSafeSQL = SQL;
 552         if (sqlite3_prepare_v2(_db, [arcSafeSQL UTF8String], -1, &handle, NULL)) {
 553             [self raise:@"Error preparing statement: %@", SQL];
 554         }
 555
 556         statement = [[SFSQLiteStatement alloc] initWithSQLite:self SQL:SQL handle:handle];
 557         _statementsBySQL[SQL] = statement;
 558     }
 559
 560     return statement;
 561 }
 562
 563 - (void)removeAllStatements {
 564     [[_statementsBySQL allValues] makeObjectsPerformSelector:@selector(finalizeStatement)];
 565     [_statementsBySQL removeAllObjects];
 566 }
 567
 568 - (NSArray *)allTableNames {
 569     NSMutableArray *tableNames = [[NSMutableArray alloc] init];
 570
 571     SFSQLiteStatement *statement = [self statementForSQL:@"select name from sqlite_master where type = 'table'"];
 572     while ([statement step]) {
 573         NSString *name = [statement textAtIndex:0];
 574         [tableNames addObject:name];
 575     }
 576     [statement reset];
 577
 578     return tableNames;
 579 }
 580
 581 - (void)dropAllTables {
 582     for (NSString *tableName in [self allTableNames]) {
 583         [self executeSQL:@"drop table %@", tableName];
 584     }
 585 }
 586
 587 - (NSString *)propertyForKey:(NSString *)key {
 588     NSAssert(key, @"Null key");
 589
 590     NSString *value = nil;
 591
 592     SFSQLiteStatement *statement = [self statementForSQL:@"select value from Properties where key = ?"];
 593     [statement bindText:key atIndex:0];
 594     if ([statement step]) {
 595         value = [statement textAtIndex:0];
 596     }
 597     [statement reset];
 598
 599     return value;
 600 }
 601
 602 - (void)setProperty:(NSString *)value forKey:(NSString *)key {
 603     NSAssert(key, @"Null key");
 604
 605     if (value) {
 606         SFSQLiteStatement *statement = [self statementForSQL:@"insert or replace into Properties (key, value) values (?,?)"];
 607         [statement bindText:key atIndex:0];
 608         [statement bindText:value atIndex:1];
 609         [statement step];
 610         [statement reset];
 611     } else {
 612         [self removePropertyForKey:key];
 613     }
 614 }
 615
 616 - (NSDateFormatter *)dateFormatter {
 617     if (!_dateFormatter) {
 618         NSDateFormatter* dateFormatter = [NSDateFormatter new];
 619         dateFormatter.dateFormat = @"yyyy-MM-dd'T'HH:mm:ssZZZZZ";
 620         _dateFormatter = dateFormatter;
 621     }
 622     return _dateFormatter;
 623 }
 624
 625 - (NSDate *)datePropertyForKey:(NSString *)key {
 626     NSString *dateStr = [self propertyForKey:key];
 627     if (dateStr.length) {
 628         return [self.dateFormatter dateFromString:dateStr];
 629     }
 630     return nil;
 631 }
 632
 633 - (void)setDateProperty:(NSDate *)value forKey:(NSString *)key {
 634     NSString *dateStr = nil;
 635     if (value) {
 636         dateStr = [self.dateFormatter stringFromDate:value];
 637     }
 638     [self setProperty:dateStr forKey:key];
 639 }
 640
 641 - (void)removePropertyForKey:(NSString *)key {
 642     NSAssert(key, @"Null key");
 643
 644     SFSQLiteStatement *statement = [self statementForSQL:@"delete from Properties where key = ?"];
 645     [statement bindText:key atIndex:0];
 646     [statement step];
 647     [statement reset];
 648 }
 649
 650 - (NSDate *)creationDate {
 651     return [NSDate dateWithTimeIntervalSinceReferenceDate:[[self propertyForKey:kSFSQLiteCreatedDateKey] floatValue]];
 652 }
 653
 654 // https://sqlite.org/pragma.html#pragma_table_info
 655 - (NSSet<NSString*> *)columnNamesForTable:(NSString*)tableName {
 656     SFSQLiteStatement *statement = [self statementForSQL:[NSString stringWithFormat:@"pragma table_info(%@)", tableName]];
 657     NSMutableSet<NSString*>* columnNames = [[NSMutableSet alloc] init];
 658     while ([statement step]) {
 659         [columnNames addObject:[statement textAtIndex:1]];
 660     }
 661     [statement reset];
 662     return columnNames;
 663 }
 664
 665 - (NSArray *)select:(NSArray *)columns from:(NSString *)tableName {
 666     return [self select:columns from:tableName where:nil bindings:nil];
 667 }
 668
 669 - (NSArray *)select:(NSArray *)columns from:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 670     NSMutableArray *results = [[NSMutableArray alloc] init];
 671
 672     NSMutableString *SQL = [NSMutableString stringWithFormat:@"select %@ from %@", [columns componentsJoinedByString:@", "], tableName];
 673     if (whereSQL) {
 674         [SQL appendFormat:@" where %@", whereSQL];
 675     }
 676
 677     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 678     [statement bindValues:bindings];
 679     while ([statement step]) {
 680         [results addObject:[statement allObjectsByColumnName]];
 681     }
 682     [statement reset];
 683
 684     return results;
 685 }
 686
 687 - (void)select:(NSArray *)columns from:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings orderBy:(NSArray *)orderBy limit:(NSNumber *)limit block:(void (^)(NSDictionary *resultDictionary, BOOL *stop))block {
 688     @autoreleasepool {
 689         NSMutableString *SQL = [[NSMutableString alloc] init];
 690         NSString *columnsString = @"*";
 691         if ([columns count]) columnsString = [columns componentsJoinedByString:@", "];
 692         [SQL appendFormat:@"select %@ from %@", columnsString, tableName];
 693
 694         if (whereSQL.length) {
 695             [SQL appendFormat:@" where %@", whereSQL];
 696         }
 697         if (orderBy) {
 698             NSString *orderByString = [orderBy componentsJoinedByString:@", "];
 699             [SQL appendFormat:@" order by %@", orderByString];
 700         }
 701         if (limit) {
 702             [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 703         }
 704
 705         SFSQLiteStatement *statement = [self statementForSQL:SQL];
 706         [statement bindValues:bindings];
 707         do {
 708             @autoreleasepool {
 709                 if (![statement step]) {
 710                     break;
 711                 }
 712                 NSDictionary *stepResult = [statement allObjectsByColumnName];
 713                 if (block) {
 714                     BOOL stop = NO;
 715                     block(stepResult, &stop);
 716                     if (stop) {
 717                         break;
 718                     }
 719                 }
 720             }
 721         } while (1);
 722         [statement reset];
 723     }
 724 }
 725
 726 - (void)selectFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings orderBy:(NSArray *)orderBy limit:(NSNumber *)limit block:(void (^)(NSDictionary *resultDictionary, BOOL *stop))block {
 727     @autoreleasepool {
 728         NSMutableString *SQL = [[NSMutableString alloc] init];
 729         [SQL appendFormat:@"select * from %@", tableName];
 730
 731         if (whereSQL.length) {
 732             [SQL appendFormat:@" where %@", whereSQL];
 733         }
 734         if (orderBy) {
 735             NSString *orderByString = [orderBy componentsJoinedByString:@", "];
 736             [SQL appendFormat:@" order by %@", orderByString];
 737         }
 738         if (limit) {
 739             [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 740         }
 741
 742         SFSQLiteStatement *statement = [self statementForSQL:SQL];
 743         [statement bindValues:bindings];
 744         do {
 745             @autoreleasepool {
 746                 if (![statement step]) {
 747                     break;
 748                 }
 749                 NSDictionary *stepResult = [statement allObjectsByColumnName];
 750                 if (block) {
 751                     BOOL stop = NO;
 752                     block(stepResult, &stop);
 753                     if (stop) {
 754                         break;
 755                     }
 756                 }
 757             }
 758         } while (1);
 759         [statement reset];
 760     }
 761 }
 762
 763 - (NSArray *)selectFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings limit:(NSNumber *)limit {
 764     NSMutableString *SQL = [[NSMutableString alloc] init];
 765     [SQL appendFormat:@"select * from %@", tableName];
 766
 767     if (whereSQL.length) {
 768         [SQL appendFormat:@" where %@", whereSQL];
 769     }
 770     if (limit) {
 771         [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 772     }
 773
 774     NSMutableArray *results = [[NSMutableArray alloc] init];
 775
 776     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 777     [statement bindValues:bindings];
 778     while ([statement step]) {
 779         [results addObject:[statement allObjectsByColumnName]];
 780     }
 781     [statement reset];
 782
 783     return results;
 784 }
 785
 786 - (void)update:(NSString *)tableName set:(NSString *)setSQL where:(NSString *)whereSQL bindings:(NSArray *)whereBindings limit:(NSNumber *)limit {
 787     NSMutableString *SQL = [[NSMutableString alloc] init];
 788     [SQL appendFormat:@"update %@", tableName];
 789
 790     NSAssert(setSQL.length > 0, @"null set expression");
 791
 792     [SQL appendFormat:@" set %@", setSQL];
 793     if (whereSQL.length) {
 794         [SQL appendFormat:@" where %@", whereSQL];
 795     }
 796     if (limit) {
 797         [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 798     }
 799
 800     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 801     [statement bindValues:whereBindings];
 802     while ([statement step]) {
 803     }
 804     [statement reset];
 805 }
 806
 807 - (NSArray *)selectAllFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 808     return [self selectFrom:tableName where:whereSQL bindings:bindings limit:nil];
 809 }
 810
 811 - (NSUInteger)selectCountFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 812     NSArray *results = [self select:@[@"count(*) as n"] from:tableName where:whereSQL bindings:bindings];
 813     return [results[0][@"n"] unsignedIntegerValue];
 814 }
 815
 816 - (SFSQLiteRowID)insertOrReplaceInto:(NSString *)tableName values:(NSDictionary *)valuesByColumnName {
 817     NSArray *columnNames = [[valuesByColumnName allKeys] sortedArrayUsingSelector:@selector(compare:)];
 818     NSMutableArray *values = [[NSMutableArray alloc] init];
 819     for (NSUInteger i = 0; i < columnNames.count; i++) {
 820         values[i] = valuesByColumnName[columnNames[i]];
 821     }
 822
 823     NSMutableString *SQL = [[NSMutableString alloc] initWithString:@"insert or replace into "];
 824     [SQL appendString:tableName];
 825     [SQL appendString:@" ("];
 826     for (NSUInteger i = 0; i < columnNames.count; i++) {
 827         [SQL appendString:columnNames[i]];
 828         if (i != columnNames.count-1) {
 829             [SQL appendString:@","];
 830         }
 831     }
 832     [SQL appendString:@") values ("];
 833     for (NSUInteger i = 0; i < columnNames.count; i++) {
 834         if (i != columnNames.count-1) {
 835             [SQL appendString:@"?,"];
 836         } else {
 837             [SQL appendString:@"?"];
 838         }
 839     }
 840     [SQL appendString:@")"];
 841
 842     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 843     [statement bindValues:values];
 844     [statement step];
 845     [statement reset];
 846
 847     return [self lastInsertRowID];
 848 }
 849
 850 - (void)deleteFrom:(NSString *)tableName matchingValues:(NSDictionary *)valuesByColumnName {
 851     NSArray *columnNames = [[valuesByColumnName allKeys] sortedArrayUsingSelector:@selector(compare:)];
 852     NSMutableArray *values = [[NSMutableArray alloc] init];
 853     NSMutableString *whereSQL = [[NSMutableString alloc] init];
 854     int bindingCount = 0;
 855     for (NSUInteger i = 0; i < columnNames.count; i++) {
 856         id value = valuesByColumnName[columnNames[i]];
 857         [whereSQL appendString:columnNames[i]];
 858         if (!value || [[NSNull null] isEqual:value]) {
 859             [whereSQL appendString:@" is NULL"];
 860         } else {
 861             values[bindingCount++] = value;
 862             [whereSQL appendString:@"=?"];
 863         }
 864         if (i != columnNames.count-1) {
 865             [whereSQL appendString:@" AND "];
 866         }
 867     }
 868     [self deleteFrom:tableName where:whereSQL bindings:values];
 869 }
 870
 871 - (void)deleteFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 872     NSString *SQL = [NSString stringWithFormat:@"delete from %@ where %@", tableName, whereSQL];
 873
 874     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 875     [statement bindValues:bindings];
 876     [statement step];
 877     [statement reset];
 878 }
 879
 880 - (NSString *)_tableNameForClass:(Class)objectClass {
 881     NSString *className = [objectClass SFSQLiteClassName];
 882     if (![className hasPrefix:_objectClassPrefix]) {
 883         [NSException raise:NSInvalidArgumentException format:@"Object class \"%@\" does not have prefix \"%@\"", className, _objectClassPrefix];
 884     }
 885     return [className substringFromIndex:_objectClassPrefix.length];
 886 }
 887
 888 - (SInt32)dbUserVersion {
 889     SInt32 userVersion = 0;
 890     SFSQLiteStatement *statement = [self statementForSQL:@"pragma user_version"];
 891     while ([statement step]) {
 892         userVersion = [statement intAtIndex:0];
 893     }
 894     [statement reset];
 895
 896     return userVersion;
 897 }
 898
 899 @end
 900
 901
 902 #define SFSQLiteErrorRaiseMethod(SQLiteError) + (void)SQLiteError:(NSString *)reason { [NSException raise:NSGenericException format:@"%@", reason]; }
 903 #define SFSQLiteErrorCase(SQLiteError) case SQLITE_ ## SQLiteError: [self SQLiteError:reason]; break
 904
 905 @implementation SFSQLiteError
 906
 907 // SQLite error codes
 908 SFSQLiteErrorRaiseMethod(ERROR)
 909 SFSQLiteErrorRaiseMethod(INTERNAL)
 910 SFSQLiteErrorRaiseMethod(PERM)
 911 SFSQLiteErrorRaiseMethod(ABORT)
 912 SFSQLiteErrorRaiseMethod(BUSY)
 913 SFSQLiteErrorRaiseMethod(LOCKED)
 914 SFSQLiteErrorRaiseMethod(NOMEM)
 915 SFSQLiteErrorRaiseMethod(READONLY)
 916 SFSQLiteErrorRaiseMethod(INTERRUPT)
 917 SFSQLiteErrorRaiseMethod(IOERR)
 918 SFSQLiteErrorRaiseMethod(CORRUPT)
 919 SFSQLiteErrorRaiseMethod(NOTFOUND)
 920 SFSQLiteErrorRaiseMethod(FULL)
 921 SFSQLiteErrorRaiseMethod(CANTOPEN)
 922 SFSQLiteErrorRaiseMethod(PROTOCOL)
 923 SFSQLiteErrorRaiseMethod(SCHEMA)
 924 SFSQLiteErrorRaiseMethod(TOOBIG)
 925 SFSQLiteErrorRaiseMethod(CONSTRAINT)
 926 SFSQLiteErrorRaiseMethod(MISMATCH)
 927 SFSQLiteErrorRaiseMethod(MISUSE)
 928 SFSQLiteErrorRaiseMethod(RANGE)
 929 SFSQLiteErrorRaiseMethod(NOTADB)
 930
 931 // SQLite extended error codes
 932 SFSQLiteErrorRaiseMethod(IOERR_READ)
 933 SFSQLiteErrorRaiseMethod(IOERR_SHORT_READ)
 934 SFSQLiteErrorRaiseMethod(IOERR_WRITE)
 935 SFSQLiteErrorRaiseMethod(IOERR_FSYNC)
 936 SFSQLiteErrorRaiseMethod(IOERR_DIR_FSYNC)
 937 SFSQLiteErrorRaiseMethod(IOERR_TRUNCATE)
 938 SFSQLiteErrorRaiseMethod(IOERR_FSTAT)
 939 SFSQLiteErrorRaiseMethod(IOERR_UNLOCK)
 940 SFSQLiteErrorRaiseMethod(IOERR_RDLOCK)
 941 SFSQLiteErrorRaiseMethod(IOERR_DELETE)
 942 SFSQLiteErrorRaiseMethod(IOERR_BLOCKED)
 943 SFSQLiteErrorRaiseMethod(IOERR_NOMEM)
 944 SFSQLiteErrorRaiseMethod(IOERR_ACCESS)
 945 SFSQLiteErrorRaiseMethod(IOERR_CHECKRESERVEDLOCK)
 946 SFSQLiteErrorRaiseMethod(IOERR_LOCK)
 947 SFSQLiteErrorRaiseMethod(IOERR_CLOSE)
 948 SFSQLiteErrorRaiseMethod(IOERR_DIR_CLOSE)
 949 SFSQLiteErrorRaiseMethod(IOERR_SHMOPEN)
 950 SFSQLiteErrorRaiseMethod(IOERR_SHMSIZE)
 951 SFSQLiteErrorRaiseMethod(IOERR_SHMLOCK)
 952 SFSQLiteErrorRaiseMethod(IOERR_SHMMAP)
 953 SFSQLiteErrorRaiseMethod(IOERR_SEEK)
 954 SFSQLiteErrorRaiseMethod(IOERR_DELETE_NOENT)
 955 SFSQLiteErrorRaiseMethod(IOERR_MMAP)
 956 SFSQLiteErrorRaiseMethod(IOERR_GETTEMPPATH)
 957 SFSQLiteErrorRaiseMethod(IOERR_CONVPATH)
 958 SFSQLiteErrorRaiseMethod(LOCKED_SHAREDCACHE)
 959 SFSQLiteErrorRaiseMethod(BUSY_RECOVERY)
 960 SFSQLiteErrorRaiseMethod(BUSY_SNAPSHOT)
 961 SFSQLiteErrorRaiseMethod(CANTOPEN_NOTEMPDIR)
 962 SFSQLiteErrorRaiseMethod(CANTOPEN_ISDIR)
 963 SFSQLiteErrorRaiseMethod(CANTOPEN_FULLPATH)
 964 SFSQLiteErrorRaiseMethod(CANTOPEN_CONVPATH)
 965 SFSQLiteErrorRaiseMethod(CORRUPT_VTAB)
 966 SFSQLiteErrorRaiseMethod(READONLY_RECOVERY)
 967 SFSQLiteErrorRaiseMethod(READONLY_CANTLOCK)
 968 SFSQLiteErrorRaiseMethod(READONLY_ROLLBACK)
 969 SFSQLiteErrorRaiseMethod(READONLY_DBMOVED)
 970 SFSQLiteErrorRaiseMethod(ABORT_ROLLBACK)
 971 SFSQLiteErrorRaiseMethod(CONSTRAINT_CHECK)
 972 SFSQLiteErrorRaiseMethod(CONSTRAINT_COMMITHOOK)
 973 SFSQLiteErrorRaiseMethod(CONSTRAINT_FOREIGNKEY)
 974 SFSQLiteErrorRaiseMethod(CONSTRAINT_FUNCTION)
 975 SFSQLiteErrorRaiseMethod(CONSTRAINT_NOTNULL)
 976 SFSQLiteErrorRaiseMethod(CONSTRAINT_PRIMARYKEY)
 977 SFSQLiteErrorRaiseMethod(CONSTRAINT_TRIGGER)
 978 SFSQLiteErrorRaiseMethod(CONSTRAINT_UNIQUE)
 979 SFSQLiteErrorRaiseMethod(CONSTRAINT_VTAB)
 980 SFSQLiteErrorRaiseMethod(CONSTRAINT_ROWID)
 981 SFSQLiteErrorRaiseMethod(NOTICE_RECOVER_WAL)
 982 SFSQLiteErrorRaiseMethod(NOTICE_RECOVER_ROLLBACK)
 983
 984 + (void)raise:(NSString *)reason code:(int)code extended:(int)extended {
 985     switch(extended) {
 986             SFSQLiteErrorCase(IOERR_READ);
 987             SFSQLiteErrorCase(IOERR_SHORT_READ);
 988             SFSQLiteErrorCase(IOERR_WRITE);
 989             SFSQLiteErrorCase(IOERR_FSYNC);
 990             SFSQLiteErrorCase(IOERR_DIR_FSYNC);
 991             SFSQLiteErrorCase(IOERR_TRUNCATE);
 992             SFSQLiteErrorCase(IOERR_FSTAT);
 993             SFSQLiteErrorCase(IOERR_UNLOCK);
 994             SFSQLiteErrorCase(IOERR_RDLOCK);
 995             SFSQLiteErrorCase(IOERR_DELETE);
 996             SFSQLiteErrorCase(IOERR_BLOCKED);
 997             SFSQLiteErrorCase(IOERR_NOMEM);
 998             SFSQLiteErrorCase(IOERR_ACCESS);
 999             SFSQLiteErrorCase(IOERR_CHECKRESERVEDLOCK);
1000             SFSQLiteErrorCase(IOERR_LOCK);
1001             SFSQLiteErrorCase(IOERR_CLOSE);
1002             SFSQLiteErrorCase(IOERR_DIR_CLOSE);
1003             SFSQLiteErrorCase(IOERR_SHMOPEN);
1004             SFSQLiteErrorCase(IOERR_SHMSIZE);
1005             SFSQLiteErrorCase(IOERR_SHMLOCK);
1006             SFSQLiteErrorCase(IOERR_SHMMAP);
1007             SFSQLiteErrorCase(IOERR_SEEK);
1008             SFSQLiteErrorCase(IOERR_DELETE_NOENT);
1009             SFSQLiteErrorCase(IOERR_MMAP);
1010             SFSQLiteErrorCase(IOERR_GETTEMPPATH);
1011             SFSQLiteErrorCase(IOERR_CONVPATH);
1012             SFSQLiteErrorCase(LOCKED_SHAREDCACHE);
1013             SFSQLiteErrorCase(BUSY_RECOVERY);
1014             SFSQLiteErrorCase(BUSY_SNAPSHOT);
1015             SFSQLiteErrorCase(CANTOPEN_NOTEMPDIR);
1016             SFSQLiteErrorCase(CANTOPEN_ISDIR);
1017             SFSQLiteErrorCase(CANTOPEN_FULLPATH);
1018             SFSQLiteErrorCase(CANTOPEN_CONVPATH);
1019             SFSQLiteErrorCase(CORRUPT_VTAB);
1020             SFSQLiteErrorCase(READONLY_RECOVERY);
1021             SFSQLiteErrorCase(READONLY_CANTLOCK);
1022             SFSQLiteErrorCase(READONLY_ROLLBACK);
1023             SFSQLiteErrorCase(READONLY_DBMOVED);
1024             SFSQLiteErrorCase(ABORT_ROLLBACK);
1025             SFSQLiteErrorCase(CONSTRAINT_CHECK);
1026             SFSQLiteErrorCase(CONSTRAINT_COMMITHOOK);
1027             SFSQLiteErrorCase(CONSTRAINT_FOREIGNKEY);
1028             SFSQLiteErrorCase(CONSTRAINT_FUNCTION);
1029             SFSQLiteErrorCase(CONSTRAINT_NOTNULL);
1030             SFSQLiteErrorCase(CONSTRAINT_PRIMARYKEY);
1031             SFSQLiteErrorCase(CONSTRAINT_TRIGGER);
1032             SFSQLiteErrorCase(CONSTRAINT_UNIQUE);
1033             SFSQLiteErrorCase(CONSTRAINT_VTAB);
1034             SFSQLiteErrorCase(CONSTRAINT_ROWID);
1035             SFSQLiteErrorCase(NOTICE_RECOVER_WAL);
1036             SFSQLiteErrorCase(NOTICE_RECOVER_ROLLBACK);
1037         default: break;
1038     }
1039     switch(code) {
1040             SFSQLiteErrorCase(ERROR);
1041             SFSQLiteErrorCase(INTERNAL);
1042             SFSQLiteErrorCase(PERM);
1043             SFSQLiteErrorCase(ABORT);
1044             SFSQLiteErrorCase(BUSY);
1045             SFSQLiteErrorCase(LOCKED);
1046             SFSQLiteErrorCase(NOMEM);
1047             SFSQLiteErrorCase(READONLY);
1048             SFSQLiteErrorCase(INTERRUPT);
1049             SFSQLiteErrorCase(IOERR);
1050             SFSQLiteErrorCase(CORRUPT);
1051             SFSQLiteErrorCase(NOTFOUND);
1052             SFSQLiteErrorCase(FULL);
1053             SFSQLiteErrorCase(CANTOPEN);
1054             SFSQLiteErrorCase(PROTOCOL);
1055             SFSQLiteErrorCase(SCHEMA);
1056             SFSQLiteErrorCase(TOOBIG);
1057             SFSQLiteErrorCase(CONSTRAINT);
1058             SFSQLiteErrorCase(MISMATCH);
1059             SFSQLiteErrorCase(MISUSE);
1060             SFSQLiteErrorCase(RANGE);
1061             SFSQLiteErrorCase(NOTADB);
1062         default: break;
1063     }
1064     [NSException raise:NSGenericException format:@"%@", reason];
1065 }
1066
1067 @end