PMS5003 Air Quality Monitor Part 2 – Raspberry Pi and SQLite

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

Installing SQLite3

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:

http://www.xyfyx.com/files/aqMonitor2.zip

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.

This entry was posted in c-electronics, c-lazarus and tagged , , , . Bookmark the permalink.

2 Responses to PMS5003 Air Quality Monitor Part 2 – Raspberry Pi and SQLite

  1. Hans Otten says:

    Have a look at the latest LazSerial and discussions on the lazarus forum., It has fixes for the missing baudrates issue for Linux in Synapse and detecting COM ports.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.