I am having an issue where I am trying to insert rows into a db only if they do not exist. If I inset certain characters, FMDB or SQLite is barfing and assuming each row is new:
I expect each set to insert the first record, and then update it:
2014-12-03 14:50:32.554 Dapper[94814:1592610] SET 0
2014-12-03 14:50:32.554 Dapper[94814:1592610] Inserted a record for an item found in iTunes: KeyItemWith PI Π.M4A 1416284085
2014-12-03 14:50:32.555 Dapper[94814:1592610] Updated a record for an item found in iTunes: KeyItemWith PI Π.M4A 1416284085
2014-12-03 14:50:32.556 Dapper[94814:1592610] SET 1
2014-12-03 14:50:32.556 Dapper[94814:1592610] Inserted a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 Π.M4A 1416284085
2014-12-03 14:50:32.557 Dapper[94814:1592610] Updated a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 Π.M4A 1416284085
2014-12-03 14:50:32.558 Dapper[94814:1592610] SET 2
2014-12-03 14:50:32.571 Dapper[94814:1592610] Inserted a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 NEW.M4A 1416284085
2014-12-03 14:50:32.574 Dapper[94814:1592610] Updated a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 NEW.M4A 1416284085
But I get (notice set 1, 2 fail, set3 is fine):
2014-12-03 14:50:32.554 Dapper[94814:1592610] SET 0
2014-12-03 14:50:32.554 Dapper[94814:1592610] Inserted a record for an item found in iTunes: KeyItemWith PI Π.M4A 1416284085
2014-12-03 14:50:32.555 Dapper[94814:1592610] Inserted a record for an item found in iTunes: KeyItemWith PI Π.M4A 1416284085
2014-12-03 14:50:32.556 Dapper[94814:1592610] SET 1
2014-12-03 14:50:32.556 Dapper[94814:1592610] Inserted a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 Π.M4A 1416284085
2014-12-03 14:50:32.557 Dapper[94814:1592610] Inserted a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 Π.M4A 1416284085
2014-12-03 14:50:32.558 Dapper[94814:1592610] SET 2
2014-12-03 14:50:32.571 Dapper[94814:1592610] Inserted a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 NEW.M4A 1416284085
2014-12-03 14:50:32.574 Dapper[94814:1592610] Updated a record for an item found in iTunes: /Volumes/X5 TF1/Radwimps/New/08 NEW.M4A 1416284085
Here is the code:
#import "AppDelegate.h"
#import "FMDB.h"
@implementation AppDelegate
FMDatabase *leftDB;
FMDatabase *rightDB;
-(void)deleteDB
{
NSFileManager *fileManager = [NSFileManager defaultManager];//create instance of NSFileManager
NSString *dapperDirectory=@"/Users/jeremylaurenson/Desktop/";
NSString *dapperDB=[dapperDirectory stringByAppendingPathComponent:@"dapperleft.sqlite"];
NSLog(@"Deleting database at %@",dapperDB);
[fileManager removeItemAtPath:dapperDB error:nil];
dapperDB=[dapperDirectory stringByAppendingPathComponent:@"dapperright.sqlite"];
NSLog(@"Deleting database at %@",dapperDB);
[fileManager removeItemAtPath:dapperDB error:nil];
[self openDB:1];
[self createTable:1];
[self closeDB:1];
}
-(void)openDB:(int)side
{
FMDatabase *db;
NSString *dapperDirectory=@"/Users/jeremylaurenson/Desktop/";
NSString *dapperDB;
if(side==1)dapperDB=[dapperDirectory stringByAppendingPathComponent:@"dapperleft.sqlite"];
if(side==2)dapperDB=[dapperDirectory stringByAppendingPathComponent:@"dapperright.sqlite"];
NSLog(@"Opening database at %@",dapperDB);
db = [FMDatabase databaseWithPath:dapperDB];
if (![db open]) {
NSLog(@"Could not open db.");
}
if(side==1)leftDB=db;
if(side==2)rightDB=db;
}
-(void)createTable:(int)side
{
FMDatabase *db;
if(side==1)db=leftDB;
if(side==2)db=rightDB;
[db executeUpdate:@"CREATE TABLE `items` ( `key` TEXT, `filepath` TEXT, `sourceFile` TEXT, `UUID` TEXT, `DAPsize` NUMERIC, `OSXsize` REAL, `DAPdate` NUMERIC, `OSXdate` NUMERIC, `MustUpdate` INTEGER, `MustDelete` INTEGER , `isOnDAP` INTEGER , `SandBoxBookMark` TEXT,`tombStone` INTEGER);"];
}
-(BOOL)zeroDatabase:(int)side
{
FMDatabase *db;
if(side==1)db=leftDB;
if(side==2)db=rightDB;
if(![db open]){
NSLog(@"DB is closed. Can not zero out");
return FALSE;
}
NSLog(@"Clearing flags in the database");
[db executeUpdate:@"update `items` set MustDelete=0,MustUpdate=0,tombStone=1"];
if([db hadError]){
NSLog(@"Database error");
return FALSE;
}
NSLog(@"Done clearing flags in the database");
return TRUE;
}
-(void)closeDB:(int)side
{
FMDatabase *db;
if(side==1)db=leftDB;
if(side==2)db=rightDB;
if(![db open]){
[db close];
}
}
-(void)insertItemFromiTunes:(NSString *)dapPath withSource:(NSString *)sourcePath withSize:(NSNumber *)size withDate:(NSDate *)date withUUID:(NSString *)UUID withSide:(int)side withSandboxBookmark:(NSString *)bookmark
{
FMDatabase *db;
if(side==1)db=leftDB;
if(side==2)db=rightDB;
BOOL updateOperation=FALSE;
if(![db open]){
NSLog(@"DB is closed. Can not insertitemfromOSX");
return;
}
NSNumber *convertedDate=[NSNumber numberWithDouble:[date timeIntervalSince1970]];
FMResultSet *results = [db executeQuery:@"select count(*) from `items` where lower(key) = lower(?)",dapPath,nil];
while([results next])
{
if([results unsignedLongLongIntForColumnIndex:0]>0){
updateOperation=TRUE;
}
}
[results close];
if(updateOperation){
NSLog(@"Updated a record for an item found in iTunes: %@ %@ ",dapPath,size);
[db executeUpdate:@"update `items` set OSXsize=?, OSXDate=?, UUID=?,sourceFile=?,tombStone=0,SandBoxBookMark=? where lower(key)=lower(?)",
size,convertedDate,UUID,sourcePath,bookmark,dapPath, nil];
}
else
{
NSLog(@"Inserted a record for an item found in iTunes: %@ %@ ",dapPath,size);
[db executeUpdate:@"insert into `items`(key,filepath, OSXsize, OSXdate,UUID,sourceFile,tombStone,SandBoxBookMark) values(?,?,?,?,?,?,0,?)",
[dapPath lowercaseString], dapPath, size,convertedDate,UUID,sourcePath,bookmark,nil];
}
}
- (void)applicationWillFinishLaunching:(NSNotification *)aNotification {
// Offer to the move the Application if necessary.
// Note that if the user chooses to move the application,
// this call will never return. Therefore you can suppress
// any first run UI by putting it after this call.
}
- (void)applicationWillTerminate:(NSNotification *)aNotification
{
[self closeDB:1];
[self closeDB:2];
}
- (void)applicationDidFinishLaunching:(NSNotification *)aNotification
{
[self deleteDB];;
[self openDB:1];
NSLog(@"SET 0");
[self insertItemFromiTunes:@"KeyItemWith PI Π.M4A" withSource:@"Some Pie.m4a" withSize:[NSNumber numberWithInt:1416284085] withDate:[NSDate date] withUUID:nil withSide:1 withSandboxBookmark:@"PiePieIs PI"];
[self insertItemFromiTunes:@"KeyItemWith PI Π.M4A" withSource:@"Some Pie.m4a" withSize:[NSNumber numberWithInt:1416284085] withDate:[NSDate date] withUUID:nil withSide:1 withSandboxBookmark:@"PiePieIs PI"];
NSLog(@"SET 1");
[self insertItemFromiTunes:@"/Volumes/X5 TF1/Radwimps/New/08 Π.M4A" withSource:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a" withSize:[NSNumber numberWithInt:1416284085] withDate:[NSDate date] withUUID:nil withSide:1 withSandboxBookmark:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a"];
[self insertItemFromiTunes:@"/Volumes/X5 TF1/Radwimps/New/08 Π.M4A" withSource:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a" withSize:[NSNumber numberWithInt:1416284085] withDate:[NSDate date] withUUID:nil withSide:1 withSandboxBookmark:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a"];
NSLog(@"SET 2");
[self insertItemFromiTunes:@"/Volumes/X5 TF1/Radwimps/New/08 NEW.M4A" withSource:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a" withSize:[NSNumber numberWithInt:1416284085] withDate:[NSDate date] withUUID:nil withSide:1 withSandboxBookmark:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a"];
[self insertItemFromiTunes:@"/Volumes/X5 TF1/Radwimps/New/08 NEW.M4A" withSource:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a" withSize:[NSNumber numberWithInt:1416284085] withDate:[NSDate date] withUUID:nil withSide:1 withSandboxBookmark:@"file:///Users/jeremylaurenson/Music/iTunes/iTunes%20Media/Music/RADWIMPS/%E7%B5%B6%E4%BD%93%E7%B5%B6%E5%91%BD/08%20%CF%80.m4a"];
[self closeDB:1];
[[NSApplication sharedApplication] terminate:self];
}
I switched my lowercase conversion all into cocoa, so that the SQL didnt do a lower() call. Fixed the issue.