Publish one day's data once a day to SQL

[phildunlap]

A client called and asked how to do this over the phone, so, here's a setup to publish data once per day to an SQL database:

1. Setup your SQL Data source. Add any JDBC drivers you need (H2 and MySQL drivers are included in Mango) to the classpath by placing their JAR at Mango/overrides/lib/ . You can have a low-impact select statement with SELECT 1; and a high update period.
2. Create a point on your SQL data source with 'Modify table only' and write your insert statement akin to:

insert into data (id, value, time) values (?,?,?)

with the parameters Integer, Double, Big Integer

3. Write your script. This script should publish the last day of data from every point except the SQL insert point we created in Step 2 (not the RQL to exclude that point based on XID):

//Publish all points except the SQL inserter
var publishPoints = DataPointQuery.query('ne(xid,DP_SQL_Insert)');
var pvd = this.pvd;
if(!pvd) {
    pvd = this.pvd = com.serotonin.m2m2.Common.databaseProxy.newPointValueDao();
}

function sqlPublish(fkId, pvtList) {
    var sqlString = '';
    var first = true;
    for(var i = 0; i < pvtList.length; i+=1) {
        if(first)
            first = false;
        else
            sqlString += ",";
        sqlString += "("+fkId+","+pvtList[ i ].value+","+pvtList[ i ].time+")";
        
        if(sqlString.length > 10000) { //some maximum size, surely
            sqlPublishPoint.set(sqlString);
            sqlString = "";
            first = true;
        }
    }
    if(sqlString !== '')
        sqlPublishPoint.set(sqlString);
}

CONTEXT.useTimestamp();
var sinceTime = CONTEXT.timestamp - CONTEXT.millisInPast(DAY);
//print(sinceTime);
for(var k = 0; k < publishPoints.length; k+=1) {
    var publishPoint = publishPoints[k];
    var dpid = com.serotonin.m2m2.db.dao.DataPointDao.instance.getDataPointIdByXid(publishPoint.xid);
    var values = pvd.getPointValues(dpid, sinceTime);
//    print("SQL publish of " + publishPoint.name + " has " + values.length + " values");
    sqlPublish(fkId, values);
}

and then we'll need to give it a cron to execute once a day, like, 0 0 16 * * ? which should execute at 4 PM server time.

And that's it! When these two sources and one point are enabled, your data will state appearing in your SQL table when the script executes. Here's the JSON for my test configuration:

{
   "dataSources":[
      {
         "xid":"DS_f090fc06-3274-4b97-806f-2c6c1402cc4e",
         "name":"ScriptedInserter",
         "enabled":false,
         "type":"SQL",
         "alarmLevels":{
            "POLL_ABORTED":"URGENT",
            "STATEMENT_EXCEPTION":"URGENT",
            "DATA_SOURCE_EXCEPTION":"URGENT"
         },
         "purgeType":"YEARS",
         "updatePeriodType":"MINUTES",
         "connectionUrl":"jdbc:mysql:\/\/localhost:3306\/testInserts",
         "driverClassname":"com.mysql.jdbc.Driver",
         "password":"root",
         "rowBasedQuery":false,
         "selectStatement":"select * from data limit 10;",
         "updatePeriods":5,
         "username":"root",
         "editPermission":"",
         "purgeOverride":false,
         "purgePeriod":1
      },
      {
         "xid":"DS_2fec32f1-672e-4615-8591-2eb30f96422f",
         "name":"PublishAllData",
         "enabled":false,
         "type":"SCRIPTING",
         "alarmLevels":{
            "SCRIPT_ERROR":"URGENT",
            "DATA_TYPE_ERROR":"URGENT",
            "POLL_ABORTED":"URGENT",
            "LOG_ERROR":"URGENT"
         },
         "purgeType":"YEARS",
         "updateEvent":"CONTEXT_UPDATE",
         "context":[
            {
               "varName":"sqlPublishPoint",
               "dataPointXid":"DP_SQL_Insert",
               "updateContext":false
            }
         ],
         "logLevel":"NONE",
         "cronPattern":"0 0 16 * * ?",
         "executionDelaySeconds":0,
         "historicalSetting":false,
         "script":"\/\/Publish all points except the SQL inserter\nvar publishPoints = DataPointQuery.query('ne(xid,DP_SQL_Insert)');\nvar pvd = this.pvd;\nif(!pvd) {\n    pvd = this.pvd = com.serotonin.m2m2.Common.databaseProxy.newPointValueDao();\n}\n\nfunction sqlPublish(fkId, pvtList) {\n    var sqlString = '';\n    var first = true;\n    for(var i = 0; i < pvtList.length; i+=1) {\n        if(first)\n            first = false;\n        else\n            sqlString += \",\";\n        sqlString += \"(\"+fkId+\",\"+pvtList*.value+\",\"+pvtList*.time+\")\";\n        \n        if(sqlString.length > 10000) { \/\/some maximum size, surely\n            sqlPublishPoint.set(sqlString);\n            sqlString = \"\";\n            first = true;\n        }\n    }\n    if(sqlString !== '')\n        sqlPublishPoint.set(sqlString);\n}\n\nCONTEXT.useTimestamp();\nvar sinceTime = CONTEXT.timestamp - CONTEXT.millisInPast(DAY);\nprint(sinceTime);\nfor(var k = 0; k < publishPoints.length; k+=1) {\n    var publishPoint = publishPoints[k];\n    var dpid = com.serotonin.m2m2.db.dao.DataPointDao.instance.getDataPointIdByXid(publishPoint.xid);\n    var values = pvd.getPointValues(dpid, sinceTime);\n\/\/    print(\"SQL publish of \" + publishPoint.name + \" has \" + values.length + \" values\");\n    sqlPublish(fkId, values);\n}",
         "scriptPermissions":{
            "customPermissions":"",
            "dataPointReadPermissions":"superadmin",
            "dataPointSetPermissions":"superadmin",
            "dataSourcePermissions":"superadmin"
         },
         "editPermission":"",
         "purgeOverride":false,
         "purgePeriod":1
      }
   ],
   "dataPoints":[
      {
         "xid":"DP_SQL_Insert",
         "name":"INSERT DATA",
         "enabled":true,
         "loggingType":"ON_CHANGE",
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"INSTANT",
         "purgeType":"YEARS",
         "pointLocator":{
            "dataType":"ALPHANUMERIC",
            "parameters":[
               "INTEGER",
               "DOUBLE",
               "BIGINTEGER"
            ],
            "dateParameterFormat":"yyyy-MM-dd'T'HH:mm:ss",
            "fieldName":"",
            "tableModifier":true,
            "timeOverrideName":"",
            "updateStatement":"insert into data (id, value, time) values (?,?,?)"
         },
         "eventDetectors":[
         ],
         "plotType":"STEP",
         "rollup":"NONE",
         "unit":"",
         "templateXid":"Alphanumeric_Default",
         "chartColour":"",
         "chartRenderer":{
            "type":"TABLE",
            "limit":10
         },
         "dataSourceXid":"DS_f090fc06-3274-4b97-806f-2c6c1402cc4e",
         "defaultCacheSize":1,
         "deviceName":"TestInserts",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623157E308,
         "discardLowLimit":-1.7976931348623157E308,
         "intervalLoggingPeriod":15,
         "intervalLoggingSampleWindowSize":0,
         "overrideIntervalLoggingSamples":false,
         "preventSetExtremeValues":false,
         "purgeOverride":false,
         "purgePeriod":1,
         "readPermission":"",
         "setExtremeHighLimit":1.7976931348623157E308,
         "setExtremeLowLimit":-1.7976931348623157E308,
         "setPermission":"",
         "textRenderer":{
            "type":"PLAIN",
            "useUnitAsSuffix":true,
            "unit":"",
            "renderedUnit":"",
            "suffix":""
         },
         "tolerance":0.0
      }
   ]
}

^ Derived from a working setup but not tested.

[phildunlap]

Another thing to consider is adding another SQL point that runs a delete statement on the table, such that after the script runs only the last day of data is in the table. You would simply write the delete statement in another "Modify table only" point and then set it to any value at all (presuming it has no parameters). You would then want to update the DataPointQuery call so as not to publish that point (not that it would matter much)