request per session, move to postgres, rename columns, fixup state/name/error handling in updates
--- a/bin/pvl.verkko-dhcp Thu Oct 18 21:17:11 2012 +0300
+++ b/bin/pvl.verkko-dhcp Thu Oct 18 23:06:23 2012 +0300
@@ -92,12 +92,15 @@
gw = attrs['gw'],
first_seen = attrs['timestamp'],
+ count = 1,
- name = attrs['name'],
last_seen = attrs['timestamp'],
- last_msg = attrs['state'],
+ state = attrs['state'],
+
+ name = attrs.get('name'),
+ error = attrs.get('error'),
)
- result = self.engine.execute(query, **attrs)
+ result = self.engine.execute(query)
id, = result.inserted_primary_key
return id
@@ -109,16 +112,23 @@
table = db.dhcp_hosts
- query = table.update(
- # where
- (table.c.ip == attrs['ip']) & (table.c.mac == attrs['mac']) & (table.c.gw == attrs['gw']),
+ query = table.update()
+ query = query.where((table.c.ip == attrs['ip']) & (table.c.mac == attrs['mac']) & (table.c.gw == attrs['gw']))
+ query = query.values(
+ count = db.func.coalesce(table.c.count, 0) + 1,
# set
- name = attrs['name'],
last_seen = attrs['timestamp'],
- last_msg = attrs['state'],
+ state = attrs['state'],
)
- result = self.engine.execute(query, **attrs)
+
+ if 'name' in attrs :
+ query = query.values(name = attrs['name'])
+
+ if 'error' in attrs :
+ query = query.values(error = attrs['error'])
+
+ result = self.engine.execute(query)
# any matched rows?
return result.rowcount > 0
@@ -127,6 +137,8 @@
"""
Process given DHCP syslog message to update the hosts table.
"""
+
+ attrs = {}
# ignore unless we have enough info to fully identify the client
# this means that we omit DHCPDISCOVER messages, but we get the OFFER/REQUEST/ACK
@@ -134,20 +146,29 @@
# ignore; we require these
return
+ # do not override error from request on NAK; clear otherwise
+ if item.get('type') == 'DHCPNAK' :
+ pass
+ else :
+ attrs['error'] = item.get('error')
+
+ # do not override name unless known
+ if item.get('name') :
+ attrs['name'] = item.get('name')
+
# db: syslog
ATTR_MAP = (
('ip', 'lease'),
('mac', 'hwaddr'),
('gw', 'gateway'),
- ('name', 'hostname'),
('timestamp', 'timestamp'),
('state', 'type'),
- ('error', 'error'),
)
- # attrs
- attrs = dict((key, item.get(name)) for key, name in ATTR_MAP)
+ # generic attrs
+ for key, name in ATTR_MAP :
+ attrs[key] = item.get(name)
# update existing?
if self.update(attrs) :
@@ -183,6 +204,9 @@
dhcp_item['timestamp'] = item['timestamp'] # XXX: fixup DHCPSyslogParser?
+ if item.get('error') :
+ item['error'] = self.filter.parse_error(item['error'])
+
self.db.process(dhcp_item)
def main (self, source, poll) :
@@ -212,6 +236,17 @@
def main (argv) :
options, args = parse_options(argv)
+ # db
+ if not options.database :
+ log.error("No database given")
+ return 1
+
+ log.info("Open up database: %s", options.database)
+ database = DHCPHostsDatabase(options.database)
+
+ if options.create :
+ database.create()
+
# syslog
log.info("Open up syslog...")
syslog_parser = pvl.syslog.parser.SyslogParser(prog=DHCP_SYSLOG_PROG) # filter by prog
@@ -231,16 +266,9 @@
poll = False # do not poll-loop
else :
- log.error("No syslog source given")
- return 1
-
- # db
- log.info("Open up database: %s", options.database)
- database = DHCPHostsDatabase(options.database)
+ log.warning("No syslog source given")
+ return 0
- if options.create :
- database.create()
-
# handler + main
handler = DHCPSyslogHandler(database)
--- a/pvl/verkko/db.py Thu Oct 18 21:17:11 2012 +0300
+++ b/pvl/verkko/db.py Thu Oct 18 23:06:23 2012 +0300
@@ -8,8 +8,7 @@
# TODO: count, completely separate dhcp_events?
dhcp_hosts = Table('dhcp_hosts', metadata,
- # TODO: rename: id
- Column('rowid', Integer, primary_key=True),
+ Column('id', Integer, primary_key=True),
# unique
Column('ip', String, nullable=False),
@@ -19,12 +18,14 @@
# updated
Column('first_seen', DateTime, nullable=False),
Column('last_seen', DateTime, nullable=False),
-
- # TODO: rename: state
- Column('last_msg', String, nullable=False),
# scalar; updated
Column('name', String, nullable=True),
+ Column('state', String, nullable=True),
+ Column('error', String, nullable=True),
+
+ # counters
+ Column('count', Integer, default=1),
UniqueConstraint('ip', 'mac', 'gw'),
)
@@ -50,12 +51,11 @@
self.engine = create_engine(database,
echo = (log.isEnabledFor(logging.DEBUG)),
)
- self.session = Session(bind=self.engine)
# ORM
- def query (self, cls) :
- return self.session.query(cls)
-
+ def session (self) :
+ return Session(bind=self.engine)
+
# SQL
def select (self, query) :
return self.engine.execute(query)
--- a/pvl/verkko/hosts.py Thu Oct 18 21:17:11 2012 +0300
+++ b/pvl/verkko/hosts.py Thu Oct 18 23:06:23 2012 +0300
@@ -6,6 +6,7 @@
import re
import datetime
import socket # dns
+import math
import logging; log = logging.getLogger('pvl.verkko.hosts')
@@ -68,16 +69,29 @@
}
def state_class (self) :
- if self.state in self.STATES :
+ if self.error :
+ return 'dhcp-error'
+
+ elif self.state in self.STATES :
return 'dhcp-' + self.STATES[self.state]
else :
return None
+ def state_title (self) :
+ return self.error # or None
+
+ def render_state (self) :
+ if self.error :
+ return "{self.state}: {self.error}".format(self=self)
+ else :
+ return self.state
+
def when (self) :
- return '{frm} - {to}'.format(
- frm = self.first_seen.strftime(self.DATE_FMT),
- to = self.last_seen.strftime(self.DATE_FMT),
+ return (
+ html.span(title=self.first_seen)(self.first_seen.strftime(self.DATE_FMT)),
+ '-',
+ html.span(title=self.last_seen)(self.last_seen.strftime(self.DATE_FMT)),
)
def dns (self) :
@@ -102,8 +116,8 @@
return u"{host.ip} ({host.mac})".format(host=self)
db.mapper(Host, db.dhcp_hosts, properties=dict(
- id = db.dhcp_hosts.c.rowid,
- state = db.dhcp_hosts.c.last_msg,
+ #id = db.dhcp_hosts.c.rowid,
+ #state = db.dhcp_hosts.c.,
))
class BaseHandler (web.Handler) :
@@ -120,24 +134,24 @@
HOST_SORT = Host.last_seen.desc()
def query (self) :
- hosts = self.db.query(Host)
-
- # sort ?
+ return self.db.query(Host)
+
+ def sort (self, hosts, default=HOST_SORT) :
self.sort = self.request.args.get('sort')
if self.sort :
sort = self.HOST_ATTRS[self.sort]
else :
- sort = self.HOST_SORT
+ sort = default
log.debug("sort: %s", sort)
-
+
hosts = hosts.order_by(sort)
# k
return hosts
- def render_hosts (self, hosts, title=None, filters=False) :
+ def render_hosts (self, hosts, title=None, filters=False, page=None) :
COLS = (
#title sort filter class
('IP', 'ip', 'ip', 'ip' ),
@@ -158,6 +172,25 @@
return self.url(**args)
+ def paginate (page, count=None) :
+ """
+ Render pagination.
+ """
+
+ if count is not None :
+ pages = int(math.ceil(count / self.PAGE)) # XXX: bad self.PAGE
+ else :
+ pages = None
+
+ if page > 0 :
+ yield html.a(href=url(page=0))(html("«« First"))
+ yield html.a(href=url(page=(page - 1)))(html("« Prev"))
+
+ yield html.span("Page {page} of {pages}".format(page=(page + 1), pages=(pages or '???')))
+
+ yield html.a(href=url(page=(page + 1)))(html("» Next"))
+
+
table = html.table(
html.caption(title) if title else None,
html.thead(
@@ -184,7 +217,7 @@
html.tr(class_=('alternate' if i % 2 else None), id=host.id)(
html.th(
html.a(href=self.url(ItemHandler, id=host.id))(
- '#' #host['rowid'])
+ '#' #host.id
)
),
html.td(class_='ip')(
@@ -202,14 +235,16 @@
host.gw
),
html.td(host.when()),
- html.td(class_=host.state_class())(host.state),
+ html.td(class_=host.state_class(), title=host.state_title())(host.state),
) for i, host in enumerate(hosts)
),
html.tfoot(
html.tr(
html.td(colspan=(1 + len(COLS)))(
- # XXX: does separate SELECT count()
- "{count} hosts".format(count=hosts.count())
+ paginate(page) if page is not None else (
+ # XXX: does separate SELECT count()
+ "{count} hosts".format(count=hosts.count())
+ )
)
)
)
@@ -232,7 +267,7 @@
('DNS', host.dns()),
('First seen', host.first_seen),
('Last seen', host.last_seen),
- ('Last state', host.state),
+ ('Last state', host.render_state()),
)
return (
@@ -255,7 +290,7 @@
if not self.host :
raise web.NotFound("No such host: {id}".format(id=id))
- self.hosts = self.hosts.filter((Host.ip == self.host.ip) | (Host.mac == self.host.mac))
+ self.hosts = self.sort(self.hosts.filter((Host.ip == self.host.ip) | (Host.mac == self.host.mac)))
def title (self) :
return u"DHCP Host: {self.host}".format(self=self)
@@ -264,10 +299,14 @@
return self.render_host(self.host, self.hosts)
class ListHandler (BaseHandler) :
+ # pagination
+ PAGE = 10
+
def process (self) :
hosts = self.query()
# filter?
+ column = None
self.filters = {}
for attr in self.HOST_ATTRS :
@@ -277,6 +316,8 @@
continue
if attr == 'seen' :
+ column = Host.last_seen
+
if value.isdigit() :
# specific date
date = datetime.datetime.strptime(value, Host.DATE_FMT).date()
@@ -288,23 +329,24 @@
else :
# recent
timedelta = parse_timedelta(value)
+
+ filter = ((db.func.now() - Host.last_seen) < timedelta)
+ # XXX: for sqlite, pgsql should handle this natively?
# to seconds
- timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds
+ #timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds
- # XXX: for sqlite, pgsql should handle this natively?
# WHERE strftime('%s', 'now') - strftime('%s', last_seen) < :timeout
- filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout)
+ #filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout)
elif attr == 'ip' :
- # parse as network expression
- ip = IPv4Network(value)
+ column = Host.ip
- if ip.masklen == 32 :
- filter = (Host.ip == value)
+ # column is IPv4 string literal format...
+ if '/' in value :
+ filter = (db.func.inet(Host.ip).op('<<')(db.func.cidr(value)))
else :
- # XXX: column is IPv4 string literal format...
- filter = ((Host.ip.op('&')(ip.mask)) == ip.base)
+ filter = (db.func.inet(Host.ip) == db.func.inet(value))
else :
# preprocess
@@ -317,16 +359,27 @@
value = Host.normalize_mac(value)
# filter
- col = self.HOST_ATTRS[attr]
+ column = self.HOST_ATTRS[attr]
if like :
- filter = (col.like(like))
+ filter = (column.like(like))
else :
- filter = (col == value)
+ filter = (column == value)
hosts = hosts.filter(filter)
self.filters[attr] = value
-
+
+ # sort XXX: default per filter column
+ hosts = self.sort(hosts) #, column)
+
+ # page?
+ self.page = self.request.args.get('page')
+
+ if self.page :
+ self.page = int(self.page)
+
+ hosts = hosts.offset(self.page * self.PAGE).limit(self.PAGE)
+
self.hosts = hosts
def title (self) :
@@ -337,7 +390,7 @@
def render (self) :
return (
- self.render_hosts(self.hosts, filters=self.filters),
+ self.render_hosts(self.hosts, filters=self.filters, page=self.page),
html.a(href=self.url())(html('«'), 'Back') if self.filters else None,
)
--- a/pvl/verkko/web.py Thu Oct 18 21:17:11 2012 +0300
+++ b/pvl/verkko/web.py Thu Oct 18 23:06:23 2012 +0300
@@ -39,10 +39,12 @@
"""
self.app = app
- self.db = app.db
self.request = request
self.urlmap = urls
self.params = params
+
+ # new ORM session per request
+ self.db = app.db.session()
def url (self, handler=None, **params) :
"""
--- a/static/style.css Thu Oct 18 21:17:11 2012 +0300
+++ b/static/style.css Thu Oct 18 23:06:23 2012 +0300
@@ -106,8 +106,8 @@
padding: 0.25em 1em;
}
-td a,
-th a
+thead a,
+tbody a
{
display: block;
}
@@ -179,3 +179,4 @@
.dhcp-ack { background-color: #448844; }
.dhcp-nak { background-color: #884444; }
.dhcp-release { background-color: #335533; }
+.dhcp-error { background-color: #ff4444; }
--- a/test.wsgi Thu Oct 18 21:17:11 2012 +0300
+++ b/test.wsgi Thu Oct 18 23:06:23 2012 +0300
@@ -4,6 +4,6 @@
import pvl.verkko.wsgi
-DATABASE_READ = 'sqlite:////home/terom/pvl/web/verkko/var/verkko.db'
+DATABASE_READ = 'postgresql://verkko_dev:iphie5Aa1ohquahd@localhost/terom_verkko_dev'
application = pvl.verkko.wsgi.Application(DATABASE_READ)