Migrate embedded database from InterBase Lite to SQLite via FireDAC

[jimmckeeth]

Overview

The training uses Embedded InterBase Lite (IBLite) as its local database. IBLite has significant drawbacks for a mobile training project in 2025: it requires a separate Embarcadero license for distribution, has a 1 GB database size limit, and its mobile deployment story is complex. SQLite is the standard embedded database for mobile apps, is built into both Android and iOS, requires no additional deployment, and is fully supported by FireDAC in Delphi 13 with nearly identical SQL syntax.

Background

Why IBLite Is Problematic Now

• Requires the InterBase ToGo license for deployment on Android/iOS (separate purchase).
• IBLite .IB file must be deployed alongside the app — file deployment steps are more complex than SQLite.
• The IBLite driver (FireDAC.Phys.IBLiteDef) has had fewer updates than the SQLite driver.
• Delphi training materials from Embarcadero itself now use SQLite for mobile examples.

SQLite Advantages

• Built into Android and iOS — no deployment of database engine files.
• No license required for redistribution.
• Simpler connection string: just a file path.
• FireDAC's SQLite driver (FireDAC.Phys.SQLite) supports the same SQL DDL/DML used by the current code with minimal changes.
• TPath.GetDocumentsPath + filename is all that's needed for the database path.

Current Database Schema

CREATE TABLE PROJECTS (
  PROJ_ID INTEGER NOT NULL PRIMARY KEY,
  PROJ_TITLE VARCHAR(50),
  PROJ_DESC VARCHAR(200)
);
CREATE TABLE LOG_ENTRIES (
  ...
);

The SQL is compatible with SQLite with only minor adjustments (e.g., VARCHAR → TEXT, BLOB handling for image data).

Files Affected

lab-src/Lab06.../units/fieldlogger.projectdata.standard.pas
lab-src/Lab06.../units/fieldlogger.logdata.standard.pas
lab-src/Lab06.../units/fieldlogger.data.pas
lab-src/Lab07.../units/ (same set)
... (Lab06 through Lab12)
lab-src/Lab08.../units/fieldlogger.authentication.pas  (auth against DB)
All FieldLogger.dproj files (remove IBLite packages, add SQLite)

Steps to Address

1. In each .dproj, remove FireDAC.Phys.IB and FireDAC.Phys.IBLiteDef from the uses and package references; add FireDAC.Phys.SQLite and FireDAC.Phys.SQLiteDef.
2. In fieldlogger.projectdata.standard.pas, change the TFDConnection driver name from IBLite to SQLite and update the connection parameters:

   FDConnection.Params.DriverID := 'SQLite';
   FDConnection.Params.Database := TPath.GetDocumentsPath + PathDelim + 'fieldlogger.db';

3. Update the CREATE TABLE SQL:
   • Replace VARCHAR(n) with TEXT
   • Replace INTEGER NOT NULL PRIMARY KEY with INTEGER PRIMARY KEY AUTOINCREMENT
   • Verify BLOB column type works for image data (it does in SQLite)
4. Remove the IBLite-specific database creation step (IBLite requires CREATE DATABASE; SQLite creates the file automatically on connect).
5. For Lab 08 Authentication: IBLite has built-in user authentication; SQLite does not. Replace the authentication lab with an application-level username/password check (store a hashed password in the SQLite DB, or use a simple hardcoded credential for training purposes). Update the lab instructions accordingly.
6. Remove IBLite from the deployment files list in each .dproj.
7. Update all lab instruction documents to reference SQLite instead of InterBase.

Test Plan

• fieldlogger.db SQLite file is created in the app documents folder on first launch (Android and iOS).
• Projects can be created, read, updated, and deleted (full CRUD) on Android 13 emulator.
• Projects can be created, read, updated, and deleted on iOS 17 simulator.
• Log entries with image BLOBs are stored and retrieved correctly.
• Database persists across app restarts.
• Lab 08 authentication flow functions with the replacement mechanism.
• No IBLite .IB file or IBLite deployment files are present in the build output.
• App compiles without any FireDAC.Phys.IB or FireDAC.Phys.IBLiteDef unit references.