🗄️ Checking Database Existence & Reading/Displaying Data in SQLite (iOS – Xcode)

---

✅ 1. Definition

🔹 Database Existence Check

It means verifying whether the SQLite database file already exists in the device storage before creating or copying it.

---

🔹 Reading Data

It means retrieving stored records from a database using the SQL SELECT statement.

---

🔹 Displaying Data

It means showing retrieved database records in the UI, usually in a:

• UITableView
• UILabel
• Custom views

---

🧠 2. Key Concepts

🔹 FileManager

• Used to check if database file exists in device storage

---

🔹 SQLite SELECT Query

• Used to fetch data from tables

---

🔹 Prepared Statement

• Secure way to read data from SQLite

---

📁 3. Checking Database Existence

---

🔹 Step 1: Define Database Path

let fileManager = FileManager.default

let documentsURL = fileManager.urls(for: .documentDirectory, in: .userDomainMask).first!

let dbPath = documentsURL.appendingPathComponent("AppDatabase.sqlite")

---

🔹 Step 2: Check If File Exists

if fileManager.fileExists(atPath: dbPath.path) {
    print("Database exists")
} else {
    print("Database does not exist")
}

---

🔹 Step 3: Copy from Bundle (If Needed)

if !fileManager.fileExists(atPath: dbPath.path) {
    let bundlePath = Bundle.main.path(forResource: "AppDatabase", ofType: "sqlite")
    
    try fileManager.copyItem(atPath: bundlePath!, toPath: dbPath.path)
    
    print("Database copied")
}

---

📖 4. Opening Database

var db: OpaquePointer?

if sqlite3_open(dbPath.path, &db) == SQLITE_OK {
    print("Database opened successfully")
}

---

📊 5. Reading Data from Database

---

🔹 SQL SELECT Query

SELECT * FROM Students;

---

🔹 Swift Code to Read Data

let query = "SELECT * FROM Students"

var stmt: OpaquePointer?

if sqlite3_prepare_v2(db, query, -1, &stmt, nil) == SQLITE_OK {
    
    while sqlite3_step(stmt) == SQLITE_ROW {
        
        let id = sqlite3_column_int(stmt, 0)
        
        let name = String(cString: sqlite3_column_text(stmt, 1))
        
        let age = sqlite3_column_int(stmt, 2)
        
        print("ID: \(id), Name: \(name), Age: \(age)")
    }
}

sqlite3_finalize(stmt)

---

📺 6. Displaying Data in UITableView

---

🔹 Step 1: Create Arrays

var names: [String] = []
var ages: [Int] = []

---

🔹 Step 2: Store Data from Database

names.append(name)
ages.append(Int(age))

---

🔹 Step 3: Table View Methods

Number of Rows

func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    return names.count
}

---

Display Data

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

    let cell = UITableViewCell(style: .subtitle, reuseIdentifier: "cell")
    
    cell.textLabel?.text = names[indexPath.row]
    cell.detailTextLabel?.text = "Age: \(ages[indexPath.row])"
    
    return cell
}

---

📊 7. Data Flow Diagram (for Exams)

Database (SQLite)
      ↓
SELECT Query
      ↓
Swift Variables (Arrays)
      ↓
UITableView
      ↓
Screen Display

---

💡 8. Example App

🎯 Student List App

• Check database exists
• Load student records
• Display in table view

👉 Output:

• Ali – 20
• Sara – 22
• Ahmed – 21

---

📌 9. Important Rules / Tips

• Always check database existence before opening
• Use Documents directory for writable DB
• Finalize statements using sqlite3_finalize()
• Store data in arrays before displaying
• Reload table view after fetching data

tableView.reloadData()

---

⚠️ 10. Common Mistakes

• ❌ Not checking database existence
• ❌ Using bundle database directly for writing
• ❌ Forgetting to finalize statement
• ❌ Not handling empty results
• ❌ Crashing due to nil database path

---

🧠 11. Best Practices

• Always use FileManager for existence check
• Separate database logic from UI
• Use arrays or models for clean data handling
• Reload UI after data fetch
• Handle errors properly

---

📝 12. Likely Exam Questions

1. How do you check if a database exists in iOS?
2. What is the use of FileManager?
3. Write SQL query to read all records.
4. Explain how data is fetched from SQLite.
5. How do you display database data in UITableView?
6. What is sqlite3_step() used for?
7. Why do we use Documents directory?
8. Draw a diagram of database data flow.

---

📚 13. Quick Revision Summary

• Check DB existence using FileManager
• If not found → copy from bundle
• Use SELECT query to read data
• Use sqlite3_step() to iterate rows
• Store data in arrays
• Display using UITableView
• Always reload UI after fetching data

---