Skip to content

Payment transaction

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

Input

  • w_id = const.
  • d_id = random(1, 10)
  • customer select 60% via last name and 40% via id
    • id: c_last = c_id = NURand(1023, 1, 3000)
    • last name: ?generation?
  • warehouse: 85% "home", 15% "remote"
    • home: c_w_id = w_id, c_d_id = d_id
    • remote: c_w_id = random other, c_d_id = Rand(1, 10)
  • h_amount random (?) between 1.0 and 5000.0

Start Transaction

getWarehouse

select W_NAME, W_STREET_1, W_STREET_2, D_CITY, D_STATE, D_ZIP
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_NAME", "W_STREET_1", "W_STREET_2", "W_CITY", "W_STATE", "W_ZIP", "W_YTD"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

UpdateWarehouseBalance

update WAREHOUSE
set W_YTD = W_YTD + h_amount
where W_ID = w_id
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "WAREHOUSE"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "EQ", "in": 0, "f": "W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
         "W_YTD": %(w_ytd)f
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

GetDistrict

select D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
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_NAME", "D_STREET_1", "D_STREET_2", "D_CITY", "D_STATE", "D_ZIP", "D_YTD"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

UpdateDistrictBalance

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

getCustomerByCId

select 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
from CUSTOMER
where C_W_ID = c_w_id
  and C_D_ID = c_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": %(c_w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(c_d_id)d},
         {"type": "EQ", "in": 0, "f": "C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d}
       ]
    },
    "project": {
      "type": "ProjectionScan",
      "fields": ["C_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_DATA"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

GetCutomerByLastName

select C_ID, C_FIRST, C_MIDDLE, 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
from CUSTOMER
where C_W_ID = c_w_id
  and C_D_ID = c_d_id
  and C_LAST = c_last
order by C_FIRST asc
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": %(c_w_id)d},
        {"type": "AND"},
        {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(c_w_id)d},
        {"type": "EQ", "in": 0, "f": "C_LAST", "vtype": 2 /*Str*/, "value": %(c_last)s}
      ]
    },
    "project": {
      "type": "ProjectionScan",
      "fields": ["C_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_DATA"]
    },
    "order": {
      "type": "SortScan",
      "fields": ["C_FIRST"]
    }
  },
  "edges": [["load", "select"], ["select", "project"], ["project", "order"]]
}
n = number of selected rows
balance_row = ceil(n / 2)

updateBCCustomer

update CUSTOMER
set C_BALANCE = C_BALANCE + h_amount,
    C_YTD_PAYMENT = C_YTD_PAYMENT + 1,
    C_PAYMENT_CNT = C_PAYMENT_CNT + 1,
    C_DATA = c_data
where C_ID = c_id
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "CUSTOMER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(c_w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(c_d_id)d},
         {"type": "EQ", "in": 0, "f": "C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
         "C_BALANCE": %(c_balance)f,
         "C_YTD_PAYMENT": %(c_ytd_payment)f,
         "C_PAYMENT_CNT": %(c_payment_cnt)d,
         "C_DATA": %(c_data)s
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

updateGCCustomer

update CUSTOMER
set C_BALANCE = C_BALANCE + h_amount,
    C_YTD_PAYMENT = C_YTD_PAYMENT + 1,
    C_PAYMENT_CNT = C_PAYMENT_CNT + 1,
where C_ID = c_id
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "CUSTOMER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(c_w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(c_d_id)d},
         {"type": "EQ", "in": 0, "f": "C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
         "C_BALANCE": %(c_balance)f,
         "C_YTD_PAYMENT": %(c_ytd_payment)f,
         "C_PAYMENT_CNT": %(c_payment_cnt)d
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

insertHistory

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 (c_id, c_d_id, c_w_id, d_id, w_id, date, h_amount, h_data)
{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "HISTORY"
    },
    "insert": {
       "type": "InsertScan",
       "data" : [[%(c_id)d, %(c_d_id)d, %(c_w_id)d, %(d_id)d, %(w_id)d, %(date)s, %(h_amount)f, %(h_data)s]]
    }
  },
  "edges": [["load", "insert"]]
}

Commit

Output

TPC

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