Failed to eliminate the ISO8601 collation for datetime fields, help.

edwinsn · 2014-07-03 09:17:33

Arnaud,

Mormot uses the custom ISO8601 collation for datetime fields, but I want my app to use sqlite built-in collations only to avoid the incompatibility with external sqlite tools - we always needs those tools during the development process, right?

After reading the manual, I tried the following and recreated the database file, but it seems that the collations doesn't change, any hint? I'm sure the TDateTime fields still use ISO8601, but not sure about unicodeString field yet since I've not tested it. I use XE4 32bit.

1 - all my record objects has a bass class called TSQLBaseModel.

2 - and in this TSQLBaseModel class I have overridden the InternalRegisterCustomProperties() method will looks like:

class procedure TSQLBaseModel.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
    Props.SetCustomCollationForAllRawUTF8('NOCASE');
    SetCustomCollationForAllDateTimeFields(TSQLRecordPropertiesHacker(Props), 'BINARY');
end;

And SetCustomCollationForAllDateTimeFields() is defined as:

class procedure TSQLBaseModel.SetCustomCollationForAllDateTimeFields(const
    aProps: TSQLRecordPropertiesHacker; const aCollationName: RawUTF8);
var i: integer;
begin
  if aProps = nil then
    Exit;

  if aProps.Fields.Count>length(aProps.fCustomCollation) then
    SetLength(aProps.fCustomCollation,aProps.Fields.Count);

  for i := 0 to aProps.Fields.Count-1 do
    if aProps.Fields.List[i].SQLFieldType=sftDateTime then
      aProps.fCustomCollation[i] := aCollationName;
end;

Last edited by edwinsn (2014-07-03 09:20:21)

ab · 2014-07-03 09:36:54

You can use the SynDBExplorer tool (sample 12) to access the SQLite3 database, which has all the expected collations built-in.
If you find missing features, feel free to ask.

Did you try to use the TSQLRecordProperties.SetCustomCollation() method directly instead of handling by hand fCustomCollation[]?
This is a risky approach, since we may change the implementation later.
And you won't need to use TSQLRecordPropertiesHacker trick.

Note that custom collations only works BEFORE the database is created.
Collations are set within CREATE TABLE ... command.

edwinsn · 2014-07-03 13:18:03

@ab, thanks for the help.

SynDBExplorer has some font displaying problem, I guess simply disabling form scaling will fix the problem, I'll try later, check this screenshot.
http://screenpresso.com/=Fuwof

And I checked SynDbExplorer again just now, apart from the problem mentioned above, its looks like it's good and should fit most of my needs.

I'd like to enhance it and contribute back the changes, but I use XE4 only, do I have to care about compatibility with other versions of Delphi and FPC?

Back to the collation issue, on the other hand, I want to make sure, if I change the default collations back to the sqlite built-in ones, will there be any side effects?

ab · 2014-07-03 14:17:53

Font display is annoyning - perhaps there are some tips to add to ensure the font won't be scaled.

You can send back your patches, I will ensure that it fit with all versions of Delphi - we want this sample to run with Delphi 6 up to XE6.
We may add some features available only for newer versions of Delphi (with conditional defines), but code should compile from Delphi 6 and up...

You would have a less accurate collation for Win1252 accents in respect to NOCASE, and your ISO-8601 content may be in diverse order, since textual ISO-8601 values order (BINARY) do not necessary match TDateTime values (ISO8601).

edwinsn · 2014-07-03 16:44:03

Thanks Ab.

Just set Scaled := False for all the forms, then VCL will not do its **incomplete and inaccurate** scaling.

If I do improve SynDbExplroer I'll send you the source.

ab · 2014-07-03 18:08:15

I've just disable buggy font scaling for SynDBExplorer.
See http://synopse.info/fossil/info/a91a29696362f914

Thanks!

mORMot Open Source

#1 2014-07-03 09:17:33

Failed to eliminate the ISO8601 collation for datetime fields, help.

#2 2014-07-03 09:36:54

Re: Failed to eliminate the ISO8601 collation for datetime fields, help.

#3 2014-07-03 13:18:03

Re: Failed to eliminate the ISO8601 collation for datetime fields, help.

#4 2014-07-03 14:17:53

Re: Failed to eliminate the ISO8601 collation for datetime fields, help.

#5 2014-07-03 16:44:03

Re: Failed to eliminate the ISO8601 collation for datetime fields, help.

#6 2014-07-03 18:08:15

Re: Failed to eliminate the ISO8601 collation for datetime fields, help.

Board footer