Since we wrote Receiving QSOs over the network from N1MM one year ago, the system has been surprisingly stable in reception of new QSOs from N1MM. Our IRC channel has enjoyed access to fresh QSO statistics during busy contests.

LB3YH Arne has this fall put up a Raspberry Pi-based system for displaying various kinds of information on screens we have mounted at our club locales. It was tempting to put up some live QSO statistics on these screens, but the local file-based nature of the database put too strict limitations to what we were able to do without being forced to do terrible workarounds with dire consequences.

It was therefore time to improve on the reception system, and move the database from the file-based sqlite database to a more wholesome postgresql database on a central Samfundet server. We’ve put the full project up at GitHub.

Database

While migrating, we also wanted to do some improvements to the database setup itself. We wanted to base it on two main principles.

1. The full edit history in the database should be retained.
(Heavily inspired by/copied/straight up stolen from an SQL database setup for electronic logging made by LA6YKA somewhere in 2008-2011.)

First, the unique key of a record is specified to be the fields (qsoid, modified), where qsoid is a unique ID and modified is the timestamp for when the QSO last was modified. Instead of deleting or updating existing records, we let updates and deletes insert new records with the same qsoid and more recent modified timestamp. Only new QSOs use new, unique qsoids. This retains the full history. A “valid” QSO is then the entry among multiple, identical qsoids that has the most recent modified time. Implementation of such complicated rules is tempting to do in the client, which brings us to the second principle:

2. The database should by itself specify a user-friendly API.
(After ARK-monday discussions with LA2TSA.)

A database user, or a client using the database, should not have to do any special tricks to insert, update or delete QSOs in the database, or even know how the underlying table works or that it in reality can only handle INSERTs. Everything should be handled automatically when doing SELECT, INSERT, UPDATE and DELETE.

To achieve this, a view qsoswas created, which functions as a metatable:

  • SELECT from qsos: Groups the entries in qsos_raw by their qsoids, and selects only the entry with the most recent modified field, with blank entries removed.
  • INSERT into qsos: Triggers an INSERT into qsos_raw, with a new unique qsoid and the current time as modified.
  • UPDATE of qsos: Triggers an INSERT into qsos_raw, with the same qsoid and the current time as modified.
  • DELETE from qsos: Triggers an INSERT into qsos_raw, with the same qsoid, the current time as modified and all fields otherwise blank.

The view qsos has the expected behavior when deleting, inserting and updating QSOs, while qsos_raw shows the full edit history. This is probably better illustrated by showing an example:

Example: INSERT, DELETE and UPDATE on qsos

To the left is the underlying table qsos_raw shown, into which entries are inserted on the various operations, while the view qsos is shown to the right.

Run an INSERT statement on qsos:

INSERT INTO qsos (timestamp, call, operator) VALUES ('2019-08-07 13:30', 'LA3WUA', 'LA9SSA');

qsos_raw then contain the inserted QSO along with the previous QSO. qsos shows the same information.

Run an update of an existing QSO:

UPDATE qsos SET call='LB7RH' WHERE qsoid = 2;

qsos_raw then still contains the previous version of the QSO, but also a new version with a more recent modified timestamp. qsos shows only the updated version, emulating a real UPDATE.

Run a delete on an existing QSO:

DELETE FROM qsos WHERE qsoid=2;

All previous versions are retained, but a new, blank record is inserted. qsos does not show the deleted QSO, emulating a real DELETE.

A client implementation don’t have to care about the underlying behavior of the database, since operations on qsos trigger the correct behavior on qsos_raw. All history is retained, automatically. Finally, a user can thus have access only to INSERT operations on the table qsos_raw, and can’t by mistake eradicate qsos_raw by a wrongly implemented UPDATE or DELETE, since only INSERT is allowed on this table.

The sql schema can be found here.

The qso_raw table otherwise contains more or less the same fields as in N1MM’s sqlite database, and we have not put much consideration into the design here. Further development and cleaning here might make the setup suitable for more than just reception of N1MM contacts in the future, for example by making use of more inspiration from the previously mentioned SQL database setup developed by LA6YKA.

N1MM receiver

We’ve mostly outlined the receiver in the previous blog post, but in short, N1MM can be set up to send XML messages containing log changes to a UDP port whenever new contacts are logged, edited or deleted. The documentation apparently can be interpreted as follows:

  • New contact: N1MM sends a contactinfo message, containing new QSO information.
  • Delete contact: N1MM sends a contactdelete message, containing enough information to identify the QSO to be deleted.
  • Edit contact: N1MM sends a contactdelete message to identify the QSO, and then a contactreplace message to replace with new information.

This works very well for new contacts and contacts to be deleted, but we’ve found that this is not necessarily the behavior for editing of contacts. When contacts are edited, there are actually three cases.

First case: Timestamp and/or call is edited. N1MM uses (timestamp, call) as key, so that these are unique identificators. The QSO has to be looked up based on information in the contactdelete message, which is what the contactdelete message is for.

Second case: The other fields are edited. contactreplace and contactdelete contain the same call and timestamp information. For consistency, we can look up the QSO to be updated from the contactdelete message.

Third case: Everything breaks down! contactdelete contains nonsense information, and the QSO has to be looked up based on information from contactreplace alone. This appears to happen randomly.

In addition, we always have to delete the QSO on contactdelete, since any contactdelete might be an actual contactdeleteand not just a message preceding contactreplace. Admittedly, to avoid unnecessary deletion one could also have used some kind of queuing behavior, and check two subsequent messages after some delay. However, this is asking for a race condition if contactdelete is not immediately followed by contactreplace within the assumed time. To be on the safe side, we always delete the QSO regardless. Luckily, we have a QSO database which retains all edit history, making a deletion undo not necessarily effortless and elegant, but at least possible.

With contactinfo and contactdelete (without subsequent contactreplace) messages as in the list above, the contactreplace case changes to the following:

  • Edit contact: N1MM sends a contactdelete message. Attempt to delete the QSO. A contactreplace message arrives. Undo deletion of the previously deleted QSO. Then attempt to look up the qsoid of the contact to edit: Any combination might be valid: (timestamp from contactdelete, call from contactdelete), (timestamp from contactdelete, call from contactreplace), (timestamp from contactreplace, call from contactdelete), (timestamp from contactreplace, call from contactreplace). Then edit the corresponding contact.

Except for the last shenanigan, the N1MM receiver is mostly straight forward. We’ve put the Python script for our N1MM receiver here.