forcing 'ID' by another field name on virtual table

tech · 2014-03-09 16:44:33

Hi,

I've mapped an existing mssql table that have primary key field 'NUM'. I want to force mORMot to use this field as its internal 'ID' because I can't create 'ID' field and want to continue incremental of 'NUM'.
Is this possible or not?

Thx.

ab · 2014-03-09 19:32:04

The ORM expects the index field to be ID for external tables.

Adding a field mapping from internal (e.g. ID) to external (e.g. MYKEY) fields is on the roadmap.
But not yet implemented.

tech · 2014-03-10 09:41:41

Ok, I'll add the ID to the virtual table and waiting for the ID field mapping feature.

thnx

ab · 2014-03-10 10:23:16

I'm working on it...

tech · 2014-03-10 10:40:18

Good news

tech · 2014-03-13 07:08:15

Hi Arnaud,

I have the same problem with a mySql table that have 'ID' like this `id` varchar(250) NOT NULL.
when I get the records, the ID only takes integers from the start until it finds an alpha char of the mySQL ID field.
Example :

mySQL_ID = 1213E30377
SQLite_ID = 1213

How I can retrieve the entire primary key in order to change the record?

Thnx,

ab · 2014-03-13 09:25:00

The ORM expects ID primary key to be of kind Integer, not text...

tech · 2014-03-13 09:41:11

I know but I can't modify the table structure. How can I get around this problem ?
My idea is to have a computed class property on delphi that copy the content of the mySQL ID for using it later.
Is there a way to have this?

Thnx,

ab · 2014-03-13 13:46:56

We have just finalized ORM external table field mapping, using e.g.

aModel.Props[aExternalClass].ExternalDB.MapField(..)

including regression tests.
See http://synopse.info/fossil/info/cae7936c88

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.ID field will be an external "Key: INTEGER" column.
- The "internal" TSQLRecord.YearOfDeath field will be an external "YOD: BIGINT" column.

What you need is a way to map any varchar(250) value from/to an integer value...
Not easy.

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?

tech · 2014-03-13 15:14:22

Great work Arnaud,

I'll test the mapping functionnality later.
I think that the easy way is to map the Varchar250 key into a RawUTF8 field (<> SQLite ID) and then specify it for the update/delete operations.

Merci beaucoup

ab · 2014-03-13 16:25:59

tech wrote:

I think that the easy way is to map the Varchar250 key into a RawUTF8 field (<> SQLite ID) and then specify it for the update/delete operations.

I see two problems for it:
1. SQLite3 primary key is by definition an integer, and especially for a virtual table, as we use with mORMot's external tables - see http://blog.synopse.info/post/2011/05/1 … -framework
2. We defined the ID as integer, or as a TSQLRecord class property for one-to-many or one-to-one

Sadly, the newly introduced WITHOUT ROWID option - see http://sqlite.org/withoutrowid.html - by which you can use e.g. a TEXT field instead of the INTEGER PRIMARY KEY, just does not work with virtual tables.

This is why I did not list your proposal in my previous answer, since when I considered it, it was simply not feasible.

tech · 2014-03-13 17:25:30

In this case I'll use

globalClient.EngineExecute('UPDATE TABLE SET FIELD1 = VAL1 WHERE ID='+ Varchar250key)

thnx,

tech · 2014-03-17 18:39:22

I've tested the mapping feature and it works fine like that

globalModel.Props[TSQLmyTable].ExternalDB.MapField('mySQLID', 'ID');

But I get the 'no such column:ID' error when I try this update :

globalClient.EngineExecute('UPDATE myTABLE SET FIELD1 = VAL1 WHERE ID='+ Varchar250key);

mingda · 2014-03-18 00:48:08

see blog, perhaps your code is converse,

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

tech · 2014-03-18 10:05:48

That is what I do :

aProps := TSQLDBZeosConnectionProperties.Create(TSQLDBZeosConnectionProperties.URI(dMySQL, myServer, 'libmysql.dll', False),
                myDB, myUser, myPwd);
VirtualTableExternalRegister(globalModel, TSQLmyTable, aProps, 'TableExternal');
globalModel.Props[TSQLmyTable].ExternalDB.MapField('myID', 'ID');

All this works fine and I retreive the varchar250 key, but I got error when I try to update one record on the external table via varchar250 key.

globalClient.EngineExecute('UPDATE myTABLE SET FIELD1 = VAL1 WHERE ID='+ Varchar250key);

ab · 2014-03-19 16:04:04

IMHO you use MapField() with inverted parameters.
It should be internal,external not external,internal.

And internally, the ID field is hard coded to be the ORM primary key.

tech · 2014-03-20 09:56:49

I use the MapField() right.On the external mysql table I've ID field and I've mapped it into myID field of the internal table.

ab · 2014-03-20 14:49:37

If you use the SQL from client side, it will be passed through the virtual table process of SQlite3.
So it will expect the "internal name" in the SQL.
On the client, you should use myID field.

But virtual table translation is a bit slower - so you may want to create a service on the server side, which will call directly the external table.
In all cases, you are by-passing the ORM so writing such direct SQL statements on the client side is just a wrong idea.
Please read the "Daily ORM" section of the SAD 1.18 pdf.
I strongly advice you switch to true n-Tier / SOA architecture, and let the SQL on the server side only.

mORMot Open Source

#1 2014-03-09 16:44:33

forcing 'ID' by another field name on virtual table

#2 2014-03-09 19:32:04

Re: forcing 'ID' by another field name on virtual table

#3 2014-03-10 09:41:41

Re: forcing 'ID' by another field name on virtual table

#4 2014-03-10 10:23:16

Re: forcing 'ID' by another field name on virtual table

#5 2014-03-10 10:40:18

Re: forcing 'ID' by another field name on virtual table

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

Re: forcing 'ID' by another field name on virtual table

#7 2014-03-13 09:25:00

Re: forcing 'ID' by another field name on virtual table

#8 2014-03-13 09:41:11

Re: forcing 'ID' by another field name on virtual table

#9 2014-03-13 13:46:56

Re: forcing 'ID' by another field name on virtual table

#10 2014-03-13 15:14:22

Re: forcing 'ID' by another field name on virtual table

#11 2014-03-13 16:25:59

Re: forcing 'ID' by another field name on virtual table

#12 2014-03-13 17:25:30

Re: forcing 'ID' by another field name on virtual table

#13 2014-03-17 18:39:22

Re: forcing 'ID' by another field name on virtual table

#14 2014-03-18 00:48:08

Re: forcing 'ID' by another field name on virtual table

#15 2014-03-18 10:05:48

Re: forcing 'ID' by another field name on virtual table

#16 2014-03-19 16:04:04

Re: forcing 'ID' by another field name on virtual table

#17 2014-03-20 09:56:49

Re: forcing 'ID' by another field name on virtual table

#18 2014-03-20 14:49:37

Re: forcing 'ID' by another field name on virtual table

Board footer