当前位置:网站首页>Zero basic IOS development learning diary - function chapter - SQLite database

Zero basic IOS development learning diary - function chapter - SQLite database

2021-06-22 00:45:41 99 is always one point away

start

SQLite database

Practical use

  • Save some cache data with low efficiency requirements , For example, chat records 、 Circle of friends content
  • Save user information

Basic usage

  • The basic usage revolves around adding, deleting, modifying and checking
  • Database operation call sqlite3.h file , It's using C Function of , And because OC It's not the same type swift Yes Any, There is no automatic data type checking , So it's going to be a little tangled
  • Before we build the database , To initialize a global database handle sqlite3 *db;, All operations on the database depend on this handle , Like a database tag
  • The corresponding visualization software is Nacicat for SQLite

Open the table

  • sqlite3_open(<#const char *filename#>, <#sqlite3 **ppDb#>) Parameter Introduction
 Parameters 
1. The full path of the database  Int8 -> uint8 -> byte / char c Language string 
2. Global database method ‘ Handle ’ ->  The pointer   
 Return value  == SQLITE_OK  It means success 
 If the database doesn't exist , Will create database , If the database exists , Will open the database 
 Copy code 
// Get absolute path  NSLibraryDirectory NSLibraryDirectory
NSString * path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
path = [path stringByAppendingPathComponent:@"demo.db"];
//const To the left of the pointer   The variable the modifier pointer points to remains unchanged 
//const To the right of the pointer   Modify a pointer   The address remains the same 
//NSString -> char
const char *cPath = [path cStringUsingEncoding:NSUTF8StringEncoding];
// open , To make a wrong judgment 
if (sqlite3_open(cPath, &db) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"success");
 Copy code 

Create table

  • Table creation SQL Sentence format
CREATE TABLE  Create table 
IF NOT EXISTS  Determine if the table exists , If it exists, it will not be executed 
'T_Person'  Table name 
(
     Field  INTEGER( Integers )/ real( decimal )/ TEXT ( character string )/ BOLB( binary data - Usually not saved in the database )
    NOT NULL  Not allowed to be empty , The primary key must have content 
    PRIMARY KEY  Primary key 
    AUTOINCREMENT  Automatic growth 
    ,  Commas distinguish fields , The last field doesn't need to be 
)
 Copy code 
  • SQL Execute statement sqlite3_exec(<#sqlite3 *#>, <#const char *sql#>, <#int (*callback)(void *, int, char **, char **)#>, <#void *#>, <#char **errmsg#>)
1. Global database handle 
2. Executes sql
3. Execution completed sql Of the call c Language function pointer , It's usually passed in nil
4. The address of the function parameter of the third parameter , It's usually passed in nil
5. error message , There are other ways to get execution , It's usually passed in nil
 Copy code 
// establish 
NSString *sql = @"CREATE TABLE IF NOT EXISTS 'local' ("
"'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
"'name' TEXT,"
"'age' INTEGER,"
"'adult' blob,"
"'height' real"
");";
const char *cSql = [sql cStringUsingEncoding:NSUTF8StringEncoding];
// Execute statement 
if (sqlite3_exec(db, cSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"success");
 Copy code 

Insert

  • Insert SQL Sentence format
INSERT INTO   Which data table to insert data into 
( Field 1,  Field 2...)
VALUES  value 
( value 1,  value 2....)
 Copy code 
NSString *insertSql = @"INSERT INTO local (id, name, age, adult, height) VALUES (2, 'liliu', 18, 0, 175.5);";
const char *cInsertSql = [insertSql cStringUsingEncoding:NSUTF8StringEncoding];
// Execute statement 
if (sqlite3_exec(db, cInsertSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"success");
 Copy code 

Delete

  • Delete SQL Sentence format
DELETE FROM  Delete from which list 
WHERE  Primary key  = id
 Copy code 
NSString *deleteSql = @"DELETE FROM local WHERE id = 2;";
const char *cDeleteSql = [deleteSql cStringUsingEncoding:NSUTF8StringEncoding];
// Execute statement 
if (sqlite3_exec(db, cDeleteSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"success");
 Copy code 

modify

  • modify SQL Sentence format
UPDATE  Table name 
SET 
 Field 1 =  value 1, Field 2 =  value 2 ...
WHERE  Primary key  = id
 Copy code 
NSString *updateSql = @"UPDATE local SET name = 'wangwu', age = 80 WHERE id = 2;";
const char *cUpdateSql = [updateSql cStringUsingEncoding:NSUTF8StringEncoding];
// Execute statement 
if (sqlite3_exec(db, cUpdateSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"success");
 Copy code 

Instead of

  • Be careful
1.sql Statement must contain primary key 
2. If the specified primary key does not exist , The new record 
3. If the specified primary key exists , Modify the record 
 Conditions   The primary key cannot be self growing , Because the primary key value needs to be specified 
 Copy code 
  • SQL sentence
INSERT OR REPLACE INTO Test (id, name, age) VALUES (2, 'wei', 99);
 Copy code 
NSString *replaceSql = @"INSERT OR REPLACE INTO Test (id, name, age) VALUES (2, 'wei', 99);";
const char *cReplaceSql = [replaceSql cStringUsingEncoding:NSUTF8StringEncoding];
// Execute statement 
if (sqlite3_exec(db, cReplaceSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"success");
 Copy code 

Inquire about

  • Inquire about SQL Sentence format
--  Check all records , Not recommended  *  wildcard , Read the code directly , Cannot know what the query returned , It can be used in testing 
-- SELECT * FROM T_Person;

--  Write the fields clearly , Easy to read 
-- SELECT id, name, height, age FROM T_Person;

--  Query quantity 
SELECT count(*) FROM T_Person;

--  Specify conditional statistics query 
SELECT count(*) FROM T_Person WHERE height > 1.5;
--  Query maximum   Application scenarios : The highest value of query experience , People who recently joined ...
SELECT MAX(age) FROM T_Person;

--  Pagination  LIMIT  Start with the number ( The starting number is 0), The number of record rows returned 
-- SELECT id, name, height, age FROM T_Person
-- LIMIT 1, 2;

-- LIMIT WHERE  Use a combination of , Easy to make paging function 
SELECT id, name, height, age FROM T_Person
WHERE id >= 3
LIMIT 2;

--  Sort   Default ascending order  ASC /  Descending  DESC
--  Sorting is based on specified conditions , From left to right , First arrange the conditions on the left 
SELECT id, name, height, age FROM T_Person
ORDER BY name DESC, age ASC;

--  Fuzzy query 
-- % Can match anything ,% Content %  As long as the content appears , Will be searched out 
--  logic  AND OR NOT
SELECT id, name, height, age FROM T_Person
WHERE (name LIKE '%a%' AND age > 80) OR name = 'wangwu';
 Copy code 
  • precompile SQL function sqlite3_prepare_v2(<#sqlite3 *db#>, <#const char *zSql#>, <#int nByte#>, <#sqlite3_stmt **ppStmt#>, <#const char **pzTail#>)
1. Global database handle 
2. To execute sql Of c Language string 
3. To execute sql The length in bytes of , But if you pass in -1,sql The frame will automatically calculate 
4.stmt  Precompiling instructions , Follow up for this query , All operations are based on this handle , Be sure to release ; adopt step function , Be able to get the results in sequence 
5. About stmt Pointer to tail parameter , It's usually passed in nil
 Return value   If the compilation is successful , Can be executed normally   return  SQLITE_OK
 Copy code 
NSString *querySql = @"SELECT * FROM local;";
const char *cQuerySql = [querySql cStringUsingEncoding:NSUTF8StringEncoding];
// Execute statement 
// Precompiling instructions , Think of every line 
sqlite3_stmt *stmt;
// precompile 
if (sqlite3_prepare_v2(db, cQuerySql, -1, &stmt, nil) != SQLITE_OK) {
    NSLog(@"failed");
    sqlite3_finalize(stmt);
    return;
}
NSMutableArray *arr = [[NSMutableArray alloc] initWithCapacity:10];
// When you have the next line, do it 
while (sqlite3_step(stmt) == SQLITE_ROW) {
    // Get the number of fields 
    int cols = sqlite3_column_count(stmt);
    NSMutableDictionary *rowDic = [NSMutableDictionary new];
    // Loop through each field 
    for (int col = 0; col < cols; col++) {
        // Get the field name 
        const char  *cName = (const char *)sqlite3_column_name(stmt, col);
        //char -> NSString
        NSString *name = [NSString stringWithCString:cName encoding:NSUTF8StringEncoding];
        // Get the value 
        const char *cValue = (const char *)sqlite3_column_text(stmt, col);
        NSString *value = [NSString stringWithCString:cValue encoding:NSUTF8StringEncoding];
        // assignment 
        rowDic[name] = value;
    }
    // Save a complete piece of information 
    [arr addObject:rowDic];
}
NSLog(@"success");
NSLog(@"%@", arr);
 Copy code 

Summary

  • It's not hard to see. , In addition to the special operation of query , Adding, deleting and modifying just need to prepare the corresponding sentences , And use sqlite3_exec Call
  • and SQL The acquisition of sentences , You can use visualization software Nacicat for SQLite To obtain a

Business

Practical use

  • Processing large amounts of data requires opening , Increase of efficiency
  • stay SQL in , If you don't explicitly open a transaction , Every database operation instruction will start a transaction , After execution , Commit transaction ; If you explicitly call , No more transactions
  • After opening , The database is snapshot , If the database is modified successfully, continue , If it fails, restore the snapshot , The snapshot is essentially the previous state
  • Transaction , If the error is to be rolled back , Make mistakes break

Basic usage

  • Insert below 20000 Take data for example , Try not to use the insertion time of things
// Get the current time 
CFTimeInterval start = CACurrentMediaTime();
// Open transaction 
NSString *transSql = @"BEGIN TRANSACTION;";
const char* cTransSql = [transSql cStringUsingEncoding:NSUTF8StringEncoding];
if (sqlite3_exec(db, cTransSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"TRANSACTION success");
// Insert a lot of data 
for (int i = 0; i < 20000; i++) {
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO local (name, age, adult, height) VALUES ( 'liliu-%d', 18, 0, 175.5);", i];
    const char *cInsertSql = [insertSql cStringUsingEncoding:NSUTF8StringEncoding];
    // Execute statement 
    if (sqlite3_exec(db, cInsertSql, nil, nil, nil) != SQLITE_OK) {
        NSLog(@"failed");
        return;
    }
    // Simulation error 
//        if (i == 1000) {
              // Error rollback 
//            NSString *rollbackSql = @"ROLLBACK TRANSACTION;";
//            const char* cRollbackSql = [rollbackSql cStringUsingEncoding:NSUTF8StringEncoding];
//            if (sqlite3_exec(db, cRollbackSql, nil, nil, nil) != SQLITE_OK) {
//                NSLog(@"failed");
//                return;
//            }
//            NSLog(@"ROLLBACK success");
              // interrupt 
//            break;
//        }
}
// If it's inserted correctly , Then commit the transaction 
NSString *commitSql = @"COMMIT TRANSACTION;";
const char* cCommitSql = [commitSql cStringUsingEncoding:NSUTF8StringEncoding];
if (sqlite3_exec(db, cCommitSql, nil, nil, nil) != SQLITE_OK) {
    NSLog(@"failed");
    return;
}
NSLog(@"COMMIT success");
// Get the time of the whole process  0.231517
NSLog(@"%f", CACurrentMediaTime() - start);
 Copy code 

Performance testing

  • Take the function of absolute time
CFAbsoluteTimeGetCurrent()   Of the core framework   Will be affected by system services , Time calibration ; During the performance test , There will be errors 
CACurrentMediaTime()  The core of animation is , It's only about hardware time 
 Copy code 

summary

  • iOS Operating the database , The most important point is SQL Statement writing , It's best to go through visualization software testing , In the writer
  • In actual development , A row of data corresponds to an object , Therefore, fields in the database are often encapsulated as attributes of objects , Convenient operation
  • The database execution method has a lot in common , It is often encapsulated as a singleton management class , To operate , The structure is generally controller call object , object call Database management class

FMDM

  • Put it in a third-party library to sort out

版权声明
本文为[99 is always one point away]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/06/20210602034556004H.html