selogger.cpp

Go to the documentation of this file.

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

#include "stdafx.h"
#include "selogger.h"
#include "isisdb.h"
#include "epicsdb.h"
#include "icputils.h"

static bool database_debug = (getenv("ICP_DATABASE_DEBUG") != NULL);
static const char* database_debug_file = "c:\\data\\log\\database_debug.txt";


// START: taken from ICPUTILS - should make common
struct MyOverlapped : public OVERLAPPED
{
private:
        MyOverlapped() {}
public:
        overlap_complete_func_t func;
        void* arg;
        MyOverlapped(overlap_complete_func_t func_, void* arg_) : func(func_), arg(arg_)
        {
                memset(this, 0, sizeof(OVERLAPPED));
        }
};

struct my_overlap_t
{
private:
        my_overlap_t() {}
public:
        HANDLE h;
    char* data;
        my_overlap_t(HANDLE h_, const char* data_) : h(h_), data(0) 
        {
                data = strdup(data_);
        }
        ~my_overlap_t()
        {
                free(data);
                if (h != INVALID_HANDLE_VALUE)
                {
                        CloseHandle(h);
                }
        }
};

static void my_overlap(void* arg)
{
        my_overlap_t* ov = (my_overlap_t*)arg;
        delete ov;
}

static HANDLE file_complete = NULL;

static void my_io_complete(void* arg)
{
        DWORD ntrans = 0;
        ULONG_PTR key = 0;
        MyOverlapped* overlapped = 0;
        while(true)
        {
                GetQueuedCompletionStatus(file_complete, &ntrans, &key, (OVERLAPPED**)&overlapped, INFINITE);
                (*(overlapped->func))(overlapped->arg);
                delete overlapped;
        }
}


static HANDLE createAppendFile(const char* filename)
{
        static SECURITY_ATTRIBUTES sec_attr = { sizeof(SECURITY_ATTRIBUTES), NULL, FALSE };
        if (file_complete == NULL)
        {
                file_complete = CreateIoCompletionPort(INVALID_HANDLE_VALUE, NULL, NULL, 1);
                _beginthread(my_io_complete, 0, NULL);
        }
        // need to specify FILE_SHARE_WRITE so other threads in this process can access it etc.
        HANDLE h = CreateFile(filename, FILE_APPEND_DATA, FILE_SHARE_READ | FILE_SHARE_WRITE, 
                        &sec_attr, OPEN_ALWAYS, FILE_ATTRIBUTE_NORMAL | FILE_FLAG_OVERLAPPED, NULL);
        if (h != INVALID_HANDLE_VALUE)
    {
            CreateIoCompletionPort(h, file_complete, 0 ,1);
    }
        return h;
}

static void appendToFileAsync(HANDLE h, const char* message, bool close_after_write)
{
        if (message == NULL)
        {
                return;
        }
        if (h != INVALID_HANDLE_VALUE)
        {
                my_overlap_t* arg;
                if (close_after_write)
                {
                        arg = new my_overlap_t(h, message);
                }
                else
                {
                        arg = new my_overlap_t(INVALID_HANDLE_VALUE, message);
                }
                MyOverlapped* overlapped = new MyOverlapped(my_overlap, arg);
                WriteFile(h, arg->data, strlen(message), NULL, overlapped);
        }
}

static void appendToFileAsync(const char* filename, const char* message)
{
        if (message == NULL)
        {
                return;
        }
        HANDLE h = createAppendFile(filename);
        if (h != INVALID_HANDLE_VALUE)
        {
                appendToFileAsync(h, message, true);
        }
}

// END: taken from ICPUTILS - should make common


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

BOOL APIENTRY DllMain( HMODULE hModule,
                       DWORD  ul_reason_for_call,
                       LPVOID lpReserved
                                         )
{
        switch (ul_reason_for_call)
        {
        case DLL_PROCESS_ATTACH:
        case DLL_THREAD_ATTACH:
        case DLL_THREAD_DETACH:
        case DLL_PROCESS_DETACH:
                break;
        }
    return TRUE;
}

#ifdef _MANAGED
#pragma managed(pop)
#endif

// ISO time of form "2004-11-10T17:04:54"
// return -1 on error
static time_t unixTime(const char* isotime)
{
        int n;
        struct tm tm_struct;
        if (strlen(isotime) < 19)
        {
                return (time_t)-1;
        }
        n = sscanf(isotime, "%d-%d-%dT%d:%d:%d", 
                                &tm_struct.tm_year, &tm_struct.tm_mon, &tm_struct.tm_mday,
                                &tm_struct.tm_hour, &tm_struct.tm_min, &tm_struct.tm_sec);
        
        if (n != 6)  // enginx had " " rather than "T" separating the bits
        {   
                n = sscanf(isotime, "%d-%d-%d %d:%d:%d", 
                                        &tm_struct.tm_year, &tm_struct.tm_mon, &tm_struct.tm_mday,
                                        &tm_struct.tm_hour, &tm_struct.tm_min, &tm_struct.tm_sec);
        }
        if (n != 6)
        {
                return (time_t)-1;
        }
        tm_struct.tm_year -= 1900;      // year field is 1900 based
        tm_struct.tm_mon -= 1;          // January is 0 not 1 in this structure
        tm_struct.tm_isdst = -1;        // status of DST is unknown - CRT will decide
        return mktime(&tm_struct);
}

static int ISOtime(time_t time, char* buffer, int len)
{
        struct tm *tm_struct;
        buffer[0] = '\0';
        tm_struct = localtime(&time);
        if (tm_struct != 0)
        {
                strftime(buffer, len, "%Y-%m-%dT%H:%M:%S", tm_struct);
        }
        else
        {
                strncpy(buffer, "1970-01-01T00:00:00", len);
        }
        buffer[len-1] = '\0';
        return 0;
}

static char last_error[1024];

static volatile LONG num_async_requests = 0;
static volatile uint64_t num_work_iterations = 0;
static volatile HANDLE async_complete_event = NULL;
static CRITICAL_SECTION work_critical;
static volatile HANDLE work_queued = NULL;

struct workpacket
{
private:
        workpacket(const workpacket& w);
        workpacket& operator=(const workpacket& w);

public:
        HANDLE done_event;
        workpacket(HANDLE ev = NULL) : done_event(ev) {}
        virtual int execute() = 0;
        int do_work()
        {
                int retcode = execute();
                InterlockedDecrement(&num_async_requests);
                if (done_event != NULL)
                {
                        SetEvent(done_event);
                }
                SetEvent(async_complete_event);
                return retcode;
        }
        virtual ~workpacket() {}
};

static std::list<workpacket*> worklist;

struct se_log_values_t 
{
        long run_number;
        std::string source;
        std::vector<std::string> iso_times;
        std::vector<std::string> block_names;
        std::vector<std::string> block_values;
        se_log_values_t(long run_number_, const char* source_, const std::vector<std::string>& iso_times_, const std::vector<std::string>& block_names_, const std::vector<std::string>& block_values_) :
            run_number(run_number_), source(source_), iso_times(iso_times_), block_names(block_names_), block_values(block_values_) {}
        se_log_values_t(long run_number_, const char* source_, const char* iso_time_, const char* block_name_, const char* block_value_) : run_number(run_number_), source(source_)
        {
           iso_times.push_back(iso_time_), block_names.push_back(block_name_), block_values.push_back(block_value_);
        }
};

static std::list<se_log_values_t*> *se_worklist = NULL;

static int __stdcall se_log_values_list(const std::list<se_log_values_t*> *tlist);

static void process_requests(void* arg)
{
        while(true)
        {
                if (WaitForSingleObject(work_queued, 30000 /*INFINITE*/) == WAIT_OBJECT_0)
                {
                        EnterCriticalSection(&work_critical);
                        while (worklist.size() > 0)
                        {
                                workpacket* w = worklist.front();
                                worklist.pop_front();
                                LeaveCriticalSection(&work_critical);
                                int ret = w->do_work();
                                delete w;
                                EnterCriticalSection(&work_critical);
                        }
                        if (se_worklist != NULL)
                        {
                                std::list<se_log_values_t*> *tlist = se_worklist;
                                se_worklist = NULL;
                                LeaveCriticalSection(&work_critical);
                                se_log_values_list(tlist);
                                BOOST_FOREACH(se_log_values_t* item, *tlist)
                                {
                                        delete item;
                                        InterlockedDecrement(&num_async_requests);
                                }
                                tlist->clear();
                                delete tlist;
                                SetEvent(async_complete_event);
                                EnterCriticalSection(&work_critical);
                        }
                        ++num_work_iterations;
                        LeaveCriticalSection(&work_critical);
                        Poco::Thread::sleep(200); // give somebody else a chance with the database
                }
        }
}

static int general_init()
{
        static volatile bool general_init_done = false;
        static SECURITY_ATTRIBUTES sec_attr = { sizeof(SECURITY_ATTRIBUTES), NULL, FALSE };
        if (!general_init_done)
        {
                general_init_done = true;
                async_complete_event = CreateEvent(&sec_attr, FALSE, FALSE, NULL);
                InitializeCriticalSection(&work_critical);
                work_queued = CreateEvent(&sec_attr, FALSE, FALSE, NULL);
                _beginthread(process_requests, 0, NULL);
        }
        return 0;
}

static int __stdcall se_async(workpacket *work)
{
        general_init();
        InterlockedIncrement(&num_async_requests);
        EnterCriticalSection(&work_critical);
        worklist.push_back(work);
        SetEvent(work_queued);
        LeaveCriticalSection(&work_critical);
        return 0;
}


static int count_rows_callback(void *arg, int argc, char **argv, char **azColName)
{
        int* count = (int*)arg;
        ++(*count);
        return 0;
}


#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); \
                        return -1; \
                }

#define SQLITE_HANDLE_EXCEPTION(__func_name) \
        catch(std::exception& ex) \
        { \
                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: %s: exception %s at %s:%d", #__func_name, ex.what(), __FILE__, __LINE__); \
                return -1; \
        }

#define OPEN_DATABASE \
        general_init(); \
        ISISDB the_database(false);


const char* __stdcall se_version()
{
        try {
                OPEN_DATABASE;
                return the_database.version();
        }
        catch(std::exception& ex)
        {
                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: %s: exception %s at %s:%d", "se_version", ex.what(), __FILE__, __LINE__);
                return "unknown";
        }
}

static int my_sqlite3_exec(sqlite3* db, const char* sql, int (*callback)(void*,int,char**,char**),  void* arg, char** errmsg)
{
//      DBLock mylock;
        *errmsg = NULL;
    return sqlite3_exec(db, sql, callback, arg, errmsg);
}

static int se_set_config_param(const char* name, const char* value)
{
        char *sql_line;
        char* errmsg = NULL;
        try 
        {
                OPEN_DATABASE;
                // we cannot use "INSERT OR REPLACE" as this will not fire an UPDATE trigger
                sql_line = sqlite3_mprintf(
                        "INSERT OR IGNORE INTO config VALUES ('%q', '%q'); "
                        "UPDATE config SET value='%q' WHERE name='%q'; ", name, value, value, name);
                int rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
                sqlite3_free(sql_line);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_set_config_param);
    return 0;
}


static int se_get_config_param(const char* name, std::string& value)
{
        char *sql_line;
        char* errmsg = NULL;
        int pnRow, pnColumn;
        char** pazResult = NULL;
        value = "";
        int rc;
        try
        {
                OPEN_DATABASE;
                sql_line = sqlite3_mprintf(
                    "SELECT value FROM config WHERE name = '%q';", name);
//              DBLock mylock;
            rc = sqlite3_get_table(the_database, sql_line, &pazResult, &pnRow, &pnColumn, &errmsg);
                sqlite3_free(sql_line);
            SQLITE_CHECK_RC(rc);
                if (pnRow == 1 && pnColumn == 1)
                {
                        value = pazResult[1*pnColumn+0]; // 1st column, 2nd result row (first result row is column headers)
                }
                sqlite3_free_table(pazResult);
        }
        SQLITE_HANDLE_EXCEPTION(se_get_config_param);
        return 0;
}

static int se_set_config_param(const char* name, long value)
{
        char buffer[256];
        _snprintf_s(buffer, sizeof(buffer), _TRUNCATE, "%ld", value);
        return se_set_config_param(name, buffer);
}

static long get_run_number()
{
        std::string s;
        if ( (se_get_config_param("run_number", s) == 0) && (s.length() > 0) )
        {
                return atol(s.c_str());
        }
        else
        {
                return -1;
        }
}

static long get_period()
{
        std::string s;
        if ( (se_get_config_param("period", s) == 0) && (s.length() > 0) )
        {
                return atol(s.c_str());
        }
        else
        {
                return -1;
        }
}

static int isReal(const char* value)
{
        char* endptr = NULL;
        double d = strtod(value, &endptr);
        if (endptr != NULL && endptr != value)
        {
                return 1;
        }
        // on Windows strtod() does not handle NAN, INF, -INFINITY etc
        int n = strlen(value);
        char* tmpstr = strdup(value);
        int is_real = 0;
        for(int i=0; i<n; i++)
        {
                tmpstr[i] = toupper(tmpstr[i]);
        }
        if ( (strstr(tmpstr, "INF") != NULL) || (strstr(tmpstr, "NAN") != NULL) )
        {
                is_real = 1;
        }
        free(tmpstr);
        return is_real;
}

SELOGGER_API int __stdcall se_start_epics_thread(epics_thread_data_t* arg)
{
        _beginthreadex(NULL, 0, run_epics_db, arg, 0, NULL);
        return 0;
}

SELOGGER_API int __stdcall se_set_block_value(const char* block_name, const char* block_value)
{
        std::vector<std::string> block_names;
        std::vector<std::string> block_values;
        block_names.push_back(block_name);
        block_values.push_back(block_value);
        return se_set_block_values(block_names, block_values);
}

SELOGGER_API int __stdcall se_set_block_values(const std::vector<std::string>& block_names, const std::vector<std::string>& block_values)
{
        char* sql_line;
        std::string s;
        int nvalues = block_names.size(); 
        s.reserve(nvalues*100);
        char* errmsg = NULL;
        time_t unix_time;
        time(&unix_time);
        char iso_time[64];
        ISOtime(unix_time, iso_time, sizeof(iso_time));
        if (database_debug)
        {
                std::stringstream sstr;
                for(int i=0; i<nvalues; i++)
                {
                        sstr << "se_set_block_values: " << block_names[i].c_str() << " = " << block_values[i].c_str() << (isReal(block_values[i].c_str()) ? " (real)" : " (string)") << "\r\n";
                }
                appendToFileAsync(database_debug_file, sstr.str().c_str());
        }
        try
        {
                OPEN_DATABASE;
                int run_number = get_run_number();
                s.append("BEGIN TRANSACTION; ");
                for(int i=0; i<nvalues; i++)
                {
                        sql_line = sqlite3_mprintf(
                                "UPDATE seblocks SET block_value='%q',is_real=%d,source='%q',value_time='%q' WHERE block_name='%q' AND run_number=%d; ", 
                                block_values[i].c_str(), isReal(block_values[i].c_str()), "UNKNOWN", iso_time, block_names[i].c_str(), run_number);
                        s.append(sql_line);
                        sqlite3_free(sql_line);
                }
                s.append("END TRANSACTION; ");
                int rc = my_sqlite3_exec(the_database, s.c_str(), NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_set_block_values);
        return 0;
}

SELOGGER_API int __stdcall se_log_value(long run_number, const char* source, const char* iso_time, const char* block_name, 
                                                                                const char* block_value)
{
        std::vector<std::string> block_names;
        std::vector<std::string> block_values;
        std::vector<std::string> block_times;
        block_names.push_back(block_name);
        block_values.push_back(block_value);
        block_times.push_back(iso_time);
        return se_log_values(run_number, source, block_times, block_names, block_values);
}

SELOGGER_API int __stdcall se_wait_for_async()
{
        general_init();
        EnterCriticalSection(&work_critical);
        uint64_t end_iter = num_work_iterations + 2;
        while( (num_async_requests > 0) && (num_work_iterations <= end_iter) )
        {
                LeaveCriticalSection(&work_critical);
                WaitForSingleObject(async_complete_event, 30000 /*INFINITE*/);
                EnterCriticalSection(&work_critical);
        }
        LeaveCriticalSection(&work_critical);
        return 0;
}

static int __stdcall se_async(se_log_values_t *work)
{
        general_init();
        InterlockedIncrement(&num_async_requests);
        EnterCriticalSection(&work_critical);
        if (se_worklist == NULL)
        {
                se_worklist = new std::list<se_log_values_t*>;
        }
        se_worklist->push_back(work);
        SetEvent(work_queued);
        LeaveCriticalSection(&work_critical);
        return 0;
}

SELOGGER_API int __stdcall se_log_value_async(long run_number, const char* source, const char* iso_time, const char* block_name, const char* block_value)
{
        return se_async(new se_log_values_t(run_number, source, iso_time, block_name, block_value));
}

SELOGGER_API int __stdcall se_log_values_async(long run_number, const char* source, const std::vector<std::string>& iso_times, const std::vector<std::string>& block_names, const std::vector<std::string>& block_values)
{
        return se_async(new se_log_values_t(run_number, source, iso_times, block_names, block_values));
}


SELOGGER_API int __stdcall se_log_fvalues(long run_number, const char* source, const std::vector<std::string>& iso_times, const std::vector<std::string>& block_names, const std::vector<float>& block_values)
{
        std::vector<std::string> svals(block_values.size());
        for(int i=0; i<block_values.size(); ++i)
        {
                svals[i] = Poco::NumberFormatter::format(block_values[i]);
//              Poco::format(svals[i], "%hf", block_values[i]);
        }
        return se_log_values(run_number, source, iso_times, block_names, svals);
}

SELOGGER_API int __stdcall se_log_values(long run_number, const char* source, const std::vector<std::string>& iso_times, const std::vector<std::string>& block_names, const std::vector<std::string>& block_values)
{
        char* sql_line;
        char* errmsg = NULL;
        std::string s;
        int nvalues = block_names.size();
        if (database_debug)
        {
                std::stringstream sstr;
                for(int i=0; i<nvalues; i++)
                {
                        sstr << "se_log_values: " << iso_times[i].c_str() << " " << block_names[i].c_str() << " = " << block_values[i].c_str() << (isReal(block_values[i].c_str()) ? " (real)" : " (string)") << "\r\n";
                }
                appendToFileAsync(database_debug_file, sstr.str().c_str());
        }
        try
        {
                OPEN_DATABASE;
                if (run_number <= 0)
                {
                        if ( (se_get_config_param("run_number", s) == 0) && (s.length() > 0) )
                        {
                                run_number = atol(s.c_str());
                        }
                        else
                        {                       
                                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: unable to detremine run number");
                                return -1;
                        }
                }
                s.clear();
                s.reserve(nvalues*100);
                time_t unix_time;
                s.append("BEGIN TRANSACTION; ");
                for(int i=0; i<nvalues; i++)
                {
                        unix_time = unixTime(iso_times[i].c_str());
                        sql_line = sqlite3_mprintf(
                                "INSERT INTO selog VALUES (%ld,'%q','%q',%llu,'%q','%q',%d); ",
                                    run_number, source, iso_times[i].c_str(), (unsigned long long)unix_time, 
                                        block_names[i].c_str(), block_values[i].c_str(), isReal(block_values[i].c_str()));
                        s.append(sql_line);
                        sqlite3_free(sql_line);
                }
                s.append("END TRANSACTION; ");
                int rc = my_sqlite3_exec(the_database, s.c_str(), NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_log_values);
        return 0;
}

static int __stdcall se_log_values_list(const std::list<se_log_values_t*> *tlist)
{
        char* sql_line;
        char* errmsg = NULL;
        std::string s;
        int nvalues = 0;
        int current_run_number, run_number;
        BOOST_FOREACH(const se_log_values_t* item, *tlist)
        {
                nvalues += item->block_names.size();
        }
        try
        {
                OPEN_DATABASE;
                if ( (se_get_config_param("run_number", s) == 0) && (s.length() > 0) )
                {
                        current_run_number = atol(s.c_str());
                }
                else
                {                       
                        _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: unable to detremine run number");
                        return -1;
                }
                s.clear();
                s.reserve(nvalues*100);
                s.append("BEGIN TRANSACTION; ");
                BOOST_FOREACH(const se_log_values_t* item, *tlist)
                {
                        run_number = (item->run_number <= 0 ? current_run_number : item->run_number);
                        time_t unix_time;
                        for(int i=0; i<item->block_names.size(); ++i)
                        {
                                unix_time = unixTime(item->iso_times[i].c_str());
                                sql_line = sqlite3_mprintf(
                                "INSERT INTO selog VALUES (%ld,'%q','%q',%llu,'%q','%q',%d); ",
                                    run_number, item->source.c_str(), item->iso_times[i].c_str(), (unsigned long long)unix_time, 
                                        item->block_names[i].c_str(), item->block_values[i].c_str(), isReal(item->block_values[i].c_str()));
                                s.append(sql_line);
                                sqlite3_free(sql_line);
                        }
                }
                s.append("END TRANSACTION; ");
                int rc = my_sqlite3_exec(the_database, s.c_str(), NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_log_values_list);
        return 0;
}


SELOGGER_API int __stdcall se_close()
{
        return 0;
}

SELOGGER_API const char* __stdcall se_get_errmsg()
{
        return last_error;
}

SELOGGER_API int __stdcall se_log_run(int run_number, const char* source, const char* iso_time, int period,
                                int run_status, int is_running, int is_waiting,
                                int good_frames, int raw_frames, 
                                float good_uamph, float raw_uamph,
                        int64_t monitor_sum0, float dae_beam_current, int64_t total_counts,
                                float count_rate, float npratio_current)
{
        char *sql_line;
        char* errmsg = NULL;
        try
        {
                OPEN_DATABASE;
                sql_line = sqlite3_mprintf(
                                "INSERT INTO run_status VALUES (%d,'%q','%q',%llu,%d,%d,%d,%d,%d,%d,%g,%g,%lld,%g,%lld,%g,%g);",
                                run_number, source, iso_time, static_cast<unsigned long long>(unixTime(iso_time)), period,
                                run_status, is_running, is_waiting,
                                good_frames, raw_frames, 
                                good_uamph, raw_uamph,
                        static_cast<long long>(monitor_sum0), dae_beam_current, static_cast<long long>(total_counts),
                                count_rate, npratio_current);
                int rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
                sqlite3_free(sql_line);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_log_run);
        return 0;
}

SELOGGER_API int __stdcall se_set_run_number(int run_number)
{
        return se_set_config_param("run_number", run_number);
}

SELOGGER_API int __stdcall se_set_period(int period)
{
        return se_set_config_param("period", period);
}

SELOGGER_API int __stdcall se_get_seci_config(std::string& seci_config)
{
        return se_get_config_param("seci_config", seci_config);
}

SELOGGER_API int __stdcall se_set_run_state(int run_number, const char* state, int is_start)
{
        char sql_line[512];
        char iso_time[64];
        ISOtime(time(NULL), iso_time, sizeof(iso_time));
        char* errmsg = NULL;
        try 
        {
                OPEN_DATABASE;
                int rc;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                    "INSERT INTO run_state VALUES (%d, %d, '%s', '%s', %llu); ",
                    run_number, is_start, state, iso_time, (unsigned long long)unixTime(iso_time));
            rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
            SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_set_block_details);
        return 0;
}

SELOGGER_API int __stdcall se_new_seci_config(const char* config_name)
{
        char* errmsg = NULL;
        try
        {
                if (se_set_config_param("seci_config", config_name) == -1)
                {
                        return -1;
                }
                OPEN_DATABASE;
                int run_number = get_run_number();
                char* sql_line = sqlite3_mprintf("DELETE FROM seblocks WHERE run_number=%d; ", run_number);
                int rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
                sqlite3_free(sql_line);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_new_seci_config);
        return 0;
}

SELOGGER_API int __stdcall se_set_block_details(const char* block_name, const char* setpoint_value, 
                const char* vi_name, const char* read_control_label, const char* set_control_label, const char* button_control_label, 
                unsigned options, const char* nexus_name, float low_limit, float high_limit, 
                const char* units, const char* current_value)
{
        char* sql_line;
        char* errmsg = NULL;
        time_t unix_time;
        time(&unix_time);
        char iso_time[64];
        ISOtime(unix_time, iso_time, sizeof(iso_time));
        try 
        {
                OPEN_DATABASE;
                int rc;
                int run_number = get_run_number();
                sql_line = sqlite3_mprintf(
                    "INSERT OR REPLACE INTO seblocks VALUES (%d, '%q', '%q', '%q', '%q', '%q', '%q', %u, '%q', %g, %g, '%q', '%q', %d, '%q', '%q', '%q'); ",
                    run_number, block_name, setpoint_value, vi_name, read_control_label, 
                    set_control_label, button_control_label,
                    options, nexus_name, low_limit, high_limit, units, current_value, 
                        isReal(current_value), "UNKNOWN", iso_time, iso_time);
            rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
                sqlite3_free(sql_line);
            SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_set_block_details);
        return 0;
}


static int seblock_rows_callback(void *arg, int argc, char **argv, char **/*azColName*/)
{
        seblock_map_t* blocks = (seblock_map_t*)arg;
        if (argc == 0)
        {
                return 0;
        }
        // always create a block - this is so we can be used both on both selog and seblocks tables
        SEBLOCK& block = (*blocks)[argv[1]];
        if (argc >= 14)  // we get called from two places, in one case we just want to create a blank entry
        {
                block.vi_name = argv[3];
                block.read_control = argv[4];
                block.set_control = argv[5];
                block.button_control = argv[6];
                block.options = atol(argv[7]);
                block.nexus_name = argv[8];
                block.low_limit = atof(argv[9]);
                block.high_limit = atof(argv[10]);
                block.units = argv[11];
                block.is_real = (atol(argv[13]) != 0 ? true : false);
                if (block.is_real)
                {
                        if (block.fsetpoint_value.size() == 0)
                        {
                                block.fsetpoint_value.push_back(atof(argv[2]));
                                block.fcurrent_value.push_back(atof(argv[12]));
                                block.fsetpoint_spread.push_back(0.0);
                                block.fcurrent_spread.push_back(0.0);
                        }
                        else
                        {
                                block.fsetpoint_value[0] = atof(argv[2]);
                                block.fcurrent_value[0] = atof(argv[12]);
                                block.fsetpoint_spread[0] = 0.0;
                                block.fcurrent_spread[0] = 0.0;
                        }
                }
                else
                {
                        if (block.setpoint_value.size() == 0)
                        {
                                block.setpoint_value.push_back(argv[2]);
                                block.current_value.push_back(argv[12]);
                        }
                        else
                        {
                                block.setpoint_value[0] = argv[2];
                                block.current_value[0] = argv[12];
                        }
                }
        }
        return 0;
}


SELOGGER_API int __stdcall se_new_measurement(const char* label, std::string& res)
{
        char sql_line[512];
        char* errmsg = NULL;
        unsigned char* uuid_str = NULL;
        UUID the_uuid;
        char iso_time[64];
        ISOtime(time(NULL), iso_time, sizeof(iso_time));
        res = "";
        int run_number = 0;
        std::string s;
        try
        {
                OPEN_DATABASE;
                if ( (se_get_config_param("run_number", s) == 0) && (s.length() > 0) )
                {
                        run_number = atol(s.c_str());
                }
                UuidCreate(&the_uuid);
                UuidToString(&the_uuid, &uuid_str);
                int rc;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                    "INSERT INTO measurement VALUES ('%s', %d, '%s', '%s'); ",
                    uuid_str, run_number, iso_time, label);
            rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
            SQLITE_CHECK_RC(rc);
                res = (const char*)uuid_str;
                RpcStringFree(&uuid_str);
        }
        SQLITE_HANDLE_EXCEPTION(se_new_measurement);
        return 0;
}

SELOGGER_API int __stdcall se_get_measurement_label(const char* measurement_id, std::string& res)
{
        char sql_line[512];
        char* errmsg = NULL;
        int pnRow, pnColumn;
        char** pazResult = NULL;
        res = "";
        try
        {
                OPEN_DATABASE;
                int rc;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                    "SELECT label FROM measurement WHERE uuid='%s'; ", measurement_id);
//              DBLock mylock;
            rc = sqlite3_get_table(the_database, sql_line, &pazResult, &pnRow, &pnColumn, &errmsg);
            SQLITE_CHECK_RC(rc);
                if (pnRow == 1 && pnColumn == 1)
                {
                        res = pazResult[1*pnColumn+0]; // 1st column, 2nd result row (first result row is column headers)
                }
                sqlite3_free_table(pazResult);
        }
        SQLITE_HANDLE_EXCEPTION(se_get_measurement_label);
        return 0;
}

SELOGGER_API int __stdcall se_set_measurement_label(const char* measurement_id, const char* label)
{
        char sql_line[512];
        char* errmsg = NULL;
        try
        {
                OPEN_DATABASE;
                int rc;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                    "UPDATE measurement SET label='%s' WHERE uuid='%s'; ", label, measurement_id);
            rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
        }
        SQLITE_HANDLE_EXCEPTION(se_set_measurement_label);
        return 0;
}

SELOGGER_API int __stdcall se_get_measurement_id(const char* label, std::string& measurement_id)
{
        char sql_line[512];
        char* errmsg = NULL;
        int pnRow, pnColumn;
        char** pazResult = NULL;
        measurement_id = "";
        try
        {
                OPEN_DATABASE;
                int rc;
                // may be more than one measurement id with this label, so order by creation date to get latest
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                    "SELECT uuid FROM measurement WHERE label='%s' ORDER BY iso_time DESC; ", label);
//              DBLock mylock;
            rc = sqlite3_get_table(the_database, sql_line, &pazResult, &pnRow, &pnColumn, &errmsg);
            SQLITE_CHECK_RC(rc);
                if (pnRow >= 1 && pnColumn == 1)
                {
                        measurement_id = pazResult[1*pnColumn+0]; // 1st column, 2nd result row (first result row is column headers)
                }
                sqlite3_free_table(pazResult);
        }
        SQLITE_HANDLE_EXCEPTION(se_get_measurement_id);
        return 0;
}

SELOGGER_API int __stdcall se_get_measurement_first_run(const char* measurement_id, long& run_number)
{
        char sql_line[512];
        char* errmsg = NULL;
        int pnRow, pnColumn;
        char** pazResult = NULL;
        run_number = 0;
        try
        {
                OPEN_DATABASE;
                int rc;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                    "SELECT run_number FROM measurement WHERE uuid='%s'; ", measurement_id);
//              DBLock mylock;
            rc = sqlite3_get_table(the_database, sql_line, &pazResult, &pnRow, &pnColumn, &errmsg);
            SQLITE_CHECK_RC(rc);
                if (pnRow == 1 && pnColumn == 1)
                {
                        run_number = atol(pazResult[1*pnColumn+0]); // 1st column, 2nd result row (first result row is column headers)
                }
                sqlite3_free_table(pazResult);
        }
        SQLITE_HANDLE_EXCEPTION(se_get_measurement_first_run);
        return 0;
}

static int se_clear_values2(int run_start, int run_finish)
{
        char sql_line[512];
        char* errmsg = NULL;
        try
        {
                OPEN_DATABASE;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE,
                                "BEGIN TRANSACTION; "
                                "DELETE FROM run_state WHERE (run_number BETWEEN %d AND %d) OR (run_number=-1); " 
                                "DELETE FROM seperiods WHERE (run_number BETWEEN %d AND %d) OR (run_number=-1); " 
                                "DELETE FROM selog WHERE (run_number BETWEEN %d AND %d) OR (run_number=-1); " 
                                "DELETE FROM run_status WHERE (run_number BETWEEN %d AND %d) OR (run_number=-1); "
                                "DELETE FROM seblocks WHERE (run_number BETWEEN %d AND %d) OR (run_number=-1); " 
                                "END TRANSACTION; ",
                                run_start, run_finish, run_start, run_finish, run_start, run_finish, run_start, run_finish, run_start, run_finish);
                int rc = my_sqlite3_exec(the_database, sql_line, NULL, NULL, &errmsg);
            SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_clear_values2);
        return 0;
}

struct se_clear_values2_t : public workpacket
{
        long run_start;
        long run_finish;
        se_clear_values2_t(long run_start_, long run_finish_) :
            workpacket(), run_start(run_start_), run_finish(run_finish_) {}
        int execute()
        {
                return se_clear_values2(run_start, run_finish);
        }
};

SELOGGER_API int __stdcall se_clear_values2_async(int run_start, int run_finish)
{
        return se_async(new se_clear_values2_t(run_start, run_finish));
}

SELOGGER_API int __stdcall se_clear_values(int run_start, int run_finish)
{
//      char* errmsg = NULL;
        std::string s;
        int current_run_number;
        if ( (se_get_config_param("run_number", s) == 0) && (s.length() > 0) )
        {
                current_run_number = atol(s.c_str());
        }
        else
        {                       
                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: unable to detremine run number");
                return -1;
        }
        run_start = (run_start <= 0 ? current_run_number : run_start);
        run_finish = (run_finish <= 0 ? current_run_number : run_finish);
        se_clear_values2_async(run_start, run_finish);
        return 0;
}

SELOGGER_API int __stdcall se_get_blocks(int run_number, time_t ref_time, seblock_map_t& blocks)
{
        int n = 0;
        const char* pzTail;
        char sql_line[512];
        char* errmsg = NULL;

        se_wait_for_async();

//      DBLock mylock;

        blocks.clear();

        sqlite3_stmt *stmt, *stmt2, *stmt3, *stmt4, *stmt_real0;
        OPEN_DATABASE;
        // this fills up the table with blocks we know about
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT * FROM seblocks WHERE run_number=%d;", run_number);
        int rc = my_sqlite3_exec(the_database, sql_line, seblock_rows_callback, &blocks, &errmsg);
        SQLITE_CHECK_RC(rc);
        // this fills up the rest of the table with blocks we have log files for but not "seblock" entries
        // it relies on seblock_rows_callback creating a blank block if the number of columns in the 
        // row passed is not correct
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                "SELECT DISTINCT run_number, block_name FROM selog WHERE run_number=%d;", run_number);
        rc = my_sqlite3_exec(the_database, sql_line, seblock_rows_callback, &blocks, &errmsg);
        SQLITE_CHECK_RC(rc);
        seblock_map_t::iterator it;
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT unix_time, block_value FROM selog WHERE run_number=? AND block_name=?;");
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt, &pzTail);
        SQLITE_CHECK_RC(rc);
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT COUNT(unix_time) FROM selog WHERE run_number=? AND block_name=?;");
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt3, &pzTail);
        SQLITE_CHECK_RC(rc);
// use last rather than average value - stops e.g. averaging over values before run started
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT period, block_setpoint, block_value FROM seperiods " 
                        "WHERE run_number=? AND block_name=? "
                        "ORDER BY period DESC, unix_time ASC;");
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt2, &pzTail);
        SQLITE_CHECK_RC(rc);
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT period, "
                        "avg(block_setpoint), avg(block_value), min(block_setpoint), min(block_value), max(block_setpoint), max(block_value) "
                        "FROM seperiods WHERE run_number=? AND block_name=? AND unix_time>=%lu "
                        "GROUP BY period ORDER BY period DESC;", ref_time);
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt4, &pzTail);
        SQLITE_CHECK_RC(rc);
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT COUNT(unix_time) FROM selog WHERE run_number=? AND block_name=? AND is_real=0;");
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt_real0, &pzTail);
        SQLITE_CHECK_RC(rc);
        for(it=blocks.begin(); it != blocks.end(); ++it)
        {
                SEBLOCK& block = it->second;
                block.ref_time = ref_time;
                rc = sqlite3_reset(stmt);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_int(stmt, 1, run_number);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_text(stmt, 2, it->first.c_str(), -1, SQLITE_STATIC);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_reset(stmt3);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_int(stmt3, 1, run_number);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_text(stmt3, 2, it->first.c_str(), -1, SQLITE_STATIC);
                SQLITE_CHECK_RC(rc);
                if ( (rc = sqlite3_step(stmt3)) == SQLITE_ROW )
                {
                   // we add a few values on in case something gets logged before the real query is run
                   // and this vaues is not big enough. Everything would still work, but the vector would
                   // get reallocated and that is inefficient.
                   // also we want to avoid (n == 0) 
                        n = sqlite3_column_int(stmt3, 0) + 10;
                }
                else
                {
                        n = 200;
                        SQLITE_CHECK_RC(rc);
                }

                rc = sqlite3_reset(stmt_real0);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_int(stmt_real0, 1, run_number);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_text(stmt_real0, 2, it->first.c_str(), -1, SQLITE_STATIC);
                SQLITE_CHECK_RC(rc);
                if ( (rc = sqlite3_step(stmt_real0)) == SQLITE_ROW )
                {
                        block.is_real = (sqlite3_column_int(stmt_real0, 0) == 0);
                }
                else
                {
                        block.is_real = false;
                        SQLITE_CHECK_RC(rc);
                }

                block.time.reserve(n);
                block.svalues.reserve(n);
                block.fvalues.reserve(n);
                while( (rc = sqlite3_step(stmt)) == SQLITE_ROW )
                {
                        block.time.push_back(sqlite3_column_int64(stmt, 0) - ref_time);
                        if (block.is_real)
                        {
                                block.fvalues.push_back(sqlite3_column_double(stmt, 1));
                        }
                        else
                        {
                                block.svalues.push_back((const char*)sqlite3_column_text(stmt, 1));
                        }
                }
                if (rc != SQLITE_DONE)
                {
                        SQLITE_CHECK_RC(rc);
                }
                rc = sqlite3_reset(stmt2);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_int(stmt2, 1, run_number);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_text(stmt2, 2, it->first.c_str(), -1, SQLITE_STATIC);
                SQLITE_CHECK_RC(rc);
                while( (rc = sqlite3_step(stmt2)) == SQLITE_ROW )
                {
                        int period = sqlite3_column_int(stmt2, 0);
                        if (block.is_real)
                        {
                                if (block.fsetpoint_value.size() <= period)
                                {
                                        block.fsetpoint_value.resize(period+1);
                                        block.fcurrent_value.resize(period+1);
                                        block.fsetpoint_spread.resize(period+1);
                                        block.fcurrent_spread.resize(period+1);
                                }
                                block.fsetpoint_value[period] = sqlite3_column_double(stmt2, 1);
                                block.fcurrent_value[period] = sqlite3_column_double(stmt2, 2);
                        }
                        else
                        {
                                if (block.setpoint_value.size() <= period)
                                {
                                        block.setpoint_value.resize(period+1);
                                        block.current_value.resize(period+1);
                                }
                                block.setpoint_value[period] = (const char*)sqlite3_column_text(stmt2, 1);
                                block.current_value[period] = (const char*)sqlite3_column_text(stmt2, 2);
                        }
                }
                if (rc != SQLITE_DONE)
                {
                        SQLITE_CHECK_RC(rc);
                }
                rc = sqlite3_reset(stmt4);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_int(stmt4, 1, run_number);
                SQLITE_CHECK_RC(rc);
                rc = sqlite3_bind_text(stmt4, 2, it->first.c_str(), -1, SQLITE_STATIC);
                SQLITE_CHECK_RC(rc);
                while( (rc = sqlite3_step(stmt4)) == SQLITE_ROW )
                {
                        int period = sqlite3_column_int(stmt4, 0);
                        if (block.is_real)
                        {
                                block.fsetpoint_spread[period] = sqlite3_column_double(stmt4, 5) - sqlite3_column_double(stmt4, 3);
                                block.fcurrent_spread[period] = sqlite3_column_double(stmt4, 6) - sqlite3_column_double(stmt4, 4);
                        }
                }
                if (rc != SQLITE_DONE)
                {
                        SQLITE_CHECK_RC(rc);
                }
        }
        rc = sqlite3_finalize(stmt);
        SQLITE_CHECK_RC(rc);
        rc = sqlite3_finalize(stmt2);
        SQLITE_CHECK_RC(rc);
        rc = sqlite3_finalize(stmt3);
        SQLITE_CHECK_RC(rc);
        rc = sqlite3_finalize(stmt4);
        SQLITE_CHECK_RC(rc);
        rc = sqlite3_finalize(stmt_real0);
        SQLITE_CHECK_RC(rc);
    return 0;
}
                
SELOGGER_API int __stdcall se_get_run_status(int run_number, time_t ref_time, RUNBLOCK& blocks)
{
        char sql_line[512];
        sqlite3_stmt *stmt, *stmt2;
        char* errmsg = NULL;
        const char* pzTail;
        int rc, n;

//      DBLock mylock;
        OPEN_DATABASE;
        blocks.ref_time = ref_time;
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                "SELECT * FROM run_status WHERE run_number=%d;", run_number);
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt, &pzTail);
        SQLITE_CHECK_RC(rc);
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                "SELECT COUNT(unix_time) FROM run_status WHERE run_number=%d;", run_number);
        rc = sqlite3_prepare_v2(the_database, sql_line, -1, &stmt2, &pzTail);
        SQLITE_CHECK_RC(rc);
        if ( (rc = sqlite3_step(stmt2)) == SQLITE_ROW )
        {
                   // we add a few values on in case something gets logged before the real query is run
                   // and this vaues is not big enough. Everything would still work, but the vector would
                   // get reallocated and that is inefficient.
                   // also we want to avoid (n == 0) 
                n = sqlite3_column_int(stmt2, 0) + 10;
        }
        else
        {
                n = 200;
                SQLITE_CHECK_RC(rc);
        }
        blocks.time.reserve(n);
        blocks.period.reserve(n);
        blocks.run_status.reserve(n);
        blocks.is_running.reserve(n);
        blocks.is_waiting.reserve(n);
        blocks.good_frames.reserve(n);
        blocks.raw_frames.reserve(n);
        blocks.good_uamps.reserve(n);
        blocks.raw_uamps.reserve(n);
        blocks.monitor1_sum.reserve(n);
        blocks.dae_beam_current.reserve(n);
        blocks.total_counts.reserve(n);
        blocks.count_rate.reserve(n);
        blocks.np_ratio.reserve(n);
        while( (rc = sqlite3_step(stmt)) == SQLITE_ROW )
        {
                blocks.time.push_back(sqlite3_column_int64(stmt, 3) - blocks.ref_time);
                blocks.period.push_back(sqlite3_column_int(stmt, 4));
                blocks.run_status.push_back(sqlite3_column_int(stmt, 5));
                blocks.is_running.push_back(sqlite3_column_int(stmt, 6));
                blocks.is_waiting.push_back(sqlite3_column_int(stmt, 7));
                blocks.good_frames.push_back(sqlite3_column_int(stmt, 8));
                blocks.raw_frames.push_back(sqlite3_column_int(stmt, 9));
                blocks.good_uamps.push_back(sqlite3_column_double(stmt, 10));
                blocks.raw_uamps.push_back(sqlite3_column_double(stmt, 11));
                blocks.monitor1_sum.push_back(sqlite3_column_int64(stmt, 12));
                blocks.dae_beam_current.push_back(sqlite3_column_double(stmt, 13));
                blocks.total_counts.push_back(sqlite3_column_int64(stmt, 14));
                blocks.count_rate.push_back(sqlite3_column_double(stmt, 15));
                blocks.np_ratio.push_back(sqlite3_column_double(stmt, 16));
        }
        if (rc != SQLITE_DONE)
        {
                SQLITE_CHECK_RC(rc);
        }
        rc = sqlite3_finalize(stmt);
        SQLITE_CHECK_RC(rc);
        rc = sqlite3_finalize(stmt2);
        SQLITE_CHECK_RC(rc);
    return 0;
}

static void db_profile_func(void* arg, const char* sql, sqlite3_uint64 tim)
{
        FILE* f = (FILE*)arg;
        fprintf(f, "SQL \"%s\" took %8.3f s\n", sql, (double)tim / 1e9);
}

SELOGGER_API int __stdcall se_profile(const char* filename)
{
//      char* errmsg = NULL;
        OPEN_DATABASE;
        FILE* f = fopen(filename, "wtN");
        if (f != NULL)
        {
                sqlite3_profile(the_database, db_profile_func, f);
                return 0;
        }
        else
        {
                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: cannot profile to %s", filename);
                return -1;
        }
}


struct se_set_period_t : public workpacket
{
        int period;
        se_set_period_t(int period_, HANDLE ev = NULL) : workpacket(ev), period(period_) {}  
        int execute()
        {
                return se_set_period(period);
        }
};

SELOGGER_API int __stdcall se_set_period_async(int period)
{
        static volatile HANDLE h = NULL; // just testing for now
        static SECURITY_ATTRIBUTES sec_attr = { sizeof(SECURITY_ATTRIBUTES), NULL, FALSE };
        if (h == NULL)
        {
                h = CreateEvent(&sec_attr, FALSE, FALSE, NULL);
        }
        int ret = se_async(new se_set_period_t(period, h));
//      WaitForSingleObject(h, INFINITE);
        return ret;
}

struct se_set_run_state_t : public workpacket
{
        int run_number;
        std::string state;
        int is_start;
        se_set_run_state_t(int run_number_, const char* state_, int is_start_) : 
                workpacket(), run_number(run_number_), state(state_), is_start(is_start_) {}
        int execute()
        {
                return se_set_run_state(run_number, state.c_str(), is_start);
        }
};

SELOGGER_API int __stdcall se_set_run_state_async(int run_number, const char* state, int is_start)
{
        return se_async(new se_set_run_state_t(run_number, state, is_start));
}


SELOGGER_API int __stdcall se_get_block_values(seblock_map_t& blocks)
{
        char* errmsg = NULL;
        int rc;
        blocks.clear();
        OPEN_DATABASE;
        // this fills up the table with blocks we know about
        // this returns only one period with the setpoint and value for the current period as the first period
        int run_number = get_run_number();
        char* sql_line = sqlite3_mprintf("SELECT * FROM seblocks WHERE run_number=%d;", run_number);
        rc = sqlite3_exec(the_database, sql_line, seblock_rows_callback, &blocks, &errmsg);
        sqlite3_free(sql_line);
        SQLITE_CHECK_RC(rc);
        return 0;
}

SELOGGER_API int __stdcall se_set_block_setpoint(const char* block_name, const char* block_setpoint)
{
        std::vector<std::string> block_names;
        std::vector<std::string> block_setpoints;
        block_names.push_back(block_name);
        block_setpoints.push_back(block_setpoint);
        return se_set_block_setpoints(block_names, block_setpoints);
}

SELOGGER_API int __stdcall se_set_block_setpoints(const std::vector<std::string>& block_names, const std::vector<std::string>& block_setpoints)
{
        char *sql_line;
        std::string s;
        int nvalues = block_names.size(); 
        s.reserve(nvalues*200);
        char* errmsg = NULL;
        time_t unix_time;
        time(&unix_time);
        char iso_time[64];
        ISOtime(unix_time, iso_time, sizeof(iso_time));
        try
        {
                OPEN_DATABASE;
                int run_number = get_run_number();
                s.append("BEGIN TRANSACTION; ");
                for(int i=0; i<nvalues; i++)
                {   // iso_time='%s', unix_time=%I64u   (unsigned __int64)unix_time
                        sql_line = sqlite3_mprintf(
                                "UPDATE seblocks SET setpoint_value='%q',setpoint_time='%q' WHERE block_name='%q' AND run_number=%d; ", 
                                block_setpoints[i].c_str(), iso_time, block_names[i].c_str(), run_number);
                        s.append(sql_line);
                        sqlite3_free(sql_line);
                }
                s.append("END TRANSACTION; ");
                int rc = my_sqlite3_exec(the_database, s.c_str(), NULL, NULL, &errmsg);
                SQLITE_CHECK_RC(rc);
        }
        SQLITE_HANDLE_EXCEPTION(se_set_block_setpoints);
        return 0;
}


SELOGGER_API int __stdcall se_get_values(long run_number, const char* source, const char* block_name, std::vector<std::string>& iso_times, std::vector<std::string>& block_values)
{
        using namespace Poco::Data;
        std::string s;
        if (run_number <= 0)
        {
                        if ( (se_get_config_param("run_number", s) == 0) && (s.length() > 0) )
                        {
                                run_number = atol(s.c_str());
                        }
                        else
                        {                       
                                _snprintf_s(last_error, sizeof(last_error), _TRUNCATE, "SELOGGER: unable to detremine run number");
                                return -1;
                        }
        }
        try 
        {
                OPEN_DATABASE;
                Session ses(sqlite_session_pool.get()->get());

                Statement stmt(ses);
                iso_times.clear();
                block_values.clear();
                stmt << "SELECT iso_time, block_value FROM selog WHERE run_number=:run_number AND source=:source AND block_name=:block_name", 
                        into(iso_times), into(block_values), use(run_number), use(source), use(block_name);   
                while(!stmt.done())
                {
                        stmt.execute();
                }
                return 0;
        }
        SQLITE_HANDLE_EXCEPTION(se_get_values);
}

SELOGGER_API int __stdcall se_get_block_details(const char* block_name, SEBLOCK_DETAILS& block)
{
        using namespace Poco::Data;
        try 
        {
                OPEN_DATABASE;
                int run_number = get_run_number();
                Session ses(sqlite_session_pool.get()->get());
                Statement stmt(ses);
                stmt << "SELECT 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 block_name=:block_name AND run_number=" << run_number,  
                                 into(block.tuple()), use(block_name);   
                while(!stmt.done())
                {
                        stmt.execute();
                }
                return 0;
        }
        SQLITE_HANDLE_EXCEPTION(se_get_block_details);
}

SELOGGER_API int __stdcall se_exec_sql(const char* sql, std::string& result)
{
        using namespace Poco::Data;
        static const int limit_rows_returned = 200;
        try 
        {
                OPEN_DATABASE;
                Session ses(sqlite_session_pool.get()->get());
                Statement stmt(ses);
                stmt << sql, limit(limit_rows_returned);
                unsigned nrows = stmt.execute();
                RecordSet rs(stmt);
                std::stringstream sstr;
                for(int i=0; i < rs.columnCount(); ++i)
                {
                        sstr << (i > 0 ? " | " : "") << rs.columnName(i);
                }
                sstr << "\n";
                for(int row=0; row < rs.rowCount(); ++row)
                {
                        for(int col=0; col < rs.columnCount(); ++col)
                        {
                                sstr << (col > 0 ? " | " : "") << rs.value(col, row).convert<std::string>();
                        }
                        sstr << "\n";
                }
                if ( !stmt.done() )  // check if row limit truncated results 
                {
                        sstr << "*** RESULT TRUNCATED AT " << limit_rows_returned << " ROWS ***\n";
                }
                if ( nrows != rs.rowCount() )
                {
                        sstr << "*** ROW COUNT MISMATCH ***\n"; 
                }
                if ( nrows <= 0 || rs.columnCount() <= 0 )
                {
                        sstr << "*** NULL result: nrows=" << nrows << " ncols=" <<  rs.columnCount() <<" ***\n"; 
                }
                result = sstr.str();
                return 0;
        }
        SQLITE_HANDLE_EXCEPTION(se_exec_sql);
}



// old stuff
#if 0
static int selog_rows_callback(void *arg, int argc, char **argv, char **azColName)
{
        SEBLOCK* block = (SEBLOCK*)arg;
        if (argc >= 3)
        {
                block->time.push_back(atoi(argv[0]) - block->ref_time);
                int is_real = atol(argv[2]);
                if (is_real)
                {
                        block->fvalues.push_back(atof(argv[1]));
                }
                else
                {
                        block->svalues.push_back(argv[1]);
                }
        }
        return 0;
}

static int seperiods_rows_callback(void *arg, int argc, char **argv, char **azColName)
{
        SEBLOCK& block = *(SEBLOCK*)arg;
        if (argc >= 3)
        {
                int period = atol(argv[0]);
                if (block.is_real)
                {
                        if (block.fsetpoint_value.size() <= period)
                        {
                                block.fsetpoint_value.resize(period+1);
                                block.fcurrent_value.resize(period+1);
                        }
                        block.fsetpoint_value[period] = atof(argv[1]);
                        block.fcurrent_value[period] = atof(argv[2]);
                }
                else
                {
                        if (block.setpoint_value.size() <= period)
                        {
                                block.setpoint_value.resize(period+1);
                                block.current_value.resize(period+1);
                        }
                        block.setpoint_value[period] = argv[1];
                        block.current_value[period] = argv[2];
                }
        }
        return 0;
}

SELOGGER_API int __stdcall se_get_blocks(int run_number, time_t ref_time, seblock_map_t& blocks)
{
        char sql_line[512];
        char* errmsg = NULL;
        OPEN_DATABASE;
        // this fills up the table with blocks we know about
        int rc = my_sqlite3_exec(the_database, "SELECT * FROM seblocks;", seblock_rows_callback, &blocks, &errmsg);
        SQLITE_CHECK_RC(rc);
        // this fills up the rest of the table with blocks we have log files for but not "seblock" entries
        // it relies on seblock_rows_callback creating a blank block if the number of columns in the 
        // row passed is not correct
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                "SELECT DISTINCT block_name FROM selog WHERE run_number=%d;", run_number);
        rc = my_sqlite3_exec(the_database, sql_line, seblock_rows_callback, &blocks, &errmsg);
        SQLITE_CHECK_RC(rc);
        seblock_map_t::iterator it;
        for(it=blocks.begin(); it != blocks.end(); ++it)
        {
                it->second.ref_time = ref_time;
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT unix_time, block_value, is_real FROM selog WHERE run_number=%d AND block_name='%s';", 
                        run_number, it->first.c_str());
                rc = my_sqlite3_exec(the_database, sql_line, selog_rows_callback, &(it->second), &errmsg);
                SQLITE_CHECK_RC(rc);
                _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                        "SELECT period, avg(block_setpoint), avg(block_value) FROM seperiods WHERE run_number=%d AND block_name='%s' "
                        "GROUP BY period ORDER BY period DESC;", run_number, it->first.c_str());
                rc = my_sqlite3_exec(the_database, sql_line, seperiods_rows_callback, &(it->second), &errmsg);
                SQLITE_CHECK_RC(rc);
        }
    return 0;
}

static int run_rows_callback(void *arg, int argc, char **argv, char **azColName)
{
        RUNBLOCK* blocks = (RUNBLOCK*)arg;
        if (argc >= 17)
        {
                blocks->time.push_back(atoi(argv[3]) - blocks->ref_time);
                blocks->period.push_back(atoi(argv[4]));
                blocks->run_status.push_back(atoi(argv[5]));
                blocks->is_running.push_back(atoi(argv[6]));
                blocks->is_waiting.push_back(atoi(argv[7]));
                blocks->good_frames.push_back(atoi(argv[8]));
                blocks->raw_frames.push_back(atoi(argv[9]));
                blocks->good_uamps.push_back(atof(argv[10]));
                blocks->raw_uamps.push_back(atof(argv[11]));
                blocks->monitor1_sum.push_back(atoi(argv[12]));
                blocks->dae_beam_current.push_back(atof(argv[13]));
                blocks->total_counts.push_back(atoi(argv[14]));
                blocks->count_rate.push_back(atof(argv[15]));;
                blocks->np_ratio.push_back(atof(argv[16]));
        }
        return 0;
}

SELOGGER_API int __stdcall se_get_run_status(int run_number, time_t ref_time, RUNBLOCK& blocks)
{
        char sql_line[512];
        char* errmsg = NULL;
        OPEN_DATABASE;
        blocks.ref_time = ref_time;
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                "SELECT * FROM run_status WHERE run_number=%d;", run_number);
        int rc = my_sqlite3_exec(the_database, sql_line, run_rows_callback, &blocks, &errmsg);
        SQLITE_CHECK_RC(rc);
    return 0;
}

SELOGGER_API int __stdcall se_get_num_blocks(int run_number)
{
        char sql_line[512];
        char* errmsg = NULL;
        OPEN_DATABASE;
        // we may have changed config during a run etc. so there could be log files for blocks
        // that are no longer defined in the seblocks table
        int count = 0;
        _snprintf_s(sql_line, sizeof(sql_line), _TRUNCATE, 
                "SELECT block_name FROM seblocks UNION SELECT block_name FROM selog WHERE run_number=%d;", run_number);
        int rc = my_sqlite3_exec(the_database, sql_line, count_rows_callback, &count, &errmsg);
        SQLITE_CHECK_RC(rc);
    return count;
}

#endif /* if 0 */