Skip to contentSkip to conversation

allow results to be filtered by symbols

PATCH Superseded Victor Huesca 2 participants 13 messages Jun 21, 2019 – Jul 12, 2019

lore
Share & tools
lore
#0 lore raw

cover allow results to be filtered by symbols

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
Signature · 1 line
2.21.0
#1 lore raw

PATCH 1/4 web/{funcs, db}.inc.php: add support for symbols in sql query

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.

---
 web/db.inc.php    | 28 +++++++++++++++++++++++++--
 web/funcs.inc.php | 48 +++++++++++++++++++++++++++++++++++++----------
 2 files changed, 64 insertions(+), 12 deletions(-)
web/db.inc.php +26 -2
index 99f83a2..f8b10a2 100644
@@ -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);
+    }
+  }
+}
+?>
web/funcs.inc.php +38 -10

88 lines not on this page · raw message

Signature · 1 line
2.21.0
#2 lore raw

PATCH 2/4 web/index.php: add support for symbols to be passed via GET

In reply to 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.

---
 web/index.php | 25 +++++++++++++++++++++----
 1 file changed, 21 insertions(+), 4 deletions(-)
web/index.php +21 -4
index f3af62d..289a866 100644
@@ -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;
Signature · 1 line
2.21.0
#3 lore raw

PATCH 3/4 web/schema.sql: add indexes on the database schema

In reply to 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.

---
 web/schema.sql | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)
web/schema.sql +6 -1
index 65fd883..8b854a2 100644
@@ -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;
Signature · 1 line
2.21.0
#4 lore raw

PATCH 4/4 web/request.{js, php} and web/stylesheet.css: new page to ease seaching for specific results

In reply to 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.

---
 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
web/request.js new file +47 -0
new file mode 100644
index 0000000..1d348bb
@@ -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;
+};
web/request.php new file +67 -0

67 lines not on this page · raw message

web/stylesheet.css +79 -0

82 lines not on this page · raw message

Signature · 1 line
2.21.0
#5 lore raw

v2 cover allow results to be filtered by symbols

In reply to 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
Signature · 1 line
2.21.0
#6 lore raw

PATCH v2 1/4 web/{funcs, db}.inc.php: add support for symbols in sql query

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.

---
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(-)
web/db.inc.php +26 -2
index 99f83a2..f8b10a2 100644
@@ -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);
+    }
+  }
+}
+?>
web/funcs.inc.php +38 -10

88 lines not on this page · raw message

Signature · 1 line
2.21.0
#7 lore raw

PATCH v2 1/4 web/{funcs, db}.inc.php: add support for symbols in sql query

Hello Victor,

Thanks for working on this topic!

Quoted 1 line · On Mon, 8 Jul 2019 10:17:04 +0200 Victor Huesca <[email protected]>…

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.
""

Quoted 3 lines

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:
"""

Quoted 2 lines

- 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

Quoted 1 line

symbols asked at the same time.

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

Quoted 1 line

- 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.

Quoted 1 line

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"

Quoted 3 lines

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".

Quoted 3 lines

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

Quoted 2 lines

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

Quoted 1 line

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

another

Quoted 1 line

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

version

select -> intersect

Quoted 1 line

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".

Quoted 1 line

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

read -> reads
use -> uses

"and uses intersect when it is supported".

Quoted 4 lines

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.

Quoted 26 lines
web/db.inc.php +14 -2
index 99f83a2..f8b10a2 100644
@@ -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

web/db.inc.php class db · line 81

Quoted 10 lines
web/db.inc.php +10 -0
+        $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.

web/funcs.inc.php line 2

Quoted 7 lines
web/funcs.inc.php +1 -0
index 7e912c1..6bf11be 100644
@@ -1,4 +1,5 @@
 <?php
+set_time_limit(0);

This is probably some left-over debugging.

web/funcs.inc.php function bab_footer() · line 34

Quoted 8 lines
web/funcs.inc.php +1 -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)

Perhaps: bab_format_sql_config_symbol_filter() would be better.

Quoted 25 lines
web/funcs.inc.php +17 -1
+{
+  $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

web/funcs.inc.php function bab_format_sql_filter($db, $filters) · line 75

Quoted 10 lines
web/funcs.inc.php +10 -0
+      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.

web/funcs.inc.php function bab_format_sql_filter($db, $filters) · line 83

Quoted 12 lines
web/funcs.inc.php +2 -4
+      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.

web/funcs.inc.php function bab_format_sql_filter($db, $filters) · line 94

Quoted 11 lines
web/funcs.inc.php +3 -2
 /*
  * 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.

Quoted 7 lines
web/funcs.inc.php +0 -0
   $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.
  */

web/funcs.inc.php function bab_total_results_count($filters) · line 116

Quoted 10 lines
web/funcs.inc.php +3 -3
-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

Signature · 3 lines
Thomas Petazzoni, CTO, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#8 lore raw

PATCH v2 2/4 web/index.php: add support for symbols to be passed via GET

In reply to 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.

---
 web/index.php | 25 +++++++++++++++++++++----
 1 file changed, 21 insertions(+), 4 deletions(-)
web/index.php +21 -4
index f3af62d..289a866 100644
@@ -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;
Signature · 1 line
2.21.0
#9 lore raw

PATCH v2 2/4 web/index.php: add support for symbols to be passed via GET

Hello Victor,

Quoted 2 lines · On Mon, 8 Jul 2019 10:17:05 +0200 Victor Huesca <[email protected]>…

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.

Quoted 6 lines

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.

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.

Quoted 7 lines
web/index.php +0 -0
index f3af62d..289a866 100644
@@ -34,7 +34,18 @@ function format_url_args($args)
 	));
 }

web/index.php function format_url_args($args) · line 45

Quoted 10 lines
web/index.php +9 -0
 
+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.

web/index.php function format_url_args($args) · line 48

Quoted 3 lines
web/index.php +2 -0
+
 $filters = array();
+$symbols = array();

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

web/index.php if ($step > 250) · line 67

Quoted 9 lines
web/index.php +1 -1
 /* 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.

web/index.php if (isset($_GET['static']) && preg_match("/^[0-1]$/", $_GET['static'])) · line 88

Quoted 9 lines
web/index.php +1 -1
   $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.

web/index.php if (isset($_GET['static']) && preg_match("/^[0-1]$/", $_GET['static'])) · line 92

Quoted 4 lines
web/index.php +2 -0
   $filters["submitter"] = urldecode($_GET['submitter']);
 
+if (isset($_GET['symbols']) && is_array($_GET['symbols']))
+  $symbols = $_GET['symbols'];

Use:

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

web/index.php if (isset($_GET['static']) && preg_match("/^[0-1]$/", $_GET['static'])) · line 95

Quoted 3 lines
web/index.php +3 -0
+
+if (isset($_GET['date']))
+  $filters["date"] = setup_date($_GET['date']);

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

web/index.php echo "<tr class=\"header\">"; · line 105

Quoted 10 lines
web/index.php +2 -1
+
 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.

web/index.php echo "</table>\n"; · line 176

Quoted 2 lines
web/index.php +1 -1
-$total = bab_total_results_count($filters);
+$total = bab_total_results_count($filters, $symbols);

Ditto.

Thanks!

Thomas

Signature · 3 lines
Thomas Petazzoni, CTO, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#10 lore raw

PATCH v2 3/4 web/schema.sql: add indexes on the database schema

In reply to 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.

---
 web/schema.sql | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)
web/schema.sql +6 -1
index 65fd883..8b854a2 100644
@@ -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;
Signature · 1 line
2.21.0
#11 lore raw

PATCH v2 3/4 web/schema.sql: add indexes on the database schema

Hello Victor,

Quoted 9 lines · On Mon, 8 Jul 2019 10:17:06 +0200 Victor Huesca <[email protected]>…

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.

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

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

Thomas

Signature · 3 lines
Thomas Petazzoni, CTO, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#12 lore raw

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

In reply to 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.

---
 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
web/request.js new file +47 -0
new file mode 100644
index 0000000..1d348bb
@@ -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;
+};
web/request.php new file +67 -0

67 lines not on this page · raw message

web/stylesheet.css +79 -0

82 lines not on this page · raw message

Signature · 1 line
2.21.0