In my previous post I explained why I chose to use SQLite and GRDB for my app ExerPlan, and in this post I’m going to share implementation details with some Swift code.
I wanted to use Structs to map to database tables. This way I could do things like:
var activity = Activity(activityID: nil, activityType: 1, activityDate: Date() )
activity.save()
I also wanted to be able to have my structs encapsulate functionality to provide data validation. Although I will use some protection at the database level to enforce certain things (primary keys, constraints, triggers) I also like to take action at the UI level before it even hits the database.
To facilitate this I chose to create two structs per database table; one struct would be a “pure” GRDB-focussed struct that would give me the persistence code for free, and the second struct would be more like the public-facing API that I would use throughout my code for actually interacting with the data in the database. The first struct would become a property of the second.
struct ActivityDB: Codable, MutablePersistableRecord, TableRecord
{
// Let GRDB know the name of the database table, otherwise it will assume it is the same as the struct’s name
static let databaseTableName = “Activity”
// This is the primary key, but it’s optional
// because until a row is Inserted there is no value
var activityID: Int64?
var activityType: Int64
var activityDate: Date
mutating func save()
{
// Save record here
// GRDB handles Insert/Update for new/modified row
// and returns the auto-generated ID if there is one
}
// This is where the auto-generated ID can be used
mutating func didInsert(with rowID: Int64, for column: String?)
{
activityTypeID = rowID
}
}
My second struct would have ActivityDB as a property, but allow for data validation that I didn’t want to have on the GRDB-focussed struct.
struct Activity
{
var dataStore: ActivityDB
var activityID: Int64?
{
get
{
return dataStore.activityID
}
}
var activityType: Int64
{
get
{
return dataStore.activityType
}
set(newValue)
{
// Perform some validation logic here
dataStore.activityType = newValue
}
}
var activityDate: Date
{
get
{
return dataStore.activityDate
}
set(newValue)
{
// Perform some validation logic here
dataStore.activityDate = newValue
}
}
mutating func save()
{
dataStore.save()
}
}
There are a few kinks to work out with this approach, notably I’m not sure if I need the two structs and could just use one. At this stage it’s working as I want so I’ll continue with it until/unless I hit any major issues.
The next step was to create some code that would allow the easy retrieving of rows. GRDB provides a number of methods to do this, but I wrap those up inside a big Data Access Layer or DAL class. The class consists of a number of static methods that return arrays of structs, or whatever else is needed from the database. The following code shows the basic structure of how I return an array of one type.
class DAL
{
private static func returnArray<T>(fromRows rows: [Row], ofType: DatabaseTypeEnum) -> Array<T>
{
var list = [T]()
for row in rows
{
switch ofType
{
case .ActivityTableView:
list.append(ActivityTableView(dbRow: row) as! T)
case .PlanTableView:
list.append(PlanTableView(dbRow: row) as! T)
}
}
return list
}
private static func doFetchAll(withSQL sql: String, returnType: DatabaseTypeEnum) -> Array<T>
{
let rows = try! dbQueue.read
{ db in
try Row.fetchAll(dB , sql)
}
returnArray(fromRows: rows, ofType: returnType)
}
static func fetchAllActivities() -> [ActivityTableView]
{
let sql = “””
Select src.ActivityID, src.ActivityType, src.ActivityDate
From Activity src
Order By
src.ActivityDate desc
“””
return doFetchAll(withSQL: sql, returningType: .ActivityTableView)
}
}
The DAL can easily be extended with new methods for retrieving data, and also for the retrieval of single values/rows rather than an array. It’s still a work in progress design and as I get further into the UI development and have to start using these methods I may end up making new design decisions.
Writing this article has been a useful exercise as it has forced me to consider my choices again, and even led to some refactoring as I wrote it. The next step is to progress with the UI prototype which will give all this code a really good workout.
