/*
 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
 * Version 1.0, and under the Eclipse Public License, Version 1.0
 * (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.test.db;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;
import org.h2.constant.ErrorCode;
import org.h2.constant.SysProperties;
import org.h2.engine.Constants;
import org.h2.store.fs.FileUtils;
import org.h2.test.TestBase;
import org.h2.tools.Csv;
import org.h2.util.IOUtils;
import org.h2.util.New;
import org.h2.util.StringUtils;

/**
 * CSVREAD and CSVWRITE tests.
 *
 * @author Thomas Mueller
 * @author Sylvain Cuaz (testNull)
 */
public class TestCsv extends TestBase {

    /**
     * Run just this test.
     *
     * @param a ignored
     */
    public static void main(String... a) throws Exception {
        TestBase test = TestBase.createCaller().init();
        test.config.traceTest = true;
        test.test();
    }

    public void test() throws Exception {
        testWriteColumnHeader();
        testCaseSensitiveColumnNames();
        testWriteResultSetDataType();
        testPreserveWhitespace();
        testChangeData();
        testOptions();
        testPseudoBom();
        testWriteRead();
        testColumnNames();
        testSpaceSeparated();
        testNull();
        testRandomData();
        testEmptyFieldDelimiter();
        testFieldDelimiter();
        testAsTable();
        testRead();
        testPipe();
        deleteDb("csv");
    }

    private void testWriteColumnHeader() throws Exception {
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("call csvwrite('" + getBaseDir() + "/test.tsv', 'select x from dual', 'writeColumnHeader=false')");
        String x = IOUtils.readStringAndClose(IOUtils.getReader(FileUtils.newInputStream(getBaseDir() + "/test.tsv")), -1);
        assertEquals("\"1\"", x.trim());
        stat.execute("call csvwrite('" + getBaseDir() + "/test.tsv', 'select x from dual', 'writeColumnHeader=true')");
        x = IOUtils.readStringAndClose(IOUtils.getReader(FileUtils.newInputStream(getBaseDir() + "/test.tsv")), -1);
        x = x.trim();
        assertTrue(x.startsWith("\"X\""));
        assertTrue(x.endsWith("\"1\""));
        conn.close();
    }


    private void testWriteResultSetDataType() throws Exception {
        // Oracle: ResultSet.getString on a date or time column returns a
        // strange result (2009-6-30.16.17. 21. 996802000 according to a
        // customer)
        StringWriter writer = new StringWriter();
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select timestamp '-100-01-01 12:00:00.0' ts, null n");
        Csv csv = new Csv();
        csv.setFieldDelimiter((char) 0);
        csv.setLineSeparator(";");
        csv.write(writer, rs);
        conn.close();
        // getTimestamp().getString() needs to be used (not for H2, but for
        // Oracle)
        assertEquals("TS,N;0101-01-01 12:00:00.0,;", writer.toString());
    }

    private void testCaseSensitiveColumnNames() throws Exception {
        OutputStream out = FileUtils.newOutputStream(getBaseDir() + "/test.tsv", false);
        out.write("lower,Mixed,UPPER\n 1 , 2, 3 \n".getBytes());
        out.close();
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        ResultSet rs;
        rs = stat.executeQuery("select * from csvread('" + getBaseDir() + "/test.tsv')");
        rs.next();
        assertEquals("LOWER", rs.getMetaData().getColumnName(1));
        assertEquals("MIXED", rs.getMetaData().getColumnName(2));
        assertEquals("UPPER", rs.getMetaData().getColumnName(3));
        rs = stat.executeQuery("select * from csvread('" + getBaseDir()
                + "/test.tsv', null, 'caseSensitiveColumnNames=true')");
        rs.next();
        assertEquals("lower", rs.getMetaData().getColumnName(1));
        assertEquals("Mixed", rs.getMetaData().getColumnName(2));
        assertEquals("UPPER", rs.getMetaData().getColumnName(3));
        conn.close();
    }

    private void testPreserveWhitespace() throws Exception {
        OutputStream out = FileUtils.newOutputStream(getBaseDir() + "/test.tsv", false);
        out.write("a,b\n 1 , 2 \n".getBytes());
        out.close();
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        ResultSet rs;
        rs = stat.executeQuery("select * from csvread('" + getBaseDir() + "/test.tsv')");
        rs.next();
        assertEquals("1", rs.getString(1));
        assertEquals("2", rs.getString(2));
        rs = stat.executeQuery("select * from csvread('" + getBaseDir()
                + "/test.tsv', null, 'preserveWhitespace=true')");
        rs.next();
        assertEquals(" 1 ", rs.getString(1));
        assertEquals(" 2 ", rs.getString(2));
        conn.close();
    }

    private void testChangeData() throws Exception {
        OutputStream out = FileUtils.newOutputStream(getBaseDir() + "/test.tsv", false);
        out.write("a,b,c,d,e,f,g\n1".getBytes());
        out.close();
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from csvread('" + getBaseDir() + "/test.tsv')");
        assertEquals(7, rs.getMetaData().getColumnCount());
        assertEquals("A", rs.getMetaData().getColumnLabel(1));
        rs.next();
        assertEquals(1, rs.getInt(1));
        out = FileUtils.newOutputStream(getBaseDir() + "/test.tsv", false);
        out.write("x".getBytes());
        out.close();
        rs = stat.executeQuery("select * from csvread('" + getBaseDir() + "/test.tsv')");
        assertEquals(1, rs.getMetaData().getColumnCount());
        assertEquals("X", rs.getMetaData().getColumnLabel(1));
        assertFalse(rs.next());
        conn.close();
    }

    private void testOptions() {
        Csv csv = new Csv();
        assertEquals(",", csv.getFieldSeparatorWrite());
        assertEquals(SysProperties.LINE_SEPARATOR, csv.getLineSeparator());
        assertEquals("", csv.getNullString());
        assertEquals(null, csv.getRowSeparatorWrite());
        assertEquals('\"', csv.getEscapeCharacter());
        assertEquals('"', csv.getFieldDelimiter());
        assertEquals(',', csv.getFieldSeparatorRead());
        assertEquals(",", csv.getFieldSeparatorWrite());
        assertEquals(Constants.VERSION_MINOR == 3 ? 0 : '#', csv.getLineCommentCharacter());
        assertEquals(false, csv.getPreserveWhitespace());

        String charset;

        charset = csv.setOptions("escape=\\  fieldDelimiter=\\\\ fieldSeparator=\n " +
                "lineComment=\" lineSeparator=\\ \\\\\\ ");
        assertEquals(' ', csv.getEscapeCharacter());
        assertEquals('\\', csv.getFieldDelimiter());
        assertEquals('\n', csv.getFieldSeparatorRead());
        assertEquals("\n", csv.getFieldSeparatorWrite());
        assertEquals('"', csv.getLineCommentCharacter());
        assertEquals(" \\ ", csv.getLineSeparator());
        assertFalse(csv.getPreserveWhitespace());
        assertFalse(csv.getCaseSensitiveColumnNames());

        charset = csv.setOptions("escape=1x fieldDelimiter=2x fieldSeparator=3x " + "lineComment=4x lineSeparator=5x "
                + "null=6x rowSeparator=7x charset=8x preserveWhitespace=true caseSensitiveColumnNames=true");
        assertEquals('1', csv.getEscapeCharacter());
        assertEquals('2', csv.getFieldDelimiter());
        assertEquals('3', csv.getFieldSeparatorRead());
        assertEquals("3x", csv.getFieldSeparatorWrite());
        assertEquals('4', csv.getLineCommentCharacter());
        assertEquals("5x", csv.getLineSeparator());
        assertEquals("6x", csv.getNullString());
        assertEquals("7x", csv.getRowSeparatorWrite());
        assertEquals("8x", charset);
        assertTrue(csv.getPreserveWhitespace());
        assertTrue(csv.getCaseSensitiveColumnNames());

        charset = csv.setOptions("escape= fieldDelimiter= fieldSeparator= " + "lineComment= lineSeparator=\r\n "
                + "null=\0 rowSeparator= charset=");
        assertEquals(0, csv.getEscapeCharacter());
        assertEquals(0, csv.getFieldDelimiter());
        assertEquals(0, csv.getFieldSeparatorRead());
        assertEquals("", csv.getFieldSeparatorWrite());
        assertEquals(0, csv.getLineCommentCharacter());
        assertEquals("\r\n", csv.getLineSeparator());
        assertEquals("\0", csv.getNullString());
        assertEquals("", csv.getRowSeparatorWrite());
        assertEquals("", charset);

        createClassProxy(Csv.class);
        assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, csv).setOptions("escape=a error=b");
        assertEquals('a', csv.getEscapeCharacter());
    }

    private void testPseudoBom() throws Exception {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        // UTF-8 "BOM" / marker
        out.write(StringUtils.convertHexToBytes("ef" + "bb" + "bf"));
        out.write("\"ID\", \"NAME\"\n1, Hello".getBytes("UTF-8"));
        byte[] buff = out.toByteArray();
        Reader r = new InputStreamReader(new ByteArrayInputStream(buff), "UTF-8");
        ResultSet rs = new Csv().read(r, null);
        assertEquals("ID", rs.getMetaData().getColumnLabel(1));
        assertEquals("NAME", rs.getMetaData().getColumnLabel(2));
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
    }

    private void testColumnNames() throws Exception {
        ResultSet rs;
        rs = new Csv().read(new StringReader("Id,First Name,2x,_x2\n1,2,3"), null);
        assertEquals("ID", rs.getMetaData().getColumnName(1));
        assertEquals("First Name", rs.getMetaData().getColumnName(2));
        assertEquals("2x", rs.getMetaData().getColumnName(3));
        assertEquals("_X2", rs.getMetaData().getColumnName(4));

        rs = new Csv().read(new StringReader("a,a\n1,2"), null);
        assertEquals("A", rs.getMetaData().getColumnName(1));
        assertEquals("A1", rs.getMetaData().getColumnName(2));

        rs = new Csv().read(new StringReader("1,2"), new String[] { "", null });
        assertEquals("C1", rs.getMetaData().getColumnName(1));
        assertEquals("C2", rs.getMetaData().getColumnName(2));
    }

    private void testSpaceSeparated() throws SQLException {
        deleteDb("csv");
        File f = new File(getBaseDir() + "/testSpace.csv");
        FileUtils.delete(f.getAbsolutePath());

        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("create temporary table test (a int, b int, c int)");
        stat.execute("insert into test values(1,2,3)");
        stat.execute("insert into test values(4,null,5)");
        stat.execute("call csvwrite('" + getBaseDir() + "/test.tsv','select * from test',null,' ')");
        ResultSet rs1 = stat.executeQuery("select * from test");
        assertResultSetOrdered(rs1, new String[][] { new String[] { "1", "2", "3" }, new String[] { "4", null, "5" } });
        ResultSet rs2 = stat.executeQuery("select * from csvread('" + getBaseDir() + "/test.tsv',null,null,' ')");
        assertResultSetOrdered(rs2, new String[][] { new String[] { "1", "2", "3" }, new String[] { "4", null, "5" } });
        conn.close();
        FileUtils.delete(f.getAbsolutePath());
        FileUtils.delete(getBaseDir() + "/test.tsv");
    }

    /**
     * Test custom NULL string.
     */
    private void testNull() throws Exception {
        deleteDb("csv");

        String fileName = getBaseDir() + "/testNull.csv";
        FileUtils.delete(fileName);

        OutputStream out = FileUtils.newOutputStream(fileName, false);
        String csvContent = "\"A\",\"B\",\"C\",\"D\"\n\\N,\"\",\"\\N\",";
        byte[] b = csvContent.getBytes("UTF-8");
        out.write(b, 0, b.length);
        out.close();
        Csv csv = new Csv();
        csv.setNullString("\\N");
        ResultSet rs = csv.read(fileName, null, "UTF8");
        ResultSetMetaData meta = rs.getMetaData();
        assertEquals(4, meta.getColumnCount());
        assertEquals("A", meta.getColumnLabel(1));
        assertEquals("B", meta.getColumnLabel(2));
        assertEquals("C", meta.getColumnLabel(3));
        assertEquals("D", meta.getColumnLabel(4));
        assertTrue(rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals("", rs.getString(2));
        // null is never quoted
        assertEquals("\\N", rs.getString(3));
        // an empty string is always parsed as null
        assertEquals(null, rs.getString(4));
        assertFalse(rs.next());

        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("call csvwrite('" + fileName +
                "', 'select NULL as a, '''' as b, ''\\N'' as c, NULL as d', 'UTF8', ',', '\"', NULL, '\\N', '\n')");
        InputStreamReader reader = new InputStreamReader(FileUtils.newInputStream(fileName));
        // on read, an empty string is treated like null,
        // but on write a null is always written with the nullString
        String data = IOUtils.readStringAndClose(reader, -1);
        assertEquals(csvContent + "\\N", data.trim());
        conn.close();

        FileUtils.delete(fileName);
    }

    private void testRandomData() throws SQLException {
        deleteDb("csv");
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("drop table if exists test");
        stat.execute("create table test(a varchar, b varchar)");
        int len = getSize(1000, 10000);
        PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
        ArrayList<String[]> list = New.arrayList();
        Random random = new Random(1);
        for (int i = 0; i < len; i++) {
            String a = randomData(random), b = randomData(random);
            prep.setString(1, a);
            prep.setString(2, b);
            list.add(new String[] { a, b });
            prep.execute();
        }
        stat.execute("CALL CSVWRITE('" + getBaseDir() + "/test.csv', 'SELECT * FROM test', 'UTF-8', '|', '#')");
        Csv csv = new Csv();
        csv.setFieldSeparatorRead('|');
        csv.setFieldDelimiter('#');
        ResultSet rs = csv.read(getBaseDir() + "/test.csv", null, "UTF-8");
        for (int i = 0; i < len; i++) {
            assertTrue(rs.next());
            String[] pair = list.get(i);
            assertEquals(pair[0], rs.getString(1));
            assertEquals(pair[1], rs.getString(2));
        }
        assertFalse(rs.next());
        conn.close();
        FileUtils.delete(getBaseDir() + "/test.csv");
    }

    private static String randomData(Random random) {
        if (random.nextInt(10) == 1) {
            return null;
        }
        int len = random.nextInt(5);
        StringBuilder buff = new StringBuilder();
        String chars = "\\\'\",\r\n\t ;.-123456|#";
        for (int i = 0; i < len; i++) {
            buff.append(chars.charAt(random.nextInt(chars.length())));
        }
        return buff.toString();
    }

    private void testEmptyFieldDelimiter() throws Exception {
        String fileName = getBaseDir() + "/test.csv";
        FileUtils.delete(fileName);
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("call csvwrite('" + fileName
                + "', 'select 1 id, ''Hello'' name', null, '|', '', null, null, chr(10))");
        InputStreamReader reader = new InputStreamReader(FileUtils.newInputStream(fileName));
        String text = IOUtils.readStringAndClose(reader, -1).trim();
        text = StringUtils.replaceAll(text, "\n", " ");
        assertEquals("ID|NAME 1|Hello", text);
        ResultSet rs = stat.executeQuery("select * from csvread('" + fileName + "', null, null, '|', '')");
        ResultSetMetaData meta = rs.getMetaData();
        assertEquals(2, meta.getColumnCount());
        assertEquals("ID", meta.getColumnLabel(1));
        assertEquals("NAME", meta.getColumnLabel(2));
        assertTrue(rs.next());
        assertEquals("1", rs.getString(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        conn.close();
        FileUtils.delete(fileName);
    }

    private void testFieldDelimiter() throws Exception {
        String fileName = getBaseDir() + "/test.csv";
        String fileName2 = getBaseDir() + "/test2.csv";
        FileUtils.delete(fileName);
        OutputStream out = FileUtils.newOutputStream(fileName, false);
        byte[] b = "'A'; 'B'\n\'It\\'s nice\'; '\nHello\\*\n'".getBytes();
        out.write(b, 0, b.length);
        out.close();
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from csvread('" + fileName + "', null, null, ';', '''', '\\')");
        ResultSetMetaData meta = rs.getMetaData();
        assertEquals(2, meta.getColumnCount());
        assertEquals("A", meta.getColumnLabel(1));
        assertEquals("B", meta.getColumnLabel(2));
        assertTrue(rs.next());
        assertEquals("It's nice", rs.getString(1));
        assertEquals("\nHello*\n", rs.getString(2));
        assertFalse(rs.next());
        stat.execute("call csvwrite('" + fileName2 +
                "', 'select * from csvread(''" + fileName +
                "'', null, null, '';'', '''''''', ''\\'')', null, '+', '*', '#')");
        rs = stat.executeQuery("select * from csvread('" + fileName2 +
                "', null, null, '+', '*', '#')");
        meta = rs.getMetaData();
        assertEquals(2, meta.getColumnCount());
        assertEquals("A", meta.getColumnLabel(1));
        assertEquals("B", meta.getColumnLabel(2));
        assertTrue(rs.next());
        assertEquals("It's nice", rs.getString(1));
        assertEquals("\nHello*\n", rs.getString(2));
        assertFalse(rs.next());
        conn.close();
        FileUtils.delete(fileName);
        FileUtils.delete(fileName2);
    }

    private void testPipe() throws SQLException {
        deleteDb("csv");
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("call csvwrite('" + getBaseDir() + "/test.csv', 'select 1 id, ''Hello'' name', 'utf-8', '|')");
        ResultSet rs = stat.executeQuery("select * from csvread('" + getBaseDir() + "/test.csv', null, 'utf-8', '|')");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        new File(getBaseDir() + "/test.csv").delete();

        // PreparedStatement prep = conn.prepareStatement("select * from
        // csvread(?, null, ?, ?)");
        // prep.setString(1, BASE_DIR+"/test.csv");
        // prep.setString(2, "utf-8");
        // prep.setString(3, "|");
        // rs = prep.executeQuery();

        conn.close();
        FileUtils.delete(getBaseDir() + "/test.csv");
    }

    private void testAsTable() throws SQLException {
        deleteDb("csv");
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("call csvwrite('" + getBaseDir() + "/test.csv', 'select 1 id, ''Hello'' name')");
        ResultSet rs = stat.executeQuery("select name from csvread('" + getBaseDir() + "/test.csv')");
        assertTrue(rs.next());
        assertEquals("Hello", rs.getString(1));
        assertFalse(rs.next());
        rs = stat.executeQuery("call csvread('" + getBaseDir() + "/test.csv')");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        new File(getBaseDir() + "/test.csv").delete();
        conn.close();
    }

    private void testRead() throws Exception {
        String fileName = getBaseDir() + "/test.csv";
        FileUtils.delete(fileName);
        OutputStream out = FileUtils.newOutputStream(fileName, false);
        byte[] b = "a,b,c,d\n201,-2,0,18\n, \"abc\"\"\" ,,\"\"\n 1 ,2 , 3, 4 \n5, 6, 7, 8".getBytes();
        out.write(b, 0, b.length);
        out.close();
        ResultSet rs = new Csv().read(fileName, null, "UTF8");
        ResultSetMetaData meta = rs.getMetaData();
        assertEquals(4, meta.getColumnCount());
        assertEquals("A", meta.getColumnLabel(1));
        assertEquals("B", meta.getColumnLabel(2));
        assertEquals("C", meta.getColumnLabel(3));
        assertEquals("D", meta.getColumnLabel(4));
        assertTrue(rs.next());
        assertEquals("201", rs.getString(1));
        assertEquals("-2", rs.getString(2));
        assertEquals("0", rs.getString(3));
        assertEquals("18", rs.getString(4));
        assertTrue(rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals("abc\"", rs.getString(2));
        assertEquals(null, rs.getString(3));
        assertEquals("", rs.getString(4));
        assertTrue(rs.next());
        assertEquals("1", rs.getString(1));
        assertEquals("2", rs.getString(2));
        assertEquals("3", rs.getString(3));
        assertEquals("4", rs.getString(4));
        assertTrue(rs.next());
        assertEquals("5", rs.getString(1));
        assertEquals("6", rs.getString(2));
        assertEquals("7", rs.getString(3));
        assertEquals("8", rs.getString(4));
        assertFalse(rs.next());

        // a,b,c,d
        // 201,-2,0,18
        // 201,2,0,18
        // 201,2,0,18
        // 201,2,0,18
        // 201,2,0,18
        // 201,2,0,18
        FileUtils.delete(fileName);
    }

    private void testWriteRead() throws SQLException {
        deleteDb("csv");
        Connection conn = getConnection("csv");
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
        // int len = 100000;
        int len = 100;
        for (int i = 0; i < len; i++) {
            stat.execute("INSERT INTO TEST(NAME) VALUES('Ruebezahl')");
        }
        long time;
        time = System.currentTimeMillis();
        new Csv().write(conn, getBaseDir() + "/testRW.csv",
                "SELECT X ID, 'Ruebezahl' NAME FROM SYSTEM_RANGE(1, " + len + ")", "UTF8");
        trace("write: " + (System.currentTimeMillis() - time));
        ResultSet rs;
        time = System.currentTimeMillis();
        for (int i = 0; i < 30; i++) {
            rs = new Csv().read(getBaseDir() + "/testRW.csv", null, "UTF8");
            while (rs.next()) {
                // ignore
            }
        }
        trace("read: " + (System.currentTimeMillis() - time));
        rs = new Csv().read(getBaseDir() + "/testRW.csv", null, "UTF8");
        // stat.execute("CREATE ALIAS CSVREAD FOR \"org.h2.tools.Csv.read\"");
        ResultSetMetaData meta = rs.getMetaData();
        assertEquals(2, meta.getColumnCount());
        for (int i = 0; i < len; i++) {
            rs.next();
            assertEquals("" + (i + 1), rs.getString("ID"));
            assertEquals("Ruebezahl", rs.getString("NAME"));
        }
        assertFalse(rs.next());
        rs.close();
        conn.close();
        FileUtils.delete(getBaseDir() + "/testRW.csv");
    }

}
