Database Abstraction Changes

[christopher-davis-afs]

Currently libocpp contains its own wrapper for SQLite3 interactions, made of the following components:

• DatabaseHandlerCommon
• DatabaseException
• DatabaseSchemaUpdater
• SqliteStatement

On top of these are the DatabaseHandler classes for v1.6 and v2.0.1, which contain many assorted actions for the database. These actions operate on a broad range of entities/tables, such as the authorization cache, connectors, EVSEs, transactions, and charging profiles. The actions range from simple CRUD cases to more complex filtering. Ultimately, this has lead to the DatabaseHandler class for v201 in particular to be extremely bloated.

The Everest project may be better served by refactoring how we use the database in two ways:

• Using an existing C++ SQlite3 wrapper
• Using a repository/object based pattern to isolate different actions for our entities/tables

These changes would reduce the scope of what needs to be maintained by the Everest community and keep our classes well-scoped.

SQlite3 Wrappers

When researching potential options for SQLite3 libraries, the two that came up most frequently were SQLiteCpp and sqlite_orm.

SQLiteCpp is the simplest option - like our internal database work, it provides convenient APIs that wrap the raw sqlite3 library. Like our code it provides wrappers for Transactions, Connections, and Statements. However, it does not seem to have a system for migrations, so we would still need to handle that ourselves.

sqlite_orm is a more complex case and may require a larger refactoring. It provides strongly typed APIs by representing tables as C++ objects. With these objects we can then query the database by writing statements as strongly typed C++ code instead of strings. Unlike SQLiteCpp, there is API for database migrations. One potential drawback to sqlite_orm is that the template-based nature means that all queries are established at compile time. This is a double-edged sword, as dynamic queries like those for K09 and K10 are more difficult.

Repository/Object-based patterns

Regardless of whether or not we continue using our DB wrappers, use SQLiteCpp, or sqlite_orm, we should consider using a repository/object based pattern to split functionality for different tables into different classes. This would mean, for example, having classes for:

• Transactions (“TRANSACTIONS”)
• MeterValues (“METER_VALUES”)
• ChargingProfiles (“CHARGING_PROFILES”)
• AuthCache (“AUTH_CACHE”)
• AuthList (“AUTH_LIST”)
• Availability (“AVAILABILITY”)

Each class would use whatever database wrapper we have to encapsulate the functions that operate on those tables, and only those tables. So for example, multiple classes may have a function to get() an object by a given id, but something like getting a charging profile by criteria would be on the ChargingProfiles class specifically. As for how these are used, there are different routes. You could make classes that use the database take only the individual DB classes and then call charging_profiles.get_charging_profiles_matching_criteria(/*…*/), or keep an overarching object for the database that exposes the repositories, such as db.charging_profiles.get_charging_profiles_matching_criteria(/*…*/).