Custom SQL Macros

Anton Kasyanov -

In this blogpost I’ll try to show you a not-so-well-known or widely used Data Abstract feature in action – the custom SQL macros. This feature allows to use your own SQL Macros, defined via server-side code or Business Rules Scripting, or even without any code at all.

I’ll use a real-world problem as an illustration, but remember – this is just a sample. There might be much more possible scenarios where this feature could be useful (for example row-level security where returned data should be filtered based on some logged user properties).

The problem

Imagine an order processing application where remote clients connect several times per day and upload their orders to the main order processing system.

One day it has been decided that some logging should be introduced to save the name of the last client to have inserted or updated the order (let’s not discuss why this wasn’t done from the beginning – remember, this is only a sample).

Also, only unprocessed orders uploaded by the user should be shown.

The most obvious solution is to add an additional field to the table and use Dynamic Where expressions to filter the data, but sometimes this approach is not possible. For example:

  1. We cannot seamlessly update several hundreds of deployed client applications (for many reasons, varying from lack of rights to install any new software on remote clients workstations to the huge amount of time this would need, etc.).
  2. Sometimes we cannot alter the server code (i.e. when a Relativity Domain is used as the Data Source).

Following, we’ll create a sample application that will use custom SQL macros to modify the data inserted into the underlying database.

The sample application

We’ll use a very simple sample application. This application was created using the New Project Wizard and uses the PCTrade SQLite database as a data source.

Let’s add a GridView and two buttons with a very straightforward OnClick event handlers code:

privatevoid LoadDataButton_Click(object sender, EventArgs e){this.OrdersDataset.Clear();this.fDataModule.DataAdapter.Fill(this.OrdersDataset);}
privatevoid UpdateDataButton_Click(object sender, EventArgs e){this.fDataModule.DataAdapter.Update(this.OrdersDataset);}
As you can see, this client app loads data about all orders registered in the database:

Also, neither the client application nor the server store information about the user updating or inserting data rows in the data table.

The Solution

Let’s add a string field User to the Orders database table (but not to the Schema):

CREATETABLE Orders ( Id INTEGERPRIMARYKEY AUTOINCREMENT NOTNULL, OrderDate DATETIME NOTNULLDEFAULT(DATE()), OrderStatus INTEGERNOTNULL, CustomerId VARCHAR(38)NOTNULLREFERENCES Customers ( Id ),USERVARCHAR(50));
Now we need to hook up the Insert operation on this table. We’ll use another great Data Abstract feature – **BusinessProcessor-less update commands**. This feature allows you to define custom **Insert**, **Update** and **Delete** commands for Schema Data Tables in a declarative way, without creating custom Business Processors in the Data Service.

Open the Schema Modeler, right-click the Orders table definition and select the Create Delta-Commands for Data Table menu item.

Now hook the newly created Insert_Orders command to the Orders datatable:

In this blogpost, we’ll only modify the Insert_Orders command, however, you might consider altering other commands in the same way later.

The statement for this command looks like this (remember that we created the User field only in the database and haven’t registered it in the Schema):

INSERTINTO"Orders"("OrderDate","OrderStatus","CustomerId")VALUES(:OrderDate, :OrderStatus, :CustomerId)
Change it to
INSERTINTO"Orders"("OrderDate","OrderStatus","CustomerId","User")VALUES(:OrderDate, :OrderStatus, :CustomerId,{DB_USER})
Do you see the **{DB_USER}** identifier? That is the **Custom SQL Macro**. The following part of this post explains how it can be used.

Solution A: The event handler

DataService exposes the UnknownSqlMacroIdentifier event. Let’s create an event handler that will handle the {DB_USER} macro:

privatevoid DataService_UnknownSqlMacroIdentifier(object sender, UnknownSqlMacroIdentifierEventArgs e){if(e.Identifier=="DB_USER"){ e.Value=this.Connection.DatabaseProfile.QuoteIdentifier((string)Session["UserID"]); e.Processed=true;}}
Here, the Macro name is checked and if it is known, the log-on user name is taken from the session and the **Processed** flag is set.

Note that the value of the macro will be inserted AS IS so we HAVE to quote it to avoid SQL injection attacks. It is recommended to use somthing like internal user identifiers here instead of just plain user names.

Now let’s try to add a row to the Orders table and see what happens (note that not a single line of code was changed in the client application):

At first glance nothing was changed. But let’s examine the underlying data table:

And this is it! The log-on user name has been saved in the data table, without any changes in the client application.

Solution B: The script

Sometimes it isn’t possible to change the server code. For example, you can’t add any code to the Relativity server. In this case Business Rules Script events need to be used.

In this post I’ll use the same server app to skip deploying of the sample Relativity Domain and recreating the client application.

Let’s remove the DataService_UnknownSqlMacroIdentifier event handler created previously and open the server Schema in the Schema Modeler.

Open the Schema Business Rules pane and define an event handler for the onUnknownSqlMacroIdentifier event:

function onUnknownSqlMacroIdentifier(identifier){if(identifier =='DB_USER')return'"'+ session['UserID']+'"';}
As you can see, it does exactly the same as the previously defined C# UnknownSqlMacroIdentifier event handler.

Add a row and everything works again!

Solution C. The SESSION_ keyword

While previous soultions allow to calculate Custom Macro values, there is a much simpler approach where the prepared value is already stored in the user’s Session.

Remove the Business Rules Script event handler and change the ‘Insert_Orders’ command statement:

INSERTINTO"Orders"("OrderDate","OrderStatus","CustomerId","User")VALUES(:OrderDate, :OrderStatus, :CustomerId,{SESSION_UserID})
The session value stored with the key “UserID” will be stored in the database:

The main disadvantage of this approach is that for string values, you have to store the already quoted string in the session.

Missing sample features

I’ll skip modifying the Update and Delete commands, as well as introducing the row-level security (i.e. filtering the Orders dataset), however, Schema changes needed to add filtering of the Orders dataset are very similar to the ones described below. Just do the following:

  1. Change the Statement type of the Orders table from stAutoSql to stSQL
    .

  2. Add the needed conditions to the WHERE clause of the Statement SQL expression
    .

Summary

This blogpost illustrates 3 different ways to use custom SQL macros. This feature can be very useful when you need to fine-tune data being inserted into the database, apply the row-security approach and in all other cases where the Schema entitiy statement has to be fine-tuned based on user data and business rules./strong