Sep 022006
 

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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive