Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.
 All Data Structures Functions Variables Pages
ImportDatabaseUtil.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 
40  abstract class ImportDatabaseUtil
41  {
42  const ALLOWED_ENCODINGS_FOR_CONVERSION = 'UTF-8, UTF-7, ASCII, CP1252, ISO-8859-1, EUC-JP, SJIS, eucJP-win, SJIS-win, JIS, ISO-2022-JP';
43 
44  const MAX_IMPORT_COLUMN_COUNT = 99;
45 
46  const BULK_INSERT_COUNT = 500;
47 
48  protected static $temporaryFileName = null;
49 
50  protected static $importDataRowCount = null;
51 
62  public static function makeDatabaseTableByFileHandleAndTableName($fileHandle, $tableName, $delimiter = ',', // Not Coding Standard
63  $enclosure = "'", $firstRowIsHeaderRow = false)
64  {
65  assert('gettype($fileHandle) == "resource"');
66  assert('is_string($tableName)');
67  assert('$tableName == strtolower($tableName)');
68  assert('$delimiter != null && is_string($delimiter)');
69  assert('$enclosure != null && is_string($enclosure)');
70  static::createTableByTableNameAndImportCsvIntoTable($fileHandle, $tableName, $delimiter,
71  $enclosure, $firstRowIsHeaderRow);
72  return true;
73  }
74 
75  protected static function createTableByTableNameAndImportCsvIntoTable($fileHandle, $tableName, $delimiter,
76  $enclosure, $firstRowIsHeaderRow)
77  {
78  $maxLengths = array();
79  $columns = array();
80  $importArray = array();
81  static::determineMaximumColumnLengthAndPopulateImportArray($fileHandle, $delimiter, $enclosure,
82  $maxLengths, $importArray, $firstRowIsHeaderRow);
83  if (!empty($maxLengths))
84  {
85  $columnCount = static::resolveColumnsByMaximumColumnLengths($maxLengths, $columns);
86  static::safeValidateColumnCountAndCreateTable($tableName, $columnCount, $columns);
87  if (static::databaseSupportsLoadLocalInFile())
88  {
89  array_walk($importArray, 'static::prependEmptyStringToAllImportRows');
90  static::convertImportArrayAndWriteToTemporaryFile($importArray, $firstRowIsHeaderRow);
91  static::loadDataFromTemporaryFileToTable($tableName);
92  }
93  else
94  {
95  $columnNames = RedBeanModelMemberToColumnUtil::resolveColumnNamesArrayFromColumnSchemaDefinition($columns);
96  static::importArrayIntoTable($tableName, $importArray, $columnNames);
97  }
98  }
99  else
100  {
101  // we need this here so even is there are nothing else to do, we clear the table, else few tests would fail.
102  ZurmoRedBean::$writer->dropTableByTableName($tableName);
103  }
104  }
105 
115  protected static function determineMaximumColumnLengthAndPopulateImportArray($fileHandle, $delimiter,
116  $enclosure, array & $maxLengths,
117  array & $importArray,
118  $firstRowIsHeaderRow)
119  {
120  rewind($fileHandle);
121  while (($data = fgetcsv($fileHandle, 0, $delimiter, $enclosure)) !== false)
122  {
123  if (count($data) > 1 || (count($data) == 1 && trim($data['0']) != ''))
124  {
125  $importData = array();
126  foreach ($data as $k => $v)
127  {
128  static::convertCurrentValueToUtf8AndPopulateImportDataArray($k, $v, $importData);
129  static::updateMaxLengthForKey($k, $v, $maxLengths);
130  }
131  $importArray[] = $importData;
132  }
133  }
134  }
135 
142  protected static function convertCurrentValueToUtf8AndPopulateImportDataArray($k, & $v, array & $importData)
143  {
144  // Convert characterset to UTF-8
145  $currentCharset = mb_detect_encoding($v, static::ALLOWED_ENCODINGS_FOR_CONVERSION);
146  if (!empty($currentCharset) && $currentCharset != "UTF-8")
147  {
148  $v = mb_convert_encoding($v, "UTF-8", static::ALLOWED_ENCODINGS_FOR_CONVERSION);
149  }
150  $importData[$k] = $v;
151  }
152 
159  protected static function padEmptyKeys(array & $sourceArray, $newSize, $value = 1)
160  {
161  if ($newSize > count($sourceArray))
162  {
163  // we are either at start or at a row that has more columns than before ones.
164  $sourceArray = array_pad($sourceArray, $newSize, $value);
165  }
166  }
167 
174  protected static function updateMaxLengthForKey($k, $v, array & $maxLengths)
175  {
176  $currentValueLength = strlen($v);
177  if (!isset($maxLengths[$k]) || $maxLengths[$k] < $currentValueLength)
178  {
179  $maxLengths[$k] = $currentValueLength;
180  }
181  }
182 
189  protected static function unsetEmptyKeysForKeyIfValueNotEmpty($k, $v, array & $emptyKeys)
190  {
191  if (($v !== null || $v !== false || strlen($v) != 0) && // value is not empty
192  isset($emptyKeys[$k])) // and we already have it as empty key
193  {
194  unset($emptyKeys[$k]);
195  }
196  }
197 
205  protected static function unsetEmptyKeysFromMaxLengthAndImportArray($clearEmptyColumns, array $emptyKeys,
206  & $maxLengths, array & $importArray)
207  {
208  if ($clearEmptyColumns && !empty($emptyKeys))
209  {
210  foreach ($emptyKeys as $emptyKey => $notUsed)
211  {
212  unset($maxLengths[$emptyKey]);
213  foreach ($importArray as $importRow)
214  {
215  unset($importRow[$emptyKey]);
216  }
217  }
218  }
219  }
220 
226  protected static function prependEmptyStringToAllImportRows(array &$val)
227  {
228  if (!is_array($val))
229  {
230  throw new NotSupportedException();
231  }
232  array_unshift($val, '');
233  }
234 
240  public static function databaseSupportsLoadLocalInFile()
241  {
242  list($databaseType, $databaseHostname, $databasePort) = array_values(
243  RedBeanDatabase::getDatabaseInfoFromDsnString(Yii::app()->db->connectionString));
244  return InstallUtil::checkDatabaseLoadLocalInFile($databaseType,
245  $databaseHostname,
246  Yii::app()->db->username,
247  Yii::app()->db->password,
248  $databasePort);
249  }
250 
257  protected static function resolveColumnsByMaximumColumnLengths(array $maxLengths, array & $columns)
258  {
259  $columnCount = 0;
260  foreach ($maxLengths as $currentValueLength)
261  {
262  $columnName = 'column_' . $columnCount;
263  $type = null;
264  $length = null;
265  RedBeanModelMemberRulesToColumnAdapter::resolveStringTypeAndLengthByMaxLength($type, $length,
266  $currentValueLength);
267  $columns[] = RedBeanModelMemberToColumnUtil::resolveColumnMetadataByHintType($columnName, $type,
268  $length);
269  $columnCount++;
270  }
271  return $columnCount;
272  }
273 
281  protected static function safeValidateColumnCountAndCreateTable($tableName, $columnCount, array $columns)
282  {
283  if ($columnCount > 0)
284  {
285  if ($columnCount > static::MAX_IMPORT_COLUMN_COUNT)
286  {
288  Zurmo::t('ImportModule', 'The file has too many columns. The maximum is 100'));
289  }
290  static::createTableByTableNameAndImportColumns($tableName, $columns);
291  }
292  }
293 
300  protected static function convertImportArrayAndWriteToTemporaryFile(array $importArray, $firstRowIsHeaderRow)
301  {
302  static::$temporaryFileName = tempnam(sys_get_temp_dir(), 'csv_import_');
303  static::$importDataRowCount = count($importArray);
304  $csv = static::convertImportArrayToCsv($importArray, $firstRowIsHeaderRow);
305  if ($csv === null || strlen(trim($csv)) === 0)
306  {
307  throw new NotSupportedException("Unable to convert importArray to csv for writing to {${static::$importDataRowCount}}");
308  }
309  static::writeCsvToTemporaryFile($csv);
310  static::fixPermissionsOnTemporaryFile();
311  }
312 
319  protected static function convertImportArrayToCsv(array $importArray, $firstRowIsHeaderRow)
320  {
321  $headerArray = array();
322  if ($firstRowIsHeaderRow)
323  {
324  $headerArray = array_shift($importArray);
325  }
326  $csv = ExportItemToCsvFileUtil::export($importArray, $headerArray, '', false, true, true);
327  return $csv;
328  }
329 
335  protected static function writeCsvToTemporaryFile($csv)
336  {
337  $temporaryFileHandle = fopen(static::$temporaryFileName, 'wb');
338  $bytesWritten = fwrite($temporaryFileHandle, $csv);
339  fclose($temporaryFileHandle);
340  if ($bytesWritten === false)
341  {
342  throw new NotSupportedException("Unable to write to {${static::$importDataRowCount}}");
343  }
344  }
345 
350  protected static function fixPermissionsOnTemporaryFile()
351  {
352  // to ensure that mysql can read this file.
353  if (!chmod(static::$temporaryFileName, 0777))
354  {
355  throw new NotSupportedException("Unable to fix permissions on temporary import file");
356  }
357  }
358 
364  protected static function loadDataFromTemporaryFileToTable($tableName)
365  {
366  $queryParameters = array(
367  'characterSet' => 'utf8', // 'binary' would also work, actually that is
368  // kind of better as we already converted data to utf8
369  'delimiter' => ExportItemToCsvFileUtil::DEFAULT_DELIMITER,
370  'enclosure' => ExportItemToCsvFileUtil::DEFAULT_ENCLOSURE,
371  'temporaryFileName' => static::$temporaryFileName,
372  );
373  $tableName = ZurmoRedBean::$writer->safeTable($tableName);
374  $query = "LOAD DATA LOCAL INFILE :temporaryFileName REPLACE INTO TABLE ${tableName}";
375  $query .= " CHARACTER SET :characterSet FIELDS TERMINATED BY :delimiter";
376  $query .= " ENCLOSED BY :enclosure";
377  try
378  {
379  $affectedRows = ZurmoRedBean::exec($query, $queryParameters);
380  }
381  catch (RedBean_Exception_SQL $e)
382  {
383  if (strpos($e->getMessage(), ' 1148 ') !== false)
384  {
385  $e = new NotSupportedException("Please enable LOCAL INFILE in mysql config. Add local-infile=1 to [mysqld] and [mysql] sections."); // Not Coding Standard
386  }
387  throw $e;
388  }
389  if (static::$importDataRowCount != $affectedRows)
390  {
391  throw new NotSupportedException("Unable to import all data: ${affectedRows}/{${static::$importDataRowCount}}");
392  }
393  unlink(static::$temporaryFileName);
394  }
395 
403  protected static function importArrayIntoTable($tableName, array & $importArray, array $columnNames)
404  {
405  assert('is_string($tableName)');
406  assert('$tableName == strtolower($tableName)');
407  assert('is_array($columnNames)');
408  assert('is_array($importArray)');
409  do
410  {
411  $importSubset = ArrayUtil::chopArray($importArray, static::BULK_INSERT_COUNT);
412  // bulkInsert needs every subarray to have same number of columns as columnNames, pad with empty strings
413  static::padSubArrays($importSubset, count($columnNames));
414  DatabaseCompatibilityUtil::bulkInsert($tableName, $importSubset, $columnNames, static::BULK_INSERT_COUNT, true);
415  } while (count($importSubset) > 0);
416  }
417 
424  protected static function padSubArrays(array & $array, $newSize, $value = '')
425  {
426  $paddedArray = array();
427  foreach ($array as $key => $subArray)
428  {
429  $subArray = array_pad($subArray, $newSize, $value);
430  $paddedArray[$key] = $subArray;
431  }
432  if (!empty($paddedArray))
433  {
434  $array = $paddedArray;
435  }
436  }
437 
445  public static function getSubset($tableName, $where = null, $count = null, $offset = null)
446  {
447  assert('is_string($tableName)');
448  assert('$offset === null || is_integer($offset) && $offset >= 0');
449  assert('$offset === null || is_integer($count) && $count >= 1');
450  $sql = 'select id from ' . $tableName;
451  if ($where != null)
452  {
453  $sql .= ' where ' . $where;
454  }
455  if ($count !== null)
456  {
457  $sql .= " limit $count";
458  }
459  if ($offset !== null)
460  {
461  $sql .= " offset $offset";
462  }
463  $ids = ZurmoRedBean::getCol($sql);
464  return ZurmoRedBean::batch ($tableName, $ids);
465  }
466 
472  public static function getCount($tableName, $where = null)
473  {
474  if ($where === null)
475  {
476  return ZurmoRedBean::$writer->count($tableName);
477  }
478  else
479  {
480  $sql = 'select count(id) count from ' . $tableName;
481  $sql .= ' where ' . $where;
482 
483  $count = ZurmoRedBean::getCell($sql);
484  if ($count === null)
485  {
486  $count = 0;
487  }
488  return $count;
489  }
490  }
491 
500  public static function updateRowAfterProcessing($tableName, $id, $status, $serializedMessages)
501  {
502  assert('is_string($tableName)');
503  assert('is_int($id)');
504  assert('is_int($status)');
505  assert('is_string($serializedMessages) || $serializedMessages == null');
506 
507  $bean = ZurmoRedBean::findOne($tableName, "id = :id", array('id' => $id));
508  if ($bean == null)
509  {
510  throw new NotFoundException();
511  }
512  $bean->status = $status;
513  $bean->serializedMessages = $serializedMessages;
514  $storedId = ZurmoRedBean::store($bean);
515  if ($storedId != $id)
516  {
517  throw new FailedToSaveModelException("Id of updated record does not match the id used in finding it.");
518  }
519  }
520 
530  public static function updateRowValue($tableName, $id, $attribute, $newValue)
531  {
532  assert('is_string($tableName)');
533  assert('is_int($id)');
534  assert('is_string($attribute)');
535  assert('is_string($newValue) || $newValue == null');
536 
537  extract(static::geColumnData($tableName, $attribute));
538  $newDbType = null;
539  $newDbLength = null;
540  RedBeanModelMemberRulesToColumnAdapter::resolveStringTypeAndLengthByMaxLength($newDbType, $newDbLength, strlen($newValue));
541  $update = false;
542  if ($newDbType == 'string')
543  {
544  if ($columnType == 'varchar' && $newDbLength > $columnLength)
545  {
546  $update = true;
547  }
548  }
549  elseif ($newDbType != $columnType)
550  {
551  if ($newDbType == 'longtext')
552  {
553  $update = true;
554  }
555  elseif ($newDbType == 'text' && $columnType == 'varchar')
556  {
557  $update = true;
558  }
559  }
560  if ($update)
561  {
562  $column = RedBeanModelMemberToColumnUtil::resolveColumnMetadataByHintType($attribute, $newDbType, $newDbLength);
564  $messageLogger = new ImportMessageLogger();
566  }
567  $bean = ZurmoRedBean::findOne($tableName, "id = :id", array('id' => $id));
568  if ($bean == null)
569  {
570  throw new NotFoundException();
571  }
572  $bean->$attribute = $newValue;
573  $storedId = ZurmoRedBean::store($bean);
574  if ($storedId != $id)
575  {
576  throw new FailedToSaveModelException("Id of updated record does not match the id used in finding it.");
577  }
578  }
579 
580  protected static function geColumnData($tableName, $column)
581  {
582  $columnsWithDetails = ZurmoRedBean::$writer->getColumnsWithDetails($tableName);
583  $columnDetails = $columnsWithDetails[$column];
584  preg_match('/([a-z]*)(\(\d*\))?/', $columnDetails['Type'], $results);
585  $columnType = strtolower($results[1]);
586  $columnLength = isset($results[2]) ? trim($results[2], '()') : null;
587  return compact('columnType', 'columnLength');
588  }
589 
595  public static function getReservedColumnNames()
596  {
597  return array('analysisStatus', 'id', 'serializedAnalysisMessages', 'serializedMessages', 'status');
598  }
599 
600  protected static function getReservedColumnMetadata()
601  {
602  $columns = array();
603  $reservedColumnsTypes = array(
604  'status' => 'integer',
605  'serializedMessages' => 'string',
606  'analysisStatus' => 'integer',
607  'serializedAnalysisMessages' => 'string',
608  );
609  foreach ($reservedColumnsTypes as $columnName => $type)
610  {
611  $length = null;
612  $unsigned = null;
613  if ($type === 'string')
614  {
615  // populate the proper type given it would be 1024 char string depending on db type.
616  RedBeanModelMemberRulesToColumnAdapter::resolveStringTypeAndLengthByMaxLength($type, $length, 1024);
617  }
618  else
619  {
620  // forcing integers to be unsigned
621  $unsigned = DatabaseCompatibilityUtil::resolveUnsignedByHintType($type, false);
622  }
623  // last argument is false because we do not want these column names to be resolved to lower characters
624  $columns[] = RedBeanModelMemberToColumnUtil::resolveColumnMetadataByHintType($columnName, $type,
625  $length, $unsigned, null, null, null, false);
626  }
627  return $columns;
628  }
629 
638  protected static function getTableSchemaByNameAndImportColumns($tableName, array $columns, $withReservedColumns = true)
639  {
640  if ($withReservedColumns)
641  {
642  $columns = CMap::mergeArray($columns, static::getReservedColumnMetadata());
643  }
645  }
646 
653  public static function createTableByTableNameAndImportColumns($tableName, array $columns)
654  {
655  // this dropTable is here just because as fail-safe for direct invocations from other classes.
656  ZurmoRedBean::$writer->dropTableByTableName($tableName);
657  $schema = static::getTableSchemaByNameAndImportColumns($tableName, $columns);
658  $messageLogger = new MessageLogger();
660  $schema,
661  $messageLogger);
662  }
663  }
664 ?>
static bulkInsert($tableName, &$rowsOfColumnValues, &$columnNames, $bulkQuantity, $shouldTrim=false)
static createTableByTableNameAndImportColumns($tableName, array $columns)
static padSubArrays(array &$array, $newSize, $value= '')
static checkDatabaseLoadLocalInFile($databaseType, $databaseHostname, $databaseUsername, $databasePassword, $databasePort)
static getCount($tableName, $where=null)
static unsetEmptyKeysFromMaxLengthAndImportArray($clearEmptyColumns, array $emptyKeys, &$maxLengths, array &$importArray)
static convertImportArrayToCsv(array $importArray, $firstRowIsHeaderRow)
static getDatabaseInfoFromDsnString($dsn)
static writeCsvToTemporaryFile($csv)
static updateRowValue($tableName, $id, $attribute, $newValue)
static updateMaxLengthForKey($k, $v, array &$maxLengths)
static generateOrUpdateTableBySchemaDefinition(array $schemaDefinition, &$messageLogger, $validate=true)
static loadDataFromTemporaryFileToTable($tableName)
static updateRowAfterProcessing($tableName, $id, $status, $serializedMessages)
static export($data, $headerData=array(), $exportFileName= 'exports.csv', $download=false, $allowHeaderOnlyConversion=false, $shouldTrim=false)
static getSubset($tableName, $where=null, $count=null, $offset=null)
static resolveColumnsByMaximumColumnLengths(array $maxLengths, array &$columns)
static fixPermissionsOnTemporaryFile()
static makeDatabaseTableByFileHandleAndTableName($fileHandle, $tableName, $delimiter= ',', $enclosure="'", $firstRowIsHeaderRow=false)
static unsetEmptyKeysForKeyIfValueNotEmpty($k, $v, array &$emptyKeys)
static prependEmptyStringToAllImportRows(array &$val)
static convertCurrentValueToUtf8AndPopulateImportDataArray($k, &$v, array &$importData)
static databaseSupportsLoadLocalInFile()
static safeValidateColumnCountAndCreateTable($tableName, $columnCount, array $columns)
static importArrayIntoTable($tableName, array &$importArray, array $columnNames)
static determineMaximumColumnLengthAndPopulateImportArray($fileHandle, $delimiter, $enclosure, array &$maxLengths, array &$importArray, $firstRowIsHeaderRow)
static padEmptyKeys(array &$sourceArray, $newSize, $value=1)
static convertImportArrayAndWriteToTemporaryFile(array $importArray, $firstRowIsHeaderRow)
static getTableSchemaByNameAndImportColumns($tableName, array $columns, $withReservedColumns=true)
Generated on Tue Jul 7 2020 07:10:33
Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.