Swift iOS SQLite 3 - Түүх

01:52, 3 Наймдугаар сар 2017 дээрх Almas

2017-08-03T01:52:16Z

01:51, 3 Наймдугаар сар 2017 дээрх Almas

2017-08-03T01:51:52Z

01:32, 3 Наймдугаар сар 2017 дээрх Almas

2017-08-03T01:32:20Z

00:08, 3 Наймдугаар сар 2017 дээрх Almas

2017-08-03T00:08:44Z

Almas: Шинэ хуудас: 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…

2017-08-03T00:06:23Z

Шинэ хуудас: 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…

Шинэ хуудас

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>

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

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

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

@@ 148-р мөр: / 148-р мөр: @@
 ==== 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
 [[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]

@@ 144-р мөр: / 144-р мөр: @@
@@ 219-р мөр: / 149-р мөр: @@
 - 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
 [[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]

@@ 22-р мөр: / 22-р мөр: @@
 <pre>
-let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
+let DBpath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true).appendingPathComponent("mainDB.sqlite")
-    // open database
+var db: OpaquePointer? = nil
-−
-    var db: OpaquePointer?
+if (sqlite3_open(DBpath.path, &db) != SQLITE_OK) {
-    if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
+    NSLog("Error!!! Can't open database")
-        print("error opening database")
+} else {
+     NSLog("Database OK :)")
 </pre>
@@ 217-р мөр: / 217-р мөр: @@
 ==== 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)
 [[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]

@@ 4-р мөр: / 4-р мөр: @@
 <pre>
 import SQLite3
 </pre>
-    In earlier versions of Xcode, you can:
+In earlier versions of Xcode, you can:
 <pre>
@@ 16-р мөр: / 17-р мөр: @@
 </pre>
 . Add the libsqlite3.tbd (or for even older versions, the libsqlite3.dylib) to your project.
 === Create/open database. ===
@@ 63-р мөр: / 64-р мөр: @@
 </pre>
-    Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:
+Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:
 <pre>
@@ 141-р мөр: / 142-р мөр: @@
      db = nil
 </pre>
@@ 217-р мөр: / 214-р мөр: @@
 updateDatabase(dbCommand)
 </pre>
 [[Ангилал:iOS]][[Ангилал:Xcode]][[Ангилал:Swift]][[Ангилал:SQLite]][[Ангилал:Software development]]