Ticket #160: combineOnebyMany.groovy

File combineOnebyMany.groovy, 7.7 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 = [[769424,2312237],
19[769404,2312237],
20[769394,2312237],
21[769402,2312237],
22[769575,2312237],
23[769836,2312237],
24[769116,2312237],
25[769163,2312237],
26[769193,2312237],
27[768833,2312237],
28[768686,2312237],
29[768703,2312237],
30[768970,2312237],
31[768418,2312237],
32[768969,2312237],
33[768417,2312237],
34[768416,2312237],
35[768415,2312237],
36[767933,2312237],
37[767920,2312237],
38[767663,2312237],
39[751839,2312237],
40[733432,2312237],
41[711692,2312237],
42[691172,2312237],
43[769253,2312229],
44[768968,2312229],
45[752279,2312229],
46[768485,2312229],
47[769118,2312270],
48[769117,2312270],
49[769120,2312270],
50[769119,2312270],
51[768486,2312270],
52[768682,2312270],
53[126534,2312270],
54[768150,2312270],
55[126532,2312270],
56[768422,2312270],
57[768424,2312270],
58[769397,2312230],
59[769043,2312230],
60[769216,2312230],
61[768714,2312230],
62[769431,2312230],
63[769563,2312230],
64[769430,2312230],
65[768138,2312230],
66[768754,2312230],
67[768675,2312230],
68[769124,2312230],
69[768674,2312230],
70[768694,2312230],
71[768693,2312230],
72[768673,2312230],
73[767964,2312230],
74[768423,2312230],
75[768426,2312230],
76[767965,2312230],
77[769954,2312230],
78[769953,2312230],
79[770143,2312271],
80[769224,2312271],
81[769395,2312271],
82[769425,2312271],
83[769596,2312271],
84[769807,2312271],
85[769808,2312271],
86[769840,2312271],
87[769841,2312271],
88[770073,2312272],
89[769464,2312272],
90[769814,2312272],
91[769396,2312272],
92[769243,2312272],
93[768999,2312272],
94[768428,2312272],
95[768432,2312272],
96[768433,2312272],
97[768434,2312272],
98[769983,2312250],
99[770096,2312250],
100[769603,2312250],
101[769214,2312250],
102[768924,2312250],
103[770170,2312238],
104[769463,2312238],
105[770104,2312273],
106[769365,2312273],
107[768438,2312251],
108[768971,2312251],
109[769964,2312240],
110[770033,2312240],
111[769453,2312240],
112[769422,2312240],
113[768442,2312240],
114[769805,2312242],
115[769597,2312242],
116[768440,2312261],
117[769434,2312261],
118[768563,2312252],
119[768562,2312252],
120[768561,2312252],
121[719833,2312253],
122[768153,2312253],
123[693751,2312253],
124[708517,2312253],
125[738914,2312253],
126[750601,2312253],
127[767905,2312253],
128[767915,2312253],
129[768578,2312253],
130[769265,2312262],
131[769429,2312262],
132[768151,2312262],
133[764814,2312275],
134[753264,2312275],
135[680918,2312275],
136[768738,2312233],
137[769456,2312233],
138[764923,2312276],
139[768736,2312276],
140[677147,2312276],
141[768296,2312276],
142[300332,2312276],
143[721447,2312254],
144[768314,2312254],
145[769950,2312239],
146[770173,2312239],
147[769949,2312232],
148[770157,2312232],
149[770093,2312274],
150[770166,2312274],
151[768966,2312231],
152[768933,2312231],
153[768934,2312241],
154[768973,2312241],
155[768718,2312249],
156[762427,2312260],
157[762428,2312260],
158[768027,2312259],
159[768028,2312259],
160[768061,2312269],
161[768056,2312269],
162[768059,2312269],
163[769584,2312269],
164[769863,2312269],
165[768037,2312269],
166[768053,2312269],
167[768054,2312269],
168[768072,2312269],
169[768383,2312269],
170[769914,2312269],
171[768403,2312269],
172[768076,2312269],
173[768074,2312269],
174[768084,2312269],
175[768033,2312269],
176[768031,2312269],
177[768034,2312269],
178[769943,2312268],
179[768107,2312268],
180[768106,2312268],
181[768103,2312268],
182[768105,2312268],
183[768102,2312268],
184[768104,2312268],
185[768101,2312268],
186[768100,2312268],
187[768099,2312268],
188[768404,2312268],
189[769944,2312268],
190[768626,2312268],
191[768625,2312268],
192[768098,2312268],
193[768096,2312268],
194[768097,2312268],
195[768095,2312268],
196[768114,2312228],
197[768113,2312228],
198[768300,2312228],
199[768299,2312228],
200[768112,2312228],
201[768111,2312228],
202[768110,2312228],
203[768109,2312228],
204[768108,2312228],
205[768627,2312267],
206[768998,2312267],
207[770175,2312267],
208[769574,2312267],
209[768297,2312267],
210[768904,2312267],
211[768298,2312267],
212[769945,2312258],
213[769706,2312258],
214[768121,2312257],
215[768120,2312257],
216[768119,2312257],
217[768122,2312257],
218[768123,2312257],
219[768124,2312257],
220[768125,2312257],
221[768126,2312257],
222[768127,2312257],
223[768128,2312257],
224[768115,2312257],
225[768116,2312257],
226[768117,2312257],
227[768014,2312248],
228[768018,2312248],
229[768007,2312248],
230[768407,2312248],
231[768408,2312248],
232[768020,2312248],
233[768016,2312248],
234[768017,2312248],
235[768015,2312248],
236[768008,2312248],
237[768012,2312248],
238[768011,2312248],
239[768797,2312247],
240[768796,2312247]];
241
242def checkAndGetAllTopics(List fids, def Db){
243   
244    //°æ¿é¶ÔÓŠÔöŒÓµÄÌû×Ó
245    def resultMap = [:];
246    (0..(fids.size() - 1)).each{i ->
247        def source = fids.get(i).get(0);
248        def result = fids.get(i).get(1);
249        def index = 0;
250        def old_topics = 0;
251        def new_topics = 0;
252        Db.eachRow(" SELECT (CASE WHEN COUNT(*) = 0 THEN 0 ELSE TABLEINDEX END) AS TABLEINDEX FROM BBS7_FORUM_INDEX WHERE FID = " + source ){
253            index = it.tableindex;
254        };
255        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + "_BAK WHERE (FID = " + source + " OR SUBFORUMID = " + source + " ) AND FID <> " + result){
256            old_topics = it.num;
257        };
258        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + " WHERE FID = " + source + " OR SUBFORUMID = " + source){
259            new_topicsics = it.num;
260        };
261        if(new_topics != 0){
262            println "Forum " + source + " has topics " + new_topics + " is error...";   
263        }else{
264           // println "Forum " + source + " has topics " + new_topics + " is Ok !";   
265        }
266        if(resultMap.get(result) == null){
267            resultMap.put(result, old_topics);
268        }else{
269            resultMap.put(result, resultMap.get(result) + old_topics);
270        }
271    }
272    resultMap.each{resultFid,num->
273        def index = 0;
274        def oldTopic1Num = 0;
275        def newTopic1Num = 0;
276        Db.eachRow(" SELECT (CASE WHEN COUNT(*) = 0 THEN 0 ELSE TABLEINDEX END) AS TABLEINDEX FROM BBS7_FORUM_INDEX WHERE FID = " + resultFid ){
277            index = it.tableindex;
278        };
279        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + "_BAK WHERE FID = " + resultFid + " OR SUBFORUMID = " + resultFid){
280            oldTopic1Num = it.num;
281        };
282        Db.eachRow(" SELECT COUNT(*) AS NUM FROM BBS7_TOPIC" + (index == 0 ? "" : "_" + index) + " WHERE FID = " + resultFid + " OR SUBFORUMID = " + resultFid){
283            newTopic1Num = it.num;
284        };
285       
286        if( num == (newTopic1Num - oldTopic1Num )){
287            println "Result forum : " +resultFid +";should add topics : " +num+ " and has added topics : "+ (newTopic1Num - oldTopic1Num ) +"; Table bbs7_topic_" + index + " is OK!"
288        }else{
289            println "Result forum : " +resultFid +";should add topics : " +num+ " but has added topics : "+ (newTopic1Num - oldTopic1Num ) +"; Table bbs7_topic_" + index + " is error!"
290        }
291   
292    };
293
294   
295}
296
297def tempArray1 = [];
298def tempArray2 = [];
299def arrayCount = 0;
300
301(0..(fids.size() - 1)).each{i ->
302   
303    //µÚÒ»²ã×Ó°æ
304    Db.eachRow(" SELECT FID FROM BBS7_FORUM_BAK WHERE PARENTID = " + fids.get(i).get(0)){row ->
305        tempArray1.add([row.fid, fids.get(i).get(1)]);
306    };
307
308}
309//µÚ¶þ²ã×Ó°æ
310   
311if( tempArray1.size() != 0){
312    (0..(tempArray1.size() - 1)).each{j ->
313        Db.eachRow(" SELECT FID FROM BBS7_FORUM_BAK WHERE PARENTID = " + tempArray1.get(j).get(0)){ row ->
314            tempArray2.add([row.fid,  tempArray1.get(j).get(1)]);
315        };
316    };
317}
318
319
320fids.plus(tempArray1);
321fids.plus(tempArray2);
322
323checkAndGetAllTopics(fids, Db);