@@ -, +, @@ tables - 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 - Apply this patch and - Run: k$ prove t/db_dependent/Tags.t - Run: k$ prove t/db_dependent/Tags.t - Sign off :-D --- .../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 --- a/installer/data/mysql/atomicupdate/bug_21846.perl +++ a/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"; +} --- a/installer/data/mysql/kohastructure.sql +++ a/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`), --