iOS开发-进阶:FMDB使用(翻译)

A Cocoa / Objective-C wrapper around SQLite  //一个对SQLite基于Cocoa/Objective-C语言的包装

FMDB v2.5

This is an Objective-C wrapper around SQLite:  http://sqlite.org/  //这是一个基于Obejective-C包装的SQLite

The FMDB Mailing List: //关于FMDB的邮件列表

http://groups.google.com/group/fmdb  //ps:请翻墙打开~

Read the SQLite FAQ: //阅读SQLite的常见问题解答：

http://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:  http://www.sqlite.org/docs.html 

//由于FMDB是基于SQLite顶层的封装,所以你需要至少一次完整的阅读本页.当你阅读到这里,请保存这份SQLite文档: 

Contributing //投稿

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的使用,你是否有很赞的想法?也许你会考虑首先看ccgus(作者ID)来确保这个想法并没有被提出来.其实,向我们发送这方面的请求也是极好的,并确保你依然坚持遵守编码约定.然而,请耐心等待,如果你没有听到ccgus一周以上的任何东西，你可能要发纸条问这是怎么回事。

CocoaPods 

FMDB can be installed using  CocoaPods . //FMDB可以通过Cocoapods安装

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

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_CODEC flag.

//如果需要使用SQLCipher来使用FMDB,则必须使用FMDB/ SQLCipher subspec。该FMDB/ SQLCipher subspec声明SQLCipher作为依赖，允许FMDB与-DSQLITE_HAS_CODEC标志进行编译。

 ps:SQLCipher是一个在SQLite基础之上进行扩展的开源数据库,它主要是在SQLite的基础之上增加了数据加密功能,如果我们在项目中使用它来存储数据的话,就可以大大提高程序的安全性.SQLCipher支持很多种不同的平台.

FMDB Class Reference: //FMDB类参考

http://ccgus.github.io/fmdb/html/index.html

Automatic Reference Counting (ARC) or Manual Memory Management?//ARC或者MRC的情况?

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会识别出这种内存管理体系,并且自动匹配这种内存管理体系.

Usage //用法

There are three main classes in FMDB: //FMDB有三个主要的类

1. FMDatabase - Represents a single SQLite database. Used for executing SQL statements.
2. FMResultSet - Represents the results of executing a query on an FMDatabase.
3. 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.

// 1.FMDatabase - 表示一个单一的SQLite数据库.用于执行SQL语句.

// 2.FMResultSet - 表示在FMDatabase执行查询的结果.

// 3.FMDatabaseQueue - 如果你想在多个线程执行查询或更新,你要使用这个类.关于它的描述在"线程安全"部分的下面.

Database Creation //创建数据库

An FMDatabase is created with a path to a SQLite database file. This path can be one of these three:

// 一个FMDatabase通过一个SQLite数据库文件的路径创建.路径可以是下面的三者之一:

1. A file system path. The file does not have to exist on disk. If it does not exist, it is created for you.
2. An empty string (@""). An empty database is created at a temporary location. This database is deleted with the FMDatabase connection is closed.
3. NULL. An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

(For more information on temporary and in-memory databases, read the sqlite documentation on the subject: http://www.sqlite.org/inmemorydb.html)

FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];

// 1.一个文件的系统路径.这个文件不一定存在,如果不存在的话,它会自动创建.

// 2.一个空的字符串.一个空的数据库将会创建在一个临时的地址.这个数据库会在FMDatabase链接关闭的时候删除.

// 3.NULL.会创建一个存在于内存中的数据库.这个数据库会在FMDatabase链接关闭的时候删除.

//(关于2,3的情况,可以阅读此链接中的SQLite文档:http://www.sqlite.org/inmemorydb.html)

Opening //打开

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];
    return;
}

// 在你使用数据库前,必须打开它.如果没有足够的资源和权限来打开和/或创建数据库,就会打开失败.

Executing Updates //执行更新

Any sort of SQL statement which is not a SELECT statement qualifies as an update. This includes CREATE, UPDATE, INSERT, ALTER, COMMIT, BEGIN, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, and REPLACE statements (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 YES means the update was successfully executed, and a return value of NO means that some error was encountered. You may invoke the -lastErrorMessage and -lastErrorCodemethods to retrieve more information.

//SQL语句的任何排序,如果不是一个SELECT语句都可以证明是一个更新语句.包括 CREAT,UPDATA,INSERT,ALTER,COMMIT,BEGIN,DETACH,DELETE,DROP,END,EXPLAIN,VACUUN,和REPLACE语句(以及其他更多的关键词).基本上如果你的SQL语句不是以SELECT开头,它便是一个更新的语句.

//执行更新返回一个值，BOOL。YES返回值表示已成功执行了更新,NO的返回值意味着出现了某种错误。你可以调用-lastErrorMessage和-lastErrorCode方法来获取更多的信息。

Executing Queries //执行查询

A SELECT statement is a query and is executed via one of the -executeQuery... methods.

//SELECT语句是一个查询语句并通过执行一个 -executeQuery 方法.

Executing queries returns an FMResultSet object if successful, and nil upon failure. You should use the -lastErrorMessage and -lastErrorCode methods to determine why a query failed.

//执行查询如果成功会返回一个FMResultSet对象, 失败返回nil.您应该使用-lastErrorMessage和-lastErrorCode方法.以确定为什么这个查询失败.

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:

//为了遍历查询的结果,您可以使用while()循环.您还需要"一步到位",从一个记录到另一个.使用FMDB,做到这一点的最简单的方法是这样的：

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 next],即使你只想要一个结果:

FMResultSet *s = [db executeQuery:@"SELECT COUNT(*) FROM myTable"];
if ([s next]) {
    int totalCount = [s intForColumnIndex:0];
}

FMResultSet has many methods to retrieve data in an appropriate format:

//FMResultSet有许多方法来以适当的格式检索数据:

• intForColumn:
• longForColumn:
• longLongIntForColumn:
• boolForColumn:
• doubleForColumn:
• stringForColumn:
• dateForColumn:
• dataForColumn:
• dataNoCopyForColumn:
• UTF8StringForColumnName:
• objectForColumnName:

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.

//每一种方法也有一个{type} ForColumnIndex：变量用于检索基于在结果列的位置上的数据,而不是列的名称.

Typically, there's no need to -close an FMResultSet yourself, since that happens when either the result set is deallocated, or the parent database is closed.

//通常情况下,不需要你自己执行-close FMResultSet,因为这种情况发生时,无论是结果集被释放,或父数据库,都会关闭。

Closing //关闭数据库

When you have finished executing queries and updates on the database, you should -close the FMDatabase connection so that SQLite will relinquish any resources it has acquired during the course of its operation.

//当你执行完毕数据库的查询或者更新操作后,你应该 -close FMDatabase链接以便SQLite放弃在它运作过程中获得的任何资源.

[db close];

  Transactions //事务处理

FMDatabase can begin and commit a transaction by invoking one of the appropriate methods or executing a begin/end transaction statement.

//FMDatabase 可以开始或提交一个事务处理通过调用一个相应的方法或执行一个开始/结束 事务处理的语句.

Multiple Statements and Batch Stuff //多个语句与批量处理

You can use FMDatabase's executeStatements:withResultBlock: to do multiple statements in a string:

//能够使用FMDatabase 执行语句 : withResultBlock: 用字符串执行多个语句的处理:

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;
}];

Data Sanitization //数据清除

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:

//在FMDB中执行一个SQL语句时,在插入语句之前,你不应该试图"清除"任何值.相反,你应使用标准的SQLite的绑定语法:

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.

// ? 字符被理解为SQLite中当一个值插入时的占位符.它的执行方法均接受一个可变数量的参数(或者表示这些参数, 如NSArray,NSDictionary,或者 va_list).都能够正确的转义.

Alternatively, you may use named parameters syntax:

//另外,你也可以使用命名参数语法:

INSERT INTO myTable VALUES (:id, :name, :value)

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.

//参数必须以冒号开始, SQLite的本身支持其它字符,但是,在字典内部的key有一个前缀的":"号,在你的字典的key中,不要包括参数中的":"号.

NSDictionary *argsDict = [NSDictionary dictionaryWithObjectsAndKeys:@"My Name", @"name", nil];
[db executeUpdate:@"INSERT INTO myTable (name) VALUES (:name)" withParameterDictionary:argsDict];

Thus, you SHOULD NOT do this (or anything like this)://因此,你不应该这样做(或任何像这样的操作):

[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" lots of ' bizarre \" quotes '"]];

Instead, you SHOULD do: //相反,你应该这样做:

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" lots of ' bizarre \" quotes '"];

All arguments provided to the -executeUpdate: method (or any of the variants that accept a va_list as a parameter) must be objects. The following will not work (and will result in a crash):

//提供给-executeUpdate: 的所有参数, 方法(或任何的变量接受va_list作为参数)必须是一个对象.下面的示例将不起作用(或导致程序崩溃):

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42];

The proper way to insert a number is to box it in an NSNumber object:

//正确的插入数字的方式,是将number包装秤一个NSNumber对象:

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]];

Alternatively, you can use the -execute*WithFormat: variant to use NSString-style substitution:

//另外,你可以使用 -execute*WithFormat:  变量使用NSString-style替代之:

[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)", 42];

Internally, the -execute*WithFormat: methods are properly boxing things for you. The following percent modifiers are recognized:  %@, %c, %s, %d, %D, %i, %u, %U, %hi, %hu, %qi, %qu, %f, %g, %ld, %lu, %lld, and %llu. Using a modifier other than those will have unpredictable results. If, for some reason, you need the % character to appear in your SQL statement, you should use %%.

//在内部, -execute*WithFormat: 方法可以用来正确的为你封装东西.下述的%是可以使用的:  %@, %c, %s, %d, %D, %i, %u, %U, %hi, %hu, %qi, %qu, %f, %g, %ld, %lu, %lld, 和 %llu. 其他情况下使用%可能会产生不可预期的结果. 如果由于某种原因你需要使用％字符出现在你的SQL语句,你应该使用%%.

Using FMDatabaseQueue and Thread Safety. //使用FMDatabaseQueue和线程安全.

Using a single instance of FMDatabase from multiple threads at once is a bad idea. It has always been OK to make a FMDatabase object 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.

//使用一个单一的FMDatabase实例到多线程当中是一个糟糕的思路.因为需要确保每一个线程中的FMDatabase对象都是OK的. 对此,就不要在多个线程间共享一个FMDatabase实例, 同时定义这个FMDatabase不要跨越多个线程. 不好的事情最终会发生,你最终得到崩溃的东西,或者一个异常, 或者陨石会从天上掉下来的,打你的Mac Pro.(哈哈我笑了~)  This would suck (这很烂,是一堆shit).

So don't instantiate a single FMDatabase object and use it across multiple threads.

//所以不要在多线程中建立一个单一的FMDatabase对象.

Instead, use FMDatabaseQueue. It's your friend and it's here to help. Here's how to use it:

//替代方法是,使用FMDatabaseQueue. 它是你的朋友,并会在这里帮助你.下面是如何使用它:

First, make your queue.

//首先,开始你的线程.

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];

Then use it like so:

//然后这样使用:

[queue inDatabase:^(FMDatabase *db) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt: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 (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    if (whoopsSomethingWrongHappened) {
        *rollback = YES;
        return;
    }
    // etc…
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:4]];
}];

FMDatabaseQueue will 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.

//FMDatabaseQueue会运行blocks在一个序列化的队列(就是这个类的名字).所以如果你在多线程中同时调用FMDatabaseQueue`s方法,他们会按照收到的先后顺序执行.这样查询和更新不会蹩脚,大家就都开心了.

Note: The calls to FMDatabaseQueue's methods are blocking. So even though you are passing along blocks, they will notbe run on another thread.

//注意:调用FMDatabaseQueue 的方法是blocking (块儿代码). 所以即使你使用blocks传递东西,他们也不会运行在其他的线程.

Making custom sqlite functions, based on blocks. //基于blocks使用自定义的SQLite功能

You can do this! For an example, look for "makeFunctionNamed:" in main.m

//你可以的!例如,查看"makeFunctionNamed:" 在 main.m

Swift 

You can use FMDB in Swift projects too. //你可以在Swift项目中使用FMDB

To do this, you must:

//使用FMDB,你必须:

1. Copy the relevant .m and .h files from the FMDB src folder into your project.

   You can copy all of them (which is easiest), or only the ones you need. Likely you will need FMDatabase and FMResultSet at a minimum. FMDatabaseAdditions provides some very useful convenience methods, so you will likely want that, too. If you are doing multithreaded access to a database, FMDatabaseQueue is quite useful, too. If you choose to not copy all of the files from the src directory, though, you may want to update FMDB.h to only reference the files that you included in your project.

   Note, if you're copying all of the files from the src folder 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).

   //1.从FMDB src文件中拷贝.m和.h文件到你的项目中.

   你可以拷贝全部的文件(最简单的方法), 或者只拷贝你需要的.至少要拷贝FMDatabase和FMResultSet文件.FMDatabaseAdditions提供一些非常方便的方法,所以你也会需要它.如果你需要在多线程中使用数据库,FMDatabaseQueue非常有用. 

   #warm mark - 暂时翻译到这里吧...

2. If prompted to create a "bridging header", you should do so. If not prompted and if you don't already have a bridging header, add one.

   For more information on bridging headers, see Swift and Objective-C in the Same Project.

3. In your bridging header, add a line that says:

   #import "FMDB.h"

4. Use the variations of executeQuery and executeUpdate with the sql and values parameters with try pattern, as shown below. These renditions of executeQuery and executeUpdate both throw errors in true Swift 2 fashion.

If you do the above, you can then write Swift code that uses FMDatabase. For example:

let documents = try! NSFileManager.defaultManager().URLForDirectory(.DocumentDirectory, inDomain: .UserDomainMask, appropriateForURL: nil, create: false)
let fileURL = documents.URLByAppendingPathComponent("test.sqlite")

let database = FMDatabase(path: fileURL.path)

if !database.open() {
    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() {
        let x = rs.stringForColumn("x")
        let y = rs.stringForColumn("y")
        let z = rs.stringForColumn("z")
        print("x = \(x); y = \(y); z = \(z)")
    }
} catch let error as NSError {
    print("failed: \(error.localizedDescription)")
}

database.close()

History

The history and changes are availbe on its GitHub page and are summarized in the "CHANGES_AND_TODO_LIST.txt" file.

Contributors

The contributors to FMDB are contained in the "Contributors.txt" file.

Additional projects using FMDB, which might be interesting to the discerning developer.

• FMDBMigrationManager, A SQLite schema migration management system for FMDB: https://github.com/layerhq/FMDBMigrationManager
• FCModel, An alternative to Core Data for people who like having direct SQL access: https://github.com/marcoarment/FCModel

Quick notes on FMDB's coding style

Spaces, not tabs. Square brackets, not dot notation. Look at what FMDB already does with curly brackets and such, and stick to that style.

Reporting bugs

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:

• Open up fmdb project in Xcode.
• Open up main.m and modify the FMDBReportABugFunction to reproduce your bug.
  • Setup your table(s) in the code.
  • Make your query or update(s).
  • Add some assertions which demonstrate the bug.

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.

Support

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.

License

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.)