How to expose a property as a database column

Use the Modify a Database Schema wizard to:

• Review the current schema of a database table.
• Change the width in bytes of a column in the database table.
• Expose a Single Value property as a new column
• Create a text file with Structured Query Language defining a table.

Select Tools > Database > Modify Database Schema to start the wizard. Click the help button () on each form of the wizard for additional details on completing the form.

You can use this facility to copy a Single Value property from the BLOB column and "expose" it as a column. This change may affect database performance and database size, but has no functional effect on activities, the property rule, or other rules referencing the property.

To modify a table schema with this facility, work at a time when no one else is using the table. Obtain a database user ID and password that allow you to modify the database schema, not just the rows in the database. A database administrator can supply these credentials. (If your account does not provide the ability to modify the schema, you can generate the Data Description Language (DDL) statements that are required to modify the schema.)

When working offline, you can review the contents of the database schema as initially installed. Locate and open the schema HTM file in the Resource Kit directory of the Process Commander installation media.

1. Select Tools > Database > Modify Database Schema.
2. A list of databases identified in Database data instances appears. Select a database and click  Next  .
3. A list of tables in the selected database appears from Database Table instances. Select a table.
4. Click  Explore Columns  .
5. The resulting List of Classes window displays the number of rows in the table, the number of columns in the table and a list of the classes assigned to that table. The Properties Set to Be Visible value counts the properties for which the Column Inclusion value is Required or Recommended. This Column Inclusion value is advisory, and does not indicate whether the property is exposed — corresponds to a column. The Count column shows the total count of properties in this class plus those its parent classes.
6. To see the columns currently defined in this table, click the numeric link labeled Number of columns in this table.
7. The List of Database Columns window shows the column name, column data type, and column width in bytes for each column in the table.

1. Access the List of Classes window for the table containing the property of interest, following steps 1 to 5 above.
2. Locate the class that contains the scalar property to be exposed.
3. Click the number in the Set to be Visible row.
4. The resulting detail window displays the properties already exposed as columns in gray text at the bottom of the display. Properties that are candidates to be exposed are shown at the top of the display in black text.
5. Check the box for each property to become exposed as a column. For properties with a Type of Text, Identifier, or Password, you can modify the column width shown in the Column/Size column. The default width is 64 characters, or the Max Length of the property (if that was defined for the property). If you change these values, remember to update later the Max Length values if defined on the Property form.
6. To complete the size changes or create the new column or columns now, enter a Database UserID and password that grants the ability to modify the schema of this database. Select the Generate Database Columns option and click  Go  .
7. If you don't have an appropriate password, or want to review the revised Structured Query Language definition for the table, select the Generate SQL Code option and click  Go  .
8. After the column is defined, use the Column Populator tool (part of the PRDBUTIL servlet) to copy values from the BLOB column into the new column.

Only top-level Single Value properties can be exposed as columns. Two tactics are available when you need a database column that contains the value of an embedded property:

Copy approach — Copy the value to a new top-level property each time the embedded property changes (or each time the instance containing the property is saved). For example, if a list view report needs to select rows based on property pyWorkPage.pxFlow("LoanDisburse").Sheet.Detail(4), you can:

1. Create a new top-level Single Value property to hold a copy of this value.
2. Create a one-step activity to copy the value to the new top-level property, with Activity Type set to Trigger.
3. Create a Declare Trigger rule that calls the activity each time the work object is saved
4. Expose the top-level property.
5. Reference the top-level property in the list view rule.

Declare Index approach — If not one but many or all values of a Value List or Value Group are needed as exposed columns, a Declare Index rule is a better approach.

1. Create a concrete class derived from the Index- base class.
2. Create Single Value properties in the new class to hold values of the embedded values.
3. Create a Declare Index rule with the appropriate embedded Page Context value that copies the embedded values into a new Index- instance.
4. Save the Declare Index rule. It executes immediately, adding and deleting instances of the new class.
5. Expose database columns corresponding to the Index- class.
6. Reference the Index- properties in the list view rule.

Don't confuse an exposed property with an indexed database column. For example, the property Work-.pyID is an exposed property in the table pc_work. However, if this table contains millions of rows, one for each work object, a list view search for .pyID of "W-135" causes an expensive database table scan. Creating database indexes or Process Commander indexes can speed database operations in such situations.

Exposing too many properties in a table may speed reporting and searching operations, but make insert and update operations slower. The tradeoff and relative impact depends on hardware and software and no general guidelines can be stated.