1 <?php
  2 /**
  3  * @package     Joomla.Libraries
  4  * @subpackage  Schema
  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.txt
  8  */
  9 
 10 defined('JPATH_PLATFORM') or die;
 11 
 12 /**
 13  * Checks the database schema against one MySQL DDL query to see if it has been run.
 14  *
 15  * @since  2.5
 16  */
 17 class JSchemaChangeitemMysql extends JSchemaChangeitem
 18 {
 19     /**
 20      * Checks a DDL query to see if it is a known type
 21      * If yes, build a check query to see if the DDL has been run on the database.
 22      * If successful, the $msgElements, $queryType, $checkStatus and $checkQuery fields are populated.
 23      * The $msgElements contains the text to create the user message.
 24      * The $checkQuery contains the SQL query to check whether the schema change has
 25      * been run against the current database. The $queryType contains the type of
 26      * DDL query that was run (for example, CREATE_TABLE, ADD_COLUMN, CHANGE_COLUMN_TYPE, ADD_INDEX).
 27      * The $checkStatus field is set to zero if the query is created
 28      *
 29      * If not successful, $checkQuery is empty and , and $checkStatus is -1.
 30      * For example, this will happen if the current line is a non-DDL statement.
 31      *
 32      * @return void
 33      *
 34      * @since  2.5
 35      */
 36     protected function buildCheckQuery()
 37     {
 38         // Initialize fields in case we can't create a check query
 39         $this->checkStatus = -1; // change status to skipped
 40         $result = null;
 41 
 42         // Remove any newlines
 43         $this->updateQuery = str_replace("\n", '', $this->updateQuery);
 44 
 45         // Fix up extra spaces around () and in general
 46         $find = array('#((\s*)\(\s*([^)\s]+)\s*)(\))#', '#(\s)(\s*)#');
 47         $replace = array('($3)', '$1');
 48         $updateQuery = preg_replace($find, $replace, $this->updateQuery);
 49         $wordArray = explode(' ', $updateQuery);
 50 
 51         // First, make sure we have an array of at least 6 elements
 52         // if not, we can't make a check query for this one
 53         if (count($wordArray) < 6)
 54         {
 55             // Done with method
 56             return;
 57         }
 58 
 59         // We can only make check queries for alter table and create table queries
 60         $command = strtoupper($wordArray[0] . ' ' . $wordArray[1]);
 61 
 62         // Check for special update statement to reset utf8mb4 conversion status
 63         if (($command === 'UPDATE `#__UTF8_CONVERSION`'
 64             || $command === 'UPDATE #__UTF8_CONVERSION')
 65             && strtoupper($wordArray[2]) === 'SET'
 66             && strtolower(substr(str_replace('`', '', $wordArray[3]), 0, 9)) === 'converted')
 67         {
 68             // Statement is special statement to reset conversion status
 69             $this->queryType = 'UTF8CNV';
 70 
 71             // Done with method
 72             return;
 73         }
 74 
 75         if ($command === 'ALTER TABLE')
 76         {
 77             $alterCommand = strtoupper($wordArray[3] . ' ' . $wordArray[4]);
 78 
 79             if ($alterCommand === 'ADD COLUMN')
 80             {
 81                 $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE field = ' . $this->fixQuote($wordArray[5]);
 82                 $this->queryType = 'ADD_COLUMN';
 83                 $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[5]));
 84             }
 85             elseif ($alterCommand === 'ADD INDEX' || $alterCommand === 'ADD KEY')
 86             {
 87                 if ($pos = strpos($wordArray[5], '('))
 88                 {
 89                     $index = $this->fixQuote(substr($wordArray[5], 0, $pos));
 90                 }
 91                 else
 92                 {
 93                     $index = $this->fixQuote($wordArray[5]);
 94                 }
 95 
 96                 $result = 'SHOW INDEXES IN ' . $wordArray[2] . ' WHERE Key_name = ' . $index;
 97                 $this->queryType = 'ADD_INDEX';
 98                 $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
 99             }
100             elseif ($alterCommand === 'ADD UNIQUE')
101             {
102                 $idxIndexName = 5;
103 
104                 if (isset($wordArray[6]))
105                 {
106                     $addCmdCheck = strtoupper($wordArray[5]);
107 
108                     if ($addCmdCheck === 'INDEX' || $addCmdCheck === 'KEY')
109                     {
110                         $idxIndexName = 6;
111                     }
112                 }
113 
114                 if ($pos = strpos($wordArray[$idxIndexName], '('))
115                 {
116                     $index = $this->fixQuote(substr($wordArray[$idxIndexName], 0, $pos));
117                 }
118                 else
119                 {
120                     $index = $this->fixQuote($wordArray[$idxIndexName]);
121                 }
122 
123                 $result = 'SHOW INDEXES IN ' . $wordArray[2] . ' WHERE Key_name = ' . $index;
124                 $this->queryType = 'ADD_INDEX';
125                 $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
126             }
127             elseif ($alterCommand === 'DROP INDEX' || $alterCommand === 'DROP KEY')
128             {
129                 $index = $this->fixQuote($wordArray[5]);
130                 $result = 'SHOW INDEXES IN ' . $wordArray[2] . ' WHERE Key_name = ' . $index;
131                 $this->queryType = 'DROP_INDEX';
132                 $this->checkQueryExpected = 0;
133                 $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
134             }
135             elseif ($alterCommand === 'DROP COLUMN')
136             {
137                 $index = $this->fixQuote($wordArray[5]);
138                 $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE Field = ' . $index;
139                 $this->queryType = 'DROP_COLUMN';
140                 $this->checkQueryExpected = 0;
141                 $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
142             }
143             elseif (strtoupper($wordArray[3]) === 'MODIFY')
144             {
145                 // Kludge to fix problem with "integer unsigned"
146                 $type = $wordArray[5];
147 
148                 if (isset($wordArray[6]))
149                 {
150                     $type = $this->fixInteger($wordArray[5], $wordArray[6]);
151                 }
152 
153                 /**
154                  * When we made the UTF8MB4 conversion then text becomes medium text - so loosen the checks to these two types
155                  * otherwise (for example) the profile fields profile_value check fails - see https://github.com/joomla/joomla-cms/issues/9258
156                  */
157                 $typeCheck = $this->fixUtf8mb4TypeChecks($type);
158 
159                 $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE field = ' . $this->fixQuote($wordArray[4])
160                     . ' AND ' . $typeCheck;
161                 $this->queryType = 'CHANGE_COLUMN_TYPE';
162                 $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[4]), $type);
163             }
164             elseif (strtoupper($wordArray[3]) === 'CHANGE')
165             {
166                 // Kludge to fix problem with "integer unsigned"
167                 $type = $wordArray[6];
168 
169                 if (isset($wordArray[7]))
170                 {
171                     $type = $this->fixInteger($wordArray[6], $wordArray[7]);
172                 }
173 
174                 /**
175                  * When we made the UTF8MB4 conversion then text becomes medium text - so loosen the checks to these two types
176                  * otherwise (for example) the profile fields profile_value check fails - see https://github.com/joomla/joomla-cms/issues/9258
177                  */
178                 $typeCheck = $this->fixUtf8mb4TypeChecks($type);
179 
180                 $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE field = ' . $this->fixQuote($wordArray[5])
181                     . ' AND ' . $typeCheck;
182                 $this->queryType = 'CHANGE_COLUMN_TYPE';
183                 $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[5]), $type);
184             }
185         }
186 
187         if ($command === 'CREATE TABLE')
188         {
189             if (strtoupper($wordArray[2] . $wordArray[3] . $wordArray[4]) === 'IFNOTEXISTS')
190             {
191                 $table = $wordArray[5];
192             }
193             else
194             {
195                 $table = $wordArray[2];
196             }
197 
198             $result = 'SHOW TABLES LIKE ' . $this->fixQuote($table);
199             $this->queryType = 'CREATE_TABLE';
200             $this->msgElements = array($this->fixQuote($table));
201         }
202 
203         // Set fields based on results
204         if ($this->checkQuery = $result)
205         {
206             // Unchecked status
207             $this->checkStatus = 0;
208         }
209         else
210         {
211             // Skipped
212             $this->checkStatus = -1;
213         }
214     }
215 
216     /**
217      * Fix up integer. Fixes problem with MySQL integer descriptions.
218      * If you change a column to "integer unsigned" it shows
219      * as "int(10) unsigned" in the check query.
220      *
221      * @param   string  $type1  the column type
222      * @param   string  $type2  the column attributes
223      *
224      * @return  string  The original or changed column type.
225      *
226      * @since   2.5
227      */
228     private function fixInteger($type1, $type2)
229     {
230         $result = $type1;
231 
232         if (strtolower($type1) === 'integer' && strtolower(substr($type2, 0, 8)) === 'unsigned')
233         {
234             $result = 'int(10) unsigned';
235         }
236         elseif (strtolower(substr($type2, 0, 8)) === 'unsigned')
237         {
238             $result = $type1 . ' unsigned';
239         }
240 
241         return $result;
242     }
243 
244     /**
245      * Fixes up a string for inclusion in a query.
246      * Replaces name quote character with normal quote for literal.
247      * Drops trailing semicolon. Injects the database prefix.
248      *
249      * @param   string  $string  The input string to be cleaned up.
250      *
251      * @return  string  The modified string.
252      *
253      * @since   2.5
254      */
255     private function fixQuote($string)
256     {
257         $string = str_replace('`', '', $string);
258         $string = str_replace(';', '', $string);
259         $string = str_replace('#__', $this->db->getPrefix(), $string);
260 
261         return $this->db->quote($string);
262     }
263 
264     /**
265      * Make check query for column changes/modifications tolerant
266      * for automatic type changes of text columns, e.g. from TEXT
267      * to MEDIUMTEXT, after comnversion from utf8 to utf8mb4
268      *
269      * @param   string  $type  The column type found in the update query
270      *
271      * @return  string  The condition for type check in the check query
272      *
273      * @since   3.5
274      */
275     private function fixUtf8mb4TypeChecks($type)
276     {
277         $fixedType = str_replace(';', '', $type);
278 
279         if ($this->db->hasUTF8mb4Support())
280         {
281             $uType = strtoupper($fixedType);
282 
283             if ($uType === 'TINYTEXT')
284             {
285                 $typeCheck = 'type IN (' . $this->db->quote('TINYTEXT') . ',' . $this->db->quote('TEXT') . ')';
286             }
287             elseif ($uType === 'TEXT')
288             {
289                 $typeCheck = 'type IN (' . $this->db->quote('TEXT') . ',' . $this->db->quote('MEDIUMTEXT') . ')';
290             }
291             elseif ($uType === 'MEDIUMTEXT')
292             {
293                 $typeCheck = 'type IN (' . $this->db->quote('MEDIUMTEXT') . ',' . $this->db->quote('LONGTEXT') . ')';
294             }
295             else
296             {
297                 $typeCheck = 'type = ' . $this->db->quote($fixedType);
298             }
299         }
300         else
301         {
302             $typeCheck = 'type = ' . $this->db->quote($fixedType);
303         }
304 
305         return $typeCheck;
306     }
307 }
308 

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.