001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.bench;
007:
008: import java.math.BigDecimal;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.Timestamp;
012: import java.util.HashMap;
013:
014: /**
015: * This class implements the functionality of one thread of BenchC.
016: */
017: public class BenchCThread {
018:
019: private Database db;
020: private int warehouseId;
021: private int terminalId;
022: private HashMap prepared = new HashMap();
023: private static final int OP_NEW_ORDER = 0, OP_PAYMENT = 1,
024: OP_ORDER_STATUS = 2, OP_DELIVERY = 3, OP_STOCK_LEVEL = 4;
025: private static final BigDecimal ONE = new BigDecimal("1");
026: private BenchCRandom random;
027: private BenchC bench;
028:
029: BenchCThread(Database db, BenchC bench, BenchCRandom random,
030: int terminal) throws Exception {
031: this .db = db;
032: this .bench = bench;
033: this .terminalId = terminal;
034: db.setAutoCommit(false);
035: this .random = random;
036: warehouseId = random.getInt(1, bench.warehouses);
037: }
038:
039: void process() throws Exception {
040: int[] deck = new int[] { OP_NEW_ORDER, OP_NEW_ORDER,
041: OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER,
042: OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER,
043: OP_PAYMENT, OP_PAYMENT, OP_PAYMENT, OP_PAYMENT,
044: OP_PAYMENT, OP_PAYMENT, OP_PAYMENT, OP_PAYMENT,
045: OP_PAYMENT, OP_PAYMENT, OP_ORDER_STATUS, OP_DELIVERY,
046: OP_STOCK_LEVEL };
047: int len = deck.length;
048: for (int i = 0; i < len; i++) {
049: int temp = deck[i];
050: int j = random.getInt(0, len);
051: deck[i] = deck[j];
052: deck[j] = temp;
053: }
054: for (int i = 0; i < len; i++) {
055: int op = deck[i];
056: switch (op) {
057: case OP_NEW_ORDER:
058: processNewOrder();
059: break;
060: case OP_PAYMENT:
061: processPayment();
062: break;
063: case OP_ORDER_STATUS:
064: processOrderStatus();
065: break;
066: case OP_DELIVERY:
067: processDelivery();
068: break;
069: case OP_STOCK_LEVEL:
070: processStockLevel();
071: break;
072: default:
073: throw new Error("op=" + op);
074: }
075: }
076: }
077:
078: private void processNewOrder() throws Exception {
079: int dId = random.getInt(1, bench.districtsPerWarehouse);
080: int cId = random.getNonUniform(1023, 1,
081: bench.customersPerDistrict);
082: int olCnt = random.getInt(5, 15);
083: boolean rollback = random.getInt(1, 100) == 1;
084: int[] supplyId = new int[olCnt];
085: int[] itemId = new int[olCnt];
086: int[] quantity = new int[olCnt];
087: int allLocal = 1;
088: for (int i = 0; i < olCnt; i++) {
089: int w;
090: if (bench.warehouses > 1 && random.getInt(1, 100) == 1) {
091: do {
092: w = random.getInt(1, bench.warehouses);
093: } while (w != warehouseId);
094: allLocal = 0;
095: } else {
096: w = warehouseId;
097: }
098: supplyId[i] = w;
099: int item;
100: if (rollback && i == olCnt - 1) {
101: // unused order number
102: item = -1;
103: } else {
104: item = random.getNonUniform(8191, 1, bench.items);
105: }
106: itemId[i] = item;
107: quantity[i] = random.getInt(1, 10);
108: }
109: char[] bg = new char[olCnt];
110: int[] stock = new int[olCnt];
111: BigDecimal[] amt = new BigDecimal[olCnt];
112: Timestamp datetime = new Timestamp(System.currentTimeMillis());
113: PreparedStatement prep;
114: ResultSet rs;
115:
116: prep = prepare("UPDATE DISTRICT SET D_NEXT_O_ID=D_NEXT_O_ID+1 "
117: + "WHERE D_ID=? AND D_W_ID=?");
118: prep.setInt(1, dId);
119: prep.setInt(2, warehouseId);
120: db.update(prep, "updateDistrict");
121: prep = prepare("SELECT D_NEXT_O_ID, D_TAX FROM DISTRICT "
122: + "WHERE D_ID=? AND D_W_ID=?");
123: prep.setInt(1, dId);
124: prep.setInt(2, warehouseId);
125: rs = db.query(prep);
126: rs.next();
127: int oId = rs.getInt(1) - 1;
128: BigDecimal tax = rs.getBigDecimal(2);
129: rs.close();
130: // TODO optimizer: such cases can be optimized! A=1 AND B=A means
131: // also B=1!
132: // prep = prepare("SELECT C_DISCOUNT, C_LAST, C_CREDIT, W_TAX "
133: // + "FROM CUSTOMER, WAREHOUSE "
134: // + "WHERE C_ID=? AND W_ID=? AND C_W_ID=W_ID AND C_D_ID=?");
135: prep = prepare("SELECT C_DISCOUNT, C_LAST, C_CREDIT, W_TAX "
136: + "FROM CUSTOMER, WAREHOUSE "
137: + "WHERE C_ID=? AND C_W_ID=? AND C_W_ID=W_ID AND C_D_ID=?");
138: prep.setInt(1, cId);
139: prep.setInt(2, warehouseId);
140: prep.setInt(3, dId);
141: rs = db.query(prep);
142: rs.next();
143: BigDecimal discount = rs.getBigDecimal(1);
144: rs.getString(2); // c_last
145: rs.getString(3); // c_credit
146: BigDecimal wTax = rs.getBigDecimal(4);
147: rs.close();
148: BigDecimal total = new BigDecimal("0");
149: for (int number = 1; number <= olCnt; number++) {
150: int olId = itemId[number - 1];
151: int olSupplyId = supplyId[number - 1];
152: int olQuantity = quantity[number - 1];
153: prep = prepare("SELECT I_PRICE, I_NAME, I_DATA "
154: + "FROM ITEM WHERE I_ID=?");
155: prep.setInt(1, olId);
156: rs = db.query(prep);
157: if (!rs.next()) {
158: if (rollback) {
159: // item not found - correct behavior
160: db.rollback();
161: return;
162: }
163: throw new Exception("item not found: " + olId + " "
164: + olSupplyId);
165: }
166: BigDecimal price = rs.getBigDecimal(1);
167: rs.getString(2); // i_name
168: String data = rs.getString(3);
169: rs.close();
170: prep = prepare("SELECT S_QUANTITY, S_DATA, "
171: + "S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, "
172: + "S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 "
173: + "FROM STOCK WHERE S_I_ID=? AND S_W_ID=?");
174: prep.setInt(1, olId);
175: prep.setInt(2, olSupplyId);
176: rs = db.query(prep);
177: if (!rs.next()) {
178: if (rollback) {
179: // item not found - correct behavior
180: db.rollback();
181: return;
182: }
183: throw new Exception("item not found: " + olId + " "
184: + olSupplyId);
185: }
186: int sQuantity = rs.getInt(1);
187: String sData = rs.getString(2);
188: String[] dist = new String[10];
189: for (int i = 0; i < 10; i++) {
190: dist[i] = rs.getString(3 + i);
191: }
192: rs.close();
193: String distInfo = dist[dId - 1];
194: stock[number - 1] = sQuantity;
195: if ((data.indexOf("original") != -1)
196: && (sData.indexOf("original") != -1)) {
197: bg[number - 1] = 'B';
198: } else {
199: bg[number - 1] = 'G';
200: }
201: if (sQuantity > olQuantity) {
202: sQuantity = sQuantity - olQuantity;
203: } else {
204: sQuantity = sQuantity - olQuantity + 91;
205: }
206: prep = prepare("UPDATE STOCK SET S_QUANTITY=? "
207: + "WHERE S_W_ID=? AND S_I_ID=?");
208: prep.setInt(1, sQuantity);
209: prep.setInt(2, olSupplyId);
210: prep.setInt(3, olId);
211: db.update(prep, "updateStock");
212: BigDecimal olAmount = new BigDecimal(olQuantity).multiply(
213: price).multiply(ONE.add(wTax).add(tax)).multiply(
214: ONE.subtract(discount));
215: olAmount = olAmount.setScale(2, BigDecimal.ROUND_HALF_UP);
216: amt[number - 1] = olAmount;
217: total = total.add(olAmount);
218: prep = prepare("INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, "
219: + "OL_I_ID, OL_SUPPLY_W_ID, "
220: + "OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) "
221: + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
222: prep.setInt(1, oId);
223: prep.setInt(2, dId);
224: prep.setInt(3, warehouseId);
225: prep.setInt(4, number);
226: prep.setInt(5, olId);
227: prep.setInt(6, olSupplyId);
228: prep.setInt(7, olQuantity);
229: prep.setBigDecimal(8, olAmount);
230: prep.setString(9, distInfo);
231: db.update(prep, "insertOrderLine");
232: }
233: prep = prepare("INSERT INTO ORDERS (O_ID, O_D_ID, O_W_ID, O_C_ID, "
234: + "O_ENTRY_D, O_OL_CNT, O_ALL_LOCAL) "
235: + "VALUES (?, ?, ?, ?, ?, ?, ?)");
236: prep.setInt(1, oId);
237: prep.setInt(2, dId);
238: prep.setInt(3, warehouseId);
239: prep.setInt(4, cId);
240: prep.setTimestamp(5, datetime);
241: prep.setInt(6, olCnt);
242: prep.setInt(7, allLocal);
243: db.update(prep, "insertOrders");
244: prep = prepare("INSERT INTO NEW_ORDER (NO_O_ID, NO_D_ID, NO_W_ID) "
245: + "VALUES (?, ?, ?)");
246: prep.setInt(1, oId);
247: prep.setInt(2, dId);
248: prep.setInt(3, warehouseId);
249: db.update(prep, "insertNewOrder");
250: db.commit();
251: }
252:
253: private void processPayment() throws Exception {
254: int dId = random.getInt(1, bench.districtsPerWarehouse);
255: int wId, cdId;
256: if (bench.warehouses > 1 && random.getInt(1, 100) <= 15) {
257: do {
258: wId = random.getInt(1, bench.warehouses);
259: } while (wId != warehouseId);
260: cdId = random.getInt(1, bench.districtsPerWarehouse);
261: } else {
262: wId = warehouseId;
263: cdId = dId;
264: }
265: boolean byName;
266: String last;
267: int cId = 1;
268: if (random.getInt(1, 100) <= 60) {
269: byName = true;
270: last = random
271: .getLastname(random.getNonUniform(255, 0, 999));
272: } else {
273: byName = false;
274: last = "";
275: cId = random.getNonUniform(1023, 1,
276: bench.customersPerDistrict);
277: }
278: BigDecimal amount = random.getBigDecimal(random.getInt(100,
279: 500000), 2);
280: Timestamp datetime = new Timestamp(System.currentTimeMillis());
281: PreparedStatement prep;
282: ResultSet rs;
283:
284: prep = prepare("UPDATE DISTRICT SET D_YTD = D_YTD+? "
285: + "WHERE D_ID=? AND D_W_ID=?");
286: prep.setBigDecimal(1, amount);
287: prep.setInt(2, dId);
288: prep.setInt(3, warehouseId);
289: db.update(prep, "updateDistrict");
290: prep = prepare("UPDATE WAREHOUSE SET W_YTD=W_YTD+? WHERE W_ID=?");
291: prep.setBigDecimal(1, amount);
292: prep.setInt(2, warehouseId);
293: db.update(prep, "updateWarehouse");
294: prep = prepare("SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME "
295: + "FROM WAREHOUSE WHERE W_ID=?");
296: prep.setInt(1, warehouseId);
297: rs = db.query(prep);
298: rs.next();
299: rs.getString(1); // w_street_1
300: rs.getString(2); // w_street_2
301: rs.getString(3); // w_city
302: rs.getString(4); // w_state
303: rs.getString(5); // w_zip
304: String wName = rs.getString(6);
305: rs.close();
306: prep = prepare("SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME "
307: + "FROM DISTRICT WHERE D_ID=? AND D_W_ID=?");
308: prep.setInt(1, dId);
309: prep.setInt(2, warehouseId);
310: rs = db.query(prep);
311: rs.next();
312: rs.getString(1); // d_street_1
313: rs.getString(2); // d_street_2
314: rs.getString(3); // d_city
315: rs.getString(4); // d_state
316: rs.getString(5); // d_zip
317: String dName = rs.getString(6);
318: rs.close();
319: BigDecimal balance;
320: String credit;
321: if (byName) {
322: prep = prepare("SELECT COUNT(C_ID) FROM CUSTOMER "
323: + "WHERE C_LAST=? AND C_D_ID=? AND C_W_ID=?");
324: prep.setString(1, last);
325: prep.setInt(2, cdId);
326: prep.setInt(3, wId);
327: rs = db.query(prep);
328: rs.next();
329: int namecnt = rs.getInt(1);
330: rs.close();
331: if (namecnt == 0) {
332: // TODO TPC-C: check if this can happen
333: db.rollback();
334: return;
335: }
336: prep = prepare("SELECT C_FIRST, C_MIDDLE, C_ID, "
337: + "C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, "
338: + "C_PHONE, C_CREDIT, C_CREDIT_LIM, "
339: + "C_DISCOUNT, C_BALANCE, C_SINCE FROM CUSTOMER "
340: + "WHERE C_LAST=? AND C_D_ID=? AND C_W_ID=? "
341: + "ORDER BY C_FIRST");
342: prep.setString(1, last);
343: prep.setInt(2, cdId);
344: prep.setInt(3, wId);
345: rs = db.query(prep);
346: // locate midpoint customer
347: if (namecnt % 2 != 0) {
348: namecnt++;
349: }
350: for (int n = 0; n < namecnt / 2; n++) {
351: rs.next();
352: }
353: rs.getString(1); // c_first
354: rs.getString(2); // c_middle
355: cId = rs.getInt(3);
356: rs.getString(4); // c_street_1
357: rs.getString(5); // c_street_2
358: rs.getString(6); // c_city
359: rs.getString(7); // c_state
360: rs.getString(8); // c_zip
361: rs.getString(9); // c_phone
362: credit = rs.getString(10);
363: rs.getString(11); // c_credit_lim
364: rs.getBigDecimal(12); // c_discount
365: balance = rs.getBigDecimal(13);
366: rs.getTimestamp(14); // c_since
367: rs.close();
368: } else {
369: prep = prepare("SELECT C_FIRST, C_MIDDLE, C_LAST, "
370: + "C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, "
371: + "C_PHONE, C_CREDIT, C_CREDIT_LIM, "
372: + "C_DISCOUNT, C_BALANCE, C_SINCE FROM CUSTOMER "
373: + "WHERE C_ID=? AND C_D_ID=? AND C_W_ID=?");
374: prep.setInt(1, cId);
375: prep.setInt(2, cdId);
376: prep.setInt(3, wId);
377: rs = db.query(prep);
378: rs.next();
379: rs.getString(1); // c_first
380: rs.getString(2); // c_middle
381: rs.getString(3); // c_last
382: rs.getString(4); // c_street_1
383: rs.getString(5); // c_street_2
384: rs.getString(6); // c_city
385: rs.getString(7); // c_state
386: rs.getString(8); // c_zip
387: rs.getString(9); // c_phone
388: credit = rs.getString(10);
389: rs.getString(11); // c_credit_lim
390: rs.getBigDecimal(12); // c_discount
391: balance = rs.getBigDecimal(13);
392: rs.getTimestamp(14); // c_since
393: rs.close();
394: }
395: balance = balance.add(amount);
396: if (credit.equals("BC")) {
397: prep = prepare("SELECT C_DATA INTO FROM CUSTOMER "
398: + "WHERE C_ID=? AND C_D_ID=? AND C_W_ID=?");
399: prep.setInt(1, cId);
400: prep.setInt(2, cdId);
401: prep.setInt(3, wId);
402: rs = db.query(prep);
403: rs.next();
404: String cData = rs.getString(1);
405: rs.close();
406: String cNewData = "| " + cId + " " + cdId + " " + wId + " "
407: + dId + " " + warehouseId + " " + amount + " "
408: + cData;
409: if (cNewData.length() > 500) {
410: cNewData = cNewData.substring(0, 500);
411: }
412: prep = prepare("UPDATE CUSTOMER SET C_BALANCE=?, C_DATA=? "
413: + "WHERE C_ID=? AND C_D_ID=? AND C_W_ID=?");
414: prep.setBigDecimal(1, balance);
415: prep.setString(2, cNewData);
416: prep.setInt(3, cId);
417: prep.setInt(4, cdId);
418: prep.setInt(5, wId);
419: db.update(prep, "updateCustomer");
420: } else {
421: prep = prepare("UPDATE CUSTOMER SET C_BALANCE=? "
422: + "WHERE C_ID=? AND C_D_ID=? AND C_W_ID=?");
423: prep.setBigDecimal(1, balance);
424: prep.setInt(2, cId);
425: prep.setInt(3, cdId);
426: prep.setInt(4, wId);
427: db.update(prep, "updateCustomer");
428: }
429: // MySQL bug?
430: // String h_data = w_name + " " + d_name;
431: String hData = wName + " " + dName;
432: prep = prepare("INSERT INTO HISTORY (H_C_D_ID, H_C_W_ID, H_C_ID, H_D_ID, "
433: + "H_W_ID, H_DATE, H_AMOUNT, H_DATA) "
434: + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
435: prep.setInt(1, cdId);
436: prep.setInt(2, wId);
437: prep.setInt(3, cId);
438: prep.setInt(4, dId);
439: prep.setInt(5, warehouseId);
440: prep.setTimestamp(6, datetime);
441: prep.setBigDecimal(7, amount);
442: prep.setString(8, hData);
443: db.update(prep, "insertHistory");
444: db.commit();
445: }
446:
447: private void processOrderStatus() throws Exception {
448: int dId = random.getInt(1, bench.districtsPerWarehouse);
449: boolean byName;
450: String last = null;
451: int cId = -1;
452: if (random.getInt(1, 100) <= 60) {
453: byName = true;
454: last = random
455: .getLastname(random.getNonUniform(255, 0, 999));
456: } else {
457: byName = false;
458: cId = random.getNonUniform(1023, 1,
459: bench.customersPerDistrict);
460: }
461: PreparedStatement prep;
462: ResultSet rs;
463:
464: prep = prepare("UPDATE DISTRICT SET D_NEXT_O_ID=-1 WHERE D_ID=-1");
465: db.update(prep, "updateDistrict");
466: if (byName) {
467: prep = prepare("SELECT COUNT(C_ID) FROM CUSTOMER "
468: + "WHERE C_LAST=? AND C_D_ID=? AND C_W_ID=?");
469: prep.setString(1, last);
470: prep.setInt(2, dId);
471: prep.setInt(3, warehouseId);
472: rs = db.query(prep);
473: rs.next();
474: int namecnt = rs.getInt(1);
475: rs.close();
476: if (namecnt == 0) {
477: // TODO TPC-C: check if this can happen
478: db.rollback();
479: return;
480: }
481: prep = prepare("SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_ID "
482: + "FROM CUSTOMER "
483: + "WHERE C_LAST=? AND C_D_ID=? AND C_W_ID=? "
484: + "ORDER BY C_FIRST");
485: prep.setString(1, last);
486: prep.setInt(2, dId);
487: prep.setInt(3, warehouseId);
488: rs = db.query(prep);
489: if (namecnt % 2 != 0) {
490: namecnt++;
491: }
492: for (int n = 0; n < namecnt / 2; n++) {
493: rs.next();
494: }
495: rs.getBigDecimal(1); // c_balance
496: rs.getString(2); // c_first
497: rs.getString(3); // c_middle
498: rs.close();
499: } else {
500: prep = prepare("SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_LAST "
501: + "FROM CUSTOMER "
502: + "WHERE C_ID=? AND C_D_ID=? AND C_W_ID=?");
503: prep.setInt(1, cId);
504: prep.setInt(2, dId);
505: prep.setInt(3, warehouseId);
506: rs = db.query(prep);
507: rs.next();
508: rs.getBigDecimal(1); // c_balance
509: rs.getString(2); // c_first
510: rs.getString(3); // c_middle
511: rs.getString(4); // c_last
512: rs.close();
513: }
514: prep = prepare("SELECT MAX(O_ID) "
515: + "FROM ORDERS WHERE O_C_ID=? AND O_D_ID=? AND O_W_ID=?");
516: prep.setInt(1, cId);
517: prep.setInt(2, dId);
518: prep.setInt(3, warehouseId);
519: rs = db.query(prep);
520: int oId = -1;
521: if (rs.next()) {
522: oId = rs.getInt(1);
523: if (rs.wasNull()) {
524: oId = -1;
525: }
526: }
527: rs.close();
528: if (oId != -1) {
529: prep = prepare("SELECT O_ID, O_CARRIER_ID, O_ENTRY_D "
530: + "FROM ORDERS WHERE O_ID=?");
531: prep.setInt(1, oId);
532: rs = db.query(prep);
533: rs.next();
534: oId = rs.getInt(1);
535: rs.getInt(2); // o_carrier_id
536: rs.getTimestamp(3); // o_entry_d
537: rs.close();
538: prep = prepare("SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, "
539: + "OL_AMOUNT, OL_DELIVERY_D FROM ORDER_LINE "
540: + "WHERE OL_O_ID=? AND OL_D_ID=? AND OL_W_ID=?");
541: prep.setInt(1, oId);
542: prep.setInt(2, dId);
543: prep.setInt(3, warehouseId);
544: rs = db.query(prep);
545: while (rs.next()) {
546: rs.getInt(1); // o_i_id
547: rs.getInt(2); // ol_supply_w_id
548: rs.getInt(3); // ol_quantity
549: rs.getBigDecimal(4); // ol_amount
550: rs.getTimestamp(5); // ol_delivery_d
551: }
552: rs.close();
553: }
554: db.commit();
555: }
556:
557: private void processDelivery() throws Exception {
558: int carrierId = random.getInt(1, 10);
559: Timestamp datetime = new Timestamp(System.currentTimeMillis());
560: PreparedStatement prep;
561: ResultSet rs;
562:
563: prep = prepare("UPDATE DISTRICT SET D_NEXT_O_ID=-1 WHERE D_ID=-1");
564: db.update(prep, "updateDistrict");
565: for (int dId = 1; dId <= bench.districtsPerWarehouse; dId++) {
566: prep = prepare("SELECT MIN(NO_O_ID) FROM NEW_ORDER "
567: + "WHERE NO_D_ID=? AND NO_W_ID=?");
568: prep.setInt(1, dId);
569: prep.setInt(2, warehouseId);
570: rs = db.query(prep);
571: int noId = -1;
572: if (rs.next()) {
573: noId = rs.getInt(1);
574: if (rs.wasNull()) {
575: noId = -1;
576: }
577: }
578: rs.close();
579: if (noId != -1) {
580: prep = prepare("DELETE FROM NEW_ORDER "
581: + "WHERE NO_O_ID=? AND NO_D_ID=? AND NO_W_ID=?");
582: prep.setInt(1, noId);
583: prep.setInt(2, dId);
584: prep.setInt(3, warehouseId);
585: db.update(prep, "deleteNewOrder");
586: prep = prepare("SELECT O_C_ID FROM ORDERS "
587: + "WHERE O_ID=? AND O_D_ID=? AND O_W_ID=?");
588: prep.setInt(1, noId);
589: prep.setInt(2, dId);
590: prep.setInt(3, warehouseId);
591: rs = db.query(prep);
592: rs.next();
593: rs.getInt(1); // o_c_id
594: rs.close();
595: prep = prepare("UPDATE ORDERS SET O_CARRIER_ID=? "
596: + "WHERE O_ID=? AND O_D_ID=? AND O_W_ID=?");
597: prep.setInt(1, carrierId);
598: prep.setInt(2, noId);
599: prep.setInt(3, dId);
600: prep.setInt(4, warehouseId);
601: db.update(prep, "updateOrders");
602: prep = prepare("UPDATE ORDER_LINE SET OL_DELIVERY_D=? "
603: + "WHERE OL_O_ID=? AND OL_D_ID=? AND OL_W_ID=?");
604: prep.setTimestamp(1, datetime);
605: prep.setInt(2, noId);
606: prep.setInt(3, dId);
607: prep.setInt(4, warehouseId);
608: db.update(prep, "updateOrderLine");
609: prep = prepare("SELECT SUM(OL_AMOUNT) FROM ORDER_LINE "
610: + "WHERE OL_O_ID=? AND OL_D_ID=? AND OL_W_ID=?");
611: prep.setInt(1, noId);
612: prep.setInt(2, dId);
613: prep.setInt(3, warehouseId);
614: rs = db.query(prep);
615: rs.next();
616: BigDecimal amount = rs.getBigDecimal(1);
617: rs.close();
618: prep = prepare("UPDATE CUSTOMER SET C_BALANCE=C_BALANCE+? "
619: + "WHERE C_ID=? AND C_D_ID=? AND C_W_ID=?");
620: prep.setBigDecimal(1, amount);
621: prep.setInt(2, noId);
622: prep.setInt(3, dId);
623: prep.setInt(4, warehouseId);
624: db.update(prep, "updateCustomer");
625: }
626: }
627: db.commit();
628: }
629:
630: private void processStockLevel() throws Exception {
631: int dId = (terminalId % bench.districtsPerWarehouse) + 1;
632: int threshold = random.getInt(10, 20);
633: PreparedStatement prep;
634: ResultSet rs;
635:
636: prep = prepare("UPDATE DISTRICT SET D_NEXT_O_ID=-1 WHERE D_ID=-1");
637: db.update(prep, "updateDistrict");
638:
639: prep = prepare("SELECT D_NEXT_O_ID FROM DISTRICT "
640: + "WHERE D_ID=? AND D_W_ID=?");
641: prep.setInt(1, dId);
642: prep.setInt(2, warehouseId);
643: rs = db.query(prep);
644: rs.next();
645: int oId = rs.getInt(1);
646: rs.close();
647: // prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
648: // + "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
649: // + "OL_D_ID=? AND OL_O_ID<? AND "
650: // + "OL_O_ID>=?-20 AND S_W_ID=? AND "
651: // + "S_I_ID=OL_I_ID AND S_QUANTITY<?");
652: // prep.setInt(1, warehouseId);
653: // prep.setInt(2, d_id);
654: // prep.setInt(3, o_id);
655: // prep.setInt(4, o_id);
656: prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
657: + "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
658: + "OL_D_ID=? AND OL_O_ID<? AND "
659: + "OL_O_ID>=? AND S_W_ID=? AND "
660: + "S_I_ID=OL_I_ID AND S_QUANTITY<?");
661: prep.setInt(1, warehouseId);
662: prep.setInt(2, dId);
663: prep.setInt(3, oId);
664: prep.setInt(4, oId - 20);
665: prep.setInt(5, warehouseId);
666: prep.setInt(6, threshold);
667: // TODO this is where HSQLDB is very slow
668: rs = db.query(prep);
669: rs.next();
670: rs.getInt(1); // stockCount
671: rs.close();
672: db.commit();
673: }
674:
675: private PreparedStatement prepare(String sql) throws Exception {
676: PreparedStatement prep = (PreparedStatement) prepared.get(sql);
677: if (prep == null) {
678: prep = db.prepare(sql);
679: prepared.put(sql, prep);
680: }
681: return prep;
682: }
683:
684: }
|