]>
Commit | Line | Data |
---|---|---|
0dc8c584 | 1 | #!/usr/bin/perl -w |
2 | ||
3 | use DBI; | |
4 | use strict; | |
5 | ||
6 | my $pathname="/home/q9test/database/"; | |
7 | ||
8 | my %chans; | |
9 | my %users; | |
10 | my %chanusers; | |
11 | ||
12 | my %suspends; | |
13 | ||
14 | my $chanid=1; | |
15 | my $chanusercount=0; | |
16 | ||
17 | $|=1; | |
18 | ||
19 | open BANS, ">bans.csv"; | |
20 | open CHANS, ">chans-000.csv"; | |
21 | open USERS, ">users-000.csv"; | |
22 | open CHANUSERS, ">chanusers-000.csv"; | |
23 | open ORPHANS, ">orphanlist"; | |
24 | open SQL, ">load.sql"; | |
25 | ||
26 | print SQL "TRUNCATE TABLE channels;\n"; | |
27 | print SQL "TRUNCATE TABLE users;\n"; | |
28 | print SQL "TRUNCATE TABLE bans;\n"; | |
29 | print SQL "TRUNCATE TABLE chanusers;\n"; | |
30 | print SQL "COPY users FROM '${pathname}users-000.csv' DELIMITER ',';\n"; | |
31 | print SQL "COPY channels FROM '${pathname}chans-000.csv' DELIMITER ',';\n"; | |
32 | print SQL "COPY chanusers FROM '${pathname}chanusers-000.csv' DELIMITER ',';\n"; | |
33 | print SQL "COPY bans FROM '${pathname}bans.csv' DELIMITER ',';\n"; | |
34 | ||
35 | print "Converting Q channels.."; | |
36 | loadchans(); | |
37 | print "\nConverting Q users.."; | |
38 | loadsuspendusers(); | |
39 | loadusers(); | |
40 | print "\nConverting L channels.."; | |
41 | loadldb(); | |
42 | ||
43 | sub loadsuspendusers { | |
44 | open SUSPLIST, "suspended_users" or die "Unable to open suspended users list"; | |
45 | ||
46 | my $state=0; | |
47 | my $name; | |
48 | my $type; | |
49 | my $who; | |
50 | my $stime; | |
51 | my $exp; | |
52 | my $reason; | |
53 | my $unl_retries; | |
54 | my $unl_password; | |
55 | ||
56 | while (<SUSPLIST>) { | |
57 | chomp; | |
58 | ||
59 | if ($state==0) { | |
60 | $name=$_; | |
61 | $state++; | |
62 | } elsif ($state==1) { | |
63 | $type=$_; | |
64 | $state++; | |
65 | } elsif ($state==2) { | |
66 | $who=$_; | |
67 | $state++; | |
68 | } elsif ($state==3) { | |
69 | $stime=$_; | |
70 | $state++; | |
71 | } elsif ($state==4) { | |
72 | $exp=$_; | |
73 | $state++; | |
74 | } elsif ($state==5) { | |
75 | if ($_ eq "!") { | |
76 | $reason=""; | |
77 | } else { | |
78 | $reason=$_; | |
79 | } | |
80 | $state++; | |
81 | } elsif ($state==6) { | |
82 | $state++; | |
83 | } elsif ($state==7) { | |
84 | $state++; | |
85 | } elsif ($state==8) { | |
86 | if ($_ ne "end") { | |
87 | die "suspended_users format errar!"; | |
88 | } | |
89 | $state=0; | |
90 | $suspends{$name} = [ $type, $who, $stime, $exp, $reason ]; | |
91 | } | |
92 | } | |
93 | ||
94 | close SUSPLIST; | |
95 | } | |
96 | ||
97 | ||
98 | sub loadchans { | |
99 | open CHANLIST, "channels" or die "Unable to open channel list"; | |
100 | ||
101 | my $state=0; | |
102 | my $cname; | |
103 | my $flags; | |
104 | my $realflags; | |
105 | my $owner; | |
106 | my $addby; | |
107 | my $suspended; | |
108 | my $suspendby; | |
109 | my $suspendtime; | |
110 | my $suspenduntil; | |
111 | my $suspendreason; | |
112 | my $type; | |
113 | my $topic; | |
114 | my $lasttopic; | |
115 | my $welcome; | |
116 | my $key; | |
117 | my $limit; | |
118 | my $firstjoin; | |
119 | my $lastjoin; | |
120 | my $joincount; | |
121 | my $banstr; | |
122 | my $banby; | |
123 | my $forcemodes; | |
124 | ||
125 | my $banid=1; | |
126 | ||
127 | while(<CHANLIST>) { | |
128 | chomp; | |
129 | if ($state==0) { | |
130 | $cname=$_; | |
131 | } elsif ($state==1) { | |
132 | $flags=$_; | |
133 | } elsif ($state==2) { | |
134 | $owner=$_; | |
135 | } elsif ($state==3) { | |
136 | $addby=$_; | |
137 | } elsif ($state==4) { | |
138 | $suspended=$_; | |
139 | } elsif ($state==5) { | |
140 | $suspendby=$_; | |
141 | } elsif ($state==6) { | |
142 | $suspendtime=$_; | |
143 | } elsif ($state==7) { | |
144 | $suspenduntil=$_; | |
145 | } elsif ($state==8) { | |
146 | $suspendreason=$_; | |
147 | if ($suspendreason eq "!") { $suspendreason = ""; } | |
148 | } elsif ($state==9) { | |
149 | $type=$_; | |
150 | } elsif ($state==10) { | |
151 | $topic=$_; | |
152 | } elsif ($state==11) { | |
153 | $lasttopic=$_; | |
154 | if ($lasttopic eq "!") { $lasttopic = ""; } | |
155 | } elsif ($state==12) { | |
156 | $welcome=$_; | |
157 | if ($welcome eq "!") { $welcome = ""; } | |
158 | } elsif ($state==13) { | |
159 | $key=$_; | |
160 | if ($key eq "!") { $key=""; } | |
161 | if (length $key > 23) { $key=""; } | |
162 | } elsif ($state==14) { | |
163 | $limit=$_; | |
164 | unless($limit =~ /^\d+$/) { $limit=0; } | |
165 | if ($limit > 9999) { $limit = 9999; } | |
166 | } elsif ($state==15) { | |
167 | $firstjoin=$_; | |
168 | } elsif ($state==16) { | |
169 | $lastjoin=$_; | |
170 | } elsif ($state==17) { | |
171 | $joincount=$_; | |
172 | } elsif ($state==18) { | |
173 | $banstr=$_; | |
174 | } elsif ($state==19) { | |
175 | $banby=$_; | |
176 | ||
177 | if ($banstr ne "!") { | |
178 | print BANS "$banid,$chanid,$banby,".doquote($banstr).",0,\n"; | |
179 | $banid++; | |
180 | $state=17; | |
181 | if (!($banid % 1000)) { | |
182 | close BANS; | |
183 | my $fname=sprintf("bans-%03d.csv",$banid/1000); | |
184 | open BANS, ">$fname"; | |
185 | print SQL "COPY bans FROM '${pathname}${fname}' DELIMITER ',';\n"; | |
186 | } | |
187 | } else { | |
188 | ($forcemodes, $realflags)=map_chanflags($flags); | |
189 | if ($suspended) { | |
190 | $realflags |= 0x4000; | |
191 | } | |
192 | $welcome = doquote($welcome); | |
193 | $lasttopic = doquote($lasttopic); | |
194 | print CHANS "$chanid,".doquote($cname).",$realflags,$forcemodes,0,$limit,10,0,$firstjoin,$lastjoin,$firstjoin,0,$owner,$addby,$suspendby,$suspendtime,$type,$joincount,$joincount,0,0,$welcome,$lasttopic,".doquote($key).",".doquote($suspendreason).",,0\n"; | |
195 | if (($chanid % 1000)==0) { | |
196 | close CHANS; | |
197 | my $fname=sprintf("chans-%03d.csv",$chanid/1000); | |
198 | open CHANS, ">$fname"; | |
199 | print SQL "COPY channels FROM '${pathname}${fname}' DELIMITER ',';\n"; | |
200 | } | |
201 | $chans{irc_lc($cname)}=$chanid; | |
202 | $state=-1; | |
203 | unless ($chanid % 1000) { | |
204 | print "."; | |
205 | } | |
206 | $chanid++; | |
207 | } | |
208 | } | |
209 | $state++; | |
210 | } | |
211 | ||
212 | close CHANLIST; | |
213 | } | |
214 | ||
215 | sub map_chanflags { | |
216 | my ($oldflags) = @_; | |
217 | ||
218 | my $newflags=0x0080; | |
219 | my $forcemodes=0x3; | |
220 | ||
221 | if ($oldflags & 0x02) { # +b | |
222 | $newflags |= 0x02; | |
223 | } | |
224 | ||
225 | if ($oldflags & 0x04) { # +c | |
226 | $newflags |= 0x04; | |
7bfa850c | 227 | $forcemodes |= 0x20; |
0dc8c584 | 228 | } |
229 | ||
230 | if ($oldflags & 0x20) { # +f | |
231 | $newflags |= 0x10; | |
232 | } | |
233 | ||
234 | if ($oldflags & 0x8000) { # +p | |
235 | $newflags |= 0x200; | |
236 | } | |
237 | ||
238 | if ($oldflags & 0x80000) { # +t | |
239 | $newflags |= 0x800; | |
240 | } | |
241 | ||
242 | if ($oldflags & 0x400000) { # +w | |
243 | $newflags |= 0x2000; | |
244 | } | |
245 | ||
246 | if ($oldflags & 0x400) { # +k: maps to forcemode +k | |
247 | $forcemodes |= 0x40; | |
248 | } | |
249 | ||
250 | if ($oldflags & 0x800) { # +l: maps to forcemode +l | |
251 | $forcemodes |= 0x20; | |
252 | } | |
253 | ||
254 | return ($forcemodes, $newflags); | |
255 | } | |
256 | ||
257 | sub loadusers { | |
258 | open USERLIST, "users" or die "Unable to open user list"; | |
259 | ||
260 | my $username; | |
261 | my $userid; | |
262 | my $password; | |
263 | my $globalauth; | |
264 | my $lastauth; | |
265 | my $lastyxx; | |
266 | my $lastusername; | |
267 | my $lasthostname; | |
268 | my $lastemail; | |
269 | my $emailaddr; | |
270 | my $chan; | |
271 | my $flags; | |
272 | my $uflags; | |
273 | my $realflags; | |
274 | my $usercount=0; | |
275 | my $lastemailrq; | |
276 | my $lockuntil; | |
277 | my $nuflags; | |
278 | my $state=0; | |
279 | my $suspendwho=0; | |
280 | my $suspendtime=0; | |
281 | my $suspendexp=0; | |
282 | my $suspendreason=""; | |
7d27d0cd | 283 | my $created=0; |
0dc8c584 | 284 | |
285 | while (<USERLIST>) { | |
286 | chomp; | |
287 | ||
288 | if ($state == 0) { | |
289 | $username=$_; | |
290 | } elsif ($state == 1) { | |
291 | $userid=$_; | |
292 | } elsif ($state == 2) { | |
293 | $password=$_; | |
294 | } elsif ($state == 3) { | |
295 | $globalauth=$_; | |
296 | } elsif ($state == 4) { | |
297 | $lastauth=$_; | |
298 | } elsif ($state == 5) { | |
299 | $lastyxx=$_; | |
300 | } elsif ($state == 6) { | |
301 | $lastusername=$_; | |
302 | } elsif ($state == 7) { | |
303 | $lasthostname=$_; | |
304 | } elsif ($state == 8) { | |
305 | $lastemailrq=$_; | |
306 | } elsif ($state == 9) { | |
307 | $emailaddr=$_; | |
308 | } elsif ($state == 10) { | |
309 | $lockuntil=$_; | |
310 | } elsif ($state == 11) { | |
311 | $lastemail=$_; | |
312 | } elsif ($state == 12) { | |
313 | $nuflags=$_; | |
314 | } elsif ($state == 13) { | |
315 | if ($_ eq "end") { | |
316 | # print "Got user $username [$userid]\n"; | |
317 | $uflags=4; | |
44486043 | 318 | # 20-900 get +h, 900+ get +o |
0dc8c584 | 319 | if ($globalauth >= 900) { |
320 | $uflags |= 0x20; | |
321 | } | |
44486043 | 322 | if ($globalauth >=20 and $globalauth < 900) { |
0dc8c584 | 323 | $uflags |= 0x100; |
324 | } | |
a58d002b | 325 | # 997-999 get +a, 1000 gets +d |
326 | if ($globalauth >= 997 and $globalauth < 1000) { | |
327 | $uflags |= 0x200; | |
328 | } | |
0dc8c584 | 329 | if ($globalauth >= 1000) { |
330 | $uflags |= 0x40; | |
331 | } | |
332 | if ($nuflags & 8) { # No auth limit | |
333 | $uflags |= 0x1000; | |
334 | } | |
335 | if ($nuflags & 64) { # No delete | |
336 | $uflags |= 0x4000; | |
337 | } | |
338 | if ($nuflags & 1) { # Has trust | |
339 | $uflags |= 0x8000; | |
340 | } | |
341 | if (exists $suspends{$username}) { | |
342 | my $ar=$suspends{$username}; | |
7d27d0cd | 343 | $created=0; # Q doesn't have created time for auths so default it to 0 |
344 | if ($$ar[0] == 7) { # never authed - copy the "last auth" time into "created time". | |
345 | $created=$lastauth; | |
346 | $lastauth=0; # new Q uses lastauth=0 to indicate an unused account | |
0dc8c584 | 347 | } elsif ($$ar[0] == 5) { # delayed gline |
348 | $uflags |= 0x0800; | |
349 | } elsif ($$ar[0] == 6) { # instant gline | |
350 | $uflags |= 0x2; | |
351 | } elsif ($$ar[0] == 1) { # public | |
352 | $uflags |= 0x10; | |
353 | } else { | |
354 | print "Suspend reason $$ar[0] found!\n"; | |
355 | $uflags |= 0x10; | |
356 | } | |
357 | $suspendwho=$$ar[1]; | |
358 | $suspendtime=$$ar[2]; | |
359 | $suspendexp=$$ar[3]; | |
360 | $suspendreason=doquote($$ar[4]); | |
361 | } else { | |
362 | $suspendwho=0; | |
363 | $suspendtime=0; | |
364 | $suspendexp=0; | |
365 | $suspendreason=""; | |
7d27d0cd | 366 | $created=0; |
0dc8c584 | 367 | } |
7d27d0cd | 368 | print USERS "$userid,".doquote($username).",$created,$lastauth,$lastemailrq,$uflags,0,$suspendwho,$suspendexp,$suspendtime,$lockuntil,".doquote($password).",".doquote($emailaddr).",".doquote($lastemail).",".doquote("${lastusername}\@${lasthostname}").",".$suspendreason.",,\n"; |
0dc8c584 | 369 | $users{irc_lc($username)}=$userid; |
370 | $state=-1; | |
371 | $usercount++; | |
372 | unless ($usercount % 1000) { | |
373 | print "."; | |
374 | close USERS; | |
375 | my $fname=sprintf("users-%03d.csv",$usercount/1000); | |
376 | open USERS, ">$fname"; | |
377 | print SQL "COPY users FROM '${pathname}${fname}' DELIMITER ',';\n"; | |
378 | } | |
379 | } else { | |
380 | if (not defined $chans{irc_lc($_)}) { | |
381 | print "Error: unknown channel $_\n"; | |
382 | $chan=0; | |
383 | } else { | |
384 | $chan=$chans{irc_lc($_)}; | |
385 | } | |
386 | } | |
387 | } elsif ($state == 14) { | |
388 | if ($chan>0) { | |
389 | $flags=$_; | |
390 | $realflags=map_chanlevflags($flags); | |
391 | my $idstr=$userid.".".$chan; | |
392 | unless (exists $chanusers{$idstr}) { | |
393 | print CHANUSERS "$userid,$chan,$realflags,0,0,\n"; | |
394 | $chanusercount++; | |
395 | if (!($chanusercount % 10000)) { | |
396 | close CHANUSERS; | |
397 | my $fname=sprintf("chanusers-%03d.csv",$chanusercount/10000); | |
398 | open CHANUSERS, ">$fname"; | |
399 | print SQL "COPY chanusers FROM '${pathname}${fname}' DELIMITER ',';\n"; | |
400 | } | |
401 | $chanusers{$idstr}=1; | |
402 | } | |
403 | } | |
404 | $state=12; | |
405 | } | |
406 | ||
407 | $state++; | |
408 | } | |
409 | ||
410 | close USERLIST; | |
411 | } | |
412 | ||
413 | sub map_chanlevflags { | |
414 | my ($oldflags) = @_; | |
415 | ||
416 | my $newflags=0; | |
417 | ||
418 | if ($oldflags & 0x1) { # +a | |
419 | $newflags |= 0x9; | |
420 | } | |
421 | ||
422 | if ($oldflags & 0x2) { # +b | |
423 | $newflags |= 0x2; | |
424 | } | |
425 | ||
426 | if ($oldflags & 0x1000) { # +m | |
427 | $newflags |= 0x4000; | |
428 | } | |
429 | ||
430 | if ($oldflags & 0x2000) { # +n | |
431 | $newflags |= 0x8000; | |
432 | } | |
433 | ||
434 | if ($oldflags & 0x4000) { # +o | |
435 | $newflags |= 0x2000; | |
436 | } | |
437 | ||
438 | if ($oldflags & 0x200000) { # +v | |
439 | $newflags |= 0x1000; | |
440 | } | |
441 | ||
442 | if ($oldflags & 0x80000) { # +t | |
443 | $newflags |= 0x40; | |
444 | } | |
445 | ||
446 | if ($newflags & 0x2000) { | |
447 | $newflags &= ~0x8; | |
448 | } elsif ($newflags & 0x1000) { | |
449 | $newflags &= ~0x1; | |
450 | } else { | |
451 | $newflags &= ~0x9; | |
452 | } | |
453 | ||
454 | return $newflags; | |
455 | } | |
456 | ||
457 | sub loadldb { | |
458 | open LDB, "accounts.0" or die "Error opening lightweight database"; | |
459 | ||
460 | my $state=0; | |
461 | ||
462 | my $channame; | |
463 | my $addedby; | |
464 | my $founder; | |
465 | my $lastused; | |
466 | my $added; | |
467 | my $flags; | |
468 | my $cuflags; | |
469 | my $userid; | |
470 | my $suspendby; | |
471 | my $suspendreason; | |
472 | my $cflags; | |
473 | my $welcome; | |
474 | my $curchanid; | |
475 | my $skipadd; | |
476 | my $forcemodes; | |
477 | ||
478 | while (<LDB>) { | |
479 | chomp; | |
480 | ||
481 | if ($state == 0) { | |
482 | /^--- End of/ && ($state=1); | |
483 | } elsif ($state==1) { | |
484 | /^(.+?) (.+?) (.+?) (\d+) (\d+) (\d+)$/ or next; | |
485 | $channame=$1; | |
486 | $addedby=getuserid($2); | |
487 | $founder=getuserid($3); | |
488 | $lastused=$4; | |
489 | $added=$5; | |
490 | $flags=$6; | |
491 | $suspendby=0; | |
492 | $suspendreason=""; | |
493 | $welcome=""; | |
494 | $forcemodes=0x3; | |
495 | $skipadd=0; | |
496 | ||
497 | if (defined $chans{irc_lc($channame)}) { | |
498 | $curchanid=$chans{irc_lc($channame)}; | |
499 | print "Duplicate channel $channame, merging chanlev\n"; | |
500 | $skipadd=1; | |
501 | } else { | |
502 | unless ($chanid % 1000) { | |
503 | print "."; | |
504 | } | |
505 | $curchanid=$chanid; | |
506 | $chanid++; | |
507 | } | |
508 | ||
509 | $cflags=0x0080; | |
510 | ||
511 | if ($flags & 0x40) { | |
512 | $forcemodes |= 0x10; | |
513 | } | |
514 | ||
515 | if ($flags & 0x2) { | |
516 | $state=2; | |
517 | } elsif ($flags & 0x20) { | |
518 | $state=3; | |
519 | } else { | |
520 | $state=10; | |
521 | } | |
522 | } elsif ($state==2) { | |
523 | /^(\S+) (.+)$/ or next; | |
524 | ||
525 | $suspendby = $users{irc_lc($1)}; | |
526 | if (not defined $suspendby) { | |
527 | $suspendby=0; | |
528 | } | |
529 | $suspendreason=doquote($2); | |
530 | ||
531 | $cflags |= 0x4000; | |
532 | if ($flags & 0x20) { | |
533 | $state=3; | |
534 | } else { | |
535 | $state=10; | |
536 | } | |
537 | } elsif ($state==3) { | |
538 | $cflags |= 0x2000; | |
539 | $welcome=doquote($_); | |
540 | $state=10; | |
541 | } elsif ($state==10) { | |
542 | if (/^--- End/) { | |
543 | unless ($skipadd) { | |
544 | print CHANS "$curchanid,".doquote($channame).",$cflags,$forcemodes,0,0,10,0,$added,$lastused,$added,0,$founder,$addedby,$suspendby,0,0,0,0,0,0,$welcome,,,$suspendreason,,0\n"; | |
545 | if (($chanid % 1000)==0) { | |
546 | close CHANS; | |
547 | my $fname=sprintf("chans-%03d.csv",$chanid/1000); | |
548 | open CHANS, ">$fname"; | |
549 | print SQL "COPY channels FROM '${pathname}${fname}' DELIMITER ',';\n"; | |
550 | } | |
551 | } | |
552 | $state=1; | |
553 | } else { | |
554 | /^(\S+)\s+(\S+)$/ or next; | |
555 | $cuflags=0; | |
556 | my $ocuflags=$2; | |
557 | $userid=getuserid($1); | |
558 | if ($userid != 0) { | |
0a53499c | 559 | if (($ocuflags =~ /g/) && ($ocuflags =~ /v/)) { |
0dc8c584 | 560 | $cuflags |= 0x8; |
561 | } | |
0a53499c | 562 | if (($ocuflags =~ /a/) && ($ocuflags =~ /o/)) { |
563 | $cuflags |= 0x1; | |
564 | $cuflags &= (~0x8); | |
565 | } | |
0dc8c584 | 566 | if ($ocuflags =~ /m/) { |
567 | $cuflags |= 0x4000; | |
568 | } | |
569 | if ($ocuflags =~ /n/) { | |
570 | $cuflags |= 0x8000; | |
571 | } | |
572 | if ($ocuflags =~ /o/) { | |
573 | $cuflags |= 0x2000; | |
574 | } | |
575 | if ($ocuflags =~ /v/) { | |
576 | $cuflags |= 0x1000; | |
577 | } | |
578 | ||
579 | my $idstr=$userid.".".$curchanid; | |
580 | unless (exists $chanusers{$idstr}) { | |
581 | $chanusers{$idstr}=1; | |
582 | print CHANUSERS "$userid,$curchanid,$cuflags,0,0,\n"; | |
583 | $chanusercount++; | |
584 | if (!($chanusercount % 10000)) { | |
585 | close CHANUSERS; | |
586 | my $fname=sprintf("chanusers-%03d.csv",$chanusercount/10000); | |
587 | open CHANUSERS, ">$fname"; | |
588 | print SQL "COPY chanusers FROM '${pathname}${fname}' DELIMITER ',';\n"; | |
589 | } | |
590 | } else { | |
591 | print "Suppressing duplicate chanuser: $idstr\n"; | |
592 | } | |
593 | } | |
594 | } | |
595 | } | |
596 | } | |
597 | } | |
598 | ||
599 | sub irc_lc { | |
600 | my ($incoming) = @_; | |
601 | ||
602 | $incoming = lc $incoming; | |
603 |