package com.oracle.bugs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.mysql.jdbc.util.BaseBugReport;

public class BugReport67375 extends BaseBugReport {

  @Override
  public void setUp() throws Exception {
    Connection conn = getConnection();
    PreparedStatement pstmt = conn.prepareStatement(
        "CREATE TABLE `testtable` (\n" + 
        "  `foo` int(10) unsigned NOT NULL,\n" + 
        "  `bar` tinyint(1) NOT NULL DEFAULT 0,\n" + 
        "  PRIMARY KEY (`foo`)\n" + 
        ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
        );
    pstmt.execute();
    pstmt.close();
    conn.close();

    conn = getConnection();
    pstmt = conn.prepareStatement("INSERT INTO `testtable` values (1, 1)");
    pstmt.execute();
    pstmt.close();
    conn.close();
  }

  @Override
  public void tearDown() throws Exception {
    Connection conn = getConnection();
    PreparedStatement pstmt = conn.prepareStatement("DROP TABLE `testtable`");
    pstmt.execute();
    pstmt.close();
    conn.close();
  }

  @Override
  public void runTest() throws Exception {
    Connection conn = getConnection();
    
    // verify that there is exactly 1 row in the table, and it looks like (1, 1)
    PreparedStatement pstmt = conn.prepareStatement("SELECT `foo`, `bar` FROM `testtable` ORDER BY `foo` ASC");
    ResultSet rs = pstmt.executeQuery();
    assertTrue(rs.next());
    assertTrue(1 == rs.getInt("foo"));
    assertTrue(true == rs.getBoolean("bar"));
    assertTrue(!rs.next());
    
    rs.close();
    pstmt.close();
    conn.close();
    
    conn = getConnection();
    pstmt = conn.prepareStatement(
        "INSERT INTO `testtable` (`foo`, `bar`)\n" +
        "VALUES (1, 1)\n" +
        "ON DUPLICATE KEY UPDATE `bar` = 0"
    );
    
    pstmt.executeUpdate();
    int rowsAffected = pstmt.getUpdateCount();
    assertTrue(
        String.format("with ON DUPLICATE KEY UPDATE, rowsAffected is 2 when an existing row is updated, but got rowsAffected = %s", rowsAffected),
        rowsAffected == 2
    );
    
    pstmt.close();
    conn.close();
    
    // verify that there is exactly 1 row in the table, and it looks like (1, 0)
    conn = getConnection();
    pstmt = conn.prepareStatement("SELECT `foo`, `bar` FROM `testtable` ORDER BY `foo` ASC");
    rs = pstmt.executeQuery();
    assertTrue(rs.next());
    assertTrue(1 == rs.getInt("foo"));
    assertTrue(false == rs.getBoolean("bar"));
    assertTrue(!rs.next());

    rs.close();
    pstmt.close();
    conn.close();
    
    // the insert is exactly the same as above.
    conn = getConnection();
    pstmt = conn.prepareStatement(
        "INSERT INTO `testtable` (`foo`, `bar`)\n" +
        "VALUES (1, 1)\n" +
        "ON DUPLICATE KEY UPDATE `bar` = 0"
    );
    
    pstmt.executeUpdate();
    rowsAffected = pstmt.getUpdateCount();
    assertTrue(
        String.format("with ON DUPLICATE KEY UPDATE, rowsAffected is 0 if an existing row is set to its current values, but got rowsAffected = %s", rowsAffected),
        rowsAffected == 0
    );
    pstmt.close();
    conn.close();
  }

  public static void main(String[] args) throws Exception {
    new BugReport67375().run();
  }
}