tpcc_current (Дополнительные методические материалы), страница 25

Only thebasic fu nctionality of the TPC-C transactions is su p p lied . All term inal I/ O Oop erations, and m iscellaneou s fu nctionshave been left ou t of these exam p les. The cod e p resented here is for d em onstration p u rp oses only, and is n ot m eantto be an op tim al im p lem entation.N ote: The exam p les in this ap p end ix, in som e areas, m ay not follow all the requ irem ents of the benchm ark. In case ofd iscrep ancy betw een the sp ecifications and the p rogram m ing exam p les, the sp ecifications p revail.A.1The N ew -Order Transactionint new ord (){EXEC SQL WH EN EVER NOT FOUN D GOTO sqlerr;EXEC SQL WH EN EVER SQLERROR GOTO sqlerr;gettim estam p (d atetim e);EXEC SQL SELECT c_d iscount, c_last, c_cred it, w _taxIN TO :c_d iscount, :c_last, :c_credit, :w _taxFROM custom er, w arehouseWH ERE w _id = :w _id AN D c_w _id = w _id AN Dc_d_id = :d _id AN D c_id = :c_id ;EXEC SQL SELECT d _next_o_id , d _tax IN TO :d _next_o_id , :d_taxFROM d istrictWH ERE d _id = :d_id AN D d _w _id = :w _id ;EXEC SQL UPDATE d istrict SET d _next_o_id = :d _next_o_id + 1WH ERE d _id = :d_id AN D d _w _id = :w _id ;o_id =d _next_o_id ;EXEC SQL IN SERT IN TO ORDERS (o_id , o_d _id , o_w _id , o_c_id ,o_entry_d , o_ol_cnt, o_all_local)VALUES (:o_id , :d _id , :w _id , :c_id ,:d atetim e, :o_ol_cnt, :o_all_local);EXEC SQL IN SERT IN TO N EW_ORDER (no_o_id , no_d_id , no_w _id )VALUES (:o_id , :d _id , :w _id );for (ol_num ber=1; ol_num ber<=o_ol_cnt; ol_num ber++){ol_supply_w _id =atol(supw are[ol_num ber -1]);if (ol_supply_w _id != w _id) o_all_local=0;ol_i_id =atol(item id [ol_num ber-1]);ol_qu antity=atol(qty[ol_num ber-1]);EXEC SQL WH EN EVER N OT FOUN D GOTO invalid item ;EXEC SQL SELECT i_price, i_nam e , i_dataIN TO :i_price, :i_nam e, :i_dataFROM itemTPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 108 of 130WH ERE i_id = :ol_i_id ;price[ol_num ber-1] = i_price;strncpy(inam e[ol_num ber-1],i_nam e,24);EXEC SQL WH EN EVER N OT FOUN D GOTO sqlerr;EXEC SQL SELECT s_quantity, s_d ata,s_d ist_01, s_dist_02, s_d ist_03, s_d ist_04, s_d ist_05s_d ist_06, s_dist_07, s_d ist_08, s_d ist_09, s_d ist_10IN TO :s_quantity, :s_d ata,:s_d ist_01, :s_d ist_02, :s_dist_03, :s_d ist_04, :s_d ist_05:s_d ist_06, :s_d ist_07, :s_dist_08, :s_d ist_09, :s_d ist_10FROM stockWH ERE s_i_id = :ol_i_id AN D s_w _id = :ol_supply_w _id ;pick_d ist_info(ol_d ist_info, ol_w _id ); / / pick correct s_dist_xxstock[ol_num ber-1] = s_quantity;if ( (strstr(i_data,"original") != N ULL) &&(strstr(s_d ata,"original") != N ULL) )bg[ol_num ber-1] = 'B';elsebg[ol_num ber-1] = 'G';if (s_quantity > ol_quantity)s_quantity = s_quantity - ol_quantity;elses_quantity = s_quantity - ol_quantity + 91;EXEC SQL UPDATE stock SET s_quantity = :s_quantityWH ERE s_i_id = :ol_i_idAN D s_w _id = :ol_supply_w _id ;ol_am ount = ol_quantity * i_price * (1+w _tax+d_tax) * (1-c_d iscount);am t[ol_num ber-1]=ol_am ount;total += ol_am ount;EXEC SQL IN SERTIN TO ord er_line (ol_o_id , ol_d_id , ol_w _id , ol_num ber,ol_i_id , ol_supply_w _id ,ol_quantity, ol_am ount, ol_dist_info)VALUES (:o_id , :d_id , :w _id , :ol_num ber,:ol_i_id , :ol_supply_w _id ,:ol_quantity, :ol_am ount, :ol_d ist_info);} / *End Ord er Lines*/EXEC SQL COMMIT WORK;return(0);invalid item :EXEC SQL ROLLBACK WORK;printf("Item num ber is not valid ");return(0);sqlerr:error();}TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 109 of 130A.2The Payment Transactionint paym ent(){EXEC SQL WH EN EVER NOT FOUN D GOTO sqlerr;EXEC SQL WH EN EVER SQLERROR GOTO sqlerr;gettim estam p (d atetim e);EXEC SQL UPDATE w arehouse SET w _ytd = w _ytd + :h_am ountWH ERE w _id =:w _id ;EXEC SQL SELECT w _street_1, w _street_2, w _city, w _state, w _zip, w _nam eIN TO :w _street_1, :w _street_2, :w _city, :w _state, :w _zip, :w _nam eFROM w arehouseWH ERE w _id =:w _id ;EXEC SQL UPDATE d istrict SET d _ytd = d_ytd + :h_am ountWH ERE d _w _id =:w _id AN D d _id =:d _id ;EXEC SQL SELECT d _street_1, d _street_2, d_city, d_state, d _zip, d _nam eIN TO :d _street_1, :d _street_2, :d _city, :d _state, :d _zip , :d _nam eFROM d istrictWH ERE d _w _id =:w _id AN D d _id =:d _id ;if (bynam e){EXEC SQL SELECT count(c_id ) IN TO :nam ecntFROM custom erWH ERE c_last=:c_last AN D c_d _id =:c_d _id AN D c_w _id =:c_w _id ;EXEC SQL DECLARE c_bynam e CURSOR FORSELECT c_first, c_m idd le, c_id,c_street_1, c_street_2, c_city, c_state, c_zip,c_phone, c_cred it, c_cred it_lim ,c_d iscount, c_balance, c_sinceFROM custom erWH ERE c_w _id =:c_w _id AN D c_d _id =:c_d _id AN D c_last=:c_lastORDER BY c_first;EXEC SQL OPEN c_byname;if (nam ecnt%2) nam ecnt++; / / Locate m id point custom er;for (n=0; n<nam ecnt/ 2; n++){EXEC SQL FETCH c_bynam eIN TO :c_first, :c_m id dle, :c_id,:c_street_1, :c_street_2, :c_city, :c_state, :c_zip,:c_phone, :c_cred it, :c_cred it_lim ,:c_discount, :c_balance, :c_since;}EXEC SQL CLOSE c_bynam e;}else{EXEC SQL SELECT c_first, c_m id d le, c_last,TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 110 of 130c_street_1, c_street_2, c_city, c_state, c_zip,c_phone, c_cred it, c_cred it_lim ,c_d iscount, c_balance, c_sinceIN TO :c_first, :c_m id dle, :c_last,:c_street_1, :c_street_2, :c_city, :c_state, :c_zip,:c_phone, :c_cred it, :c_cred it_lim ,:c_discount, :c_balance, :c_sinceFROM custom erWH ERE c_w _id =:c_w _id AN D c_d _id =:c_d _id AN D c_id =:c_id ;}c_balance += h_am ount;c_credit[2]='\ 0';if (strstr(c_cred it, "BC") ){EXEC SQL SELECT c_data IN TO :c_d ataFROM custom erWH ERE c_w _id =:c_w _id AN D c_d _id =:c_d _id AN D c_id =:c_id ;sprintf(c_new _d ata,"| %4d %2d %4d %2d %4d $%7.2f %12c %24c",c_id ,c_d_id ,c_w _id ,d_id ,w _id ,h_am ounth_date, h_d ata);strncat(c_new _data,c_data,500-strlen(c_new _d ata));EXEC SQL UPDATE custom erSET c_balance = :c_balance, c_d ata = :c_new _dataWH ERE c_w _id = :c_w _id AN D c_d _id = :c_d _id AN Dc_id = :c_id;}else{EXEC SQL UPDATE custom er SET c_balance = :c_balanceWH ERE c_w _id = :c_w _id AN D c_d _id = :c_d _id AN Dc_id = :c_id;}strncpy(h_data,w _nam e,10);h_d ata[10]='\ 0';strncat(h_data,d _nam e,10);h_d ata[20]=' ';h_d ata[21]=' ';h_d ata[22]=' ';h_d ata[23]=' ';EXEC SQL IN SERT IN TO history (h_c_d _id , h_c_w _id, h_c_id , h_d _id ,h_w _id , h_d ate, h_am ount, h_d ata)VALUES (:c_d _id, :c_w _id, :c_id , :d_id ,:w _id , :d atetim e, :h_am ount, :h_data);EXEC SQL COMMIT WORK;return(0);sqlerr:error();}TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 111 of 130A.3The Order-Status Transactionint ostat(){EXEC SQL WH EN EVER NOT FOUN D GOTO sqlerr;EXEC SQL WH EN EVER SQLERROR GOTO sqlerr;if (bynam e){EXEC SQL SELECT count(c_id ) IN TO :nam ecntFROM custom erWH ERE c_last=:c_last AN D c_d _id =:d _id AN D c_w _id =:w _id;EXEC SQL DECLARE c_nam e CURSOR FORSELECT c_balance, c_first, c_m id d le, c_idFROM custom erWH ERE c_last=:c_last AN D c_d _id =:d _id AN D c_w _id =:w _idORDER BY c_first;EXEC SQL OPEN c_nam e;if (nam ecnt%2) nam ecnt++; / / Locate m id point custom erfor (n=0; n<nam ecnt/ 2; n++){EXEC SQL FETCH c_nameIN TO :c_balance, :c_first, :c_m id dle, :c_id ;}EXEC SQL CLOSE c_nam e;}else {EXEC SQL SELECT c_balance, c_first, c_m id d le, c_lastIN TO :c_balance, :c_first, :c_m id dle, :c_lastFROM custom erWH ERE c_id =:c_id AN D c_d _id =:d _id AN D c_w _id =:w _id;}EXEC SQL SELECT o_id, o_carrier_id, o_entry_dIN TO :o_id , :o_carrier_id , :entdateFROM ord ersORDER BY o_id DESC;EXEC SQL DECLARE c_line CURSOR FORSELECT ol_i_id , ol_supply_w _id , ol_quantity,ol_am ount, ol_d elivery_dFROM ord er_lineWH ERE ol_o_id =:o_id AN D ol_d _id =:d _id AN D ol_w _id =:w _id ;EXEC SQL OPEN c_line;EXEC SQL WH EN EVER NOT FOUN D CON TIN UE;i=0;w hile (sql_notfound (FALSE)){i++;EXEC SQL FETCH c_lineIN TO :ol_i_id [i], :ol_supply_w _id [i], :ol_quantity[i],:ol_am ount[i], :ol_d elivery_d [i];}TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 112 of 130EXEC SQL CLOSE c_line;EXEC SQL COMMIT WORK;return(0);sqlerr:error();}TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 113 of 130A.4The D elivery Transactionint d elivery(){EXEC SQL WH EN EVER SQLERROR GOTO sqlerr;gettim estam p (d atetim e);/ * For each district in w arehouse */printf("W: %d \ n", w _id );for (d _id =1; d_id <=DIST_PER_WARE; d _id ++){EXEC SQL WH EN EVER N OT FOUN D GOTO sqlerr;EXEC SQL DECLARE c_no CURSOR FORSELECT no_o_idFROM new _ord erWH ERE no_d_id = :d _id AN D no_w _id = :w _idORDER BY no_o_id ASC;EXEC SQL OPEN c_no;EXEC SQL WH EN EVER N OT FOUN D continue;EXEC SQL FETCH c_no IN TO :no_o_id ;EXEC SQL DELETE FROM new _ord er WH ERE CURREN T OF c_no;EXEC SQL CLOSE c_no;EXEC SQL SELECT o_c_id IN TO :c_id FROM ord ersWH ERE o_id = :no_o_id AN D o_d_id = :d _id AN Do_w _id = :w _id ;EXEC SQL UPDATE ord ers SET o_carrier_id = :o_carrier_idWH ERE o_id = :no_o_id AN D o_d_id = :d _id AN Do_w _id = :w _id ;EXEC SQL UPDATE ord er_line SET ol_d elivery_d = :datetim eWH ERE ol_o_id = :no_o_id AN D ol_d_id = :d _id AN Dol_w _id = :w _id;EXEC SQL SELECT SUM(ol_am ount) IN TO :ol_totalFROM ord er_lineWH ERE ol_o_id = :no_o_id AN D ol_d_id = :d _idAN D ol_w _id = :w _id;EXEC SQL UPDATE custom er SET c_balance = c_balance + :ol_totalWH ERE c_id = :c_id AN D c_d _id = :d_id AN Dc_w _id = :w _id ;EXEC SQL COMMIT WORK;printf("D: %d , O: %d , tim e: %d \ n", d_id , o_id , tad );}EXEC SQL COMMIT WORK;return(0);sqlerr:TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 114 of 130error();}TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 115 of 130A.5The Stock-Level Transactionint slev(){EXEC SQL WH EN EVER NOT FOUN D GOTO sqlerr;EXEC SQL WH EN EVER SQLERROR GOTO sqlerr;EXEC SQL SELECT d _next_o_id IN TO :o_idFROM d istrictWH ERE d _w _id =:w _id AN D d _id =:d _id ;EXEC SQL SELECT COUNT(DISTIN CT (s_i_id )) IN TO :stock_countFROM ord er_line, stockWH ERE ol_w _id =:w _id AN Dol_d _id =:d _id AN D ol_o_id <:o_id AN Dol_o_id >=:o_id -20 AN D s_w _id =:w _id AN Ds_i_id =ol_i_id AND s_quantity < :threshold;EXEC SQL COMMIT WORK;return(0);sqlerr:error();}TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 116 of 130A.6Sample Load Program/ *==================================================================+| Load TPCC tables+==================================================================*/#d efine MAXITEMS 100000#d efine CUST_PER_DIST 3000#d efine DIST_PER_WARE 10#d efine ORD_PER_DIST 3000extern long count_w are;/ * Functions */longvoidvoidvoidvoidvoidvoidvoidvoidvoidvoidvoidvoidvoidN URand ();Load Item s();Load Ware();Load Cust();Load Ord ();Load N ew Ord ();Stock();District();Custom er();Ord ers();N ew _Ord ers();MakeAd d ress();Error();Lastnam e();/ * Global SQL Variables */EXEC SQL BEGIN DECLARE SECTION ;chartim estam p [20];longcount_w are;EXEC SQL EN D DECLARE SECTION ;/ * Global Variables */inti;intoption_d ebug = 0;/ * 1 if generating debug output*// *================================================= =================+|main()| ARGUMEN TS|Warehouses n [Debug] [H elp]+==================================================================*/void m ain( argc, argv )intargc;char *argv[];{chararg[2];EXEC SQL WH EN EVER SQLERROR GOTO Error_SqlCall;count_w are=0;for (i=1; i<argc; i++){TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 117 of 130strncpy(arg,argv[i],2);arg[0] = toupper(arg[0]);sw itch (arg[0]) {case 'W': / * Warehouses */if (count_w are){printf("Error - Warehouses specified m ore than once.\ n");exit(-1);}if (argc-1>i){i++;count_w are=atoi(argv[i]);if (count_w are<=0){printf("Invalid Warehouse Count.\ n");exit(-1);}}else{printf("Error - Warehouse count m ust follow Warehouse keyw ord \ n");exit(-1);}break;/ ******* Generic Args *********************/case 'D': / * Debug Option */if (option_d ebug){printf("Error - Debug option specified m ore than once\ n");exit(-1);}option_d ebug=1;break;case 'H ': / * List Args */printf("Usage - Warehouses n [Debug] [H elp]\ n");exit(0);break;d efault : printf("Error - Unknow n Argum ent (%s)\ n",arg);printf("Usage - Warehouses n [Debug] [H elp]\ n");exit(-1);}}if (!(count_w are)) {printf("N ot enough argum ents.\ n");printf("Usage - Warehouses n ");printf(" [Debug] [H elp]\ n");exit(-1);}SetSeed ( tim e( 0 ) );/ * Initialize tim estam p (for d ate colum ns) */gettim estam p (tim estam p);printf( "TPCC Data Load Started ...\ n" );TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 118 of 130Load Item s();Load Ware();Load Cust();Load Ord ();EXEC SQL COMMIT WORK RELEASE;printf( "\ n...DATA LOADIN G COMPLETED SUCCESSFULLY.\ n" );exit( 0 );Error_SqlCall:Error();}/ *==================================================================+| ROUTIN E N AME|Load Item s| DESCRIPTION|Load s the Item table| ARGUMEN TS|none+==================================================================*/void Load Item s(){EXEC SQL BEGIN DECLARE SECTION ;long i_id ;char i_nam e[24];float i_price;char i_d ata[50];EXEC SQL EN D DECLARE SECTION ;int idatasiz;int orig[MAXITEMS];long pos;int i;EXEC SQL WH EN EVER SQLERROR GOTO sqlerr;printf("Load ing Item \ n");for (i=0; i<MAXITEMS/ 10; i++) orig[i]=0;for (i=0; i<MAXITEMS/ 10; i++){do{pos = Rand om N um ber(0L,MAXITEMS);} w hile (orig[pos]);orig[pos] = 1;}for (i_id =1; i_id <=MAXITEMS; i_id ++) {/ * Generate Item Data */MakeAlphaString( 14, 24, i_nam e);i_price=((float) Rand om N um ber(100L,10000L))/ 100.0;id atasiz=MakeAlphaString(26,50,i_data);if (orig[i_id ]){pos = Rand om N um ber(0L,id atasiz-8);i_d ata[pos]='o';i_d ata[pos+1]='r';i_d ata[pos+2]='i';i_d ata[pos+3]='g';i_d ata[pos+4]='i';i_d ata[pos+5]='n';i_d ata[pos+6]='a';i_d ata[pos+7]='l';TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 119 of 130}if ( option_d ebug )printf( "IID = %ld , N am e= %16s, Price = %5.2f\ n",i_id , i_nam e, i_price );EXEC SQL IN SERT IN TOitem (i_id , i_nam e, i_price, i_data)values (:i_id , :i_nam e, :i_price, :i_d ata);if ( !(i_id % 100) ) {printf(".");EXEC SQL COMMIT WORK;if ( !(i_id % 5000) ) printf(" %ld \ n",i_id );}}EXEC SQL COMMIT WORK;printf("Item Done.