allow results to be filtered by symbols
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
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
@@ -54,6 +54,30 @@ class db return $value; } -} -?> + + // 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
PATCH 2/4 web/index.php: add support for symbols to be passed via GET
In reply to Victor HuescaThis 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
@@ -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
PATCH 3/4 web/schema.sql: add indexes on the database schema
In reply to Victor HuescaAdd 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
@@ -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
PATCH 4/4 web/request.{js, php} and web/stylesheet.css: new page to ease seaching for specific results
In reply to Victor HuescaAdd 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
@@ -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
v2 cover allow results to be filtered by symbols
In reply to Victor HuescaThis 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
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
@@ -54,6 +54,30 @@ class db return $value; } -} -?> + + // 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
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 inandintersect.
"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
intersectwhich 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
intersectin 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
intersectwithout 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@@ -54,6 +54,30 @@ class db return $value; } -} -?> + + // 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@@ -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
-0include(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
PATCH v2 2/4 web/index.php: add support for symbols to be passed via GET
In reply to Victor HuescaThis 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
@@ -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
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.Signed-off-by Victor Huesca <[email protected]>
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@@ -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
PATCH v2 3/4 web/schema.sql: add indexes on the database schema
In reply to Victor HuescaAdd 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
@@ -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
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_resultto 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 <[email protected]>
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
PATCH v2 4/4 web/request.{js, php} and web/stylesheet.css: new page to ease seaching for specific results
In reply to Victor HuescaAdd 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
@@ -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