Format

This is the plugin required to get meteorological data from MySQL databases. The connection to the database is encrypted (with SSL) and compressed (with zlib) if supported by the server. It supports multiple data schemas on the MySQL database engine, allowing to use the same plugin for several data providers.

Plugin dependencies and compilation

This plugin requires the Mysql C API. This must be installed before attempting to compile the plugin. This can be installed from source and recompiled (for example getting it from MariaDB) or from precompiled binaries for your plateform.

Linux

On Linux, you need to install the libmysqlclient-dev (Debian, Ubuntu) or mysql-devel (RedHat, Centos, Fedora, Suse) package. Then CMake will find it and you'll be able to compile the plugin.

Mac

If you have brew on your system, you can simply install the mysql-connector-c package from brew and then CMake fill find it and you'll be able to compile the plugin.

Otherwise, you can download the MySQL server package (in the archives you can find packages for earlier versions of Macos, for example Mysql 8.0.23 that is the latest version compatible with macOS 10.15 Catalina). Then install and cancel the installation when the installer tries to configure a MySQL server (as this is not needed and it keeps everything that it has installed so far in place). CMake will then be able to find the libmysqlclient that MeteoIO needs to compile the plugin.

Windows

First, download the Mysql installer (yes, you can use the 32 bits version, this only applies to the installer itself). Run the installer and select to install the Mysql server package. When asked to configure the server, skip this step.

In CMake, select the include sub-directory of the Mysql install directory and select the libmysql.dll library within the lib sub-directory. You can then compile the plugin. Please do not forget to copy libmysql.dll as well as libcrypto-1_1-x64.dll and libssl-1_1-x64.dll into the bin sub-directory of MeteoIO before running meteoio_timeseries.

Units

The units are assumed to be the following:

temperatures in celsius
relative humidity in %
pressures in Pa

Keywords

This plugin uses the following keywords:

COORDSYS: coordinate system (see Coords); [Input] and [Output] section
COORDPARAM: extra coordinates parameters (see Coords); [Input] and [Output] section
TIME_ZONE: For [Input] and [Output] sections (Input::TIME_ZONE should describe the timezone of the data in the database while the resulting data will be converted to Output::TIME_ZONE )
MYSQL_HOST: MySQL Host Name (e.g. localhost or 191.168.145.20); [Input] section
MYSQL_DB: MySQL Database (e.g. wwcs); [Input] section
MYSQL_USER: MySQL User Name (e.g. wwcs); [Input] section
MYSQL_PASS: MySQL password; [Input] section
MYSQL_SCHEMA: which database schema to use. Currently supported: WWCS. [Input] section
STATION#: station code for the given number #; [Input] section

Example configuration:

[Input]
COORDSYS  = CH1903
TIME_ZONE = 1
 
METEO     = WWCS
MYSQL_HOST = nesthorn.slf.ch
MYSQL_DB   = wwcs
MYSQL_USER = wwcs
MYSQL_PASS = XXX
MYSQL_PROFILE = WWCS
 
STATION1  = SLF01
STATION2  = WWCS_BAL01
STATION3  = WWCS_LUC

Developers: adding a new schema

A new schema is defined by two queries: one to retrieve the metadata (station's coordinates) and one to retrive the data for a given station and date range. A profile is thus a set of metadata and meteodata queries together with the result parsing specifications (defined as a std::vector<SQL_FIELD> ). In order to add a new profile, simply provide the queries and parsing specifications vectors, define a profile name (add it to the documentation) and attribute the profile to the generic queries and parsing specifications in MYSQLIO::readConfig().

The metadata query and the meteodata queries must be defined as follow:

the query itself is a string with the marker ? used as placeholder for parameters that will be bound when preparing the query;
the parsing of the query result is defined by the parsing specification vector that follows the query. It must provide the MySQL data type as well as a string that describes what MeteoIO should do with the resulting field.

In the parsing specifications, the following field are specialy handled: STATNAME, LAT, LON, ALT, SLOPE, AZI, DATETIME. All other fields will be directly used as filed names in the populated MeteData object (fields not already existing will be created as needed).

The architecture is a little suprising (relying on file-scope variables) in order to avoid exposing <mysql.h> content to the rest of MeteoIO as this file does not wrap its content in a namespace and therefore would pollute everything else with its definitions.