History System

• Midas Doc
• Feature Listing
• Application Listing
• Online Database
• FAQ
• Index

Links

• /History ODB tree
• History Page
• MIDAS history file format
• /Logger ODB tree
• /Logger/History subtree
• Image History

Introduction

The history system is an add-on capability built into the MIDAS data logger mlogger to record useful information in parallel to the data logging. This information is recorded with a time stamp and saved into a history file (see History drivers) for later retrieval. One set of files is created per day containing all the requested history events. The history data may be displayed in graphical form using the mhttpd MIDAS webserver utility, giving the user an easy way of seeing how experimental variables have changed with time. The history logging will be in action only if the MIDAS data logger is running, but it is not necessary to have any data logging channel enabled.

Since June 2020, it is possible to also use the midas history system to record images (png/jpeg etc) periodically, for example to later produce a timelapse from a webcam - see the Image History page for more details.

MidasHistoryInterface

The new history configuration defines history channels similar to logger channels. Each history channel creates a history interface class (class MidasHistoryInterface). The data logger can use this interface to write data into the history, mhttpd, mhist & co can use this interface to read data from the history. The interface is defined in history.h

• http://ladd00.triumf.ca/~daqweb/doc/midas/include/history.h

History drivers

Presently, the MIDAS distribution contains these drivers:

MIDAS-History

this is the traditional history driver, with data stored in the .hst binary files. (format is documented here mhformat, see also mhist and mhdump)

By default ODB subtree "MIDAS" is set up to write MIDAS-history.

ODBC-History

stores history data in SQL database using the ODBC abstraction layer. See ODBC SQL History system.

By default ODB subtree "ODBC" is set up to write ODBC-history.

SQLITE-History

stores data in SQL database as SQLITE3 .sqlite3 files. The SQL table format is similar to ODBC-history, performance is similar, no database server required.

By default ODB subtree "SQLITE" is set up to write SQLITE-history.

MYSQL-History

By default ODB subtree "MYSQL" is set up to write MYSQL-history.

FILE-History

stores each variable in its own file, making it much easier to trace the history of a particular variable.

By default ODB subtree FILE is set up to write FILE-history.

More drivers can be easily added by the user (create history_xxx.cxx, add constructor to hs_get_history(), create the ODB subtree /Logger/History/xxx/channel).

Location of History Files

MIDAS-history

By default, the traditional MIDAS-history is enabled, writing history files into the MIDAS data directory path given by the ODB key Data dir.

If history data is important, it is recommended to write it to a directory that is backed up or archived. (The MIDAS data directory is usually located on a scratch data disk without backups).

The location of the MIDAS-history data files can be changed by use of the ODB Key History dir.

ODBC-History

The location of ODBC-History files is ...

SQLITE-History

The location of SQLITE-History files is the directory path given by the ODB key Sqlite dir.

MYSQL-History

The location of MYSQL-History files is ...

FILE-History

The location of FILE-History files is the directory path given by the ODB key History dir.

Types of History Events

There are two basic types of history events, which are defined in different ways:

• "Frontend" (or "Equipment") History event composed in the frontend. See Frontend History Event for details.
• "Virtual" (or "Links") History event composed within the ODB under the /History/Links subtree. See Virtual History Event for details.

Both these history event definitions take effect when the data logger gets a start run transition. Any modifications made during the run are not applied until the start of the next run.

Virtual History Event

The history of any ODB variable can be recorded in a "virtual" history event by creating a link to that variable in the /History/Links subtree. This is used for ODB variables that are not in an Equipment.

To enable Virtual History Events

Create links as desired in /History/Links subtree.

History events are created for each entry under ODB subdirectory /History/Links.

Two types of links are permitted:

ODB key /history/links/aaa is a link to a subdirectory

1. db_watch() is setup to watch this subdirectory
2. tags are created for each subdirectory entry (1 tag per entry)
3. There is no possibility for naming array elements, so 1 tag per array, regardless of the number of elements.

ODB key /history/links/bbb is a subdirectory with links to ODB values

1. db_watch is setup to watch each link target
2. tags are created for each link (1 tag per link)
3. tag name is the link name (NOT the target name). There is no possibility for naming array elements

Mixing links and subdirectories is not permitted.

The update period of history events created for /history/links is controlled by entries in /history/links periods. Numeric values of periods are same as for a Frontend History event. Numeric value 0 disables the history for a particular event.

See MidasElog#1087 for more information.

Frontend History Event

Each Equipment has the capability to generate "history data".

To enable Frontend History Events

Set the Log history ODB key of each equipment to be logged non-zero. (See Log History parameter). (You may need to restart the frontend.)

This causes the event contents to be copied to the ODB /Equipment/<equipment-name>/Variables subtree for the use of the History system. history events are created by parsing the content of /Equipment/<equipment-name>/variables.

For each equipment, history is controlled by the value of /Equipment/<equipment-name>/common/period as follows:

• 0 = history disabled
• 1 = history is enabled
• >1 = history is enabled, throttled down

The throttling is implemented in log_history()/watch_history() by this algorithm: the very first history event is recorded, then all changed to the data are ignored until "period" seconds has elapsed. Then the next history event will be recorded, and following changes will be ignored until "period" second elapses, and so forth. Period value "1" has special meaning - there is no throttling, all history events are logged.

This information is from MidasElog#1087.

Sequence for frontend History Event

The main steps for the frontend History Event is as follows:

• The user code in the frontend equipment Event Readout routines reads the data, placing it into a MIDAS data bank
• In mfe.c, if the key Log history > 0 (i.e. the history system is enabled for this equipment), this data bank is written into ODB (by update_odb()).
• This ODB write triggers an ODB hot-link into mlogger
• The hot-link calls mlogger.c::log_history(), which calls hs_write() to write the data into the history file. The frequency of the history writes is specified by the number of seconds between writes stored in the ODB key Log history.
• history.c::hs_write() or history_odbc::hs_write_odbc() writes the data into a history file or into an SQL database.

How to Write History events

• Enable desired Frontend and/or Virtual history events
• Select the history driver
• Follow instructions below for selected driver:
  • Write MIDAS-history events
  • Write ODBC-history events
  • Write SQLITE-history events
  • Write MYSQL-history events
  • Write FILE-history events

Select the history driver

• Select the history driver and therefore the history-logging-channel to use to write the MIDAS-history files.
• set Active ODB key in the selected history-logging-channel to "y". All other logging channels should be disabled.
• set LoggerHistoryChannel ODB key to the selected History channel.

Write MIDAS-History events

• Enable desired Frontend and/or Virtual history events
• Perform steps in Select History Driver with history driver "MIDAS".
• Restart mhttpd and mlogger, start the run.

MIDAS-history files will be saved into the MIDAS-history data directory.

Write ODBC-History events

See MIDAS ODBC SQL History system.

• If necessary, set up a MySQL database.
• Enable desired virtual and frontend history events (see Virtual history events and Frontend history events).
• Perform steps in Select History Driver with history driver "ODBC".
• Check ODB Key Writer_ODBC_DSN is set to "history writer" and Key Reader_ODBC_DSN is set to "history reader" (the default values)
• set Debug to 1 if desired
• restart mlogger in verbose mode (i.e. mlogger -v), and observe how it issues SQL commands to create the tables and columns corresponding to MIDAS-history events and tags.
• restart mhttpd

Notes

1. mlogger is programmed to raise alarms if connection to SQL database is interrupted or if some events cannot be written into the database (i.e. data type mismatch, SQL syntax errors, etc). The traditional MIDAS-history never raised alarms because it "never failed" - other than from "disk full" errors, which are immediately obvious.
2. With ODBC History enabled, mhttpd will only use history information from the SQL database to make history plots and to extract history variable names for the history plot editor. The ODB

/History subtreesTags and Events are not used.

1. variable names presented to the user may change from MIDAS names to SQL names (use the history panel "Label" text fields to create permanent plot labels).

Write SQLITE-history events

No special configuration required.

• Enable desired virtual and frontend history events (see Virtual history events and Frontend history events).
• Perform steps to Select History Driver with history driver "SQLITE".

• Create a directory for storage of sqlite history files
• save its path in the ODB key Sqlite dir
• restart mhttpd and mlogger

In the history data directory (in Sqlite dir) you should see files with name "mh_xxx.sqlite3" corresponding to your equipment and variable names.

Write MYSQL-history events

• Enable desired virtual and frontend history events (see Virtual history events and Frontend history events).
• Perform steps to Select History Driver with history driver "MYSQL".
• setup the MySQL database:
• install mariadb-10.4

macos:
sudo port install mariadb-10.4
ln -s ln -fs /opt/local/lib/mariadb-10.4/bin/mysql_config ~/bin/
mysql_config --version
10.4.13

• rebuild midas, check the cmake finds the correct mysql: MIDAS: Found MySQL version 10.4.13
• create $HOME/.my.cnf

[mysqld]
#port=3306
socket=/Users/olchansk/mysql/mysql.sock
datadir=/Users/olchansk/mysql/db
pid-file=/Users/olchansk/mysql/mysqld.pid

[client]
#port=3306
socket=/Users/olchansk/mysql/mysql.sock

• mkdir $HOME/mysql
• start mysql:

macos: /opt/local/lib/mariadb-10.4/bin/mysqld

• configure the database: start the "mysql" utility (/opt/local/lib/mariadb-10.4/bin/mysql)

NOTE: instructions thanks to Ben S.
NOTE: use some other passwords instead of the example "reader_password" and "writer_password"

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS history;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> CREATE USER history_reader@'localhost' IDENTIFIED BY 'reader_password';
Query OK, 0 rows affected (0.027 sec)

MariaDB [(none)]> GRANT SELECT ON history.* TO history_reader@'localhost';
Query OK, 0 rows affected (0.016 sec)

MariaDB [(none)]> CREATE USER 'history_writer'@'localhost' IDENTIFIED BY 'writer_password';
Query OK, 0 rows affected (0.014 sec)

MariaDB [(none)]> GRANT SELECT,INSERT,CREATE,ALTER,INDEX ON history.* TO 'history_writer'@'localhost';
Query OK, 0 rows affected (0.017 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

• try connecting to both users:

/opt/local/lib/mariadb-10.4/bin/mysql -u history_reader -p ### should ask for reader password
/opt/local/lib/mariadb-10.4/bin/mysql -u history_writer -p ### should ask for writer password

• create midas config files:
  • mysql_writer.txt

#server=localhost
#port=xxx
database=history
socket=/Users/olchansk/mysql/mysql.sock
user=history_writer
password=writer_password ### change this!!!
#buffer=1000

• • mysql_reader.txt

#server=localhost
#port=xxx
database=history
socket=/Users/olchansk/mysql/mysql.sock
user=history_reader
password=reader_password ### change this!!!

• test MIDAS connection

mh2sql --mysql mysql_writer.txt
(there should be no errors printed)

• if desired, import midas history from .hst files:

mh2sql --mysql mysql_writer.txt *.hst
Reading 130813.hst
[mh2sql,ERROR] [history_schema.cxx:1165:Mysql::Prepare,ERROR] mysql_query(SELECT event_name, table_name, itimestamp FROM _history_index WHERE table_name!=;) error 1146 (Table 'history._history_index' doesn't exist)
[mh2sql,INFO] Adding SQL table "rpcexample", status 311
[mh2sql,INFO] Adding SQL table "rpcexample_1596127680", status 1
[mh2sql,INFO] Adding SQL table "_history_index", status 1
[mh2sql,INFO] Adding column "event_name" to SQL table "_history_index", status 1
...
[mh2sql,INFO] Adding SQL table "run_transitions", status 311
[mh2sql,INFO] Adding SQL table "run_transitions_1596127681", status 1
[mh2sql,INFO] Adding column "state" to SQL table "run_transitions_1596127681", status 1
[mh2sql,INFO] Adding column "run_number" to SQL table "run_transitions_1596127681", status 1
Reading 130815.hst
...
</re>
* enable MYSQL history in mlogger:
** change ODB /Logger/History/MYSQL/Active to "y", "mysql writer" and "mysql reader" to location of these files (leave the default values if these files are in the experiment directory).
* enable MYSQL history in mhttpd:
** change ODB /History/LoggerHistoryChannel to "MYSQL"
* restart mlogger, restart mhttpd
...........

Write  FILE-history events
* Enable desired virtual and frontend history events (see Virtual history events and Frontend history events).
* Perform steps to Select History Driver with history driver "SQLITE".
* Assign directory path for saved files using the ODB key History dir.
...........








History Events
The following information is derived from MidasElog#1087.

A "history event" is a history atomic unit of data. Associated with each history event is a timestamp (unix time),
a name (limited to NAME_LENGTH in the old history) and a list of history tags that describe the individual data
values inside the history event.

When making history plots in mhttpd (see History Page) for each curve on the plot, one selects a history event (from the list
of currently active events, recently active events or the list of all events that ever existed), then from the list of tags
inside the history event one selects the particular variable that will be plotted.

Old MIDAS history events
In the old MIDAS history, all history events are written into one history file (.hst file + optional .def and .idx event definition and time index files
which can be/are regenerated automatically from the .hst file). History events are identified by 16-bit history event IDs, the persistent mapping
from history event names and the 16-bit history event IDs is stored in ODB subtree 
/History/Events. In addition the list of all known history event tags is
stored in ODB subtree 
/History/Tags. For per-equipment history, the 16-bit history event ID is the value of ODB key /Equipment/<equipment-name>/Common/Event ID.

SQL history events
In the SQL history (MySQL, SQLITE, etc), each history event is an SQL table. The history event tags are the SQL table columns.

FILE history events
In the new FILE history, each history event is written into a separate file, tag definition are recorded in text format in the file header, history event
data is appended to the file in binary format (fixed record size). If the history event definition is changed, a new file will be started.

History Event Construction
The mlogger creates #Frontend history events in open_history() by parsing the ODB subtree 
/equipment/<equipment-name>/variables. Each ODB entry under the 
variables/ subtree is referred to as a "variable". Each variable can be a single ODB value, an array of ODB values, or a subdirectory (corresponding to TID_STRUCT structured data banks). As each variable is processed, one or more tags are created to describe it. 

Single ODB values will generally produce a single tag, while arrays can produce (depending whether the array is "Named" or not)
* multiple tags  - one per array element ("Named" array) or
* one single tag - describing the whole array  
 
The code can generate two types of history:

 "per-equipment" history
 will have the tags for all variables concatenated together into one single history event
 "per-variable" history
 will have one history event defined for each variable. Inside could be one tag (for single odb values and unnamed array)  or multiple tags (for named arrays and structured data 
banks).


Per-equipment history is the original MIDAS history implementation. Per-variable history was added to permit efficient data storage in SQL tables. Its initial implementation used 1 ODB hotlink for each variable and it was easy to exceed the maximum permitted number of ODB hotlinks (db_open_record()). To reduce consumption of hotlinks, db_watch() has been implemented and now per-variable history only uses 1 ODB hotlink per equipment.

;NOTE
: With db_watch, per-equipment history is no longer available. Per-variable history is the new default (and the only option).

MidasElog#1087

Construction of history event tags
Currently,
* single ODB values are treated as arrays of length "1"
* string ODB entries are not permitted

History event tags are constructed in the mlogger add_equipment(). Presently there are some quirks with the generation of history event tags (see  [1]),
e.g. single odb values are treated as arrays of length "1" and  "string" ODB entries are not permitted.

For variables of type TID_KEY (subdirectories, corresponding to TID_STRUCT structured banks), one tag is generated for each subdirectory entry. Tag names for  
/equipment/<equipment-name>/variables/aaa/bbb will be "aaa_bbb" (with an underscore). Subdirectory entries of type TID_KEY and TID_LINK are currently not supported.  


For "Named Arrays", individual tags are generated for each array element. Tag names are taken from the Names array. For empty tag names (empty names array), tags are "aaa_0", "aaa_1", etc (for 
/equipment/<equipment-name>/variables/aaa). 

For "Single Names Arrays", tag names have the variable name appended (with a space), for  
/equipment/<equipment-name>/variables/aaa and an empty names array, tags will be "aaa_0 aaa", "aaa_1 aaa", etc. For a populated names array, the tags will be "name0 aaa", "name1 aaa", etc.

For unnamed arrays and single odb variables (in ODB, single odb variables are arrays of length 1), a single tag is generated.


Named Arrays
Named Arrays have a "Names" entry in 
/equipment/<equipment-name>/settings.

For example, to add names to 
/equipment/<equipment-name>/variables/aaa, create a string array 
/equipment/<equipment-name>/settings/names aaa. The names array should be at least as long as the corresponding data array. Individual entries in the names array can be left blank (tag names will be "aaa_0", "aaa_1", etc). Duplicate tag names are not permitted.

A single "Names" entry can be created to name all arrays in variables with the same names ("Single Names").

Create 
/equipment/<equipment-name>/settings/names and arrays 
/equipment/<equipment-name>/variables/aaa and 
/equipment/<equipment-name>/variables/bbb will have 
history tags "name0 aaa", "name1 aaa", "name0 bbb", "name1 bbb", etc. If "names" are left blank, tag names will be "aaa_0 aaa", "aaa_1 aaa", "bbb_0 bbb", "bbb_1 bbb", etc.

In the mhttpd web server History Page variables viewer, "single name" arrays are displayed in a 2D table.


This information is from [2].



Historical note on MIDAS-History Tag names
Tags are only used for MIDAS-history events. 

The mlogger/mhttpd implementation of /History/Tags has proved troublesome and we are moving away from it.

;During the transition period:
:   Implementations starting with Version 4435 of mlogger and mhttpd (i.e. "new" versions) will now work without /History/Tags. The history tags are read directly from the history files themselves. To run in this mode, set  ODB key /History/DisableTags to "y". Existing /History/Tags subtree will be automatically deleted.

Two downsides to running like this are
* it is slower and tags become non-persistent
* if some frontends have not been running for a while, their variables may vanish from the history panel editor.
    

New-style History Tags
For the above two reasons, using /History/Tags is still recommended.  
However, the format of the tags is now changed to simplify management and reduce ODB size. 

; For NEW implementations
: the key CreateOldTags will not be present. "New-style" history tags will be created automatically in the Tags subtree.


;If you have old-style tags
: mlogger will automatically convert the old tags to this new format (this is why you should make a backup of your ODB).

** using "old" (i.e. pre Version 4435) mlogger with "new" mhttpd is OK: new mhttpd understands both formats of /History/Tags.
** using "old" mhttpd with "new" mlogger is OK provided the ODB key /History/CreateOldTags is set to "y" before starting mlogger.



Old-style History Tags
Prior to versions 4435 of mlogger and mhttpd, old-style history tags were generated.
See
* http://ladd00.triumf.ca/~daqweb/doc/midas-old/html/F_History_logging.html
for details.









MIDAS ODBC SQL History system
This section describes the internal workings of the MIDAS SQL history system.

The SQL history system implements the MIDAS history using a SQL database for data storage instead of flat binary files.

The SQL history code lives in a separate files history.h and history_sql.cxx. The present implementation uses the ODBC API for accessing SQL databases. 
MySQL is fully supported, Pgsql support is partially implemented, support for arbitrary ODBC drivers should be easy to add in history_sql and most SQL operations are done using an interface class.

Preliminary testing with MySQL and PgSQL indicates that disk space requirements are the same for both storage mechanisms using flat binary files or either of the 2 SQL databases. All 3 tested storage mechanisms store the data uncompressed. (Observed gzip-1 compression ratios from CERN-ALPHA history files are 50% or better).

Preliminary testing also indicates that MySQL database is "not too slow" for use by T2K/ND280 slow control system. Using MySQL, performance seems to improve somewhat compared to traditional MIDAS-history because each SQL table is stored into a separate file, compared to everything-in-one-file in traditional MIDAS-history.

Set up a MySQL database
In order to setup a MySQL database, the following is needed:

*    the server hostname and port number
*    database name
*    user name and password.

It is recommended to create 3 different users:

*    a "root" user with full priveleges,
*    a "reader" user with read-only priveleges
*    and a "writer" user with "insert, create table, and add column" priveleges (no permission to drop table, remove columns, etc)

Example
#Setup a MySQL database (RHEL/SL 5)

 ssh root@localhost
 service mysqld start
 /usr/bin/mysql_secure_installation (enter current password for root: press <enter>, then accept  default answer "Y" to all questions)
 mysqladmin -p create history
 using mysql-administrator, create 2 users:
 history_writer, with database "history" schema privileges: select, insert, create, alter, index
 history_reader, with database "history" schema privileges: select

#Setup $HOME/.odbc.ini file:

 cat $HOME/.odbc.ini
 [history_writer]
 Description  = history_writer
 Driver       = /usr/lib64/libmyodbc3.so
 SERVER       = ladd05.triumf.ca
 PORT         =
 Database     = history
 OPTION       = 3
 SOCKET       =
 User         = history_writer
 Password     = ******
 [history_reader]
 Description  = history_reader
 Driver       = /usr/lib64/libmyodbc3.so
 SERVER       = ladd05.triumf.ca
 PORT         =
 Database     = history
 OPTION       = 3
 SOCKET       =
 User         = history_reader
 Password     =


Note: if you are using mhist you will need a separate .odbc.ini file for each user that you have connecting to the database (through MIDAS).

Layout of SQL tables
* One table is created for each history event. Table name is the same as the history event name (as reported by mhdump).
* one column is created for each history tag (history variable). Column name is the same as the tag name (as reported by mhdump). For array tags, one column is created for each array element: array a[3] will produce columns a_0, a_1 and a_2.
* two special columns are always created: _i_time and _t_time are the timestamps in the UNIX-time integer format and in the SQL "time" format. (A priori, it is not clear which timestamp format is more useful for end users. mhttpd uses the _i_time integer time format). Note that this timestamp is the time when mlogger receives the hot-link signal (see above) and it can be a few seconds behind the time when the user placed the data into the MIDAS bank. For high precision (and sub-second time resolution) time stamps, users should generate their own timestamps and include them as part of the data itself.
* a special table called _history_index is created to remember the mapping between SQL names and MIDAS names for event names, variable name and tag names.

Schema Evolution
*    when new data fields are added, new SQL columns are created automatically
*    existing SQL columns are never deleted
*    arrays are expanded automatically, and arrays never shrink.
*    if the type of a data field changes, a conflict may occur with existing SQL columns, for example if previous type was "FLOAT" and new type is "DOUBLE", the existing SQL columns would have the SQL data type "float", too narrow to store new values. mlogger will complain about this. Such conflicts may be resolved by changing the type of the SQL column using SQL tools. In general, SQL names are created from MIDAS names by replacing all non-alphanumerical characters to an underscore "_". If this procedure creates duplicate column names, the column names and made unique by appending them with a random number, i.e. MIDAS names "A+B" and "A-B" will be translated to "A_B" and "A_B_12345" (random integer). True MIDAS names are saved into the _history_index.