Bug 21846: Make 'term' use utf8mb_bin collation on tags tables

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 <oleonard@myacpl.org>

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
(cherry picked from commit 04f1b0ca950a5a133f6afe6785787d1982414a1e)
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

diff --git a/installer/data/mysql/atomicupdate/bug_21846.perl b/installer/data/mysql/atomicupdate/bug_21846.perl
new file mode 100644 (file)
index 0000000..6396f19
--- /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 4b33b18..2feb824 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2204,7 +2204,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`),
@@ -2222,7 +2222,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)
@@ -2239,7 +2239,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`),