Scripting

You may have already spotted that your application currently has a pretty major flaw. Right now it displays all the tasks in the Tasks table rather than just the tasks of the user who has logged in. To see this, restart your sample application and log in with a different user (Alex/111). The single task happens to belong to user Daniel, so the user Alex you have now logged in with should not be able to see it.

One solution to this problem would be to change the client to add a condition to only select data for the current user. (This could be done using DA SQL or Dynamic Where, which are not covered in this tutorial.) Even though this will work, it's really not good to leave something as important as data privacy to the client. Ideally, you’ll want to ensure there is no way the client can ever see someone else's data , even by accident. Fortunately, Data Abstract supports this by allowing you to apply filtering of data at the Relativity domain. With the filter applied in the Relativity Server, the client can still request the whole Tasks table, but will actually only receive a filtered set of data that belongs to them.

Auto-SQL vs. Custom-SQL

Open the Domain Schema in Schema Modeler (select the Schema in the Relativity Admin Tool and press the 'Open in Schema Modeler' button).

In the Schema on the left side of the main window, find the Tasks table, expand its Statements node and select the single statement contained in this node.

By default, each table in a schema is set to use an Auto-SQL statement. This means that the SELECT statement for the table will be generated by the Data Abstract library automatically, depending on the schema for the table. In this case, you’ll want to use a custom SQL expression for getting data from this table, so change the Statement Type to SQL. Schema Modeler will generate an initial custom SQL statement for the table for you.

NOTE: The default SQL has a {WHERE} macro. This serves as a placeholder for adding a Dynamic Where expression in the client. At runtime, this macro will be replaced with a translated DynamicWhere, or a TRUE expression (like 1 = 1) in case the request doesn't use the DynamicWhere feature. You need to make sure to leave this in the SQL.

You need to alter the SQL expression to add the condition to select records only for the currently logged-in user. Since “current user” is a rather dynamic thing, you are going to have to use a custom macro to represent the user identifier. Add the line AND "User" = {USER_ID} to the SQL.

SELECT
    "Id", "Done",
    "DueDate", "Priority", "Task",
    "Details", "User"
FROM
    "Tasks"
WHERE
    {WHERE}
AND "User" = {USER_ID}

NOTE: The {} syntax declares a macro. You can it anything you like, {USER_ID} is just a suggestion. The only requirement is that the macro name is unique within the schema.

While obtaining data for a given table, Data Abstract will try to prepare a final DB-specific SQL statement. It will try to resolve all macros and right now will fail as it will not recognize the {USER_ID} macro. This will cause it to trigger an onUnknownSqlMacroIdentifier event. You are now going to hook into that event and give it an implementation for the {USER_ID}} macro so that it no longer fails.

Enabling the Macro executution

Go back to the Relativity Admin Tool and open the Domain properties page.

In the Relativity Admin Tool, expand the Domains tree node and select the DATutorial node. The Domain properties pane will appear in the right pane. Enable the option Allow custom SQL macro processing and apply the changes made.

Implementing the Macro

Expand the SCHEMA tree node and select the Scripts entry. Using the Add Event button, add the onUnknownSqlMacroIdentifier script handler.

Implement the macro as follows:

// Called when an unknown macro is used from within an SQL statement.
function onUnknownSqlMacroIdentifier(name)
{
    if (name = 'USER_ID') {
        return session['Login.Id'];
    }
}

You now need to save the changed schema and return to your running application. Click the Load Data button again and the row belonging to Daniel will hopefully disappear (assuming you are still logged in as Alex). From now on the user should only see their own tasks.

What is going on?

So what happened here? If you cast your mind all the way back to the section of this tutorial on domain access, you will hopefully remember that you configured the data login for your domain to use the User table as the LoginProvider. After a user has successfully logged in, the session will get a bunch of properties with values from the Data table row for the current user. So if, for example, the table used in DbTableLoginProvider has the fields ID, Name, LoginName, GroupID and Active, the session will get the following entries after a successful login:

'Login.ID' = 2
'Login.Name' = 'Daniel Jackson'
'Login.LoginName' = 'Daniel'
'Login.GroupID' = '2'
'Login.Active' = '1'

The macro you have just written is able to access these session properties and is simply returning the Login.ID value. The value will get plugged into the SQL statement at the place the macro was used, which means the SQL statement used to return the Tasks table will now only return records whose User field match the Login.Id of the client making the request.