#1 2011-06-13 12:31:43

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

How to Implement Trigger and Stored Procedure

Hallo every one

I have readed the documentation about Stored Procedure,
and i found TOnSQLStoredProc and TSQLRestServerDB.EngineExecute,
but i don't know how to use it.

I imagine can write code like this (like regular database engine)

  TSQLSales = class(TSQLRecord)
  private
  protected
  public
    fLineTotal: Currency;
  published
  end;

  TSQLSaleLines = class(TSQLRecord)
  private
    procedure OnBeforeInsert;
    procedure OnBeforeUpdate;
    
    procedure OnAfterInsert;
    procedure OnAfterUpdate;
    procedure OnAfterDelete;
  protected
  public
     fSale:TSQLSales;
     fItem: TSQLItem;
     fQuantity: Double;
     fPrice: Currency;
     fSubtotal: Currency;
  published
     property Item: TSQLItemread fItem write fItem;
     property Quantity: Doubleread fQuantity write fQuantity;
     property Price: Currencyread fPrice write fPrice;
     property Subtotal: Currency read fSubtotal write fSubtotal;
  end;
  ..
  ..
  ..

  implementation

    procedure TSQLSaleLines.OnBeforeInsert;
    begin
       Subtotal:=NEW.Quantity * NEW.Price;
    end;
    
    procedure TSQLSaleLines.OnBeforeUpdate;
    begin
      if NEW.Quantity<>OLD.Quantity or NEW.Price<>OLD.Price then
        Subtotal:=NEW.Quantity * NEW.Price;
    end;
    
    procedure TSQLSaleLines.OnAfterInsert;
    begin
       with fSale do
         fLineTotal:=fLineTotal+NEW.Subtotal;
       
       (*in regular db engine i use this code
       update TSQLSale Set
           LineTotal=LineTotal+NEW.Subtotal
       where SaleID=NEW.SaleID;
     *)
    end;
        
    procedure TSQLSaleLines.OnAfterUpdate;
    begin
       if NEW.Subtotal<>OLD.Subtotal then 
       with fSale do
         fLineTotal:=fLineTotal+NEW.Subtotal-OLD.Subtotal;
       
       (*in regular db engine i use this code
       if NEW.Subtotal<>OLD.Subtotal then  
         update TSQLSale Set
           LineTotal=LineTotal+NEW.Subtotal-OLD.Subtotal
         where SaleID=OLD.SaleID
        endif;
     *)
    end;
        
    procedure TSQLSaleLines.OnAfterDelete;
    begin
       with fSale do
         fLineTotal:=fLineTotal-OLD.Subtotal;
       
       (*in regular db engine i use this code
       update TSQLSale Set
           LineTotal=LineTotal-OLD.Subtotal
       where SaleID=OLD.SaleID;
     *)
    end;
    

how can we do that with our ORM?

i'm sorry about my english.
thanks

Offline

#2 2011-06-13 13:29:17

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

Re: How to Implement Trigger and Stored Procedure

"Don't think database, think objects".

So to set up some calculated fields, use a Set.....() protected method for the write published field attribute.
Just a regular Delphi class.

Offline

#3 2011-06-14 06:12:31

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

But, i want to implemented on the server side (database level) not on the client side. so, every new client app i make, no need to write it any more.

i'm not an expert with class

Offline

#4 2011-06-14 08:00:45

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

Re: How to Implement Trigger and Stored Procedure

On the Server side, you can use setters for properties, also.

But it will be more difficult.
Perhaps some new features to be introduced.
I'll think about it, to find out the best implementation I could do.

Offline

#5 2011-06-14 09:01:44

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

thanks for the response.

I need some event for insert,update,delete (before and after) on TSQLRecord,
to calculate some internal fields or update value on other TSQLRecord class.

thanks..

Offline

#6 2011-06-14 09:26:38

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

Re: How to Implement Trigger and Stored Procedure

coblongpamor wrote:

I need some event for insert,update,delete (before and after) on TSQLRecord,
to calculate some internal fields or update value on other TSQLRecord class.

The fact is that, in the current framework implementation, the UPDATE SQL statement is created directly from the supplied JSON values, directly retrieved from the Client.
So the TSQLRecord is NOT used on the server side, in case of a row update (this is the same for adding).
See this code:

function TSQLRestServerDB.EngineUpdate(Table: TSQLRecordClass; ID: integer;
  const SentData: RawUTF8): boolean;
begin
  if (self=nil) or (Table=nil) or (ID<=0) then
    result := false else begin
    // this SQL statement use :(inlined params): for all values
    result := EngineExecuteFmt('UPDATE % SET % WHERE RowID=:(%):;',
      [Table.RecordProps.SQLTableName,GetJSONObjectAsSQL(SentData,true,true),ID]);
    if Assigned(OnUpdateEvent) then
       OnUpdateEvent(self,seUpdate,Table,ID);
  end;
end;

The OnUpdateEvent is called BEFORE deletion, and AFTER insertion or update, on the server side only.
So it was not defined to change the values property.

So in the current implementation, these calculated properties should be computed on the Client side, for internal calculation only.
This was the purpose of using setters in the TSQLRecord child definition (via write Set*** syntax).
Those setters will be common to the class definition, so will be shared among all clients, and also available on the Server side, when working at the ORM level, not the SQL level.
Those setters are meant to calculate some internal fields, but NOT meant to update value on other TSQLRecord class: this would break the OOP principle to have the object stay at its own level (a TSQLRecord should not normally have access to the TSQLRest level, for instance).

So the short answer is that you should use TSQLRecord-level calculated properties, which will be computed on the Client side, then directly sent to the Client.
If you need a lot of DB process in order to compute the clients, then you should use a dedicated server-side Service, which will handle all the process for you.
IMHO there is no need to add such server side event, or we'll break the ORM architecture. Those events would not be record related, but server related. So the place to implement it won't be at the TSQLRecord level (or only if it stays at the record level), but it could be implemented as a Service.

Offline

#7 2011-06-14 10:09:02

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

Re: How to Implement Trigger and Stored Procedure

I've updated the documentation as such:


Calculated fields

It is often useful to handle some calculated fields. That is, having some field values computed when you set another field value. For instance, if you set an error code from an enumeration (stored in an INTEGER field), you may want the corresponding text (to be stored on a TEXT field). Or you may want a total amount to be computed automatically from some detailed records.

This should not be done on the Server side. In fact, the framework expects the transmitted JSON transmitted from client to be set directly to the database layer, as stated by this code from the SQLite3 unit:

function TSQLRestServerDB.EngineUpdate(Table: TSQLRecordClass; ID: integer;
  const SentData: RawUTF8): boolean;
begin
  if (self=nil) or (Table=nil) or (ID<=0) then
    result := false else begin
    // this SQL statement use :(inlined params): for all values
    result := EngineExecuteFmt('UPDATE % SET % WHERE RowID=:(%):;',
      [Table.RecordProps.SQLTableName,GetJSONObjectAsSQL(SentData,true,true),ID]);
    if Assigned(OnUpdateEvent) then
       OnUpdateEvent(self,seUpdate,Table,ID);
  end;
end;

The direct conversion from the received JSON content into the SQL UPDATE statement values is performed very quickly via the GetJSONObjectAsSQL procedure. It won't use any intermediary TSQLRecord, so there will be no server-side field calculation possible.

Record-level calculated fields should be done on the Client side, using some setters.

For instance, here we define a new table named INVOICE, with only two fields. A dynamic array containing the invoice details, then a field with the total amount. The dynamic array property will be stored as BLOB into the database, and no additional Master/Detail table will be necessary.

type
  TInvoiceRec = record
    Ident: RawUTF8;
    Amount: currency;
  end;
  TInvoiceRecs = array of TInvoiceRec;
  TSQLInvoice = class(TSQLRecord)
  protected
    fDetails: TInvoiceRecs;
    fTotal: Currency;
    procedure SetDetails(const Value: TInvoiceRecs);
  published
    property Details: TInvoiceRecs read fDetails write SetDetails;
    property Total: Currency read fTotal;
  end;

Note that the Total property does not have any setter (aka write statement). So it will be read-only, from the ORM point of view. In fact, the following protected method will compute the Total property content from the Details property values, when they will be modified:

procedure TSQLInvoice.SetDetails(const Value: TInvoiceRecs);
var i: integer;
begin
  fDetails := Value;
  fTotal := 0;
  for i := 0 to high(Value) do
    fTotal := fTotal+Value[ i ].Amount;
end;

When the object content will be sent to the Server, the Total value of the JSON content sent will contain the expected value.

Note that with this implementation, the SetDetails must be called explicitly. That is, you should not only modify directly the Details[] array content, but either use a temporary array during edition then assign its value to Invoice.Details, either force the update with a line of code like:

Invoice.Details := Invoice.Details; // force Total calculation

If the computed fields need a more complex implementation (e.g. if some properties of another record should be modified), a dedicated RESTful service should be implemented.
Adding cross-objects methods would break the OOP principle to have the object stay at its own level: a TSQLRecord should not normally have access to the TSQLRest level, for instance, but a service could be defined on the TSQLRestServer level to handle this process.

Offline

#8 2011-06-15 03:02:56

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

i think i rather understand what you mean as described above.
but will be realy dificult to implemented for real application with my ability.

but i tried to write test trigger manualy on SQLiteExpert (http://www.sqliteexpert.com) as such

CREATE TRIGGER [TRG_Account_AUR_TEST]
AFTER UPDATE
ON Account
FOR EACH ROW
BEGIN
  UPDATE Account SET
        IsCreditBalance=1
    WHERE ID=OLD.ID
        AND AccountClass IN(1,2,4,6);           
  
    UPDATE Account SET
        IsCreditBalance=0
    WHERE ID=OLD.ID
        AND AccountClass IN(0,3,5,7);
END

i tried to edit some records directly on the SQLiteExpert and from then maindemo app,
and the trigger work as expected.

but i don't know what is the side efect with the framework..

thanks..

Offline

#9 2011-06-15 03:12:44

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

if no side effects, i think, i'll do this method.

write tables defenition with the framework, and then write stored proc, trigger, etc on the external DB Administrator, e.g SQLiteExpert.

Offline

#10 2011-06-15 03:38:23

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

another thing,

if Field declared as TSQLRecord class, the framework will automaticly add index coresponding to the field, right?

//account types
  TAccountType= (atHeader,atDetail,atBank,atSystem);
  //account classifications
  TAccountClass= (acAsset,acLiability,acEquity,acCoS,acIncome,acExpense,acOtherIncome,acOtherExpense);

TSQLAccount= class(TSQLBaseMaster)
  private
     fOpeningBalance: Currency;
     fCurrentBalance: Currency;

     fParenAccount: TSQLAccount;
     fAccountLevel: Integer;
     fIsCreditBalance: Boolean;
     fAccountType: TAccountType;
     fAccountClass: TAccountClass;
     fAccountSubClass: TSQLAccountSubClass;
     //fOnCalcFields: TSQLRecordNotifyEvent;
  protected

  public
  published
     property OpeningBalance: Currency read fOpeningBalance write fOpeningBalance;
     property CurrentBalance: Currency read fCurrentBalance write fCurrentBalance;

     property ParenAccount: TSQLAccount read fParenAccount write fParenAccount;
     property AccountLevel: Integer read fAccountLevel write fAccountLevel;
     property IsCreditBalance: Boolean read fIsCreditBalance write fIsCreditBalance;

     property AccountType: TAccountType read fAccountType write fAccountType default atDetail;
     property AccountClass: TAccountClass read fAccountClass write fAccountClass;
     property AccountSubClass: TSQLAccountSubClass read fAccountSubClass write fAccountSubClass;

     //property OnCalcFields: TSQLRecordNotifyEvent read fOnCalcFields write fOnCalcFields;
  end;

framework will automaticly create an index for fields ParenAccount, and AccountSubClass..

i see it when i open DB file with SQLiteExpert

i imagine that i can write some Properties for the trigger on TSQLAccount and then i can see it on the e.g SQLiteExpert..
is it possible..?

thanks.

Last edited by coblongpamor (2011-06-15 03:40:17)

Offline

#11 2011-06-15 05:22:01

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

Re: How to Implement Trigger and Stored Procedure

coblongpamorbut wrote:

i don't know what is the side efect with the framework..

Since the trigger occurs during a table update, the global status flag of the ORM will be changed, so the framework will handle it as expected, for instance in the Refresh method.
I don't think there will be side effects.

i imagine that i can write some Properties for the trigger on TSQLAccount and then i can see it on the e.g SQLiteExpert..
is it possible..?

You can override the following method for your TSQLAccount class in order to create the SQL triggers, just as the initial TSQLRecord method creates the index:

class procedure TSQLRecord.InitializeTable(Server: TSQLRestServer; const FieldName: RawUTF8);
var f: integer;
begin // is not part of TSQLRecordProperties because has been declared as virtual
  if (self<>nil) and (Server<>nil) then
  with RecordProps do
  for f := 0 to high(Fields) do
    if FieldType[f] in [sftRecord, sftID] then
      if (FieldName='') or IdemPropNameU(FieldName,FieldsName[f]) then
          Server.CreateSQLIndex(self,FieldsName[f],false);
end;

Offline

#12 2011-06-15 09:22:59

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

i was learn the method InitializeTable, but i don't know how to implement in the TSQLRecord level..

and i found TSQLRestServer.CreateSQLMultiIndex method, then i try to do these (i use maindemo project)

in the filetables unit i declare some types like this:

  TTriggerEvent=(teInsert, teUpdate, teDelete, teUpdateOf);
  TTriggerTime=(ttBefore, ttAfter);
  TTriggerKind=(tkForEachRow, tkForEachStatement);
  
  TSQLTrigger=record
    IsTemporary:Boolean;
    TriggerEvent:TTriggerEvent;
    TriggerTime:TTriggerTime;
    TriggerKind:TTriggerKind;
    WhenClause:RawUTF8;
    TriggerSQL:RawUTF8;
  end;

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

then, in the FileServer unit i add two public functions

function TFileServer.CreateSQLTrigger(aTable:TSQLRecordClass; aSQLTrigger:TSQLTrigger):Boolean;
const
   CR=#13;
var
   TriggerName:RawUTF8;
   SQL: RawUTF8;
   TableIndex: integer;
   Props: TSQLRecordProperties;
begin
   result:=False;
   if Self=nil then
      exit;
   TableIndex := Model.GetTableIndex(aTable);
   if (TableIndex<0) or
      ((fStaticData<>nil) and (fStaticData[TableIndex]<>nil)) then
         exit; // invalid Table or in Static data (Trigger not needed)
   Props := Model.TableProps[TableIndex];
   with aSQLTrigger do
   begin
      TriggerName:=FormatUTF8('TRG_%_%%R',
         [Props.SQLTableName,LeftStr(TimeSQL[TriggerTime],1),LeftStr(EventSQL[TriggerEvent],1)]);
      SQL:=FormatUTF8('DROP TRIGGER IF EXISTS [%];%',[TriggerName,CR]);
      SQL:='CREATE ';
      if IsTemporary then
         SQL:=SQL+'TEMPORARY ';
      SQL := SQL+FormatUTF8('TRIGGER IF NOT EXISTS [%]%',[TriggerName,CR]);
      SQL := SQL+FormatUTF8('% %%',[TimeSQL[TriggerTime],EventSQL[TriggerEvent],CR]);
      SQL := SQL+FormatUTF8('ON %%',[Props.SQLTableName,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 := EngineExecuteAll(SQL);
end;

function TFileServer.AccountCreateTriggerBUR:Boolean;
const
   CR=#13;
var
   aTrigger:TSQLTrigger;
begin
   with aTrigger do
   begin
      TriggerEvent:=teUpdate;
      TriggerTime:=ttAfter;
      TriggerKind:=tkForEachRow;
      IsTemporary:=False;
      WhenClause:='';
      TriggerSQL:=
         '  UPDATE Account SET '+CR+
         '     IsCreditBalance=1 '+CR+
         '  WHERE ID=OLD.ID '+CR+
         '     AND AccountClass IN(1,2,4,6); '+CR+
          '  UPDATE Account SET '+CR+
         '     IsCreditBalance=0 '+CR+
          '  WHERE ID=OLD.ID '+CR+
         '     AND AccountClass IN(0,3,5,7); '+CR;
   end;
   result:=CreateSQLTrigger(TSQLAccount,aTrigger);

end;

and then i call AccountCreateTriggerBUR on then TFileServer Constructor.
and everything work as expected

but i think maybe this is not the simple way.
could you suggest me the better and more simple and more object orientation?

maybe if possible can implemented in the TSQLRecord level..

thanks..

Offline

#13 2011-06-15 09:35:46

Leander007
Member
From: Slovenia
Registered: 2011-04-29
Posts: 113

Re: How to Implement Trigger and Stored Procedure

Here is the way how the InitializeTable is used in my example:

TSQLToken = class(TSQLRecord)
(...)
public
  class procedure InitializeTable(Server: TSQLRestServer; const FieldName: RawUTF8); override;
(...)

and then in implementation

class procedure TSQLToken.InitializeTable(Server: TSQLRestServer;
  const FieldName: RawUTF8);
var
(...)
begin
  inherited InitializeTable(Server, FieldName);
  if FieldName = '' then begin //'' is for table creation in e.g. CreateMissingTables
    TI := TypeInfo(TSystemMessage);
    AddEnumToTable(TI);
    TI := TypeInfo(TPumpMessage);
    AddEnumToTable(TI, true);
  end;
end;

"Uncertainty in science: There no doubt exist natural laws, but once this fine reason of ours was corrupted, it corrupted everything.", Blaise Pascal

Offline

#14 2011-06-15 09:38:23

Leander007
Member
From: Slovenia
Registered: 2011-04-29
Posts: 113

Re: How to Implement Trigger and Stored Procedure

I was using InitializeTable for filling the delphi enum in some reference table (on table creation), where the enum's could be translated (not i18n) in some other text.


"Uncertainty in science: There no doubt exist natural laws, but once this fine reason of ours was corrupted, it corrupted everything.", Blaise Pascal

Offline

#15 2011-06-15 10:06:39

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

thank you, i'll try to learn more abaut the example.
when the InitializeTable called?

i'll be realy appreciate if you could give more detail example, since i'm not understand about RTTI.

thanks.

Offline

#16 2011-06-15 10:17:48

Leander007
Member
From: Slovenia
Registered: 2011-04-29
Posts: 113

Re: How to Implement Trigger and Stored Procedure

Forget RTTI, that was just used in my example.

You just need to override next procedure in your derived class in which you need the trigger on related table:

public
  class procedure InitializeTable(Server: TSQLRestServer; const FieldName: RawUTF8); override; 
(...)

and then in implementation:

class procedure TSQLToken.InitializeTable(Server: TSQLRestServer;
  const FieldName: RawUTF8);
begin
  inherited InitializeTable(Server, FieldName);
  if FieldName = '' then begin //'' empty string means that the table is in creation faze (alias OnCreateTable)...
    //Here comes your code which initialize some table data (in my case Token table) or do something else
    //You could create your trigger at this stage with Server.EngineExecuteAll 
  end;
end;

This is the proper object way, but you can achieve the same goal in many ways.

Last edited by Leander007 (2011-06-15 10:29:54)


"Uncertainty in science: There no doubt exist natural laws, but once this fine reason of ours was corrupted, it corrupted everything.", Blaise Pascal

Offline

#17 2011-06-15 10:38:27

Leander007
Member
From: Slovenia
Registered: 2011-04-29
Posts: 113

Re: How to Implement Trigger and Stored Procedure

You don't need to check for trigger existence, because you know that is not yet created, so just call "create trigger...".


"Uncertainty in science: There no doubt exist natural laws, but once this fine reason of ours was corrupted, it corrupted everything.", Blaise Pascal

Offline

#18 2011-06-15 10:42:39

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

i think i understand now. did you mean like this..?

  class procedure TSQLToken.InitializeTable(Server: TSQLRestServer;
  const FieldName: RawUTF8);
begin
  inherited InitializeTable(Server, FieldName);
  if FieldName = '' then begin
    Server.EngineExecuteAll(FieldName);
  end;
end;

that FieldName Contain complete SQL Trigger syntact..?

thanks..

Offline

#19 2011-06-15 10:54:52

Leander007
Member
From: Slovenia
Registered: 2011-04-29
Posts: 113

Re: How to Implement Trigger and Stored Procedure

No , this is not correct. FieldName is name of field (column).

With test

if FieldName = '' then 

you know that the table is in CREATION faze (could be named OnCreateTable) and you can execute the trigger creation at this stage (Table is created, so create the trigger too). You don't need other checks (this is the benefit of this approach).
Just override mentioned procedure for each TSQLRecord descendant which needs trigger (your case) or something else.

You must provide whole DDL sentence for trigger creation in Server.EngineExecuteAll(WholeDDLneeded).

Last edited by Leander007 (2011-06-15 10:58:49)


"Uncertainty in science: There no doubt exist natural laws, but once this fine reason of ours was corrupted, it corrupted everything.", Blaise Pascal

Offline

#20 2011-06-15 11:13:15

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

did you mean like this..?

class procedure TSQLToken.InitializeTable(Server: TSQLRestServer;
  const FieldName: RawUTF8);
var
  aSQLTrigger:RawUTF8;
begin
  inherited InitializeTable(Server, FieldName);
  if FieldName = '' then begin
    aSQLTrigger='CREATE TRIGGER ....';
    Server.EngineExecuteAll(aSQLTrigger);
  end;
end;

Offline

#21 2011-06-15 11:14:09

Leander007
Member
From: Slovenia
Registered: 2011-04-29
Posts: 113

Re: How to Implement Trigger and Stored Procedure

Yes smile


"Uncertainty in science: There no doubt exist natural laws, but once this fine reason of ours was corrupted, it corrupted everything.", Blaise Pascal

Offline

#22 2011-06-15 11:27:01

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

oh...finaly.., thank you very much...

Offline

#23 2011-06-15 11:53:12

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

Re: How to Implement Trigger and Stored Procedure

Nice chat!

I'm very pleased seeing users help each other...
My answer was not clear enough...

Thanks to you!
smile

Offline

#24 2011-06-16 02:49:12

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

but i think there is some isues with ovorriding InitializeTable, in this case adding trigger to the database.
if i need to modify the trigger definition, i must drop the table first, and i'll lose data contained in the table.

i know that i can change trigger definition in the third party DB administrator e.g SQLite Expert, but i think is better to do that with pure delphi code in our frame work.

is possible to create other DB objects e.g trigger adhering to TSQLRecord object, View,.. without breaking OOP and our framework?

thank for all of you in this forum for supported.

Offline

#25 2011-06-16 05:42:19

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

Re: How to Implement Trigger and Stored Procedure

In order to change the trigger content, AND stay compatible with existing database content, you may have to work at the database level, not at the record level.

In this case, you may have to use the database user version.
You can set this database user version by setting a non zero parameter value to the TSQLRestServerDB.CreateMissingTables(user_version: cardinal) method: it will update the internal database version. Be aware that the database version will be updated only if some new tables or fields have been created. But you can also set this user_version by hand using a simple PRAGMA statement.

Then, when you create the TSQLRestServerDB instance, check for the DB.UserVersion (before calling CreateMissingTables method of course, since it will update the internal user version). If this is not the correct version, you may update the trigger definition.

Offline

#26 2011-06-16 06:01:52

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

ok. i think i'll try this.
much to be learn about framework..

thanks..

Offline

#27 2011-06-16 06:14:52

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

Re: How to Implement Trigger and Stored Procedure

I've made TSQLDatabase.user_version now defined as a property, with a getter (not read/only any more).
It could be useful for you, to force a particular database internal version without the need of calling CreateMissingTables with a table change.

See http://synopse.info/fossil/info/4aa2672ad4

Offline

#28 2011-06-16 06:53:31

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

so, did you mean that i just need to change Trigger Definition and then Framework will automatically updated for me?
or i still need to check or call some method?

thanks.

Offline

#29 2011-06-16 06:58:21

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

Re: How to Implement Trigger and Stored Procedure

You still need to check the user_version value.
But since it will be set to 0 by default, you may put your code only once.
E.g. in a Create from a TSQLRestServerDB inheriting class:

if DB.user_version<1 then begin
  DB.Execute('DROP TRIGGER IF EXISTS ...');
  DB.Execute('CREATE TRIGGER...');
  DB.user_version := 1;
end;

Offline

#30 2011-06-16 09:31:46

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: How to Implement Trigger and Stored Procedure

ok, now i understand.

thank you.

Offline

Board footer

Powered by FluxBB