Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.
 All Data Structures Functions Variables Pages
CreateOrUpdateExistingTableFromSchemaDefinitionArrayUtil.php
1 <?php
2  /*********************************************************************************
3  * Zurmo is a customer relationship management program developed by
4  * Zurmo, Inc. Copyright (C) 2017 Zurmo Inc.
5  *
6  * Zurmo is free software; you can redistribute it and/or modify it under
7  * the terms of the GNU Affero General Public License version 3 as published by the
8  * Free Software Foundation with the addition of the following permission added
9  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10  * IN WHICH THE COPYRIGHT IS OWNED BY ZURMO, ZURMO DISCLAIMS THE WARRANTY
11  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
12  *
13  * Zurmo is distributed in the hope that it will be useful, but WITHOUT
14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15  * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
16  * details.
17  *
18  * You should have received a copy of the GNU Affero General Public License along with
19  * this program; if not, see http://www.gnu.org/licenses or write to the Free
20  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
21  * 02110-1301 USA.
22  *
23  * You can contact Zurmo, Inc. with a mailing address at 27 North Wacker Drive
24  * Suite 370 Chicago, IL 60606. or at email address contact@zurmo.com.
25  *
26  * The interactive user interfaces in original and modified versions
27  * of this program must display Appropriate Legal Notices, as required under
28  * Section 5 of the GNU Affero General Public License version 3.
29  *
30  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31  * these Appropriate Legal Notices must retain the display of the Zurmo
32  * logo and Zurmo copyright notice. If the display of the logo is not reasonably
33  * feasible for technical reasons, the Appropriate Legal Notices must display the words
34  * "Copyright Zurmo Inc. 2017. All rights reserved".
35  ********************************************************************************/
36 
41  {
42  const CACHE_KEY = 'CreateOrUpdateExistingTableFromSchemaDefinitionArrayUtil_processedTableNames';
43 
51  public static function getTableSchema($tableName, $columns = array(), $indexes = array())
52  {
53  $schema = array(
54  $tableName => array(
55  'columns' => $columns,
56  'indexes' => $indexes,
57  )
58  );
59  return $schema;
60  }
61 
70  public static function generateOrUpdateTableBySchemaDefinition(array $schemaDefinition, & $messageLogger, $validate = true)
71  {
72  $tableName = strtolower(key($schemaDefinition));
73  if ($validate)
74  {
75  $schemaValidation = static::validateSchemaDefinition($schemaDefinition);
76  if (!$schemaValidation['isValid'])
77  {
78  $errorMessage = Zurmo::t('Core', 'Invalid Schema definition received for {{tableName}}.',
79  array('{{tableName}}' => $tableName));
80  $errorMessage .= ' ' . $schemaValidation['message'];
81  $messageLogger->addErrorMessage($errorMessage);
82  throw new CException($errorMessage);
83  }
84  }
85 
86  $columnsAndIndexes = reset($schemaDefinition);
87  if (ProcessedTableCache::isProcessed($tableName, static::CACHE_KEY) && Yii::app()->params['isFreshInstall'])
88  {
89  // we don't skip if running under updateSchema as we might have multiple requests to update same table.
90  return;
91  }
92 
93  $messageLogger->addInfoMessage(Zurmo::t('Core', 'Creating/Updating schema for {{tableName}}',
94  array('{{tableName}}' => $tableName)));
95  $existingFields = array();
96  // only check for table existence if we are not on fresh install
97  if (!isset(Yii::app()->params['isFreshInstall']) || !Yii::app()->params['isFreshInstall'])
98  {
99  try
100  {
101  $existingFields = ZurmoRedBean::$writer->getColumnsWithDetails($tableName);
102  }
103  catch (RedBean_Exception_SQL $e)
104  {
105  //42S02 - Table does not exist.
106  if (!in_array($e->getSQLState(), array('42S02')))
107  {
108  throw $e;
109  }
110  }
111  }
112 
113  if (empty($existingFields))
114  {
115  $query = static::resolveCreateTableQuery($tableName, $columnsAndIndexes);
116  }
117  else
118  {
119  $existingIndexes = ZurmoRedBean::$writer->getIndexes($tableName);
120  $query = static::resolveAlterTableQuery($tableName,
121  $columnsAndIndexes,
122  $existingFields,
123  $existingIndexes);
124  }
125  if ($query)
126  {
127  ZurmoRedBean::exec($query);
128  }
129  ProcessedTableCache::setAsProcessed($tableName, static::CACHE_KEY);
130  }
131 
136  public static function resolveProcessedTables()
137  {
138  // this is only used by tests
139  return ProcessedTableCache::resolveProcessedTableNames(static::CACHE_KEY);
140  }
141 
142  protected static function validateSchemaDefinition(array $schemaDefinition)
143  {
144  if (count($schemaDefinition) == 1)
145  {
146  $columnsAndIndexes = reset($schemaDefinition);
147  $tableName = key($schemaDefinition);
148  if (!is_string($tableName))
149  {
150  return static::returnSchemaValidationResult(
151  Zurmo::t('Core', 'Table name: {{tableName}} is not string',
152  array('{{tableName}}' => $tableName)));
153  }
154  if (count($columnsAndIndexes) != 2)
155  {
156  return static::returnSchemaValidationResult(
157  Zurmo::t('Core', 'Table schema should always contain 2 sub-definitions'));
158  }
159  if (!isset($columnsAndIndexes['columns'], $columnsAndIndexes['indexes']))
160  {
161  return static::returnSchemaValidationResult(
162  Zurmo::t('Core', 'Table schema is either missing columns or indexes'));
163  }
164  $columnValidation = static::validateColumnDefinitionsFromSchema($columnsAndIndexes['columns']);
165  if (!$columnValidation['isValid'])
166  {
167  return $columnValidation;
168  }
169  return static::validateIndexDefinitionsFromSchema($columnsAndIndexes['indexes'],
170  $columnsAndIndexes['columns']);
171  }
172  return static::returnSchemaValidationResult(
173  Zurmo::t('Core', 'More than one table definitions defined in schema'));
174  }
175 
176  protected static function validateColumnDefinitionsFromSchema(array $columns)
177  {
178  $keys = array('name', 'type', 'unsigned', 'notNull', 'collation', 'default');
179  foreach ($columns as $column)
180  {
181  if (count($column) != 6)
182  {
183  return static::returnSchemaValidationResult(
184  Zurmo::t('Core', 'Column: {{columnName}} definition should always have 6 clauses',
185  array('{{columnName}}' => $column['name'])));
186  }
187  foreach ($keys as $key)
188  {
189  if (!ArrayUtil::isValidArrayIndex($key, $column))
190  {
191  return static::returnSchemaValidationResult(
192  Zurmo::t('Core', 'Column: {{columnName}} missing {{key}} clause',
193  array('{{columnName}}' => $column['name'],
194  '{{key}}' => $key)));
195  }
196  }
197  }
198  return static::returnSchemaValidationResult(null, true);
199  }
200 
201  protected static function validateIndexDefinitionsFromSchema(array $indexes, array $columns)
202  {
203  $columnNames = RedBeanModelMemberToColumnUtil::resolveColumnNamesArrayFromColumnSchemaDefinition($columns);
204  foreach ($indexes as $indexName => $index)
205  {
206  $indexNameLength = strlen($indexName);
207  if (!is_string($indexName))
208  {
209  return static::returnSchemaValidationResult(
210  Zurmo::t('Core', 'Index Name: {{indexName}} is not a string',
211  array('{{indexName}}' => $indexName)));
212  }
213  if ($indexNameLength > 64)
214  {
215  return static::returnSchemaValidationResult(
216  Zurmo::t('Core',
217  'Index Name: {{indexName}} is {{length}} characters, {{over}} characters over limit(64).',
218  array('{{indexName}}' => $indexName,
219  '{{length}}' => $indexNameLength,
220  '{{over}}' => $indexNameLength - 64)));
221  }
222  if (count($index) != 2)
223  {
224  return static::returnSchemaValidationResult(
225  Zurmo::t('Core', 'Index: {{indexName}} does not have 2 clauses',
226  array('{{indexName}}' => $indexName)));
227  }
228  if (!ArrayUtil::isValidArrayIndex('columns', $index))
229  {
230  return static::returnSchemaValidationResult(
231  Zurmo::t('Core', 'Index: {{indexName}} does not have indexed column names',
232  array('{{indexName}}' => $indexName)));
233  }
234  if (!ArrayUtil::isValidArrayIndex('unique', $index))
235  {
236  return static::returnSchemaValidationResult(
237  Zurmo::t('Core', 'Index: {{indexName}} does not have index uniqueness clause defined',
238  array('{{indexName}}' => $indexName)));
239  }
240  if (!is_array($index['columns']))
241  {
242  return static::returnSchemaValidationResult(
243  Zurmo::t('Core', 'Index: {{indexName}} column definition is not an array',
244  array('{{indexName}}' => $indexName)));
245  }
246  foreach ($index['columns'] as $column)
247  {
248  list($column) = explode('(', $column);
249  if (!in_array($column, $columnNames))
250  {
251  return static::returnSchemaValidationResult(
252  Zurmo::t('Core', 'Index: {{indexName}} column: {{columnName}} does not exist' .
253  ' in current schema definition provided. Columns: {{columns}}',
254  array('{{indexName}}' => $indexName,
255  '{{columnName}}' => $column,
256  '{{columns}}' => print_r($columnNames, true))));
257  }
258  }
259  }
260  return static::returnSchemaValidationResult(null, true);
261  }
262 
263  protected static function resolveAlterQueryForColumn($column)
264  {
265  $columnDefinition = $column['columnDefinition'];
266  $statement = strtoupper($column['method']) . ' ';
267  $isAddition = ($column['method'] == 'add');
268  $statement .= static::resolveColumnStatementFromDefinition($columnDefinition, $isAddition);
269  return $statement;
270  }
271 
272  protected static function resolveColumnUpgradeQueries($columns, $existingFields)
273  {
274  $columnsNeedingUpgrade = array();
275  $columnUpgradeStatements = array();
276  foreach ($columns as $column)
277  {
278  if ($upgradeDefinition = static::resolveColumnUpgradeDefinition($column, $existingFields))
279  {
280  $columnsNeedingUpgrade[] = $upgradeDefinition;
281  }
282  }
283  foreach ($columnsNeedingUpgrade as $columnNeedingUpgrade)
284  {
285  $columnUpgradeStatements[] = static::resolveAlterQueryForColumn($columnNeedingUpgrade);
286  }
287  return $columnUpgradeStatements;
288  }
289 
290  protected static function doesIndexNeedUpgrade($indexMetadata, $existingIndexes)
291  {
292  $needsUpgrade = true;
293  $indexColumns = $indexMetadata['columns'];
294  sort($indexColumns);
295  // get rid of (767) and other prefixes for
296  // Begin Not Coding Standard
297  $indexColumns = array_map(function ($column)
298  {
299  $parenthesisStart = strpos($column, '(');
300  if ($parenthesisStart !== false)
301  {
302  return substr($column, 0, $parenthesisStart);
303  }
304  return $column;
305  }, $indexColumns);
306  // End Not Coding Standard
307  foreach ($existingIndexes as $existingIndexMetadata)
308  {
309  $existingIndexColumns = $existingIndexMetadata['columns'];
310  sort($existingIndexColumns);
311  if ($indexMetadata['unique'] === $existingIndexMetadata['unique'] && $indexColumns === $existingIndexColumns)
312  {
313  $needsUpgrade = false;
314  break;
315  }
316  }
317  return $needsUpgrade;
318  }
319 
320  protected static function resolveIndexUpgradeQueries($indexes, $existingIndexes)
321  {
322  $indexesNeedingUpgrade = array();
323  $indexUpgradeStatements = array();
324  foreach ($indexes as $indexName => $indexMetadata)
325  {
326  if (static::doesIndexNeedUpgrade($indexMetadata, $existingIndexes))
327  {
328  $indexesNeedingUpgrade[$indexName] = $indexMetadata;
329  }
330  }
331  foreach ($indexesNeedingUpgrade as $indexName => $indexMetadata)
332  {
333  $indexUpgradeStatements[] = static::resolveIndexStatementCreation($indexName, $indexMetadata, true);
334  }
335  return $indexUpgradeStatements;
336  }
337 
338  protected static function resolveAlterTableQuery($tableName, $columnsAndIndexes, $existingFields, $existingIndexes)
339  {
340  $upgradeStatements = array();
341  $columnUpgradeStatements = static::resolveColumnUpgradeQueries($columnsAndIndexes['columns'], $existingFields);
342  $indexUpgradeStatements = static::resolveIndexUpgradeQueries($columnsAndIndexes['indexes'], $existingIndexes);
343  $upgradeStatements = CMap::mergeArray($columnUpgradeStatements, $indexUpgradeStatements);
344  if (!empty($upgradeStatements))
345  {
346  $upgradeStatements = join(',' . PHP_EOL, $upgradeStatements); // Not Coding Standard
347  $query = "ALTER TABLE `${tableName}` " . PHP_EOL .
348  $upgradeStatements . ";";
349  return $query;
350  }
351  return false;
352  }
353 
354  protected static function resolveColumnUpgradeDefinition($column, $existingFields)
355  {
356  if (!in_array($column['name'], array_keys($existingFields)))
357  {
358  return array('columnDefinition' => $column, 'method' => 'add');
359  }
360  elseif (static::doesColumnNeedUpgrade($column, $existingFields[$column['name']]))
361  {
362  return array('columnDefinition' => $column, 'method' => 'change');
363  }
364  return null;
365  }
366 
367  protected static function doesColumnNeedUpgrade($column, $existingField)
368  {
369  if (static::isColumnTypeSameAsExistingFieldOrSmaller($column, $existingField) &&
370  static::isColumnNullSameAsExistingField($column, $existingField) &&
371  static::isColumnDefaultValueSameAsExistingField($column, $existingField))
372  {
373  return false;
374  }
375  return true;
376  }
377 
378  protected static function isColumnTypeSameAsExistingFieldOrSmaller($column, $existingField)
379  {
380  if (!static::isColumnTypeSameAsExistingField($column, $existingField))
381  {
382  // column type doesn't match field type exactly, lets try to figure out which part it doesn't match.
383  $fieldType = null;
384  $fieldLength = null;
385  $fieldUnsigned = null;
386  list($fieldType, $fieldLength, $fieldUnsigned) = static::resolveExistingFieldTypeAndLengthAndUsigned(
387  $existingField);
388  list($columnType, $columnLength) = static::resolveColumnTypeAndLength($column['type']);
389  if ($columnType == $fieldType)
390  {
391  // type is same, unsigned or length might be different.
392  return static::isColumnLengthShrinkOrConversionToUnsigned($columnLength, $fieldLength,
393  $column['unsigned'], $fieldUnsigned);
394  }
395  return static::isColumnTypeSmallerThanExistingFieldType($columnType, $fieldType);
396  }
397  return false;
398  }
399 
400  protected static function isColumnLengthShrinkOrConversionToUnsigned($columnLength, $fieldLength,
401  $columnUnsigned, $fieldUnsigned)
402  {
403  //($fieldUnsigned !== $columnUnsigned && $columnUnsigned = 'UNSIGNED') ||
404  // we do not check for signed/unsigned checks as again, we do not know if it would be safe.
405  // changing unsigned to signed might lose some data thats beyond signed range for that column
406  // changing signed to unsigned might lose some signed data;
407  if ($columnLength < $fieldLength)
408  {
409  return true;
410  }
411  return false;
412  }
413 
414  protected static function isColumnTypeSmallerThanExistingFieldType($columnType, $fieldType)
415  {
416  return false;
417  // We have intentionally kept this here but not implemented it.
418  // Design wise its bit tough decision with regard to what to allow and what not to.
419  // For example, do we allow change from a text type to mediumblob?
420  // mediumblob is smaller in size but may be user did that knowingly and he wants us to do in db.
421  // and if we don't then his binary data won't be stored as he expects it to be.
422  // What now?
423  // TODO: @Shoaibi: Low: Implement
424  $integerTypes = array_keys(DatabaseCompatibilityUtil::resolveIntegerMaxAllowedValuesByType());
425  // TODO: @Shoaibi: Low: Shouldn't these types also come from DbCU
426  $floatTypes = array('float', 'double', 'decimal');
427  $stringTypes = array('char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext');
428  $blogTypes = array('tinyblob', 'mediumblob', 'blob', 'longblob');
429  //DatabaseCompatibilityUtil::resolveIntegerMaxAllowedValuesByType
430  // type is different, check if we are switching to higher type or not.
431  // check int types
432  // check float types
433  // check string types(blob inclusive)
434  // if types from totally different domain, then what? int < float < text < blob?
435  }
436 
437  protected static function resolveExistingFieldTypeAndLengthAndUsigned(array $existingField)
438  {
439  $existingFieldType = $existingField['Type'];
440  $existingFieldUnsigned = null;
441  if (strpos($existingField['Type'], ' ') !== false)
442  {
443  list($existingFieldType, $existingFieldUnsigned) = explode(' ', $existingField['Type']);
444  }
445  list($existingFieldType, $existingFieldLength) = static::resolveColumnTypeAndLength($existingFieldType);
446  return array($existingFieldType, $existingFieldLength, $existingFieldUnsigned);
447  }
448 
449  protected static function resolveColumnTypeAndLength($columnTypeAndLength)
450  {
451  $columnType = $columnTypeAndLength;
452  $columnLength = null;
453  $openingBracePosition = strpos($columnType, '(');
454  $closingBracePosition = strpos($columnType, ')');
455  if ($openingBracePosition && $closingBracePosition)
456  {
457  $columnLength = substr($columnType, $openingBracePosition + 1,
458  $closingBracePosition - $openingBracePosition -1);
459  $columnType = strtolower(substr($columnType, 0, $openingBracePosition));
460  }
461  return array($columnType, $columnLength);
462  }
463 
464  protected static function isColumnTypeSameAsExistingField($column, $existingField)
465  {
466  $resolvedType = $column['type'];
467  if ($column['unsigned'])
468  {
469  $resolvedType .= ' ' . $column['unsigned'];
470  }
471  $resolvedType = strtolower($resolvedType);
472  $existingFieldType = strtolower($existingField['Type']);
473  return ($resolvedType == $existingFieldType);
474  }
475 
476  protected static function isColumnNullSameAsExistingField($column, $existingField)
477  {
478  $notNull = 'NOT NULL'; // Not Coding Standard
479  if ($existingField['Null'] == 'YES') // Not Coding Standard
480  {
481  $notNull = 'NULL'; // Not Coding Standard
482  }
483  return ($column['notNull'] == $notNull);
484  }
485 
486  protected static function isColumnDefaultValueSameAsExistingField($column, $existingField)
487  {
488  $default = null;
489  if ($column['default'] != 'DEFAULT NULL') // Not Coding Standard
490  {
491  $default = substr($column['default'], strpos($column['default'], 'DEFAULT '));
492  }
493  return ($default == $existingField['Default']);
494  }
495 
496  protected static function resolveCreateTableQuery($tableName, $columnsAndIndexesSchema)
497  {
498  $columns = array('`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT'); // Not Coding Standard
499  $indexes = array('PRIMARY KEY (id)');
500  foreach ($columnsAndIndexesSchema['columns'] as $column)
501  {
502  $columns[] = static::resolveColumnStatementFromDefinition($column, true);
503  }
504  foreach ($columnsAndIndexesSchema['indexes'] as $indexName => $indexMetadata)
505  {
506  $indexes[] = static::resolveIndexStatementCreation($indexName, $indexMetadata, false);
507  }
508  // PHP_EOLs below are purely for readability, sql would work just fine without it.
509  $tableMetadata = CMap::mergeArray($columns, $indexes);
510  $tableMetadata = join(',' . PHP_EOL, $tableMetadata); // Not Coding Standard
511  $query = "CREATE TABLE `${tableName}` (" . PHP_EOL .
512  $tableMetadata . PHP_EOL .
513  " ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;"; // Not Coding Standard
514  return $query;
515  }
516 
517  protected static function resolveColumnStatementFromDefinition($column, $isAddition = true)
518  {
519  extract($column);
520  if ($isAddition)
521  {
522  $clause = "`${name}` ${type} ${unsigned} ${notNull} ${collation} {$default}";
523  }
524  else
525  {
526  $clause = "`${name}` `${name}` ${type} ${unsigned} ${collation} ${notNull} {$default}";
527  }
528  return $clause;
529  }
530 
531  protected static function resolveIndexStatementCreation($indexName, $indexMetadata, $alterTable = false)
532  {
533  $clause = "KEY ${indexName} (" . join(',', $indexMetadata['columns']) . ")"; // Not Coding Standard
534  if ($indexMetadata['unique'])
535  {
536  $clause = 'UNIQUE ' . $clause;
537  }
538  if ($alterTable)
539  {
540  $clause = 'ADD ' . $clause;
541  }
542  return $clause;
543  }
544 
545  protected static function returnSchemaValidationResult($message, $isValid = false)
546  {
547  return compact('isValid', 'message');
548  }
549  }
550 ?>
static generateOrUpdateTableBySchemaDefinition(array $schemaDefinition, &$messageLogger, $validate=true)
Generated on Fri Jul 10 2020 07:10:28
Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.