开发者社区> ghost丶桃子> 正文

iOS 4 iPhone Database Implementation using SQLite [转]

简介:
+关注继续查看

What is SQLite?

SQLite is an embedded, relational database management system (RDBMS). Most relational databases (Oracle and MySQL being prime examples) are standalone server processes that run independently, and in cooperation with, applications that require database access. SQLite is referred to as embedded because it is provided in the form of a library that is linked into applications. As such, there is no standalone database server running in the background. All database operations are handled internally within the application through calls to functions contained in the SQLite library.

The developers of SQLite have placed the technology into the public domain with the result that it is now a widely deployed database solution.

SQLite is written in the C programming language and therefore using SQLite on the iPhone involves direct calls to C functions and access to C data structures. In order to bridge the differences between Objective-C and the C based SQLite library it will be necessary, for example, to convert any NSString objects to UTF8 format before passing them through as arguments to these functions.

For additional information about SQLite refer to http://www.sqlite.org.

Structured Query Language (SQL)

Data is accessed in SQLite databases using a high level language known as Structured Query Language. This usually abbreviated to SQL and typical pronounced sequel. SQL is a standard language used by most relational database management systems. SQLite conforms mostly to the SQL-92 standard.

Whilst use of some basic SQL statements will be used within this chapter, a detailed overview of SQL is beyond the scope of this book. There are, however, many other resources that provide a far better overview of SQL that we could ever hope to provide in a single chapter here.

Trying SQLite on MacOS X

For readers unfamiliar with databases in general and SQLite in particular, diving right into creating an iPhone application that uses SQLite can be a little intimidating. Fortunately, MacOS X is shipped with SQLite pre-installed, including an interactive environment for issuing SQL commands from within a Terminal window. This is both a useful way to learn about SQLite and SQL, and also an invaluable tool for identifying problems with databases created by applications in the iOS simulator.

To launch an interactive SQLite session, open a Terminal window on your Mac OS X system, change directory to a suitable location and run the following command:

sqlite3 ./mydatbase.db


SQLite version 3.6.12

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

At the sqlite> prompt, commands may be entered to perform tasks such as creating tables and inserting and retrieving data. For example, to create a new table in our database with fields to hold ID, name, address and phone number fields the following statement is required:

create table contacts (id integer primary key autoincrement, name text, address text, phone text);

Note that each row in a table must have a primary key that is unique to that row. In the above example we have designated the ID field as the primary key, declared it as being of type integer and asked SQLite to automatically increment the number each time a row is added. This is a common way to make sure that each row has a unique primary key. The remaining fields are each declared as being of type text.

To list the tables in the currently selected database use the .tables statement:

sqlite> .tables

contacts

To insert records into the table:

sqlite> insert into contacts (name, address, phone) values ("Bill Smith", "123 main Street, California", "123-555-2323");

sqlite> insert into contacts (name, address, phone) values ("Mike Parks", "10 Upping Street, Idaho", "444-444-1212");

To retrieve all rows from a table:

sqlite> select * from contacts;

1|Bill Smith|123 main Street, California|123-555-2323

2|Mike Parks|10 Upping Street, Idaho|444-444-1212

To extract a row that meets specific criteria:

sqlite> select * from contacts where name="Mike Parks";

2|Mike Parks|10 Upping Street, Idaho|444-444-1212

To exit from the sqlite3 interactive environment:

sqlite> .exit

When running an iPhone application in the iOS Simulator environment, any database files will be created on the file system of the computer on which the simulator is running. This has the advantage that you can navigate to the location of the database file, load it into the sqlite3 interactive tool and perform tasks on the data to identify possible problems occurring in the application code. If, for example, an application creates a database file named contacts.db in its documents directory, the file will be located on the host system in the following folder:

/Users/<user>/Library/Application Support/iPhone Simulator/<sdk version>/Applications/<id>/Documents

Where <user> is the login name of the user running the iOS Simulator session, <sdk version> is the version of the iOS SDK used to build the application and <id> is the unique ID of the application.

Preparing an iOS 4 Application Project for SQLite Integration

By default, the Xcode environment does not assume that you will be including SQLite in your application. When developing SQLite based applications a few additional steps are required to ensure the code will compile when the application is built. Firstly, the project needs to be configured to include the libsqlite3.dylib dynamic library during the link phase of the build process. To achieve this, hold down the Ctrl key and click with the mouse on the Frameworks entry of the Groups and Files panel located on the left hand side of the Xcode project window. From the resulting menu, select the Add -> Existing Frameworks…option. From the list of framework options, scroll down to the libsqlite3.dylib entry, select it and click on the Add button.

Secondly, the sqlite3.h include file must be imported into any files where references are made to SQLite definitions, declarations or functions. This file in located in the /usr/include directory and may be imported when needed as follows:

#import "/usr/include/sqlite3.h"

Key SQLite Functions

<a href="http://ad.yieldmanager.com/imageclick?Z=300x250&s=256571&t=2" target="parent"><img border="0" src="http://ad.yieldmanager.com/imp?Z=300x250&s=256571&t=2"></img></a>


<a href="http://www.burstnet.com/ads/ad12897b-map.cgi/ns/v=2.3S/sz=300x250A/" target="_top"> <img src="http://www.burstnet.com/cgi-bin/ads/ad12897b.cgi/ns/v=2.3S/sz=300x250A/" border="0" alt="Click Here" /></a>

When implementing a database using SQLite it will be necessary to utilize a number of C functions contained within the libsqlite3.dylib library. A summary of the most commonly used functions is as follows:

  • sqlite3_open() - Opens specified database file. If the database file does not already exist, it is created.
  • sqlite3_close() - Closes a previously opened database file.
  • sqlite3_prepare_v2() - Prepares a SQL statement ready for execution.
  • sqlite3_step() - Executes a SQL statement previously prepared by the sqlite3_prepare_v2() function.
  • sqlite3_column_<type>() - Returns a data field from the results of a SQL retrieval operation where <type> is replaced by the data type of the data to be extracted (text, blob, bytes, int, int16 etc).
  • sqlite3_finalize() - Deletes a previously prepared SQL statement from memory.
  • sqlite3_exec() - Combines the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call.

This, of course, represents only a small subset of the complete range of functions available with SQLite. A full list can be found athttp://www.sqlite.org/c3ref/funclist.html.

Declaring a SQLite Database

Before any tasks can be performed on database, it must first be declared. To do so it is necessary to declare a variable that points to an instance of a structure of type sqlite3 (the sqlite3 structure is defined in the sqlite3.h include file). For example:

sqlite3 *contactDB; //Declare a pointer to sqlite database structure

Opening or Creating a Database

Once declared, a database file may be opened using the sqlite3_open() function. If the specified database file does not already exist it is first created before being opened. The syntax for this function is as follows:

int sqlite3_open(const char *filename, sqlite3 **database);

In the above syntax, filename is the path to the database file in the form of a UTF-8 character string and database is the reference to the database structure. The result of the operation is returned as an int. Various definitions for result values are defined in the include file such as SQLITE_OK for a successful operation.

For example, the code to open a database file named contacts.db in the Documents directory of an iPhone application might read as follows. Note that the code assumes an NSString variable named databasePath contains the path to the database file:

sqlite3 *contactDB; //Declare a pointer to sqlite database structure


const char *dbpath = [databasePath UTF8String]; // Convert NSString to UTF-8

        

if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)

{

//Database opened successfully

} else {

//Failed to open database

}

The key point to note in the above example is that the string contained in the NSString object was converted to a UTF-8 string before being passed to the function to open the database. This is a common activity that you will see performed frequently when working with SQLite in Objective-C.

Preparing and Executing a SQL Statement

SQL statements are prepared and stored in a structure of type sqlite3_stmt using the sqlite3_prepare_v2() function. For example:

sqlite3_stmt *statement;


NSString *querySQL = @"SELECT address, phone FROM contacts”;


const char *query_stmt = [querySQL UTF8String];


if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)

{

//Statement prepared successfully

} else {

//Statement preparation failed

}

A prepared SQL statement may subsequently be executed using a call to the sqlite3_step() function, passing through the sqlite3_stmt variable as the sole argument:

sqlite3_step(statement);

sqlite3_finalize(statement);

Note that the sqlite3_step() function also returns a result value. The value returned, however, will depend on the nature of the statement being executed. For example, a successful insertion of data into a database table will return a SQLITE_OK result, whilst the successful retrieval of data will return SQLITE_ROW.

Alternatively, the same results may be achieved with a single call to the sqlite3_exec() function as illustrated in the following section.

Creating a Database Table

Database data is organized into tables. Before data can be stored into a database, therefore, a table must first be created. This is achieved using the SQL CREATE TABLE statement. The following code example illustrates the creation of a table named contacts, the preparation and execution of which is performed using the sqlite3_exec() function:

const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";


if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) == SQLITE_OK)

{

          // SQL statement execution succeeded

}

Extracting Data from a Database Table

Those familiar with SQL will be aware that data is retrieved from databases using the SELECT statement. Depending on the criteria defined in the statement, it is typical for more than one data row to be returned. It is important, therefore, to learn how to retrieve data from a database using the SQLite C function calls.

As with previous examples, the SQL statement must first be prepared. In the following code excerpt, a SQL statement to extract the address and phone fields from all the rows of a database table named contacts is prepared:

sqlite3_stmt    *statement;


NSString *querySQL = @"SELECT address, phone FROM contacts”;


const char *query_stmt = [querySQL UTF8String];


sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL);

The statement subsequently needs to be executed. If a row of data matching the selection criteria is found in the database table, the sqlite3_step() function returns a SQLITE_ROW result. The data for the matching row is stored in the sqlite3_stmt structure and may be extracted using the sqlite3_column_<type>() function call, where <type> is replaced by the type of data being extracted from the corresponding field of data. Through the implementation of a while loop, the sqlite3_step() function can be called repeatedly to cycle through multiple matching rows of data until all the matches have been extracted. Note that the sqlite_column_<type>() function takes as its second argument the number of the column to be extracted. In the case of our example, column 0 contains the address and column 1 the phone number:

while (sqlite3_step(statement) == SQLITE_ROW)

{

       NSString *addressField = [[NSString alloc] initWithUTF8String:

                (const char *) sqlite3_column_text(statement, 0)];


       NSString *phoneField = [[NSString alloc] initWithUTF8String:

                (const char *) sqlite3_column_text(statement, 1)];


// Code to do something with extracted data here


[phoneField release];

[addressField release];

}

sqlite3_finalize(statement);

Closing a SQLite Database

When an application has finished working on a database it is important that the database be closed. This is achieved with a call to the sqlite3_close() function, passing through a pointer to the database to be closed: sqlite3_close(contactDB);

Summary

In this chapter we have looked at the basics of implementing a database within an iPhone application using the embedded SQLite relational database management system. In the next chapter we will put this theory into practice and work through an example that creates a functional iPhone application that is designed to store data in a database.

欢迎加群互相学习,共同进步。QQ群:iOS: 58099570 | Android: 330987132 | Go:217696290 | Python:336880185 | 做人要厚道,转载请注明出处!

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
23597 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
31880 0
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
10655 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
11150 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
13780 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
19778 0
1955
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载