From 52048137e978ece76d395258bf4815e94a751d0f Mon Sep 17 00:00:00 2001 From: Aleisha Amohia Date: Tue, 25 Feb 2020 22:31:38 +0000 Subject: [PATCH] Bug 8628: (follow-up) Use utf8mb4 and TableExists in atomic update --- .../mysql/atomicupdate/bug_8628-digital-signs.perl | 70 ++++++++++++--------- installer/data/mysql/kohastructure.sql | 6 +- tools/.nfs00000000028807d100000006 | Bin 12288 -> 0 bytes 3 files changed, 43 insertions(+), 33 deletions(-) delete mode 100644 tools/.nfs00000000028807d100000006 diff --git a/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl b/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl index 54e24c1..5e68958 100644 --- a/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl +++ b/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl @@ -1,36 +1,46 @@ $DBversion = 'XXX'; if( CheckVersion( $DBversion ) ) { - $dbh->do(q{DROP TABLE IF EXISTS sign_streams }); - $dbh->do(q{DROP TABLE IF EXISTS signs }); - $dbh->do(q{CREATE TABLE sign_streams ( - sign_stream_id int(11) NOT NULL auto_increment, -- primary key, used to identify streams - saved_sql_id int(11) NOT NULL, -- foreign key from the saved_sql table - name varchar(64), -- name/title of the sign - PRIMARY KEY (sign_stream_id), - CONSTRAINT sign_streams_ibfk_1 FOREIGN KEY (saved_sql_id) REFERENCES saved_sql (id) ON DELETE CASCADE - ) ENGINE=InnoDB DEFAULT CHARSET=utf8 }); - $dbh->do(q{CREATE TABLE signs ( - sign_id int(11) NOT NULL auto_increment, -- primary key, used to identify signs - name varchar(64), -- name/title of the deck - webapp int(1) NOT NULL DEFAULT 0, -- display as web app or normal page - transition char(16) NOT NULL DEFAULT 'none', -- transition to use between pages, must be one of the transitions defined by jQuery Mobile (see their docs) - swatch char(1) NOT NULL DEFAULT '', -- swatches determine the colors of page elements - idleafter int(11) NOT NULL default 0, -- seconds to wait before automatic page turning cicks in - pagedelay int(11) NOT NULL default 0, -- seconds to wait before turning to a new page/record - reloadafter int(11) NOT NULL default 0, -- seconds to wait before reloading all the pages - PRIMARY KEY (sign_id) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8 }); + if( TableExists('sign_streams') ){ + $dbh->do(q{ ALTER TABLE sign_streams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci }); + } else { + $dbh->do(q{ CREATE TABLE sign_streams ( + sign_stream_id int(11) NOT NULL auto_increment, -- primary key, used to identify streams + saved_sql_id int(11) NOT NULL, -- foreign key from the saved_sql table + name varchar(64), -- name/title of the sign + PRIMARY KEY (sign_stream_id), + CONSTRAINT sign_streams_ibfk_1 FOREIGN KEY (saved_sql_id) REFERENCES saved_sql (id) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4 }); + } - $dbh->do(q{DROP TABLE IF EXISTS signs_to_streams }); - $dbh->do(q{CREATE TABLE signs_to_streams ( - sign_to_stream_id int(11) NOT NULL auto_increment, -- primary key, used to identify connections between signs and streams (the same stream can be attached to a sign more than once, with different parameters) - sign_stream_id int(11) NOT NULL, -- foreign key from the decks sign_streams table - sign_id int(11) NOT NULL, -- foreign key from the signs table - params varchar(255) NOT NULL DEFAULT '', -- list of parameters for the SQL associated with the sign_stream - PRIMARY KEY (sign_to_stream_id), - CONSTRAINT signs_to_streams_ibfk_1 FOREIGN KEY (sign_stream_id) REFERENCES sign_streams (sign_stream_id) ON DELETE CASCADE, - CONSTRAINT signs_to_streams_ibfk_2 FOREIGN KEY (sign_id) REFERENCES signs (sign_id) ON DELETE CASCADE - ) ENGINE=InnoDB DEFAULT CHARSET=utf8 }); + if( TableExists('signs') ){ + $dbh->do(q{ ALTER TABLE signs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci }); + } else { + $dbh->do(q{ CREATE TABLE signs ( + sign_id int(11) NOT NULL auto_increment, -- primary key, used to identify signs + name varchar(64), -- name/title of the deck + webapp int(1) NOT NULL DEFAULT 0, -- display as web app or normal page + transition char(16) NOT NULL DEFAULT 'none', -- transition to use between pages, must be one of the transitions defined by jQuery Mobile (see their docs) + swatch char(1) NOT NULL DEFAULT '', -- swatches determine the colors of page elements + idleafter int(11) NOT NULL default 0, -- seconds to wait before automatic page turning cicks in + pagedelay int(11) NOT NULL default 0, -- seconds to wait before turning to a new page/record + reloadafter int(11) NOT NULL default 0, -- seconds to wait before reloading all the pages + PRIMARY KEY (sign_id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci }); + } + + if( TableExists('signs_to_streams') ){ + $dbh->do(q{ ALTER TABLE signs_to_streams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci }); + } else { + $dbh->do(q{ CREATE TABLE signs_to_streams ( + sign_to_stream_id int(11) NOT NULL auto_increment, -- primary key, used to identify connections between signs and streams (the same stream can be attached to a sign more than once, with different parameters) + sign_stream_id int(11) NOT NULL, -- foreign key from the decks sign_streams table + sign_id int(11) NOT NULL, -- foreign key from the signs table + params varchar(255) NOT NULL DEFAULT '', -- list of parameters for the SQL associated with the sign_stream + PRIMARY KEY (sign_to_stream_id), + CONSTRAINT signs_to_streams_ibfk_1 FOREIGN KEY (sign_stream_id) REFERENCES sign_streams (sign_stream_id) ON DELETE CASCADE, + CONSTRAINT signs_to_streams_ibfk_2 FOREIGN KEY (sign_id) REFERENCES signs (sign_id) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci }); + } SetVersion( $DBversion ); print "Upgrade to $DBversion done (Bug 8628 - add signs, sign_streams and signs_to_streams tables)\n"; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 0b609c2..c53f058 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4426,7 +4426,7 @@ CREATE TABLE sign_streams ( `name` varchar(64), -- name/title of the sign PRIMARY KEY (`sign_stream_id`), CONSTRAINT `sign_streams_ibfk_1` FOREIGN KEY (`saved_sql_id`) REFERENCES `saved_sql` (`id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table 'signs' @@ -4443,7 +4443,7 @@ CREATE TABLE signs ( `pagedelay` int(11) NOT NULL default 0, -- seconds to wait before turning to a new page/record `reloadafter` int(11) NOT NULL default 0, -- seconds to wait before reloading all the pages PRIMARY KEY (`sign_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table 'signs_to_streams' @@ -4458,7 +4458,7 @@ CREATE TABLE signs_to_streams ( PRIMARY KEY (`sign_to_stream_id`), CONSTRAINT `signs_to_streams_ibfk_1` FOREIGN KEY (`sign_stream_id`) REFERENCES `sign_streams` (`sign_stream_id`) ON DELETE CASCADE, CONSTRAINT `signs_to_streams_ibfk_2` FOREIGN KEY (`sign_id`) REFERENCES `signs` (`sign_id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; diff --git a/tools/.nfs00000000028807d100000006 b/tools/.nfs00000000028807d100000006 deleted file mode 100644 index a73a1f3c28fa6f94e65ac85bda5e34ff28f148c6..0000000000000000000000000000000000000000 GIT binary patch literal 0 HcmV?d00001 literal 12288 zcmeI2&u<$=6vwAMAn~IVm4D#nhg1hS-jo(WZ4${uNovRs%W>YJ?XEQr% zV}-z#Q%@X`sKf$_o z-IX^RJM?np2E+9nW50hC-g$|AaPj*m85{C}=CQFO=U#VJzKH|Pu#9k@x7yflGn(pQZI0BAAL>Di&|jAr z`vdwMI)W1DZRifP1>J;}pueAC>=)=~=sV~`=pOVIbQ8J){rNOwUqhckpFsDakD(?s z56wZBp`Q@*SJ0Qx7m&^27;={*;0QPZj({WJ2si?c!2gqg%1n}(PGF+2Gm<92CzEsn zfy1OarNyI6=y91AdrIAv0w0Wsl8_s*ymmUXhOd-0Mz|6Y`!XIFK1gkoR@Z28rjOaa zqTN$bBw~}!+D!V&Y~~pW*E}jQ7L6b9MXNLrrqfedY&zX>2hZ%JhMO!ctst5i>xoy) zcmoGstJRhmN-;{8*HXcCFX)6al1BKYW8z7o)lm30s1-U{T3*ZjPZK=JQ?P0HqMHru zhvLGFYh^ZR^+O=g;wY0E-f|GflpnISuCKmul}@I{JTpP8%$d1;_;l2a6ESgX?--qz zvB8O8Vdkf1d6j1DU{+L)O?oGF>%=^=F0H5k9Q-vCr^l9IX>tQLVP^$~hW&4=JrTwcN`W?X+D7 z#GhJy7+$ z(P&f|#92k@ffvf2h*OcSRWSQdWt0Xg3w;XsP>>cqA??!P)KkfrRG&;BXlwU?wnQv6 z52=}T;exhL-LM6CdH68(m8M8(ft6;s3{(5iosHJ|t@>`e{#s+Z(LN-YY&P1v8~gjT zx!0mPHS4W*WBp*e-lFC~tGTzoQK5aLgHSO2cB6f3@1RZf-9x%vZ?)j*u$B|RMkX;7 zaUQe8_Lj(G#I-$lZgat8yLU*YoHRgE-SJGeeH%+!#8R^fP>;u`_7sCn8cW{G>*dS- l7#(@W?Etv?G87gYUSq~aVlh^*{N}tY)m~S|UV;V5{s9J?0Ve