SQLite C Language Interface Specification (i)--ready and Open Your SQLite

Why do you want to engage in the C-language interface specification of SQLite? Because you will inevitably encounter the operation of the database in iOS development, you can use third-party fmdb, etc., or use CoreData. But we still need to figure out how to use SQLite's C interface to manipulate the SQLite database. Starting from today, we will give you a detailed example of the C language interface of SQLite. For CoreData, please see the previous blog "iOS development Table view in love with CoreData".

If the English good small partner, you can not listen to my wordy, direct official website to walk:http://www.sqlite.org above things are everything, You can download resources such as SQLite's shell, which also has a lot of learning resources. But the premise is that English can not be too low. I have seen several books developed by iOS, including the "Proficient iOS Development" published by XXX Publishers, although the online evaluation is good, but always do not feel when reading books. Most books on the introduction of SQLite, speak too shallow, just list the code, interface parameters is what meaning, why do not say so. Read a book look uncomfortable, on the official web to find comfort it, sure enough, before the light. Just write a few blogs and make a good summary.

First, prepare the SQLite test project and the required tools

1. Prepare an iOS singleton project that has been introduced to the dynamic link library libsqlite3.0.dylib (of course, you can also create a console project, which is not the point to look at your mood).

2. Prepare a SQLite visual management tool, I use Sqlitemanager, of course, you can choose to use the handy management tool (self-Baidu bar). Of course, if you are a beginner, and want to "self-harm" a word, you can download from the official website called Sqlite-shell, with pure command line to manage your SQLite database. In fact, if used to, with pure command or with a relatively cool, after all, can be used to install 13 is not it! SQLite official online has detailed shell Operation command: How to create a database, how to create a series of operations such as tables, do not repeat today. (If you've ever done MySQL, Oracle, etc., you should be familiar with the command-line Operations database.)

3. You can create a database with Sqlitemanager to insert some test data for use in our test project. Or you can be lazy, directly from the online download a ready-made SQLite database to operate using (I downloaded a file called Cars.sqlite to test, the database table structure and data as shown below).

Second, open your database

1. Bring the prepared test SQLite database into our test project.

2. Load our database resources via NSBundle

    // get the path    to the Sqllite file NSString *sqlpath = [[NSBundle mainbundle] Pathforresource:@ "Cars" ofType:@ "  sqlite"];

3. Because it is a C language interface, the strings used in the arguments are strings in the C language, so it is necessary to convert the string to a string in the C language (that is, a pointer to the char type in C)

    // Turn the path into a C string    Const Char * filePath = [SQLPath utf8string];

4. You need to define a pointer variable for the SQLITE3 struct type, open the database to get the value of the Sqlite3 struct pointer and assign it to the corresponding pointer variable before you can manipulate the database through the sqlite3 struct pointer variable. The following defines a pointer variable for the SQLITE3 struct type, then passes the address of the pointer variable to the Sqlite3_open () function, the reference to the function argument, and the database operation pointer in the C language. For ease of understanding, sqlite3 structures can be treated as a class, while pointers to sqlite3 structures can be seen as objects of classes.

    Sqlite3 * database;         // Open Database    int result = Sqlite3_open (FilePath, &database);

　

The above steps can be obtained to the operation of the database structure pointer, Sqlite3_open () function, the first parameter is the C string format of the database file path, the second parameter is the address of the struct pointer, used to get the handle of the Operation database. The function has a return value of type int (0-101), which corresponds to a different link state. 0 stands for Success, others see:

    if (Result = = Sqlite_ok) {        NSLog (@ " connection succeeded ")    ; Else {        *error = [NSString stringWithFormat:@ " error result code:%d", result];        NSLog (@ "%@", error);    }

　　　　

Sqlite3_open () is a constructor, in addition to Sqlite3_open16 () and Sqlite3_open_v2 (), whose function is to open a new database connection, the required parameters are as follows. These constructors can connect to a database through the database file name parameter. If the filename parameter is in UTF-8 encoded format, you can call Sqlite3_open () and SQLITE3_OPEN_V2 (), then call the constructor Sqlite3_open16 () if the file parameter is UTF-16 encoded. The second parameter is the pointer address of the database operation handle that is returned.

The figure below shows that SQLITE3_OPEN_V2 () has two more parameters than Sqlite3_open (), an int flags, and a const char *ZVFS. the use of SQLITE3_OPEN_V2 () is similar to Sqlite3_open (), which can be said to be an enhanced version of the latter. Sqlite3_open () is the previous old method, and Sqlite3_open_v2 () is the method that was later improved.

Parameter flag, different values represent different operations that can be obtained after opening the database, similar to the operation permissions of the database, and below are the operation permissions represented by the value of flag.

　　　　The sqlite_open_readonly database is open in read-only mode. If the database does not exist, an error is returned.

　　　　The sqlite_open_readwrite database is opened in read-write mode and is read-only if the file is set to protected mode by the operating system. In both cases the database must already exist, or an error will be returned.

　　　　Sqlite_open_readwrite | The sqlite_open_create database is opened in read-write mode, and if the database does not exist, create one. This behavior is the default when you use Sqlite3_open () and Sqlite3_open16 () to connect to a database.

If the third parameter of SQLITE3_OPEN_V2 () does not contain one of the three combinations above, then the database connection permission is undefined. In other words, the database does not know whether to read or write, or to create, so the operation of the database is meaningless, so the above must choose a participation "and" operation.

　　　　Sqlite_open_nomutex The database is connected in multithreaded mode as long as the compile start time is not set in single-threaded mode.

　　　　Sqlite_open_fullmutex in the serialized threading pattern (in this mode, SQLITE can safely use in multiple threads without constraints) to open the database connection unless the start time is selected at compile time or before a single thread.

　　　　Sqlite_open_sharedcache can make the database connection appropriate to use shared cache mode, regardless of whether Sqlite3_enable_shared_cache () is used to enable shared caching.

　　　　sqlite_open_privatecache causes the database connection not to use shared cache mode, even if the shared cache model is available.

SQLITE3_OPEN_V2 () The fourth parameter is the name of the Sqlite3_vfs object, which defines the operating system interface that should use the new database connection. If the fourth parameter is a nil, then the default Sqlite3_vfs object is used. Below are the specific contents of the structure SQLITE3_VFS:

An instance of the Vfs:sqlite3_vfs object defines an interface between the SQLite core and the underlying operating system. The name of the "Vfs" object represents "virtual file system". A detailed description of VFS is here:https://www.sqlite.org/vfs.html Interested partners can do a good bit. If you have time in the future, please introduce the VFS well. Do not do too much to repeat today. The fourth parameter passed into nil will use the default Sqlite3_vfs default object.

Something about the VFS and SQLITE3_VFS structure, if there is time, take it out alone. It is still necessary to understand the structure and pattern of the VFS. ok~ today open and connect to the database, about how to go through the interface to operate the database will be left in the future blog to introduce it.

Database used and SQLITEAPI code GitHub share address:Https://github.com/lizelu/SQLiteResource

At the end of the blog, give a simple encapsulation of how to open a database:

1 /*******************************2 * Function: Open database3 * Parameter: databaseName--database name4 * Return: Database object (Sqlite3 object)5  *******************************/6+ (Sqlite3 *) Opendatabasewithname: (NSString *) databasename{7     8     //get the path to the Sqllite file9NSString *sqlpath = [[NSBundle mainbundle] pathforresource:databasename ofType:@"SQLite"];Ten      One     //Turn the path into a C string A     Const Char* FilePath =[SQLPath utf8string]; -      -Sqlite3 *database; the      -     //Open Database -     intresult = Sqlite3_open (FilePath, &database); -      +     if(Result = =SQLITE_OK) { -         returndatabase; +     } A      at     returnNil; -  -}

SQLite C Language Interface Specification (i)--ready and Open Your SQLite