/**
 * Demo for an escape bug in Columnstore during delete
 */

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

public class CSEscapeBug {
	
	public static void main(String[] args) throws Exception {
		
		final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
		final String HOST = "192.168.56.33";
		final int PORT = 3306;
		final String USER = "informatica";
		final String PASSWORD = "informatica";
		final String DB = "informatica";
		
		final String test_value = "ab';CREATE TABLE pwnd (i int) engine=columnstore; -- "; // this value fails
		//final String test_value = "this value is okay";                                  // this value is okay
		
   		// get the JDBC connection
		Class.forName(JDBC_DRIVER);
		String connectionURL = "jdbc:mariadb://" + HOST + ":" + Integer.toString(PORT) + "/" + DB;
    	Connection conn = DriverManager.getConnection(connectionURL, USER, PASSWORD);
    	
    	// Create the test tables
    	Statement stmt = conn.createStatement();
    	stmt.executeUpdate("DROP TABLE IF EXISTS inno_escape");
    	stmt.executeUpdate("CREATE TABLE inno_escape (v varchar(255)) engine=innodb");
    	
    	stmt.executeUpdate("DROP TABLE IF EXISTS cs_escape");
    	stmt.executeUpdate("CREATE TABLE cs_escape (v varchar(255)) engine=columnstore");
    	
    	// Display the number of rows before insert
    	System.out.println("number of rows in inno_escape before insert: " + getRowCount(stmt, "inno_escape"));
    	System.out.println("number of rows in cs_escape before insert: " + getRowCount(stmt, "cs_escape"));
    	
    	// Insert the test values
    	PreparedStatement pstmt = conn.prepareStatement("INSERT INTO inno_escape VALUES (?)");
    	pstmt.setString(1, test_value);
    	pstmt.executeUpdate();
    	
    	pstmt = conn.prepareStatement("INSERT INTO cs_escape VALUES (?)");
    	pstmt.setString(1, test_value);
    	pstmt.executeUpdate();
    	
    	// Display the number of rows after insert
    	System.out.println("number of rows in inno_escape after insert: " + + getRowCount(stmt, "inno_escape"));
    	System.out.println("number of rows in cs_escape after insert: " + getRowCount(stmt, "cs_escape"));
    	
    	// Delete the test values 
    	pstmt = conn.prepareStatement("DELETE FROM inno_escape WHERE v = ? ");
    	pstmt.setString(1, test_value);
    	pstmt.executeUpdate();
    	
    	pstmt = conn.prepareStatement("DELETE FROM cs_escape WHERE v = ? ");  // <--- HERE STARTS THE BUG
    	pstmt.setString(1, test_value);
    	pstmt.executeUpdate();                                                // <--- AND HERE ENDS IT
    	
    	// Display the number of rows
    	System.out.println("number of rows in inno_escape after delete: " + + getRowCount(stmt, "inno_escape"));
    	System.out.println("number of rows in cs_escape after delete: " + getRowCount(stmt, "cs_escape"));
    	
    	// Close the connection
    	stmt.close();
    	pstmt.close();
    	conn.close();
	}
	
	// returns the row count of a table
	private static int getRowCount(Statement stmt, String table) throws Exception{
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS TOTAL FROM " + table);
		while(rs.next()){
			return rs.getInt("TOTAL");
		}
		return -1;
	}
}