#!/usr/bin/perl -w
# This is the CS3 webservice which interacts with remote networks to receive data and send actuation commands
# It responds to http (port 80) requests from snetbase.pl running on a CS3 gateway.
# It stores data and metadata in an sqliteDB which it uses to communicate with a web app with a U.I. for control
# Revising 01/12/13 to send commands to XBee Gateway in response to web client input
# Adding sqlite tables and storage here. 1/20/13 M.H.
# Adding variable lengths multi-parameter reception and storage to also encompass eKo Pro networks 4/19/13 M.H.
# Forked snetServe2 to snetServe3 to safely continue work on web commands/actuation 6/30/13 M.H.
# snetbase.pl now has code working to send remote_at commands to remote nodes causing digital pins to switch.
# Node 123 on pcD5 uses a Seed Studio Relay shield and relay stack to switch a 30Amp 220V relay. 7/3/13
# Got actuation working. 7/10/13 M.H.
# Working on closing actuation loop, execute each command only once, record result/response from gateway 7/23/13 Wash. M.H.
# Reducing response verbiage to clean up snetbase.pl monitoring display. M.H. 11/29/13 and adding base radio config
# Send ND query commands only once, mark as sent and don't wait for success. M.H. 12/14/13
# Added Reception of "pConfig" packets 1/28/14 M.H. Added store to ports table. 1/29/14 M.H.
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use strict;
use POSIX;
use DBI;
use RRDs;
print header;
my $progInfo = "snetServe3.cgi Rev 1/29/14";
my $rrdDir="/home/user/RRDs/";
my $sqliteDBdir = "/home/user/sqliteDB/";
my $sqliteDB = "cs3.sqlite";
my $rrdPrefix = ("RRX"); #This corresponds to a type of RRD, sampling interval, data types etc...
my $rrdStorageInterval = 15; #Seconds between samples in the RRD
my $key = "";
my $cmdExpireTime = 7200 ; #Length of time after which to give up on a command.
my %dsID = ( # This hash stores the elements which identify a specific data source
'netID', "", # Three Letter Acronym for the network
'nodeID', "",
'port', "",
'sensorType', "",
);
#************** Create or connect to the sqlite DB *******************************************
my $dbh = DBI->connect("DBI:SQLite:".$sqliteDBdir.$sqliteDB)
or print "Couldn't connect to database: ". DBI->errstr."\n";
#************** Create Tables if they don't already exist ************************************
if (! tableExists($dbh,"nodes")) {
my $sth = $dbh->prepare('CREATE TABLE nodes(netID INTEGER, nodeID INTEGER, nodeName TEXT, location TEXT, atTime INTEGER)')
or print "Couldn't prepare nodes table creation statement: " .$dbh->errstr."\n";
$sth->execute()
or print "Couldn't execute create nodes table " . $sth->errstr."\n";
}
if (! tableExists($dbh,"ports")) {
my $sth = $dbh->prepare('CREATE TABLE ports(netID INTEGER, nodeID INTEGER, portID TEXT, atTime INTEGER, configStr TEXT)')
or print "Couldn't prepare ports table creation statement: " .$dbh->errstr."\n";
$sth->execute()
or print "Couldn't execute create ports table " . $sth->errstr."\n";
}
if (! tableExists($dbh,"networks")) {
my $sth2 = $dbh->prepare('CREATE TABLE networks(networkID INTEGER, networkName TEXT, localIPaddr TEXT, atTime INTEGER)')
or print "Couldn't prepare networks table creation statement: " .$dbh->errstr."\n";
$sth2->execute()
or print "Couldn't execute create networks table " . $sth2->errstr."\n";
}
if (! tableExists($dbh,"commands")) {
my $sth = $dbh->prepare('CREATE TABLE commands(NetID INTEGER, nodeID INTEGER, command TEXT,targValue TEXT, cmdTime INTEGER, respTxt TEXT, respInt INTEGER, respTime INTEGER )')
or print "Couldn't prepare commands table creation statement: " .$dbh->errstr."\n";
$sth->execute()
or print "Couldn't execute create commands table " . $sth->errstr."\n";
}
#********************************* MAIN ***************************************************************
$| = 1; # enable auto-flushing
#Extract info from the URI
my $packetType = param('packetType');
my $netID = param('netID'); #too often undefined
# need to set $netID to zero or something here if undefined.
my $atTime = param('atTime'); #unix time
#Detect what type of info is in the URI and take action
# Types of information which might be in a URI sent to this service
#data Raw data packet
#node Node info packet
#network Network info packet
#network status Network/node status packet
#command request
#command response
#port config
print "$progInfo at $ENV{SERVER_ADDR} Received URI from $ENV{REMOTE_ADDR}, Network: $netID \n";
#******************************************** data Packet ************************************************************
if ($packetType eq "data") { # Receive data from a node/port and store to RRD
my $nodeID = param('nodeID');#low 32 bits of the node serial number in decimal form
my $port = param('port');
my $sensorType = param('sensorType'); # this is superfluous
print " Node $nodeID, Port $port, SensorType $sensorType at Time $atTime \n";
#Extract data from the URI which looks like this:
# ?packetType=data&netID=CAM&nodeID=35&port=0&sensorType=2&internalTemp=17.948090&batteryV=3.816346
# &solarV=3.950625&externalV=0.151947&packetNumber=36&vRef=416&lastHeard=1366468268
my @names = param(); #param() returns an array containing variable names from the HTTP string like above.
my @values = ();
foreach my $name (@names) {
my $value = param($name);
@values = (@values, param($name));
#print "\n$name -- $value";
}
# This is where the data gets put into the Web Server's RRDtools database.
my $RRDexists = checkForRRD($netID, $nodeID, $port, $sensorType);
if(!$RRDexists) { #if no RRD exists for this data source create one
print "Need to create an RRD for this data source\n";
createRRD($netID, $nodeID, $port, $sensorType, @names);
}
addDataToRRD($netID, $nodeID, $port, $sensorType, @values);
# addDataToSQLiteDB($netID,$nodeID,$port,$sensorType,@values);
# Store the data to sqlite database. (node_snl INTEGER, time TEXT, parameterName TEXT, value REAL)
# Would need to change this to handle variable numbers of data values/sensor.
# Sqlite is too slow to retrieve large volumes of data from anyway. Last values are useful.
# Can Get last values from RRDtools
# my $sth4 = $dbh->prepare('INSERT INTO data VALUES (?,?,?,?)')
# or print "Couldn't prepare data table creation statement: " .$dbh->errstr."\n";
# if ($sth4->execute($nodeid,my $CAtime=localtime(time),$paramName,$paramVal)) {
# print "Successful store of data to remote sqlite table \n";
# }else {
# print "Couldn't store data to sqliteDB cs3.sqlite-table data " . $sth4->errstr."\n";
# }
#******************* node Packet ************************************************************************************************
}elsif ($packetType eq "node") {# Receive info about a node and store to $sqliteDB:nodes
my $nodeID = param('nodeID');
my $nodeName = param('nodeName');
my $location = param('location');
# Store node info to the 'nodes' table
print " Received node packet from $ENV{REMOTE_ADDR}, XBee Network $netID node $nodeID named $nodeName at Time $atTime \n";
my $sthNode = $dbh->prepare('INSERT INTO nodes VALUES (?,?,?,?,?)')
#nodes table row definition (netID INTEGER, nodeID INTEGER, nodeName(ni) TEXT, Location TEXT, atTime INTEGER)
or print "Couldn't prepare nodes table insertion statement: " .$dbh->errstr."\n";
$sthNode->execute( $netID , $nodeID , $nodeName, $location, my $CAtime=time)
or print "Couldn't execute insert node info into nodes table " . $sthNode->errstr."\n";
# end else if packet type=node
#******************* Port Config Packet ************************************************************************************************
}elsif ($packetType eq "pConfig") {# Receive info about sensors connected to a port and store to $sqliteDB:nodes
my $nodeID = param('nodeID');
my $portID = param('port');
my $sensorType = param('sensorType');
my $atTime = param('atTime');
my $configStr = param('configStr'); # This is the unparsed config string from an arduino on a node. Could be anything in it.
# Store port configuration info to the 'ports' table; just a big string for now.
print " Received Port Config packet from node $nodeID port $portID at Time $atTime \n";
print " Received String: $configStr \n";
#ports table row definition (netID INTEGER, nodeID INTEGER, portID TEXT, atTime INTEGER, configStr TEXT)
# Need to do an update here if an entry already exists for this net/node/port/ or an insert if not.
my $sthPort = $dbh->prepare('SELECT * FROM ports WHERE netID = ? AND nodeID = ? AND portID = ? ')
or print "Couldn't prepare ports upsert statement: " .$dbh->errstr."\n";
$sthPort->execute( $netID , $nodeID , $portID)
or print "Couldn't execute upsert port info into ports table " . $sthPort->errstr."\n";
my $portRowHashRef = $sthPort->fetchrow_hashref();
if($portRowHashRef) {
my $sthPort = $dbh->prepare('UPDATE ports SET atTime = ?, configStr = ? WHERE netID = ? AND nodeID = ? AND portID = ? ');
$sthPort->execute( $atTime, $configStr, $netID , $nodeID , $portID);
}else {
my $sthPort = $dbh->prepare('INSERT INTO ports VALUES (?,?,?,?,?)');
$sthPort->execute( $netID , $nodeID , $portID, $atTime, $configStr);
}
# end else if packet type=pConfig
#******************** network Packet *********************************************************************************************
}elsif ($packetType eq "network") { # Receive info about a network and store to $sqliteDB:networks
my $networkName = param('networkName');
my $localIPaddr = param('localIPaddr');
my $atTime = param('lastHeard');
my $sthNet = $dbh->prepare('INSERT INTO networks VALUES (?,?,?,?)')
#nodes table row definition CREATE TABLE networks(networkID INTEGER, networkName TEXT, localIPaddr TEXT, atTime INTEGER)
or print "Couldn't prepare networks table insertion statement: " .$dbh->errstr."\n";
if (!$networkName) { # if no label found just store 'unknown'
$networkName = "unknown";
}
if ($sthNet->execute( $netID, $networkName, $localIPaddr, $atTime)) {
print "Successful store of network $netID and $localIPaddr to remote sqlite TABLE \n";
}else {
print "Couldn't execute insert network info into networks table " . $sthNet->errstr."\n";
}
# end else if packet type=network
#****************** command request *****************************************************************************************
}elsif ($packetType eq "commandReq") {# Gateway ready for command, go ahead and send latest command entered
# Should Go through the command table and mark commands older than $cmdTimout as "failed".
# Pull the oldest command not yet sent from the commands table and send it to snetbase.pl on the gateway.
my $netID = param('netID');
my $timeThresh = time - $cmdExpireTime; #commands before this time are abandoned. Should mark as such in the table.
#print "time threshold = $timeThresh\n";
my $sth2 = $dbh->prepare('SELECT NetID, nodeID, command, targValue, cmdTime FROM commands WHERE NetID = ? and respTxt != "success" and respTxt != "sent" and cmdTime > ?')
or print "Couldn't prepare most recent command select: " .$dbh->errstr."\n";
$sth2->execute($netID, $timeThresh)
or print "Couldn't execute SELECT command " . $sth2->errstr."\n";
my $commandRowHashRef = $sth2->fetchrow_hashref();
#print "$commandRowHashRef->{command}$commandRowHashRef->{targValue}$commandRowHashRef->{NetID}$commandRowHashRef->{nodeID}$commandRowHashRef->{cmdTime}\n";
if( $commandRowHashRef && $commandRowHashRef->{command} && $commandRowHashRef->{nodeID}) {
print "$commandRowHashRef->{command}$commandRowHashRef->{targValue}$commandRowHashRef->{NetID}$commandRowHashRef->{nodeID}$commandRowHashRef->{cmdTime}\n";
# if command is sent, especially ND mark it as sent and don't send again.
my $unixTimeNow = time;
my $sth5Command = $dbh->prepare('UPDATE commands SET respTxt = ?, respTime = ?, respInt = ? WHERE nodeID = ? AND cmdTime = ?') # need to update a specific$
#commands table row definition (netID INTEGER, nodeID INTEGER, targValue INTEGER,cmdTime INTEGER,respTxt TEXT,respInt INTEGER,respTime,INTEGER)
or print "Couldn't prepare commands table insertion statement: " .$dbh->errstr."\n";
my $commandResp = "sent";
my $valueX = $commandRowHashRef->{targValue};
my $nodeID = $commandRowHashRef->{nodeID};
my $cmdTime = $commandRowHashRef->{cmdTime};
if ($sth5Command->execute($commandResp, $unixTimeNow,$valueX, $nodeID, $cmdTime)) {
#can't print here or it will be interpreted as part of the command. These prints are for debug only.
#print "Successful store of command update \"$commandResp\", node $nodeID value $valueX to cloud sqlite table \n";
}else {
#print "Couldn't execute insert command response into commands table " . $sth5Command->errstr."\n";
} # end if found command to send
}# end else if packet type=commandReq
#******************** command response ***************************************************************************************
# need a means to tie this to a specific command and modify that command's table entry. Serial Number? frame_id?
}elsif ($packetType eq "commandResp") {# Receive info about a command execution and store to cs3.sqlite:commands
# also store any info about a node's configuration that is returned in cs3.sqlite:nodes
my $netID = param('netID');
my $nodeID = param('nodeID');#low 32 bits of the node serial number in decimal form
my $port = param('port');
my $commandResp = param('result'); # This is the actual response string from the gateway
my $cmdTime = param('cmdTime');
my $valueX = param('value'); # This is the value returned if command was a query
print "camnets Received command Response= $commandResp for node $nodeID command originated at $cmdTime \n";
my $unixTimeNow = time;
my $sthCommand = $dbh->prepare('UPDATE commands SET respTxt = ?, respTime = ?, respInt = ? WHERE nodeID = ? AND cmdTime = ?') # need to update a specific command here.
#commands table row definition (netID INTEGER, nodeID INTEGER, targValue INTEGER,cmdTime INTEGER,respTxt TEXT,respInt INTEGER,respTime,INTEGER)
or print "Couldn't prepare commands table insertion statement: " .$dbh->errstr."\n";
#if (!$nodeName) { # if no label found just store 'unknown' also store location unknown for now.
#$nodeName = "unknown";
#}
if ($sthCommand->execute($commandResp, $unixTimeNow,$valueX, $nodeID, $cmdTime)) {
print "Successful store of command response $commandResp and value $valueX to remote sqlite table \n";
}else {
print "Couldn't execute insert command response into commands table " . $sthCommand->errstr."\n";
}
# In the commands table mark the command as processed or failed if too long for response To Do.
} # end elseif packet type=commandResp and whole packet parser.
#**************** end of packet Parser ***********************************************************************************
#print "End $progInfo at $ENV{SERVER_ADDR} Response to $packetType packet from Network: $netID at $ENV{REMOTE_ADDR}\n";
# end of main program **********************************************************************
#********************** Function Implementations ********************************************
#********************** Test to see if a table already exists *******************************
sub tableExists { # takes two parameters, database handle and tablename string
#print "Checking for table $_[1] \n";
my @tableNames = $_[0]->tables("main", '%', "$_[1]" ,"TABLE")
or print "table $_[1] doesn't exist yet " .$_[0]->errstr."\n";
#print "Checked for table $_[1] \n";
if (@tableNames) {
#print "Table $_[1] exists; ";
return 1;
} # end if
} # end sub tableExists() *******************************************************************
#*********************************************************************************************************
# checkForRRD() Takes a data source tuple and sees if the corresponding RRD file
# exists; Looks in $rrdDir for the RRD
# RRD Name format is RRDprefix.netID."n".nodeID."p".port."s".sensorType
#*********************************************************************************************************
sub checkForRRD {
my $RRDfileStr = ($rrdDir.$rrdPrefix.$_[0]."n".$_[1]."p".$_[2]."s".$_[3].".rrd");
if(-e $RRDfileStr) {
#print "\n$RRDfileStr already exists\n";
return 1;
}else {
print "\n$RRDfileStr Not present\n";
return "";
}
}
#*******************************************************************************************************
# createRRD() Takes a data source tuple and parameter list and Creates a Round Robin Data
# Base for that data source.
# The RRD Name format is RRDprefix.netID."n".nodeID."p".port."s".sensorType
#*******************************************************************************************************
sub createRRD {
my $RRDfileStr = ($rrdDir.$rrdPrefix.$_[0]."n".$_[1]."p".$_[2]."s".$_[3].".rrd");
my $start=time;
# Make space for a data point every $rrdStorageInterval seconds in the RRD
# create a data source statement for every parameter, create a string for all of them together
my @DSources = ();
my $dsIndex = 10; #Index of the first parameter name in the $_ parameter array
# need to skip over 4-tuple twice and atTime (lame)
# depends on order of parmeters in the tuple.
while ($_[$dsIndex]) {
@DSources = (@DSources,"DS:".$_[$dsIndex].":GAUGE:1000:U:U");
print "parameter $dsIndex =$_[$dsIndex]";
$dsIndex++;
}
print ("\nDSources =", @DSources);
RRDs::create ($RRDfileStr, "--start",$start-1, "--step",$rrdStorageInterval,
@DSources,
"RRA:LAST:0.5:1:7262482", #one packet/pt, 2524964 packets 2 months From newer
"RRA:LAST:0.5:96:42826", #400s/pt.
"RRA:LAST:0.5:4:1223837,"); #16s/pt.
print "\nSuccessful creation of $RRDfileStr";
my $ERROR = RRDs::error;
die "$0: unable to create $RRDfileStr : $ERROR\n" if $ERROR;
return 1;
}
#*******************************************************************
# addDataToRRD() Take a Data Source tupple and an array of values
# and store the data in the RRD for that data source
#*******************************************************************
sub addDataToRRD {
my $RRDfileStr = ($rrdDir.$rrdPrefix.$_[0]."n".$_[1]."p".$_[2]."s".$_[3].".rrd");
#print "Adding data to $RRDfileStr \n";
my $ERROR = "";
my $dvIndex = 10; # need to skip over 4-tuple twice and atTime (lame)
# depends on order of parmeters in the tuple.
my $DValuesStr = ("N");
# problem here, if a data value is zero $_[x] tests as false and the value doesn't get inserted
# into the data value string that gets passed to RRDs::updatev; must test for the value to be defined.
# The defined state is more specific than the test for true state.
# while (($_[$dvIndex]) || ($_[$dvIndex]) eq "0") { This didn't work, lots of undefined errors.
while (defined $_[$dvIndex]) {
$_[$dvIndex] = $_[$dvIndex]; # could add zero to force string conversion to a number
# strings with leading nulls get read as the null string.
# but port may be a numerical 0 which is an ascii null
# data in the string will be lost however, it will store
#print "dataparam = $_[$dvIndex]\n";
$DValuesStr = ($DValuesStr.":".$_[$dvIndex]);
$dvIndex++;
}
#print "DValues string = $DValuesStr \n";
RRDs::updatev $RRDfileStr, $DValuesStr;
if ($ERROR = RRDs::error)
{
die "$0: unable to update `$RRDfileStr': $ERROR\n";
}
else
{
print "Successful store to RRD $RRDfileStr \n";
}
my $rrdWrite = $RRDfileStr."write";
system("touch $rrdWrite");
#print "Touched $rrdWrite\n" ;
return "1"; # success
} #end addDataToRRD()