View | Details | Raw Unified | Return to bug 18336
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_18336_utf8mb4.perl (-1 / +157 lines)
Line 0 Link Here
0
- 
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
4
    $dbh->do(q|SET foreign_key_checks = 0|);
5
    my $sth = $dbh->table_info( '','','','TABLE' );
6
7
    while ( my ( $cat, $schema, $name, $type, $remarks ) = $sth->fetchrow_array ) {
8
        my $table_sth = $dbh->prepare(qq|SHOW CREATE TABLE $name|);
9
        $table_sth->execute;
10
        my @table = $table_sth->fetchrow_array;
11
        unless ( $table[1] =~ /COLLATE=utf8mb4_unicode_ci/ ) {
12
            # Some users might have done the upgrade to utf8mb4 on their own
13
            # to support supplemental chars (japanese, chinese, etc)
14
            if ( $name eq 'additional_fields' ) {
15
                $dbh->do(qq|
16
                    ALTER TABLE $name
17
                        DROP KEY `fields_uniq`,
18
                        ADD UNIQUE KEY `fields_uniq` (`tablename` (191), `name` (191))
19
                |);
20
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
21
            }
22
            elsif ( $name eq 'authorised_values' ) {
23
                $dbh->do(qq|
24
                    ALTER TABLE $name
25
                        DROP KEY `lib`,
26
                        ADD KEY `lib` (`lib` (191))
27
                |);
28
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
29
            }
30
            elsif ( $name eq 'borrower_modifications' ) {
31
                $dbh->do(qq|
32
                    ALTER TABLE $name
33
                        DROP PRIMARY KEY,
34
                        DROP KEY `verification_token`,
35
                        ADD PRIMARY KEY (`verification_token` (191),`borrowernumber`),
36
                        ADD KEY `verification_token` (`verification_token` (191))
37
                |);
38
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
39
            }
40
            elsif ( $name eq 'columns_settings' ) {
41
                $dbh->do(qq|
42
                    ALTER TABLE $name
43
                        DROP PRIMARY KEY,
44
                        ADD PRIMARY KEY (`module` (191), `page` (191), `tablename` (191), `columnname` (191))
45
                |);
46
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
47
            }
48
            elsif ( $name eq 'illrequestattributes' ) {
49
                $dbh->do(qq|
50
                    ALTER TABLE $name
51
                        DROP PRIMARY KEY,
52
                        ADD PRIMARY KEY  (`illrequest_id`, `type` (191))
53
                |);
54
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
55
            }
56
            elsif ( $name eq 'items_search_fields' ) {
57
                $dbh->do(qq|
58
                    ALTER TABLE $name
59
                        DROP PRIMARY KEY,
60
                        ADD PRIMARY KEY (`name` (191))
61
                |);
62
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
63
            }
64
            elsif ( $name eq 'marc_subfield_structure' ) {
65
                # In this case we convert each column explicitly
66
                # to preserve 'tagsubield' collation (utf8mb4_bin)
67
                $dbh->do(qq|
68
                    ALTER TABLE $name
69
                        MODIFY COLUMN tagfield
70
                            VARCHAR(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
71
                        MODIFY COLUMN tagsubfield
72
                            VARCHAR(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
73
                        MODIFY COLUMN liblibrarian
74
                            VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
75
                        MODIFY COLUMN libopac
76
                            VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
77
                        MODIFY COLUMN kohafield
78
                            VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
79
                        MODIFY COLUMN authorised_value
80
                            VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
81
                        MODIFY COLUMN authtypecode
82
                            VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
83
                        MODIFY COLUMN value_builder
84
                            VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
85
                        MODIFY COLUMN frameworkcode
86
                            VARCHAR(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
87
                        MODIFY COLUMN seealso
88
                            VARCHAR(1100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
89
                        MODIFY COLUMN link
90
                            VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL
91
                |);
92
                $dbh->do(qq|ALTER TABLE $name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
93
            }
94
            elsif ( $name eq 'plugin_data' ) {
95
                $dbh->do(qq|
96
                    ALTER TABLE $name
97
                        DROP PRIMARY KEY,
98
                        ADD PRIMARY KEY (`plugin_class` (191), `plugin_key` (191))
99
                |);
100
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
101
            }
102
            elsif ( $name eq 'search_field' ) {
103
                $dbh->do(qq|
104
                    ALTER TABLE $name
105
                        DROP KEY `name`,
106
                        ADD UNIQUE KEY `name` (`name` (191))
107
                |);
108
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
109
            }
110
            elsif ( $name eq 'search_marc_map' ) {
111
                $dbh->do(qq|
112
                    ALTER TABLE $name
113
                        DROP KEY `index_name`,
114
                        ADD UNIQUE KEY `index_name` (`index_name`, `marc_field` (191), `marc_type`)
115
                |);
116
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
117
            }
118
            elsif ( $name eq 'sms_providers' ) {
119
                $dbh->do(qq|
120
                    ALTER TABLE $name
121
                        DROP KEY `name`,
122
                        ADD UNIQUE KEY `name` (`name` (191))
123
                |);
124
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
125
            }
126
            elsif ( $name eq 'tags' ) {
127
                $dbh->do(qq|
128
                    ALTER TABLE $name
129
                        DROP PRIMARY KEY,
130
                        ADD PRIMARY KEY (`entry` (191))
131
                |);
132
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
133
            }
134
            elsif ( $name eq 'tags_approval' ) {
135
                $dbh->do(qq|
136
                    ALTER TABLE $name
137
                        MODIFY COLUMN `term` VARCHAR(191) NOT NULL
138
                |);
139
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
140
            }
141
            elsif ( $name eq 'tags_index' ) {
142
                $dbh->do(qq|
143
                    ALTER TABLE $name
144
                        MODIFY COLUMN `term` VARCHAR(191) NOT NULL
145
                |);
146
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
147
            }
148
            else {
149
                $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|);
150
            }
151
        }
152
    }
153
    $dbh->do(q|SET foreign_key_checks = 1|);;
154
155
    print "Upgrade to $DBversion done (Bug 18336: Convert DB tables to utf8mb4)\n";
156
    SetVersion($DBversion);
157
}

Return to bug 18336