You are not logged in.
I think you don't understand what is being proposed in this thread. The proposal is for mormot to prevent a record from being removed from the database if the record is being used by some property of another object that references this table. this will to avoid inconsistency in the database or the table that references the deleted record being set to ZERO as mormot does today.
The issue of creating the FK is just something additional to prevent the programmer from using a delete outside the ORM from also leaving the database inconsistent.
Kabiri, I undesrstand you but FKs is a great way to protect yout database against inconsistence. If mormot could grant this integrity will be great. this will remove of programmer many responsability. We know that are programmers and programmers.
Some answer to this topic Arnold?
WAW!!!!!!!!!!!!!!!!!!!!!
Very very very thanks Arnold. This is a Great Feature to MORMOT. 10 points Gain
Arnold have u some method similar to copy values from RecordToObject and ObjectToRecord?
Tnks AB
Arnold, I practically read the entire forum and you always give the same answer regarding TID null, FKs etc...
I really want to work with ORM, just like many programmers who ventured before me in trying to use mormot, I contacted some of them and they told me that they stopped using mormot's ORM precisely for the same reasons that I didn't. I want to give up.
I know that you need to keep your ORM capable of working with both SQL and NoSQL databases.
But is there any way to implement things that the orm itself can handle internally instead of having to write it in my code? Example:
I have a table in my database that looks like this:
CREATE TABLE TABELA_AB (
CD_AB ID_NN /* ID_NN = INTEGER NOT NULL */,
CD_A ID_NN /* ID_NN = INTEGER NOT NULL */,
CD_B ID_NN /* ID_NN = INTEGER NOT NULL */,
CD_C ID /* ID = INTEGER ACCEPT NULL VALUES */
);
ALTER TABLE TABELA_AB ADD CONSTRAINT UNQ1_TABELA_AB UNIQUE (CD_A, CD_B, CD_C);
ALTER TABLE TABELA_AB ADD CONSTRAINT PK_TABELAAB PRIMARY KEY (CD_AB);
ALTER TABLE TABELA_AB ADD CONSTRAINT FK_TABELA_AB_2 FOREIGN KEY (CD_A) REFERENCES TABELA_A (CD_A );
ALTER TABLE TABELA_AB ADD CONSTRAINT FK_TABELA_AB_3 FOREIGN KEY (CD_B) REFERENCES TABELA_B (CD_B );
ALTER TABLE TABELA_AB ADD CONSTRAINT FK_TABELA_AB_1 FOREIGN KEY (CD_C ) REFERENCES TABELA_C (CD_C );
TC = class(TOrm);
TCNullable = class(TC);
TTableAB = class(TOrm)
published
A: TA;
B: TB;
C: TCNullable;
end;
1) Being able to define field as not null. Currently mormot creates tables without defining any field as not null other than the primary key. Even so, using orm all fields will always be filled with a certain value. unless TNullableXXX is used. My idea:
VirtualTableExternalMap(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal')
.SetRequired('A', True)
.SetRequired('B', True)
Although I think this should be automatic when using the TORM field type! You store ZERO, why dont create the field with not null? Is not it?
2) Prevent deletion of a record whose primary key is being used in another table as a foreign key.
I know you've said many times that this should be controlled via business rules and such, but the programmer can always forget to do this somewhere. being in the model and the model creating the FK we do not run this risk.
I liked you ide of TClassNameRestrictID = A kind of TID that:
- can store value as null at database if field not is on Requerid list (via SetRequired)
- create fk automatically on databased that support this feature
- during the update, prevent it from placing a value that does not exist in the related table.
during the delete, prevent it from remove the record if some field fk are using this key
this would help for nosql banks for sql banks the foreign key was created so an exception would already happen anyway.
Maybe this is difficult to implement or not, I don't know. but you don't think these implementations would help the adoption of MORMOT. If we do a poll about which database most Delphi programmers use, I'm sure there will be more people using SQL than NoSQL.
MapField('FieldName','FieldNameAlternative').
There are something way to store null instead 0 on Solicitacao?
0 will raise foreign key error.
I find TNullableID or TIDNullable wothout success!
pAmostras: Variant receive a int64 number => Solicitacao: 123
vAmostra.Solicitacao: TAmostra(TOrm)
How can i pass this integer as TID to works in delphi and freepascal?
vAmostra.Solicitacao := Pointer(PtrInt(vItemAmostra.Solicitacao));
tnks
I'm finding some errors when try to compile my project in FPC.
function TAmostraService.InsertMany(pAmostras: variant): TServiceCustomAnswer;
var
vListaAmostras: IDocList;
vItemAmostra: variant;
vAmostra: TAmostra;
begin
vAmostra := TAmostra.Create;
try
// delphi compiles, fpc no (Sorotrack.AmostraService.pas(339,23) Error: Can't determine which overloaded function to call)
vListaAmostras := DocListFrom(pAmostras.amostras);
for vItemAmostra in vListaAmostras do
begin
// delphi compiles, fpc no. what do do? Sorotrack.AmostraService.pas(343,38) Error: Incompatible types: got "OleVariant" expected "UTF8String"
vAmostra.Numero := vItemAmostra.Numero;
// delphi compiles, fpc no. what do do?
// Sorotrack.AmostraService.pas(347,31) Error: Incompatible types: got "Pointer" expected "TSolicitacao"
vAmostra.Solicitacao := Pointer(PtrInt(vItemAmostra.Solicitacao));
vAmostra.Exames := vItemAmostra.Exames;
vAmostra.DataEtiqueta := vItemAmostra.DataEtiqueta;
vAmostra.Empresa := Pointer(PtrInt(CurrentUserCompany));
Result := Add(vAmostra);
end;
finally
vAmostra.Free;
end;
end;
I've alredy read at the documentacion the part about FPC Clients here: https://synopse.info/files/html/Synopse … m*%20units , but it didn't works for me. Can someone help me with this issues?
Putting to zero should still be done AFTER the main deletion, to detect any failure of it.
Do you intend to make this change soon (without pressuring you)?
If I'm not going to do it now, I can try to implement it and pass it on to you. I don't know the mormot sources very well but I can follow the same steps you did in AfterDeleteForceCoherency
I was thinking here, unless you want to treat the restrict at the orm level, you would only need the RESTRICT field type to create the foreign key in the database.
If I create it at ORM level, I would still create the foreign key to avoid incorrect data in the database due to some statement that the programmer executes (INSERT OR UPDATE) outside the ORM.
So I guess you expect to refuse the deletion if the ID is used somewhere else?
Some kind of "ON DELETE RESTRICT" to raise an error when Delete() is called with some existing reference in other tables?
Yeah this is very necessary to many of us that work with relational databases.
We would need to add TOrm*RestrictID field type name detection, and TRecordReferenceRestrict field, similar to TOrm*ToBeDeletedID and TRecordReferenceToBeDelete.
Yeah xxxRestrictID would be great. and this field types could create automatically that foreign key if not exists.
Finally foreign key on mormot >:)
I think that maybe whould be necessary remove AfterDeleteForceCoherency and put a BeforeDeleteForceCoherency to treat XXXRestrictID XXXToBeDeletedID XXXToBeDeletedIDSetNull or XXXToBeDeletedIDSetZero. just ideas!!!
I know, i dont want send detailed error information to a client, but i need show something. client need know the cause to not delete or update or insert the record.
There are some event or a method that can i should override to treat whatever type of exception on rest service?
Hy AB,
to try to guarantee the integrity of the data, since there is no foreign key support yet, couldn't there be something that could be done before calling
result := inherited Delete(Table,ID); // call EngineDelete
to check if this id is being used in another TOrm? thus preventing deletion?
Something like BeforeDeleteForceCoherency(Table,ID);
Sorry, I didn't ask the proper question.
The error appears in the log:
0000000044FC141D EXC ESqlDBPostgres {Message:"TSqlDBPostgresLib Exec failed: 23503 [ERROR: update or delete on table \"empresa\" violates foreign key constraint \"fk_posto_01\" on table \"posto\"\nDETAIL: Key (id)=(1) is still referenced from table \"posto\".\n]",Statement:null} [TAppRestHttpSrv 8081v1 THttpSrv] at ad04c1
But this error cannot be caught by a try except in my application.
function TServiceBase.Delete(pOrmClass: TOrmClass; pId: Int64): TServiceCustomAnswer;
var
vErros: RawUtf8;
begin
if not GlobalRepository.Delete(pOrmClass, pId, vErros) then
Result := ReturnCannotDeleteRecord(StringToUtf8(vErros), HTTP_BADREQUEST);
end;
....
function TRepository.Delete(pOrmClass: TOrmClass; pId: Int64; var pErros: RawUTF8): Boolean;
begin
pErros := EmptyStr;
try
Result := FRestOrm.MemberExists(pOrmClass, pId)
and FRestOrm.Delete(pOrmClass, pId); <<<<< into here generate exception
except on E: Exception do
begin
Result := False; <<<<<<<< never quick here
pErros := E.Message;
end;
end;
end;
How to capture this error message so you can send on TServiceCustomAnswer?
I looked through the code and documentation and didn't find in IRestORM or any other class that had an event or property with LastError
Tnks AB.
There are something midleware on momot to exception´s treatment?
My client Rest receive:
{
"errorCode": 400,
"errorText": "Bad Request"
}
I whould like receive errorText with real information or treat it before return to rest client.
AB,
I found the error, but why it dont appear on message box?
tnks.
I'm trying to creat a postgres connection using this:
function TRepository.ConnectionClass(pDBConnection: TSqlDBConnectionPropertiesClass): IRepository;
begin
Result := Self;
FConfigDB := pDBConnection.Create(FHost, FDatabase, FUser, FPassword);
VirtualTableExternalRegisterAll(FOrmModel, FConfigDB);
FOrmModel.Props[TAmostra].ExternalDB.MapField('Solicitacao', 'IdSolicitacao');
FRestOrm := TRestServerDB.Create(FOrmModel, SQLITE_MEMORY_DATABASE_NAME);
FRestOrm.DB.Synchronous := smOff;
FRestOrm.DB.LockingMode := lmExclusive;
FRestOrm.Server.CreateMissingTables;
FRestOrm.Server.CreateSqlMultiIndex(TAmostra, ['empresa', 'numero'], true, 'sk_amostra_01');
FRestOrm.Server.CreateSqlMultiIndex(TSolicitacao, ['empresa', 'numero'], true, 'sk_solicitacao_01');
FRestOrm.Server.TrackChanges([TEmpresa, TUsuario, TAmostra,
TRack, TEvento], TMyHistory, 100, 10, 65536);
end;
But it's returning an error: Project SorotrackAPI.exe raised exception class ESqlDBPostgres with message 'TSqlDBPostgresLib' Exec failed '. Can someone help me to find what's wrong?
If the field names do match, you can just fill the TBaby record from JSON, directly from the DB, with no transient TOrmBaby.
What Orm´s Method I can use for this?
My TDTOBaby is a packet record! I just see ORM.RetriveXXX(passing TORMClass)
Can you show a simple sample?
When you told:
Create ORM classes just to interact with the database.
Create another layer of classes (DTO) for your api or business rules...
Means that i have to read the objects to TOrmObjects and manually transport the data to the DTO records?
sample:
In someplace of my apy i need get a baby then a call the repository. with the repository pattern I can isolate the orm of all my application (Clean Code)
procedure TBabyRepository.GetBaby(pID: Int64; var pDTOBaby: TDTOBaby);
var
Baby: TSQLBaby; // store a record
begin
Baby := TSQLBaby.Create;
try
TRestDB.Retrieve(pID, Baby);
// transpose to DTO, TRestServerFullMemory can return to client
pDTOBaby.ID := Baby.Id;
pDTOBaby.Name := Baby.Name;
pDTOBaby.Address := Baby.Address;
pDTOBaby.BirthDate := Baby.BirthDate;
pDTOBaby.Sex := Baby.Sex;
finally
Baby.Free;
end;
end;
this translate can be very slow approach depending!
I can do this of other way?
Mormot treat /Auth route. Could it treat /RefreshToken too?
and call virtual method on registred CustomAutentication?
in this code
function TRestServerUriContext.Authenticate: boolean;
...
begin
...
if (rsoSessionInConnectionOpaque in Server.Options) and
(Call^.LowLevelConnectionOpaque <> nil) then
begin
// TAuthSession instance may have been stored at connection level
// to avoid signature parsing and session lookup
s := pointer(Call^.LowLevelConnectionOpaque^.ValueInternal);
if s <> nil then
if s.InheritsFrom(Server.fSessionClass) then
begin
SessionAssign(s); <<<<<<<<<<<<<<<<<<<<< here does
exit;
end
else
Call^.LowLevelConnectionOpaque^.ValueInternal := 0; // paranoid
end;
// parse signature to retrieve the associated session
Server.fSessions.Safe.ReadOnlyLock; // allow concurrent authentication
try
a := pointer(Server.fSessionAuthentication);
if a <> nil then
begin
n := PDALen(PAnsiChar(a) - _DALEN)^ + _DAOFF;
repeat
s := a^.RetrieveSession(self); // retrieve from URI or cookie
if s <> nil then
begin
if (Log <> nil) and
(s.RemoteIP <> '') and
(s.RemoteIP <> '127.0.0.1') then
Log.Log(sllUserAuth, '%/% %',
[s.User.LogonName, s.ID, s.RemoteIP], self);
SessionAssign(s) <<<<<< are not missing this here????
exit;
end;
inc(a);
dec(n);
until n = 0;
end;
...
end;
To implement properly a JWT, you could rather use TRestServer.JwtForUnauthenticatedRequest.
Then, once authenticated, you got the JWT payload information (and any session you wish) within TRestUriContext.JwtContent.
I understand that I should store any and all information I need in the JWT payload instead of using sessions. ok i doing this!
I would like to know where to store, for example, email and user id per request to be used within my classes that implement the REST endpoint.
there are some way to put this extra information into ServiceRunningContext?
Arnold
First of all I want to congratulate you on the mormot. this is a great framework. Despite the vast documentation, many things are evident in it for those who are starting to use the framework.
I would like to understand the reason for this limitation, I am not talking about complex queries here, just talking about the fields to be returned by the ORM query.
This is not a complex query, I simply do the join to filter the records in the where clause.
From what I understand when using VirtualTableExternalRegisterAll with SQLITE_MEMORY_DATABASE_NAME my ID fields have to be treated as RowId when mentioning them in my source code. mormot will automatically convert RowID to ID to run against the database. Am I right?
What I don't understand is why something as basic as returning fields cannot be done in a simple way and consistent with what programmers are used to doing. Again this is not a criticism but an attempt to improve mormot so that we can make mormot closer to what programmers are used to.
So here samples that shouls be works IMHO:
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], 'RowID');
Should be bring all Amostra records, just field ID, but mormot raise error "no such column: RowId" and not is because join
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], '');
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], '*');
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], 'Amostra.*');
All this should be bring the samething, all amostra records.
by your documentation, the last parameter as '' should be bring all fields except RowId/Id and blob fields
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], 'Amostra.*, s.*');
Should be bring all Amostra records and all fields, and all fields of solicitacao (as extra properties on variant).
What I mean is that it doesn't matter that the joins I put on where, should return only TORM´s fields passed on first parameter.
Unless I put join fields in the fields, but in this case I will be bringing the information in json without any problems
I have this instruction:
var
vAmostras: Variant;
begin
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)',
[], <MYFIELDS>);
...
end;
I make many tests replacing <MYFIELDS>
<MYFIELDS> = 'RowId'
Log: EXC ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT RowId FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: RowId",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c
<MYFIELDS> = 'Id'
Log: EXC ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT Id FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: Id",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c
<MYFIELDS> = 'amostra.RowId, s.numero, amostra.numero'
Log: works and bring Amostra ID rightly
<MYFIELDS> = 'amostra.Rowid, amostra.*'
Log: works and return amostra.Id, others fields...
<MYFIELDS> = 'amostra.*, s.numero, amostra.numero'
Log: works but dont return amostra.Id neither amostra.RowId
<MYFIELDS> = 'amostra.RowId'
Log: don't execute sql nothing at logger
<MYFIELDS> = 'amostra.Id'
Log: don't execute sql nothing at logger
<MYFIELDS> = 'amostra.*'
Log: don't execute sql nothing at logger
<MYFIELDS> = 'amostra.Id, s.numero, amostra.numero'
Log: EXC ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT amostra.Id, s.numero, amostra.numero FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: amostra.Id",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c
<MYFIELDS> = '*'
Log: EXC ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT RowID,DataCriacao,UltimaAlteracao,Numero,Solicitacao,Exames,DataEtiqueta,Rack,Linha,Coluna FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: RowID",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c
<MYFIELDS> = ''
Log: EXC ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT RowID,DataCriacao,UltimaAlteracao,Numero,Solicitacao,Exames,DataEtiqueta,Rack,Linha,Coluna FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: RowID",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c
I forgot my configuration:
FConfigDB := TSqlDBPostgresConnectionProperties.Create(
'191.101.78.31:5001',
'DB_SOROTRACK',
'microprocess',
'mpPost19dokcer!');
FOrmModel := CreateModel;
VirtualTableExternalRegisterAll(FOrmModel, FConfigDB);
FOrmModel.Props[TAmostra].ExternalDB.MapField('Solicitacao', 'IdSolicitacao');
FRestOrm := TRestClientDB.Create(FOrmModel, nil, SQLITE_MEMORY_DATABASE_NAME, TRestServerDB, false, '');
FRestOrm.Server.Server.CreateMissingTables;
I want use ORM but can put my owner sqls too.
I read and reread documentation and dont understand when use RowID or ID (my field) too.
Other thing I'm doing a POST passing filter on body
{{URL}}/amostra/listar
.Post('/amostra/listar', 'amostraService/listaramostras')
TFiltro = packed record
Amostra: RawUTF8;
EventoTipo: Integer;
Solicitacao: RawUTF8;
Paciente: RawUTF8;
Posto: RawUTF8;
PeriodoTipo: Integer;
PeriodoInicial: TDateTime;
PeriodoFinal: TDateTime;
end;
function TAmostraService.ListarAmostras(const pFiltro: TFiltro): TServiceCustomAnswer;
Why I have to pass:
[{ "Amostra": "1234567891"}]
instead of
{ "Amostra": "1234567891" }
???
Great, I'm testing.
Arnold I think I found a possible bug:
FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', FWhere, TDocVariantData(FWhereParamValues).ToArrayOfConst,
'Amostra.*') <<<<< this doesn't work. I lost a time debugging
FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', FWhere, TDocVariantData(FWhereParamValues).ToArrayOfConst,
'*') <<<<< this work
Question 1
var
vAmostras: Variant;
...
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], 's.numero, s.paciente, Amostra.*');
>>> SELECT s.numero, s.paciente, amostra.* FROM Amostra left outer join solicitacao s on (s.Id = Amostra.solicitacao)
I whould like define a alias to orm amostra ===> a
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'left outer join Solicitacao s on (s.RowId = a.solicitacao)', [], 's.numero, s.paciente, a.*');
>>> mormot cannot execute this select because it don't know about alias A.
i try too:
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'a left outer join Solicitacao s on (s.RowId = a.solicitacao)', [], 's.numero, s.paciente, a.*');
but mormot generate this select
>>> SELECT s.numero, s.paciente, a.* FROM Amostra WHERE a left outer join solicitacao s on (s.Id = Amostra.solicitacao)
Question 2
There is some way to pass the raw sql to orm? something like below. Be by ORM or other method do RestORM or other way?
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra,
'SELECT s.numero, s.paciente, a.* FROM Amostra a '+
'left outer join solicitacao s on (s.Id = Amostra.solicitacao) '+
'WHERE s.numero = ?', [12345]);
I have this class.
TContatos = class(TOrmMany)
private
FSource: TPessoa;
FDest: TContato;
published
property Source: TPessoa read FSource;
property Dest: TContato read FDest;
end;
mormot create table with: id, source, dest
How to do mormot create the table with:
id, pessoa, contato?
or
id , pessoaid, contatoid?
Sry brind this questions but I need help.
this is my model:
TContatos = class(TOrmMany)
private
FSource: TPessoa;
FDest: TContato;
published
property Source: TPessoa read FSource;
property Dest: TContato read FDest;
end;
TContato = class(TOrm)
private
FTelefone: RawUTF8;
FEmail: RawUTF8;
published
property Telefone: RawUTF8 read FTelefone write FTelefone;
property Email: RawUTF8 read FEmail write FEmail;
end;
TRacas = (rPoodle, rViraLata, rPitbull, rShawShaw);
TPet = class(TOrm)
private
FRaca: TRacas;
FNome: RawUTF8;
FDono: TPessoa;
published
property Raca: TRacas read FRaca write FRaca;
property Nome: RawUTF8 read FNome write FNome;
property Dono: TPessoa read FDono write FDono;
end;
TCidade = class(TOrm)
private
FNome: RawUTF8;
published
property Nome: RawUTF8 read FNome write FNome;
end;
TPessoa = class(TOrm)
private
FNome: RawUTF8;
FIdade: Integer;
FCidade: TCidade;
FContatos: TContatos;
FPets: TObjectList;
public
function NewContato(const pEmail, pFone: RawUtf8): TContato;
function NewPet(const pRaca: TRacas; const pNome: RawUTF8): TPet;
property Pets: TObjectList read FPets write FPets;
destructor Destroy; override;
published
property Nome: RawUTF8 read FNome write FNome;
property Idade: Integer read FIdade write FIdade;
property Cidade: TCidade read FCidade write FCidade;
property Contatos: TContatos read FContatos write FContatos;
end;
this is my data on database
Pessoa
id,"nome","idade","cidade"
1,"Pedro","25","1"
2,"Maria","39","2"
pet
id,"raca","nome","dono"
1,0,"miau","1"
2,1,"auau","1"
3,3,"goal","1"
4,1,"sansao","2"
cidade
id,"nome"
1,"Conquista"
2,"Jequié"
contato
id,"telefone","email"
1,"77 988526666","pedro@gmail.com"
2,"77 988526666","joao@gmail.com"
3,"73 999566489","jose@gmail.com"
contatos
id,"source","dest"
1,"1","1"
2,"1","2"
3,"2","3"
4,"2","1"
There is some way to retrieve all Pessoa with all properties? Cidade, Contatos, Pets, How can I do this?
my tries:
var
vPessoas: TPessoa;
vCidade: TCidade;
vContato: TContato;
vOrm: IRestOrm;
vIdPessoa: TID;
vI: Integer;
begin
vPessoas := TPessoa.CreateAndFillPrepareJoined(FRestOrm.Orm, '', [], []);
try
Memo2.Lines.Add(Format('Quantidade de pessoas: %d', [vPessoas.FillTable.RowCount])); // ok return 2 pessoa
while vPessoas.FillOne do
begin
Memo2.Lines.Add(Format('Id: %d', [vPessoas.ID]));
Memo2.Lines.Add(Format('Nome: %s', [vPessoas.Nome]));
Memo2.Lines.Add(Format('Idade: %d', [vPessoas.Idade]));
Memo2.Lines.Add(Format('Cidade: %s', [vPessoas.Cidade.Nome]));
vPessoas.Contatos.FillPrepareMany(FRestOrm.Orm, 'RowId>=?', [], [vPessoas.ID]);
Memo2.Lines.Add(Format('Quantidade de Contatos: %d', [vPessoas.Contatos.FillTable.RowCount])); // ERROR HERE. return 0 should be 2 (pedro and joao) <<<<<<
while vPessoas.Contatos.Dest.FillOne do // fillone return false
begin
Memo2.Lines.Add(Format(' Contato: %s - %s', [vI, vPessoas.Contatos.Dest.Email, vPessoas.Contatos.Dest.Telefone])); //is not retrieving email and telefone value
end;
vPessoas.FillClose;
// pets is nil on this moment then i do this, but i don´t know if is the better aproach. <<<<<<
vPessoas.Pets := FRestOrm.RetrieveList(TPet, 'dono=?', [vPessoas.Id]);
Memo2.Lines.Add(Format('Quantidade de Pets: %d', [vPessoas.Pets.Count])); ok 3 from first pessoa and 1 to second
for vI := 0 to vPessoas.Pets.Count-1 do
Memo2.Lines.Add(Format(' Pets %d: %s', [vI, TPet(vPessoas.Pets[vI]).Nome]));
Memo2.Lines.Add('-----------------------');
vPessoas.Pets.Clear;
vPessoas.Pets.Free;
vPessoas.Pets := nil;
end;
finally
vPessoas.Free;
end;
I try too
vPessoas := TPessoa.CreateAndFillPrepareMany(FRestOrm.Orm, '', [], []);
but this way it retrieve 4 pessoa on while
I try too
vPessoas.Contatos.FillPrepareMany(FRestOrm.Orm, 'RowId>=?', [], [vPessoas.ID]);
but vPessoas.Contatos.FillTable.RowCount return 0 and vPessoas.Contatos.FillOne return false
I try too
vPessoas.Contatos.FillMany(FRestOrm.Orm, vPessoas.ID);
vPessoas.Contatos.FillTable.RowCount return 2 ok but vPessoas.Contatos.FillOne return false
Oh my god!!!! I read documentation already and cannot make this work.
Hello Community,
I'm facing a challenge with my API and would appreciate your help.
1 - In my project, I have the "TAmostra" table, where one of the columns is "Dados", of type "TDados", containing various fields.
TDados = packed record
NumeroSolicitacao: RawUTF8;
DataSolcitacao: TDateTime;
Exames: RawUTF8;
Paciente: RawUTF8;
Posto: RawUTF8;
Atendente: RawUTF8;
DataEtiqueta: TDateTime;
Temperatura: Double;
Motivo: RawUTF8;
Rack: RawUTF8;
Linha: RawUTF8;
Coluna: Int64;
end;
TAmostra = class(TOrm)
private
FNumero: RawUTF8;
FDados: TDados;
published
property Numero: RawUTF8 index 40 read FNumero write FNumero;
property Dados: TDados read FDados write FDados;
end;
TEvento = class(TOrm)
private
FTipo: TTipoEvento;
FAmostra: TAmostra;
published
property Id:
property Tipo: TTipoEvento read FTipo write FTipo;
property Amostra: TAmostra read FAmostra write FAmostra;
end;
How can I create a SELECT query to Evento join with Amostra by Dados.DataSolcitacao? SomethingLike:
SELECT A.*
FROM EVENTO A
LEFT OUTER JOIN AMOSTRA B ON (B.ID = A.ID)
WHERE B.AMOSTRA.DADOS.DATASOLICITACAO BETWEEN '01.12.2023' AND '01.01.2024'
if this not is possible, i can transform TDados in TOrm too, add TDados into TAmostra. then how make this retrieve on ORM?
SELECT A.*
FROM EVENTO A
INNER JOIN AMOSTRA B ON (B.ID = A.ID)
INNER JOIN DADOS C ON (C.ID = B.IDSOLCITACAO)
WHERE C.DATASOLICITACAO BETWEEN '01.12.2023' AND '01.01.2024'
I appreciate any guidance or suggestions you can offer.
Best regards,
Sry guys, my fault!
This is really very confusing!!!! RowID e ID
Hi A. Bouchez
A friend mine was comparing some REST frameworks delphi:
horse (use indy or nethttp) https://github.com/HashLoad/horse
restdataware (use indy or nethttp)
brooks framework (luse ibsagui Cross-platform library) https://github.com/risoflora/brookframework
We was impressive with benchmarks results with this comparassion.
this lib https://risoflora.github.io/libsagui/ is very very very faster and secure. more fast than HTTP.SYS and it is cross-plataform yet.
I know that u like adjust your framework to get more and more peformance.
My intention is this too.
U make a benchmark tool a sometime ago about this, where we can found this tests? can u include this lib on this benchmarks?
Note that the MVC requests are expected to be regular HTTP/Web request from HTML, so are usually GET and sometimes POST (from a form).
....
Please try https://github.com/synopse/mORMot2/commit/82ecb05f
Arnold I see your commit and I think that u put erro on your code no?
fAllowedMethods by default is [mGET, mPOST] but in some parts of code was [mGET, mHEAD]
sample
if publishMvcInfo in fPublishOptions then
fRestServer.ServiceMethodRegister(
before:
MVCINFO_URI, RunOnRestServerRoot, bypass, [mGET, mHEAD]);
now:
MVCINFO_URI, RunOnRestServerRoot, bypass, fAllowedMethods);
I understand u AB!
But we cannot force old companies chage your server to REST. Many companies using SOAP/XML.
I just would like some http socket client that could send a xml soap to this kind of server.
There some way? What base class could i use?
What class I can use to call a third server that use SOAP?
I don´t would like use delphi THttpRIO!
I think that I found a problem guys.
var
vParams1, vParams2: TDocVariantData;
vJson: String;
begin
vParams1.InitJson('{}');
vQueryTmp := NewQuery(' SELECT * FROM EST_ENTRADA_ITEM WHERE CD_ENTRADA_ITEM = 8', []);
vParams.AddOrUpdateValue('RetVariant1', vQueryTmp.FieldByName('CD_ENTRADA_ITEM').Value); // integer field
vParams.AddOrUpdateValue('RetVariant2', vQueryTmp.FieldByName('VL_TOTAL').Value); // float field VarTypeAsText(VarType(pValue)) => double
vParams.AddOrUpdateValue('RetVariant3', vQueryTmp.FieldByName('ST_NOTA_ITEM').Value); // smallint field
vParams.AddOrUpdateValue('RetVariant4', vQueryTmp.FieldByName('DT_CONFIRMACAO').Value); // datetime field
vParams.AddOrUpdateValue('RetVariant5', vQueryTmp.FieldByName('VL_RECEBIDO').Value); // numeric(15,2) field (currency)
vJson := vParams1.ToJson;
// vJson = '{"RetVariant1":8,"RetVariant2":39.9000015258789,"RetVariant3":1,"RetVariant4":"2019-03-15T16:12:21","RetVariant5":29.24}'
vParams2.InitJson(vJson);
vJson := vParams1.ToJson;
// '{"RetVariant1":8,"RetVariant2":"39.9000015258789","RetVariant3":1,"RetVariant4":"2019-03-15T16:12:21","RetVariant5":29.24}'
Why RetVariant2 change to a string? Why not hold 39.9000015258789?
I see that if I does
vParams2.InitJson(vJson, [dvoAllowDoubleValue]);
the value is converted correctly.
by default the options is []?
I solve this way. I hope that do not apear others type of variant.
function TCallServerParams.SetParam(const pName: String; pValue: Variant): ICallServerParams;
var
vSqlTimeStamp: TSQLTimeStamp;
vDateTime: TDateTime;
begin
if VarIsSQLTimeStamp(pValue) then
begin
vSqlTimeStamp := VarToSQLTimeStamp(pValue);
vDateTime := SQLTimeStampToDateTime(vSqlTimeStamp);
FParams.AddOrUpdateValue(pName, vDateTime)
end else
FParams.AddOrUpdateValue(pName, pValue);
Result := Self;
end;
You can play a bit with SQLTimeStampVariantType. Delphi just needs to know what you want:
var value: Variant := VarSQLTimeStampCreate(Now); // <- Variant of type SQLTimeStampVariantType
Or have a look in Unit Data.SqlTimSt at class TSQLTimeStampData and TSQLTimeStampVariantType.
With best regards
Thomas
I know that if i convert the variant all works TBO. But I need avoid that my programmers fails during use of this. then I was thinking generate a exception on cases were the mormot cannot identify the vartype! Do you understand?
I don't think such a method exists.
But its possible make it?
Where I can find on source code mormot.variants what vartypes it can treat?
Thanks guys.
A last question. There are something function to validate a variant type to know when mormot does not know what to do with the value of this variant?
I know that using. FieldByName('DT_NASC').AsDateTime or VarToDateTime(...) works
but some other programmer can do not use this way and cause erros on runtime!
There are something that can be done on mormot internal to dont need make this conversion by hand????
I have edited my post.
ShowMessage(VarTypeAsText(VarType(vValue))) => SQLTimeStampVariantType
looking for this type i found in SqlTimst.pas
TSQLTimeStampVariantType = class(TPublishableVariantType)
SQLTimeStampVariantType: TSQLTimeStampVariantType = nil;
I think that this type of variant not is treated by TDocVariantData
How we can resolve this?
var
vValue: Variant;
vQueryTmp: TSQLQuery;
vParams: TDocVariantData;
const
cSQL = ' SELECT DT_NASC FROM PACIENTE WHERE CD_PACIENTE = 1';
begin
vQueryTmp := NewQuery(cSQL, []);
try
vQueryTmp.First;
vValue := vQueryTmp.FieldByName('DT_NASC').Value; // vValue here has '01/07/2020'
finally
FreeQuery(vQueryTmp);
end;
vParams.InitJson('{}');
vParams.AddOrUpdateValue('MyInt', 10);
vParams.AddOrUpdateValue('MyBoolean', True);
vParams.AddOrUpdateValue('MyVariantDate', vValue);
ShowMessage(vParams.ToJson) => {"Myint":10,"MyBoolean":true,"MyVariantDate":null}' ?????
I was expecting {"Myint":10,"MyBoolean":true,"MyVariantDate": '2020-07-01T00:00:00'}'
What I should?
Tnks. I will study Better the auth mechanism I want jwt with authuser and authsession control.
When i ahould be use this in rest options?
i have created this record
TUsuarioRecuperaSenha = packed record
UserPass: RawByteString;
end;
...
TUsuarioService = class(TServiceBase, IUsuarioService)
function TrocaSenha(const pUsuario: TUsuarioTrocaSenha) : TServiceCustomAnswer;
...
GetApp.RegisterService(TUsuarioService, [TypeInfo(IUsuarioService)]);
GetApp.Put('/usuariotrocasenha', 'usuarioService/trocasenha');
I got erro on this part of mormot
constructor TInterfaceFactory.Create(aInterface: PRttiInfo);
...
imvRecord:
if ArgRtti.Size <= POINTERBYTES then <<<<<<<<<<<<<<<<<<< shouldnt be "if ArgRtti.Size < POINTERBYTES then" ??????????
raise EInterfaceFactory.CreateUtf8(
'%.Create: % record too small in %.% method % parameter: it ' +
'should be at least % bytes (i.e. a pointer) to be on stack',
[self, ArgTypeName^, fInterfaceName, URI, ParamName^, POINTERBYTES]);
If i put more fields on record it works
I see some posts on group about jwt. for sample:
1. https://synopse.info/forum/viewtopic.php?id=4840
but i have some problems with retrieve session, release session, etc..?
the implementation create 1 session to each request, when should be by user (TAuthSession.AuthUser).
I need refreshtoken too.
Anyone has a more complete implementation?
Anyone can help us?
To be honest, I don't expect "with ... do begin ... end" to work with late binding.
It is very confusing for the compiler and human reader.
Whatever solution you would find (like a pointer) just adds confusion.I won't use late binding for such code, but rather TDocVariantData.Update([]) by name/value pairs.
Perhaps with _Safe().
You sugestion is something like this?
TMyClasse = class
private
FParams: TDocVariantData;
public
procedure Params(const pName: String; pValue: Variant);
...
constructor TMyClasse.Create;
begin
inherited;
FParams.Init; <<<<<<< _Safe(Need Variant) and return PDocVariant, then I have used .Init. Some problem with this?
end;
procedure TMyClasse.Params(const pName: String; pValue: Variant);
begin
FParams.AddOrUpdateValue(pName, pValue); <<<<<<<<
end;
There are some way to use a property published to use latebiding? If yes, what type should be this property? else thanks AB! I will following this way.