SiPM Database

From GlueXWiki
Revision as of 17:49, 5 January 2016 by Elton (Talk | contribs) (Through Terminal)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

MySQL Database At Jefferson Lab

A copy can be found in HallD-ELOG.

MySQL Server

  • hostname: hallddb.jlab.org
  • login: halld
  • password: the normal "halld" password as gluon linux boxes, or ask Elton to get it
  • port: 3306 (MySQL default)
  • database name: halld_sipm

Tables

sipm_spec

The table stores the Hamamatsu specifications for SiPM arrays. List of columns:

  • sn: Serial Number
  • voltage: Average voltage at 25C (V)
  • vol_var: Standard deviation in voltage of individual channels (mV)
  • m_var: Variation of gain (%, from Hamamatsu spec sheet)
  • dark_rate: Total dark rate (MHz)
  • dr_var: Relative standard deviation in dark rate of individual channels (%)
  • module: BCAL module name (01-48U/D)
  • row: Row number on BCAL module (1-10 from inner layer)
  • col: Column number on BCAL module (1-4)
  • pos: Position number on PCB (1-40)
  • bias_channel: Bias voltage channel (1-4)
  • remark: Remarks

SiPM Numbering.png

sipm_spec_tile

The table stores the Hamamatsu specifications for individual 3×3 mm2 cells. List of columns:

  • sn: Serial Number
  • cell: Cell number (1-16)
  • voltage: Voltage of individual channels for a 7.5×105 gain at 25C (V)
  • dark_rate: Dark rate of individual channels (MHz)

sipm_data

The table stores the info of the production test. List of columns:

  • sn: serial number
  • time: date and time the data were taken
  • temperature_a: average temperature reading from sensor A (C), bottom, upstream
  • temperature_b: average temperature reading from sensor B (C), top, downstream
  • filter_1: filter 1 setting
  • filter_2: filter 2 setting
  • frequency: pulse frequency (Hz)
  • position: position of SiPM on the cooling plate (1-1 - 4-4)
  • voltage_channel: channel number of HV power supply (0-15)
  • voltage_25C: operational voltage at 25C given by Hamamatsu (V)
  • voltage_offset: offset on bias voltage (V)
  • voltage_set: set voltage (V)
  • voltage_read: average read voltage (V)
  • current: average current (uA)
  • gate: gate width for QDC (ns)
  • daq_module: V792 module used (4-20)
  • daq_first_channel: V792 channel used for first cell (0 or 16)
  • event: total number of events
  • data_directory: sub-directory on farm work disk: /work/halld/Subsystems/SiPM/production/data/[sub-directory]
  • replayed: whether the data have been replayed (-1 not replayed, 0 submitted to farm, 1 replayed, >1 replayed but will not be used in PDE extraction)
  • remark: remarks

sipm_pde

The table stores the quantities of SiPM arrays extracted from the data: PDE, gain, dark rate and cross-talk. List of columns:

  • sn: serial number
  • temperature: average(targeting) temperature of for the whole set of measurements (C)
  • voltage_offset: targeting bias voltage offset from nominal operational voltage (V)
  • gate: gate width for QDC (ns)
  • voltage: average read voltage (V)
  • dark_rate: average dark rate per cell (MHz)
  • dr_var: dark rate variation
  • pde: average PDE
  • pde_var: PDE variation
  • gain: average gain (×105)
  • gain_var: gain variation
  • x_talk: average probability of cross talk
  • xt_var: cross talk variation
  • remark: remarks

sipm_pde_cell

The table stores the extracted quantities of individual cells. List of columns:

  • sn: serial number
  • temperature: average(targeting) temperature of for the whole set of measurements (C)
  • voltage_offset: targeting bias voltage offset from nominal operational voltage (V)
  • gate: gate width for QDC (ns)
  • dark_rate: dark rate of the cell (MHz)
  • pde: PDE of the cell
  • gain: gain of the cell (×105)
  • x_talk: cross talk of the cell

osoto_sipm_pde

(Temporary name, should be changed)

The table stores the quantities of all SiPM arrays extracted from the data: PDE, gain, dark rate and cross-talk. List of columns:

  • sn: Serial number.
  • temperature: Average(targeting) temperature of for the whole set of measurements (C).
  • temperature_m: Average measured temperature for the set of measurements (C). (for JLAB is equal to temperature)
  • temperature_m_sigma: Standard deviation over mean for temperature. (\sigma /\mu )
  • voltage_over: Targeting bias voltage above breakdown voltage (V). (For UTFSM, UTFSM breakdown voltage estimation used. For JLAB, Hamamatsu breakdown voltage used)
  • position: Position on the measurement setup.
  • module: Module number (1-48) and side (U,D) on BCAL.
  • module_pos: Position into the BCAL module.
  • gate: Gate width for QDC (ns).
  • voltage: Average read voltage (V).
  • voltage_Ham_br: Average breakdown voltage over 16 cells from Hamamatsu (V) at 25 degC (a 0.9 V overvoltage has been subtracted).
  • dark_rate: Average dark rate per cell (MHz). (UTFSM data includes crosstalk)
  • dr_sigma: Standard deviation over mean for dark rate. (\sigma /\mu )
  • pde: Average PDE
  • pde_sigma: Standard deviation over mean for pde. (\sigma /\mu )
  • gain: Average gain (×105)
  • gain_sigma: Standard deviation over mean for gain. (\sigma /\mu )
  • x_talk: Average probability of cross talk
  • xt_sigma: Standard deviation over mean for gain. (\sigma /\mu )
  • remark: 'UTFSM' or 'JLAB' indicating where the measurement were performed.

How-To Access MySQL Databalse

Windows Platform

MySQL Workbench

  1. Install the lastest MySQL Workbench download page.
  2. Start MySQL Workbench, in the SQL Development section, click "New Connection".
  3. Type in a Connection Name at your choice, Hostname and Username, (Optional) set default Schema to halld_sipm and leave everything else unchanged.
  4. Once everything configured, click "Test Connection" to test the configuration, and a new window will prompt you for password, hopefully you will see a success message at the end. Then click "OK"s to close all these windows, and go back to the main window. Now you should now see a new connection available in your connection list.
  5. Double click the newly created connection to bring up the query window.
  6. Select halld_sipm schema if it's now already selected, then double click the table you'd like to access to query it's data.

Microsoft Access

  1. Install 32-bit MySQL ODBC Connector download page. You need 32-bit to fit your 32-bit Microsoft Office Software even if you run a 64-bit operating system.
  2. Create an ODBC database linked to MySQL database in Access.
  3. In MS Access tool bar, select "External Data" tab
  4. In the "Import" section, click "More" and then select "ODBC Database"
  5. In the "Get External Data - ODBC Database" window, choose "Link to the data source by creating a linked table", click "OK"
  6. In the "Select Data Source" window select "Machine Data Source" tab
  7. If you haven't created a MySQL-ODBC data source yet, follow these steps to create one:
    1. Click "New...", acknowledge the following warning if you do not have Administrative privilege.
    2. In the "Create New Data Source" window, select the type of data source preferred, choose "User Data Source" then click "Next>"
    3. Choose "MySQL ODBC 5.x Driver" then click "Next>", then "Finish"
    4. In the "MySQL Connector/ODBC Data Source Configuration" Windows, give a new "Data Source Name", type in halldweb1.jlab.org for "TCP/IP Server", halld for "User" and corresponding password. If everything goes correctly, you should be able to select halld_sipm database in the pull-down menu. Click "OK" to finish the configuration.
  8. Select the Data Source you created for the halld_sipm database, click "OK"
  9. Select the table(s) you want to access then click "OK"
  10. You can now operate the MySQL database through Access

Microsoft Excel

Unlike MySQL Workbench and MS Access, you can only import data to MS Excel but not change the values on the fly. Here are steps to import data from a MySQL database.

  1. You first need to create a ODBC User Data Source in the ODBC Data Source Administrator if you haven't done so.
    1. If you're running a 32-bit Windows, it's your luck day since you can easily find the manager in the Control Panel. If you're unfortunately running a 64-bit Windows, you need to dig more, the one you can find in the Control Panel is not what you want to use since it can only create 64-bit sources. Here is the location for the 32-bit manager: C:\Windows\SysWOW64\odbcad32.exe
    2. Once you open the 32-bit ODBC Data Source Administrator, in the "User DSN" tab, click "Add" to add a new user data source.
    3. Choose "MySQL ODBC 5.x Driver" then click "Next>", then "Finish"
    4. In the "MySQL Connector/ODBC Data Source Configuration" Windows, give a new "Data Source Name", type in halldweb1.jlab.org for "TCP/IP Server", halld for "User" and corresponding password. If everything goes correctly, you should be able to select halld_sipm database in the pull-down menu. Click "OK" to finish the configuration. Now you can exit the manager.
  2. In MS Excel, in the "Data" tab, in the "From Other Sources" pull-down menu select "From Data Connection Wizard"
  3. Choose "ODBC DSN" then click "Next>"
  4. Choose the ODBC data source you created for halld_sipm then click "Next>"
  5. Choose the table you want to import, then click "Next>"
  6. Follow the wizard to finish the rest of the steps.

Linux

Through Terminal

Connect to MySQL server

mysql -h hallddb.jlab.org -u halld -p halld_sipm

Show table list

mysql> SHOW tables;

List columns

mysql> SHOW columns FROM [table];

Query table content

mysql> SELECT [column1,column2,column3...] FROM [table] WHERE [expressions];

for example:

mysql> SELECT sn,date,gate,remark FROM sipm_data WHERE sn=16 AND gate=200 AND remark like 'Uniformity%' AND position like '_-1';

'%' and '_' are wildcards for multiple or single character(s) in mysql

Update entry

mysql> UPDATE [table] SET [column1=value1,column2=value2...] WHERE [expressions];

Delete entry

mysql> DELETE [table] WHERE [expressions];

Disconnect

mysql> EXIT;

Official MySQL Manual

MySQL Database At UTFSM

MySQL Server

  • hostname: atlasusr.fis.utfsm.cl
  • login: royarzun The login will be changed for public access of JLab members
  • password: There will be a public password for jlab
  • port: 3306 (MySQL default)
  • database name: MPPCTest

Tables

Stage 1: Shipping and Receiving - Test procedures

Stage1_mppc

The table stores the state of the MPPC out of the box for the stage 1 of testing at UTFSM. List of columns:

  • id_mppc: Hamamatsu Serial Number
  • voltage: Hamamatsu average operational voltage [V] at 25[^{o}C] and gain 7.5E+05
  • Lot: Internal MPPC UTFSM Lot indicates the group of MPPCs tested together in stage 3 (or USMLOT)
  • HM_Lot: MPPC Hamamatsu Lot shipment number
  • Rev_date: MPPC receiving date (datetime)
  • Rev_obs: Remarks on MPPC at the receiving stage
  • Rev_state: MPPC acceptance condition in the receiving stage (Boolean) [Yes/No]

Stage1_stage1

Upon receipt of shipments, there will be an initial visual inspection of each MPPC array. List of columns: Note: Stage1_stage1 tables were not included in the dump we received. New program should include the missing data

  • id_mppc: Hamamatsu Serial Number
  • obs: General observations recorded by the operator
  • PhotoObs1: MPPC photo sensitive area test 1. Units mm2?
  • photo_sensitive1: MPPC photo sensitive area test 1 acceptance.Determine whether the photo-sensitive area is greater than 140 mm^{2}. [Yes/No]
  • PhotoObs2: MPPC photo sensitive area test 2 Units mm2?
  • photo_sensitive2: MPPC photo sensitive area test 2 acceptance [Yes/No]
  • mechanical_test: MPPC mechanical test acceptance [Yes/No]
  • pin_test1: MPPC pin test 1 acceptance.Determine whether the output connector pins have the correct spacing and are placed in the correct location. [Yes/No]
  • pin_test2: MPPC pin test 2 acceptance. Evaluate whether there is or is not visible mechanical damage or an abnormal mechanical condition.. [Yes/No]
  • pin_test_obs: MPPC pin test remarks.
  • Operator: MPPC test operator (name)
  • OverAllBool: MPPC final acceptance [Yes/No]

Stage1_media

The table stores the stage 1 data files, as well as the documents that verify the MPPC arriving state (generic files: images and documentation, etc). List of columns:

  • id_mppc: Hamamatsu serial number
  • id_media: Internal serial number for the media table
  • type: MPPC media identificator [front picture=0/rear picture=1/document=2]
  • file: The file to be stored in the table [BLOB]
  • description: Media file short description
  • Operator: MPPC test operator

Stage 2: PDE Station

Stage2_pulseshape

The table stores the stage 2, pulse shape station data:

  • id_mppc: Hamamatsu Serial Number
  • Channel: Electronic channel (1-16)
  • Risetime: [ns]
  • Pulse_Width: [ns]
  • Observations

Stage2_stage2

The table stores the stage 2 generated data: Note: The measurements were conducted at a fixed voltage above breakdown and the PDE and gain should be independent of temperature.


  • id_stage2: Internal serial number for the stage2 table
  • id_mppc: Hamamatsu Serial Number
  • Lot: MPPC UTFSM Lot (or USMLOT) [the shipment lot for MPPC]
  • Test_Date: MPPC stage 2 test date
  • Operator: MPPC test operator
  • Voltage: Average breakdown (16 cells), determined at USM, +0.9V [V]
  • PDE: Average PDE (16 cells)[fraction]
  • PDE_Var: PDE RMS variation (16 cells) [fraction]
  • Noise: Average Noise excess Factor (16 cells. Used for internal studies only.
  • Noise_Var: Noise excess Factor Variation. This is used for internal studies only
  • BDV: Average breakdown voltage (16 cells) [V]
  • BDV_Var: Breakdown Voltage RMS Variation(16 cells) [V}
  • Gain: Average Gain (16 cells) [electrons]
  • Gain_Var: Gain RMS Variation (16 cells) [electrons]
  • V_Gain: Calculated bias voltage [V] for a gain of 7.5E+05
  • V_Gain_Var: Bias voltage RMS variation between cells in one MPPC array at a gain of 7.5E+05.
  • LogFile: Log file output from the PDE data acquisition machine
  • rootOutput: ROOT file output from the data analysis program (contains clean data from the raw files obtained from the machine)

Stage 3 : Dark Box

Stage3_mppcdata

Stores the data obtained in a single position of the Dark box test. It would be useful to order these entries according to their position in the table (e.g. the position is the next-to-last entry in the table. Also the entry for Run number and type are missing

  • id_mppc: Hamamatsu serial number
  • position: position in the setup
  • Box: Internal id for the table
  • Bunch: Internal id for the MPPC shipment
  • Temp: Set temperature of the test (5,7 or 20) [degC]
  • Date: MPPC stage 3 test date
  • crosstalk: average crosstalk [fraction]
  • crosstalk_err: RMS variation of crosstalk (16 cells)
  • gain: average gain [1E+05 electrons]
  • gain_err: RMS variation of the gain between 16 cells [1E+05 electrons]
  • lambdaf: Average number of fired pixels in response to 505 nm LED [pixels]
  • lambdaf_err: RMS variation in the number of pixels fired for 16 cells in response to 505 nm LED [pixels]
  • OpVoltUSM: Breakdown voltage + 0.9 [V]
  • OpVoltUSM_err: RMS variation of breakdown voltage between 16 cells [V]
  • Temp_M: Average of 16 temperature measurements of the copper plate [deg C]
  • Temp_M_err: RMS deviation of the 16 temperature measurements on the copper plate [deg C]
  • root_output: root blob [root file]

Stage3_mppcdatachannel

Stores the data obtained in a single position of the Dark box test for a particular MPPC cell

  • id_mppc_id: Hamamatsu serial number (references stage3_mppcdata table)
  • channel: MPPC cell (1-16)
  • gain_oper: Gain at OpVoltUSM [1E+05 electrons]
  • gain_oper_channel: Gain at OpVoltUSM [QDC Channel, 100 [fC/channel] ]
  • crosstalk_oper: Crosstalk at OpVoltUSM [fraction]
  • lambdaf: Average number of pixels fired in response to the 505 nm LED </span>
  • OpVoltUSM: Breakdown voltage + 0.9 [V]