diff -r f9f5e669bace -r 706972d09f05 pvl/verkko/hosts.py --- a/pvl/verkko/hosts.py Sat Jan 26 17:52:40 2013 +0200 +++ b/pvl/verkko/hosts.py Sat Jan 26 19:40:24 2013 +0200 @@ -64,6 +64,9 @@ else : return unicode(self.mac) + def network (self) : + return self.gw + def render_name (self) : if self.name : return self.name.decode('ascii', 'replace') @@ -105,7 +108,7 @@ else : return dt.strftime(cls.TIME_FMT) - + def seen (self) : return ( html.span(title=self.first_seen)(self.format_datetime(self.first_seen)), @@ -140,6 +143,13 @@ )) ## Controller +def column (attr, title, column, rowhtml=None, sort=True, filter=True, colcss=True, rowfilter=True, rowtitle=None, rowcss=None) : + """ + web.Table column spec. + """ + + return (attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss) + class BaseHandler (web.DatabaseHandler) : """ Common controller stuff for DHCP hosts @@ -151,24 +161,45 @@ JS = ( #"/static/jquery/jquery.js" ) - - HOST_ATTRS = { - 'id': Host.id, - 'net': Host.gw, - 'ip': Host.ip, - 'mac': Host.mac, - 'name': Host.name, - 'seen': Host.last_seen, - 'state': Host.state, - 'count': Host.count, - } - - HOST_SORT = Host.last_seen.desc() + + TABLE = Host + TABLE_COLUMNS = ( + #column('id', "#", Host.id ), + column('ip', "IP", Host.ip, ), + column('mac', "MAC", Host.mac, Host.render_mac), + column('name', "Hostname", Host.name, Host.render_name, rowfilter=False), + column('gw', "Network", Host.gw, Host.network, rowfilter=False), + column('seen', "Seen", Host.last_seen, Host.seen, rowfilter=False), + column('state', "State", Host.count, rowtitle=Host.state_title, rowcss=Host.state_class, rowfilter=False), + ) + + # attr -> column + TABLE_ATTRS = dict((attr, column) for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in TABLE_COLUMNS) + + # default sort + TABLE_SORT = Host.last_seen.desc() + + # items per page + TABLE_PAGE = 10 + + # target for items + TABLE_URL = None + TABLE_ITEM_URL = None def query (self) : - return self.db.query(Host) + """ + Database SELECT query. + """ + + return self.db.query(self.TABLE) - def sort (self, hosts, default=HOST_SORT) : + def sort (self, query, default=TABLE_SORT) : + """ + Apply ?sort= from requset args to query. + + Return { attr: sort }, query + """ + sort = self.request.args.get('sort') if sort : @@ -177,11 +208,11 @@ name = None if name : - order_by = self.HOST_ATTRS[name] + order_by = self.TABLE_ATTRS[name] else : order_by = default - # prefix + # prefix -> ordering if not sort : pass elif sort.startswith('+') : @@ -190,71 +221,99 @@ order_by = order_by.desc() else : pass - + + # apply log.debug("sort: %s", order_by) - hosts = hosts.order_by(order_by) + query = query.order_by(order_by) - # k - return sort, hosts + return sort, query + def filter_seen (self, value) : + """ + Return filter expression for given attr == value + """ + + column = Host.last_seen + + if value.isdigit() : + # specific date + date = datetime.datetime.strptime(value, Host.DATE_FMT).date() + + return db.between(date.strftime(Host.DATE_FMT), + db.func.strftime(Host.DATE_FMT, Host.first_seen), + db.func.strftime(Host.DATE_FMT, Host.last_seen) + ) + else : + # recent + timedelta = parse_timedelta(value) + + return ((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 + + # WHERE strftime('%s', 'now') - strftime('%s', last_seen) < :timeout + #filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout) + + def filter_ip (self, value) : + column = Host.ip + + # column is IPv4 string literal format... + if '/' in value : + return (db.func.inet(Host.ip).op('<<')(db.func.cidr(value))) + else : + return (db.func.inet(Host.ip) == db.func.inet(value)) + + def filter_mac (self, value) : + return self.filter_attr('mac', Host.normalize_mac(value)) + def filter_attr (self, attr, value) : """ Return filter expression for given attr == value """ - if attr == 'seen' : - column = Host.last_seen - - if value.isdigit() : - # specific date - date = datetime.datetime.strptime(value, Host.DATE_FMT).date() - - return db.between(date.strftime(Host.DATE_FMT), - db.func.strftime(Host.DATE_FMT, Host.first_seen), - db.func.strftime(Host.DATE_FMT, Host.last_seen) - ) - else : - # recent - timedelta = parse_timedelta(value) - - return ((db.func.now() - Host.last_seen) < timedelta) + # preprocess + like = False - # XXX: for sqlite, pgsql should handle this natively? - # to seconds - #timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds - - # WHERE strftime('%s', 'now') - strftime('%s', last_seen) < :timeout - #filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout) - - elif attr == 'ip' : - column = Host.ip + if value.endswith('*') : + like = value.replace('*', '%') - # column is IPv4 string literal format... - if '/' in value : - return (db.func.inet(Host.ip).op('<<')(db.func.cidr(value))) - else : - return (db.func.inet(Host.ip) == db.func.inet(value)) + # filter + column = self.TABLE_ATTRS[attr] + if like : + return (column.like(like)) else : - # preprocess - like = False - - if value.endswith('*') : - like = value.replace('*', '%') - - elif attr == 'mac' : - value = Host.normalize_mac(value) + return (column == value) + + def _filter (self, attr, values) : + """ + Apply filters for given attr -> (value, expression) + """ - # filter - column = self.HOST_ATTRS[attr] + for value in values : + value = value.strip() + + # ignore empty fields + if not value : + continue - if like : - return (column.like(like)) + # lookup attr-specific filter + filter = getattr(self, 'filter_{attr}'.format(attr=attr), None) + + if filter : + filter = filter(value) else : - return (column == value) + # use generic + filter = self.filter_attr(attr, value) + + log.debug("%s: %s: %s", attr, value, filter) + + yield value, filter - def filter (self, hosts) : + def filter (self, query) : """ Apply filters from request.args against given hosts. @@ -264,22 +323,25 @@ # filter? filters = {} - for attr in self.HOST_ATTRS : - values = [value.strip() for value in self.request.args.getlist(attr) if value.strip()] + for attr in self.TABLE_ATTRS : + # from request args + values = self.request.args.getlist(attr) + + # lookup attr filters as expressions + value_filters = list(self._filter(attr, values)) # ignore empty fields - if not values : + if not value_filters : continue - - # build query expression - filter = db.or_(*[self.filter_attr(attr, value) for value in values]) + + # filtering values, and filter expressions + values, expressions = zip(*value_filters) - log.debug("filter %s: %s", attr, filter) - - hosts = hosts.filter(filter) + # apply + query = query.filter(db.or_(*expressions)) filters[attr] = values - return filters, hosts + return filters, query def filters_title (self) : """ @@ -288,46 +350,190 @@ return ', '.join(value for values in self.filters.itervalues() for value in values) - def render_hosts (self, hosts, title=None, filters=False, page=None, hilight=None) : - COLS = ( - #title sort filter class - ('IP', 'ip', 'ip', 'ip' ), - ('MAC', 'mac', 'mac', 'mac' ), - ('Hostname', 'name', False, False ), - ('Network', 'net', 'net', False ), - ('Seen', 'seen', 'seen', 'seen' ), - ('State', 'state', 'state', False ), - ) + def render_table (self, query, caption=None, sort=None, filters=None, page=None, hilight=None) : + """ + Return