From 0d942ba6981598aacc2b643344251a3dbefd749f 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 +- 2 files changed, 43 insertions(+), 33 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl b/installer/data/mysql/atomicupdate/bug_8628-digital-signs.perl index 54e24c1..b496e31 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_unicode_ci }); + } - $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 */; -- 2.1.4