# 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`