I have different problems implementing SQLite in Xcode for iOS app.
I use: Xcode 16 Swift 6.0 (SwiftUI, Foundation, SQLite3) iOS 18.0
I have SQLite.sqlite file with data, I need to reed only data from database. It can happen later that I will need to implement most famous element.
Is there any useful example how to connect, get all data, and pass to view. with search option.
What is solution here?
I have next code:
DataModel.swift
struct DataModel: Identifiable {
let value1: Int
let value2: Int
let value3: String
let value4: String
let value5: Int
}
DBManager.swift
import Foundation
import SQLite3
class DBManager {
var db: OpaquePointer?
let dbName: String = "MyDB.sqlite"
init() {
openDatabase()
closeDatabase()
fetchAllData()
}
func openDatabase() {
// let fileURL = try! FileManager.default
// .url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
// .appendingPathComponent(dbName)
// let fileURL = URL(fileURLWithPath: NSTemporaryDirectory()).appendingPathComponent(dbName)
let dbFile = Bundle.main.url(forResource: dbName, withExtension: nil)!
if sqlite3_open(dbFile.path, &db) != SQLITE_OK {
print("Error opening database")
} else {
print("Database opened successfully at \(dbFile.path)")
}
}
func fetchAllData() -> [DataModel] {
var result: [DataModel] = []
let queryStatementString = "SELECT * FROM tableOfMyDB;"
var queryStatement: OpaquePointer?
if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
while sqlite3_step(queryStatement) == SQLITE_ROW {
let value1 = sqlite3_column_int(queryStatement, 0)
let value2 = sqlite3_column_int(queryStatement, 1)
let value3 = String(cString: sqlite3_column_text(queryStatement, 2))
let value4 = String(cString: sqlite3_column_text(queryStatement, 3))
let value5 = sqlite3_column_int(queryStatement, 4)
result.append(DataModel(value1: Int(value1), value2: Int(value2), value3: value3, value4: value4, value5: Int(value5)))
}
}else {
print("Error preparing query statement")
}
sqlite3_finalize(queryStatement)
return result
}
func closeDatabase() {
sqlite3_close(db)
}
}
ViewModel.swift
import Foundation
import Combine
class DataViewModel: ObservableObject {
@Published var data: [DataModel] = []
@Published var searchText = ""
private var dbManager = DBManager()
init() {
loadData()
}
func loadData() {
data = dbManager.fetchAllData()
}
var filteredData: [DataModel] {
if searchText.isEmpty {
return data
} else {
return data.filter { $0.value4.contains(searchText) || $0.value3.contains(searchText) }
}
}
}
ContentView.swift
import SwiftUI
struct ContentView: View {
@ObservedObject var viewModel = DataViewModel()
var body: some View {
VStack {
TextField("Search...", text: $viewModel.searchText)
.textFieldStyle(RoundedBorderTextFieldStyle())
.padding()
List(viewModel.filteredData) { item in
VStack(alignment: .leading) {
Text("Name: \(item.value4)")
Text("Guide: \(item.value3)")
}
}
}
.navigationTitle("Data List")
}
}
#Preview {
ContentView()
}
Is there any solution for above code.
After I get answers I have next working solution:
DataModel.swift
struct DataModel: Identifiable {
let value1: Int
let value2: Int
let value3: String
let value4: String
let value5: Int
}
DBManager.swift
import Foundation
import SQLite3
class DBManager {
var db: OpaquePointer?
let dbName: String = "MyDB.sqlite"
init() {
openDatabase()
fetchAllData()
}
func openDatabase() {
let dbFile = Bundle.main.url(forResource: dbName, withExtension: nil)!
if sqlite3_open(dbFile.path, &db) != SQLITE_OK {
print("Error opening database")
} else {
print("Database opened successfully at \(dbFile.path)")
}
}
@discardableResult
func fetchAllData() -> [DataModel] {
var result: [DataModel] = []
let queryStatementString = "SELECT * FROM TableOfMyDB;"
var queryStatement: OpaquePointer?
if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
while sqlite3_step(queryStatement) == SQLITE_ROW {
let value1 = sqlite3_column_int(queryStatement, 0)
let value2 = sqlite3_column_int(queryStatement, 1)
let value3 = String(cString: sqlite3_column_text(queryStatement, 2))
let value4 = String(cString: sqlite3_column_text(queryStatement, 3))
let value5 = sqlite3_column_int(queryStatement, 4)
result.append(DataModel(value1: Int(value1), value2: Int(value2), value3: value3, value4: value4, value5: Int(value5)))
}
}else {
print("Error preparing query statement")
}
sqlite3_finalize(queryStatement)
return result
}
func closeDatabase() {
sqlite3_close(db)
}
deinit {
closeDatabase()
}
}
ViewModel.swift
import Foundation
import Combine
class DataViewModel: ObservableObject {
@Published var data: [DataModel] = []
@Published var searchText = ""
private var dbManager = DBManager()
init() {
loadData()
}
func loadData() {
data = dbManager.fetchAllData()
}
var filteredData: [DataModel] {
if searchText.isEmpty {
return data
} else {
return data.filter { $0.value4(searchText) || String($0.value3).contains(searchText) }
}
}
}
ContentView.swift
import Foundation
import SwiftUI
struct ContentView: View {
@ObservedObject var viewModel = DataViewModel()
var body: some View {
NavigationStack {
List(viewModel.filteredData) { item in
let vValue2 = String(item.value2)
let vValue3 = item.item.value3
let vValue4 = item.item.value4
let vValue5 = item.item.value5
HStack {
VStack (alignment: .leading) {
if item.id_no > 10 {
Text(vValue2)
.font(.headline)
}
Text(vValue3)
.font(.subheadline)
}
Spacer()
HStack {
if vValue5 == 1 {
Text(vValue4)
.fontWeight(.heavy)
.fontDesign(.monospaced)
.foregroundStyle(Color.someColor) // change someColor to color of your choice
.frame(width: 50, height: 40, alignment: .leading)
.padding(.leading, 10)
} else {
Text(vValue4)
.fontWeight(.heavy)
.fontDesign(.monospaced)
.foregroundStyle(.primary)
.frame(width: 50, height: 40, alignment: .leading)
.padding(.leading, 20)
}
}
}
.listRowBackground(vValue5 == 1 ? Color.someColor.opacity(0.50) : Color(UIColor.systemBackground))
}
.navigationBarTitle("Data List")
.searchable(text: $viewModel.searchText, placement: .automatic, prompt: "Search...")
}
}
}
#Preview {
ContentView()
}
In DBManager.swift instead of this:
init() {
openDatabase()
closeDatabase()
fetchAllData()
}
Use this:
init() {
openDatabase()
fetchAllData()
}
deinit {
closeDatabase()
}
It leaves the database connection open until the object (DBManager) goes out of memory. I also recommend some protection against race conditions via actor isolation, NSLock, etc. Also, mark the class with final if you aren't going to inherit from it. Also (ok, I'm going to stop this time), you can mark your class ~Copyable and make the close database function consuming, but with your implementation, it will require quite a bit of work, so you don't need to do it.