benchc.java

非常棒的java数据库

/*
 * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
 * (license2)
 * Initial Developer: H2 Group
 */
package org.h2.test.bench;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.sql.Types;

/**
 * This test is similar to the TPC-C test of the Transaction Processing Council
 * (TPC). Only one connection and one thread is used. Referential integrity is
 * not implemented.
 * <p>
 * See also http://www.tpc.org
 */
public class BenchC implements Bench {

    private Database db;

    int warehouses = 1;
    int items = 10000;
    int districtsPerWarehouse = 10;
    int customersPerDistrict = 300;
    private int ordersPerDistrict = 300;

    private BenchCRandom random;
    private String action;

    private int commitEvery = 1000;

    // private final static String[] OPERATION_TEXT = { "Neworder", "Payment",
    // "Order Status", "Delivery (deferred)", "Delivery (interactive)",
    // "Stock-level" };
    //
    private static final String[] TABLES = new String[] { "WAREHOUSE", "DISTRICT", "CUSTOMER", "HISTORY", "ORDERS",
            "NEW_ORDER", "ITEM", "STOCK", "ORDER_LINE", "RESULTS" };
    private static final String[] CREATE_SQL = new String[] {
            "CREATE TABLE  WAREHOUSE(\n" + " W_ID INT NOT NULL PRIMARY KEY,\n" + " W_NAME VARCHAR(10),\n"
                    + " W_STREET_1 VARCHAR(20),\n" + " W_STREET_2 VARCHAR(20),\n" + " W_CITY VARCHAR(20),\n"
                    + " W_STATE CHAR(2),\n" + " W_ZIP CHAR(9),\n" + " W_TAX DECIMAL(4, 4),\n"
                    + " W_YTD DECIMAL(12, 2))",
            "CREATE TABLE  DISTRICT(\n" + " D_ID INT NOT NULL,\n" + " D_W_ID INT NOT NULL,\n"
                    + " D_NAME VARCHAR(10),\n" + " D_STREET_1 VARCHAR(20),\n" + " D_STREET_2 VARCHAR(20),\n"
                    + " D_CITY VARCHAR(20),\n" + " D_STATE CHAR(2),\n" + " D_ZIP CHAR(9),\n"
                    + " D_TAX DECIMAL(4, 4),\n" + " D_YTD DECIMAL(12, 2),\n" + " D_NEXT_O_ID INT,\n"
                    + " PRIMARY KEY (D_ID, D_W_ID))", // ,\n"
            // + " FOREIGN KEY (D_W_ID)\n"
            // + " REFERENCES WAREHOUSE(W_ID))",
            "CREATE TABLE  CUSTOMER(\n" + " C_ID INT NOT NULL,\n" + " C_D_ID INT NOT NULL,\n"
                    + " C_W_ID INT NOT NULL,\n" + " C_FIRST VARCHAR(16),\n" + " C_MIDDLE CHAR(2),\n"
                    + " C_LAST VARCHAR(16),\n" + " C_STREET_1 VARCHAR(20),\n" + " C_STREET_2 VARCHAR(20),\n"
                    + " C_CITY VARCHAR(20),\n" + " C_STATE CHAR(2),\n" + " C_ZIP CHAR(9),\n" + " C_PHONE CHAR(16),\n"
                    + " C_SINCE TIMESTAMP,\n" + " C_CREDIT CHAR(2),\n" + " C_CREDIT_LIM DECIMAL(12, 2),\n"
                    + " C_DISCOUNT DECIMAL(4, 4),\n" + " C_BALANCE DECIMAL(12, 2),\n"
                    + " C_YTD_PAYMENT DECIMAL(12, 2),\n" + " C_PAYMENT_CNT DECIMAL(4),\n"
                    + " C_DELIVERY_CNT DECIMAL(4),\n" + " C_DATA VARCHAR(500),\n"
                    + " PRIMARY KEY (C_W_ID, C_D_ID, C_ID))", // ,\n"
            // + " FOREIGN KEY (C_W_ID, C_D_ID)\n"
            // + " REFERENCES DISTRICT(D_W_ID, D_ID))",
            "CREATE INDEX CUSTOMER_NAME ON CUSTOMER(C_LAST, C_D_ID, C_W_ID)",
            "CREATE TABLE  HISTORY(\n" + " H_C_ID INT,\n" + " H_C_D_ID INT,\n" + " H_C_W_ID INT,\n" + " H_D_ID INT,\n"
                    + " H_W_ID INT,\n" + " H_DATE TIMESTAMP,\n" + " H_AMOUNT DECIMAL(6, 2),\n" + " H_DATA VARCHAR(24))", // ,\n"
            // + " FOREIGN KEY(H_C_W_ID, H_C_D_ID, H_C_ID)\n"
            // + " REFERENCES CUSTOMER(C_W_ID, C_D_ID, C_ID),\n"
            // + " FOREIGN KEY(H_W_ID, H_D_ID)\n"
            // + " REFERENCES DISTRICT(D_W_ID, D_ID))",
            "CREATE TABLE  ORDERS(\n" + " O_ID INT NOT NULL,\n" + " O_D_ID INT NOT NULL,\n" + " O_W_ID INT NOT NULL,\n"
                    + " O_C_ID INT,\n" + " O_ENTRY_D TIMESTAMP,\n" + " O_CARRIER_ID INT,\n" + " O_OL_CNT INT,\n"
                    + " O_ALL_LOCAL DECIMAL(1),\n" + " PRIMARY KEY(O_W_ID, O_D_ID, O_ID))", // ,\n"
            // + " FOREIGN KEY(O_W_ID, O_D_ID, O_C_ID)\n"
            // + " REFERENCES CUSTOMER(C_W_ID, C_D_ID, C_ID))",
            "CREATE INDEX ORDERS_OID ON ORDERS(O_ID)",
            "CREATE TABLE  NEW_ORDER(\n" + " NO_O_ID INT NOT NULL,\n" + " NO_D_ID INT NOT NULL,\n"
                    + " NO_W_ID INT NOT NULL,\n" + " PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID))", // ,\n"
            // + " FOREIGN KEY(NO_W_ID, NO_D_ID, NO_O_ID)\n"
            // + " REFERENCES ORDER(O_W_ID, O_D_ID, O_ID))",
            "CREATE TABLE  ITEM(\n" + " I_ID INT NOT NULL,\n" + " I_IM_ID INT,\n" + " I_NAME VARCHAR(24),\n"
                    + " I_PRICE DECIMAL(5, 2),\n" + " I_DATA VARCHAR(50),\n" + " PRIMARY KEY(I_ID))",
            "CREATE TABLE  STOCK(\n" + " S_I_ID INT NOT NULL,\n" + " S_W_ID INT NOT NULL,\n"
                    + " S_QUANTITY DECIMAL(4),\n" + " S_DIST_01 CHAR(24),\n" + " S_DIST_02 CHAR(24),\n"
                    + " S_DIST_03 CHAR(24),\n" + " S_DIST_04 CHAR(24),\n" + " S_DIST_05 CHAR(24),\n"
                    + " S_DIST_06 CHAR(24),\n" + " S_DIST_07 CHAR(24),\n" + " S_DIST_08 CHAR(24),\n"
                    + " S_DIST_09 CHAR(24),\n" + " S_DIST_10 CHAR(24),\n" + " S_YTD DECIMAL(8),\n"
                    + " S_ORDER_CNT DECIMAL(4),\n" + " S_REMOTE_CNT DECIMAL(4),\n" + " S_DATA VARCHAR(50),\n"
                    + " PRIMARY KEY(S_W_ID, S_I_ID))", // ,\n"
            // + " FOREIGN KEY(S_W_ID)\n"
            // + " REFERENCES WAREHOUSE(W_ID),\n"
            // + " FOREIGN KEY(S_I_ID)\n" + " REFERENCES ITEM(I_ID))",
            "CREATE TABLE  ORDER_LINE(\n" + " OL_O_ID INT NOT NULL,\n" + " OL_D_ID INT NOT NULL,\n"
                    + " OL_W_ID INT NOT NULL,\n" + " OL_NUMBER INT NOT NULL,\n" + " OL_I_ID INT,\n"
                    + " OL_SUPPLY_W_ID INT,\n" + " OL_DELIVERY_D TIMESTAMP,\n" + " OL_QUANTITY DECIMAL(2),\n"
                    + " OL_AMOUNT DECIMAL(6, 2),\n" + " OL_DIST_INFO CHAR(24),\n"
                    + " PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER))", // ,\n"
            // + " FOREIGN KEY(OL_W_ID, OL_D_ID, OL_O_ID)\n"
            // + " REFERENCES ORDER(O_W_ID, O_D_ID, O_ID),\n"
            // + " FOREIGN KEY(OL_SUPPLY_W_ID, OL_I_ID)\n"
            // + " REFERENCES STOCK(S_W_ID, S_I_ID))",
            "CREATE TABLE RESULTS(\n" + " ID INT NOT NULL PRIMARY KEY,\n" + " TERMINAL INT,\n" + " OPERATION INT,\n"
                    + " RESPONSE_TIME INT,\n" + " PROCESSING_TIME INT,\n" + " KEYING_TIME INT,\n"
                    + " THINK_TIME INT,\n" + " SUCCESSFUL INT,\n" + " NOW TIMESTAMP)" };

    public void init(Database db, int size) throws Exception {
        this.db = db;

        random = new BenchCRandom();

        items = size * 10;
        warehouses = 1;
        districtsPerWarehouse = Math.max(1, size / 100);
        customersPerDistrict = Math.max(1, size / 100);
        ordersPerDistrict = Math.max(1, size / 1000);

        db.start(this, "Init");
        db.openConnection();
        load();
        db.commit();
        db.closeConnection();
        db.end();

        // db.start(this, "Open/Close");
        // db.openConnection();
        // db.closeConnection();
        // db.end();

    }

    private void load() throws Exception {
        for (int i = 0; i < TABLES.length; i++) {
            db.dropTable(TABLES[i]);
        }
        for (int i = 0; i < CREATE_SQL.length; i++) {
            db.update(CREATE_SQL[i]);
        }
        db.setAutoCommit(false);
        loadItem();
        loadWarehouse();
        loadCustomer();
        loadOrder();
        db.commit();
        trace("load done");
    }

    void trace(String s) {
        action = s;
    }

    void trace(int i, int max) {
        db.trace(action, i, max);
    }

    private void loadItem() throws Exception {
        trace("load item");
        boolean[] original = random.getBoolean(items, items / 10);
        PreparedStatement prep = db.prepare("INSERT INTO ITEM(I_ID, I_IM_ID, I_NAME, I_PRICE, I_DATA) "
                + "VALUES(?, ?, ?, ?, ?)");
        for (int id = 1; id <= items; id++) {
            String name = random.getString(14, 24);
            BigDecimal price = random.getBigDecimal(random.getInt(100, 10000), 2);
            String data = random.getString(26, 50);
            if (original[id - 1]) {
                data = random.replace(data, "original");
            }
            prep.setInt(1, id);
            prep.setInt(2, random.getInt(1, 10000));
            prep.setString(3, name);
            prep.setBigDecimal(4, price);
            prep.setString(5, data);
            db.update(prep, "insertItem");
            trace(id, items);
            if (id % commitEvery == 0) {
                db.commit();
            }
        }
    }

    private void loadWarehouse() throws Exception {
        trace("loading warehouses");
        PreparedStatement prep = db.prepare("INSERT INTO WAREHOUSE(W_ID, W_NAME, W_STREET_1, "
                + "W_STREET_2, W_CITY, W_STATE, W_ZIP, W_TAX, W_YTD) " + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)");
        for (int id = 1; id <= warehouses; id++) {
            String name = random.getString(6, 10);
            String[] address = random.getAddress();
            String street1 = address[0];
            String street2 = address[1];
            String city = address[2];
            String state = address[3];
            String zip = address[4];
            BigDecimal tax = random.getBigDecimal(random.getInt(0, 2000), 4);
            BigDecimal ytd = new BigDecimal("300000.00");
            prep.setInt(1, id);
            prep.setString(2, name);
            prep.setString(3, street1);
            prep.setString(4, street2);
            prep.setString(5, city);
            prep.setString(6, state);
            prep.setString(7, zip);
            prep.setBigDecimal(8, tax);
            prep.setBigDecimal(9, ytd);
            db.update(prep, "insertWarehouse");
            loadStock(id);
            loadDistrict(id);
            if (id % commitEvery == 0) {
                db.commit();
            }
        }
    }

    private void loadCustomer() throws Exception {
        trace("load customers");
        int max = warehouses * districtsPerWarehouse;
        int i = 0;
        for (int id = 1; id <= warehouses; id++) {
            for (int districtId = 1; districtId <= districtsPerWarehouse; districtId++) {
                loadCustomerSub(districtId, id);
                trace(i++, max);
                if (i % commitEvery == 0) {
                    db.commit();
                }
            }
        }
    }

    private void loadCustomerSub(int dId, int wId) throws Exception {
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        PreparedStatement prepCustomer = db.prepare("INSERT INTO CUSTOMER(C_ID, C_D_ID, C_W_ID, "
                + "C_FIRST, C_MIDDLE, C_LAST, " + "C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, "
                + "C_PHONE, C_SINCE, C_CREDIT, " + "C_CREDIT_LIM, C_DISCOUNT, C_BALANCE, C_DATA, "
                + "C_YTD_PAYMENT, C_PAYMENT_CNT, C_DELIVERY_CNT) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        PreparedStatement prepHistory = db.prepare("INSERT INTO HISTORY(H_C_ID, H_C_D_ID, H_C_W_ID, "
                + "H_W_ID, H_D_ID, H_DATE, H_AMOUNT, H_DATA) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        for (int cId = 1; cId <= customersPerDistrict; cId++) {
            String first = random.getString(8, 16);