PageRenderTime 43ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 0ms

/core/Db/BatchInsert.php

https://github.com/CodeYellowBV/piwik
PHP | 254 lines | 182 code | 21 blank | 51 comment | 16 complexity | f693b38d7b44c2b80bfea950b3b145ef MD5 | raw file
Possible License(s): LGPL-3.0, JSON, MIT, GPL-3.0, LGPL-2.1, GPL-2.0, AGPL-1.0, BSD-2-Clause, BSD-3-Clause
  1. <?php
  2. /**
  3. * Piwik - free/libre analytics platform
  4. *
  5. * @link http://piwik.org
  6. * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
  7. *
  8. */
  9. namespace Piwik\Db;
  10. use Exception;
  11. use Piwik\AssetManager;
  12. use Piwik\Common;
  13. use Piwik\Config;
  14. use Piwik\Db;
  15. use Piwik\DbHelper;
  16. use Piwik\Log;
  17. use Piwik\SettingsPiwik;
  18. use Piwik\SettingsServer;
  19. class BatchInsert
  20. {
  21. /**
  22. * Performs a batch insert into a specific table by iterating through the data
  23. *
  24. * NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
  25. *
  26. * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
  27. * @param array $fields array of unquoted field names
  28. * @param array $values array of data to be inserted
  29. * @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
  30. */
  31. public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
  32. {
  33. $fieldList = '(' . join(',', $fields) . ')';
  34. $ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
  35. foreach ($values as $row) {
  36. $query = "INSERT $ignore
  37. INTO " . $tableName . "
  38. $fieldList
  39. VALUES (" . Common::getSqlStringFieldsArray($row) . ")";
  40. Db::query($query, $row);
  41. }
  42. }
  43. /**
  44. * Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs,
  45. * as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs.
  46. *
  47. * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
  48. * @param array $fields array of unquoted field names
  49. * @param array $values array of data to be inserted
  50. * @param bool $throwException Whether to throw an exception that was caught while trying
  51. * LOAD DATA INFILE, or not.
  52. * @throws Exception
  53. * @return bool True if the bulk LOAD was used, false if we fallback to plain INSERTs
  54. */
  55. public static function tableInsertBatch($tableName, $fields, $values, $throwException = false)
  56. {
  57. $filePath = PIWIK_USER_PATH . '/tmp/assets/' . $tableName . '-' . Common::generateUniqId() . '.csv';
  58. $filePath = SettingsPiwik::rewriteTmpPathWithInstanceId($filePath);
  59. $loadDataInfileEnabled = Config::getInstance()->General['enable_load_data_infile'];
  60. if ($loadDataInfileEnabled
  61. && Db::get()->hasBulkLoader()) {
  62. try {
  63. $fileSpec = array(
  64. 'delim' => "\t",
  65. 'quote' => '"', // chr(34)
  66. 'escape' => '\\\\', // chr(92)
  67. 'escapespecial_cb' => function ($str) {
  68. return str_replace(array(chr(92), chr(34)), array(chr(92) . chr(92), chr(92) . chr(34)), $str);
  69. },
  70. 'eol' => "\r\n",
  71. 'null' => 'NULL',
  72. );
  73. // hack for charset mismatch
  74. if (!DbHelper::isDatabaseConnectionUTF8() && !isset(Config::getInstance()->database['charset'])) {
  75. $fileSpec['charset'] = 'latin1';
  76. }
  77. self::createCSVFile($filePath, $fileSpec, $values);
  78. if (!is_readable($filePath)) {
  79. throw new Exception("File $filePath could not be read.");
  80. }
  81. $rc = self::createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec);
  82. if ($rc) {
  83. unlink($filePath);
  84. return true;
  85. }
  86. } catch (Exception $e) {
  87. Log::info("LOAD DATA INFILE failed or not supported, falling back to normal INSERTs... Error was: %s", $e->getMessage());
  88. if ($throwException) {
  89. throw $e;
  90. }
  91. }
  92. }
  93. // if all else fails, fallback to a series of INSERTs
  94. @unlink($filePath);
  95. self::tableInsertBatchIterate($tableName, $fields, $values);
  96. return false;
  97. }
  98. /**
  99. * Batch insert into table from CSV (or other delimited) file.
  100. *
  101. * @param string $tableName Name of table
  102. * @param array $fields Field names
  103. * @param string $filePath Path name of a file.
  104. * @param array $fileSpec File specifications (delimiter, line terminator, etc)
  105. *
  106. * @throws Exception
  107. * @return bool True if successful; false otherwise
  108. */
  109. public static function createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec)
  110. {
  111. // Chroot environment: prefix the path with the absolute chroot path
  112. $chrootPath = Config::getInstance()->General['absolute_chroot_path'];
  113. if(!empty($chrootPath)) {
  114. $filePath = $chrootPath . $filePath;
  115. }
  116. // On Windows, MySQL expects forward slashes as directory separators
  117. if (SettingsServer::isWindows()) {
  118. $filePath = str_replace('\\', '/', $filePath);
  119. }
  120. $query = "
  121. '$filePath'
  122. REPLACE
  123. INTO TABLE
  124. `" . $tableName . "`";
  125. if (isset($fileSpec['charset'])) {
  126. $query .= ' CHARACTER SET ' . $fileSpec['charset'];
  127. }
  128. $fieldList = '(' . join(',', $fields) . ')';
  129. $query .= "
  130. FIELDS TERMINATED BY
  131. '" . $fileSpec['delim'] . "'
  132. ENCLOSED BY
  133. '" . $fileSpec['quote'] . "'
  134. ";
  135. if (isset($fileSpec['escape'])) {
  136. $query .= " ESCAPED BY '" . $fileSpec['escape'] . "'";
  137. }
  138. $query .= "
  139. LINES TERMINATED BY
  140. '" . $fileSpec['eol'] . "'
  141. $fieldList
  142. ";
  143. /*
  144. * First attempt: assume web server and MySQL server are on the same machine;
  145. * this requires that the db user have the FILE privilege; however, since this is
  146. * a global privilege, it may not be granted due to security concerns
  147. */
  148. $keywords = array('');
  149. /*
  150. * Second attempt: using the LOCAL keyword means the client reads the file and sends it to the server;
  151. * the LOCAL keyword may trigger a known PHP PDO_MYSQL bug when MySQL not built with --enable-local-infile
  152. * @see http://bugs.php.net/bug.php?id=54158
  153. */
  154. $openBaseDir = ini_get('open_basedir');
  155. $safeMode = ini_get('safe_mode');
  156. if (empty($openBaseDir) && empty($safeMode)) {
  157. // php 5.x - LOAD DATA LOCAL INFILE is disabled if open_basedir restrictions or safe_mode enabled
  158. $keywords[] = 'LOCAL ';
  159. }
  160. $exceptions = array();
  161. foreach ($keywords as $keyword) {
  162. $queryStart = 'LOAD DATA ' . $keyword . 'INFILE ';
  163. $sql = $queryStart . $query;
  164. try {
  165. $result = @Db::exec($sql);
  166. if (empty($result) || $result < 0) {
  167. continue;
  168. }
  169. return true;
  170. } catch (Exception $e) {
  171. // echo $sql . ' ---- ' . $e->getMessage();
  172. $code = $e->getCode();
  173. $message = $e->getMessage() . ($code ? "[$code]" : '');
  174. if (!Db::get()->isErrNo($e, '1148')) {
  175. Log::info("LOAD DATA INFILE failed... Error was: %s", $message);
  176. }
  177. $exceptions[] = "\n Try #" . (count($exceptions) + 1) . ': ' . $queryStart . ": " . $message;
  178. }
  179. }
  180. if (count($exceptions)) {
  181. throw new Exception(implode(",", $exceptions));
  182. }
  183. return false;
  184. }
  185. /**
  186. * Create CSV (or other delimited) files
  187. *
  188. * @param string $filePath filename to create
  189. * @param array $fileSpec File specifications (delimiter, line terminator, etc)
  190. * @param array $rows Array of array corresponding to rows of values
  191. * @throws Exception if unable to create or write to file
  192. */
  193. static protected function createCSVFile($filePath, $fileSpec, $rows)
  194. {
  195. // Set up CSV delimiters, quotes, etc
  196. $delim = $fileSpec['delim'];
  197. $quote = $fileSpec['quote'];
  198. $eol = $fileSpec['eol'];
  199. $null = $fileSpec['null'];
  200. $escapespecial_cb = $fileSpec['escapespecial_cb'];
  201. $fp = @fopen($filePath, 'wb');
  202. if (!$fp) {
  203. throw new Exception('Error creating the tmp file ' . $filePath . ', please check that the webserver has write permission to write this file.');
  204. }
  205. foreach ($rows as $row) {
  206. $output = '';
  207. foreach ($row as $value) {
  208. if (!isset($value) || is_null($value) || $value === false) {
  209. $output .= $null . $delim;
  210. } else {
  211. $output .= $quote . $escapespecial_cb($value) . $quote . $delim;
  212. }
  213. }
  214. // Replace delim with eol
  215. $output = substr_replace($output, $eol, -1);
  216. $ret = fwrite($fp, $output);
  217. if (!$ret) {
  218. fclose($fp);
  219. throw new Exception('Error writing to the tmp file ' . $filePath);
  220. }
  221. }
  222. fclose($fp);
  223. @chmod($filePath, 0777);
  224. }
  225. }