Archive for May, 2019

JIRA is Down

Thursday, May 16th, 2019

Just a note to EMBT customers, JIRA has been down for several hours now.  It is currently giving the following message

HTTP Status 500 - org.ofbiz.core.util.GeneralRuntimeException: Could not determine database type. (Network error IOException: Connection refused: connect)

With a Java callstack if you like that kind of thing.  EMBT has been advised and I’ve been told EDN may also be affected. Hopefully it will be back up shortly.

More Persistence with Spring4D ORM

Wednesday, May 15th, 2019

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, {for TQueryLanguage}
  uiblib;

procedure TUIBDatabaseManager.BuildDatabase;
begin
  //if the database does not exist then create it
  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);
  {add support for Firebird 3.0/Interbase 7 new boolean datatype}
  {$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.

Persistence with Spring4D

Tuesday, May 14th, 2019

My blog tagline is certainly no accident.  I have been interested in persistence frameworks for a long time, and thought I would use Spring4D’s Marshmellow ORM for a project.  Spring4D has been around for quite some time and just had it’s first conference in Italy so I figured the framework was mature and warranted a closer look.  I had previously used the Spring4D DI container, and decided this time to use Spring4D collections as well to avoid as much code bloat as possible.

The first thing I discovered is that the Spring persistence layer aka Marshmellow has a very unfortunate name.  Trying to google it with “delphi” leads to all sorts of hits related that Android version.  The next thing I learned is that development has been put on hold as of September 2018 due to a lack of resources.  This is unfortunate, as there are not that many open source ORMs that use the newer language features of Delphi when compared C# or Java.

The third thing I quickly learned is that other than the Tests, there is not a whole lot of documentation available.  There is the reference help which is really not that helpful.  It doesn’t contain descriptions of the class interactions or architecture and has no examples of usage.  Really it’s not much more informative than drilling through the code.  The best source of “getting started” help that I could find is the previous bitbucket repo. There is of course also the google groups if you need to get clarification of something, and reading previous posts can help you from running into common issues.

As a newbie, it’s not clear how the [InheritenceAttribute()] works so I will have to investigate it further. I assume if the last descendant in a class hierarchy is the only one marked as an entity, it will by default have all the fields marked with the Column() attribute in all ancestor classes.

Another thing that is not particularly clear from any documentation is how the underlying datatype employed by the database is determined.  There are really only 3 pertinent parameters supplied to the Column() attribute, namely length, precision and scale.  Length only applies to string types and precision, scale to numeric.  It is unclear how precision and scale effectively change the numeric datatype and it’s corresponding precision or scale, other than for Integer datatypes you specify 0,0 for precision,scale.

I’m sure all this will become clear as I use the framework more, but some more thorough ‘Getting Started’ documentation would have been really nice.

How to get Login Dialogs Appearing on the TaskBar

Thursday, May 9th, 2019

Most of the applications I have worked on in Delphi are database apps that may present a splash form quickly followed by a login dialog.  If the user fails to authenticate, the application needs to terminate gracefully.  The only way to do so cleanly is to modify the DPR code with some conditional logic. I’ve seen scenarios where after the main form was created the login dialog was invoked and if authentication failed everything was torn down. This complicates the shutdown logic, and often didn’t work well, encouraging a call to Halt() and sometimes leaving the process in memory.

Any long time Delphi user knows that messing with the generated DPR code in Delphi can cause all sorts of grief later when Delphi tries to auto create forms and add units to the uses clause.  That is out of scope for this post, suffice to say that it is possible to write something like this:

var
  User :TUser;
begin
  Screen.Cursor := crAppStart;
  try
    Application.Initialize;
    Application.MainFormOnTaskbar := True;
    Application.Title := 'My Secure App';
    Application.CreateForm(TMainDm, MainDm);
  finally
    Screen.Cursor := crDefault;
  end;
  User := TfrmLogin.Login
  (
    function (const UserName,Password :string) :TObject
    begin
      Result := MainDm.Session.FindWhere<TUser>(
        Restrictions.&And(
          Restrictions.Eq('UserName',UserName),
          Restrictions.Eq('Password',Password)
        )
      ).FirstOrDefault;
    end,
    {
      UserName can be passed as first parameter so don't
      have to type it in all the time
    }
    ParamStr(1),
    3  {credential retries available }
  ) as TUser;
  if User <> nil then
  begin
    Application.CreateForm(TfrmMain, frmMain);
    frmMain.CurrentUser := User;
    Application.Run;
  end;
end.

The problem is that the Login Dialog does not appear on the Windows Taskbar. If it is hidden behind other windows, the user may think the application has not been launched and attempt to start another instance. There is no easy way for the user to bring the login dialog to the foreground short of closing other windows that may be in front of it. Putting the form on the taskbar solves this. As a quick solution I looked at the SetMainForm method in the Vcl.Forms unit, and decided to extract the ChangeAppWindow() procedure since it is not available outside of the Forms unit. Then I simply called it from this event, and voila! a taskbar button showing the Login form.

procedure TfrmLogin.FormCreate(Sender: TObject);
begin
  ChangeAppWindow(handle,True,True);
end;

I’m sure there are reasons why this method is not exposed as a public TApplication class procedure, but perhaps it could be with a usage caveat.