FESA 3 Database integration intor GSI environment

This page will describe the integration of the FESA3 Database into the GSI environment. It shortly introduces the modifications on the database which were done to run it on the GSI side.

Introduction

The FESA 3 database was deployed at GSI in March-April 2014. The main purpose of this deployment was the integration of the database in the current FESA class delivery workflow to allow the LSA framework to access the class data. Additionally the new database should replace the old FESA2 DB used mostly by the CMW directory server.

However due of different constrains and references to non-existing data, the database was not ready to operation directly and required some additional modification and adjustments. However the deployed version of the database itself was not the newest one at the time of the deployment. Hence of the adjustments described here may not be required with the newer version of the database and should be considered rather as documentation of work done then guidance for future FESA3 database integrations.

In following, a short description of the database functions and workflow.

The FESA 3 database stores all data considering the developed FESA classes, such as internal fields, interfaces, configuration, timing and deployment parameters and servers on which the particular devices are deployed. To connect to the database and write the information the FESA Eclipse plug in uses a provided external interface configdb-fesa-client. Via this interface the plug-in connects to the DB and transmits the class information. The information about a particular class is divided in 3 different data parts. Those are:

• Class design (.design files)
• Deployment unit information (.deploy files)
• Device instance information (.instance files)

Each part is transmitted separately during the according development process. Each of the data parts is encoded as a XML files with the proper ending. The FESA plug in does not transmit the basic class data, but rather sends the complete xml by calling of particular PL/SQL procedure on the DB side with the content of xml as string parameter. The extraction of data is then done on the DB side. Therefore the database uses the XLST scripts, which are also stored in the db. In short the procedure does perform following steps:

1. Getting the proper XLSTs from the DB using the FESA version from the file
2. Applying of the XSLTs on the received XML files and extracting the proper data rows
3. Writing of extracted data rows in temporary tables
4. Writing the data from the temporary tables into "normal" ones (different internal constrains and check are performed here)

XSLT adjustments

XSLT scripts are stored in the table FESA_TRANSFER_METAMODELS. Directly after the deployment of the database the XSLT scripts did not worked properly resulting in an oracle exception. For some unknown reason the content of the scripts was stored using a mixed upper and lower case. Moreover the case itself could vary within of single scripts. To solve this issue all XSLT scripts were adjusted to consequently use the lower case. This had to be done half manually, since some variable and column names should stay in lower case.

Additionally 2 of the XSLT script required some modification. They were extended to extract the GSI specific fields and properties. Those scripts are:

• Insertion Fields
• Insertion Properties

Note that only the insertion scripts where adjusted, while the extraction script where left in the original state. Most probably the extraction will not work property, but this function is currently not used by any department at GSI.

Adjustments of data in the tables

Some of the insertion operation steps rely on the existence of particular data row. This data is not added during the import of the FESA files and hence should be inserted manually or by some script. Mostly this data is checked by different internal constraints during the transport from temp to normal tables. Following was added:

• FESA_IO_ITEMS_ROLES: added a new role 'NONE'. This is a default role, which the insertion function is using.
• FESA_PROPERTY_TYPES: changed the ID column of the NOT_USE type to '3'. The value comes from investigating of the function code, where the value is simply hard coded
• RESPONSIBLE_UTINS: added a new row "Undefined", which is used if no responsible unit is defined in the FESA files

View adjustments

• CLASSVERSIONS_V: changed the join parameter of the LOV_FESA_FRAMEWORKS from version id to release id, otherwise no proper data was found and hence the view was empty.

Constrains adjustments

• Table DEVICES: disabled constraints DEVICES_COMPUTER_FK and DEVICES_COMPUTER_ID_FK. Those constrains are checking the existence of the computer on which a device should be deployed. However, there is not intent at GSI to keep the information about the computer inside of this database
• Table INSTANTIATION_UNITS: disabled the constraint FIU_SERVER_UK and additionally removed the unique index FIU_SERVER_UK. Otherwise no devices with the same server are allowed to deploy

Integration with FESA development environment

The integration of the FESA eclipse plugin with the database is based on two java components:

• configdb-fesa-client
• configdb-fesa-server

The server component is used to connect to the database itself and provides a spring-RMI interface to access methods to write the data into the database. On the CERN side multiple servers are deployed to use in different development steps (DEV, TEST, PROD). The servers encapsulating the database connections and hiding the DB configuration. On the other hand the client component provides an interface for the client (it this case the FESA plugin) and uses a Spring-RMI call to call the server. In particular the server and the client share the same interface, so the FESA plugin can also use the server code directly. This however, exposes the database configuration to the client. Additionally the database server needs to be reachable from the eclipse plug in side.

Currently at GSI the direct connection to the database is used, which means that the server class is instantiated in the plugin configuration and the plugin connects to the database directly. This solution is considered as temporarily and should be replaced by remote server solution similar to CERN.

DirectoryServer

The CMW Directory Server is connecting the name of devices with the location of the servers on which they are deployed. Among other information the FESA plugin also provides the information about the server names on which the particular devices are running. The other part of the information (server name -> server location) is written by the Directory server itself. Therefore it requires additional table for Directory server to store the information. The table is called SERVERS_DIRECTORY. This table also comes with triggers SERVERS_DIRECTORY_ID_CHECK and SDIR_BEFORE_UPDATE_FER as well as a sequence SERVERS_DIRECTORY_ID_SEQ. Those components can be found in the CERN repository and used directly from there.

Also the Directory server relies on the view CMW_DEVICES, which in the current GSI database is constructed as following:

CREATE OR REPLACE FORCE VIEW "FESA30"."CMW_DEVICES" ("DEVICENAME", "SERVERNAME", "ACCELERATOR", "FECNAME", "SUBSYSTEM", "CLASSNAME", "ALARMS", "SL_FAMILY", "PROXYNAME") AS 
  SELECT d.devicename ,d.servername  ,d.accelerator
      ,d.fecname    ,d.subsystem   ,d.classname
      ,d.alarms     ,d.sl_family   ,null
FROM devices_v d
UNION ALL
SELECT rm.devicename, rm.servername, rm.accelerator, 
       rm.fecname, rm.subsystem, rm.classname, 
       rm.alarms, rm.sl_family, rm.proxyname 
FROM cmw_devices@FESA20T.ACC.GSI.DE rm WHERE NOT EXISTS (select 1 from devices_v dtemp where rm.devicename = dtemp.devicename)
ORDER BY devicename;

Currently this view is connected with another similar view from the old database, allowing the usage of old devices, which are still being developed in FESA2 and cannot be inserted into the new database. However in the long term this connection will be obsolete.

NOTE: On the CERN side exist a concept of proxies. Those are special RDA "devices" to provide the internal compatibility between the RDA2 and RDA3 clients/servers. At GSI no such concept is required and hence all the proxy related references are either removed or replaced by some constants (proxyname).

RDA Admin config generator

The CMW team offers a tool called RDA admin console, which is used to inspect different known RDA servers. This tool requires a specific configuration with listed server names. The configuration itself may be generated by using a provided generate option. The generator accesses the database to get the information about deployed servers. This information is acquired from the view called CMW_DEVICECLASSES. This view was not a part of the original database deployment needed to be added to the database in order to use the config generator. The code for the view is as follows (alternatively can be found in CERNs repository):

CREATE OR REPLACE FORCE VIEW "FESA30"."CMW_DEVICECLASSES" ("CLASSNAME", "FECNAME", "SERVERNAME", "IMPLEMENTATION", "DESCRIPTION", "ACCELERATOR") AS
SELECT DISTINCT d.classname, d.fecname , d.servername , d.implementation, d.description, d.accelerator FROM devices_v d;=

-- VitaliyRapp - 01 Dec 2014