iOS USES the SQLite tutorial

SQLite, a lightweight database, is a relational database management system that complies with ACID. Its design target is embedded, and it has been used in many embedded products at present. It occupies very low resources. It can support Windows/Linux/Unix and other mainstream operating systems. At the same time, it can be combined with many programming languages, such as Tcl, C#, PHP, Java, and ODBC interfaces. It is also faster than Mysql and PostgreSQL, two world famous open source database management systems. The first version of SQLite, Alpha, was created in May 2000. It's been 14 years since SQLite was launched, and SQLite 3 has been released.

The characteristics of SQLite

1. ACID affairs

ACID refers to the four properties that transactions (transaction) should have in a reliable database management system (DBMS) : atomicity (Atomicity), 1 uniqueness (Consistency), isolation (Isolation), and persistence (Durability). Atomicity means that transactions in the database are executed as atoms. The entire statement either executes or does not. 1 uniqueness means that database transactions cannot compromise the integrity of relational data and 1 uniqueness in business logic. For example, for a bank transfer transaction, whether the transaction succeeds or fails, the total amount of Tom and Jack deposits in ACCOUNTS form at the end of the transaction should be guaranteed to be 2,000 yuan. Transaction isolation refers to the fact that when multiple users concurrently access the database, the database is open for each user, which cannot be interfered by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other. Persistence is when a transaction is committed and the changes it makes to the data in the database are permanent, and the subsequent failure of the database should not have any impact on it.

2. Zero configuration without installation and management configuration

3. A complete database stored in a single disk file

4. Database files can be freely Shared between machines with different byte orders

5. Support database sizes up to 2TB

6. Small enough to be approximately 130,000 lines of C code, 4.43M

7. Faster than some popular databases in most common database operations

8. Simple, relaxed API

9. Includes TCL bindings, and supports bindings for other languages through Wrapper

10. Well-commented source code with over 90% test coverage

11. Independence, no extra dependence

12. The source code is completely open source, you can use it for any purpose, including selling it

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

Basic use of SQLite in iOS

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

The following is a code description of how sqlite is used in iOS

Introduction of the sqlite.h file

The first is to open and close the database. Opening and creating the database are sqlite3_open functions. If filename has been created, it is opened.

NSString *filename;// Database file path 
sqlite3 *database; //sqlite3 A pointer to a database handle 
// Open the database 
- (int) open{
int rc=sqlite3_open([filename UTF8String], &database);
if (rc) {
sqlite3_close(database);
NSLog(@"open database failed");
}
return rc;
}
// Shut down the database 
- (void) close{
if (database!=NULL) {
sqlite3_close(database);
}
}
 Insert, delete, update are used sqlite3_exec Function, remember to execute the statement, you have to open the database first, and then you have to close the database. 
// perform  insert,update,delete  Such as the query SQL statements 
- (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"
forKey: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"
forKey:NSLocalizedDescriptionKey];
*error =
[NSError errorWithDomain:kSqliteErrorDomain code:rc userInfo:eDict];
}
NSLog(@"%s", errmsg);
sqlite3_free(errmsg);
}
[self close];
return rc;
}

sqlite3_free in the above function is to free the memory space where the error information is stored. The query operation is slightly more complicated, and also requires the operation of turning on and off the database, but there is one operation to prepare and finally release the result set, sqlite3_prepare_v2 and sqlite3_finalize, respectively. sqlite3_stmt is the result set, and 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, /*  The maximum length of the result set. */
// sqlite3_stmt **ppStmt, /* OUT:  The result set  */
// const char **pzTail /* OUT: A pointer to an unused portion of memory in the result set.  */
// );
sqlite3_stmt* rc;// Declarative handle 
if (sqlite3_prepare_v2(db, strsql, -1, &rc, NULL)!=SQLITE_OK) {
}
// sqlite3_step The result set data pointer points down 1 An element. 
//  The return value of this function if SQLITE_ROW That means we have data in our result set. 
//  Otherwise our result set will be empty. 
while (sqlite3_step(rc)==SQLITE_ROW) {
// sqlite3_column Series of functions. 1 There are usually two input parameters. The first 1 One is a pointer to the result set, one 2 Is the ordinal number of the column in which the data resides. 
//  Like what we have now 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));
}
//  After the check 1 You have to release the result set. 
sqlite3_finalize(rc);
[self close];

Database encryption

The free version of SQLite has one fatal flaw: it doesn't support encryption. This results in data stored in SQLite that can be viewed by anyone using any text editor.

There are two ways to encrypt a database:

1. Encrypt the content and then write it to the database

This method is simple to use, in the warehouse/warehouse only need to do the corresponding field encryption and decryption operation, 1 to a certain extent to solve the problem of exposing the data naked.

However, this is not a complete encryption, because the database table structure and other information can be seen. In addition, after the contents of the database are encrypted, search is also a problem.

2. Encrypt database files

The entire database file encryption, this way basically can solve the database information security problem. The existing SQLite encryption is basically implemented in this way. Here is introduced a open source encryption tool SQLCipher, installation method can consult website document, https: / / www zetetic. net/sqlcipher/ios tutorial /, use 256 - bit SQLCipher AES encryption, because of its SQLite, based on the free version of main encryption interface and SQLite is the same, but also increased 1 some of its own interface.

In fact, the two encryption functions of SQLite are very simple to use, as follows:

Add 1 to 1 unencrypted database password: if you want to add a password, after you can open the database file, close the database file before any time call sqlite3_key function, this function has three parameters, including the first parameter to database objects, the second parameter is to set a password, the third is the length of the password. For example: sqlite3_key (db, "q2w3e4r" 1, 8). / / 1 q2w3e4r set password to the database

2 read data from an encrypted database: it is still 10 minutes easy to complete this task, you only need to open the database and call sqlite3_key function under 1 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 open the database correctly with the current password, then you can call sqlite3_rekey(db, "112233",6) to change the database password.

Delete password: that is, restore the database to plaintext state. You still only need to call the sqlite3_rekey function and set the second argument to NULL or "", or set the third argument to 0.

The transaction operations

So again, what do you do if iOS's sqlite inserts or queries 10,000 pieces of data at the same time?

There are 3 steps to do here, the first, reduce the switch database operation, insert 10000 pieces of data, can not switch the database 10000 times, only once switch;

Number two, you can't put it on the main thread;

Third, the most important point is to add transaction operations.

A transaction (Transaction) is a unit of program execution (unit) that accesses and may update various data items in a database. When sqlite inserts data, by default one statement is one transaction, and there are as many disk operations as there are data. So 10,000 disk operations can take a few minutes to complete, and you need to wrap 10,000 statements into one transaction.

Here is the code to start 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;}

Next, combine 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, &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;
}

iOS about the use of SQLite tutorial this site to give you so much, I hope to help you!