Adapter
3 years ago
Schema
3 years ago
Adapter.php
4 years ago
AdapterInterface.php
4 years ago
BatchInsert.php
4 years ago
Schema.php
5 years ago
SchemaInterface.php
5 years ago
Settings.php
4 years ago
TransactionLevel.php
4 years ago
BatchInsert.php
311 lines
| 1 | <?php |
| 2 | /** |
| 3 | * Matomo - free/libre analytics platform |
| 4 | * |
| 5 | * @link https://matomo.org |
| 6 | * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later |
| 7 | * |
| 8 | */ |
| 9 | namespace Piwik\Db; |
| 10 | |
| 11 | use Exception; |
| 12 | use Piwik\Common; |
| 13 | use Piwik\Config; |
| 14 | use Piwik\Container\StaticContainer; |
| 15 | use Piwik\Db; |
| 16 | use Piwik\Log; |
| 17 | use Piwik\SettingsServer; |
| 18 | use Piwik\SettingsPiwik; |
| 19 | |
| 20 | class BatchInsert |
| 21 | { |
| 22 | /** |
| 23 | * Performs a batch insert into a specific table by iterating through the data |
| 24 | * |
| 25 | * NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available |
| 26 | * |
| 27 | * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name |
| 28 | * @param array $fields array of unquoted field names |
| 29 | * @param array $values array of data to be inserted |
| 30 | * @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows |
| 31 | */ |
| 32 | public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true) |
| 33 | { |
| 34 | $tableName = preg_replace('/[^a-zA-Z\d_-]/', '', $tableName); |
| 35 | $fieldList = '(' . join(',', $fields) . ')'; |
| 36 | $ignore = $ignoreWhenDuplicate ? 'IGNORE' : ''; |
| 37 | |
| 38 | foreach ($values as $row) { |
| 39 | $row = array_values($row); |
| 40 | $query = "INSERT $ignore INTO `" . $tableName . "` |
| 41 | $fieldList |
| 42 | VALUES (" . Common::getSqlStringFieldsArray($row) . ")"; |
| 43 | Db::query($query, $row); |
| 44 | } |
| 45 | } |
| 46 | |
| 47 | /** |
| 48 | * Performs a batch insert into a specific table by sending all data in one SQL statement. |
| 49 | * |
| 50 | * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name |
| 51 | * @param array $fields array of unquoted field names |
| 52 | * @param array $values array of data to be inserted |
| 53 | * @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows |
| 54 | */ |
| 55 | public static function tableInsertBatchSql($tableName, $fields, $values, $ignoreWhenDuplicate = true) |
| 56 | { |
| 57 | $insertLines = array(); |
| 58 | $bind = array(); |
| 59 | foreach ($values as $row) { |
| 60 | $insertLines[] = "(" . Common::getSqlStringFieldsArray($row) . ")"; |
| 61 | $bind = array_merge($bind, $row); |
| 62 | } |
| 63 | |
| 64 | $fieldList = '(' . implode(',', $fields) . ')'; |
| 65 | $insertLines = implode(',', $insertLines); |
| 66 | $ignore = $ignoreWhenDuplicate ? 'IGNORE' : ''; |
| 67 | $query = "INSERT $ignore INTO $tableName $fieldList VALUES $insertLines"; |
| 68 | Db::query($query, $bind); |
| 69 | } |
| 70 | |
| 71 | /** |
| 72 | * Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs, |
| 73 | * as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs. |
| 74 | * |
| 75 | * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name |
| 76 | * @param array $fields array of unquoted field names |
| 77 | * @param array $values array of data to be inserted |
| 78 | * @param bool $throwException Whether to throw an exception that was caught while trying |
| 79 | * LOAD DATA INFILE, or not. |
| 80 | * @param string $charset The charset to use, defaults to utf8 |
| 81 | * @throws Exception |
| 82 | * @return bool True if the bulk LOAD was used, false if we fallback to plain INSERTs |
| 83 | */ |
| 84 | public static function tableInsertBatch($tableName, $fields, $values, $throwException = false, $charset = 'utf8') |
| 85 | { |
| 86 | $loadDataInfileEnabled = Config::getInstance()->General['enable_load_data_infile']; |
| 87 | |
| 88 | if ($loadDataInfileEnabled |
| 89 | && Db::get()->hasBulkLoader()) { |
| 90 | |
| 91 | $path = self::getBestPathForLoadData(); |
| 92 | $instanceId = SettingsPiwik::getPiwikInstanceId(); |
| 93 | if (empty($instanceId)) { |
| 94 | $instanceId = ''; |
| 95 | } |
| 96 | $filePath = $path . $tableName . '-' . $instanceId . Common::generateUniqId() . '.csv'; |
| 97 | |
| 98 | |
| 99 | try { |
| 100 | $fileSpec = array( |
| 101 | 'delim' => "\t", |
| 102 | 'quote' => '"', // chr(34) |
| 103 | 'escape' => '\\\\', // chr(92) |
| 104 | 'escapespecial_cb' => function ($str) { |
| 105 | return str_replace(array(chr(92), chr(34)), array(chr(92) . chr(92), chr(92) . chr(34)), $str); |
| 106 | }, |
| 107 | 'eol' => "\r\n", |
| 108 | 'null' => 'NULL', |
| 109 | 'charset' => $charset |
| 110 | ); |
| 111 | |
| 112 | self::createCSVFile($filePath, $fileSpec, $values); |
| 113 | |
| 114 | if (!is_readable($filePath)) { |
| 115 | throw new Exception("File $filePath could not be read."); |
| 116 | } |
| 117 | |
| 118 | $rc = self::createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec); |
| 119 | if ($rc) { |
| 120 | unlink($filePath); |
| 121 | return true; |
| 122 | } |
| 123 | } catch (Exception $e) { |
| 124 | if ($throwException) { |
| 125 | throw $e; |
| 126 | } |
| 127 | } |
| 128 | |
| 129 | // if all else fails, fallback to a series of INSERTs |
| 130 | if (file_exists($filePath)) { |
| 131 | @unlink($filePath); |
| 132 | } |
| 133 | } |
| 134 | |
| 135 | self::tableInsertBatchIterate($tableName, $fields, $values); |
| 136 | |
| 137 | return false; |
| 138 | } |
| 139 | |
| 140 | private static function getBestPathForLoadData() |
| 141 | { |
| 142 | try { |
| 143 | $path = Db::fetchOne('SELECT @@secure_file_priv'); // was introduced in 5.0.38 |
| 144 | } catch (Exception $e) { |
| 145 | // we do not rethrow exception as an error is expected if MySQL is < 5.0.38 |
| 146 | // in this case tableInsertBatch might still work |
| 147 | } |
| 148 | |
| 149 | if (empty($path) || !@is_dir($path) || !@is_writable($path)) { |
| 150 | $path = StaticContainer::get('path.tmp') . '/assets/'; |
| 151 | } elseif (!Common::stringEndsWith($path, '/')) { |
| 152 | $path .= '/'; |
| 153 | } |
| 154 | |
| 155 | return $path; |
| 156 | } |
| 157 | |
| 158 | /** |
| 159 | * Batch insert into table from CSV (or other delimited) file. |
| 160 | * |
| 161 | * @param string $tableName Name of table |
| 162 | * @param array $fields Field names |
| 163 | * @param string $filePath Path name of a file. |
| 164 | * @param array $fileSpec File specifications (delimiter, line terminator, etc) |
| 165 | * |
| 166 | * @throws Exception |
| 167 | * @return bool True if successful; false otherwise |
| 168 | */ |
| 169 | public static function createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec) |
| 170 | { |
| 171 | // Chroot environment: prefix the path with the absolute chroot path |
| 172 | $chrootPath = Config::getInstance()->General['absolute_chroot_path']; |
| 173 | if (!empty($chrootPath)) { |
| 174 | $filePath = $chrootPath . $filePath; |
| 175 | } |
| 176 | |
| 177 | // On Windows, MySQL expects forward slashes as directory separators |
| 178 | if (SettingsServer::isWindows()) { |
| 179 | $filePath = str_replace('\\', '/', $filePath); |
| 180 | } |
| 181 | |
| 182 | $query = " |
| 183 | '$filePath' |
| 184 | REPLACE |
| 185 | INTO TABLE |
| 186 | `" . $tableName . "`"; |
| 187 | |
| 188 | if (isset($fileSpec['charset'])) { |
| 189 | $query .= ' CHARACTER SET ' . $fileSpec['charset']; |
| 190 | } |
| 191 | |
| 192 | $fieldList = '(' . join(',', $fields) . ')'; |
| 193 | |
| 194 | $query .= " |
| 195 | FIELDS TERMINATED BY |
| 196 | '" . $fileSpec['delim'] . "' |
| 197 | ENCLOSED BY |
| 198 | '" . $fileSpec['quote'] . "' |
| 199 | "; |
| 200 | if (isset($fileSpec['escape'])) { |
| 201 | $query .= " ESCAPED BY '" . $fileSpec['escape'] . "'"; |
| 202 | } |
| 203 | $query .= " |
| 204 | LINES TERMINATED BY |
| 205 | '" . $fileSpec['eol'] . "' |
| 206 | $fieldList |
| 207 | "; |
| 208 | |
| 209 | /* |
| 210 | * First attempt: assume web server and MySQL server are on the same machine; |
| 211 | * this requires that the db user have the FILE privilege; however, since this is |
| 212 | * a global privilege, it may not be granted due to security concerns |
| 213 | */ |
| 214 | if (Config::getInstance()->General['multi_server_environment']) { |
| 215 | $keywords = array(); // don't try 'LOAD DATA INFILE' if in a multi_server_environment |
| 216 | } else { |
| 217 | $keywords = array(''); |
| 218 | } |
| 219 | |
| 220 | /* |
| 221 | * Second attempt: using the LOCAL keyword means the client reads the file and sends it to the server; |
| 222 | * the LOCAL keyword may trigger a known PHP PDO\MYSQL bug when MySQL not built with --enable-local-infile |
| 223 | * @see http://bugs.php.net/bug.php?id=54158 |
| 224 | */ |
| 225 | $openBaseDir = ini_get('open_basedir'); |
| 226 | $safeMode = ini_get('safe_mode'); |
| 227 | |
| 228 | if ((function_exists('mysqli_get_client_stats') || empty($openBaseDir)) && empty($safeMode)) { |
| 229 | // php 5.x - LOAD DATA LOCAL INFILE only used if open_basedir is not set (or we're using a non-buggy version of mysqlnd) |
| 230 | // and if safe mode is not enabled |
| 231 | $keywords[] = 'LOCAL '; |
| 232 | } |
| 233 | |
| 234 | $exceptions = array(); |
| 235 | foreach ($keywords as $keyword) { |
| 236 | $queryStart = 'LOAD DATA ' . $keyword . 'INFILE '; |
| 237 | $sql = $queryStart . $query; |
| 238 | try { |
| 239 | $result = @Db::exec($sql); |
| 240 | if (empty($result) || $result < 0) { |
| 241 | continue; |
| 242 | } |
| 243 | |
| 244 | return true; |
| 245 | } catch (Exception $e) { |
| 246 | $code = $e->getCode(); |
| 247 | $message = $e->getMessage() . ($code ? "[$code]" : ''); |
| 248 | if (\Piwik_ShouldPrintBackTraceWithMessage()) { |
| 249 | $message .= "\n" . $e->getTraceAsString(); |
| 250 | } |
| 251 | $exceptions[] = "\n Try #" . (count($exceptions) + 1) . ': ' . $queryStart . ": " . $message; |
| 252 | } |
| 253 | } |
| 254 | |
| 255 | if (count($exceptions)) { |
| 256 | $message = "LOAD DATA INFILE failed... Error was: " . implode(",", $exceptions); |
| 257 | Log::info($message); |
| 258 | throw new Exception($message); |
| 259 | } |
| 260 | |
| 261 | return false; |
| 262 | } |
| 263 | |
| 264 | /** |
| 265 | * Create CSV (or other delimited) files |
| 266 | * |
| 267 | * @param string $filePath filename to create |
| 268 | * @param array $fileSpec File specifications (delimiter, line terminator, etc) |
| 269 | * @param array $rows Array of array corresponding to rows of values |
| 270 | * @throws Exception if unable to create or write to file |
| 271 | */ |
| 272 | protected static function createCSVFile($filePath, $fileSpec, $rows) |
| 273 | { |
| 274 | // Set up CSV delimiters, quotes, etc |
| 275 | $delim = $fileSpec['delim']; |
| 276 | $quote = $fileSpec['quote']; |
| 277 | $eol = $fileSpec['eol']; |
| 278 | $null = $fileSpec['null']; |
| 279 | $escapespecial_cb = $fileSpec['escapespecial_cb']; |
| 280 | |
| 281 | $fp = @fopen($filePath, 'wb'); |
| 282 | if (!$fp) { |
| 283 | throw new Exception('Error creating the tmp file ' . $filePath . ', please check that the webserver has write permission to write this file.'); |
| 284 | } |
| 285 | |
| 286 | foreach ($rows as $row) { |
| 287 | $output = ''; |
| 288 | foreach ($row as $value) { |
| 289 | if (!isset($value) || is_null($value) || $value === false) { |
| 290 | $output .= $null . $delim; |
| 291 | } else { |
| 292 | $output .= $quote . $escapespecial_cb($value) . $quote . $delim; |
| 293 | } |
| 294 | } |
| 295 | |
| 296 | // Replace delim with eol |
| 297 | $output = substr_replace($output, $eol, -1); |
| 298 | |
| 299 | $ret = fwrite($fp, $output); |
| 300 | if (!$ret) { |
| 301 | fclose($fp); |
| 302 | throw new Exception('Error writing to the tmp file ' . $filePath); |
| 303 | } |
| 304 | } |
| 305 | |
| 306 | fclose($fp); |
| 307 | |
| 308 | @chmod($filePath, 0777); |
| 309 | } |
| 310 | } |
| 311 |