Bug 13795 - Delete unused columns from statistics table
Summary: Delete unused columns from statistics table
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low minor (vote)
Assignee: Josef Moravec
QA Contact: Katrin Fischer
URL:
Keywords:
Depends on: 21683
Blocks:
  Show dependency treegraph
 
Reported: 2015-03-05 13:03 UTC by Nicole C. Engard
Modified: 2020-01-06 20:17 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
19.05.00


Attachments
Bug 13795: Delete unused columns from stats table (3.09 KB, patch)
2015-03-05 13:05 UTC, Nicole C. Engard
Details | Diff | Splinter Review
Bug 13795: Delete unused columns from statistics table (3.29 KB, patch)
2016-02-02 14:38 UTC, Mark Tompsett
Details | Diff | Splinter Review
Bug 13795: Delete unused columns from statistics table (2.99 KB, patch)
2019-03-20 15:36 UTC, Josef Moravec
Details | Diff | Splinter Review
Bug 13795: Make database update idempotent (1.32 KB, patch)
2019-03-20 15:36 UTC, Josef Moravec
Details | Diff | Splinter Review
Bug 13795: Remove occurences of unused columns in code (2.24 KB, patch)
2019-03-20 15:36 UTC, Josef Moravec
Details | Diff | Splinter Review
Bug 13795: Delete unused columns from statistics table (3.08 KB, patch)
2019-04-17 19:16 UTC, Michal Denar
Details | Diff | Splinter Review
Bug 13795: Make database update idempotent (1.41 KB, patch)
2019-04-17 19:16 UTC, Michal Denar
Details | Diff | Splinter Review
Bug 13795: Remove occurences of unused columns in code (2.34 KB, patch)
2019-04-17 19:16 UTC, Michal Denar
Details | Diff | Splinter Review
Bug 13795: Delete unused columns from statistics table (3.14 KB, patch)
2019-04-17 20:08 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 13795: Make database update idempotent (1.47 KB, patch)
2019-04-17 20:08 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 13795: Remove occurences of unused columns in code (2.39 KB, patch)
2019-04-17 20:08 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nicole C. Engard 2015-03-05 13:03:25 UTC
I ran the following on a system that has been using Koha for 3 years and it appears that none of these fields are ever used:


mysql> select distinct proccode from statistics;
+----------+
| proccode |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

mysql> select distinct other from statistics;
+-------+
| other |
+-------+
|       |
+-------+
1 row in set (18.30 sec)

mysql> select distinct usercode from statistics;
+----------+
| usercode |
+----------+
| NULL     |
+----------+
1 row in set (0.03 sec)

mysql> select distinct associatedborrower from statistics;
+--------------------+
| associatedborrower |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)
Comment 1 Nicole C. Engard 2015-03-05 13:05:58 UTC Comment hidden (obsolete)
Comment 2 Dobrica Pavlinusic 2015-03-05 13:40:29 UTC
I have something in proccode, but nothing in others:

mysql> select distinct proccode from statistics;        +----------+
| proccode |
+----------+
| NULL     |
|          |
| 1        |
| 2        |
| 3        |
| 6        |
+----------+
6 rows in set (0.00 sec)
Comment 3 Héctor Eduardo Castro Avalos 2015-11-14 03:04:04 UTC

All this columns are used by module Koha/Schema/Result/Statistic.pm

Inside the file it read
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE

and

# Created by DBIx::Class::Schema::Loader v0.07025 @ 2013-10-14 20:56:21
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:THELVmdFFi1RCI4DfqwGIw
Comment 4 Katrin Fischer 2015-11-17 13:05:01 UTC
Hi Hector, those files are generated from the database - so all the columns will appear there, but it doesn't indicate that they are used.
Comment 5 Bernardo Gonzalez Kriegel 2015-11-17 18:23:36 UTC
Patch does not apply.

Besides, there are other places that reference some of this values

e.g. proccode

koha-tmpl/intranet-tmpl/prog/en/columns.def:<field name="statistics.proccode">Type of procedure</field>

t/db_dependent/Stats.t:is ($params-> {accountno},      $line->{proccode},       "UpdateStats save accountno param in proccode field of statistics table");
Comment 6 Mark Tompsett 2016-02-02 14:38:42 UTC
Created attachment 47555 [details] [review]
Bug 13795: Delete unused columns from statistics table

The following 4 fields are never used in the statistics table:

associatedborrower
other
proccode
usercode

This patch removes them.

To test:

* check the database and make sure columns are gone
Comment 7 Mark Tompsett 2016-02-02 15:07:53 UTC
I made it apply, but Bernardo Gonzalez Kriegel raises a valid problem in comment #5.

$ git grep associatedborrower
-- There is mention of it in installer/data/mysql/backfill_statistics.pl
Is this script used at all? Obviously people aren't going to need it after upgrading from 2.x to 3.x versions. Perhaps safe to remove? Perhaps backfill_statistics.pl needs a rewrite?

other is used in C4::Circulation::AddIssue related to SIP mode.

In C4/Accounts.pm and C4/Circulation.pm (only places to use UpdateStats), the following fields are used:
branch
type
amount
other
itemnumber
itemtype
borrowernumber
ccode
accountno

schema says that usercode and associatedborrower is unused.
Looking at C4/Stats.pm it becomes painfully clear (painful, because they shouldn't have renamed the field like that) that accountno is proccode. I wonder if people are expecting it to be longer than varchar(4).

I would resubmit this modified with:
1) Just usercode and associatedborrower removed
2) an atomic update instead of a updatedatabase.pl
(https://wiki.koha-community.org/wiki/Database_updates)
Comment 8 Caroline Cyr La Rose 2018-03-30 14:51:21 UTC
Could we use "usercode" to log the user/librarian who did the action? It would be useful especially for action types that are not logged in action_logs (i.e. issue renewals, payments, writeoffs)

I just want to mention that I have a bunch of different values in proccode, but I have no idea what they mean.

mysql> select distinct(proccode) from statistics;
+----------+
| proccode |
+----------+
|          |
| 10       |
| 1005     |
| 1064     |
| 1073     |
| 1093     |
| 11       |
| 1152     |
| 12       |
...
| 9        |
| 903      |
| 918      |
| 920      |
| 932      |
| 947      |
| 98       |
| 991      |
| 996      |
+----------+
220 rows in set (0,07 sec)
Comment 9 Josef Moravec 2019-03-20 15:36:49 UTC
Created attachment 86809 [details] [review]
Bug 13795: Delete unused columns from statistics table

The following 2 fields are never used in the statistics table:

associatedborrower
usercode

This patch removes them.

To test:

* check the database and make sure columns are gone
Comment 10 Josef Moravec 2019-03-20 15:36:52 UTC
Created attachment 86810 [details] [review]
Bug 13795: Make database update idempotent
Comment 11 Josef Moravec 2019-03-20 15:36:56 UTC
Created attachment 86811 [details] [review]
Bug 13795: Remove occurences of unused columns in code

These patch removes these two columns from backfill_statstics script
and from columns.def - this file is used to create description in guided
reports

Test plan:
1. Run installer/data/mysql/backfull_statistics.pl
--> without this patch, there are associatedborrower and usercode errors
reported
--> with patch, these two columns are not reported
Comment 12 Michal Denar 2019-04-17 19:16:38 UTC
Created attachment 88233 [details] [review]
Bug 13795: Delete unused columns from statistics table

The following 2 fields are never used in the statistics table:

associatedborrower
usercode

This patch removes them.

To test:

* check the database and make sure columns are gone

Signed-off-by: Michal Denar <black23@gmail.com>
Comment 13 Michal Denar 2019-04-17 19:16:41 UTC
Created attachment 88234 [details] [review]
Bug 13795: Make database update idempotent

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Michal Denar <black23@gmail.com>
Comment 14 Michal Denar 2019-04-17 19:16:45 UTC
Created attachment 88235 [details] [review]
Bug 13795: Remove occurences of unused columns in code

These patch removes these two columns from backfill_statstics script
and from columns.def - this file is used to create description in guided
reports

Test plan:
1. Run installer/data/mysql/backfull_statistics.pl
--> without this patch, there are associatedborrower and usercode errors
reported
--> with patch, these two columns are not reported

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Michal Denar <black23@gmail.com>
Comment 15 Katrin Fischer 2019-04-17 20:08:24 UTC
Created attachment 88238 [details] [review]
Bug 13795: Delete unused columns from statistics table

The following 2 fields are never used in the statistics table:

associatedborrower
usercode

This patch removes them.

To test:

* check the database and make sure columns are gone

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 16 Katrin Fischer 2019-04-17 20:08:28 UTC
Created attachment 88239 [details] [review]
Bug 13795: Make database update idempotent

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 17 Katrin Fischer 2019-04-17 20:08:32 UTC
Created attachment 88240 [details] [review]
Bug 13795: Remove occurences of unused columns in code

These patch removes these two columns from backfill_statstics script
and from columns.def - this file is used to create description in guided
reports

Test plan:
1. Run installer/data/mysql/backfull_statistics.pl
--> without this patch, there are associatedborrower and usercode errors
reported
--> with patch, these two columns are not reported

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Michal Denar <black23@gmail.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 18 Nick Clemens 2019-04-18 12:06:03 UTC
Awesome work all!

Pushed to master for 19.05
Comment 19 Martin Renvoize 2019-04-26 13:45:51 UTC
Better safe than sorry I'm not going to backport this cleanup bug just in case anyone is using these fields locally.