Perf dashboard should store commit logs
authorrniwa@webkit.org <rniwa@webkit.org@268f45cc-cd09-0410-ab3c-d52691b4dbfc>
Wed, 8 Oct 2014 17:20:32 +0000 (17:20 +0000)
committerrniwa@webkit.org <rniwa@webkit.org@268f45cc-cd09-0410-ab3c-d52691b4dbfc>
Wed, 8 Oct 2014 17:20:32 +0000 (17:20 +0000)
https://bugs.webkit.org/show_bug.cgi?id=137510

Reviewed by Darin Adler.

For the v2 version of the perf dashboard, we would like to be able to see commit logs in the dashboard itself.

This patch replaces "build_revisions" table with "commits" and "build_commits" relations to store commit logs,
and add JSON APIs to report and retrieve them. It also adds a tools/pull-svn.py to pull commit logs from
a subversion directory. The git version of this script will be added in a follow up patch.

In the new database schema, each revision in each repository is represented by exactly one row in "commits"
instead of one row for each build in "build_revisions". "commits" and "builds" now have a proper many-to-many
relationship via "build_commits" relations.

In order to migrate an existing instance of this application, run the following SQL commands:

BEGIN;

INSERT INTO commits (commit_repository, commit_revision, commit_time)
    (SELECT DISTINCT ON (revision_repository, revision_value)
        revision_repository, revision_value, revision_time FROM build_revisions);

INSERT INTO build_commits (commit_build, build_commit) SELECT revision_build, commit_id
    FROM commits, build_revisions
    WHERE commit_repository = revision_repository AND commit_revision = revision_value;

DROP TABLE build_revisions;

COMMIT;

The helper script to submit commit logs can be used as follows:

python ./tools/pull-svn.py "WebKit" https://svn.webkit.org/repository/webkit/ https://perf.webkit.org
    feeder-slave feeder-slave-password 60 "webkit-patch find-users"

The above command will pull the subversion server at https://svn.webkit.org/repository/webkit/ every 60 seconds
to retrieve at most 10 commits, and submits the results to https://perf.webkit.org using "feeder-slave" and
"feeder-slave-password" as the builder name and the builder password respectively.

The last, optional, argument is the shell command to convert a subversion account to the corresponding username.
e.g. "webkit-patch find-users rniwa@webkit.org" yields "Ryosuke Niwa" <rniwa@webkit.org> in the stdout.

* init-database.sql: Replaced "build_revisions" relation with "commits" and "build_commits" relations.

* public/api/commits.php: Added. Retrieves a list of commits based on arguments in its path of the form
    /api/commits/<repository-name>/<filter>. The behavior of this API depends on <filter> as follows:

    - Not specified - It returns every single commit for a given repository.
    - Matches "oldest" - It returns the commit with the oldest timestamp.
    - Matches "latest" - It returns the commit with the latest timestamp.
    - Matches "last-reported" - It returns the commit with the latest timestamp added via report-commits.php.
    - Is entirely alphanumeric - It returns the commit whose revision matches the filter.
    - Is of the form <alphanumeric>:<alphanumeric> or <alphanumeric>-<alphanumeric> - It retrieves the list
    of commits added via report-commits.php between two timestamps retrieved from commits whose revisions
    match the two alphanumeric values specified. Because it retrieves commits based on their timestamps,
    the list may contain commits that do not appear as neither hash's ancestor in git/mercurial.
(main):
(commit_from_revision):
(fetch_commits_between):
(format_commits):

* public/api/report-commits.php: Added. A JSON API to report new subversion, git, or mercurial commits.
See tests/api-report-commits.js for examples on how to use this API.

* public/api/runs.php: Updated the query to use "commit_builds" and "commits" relations instead of
"build_revisions". Regrettably, the new query is 20% slower but I'm going to wait until the new UI is ready
to optimize this and other JSON APIs.

* public/include/db.php:
(Database::select_or_insert_row):
(Database::update_or_insert_row): Added.
(Database::_select_update_or_insert_row): Extracted from select_or_insert_row. Try to update first and then
insert if the update fails for update_or_insert_row. Preserves the old behavior when $should_update is false.

(Database::select_first_row):
(Database::select_last_row): Added.
(Database::select_first_or_last_row): Extracted from select_first_row. Fixed a bug that we were asserting
$order_by to be not alphanumeric/underscore. Retrieve the last row instead of the first if $descending_order.

* public/include/report-processor.php:
(ReportProcessor::resolve_build_id): Store commits instead of build_revisions. We don't worry about the race
condition for adding "build_commits" rows since we shouldn't have a single tester submitting the same result
concurrently. Even if it happened, it will only result in a PHP error and the database will stay consistent.

* run-tests.js:
(pathToTests): Don't call path.resolve with "undefined" testName; It throws an exception in the latest node.js.

* tests/api-report-commits.js: Added.
* tests/api-report.js: Fixed a test per build_revisions to build_commits/commits replacement.

* tools: Added.
* tools/pull-svn.py: Added. See above for how to use this script.
(main):
(determine_first_revision_to_fetch):
(fetch_revision_from_dasbhoard):
(fetch_commit_and_resolve_author):
(fetch_commit):
(textContent):
(resolve_author_name_from_email):
(submit_commits):

git-svn-id: https://svn.webkit.org/repository/webkit/trunk@174459 268f45cc-cd09-0410-ab3c-d52691b4dbfc

Websites/perf.webkit.org/ChangeLog
Websites/perf.webkit.org/init-database.sql
Websites/perf.webkit.org/public/api/commits.php [new file with mode: 0644]
Websites/perf.webkit.org/public/api/report-commits.php [new file with mode: 0644]
Websites/perf.webkit.org/public/api/runs.php
Websites/perf.webkit.org/public/include/db.php
Websites/perf.webkit.org/public/include/report-processor.php
Websites/perf.webkit.org/run-tests.js
Websites/perf.webkit.org/tests/api-report-commits.js [new file with mode: 0644]
Websites/perf.webkit.org/tests/api-report.js
Websites/perf.webkit.org/tools/pull-svn.py [new file with mode: 0644]

index ea034d1..7942783 100644 (file)
@@ -1,3 +1,110 @@
+2014-10-08  Ryosuke Niwa  <rniwa@webkit.org>
+
+        Perf dashboard should store commit logs
+        https://bugs.webkit.org/show_bug.cgi?id=137510
+
+        Reviewed by Darin Adler.
+
+        For the v2 version of the perf dashboard, we would like to be able to see commit logs in the dashboard itself.
+
+        This patch replaces "build_revisions" table with "commits" and "build_commits" relations to store commit logs,
+        and add JSON APIs to report and retrieve them. It also adds a tools/pull-svn.py to pull commit logs from
+        a subversion directory. The git version of this script will be added in a follow up patch.
+
+
+        In the new database schema, each revision in each repository is represented by exactly one row in "commits"
+        instead of one row for each build in "build_revisions". "commits" and "builds" now have a proper many-to-many
+        relationship via "build_commits" relations.
+
+        In order to migrate an existing instance of this application, run the following SQL commands:
+
+        BEGIN;
+
+        INSERT INTO commits (commit_repository, commit_revision, commit_time)
+            (SELECT DISTINCT ON (revision_repository, revision_value)
+                revision_repository, revision_value, revision_time FROM build_revisions);
+
+        INSERT INTO build_commits (commit_build, build_commit) SELECT revision_build, commit_id
+            FROM commits, build_revisions
+            WHERE commit_repository = revision_repository AND commit_revision = revision_value;
+
+        DROP TABLE build_revisions;
+
+        COMMIT;
+
+
+        The helper script to submit commit logs can be used as follows:
+
+        python ./tools/pull-svn.py "WebKit" https://svn.webkit.org/repository/webkit/ https://perf.webkit.org
+            feeder-slave feeder-slave-password 60 "webkit-patch find-users"
+
+        The above command will pull the subversion server at https://svn.webkit.org/repository/webkit/ every 60 seconds
+        to retrieve at most 10 commits, and submits the results to https://perf.webkit.org using "feeder-slave" and
+        "feeder-slave-password" as the builder name and the builder password respectively.
+
+        The last, optional, argument is the shell command to convert a subversion account to the corresponding username.
+        e.g. "webkit-patch find-users rniwa@webkit.org" yields "Ryosuke Niwa" <rniwa@webkit.org> in the stdout.
+
+
+        * init-database.sql: Replaced "build_revisions" relation with "commits" and "build_commits" relations.
+
+        * public/api/commits.php: Added. Retrieves a list of commits based on arguments in its path of the form
+            /api/commits/<repository-name>/<filter>. The behavior of this API depends on <filter> as follows:
+
+            - Not specified - It returns every single commit for a given repository.
+            - Matches "oldest" - It returns the commit with the oldest timestamp.
+            - Matches "latest" - It returns the commit with the latest timestamp.
+            - Matches "last-reported" - It returns the commit with the latest timestamp added via report-commits.php.
+            - Is entirely alphanumeric - It returns the commit whose revision matches the filter.
+            - Is of the form <alphanumeric>:<alphanumeric> or <alphanumeric>-<alphanumeric> - It retrieves the list
+            of commits added via report-commits.php between two timestamps retrieved from commits whose revisions
+            match the two alphanumeric values specified. Because it retrieves commits based on their timestamps,
+            the list may contain commits that do not appear as neither hash's ancestor in git/mercurial.
+        (main):
+        (commit_from_revision):
+        (fetch_commits_between):
+        (format_commits):
+
+        * public/api/report-commits.php: Added. A JSON API to report new subversion, git, or mercurial commits.
+        See tests/api-report-commits.js for examples on how to use this API.
+
+        * public/api/runs.php: Updated the query to use "commit_builds" and "commits" relations instead of
+        "build_revisions". Regrettably, the new query is 20% slower but I'm going to wait until the new UI is ready
+        to optimize this and other JSON APIs.
+
+        * public/include/db.php:
+        (Database::select_or_insert_row):
+        (Database::update_or_insert_row): Added.
+        (Database::_select_update_or_insert_row): Extracted from select_or_insert_row. Try to update first and then
+        insert if the update fails for update_or_insert_row. Preserves the old behavior when $should_update is false.
+
+        (Database::select_first_row):
+        (Database::select_last_row): Added.
+        (Database::select_first_or_last_row): Extracted from select_first_row. Fixed a bug that we were asserting
+        $order_by to be not alphanumeric/underscore. Retrieve the last row instead of the first if $descending_order.
+
+        * public/include/report-processor.php:
+        (ReportProcessor::resolve_build_id): Store commits instead of build_revisions. We don't worry about the race
+        condition for adding "build_commits" rows since we shouldn't have a single tester submitting the same result
+        concurrently. Even if it happened, it will only result in a PHP error and the database will stay consistent.
+
+        * run-tests.js:
+        (pathToTests): Don't call path.resolve with "undefined" testName; It throws an exception in the latest node.js.
+
+        * tests/api-report-commits.js: Added.
+        * tests/api-report.js: Fixed a test per build_revisions to build_commits/commits replacement.
+
+        * tools: Added.
+        * tools/pull-svn.py: Added. See above for how to use this script.
+        (main):
+        (determine_first_revision_to_fetch):
+        (fetch_revision_from_dasbhoard):
+        (fetch_commit_and_resolve_author):
+        (fetch_commit):
+        (textContent):
+        (resolve_author_name_from_email):
+        (submit_commits):
+
 2014-09-30  Ryosuke Niwa  <rniwa@webkit.org>
 
         Update Install.md for Mavericks and fix typos
index 554ad44..148787e 100644 (file)
@@ -3,8 +3,9 @@ DROP TABLE test_runs CASCADE;
 DROP TABLE test_configurations CASCADE;
 DROP TYPE test_configuration_type CASCADE;
 DROP TABLE aggregators CASCADE;
-DROP TABLE build_revisions CASCADE;
 DROP TABLE builds CASCADE;
+DROP TABLE commits CASCADE;
+DROP TABLE build_commits CASCADE;
 DROP TABLE builders CASCADE;
 DROP TABLE repositories CASCADE;
 DROP TABLE platforms CASCADE;
@@ -50,14 +51,26 @@ CREATE TABLE builds (
     CONSTRAINT builder_build_time_tuple_must_be_unique UNIQUE(build_builder, build_number, build_time));
 CREATE INDEX build_builder_index ON builds(build_builder);
 
-CREATE TABLE build_revisions (
-    revision_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
-    revision_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
-    revision_value varchar(64) NOT NULL,
-    revision_time timestamp,
-    PRIMARY KEY (revision_repository, revision_build));
-CREATE INDEX revision_build_index ON build_revisions(revision_build);
-CREATE INDEX revision_repository_index ON build_revisions(revision_repository);
+CREATE TABLE commits (
+    commit_id serial PRIMARY KEY,
+    commit_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
+    commit_revision varchar(64) NOT NULL,
+    commit_parent integer REFERENCES commits ON DELETE CASCADE,
+    commit_time timestamp,
+    commit_author_name varchar(128),
+    commit_author_email varchar(320),
+    commit_message text,
+    commit_reported boolean NOT NULL DEFAULT FALSE,
+    CONSTRAINT commit_in_repository_must_be_unique UNIQUE(commit_repository, commit_revision));
+CREATE INDEX commit_time_index ON commits(commit_time);
+CREATE INDEX commit_author_name_index ON commits(commit_author_name);
+CREATE INDEX commit_author_email_index ON commits(commit_author_email);
+
+CREATE TABLE build_commits (
+    commit_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
+    build_commit integer NOT NULL REFERENCES commits ON DELETE CASCADE
+    PRIMARY KEY (commit_build, build_commit));
+CREATE INDEX build_commits_index ON build_commits(commit_build, build_commit);
 
 CREATE TABLE aggregators (
     aggregator_id serial PRIMARY KEY,
diff --git a/Websites/perf.webkit.org/public/api/commits.php b/Websites/perf.webkit.org/public/api/commits.php
new file mode 100644 (file)
index 0000000..83672af
--- /dev/null
@@ -0,0 +1,84 @@
+<?php
+
+require_once('../include/json-header.php');
+
+function main($paths) {
+    if (count($paths) < 1 || count($paths) > 2)
+        exit_with_error('InvalidRequest');
+
+    $db = new Database;
+    if (!$db->connect())
+        exit_with_error('DatabaseConnectionFailure');
+
+    $repository_name = $paths[0];
+    $repository_row = $db->select_first_row('repositories', 'repository', array('name' => $repository_name));
+    if (!$repository_row)
+        exit_with_error('RepositoryNotFound', array('repositoryName' => $repository_name));
+    $repository_id = $repository_row['repository_id'];
+
+    $filter = array_get($paths, 1);
+    $single_commit = NULL;
+    $commits = array();
+    if (!$filter) {
+        $commits = $db->fetch_table('commits', 'commit_time');
+    } else if ($filter == 'oldest') {
+        $single_commit = $db->select_first_row('commits', 'commit', array('repository' => $repository_id), 'time');
+    } else if ($filter == 'latest') {
+        $single_commit = $db->select_last_row('commits', 'commit', array('repository' => $repository_id), 'time');
+    } else if ($filter == 'last-reported') {
+        $single_commit = $db->select_last_row('commits', 'commit', array('repository' => $repository_id, 'reported' => true), 'time');
+    } else if (ctype_alnum($filter)) {
+        $single_commit = commit_from_revision($db, $repository_id, $repository_name, $filter);
+    } else {
+        $matches = array();
+        if (!preg_match('/([A-Za-z0-9]+)[\:\-]([A-Za-z0-9]+)/', $filter, $matches))
+            exit_with_error('UnknownFilter', array('repositoryName' => $repository_name, 'filter' => $filter));
+
+        $first = commit_from_revision($db, $repository_id, $matches[1])['commit_time'];
+        $second = commit_from_revision($db, $repository_id, $matches[2])['commit_time'];
+        $in_order = $first < $second;
+
+        $commits = fetch_commits_between($db, $repository_id, $in_order ? $first : $second, $in_order ? $second : $first);
+    }
+
+    exit_with_success(array('commits' => format_commits($single_commit ? array($single_commit) : $commits)));
+}
+
+function commit_from_revision($db, $repository_id, $revision) {
+    $all_but_first = substr($revision, 1);
+    if ($revision[0] == 'r' && ctype_digit($all_but_first))
+        $revision = $all_but_first;
+    $commit_info = array('repository' => $repository_id, 'revision' => $revision);
+    $row = $db->select_last_row('commits', 'commit', $commit_info);
+    if (!$row)
+        exit_with_error('UnknownCommit', $commit_info);
+    return $row;
+}
+
+function fetch_commits_between($db, $repository_id, $from, $to) {
+    $commits = $db->query_and_fetch_all('SELECT * FROM commits
+        WHERE commit_repository = $1 AND commit_time >= $2 AND commit_time <= $3 AND commit_reported = true ORDER BY commit_time',
+        array($repository_id, $from, $to));
+    if (!$commits)
+        exit_with_error('FailedToFetchCommits', array('repository' => $repository_id, 'from' => $from, 'to' => $to));
+    return $commits;
+}
+
+function format_commits($commits) {
+    $formatted_commits = array();
+    foreach ($commits as $commit_row) {
+        array_push($formatted_commits, array(
+            'id' => $commit_row['commit_id'],
+            'revision' => $commit_row['commit_revision'],
+            'parent' => $commit_row['commit_parent'],
+            'time' => $commit_row['commit_time'],
+            'author' => array('name' => $commit_row['commit_author_name'], 'email' => $commit_row['commit_author_email']),
+            'message' => $commit_row['commit_message']
+        ));
+    }
+    return $formatted_commits;
+}
+
+main(array_key_exists('PATH_INFO', $_SERVER) ? explode('/', trim($_SERVER['PATH_INFO'], '/')) : array());
+
+?>
diff --git a/Websites/perf.webkit.org/public/api/report-commits.php b/Websites/perf.webkit.org/public/api/report-commits.php
new file mode 100644 (file)
index 0000000..94a6109
--- /dev/null
@@ -0,0 +1,162 @@
+<?php
+
+require('../include/json-header.php');
+
+function main($post_data) {
+    $db = new Database;
+    if (!$db->connect())
+        exit_with_error('DatabaseConnectionFailure');
+
+    $report = json_decode($post_data, true);
+
+    verify_builder($db, $report);
+
+    $commits = array_get($report, 'commits', array());
+
+    foreach ($commits as $commit_info) {
+        if (!array_key_exists('repository', $commit_info))
+            exit_with_error('MissingRepositoryName', array('commit' => $commit_info));
+        if (!array_key_exists('revision', $commit_info))
+            exit_with_error('MissingRevision', array('commit' => $commit_info));
+        if (!ctype_alnum($commit_info['revision']))
+            exit_with_error('InvalidRevision', array('commit' => $commit_info));
+        if (!array_key_exists('time', $commit_info))
+            exit_with_error('MissingTimestamp', array('commit' => $commit_info));
+        if (!array_key_exists('author', $commit_info) || !is_array($commit_info['author']))
+            exit_with_error('MissingAuthorOrInvalidFormat', array('commit' => $commit_info));
+    }
+
+    $db->begin_transaction();
+    foreach ($commits as $commit_info) {
+        $repository_id = $db->select_or_insert_row('repositories', 'repository', array('name' => $commit_info['repository']));
+        if (!$repository_id) {
+            $db->rollback_transaction();
+            exit_with_error('FailedToInsertRepository', array('commit' => $commit_info));
+        }
+
+        $parent_revision = array_get($commit_info, 'parent');
+        $parent_id = NULL;
+        if ($parent_revision) {
+            $parent_commit = $db->select_first_row('commits', 'commit', array('repository' => $repository_id, 'revision' => $parent_revision));
+            if (!$parent_commit) {
+                $db->rollback_transaction();
+                exit_with_error('FailedToFindParentCommit', array('commit' => $commit_info));
+            }
+            $parent_id = $parent_commit['commit_id'];
+        }
+
+        $data = array(
+            'repository' => $repository_id,
+            'revision' => $commit_info['revision'],
+            'parent' => $parent_id,
+            'time' => $commit_info['time'],
+            'author_name' => array_get($commit_info['author'], 'name'),
+            'author_email' => array_get($commit_info['author'], 'email'),
+            'message' => $commit_info['message'],
+            'reported' => true,
+        );
+        $db->update_or_insert_row('commits', 'commit', array('repository' => $repository_id, 'revision' => $data['revision']), $data);
+    }
+    $db->commit_transaction();
+
+    exit_with_success();
+}
+
+function verify_builder($db, $report) {
+    array_key_exists('builderName', $report) or exit_with_error('MissingBuilderName');
+    array_key_exists('builderPassword', $report) or exit_with_error('MissingBuilderPassword');
+
+    $builder_info = array(
+        'name' => $report['builderName'],
+        'password_hash' => hash('sha256', $report['builderPassword'])
+    );
+
+    $matched_builder = $db->select_first_row('builders', 'builder', $builder_info);
+    if (!$matched_builder)
+        exit_with_error('BuilderNotFound', array('name' => $builder_info['name']));
+}
+
+main($HTTP_RAW_POST_DATA);
+
+?>
+<?php
+
+require('../include/json-header.php');
+
+function main($post_data) {
+    $db = new Database;
+    if (!$db->connect())
+        exit_with_error('DatabaseConnectionFailure');
+
+    $report = json_decode($post_data, true);
+
+    verify_builder($db, $report);
+
+    $commits = array_get($report, 'commits', array());
+
+    foreach ($commits as $commit_info) {
+        if (!array_key_exists('repository', $commit_info))
+            exit_with_error('MissingRepositoryName', array('commit' => $commit_info));
+        if (!array_key_exists('revision', $commit_info))
+            exit_with_error('MissingRevision', array('commit' => $commit_info));
+        if (!ctype_alnum($commit_info['revision']))
+            exit_with_error('InvalidRevision', array('commit' => $commit_info));
+        if (!array_key_exists('time', $commit_info))
+            exit_with_error('MissingTimestamp', array('commit' => $commit_info));
+        if (!array_key_exists('author', $commit_info) || !is_array($commit_info['author']))
+            exit_with_error('MissingAuthorOrInvalidFormat', array('commit' => $commit_info));
+    }
+
+    $db->begin_transaction();
+    foreach ($commits as $commit_info) {
+        $repository_id = $db->select_or_insert_row('repositories', 'repository', array('name' => $commit_info['repository']));
+        if (!$repository_id) {
+            $db->rollback_transaction();
+            exit_with_error('FailedToInsertRepository', array('commit' => $commit_info));
+        }
+
+        $parent_revision = array_get($commit_info, 'parent');
+        $parent_id = NULL;
+        if ($parent_revision) {
+            $parent_commit = $db->select_first_row('commits', 'commit', array('repository' => $repository_id, 'revision' => $parent_revision));
+            if (!$parent_commit) {
+                $db->rollback_transaction();
+                exit_with_error('FailedToFindParentCommit', array('commit' => $commit_info));
+            }
+            $parent_id = $parent_commit['commit_id'];
+        }
+
+        $data = array(
+            'repository' => $repository_id,
+            'revision' => $commit_info['revision'],
+            'parent' => $parent_id,
+            'time' => $commit_info['time'],
+            'author_name' => array_get($commit_info['author'], 'name'),
+            'author_email' => array_get($commit_info['author'], 'email'),
+            'message' => $commit_info['message'],
+            'reported' => true,
+        );
+        $db->update_or_insert_row('commits', 'commit', array('repository' => $repository_id, 'revision' => $data['revision']), $data);
+    }
+    $db->commit_transaction();
+
+    exit_with_success();
+}
+
+function verify_builder($db, $report) {
+    array_key_exists('builderName', $report) or exit_with_error('MissingBuilderName');
+    array_key_exists('builderPassword', $report) or exit_with_error('MissingBuilderPassword');
+
+    $builder_info = array(
+        'name' => $report['builderName'],
+        'password_hash' => hash('sha256', $report['builderPassword'])
+    );
+
+    $matched_builder = $db->select_first_row('builders', 'builder', $builder_info);
+    if (!$matched_builder)
+        exit_with_error('BuilderNotFound', array('name' => $builder_info['name']));
+}
+
+main($HTTP_RAW_POST_DATA);
+
+?>
index 7494dc4..d06a5fc 100644 (file)
@@ -30,12 +30,10 @@ if ($repository_table = $db->fetch_table('repositories')) {
 
 function fetch_runs_for_config($db, $config) {
     $raw_runs = $db->query_and_fetch_all('
-    SELECT test_runs.*, builds.*, array_agg((revision_repository, revision_value, revision_time)) AS revisions
-        FROM builds LEFT OUTER JOIN build_revisions ON revision_build = build_id, test_runs
-        WHERE run_build = build_id AND run_config = $1
-        GROUP BY build_id, build_builder, build_number, build_time, build_latest_revision,
-            run_id, run_config, run_build, run_iteration_count_cache,
-            run_mean_cache, run_sum_cache, run_square_sum_cache', array($config['config_id']));
+    SELECT test_runs.*, builds.*, array_agg((commit_repository, commit_revision, commit_time)) AS revisions
+        FROM builds LEFT OUTER JOIN build_commits ON commit_build = build_id, test_runs, commits
+        WHERE run_build = build_id AND run_config = $1 AND build_commit = commit_id
+        GROUP BY build_id, run_id', array($config['config_id']));
 
     $formatted_runs = array();
     if (!$raw_runs)
index adce27d..ed56506 100644 (file)
@@ -104,6 +104,14 @@ class Database
     }
 
     function select_or_insert_row($table, $prefix, $select_params, $insert_params = NULL, $returning = 'id') {
+        return $this->_select_update_or_insert_row($table, $prefix, $select_params, $insert_params, $returning, FALSE);
+    }
+
+    function update_or_insert_row($table, $prefix, $select_params, $insert_params = NULL, $returning = 'id') {
+        return $this->_select_update_or_insert_row($table, $prefix, $select_params, $insert_params, $returning, TRUE);
+    }
+
+    private function _select_update_or_insert_row($table, $prefix, $select_params, $insert_params, $returning, $should_update) {
         $values = array();
 
         $select_placeholders = array();
@@ -124,23 +132,42 @@ class Database
 
         $insert_column_names = $this->prefixed_column_names($insert_column_names, $prefix);
         $insert_placeholders = join(', ', $insert_placeholders);
-        $rows = $this->query_and_fetch_all("INSERT INTO $table ($insert_column_names) SELECT $insert_placeholders WHERE NOT EXISTS
-            ($query) RETURNING $returning_column_name", $values);
-        if (!$rows)
+
+        // http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql
+        $rows = NULL;
+        if ($should_update) {
+            $rows = $this->query_and_fetch_all("UPDATE $table SET ($insert_column_names) = ($insert_placeholders)
+                WHERE ($select_column_names) = ($select_placeholders) RETURNING $returning_column_name", $values);
+        }
+        if (!$rows) {
+            $rows = $this->query_and_fetch_all("INSERT INTO $table ($insert_column_names) SELECT $insert_placeholders
+                WHERE NOT EXISTS ($query) RETURNING $returning_column_name", $values);            
+        }
+        if (!$should_update && !$rows)
             $rows = $this->query_and_fetch_all($query, $select_values);
 
         return $rows ? ($returning == '*' ? $rows[0] : $rows[0][$returning_column_name]) : NULL;
     }
 
     function select_first_row($table, $prefix, $params, $order_by = NULL) {
+        return $this->select_first_or_last_row($table, $prefix, $params, $order_by, FALSE);
+    }
+
+    function select_last_row($table, $prefix, $params, $order_by = NULL) {
+        return $this->select_first_or_last_row($table, $prefix, $params, $order_by, TRUE);
+    }
+
+    private function select_first_or_last_row($table, $prefix, $params, $order_by, $descending_order) {
         $placeholders = array();
         $values = array();
         $column_names = $this->prefixed_column_names($this->prepare_params($params, $placeholders, $values), $prefix);
         $placeholders = join(', ', $placeholders);
         $query = "SELECT * FROM $table WHERE ($column_names) = ($placeholders)";
         if ($order_by) {
-            assert(!ctype_alnum_underscore($order_by));
+            assert(ctype_alnum_underscore($order_by));
             $query .= ' ORDER BY ' . $this->prefixed_name($order_by, $prefix);
+            if ($descending_order)
+                $query .= ' DESC';
         }
         $rows = $this->query_and_fetch_all($query . ' LIMIT 1', $values);
 
index 110d4e4..162b3a5 100644 (file)
@@ -98,13 +98,24 @@ class ReportProcessor {
             if (!$repository_id)
                 $this->exit_with_error('FailedToInsertRepository', array('name' => $repository_name));
 
-            $revision_data = array('repository' => $repository_id, 'build' => $build_id, 'value' => $revision_data['revision'],
-                'time' => array_get($revision_data, 'timestamp'));
-            $revision_row = $this->db->select_or_insert_row('build_revisions', 'revision', array('repository' => $repository_id, 'build' => $build_id), $revision_data, '*');
-            if (!$revision_row)
-                $this->exit_with_error('FailedToInsertRevision', $revision_data);
-            if ($revision_row['revision_value'] != $revision_data['value'])
-                $this->exit_with_error('MismatchingRevisionData', array('existing' => $revision_row, 'new' => $revision_data));
+            $commit_data = array('repository' => $repository_id, 'revision' => $revision_data['revision'], 'time' => array_get($revision_data, 'timestamp'));
+
+            $mismatching_commit = $this->db->query_and_fetch_all('SELECT * FROM build_commits, commits
+                WHERE build_commit = commit_id AND commit_build = $1 AND commit_repository = $2 AND commit_revision != $3 LIMIT 1',
+                array($build_id, $repository_id, $revision_data['revision']));
+            if ($mismatching_commit)
+                $this->exit_with_error('MismatchingCommitRevision', array('build' => $build_id, 'existing' => $mismatching_commit, 'new' => $commit_data));
+
+            $commit_row = $this->db->select_or_insert_row('commits', 'commit',
+                array('repository' => $repository_id, 'revision' => $revision_data['revision']), $commit_data, '*');
+            if (!$commit_row)
+                $this->exit_with_error('FailedToRecordCommit', $commit_data);
+            if (abs($commit_row['commit_time'] - $commit_data['time']) > 1.0)
+                $this->exit_with_error('MismatchingCommitTime', array('existing' => $commit_row, 'new' => $commit_data));
+
+            if (!$this->db->insert_row('build_commits', null,
+                array('commit_build' => $build_id, 'build_commit' => $commit_row['commit_id']), null))
+                $this->exit_with_error('FailedToRelateCommitToBuild', array('commit' => $commit_row, 'build' => $build_id));
         }
 
         return $build_id;
index 7b3c393..f71c419 100644 (file)
@@ -30,7 +30,7 @@ function pathToDatabseSQL(relativePath) {
 }
 
 function pathToTests(testName) {
-    return path.resolve(__dirname, 'tests', testName);
+    return testName ? path.resolve(__dirname, 'tests', testName) : path.resolve(__dirname, 'tests');
 }
 
 var configurationJSON = require('./config.json');
diff --git a/Websites/perf.webkit.org/tests/api-report-commits.js b/Websites/perf.webkit.org/tests/api-report-commits.js
new file mode 100644 (file)
index 0000000..b82a845
--- /dev/null
@@ -0,0 +1,219 @@
+describe("/api/report-commits/", function () {
+    var emptyReport = {
+        "builderName": "someBuilder",
+        "builderPassword": "somePassword",
+    };
+    var subversionCommit = {
+        "builderName": "someBuilder",
+        "builderPassword": "somePassword",
+        "commits": [
+            {
+                "repository": "WebKit",
+                "revision": "141977",
+                "time": "2013-02-06T08:55:20.9Z",
+                "author": {"name": "Commit Queue", "email": "commit-queue@webkit.org"},
+                "message": "some message",
+            }
+        ],
+    };
+    var subversionInvalidCommit = {
+        "builderName": "someBuilder",
+        "builderPassword": "somePassword",
+        "commits": [
+            {
+                "repository": "WebKit",
+                "revision": "_141977",
+                "time": "2013-02-06T08:55:20.9Z",
+                "author": {"name": "Commit Queue", "email": "commit-queue@webkit.org"},
+                "message": "some message",
+            }
+        ],
+    };
+    var subversionTwoCommits = {
+        "builderName": "someBuilder",
+        "builderPassword": "somePassword",
+        "commits": [
+            {
+                "repository": "WebKit",
+                "revision": "141977",
+                "time": "2013-02-06T08:55:20.9Z",
+                "author": {"name": "Commit Queue", "email": "commit-queue@webkit.org"},
+                "message": "some message",
+            },
+            {
+                "repository": "WebKit",
+                "parent": "141977",
+                "revision": "141978",
+                "time": "2013-02-06T09:54:56.0Z",
+                "author": {"name": "Mikhail Pozdnyakov", "email": "mikhail.pozdnyakov@intel.com"},
+                "message": "another message",
+            }
+        ]
+    }
+
+    function addBuilder(report, callback) {
+        queryAndFetchAll('INSERT INTO builders (builder_name, builder_password_hash) values ($1, $2)',
+            [report.builderName, sha256(report.builderPassword)], callback);
+    }
+
+    it("should reject error when builder name is missing", function () {
+        postJSON('/api/report-commits/', {}, function (response) {
+            assert.equal(response.statusCode, 200);
+            assert.equal(JSON.parse(response.responseText)['status'], 'MissingBuilderName');
+            notifyDone();
+        });
+    });
+
+    it("should reject when there are no builders", function () {
+        postJSON('/api/report-commits/', emptyReport, function (response) {
+            assert.equal(response.statusCode, 200);
+            assert.notEqual(JSON.parse(response.responseText)['status'], 'OK');
+
+            queryAndFetchAll('SELECT COUNT(*) from commits', [], function (rows) {
+                assert.equal(rows[0].count, 0);
+                notifyDone();
+            });
+        });
+    });
+
+    it("should accept an empty report", function () {
+        addBuilder(emptyReport, function () {
+            postJSON('/api/report-commits/', emptyReport, function (response) {
+                assert.equal(response.statusCode, 200);
+                assert.equal(JSON.parse(response.responseText)['status'], 'OK');
+                notifyDone();
+            });
+        });
+    });
+
+    it("should add a missing repository", function () {
+        addBuilder(subversionCommit, function () {
+            postJSON('/api/report-commits/', subversionCommit, function (response) {
+                assert.equal(response.statusCode, 200);
+                assert.equal(JSON.parse(response.responseText)['status'], 'OK');
+                queryAndFetchAll('SELECT * FROM repositories', [], function (rows) {
+                    assert.equal(rows.length, 1);
+                    assert.equal(rows[0]['repository_name'], subversionCommit.commits[0]['repository']);
+                    notifyDone();
+                });
+            });
+        });
+    });
+
+    it("should store a commit from a valid builder", function () {
+        addBuilder(subversionCommit, function () {
+            postJSON('/api/report-commits/', subversionCommit, function (response) {
+                assert.equal(response.statusCode, 200);
+                assert.equal(JSON.parse(response.responseText)['status'], 'OK');
+                queryAndFetchAll('SELECT * FROM commits', [], function (rows) {
+                    assert.equal(rows.length, 1);
+                    var reportedData = subversionCommit.commits[0];
+                    assert.equal(rows[0]['commit_revision'], reportedData['revision']);
+                    assert.equal(rows[0]['commit_time'].toString(), new Date('2013-02-06 08:55:20.9').toString());
+                    assert.equal(rows[0]['commit_author_name'], reportedData['author']['name']);
+                    assert.equal(rows[0]['commit_author_email'], reportedData['author']['email']);
+                    assert.equal(rows[0]['commit_message'], reportedData['message']);
+                    notifyDone();
+                });
+            });
+        });
+    });
+
+    it("should reject an invalid revision number", function () {
+        addBuilder(subversionCommit, function () {
+            subversionCommit
+            postJSON('/api/report-commits/', subversionInvalidCommit, function (response) {
+                assert.equal(response.statusCode, 200);
+                assert.notEqual(JSON.parse(response.responseText)['status'], 'OK');
+                queryAndFetchAll('SELECT * FROM commits', [], function (rows) {
+                    assert.equal(rows.length, 0);
+                    notifyDone();
+                });
+            });
+        });
+    });
+
+    it("should store two commits from a valid builder", function () {
+        addBuilder(subversionTwoCommits, function () {
+            postJSON('/api/report-commits/', subversionTwoCommits, function (response) {
+                assert.equal(response.statusCode, 200);
+                assert.equal(JSON.parse(response.responseText)['status'], 'OK');
+                queryAndFetchAll('SELECT * FROM commits ORDER BY commit_time', [], function (rows) {
+                    assert.equal(rows.length, 2);
+                    var reportedData = subversionTwoCommits.commits[0];
+                    assert.equal(rows[0]['commit_revision'], reportedData['revision']);
+                    assert.equal(rows[0]['commit_time'].toString(), new Date('2013-02-06 08:55:20.9').toString());
+                    assert.equal(rows[0]['commit_author_name'], reportedData['author']['name']);
+                    assert.equal(rows[0]['commit_author_email'], reportedData['author']['email']);
+                    assert.equal(rows[0]['commit_message'], reportedData['message']);
+                    var reportedData = subversionTwoCommits.commits[1];
+                    assert.equal(rows[1]['commit_revision'], reportedData['revision']);
+                    assert.equal(rows[1]['commit_time'].toString(), new Date('2013-02-06 09:54:56.0').toString());
+                    assert.equal(rows[1]['commit_author_name'], reportedData['author']['name']);
+                    assert.equal(rows[1]['commit_author_email'], reportedData['author']['email']);
+                    assert.equal(rows[1]['commit_message'], reportedData['message']);
+                    notifyDone();
+                });
+            });
+        });
+    });
+
+    it("should update an existing commit if there is one", function () {
+        queryAndFetchAll('INSERT INTO repositories (repository_name) VALUES ($1) RETURNING *', ['WebKit'], function (repositories) {
+            var repositoryId = repositories[0]['repository_id'];
+            var reportedData = subversionCommit.commits[0];
+            queryAndFetchAll('INSERT INTO commits (commit_repository, commit_revision, commit_time) VALUES ($1, $2, $3) RETURNING *',
+                [repositoryId, reportedData['revision'], reportedData['time']], function (existingCommits) {
+                var commitId = existingCommits[0]['commit_id'];
+                assert.equal(existingCommits[0]['commit_author_name'], null);
+                assert.equal(existingCommits[0]['commit_author_email'], null);
+                assert.equal(existingCommits[0]['commit_message'], null);
+                addBuilder(subversionCommit, function () {
+                    postJSON('/api/report-commits/', subversionCommit, function (response) {
+                        assert.equal(response.statusCode, 200);
+                        assert.equal(JSON.parse(response.responseText)['status'], 'OK');
+                        queryAndFetchAll('SELECT * FROM commits', [], function (rows) {
+                            assert.equal(rows.length, 1);
+                            var reportedData = subversionCommit.commits[0];
+                            assert.equal(rows[0]['commit_author_name'], reportedData['author']['name']);
+                            assert.equal(rows[0]['commit_author_email'], reportedData['author']['email']);
+                            assert.equal(rows[0]['commit_message'], reportedData['message']);
+                            notifyDone();
+                        });
+                    });
+                });
+            });
+        });
+    });
+
+    it("should not update an unrelated commit", function () {
+        queryAndFetchAll('INSERT INTO repositories (repository_name) VALUES ($1) RETURNING *', ['WebKit'], function (repositories) {
+            var repositoryId = repositories[0]['repository_id'];
+            var reportedData = subversionTwoCommits.commits[1];
+            queryAndFetchAll('INSERT INTO commits (commit_repository, commit_revision, commit_time) VALUES ($1, $2, $3) RETURNING *',
+                [repositoryId, reportedData['revision'], reportedData['time']], function (existingCommits) {
+                reportedData = subversionTwoCommits.commits[0];
+                queryAndFetchAll('INSERT INTO commits (commit_repository, commit_revision, commit_time) VALUES ($1, $2, $3) RETURNING *',
+                    [repositoryId, reportedData['revision'], reportedData['time']], function () {
+                        addBuilder(subversionCommit, function () {
+                            postJSON('/api/report-commits/', subversionCommit, function (response) {
+                                assert.equal(response.statusCode, 200);
+                                assert.equal(JSON.parse(response.responseText)['status'], 'OK');
+                                queryAndFetchAll('SELECT * FROM commits ORDER BY commit_time', [], function (rows) {
+                                    assert.equal(rows.length, 2);
+                                    assert.equal(rows[0]['commit_author_name'], reportedData['author']['name']);
+                                    assert.equal(rows[0]['commit_author_email'], reportedData['author']['email']);
+                                    assert.equal(rows[0]['commit_message'], reportedData['message']);
+                                    assert.equal(rows[1]['commit_author_name'], null);
+                                    assert.equal(rows[1]['commit_author_email'], null);
+                                    assert.equal(rows[1]['commit_message'], null);
+                                    notifyDone();
+                                });
+                            });
+                        });
+                });
+            });
+        });
+    });
+
+});
index a5795b4..367a1d8 100644 (file)
@@ -111,14 +111,14 @@ describe("/api/report", function () {
 
                     var repositoryIdToName = {};
                     rows.forEach(function (row) { repositoryIdToName[row['repository_id']] = row['repository_name']; });
-                    queryAndFetchAll('SELECT * FROM build_revisions', [], function (rows) {
+                    queryAndFetchAll('SELECT * FROM build_commits, commits WHERE build_commit = commit_id', [], function (rows) {
                         var repositoryNameToRevisionRow = {};
                         rows.forEach(function (row) {
-                            repositoryNameToRevisionRow[repositoryIdToName[row['revision_repository']]] = row;
+                            repositoryNameToRevisionRow[repositoryIdToName[row['commit_repository']]] = row;
                         });
-                        assert.equal(repositoryNameToRevisionRow['OS X']['revision_value'], '10.8.2 12C60');
-                        assert.equal(repositoryNameToRevisionRow['WebKit']['revision_value'], '141977');
-                        assert.equal(repositoryNameToRevisionRow['WebKit']['revision_time'].toString(),
+                        assert.equal(repositoryNameToRevisionRow['OS X']['commit_revision'], '10.8.2 12C60');
+                        assert.equal(repositoryNameToRevisionRow['WebKit']['commit_revision'], '141977');
+                        assert.equal(repositoryNameToRevisionRow['WebKit']['commit_time'].toString(),
                             new Date('2013-02-06 08:55:20.9').toString());
                         notifyDone();
                     });
diff --git a/Websites/perf.webkit.org/tools/pull-svn.py b/Websites/perf.webkit.org/tools/pull-svn.py
new file mode 100644 (file)
index 0000000..dcb510c
--- /dev/null
@@ -0,0 +1,163 @@
+#!/usr/bin/python
+
+import json
+import re
+import subprocess
+import sys
+import time
+import urllib2
+
+from xml.dom.minidom import parseString as parseXmlString
+
+
+def main(argv):
+    if len(argv) < 7:
+        sys.exit('Usage: pull-svn <repository-name> <repository-URL> <dashboard-URL> <builder-name> <builder-password> <seconds-to-sleep> [<email-to-name-helper>]')
+
+    repository_name = argv[1]
+    repository_url = argv[2]
+    dashboard_url = argv[3]
+    builder_name = argv[4]
+    builder_password = argv[5]
+    seconds_to_sleep = float(argv[6])
+    email_to_name_helper = argv[7] if len(argv) > 7 else None
+
+    print "Submitting revision logs for %s at %s to %s" % (repository_name, repository_url, dashboard_url)
+
+    revision_to_fetch = determine_first_revision_to_fetch(dashboard_url, repository_name)
+    print "Start fetching commits at r%d" % revision_to_fetch
+
+    pending_commits_to_send = []
+
+    while True:
+        commit = fetch_commit_and_resolve_author(repository_name, repository_url, email_to_name_helper, revision_to_fetch)
+
+        if commit:
+            print "Fetched r%d." % revision_to_fetch
+            pending_commits_to_send += [commit]
+            revision_to_fetch += 1
+        else:
+            print "Revision %d not found" % revision_to_fetch
+
+        if not commit or len(pending_commits_to_send) >= 10:
+            if pending_commits_to_send:
+                print "Submitting the above commits to %s..." % dashboard_url
+                submit_commits(pending_commits_to_send, dashboard_url, builder_name, builder_password)
+                print "Successfully submitted."
+            pending_commits_to_send = []
+            time.sleep(seconds_to_sleep)
+
+
+def determine_first_revision_to_fetch(dashboard_url, repository_name):
+    try:
+        last_reported_revision = fetch_revision_from_dasbhoard(dashboard_url, repository_name, 'last-reported')
+    except Exception as error:
+        sys.exit('Failed to fetch the latest reported commit: ' + str(error))
+
+    if last_reported_revision:
+        return last_reported_revision + 1
+
+    # FIXME: This is a problematic if dashboard can get results for revisions older than oldest_revision
+    # in the future because we never refetch older revisions.
+    try:
+        return fetch_revision_from_dasbhoard(dashboard_url, repository_name, 'oldest') or 1
+    except Exception as error:
+        sys.exit('Failed to fetch the oldest commit: ' + str(error))
+
+
+def fetch_revision_from_dasbhoard(dashboard_url, repository_name, filter):
+    result = urllib2.urlopen(dashboard_url + '/api/commits/' + repository_name + '/' + filter).read()
+    parsed_result = json.loads(result)
+    if parsed_result['status'] != 'OK' and parsed_result['status'] != 'RepositoryNotFound':
+        raise Exception(result)
+    commits = parsed_result.get('commits')
+    return int(commits[0]['revision']) if commits else None
+
+
+def fetch_commit_and_resolve_author(repository_name, repository_url, email_to_name_helper, revision_to_fetch):
+    try:
+        commit = fetch_commit(repository_name, repository_url, revision_to_fetch)
+    except Exception as error:
+        sys.exit('Failed to fetch the commit %d: %s' % (revision_to_fetch, str(error)))
+
+    if not commit:
+        return None
+
+    email = commit['author']['email']
+    try:
+        name = resolve_author_name_from_email(email_to_name_helper, email) if email_to_name_helper else None
+        if name:
+            commit['author']['name'] = name
+    except Exception as error:
+        sys.exit('Failed to resolve the author name from an email %s: %s' % (email, str(error)))
+
+    return commit
+
+
+def fetch_commit(repository_name, repository_url, revision):
+    args = ['svn', 'log', '--revision', str(revision), '--xml', repository_url]
+    try:
+        output = subprocess.check_output(args, stderr=subprocess.STDOUT)
+    except subprocess.CalledProcessError as error:
+        if (': No such revision ' + str(revision)) in error.output:
+            return None
+        raise error
+    xml = parseXmlString(output)
+    time = textContent(xml.getElementsByTagName("date")[0])
+    author_email = textContent(xml.getElementsByTagName("author")[0])
+    message = textContent(xml.getElementsByTagName("msg")[0])
+    return {
+        'repository': repository_name,
+        'revision': revision,
+        'time': time,
+        'author': {'email': author_email},
+        'message': message,
+    }
+
+
+def textContent(element):
+    text = ''
+    for child in element.childNodes:
+        if child.nodeType == child.TEXT_NODE:
+            text += child.data
+        else:
+            text += textContent(child)
+    return text
+
+
+name_email_compound_regex = re.compile(r'^\s*(?P<name>(\".+\"|[^<]+?))\s*\<(?P<email>.+)\>\s*$')
+
+
+def resolve_author_name_from_email(helper, email):
+    output = subprocess.check_output(helper + ' ' + email, shell=True)
+    match = name_email_compound_regex.match(output)
+    if match:
+        return match.group('name').strip('"')
+    return output.strip()
+
+
+def submit_commits(commits, dashboard_url, builder_name, builder_password):
+    try:
+        payload = json.dumps({
+            'builderName': builder_name,
+            'builderPassword': builder_password,
+            'commits': commits,
+        })
+        request = urllib2.Request(dashboard_url + '/api/report-commits')
+        request.add_header('Content-Type', 'application/json')
+        request.add_header('Content-Length', len(payload))
+
+        output = urllib2.urlopen(request, payload).read()
+        try:
+            result = json.loads(output)
+        except Exception, error:
+            raise Exception(error, output)
+
+        if result.get('status') != 'OK':
+            raise Exception(result)
+    except Exception as error:
+        sys.exit('Failed to submit commits: %s' % str(error))
+
+
+if __name__ == "__main__":
+    main(sys.argv)