primary key fields in an existing table & and other indexes

dcoun · 2021-12-05 19:17:47

I have a table with an existing Integer primary key index in an external database and I map to the Virtual Sqlite table:

VirtualTableExternalRegister(model,TOrmmytable,extrndb,'mytable');

if this primary key is not named as 'ID', I can map it with:

 model.Props[TOrmmytable].ExternalDB.MapField('ID','mycustomname');

I am issuing the following:

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,@sum);

I am getting the following error:
Project Project1.exe raised exception class ERestStorage with message 'TRestStorageExternal.EngineList(ReturnedRowCount<>nil) for TOrmmytable'.

With the follwoing it works and I am getting back all fields including the RowID as 'ID':

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,nil);

if this table is not external and it is TRestStorageInMemory then the RowID as ID is not returned for the same query and I do not get the above error.

I can use the RowID in the sql query but it can not retrieved. How can I change that?

I noticed also that if an Database integer field is declared in the Torm class as TRecordReference then when creating the database table it has also an non unique index
For a unique index, I must use the "stored AS_UNIQUE" in the property in the class definition
Multi-field indexes are automatically created in a other way or should I override the InitializeTable ?
In case I am using the InitializeTable, is there a way to to read if an index already exists?

Thank you in advance

Last edited by dcoun (2021-12-05 20:32:46)

dcoun · 2021-12-05 20:00:56

What is the reason for this error? TRestStorageExternal.EngineList(ReturnedRowCount<>nil)
I really can not understand it

dcoun · 2021-12-05 20:10:00

I think the above error is originated from mormot.orm.server line 1023

function TRestOrmServer.EngineList(const SQL: RawUtf8; ForceAjax: boolean;
  ReturnedRowCount: PPtrInt): RawUtf8;
var
  rest: TRestOrm;
  sqladapted: RawUtf8;
begin
  sqladapted := SQL;
  rest := InternalAdaptSql(fModel.GetTableIndexFromSqlSelect(SQL, false), sqladapted);
  if rest = nil then
    result := MainEngineList(SQL, ForceAjax, ReturnedRowCount)
  else
    result := rest.EngineList(sqladapted, ForceAjax, ReturnedRowCount);
end;

ab · 2021-12-05 21:00:52

I am very sorry, but I don't understand the exact problem you are facing.

dcoun · 2021-12-05 21:28:31

The configuration is shown here: https://synopse.info/forum/viewtopic.php?id=6069

I am running the following code:

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,@sum);

if mytable is a table mapped to an external db, I am getting the following error:
Project Project1.exe raised exception class ERestStorage with message 'TRestStorageExternal.EngineList(ReturnedRowCount<>nil) for TOrmmytable'.

if mytable is a table of type TRestStorageInMemory I get no error

Second problem in the same configuration:
Same Tormmytable definition, which is created as mapped to an external database and the second test is created as TRestStorageInMemory
As external database, with the following code I am getting back the RowID as ID.

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,@sum);

If the same Tormmytable is created as TRestStorageInMemory with the above code, I am not getting back the RowID as ID.
How can I change this?
I apologize for the complex way of presenting it. You have right

ab · 2021-12-06 08:02:07

ReturnedRowCount is just not supported with TRestStorageExternal.

The reason is because the external DB ISqlDbStatement.ExecutePreparedAndFetchAllAsJson() method does not provide this information.

Why do you need it at this stage?
You have the JSON, so you can parse the content.

I have just added this ReturnedRowCount parameter for most providers, but Remote DB, which does not support it in its protocol.

dcoun · 2021-12-06 08:09:16

ok, I will not use it.
Thank you very much for adding this feature.
I am afraid of using it as remoteDB is something I will need in the near future.
You have right, I can evaluate the JSON for the moment

Concerning the missing RowID if the table is TRestStorageInMemory, is it possible to config it to be returned?
thank you in advance

Last edited by dcoun (2021-12-06 08:49:14)

dcoun · 2021-12-06 08:55:50

To help more to unify them:
If I issue the following queries:
1. Select RowID from mytable Limit 2
2. Select ID from mytable limit 2
3. Select * from mytable limit 2

For an external table it returns:
Query 1. [{"ID":1},{"ID":2}]
Query 2. [{"ID":1},{"ID":2}]
Query 3. [{"ID":1,"test":3},{"ID":2,"test":5}]

For a table in TRestStorageInMemory it returns:
Query 1. [{"rowid":1},{"rowid":2}]
Query 2. Error no such column: ID
Query 3. [{"test":3},{"test":5}]

Last edited by dcoun (2021-12-06 09:00:26)

ab · 2021-12-06 13:21:25

I am not able to reproduce your problem.

Here is what I tried:
https://github.com/synopse/mORMot2/comm … 1a92b48a3d

dcoun · 2021-12-06 13:53:37

I have check the tests and they pass to me too

Do you believe the problem is due to the definition of the model?

The Torm classes derive from Torm and they are created as follows in the model:

type Dtablclasses=record cl:Tormclass; tb,ID:string; end;

result:=tormmodel.Create(MasterdataArr+LiveDBArr,'mine');
for n:=0 to high(MasterdataClasses) do begin
result.VirtualTableRegister(MasterdataClasses[n].cl,TOrmVirtualTableBinary);
result.Props[MasterdataClasses[n].cl].Kind:=ovkCustomAutoID; end;

could the ovkCustomAutoID caused all this trouble?

I am now thinking that it probably caused by the ovkCustomAutoID;
I do not want the Torm classes of the model to be derived from TormAutoid as I am using them in an other TrestremoteDB instance to do the

I have search the forum but I did not found a way to set the customID in either ovkCustomForcedID and ovkCustomAutoID

Last edited by dcoun (2021-12-06 13:54:50)

dcoun · 2021-12-06 14:08:14

I can see now that ovkCustomForcedID is not supported for static tables and ovkCustomAutoID can be set probably by adding a TID type published property in the derived Torm class
But still a rowID exists except the TID I created

dcoun · 2021-12-06 14:17:02

Let me describe more my situation
I have 2 TrestserverDB

I am first creating a TrestserverDB with a model that has the tables that in the second server will exist in TRestStorageInMemory

function createMasterDataModel(extrndb:TSqlDBConnectionProperties):TOrmModel;
begin
 result:=tormmodel.Create(MasterdataArr,'dbroot');
 for n:=0 to high(MasterdataClasses) do begin
  VirtualTableExternalRegister(result,MasterdataClasses[n].cl,extrndb,MasterdataClasses[n].tb);
  with result.Props[MasterdataClasses[n].cl] do begin 
   ExternalDB.MapAutoKeywordFields;
   if MasterdataClasses[n].ID<>'' then ExternalDB.MapField('ID',MasterdataClasses[n].ID);
  end;
 end;
end;
....

srvrepl:=TRestServerDB.Create(createMasterDataModel(dbmormot));
srvrepl.Model.Owner:=srvrepl; 
srvrepl.CreateMissingTables;

I am creating a second TrestserverDB that has the previous Tormclasses plus others that they are external to the same database
I am using the first server to retrieve frequently multithread used value-sets to create in the second server a dozen of TRestStorageInMemory tables.

I am coping data in the following way:

var mmtabls:TRestStorageInMemoryDynArray;
...
for n:=0 to high(MasterdataClasses) do begin 
mmtabls[n]:=(srvmem.Orm as TRestOrmServer).StaticVirtualTable[MasterdataClasses[n].cl] as TRestStorageInMemory; 
mmtabls[n].dropvalues(false); 
mmtabls[n].LoadFromJson(srvrepl.Orm.RetrieveListJson(MasterdataClasses[n].cl,'',[]););
end;

The above TRestStorageInMemory tables in the TRestStorageInMemoryDynArray are in the second TrestserverDB created with the model that it is shown my previous post

Last edited by dcoun (2021-12-06 14:21:53)

ab · 2021-12-06 14:43:20

So the problem is from mmtabls[n] or from the main srvrepl?
Is the virtual tables mechanism involved?

Try to use the debugger and find out where there is something wrong.

dcoun · 2021-12-06 14:57:15

ab wrote:

So the problem is from mmtabls[n] or from the main srvrepl?

using mmtabls[0].ExecuteJson([],sqlquery) with the above queries I am getting:

Query 1. [{"rowID":1},{"rowID":2}]
Query 2. [{"rowID":1},{"rowID":2}]
Query 3. [{"rowID":1,"test":3},{"rowID":2,"test":5}]

It is srvrepl (TrestserverDB) that returns the following

Query 1. [{"rowid":1},{"rowid":2}]
Query 2. Error no such column: ID
Query 3. [{"test":3},{"test":5}]

dcoun · 2021-12-06 15:31:14

using srvrepl the fields returned are added by sqlite in file mormot.db.raw.sqlite3 line 7895
for i := 0 to FieldCount - 1 do
W.AddColumn(sqlite3.column_name(Request, i), i, FieldCount);
RowID is not included there

The error in Query 2 above is returned by file mormot.db.raw.sqlite3 line 8158 as
ID does exist in table for sqlite3 and it is not changed before to rowID from GetAndPrepareStatement in mormot.orm.sqlite3 (or probably TRestStorageInMemory.AdaptSqlForEngineList in mormot.orm.storage )

Last edited by dcoun (2021-12-06 15:33:53)

ab · 2021-12-06 16:08:44

For the external tables, you need to use RowID and not ID, due to how SQLite3 itself works.

What is the generated SQL? Is the RowID part of it?

dcoun · 2021-12-06 16:54:51

ab wrote:

For the external tables, you need to use RowID and not ID, due to how SQLite3 itself works.
What is the generated SQL? Is the RowID part of it?

For external databases, as I showed in the queries' results, for external databases mapped to sqlite vtables both rowID and ID work without problems and return ID as primary field index

The SQL that arrives to the above mormot.db.raw.sqlite3 line 8158 is not changed at all, it is the same that was entered in ExecuteJson([],sqlquery). I am not sure if it is a bug or a feature.

To be honest, I give up. Nothing about you and thank you for your time. I will return in a future time.

Last edited by dcoun (2021-12-06 17:34:11)

mORMot Open Source

#1 2021-12-05 19:17:47

primary key fields in an existing table & and other indexes

#2 2021-12-05 20:00:56

Re: primary key fields in an existing table & and other indexes

#3 2021-12-05 20:10:00

Re: primary key fields in an existing table & and other indexes

#4 2021-12-05 21:00:52

Re: primary key fields in an existing table & and other indexes

#5 2021-12-05 21:28:31

Re: primary key fields in an existing table & and other indexes

#6 2021-12-06 08:02:07

Re: primary key fields in an existing table & and other indexes

#7 2021-12-06 08:09:16

Re: primary key fields in an existing table & and other indexes

#8 2021-12-06 08:55:50

Re: primary key fields in an existing table & and other indexes

#9 2021-12-06 13:21:25

Re: primary key fields in an existing table & and other indexes

#10 2021-12-06 13:53:37

Re: primary key fields in an existing table & and other indexes

#11 2021-12-06 14:08:14

Re: primary key fields in an existing table & and other indexes

#12 2021-12-06 14:17:02

Re: primary key fields in an existing table & and other indexes

#13 2021-12-06 14:43:20

Re: primary key fields in an existing table & and other indexes

#14 2021-12-06 14:57:15

Re: primary key fields in an existing table & and other indexes

#15 2021-12-06 15:31:14

Re: primary key fields in an existing table & and other indexes

#16 2021-12-06 16:08:44

Re: primary key fields in an existing table & and other indexes

#17 2021-12-06 16:54:51

Re: primary key fields in an existing table & and other indexes

Board footer