/*
 * 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.synth;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Random;
import org.h2.test.TestBase;
import org.h2.util.New;

/**
 * Tests random compare operations.
 */
public class TestRandomCompare extends TestBase {

    private ArrayList<Statement> dbs = New.arrayList();
    private int aliasId;

    /**
     * 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 {
        deleteDb("randomCompare");
        testCases();
        testRandom();
        deleteDb("randomCompare");
    }

    private void testRandom() throws Exception {
        Connection conn = getConnection("randomCompare");
        dbs.add(conn.createStatement());

        try {
            Class.forName("org.postgresql.Driver");
            Connection c2 = DriverManager.getConnection("jdbc:postgresql:test", "sa", "sa");
            dbs.add(c2.createStatement());
        } catch (Exception e) {
            // database not installed - ok
        }

        String shortest = null;
        Throwable shortestEx = null;
        /*
        drop table test;
        create table test(x0 int, x1 int);
        create index idx_test_x0 on test(x0);
        insert into test values(null, null);
        insert into test values(null, 1);
        insert into test values(null, 2);
        insert into test values(1, null);
        insert into test values(1, 1);
        insert into test values(1, 2);
        insert into test values(2, null);
        insert into test values(2, 1);
        insert into test values(2, 2);
        */
        try {
            execute("drop table test");
        } catch (Exception e) {
            // ignore
        }
        try {
            execute("drop table test cascade");
        } catch (Exception e) {
            // ignore
        }
        String sql = "create table test(x0 int, x1 int)";
        trace(sql + ";");
        execute(sql);
        sql = "create index idx_test_x0 on test(x0)";
        trace(sql + ";");
        execute(sql);
        for (int x0 = 0; x0 < 3; x0++) {
            for (int x1 = 0; x1 < 3; x1++) {
                sql = "insert into test values(" + (x0 == 0 ? "null" : x0) + ", " + (x1 == 0 ? "null" : x1) + ")";
                trace(sql + ";");
                execute(sql);
            }
        }
        Random random = new Random(1);
        for (int i = 0; i < 1000; i++) {
            StringBuilder buff = new StringBuilder();
            appendRandomCompare(random, buff);
            sql = buff.toString();
            try {
                execute(sql);
            } catch (Throwable e) {
                if (e instanceof SQLException) {
                    trace(sql);
                    fail(sql);
                    // SQLException se = (SQLException) e;
                    // System.out.println(se);
                    // System.out.println("  " + sql);
                }
                if (shortest == null || sql.length() < shortest.length()) {
                    shortest = sql;
                    shortestEx = e;
                }
            }
        }
        if (shortest != null) {
            shortestEx.printStackTrace();
            fail(shortest + " " + shortestEx);
        }
        for (int i = 0; i < 10; i++) {
            try {
                execute("drop table t" + i);
            } catch (Exception e) {
                // ignore
            }
        }
        for (Statement s : dbs) {
            s.getConnection().close();
        }
        deleteDb("randomCompare");
    }

    private void appendRandomCompare(Random random, StringBuilder buff) {
        buff.append("select * from ");
        int alias = aliasId++;
        if (random.nextBoolean()) {
            buff.append("(");
            appendRandomCompare(random, buff);
            buff.append(")");
        } else {
            buff.append("test");
        }
        buff.append(" as t").append(alias);
        if (random.nextInt(10) == 0) {
            return;
        }
        buff.append(" where ");
        int count = 1 + random.nextInt(3);
        for (int i = 0; i < count; i++) {
            if (i > 0) {
                buff.append(random.nextBoolean() ? " or " : " and ");
            }
            if (random.nextInt(10) == 0) {
                buff.append("not ");
            }
            appendRandomValue(random, buff);
            switch (random.nextInt(8)) {
            case 0:
                buff.append("=");
                appendRandomValue(random, buff);
                break;
            case 1:
                buff.append("<");
                appendRandomValue(random, buff);
                break;
            case 2:
                buff.append(">");
                appendRandomValue(random, buff);
                break;
            case 3:
                buff.append("<=");
                appendRandomValue(random, buff);
                break;
            case 4:
                buff.append(">=");
                appendRandomValue(random, buff);
                break;
            case 5:
                buff.append("<>");
                appendRandomValue(random, buff);
                break;
            case 6:
                buff.append(" is distinct from ");
                appendRandomValue(random, buff);
                break;
            case 7:
                buff.append(" is not distinct from ");
                appendRandomValue(random, buff);
                break;
            }
        }
    }

    private static void appendRandomValue(Random random, StringBuilder buff) {
        switch (random.nextInt(7)) {
        case 0:
            buff.append("null");
            break;
        case 1:
            buff.append(1);
            break;
        case 2:
            buff.append(2);
            break;
        case 3:
            buff.append(3);
            break;
        case 4:
            buff.append(-1);
            break;
        case 5:
            buff.append("x0");
            break;
        case 6:
            buff.append("x1");
            break;
        }
    }

    private void execute(String sql) throws SQLException {
        String expected = null;
        SQLException e = null;
        for (Statement s : dbs) {
            try {
                boolean result = s.execute(sql);
                if (result) {
                    String data = getResult(s.getResultSet());
                    if (expected == null) {
                        expected = data;
                    } else {
                        assertEquals(sql, expected, data);
                    }
                }
            } catch (SQLException e2) {
                // ignore now, throw at the end
                e = e2;
            }
        }
        if (e != null) {
            throw e;
        }
    }

    private static String getResult(ResultSet rs) throws SQLException {
        ArrayList<String> list = New.arrayList();
        while (rs.next()) {
            StringBuilder buff = new StringBuilder();
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                if (i > 0) {
                    buff.append(" ");
                }
                buff.append(rs.getString(i + 1));
            }
            list.add(buff.toString());
        }
        Collections.sort(list);
        return list.toString();
    }

    private void testCases() throws Exception {

        Connection conn = getConnection("randomCompare");
        Statement stat = conn.createStatement();
        ResultSet rs;

        /*
        create table test(x int);
        insert into test values(null);
        select * from (select x from test
        union all select x from test) where x is null;
        select * from (select x from test) where x is null;
         */
        stat.execute("create table test(x int)");
        stat.execute("insert into test values(null)");
        rs = stat.executeQuery("select * from (select x from test union all select x from test) where x is null");
        assertTrue(rs.next());
        rs = stat.executeQuery("select * from (select x from test) where x is null");
        assertTrue(rs.next());
        rs = stat.executeQuery("select * from (select x from test union all select x from test) where x is null");
        assertTrue(rs.next());
        assertTrue(rs.next());

        Connection conn2 = DriverManager.getConnection("jdbc:h2:mem:temp");
        conn2.createStatement().execute("create table test(x int) as select null");
        stat.execute("drop table test");
        stat.execute("create linked table test(null, 'jdbc:h2:mem:temp', null, null, 'TEST')");
        rs = stat.executeQuery("select * from (select x from test) where x is null");
        assertTrue(rs.next());
        rs = stat.executeQuery("select * from (select x from test union all select x from test) where x is null");
        assertTrue(rs.next());
        assertTrue(rs.next());
        conn2.close();

        conn.close();
        deleteDb("randomCompare");
    }

}
