Swift iOS SQLite 3 хуудсын эх сурвалжийг харах

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: ===

<pre>

import SQLite3

</pre>

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:

<pre>
#import <sqlite3.h>
</pre>

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

=== Create/open database. ===

<pre>
let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
        .appendingPathComponent("test.sqlite")

    // open database

    var db: OpaquePointer?
    if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
        print("error opening database")
    }
</pre>

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

<pre>
    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)")
    }
</pre>


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

<pre>
    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)")
    }
</pre>

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

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


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

<pre>
    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)")
    }
</pre>


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

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

    statement = nil
</pre>


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

<pre>
    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
</pre>


=== Close database: ===

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

    db = nil
</pre>



=== 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: ====
For Swift 2, see previous revision of this answer. (https://stackoverflow.com/revisions/28642293/7)

[[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]