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