Joomla Platform  13.1
Documentation des API du framework Joomla Platform
 Tout Classes Espaces de nommage Fichiers Fonctions Variables Pages
postgresql.php
Aller à la documentation de ce fichier.
1 <?php
2 /**
3  * @package Joomla.Platform
4  * @subpackage Database
5  *
6  * @copyright Copyright (C) 2005 - 2013 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  * PostgreSQL import driver.
14  *
15  * @package Joomla.Platform
16  * @subpackage Database
17  * @since 12.1
18  */
19 class JDatabaseImporterPostgresql extends JDatabaseImporter
20 {
21  /**
22  * Checks if all data and options are in order prior to exporting.
23  *
24  * @return JDatabaseImporterPostgresql Method supports chaining.
25  *
26  * @since 12.1
27  * @throws Exception if an error is encountered.
28  */
29  public function check()
30  {
31  // Check if the db connector has been set.
32  if (!($this->db instanceof JDatabaseDriverPostgresql))
33  {
34  throw new Exception('JPLATFORM_ERROR_DATABASE_CONNECTOR_WRONG_TYPE');
35  }
36 
37  // Check if the tables have been specified.
38  if (empty($this->from))
39  {
40  throw new Exception('JPLATFORM_ERROR_NO_TABLES_SPECIFIED');
41  }
42 
43  return $this;
44  }
45 
46  /**
47  * Get the SQL syntax to add a column.
48  *
49  * @param string $table The table name.
50  * @param SimpleXMLElement $field The XML field definition.
51  *
52  * @return string
53  *
54  * @since 12.1
55  */
56  protected function getAddColumnSQL($table, SimpleXMLElement $field)
57  {
58  return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ADD COLUMN ' . $this->getColumnSQL($field);
59  }
60 
61  /**
62  * Get the SQL syntax to add an index.
63  *
64  * @param SimpleXMLElement $field The XML index definition.
65  *
66  * @return string
67  *
68  * @since 12.1
69  */
70  protected function getAddIndexSQL(SimpleXMLElement $field)
71  {
72  return (string) $field['Query'];
73  }
74 
75  /**
76  * Get alters for table if there is a difference.
77  *
78  * @param SimpleXMLElement $structure The XML structure of the table.
79  *
80  * @return array
81  *
82  * @since 12.1
83  */
84  protected function getAlterTableSQL(SimpleXMLElement $structure)
85  {
86  $table = $this->getRealTableName($structure['name']);
87  $oldFields = $this->db->getTableColumns($table);
88  $oldKeys = $this->db->getTableKeys($table);
89  $oldSequence = $this->db->getTableSequences($table);
90  $alters = array();
91 
92  // Get the fields and keys from the XML that we are aiming for.
93  $newFields = $structure->xpath('field');
94  $newKeys = $structure->xpath('key');
95  $newSequence = $structure->xpath('sequence');
96 
97  /* Sequence section */
98  $oldSeq = $this->getSeqLookup($oldSequence);
99  $newSequenceLook = $this->getSeqLookup($newSequence);
100 
101  foreach ($newSequenceLook as $kSeqName => $vSeq)
102  {
103  if (isset($oldSeq[$kSeqName]))
104  {
105  // The field exists, check it's the same.
106  $column = $oldSeq[$kSeqName][0];
107 
108  /* For older database version that doesn't support these fields use default values */
109  if (version_compare($this->db->getVersion(), '9.1.0') < 0)
110  {
111  $column->Min_Value = '1';
112  $column->Max_Value = '9223372036854775807';
113  $column->Increment = '1';
114  $column->Cycle_option = 'NO';
115  $column->Start_Value = '1';
116  }
117 
118  // Test whether there is a change.
119  $change = ((string) $vSeq[0]['Type'] != $column->Type) || ((string) $vSeq[0]['Start_Value'] != $column->Start_Value)
120  || ((string) $vSeq[0]['Min_Value'] != $column->Min_Value) || ((string) $vSeq[0]['Max_Value'] != $column->Max_Value)
121  || ((string) $vSeq[0]['Increment'] != $column->Increment) || ((string) $vSeq[0]['Cycle_option'] != $column->Cycle_option)
122  || ((string) $vSeq[0]['Table'] != $column->Table) || ((string) $vSeq[0]['Column'] != $column->Column)
123  || ((string) $vSeq[0]['Schema'] != $column->Schema) || ((string) $vSeq[0]['Name'] != $column->Name);
124 
125  if ($change)
126  {
127  $alters[] = $this->getChangeSequenceSQL($kSeqName, $vSeq);
128  }
129 
130  // Unset this field so that what we have left are fields that need to be removed.
131  unset($oldSeq[$kSeqName]);
132  }
133  else
134  {
135  // The sequence is new
136  $alters[] = $this->getAddSequenceSQL($newSequenceLook[$kSeqName][0]);
137  }
138  }
139 
140  // Any sequences left are orphans
141  foreach ($oldSeq as $name => $column)
142  {
143  // Delete the sequence.
144  $alters[] = $this->getDropSequenceSQL($name);
145  }
146 
147  /* Field section */
148  // Loop through each field in the new structure.
149  foreach ($newFields as $field)
150  {
151  $fName = (string) $field['Field'];
152 
153  if (isset($oldFields[$fName]))
154  {
155  // The field exists, check it's the same.
156  $column = $oldFields[$fName];
157 
158  // Test whether there is a change.
159  $change = ((string) $field['Type'] != $column->Type) || ((string) $field['Null'] != $column->Null)
160  || ((string) $field['Default'] != $column->Default);
161 
162  if ($change)
163  {
164  $alters[] = $this->getChangeColumnSQL($table, $field);
165  }
166 
167  // Unset this field so that what we have left are fields that need to be removed.
168  unset($oldFields[$fName]);
169  }
170  else
171  {
172  // The field is new.
173  $alters[] = $this->getAddColumnSQL($table, $field);
174  }
175  }
176 
177  // Any columns left are orphans
178  foreach ($oldFields as $name => $column)
179  {
180  // Delete the column.
181  $alters[] = $this->getDropColumnSQL($table, $name);
182  }
183 
184  /* Index section */
185  // Get the lookups for the old and new keys
186  $oldLookup = $this->getIdxLookup($oldKeys);
187  $newLookup = $this->getIdxLookup($newKeys);
188 
189  // Loop through each key in the new structure.
190  foreach ($newLookup as $name => $keys)
191  {
192  // Check if there are keys on this field in the existing table.
193  if (isset($oldLookup[$name]))
194  {
195  $same = true;
196  $newCount = count($newLookup[$name]);
197  $oldCount = count($oldLookup[$name]);
198 
199  // There is a key on this field in the old and new tables. Are they the same?
200  if ($newCount == $oldCount)
201  {
202  for ($i = 0; $i < $newCount; $i++)
203  {
204  // Check only query field -> different query means different index
205  $same = ((string) $newLookup[$name][$i]['Query'] == $oldLookup[$name][$i]->Query);
206 
207  if (!$same)
208  {
209  // Break out of the loop. No need to check further.
210  break;
211  }
212  }
213  }
214  else
215  {
216  // Count is different, just drop and add.
217  $same = false;
218  }
219 
220  if (!$same)
221  {
222  $alters[] = $this->getDropIndexSQL($name);
223  $alters[] = (string) $newLookup[$name][0]['Query'];
224  }
225 
226  // Unset this field so that what we have left are fields that need to be removed.
227  unset($oldLookup[$name]);
228  }
229  else
230  {
231  // This is a new key.
232  $alters[] = (string) $newLookup[$name][0]['Query'];
233  }
234  }
235 
236  // Any keys left are orphans.
237  foreach ($oldLookup as $name => $keys)
238  {
239  if ($oldLookup[$name][0]->is_primary == 'TRUE')
240  {
241  $alters[] = $this->getDropPrimaryKeySQL($table, $oldLookup[$name][0]->Index);
242  }
243  else
244  {
245  $alters[] = $this->getDropIndexSQL($name);
246  }
247  }
248 
249  return $alters;
250  }
251 
252  /**
253  * Get the SQL syntax to drop a sequence.
254  *
255  * @param string $name The name of the sequence to drop.
256  *
257  * @return string
258  *
259  * @since 12.1
260  */
261  protected function getDropSequenceSQL($name)
262  {
263  return 'DROP SEQUENCE ' . $this->db->quoteName($name);
264  }
265 
266  /**
267  * Get the syntax to add a sequence.
268  *
269  * @param SimpleXMLElement $field The XML definition for the sequence.
270  *
271  * @return string
272  *
273  * @since 12.1
274  */
275  protected function getAddSequenceSQL($field)
276  {
277  /* For older database version that doesn't support these fields use default values */
278  if (version_compare($this->db->getVersion(), '9.1.0') < 0)
279  {
280  $field['Min_Value'] = '1';
281  $field['Max_Value'] = '9223372036854775807';
282  $field['Increment'] = '1';
283  $field['Cycle_option'] = 'NO';
284  $field['Start_Value'] = '1';
285  }
286 
287  return 'CREATE SEQUENCE ' . (string) $field['Name'] .
288  ' INCREMENT BY ' . (string) $field['Increment'] . ' MINVALUE ' . $field['Min_Value'] .
289  ' MAXVALUE ' . (string) $field['Max_Value'] . ' START ' . (string) $field['Start_Value'] .
290  (((string) $field['Cycle_option'] == 'NO') ? ' NO' : '') . ' CYCLE' .
291  ' OWNED BY ' . $this->db->quoteName((string) $field['Schema'] . '.' . (string) $field['Table'] . '.' . (string) $field['Column']);
292  }
293 
294  /**
295  * Get the syntax to alter a sequence.
296  *
297  * @param SimpleXMLElement $field The XML definition for the sequence.
298  *
299  * @return string
300  *
301  * @since 12.1
302  */
303  protected function getChangeSequenceSQL($field)
304  {
305  /* For older database version that doesn't support these fields use default values */
306  if (version_compare($this->db->getVersion(), '9.1.0') < 0)
307  {
308  $field['Min_Value'] = '1';
309  $field['Max_Value'] = '9223372036854775807';
310  $field['Increment'] = '1';
311  $field['Cycle_option'] = 'NO';
312  $field['Start_Value'] = '1';
313  }
314 
315  return 'ALTER SEQUENCE ' . (string) $field['Name'] .
316  ' INCREMENT BY ' . (string) $field['Increment'] . ' MINVALUE ' . (string) $field['Min_Value'] .
317  ' MAXVALUE ' . (string) $field['Max_Value'] . ' START ' . (string) $field['Start_Value'] .
318  ' OWNED BY ' . $this->db->quoteName((string) $field['Schema'] . '.' . (string) $field['Table'] . '.' . (string) $field['Column']);
319  }
320 
321  /**
322  * Get the syntax to alter a column.
323  *
324  * @param string $table The name of the database table to alter.
325  * @param SimpleXMLElement $field The XML definition for the field.
326  *
327  * @return string
328  *
329  * @since 12.1
330  */
331  protected function getChangeColumnSQL($table, SimpleXMLElement $field)
332  {
333  return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ALTER COLUMN ' . $this->db->quoteName((string) $field['Field']) . ' '
334  . $this->getAlterColumnSQL($table, $field);
335  }
336 
337  /**
338  * Get the SQL syntax for a single column that would be included in a table create statement.
339  *
340  * @param string $table The name of the database table to alter.
341  * @param SimpleXMLElement $field The XML field definition.
342  *
343  * @return string
344  *
345  * @since 12.1
346  */
347  protected function getAlterColumnSQL($table, $field)
348  {
349  // TODO Incorporate into parent class and use $this.
350  $blobs = array('text', 'smalltext', 'mediumtext', 'largetext');
351 
352  $fName = (string) $field['Field'];
353  $fType = (string) $field['Type'];
354  $fNull = (string) $field['Null'];
355  $fDefault = (isset($field['Default']) && $field['Default'] != 'NULL' ) ?
356  preg_match('/^[0-9]$/', $field['Default']) ? $field['Default'] : $this->db->quote((string) $field['Default'])
357  : null;
358 
359  $query = ' TYPE ' . $fType;
360 
361  if ($fNull == 'NO')
362  {
363  if (in_array($fType, $blobs) || $fDefault === null)
364  {
365  $query .= ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET NOT NULL' .
366  ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' DROP DEFAULT';
367  }
368  else
369  {
370  $query .= ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET NOT NULL' .
371  ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET DEFAULT ' . $fDefault;
372  }
373  }
374  else
375  {
376  if ($fDefault !== null)
377  {
378  $query .= ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' DROP NOT NULL' .
379  ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET DEFAULT ' . $fDefault;
380  }
381  }
382 
383  /* sequence was created in other function, here is associated a default value but not yet owner */
384  if (strpos($fDefault, 'nextval') !== false)
385  {
386  $query .= ";\nALTER SEQUENCE " . $this->db->quoteName($table . '_' . $fName . '_seq') . ' OWNED BY ' . $this->db->quoteName($table . '.' . $fName);
387  }
388 
389  return $query;
390  }
391 
392  /**
393  * Get the SQL syntax for a single column that would be included in a table create statement.
394  *
395  * @param SimpleXMLElement $field The XML field definition.
396  *
397  * @return string
398  *
399  * @since 12.1
400  */
401  protected function getColumnSQL(SimpleXMLElement $field)
402  {
403  // TODO Incorporate into parent class and use $this.
404  $blobs = array('text', 'smalltext', 'mediumtext', 'largetext');
405 
406  $fName = (string) $field['Field'];
407  $fType = (string) $field['Type'];
408  $fNull = (string) $field['Null'];
409  $fDefault = (isset($field['Default']) && $field['Default'] != 'NULL' ) ?
410  preg_match('/^[0-9]$/', $field['Default']) ? $field['Default'] : $this->db->quote((string) $field['Default'])
411  : null;
412 
413  /* nextval() as default value means that type field is serial */
414  if (strpos($fDefault, 'nextval') !== false)
415  {
416  $query = $this->db->quoteName($fName) . ' SERIAL';
417  }
418  else
419  {
420  $query = $this->db->quoteName($fName) . ' ' . $fType;
421 
422  if ($fNull == 'NO')
423  {
424  if (in_array($fType, $blobs) || $fDefault === null)
425  {
426  $query .= ' NOT NULL';
427  }
428  else
429  {
430  $query .= ' NOT NULL DEFAULT ' . $fDefault;
431  }
432  }
433  else
434  {
435  if ($fDefault !== null)
436  {
437  $query .= ' DEFAULT ' . $fDefault;
438  }
439  }
440  }
441 
442  return $query;
443  }
444 
445  /**
446  * Get the SQL syntax to drop an index.
447  *
448  * @param string $name The name of the key to drop.
449  *
450  * @return string
451  *
452  * @since 12.1
453  */
454  protected function getDropIndexSQL($name)
455  {
456  return 'DROP INDEX ' . $this->db->quoteName($name);
457  }
458 
459  /**
460  * Get the SQL syntax to drop a key.
461  *
462  * @param string $table The table name.
463  * @param string $name The constraint name.
464  *
465  * @return string
466  *
467  * @since 12.1
468  */
469  protected function getDropPrimaryKeySQL($table, $name)
470  {
471  return 'ALTER TABLE ONLY ' . $this->db->quoteName($table) . ' DROP CONSTRAINT ' . $this->db->quoteName($name);
472  }
473 
474  /**
475  * Get the details list of keys for a table.
476  *
477  * @param array $keys An array of objects that comprise the keys for the table.
478  *
479  * @return array The lookup array. array({key name} => array(object, ...))
480  *
481  * @since 12.1
482  * @throws Exception
483  */
484  protected function getIdxLookup($keys)
485  {
486  // First pass, create a lookup of the keys.
487  $lookup = array();
488 
489  foreach ($keys as $key)
490  {
491  if ($key instanceof SimpleXMLElement)
492  {
493  $kName = (string) $key['Index'];
494  }
495  else
496  {
497  $kName = $key->Index;
498  }
499 
500  if (empty($lookup[$kName]))
501  {
502  $lookup[$kName] = array();
503  }
504 
505  $lookup[$kName][] = $key;
506  }
507 
508  return $lookup;
509  }
510 
511  /**
512  * Get the details list of sequences for a table.
513  *
514  * @param array $sequences An array of objects that comprise the sequences for the table.
515  *
516  * @return array The lookup array. array({key name} => array(object, ...))
517  *
518  * @since 12.1
519  * @throws Exception
520  */
521  protected function getSeqLookup($sequences)
522  {
523  // First pass, create a lookup of the keys.
524  $lookup = array();
525 
526  foreach ($sequences as $seq)
527  {
528  if ($seq instanceof SimpleXMLElement)
529  {
530  $sName = (string) $seq['Name'];
531  }
532  else
533  {
534  $sName = $seq->Name;
535  }
536 
537  if (empty($lookup[$sName]))
538  {
539  $lookup[$sName] = array();
540  }
541 
542  $lookup[$sName][] = $seq;
543  }
544 
545  return $lookup;
546  }
547 }