postgresql hstore is easy to compare

hstore is an option key=>value column type that's been around in postgresql for a long time. I was looking at it for a project where I want to compare "new data" to old, so I can approve it. There is a hstore-hstore option that compares two hstore collections and shows the differences.

In reality, an hstore column looks like text. It's just in a format that postgresql understands.

Here, we have an existing record with some network information.

hs1=# select id, data::hstore from d1 where id = 3;
 id |                          data                          
----+--------------------------------------------------------
  3 | "ip"=>"192.168.219.2", "fqdn"=>"hollaback.example.com"
(1 row)

Let's say I submitted a form with slightly changed network information. I can do a select statement to get the differences.

hs1=# select id, hstore('"ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com"')-data from d1 where id =3;
 id |             ?column?              
----+-----------------------------------
  3 | "fqdn"=>"hollaback01.example.com"
(1 row)

This works just as well if we're adding a new key.

hs1=# select id, hstore('"ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"')-data from d1 where id =3;
 id |                           ?column?                            
----+---------------------------------------------------------------
  3 | "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"
(1 row)

This information could be displayed on a confirmation page. Ideally, a proposed dataset would be placed somewhere, and a page could be rendered on the fly showing any changes an approval would create within the database.

Then we can update with the newly submitted form.

hs1=# update d1 set data = data || hstore('"ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"') where id = 3;
UPDATE 3

hs1=# select id, data::hstore from d1 where id = 3; id |                                         data                                         
----+--------------------------------------------------------------------------------------
  3 | "ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"
(1 row)

Note that if I wanted to delete a key instead of just setting it to NULL, that would be a separate operation.

update d1 SET data = delete(data, 'ip') where id = 3;
UPDATE 1

http://stormatics.com/howto-handle-key-value-data-in-postgresql-the-hstore-contrib/

Programming Uniden AMH-350 for APRS

This is a narrative post. If you want to see my python program that calculates out the diode matrix, skip to the end or click here,

I recently received this "Force Communications AMH-350" radio. Actually, it was an entire cabinet with a large power supply, an MFJ TNC2 tnc, and an old DOS PC running JNOS. These had active in a tower shed and turned off 3 years ago. The club wanted me to repurpose this packet system for APRS.

Once I plugged it in, the computer booted up to JNOS, but the radio and TNC did not turn on. The power supply had a plastic box on the back with a larger bussman 30A fuse. When I pulled it out, corrosion dust leaked out. I made a trip to the hardware store and replaced it. The radio turned on but not the TNC. On the front I found 3 smaller fuses and a note describing that "F3" ran the TNC. Pulled that fuse out and it was dead. A second trip to the hardware store got this fuse replaced. Then I plugged everything back in and turned on the power supply. Within 10 seconds, the "make it work smoke" had leaked out of the TNC2. This is probably why the F3 fuse had blown in the first place. This was disappointing, because there is new firmware for the TNC2 that makes it a decent APRS TNC, no computer needed.

The computer, I deemed too old to run a soundcard packet (using direwolf as my driver), so this left me with the power supply and radio. Grounding out the PTT line and using a frequency counter, it showed me "channel 2" was transmitting on 145.050. Channel 1 was not programmed at all.

A quick google search told me that Uniden bought Force Communications and sold this radio as a Uniden AMH-350. I found 2 other people looking for how to program it (one in 1994, and the other in 2004) with no response. I found someone selling the radio's manual on ebay for $20. I offered them $10 and received the manual earlier this week.

The radio itself is programmed with a common cathode diode matrix, representing a binary value. Here is a picture of one back side of it programmed for 145.050. The manual provides a table covering frequencies from 148Mhz to 174Mhz in 5khz increments. Fortunately, it provides a formula on how to come up with your own frequencies. I ran through this formula multiple times getting different results from the book, till I realized the book was rounding some values UP or outright disregarding fractional parts. It also took a bit to wrap my head around binary "1" being disconnected (or cut) and binary "0" being connected. That felt backwards to me.

Eventually though, I was able to match the book, create a chart that matched the existing programmed 145.050 frequency (both Tx and Rx, which are programmed separately). Then, I wrapped the whole thing up in a set of python functions inside an ipython notebook. You can view this on ipython's nbviewer or the direct gist.

I don't have the radio programmed yet. I feel getting the diode matrixes out of "channel 2" and still having them useful for programming with is going to be difficult. I will need 7 diodes connected for each Tx and Rx slot, 14 total. I am attempting to program up channel 1. By the time I got to this portion, I was a bit tired and making mistakes, so I called it a night. Once I get to building out the programming board, I'll post some more pictures.