From ff54fa8d1411de1d31ca3dd3263ffc0d381d1c33 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Fri, 23 Nov 2018 16:47:05 -0300 Subject: [PATCH] Bug 21846: Make 'term' use utf8mb_bin collation on tags tables MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch makes the utf8mb4_bin collation preferred for comparing tags. Otherwise suppolemental unicode characters all match. To test: - Enable tags and disable moderation (or plan to moderate and accept tags) - Tag 3 records: a - with 🐋 b - with 🌮 c - with 👍 - Note the weight on each says '3' - Click the tag to search, you get back all the records - Apply the previous patches from this bug report - Run: $ kshell k$ prove t/db_dependent/Tags.t => FAIL: Tests fail, related to counting stuffs - Apply this patch and (a) Run updatedatabase to upgrade the schema - Run: k$ prove t/db_dependent/Tags.t => SUCCESS: Tests pass! (b) reset_all to get a fresh DB using kohastructure.sql - Run: k$ prove t/db_dependent/Tags.t => SUCCESS: Tests pass too! - Sign off :-D Signed-off-by: Owen Leonard Signed-off-by: Josef Moravec --- installer/data/mysql/atomicupdate/bug_21846.perl | 26 ++++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 +++--- 2 files changed, 29 insertions(+), 3 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_21846.perl diff --git a/installer/data/mysql/atomicupdate/bug_21846.perl b/installer/data/mysql/atomicupdate/bug_21846.perl new file mode 100644 index 0000000000..6396f19acb --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_21846.perl @@ -0,0 +1,26 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + $dbh->do('SET FOREIGN_KEY_CHECKS=0'); + + # Change columns accordingly + $dbh->do(q{ + ALTER TABLE tags_index + MODIFY COLUMN term VARCHAR(191) COLLATE utf8mb4_bin NOT NULL; + }); + + $dbh->do(q{ + ALTER TABLE tags_approval + MODIFY COLUMN term VARCHAR(191) COLLATE utf8mb4_bin NOT NULL; + }); + + $dbh->do(q{ + ALTER TABLE tags_all + MODIFY COLUMN term VARCHAR(191) COLLATE utf8mb4_bin NOT NULL; + }); + + $dbh->do('SET FOREIGN_KEY_CHECKS=1'); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 21846 - Using emoji as tags has broken weights)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 76798c3163..74e2e4b5df 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2205,7 +2205,7 @@ CREATE TABLE `tags_all` ( -- all of the tags `tag_id` int(11) NOT NULL auto_increment, -- unique id and primary key `borrowernumber` int(11) DEFAULT NULL, -- the patron who added the tag (borrowers.borrowernumber) `biblionumber` int(11) NOT NULL, -- the bib record this tag was left on (biblio.biblionumber) - `term` varchar(255) NOT NULL, -- the tag + `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag `language` int(4) default NULL, -- the language the tag was left in `date_created` datetime NOT NULL, -- the date the tag was added PRIMARY KEY (`tag_id`), @@ -2223,7 +2223,7 @@ CREATE TABLE `tags_all` ( -- all of the tags DROP TABLE IF EXISTS `tags_approval`; CREATE TABLE `tags_approval` ( -- approved tags - `term` varchar(191) NOT NULL, -- the tag + `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag `approved` int(1) NOT NULL default '0', -- whether the tag is approved or not (1=yes, 0=pending, -1=rejected) `date_approved` datetime default NULL, -- the date this tag was approved `approved_by` int(11) default NULL, -- the librarian who approved the tag (borrowers.borrowernumber) @@ -2240,7 +2240,7 @@ CREATE TABLE `tags_approval` ( -- approved tags DROP TABLE IF EXISTS `tags_index`; CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are used - `term` varchar(191) NOT NULL, -- the tag + `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag `biblionumber` int(11) NOT NULL, -- the bib record this tag was used on (biblio.biblionumber) `weight` int(9) NOT NULL default '1', -- the number of times this term was used on this bib record PRIMARY KEY (`term`,`biblionumber`), -- 2.11.0