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 #if __OBJC2__
  25
  26 #import "SFSQLite.h"
  27 #import "SFSQLiteStatement.h"
  28 #include <sqlite3.h>
  29 #include <CommonCrypto/CommonDigest.h>
  30 #import "utilities/debugging.h"
  31 #include <os/transaction_private.h>
  32
  33 #define kSFSQLiteBusyTimeout       (5*60*1000)
  34
  35 #define kSFSQLiteSchemaVersionKey  @"SchemaVersion"
  36 #define kSFSQLiteCreatedDateKey    @"Created"
  37 #define kSFSQLiteAutoVacuumFull    1
  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
  46 NSArray *SFSQLiteJournalSuffixes() {
  47     return @[@"-journal", @"-wal", @"-shm"];
  48 }
  49
  50 @interface NSObject (SFSQLiteAdditions)
  51 + (NSString *)SFSQLiteClassName;
  52 @end
  53
  54 @implementation NSObject (SFSQLiteAdditions)
  55 + (NSString *)SFSQLiteClassName {
  56     return NSStringFromClass(self);
  57 }
  58 @end
  59
  60 @interface SFSQLite ()
  61
  62 @property (nonatomic, assign)            sqlite3                *db;
  63 @property (nonatomic, assign)            NSUInteger              openCount;
  64 @property (nonatomic, assign)            BOOL                    corrupt;
  65 @property (nonatomic, readonly, strong)  NSMutableDictionary    *statementsBySQL;
  66 @property (nonatomic, strong)            NSDateFormatter        *dateFormatter;
  67 @property (nonatomic, strong)            NSDateFormatter        *oldDateFormatter;
  68
  69 @end
  70
  71 static char intToHexChar(uint8_t i)
  72 {
  73     return i >= 10 ? 'a' + i - 10 : '0' + i;
  74 }
  75
  76 static char *SecHexCharFromBytes(const uint8_t *bytes, NSUInteger length, NSUInteger *outlen) {
  77     // Fudge the math a bit on the assert because we don't want a 1GB string anyway
  78     if (length > (NSUIntegerMax / 3)) {
  79         return nil;
  80     }
  81     char *hex = calloc(1, length * 2 * 9 / 8); // 9/8 so we can inline ' ' between every 8 character sequence
  82     char *destPtr = hex;
  83
  84     NSUInteger i;
  85
  86     for (i = 0; length > 4; i += 4, length -= 4) {
  87         for (NSUInteger offset = 0; offset < 4; offset++) {
  88             *destPtr++ = intToHexChar((bytes[i+offset] & 0xF0) >> 4);
  89             *destPtr++ = intToHexChar(bytes[i+offset] & 0x0F);
  90         }
  91         *destPtr++ = ' ';
  92     }
  93
  94     /* Using the same i from the above loop */
  95     for (; length > 0; i++, length--) {
  96         *destPtr++ = intToHexChar((bytes[i] & 0xF0) >> 4);
  97         *destPtr++ = intToHexChar(bytes[i] & 0x0F);
  98     }
  99
 100     if (outlen) *outlen = destPtr - hex;
 101
 102     return hex;
 103 }
 104
 105 static BOOL SecCreateDirectoryAtPath(NSString *path, NSError **error) {
 106     BOOL success = YES;
 107     NSError *localError;
 108     NSFileManager *fileManager = [NSFileManager defaultManager];
 109
 110     if (![fileManager createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:&localError]) {
 111         if (![localError.domain isEqualToString:NSCocoaErrorDomain] || localError.code != NSFileWriteFileExistsError) {
 112             success = NO;
 113         }
 114     }
 115
 116 #if TARGET_OS_IPHONE
 117     if (success) {
 118         NSDictionary *attributes = [fileManager attributesOfItemAtPath:path error:&localError];
 119         if (![attributes[NSFileProtectionKey] isEqualToString:NSFileProtectionCompleteUntilFirstUserAuthentication]) {
 120             [fileManager setAttributes:@{ NSFileProtectionKey: NSFileProtectionCompleteUntilFirstUserAuthentication }
 121                           ofItemAtPath:path error:nil];
 122         }
 123     }
 124 #endif
 125     if (!success) {
 126         if (error) *error = localError;
 127     }
 128     return success;
 129 }
 130
 131 @implementation NSData (CKUtilsAdditions)
 132
 133 - (NSString *)CKHexString {
 134     NSUInteger hexLen = 0;
 135     NS_VALID_UNTIL_END_OF_SCOPE NSData *arcSafeSelf = self;
 136     char *hex = SecHexCharFromBytes([arcSafeSelf bytes], [arcSafeSelf length], &hexLen);
 137     return [[NSString alloc] initWithBytesNoCopy:hex length:hexLen encoding:NSASCIIStringEncoding freeWhenDone:YES];
 138 }
 139
 140 - (NSString *)CKLowercaseHexStringWithoutSpaces {
 141     NSMutableString *retVal = [[self CKHexString] mutableCopy];
 142     [retVal replaceOccurrencesOfString:@" " withString:@"" options:0 range:NSMakeRange(0, [retVal length])];
 143     return retVal;
 144 }
 145
 146 - (NSString *)CKUppercaseHexStringWithoutSpaces {
 147     NSMutableString *retVal = [[[self CKHexString] uppercaseString] mutableCopy];
 148     [retVal replaceOccurrencesOfString:@" " withString:@"" options:0 range:NSMakeRange(0, [retVal length])];
 149     return retVal;
 150 }
 151
 152 + (NSData *)CKDataWithHexString:(NSString *)hexString stringIsUppercase:(BOOL)stringIsUppercase {
 153     NSMutableData *retVal = [[NSMutableData alloc] init];
 154     NSCharacterSet *hexCharacterSet = nil;
 155     char aChar;
 156     if (stringIsUppercase) {
 157         hexCharacterSet = [NSCharacterSet characterSetWithCharactersInString:@"0123456789ABCDEF"];
 158         aChar = 'A';
 159     } else {
 160         hexCharacterSet = [NSCharacterSet characterSetWithCharactersInString:@"0123456789abcdef"];
 161         aChar = 'a';
 162     }
 163
 164     unsigned int i;
 165     for (i = 0; i < [hexString length] ; ) {
 166         BOOL validFirstByte = NO;
 167         BOOL validSecondByte = NO;
 168         unichar firstByte = 0;
 169         unichar secondByte = 0;
 170
 171         for ( ; i < [hexString length]; i++) {
 172             firstByte = [hexString characterAtIndex:i];
 173             if ([hexCharacterSet characterIsMember:firstByte]) {
 174                 i++;
 175                 validFirstByte = YES;
 176                 break;
 177             }
 178         }
 179         for ( ; i < [hexString length]; i++) {
 180             secondByte = [hexString characterAtIndex:i];
 181             if ([hexCharacterSet characterIsMember:secondByte]) {
 182                 i++;
 183                 validSecondByte = YES;
 184                 break;
 185             }
 186         }
 187         if (!validFirstByte || !validSecondByte) {
 188             goto allDone;
 189         }
 190         if ((firstByte >= '0') && (firstByte <= '9')) {
 191             firstByte -= '0';
 192         } else {
 193             firstByte = firstByte - aChar + 10;
 194         }
 195         if ((secondByte >= '0') && (secondByte <= '9')) {
 196             secondByte -= '0';
 197         } else {
 198             secondByte = secondByte - aChar + 10;
 199         }
 200         char totalByteValue = (char)((firstByte << 4) + secondByte);
 201
 202         [retVal appendBytes:&totalByteValue length:1];
 203     }
 204 allDone:
 205     return retVal;
 206 }
 207
 208 + (NSData *)CKDataWithHexString:(NSString *)hexString {
 209     return [self CKDataWithHexString:hexString stringIsUppercase:NO];
 210 }
 211
 212 @end
 213
 214 @implementation SFSQLite
 215
 216 @synthesize delegate = _delegate;
 217 @synthesize path = _path;
 218 @synthesize schema = _schema;
 219 @synthesize schemaVersion = _schemaVersion;
 220 @synthesize objectClassPrefix = _objectClassPrefix;
 221 @synthesize userVersion = _userVersion;
 222 @synthesize synchronousMode = _synchronousMode;
 223 @synthesize hasMigrated = _hasMigrated;
 224 @synthesize traced = _traced;
 225 @synthesize db = _db;
 226 @synthesize openCount = _openCount;
 227 @synthesize corrupt = _corrupt;
 228 @synthesize statementsBySQL = _statementsBySQL;
 229 @synthesize dateFormatter = _dateFormatter;
 230 @synthesize oldDateFormatter = _oldDateFormatter;
 231 #if DEBUG
 232 @synthesize unitTestOverrides = _unitTestOverrides;
 233 #endif
 234
 235 - (instancetype)initWithPath:(NSString *)path schema:(NSString *)schema {
 236     if (![path length]) {
 237         seccritical("Cannot init db with empty path");
 238         return nil;
 239     }
 240     if (![schema length]) {
 241         seccritical("Cannot init db without schema");
 242         return nil;
 243     }
 244
 245     if ((self = [super init])) {
 246         _path = path;
 247         _schema = schema;
 248         _schemaVersion = [self _createSchemaHash];
 249         _statementsBySQL = [[NSMutableDictionary alloc] init];
 250         _objectClassPrefix = @"CK";
 251         _synchronousMode = SFSQLiteSynchronousModeNormal;
 252         _hasMigrated = NO;
 253     }
 254     return self;
 255 }
 256
 257 - (void)dealloc {
 258     @autoreleasepool {
 259         [self close];
 260     }
 261 }
 262
 263 - (SInt32)userVersion {
 264     if (self.delegate) {
 265         return self.delegate.userVersion;
 266     }
 267     return _userVersion;
 268 }
 269
 270 - (NSString *)_synchronousModeString {
 271     switch (self.synchronousMode) {
 272         case SFSQLiteSynchronousModeOff:
 273             return @"off";
 274         case SFSQLiteSynchronousModeFull:
 275             return @"full";
 276         case SFSQLiteSynchronousModeNormal:
 277             break;
 278         default:
 279             assert(0 && "Unknown synchronous mode");
 280     }
 281     return @"normal";
 282 }
 283
 284 - (NSString *)_createSchemaHash {
 285     unsigned char hashBuffer[CC_SHA256_DIGEST_LENGTH] = {0};
 286     NSData *hashData = [NSData dataWithBytesNoCopy:hashBuffer length:CC_SHA256_DIGEST_LENGTH freeWhenDone:NO];
 287     NS_VALID_UNTIL_END_OF_SCOPE NSData *schemaData = [self.schema dataUsingEncoding:NSUTF8StringEncoding];
 288     CC_SHA256([schemaData bytes], (CC_LONG)[schemaData length], hashBuffer);
 289     return [hashData CKUppercaseHexStringWithoutSpaces];
 290 }
 291
 292 - (BOOL)isOpen {
 293     return _db != NULL;
 294 }
 295
 296 /*
 297  Best-effort attempts to set/correct filesystem permissions.
 298  May fail when we don't own DB which means we must wait for them to update permissions,
 299  or file does not exist yet which is okay because db will exist and the aux files inherit permissions
 300 */
 301 - (void)attemptProperDatabasePermissions
 302 {
 303 #if TARGET_OS_IPHONE
 304     NSFileManager* fm = [NSFileManager defaultManager];
 305     [fm setAttributes:@{NSFilePosixPermissions : [NSNumber numberWithShort:0666]}
 306          ofItemAtPath:_path
 307                 error:nil];
 308     [fm setAttributes:@{NSFilePosixPermissions : [NSNumber numberWithShort:0666]}
 309          ofItemAtPath:[NSString stringWithFormat:@"%@-wal",_path]
 310                 error:nil];
 311     [fm setAttributes:@{NSFilePosixPermissions : [NSNumber numberWithShort:0666]}
 312          ofItemAtPath:[NSString stringWithFormat:@"%@-shm",_path]
 313                 error:nil];
 314 #endif
 315 }
 316
 317 - (BOOL)openWithError:(NSError **)error {
 318     BOOL success = NO;
 319     NSError *localError;
 320     NSString *dbSchemaVersion, *dir;
 321     NSArray *results;
 322     NS_VALID_UNTIL_END_OF_SCOPE NSString *arcSafePath = _path;
 323
 324     if (_openCount > 0) {
 325         NSAssert(_db != NULL, @"Missing handle for open cache db");
 326         _openCount += 1;
 327         success = YES;
 328         goto done;
 329     }
 330
 331     // Create the directory for the cache.
 332     dir = [_path stringByDeletingLastPathComponent];
 333     if (!SecCreateDirectoryAtPath(dir, &localError)) {
 334         goto done;
 335     }
 336
 337     int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
 338 #if TARGET_OS_IPHONE
 339     flags |= SQLITE_OPEN_FILEPROTECTION_COMPLETEUNTILFIRSTUSERAUTHENTICATION;
 340 #endif
 341     int rc = sqlite3_open_v2([arcSafePath fileSystemRepresentation], &_db, flags, NULL);
 342     if (rc != SQLITE_OK) {
 343         localError = [NSError errorWithDomain:NSCocoaErrorDomain code:rc userInfo:@{NSLocalizedDescriptionKey : [NSString stringWithFormat:@"Error opening db at %@, rc=%d(0x%x)", _path, rc, rc]}];
 344         goto done;
 345     }
 346
 347     // Filesystem foo for multiple daemons from different users
 348     [self attemptProperDatabasePermissions];
 349
 350     sqlite3_extended_result_codes(_db, 1);
 351     rc = sqlite3_busy_timeout(_db, kSFSQLiteBusyTimeout);
 352     if (rc != SQLITE_OK) {
 353         goto done;
 354     }
 355
 356     // You don't argue with the Ben: rdar://12685305
 357     if (![self executeSQL:@"pragma journal_mode = WAL"]) {
 358         goto done;
 359     }
 360     if (![self executeSQL:@"pragma synchronous = %@", [self _synchronousModeString]]) {
 361         goto done;
 362     }
 363     if ([self autoVacuumSetting] != kSFSQLiteAutoVacuumFull) {
 364         /* After changing the auto_vacuum setting the DB must be vacuumed */
 365         if (![self executeSQL:@"pragma auto_vacuum = FULL"] || ![self executeSQL:@"VACUUM"]) {
 366             goto done;
 367         }
 368     }
 369
 370     // rdar://problem/32168789
 371     // [self executeSQL:@"pragma foreign_keys = 1"];
 372
 373     // Initialize the db within a transaction in case there is a crash between creating the schema and setting the
 374     // schema version, and to avoid multiple threads trying to re-create the db at once.
 375     [self begin];
 376
 377     // Create the Properties table before trying to read the schema version from it. If the Properties table doesn't
 378     // exist we can't prepare a statement to access it.
 379     results = [self select:@[@"name"] from:@"sqlite_master" where:@"type = ? AND name = ?" bindings:@[@"table", @"Properties"]];
 380     if (!results.count) {
 381         [self executeSQL:kSFSQLiteCreatePropertiesTableSQL];
 382     }
 383
 384     // Check the schema version and create or re-create the db if needed.
 385     BOOL create = NO;
 386     dbSchemaVersion = [self propertyForKey:kSFSQLiteSchemaVersionKey];
 387     SInt32 dbUserVersion = [self dbUserVersion];
 388
 389     if (!dbSchemaVersion) {
 390         // The schema version isn't set so the db was just created or we failed to initialize it previously.
 391         create = YES;
 392     } else if (![dbSchemaVersion isEqualToString:self.schemaVersion]
 393                || (self.userVersion && dbUserVersion != self.userVersion)) {
 394
 395         if (self.delegate && [self.delegate migrateDatabase:self fromVersion:dbUserVersion]) {
 396             _hasMigrated = YES;
 397         }
 398
 399         if (!_hasMigrated) {
 400             // 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.
 401             [self removeAllStatements];
 402             [self dropAllTables];
 403             create = YES;
 404             _hasMigrated = YES;
 405         }
 406     }
 407     if (create) {
 408         [self executeSQL:kSFSQLiteCreatePropertiesTableSQL];
 409         [self executeSQL:@"%@", self.schema];
 410         NSString *createdDateString = [NSString stringWithFormat:@"%f", [[NSDate date] timeIntervalSinceReferenceDate]];
 411         [self setProperty:createdDateString forKey:kSFSQLiteCreatedDateKey];
 412     }
 413
 414     [self end];
 415
 416 #if DEBUG
 417     // TODO: <rdar://problem/33115830> Resolve Race Condition When Setting 'userVersion/schemaVersion' in SFSQLite
 418     if ([self.unitTestOverrides[@"RacyUserVersionUpdate"] isEqual:@YES]) {
 419         success = YES;
 420         goto done;
 421     }
 422 #endif
 423
 424     if (create || _hasMigrated) {
 425         [self setProperty:self.schemaVersion forKey:kSFSQLiteSchemaVersionKey];
 426         if (self.userVersion) {
 427             [self executeSQL:@"pragma user_version = %ld", (long)self.userVersion];
 428         }
 429     }
 430
 431     _openCount += 1;
 432     success = YES;
 433
 434 done:
 435     if (!success) {
 436         sqlite3_close_v2(_db);
 437         _db = nil;
 438     }
 439
 440     if (!success && error) {
 441         if (!localError) {
 442             localError = [NSError errorWithDomain:NSCocoaErrorDomain code:0 userInfo:@{NSLocalizedDescriptionKey : [NSString stringWithFormat:@"Error opening db at %@", _path]}];
 443         }
 444         *error = localError;
 445     }
 446     return success;
 447 }
 448
 449 - (void)open {
 450     NSError *error;
 451     if (![self openWithError:&error] && !(error && error.code == SQLITE_AUTH)) {
 452         secerror("sfsqlite: Error opening db at %@: %@", self.path, error);
 453         return;
 454     }
 455 }
 456
 457
 458 - (void)close {
 459     if (_openCount > 0) {
 460         if (_openCount == 1) {
 461             NSAssert(_db != NULL, @"Missing handle for open cache db");
 462
 463             [self removeAllStatements];
 464
 465             if (sqlite3_close(_db)) {
 466                 secerror("sfsqlite: Error closing database");
 467                 return;
 468             }
 469             _db = NULL;
 470         }
 471         _openCount -= 1;
 472     }
 473 }
 474
 475 - (void)remove {
 476     NSAssert(_openCount == 0, @"Trying to remove db at: %@ while it is open", _path);
 477     [[NSFileManager defaultManager] removeItemAtPath:_path error:nil];
 478     for (NSString *suffix in SFSQLiteJournalSuffixes()) {
 479         [[NSFileManager defaultManager] removeItemAtPath:[_path stringByAppendingString:suffix] error:nil];
 480     }
 481 }
 482
 483 - (void)begin {
 484     [self executeSQL:@"begin exclusive"];
 485 }
 486
 487 - (void)end {
 488     [self executeSQL:@"end"];
 489 }
 490
 491 - (void)rollback {
 492     [self executeSQL:@"rollback"];
 493 }
 494
 495 - (void)analyze {
 496     [self executeSQL:@"analyze"];
 497 }
 498
 499 - (void)vacuum {
 500     [self executeSQL:@"vacuum"];
 501 }
 502
 503 - (SFSQLiteRowID)lastInsertRowID {
 504     if (!_db) {
 505         secerror("sfsqlite: Database is closed");
 506         return -1;
 507     }
 508
 509     return sqlite3_last_insert_rowid(_db);
 510 }
 511
 512 - (int)changes
 513 {
 514     if (!_db) {
 515         secerror("sfsqlite: Database is closed");
 516         return -1;
 517     }
 518
 519     return sqlite3_changes(_db);
 520 }
 521
 522 - (BOOL)executeSQL:(NSString *)format, ... {
 523     va_list args;
 524     va_start(args, format);
 525     BOOL result = [self executeSQL:format arguments:args];
 526     va_end(args);
 527     return result;
 528 }
 529
 530 - (BOOL)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         secerror("sfsqlite: Database is closed");
 534         return NO;
 535     }
 536     int execRet = sqlite3_exec(_db, [SQL UTF8String], NULL, NULL, NULL);
 537     if (execRet != SQLITE_OK) {
 538         if (execRet != SQLITE_AUTH && execRet != SQLITE_READONLY) {
 539             secerror("sfsqlite: Error executing SQL: \"%@\" (%d)", SQL, execRet);
 540         }
 541         return NO;
 542     }
 543
 544     return YES;
 545 }
 546
 547 - (SFSQLiteStatement *)statementForSQL:(NSString *)SQL {
 548     if (!_db) {
 549         secerror("sfsqlite: Database is closed");
 550         return nil;
 551     }
 552
 553     SFSQLiteStatement *statement = _statementsBySQL[SQL];
 554     if (statement) {
 555         NSAssert(statement.isReset, @"Statement not reset after last use: \"%@\"", SQL);
 556     } else {
 557         sqlite3_stmt *handle = NULL;
 558         NS_VALID_UNTIL_END_OF_SCOPE NSString *arcSafeSQL = SQL;
 559         if (sqlite3_prepare_v2(_db, [arcSafeSQL UTF8String], -1, &handle, NULL)) {
 560             secerror("Error preparing statement: %@", SQL);
 561             return nil;
 562         }
 563
 564         statement = [[SFSQLiteStatement alloc] initWithSQLite:self SQL:SQL handle:handle];
 565         _statementsBySQL[SQL] = statement;
 566     }
 567
 568     return statement;
 569 }
 570
 571 - (void)removeAllStatements {
 572     [[_statementsBySQL allValues] makeObjectsPerformSelector:@selector(finalizeStatement)];
 573     [_statementsBySQL removeAllObjects];
 574 }
 575
 576 - (NSArray *)allTableNames {
 577     NSMutableArray *tableNames = [[NSMutableArray alloc] init];
 578
 579     SFSQLiteStatement *statement = [self statementForSQL:@"select name from sqlite_master where type = 'table'"];
 580     while ([statement step]) {
 581         NSString *name = [statement textAtIndex:0];
 582         [tableNames addObject:name];
 583     }
 584     [statement reset];
 585
 586     return tableNames;
 587 }
 588
 589 - (void)dropAllTables {
 590     for (NSString *tableName in [self allTableNames]) {
 591         [self executeSQL:@"drop table %@", tableName];
 592     }
 593 }
 594
 595 - (NSString *)propertyForKey:(NSString *)key {
 596     if (![key length]) {
 597         secerror("SFSQLite: attempt to retrieve property without a key");
 598         return nil;
 599     }
 600
 601     NSString *value = nil;
 602
 603     SFSQLiteStatement *statement = [self statementForSQL:@"select value from Properties where key = ?"];
 604     [statement bindText:key atIndex:0];
 605     if ([statement step]) {
 606         value = [statement textAtIndex:0];
 607     }
 608     [statement reset];
 609
 610     return value;
 611 }
 612
 613 - (void)setProperty:(NSString *)value forKey:(NSString *)key {
 614     if (![key length]) {
 615         secerror("SFSQLite: attempt to set property without a key");
 616         return;
 617     }
 618
 619     if (value) {
 620         SFSQLiteStatement *statement = [self statementForSQL:@"insert or replace into Properties (key, value) values (?,?)"];
 621         [statement bindText:key atIndex:0];
 622         [statement bindText:value atIndex:1];
 623         [statement step];
 624         [statement reset];
 625     } else {
 626         [self removePropertyForKey:key];
 627     }
 628 }
 629
 630 - (NSDateFormatter *)dateFormatter {
 631     if (!_dateFormatter) {
 632         NSDateFormatter* dateFormatter = [NSDateFormatter new];
 633         dateFormatter.dateFormat = @"yyyy-MM-dd'T'HH:mm:ss.SSSZZZZ";
 634         _dateFormatter = dateFormatter;
 635     }
 636     return _dateFormatter;
 637 }
 638
 639 - (NSDateFormatter *)oldDateFormatter {
 640     if (!_oldDateFormatter) {
 641         NSDateFormatter* dateFormatter = [NSDateFormatter new];
 642         dateFormatter.dateFormat = @"yyyy-MM-dd'T'HH:mm:ssZZZZZ";
 643         _oldDateFormatter = dateFormatter;
 644     }
 645     return _oldDateFormatter;
 646 }
 647
 648 - (NSDate *)datePropertyForKey:(NSString *)key {
 649     NSString *dateStr = [self propertyForKey:key];
 650     if (dateStr.length) {
 651         NSDate  *date = [self.dateFormatter dateFromString:dateStr];
 652         if (date == NULL) {
 653             date = [self.oldDateFormatter dateFromString:dateStr];
 654         }
 655         return date;
 656     }
 657     return nil;
 658 }
 659
 660 - (void)setDateProperty:(NSDate *)value forKey:(NSString *)key {
 661     NSString *dateStr = nil;
 662     if (value) {
 663         dateStr = [self.dateFormatter stringFromDate:value];
 664     }
 665     [self setProperty:dateStr forKey:key];
 666 }
 667
 668 - (void)removePropertyForKey:(NSString *)key {
 669     if (![key length]) {
 670         return;
 671     }
 672
 673     SFSQLiteStatement *statement = [self statementForSQL:@"delete from Properties where key = ?"];
 674     [statement bindText:key atIndex:0];
 675     [statement step];
 676     [statement reset];
 677 }
 678
 679 - (NSDate *)creationDate {
 680     return [NSDate dateWithTimeIntervalSinceReferenceDate:[[self propertyForKey:kSFSQLiteCreatedDateKey] floatValue]];
 681 }
 682
 683 // https://sqlite.org/pragma.html#pragma_table_info
 684 - (NSSet<NSString*> *)columnNamesForTable:(NSString*)tableName {
 685     SFSQLiteStatement *statement = [self statementForSQL:[NSString stringWithFormat:@"pragma table_info(%@)", tableName]];
 686     NSMutableSet<NSString*>* columnNames = [[NSMutableSet alloc] init];
 687     while ([statement step]) {
 688         [columnNames addObject:[statement textAtIndex:1]];
 689     }
 690     [statement reset];
 691     return columnNames;
 692 }
 693
 694 - (NSArray *)select:(NSArray *)columns from:(NSString *)tableName {
 695     return [self select:columns from:tableName where:nil bindings:nil];
 696 }
 697
 698 - (NSArray *)select:(NSArray *)columns from:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 699     NSMutableArray *results = [[NSMutableArray alloc] init];
 700
 701     NSMutableString *SQL = [NSMutableString stringWithFormat:@"select %@ from %@", [columns componentsJoinedByString:@", "], tableName];
 702     if (whereSQL) {
 703         [SQL appendFormat:@" where %@", whereSQL];
 704     }
 705
 706     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 707     [statement bindValues:bindings];
 708     while ([statement step]) {
 709         [results addObject:[statement allObjectsByColumnName]];
 710     }
 711     [statement reset];
 712
 713     return results;
 714 }
 715
 716 - (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 {
 717     @autoreleasepool {
 718         NSMutableString *SQL = [[NSMutableString alloc] init];
 719         NSString *columnsString = @"*";
 720         if ([columns count]) columnsString = [columns componentsJoinedByString:@", "];
 721         [SQL appendFormat:@"select %@ from %@", columnsString, tableName];
 722
 723         if (whereSQL.length) {
 724             [SQL appendFormat:@" where %@", whereSQL];
 725         }
 726         if (orderBy) {
 727             NSString *orderByString = [orderBy componentsJoinedByString:@", "];
 728             [SQL appendFormat:@" order by %@", orderByString];
 729         }
 730         if (limit != nil) {
 731             [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 732         }
 733
 734         SFSQLiteStatement *statement = [self statementForSQL:SQL];
 735         [statement bindValues:bindings];
 736         do {
 737             @autoreleasepool {
 738                 if (![statement step]) {
 739                     break;
 740                 }
 741                 NSDictionary *stepResult = [statement allObjectsByColumnName];
 742                 if (block) {
 743                     BOOL stop = NO;
 744                     block(stepResult, &stop);
 745                     if (stop) {
 746                         break;
 747                     }
 748                 }
 749             }
 750         } while (1);
 751         [statement reset];
 752     }
 753 }
 754
 755 - (void)selectFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings orderBy:(NSArray *)orderBy limit:(NSNumber *)limit block:(void (^)(NSDictionary *resultDictionary, BOOL *stop))block {
 756     @autoreleasepool {
 757         NSMutableString *SQL = [[NSMutableString alloc] init];
 758         [SQL appendFormat:@"select * from %@", tableName];
 759
 760         if (whereSQL.length) {
 761             [SQL appendFormat:@" where %@", whereSQL];
 762         }
 763         if (orderBy) {
 764             NSString *orderByString = [orderBy componentsJoinedByString:@", "];
 765             [SQL appendFormat:@" order by %@", orderByString];
 766         }
 767         if (limit != nil) {
 768             [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 769         }
 770
 771         SFSQLiteStatement *statement = [self statementForSQL:SQL];
 772         [statement bindValues:bindings];
 773         do {
 774             @autoreleasepool {
 775                 if (![statement step]) {
 776                     break;
 777                 }
 778                 NSDictionary *stepResult = [statement allObjectsByColumnName];
 779                 if (block) {
 780                     BOOL stop = NO;
 781                     block(stepResult, &stop);
 782                     if (stop) {
 783                         break;
 784                     }
 785                 }
 786             }
 787         } while (1);
 788         [statement reset];
 789     }
 790 }
 791
 792 - (NSArray *)selectFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings limit:(NSNumber *)limit {
 793     NSMutableString *SQL = [[NSMutableString alloc] init];
 794     [SQL appendFormat:@"select * from %@", tableName];
 795
 796     if (whereSQL.length) {
 797         [SQL appendFormat:@" where %@", whereSQL];
 798     }
 799     if (limit != nil) {
 800         [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 801     }
 802
 803     NSMutableArray *results = [[NSMutableArray alloc] init];
 804
 805     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 806     [statement bindValues:bindings];
 807     while ([statement step]) {
 808         [results addObject:[statement allObjectsByColumnName]];
 809     }
 810     [statement reset];
 811
 812     return results;
 813 }
 814
 815 - (void)update:(NSString *)tableName set:(NSString *)setSQL where:(NSString *)whereSQL bindings:(NSArray *)whereBindings limit:(NSNumber *)limit {
 816     if (![setSQL length]) {
 817         return;
 818     }
 819
 820     NSMutableString *SQL = [[NSMutableString alloc] init];
 821     [SQL appendFormat:@"update %@", tableName];
 822
 823     [SQL appendFormat:@" set %@", setSQL];
 824     if (whereSQL.length) {
 825         [SQL appendFormat:@" where %@", whereSQL];
 826     }
 827     if (limit != nil) {
 828         [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 829     }
 830
 831     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 832     [statement bindValues:whereBindings];
 833     while ([statement step]) {
 834     }
 835     [statement reset];
 836 }
 837
 838 - (NSArray *)selectAllFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 839     return [self selectFrom:tableName where:whereSQL bindings:bindings limit:nil];
 840 }
 841
 842 - (NSUInteger)selectCountFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 843     NSArray *results = [self select:@[@"count(*) as n"] from:tableName where:whereSQL bindings:bindings];
 844     return [results[0][@"n"] unsignedIntegerValue];
 845 }
 846
 847 - (SFSQLiteRowID)insertOrReplaceInto:(NSString *)tableName values:(NSDictionary *)valuesByColumnName {
 848     NSArray *columnNames = [[valuesByColumnName allKeys] sortedArrayUsingSelector:@selector(compare:)];
 849     NSMutableArray *values = [[NSMutableArray alloc] init];
 850     for (NSUInteger i = 0; i < columnNames.count; i++) {
 851         values[i] = valuesByColumnName[columnNames[i]];
 852     }
 853
 854     NSMutableString *SQL = [[NSMutableString alloc] initWithString:@"insert or replace into "];
 855     [SQL appendString:tableName];
 856     [SQL appendString:@" ("];
 857     for (NSUInteger i = 0; i < columnNames.count; i++) {
 858         [SQL appendString:columnNames[i]];
 859         if (i != columnNames.count-1) {
 860             [SQL appendString:@","];
 861         }
 862     }
 863     [SQL appendString:@") values ("];
 864     for (NSUInteger i = 0; i < columnNames.count; i++) {
 865         if (i != columnNames.count-1) {
 866             [SQL appendString:@"?,"];
 867         } else {
 868             [SQL appendString:@"?"];
 869         }
 870     }
 871     [SQL appendString:@")"];
 872
 873     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 874     [statement bindValues:values];
 875     [statement step];
 876     [statement reset];
 877
 878     return [self lastInsertRowID];
 879 }
 880
 881 - (void)deleteFrom:(NSString *)tableName matchingValues:(NSDictionary *)valuesByColumnName {
 882     NSArray *columnNames = [[valuesByColumnName allKeys] sortedArrayUsingSelector:@selector(compare:)];
 883     NSMutableArray *values = [[NSMutableArray alloc] init];
 884     NSMutableString *whereSQL = [[NSMutableString alloc] init];
 885     int bindingCount = 0;
 886     for (NSUInteger i = 0; i < columnNames.count; i++) {
 887         id value = valuesByColumnName[columnNames[i]];
 888         [whereSQL appendString:columnNames[i]];
 889         if (!value || [[NSNull null] isEqual:value]) {
 890             [whereSQL appendString:@" is NULL"];
 891         } else {
 892             values[bindingCount++] = value;
 893             [whereSQL appendString:@"=?"];
 894         }
 895         if (i != columnNames.count-1) {
 896             [whereSQL appendString:@" AND "];
 897         }
 898     }
 899     [self deleteFrom:tableName where:whereSQL bindings:values];
 900 }
 901
 902 - (void)deleteFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 903     NSString *SQL = [NSString stringWithFormat:@"delete from %@ where %@", tableName, whereSQL];
 904
 905     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 906     [statement bindValues:bindings];
 907     [statement step];
 908     [statement reset];
 909 }
 910
 911 - (NSString *)_tableNameForClass:(Class)objectClass {
 912     NSString *className = [objectClass SFSQLiteClassName];
 913     if (![className hasPrefix:_objectClassPrefix]) {
 914         secerror("sfsqlite: %@", [NSString stringWithFormat:@"Object class \"%@\" does not have prefix \"%@\"", className, _objectClassPrefix]);
 915         return nil;
 916     }
 917     return [className substringFromIndex:_objectClassPrefix.length];
 918 }
 919
 920 - (SInt32)dbUserVersion {
 921     SInt32 userVersion = 0;
 922     SFSQLiteStatement *statement = [self statementForSQL:@"pragma user_version"];
 923     while ([statement step]) {
 924         userVersion = [statement intAtIndex:0];
 925     }
 926     [statement reset];
 927
 928     return userVersion;
 929 }
 930
 931 - (SInt32)autoVacuumSetting {
 932     SInt32 vacuumMode = 0;
 933     SFSQLiteStatement *statement = [self statementForSQL:@"pragma auto_vacuum"];
 934     while ([statement step]) {
 935         vacuumMode = [statement intAtIndex:0];
 936     }
 937     [statement reset];
 938
 939     return vacuumMode;
 940 }
 941
 942 @end
 943
 944 #endif