1 <?php
2 /**
3 * @package Joomla.Platform
4 * @subpackage Database
5 *
6 * @copyright Copyright (C) 2005 - 2017 Open Source Matters, Inc. All rights reserved.
7 * @license GNU General Public License version 2 or later; see LICENSE
8 */
9
10 defined('JPATH_PLATFORM') or die;
11
12 /**
13 * MySQLi import driver.
14 *
15 * @since 11.1
16 */
17 class JDatabaseImporterMysqli extends JDatabaseImporter
18 {
19 /**
20 * Checks if all data and options are in order prior to exporting.
21 *
22 * @return JDatabaseImporterMysqli Method supports chaining.
23 *
24 * @since 11.1
25 * @throws Exception if an error is encountered.
26 */
27 public function check()
28 {
29 // Check if the db connector has been set.
30 if (!($this->db instanceof JDatabaseDriverMysqli))
31 {
32 throw new Exception('JPLATFORM_ERROR_DATABASE_CONNECTOR_WRONG_TYPE');
33 }
34
35 // Check if the tables have been specified.
36 if (empty($this->from))
37 {
38 throw new Exception('JPLATFORM_ERROR_NO_TABLES_SPECIFIED');
39 }
40
41 return $this;
42 }
43
44 /**
45 * Get the SQL syntax to add a table.
46 *
47 * @param SimpleXMLElement $table The table information.
48 *
49 * @return string
50 *
51 * @since 11.1
52 * @throws RuntimeException
53 */
54 protected function xmlToCreate(SimpleXMLElement $table)
55 {
56 $existingTables = $this->db->getTableList();
57 $tableName = (string) $table['name'];
58
59 if (in_array($tableName, $existingTables))
60 {
61 throw new RuntimeException('The table you are trying to create already exists');
62 }
63
64 $createTableStatement = 'CREATE TABLE ' . $this->db->quoteName($tableName) . ' (';
65
66 foreach ($table->xpath('field') as $field)
67 {
68 $createTableStatement .= $this->getColumnSQL($field) . ', ';
69 }
70
71 $newLookup = $this->getKeyLookup($table->xpath('key'));
72
73 // Loop through each key in the new structure.
74 foreach ($newLookup as $key)
75 {
76 $createTableStatement .= $this->getKeySQL($key) . ', ';
77 }
78
79 // Remove the comma after the last key
80 $createTableStatement = rtrim($createTableStatement, ', ');
81
82 $createTableStatement .= ')';
83
84 return $createTableStatement;
85 }
86
87 /**
88 * Get the SQL syntax to add a column.
89 *
90 * @param string $table The table name.
91 * @param SimpleXMLElement $field The XML field definition.
92 *
93 * @return string
94 *
95 * @since 11.1
96 */
97 protected function getAddColumnSql($table, SimpleXMLElement $field)
98 {
99 return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ADD COLUMN ' . $this->getColumnSql($field);
100 }
101
102 /**
103 * Get the SQL syntax to add a key.
104 *
105 * @param string $table The table name.
106 * @param array $keys An array of the fields pertaining to this key.
107 *
108 * @return string
109 *
110 * @since 11.1
111 */
112 protected function getAddKeySql($table, $keys)
113 {
114 return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ADD ' . $this->getKeySql($keys);
115 }
116
117 /**
118 * Get alters for table if there is a difference.
119 *
120 * @param SimpleXMLElement $structure The XML structure pf the table.
121 *
122 * @return array
123 *
124 * @since 11.1
125 */
126 protected function getAlterTableSql(SimpleXMLElement $structure)
127 {
128 $table = $this->getRealTableName($structure['name']);
129 $oldFields = $this->db->getTableColumns($table, false);
130 $oldKeys = $this->db->getTableKeys($table);
131 $alters = array();
132
133 // Get the fields and keys from the XML that we are aiming for.
134 $newFields = $structure->xpath('field');
135 $newKeys = $structure->xpath('key');
136
137 // Loop through each field in the new structure.
138 foreach ($newFields as $field)
139 {
140 $fName = (string) $field['Field'];
141
142 if (isset($oldFields[$fName]))
143 {
144 // The field exists, check it's the same.
145 $column = $oldFields[$fName];
146
147 // Test whether there is a change.
148 $change = ((string) $field['Type'] != $column->Type) || ((string) $field['Null'] != $column->Null)
149 || ((string) $field['Default'] != $column->Default) || ((string) $field['Extra'] != $column->Extra);
150
151 if ($change)
152 {
153 $alters[] = $this->getChangeColumnSql($table, $field);
154 }
155
156 // Unset this field so that what we have left are fields that need to be removed.
157 unset($oldFields[$fName]);
158 }
159 else
160 {
161 // The field is new.
162 $alters[] = $this->getAddColumnSql($table, $field);
163 }
164 }
165
166 // Any columns left are orphans
167 foreach ($oldFields as $name => $column)
168 {
169 // Delete the column.
170 $alters[] = $this->getDropColumnSql($table, $name);
171 }
172
173 // Get the lookups for the old and new keys.
174 $oldLookup = $this->getKeyLookup($oldKeys);
175 $newLookup = $this->getKeyLookup($newKeys);
176
177 // Loop through each key in the new structure.
178 foreach ($newLookup as $name => $keys)
179 {
180 // Check if there are keys on this field in the existing table.
181 if (isset($oldLookup[$name]))
182 {
183 $same = true;
184 $newCount = count($newLookup[$name]);
185 $oldCount = count($oldLookup[$name]);
186
187 // There is a key on this field in the old and new tables. Are they the same?
188 if ($newCount == $oldCount)
189 {
190 // Need to loop through each key and do a fine grained check.
191 for ($i = 0; $i < $newCount; $i++)
192 {
193 $same = (((string) $newLookup[$name][$i]['Non_unique'] == $oldLookup[$name][$i]->Non_unique)
194 && ((string) $newLookup[$name][$i]['Column_name'] == $oldLookup[$name][$i]->Column_name)
195 && ((string) $newLookup[$name][$i]['Seq_in_index'] == $oldLookup[$name][$i]->Seq_in_index)
196 && ((string) $newLookup[$name][$i]['Collation'] == $oldLookup[$name][$i]->Collation)
197 && ((string) $newLookup[$name][$i]['Index_type'] == $oldLookup[$name][$i]->Index_type));
198
199 /*
200 Debug.
201 echo '<pre>';
202 echo '<br />Non_unique: '.
203 ((string) $newLookup[$name][$i]['Non_unique'] == $oldLookup[$name][$i]->Non_unique ? 'Pass' : 'Fail').' '.
204 (string) $newLookup[$name][$i]['Non_unique'].' vs '.$oldLookup[$name][$i]->Non_unique;
205 echo '<br />Column_name: '.
206 ((string) $newLookup[$name][$i]['Column_name'] == $oldLookup[$name][$i]->Column_name ? 'Pass' : 'Fail').' '.
207 (string) $newLookup[$name][$i]['Column_name'].' vs '.$oldLookup[$name][$i]->Column_name;
208 echo '<br />Seq_in_index: '.
209 ((string) $newLookup[$name][$i]['Seq_in_index'] == $oldLookup[$name][$i]->Seq_in_index ? 'Pass' : 'Fail').' '.
210 (string) $newLookup[$name][$i]['Seq_in_index'].' vs '.$oldLookup[$name][$i]->Seq_in_index;
211 echo '<br />Collation: '.
212 ((string) $newLookup[$name][$i]['Collation'] == $oldLookup[$name][$i]->Collation ? 'Pass' : 'Fail').' '.
213 (string) $newLookup[$name][$i]['Collation'].' vs '.$oldLookup[$name][$i]->Collation;
214 echo '<br />Index_type: '.
215 ((string) $newLookup[$name][$i]['Index_type'] == $oldLookup[$name][$i]->Index_type ? 'Pass' : 'Fail').' '.
216 (string) $newLookup[$name][$i]['Index_type'].' vs '.$oldLookup[$name][$i]->Index_type;
217 echo '<br />Same = '.($same ? 'true' : 'false');
218 echo '</pre>';
219 */
220
221 if (!$same)
222 {
223 // Break out of the loop. No need to check further.
224 break;
225 }
226 }
227 }
228 else
229 {
230 // Count is different, just drop and add.
231 $same = false;
232 }
233
234 if (!$same)
235 {
236 $alters[] = $this->getDropKeySql($table, $name);
237 $alters[] = $this->getAddKeySql($table, $keys);
238 }
239
240 // Unset this field so that what we have left are fields that need to be removed.
241 unset($oldLookup[$name]);
242 }
243 else
244 {
245 // This is a new key.
246 $alters[] = $this->getAddKeySql($table, $keys);
247 }
248 }
249
250 // Any keys left are orphans.
251 foreach ($oldLookup as $name => $keys)
252 {
253 if (strtoupper($name) == 'PRIMARY')
254 {
255 $alters[] = $this->getDropPrimaryKeySql($table);
256 }
257 else
258 {
259 $alters[] = $this->getDropKeySql($table, $name);
260 }
261 }
262
263 return $alters;
264 }
265
266 /**
267 * Get the syntax to alter a column.
268 *
269 * @param string $table The name of the database table to alter.
270 * @param SimpleXMLElement $field The XML definition for the field.
271 *
272 * @return string
273 *
274 * @since 11.1
275 */
276 protected function getChangeColumnSql($table, SimpleXMLElement $field)
277 {
278 return 'ALTER TABLE ' . $this->db->quoteName($table) . ' CHANGE COLUMN ' . $this->db->quoteName((string) $field['Field']) . ' '
279 . $this->getColumnSql($field);
280 }
281
282 /**
283 * Get the SQL syntax for a single column that would be included in a table create or alter statement.
284 *
285 * @param SimpleXMLElement $field The XML field definition.
286 *
287 * @return string
288 *
289 * @since 11.1
290 */
291 protected function getColumnSql(SimpleXMLElement $field)
292 {
293 // TODO Incorporate into parent class and use $this.
294 $blobs = array('text', 'smalltext', 'mediumtext', 'largetext');
295
296 $fName = (string) $field['Field'];
297 $fType = (string) $field['Type'];
298 $fNull = (string) $field['Null'];
299 $fDefault = isset($field['Default']) ? (string) $field['Default'] : null;
300 $fExtra = (string) $field['Extra'];
301
302 $query = $this->db->quoteName($fName) . ' ' . $fType;
303
304 if ($fNull == 'NO')
305 {
306 if (in_array($fType, $blobs) || $fDefault === null)
307 {
308 $query .= ' NOT NULL';
309 }
310 else
311 {
312 // TODO Don't quote numeric values.
313 $query .= ' NOT NULL DEFAULT ' . $this->db->quote($fDefault);
314 }
315 }
316 else
317 {
318 if ($fDefault === null)
319 {
320 $query .= ' DEFAULT NULL';
321 }
322 else
323 {
324 // TODO Don't quote numeric values.
325 $query .= ' DEFAULT ' . $this->db->quote($fDefault);
326 }
327 }
328
329 if ($fExtra)
330 {
331 $query .= ' ' . strtoupper($fExtra);
332 }
333
334 return $query;
335 }
336
337 /**
338 * Get the SQL syntax to drop a key.
339 *
340 * @param string $table The table name.
341 * @param string $name The name of the key to drop.
342 *
343 * @return string
344 *
345 * @since 11.1
346 */
347 protected function getDropKeySql($table, $name)
348 {
349 return 'ALTER TABLE ' . $this->db->quoteName($table) . ' DROP KEY ' . $this->db->quoteName($name);
350 }
351
352 /**
353 * Get the SQL syntax to drop a key.
354 *
355 * @param string $table The table name.
356 *
357 * @return string
358 *
359 * @since 11.1
360 */
361 protected function getDropPrimaryKeySql($table)
362 {
363 return 'ALTER TABLE ' . $this->db->quoteName($table) . ' DROP PRIMARY KEY';
364 }
365
366 /**
367 * Get the details list of keys for a table.
368 *
369 * @param array $keys An array of objects that comprise the keys for the table.
370 *
371 * @return array The lookup array. array({key name} => array(object, ...))
372 *
373 * @since 11.1
374 * @throws Exception
375 */
376 protected function getKeyLookup($keys)
377 {
378 // First pass, create a lookup of the keys.
379 $lookup = array();
380
381 foreach ($keys as $key)
382 {
383 if ($key instanceof SimpleXMLElement)
384 {
385 $kName = (string) $key['Key_name'];
386 }
387 else
388 {
389 $kName = $key->Key_name;
390 }
391
392 if (empty($lookup[$kName]))
393 {
394 $lookup[$kName] = array();
395 }
396
397 $lookup[$kName][] = $key;
398 }
399
400 return $lookup;
401 }
402
403 /**
404 * Get the SQL syntax for a key.
405 *
406 * @param array $columns An array of SimpleXMLElement objects comprising the key.
407 *
408 * @return string
409 *
410 * @since 11.1
411 */
412 protected function getKeySql($columns)
413 {
414 // TODO Error checking on array and element types.
415
416 $kNonUnique = (string) $columns[0]['Non_unique'];
417 $kName = (string) $columns[0]['Key_name'];
418 $kColumn = (string) $columns[0]['Column_name'];
419
420 $prefix = '';
421
422 if ($kName == 'PRIMARY')
423 {
424 $prefix = 'PRIMARY ';
425 }
426 elseif ($kNonUnique == 0)
427 {
428 $prefix = 'UNIQUE ';
429 }
430
431 $nColumns = count($columns);
432 $kColumns = array();
433
434 if ($nColumns == 1)
435 {
436 $kColumns[] = $this->db->quoteName($kColumn);
437 }
438 else
439 {
440 foreach ($columns as $column)
441 {
442 $kColumns[] = (string) $column['Column_name'];
443 }
444 }
445
446 $query = $prefix . 'KEY ' . ($kName != 'PRIMARY' ? $this->db->quoteName($kName) : '') . ' (' . implode(',', $kColumns) . ')';
447
448 return $query;
449 }
450 }
451