java/testing/org/apache/derbyTesting/system/oe/load/SimpleInsert.java

/*
 *
 * Derby - Class org.apache.derbyTesting.system.oe.load.SimpleInsert
 *
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an 
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
 * either express or implied. See the License for the specific 
 * language governing permissions and limitations under the License.
 */
package org.apache.derbyTesting.system.oe.load;

import java.sql.*;

import org.apache.derbyTesting.system.oe.client.Load;
import org.apache.derbyTesting.system.oe.util.OERandom;

/**
 * Implement the initial database population according to the TPC-C database
 * population requirements in Clause 4.3 using simple Insert sql statements
 */
public class SimpleInsert implements Load {

    /**
     * database connection
     */
    Connection conn = null;

    /**
     * warehouse scale factor, default to 1.
     */
    short scale = 1;
    
    /**
     * Seed value for random number generator.
     */
    long seed = System.currentTimeMillis();

    /**
     * Utility to generate random data per the TPC-C requirements
     */
    OERandom random;

    /**
     * Perform the necessary setup before database population.
     * 
     * @param conn -
     *            database connection
     * @param scale -
     *            scale of the database. The WAREHOUSE table is used as the base
     *            unit of scaling.
     * @throws Exception
     */
    public void setupLoad(Connection conn, short scale) throws SQLException {

        setupConnection(conn, scale);

        Statement s = conn.createStatement();
        try {
            s.execute("DROP TABLE C");
        } catch (SQLException sqle) {
            // ignore
        }
        conn.commit();
        s.execute("CREATE TABLE C(CLOAD INT)");
        conn.commit();

        random = new OERandom(-1, seed);

        // Section 2.1.6.1 of TPC-C spec
        int loadRandomFactor = random.randomInt(0, 255);
        s.execute("INSERT INTO C VALUES(" + loadRandomFactor + ")");
        s.close();
        conn.commit();  
        
        setRandomGenerator();
    }
    
    /**
     * Set the connection up to the intended state.
     * Intended for use by sub-classes.
     */
    void setupConnection(Connection conn, short scale) throws SQLException
    {
        this.conn = conn;
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        this.scale = scale;
    }
    
    /**
     * Setup the random number generator to be used for the load.
     * @throws SQLException
     */
    void setRandomGenerator() throws SQLException
    {
        Statement s = conn.createStatement();
        ResultSet rs = s.executeQuery("SELECT CLOAD FROM C");
        rs.next();
        int loadRandomFactor = rs.getInt(1);
        rs.close();
        random = new OERandom(loadRandomFactor, seed);
        s.close();
        conn.commit();
    }

    /**
     * Follow the initial database population requirements in Section 4.3.3 and
     * populate all the required tables.
     * 
     * @throws SQLException
     */
    public void populateAllTables() throws Exception {
        // load item table
        itemTable(1, Load.ITEM_COUNT);

        for (short w = 1; w <= scale; w++) {
            populateForOneWarehouse(w);
        }

    }
    
    /**
     * Populate all the tables needed for a specific warehouse.
     * for each row in warehouse table, load the stock,
     * district table. For each row in district table, load
     * the customer table. for each row in customer table, load
     * the customer table. for each row in customer table, load
     * @param w Warehouse to be populated.
     * @throws SQLException
     */
    void populateForOneWarehouse(short w) throws SQLException
    {
        warehouseTable(w);
        // for each warehouse: load the stock table
        stockTable(1, Load.STOCK_COUNT_W, w);
        for (short d = 1; d <= Load.DISTRICT_COUNT_W; d++) {
            districtTable(w, d);
            customerTable(w, d);
            orderTable(w, d);
        }
    }

    /**
     * Populate the ITEM table. See population requirements in section 4.3.3.1
     * <BR>
     * 
     * @param itemStart
     *            insert item information starting from this Item id (ITEM.I_ID)
     * @param itemEnd
     *            last Item id (ITEM.I_ID) for inserting information for
     * @throws SQLException
     */
    public void itemTable(int itemStart, int itemEnd) throws SQLException {
        PreparedStatement ps = conn
                .prepareStatement("INSERT INTO ITEM(I_ID,I_IM_ID,I_NAME,I_PRICE,I_DATA)"
                        + " VALUES (?, ?, ?, ?, ?)");

        String price;

        for (int i = itemStart; i <= itemEnd; i++) {
            ps.setInt(1, i);
            ps.setInt(2, random.randomInt(1, 10000));
            ps.setString(3, random.randomAString14_24());
            price = random.randomDecimalString(100, 9999, 2);
            ps.setString(4, price);
            ps.setString(5, random.randomData());
            ps.executeUpdate();

            if ((i % (1000)) == 0) {
                conn.commit();
            }
        }
        conn.commit();
        ps.close();
    }

    /**
     * Populate the WAREHOUSE table for a given warehouse. See population
     * requirements in section 4.3.3.1
     * 
     * @param w
     *            WAREHOUSE ID (W_ID) to insert data for.
     * @throws SQLException
     */
    public void warehouseTable(short w) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("INSERT INTO WAREHOUSE "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, 300000.00)");

        ps.setShort(1, w);
        ps.setString(2, random.randomAString(6, 10));
        ps.setString(3, random.randomAString10_20());
        ps.setString(4, random.randomAString10_20());
        ps.setString(5, random.randomAString10_20());
        ps.setString(6, random.randomState());
        ps.setString(7, random.randomZIP());
        ps.setString(8, random.randomDecimalString(0, 2000, 4));

        ps.executeUpdate();
        conn.commit();
        ps.close();
    }

    /**
     * Populate the STOCK table for a given warehouse. See population
     * requirements in section 4.3.3.1 <BR>
     * 
     * @param itemStart
     *            insert stocks of items from this Item id (ITEM.I_ID)
     * @param itemEnd
     *            last Item id (ITEM.I_ID) to insert stocks of times for.
     * @param w
     *            WAREHOUSE id (W_ID) for which the stock is populated.
     * @throws SQLException
     */
    public void stockTable(int itemStart, int itemEnd, short w)
            throws SQLException {
        PreparedStatement ps = conn.prepareStatement("INSERT INTO STOCK "
                + "(S_I_ID, S_W_ID, S_QUANTITY,S_DIST_01, S_DIST_02,"
                + " S_DIST_03,S_DIST_04,S_DIST_05,"
                + "S_DIST_06,S_DIST_07,S_DIST_08,S_DIST_09,S_DIST_10,"
                + "S_ORDER_CNT, S_REMOTE_CNT, S_YTD, S_DATA ) VALUES "
                + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, ?)");

        ps.setShort(2, w);

        for (int i = itemStart; i <= itemEnd; i++) {
            ps.setInt(1, i);
            ps.setInt(3, random.randomInt(10, 100));
            ps.setString(4, random.randomAString24());
            ps.setString(5, random.randomAString24());
            ps.setString(6, random.randomAString24());
            ps.setString(7, random.randomAString24());
            ps.setString(8, random.randomAString24());
            ps.setString(9, random.randomAString24());
            ps.setString(10, random.randomAString24());
            ps.setString(11, random.randomAString24());
            ps.setString(12, random.randomAString24());
            ps.setString(13, random.randomAString24());

            ps.setString(14, random.randomData());
            ps.executeUpdate();

            if ((i % 1000) == 0) {
                conn.commit();
            }
        }
        conn.commit();
        ps.close();
    }

    /**
     * Populate the DISTRICT table for a given warehouse. See population
     * requirements in section 4.3.3.1 <BR>
     * 
     * @param w -
     *            WAREHOUSE id (W_ID)
     * @param d -
     *            DISTRICT id (D_ID)
     * @throws SQLException
     */
    public void districtTable(short w, short d) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("INSERT INTO DISTRICT"
                + " (D_ID, D_W_ID, D_NAME, D_STREET_1, D_STREET_2,"
                + " D_CITY, D_STATE, D_ZIP, D_TAX, D_YTD, D_NEXT_O_ID) "
                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, 30000.00, 3001)");

        ps.setShort(1, d);
        ps.setShort(2, w);
        ps.setString(3, random.randomAString(6, 10));
        ps.setString(4, random.randomAString10_20());
        ps.setString(5, random.randomAString10_20());
        ps.setString(6, random.randomAString10_20());
        ps.setString(7, random.randomState());
        ps.setString(8, random.randomZIP());
        ps.setString(9, random.randomDecimalString(0, 2000, 4));
        ps.executeUpdate();
        conn.commit();
        ps.close();

    }

    /**
     * Populate the CUSTOMER table for a given district for a specific
     * warehouse. See population requirements in section 4.3.3.1 <BR>
     * 
     * @param w -
     *            WAREHOUSE id (W_ID)
     * @param d -
     *            DISTRICT id (D_ID)
     * @throws SQLException
     */
    public void customerTable(short w, short d) throws SQLException {
        PreparedStatement psC = conn.prepareStatement("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_YTD_PAYMENT, "
                + "C_PAYMENT_CNT, C_DELIVERY_CNT, C_DATA)  "
                + "VALUES (?, ?, ?, ?, 'OE', ?, ?, ?, ?, ?, ?, ?, "
                + " CURRENT TIMESTAMP ,?, 50000.00, ?, -10.0, 10.0,"
                + " 1, 0, ?)");

        PreparedStatement psH = conn
                .prepareStatement("INSERT INTO HISTORY (H_C_ID, H_C_D_ID, H_C_W_ID, H_D_ID, H_W_ID, H_DATE, H_AMOUNT, H_DATA) VALUES (?, ?, ?, ?, ?, CURRENT TIMESTAMP, 10.00, ?)");

        psC.setShort(2, d); // c_d_id
        psC.setShort(3, w); // c_w_id
        psH.setShort(2, d);
        psH.setShort(3, w);
        psH.setShort(4, d);
        psH.setShort(5, w);
        for (int c = 1; c <= Load.CUSTOMER_COUNT_W / Load.DISTRICT_COUNT_W; c++) {
            psC.setInt(1, c); // c_id
            psC.setString(4, random.randomAString8_16()); // c_first

            psC.setString(5, random.randomCLastPopulate(c)); // c_last
            psC.setString(6, random.randomAString10_20()); // c_street_1
            psC.setString(7, random.randomAString10_20()); // c_street_2
            psC.setString(8, random.randomAString10_20()); // c_city
            psC.setString(9, random.randomState()); // c_state
            psC.setString(10, random.randomZIP()); // c_zip
            psC.setString(11, random.randomNString(16, 16)); // c_phone

            psC.setString(12, Math.random() < 0.10 ? "BC" : "GC"); // c_credit

            psC.setString(13, random.randomDecimalString(0, 5000, 4)); // c_discount

            String str = random.randomAString300_500();
            if (str.length() > 255)
                str = str.substring(255);
            psC.setString(14, str); // c_data

            psC.executeUpdate();

            psH.setInt(1, c);
            psH.setString(6, random.randomAString(12, 24));
            psH.executeUpdate();

            if ((c % 1000) == 0) {
                conn.commit();
            }

        }
        conn.commit();

        psC.close();
        psH.close();
    }

    /**
     * Populate the ORDER table See population requirements in section 4.3.3.1
     * 
     * @param w -
     *            WAREHOUSE id (W_ID)
     * @param d -
     *            DISTRICT id (D_ID)
     * @throws SQLException
     */
    public void orderTable(short w, short d) throws SQLException {

        PreparedStatement psO = conn
                .prepareStatement("INSERT INTO ORDERS (O_ID, O_D_ID, O_W_ID, O_C_ID, O_ENTRY_D, O_CARRIER_ID, O_OL_CNT, O_ALL_LOCAL) VALUES (?, ?, ?, ?, ?, ?, ?, 1)");

        PreparedStatement psOL = conn
                .prepareStatement("INSERT INTO ORDERLINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_DELIVERY_D, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO)  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        PreparedStatement psNO = conn
                .prepareStatement("INSERT INTO NEWORDERS (NO_O_ID, NO_D_ID, NO_W_ID) VALUES (?, ?, ?)");

        psO.setShort(2, d);
        psO.setShort(3, w);
        
        // Section 4.3.3.1 of TPC-C specification revision 5.8.0
        // O_C_ID selected sequentially from a random permutation 
        // of [1 .. 3,000]
        int[] cid = random.randomIntPerm(Load.CUSTOMER_COUNT_W
                / Load.DISTRICT_COUNT_W);
        
        for (int o_id = 1; o_id <= cid.length; o_id++) {
            psO.setInt(1, o_id);
            psO.setInt(4, cid[o_id-1]);

            Timestamp o_entry_d = new Timestamp(System.currentTimeMillis());

            psO.setTimestamp(5, o_entry_d);

            if (o_id <= Load.NEWORDERS_BREAKPOINT)
                psO.setShort(6, (short) random.randomInt(1, 10));
            else
                psO.setNull(6, Types.SMALLINT);

            int o_ol_cnt = random.randomInt(5, 15);
            psO.setInt(7, o_ol_cnt);

            psO.executeUpdate();

            psOL.setShort(2, d);
            psOL.setShort(3, w);
            psNO.setShort(2, d);
            psNO.setShort(3, w);
            for (int ol_number = 1; ol_number <= o_ol_cnt; ol_number++) {

                psOL.setInt(1, o_id);
                psOL.setInt(4, ol_number);

                // OL_I_ID random within [1 .. 100,000]
                psOL.setInt(5, random.randomInt(1, Load.ITEM_COUNT));
                psOL.setShort(6, w);
                if (o_id <= Load.NEWORDERS_BREAKPOINT) {
                    psOL.setTimestamp(7, o_entry_d);
                    psOL.setString(9, "0.00");
                } else {
                    psOL.setNull(7, Types.TIMESTAMP);
                    psOL.setString(9, random.randomDecimalString(1, 999999, 2));
                }
                psOL.setInt(8, 5);
                psOL.setString(10, random.randomAString24());
                psOL.executeUpdate();
            }
            if (o_id > Load.NEWORDERS_BREAKPOINT) {
                psNO.setInt(1, o_id);
                psNO.executeUpdate();
            }
            if ((o_id % 1000) == 0) {
                conn.commit();
            }

        }
        conn.commit();

        psOL.close();
        psO.close();
        psNO.close();
    }

    public void setSeed(long seed) {
        this.seed = seed;
    }

    /**
     * Ignore, this is a single threaded load.
     */
    public void setThreadCount(int threadCount) {
    }

}