A Cocoa / Objective-C wrapper around SQLite
This is an Objective-C wrapper around SQLite.
https://groups.google.com/group/fmdb
https://www.sqlite.org/faq.html
Since FMDB is built on top of SQLite, you're going to want to read this page top to bottom at least once. And while you're there, make sure to bookmark the SQLite Documentation page: https://www.sqlite.org/docs.html
Do you have an awesome idea that deserves to be in FMDB? You might consider pinging ccgus first to make sure he hasn't already ruled it out for some reason. Otherwise pull requests are great, and make sure you stick to the local coding conventions. However, please be patient and if you haven't heard anything from ccgus for a week or more, you might want to send a note asking what's up.
FMDB can be installed using CocoaPods.
If you haven't done so already, you might want to initialize the project, to have it produce a
Podfiletemplate for you:
$ pod init
Then, edit the
Podfile, adding
FMDB:
# Uncomment the next line to define a global platform for your project # platform :ios, '9.0'target 'MyApp' do # Comment the next line if you're not using Swift and don't want to use dynamic frameworks use_frameworks!
# Pods for MyApp2 pod 'FMDB' # pod 'FMDB/FTS' # FMDB with FTS # pod 'FMDB/standalone' # FMDB with latest SQLite amalgamation source # pod 'FMDB/standalone/FTS' # FMDB with latest SQLite amalgamation source and FTS # pod 'FMDB/SQLCipher' # FMDB with SQLCipher
end
Then install the pods:
$ pod install
Then open the
.xcworkspacerather than the
.xcodeproj.
For more information on Cocoapods visit https://cocoapods.org.
If using FMDB with SQLCipher you must use the FMDB/SQLCipher subspec. The FMDB/SQLCipher subspec declares SQLCipher as a dependency, allowing FMDB to be compiled with the
-DSQLITE_HAS_CODECflag.
Once you make sure you have the latest version of Carthage, you can open up a command line terminal, navigate to your project's main directory, and then do the following commands:
$ echo ' github "ccgus/fmdb" ' > ./Cartfile $ carthage update
You can then configure your project as outlined in Carthage's Getting Started (i.e. for iOS, adding the framework to the "Link Binary with Libraries" in your target and adding the
copy-frameworksscript; in macOS, adding the framework to the list of "Embedded Binaries").
Declare FMDB as a package dependency.
swift .package( name: "FMDB", url: "https://github.com/ccgus/fmdb", .upToNextMinor(from: "2.7.7")),
Use FMDB in target dependencies
swift .product(name: "FMDB", package: "FMDB")
https://ccgus.github.io/fmdb/html/index.html
You can use either style in your Cocoa project. FMDB will figure out which you are using at compile time and do the right thing.
FMDB 2.7 attempts to support a more natural interface. This represents a fairly significant change for Swift developers (audited for nullability; shifted to properties in external interfaces where possible rather than methods; etc.). For Objective-C developers, this should be a fairly seamless transition (unless you were using the ivars that were previously exposed in the public interface, which you shouldn't have been doing, anyway!).
FMDB 2.7 is largely the same as prior versions, but has been audited for nullability. For Objective-C users, this simply means that if you perform a static analysis of your FMDB-based project, you may receive more meaningful warnings as you review your project, but there are likely to be few, if any, changes necessary in your code.
For Swift users, this nullability audit results in changes that are not entirely backward compatible with FMDB 2.6, but is a little more Swifty. Before FMDB was audited for nullability, Swift was forced to defensively assume that variables were optional, but the library now more accurately knows which properties and method parameters are optional, and which are not.
This means, though, that Swift code written for FMDB 2.7 may require changes. For example, consider the following Swift 3/Swift 4 code for FMDB 2.6:
queue.inTransaction { db, rollback in do { guard let db == db else { // handle error here return }try db.executeUpdate("INSERT INTO foo (bar) VALUES (?)", values: [1]) try db.executeUpdate("INSERT INTO foo (bar) VALUES (?)", values: [2]) } catch { rollback?.pointee = true }
}
Because FMDB 2.6 was not audited for nullability, Swift inferred that
dband
rollbackwere optionals. But, now, in FMDB 2.7, Swift now knows that, for example, neither
dbnor
rollbackabove can be
nil, so they are no longer optionals. Thus it becomes:
queue.inTransaction { db, rollback in do { try db.executeUpdate("INSERT INTO foo (bar) VALUES (?)", values: [1]) try db.executeUpdate("INSERT INTO foo (bar) VALUES (?)", values: [2]) } catch { rollback.pointee = true } }
In the past, when writing custom functions, you would have to generally include your own
@autoreleasepoolblock to avoid problems when writing functions that scanned through a large table. Now, FMDB will automatically wrap it in an autorelease pool, so you don't have to.
Also, in the past, when retrieving the values passed to the function, you had to drop down to the SQLite C API and include your own
sqlite3_value_XXXcalls. There are now
FMDatabasemethods,
valueInt,
valueString, etc., so you can stay within Swift and/or Objective-C, without needing to call the C functions yourself. Likewise, when specifying the return values, you no longer need to call
sqlite3_result_XXXC API, but rather you can use
FMDatabasemethods,
resultInt,
resultString, etc. There is a new
enumfor
valueTypecalled
SqliteValueType, which can be used for checking the type of parameter passed to the custom function.
Thus, you can do something like (as of Swift 3):
db.makeFunctionNamed("RemoveDiacritics", arguments: 1) { context, argc, argv in guard db.valueType(argv[0]) == .text || db.valueType(argv[0]) == .null else { db.resultError("Expected string parameter", context: context) return }if let string = db.valueString(argv[0])?.folding(options: .diacriticInsensitive, locale: nil) { db.resultString(string, context: context) } else { db.resultNull(context: context) }
}
And you can then use that function in your SQL (in this case, matching both "Jose" and "José"):
SELECT * FROM employees WHERE RemoveDiacritics(first_name) LIKE 'jose'
Note, the method
makeFunctionNamed:maximumArguments:withBlock:has been renamed to
makeFunctionNamed:arguments:block:, to more accurately reflect the functional intent of the second parameter.
In addition to the
makeFunctionNamednoted above, there are a few other API changes. Specifically,
To become consistent with the rest of the API, the methods
objectForColumnNameand
UTF8StringForColumnNamehave been renamed to
objectForColumnand
UTF8StringForColumn.
Note, the
objectForColumn(and the associted subscript operator) now returns
nilif an invalid column name/index is passed to it. It used to return
NSNull.
To avoid confusion with
FMDatabaseQueuemethod
inTransaction, which performs transactions, the
FMDatabasemethod to determine whether you are in a transaction or not,
inTransaction, has been replaced with a read-only property,
isInTransaction.
Several functions have been converted to properties, namely,
databasePath,
maxBusyRetryTimeInterval,
shouldCacheStatements,
sqliteHandle,
hasOpenResultSets,
lastInsertRowId,
changes,
goodConnection,
columnCount,
resultDictionary,
applicationID,
applicationIDString,
userVersion,
countOfCheckedInDatabases,
countOfCheckedOutDatabases, and
countOfOpenDatabases. For Objective-C users, this has little material impact, but for Swift users, it results in a slightly more natural interface. Note: For Objective-C developers, previously versions of FMDB exposed many ivars (but we hope you weren't using them directly, anyway!), but the implmentation details for these are no longer exposed.
In keeping with Apple's shift from paths to URLs, there are now
NSURLrenditions of the various
initmethods, previously only accepting paths.
There are three main classes in FMDB:
FMDatabase- Represents a single SQLite database. Used for executing SQL statements.
FMResultSet- Represents the results of executing a query on an
FMDatabase.
FMDatabaseQueue- If you're wanting to perform queries and updates on multiple threads, you'll want to use this class. It's described in the "Thread Safety" section below.
An
FMDatabaseis created with a path to a SQLite database file. This path can be one of these three:
@""). An empty database is created at a temporary location. This database is deleted when the
FMDatabaseconnection is closed.
NULL. An in-memory database is created. This database will be destroyed when the
FMDatabaseconnection is closed.
(For more information on temporary and in-memory databases, read the sqlite documentation on the subject: https://www.sqlite.org/inmemorydb.html)
NSString *path = [NSTemporaryDirectory() stringByAppendingPathComponent:@"tmp.db"]; FMDatabase *db = [FMDatabase databaseWithPath:path];
Before you can interact with the database, it must be opened. Opening fails if there are insufficient resources or permissions to open and/or create the database.
if (![db open]) { // [db release]; // uncomment this line in manual referencing code; in ARC, this is not necessary/permitted db = nil; return; }
Any sort of SQL statement which is not a
SELECTstatement qualifies as an update. This includes
CREATE,
UPDATE,
INSERT,
ALTER,
COMMIT,
BEGIN,
DETACH,
DELETE,
DROP,
END,
EXPLAIN,
VACUUM, and
REPLACEstatements (plus many more). Basically, if your SQL statement does not begin with
SELECT, it is an update statement.
Executing updates returns a single value, a
BOOL. A return value of
YESmeans the update was successfully executed, and a return value of
NOmeans that some error was encountered. You may invoke the
-lastErrorMessageand
-lastErrorCodemethods to retrieve more information.
A
SELECTstatement is a query and is executed via one of the
-executeQuery...methods.
Executing queries returns an
FMResultSetobject if successful, and
nilupon failure. You should use the
-lastErrorMessageand
-lastErrorCodemethods to determine why a query failed.
In order to iterate through the results of your query, you use a
while()loop. You also need to "step" from one record to the other. With FMDB, the easiest way to do that is like this:
FMResultSet *s = [db executeQuery:@"SELECT * FROM myTable"]; while ([s next]) { //retrieve values for each record }
You must always invoke
-[FMResultSet next]before attempting to access the values returned in a query, even if you're only expecting one:
FMResultSet *s = [db executeQuery:@"SELECT COUNT(*) FROM myTable"]; if ([s next]) { int totalCount = [s intForColumnIndex:0]; } [s close]; // Call the -close method on the FMResultSet if you cannot confirm whether the result set is exhausted.
FMResultSethas many methods to retrieve data in an appropriate format:
intForColumn:
longForColumn:
longLongIntForColumn:
boolForColumn:
doubleForColumn:
stringForColumn:
dateForColumn:
dataForColumn:
dataNoCopyForColumn:
UTF8StringForColumn:
objectForColumn:
Each of these methods also has a
{type}ForColumnIndex:variant that is used to retrieve the data based on the position of the column in the results, as opposed to the column's name.
Typically, there's no need to
-closean
FMResultSetyourself, since that happens when either the result set is exhausted. However, if you only pull out a single request or any other number of requests which don't exhaust the result set, you will need to call the
-closemethod on the
FMResultSet.
When you have finished executing queries and updates on the database, you should
-closethe
FMDatabaseconnection so that SQLite will relinquish any resources it has acquired during the course of its operation.
[db close];
FMDatabasecan begin and commit a transaction by invoking one of the appropriate methods or executing a begin/end transaction statement.
You can use
FMDatabase's executeStatements:withResultBlock: to do multiple statements in a string:
NSString *sql = @"create table bulktest1 (id integer primary key autoincrement, x text);" "create table bulktest2 (id integer primary key autoincrement, y text);" "create table bulktest3 (id integer primary key autoincrement, z text);" "insert into bulktest1 (x) values ('XXX');" "insert into bulktest2 (y) values ('YYY');" "insert into bulktest3 (z) values ('ZZZ');";success = [db executeStatements:sql];
sql = @"select count() as count from bulktest1;" "select count() as count from bulktest2;" "select count(*) as count from bulktest3;";
success = [self.db executeStatements:sql withResultBlock:^int(NSDictionary *dictionary) { NSInteger count = [dictionary[@"count"] integerValue]; XCTAssertEqual(count, 1, @"expected one record for dictionary %@", dictionary); return 0; }];
When providing a SQL statement to FMDB, you should not attempt to "sanitize" any values before insertion. Instead, you should use the standard SQLite binding syntax:
INSERT INTO myTable VALUES (?, ?, ?, ?)
The
?character is recognized by SQLite as a placeholder for a value to be inserted. The execution methods all accept a variable number of arguments (or a representation of those arguments, such as an
NSArray,
NSDictionary, or a
va_list), which are properly escaped for you.
And, to use that SQL with the
?placeholders from Objective-C:
NSInteger identifier = 42; NSString *name = @"Liam O'Flaherty (\"the famous Irish author\")"; NSDate *date = [NSDate date]; NSString *comment = nil;BOOL success = [db executeUpdate:@"INSERT INTO authors (identifier, name, date, comment) VALUES (?, ?, ?, ?)", @(identifier), name, date, comment ?: [NSNull null]]; if (!success) { NSLog(@"error = %@", [db lastErrorMessage]); }
Note: Fundamental data types, like the
NSIntegervariableidentifier, should be as aNSNumberobjects, achieved by using the@syntax, shown above. Or you can use the[NSNumber numberWithInt:identifier]syntax, too.Likewise, SQL
NULLvalues should be inserted as[NSNull null]. For example, in the case ofcommentwhich might benil(and is in this example), you can use thecomment ?: [NSNull null]syntax, which will insert the string ifcommentis notnil, but will insert[NSNull null]if it isnil.
In Swift, you would use
executeUpdate(values:), which not only is a concise Swift syntax, but also
throwserrors for proper error handling:
do { let identifier = 42 let name = "Liam O'Flaherty (\"the famous Irish author\")" let date = Date() let comment: String? = niltry db.executeUpdate("INSERT INTO authors (identifier, name, date, comment) VALUES (?, ?, ?, ?)", values: [identifier, name, date, comment ?? NSNull()])
} catch { print("error = (error)") }
Note: In Swift, you don't have to wrap fundamental numeric types like you do in Objective-C. But if you are going to insert an optional string, you would probably use the
comment ?? NSNull()syntax (i.e., if it isnil, useNSNull, otherwise use the string).
Alternatively, you may use named parameters syntax:
INSERT INTO authors (identifier, name, date, comment) VALUES (:identifier, :name, :date, :comment)
The parameters must start with a colon. SQLite itself supports other characters, but internally the dictionary keys are prefixed with a colon, do not include the colon in your dictionary keys.
NSDictionary *arguments = @{@"identifier": @(identifier), @"name": name, @"date": date, @"comment": comment ?: [NSNull null]}; BOOL success = [db executeUpdate:@"INSERT INTO authors (identifier, name, date, comment) VALUES (:identifier, :name, :date, :comment)" withParameterDictionary:arguments]; if (!success) { NSLog(@"error = %@", [db lastErrorMessage]); }
The key point is that one should not use
NSStringmethod
stringWithFormatto manually insert values into the SQL statement, itself. Nor should one Swift string interpolation to insert values into the SQL. Use
?placeholders for values to be inserted into the database (or used in
WHEREclauses in
SELECTstatements).
Using a single instance of
FMDatabasefrom multiple threads at once is a bad idea. It has always been OK to make a
FMDatabaseobject per thread. Just don't share a single instance across threads, and definitely not across multiple threads at the same time. Bad things will eventually happen and you'll eventually get something to crash, or maybe get an exception, or maybe meteorites will fall out of the sky and hit your Mac Pro. This would suck.
So don't instantiate a single
FMDatabaseobject and use it across multiple threads.
Instead, use
FMDatabaseQueue. Instantiate a single
FMDatabaseQueueand use it across multiple threads. The
FMDatabaseQueueobject will synchronize and coordinate access across the multiple threads. Here's how to use it:
First, make your queue.
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];
Then use it like so:
[queue inDatabase:^(FMDatabase *db) { [db executeUpdate:@"INSERT INTO myTable VALUES (?)", @1]; [db executeUpdate:@"INSERT INTO myTable VALUES (?)", @2]; [db executeUpdate:@"INSERT INTO myTable VALUES (?)", @3];FMResultSet *rs = [db executeQuery:@"select * from foo"]; while ([rs next]) { … }
}];
An easy way to wrap things up in a transaction can be done like this:
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) { [db executeUpdate:@"INSERT INTO myTable VALUES (?)", @1]; [db executeUpdate:@"INSERT INTO myTable VALUES (?)", @2]; [db executeUpdate:@"INSERT INTO myTable VALUES (?)", @3];if (whoopsSomethingWrongHappened) { *rollback = YES; return; } // etc ...
}];
The Swift equivalent would be:
queue.inTransaction { db, rollback in do { try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [1]) try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [2]) try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [3])if whoopsSomethingWrongHappened { rollback.pointee = true return } // etc ... } catch { rollback.pointee = true print(error) }
}
(Note, as of Swift 3, use
pointee. But in Swift 2.3, use
memoryrather than
pointee.)
FMDatabaseQueuewill run the blocks on a serialized queue (hence the name of the class). So if you call
FMDatabaseQueue's methods from multiple threads at the same time, they will be executed in the order they are received. This way queries and updates won't step on each other's toes, and every one is happy.
Note: The calls to
FMDatabaseQueue's methods are blocking. So even though you are passing along blocks, they will not be run on another thread.
You can do this! For an example, look for
-makeFunctionNamed:in main.m
You can use FMDB in Swift projects too.
To do this, you must:
.mand
.hfiles from the FMDB
srcfolder into your project.
You can copy all of them (which is easiest), or only the ones you need. Likely you will need
FMDatabaseand
FMResultSetat a minimum.
FMDatabaseAdditionsprovides some very useful convenience methods, so you will likely want that, too. If you are doing multithreaded access to a database,
FMDatabaseQueueis quite useful, too. If you choose to not copy all of the files from the
srcdirectory, though, you may want to update
FMDB.hto only reference the files that you included in your project.
Note, if you're copying all of the files from the
srcfolder into to your project (which is recommended), you may want to drag the individual files into your project, not the folder, itself, because if you drag the folder, you won't be prompted to add the bridging header (see next point).
For more information on bridging headers, see Swift and Objective-C in the Same Project.
In your bridging header, add a line that says: ```objc
Use the variations of
executeQueryand
executeUpdatewith the
sqland
valuesparameters with
trypattern, as shown below. These renditions of
executeQueryand
executeUpdateboth
throwerrors in true Swift fashion.
If you do the above, you can then write Swift code that uses
FMDatabase. For example, as of Swift 3:
let fileURL = try! FileManager.default .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true) .appendingPathComponent("test.sqlite")let database = FMDatabase(url: fileURL)
guard database.open() else { print("Unable to open database") return }
do { try database.executeUpdate("create table test(x text, y text, z text)", values: nil) try database.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", values: ["a", "b", "c"]) try database.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", values: ["e", "f", "g"])
let rs = try database.executeQuery("select x, y, z from test", values: nil) while rs.next() { if let x = rs.string(forColumn: "x"), let y = rs.string(forColumn: "y"), let z = rs.string(forColumn: "z") { print("x = \(x); y = \(y); z = \(z)") } }
} catch { print("failed: (error.localizedDescription)") }
database.close()
The history and changes are availbe on its GitHub page and are summarized in the "CHANGESANDTODO_LIST.txt" file.
The contributors to FMDB are contained in the "Contributors.txt" file.
Spaces, not tabs. Square brackets, not dot notation. Look at what FMDB already does with curly brackets and such, and stick to that style.
Reduce your bug down to the smallest amount of code possible. You want to make it super easy for the developers to see and reproduce your bug. If it helps, pretend that the person who can fix your bug is active on shipping 3 major products, works on a handful of open source projects, has a newborn baby, and is generally very very busy.
And we've even added a template function to main.m (FMDBReportABugFunction) in the FMDB distribution to help you out:
Then you can bring it up on the FMDB mailing list by showing your nice and compact FMDBReportABugFunction, or you can report the bug via the github FMDB bug reporter.
Optional:
Figure out where the bug is, fix it, and send a patch in or bring that up on the mailing list. Make sure all the other tests run after your modifications.
The support channels for FMDB are the mailing list (see above), filing a bug here, or maybe on Stack Overflow. So that is to say, support is provided by the community and on a voluntary basis.
FMDB development is overseen by Gus Mueller of Flying Meat. If FMDB been helpful to you, consider purchasing an app from FM or telling all your friends about it.
The license for FMDB is contained in the "License.txt" file.
If you happen to come across either Gus Mueller or Rob Ryan in a bar, you might consider purchasing a drink of their choosing if FMDB has been useful to you.
(The drink is for them of course, shame on you for trying to keep it.)