Introduction to iOS and SQLite: Returning Structured Data from a Table

As mobile app developers, we often need to interact with databases stored on the device. In this article, we’ll explore how to retrieve structured data from an SQLite database in an iOS application, specifically when dealing with hierarchical data like bookmarks in Safari.

Understanding the Challenge

The question posed by the OP (original poster) highlights a common issue when working with hierarchical data in iOS and SQLite. They’re trying to display a list of folders with their respective children, similar to how bookmarks are displayed in Safari. The challenge lies in retrieving the correct relationships between these entities and representing them as an array or dictionary for use in a UITableViewController.

SQLite Database Structure

To understand the solution, let’s first examine the SQLite database structure suggested by the answer. The key concept here is the introduction of “lineage,” which represents the hierarchical relationship between nodes.

Node    ParentNode  Depth   Lineage
100     NULL        0       /
101     100         1       /100/
102     101         2       /100/101/
103     102         3       /100/101/102/
104     102         3       /100/101/102/
105     102         3       /100/101/102/

In this example, each row represents a node with its parent node ID, depth, and lineage. The lineage column stores the hierarchical relationship as a string.

Lineage Data Structure

To work with this data structure, we need to understand how to manipulate and query it efficiently.

// Define a struct to represent a node in the hierarchy
struct Node {
    id: Int64,
    parent_id: Int64,
    depth: Int64,
    lineage: String,
}

Retrieving Data from SQLite

When interacting with an SQLite database, we use SQL statements to retrieve data. In this case, we need to fetch all nodes and their corresponding children.

// Create a query that retrieves all nodes with their lineage
let query = "SELECT id, parent_id, depth, lineage FROM nodes ORDER BY parent_id DESC"

// Execute the query using sqlite3
let result = executeQuery(query)

Processing Lineage Data

Once we have retrieved the data from SQLite, we need to process it and return an array of dictionaries representing each node in the hierarchy.

// Define a function to generate the lineage array
func generateLineageArray(nodes: [Node]) -> [[String: String]] {
    var lineup = [[String: String]]()
    
    // Create a dictionary to store nodes by their IDs for efficient lookup
    let nodeDict: [Int64: Node] = Dictionary(uniqueKeysWithValues: nodes.map { ($0.id, $0) })
    
    // Iterate through the nodes and build the lineage array
    for node in nodes {
        var lineupNode = [String: String]()
        
        // Start from the current node and traverse up the hierarchy
        while node.parent_id != 0 {
            let parentNode = nodeDict[node.parent_id]
            if let parentLineage = parentNode.lineage {
                lineupNode["lineage"] = "\(parentLineage)/\(node.id)"
            }
            
            // Move to the parent node
            node = parentNode
        }
        
        lineup.append(lineupNode)
    }
    
    return lineup
}

Using the Lineage Data in a UITableViewController

Now that we have generated the lineage array, we can use it to populate our UITableViewController. We’ll create a custom cell class that displays each node’s ID and lineage.

// Define a custom cell class to display nodes with their lineages
class LineageCell: UITableViewCell {
    @IBOutlet weak var lineageLabel: UILabel!
    
    func setupNode(node: Node) {
        self.lineageLabel.text = "\(node.id): \(node.lineage)"
    }
}

Conclusion

In this article, we explored how to return structured data from a SQLite table in an iOS application. By introducing the concept of lineage and manipulating the data accordingly, we can efficiently retrieve hierarchical relationships between entities and represent them as arrays or dictionaries for use in UITableViewController.

Last modified on 2023-08-13