/*
Customer Emulator System Under Test
由用戶
*/
enum eCESUTStmt
{
        // Customer-Position 有2 階段、4 sql。文檔是3 階段（Frame），但第三階段只有 commit ，其他有意義的 sql 是對得上的。
    CESUT_STMT_CPF1_1,
    // Market-Watch（市場觀察）
    CESUT_STMT_MWF1_1a,
    // Security-Detail（證券詳情查詢）
    CESUT_STMT_SDF1_1,
    // Trade-Lookup（交易查詢），非常巨大的事務(wù)
        CESUT_STMT_TLF1_1,
        // Trade-Order（交易委托）
        CESUT_STMT_TOF1_1,
        //Trade-Status（交易狀態(tài)查詢）
        CESUT_STMT_TSF1_1,
        // Trade-Update（交易更新）
        CESUT_STMT_TUF1_1, 
}

/*
Market Exchange Emulator SUT
*/
enum eMEESUTStmt
{
    // 極其巨大的事務(wù)
    MEESUT_STMT_TRF1_1,
    // Market-Feed（市場數(shù)據(jù)更新）
    MEESUT_STMT_MFF1_1,
};

/*
Data Maintenance SUT
*/
enum eDMSUTStmt
{
    // Trade-Cleanup，開測前初始化；
    DMSUT_STMT_TCF1_2,
};

// 其他無對應(yīng)代碼 enum 的：
// Broker-Volume（經(jīng)紀(jì)商成交量）：只有一個 frame、一句 sql，無 enum

-- Broker-Volume 事務(wù)的 SQL 查詢
SELECT b_name, SUM(tr_qty * tr_bid_price) -- 經(jīng)紀(jì)人的總交易量
FROM trade_request, sector, industry, company, broker, security
WHERE tr_b_id = b_id -- 經(jīng)紀(jì)人表，通過經(jīng)紀(jì)人ID關(guān)聯(lián)
 AND tr_s_symb = s_symb  -- 行業(yè)表，通過證券符號關(guān)聯(lián)
 AND s_co_id = co_id -- 行業(yè)表，通過行業(yè)ID關(guān)聯(lián)
 AND co_in_id = in_id -- 確保公司表中的國家ID與行業(yè)表中的國家ID匹配
 AND sc_id = in_sc_id -- 確保行業(yè)表中的公司ID與公司表中的ID匹配
 AND b_name IN (%s..) -- 經(jīng)紀(jì)人名稱列表，這里 %s.. 是一個占位符，表示一系列經(jīng)紀(jì)人名稱
 AND sc_name = '%s' -- 行業(yè)名稱，這里 '%s' 是一個占位符，表示特定的行業(yè)名稱
GROUP BY b_name
ORDER BY 2 DESC -- 按總交易量降序排列

-- 設(shè)置事務(wù)隔離級別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 根據(jù)稅號查詢客戶ID
SELECT c_id FROM customer WHERE c_tax_id = _latin1'970AM8516RE955';

-- 獲取客戶詳細(xì)信息
SELECT 
    c_st_id, c_l_name, c_f_name, c_m_name, c_gndr, c_tier, 
    DATE_FORMAT(c_dob,'%Y-%m-%d'), c_ad_id, 
    c_ctry_1, c_area_1, c_local_1, c_ext_1, 
    c_ctry_2, c_area_2, c_local_2, c_ext_2, 
    c_ctry_3, c_area_3, c_local_3, c_ext_3, 
    c_email_1, c_email_2 
FROM customer 
WHERE c_id = 4300001491;

-- 查詢客戶賬戶的前10個持倉及其總價值
SELECT 
    ca_id, ca_bal, COALESCE(SUM(hs_qty * lt_price),0) AS price_sum 
FROM 
    customer_account 
    LEFT OUTER JOIN holding_summary ON hs_ca_id = ca_id, last_trade 
WHERE 
    ca_c_id = 4300001491 AND lt_s_symb = hs_symb 
GROUP BY 
    ca_id, ca_bal 
ORDER BY 
    price_sum ASC 
LIMIT 10;

-- 查詢客戶最近的30條交易歷史記錄
SELECT 
    t_id, t_s_symb, t_qty, st_name, DATE_FORMAT(th_dts,'%Y-%m-%d %H:%i:%s.%f') 
FROM 
    (SELECT t_id AS id FROM trade WHERE t_ca_id = 43000014904 ORDER BY t_dts DESC LIMIT 10) AS t, 
    trade, trade_history, status_type 
FORCE INDEX(PRIMARY) 
WHERE 
    t_id = id AND th_t_id = t_id AND st_id = th_st_id 
ORDER BY 
    th_dts DESC 
LIMIT 30;

-- 提交事務(wù)
COMMIT;

-- 設(shè)置事務(wù)隔離級別為可重復(fù)讀，確保在事務(wù)期間讀取的數(shù)據(jù)保持一致
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 更新 last_trade 表，模擬市場交易所的最新成交信息
UPDATE last_trade 
SET 
    lt_price = '2.93399999999999999e+01', -- 設(shè)置最新的成交價格
    lt_vol = lt_vol + '100', -- 增加成交量
    lt_dts = '2024-02-27 20:48:17' -- 更新成交時間
WHERE 
    lt_s_symb = 'CLYS'; -- 指定特定的證券符號

-- 查詢 trade_request 表，找出與最新成交信息相關(guān)的待處理交易請求
SELECT 
    tr_t_id, tr_bid_price, tr_tt_id, tr_qty
FROM 
    trade_request
WHERE 
    tr_s_symb = 'CLYS' -- 指定證券符號
    AND (
        (tr_tt_id = 'TSL' AND tr_bid_price >= '2.93399999999999999e+01') -- 買入限價單，且報價大于等于最新成交價
        OR (tr_tt_id = 'TLS' AND tr_bid_price <= '2.93399999999999999e+01') -- 賣出限價單，且報價小于等于最新成交價
        OR (tr_tt_id = 'TLB' AND tr_bid_price >= '2.93399999999999999e+01') -- 買入止損單，且報價大于等于最新成交價
    );

-- 提交事務(wù)，確保所有更改都被保存
COMMIT;

-- 設(shè)置事務(wù)隔離級別為 READ COMMITTED，確保事務(wù)在讀取數(shù)據(jù)時不會受到其他并發(fā)事務(wù)的影響
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 執(zhí)行查詢，計算市值變化
-- 這個查詢涉及到多個表的連接，包括 watch_item, watch_list, last_trade, security, 和 daily_market
-- 它計算了在特定日期（'2004-12-31'）的收盤價（dm_close）和當(dāng)前價格（lt_price）的總和
-- 通過比較這兩個總和，可以得到市值的百分比變化
SELECT 
    COALESCE(SUM(s_num_out * dm_close), 0) AS market_cap_change, -- 計算特定日期的市值
    COALESCE(SUM(s_num_out * lt_price), 0) AS current_market_cap -- 計算當(dāng)前市值
FROM 
    watch_item, 
    watch_list, 
    last_trade, 
    security, 
    daily_market
WHERE 
    wl_c_id = '4300000678' -- 指定客戶ID
    AND wi_wl_id = wl_id -- 確保 watch_item 和 watch_list 的關(guān)聯(lián)ID匹配
    AND dm_s_symb = wi_symb -- 確保證券符號匹配
    AND dm_date = '2004-12-31' -- 指定比較的日期
    AND lt_s_symb = dm_s_symb -- 確保 last_trade 中的證券符號與 daily_market 中的匹配
    AND s_symb = dm_s_sym; -- 確保 security 表中的證券符號與 daily_market 中的匹配

-- 關(guān)閉語句，結(jié)束查詢
Close stmt;

-- 設(shè)置事務(wù)隔離級別為 READ COMMITTED，確保事務(wù)在讀取數(shù)據(jù)時的一致性
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 查詢證券和公司詳細(xì)信息
SELECT 
    s_name, co_id, co_name, co_sp_rate, co_ceo, co_desc, 
    DATE_FORMAT(co_open_date,'%Y-%m-%d'), co_st_id, 
    ca.ad_line1, ca.ad_line2, zca.zc_town, zca.zc_div, ca.ad_zc_code, ca.ad_ctry, 
    s_num_out, DATE_FORMAT(s_start_date,'%Y-%m-%d'), 
    DATE_FORMAT(s_exch_date,'%Y-%m-%d'), s_pe, s_52wk_high, 
    DATE_FORMAT(s_52wk_high_date,'%Y-%m-%d'), s_52wk_low, 
    DATE_FORMAT(s_52wk_low_date,'%Y-%m-%d'), s_dividend, s_yield, 
    zea.zc_div, ea.ad_ctry, ea.ad_line1, ea.ad_line2, zea.zc_town, 
    ea.ad_zc_code, ex_close, ex_desc, ex_name, ex_num_symb, ex_open 
FROM 
    security, company, address ca, address ea, zip_code zca, zip_code zea, exchange 
WHERE 
    s_symb = _latin1'XTRM' 
    AND co_id = s_co_id 
    AND ca.ad_id = co_ad_id 
    AND ea.ad_id = ex_ad_id 
    AND ex_id = s_ex_id 
    AND ca.ad_zc_code = zca.zc_code 
    AND ea.ad_zc_code = zea.zc_code;

-- 查詢公司競爭對手信息
SELECT 
    co_name, in_name 
FROM 
    company_competitor, company, industry 
WHERE 
    cp_co_id = 4300000566 
    AND co_id = cp_comp_co_id 
    AND in_id = cp_in_id 
LIMIT 3;

-- 查詢公司財務(wù)數(shù)據(jù)
SELECT 
    fi_year, fi_qtr, DATE_FORMAT(fi_qtr_start_date,'%Y-%m-%d'), 
    fi_revenue, fi_net_earn, fi_basic_eps, fi_dilut_eps, 
    fi_margin, fi_inventory, fi_assets, fi_liability, 
    fi_out_basic, fi_out_dilut 
FROM 
    financial 
WHERE 
    fi_co_id = 4300000566 
ORDER BY 
    fi_year ASC, fi_qtr 
LIMIT 20;

-- 查詢證券市場歷史數(shù)據(jù)
SELECT 
    DATE_FORMAT(dm_date,'%Y-%m-%d'), dm_close, dm_high, dm_low, dm_vol 
FROM 
    daily_market 
WHERE 
    dm_s_symb = _latin1'XTRM' 
    AND dm_date >= _latin1'2000-08-12' 
ORDER BY 
    dm_date ASC 
LIMIT 15;

-- 查詢最后一筆交易信息
SELECT 
    lt_price, lt_open_price, lt_vol 
FROM 
    last_trade 
WHERE 
    lt_s_symb = _latin1'XTRM';

-- 查詢公司最新新聞條目
SELECT 
    DATE_FORMAT(ni_dts, '%Y-%m-%d %H:%i:%s.%f'), ni_source, ni_author, ni_headline, ni_summary 
FROM 
    news_xref, news_item 
WHERE 
    ni_id = nx_ni_id 
    AND nx_co_id = 4300000566 
LIMIT 2;

-- 提交事務(wù)，確保所有查詢結(jié)果被正確處理
COMMIT;

-- 3.3.6 Trade-Lookup
Query        SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- F1
Execute        SELECT t_bid_price, t_exec_name, t_is_cash, tt_is_mrkt, t_trade_price FROM trade, trade_type WHERE t_id = '200000005238470' AND t_tt_id = tt_id
Execute        SELECT se_amt, DATE_FORMAT(se_cash_due_date, '%Y-%m-%d'), se_cash_type FROM settlement WHERE se_t_id = '200000005238470'
Execute        SELECT ct_amt, DATE_FORMAT(ct_dts, '%Y-%m-%d %H:%i:%s.%f'), ct_name FROM cash_transaction WHERE ct_t_id = '200000005238470'
Execute        SELECT DATE_FORMAT(th_dts, '%Y-%m-%d %H:%i:%s.%f'), th_st_id FROM trade_history WHERE th_t_id = '200000005238470' ORDER BY th_dts LIMIT 3

-- F2
Query        SELECT t_bid_price, t_exec_name, t_is_cash, tt_is_mrkt, t_trade_price FROM trade, trade_type WHERE t_id = 200000005236617 AND t_tt_id = tt_id
Query        SELECT se_amt, DATE_FORMAT(se_cash_due_date, '%Y-%m-%d'), se_cash_type FROM settlement WHERE se_t_id = 200000005236617
Query        SELECT ct_amt, DATE_FORMAT(ct_dts, '%Y-%m-%d %H:%i:%s.%f'), ct_name FROM cash_transaction WHERE ct_t_id = 200000005236617
Query        SELECT DATE_FORMAT(th_dts, '%Y-%m-%d %H:%i:%s.%f'), th_st_id FROM trade_history WHERE th_t_id = 200000005236617 ORDER BY th_dts LIMIT 3
Query        COMMIT

-- F3
-- F4

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- F1
SELECT t_ca_id, t_tt_id, t_s_symb, t_qty, t_chrg, t_lifo, t_is_cash FROM trade WHERE t_id = 200000014584794
SELECT tt_name, tt_is_sell, tt_is_mrkt FROM trade_type WHERE tt_id = _latin1'TMS'
SELECT hs_qty FROM holding_summary WHERE hs_ca_id = 43000012441 AND hs_s_symb = _latin1'BDGPRB'
-- F2
SELECT ca_b_id, ca_c_id, ca_tax_st FROM customer_account WHERE ca_id = 43000012441
-- TRF2_2a INSERT, or TRF2_2b UPDATE
UPDATE holding_summary SET hs_qty = 8700 WHERE hs_ca_id = 43000012441 AND hs_s_symb = _latin1'BDGPRB'
-- MEESUT_STMT_TRF2_3a ASC, or TRF2_3b DESC
SELECT h_t_id, h_qty, h_price FROM holding WHERE h_ca_id = '43000012441' AND h_s_symb = 'BDGPRB' ORDER BY h_dts ASC
-- TRF2_4
INSERT INTO holding_history(hh_h_t_id, hh_t_id, hh_before_qty, hh_after_qty) VALUES('200000013784914', '200000014584794', '700', '600')
-- TRF2_5a or TRF2_5b (DELETE)
UPDATE holding SET h_qty = '600' WHERE h_t_id = '200000013784914'
-- TRF3_1 在這個事務(wù)中 miss，如果要尋找，其他事務(wù)中可以搜到
-- SELECT sum(tx_rate) FROM taxrate, customer_taxrate WHERE tx_id = cx_tx_id AND cx_c_id = ?
-- TRF4_1
SELECT s_ex_id, s_name FROM security WHERE s_symb = _latin1'BDGPRB'
-- TRF4_2
SELECT c_tier FROM customer WHERE c_id = 4300001245
-- TRF4_3
SELECT cr_rate FROM commission_rate WHERE cr_c_tier = 1 AND cr_tt_id = _latin1'TMS' AND cr_ex_id = _latin1'NASDAQ' AND cr_from_qty <= 100 AND cr_to_qty >= 100
-- TRF5_1
UPDATE trade SET t_comm = 1.14299999999999997e+01, t_dts = _latin1'2024-02-27 20:48:15.000000', t_st_id = _latin1'CMPT', t_trade_price = 2.85799999999999983e+01 WHERE t_id = 200000014584794
-- TRF5_2
INSERT INTO trade_history(th_t_id, th_dts, th_st_id) VALUES(200000014584794, _latin1'2024-02-27 20:48:15.000000', _latin1'CMPT')
-- TRF5_3
UPDATE broker SET b_comm_total = b_comm_total + 1.14299999999999997e+01, b_num_trades = b_num_trades + 1 WHERE b_id = 4300000017
-- TRF6_1
INSERT INTO settlement(se_t_id, se_cash_type, se_cash_due_date, se_amt) VALUES(200000014584794, _latin1'Cash Account', _latin1'2024-02-29', 2.84157000000000016e+03)
-- TRF6_2
UPDATE customer_account SET ca_bal = ca_bal + 2.84157000000000016e+03 WHERE ca_id = 43000012441
-- TRF6_3
INSERT INTO cash_transaction(ct_dts, ct_t_id, ct_amt, ct_name) VALUES(_latin1'2024-02-27 20:48:15.000000', 200000014584794, 2.84157000000000016e+03, _latin1'Market-Sell 100 shared of PREF_B of Bandag, Inc.')
-- TRF6_4
SELECT ca_bal FROM customer_account WHERE ca_id = 43000012441
COMMIT

-- 設(shè)置事務(wù)隔離級別為 READ COMMITTED，確保事務(wù)在讀取數(shù)據(jù)時的一致性
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 執(zhí)行查詢，獲取最近 50 筆交易的狀態(tài)信息
SELECT 
    t_id, DATE_FORMAT(t_dts,'%Y-%m-%d %H:%i:%s.%f'), st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name 
FROM 
    trade, status_type, trade_type, security, exchange 
WHERE 
    t_ca_id = '43000003162' -- 指定客戶賬戶 ID
    AND st_id = t_st_id -- 確保交易狀態(tài)與交易 ID 匹配
    AND tt_id = t_tt_id -- 確保交易類型與交易 ID 匹配
    AND s_symb = t_s_symb -- 確保證券符號與交易 ID 匹配
    AND ex_id = s_ex_id -- 確保交易所與證券符號匹配
ORDER BY 
    t_dts DESC -- 按交易時間降序排列
LIMIT 50; -- 限制結(jié)果為最近的 50 筆交易

-- 關(guān)閉語句
Close stmt;

-- 執(zhí)行查詢，獲取客戶、賬戶和經(jīng)紀(jì)人的詳細(xì)信息
SELECT 
    c_l_name, c_f_name, b_name 
FROM 
    customer_account, customer, broker 
WHERE 
    ca_id = '43000003162' -- 指定客戶賬戶 ID
    AND c_id = ca_c_id -- 確保客戶賬戶與客戶 ID 匹配
    AND b_id = ca_b_id; -- 確保經(jīng)紀(jì)人 ID 與客戶賬戶匹配

-- 關(guān)閉語句
Close stmt;

-- 提交事務(wù)，確保所有查詢結(jié)果被正確處理
Query COMMIT;

-- 查詢特定交易 ID 的執(zhí)行者名字
SELECT t_exec_name FROM trade WHERE t_id = '200000001949399';

-- 更新執(zhí)行者名字
UPDATE trade SET t_exec_name = 'Jessica X Lowery' WHERE t_id = '200000001949399';

-- 查詢交易相關(guān)信息
SELECT t_bid_price, t_exec_name, t_is_cash, tt_is_mrkt, t_trade_price FROM trade, trade_type WHERE t_id = '200000001949399' AND t_tt_id = tt_id;

-- 查詢結(jié)算信息
SELECT se_amt, DATE_FORMAT(se_cash_due_date, '%Y-%m-%d'), se_cash_type FROM settlement WHERE se_t_id = '200000001949399';

-- 查詢現(xiàn)金交易信息
SELECT ct_amt, DATE_FORMAT(ct_dts, '%Y-%m-%d %H:%i:%s.%f'), ct_name FROM cash_transaction WHERE ct_t_id = '200000001949399';

-- 查詢交易歷史記錄
SELECT DATE_FORMAT(th_dts, '%Y-%m-%d %H:%i:%s.%f'), th_st_id FROM trade_history WHERE th_t_id = '200000001949399' ORDER BY th_dts LIMIT 3;

-- 查詢另一個交易 ID 的執(zhí)行者名字
SELECT t_exec_name FROM trade WHERE t_id = 200000000135883;

-- 更新執(zhí)行者名字
UPDATE trade SET t_exec_name = _latin1'Roxann Kniffen' WHERE t_id = 200000000135883;

-- 提交事務(wù)
COMMIT;

-- 設(shè)置事務(wù)隔離級別為可重復(fù)讀
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查詢特定客戶賬戶 ID 在指定時間范圍內(nèi)的交易信息
SELECT t_bid_price, t_exec_name, t_is_cash, t_id, t_trade_price FROM trade WHERE t_ca_id = 43000008818 AND t_dts >= _latin1'2005-01-27 13:24:52.109000' AND t_dts <= _latin1'2005-03-14 09:15:00.000000' ORDER BY t_dts ASC LIMIT 20;

-- 對于每筆交易，更新結(jié)算類型為 'Cash'
-- 下面的語句會重復(fù)多組
SELECT se_cash_type FROM settlement WHERE se_t_id = 200000002704863;
UPDATE settlement SET se_cash_type = 'Cash' WHERE se_t_id = 200000002704863;

-- 查詢并返回與特定交易 ID 相關(guān)的結(jié)算信息
SELECT se_amt, DATE_FORMAT(se_cash_due_date, '%Y-%m-%d'), se_cash_type FROM settlement WHERE se_t_id = 200000002704863;

-- 查詢并返回與特定交易 ID 相關(guān)的現(xiàn)金交易信息
SELECT ct_amt, DATE_FORMAT(ct_dts, '%Y-%m-%d %H:%i:%s.%f'), ct_name FROM cash_transaction WHERE ct_t_id = 200000002704863;

-- 查詢并返回與特定交易 ID 相關(guān)的交易歷史記錄
SELECT DATE_FORMAT(th_dts, '%Y-%m-%d %H:%i:%s.%f'), th_st_id FROM trade_history WHERE th_t_id = 200000002704863 ORDER BY th_dts LIMIT 3;

-- 提交事務(wù)
Query COMMIT;

-- 查詢特定證券符號在指定時間范圍內(nèi)的交易信息
SELECT t_ca_id, t_exec_name, t_is_cash, t_trade_price, t_qty, s_name, DATE_FORMAT(t_dts, '%Y-%m-%d %H:%i:%s.%f'), t_id, t_tt_id, tt_name FROM trade, trade_type FORCE INDEX(PRIMARY), security WHERE t_s_symb = _latin1'AMGN' AND t_dts >= _latin1'2005-02-09 16:05:31.891000' AND t_dts <= _latin1'2005-03-14 09:15:00.000000' AND tt_id = t_tt_id AND s_symb = t_symb ORDER BY t_dts ASC LIMIT 20;

-- 對于每筆現(xiàn)金交易，更新交易描述
-- 下面的語句會重復(fù)多組
SELECT se_amt, DATE_FORMAT(se_cash_due_date, '%Y-%m-%d'), se_cash_type FROM settlement WHERE se_t_id = 200000004055564;
SELECT ct_name FROM cash_transaction WHERE ct_t_id = 200000004055564;
UPDATE cash_transaction SET ct_name = _latin1'Limit-Sell 400 Shares of COMMON of Amgen, Inc.' WHERE ct_t_id = 200000004055564;

-- 查詢并返回與特定交易 ID 相關(guān)的結(jié)算信息
SELECT ct_amt, DATE_FORMAT(ct_dts, '%Y-%m-%d %H:%i:%s.%f'), ct_name FROM cash_transaction WHERE ct_t_id = 200000004055564;

-- 查詢并返回與特定交易 ID 相關(guān)的交易歷史記錄
SELECT DATE_FORMAT(th_dts, '%Y-%m-%d %H:%i:%s.%f'), th_st_id FROM trade_history WHERE th_t_id = 200000004055564 ORDER BY th_dts ASC LIMIT 3;

-- 設(shè)置事務(wù)隔離級別為 READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 選擇 trade_request 表中的所有交易 ID 并排序
SELECT tr_t_id FROM trade_request ORDER BY tr_t_id;

-- 為每個交易 ID 插入一條記錄到 trade_history 表，表示交易已提交
INSERT INTO trade_history (th_t_id, th_dts, th_st_id) VALUES ('200000014582105', '2024-02-27 20:48:13', 'SBMT');

-- 重復(fù)多次，為每個交易 ID 更新 trade 表，設(shè)置狀態(tài)為已取消，并記錄時間
UPDATE trade SET t_st_id = 'CNCL', t_dts = '2024-02-27 20:48:13' WHERE t_id = '200000014582105';

-- 為已取消的交易插入一條記錄到 trade_history 表
INSERT INTO trade_history (th_t_id, th_dts, th_st_id) VALUES (200000014582105, _latin1'2024-02-27 20:48:13', _latin1'CNCL');

-- 如果有其他交易 ID，也執(zhí)行相同的插入和更新操作
-- 例如：
INSERT INTO trade_history (th_t_id, th_dts, th_st_id) VALUES (200000014582119, _latin1'2024-02-27 20:48:13', _latin1'SBMT');

# 安裝 unixodbc
sudo apt-get install unixodbc unixodbc-dev

# 安裝 mysql8 的驅(qū)動：
wget https://downloads.mysql.com/archives/get/p/10/file/mysql-connector-odbc_8.0.20-1debian9_amd64.deb

sudo dpkg -i mysql-connector-odbc_8.0.20-1debian9_amd64.deb
sudo apt-get install -f

# 設(shè)置 odbc 環(huán)境變量 /etc/odbcinst.ini
# 若[MySQL ODBC 8.0 Driver]已經(jīng)存在，則需要先刪除，避免重復(fù)
cat /etc/odbcinst.ini
[MySQL ODBC 8.0 Driver]
Description=MySQL ODBC 8.0 Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so

# 設(shè)置 odbc 連接信息
cat /etc/odbc.ini
[MySQLServer_ODBC_NAME]
Description=My MySQL tpce
Driver=MySQL ODBC 8.0 Driver
Server=xxx.xxx.xxx.xxx
Port=3308
User=root
Password=password
Database=tpce
Option=3

git clone git@github.com:VincentS/tpcemysql.git

cd tpce_mysql

mkdir flat_out
cd prj
make clean

# 修改 makefile
# 將 CCFLAGS=-g -O2 -Wall  -D__STDC_CONSTANT_MACROS -D__STDC_FORMAT_MACROS -DHANA_ODBC -DUSE_PREPARE 中的 -DHANA_ODBC 修改為 -DMYSQL_ODBC

cp Makefile.Mysql Makefile

make

cd ~/tpcemysql

# 生成數(shù)據(jù)，生成后，數(shù)據(jù)會寫入 flat_out，等待 LOAD DATA INFILE
./bin/EGenLoader -i flat_in -o flat_out -c 2000 -t 2000 -f 200 -w 50

cd scripts/mysql/

# 首先在 mysql 中創(chuàng)建一個空庫 tpce

# 步驟 1：建表
mysql --local-infile=1 -h 127.0.0.1 -uroot -ppassword -P 3308 -Dtpce < 1_create_table.sql

# 導(dǎo)入數(shù)據(jù)等等后續(xù)操作與步驟 1 類似
mysql --local-infile=1 -h 127.0.0.1 -uroot -ppassword -P 3308 -Dtpce < 2_load_data.sql
mysql --local-infile=1 -h 127.0.0.1 -uroot -ppassword -P 3308 -Dtpce < 3_create_index.sql
mysql --local-infile=1 -h 127.0.0.1 -uroot -ppassword -P 3308 -Dtpce < 4_create_fk.sql
mysql --local-infile=1 -h 127.0.0.1 -uroot -ppassword -P 3308 -Dtpce < 5_create_sequence.sql

cd ~/tpcemysql

./bin/EGenSimpleTest -c 2000 -a 2000 -f 200 -d 50 -l 200 -e flat_in -j tpce  -U root -P password -r 10 -u 10 -t 90 -D MySQLServer_ODBC_NAME