Elgg  Version master
AlterDatabaseToMultiByteCharset.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Elgg\Upgrades;
4 
7 
12 
13  private $utf8mb4_tables = [
14  // InnoDB
15  'access_collection_membership',
16  'access_collections',
17  'annotations',
18  'api_users',
19  'config',
20  'entities',
21  'entity_relationships',
22  'metadata',
23  'private_settings',
24  'queue',
25  'river',
26  'system_log',
27  'users_remember_me_cookies',
28  'users_sessions',
29  // MEMORY
30  'hmac_cache',
31  'users_apisessions',
32  ];
33 
34  // Columns with utf8 encoding and utf8_general_ci collation
35  // $table => [
36  // $column => $index
37  // ]
38 
39  private $non_mb4_columns = [
40  'config' => [
41  'name' => [
42  'primary' => true,
43  'name' => 'name',
44  'unique' => false,
45  ],
46  ],
47  'entities' => [
48  'subtype' => [
49  'primary' => false,
50  'name' => 'subtype',
51  'unique' => false,
52  ],
53  ],
54  'queue' => [
55  'name' => [
56  'primary' => false,
57  'name' => "name",
58  'unique' => false,
59  ],
60  ],
61  'users_sessions' => [
62  'session' => [
63  'primary' => true,
64  'name' => 'session',
65  'unique' => false,
66  ],
67  ],
68  'hmac_cache' => [
69  'hmac' => [
70  'primary' => true,
71  'name' => 'hmac',
72  'unique' => false,
73  ],
74  ],
75  'system_log' => [
76  'object_class' => [
77  'primary' => false,
78  'name' => 'object_class',
79  'unique' => false,
80  ],
81  'object_type' => [
82  'primary' => false,
83  'name' => 'object_type',
84  'unique' => false,
85  ],
86  'object_subtype' => [
87  'primary' => false,
88  'name' => 'object_subtype',
89  'unique' => false,
90  ],
91  'event' => [
92  'primary' => false,
93  'name' => 'event',
94  'unique' => false,
95  ],
96  'river_key' => [
97  'primary' => false,
98  'name' => 'river_key',
99  'unique' => false,
100  'columns' => ['object_type', 'object_subtype', 'event']
101  ],
102  ]
103  ];
104 
108  public function getVersion(): int {
109  return 2017080900;
110  }
111 
115  public function needsIncrementOffset(): bool {
116  return false;
117  }
118 
122  public function shouldBeSkipped(): bool {
123 
124  $config = _elgg_services()->dbConfig->getConnectionConfig();
125  $rows = _elgg_services()->db->getConnection('read')->executeQuery("SHOW TABLE STATUS FROM `{$config['database']}`");
126 
127  $prefixed_table_names = array_map(function ($t) use ($config) {
128  return "{$config['prefix']}{$t}";
129  }, $this->utf8mb4_tables);
130 
131  foreach ($rows->fetchAllAssociative() as $row) {
132  $row = (object) $row;
133  if (in_array($row->Name, $prefixed_table_names) && $row->Collation !== 'utf8mb4_general_ci') {
134  return false;
135  }
136  }
137 
138  return true;
139  }
140 
144  public function countItems(): int {
145  return 1;
146  }
147 
151  public function run(Result $result, $offset): Result {
152 
153  $config = _elgg_services()->dbConfig->getConnectionConfig();
154 
155  try {
156  // check if we need to change a global variable
157  $db_result = _elgg_services()->db->getConnection('read')->executeQuery("SHOW GLOBAL VARIABLES LIKE 'innodb_large_prefix'");
158  $rows = $db_result->fetchAllAssociative();
159 
160  if (empty($rows) || $rows[0]['Value'] === 'OFF') {
161  // required to allow bigger index sizes required for utf8mb4
162  _elgg_services()->db->getConnection('write')->executeStatement("SET GLOBAL innodb_large_prefix = 'ON'");
163  }
164  } catch (\Exception $e) {
165  // something went wrong, maybe database permissions, or version
166  $result->addFailures();
167  $result->addError("Failure to set 'innodb_large_prefix'. Ask your database administrator for more information.");
168  $result->addError("Alternatively ask the database administrator to (temporarily) set 'innodb_large_prefix' to 'ON'.");
169  $result->addError($e->getMessage());
170 
171  return $result;
172  }
173 
174  try {
175  // alter table structure
176  $connection = _elgg_services()->db->getConnection('write');
177  $connection->executeStatement("
178  ALTER DATABASE
179  `{$config['database']}`
180  CHARACTER SET = utf8mb4
181  COLLATE = utf8mb4_unicode_ci
182  ");
183 
184  foreach ($this->utf8mb4_tables as $table) {
185  if (!empty($this->non_mb4_columns[$table])) {
186  foreach ($this->non_mb4_columns[$table] as $column => $index) {
187  if ($index) {
188  if ($index['primary']) {
189  $connection->executeStatement("
190  ALTER TABLE {$config['prefix']}{$table}
191  DROP PRIMARY KEY
192  ");
193  } else {
194  $connection->executeStatement("
195  ALTER TABLE {$config['prefix']}{$table}
196  DROP KEY {$index['name']}
197  ");
198  }
199  }
200  }
201  }
202 
203  $connection->executeStatement("
204  ALTER TABLE {$config['prefix']}{$table}
205  ROW_FORMAT=DYNAMIC
206  ");
207 
208  $connection->executeStatement("
209  ALTER TABLE {$config['prefix']}{$table}
210  CONVERT TO CHARACTER SET utf8mb4
211  COLLATE utf8mb4_general_ci
212  ");
213 
214  if (!empty($this->non_mb4_columns[$table])) {
215  foreach ($this->non_mb4_columns[$table] as $column => $index) {
216  if (empty($index['columns'])) {
217  // Alter table only if the key is not composite
218  $connection->executeStatement("
219  ALTER TABLE {$config['prefix']}{$table}
220  MODIFY $column VARCHAR(255)
221  CHARACTER SET utf8
222  COLLATE utf8_unicode_ci
223  ");
224  }
225 
226  if (!$index) {
227  continue;
228  }
229 
230  $sql = "ADD";
231  if ($index['unique']) {
232  $sql .= " UNIQUE ({$index['name']})";
233  } else if ($index['primary']) {
234  $sql .= " PRIMARY KEY ({$index['name']})";
235  } else {
236  $key_columns = elgg_extract('columns', $index, [$column]);
237  $key_columns = implode(',', $key_columns);
238  $sql .= " KEY {$index['name']} ($key_columns)";
239  }
240 
241  $connection->executeStatement("
242  ALTER TABLE {$config['prefix']}{$table}
243  $sql
244  ");
245  }
246  }
247  }
248  } catch (\Exception $e) {
249  $result->addFailures();
250  $result->addError($e->getMessage());
251 
252  return $result;
253  }
254 
255  $result->addSuccesses();
256 
257  return $result;
258  }
259 }
$column
Definition: add.php:10
return[ 'admin/delete_admin_notices'=>['access'=> 'admin'], 'admin/menu/save'=>['access'=> 'admin'], 'admin/plugins/activate'=>['access'=> 'admin'], 'admin/plugins/activate_all'=>['access'=> 'admin'], 'admin/plugins/deactivate'=>['access'=> 'admin'], 'admin/plugins/deactivate_all'=>['access'=> 'admin'], 'admin/plugins/set_priority'=>['access'=> 'admin'], 'admin/security/security_txt'=>['access'=> 'admin'], 'admin/security/settings'=>['access'=> 'admin'], 'admin/security/regenerate_site_secret'=>['access'=> 'admin'], 'admin/site/cache/invalidate'=>['access'=> 'admin'], 'admin/site/flush_cache'=>['access'=> 'admin'], 'admin/site/icons'=>['access'=> 'admin'], 'admin/site/set_maintenance_mode'=>['access'=> 'admin'], 'admin/site/set_robots'=>['access'=> 'admin'], 'admin/site/theme'=>['access'=> 'admin'], 'admin/site/unlock_upgrade'=>['access'=> 'admin'], 'admin/site/settings'=>['access'=> 'admin'], 'admin/upgrade'=>['access'=> 'admin'], 'admin/upgrade/reset'=>['access'=> 'admin'], 'admin/user/ban'=>['access'=> 'admin'], 'admin/user/bulk/ban'=>['access'=> 'admin'], 'admin/user/bulk/delete'=>['access'=> 'admin'], 'admin/user/bulk/unban'=>['access'=> 'admin'], 'admin/user/bulk/validate'=>['access'=> 'admin'], 'admin/user/change_email'=>['access'=> 'admin'], 'admin/user/delete'=>['access'=> 'admin'], 'admin/user/login_as'=>['access'=> 'admin'], 'admin/user/logout_as'=>[], 'admin/user/makeadmin'=>['access'=> 'admin'], 'admin/user/resetpassword'=>['access'=> 'admin'], 'admin/user/removeadmin'=>['access'=> 'admin'], 'admin/user/unban'=>['access'=> 'admin'], 'admin/user/validate'=>['access'=> 'admin'], 'annotation/delete'=>[], 'avatar/upload'=>[], 'comment/save'=>[], 'diagnostics/download'=>['access'=> 'admin'], 'entity/chooserestoredestination'=>[], 'entity/delete'=>[], 'entity/mute'=>[], 'entity/restore'=>[], 'entity/subscribe'=>[], 'entity/trash'=>[], 'entity/unmute'=>[], 'entity/unsubscribe'=>[], 'login'=>['access'=> 'logged_out'], 'logout'=>[], 'notifications/mute'=>['access'=> 'public'], 'plugins/settings/remove'=>['access'=> 'admin'], 'plugins/settings/save'=>['access'=> 'admin'], 'plugins/usersettings/save'=>[], 'register'=>['access'=> 'logged_out', 'middleware'=>[\Elgg\Router\Middleware\RegistrationAllowedGatekeeper::class,],], 'river/delete'=>[], 'settings/notifications'=>[], 'settings/notifications/subscriptions'=>[], 'user/changepassword'=>['access'=> 'public'], 'user/requestnewpassword'=>['access'=> 'public'], 'useradd'=>['access'=> 'admin'], 'usersettings/save'=>[], 'widgets/add'=>[], 'widgets/delete'=>[], 'widgets/move'=>[], 'widgets/save'=>[],]
Definition: actions.php:73
$table
Definition: user.php:37
Class to extend for asynchronous upgrades, i.e.
Result of a single BatchUpgrade run.
Definition: Result.php:10
shouldBeSkipped()
{Should this upgrade be skipped?If true, the upgrade will not be performed and cannot be accessed lat...
countItems()
{The total number of items to process during the upgrade.If unknown, Batch::UNKNOWN_COUNT should be r...
run(Result $result, $offset)
{Runs upgrade on a single batch of items.If countItems() returns Batch::UNKNOWN_COUNT,...
getVersion()
{Version of the upgrade.This tells the date when the upgrade was added. It consists of eight digits a...
needsIncrementOffset()
{Should the run() method receive an offset representing all processed items?If true,...
$config
Advanced site settings, debugging section.
Definition: debugging.php:6
$index
Definition: gallery.php:40
_elgg_services()
Get the global service provider.
Definition: elgglib.php:353
elgg_extract($key, $array, $default=null, bool $strict=true)
Checks for $array[$key] and returns its value if it exists, else returns $default.
Definition: elgglib.php:256
if(empty($count)) $offset
Definition: pagination.php:26
$rows
Definition: redis.php:25