Skip to content

New Order Transaction

Jump to bottom Edit New page
Georg Höfer edited this page Dec 2, 2013 · 43 revisions

Input

  • w_id = d_w_id = const.
  • d_id = c_d_id = rand(1, 10)
  • c_id = NURand(1023, 1, 3000) from District
  • ol_cnt = Rand(5, 15)
  • rbk: 1% true (do rollback), 99% false
  • foreach ol_cnt:
    • ol_i_id = NURand(8191, 1, 100000); if last item on order and rbk => >unused value<
    • ol_supply_w_id: 1% w_id ("home"); 99% other warehouse ("remote")
    • ol_quantity = Rand(1,10)
  • all_local: 1 if all order-lines are home otherwise 0
  • o_entry_d = currentSystemDateTime (to be retrieved by SUT!)

Start Transaction

GetWarehouseTaxRate

select W_TAX
from WAREHOUSE
where W_ID = w_id
limit 1
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "WAREHOUSE"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "EQ", "in": 0, "f": "W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["W_TAX"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

=>w_tax

GetDistrict

select D_TAX, D_NEXT_O_ID
from DISTRICT
where D_W_ID = w_id
  and D_ID = d_id
limit 1
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "DISTRICT"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "D_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "EQ", "in": 0, "f": "D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["D_TAX", "D_NEXT_O_ID"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

=> d_tax, o_id

UpdateDistrict

update DISTRICT
set D_NEXT_O_ID = D_NEXT_O_ID + 1
where D_W_ID = w_id
  and D_ID = d_id
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "DISTRICT"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "D_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
          "D_NEXT_O_ID": %(d_next_o_id)d
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

GetCustomer

select C_DISCOUNT, C_LAST, C_CREDIT
from CUSTOMER
where C_W_ID = w_id
  and C_D_ID = d_id
  and C_ID = c_id
limit 1
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "CUSTOMER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["C_DISCOUNT", "C_LAST", "C_CREDIT"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

=> c_discount, c_last, c_credit

CreateOrder

insert into ORDER (O_ID, O_D_ID, O_W_ID, O_C_ID, O_ENTRY_D, O_CARRIER_ID, O_OL_CNT, O_ALL_LOCAL)
values (o_id, d_id, w_id, c_id, o_entry_d, o_carrier_id, o_ol_cnt, all_local)
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDERS"
    },
    "insert": {
       "type": "InsertScan",
       "data" : [[%(o_id)d, %(d_id)d, %(w_id)d, %(c_id)d, %(date)s, %(o_carrier_id)d, %(o_ol_cnt)d, %(all_local)d]]
    }
  },
  "edges": [["load", "insert"]]
}

CreateNewOrder

insert into NEW_ORDER (NO_O_ID, NO_D_ID, NO_W_ID)
values (o_id, d_id, w_id)
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "NEW_ORDER"
    },
    "insert": {
       "type": "InsertScan",
       "data" : [[%(o_id)d, %(d_id)d, %(w_id)d]]
    }
  },
  "edges": [["load", "insert"]]
}

GetItemInfo (ol_cnt times)

select I_PRICE, I_NAME, I_DATA
from ITEM
where I_ID = ol_i_id
limit 1
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ITEM"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "EQ", "in": 0, "f": "I_ID", "vtype": 0 /*Int*/, "value": %(i_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["I_PRICE", "I_NAME", "I_DATA"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

=> i_price, i_name, i_data, i_id

(opt) Rollback

GetStockData (ol_cnt times)

S_DIST_XX => S_DIST_01, S_DIST_02 ...

select S_QUANTITY, S_DIST_xx, S_DATA
from STOCK
where S_I_ID = ol_i_id
  and S_W_ID = ol_supply_w_id
limit 1
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "STOCK"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "S_I_ID", "vtype": 0 /*Int*/, "value": %(ol_i_id)d},
         {"type": "EQ", "in": 0, "f": "S_W_ID", "vtype": 0 /*Int*/, "value": %(ol_supply_w_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["S_QUANTITY", "S_DATA", "S_YTD", "S_ORDER_CNT", "S_REMOTE_CNT", "S_DIST_%(d_id)2d"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

=> s_quantity, s_dist, s_data

UpdateStock (ol_cnt times)

Client:

if (s_quantity - ol_quantity >= 10)
  s_quantity = s_quantity - ol_quantity;
else
  s_quantity = s_quantity - ol_quantity + 91;
update STOCK
set S_QUANTITY = s_quantity,
    S_YTD = S_YTD + ol_quantity,
    S_ORDER_CNT = S_ORDER_CNT + 1
where S_I_ID = ol_i_id
  and S_W_ID = ol_supply_w_id
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "STOCK"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "S_I_ID", "vtype": 0 /*Int*/, "value": %(ol_i_id)d},
         {"type": "EQ", "in": 0, "f": "S_W_ID", "vtype": 0 /*Int*/, "value": %(ol_supply_w_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
          "S_QUANTITY": %(s_quantity)d,
          "S_ORDER_CNT": %(s_order_cnt)d,
          "S_REMOTE_CNT": %(s_remote_cnt)d,
          "S_YTD": %(s_ytd)f
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

Client:

ol_amount = ol_quantity * i_price;

isOriginal (ol_cnt times)

Client:

if (l_data.find("ORIGINAL") != string::npos && s_data.find("ORIGINAL") != string::npos)
  brand-generic = "B";
else
  brand-generic = "G";

CreateOrderLine (ol_cnt times)

insert into ORDER-LINE (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 (o_id, d_id, w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDER_LINE"
    },
    "insert": {
       "type": "InsertScan",
       "data" : [[%(o_id)d, %(d_id)d, %(w_id)d, %(ol_number)d, %(ol_i_id)d, %(ol_supply_w_id)d, %(date)s, %(ol_quantity)d, %(ol_amount)f, %(ol_dist_info)s]]
    }
  },
  "edges": [["load", "insert"]]
}

GetTotalAmount

select sum(OL_AMOUNT)
from ORDER-LINE
{
  "operators": {
    "load": {
        "type": "TableLoad",
        "table": "ORDER-LINE",
        "filename": "order_line.tbl"
    },
    "hash": {
      "type": "HashBuild",
      "fields": [>all<], <TODO>
      "key": "groupby"
    },
    "groupby": {
        "type": "GroupByScan",
        "fields": [>all<], <TODO>
        "functions": [
            {"type": 0 /*SUM*/, "field": "OL_AMOUNT"},
          ]
      }
  },
  "edges" : [["load", "hash"], ["load", "groupby"], ["hash", "groupby"]]
}

=> sum_of_amounts

Client:

total_amount = sum_of_amounts * (1 - c_discount) * (1 + w_tax + d_tax);

Commit

Output

TPC

Add a custom footer
Add a custom sidebar
Clone this wiki locally