Add a database last-modified-time

Add a new table which will have one record, simply containing the
last-modified-time of the database as a whole. Will be useful for
caching in the visualisation layer.

Create the table at startup in the main VLANd process if it doesn't
exist, and create the record too.
Add code to update the record on all other changes to the database.
Add code to report it through the daemon.status() API call.

Change-Id: I503c4371c04accb2c3f91adecc3e8a8a8614ea90

6 files changed, 42 insertions, 4 deletions

diff --git a/admin.py b/admin.py
index 1230c81..1d9063b 100755
--- a/admin.py
+++ b/admin.py
@@ -22,6 +22,7 @@
 
 import os, sys
 import optparse
+import datetime, time
 
 vlandpath = os.path.abspath(os.path.normpath(os.path.dirname(sys.argv[0])))
 sys.path.insert(0, vlandpath)
@@ -748,6 +749,8 @@ elif opts.status:
     print 'The default vlan tag (%d) is vlan_id %d' % (config.vland.default_vlan_tag, default_vlan_id)
     stat = call_vland('daemon_query', {'command':'daemon.status', 'data': None})
     print 'VLANd is running %s' % stat['running']
+    lastmod = datetime.datetime.strptime(stat['last_modified'][0], '%Y-%m-%dT%H:%M:%S.%f')
+    print 'DB Last modified %s' % lastmod.strftime('%Y-%m-%d %H:%M:%S %Z')
     print 'DB via VLANd:'
     switches = call_vland('db_query', {'command':'db.all_switches', 'data':None})
     print '  knows about %d switch(es)' % len(switches)
diff --git a/db/db.py b/db/db.py
index 71e458d..180a934 100644
--- a/db/db.py
+++ b/db/db.py
@@ -34,10 +34,12 @@ if __name__ == '__main__':
 from errors import CriticalError, InputError
 
 class VlanDB:
-    def __init__(self, db_name="vland", username="vland"):
+    def __init__(self, db_name="vland", username="vland", readonly=True):
         try:
             self.connection = psycopg2.connect(database=db_name, user=username)
             self.cursor = self.connection.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)
+            if not readonly:
+                self._init_state()
         except Exception as e:
             logging.error("Failed to access database: %s", e)
             raise
@@ -46,6 +48,21 @@ class VlanDB:
         self.cursor.close()
         self.connection.close()
 
+    # Create the state table (if needed) and add its only record
+    def _init_state(self):
+        try:
+            sql = "SELECT * FROM state"
+            self.cursor.execute(sql)
+            self.cursor.execute('DELETE FROM state')
+        except psycopg2.ProgrammingError:
+            self.connection.commit() # state doesn't exist; clear error
+            sql = "CREATE TABLE state (last_modified TIMESTAMP)"
+            self.cursor.execute(sql)
+        sql = "INSERT INTO state (last_modified) VALUES (%s)"
+        data = (datetime.datetime.now(), )
+        self.cursor.execute(sql, data)
+        self.connection.commit()
+
     # Create a new switch in the database. Switches are really simple
     # devices - they're just containers for ports.
     #
@@ -62,6 +79,7 @@ class VlanDB:
             data = (name, )
             self.cursor.execute(sql, data)
             switch_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -108,6 +126,7 @@ class VlanDB:
                     current_vlan_id, base_vlan_id, TRUNK_ID_NONE)
             self.cursor.execute(sql, data)
             port_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -145,6 +164,7 @@ class VlanDB:
             data = (name, tag, is_base_vlan, dt)
             self.cursor.execute(sql, data)
             vlan_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -181,6 +201,7 @@ class VlanDB:
             data = (dt, )
             self.cursor.execute(sql, data)
             trunk_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
             # And update the ports
             for port_id in (port_id1, port_id2):
@@ -197,6 +218,7 @@ class VlanDB:
             sql = "DELETE FROM %s WHERE %s = %s" % (table, field, '%s')
             data = (value,)
             self.cursor.execute(sql, data)
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -526,6 +548,12 @@ class VlanDB:
     def get_trunk_by_id(self, trunk_id):
         return self._get_row("trunk", "trunk_id", int(trunk_id))
 
+    # Get the last-modified time for the database
+    def get_last_modified_time(self):
+        sql = "SELECT last_modified FROM state"
+        self.cursor.execute(sql)
+        return self.cursor.fetchone()
+
     # Grab one row of a query on one column; useful as a quick wrapper
     def _get_row(self, table, field, value):
 
@@ -559,6 +587,7 @@ class VlanDB:
             data = (is_locked, port_id)
             self.cursor.execute(sql, data)
             port_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -582,6 +611,7 @@ class VlanDB:
             data = (is_trunk, port_id)
             self.cursor.execute(sql, data)
             port_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -613,6 +643,7 @@ class VlanDB:
             data = (vlan_id, port_id)
             self.cursor.execute(sql, data)
             port_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -646,6 +677,7 @@ class VlanDB:
             data = (vlan_id, port_id)
             self.cursor.execute(sql, data)
             port_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
@@ -668,6 +700,7 @@ class VlanDB:
             data = (int(trunk_id), int(port_id))
             self.cursor.execute(sql, data)
             port_id = self.cursor.fetchone()[0]
+            self.cursor.execute("UPDATE state SET last_modified=%s", (datetime.datetime.now(),))
             self.connection.commit()
         except:
             self.connection.rollback()
diff --git a/db/setup_db.py b/db/setup_db.py
index 51e8f83..37ed7dc 100755
--- a/db/setup_db.py
+++ b/db/setup_db.py
@@ -45,6 +45,6 @@ cur.execute("CREATE TABLE vlan (vlan_id SERIAL, name VARCHAR(32),"
             "tag INTEGER, is_base_vlan BOOLEAN, creation_time TIMESTAMP)")
 cur.execute("CREATE TABLE trunk (trunk_id SERIAL,"
             "creation_time TIMESTAMP)")
-
+cur.execute("CREATE TABLE state (last_modified TIMESTAMP)")
 cur.execute("COMMIT;")
 
diff --git a/util.py b/util.py
index 9b30325..4c9816a 100644
--- a/util.py
+++ b/util.py
@@ -117,6 +117,7 @@ class VlanUtil:
             if command == 'daemon.status':
                 # data ignored
                 ret['running'] = 'ok'
+                ret['last_modified'] = state.db.get_last_modified_time()
             elif command == 'daemon.version':
                 # data ignored
                 ret['version'] = state.version
diff --git a/visualisation/visualisation.py b/visualisation/visualisation.py
index 164db5f..f004f70 100644
--- a/visualisation/visualisation.py
+++ b/visualisation/visualisation.py
@@ -59,7 +59,8 @@ class Visualisation(object):
     # The main loop for the visualisation webserver
     def visloop(self):
         self.state.db = VlanDB(db_name=self.state.config.database.dbname,
-                               username=self.state.config.database.username)
+                               username=self.state.config.database.username,
+                               readonly=True)
 
         loglevel = VlanUtil().set_logging_level(self.state.config.logging.level)
 
diff --git a/vland.py b/vland.py
index 5595caf..3dc50f9 100755
--- a/vland.py
+++ b/vland.py
@@ -59,7 +59,7 @@ util = VlanUtil()
 
 print 'Connecting to DB...'
 state.db = VlanDB(db_name=state.config.database.dbname,
-                  username=state.config.database.username)
+                  username=state.config.database.username, readonly=False)
 
 switches = state.db.all_switches()
 print '  DB knows about %d switches' % len(switches)