Web Screen Painter schema modification - How it works and what to check if it goes wrong.

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

You can use Web Screen Painter (WSP) Schema Designer to modify the flexible database schema of ServiceDesk to meet your needs. The Schema Designer provides an easy-to-use graphical user interface to review and modify the ServiceDesk schema. WSP also allows you to test your schema changes on your own web forms before updating the physical DBMS schema or affecting other users.

Environment

CA Service Desk Manager 17.X.

Resolution

When you use WSP to make schema changes, it's a three step process:

Make the changes in WSP and save them.
Publish them from the WSP interface.
Run the "pdm_publish" command.

(Once you have executed step 2, publishing from WSP, you must then run step 3, pdm_publish, before you can make and save any more schema changes. If you don't do this, you will not have the ability to save any more schema changes in WSP).

This document details what happens at each step of this process, and should help you to troubleshoot any WSP problems related to schema changes.

Make the changes in WSP and save them.

When you make these changes, and save them in WSP, the information is written to the WSPCOL and WSPTBL database tables in the MDB. WSPCOL contains details of the custom columns (name, table it was added to, all attributes such as whether it's required, the data type etc.) and WSPTBL contains details of the custom tables.
Publish them from the WSP interface.

When you select "Save and Publish" in WSP, four files are created/modified in the $NX_ROOT\site\mods directory:
- wsp.altertbl - contain a list of the tables which have been added.
- wsp.altercol - contains a list of columns which have been added, and the name of the table to which they were added.
- wsp_index.sch - contains the index information for the new columns and tables.
- wsp_schema.sch - contain the database schema definition for the new tables and columns.
  
  and one file in the $NX_ROOT\site\mods\majic directory
- wsp.mods - contains the object definitions for the new tables and columns.
Run the "pdm_publish" command.

When you run pdm_publish at the command prompt, it looks for the wsp.altercol and wsp.altertbl files. If these don't exist, pdm_publish will not run, as it knows that there's nothing to publish. If these files exist, pdm_publish reads these files and this tells the process which tables and columns it needs to add. It reads the WSP schema files, adds the appropriate fields to the mdb database, updates the ddict.sch file so that it contains the newly created schema and refreshes the Service Desk object manager cache so that these new columns and tables are available for immediate use. The final step is for the process to delete the wsp.altertbl and wsp.altercol files, so that the next time pdm_publish is run, it doesn't try to re-add the same database tables and columns.

Problems publishing schema changes are usually due to inconsistencies between the existing database schema, and the information contained in these files. A common cause for this problem is when the MDB database is copied from one system to another. If this is done, you should first of all make sure that there are no un-published WSP changes on the source system and as well as copying the MDB database, you should also copy these associated WSP files:
- $NX_ROOT\site\mods\wsp_index.sch
- $NX_ROOT\site\mods\wsp_schema.sch
- $NX_ROOT\site\mods\majic\wsp.mods
Once you've copied the MDB and these files, you should then run pdm_configure to ensure that the ddict.sch file is updates with the appropriate information from the WSP schema definition files.

If you're having problems on a single system (the database hasn't been copied from another system), you need to check the consistency of the files and database tables noted in this document. You need to make sure that the entries in the wsptbl and wspcol database tables have entries in the wsp_schema.sch, wsp_index.sch (if applicable), wsp.mods and ddict.sch files, and you also need to check that these database tables and columns exist in the MDB. Any mismatch of these files/database entries can cause problems in the future.

If all existing changes have been published using pdm_publish, the wsp.altertbl and wsp.altercol files should not exist. If something went wrong during the pdm_publish command and these files remain after successfully running pdm_publish, delete these files. Failing to do this means that when you try to make further changes in WSP, you will get a message saying that you won't be able to save any changes.

Additional Information

Custom schema modifications made outside of WSP/Schema Designer aren't supported