IOS data storage (1) DB technical framework comparison
IOS Data Store Realm. Swift Usage
IOS Data store (3) Realm. Swift (2
IOS Data Store (5) WCDB (2) WCDB.swift
1. Database Overview
- At present, mobile database schemes can be divided into two categories according to their implementation:
- Relational database, representing CoreData, FMDB and so on.
- Key-value database, which represents Realm, LevelDB, and RocksDB.
- CoreData
It is apple’s built-in framework, and Xcode deep integration, can be very easy to ORM; However, it is difficult to master due to its high cost of hands-on learning. Stability is also worrying, it is easy to crash; Multithreaded support is also weak.
- FMDB
It is based on SQLite packaging and is easy to understand for developers with SQLite and ObjC foundation. On the downside, FMDB simply encapsulates SQLite’s C interface into ObjC interface without much other optimization, called Glue Code. Use process needs to use a lot of code splicing SQL, assemble Object, not convenient.
- Realm
Because of its advantages of encapsulation and optimization in various platforms, it is more popular with mobile developers. For iOS developers, the key-value implementation is straightforward and can be used in the same way as NSDictionary. And ORM is thorough, eliminating the process of assembling objects. But it’s very intrusive, and Realm requires classes to inherit from RLMObject’s base class. For single-inherited ObjC, this means that it can no longer inherit from other custom subclasses. At the same time, the key-value database is weak for more complex query scenarios.
- Visible, each program has its unique advantages and disadvantages, there is no best, only the most suitable.
- In the selection, FMDB SQL splicing, SQL injection is difficult to prevent; CoreData is convenient for ORM, but it has high learning cost, worrying stability, and multithreading. In addition, I would like to use SQLite based on C language should not be many people; In addition to the above relational databases, there are other key-value databases, such as Realm, which I used. This is not too difficult for ObjC developers to get started with, but the disadvantages are obvious. Inheritance is required and invasive, which is not ideal for single-inheritance OC, and collection types are not fully supported. Complex queries are also weak.
- Here are the ones used in wechatWCDBDatabase, which meets the following requirements:
- High efficiency; Add, delete, change and check efficiently is the most basic requirement of database. In addition, we also hope to support multiple threads to operate the database with high concurrency, so as to cope with the scene of wechat frequently sending and receiving messages.
- Easy to use; This is the principle of wechat open source as well as WCDB. SQLite is not an easy component to use: to complete a query, we often need to write a lot of glue code to concatenate strings and assemble objects. This code is verbose and error-prone, and we want the components to do this uniformly.
- Complete; Database operations are a complex scenario, and we expect database components to cover all scenarios completely. This includes database corruption, monitoring statistics, complex queries, backinjection, etc.
1.1 WCDB – iOS/Mac
WCDB-iOS/Mac (WCDB) (github.com/Tencent/wcd…) WCDB is an Objective-C++ database component based on SQLite encapsulation, which provides the following functions:
- Convenient ORM and CRUD interfaces: With WCDB, developers can easily define database tables and indexes without having to write a bunch of glue code to assemble objects.
- WINQ (WCDB language integrated query) : Through WINQ, developers do not need to concatenate strings, you can complete SQL conditions, sorting, filtering and so on.
- Multi-threaded high concurrency: Basic add, delete, check and change interface support multi-threaded access, developers do not need to worry about thread safety issues.
- Read and write operations between threads can be executed concurrently.
- Write and write operations are performed sequentially and have performance improvements based on SQLite source optimization. Please refer to another article “wechat iOS SQLite source optimization practice”
- Damage repair: Database damage is always a problem, WCDB built in our own repair tool WCDBRepair. Also refer to another article “wechat SQLite database repair practice”
- Statistical analysis: WCDB provides an interface to directly obtain the EXECUTION time of SQL, which can be used to monitor performance.
- Reverse injection: THE WCDB framework layer prevents SQL injection to prevent malicious information from compromising user data.
WCDB covers most scenarios of database use, and has been verified by a large number of wechat users, and will continue to add new capabilities.
For details on how to use WCDB, please refer to these two blogs:
- WCDB OC use
- WCDB. Swift use
2. Database Realm, WCDB, SQLite performance comparison
2.1 Test data table structure
The Student table.
Fields: ID, NAME, age, Money.
| ID | name | age | money |
|---|---|---|---|
| A primary key | The name | age | Deposits (index) |
Age is a random number ranging from 0 to 100, and Money is a random number ranging from 0 to 10000.
2.2 Test Data
For the following test data, only the specific values after a test are given for reference. After repeated tests, they are basically in this time order.
The tests used here were pure SQLite, not FMDB.
2.2.1 SQLite3
- It takes 1462ms to continuously insert 10,000 pieces of data based on 90,000 pieces of data.
- The index has been created. However, if you are searching for a field with a large number of duplicate data, it is not suitable to create an index, and the search speed will be slow because the index node scan speed is slower than the full table scan speed. For example, when I index and then retrieve age, which is often repeated, the query is more than twice as slow as the query without indexing.
- The WAL mode has been set.
- It takes 331ms to perform 10,000 simple queries
- Dispatch 100 blocks to query 10,000 times: 150ms
2.2.2 realm
- It takes 32851ms to continuously insert 10,000 pieces of data based on 90,000 pieces of data.
- Note that Realm seems to have to be inserted through transactions. Single inserts are transactions that are switched on and off once at a time, which takes a lot of time. If 10,000 are inserted in a transaction, it takes 735ms.
- An index has been created.
- The time for 10,000 simple queries is 699ms.
- Dispatch 100 blocks to query 10,000 times: 205ms
2.2.3 WCDB
- It takes 750ms to continuously insert 10,000 pieces of data based on 90,000 pieces of data.
- This is the time when no transaction operation is performed. If a unified transaction operation is performed, the total time is 667ms.
- An index has been created.
- The time required for 10,000 simple queries is 690ms.
- Dispatch 100 blocks to query 10,000 times: 199ms
2.2.4 Comparison of the three
| The test content | Realm | WCDB | SQLite | Use case number |
|---|---|---|---|---|
| Insert 10,000 pieces per piece | 32851ms | 750ms | 1462ms | 90000 + 10000 |
| Loop the query 10,000 times | 699ms | 690ms | 331ms | 100000 |
| 100 block queries for 10,000 times | 205ms | 199ms | 186ms | 100000 |
- Since 10,000 transactions per transaction are too long for a Realm to be shown in the chart, the time spent on each transaction is measured in terms of the time spent on the bulk transaction. In fact, WCDB inserts are better than Realms.
- As a result, it seems that Realm will have to use transactions, where single inserts will perform much worse, but transactions for batch operations will be better. According to the tests in Resources [3], Realm inserts are slower than SQLite, faster than FMDB, and queries are faster than SQLite.
- WCDB, which is also based on SQLite extensions, is surprisingly fast to insert, even an order of magnitude faster than SQLite. The query speed of WCDB is also acceptable, and the result is actually similar to the official result: read operation is roughly equal to FMDB speed, and write operation is much faster than FMDB.
3. Performance comparison of WCDB and FMDB
4. Compare the advantages and disadvantages of database framework
4.1 Advantages and disadvantages of SQLite
advantages
- SQLite is lightweight, has no client or server side, and is a cross-platform relational database.
- SQLite is a single file that can be copied out and used elsewhere.
- There is a sqlite.swift framework that is very useful.
disadvantages
- SQLite does not perform well on concurrent reads and writes. The database can sometimes be monopolized by one read or write operation, which can cause other read or write operations to block or fail.
- Does not support THE SQL92 standard, sometimes the syntax is not strict can also pass, will develop bad habits, resulting in no maintenance.
- Need to write a lot of SQL splicing statements, write a lot of glue code, easy to inject malicious code through SQL.
- Inefficient: SQL is based on strings, which command line enthusiasts love. But for mobile developers based on modern ides, it’s a pain. Strings can not be checked by any compiler, and business development is often in a hot mood, writing hundreds of lines of code, full of joy under Run, only to discover: something is wrong! When you sit down and look at the log and the breakpoint step by step, you find that, oh, SELECT hits SLEECT. Correct, and then wait for compilation to complete, this time has passed more than ten minutes.
4.2 Advantages and disadvantages of FMDB
advantages
- It’s built on SQLite packaging, and for developers with SQLite and ObjC roots,
- Easy to understand, can be directly used;
disadvantages
- FMDB simply encapsulates SQLite’s C interface into ObjC interface without much other optimization, known as Glue Code.
- Use process needs to use a lot of code splicing SQL, assemble Object, not convenient.
- Easy to inject through SQL code.
- Directly expose the string interface, let the business development their own concatenation string, take out the data after assigning to the corresponding Object. This approach is simplistic and crude.
The official documentation
4.3 Advantages and disadvantages of CoreData
advantages
It is apple’s built-in framework, and Xcode deep integration, can be very easy to ORM;
disadvantages
- It is difficult to master due to its high learning cost.
- Stability is also worrying, it is easy to crash; Multithreaded support is also weak.
4.4 Advantages and Disadvantages of Realm
advantages
- Create a table in the Model class. Create a table in the Model class. Create a table in the Model class. Unlike SQLite’s SQL statements (which are still a bit cumbersome to encapsulate using FMDB), Realm is very simple to use on a daily basis, with at least half of SQLite’s code performing some of the same operations on both databases in this test case.
- You can also create relationships between realms’ tables. You can create properties for both one and many.
- Determine the primary key, attribute defaults, indexed fields, and so on for the “table” in the. M method.
- When you modify data, you can simply drop a piece of data in, and the Realm will determine whether it exists based on the primary key, update it if it exists, and add it if it doesn’t.
- Query operations are too simple; one line of code gets an array of query results based on the purpose of the query.
- Supports KVC and KVO.
- Disbursement database encryption.
- Notifications are supported.
- For database changes (table additions, deletions, and structural changes may occur during version iteration), Realm monitors new and removed properties, and then updates the database schema on disk. Realm can configure the database version to determine which properties are being added or removed.
- Realm generally takes up less space on your hard disk than SQLite.
disadvantages
- Realm also has some limitations that you need to consider if they affect.
- The class name contains a maximum of 57 UTF8 characters.
- The attribute name contains a maximum of 63 UTF8 characters.
- The NSData and NSString attributes cannot hold more than 16MB of data, and can be partitioned if large.
- String sorting and case-insensitive queries only support basic Latin Character set, Supplementary Latin Character Set, Extended Latin Character set A, and Extended Latin Character set B (UTF-8 ranges from 0 to 591).
- Creating a new Realm object is required for multithreaded access.
- Realm has no increment properties. [[NSUUID UUID] UUIDString] [NSUUID UUID] UUIDString] [NSUUID UUID] UUIDString [NSUUID UUID] UUIDString [NSUUID UUID] UUIDString [NSUUID UUID] UUIDString [NSUUID UUID]
- Realm supports the following property types: BOOL, BOOL, int, NSInteger, long, long, float, double, NSString, NSDate, NSData, and NSNumber marked by special types. Note that set types are not supported. Only one set RLMArray, If there is an array from the server, then we need to take the data for conversion and storage.
The official documentation
- Realm Framework official documentation
4.5 Advantages and disadvantages of WCDB
advantages
- In practice, WCDB’s code experience was very good, almost half that of SQLite.
- The style is closer to OC’s original style than Realm, and you don’t feel like you’re writing SQL to a database. And its query statement WINQ also write very logical, basic can understand a look, do not even need you to understand SQL statements.
- The whole development process is very smooth, in addition to the configuration of the environment when there is a problem and no reference can only guess to solve their own, the code is basically written in one go perfect operation.
- Through ORM and WINQ, WCDB embodies its advantages in ease of use, making database operation no longer complicated. At the same time, through the chain call, the developer can easily obtain the database operation time and other performance information.
- Ease of use
- One line of code is the principle it adheres to, and most operations require only one line of code.
- Use WINQ statement query, do not have to splice SQL statement and trouble, model binding mapping is in accordance with the provisions of the template to achieve convenient and fast.
-
Efficiency: Performance comparisons have been made above, and WCDB is much more efficient and performance than other frameworks.
-
integrity
- Supports encryption based on SQLCipher
- Full text search
- Supports reverse injection, which prevents the third party from injecting SQL from the input box and performing unexpected malicious operations.
- Users do not need to manually manage the database field version, easy to upgrade automatically.
- Provides database repair tools.
disadvantages
- The most obvious disadvantage is the lack of relevant information
Post a review
The official documentation
- Wcdb. swift uses official documentation
5. To summarize
- Personally recommend the use of wechat WCDB framework, this framework is open source, if there is a need to splice their own SQL statements, to achieve the expansion of SQL statements is also very easy things.
- In the selection, each framework has its own advantages and disadvantages, there is no pros and cons, only suitable for the needs of the project. In fact, for small projects, Sqlite or FMDB can meet the requirements, but if you encounter security problems, you need to repeat many wheels to achieve encryption and other functions. In the use of the above if the direct use of SQL statements, like in Jimu1.0 SQL statements scattered everywhere, the problem is difficult to locate, need to write a lot of repeated splicing SQL statements glue code. And if the SQL statement is written wrong, there will be no error or warning. If there is a bug in the SQL statement, it is difficult to locate it in the later stage of the project.
- FMDB SQL splicing, SQL injection that is difficult to prevent; CoreData is convenient for ORM, but it has high learning cost, worrying stability, and multithreading. In addition, I would like to use SQLite based on C language should not be many people; In addition to the above relational databases, there are other key-value databases, such as Realm, which I used. This is not too difficult for ObjC developers to get started with, but the disadvantages are obvious. Inheritance is required and invasive, which is not ideal for single-inheritance OC, and collection types are not fully supported. Complex queries are also weak.
- WCDB was opened by wechat team on June 9, 2017. Open source has not been around long. May be less relevant information, can only rely on the official documents.
- SQLite3 is more cumbersome to use directly, while FMDB is written in OC, if using the Swift version of the recommended use: SQLite. Swift this is a good framework than using FMDB simple, code introduction is a lot. SQLite. Swift fully encapsulates SQLite, has a full pure SWIFT interface, even if you do not know SQL statements, you can use the database. The author uses the chain programming writing method, so that the database management becomes elegant, readable is also very strong.
- In conclusion, my suggestions are as follows:
- Best option A is to use the WCDB.swift framework.
- Option B is to use SQlite.swift
- Scenario C uses the Realm framework
- Scenario D uses the FMDB framework
6. Simple contrast WCDB. Swift, Realm. Swift, SQLite. The use of the swift
- Simple contrast WCDB. Swift, Realm. Swift, FMDB, SQLite. The use of the swift
6.1 Basic usage of Wcdb. swift
- Swift uses demo
6.1.0 Creating a Model
import Foundation
import WCDBSwift
class Sample: TableCodable {
var identifier: Int? = nil
var description: String? = nil
enum CodingKeys: String.CodingTableKey {
typealias Root = Sample
static let objectRelationalMapping = TableBinding(CodingKeys.self)
case identifier
case description
static var columnConstraintBindings: [CodingKeys: ColumnConstraintBinding]? {
return [
identifier: ColumnConstraintBinding(isPrimary: true),]}}}Copy the code6.1.1 Creating a Database
private lazy var db : Database? = {
1. Create a database
let docPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first! + "/wcdb.db"
let database = Database(withPath: docPath + "/wcdb.db")
return database
}()
Copy the code6.1.2 Creating a Database Table
private func testCreateTable(a) {
guard let db = db else {
return
}
do {
// Create a database table
try db.create(table: TB_Sample, of: Sample.self)}catch {
print(error)
}
}
Copy the code6.1.3 Insertion Operations
private func testInsert(a) {
guard let db = db else {
return
}
do {
// Insert the database
let object = Sample()
object.identifier = 1
object.description = "insert"
try db.insert(objects: object, intoTable: TB_Sample) // The insertion succeeded
try db.insert(objects: object, intoTable: TB_Sample) // Insert failed because primary key identifier = 1 already exists
object.description = "insertOrReplace"
try db.insertOrReplace(objects: object, intoTable: TB_Sample) // Successful insertion, and description is replaced with "insertOrReplace"
} catch {
print(error)
}
}
Copy the code6.1.4 Deleting a vm
private func testDelete(a) {
guard let db = db else {
return
}
do {
// Delete operation
// Delete all rows in sampleTable whose identifier is greater than 1
try db.delete(fromTable: TB_Sample.where: Sample.Properties.identifier > 1)
// Delete all data in sampleTable
try db.delete(fromTable: TB_Sample)}catch {
print(error)
}
}
Copy the code6.1.5 Update Operations
private func testUpdate(a) {
guard let db = db else {
return
}
do {
// Update data
let object = Sample()
object.description = "update"
// Update the description field in the first three rows of sampleTable to "update"
try db.update(table: TB_Sample,
on: Sample.Properties.description,
with: object,
limit: 3)}catch {
print(error)
}
}
Copy the code6.1.6 Query Operations
private func testQuery(a) {
guard let db = db else {
return
}
do {
// Query operation
// Return all data in sampleTable
let allObjects: [Sample] = try db.getObjects(fromTable: TB_Sample)
print(allObjects)
// Return the data for the sampleTable rows whose identifier is less than 5 or greater than 10
let objects: [Sample] = try db.getObjects(fromTable: TB_Sample.where: Sample.Properties.identifier < 5 || Sample.Properties.identifier > 10)
print(objects)
// Returns the data for the largest identifier row in sampleTable
// let object: Sample? = try db.getObject(fromTable: TB_Sample,
// orderBy: Sample.Properties.identifier.asOrder(by: .descending))
// Get all the content
let allRows = try db.getRows(fromTable: TB_Sample)
print(allRows[row: 2, column: 0].int32Value) 3 / / output
// Get the second line
let secondRow = try db.getRow(fromTable: TB_Sample, offset: 1)
print(secondRow[0].int32Value) 2 / / output
// Get the description column
let descriptionColumn = try db.getColumn(on: Sample.Properties.description, fromTable: TB_Sample)
print(descriptionColumn) // Output "sample1", "sample1", "sample1", "sample2", "sample2"
// Get the value of the non-duplicate description column
let distinctDescriptionColumn = try db.getDistinctColumn(on: Sample.Properties.description, fromTable: TB_Sample)
print(distinctDescriptionColumn) // output "sample1", "sample2"
// Get the value of the description column in the second row
let value = try db.getValue(on: Sample.Properties.description, fromTable: TB_Sample, offset: 1)
print(value.stringValue) / / output "sample1"
// Get the maximum value of identifier
let maxIdentifier = try db.getValue(on: Sample.Properties.identifier.max(), fromTable: TB_Sample)
print(maxIdentifier.stringValue)
// Get the non-duplicate value of description
let distinctDescription = try db.getDistinctValue(on: Sample.Properties.description, fromTable: TB_Sample)
print(distinctDescription.stringValue) / / output "sample1"
} catch {
print(error)
}
}
Copy the code6.2 Realm.swift Basic Usage
6.2.1 Creating a Database
import RealmSwift
static let sharedInstance = try! Realm(a)static func initRealm(a) {
var config = Realm.Configuration(a)// Use the default directory, but you can use the username instead of the default filenameconfig.fileURL = config.fileURL! .deletingLastPathComponent().appendingPathComponent("Bilibili.realm")
// Get the parent directory of our Realm file
letfolderPath = config.fileURL! .deletingLastPathComponent().path// Unprotect this directory
try! FileManager.default.setAttributes([FileAttributeKey.protectionKey: FileProtectionType.none], ofItemAtPath: folderPath)
/ / create a Realm
Realm.Configuration.defaultConfiguration = config
}
Copy the code6.2.2 Creating a Database Table
static func add<T: Object>(_ object: T) {
try! sharedInstance.write {
sharedInstance.add(object)
}
}
Copy the code6.2.3 Insertion Operations
// add a piece of data
static func addCanUpdate<T: Object>(_ object: T) {
try! sharedInstance.write {
sharedInstance.add(object, update: true)}}Copy the code- Add a set of data
static func addListData<T: Object>(_ objects: [T]) {
autoreleasepool {
// Get the Realm and table instances in this thread
let realm = try! Realm(a)// Batch write operation
realm.beginWrite()
The add method supports update, and the item object must have a primary key
for item in objects {
realm.add(item, update: true)}// Commit a write transaction to ensure data is available in other threads
try! realm.commitWrite()
}
}
Copy the code- A single background process writes a set of data
static func addListDataAsync<T: Object>(_ objects: [T]) {
let queue = DispatchQueue.global(qos: DispatchQoS.QoSClass.default)
// Import many items in a background thread
queue.async {
// Why add the following keywords, see the comments for Realm file deletion
autoreleasepool {
// Get the Realm and table instances in this thread
let realm = try! Realm(a)// Batch write operation
realm.beginWrite()
The add method supports update, and the item object must have a primary key
for item in objects {
realm.add(item, update: true)}// Commit a write transaction to ensure data is available in other threads
try! realm.commitWrite()
}
}
}
Copy the code6.2.4 Deleting a vm
/// Delete some data
static func delete<T: Object>(_ object: T) {
try! sharedInstance.write {
sharedInstance.delete(object)
}
}
Copy the code6.2.5 Update Operations
- Update operation is the same as add operation,
// add a piece of data
static func addCanUpdate<T: Object>(_ object: T) {
try! sharedInstance.write {
sharedInstance.add(object, update: true)}}static func addListData<T: Object>(_ objects: [T]) {
autoreleasepool {
// Get the Realm and table instances in this thread
let realm = try! Realm(a)// Batch write operation
realm.beginWrite()
The add method supports update, and the item object must have a primary key
for item in objects {
realm.add(item, update: true)}// Commit a write transaction to ensure data is available in other threads
try! realm.commitWrite()
}
}
Copy the code6.2.6 Query Operations
// query data based on conditions
static func selectByNSPredicate<T: Object>(_: T.Type , predicate: NSPredicate) -> Results<T> {return sharedInstance.objects(T.self).filter(predicate)
}
/// query data based on conditions
static func BGselectByNSPredicate<T: Object>(_: T.Type , predicate: NSPredicate) -> Results<T> {return try! Realm().objects(T.self).filter(predicate)
}
// query all data
static func selectByAll<T: Object>(_: T.Type) -> Results<T> {return sharedInstance.objects(T.self)}// query all data after sorting, keyword and whether ascending order
static func selectScoretByAll<T: Object>(_: T.Type ,key: String, isAscending: Bool) -> Results<T> {return sharedInstance.objects(T.self).sorted(byKeyPath: key, ascending: isAscending)
}
Copy the code6.3 Basic Usage of FMDB
- The usage of FMDB should be familiar and will not be discussed here
6.4 SQLite. Swift Basic usage
- If you are using SQL statements, this framework is recommended.
SQLite. Swift fully encapsulates SQLite, has a full pure SWIFT interface, even if you do not know SQL statements, you can use the database. The author uses the chain programming writing method, so that the database management becomes elegant, readable is also very strong.
- SQLite framework for Swift: sqlite.swift
- Integration:
- Carthage:
github "stephencelis/SQLite.swift"
Copy the code- CocoaPods:
pod 'SQLite.swift'
Copy the code- Swift Package Manager:
dependencies: [
.package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.11.5")]Copy the code6.4.1 Creating a Database
- Here we set the path and name of the database file and initialize a Connection object as parameters. If the file in the path does not exist, it will be created automatically.
import SQLite
let path = NSSearchPathForDirectoriesInDomains(
.documentDirectory, .userDomainMask, true
).first!
let db = try! Connection("\(path)/db.sqlite3")
Copy the code- Initialization method:
- This is only the most simple way, let us see Connection initialization method and parameters can be set: public init (_ the location: SQLite. Connection. The location = default, readonly: Bool = default) throws
- The first parameter Location refers to the Location of the database, in three cases: inMemory the database exists inMemory; Temporary A temporary database that is released after being used. Filename (or path) exists on the hard disk, which is what we used above. The first two will be released after use and will not be saved. The third one can be saved. The first database exists in memory, and the latter two exist on hard disk.
- Readonly Indicates whether the database is read-only and unmodifiable. The default value is false. Read-only is when we copy a database file to our project and only read the data for use, without modifying it.
- Thread safety Settings:
You can’t avoid multithreading with a database, so we have two options for sqLite.swift
db.busyTimeout = 5.0
db.busyHandler({ tries in
if tries >= 5 {
return false
}
return true
})
Copy the code6.4.2 Creating a Database Table
let users = Table("users")
let id = Expression<Int64> ("id")
let name = Expression<String? > ("name")
let email = Expression<String> ("email")
try db.run(users.create { t in
t.column(id, primaryKey: true)
t.column(name)
t.column(email, unique: true)})Copy the codeThis is equivalent to executing SQL:
// CREATE TABLE "users" (
// "id" INTEGER PRIMARY KEY NOT NULL,
// "name" TEXT,
// "email" TEXT NOT NULL UNIQUE
// )
Copy the codeIt can also be created like this:
let users = Table("users")
let id = Expression<Int64> ("id")
let name = Expression<String? > ("name")
let email = Expression<String> ("email")
try db.run(users.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (t) in
t.column(id, primaryKey: true)
t.column(name)
t.column(email, unique: true)}))/* temporary: whether the table is temporary ifNotExists: whether the table is not created, remember to set it to true withoutRowid: whether the rowid is automatically created with increment */
Copy the code6.4.3 Insertion Operations
let insert = users.insert(name <- "Alice", email <- "[email protected]")
if let rowId = try? db.run(insert) {
print("Insert successful:\(rowId)")}else {
print("Insert failed")}// This is equivalent to executing the following SQL
// INSERT INTO "users" ("name", "email") VALUES ('Alice', '[email protected]')
Copy the codeThe roWID is returned after successful insertion
6.4.4 Deleting a vm
let alice = users.filter(id == rowid)
if let count = try? db.run(alice.delete()) {
print("The number of deleted items is:\ [count)")}else {
print("Delete failed")}// This is equivalent to executing the following SQL
// DELETE FROM "users" WHERE ("id" = 1)
Copy the codeThe number of deleted rows int is returned after a successful deletion
6.4.5 Update Operations
let alice = users.filter(id == rowid)
try db.run(alice.update(email <- email.replace("mac.com", with: "me.com")))
// This is equivalent to executing the following SQL
// UPDATE "users" SET "email" = replace("email", 'mac.com', 'me.com')
// WHERE ("id" = 1)
// We can do this directly
if let count = try? db.run(alice. update()) {
print("The number of modified items is:\ [count)")}else {
print("Modification failed")}Copy the code6.4.6 Query Operations
let query = users.filter(name == "Alice").select(email).order(id.desc).limit(l, offset: 1)
for user in try db.prepare(query) {
print("email: \(user[email])")
//email: [email protected]
}
for user in try db.prepare(users) {
print("id: \(user[id]), name: \(user[name]), email: \(user[email])")
// id: 1, name: Optional("Alice"), email: [email protected]
}
// This is equivalent to executing the following SQL
// SELECT * FROM "users"
Copy the codelet stmt = try db.prepare("INSERT INTO users (email) VALUES (?) ")
for email in ["[email protected]"."[email protected]"] {
try stmt.run(email)
}
db.totalChanges / / 3
db.changes / / 1
db.lastInsertRowid / / 3
for row in try db.prepare("SELECT id, email FROM users") {
print("id: \(row[0]), email: \(row[1])")
// id: Optional(2), email: Optional("[email protected]")
// id: Optional(3), email: Optional("[email protected]")
}
try db.scalar("SELECT count(*) FROM users") / / 2
Copy the code6.4.7 Encapsulating code
import UIKit
import SQLite
import SwiftyJSON
let type_column = Expression<Int> ("type")
let time_column = Expression<Int> ("time")
let year_column = Expression<Int> ("year")
let month_column = Expression<Int> ("month")
let week_column = Expression<Int> ("week")
let day_column = Expression<Int> ("day")
let value_column = Expression<Double> ("value")
let tag_column = Expression<String> ("tag")
let detail_column = Expression<String> ("detail")
let id_column = rowid
class SQLiteManager: NSObject {
static let manager = SQLiteManager(a)private var db: Connection?
private var table: Table?
func getDB(a) -> Connection {
if db == nil {
let path = NSSearchPathForDirectoriesInDomains(
.documentDirectory, .userDomainMask, true
).first!
db = try! Connection("\(path)/db.sqlite3") db? .busyTimeout =5.0
}
return db!
}
func getTable(a) -> Table {
if table == nil {
table = Table("records")
try! getDB().run( table! .create(temporary:false, ifNotExists: true, withoutRowid: false, block: { (builder) in
builder.column(type_column)
builder.column(time_column)
builder.column(year_column)
builder.column(month_column)
builder.column(week_column)
builder.column(day_column)
builder.column(value_column)
builder.column(tag_column)
builder.column(detail_column)
})
)
}
return table!
}
/ / to add
func insert(item: JSON) {
let insert = getTable().insert(type_column <- item["type"].intValue, time_column <- item["time"].intValue, value_column <- item["value"].doubleValue, tag_column <- item["tag"].stringValue , detail_column <- item["detail"].stringValue, year_column <- item["year"].intValue, month_column <- item["month"].intValue, week_column <- item["week"].intValue, day_column <- item["day"].intValue)
if let rowId = try? getDB().run(insert) {
print_debug("Insert successful:\(rowId)")}else {
print_debug("Insert failed")}}/ / delete a single
func delete(id: Int64) {
delete(filter: rowid == id)
}
// Delete according to conditions
func delete(filter: Expression<Bool>? = nil) {
var query = getTable()
if let f = filter {
query = query.filter(f)
}
if let count = try? getDB().run(query.delete()) {
print_debug("The number of deleted items is:\ [count)")}else {
print_debug("Delete failed")}}/ / change
func update(id: Int64, item: JSON) {
let update = getTable().filter(rowid == id)
if let count = try? getDB().run(update.update(value_column <- item["value"].doubleValue, tag_column <- item["tag"].stringValue , detail_column <- item["detail"].stringValue)) {
print_debug("The modified result is:\ [count= =1)")}else {
print_debug("Modification failed")}}/ / check
func search(filter: Expression<Bool>? = nil, select: [Expressible] = [rowid, type_column, time_column, value_column, tag_column, detail_column], order: [Expressible] = [time_column.desc], limit: Int? = nil, offset: Int? = nil)- > [Row] {
var query = getTable().select(select).order(order)
if let f = filter {
query = query.filter(f)
}
if let l = limit {
if let o = offset{
query = query.limit(l, offset: o)
}else {
query = query.limit(l)
}
}
let result = try! getDB().prepare(query)
return Array(result)
}
}
Copy the code- It is more convenient to use after encapsulation
let inV = SQLiteManager.manager.search(filter: year_column == year && month_column == month && type_column == 1, select: [value_column.sum]).first? [value_column.sum] ??0.0
// Calculate the sum of all values of year year month month type 1
Copy the code