Hi,

I've committed in the FPC packages (fcl-db/src/sql) a SQL scanner/parser/Abstract Syntax Tree.

It can parse the complete SQL syntax, as implemented by Firebird, SQL dialect 3 (which should come pretty close to SQL-92) and builds a syntax tree from it. The Abstract Syntax Tree can re-create the SQL with limited formatting support.

It comes with extensive test suite (over 750 testcases) and I have tested it on almost 400.000 SQL statements; Nevertheless bugs may remain, so I'd welcome any test results you may produce. Especially the GRANT/REVOKE statements are tested only theoretically, since I don't use them. The test suite is committed as well, it can serve as an example of how to use the units.

The scanner/parser have been set up so they should be able to cope with other SQL dialects (using a set of flags) such as MySQL, but this support is currently not implemented.

The purpose is 3-fold:
- Add SQL syntax checking to property editors in the Lazarus IDE.
- Add reverse engineering of database creation scripts to the Lazarus Database Desktop (and change the SQL generation to use the abstract syntax tree)
- Add the ability to reliably alter queries at runtime. (adding fields, filters and whatnot).

If someone wants to implement support for dialects of other SQL databases, let me know, and I'll do my best to explain what must be done.

Michael.

This does sound great !

Will this help creating new high performance database enabled projects, that don't need a user interface (such as DBGrid type of components ?

Could it be used to do database operations in a more Pascal-developer friendly way than directly writing SQL in the application ?

-Michael

Michael Schnell wrote

This does sound great ! Will this help creating new high performance database enabled projects, that don't need a user interface (such as DBGrid type of components ?

I'm not sure I understand what you mean with this ?

Could it be used to do database operations in a more Pascal-developer friendly way than directly writing SQL in the application ?

If you wish, yes.

One of the things I've been thinking about is integrating it with tiOPF, so one wouldn't need to write SQL any more. You could use it to reverse engineer all used SQL statements and convert it to something more OOP.

However, my main use case will be run-time modification of design-time SQL statements.

Another thing it could be used for is creating a visual query builder. It should be easily doable now.

Michael.

Michael Van Canneyt wrote

I'm not sure I understand what you mean with this ?

(As in many cases) I'm not writing for my own benefit, as I'm not at all a typical Delphi/Lazarus user :) . )

I once did a quite small project doing a kind of "embedded" server-type application that high-performance database access. I did this using Delphi and coding the database operation in SQL.

Now my colleagues, the "Pascal division", often needs to do similar stuff, but they don't want to write native SQL code, but use things like DBGrid and "visual" thread party components to access the database, even if the screen display of the application is only needed for debugging and never seen by an end-user.

So a means to code database operations in a more Pascal-developer friendly way might be very welcome.

-Michael

How else can you do database access ?

What are 'visual' thread party components ?

So a means to code database operations in a more Pascal-developer friendly way might be very welcome.

I still do not understand what you want to say.

If they want to create a number of classes , set some properties, and then expect an SQL statement to roll out which can be fed to a database, then, yes, this will be possible. But IMHO directly writing the SQL will be 10 times faster.

If they visually want to drop some tables, specify some field names and set some conditions, yes, this will also be possible if someone builds a visual query builder.

Michael.

Michael Van Canneyt wrote

What are 'visual' thread party components ?

Sorry for too fast typing ;(
"visual third party components"

-Michael

I suppose they would be happy with everything that uses their well known Pascal paradigms for database access instead of forcing them to write native SQL code, but does not produce a huge performance degradation.

Right now, I'm not really involved in this kind of projects, so I only guess....

I think as a first step issuing a decently qualified "select" and iterating through the record of the result set with it's multiple items would be a nice start. Of course the result record would need to include definable related "relational" results from other tables. Allowing for (multi-table) updates of such constructed records, including handling of locking/transactions.

Of course to be really fast you always need stored procedures. I understand that your work might be used to construct SQL code that could be used here. An integration in Pascal projects (using a compatible syntax to the things described above) would be GREAT, but supposedly not that easy ;).

-Michael

It would be very cool to be have an OQL in tiOPF and your new parser may just be the ticket to do that.

SELECT
Person.FirstName,
Person.LastName,
Person.Age
FROM
Person
WHERE

   Person.OID = '1234';

Exactly, and I was planning to bug Graeme with this :-)

Michael.

Example:

There is a table called "client" that has fields "ID" and "Name"

There is a table called "Account" that has fields "ID" and "Sum"

The tables are relationally connected by the "ID" fields.

Now a "perfect SQL hiding component" would provide an easy way to create a function that e.g. doubles "Sum" if the "Name" String contains "Schnell" and "Sum" is greater than "0".

in a second step it would be possible to create a stored procedure from this and finally call same from a program that is done from the same Pascal source code with just some switch set from "local" to "create_stored_procedure" and finally "use_stored_procedure"

Just wildly raving...

-Michael

why stop there? Maybe a language-construct like LINQ in .Net for dynamic+iteratable resultsets?

kind regards,
Dimitri Smits

As far as I understand it, LINQ does the opposite; it expresses iterators in a kind of SQL syntax ?

Michael.

It would be useful in that context too I think. I wrote a custom iterator for tiOPF object lists a while back that uses very simple selection syntax. This parser may be perfect for extending it...

----- "Michael Van Canneyt" schreef:

[snip on usage of new SQL parser]

I've only read about it am not a C# (or .net) buff, so I cannot comment on how the language-construct is translated into ITL or "how the magic occurs". Could be that it is an ad-hoc iterator-defintion, or that the statement makes an ad-hoc dynamic, iteratable type that is populated when the statement occurs. Either way, it is a powerful construct that allows one to make a terse 'selection' in an OO way.

Either way, I guess this may not be so easily construed and "translated" into pascal. You need to place the result into a variable. And that variable needs to be typed.

var resultobject : ???
begin

  resultobject := from
                      someobject.tlistproperty, sometdataset, somearray, somesettype
                  where
                      someobject.tlistproperty.someotherobject.property=somesettype
                      and somesettype.value=sstLoaded
                      and somearry.value=somedataset.fieldbyname('foo').value
                      and somedataset.fieldbyname('bar').value=someobject.tlistproperty.someotherobject.property2
                  groupby
                      somedataset.fieldbyname('foo')
                  select
                      new Record(sa := count(somearray.value), sf := somedataset.fieldbyname('foo').value);

...

foreach ...

end;

Anyway, that is probably something that should be discussed in fpc lists then. It is quite a powerful construct. I also believe that it works with providers. Maybe something can be done without going into the language itself through a library and given enough rtti?

kind regards,
Dimitri Smits

Dimitri Smits pisze:

or use pascal script...

Boguslaw