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

tech
Member
Registered: 2014-01-13
Posts: 107

forcing 'ID' by another field name on virtual table

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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.

Online

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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

thnx

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

I'm working on it...

Online

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

Good news

Offline

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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,

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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

Online

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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,

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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?

Online

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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.
sad

Online

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

In this case I'll use

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

thnx,

Offline

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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 sad :

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

Offline

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

mingda
Member
Registered: 2013-01-04
Posts: 121

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

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');

Offline

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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);

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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.

Online

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

tech
Member
Registered: 2014-01-13
Posts: 107

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

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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.
smile

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.

Online

Board footer

Powered by FluxBB