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