[翻译] FMDB

FMDB

https://github.com/ccgus/fmdb

This is an Objective-C wrapper around SQLite: http://sqlite.org/

这是对 SQLite 的封装：http://sqlite.org/

Read the SQLite FAQ:

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 文档看一遍。看的时候，将 http://www.sqlite.org/docs.html 网址添加到你的浏览器书签中吧。

FMDB Class Reference:

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

Automatic Reference Counting (ARC) or Manual Memory Management?

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 支持 ARC 与 非ARC 。

Usage

There are three main classes in FMDB:

FMDB 包含了 3 个主要的类：

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.
4. FMDatabase - 代表了单一的一个 SQLite 数据库。用来执行 SQL 语句的。
5. FMResultSet - 在 FMDatabase 上执行了查询操作后展示的结果。
6. FMDatabaseQueue - 如果你想在不同的线程中同时执行查询和更新操作，你会用到这个类。在最下面有对其的描述。

Database Creation

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

通过给定一个 SQLite 数据库文件的路径来创建 FMDatabase，这个文件路径可由下面 3 种方式提供：

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 FMDatabaseconnection is closed.
4. 一个系统文件路径，这个文件路径不一定需要存在，如果不存在，它会自动为你创建。
5. 一个空的字符串 @“”，它会为你创建一个临时的数据库文件。当 FMDatabase 关闭后，这个数据库就会被删除掉。
6. 如果参数为 NULL ，那就会在内存中创建一个数据库，当 FMDatabase 关闭后，这个数据库就会被删除掉。

(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"];

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 REPLACEstatements (plus many more). Basically, if your SQL statement does not begin with SELECT, it is an update statement.

任何 SQL 语句，如果不是 SELECT 语句都会被看做是一个 updata 操作。包含 CREATE, UPDATE, INSERT, ALTER, COMMIT, BEGIN, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, 以及 REPLACE。所以请注意，如果你的 SQL 语句不是以 SELECT 开始的，那是一个更新操作哦。

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 -lastErrorCode methods to retrieve more information.

执行更新操作会返回一个 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. Like executing updates, there is a variant that accepts an NSError ** parameter. Otherwise you should use the -lastErrorMessageand -lastErrorCode methods to determine why a query failed.

查询成功会返回一个 FMResultSet 对象，失败返回 nil。与执行更新操作一样，有一个变量来接收一个 NSError 参数。当然，你也可以使用 -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（） 循环。你需要使用 “step” 来从一条记录跳到另外一条记录。在 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.

尤其注意，你不要自己关闭 FMResultSet 。当父数据库关闭，或者是结果集被丢弃，它会自动关闭。

Closing（关闭）

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

当你已经执行了查询，更新了数据库，你应该使用 -close 来关闭数据库的连接，让 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 可以提交一个事务，通过调用 begin/end 事务语句。

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:

当你提供一条 SQL 语句给 FMDB 时，你不要直接给语句中的变量附上值。你应该使用标准的 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.

？会被识别成一个将要被替换的值。所有的的执行方法都接受不同类型的变量（包括 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 本身支持很多种字符，但是 OC 中字典对象的前缀就是冒号，所以，不要在你的字典 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：方法的参数必须是一个对象。下面的写法是错误的，会导致崩溃。

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

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

插入一个数字类型的，需要使用 NSNumber 对象：

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

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

作为一项选择，你可以使用 -exexute*WithFormat：来提交 NSString 方式的语句：

[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 %%.

本质上，-excute*WithFormat：方法也是一个？的方式。下面的百分号的方式是能够被识别的： %@, %c, %s, %d, %D, %i, %u, %U, %hi, %hu, %qi, %qu, %f, %g, %ld, %lu, %lld, 和 %llu。你使用了其他修饰语而不是这些会导致预料不到的结果。由于某些原因，你需要使用 % 出现在你的 SQL 语句中，你应该使用 %%。

Using FMDatabaseQueue and Thread Safety.（使用数据库队列以及线程安全）

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 对象是安全的。请不要创建数据库单例，并在不同的线程中访问，如果实在不行，也不要让不同的线程同时访问这一个数据库对象。如果你坚持这么做，那经常就会出现一些莫名其妙的崩溃，异常，或者从天而降的陨石砸坏你的苹果电脑，别这么干哦。

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.

首先，创建你的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 运行在一个串行队列当中。所以，当你在不同的线程中调用了 FMDatabaseQueue 方法，他们将会被序列执行。这种处理方式，不同线程间不会互相干扰，每个线程都很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.

注意：调用 FMDatabaseQueue 方法是一个 block 。即使你在 block 中使用了 block，它也不会在其它线程中运行。

Making custom sqlite functions, based on blocks.（定制 sqlite 函数，基于 blocks）

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

你可以定制哦！你在 main.m 文件中看看吧。