Published TSQLRecord fields should not be mapped as ID fields.

[turkerali]

If a TSQLRecord field is to be mapped to an external database as an ID field, it should not be published. Otherwise CreateMissingTables raises an ESQLite3Exception.

[synopse]

Please give some feedback.

Otherwise I will close this PR.

[turkerali]

Hi Arnaud.

Let me give a simple example for this error. Here is my TSQLRecord definition:

TSQLUserDefs = class(TSQLRecord) private FUSER_ID: TID; FUSERNAME: RawUTF8; FUSERPASS: RawUTF8; published property USER_ID: TID read FUSER_ID write FUSER_ID; property USERNAME: RawUTF8 index 32 read FUSERNAME write FUSERNAME stored AS_UNIQUE; property USERPASS: RawUTF8 index 32 read FUSERPASS write FUSERPASS; end;

This record will be mapped to an external database as follows:

TestSchema := TSQLModel.Create([TSQLUserDefs], 'root'); fConnection := TSQLDBZEOSConnectionProperties.Create(ZeosDSN, '', '', ''); VirtualTableExternalMap(TestSchema, TSQLUserDefs, fConnection, 'testDB.user_defs').MapField('ID', 'USER_ID');

When you call CreateMissingTables function, program will fail with an exception:

An unhandled exception occurred at $004F1DFF: ESQLite3Exception: Error SQLITE_ERROR (1) [Step] using 3.35.5 - TSQLRestStorageExternal.Create: TSQLUserDefs: unable to create external missing field testDB.user_defs.USER_ID - SQL="ALTER TABLE testDB.user_defs ADD USER_ID bigint" $004F1DFF $004F1888 $004F0C30 $004F0C9B $004EE92B $004E7E1E $00402DBC

The problem is, you declared the USER_ID field in TSQLUserDefs, and then you tried to map it as the ID field via VirtualTableExternalMap. That triggers the ESQLite3Exception. If you do not declare USER_ID field in TSQLUserDefs, the code will run without any errors, and the USER_ID field will be created properly. My fix detects this situation beforehand, generates an exception and warns the user as follows:

An unhandled exception occurred at $004839DB: EModelException: Mapping failed: testDB.user_defs.USER_ID is defined as "published" and cannot be mapped as ID.

[synopse]

You should better have use our forum for discussion.

Your additional information confirm that this is not the proper way of writing TSQLUserDefs fields. Don't create any TSQLUserDefs.USER_ID field but just map the TSQLUserDefs.ID existing field into USER_ID.

I guess you are making confusion between the TSQLRecord fields and the database columns/fields. No need to name your TSQLUserDefs fields with the exact database column names: use regular pascal names, THEN map the fields into external database columns. Please read the documentation again.