A minor oversight in SynDB.TQuery?

wloochacz · 2021-03-22 20:41:54

Hello all!

line 3809 in SynDB module (TQuery.Execute):

if P=nil then
    ESQLQueryException.Create('No SQL statement');

And it should be:

if P=nil then
    raise ESQLQueryException.Create('No SQL statement');

Dear AB, am I right?

ab · 2021-03-23 08:11:27

Indeed.
Should be fixed by https://synopse.info/fossil/info/2b37cc1c7b

Thanks for the feedback!

wloochacz · 2021-03-23 14:31:32

Oh no, this is me thanking you for your work!

By the way; I would like to ask you to mark all (so also GetXXX methods from protected section) methods of SynDB.TQuery class as virtual.
I would like to use this class for my own purposes, but I need to be able to overload these methods in e own child class.

Why do I need to do this?
And, for example, to build an SQL parser into SynDB.TQuery; I've already done that and it works like this:

    lQuery.SQL.Text := 'select * from vPpSchedulerTask where IdppTask = :ID';
    lQuery.SQLParser.Where
      .Clear
      .Add('DateStart', coAnd, opGreaterEqual)
      .Add('DocNo', coAnd, opIsNotNull);
    lQuery.SQLParser.OrderBy
      .Add('DateStart', soDesc);

    lQuery.SQLParser.CommitSQL([Now - 90], [ftDateTime]);

lQuery is an object based on TQuery, but brings support for an SQL parser.
This code will result in the construction of such SQL:

select * from vPpSchedulerTask
where DateStart >= :DateStart and DocNo is not null
order by DateStart desc

And since it's a parser (based on gaSQLParser) it can handle much more complex queries.
I have of course tested for joins, subqueries and even Common Table Expression (CTE).

By the way, I found out that parameters are created only after they are explicitly asked for via ParamByName.
Which raises the question of whether Params[index] makes sense, because it will only work properly if we explicitly request this parameter via ParamByName.
Strange...

Wouldn't it be better to create the parameter list after the SQL change, i.e. in the OnSQLChange method?

ab · 2021-03-23 14:44:43

TQuery was just a wrapper to be compatible with the DB.pas homonymous class.
So the parameters array works just as it needs to call the SynDB classes.

Perhaps extending the native SynDB classes could make more sense.
And I would rather see the SQL generation not be directly within the query/statement class, but as a stand-alone class - and unit.

wloochacz · 2021-03-23 15:47:22

ab wrote:

TQuery was just a wrapper to be compatible with the DB.pas homonymous class.

Yes I know that, I read the documentation
However, its compatibility with classic TQuery and further with TParam/TField is hmm... debatable.

Personally I think that naming data types identically as in DB module, but which however provide different contract and work differently, introduces more harm than good.

I understand the idea, but...
Besides, TQuery is exactly what e.g. FIreDAC lacks; a lightweight simple class, serving primarily to read data as efficiently as possible without the unnecessary overhead of TDataSet.

I wanted to extend it a bit and maybe also propose some changes for the benefit of the community.
But for that I need to have virtual methods in TQuery.

ab wrote:

So the parameter array works as needed to call SynDB classes.

Exactly.
But not the way it works in every other DAC for Delphi that I know of.
And that's just the point.

Perhaps extending the native SynDB classes could make more sense.

Perhaps.
However, it's so different, and I have too little time to discern the topic thoroughly, that I don't dare touch it without (your AB? ) support.

But OK; where should I look to implement something similar for native SynDB classes?

And I would rather see the SQL generation not be directly within the query/statement class, but as a stand-alone class - and unit.

I had a similar opinion when I started doing this, and this is exactly how it was implemented and can still be used.

However, various use cases in a real project showed that such a solution has limitations and is not very convenient to use. That's why SQLParser was aggregated into TQuery class. And that at first it was an independent class, that's why I provided communication with TQuery using dedicated interface (and precisely - if TQuery inherited from TInterfacedObject or even from TSingletonImplmentation it wouldn't be bad, but it's not necessary).

The limitation I came across is that it needs to remember the state of WHERE clauses for an instance of TQuery.
SQLParser has methods like Save/RestorePrimaryWhere so that when you modify the WHERE at runtime, you can simply and quickly return to the original query.

And besides, the syntax I showed is just convenient, and the overhead is practically negligible, also because SQLParser is created by Query only when it is needed. You don't use the parser - it won't be created.

ab · 2021-03-23 18:20:43

I would just try the opposite: the SQLParser (which is not a parser by the way, more a SQL constructor) should be a stand-alone class, in which you inject/create your SynDB statement when needed. I would forget about TQuery.
This idea is better following SOLID principle, and the Single Responsibility principle, I think.

wloochacz · 2021-03-24 08:19:56

I would just try the opposite: the SQLParser
(which is not a parser by the way, more a SQL constructor)

Well, no.
SQLBuilder can't modify existing SQL.
This object can, because it's built on top of a full parser.
And the fact that I showed how to build Where (you may have noticed the Clear method call in my example) and OrderBy, does not mean that it can only do that

should be a stand-alone class, in which you inject/create your SynDB statement when needed.

As I wrote earlier, SQLParser is a standalone class that doesn't depend on anything.
But there are functional problems with it and it is just inconvenient to use.
This is simple, convenient helper code that could just as well be implemented as ClassHeleper as well. But it has its limitations, which I didn't agree with.

I would forget about TQuery.
This idea is better following SOLID principle, and the Single Responsibility principle, I think.

Too bad you want to forget about TQuery.
It's very simple and super convenient to use, which is exactly the opposite of ISQLDBStatement

I'll ask you straight - are you not planning to develop TQuery?

Just so we understand each other, my point is that the design and use of ISQLDBStatement is very far from the conventions that have been known for years.

I understand that it works perfectly with the ORM layer.
The problem is that I don't always can or want to use ORM.

For me, the ideal solution would not be an ORM, but a DataMapper.
DataMapper, where I could have full control over all SQL statements for an entity.
Is it even possible in mORMot to allow it to do this?
And if so, where would one start?

ab · 2021-03-24 08:29:09

The idea is that the SQLParser is creating the TSQLDBStatement at execution, using its TSQLDBConnectionProperties field.
Then you can walk into the results using the TSQLDBStatement, or directly export the result as binary or JSON.
I don't think there is anything complicated with this design. It sounds easier to use than the TQuery.

I guess to integrate with the ORM, we could use the JSON.

wloochacz · 2021-03-25 21:17:25

OK, perhaps we'll come back to this.
But first I need to get competent in ORM, so dear AB I will definitely have questions

mORMot Open Source

#1 2021-03-22 20:41:54

A minor oversight in SynDB.TQuery?

#2 2021-03-23 08:11:27

Re: A minor oversight in SynDB.TQuery?

#3 2021-03-23 14:31:32

Re: A minor oversight in SynDB.TQuery?

#4 2021-03-23 14:44:43

Re: A minor oversight in SynDB.TQuery?

#5 2021-03-23 15:47:22

Re: A minor oversight in SynDB.TQuery?

#6 2021-03-23 18:20:43

Re: A minor oversight in SynDB.TQuery?

#7 2021-03-24 08:19:56

Re: A minor oversight in SynDB.TQuery?

#8 2021-03-24 08:29:09

Re: A minor oversight in SynDB.TQuery?

#9 2021-03-25 21:17:25

Re: A minor oversight in SynDB.TQuery?

Board footer