Following on from the previous post, I found myself with a few hours to myself tonight. The estrogen in the house went out as a group so I managed to do a bit more coding on the FreshPorts listening daemon.

Sometime this past week, I managed to add some triggers to the database that invoke the LISTEN command each time a port is updated. Here is the trigger I created:

CREATE OR REPLACE FUNCTION ports_clear_cache() RETURNS TRIGGER AS $$
      -- when a port changes, add an entry to the cache clearing table
   DECLARE
      l_cache_clearing_ports_id   int8;
      l_port      text;
      l_category  text;
   BEGIN
	IF TG_OP = 'UPDATE' THEN
         -- check first to see if this entry already exists
         SELECT id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = NEW.id;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.id;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (NEW.id, l_category, l_port);
          END IF;

          NOTIFY port_updated;
      END IF;

      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER ports_clear_cache ON ports;
CREATE TRIGGER ports_clear_cache
    AFTER UPDATE on ports
    FOR EACH ROW
    EXECUTE PROCEDURE ports_clear_cache();

The cache_clearing_ports table looks like this:

create table cache_clearing_ports
(
    id          serial                not null,
    port_id     integer               not null,
    category    text                  not null,
    port        text                  not null,
    date_added  timestamp without time zone not null
        default CURRENT_TIMESTAMP,
    primary key (id)
);

alter table cache_clearing_ports
    add foreign key  (port_id)
       references ports (id) on update cascade on delete cascade;

When the FreshPorts listen daemon gets a NOTIFY, it will look at that table, remove the entries from the cache, and delete the rows it just processed.

Here is where we are with the listen daemon:

#!/usr/bin/env python
#
# This program listens for events on the database and processes them
#

#
# configuration items
#
DSN = 'dbname=freshports.org user=dan'

import sys, psycopg, select

import syslog

def RemoveCacheEntry():
  syslog.syslog(syslog.LOG_NOTICE, 'removing cache entries')

def ProcessPortsMoved():
  syslog.syslog(syslog.LOG_NOTICE, 'processing ports/MOVED')

def ProcessPortsUpdating():
  syslog.syslog(syslog.LOG_NOTICE, 'processing ports/UPDATING')

def ProcessVUXML():
  syslog.syslog(syslog.LOG_NOTICE, 'processing ports/security/portaudit/vuln.xml')

syslog.openlog('fp-listen')

syslog.syslog(syslog.LOG_NOTICE, 'Starting up')

print "Opening connection using dns:", DSN
conn = psycopg.connect(DSN)
conn.autocommit(1)

curs = conn.cursor()

curs.execute("SELECT name, script_name FROM listen_for ORDER BY id")
listens_for = curs.fetchall()

listens = dict()
for listen in listens_for:
  curs.execute("LISTEN %s" % listen[0])
  listens[listen[0]] = listen[1]
  print listen

print "Now listening..."
while 1:
  select.select([curs],[],[])==([],[],[])
  curs.execute("SELECT 1")
  notifies = curs.notifies()
  for n in notifies:
    print "got %s" % n[0]
    print "this is index %s" % listens[n[0]]
    # in real life, do something with each...
    print "got %s and I need to call %s" % (n[0], listens[n[0]])
    syslog.syslog(syslog.LOG_NOTICE, "got %s and I need to call %s" % (n[0], listens[n[0]]))
    if listens.has_key(n[0]):
      syslog.syslog(syslog.LOG_NOTICE, 'found that key')
      if listens[n[0]]   == 'listen_port':
        RemoveCacheEntry()
      elif listens[n[0]] == 'listen_ports_moved':
        ProcessPortsMoved()
      elif listens[n[0]] == 'listen_ports_updating':
        ProcessPortsUpdating()
      elif listens[n[0]] == 'listen_vuxml':
        ProcessVUXML()
      else:
        syslog.syslog(syslog.LOG_ERR, "Code does not know what to do when '%s' is found." % n[0])
    else:
      syslog.syslog(syslog.LOG_NOTICE, 'no such key!')

logging.error('terminating')

Here is some sample output from /var/log/messages as a commit is processed:

Sep  2 20:34:45 polo fp-listen: got port_updated and I need to call listen_port
Sep  2 20:34:45 polo fp-listen: found that key
Sep  2 20:34:45 polo fp-listen: removing cache entries
Sep  2 20:34:46 polo fp-listen: got port_updated and I need to call listen_port
Sep  2 20:34:46 polo fp-listen: found that key
Sep  2 20:34:46 polo fp-listen: removing cache entries

I’ll let this run in BETA for a while before I starting fleshing out the stubs at the top of the script. I’ll start by completing RemoveCacheEntry() first.

Watch this space.

This entry was posted on Saturday, September 2nd, 2006 at 4:38 pm and is filed under Development, New ideas, PostgreSQL, Python, Watch Lists, vuxml. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.