# Presentation: 329880

## RDB for Diamond

- K.Vijayan
- March 2005

## Comments

- Good morning everyone, I am very much interested to attend to this meeting, but I was unable to come due to family commitments.
- I have prepared a small presentation which explains our database work in DLS.

## DLS Schema contains

**EPICS**
- Domain, Sub domain, Technical Area, Component, Subsystem, Record Specifier
- Crate
- Device
- IOC
- DBD
- DBGroups
- DB File
- DB file Templates
- Slot
- Carrier & IP Cards
- Connection Between the Cards & the Device
**Equipment Tracking System**
- Equipment Entry (Master and Equipment Entries )
- Equipment Transfer ( Local and Subcontract transfer)
- Fault registration ( Equipment fault registration and its details)
**Cable**
**E-Log ( Under Development)**

## Comments

- DLS schema contains
**EPICS**
- We are capturing the device names, crate type, crate, IOC information and the Expanded db (after substitution) files to RDB.
- Rack location and the devices in the rack are also in rdb.
**Cable**
- Cable data are prepared by their user in the excel spreadsheet.
- This data is then uploaded from spreadsheet to oracle database. Users can add/modify/delete there cable information using a web application. Data can be download to TAB delimited .xls file extension. This .xls file can be converted to MS-Access Database, which is used by the contractors.
**Equipment tracking system**
- Once the equipment is delivered to the DLS, it is given a unique DLS Equipment Id ( This DLS Equipment Id will differ for different group of equipments)
- Basically this system save all the equipment information (manufacturer name, model, type etc.,) , location of the equipment, responsible group/user of the equipment, status of the equipment.
** ****Elog.**
- Under development.

## Device Naming

**Domain, Subdomain, Technical Area & Component**
**Device - DD[SSS]-TT-CCCCC-NN**
**Examples:**
**	****LI-VA-PIRG-01**
**	****BR01C-MA-VSTR-01**
**	****BS-MA-QUAD-01**
**	****SR01A-MA-QUAD-10**
**Subsystem **
**Record Specifier**
**PV Name = Device:[Subsystem]:Record Specifier**
**Example:**
**     ****BL06I-MO-MONO-01:AX1:HOMEF**

## Comments

**Device ID : DD[SSS]-TA-CCCCC-NN**
**     ****DD – Domain identifies a major area of the accelerator complex.**
**     ****SSS – Optional - Sub-domain is used to further subdivide complex domains such as SR (Storage**
**                ****Ring).  Each domain has its own separately defined list of sub-domains. **
**     ****TA – Technical area defines the technical sub-system to which the device belongs.  It can also be**
**               ****used to help identify the group or person responsible for the device.**
**    ****CCCCC - defines the particular type of device.**
**    ****NN - A two digit number identifying a particular instance of a device.**
_**PV Name :**_
**EPICS record reference names are constructed from device names by appending a**
**record specifier (and optionally, a sub-system name)  as follows**
**     ****Device ID[:Subsystem]:Record Specifier**
**Sub system – sub system is intended to allow the use of hierarchy within the EPICS IOC database (i.e. Visual DCT hierarchy) **
**The total length of sub system and record specifier may be up to 9 characters.  However, if the sub system component is not used then record specifier may be up to 10 characters in length.  (This accommodates the : delimiter between sub system and the record.) **
**Separate tables for each of device, subsystem and record specifier.**

## Expanded DB File in RDB

- RDB

- Perl Scripts
- Expanded
- DB Files

- Perl Scripts
- Expanded
- DB Files &
- Template
- DB Files

- Front end
- screens (web browser)
- using Apache/PHP

- Epics Record
- Template File

- Substitution
- File

- dbLoadTemplate

- Perl Scripts
- DBD File

- Perl Scripts
- Template
- DB Files

## Comments

**Upload Process of Expanded DB (after substitution) File to RDB.**
**This upload process in done using Perl scripts (thanks to Kay- Uwe Kasemir)**
**Before using this Perl scripts, user have to identify the db group (i.e. A unique name is given**
**to the expanded file with ioc id ). Then they can use this db group name to upload the **
**Expanded db file to RDB. **
**I modified the same Perl scripts according to our requirements. Most of our tables will have **
**two columns updated_by and updated_date. While uploading the expanded db file to rdb**
**User id and the date time is captured in the oracle tables. So later we can view which user is**
**using this expanded db file and the date time of last modification. **
**After uploading the expanded db file to rdb,  user can modify or delete any  PV’s in the RDB using**
**the web browser.**
**This upload process will fail whenever any extra PV’s exists in the Oracle Database**
**compare to the uploaded Expanded DB File. Otherwise the expanded db file will be**
**successfully uploaded to RDB.**
**Example : BL06I-MO-MONO-01:AX1:HOMEF**
**PV Name : Device ID:[Subsystem]:Record Specifier**
**While uploading the expanded DB file to RDB, it inserts the device id, subsystem and record**
**specifier to the device table, subsystem table and the record specifier table if the id’s are not exists.**

## DB File

## Equipment Tracking System

**The Equipment tracking system will identify :**
**The location of the equipment.**
**Current and Previous Group/User of the equipment.**
**Current and Previous location (Device ID) with EPICS Naming(SR01A-CS-CIA-01),  building and room also included.**
**Equipment Name (Device ID) with EPICS Naming convention (BL02I-VA-IONP-03).**
**Equipment on Loan (i.e. Equipments with Subcontract ) and whether it is returned or not?**
**Fault Equipment (i.e. Whether it is sent to manufacturer and received back or not?)**
**Status of the each equipment (Idle, Working, Loan, Fault, Sent to Manufacturer,  received).**

## Comments

**Equipment Tracking system **
- This system will store all the information of the equipment like manufacturer name, model,
- type of the equipment (crate, ip carrier, etc.,)
- This system will identify the physical location of the equipment. It is identified by the
- building id and by room id and there is facility to use the epics device naming convention to
- identify the location of the equipment.
- We have a status of the equipment like Idle, Working, Loan, Fault, Sent to manufacturer and
- Received. The equipment is delivered to the DLS, the initial status of the equipment will be
- Idle and once the equipment is starts functioning or assigned to user, then it becomes
- Working.
**We have two types of transfer of equipment. **
**Local Transfer : **In local transfer an equipment is transferred within the company ( e.g. transfer the equipment from one group to another group or one user to another user or from one location to another location all the transfers are updated in the RDB).
**Subcontract transfer : **User can transfer an equipment to the subcontract and the details of the transfer. Then the status will become Loan.  When we receive the equipment from the subcontract then the received information are stored in the RDB.

## comments

**Fault registration : **If an equipment become faulty, user has to register the
- fault in the fault registration program. It accepts detail information about the
- fault. The whereabouts of the faulty equipment is tracked. (i.e.) the rdb is
- updated when the equipment is sent to Manufacturer for repair and
- subsequently when received from the manufacturer after repair. Some times
- the faulty equipment will be repaired by the DLS group or user. This
- information is also captured in this system.

## Cable

- Each Cable is identified by cable id.
- Cable users are following a different naming convention for their cable id. But it is mapped according to the Device Naming Convention in the oracle database.
- Cable users have permissions to view, add, modify and delete cables from the database, all other users have the read permission to view the cable data.
- Each cable identifies – Start position id, Finish position id, junction box, run length, cable type, cable class, drawing number, comments etc.,

## What we are using

-  Oracle 9i on Red Hat Linux AS v2.1 - Dell Server
-  Apache Web Server
-  PHP
- User interface screens / forms for data input & reading  using web browser
-  Perl
-  for uploading of flat files to the RDB & vice versa
-  ER Studio
-  Logical and Physical design of the EPICS database

## Future Implementation

- DB Template Files stored in RDB
- VDCT Comment & user comments stored in RDB
- Connection between cards and the devices
- RDB has change of record fields. Use of sticky attributes to preserve data.

## comments

**       ****We have plans to store templates of the db file with both VDCT comments and user comments in the RDB. **
**	****We have also planned to capture the information of connection between cards and the devices.   **

## Web Application and some screen shots 

## Web Browser

**We have an web application to access our Oracle Database. This application will**
**have user login and each user will have their own profile. This profile explains the**
**permission (insert, modify, delete) for each user. And we can restrict each user to**
**access different programs in the web application. **
**For example : Cable user will not have all the access of the EPICS programs and**
**EPICS users will have only the READ permission to the cable data.**
**Most of our oracle table will have two columns updated_by and updated_date. We can**
**identify the last modified user/date time of  each record. This is very useful for**
**them to check who is created this device or record etc.,**
**User click the left function button (device), it will displays with first 10**
**(changeable up to 50) records out of total records in the table. Every page will**
**have a search criteria, user can search records easily from the database.**
**Session time out is an hour. After user logged in and the application is idle (not used)**
**for one hour, the session will expired.**
** **

## Web Browser

## Main Menu

## Main menu comments

- Each user will have a profile and according to their profile the menus will be displayed.
- A user with Admin profile will be able to view and use the admin programs while other will not have permission to access the admin programs.

## Device Maintenance

## Device Maintenance comments

- Users initially define their device names in this program.
- During the upload process of expanded DB file, the device names (id) are inserted in to this table from the PV name (device_id:[subsystem]:record specifier).
- We can view the user name and the date time of device creation.

## Subsystem Maintenance

## Record Specifier

## Subsystem and Record Specifier comments

- During the upload process of Expanded DB file to RDB, these two tables are populated with the subsystem id in the subsystem table and record specifier id in the record specifier table.
- Later user is expected to give their appropriate subsystem and record specifier description.

## DB Group

## DB Group Comments

- Unique name for each expanded db file with ioc reference.
- Using this program, user will delete the expanded db file from rdb.
- User can also delete a db group.

## DB Record Maintenance

## DB Field Maintenance

## Record and Field comments

- After uploading an expanded DB file to RDB, users can view the db file as records and fields using this record maintenance and field maintenance screen.
- Next to record id column there is “D” button, after clicking this “D” button user will able to see all the fields of the record type of the particular record id (PV). And they can add any field or modify using this program.
- That’s it from me. Thank you very much.

## THE END