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 |
} |