UnifedSQL functions not implemented in SQLite

Elias Zurschmiede · Post by **Elias Zurschmiede** » Tue 14 Jun 2011 12:45

Hi

Some (for me important) scalar function for UnifiedSQL seems not to be implemented in SQLite-Provider. Have implemented this functions in SQLiteUniProvider.pas. May some other users need them too. Hope to see this changes in the next release.

Functions: YEAR,MONTH,DAY, DATEDIFF(second,minute,hour,day,month,year)

Implementation:

Code: Select all

  LiteFunctions.AddObject('YEAR', TObject(PChar('CAST(strftime(''%%Y'', %s) AS INTEGER)'))); // EZ: implemented according to http://www.sqlite.org/lang_datefunc.html
  LiteFunctions.AddObject('MONTH', TObject(PChar('CAST(strftime(''%%m'', %s) AS INTEGER)'))); // EZ: implemented according to http://www.sqlite.org/lang_datefunc.html
  LiteFunctions.AddObject('DAY', TObject(PChar('CAST(strftime(''%%d'', %s) AS INTEGER)'))); // EZ: implemented according to http://www.sqlite.org/lang_datefunc.html

  //LiteFunctions.AddObject('DATEDIFF', TObject(PChar('EXTRACT(%s FROM (%2:s - %1:s))')));
  LiteFunctions.AddObject('DATEDIFF', TObject(PChar('CASE ''%s''' +   // EZ: partially implemented
    ' WHEN ''year'' THEN (julianday(%2:s) - julianday(%1:s)) / 365.25' +  // Delphi.DateUtils.pas.ApproxDaysPerYear
    ' WHEN ''month'' THEN (julianday(%2:s) - julianday(%1:s)) / 30.4375' + // Delphi.DateUtils.pas.ApproxDaysPerMonth
    ' WHEN ''day'' THEN julianday(%2:s) - julianday(%1:s)' +
    ' WHEN ''hour'' THEN (julianday(%2:s) - julianday(%1:s)) * 24' +
    ' WHEN ''minute'' THEN (julianday(%2:s) - julianday(%1:s)) * 24 * 60' +
    ' WHEN ''second'' THEN (julianday(%2:s) - julianday(%1:s)) * 24 * 60 * 60' +
    ' ELSE NULL' +
    ' END')));

greetings
Elias

AlexP · Post by **AlexP** » Wed 15 Jun 2011 06:44

Hello,

Thank you for the information.
We'll look through your code and will try to include it in one of the nearest product versions.
Also if you need any specific features that are not available in UniDAC at the moment, you can use user functions for adding the features you need. You can get more detailed information on registering user functions in the help file in the TLiteUtils.RegisterFunction Method topic.

Elias Zurschmiede · Post by **Elias Zurschmiede** » Wed 15 Jun 2011 07:45

Hi Alex

Thanks for your fast reply. I already use the user functions. I'm talking about UnifiedSQL base functions here. IMHO the unified sql functions and macros should work on any provider. This is one of the big pluses of your very cool product.

greetings
Elias

AlexP · Post by **AlexP** » Fri 17 Jun 2011 07:40

Hello,

We will try to implement your suggestion as soon as possible. Also if you have any other suggestions on expanding functionality or adding new features, you can post them on our UserVoice page at http://devart.uservoice.com/forums/1046 ... components , and if enough people vote for it, it will be implemented (the more people vote for it, the sooner it will be implemented).