Ticket #160: combineOnebyOne.groovy

File combineOnebyOne.groovy, 10.1 KB (added by jiangyichao, 12 years ago)

检查一对多合并脚本

Line 
1import groovy.sql.*
2import java.io.RandomAccessFile;
3
4//ÐÂœšÊýŸÝ¶ÔÏó
5def dburl = "jdbc:mysql://192.168.74.5:3310/bbs7_it_app";
6def ussername = "root";
7def password = "rootbbsmysql";
8def Driver = "com.mysql.jdbc.Driver";
9
10def Db = Sql.newInstance(dburl,ussername,password,Driver);
11
12//¶šÒå²ÎÊý
13def all_topics = 0;
14def old_topics = 0;
15def new_topics = 0;
16
17
18def fids = [[769094,769093],[769095,769093],[769096,769093],[769097,769093],[769098,769093],[769103,769044],[769104,769044],
19[769105,769044],[769109,768523],[769112,768523],[769110,768523],[769111,768523],[769099,768968],[769100,768968],[769101,768968],
20[769106,769085],[769107,769085],[769108,769085],[767653,679205],[768571,768570],[767974,768570],[767973,768570],[767753,749265],
21[709247,768581],[768146,768581],[768130,768581],[686412,768581],[676750,768581],[678447,768581],[686328,768581],[680066,768581],
22[768132,768581],[680064,768581],[716812,768581],[127918,768581],[768134,768581],[127931,768581],[767904,768581],[767913,768581],
23[750605,768581],[767916,768581],[679735,768581],[127902,768581],[127891,768581],[127895,768581],[678368,768581],
24[678449,4021],[127942,4021],[127908,4021],[127884,4021],[764971,4021],[127894,4022],[62529,4022],[764073,4022],
25[767754,667236],[768615,768614],[768616,768614],[767977,768614],[768147,768617],[768148,768617],[680180,768617],[736684,768617],
26[717781,768617],[128260,768617],[723891,768617],[680178,768617],[710102,768617],[128295,768617],[686873,768617],
27[678065,768617],[128294,768617],[199340,768618],[676799,768618],[189928,768618],[711074,768618],
28[196170,768618],[126508,768618],[683964,768618],[668645,768618],[765007,768618],[765009,768618],
29[713491,768619],[751041,768619],
30[768152,768619],
31[726452,768619],
32[767907,768619],
33[723572,768619],
34[764978,768619],
35[768446,680894],
36[767918,302866],
37[769553,768427],
38[768487,768427],
39[768683,768427],
40[768684,768427],
41[768685,768427],
42[768484,768427],
43[757247,768427],
44[768430,768427],
45[768678,768677],
46[768679,768677],
47[768680,768677],
48[768681,768677],
49[768783,768677],
50[769806,768431],
51[769753,768431],
52[769704,768431],
53[768203,768431],
54[767857,768431],
55[769966,768488],
56[769153,768488],
57[768489,768488],
58[768490,768488],
59[768491,768488],
60[127024,768606],
61[303843,768606],
62[127046,768606],
63[764976,768606],
64[764979,768606],
65[129933,768606],
66[767967,768606],
67[689283,768606],
68[677941,768606],
69[768574,768573],
70[768575,768573],
71[768576,768573],
72[767914,768573],
73[768558,768557],
74[768559,768557],
75[767978,768557],
76[768184,767855],
77[770154,300700],
78[770156,300700],
79[769497,300700],
80[769648,300700],
81[769649,300700],
82[769665,300700],
83[768633,300700],
84[767903,300700],
85[768997,300700],
86[682544,300700],
87[768719,300700],
88[769703,300700],
89[768503,300700],
90[768914,300700],
91[683338,300700],
92[768351,300700],
93[769586,301622],
94[770164,301622],
95[686443,301622],
96[768729,301622],
97[737642,301622],[769947,301622],[725829,301622],
98[715234,301622],[768730,301622],[769925,301582],[770003,301582],[769595,301582],[769674,301582],[769587,301582],[768515,301582],[768715,301582],[768283,301582],
99[768334,301582],[769013,301582],[768721,301582],[768716,301582],[768284,301582],[768332,301582],[768916,301582],[769585,300328],[769676,300328],[769813,300328],
100[769927,300328],[770103,300328],[687012,300328],[686995,300328],[768743,300328],[768996,300328],[768357,300328],[143388,300328],[768744,300328],[769594,300335],[769666,300335],
101[769926,300335],[770013,300335],[770163,300335],[753692,300335],[732912,300335],[768338,300335],[768339,300335],[768727,300335],[769064,300335],[768728,300335],
102[769833,300331],[767784,300331],[757278,300331],[697290,300331],[768731,300331],[768732,300331],[768733,300331],[769657,300327],[769658,300327],[769693,300327],
103[726365,300327],[768734,300327],[768735,300327],[769677,301209],[146836,301209],[764922,301209],[769948,300330],[769675,300330],[768725,300330],[730062,300330],
104[735919,300330],[730008,300330],[768726,300330],[769667,302226],[769656,302226],[770023,302226],[768174,302226],[768763,302226],[768437,302226],[768436,302226],
105[683853,302226],[768722,302226],[768723,302226],[768724,302226],[769707,300759],[769724,300759],[769725,300759],[769726,300759],[770165,769306],
106[769247,769306],[769288,769364],[769257,769305],[769291,769314],[769290,769315],[769289,769354],[769258,769355],[769248,769363],[769304,769316],[769303,769356],
107[767963,762371],[768463,762371],[767764,253],[592351,300235],[762377,300235],[240141,768911],[768021,768024],[768005,768004],[769435,768384],[768035,768384],[768036,768384],
108[769923,768384],
109[768089,768090],
110[769455,768118],
111[769467,768118],
112[770161,768118],
113[768903,768118],
114[769915,4034],
115[770083,4034],
116[770159,4034],
117[770160,4034],
118[769473,4034],
119[768543,4034],
120[767840,4034],
121[767841,4034],
122[768301,4034],
123[767995,4034],
124[767996,4034],
125[767997,4034],
126[767998,4034],
127[767999,4034],
128[768000,4034],
129[768001,4034],
130[768002,4034],
131[768003,4034],
132[767994,4034],
133[767993,4034],
134[767983,4034],
135[767985,4034],
136[767986,4034],
137[767987,4034],
138[767988,4034],
139[767989,4034],
140[767990,4034],
141[767991,4034],
142[767992,4034],
143[768628,768908],
144[769565,768908],
145[768009,768908],
146[768909,768019],
147[769946,768019],
148[768023,768019],
149[768406,768019],
150[768063,4033],
151[768071,4033],
152[768070,4033],
153[768069,4033],
154[768068,4033],
155[768067,4033],
156[768066,4033],[768065,4033],[768064,4033],[769683,4033],[769744,4033],
157[769466,4039],[768042,4039],[768052,4039],[768051,4039],[768050,4039],[768049,4039],[768048,4039],[768047,4039],[768046,4039],
158[768045,4039],[768044,4039],[768043,4039],[769454,4036],[769924,4036],[768906,4036],[768907,4036],[768055,4036],[768057,4036],
159[768058,4036],[768060,4036],[768062,4036],[768631,4036],[768630,4036],[768629,4036],[768905,4036],[768025,4038],[768026,4038],[768029,4038],
160[768030,4038],[768032,4038],[768038,4038],[768040,4038],[768041,4038],[302431,4038],[678856,4040],[302433,4040],[768079,4040],
161[768080,4040],[768081,4040],[768082,4040],[768083,4040],[768073,4037],[768075,4037],[768077,4037],[768078,4037],[305005,300270],[305021,300270],
162[305010,300270],[305006,300270],[305008,300270],[305007,300270],[305009,300270],[305012,300270],[305013,300270],[305018,300270],
163[305014,300270],[305017,300270],[305016,300270],[305015,300270],[305019,300270],[595477,300270],[4042,300270],
164[305040,300269],[305027,300269],[305037,300269],[4043,300269],[305066,300254],[305062,300254],[305079,300254],[305061,300254],
165[305055,300254],[681302,300254],[4044,300254],[305586,300285],[305307,300285],[678905,300285],[690861,300285],[305305,300285],
166[678904,300285],[4045,300285],[767757,300191],[303883,300191],[304708,300191],[596424,300191],[304703,300191],[304665,300191],
167[594910,300191],[304302,300191],[677166,300191],[728474,300191],[378387,300191],[304747,300191],[304692,300191],[693024,300191],[676960,300191],[678795,300191],
168[369155,300191],[305584,300191],[304767,300191],[664239,300191],[304284,300191],[748308,300191],[681561,300191],[303793,300191],[123,300191],[4041,300191]];
169
170def checkAndGetAllTopics(List fids, def Db){
171   
172    //°æ¿é¶ÔÓŠÔöŒÓµÄÌû×Ó
173    def resultMap = [:];
174    (0..(fids.size() - 1)).each{i ->
175        def source = fids.get(i).get(0);
176        def result = fids.get(i).get(1);
177        def index = 0;
178        def old_topics = 0;
179        def new_topics = 0;
180        Db.eachRow(" SELECT (CASE WHEN COUNT(*) = 0 THEN 0 ELSE TABLEINDEX END) AS TABLEINDEX FROM BBS7_FORUM_INDEX WHERE FID = " + source ){
181            index = it.tableindex;
182        };
183        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + "_BAK WHERE (FID = " + source + " OR SUBFORUMID = " + source + " ) AND FID <> " + result){
184            old_topics = it.num;
185        };
186        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + " WHERE FID = " + source + " OR SUBFORUMID = " + source){
187            new_topicsics = it.num;
188        };
189        if(new_topics != 0){
190            println "Forum " + source + " has topics " + new_topics + " is error...";   
191        }else{
192           // println "Forum " + source + " has topics " + new_topics + " is Ok !";   
193        }
194        if(resultMap.get(result) == null){
195            resultMap.put(result, old_topics);
196        }else{
197            resultMap.put(result, resultMap.get(result) + old_topics);
198        }
199    }
200    resultMap.each{resultFid,num->
201        def index = 0;
202        def oldTopic1Num = 0;
203        def newTopic1Num = 0;
204        Db.eachRow(" SELECT (CASE WHEN COUNT(*) = 0 THEN 0 ELSE TABLEINDEX END) AS TABLEINDEX FROM BBS7_FORUM_INDEX WHERE FID = " + resultFid ){
205            index = it.tableindex;
206        };
207        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + "_BAK WHERE FID = " + resultFid + " OR SUBFORUMID = " + resultFid){
208            oldTopic1Num = it.num;
209        };
210        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + " WHERE FID = " + resultFid + " OR SUBFORUMID = " + resultFid){
211            newTopic1Num = it.num;
212        };
213       
214        if( num == (newTopic1Num - oldTopic1Num )){
215            println "Result forum : " +resultFid +";should add topics : " +num+ " and has added topics : "+ (newTopic1Num - oldTopic1Num ) +"; Table bbs7_topic_" + index + " is OK!"
216        }else{
217            println "Result forum : " +resultFid +";should add topics : " +num+ " but has added topics : "+ (newTopic1Num - oldTopic1Num ) +"; Table bbs7_topic_" + index + " is error!"
218        }
219   
220    };
221
222   
223}
224
225def tempArray1 = [];
226def tempArray2 = [];
227def arrayCount = 0;
228
229(0..(fids.size() - 1)).each{i ->
230   
231    //µÚÒ»²ã×Ó°æ
232    Db.eachRow(" SELECT FID FROM BBS7_FORUM_BAK WHERE PARENTID = " + fids.get(i).get(0)){row ->
233        tempArray1.add([row.fid, fids.get(i).get(1)]);
234    };
235
236}
237//µÚ¶þ²ã×Ó°æ
238   
239if( tempArray1.size() != 0){
240    (0..(tempArray1.size() - 1)).each{j ->
241        Db.eachRow(" SELECT FID FROM BBS7_FORUM_BAK WHERE PARENTID = " + tempArray1.get(j).get(0)){ row ->
242            tempArray2.add([row.fid,  tempArray1.get(j).get(1)]);
243        };
244    };
245}
246
247
248fids.plus(tempArray1);
249fids.plus(tempArray2);
250
251checkAndGetAllTopics(fids, Db);