ISISDB.cpp

Go to the documentation of this file.

// selogcger.cpp : Defines the entry point for the DLL application.
//

#include "stdafx.h"
#include "isisdb.h"

static const int db_schema_version = 43;

#ifdef _MANAGED
#pragma managed(push, off)
#endif

class DBLock
{
        private:
                static volatile HANDLE db_mutex;

        public:
                DBLock() 
                {
                        static SECURITY_ATTRIBUTES sec_attr = { sizeof(SECURITY_ATTRIBUTES), NULL, FALSE };
                        if (db_mutex == NULL)
                        {
                                db_mutex = CreateMutex(&sec_attr, FALSE, "icpsqlitemutex");
                        }
                        if (db_mutex == NULL)
                        {
                                throw(std::exception("error creating mutex"));
                        }
                        if (WaitForSingleObject(db_mutex, INFINITE) != WAIT_OBJECT_0)
                        {
                                throw(std::exception("error waiting on mutex"));
                        }
                }
                ~DBLock() 
                { 
                        if (ReleaseMutex(db_mutex) == 0)
                        {
                                throw(std::exception("error releasing mutex"));
                        }
                }
};

volatile HANDLE DBLock::db_mutex = NULL;

const char* ISISDB::version()
{
        static char buffer[256];
        sprintf(buffer, "ISISDB schema rev %d using SQLITE %s", db_schema_version, SQLITE_VERSION);
        return buffer;
}

ISISDB::~ISISDB()
{
        DBLock mylock;
        if (m_db != NULL)
        {
                db_list.push_back(m_db);
                m_db = NULL;
        }
}

int ISISDB::closeDatabase()
{
        char last_error[512];
        DBLock mylock;
        if (m_db != NULL)
        {
                int rc = sqlite3_close(m_db);
                m_db = NULL;
                if (rc != SQLITE_OK)
                {
                        _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: SQLITE error code %d from close", rc);
                        throw(std::exception(last_error));
                }
        }
        return 0;
}

const char* ISISDB::db_file = "c:\\data\\selog.sq3";

std::list<sqlite3*> ISISDB::db_list;

#define SQLITE_CHECK_RC(__rc) \
            if (__rc != SQLITE_OK) \
                { \
                        _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: SQLITE error %s at %s:%d", errmsg, __FILE__, __LINE__); \
                        sqlite3_free(errmsg); \
                        closeDatabase(); \
                        throw std::exception(last_error); \
                }

ISISDB::ISISDB(bool integrity_check) : m_db(NULL)
{
        DBLock mylock;
        if (db_list.size() > 0)
        {
                m_db = db_list.front();
                db_list.pop_front();
                return;
        }
        char last_error[512];
        int rc;
        struct stat stat_struct;
        char* errmsg = NULL;
        bool new_file = false;
        if (stat(db_file, &stat_struct) == -1)
        {
                new_file = true;
        }
        m_db = NULL;
        if (sqlite3_threadsafe() == 0)
        {
                throw std::exception("sqlite3 not threadsafe");
        }
        // use sqlite3 multi-thread mode as we never have the same handle used by two threads at the same time
        rc = sqlite3_open_v2(db_file, &m_db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, NULL);
        if (rc != SQLITE_OK)
        {
                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: Error opening database %s: %s", db_file, sqlite3_errmsg(m_db));
                m_db = NULL;
                throw std::exception(last_error);
        }
        rc = sqlite3_busy_timeout(m_db, 7 * 24 * 60 * 60 * 1000);   // wait up to 1 week for database lock...
        if (rc != SQLITE_OK)
        {
                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: Error setting timeout");
                closeDatabase();
                throw std::exception(last_error);
        }
        int pnRow, pnColumn;
        char** pazResult = NULL;
        if (integrity_check)
        {
                rc = sqlite3_get_table(m_db,
//                              "PRAGMA integrity_check;",
                                "PRAGMA quick_check;",
                                &pazResult, &pnRow, &pnColumn, &errmsg); 
                SQLITE_CHECK_RC(rc);
                if (pnRow == 1 && !strcmp(pazResult[0], "integrity_check"))
                {
                        sqlite3_free_table(pazResult);
                }
                else
                {
                        std::string s;
                        for(int i=0; i<pnRow; i++)
                        {
                                s.append(pazResult[i]);
                        }
                        _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: integrity check failed %s", s.c_str());
                        sqlite3_free_table(pazResult);
                        closeDatabase();
                        throw std::exception(last_error);
                }
        }
        rc = sqlite3_exec(m_db, "PRAGMA cache_size = 40000; " 
                                                        // "PRAGMA synchronous = NORMAL; "
                                                        "PRAGMA auto_vacuum = FULL; "
                                                        "PRAGMA journal_mode = WAL; "
                                                        // "PRAGMA journal_mode = PERSIST; "
                                                        "PRAGMA temp_store = MEMORY; ", NULL, NULL, &errmsg);
        SQLITE_CHECK_RC(rc);
        if (!new_file)
        {
                int version = -1;
                rc = sqlite3_get_table(m_db,
                        "SELECT * FROM config WHERE name = 'version';",
                        &pazResult, &pnRow, &pnColumn, &errmsg); 
                if (rc == SQLITE_OK)
                {
                        if (pnRow == 1 && pnColumn == 2)
                        {
                                version = atol(pazResult[pnColumn+1]); // 2nd column, 2nd result row (first result row is column headers)
                        }
                        sqlite3_free_table(pazResult);
                }
                else
                {
                        sqlite3_free(errmsg);
                }
                if (version != db_schema_version)
                {
                        new_file = true;
                }
        }
        char sql_line[512];
        if (new_file)   // remember to increment "db_schema_version" at top of file if you change the table schema
        {
                // remove old stuff
                rc = sqlite3_exec(m_db, 
                                                "DROP TABLE IF EXISTS run; "
                                                "DROP TRIGGER IF EXISTS on_selog; ", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                // now re-create current tables
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS config;"
                        "CREATE TABLE config ("
                                "name TEXT PRIMARY KEY, "
                                "value TEXT );", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, "INSERT INTO config VALUES ('version', %d);", db_schema_version);
                rc = sqlite3_exec(m_db, sql_line, NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS selog;"
                        "CREATE TABLE selog ("
                                "run_number INTEGER NOT NULL, "
                                "source TEXT DEFAULT 'UNKNOWN', "
                                "iso_time TEXT DEFAULT '1970-01-01T00:00:00', "
                                "unix_time INTEGER DEFAULT 0, "
                                "block_name TEXT NOT NULL, "
                                "block_value TEXT NOT NULL, "
                                "is_real INTEGER DEFAULT 0);", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS run_status;"
                        "CREATE TABLE run_status ("
                                "run_number INTEGER NOT NULL, "
                                "source TEXT, "
                                "iso_time TEXT, "
                                "unix_time INTEGER, "
                                "period INTEGER, "
                                "run_status INTEGER, "
                                "is_running INTEGER, "
                                "is_waiting INTEGER, "
                                "good_frames INTEGER, "
                                "raw_frames INTEGER, "
                                "good_uamps REAL, "
                                "raw_uamps REAL, "
                                "monitor1_sum INTEGER, " 
                                "dae_beam_current REAL, " 
                                "total_counts INTEGER, "
                                "count_rate REAL, "
                                "np_ratio REAL );", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS seblocks;"
                        "CREATE TABLE seblocks ("
                                "run_number INTEGER NOT NULL, "
                                "block_name TEXT NOT NULL, "
                                "setpoint_value TEXT DEFAULT '0.0', "
                                "vi_name TEXT DEFAULT '', "
                                "read_control TEXT DEFAULT '', "
                                "set_control TEXT DEFAULT '', "
                                "button_control TEXT DEFAULT '', "
                                "options INTEGER DEFAULT 0, "
                                "nexus_name TEXT DEFAULT '', "
                                "low_limit REAL DEFAULT 0.0, "
                                "high_limit REAL DEFAULT 0.0, "
                                "units TEXT DEFAULT '', "
                                "block_value TEXT DEFAULT '0.0', "
                                "is_real INTEGER DEFAULT 0, "
                                "source TEXT DEFAULT 'UNKNOWN', "
                                "setpoint_time TEXT DEFAULT '1970-01-01T00:00:00', "
                                "value_time TEXT DEFAULT '1970-01-01T00:00:00', "
                                "PRIMARY KEY(run_number, block_name) );", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS seperiods;"
                        "CREATE TABLE seperiods ("
                                "run_number INTEGER NOT NULL, "
                                "period INTEGER, "
                                "iso_time TEXT, "
                                "unix_time INTEGER, "
                                "is_start INTEGER, "
                                "block_name TEXT NOT NULL, "
                                "block_setpoint TEXT, "
                                "block_value TEXT );", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS run_state;"
                        "CREATE TABLE run_state ("
                                "run_number INTEGER NOT NULL, "
                                "is_start INTEGER, "
                                "state TEXT, "
                                "iso_time TEXT, "
                                "unix_time INTEGER );", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TABLE IF EXISTS measurement;"
                        "CREATE TABLE measurement ("
                                "uuid STRING PRIMARY KEY, "
                                "run_number INTEGER, "
                                "iso_time TEXT, "
                                "label STRING );", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
// indexes
                rc = sqlite3_exec(m_db,
                        "DROP INDEX IF EXISTS selog_index; "
                        "CREATE INDEX selog_index ON selog (run_number, block_name); " 
                        "DROP INDEX IF EXISTS seperiods_index; "
                        "CREATE INDEX seperiods_index ON seperiods (run_number, block_name); "
                        "DROP INDEX IF EXISTS run_status_index; "
                        "CREATE INDEX run_status_index ON run_status (run_number); "
                        "DROP INDEX IF EXISTS run_state_index; "
                        "CREATE INDEX run_state_index ON run_state (run_number); ", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
// views
                rc = sqlite3_exec(m_db, 
                        "DROP VIEW IF EXISTS c_period; "
                        "CREATE VIEW c_period AS SELECT value FROM config WHERE name='period'; "
                        "DROP VIEW IF EXISTS c_run_number; "
                        "CREATE VIEW c_run_number AS SELECT value FROM config WHERE name='run_number'; ",
                        NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
// triggers
//              rc = sqlite3_exec(m_db, 
//                      "DROP TRIGGER IF EXISTS on_selog; "
//                      "CREATE TRIGGER on_selog AFTER INSERT ON selog FOR EACH ROW BEGIN "
//                      "UPDATE seblocks SET block_value=NEW.block_value, value_time=NEW.iso_time, is_real=NEW.is_real, source=NEW.source "
//                              "WHERE block_name=NEW.block_name AND run_number=NEW.run_number; "
//                      "END; ", NULL, NULL, &errmsg);
//              SQLITE_CHECK_RC(rc);
                // this trigger creates entries in the new SEBLOCKS table for a new run number by
                // copying from the old run number
                rc = sqlite3_exec(m_db, 
                        "DROP TRIGGER IF EXISTS on_config_update; "
                        "CREATE TRIGGER on_config_update AFTER UPDATE ON config FOR EACH ROW WHEN (NEW.name='run_number') BEGIN "
                           "INSERT OR IGNORE INTO seblocks SELECT NEW.value, block_name, setpoint_value, vi_name, read_control, "
                             "set_control, button_control, options, nexus_name, low_limit, high_limit, units, block_value, "
                             "is_real, source, setpoint_time, value_time FROM seblocks WHERE run_number=OLD.value; "   
                        "END; ", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                // this should only get called on database creation - we may then have a few values logged by SECI that have run_number=-1
                // as get_run_number() would have failed and returned -1
                rc = sqlite3_exec(m_db, 
                        "DROP TRIGGER IF EXISTS on_config_insert; "
                        "CREATE TRIGGER on_config_insert AFTER INSERT ON config FOR EACH ROW WHEN (NEW.name='run_number') BEGIN "
                           "INSERT OR IGNORE INTO seblocks SELECT NEW.value, block_name, setpoint_value, vi_name, read_control, "
                             "set_control, button_control, options, nexus_name, low_limit, high_limit, units, block_value, "
                             "is_real, source, setpoint_time, value_time FROM seblocks WHERE run_number=-1; "   
                        "END; ", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_exec(m_db, 
                        "DROP TRIGGER IF EXISTS on_run_state; "
                        "CREATE TRIGGER on_run_state AFTER INSERT ON run_state FOR EACH ROW BEGIN "
                        "INSERT INTO seperiods SELECT c_run_number.value,c_period.value,NEW.iso_time,NEW.unix_time,NEW.is_start,seblocks.block_name,seblocks.setpoint_value,seblocks.block_value FROM c_run_number,c_period,seblocks WHERE seblocks.run_number=NEW.run_number; "
                        "INSERT INTO selog SELECT c_run_number.value,'TRIGGER',NEW.iso_time,NEW.unix_time,seblocks.block_name,seblocks.block_value,seblocks.is_real FROM c_run_number,seblocks WHERE seblocks.run_number=NEW.run_number; "
                        "END; ", NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
        }
        // create temporary tables and triggers
        // triggers in main database cannot referecen outher databases (e.g. temp)
        // so we need to create a temporary trigger
    // don't think need one on period change (i.e. config table) as covered by run state change as e.g. pause change_period resume
        if (strlen(isisdb_per_session_sql) > 0)
        {
                rc = sqlite3_exec(m_db, isisdb_per_session_sql, NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
        }
//      rc = my_sqlite3_exec(the_database, "BEGIN EXCLUSIVE TRANSACTION; VACUUM; END TRANSACTION;", NULL, NULL, &errmsg);
//      SQLITE_CHECK_RC(rc);
}

Poco::SingletonHolder<SQLITESessionPool> sqlite_session_pool;