package org.postgresforest.tool.tests;

import java.io.*;
import java.sql.*;
import java.util.*;
import junit.framework.*;

import org.postgresforest.tool.lib.Config;
import org.postgresforest.tool.lib.Database;
import org.postgresforest.tool.lib.Instance;
import org.postgresforest.tool.lib.GSCdata;
import org.postgresforest.tool.lib.ForestToolException;
import org.postgresforest.tool.lib.SqlTokenizer;

public class TestSqlTokenizer extends TestCase {
	public boolean check(String[] tokens, SqlTokenizer t)
	{
		System.out.println("\nSQL = " + t.toString());

		for (int i=0 ; i<tokens.length ; i++)
		{
			String token = t.nextToken();
			
			System.out.println("["+ token +"],[" + tokens[i] + "]");
			assertTrue( token.equals(tokens[i]) );
		}
		return true;
	}

	public void testSqlTokenizer001()
	{
		String sql = "CREATE TABLE t1 ( uid integer primary key,name text not       null)";

		SqlTokenizer t = new SqlTokenizer(sql);

		String[] tokens = { "CREATE",
							" ",
							"TABLE",
							" ",
							"t1",
							" ",
							"(",
							" ",
							"uid",
							" ",
							"integer",
							" ",
							"primary",
							" ",
							"key",
							",",
							"name",
							" ",
							"text",
							" ",
							"not",
							"       ",
							"null",
							")" };

		check(tokens, t);
	}

    public void testSqlTokenizer002()
    {
        String sql = "CREATE TABLE ( uid integer primary key, name text not /* hoge */      null );";

        String[] tokens = { "CREATE",
							" ",
							"TABLE",
							" ",
							"(",
							" ",
							"uid",
							" ",
							"integer",
							" ",
							"primary",
							" ",
							"key",
							",",
							" ",
							"name",
							" ",
							"text",
							" ",
							"not",
							" ",
							"/* hoge */",
							"      ",
							"null",
							" ",
							")",
							";" };

        SqlTokenizer t = new SqlTokenizer(sql);

		check(tokens, t);
    }

    public void testSqlTokenizer003()
    {
        String sql = "CREATE TABLE ( uid integer primary key, name text not -- /* hoge */\n      null );";

        String[] tokens = { "CREATE",
							" ",
							"TABLE",
							" ",
							"(",
							" ",
							"uid",
							" ",
							"integer",
							" ",
							"primary",
							" ",
							"key",
							",",
							" ",
							"name",
							" ",
							"text",
							" ",
							"not",
							" ",
							"-- /* hoge */\n",
							"      ",
							"null",
							" ",
							")",
							";" };

        SqlTokenizer t = new SqlTokenizer(sql);

		check(tokens, t);
    }

    public void testSqlTokenizer004()
    {
        String sql = "UPDATE hoge SET uid=30.4, name = '私の名前わ''-- 永安です、か？'";

        String[] tokens = { "UPDATE",
							" ",
							"hoge",
							" ",
							"SET",
							" ",
							"uid",
							"=",
							"30.4",
							",",
							" ",
							"name",
							" ",
							"=",
							" ",
							"'私の名前わ''-- 永安です、か？'" };

        SqlTokenizer t = new SqlTokenizer(sql);

		check(tokens, t);
    }

	public void testSqlTokenizer005()
	{
		String sql = "SELECT * FROM t1 WHERE uid <> 100.0";

		String[] tokens = { "SELECT",
							" ",
							"*",
							" ",
							"FROM",
							" ",
							"t1",
							" ",
							"WHERE",
							" ",
							"uid",
							" ",
							"<>",
							" ",
							"100.0" };

		SqlTokenizer t = new SqlTokenizer(sql);

		check(tokens, t);
	}

    public void testSqlTokenizer006()
    {
        String sql = "SELECT 'Hello,' || ' world.'";

        String[] tokens = { "SELECT",
                            " ",
                            "'Hello,'",
                            " ",
                            "||",
                            " ",
                            "' world.'" };

        SqlTokenizer t = new SqlTokenizer(sql);

        check(tokens, t);
    }


    public void testSqlTokenizer007()
    {
        String sql = "SELECT * FROM t1 WHERE c1 @@ 'hoge'";

        String[] tokens = { "SELECT",
                            " ",
                            "*",
                            " ",
                            "FROM",
                            " ",
                            "t1",
                            " ",
                            "WHERE",
                            " ",
                            "c1",
                            " ",
                            "@@",
                            " ",
                            "'hoge'" };

        SqlTokenizer t = new SqlTokenizer(sql);

        check(tokens, t);
    }

    public void testSqlTokenizer008()
    {
        String sql = "SELECT * FROM \"t1\"";

        String[] tokens = { "SELECT",
                            " ",
                            "*",
                            " ",
                            "FROM",
                            " ",
                            "\"t1\"" };

        SqlTokenizer t = new SqlTokenizer(sql);

        check(tokens, t);
    }

    public void testSqlTokenizer009()
    {
        String sql = "SELECT * FROM \"schema\".\"table\"";

        String[] tokens = { "SELECT",
                            " ",
                            "*",
                            " ",
                            "FROM",
                            " ",
                            "\"schema\".\"table\"" };

        SqlTokenizer t = new SqlTokenizer(sql);

        check(tokens, t);
    }

    public void testSqlTokenizer101()
    {
        String sql = "CREATE TABLE t1 ( uid integer primary key,name text not      null)";

        String tableName = Database.parseTableName(sql);

		System.out.println("[table]" + tableName);

		assertTrue(tableName.equals("t1"));

		String[] colNames = Database.parseColumnNames(sql);

		assertTrue(colNames[0].equals("uid"));
		assertTrue(colNames[1].equals("name"));
    }

    public void testSqlTokenizer102()
    {
        String sql = "CREATE TABLE t1 ( uid integer primary key,"
			+ " name text not      null,"
			+ "uid2 integer references t2(uid),"
			+ "memo text)";

        String tableName = Database.parseTableName(sql);

        System.out.println("[table]" + tableName);

        assertTrue(tableName.equals("t1"));

        String[] colNames = Database.parseColumnNames(sql);

		assertTrue(colNames[0].equals("uid"));
		assertTrue(colNames[1].equals("name"));
		assertTrue(colNames[2].equals("uid2"));
		assertTrue(colNames[3].equals("memo"));
    }

    public void testSqlTokenizer103()
    {
        String sql = "CREATE TABLE t1 ( uid integer primary key,\n"
            + " name text not      null,\n"
            + "uid2 integer references t2(uid),\n"
            + "memo text)";

        String tableName = Database.parseTableName(sql);

        System.out.println("[table]" + tableName);

        assertTrue(tableName.equals("t1"));

        String[] colNames = Database.parseColumnNames(sql);

        assertTrue(colNames[0].equals("uid"));
        assertTrue(colNames[1].equals("name"));
        assertTrue(colNames[2].equals("uid2"));
        assertTrue(colNames[3].equals("memo"));
    }


    public void testSqlTokenizer104()
    {
        String sql = "CREATE TABLE \"myspc\".t1 ( uid integer primary key,\n"
            + " name text not      null,\n"
            + "uid2 integer references t2(uid),\n"
            + "memo text)";

        String tableName = Database.parseTableName(sql);

        System.out.println("[table]" + tableName);

        assertTrue(tableName.equals("myspc.t1"));

        String[] colNames = Database.parseColumnNames(sql);

        assertTrue(colNames[0].equals("uid"));
        assertTrue(colNames[1].equals("name"));
        assertTrue(colNames[2].equals("uid2"));
        assertTrue(colNames[3].equals("memo"));
    }

    public void testSqlTokenizer105()
    {
        String sql = "CREATE TABLE myspc.\"t1\" ( uid integer primary key,\n"
            + " name text not      null,\n"
            + "uid2 integer references t2(uid),\n"
            + "memo text)";

        String tableName = Database.parseTableName(sql);

        System.out.println("[table]" + tableName);

        assertTrue(tableName.equals("myspc.t1"));

        String[] colNames = Database.parseColumnNames(sql);

        assertTrue(colNames[0].equals("uid"));
        assertTrue(colNames[1].equals("name"));
        assertTrue(colNames[2].equals("uid2"));
        assertTrue(colNames[3].equals("memo"));
    }

    public void testSqlTokenizer106()
    {
        String sql = "CREATE TABLE \"myspc.t1\" ( uid integer primary key,\n"
            + " name text not      null,\n"
            + "uid2 integer references t2(uid),\n"
            + "memo text)";

        String tableName = Database.parseTableName(sql);

        System.out.println("[table]" + tableName);

        assertTrue(tableName.equals("myspc.t1"));

        String[] colNames = Database.parseColumnNames(sql);

        assertTrue(colNames[0].equals("uid"));
        assertTrue(colNames[1].equals("name"));
        assertTrue(colNames[2].equals("uid2"));
        assertTrue(colNames[3].equals("memo"));
    }

    public void testSqlTokenizer107()
    {
        String sql = "CREATE TABLE public.t1 ( uid integer primary key,\n"
            + " name text not      null,\n"
            + "uid2 integer references t2(uid),\n"
            + "memo text)";

        String tableName = Database.parseTableName(sql);

        System.out.println("[table]" + tableName);

        assertTrue(tableName.equals("t1"));

        String[] colNames = Database.parseColumnNames(sql);

        assertTrue(colNames[0].equals("uid"));
        assertTrue(colNames[1].equals("name"));
        assertTrue(colNames[2].equals("uid2"));
        assertTrue(colNames[3].equals("memo"));
    }



    public static void main(String[] args)
    {
        junit.textui.TestRunner.run(TestSqlTokenizer.class);
    }
}
