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 element. Wrapped in if filters. - def url (**opts) : + query - filter()'d sort()'d SELECT query() + caption - optional
+ sort - None for no sorting ui, sort-attr otherwise. + filters - None for no filtering ui, dict of filters otherwise. + page - display pagination for given page + hilight - { attr: value } cells to hilight + """ + + def url (filters=filters, sort=sort, **opts) : + """ + URL for table with given opts, keeping our sorting/filtering unless overriden. + """ + args = dict() if filters : args.update(filters) - - args.update(opts) - - return self.url(**args) + + if sort : + args['sort'] = sort + + if opts : + args.update(opts) - def sortlink (attr) : - if not self.sorts : + return self.url(self.TABLE_URL, **args) + + def sorturl (attr, sort=sort) : + """ + URL for table sorted by given column, reversing direction if already sorting by given column. + """ + + if not sort : sort = attr - elif self.sorts.lstrip('+-') != attr : + elif sort.lstrip('+-') != attr : sort = attr - elif self.sorts.startswith('-') : + elif sort.startswith('-') : sort = "+" + attr else : sort = "-" + attr - return html.a(href=url(sort=sort)) + return url(sort=sort) - def paginate (page, count=None) : + def itemurl (item) : """ - Render pagination. + URL for given item, by id. + """ + + if self.TABLE_ITEM_URL : + # separate page + return self.url(self.TABLE_ITEM_URL, id=item.id) + else : + # to our table + return url() + '#{id}'.format(id=item.id) + + def render_filter (attr) : + """ + Render filter-input for column. + """ + + value = filters.get(attr) + + if value : + # XXX: multi-valued filters? + value = value[0] + else : + value = None + + return html.input(type='text', name=attr, value=value) + + def render_head () : + """ + Yield header, filter rows for columns in table header. + """ + + # id + yield html.td('#'), html.td(html.input(type='submit', value=u'\u00BF')) + + for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in self.TABLE_COLUMNS : + header = title + + if sort : + header = html.a(href=sorturl(attr))(header) + + header = html.th(header) + + if filters is not None and filter : + filter = render_filter(attr) + else : + filter = None + + if colcss is True : + colcss = attr + + filter = html.td(class_=colcss)(filter) + + yield header, filter + + def render_cell (attr, value, rowhtml=None, colcss=True, filter=None, rowtitle=None, rowcss=None, hilight=hilight) : + """ + Render a single cell. + + colcss - css class for column; True -> attr + filter - render filter link for value? + htmlvalue - rendered value? + title - mouseover title for cell + rowcss - css class for row + """ + + if not rowhtml : + rowhtml = value + + if filter : + cell = html.a(href=url(filters=None, **{attr: value}))(rowhtml) + else : + cell = rowhtml + + if colcss is True : + colcss = attr + + if hilight : + hilight = attr in hilight and value in hilight[attr] + + css = (colcss, rowcss, 'hilight' if hilight else None) + css = ' '.join(cls for cls in css if cls) + + return html.td(class_=css, title=rowtitle)(cell) + + def render_row (item) : + """ + Yield columns for row. + """ + + for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in self.TABLE_COLUMNS : + # XXX: this is sometimes broken, figure out how to index by column + value = getattr(item, attr) + + if rowhtml : + rowhtml = rowhtml(item) + else : + rowhtml = value + + if rowtitle : + rowtitle = rowtitle(item) + else : + rowtitle = None + + if rowcss : + rowcss = rowcss(item) + else : + rowcss = None + + yield render_cell(attr, value, + rowhtml = rowhtml, + colcss = colcss, + filter = value if rowfilter else None, + rowtitle = rowtitle, + rowcss = rowcss, + ) + + def render_body (rows) : + """ + Yield rows. + """ + + for i, item in enumerate(rows) : + yield html.tr(class_=('alternate' if i % 2 else None), id=item.id)( + html.th( + html.a(href=itemurl(item))("#") + ), + + render_row(item) + ) + + def render_pagination (page, count=None) : + """ + Render pagination links. """ if count is not None : - pages = int(math.ceil(count / self.PAGE)) # XXX: bad self.PAGE + pages = int(math.ceil(count / self.TABLE_PAGE)) else : pages = None @@ -339,90 +545,45 @@ yield html.a(href=url(page=(page + 1)))(html("» Next")) - def render_filter (filter) : - value = filters.get(filter) - - if value : - # XXX: multi-valued filters? - value = value[0] - else : - value = None - - return html.input(type='text', name=filter, value=value) - - def render_cell (attr, value, cssclass=True, filter=None, htmlvalue=None) : - if htmlvalue : - cell = htmlvalue - else : - cell = value - - if filter : - cell = html.a(href=self.url(ListHandler, **{attr: value}))(cell) - - if cssclass is True : - cssclass = attr - css = (cssclass, 'hilight' if (hilight and attr in hilight and value in hilight[attr]) else None) - css = ' '.join(cls for cls in css if cls) - - return html.td(class_=css)(cell) + def render_foot () : + # XXX: does separate SELECT count() + count = query.count() + if page : + return render_pagination(page, count) + else : + return "{count} hosts".format(count=count) + + # columns for the two header rows + headers, filtering = zip(*list(render_head())) + + # render table table = html.table( - html.caption(title) if title else None, + html.caption(caption) if caption else None, html.thead( - html.tr( - html.th('#'), - ( - html.th( - sortlink(sort)(title) if sort else (title) - ) for title, sort, filter, class_ in COLS - ) - ), - html.tr(class_='filter')( - html.td( - html.input(type='submit', value=u'\u00BF'), - ), - ( - html.td(class_=class_)( - render_filter(filter) if filter else None - ) for title, sort, filter, class_ in COLS - ) - ) if filters is not False else None + html.tr(headers), + # filters? + html.tr(class_='filter')(filtering) if filters is not None else None, ), html.tbody( - html.tr(class_=('alternate' if i % 2 else None), id=host.id)( - html.th( - html.a(href=self.url(ItemHandler, id=host.id))( - '#' #host.id - ) - ), - - render_cell('ip', host.ip, filter=True), - render_cell('mac', host.mac, filter=True, htmlvalue=host.render_mac()), - render_cell('name', host.name, htmlvalue=host.render_name()), - render_cell('gw', host.gw), - - render_cell('seen', host.seen()), - html.td(class_=host.state_class(), title=host.state_title())(host.state), - ) for i, host in enumerate(hosts) + render_body(query) ), html.tfoot( html.tr( - html.td(colspan=(1 + len(COLS)))( - paginate(page) if page is not None else ( - # XXX: does separate SELECT count() - "{count} hosts".format(count=hosts.count()) - ) + html.td(colspan=(1 + len(self.TABLE_COLUMNS)))( + render_foot() ) ) ) ) - if filters is False : + # filters form? + if filters is None : return table else : - return html.form(method='get', action=self.url())( - html.input(type='hidden', name='sort', value=self.sorts), + return html.form(method='get', action=url(filters=None, sort=None))( + html.input(type='hidden', name='sort', value=sort), table, ) @@ -430,7 +591,7 @@ """ A specific DHCP host, along with a list of related hosts. """ - + def process (self, id) : self.hosts = self.query() self.host = self.hosts.get(id) @@ -472,7 +633,7 @@ self.render_host(self.host), html.h2('Related'), - self.render_hosts(self.hosts, hilight=dict(ip=self.host.ip, mac=self.host.mac)), + self.render_table(self.hosts, sort=self.sorts, hilight=dict(ip=self.host.ip, mac=self.host.mac)), html.a(href=self.url(ListHandler))(html('«'), 'Back'), ) @@ -483,8 +644,7 @@ List of DHCP hosts for given filter. """ - # pagination - PAGE = 10 + TABLE_PAGE = 10 def process (self) : hosts = self.query() @@ -513,11 +673,15 @@ def render (self) : return ( - self.render_hosts(self.hosts, filters=self.filters, page=self.page), + self.render_table(self.hosts, filters=self.filters, sort=self.sorts, page=self.page), html.a(href=self.url())(html('«'), 'Back') if self.filters else None, ) +# XXX: +BaseHandler.TABLE_URL = ListHandler +BaseHandler.TABLE_ITEM_URL = ItemHandler + class RealtimeHandler (BaseHandler) : TITLE = "DHCP Pseudo-Realtime hosts.." CSS = BaseHandler.CSS + (