sqlite[翻译] FMDB

sqlite 1

sqlite 2

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

其余 SQL
语句,如若不是 SELECT 语句都谋面叫看做是一个 updata
操作。
包含 CREATEUPDATEINSERTALTERCOMMITBEGINDETACHDELETEDROPENDEXPLAINVACUUM,
以及 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(执行查询)

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
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 NSArrayNSDictionary, or a va_list), which are properly
escaped for you.

?会被识别成一个即将被替换的价。所有的之举办措施还接受不同体系的变量(包括
NSArrayNSDictionary 或者一个 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
文件中看看吧。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

网站地图xml地图