From 425f54886ded0acc7a256921d51059509c46045b Mon Sep 17 00:00:00 2001 From: jasplin Date: Fri, 18 Feb 2011 10:14:09 +0100 Subject: Added basic annotation. On the rankings page, a note for an individual benchmark can now be edited. This is the first step towards a more sophisticated annotation concept. It could for example be useful to interactively classify a benchmark as "suspended" or "active". --- database/scripts/plpgsqlfuncs.sql | 27 +++++++++ database/scripts/privileges.sql | 6 ++ database/scripts/tabledefs.sql | 25 ++++++++ scripts/finalizeresults.py | 2 +- scripts/getrankings.py | 31 ++++++++-- scripts/getstats.py | 23 +++++++- scripts/settimeseriesnote.py | 45 +++++++++++++++ web/getstats/rankings.js | 116 +++++++++++++++++++++++++++++++++----- web/getstats/rankings.shtml | 11 ++-- web/getstats/rankingtabledef.html | 1 + 10 files changed, 260 insertions(+), 27 deletions(-) create mode 100644 scripts/settimeseriesnote.py diff --git a/database/scripts/plpgsqlfuncs.sql b/database/scripts/plpgsqlfuncs.sql index d575a06..052a54e 100644 --- a/database/scripts/plpgsqlfuncs.sql +++ b/database/scripts/plpgsqlfuncs.sql @@ -26,3 +26,30 @@ BEGIN END; $$ LANGUAGE plpgsql; + + +-- Inserts or updates a row in the 'timeSeriesAnnotation' table. +CREATE OR REPLACE FUNCTION merge_timeSeriesNote( + hostId_ BIGINT, platformId_ BIGINT, branchId_ BIGINT, + benchmarkId_ BIGINT, metricId_ BIGINT, note_ TEXT) RETURNS VOID AS +$$ +BEGIN + BEGIN + INSERT INTO timeSeriesAnnotation( + hostId, platformId, branchId, benchmarkId, metricId, note) + VALUES ( + hostId_, platformId_, branchId_, benchmarkId_, metricId_, note_); + RETURN; + EXCEPTION WHEN unique_violation THEN + UPDATE timeSeriesAnnotation + SET hostId = hostId_, platformId = platformId_, branchId = branchId_, + note = note_ + WHERE hostId = hostId_ + AND platformId = platformId_ + AND branchId = branchId_ + AND benchmarkId = benchmarkId_ + AND metricId = metricId_; + END; +END; +$$ +LANGUAGE plpgsql; diff --git a/database/scripts/privileges.sql b/database/scripts/privileges.sql index a88f1f7..7571fc6 100644 --- a/database/scripts/privileges.sql +++ b/database/scripts/privileges.sql @@ -52,3 +52,9 @@ GRANT SELECT ON ranking TO bmuser; GRANT INSERT ON ranking TO bmuser; GRANT UPDATE ON ranking TO bmuser; GRANT UPDATE ON ranking_id_seq TO bmuser; + +GRANT SELECT ON timeSeriesAnnotation TO bmuser; +GRANT INSERT ON timeSeriesAnnotation TO bmuser; +GRANT UPDATE ON timeSeriesAnnotation TO bmuser; +GRANT DELETE ON timeSeriesAnnotation TO bmuser; +GRANT UPDATE ON timeSeriesAnnotation_id_seq TO bmuser; diff --git a/database/scripts/tabledefs.sql b/database/scripts/tabledefs.sql index 20bf9df..31c25fc 100644 --- a/database/scripts/tabledefs.sql +++ b/database/scripts/tabledefs.sql @@ -130,3 +130,28 @@ CREATE INDEX ranking_metric_idx ON ranking (metricId); CREATE INDEX ranking_lctimestamp_idx ON ranking (lastChangeTimestamp); CREATE INDEX ranking_stat_idx ON ranking (statId); CREATE INDEX ranking_pos_idx ON ranking (pos); + + +-- Time series annotations: +CREATE TABLE timeSeriesAnnotation +( + id BIGSERIAL PRIMARY KEY, + + -- Time series key: + hostId BIGINT NOT NULL REFERENCES host ON DELETE CASCADE, + platformId BIGINT NOT NULL REFERENCES platform ON DELETE CASCADE, + branchId BIGINT NOT NULL REFERENCES branch ON DELETE CASCADE, + benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE, + metricId BIGINT NOT NULL REFERENCES metric ON DELETE CASCADE, + + -- Annotations: + note TEXT NOT NULL, + + UNIQUE (hostId, platformId, branchId, benchmarkId, metricId) +) WITH (OIDS=FALSE); +ALTER TABLE ranking OWNER TO postgres; +CREATE INDEX tsanno_host_idx ON timeSeriesAnnotation (hostId); +CREATE INDEX tsanno_platform_idx ON timeSeriesAnnotation (platformId); +CREATE INDEX tsanno_branch_idx ON timeSeriesAnnotation (branchId); +CREATE INDEX tsanno_benchmark_idx ON timeSeriesAnnotation (benchmarkId); +CREATE INDEX tsanno_metric_idx ON timeSeriesAnnotation (metricId); diff --git a/scripts/finalizeresults.py b/scripts/finalizeresults.py index 17d2bbe..8b32365 100755 --- a/scripts/finalizeresults.py +++ b/scripts/finalizeresults.py @@ -332,7 +332,7 @@ if context2_id == -1: updateRankings(host_id, platform_id, branch_id, sha12_id, context2_id) -# Make sure everything is written to the database: +# Write to database: commit() print "finalization done" diff --git a/scripts/getrankings.py b/scripts/getrankings.py index 1ed2f3b..d1106f8 100644 --- a/scripts/getrankings.py +++ b/scripts/getrankings.py @@ -59,6 +59,17 @@ class GetRankings: rankings = {} context_ids = set([self.context2_id]) # Affected context IDs + + # Get all time series notes: + qres = execQuery( + "SELECT benchmarkId, metricId, note FROM timeSeriesAnnotation" + " WHERE hostId = %d AND platformId = %d AND branchId = %d" + % (self.host_id, self.platform_id, self.branch_id)) + notes = {} + for benchmark_id, metric_id, note in qres: + notes[benchmark_id, metric_id] = note + + # Get rankings for each statistic: stat_infos = execQuery("SELECT id, value FROM rankingStat;") for stat_id, stat_name in stat_infos: @@ -74,7 +85,7 @@ class GetRankings: ranking = [] - # Apply test case filter: + # Apply test case filter and add notes: for row in ranking_all: benchmark_id = row[0] benchmark = idToText("benchmark", benchmark_id) @@ -82,7 +93,17 @@ class GetRankings: benchmarkToComponents(benchmark)) if ((self.test_case_filter == None) or (test_case in self.test_case_filter)): - ranking.append(row) + + # Append note if any: + metric_id = row[1] + try: + note = notes[benchmark_id, metric_id] + except KeyError: + note = "" + + ranking.append(( + benchmark_id, metric_id, row[2], row[3], row[4], + row[5], note)) for row in ranking: @@ -112,9 +133,9 @@ class GetRankings: ranking = [] for (benchmark_id, metric_id, context1_id, pos, value, - lc_timestamp) in ranking_without_deltas: + lc_timestamp, note) in ranking_without_deltas: row = [benchmark_id, metric_id, context1_id, pos, value, - lc_timestamp] + lc_timestamp, note] if pos >= 0: pos_prev = ranking_prev[benchmark_id, metric_id] if pos_prev >= 0: @@ -185,7 +206,7 @@ class GetRankings: 'snapshots': map( lambda s: (idToText("sha1", s[0]), s[1]), self.snapshots), 'rankings': self.rankings - }, sys.stdout, indent=4) + }, sys.stdout) class GetRankingsAsJSON(GetRankings): diff --git a/scripts/getstats.py b/scripts/getstats.py index 286a162..5527c46 100755 --- a/scripts/getstats.py +++ b/scripts/getstats.py @@ -10,6 +10,7 @@ from gettimeseriesstats import GetTimeSeriesStatsAsJSON from gettimeseriesdetails import GetTimeSeriesDetailsAsJSON from getsnapshots import GetSnapshotsAsJSON from getrankings import GetRankingsAsJSON +from settimeseriesnote import SetTimeSeriesNote from dbaccess import setDatabase from misc import printErrorAsJSON @@ -88,7 +89,9 @@ def createCommand(options, http_get): "--branch B --sha11 S --sha12 S | \\\n" + " --db D --cmd rankings --host H --platform P " + "--branch B --sha1 S [--testcasefilter 'TC1 TC2 ...'] " + - "[--maxsize M]") + "[--maxsize M] | \\\n" + + " --db D --cmd settimeseriesnote --host H --platform P " + + "--branch B --benchmark B --metric M --note N") if http_get: printErrorAsJSON("usage error") @@ -309,6 +312,24 @@ def createCommand(options, http_get): return GetRankingsAsJSON( host, platform, branch, sha1, test_case_filter, maxsize) + # --- 'settimeseriesnote' --------------------------------- + # ### Hm ... this command doesn't really get statistics, so maybe + # rename getstats.py to something more generic + # (like bmclient.py) ....... 2 B DONE! + elif cmd == "settimeseriesnote": + if ("host" in options and "platform" in options and + "branch" in options and "benchmark" in options and + "metric" in options and "note" in options): + host = options["host"] + platform = options["platform"] + branch = options["branch"] + benchmark = options["benchmark"] + metric = options["metric"] + note = options["note"] + + return SetTimeSeriesNote( + host, platform, branch, benchmark, metric, note) + # No match: printUsageError() sys.exit(1) diff --git a/scripts/settimeseriesnote.py b/scripts/settimeseriesnote.py new file mode 100644 index 0000000..7e4aa5b --- /dev/null +++ b/scripts/settimeseriesnote.py @@ -0,0 +1,45 @@ +import sys +import json +from dbaccess import execQuery, database, commit +from misc import textToId, printJSONHeader + +class SetTimeSeriesNote: + + def __init__( + self, host, platform, branch, benchmark, metric, note): + self.host_id = textToId('host', host) + self.platform_id = textToId('platform', platform) + self.branch_id = textToId('branch', branch) + self.benchmark_id = textToId('benchmark', benchmark) + self.metric_id = textToId('metric', metric) + max_length = 256 + self.note = note.strip()[:max_length] + + def execute(self): + + if len(self.note) == 0: + # Delete the row from the table: + query = ( + "DELETE FROM timeSeriesAnnotation" + " WHERE hostId = %d" + " AND platformId = %d" + " AND branchId = %d" + " AND benchmarkId = %d" + " AND metricId = %d;" + % (self.host_id, self.platform_id, self.branch_id, + self.benchmark_id, self.metric_id)) + execQuery(query, False) + else: + # Insert or update table row: + query = ( + "SELECT merge_timeSeriesNote(%d, %d, %d, %d, %d, '%s');" + % (self.host_id, self.platform_id, self.branch_id, + self.benchmark_id, self.metric_id, self.note)) + execQuery(query, False) + + # Write to database: + commit() + + # Return empty reply to client: + printJSONHeader() + json.dump({ }, sys.stdout) diff --git a/web/getstats/rankings.js b/web/getstats/rankings.js index a66e1f0..ad33b1b 100644 --- a/web/getstats/rankings.js +++ b/web/getstats/rankings.js @@ -30,6 +30,83 @@ function selectRankingTable() { } } + +// Submits a note for a given time series. +function submitTimeSeriesNote( + textInput, tableSel, database, host, platform, branch, benchmark, metric) { + + updateStatus("updating note ...", true); + + // Synchronize other ranking tables: + // ### WORK IN PROGRESS: + // var types = ["qs", "lcssr", "lcssi", "lcss1r", "lcss1i"]; + // for (var i in types) { + // var tsel = "#rankingTable_" + types[i]; + // if (tsel == tableSel) + // continue; + // alert("i: " + i + ", tsel: " + tsel); + // var tr = $(tsel + " tr").find( + // "td:nth-child(6):contains(" + metric + ")").find( + // "td:nth-child(7):contains(" + benchmark + ")"); + // alert("tr.length: " + tr.length); + // } + + + // Update database: + query = "?db=" + database + + "&cmd=settimeseriesnote" + + "&host=" + encodeURIComponent(host) + + "&platform=" + encodeURIComponent(platform) + + "&branch=" + encodeURIComponent(branch) + + "&benchmark=" + encodeURIComponent(benchmark) + + "&metric=" + encodeURIComponent(metric) + + "¬e=" + encodeURIComponent(textInput.value.substring(0, 256)); + + url = "http://" + location.host + "/cgi-bin/getstatswrapper" + query; + //alert("url: >" + url + "<"); + + $.ajax({ + url: url, + type: "GET", + dataType: "json", + + success: function(data, textStatus, request) { + if (request.readyState == 4) { + if (request.status == 200) { + + if (data.error != null) { + updateStatus( + "updating note ... failed: " + + data.error, false); + return + } + + updateStatus("updating note ... done", false); + updateStatus("", false); + } + } + }, + + error: function(request, textStatus, errorThrown) { + descr = errorThrown; + if (errorThrown == null) { + descr = "undefined error - is the server down?"; + } + updateStatus("updating note ... error: " + descr, false); + } + + // complete: function(request, textStatus) { + // alert("complete; request.status: " + request.status) + // } + + }); +} + +// Submits a note for a given snapshot in a given time series. +function submitSnapshotNote(textInput) { + // 2 B DONE! +} + function populateRankingTable( tableSel, rankings, database, host, platform, branch, sha11, sha12, difftol, durtolmin, durtolmax, bmarkId2Name, metricId2Name) { @@ -49,8 +126,9 @@ function populateRankingTable( var pos = row[3]; var val = row[4]; var lcTimestamp = row[5]; - var hasPrevDelta = (row.length > 6); - var prevDelta = hasPrevDelta ? row[6] : null; + var note = row[6]; + var hasPrevDelta = (row.length > 7); + var prevDelta = hasPrevDelta ? row[7] : null; benchmark = bmarkId2Name[bmarkId]; metric = metricId2Name[metricId]; @@ -94,7 +172,18 @@ function populateRankingTable( } html += "" + metric + ""; - html += "" + benchmark + ""; + html += "" + + benchmark + ""; + + html += "" + + ""; + html += ""; } @@ -146,6 +235,12 @@ function fetchRankings( var sha11 = data.snapshots[0][0]; var sha12 = data.snapshots[data.snapshots.length - 1][0]; + // ### The tolerance values should automatically be + // set to those hardcoded in finalizeresults.py! + // ... 2 B DONE! + difftol = 1.1; + durtolmin = 3; + durtolmax = 30; // Show context ... $("#main_context_database").text(data.database); @@ -154,20 +249,13 @@ function fetchRankings( $("#main_context_branch").text(data.branch); $("#main_context_sha11").text(sha11); $("#main_context_sha12").text(sha12); - $("#main_context_difftol").text("---"); - $("#main_context_durtolmin").text("---"); - $("#main_context_durtolmax").text("---"); + $("#main_context_difftol").text(difftol); + $("#main_context_durtolmin").text(durtolmin); + $("#main_context_durtolmax").text(durtolmax); setSnapshots(data.snapshots); - // ### The tolerance values should automatically be - // set to those hardcoded in finalizeresults.py! - // ... 2 B DONE! - difftol = 1.1; - durtolmin = 3; - durtolmax = 30; - var bmarkId2Name = []; for (var i = 0; i < data.benchmarks.length; ++i) { bmarkInfo = data.benchmarks[i]; @@ -180,7 +268,7 @@ function fetchRankings( metricId2Name[metricInfo[0]] = metricInfo[1]; } - rankings = { + var rankings = { "qs": data.rankings.qs, "lcssr": data.rankings.lcssr, "lcssi": data.rankings.lcssi, diff --git a/web/getstats/rankings.shtml b/web/getstats/rankings.shtml index 611a806..dcac636 100644 --- a/web/getstats/rankings.shtml +++ b/web/getstats/rankings.shtml @@ -84,41 +84,40 @@ $("#mainPageLink").attr("href", "http://" + location.host + "/bm2");
+ cellspacing="1" style="width:100%">
+ cellspacing="1" style="width:100%">
+ cellspacing="1" style="width:100%">
+ cellspacing="1" style="width:100%">
+ cellspacing="1" style="width:100%">
- diff --git a/web/getstats/rankingtabledef.html b/web/getstats/rankingtabledef.html index ca0758c..6eb662b 100644 --- a/web/getstats/rankingtabledef.html +++ b/web/getstats/rankingtabledef.html @@ -7,6 +7,7 @@ LCDA Metric Benchmark + Note -- cgit v1.2.3