"Swift iOS SQLite 3"-ны өөр хувилбарууд

Дусал нэвтэрхий толь-с
 
(Хэрэглэгчид дундын нэг хувилбар харагдахгүй)
144-р мөр: 144-р мөр:
  
  
 
=== SQLite Wrapper for iOS in Swift (old Xcode) ===
 
 
Introduction
 
 
This is an easy One-Class Wrapper for most of your SQL needs while working with iOS.
 
Background
 
 
I am working on an app that will be searching and manipulating a large amount of data, so I decided to seed and store the data in a database instead of holding it in memory and praying the app will not crash. I was unable to find anything easy to use, and even the tutorials did not give instructions on actually making anything work. They just gave a few commands, so I decided to write my own wrapper.
 
Using the Code
 
 
To add SQLite functions to your project:
 
 
    Make sure that the libsqlite3.tbd is linked to the project and you can do that in the General Settings of the app
 
    Add a header file to the project and call it BridgingHeader.h and type the following line to include the C API:
 
    Hide  Copy Code
 
 
    #include <sqlite3.h>
 
 
    Go to "Build Settings" and find "Objective-C Bridging Header." Use the search bar to find it quickly. Double-click and type "BridgingHeader.h". If you get "Could not import Objective-C Header," try "my-project-name/BridgingHeader.h"
 
    Go to "Build Phases," "Link Binary With Libraries," and add libsqlite3.tbd
 
    Add in the SQLDataIO.swift to your project (or just create a new one and copy/paste the code in from this sample project)
 
 
Functions:
 
 
    updateDatabase
 
    dbValue
 
    dbInt
 
    nextID
 
    getRows
 
 
To use:
 
Hide  Shrink  Copy Code
 
<pre>
 
var dbCommand: String = ""
 
dbCommand = "CREATE TABLE Family(ID INT PRIMARY KEY NOT NULL,
 
FirstName CHAR(100), LastName CHAR(100), Age INT);"
 
updateDatabase(dbCommand)
 
 
var databaseRows: [[String]] = [[]]
 
var id: Int = 0
 
 
for i in 0...6
 
{
 
    id = nextID("Family")
 
 
    dbCommand = "insert into Family(ID, FirstName, LastName, Age)
 
    values (\(id), '\(firstName[i])', '\(lastName[i])', '\(age[i])')"
 
 
    updateDatabase(dbCommand)
 
}
 
 
dbCommand = "select ID, FirstName, LastName, Age from Family"
 
databaseRows = getRows(dbCommand, numColumns: 4)
 
 
dbCommand = "UPDATE Family SET FirstName = 'Adam' WHERE ID = 1;"
 
updateDatabase(dbCommand)
 
 
dbCommand = "select LastName from Family where ID = 1"
 
let lName: String! = dbValue(dbCommand)
 
 
dbCommand = "select Age from Family where ID = 2"
 
let ageInt: Int = dbInt(dbCommand)
 
 
dbCommand = "select Age from Family where ID = 2"
 
let ageString: String  = dbValue(dbCommand)
 
 
dbCommand = "DELETE FROM Family WHERE ID = 1;"
 
updateDatabase(dbCommand)
 
</pre>
 
  
  
 
==== References: ====
 
==== References: ====
 
- For Swift 2, see previous revision of this answer. (https://stackoverflow.com/revisions/28642293/7)
 
- For Swift 2, see previous revision of this answer. (https://stackoverflow.com/revisions/28642293/7)
 +
 
- FMDB old and great wrapper: http://www.theappguruz.com/blog/use-sqlite-database-swift
 
- FMDB old and great wrapper: http://www.theappguruz.com/blog/use-sqlite-database-swift
 +
 +
- Good Example code with wrapper: https://www.codeproject.com/Tips/1103368/SQLite-Wrapper-for-iOS-in-Swift
  
  
 
[[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]
 
[[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]

09:52, 3 Наймдугаар сар 2017-ий байдлаарх одоогийн засвар

While you should probably use one of the many SQLite wrappers (I prefer FMDB, myself), if you wanted to know how to call the SQLite library yourself, you would:

Configure your Swift project to handle SQLite C calls. If using Xcode 9, you can simply do:


import SQLite3

In earlier versions of Xcode, you can:

1. Create bridging header file to the project. See the Importing Objective-C into Swift section of the Using Swift with Cocoa and Objective-C. This bridging header should import sqlite3.h:

#import <sqlite3.h>

2. Add the libsqlite3.tbd (or for even older versions, the libsqlite3.dylib) to your project.

Create/open database.

let DBpath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true).appendingPathComponent("mainDB.sqlite")

var db: OpaquePointer? = nil
        
if (sqlite3_open(DBpath.path, &db) != SQLITE_OK) {
    NSLog("Error!!! Can't open database")
} else {
    NSLog("Database OK :)")
}

Use sqlite3_exec to perform SQL (e.g. create table).

    if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error creating table: \(errmsg)")
    }


Use sqlite3_prepare_v2 to prepare SQL with ? placeholder to which we'll bind value.

    var statement: OpaquePointer?

    if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing insert: \(errmsg)")
    }

    if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure binding foo: \(errmsg)")
    }

    if sqlite3_step(statement) != SQLITE_DONE {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure inserting foo: \(errmsg)")
    }

Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:

    internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
    internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)


Reset SQL to insert another value. In this example, I'll insert a NULL value:

    if sqlite3_reset(statement) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error resetting prepared statement: \(errmsg)")
    }

    if sqlite3_bind_null(statement, 1) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure binding null: \(errmsg)")
    }

    if sqlite3_step(statement) != SQLITE_DONE {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure inserting null: \(errmsg)")
    }


Finalize prepared statement to recover memory associated with that prepared statement:

    if sqlite3_finalize(statement) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error finalizing prepared statement: \(errmsg)")
    }

    statement = nil


Prepare new statement for selecting values from table and loop through retrieving the values:

    if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing select: \(errmsg)")
    }

    while sqlite3_step(statement) == SQLITE_ROW {
        let id = sqlite3_column_int64(statement, 0)
        print("id = \(id); ", terminator: "")

        if let cString = sqlite3_column_text(statement, 1) {
            let name = String(cString: cString)
            print("name = \(name)")
        } else {
            print("name not found")
        }
    }

    if sqlite3_finalize(statement) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error finalizing prepared statement: \(errmsg)")
    }

    statement = nil


Close database:

    if sqlite3_close(db) != SQLITE_OK {
        print("error closing database")
    }

    db = nil



References:

- For Swift 2, see previous revision of this answer. (https://stackoverflow.com/revisions/28642293/7)

- FMDB old and great wrapper: http://www.theappguruz.com/blog/use-sqlite-database-swift

- Good Example code with wrapper: https://www.codeproject.com/Tips/1103368/SQLite-Wrapper-for-iOS-in-Swift