Skip to contentSkip to conversation

allows to filter autobuild results by configuration symbols

PATCH New Victor Huesca 2 participants 5 messages Aug 13, 2019

lore
Share & tools
lore
#0 lore raw

v3 cover allows to filter autobuild results by configuration symbols

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

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

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

---
 web/db.inc.php | 28 ++++++++++++++++++++++++++--
 1 file changed, 26 insertions(+), 2 deletions(-)
web/db.inc.php +26 -2
index 99f83a2..2dee2af 100644
@@ -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);
+    }
+  }
+}
+?>
Signature · 1 line
2.21.0
#2 lore raw

PATCH v3 2/3 web/funcs.inc.php: add support for filtering autobuild results by configuration options

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

---
 web/funcs.inc.php | 40 ++++++++++++++++++++++++++++++++++------
 1 file changed, 34 insertions(+), 6 deletions(-)
web/funcs.inc.php +34 -6
index 84f79a3..e3fb577 100644
@@ -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";
Signature · 1 line
2.21.0
#3 lore raw

PATCH v3 3/3 web/index.php: add support for configuration symbols 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.

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.

---
 web/index.php | 3 +++
 1 file changed, 3 insertions(+)
web/index.php +3 -0
index 0c79aa6..503e5ad 100644
@@ -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";
Signature · 1 line
2.21.0
#4 lore raw

v3 cover allows to filter autobuild results by configuration symbols

In reply to Victor Huesca

Hello Victor,

Quoted 10 lines · On Tue, 13 Aug 2019 15:13:16 +0200 Victor Huesca <[email protected]>…

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

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