package org.postgresforest.tool.tests;

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

import org.postgresforest.tool.Logger;
import org.postgresforest.tool.lib.Config;
import org.postgresforest.tool.lib.Database;
import org.postgresforest.tool.lib.Instance;
import org.postgresforest.tool.lib.Table;
import org.postgresforest.tool.lib.GSCdata;
import org.postgresforest.tool.lib.PartitionUtils;
import org.postgresforest.tool.lib.ForestToolException;

public class TestTable2 extends TestCase {
	String user = "snaga";
	String pass = "";

	public void setUp()
	{
	}

	public void tearDown()
	{
	}

	private boolean dropdb(String dbname, String portNumber)
	{
		boolean rc = false;

		try {
            Thread.sleep(2000);

			String url = "jdbc:postgresql://localhost:" + portNumber + "/postgres";
			System.out.println(url);

			Connection con = DriverManager.getConnection(url, user, pass);

			con.createStatement().executeUpdate("DROP DATABASE " + dbname);
			con.close();
			rc = true;

            Thread.sleep(2000);
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
		}

		return rc;
	}

	public void testInitialize001()
	{
		GSCdata gsc = null;

		dropdb("gsctest", "9998");
		dropdb("testdb", "9998");
		dropdb("testdb", "9999");

		try {
			gsc = GSCdata.initialize("localhost", "9998", "gsctest",
									 user, pass);
			assertTrue(gsc!=null);

			gsc.registerInstance("localhost", "9999");

			Database d = gsc.createDatabase("testdb");

			assertTrue( d!=null );
			
			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * INTEGERでパーティション化
	 */
	public void testPart001()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t001 ( uid INTEGER PRIMARY KEY, name TEXT NOT NULL ); " +
									"CREATE INDEX t1_name_idx ON t001(name)");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 2, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * INT2でパーティション化
	 */
	public void testPart002()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t002 ( uid INT2 PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			Logger.trace(e);
			fail();
		}
	}

	/*
	 * INT4でパーティション化
	 */
	public void testPart003()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t003 ( uid INT4 PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * OIDでパーティション化
	 */
	public void testPart004()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t101 ( uid oid PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * CHARでパーティション化
	 */
	public void testPart201()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t201 ( uid char PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * CHAR(10)でパーティション化
	 */
	public void testPart202()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t202 ( uid char(10) PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * VARCHARでパーティション化
	 */
	public void testPart203()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t203 ( uid varchar PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * VARCHAR(10)でパーティション化
	 */
	public void testPart204()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t204 ( uid varchar(10) PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * textでパーティション化
	 */
	public void testPart205()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t205 ( uid text PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * nameでパーティション化
	 */
	public void testPart206()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t206 ( uid name PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * dateでパーティション化
	 */
	public void testPart301()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t301 ( uid date PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * timeでパーティション化
	 */
	public void testPart302()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t302 ( uid time PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * timetzでパーティション化
	 */
	public void testPart303()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t303 ( uid timetz PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * timestampでパーティション化
	 */
	public void testPart304()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t304 ( uid timestamp PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * timestamptzでパーティション化
	 */
	public void testPart305()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t305 ( uid timestamptz PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * INTEGERで二分割
	 */
	public void testPart401()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t401 ( uid INTEGER  PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 2, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * INTEGERで三分割
	 */
	public void testPart402()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t402 ( uid INTEGER  PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 3, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}


	/*
	 * INTEGERで四分割
	 */
	public void testPart403()
	{
		GSCdata gsc = null;

		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t403 ( uid INTEGER  PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 4, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}
	}

	/*
	 * INTEGERで2分割。
	 * パーティションテーブル１に1000件をINSERT。
	 * 多重化テーブルに戻しても同じ件数が存在することを確認。
	 */
	public void testPart501()
	{
		GSCdata gsc = null;

		/*
		 * パーティション１テーブル作成
		 */
		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.createTable("CREATE TABLE t501 ( uid INTEGER  PRIMARY KEY, name TEXT NOT NULL );");

			String[] pk = { "uid" };

			assertTrue( t.part(pk, 2, null) );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}

		/*
		 * 1000件INSERT
		 */
		try {
			Class.forName("org.postgresforest.Driver");

			Connection con = DriverManager.getConnection("jdbc:postgresforest:GSC=//localhost:9998/gsctest@testdb",
														 "snaga", "snaga");
			con.setAutoCommit(false);

			Statement stmt = con.createStatement();

			for (int i=0 ; i<1000 ; i++)
			{
				stmt.executeUpdate("INSERT INTO t501 VALUES ( " + i + ", '" + i + "');");
			}

			con.commit();
			con.close();
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
			fail();
		}

		/*
		 * 1000件あるか確認
		 */
		try {
			Class.forName("org.postgresforest.Driver");

			Connection con = DriverManager.getConnection("jdbc:postgresforest:GSC=//localhost:9998/gsctest@testdb",
														 "snaga", "snaga");
			con.setAutoCommit(false);

			Statement stmt = con.createStatement();

			ResultSet rs = stmt.executeQuery("SELECT count(*) FROM t501");

			assertTrue( rs.next() );
			assertTrue( rs.getInt(1)==1000 );

			con.commit();
			con.close();
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
			fail();
		}

		/*
		 * 多重化テーブルに戻す
		 */
		try {
			gsc = new GSCdata("localhost", "9998", "gsctest",
							  user, pass);
			assertTrue(gsc!=null);

			Database d = gsc.getDatabase("testdb");

			Table t = d.getTable("t501");

			assertTrue( t.unpart() );

			gsc.close();
		}
		catch (ForestToolException e)
		{
			System.out.println(e.getMessage());
			fail();
		}

		/*
		 * 1000件あるか確認
		 */
		try {
			Class.forName("org.postgresforest.Driver");

			Connection con = DriverManager.getConnection("jdbc:postgresforest:GSC=//localhost:9998/gsctest@testdb",
														 "snaga", "snaga");
			con.setAutoCommit(false);

			Statement stmt = con.createStatement();

			ResultSet rs = stmt.executeQuery("SELECT count(*) FROM t501");

			assertTrue( rs.next() );
			assertTrue( rs.getInt(1)==1000 );

			con.commit();
			con.close();
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
			fail();
		}

	}


	public static void main(String[] args)
	{
		try {
			Class.forName("org.postgresql.Driver");
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
			System.exit(-1);
		}

		junit.textui.TestRunner.run(TestTable2.class);
	}
}
