Bug#549227: UDD: please collect and expose the load time for update scripts

[Serafeim Zanikolas <serzan@hellug.gr>]

Hi guys,

On Wed, Oct 07, 2009 at 09:47:11PM +0200, Lucas Nussbaum wrote [edited]:
> On 01/10/09 at 20:08 +0200, Sandro Tosi wrote:
> > It's common in a datawarehouse system (like UDD can be considered) to keep track
> > of the update jobs times: start, end, duration, records elaborated and so on.
[..]
> A patch adding the table you describe would be appreciated (the code
> would have to be python)

Patch attached. I didn't add a duration column as it's trivially calculated on
the fly. I'm open to suggestions about getting record counts before and after
updates in a generic way.

Cheers,
Serafeim

ps. hacking UDD would be more fun without mixed indentation ;)

-- 
debtags-organised WNPP bugs: http://members.hellug.gr/serzan/wnpp

Index: udd.py
===================================================================
--- udd.py	(revision 1612)
+++ udd.py	(working copy)
@@ -8,7 +8,7 @@
 import string
 import sys
 from os import system
-from time import asctime
+import time
 import udd.aux
 import os.path
 
@@ -20,6 +20,23 @@
   for cmd in available_commands:
     print '  %s' % cmd
 
+def insert_timestamps(config, source, command, start_time, end_time):
+    connection = udd.aux.open_connection(config)
+    cur = connection.cursor()
+    values = { 'source' : source,
+               'command' : command,
+               'start_time' : start_time,
+               'end_time' : end_time }
+    cur.execute("""INSERT INTO udd_timestamps
+                            (source, command, start_time, end_time)
+                     VALUES (%(source)s, %(command)s, %(start_time)s,
+                             %(end_time)s)
+                """, values)
+    connection.commit()
+
+def get_timestamp():
+    return time.strftime('%Y-%m-%d %H:%M:%S')
+
 if __name__ == '__main__':
   if len(sys.argv) < 4:
     print_help()
@@ -46,25 +63,13 @@
       # can just use the gatherer's methods
       if command == 'update':
 	if "update-command" in src_config:
-	  if 'timestamp-dir' in config['general']:
-	    f = open(os.path.join(config['general']['timestamp-dir'],
-                                  src+".update-start"), "w")
-	    f.write(asctime())
-	    f.close()
+          start_time = get_timestamp()
 	  result = system(src_config['update-command']) 
 	  if result != 0:
 	    sys.exit(result)
-	  if 'timestamp-dir' in config['general']:
-	    f = open(os.path.join(config['general']['timestamp-dir'],
-                                  src+".update-end"), "w")
-	    f.write(asctime())
-	    f.close()
+        end_time = get_timestamp()
       else:
-	if 'timestamp-dir' in config['general']:
-	  f = open(os.path.join(config['general']['timestamp-dir'],
-                                src+".insert-start"), "w")
-	  f.write(asctime())
-	  f.close()
+	start_time = get_timestamp()
 	(src_command,rest) = types[type].split(None, 1)
 	if src_command == "exec":
 	  system(rest + " " + sys.argv[1] + " " + sys.argv[2] + " " + src)
@@ -83,11 +88,8 @@
 	  else:
 	    exec "gatherer.%s()" % command
 	  connection.commit()
-	if 'timestamp-dir' in config['general']:
-	  f = open(os.path.join(config['general']['timestamp-dir'],
-                                src+".insert-end"), "w")
-	  f.write(asctime())
-	  f.close()
+	end_time = get_timestamp()
+      insert_timestamps(config, src, command, start_time, end_time)
     except:
       udd.aux.unlock(config, src)
       raise
Index: sql/setup.sql
===================================================================
--- sql/setup.sql	(revision 1612)
+++ sql/setup.sql	(working copy)
@@ -535,6 +535,16 @@
 );
 GRANT SELECT ON wannabuild TO public;
 
+-- timings of data operations
+CREATE TABLE udd_timestamps (
+  id serial,
+  source text,
+  command text,
+  start_time timestamp,
+  end_time timestamp,
+  PRIMARY KEY (id)
+);
+GRANT SELECT ON udd_timestamps TO public;
 
 -- views
 -- bugs_count