Data Persistency with SQLite.Swift

Use SQLite.Swift on Android and iOS with zero code changes

Introduction

SCADE provides support of all necessary APIs, including Swift Foundation und linking of C libraries that are necessary for running SQLite.Swift:

All your pure SQLite.swift code compiles and runs on Android without modifications

📘

Download Sourcecode

Please see the UgSqliteSwift project for the source code created in this chapter. See Installing examples for information on how to download the examples. The URL is https://github.com/scadedoc/UgExamples/tree/master/UgSwissClockDemo

Add SQLite libraries and modulemap file

The use of SQLite and SQLite.Swift is straightforward and easy:

1. Setup directory structure

Add the following directory structure below <project_root>

./lib/android/x86
./lib/android/armeabi-v7a
./lib/include

2. Copy files

👍

File location

Please find all files in Examples section on GitHub here https://github.com/scadedoc/UgExamples/tree/master/UgSqlLiteSwift/lib/android

  • Copy to x86/libsqlite3.so to ./lib/android/x86
  • Copy to armeabi-v7a/libsqlite3.so to ./lib/android/armeabi-v7a
  • Copy header file sqlite3.h to ./lib/include
  • Copy module map file module.modulemap to ./lib/include
module SQLite3 [system] [extern_c] {
    header "sqlite3.h"
    link "sqlite3"
    export *
}

Your directory should now look like this

Add dependency for SQLite.Swift

Open your build file and add the dependency

Your build.file should look similar to this now:

Replace main.page.swift with sample code

import ScadeKit
import SQLite

class MainPageAdapter: SCDLatticePageAdapter {

    // page adapter initialization
    override func load(_ path: String) {        
        super.load(path)
        
        do {
            let db = try Connection(NSHomeDirectory() + "/data.db")
    
            let users = Table("users")
            let id = Expression<Int64>("id")
            let name = Expression<String?>("name")
            let email = Expression<String>("email")
            
            _ = try?  w w                db.run(users.create { t in
                t.column(id, primaryKey: true)
                t.column(name)
                t.column(email, unique: true)
            })
            // CREATE TABLE "users" (
            //     "id" INTEGER PRIMARY KEY NOT NULL,
            //     "name" TEXT,
            //     "email" TEXT NOT NULL UNIQUE
            // )
            
            let insert = users.insert(name <- "Alice", email <- "[email protected]")
            let rowid = try db.run(insert)
            // INSERT INTO "users" ("name", "email") VALUES ('Alice', '[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]
            }
            // SELECT * FROM "users"
            
            let alice = users.filter(id == rowid)
            
            try db.run(alice.update(email <- email.replace("mac.com", with: "me.com")))
            // UPDATE "users" SET "email" = replace("email", 'mac.com', 'me.com')
            // WHERE ("id" = 1)
            
            try db.run(alice.delete())
            // DELETE FROM "users" WHERE ("id" = 1)
            
            try db.scalar(users.count) // 0
            // SELECT count(*) FROM "users"
            
            }
        catch {
            print("ERROR: \(error)")
        }
        
    }
}

Run the project

You should see the following output in the console.