Home > Software engineering >  SQLite.Swift - Querying an existing Db table
SQLite.Swift - Querying an existing Db table

Time:11-08

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: [email protected], 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

CodePudding user response:

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])")
}
  • Related