A Curious Inheritance Problem... what pattern solves it?

[David Schwartz 426]

I'm curious about something that I've run into a few times, and I've never figured out how to solve it nicely. I'm wondering if there are any good solutions.

 

Basically, I have a couple of things derived from TDataset: TOracleDataset and TPgQuery. Both of them are essentially TQuery variants. The problem is, I guess there are some shortcomings with the design of TQuery such that nobody ever derives query components from it. Or maybe it's that TQuery is too restrictive for their needs.

 

Regardless, I want to be able to pass a parameter to a function that takes instances of either one of these. The problem is, they both have a SQL property that's not in the TDataset class they inherit from (along with some other things).

 

procedure Form1.Func1( aQry : <<what_goes_here?>> );

 

I want to be able to call Func1( aOracleQry ) as well as Func1(aPgQry).

 

But since TDataset doesn't have an SQL property, if I use that as the parameter type, I have to use code like this:

procedure Form1.Proc1( aQry : TDataset );
begin
. . .
  aQry.Close;
  if (aQry is TOracleDataset) then
    TOracleDataset(aQry).SQL.Text := 'select * from xyz'
  else
    TPgQuery(aQry).SQL.Text := 'select * from xyz';
  aQry.Open;
. . .
end;

I don't want the function to have to know what kinds of datasets might be passed. That's what inheritance and polymorphism are supposed to handle.

 

What I want to simply say is something like this:

procedure Form1.Proc1( aQry : TSomething );
begin
. . .
  aQry.Close;
  aQry.SQL.Text := 'select * from xyz';
  aQry.Open;
. . .
end;

The actual variables themselves DO have an SQL : Strings property, but their common ancestor class does not, and I have no way of inserting a class in between the two.

 

This is just one example; I've encountered the same thing in different contexts. I've just never figured out a good way to address it.

 

Any ideas?

[Dmitry Arefiev 101]

You can do, when both conditions are met:

* TOracleDataSet and TgQuery both are inherited from TDataSet

* and properly implement IProviderSupport methods:

procedure Form1.Proc1( aQry : TDataSet );
begin
  ...
  aQry.Close;
  (aQry as IProviderSupport).PSSetCommandText('select * from xyz');
  aQry.Open;
  ...
end;

 

Edited March 27, 2019 by Dmitry Arefiev

[PeterBelow 238]

10 hours ago, David Schwartz said:

I'm curious about something that I've run into a few times, and I've never figured out how to solve it nicely. I'm wondering if there are any good solutions.

 

Basically, I have a couple of things derived from TDataset: TOracleDataset and TPgQuery. Both of them are essentially TQuery variants. The problem is, I guess there are some shortcomings with the design of TQuery such that nobody ever derives query components from it. Or maybe it's that TQuery is too restrictive for their needs.

 

Regardless, I want to be able to pass a parameter to a function that takes instances of either one of these. The problem is, they both have a SQL property that's not in the TDataset class they inherit from (along with some other things).

 

procedure Form1.Func1( aQry : <<what_goes_here?>> );

 

I want to be able to call Func1( aOracleQry ) as well as Func1(aPgQry).

 

But since TDataset doesn't have an SQL property, if I use that as the parameter type, I have to use code like this:

procedure Form1.Proc1( aQry : TDataset );
begin
. . .
  aQry.Close;
  if (aQry is TOracleDataset) then
    TOracleDataset(aQry).SQL.Text := 'select * from xyz'
  else
    TPgQuery(aQry).SQL.Text := 'select * from xyz';
  aQry.Open;
. . .
end;

I don't want the function to have to know what kinds of datasets might be passed. That's what inheritance and polymorphism are supposed to handle.

 

What I want to simply say is something like this:

procedure Form1.Proc1( aQry : TSomething );
begin
. . .
  aQry.Close;
  aQry.SQL.Text := 'select * from xyz';
  aQry.Open;
. . .
end;

The actual variables themselves DO have an SQL : Strings property, but their common ancestor class does not, and I have no way of inserting a class in between the two.

 

This is just one example; I've encountered the same thing in different contexts. I've just never figured out a good way to address it.

 

Any ideas?

Dmitry gave you a possible solution for the specific problem you posted (TDataset descendents).

I think this is generally a case for the facade pattern: you extract the functionality the method needs to access to an interface type (or a wrapper base class) and make your divergent descendents implement this interface or provide a factory method for a suitable derived wrapper class instance. The method parameter is then typed as the interface or wrapper base class type.

[Lars Fosdal 1792]

We chose to do wrapper classes that further abstract the actual database types away from our code.  Hence, in our code, there is no difference between running a BDE wrapped ADO connection and a FireDAC connection.

Those classes use old school encapsulation to hide such differences from our code with very little overhead.

[David Schwartz 426]

12 hours ago, Dmitry Arefiev said:

You can do, when both conditions are met:

* TOracleDataSet and TgQuery both are inherited from TDataSet

* and properly implement IProviderSupport methods:

procedure Form1.Proc1( aQry : TDataSet );
begin
  ...
  aQry.Close;
  (aQry as IProviderSupport).PSSetCommandText('select * from xyz');
  aQry.Open;
  ...
end;

 

very cool! I never knew about this. 🙂

 

EDIT: But this interface says it's deprecated. Has it been replaced with something else?

Edited March 27, 2019 by David Schwartz

[David Schwartz 426]

5 hours ago, Lars Fosdal said:

We chose to do wrapper classes that further abstract the actual database types away from our code.  Hence, in our code, there is no difference between running a BDE wrapped ADO connection and a FireDAC connection.

Those classes use old school encapsulation to hide such differences from our code with very little overhead.

well, wouldn't you still need something to say which one you need?

 

I was thinking of a regular Pascal Variant Record, which would work fine to hold both types of pointers. But you'd need a flag to say which one to pick.

 

Unless ... in the case of a wrapper class, you just set one instance (pointer) and internally the logic selected the one that's not NIL.

 

Or am I missing something?

Edited March 27, 2019 by David Schwartz

[Remy Lebeau 1393]

18 hours ago, David Schwartz said:

The actual variables themselves DO have an SQL : Strings property, but their common ancestor class does not, and I have no way of inserting a class in between the two.

If checking for a common interface, like Dmitry showed, and creating wrappers, like Lars mentioned, do not work for you and/or are not options in your case, you can use RTTI instead:

uses
  ..., System.Rtti;
  
procedure Form1.Proc1( aQry : TDataset );
var
  Ctx: TRttiContext;
  SQL: TStrings;
begin
  ...
  aQry.Close;
  SQL := TStrings(Ctx.GetType(aQry.ClassType).GetProperty('SQL').GetValue(aQry).AsObject);
  SQL.Text := 'select * from xyz';
  aQry.Open;
  ...
end;

 

Edited March 27, 2019 by Remy Lebeau

[Lars Fosdal 1792]

I'll get back to you on this, @David Schwartz

Think base class, inheritance and encapsulation.

[Lars Fosdal 1792]

A rough draft from the top of my head.

I've made the two query types look a little different from what they probably actually are to point out how the encapsulation hides the differences.
Your actual wrapper will be different, and the number of methods that you need to wrap depends on the various TDataSet descendants and how you use them now.

The wrapper exposes the properties and methods I need.

type
  TxQuery<T: TDataSet> = class abstract
   private
     FiQuery: T;
   protected
     procedure SetSQL(aValue: string); ; virtual; abstract;
     function GetSQL: string; virtual; abstract;
     function GetDataSet: TDataSet; virtual; abstract;
     property iQuery: T read FiQuery write FiQuery;
   public
     procedure Execute; virtual; abstract;
     property SQL: string read GetSQL write SetSQL;
     property DataSet: TDataSet read GetDataSet;
  end
  TxQueryClass = class of TxQuery;

  TxOraQuery = class TxQuery<TOracleDataset>
  protected
    procedure SetSQL(aValue: string); override;
    function GetSQL: string; override;
    function GetDataSet: TDataSet; virtual; abstract;
  end;

  TxPgQuery = class TxQuery<TPgQuery>
  protected
    procedure SetSQL(aValue: string); override;
    function GetSQL: string; override;
     function GetDataSet: TDataSet; virtual; abstract;
  end;
  

procedure TxOraQuery.SetSQL(aValue: string); 
begin
  iQuery.SQL.Text := aValue;
end;

function TxOraQuery.GetSQL: string; 
begin
  Result := iQuery.SQL.Text;
end;

function TxOraQuery.GetDataSet: TDataSet;
begin
  Result := iQuery;
end;


procedure TxPgQuery.SetSQL(aValue: string); 
begin
  iQuery.SQL := aValue;
end;

function TxPgQuery.GetSQL: string; 
begin
  Result := iQuery.SQL;
end;

function TxPgQuery.GetDataSet: TDataSet;
begin
  Result := iQuery.DataSet;
end;

type
  TForm1 = class(TForm)
  var
    QryClass: TxQueryClass;
    procedure FormCreate;
    procedure TestQuery(aQuery: TxQuery; aSQL: string);
  end;

procedure TForm1.FormCreate;
begin
  if Config = Pg
  then QryClass := TxPgQuery
  else QryClass := TxOraQuery;
end;

procedure TForm1.TestQuery;
var
  Qry: TxQuery;
  SQL: string;
begin
  Qry := QryClass.Create;
  Proc1(Qry, aSQL);
end;

procedure TForm1.Proc1(aQuery: TxQuery; aSQL: string);
begin
  aQuery.DataSet.Close;
  aQuery.SQL := aSQL;
  aQuery.DataSet.Open;
  ...
end;

 

[David Schwartz 426]

@Lars Fosdal that's great! 

 

I'm chuckling because you're using the TxQueryClass as the discriminant. I had not thought of that.

 

Otherwise it's very straightforward. Sweet!

[Remy Lebeau 1393]

2 hours ago, Lars Fosdal said:

A rough draft from the top of my head.

I've made the two query types look a little different from what they probably actually are to point out how the encapsulation hides the differences.
Your actual wrapper will be different, and the number of methods that you need to wrap depends on the various TDataSet descendants and how you use them now.

I would suggest having the wrapper expose access to the actual TStrings object for the SQL, instead of getting/setting the SQL as just a String.  It is usually beneficial to be able to build up SQL queries, especially long/complex queries, in multiple pieces, which can be tricky/inefficient as a single string.

type
  TxQuery<T: TDataSet> = class abstract
   private
     FiQuery: T;
   protected
     function GetSQL: TStrings; virtual; abstract;
     function GetDataSet: TDataSet; virtual; abstract;
     property iQuery: T read FiQuery write FiQuery;
   public
     procedure Execute; virtual; abstract;
     property SQL: TStrings read GetSQL;
     property DataSet: TDataSet read GetDataSet;
  end
  TxQueryClass = class of TxQuery;

  TxOraQuery = class TxQuery<TOracleDataset>
  protected
    function GetSQL: TStrings; override;
    function GetDataSet: TDataSet; override;
  end;

  TxPgQuery = class TxQuery<TPgQuery>
  protected
    function GetSQL: string; override;
    function GetDataSet: TDataSet; override;
  end;

function TxOraQuery.GetSQL: TStrings;
begin
  Result := iQuery.SQL;
end;

function TxOraQuery.GetDataSet: TDataSet;
begin
  Result := iQuery.DataSet;
end;

function TxPgQuery.GetSQL: TStrings;
begin
  Result := iQuery.SQL;
end;

function TxPgQuery.GetDataSet: TDataSet;
begin
  Result := iQuery.DataSet;
end;

type
  TForm1 = class(TForm)
  var
    QryClass: TxQueryClass;
    procedure FormCreate;
    procedure TestQuery(aQuery: TxQuery; aSQL: string);
  end;

procedure TForm1.FormCreate;
begin
  if Config = Pg then
    QryClass := TxPgQuery
  else
    QryClass := TxOraQuery;
end;

procedure TForm1.TestQuery;
var
  Qry: TxQuery;
  SQL: string;
begin
  Qry := QryClass.Create;
  Proc1(Qry, aSQL);
end;

procedure TForm1.Proc1(aQuery: TxQuery; aSQL: string);
begin
  aQuery.DataSet.Close;
  aQuery.SQL.Text := aSQL;
  aQuery.DataSet.Open;
  ...
end;

 

Edited March 27, 2019 by Remy Lebeau

[Lars Fosdal 1792]

@Remy Lebeau Yeah, that can be a good idea. Or, you do like we do, and have various custom query builder classes that produce a final string - which you then pass on.

[Rudy Velthuis 91]

I know I am late, but I would simply have an intermediate class deriving from TDataSet, but with the extra properties and methods that both classes should have. The two classes then derive from that intermediat class. And you can specify the intermediate class as the parameter type for functions, etc.

 

Or is that too naive?

[Clément 148]

If I may.....
Can you use an Interface instead? For example:

 

type
  IHasSQLSupport = Interface
   ['{E38C927E-019E-423C-A0C3-5C65A836D0C7}']
   procedure AssignSQL( const aSQL : string );
  End;

  TOracleDataset = class (...., IHasSQLSupport)
    // implements IHasSQLSupport
   procedure AssignSQL( const aSQL : string );
  end;

  TPgQuery = class ( ..., IHasSQLSupport )
   // implements IHasSQLSupport
   procedure AssignSQL( const aSQL : string );
  end;

  TForm72 = class(TForm)
  private
    { Private declarations }
    procedure Myfunc( aQry : IHasSQLSupport );
  public
    { Public declarations }
  end;
  
 
implementation

procedure TForm72.Myfunc(aQry: IHasSQLSupport; const aSQL : string);
begin
  aQry.AssignSQL(aSQL);
end;

As long as SomeDataset is TOracleDataset or TpgQuery, you can call it as

 

   MyFunc( SomeDataset, aSQL );

  SomeDataset.Open;

 

This way you could also implement transaction support, autoinc support etc...

 

 

[Dmitry Arefiev 101]

This is IProviderSupport or IProviderSupportNG.

[Uwe Raabe 2057]

2 hours ago, Rudy Velthuis said:

The two classes then derive from that intermediat class.

The two classes are probably already existing classes derived from TDataSet with only a very little chance to adjust that.

[David Schwartz 426]

4 hours ago, Rudy Velthuis said:

I know I am late, but I would simply have an intermediate class deriving from TDataSet, but with the extra properties and methods that both classes should have. The two classes then derive from that intermediat class. And you can specify the intermediate class as the parameter type for functions, etc.

 

Or is that too naive?

In theory it's great, as long as you're building the entire library. Unfortunately, Delphi didn't make it easy to do in this case.

 

TDatasets and TQuerys already exit. The 3rd-party lib devs inherited from TDataset and added their own query support, so they're peers of TQuery, not descendants of it. We don't have the option of going back and redesigning TQuery, along with its relationship to TDataset.

 

It's also instructive to note that TQuery specializes TDataset; it hides as much as it adds, making it unattractive to use as a parent class.

[Rudy Velthuis 91]

10 hours ago, Uwe Raabe said:

The two classes are probably already existing classes derived from TDataSet with only a very little chance to adjust that.

Then I got the wrong impression. I assumed he had written those two classes.

 

In the case he can't use an intermediate, I would indeed use a wrapper class or interface.

[Rudy Velthuis 91]

9 hours ago, David Schwartz said:

In theory it's great, as long as you're building the entire library. Unfortunately, Delphi didn't make it easy to do in this case.

 

TDatasets and TQuerys already exit. The 3rd-party lib devs inherited from TDataset and added their own query support, so they're peers of TQuery, not descendants of it. We don't have the option of going back and redesigning TQuery, along with its relationship to TDataset.

 

It's also instructive to note that TQuery specializes TDataset; it hides as much as it adds, making it unattractive to use as a parent class.

Ok, then I misunderstood. I thought you had written those classes.

 

Then go for the wrapper. Best solution, IMO.

[David Schwartz 426]

@Rudy Velthuis No, I didn't write these. They're two different 3rd-party libs.

[David Schwartz 426]

On 3/28/2019 at 12:39 PM, Clément said:

If I may.....
Can you use an Interface instead? For example:

 

type
  IHasSQLSupport = Interface
   ['{E38C927E-019E-423C-A0C3-5C65A836D0C7}']
   procedure AssignSQL( const aSQL : string );
  End;

  TOracleDataset = class (...., IHasSQLSupport)
    // implements IHasSQLSupport
   procedure AssignSQL( const aSQL : string );
  end;

  TPgQuery = class ( ..., IHasSQLSupport )
   // implements IHasSQLSupport
   procedure AssignSQL( const aSQL : string );
  end;

As long as SomeDataset is TOracleDataset or TpgQuery, you can call it as

 

   MyFunc( SomeDataset, aSQL );

  SomeDataset.Open;

 

This way you could also implement transaction support, autoinc support etc...

 

 

Sorry, I missed this earlier.

 

Same as what I told Rudy ... these are two different libs from separate 3rd-party vendors. Otherwise, it's a fine idea. 🙂

[David Schwartz 426]

hmmmm... actually it's a bit more complex that I first realized ...

 

There's the SQL property, which is a TStrings -- that's common to both query classes.

 

But it turns out they have different ways of supporting SQL parameters.

 

One requires you to define each parameter and its type, and the types are not the same "stuff"; in the other, they're optional.

 

One can do auto-prepare, the other requires an explicit call to Prepare, but both can take an explicit call to Prepare.

 

-- Both can do auto-Prepare when the .SQL.Lines property is assigned.

 

One uses qry.ParamByName(...).AsSomeType := blahblah to set values, the other uses SetValue or something like that

 

I'm not sure if even IProviderSupport covers these inconsistencies.

 

Boy ... what a mess. 

Edited March 30, 2019 by David Schwartz

[Rudy Velthuis 91]

On 3/30/2019 at 1:18 AM, David Schwartz said:

Sorry, I missed this earlier.

 

Same as what I told Rudy ... these are two different libs from separate 3rd-party vendors. Otherwise, it's a fine idea. 🙂

Then write simple wrappers that do derive from a common base class. Especially if SQL parameter handling is different for both, wrappers are the best choice.

[Lars Fosdal 1792]

@David Schwartz - What about vanilla FireDAC? Doesn't the FD drivers fully hide the differences between Oracle and PostgreSQL?

[David Schwartz 426]

On 4/1/2019 at 3:26 AM, Lars Fosdal said:

@David Schwartz - What about vanilla FireDAC? Doesn't the FD drivers fully hide the differences between Oracle and PostgreSQL?

perhaps. But we've got to migrate to one or the other, regardless. Also, FD uses a DLL for Postgres, while PgDAC goes direct.