[Buildroot] [PATCH buildroot-test 0/4] allow results to be filtered by symbols

[Buildroot] [PATCH buildroot-test 0/4] allow results to be filtered by symbols

[Victor Huesca]

The following patch provides a better support for interaction with the autobuild
database.

The patch is composed of 4 commits that add the following changes:
 - Update internal sql query (func.inc.php) to allow requests on symbols.
 - Update the index.php page to parse read symbols from GET with the syntax 
   `symbols[BR2_INIT_SYSTEMD]=y`
 - Update the database schema by adding indexes on the two columns of  
   `symbol_per_result` to improve queries that involve specific symbols.
   Also mark these columns as foreign keys to ensure integrity of the database.
 - Add a new page which consist a basic form to ease searching in the database.

This patch is a v1 and there are probably many issue to fix, so any suggestion
concerning functionnalities/coding style are welcome -- especially as it is an
ugly php script and an even more ugly javascript.


Victor Huesca (4):
  web/{funcs,db}.inc.php: add support for symbols in sql query
  web/index.php: add support for symbols to be passed via GET
  web/schema.sql: add indexes on the database schema
  web/request.{js,php} and web/stylesheet.css: new page to ease seaching
    for specific results

 web/db.inc.php     | 28 ++++++++++++++--
 web/funcs.inc.php  | 48 ++++++++++++++++++++++------
 web/index.php      | 25 ++++++++++++---
 web/request.js     | 47 +++++++++++++++++++++++++++
 web/request.php    | 67 +++++++++++++++++++++++++++++++++++++++
 web/schema.sql     |  7 +++-
 web/stylesheet.css | 79 ++++++++++++++++++++++++++++++++++++++++++++++
 7 files changed, 284 insertions(+), 17 deletions(-)
 create mode 100644 web/request.js
 create mode 100644 web/request.php

-- 
2.21.0

[Buildroot] [PATCH buildroot-test 1/4] web/{funcs, db}.inc.php: add support for symbols in sql query

[Victor Huesca]

This patch provide a way to filter results from database with a list of symbols.

This query has been optimized to scale the best it can when multiple symbols are
asked together. Actually I found 3 way to get the same results: `join`, `where in`
and `intersect`.
- The 1st one is really not usable in practice since it only returns results if
the symbols subquery return only a douzen of rows and there no more than 2 or 3
symbols asked at the same time.
- The 2nd can handle queries where thousand rows are involved -- which is still
less than common cases -- but on a very limited number of symbols too. We could
have hope a better result but it is still a way better than join.
- The last is the one realy want to use. It can handle any number of symbols
with any number of rows and return a result in a few seconds.

Unfortunalty this query make use of `intersect` which is not implemented in
mysql. However mariaDB implemented this feature in 2017 w/ the version 10.3.10
but our current databse is an oracle mysql not a mariaDB :(
We planned to move the database to an other server but since it is an stable
debian, the mariadb verison is too old to support select.
I implemented a dynamic check of mysql version. The type of query use to
handle symbols read this version and use `intersect` in case when the
version supports it.

TDRL; The select on symbols works but is not optimal yet. It will be optimal
as soon as the database support `intersect` without changing the php code.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/db.inc.php    | 28 +++++++++++++++++++++++++--
 web/funcs.inc.php | 48 +++++++++++++++++++++++++++++++++++++----------
 2 files changed, 64 insertions(+), 12 deletions(-)

diff --git a/web/db.inc.php b/web/db.inc.php
index 99f83a2..f8b10a2 100644
--- a/web/db.inc.php
+++ b/web/db.inc.php
@@ -54,6 +54,30 @@ class db
 
     return $value;
   }
-}
 
-?>
\ No newline at end of file
+
+  // Test whereas the database the support a given feature
+  function has_feature($feature)
+  {
+    // Return -1 on v1 < v2, 0 on v1 = v2 and 1 on v1 > v2
+    $compare_versions = function($v1, $v2) {
+      for ($i = 0; $i < min(sizeof($v1), sizeof($v2)); $i++)
+        if ($v1[$i] != $v2[$i])
+          return $v1[$i] - $v2[$i];
+      return 0;
+    };
+
+    switch ($feature) {
+      case 'intersect': // SELECT was introduced in mariadb version 10.3.10
+        $res = $this->query("select version() version;");
+        $ver = mysqli_fetch_object($res)->version;
+        preg_match("/^(\d+(?:\.\d+)*)-.+$/", $ver, $match);
+        $version = array_map(function ($v) { return (int)$v; }, explode('.', $match[1]));
+        return $compare_versions($version, array(10, 3, 10)) >= 0;
+
+      default:
+        throw new Exception("Unknown feature", 1);
+    }
+  }
+}
+?>
diff --git a/web/funcs.inc.php b/web/funcs.inc.php
index 7e912c1..f1f74dc 100644
--- a/web/funcs.inc.php
+++ b/web/funcs.inc.php
@@ -1,4 +1,5 @@
 <?php
+set_time_limit(0);
 include(dirname(__FILE__) . "/../web/config.inc.php");
 include(dirname(__FILE__) . "/../web/db.inc.php");
 
@@ -30,6 +31,24 @@ function bab_footer()
   echo "</html>\n";
 }
 
+function bab_format_sql_symbols($db, $symbols)
+{
+  $get_res_id = "select result_id id from symbol_per_result where symbol_id = (select id from config_symbol where name=%s and value=%s)";
+
+  $r = array_map(
+    function($name, $value) use ($db, $get_res_id) {
+      return sprintf($get_res_id, $db->quote_smart($name), $db->quote_smart($value));
+    },
+    array_keys($symbols),
+    $symbols
+  );
+
+  if ($db->has_feature('intersect'))
+    return implode(" intersect ", $r);
+  else
+    return implode(" and result_id in (", $r) . str_repeat(")", count($symbols)-1);
+}
+
 function bab_format_sql_filter($db, $filters)
 {
 	$status_map = array(
@@ -44,27 +63,35 @@ function bab_format_sql_filter($db, $filters)
 				return sprintf("%s like %s", $k, $db->quote_smart($v));
 			else if ($k == "status")
 				return sprintf("%s=%s", $k, $db->quote_smart($status_map[$v]));
-			else
+      elseif ($k == "date")
+        if (is_array($v)) {
+          if (isset($v['from'], $v['to']))
+            return sprintf("builddate between %s and %s", $db->quote_smart($v['from']), $db->quote_smart($v['to']));
+          else if (isset($v['to']))
+            return sprintf("builddate<=%s", $db->quote_smart($v['to']));
+          else
+            return sprintf("builddate>=%s", $db->quote_smart($v['from']));
+        } else // Assuming the date is a lower-bound
+          return sprintf("builddate>=%s", $db->quote_smart($v));
+      else
 				return sprintf("%s=%s", $k, $db->quote_smart($v));
 		},
 		$filters,
 		array_keys($filters)
 	));
 
-	if (count($filters))
-		return "where " . $sql_filters;
-	else
-		return "";
+  return (count($filters) ? "where " . $sql_filters : "");
 }
 
 /*
  * Returns the total number of results.
  */
-function bab_total_results_count($filters)
+function bab_total_results_count($filters, $symbols)
 {
   $db = new db();
   $condition = bab_format_sql_filter($db, $filters);
-  $sql = "select count(*) from results $condition;";
+  $symbols_condition = bad_format_sql_symbols($db, $symbols);
+  $sql = "select count(*) from results ".(count($symbols) > 0 ? "inner join ($symbols_condition) SYMBOLS using (id)" : "")."$condition;";
   $ret = $db->query($sql);
   if ($ret == FALSE) {
     echo "Something's wrong in here\n";
@@ -80,13 +107,14 @@ function bab_total_results_count($filters)
  * and limited to $count items. The items starting with $start=0 are
  * the most recent build results.
  */
-function bab_get_results($start=0, $count=100, $filters = array())
+function bab_get_results($start=0, $count=100, $filters = array(), $symbols = array())
 {
   global $status_map;
   $db = new db();
-
+  $symbols_condition = bab_format_sql_symbols($db, $symbols);
   $condition = bab_format_sql_filter($db, $filters);
-  $sql = "select * from results $condition order by builddate desc limit $start, $count;";
+  $sql = "select * from results ".(count($symbols) > 0 ? "inner join ($symbols_condition) SYMBOLS using (id)" : "")."$condition order by builddate desc limit $start, $count;";
+
   $ret = $db->query($sql);
   if ($ret == FALSE) {
     echo "Something's wrong with the SQL query\n";
-- 
2.21.0

[Buildroot] [PATCH buildroot-test 2/4] web/index.php: add support for symbols to be passed via GET

[Victor Huesca]

This patch add support of a `symbols[<symbol>]=<value>` option via GET as it is
done with other fields.

The syntax used is `symbols[<symbol>]=<value>`, so multiple symbols can be
passed while the url is still readable and symbols are clearly isolated from
other fields.
These symbols are forwared to the backend to be integrated in the sql query.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/index.php | 25 +++++++++++++++++++++----
 1 file changed, 21 insertions(+), 4 deletions(-)

diff --git a/web/index.php b/web/index.php
index f3af62d..289a866 100644
--- a/web/index.php
+++ b/web/index.php
@@ -34,7 +34,18 @@ function format_url_args($args)
 	));
 }
 
+function setup_date($dates)
+{
+  if (isset($dates['from']) && isset($dates['to']))
+    return $dates;
+  else if (isset($dates['from']))
+    return $dates['from'];
+  else if (isset($dates['to']))
+    return array('to' => $dates['to']);
+}
+
 $filters = array();
+$symbols = array();
 
 /* When no start is given, or start is a crazy value (not an integer),
    just default to start=0 */
@@ -53,7 +64,7 @@ if ($step > 250)
 
 $valid_status = array("OK", "NOK", "TIMEOUT");
 
-if (isset ($_GET['status']) && in_array($_GET['status'], $valid_status))
+if (isset($_GET['status']) && in_array($_GET['status'], $valid_status))
   $filters["status"] = $_GET['status'];
 
 if (isset($_GET['arch']) && preg_match("/^[a-z0-9_]*$/", $_GET['arch']))
@@ -74,9 +85,15 @@ if (isset($_GET['static']) && preg_match("/^[0-1]$/", $_GET['static']))
 if (isset($_GET['subarch']) && preg_match("/^[A-Za-z0-9_\+\.\-]*$/", $_GET['subarch']))
   $filters["subarch"] = $_GET['subarch'];
 
-if (isset ($_GET['submitter']))
+if (isset($_GET['submitter']))
   $filters["submitter"] = urldecode($_GET['submitter']);
 
+if (isset($_GET['symbols']) && is_array($_GET['symbols']))
+  $symbols = $_GET['symbols'];
+
+if (isset($_GET['date']))
+  $filters["date"] = setup_date($_GET['date']);
+
 bab_header("Buildroot tests");
 
 echo "<table>\n";
@@ -85,7 +102,7 @@ echo "<tr class=\"header\">";
 echo "<td>Date</td><td>Duration</td><td>Status</td><td>Commit ID</td><td>Submitter</td><td>Arch/Subarch</td><td>Failure reason</td><td>Libc</td><td>Static?</td><td>Data</td>";
 echo "</tr>";
 
-$results = bab_get_results($start, $step, $filters);
+$results = bab_get_results($start, $step, $filters, $symbols);
 
 while ($current = mysqli_fetch_object($results)) {
 
@@ -156,7 +173,7 @@ echo "</table>\n";
 
 echo "<p style=\"text-align: center;\">";
 
-$total = bab_total_results_count($filters);
+$total = bab_total_results_count($filters, $symbols);
 
 $prev_args = $filters;
 $next_args = $filters;
-- 
2.21.0

[Buildroot] [PATCH buildroot-test 3/4] web/schema.sql: add indexes on the database schema

[Victor Huesca]

Add indexes on the two columns of `symbol_per_result` to improve significantly
queries involving symbols (this is an over 80M rows table).
This table is heavily used to retrieve results matching a symbols and it is not
an option filter symbols without these indexes.

Also mark these same columns as foreign keys to ensure integrity of the
database.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/schema.sql | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)

diff --git a/web/schema.sql b/web/schema.sql
index 65fd883..8b854a2 100644
--- a/web/schema.sql
+++ b/web/schema.sql
@@ -27,5 +27,10 @@ CREATE TABLE `symbol_per_result` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `result_id` int(11) NOT NULL DEFAULT '0',
   `symbol_id` int(11) NOT NULL DEFAULT '0',
-  PRIMARY KEY (`id`)
+  PRIMARY KEY (`id`),
+  CONSTRAINT `fk_result_id` FOREIGN KEY (`result_id`) REFERENCES `results`(`id`),
+  CONSTRAINT `fk_symbol_id` FOREIGN KEY (`symbol_id`) REFERENCES `config_symbol`(`id`),
+  INDEX `ix_symbol_id`(`symbol_id`),
+  INDEX `ix_result_id`(`result_id`)
+
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 
2.21.0

[Buildroot] [PATCH buildroot-test 4/4] web/request.{js, php} and web/stylesheet.css: new page to ease seaching for specific results

[Victor Huesca]

Add form page to allow a more advanced and convenient way to search for specific configs.

Currently results are filtered by clicking on a symbol value in the autobuild
results page. With this method, we are limited to one field only (eg. submitter,
reason, etc). Any additional field has to be manually provided in the url.
While this method is great for automation, it is not intuitive nor efficient
for a human especially when it comes to filter by symbols since it it now
possible.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/request.js     | 47 +++++++++++++++++++++++++++
 web/request.php    | 67 +++++++++++++++++++++++++++++++++++++++
 web/stylesheet.css | 79 ++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 193 insertions(+)
 create mode 100644 web/request.js
 create mode 100644 web/request.php

diff --git a/web/request.js b/web/request.js
new file mode 100644
index 0000000..1d348bb
--- /dev/null
+++ b/web/request.js
@@ -0,0 +1,47 @@
+// Avoid empty input to be send by the form (this is clearer
+// and simplify treatment on server)
+function disableEmpties(node) {
+    if (node == undefined) {
+        node = document.getElementById("noblank");
+        prepareSymbols();
+    }
+    if (node.hasChildNodes() && node.tagName != "TEXTAREA" && node.tagName != "SELECT")
+        for(const child of node.children)
+            arguments.callee(child);
+    else if (node.value == "")
+        node.disabled = true;
+}
+
+// Add hidden inputs according to the symbols textarea
+function prepareSymbols() {
+    const container = document.getElementById("symbols-holder");
+    const symbols = document.getElementById("symbols");
+    const arr = symbols.value.trim().split("\n").filter(function (s) {
+        return s.trim() != '';
+    });
+
+    while (container.hasChildNodes())
+        container.removeChild(container.lastChild);
+
+    for (const line of arr) {
+        const [sym, ...rest] = line.split("=");
+        const input = document.createElement("input");
+        input.type = "hidden";
+        input.name = `symbols[${sym.trim()}]`;
+        input.value = rest.join("=").trim();
+        container.appendChild(input);
+    }
+    symbols.disabled = true;
+}
+
+// Re-enable inputs (in case of navigation via cache eg. w/
+// the previous button)
+window.onpageshow = function(node) {
+    if (node.tagName == undefined)
+        node = document.getElementById("noblank");
+    if (node.hasChildNodes() && node.tagName != "TEXTAREA" && node.tagName != "SELECT")
+        for(const child of node.children)
+            arguments.callee(child);
+    else
+        node.disabled = false;
+};
diff --git a/web/request.php b/web/request.php
new file mode 100644
index 0000000..da26491
--- /dev/null
+++ b/web/request.php
@@ -0,0 +1,67 @@
+<?php
+include("funcs.inc.php");
+
+bab_header("Buildroot tests - Request page");
+?>
+
+<script type='text/javascript' src="request.js"></script>
+
+<div class="form-class">
+    <form action="index.php" method="get" class="request-form" id="requester" onsubmit="disableEmpties() && prepareSymbols()">
+        <div id="noblank">
+            <label>Submitter:
+                <input type="text" name="submitter" placeholder="Submitter">
+            </label>
+
+            <label>Failure reason:
+                <input type="text" name="failure-reason"  placeholder="Reason">
+            </label>
+
+            <label>Arch:
+                <input type="text" name="arch" placeholder="Architecture">
+            </label>
+
+            <label>Subarch:
+                <input type="text" name="subarch" placeholder="Sub-Architecture">
+            </label>
+
+            <span style="white-space: nowrap">
+                <label class="checkbox">Static?
+                    <input type="checkbox" name="static" placeholder="Static">
+                </label>
+                <label class="checkbox">Status?
+                    <select class="checkbox" name="status">
+                        <option value="">Unspecified</option>
+                        <option value="OK">OK</option>
+                        <option value="NOK">NOK</option>
+                        <option value="TIMEOUT">TIMEOUT</option>
+                    </select>
+                </label>
+            </span>
+
+            <label>C library:
+                <input type="text" name="libc" placeholder="Library">
+            </label>
+
+            <label>From:
+                <input type="date" name="date[from]" id="date_f" placeholder="From">
+            </label>
+
+            <label>Symbols:
+                <textarea name="symbols" id="symbols" rows="20" cols="80"></textarea>
+            </label>
+
+            <label>To:
+                <input type="date" name="date[to]" id="date_t" placeholder="To">
+            </label>
+        </div>
+        <div id="symbols-holder">
+            <!-- Place here symbols with syntax: symbols[BR2_SYMBOL]=SYMBOL_VALUE -->
+        </div>
+        <input type="submit" value="Search!">
+    </form>
+</div>
+
+<?php
+bab_footer();
+?>
diff --git a/web/stylesheet.css b/web/stylesheet.css
index f2b5ac1..a3c6e50 100644
--- a/web/stylesheet.css
+++ b/web/stylesheet.css
@@ -37,3 +37,82 @@ table tr.nok td {
 table tr.timeout td {
     background-color: #ffe79e;
 }
+
+div .from-class {
+	margin: auto;
+}
+
+form {
+	margin: 0px auto 50px;
+	max-width: 1080px;
+	min-width: 350px;
+	padding: 20px 12px 10px 20px;
+}
+
+form label {
+	display: inline-block;
+	width: 150px;
+	margin: 0px 200px 20px 0px;
+}
+
+form label.checkbox {
+	all: initial;
+	display: inline-block;
+	width: 75px;
+	margin: 0px 100px 20px 0px;
+}
+
+form input,
+form select,
+form textarea {
+	float: left;
+	width: 25%;
+	min-width: 300px;
+	box-sizing: border-box;
+	border: 1px solid #c2c2c2;
+	box-shadow: 1px 1px 4px #ebebeb;
+	border-radius: 3px;
+	outline: none;
+	padding: 7px;
+	margin: 0px 50px 0px 0px;
+}
+
+form input[type=checkbox] {
+	min-width: 20px;
+	width:25px;
+	height:25px;
+	margin-top: 5px;
+}
+
+form select.checkbox {
+	min-width: 120px;
+}
+
+form textarea {
+	height: 100px;
+	vertical-align: top;
+}
+
+form input:focus,
+form textarea:focus,
+form select:focus {
+	border: 1px solid #0c0;
+}
+
+form input[type=submit],
+form input[type=button] {
+	border: none;
+	margin: 0 33%;
+	padding: 8px 15px 8px 15px;
+	margin-bottom: 0 auto;
+	background: #00aaff;
+	color: #fff;
+	box-shadow: 1px 1px 4px #dadada;
+	border-radius: 3px;
+}
+
+form input[type=submit]:hover,
+form input[type=button]:hover {
+	background: #0088ff;
+	color: #fff;
+}
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v2 0/4] allow results to be filtered by symbols

[Victor Huesca]

This second version fix a stupid typo in a function called causing the footer to
not display as excepted.


Victor Huesca (4):
  web/{funcs,db}.inc.php: add support for symbols in sql query
  web/index.php: add support for symbols to be passed via GET
  web/schema.sql: add indexes on the database schema
  web/request.{js,php} and web/stylesheet.css: new page to ease seaching
    for specific results

 web/db.inc.php     | 28 ++++++++++++++--
 web/funcs.inc.php  | 48 ++++++++++++++++++++++------
 web/index.php      | 25 ++++++++++++---
 web/request.js     | 47 +++++++++++++++++++++++++++
 web/request.php    | 67 +++++++++++++++++++++++++++++++++++++++
 web/schema.sql     |  7 +++-
 web/stylesheet.css | 79 ++++++++++++++++++++++++++++++++++++++++++++++
 7 files changed, 284 insertions(+), 17 deletions(-)
 create mode 100644 web/request.js
 create mode 100644 web/request.php

-- 
2.21.0

[Buildroot] [PATCH buildroot-test v2 1/4] web/{funcs, db}.inc.php: add support for symbols in sql query

[Victor Huesca]

This patch provide a way to filter results from database with a list of symbols.

This query has been optimized to scale the best it can when multiple symbols are
asked together. Actually I found 3 way to get the same results: `join`, `where in`
and `intersect`.
- The 1st one is really not usable in practice since it only returns results if
the symbols subquery return only a douzen of rows and there no more than 2 or 3
symbols asked at the same time.
- The 2nd can handle queries where thousand rows are involved -- which is still
less than common cases -- but on a very limited number of symbols too. We could
have hope a better result but it is still a way better than join.
- The last is the one realy want to use. It can handle any number of symbols
with any number of rows and return a result in a few seconds.

Unfortunalty this query make use of `intersect` which is not implemented in
mysql. However mariaDB implemented this feature in 2017 w/ the version 10.3.10
but our current databse is an oracle mysql not a mariaDB :(
We planned to move the database to an other server but since it is an stable
debian, the mariadb verison is too old to support select.
I implemented a dynamic check of mysql version. The type of query use to
handle symbols read this version and use `intersect` in case when the
version supports it.

TDRL; The select on symbols works but is not optimal yet. It will be optimal
as soon as the database support `intersect` without changing the php code.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>

---
Changes v1 --> v2:
  - Fix a typo in in a function call (bad_* instead of bab_*)
---
 web/db.inc.php    | 28 +++++++++++++++++++++++++--
 web/funcs.inc.php | 48 +++++++++++++++++++++++++++++++++++++----------
 2 files changed, 64 insertions(+), 12 deletions(-)

diff --git a/web/db.inc.php b/web/db.inc.php
index 99f83a2..f8b10a2 100644
--- a/web/db.inc.php
+++ b/web/db.inc.php
@@ -54,6 +54,30 @@ class db
 
     return $value;
   }
-}
 
-?>
\ No newline at end of file
+
+  // Test whereas the database the support a given feature
+  function has_feature($feature)
+  {
+    // Return -1 on v1 < v2, 0 on v1 = v2 and 1 on v1 > v2
+    $compare_versions = function($v1, $v2) {
+      for ($i = 0; $i < min(sizeof($v1), sizeof($v2)); $i++)
+        if ($v1[$i] != $v2[$i])
+          return $v1[$i] - $v2[$i];
+      return 0;
+    };
+
+    switch ($feature) {
+      case 'intersect': // SELECT was introduced in mariadb version 10.3.10
+        $res = $this->query("select version() version;");
+        $ver = mysqli_fetch_object($res)->version;
+        preg_match("/^(\d+(?:\.\d+)*)-.+$/", $ver, $match);
+        $version = array_map(function ($v) { return (int)$v; }, explode('.', $match[1]));
+        return $compare_versions($version, array(10, 3, 10)) >= 0;
+
+      default:
+        throw new Exception("Unknown feature", 1);
+    }
+  }
+}
+?>
diff --git a/web/funcs.inc.php b/web/funcs.inc.php
index 7e912c1..6bf11be 100644
--- a/web/funcs.inc.php
+++ b/web/funcs.inc.php
@@ -1,4 +1,5 @@
 <?php
+set_time_limit(0);
 include(dirname(__FILE__) . "/../web/config.inc.php");
 include(dirname(__FILE__) . "/../web/db.inc.php");
 
@@ -30,6 +31,24 @@ function bab_footer()
   echo "</html>\n";
 }
 
+function bab_format_sql_symbols($db, $symbols)
+{
+  $get_res_id = "select result_id id from symbol_per_result where symbol_id = (select id from config_symbol where name=%s and value=%s)";
+
+  $r = array_map(
+    function($name, $value) use ($db, $get_res_id) {
+      return sprintf($get_res_id, $db->quote_smart($name), $db->quote_smart($value));
+    },
+    array_keys($symbols),
+    $symbols
+  );
+
+  if ($db->has_feature('intersect'))
+    return implode(" intersect ", $r);
+  else
+    return implode(" and result_id in (", $r) . str_repeat(")", count($symbols)-1);
+}
+
 function bab_format_sql_filter($db, $filters)
 {
 	$status_map = array(
@@ -44,27 +63,35 @@ function bab_format_sql_filter($db, $filters)
 				return sprintf("%s like %s", $k, $db->quote_smart($v));
 			else if ($k == "status")
 				return sprintf("%s=%s", $k, $db->quote_smart($status_map[$v]));
-			else
+      elseif ($k == "date")
+        if (is_array($v)) {
+          if (isset($v['from'], $v['to']))
+            return sprintf("builddate between %s and %s", $db->quote_smart($v['from']), $db->quote_smart($v['to']));
+          else if (isset($v['to']))
+            return sprintf("builddate<=%s", $db->quote_smart($v['to']));
+          else
+            return sprintf("builddate>=%s", $db->quote_smart($v['from']));
+        } else // Assuming the date is a lower-bound
+          return sprintf("builddate>=%s", $db->quote_smart($v));
+      else
 				return sprintf("%s=%s", $k, $db->quote_smart($v));
 		},
 		$filters,
 		array_keys($filters)
 	));
 
-	if (count($filters))
-		return "where " . $sql_filters;
-	else
-		return "";
+  return (count($filters) ? "where " . $sql_filters : "");
 }
 
 /*
  * Returns the total number of results.
  */
-function bab_total_results_count($filters)
+function bab_total_results_count($filters, $symbols)
 {
   $db = new db();
   $condition = bab_format_sql_filter($db, $filters);
-  $sql = "select count(*) from results $condition;";
+  $symbols_condition = bab_format_sql_symbols($db, $symbols);
+  $sql = "select count(*) from results ".(count($symbols) > 0 ? "inner join ($symbols_condition) SYMBOLS using (id)" : "")."$condition;";
   $ret = $db->query($sql);
   if ($ret == FALSE) {
     echo "Something's wrong in here\n";
@@ -80,13 +107,14 @@ function bab_total_results_count($filters)
  * and limited to $count items. The items starting with $start=0 are
  * the most recent build results.
  */
-function bab_get_results($start=0, $count=100, $filters = array())
+function bab_get_results($start=0, $count=100, $filters = array(), $symbols = array())
 {
   global $status_map;
   $db = new db();
-
+  $symbols_condition = bab_format_sql_symbols($db, $symbols);
   $condition = bab_format_sql_filter($db, $filters);
-  $sql = "select * from results $condition order by builddate desc limit $start, $count;";
+  $sql = "select * from results ".(count($symbols) > 0 ? "inner join ($symbols_condition) SYMBOLS using (id)" : "")."$condition order by builddate desc limit $start, $count;";
+
   $ret = $db->query($sql);
   if ($ret == FALSE) {
     echo "Something's wrong with the SQL query\n";
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v2 2/4] web/index.php: add support for symbols to be passed via GET

[Victor Huesca]

This patch add support of a `symbols[<symbol>]=<value>` option via GET as it is
done with other fields.

The syntax used is `symbols[<symbol>]=<value>`, so multiple symbols can be
passed while the url is still readable and symbols are clearly isolated from
other fields.
These symbols are forwared to the backend to be integrated in the sql query.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/index.php | 25 +++++++++++++++++++++----
 1 file changed, 21 insertions(+), 4 deletions(-)

diff --git a/web/index.php b/web/index.php
index f3af62d..289a866 100644
--- a/web/index.php
+++ b/web/index.php
@@ -34,7 +34,18 @@ function format_url_args($args)
 	));
 }
 
+function setup_date($dates)
+{
+  if (isset($dates['from']) && isset($dates['to']))
+    return $dates;
+  else if (isset($dates['from']))
+    return $dates['from'];
+  else if (isset($dates['to']))
+    return array('to' => $dates['to']);
+}
+
 $filters = array();
+$symbols = array();
 
 /* When no start is given, or start is a crazy value (not an integer),
    just default to start=0 */
@@ -53,7 +64,7 @@ if ($step > 250)
 
 $valid_status = array("OK", "NOK", "TIMEOUT");
 
-if (isset ($_GET['status']) && in_array($_GET['status'], $valid_status))
+if (isset($_GET['status']) && in_array($_GET['status'], $valid_status))
   $filters["status"] = $_GET['status'];
 
 if (isset($_GET['arch']) && preg_match("/^[a-z0-9_]*$/", $_GET['arch']))
@@ -74,9 +85,15 @@ if (isset($_GET['static']) && preg_match("/^[0-1]$/", $_GET['static']))
 if (isset($_GET['subarch']) && preg_match("/^[A-Za-z0-9_\+\.\-]*$/", $_GET['subarch']))
   $filters["subarch"] = $_GET['subarch'];
 
-if (isset ($_GET['submitter']))
+if (isset($_GET['submitter']))
   $filters["submitter"] = urldecode($_GET['submitter']);
 
+if (isset($_GET['symbols']) && is_array($_GET['symbols']))
+  $symbols = $_GET['symbols'];
+
+if (isset($_GET['date']))
+  $filters["date"] = setup_date($_GET['date']);
+
 bab_header("Buildroot tests");
 
 echo "<table>\n";
@@ -85,7 +102,7 @@ echo "<tr class=\"header\">";
 echo "<td>Date</td><td>Duration</td><td>Status</td><td>Commit ID</td><td>Submitter</td><td>Arch/Subarch</td><td>Failure reason</td><td>Libc</td><td>Static?</td><td>Data</td>";
 echo "</tr>";
 
-$results = bab_get_results($start, $step, $filters);
+$results = bab_get_results($start, $step, $filters, $symbols);
 
 while ($current = mysqli_fetch_object($results)) {
 
@@ -156,7 +173,7 @@ echo "</table>\n";
 
 echo "<p style=\"text-align: center;\">";
 
-$total = bab_total_results_count($filters);
+$total = bab_total_results_count($filters, $symbols);
 
 $prev_args = $filters;
 $next_args = $filters;
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v2 3/4] web/schema.sql: add indexes on the database schema

[Victor Huesca]

Add indexes on the two columns of `symbol_per_result` to improve significantly
queries involving symbols (this is an over 80M rows table).
This table is heavily used to retrieve results matching a symbols and it is not
an option filter symbols without these indexes.

Also mark these same columns as foreign keys to ensure integrity of the
database.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/schema.sql | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)

diff --git a/web/schema.sql b/web/schema.sql
index 65fd883..8b854a2 100644
--- a/web/schema.sql
+++ b/web/schema.sql
@@ -27,5 +27,10 @@ CREATE TABLE `symbol_per_result` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `result_id` int(11) NOT NULL DEFAULT '0',
   `symbol_id` int(11) NOT NULL DEFAULT '0',
-  PRIMARY KEY (`id`)
+  PRIMARY KEY (`id`),
+  CONSTRAINT `fk_result_id` FOREIGN KEY (`result_id`) REFERENCES `results`(`id`),
+  CONSTRAINT `fk_symbol_id` FOREIGN KEY (`symbol_id`) REFERENCES `config_symbol`(`id`),
+  INDEX `ix_symbol_id`(`symbol_id`),
+  INDEX `ix_result_id`(`result_id`)
+
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v2 4/4] web/request.{js, php} and web/stylesheet.css: new page to ease seaching for specific results

[Victor Huesca]

Add form page to allow a more advanced and convenient way to search for specific configs.

Currently results are filtered by clicking on a symbol value in the autobuild
results page. With this method, we are limited to one field only (eg. submitter,
reason, etc). Any additional field has to be manually provided in the url.
While this method is great for automation, it is not intuitive nor efficient
for a human especially when it comes to filter by symbols since it it now
possible.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/request.js     | 47 +++++++++++++++++++++++++++
 web/request.php    | 67 +++++++++++++++++++++++++++++++++++++++
 web/stylesheet.css | 79 ++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 193 insertions(+)
 create mode 100644 web/request.js
 create mode 100644 web/request.php

diff --git a/web/request.js b/web/request.js
new file mode 100644
index 0000000..1d348bb
--- /dev/null
+++ b/web/request.js
@@ -0,0 +1,47 @@
+// Avoid empty input to be send by the form (this is clearer
+// and simplify treatment on server)
+function disableEmpties(node) {
+    if (node == undefined) {
+        node = document.getElementById("noblank");
+        prepareSymbols();
+    }
+    if (node.hasChildNodes() && node.tagName != "TEXTAREA" && node.tagName != "SELECT")
+        for(const child of node.children)
+            arguments.callee(child);
+    else if (node.value == "")
+        node.disabled = true;
+}
+
+// Add hidden inputs according to the symbols textarea
+function prepareSymbols() {
+    const container = document.getElementById("symbols-holder");
+    const symbols = document.getElementById("symbols");
+    const arr = symbols.value.trim().split("\n").filter(function (s) {
+        return s.trim() != '';
+    });
+
+    while (container.hasChildNodes())
+        container.removeChild(container.lastChild);
+
+    for (const line of arr) {
+        const [sym, ...rest] = line.split("=");
+        const input = document.createElement("input");
+        input.type = "hidden";
+        input.name = `symbols[${sym.trim()}]`;
+        input.value = rest.join("=").trim();
+        container.appendChild(input);
+    }
+    symbols.disabled = true;
+}
+
+// Re-enable inputs (in case of navigation via cache eg. w/
+// the previous button)
+window.onpageshow = function(node) {
+    if (node.tagName == undefined)
+        node = document.getElementById("noblank");
+    if (node.hasChildNodes() && node.tagName != "TEXTAREA" && node.tagName != "SELECT")
+        for(const child of node.children)
+            arguments.callee(child);
+    else
+        node.disabled = false;
+};
diff --git a/web/request.php b/web/request.php
new file mode 100644
index 0000000..da26491
--- /dev/null
+++ b/web/request.php
@@ -0,0 +1,67 @@
+<?php
+include("funcs.inc.php");
+
+bab_header("Buildroot tests - Request page");
+?>
+
+<script type='text/javascript' src="request.js"></script>
+
+<div class="form-class">
+    <form action="index.php" method="get" class="request-form" id="requester" onsubmit="disableEmpties() && prepareSymbols()">
+        <div id="noblank">
+            <label>Submitter:
+                <input type="text" name="submitter" placeholder="Submitter">
+            </label>
+
+            <label>Failure reason:
+                <input type="text" name="failure-reason"  placeholder="Reason">
+            </label>
+
+            <label>Arch:
+                <input type="text" name="arch" placeholder="Architecture">
+            </label>
+
+            <label>Subarch:
+                <input type="text" name="subarch" placeholder="Sub-Architecture">
+            </label>
+
+            <span style="white-space: nowrap">
+                <label class="checkbox">Static?
+                    <input type="checkbox" name="static" placeholder="Static">
+                </label>
+                <label class="checkbox">Status?
+                    <select class="checkbox" name="status">
+                        <option value="">Unspecified</option>
+                        <option value="OK">OK</option>
+                        <option value="NOK">NOK</option>
+                        <option value="TIMEOUT">TIMEOUT</option>
+                    </select>
+                </label>
+            </span>
+
+            <label>C library:
+                <input type="text" name="libc" placeholder="Library">
+            </label>
+
+            <label>From:
+                <input type="date" name="date[from]" id="date_f" placeholder="From">
+            </label>
+
+            <label>Symbols:
+                <textarea name="symbols" id="symbols" rows="20" cols="80"></textarea>
+            </label>
+
+            <label>To:
+                <input type="date" name="date[to]" id="date_t" placeholder="To">
+            </label>
+        </div>
+        <div id="symbols-holder">
+            <!-- Place here symbols with syntax: symbols[BR2_SYMBOL]=SYMBOL_VALUE -->
+        </div>
+        <input type="submit" value="Search!">
+    </form>
+</div>
+
+<?php
+bab_footer();
+?>
diff --git a/web/stylesheet.css b/web/stylesheet.css
index f2b5ac1..a3c6e50 100644
--- a/web/stylesheet.css
+++ b/web/stylesheet.css
@@ -37,3 +37,82 @@ table tr.nok td {
 table tr.timeout td {
     background-color: #ffe79e;
 }
+
+div .from-class {
+	margin: auto;
+}
+
+form {
+	margin: 0px auto 50px;
+	max-width: 1080px;
+	min-width: 350px;
+	padding: 20px 12px 10px 20px;
+}
+
+form label {
+	display: inline-block;
+	width: 150px;
+	margin: 0px 200px 20px 0px;
+}
+
+form label.checkbox {
+	all: initial;
+	display: inline-block;
+	width: 75px;
+	margin: 0px 100px 20px 0px;
+}
+
+form input,
+form select,
+form textarea {
+	float: left;
+	width: 25%;
+	min-width: 300px;
+	box-sizing: border-box;
+	border: 1px solid #c2c2c2;
+	box-shadow: 1px 1px 4px #ebebeb;
+	border-radius: 3px;
+	outline: none;
+	padding: 7px;
+	margin: 0px 50px 0px 0px;
+}
+
+form input[type=checkbox] {
+	min-width: 20px;
+	width:25px;
+	height:25px;
+	margin-top: 5px;
+}
+
+form select.checkbox {
+	min-width: 120px;
+}
+
+form textarea {
+	height: 100px;
+	vertical-align: top;
+}
+
+form input:focus,
+form textarea:focus,
+form select:focus {
+	border: 1px solid #0c0;
+}
+
+form input[type=submit],
+form input[type=button] {
+	border: none;
+	margin: 0 33%;
+	padding: 8px 15px 8px 15px;
+	margin-bottom: 0 auto;
+	background: #00aaff;
+	color: #fff;
+	box-shadow: 1px 1px 4px #dadada;
+	border-radius: 3px;
+}
+
+form input[type=submit]:hover,
+form input[type=button]:hover {
+	background: #0088ff;
+	color: #fff;
+}
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v2 1/4] web/{funcs, db}.inc.php: add support for symbols in sql query

[Thomas Petazzoni]

Hello Victor,

Thanks for working on this topic!

On Mon,  8 Jul 2019 10:17:04 +0200
Victor Huesca <victor.huesca@bootlin.com> wrote:

> This patch provide a way to filter results from database with a list of symbols.

I think using just the terminology "symbols" is a bit unclear. Instead
"configuration symbols" or "configuration options" would be better.

So perhaps:

""
This patch improves the bab_get_results() and bab_total_results_count()
so that we can filter autobuilder results from the database with a list
of configuration options and values.
""

> This query has been optimized to scale the best it can when multiple symbols are
> asked together. Actually I found 3 way to get the same results: `join`, `where in`
> and `intersect`.

"This" in "This query" doesn't mean much since there is no mention if a
query before. So just say "The SQL query". Also, avoiding first person
sentences in commit logs is typically preferred, i.e:

"""
The SQL query has been optimized to scale as best as possible when the
filtering takes place on multiple configuration options/values. We
identified three solutions for this SQL query, each providing the same
results: join, where in and intersect:
"""

> - The 1st one is really not usable in practice since it only returns results if
> the symbols subquery return only a douzen of rows and there no more than 2 or 3

return -> returns

douzen -> dozen

there no -> there are no

> symbols asked at the same time.

It really doesn't return any results ? Or is it that it is massively
slow ?

> - The 2nd can handle queries where thousand rows are involved -- which is still

thousand *of* rows. Also, we're not sure if by "rows" you're talking
about the build results or the config symbols.

> less than common cases -- but on a very limited number of symbols too. We could

I don't follow what you mean by "but on a very limited number of symbols too"

> have hope a better result but it is still a way better than join.

> - The last is the one realy want to use. It can handle any number of symbols

"is the one we really want to use".

> with any number of rows and return a result in a few seconds.
> 
> Unfortunalty this query make use of `intersect` which is not implemented in

Unfortunately

make -> makes

> mysql. However mariaDB implemented this feature in 2017 w/ the version 10.3.10
> but our current databse is an oracle mysql not a mariaDB :(

database

> We planned to move the database to an other server but since it is an stable

another

> debian, the mariadb verison is too old to support select.

version

select -> intersect

> I implemented a dynamic check of mysql version. The type of query use to

"Therefore, this commit implements a dynamic check of MySQL's version".

> handle symbols read this version and use `intersect` in case when the

read -> reads
use -> uses

"and uses intersect when it is supported".

> version supports it.
> 
> TDRL; The select on symbols works but is not optimal yet. It will be optimal
> as soon as the database support `intersect` without changing the php code.

TDRL -> TLDR.

But I'm not sure if this TLDR is really useful. The implementation is
optimal, it's just the current deployment that isn't.

> diff --git a/web/db.inc.php b/web/db.inc.php
> index 99f83a2..f8b10a2 100644
> --- a/web/db.inc.php
> +++ b/web/db.inc.php
> @@ -54,6 +54,30 @@ class db
>  
>      return $value;
>    }
> -}
>  
> -?>  
> \ No newline at end of file
> +
> +  // Test whereas the database the support a given feature
> +  function has_feature($feature)
> +  {
> +    // Return -1 on v1 < v2, 0 on v1 = v2 and 1 on v1 > v2
> +    $compare_versions = function($v1, $v2) {
> +      for ($i = 0; $i < min(sizeof($v1), sizeof($v2)); $i++)
> +        if ($v1[$i] != $v2[$i])
> +          return $v1[$i] - $v2[$i];
> +      return 0;
> +    };
> +
> +    switch ($feature) {
> +      case 'intersect': // SELECT was introduced in mariadb version 10.3.10
> +        $res = $this->query("select version() version;");
> +        $ver = mysqli_fetch_object($res)->version;
> +        preg_match("/^(\d+(?:\.\d+)*)-.+$/", $ver, $match);
> +        $version = array_map(function ($v) { return (int)$v; }, explode('.', $match[1]));
> +        return $compare_versions($version, array(10, 3, 10)) >= 0;
> +
> +      default:
> +        throw new Exception("Unknown feature", 1);
> +    }
> +  }

This could be added as a preliminary patch, separate from the filtering
logic.

> diff --git a/web/funcs.inc.php b/web/funcs.inc.php
> index 7e912c1..6bf11be 100644
> --- a/web/funcs.inc.php
> +++ b/web/funcs.inc.php
> @@ -1,4 +1,5 @@
>  <?php
> +set_time_limit(0);

This is probably some left-over debugging.

>  include(dirname(__FILE__) . "/../web/config.inc.php");
>  include(dirname(__FILE__) . "/../web/db.inc.php");
>  
> @@ -30,6 +31,24 @@ function bab_footer()
>    echo "</html>\n";
>  }
>  
> +function bab_format_sql_symbols($db, $symbols)

Perhaps: bab_format_sql_config_symbol_filter() would be better.

> +{
> +  $get_res_id = "select result_id id from symbol_per_result where symbol_id = (select id from config_symbol where name=%s and value=%s)";
> +
> +  $r = array_map(
> +    function($name, $value) use ($db, $get_res_id) {
> +      return sprintf($get_res_id, $db->quote_smart($name), $db->quote_smart($value));
> +    },
> +    array_keys($symbols),
> +    $symbols
> +  );
> +
> +  if ($db->has_feature('intersect'))
> +    return implode(" intersect ", $r);
> +  else
> +    return implode(" and result_id in (", $r) . str_repeat(")", count($symbols)-1);
> +}
> +
>  function bab_format_sql_filter($db, $filters)
>  {
>  	$status_map = array(
> @@ -44,27 +63,35 @@ function bab_format_sql_filter($db, $filters)
>  				return sprintf("%s like %s", $k, $db->quote_smart($v));
>  			else if ($k == "status")
>  				return sprintf("%s=%s", $k, $db->quote_smart($status_map[$v]));
> -			else
> +      elseif ($k == "date")
> +        if (is_array($v)) {
> +          if (isset($v['from'], $v['to']))
> +            return sprintf("builddate between %s and %s", $db->quote_smart($v['from']), $db->quote_smart($v['to']));
> +          else if (isset($v['to']))
> +            return sprintf("builddate<=%s", $db->quote_smart($v['to']));
> +          else
> +            return sprintf("builddate>=%s", $db->quote_smart($v['from']));
> +        } else // Assuming the date is a lower-bound
> +          return sprintf("builddate>=%s", $db->quote_smart($v));

This date filtering is unrelated to the configuration symbol filtering,
it should be a separate patch. Also, the indentation is bogus.

> +      else
>  				return sprintf("%s=%s", $k, $db->quote_smart($v));
>  		},
>  		$filters,
>  		array_keys($filters)
>  	));
>  
> -	if (count($filters))
> -		return "where " . $sql_filters;
> -	else
> -		return "";
> +  return (count($filters) ? "where " . $sql_filters : "");

This is some unrelated refactoring, should be in a separate patch.

>  /*
>   * Returns the total number of results.
>   */
> -function bab_total_results_count($filters)
> +function bab_total_results_count($filters, $symbols)
>  {
>    $db = new db();
>    $condition = bab_format_sql_filter($db, $filters);
> -  $sql = "select count(*) from results $condition;";
> +  $symbols_condition = bab_format_sql_symbols($db, $symbols);
> +  $sql = "select count(*) from results ".(count($symbols) > 0 ? "inner join ($symbols_condition) SYMBOLS using (id)" : "")."$condition;";

Can we do it like this perhaps:

     $sql = "select count(*) from results";

     if ($symbols_condition != "")
            $sql .= " inner join ($symbols_condition) symbols using (id) ";

     if ($condition != "")
            $sql .= " " . $condition";

     $sql .= ";"

And use lower-case "symbols" instead of "SYMBOLS" in upper-case.

>    $ret = $db->query($sql);
>    if ($ret == FALSE) {
>      echo "Something's wrong in here\n";
> @@ -80,13 +107,14 @@ function bab_total_results_count($filters)
>   * and limited to $count items. The items starting with $start=0 are
>   * the most recent build results.
>   */
> -function bab_get_results($start=0, $count=100, $filters = array())
> +function bab_get_results($start=0, $count=100, $filters = array(), $symbols = array())
>  {
>    global $status_map;
>    $db = new db();
> -
> +  $symbols_condition = bab_format_sql_symbols($db, $symbols);
>    $condition = bab_format_sql_filter($db, $filters);
> -  $sql = "select * from results $condition order by builddate desc limit $start, $count;";
> +  $sql = "select * from results ".(count($symbols) > 0 ? "inner join ($symbols_condition) SYMBOLS using (id)" : "")."$condition order by builddate desc limit $start, $count;";

Same comment as above.

But overall, I am wondering if it isn't the bab_format_sql_filter()
function that should be responsible for generating the condition. After
all, the symbols are part of the filtering.

So, what about having $filters["symbols"] contain what you pass as
$symbols. It really is a filter. It would also avoid duplicating the
logic to build the SQL query between bab_get_results() and
bab_get_total_result_count().

Of course, you can keep a sub-function bab_format_sql_symbols(), but it
would be called by bab_format_sql_filter().

Does that make sense ?

Thanks!

Thomas
-- 
Thomas Petazzoni, CTO, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

[Buildroot] [PATCH buildroot-test v2 2/4] web/index.php: add support for symbols to be passed via GET

[Thomas Petazzoni]

Hello Victor,

On Mon,  8 Jul 2019 10:17:05 +0200
Victor Huesca <victor.huesca@bootlin.com> wrote:

> This patch add support of a `symbols[<symbol>]=<value>` option via GET as it is
> done with other fields.

And it also implements date[...]=..., which should be in a separate patch.

> The syntax used is `symbols[<symbol>]=<value>`, so multiple symbols can be
> passed while the url is still readable and symbols are clearly isolated from
> other fields.
> These symbols are forwared to the backend to be integrated in the sql query.
> 
> Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>

It would be good to have another page that documents the HTTP query
arguments that this page understands, because these are not visible at
all through the front page.

> diff --git a/web/index.php b/web/index.php
> index f3af62d..289a866 100644
> --- a/web/index.php
> +++ b/web/index.php
> @@ -34,7 +34,18 @@ function format_url_args($args)
>  	));
>  }
>  
> +function setup_date($dates)
> +{
> +  if (isset($dates['from']) && isset($dates['to']))
> +    return $dates;
> +  else if (isset($dates['from']))
> +    return $dates['from'];
> +  else if (isset($dates['to']))
> +    return array('to' => $dates['to']);
> +}

Is this all really needed ? The last case just turns a dict with a "to"
key.. into a dict with a "to" key. And the second case turns a dict
with a "from" key into just the value, while your PATCH 1/4 implements
support for understanding a dict with a "from" key as well.

So, to me, this setup_date() function seems not necessary.

But again, it should be a separate patch.

> +
>  $filters = array();
> +$symbols = array();

I think this separate array should not be needed (see my comments on
PATCH 1/4).

>  
>  /* When no start is given, or start is a crazy value (not an integer),
>     just default to start=0 */
> @@ -53,7 +64,7 @@ if ($step > 250)
>  
>  $valid_status = array("OK", "NOK", "TIMEOUT");
>  
> -if (isset ($_GET['status']) && in_array($_GET['status'], $valid_status))
> +if (isset($_GET['status']) && in_array($_GET['status'], $valid_status))

This is a cosmetic change unrelated to this commit, should be part of
another patch.

Hint: use "git add -p" to stage your changes. This way, you can have
multiple changes in a file, and still put them into separate commits.

>    $filters["status"] = $_GET['status'];
>  
>  if (isset($_GET['arch']) && preg_match("/^[a-z0-9_]*$/", $_GET['arch']))
> @@ -74,9 +85,15 @@ if (isset($_GET['static']) && preg_match("/^[0-1]$/", $_GET['static']))
>  if (isset($_GET['subarch']) && preg_match("/^[A-Za-z0-9_\+\.\-]*$/", $_GET['subarch']))
>    $filters["subarch"] = $_GET['subarch'];
>  
> -if (isset ($_GET['submitter']))
> +if (isset($_GET['submitter']))

Also unrelated cosmetic change.

>    $filters["submitter"] = urldecode($_GET['submitter']);
>  
> +if (isset($_GET['symbols']) && is_array($_GET['symbols']))
> +  $symbols = $_GET['symbols'];

Use:

     $filters["symbols"] = $_GET['symbols'];

> +
> +if (isset($_GET['date']))
> +  $filters["date"] = setup_date($_GET['date']);

Should be in the separate patch that adds support for date filtering.

> +
>  bab_header("Buildroot tests");
>  
>  echo "<table>\n";
> @@ -85,7 +102,7 @@ echo "<tr class=\"header\">";
>  echo "<td>Date</td><td>Duration</td><td>Status</td><td>Commit ID</td><td>Submitter</td><td>Arch/Subarch</td><td>Failure reason</td><td>Libc</td><td>Static?</td><td>Data</td>";
>  echo "</tr>";
>  
> -$results = bab_get_results($start, $step, $filters);
> +$results = bab_get_results($start, $step, $filters, $symbols);

With the above change to use $filters to pass the array of symbols,
this change should no longer be needed.

> -$total = bab_total_results_count($filters);
> +$total = bab_total_results_count($filters, $symbols);

Ditto.

Thanks!

Thomas
-- 
Thomas Petazzoni, CTO, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

[Buildroot] [PATCH buildroot-test v2 3/4] web/schema.sql: add indexes on the database schema

[Thomas Petazzoni]

Hello Victor,

On Mon,  8 Jul 2019 10:17:06 +0200
Victor Huesca <victor.huesca@bootlin.com> wrote:

> Add indexes on the two columns of `symbol_per_result` to improve significantly
> queries involving symbols (this is an over 80M rows table).
> This table is heavily used to retrieve results matching a symbols and it is not
> an option filter symbols without these indexes.
> 
> Also mark these same columns as foreign keys to ensure integrity of the
> database.
> 
> Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>

Thanks, I've applied to buildroot-test with a slightly improved commit
log:

  https://git.buildroot.org/buildroot-test/commit/?id=d40fe4f389a60569e2369cb69f1703f7d8b1c6a3

Thomas
-- 
Thomas Petazzoni, CTO, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com