3.3.6-rc1

[phpmyadmin/dkf.git] / libraries / import / csv.php

<?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$
 * @package phpMyAdmin-Import
 */
if (! defined('PHPMYADMIN')) {
    exit;
}

$analyze = false;

if ($plugin_param !== 'table') {
    $analyze = true;
}

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),
            ),
        'options_text' => 'strOptions',
        );
    
    if ($plugin_param !== 'table') {
        $plugin_list['csv']['options'][] =
            array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames');
    } else {
        $plugin_list['csv']['options'][] =
            array('type' => 'text', 'name' => 'columns', 'text' => 'strColumnNames');
    }
    
    /* 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 = PMA_Message::error('strInvalidCSVParameter');
    $message->addParam('strFieldsTerminatedBy', false);
    $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 = PMA_Message::error('strInvalidCSVParameter');
    $message->addParam('strFieldsEnclosedBy', false);
    $error = TRUE;
} elseif (strlen($csv_escaped) != 1) {
    $message = PMA_Message::error('strInvalidCSVParameter');
    $message->addParam('strFieldsEscapedBy', false);
    $error = TRUE;
} elseif (strlen($csv_new_line) != 1 && $csv_new_line != 'auto') {
    $message = PMA_Message::error('strInvalidCSVParameter');
    $message->addParam('strLinesTerminatedBy', false);
    $error = TRUE;
}

$buffer = '';
$required_fields = 0;

if (!$analyze) {
    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   = preg_split('/,( ?)/', $csv_columns);
        foreach ($tmp as $key => $val) {
            if (count($fields) > 0) {
                $sql_template .= ', ';
            }
            /* Trim also `, if user already included backquoted fields */
            $val = trim($val, " \t\r\n\0\x0B`");
            $found = FALSE;
            foreach ($tmp_fields as $id => $field) {
                if ($field['Field'] == $val) {
                    $found = TRUE;
                    break;
                }
            }
            if (!$found) {
                $message = PMA_Message::error('strInvalidColumn');
                $message->addParam($val);
                $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;

$tempRow = array();
$rows = array();
$col_names = array();
$tables = array();

$col_count = 0;
$max_cols = 0;

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 = PMA_Message::error('strInvalidCSVFormat');
            $message->addParam($line);
            $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[] = '';
            }
            
            if ($analyze) {
                foreach ($values as $ley => $val) {
                    $tempRow[] = $val;
                    ++$col_count;
                }
                
                if ($col_count > $max_cols) {
                    $max_cols = $col_count;
                }
                $col_count = 0;
                
                $rows[] = $tempRow;
                $tempRow = array();
            } else {
                // 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 = PMA_Message::error('strInvalidCSVFieldCount');
                        $message->addParam($line);
                        $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

if ($analyze) {
    /* Fill out all rows */
    $num_rows = count($rows);
    for ($i = 0; $i < $num_rows; ++$i) {
        for ($j = count($rows[$i]); $j < $max_cols; ++$j) {
            $rows[$i][] = 'NULL';
        }
    }
    
    if ($_REQUEST['csv_col_names']) {
        $col_names = array_splice($rows, 0, 1);
        $col_names = $col_names[0];
    }
    
    if ((isset($col_names) && count($col_names) != $max_cols) || !isset($col_names)) {
        // Fill out column names
        for ($i = 0; $i < $max_cols; ++$i) {
            $col_names[] = 'COL '.($i+1);
        }
    }
    
    if (strlen($db)) {
        $result = PMA_DBI_fetch_result('SHOW TABLES');
        $tbl_name = 'TABLE '.(count($result) + 1);
    } else {
        $tbl_name = 'TBL_NAME';
    }
    
    $tables[] = array($tbl_name, $col_names, $rows);

    /* Obtain the best-fit MySQL types for each column */
    $analyses = array();
    $analyses[] = PMA_analyzeTable($tables[0]);

    /**
     * string $db_name (no backquotes)
     *
     * array $table = array(table_name, array() column_names, array()() rows)
     * array $tables = array of "$table"s
     *
     * array $analysis = array(array() column_types, array() column_sizes)
     * array $analyses = array of "$analysis"s
     *
     * array $create = array of SQL strings
     *
     * array $options = an associative array of options
     */

    /* Set database name to the currently selected one, if applicable */
    if (strlen($db)) {
        $db_name = $db;
        $options = array('create_db' => false);
    } else {
        $db_name = 'CSV_DB';
        $options = NULL;
    }

    /* Non-applicable parameters */
    $create = NULL;

    /* Created and execute necessary SQL statements from data */
    PMA_buildSQL($db_name, $tables, $analyses, $create, $options);

    unset($tables);
    unset($analyses);
}

// Commit any possible data in buffers
PMA_importRunQuery();

if (count($values) != 0 && !$error) {
    $message = PMA_Message::error('strInvalidCSVFormat');
    $message->addParam($line);
    $error = TRUE;
}
?>