contrib/flat2sql.pl

   1 #!/usr/bin/perl
   2
   3 # flat2sql.pl v1.0
   4 #
   5 # Copyright (c) 2002,  Michael Blandford and Tal Yardeni
   6 #
   7 # This script is licensed under GPL.
   8 ##### Conf Section #####
   9
  10 $data_dir = "/home/www/mail.topolis.inet/www/squirrelmail-devel-20050911/data";
  11 $db = "squirrelmail";
  12 $abook_table = "address";
  13 $pref_table = "userprefs";
  14
  15 ##### ##### #####
  16
  17 use Getopt::Long;
  18
  19 &GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s ) );
  20
  21 &Usage if ( defined $opts{h} or defined $opts{help} );
  22
  23 unless ( defined $opts{abook} or defined $opts{pref} or
  24          defined $opts{sig}) {
  25         $opts{abook}=TRUE;
  26         $opts{pref}=TRUE;
  27         $opts{sig}=TRUE;
  28 }
  29
  30 # Override the data directory if passed as an argument
  31 $data_dir = $opts{data_dir} if ( defined $opts{data_dir} );
  32
  33 # Are we looking for specific users or all users?
  34 # There has to be a better way to do this - Below
  35  @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
  36
  37 # Here we go
  38 # If no arguments are passed, and we cant open the dir, we should
  39 # get a usage.
  40 opendir(DIR, $data_dir) or
  41   die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
  42
  43 while ( $filename = readdir DIR ) {
  44   next if ( $filename eq "." or $filename eq ".." );
  45   $filename =~ /(.*)\.(.*)/;
  46   $username = $1;
  47
  48   # Deal with the people
  49   # There has to be a better way to do this - Above
  50   next if ( defined $opts{user} and grep(!/$username/, @user_list));
  51
  52   # Deal with the extension files
  53   $ext = $2;
  54   next unless $ext;
  55   &abook if ( $ext eq "abook" and defined $opts{abook} );
  56   &pref  if ( $ext eq "pref"  and defined $opts{pref}  );
  57   &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
  58 }
  59 closedir ( DIR );
  60
  61 # All done.  Below are functions
  62
  63 # Process a user address file
  64
  65 sub abook {
  66   print "DELETE FROM $db.$abook_table WHERE owner = '$username;\n"
  67     if ( defined $opts{delete} );
  68
  69   open(ABOOK, ">$data_dir/$filename") or
  70     die "FILE READ ERROR: Could not open $filename!!\n";
  71
  72   while (my $line = <ABOOK> ) {
  73
  74     chomp $line;
  75     my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
  76
  77     print "INSERT INTO $db.$abook_table "
  78         . "(owner,nickname,firstname,lastname,email,label) "
  79         . "VALUES ('$username','$nickname','$firstname','$lastname',"
  80         . "'$email','$label');\n";
  81   }
  82
  83   close(ABOOK);
  84 }
  85
  86 # Process a user prefernce file
  87
  88 sub pref {
  89   print "DELETE FROM $db.$pref_table "
  90     . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
  91     if ( defined $opts{delete} );
  92
  93   open(PREFS, "<$data_dir/$filename") or
  94     die "FILE READ ERROR: Could not open $filename!!\n";
  95
  96   while (my $line = <PREFS> ) {
  97
  98     chomp $line;
  99     my ( $prefkey, $prefval ) = split(/=/, $line);
 100
 101     print "INSERT INTO $db.$pref_table "
 102         . "(user,prefkey,prefval) "
 103         . "VALUES ('$username','$prefkey','$prefval');\n";
 104
 105   }
 106
 107   close(PREFS);
 108 }
 109
 110 # Process a user sig file
 111
 112 sub sig {
 113
 114   $del_ext = $1;
 115   $del_ext = "nature" if ( $del_ext eq "g" );
 116   print "DELETE FROM $db.$pref_table "
 117     . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
 118       if ( defined $opts{delete} );
 119
 120   open(SIG, "<$data_dir/$filename") or
 121     die "FILE READ ERROR: Could not open $filename!!\n";
 122
 123   my  @lines = <SIG>;
 124   close(SIG);
 125
 126   $filename =~ /.*\.si([g,\d]$)/;
 127   $prefkey = "___sig";
 128   if ( $1 eq "g" ) {
 129     $prefkey .= "nature___";
 130   } else {
 131     $prefkey .= "$1___";
 132   }
 133
 134   print "INSERT INTO $db.$sig_table (user,prefkey,prefval) "
 135     . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
 136 }
 137
 138 # Print out the usage screen
 139
 140 sub Usage {
 141
 142 $0 =~ /.*\/(.*)/;
 143 $prog = $1;
 144   print <<EOL;
 145 This program generates SQL statements to aid importing squirrelmail
 146 user config into a database.
 147
 148 Usage: $prog [--delete] [--abook] [--pref] [--sig] [--data_dir=<>] [--user=<username0[,username1[,username2]...]]
 149
 150 Prefs --abook, --pref, and --sig are assumed if none of them as passed
 151
 152 --delete removes all previous values for users ( --users=<> ) already in
 153 the database.  This is useful to reimport users.
 154 It respects --abook, --pref, and --sig.
 155
 156 If --user is not specified, it will try to do all users.
 157
 158 EOL
 159   exit 1;
 160 }