---
# System prepended metadata

title: Access to Database
tags: [database, HGCal]

---

# Access to Database
## table of contents
## Connection
## SQL Commands
```
select table_name from all_tables where table_name like '%HGC%'
```

all_tables is an reserved words to call all tables. "where" adds condition. "like" makes comparison.


```
describe CMS_HGC_CORE_CONSTRUCT.PARTS
```

Lists all available column in PARTS label. Which CMS_HGC_CORE_CONSTRUCT is an user.


```
select KIND_OF_PART_ID,DISPLAY_NAME,LPNAME,COMMENT_DESCRIPTION from CMS_HGC_CORE_CONSTRUCT.KINDS_OF_PARTS
```

Lists part_id definitions. Only selected columns are listed.

## Server structure
```
## All columns in PARTS table
describe CMS_HGC_CORE_CONSTRUCT.PARTS

## standard output of command
PART_ID                NOT NULL NUMBER(38)                  
KIND_OF_PART_ID        NOT NULL NUMBER(38)                  
LOCATION_ID                     NUMBER(38)                  
MANUFACTURER_ID                 NUMBER(38)                  
IS_RECORD_DELETED      NOT NULL CHAR(1)                     
RECORD_INSERTION_TIME  NOT NULL TIMESTAMP(6) WITH TIME ZONE 
RECORD_INSERTION_USER  NOT NULL VARCHAR2(50)                
BARCODE                         VARCHAR2(40)                
SERIAL_NUMBER                   VARCHAR2(40)                
VERSION                         VARCHAR2(40)                
NAME_LABEL                      VARCHAR2(40)                
INSTALLED_DATE                  TIMESTAMP(6) WITH TIME ZONE 
REMOVED_DATE                    TIMESTAMP(6) WITH TIME ZONE 
INSTALLED_BY_USER               VARCHAR2(50)                
REMOVED_BY_USER                 VARCHAR2(50)                
EXTENSION_TABLE_NAME            VARCHAR2(30)                
COMMENT_DESCRIPTION             VARCHAR2(4000)              
RECORD_LASTUPDATE_TIME          TIMESTAMP(6) WITH TIME ZONE 
RECORD_LASTUPDATE_USER          VARCHAR2(50)                
PRODUCTION_DATE                 DATE                        
BATCH_NUMBER                    VARCHAR2(30)                
```

```
## All users related to HGC

CMS_HGC_CORE_ATTRIBUTE
CMS_HGC_CORE_COND
CMS_HGC_CORE_CONSTRUCT
CMS_HGC_CORE_IOV_MGMNT
CMS_HGC_CORE_MANAGEMNT
CMS_HGC_HGCAL_COND
CMS_HGC_HGCAL_CONSTRUCT
CMS_HGC_HGCAL_VIEW
CMS_HGC_PRTTYPE_HGCAL_ADMIN
CMS_HGC_PRTTYPE_HGCAL_WRITER
CMS_HGCAL_APPUSER_ADMIN
CMS_HGCAL_APPUSER_R
CMS_HGCAL_APPUSER_W
CMS_HGCAL_PVSS_COND
```

Available table and list of variables in PARTS. KIND_OF_PARTS maps ID and string.

```
category : manufacturer ID
  1000 : Hamamatsu-HPK
  1400 : CERN
  1420 : NCU
  1440 : Academia Sinica
  1460 : CTT
  1780 : CERN
  1781 : ELTOS
  1782 : PLOTECH
  1783 : ¿
  1784 : CISTELAIER
  2200 : CISTALAIER
  2201 : ELTOS
  2580 : ELTOS
  3380 : 6
  4180 : Cistelaier
  4181 : Eltos
  4182 : Plotech
  4620 : IBM
  5080 : TIFR
  5380 : Tile Manf Comp
  5400 : IBM France
  6180 : Fermilab
```
```
category : kinds_of_parts ID
  1000 : HGCAL Detector ROOT
  1020 : Hamamatsu-S10938-4956 Sensor
  1040 : Hamamatsu-S10938-4956 Cell
  1060 : Hamamatsu-S10938-4956 Calibration Cell
  1080 : Hamamatsu-S10938-4956 Guard Ring
  1400 : HPK Six Inch 128 Cell Silicon Sensor
  1800 : HGC Six Inch 128 Channel PCB
  1820 : HGC Six Inch Kaptonized Plate
  2200 : HGC Six Inch Silicon Module
  2620 : HPK Eight Inch 271 Cell Silicon Sensor
  2700 : HPK Six Inch 128 Sensor Cell
  2720 : HPK Six Inch 128 Sensor Calib Cell
  2740 : HPK Six Inch 128 Sensor Guard Ring
  2760 : HPK Eight Inch 271 Sensor Cell
  2780 : HPK Eight Inch 271 Sensor Calib Cell
  2800 : HPK Eight Inch 271 Sensor Guard Ring
  3000 : HPK Six Inch 256 Cell Silicon Sensor
  3020 : HPK Six Inch 256 Sensor Cell
  3040 : HPK Six Inch 256 Sensor Calib Cell
  3060 : HPK Six Inch 256 Sensor Guard Ring
  3400 : HPK Eight Inch 271 Sensor Monitor Diode
  4200 : HGC Six Inch Silicon Proto Module
  5000 : HGC Eight Inch 271 Channel PCB
  5020 : HGC Six Inch 256 Channel PCB
  5400 : SKI ROC
  6600 : HGC Sensor Wafer
  6620 : HGC Sensor
  7040 : HGCROC2
  7400 : HGC Halfmoon
  8200 : HGC LD Hex PCB
  8220 : HGC HD Hex PCB
  9040 : HGCROCV2
  9060 : HGCROCV2A
  9440 : HGC Half Sensor
  9460 : HGC Five Sensor
  9480 : HGC Three Sensor
  9500 : HGC Semi Sensor
  9520 : HGC Semi(-) Sensor
  9540 : HGC Choptwo(-) Sensor
  9840 : FatherKOP
  9860 : Children KOP
 10240 : PCB baseplate with Kapton overlay V1
 10260 : PCB baseplate with Kapton overlay V2
 10280 : PCB baseplate with Kapton overlay V3
 11040 : 120um HD Si Sensor Wafer
 11060 : 200um LD Si Sensor Wafer
 11080 : 300um LD Si Sensor Wafer
 11100 : 120um Si Sensor HD Full
 11120 : 120um Si Sensor HD Top
 11140 : 120um Si Sensor HD Bottom
 11160 : 120um Si Sensor HD Left
 11180 : 120um Si Sensor HD Right
 11200 : 120um Si Sensor HD Five
 11220 : 120um Si Sensor HD Halfmoon-T
 11880 : 200um Si Sensor LD Full
 11900 : 200um Si Sensor LD Top
 11920 : 200um Si Sensor LD Bottom
 11940 : 200um Si Sensor LD Left
 11960 : 200um Si Sensor LD Right
 11980 : 200um Si Sensor LD Five
 12000 : 200um Si Sensor LD Three
 12020 : 200um Si Sensor LD Halfmoon-T
 12040 : 300um Si Sensor LD Full
 12060 : 300um Si Sensor LD Top
 12080 : 300um Si Sensor LD Bottom
 12100 : 300um Si Sensor LD Left
 12120 : 300um Si Sensor LD Right
 12140 : 300um Si Sensor LD Five
 12160 : 300um Si Sensor LD Three
 12180 : 300um Si Sensor LD Halfmoon-T
 12240 : HD HGCROC
 12260 : LD HGCROC
 12280 : SiPM HGCROC
 12760 : 120um Si Sensor HD Halfmoon-B
 12780 : 120um Si Sensor HD Halfmoon-TL
 12800 : 120um Si Sensor HD Halfmoon-BL
 12820 : 120um Si Sensor HD Halfmoon-BR
 12840 : 120um Si Sensor HD Halfmoon-TR
 12860 : 200um Si Sensor LD Halfmoon-B
 12880 : 200um Si Sensor LD Halfmoon-TL
 12900 : 200um Si Sensor LD Halfmoon-BL
 12920 : 200um Si Sensor LD Halfmoon-BR
 12940 : 200um Si Sensor LD Halfmoon-TR
 12960 : 300um Si Sensor LD Halfmoon-B
 12980 : 300um Si Sensor LD Halfmoon-TL
 13000 : 300um Si Sensor LD Halfmoon-BL
 13020 : 300um Si Sensor LD Halfmoon-BR
 13040 : 300um Si Sensor LD Halfmoon-TR
 14000 : PCB HD Full
 14020 : PCB HD Top
 14040 : PCB HD Bottom
 14060 : PCB HD Left
 14080 : PCB HD Right
 14100 : PCB HD Five
 14120 : PCB LD Full
 14140 : PCB LD Top
 14160 : PCB LD Bottom
 14180 : PCB LD Left
 14200 : PCB LD Right
 14220 : PCB LD Five
 14240 : PCB LD Three
 14260 : CuW/Kapton Baseplate HD Full
 14280 : CuW/Kapton Baseplate HD Top
 14300 : CuW/Kapton Baseplate HD Bottom
 14320 : CuW/Kapton Baseplate HD Left
 14340 : CuW/Kapton Baseplate HD Right
 14360 : CuW/Kapton Baseplate HD Five
 14380 : CuW/Kapton Baseplate LD Full
 14400 : CuW/Kapton Baseplate LD Top
 14420 : CuW/Kapton Baseplate LD Bottom
 14440 : CuW/Kapton Baseplate LD Left
 14460 : CuW/Kapton Baseplate LD Right
 14480 : CuW/Kapton Baseplate LD Five
 14500 : CuW/Kapton Baseplate LD Three
 14520 : PCB/Kapton Baseplate HD Full
 14540 : PCB/Kapton Baseplate HD Top
 14560 : PCB/Kapton Baseplate HD Bottom
 14580 : PCB/Kapton Baseplate HD Left
 14600 : PCB/Kapton Baseplate HD Right
 14620 : PCB/Kapton Baseplate HD Five
 14640 : PCB/Kapton Baseplate LD Full
 14660 : PCB/Kapton Baseplate LD Top
 14680 : PCB/Kapton Baseplate LD Bottom
 14700 : PCB/Kapton Baseplate LD Left
 14720 : PCB/Kapton Baseplate LD Right
 14740 : PCB/Kapton Baseplate LD Five
 14760 : PCB/Kapton Baseplate LD Three
 15040 : EM 120um Si ProtoModule HD Full
 15060 : EM 120um Si ProtoModule HD Top
 15080 : EM 120um Si ProtoModule HD Bottom
 15100 : EM 120um Si ProtoModule HD Left
 15120 : EM 120um Si ProtoModule HD Right
 15140 : EM 120um Si ProtoModule HD Five
 15160 : EM 200um Si ProtoModule LD Full
 15180 : EM 200um Si ProtoModule LD Top
 15200 : EM 200um Si ProtoModule LD Bottom
 15220 : EM 200um Si ProtoModule LD Left
 15240 : EM 200um Si ProtoModule LD Right
 15260 : EM 200um Si ProtoModule LD Five
 15280 : EM 200um Si ProtoModule LD Full+Three
 15300 : EM 300um Si ProtoModule LD Full
 15320 : EM 300um Si ProtoModule LD Top
 15340 : EM 300um Si ProtoModule LD Bottom
 15360 : EM 300um Si ProtoModule LD Left
 15380 : EM 300um Si ProtoModule LD Right
 15400 : EM 300um Si ProtoModule LD Five
 15420 : EM 300um Si ProtoModule LD Full+Three
 15440 : EM 120um Si Module HD Full
 15460 : EM 120um Si Module HD Top
 15480 : EM 120um Si Module HD Bottom
 15500 : EM 120um Si Module HD Left
 15520 : EM 120um Si Module HD Right
 15540 : EM 120um Si Module HD Five
 15560 : EM 200um Si Module LD Full
 15580 : EM 200um Si Module LD Top
 15600 : EM 200um Si Module LD Bottom
 15620 : EM 200um Si Module LD Left
 15640 : EM 200um Si Module LD Right
 15660 : EM 200um Si Module LD Five
 15680 : EM 200um Si Module LD Full+Three
 15700 : EM 300um Si Module LD Full
 15720 : EM 300um Si Module LD Top
 15740 : EM 300um Si Module LD Bottom
 15760 : EM 300um Si Module LD Left
 15780 : EM 300um Si Module LD Right
 15800 : EM 300um Si Module LD Five
 15820 : EM 300um Si Module LD Full+Three
 15840 : HAD 120um Si ProtoModule HD Full
 15860 : HAD 120um Si ProtoModule HD Top
 15880 : HAD 120um Si ProtoModule HD Bottom
 15900 : HAD 120um Si ProtoModule HD Left
 15920 : HAD 120um Si ProtoModule HD Right
 15940 : HAD 120um Si ProtoModule HD Five
 15960 : HAD 200um Si ProtoModule LD Full
 15980 : HAD 200um Si ProtoModule LD Top
 16000 : HAD 200um Si ProtoModule LD Bottom
 16020 : HAD 200um Si ProtoModule LD Left
 16040 : HAD 200um Si ProtoModule LD Right
 16060 : HAD 200um Si ProtoModule LD Five
 16080 : HAD 200um Si ProtoModule LD Full+Three
 16100 : HAD 300um Si ProtoModule LD Full
 16120 : HAD 300um Si ProtoModule LD Top
 16140 : HAD 300um Si ProtoModule LD Bottom
 16160 : HAD 300um Si ProtoModule LD Left
 16180 : HAD 300um Si ProtoModule LD Right
 16200 : HAD 300um Si ProtoModule LD Five
 16220 : HAD 300um Si ProtoModule LD Full+Three
 16240 : HAD 120um Si Module HD Full
 16260 : HAD 120um Si Module HD Top
 16280 : HAD 120um Si Module HD Bottom
 16300 : HAD 120um Si Module HD Left
 16320 : HAD 120um Si Module HD Right
 16340 : HAD 120um Si Module HD Five
 16360 : HAD 200um Si Module LD Full
 16380 : HAD 200um Si Module LD Top
 16400 : HAD 200um Si Module LD Bottom
 16420 : HAD 200um Si Module LD Left
 16440 : HAD 200um Si Module LD Right
 16460 : HAD 200um Si Module LD Five
 16480 : HAD 200um Si Module LD Full+Three
 16500 : HAD 300um Si Module LD Full
 16520 : HAD 300um Si Module LD Top
 16540 : HAD 300um Si Module LD Bottom
 16560 : HAD 300um Si Module LD Left
 16580 : HAD 300um Si Module LD Right
 16600 : HAD 300um Si Module LD Five
 16620 : HAD 300um Si Module LD Full+Three
 17840 : Scint Tileboard A5
 17860 : Scint Tileboard A6
 17880 : Scint Tileboard B11
 17900 : Scint Tileboard B12
 17920 : Scint Tileboard C5
 17940 : Scint Tileboard D8
 17960 : Scint Tileboard E8
 17980 : Scint Tileboard J8
 18000 : Scint Tileboard G3L
 18020 : Scint Tileboard G3C
 18040 : Scint Tileboard G3R
 18060 : Scint Tileboard G5L
 18080 : Scint Tileboard G5C
 18100 : Scint Tileboard G5R
 18120 : Scint Tileboard G6L
 18140 : Scint Tileboard G6C
 18160 : Scint Tileboard G6R
 18180 : Scint Tileboard G7L
 18200 : Scint Tileboard G7C
 18220 : Scint Tileboard G7R
 18240 : Scint Tileboard G8L
 18260 : Scint Tileboard G8C
 18280 : Scint Tileboard G8R
 18300 : Scint Tileboard K4L
 18320 : Scint Tileboard K4C
 18340 : Scint Tileboard K4R
 18360 : Scint Tileboard K6L
 18380 : Scint Tileboard K6C
 18400 : Scint Tileboard K6R
 18420 : Scint Tileboard K7L
 18440 : Scint Tileboard K7C
 18460 : Scint Tileboard K7R
 18480 : Scint Tileboard K8L
 18500 : Scint Tileboard K8C
 18520 : Scint Tileboard K8R
 19040 : CastCalibTile aa
 19060 : CastCalibTile bb
 19080 : CastCalibTile cc
 19100 : CastCalibTile dd
 19120 : CastCalibTile ee
 19140 : CastCalibTile ff
 19160 : CastCalibTile gg
 19180 : CastCalibTile hh
 19200 : CastCalibTile ii
 19220 : CastCalibTile jj
 19240 : CastCalibTile kk
 19260 : CastCalibTile ll
 19280 : CastCalibTile mm
 19300 : CastCalibTile nn
 19320 : CastCalibTile oo
 19340 : CastCalibTile pp
 19360 : CastCalibTile qq
 19380 : CastCalibTile rr
 19400 : CastCalibTile ss
 19420 : CastCalibTile tt
 19440 : CastCalibTile uu
 19460 : CastScintTile aa
 19480 : CastScintTile bb
 19500 : CastScintTile cc
 19520 : CastScintTile dd
 19540 : CastScintTile ee
 19560 : CastScintTile ff
 19580 : CastScintTile gg
 19600 : CastScintTile hh
 19620 : CastScintTile ii
 19640 : CastScintTile jj
 19660 : CastScintTile kk
 19680 : CastScintTile ll
 19700 : CastScintTile mm
 19720 : CastScintTile nn
 19740 : CastScintTile oo
 19760 : CastScintTile pp
 19780 : CastScintTile qq
 19800 : CastScintTile rr
 19820 : CastScintTile ss
 19840 : CastScintTile tt
 19860 : CastScintTile uu
 20240 : 2mm SiPM
 20260 : 4mm SiPM
 20640 : Cast Scint Tile Module A5
 20660 : Cast Scint Tile Module A6
 20680 : Cast Scint Tile Module B11
 20700 : Cast Scint Tile Module B12
 20720 : Cast Scint Tile Module C5
 20740 : Cast Scint Tile Module D8
 20760 : Cast Scint Tile Module E8
 20780 : Cast Scint Tile Module J8
 20800 : Cast Scint Tile Module G3L
 20820 : Cast Scint Tile Module G3C
 20840 : Cast Scint Tile Module G3R
 20860 : Cast Scint Tile Module G5L
 20880 : Cast Scint Tile Module G5C
 20900 : Cast Scint Tile Module G5R
 20920 : Cast Scint Tile Module G6L
 20940 : Cast Scint Tile Module G6C
 20960 : Cast Scint Tile Module G6R
 20980 : Cast Scint Tile Module G7L
 21000 : Cast Scint Tile Module G7C
 21020 : Cast Scint Tile Module G7R
 21040 : Cast Scint Tile Module G8L
 21060 : Cast Scint Tile Module G8C
 21080 : Cast Scint Tile Module G8R
 21100 : Cast Scint Tile Module K4L
 21120 : Cast Scint Tile Module K4C
 21140 : Cast Scint Tile Module K4R
 21160 : Cast Scint Tile Module K6L
 21180 : Cast Scint Tile Module K6C
 21200 : Cast Scint Tile Module K6R
 21220 : Cast Scint Tile Module K7L
 21240 : Cast Scint Tile Module K7C
 21260 : Cast Scint Tile Module K7R
 21280 : Cast Scint Tile Module K8L
 21300 : Cast Scint Tile Module K8C
 21320 : Cast Scint Tile Module K8R
 21340 : 9mm SiPM
 21440 : MoldCalibTile aa
 21460 : MoldCalibTile bb
 21480 : MoldCalibTile cc
 21500 : MoldCalibTile dd
 21520 : MoldCalibTile ee
 21540 : MoldCalibTile ff
 21560 : MoldCalibTile gg
 21580 : MoldCalibTile hh
 21600 : MoldCalibTile ii
 21620 : MoldCalibTile jj
 21640 : MoldCalibTile kk
 21660 : MoldCalibTile ll
 21680 : MoldCalibTile mm
 21700 : MoldCalibTile nn
 21720 : MoldCalibTile oo
 21740 : MoldCalibTile pp
 21760 : MoldCalibTile qq
 21780 : MoldCalibTile rr
 21800 : MoldCalibTile ss
 21820 : MoldCalibTile tt
 21840 : MoldCalibTile uu
 21860 : MoldScintTile aa
 21880 : MoldScintTile bb
 21900 : MoldScintTile cc
 21920 : MoldScintTile dd
 21940 : MoldScintTile ee
 21960 : MoldScintTile ff
 21980 : MoldScintTile gg
 22000 : MoldScintTile hh
 22020 : MoldScintTile ii
 22040 : MoldScintTile jj
 22060 : MoldScintTile kk
 22080 : MoldScintTile ll
 22100 : MoldScintTile mm
 22120 : MoldScintTile nn
 22140 : MoldScintTile oo
 22160 : MoldScintTile pp
 22180 : MoldScintTile qq
 22200 : MoldScintTile rr
 22220 : MoldScintTile ss
 22240 : MoldScintTile tt
 22260 : MoldScintTile uu
 22640 : Mold Scint Tile Module D8
 22660 : Mold Scint Tile Module E8
 22680 : Mold Scint Tile Module G6L
 22700 : Mold Scint Tile Module G6C
 22720 : Mold Scint Tile Module G6R
 22740 : Mold Scint Tile Module G8L
 22760 : Mold Scint Tile Module G8C
 22780 : Mold Scint Tile Module G8R
 ```
## XML Structure
The nested tree for assembly.

## recommended variables
### Quality Control
#### Assembly
* Final flatness
* assembly displacement
#### Electronics
* Noise Level
* Pedestal
* Signal-Noise ratio
### Quality Assurance
#### Component information
* flatness measurement
* diameter measurement
* thickness measurement
#### Assembly
* flatness measurement
* PCB Shifting Distance
* Failed Channel Number
#### Links for detail
* ELOG Link
* Google Drive Link


## to do
### feature

- [ ] map partID and show name to human. And show ID to DB. (Extract infomation from first table and compare the result with second table.)

### website functionality

* [x] Flask powered HTML + JavaScripts. (php is given up)
* [ ] Generalize HTML content by Flask.
* [x] Variable uploaded as a xml file or json file.
* [ ] Generalize uploaded xml file by Flask.
* [ ] Publish the website to innernet.


## Backups
### Connection database by python
Status : Success.
Failed to install instantclient at macOS (M1). -> Use lxplus to do it.

```
#!/usr/bin/env sh
# install cx_Oracle py pip
pip3 install cx_Oracle
```
```
#!/usr/bin/env python3
import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir="Downloaded/instantclient_19_8")
conn = cx_Oracle.connect(                                                                                                         
        user="CMS_HGC_PRTTYPE_HGCAL_READER",
        password="HGCAL_Reader_2016",
        dsn="localhost:10132/int2r_lb.cern.ch",
        encoding="UTF-8",
        )  
```

Useful links:
[How to use cx_Oracle](https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html)
[Download page of instantclient](https://www.oracle.com/database/technologies/instant-client/downloads.html)


# Access to UCSB DB GUI
## installation
* Install PyQt5
* install github packages
* Link downloaded github path to PYTHONPATH
## run

## Problems
* [ ] Not able to login! No responsed after password filled. ![](https://i.imgur.com/k6iBaXF.png)
* [ ] getsockname failed: Bad file descriptor: GUI opened but no password asked. ![](https://i.imgur.com/sz91CGX.png)
* [ ] New PCB entry created but no further action found. ![](https://i.imgur.com/Izbkur9.png)



## Questions
* [ ] KIT is not listed in **[Manufacturer ID](https://cms-shipment.web.cern.ch/manufacturers/)**
    * [ ] Is there any reason do this?
###### tags: `HGCal`,`database`