https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42218 Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Failed QA --- Comment #23 from Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> --- Hi all, I don't think we should introduce a VIEW paradigm here (unless we really must): - It would be Koha's first, bringing new patterns and overhead: a DBIC view class and a TestBuilder special case. - Restoring a dump under another DB user breaks it. mysqldump carries the DEFINER (koha_kohadev@%), so a prod copy into test, a dump into KTD, or a server move makes every query on erm_usage_yus fail with ERROR 1449 (SQL SECURITY INVOKER would avoid this). - It becomes invalid in the DB if an erm_usage_mus column is later renamed or dropped: the ALTER would succeed but the next report execution would fail. - It re-sums all monthly usage every time a report runs. My suggestion going forward: - Keep patches 1 and 2 (with the view wording in 2 adjusted) and the erm_usage_yus table. - At the end of CounterFile->store, rebuild the yearly rows for the provider, report type and years just imported: in one transaction, delete them and insert a SUM over all of erm_usage_mus (same GROUP BY as the view). Putting this in a single method (e.g. Koha::ERM::EUsage::YearlyUsages->rebuild) keeps it reusable if anything else ever writes monthly usage. - Do the same over the whole table in the atomicupdate (plain SQL) to fix existing data. - Add a test checking that every erm_usage_mus column is grouped, summed or deliberately skipped, so a new column can't be silently missed. I believe this would fix 42214 and 42215 at once: patch 2 in this series already removes the duplicate check that skipped rows, and the rebuild I'm proposing above leaves a single row per title/year/metric (and access type or YOP where the report has them) holding the full total, rather than partial rows from whatever import just happened. yus stays as stored data, but since it's always rebuilt from mus, it can't drift as long as monthly writes go through the rebuild. -- You are receiving this mail because: You are watching all bug changes.