Today I decided to ensure the project I am working on is in fact database independent. The best way I have found is to make sure you develop and test on multiple databases as you go. Since the ORM portion of Spring4D often called Marshmellow is supposed to isolate your application from the underlying data store, I figured it would be a piece of cake.
The Spring4D ORM supplies numerous database adapters including UIB (Unified Interbase). There is also another I found for IBX. I decided to try UIB since I want minimal deployment dependencies and am using Firebird, but was a little concerned since the last commit was from Jun 13, 2016.
After pulling down the source code from the repo I discovered that there are no projects for Delphi Rio, so I copied the projects for the last version (21) and renamed them to create Rio projects. Getting the components installed proved quite easy, but once I had the basic data ORM setup code for an alternate database incorporated into my project, that’s when the fun started.
Initially UIB would not load the GDS32.DLL from c:\Windows\System32 that was generated from my Firebird 3.0 Win64 install. Providing the full path did not resolve the issue, and GetLastError() wasn’t helpful so I changed the client library to fbclient, and the app crashed a little further down the line.
The ORM uses a TDatabaseManager class to build the database structures from the registered entity metadata when you call the BuildDatabase method. It worked for SQLLite, but did not for Firebird as the database file did not exist. Unfortunately the TDatabaseManager was never designed to be extended. The class contains only private fields with no protected accessors, and the constructor as well as the BuildDatabase method, are not virtual. As a result I wrote the following decorator class to provide the extended functionality:
type
TUIBDatabaseManager = class(TObject)
private
FConnection :IDBConnection;
FUIBConnectionAdapter :TUIBConnectionAdapter;
FDatabaseManager :TDatabaseManager;
procedure CreateTheDatabase;
public
constructor Create(const connection: IDBConnection);
procedure BuildDatabase;
end;
implementation
uses
Spring.Persistence.SQL.Interfaces, uiblib;
procedure TUIBDatabaseManager.BuildDatabase;
begin
if not FileExists(FUIBConnectionAdapter.Connection.DatabaseName) then
CreateTheDatabase;
FDatabaseManager.BuildDatabase;
end;
constructor TUIBDatabaseManager.Create(const connection: IDBConnection);
begin
FConnection := connection;
FConnection.QueryLanguage := qlFirebird;
FDatabaseManager := TDatabaseManager.Create(FConnection);
FUIBConnectionAdapter := connection as TUIBConnectionAdapter;
end;
procedure TUIBDatabaseManager.CreateTheDatabase;
begin
FUIBConnectionAdapter.Connection.CreateDatabase(TCharacterSet.csWIN1252);
end;
After I got the database created, the application bombed because it was attempting to create boolean object fields as BIT database columns. Tracing through the code I found the where the datatype mapping was performed and added a snippet for FireBird 3 support after descovering that UIB already supported the Boolean datatype for Interbase and Firebird. All I had to do was compile UIB with the FB30 directive. Here is the amended version of the GetSQLDataTypeName method:
function TFirebirdSQLGenerator.GetSQLDataTypeName(
const field: TSQLCreateField): string;
begin
Result := inherited GetSQLDataTypeName(field);
{$ifdef FB30}
if (field.TypeInfo.Kind = tkEnumeration) and
(field.typeInfo = System.TypeInfo(Boolean)) then
Result := 'BOOLEAN'
else
{$endif}
if StartsText('NCHAR', Result) then
Result := Copy(Result, 2, Length(Result)) + ' CHARACTER SET UNICODE_FSS'
else if StartsText('NVARCHAR', Result) then
Result := Copy(Result, 2, Length(Result)) + ' CHARACTER SET UNICODE_FSS';
end;
Once I got that issue resolved the app crashed on a SQL snippet I had in a call to the ExecuteSQL method because Firebird likes quoted identifiers when you use mixed case column names. Once I had worked around that I found that Firebird threw many more errors importing data due to my Column attributes than SQLLite had. It also complained because I had used a reserved word in Firebird as a table name. After a some more fixups I had a running application capable of using either database back end. In the process I re-discovered how much I like Firebird, even if some of it’s SQL error messages are rather cryptic. It is also much faster importing data that SQLLite.