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