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