#101 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 11:13:15

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;

#102 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 10:42:39

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..

#103 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 10:06:39

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.

#105 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 09:22:59

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..

#106 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 03:38:23

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.

#107 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 03:12:44

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.

#108 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-15 03:02:56

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..

#109 Re: mORMot 1 » Problem with Currency properties » 2011-06-14 16:33:53

so, what sould to be corection? because it checked on the keypress event.

if IsValid(TempString,Temp) and (Temp>MaxValue) then begin
    Key := #0;
    Beep;
  end;

#110 Re: mORMot 1 » Problem with Currency properties » 2011-06-14 12:18:20

I try to trace the code of TSynLabeldEdit.
i think the problem is on MaxValue property, there set to 100 on constructor.

but i don't know how to set it dinamically depending on Kind:TSynLabeledEditKind property.

#111 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-14 09:01:44

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..

#112 Re: mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-14 06:12:31

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

#113 Re: mORMot 1 » Problem with Currency properties » 2011-06-13 12:45:23

hi..
I Created new form inherited from TRecordEditForm (part of the framework),
but with Currency Field Type, i can only type just one digit numerical value, but i can type "00000"...

what is the problem?

note:
i use the MainDemo from the framework..

#114 mORMot 1 » How to Implement Trigger and Stored Procedure » 2011-06-13 12:31:43

coblongpamor
Replies: 29

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

#115 Re: mORMot 1 » In my SQLite3 Framework TODO list » 2011-04-26 07:55:28

hallo AB, i have some request,
1. Can you complete TSQLTableToGrid, so i can add,edit,and delete record directly on the grid, is it possible?
2. Can you create TSynLookupComboBox component?
i imagine i can create the component like this:

TSQLCountries=class(TSQLRecord)
private
protected
public
  ID:RowUTF8;
  Code:RowUTF8;
  Name:RowUTF8;
end;

TSQLPeoples=class(TSQLRecord)
private
protected
public
  Country:TSQLCountry;
end;


var
  aCountries:TSQLCountries;
  aPeoples:TSQLPeoples;
  lcb:TSynLookupComboBox;
begin
  lcb:=TSynLookupComboBox.Create(aPeoples,Country,aCountries,'Code,Name');
  //i want lcb will automaticaly filled with aCountries Data (records).
  
end;

thanks

#116 Re: mORMot 1 » Error in last version :Synopse OpenSource-8455306490772f68 » 2011-03-28 23:54:51

In the main demo, when i mark all record, and then Click Delete, the grid still display All Record and add one row header after actual header.
Click Refresh does no efect.

Also Picture can not display as expected. i don't know where to fix it.

#117 mORMot 1 » Recursif Record Reference » 2011-03-23 12:46:12

coblongpamor
Replies: 1

How to implement a Recursif Record Reference in the same table?

is Record Class Defininition like bellow correct?

TSQLAccount= class(TSQLRecord)
private
  fAccountNumber: TRawUTF8;
  fAccountName: TRawUTF8;
  fAccountLevel: Integer;
  ...
  ...
  fHeaderAccount: TSQLAccount;
published
  ...
  HeaderAccount: TSQLAccount read fHeaderAccount write fHeaderAccount;
  AccountLevel: Integer read fAccountLevel write fAccountLevel;
  ...
end;

so i can do something like this

var
  aAccount:TSQLAccount;
  aHeaderAccount:TSQLAccount;
begin
  ...
  ...
  aHeaderAccount:=aAccount.HeaderAccount;
  aAccount.Level:=aHeaderAccount.Level+1;
  ...
  ...
end;

thanks.

#119 Re: mORMot 1 » "has many" and "has many through" relationships » 2011-03-23 05:53:22

ab wrote:

You've some new methods, perhaps the most powerful could be DestGetJoinTable(), which creates a TSQLTable, containing all specified Fields, after a JOIN associated to the current or specified Source ID. The JOIN extend is specified by an enumeration:

  /// the kind of fields to be available in a Table resulting of
  // a TSQLRecordMany.DestGetJoinedTable() method call
  // - Source fields are not available, because they will be always the same for
  // a same SourceID, and they should be available from the TSQLRecord which
  // hold the TSQLRecordMany instance
  TSQLRecordManyJoinKind = (
    jkDestID, jkPivotID, jkDestFields, jkPivotFields, jkPivotAndDestFields);

If i use this function bellow, i'll get all Dest Field.

DestGetJoinedTable(Client,'',AID,jkDestFields);

for example, i'll get ID,Field1,Field2,Field3,Field4,Field5,...

but how i can get only part of Dest Field (not all Dest field)
maybe if possible, i can write the code like

DestGetJoinedTable(Client,'',AID,jkDestFields,[Field1,Field2,Field3]);

or more simple parameter than [Field1,Field2,Field3]

Thanks AB.

#120 Re: mORMot 1 » Problem with data in a grid » 2011-03-23 03:05:11

Tanks AB for the response, i'll try the functions that you suggest.

#121 Re: mORMot 1 » Problem with data in a grid » 2011-03-22 00:31:34

Is it possible to Get Record from CurrentFocusedRow from TDrawGrid (TSQLTableToGrid)?

so i can write code like bellow (maybe)

procedure TForm1.DrawGrid1FocusedRow(Sender: TObject; aFocusedRow:TSQLRecord);
var
   aPeople:TSQLPeople;
begin
   aPeople:=aFocusedRow.
end;

and, if possible to Select multiple row.

var
  i:integer;
  aPeople:TSQLPeople;
begin
  for i:=0 to Drawgrid1.selectedrows.count-1 do
  begin
    Drawgrid1.selectedrows[i].focused;
    aPeople:=Drawgrid1.FocusedRow;
  end;
end;

thanks.

Board footer

Powered by FluxBB