[Sommaire] [Imprimer]

   1  <?php
   2  /* $Id: csv.php 9606 2006-10-26 07:37:48Z nijel $ */
   3  // vim: expandtab sw=4 ts=4 sts=4:
   4  
   5  /* CSV import plugin for phpMyAdmin */
   6  
   7  if ($plugin_param == 'table') {
   8      if (isset($plugin_list)) {
   9          $plugin_list['csv'] = array(
  10              'text' => 'strCSV',
  11              'extension' => 'csv',
  12              'options' => array(
  13                  array('type' => 'bool', 'name' => 'replace', 'text' => 'strReplaceTable'),
  14                  array('type' => 'bool', 'name' => 'ignore', 'text' => 'strIgnoreDuplicates'),
  15                  array('type' => 'text', 'name' => 'terminated', 'text' => 'strFieldsTerminatedBy', 'size' => 2, 'len' => 2),
  16                  array('type' => 'text', 'name' => 'enclosed', 'text' => 'strFieldsEnclosedBy', 'size' => 2, 'len' => 2),
  17                  array('type' => 'text', 'name' => 'escaped', 'text' => 'strFieldsEscapedBy', 'size' => 2, 'len' => 2),
  18                  array('type' => 'text', 'name' => 'new_line', 'text' => 'strLinesTerminatedBy', 'size' => 2),
  19                  array('type' => 'text', 'name' => 'columns', 'text' => 'strColumnNames'),
  20                  ),
  21              'options_text' => 'strCSVImportOptions',
  22              );
  23      } else {
  24      /* We do not define function when plugin is just queried for information above */
  25          $replacements = array(
  26              '\\n'   => "\n",
  27              '\\t'   => "\t",
  28              '\\r'   => "\r",
  29              );
  30          $csv_terminated = strtr($csv_terminated, $replacements);
  31          $csv_enclosed = strtr($csv_enclosed,  $replacements);
  32          $csv_escaped = strtr($csv_escaped, $replacements);
  33          $csv_new_line = strtr($csv_new_line, $replacements);
  34  
  35          if (strlen($csv_terminated) != 1) {
  36              $message = sprintf($strInvalidCSVParameter, $strFieldsTerminatedBy);
  37              $show_error_header = TRUE;
  38              $error = TRUE;
  39          } elseif (strlen($csv_enclosed) != 1) {
  40              $message = sprintf($strInvalidCSVParameter, $strFieldsEnclosedBy);
  41              $show_error_header = TRUE;
  42              $error = TRUE;
  43          } elseif (strlen($csv_escaped) != 1) {
  44              $message = sprintf($strInvalidCSVParameter, $strFieldsEscapedBy);
  45              $show_error_header = TRUE;
  46              $error = TRUE;
  47          } elseif (strlen($csv_new_line) != 1 && $csv_new_line != 'auto') {
  48              $message = sprintf($strInvalidCSVParameter, $strLinesTerminatedBy);
  49              $show_error_header = TRUE;
  50              $error = TRUE;
  51          }
  52  
  53          $buffer = '';
  54          if (isset($csv_replace)) {
  55              $sql_template = 'REPLACE';
  56          } else {
  57              $sql_template = 'INSERT';
  58              if (isset($csv_ignore)) {
  59                  $sql_template .= ' IGNORE';
  60              }
  61          }
  62          $sql_template .= ' INTO ' . PMA_backquote($table);
  63  
  64          $tmp_fields = PMA_DBI_get_fields($db, $table);
  65  
  66          if (empty($csv_columns)) {
  67              $fields = $tmp_fields;
  68          } else {
  69              $sql_template .= ' (';
  70              $fields = array();
  71              $tmp   = split(',( ?)', $csv_columns);
  72              foreach ($tmp as $key => $val) {
  73                  if (count($fields) > 0) {
  74                      $sql_template .= ', ';
  75                  }
  76                  $val = trim($val);
  77                  $found = FALSE;
  78                  foreach ($tmp_fields as $id => $field) {
  79                      if ($field['Field'] == $val) {
  80                          $found = TRUE;
  81                          break;
  82                      }
  83                  }
  84                  if (!$found) {
  85                      $message = sprintf($strInvalidColumn, $val);
  86                      $show_error_header = TRUE;
  87                      $error = TRUE;
  88                      break;
  89                  }
  90                  $fields[] = $field;
  91                  $sql_template .= PMA_backquote($val);
  92              }
  93              $sql_template .= ') ';
  94          }
  95  
  96          $required_fields = count($fields);
  97  
  98          $sql_template .= ' VALUES (';
  99  
 100          // Defaults for parser
 101          $i = 0;
 102          $len = 0;
 103          $line = 1;
 104          $lasti = -1;
 105          $values = array();
 106          $csv_finish = FALSE;
 107  
 108          while (!($finished && $i >= $len) && !$error && !$timeout_passed) {
 109              $data = PMA_importGetNextChunk();
 110              if ($data === FALSE) {
 111                  // subtract data we didn't handle yet and stop processing
 112                  $offset -= strlen($buffer);
 113                  break;
 114              } elseif ($data === TRUE) {
 115                  // Handle rest of buffer
 116              } else {
 117                  // Append new data to buffer
 118                  $buffer .= $data;
 119                  // Do not parse string when we're not at the end and don't have new line inside
 120                  if (($csv_new_line == 'auto' && strpos($buffer, "\r") === FALSE && strpos($buffer, "\n") === FALSE)
 121                      || ($csv_new_line != 'auto' && strpos($buffer, $csv_new_line) === FALSE)) {
 122                      continue;
 123                  }
 124              }
 125  
 126              // Current length of our buffer
 127              $len = strlen($buffer);
 128              // Currently parsed char
 129              $ch = $buffer[$i];
 130              while ($i < $len) {
 131                  // Deadlock protection
 132                  if ($lasti == $i && $lastlen == $len) {
 133                      $message = sprintf($strInvalidCSVFormat, $line);
 134                      $show_error_header = TRUE;
 135                      $error = TRUE;
 136                      break;
 137                  }
 138                  $lasti = $i;
 139                  $lastlen = $len;
 140  
 141                  // This can happen with auto EOL and \r at the end of buffer
 142                  if (!$csv_finish) {
 143                      // Grab empty field
 144                      if ($ch == $csv_terminated) {
 145                          if ($i == $len - 1) {
 146                              break;
 147                          }
 148                          $values[] = '';
 149                          $i++;
 150                          $ch = $buffer[$i];
 151                          continue;
 152                      }
 153  
 154                      // Grab one field
 155                      $fallbacki = $i;
 156                      if ($ch == $csv_enclosed) {
 157                          if ($i == $len - 1) {
 158                              break;
 159                          }
 160                          $need_end = TRUE;
 161                          $i++;
 162                          $ch = $buffer[$i];
 163                      } else {
 164                          $need_end = FALSE;
 165                      }
 166                      $fail = FALSE;
 167                      $value = '';
 168                      while (($need_end && $ch != $csv_enclosed) || (!$need_end && !($ch == $csv_terminated || $ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n"))))) {
 169                          if ($ch == $csv_escaped) {
 170                              if ($i == $len - 1) {
 171                                  $fail = TRUE;
 172                                  break;
 173                              }
 174                              $i++;
 175                              $ch = $buffer[$i];
 176                          }
 177                          $value .= $ch;
 178                          if ($i == $len - 1) {
 179                              if (!$finished) {
 180                                  $fail = TRUE;
 181                              }
 182                              break;
 183                          }
 184                          $i++;
 185                          $ch = $buffer[$i];
 186                      }
 187                      if ($fail) {
 188                          $i = $fallbacki;
 189                          $ch = $buffer[$i];
 190                          break;
 191                      }
 192                      // Need to strip trailing enclosing char?
 193                      if ($need_end && $ch == $csv_enclosed) {
 194                          if ($finished && $i == $len - 1) {
 195                              $ch = NULL;
 196                          } elseif ($i == $len - 1) {
 197                              $i = $fallbacki;
 198                              $ch = $buffer[$i];
 199                              break;
 200                          } else {
 201                              $i++;
 202                              $ch = $buffer[$i];
 203                          }
 204                      }
 205                      // Are we at the end?
 206                      if ($ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n")) || ($finished && $i == $len - 1)) {
 207                          $csv_finish = TRUE;
 208                      }
 209                      // Go to next char
 210                      if ($ch == $csv_terminated) {
 211                          if ($i == $len - 1) {
 212                              $i = $fallbacki;
 213                              $ch = $buffer[$i];
 214                              break;
 215                          }
 216                          $i++;
 217                          $ch = $buffer[$i];
 218                      }
 219                      // If everything went okay, store value
 220                      $values[] = $value;
 221                  }
 222  
 223                  // End of line
 224                  if ($csv_finish || $ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n"))) {
 225                      if ($csv_new_line == 'auto' && $ch == "\r") { // Handle "\r\n"
 226                          if ($i >= ($len - 2) && !$finished) {
 227                              break; // We need more data to decide new line
 228                          }
 229                          if ($buffer[$i + 1] == "\n") {
 230                              $i++;
 231                          }
 232                      }
 233                      // We didn't parse value till the end of line, so there was empty one
 234                      if (!$csv_finish) {
 235                          $values[] = '';
 236                      }
 237                      // Do we have correct count of values?
 238                      if (count($values) != $required_fields) {
 239                          // Hack for excel
 240                          if ($values[count($values) - 1] == ';') {
 241                              unset($values[count($values) - 1]);
 242                          } else {
 243                              $message = sprintf($strInvalidCSVFieldCount, $line);
 244                              $show_error_header = TRUE;
 245                              $error = TRUE;
 246                              break;
 247                          }
 248                      }
 249  
 250                      $first = TRUE;
 251                      $sql = $sql_template;
 252                      foreach ($values as $key => $val) {
 253                          if (!$first) {
 254                              $sql .= ', ';
 255                          }
 256                          $sql .= '\'' . addslashes($val) . '\'';
 257                          $first = FALSE;
 258                      }
 259                      $sql .= ')';
 260  
 261                      /**
 262                       * @todo maybe we could add original line to verbose SQL in comment
 263                       */
 264                      PMA_importRunQuery($sql, $sql);
 265                      $line++;
 266                      $csv_finish = FALSE;
 267                      $values = array();
 268                      $buffer = substr($buffer, $i + 1);
 269                      $len = strlen($buffer);
 270                      $i = 0;
 271                      $lasti = -1;
 272                      $ch = $buffer[0];
 273                  }
 274              } // End of parser loop
 275          } // End of import loop
 276  
 277          // Commit any possible data in buffers
 278          PMA_importRunQuery();
 279  
 280          if (count($values) != 0 && !$error) {
 281              $message = sprintf($strInvalidCSVFormat, $line);
 282              $show_error_header = TRUE;
 283              $error = TRUE;
 284          }
 285      }
 286  }
 287  ?>