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