SQLite Tutorial in IOS _ios

SQLite, is a lightweight database, is to comply with the ACID relational database management system, its design goal is embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, simultaneously can with many program language combination, for instance TCL, C #, PHP, Java and so on, as well as ODBC interface, also compared with MySQL, PostgreSQL the two open source world's most famous database management system, it handles faster than them. The first alpha version of SQLite was born in May 2000. So far there have been 14 years, SQLite also ushered in a version of SQLite 3 has been released.

Characteristics of SQLite

1. Acid Services

ACID refers to the four characteristics that a transaction (transaction) should have in a reliable database management system (DBMS): atomicity (atomicity), consistency (consistency), isolation (isolation), Persistence (durability). Atomicity means that the execution of a transaction in a database is as an atom. That is, no more points, the entire statement is either executed or not executed. Consistency means that a database transaction cannot break the integrity of relational data and the consistency of business logic. For example, a bank transfer transaction, whether successful or unsuccessful, should ensure that the total deposit of Tom and Jack in the accounts table after the end of the transaction is 2000 yuan. The isolation of a transaction is when multiple users access the database concurrently, the transaction that the database opens for each user is not interfered by the operational data of the other transaction, and the multiple concurrent transactions are isolated from each other. Persistence means that once a transaction is committed, its changes to the data in the database are permanent and should not be affected even if the database fails.

2.0 Configuration – No installation and management configuration required

3. A complete database stored in a single disk file

4. Database files can be shared freely between machines in different byte order

5. Support Database size to 2TB

6. Small enough, roughly 130,000 lines C code, 4.43M

7. Faster than some popular databases operate in most ordinary databases

8. Simple, Easy API

9. Includes TCL binding, while supporting other languages via wrapper

10. The source code of good annotation, and has more than 90% test coverage

11. Independence, no additional reliance

12. Source code completely open source, you can be used for any purpose, including selling it

13. Support for multiple development languages, C, PHP, Perl, Java, C#,python, Ruby

The basic use of SQLite in iOS

Some SQLite database management tools, such as Sqlitemanager, can be used in iOS development.

Next, we'll tell you how to use SQLite in iOS using code.

Introduction of Sqlite.h Files

The first is to open and close the database, open and create the database is the Sqlite3_open function, if filename has been created that is open.

NSString *filename;//Database file path sqlite3 *database; Sqlite3 database handle pointer//Open database-(int) open{int rc=sqlite3_open ([filename utf8string], &database); if (RC) {Sqlite3_close
(database);
NSLog (@ "Open database Failed");
} return RC; }//Close database-(void) close{if (database!=null) {sqlite3_close (db)}} Next insert, delete, update is the SQLITE3_EXEC function, remember to execute the statement, you must first open the data
Library, and you need to close the database when you are done. 
Execute Insert,update,delete SQL statement-(int) ExecuteNonQuery: (NSString *) SQL error: (NSERROR * *) error {int rc; char *errmsg;
rc = [self open]; if (RC) {//error-handling if (Error!= NULL) {nsdictionary *edict = [nsdictionary dictionarywithobject:@ ' Open database failed ' fo
Rkey:nslocalizeddescriptionkey];
*error = [Nserror errorwithdomain:ksqliteerrordomain code:rc userinfo:edict];
} return RC;
rc = sqlite3_exec (database, [SQL Utf8string], NULL, NULL, &ERRMSG); if (RC!= sqlite_ok) {if (Error!= NULL) {nsdictionary *edict = [nsdictionary dictionarywithobject:@ ' exec SQL error ' for
Key:nslocalizeddescriptionkey]; *error = [Nserror errOrwithdomain:ksqliteerrordomain CODE:RC Userinfo:edict];
} NSLog (@ "%s", errmsg);
Sqlite3_free (errmsg);
[Self close];
return RC; }

The Sqlite3_free in the above function is to release the memory space that holds the error message. Query operations are slightly more complex and require the operation of a switch database, but there is an operation to prepare the result set and finally release the result set, respectively, SQLITE3_PREPARE_V2 and sqlite3_finalize,sqlite3_stmt are the result sets, The following is the specific operation.

[Self open];
Check
strSQL = "SELECT * from users";
Sqlite_api int Sqlite3_prepare_v2 (
//Sqlite3 *db,/* Database handle/
/const char *zsql,/* SQL statement* ///
int nbyte,/* Maximum length of result set. *
///SQLITE3_STMT **ppstmt,/* out: Result set
//const char **pztail/* out: A pointer to the memory portion of the result set that is not used. */
// );
sqlite3_stmt* rc;//Declarative Handle
if (SQLITE3_PREPARE_V2 (DB, strSQL,-1, &RC, NULL)!=sqlite_ok) {
}
// Sqlite3_step says the result set data pointer points to the next element.
//The return value of this function, if it is Sqlite_row, indicates that there is data in our result set.
//Otherwise our result set is empty. While
(Sqlite3_step (RC) ==sqlite_row) {
//Sqlite3_column series functions. There are generally two input parameters. The first is the result set pointer, and the second is the ordinal number of the column that contains the data.
//For example, we now use the Sqlite3_column_int and Sqlite3_column_text.
printf ("id:%d | username:%s | password:%s \ n ", Sqlite3_column_int (RC, 0), Sqlite3_column_text (RC, 1), Sqlite3_column_text (RC, 2))
;
Be sure to release the result set after you check it out.
sqlite3_finalize (RC);
[Self close];

Database encryption

The free version of SQLite has a fatal drawback: encryption is not supported. This causes the data stored in the SQLite to be seen by anyone in any text editor.

There are two ways to encrypt a database:

1. Encrypt content and write to database

This way the use of simple, in the storage/out of the library only need to do the corresponding encryption and decryption operation can, to some extent, to solve the problem of exposing the data naked.

However, this approach is not a complete encryption, because the database table structure and other information can be found. The search is also a problem when the contents of the write to the database are encrypted.

2. Encrypt the database file

The whole database is encrypted, which can basically solve the information security problem of the database. The existing SQLite encryption is basically implemented in this way. Here is an open source encryption tool Sqlcipher, installation method can refer to the official website documents, Https://www.zetetic.net/sqlcipher/ios-tutorial/,SQLCipher use 256-bit AES encryption, Because of its free version of the SQLite, the main encryption interface and SQLite are the same, but also added some of their own interface.

In fact, SQLite's two encryption functions are very simple to use, the following points:

1 Add a password to an unencrypted database: If you want to add a password, you can call the Sqlite3_key function at any time before you close the database file after you open the database file, which has three parameters, the first parameter is the database object, and the second is the password you want to set. The third is the length of the password. For example: Sqlite3_key (db, "1q2w3e4r", 8); Set the password for the database 1q2w3e4r

2 reading data from an encrypted database: This task is still very simple, you only need to open the database, call the Sqlite3_key function again, for example, when the database password is 123456, you only need to add sqlite3_key (db, " 123456″,6);

3 Change the database password: first you need to use the current password to open the database correctly, and then you can call Sqlite3_rekey (db, "112233″,6") to change the database password.

4 Delete Password: That is, restore the database to clear state. You will still need to call the Sqlite3_rekey function only, and place the second argument of the function null or "", or set the third argument to 0.

Transaction operations

So the question is, what do you do if iOS SQLite inserts or queries 10,000 of data at the same time?

Here are three steps to do, first, reduce the operation of the switch database, insert 10,000 data, can not switch 10,000 times the database, only one switch;

Second, is not to be placed in the main thread;

Third, the most important point is to join the transaction operation.

A transaction (Transaction) is a program execution unit (units) that accesses and may update various data items in a database. When SQLite inserts data, the default statement is a transaction, and how many times the disk operation is available. So 10,000 disk operations may not be completed in a few minutes, this time need to encapsulate 10,000 statements into a transaction.

Here is the code to start the transaction and commit the transaction.

-(int) beginservice{
char *errmsg;
int rc = sqlite3_exec (Database, "BEGIN transaction", NULL, NULL, &ERRMSG);
return RC;
}
-(int) commitservice{
char *errmsg;
int rc = sqlite3_exec (Database, "COMMIT transaction", NULL, NULL, &ERRMSG);
return RC;}

Then merge the three operations

-(int) Addmodelstest: (Nsarray *) Models Error: (NSERROR * *) error{char *errmsg; __block Nsmutablearray *sqls=[
Nsmutablearray array];
__block Noticemodel *amodel=[[noticemodel alloc] init];
Dispatch_async (Dispatch_get_global_queue (dispatch_queue_priority_default, 0), ^{for (int i=0; i<100000; i++) {
Amodel=[models objectatindex:0]; NSString *sql=[nsstring stringwithformat:@ INSERT INTO notices values ('%LF ', '%d ', '%@ ', '%@ ', '%@ ', '%d ', '%d ', '%d ', '%d ') , '%@ ') ', Amodel.myid,amodel.news_id,amodel.news_title,amodel.content,amodel.pic,amodel.sort,amodel.record_
Status,amodel.counter,amodel.suid,amodel.publish_time];
[Sqls Addobject:sql];
int r1=[self Open];
[Self beginservice];
int RC;
int i; For (i=0 i<100000; i++) {rc=sqlite3_exec (database, [[Sqls objectatindex:i] utf8string], NULL, NULL, &AMP;ERRMSG);} [
Self Commitservice];
[Self close]; if (i ==100000) {Dispatch_async (Dispatch_get_main_queue (), ^{NSLog (@ "Call back," The data is:%@ ", i);});} else {NSLog (@ "error when download:%@", ERROR);
}
});
return 0; }

About the use of SQLite in iOS tutorial small series to introduce so many people, I hope to help you!