/*
 * Copyright (c) 2002, 2023, Oracle and/or its affiliates.
 *
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, version 2.0, as published by the
 * Free Software Foundation.
 *
 * This program is also distributed with certain software (including but not
 * limited to OpenSSL) that is licensed under separate terms, as designated in a
 * particular file or component or in included license documentation. The
 * authors of MySQL hereby grant you an additional permission to link the
 * program and your derivative works with the separately licensed software that
 * they have included with MySQL.
 *
 * Without limiting anything contained in the foregoing, this file, which is
 * part of MySQL Connector/J, is also subject to the Universal FOSS Exception,
 * version 1.0, a copy of which can be found at
 * http://oss.oracle.com/licenses/universal-foss-exception.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License, version 2.0,
 * for more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
 */

package testsuite.perf;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.NumberFormat;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import com.mysql.cj.conf.PropertyDefinitions;

import testsuite.BaseTestCase;

/**
 * Simple performance testing unit test.
 */
public class LoadStorePerfTest extends BasePerfTest {

    /** The table type to use (only for MySQL), 'HEAP' by default */
    private String tableType = "HEAP";

    private boolean takeMeasurements = false;

    private boolean useColumnNames = false;

    private boolean largeResults = false;

    @BeforeEach
    public void setUp() throws Exception {
        String newTableType = System.getProperty(PropertyDefinitions.SYSP_testsuite_loadstoreperf_tabletype);

        this.largeResults = "TRUE".equalsIgnoreCase(System.getProperty(PropertyDefinitions.SYSP_testsuite_loadstoreperf_useBigResults));

        if (newTableType != null && newTableType.length() > 0) {
            this.tableType = newTableType;

            System.out.println("Using specified table type of '" + this.tableType + "'");
        }

        try {
            this.stmt.executeUpdate("DROP TABLE perfLoadStore");
        } catch (SQLException sqlEx) {
            // ignore
        }

        String dateTimeType = "DATETIME";

        if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
            dateTimeType = "TIMESTAMP";
        }

        //
        // Approximate a run-of-the-mill entity in a business application
        //
        String query = "CREATE TABLE perfLoadStore (priKey INT NOT NULL, fk1 INT NOT NULL, fk2 INT NOT NULL, dtField " + dateTimeType
                + ", charField1 CHAR(32), charField2 CHAR(32), charField3 CHAR(32), charField4 CHAR(32), intField1 INT, intField2 INT, "
                + "intField3 INT, intField4 INT, doubleField1 DECIMAL, doubleField2 DOUBLE, doubleField3 DOUBLE, doubleField4 DOUBLE,"
                + "PRIMARY KEY (priKey))";

        if (BaseTestCase.dbUrl.indexOf("mysql") != -1) {
            query += "ENGINE =" + this.tableType;
        }

        this.stmt.executeUpdate(query);

        String currentDateValue = "NOW()";

        if (BaseTestCase.dbUrl.indexOf("sqlserver") != -1) {
            currentDateValue = "GETDATE()";
        }

        if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
            currentDateValue = "CURRENT_TIMESTAMP";
        }

        int numLoops = 1;

        if (this.largeResults) {
            numLoops = 32;
        }

        System.out.println("Inserting " + numLoops + " rows to retrieve...");

        for (int i = 0; i < numLoops; i++) {
            this.stmt.executeUpdate("INSERT INTO perfLoadStore (priKey, fk1, fk2, dtField, charField1, charField2, charField3, charField4, "
                    + "intField1, intField2, intField3, intField4, doubleField1, doubleField2, doubleField3, doubleField4) VALUES (" + i + "," // priKey
                    + "2," // fk1
                    + "3," // fk2
                    + currentDateValue + "," // dtField
                    + "'0123456789ABCDEF0123456789ABCDEF'," // charField1
                    + "'0123456789ABCDEF0123456789ABCDEF'," // charField2
                    + "'0123456789ABCDEF0123456789ABCDEF'," // charField3
                    + "'0123456789ABCDEF0123456789ABCDEF'," // charField4
                    + "7," // intField1
                    + "8," // intField2
                    + "9," // intField3
                    + "10," // intField4
                    + "1.20," // doubleField1
                    + "2.30," // doubleField2
                    + "3.40," // doubleField3
                    + "4.50" // doubleField4
                    + ")");
        }
    }

    @AfterEach
    public void tearDown() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE perfLoadStore");
        } catch (SQLException sqlEx) {
            // ignore
        }
    }

    /**
     * Tests and times 1000 load/store type transactions
     *
     * @throws Exception
     *             if an error occurs
     */
    @Test
    public void test1000Transactions() throws Exception {
        this.takeMeasurements = false;
        warmUp();
        this.takeMeasurements = true;
        doIterations(29);

        reportResults("\n\nResults for instance # 1: ");
    }

    /**
     * Runs one iteration of the test.
     *
     * @see testsuite.perf.BasePerfTest#doOneIteration()
     */
    @Override
    protected void doOneIteration() throws Exception {
        PreparedStatement pStmtStore = this.conn.prepareStatement("UPDATE perfLoadStore SET priKey = ?, fk1 = ?, fk2 = ?, dtField = ?, charField1 = ?, "
                + "charField2 = ?, charField3 = ?, charField4 = ?, intField1 = ?, intField2 = ?, intField3 = ?, intField4 = ?, doubleField1 = ?,"
                + "doubleField2 = ?, doubleField3 = ?, doubleField4 = ? WHERE priKey=?");
        PreparedStatement pStmtCheck = this.conn.prepareStatement("SELECT COUNT(*) FROM perfLoadStore WHERE priKey=?");
        PreparedStatement pStmtLoad = null;

        if (this.largeResults) {
            pStmtLoad = this.conn.prepareStatement("SELECT priKey, fk1, fk2, dtField, charField1, charField2, charField3, charField4, intField1, "
                    + "intField2, intField3, intField4, doubleField1, doubleField2, doubleField3, doubleField4 FROM perfLoadStore");
        } else {
            pStmtLoad = this.conn.prepareStatement("SELECT priKey, fk1, fk2, dtField, charField1, charField2, charField3, charField4, intField1, "
                    + "intField2, intField3, intField4, doubleField1, doubleField2, doubleField3, doubleField4 FROM perfLoadStore WHERE priKey=?");
        }

        NumberFormat numFormatter = NumberFormat.getInstance();
        numFormatter.setMaximumFractionDigits(4);
        numFormatter.setMinimumFractionDigits(4);

        int transactionCount = 5000;

        if (this.largeResults) {
            transactionCount = 50;
        }

        long begin = System.currentTimeMillis();

        for (int i = 0; i < transactionCount; i++) {
            this.conn.setAutoCommit(false);
            pStmtCheck.setInt(1, 1);
            this.rs = pStmtCheck.executeQuery();

            while (this.rs.next()) {
                this.rs.getInt(1);
            }

            this.rs.close();

            if (!this.largeResults) {
                pStmtLoad.setInt(1, 1);
            }

            this.rs = pStmtLoad.executeQuery();

            if (this.rs.next()) {
                int key = this.rs.getInt(1);

                if (!this.useColumnNames) {
                    pStmtStore.setInt(1, key); // priKey
                    pStmtStore.setInt(2, this.rs.getInt(2)); // fk1
                    pStmtStore.setInt(3, this.rs.getInt(3)); // fk2
                    pStmtStore.setTimestamp(4, this.rs.getTimestamp(4)); // dtField
                    pStmtStore.setString(5, this.rs.getString(5)); // charField1
                    pStmtStore.setString(6, this.rs.getString(7)); // charField2
                    pStmtStore.setString(7, this.rs.getString(7)); // charField3
                    pStmtStore.setString(8, this.rs.getString(8)); // charField4
                    pStmtStore.setInt(9, this.rs.getInt(9)); // intField1
                    pStmtStore.setInt(10, this.rs.getInt(10)); // intField2
                    pStmtStore.setInt(11, this.rs.getInt(11)); // intField3
                    pStmtStore.setInt(12, this.rs.getInt(12)); // intField4
                    pStmtStore.setDouble(13, this.rs.getDouble(13)); // doubleField1
                    pStmtStore.setDouble(14, this.rs.getDouble(14)); // doubleField2
                    pStmtStore.setDouble(15, this.rs.getDouble(15)); // doubleField3
                    pStmtStore.setDouble(16, this.rs.getDouble(16)); // doubleField4

                    pStmtStore.setInt(17, key);
                } else {
                    /*
                     * "UPDATE perfLoadStore SET " + "priKey = ?, " + "fk1 = ?, " +
                     * "fk2 = ?, " + "dtField = ?, " + "charField1 = ?, " +
                     * "charField2 = ?, " + "charField3 = ?, " + "charField4 = ?, " +
                     * "intField1 = ?, " + "intField2 = ?, " + "intField3 = ?, " +
                     * "intField4 = ?, " + "doubleField1 = ?," + "doubleField2 =
                     * ?," + "doubleField3 = ?," + "doubleField4 = ?" + " WHERE
                     * priKey=?");
                     */
                    pStmtStore.setInt(1, key); // priKey
                    pStmtStore.setInt(2, this.rs.getInt("fk1")); // fk1
                    pStmtStore.setInt(3, this.rs.getInt("fk2")); // fk2
                    pStmtStore.setTimestamp(4, this.rs.getTimestamp("dtField")); // dtField
                    pStmtStore.setString(5, this.rs.getString("charField1")); // charField1
                    pStmtStore.setString(6, this.rs.getString("charField2")); // charField2
                    pStmtStore.setString(7, this.rs.getString("charField3")); // charField3
                    pStmtStore.setString(8, this.rs.getString("charField4")); // charField4
                    pStmtStore.setInt(9, this.rs.getInt("intField1")); // intField1
                    pStmtStore.setInt(10, this.rs.getInt("intField2")); // intField2
                    pStmtStore.setInt(11, this.rs.getInt("intField3")); // intField3
                    pStmtStore.setInt(12, this.rs.getInt("intField4")); // intField4
                    pStmtStore.setDouble(13, this.rs.getDouble("doubleField1")); // doubleField1
                    pStmtStore.setDouble(14, this.rs.getDouble("doubleField2")); // doubleField2
                    pStmtStore.setDouble(15, this.rs.getDouble("doubleField3")); // doubleField3
                    pStmtStore.setDouble(16, this.rs.getDouble("doubleField4")); // doubleField4

                    pStmtStore.setInt(17, key);
                }

                pStmtStore.executeUpdate();
            }

            this.rs.close();

            this.conn.commit();
            this.conn.setAutoCommit(true);
        }

        pStmtStore.close();
        pStmtCheck.close();
        pStmtLoad.close();

        long end = System.currentTimeMillis();

        long timeElapsed = end - begin;

        double timeElapsedSeconds = (double) timeElapsed / 1000;
        double tps = transactionCount / timeElapsedSeconds;

        if (this.takeMeasurements) {
            addResult(tps);
            System.out.print("1 [ " + numFormatter.format(getMeanValue()) + " ] ");
        } else {
            System.out.println("Warm-up: " + tps + " trans/sec");
        }
    }

    /**
     * Runs the test 10 times to get JIT going, and GC going
     *
     * @throws Exception
     *             if an error occurs.
     */
    protected void warmUp() throws Exception {
        try {
            System.out.print("Warm-up period (10 iterations)");

            for (int i = 0; i < 10; i++) {
                doOneIteration();
                System.out.print(".");
            }

            System.out.println();
            System.out.println("Warm-up period ends");
            System.out.println("\nUnits for this test are transactions/sec.");
        } catch (Exception ex) {
            ex.printStackTrace();

            throw ex;
        }
    }

}
