Analytics/SQLite/SFSQLite.m

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