[Buildroot] [PATCH buildroot-test v3 0/3] allows to filter autobuild results by configuration symbols

[Buildroot] [PATCH buildroot-test v3 0/3] allows to filter autobuild results by configuration symbols

[Victor Huesca]

This patch series allows to filter autobuild results by configuration
options an values from the '.config' file.

Changes:
v1 --> v2:
 - fix a typo causing the footer to not appear
v2 --> v3:
 - reorganize commits
 - reword commit messages
 - move unrelated changes to other patch series
 - replace occurences of 'symbols' by 'config symbols'
 - move the call to 'bab_format_sql_config_symbol_filter' from the 
   '*get_results*' functions to 'bab_format_sql_filter'

Victor Huesca (3):
  web/db.inc.php: add support to test if the database supports a feature
  web/funcs.inc.php: add support for filtering autobuild results by
    configuration options
  web/index.php: add support for configuration symbols via GET

 web/db.inc.php    | 28 ++++++++++++++++++++++++++--
 web/funcs.inc.php | 40 ++++++++++++++++++++++++++++++++++------
 web/index.php     |  3 +++
 3 files changed, 63 insertions(+), 8 deletions(-)

-- 
2.21.0

[Buildroot] [PATCH buildroot-test v3 1/3] web/db.inc.php: add support to test if the database supports a feature

[Victor Huesca]

This patch allows to test if the database supports a given feature.

Any feature to be test against can be added by simply adding a new case
to the switch.

This patch allows to check if the database engine supports the
'intersect' SQL keyword. This keyword was implemented in mariadb 10.3.10
released in oct. 2018

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/db.inc.php | 28 ++++++++++++++++++++++++++--
 1 file changed, 26 insertions(+), 2 deletions(-)

diff --git a/web/db.inc.php b/web/db.inc.php
index 99f83a2..2dee2af 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 supports 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);
+    }
+  }
+}
+?>
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v3 2/3] web/funcs.inc.php: add support for filtering autobuild results by configuration options

[Victor Huesca]

This patch improves '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.

The SQL query has been optimized to scale the better when multiple
symbols are involved altogether. There is at least 3 ways to get
the same results: 'join', 'where in' and 'intersect'.
- The 'join' does not scale at all and can only return a dozen
  configurations in a reasonable amount of time.
- The 'intersect' outperforms the two others and allows to add as many
  configuration symbols as wanted, even when thousand of configs are
  returned.
- The 'where in' is the compromise solution. It works well for asking
  two or three configuration symbols at the same time but can become a
  real bottleneck when those symbols are commons are returns a too many
  configurations.
  Also its results depends on the underlying DBMS, this make its
  performances unpredictable.

The current deployment uses an old mysql server that does not supports
the 'intersect' queries. In order to scale well with a potential
migration of the SQL server to a MariaDB 10.3.10+, this patch implements
both the 'intersect' and 'where in' versions of the query. The default
behavior is to look for 'intersect' support and fallback on the 'where
in' query otherwise.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/funcs.inc.php | 40 ++++++++++++++++++++++++++++++++++------
 1 file changed, 34 insertions(+), 6 deletions(-)

diff --git a/web/funcs.inc.php b/web/funcs.inc.php
index 84f79a3..e3fb577 100644
--- a/web/funcs.inc.php
+++ b/web/funcs.inc.php
@@ -30,6 +30,24 @@ function bab_footer()
   echo "</html>\n";
 }
 
+function bab_format_sql_config_symbol_filter($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(
@@ -38,6 +56,10 @@ function bab_format_sql_filter($db, $filters)
     "TIMEOUT" => 2,
   );
 
+  # Move the symbols away from filters since implode wouldn't work with an empty key
+  $symbols = $filters['symbols'];
+  unset($filters['symbols']);
+
   $sql_filters = implode(' and ', array_map(
     function ($v, $k) use ($db, $status_map) {
       if ($k == "reason")
@@ -61,10 +83,16 @@ function bab_format_sql_filter($db, $filters)
     array_keys($filters)
   ));
 
-  if (count($filters))
-    return "where " . $sql_filters;
-  else
-    return "";
+  $sql = "";
+  if ($symbols) {
+    $symbols_condition = bab_format_sql_config_symbol_filter($db, $symbols);
+    $sql .= " inner join ($symbols_condition) symbols using (id)";
+
+  }
+  if (count($filters) != 0)
+    $sql .= " where $sql_filters";
+
+  return $sql;
 }
 
 /*
@@ -74,7 +102,7 @@ function bab_total_results_count($filters)
 {
   $db = new db();
   $condition = bab_format_sql_filter($db, $filters);
-  $sql = "select count(*) from results $condition;";
+  $sql = "select count(*) from results$condition;";
   $ret = $db->query($sql);
   if ($ret == FALSE) {
     echo "Something's wrong in here\n";
@@ -96,7 +124,7 @@ function bab_get_results($start=0, $count=100, $filters = array())
   $db = new db();
 
   $condition = bab_format_sql_filter($db, $filters);
-  $sql = "select * from results $condition order by builddate desc limit $start, $count;";
+  $sql = "select * from results$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 v3 3/3] web/index.php: add support for configuration symbols via GET

[Victor Huesca]

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

This syntax allows to pass multiple configuration symbols and values
while keeping the URL clean.

This interface allows to search configurations with specific symbols and
values.

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

diff --git a/web/index.php b/web/index.php
index 0c79aa6..503e5ad 100644
--- a/web/index.php
+++ b/web/index.php
@@ -62,6 +62,9 @@ if (isset ($_GET['submitter']))
 if (isset($_GET['date']))
   $filters["date"] = $_GET['date'];
 
+if (isset($_GET['symbols']) && is_array($_GET['symbols']))
+  $filters['symbols'] = $_GET['symbols'];
+
 bab_header("Buildroot tests");
 
 echo "<table>\n";
-- 
2.21.0

[Buildroot] [PATCH buildroot-test v3 0/3] allows to filter autobuild results by configuration symbols

[Thomas Petazzoni]

Hello Victor,

On Tue, 13 Aug 2019 15:13:16 +0200
Victor Huesca <victor.huesca@bootlin.com> wrote:

> Victor Huesca (3):
>   web/db.inc.php: add support to test if the database supports a feature
>   web/funcs.inc.php: add support for filtering autobuild results by
>     configuration options
>   web/index.php: add support for configuration symbols via GET
> 
>  web/db.inc.php    | 28 ++++++++++++++++++++++++++--
>  web/funcs.inc.php | 40 ++++++++++++++++++++++++++++++++++------
>  web/index.php     |  3 +++
>  3 files changed, 63 insertions(+), 8 deletions(-)

Thanks, series applied to buildroot-test! You'll notice that on the
production server, it is quite slow when this new feature is used. I
guess it's going to be like this until we migrate to a newer version of
MariaDB that implements the "intersect" feature.

Thanks!

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