<?php/* vim: set expandtab sw=4 ts=4 sts=4: *//** * CSV import plugin for phpMyAdmin * * @todo    add an option for handling NULL values * @version $Id: csv.php 11335 2008-06-21 14:01:54Z lem9 $ */if (! defined('PHPMYADMIN')) {    exit;}/** * */if ($plugin_param !== 'table') {    return;}if (isset($plugin_list)) {    $plugin_list['csv'] = array(        'text' => 'strCSV',        'extension' => 'csv',        'options' => array(            array('type' => 'bool', 'name' => 'replace', 'text' => 'strReplaceTable'),            array('type' => 'bool', 'name' => 'ignore', 'text' => 'strIgnoreDuplicates'),            array('type' => 'text', 'name' => 'terminated', 'text' => 'strFieldsTerminatedBy', 'size' => 2, 'len' => 2),            array('type' => 'text', 'name' => 'enclosed', 'text' => 'strFieldsEnclosedBy', 'size' => 2, 'len' => 2),            array('type' => 'text', 'name' => 'escaped', 'text' => 'strFieldsEscapedBy', 'size' => 2, 'len' => 2),            array('type' => 'text', 'name' => 'new_line', 'text' => 'strLinesTerminatedBy', 'size' => 2),            array('type' => 'text', 'name' => 'columns', 'text' => 'strColumnNames'),            ),        'options_text' => 'strOptions',        );    /* We do not define function when plugin is just queried for information above */    return;}$replacements = array(    '\\n'   => "\n",    '\\t'   => "\t",    '\\r'   => "\r",    );$csv_terminated = strtr($csv_terminated, $replacements);$csv_enclosed = strtr($csv_enclosed,  $replacements);$csv_escaped = strtr($csv_escaped, $replacements);$csv_new_line = strtr($csv_new_line, $replacements);if (strlen($csv_terminated) != 1) {    $message = sprintf($strInvalidCSVParameter, $strFieldsTerminatedBy);    $show_error_header = TRUE;    $error = TRUE;    // The default dialog of MS Excel when generating a CSV produces a     // semi-colon-separated file with no chance of specifying the     // enclosing character. Thus, users who want to import this file     // tend to remove the enclosing character on the Import dialog.     // I could not find a test case where having no enclosing characters     // confuses this script.    // But the parser won't work correctly with strings so we allow just    // one character.} elseif (strlen($csv_enclosed) > 1) {    $message = sprintf($strInvalidCSVParameter, $strFieldsEnclosedBy);    $show_error_header = TRUE;    $error = TRUE;} elseif (strlen($csv_escaped) != 1) {    $message = sprintf($strInvalidCSVParameter, $strFieldsEscapedBy);    $show_error_header = TRUE;    $error = TRUE;} elseif (strlen($csv_new_line) != 1 && $csv_new_line != 'auto') {    $message = sprintf($strInvalidCSVParameter, $strLinesTerminatedBy);    $show_error_header = TRUE;    $error = TRUE;}$buffer = '';if (isset($csv_replace)) {    $sql_template = 'REPLACE';} else {    $sql_template = 'INSERT';    if (isset($csv_ignore)) {        $sql_template .= ' IGNORE';    }}$sql_template .= ' INTO ' . PMA_backquote($table);$tmp_fields = PMA_DBI_get_fields($db, $table);if (empty($csv_columns)) {    $fields = $tmp_fields;} else {    $sql_template .= ' (';    $fields = array();    $tmp   = split(',( ?)', $csv_columns);    foreach ($tmp as $key => $val) {        if (count($fields) > 0) {            $sql_template .= ', ';        }        $val = trim($val);        $found = FALSE;        foreach ($tmp_fields as $id => $field) {            if ($field['Field'] == $val) {                $found = TRUE;                break;            }        }        if (!$found) {            $message = sprintf($strInvalidColumn, $val);            $show_error_header = TRUE;            $error = TRUE;            break;        }        $fields[] = $field;        $sql_template .= PMA_backquote($val);    }    $sql_template .= ') ';}$required_fields = count($fields);$sql_template .= ' VALUES (';// Defaults for parser$i = 0;$len = 0;$line = 1;$lasti = -1;$values = array();$csv_finish = FALSE;while (!($finished && $i >= $len) && !$error && !$timeout_passed) {    $data = PMA_importGetNextChunk();    if ($data === FALSE) {        // subtract data we didn't handle yet and stop processing        $offset -= strlen($buffer);        break;    } elseif ($data === TRUE) {        // Handle rest of buffer    } else {        // Append new data to buffer        $buffer .= $data;        unset($data);        // Do not parse string when we're not at the end and don't have new line inside        if (($csv_new_line == 'auto' && strpos($buffer, "\r") === FALSE && strpos($buffer, "\n") === FALSE)            || ($csv_new_line != 'auto' && strpos($buffer, $csv_new_line) === FALSE)) {            continue;        }    }    // Current length of our buffer    $len = strlen($buffer);    // Currently parsed char    $ch = $buffer[$i];    while ($i < $len) {        // Deadlock protection        if ($lasti == $i && $lastlen == $len) {            $message = sprintf($strInvalidCSVFormat, $line);            $show_error_header = TRUE;            $error = TRUE;            break;        }        $lasti = $i;        $lastlen = $len;        // This can happen with auto EOL and \r at the end of buffer        if (!$csv_finish) {            // Grab empty field            if ($ch == $csv_terminated) {                if ($i == $len - 1) {                    break;                }                $values[] = '';                $i++;                $ch = $buffer[$i];                continue;            }            // Grab one field            $fallbacki = $i;            if ($ch == $csv_enclosed) {                if ($i == $len - 1) {                    break;                }                $need_end = TRUE;                $i++;                $ch = $buffer[$i];            } else {                $need_end = FALSE;            }            $fail = FALSE;            $value = '';            while (($need_end && $ch != $csv_enclosed)             || (!$need_end && !($ch == $csv_terminated               || $ch == $csv_new_line || ($csv_new_line == 'auto'                && ($ch == "\r" || $ch == "\n"))))) {                if ($ch == $csv_escaped) {                    if ($i == $len - 1) {                        $fail = TRUE;                        break;                    }                    $i++;                    $ch = $buffer[$i];                }                $value .= $ch;                if ($i == $len - 1) {                    if (!$finished) {                        $fail = TRUE;                    }                    break;                }                $i++;                $ch = $buffer[$i];            }            // unquoted NULL string            if (false === $need_end && $value === 'NULL') {                $value = null;            }            if ($fail) {                $i = $fallbacki;                $ch = $buffer[$i];                break;            }            // Need to strip trailing enclosing char?            if ($need_end && $ch == $csv_enclosed) {                if ($finished && $i == $len - 1) {                    $ch = NULL;                } elseif ($i == $len - 1) {                    $i = $fallbacki;                    $ch = $buffer[$i];                    break;                } else {                    $i++;                    $ch = $buffer[$i];                }            }            // Are we at the end?            if ($ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n")) || ($finished && $i == $len - 1)) {                $csv_finish = TRUE;            }            // Go to next char            if ($ch == $csv_terminated) {                if ($i == $len - 1) {                    $i = $fallbacki;                    $ch = $buffer[$i];                    break;                }                $i++;                $ch = $buffer[$i];            }            // If everything went okay, store value            $values[] = $value;        }        // End of line        if ($csv_finish || $ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n"))) {            if ($csv_new_line == 'auto' && $ch == "\r") { // Handle "\r\n"                if ($i >= ($len - 2) && !$finished) {                    break; // We need more data to decide new line                }                if ($buffer[$i + 1] == "\n") {                    $i++;                }            }            // We didn't parse value till the end of line, so there was empty one            if (!$csv_finish) {                $values[] = '';            }            // Do we have correct count of values?            if (count($values) != $required_fields) {                // Hack for excel                if ($values[count($values) - 1] == ';') {                    unset($values[count($values) - 1]);                } else {                    $message = sprintf($strInvalidCSVFieldCount, $line);                    $show_error_header = TRUE;                    $error = TRUE;                    break;                }            }            $first = TRUE;            $sql = $sql_template;            foreach ($values as $key => $val) {                if (!$first) {                    $sql .= ', ';                }                if ($val === null) {                    $sql .= 'NULL';                } else {                    $sql .= '\'' . addslashes($val) . '\'';                }                $first = FALSE;            }            $sql .= ')';            /**             * @todo maybe we could add original line to verbose SQL in comment             */            PMA_importRunQuery($sql, $sql);            $line++;            $csv_finish = FALSE;            $values = array();            $buffer = substr($buffer, $i + 1);            $len = strlen($buffer);            $i = 0;            $lasti = -1;            $ch = $buffer[0];        }    } // End of parser loop} // End of import loop// Commit any possible data in buffersPMA_importRunQuery();if (count($values) != 0 && !$error) {    $message = sprintf($strInvalidCSVFormat, $line);    $show_error_header = TRUE;    $error = TRUE;}?>