How to use events to personalize SQLEdit engine.

Starting from version 06.00.10, you have a powerful tool to alter SQLEdit standard flow: the events. Events are hooks that, placed in some strategical point of flow code, give you the ability to change internal SQLEdit parameters to alter normal operation.

To give you an example, we create a simple table and SQLEdit element to manage user bank balance.

Let's start creating table with this SQL code:

CREATE TABLE bank_account (

  username varchar(100) NOT NULL default '',

  deposit decimal(10,2) NOT NULL default '0.00',

  balance decimal(10,2) NOT NULL default '0.00',

  lastMod datetime NOT NULL default '0000-00-00 00:00:00',

  lastModUser varchar(100) NOT NULL default '',

  PRIMARY KEY (username)

)

and add an SQLEdit to a page, by binding it to this table.

Normally, user that can access this page can add, delete and freely edit records. But this is not good for a bank account. We wish that normal users can view and edit only their own account, and (of course) cannot edit balance in no case. Only admin can freely do whatever he wants. These ties cannot be imposed in SQLEdit before 06.00.10.

We added at this table two fields, "lastMod" and "lastModUser", that shouldn't be visible and have to be updated when someone changes record so, to begin we change lastMod and lastModUser to be only visible (read-only). In real world we could make them hidden.

Let's look some examples of what we can do with events

Change user request of records

The onLoad event is a good place to impose that normal user can edit only his own record. Add this PERL code to this event:

# Not Admins will be redirect to edit own record
my $sform = $_[0]->{session_form};
if ('^@;' ne 'Admin') {
    $sform->{exec} = 'edit';
    $sform->{username} = '^@;';
}

so that, whichever record the user tries to edit or add, it will be redirect to his own record.

We analyze what this code means. ....

Change field configuration

The onBeforeEdit is a good place to impose that normal user cannot modify some "important" values:

# users can't change username and balance
my $fs = $_[0]->{fld_pers};
if ('^@;' ne 'Admin') {
    $fs->{username}->{status} = 'visible';
    $fs->{balance}->{status} = 'visible';
}

Update some fields before saving

During onBeforeSave we can set lastMod and lastModUser with current date and with username of whom is changing record

# update lastMod and lastModUser value
my $fv = $_[0]->{fld_values};
$fv->{lastMod} = '^D(m-%d %J:%n:%s);';
$fv->{lastModUser} = '^@;';

Have some computed values

OnBeforeSave is also a point where update computed values. Before save we need to increment balance with current deposit. This can be done with this code:

# calculate new balance
$fv->{balance} += $fv->{deposit};

Impose some restriction for saving and deleting operations

Of course we don't want users can delete records so, during onBeforeCancel, we abort this operation.

# normal users can't delete records
if ('^@;' ne 'Admin') {
    $_[0]->{cancel} = '^International(38);';
}

Of course we should also change SQLEdit global template to remove delete link for normal users but this is beyond the scope of this example.