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 #import "debugging.h"
  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
  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 shouldVacuum = _shouldVacuum;
 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 #if DEBUG
 231 @synthesize unitTestOverrides = _unitTestOverrides;
 232 #endif
 233
 234 - (instancetype)initWithPath:(NSString *)path schema:(NSString *)schema {
 235     if ((self = [super init])) {
 236         NSAssert([path length], @"Can't init a database with a zero-length path");
 237         _path = path;
 238         _schema = schema;
 239         _schemaVersion = [self _createSchemaHash];
 240         _statementsBySQL = [[NSMutableDictionary alloc] init];
 241         _objectClassPrefix = @"CK";
 242         _synchronousMode = SFSQLiteSynchronousModeNormal;
 243         _hasMigrated = NO;
 244         _shouldVacuum = YES;
 245     }
 246     return self;
 247 }
 248
 249 - (void)dealloc {
 250     @autoreleasepool {
 251         [self close];
 252     }
 253 }
 254
 255 - (SInt32)userVersion {
 256     if (self.delegate) {
 257         return self.delegate.userVersion;
 258     }
 259     return _userVersion;
 260 }
 261
 262 - (NSString *)_synchronousModeString {
 263     switch (self.synchronousMode) {
 264         case SFSQLiteSynchronousModeOff:
 265             return @"off";
 266         case SFSQLiteSynchronousModeFull:
 267             return @"full";
 268         case SFSQLiteSynchronousModeNormal:
 269             break;
 270         default:
 271             assert(0 && "Unknown synchronous mode");
 272     }
 273     return @"normal";
 274 }
 275
 276 - (NSString *)_createSchemaHash {
 277     unsigned char hashBuffer[CC_SHA256_DIGEST_LENGTH] = {0};
 278     NSData *hashData = [NSData dataWithBytesNoCopy:hashBuffer length:CC_SHA256_DIGEST_LENGTH freeWhenDone:NO];
 279     NS_VALID_UNTIL_END_OF_SCOPE NSData *schemaData = [self.schema dataUsingEncoding:NSUTF8StringEncoding];
 280     CC_SHA256([schemaData bytes], (CC_LONG)[schemaData length], hashBuffer);
 281     return [hashData CKUppercaseHexStringWithoutSpaces];
 282 }
 283
 284 - (BOOL)isOpen {
 285     return _db != NULL;
 286 }
 287
 288 - (void)_periodicVacuum {
 289     // "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.
 290     // 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.
 291     // In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse."
 292     // https://sqlite.org/pragma.html#pragma_auto_vacuum
 293     NSDate *lastVacuumDate = [NSDate dateWithTimeIntervalSinceReferenceDate:[[self propertyForKey:kSFSQLiteLastVacuumKey] floatValue]];
 294     if ([lastVacuumDate timeIntervalSinceNow] < -(kCKSQLVacuumInterval)) {
 295         [self executeSQL:@"VACUUM"];
 296
 297         NSString *vacuumDateString = [NSString stringWithFormat:@"%f", [[NSDate date] timeIntervalSinceReferenceDate]];
 298         [self setProperty:vacuumDateString forKey:kSFSQLiteLastVacuumKey];
 299     }
 300 }
 301
 302 - (BOOL)openWithError:(NSError **)error {
 303     BOOL success = NO;
 304     NSError *localError;
 305     NSString *dbSchemaVersion, *dir;
 306     NSArray *results;
 307     NS_VALID_UNTIL_END_OF_SCOPE NSString *arcSafePath = _path;
 308
 309     if (_openCount > 0) {
 310         NSAssert(_db != NULL, @"Missing handle for open cache db");
 311         _openCount += 1;
 312         success = YES;
 313         goto done;
 314     }
 315
 316     // Create the directory for the cache.
 317     dir = [_path stringByDeletingLastPathComponent];
 318     if (!SecCreateDirectoryAtPath(dir, &localError)) {
 319         goto done;
 320     }
 321
 322     int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
 323 #if TARGET_OS_IPHONE
 324     flags |= SQLITE_OPEN_FILEPROTECTION_COMPLETEUNTILFIRSTUSERAUTHENTICATION;
 325 #endif
 326     int rc = sqlite3_open_v2([arcSafePath fileSystemRepresentation], &_db, flags, NULL);
 327     if (rc != SQLITE_OK) {
 328         localError = [NSError errorWithDomain:NSCocoaErrorDomain code:0 userInfo:@{NSLocalizedDescriptionKey : [NSString stringWithFormat:@"Error opening db at %@, rc=%d(0x%x)", _path, rc, rc]}];
 329         goto done;
 330     }
 331     sqlite3_extended_result_codes(_db, 1);
 332     rc = sqlite3_busy_timeout(_db, kSFSQLiteBusyTimeout);
 333     if (rc != SQLITE_OK) {
 334         goto done;
 335     }
 336
 337     // You don't argue with the Ben: rdar://12685305
 338     if (![self executeSQL:@"pragma journal_mode = WAL"]) {
 339         goto done;
 340     }
 341     if (![self executeSQL:@"pragma synchronous = %@", [self _synchronousModeString]]) {
 342         goto done;
 343     }
 344     if (![self executeSQL:@"pragma auto_vacuum = FULL"]) {
 345         goto done;
 346     }
 347
 348     // rdar://problem/32168789
 349     // [self executeSQL:@"pragma foreign_keys = 1"];
 350
 351     // Initialize the db within a transaction in case there is a crash between creating the schema and setting the
 352     // schema version, and to avoid multiple threads trying to re-create the db at once.
 353     [self begin];
 354
 355     // Create the Properties table before trying to read the schema version from it. If the Properties table doesn't
 356     // exist we can't prepare a statement to access it.
 357     results = [self select:@[@"name"] from:@"sqlite_master" where:@"type = ? AND name = ?" bindings:@[@"table", @"Properties"]];
 358     if (!results.count) {
 359         [self executeSQL:kSFSQLiteCreatePropertiesTableSQL];
 360     }
 361
 362     // Check the schema version and create or re-create the db if needed.
 363     BOOL create = NO;
 364     dbSchemaVersion = [self propertyForKey:kSFSQLiteSchemaVersionKey];
 365     SInt32 dbUserVersion = [self dbUserVersion];
 366
 367     if (!dbSchemaVersion) {
 368         // The schema version isn't set so the db was just created or we failed to initialize it previously.
 369         create = YES;
 370     } else if (![dbSchemaVersion isEqualToString:self.schemaVersion]
 371                || (self.userVersion && dbUserVersion != self.userVersion)) {
 372
 373         if (self.delegate && [self.delegate migrateDatabase:self fromVersion:dbUserVersion]) {
 374             _hasMigrated = YES;
 375         }
 376
 377         if (!_hasMigrated) {
 378             // 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.
 379             [self removeAllStatements];
 380             [self dropAllTables];
 381             create = YES;
 382             _hasMigrated = YES;
 383         }
 384     }
 385     if (create) {
 386         [self executeSQL:kSFSQLiteCreatePropertiesTableSQL];
 387         [self executeSQL:@"%@", self.schema];
 388         NSString *createdDateString = [NSString stringWithFormat:@"%f", [[NSDate date] timeIntervalSinceReferenceDate]];
 389         [self setProperty:createdDateString forKey:kSFSQLiteCreatedDateKey];
 390     }
 391
 392     [self end];
 393
 394 #if DEBUG
 395     // TODO: <rdar://problem/33115830> Resolve Race Condition When Setting 'userVersion/schemaVersion' in SFSQLite
 396     if ([self.unitTestOverrides[@"RacyUserVersionUpdate"] isEqual:@YES]) {
 397         success = YES;
 398         goto done;
 399     }
 400 #endif
 401
 402     if (self.shouldVacuum) [self _periodicVacuum];
 403
 404     if (create || _hasMigrated) {
 405         [self setProperty:self.schemaVersion forKey:kSFSQLiteSchemaVersionKey];
 406         if (self.userVersion) {
 407             [self executeSQL:@"pragma user_version = %ld", (long)self.userVersion];
 408         }
 409     }
 410
 411     _openCount += 1;
 412     success = YES;
 413
 414 done:
 415     if (!success) {
 416         sqlite3_close_v2(_db);
 417         _db = nil;
 418     }
 419
 420     if (!success && error) {
 421         if (!localError) {
 422             localError = [NSError errorWithDomain:NSCocoaErrorDomain code:0 userInfo:@{NSLocalizedDescriptionKey : [NSString stringWithFormat:@"Error opening db at %@, ", _path]}];
 423         }
 424         *error = localError;
 425     }
 426     return success;
 427 }
 428
 429 - (void)open {
 430     NSError *error;
 431     if (![self openWithError:&error]) {
 432         secerror("sfsqlite: Error opening db at %@: %@", self.path, error);
 433         return;
 434     }
 435 }
 436
 437
 438 - (void)close {
 439     if (_openCount > 0) {
 440         if (_openCount == 1) {
 441             NSAssert(_db != NULL, @"Missing handle for open cache db");
 442
 443             [self removeAllStatements];
 444
 445             if (sqlite3_close(_db)) {
 446                 secerror("sfsqlite: Error closing database");
 447                 return;
 448             }
 449             _db = NULL;
 450         }
 451         _openCount -= 1;
 452     }
 453 }
 454
 455 - (void)remove {
 456     NSAssert(_openCount == 0, @"Trying to remove db at: %@ while it is open", _path);
 457     [[NSFileManager defaultManager] removeItemAtPath:_path error:nil];
 458     for (NSString *suffix in SFSQLiteJournalSuffixes()) {
 459         [[NSFileManager defaultManager] removeItemAtPath:[_path stringByAppendingString:suffix] error:nil];
 460     }
 461 }
 462
 463 - (void)begin {
 464     [self executeSQL:@"begin exclusive"];
 465 }
 466
 467 - (void)end {
 468     [self executeSQL:@"end"];
 469 }
 470
 471 - (void)rollback {
 472     [self executeSQL:@"rollback"];
 473 }
 474
 475 - (void)analyze {
 476     [self executeSQL:@"analyze"];
 477 }
 478
 479 - (void)vacuum {
 480     [self executeSQL:@"vacuum"];
 481 }
 482
 483 - (SFSQLiteRowID)lastInsertRowID {
 484     if (!_db) {
 485         secerror("sfsqlite: Database is closed");
 486         return -1;
 487     }
 488
 489     return sqlite3_last_insert_rowid(_db);
 490 }
 491
 492 - (int)changes
 493 {
 494     if (!_db) {
 495         secerror("sfsqlite: Database is closed");
 496         return -1;
 497     }
 498
 499     return sqlite3_changes(_db);
 500 }
 501
 502 - (BOOL)executeSQL:(NSString *)format, ... {
 503     va_list args;
 504     va_start(args, format);
 505     BOOL result = [self executeSQL:format arguments:args];
 506     va_end(args);
 507     return result;
 508 }
 509
 510 - (BOOL)executeSQL:(NSString *)format arguments:(va_list)args {
 511     NS_VALID_UNTIL_END_OF_SCOPE NSString *SQL = [[NSString alloc] initWithFormat:format arguments:args];
 512     if (!_db) {
 513         secerror("sfsqlite: Database is closed");
 514         return NO;
 515     }
 516     int execRet = sqlite3_exec(_db, [SQL UTF8String], NULL, NULL, NULL);
 517     if (execRet != SQLITE_OK) {
 518         secerror("sfsqlite: Error executing SQL: \"%@\" (%d)", SQL, execRet);
 519         return NO;
 520     }
 521
 522     return YES;
 523 }
 524
 525 - (SFSQLiteStatement *)statementForSQL:(NSString *)SQL {
 526     if (!_db) {
 527         secerror("sfsqlite: Database is closed");
 528         return nil;
 529     }
 530
 531     SFSQLiteStatement *statement = _statementsBySQL[SQL];
 532     if (statement) {
 533         NSAssert(statement.isReset, @"Statement not reset after last use: \"%@\"", SQL);
 534     } else {
 535         sqlite3_stmt *handle = NULL;
 536         NS_VALID_UNTIL_END_OF_SCOPE NSString *arcSafeSQL = SQL;
 537         if (sqlite3_prepare_v2(_db, [arcSafeSQL UTF8String], -1, &handle, NULL)) {
 538             secerror("Error preparing statement: %@", SQL);
 539             return nil;
 540         }
 541
 542         statement = [[SFSQLiteStatement alloc] initWithSQLite:self SQL:SQL handle:handle];
 543         _statementsBySQL[SQL] = statement;
 544     }
 545
 546     return statement;
 547 }
 548
 549 - (void)removeAllStatements {
 550     [[_statementsBySQL allValues] makeObjectsPerformSelector:@selector(finalizeStatement)];
 551     [_statementsBySQL removeAllObjects];
 552 }
 553
 554 - (NSArray *)allTableNames {
 555     NSMutableArray *tableNames = [[NSMutableArray alloc] init];
 556
 557     SFSQLiteStatement *statement = [self statementForSQL:@"select name from sqlite_master where type = 'table'"];
 558     while ([statement step]) {
 559         NSString *name = [statement textAtIndex:0];
 560         [tableNames addObject:name];
 561     }
 562     [statement reset];
 563
 564     return tableNames;
 565 }
 566
 567 - (void)dropAllTables {
 568     for (NSString *tableName in [self allTableNames]) {
 569         [self executeSQL:@"drop table %@", tableName];
 570     }
 571 }
 572
 573 - (NSString *)propertyForKey:(NSString *)key {
 574     NSAssert(key, @"Null key");
 575
 576     NSString *value = nil;
 577
 578     SFSQLiteStatement *statement = [self statementForSQL:@"select value from Properties where key = ?"];
 579     [statement bindText:key atIndex:0];
 580     if ([statement step]) {
 581         value = [statement textAtIndex:0];
 582     }
 583     [statement reset];
 584
 585     return value;
 586 }
 587
 588 - (void)setProperty:(NSString *)value forKey:(NSString *)key {
 589     NSAssert(key, @"Null key");
 590
 591     if (value) {
 592         SFSQLiteStatement *statement = [self statementForSQL:@"insert or replace into Properties (key, value) values (?,?)"];
 593         [statement bindText:key atIndex:0];
 594         [statement bindText:value atIndex:1];
 595         [statement step];
 596         [statement reset];
 597     } else {
 598         [self removePropertyForKey:key];
 599     }
 600 }
 601
 602 - (NSDateFormatter *)dateFormatter {
 603     if (!_dateFormatter) {
 604         NSDateFormatter* dateFormatter = [NSDateFormatter new];
 605         dateFormatter.dateFormat = @"yyyy-MM-dd'T'HH:mm:ssZZZZZ";
 606         _dateFormatter = dateFormatter;
 607     }
 608     return _dateFormatter;
 609 }
 610
 611 - (NSDate *)datePropertyForKey:(NSString *)key {
 612     NSString *dateStr = [self propertyForKey:key];
 613     if (dateStr.length) {
 614         return [self.dateFormatter dateFromString:dateStr];
 615     }
 616     return nil;
 617 }
 618
 619 - (void)setDateProperty:(NSDate *)value forKey:(NSString *)key {
 620     NSString *dateStr = nil;
 621     if (value) {
 622         dateStr = [self.dateFormatter stringFromDate:value];
 623     }
 624     [self setProperty:dateStr forKey:key];
 625 }
 626
 627 - (void)removePropertyForKey:(NSString *)key {
 628     NSAssert(key, @"Null key");
 629
 630     SFSQLiteStatement *statement = [self statementForSQL:@"delete from Properties where key = ?"];
 631     [statement bindText:key atIndex:0];
 632     [statement step];
 633     [statement reset];
 634 }
 635
 636 - (NSDate *)creationDate {
 637     return [NSDate dateWithTimeIntervalSinceReferenceDate:[[self propertyForKey:kSFSQLiteCreatedDateKey] floatValue]];
 638 }
 639
 640 // https://sqlite.org/pragma.html#pragma_table_info
 641 - (NSSet<NSString*> *)columnNamesForTable:(NSString*)tableName {
 642     SFSQLiteStatement *statement = [self statementForSQL:[NSString stringWithFormat:@"pragma table_info(%@)", tableName]];
 643     NSMutableSet<NSString*>* columnNames = [[NSMutableSet alloc] init];
 644     while ([statement step]) {
 645         [columnNames addObject:[statement textAtIndex:1]];
 646     }
 647     [statement reset];
 648     return columnNames;
 649 }
 650
 651 - (NSArray *)select:(NSArray *)columns from:(NSString *)tableName {
 652     return [self select:columns from:tableName where:nil bindings:nil];
 653 }
 654
 655 - (NSArray *)select:(NSArray *)columns from:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 656     NSMutableArray *results = [[NSMutableArray alloc] init];
 657
 658     NSMutableString *SQL = [NSMutableString stringWithFormat:@"select %@ from %@", [columns componentsJoinedByString:@", "], tableName];
 659     if (whereSQL) {
 660         [SQL appendFormat:@" where %@", whereSQL];
 661     }
 662
 663     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 664     [statement bindValues:bindings];
 665     while ([statement step]) {
 666         [results addObject:[statement allObjectsByColumnName]];
 667     }
 668     [statement reset];
 669
 670     return results;
 671 }
 672
 673 - (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 {
 674     @autoreleasepool {
 675         NSMutableString *SQL = [[NSMutableString alloc] init];
 676         NSString *columnsString = @"*";
 677         if ([columns count]) columnsString = [columns componentsJoinedByString:@", "];
 678         [SQL appendFormat:@"select %@ from %@", columnsString, tableName];
 679
 680         if (whereSQL.length) {
 681             [SQL appendFormat:@" where %@", whereSQL];
 682         }
 683         if (orderBy) {
 684             NSString *orderByString = [orderBy componentsJoinedByString:@", "];
 685             [SQL appendFormat:@" order by %@", orderByString];
 686         }
 687         if (limit) {
 688             [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 689         }
 690
 691         SFSQLiteStatement *statement = [self statementForSQL:SQL];
 692         [statement bindValues:bindings];
 693         do {
 694             @autoreleasepool {
 695                 if (![statement step]) {
 696                     break;
 697                 }
 698                 NSDictionary *stepResult = [statement allObjectsByColumnName];
 699                 if (block) {
 700                     BOOL stop = NO;
 701                     block(stepResult, &stop);
 702                     if (stop) {
 703                         break;
 704                     }
 705                 }
 706             }
 707         } while (1);
 708         [statement reset];
 709     }
 710 }
 711
 712 - (void)selectFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings orderBy:(NSArray *)orderBy limit:(NSNumber *)limit block:(void (^)(NSDictionary *resultDictionary, BOOL *stop))block {
 713     @autoreleasepool {
 714         NSMutableString *SQL = [[NSMutableString alloc] init];
 715         [SQL appendFormat:@"select * from %@", tableName];
 716
 717         if (whereSQL.length) {
 718             [SQL appendFormat:@" where %@", whereSQL];
 719         }
 720         if (orderBy) {
 721             NSString *orderByString = [orderBy componentsJoinedByString:@", "];
 722             [SQL appendFormat:@" order by %@", orderByString];
 723         }
 724         if (limit) {
 725             [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 726         }
 727
 728         SFSQLiteStatement *statement = [self statementForSQL:SQL];
 729         [statement bindValues:bindings];
 730         do {
 731             @autoreleasepool {
 732                 if (![statement step]) {
 733                     break;
 734                 }
 735                 NSDictionary *stepResult = [statement allObjectsByColumnName];
 736                 if (block) {
 737                     BOOL stop = NO;
 738                     block(stepResult, &stop);
 739                     if (stop) {
 740                         break;
 741                     }
 742                 }
 743             }
 744         } while (1);
 745         [statement reset];
 746     }
 747 }
 748
 749 - (NSArray *)selectFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings limit:(NSNumber *)limit {
 750     NSMutableString *SQL = [[NSMutableString alloc] init];
 751     [SQL appendFormat:@"select * from %@", tableName];
 752
 753     if (whereSQL.length) {
 754         [SQL appendFormat:@" where %@", whereSQL];
 755     }
 756     if (limit) {
 757         [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 758     }
 759
 760     NSMutableArray *results = [[NSMutableArray alloc] init];
 761
 762     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 763     [statement bindValues:bindings];
 764     while ([statement step]) {
 765         [results addObject:[statement allObjectsByColumnName]];
 766     }
 767     [statement reset];
 768
 769     return results;
 770 }
 771
 772 - (void)update:(NSString *)tableName set:(NSString *)setSQL where:(NSString *)whereSQL bindings:(NSArray *)whereBindings limit:(NSNumber *)limit {
 773     NSMutableString *SQL = [[NSMutableString alloc] init];
 774     [SQL appendFormat:@"update %@", tableName];
 775
 776     NSAssert(setSQL.length > 0, @"null set expression");
 777
 778     [SQL appendFormat:@" set %@", setSQL];
 779     if (whereSQL.length) {
 780         [SQL appendFormat:@" where %@", whereSQL];
 781     }
 782     if (limit) {
 783         [SQL appendFormat:@" limit %ld", (long)limit.integerValue];
 784     }
 785
 786     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 787     [statement bindValues:whereBindings];
 788     while ([statement step]) {
 789     }
 790     [statement reset];
 791 }
 792
 793 - (NSArray *)selectAllFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 794     return [self selectFrom:tableName where:whereSQL bindings:bindings limit:nil];
 795 }
 796
 797 - (NSUInteger)selectCountFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 798     NSArray *results = [self select:@[@"count(*) as n"] from:tableName where:whereSQL bindings:bindings];
 799     return [results[0][@"n"] unsignedIntegerValue];
 800 }
 801
 802 - (SFSQLiteRowID)insertOrReplaceInto:(NSString *)tableName values:(NSDictionary *)valuesByColumnName {
 803     NSArray *columnNames = [[valuesByColumnName allKeys] sortedArrayUsingSelector:@selector(compare:)];
 804     NSMutableArray *values = [[NSMutableArray alloc] init];
 805     for (NSUInteger i = 0; i < columnNames.count; i++) {
 806         values[i] = valuesByColumnName[columnNames[i]];
 807     }
 808
 809     NSMutableString *SQL = [[NSMutableString alloc] initWithString:@"insert or replace into "];
 810     [SQL appendString:tableName];
 811     [SQL appendString:@" ("];
 812     for (NSUInteger i = 0; i < columnNames.count; i++) {
 813         [SQL appendString:columnNames[i]];
 814         if (i != columnNames.count-1) {
 815             [SQL appendString:@","];
 816         }
 817     }
 818     [SQL appendString:@") values ("];
 819     for (NSUInteger i = 0; i < columnNames.count; i++) {
 820         if (i != columnNames.count-1) {
 821             [SQL appendString:@"?,"];
 822         } else {
 823             [SQL appendString:@"?"];
 824         }
 825     }
 826     [SQL appendString:@")"];
 827
 828     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 829     [statement bindValues:values];
 830     [statement step];
 831     [statement reset];
 832
 833     return [self lastInsertRowID];
 834 }
 835
 836 - (void)deleteFrom:(NSString *)tableName matchingValues:(NSDictionary *)valuesByColumnName {
 837     NSArray *columnNames = [[valuesByColumnName allKeys] sortedArrayUsingSelector:@selector(compare:)];
 838     NSMutableArray *values = [[NSMutableArray alloc] init];
 839     NSMutableString *whereSQL = [[NSMutableString alloc] init];
 840     int bindingCount = 0;
 841     for (NSUInteger i = 0; i < columnNames.count; i++) {
 842         id value = valuesByColumnName[columnNames[i]];
 843         [whereSQL appendString:columnNames[i]];
 844         if (!value || [[NSNull null] isEqual:value]) {
 845             [whereSQL appendString:@" is NULL"];
 846         } else {
 847             values[bindingCount++] = value;
 848             [whereSQL appendString:@"=?"];
 849         }
 850         if (i != columnNames.count-1) {
 851             [whereSQL appendString:@" AND "];
 852         }
 853     }
 854     [self deleteFrom:tableName where:whereSQL bindings:values];
 855 }
 856
 857 - (void)deleteFrom:(NSString *)tableName where:(NSString *)whereSQL bindings:(NSArray *)bindings {
 858     NSString *SQL = [NSString stringWithFormat:@"delete from %@ where %@", tableName, whereSQL];
 859
 860     SFSQLiteStatement *statement = [self statementForSQL:SQL];
 861     [statement bindValues:bindings];
 862     [statement step];
 863     [statement reset];
 864 }
 865
 866 - (NSString *)_tableNameForClass:(Class)objectClass {
 867     NSString *className = [objectClass SFSQLiteClassName];
 868     if (![className hasPrefix:_objectClassPrefix]) {
 869         secerror("sfsqlite: %@", [NSString stringWithFormat:@"Object class \"%@\" does not have prefix \"%@\"", className, _objectClassPrefix]);
 870         return nil;
 871     }
 872     return [className substringFromIndex:_objectClassPrefix.length];
 873 }
 874
 875 - (SInt32)dbUserVersion {
 876     SInt32 userVersion = 0;
 877     SFSQLiteStatement *statement = [self statementForSQL:@"pragma user_version"];
 878     while ([statement step]) {
 879         userVersion = [statement intAtIndex:0];
 880     }
 881     [statement reset];
 882
 883     return userVersion;
 884 }
 885
 886 @end