HGTD Production Database Tutorial

1. Introduction

During the construction of the High Granularity Timing Detector, several thousands of silicon sensor modules will be produced. The purpose of the database is to track the history and the quality control of the modules and the detector units components.

The current content of the database is described in the below table. Preliminarily the production database consists of the following tables, each table corresponds to a component
type: sensor, asic, hybrid, module, moduleflex, support unit detector unit .

2. Setting up the database

-In order to facilitate the implementation of the content in the database, an Entity relationship diagram(ERD) was designed as illustrated in the below attached file (HGTDPDB_ER.pdf). The columns
and their types are shown for each table. Each table should contain a column that identifies each row stored in the table, the column is supposed to be unique. An identifier to track each component in building hgtd modules would be essential , a serial number definition for hgtd components is proposed, the detail can be found in this presentation: https://indico.cern.ch/event/1121537/contributions/4708682/attachments/2380675/4067695/prodDB_serialnumber_28jan22_1.pdf

- The serial number is an ideal candidate to be the primary key in database terminology, an auto-increment (id) is implemented as the primary key for all of the tables and the serial number can be used for queries on the website.
-A SQL script was created based on this ER diagram and implemented using the MYSQL database Management system. The current database is hosted at CERN in the Database On demand service. The parameters of the database are the following:

- Host: dbod-hgtd-pdb.cern.ch

-Port: 5506

-User: admin

-Password: (distributed privately when needed)

These parameters are used to connect to the database and to interface with it.

3. Web Interface

After setting up the database, a web user interface was developed for uploading and retrieving data from this database. This web interface page holds many features for the registered users to interface to the database. The users can browse over all the recorded information of all the defined types of the HGTD module components.

The website link: https://dbod-hgtd-pdb.web.cern.ch/dbod-hgtd-pdb/HGTD-Website/

The web interface was developed using PHP and JavaScript as programming languages. The website's front-end stack is made up using HTML and CSS. Useful link to learn these languages: https://www.w3schools.com/

3.1 Code description

The full code used in the development is uploaded here: https://gitlab.cern.ch/maittaml/productiondatabase-webinterface/

-The structure of the code is as follows: the HGTD-Website folder contains many scripts and folders, here I will explain the role of each script:

• dbconnection.php: in this script, we defined the parameters of the database.
• index.php: This file is the skeleton of the website. It showed the content of the first page of the website which is the login-registration system.
• Template.php: It describes the main page of the website after login.
• interface.php: the script is dedicated to the user profile(to update, and change the login parameters).
• select.php: It is used to make a selection query of the components pages.
• style.css: It describes the style of the web page.
• Images: This folder contains the pictures used in the website design.
• Login-Registration system: This folder contains all the scripts used to build the login-registration system.
• components: It contains the code folder of each component page. I will take the sensor folder as an example to describe the role of each script. The same options were implemented for the other components. *sensor:

-index.php: the main script of the sensor page. It holds the features of reading data form the database, display them in the website, add new record, search query and import an .csv file contains new data.

-insert.php: the script used to add new record in the sensor table.

- import-csv.php: It is used to upload a .csv file containing new sensor data.

-export.php: It is used to export the data from the website in the format of .csv file.

-plotiv.php: it is used to plot the current-voltage (IV) curve using the IV sensor measurement file (in the format of .csv)

-plotcv.php : it is used to plot the capacitance voltage (CV) curve using the CV sensor measurement file(in the format of .csv)

-downloadiv.php and downloadiv.php : The scripts are used to export the IV and CV files from the website.

3.2 Possibles features to be added to the website

• When importing .csv file :

-No check of data quality at upload/plot time, an example is shown in the below picture:

These options had to be added to the import or plotting script.

• When adding a new record, if no entries for IV/C fields, put "None" in the field. An improvement of the insert.php script is needed.
• Expand the query capabilities: - Create QR / barcode for SN and create a query using the QR / barcode.

• Display of fields in the Web interface page:

  • Currently, all fields are shown -> many columns and making the page very wide. We need to display a few of the more important columns first, and then if we want more information or information from a specific group of the component, we can do it by using a query table (e.g. from a specific vendor, production date, range of serial numbers), Once this group is selected, we can then look at more information.

4. Script-based interface

Another command tool was developed to interface with the database. It is mainly written using python language which allows the reading/writing of information using a command line in a terminal window. The sqlalchemy package is chosen as the main toolkit to provide developers the full power and flexibility of SQL, this library is used as an Object Relational Mapper (ORM) tool that maps the tables on the production database to python classes and automatically converts function to SQL code.

-The code repository is found here: https://gitlab.cern.ch/atlas-hgtd/atlas-hgtd-proddatabase/-/tree/master/hgtd_db

-The setup steps and examples of running the database interface scripts are described in the README.md file.

-Currently, the script tool is used to read/write data from the database. More features need to be implemented.