Converting from Clarify Desktop to Dovetail Agent

July 12, 2010

This is the first installment of a new series of posts on migrating applications/enhancements from the Clarify desktop application to Dovetail Agent features. There are usually a few components involved – schema changes, customized forms, and custom code – that all need to be converted in some form or fashion.

For this series of posts, I will be migrating the Employee Manager Utility (EMU) from a Clarify desktop integration to a new feature for Dovetail Agent and Dovetail Admin. EMU is part of the Administration Series, and was last modified when Dovetail Software was still known as First Choice Software.

The first things to do is to examine the Employee Manager Utility product. EMU is an administration tool that allows an administrator to associate employee properties (such as queues, office location, supervisor, and resource configuration) to a workgroup. New employees that are assigned to that workgroup can automatically inherit all of the proper attributes. This will speed up and simplify the process of adding new employees to the Clarify database, since most of the attributes for an employee will be defined by the type of job they are destined to be performing. When an employee changes work assignments, the attributes of the new workgroup can override the current settings for the employee automatically just by changing the workgroup.

EMU is primarily a workgroup management utility used by administrators. The main form of the application will be used in Dovetail Admin, and there will be some modifications to the Employee page in Dovetail Agent. Converting the forms to pages will be the topics of future posts in this series. For this post, I will go through the process of converting a schema file from the Schema Editor format to a Dovetail SchemaEditor SchemaScript import file.

Moving from Schema Manager to Dovetail SchemaEditor SchemaScript

When EMU was shipped, a text file was provided that detailed the necessary changes that needed to be made to the Clarify database to support the new features. It was a partial schema file, and just included the changes necessary to update the Clarify schema.

One of the best features of Dovetail SchemaEditor is the ability to create and use SchemaScript files, which can be used to make stand-alone changes to the database schema without having to do any manual schema file exporting and editing. This process in the past was both time consuming and troublesome. Any incorrect edits made to a schema file and imported could cause schema issues that would require a database restore or manually corrections. Schema changes still have some inherent risk, but having a file that does not need to be integrated into an existing schema eliminates most of the chances for errors.

The schema changes required for EMU are not too complex. There are three custom tables, and those tables have a handful of relations to existing tables.

To start the process of converting the text file to SchemaScript, it is easier to start from an existing SchemaScript file. Dovetail Agent has one included in its installation files, so that one works well.

There are just three different components of SchemaScript that are needed in this current conversion effort. Stripping the Dovetail Agent SchemaScript down to the minimum components leaves this result:

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd"> <addTable name="x_web_cmd" id="4600"> <description>Commands available to web users.</description> <groupName>Web Agent</groupName> </addTable> <addColumn name="objid" dataType="Integer" table="x_web_cmd" > <description>Internal record number</description> <clarifyGenericFieldID>3</clarifyGenericFieldID> </addColumn> <addRelation name="ce_object2ce_query" type="OneToMany" table="ce_object" inverseRelationName="ce_query2ce_object" inverseTable="ce_query" > <description>Queries for this object</description> <inverseDescription>Object for this query</inverseDescription> </addRelation> </schemaScript>

Tables

From this starting point, creating the new SchemaScript file is an easy process. For each of the three new tables, copy and edit the AddTable node. Changes need to be made for the table name, table id, and description. The subject property is used as the group name. The fc_string table is specified like this in the schema text file:

OBJECT fc_string 3500 SUBJECT="System" COMMENT="Locale-based strings for First Choice Software Customizations"

Creating the addTable node in SchemaScript ends with an XML node like the following:

  <addTable name="fc_string" id="3500"> <groupName>System</groupName> </addTable>

Columns

For each column under each new table, copy and edit an AddColumn node. Again, each new node needs to have the column name, data type, table name and description specified. There are a few different data types that need to be used for EMU, and each one has some subtle differences in the SchemaScript details required.

String Fields

Most of the columns for EMU are for string columns in the tables. In the schema text file each one looks similar to this:

locale_name CMN_DATA_TYPE="char pointer" DB_DATA_TYPE=7 ARRAY_SIZE=20 COMMENT="name of the locale"

Converting this schema entry to SchemaScript results in this XML node:

<addColumn name="locale_name" dataType="String" table="fc_locale" > <length>20</length> </addColumn>

The column name is the same, the data type is now String, and the length attribute is set to 20. The table name is also specified as an attribute in SchemaScript, since the node are not nested under a parent table node.

Integer Fields

The objid and dev fields are both required fields for each table, and have a couple of different aspects compared to string columns. Here are both columns for the fc_locale table in schema text. Note the generic field id for each, and the fact that the dev column is nullable.

objid CMN_DATA_TYPE="long integer" DB_DATA_TYPE=0 MANDATORY PREDEFINED GEN_FIELD_ID=3 COMMENT="Internal record number" , dev CMN_DATA_TYPE="long integer" DB_DATA_TYPE=0 MANDATORY USER_DEFINED ALLOW_NULL GEN_FIELD_ID=151 DEFAULT="1" COMMENT="Row version number for mobile distribution purposes"

Each of these columns is easily added to the SchemaScript file. Each column has its generic field id specified as an attribute, and the dev column also gets an IsNullable attribute set to true. All of the other integer columns follow this same format except that the other columns do not have generic field id attributes.

<addColumn name="objid" dataType="Integer" table="fc_locale" > <description>Internal record number</description> <clarifyGenericFieldID>3</clarifyGenericFieldID> </addColumn> <addColumn name="dev" dataType="Integer" table="fc_locale" > <isNullable>true</isNullable> <description>Row version number for mobile distribution purposes</description> <clarifyGenericFieldID>151</clarifyGenericFieldID> </addColumn>

Date-Time Fields

The next data type used for EMU is a date/time field. Again, this type of column is easily translated from schema text to SchemaScript. The field name and table name need to be specified, and everything else is optional. Here is an example from the schema text file:

x_bizcal_start_date CMN_DATA_TYPE="clk_ty_time" DB_DATA_TYPE=9 OPTIONAL USER_DEFINED COMMENT="business calendar start date when defining new member in this workgroup" 

Converting this start date field to SchemaScript looks like this:

<addColumn name="x_bizcal_start_date" dataType="DateTime" table="workgroup" > <description>business calendar start date when defining new member in this workgroup</description> </addColumn>

Decimal Fields

The last data type in EMU is for a floating decimal field, which is used for a dollar amount. The labor rate column gets added to the workgroup table, and the schema text version looks like this:

x_labor_rate CMN_DATA_TYPE="float" DB_DATA_TYPE=3 OPTIONAL USER_DEFINED COMMENT="labor rate for new member of this workgroup"

Converting this float data type to SchemaScript is a little different than the other columns. The column name and table name are required, but also the detail floating decimal information is needed. For baseline Clarify floating fields, the decimal width and mantissa width are set to 19 and 4 respectively. This information in SchemaScript looks like this:

<addColumn name="x_labor_rate" dataType="Decimal" table="workgroup" > <description>labor rate for new member of this workgroup</description> <decimalWidth>19</decimalWidth> <mantissaWidth>4</mantissaWidth> </addColumn>

Relations

There are two relation types used in EMU, One-to-Many (OTM) relations and Many-to-Many (MTM) relations. SchemaScript simplifies how relations are specified compared the schema text files. Since SchemaEditor will create the relation information for both sides of the relation, the relation details only need to be specified once in SchemaScript as opposed to twice in schema text.

One-to-Many Relations

In EMU, there are six OTM relations, and they all relate from the new workgroup table to other existing tables. All relation definitions in SchemaScript look similar, with the relation type being the big differentiation between them. Here is one the the OTM relations in schema text:

empl2wgroup_super OTM workgroup USER_DEFINED INV_REL=wgroup_super2empl COMMENT="supervisor of Related workgroup"

The relation in SchemaScript has the same five key pieces of information – relation name, inverse relation name, base table, inverse relation table, and relation type. Here is the same relation in SchemaScript:

<addRelation name="empl2wgroup_super" type="OneToMany" table="workgroup" inverseRelationName="wgroup_super2empl" inverseTable="employee" > <description>supervisor of Related workgroup</description> </addRelation>

 

Summary

That covers the basics of converting from a schema text file to SchemaScript. For EMU, it is a matter of working through the 98 columns and 7 relations.

Once complete, it is easy to set up SchemaEditor to preview the changes that the SchemaScript file will make to the database, and then follow up and apply the changes as well.

For the next post in this series we will cover converting Clarify Forms into Dovetail Agent/Admin pages.