[Koha-bugs] [Bug 23626] Add a system preference to limit the number of rows of data used in a chart when viewing report results

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23626

--- Comment #13 from Paul Hoffman <paul at flo.org> ---
Here's an example from our Koha instance:

SELECT monthname(timestamp) AS month, year(timestamp) AS year,
count(itemnumber) AS count
FROM items
WHERE timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and
(yyyy-mm-dd)|date>>
GROUP BY year(timestamp), month(timestamp)

I have screenshots showing the steps in charting this -- I can attach them if
they're useful.

However, I don't want this to distract from the problem that this bug report
and patch are trying to mitigate (not solve!) which is that no matter what the
report is and what the user might end up doing with it -- charting, downloading
as *.ods or *.csv, or just viewing page by page -- the full report results (n
rows of data with n unbounded) are loaded into memory on the Koha server,
converted to JSON, and then returned to the user embedded in the generated
HTML.

For example, I ran a query that returned a single row; it yielded HTML
containing the following (some whitespace, along with the bulk of the function
body, removed for clarity):

--------------------------------------------------------------------------------
$('#draw-chart').click(function() {
    var x_elements = $('select[name="x"]').val();
    var y_elements = [];
    var groups = [];
    var lines = [];
    var options = {};
    headers = [{"cell":"month"},{"cell":"year"},{"cell":"count"}];
    var results;
    if ($('input[name="chart-include-all"]').prop('checked')) {
        results = [{"cells":[{"cell":"September"},{"cell":2019},{"cell":39}]}]
    }
    else {
        results =
[{"cells":[{"cell":"September"},{"cell":"2019"},{"cell":"39"}]}]
    }
    [...]
}
--------------------------------------------------------------------------------

The first line that sets the _results_ variable is where the full report
results appear; if the report had yielded 10,000 rows, there would be 10,000
elements in the array.

And I haven't even touched on the possibility of a query like this that fails
to properly JOIN tables:

SELECT foo.bar, baz.qux
FROM   foo, baz

If foo and baz each contain 10,000 rows, that's 100 million rows returned by
the DB, loaded into memory, converted to JSON, and -- well, it won't get that
far, because nobody is running a Koha instance with enough RAM and swap to
survive this.  But I would bet that a lot of Koha instances have (staff) users
with the requisite permissions to write reports but not the expertise in SQL
and RDBMSes that would steer them away from writing a query like this.

Stepping outside the bounds of this bug report for a moment, I'll just mention
some options for truly *fixing* the underlying problem that may be worth
considering; these include (a) Removing the option to chart the full report
results; (b) Implementing full-data charting using callbacks (AJAX or whatever)
to fetch data from Koha only as it's needed; and (c) Ripping out the charting
feature altogether on the principle of "do one thing well", which entails
taking the position that charting is best done in a tool (Libreoffice Calc,
Excel, whatever) dedicated to that task.  This last one is my favorite, but I'm
sure not everyone agrees!  :-)

-- 
You are receiving this mail because:
You are watching all bug changes.