Table editor

A Table, or Data Table, in a schema represents an abstract, database independent, structure which corresponds to a database table.

The Table exposes various properties and contains collections of fields, parameters and statements. A Field could be a direct match to a field in a database, or a calculated field based on multiple other fields. A Statement contains a reference to certain database connections and provides a mapping between the underlying database table (view or stored procedure) and the schema table. Parameters can use used to filter the records retrieved from a data table for certain conditions, or to simply provide parameters that certain operations or stored procedures require.

Tables can be created in one of a couple of ways; either by manually creating a table and adding the fields one by one or by using drag and drop from the Connection Manager.

This document will describe how to create or edit a table, and describe the properties you can set on that table.

This top level page is the Table section which is visible when you select the TABLES folder in the navigation tree view. It gives you some basic information about the tables that have already been defined for the Schema. At a quick glance you can see the names of the tables, a description if its been supplied, whether the tables are visible to all and whether the tables are read-only.

Creating a Table by Drag & Drop

The most efficient method to creating and populating a table in the Schema Modeler is to drag a Table, View or Stored Procedure from the Connection Manager window into the navigation tree view in the left pane.

A new Schema table will then be created and properly configured for you. Each table will have a list of fields which match those in the database, views will be added as appropriate and the statement will be configured to the appropriate connection.

Once everything has been imported, you can then apply any additional configuration you require to the Table. See the Editing the Table properties section below for more information on that.

Create a Table manually

If you didn't create a table through the wizard or by using drag & drop, then you can either:

• Quickly add a new table by right clicking on "Tables" from the navigation tree view in the left pane and selecting Add.
• Click on "Add New Table" which is available in the right hand pane when you click on the Tables folder in the navigation tree view.

Either of which opens a table editor where you can set the basic information about the table.

Editing the Table properties

Once a table has been created, or if you select a table from the Tables folder in the navigation tree view you will be taken to the Table View where you can edit the basic properties of the table and get a quick overview of the fields, statements and parameters that it contains.

The page is broken down into three sections:

• General - Which covers the basic details about the table like its unique name, an optional description, whether its publicly available to client applications and whether the table is supposed to be read-only.
• Data - Which allows you to specify what database command to execute when Insert, Update or Delete delta requests are received. You can also specify a Dynamic Where XML statement.
• Misc - Here you can specify custom attributes that can be used for any purpose you desire. You supply a string that can then be read by the client, allowing you to for instance restrict access to the table based on user roles.

Working with Fields

The Fields view, visible when you click on the Fields folder in the navigation tree view, provides an overview of the fields that are currently available in the selected Table.

Not only can you see the type of each field based on its icon and the handy key, you can also see its name, DataType including size, whether changes to it are logged and its optional description.

In this Field View you can also add, remove and update fields, update their ordering or if you have messed up you can recreate the fields again based on the underlying database table.

If you open the Fields folder and select a field, you can see and edit the details of that field.

This view is split into three sections allowing you to customise the field to your needs:

• General - You can change the field name here, and supply an optional description of what the field is used for.
• Data - Here you can manage the main details of the field. You can specify if its a primary key, a Calculated field, its data type and much more.
• Presentation - allows you to supply information about how the field would be presented in a table view. You can specify a separate label to use instead of the field's name, its alignment, whether it should be visible (for instance in a client app you wouldn't normally display the id field) and so on.

Working with Parameters

Parameters can be used to filter the records retrieved from a data table based on particular conditions, to provider parameters that an operation requires, or to pass additional data to insert/update/delete statements.

When you click on the Parameters folder from the navigation tree view you can see the main Parameters view, which provides an overview of any parameters that have already been created; including their name, data type, direction and optional description.

From here you can quickly add a new Parameter, remove a selected parameter or recreate any parameters from the underlying database.

If you add a new parameter, or select a parameter in the Parameters folder you will be taken to the Parameters View where you can edit the details.

The view is broken into two sections:

• General which allows you to edit the unique name of the parameter, and provide an optional description.
• Data allows you to set/tweak the details about the parameter, including its data type, whether its used as an input/output/both parameter, its value and so on.

Working with Statements

A Statement defines the mapping between the Schema Table and the underlying database Table. It can either based on a SQL statement, a stored procedure or dynamically generated using AutoSQL.

When you select the Statements folder in the navigation tree view view, you get an overview of the Statements that have already been defined. You can quickly see its name, its type which is usually the database type its connected to, the target table, and whether it is the default statement.

You can quickly add or remove statements by pressing the appropriate button.

If you add a new Statement, or select a statement from the Statements folder in the navigation tree view you will be taken to the Statement View where you can edit the statement's details. Note that the drop down selector on the right side of the textfields provides a handy shortcut to specifying a value.

The first field, Connection is the connection name. It should be set to the name of the Connection in the Connection Manager you wish to use. If left blank, then the connection name will be set to the value specified for Connection Type of the default Connection.

The Connection Type field will update to match the Connection Type specified for the selected Connection. You would normally not need to edit this.

The Target Table allows you to choose which of underlying database tables this statement is connected to.

The Statement Type allows you to specify whether the schema should use AutoSQL, Custom SQL (which it can generate for you) or a Stored Procedure. Note that the area below this field will change depending on your selection.

You will then be able to view the sql, the mappings between schema fields and the database fields, and preview the data returned based on the SQL statement.

Working with Scripts

Here you can specify Business Logic, or Business Rules that are written in JavaScript and executed when certain specified events occur. For instance when adding a new row, or before getting data or processing a delta update.

To add a new script, or see the scripts already set for the table, select the Scripts view from the navigation tree view.

Here you can edit the rules for both the server, and for certain client/server operations. Each script hangs off an event that you can add by pressing the Add Event button. You then add your code directly into the syntax aware editor in the section marked // Add your code here. When you are ready press the "Validate" button and Server Explorer will perform some basic validation of your JavaScript code and report any errors.

You can find more information about the events able to Business Rules in the Business Rules API section.