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