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
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
"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 @@
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 @@
# 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 @@
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)