Mapping the south SMT in the EPICS database   

 

(by Freya Blekman, fblekman@fnal.gov, NIKHEF, Amsterdam, the Netherlands.)

 

            Contents:

 

  1. The Excel files
  2. how to get the information to SQL commands
  3. General structure
  4. The python script  southhalfinstaller.py
  5. the SQL macro setupSMTS.sql
  6. changing the format of the Excel sheet and still getting correct SQL commands
  7. changing the location of the .sql files created by Visual Basic

 

 

 

1.     The Excel files

 

The cabling and mapping of all components of the south half of the SMT is done and maintained by John Foglesong and Linda Bagby.  Their most recent version can usually be found in d0server4/users/fogie/tempfiles/LindaMapping/. These excel sheets contain much more information than is necessary for our purposes. This is why I added an extra worksheet which gathers all the info necessary and creates the appropriate SQL commands. The modified version of the spreadsheet can then be found in the following dir on d0server4: d0server4/users/fblekman/ExceltoSQL/. This directory also contains a MS Visual Basic Module which can be used to collect and save the SQL commands.

 

2.     How to get the information to SQL commands

 

When looking at the file d0server4/users/fblekman/excel to sql/my SE Quad Assignment.xls one can see that it contains several worksheets: Sheet1, Sheet2, Sheet3 and SQL_Commands. The sheets 1 to 3 are direct copies from the Sector_6/1/2VRB files in the excel documents created by John. The SQL_Commands sheet contains the macros to extract the information from all different cells. There are two buttons on there, one extracts all info and creates the SQL commands, pressing the other button will save the commands in files (and ask you to confirm this).

 

3.     General structure

 

The Epics database for the south SMT is set up in the following way:

After declaration of all different types of tables, the HDIs and SVX tables are inserted. Then the Interface boards, Sequencers and VRBs (and their channels, etc) are added. At this time a 1 to 1 mapping is still used in the database.  The Interface channels are connected to dummy HDIs which are deleted after the proper HDI is associated with the interface channel which belongs to it. Eventually, the interface channels are associated with the right Sequencer channels, which in their place are then assigned to certain VRB channels.

All these different steps contain large amounts of SQL commands. The SQL commands belonging to each different step or part of the DAQ chain are saved in different files, to make it easier to modify single parts of it.

When extracting onl_smtcalib from CVS, the /onl_smtcalib/oracle/ directory will have the following file structure:

 

drwxr-xr-x    2 fblekman D0            57 Dec  2 16:57 CVS

-rw-r--r--    1 fblekman D0         25980 Nov 30 16:34 createDb.sql

drwxr-xr-x    3 fblekman D0          4096 Dec  2 16:47 filesFromExcel

                                                        -> files created by excel

-rw-r--r--    1 fblekman D0         12606 Sep 21 18:54 setup1Per.sql

-rwxr-xr-x    1 fblekman D0          9771 Sep 27 14:50 setupBarrel.py

-rw-r--r--    1 fblekman D0         49535 Sep 28 16:14 setupBarrel.sql

-rw-r--r--    1 fblekman D0          8189 Sep 21 18:54 setupNu.sql

-rw-r--r--    1 fblekman D0           537 Dec  2 16:42 setupSMTS.sql

drwxr-xr-x    3 fblekman D0            51 Dec  2 16:47 setupSouthSMT

 -> southhalfinstaller.py

 

 

The files and directories used by setupSMTS.sql are underlined.

 

4.     The python script southhalfinstaller.py

 

Run this script to create all SQL files needed for the setup of the database. One can also set the script to connect to the fblekman@d0on ORACLE account itself, but at the moment it is more convenient to write to separate files first. If this script isn’t run, the setupSMTS.sql macro won’t have all the files it needs. After running this python script, the setupSouthSMT directory will also contain a collection of SQL files.

 

5.     The SQL macro setupSMTS.sql

 

This macro runs all different sql macros in the right order.  Look what’s in there to get an idea of how it works. Essentially it first runs SetupDb.sql and then installs the HDIs, SVX chips, Crates, VRB and SEQ Controllers, Interface Boards, Sequencers and VRBs and their respective channels. The connections between the different

Then it starts to modify the different connections in the ORACLE database, to get the same configuration as in the real SMTS.

 

6.     Changing the Excel sheet and still getting correct SQL commands

 

The Visual Basic code for the Excel sheet is set up to ensure that the ingoing exceed worksheet can be changed fairly easily without having to completely re-write the VB code. To modify the VB code, open excel, select the SQL_Commands worksheet and right-click on the ‘get info from Excel’-button. Then Choose ‘Assign Macro’ to open a window and view the VB code. One of the choices there is ‘Edit’, this opens the Microsoft Visual Basic Editor. The three variables that can be modified easily are situated at the top of the VB script:

·        NSheet is the number of sheets that the script will be looping over. For the south SMT this is set to 3.

·        String sStartSheetName(6) should contain the names of the sheets to be looped over.

·        Double lStartColumn(nReadCols) contains the column numbers in the original sheet. The comment behind the number tells you what type of column the number should be pointing at. NReadCols is the number of columns to be read.

 

After changing any of these values the VB code should be recompiled. This can be done in the ‘debug’ menu in the taskbar.

 

7.     Changing the location of the .sql files created by Visual Basic

 

Open the macro that saves the SQL commands in Excel. (see 6.)  The filenames are defined at the top of the subroutine. Don’t forget to recompile and save the macro before closing excel.