====== Database Handler ======

A Log-Handler subclass to write log-events into a database. This Handler uses the //[[code:open-source:hse-logger|HSE-DB]]// library and the //DB-CONNECTOR// DQMH-module as a general backend to communicate with several different databases.


===== Installation =====

Copy Database Handler source code from the [[https://code.hampel-soft.com/commercial/hse-logger-db-handler|repository]] into the project or use the [[organization:code:releases:log-db-handler|latest Log DB Handler release]]
To create a table with the correct schema, you can use the SQL-files ''Create Table (MySQL_MariaDB).sql'' or ''Create Table (SQLite).sql'' in the ''Documentation'' folder. For other DB-system, you may have to adapt the SQL-files.

//SQL to create an appropriate table in MySQL or MariaDB//
<code sql>
CREATE TABLE IF NOT EXISTS `log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `logger_name` varchar(50) NOT NULL DEFAULT 'NA',
  `time` timestamp(3) NOT NULL,
  `level` enum('DEBUG','INFO','WARNING','ERROR','CRITICAL') NOT NULL,
  `source` varchar(250) NOT NULL,
  `message` varchar(5000) NOT NULL DEFAULT '''',
  `error_code` int(11) NOT NULL DEFAULT 0,
  `error_message` varchar(1000) NOT NULL DEFAULT '''',
  PRIMARY KEY (`id`),
  KEY `logger_name` (`logger_name`),
  KEY `level` (`level`),
  KEY `error_code` (`error_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='HSE-Logger DB-Handler content.';
</code>

//SQL to create an appropriate table in SQLite//
<code sql>
CREATE TABLE "log" (
  "id"	INTEGER,
  "logger_name" TEXT,
  "time" TEXT,
  "level" TEXT,
  "source" TEXT,
  "message" TEXT,
  "error_code" INTEGER,
  "error_message" INTEGER,
  PRIMARY KEY("id" AUTOINCREMENT)
);
</code>


===== Configuration =====

A valid ''DB_CONNECTOR.ini'' configuration file for the [[code:open-source:hse-db:|HSE DQMH DB Connector module]] must be provided. Usually it is provided in the [[code:dqmh:hse-application-template:configuration#unit_folders|configuration unit folder.]] Please see the section about ''Alternative Paths'' for deploying on an RT system.


=== User Authentication ===

For MySQL and MariaDB, we are using our open driver [[code:open-source:mysqlnetcom|MySQLNetCom]]. Because this driver doesn't yet support the latest authentication methods, make sure that the user has //mysql_native_password// access. You can enable //mysql_native_password// authentication in the DBMS configuration or with the following SQL query.

    CREATE USER 'user'@'%'
    IDENTIFIED WITH mysql_native_password
    BY 'password';

See https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password 


===== Usage =====

Use the method ''Create DB-Handler.vi'' to instantiate a new DB-Handler instance. Additional to the default terminals ''Name'' and ''Level'', the create method only needs the right configuration section in the ''DB-CONNECTOR.ini'', the db-table name to store the log-events in and a database-object for the DBMS in-use.
 
{{:code:commercial:hse-logger_extensions:create_db-handler.png|}}

===== Grafana =====

  * [[kb:iot:grafana#hse-logger_dashboard|How to use the Database Handler as a data source for Grafana]]. 

===== Encryption =====
  * follow these steps to create a [[code:open-source:hse-db:faq#how_can_i_encrypt_my_credentials_in_the_config_file|DB_Connector capable of reading encrypted credentials]]
  * create a child class of DB-Handler
  * create a wrapper for starting a database handler which uses a DB_Connector with the DB Interface class, which can read encrypted credentials

{{:code:commercial:hse-logger_extensions:pasted:20230714-081331.png}}
{{:code:commercial:hse-logger_extensions:pasted:20230714-081535.png}}