Introduction
With the intent to build a small plant, I had the necessity to control it. With the whole functions required from a basic SCADA. I think that, a lot of you, with research intent, school, hobby, need to supervise, control and of course acquire the data of a system.
Safety and functional needs require:
- Control of the variables with hysteresis (threshold ON != threshold OFF)
- Control of the variables with feedback
- Set the thresholds On or OFF and the feedback "Setpoint" run-time, via PC
- Actuate the corrections
- Detect the local actions (such push of a button, example: a pump will be activated when the tank is full, or when I push a button, because I want to empty it, and I can activate the pump also from PC)
- If something is activated manually, show a warning
- And important for the improvement saves in DB .
Background
To allow the communication between my PC and my controller, there is the need to map the variables.
With an array we assign automatically a number to each variable. But which variables? All those we are interested to set or also to read.
Therefore in the variable register takes place :
- Threshold Limits and Set-points
- Status (on/off)
- Analog and digital reads
- Each sensor
Now we organize the communication.
I need a protocol. Which defines the meaning of a 8 byte token. With this token Controller and Pc communicate.
This protocol is included in the source code in the class "Sprotocol".
Arduino
SCADA systems, and SPADA for sure, are massively influenced from the system that monitor. And a certain point you have to recognize the nature of your variable. But is possible define a template: this is a Template of code for Arduino.
#include <EEPROM.h>
enum variables{ SYSTEM=0,
LIGHT_ON=1, LIGHT_OFF=2 LIGHT_READ=3,
L1=4, };
#define VARIABLES_COUNT 10
enum msg_Type {
};
struct register_record
{
long value;
byte warnings;
register_record()
{
value=0;
warnings=0;
}
boolean GetWarning(byte i)
{
return (warnings & (1 << i))>0;
}
void SetWarning(byte i)
{
warnings= warnings ^ (1 << i) ;
}
}v[VARIABLES_COUNT ];
void SendMessage(byte a, byte *value ,byte msg_type)
{
Serial.write(cnctBegin);
....
);
}
In the loop function is possible recognize clearly the flow such in picture:
- switch inputs from Pc , and store it in the register
- Catch local inputs (local inputs, are build on loco, the code depend from your system, but the input must also be stored in a variable)
- Read the sensors, levels, ecc. and store in a variable
- NOW Process the variables, actuate the action, and store the result in a variable
- Send Reports(all the values, reads, levels, inputs)
if (Serial.available()>7 )
{
if (Serial.read()==cnctBegin) {
action=Serial.read(); i=Serial.read(); bi[0]=Serial.read(); bi[1]=Serial.read();
bi[2]=Serial.read();
bi[3]=Serial.read();
what =bToI(bi); ...
Visual Basic
Arduino will send me therefore, each "DELAY" millis(), the status of the register . The frequency of Arduino, is not the same of the saves in database. Who needs 10 saved values for second? In the program, there is a timer, now set to 1 second, that open the "gate" to the database.
PC catches these variables, and can understand what happen in the controller, and if he stores them in a buffer can draw the temporal graphic.
How :
- VB has available System.IO.Ports.SerialPort, I create a derivative class, Sprotocol.
- SerialPort, raise an event when the message is incoming. I catch it, and I compose back my byte
- I raise a new event that makes available the "readable message" outside the class
Public Class SProtocol
Inherits System.IO.Ports.SerialPort
...
Private Sub SProtocol_DataReceived(value As Object, e As System.IO.Ports.SerialDataReceivedEventArgs) Handles Me.DataReceived
...
RaiseEvent MessageReceived(MainBuffer(1), MainBuffer(2), v)
...
End Sub
End Class
For example, in my Form: I will have;
If msg_type = SProtocol.msg_Type.ask_REPORT Then
Select Case var
Case variables.LIGHT_OFF
nuOff.Value = value dImg.NewPoint(variables.LIGHT_OFF, Now(), value) Case variables.LIGHT_ON
nuOn.Value = value
dImg.NewPoint(variables.LIGHT_ON, Now(), value)
end select
end if
It is important to say: SerialPort works ThreadSafe, this implicate that the function that handles the event, can not "communicate" with the form class
It will done via the function Invoke that will send the event finally to my ProcessMessage function:
Private Sub com_MessageReceive(var As SProtocol.variables, type As SProtocol.msg_Type, value As Long) Handles com.MessageReceive
Invoke(New MsgRecived(AddressOf ProcessMessage), var, type, value)
End Sub
The Graphic
The base class was taken from Carl Morey
http://www.codeproject.com/Articles/42557/Arduino-with-Visual-Basic
I did a lot of improvements. Now is fully re-sizable, and allow to draw more variables in the same graphic.
How use it
Declare else where a normal PictureBox control, then a variable such:
dim dImg as Display
dImg = New Display(Me.PictureBox1.Width, Me.PictureBox1.Height)
Me.PictureBox1.Image = dImg.GetImage
Each line, need is own buffer. Therefore we must pass these information with:
dImg.AddVariable(variables.LIGHT_READ, Color.Lime , "Light Read")
At this point the graphic is ready to accept values:
dImg.NewPoint(variables.LIGHT_OFF, Now(), value)
Instead of the points it can accept also a data table, incoming from a query for example:
img.setDataTable(dt)
It will recognize the columns with a numeric value and draw the graphic consequently.
IMPORTANT: the first column of the datatable must be a datetime value. And ever crescent. If two fields have the same value could be raised an exception.
then the order of the other columns is not important, it will draw a graphic for each numeric column.
The need to draw more variables and to draw datatables, comes late. Therefore the class is not optimized for this purpose. I just adapt the old class to work with more graphics, but there is margin to improve.
The Database
The database is FIREBIRD.
I think no database is more indicated for this purpose. Embedded(does not need to install nothing), free, extremely powerful, and what normally let say you: "uff", such the case sensitivity, when you work with numbers does not boring. So lets enjoy this incredible powerful db.
The GUI
The GUI is just a text box where write the queries for select, insert, update and delete.
As told you, the graphic, will read the datatable, (where the First column is the time,remember) and track the relative graphic.
The target of this program is qualified people, with programming skills. I think it is not a problem "speak" with the program via SQL. Better, it is incredible powerful. You can make each kind of setting.
Below I will show you some important queries, such the one in the picture, that allow you to re-build the tuple of a given moment. And determine the whole system in a certain moment.
The functions are:
1)Queries: Inserts in the textbox the SQL instruction :
Select trim(name),qryString from queries
this instruction calls the table queries where you can choose a saved command, make copy-paste ready to lunch.
2) Run: It executes the SQL instruction in the textbox
3) Quota Qry: it prepares the text in the box, ready to be inserted in the database with quotation marks and the clause "INSERT" . For example you write :
Select t , val as Pressure from dates
if you click Quota Qry in your box appear:
insert into queries(name,qrystring)
values('NAME HERE',
'Select t , val as Pressure from dates ;');
where you have to to put the "Name" where indicated, and then click Run.
4) Backup It make a backup of your database in a .sql file: nothing else than a text file, that contains the SQL instruction (such CREATE TABLE...bla bla) to rebuild your database. If you open this file, you may gotta scared, for all those strange instruction at the begin. You can look the explanation in my site. It makes the back up of whole database.
5)Export to Excel: Exactly, it export to excel. Buuuut just the query in the textbox (no need to run).
Getting start with SQL
If you wanna begin to make queries, you must know the structure.
The tables are just three and are extremely simple. Two are for the data logging and the third is where we can store the queries.
IMPORTANT :Firebird, has no autoincrement value. But works with triggers and generators. Here such info's about. The tables variables and queries, have the column i with a trigger for the autoicrement. The backup will include also these instructions.
CREATE TABLE variables (
i INTEGER NOT NULL, name VARCHAR(255) ,
description BLOB SUB_TYPE 1 ,
um VARCHAR(5) ,
CONSTRAINT PRIMARY KEY(i)
);
Variables contains the descriptive definition of the variables... such, the name and description. It has a trigger associated to i, that is also his primary key (if you set all right,i will be the same value that you send to Arduino, for require his register)
CREATE TABLE dates (
var INTEGER NOT NULL,
t TIMESTAMP NOT NULL,
val BIGINT ,
CONSTRAINT PRIMARY KEY(var,t)
);
CREATE INDEX ixVal on table dates(val);
Dates is the scope of the work, contains the tuples that come from Arduino. var is the foreign key that point to i of the table Variables. T is timestamp. And val is the value.
Dates is fully indicized. The primary key is composed from the columns (var, t) and there is also an addition index on val.
Dates can become huge. These index are important.
And a third table called queries store our SQL instructions:
CREATE TABLE queries (
i INTEGER NOT NULL,
name VARCHAR(255) ,
qrystring BLOB SUB_TYPE 1 ,
CONSTRAINT PRIMARY KEY(i)
);
Getting serious with SQL
It is important look at, the same time, all the graphics of all the variables to identify for example correlation between cause and effects or if your system start up, when happen something wrong. So we need the tuple of all variables at the same time. The number of variable could be huge, and not possible to determine in develop time. Instead to put more columns in the table I just put a column, but each tuple has the same insertion time. Virtually is like the system insert all the variables contemporaneous at the same millisecond. (VB make this. is commented in the source code)
The query to rebuild the tuple is the follow:
SELECT a.T, a.VAL as Light_on,b.val as Light_off , c.val as Light_read,(500*d.val) as L1,e.val as pressure_on,
f.val as pressure_off, g.val as pressure_setpoint
FROM DATES a
inner join DATES B on a.t=b.t
inner join DATES C on a.t=c.t
inner join DATES D on a.t=d.t
inner join DATES E on a.t=e.t
inner join DATES F on a.t=f.t
inner join DATES G on a.t=g.t
where a.t between '23.03.2014 14:00:00' and '23.03.2014 18:00:00'
AND a.var = 1
AND b.var = 2
AND C.var = 3
AND D.var = 4
AND E.var =5
AND F.var=6
AND G.var=7
order by a.t;
Pratically I put so much join of the table dates as the number of variables that I wanna display.
Tricks:
- give an alias at the column, you will retrieve in the legend
- to scale the graphic, multiply for a factor in the select (useful with the values 1/0)
The strictly "=" in the condition of the join, and the index allow us to have good answers time.
Same thing you can make with the block below:
EXECUTE BLOCK returns( t timestamp,a bigint,b bigint,c bigint, d bigint,e bigint,f bigint,g bigint,n integer)
AS
declare tmpt2 timestamp;
declare tmpVar integer;
declare tmpVar2 integer;
declare tmpVal bigint;
declare tmpVal2 bigint;
declare ctrlFlag integer;
DECLARE cur cursor FOR
(SELECT var,t, val from dates
where t> '22.03.2014 00:00:00' order by t,var ); BEGIN
n=0;
ctrlFlag=254;
OPEN cur;
fetch cur INTO tmpVar,t,tmpVal;
IF (ROW_COUNT = 0) THEN exit;
while (1=1) do
BEGIN
ctrlFlag=bin_xor(ctrlFlag,bin_shl(1,tmpVar));
fetch cur INTO tmpVar2,tmpt2,tmpVal2;
IF (ROW_COUNT = 0) THEN leave;
IF (tmpVar=1) THEN a=tmpVal;
IF (tmpVar=2) THEN b=tmpVal;
IF (tmpVar=3) THEN c=tmpVal;
IF (tmpVar=4) THEN d=tmpVal;
IF (tmpVar=5) THEN e=tmpVal;
IF (tmpVar=6) THEN f=tmpVal;
IF (tmpVar=7) THEN g=tmpVal;
if (tmpt2<>t ) then
BEGIN
if (ctrlFlag=0) then
begin
n=n+1;
suspend;
end
ctrlFlag=254;
END
tmpvar=tmpVar2;
t=tmpt2;
tmpVal=tmpVal2;
END
if (ctrlFlag=0) then
begin
n=n+1;
suspend;
end
close cur;
END
When I spoke about power of firebird, this is an example. Firebird supports a complete and powerful procedural language. with which you can do a lot of things. This is a EXECUTE BLOCK. It allow to execute a block of instructions without save any procedure. MySQL does not support it. This block results a bit, more fast than the SELECT : 0.160 ms the SELECT, 0.120, the BLOCK, in a query of 1700 elements for column (so around 11900 elements).
Maybe I have to spend few words about the ctrlFlag.
Work with tuple, imposes to check if all the variables have been set, or saved, or if are already saved. Each variable has an index. If a put a flag 1 at the place of required variable I can properly activate or deactivate his flag. For example:
I did not require system, therefore is 0. I require all the others that are 1.
At end of all. If all values are set to zero, ctrlFlag will be equal to 0.
