SQLite.Swift - Querying an existing Db table

Question

I am just now learning about SQLite.swift and was reading the documentation on it. I am trying to query an existing table that I already have but do not know how to do this. In the documentation it shows how to Query a table that is created (shown below https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#selecting-rows)

let users = Table("users")
try db.run(users.create { t in     // CREATE TABLE "users" (
t.column(id, primaryKey: true) //     "id" INTEGER PRIMARY KEY NOT NULL,
t.column(email, unique: true)  //     "email" TEXT UNIQUE NOT NULL,
t.column(name)                 //     "name" TEXT
})  
                           // )
for user in try db.prepare(users) {
print("id: \(user[id]), email: \(user[email]), name: \(user[name])")
// id: 1, email: alice@mac.com, name: Optional("Alice")
}
// SELECT * FROM "users"

I have an existing table that I am able to connect to it but the only way I'm able to get information from it is by doing db.scalar but not sure if this is the correct way of doing it.

let home = FileManager.default.homeDirectoryForCurrentUser
let dbURL = "Desktop/MyPracticeCode/EpubParser/"
let myPath = home.appendingPathComponent(dbURL)
let db = try Connection("\(myPath)/TestTable.sqlite3")
print(db.scalar("SELECT WtName FROM MyTable"))

this prints out the data I need but not sure if this is the correct approach. Is there a way to assign my existing table to a type "Table" and query it just like they did in the example. I searched everywhere online but couldn't find a clear answer. Thanks

score 0 · Accepted Answer · answered Nov 05 '21 at 02:16

I managed to figure out the answer. If anyone ran into this problem or the same question this is how I managed to solve it. Still not sure if it is the correct way. Basically you need to recreate the table with the columns just like in the example but with the name of your columns. Create a table and name it the exact name as the table in your db. Below is how I did it

let id = Expression<Int64>("id")
let Article = Expression<String?>("Article")
let ArticleName = Expression<String?>("ArticleName")
let ImageThumbnail = Expression<String?>("ImageThumbnail")
let WtCoverImage = Expression<String?>("WtCoverImage")
let myTable = Table("MyTable")
try db.run(myTable.create(ifNotExists: true){ t in
  t.column(id,primaryKey: true)
  t.column(Article)
  t.column(ArticleName)
  t.column(ImageThumbnail)
  t.column(WtCoverImage)
 })

This here is what I used:

try db.run(users.create(ifNotExists: true) { t in /* ... */ })
// CREATE TABLE "users" IF NOT EXISTS -- ...

Now I'm able to query it like this

for myData in try db.prepare(myTable){
print("WtCoverImage\(myData[WtCoverImage])")
}

score 0 · Answer 2 · answered Jun 29 '22 at 14:34

class SqliteDB{
    static let shared:SqliteDB = SqliteDB()
    private init(){}
    
    var DB:Connection!
    
    /// path for creat a databased file.
    func createDBPath(){
        do {
            let dbPath = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("chatDB.sqlite").path
            DB = try Connection(dbPath)
            print(dbPath)

        } catch {
            print(error.localizedDescription)
        }
    }
    
    func dbTables_ChatMessage(){
        let tableQuery = """
           CREATE TABLE IF NOT EXISTS message_list (id INTEGER, conversation_id INTEGER UNIQUE, conversation_recipient_id INTEGER, timestamp INTEGER UNIQUE, content_type TEXT, message TEXT, user_id INTEGER, user_name TEXT, group_id INTEGER,isOnline TEXT,room_id INTEGER, local_conversation_id INTEGER ,room_unique_id TEXT,PRIMARY KEY(id AUTOINCREMENT))
          """
        do {
            try DB.run(tableQuery)
        } catch  {
            print(error.localizedDescription)
        }
    }
}

Your answer could be improved by adding more information on what the code does and how it helps the OP. — Tyler2P, Jun 30 '22 at 17:13

SQLite.Swift - Querying an existing Db table

2 Answers2