#451 Re: mORMot 1 » Exception with empty array parameter? » 2022-03-07 14:44:22

That was the post about having interface methods and not to expose ORM functions that I have read in the past and I need to revisit.
MVC example was not working in the past but I noticed some commits last days and I revisit it.
Thank you a lot. I will check it and I will return

#452 Re: mORMot 1 » UpdateField and WHERE clause » 2022-03-06 16:09:45

I believe it works like the following:

UpdateField(linksSelected.RecordClass,'field1_to_match_in_where>=? AND  field2_to_match_in_where>=?',[field1_value,field2_value],'Field_to_update',[value_to_put_to_update_field])

I am not sure if the WhereFieldName can have more than one fields in the where expression in Mormot1

#453 Re: mORMot 1 » Exception with empty array parameter? » 2022-03-05 23:25:40

Thank you a lot for your response.
To be honest, after posting this thread I was wondering if array of const could be allowed  to be used.
I was coping-pasting orm methods and I found it working but the empty brackets did not. Probably it was working by chance

Do we need the "restServer := GetDBDataRestServer;" I found that the Server property from TInjectableObjectRest is working excellent
The TOrmArticleObjArray how it it defined? for the moment, I am using Json arrays to get data as they are more compatible with mobile devices.

I removed Tormclass procedure arguments as I am not sure if I can pass them safely. I am using tormclass.sqltable to transform it as a utf8string argument and back with "server.Model.Table[Table]" in order to use "generic" orm functions for multiple Tormclasses

My main problem now is how to pass and get generic Torm instances that they are usually passed as values in orm functions, e.g. OrmRetrieve(aID: TID; Value: TOrm; ForUpdate: boolean): boolean;
Probably the GetJsonValues can prepare a utf8string value and fillprepare can use it in the other side but I have not yet managed to use them successfully.
I have seen tdocvariant functions with variant as input/output but I have not find an example and I am not sure how to use them

probably a way to have custom authentication over the ORM interface is the most easy way to deal with the server, but as I want to use it from mobile and web clients json serialization/deserialization is the only way I am thinking of.

#454 mORMot 1 » Exception with empty array parameter? » 2022-03-05 19:21:24

dcoun
Replies: 4

Following an advice I read somewhere in the forum, not to have orm endpoint accessible on Thttpserver in mormot2, I am using interface methods to access orm functions (to include authentication in future) like the following:

function TwbService.OrmRetrieveListJson1(Table: TOrmClass; const SqlWhere,  aCustomFieldsCsv: RawUtf8; aForceAjax: boolean): RawJson;
begin result:=server.Orm.RetrieveListJson(Table,SqlWhere,aCustomFieldsCsv,aForceAjax); end;

function TwbService.OrmRetrieveListJson2(Table: TOrmClass;  const FormatSqlWhere: RawUtf8; const BoundsSqlWhere: array of const;  const aCustomFieldsCsv: RawUtf8; aForceAjax: boolean): RawJson;
begin result:=server.Orm.RetrieveListJson(Table,FormatSqlWhere,BoundsSqlWhere,aCustomFieldsCsv,aForceAjax); end;

I noticed that the following gives exception:

service.OrmRetrieveListJson2(TmyORMclass,'',[],'*');

The following works OK:

service.OrmRetrieveListJson1(TmyORMclass,'','*');

Also the following works OK:

service.OrmRetrieveListJson2(TmyORMclass,'RowID>?',[0],'*');

Is the empty array of const causing the problem?

#455 mORMot 1 » cross-platform json record serialization » 2022-03-04 09:00:03

dcoun
Replies: 0

Mormot1 does not support cross-platform JSON serialization / unserialization for records. Am I wrong?
Will that change in mormot2?

The main reason of asking is the use of RecordLoadJson/DynArrayLoadJson in getters/setters in TOrm derived entitles.

#456 Re: mORMot 1 » Stop the war! » 2022-02-25 10:55:27

I read that Kyiv is a real battlefield with Russian troops of both ordinary and secret army.
Take care of yourself and your family, we are with you!

#457 Re: mORMot 1 » Mormot2 orm unavailable » 2022-02-25 07:03:40

I confirm that setting the ConnectionTimeOutMinutes to less minutes than the wait_timeout mysql variable (that it is in seconds) solves the problem.
I apologize for the trouble

#458 Re: mORMot 1 » Mormot2 orm unavailable » 2022-02-24 09:26:46

I have deleted the logs and I apologize
It is not compiled in Lazarus so I will probably try it in debug mode today
Thank you a lot

#459 Re: mORMot 1 » Mormot2 orm unavailable » 2022-02-24 09:00:24

I have left two clients, both with useBidirsocket, the first with the last patch and the other before
Both have the timer with the ServerTimestampSynchronize.

Both machines can work the following day and the problem seems to start from Zeos's connection timeout to mysql
I notice in mormot.qb.sql that there are two properties:
"ConnectionTimeOutMinutes": I should set that to less than mysql connection timeout
"ReconnectAfterConnectionError": It is by default false and I should probably enable it
Do you recommend something more before retesting it?

#460 Re: mORMot 1 » Mormot2 orm unavailable » 2022-02-23 12:48:36

pvn0 wrote:

Hard to tell unless you provide a minimal example we can test, tbh I suspect it might be the new httpasync server which is the default in mORMot 2, can you test with the normal http socket server, or if you're using websockets, BidirSocket server.

TRestHttpServer.Create(..., TRestHttpServerUse.useHttpSocket,..l);

I compiled it with useBidirsocket and I will report tomorrow.

#461 mORMot 1 » Mormot2 orm unavailable » 2022-02-23 11:43:52

dcoun
Replies: 8

I have a mormot2 Delphi 11 vcl program with a TRestServerDB that connects to an external mysql database
The above TRestServerDB is available through a TRestHttpServer in the URL "http://127.0.0.1:44332/url/srv/" with WebSockets enabled
Everything works OK and I am using this server from the same program that has this server.
If I leave the computer and the program idle (no hibernation, sleep, or anything like that happens) the TRestHttpServer is not accessible the following day.
I make a thread timer to call a ServerTimestampSynchronize every 10 minutes and the following day, the server is accessible but the TRestServerDB does not work
A call to "http://127.0.0.1:44332/url/srv/Mytable/1" returns a 404 error in JSON
This seems to happen to an other computer too. I am not using any authentication or sessions and I have not an error in the meantime.
I have enabled verbose log to file to see if anything is reported in the logs, and I waiting for.

Do we expect something to automatically shutdown the service due to inactivity?

#463 Re: mORMot 1 » TWebSocketAsyncServer hangs when application is terminated » 2022-02-14 09:15:21

ab wrote:

Do you use them from WS clients, or regular REST clients?

forgot to tell
From Regular rest clients is OK. This happens with Websocket clients connected before.

#464 mORMot 1 » TWebSocketAsyncServer hangs when application is terminated » 2022-02-13 17:51:40

dcoun
Replies: 4

Delphi 11, win32 target, mormot2

A TRestHttpServer with two TRestServerDB/WebSocketsEnable

when destroying it:
srv.shutdown; (the TRestHttpServer)
srvdb.Free; (one  TRestServerDB)
oper.Free; (second  TRestServerDB)
srv.free;

The TRestHttpServer hangs at file mormot.net.ws.async, line 597,

    // use TWebSocketAsyncConnection.SendDirect for non-blocking socket sending
    if aClientsConnectionID = nil then
      // broadcast to all connected clients
      for i := 0 to fAsync.ConnectionCount - 1 do
        inc(result, ord(TWebSocketAsyncConnection(fAsync.Connection[i]).
           SendDirect(tmp, aFrame.opcode, aTimeOut)))

In the logs,

HTTP: 20220213 17480325 http mormot.rest.http.server.TRestHttpServer(11686740) {"TWebSocketAsyncServerRest(11a9bd80)":{ApiVersion:"WinSock 2.0.514",ServerName:"mORMot2 (Win)",ProcessName:"ppp/srv ppp/oper",SockPort:"0.0.0.0:50480",ServerKeepAliveTimeOut:30000,StatHeaderProcessed:4,StatBodyProcessed:1,StatUpgraded:1,HeadersDefaultBufferSize:2048,Async:{"TWebSocketAsyncConnections(08828170)":{ThreadPoolCount:32,ConnectionCount:1,ConnectionHigh:2,Clients:{"TAsyncConnectionsSockets(111b27c0)":{Count:1,ReadCount:23,WriteCount:23,ReadBytes:3798,WriteBytes:2838962,Total:2}},Server:{"TCrtSocket(0b205d50)":{Server:"0.0.0.0",Port:"50480",RawSocket:1396,TimeOut:10000}},Accepted:2,MaxConnections:7777777,MaxPending:10000}}}} finalized for 2 servers
Warning: 20220213 17480640 warn mormot.net.ws.async.TWebSocketAsyncConnection(112ba170) AfterWrite: unexpected hrsGetCommand]

Thank you in advance

#465 mORMot 1 » JWT and ORM calls » 2022-02-13 11:00:30

dcoun
Replies: 1

I am having a TRestHttpServer created with useBidirAsync, secSynShaAes and I set the JWTForUnauthenticatedRequest to use JWT

1. Reading a discussion (https://synopse.info/forum/viewtopic.php?id=5589) about JWT renewal, you propose to check if the token is expired before each request. How to deal with ORM methods calls from a TRestHttpClientWinHttp client in a server with JWT authentication? Should I check before every ORM call (Add, Delete, Update) if JWT is expired? Is there a callback method for ORM calls to check this automatically?
2. Websockets use already encryption using password (WebSocketsEnable/WebSocketsUpgrade), does that means that JWT is not need, if I do not need user sessions?
3. If Websocket connections do not need an additional authentication like JWT, do you recommend to run ORM methods over a Websocket upgraded client? Is the load penalty acceptable?

Thank you in advance

#466 Re: mORMot 1 » Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'" » 2022-02-10 06:54:59

You have right, I am absolutely with you.
But for a 12 years old application still in use and learning mormot2 at the same time, I have to go with smaller steps: Refractor the code inside the application that will be the future service.
I am waiting also the mobile Delphi compatible part of mormot2 before splitting the service code.

#467 Re: mORMot 1 » Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'" » 2022-02-09 07:05:46

I found the problem:
mormot.orm.sql, line 296
   /// by default, any INSERT will compute the new ID from an internal variable
    // - it is very fast and reliable, unless external IDs can be created
    // outside this engine
    // - you can set EngineAddUseSelectMaxID=true to execute a slower
    // 'select max(ID) from TableName' SQL statement before each EngineAdd()
    // - a lighter alternative may be to call EngineAddForceSelectMaxID only
    // when required, i.e. when the external DB has just been modified
    // by a third-party/legacy SQL process

#468 mORMot 1 » Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'" » 2022-02-08 22:33:52

dcoun
Replies: 3

I can not explain the following error message I am getting:

 EXC EZSQLException {Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'"}

I am using mormot2 for its ORM capabilities (without a central API server but as framework in workstations that perform db operations).
Two different workstations connect to the same Mariab server using Zeos
The table is external with primary key auto_increment and the two workstations read and insert records in this table through orm.AddOrUpdate(ormtable) (with IDvalue=0 for inserting)
Sometimes, the program needs to restarted as it continues to show this error upon inserting a new record .

Initially I though it could be a cache problem, but when retrieving records it shows all recent records with bigger primary key than the new it tries to use when inserting new record. It is just trying to insert a new record with a primary key that already exists.
The primary key that it tries to use in the sql query when inserting the record is increased by one in each try.

Any ideas?

#469 Re: mORMot 1 » Results based on two or three Orm entities » 2022-01-25 10:42:35

ab wrote:

Only the TOrmMany class has the methods to handle joins.

You can just inherit the ORM class which maps the pivot table from TOrmMany instead of plain TOrm.

Understood. not possible without a pivot table. I have to stay with sql queries then. Thanks a lot.

#470 Re: mORMot 1 » Results based on two or three Orm entities » 2022-01-25 09:03:44

ab wrote:

The mORMot 1 documentation still work on this subject.

Just replace TSQLRecord* into TOrm* names. wink

https://synopse.info/files/html/Synopse … ml#TITL_58


I am a bit lost reading about many2many associations with a separate Torm that holds many to many information.
I have already tables that they are mapped to TOrms and have pointers to other TOrms tables.
Is there a way to use an 'one to many' relationship with the included pointer (Fopa:TOrmProductDest)?
I do not need something complicated. Each Torm has max 2 pointers to other Torms

#471 mORMot 1 » Results based on two or three Orm entities » 2022-01-25 08:46:39

dcoun
Replies: 4

Probably it is stupid but as I am doing my first moves using Mormot2 I have not find a sure way to deal with the following:
I have the following entities:

 TOrmProductSource = class(TOrm)
  private
    Fmoo:rawutf8;
    Fopa:TOrmProductDest;
    Fcount:PtrInt;
  published
    property moo:rawutf8 read Fmoo write Fmoo;
    property opa:TOrmProductDest read Fopa write Fopa;
    property count:PtrInt read Fcount write Fcount;
end;

TOrmProductDest = class(TOrm)
  private
    Ftest:RawUtf8;
    Famount:PtrInt;
  published
    property test:RawUtf8 read Ftest write Ftest;
    property amount:PtrInt read Famount write Famount;
end;

The TOrmProductDest is used by other Torms also and can not be an integrated collection in TOrmProductSource.

I want to retrieve all TOrmProductSource that have a count property of less than 10 and  the opa's amount property with a value of more than 50.
How can I combine these two or more Torms in one request?

Thank you in advance

#472 Re: mORMot 1 » Mormot2, example extdb-bench, TTestDatabaseExternalAbstract problem » 2022-01-06 15:50:31

ab wrote:

I have just fixed this sample, and tested it with PostgreSQL.
Please check https://github.com/synopse/mORMot2/commit/779ad860

Seems to work fine by now.

Thank you @ab

#473 Re: mORMot 1 » Mormot2, Delphi and arm devices » 2022-01-02 10:22:03

please include mustache in cross platform units in mormot2

My best wishes for the new year.

#474 Re: mORMot 1 » Mormot2, Delphi and arm devices » 2021-12-27 10:30:17

Thank you @ab, we can survive with slower results and Mormot2's cross platform units are also very welcome.
To be honest, we will never say no to updated and extended in functions cross platform mormot2 units than Mormot1's units smile

PS I started using RecordLoadJson/RecordSaveJson and TDocVariant, and I am a bit addicted now.....

#475 mORMot 1 » Mormot2, Delphi and arm devices » 2021-12-26 08:43:11

dcoun
Replies: 4

I tried today to use a couple of units from Mormot2 in a mobile app using Delphi 11 and I noticed that it is not supported.
I understand that Mormot2 is not even yet released, but is it possible/planned for the future?

eg TDocVariant, json and rtti functions

Delphi is now free through Community editions, mobile development is practically available (but not for Google playstore due to version 11 limitation)
A lot of people use now community edition, even a professional delphi subscription is quite affordable now

#476 Re: mORMot 1 » Fields order from TRestOrm.MultiFieldValue in a TRestStorageInMemory » 2021-12-24 10:08:26

ab wrote:

You are right.

Please try https://github.com/synopse/mORMot2/commit/6f29067f

Sorry for the issue.

@ab,
Thank you again for your work and the people that contribute
Merry Christmas, Happy Year, seasonal wishes to all

#477 Re: mORMot 1 » Fields order from TRestOrm.MultiFieldValue in a TRestStorageInMemory » 2021-12-23 22:00:34

I think the code in mormot.orm.rest line 908 should be like the following:

        // get field values from the first (and unique) row
        for f := 0 to T.FieldCount - 1 do
        begin
          i := f; // regular SQL SELECT order by default
          if (T.FieldCount > 1) and
             (StrIComp(T.Results[i], pointer(FieldName[i])) <> 0) then
          begin
            // not the regular order (e.g. TRestStorageInMemory = TOrm order)
            i := T.FieldIndex(FieldName[f]);
            if i < 0 then
              if not IsFieldName(pointer(FieldName[f])) then
                exit    // something went wrong
              else
                i := f; // expects the function results to be in-order
          end;
          o := i + T.FieldCount;
          FastSetString(FieldValue[f], T.Results[o], T.ResultsLen[o]);
        end;

The above code works

#478 Re: mORMot 1 » Fields order from TRestOrm.MultiFieldValue in a TRestStorageInMemory » 2021-12-23 17:24:57

ab wrote:

Oh you are right: for MultiFieldValue it is confusing.
I will fix it.

Edit: Please check https://github.com/synopse/mORMot2/commit/35583c64

I tested requesting 7 fields from a table with 72 fields.
                                         
As they exist in table definition: RowID, field1, field2, field3, field4, field5, field6
As requested: RowID, field1, field4, field6, field2, field3, field5
as received: RowID, field1, field4, field5, field2, field6, field3

#479 Re: mORMot 1 » Fields order from TRestOrm.MultiFieldValue in a TRestStorageInMemory » 2021-12-23 12:18:17

If we do not know the table definition, Is it possible somehow to get the fieldname of each result returned?

I used the ExecuteJson and parse it with _json(json,variant,JSON_FAST) as alternative

Do you recommend an other function?

#480 mORMot 1 » Fields order from TRestOrm.MultiFieldValue in a TRestStorageInMemory » 2021-12-23 11:03:46

dcoun
Replies: 8

I am using with Mormot2 the following function in a table with TRestStorageInMemory type storage

function TRestOrm.MultiFieldValue(Table: TOrmClass;
  const FieldName: array of RawUtf8; var FieldValue: array of RawUtf8;
  const WhereClause: RawUtf8): boolean;

I noticed that the order of FieldValue results do not follow the FieldName fields order but the fields' order in the table definition.

#481 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-22 12:33:57

Thank you very much Arnaud. It works now OK

#482 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-22 07:22:05

Adding the following to test.core.collections with the above Ttest type definition and it fails

  
  lt: ilist<ttest>;
  t:ttest;
.....
  lt := Collections.NewList<Ttest>;
  lt.Capacity := MAX + 1;
  for i := 0 to MAX do begin
    t:=ttest.Create; t.soup:=inttostr(i);
    Check(lt.Add(t) = i);
  end;
  for i := 0 to lo.Count - 1 do
    Check(lt[i].soup = inttostr(i));

I have noticed also that when adding in the following screen, value is none in add function
https://pasteboard.co/U3hiCtiA6j7N.png

#483 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-21 21:30:33

ab wrote:

Try to debug why it is the case on your side because I can't reproduce it here.

Do the regression tests pass on your side?
There is a test for this exact case in https://github.com/synopse/mORMot2/commit/2758a7aa

it works OK using the following and TobjectwithID instances:

v:=collections.NewList<TobjectwithID>([loCreateUniqueIndex]); 

using the following type instances it does not

Ttest=class
  private
    fsoup:string;
  published
    property soup:string read fsoup write fsoup;
  end;

Should I register them before using them?

#484 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-21 10:25:41

Now the object is added but when trying to access it with the last line in the code above the result for 0-index items is nil

#485 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-20 16:14:37

In the following the returned i is -1
if I remove the loCreateUniqueIndex, I am getting i=0, but the following

v.items[i]

is null
Delphi 11 and FPC 3.2

var i:integer; s:Ttest; 
begin
v:=collections.NewList<Ttest>([loCreateUniqueIndex]);
s:=Ttest.Create; 
s.soup:='hello';
i:=v.Add(s);
showmessage(v.items[i].soup);

#486 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-20 15:09:02

Thank you very much Arnaud.
playing a bit with it, I can say that an "Exchange" between two items could be useful too (if loCreateUniqueIndex is not used).

#487 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-20 14:34:24

Running the above statement you showed for the Ttest class, and having v:ilist<Ttest>, I am getting:
Project Project1.exe raised exception class ESynList with message 'TSynListSpecialized<System.Integer>.Create: T does not match TArray<System.Integer>'.

In line 777 of mormot.collections,

     (fDynArray.Info.ArrayRtti.Kind <> aItemTypeInfo^.Kind))  then

The first rkInteger and the second is rkClass

#488 Re: mORMot 1 » mORMot 2 Generics and Collections » 2021-12-20 13:34:32

Does it need to register a new class like the following in order to work?

  Ttest=class
  private
    fsoup:string;
  published
    property soup:string read fsoup write fsoup;
  end;

v:=collections.NewList<Ttest>([loCreateUniqueIndex],typeinfo(tarray<Ttest>));

#489 Re: mORMot 1 » RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty » 2021-12-18 14:14:42

Thank you Arnaud,
There is something more. The following does not work

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'ID=?',[1],'*');

nor the following:

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'testID=?',[1],'ID,xml');

Should I use RowID? 
Thank you in advance

#490 Re: mORMot 1 » RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty » 2021-12-18 13:39:30

Am I missing something from the documentation in the above?

#491 mORMot 1 » class EInOutError with message 'I/O error 105' » 2021-12-18 12:04:40

dcoun
Replies: 1

Last commits in Mormot2 when compiling using Delphi 11 in file mormot.crypt.ecc256r1 line 1560

Project Project3.exe raised exception class EInOutError with message 'I/O error 105'.

A writeln needs to be deleted

Cheers

#492 mORMot 1 » RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty » 2021-12-16 20:52:16

dcoun
Replies: 4
TOrmInvcanceled=class(Torm)
    private
      Ftestid: PtrInt;
      Fxml: rawutf8;
    published
      property testID: PtrInt read Ftestid write Ftestid;
      property xml:rawutf8 read Fxml write Fxml;
  end;
procedure errororm;
var lis:ilist<TOrmInvcanceled>; model:TOrmModel;
begin
model:=tormmodel.Create([TOrmInvcanceled]);
Model.VirtualTableRegister(TOrmInvcanceled,TOrmVirtualTableBinary);
srvmem:=TRestServerDB.Create(model);
srvmem.Model.Owner:=srvmem;
srvmem.CreateMissingTables;
showmessage(TOrmInvcanceled.OrmProps.SqlTableRetrieveAllFields);
srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'testID=?',[1],'*');
end;

The above causes the following error:
Project Project3.exe raised exception class ESqlite3Exception with message 'Error SQLITE_ERROR (1) [SELECT ID,testID,xml FROM Invcanceled WHERE testID=?] using 3.37.0 - no such column: ID, extended_errcode=1'.

if the following is used, no error is returned:

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'',[],'*');

Using the following, no error is returned also:

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'testID=?',[1],'Row'+TOrmInvcanceled.OrmProps.SqlTableRetrieveAllFields);

#493 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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.

#494 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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 )

#495 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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}]

#496 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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

#497 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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

#498 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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

#499 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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}]

#500 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 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

Board footer

Powered by FluxBB