/*
 * This script demonstrates the use of the threaded ODBC interface to insert
 * data into a database based on a timer.
 */

require ("Application");
require ("ODBCThreadSupport");

class ODBCThreadDemo Application
{
    DSN = "test";           // The DSN name to use for the database connection
    username = "test";          // The user name for connecting to the database
    password = "test";          // The password for connecting to the database
    tablename = "andrew5";      // The name of the database table
    cachefile = "c:/tmp/testcache.txt";     // Base name for the disk cache file
    
    tableclass;
    thread;
}

/* This method will be called every time the connection is established to the database.
 * If there is something we only want to perform on the first connection, we can test
 * is_first_connect to perform the code only once.
 */
method ODBCThreadDemo.onConnect()
{
    princ ("Connection succeeded\n");
    if (.thread.is_first_connect)
    {
        // Start the sequence defined by the AddInitStage calls in the constructor
        .thread.BeginAsyncInit();
    }
}

/* If we get a connection attempt failure, or the connection fails after having been
 * connected, this method is called.
 */
method ODBCThreadDemo.onConnectFail()
{
    princ ("Connection closed: ", SQLResult.Description, "\n");
}

/* Map the table in the set of table definitions that matches the name in .tablename
 * into a Gamma class.  This lets us easily convert between class instances and rows
 * in the table.
 */
method ODBCThreadDemo.mapTable(name, tabledefinitions)
{
    .tableclass = .thread.ClassFromTable(name, tabledefinitions);
}

/* Set up the timer or event handler functions to write to the table. */
method ODBCThreadDemo.startLogging()
{
    .TimerEvery(1, `(@self).writeData(#$DataSim:Sine));
    .OnChange(#$DataSim:Square, `(@self).writeData(this));
}

method ODBCThreadDemo.writeData(pointsymbol)
{
    local       row = new (.tableclass);
    local       pttime, ptltime;
    local       timestring;
    
    // Generate a timestamp in database-independent format to the millisecond.
    // Many databases strip the milliseconds from a timestamp, but it is harmless
    // to provide them in case the database can store them.
    pttime = WindowsTimeToUnixTime(PointMetadata(pointsymbol).timestamp);
    ptltime = localtime(pttime);
    timestring = format("{ts '%04d-%02d-%02d %02d:%02d:%02d.%03d'}",
        ptltime.year+1900, ptltime.mon+1, ptltime.mday, ptltime.hour, ptltime.min, ptltime.sec,
        (pttime % 1) * 1000);

    // Fill the row.  Since we mapped the table into a Gamma class, we can access
    // the columns in the row as member variables of the mapped class.
    row.ptname = string(pointsymbol);
    row.ptvalue = eval(pointsymbol);
    row.pttime = timestring;
    
    // Perform the insertion.  In this case we are providing no callback on completion.
    .thread.Insert(row, nil);
}

/* Print some statistics for the database thread. */
method ODBCThreadDemo.printStats ()
{
    // NCommands = the number of commands successfully transmitted to the database 
    //             thread or stored in cache for later transmission
    // NRejected = the number of commands that could not be transmitted or stored
    // NStoredPrimary = the number of commands stored in level 1 cache
    // NStoredSecondary = the number of commands stored in level 2 cache
    // NForwarded = the number of commands executed from level 1 or level 2 cache
    // NForwardFail = the number of commands that failed while forwarding from cache
    
    princ ("Commands sent: ", .thread.NCommands, " (", .thread.NRejected, " rejected)  Stored: ",
        .thread.NStoredPrimary, "/", .thread.NStoredSecondary, "  Forwarded: ", .thread.NForwarded,
        " (", .thread.NForwardFail, " failed)",
        "\n");
}

/* Write the 'main line' of the program here. */
method ODBCThreadDemo.constructor ()
{
    // Create and configure the database connection object
    .thread = new ODBCThread();
    .thread.Configure(.DSN, .username, .password, STORE_AND_FORWARD, .cachefile);
    
    // If we wanted to delete the table on the first connection after the script starts,
    // do this.  Be careful - re-running the script will start over and delete the table
    // again.
    .thread.AddInitStage(format("drop table %s", .tablename), nil, t);
    
    // Specify the initialization steps that must be executed in sequence
    // First, create the table if it does not exist.  The 't' in the onFail argument says
    // to ignore errors and continue with the next stage.
    .thread.AddInitStage(format("create table %s (ptid int auto_increment primary key, ptname varchar(64),
                                                  ptvalue double, pttime datetime )", .tablename),
        nil, t);
    
    // Query the table and map it to a class for eash insertion.  We want to run an asynchronous event
    // within the asynchronous initialization stage, so to do that we specify the special method
    // cbInitStage as the callback function of our asynchronous event (GetTableInfo).  We deal with
    // the return from the GetTableInfo in the onSuccess argument of the init stage.
    .thread.AddInitStage(`(@.thread).GetTableInfo("", "", (@.tablename), "TABLE,VIEW",
                                                  `(@.thread).cbInitStage()),
        `(@self).mapTable(@.tablename, SQLTables), nil);
    
    // Do not start writing data to the table until we have successfully created and mapped
    // the table to a class.  If we wanted to start writing data immediately, then we would
    // create the table class beforehand instead of querying the database for the table
    // definition.  Then, even if the database were unavailable we could still cache to the
    // local disk until the database was ready.
    .thread.AddInitStage(nil, `(@self).startLogging(), nil);
    
    // Set up the callback functions for various events from the database thread
    .thread.OnConnectionSucceeded = `(@self).onConnect();
    .thread.OnConnectionFailed = `(@self).onConnectFail();
    .thread.OnFileSystemError = `princ("File System Error: ", SQLResult, "\n");
    .thread.OnODBCError = `princ("ODBC Error: ", SQLResult, "\n");
    .thread.OnExecuteStored = nil;
    
    // Now that everything is configured, start the thread and begin connecting.  All of the
    // logic now will be driven through the onConnect callback and then through the init
    // stages.
    .thread.Start();
    
    // Print statistics about the database thread every 2 seconds.
    .TimerEvery(2, `(@self).printStats());
}

/* Any code to be run when the program gets shut down. */
method ODBCThreadDemo.destructor ()
{
}

/* Start the program by instantiating the class. */
ApplicationSingleton (ODBCThreadDemo);