ORM mapping class fields to external table columns

When working with an ORM, you have mainly two possibilites:

Start from scratch, i.e. write your classes and let the ORM creates all the database structure - it is also named "code-first";
From an existing database, you define in your model how your classes map the existing database structure - this is "database-first".

We have just finalized ORM external table field mapping in mORMot, using e.g.
aModel.Props[aExternalClass].ExternalDB.MapField(..)
See this last commit.

So you can write e.g.

fProperties := TSQLDBSQLite3ConnectionProperties.Create(
  SQLITE_MEMORY_DATABASE_NAME,'','','');
VirtualTableExternalRegister(fExternalModel,
  TSQLRecordPeopleExt,fProperties,'PeopleExternal');
fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
  MapField('ID','Key').
  MapField('YearOfDeath','YOD');

Then you use your TSQLRecordPeopleExt table as usual from Delphi code, with ID and YearOfDeath fields:

The "internal" TSQLRecord class will be stored within the PeopleExternal external table;
The "internal" TSQLRecord.ID field will be an external "Key: INTEGER" column;
The "internal" TSQLRecord.YearOfDeath field will be an external "YOD: BIGINT" column;
Other internal published properties will be mapped by default with the same name to external column.

By default, the field / column mapping will be:

With your customize mapping, two fields are mapped differently:

Due to the design of SQLite3 virtual tables, and mORMot internals in its current state, the database primary key must be an INTEGER field to be mapped as expected by the ORM.

Next step is to allow on-the-fly conversion of values, from internal to external...
Which is required to use our ORM with an existing complex database.
For instance, it may help use another primary key (add a way to map any varchar(250) value from/to an integer value for a primary key), or map a hashed column of a limited set of values into a Delphi enumeration, or map a foreign key to some fixed list of items using also a Delphi enumeration.

Not so easy to implement...

I see at least several patterns:

My current best idea is that we may have to generate ALTER TABLE ADD COLUMN for a new INTEGER NON NULL UNIQUE column to the table, so that we will be able to use it as the primary key on an existing table.
Another option may be to maintain a separate IntegerID/Varchar250Key mapping (e.g. in one or several dedicated SQLite3 tables). But this may be slow and confusing, especially when the DB is accessed directly from other non-mORMot applications;
Last, we could use a callback to compute the ID to/from varchar(250), but we may have collision, so it could be also very difficult to work with.

What do you think?

Feedback is welcome on our forum, as usual.

Synopse Open Source

ORM mapping class fields to external table columns