The Ramblings of Ken

This is a continuation from my previous post about PostgreSQL in LPFM.

In December 2023, KVWJ installed a new Nautel VX300 transmitter. You can read more about that at these places if you want:

• Nautel News
• Radioworld
• KVWJ Station News (Jan 2024)

This new transmitter enabled the station to display the song title and artist information on RDS equipped radios. We were excited to start doing this. I was afraid that this was a very complicated process, but it is quite simple. You send a netcat command to the transmitter with the text information you want. No problem. I originally accomplished this with a simple bash script. Here's a snippet from that:

SONGNAME=$(</mnt/zara/ZaraLogs/CurrentSong.txt)
NAMELENGTH=${#SONGNAME}
if [ "$NAMELENGTH" -gt 64 ]; then
  SONGNAMECUT=$(echo $SONGNAME | cut -c 1-64)
  echo "TEXT=$(echo $SONGNAMECUT )"|nc -i 2 -w 2 192.168.0.108 7005
  echo "TEXT?"|nc -i 2 -w 2 192.168.0.108 7005 &
fi
if [ "$NAMELENGTH" -lt 65 ]; then
  echo "TEXT=$(echo $SONGNAME )"|nc -i 2 -w 2 192.168.0.108 7005
  echo "TEXT?"|nc -i 2 -w 2 192.168.0.108 7005 &
fi

I was surprised how easy it was to send RDS commands.

In 2026, however, we decided it was time to take RDS to the next level. One minor problem we had was that the CurrentSong.txt from ZaraRadio is reading the filename. If we had something wrong with the filename then the information would be displayed incorrectly on RDS. One common thing we run into is that filenames do not include "The" in them. So a song by "The Turtles" is simply labeled "Turtles" in the filename. This means that you see on RDS "So Happy Together - Turtles." This is not ideal.

I realized we could use the PostgreSQL database for this. I extended the database to include artist_display_name and track_display_name fields. I then added a template view with a form so you could edit the artist_display_name and track_display_name.

With the new artist_display_name and track_display_name fields and the template view in place, we were ready to move on from the bash script to something more sophisticated. My original implementation was a Python script watching for the Zara daily log to change and then looking up the song in the database to see if it had a display_name field populated. This was problematic because the file would change and sometimes the song in the log wouldn't be in the database yet. That's when I realized I could use a PostgreSQL database trigger and a Python listener for this. Here's a snippet from the Python:

# Wait for a Postgres notification, up to one second.
ready, _, _ = select.select([listener_conn], [], [], 1)

if ready:
    listener_conn.poll()

    while listener_conn.notifies:
        current_song_category = None

        notification = listener_conn.notifies.pop(0)
        database_song_text, current_song_category = process_notification(
            listener_conn,
            notification,
        )            

So the Python code is waiting for a trigger to fire which then send a notification with a new playback ID when a new record is inserted into the playback table. More database queries are executed to then get the proper artist and track information:

SELECT CONCAT_WS(
  ' - ',
  COALESCE(NULLIF(t.track_display_name, ''), NULLIF(t.track_name, '')),
  COALESCE(NULLIF(a.artist_display_name, ''), NULLIF(a.artist_name, '')),
  NULLIF(y.year::text, '')
)
FROM playback p
JOIN tracks t
  ON t.id = p.trackid
LEFT JOIN artists a
  ON a.id = t.artistid
LEFT JOIN years y
  ON y.id = t.yearid
WHERE p.id = %s
LIMIT 1

The COALESCE database command allows the display_name field to be selected if it exists, otherwise it falls back to the regular name field.

With the right information we can build a string that can be sent to the transmitter. Instead of using netcat from the shell, we use a Python socket:

def send_rds_command(command: str) -> None:
    with socket.create_connection((RDS_HOST, RDS_PORT), timeout=2) as sock:
        sock.sendall((command + "\n").encode("utf-8"))

This architecture blew away my expectations. It's a foolproof way to control what information actually gets sent to the transmitter.

More is yet to come from my PostgreSQL in LPFM series...

Confession: I'm into terrestrial radio.

I volunteer my time doing IT projects for the local LPFM. If you want to read all about this station head on over to kvwj.org. It's pretty cool what has been done with next to zero budget.

Back in 2024 the director and chief engineer was wondering how he could get a list of the songs that have played on the air over the last four weeks. He was wanting to analyze just how often songs are playing. This station uses the free version of ZaraRadio. It gives you playback logs for every day. Going through the logs and figuring out what played is a big hassle though. Here's a small sample of what a daily log looks like:

06:32:39	start	C:\Users\KVWJ\Desktop\ye olde new gold\playback\clockwheel rotation\B medium oldie\A Well Respected Man - Kinks - 1965.mp3
06:35:16	start	C:\Users\KVWJ\Desktop\ye olde new gold\playback\clockwheel rotation\C slow oldie\It's Just A Matter Of Time - Brook Benton - 1959.mp3
06:37:41	start	C:\Users\KVWJ\Desktop\ye olde new gold\playback\clockwheel rotation\D extra slow oldie\Your Mama Said You Cried - Kenny Dino - 1961.mp3

I thought it shouldn't be too bad to write a Python script that can scrape these logs and throw them into a SQL database. I took a few hours over a week or so and got a proof of concept working. I had a script running in a loop and grabbing lines from the log that matched the pattern above. I started with a SQLite database with a flat one table structure.

The next thing I worked on was a web interface to interact with the SQLite database. I have some experience in Django, so I cobbled together a project based on django-tables2 and got the data displaying on a web page. I then threw a download button on it so that our director could have a nice csv of the data to take with him.

I eventually realized that this flat SQLite database would not be sustainable.

I then got to work restructuring the database so it wasn't basically a large Excel spreadsheet. I broke it off into artist, track, category, year, and playback tables. The rows in the playback table corresponding to the log lines above now looks like this:

id,track,date,timestamp
4244293,2511,2026-10-20,06:37:41
4244292,1659,2026-10-20,06:35:16
4244291,1207,2026-10-20,06:32:39

This makes the database much more efficient.

I was at this point fighting with Django. Randomly it would get really slow. I decided it was time to move from the SQLite database to a more powerful PostgreSQL database. I created tables with the same name in the new database and then dumped the SQLite tables and imported them into the new PostregSQL tables. Much better.

And this is just part 1 is a series of posts I will be making about this.