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