--- a/bin/pvl.wlan-sta	Fri Jul 05 00:59:36 2013 +0300
+++ b/bin/pvl.wlan-sta	Fri Jul 05 00:59:56 2013 +0300
@@ -37,13 +37,11 @@
     # common
     parser.add_option_group(pvl.args.parser(parser))
     parser.add_option_group(pvl.syslog.args.parser(parser, prog=WLAN_STA_PROG))
+    parser.add_option_group(pvl.verkko.db.parser(parser, table=db.wlan_sta))
 
     parser.add_option('--interfaces', metavar='PATH',
             help="Load interface/node names from mapping file")
 
-    parser.set_defaults(
-    )
-
     # parse
     options, args = parser.parse_args(args)
 
@@ -53,16 +51,81 @@
     return options, args
 
 import re
+from pvl.verkko import db
 
-class HostapdHandler (object) :
+class WlanStaDatabase (object) :
     HOSTAPD_STA_RE = re.compile(r'(?P<wlan>.+?): STA (?P<sta>.+?) (?P<msg>.+)')
 
-    def __init__ (self, interface_map=None) :
+    DB_TABLE = db.wlan_sta
+    DB_LAST_SEEN = db.wlan_sta.c.last_seen
+    DB_SELECT = (db.dhcp_hosts.c.gw, db.dhcp_hosts.c.ip)
+
+    def __init__ (self, db, interface_map=None) :
         """
             interface_map       - {(hostname, interface): (nodename, wlan)}
         """
+        self.db = db
         self.interface_map = interface_map
 
+
+    def select (self, distinct=DB_SELECT, interval=None) :
+        """
+            SELECT unique gw/ip hosts, for given interval.
+        """
+
+        query = db.select(distinct, distinct=True)
+
+        if interval :
+            # timedelta
+            query = query.where(db.func.now() - self.DB_LAST_SEEN < interval)
+
+        return self.db.select(query)
+
+    def insert (self, key, update, timestamp, count=True) :
+        """
+            INSERT new host
+        """
+
+        query = self.DB_TABLE.insert().values(**key).values(**update).values(
+                first_seen  = timestamp,
+                last_seen   = timestamp,
+        )
+
+        if count :
+            query = query.values(count=1)
+        
+        # -> id
+        return self.db.insert(query)
+
+    def update (self, key, update, timestamp, count=True) :
+        """
+            UPDATE existing host, or return False if not found.
+        """
+
+        table = self.DB_TABLE
+        query = table.update()
+
+        for col, value in key.iteritems() :
+            query = query.where(table.c[col] == value)
+
+        query = query.values(last_seen=timestamp)
+
+        if count :
+            query = query.values(count=db.func.coalesce(table.c.count, 0) + 1)
+
+        query = query.values(**update)
+        
+        # -> any matched rows?
+        return self.db.update(query)
+
+    def touch (self, key, update, timestamp, **opts) :
+        # update existing?
+        if self.update(key, update, timestamp, **opts) :
+            log.info("Update: %s: %s: %s", key, update, timestamp)
+        else :
+            log.info("Insert: %s: %s: %s", key, update, timestamp)
+            self.insert(key, update, timestamp, **opts)
+
     def parse (self, item) :
         """
             Parse fields from a hostapd syslog message.
@@ -73,31 +136,39 @@
         if not match :
             return None
 
-        return dict(item, **match.groupdict())
+        return match.groupdict()
 
-    def build_sta (self, match) :
-        ap, wlan = match['host'], match['wlan']
+    def __call__ (self, item) :
+        match = self.parse(item)
+
+        if not match :
+            return
+
+        # lookup?
+        ap, wlan = item['host'], match['wlan']
         
-        # override?
         if self.interface_map :
             mapping = self.interface_map.get((ap, wlan))
 
             if mapping :
                 ap, wlan = mapping
         
-        build = dict(
-                timestamp       = match['timestamp'],
+        # update/insert
+        self.touch(
+            dict(
                 ap              = ap,
                 wlan            = wlan,
                 sta             = match['sta'],
+            ), dict(
                 msg             = match['msg'],
+            ), item['timestamp']
         )
 
-
-        return build
-
 def main (argv) :
     options, args = parse_argv(argv)
+    
+    # database
+    db = pvl.verkko.db.apply(options)
 
     if options.interfaces :
         interfaces = dict(pvl.rrd.hosts.map_interfaces(options, open(options.interfaces)))
@@ -107,21 +178,14 @@
     # syslog
     log.info("Open up syslog...")
     syslog = pvl.syslog.args.apply(options)
-    handler = HostapdHandler(interface_map=interfaces)
+
+    # handler
+    handler = WlanStaDatabase(db, interface_map=interfaces)
 
     log.info("Enter mainloop...")
     for source in syslog.main() :
         for item in source:
-            match = handler.parse(item)
-
-            if not match :
-                continue
-            elif 'sta' in match :
-                sta = handler.build_sta(match)
-            else :
-                continue
-        
-            print "{ap:>30}/{wlan:10} {sta:20} : {msg}".format(**sta)
+            handler(item)
 
     return 0