chanserv/utils/conv4.pl

   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;
 227   }
 228
 229   if ($oldflags & 0x20) { # +f
 230     $newflags |= 0x10;
 231   }
 232
 233   if ($oldflags & 0x8000) { # +p
 234     $newflags |= 0x200;
 235   }
 236
 237   if ($oldflags & 0x80000) { # +t
 238     $newflags |= 0x800;
 239   }
 240
 241   if ($oldflags & 0x400000) { # +w
 242     $newflags |= 0x2000;
 243   }
 244
 245   if ($oldflags & 0x400) { # +k: maps to forcemode +k
 246     $forcemodes |= 0x40;
 247   }
 248
 249   if ($oldflags & 0x800) { # +l: maps to forcemode +l
 250     $forcemodes |= 0x20;
 251   }
 252
 253   return ($forcemodes, $newflags);
 254 }
 255
 256 sub loadusers {
 257   open USERLIST, "users" or die "Unable to open user list";
 258
 259   my $username;
 260   my $userid;
 261   my $password;
 262   my $globalauth;
 263   my $lastauth;
 264   my $lastyxx;
 265   my $lastusername;
 266   my $lasthostname;
 267   my $lastemail;
 268   my $emailaddr;
 269   my $chan;
 270   my $flags;
 271   my $uflags;
 272   my $realflags;
 273   my $usercount=0;
 274   my $lastemailrq;
 275   my $lockuntil;
 276   my $nuflags;
 277   my $state=0;
 278   my $suspendwho=0;
 279   my $suspendtime=0;
 280   my $suspendexp=0;
 281   my $suspendreason="";
 282   my $created=0;
 283
 284   while (<USERLIST>) {
 285     chomp;
 286
 287     if ($state == 0) {
 288       $username=$_;
 289     } elsif ($state == 1) {
 290       $userid=$_;
 291     } elsif ($state == 2) {
 292       $password=$_;
 293     } elsif ($state == 3) {
 294       $globalauth=$_;
 295     } elsif ($state == 4) {
 296       $lastauth=$_;
 297     } elsif ($state == 5) {
 298       $lastyxx=$_;
 299     } elsif ($state == 6) {
 300       $lastusername=$_;
 301     } elsif ($state == 7) {
 302       $lasthostname=$_;
 303     } elsif ($state == 8) {
 304       $lastemailrq=$_;
 305     } elsif ($state == 9) {
 306       $emailaddr=$_;
 307     } elsif ($state == 10) {
 308       $lockuntil=$_;
 309     } elsif ($state == 11) {
 310       $lastemail=$_;
 311     } elsif ($state == 12) {
 312       $nuflags=$_;
 313     } elsif ($state == 13) {
 314       if ($_ eq "end") {
 315 #       print "Got user $username [$userid]\n";
 316         $uflags=4;
 317         if ($globalauth >= 900) {
 318           $uflags |= 0x20;
 319         }
 320         if ($globalauth > 10 and $globalauth < 900) {
 321           $uflags |= 0x100;
 322         }
 323         if ($globalauth >= 1000) {
 324           $uflags |= 0x40;
 325         }
 326         if ($nuflags & 8) { # No auth limit
 327           $uflags |= 0x1000;
 328         }
 329         if ($nuflags & 64) { # No delete
 330           $uflags |= 0x4000;
 331         }
 332         if ($nuflags & 1) { # Has trust
 333           $uflags |= 0x8000;
 334         }
 335         if (exists $suspends{$username}) {
 336           my $ar=$suspends{$username};
 337           $created=0; # Q doesn't have created time for auths so default it to 0
 338           if ($$ar[0] == 7) { # never authed - copy the "last auth" time into "created time".
 339             $created=$lastauth;
 340             $lastauth=0; # new Q uses lastauth=0 to indicate an unused account
 341           } elsif ($$ar[0] == 5) { # delayed gline
 342             $uflags |= 0x0800;
 343           } elsif ($$ar[0] == 6) { # instant gline
 344             $uflags |= 0x2;
 345           } elsif ($$ar[0] == 1) { # public
 346             $uflags |= 0x10;
 347           } else {
 348             print "Suspend reason $$ar[0] found!\n";
 349             $uflags |= 0x10;
 350           }
 351           $suspendwho=$$ar[1];
 352           $suspendtime=$$ar[2];
 353           $suspendexp=$$ar[3];
 354           $suspendreason=doquote($$ar[4]);
 355         } else {
 356           $suspendwho=0;
 357           $suspendtime=0;
 358           $suspendexp=0;
 359           $suspendreason="";
 360           $created=0;
 361         }
 362         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";
 363         $users{irc_lc($username)}=$userid;
 364         $state=-1;
 365         $usercount++;
 366         unless ($usercount % 1000) {
 367           print ".";
 368           close USERS;
 369           my $fname=sprintf("users-%03d.csv",$usercount/1000);
 370           open USERS, ">$fname";
 371           print SQL "COPY users FROM '${pathname}${fname}' DELIMITER ',';\n";
 372         }
 373       } else {
 374         if (not defined $chans{irc_lc($_)}) {
 375           print "Error: unknown channel $_\n";
 376           $chan=0;
 377         } else {
 378           $chan=$chans{irc_lc($_)};
 379         }
 380       }
 381     } elsif ($state == 14) {
 382       if ($chan>0) {
 383         $flags=$_;
 384         $realflags=map_chanlevflags($flags);
 385         my $idstr=$userid.".".$chan;
 386         unless (exists $chanusers{$idstr}) {
 387           print CHANUSERS "$userid,$chan,$realflags,0,0,\n";
 388           $chanusercount++;
 389           if (!($chanusercount % 10000)) {
 390             close CHANUSERS;
 391             my $fname=sprintf("chanusers-%03d.csv",$chanusercount/10000);
 392             open CHANUSERS, ">$fname";
 393             print SQL "COPY chanusers FROM '${pathname}${fname}' DELIMITER ',';\n";
 394           }
 395           $chanusers{$idstr}=1;
 396         }
 397       }
 398       $state=12;
 399     }
 400
 401     $state++;
 402   }
 403
 404   close USERLIST;
 405 }
 406
 407 sub map_chanlevflags {
 408   my ($oldflags) = @_;
 409
 410   my $newflags=0;
 411
 412   if ($oldflags & 0x1) { # +a
 413     $newflags |= 0x9;
 414   }
 415
 416   if ($oldflags & 0x2) { # +b
 417     $newflags |= 0x2;
 418   }
 419
 420   if ($oldflags & 0x1000) { # +m
 421     $newflags |= 0x4000;
 422   }
 423
 424   if ($oldflags & 0x2000) { # +n
 425     $newflags |= 0x8000;
 426   }
 427
 428   if ($oldflags & 0x4000) { # +o
 429     $newflags |= 0x2000;
 430   }
 431
 432   if ($oldflags & 0x200000) { # +v
 433     $newflags |= 0x1000;
 434   }
 435
 436   if ($oldflags & 0x80000) { # +t
 437     $newflags |= 0x40;
 438   }
 439
 440   if ($newflags & 0x2000) {
 441     $newflags &= ~0x8;
 442   } elsif ($newflags & 0x1000) {
 443     $newflags &= ~0x1;
 444   } else {
 445     $newflags &= ~0x9;
 446   }
 447
 448   return $newflags;
 449 }
 450
 451 sub loadldb {
 452   open LDB, "accounts.0" or die "Error opening lightweight database";
 453
 454   my $state=0;
 455
 456   my $channame;
 457   my $addedby;
 458   my $founder;
 459   my $lastused;
 460   my $added;
 461   my $flags;
 462   my $cuflags;
 463   my $userid;
 464   my $suspendby;
 465   my $suspendreason;
 466   my $cflags;
 467   my $welcome;
 468   my $curchanid;
 469   my $skipadd;
 470   my $forcemodes;
 471
 472   while (<LDB>) {
 473     chomp;
 474
 475     if ($state == 0) {
 476       /^--- End of/ && ($state=1);
 477     } elsif ($state==1) {
 478       /^(.+?) (.+?) (.+?) (\d+) (\d+) (\d+)$/ or next;
 479       $channame=$1;
 480       $addedby=getuserid($2);
 481       $founder=getuserid($3);
 482       $lastused=$4;
 483       $added=$5;
 484       $flags=$6;
 485       $suspendby=0;
 486       $suspendreason="";
 487       $welcome="";
 488       $forcemodes=0x3;
 489       $skipadd=0;
 490
 491       if (defined $chans{irc_lc($channame)}) {
 492         $curchanid=$chans{irc_lc($channame)};
 493         print "Duplicate channel $channame, merging chanlev\n";
 494         $skipadd=1;
 495       } else {
 496         unless ($chanid % 1000) {
 497           print ".";
 498         }
 499         $curchanid=$chanid;
 500         $chanid++;
 501       }
 502
 503       $cflags=0x0080;
 504
 505       if ($flags & 0x40) {
 506         $forcemodes |= 0x10;
 507       }
 508
 509       if ($flags & 0x2) {
 510         $state=2;
 511       } elsif ($flags & 0x20) {
 512         $state=3;
 513       } else {
 514         $state=10;
 515       }
 516     } elsif ($state==2) {
 517       /^(\S+) (.+)$/ or next;
 518
 519       $suspendby = $users{irc_lc($1)};
 520       if (not defined $suspendby) {
 521         $suspendby=0;
 522       }
 523       $suspendreason=doquote($2);
 524
 525       $cflags |= 0x4000;
 526       if ($flags & 0x20) {
 527         $state=3;
 528       } else {
 529         $state=10;
 530       }
 531     } elsif ($state==3) {
 532       $cflags |= 0x2000;
 533       $welcome=doquote($_);
 534       $state=10;
 535     } elsif ($state==10) {
 536       if (/^--- End/) {
 537         unless ($skipadd) {
 538           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";
 539           if (($chanid % 1000)==0) {
 540             close CHANS;
 541             my $fname=sprintf("chans-%03d.csv",$chanid/1000);
 542             open CHANS, ">$fname";
 543             print SQL "COPY channels FROM '${pathname}${fname}' DELIMITER ',';\n";
 544           }
 545         }
 546         $state=1;
 547       } else {
 548         /^(\S+)\s+(\S+)$/ or next;
 549         $cuflags=0;
 550         my $ocuflags=$2;
 551         $userid=getuserid($1);
 552         if ($userid != 0) {
 553           if ($ocuflags =~ /a/) {
 554             $cuflags |= 0x1;
 555           }
 556           if ($ocuflags =~ /g/) {
 557             $cuflags |= 0x8;
 558           }
 559           if ($ocuflags =~ /m/) {
 560             $cuflags |= 0x4000;
 561           }
 562           if ($ocuflags =~ /n/) {
 563             $cuflags |= 0x8000;
 564           }
 565           if ($ocuflags =~ /o/) {
 566             $cuflags |= 0x2000;
 567           }
 568           if ($ocuflags =~ /v/) {
 569             $cuflags |= 0x1000;
 570           }
 571
 572           my $idstr=$userid.".".$curchanid;
 573           unless (exists $chanusers{$idstr}) {
 574             $chanusers{$idstr}=1;
 575             print CHANUSERS "$userid,$curchanid,$cuflags,0,0,\n";
 576             $chanusercount++;
 577             if (!($chanusercount % 10000)) {
 578               close CHANUSERS;
 579               my $fname=sprintf("chanusers-%03d.csv",$chanusercount/10000);
 580               open CHANUSERS, ">$fname";
 581               print SQL "COPY chanusers FROM '${pathname}${fname}' DELIMITER ',';\n";
 582             }
 583           } else {
 584             print "Suppressing duplicate chanuser: $idstr\n";
 585           }
 586         }
 587       }
 588     }
 589   }
 590 }
 591
 592 sub irc_lc {
 593   my ($incoming) = @_;
 594
 595   $incoming = lc $incoming;
 596   $incoming =~ tr/\[\]\|~ÆÁÀÜÐÄÅÖ/\{\}\\^æáàüðäåö/;
 597
 598   return $incoming;
 599 }
 600
 601 sub getuserid {
 602   my ($username) = @_;
 603   my $userid;
 604
 605   $userid=$users{irc_lc($username)};
 606   if (not defined $userid) {
 607     print ORPHANS irc_lc($username)."\n";
 608     return 0;
 609   }
 610
 611   return $userid;
 612 }
 613
 614 sub doquote {
 615   my ($instr) = @_;
 616
 617  my $outstr = "";
 618
 619   for (split //, $instr) {
 620     if ($_ eq ",") {
 621       $outstr .= "\\,";
 622     } elsif ($_ eq "\\") {
 623       $outstr .= "\\\\";
 624     } else {
 625       $outstr .= $_;
 626     }
 627   }
 628
 629 #  $outstr .= "\'";
 630
 631   return $outstr;
 632 }