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

Joomla! Framework TM API documentation generated by ApiGen 2.8.0
Joomla!® and Joomla! Framework™ are trademarks of Open Source Matters, Inc. in the United States and other countries.