After watching my little program monitor the PMS5003 air monitor properly for a few days, it was time to move on to the next steps: migrating the program to a Raspberry Pie and writing data to an SQLite database.
Moving to Raspberry PI (RPI)
The nice thing about Lazaras / Free Pascal is there is very little to do to move a program between architectures – just recompile the source on the ARM architecture.
Typically that works correctly, but the synapse library I’m using for serial I/O had a few issues recompiling on the ARM platform. Primarily it thinks there are some baud rates available that are not. Easy enough to just comment those out.
Connecting the FTDI breakout board was pretty easy as well. All drivers were already installed. After you plug the USB cable into the raspberry pi, use dmsg | grep FTDI to see the port assigned:
You can see that /dev/ttyUSB0 was assigned.
As in part 1, I used putty on the RPI to verify I was getting output from the PMS5003 sensor before going any further.
The only issue with the program written in part 1 was it was hardcoded for ports named COM. I changed the program to allow input of a string rather than a digit so I could use /dev/ttyUSB0 rather than just COM<n>.
With that change, I was receiving sensor data just as I had been with windows:
Allowing non-root Access to /dev/ttyUSB0
One difference with Linux is root access is required to use /dev/ttyUSB0. It is easy enough to allow your user access using usermod:
usermod -a -G dialout $USER
Fixing Control-C Issues
While playing around with the program, I quickly realized there was a very annoying problem. In Linux, if I use control-C to abort the program (which is the only way out), it will not properly close the serial port, making it impossible to restart the program.
When this happens you will see a message like this:
Communication error 9991: Port owned by other process Unable to open communications port. Terminating.
At first, I was forced to reboot the RPI when I received this error, but I found that if you look at the /run/lock directory you will find a file called LCK..ttyUSB0. Delete this file and you will have access to the ttyUSB0 device again.
Although I used try/except to catch any program errors, control-C doesn’t get trapped. In Linux, the program just exits and leaves a mess unlike the Windows version.
To get around this problem, I trap for several Linux signals as the program starts:
// trap for signals that could cause abnormal termination so the com port // can be properly closed if FpSignal(SIGINT, @HandleSigInt) = signalhandler(SIG_ERR) then begin Writeln('Failed to install signal error: ', fpGetErrno); Halt(1); end; if FpSignal(SIGHUP, @HandleSigInt) = signalhandler(SIG_ERR) then begin Writeln('Failed to install signal error: ', fpGetErrno); Halt(1); end; if FpSignal(SIGTERM, @HandleSigInt) = signalhandler(SIG_ERR) then begin Writeln('Failed to install signal error: ', fpGetErrno); Halt(1); end;
These all install the same trap handler for all of the traps:
procedure handleSigInt( aSignal : LongInt ); cdecl; begin writeln; writeln('User requested program termination.'); writeln; abortRequested := true; end; // handleSigInt
Then, the infinite loop that monitors the sensor output watches for abortedRequested to become true:
while not abortRequested do begin
I needed to install SQLite:
sudo apt install sqlite3 libsqlite3-dev
I also like to use the GUI SQL browser, so I installed that:
sudo apt install sqlitebrowser
Creating the Database
I created the database as follows:
>sqlite3 aqmonitor.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> CREATE TABLE observations ( ...> id INTEGER PRIMARY KEY AUTOINCREMENT ...> NOT NULL ...> UNIQUE, ...> date DATETIME NOT NULL, ...> duration INTEGER NOT NULL, ...> pm10Std INTEGER NOT NULL, ...> pm25Std INTEGER NOT NULL, ...> pm100Std INTEGER NOT NULL, ...> pm10Env INTEGER NOT NULL, ...> pm25Env INTEGER NOT NULL, ...> pm100E INTEGER NOT NULL, ...> part03 INTEGER NOT NULL, ...> part05 INTEGER NOT NULL, ...> part10 INTEGER NOT NULL, ...> part25 INTEGER NOT NULL, ...> part50 INTEGER NOT NULL, ...> part100 INTEGER NOT NULL ...> ); sqlite> .schema CREATE TABLE observations ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, date DATETIME NOT NULL, duration INTEGER NOT NULL, pm10Std INTEGER NOT NULL, pm25Std INTEGER NOT NULL, pm100Std INTEGER NOT NULL, pm10Env INTEGER NOT NULL, pm25Env INTEGER NOT NULL, pm100E INTEGER NOT NULL, part03 INTEGER NOT NULL, part05 INTEGER NOT NULL, part10 INTEGER NOT NULL, part25 INTEGER NOT NULL, part50 INTEGER NOT NULL, part100 INTEGER NOT NULL ); CREATE TABLE sqlite_sequence(name,seq); sqlite> .quit
This creates fields for all of the data from the sensor. It also has a unique id which is the primary key. If you need to delete/modify a specific record, the Id uniquely identifies the record.
There are also fields for the date&time of the observation, and the length of time observations were averaged to get the record.
Modifying the Program to Create Database Records
SQLite access is easily integrated into Lazarus / Free Pascal using the db, sqldb, and sqlite3conn modules:
I added 3 functions to my original aqMonitor program to handle writing data to the database: dbOpen, dbClose, and dbAdd.
Opening and closing the db is very straightforward so I won’t cover that here. Adding is done each time we compute an average of the observations. When the timer pops, a new average is computed and we write it to the database using dbAdd:
if SecondsBetween(now, avgTimer) >= duration * 60 then begin packet := historyAvg(duration * 60, history); dbAdd(duration, packet); printPacket(packet); avgTimer := now; end;
dbAdd is fairly straightforward as well. It creates an SQL insert statement, then executes it against the database.
The insert command is put into the variable s:
with packet do begin s := 'insert into observations (' + 'date, duration, pm10Std, pm25Std, pm100Std, pm10Env, pm25Env, pm100E, ' + 'part03, part05, part10, part25, part50, part100' + ')' + 'values(' + '"' + FormatDateTime('yy-mm-dd hh:nn:ss', now) + '", ' + inttostr(duration) + ', ' + inttostr(pm10Std) + ', ' + inttostr(pm25Std) + ', ' + inttostr(pm100Std) + ', ' + inttostr(pm10Env) + ', ' + inttostr(pm25Env) + ', ' + inttostr(pm100Env) + ', ' + inttostr(particles03um) + ', ' + inttostr(particles05um) + ', ' + inttostr(particles10um) + ', ' + inttostr(particles25um) + ', ' + inttostr(particles50um) + ', ' + inttostr(particles100um) + ')'; end; // with
Once the string is created, a start transaction is executed (trans), the query is created (q), and finally executed (q.ExecSQL). Exceptions are handled, and if the Insert succeeds, everything is cleaned up in the finally block:
try try trans := TSQLTransaction.Create(nil); trans.DataBase := dbCB; trans.StartTransaction; q := TSQLQuery.Create(nil); q.DataBase := dbCB; q.SQL.Text := s; q.ExecSQL; except on e: EDatabaseError do begin writeln('Error: ' + e.Message); writeln('insert failed.'); writeln(s); raise; end; end; // try except; finally q.close; q.Free; trans.Commit; trans.Free; end; // try finally
Using sqlitebrowser to look at the table:
As I said, writing data to an SQL database is pretty easy.
Handling SQLite Locks
Well, there is one problem with SQLite – database locks. I’ve used SQLite pretty extensively and I like it a lot, but it is pretty stupid when it comes to locking, at least as far as I’m concerned. If it can’t obtain a lock, it will give up and give an error. There is no option to just wait until the other writer released the lock.
On a program like this, I do not want it aborting except for truly exceptional reasons. It is going to be running via cron in the background and I don’t intend to have to babysit it!
In other programs, I get around locking issues by locking my own semaphore before using SQLite. My semaphore lock would wait indefinitely rather than abort the program. That causes all database accessors to be singly threaded thru the semaphore before being allowed access to the database.
But that is too complicated to implement here, and requires all accessors use the semaphore which makes using tools like sqlitebrowser a bad idea.
Since the aqmonitor program has a single insert SQL command, it is fairly easy to simply do several retries myself. So rather than the simple q.ExecSQL I showed you above, what I actually do is this:
// attempt to insert record. If dblocked occurs (error 5), then retry until // succeeds or # of retries are exceeded retries := 0; while true do begin try // insert q.ExecSQL; except on e: ESQLDatabaseError do begin if e.ErrorCode = 5 then begin retries := retries + 1; if retries > maxRetries then begin writeln('Insert failed due to dblock after ', retries, ' attempts.'); raise EInsertFailed.Create ('Insert retries exceeded due to dblock'); end; sleep(retries * 1000); continue; end else raise end; // on end; //try insert break; end; // while
I try the insert (q.ExecSQL). If an exception occurs, I check to see if it is a lock error (errorCode = 5). If it is, I add 1 to the retries count, sleep for a while, then try again. If the number of retries is exceeded, then I raise EInsertFailed to let the caller know I gave up trying to add the record.
Further, in the while loop where dbAdd is called, I count the number of successive dbAdds that fail. If dbAdd fails 10 times in a row, then something is seriously wrong and I abort the program.
This concludes the data capture part of my Air Quality monitor.
The source the the program as it stands now, with the SQL code can be found at:
I’ll let this run for a few days to see if I can get dirtier air than I’ve had the past couple of days. Then I’ll start working on reading the database and generating some graphs to post on a website.