Basic use of Swift learning course

  • 2020-06-15 10:21:13
  • OfStack

preface

In our daily development, we often encounter the situation that users disconnect the Internet or the network is slow, so that users will display blank pages when they enter the page on 1. So how to avoid the embarrassment of showing blank pages without the network? The answer is: first, cache 1 part of the data when the network is good, so that the next time the network is bad, at least the user can see the content cached before, thus improving the user experience of APP.

SQLite is a scheme for us to implement local data cache. SQLite has the following advantages: iOS is embedded with SQLite; Verified by time; Open source; Cross-platform.
OK, without further ado, let's get into the SQLite experience. Of course, we have to do a little preparatory work before we start. After all, we don't fight wars without preparation.

The preparatory work

Creating alternate data

Import SQLite3: import SQLite3 Create an Goods class to represent the type of data stored in the database Create 1 array of type Goods Declare 1 global variable for dbPath and db, and 1 function for libraryDirectory path (how to select the database path)

The code is as follows:


class Goods {
 let name: String!
 let weight: Int!
 var price: Double!
 
 init(name: String, weight: Int, price: Double) {
  self.name = name
  self.weight = weight
  self.price = price
 }
}

let goods = Goods(name: "computer", weight: 10, price: 2000.0)
var goodArr = [Goods]()
var dbPath = ""
var db: OpaquePointer?

func createData() {
 for index in 0...4 {
  let goods = Goods(name: "computer" + "\(index)", weight: index * 10, price: 20.0)
  goodArr.append(goods)
 }
}

func fetchLibraryPath() {
 if let libraryPathString = NSSearchPathForDirectoriesInDomains(.libraryDirectory, .userDomainMask, true).first {
  let pathURL = URL(fileURLWithPath: libraryPathString).appendingPathComponent("goods.sqlite")
  dbPath = pathURL.path
 }
}

Create and connect to the database


func openDatabase() -> OpaquePointer? {
 var db: OpaquePointer?
 if sqlite3_open(dbPath, &db) == SQLITE_OK {
  resultLabel.text = " Successfully open the database, path: \(dbPath)"
  return db
 } else {
  resultLabel.text = " Failed to open database "
  return nil
 }
}

As we can see from the above code, we first declare an optional value of OpaquePointer type db, and then call the sqlite3_open() method, which opens the database if it has been created before, or creates one if it has not. If the method call is successful, it returns an OpaquePointer assignment to the db you passed in.

SQLITE_OK is 1 constant defined in the SQLite library, which represents the 0 of 1 Int32. Most SQLite functions return an Int32 value, such as SQLITE_ROW (100), SQLITE_DONE (101), etc. You can see the list here.

Now you can open or create a database by calling db = openDatabase(). Normally you will see the database opened successfully, with the output of xxx/ xxx.sqlite.

Now that we have successfully created a database named ES69en.sqlite, all we need to do is create a table.

Create a table

code


func createTable() {
 let createTableString = """
       CREATE TABLE Computer(
       Id INT PRIMARY KEY NOT NULL,
       Name CHAR(255),
       Weight Int,
       Price Float);
       """
 var createTableStatement: OpaquePointer?
 //  The first 1 step 
 if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
  //  The first 2 step 
  if sqlite3_step(createTableStatement) == SQLITE_DONE {
   resultLabel.text = " Table created successfully "
  } else {
   resultLabel.text = " Table was not created successfully "
  }
 } else {
   
 }
 // The first 3 step 
 sqlite3_finalize(createTableStatement)
}

Code instructions

First explain createTableString under 1: Create a table named Computer, Id is primary key and not empty, Name is no more than 255 characters, Weight is of type Int and Price is of type Float.
Then I create an OpaquePointer? A variable of type is used for the following function: sqlite3_prepare_v2().

Step 1: This function compiles createTableString to byte code (byte code) and returns 1 status code. Successful execution of this function indicates that database is ready to execute any SQL statement (that is, the string of the created SQL), which executes sqlite3_step(). Step 2: sqlite3_step() is used to perform the compiled statement handle(createTableStatement) and return 1 status code. Step 3: After each operation you must call sqlite3_finalize() to delete your statement to avoid resource leak. Note: once 1 statement is finalized, you should not use it again.

Insert 1 piece of data

code


func insertOneData() {
 let insertRowString = "INSERT INTO Computer (Id, Name, Weight, Price) VALUES (?, ?, ?, ?);"
 var insertStatement: OpaquePointer?
 // The first 1 step 
 if sqlite3_prepare_v2(db, insertRowString, -1, &insertStatement, nil) == SQLITE_OK {
   let id: Int32 = 1
   // The first 2 step 
   sqlite3_bind_int(insertStatement, 1, id)
   
   sqlite3_bind_text(insertStatement, 2, goods.name, -1, nil)
   
   sqlite3_bind_int(insertStatement, 3, Int32(goods.weight))
   
   sqlite3_bind_double(insertStatement, 4, goods.price)
   // The first 3 step 
   if sqlite3_step(insertStatement) == SQLITE_DONE {
    resultLabel.text = " Insert data successfully "
   } else {
    resultLabel.text = " Failed to insert data "
   }
 } else {
  
 }
 // The first 4 step 
 sqlite3_finalize(insertStatement)
}

Code instructions

insertRowString the & # 63; It is a placeholder that tells the compiler to insert the value when the statement is actually executed. Step 2: sqlite3_bind_int() identifies a value of type Int to which the first argument is your statement(i.e. insertStatement) and the second argument is ? Is in your statement(note that the value is non-zero), in this case 1, and the third parameter is the value you want to bind to. The sqlite3_bind_text() function means that you are binding to an text (usually used for long strings). This function takes two more arguments than sqlite3_bind_int(). The fourth argument means the number of bytes of text, usually -1, and the fifth argument is an closure callback, which is called after processing string. Step 3 Step 4 is the same as above

Insert multiple data

code


func insertMutipleData() {
  let insertRowString = "INSERT INTO Computer (Id, Name, Weight, Price) VALUES (?, ?, ?, ?);"
  var insertStatement: OpaquePointer?
  // The first 1 step 
  if sqlite3_prepare_v2(db, insertRowString, -1, &insertStatement, nil) == SQLITE_OK {
    for (index, good) in goodArr.enumerated() {
      let id: Int32 = Int32(index + 1)
      // The first 2 step 
      sqlite3_bind_int(insertStatement, 1, id)
      
      sqlite3_bind_text(insertStatement, 2, good.name, -1, nil)
      
      sqlite3_bind_int(insertStatement, 3, Int32(good.weight))
      
      sqlite3_bind_double(insertStatement, 4, good.price)
      // The first 3 step 
      if sqlite3_step(insertStatement) == SQLITE_DONE {
        resultLabel.text = " Insert data successfully "
      } else {
        resultLabel.text = " Failed to insert data "
      }
      // The first 4 step 
      sqlite3_reset(insertStatement)
    }
  } else {
    
  }
  // The first 5 step 
  sqlite3_finalize(insertStatement)
}

Code instructions

insertRowString ditto. Step 4: Call the sqlite3_reset() function to execute insertStatement again on the next loop Step 1, step 2, Step 3 and Step 5 are the same as above.

Update the data

code


func updateData() {
  let updateString = "UPDATE Computer SET Name = 'changeComputer' WHERE Id = 2;"
  var updateStatement: OpaquePointer?
  // The first 1 step 
  if sqlite3_prepare_v2(db, updateString, -1, &updateStatement, nil) == SQLITE_OK {
    // The first 2 step 
    if sqlite3_step(updateStatement) == SQLITE_DONE {
      resultLabel.text = " The update is successful "
    } else {
      
    }
  }
  // The first 3 step 
  sqlite3_finalize(updateStatement)
}

Code instructions

updateString: Changes the Name field for Id==2 data to changeComputer. sqlite3_prepare_v2() : ready, sqlite3_step() : Perform update statement, sqlite3_finalize() : end.

Delete the data

code


func deleteData() {
  let deleteString = "DELETE FROM Computer WHERE Id = 2;"
  var deleteStatement: OpaquePointer?
  // The first 1 step 
  if sqlite3_prepare_v2(db, deleteString, -1, &deleteStatement, nil) == SQLITE_OK {
    // The first 2 step 
    if sqlite3_step(deleteStatement) == SQLITE_DONE {
      resultLabel.text = " Delete the success "
    }
  } else {
    
  }
  // The first 3 step 
  sqlite3_finalize(deleteStatement)
}

Code instructions

deleteString: Delete the data in table Id==2. sqlite3_prepare_v2() : ready, sqlite3_step() : Execute delete statement, sqlite3_finalize() : end.

Query 1 piece of data

code


func queryOneData() {
  let queryString = "SELECT * FROM Computer WHERE Id == 2;"
  var queryStatement: OpaquePointer?
  // The first 1 step 
  if sqlite3_prepare_v2(db, queryString, -1, &queryStatement, nil) == SQLITE_OK {
    // The first 2 step 
    if sqlite3_step(queryStatement) == SQLITE_ROW {
      // The first 3 step 
      let id = sqlite3_column_int(queryStatement, 0)
      
      let queryResultName = sqlite3_column_text(queryStatement, 1)
      let name = String(cString: queryResultName!)
      let weight = sqlite3_column_int(queryStatement, 2)
      let price = sqlite3_column_double(queryStatement, 3)
      
      
      resultLabel.text = "id: \(id), name: \(name), weight: \(weight), price: \(price)"
    } else {
      resultLabel.text = "error"
    }
  }
  // The first 4 step 
  sqlite3_finalize(queryStatement)
}

Code instructions

queryString: Find all Id == 2 data in Computer table. Step 2: Note that status code is SQLITE_ROW. If true is true, then the data you are querying exists in the table. Step 3: The sqlite3_column_int() function fetches the data according to the number of columns, the first parameter is statement, and the second parameter is the column of the field (Id is the first column in the table, calculated from 0). sqlite3_column_text() is a little more complicated by the fact that he needs to convert the type via String(cString: queryResultName!). . Steps 1 and 4 are the same as above

Query multiple data

code


func queryAllData() {
  let queryString = "SELECT * FROM Computer;"
  var queryStatement: OpaquePointer?
  // The first 1 step 
  if sqlite3_prepare_v2(db, queryString, -1, &queryStatement, nil) == SQLITE_OK {
    // The first 2 step 
    while(sqlite3_step(queryStatement) == SQLITE_ROW) {
      // The first 3 step 
      let id = sqlite3_column_int(queryStatement, 0)
      
      let queryResultName = sqlite3_column_text(queryStatement, 1)
      let name = String(cString: queryResultName!)
      let weight = sqlite3_column_int(queryStatement, 2)
      let price = sqlite3_column_double(queryStatement, 3)
      
      
      resultLabel.text = "id: \(id), name: \(name), weight: \(weight), price: \(price)"
    }
  }
  // The first 4 step 
  sqlite3_finalize(queryStatement)
}

Code instructions

Step 2: Here is the while loop, which ends by returning SQLITE_DONE status code when the last row of the query is reached. Step 1 step 3 Step 4 same as above.

summary

From the above we can summarize the general flow of executing 1 statement :sqlite3_prepare_v2() : ready, sqlite3_step() : execute statement, sqlite3_finalize() : end. Ok, here the basic operation of SQLite3 is finished. In the next part, we will learn about the advanced use of SQLite. Bye ~


Related articles: