mORMot Open Source

corchi72

Member

Registered: 2010-12-10

Posts: 232

Why did you decide to keep the record of relationships and set val "0"

Why did you decide to keep the record of relationships and set the value to 0 rather than deleting the record, remain a lot of unnecessary records of relationships
I have written

function TSQLRestServer.AfterDeleteForceCoherency(Table: TSQLRecordClass;
  aID: integer): boolean;
var T, Where: integer;
    RecRef: TRecordReference;
begin
  result := true; // success if no property found
  Where := 0; // make compiler happy
  RecRef := RecordReference(Model,Table,aID);
  if RecRef<>0 then
  for T := 0 to high(Model.RecordReferences) do
  with Model.RecordReferences[T] do begin
    case FieldType of
    sftRecord: // TRecordReference published field
      Where := RecRef;
    sftID:     // TSQLRecord published field
      if FieldRecordClass=Table then
        Where := aID else
        continue;
    else continue;
    end;
    // set Field=0 where Field references aID
//    UpdateField(Model.Tables[TableIndex],Where,FieldName^,0,False);  
      Delete(Model.Tables[TableIndex],Where);
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Why did you decide to keep the record of relationships and set val "0"

It was because there was some other fields in this record.

In fact, SQL triggers allow either to delete the row, either to reset the column.
In our framework, we only reset to zero. It is a choice.

You have some events trigerred when a record is deleted in order to make you own coherency scheme.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: Why did you decide to keep the record of relationships and set val "0"

for one table I have three or four tables of relationships,

each table is related to the tables of the user roles and groups

Last edited by corchi72 (2011-09-22 14:48:03)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Why did you decide to keep the record of relationships and set val "0"

You can do this automatically by adding a simple SQLite3 trigger in plain SQL.

See http://www.sqlite.org/lang_createtrigger.html

So it will be done by the database without any Delphi code.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: Why did you decide to keep the record of relationships and set val "0"

how to insert a trigger from delphi using your classes

coblongpamor

Member

From: Bali-Indonesia

Registered: 2010-11-07

Posts: 130

Website

Re: Why did you decide to keep the record of relationships and set val "0"

how to insert a trigger from delphi using your classes

i did tried something like this,

TTriggerEvent = (teInsert, teUpdate, teDelete, teUpdateOf);
  TTriggerTime = (ttBefore, ttAfter);
  TTriggerKind = (tkForEachRow, tkForEachStatement);
  TTriggerEventTime = (
    tetBeforeInsert, tetBeforeUpdate, tetBeforeUpdateOf, tetBeforeDelete,
    tetAfterInsert, tetAfterUpdate, tetAfterUpdateOf, tetAfterDelete);

  TSQLTrigger = record
    IsTemporary:Boolean;
    TriggerEvent:TTriggerEvent;
    TriggerTime:TTriggerTime;
    UpdateOf:RawUTF8;
    TriggerKind:TTriggerKind;
    WhenClause:RawUTF8;
    TriggerSQL:RawUTF8;
  end;
  
  
const
  TRGSQL_MASTER_CODE_AIRBUR=
    '  UPDATE % SET '+CR+
    '    Number3 = '+CR+
    '      CASE WHEN LENGTH(NEW.Number4) >= 4 '+CR+
    '        THEN SUBSTR(NEW.Number4,2,3) '+CR+
    '        ELSE NEW.Number4 '+CR+
    '      END, '+CR+
    '    Number2 = '+CR+
    '      CASE WHEN LENGTH(NEW.Number4) >= 3 '+CR+
    '        THEN SUBSTR(NEW.Number4,3,2) '+CR+
    '        ELSE NEW.Number4 '+CR+
    '      END, '+CR+
    '    ManualCount = 0 '+CR+
    '  WHERE ID = %.ID; ';
  EventSQL:array[TTriggerEvent] of string=('INSERT','UPDATE','DELETE','UPDATE OF');
  TimeSQL:array[TTriggerTime] of string=('BEFORE','AFTER');
  KindSQL:array[TTriggerKind] of string=('FOR EACH ROW','FOR EACH STATEMENT');
  
function SetTriggerParameter(aSQLTrigger: TSQLTrigger;
  aTET: TTriggerEventTime; aUpdateOf: RawUTF8=''): TSQLTrigger;
begin
  if aUpdateOf<>'' then begin
    Assert(aTET in [tetBeforeUpdateOf,tetAfterUpdateOf],'Error Parameter Update of');
  end;

  if aTET in [tetBeforeUpdateOf,tetAfterUpdateOf] then begin
    Assert(aUpdateOf<>'','Error Parameter Update of');
  end;

  with aSQLTrigger do begin
    case aTET of
      tetBeforeInsert..tetBeforeDelete:
        TriggerTime:= ttBefore;
      tetAfterInsert..tetAfterDelete:
        TriggerTime:= ttAfter;
    end;
    case aTET of
      tetBeforeInsert,tetAfterInsert:
        TriggerEvent:= teInsert;
      tetBeforeUpdate,tetAfterUpdate:
        TriggerEvent:= teUpdate;
      tetBeforeUpdateOf,tetAfterUpdateOf:
        TriggerEvent:= teUpdateOf;
      tetBeforeDelete,tetAfterDelete:
        TriggerEvent:= teDelete;
    end;
    UpdateOf:= aUpdateOf;
    TriggerKind:= tkForEachRow;
    IsTemporary:= False;
    WhenClause:= '';
  end;
  Result:= aSQLTrigger;
end;

function GenerateTriggerSQL(aTableName: RawUTF8; aSQLTrigger: TSQLTrigger): RawUTF8;
var
  TriggerName: RawUTF8;
  SQL: RawUTF8;
begin
  with aSQLTrigger do
  begin
    TriggerName:=FormatUTF8('TRG_%_%%R',
       [aTableName,LeftStr(TimeSQL[TriggerTime],1),LeftStr(EventSQL[TriggerEvent],1)]);
    SQL:=FormatUTF8('DROP TRIGGER IF EXISTS [%];%',[TriggerName,CR]);
    SQL:=SQL+'CREATE ';
    if IsTemporary then
       SQL:=SQL+'TEMPORARY ';
    SQL := SQL+FormatUTF8('TRIGGER IF NOT EXISTS [%]%',[TriggerName,CR]);
    SQL := SQL+FormatUTF8('% % %%',[TimeSQL[TriggerTime],EventSQL[TriggerEvent],UpdateOf,CR]);
    SQL := SQL+FormatUTF8('ON %%',[aTableName,CR]);
    SQL := SQL+FormatUTF8('%%',[KindSQL[TriggerKind],CR]);
    SQL := SQL+FormatUTF8('%%',[WhenClause,CR]);
    SQL := SQL+FormatUTF8('%%',['BEGIN',CR]);
    SQL := SQL+FormatUTF8('%%',[TriggerSQL,CR]);
    SQL := SQL+FormatUTF8('%%',['END',CR]);
  end;

  Result := SQL;
end;

function TFileServer.ExecuteTrigger(aRecordClass: TSQLRecordClass;
  aTET: TTriggerEventTime; aTriggerSQL: RawUTF8; aUpdateOf: RawUTF8=''): RawUTF8;
var
  aTrigger: TSQLTrigger;
  aTableName: RawUTF8;
  aTriggerDef: RawUTF8;
begin
  aTableName:= aRecordClass.SQLTableName;
  aTrigger:=SetTriggerParameter(aTrigger,aTET,aUpdateOf);
  with aTrigger do
    TriggerSQL:= aTriggerSQL;
  aTriggerDef:= GenerateTriggerSQL(aTableName, aTrigger);
  Clipboard.AsText:=aTriggerDef;
  with Model do
    EngineExecuteAll(aTriggerDef);
end;

constructor TFileServer.Create;
var
  aDB:TFileName;
begin
  aDB:='sgp.dat';
  inherited Create(CreateFileModel(self),aDB);
  CreateMissingTables(ExeVersion.Version.Version32);
  Server := TSQLite3HttpServer.Create(SERVER_HTTP_PORT,self);
  AddAuditTrail(feServerStarted);
  OnUpdateEvent := OnDatabaseUpdateEvent;
  //DB.user_version:=0;
  if  DB.user_version<1 then
  begin
    ExecuteTrigger(TSQLMasterCode,tetAfterInsert,FormatUTF8(TRGSQL_MASTER_CODE_AIRBUR,[TSQLMasterCode.SQLTableName,'NEW']));
    ExecuteTrigger(TSQLMasterCode,tetBeforeUpdateOf,FormatUTF8(TRGSQL_MASTER_CODE_AIRBUR,[TSQLMasterCode.SQLTableName,'NEW']),'Number4');
    ...
    ....
    DB.user_version:=1;
  end;
end;

this is work as expected. the trigger shown when i open database with third party sqlite manager.
but, i think, my code is still dirty, since i don't know more about RTTI.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Why did you decide to keep the record of relationships and set val "0"

This is some very nice piece of code.

I'll try to add it to the framework.

But perhaps it may need some testing to ensure it works as expected in most cases.
Covering most test cases related to this will need some time to guess, I imagine.

Thanks!

coblongpamor

Member

From: Bali-Indonesia

Registered: 2010-11-07

Posts: 130

Website

Re: Why did you decide to keep the record of relationships and set val "0"

I think, Synopse/mORMot frame is realy great. i imagine, that i can create demo app with embedded db model, and simply change to the client/server model in the future with just change TSQLResClient and TSQLResServer class.

i open this forum everyday, just want to know, if there something new or isue.
this forum is the first most visited website in my google chrome browser (second is facebook )

but, till know, some time there are exception occured that i can't fix for my self.
i use the maindemo sample to learn the framework.

e.g try to change maindemo sample to embedded db model like this,

//TFileClient = class(TSQLite3HttpClient)
  TFileClient = class(TSQLRestClientDB)
  public
    DB: TSQLDatabase;
    Model: TSQLModel;
    Filename: TFileName;

//contructor
constructor TFileClient.Create(const aServer: AnsiString);
begin
  //inherited Create(aServer,SERVER_HTTP_PORT,CreateFileModel(self));
  Model:= CreateFileModel(Self);
  Filename:= ChangeFileExt(paramstr(0),'.dat');
  //TSQLRestClientDB.create
  inherited Create(Model,Model,Filename,TSQLRestServerDB,False);
  Server.CreateMissingTables(ExeVersion.Version.Version32);
  ForceBlobTransfert := true;
end;

and then in the MainForm i disable DEBUGINTERNALSERVER

//{$define DEBUGINTERNALSERVER}

but the edit action is not working. cause

Tab.Retrieve(Client,Tab.List.Row,true)

is always return false.
and then if terminate the application, FastMM reported too many memory leaks.

is this bug in the framework or i do something wrong in my code?

thanks for your the hard work, and keep the framework open source.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Why did you decide to keep the record of relationships and set val "0"

The server part is implementing also some services, so you should better use a TSQLRestClientURIDll then direct link to a modified TFileServer, not using an internal TSQLite3HttpServer, which is not necessary any more.
Or use TSQLRestClientURI, but define TFileServer as the server class, instead of TSQLRestServerDB, to dispatch also the services.

It will use direct in-process call instead of HTTP.

Thanks a lot for all your interest!