PageRenderTime 123ms CodeModel.GetById 25ms app.highlight 94ms RepoModel.GetById 1ms app.codeStats 0ms

/MapView/Map/FMDB/fmdb.m

http://github.com/route-me/route-me
Objective C | 496 lines | 304 code | 165 blank | 27 comment | 58 complexity | 448a881a68a1e7e3d71ce67140d18bdb MD5 | raw file
  1#import <Foundation/Foundation.h>
  2#import "FMDatabase.h"
  3#import "FMDatabaseAdditions.h"
  4
  5#define FMDBQuickCheck(SomeBool) { if (!(SomeBool)) { NSLog(@"Failure on line %d", __LINE__); return 123; } }
  6
  7int main (int argc, const char * argv[]) {
  8    NSAutoreleasePool * pool = [[NSAutoreleasePool alloc] init];
  9    
 10    // delete the old db.
 11    NSFileManager *fileManager = [NSFileManager defaultManager];
 12    [fileManager removeFileAtPath:@"/tmp/tmp.db" handler:nil];
 13    
 14    FMDatabase* db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
 15    if (![db open]) {
 16        NSLog(@"Could not open db.");
 17        [pool release];
 18        return 0;
 19    }
 20    
 21    // kind of experimentalish.
 22    [db setShouldCacheStatements:YES];
 23    
 24    // create a bad statement, just to test the error code.
 25    [db executeUpdate:@"blah blah blah"];
 26    
 27    FMDBQuickCheck([db hadError]);
 28    
 29    if ([db hadError]) {
 30        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
 31    }
 32    
 33    // but of course, I don't bother checking the error codes below.
 34    // Bad programmer, no cookie.
 35    
 36    [db executeUpdate:@"create table test (a text, b text, c integer, d double, e double)"];
 37    
 38    
 39    [db beginTransaction];
 40    int i = 0;
 41    while (i++ < 20) {
 42        [db executeUpdate:@"insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)" ,
 43            @"hi'", // look!  I put in a ', and I'm not escaping it!
 44            [NSString stringWithFormat:@"number %d", i],
 45            [NSNumber numberWithInt:i],
 46            [NSDate date],
 47            [NSNumber numberWithFloat:2.2f]];
 48    }
 49    [db commit];
 50    
 51    
 52    
 53    // do it again, just because
 54    [db beginTransaction];
 55    i = 0;
 56    while (i++ < 20) {
 57        [db executeUpdate:@"insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)" ,
 58         @"hi again'", // look!  I put in a ', and I'm not escaping it!
 59         [NSString stringWithFormat:@"number %d", i],
 60         [NSNumber numberWithInt:i],
 61         [NSDate date],
 62         [NSNumber numberWithFloat:2.2f]];
 63    }
 64    [db commit];
 65    
 66    
 67    
 68    
 69    
 70    FMResultSet *rs = [db executeQuery:@"select rowid,* from test where a = ?", @"hi'"];
 71    while ([rs next]) {
 72        // just print out what we've got in a number of formats.
 73        NSLog(@"%d %@ %@ %@ %@ %f %f",
 74              [rs intForColumn:@"c"],
 75              [rs stringForColumn:@"b"],
 76              [rs stringForColumn:@"a"],
 77              [rs stringForColumn:@"rowid"],
 78              [rs dateForColumn:@"d"],
 79              [rs doubleForColumn:@"d"],
 80              [rs doubleForColumn:@"e"]);
 81        
 82        
 83        if (!([[rs columnNameForIndex:0] isEqualToString:@"rowid"] &&
 84              [[rs columnNameForIndex:1] isEqualToString:@"a"])
 85              ) {
 86            NSLog(@"WHOA THERE BUDDY, columnNameForIndex ISN'T WORKING!");
 87            return 7;
 88        }
 89    }
 90    // close the result set.
 91    // it'll also close when it's dealloc'd, but we're closing the database before
 92    // the autorelease pool closes, so sqlite will complain about it.
 93    [rs close];  
 94    
 95    // ----------------------------------------------------------------------------------------
 96    // blob support.
 97    [db executeUpdate:@"create table blobTable (a text, b blob)"];
 98    
 99    // let's read in an image from safari's app bundle.
100    NSData *safariCompass = [NSData dataWithContentsOfFile:@"/Applications/Safari.app/Contents/Resources/compass.icns"];
101    if (safariCompass) {
102        [db executeUpdate:@"insert into blobTable (a, b) values (?,?)", @"safari's compass", safariCompass];
103        
104        rs = [db executeQuery:@"select b from blobTable where a = ?", @"safari's compass"];
105        if ([rs next]) {
106            safariCompass = [rs dataForColumn:@"b"];
107            [safariCompass writeToFile:@"/tmp/compass.icns" atomically:NO];
108            
109            // let's look at our fancy image that we just wrote out..
110            system("/usr/bin/open /tmp/compass.icns");
111            
112            // ye shall read the header for this function, or suffer the consequences.
113            safariCompass = [rs dataNoCopyForColumn:@"b"];
114            [safariCompass writeToFile:@"/tmp/compass_data_no_copy.icns" atomically:NO];
115            system("/usr/bin/open /tmp/compass_data_no_copy.icns");
116        }
117        else {
118            NSLog(@"Could not select image.");
119        }
120        
121        [rs close];
122        
123    }
124    else {
125        NSLog(@"Can't find compass image..");
126    }
127    
128    
129    // test out the convenience methods in +Additions
130    [db executeUpdate:@"create table t1 (a integer)"];
131    [db executeUpdate:@"insert into t1 values (?)", [NSNumber numberWithInt:5]];
132    int a = [db intForQuery:@"select a from t1 where a = ?", [NSNumber numberWithInt:5]];
133    if (a != 5) {
134        NSLog(@"intForQuery didn't work (a != 5)");
135    }
136    
137    // test the busy rety timeout schtuff.
138    
139    [db setBusyRetryTimeout:50000];
140    
141    FMDatabase *newDb = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
142    [newDb open];
143    
144    rs = [newDb executeQuery:@"select rowid,* from test where a = ?", @"hi'"];
145    [rs next]; // just grab one... which will keep the db locked.
146    
147    NSLog(@"Testing the busy timeout");
148    
149    BOOL success = [db executeUpdate:@"insert into t1 values (5)"];
150    
151    if (success) {
152        NSLog(@"Whoa- the database didn't stay locked!");
153        return 7;
154    }
155    else {
156        NSLog(@"Hurray, our timeout worked");
157    }
158    
159    [rs close];
160    [newDb close];
161    
162    success = [db executeUpdate:@"insert into t1 values (5)"];
163    if (!success) {
164        NSLog(@"Whoa- the database shouldn't be locked!");
165        return 8;
166    }
167    else {
168        NSLog(@"Hurray, we can insert again!");
169    }
170    
171    
172    
173    // test some nullness.
174    [db executeUpdate:@"create table t2 (a integer, b integer)"];
175    
176    if (![db executeUpdate:@"insert into t2 values (?, ?)", nil, [NSNumber numberWithInt:5]]) {
177        NSLog(@"UH OH, can't insert a nil value for some reason...");
178    }
179    
180    
181    
182    
183    rs = [db executeQuery:@"select * from t2"];
184    while ([rs next]) {
185        NSString *a = [rs stringForColumnIndex:0];
186        NSString *b = [rs stringForColumnIndex:1];
187        
188        if (a != nil) {
189            NSLog(@"%s:%d", __FUNCTION__, __LINE__);
190            NSLog(@"OH OH, PROBLEMO!");
191            return 10;
192        }
193        else {
194            NSLog(@"YAY, NULL VALUES");
195        }
196        
197        if (![b isEqualToString:@"5"]) {
198            NSLog(@"%s:%d", __FUNCTION__, __LINE__);
199            NSLog(@"OH OH, PROBLEMO!");
200            return 10;
201        }
202    }
203    
204    
205    
206    
207    
208    
209    
210    
211    
212    
213    // test some inner loop funkness.
214    [db executeUpdate:@"create table t3 (a somevalue)"];
215    
216    
217    // do it again, just because
218    [db beginTransaction];
219    i = 0;
220    while (i++ < 20) {
221        [db executeUpdate:@"insert into t3 (a) values (?)" , [NSNumber numberWithInt:i]];
222    }
223    [db commit];
224    
225    
226    
227    
228    rs = [db executeQuery:@"select * from t3"];
229    while ([rs next]) {
230        int foo = [rs intForColumnIndex:0];
231        
232        int newVal = foo + 100;
233        
234        [db executeUpdate:@"update t3 set a = ? where a = ?" , [NSNumber numberWithInt:newVal], [NSNumber numberWithInt:foo]];
235        
236        
237        FMResultSet *rs2 = [db executeQuery:@"select a from t3 where a = ?", [NSNumber numberWithInt:newVal]];
238        [rs2 next];
239        
240        if ([rs2 intForColumnIndex:0] != newVal) {
241            NSLog(@"Oh crap, our update didn't work out!");
242            return 9;
243        }
244        
245        [rs2 close];
246    }
247    
248    
249    // NSNull tests
250    [db executeUpdate:@"create table nulltest (a text, b text)"];
251    
252    [db executeUpdate:@"insert into nulltest (a, b) values (?, ?)" , [NSNull null], @"a"];
253    [db executeUpdate:@"insert into nulltest (a, b) values (?, ?)" , nil, @"b"];
254    
255    rs = [db executeQuery:@"select * from nulltest"];
256    
257    while ([rs next]) {
258        
259        NSString *a = [rs stringForColumnIndex:0];
260        NSString *b = [rs stringForColumnIndex:1];
261        
262        if (!b) {
263            NSLog(@"Oh crap, the nil / null inserts didn't work!");
264            return 10;
265        }
266        
267        if (a) {
268            NSLog(@"Oh crap, the nil / null inserts didn't work (son of error message)!");
269            return 11;
270        }
271        else {
272            NSLog(@"HURRAH FOR NSNULL (and nil)!");
273        }
274    }
275    
276    
277    
278    
279    
280    
281    // null dates
282    
283    NSDate *date = [NSDate date];
284    [db executeUpdate:@"create table datetest (a double, b double, c double)"];
285    [db executeUpdate:@"insert into datetest (a, b, c) values (?, ?, 0)" , [NSNull null], date];
286    
287    rs = [db executeQuery:@"select * from datetest"];
288    
289    while ([rs next]) {
290        
291        NSDate *a = [rs dateForColumnIndex:0];
292        NSDate *b = [rs dateForColumnIndex:1];
293        NSDate *c = [rs dateForColumnIndex:2];
294        
295        if (a) {
296            NSLog(@"Oh crap, the null date insert didn't work!");
297            return 12;
298        }
299        
300        if (!c) {
301            NSLog(@"Oh crap, the 0 date insert didn't work!");
302            return 12;
303        }
304        
305        NSTimeInterval dti = fabs([b timeIntervalSinceDate:date]);
306        
307        if (floor(dti) > 0.0) {
308            NSLog(@"Date matches didn't really happen... time difference of %f", dti);
309            return 13;
310        }
311        
312        
313        dti = fabs([c timeIntervalSinceDate:[NSDate dateWithTimeIntervalSince1970:0]]);
314        
315        if (floor(dti) > 0.0) {
316            NSLog(@"Date matches didn't really happen... time difference of %f", dti);
317            return 13;
318        }
319    }
320    
321    NSDate *foo = [db dateForQuery:@"select b from datetest where c = 0"];
322    assert(foo);
323    NSTimeInterval dti = fabs([foo timeIntervalSinceDate:date]);
324    if (floor(dti) > 0.0) {
325        NSLog(@"Date matches didn't really happen... time difference of %f", dti);
326        return 14;
327    }
328    
329    [db executeUpdate:@"create table nulltest2 (s text, d data, i integer, f double, b integer)"];
330    
331    [db executeUpdate:@"insert into nulltest2 (s, d, i, f, b) values (?, ?, ?, ?, ?)" , @"Hi", safariCompass, [NSNumber numberWithInt:12], [NSNumber numberWithFloat:4.4], [NSNumber numberWithBool:YES]];
332    [db executeUpdate:@"insert into nulltest2 (s, d, i, f, b) values (?, ?, ?, ?, ?)" , nil, nil, nil, nil, [NSNull null]];
333    
334    rs = [db executeQuery:@"select * from nulltest2"];
335    
336    while ([rs next]) {
337        
338        int i = [rs intForColumnIndex:2];
339        
340        if (i == 12) {
341            // it's the first row we inserted.
342            FMDBQuickCheck(![rs columnIndexIsNull:0]);
343            FMDBQuickCheck(![rs columnIndexIsNull:1]);
344            FMDBQuickCheck(![rs columnIndexIsNull:2]);
345            FMDBQuickCheck(![rs columnIndexIsNull:3]);
346            FMDBQuickCheck(![rs columnIndexIsNull:4]);
347            FMDBQuickCheck( [rs columnIndexIsNull:5]);
348            
349            FMDBQuickCheck([[rs dataForColumn:@"d"] length] == [safariCompass length]);
350            FMDBQuickCheck(![rs dataForColumn:@"notthere"]);
351            FMDBQuickCheck(![rs stringForColumnIndex:-2]);
352            FMDBQuickCheck([rs boolForColumnIndex:4]);
353            FMDBQuickCheck([rs boolForColumn:@"b"]);
354            
355            FMDBQuickCheck(fabs(4.4 - [rs doubleForColumn:@"f"]) < 0.0000001);
356            
357            FMDBQuickCheck(12 == [rs intForColumn:@"i"]);
358            FMDBQuickCheck(12 == [rs intForColumnIndex:2]);
359            
360            FMDBQuickCheck(0 == [rs intForColumnIndex:12]); // there is no 12
361            FMDBQuickCheck(0 == [rs intForColumn:@"notthere"]);
362            
363            FMDBQuickCheck(12 == [rs longForColumn:@"i"]);
364            FMDBQuickCheck(12 == [rs longLongIntForColumn:@"i"]);
365        }
366        else {
367            // let's test various null things.
368            
369            FMDBQuickCheck([rs columnIndexIsNull:0]);
370            FMDBQuickCheck([rs columnIndexIsNull:1]);
371            FMDBQuickCheck([rs columnIndexIsNull:2]);
372            FMDBQuickCheck([rs columnIndexIsNull:3]);
373            FMDBQuickCheck([rs columnIndexIsNull:4]);
374            FMDBQuickCheck([rs columnIndexIsNull:5]);
375            
376            
377            FMDBQuickCheck(![rs dataForColumn:@"d"]);
378            
379        }
380    }
381    
382    
383    
384    {
385        [db executeUpdate:@"create table testOneHundredTwelvePointTwo (a text, b integer)"];
386        [db executeUpdate:@"insert into testOneHundredTwelvePointTwo values (?, ?)" withArgumentsInArray:[NSArray arrayWithObjects:@"one", [NSNumber numberWithInteger:2], nil]];
387        [db executeUpdate:@"insert into testOneHundredTwelvePointTwo values (?, ?)" withArgumentsInArray:[NSArray arrayWithObjects:@"one", [NSNumber numberWithInteger:3], nil]];
388        
389        
390        rs = [db executeQuery:@"select * from testOneHundredTwelvePointTwo where b > ?" withArgumentsInArray:[NSArray arrayWithObject:[NSNumber numberWithInteger:1]]];
391        
392        FMDBQuickCheck([rs next]);
393        
394        FMDBQuickCheck([rs hasAnotherRow]);
395        FMDBQuickCheck(![db hadError]);
396        
397        FMDBQuickCheck([[rs stringForColumnIndex:0] isEqualToString:@"one"]);
398        FMDBQuickCheck([rs intForColumnIndex:1] == 2);
399        
400        FMDBQuickCheck([rs next]);
401        
402        FMDBQuickCheck([rs intForColumnIndex:1] == 3);
403        
404        FMDBQuickCheck(![rs next]);
405        FMDBQuickCheck(![rs hasAnotherRow]);
406        
407    }
408    
409    {
410        
411        FMDBQuickCheck([db executeUpdate:@"create table t4 (a text, b text)"]);
412        FMDBQuickCheck(([db executeUpdate:@"insert into t4 (a, b) values (?, ?)", @"one", @"two"]));
413        
414        rs = [db executeQuery:@"select t4.a as 't4.a', t4.b from t4;"];
415        
416        FMDBQuickCheck((rs != nil));
417        
418        [rs next];
419        
420        FMDBQuickCheck([[rs stringForColumn:@"t4.a"] isEqualToString:@"one"]);
421        FMDBQuickCheck([[rs stringForColumn:@"b"] isEqualToString:@"two"]);
422        
423        FMDBQuickCheck(strcmp((const char*)[rs UTF8StringForColumnName:@"b"], "two") == 0);
424        
425        [rs close];
426        
427        // let's try these again, with the withArgumentsInArray: variation
428        FMDBQuickCheck([db executeUpdate:@"drop table t4;" withArgumentsInArray:[NSArray array]]);
429        FMDBQuickCheck([db executeUpdate:@"create table t4 (a text, b text)" withArgumentsInArray:[NSArray array]]);
430        FMDBQuickCheck(([db executeUpdate:@"insert into t4 (a, b) values (?, ?)" withArgumentsInArray:[NSArray arrayWithObjects:@"one", @"two", nil]]));
431        
432        rs = [db executeQuery:@"select t4.a as 't4.a', t4.b from t4;" withArgumentsInArray:[NSArray array]];
433        
434        FMDBQuickCheck((rs != nil));
435        
436        [rs next];
437        
438        FMDBQuickCheck([[rs stringForColumn:@"t4.a"] isEqualToString:@"one"]);
439        FMDBQuickCheck([[rs stringForColumn:@"b"] isEqualToString:@"two"]);
440        
441        FMDBQuickCheck(strcmp((const char*)[rs UTF8StringForColumnName:@"b"], "two") == 0);
442        
443        [rs close];
444        
445        
446        
447        
448        
449        
450    }
451    
452    
453    
454    
455    {
456        FMDBQuickCheck([db tableExists:@"t4"]);
457        FMDBQuickCheck(![db tableExists:@"thisdoesntexist"]);
458        
459        rs = [db getSchema];
460        while ([rs next]) {
461            FMDBQuickCheck([[rs stringForColumn:@"type"] isEqualToString:@"table"]);
462        }
463        
464        
465    }
466    
467    
468    
469    
470    
471    // just for fun.
472    rs = [db executeQuery:@"PRAGMA database_list"];
473    while ([rs next]) {
474        NSString *file = [rs stringForColumn:@"file"];
475        NSLog(@"database_list: %@", file);
476    }
477    
478    
479    // print out some stats if we are using cached statements.
480    if ([db shouldCacheStatements]) {
481        
482        NSEnumerator *e = [[db cachedStatements] objectEnumerator];;
483        FMStatement *statement;
484        
485        while ((statement = [e nextObject])) {
486        	NSLog(@"%@", statement);
487        }
488    }
489    NSLog(@"That was version %@ of sqlite", [FMDatabase sqliteLibVersion]);
490    
491    
492    [db close];
493    
494    [pool release];
495    return 0;
496}