CampMaster Plug-in Guide

From DataSplice

The CampMaster Plug-in supports authoring database tables in DataSplice to track custom information that is not currently handled by an existing system. Unlike the default DataSplice views where a SQL statement is provided to describe the data from an existing database to be displayed, CampMaster views allow the administrator to define the fields that should be available in a view and automatically constructs a table in the database to store data.

Contents 1 Installation 2 Configuration 3 Creating Views 3.1 Fields 3.2 Relationships 3.3 Calculated Fields 3.3.1 Raw Calculated Fields 4 Managing Data 4.1 Database Updates

Installation

Installation of the plug-in is very simple, and is the same process as the other DataSplice plug-ins. Extract the contents of the CampMaster plug-in zip file into the root of the DataSplice installation. Upgrades work the same way; just unzip the contents of the distribution package and overwrite the old files. This will not affect any of the configuration files or other local settings.

Note that CampMaster only has server-side components. There is no need to install anything for the client applications accessing the system.

Configuration

The CampMaster Plug-in options are available through the Server Settings section of the Administration Client:

The two available settings are:

Connection Profile

Specifies the ADO.NET plug-in database profile to use for the tables managed by the plug-in.

CampMaster supports both Oracle and SQL Server backend databases.

The user account associated with the profile must have full privileges in the database because the plug-in must be able to create, alter, and drop tables and other schema.

Table Prefix

Each table managed by the plug-in will use this prefix in the table name. This is useful to distinguish CampMaster plug-in tables from other tables that are available, as well as to manage data from multiple instances in the same backend database (DEV_, TEST_, PROD_, etc.)

Creating Views

New CampMaster views are created by selecting the SimpleTableSource backend in the create view dialog:

This will create a table in the database named <Prefix><ViewName>.

Once the view has been created you'll notice that the Basic Options are the same as for other views, except the Connection Profile Name and Base SQL Select options are not available.

Fields

The Fields section supports defining the underlying schema of the data in the view.

Note that views are automatically created with a GUID field named ID that is used for the primary key. This allows for a consistent mechanism for identifying records and relating data together. The primary key behavior cannot be changed.

Each entry in the fields collection has the following settings:

• Field Name - Defines a unique name for the field. This is used as the basis for the column name in the table as well (after stripping illegal characters)
• Data Type - Controls the data type of the column in the database.
• Width - Specifies how many characters of data are available in Text fields. All text fields are created with VARCHAR types.
• FieldNotNull - Specifies required information in the table.
• UniqueKey - Enforces data uniqueness constraints. Only one unique constraint is created using the collection of fields with this property selected. Multiple unique constraints are not currently supported.
• Indexed - Creates an index on the field to improve query performance. One index is created per field that has this property selected. Composite indexes or additional advanced indexing options are not currently supported.

Relationships

The Relationships section describes how the records in multiple CampMaster views relates to each other. Relationships are defined in the target view of the relationship. For instance, with a parent/child relationship the relationship entry is defined in the child view, not the parent.

Each item in the list has the following settings:

• Relationship Name - Provides a unique name for the relationship. This will create a GUID field named <Relationship Name>_ID in the view to store the ID of the associated record.
• Related View - The name of the related view. Relationships can only be defined between CampMaster views.
• Relationship Type - Controls the behavior of the relationship.
  • Parent - Defines a parent/child relationship. The child record must be related to an associated parent record, and deleting the parent will cascade the delete through the child records.
  • Dependency - Similar to a parent relationship in that the associated record cannot be null, but does not cascade deletes.
  • Association - Allows the related record value to be null.

Calculated Fields

Calculated fields support having field data that is the result of a subquery or join into another table. In general, the same results can be achieved by creating virtual fields and using the appropriate expression as the field default. However, this mechanism can perform poorly because it results in many requests being sent to the database. Using Calculated Fields allows the subqueries to be processed as a single statement by the database.

The following settings are defined for each calculated field:

• Field Name - The unique name of the field. The data type and other details will be inferred from the details of the target calculation.
• Calculation Type - Specifies the type of calculation that is performed.
  • Lookup - Returns the value of a field in the target view.
  • Count/Average/Sum - Performs the specified aggregate function on the matching records in the target view.
  • Raw - Supports joining to tables that are not managed by CampMaster. See the notes below for more details.
• Target View - The name of the view to query. Other than for Raw calculations, this must be a CampMaster view.
• Target Field - The name of the field in the target view. This is not used with the Count type.
• Query Expression - Specifies a query substring that is used to relate the record to values in the target view. The syntax is the same as with the query expression functions, where the left-hand side values are fields in the target view and the right-hand side values refer to data in the current record.

Raw Calculated Fields

When the Raw type is specified it works slightly differently than the other calculated types that refer to views and fields maintained by the CampMaster Plug-in. Fields of this type are inserted in the SELECT SQL statement as follows:

( select <Target Field> from <Target View> where <Query Expression> ) <Field Name>

Because the names are not managed by the plug-in the amount of validation that is performed is fairly minimal. In general it will construct the SQL statement with the values specified and directly pass it to the database - any issues will need to be debugged by looking at the returned database error statement.

Note that the statement is performed using the ADO.NET connection profile specified in the plug-in options. This means the associated user must have access to the tables in question, and often an explicit schema or database name is needed in the Target View'.

Managing Data

After the basic schema for the view has been defined, the view configuration is the same as any other type of DataSplice view. The edit permissions, field settings, queries and relationships, and other settings should be defined to achieve the desired functionality. The View Administration section of the administration guide has extensive details about these configuration options.

The Insert, Update, and Delete Record events typically do not need to be overridden as when dealing with external database, save for validating user input. The default action in these events will automatically generate the appropriate SQL statement to modify the data, and will validate the uniqueness and null constraints defined in the field settings.

Database Updates

When the fields for a view are modified and the changes committed to the server, the plug-in will attempt to rebuild the underlying table. Depending on how the data is defined, certain modifications might fail, or if there is a very large amount of existing data the operation might take a long time to complete.

Any operation that deletes data (deleting a view or removing fields from it) will immediately process the change on the database, and there is no mechanism for undoing the changes. Database backup procedures are highly recommended so data can be restored if necessary.

Database updates are performed in an all or nothing fashion, where a temporary table with the new schema is created first, the data from the old table is copied to the new table, and finally the old version dropped and the new table is renamed. Any errors during the process will abort the update and leave the original table unmodified.

The most likely issue will be adding new fields that do not allow null values, which will fail if there are records in the table. This can be accomplished by addition the field without the null constraint, manually setting a default value for the existing records, and then selecting the Field Not Null setting.