blob: adf687cdbda35620d4c1665df796a2138d277d6c [file] [log] [blame]
//
// GTMSQLiteTest.m
//
// Copyright 2006-2008 Google Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License"); you may not
// use this file except in compliance with the License. You may obtain a copy
// of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
// WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
// License for the specific language governing permissions and limitations under
// the License.
//
#import "GTMSQLite.h"
#import "GTMSenTestCase.h"
@interface GTMSQLiteTest : GTMTestCase
@end
// This variable is used by a custom upper function that we set in a
// SQLite database to indicate that the custom function was
// successfully called. It has to be a global rather than instance
// variable because the custom upper function is not an instance function
static BOOL customUpperFunctionCalled = NO;
@interface GTMSQLiteStatementTest : GTMTestCase
@end
// Prototype for LIKE/GLOB test helper
static NSArray* LikeGlobTestHelper(GTMSQLiteDatabase *db, NSString *sql);
@implementation GTMSQLiteTest
// Test cases for change counting
- (void)testTransactionAPI {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
err = [db executeSQL:@"CREATE TABLE foo (bar TEXT COLLATE NOCASE_NONLITERAL);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to create table");
int changeCount = [db lastChangeCount];
XCTAssertEqual(changeCount, 0,
@"Change count was not 0 after creating database/table!");
err = [db executeSQL:@"insert into foo (bar) values ('blah!');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
changeCount = [db lastChangeCount];
XCTAssertEqual(changeCount, 1, @"Change count was not 1!");
// Test last row id!
unsigned long long lastRowId;
lastRowId = [db lastInsertRowID];
XCTAssertEqual(lastRowId, (unsigned long long)1L,
@"First row in database was not 1?");
// Test setting busy and retrieving it!
int busyTimeout = 10000;
err = [db setBusyTimeoutMS:busyTimeout];
XCTAssertEqual(err, SQLITE_OK, @"Error setting busy timeout");
int retrievedBusyTimeout;
retrievedBusyTimeout = [db busyTimeoutMS];
XCTAssertEqual(retrievedBusyTimeout, busyTimeout,
@"Retrieved busy time out was not equal to what we set it"
@" to!");
BOOL xactOpSucceeded;
xactOpSucceeded = [db beginDeferredTransaction];
XCTAssertTrue(xactOpSucceeded, @"beginDeferredTransaction failed!");
err = [db executeSQL:@"insert into foo (bar) values ('blah!');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
changeCount = [db lastChangeCount];
XCTAssertEqual(changeCount, 1,
@"Change count didn't stay the same"
@"when inserting during transaction");
xactOpSucceeded = [db rollback];
XCTAssertTrue(xactOpSucceeded, @"could not rollback!");
changeCount = [db lastChangeCount];
XCTAssertEqual(changeCount, 1, @"Change count isn't 1 after rollback :-(");
xactOpSucceeded = [db beginDeferredTransaction];
XCTAssertTrue(xactOpSucceeded, @"beginDeferredTransaction failed!");
for (unsigned int i = 0; i < 100; i++) {
err = [db executeSQL:@"insert into foo (bar) values ('blah!');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
}
xactOpSucceeded = [db commit];
XCTAssertTrue(xactOpSucceeded, @"could not commit!");
changeCount = [db totalChangeCount];
XCTAssertEqual(changeCount, 102, @"Change count isn't 102 after commit :-(");
}
- (void)testSQLiteWithoutCFAdditions {
int err;
GTMSQLiteDatabase *dbNoCFAdditions =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:NO
utf8:YES
errorCode:&err]
autorelease];
XCTAssertNotNil(dbNoCFAdditions, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
err = [dbNoCFAdditions executeSQL:nil];
XCTAssertEqual(err, SQLITE_MISUSE, @"Nil SQL did not return error");
err = [dbNoCFAdditions executeSQL:@"SELECT UPPER('Fred');"];
XCTAssertEqual(err, SQLITE_OK, @"Nil SQL did not return error");
}
- (void)testSynchronousAPI {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
[db synchronousMode:YES];
[db synchronousMode:NO];
}
- (void)testEmptyStringsCollation {
int err;
GTMSQLiteDatabase *db8 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
XCTAssertNotNil(db8, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
GTMSQLiteDatabase *db16 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:NO
errorCode:&err]
autorelease];
XCTAssertNotNil(db16, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
NSArray *databases = [NSArray arrayWithObjects:db8, db16, nil];
GTMSQLiteDatabase *db;
for (db in databases) {
err = [db executeSQL:
@"CREATE TABLE foo (bar TEXT COLLATE NOCASE_NONLITERAL,"
@" barrev text collate reverse);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for collation test");
// Create blank rows to test matching inside collation functions
err = [db executeSQL:@"insert into foo (bar, barrev) values ('','');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
// Insert one row we want to match
err = [db executeSQL:
@"INSERT INTO foo (bar, barrev) VALUES "
@"('teststring','teststring');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
NSString *matchString = @"foobar";
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:[NSString stringWithFormat:
@"SELECT bar FROM foo WHERE bar == '%@';", matchString]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
[statement stepRow];
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:[NSString stringWithFormat:
@"SELECT bar FROM foo WHERE barrev == '%@' order by barrev;", matchString]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
[statement stepRow];
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:[NSString stringWithFormat:
@"SELECT bar FROM foo WHERE bar == '';"]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
[statement stepRow];
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:[NSString stringWithFormat:
@"SELECT bar FROM foo WHERE barrev == '' order by barrev;"]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
[statement stepRow];
[statement finalizeStatement];
}
}
- (void)testUTF16Database {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:NO
errorCode:&err]
autorelease];
XCTAssertNotNil(db, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
err = [db executeSQL:@"CREATE TABLE foo (bar TEXT COLLATE NOCASE_NONLITERAL);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to create table for collation test");
// Insert one row we want to match
err = [db executeSQL:[NSString stringWithFormat:
@"INSERT INTO foo (bar) VALUES ('%@');",
[NSString stringWithCString:"Frédéric" encoding:NSUTF8StringEncoding]]];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
// Create blank rows to test matching inside collation functions
err = [db executeSQL:@"insert into foo (bar) values ('');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
err = [db executeSQL:@"insert into foo (bar) values ('');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
// Loop over a few things all of which should match
NSArray *testArray = [NSArray arrayWithObjects:
[NSString stringWithCString:"Frédéric"
encoding:NSUTF8StringEncoding],
[NSString stringWithCString:"frédéric"
encoding:NSUTF8StringEncoding],
[NSString stringWithCString:"FRÉDÉRIC"
encoding:NSUTF8StringEncoding],
nil];
NSString *testString = nil;
for (testString in testArray) {
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:[NSString stringWithFormat:
@"SELECT bar FROM foo WHERE bar == '%@';", testString]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
int count = 0;
while ([statement stepRow] == SQLITE_ROW) {
count++;
}
XCTAssertEqual(count, 1, @"Wrong number of collated rows for \"%@\"",
testString);
[statement finalizeStatement];
}
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:@"select * from foo;"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
while ([statement stepRow] == SQLITE_ROW) ;
[statement finalizeStatement];
}
- (void)testUpperLower {
// Test our custom UPPER/LOWER implementation, need a database and statement
// to do it.
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
XCTAssertNotNil(db, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
GTMSQLiteStatement *statement = nil;
// Test simple ASCII
statement = [GTMSQLiteStatement statementWithSQL:@"SELECT LOWER('Fred');"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertEqualObjects([statement resultStringAtPosition:0],
@"fred",
@"LOWER failed for ASCII string");
[statement finalizeStatement];
statement = [GTMSQLiteStatement statementWithSQL:@"SELECT UPPER('Fred');"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertEqualObjects([statement resultStringAtPosition:0],
@"FRED",
@"UPPER failed for ASCII string");
[statement finalizeStatement];
// Test UTF-8, have to do some dancing to make the compiler take
// UTF8 literals
NSString *utfNormalString =
[NSString stringWithCString:"Frédéric"
encoding:NSUTF8StringEncoding];
NSString *utfLowerString =
[NSString stringWithCString:"frédéric"
encoding:NSUTF8StringEncoding];
NSString *utfUpperString =
[NSString stringWithCString:"FRÉDÉRIC" encoding:NSUTF8StringEncoding];
statement =
[GTMSQLiteStatement statementWithSQL:
[NSString stringWithFormat:@"SELECT LOWER('%@');", utfNormalString]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertEqualObjects([statement resultStringAtPosition:0],
utfLowerString,
@"UPPER failed for UTF8 string");
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:
[NSString stringWithFormat:@"SELECT UPPER('%@');", utfNormalString]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertEqualObjects([statement resultStringAtPosition:0],
utfUpperString,
@"UPPER failed for UTF8 string");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE, @"Should be done");
[statement finalizeStatement];
}
- (void)testUpperLower16 {
// Test our custom UPPER/LOWER implementation, need a database and
// statement to do it.
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:NO
errorCode:&err]
autorelease];
XCTAssertNotNil(db, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
GTMSQLiteStatement *statement = nil;
// Test simple ASCII
statement = [GTMSQLiteStatement statementWithSQL:@"SELECT LOWER('Fred');"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertEqualObjects([statement resultStringAtPosition:0],
@"fred",
@"LOWER failed for ASCII string");
[statement finalizeStatement];
statement = [GTMSQLiteStatement statementWithSQL:@"SELECT UPPER('Fred');"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertEqualObjects([statement resultStringAtPosition:0],
@"FRED",
@"UPPER failed for ASCII string");
[statement finalizeStatement];
}
typedef struct {
BOOL upperCase;
int textRep;
} UpperLowerUserArgs;
static void TestUpperLower16Impl(sqlite3_context *context,
int argc, sqlite3_value **argv);
- (void)testUTF16DatabasesAreReallyUTF16 {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:NO
errorCode:&err]
autorelease];
const struct {
const char *sqlName;
UpperLowerUserArgs userArgs;
void *function;
} customUpperLower[] = {
{ "upper", { YES, SQLITE_UTF16 }, &TestUpperLower16Impl },
{ "upper", { YES, SQLITE_UTF16BE }, &TestUpperLower16Impl },
{ "upper", { YES, SQLITE_UTF16LE }, &TestUpperLower16Impl }
};
sqlite3 *sqldb = [db sqlite3DB];
int rc;
for (size_t i = 0;
i < (sizeof(customUpperLower) / sizeof(customUpperLower[0]));
i++) {
rc = sqlite3_create_function(sqldb,
customUpperLower[i].sqlName,
1,
customUpperLower[i].userArgs.textRep,
(void *)&customUpperLower[i].userArgs,
customUpperLower[i].function,
NULL,
NULL);
XCTAssertEqual(rc, SQLITE_OK,
@"Failed to register upper function"
@"with SQLite db");
}
customUpperFunctionCalled = NO;
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:@"SELECT UPPER('Fred');"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"Failed to step row");
XCTAssertTrue(customUpperFunctionCalled,
@"Custom upper function was not called!");
[statement finalizeStatement];
}
- (void)testLikeComparisonOptions {
int err;
GTMSQLiteDatabase *db8 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err] autorelease];
GTMSQLiteDatabase *db16 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:NO
errorCode:&err] autorelease];
NSArray *databases = [NSArray arrayWithObjects:db8, db16, nil];
GTMSQLiteDatabase *db;
for (db in databases) {
CFOptionFlags c = 0, oldFlags;
oldFlags = [db likeComparisonOptions];
// We'll do a case sensitivity test by making comparison options
// case insensitive
[db setLikeComparisonOptions:c];
XCTAssertTrue([db likeComparisonOptions] == 0,
@"LIKE Comparison options setter/getter does not work!");
NSString *createString = nil;
createString = @"CREATE TABLE foo (bar NODIACRITIC_WIDTHINSENSITIVE TEXT);";
err = [db executeSQL:createString];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for like comparison options test");
err = [db executeSQL:@"insert into foo values('test like test');"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create row for like comparison options test");
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:@"select * from foo where bar like '%LIKE%'"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE, @"failed to retrieve row!");
// Now change it back to case insensitive and rerun the same query
c |= kCFCompareCaseInsensitive;
[db setLikeComparisonOptions:c];
err = [statement reset];
XCTAssertEqual(err, SQLITE_OK, @"failed to reset select statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to retrieve row!");
c |= (kCFCompareDiacriticInsensitive | kCFCompareWidthInsensitive);
[db setLikeComparisonOptions:c];
// Make a new statement
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:@"select * from foo where bar like '%LIKE%'"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to retrieve row!");
// Now reset comparison options
[db setLikeComparisonOptions:oldFlags];
[statement finalizeStatement];
}
}
- (void)testGlobComparisonOptions {
int err;
GTMSQLiteDatabase *db = [[[GTMSQLiteDatabase alloc]
initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err] autorelease];
CFOptionFlags c = 0, oldFlags;
oldFlags = [db globComparisonOptions];
[db setGlobComparisonOptions:c];
XCTAssertTrue([db globComparisonOptions] == 0,
@"GLOB Comparison options setter/getter does not work!");
err = [db executeSQL:@"CREATE TABLE foo (bar TEXT);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for glob comparison options test");
err = [db executeSQL:@"insert into foo values('test like test');"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create row for glob comparison options test");
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:@"select * from foo where bar GLOB 'TEST*'"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE, @"failed to retrieve row!");
// Now change it back to case insensitive and rerun the same query
c |= kCFCompareCaseInsensitive;
[db setGlobComparisonOptions:c];
err = [statement reset];
XCTAssertEqual(err, SQLITE_OK, @"failed to reset select statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to retrieve row!");
[statement finalizeStatement];
// Now reset comparison options
[db setGlobComparisonOptions:oldFlags];
}
- (void)testCFStringReverseCollation {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err] autorelease];
err = [db executeSQL:@"CREATE table foo_reverse (bar TEXT COLLATE REVERSE);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for reverse collation test");
err = [db executeSQL:@"insert into foo_reverse values('a2');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
err = [db executeSQL:@"insert into foo_reverse values('b1');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:@"SELECT bar from foo_reverse order by bar"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to advance row");
NSString *oneRow = [statement resultStringAtPosition:0];
XCTAssertEqualStrings(oneRow, @"b1", @"b did not come first!");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to advance row!");
XCTAssertEqual(err, [db lastErrorCode],
@"lastErrorCode API did not match what last API returned!");
oneRow = [statement resultStringAtPosition:0];
XCTAssertEqualStrings(oneRow, @"a2", @"a did not come second!");
[statement finalizeStatement];
}
- (void)testCFStringNumericCollation {
int err;
GTMSQLiteDatabase *db = [[[GTMSQLiteDatabase alloc]
initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err] autorelease];
err = [db executeSQL:
@"CREATE table numeric_test_table "
@"(numeric_sort TEXT COLLATE NUMERIC, lexographic_sort TEXT);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for numeric collation test");
err = [db executeSQL:@"insert into numeric_test_table values('4','17');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
err = [db executeSQL:@"insert into numeric_test_table values('17','4');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:@"SELECT numeric_sort from numeric_test_table order by numeric_sort"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to advance row");
NSString *oneRow = [statement resultStringAtPosition:0];
XCTAssertEqualStrings(oneRow, @"4", @"4 did not come first!");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to advance row!");
oneRow = [statement resultStringAtPosition:0];
XCTAssertEqualStrings(oneRow, @"17", @"17 did not come second!");
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:
@"SELECT lexographic_sort from numeric_test_table "
@"order by lexographic_sort"
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"failed to create statement for lexographic sort");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to advance row");
oneRow = [statement resultStringAtPosition:0];
XCTAssertEqualStrings(oneRow, @"17", @"17 did not come first!");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW, @"failed to advance row!");
oneRow = [statement resultStringAtPosition:0];
XCTAssertEqualStrings(oneRow, @"4", @"4 did not come second!");
[statement finalizeStatement];
}
- (void)testCFStringCollation {
// Test just one case of the collations, they all exercise largely the
// same code
int err;
GTMSQLiteDatabase *db =
[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err];
XCTAssertNotNil(db, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
err = [db executeSQL:
@"CREATE TABLE foo (bar TEXT COLLATE NOCASE_NONLITERAL_LOCALIZED);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to create table for collation test");
// Insert one row we want to match
err = [db executeSQL:[NSString stringWithFormat:
@"INSERT INTO foo (bar) VALUES ('%@');",
[NSString stringWithCString:"Frédéric" encoding:NSUTF8StringEncoding]]];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute SQL");
// Loop over a few things all of which should match
NSArray *testArray = [NSArray arrayWithObjects:
[NSString stringWithCString:"Frédéric"
encoding:NSUTF8StringEncoding],
[NSString stringWithCString:"frédéric"
encoding:NSUTF8StringEncoding],
[NSString stringWithCString:"FRÉDÉRIC"
encoding:NSUTF8StringEncoding],
nil];
NSString *testString = nil;
for (testString in testArray) {
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:[NSString stringWithFormat:
@"SELECT bar FROM foo WHERE bar == '%@';", testString]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
int count = 0;
while ([statement stepRow] == SQLITE_ROW) {
count++;
}
XCTAssertEqual(count, 1, @"Wrong number of collated rows for \"%@\"",
testString);
[statement finalizeStatement];
}
// Force a release to test the statement cleanup
[db release];
}
- (void)testDiacriticAndWidthInsensitiveCollations {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err] autorelease];
XCTAssertNotNil(db, @"Failed to create DB");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create DB");
NSString *tableSQL =
@"CREATE TABLE FOOBAR (collated TEXT COLLATE NODIACRITIC_WIDTHINSENSITIVE, "
@" noncollated TEXT);";
err = [db executeSQL:tableSQL];
XCTAssertEqual(err, SQLITE_OK, @"error creating table");
NSString *testStringValue = [NSString stringWithCString:"Frédéric"
encoding:NSUTF8StringEncoding];
// Insert one row we want to match
err = [db executeSQL:[NSString stringWithFormat:
@"INSERT INTO FOOBAR (collated, noncollated) "
@"VALUES ('%@','%@');",
testStringValue, testStringValue]];
GTMSQLiteStatement *statement =
[GTMSQLiteStatement statementWithSQL:
[NSString stringWithFormat:@"SELECT noncollated FROM foobar"
@" WHERE noncollated == 'Frederic';"]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
// Make sure the comparison query didn't return a row because
// we're doing a comparison on the row without the collation
XCTAssertEqual([statement stepRow], SQLITE_DONE,
@"Comparison with diacritics did not succeed");
[statement finalizeStatement];
statement =
[GTMSQLiteStatement statementWithSQL:
[NSString stringWithFormat:@"SELECT collated FROM foobar"
@" WHERE collated == 'Frederic';"]
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
XCTAssertEqual([statement stepRow], SQLITE_ROW,
@"Comparison ignoring diacritics did not succeed");
[statement finalizeStatement];
}
- (void)testCFStringLikeGlob {
// Test cases drawn from SQLite test case source
int err;
GTMSQLiteDatabase *db8 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
XCTAssertNotNil(db8, @"Failed to create database");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create database");
GTMSQLiteDatabase *db16 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:NO
errorCode:&err]
autorelease];
XCTAssertNotNil(db16, @"Failed to create database");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create database");
NSArray *databases = [NSArray arrayWithObjects:db8, db16, nil];
GTMSQLiteDatabase *db;
for (db in databases) {
err = [db executeSQL:@"CREATE TABLE t1 (x TEXT);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for LIKE/GLOB test");
// Insert data set
err = [db executeSQL:@"INSERT INTO t1 VALUES ('a');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('ab');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('abc');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('abcd');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('acd');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('abd');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('bc');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('bcd');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('xyz');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('ABC');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('CDE');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t1 VALUES ('ABC abc xyz');"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
// Section 1, case tests
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;"),
([NSArray arrayWithObjects:@"ABC", @"abc", nil]),
@"Fail on LIKE test 1.1");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", nil]),
@"Fail on LIKE test 1.2");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;"),
([NSArray arrayWithObjects:@"ABC", @"abc", nil]),
@"Fail on LIKE test 1.3");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"ABC", @"ABC abc xyz", @"abc", @"abcd", nil]),
@"Fail on LIKE test 3.1");
[db setLikeComparisonOptions:(kCFCompareNonliteral)];
err = [db executeSQL:@"CREATE INDEX i1 ON t1(x);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 3.3");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", nil]),
@"Fail on LIKE test 3.5");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abcd", @"abd", nil]),
@"Fail on LIKE test 3.7");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 3.9");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abcd", @"bcd", nil]),
@"Fail on LIKE test 3.11");
[db setLikeComparisonOptions:(kCFCompareNonliteral | kCFCompareCaseInsensitive)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"ABC", @"ABC abc xyz", @"abc", @"abcd", nil]),
@"Fail on LIKE test 3.13");
[db setLikeComparisonOptions:(kCFCompareNonliteral)];
err = [db executeSQL:@"DROP INDEX i1;"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 3.15");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 3.17");
err = [db executeSQL:@"CREATE INDEX i1 ON t1(x);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 3.19");
[db setLikeComparisonOptions:(kCFCompareNonliteral)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 3.21");
[db setLikeComparisonOptions:(kCFCompareNonliteral |
kCFCompareCaseInsensitive)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abd", @"acd", nil]),
@"Fail on LIKE test 3.23");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x GLOB 'a[^xyz]d' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abd", @"acd", nil]),
@"Fail on glob inverted character set test 3.24");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x GLOB 'a[^' ORDER BY 1;"),
([NSArray array]),
@"Fail on glob inverted character set test 3.25");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x GLOB 'a['"),
([NSArray array]),
@"Unclosed glob character set did not return empty result set 3.26");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x GLOB 'a[^]'"),
([NSArray array]),
@"Unclosed glob inverted character set did not return empty "
@"result set 3.27");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x GLOB 'a[^]c]d'"),
([NSArray arrayWithObjects:@"abd", nil]),
@"Glob character set with inverted set not matching ] did not "
@"return right rows 3.28");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x GLOB 'a[bcdefg'"),
([NSArray array]),
@"Unclosed glob character set did not return empty result set 3.29");
// Section 4
[db setLikeComparisonOptions:(kCFCompareNonliteral)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 4.1");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 4.2");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 4.3");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x LIKE 'a[xyz]\%' ESCAPE ''"),
([NSArray array]),
@"0-Character escape clause did not return empty set 4.4");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x LIKE "
@"'a[xyz]\%' ESCAPE NULL"),
([NSArray array]),
@"Null escape did not return empty set 4.5");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x from t1 where x LIKE 'a[xyz]\\%' "
@"ESCAPE '\\'"),
([NSArray array]),
@"Literal percent match using ESCAPE clause did not return empty result "
@"set 4.6");
// Section 5
[db setLikeComparisonOptions:(kCFCompareNonliteral | kCFCompareCaseInsensitive)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"ABC", @"ABC abc xyz", @"abc", @"abcd", nil]),
@"Fail on LIKE test 5.1");
err = [db executeSQL:@"CREATE TABLE t2(x COLLATE NOCASE);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"INSERT INTO t2 SELECT * FROM t1;"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
err = [db executeSQL:@"CREATE INDEX i2 ON t2(x COLLATE NOCASE);"];
XCTAssertEqual(err, SQLITE_OK, @"Failed to execute sql");
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"ABC", @"ABC abc xyz", @"abcd", nil]),
@"Fail on LIKE test 5.3");
[db setLikeComparisonOptions:(kCFCompareNonliteral)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 5.5");
[db setLikeComparisonOptions:(kCFCompareNonliteral | kCFCompareCaseInsensitive)];
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abc", @"abcd", nil]),
@"Fail on LIKE test 5.5");
// Non standard tests not from the SQLite source
XCTAssertEqualObjects(
LikeGlobTestHelper(db,
@"SELECT x FROM t1 WHERE x GLOB 'a[b-d]d' ORDER BY 1;"),
([NSArray arrayWithObjects:@"abd", @"acd", nil]),
@"Fail on GLOB with character range");
}
}
- (void)testDescription {
int err;
GTMSQLiteDatabase *db8 =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
XCTAssertNotNil(db8, @"Failed to create database");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create database");
XCTAssertNotNil([db8 description]);
}
// // From GTMSQLite.m
// CFStringEncoding SqliteTextEncodingToCFStringEncoding(int enc);
// - (void)testEncodingMappingIsCorrect {
// XCTAssertTrue(SqliteTextEncodingToCFStringEncoding(SQLITE_UTF8) ==
// kCFStringEncodingUTF8,
// @"helper method didn't return right encoding for "
// @"kCFStringEncodingUTF8");
// XCTAssertTrue(SqliteTextEncodingToCFStringEncoding(SQLITE_UTF16BE)
// == kCFStringEncodingUTF16BE,
// @"helper method didn't return right encoding for "
// @"kCFStringEncodingUTF16BE");
// XCTAssertTrue(SqliteTextEncodingToCFStringEncoding(SQLITE_UTF16LE)
// == kCFStringEncodingUTF16LE,
// @"helper method didn't return right encoding for "
// @"kCFStringEncodingUTF16LE");
// XCTAssertTrue(SqliteTextEncodingToCFStringEncoding(9999)
// == kCFStringEncodingUTF8, @"helper method didn't "
// @"return default encoding for invalid input");
// }
@end
// Helper function for LIKE/GLOB testing
static NSArray* LikeGlobTestHelper(GTMSQLiteDatabase *db, NSString *sql) {
int err;
NSMutableArray *resultArray = [NSMutableArray array];
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:sql
inDatabase:db
errorCode:&err];
if (!statement || err != SQLITE_OK) return nil;
while ([statement stepRow] == SQLITE_ROW) {
id result = [statement resultFoundationObjectAtPosition:0];
if (result) [resultArray addObject:result];
}
if (err != SQLITE_DONE && err != SQLITE_OK) resultArray = nil;
[statement finalizeStatement];
return resultArray;
}
// =============================================================================
@implementation GTMSQLiteStatementTest
#pragma mark Parameters/binding tests
- (void)testInitAPI {
int err;
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:nil
inDatabase:nil
errorCode:&err];
XCTAssertNil(statement, @"Create statement succeeded with nil SQL string");
XCTAssertEqual(err, SQLITE_MISUSE, @"Err was not SQLITE_MISUSE on nil "
@"SQL string");
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
statement = [GTMSQLiteStatement statementWithSQL:@"select * from blah"
inDatabase:db
errorCode:&err];
XCTAssertNil(statement, @"Select statement succeeded with invalid table");
XCTAssertNotEqual(err, SQLITE_OK,
@"Err was not SQLITE_MISUSE on invalid table");
[statement finalizeStatement];
}
- (void)testParameterCountAPI {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
NSString *tableCreateSQL =
@"CREATE TABLE foo (tc TEXT,"
@"ic integer,"
@"rc real,"
@"bc blob);";
err = [db executeSQL:tableCreateSQL];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for collation test");
NSString *insert =
@"insert into foo (tc, ic, rc, bc) values (:tc, :ic, :rc, :bc);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
XCTAssertEqual([statement parameterCount], 4,
@"Bound parameter count was not 4");
[statement sqlite3Statement];
[statement finalizeStatement];
}
- (void)testPositionOfNamedParameterAPI {
int err;
GTMSQLiteDatabase *dbWithCF =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
GTMSQLiteDatabase *dbWithoutCF =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:NO
utf8:YES
errorCode:&err]
autorelease];
NSArray *databases = [NSArray arrayWithObjects:dbWithCF, dbWithoutCF, nil];
GTMSQLiteDatabase *db;
for (db in databases) {
NSString *tableCreateSQL =
@"CREATE TABLE foo (tc TEXT,"
@"ic integer,"
@"rc real,"
@"bc blob);";
err = [db executeSQL:tableCreateSQL];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for collation test");
NSString *insert =
@"insert into foo (tc, ic, rc, bc) "
@"values (:tc, :ic, :rc, :bc);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create statement");
NSArray *parameterNames = [NSArray arrayWithObjects:@":tc",
@":ic",
@":rc",
@":bc", nil];
for (unsigned int i = 1; i <= [parameterNames count]; i++) {
NSString *paramName = [parameterNames objectAtIndex:i-1];
// Cast to signed int to avoid type errors from XCTAssertEqual
XCTAssertEqual((int)i,
[statement positionOfParameterNamed:paramName],
@"positionOfParameterNamed API did not return correct "
@"results");
XCTAssertEqualStrings(paramName,
[statement nameOfParameterAtPosition:i],
@"nameOfParameterAtPosition API did not return "
@"correct name");
}
[statement finalizeStatement];
}
}
- (void)testBindingBlob {
int err;
const int BLOB_COLUMN = 0;
GTMSQLiteDatabase *dbWithCF =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
GTMSQLiteDatabase *dbWithoutCF =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:NO
utf8:YES
errorCode:&err]
autorelease];
NSArray *databases = [NSArray arrayWithObjects:dbWithCF, dbWithoutCF, nil];
GTMSQLiteDatabase *db;
for (db in databases) {
// Test strategy is to create a table with 3 columns
// Insert some values, and use the result collection APIs
// to make sure we get the same values back
err = [db executeSQL:
@"CREATE TABLE blobby (bc blob);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for BLOB binding test");
NSString *insert = @"insert into blobby (bc) values (:bc);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
char bytes[] = "DEADBEEF";
NSUInteger bytesLen = strlen(bytes);
NSData *originalBytes = [NSData dataWithBytes:bytes length:bytesLen];
err = [statement bindBlobAtPosition:1 data:originalBytes];
XCTAssertEqual(err, SQLITE_OK, @"error binding BLOB at position 1");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE, @"failed to insert BLOB for BLOB test");
[statement finalizeStatement];
NSString *selectSQL = @"select * from blobby;";
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
XCTAssertEqual([statement resultColumnCount], 1,
@"result had more columns than the table had?");
XCTAssertEqualStrings([statement resultColumnNameAtPosition:BLOB_COLUMN],
@"bc",
@"column name dictionary was not correct");
XCTAssertEqual([statement rowDataCount],
1,
@"More than one column returned?");
XCTAssertEqual([statement resultColumnTypeAtPosition:BLOB_COLUMN],
SQLITE_BLOB,
@"Query for column 1 of test table was not BLOB!");
NSData *returnedbytes = [statement resultBlobDataAtPosition:BLOB_COLUMN];
XCTAssertTrue([originalBytes isEqualToData:returnedbytes],
@"Queried data was not equal :-(");
[statement finalizeStatement];
}
}
- (void)testBindingNull {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
err = [db executeSQL:
@"CREATE TABLE foo (tc TEXT);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for NULL binding test");
NSString *insert = @"insert into foo (tc) values (:tc);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
err = [statement bindSQLNullAtPosition:1];
XCTAssertEqual(err, SQLITE_OK, @"error binding NULL at position 1");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE, @"failed to insert NULL for Null Binding test");
[statement finalizeStatement];
NSString *selectSQL = @"select 1 from foo where tc is NULL;";
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
[statement finalizeStatement];
}
- (void)testBindingDoubles {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
// Test strategy is to create a table with 2 real columns.
// For the first one, we'll use bindDoubleAtPosition
// For the second one, we'll use bindNumberAsDoubleAtPosition
// Then, for verification, we'll use a query that returns
// all rows where the columns are equal
double testVal = 42.42;
NSNumber *doubleValue = [NSNumber numberWithDouble:testVal];
err = [db executeSQL:
@"CREATE TABLE realTable (rc1 REAL, rc2 REAL);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for double binding test");
NSString *insert = @"insert into realTable (rc1, rc2) values (:rc1, :rc2);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
err = [statement bindDoubleAtPosition:1 value:testVal];
XCTAssertEqual(err, SQLITE_OK, @"error binding double at position 1");
err = [statement bindNumberAsDoubleAtPosition:2 number:doubleValue];
XCTAssertEqual(err, SQLITE_OK,
@"error binding number as double at "
@"position 2");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE,
@"failed to insert doubles for double "
@"binding test");
[statement finalizeStatement];
NSString *selectSQL = @"select rc1, rc2 from realTable where rc1 = rc2;";
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
double retrievedValue = [statement resultDoubleAtPosition:0];
XCTAssertEqualWithAccuracy(retrievedValue, testVal, 0.01,
@"Retrieved double did not equal "
@"original");
NSNumber *retrievedNumber = [statement resultNumberAtPosition:1];
XCTAssertEqualObjects(retrievedNumber, doubleValue,
@"Retrieved NSNumber object did not equal");
[statement finalizeStatement];
}
- (void) testResultCollectionAPI {
int err;
GTMSQLiteDatabase *dbWithCF =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
GTMSQLiteDatabase *dbWithoutCF =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:NO
utf8:YES
errorCode:&err]
autorelease];
NSArray *databases = [NSArray arrayWithObjects:dbWithCF, dbWithoutCF, nil];
GTMSQLiteDatabase *db;
for (db in databases) {
// Test strategy is to create a table with 3 columns
// Insert some values, and use the result collection APIs
// to make sure we get the same values back
err = [db executeSQL:
@"CREATE TABLE test (a integer, b text, c blob, d text);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for result collection test");
NSString *insert =
@"insert into test (a, b, c, d) "
@"values (42, 'text text', :bc, null);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
char blobChars[] = "DEADBEEF";
NSUInteger blobLength = strlen(blobChars);
NSData *blobData = [NSData dataWithBytes:blobChars length:blobLength];
err = [statement bindBlobAtPosition:1 data:blobData];
XCTAssertEqual(err, SQLITE_OK, @"error binding BLOB at position 1");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE,
@"failed to insert doubles for double "
@"binding test");
NSString *selectSQL = @"select * from test;";
[statement finalizeStatement];
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
XCTAssertNotNil([statement resultRowArray],
@"Failed to retrieve result array");
XCTAssertNotNil([statement resultRowDictionary],
@"Failed to retrieve result dictionary");
[statement finalizeStatement];
}
}
- (void) testBindingIntegers {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
// Test strategy is to create a table with 2 real columns.
// For the first one, we'll use bindIntegerAtPosition
// For the second one, we'll use bindNumberAsIntegerAtPosition
// Then, for verification, we'll use a query that returns
// all rows where the columns are equal
int testVal = 42;
NSNumber *intValue = [NSNumber numberWithInt:testVal];
err = [db executeSQL:
@"CREATE TABLE integerTable (ic1 integer, ic2 integer);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for integer binding test");
NSString *insert =
@"insert into integerTable (ic1, ic2) values (:ic1, :ic2);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
err = [statement bindInt32AtPosition:1 value:testVal];
XCTAssertEqual(err, SQLITE_OK, @"error binding integer at position 1");
err = [statement bindNumberAsInt32AtPosition:2 number:intValue];
XCTAssertEqual(err, SQLITE_OK,
@"error binding number as integer at "
@"position 2");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE,
@"failed to insert integers for integer "
@"binding test");
[statement finalizeStatement];
NSString *selectSQL = @"select ic1, ic2 from integerTable where ic1 = ic2;";
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
int retrievedValue = [statement resultInt32AtPosition:0];
XCTAssertEqual(retrievedValue, testVal,
@"Retrieved integer did not equal "
@"original");
NSNumber *retrievedNumber = [statement resultNumberAtPosition:1];
XCTAssertEqualObjects(retrievedNumber, intValue,
@"Retrieved NSNumber object did not equal");
[statement finalizeStatement];
}
- (void) testBindingLongLongs {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
// Test strategy is to create a table with 2 long long columns.
// For the first one, we'll use bindLongLongAtPosition
// For the second one, we'll use bindNumberAsLongLongAtPosition
// Then, for verification, we'll use a query that returns
// all rows where the columns are equal
long long testVal = LLONG_MAX;
NSNumber *longlongValue = [NSNumber numberWithLongLong:testVal];
err = [db executeSQL:
@"CREATE TABLE longlongTable (llc1 integer, llc2 integer);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for long long binding test");
NSString *insert =
@"insert into longlongTable (llc1, llc2) "
@"values (:llc1, :llc2);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
err = [statement bindLongLongAtPosition:1 value:testVal];
XCTAssertEqual(err, SQLITE_OK, @"error binding long long at position 1");
err = [statement bindNumberAsLongLongAtPosition:2 number:longlongValue];
XCTAssertEqual(err, SQLITE_OK,
@"error binding number as long long at "
@"position 2");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE,
@"failed to insert long longs for long long "
@"binding test");
[statement finalizeStatement];
NSString *selectSQL = @"select llc1, llc2 from longlongTable where llc1 = llc2;";
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
long long retrievedValue = [statement resultLongLongAtPosition:0];
XCTAssertEqual(retrievedValue, testVal,
@"Retrieved long long did not equal "
@"original");
NSNumber *retrievedNumber = [statement resultNumberAtPosition:1];
XCTAssertEqualObjects(retrievedNumber, longlongValue,
@"Retrieved NSNumber object did not equal");
[statement finalizeStatement];
}
- (void) testBindingString {
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
// Test strategy is to create a table with 1 string column
// Then, for verification, we'll use a query that returns
// all rows where the strings are equal
err = [db executeSQL:
@"CREATE TABLE stringTable (sc1 text);"];
XCTAssertEqual(err, SQLITE_OK,
@"Failed to create table for string binding test");
NSString *insert =
@"insert into stringTable (sc1) "
@"values (:sc1);";
GTMSQLiteStatement *statement = [GTMSQLiteStatement statementWithSQL:insert
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create insert statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create insert statement");
NSString *testVal = @"this is a test string";
err = [statement bindStringAtPosition:1 string:testVal];
XCTAssertEqual(err, SQLITE_OK, @"error binding string at position 1");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE,
@"failed to insert string for string binding test");
[statement finalizeStatement];
NSString *selectSQL =
[NSString stringWithFormat:@"select 1 from stringtable where sc1 = '%@';",
testVal];
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
err = [statement stepRow];
// Check that we got at least one row back
XCTAssertEqual(err, SQLITE_ROW, @"did not retrieve a row from db :-(");
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_DONE, @"retrieved more than 1 row from db :-(");
[statement finalizeStatement];
}
- (void)testThatNotFinalizingStatementsThrowsAssertion {
NSAutoreleasePool *localPool = [[NSAutoreleasePool alloc] init];
int err;
GTMSQLiteDatabase *db =
[[[GTMSQLiteDatabase alloc] initInMemoryWithCFAdditions:YES
utf8:YES
errorCode:&err]
autorelease];
XCTAssertNotNil(db, @"Failed to create database");
sqlite3 *sqlite3DB = [db sqlite3DB];
NSString *selectSQL = @"select 1";
GTMSQLiteStatement *statement;
statement = [GTMSQLiteStatement statementWithSQL:selectSQL
inDatabase:db
errorCode:&err];
XCTAssertNotNil(statement, @"Failed to create select statement");
XCTAssertEqual(err, SQLITE_OK, @"Failed to create select statement");
sqlite3_stmt *sqlite3Statment = [statement sqlite3Statement];
err = [statement stepRow];
XCTAssertEqual(err, SQLITE_ROW,
@"failed to step row for finalize test");
[localPool drain];
// Clean up leaks. Since we hadn't finalized the statement above we
// were unable to clean up the sqlite databases. Since the pool is drained
// all of our objective-c objects are gone, so we have to call the
// sqlite3 api directly.
XCTAssertEqual(sqlite3_finalize(sqlite3Statment), SQLITE_OK);
XCTAssertEqual(sqlite3_close(sqlite3DB), SQLITE_OK);
}
- (void)testCompleteSQLString {
NSString *str = @"CREATE TABLE longlongTable (llc1 integer, llc2 integer);";
BOOL isComplete = [GTMSQLiteStatement isCompleteStatement:str];
XCTAssertTrue(isComplete);
isComplete = [GTMSQLiteStatement isCompleteStatement:@""];
XCTAssertFalse(isComplete);
isComplete = [GTMSQLiteStatement isCompleteStatement:@"CR"];
XCTAssertFalse(isComplete);
}
- (void)testQuotingSQLString {
NSString *str = @"This is wild! It's fun!";
NSString *str2 = [GTMSQLiteStatement quoteAndEscapeString:str];
XCTAssertEqualObjects(str2, @"'This is wild! It''s fun!'");
str2 = [GTMSQLiteStatement quoteAndEscapeString:@""];
XCTAssertEqualObjects(str2, @"''");
}
- (void)testVersion {
XCTAssertGreaterThan([GTMSQLiteDatabase sqliteVersionNumber], 0);
XCTAssertNotNil([GTMSQLiteDatabase sqliteVersionString]);
}
@end
static void TestUpperLower16Impl(sqlite3_context *context,
int argc, sqlite3_value **argv) {
customUpperFunctionCalled = YES;
}