case sensitiv delimiter

danielkuettner · 2015-05-18 20:25:35

Is there a way to make mORMot case-sensitiv?

If I use mapfield for externaldb then creation of table is fine, but mORMot then tries to create fields, that was already created before and raise an exception.

Daniel

ab · 2015-05-18 21:03:50

Do you mean case insensitive?

danielkuettner · 2015-05-19 04:17:07

No casesensitiv.
For mssql I need a delimiter of [Col], for firebird "Col".

For tablename I can do it with VirtualTableExternalRegister;

but for Tablenames I see no way.

ab · 2015-05-19 06:49:12

I'm sorry I do not get it exactly what you expect.

I've added a new itoNoCreateMissingField option to void the creation of missing fields in TSQLRestServerDB.CreateMissingTables.
See http://synopse.info/fossil/info/6972509ea3
It may help to circumvent the issue in the meanwhile.

ab · 2015-05-19 07:53:27

OK.
I think I've understood your problem.

I've added the new dedicated TSQLRecordPropertiesMapping.Options settings, e.g. rpmNoCreateMissingTable, rpmNoCreateMissingField or rpmMissingFieldNameCaseSensitive.
It would let TSQLRestStorageExternal.Create behave as you expect, I hope.
See http://synopse.info/fossil/info/73ec73141b

danielkuettner · 2015-05-19 10:17:16

My first test was successful.

Thanks,
Daniel

danielkuettner · 2015-05-19 12:25:29

Hi Arnaud,

there is one thing to change in mORMotDB.pas:

in constructor TSQLRestStorageExternal.Create is a local procedure GetFields.

The fTableName is already quoted with VirtualTableExternalRegister ([] or "") and will be quoted twice in SynDBZeos by TSQLDBZeosConnectionProperties.GetFields.

Because of this Zeos can't get metadata for the double-quoted Tablename.

Daniel

ab · 2015-05-19 12:42:37

Should be done by http://synopse.info/fossil/info/15abf79914

Thanks for the feedback!

danielkuettner · 2015-05-19 12:49:55

Thanks, AB.

Next error in mORMotDB, function TSQLRestStorageExternal.InternalFieldNameToFieldExternalIndex.

InternalFieldName is 'Gruppe', external is '[Gruppe]', Result is -1 -> raise

Daniel

ab · 2015-05-19 13:48:39

Why is external [Gruppe] ?

danielkuettner · 2015-05-19 13:51:42

Because of MapField?

ab · 2015-05-19 13:52:30

So you mapped your internal field into '[Gruppe]' ?

The code does not support it yet, I'm afraid.

danielkuettner · 2015-05-19 14:03:23

I'm mapping only external fields to [Gruppe].

ab · 2015-05-19 14:27:52

Yes, but this is unsupported by the current code base.
Such [] or "" quoting are very DB-specific, and low-level DB libraries do not handle them the same way.
So making a generic support of it may be quite error prone.

danielkuettner · 2015-05-19 15:19:22

I understand. But CreateMissingTables works very well with your "small" changes.
To use your framework really and to avoid coding all by myself, I need a way to work case-sensitiv. My javascript client is also casesensitiv and the DB-backend could change dependent on client-choices or technical changes in time.
I have to be sure, that tablenames or fieldnames were created as I want it with my TSQLRecords.

Do you see a way to support this? At the moment I've the problem only in TSQLRestBatch.

Daniel

danielkuettner · 2015-05-19 15:35:41

function TSQLRestStorageExternal.InternalFieldNameToFieldExternalIndex(
const InternalFieldName: RawUTF8): integer;
begin
result := StoredClassRecordProps.Fields.IndexByNameOrExcept(InternalFieldName); <-- this is good; result for 'Gruppe' is 0
result := fFieldsInternalToExternal[result+1]; <-- but here fFieldsInternalToExternal has value of (0, -1, -1, -1, -1, -1, -1, -1, -1, -1) and results in -1
end;

danielkuettner · 2015-05-19 15:44:24

problem seems to be here:

function TSQLRecordPropertiesMapping.ExternalToInternalIndex(
const ExtFieldName: RawUTF8): integer;
begin
if IdemPropNameU(ExtFieldName,RowIDFieldName) then
result := -1 else begin
// search for customized field mapping
for result := 0 to high(fFieldNames) do
if IdemPropNameU(ExtFieldName,fFieldNames[result]) then <-- fFieldNames[result] is [Gruppe]
exit;
result := -2; // indicates not found
end;
end;

Could you make a fix here like:

if IdemPropNameU(ExtFieldName,Unquote(fFieldNames[result])) then

and unquote removes all suche delimiters ([, ", ...) but only if rpmMissingFieldNameCaseSensitive in TSQLRecordPropertiesMapping.Options?

Daniel

danielkuettner · 2015-05-19 16:05:00

With this code my TRestBatch works fine:

function TSQLRecordPropertiesMapping.ExternalToInternalIndex(
  const ExtFieldName: RawUTF8): integer;

  function UnQuotedFieldNames(aFieldName: RawUTF8): RawUTF8;
  var
    i: Integer;
  begin
    Result:= '';
    for i:= 1 to Length(aFieldName) do
      if not (aFieldName[i] in ['[', ']', '"', '''']) then  <-- here some other quotes are still missing, I don't know what mysql, postgres ... are using
        Result:= Result + aFieldName[i];

  end;

begin
  if IdemPropNameU(ExtFieldName,RowIDFieldName) then
    result := -1 else begin
    // search for customized field mapping
    for result := 0 to high(fFieldNames) do
      if IdemPropNameU(ExtFieldName,UnQuotedFieldNames(fFieldNames[result])) then
        exit;
    result := -2; // indicates not found
  end;
end;

Last edited by danielkuettner (2015-05-19 16:05:57)

ab · 2015-05-20 06:08:36

Should be implemented by http://synopse.info/fossil/info/db5c0c622e
But I do not know if it would be sufficient.
What is nice is that it should not break any existing code.

Thanks for the feedback.

danielkuettner · 2015-05-20 06:52:58

Thanks.
I have to test more, sorry.
My Batch was successful, but only with MSSQL, with FB not. MSSQL accepts Gruppe also without [, in Fb Gruppe <> "Gruppe".

In mORMotDB line 1810 ExternalFields will created only from Fieldnames from JSON ('Gruppe') and fFieldsInternalToExternal array. Here I would need access to Option if CaseSensitiv or the "real" ExternalField-list (with [ or ").

Perhaps second way this is already possible, but I don't know.

Daniel

danielkuettner · 2015-05-20 07:06:29

There is fFieldNames in Debugger (but I don't the class, instance), here we have the right external Fieldnames (e.g. "Gruppe").

In TSQLRestStorageExternal.InternalBatchStop there is fStorageClassProps with "right" external fieldnames.

Last edited by danielkuettner (2015-05-20 07:13:07)

danielkuettner · 2015-05-20 08:04:24

So it works also with firebird:

function TSQLRestStorageExternal.JSONDecodedPrepareToSQL(
  var Decoder: TJSONObjectDecoder; out ExternalFields: TRawUTF8DynArray;
  out Types: TSQLDBFieldTypeArray; Occasion: TSQLOccasion;
  BatchOptions: TSQLRestBatchOptions): RawUTF8;
var f,k: Integer;
begin
  SetLength(ExternalFields,Decoder.FieldCount);
  for f := 0 to Decoder.FieldCount-1 do begin
    k := InternalFieldNameToFieldExternalIndex(Decoder.FieldNames[f]);
    if k<0 then
      raise ESQLDBException.CreateUTF8(
        '%.JSONDecodedPrepareToSQL: Unknown field "%" in %',
        [self,Decoder.FieldNames[f],StoredClass]);
    //fFieldNames
    if IsRowID(Pointer(Decoder.FieldNames[f])) then
      ExternalFields[f] := fFieldsExternal[k].ColumnName
    else
      ExternalFields[f] := fStoredClassProps.ExternalDB.FieldNameByIndex(k-1);

    Types[f] := fFieldsExternal[k].ColumnType;
  end;

This line have to be fixed in the right way:

ExternalFields[f] := fStoredClassProps.ExternalDB.FieldNameByIndex(k-1);

Perhaps you know how?

ab · 2015-05-20 10:35:37

You may try http://synopse.info/fossil/info/ff05ffb48b

Since the order in fFieldsExternal[] may not match the order in fStoredClassProps.ExternalDB.

danielkuettner · 2015-05-20 12:44:04

Läuft!

mORMot Open Source

#1 2015-05-18 20:25:35

case sensitiv delimiter

#2 2015-05-18 21:03:50

Re: case sensitiv delimiter

#3 2015-05-19 04:17:07

Re: case sensitiv delimiter

#4 2015-05-19 06:49:12

Re: case sensitiv delimiter

#5 2015-05-19 07:53:27

Re: case sensitiv delimiter

#6 2015-05-19 10:17:16

Re: case sensitiv delimiter

#7 2015-05-19 12:25:29

Re: case sensitiv delimiter

#8 2015-05-19 12:42:37

Re: case sensitiv delimiter

#9 2015-05-19 12:49:55

Re: case sensitiv delimiter

#10 2015-05-19 13:48:39

Re: case sensitiv delimiter

#11 2015-05-19 13:51:42

Re: case sensitiv delimiter

#12 2015-05-19 13:52:30

Re: case sensitiv delimiter

#13 2015-05-19 14:03:23

Re: case sensitiv delimiter

#14 2015-05-19 14:27:52

Re: case sensitiv delimiter

#15 2015-05-19 15:19:22

Re: case sensitiv delimiter

#16 2015-05-19 15:35:41

Re: case sensitiv delimiter

#17 2015-05-19 15:44:24

Re: case sensitiv delimiter

#18 2015-05-19 16:05:00

Re: case sensitiv delimiter

#19 2015-05-20 06:08:36

Re: case sensitiv delimiter

#20 2015-05-20 06:52:58

Re: case sensitiv delimiter

#21 2015-05-20 07:06:29

Re: case sensitiv delimiter

#22 2015-05-20 08:04:24

Re: case sensitiv delimiter

#23 2015-05-20 10:35:37

Re: case sensitiv delimiter

#24 2015-05-20 12:44:04

Re: case sensitiv delimiter

Board footer