Open bluelobe opened 10 years ago
Nice.
A couple of observations:
I'd suggest including FMDB.h
in the bridging header, instead of just those two headers. That way, with a single #import
, you get everything you'd commonly need:
#import "FMDB.h"
You describe the process of manually creating a bridging header (and, by the way, Apple advises a filename of Module-Bridging-Header.h
, where Module
is the product module name). If, rather than dragging the src/fmdb
folder into your project, you drag the individual .m
and .h
, Xcode will automatically prompt to create the appropriately named bridging header for you.
Hopefully this will be remedied before we get out of beta (that copying the folder won't create bridging header, but copying the individual files will), but I just find that currently dragging the source files into a project is more successful than dragging the folders, and it automates the creation of the shell of the bridging header.
By the way, I'd suggest (especially in a tutorial where you're trying to illustrate best practices), always checking your error codes and retrieving meaningful descriptions. For example, instead of
let copiedBackupDb = filemanager.copyItemAtPath(backupDbPath, toPath:dbFilePath, error: nil)
if (!copiedBackupDb) {
return false;
}
I'd suggest:
var error: NSError?
let copySuccessful = filemanager.copyItemAtPath(backupDbPath, toPath:dbFilePath, error: &error)
if !copySuccessful {
println("copy failed: \(error?.localizedDescription)")
return false
}
or:
var error: NSError?
if !filemanager.copyItemAtPath(backupDbPath, toPath:dbFilePath, error: &error) {
println("copy failed: \(error?.localizedDescription)")
return false
}
Likewise, where you might have something like:
db.executeUpdate(addQuery, withArgumentsInArray: nil)
I'd suggest:
let updateSuccessful = db.executeUpdate(addQuery, withArgumentsInArray: nil)
if !updateSuccessful {
println("update failed: \(db.lastErrorMessage())")
return false
}
Or:
if !db.executeUpdate(addQuery, withArgumentsInArray: nil) {
println("update failed: \(db.lastErrorMessage())")
return
}
It's just a common SQLite problem I see on Stack Overflow is a failure to check return codes and handle/detect failures properly, so if we're offering a tutorial, we might want to encourage best practices.
If you wanted to supplement your tutorial with a FMDatabaseQueue
example (and many of us are writing multithreaded code in which FMDatabaseQueue
is invaluable), here's an example:
var queue: FMDatabaseQueue?
func testDatabaseQueue() {
let documentsFolder = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0] as String
let databasePath = documentsFolder.stringByAppendingPathComponent("test.sqlite")
queue = FMDatabaseQueue(path: databasePath)
// create table
queue?.inDatabase() {
db in
if !db.executeUpdate("create table test (id integer primary key autoincrement, a text)", withArgumentsInArray:nil) {
println("table create failure: \(db.lastErrorMessage())")
return
}
}
// let's insert five rows
queue?.inTransaction() {
db, rollback in
for i in 0 ..< 5 {
if !db.executeUpdate("insert into test (a) values (?)", withArgumentsInArray: ["Row \(i)"]) {
println("insert \(i) failure: \(db.lastErrorMessage())")
rollback.initialize(true)
return
}
}
}
// let's try inserting rows, but deliberately fail half way and make sure it rolls back correctly
queue?.inTransaction() {
db, rollback in
for i in 5 ..< 10 {
if !db.executeUpdate("insert into test (a) values (?)", withArgumentsInArray: ["Row \(i)"]) {
println("insert \(i) failure: \(db.lastErrorMessage())")
rollback.initialize(true)
return
}
if (i == 7) {
rollback.initialize(true)
}
}
}
// let's prove that only the first five rows are there
queue?.inDatabase() {
db in
if let rs = db.executeQuery("select * from test", withArgumentsInArray:nil) {
while rs.next() {
println(rs.resultDictionary())
}
} else {
println("select failure: \(db.lastErrorMessage())")
}
}
// let's drop that table
queue?.inDatabase() {
db in
if !db.executeUpdate("drop table test", withArgumentsInArray:nil) {
println("table drop failure: \(db.lastErrorMessage())")
return
}
}
}
But this not only illustrates the use of FMDatabaseQueue
(and the non-intuitive way one sets the rollback
boolean), but also the use of a class property for the database/queue (because its not advisable to constantly open and close databases, but rather open it up once and use it repeatedly).
But, please, don't take my comments as a criticism. I think the fact that you've pulled together a demonstration is wonderful. I just wanted to offer a few constructive suggestions.
By the way, @ccgus, you'll notice that we're using the withArgumentsInArray
variation of executeUpdate
and executeQuery
. This is because Swift doesn't currently offer nice interoperability with C/Objective-C variadic parameters. This is a known Swift issue and hopefully they'll remedy it before it goes into production.
Swift is going through a startling number of functional enhancements from one beta version to the next, so I wouldn't be surprised if this is remedied before it gets out of beta. If not, we might want to offer a Swift extension that reproduces the elegance we previously enjoyed with the Objective-C variadic rendition of those two methods.
BTW, the extension might look like:
extension FMDatabase {
/// This is rendition of executeQuery that handles Swift variadic parameters
/// for the values to be bound to the ? placeholders in the SQL.
///
/// :param: sql The SQL statement to be used.
/// :param: values The values to be bound to the ? placeholders
///
/// :returns: This returns FMResultSet if successful. Returns nil upon error.
func executeQuery(sql:String, _ values: AnyObject...) -> FMResultSet? {
return executeQuery(sql, withArgumentsInArray: values as NSArray);
}
/// This is rendition of executeUpdate that handles Swift variadic parameters
/// for the values to be bound to the ? placeholders in the SQL.
///
/// :param: sql The SQL statement to be used.
/// :param: values The values to be bound to the ? placeholders
///
/// :returns: This returns true if successful. Returns false upon error.
func executeUpdate(sql:String, _ values: AnyObject...) -> Bool {
return executeUpdate(sql, withArgumentsInArray: values as NSArray);
}
}
(BTW, the above is simpler than my initial rendition. Credit to muqaddar.)
That allows you to do things like:
if !db.executeUpdate("insert into test (a, timestamp) values (?, ?)", "Row \(i)", NSDate()) {
println("insert \(i) failure: \(db.lastErrorMessage())")
rollback.initialize(true)
return
}
Or
if let rs = db.executeQuery("select a, timestamp from test where a = ?", "Row 2") {
while rs.next() {
let a = rs.objectForColumnName("a") as String
let timestamp = rs.dateForColumn("timestamp")
println("a = \(a); timestamp = \(timestamp)")
}
} else {
println("select failure: \(db.lastErrorMessage())")
}
Thank you very much Rob. I've integrated your suggestions in the tutorial.
Is it not possible to also submit this as a markdown file, so it can be read via the git repo?
I certainly do understand the value of having a link from here or the markdown file back to the original.
I'm using a PHP third-party library to format the source code. It has to be installed to work--so I'm unable to add it to the git repo.
vip
From: Chris notifications@github.com To: ccgus/fmdb fmdb@noreply.github.com Cc: bluelobe vip_m@yahoo.com Sent: Wednesday, August 13, 2014 4:57 PM Subject: Re: [fmdb] Created Tutorial how to use FMDB with Swift (#291)
Is it not possible to also submit this as a markdown file, so it can be read via the git repo? I certainly do understand the value of having a link from here or the markdown file back to the original. — Reply to this email directly or view it on GitHub.
Awesome- thanks for doing this! And yes, at some point after Swift is no longer beta, a nice swift friendly wrapper would be awesome to have.
Where can we see a Swift example of using FMDatabaseQueue (1) across multiple threads (NSOperation) and (2) closing the database connection at the appropriate time?
The above example of FMDatabaseQueue looks like a single-thread example using FMDatabaseQueue...
@user919 FYI, to use from multiple threads, it's just like it is in Objective-C: Just make sure you instantiate a single FMDatabaseQueue
, and then use that same, single FMDatabaseQueue
instance within all of your separate NSOperation
objects (which, if you're adding them to your own operation queues, will be invoked from various threads).
My only word of caution would be to make sure you constrain the degree of concurrency of the operation queue to which you add these operations (via maxConcurrentOperationCount
) so that foreground requests can't ever get buried behind a huge backlog of background requests. Remember that while you can use FMDatabaseQueue
from whatever thread you want, the FMDatabaseQueue
, itself, is a serial queue.
What about when/where/how to close the queue or database connection?
I'm concerned: (1) if I manually close the database connection in thread A, then thread B will crash and (2) if I dont close the database connection, then I'll run into other issues. When should I close? Where should I close? And what is the syntax?
You can close when the app terminates, but otherwise there's no need to close it and you generally just keep it open.
Thanks for the swift tutorial guys, really helpfull
Would love to see the tutorial -- the link seems to be broken.
I've fixed the link. Enjoy!
Speaking of swift, does anyone got a working example about query sanitization with multiple formats as values? seems that the executeUpdateWithFormat method does not exist in swift.
I'd suggest using executeUpdate(values:)
, e.g. :
let db = FMDatabase(path: fileURL.path)
db.open()
do {
try db.executeUpdate("create table test (name text, age integer, create_date date)", values: nil)
let now = NSDate()
try db.executeUpdate("insert into test (name, age, create_date) values (?, ?, ?)", values: ["Liam O'Flaherty", 88, now])
let rs = try db.executeQuery("select name, age, create_date from test", values: nil)
while rs.next() {
print(rs.stringForColumnIndex(0))
print(rs.intForColumnIndex(1))
print(rs.dateForColumnIndex(2))
}
} catch {
print(error)
}
What Swift type should we use for primary keys (INTEGER)? What method should we call to retrieve it from the result set? eg. resultSet.intForColumn("id"), etc...
Yes, using INTEGER PRIMARY KEY
is a very common practice.
When retrieving this, though, I personally use longForColumn
, which returns an Objective-C long
(i.e. 64-bit integer), which is equivalent to Objective-C NSInteger
, which is equivalent to Swift Int
. So, rather than intForColumn
(which returns Int32
), I use longForColumn
.
I do this because when you define a primary key that is a single integer column, it doubles for the ROWID
, which is explicitly defined to be 64-bit. See ROWIDs and the INTEGER PRIMARY KEY.
longForColumn works great Thanks.
Just wanted to say "thanks" to @robertmryan for those Swift examples of FMDatabaseQueue
. This is very helpful!
how to upgrade database table in swift? using db version?
In the AppDelegate, you can have a variable/constant like 'kCurrentVersion = 2' and compare it with what's in the database (you can have a table with just one field 'current_version' and if the variable is higher than the one in the database, then overwrite the database with the new one (usually the one in the Build directory replaces the one in the device's Documents directory).
hello my code is :
let path = NSBundle.mainBundle().pathForResource("A", ofType:"bin")
let database = FMDatabase(path: path)
if !database.open() {
print("Unable to open database")
return
}
database.executeStatements("SELECT * FROM item WHERE pid = '1'", withResultBlock: nil)
if let rs = database.executeQuery(" SELECT * FROM item WHERE pid = '1' ", withArgumentsInArray: nil) {
while rs.next() {
//...
}
} else {
print("executeQuery failed: \(database.lastErrorMessage())")
}
database.close()
// Do any additional setup after loading the view, typically from a nib.
}
and my console log is: DB Path: (null) executeQuery failed: no such table: item
can you help me?
The error is telling you it can't find the table item
. So either that database exists but it doesn't have a table called item
, or the database doesn't exist and it created a new one (which obviously didn't have that table).
Double check that the database, A.bin
exists, that the capitalization matches, that it was included in the bundle, and that it actually has a table called item
.
Hi,
This issue occur when I have realase new build update on exist build database not change because I have implement this condition:
if !fileManager.fileExists(atPath: databasePath) {
let documentsURL = Bundle.main.resourceURL
let fromPath = documentsURL?.appendingPathComponent(databaseName as String)
do {
try fileManager.copyItem(atPath: fromPath!.path, toPath: databasePath)
} catch {
print("File not saved")
}
}
how can override database from old Database to new database when I have to add one new column in new database?
in swift 3.0
@sazzad7002 I'm not sure if I have correctly understood your question, but there are two different paths here.
Option 1: Keep the Data
If you need to preserve the data in the database, and you need to add a new column, then you need to ALTER
the database columns as explained in this answer.
Option 2: Delete Data, Get new Database Structure If you don't care about the data in the database, then you can define your new schema is a database you store in your app's Resources folder. Then when your app starts, delete the old database and replace it with the new one (which contains the new column). Here is some code that shows how to do this:
//Check if database already exists
let documentsURL = NSFileManager.defaultManager().URLsForDirectory(.DocumentDirectory, inDomains: .UserDomainMask)[0]
let databaseURL = documentsURL.URLByAppendingPathComponent("YourDatabase.db")
if !NSFileManager.defaultManager().fileExistsAtPath(databaseURL!.path!){
//Copy the file from the Resources folder to the app folder
do{
let filePath = NSBundle.mainBundle().pathForResource("YourDatabase", ofType: "db")
try NSFileManager.defaultManager().copyItemAtPath(filePath!, toPath: databaseURL!.path!)
}catch{
print("Error copying database file!")
}
}
I hope that helps.
I got this error with flutter:
Xcode's output:
↳
=== BUILD TARGET Pods-Runner OF PROJECT Pods WITH CONFIGURATION Release ===
ld: '/Users/ies/Desktop/work/ntop/ivo_lite/build/ios/Release-iphoneos/FMDB/libFMDB.a(FMDatabasePool.o)' does not contain bitcode. You must rebuild it with bitcode enabled (Xcode setting ENABLE_BITCODE), obtain an updated
library from the vendor, or disable bitcode for this target. file '/Users/ies/Desktop/work/ntop/ivo_lite/build/ios/Release-iphoneos/FMDB/libFMDB.a' for architecture arm64
clang: error: linker command failed with exit code 1 (use -v to see invocation)
any help?
the same issue as @Allan-Nava
Since it took me some figuring out, thought I'd share what I've learned (avoiding issues with importing an Objective-C library) integrating FMDB/SQLite into a Swift-language project. It's a tutorial and it's available at http://metrozines.com/