141 #id = db.dhcp_hosts.c.rowid, |
141 #id = db.dhcp_hosts.c.rowid, |
142 #state = db.dhcp_hosts.c., |
142 #state = db.dhcp_hosts.c., |
143 )) |
143 )) |
144 |
144 |
145 ## Controller |
145 ## Controller |
146 def column (attr, title, column, rowhtml=None, sort=True, filter=True, colcss=True, rowfilter=True, rowtitle=None, rowcss=None) : |
146 class HostsTable (table.Table) : |
147 """ |
147 """ |
148 web.Table column spec. |
148 Table of hosts. |
149 """ |
149 """ |
150 |
150 |
151 return (attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss) |
151 COLUMNS = ( |
152 |
152 table.Column('ip', "IP", Host.ip, |
153 class BaseHandler (web.DatabaseHandler) : |
153 rowfilter = True, |
154 """ |
154 ), |
155 Common controller stuff for DHCP hosts |
155 table.Column('mac', "MAC", Host.mac, Host.render_mac, |
156 """ |
156 rowfilter = True, |
157 |
157 ), |
158 CSS = ( |
158 table.Column('name', "Hostname", Host.name, Host.render_name, ), |
|
159 table.Column('gw', "Network", Host.gw, Host.network, ), |
|
160 table.Column('seen', "Seen", Host.last_seen, Host.seen, ), |
|
161 table.Column('state', "State", Host.count, |
|
162 rowtitle = Host.state_title, |
|
163 rowcss = Host.state_class, |
|
164 ), |
|
165 ) |
|
166 |
|
167 # XXX: have to set again |
|
168 ATTRS = dict((col.attr, col) for col in COLUMNS) |
|
169 |
|
170 # XXX: set later |
|
171 TABLE_URL = ITEM_URL = None |
|
172 |
|
173 # default |
|
174 SORT = Host.last_seen.desc() |
|
175 PAGE = 10 |
|
176 |
|
177 class HostsHandler (table.TableHandler, web.DatabaseHandler) : |
|
178 """ |
|
179 Combined database + <table> |
|
180 """ |
|
181 |
|
182 CSS = web.DatabaseHandler.CSS + table.TableHandler.CSS + ( |
159 "/static/dhcp/hosts.css", |
183 "/static/dhcp/hosts.css", |
160 ) |
184 ) |
161 JS = ( |
185 |
162 #"/static/jquery/jquery.js" |
186 # model |
163 ) |
187 TABLE = HostsTable |
164 |
|
165 TABLE = Host |
|
166 TABLE_COLUMNS = ( |
|
167 #column('id', "#", Host.id ), |
|
168 column('ip', "IP", Host.ip, ), |
|
169 column('mac', "MAC", Host.mac, Host.render_mac), |
|
170 column('name', "Hostname", Host.name, Host.render_name, rowfilter=False), |
|
171 column('gw', "Network", Host.gw, Host.network, rowfilter=False), |
|
172 column('seen', "Seen", Host.last_seen, Host.seen, rowfilter=False), |
|
173 column('state', "State", Host.count, rowtitle=Host.state_title, rowcss=Host.state_class, rowfilter=False), |
|
174 ) |
|
175 |
|
176 # attr -> column |
|
177 TABLE_ATTRS = dict((attr, column) for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in TABLE_COLUMNS) |
|
178 |
|
179 # default sort |
|
180 TABLE_SORT = Host.last_seen.desc() |
|
181 |
|
182 # items per page |
|
183 TABLE_PAGE = 10 |
|
184 |
|
185 # target for items |
|
186 TABLE_URL = None |
|
187 TABLE_ITEM_URL = None |
|
188 |
188 |
189 def query (self) : |
189 def query (self) : |
190 """ |
190 """ |
191 Database SELECT query. |
191 Database SELECT query. |
192 """ |
192 """ |
193 |
193 |
194 return self.db.query(self.TABLE) |
194 return self.db.query(Host) |
195 |
195 |
196 def sort (self, query, default=TABLE_SORT) : |
|
197 """ |
|
198 Apply ?sort= from requset args to query. |
|
199 |
|
200 Return { attr: sort }, query |
|
201 """ |
|
202 |
|
203 sort = self.request.args.get('sort') |
|
204 |
|
205 if sort : |
|
206 name = sort.lstrip('+-') |
|
207 else : |
|
208 name = None |
|
209 |
|
210 if name : |
|
211 order_by = self.TABLE_ATTRS[name] |
|
212 else : |
|
213 order_by = default |
|
214 |
|
215 # prefix -> ordering |
|
216 if not sort : |
|
217 pass |
|
218 elif sort.startswith('+') : |
|
219 order_by = order_by.asc() |
|
220 elif sort.startswith('-') : |
|
221 order_by = order_by.desc() |
|
222 else : |
|
223 pass |
|
224 |
|
225 # apply |
|
226 log.debug("sort: %s", order_by) |
|
227 |
|
228 query = query.order_by(order_by) |
|
229 |
|
230 return sort, query |
|
231 |
|
232 def filter_seen (self, value) : |
196 def filter_seen (self, value) : |
233 """ |
197 """ |
234 Return filter expression for given attr == value |
198 Return filter expression for given attr == value |
235 """ |
199 """ |
236 |
200 |
267 return (db.func.inet(Host.ip) == db.func.inet(value)) |
231 return (db.func.inet(Host.ip) == db.func.inet(value)) |
268 |
232 |
269 def filter_mac (self, value) : |
233 def filter_mac (self, value) : |
270 return self.filter_attr('mac', Host.normalize_mac(value)) |
234 return self.filter_attr('mac', Host.normalize_mac(value)) |
271 |
235 |
272 def filter_attr (self, attr, value) : |
236 class ItemHandler (HostsHandler) : |
273 """ |
|
274 Return filter expression for given attr == value |
|
275 """ |
|
276 |
|
277 # preprocess |
|
278 like = False |
|
279 |
|
280 if value.endswith('*') : |
|
281 like = value.replace('*', '%') |
|
282 |
|
283 # filter |
|
284 column = self.TABLE_ATTRS[attr] |
|
285 |
|
286 if like : |
|
287 return (column.like(like)) |
|
288 else : |
|
289 return (column == value) |
|
290 |
|
291 def _filter (self, attr, values) : |
|
292 """ |
|
293 Apply filters for given attr -> (value, expression) |
|
294 """ |
|
295 |
|
296 for value in values : |
|
297 value = value.strip() |
|
298 |
|
299 # ignore empty fields |
|
300 if not value : |
|
301 continue |
|
302 |
|
303 # lookup attr-specific filter |
|
304 filter = getattr(self, 'filter_{attr}'.format(attr=attr), None) |
|
305 |
|
306 if filter : |
|
307 filter = filter(value) |
|
308 else : |
|
309 # use generic |
|
310 filter = self.filter_attr(attr, value) |
|
311 |
|
312 log.debug("%s: %s: %s", attr, value, filter) |
|
313 |
|
314 yield value, filter |
|
315 |
|
316 def filter (self, query) : |
|
317 """ |
|
318 Apply filters from request.args against given hosts. |
|
319 |
|
320 Returns (filters, hosts). |
|
321 """ |
|
322 |
|
323 # filter? |
|
324 filters = {} |
|
325 |
|
326 for attr in self.TABLE_ATTRS : |
|
327 # from request args |
|
328 values = self.request.args.getlist(attr) |
|
329 |
|
330 # lookup attr filters as expressions |
|
331 value_filters = list(self._filter(attr, values)) |
|
332 |
|
333 # ignore empty fields |
|
334 if not value_filters : |
|
335 continue |
|
336 |
|
337 # filtering values, and filter expressions |
|
338 values, expressions = zip(*value_filters) |
|
339 |
|
340 # apply |
|
341 query = query.filter(db.or_(*expressions)) |
|
342 filters[attr] = values |
|
343 |
|
344 return filters, query |
|
345 |
|
346 def filters_title (self) : |
|
347 """ |
|
348 Return a string representing the applied filters. |
|
349 """ |
|
350 |
|
351 return ', '.join(value for values in self.filters.itervalues() for value in values) |
|
352 |
|
353 def render_table (self, query, caption=None, sort=None, filters=None, page=None, hilight=None) : |
|
354 """ |
|
355 Return <table> element. Wrapped in <form> if filters. |
|
356 |
|
357 query - filter()'d sort()'d SELECT query() |
|
358 caption - optional <caption> |
|
359 sort - None for no sorting ui, sort-attr otherwise. |
|
360 filters - None for no filtering ui, dict of filters otherwise. |
|
361 page - display pagination for given page |
|
362 hilight - { attr: value } cells to hilight |
|
363 """ |
|
364 |
|
365 def url (filters=filters, sort=sort, **opts) : |
|
366 """ |
|
367 URL for table with given opts, keeping our sorting/filtering unless overriden. |
|
368 """ |
|
369 |
|
370 args = dict() |
|
371 |
|
372 if filters : |
|
373 args.update(filters) |
|
374 |
|
375 if sort : |
|
376 args['sort'] = sort |
|
377 |
|
378 if opts : |
|
379 args.update(opts) |
|
380 |
|
381 return self.url(self.TABLE_URL, **args) |
|
382 |
|
383 def sorturl (attr, sort=sort) : |
|
384 """ |
|
385 URL for table sorted by given column, reversing direction if already sorting by given column. |
|
386 """ |
|
387 |
|
388 if not sort : |
|
389 sort = attr |
|
390 elif sort.lstrip('+-') != attr : |
|
391 sort = attr |
|
392 elif sort.startswith('-') : |
|
393 sort = "+" + attr |
|
394 else : |
|
395 sort = "-" + attr |
|
396 |
|
397 return url(sort=sort) |
|
398 |
|
399 def itemurl (item) : |
|
400 """ |
|
401 URL for given item, by id. |
|
402 """ |
|
403 |
|
404 if self.TABLE_ITEM_URL : |
|
405 # separate page |
|
406 return self.url(self.TABLE_ITEM_URL, id=item.id) |
|
407 else : |
|
408 # to our table |
|
409 return url() + '#{id}'.format(id=item.id) |
|
410 |
|
411 def render_filter (attr) : |
|
412 """ |
|
413 Render filter-input for column. |
|
414 """ |
|
415 |
|
416 value = filters.get(attr) |
|
417 |
|
418 if value : |
|
419 # XXX: multi-valued filters? |
|
420 value = value[0] |
|
421 else : |
|
422 value = None |
|
423 |
|
424 return html.input(type='text', name=attr, value=value) |
|
425 |
|
426 def render_head () : |
|
427 """ |
|
428 Yield header, filter rows for columns in table header. |
|
429 """ |
|
430 |
|
431 # id |
|
432 yield html.td('#'), html.td(html.input(type='submit', value=u'\u00BF')) |
|
433 |
|
434 for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in self.TABLE_COLUMNS : |
|
435 header = title |
|
436 |
|
437 if sort : |
|
438 header = html.a(href=sorturl(attr))(header) |
|
439 |
|
440 header = html.th(header) |
|
441 |
|
442 if filters is not None and filter : |
|
443 filter = render_filter(attr) |
|
444 else : |
|
445 filter = None |
|
446 |
|
447 if colcss is True : |
|
448 colcss = attr |
|
449 |
|
450 filter = html.td(class_=colcss)(filter) |
|
451 |
|
452 yield header, filter |
|
453 |
|
454 def render_cell (attr, value, rowhtml=None, colcss=True, filter=None, rowtitle=None, rowcss=None, hilight=hilight) : |
|
455 """ |
|
456 Render a single cell. |
|
457 |
|
458 colcss - css class for column; True -> attr |
|
459 filter - render filter link for value? |
|
460 htmlvalue - rendered value? |
|
461 title - mouseover title for cell |
|
462 rowcss - css class for row |
|
463 """ |
|
464 |
|
465 if not rowhtml : |
|
466 rowhtml = value |
|
467 |
|
468 if filter : |
|
469 cell = html.a(href=url(filters=None, **{attr: value}))(rowhtml) |
|
470 else : |
|
471 cell = rowhtml |
|
472 |
|
473 if colcss is True : |
|
474 colcss = attr |
|
475 |
|
476 if hilight : |
|
477 hilight = attr in hilight and value in hilight[attr] |
|
478 |
|
479 css = (colcss, rowcss, 'hilight' if hilight else None) |
|
480 css = ' '.join(cls for cls in css if cls) |
|
481 |
|
482 return html.td(class_=css, title=rowtitle)(cell) |
|
483 |
|
484 def render_row (item) : |
|
485 """ |
|
486 Yield columns for row. |
|
487 """ |
|
488 |
|
489 for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in self.TABLE_COLUMNS : |
|
490 # XXX: this is sometimes broken, figure out how to index by column |
|
491 value = getattr(item, attr) |
|
492 |
|
493 if rowhtml : |
|
494 rowhtml = rowhtml(item) |
|
495 else : |
|
496 rowhtml = value |
|
497 |
|
498 if rowtitle : |
|
499 rowtitle = rowtitle(item) |
|
500 else : |
|
501 rowtitle = None |
|
502 |
|
503 if rowcss : |
|
504 rowcss = rowcss(item) |
|
505 else : |
|
506 rowcss = None |
|
507 |
|
508 yield render_cell(attr, value, |
|
509 rowhtml = rowhtml, |
|
510 colcss = colcss, |
|
511 filter = value if rowfilter else None, |
|
512 rowtitle = rowtitle, |
|
513 rowcss = rowcss, |
|
514 ) |
|
515 |
|
516 def render_body (rows) : |
|
517 """ |
|
518 Yield rows. |
|
519 """ |
|
520 |
|
521 for i, item in enumerate(rows) : |
|
522 yield html.tr(class_=('alternate' if i % 2 else None), id=item.id)( |
|
523 html.th( |
|
524 html.a(href=itemurl(item))("#") |
|
525 ), |
|
526 |
|
527 render_row(item) |
|
528 ) |
|
529 |
|
530 def render_pagination (page, count=None) : |
|
531 """ |
|
532 Render pagination links. |
|
533 """ |
|
534 |
|
535 if count is not None : |
|
536 pages = int(math.ceil(count / self.TABLE_PAGE)) |
|
537 else : |
|
538 pages = None |
|
539 |
|
540 if page > 0 : |
|
541 yield html.a(href=url(page=0))(html("«« First")) |
|
542 yield html.a(href=url(page=(page - 1)))(html("« Prev")) |
|
543 |
|
544 yield html.span("Page {page} of {pages}".format(page=(page + 1), pages=(pages or '???'))) |
|
545 |
|
546 yield html.a(href=url(page=(page + 1)))(html("» Next")) |
|
547 |
|
548 |
|
549 def render_foot () : |
|
550 # XXX: does separate SELECT count() |
|
551 count = query.count() |
|
552 |
|
553 if page : |
|
554 return render_pagination(page, count) |
|
555 else : |
|
556 return "{count} hosts".format(count=count) |
|
557 |
|
558 # columns for the two header rows |
|
559 headers, filtering = zip(*list(render_head())) |
|
560 |
|
561 # render table |
|
562 table = html.table( |
|
563 html.caption(caption) if caption else None, |
|
564 html.thead( |
|
565 html.tr(headers), |
|
566 # filters? |
|
567 html.tr(class_='filter')(filtering) if filters is not None else None, |
|
568 ), |
|
569 html.tbody( |
|
570 render_body(query) |
|
571 ), |
|
572 html.tfoot( |
|
573 html.tr( |
|
574 html.td(colspan=(1 + len(self.TABLE_COLUMNS)))( |
|
575 render_foot() |
|
576 ) |
|
577 ) |
|
578 ) |
|
579 ) |
|
580 |
|
581 # filters form? |
|
582 if filters is None : |
|
583 return table |
|
584 else : |
|
585 return html.form(method='get', action=url(filters=None, sort=None))( |
|
586 html.input(type='hidden', name='sort', value=sort), |
|
587 table, |
|
588 ) |
|
589 |
|
590 class ItemHandler (BaseHandler) : |
|
591 """ |
237 """ |
592 A specific DHCP host, along with a list of related hosts. |
238 A specific DHCP host, along with a list of related hosts. |
593 """ |
239 """ |
594 |
240 |
595 def process (self, id) : |
241 def process (self, id) : |
636 self.render_table(self.hosts, sort=self.sorts, hilight=dict(ip=self.host.ip, mac=self.host.mac)), |
282 self.render_table(self.hosts, sort=self.sorts, hilight=dict(ip=self.host.ip, mac=self.host.mac)), |
637 |
283 |
638 html.a(href=self.url(ListHandler))(html('«'), 'Back'), |
284 html.a(href=self.url(ListHandler))(html('«'), 'Back'), |
639 ) |
285 ) |
640 |
286 |
641 |
287 class ListHandler (HostsHandler) : |
642 class ListHandler (BaseHandler) : |
|
643 """ |
288 """ |
644 List of DHCP hosts for given filter. |
289 List of DHCP hosts for given filter. |
645 """ |
290 """ |
646 |
291 |
647 TABLE_PAGE = 10 |
292 TABLE_ITEM_URL = ItemHandler |
648 |
293 |
649 def process (self) : |
294 def process (self) : |
650 hosts = self.query() |
295 # super |
651 |
296 table.TableHandler.process(self) |
652 # filter |
297 |
653 self.filters, hosts = self.filter(hosts) |
|
654 |
|
655 # sort XXX: default per filter column? |
|
656 self.sorts, hosts = self.sort(hosts) |
|
657 |
|
658 # page? |
|
659 self.page = self.request.args.get('page') |
|
660 |
|
661 if self.page : |
|
662 self.page = int(self.page) |
|
663 |
|
664 hosts = hosts.offset(self.page * self.PAGE).limit(self.PAGE) |
|
665 |
|
666 self.hosts = hosts |
|
667 |
|
668 def title (self) : |
298 def title (self) : |
669 if self.filters : |
299 if self.filters : |
670 return "DHCP Hosts: {filters}".format(filters=self.filters_title()) |
300 return "DHCP Hosts: {filters}".format(filters=self.filters_title()) |
671 else : |
301 else : |
672 return "DHCP Hosts" |
302 return "DHCP Hosts" |
673 |
303 |
674 def render (self) : |
304 def render (self) : |
675 return ( |
305 return ( |
676 self.render_table(self.hosts, filters=self.filters, sort=self.sorts, page=self.page), |
306 self.render_table(self.query, filters=self.filters, sort=self.sorts, page=self.page), |
677 |
307 |
678 html.a(href=self.url())(html('«'), 'Back') if self.filters else None, |
308 #html.a(href=self.url())(html('«'), 'Back') if self.filters else None, |
679 ) |
309 ) |
680 |
310 |
681 # XXX: |
311 class RealtimeHandler (HostsHandler) : |
682 BaseHandler.TABLE_URL = ListHandler |
|
683 BaseHandler.TABLE_ITEM_URL = ItemHandler |
|
684 |
|
685 class RealtimeHandler (BaseHandler) : |
|
686 TITLE = "DHCP Pseudo-Realtime hosts.." |
312 TITLE = "DHCP Pseudo-Realtime hosts.." |
687 CSS = BaseHandler.CSS + ( |
313 CSS = HostsHandler.CSS + ( |
688 'http://code.jquery.com/ui/1.9.0/themes/base/jquery-ui.css', |
314 'http://code.jquery.com/ui/1.9.0/themes/base/jquery-ui.css', |
689 ) |
315 ) |
690 JS = ( |
316 JS = ( |
691 #"/static/jquery/jquery.js", |
317 #"/static/jquery/jquery.js", |
692 'http://code.jquery.com/jquery-1.8.2.js', |
318 'http://code.jquery.com/jquery-1.8.2.js', |