/**
 * Project: ${zebra-client.aid}
 * 
 * File Created at 2011-6-30
 * $Id$
 * 
 * Copyright 2010 dianping.com.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Dianping Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with dianping.com.
 */
package com.dianping.zebra.shard.jdbc;

import junit.framework.Assert;
import org.junit.Test;

import com.dianping.zebra.shard.jdbc.base.SingleDBBaseTestCase;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Date;

/**
 * 
 * @author Leo Liang
 * 
 */
public class SingleDBPreparedStatementGroupFollowNoteIntegrationTest extends SingleDBBaseTestCase {

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.dianping.zebra.jdbc.ZebraDBTestCase#getDBUrl()
	 */
	@Override
	protected String getDBUrl() {
		return "jdbc:h2:mem:zebra_ut;DB_CLOSE_DELAY=-1";
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.dianping.zebra.jdbc.ZebraDBTestCase#getDataSetFilePath()
	 */
	@Override
	protected String getDataSetFilePath() {
		return "db-datafiles/data-singledb-group-follownote-integrationtest.xml";
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.dianping.zebra.jdbc.ZebraDBTestCase#getCreateTableScriptPath()
	 */
	@Override
	protected String getCreateTableScriptPath() {
		return "db-datafiles/createtable-singledb-group-follownote-integrationtest.xml";
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.dianping.zebra.jdbc.ZebraDBTestCase#getSpringConfigLocations()
	 */
	@Override
	protected String[] getSpringConfigLocations() {
		return new String[] { "ctx-singledb-group-follownote-integrationtest.xml" };
	}

	@Test
	public void test1() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT FN.FollowNoteID AS NoteID, N.GroupID, FN.UserID, CONCAT('RE:', N.NoteTitle) AS NoteTitle, '' AS NoteBody, FN.AddTime, FN.LastIP, G.GroupName, G.GroupPermaLink, FN.NoteID AS GroupNoteID FROM DP_GroupFollowNote FN JOIN DP_GroupNote N ON N.NoteID = FN.NoteID JOIN DP_Group G ON N.GroupID = G.GroupID WHERE FN.NoteClass = 3 ORDER BY FN.AddTime LIMIT 0, 10");
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			int count = 0;
			System.out.println("Test 1 ------------------------------------------------------");
			while (rs.next()) {
				count++;
				System.out
						.println(" NoteID: " + rs.getObject(1) + " GroupID: " + rs.getObject(2) + " UserID: "
								+ rs.getObject(3) + " NoteTitle: " + rs.getString(4) + " NoteBody: " + rs.getString(5)
								+ " AddTime: " + rs.getObject(6) + " LastIP: " + rs.getString(7) + " GroupName: "
								+ rs.getString(8) + " GroupPermaLink: " + rs.getString(9) + " GroupNoteID: "
								+ rs.getObject(10));
			}
			System.out.println("Test 1 ------------------------------------------------------");
			Assert.assertEquals(8, count);
		} catch (Exception e) {
			e.printStackTrace();
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test2() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("UPDATE DP_GroupFollowNote SET NoteClass = ? WHERE UserID = ?");
			stmt.setInt(1, 4);
			stmt.setInt(2, 1);
			stmt.execute();

			Statement stmt2 = conn.createStatement();
			stmt2.execute("select count(*) total from DP_GroupFollowNote where NoteClass=4");
			ResultSet rs = stmt2.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(3, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test3() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT N.GroupID, F.FollowNoteID, F.UserID, F.NoteId FROM DP_GroupFollowNote F INNER JOIN DP_GroupNote N ON N.NoteID = F.NoteID WHERE F.UserID = ? AND F.NoteClass <> 3");
			stmt.setInt(1, 0);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			System.out.println("Test 3 ------------------------------------------------------");
			int count = 0;
			while (rs.next()) {
				count++;
				System.out.println(" GroupID: " + rs.getObject(1) + " FollowNoteID: " + rs.getObject(2) + " UserID: "
						+ rs.getObject(3) + " NoteId: " + rs.getObject(4));
			}
			System.out.println("Test 3 ------------------------------------------------------");
			Assert.assertEquals(2, count);
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test4() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? LIMIT ?, ?");
			stmt.setInt(1, 0);
			stmt.setInt(2, 7);
			stmt.setInt(3, 1);
			stmt.setInt(4, 10);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			System.out.println("Test 4 ------------------------------------------------------");
			int count = 0;
			while (rs.next()) {
				count++;
				System.out.println(" FollowNoteID: " + rs.getObject(1));
			}
			System.out.println("Test 4 ------------------------------------------------------");
			Assert.assertEquals(1, count);
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test5() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ?");
			stmt.setInt(1, 0);
			stmt.setInt(2, 7);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(2, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test6() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? AND UserID = ? LIMIT ?, ?");
			stmt.setInt(1, 0);
			stmt.setInt(2, 7);
			stmt.setInt(3, 0);
			stmt.setInt(4, 0);
			stmt.setInt(5, 10);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			int count = 0;
			System.out.println("Test 6 ------------------------------------------------------");
			while (rs.next()) {
				count++;
				System.out.println("FollowNoteID: " + rs.getObject("FollowNoteID"));
			}
			System.out.println("Test 6 ------------------------------------------------------");
			Assert.assertEquals(2, count);
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test7() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? AND UserID = ?");
			stmt.setInt(1, 0);
			stmt.setInt(2, 7);
			stmt.setInt(3, 0);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(2, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test8() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("INSERT INTO DP_GroupFollowNote (NoteID, UserID, NoteClass, ADDTIME, UpdateTime, LastIP, DCashNumber) values(?,?,?,?,?,?,?)");
			stmt.setInt(1, 8);
			stmt.setInt(2, 8);
			stmt.setInt(3, 1);
			stmt.setTimestamp(4, new Timestamp(new Date().getTime()));
			stmt.setTimestamp(5, new Timestamp(new Date().getTime()));
			stmt.setString(6, "0.0.0.0");
			stmt.setInt(7, 10);
			stmt.execute();
			Class.forName(getDriverName());
			Connection conn2 = DriverManager.getConnection(getDBUrl());
			Statement stmt2 = conn2.createStatement();
			stmt2.execute("select count(*) from DP_GroupFollowNote_noteid0 where UserID = 8");
			ResultSet rs = stmt2.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(1, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test9() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE FollowNoteID = ?");
			stmt.setInt(1, 140);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			int count = 0;
			while (rs.next()) {
				count++;
				Assert.assertEquals(140, rs.getInt("FollowNoteID"));
			}
			Assert.assertEquals(1, count);
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test10() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE (NoteClass = 1 OR (NoteClass = 4 AND UserID = ?)) AND NoteID = ? AND FollowNoteID <= ?");
			stmt.setInt(1, 0);
			stmt.setInt(2, 7);
			stmt.setInt(3, 141);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(1, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test11() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT COUNT(DISTINCT(UserID)) FROM DP_GroupFollowNote WHERE NoteID = ?");
			stmt.setInt(1, 7);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(1, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test12() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT COUNT(FollowNoteID) FROM DP_GroupFollowNote WHERE NoteID = ? AND UserID = ?");
			stmt.setInt(1, 7);
			stmt.setInt(2, 0);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(3, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test13() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT * FROM DP_GroupFollowNote WHERE NoteID = ? AND NoteClass = 1 ORDER BY FollowNoteID DESC LIMIT 1");
			stmt.setInt(1, 7);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(141, rs.getInt("FollowNoteID"));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test14() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT COUNT(FollowNoteID) F FROM DP_GroupFollowNote F INNER JOIN DP_GroupNote N ON F.NoteID = N.NoteID AND N.GroupID=? AND N.Status = 1 WHERE F.UserID = ? AND F.NoteClass = 1");
			stmt.setInt(1, 100);
			stmt.setInt(2, 0);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(1, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test15() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("UPDATE DP_GroupFollowNote SET DCashNumber = DCashNumber + ? WHERE FollowNoteID = ?");
			stmt.setInt(1, 10);
			stmt.setInt(2, 142);
			stmt.execute();
			Statement stmt2 = conn.createStatement();
			stmt2.execute("select DCashNumber from DP_GroupFollowNote where FollowNoteID=142");
			ResultSet rs = stmt2.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(21, rs.getShort(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test16() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT DISTINCT GN.NoteID F FROM DP_GroupNote GN INNER JOIN DP_Group G ON GN.GroupID = G.GroupID AND G.Status = 0 INNER JOIN DP_GroupFollowNote GFN ON GN.NoteID = GFN.NoteID WHERE (GN.Status = 1 OR (GN.Status = 3 AND GN.UserID = ?)) AND GN.UserID <> ? AND GFN.UserID = ? AND GFN.NoteClass = 1");
			stmt.setInt(1, 7);
			stmt.setInt(2, 7);
			stmt.setInt(3, 7);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(0, rs.getLong(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test17() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("UPDATE DP_GroupFollowNote SET LastIP = ? WHERE FollowNoteID = ?");
			stmt.setString(1, "5.1.1.1");
			stmt.setInt(2, 142);
			stmt.execute();
			Statement stmt2 = conn.createStatement();
			stmt2.execute("select LastIP from DP_GroupFollowNote where FollowNoteID=142");
			ResultSet rs = stmt2.getResultSet();
			while (rs.next()) {
				Assert.assertEquals("5.1.1.1", rs.getString(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test
	public void test18() throws Exception {
		DataSource ds = (DataSource) context.getBean("zebraDS");
		Connection conn = null;
		try {
			conn = ds.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("UPDATE DP_GroupFollowNote SET NoteClass = ? WHERE FollowNoteID = ?");
			stmt.setInt(1, 10);
			stmt.setInt(2, 142);
			stmt.execute();
			Statement stmt2 = conn.createStatement();
			stmt2.execute("select NoteClass from DP_GroupFollowNote where FollowNoteID=142");
			ResultSet rs = stmt2.getResultSet();
			while (rs.next()) {
				Assert.assertEquals(10, rs.getInt(1));
			}
		} catch (Exception e) {
			Assert.fail();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

}