IOS Data store (persistent storage, cache)

Storage basics

Application sandbox

Apply the sandbox folder

Application: Contains all resource files and executables, which are digitally signed before being launched and cannot be modified after being launched.
Documents: A directory for storing data generated by the application, which is automatically stored in iCloud. Store persistent data generated while the app is running, and back it up when iTunes synchronizes the device. For example, a game application can save a game archive in this directory. (Note: Do not save files downloaded from the Internet, or they will not be on the shelf!)
Library:
1. User preference, use NSUserDefault to read and write directly!
2. If you want data to be written to disk in a timely manner, you also need to invoke a synchronization method
3. Save temporary files “for later use”, e.g. cached images, offline data (map data)
4. The system will not clean the files in the cache directory, so it requires the program development, “must provide cache directory cleaning solution”.
5. Caches: Store bulky data that doesn’t need to be backed up
6. Preference: Stores all application preferences. ICloud backs up the Settings
Tmp: temporary files. The system automatically clears them. A reboot will clean it up. Save temporary data for application running and delete files from this directory after use. If the application is not running, the system may delete files in this directory. ITunes does not back up this directory when it syncs devices.
1. Store temporary files that can’t be backed up, and the data in this file can be erased at any time
2. Save temporary file “not needed for future use”
3. The system automatically deletes files in the TMP directory
4. Restart the phone and the TMP directory will be cleared
5. If the system disk space is insufficient, the system automatically deletes the disk space

A common way to get sandbox directories

Sandbox root directory:NSString *home = NSHomeDirectory();

Documents :(2 ways)

Concatenate the “Documents” string using the sandbox root (not recommended as directory names may change on newer operating systems)
```
NSString *home = NSHomeDirectory();
NSString *documents = [home stringByAppendingPathComponent:@"Documents"];
Copy the code
```

Using NSSearchPathForDirectoriesInDomains function

// NSUserDomainMask indicates to search from the user folder. // YES indicates the wavy character "~" NSArray *array = in the expansion path NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, NO); NSString * Documents = [array objectAtIndex:0]; NSString * Documents = [array objectAtIndex:0]; NSString * Documents = [array objectAtIndex:0];Copy the code

TMP:NSString *tmp = NSTemporaryDirectory();
Library/Caches :(2 methods similar to Documents)
1. Concatenate the “Caches” string using the sandbox root directory
2. Using NSSearchPathForDirectoriesInDomains function (the function of the second parameter is changed to: NSCachesDirectory)
Library/Preference: Access Settings in this directory through the NSUserDefaults class.

The difference between memory cache and disk cache

There are two types of cache: memory cache and disk cache. Among them, memory refers to the running space of the current program. The cache speed is fast and the capacity is small. It is used to temporarily store files for THE CPU to read directly. Disk is the storage space of program. It has large cache capacity, slow speed and persistence. Unlike memory, disk is a permanent store of things, as long as it stores things, whether running or not, it takes up space! Disk cache is present with Library/Caches.

Memory partition

IOS memory is divided into five areas: stack area, heap area, global area, constant area, code area (from left to right from high address like low address).

Stack area: this area will be managed by the system, we do not need to intervene, mainly save some local variables, as well as the on-site protection when the function jumps. So a large number of local variables, deep recursion, and function loop calls can all cause memory to run out and crash.
Heap: In contrast to the stack area, this area is generally managed by ourselves, such as alloc, free operations, storing objects created by ourselves.
Global area (static area) : global variables and static variables are stored in this area, initialized and not initialized will be stored separately in the adjacent area, the system will be released after the program ends.
Constant area: Stores constant strings and const constants
Code area: Stores code

NSCache

NSCache is a caching mechanism provided by Apple. It is similar to NSMutableDictionary. It is used in AFNetworking, SDWebImage, and Kingfisher.

NSCache automatically releases memory when the memory is insufficient. NSCache Sets the number of cache objects and the memory occupied by the cache. When the cache exceeds the specified value, the memory is automatically released. NSCache is a key-value data structure, in which the Key is a strong reference. The NSCoping protocol is not implemented, and the object used as the Key is not copied.

The properties of NSCache

CountLimit: The maximum number of objects that can be cached. The default value is 0 and there is no limit. TotalCostLimit: set the cache memory size evictsObjectsWithDiscardedContent: whether the content of recycling waste, the default is YES

The method of NSCache

ObjectForKey: Gets the cache object by key. SetObject: forKey: cache object. SetObject: forKey: cost: caches the object and specifies the cost of the key value, which is used to calculate the total cost of all objects in the cache. RemoveObjectForKey: Deletes the specified object. RemoveAllObjects: Removes all cached objects.

NSCacheDelegate agent

WillEvictObject: Called when the cache object is about to be cleaned. It is used for debugging by developers. You cannot modify the cache in this method.

Is called in the following scenarios:

removeObjectForKey
The cache object exceeds the limit set by the countLimit and otalCostLimit properties of NSCache
App entering the background
The system generates a memory warning
Cache before the end of the cache instance’s life cycle

NSCache points to pay attention to

When we receive a memory warning and call removeAllObjects, we cannot continue to add data to the cache. The total cache size is not provided. If you want to know the memory size occupied by NSCache, you can calculate the cost of adding cache. The algorithm for NSCache to automatically free memory is uncertain, sometimes by LRU(most recently unused), sometimes at random. The data in NSCache will disappear after the APP restarts, because NSCache only stores the data in memory.

NSCache is thread-safe and does not require a thread lock, whereas NSMutableDictionary threads are not.

The code for

@interface NSCacheVC ()<NSCacheDelegate> @property (nonatomic, strong) NSCache *myCache; @end @implementation NSCacheVC - (void)viewDidLoad { [super viewDidLoad]; // Do any additional setup after loading the view. self.view.backgroundColor = [UIColor purpleColor]; self.myCache = [[NSCache alloc]init]; self.myCache.delegate = self; for (int i = 0; i<10; i++) { [self.myCache setObject:[NSString stringWithFormat:@"%d", i] forKey:@(i) cost: 1]; } for (int i = 0; i<10; I++) {NSLog (@ "NSCache out - % @", [self. MyCache objectForKey: @ (I))); } // remove cache [self.myCache removeAllObjects]; / / / set the cache limits self. MyCache. TotalCostLimit = 5; NSLog(@" after setting cache limits ================="); for (int i = 0; i<10; I ++) {// Set cost to 1 [self.myCache setObject:[NSString stringWithFormat:@"%d", I] forKey:@(I) cost: 1]; } for (int i = 0; i<10; I++) {NSLog (@ "NSCache out - % @", [self. MyCache objectForKey: @ (I))); } // remove cache [self.myCache removeAllObjects]; NSLog(@" set cache limit but not set cost================="); for (int i = 0; i<10; i++) { [self.myCache setObject:[NSString stringWithFormat:@"%d", i] forKey:@(i)]; } for (int i = 0; i<10; I++) {NSLog (@ "NSCache out - % @", [self. MyCache objectForKey: @ (I))); } // remove cache [self.myCache removeAllObjects]; } // this is called when an object is about to be reclaimed, and complies with the NSCacheDelegate protocol before implementing the proxy method. - (void)cache:(NSCache *)cache willEvictObject:(id)obj{NSLog(@" reclaim NSCache --%@", obj); } @endCopy the code

Print the resultIt can be seen from the printed result:

When you set the cache limit and know the cache cost, exceeding the limit is automatically reclaimed. However, setting the cache limit without knowing the cache cost is not automatically reclaimed.
WillEvictObject’s proxy method is called when it is recycled.

Persistent storage

Plist (NSArray\NSDictionary) stores only arrays and dictionaries, but arrays and dictionaries cannot have custom objects.
Preference\NSUserDefaults also cannot store custom objects.
Archive NSCoding (NSKeyedArchiver\NSkeyedUnarchiver) to store custom objects. Limitations: One-time read and store operations.
SQLite3
- Manipulating data is faster
- It can be read locally
- It is small and occupies less memory resources
Core Data

Plist

An attribute list is an XML file with the extension plist. If it is an NSString, NSDictionary, NSArray, NSData, NSNumber, etc., You can write the object directly to the property list file using the writeToFile:atomically: method.

Property list – Archive NSDictionary Archive an NSDictionary object into a plist property list

NSMutableDictionary *dict = [NSMutableDictionary dictionary]; Dict setObject:@" dict "forKey:@"name"]; [dict setObject:@"155xxxxxxx" forKey:@"phone"]; [dict setObject:@"27" forKey:@"age"]; // Persist dictionaries to Documents/stu.plist file [dict writeToFile:path atomically:YES];Copy the code

Property list – Restore NSDictionary Read the property list and restore the NSDictionary object

/ / read the Documents/stu. The content of the plist, instantiation NSDictionaryNSDictionary * dict = [NSDictionary dictionaryWithContentsOfFile: path]; NSLog(@"name:%@", [dict objectForKey:@"name"]); NSLog(@"phone:%@", [dict objectForKey:@"phone"]); NSLog(@"age:%@", [dict objectForKey:@"age"]);Copy the code

Property list -NSDictionary storage and reading procedures

preferences

Many iOS apps support preferences, such as saving usernames, passwords, font sizes, etc., and iOS provides a standard solution for adding preferences to apps. Every application has an instance of NSUserDefaults through which preferences are accessed.

For example, save username, font size, automatic login:

NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults]; [defaults setObject:@" username"]; [defaults setFloat: 18.0 f forKey: @ "text_size"]. [defaults setBool:YES forKey:@"auto_login"];Copy the code

Reads the Settings last saved

NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
NSString *username = [defaults stringForKey:@"username"];
float textSize = [defaults floatForKey:@"text_size"];
BOOL autoLogin = [defaults boolForKey:@"auto_login"];
Copy the code

Note: When UserDefaults sets data, it does not write immediately, but writes the cached data to local disk periodically, based on a timestamp. So it is possible that the application will terminate before the data is written to disk after the set method is called. This can be done by calling the synchornize method [defaults synchornize]. Write forcibly.

Belong to the solution file

NSKeyedArchiver If the object type is NSString, NSDictionary, NSArray, NSData, and NSNumber, you can use NSKeyedArchiver to archive and restore the object. Not all objects can be archived directly in this way, only objects that comply with the NSCoding protocol.

The NSCoding protocol has two methods:
1. encodeWithCoder: This method is called each time an object is archived. This method typically specifies how to archive each instance variable in an object. You can archive instance variables using the encodeObject:forKey: method.
2. initWithCoder: This method is called every time an object is recovered (decoded) from a file. This method specifies how to decode the data in the file as the instance variable of the object. DecodeObject :forKey can be used to decode the instance variable.

Archive an NSArray object into Documents/ Array.archive

NSArray *array = [NSArray arrayWithObjects:@ "a",@ "b",nil]; [NSKeyedArchiver archiveRootObject:array toFile:path];Copy the code

NSKeyedArchiver- Archive the Person object (person.h)

@interface Person : NSObject, NSCoding @property (nonatomic, copy) NSString *name; @property (nonatomic, assign) int age; @property (nonatomic, assign) float height; @end @implementation Person - (void)encodeWithCoder:(NSCoder *)encoder { [encoder encodeObject:self.name forKey:@"name"]; [encoder encodeInt:self.age forKey:@"age"]; [encoder encodeFloat:self.height forKey:@"height"]; } - (id)initWithCoder:(NSCoder *)decoder { self.name = [decoder decodeObjectForKey:@"name"]; self.age = [decoder decodeIntForKey:@"age"]; self.height = [decoder decodeFloatForKey:@"height"]; return self; } @end // archive (code) Person * Person = [[Person alloc] init]; person.name = @"xxx"; person.age = 27; Person. Height = 1.83 f; [NSKeyedArchiver archiveRootObject:person toFile:path]; / / recovery (decoding) Person * Person = [NSKeyedUnarchiver unarchiveObjectWithFile: path];Copy the code

If the parent class also complies with the NSCoding protocol, note that you should add [super encodeWithCode:encode] to the encodeWithCoder: method; Ensure that inherited instance variables can also be encoded, that is, archived. You should add self = [super initWithCoder:decoder] to the initWithCoder: method; Ensure that inherited instance variables can also be decoded, that is, can also be restored.

NSData – archive

Using archiveRootObject: toFile: method, an object can be directly written to a file, but may sometimes want to write to the same file multiple objects, then using NSData object file. NSData can provide temporary storage for some data to be later written to a file or to hold the contents of a file read from disk. Mutable data Spaces can be created using [NSMutableData data].

Archiving (coding)

NSMutableData *data = [NSMutableData data]; / / connect the data area to an NSKeyedArchiver object NSKeyedArchiver * archiver = [[NSKeyedArchiver alloc] initForWritingWithMutableData: data]; NSMutableData [archiver encodeObject:person1 forKey:@"person1"]; [archiver encodeObject:person2 forKey:@"person2"]; [archiver finishEncoding]; // Write the archival data to the file [data writeToFile:path atomically:YES];Copy the code

NSData- Recover 2 Person objects from the same file recovery (decoded)

/ / the data read from the file NSData * data = [NSData dataWithContentsOfFile: path]; // According to the data, Parsed into a NSKeyedUnarchiver object NSKeyedUnarchiver * unarchiver = [[NSKeyedUnarchiver alloc] initForReadingWithData: data]; Person *person1 = [unarchiver decodeObjectForKey:@"person1"]; Person *person2 = [unarchiver decodeObjectForKey:@"person2"]; [unarchiver finishDecoding];Copy the code

Deep copy using archives such as a Person object

/ / temporary data storage person1 NSData * data = [NSKeyedArchiver archivedDataWithRootObject: person1]; / / parse the data and generate a new Person object Student * person2 = [NSKeyedUnarchiver unarchiveObjectWithData: data]; NSLog(@"person1:0x%x", person1); // person1:0x7177a60 NSLog(@"person2:0x%x", person2); // person2:0x7177cf0Copy the code

Core Data

Three objects in Core Data

Ns-managed object whenever you define a class that inherits from that class, you create a table that corresponds to that class, and every object that you create by inheriting from that class is a piece of data in that table.
Ns-managed ObjectContext is used to manipulate a database, and it can add, delete, modify, and query tables in the database as long as there are classes
NSPersistentStoreCoordinator decide the location of the storage

Core data Multithreading is unsafe

Core Data itself is not a concurrency safe architecture, so implementing Core Data in multiple threads can be problematic.

Cause: NS-Managed ObjectContext in CoreData is not secure in multithreading.
If you want multiple threads to access CoreData, the best way to do that is one ns-managed object context per thread.
Each NSManagedObjectContext can use the same NSPersistentStoreCoordinator instance, Because NSManagedObjectContext before using NSPersistentStoreCoordinator locked.

Key string

The keychain is stored on the hard disk. After the application is deleted, the saved data still exists.

The keychain of each APP is relatively independent. However, keychain data between apps can be shared under the premise that the same TeamID is configured for data sharing.

Realize data sharing

Run the capabilities -> keychain sharing capabilities -> Keychain Sharing capabilities -> Keychain Sharing Enable keychain and add an identifier to it. All apps under the same TeamID share the same identifier, so that you can access the data stored under the same identifier.

Note: You will notice that when Keychain is enabled, Xcode automatically adds our bundleID to the first entry. If you do not specify an entry when writing data, it will be saved to the first entry by default.

The account password is automatically filled in

Requires iOS11.0 and higher.

To store passwords in keychains and share them between devices:
1. You need to have a domain name that supports THE HTTPS protocol, because the App automatically matches usernames and passwords to be associated with the site.
2. Turn on the iPhone’s iCloud keychain service.
Auto fill development configuration steps:
1. Enable Associated Domains in the App account
2. Enable Associated Domains in the project
3. Create file: apple-app-site-association
```
{
    "webcredentials": {
        "apps": ["teamID.BundleID"]
    }
}
Copy the code
```
1. Upload the file to the directory specified in Associated Domains or to the. Well-know folder. Verification method: You can access the file in HTTPS mode: example.com/.wellhnow/a…
2. So in your Xib or in your code, fill the textContentType of the input field with.username and.password.
3. Mobile phone [Settings] – [Password and Account] – Website and Application Password] Enable the automatic password filling function.
Principle of automatic filling: After the user installs the APP, the system will associate the APP with the domain name list and obtain each domain from the Associated Domains Entitlement. The system will try to download the Apple APP Site Association file of the domain. If all the above steps are successful, The system associates the application with the domain and starts automatic password filling for the credentials of the domain.

icloud

ICloud document storage function to meet the requirements of application data cloud storage, users can access or modify this part of App data from any device under their iCloud account.

In iOS iCloud storage, Apple offers three features:

Key-value storage is similar to the function of NSUserDefaults in iOS to save simple data by key-value pairs. This property-list data format is suitable for storing some non-critical data, such as user configuration.
ICloud Documents provides the data types of files and directories, which determines that this function is more suitable for storing critical data than key-value storage.
CloudKit is much more complex than the first two. It is a suite of database tools that Apple provides to developers. Developers can configure the required table structure through the Apple-provided Cloud Dashboard website and perform database operations by importing CloudKit into their code.

SQLite3

To use SQLite3 in iOS, first add the library libsqlite3.dylib and import the main header file #import

SQLite is an embedded, lightweight relational file database. It has a very low resource footprint, and in embedded devices, may only need a few hundred K of memory. Its processing speed is faster than Mysql and PostgreSQL, two well-known databases.
What is a Database A Database is a warehouse that organizes, stores, and manages data according to its data structure. Database can be divided into two types: relational database (mainstream), object database.
Common relational databases on PC: Oracle, MySQL, SQL Server, Access, DB2, Sybase. Embedded mobile client: SQLite.
How does a database store data? A database stores data in tables, much like Excel, right
Procedure for storing data in a database Create a table. Add multiple fields (column, column, attribute). Add multiple rows (rows that hold values for multiple fields).

Some nouns

A primary key

Select * from t_student; select * from t_student; select * from t_student; select * from t_student; select * from t_student; select * from t_student; Good database programming practices should ensure that each record is unique, and to do so, add a primary key constraint. That is, each table must have a primary key that identifies the uniqueness of the record.
What is a Primary Key? A Primary Key (PK) uniquely identifies a record. For example, t_student can add an ID field as the primary key, which is equivalent to a person’s ID card. The primary key can be a field or multiple fields.
Design principles for primary keys
1. Primary keys should be meaningless to the user
2. Never update the primary key
3. Primary keys should not contain dynamically changing data
4. The primary key should be generated automatically by the computer
Primary key declaration
1. Select * from table t_student; select * from table t_student; select * from table t_student;create table t_student (id integer primary key, name text, age integer);
2. If a primary key field is declared as a primary key, it is a primary key field. The primary key field contains both not NULL and unique constraints by default.
3. If you want the primary key to grow automatically (it must be of type INTEGER), you should add autoINCREMENTcreate table t_student (id integer primary key autoincrement, name text, age integer);

A foreign key

Foreign key constraints Foreign key constraints can be used to establish relationships between tables. A foreign key is a field in a table that references a primary key field in another table.
Create a new foreign keycreate table t_student (id integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_t_student_class_id_t_class_id foreign key (class_id) references t_class (id);The T_student table has a foreign key called fk_t_student_class_id_t_class_id. This foreign key is used to reference the ID field of t_class with the class_id field in T_student.

The field type

SQLite divides data into the following storage types: INTEGER: Integer data. The size of the data is 4 bytes. Bigint: Integer data. The size of the data is 8 bytes. Smallint: integer data. The value is 2 bytes. Tinyint: Data ranging from 0 to 255, stored in 1 byte. Float: 4-byte floating point number. Double: an 8-byte floating point number. Real: 8-byte floating point number. Text: indicates a text string. Blob: Binary data (such as files).
Other types NULL: null value. Blob: binary object. Default: indicates the default value. Primary Key: indicates the primary key. Autoincrement: indicates that the primary key automatically increases its length
SQLite is actually untyped and can store string text (except for primary keys) even if declared as integer. Create table t_student(name, age); create table t_student(age); In order to maintain good programming practices and facilitate communication between programmers, it is best to include the specific type of each field when writing table building sentences.

The SQL statement

Type of SQL statement
1. DDL: Data Definition Language (DDL: Data Definition Language) includes operations such as create and DROP to create a new table or drop a table in a database.
2. Data Manipulation Language (DML) Includes insert, UPDATE, and DELETE operations. The preceding operations are used to add, modify, and delete Data in a table
3. DQL: Data Query Language (DQL: Data Query Language) can be used to Query the Data in a table. The keyword SELECT is the most commonly used operation in DQL (and all SQL). Other DQL keywords commonly used are WHERE, ORDER BY, Group BY, and HAVING
SQL statements are case-insensitive (for example, the database considers user to be the same as user). Each statement must be preceded by a semicolon; At the end.
Common keywords in SQL include SELECT, INSERT, update, delete, FROM, create, WHERE, desc, order, BY, group, table, ALTER, view, index, etc.
You cannot use keywords to name tables and fields in a database.

Gen table

Format: create table Table name (field name 1, field type 1, field name 2, field type 2…) ; Create table if not exists Table name (field name 1 field type 1, field name 2 Field type 2,…) ; Example:

create table t_student (id integer, name text, age inetger, score real);
create table if not exists Student (
    ID integer primary key autoincrement,
    Name varchar(128),
    Age integer,
    Class interger default 0,
    RegisterTime datetime,
    Money float default 0,
    Birthday date
);
Copy the code

Delete table

Format: drop table Table name; Drop table if exists Specifies the name of the table. Example:

drop table t_student;
Copy the code

Insert data

Insert into table name (insert into table name, insert into table name, insert into table name, insert into table name, insert into table name (insert into table name, insert into table name) Values (field 1 value, field 2 value,…) ; Example:

Insert into T_student (name, age) values (' 三 ', 10);Copy the code

Note: String contents in the database should be enclosed in single quotes; All strings must be quoted; Integer, floating point numbers do not use “”; Dates need single quotation marks. Field order doesn’t matter; There is no need to insert fields for self-growing primary keys;

Update data

Format: the update table name set the value of the field 1 = 1, 2 = field 2 value,… ; Example:

Update t_student set name = 'jack', age = 20;Copy the code

Note: The above example changes the name of all records in the T_student table to Jack and the age to 20

Delete data (delete)

Format: delete from table name; Example:

Delete from t_student where ID=2; Delete from t_student; // delete from t_student;Copy the code

Note: The above example deletes all records from the T_student table

DQL statement — The conditional statement WHERE

If you only want to update or delete fixed records, you must add conditions to the DML statement

A common format for conditional statements is where field = some value; // Can not use two = where field is a value; // is equivalent to = where field! = a value; Where field is not a value; // Is not equal to! = select * from ‘where’; Where field 1 = 2 > a value and field a value; // and = && where (1, 2); / / or equivalent to the C language in the | |

Example:

Update t_student set age = 5 where age > 10 and name! = 'jack'; Delete from t_student where age <= 10 or age > 30; delete from t_student where age <= 10 or age > 30; Update t_student set score = age where name = 'jack'; update t_student set score = age where name = 'jack';Copy the code

DQL statement — select

Select field 1, field 2… From the name of the table; Select * from table_name; // Query all fields:

select name, age from t_student ; select * from t_student ; Select * from t_student where age > 10; Select * from t_student where name like '% s_ %' or phone like '% s_ %';Copy the code

Select field 1 alias, field 2 alias,… From table name alias; Select 1 as alias, 2 as alias,… From 表名 as alias; Select the alias. Field 1, alias. Field 2,… From table name alias; Example:
```
Select name myname, age myage from t_student; select name myname, age myage from t_student; Select s.name, s.age from t_student; select s.name, s.age from t_student;Copy the code
```

Select count (*) from table name; Select count (*) from table_name; Example:

select count (age) from t_student ;
select count ( * ) from t_student where score >= 60;
select count(*) from t_student; 
select avg(Age) from t_student; 
select sum(Age) from t_student;
Copy the code

Sort Searches based on the value of a field

Select * from t_student order by; select * from t_student order by age;Copy the code

The default is to sort in ascending order (from smallest to largest) or descending order (from largest to smallest).

select * from t_student order by age desc; Select * from t_student order by age asc; // Ascending (default)Copy the code

You can also sort with multiple fields

Select * from t_student order by age ASc, height desc;Copy the code

DQL statement — limit

Select * from table name limit 1, 2; select * from table name limit 2; Example:

Select * from t_student limit 4, 8; select * from t_student limit 4, 8; Select * from t_student limit 1; Select * from t_student limit 0, 7;Copy the code

Limit is usually used for paging queries, such as 5 columns per page.

Limit 0, 5 limit 5, 5 limit 10, 5 Page n: limit 5*(n-1), 5Copy the code

Simple constraints

Common constraints include not NULL: specifies that the value of a field cannot be null. Unique: specifies that the value of a field must be unique. Default: specifies the default value of a field. The name field cannot be null, and the unique age field cannot be null. Create table t_student (ID INTEGER, name text not null unique, age INTEGER not null default 1);

Table join query

Select * from inner join; select * from left outer join; select * from left outer join; select * from left outer join;

Select s.name, s.age from t_student s, t_class c where s.class_id = c.id and c.name = '0316iOS'; select s.name, s.age from t_student s, t_class c where s.class_id = c.id and c.name = '0316iOS';Copy the code

Database operation statement

Create or open the database

// path is the directory where the database file is stored sqlite3 *db = NULL; int result = sqlite3_open([path UTF8String], &db);Copy the code

Code parsing: sqlite3_open() will open the database based on the file path, or create a new database if none exists. If result equals the constant SQLITE_OK, the database is successfully opened. Sqlite3 * DB: An open database instance. The path to the database file must be passed in as a C string, not an NSString.

Closing the database

sqlite3_close(db);

Execute SQL statements that do not return data

char *errorMsg = NULL; Char * SQL = "create table if not exists t_person(ID INTEGER primary key autoincrement, name text, age integer);" ; Int result = sqlite3_exec(db, SQL, NULL, NULL, &errorMsg);Copy the code

Sqlite3_exec () can perform any SQL statement, such as table creation, update, insert, and delete operations. Sqlite3_exec () can also execute statements: sqlite3_exec()

Start a transaction: begin Transaction;
Rollback transaction: rollback;
Commit transaction: commit;

Insert data with placeholders

char *sql = "insert into t_person(name, age) values(? ,?) ;" ; sqlite3_stmt *stmt; If (sqlite3_prepare_v2(db, SQL, -1, &stMT, NULL) == SQLITE_OK) {sqlite3_bind_text(STMT, 1, -1, NULL); sqlite3_bind_int(stmt, 2, 27); } if (sqlite3_step(stmt) ! = SQLITE_DONE) {NSLog(@" insert data error "); } sqlite3_finalize(stmt);Copy the code

Sqlite3_prepare_v2 () returns a value equal to SQLITE_OK, indicating that the SQL statement has been prepared successfully without syntax problems. Sqlite3_bind_text () Most binding functions take only three arguments:

The first argument is of type SQlite3_STMT *.
The second argument refers to the position of the placeholder. The position of the first placeholder is 1, not 0.
The third argument refers to the value to which the placeholder is bound.
The fourth argument refers to the length of the data passed in the third argument. For C strings, you can pass -1 instead of the length of the string.
The fifth argument is an optional function callback, typically used to clean up memory after the statement is executed.

Sqlite_finalize () : sqlite_step() : execute SQL statement, return SQLITE_DONE to finalize sqlite_FINALIZE () : destroy SQlite3_STMT * object

Query data

char *sql = "select id,name,age from t_person;" ; sqlite3_stmt *stmt; if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) { while (sqlite3_step(stmt) == SQLITE_ROW) { int _id = sqlite3_column_int(stmt, 0); char *_name = (char *)sqlite3_column_text(stmt, 1); NSString *name = [NSString stringWithUTF8String:_name]; int _age = sqlite3_column_int(stmt, 2); NSLog(@"id=%i, name=%@, age=%i", _id, name, _age); } } sqlite3_finalize(stmt);Copy the code

Sqlite3_step (): returns SQLITE_ROW to represent iterating over a new record. Sqlite3_column_ *(): Used to get the value of each field. The second parameter is the index of the field, starting from 0.

Add fields in the database later

􏰛 􏰜 􏰝 􏰞 􏰟 􏰠 􏰝 􏰡 􏰋 􏰌 􏰍 􏰎 1. Increase the TABLE field ALTER TABLE TABLE name 􏰡 􏰢 ADD COLUMN 􏰍 􏰎 􏰢 field name The field type 􏰍 􏰎 􏰣 􏰤; 2. 􏰥 􏰦 􏰡 􏰍 􏰎 delete TABLE field ALTER TABLE 􏰡 􏰢 TABLE name 􏰡 􏰢 DROP COLUMN 􏰍 field name The field type 􏰍 􏰎 􏰣 􏰤; 3. 􏰧 􏰨 􏰡 􏰍 􏰎 modify TABLE field ALTER TABLE 􏰡 􏰢 TABLE name 􏰡 􏰢 RENAME the COLUMN 􏰩 􏰍 􏰎 􏰢 old field name TO the new field name 􏰪 􏰍 􏰎 􏰢;

FMDB

FMDB is the SQLite database framework for iOS. FMDB encapsulates SQLite’s C API as an OC
The advantage of FMDB is that it is more object-oriented to use, eliminating a lot of cumbersome, redundant C code. Compared to Apple’s own Core Data framework, it is more lightweight and flexible. Multithread safe database operation method is provided to effectively prevent data chaos.

FMDB has three main classes

FMDatabase: AN FMDatabase object represents a separate SQLite database used to execute SQL statements. FMResultSet: The result set of the query executed using FMDatabase. FMDatabaseQueue: Used to perform multiple queries or updates in multiple threads. It is thread-safe.

FMDB use

Open the database

Create the FMDatabase object FMDatabase *db = [FMDatabase databaseWithPath:path] by specifying the SQLite database file path; if (! [db open]) {NSLog(@” database open failed! ); }

An empty string @”” will be created automatically if it does not exist. An empty database will be created in the temporary directory. When the FMDatabase connection is closed, the database file will be deleted. nil; An in-memory temporary database is created and destroyed when the FMDatabase connection is closed.

Performing updates In FMDB, all operations other than queries are called “updates.” Create, drop, INSERT, update, delete, etc. Perform updates using the executeUpdate: method:

- (BOOL)executeUpdate:(NSString*)sql, ...
- (BOOL)executeUpdateWithFormat:(NSString*)format, ...
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments
Copy the code

The sample

[db executeUpdate:@"UPDATE t_student SET age = ? WHERE name = ?;", @20, @"Jack"]
Copy the code

Execute the query query method

- (FMResultSet *)executeQuery:(NSString*)sql, ...
- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...
- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
Copy the code