Databases

Swonk prefers PostgreSQL for his database needs. PostgreSQL is a fully featured and well documented SQL database. Swonk isn't a heavy duty database developer or administrator, so having a database that is simple to set up and has clear and accurate documentation is essential.

When installing Postgresql on OpenBSD using the package manager, there are some manual steps required.

pkg_add postgresql-server: as root, install the package
su - _postgresql: become the postgres user
initdb -D /var/postgresql/data: create the database as the postgres user
rcctl enable postgresql: as root, enable postgresql in the rc.conf for next startup
rcctl start postgresql: as root, run the startup script for postgresql
su - _postgresql: become the postgres user again
psql: connect to the database to create the users and schemas/databases etc the are needed

Swonk has some sensors that log temperature and dewpoint into a PostgreSQL table.

               Table "public.environment"
   Column    |           Type           |   Modifiers   
-------------+--------------------------+---------------
 temperature | numeric                  | 
 dewpoint    | numeric                  | 
 time        | timestamp with time zone | default now()
 location    | text                     |

Data is inserted every minute if the sensor is working. For reporting Swonk wanted five minute intervals. Extensive use of Google garnered a few hints and eventually a query was formed.

select
 concat(to_char(date_part('hour',time),'00'),':',
        to_char(floor(date_part('minute',time)/5)*5,'00')) as minutes, 
 round(avg(temperature),1) as temp, 
 round(avg(dewpoint),1) as dew 
 from environment 
 where  time > now() - interval '1 day'
 group by date_part('hour',time), floor(date_part('minute',time)/5)*5 
 order by date_part('hour',time), floor(date_part('minute',time)/5)*5

This groups the data by hour and five minute intervals for the previous day. The floor function gets the data starting at each five minute interval. The hints offered by Google used the round function, but that messed up the first and last intervals.

Using the C library

Postgres has a client library for the C programming language. Swonk was bored, so wrote a program to accept data from a UDP port and insert it into the database. The data comes from an Arduino Nano with a DHT21 temperature/humidity sensor and an ethernet adapter.

First we get the UDP listener working so we know we have data incoming. A very basic socket program that opens a sockect and listens for a UDP packet. It is compiled with:

gcc udp-listener.c -o udp-listener

Next we get the JSON parser working so we can get at the individual fields. It is compiled with:

gcc udp-json.c -ljson-c -o udp-json

Finally we get the database connection and insert going. It is compiled with:

gcc udp-json-pg.c -I /usr/include/postgresql -lpq -ljson-c -o udp-json-pg

Direct JSON output

A common scenario is having a web page front end drawing data from a database. This can be done in a number of different ways, but the 'modern' solution uses javascript fetching data from a CGI, PHP, or similar middle layer. As javascript has JSON to ease the importing of data into native data structures, that is the obvious way to do things.

I recently looked at the middle layer I had been using and thought it needed a rethink. I had a collection of perl CGI scripts, one for each different query.

First I developed a series of queries that used the native Postgres JSON functions. This simplified the middle layer as it didn't need to generate the JSON, merely pass through to data from the database response.

First I got the query returning a JSON object for each row.

select row_to_json(t) from ( 
 select serial,date,registration,duration from logbook 
  order by serial desc
  limit 3
) t;

This generates a response like:

                              row_to_json                               
------------------------------------------------------------------------
 {"serial":5977,"date":"2023-04-02","registration":"OD","duration":230}
 {"serial":5976,"date":"2023-03-26","registration":"OD","duration":187}
 {"serial":5975,"date":"2023-03-24","registration":"NE","duration":20}
(3 rows)

That's a good first step, and would do for a single line response. For a multi row response I tried:

select json_agg(t) from ( 
 select serial,date,registration,duration from logbook
  order by serial desc 
  limit 3
) t;

This generates a JSON array of objects.

                                 json_agg                                  
---------------------------------------------------------------------------
 [{"serial":5977,"date":"2023-04-02","registration":"OD","duration":230}, +
  {"serial":5976,"date":"2023-03-26","registration":"OD","duration":187}, +
  {"serial":5975,"date":"2023-03-24","registration":"NE","duration":20}]
(1 row)

I changed the perl CGI scripts to a single FCGI script that takes a parameter to select which query to use. Parameters are passed as JSON rather than using form data. The script just adds a name, wraps it up with some curly brackets, and passes it on.