swift xcode sqlite sections uitableviewsectionheader

How to get Tableview Sections working with SQLite local database in Swift 5

I am new to Xcode and swift and learning. I am trying to build an app with SQLite database that is saved within the app. The database has five fields viz; id, cdcommand, cdtable, cdshortcut, cddescription; My tableview is loading completely fine. Now I am trying to add sections to tableview. I want to divide data into sections from database field named cdtable. I am struggling to get it right.

numberofrowsinSection is broken totally and so is the UITableView, cellForRowAt indexPath: IndexPath). Both shows weird results.

The only thing I managed to get it right somehow is numberOfSections & viewForHeaderInSection.

Can anyone please guide how to get this working? Below is the code I am using...

My code is shared below from 2 files. Thanks in advance.

File1.swift

class Shortcuts {

var id: Int
var cdcommand: String?
var cdtable: String?
var cdshortcut: String?
var cddescription: String?

init(id: Int, cdcommand: String?, cdtable: String?, cdshortcut: String?, cddescription: String?){
    self.id = id
    self.cdcommand = cdcommand
    self.cdtable = cdtable
    self.cdshortcut = cdshortcut
    self.cddescription = cddescription
}
}
class tableNames {
    var cdtable: String?

    init(cdtable: String?){
        self.cdtable = cdtable
    }
}

File2.swift

import Foundation
import UIKit
import SQLite3

class shortcutCustomCell: UITableViewCell {


@IBOutlet weak var commandText: UILabel!
@IBOutlet weak var tableText: UILabel!
@IBOutlet weak var shortcutText: UILabel!
@IBOutlet weak var descText: UITextView!


}

class shortcutsController: UIViewController, UITableViewDelegate, UITableViewDataSource, UISearchBarDelegate {

var db: OpaquePointer?
var shortcutList = [Shortcuts]()
var tablenameList = [tableNames]()

@IBOutlet weak var tableViewShortcuts: UITableView!
@IBOutlet weak var searchBar: UISearchBar!


@IBAction func unwindToPreviousViewController(_ sender: UIBarButtonItem) {
    self.navigationController?.popViewController(animated: true)
}

override func viewDidLoad() {
    super.viewDidLoad()

    // Do any additional setup after loading the view.

    let appearance = UINavigationBarAppearance()
    appearance.titleTextAttributes = [.foregroundColor: UIColor.white]
    appearance.largeTitleTextAttributes = [.foregroundColor: UIColor.white]
    appearance.backgroundColor = UIColor(red:0.79, green:0.37, blue:0.31, alpha:1.00)
    navigationItem.standardAppearance = appearance
    navigationItem.scrollEdgeAppearance = appearance

    self.navigationController?.interactivePopGestureRecognizer?.isEnabled = true
    self.navigationController?.interactivePopGestureRecognizer?.delegate = nil



    //Search Begin

    searchBar.delegate = self
    searchBar.searchTextField.backgroundColor = .white
    searchBar.barTintColor = UIColor.darkGray//(red: 0.79, green: 0.37, blue: 0.31, alpha: 1.00)
    searchBar.searchTextField.textColor = .darkGray

    //Search End

    //SQLite Connection Begin

    let fileMgr = FileManager.default
    let dbPath = URL(fileURLWithPath: Bundle.main.resourcePath ?? "").appendingPathComponent("database.db")
    let pathString = dbPath.path

    let success = fileMgr.fileExists(atPath: pathString)

    if !success {
        print("Cannot locate database file '\(dbPath)'.")
    }
    if !(sqlite3_open(dbPath.absoluteString, &db) == SQLITE_OK) {
        print("An error has occured.")
    }

    readValues()
    queryAlltableNames()

    //SQLite Connection End

}

func readValues(){
    shortcutList.removeAll()

    let queryString = "SELECT * FROM main ORDER BY cdtable, cdcommand ASC"

    var stmt:OpaquePointer?

    if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing insert: \(errmsg)")
        return
    }

    while(sqlite3_step(stmt) == SQLITE_ROW){
        let id = sqlite3_column_int(stmt, 0)
        let cdcommand = String(cString: sqlite3_column_text(stmt, 1))
        let cdtable = String(cString: sqlite3_column_text(stmt, 2))
        let cdshortcut = String(cString: sqlite3_column_text(stmt, 3))
        let cddescription = String(cString: sqlite3_column_text(stmt, 4))

        shortcutList.append(Shortcuts(id: Int(id), cdcommand: String(describing: cdcommand), cdtable: String(describing: cdtable), cdshortcut: String(describing: cdshortcut), cddescription: String(describing: cddescription)))
    }

    self.tableViewShortcuts.reloadData()
}

func queryAlltableNames() {

    let queryTableNames = "SELECT DISTINCT cdtable FROM main ORDER BY cdtable ASC"

    var stmt:OpaquePointer?

    if sqlite3_prepare(db, queryTableNames, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing insert: \(errmsg)")
        return
    }

    while(sqlite3_step(stmt) == SQLITE_ROW){
        let cdtable = String(cString: sqlite3_column_text(stmt, 0))
        tablenameList.append(tableNames(cdtable: String(describing: cdtable)))
    }

}

func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    return shortcutList[section].cdtable!.count
}

func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {

   let cell = tableView.dequeueReusableCell(withIdentifier: "cellShortcuts", for: indexPath) as! shortcutCustomCell

    let shortcut: Shortcuts
    shortcut = shortcutList[indexPath.row]

    cell.commandText.text = shortcut.cdcommand
    cell.shortcutText.text = shortcut.cdshortcut
    cell.descText.text = shortcut.cddescription

    return cell
}

func numberOfSections(in tableView: UITableView) -> Int {
    return tablenameList.count
}

func tableView(_ tableView: UITableView, viewForHeaderInSection section: Int) -> UIView? {
    let view = UIView(frame: CGRect(x: 0, y: 0, width: tableView.frame.width, height: 30))
    view.backgroundColor = UIColor.black

    let lbl = UILabel(frame: CGRect(x: 15, y: 0, width: view.frame.width - 15, height: 30))
    lbl.font = UIFont.boldSystemFont(ofSize: 16)
    lbl.textColor = UIColor.white
    lbl.text = tablenameList[section].cdtable
    view.addSubview(lbl)
    return view
}

func tableView(_ tableView: UITableView, heightForHeaderInSection section: Int) -> CGFloat {
    return 30
}
}

Solution

I’d suggest a model structure to capture the sections of the table view. For example:

class Section<Key, Element> {
    let name: Key?
    let elements: [Element]

    init(name: Key?, elements: [Element]) {
        self.name = name
        self.elements = elements
    }
}

Then you can write a routine to iterate through your sorted array and grouping it:

extension Sequence {
    /// Group sorted array.
    ///
    /// This assumes the array is already sorted by whatever you will be grouping it.
    ///
    ///     let input = [Foo(bar: "a", baz: "x"), Foo(bar: "a", baz: "y"), Foo(bar: "b", baz: "z")]
    ///     let result = input.grouped { $0.bar }
    ///     // [
    ///     //     ("a", [Foo(bar: "a", baz: "x"), Foo(bar: "a", baz: "y")]),
    ///     //     ("b", [Foo(bar: "b", baz: "z")])
    ///     // ]
    ///
    /// - Parameter groupedBy: Closure to dictate how it will be grouped.
    ///
    /// - Returns: An array of tuples, one entry for every new occurenc of the `groupedBy` result.

    func grouped<Key: Equatable>(by groupedBy: (Element) -> Key) -> [(Key, [Element])] {
        var results: [(Key, [Element])] = []
        var previousKey: Key?
        var previousElements: [Element] = []

        for element in self {
            let key = groupedBy(element)
            if key != previousKey {
                if let previousKey = previousKey {
                    results.append((previousKey, previousElements))
                }
                previousKey = key
                previousElements = []
            }

            previousElements.append(element)
        }

        if let previousKey = previousKey {
            results.append((previousKey, previousElements))
        }

        return results
    }
}

Personally, just because your table is using cryptic column names doesn’t mean your Shortcuts should use them, too. I’d also remove the s from the end of Shortcuts because each object represents a single shortcut:

class Shortcut {
    let id: Int
    let command: String?
    let table: String?
    let shortcut: String?
    let description: String?
    let image: UIImage?

    init(id: Int, command: String?, table: String?, shortcut: String?, description: String?, image: UIImage?) {
        self.id = id
        self.command = command
        self.table = table
        self.shortcut = shortcut
        self.description = description
        self.image = image
    }
}

Then, your reading routine can read the results in, and call this grouped(by:) routine to populate the model structure for your grouped table. Thus, given:

var sections: [Section<String, Shortcut>] = []

You can populate it with:

sections = shortcuts.grouped { $0.table }
    .map { Section(name: $0.0, elements: $0.1) }

And then your data source methods would look like:

func numberOfSections(in tableView: UITableView) -> Int {
    return sections.count
}

func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    return sections[section].elements.count
}

func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
    let cell = tableView.dequeueReusableCell(withIdentifier: "Cell", for: indexPath)

    let shortcut = sections[indexPath.section].elements[indexPath.row]

    // configure your cell however you want

    cell.textLabel?.text = shortcut.command
    cell.detailTextLabel?.text = shortcut.shortcut

    return cell
}

And get the title for the section like so:

func tableView(_ tableView: UITableView, titleForHeaderInSection section: Int) -> String? {
    return sections[section].name
}

Then a database like so ...

... will yield a table like so:

So readValues might look like:

var sections: [Section<String, Shortcut>] = []
var shortcuts: [Shortcut] = []

func readValues() {
    let queryString = "SELECT * FROM main ORDER BY cdtable, cdcommand"
    var statement: OpaquePointer?

    guard sqlite3_prepare(db, queryString, -1, &statement, nil) == SQLITE_OK else {
        print("error preparing select: \(errorMessage())")
        return
    }

    defer { sqlite3_finalize(statement) }

    shortcuts = []

    while sqlite3_step(statement) == SQLITE_ROW {
        let id = Int(sqlite3_column_int(statement, 0))

        let command     = sqlite3_column_text(statement, 1).flatMap({ String(cString: $0) })
        let table       = sqlite3_column_text(statement, 2).flatMap({ String(cString: $0) })
        let shortcut    = sqlite3_column_text(statement, 3).flatMap({ String(cString: $0) })
        let description = sqlite3_column_text(statement, 4).flatMap({ String(cString: $0) })

        let count = Int(sqlite3_column_bytes(statement, 5))
        var image: UIImage?
        if count > 0, let bytes = sqlite3_column_blob(statement, 5) {
            let data = Data(bytes: bytes, count: count)
            image = UIImage(data: data)
        }

        shortcuts.append(Shortcut(id: id, command: command, table: table, shortcut: shortcut, description: description, image: image))
    }

    sections = shortcuts.grouped { $0.table }
        .map { Section(name: $0.0, elements: $0.1) }
}

func errorMessage() -> String {
    return sqlite3_errmsg(db)
        .flatMap { String(cString: $0) } ?? "Unknown error"
}

A few other observations:

Make sure to sqlite3_finalize every successfully prepared statement.

You’re opening the database from the bundle. Generally you wouldn’t do that. But if you were, I might suggest opening it like so:

func openDatabase() -> Bool {
    guard let dbPath = Bundle.main.url(forResource: "database", withExtension: "db") else {
        print("Cannot locate database file.")
        return false
    }

    guard sqlite3_open_v2(dbPath.path, &db, SQLITE_OPEN_READONLY, nil) == SQLITE_OK else {
        print("An error has occurred.", errorMessage())
        sqlite3_close(db)
        return false
    }

    return true
}

The Bundle.main.url(forResource:withExtension:) will check for existence for you.

NB: I used the SQLITE_OPEN_READONLY as documents in the bundle are read-only.

Frankly, we don’t generally open from the bundle, as that’s read-only. We’d generally open from somewhere like the application support directory, and copy from the bundle if we couldn’t find it:

func openDatabase() -> Bool {
    let applicationSupportURL = try! FileManager.default
        .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        .appendingPathComponent("database.db")

    if sqlite3_open_v2(applicationSupportURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
        return true
    }

    // if we got here, we were unable to open database, so we'll copy it from the bundle

    sqlite3_close(db)

    guard let bundleURL = Bundle.main.url(forResource: "database", withExtension: "db") else {
        print("Cannot locate database file.")
        return false
    }

    do {
        try FileManager.default.copyItem(at: bundleURL, to: applicationSupportURL)
    } catch {
        print(error)
        return false
    }

    guard sqlite3_open_v2(applicationSupportURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK else {
        print("An error has occurred.", errorMessage())
        sqlite3_close(db)
        return false
    }

    return true
}

Note in both of these alternatives, always close the database if the open failed. I know it seems strange, but see the sqlite3_open documentation, which is specific on this point.
I’d be wary about using * in SQL statements. The correctness of your code should not be contingent on order of the columns in the table. So, rather than:
```
let queryString = "SELECT * FROM main ORDER BY cdtable, cdcommand"
```
I’d instead recommend being explicit about the order of the columns:
```
let queryString = "SELECT id, cdcommand, cdtable, cdshortcut, cddescription FROM main ORDER BY cdtable, cdcommand"
```
Do I infer from the cd prefixes in your table that you’re dealing with a CoreData database? Having spent all this time talking about SQLite, if you’re using CoreData, I’d suggest staying within (especially if you plan on updating it later). Now, if you’ve given up on CoreData, that’s fine. But then I’d lose the cd prefixes.