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.