Alternatives for the LIMIT 1 additive

Bascy · 2012-08-15 10:43:37

Busy implementing a native NexusDB implementation, i ran into a difference of SQL implementation.
While SQLite is using LIMIT 1 to only retrieve one record with a SQL statement, NexusDB uses TOP 1

How can i best implement this?
SHould we add LIMIT 1 to the implementation specific constants as DB_FIELDS[] and DB_SERVERTIME[] ?

Edit:
The place of TOP 1 in the SELECT statement is also different form the LIMIT 1
<TOP n> should be directly behind the SELECT keyword

Last edited by Bascy (2012-08-15 12:01:05)

ab · 2012-08-15 11:51:08

Yes it could be a good idea, indeed.

Those constant arrays should be completed, also to other SQL table/column metadata patterns.

Bascy · 2012-08-15 11:53:52

How can i determine the database implementation used in i.e. TSQLRest?

ab · 2012-08-15 12:23:04

There is no direct use of SQLite3DB within SQLite3Commons unit, by design, to keep the SOLID principles.

We would need some kind of on-the-fly conversion of "LIMIT 1" SQL request into the DB-dependent value in TSQLRestServerStaticExternal.AdaptSQLForEngineList() method.

I'll implement it soon. No need to do it on your own, it is perhaps better to way for my implementation.

mpv · 2012-08-15 16:13:43

this query syntax different in all databases

for MS SQL syntax is: select top1 ......
for Oracle: select .... where rowNum < 1

ab · 2012-08-15 16:24:11

you are right: that is why we need some global constants, per database.

Bascy · 2012-08-16 10:19:07

Another difference: date literals in parameters, for NexusDB they need to be formatted like DATE '2012-01-29'

ab · 2012-08-16 11:29:26

'2012-01-29' is the ISO-8601 format, which is the one used by SQL standard I suspect.
Is DATE'2002-11-24' mandatory, or is '2002-11-24' enough?

There is some improvement of date handling to be done with mORMot, in some cases.
When used in BATCH mode, there is no problem, but in other areas, they may be a problem if the underlying engine does not accept ISO-8601 date/time encoding for its corresponding columns.

Bascy · 2012-08-16 11:32:51

The DATE keyword is mandatory for NexusDB, so I guess there will be another constant array referring to NexusDBIso8601ToDate methods like the already present OracleSQLIso8601ToDate

ab · 2012-08-16 11:55:15

And if you create a parameter and assign an ISO-8601 string to it at binding time?

Will it work with NexusDB (as it does with Oracle, for instance)?
Or does NexusDB limit bind parameter to be exactly of the column type, without runtime conversion?

Bascy · 2012-08-16 12:06:17

In my current implementation of NexusDB i use the original TQuery.Params[] created when assigning the SQL statement to the TQuery.SQL.Text property
The binding is done with BindTextU and this assigns the Iso8601 string to the param value .AsString
As far as i know the TParam object does not know what kind of datatype is expected ... so i cannot make a special case when a date is given

Bascy · 2012-08-21 08:58:14

Any news on this issue? Or any idea when you will be able to implement this?

ab · 2012-08-21 14:44:41

I've made the following modifications to the SynDB units:
- added TSQLDBConnectionProperties.DBMS property, and huge code refactoring among all SynDB* units for generic handling of DBMS-specific properties;
- added TSQLDBConnectionProperties.AdaptSQLLimitForEngineList for handling the LIMIT # statement in a database-agnostic form.
See http://synopse.info/fossil/info/403804e7d0

So the LIMIT # statement is now handled in an abstract way, and converted on the fly when using external engines.

The supplied regression tests, run in TTestExternalDatabase.AutoAdaptSQL, are the following:

  Test2('select rowid,firstname from PeopleExt where rowid=2',
        'select id,firstname from SampleRecord where id=2');
  Test2('select rowid,firstname from PeopleExt where rowid=2 order by RowID',
        'select id,firstname from SampleRecord where id=2 order by ID');
  Test2('select rowid,firstname from PeopleExt where firstname like :(''test''): order by lastname',
        'select id,firstname from SampleRecord where firstname like :(''test''): order by lastname');
  SQLOrigin := 'select rowid,firstname from PeopleExt where rowid=2 limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and id=2');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where id=2');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where id=2');
  Test(dMySQL,true,'select id,firstname from SampleRecord where id=2 limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where id=2 limit 2');
  SQLOrigin := 'select rowid,firstname from PeopleExt where rowid=2 order by LastName limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and id=2 order by LastName');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where id=2 order by LastName');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where id=2 order by LastName');
  Test(dMySQL,true,'select id,firstname from SampleRecord where id=2 order by LastName limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where id=2 order by LastName limit 2');
  SQLOrigin := 'select rowid,firstname from PeopleExt where firstname=:(''test''): limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and firstname=:(''test''):');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where firstname=:(''test''):');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where firstname=:(''test''):');
  Test(dMySQL,true,'select id,firstname from SampleRecord where firstname=:(''test''): limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where firstname=:(''test''): limit 2');
  SQLOrigin := 'select id,firstname from PeopleExt limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord');
  Test(dMySQL,true,'select id,firstname from SampleRecord limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord limit 2');

So I guess this will be pretty simple to add NexusDB support here.

Still some enhancements to be done at the date / time level, when Iso-8601 text is not handled natively for date/time columns.

Bascy · 2012-08-22 07:34:23

Ok, thanks for the adjustments.

Is it possible to add my own native database implementation, without having to make major alterations to your sources?
With the current setup I have to add dNexusDB to the TSQLDBDefinition. But when i do that, i also have to alter all the const arrays containing database specific implementations ..
So i was wondering if that was the way to go ..

ab · 2012-08-22 13:08:56

Yes, this is exactly the way to go.
Adding a new item to TSQLDBDefinition enumeration, then filling all the constant arrays is the minimum.
Then you'll have to do some "tuning", according to NexusDB itself.
I suspect than "BATCH" mode is to be added in your case, since you expect the best performance possible.

In the latest commit, any direct or virtual-table based insertion to the external database will now use a binding matching the exact time of each column: it will e.g. allow to support DBMS which does not accept date/time to be supplied as ISO-8601 text, and make more efficient data conversion (like avoid conversion to floating-point from a currency value) - code shared with BATCH mode and newly added TSQLRestServerStaticExternal.ExecuteFromJSON() protected method.
So it should fix your "date/time" binding error, and also probably increase performance and robustness of the external database processing.
See http://synopse.info/fossil/info/036c93bf9b

mORMot Open Source

#1 2012-08-15 10:43:37

Alternatives for the LIMIT 1 additive

#2 2012-08-15 11:51:08

Re: Alternatives for the LIMIT 1 additive

#3 2012-08-15 11:53:52

Re: Alternatives for the LIMIT 1 additive

#4 2012-08-15 12:23:04

Re: Alternatives for the LIMIT 1 additive

#5 2012-08-15 16:13:43

Re: Alternatives for the LIMIT 1 additive

#6 2012-08-15 16:24:11

Re: Alternatives for the LIMIT 1 additive

#7 2012-08-16 10:19:07

Re: Alternatives for the LIMIT 1 additive

#8 2012-08-16 11:29:26

Re: Alternatives for the LIMIT 1 additive

#9 2012-08-16 11:32:51

Re: Alternatives for the LIMIT 1 additive

#10 2012-08-16 11:55:15

Re: Alternatives for the LIMIT 1 additive

#11 2012-08-16 12:06:17

Re: Alternatives for the LIMIT 1 additive

#12 2012-08-21 08:58:14

Re: Alternatives for the LIMIT 1 additive

#13 2012-08-21 14:44:41

Re: Alternatives for the LIMIT 1 additive

#14 2012-08-22 07:34:23

Re: Alternatives for the LIMIT 1 additive

#15 2012-08-22 13:08:56

Re: Alternatives for the LIMIT 1 additive

Board footer