Home
- Introduction
- Overview of the database
- Functions
- Object classes
- Object types
- Objects
- Measurements
- Display formats
- Object relationships
- Networks
- Images
- Coordinates
- Display groups
- List of devices grouped by function
The Helium Management (HM) project is concerned with developing software and hardware for the management of helium for research facilities. The project was started by the Helmholtz-Zentrum Berlin (HZB) and is now a collaboration of the HZB, the Institut Laue Langevin (ILL), the ISIS Neutron and Muon Source and the European Spallation Source (ESS) under the umbrella of the International Society for Sample Environment (ISSE).
Helium is becoming increasingly rare and expensive. Therefore, smart management is needed in order to minimise losses, track expenses, and localise and fix leaks.
The HM software and hardware developed at the HZB allow for the tracking of all inputs and outputs of the helium supply system, helium usage at individual experiments, helium levels in all types of vessels or cryostats, and the flow of helium gas in recovery lines. Moreover, it allows for detecting overpressure in vessels and detecting warm/forgotten vessels.
The data is collected by various independent measurement devices and programs and fed into the Gas Management database (GAM database). For a complete HM system the following parts are needed:
- The GAM database is the backbone of the HM. The database stores the information to all of the measurements taken from devices, as well as information about every device, their model, type and display properties. It was developed by the HZB and currently runs on Oracle and MySQL.
- A balance is needed to book-in the helium vessels when they arrive to the facility (normally full) and to book-out the vessels when they leave the facility. The events of booking -in and -out have to be written to the GAM database for example by a software directly controlling the balance.
- A book-in/out software is also needed to monitor the gas inputs to the system (gas cylinders/bundles) and in most cases to monitor the the bundles for compressed (warm) helium sent to the liquefaction plant.
- All wireless devices such as level meters and gas counter monitors are connected to the network of base stations (coordinator network) based on XBee modules (IEEE 802.15.4 protocol). As the wireless level meters are mobile, the coordinator network is used to locate them in the facility.
- All data traffic in the coordinator network is organized and collected by the Helium Management Program (HMP) developed by HZB. In addition, the HMP offers a set of tools for interacting with the GAM database such as creating objects or object types (see below), changing parameters or creating or ending object relations (see below).
- The data of the fixed installation which is individual to a facility, such as the contamination of the helium gas, filling levels of helium bladders, the pressures in the helium gas storage tanks or the respective temperatures, has to be collected by software individual to each facility and then written to the GAM database.
The following document gives a complete introduction to the GAM database, its structure and functionality.
The latest version of the database is written in MySQL and runs under MySQL version 8.0.19 .
The main table in the database is the GAM_OBJECT table. This table represents an object, which is an individual piece of equipment used for helium management: an individual level meter, an individual cryostat, and so on.
Objects are classified into object types, which are themselves classified into object classes, which are classified into functions. There are many attributes in the GAM_OBJECT table, and many have their exact meaning determined by the class the object belongs to.
For each object, the database can store measurements whose meaning is again defined by the object class. Each object can have its own display format, which specifies the levels at which alarms are activated for the values in the measurements.
In addition, the database stores relationships between individual devices, which are used for the graphical representation of the network on the web client.
Finally, the database has the GAM_COORDINATE, GAM_DISPLAYGROUP, GAM_IMAGE and GAM_NETWORK tables, which provide information used for the graphical UI representation of the helium management system.
Below you can see the Entity Relationship Diagram of the database:
The GAM_FUNCTION table represents the highest classification of objects, and it is based on the overall function or purpose that they fulfil.
As an example, storage represents a function, and all dewars, cryostats, gas bladders and so on belong to this category, as they all fulfil the same general purpose.
Functions are further divided into classes, so between the GAM_FUNCTION and GAM_OBJECTCLASS tables there is a one to many relationship with total participation, each function has one or more classes.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| OF_ID | ID of function | 24-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| OF_NAME | Name of the function | String, up to 50 bytes in size | No | |
| OF_COMMENT | Comments related to this function | String, up to 1000 bytes in size | No |
While functions represent the overall purpose a device fulfils, the class represents general type of the device, irrespective of the manufacturer of said device.
Thus, for the storage function, it has the classes: dewar, cryostat, gas bladder, compressed gas storage and gas bundle. For the indicator function, the classes are: pressure sensor, thermometer and contamination sensor. Functions describe the overall purpose of the device, while the class groups all devices of the same type, regardless of the specific model and manufacturer.
Between the GAM_OBJECTCLASS and GAM_FUNCTION tables there is a many to one relationship with total participation, each function has one or more classes and each class must belong to a function.
Between the GAM_OBJECTCLASS and GAM_DISPLAYFORMAT tables there are 5 optional many-to-one relationships. A display format can be applied to multiple classes, and each can have up to 5 display formats.
Classes are further divided into object types, and therefore between the GAM_OBJECTCLASS and GAM_OBJECTTYPE there is a one to many relationship with total participation, each class has one or more object types.
The class of an object is important as it defines the meanings of the measurements taken for that device. A measurement can contain up to 5 values, and the meanings of those five values are defined by five attributes in the class table. These attributes describe what the numbers in the measurement mean. Devices belonging to a certain object class may have no measurements, or may need less than 5 values for a measurement. Therefore, the OC_MEASURETYPEx attributes are optional.
The OC_ICONX attributes, where X is 0, 20, 40, 60 or 80, represent icons that are meant to be used for displaying object of this class depending on their current fill level. The current level of the object would be approximated to 0, 20, 40, 60 or 80 per cent, and the corresponding icon would be used. These attributes are not actually used by the web client made by HZB, but have been left it as an option if other facilities want to use it. These icons are meant to be used for object classes that hold Helium, or for gas counters.
The web client made at the HZB displays floor plans of a facility, with various fixed positions in it. Multiple objects can be located in the same position, and the fixed position itself is also an object. Therefore, the OC_DISPLAYPRIORITY attribute is used to prioritise objects of a certain class so that they are more likely to be displayed on the floor plan. The lower the value of OC_DISPLAYPRIORITY, the higher the display priority, thus the object with the lowest value will be displayed. Positions themselves never overlay anything.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| OC_ID | ID of class | 24-bit Integer | Yes | It is the primary key. |
| OC_FUNCTION_ID | ID of the function of this class | 24-bit Integer | Yes | It is a foreign key to the primary key of the GAM_FUNCTION table. |
| OC_NAME | Name of this class | String, up to 50 bytes in size | Yes | |
| OC_POSITIONTYPE | Defines how objects are displayed on floor plans | Integer, with 2 digits at most | Yes | Says if if they are mobile or not, if they can dragged and dropped, and so on. More information to come later. |
| OC_COMMENT | Comments related to this class | String, up to 1000 bytes in size | No | |
| OC_MEASURETYPE1 | What the first value of a measurement represents, and its units of measurement | String, up to 100 bytes in size | No | |
| OC_MEASURETYPE2 | What the second value of a measurement represents, and its units of measurement | String, up to 100 bytes in size | No | |
| OC_MEASURETYPE3 | What the third value of a measurement represents, and its units of measurement | String, up to 100 bytes in size | No | |
| OC_MEASURETYPE4 | What the fourth value of a measurement represents, and its units of measurement | String, up to 100 bytes in size | No | |
| OC_MEASURETYPE5 | What the fifth value of a measurement represents, and its units of measurement | String, up to 100 bytes in size | No | |
| OC_ICON0 | File path to the icon for representing the device when it is at 0% capacity. | String, up to 500 bytes in size | No | |
| OC_ICON20 | File path to the icon for representing the device when it is at 20% capacity. | String, up to 500 bytes in size | No | |
| OC_ICON40 | File path to the icon for representing the device when it is at 40% capacity. | String, up to 500 bytes in size | No | |
| OC_ICON60 | File path to the icon for representing the device when it is at 60% capacity. | String, up to 500 bytes in size | No | |
| OC_ICON80 | File path to the icon for representing the device when it is at 80% capacity. | String, up to 500 bytes in size | No | |
| OC_ICON_ALARM | File path to the icon for representing the device in an alarm state | String, up to 500 bytes in size | No | |
| OC_DISPLAYPRIORITY | Determines if objects of this class are prioritised when multiple objects are in the same position on a floor plan in the web client | Integer from 0 to 9 | No | Value is 1 for high priority, 2 for medium priority, 3 for low priority, and 0 for when the object is not prioritised. |
| OC_DF_ID_1 | ID of the first display format of this class. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OC_DF_ID_2 | ID of the second display format of this class. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OC_DF_ID_3 | ID of the third display format of this class. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OC_DF_ID_4 | ID of the fourth display format of this class. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OC_DF_ID_5 | ID of the fifth display format of this class. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
As mentioned above, the GAM_OBJECTTYPE table represents the specific model of an object from a specific manufacturer. Therefore, an object that is a Helios 100A dewar is of object type Helios 100A, of object class dewar, of function storage.
There is a many to one relationship with total participation between the GAM_OBJECTTYPE and GAM_OBJECTCLASS, each class has one or more types, and each type must belong to a class.
Between the GAM_OBJECTTYPE and GAM_OBJECT tables there is a one to many relationship with total participation, each object type has one or more objects.
Between the GAM_OBJECTTYPE and GAM_DISPLAYFORMAT tables there are 5 optional many-to-one relationships. A display format can be applied to multiple types, and each can have up to 5 display formats.
A calibration refers to a translation between a measured value and the actual value, defined using a function. This function is captured in the table with the OT_CALIB_X, OT_CALIB_Y and OT_CALIB_NPOINTS attributes.
The optional OT_CALIB_X and OT_CALIB_Y attributes represent arrays of x and y coordinates, respectively. Each pair of values at the same index represent a break-point on the graph of the piecewise linear function between the level and the volume. The OT_CALIB_NPOINTS attribute defines how many elements are in each array.
The x axis of the function represents the measured value, and the y axis represents the actual values. These are generic and can be anything depending on the situation.
One such situations is for objects that store liquids, where we want to measure their level. But these objects are not always perfect cylinders, and as a result the function from the level to the current volume of liquid is not a linear one. For example, if a vessel is more narrow in its middle portion, a 1 cm increase in the level will result in a smaller increase in liquid volume compared to if the 1 cm increase occurred in the wider base of the object.
Another reason you want to have a calibration for helium vessels is that they do not only store liquid Helium, they also store some gas Helium because due to evaporation. Depending on what you want to measure, you need to take this into account.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| OT_ID | ID of type | 24-bit Integer | Yes | It is the primary key. |
| OT_OBJECTCLASS_ID | ID of the class of this type | 24-bit Integer | Yes | It is a foreign key to the GAM_OBJECTCLASS table. |
| OT_NAME | Name of this type | String, up to 50 bytes in size | Yes | |
| OT_OUTOFOPERATION | For things you will not use, you can not delete things from the database? | 0 or 1, o for not out of operation, 1 for out of operation, should not tolerate null | No | |
| OT_VOLUME | Volume of objects of this type | Number with a precision of 10 digits, and a scale of 4 | No | |
| OT_SUBSTANCE | Substance stored or used by objects of this type | String, up to 20 bytes in size | No | |
| OT_COMMENT | Comment regarding this object type | String, up to 1000 bytes in size | No | |
| OT_PRODUCER | The manufacturer of this type of device | String, up to 200 bytes in size | No | |
| OT_MODEL | The model of this type of device | String, up to 200 bytes in size | No | |
| OT_INTERNAL_TCOMP | For gas counters, which have internal temperature compensation | Integer from 0 to 9 | No | For GasCounter
|
| OT_TEMP_NORM | Temp normal, know which temp this is compensating, for gas counters | Number with a precision of 10 digits, and a scale of 4 | No | GasCounter |
| OT_INTERNAL_PCOMP | For gas counters, which have internal pressure compensation | Integer from 0 to 9 | No | For GasCounter
|
| OT_PRESS_NORM | Pressure normal, know which pressure this is compensating, for gas counters | Number with a precision of 10 digits, and a scale of 4 | No | For GasCounter
|
| OT_STEP | Incremental step meant for gas counters, gas counters operate in steps | Number with a precision of 6 digits, and a scale of 4 | No | For GasCounter
|
| OT_CALIB_NPOINTS | The number of calibration points on the defining the calibration function | 24-bit Integer | No | |
| OT_CALIB_X | A string representing the array of coordinates on the x axis of each calibration point | String, up to 1000 bytes in size | No | |
| OT_CALIB_Y | A string representing the array of coordinates on the y axis of each calibration point | String, up to 1000 bytes in size | No | |
| OT_CALIB_NAME | Name you give to your calibration | String, up to 200 bytes in size | No | |
| OT_DF_ID_1 | ID of the first display format of this type. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OT_DF_ID_2 | ID of the second display format of this type. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OT_DF_ID_3 | ID of the third display format of this type. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OT_DF_ID_4 | ID of the fourth display format of this type. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
| OT_DF_ID_5 | ID of the fifth display format of this type. | 24-bit Integer | No | It is a foreign key to the GAM_DISPLAYFORMAT table. |
An object represents the individual instance of a type of device. For example, a row in the GAM_OBJECT table represents one
Helios 100A dewar.
There are a lot of attributes in the GAM_OBJECT table storing data pertaining to each individual device, and many of these attributes are not used for all types of devices, and some attributes have different meanings based on the type of device. The meaning and use of the optional attributes in the object table is determined by the class of the object.
There is a many to one relationship between the GAM_OBJECT and GAM_OBJECTTYPE tables, with total participation on the GAM_OBJECT side. Each object needs to belong to a type, and there can multiple objects of the same type.
There are two one to many relationships between the GAM_OBJECT and GAM_OBJECTRELATION tables. Each object can participate in multiple relationships, or none. The GAM_OBJECTRELATION table represents a relationship between two objects.
There is a one to many relationship between the GAM_OBJECT and GAM_MEASUREMENT tables, each object can have multiple measurements, or none at all. Each measurement must belong to an object.
There is a many to one relationship between the GAM_OBJECT and GAM_NETWORK tables. Each network has multiple objects, and an object can belong to one and only one network, but does not have to.
There is a one to many relationship between the GAM_OBJECT and the GAM_COORDINATE tables. An object can have multiple coordinates, or none at all. Each coordinate must belong to one object.
There is a many to one relationship between the GAM_OBJECT and GAM_DISPLAYGROUP tables. Each display group must have one or more objects, and an object may or may not belong to a display group.
Between the GAM_OBJECT and GAM_DISPLAYFORMAT tables there are 5 optional many-to-one relationships. A display format can be applied to multiple objects, and each object can have up to 5 display formats.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| OB_ID | ID of object | 24-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| OB_OBJECTTYPE_ID | ID of the type of this object | 24-bit Integer | Yes | It is a foreign key to the GAM_OBJECTTYPE table |
| OB_NAME | Name of object | String, up to 50 bytes in size | Yes | |
| OB_ADDRESS | The XBee Address (or IP for LAN Gascount. Mod.) | String, up to 20 bytes in size | No | |
| OB_COMMENT | Comments regarding this object | String, up to 1000 bytes in size | No | |
| OB_POSINFORMATION | Physical position of the device | String, up to 1000 bytes in size | No | e.g. "Cryogenic Lab Upstairs" |
| OB_STATUS | ? | Integer from 0 to 255 | No | Is this related to what measurements it sent to the base stations? |
| OB_LASTTIMEACTIVE | Last time this device was active | String showing date in YYYY-MM-DD hh:mm:ss format | No | Active = sent a measurement? |
| OB_ACTIVE | Whether the device is active | Integer from 0 to 9 | No |
Vessel: is on campus/booked-in |
| OB_IP | The IP address of the device | String, up to 20 bytes in size | No | |
| OB_VOLUME | Vessel Type litre volume | Decimal number, with a precision of 10 and a scale of 4 | No | |
| OB_VALUE | ? | Decimal number, with a precision of 10 and a scale of 4 | No | |
| OB_MINVALUE | Device type-specific minimum value | Decimal number, with a precision of 10 and a scale of 4 | No |
Vessel resistance (ohm), Level Meter battery voltage (volt)
|
| OB_MAXVALUE | Device type-specific maximum value | Decimal number, with a precision of 10 and a scale of 4 | No |
Vessel resistance (ohm), Level Meter battery voltage (volt)
|
| OB_CRITVALUE | Device Critical value | Decimal number, with a precision of 10 and a scale of 4 | No |
Vessel AutoFill Min. Level %, Level Meter Critical Battery Capacity %
|
| OB_COMPORT | Device COM Port | String, up to 10 bytes in size | No | For Coordinator
|
| OB_TARE | Tare/unladen weight of the vessel in kg | Decimal number, with a precision of 7 and a scale of 3 | No | |
| OB_ENABLED1 | Object class-specific setting #1 (see notes) | Integer, from 0 to 9 | No |
ILM Module, Software Level Device: Channel 1 on/off,Position: "Display on floorplan for drag&drop" |
| OB_SUBSTANCE1 | Object substance #1 (see notes) | String, up to 20 bytes in size | No |
ILM Module, Software Level Device: Channel 1 Gas |
| OB_SPAN1 | Object class-specific span #1 (see notes) | Decimal number, with a precision of 10 and a scale of 4 | No |
ILM Module: Channel 1 Span |
| OB_ZERO1 | Object class-specific zero #1 (see notes) | Decimal number, with a precision of 10 and a scale of 4 | No |
ILM Module: Channel 1 Zero |
| OB_ENABLED2 | Object class-specific setting #2 (see notes) | Integer, from 0 to 9 | No |
ILM Module, Software Level Device: Channel 2 on/off,Vessel: "Level Meter Properties are valid",Position: "Include in levelmeter position list" |
| OB_SUBSTANCE2 | Object substance #2 (see notes) | String, up to 20 bytes in size | No |
ILM Module, Software Level Device: Channel 2 Gas |
| OB_SPAN2 | Object class-specific span #2 (see notes) | Decimal number, with a precision of 10 and a scale of 4 | No |
ILM Module: Channel 2 Span |
| OB_ZERO2 | Object class-specific zero #2 (see notes) | Decimal number, with a precision of 10 and a scale of 4 | No |
ILM Module: Channel 2 Zero |
| OB_ENABLED3 | Object class-specific setting #3 (see notes) | Integer, from 0 to 9 | No |
ILM Module, Software Level Device: Channel 3 on/off, Vessel: "Display reversed" |
| OB_SUBSTANCE3 | Object substance #3 (see notes) | String, up to 20 bytes in size | No |
ILM Module, Software Level Device: Channel 3 Gas |
| OB_SPAN3 | Object class-specific span #3 (see notes) | Decimal number, with a precision of 10 and a scale of 4 | No |
ILM Module: Channel 3 Span |
| OB_ZERO3 | Object class-specific zero #3 (see notes) | Decimal number, with a precision of 10 and a scale of 4 | No |
ILM Module: Channel 3 Zero |
| OB_SHORTINTERVAL | Vessel short interval in seconds | 32-bit Integer | No |
Vessel: Minimum 30 seconds, Maximum 10 minutes |
| OB_LONGINTERVAL | Vessel long interval in minutes | 32-bit Integer | No |
Vessel: Minimum 10 minutes, Maximum 24 hours |
| OB_QUENCHTIME | Vessel quench time in milliseconds | 32-bit Integer | No | For Vessel
|
| OB_QUENCHCURRENT | Vessel quench current in milliamperes (mA) | 32-bit Integer | No | For Vessel
|
| OB_WAITTIME | Vessel wait time in milliseconds | 32-bit Integer | No | For Vessel
|
| OB_MEASCURRENT | Vessel measurement current in milliamperes (mA) | 32-bit Integer | No | For Vessel
|
| OB_ADCLOOP | Vessel number of cycles (ADC loop) | 32-bit Integer | No | For Vessel
|
| OB_FILLTIMEOUT | Vessel fill timeout in minutes | 32-bit Integer | No | For Vessel
|
| OB_CELLCOUNT | ? | Integer from 0 to 9 | No | |
| OB_INSTALLED | Date this device was created | String showing date in YYYY-MM-DD hh:mm:ss format | No | |
| OB_SERNO | Serial number of this device | String, up to 50 bytes in size | No | |
| OB_OFFSET_VALUE | The offset value for measurements | Decimal number, with a precision of 10 and a scale of 3 | No | It is used for gas counter modules, so that when a new gas counter module connects to its gas counter, it takes into account the amount of gas measured before the gas counter was installed. |
| OB_OFFSET_VOLUME | The offset volume for when a gas counter is replaced | Decimal number, with a precision of 10 and a scale of 3 | No | It is only used for a gas counter module when its gas counter is replaced by a newer one. The offset makes sure that the measurement continues on from the last value of the old gas counter. |
| OB_OFFSET_CORRVOLUME | The offset corrected volume for when a gas counter is replaced | Decimal number, with a precision of 10 and a scale of 3 | No | It is only used for positions where a gas counter is replaced. The offset is similar to OB_OFFSET_VOLUME, but is instead for volume corrected to take into account pressure and temperature. |
| OB_SEND_DELTA_V | ? | Decimal number, with a precision of 10 and a scale of 3 | No | |
| OB_SEND_DELTA_P | ? | Integer with up to 4 digits | No | |
| OB_DISPLAYGROUP_ID | ID of the display group this object belongs to | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_DISPLAYGROUP table |
| OB_NW_ID | ID of the network this object is on | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_NETWORK table |
| OB_ALIASNAME | Object alias name | String, up to 10 bytes in size | No | For GasCounter, Position, ...? |
| OB_DF_ID_1 | ID of the first display format of this object. | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_DISPLAYFORMAT table. |
| OB_DF_ID_2 | ID of the second display format of this object. | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_DISPLAYFORMAT table. |
| OB_DF_ID_3 | ID of the third display format of this object. | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_DISPLAYFORMAT table. |
| OB_DF_ID_4 | ID of the fourth display format of this object. | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_DISPLAYFORMAT table. |
| OB_DF_ID_5 | ID of the fifth display format of this object. | 24-bit Integer | No | It is a foreign key to the primary key of the GAM_DISPLAYFORMAT table. |
| OB_ENDOFOPERATION | Date when this object is no longer being used | String showing date in YYYY-MM-DD hh:mm:ss format | No |
The GAM_MEASUREMENT table stores information received through the base stations from the various devices. Each row in this table represents a measurement taken at a certain point in time from a certain device. The data can come from the scales that communicate with the dewar balance program, the level meters, cryostats and gas counters communicating with the Wireless Network program and also from the gas storage devices that communicate with the Gas Storage program.
There is a many to one relationship between the GAM_MEASUREMENT and the GAM_OBJECT table. Each measurement must belong to one device, but devices do not need to have measurements.
A measurement can store up to 5 different values, in the MEA_VALUEx attribute, where x is between 1 and 5. The meaning of each of the 5 numbers in a measurement is not stored in this table, it is defined by the object class of the object type of the object to which the measurement belongs. The OC_MEASURETYPEx fields in the GAM_OBJECTCLASS table should store the names and units of the parameter being measured in the MEA_VALUEx attributes. Measurements do not have to make use of all 5 values and can store only one value.
One of the possible sources of measurements is Balance program. This is the server software that handles the book-in and book-out of vessels or dewars and the management of vessels and their types and properties.
When dewars are brought to the facility, they are weighed and the scale sends the measurement to the server. This process is the book-in and is done so that you know how much liquid Helium enters your system. When the dewars have been used and are sent back to be filled with Helium, they are weighed again. This is called a book-out and is done to know how Helium leaves the system with the dewar. These processes are the only ways Helium enters or leaves the system, except leaks.
Since the weight sent by the scale represents a measurement, we need to know which of these measurements were taken for a book-in and which were taken for a book-out. This is what the MEA_BOOKINGCODE attribute is for. It has a value of 1 is the measurement is for a book-in, a value of 2 if a measurement is for a book-out, and 0 or NULL if this measurement is not for a vessel's balance and thus has needs no booking code. This attribute is not used for measurements that do not come from the balance program. The MEA_BOOKINGCODE attribute is also used for the the book-in/book-out process for gas bundles.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| MEA_ID | ID of measurement | 32-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| MEA_OBJECT_ID | ID of the object from which this measurement comes from | 24-bit Integer | Yes | It is a foreign key to the primary key of the GAM_OBJECT table. |
| MEA_DATE | Date when the measurement was taken | String showing date in YYYY-MM-DD hh:mm:ss format | Yes | |
| MEA_DATE2 | ? | String showing date in YYYY-MM-DD hh:mm:ss format | No | |
| MEA_STATUS | ? | Integer with up to 3 digits | No | |
| MEA_COMMENT | Comments regarding this measurement | String, with up to 1000 bytes in size | No | |
| MEA_VALUE1 | First value of this measurement | Decimal number, with a precision of 12 and a scale of 3 | No | |
| MEA_VALUE2 | Second value of this measurement | Decimal number, with a precision of 12 and a scale of 3 | No | |
| MEA_VALUE3 | Third value of this measurement | Decimal number, with a precision of 12 and a scale of 3 | No | |
| MEA_VALUE4 | Fourth value of this measurement | Decimal number, with a precision of 12 and a scale of 3 | No | |
| MEA_VALUE5 | Fifth value of this measurement | Decimal number, with a precision of 12 and a scale of 3 | No | |
| MEA_VALID | ? | Integer, from 0 to 9 | No | |
| MEA_BOOKINGCODE | Code that specifies if the measurements is taken when a dewar enters the system, or leaves it. | Integer, can be 0, 1 or 2. | No | Value is 1 for book-in, 2 for book-out and 0 for measurements not from the balance program. |
The GAM_DISPLAYFORMAT table is used to define the alarm levels for the measured parameters of an object.
We would like to know when the values stored in the GAM_MEASUREMENT table are too low or too high. This is what the alarms are for, an indication for a state of a parameter that they have an undesired value.
This table stores an alarm display format independent of the object, its type or class. For example, different cryostats can have different alarm display formats.
The table has an attribute called DF_ALARMLOW and an attribute called DF_ALARMHIGH. They represent the low and high thresholds for the corresponding parameter specified in the OC_MEASURETYPEx attributes of the GAM_OBJECTCLASS table, where x is a number from 1 to 5 that corresponds to one of 5 display format foreign keys in either the GAM_OBJECT, GAM_OBJECTTYPE or GAM_OBJECTCLASS tables. A display format is defined for one of the five possible measurements devices can have.
When the value of a parameter is measured and it is lower than DF_ALARMLOW, an alarm will be displayed. When the value is measured and it is higher than DF_ALARMHIGH, an alarm will be displayed. The alarms are specified in percentages. In the display format table, lower and upper limits for the possible values of a measurement are defined in DF_UPPERLIMIT and DF_LOWERLIMIT. Based on these limits, the alarm percentage is specified, which corresponds to a measurement value between the lower and upper limtis.
Between the GAM_DISPLAYFORMAT and GAM_OBJECTCLASS tables there are 5 optional one-to-many relationships. A display format can be applied to multiple objects classes, and each class can have up to 5 display formats.
Between the GAM_DISPLAYFORMAT and GAM_OBJECTTYPE tables there are 5 optional one-to-many relationships. A display format can be applied to multiple objects types, and each type can have up to 5 display formats.
Between the GAM_DISPLAYFORMAT and GAM_OBJECT tables there are 5 optional one-to-many relationships. A display format can be applied to multiple objects, and each object can have up to 5 display formats.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| DF_ID | ID of display format | Number ? | Yes | It is the primary key. |
| DF_UPPERLIMIT | The maximum possible value of the measurement | Decimal number, with a precision of 10 and a scale of 4 | No | |
| DF_LOWERLIMIT | The minimum possible value of the measurement | Decimal number, with a precision of 10 and a scale of 4 | No | |
| DF_ALARMHIGH | The percentage corresponding to a value that, if the measurement is greater, means an alarm will be triggered | Decimal number, with a precision of 10 and a scale of 4 | No | |
| DF_ALARMLOW | The percentage corresponding to a value that, if the measurement is smaller, means an alarm will be triggered | Decimal number, with a precision of 10 and a scale of 4 | No | |
| DF_RATETYPE | The type of rate used for calculating flow rate | Integer from 0 to 9 ? | No | Rate type can be per second, per hour, etc. It is only used for gas counters. The value is 0 for no rate, 1 for per second, 2 for per minute, 3 for per hour, 4 for per day, 5 for per month (30.5 days) and 6 for per year. |
| DF_DECIMALPLACES | How many decimal places are displayed for the corresponding measurement | Integer from 0 to 9 ? | No |
The GAM_OBJECTRELATION table is meant to represent the relationship between objects. Here, a relationship between two objects usually refers to objects are connected by the flow of Helium through the system. When Helium moves from one object to another, there is a relationship between the two. The information stored by this table is used for the representation of the system in the web client used at HZB.
Some examples of relationships and the devices connected by them are:
- Vessels are assigned to level meters through relationships
- Level meters are assigned to positions through relationships
- Level meters are assigned to base stations through relationships
- Base stations are assigned to positions through relationships
- Vessels are assigned to balance devices using relationships
Some objects such as positions or base stations are most of the time static, while others such as level meters and vessels are not. This reflects on the status of relationships, which can be permanent or temporary. When a vessel moves around, a relationship between the level meter and a coordinator is ended and a new relationship is made with the coordinator that is now the closest.
In order for a balance measurement to be made and thus for a vessels to be checked in or out, a relationships needs to be created between the vessel and the balance device. This relationship is only active while the vessel is being weighed, and as a result it ends in only a couple of seconds.
The OR_OUTFLOW attribute is used for relationships between a position and any another object except coordinators. It is used to specify if the object drains or feeds the system with Helium when connected to a position. The value of it is 0 for feeding the system, and 1 for draining the system. This attribute can also impact how the object connected to the position is displayed. For relationships between coordinators and positions, this attribute will always be NULL.
The booking request attribute is used to coordinate various programs that make up the Helium Management Program.
When you book in a gas bottle, the measurement program needs to tell the bottle program it is booked in. The relationship in this case is between the bottle and the position.
This attribute is usually only used for relationships between bottles and positions, but could be used for relationships between vessels and balance devices.
Primary relationships are relationships between coordinators and positions whose position is the default position of the coordinate to be given to a vessel. There is at most one primary relationship for each coordinator. A relationship is primary if it is specified in the OR_PRIMARY attribute. This attribute is only used for relationships between coordinators and positions.
There are two many to one relationships between this table and the GAM_OBJECT table, each relationship must be between two object, but objects do not have to participate in a relationship. These two relationships are represented by two foreign keys.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| OR_ID | ID of relationship | 32-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| OR_PRIMARY | Says whether the relationship is a primary relationship. | Integer from 0 to 9 | No | Only used for relationships between coordinators and positions. It is 1 for a primary relationship, and NULL otherwise. |
| OR_OBJECT_ID | ID of one of the objects in the relationship | 24-bit integer | Yes | It is a foreign key to the primary key of the GAM_OBJECT table. |
| OR_OBJECT_ID_ASSIGNED | ID of the other objects in the relationship | 24-bit integer | Yes | It is a foreign key to the primary key of the GAM_OBJECT table. |
| OR_DATE_ASSIGNMENT | Date this relationship was created | String showing date in YYYY-MM-DD hh:mm:ss format | Yes | |
| OR_DATE_REMOVAl | Date this relationship was ended | String showing date in YYYY-MM-DD hh:mm:ss format | No | |
| OR_OUTFLOW | Code that specifies if the object in a relationship with a position feeds or drains the system | Integer can be 1 or 2 | No | It is 0 for an object that feeds the system, 1 for an object that drains the system |
| OR_BOOKINGREQUEST | Code that specifies the status of the booking request | Integer from 0 to 9 | No | It is 0 for NULL, 1 for asking to be booked in, 2 for booked in, 3 asking to booked out, 4 for booked out |
The GAM_NETWORK table stores information about the individual Helium network of a facility.
Here, network refers to an individual physical circuit where liquid and gas Helium circulates and is used. There is usually one network for each location of a facility. For example, at HZB there is one network for the site in Wannsee and there is a separate network for the site in Adlershof.
There is a one to many relationship between the GAM_NETWORK and GAM_OBJECT tables. A network has multiple objects in it, or none at all. An object can be in one network, but does not have to; an object can not be in multiple networks. Usually only positions, gas counters, balance devices and coordinators. Positions, balance devices and base stations must always belong to a network. Vessels do not usually have a network foreign key, but you find out their network by looking at their level meter, then at the meter's position, then at the position's network.
There is a one to many relationship between the GAM_NETWORK and GAM_IMAGE tables. A network is associated with multiple images or floor plans, and cannot have 0 images. Each image must belong to one network.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| NW_ID | ID of network | 24-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| NW_NAME | Name of network | String, up to 50 bytes in size | Yes | |
| NW_COMMENT | Comments regarding this network | String, up to 1000 bytes in size | No | |
| NW_OUTOFOPERATION | ? | Integer from 0 to 9 | Yes | It is 0 by default |
The GAM_IMAGE table stores information about images that represent the floor plans in a facility. A network can extend to multiple stories of a building or to multiple buildings, and therefore a network has multiple floor plans so that users of the software can visualise their devices according to their physical location. The physical location of an object can change, vessels can be moved around for example.
Although images usually display the physical locations of objects, they can also display objects for functional purposes.
Objects are represented on images or floor plans through coordinates, as objects can be present on multiple floor plans. That can happen for vessels that are booked in but it is not known where these vessels will be, so they are displayed on multiple images.
There is a many to one relationship between the GAM_IMAGE and GAM_NETWORK tables, each network is made up of multiple floor plans. Each image must belong to a network.
There is a one to many relationship between the GAM_IMAGE and GAM_COORDINATE tables, as each image/floor plan can have multiple coordinates. Each coordinate must belong to one image.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| IMG_ID | ID of image | 24-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| IMG_BLOB | I assume this is the image for the network ? | Byte string up to 65,638 bytes in size | No | Represents the actual image file of the floor plan |
| IMG_NAME | Name of this floor plan | String, up to 50 bytes in size | Yes | |
| IMG_COMMENT | Comments regarding this floor plan | String, up to 1000 bytes in size | Yes | |
| IMG_DIAMETER | ? | 32-bit Integer | No | |
| IMG_OUTOFOPERATION | ? | Integer from 0 to 9 | Yes | It is 0 by default |
| IMG_NW_ID | ID of the network the floor plan belongs to | 24-bit Integer | Yes | It is a foreign key to the primary key of the GAM_NETWORK table. |
The GAM_COORDINATE table stores information about how objects are displayed on floor plans. It represents the physical coordinates of an object on a floor plan.
The reason there is a separate table for this information is that one object can appear on multiple floor plans. Therefore, this table represents the many-to-many relationship between images/floor plans and objects. There is one coordinate for each object for each image, so one object can only appear once on an image.
There is a many-to-one relationship between the GAM_COORDINATE and GAM_IMAGE tables, each images has multiple coordinates. Each coordinate must belong to an image.
There is a many-to-one relationship between the GAM_COORDINATE and GAM_OBJECT tables, an object can appear on multiple images and thus have multiple coordinates. An object does not need to have any coordinates, but each coordinate must be associated to an object.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| COO_ID | ID of coordinate | 24-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| COO_X | Coordinate on the x axis of the object on the floor plan | Integer with up to 5 digits | No | |
| COO_Y | Coordinate on the y axis of the object on the floor plan | Integer with up to 5 digits | No | |
| COO_OB_ID | ID of the object of this coordinate | 24-bit Integer | Yes | It is a foreign key to the primary key of the GAM_OBJECT table. |
| COO_IMG_ID | ID of the image of this coordinate | 24-bit Integer | Yes | It is a foreign key to the primary key of the GAM_IMAGE table. |
The GAM_DISPLAYGROUP table displays information about groups of objects that should be displayed together. A display group is just a grouping of objects that users would like to see in the same place.
There is one to many relationship between the GAM_DISPLAYGROUP and GAM_OBJECT tables. One display group can have multiple objects, but objects do not need to be in any display group. An object can only be in one display group.
| Attribute name | Attribute meaning | Data type | Mandatory | Notes |
|---|---|---|---|---|
| DG_ID | ID of display group | 24-bit Integer | Yes | It is the primary key. It is automatically incremented, no need to set it manually. |
| DG_NAME | Name of display group | String, up to 50 bytes in size | No | |
| DG_OUTOFOPERATION | ? | Integer from 0 to 9 | No |