Commands

A Schema Command represents an action your Data Abstract application wants to execute on the database to perform data manipulations, like a stored procedure or Inserting/Updating/Deleting records or some other action using a custom SQL statement.

Each Schema Command has a collection of parameters and statements. A Statement contains a reference to a database connection, the data table it affect and either a custom SQL statement or stored procedure. A Parameter can be used to filter the records retrieved from a data table for certain conditions, to pass new data to insert/update/delete statements, return data to the client application or to simply provide parameters that a certain operation or stored procedure requires.

The Schema Modeler provides several options to create new Commands; for instance adding them manually or using drag & drop.

Creating a Command using Drag & Drop

A quick way to create new Commands, is to drag a Stored Procedure from the Connection Manager window and drop it into the Commands folder in the navigation tree view.

This will generate a new Schema Command that contains a Statement which references the appropriate Connection from the Connection Manager and the Stored Procedure.

Adding or Editing a Command

Another way to create a new schema command is to do so manually. To add a new Schema Command you can either:

Right Click on the Commands folder in the navigation tree view and select the "Add new Command" button.
Click on the "Add New Command" button on the Commands collection view.

After you have selected one of those options you are switched to the "Schema Command" view where you can configure the command's basic properties.

Command Name a unique name, the recommendation is to combine the the table name and the action.
Public checkbox to indicate in the command is available publicly.
Description an optional field to describe the command
Custom Attributes allows you to specify an optional string as a Custom Attribute(s) that can be used as part of the Business Rules scripting

To complete the command you need to add any required Parameters and a Statement that will be executed.

When you have made modifications to the Command you will see a black dot in the Red window control which will indicate this. Make sure to save the changes you have made by clicking on File->Save or Cmd+S. You can also discard the changes you've made if you are unhappy with them.

Adding or Editing a Parameter

Having added the command, the next step is add the parameter(s) you will need to execute the command. For instance the "Clients_DELETE" command has a single parameter (OLD_ClientId) which represents the id of the object to be deleted from the Clients table.

To add a new parameter expand the command in the tree view and click on the Parameters folder. This Schema Parameters page gives an overview of any parameters that are currently defined, including their name, data type and whether its an "Input", "Output" or "Result" parameter.

You can then either click the "Add New Parameter" button, or right click on the Parameters folder which will then open the "Schema Parameter" view.

This view allows you to define the details of the parameter:

Parameter Name is the name of the parameter and will be referenced in the client application and/or in the command's Statement.
Description an optional field to describe the parameter
Parameter Type is a selection box where you can choose what type of parameter it is. Options include "Input", "Output", "Input/Output", "Result".
Data Type this selection box allows you to specify what the data type is from the many that Data Abstract supports.
Size allows you to specify the parameters size, typically this is for a string based data type. Otherwise its size should be 0.
Value is an optional field for a default value.

Adding or Editing a Statement

The last part of a Command, is the Statement which contains the code that will be executed when the command is called from a client application.

Selecting the Statements folder in the tree view which will open the Schema Statements view. Here you can see an overview of any existing statements, and the table they are targeting.

To add a new statement either right click on the Statements folder and click on the "Add New Statement" button, or click on the "Add New Statement" button on the Schema Statements view. To edit an existing statement expand the Statements folder and click on the statement to open the statement editor view.

On this view you define the important details about the statement, specifically the Connection it uses, the Target Table that it affects and the Statement Type. By default a new statement uses AutoSQL, you will need to choose the Target Table from those available (based on the Connection you choose).

Connection a selection box to specify which connection is being used.
Connection Type is an optional field to describe how the connection is made
Target Table here you can enter the name of the table that this statement will affects. Note that this is the name of the table in the Connection, not the name in the schema.
Statement Type allows you to specify the statement type to use. Typically you would use "Custom SQL" unless you are executing a stored procedure.

The afore mentioned Clients_DELETE command uses a custom SQL statement that uses the OLD_ClientId parameter to specify which record should be deleted from the Agents table (in the schema this is the Clients table).

DELETE FROM
    "Agents"
WHERE
    "Id" = :OLD_ClientId

Adding or Editing Scripts

It is possible to add server side scripting using Business Rules Scripting which can be triggered at certain times. There are three events available here, all of which are optional:

onValidateCommandAccess which is called before the command is executed to verify that the currently logged in user has permission to do so. Method should return true is the user may execute the command.
beforeExecuteCommand which is called before an SQL command is executed.
afterExecuteCommand which is called after an SQL command has been executed.