Web based QSO database

Introduction

To be able to handle the QSO's of SM6ONS I have, using a database, PostgreSQL, the web server Apache and a suitable programming enviroment, mod_perl, created a web based user interface to the QSO database. The Perl program is about 500 rows of code, but to this comes at least as much XSL code and some CSS.

Since the layout is controlled by using CSS, it's easy to change this. And when using XSLT four additional formats are possible for various clients: WML for the cell phone, ADIF for other logs, text for simplicity, and HTML for those web browsers that can't handle XML. The web server keeps track of what format the clients are requesting and replies with the most suitable type.

A security backup of the database contents is made every day and the last five copies are always kept. This enables the restoring of the database if any major errors have ocurred.

Results

This is what the web interface looks like after a search showing some QSO's. The results are ordered in reverse cronological order, but it's possible to move forwards or backwards using links in the lower part of the page.

At the bottom of the page is the form to fill out when searching for specific QSO's. If the user is authorized there is also a link to the form to fill out for new QSO's.

If QSL received is marked, a click on this mark will show the received QSL card - if it has been scanned... There is a special little program that handles the scanning of QSL cards which also updates the database accordingly, e.g. when new QSL card has arrived.

Searching

When searching the database a call or part of it is put in the form at the bottom of the page. A search is done for any occurence of the text in any of the calls in the database. This means that a search on SM finds both SM5VYF as well as W7/SM5VYF and SK9SM; these additional hits are, as can be seen, both an advantage as well as a disadvantage. Users with the right type of privileges uses search criterias directly in SQL. Results from searches are presented as shown above. And if the call is clicked on more information is shown:

Note in the header for the QSO here, that the country displayed is taken from a table in a databse containing information about countries and their calls. Data for this table is taken from Amateur and Amateur-satellite service at ITU. The table itself is taken from Table of Call Sign from Radio Regulations, cf. ARTICLE 19 Identification of Stations.

When the link confirming is clicked a QSL card for printing is shown:

This image is generated by another little Perl program (about 100 rows using GD).

Editing and adding new

If the user of the QSO database is authorized he's given the opportunity to edit or delete QSO's in the database. The form is similar to what is used for presentation of single QSO's, but all fields are editable.

An empty form to fill out for a new QSO is very similar to the one used for editing. The form is initiated with current date and time. It is also possible to add functions for reading frequency, power, mode etc. from the radio.

Technology details

SQL

The database used here is PostgreSQL with which one, i.e. the Perl program, communicates using SQL. Data for the QSO's are stored in a table with the following columns:

CREATE TABLE qso (
    id serial NOT NULL,
    daytime timestamp without time zone,
    affix character varying(10),
    call character varying(15),
    freq bigint,
    power smallint,
    mode character(8),
    rstout character(7),
    rstin character(7),
    sent boolean,
    received boolean,
    name character varying(40),
    qth character varying(40)
);

If the web interface can't do the job and more intricate searches are requested it's possible to use any of the tools that are available for PostgreSQL, e.g. psql where queries can be made in SQL.

XML

The Perl program in the web server generates XML, which considerably facilitates the programming, since the grammar for the XML code can be adapted to the columns of the database table. Here's an example of the XML code:

<qsolist owner="SM6ONS" auth="nc" pattern="sm5vyf" index="0" total="2">
 <qso id="2106" utc="1997-01-21 13:34:00">
  <stations>
   <local>
   </local>
   <remote>
    <call>SM5VYF-9</call>
    <operator>Arne</operator>
    <qth>Linköping</qth>
   </remote>
  </stations>
  <characteristics>
   <frequency>144000000</frequency>
   <power>1</power>
   <mode>PACK</mode>
   <report out="0" in="0"/>
  </characteristics>
 </qso>
 <qso id="2026" utc="1996-04-19 16:27:00">
  <stations>
   <local>
    <qsl/>
   </local>
   <remote>
    <call>SM5VYF</call>
    <operator>ARNE</operator>
    <qth>Linköping</qth>
    <qsl/>
   </remote>
  </stations>
  <characteristics>
   <frequency>144000000</frequency>
   <power>1</power>
   <mode>PACK</mode>
   <report out="0" in="0"/>
  </characteristics>
 </qso>
</qsolist>

This code is sent to the client if this requests XML.

XSLT

If the client can't handle XML the program makes a transformation, XSLT, to a suitable format. The tools for this are XML::LibXML and XML::LibXSLT.

For the present ther are transformations for the following formats: WML, ADIF, text, and HTML. To implement another format only a new XSL transformation is needed. In most cases this means 100 - 200 rows of code.

Contents