1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Core
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: /**
 28:  * Modifier of queries, developed for backwards compatibility on MySQL,
 29:  * while creating foreign keys
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Core
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Core_Model_Resource_Setup_Query_Modifier
 36: {
 37:     /**
 38:      * MySQL adapter instance
 39:      *
 40:      * @var Varien_Db_Adapter_Pdo_Mysql
 41:      */
 42:     protected $_adapter;
 43: 
 44:     /**
 45:      * Types of column we process for foreign keys
 46:      *
 47:      * @var array
 48:      */
 49:     protected $_processedTypes = array('tinyint', 'smallint', 'mediumint', 'int', 'longint');
 50: 
 51:     /**
 52:      * Inits query modifier
 53:      *
 54:      * @param $adapter Varien_Db_Adapter_Pdo_Mysql
 55:      * @return void
 56:      */
 57:     public function __construct($args)
 58:     {
 59:         $this->_adapter = $args[0];
 60:     }
 61: 
 62:     /**
 63:      * Returns column definition from CREATE TABLE sql
 64:      *
 65:      * @param string $sql
 66:      * @param string $column
 67:      * @return array
 68:      */
 69:     protected function _getColumnDefinitionFromSql($sql, $column)
 70:     {
 71:         $result = null;
 72:         foreach ($this->_processedTypes as $type) {
 73:             $pattern = '/\s([^\s]+)\s+' . $type . '[^\s]*(\s+[^,]+)/i';
 74:             if (!preg_match_all($pattern, $sql, $matches, PREG_SET_ORDER)) {
 75:                 continue;
 76:             }
 77:             foreach ($matches as $match) {
 78:                 $gotColumn = $this->_prepareIdentifier($match[1]);
 79:                 if ($gotColumn != $column) {
 80:                     continue;
 81:                 }
 82: 
 83:                 $definition = $match[2];
 84:                 $unsigned = preg_match('/\sUNSIGNED/i', $definition) > 0;
 85: 
 86:                 $result = array(
 87:                     'type' => $type,
 88:                     'unsigned' => $unsigned
 89:                 );
 90:                 break;
 91:             }
 92:             if ($result) {
 93:                 break;
 94:             }
 95:         }
 96: 
 97:         return $result;
 98:     }
 99: 
100:     /**
101:      * Replaces first occurence of $needle in a $haystack
102:      *
103:      * @param string $haystack
104:      * @param string $needle
105:      * @param array $replacement
106:      * @param bool $caseInsensitive
107:      * @return string
108:      */
109:     protected function _firstReplace($haystack, $needle, $replacement, $caseInsensitive = false)
110:     {
111:         $pos = $caseInsensitive ? stripos($haystack, $needle) : strpos($haystack, $needle);
112:         if ($pos === false) {
113:             return $haystack;
114:         }
115: 
116:         return substr($haystack, 0, $pos) . $replacement . substr($haystack, $pos + strlen($needle));
117:     }
118: 
119:     /**
120:      * Fixes column definition in CREATE TABLE sql to match defintion of column it's set to
121:      *
122:      * @param string $sql
123:      * @param string $column
124:      * @param array $refColumnDefinition
125:      * @return Mage_Core_Model_Resource_Setup_Query_Modifier
126:      */
127:     protected function _fixColumnDefinitionInSql(&$sql, $column, $refColumnDefinition)
128:     {
129:         $pos = stripos($sql, "`{$column}`"); // First try to find column directly recorded
130:         if ($pos === false) {
131:             $pattern = '/[`\s]' . preg_quote($column, '/') . '[`\s]/i';
132:             if (!preg_match($pattern, $sql, $matches)) {
133:                 return $this;
134:             }
135: 
136:             $columnEntry = $matches[0];
137:             $pos = strpos($sql, $columnEntry);
138:             if ($pos === false) {
139:                 return $this;
140:             }
141:         }
142: 
143:         $startSql = substr($sql, 0, $pos);
144:         $restSql = substr($sql, $pos);
145: 
146:         // Column type definition
147:         $columnDefinition = $this->_getColumnDefinitionFromSql($sql, $column);
148:         if (!$columnDefinition) {
149:             return $this;
150:         }
151: 
152:         // Find pattern for type defintion
153:         $pattern = '/\s*([^\s]+)\s+(' . $columnDefinition['type'] . '[^\s]*)\s+([^,]+)/i';
154:         if (!preg_match($pattern, $restSql, $matches)) {
155:             return $this;
156:         }
157: 
158:         // Replace defined type with needed type
159:         $typeDefined = $matches[2];
160:         $typeNeeded = $refColumnDefinition['type'];
161:         if ($refColumnDefinition['unsigned'] && !$columnDefinition['unsigned']) {
162:             $typeNeeded .= ' unsigned';
163:         }
164: 
165:         $restSql = $this->_firstReplace($restSql, $typeDefined, $typeNeeded);
166: 
167:         if (!$refColumnDefinition['unsigned'] && ($columnDefinition['unsigned'])) {
168:             $restSql = $this->_firstReplace($restSql, 'unsigned', '', true);
169:         }
170: 
171:         // Compose SQL back
172:         $sql = $startSql . $restSql;
173: 
174:         return $this;
175:     }
176: 
177:     /**
178:      * Fixes column definition in already existing table, so outgoing foreign key will be successfully set
179:      *
180:      * @param string $sql
181:      * @param string $column
182:      * @param array $refColumnDefinition
183:      * @return Mage_Core_Model_Resource_Setup_Query_Modifier
184:      */
185:     protected function _fixColumnDefinitionInTable($table, $column, $refColumnDefinition)
186:     {
187:         $description = $this->_adapter->fetchAll('DESCRIBE ' . $table);
188:         foreach ($description as $columnData) {
189:             $columnName = $this->_prepareIdentifier($columnData['Field']);
190:             if ($columnName != $column) {
191:                 continue;
192:             }
193:             $definition = $refColumnDefinition['type'];
194:             if ($refColumnDefinition['unsigned']) {
195:                 $definition .= ' UNSIGNED';
196:             }
197:             if ($columnData['Null'] == 'YES') {
198:                 $definition .= ' NULL';
199:             } else {
200:                 $definition .= ' NOT NULL';
201:             }
202:             if ($columnData['Default']) {
203:                 $definition .= ' DEFAULT ' . $columnData['Default'];
204:             }
205:             if ($columnData['Extra']) {
206:                 $definition .= ' ' . $columnData['Extra'];
207:             }
208: 
209:             $query = 'ALTER TABLE ' . $table . ' MODIFY COLUMN ' . $column . ' ' . $definition;
210:             $this->_adapter->query($query);
211:         }
212:         return $this;
213:     }
214: 
215:     /**
216:      * Returns column definition from already existing table
217:      *
218:      * @param string $sql
219:      * @param string $column
220:      * @return array|null
221:      */
222:     protected function _getColumnDefinitionFromTable($table, $column)
223:     {
224:         $description = $this->_adapter->describeTable($table);
225:         if (!isset($description[$column])) {
226:             return null;
227:         }
228: 
229:         return array(
230:             'type' => $this->_prepareIdentifier($description[$column]['DATA_TYPE']),
231:             'unsigned' => (bool) $description[$column]['UNSIGNED']
232:         );
233:     }
234: 
235:     /**
236:      * Returns whether table exists
237:      *
238:      * @param string $table
239:      * @return bool
240:      */
241:     protected function _tableExists($table)
242:     {
243:         $rows = $this->_adapter->fetchAll('SHOW TABLES');
244:         foreach ($rows as $row) {
245:             $tableFound = strtolower(current($row));
246:             if ($table == $tableFound) {
247:                 return true;
248:             }
249:         }
250:         return false;
251:     }
252: 
253:     /**
254:      * Trims and lowercases identifier, to make common view of all of them
255:      *
256:      * @param string $identifier
257:      * @return string
258:      */
259:     protected function _prepareIdentifier($identifier)
260:     {
261:         return strtolower(trim($identifier, "`\n\r\t"));
262:     }
263: 
264:     /**
265:      * Processes query, modifies targeted columns to fit foreign keys restrictions
266:      *
267:      * @param string $sql
268:      * @param array $bind
269:      * @return Mage_Core_Model_Resource_Setup_Query_Modifier
270:      */
271:     public function processQuery(&$sql, &$bind)
272:     {
273:         // Quick test to skip queries without foreign keys
274:         if (!stripos($sql, 'foreign')) {
275:             return $this;
276:         }
277: 
278:         // Find foreign keys set
279:         $pattern = '/CONSTRAINT\s+[^\s]+\s+FOREIGN\s+KEY[^(]+\\(([^),]+)\\)\s+REFERENCES\s+([^\s.]+)\s+\\(([^)]+)\\)/i';
280:         if (!preg_match_all($pattern, $sql, $matchesFk, PREG_SET_ORDER)) {
281:             return $this;
282:         }
283: 
284:         // Get current table name
285:         if (!preg_match('/\s*(CREATE|ALTER)\s+TABLE\s+([^\s.]+)/i', $sql, $match)) {
286:             return $this;
287:         }
288: 
289:         $operation = $this->_prepareIdentifier($match[1]);
290:         $table = $this->_prepareIdentifier($match[2]);
291: 
292:         // Process all
293:         foreach ($matchesFk as $match) {
294:             $column = $this->_prepareIdentifier($match[1]);
295:             $refTable = $this->_prepareIdentifier($match[2]);
296:             $refColumn = $this->_prepareIdentifier($match[3]);
297: 
298:             // Check tables existance
299:             if (($operation != 'create') && !($this->_tableExists($table))) {
300:                 continue;
301:             }
302:             if (!$this->_tableExists($refTable)) {
303:                 continue;
304:             }
305: 
306:             // Self references are out of our fix scope
307:             if ($refTable == $table) {
308:                 continue;
309:             }
310: 
311:             // Extract column type
312:             if ($operation == 'create') {
313:                 $columnDefinition = $this->_getColumnDefinitionFromSql($sql, $column);
314:             } else {
315:                 $columnDefinition = $this->_getColumnDefinitionFromTable($table, $column);
316:             }
317: 
318:             // We fix only int columns
319:             if (!$columnDefinition || !in_array($columnDefinition['type'], $this->_processedTypes)) {
320:                 continue;
321:             }
322: 
323:             // Extract referenced column type
324:             $refColumnDefinition = $this->_getColumnDefinitionFromTable($refTable, $refColumn);
325:             if (!$refColumnDefinition) {
326:                 continue;
327:             }
328: 
329:             // We fix only int columns
330:             if (!$refColumnDefinition || !in_array($refColumnDefinition['type'], $this->_processedTypes)) {
331:                 continue;
332:             }
333: 
334:             // Whether we need to fix
335:             if ($refColumnDefinition == $columnDefinition) {
336:                 continue;
337:             }
338: 
339:             // Fix column to be the same type as referenced one
340:             if ($operation == 'create') {
341:                 $this->_fixColumnDefinitionInSql($sql, $column, $refColumnDefinition);
342:             } else {
343:                 $this->_fixColumnDefinitionInTable($table, $column, $refColumnDefinition);
344:             }
345:         }
346: 
347:         return $this;
348:     }
349: }
350: