content: /*
   * Copyright (c) 2002, 2020, 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;
          }
      }
  }
  