testself.java

用于数据库数据潜移

      for (int i = 0; i < b2n.length; i++) {
        if (b2[i] != b2n[i]) {
          mismatch = true;
        }
      }

//            s = "drop table TabProfile";
//            sStatement.execute(s);
      s = "create table obj(id int,o object)";

      sStatement.execute(s);

      s = "insert into obj values(?,?)";
      p = cConnection.prepareStatement(s);

      p.setInt(1, 1);

      int[] ia1 = {
          1, 2, 3
      };

      p.setObject(2, ia1);
      p.executeUpdate();
      p.clearParameters();
      p.setInt(1, 2);

      java.awt.Rectangle r1 = new java.awt.Rectangle(10, 11, 12, 13);

      p.setObject(2, r1);
      p.executeUpdate();

      r = sStatement.executeQuery("SELECT o FROM obj ORDER BY id DESC");

      r.next();

      java.awt.Rectangle r2 = (java.awt.Rectangle) r.getObject(1);

      if (r2.x != 10 || r2.y != 11 || r2.width != 12
          || r2.height != 13) {
        throw new Exception("Object data error: Rectangle");
      }

      r.next();

      int[] ia2 = (int[]) (r.getObject(1));

      if (ia2[0] != 1 || ia2[1] != 2 || ia2[2] != 3
          || ia2.length != 3) {
        throw new Exception("Object data error: int[]");
      }

//            s = "drop table obj";
//            sStatement.execute(s);
      sStatement.close();
    }
    catch (Exception e) {
      print("");
      print("TabProfile error: " + e);
      print("with SQL command: " + s);
      e.printStackTrace();
    }
  }

  static void readTabProfileTest(Statement sStatement) throws Exception {

    String s = "select * from TabProfile where id=-2";
    ResultSet r = sStatement.executeQuery(s);

    r.next();

    if (!r.getString(2).equals("\"Birdie\"'s car ?")) {
      throw new Exception("Unicode error.");
    }

    boolean mismatch = false;
    byte[] b2n = r.getBytes(4);

    for (int i = 0; i < b2n.length; i++) {
      if (b2[i] != b2n[i]) {
        mismatch = true;
      }
    }

    r.close();

    s = "select * from TabProfile where id=10";
    r = sStatement.executeQuery(s);

    r.next();

    byte[] b1n = r.getBytes(4);

    for (int i = 0; i < b1n.length; i++) {
      if (b1[i] != b1n[i]) {
        mismatch = true;
      }
    }

    r.close();
  }

  static void testMarotest(Connection cConnection, boolean persistent) {

    Statement sStatement = null;
    ResultSet r;
    String s = "";
    long start;
    boolean bDropError = false;
    String name = persistent ? "Persistent"
        : "Memory";

    print(name + " Marotest");

    try {
      sStatement = cConnection.createStatement();
    }
    catch (Exception e) {
      e.printStackTrace();
      print("Marotest init error: " + e.getMessage());
    }

    try {

      // test duplicate keys & small transaction rollback
      s = "CREATE TABLE marotest (id int PRIMARY KEY, dat int);"
          + "INSERT INTO marotest VALUES (1,0);"
          + "INSERT INTO marotest VALUES (2,0);"
          + "INSERT INTO marotest VALUES (2,0);";

      try {
        sStatement.execute(s);

        s = "";
      }
      catch (Exception e) {}

      if (s.equals("")) {
        throw new Exception("Duplicate key gave no error on insert");
      }

      try {
        s = "UPDATE marotest SET id=1, dat=-1 WHERE dat=0";

        sStatement.execute(s);

        s = "";
      }
      catch (Exception e) {}

      if (s.equals("")) {
        throw new Exception("Duplicate key gave no error on update");
      }

      int count = 0;

      s = "SELECT *, id as marotest_id FROM marotest";
      r = sStatement.executeQuery(s);

      while (r.next()) {
        r.getFloat(1);
        r.getString("ID");
        r.getInt("DAT");
        r.getInt("MAROTEST_ID");

        if (r.getShort("DAT") != 0) {
          throw new Exception("Bad update worked");
        }

        r.getLong("DAT");
        r.getString(2);
        r.getObject("ID");
        r.clearWarnings();

        try {

          // this must throw an error
          r.getTimestamp("Timestamp?");

          count = 99;
        }
        catch (Exception e) {}

        count++;
      }

      r.close();

      if (count != 2) {
        throw new Exception("Should have 2 but has " + count
                            + " rows");
      }

      // test database meta data
      DatabaseMetaData dbMeta = cConnection.getMetaData();

      r = dbMeta.getColumns(null, "DBO", "MAROTEST", "%");

      while (r.next()) {
        s = r.getString(4).trim(); // COLUMN_NAME

        int i = r.getInt(5); // DATA_TYPE

        s += i + r.getString("TYPE_NAME");
        i = r.getInt(7); // COLUMN_SIZE
        i = r.getInt(9); // "Decimal_Digits"
        i = r.getInt(11); // NULLABLE
        s = s.toUpperCase();

        if (!s.equals("ID4INTEGER") && !s.equals("DAT4INTEGER")) {
          throw new Exception("Wrong database meta data");
        }
      }

      s = "DROP TABLE marotest";

      sStatement.execute(s);
      sStatement.close();
    }
    catch (Exception e) {
      print("");
      print("Marotest error: " + e);
      print("with SQL command: " + s);
      e.printStackTrace();
    }
  }

  static void testPerformance(String url, String user, String password,
                              int max,
                              boolean persistent) throws Exception {

    if (persistent) {
      deleteDatabase("test2");
    }

    Statement sStatement = null;
    Connection cConnection = null;
    ResultSet r;
    String s = "";
    long start;
    boolean bDropError = false;
    String name = persistent ? "Persistent"
        : "Memory";

    print(name + " Performance");

    try {
      cConnection = DriverManager.getConnection(url, user, password);
      sStatement = cConnection.createStatement();
    }
    catch (Exception e) {
      e.printStackTrace();
      print("TestSelf init error: " + e.getMessage());
    }

    try {

      // cache, index and performance tests
      s = "CREATE CACHED TABLE Addr(ID INT PRIMARY KEY,First CHAR,"
          + "Name CHAR,ZIP INT)";

      sStatement.execute(s);

      s = "CREATE INDEX iName ON Addr(Name)";

      sStatement.execute(s);

      s = "SET WRITE_DELAY TRUE";

      sStatement.execute(s);

      start = System.currentTimeMillis();

      for (int i = 0; i < max; i++) {
        s = "INSERT INTO Addr VALUES(" + i + ",'Marcel" + i + "',"
            + "'Renggli" + (max - i - (i % 31)) + "',"
            + (3000 + i % 100) + ")";

        if (sStatement.executeUpdate(s) != 1) {
          throw new Exception("Insert failed");
        }

        if (i % 100 == 0) {
          printStatus("insert   ", i, max, start);
        }
      }

      printStatus("insert   ", max, max, start);
      print("");

      s = "SELECT COUNT(*) FROM Addr";
      r = sStatement.executeQuery(s);

      r.next();

      int c = r.getInt(1);

      if (c != max) {
        throw new Exception("Count should be " + (max) + " but is "
                            + c);
      }

      if (persistent) {

        // close & reopen to test backup
        cConnection.close();

        cConnection = DriverManager.getConnection(url, user,
                                                  password);
        sStatement = cConnection.createStatement();
      }

      start = System.currentTimeMillis();

      for (int i = 0; i < max; i++) {
        s = "UPDATE Addr SET Name='Robert" + (i + (i % 31))
            + "' WHERE ID=" + i;

        if (sStatement.executeUpdate(s) != 1) {
          throw new Exception("Update failed");
        }

        if (i % 100 == 0) {
          printStatus("updated  ", i, max, start);

          // s="SELECT COUNT(*) FROM Addr";
          // r=sStatement.executeQuery(s);
          // r.next();
          // int c=r.getInt(1);
          // if(c!=max) {
          // throw new Exception("Count should be "+max+" but is "+c);
          // }
        }
      }

      printStatus("update   ", max, max, start);
      print("");

      if (persistent) {
        s = "SHUTDOWN IMMEDIATELY";

        sStatement.execute(s);

        // open the database; it must be restored after shutdown
        cConnection.close();

        cConnection = DriverManager.getConnection(url, user,
                                                  password);
        sStatement = cConnection.createStatement();
      }

      start = System.currentTimeMillis();

      for (int i = 0; i < max; i++) {
        s = "DELETE FROM Addr WHERE ID=" + (max - 1 - i);

        if (sStatement.executeUpdate(s) != 1) {
          throw new Exception("Delete failed");
        }

        if (i % 100 == 0) {
          printStatus("deleting ", i, max, start);

          // s="SELECT COUNT(*) FROM Addr";
          // r=sStatement.executeQuery(s);
          // r.next();
          // int c=r.getInt(1);
          // if(c!=max-i-1) {
          // throw new Exception("Count should be "+(max-i-1)+" but is "+c);
          // }
        }
      }

      printStatus("delete   ", max, max, start);
      print("");
      sStatement.execute("DROP TABLE Addr");
    }
    catch (Exception e) {
      print("");
      print("TestSelf error: " + e);
      print("with SQL command: " + s);
      e.printStackTrace();
    }

    cConnection.close();
    print("Test finished");
  }

  /**
   *  Method declaration
   *
   * @param  s
   * @param  i
   * @param  max
   * @param  start
   */
  static void printStatus(String s, int i, int max, long start) {

    System.out.print(s + ": " + i + "/" + max + " " + (100 * i / max)
                     + "% ");

    long now = System.currentTimeMillis();

    if (now > start) {
      System.out.print( (i * 1000 / (now - start)));
    }

    System.out.print(" rows/s                \r");
  }
}