contrib/flat2sql.pl

   1 #!/usr/bin/perl
   2 #
   3 # Converts file based preferences into SQL statements.
   4 #
   5 # WARNING: this script is experimental. Don't use it as
   6 # privileged user or backup your data directory before using it.
   7 #
   8 # Copyright (c) 2002, Michael Blandford and Tal Yardeni
   9 # Copyright (c) 2005, The SquirrelMail Project Team
  10 #
  11 # This script is licensed under GPL.
  12 # $Id$
  13 #
  14
  15 ##### Default values #####
  16 $db = "squirrelmail";
  17 $abook_table = "address";
  18 $pref_table = "userprefs";
  19 ##### ##### #####
  20
  21 use Getopt::Long;
  22
  23 &GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) );
  24
  25 &Usage if ( defined $opts{h} or defined $opts{help} );
  26
  27 unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
  28     $opts{abook}=TRUE;
  29     $opts{pref}=TRUE;
  30     $opts{sig}=TRUE;
  31 }
  32
  33
  34 if ( defined $opts{db} and $opts{db} ) {
  35     $db = $opts{db};
  36 }
  37 if ( defined $opts{pref_table} and $opts{pref_table} ) {
  38     $pref_table = $opts{pref_table};
  39 }
  40 if ( defined $opts{abook_table} and $opts{abook_table}) {
  41     $abook_table = $opts{abook_table};
  42 }
  43
  44 # Get data directory option and display help if it is not defined
  45 if ( defined $opts{data_dir} and $opts{data_dir} ) {
  46     $data_dir = $opts{data_dir};
  47 } else {
  48     &Usage;
  49 }
  50
  51 # Are we looking for specific users or all users?
  52 # There has to be a better way to do this - Below
  53  @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
  54
  55 # Here we go
  56 # If no arguments are passed, and we cant open the dir, we should
  57 # get a usage.
  58 opendir(DIR, $data_dir) or
  59   die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
  60
  61 while ( $filename = readdir DIR ) {
  62   next if ( $filename eq "." or $filename eq ".." );
  63   $filename =~ /(.*)\.(.*)/;
  64   $username = $1;
  65
  66   # Deal with the people
  67   # There has to be a better way to do this - Above
  68   next if ( defined $opts{user} and grep(!/$username/, @user_list));
  69
  70   # Deal with the extension files
  71   $ext = $2;
  72   next unless $ext;
  73   &abook if ( $ext eq "abook" and defined $opts{abook} );
  74   &pref  if ( $ext eq "pref"  and defined $opts{pref}  );
  75   &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
  76 }
  77 closedir ( DIR );
  78
  79 # All done.  Below are functions
  80
  81 # Process a user address file
  82
  83 sub abook {
  84   print "DELETE FROM $db.$abook_table WHERE owner = '$username';\n"
  85     if ( defined $opts{delete} );
  86
  87   open(ABOOK, "<$data_dir/$filename") or
  88     die "FILE READ ERROR: Could not open $filename!!\n";
  89
  90   while (my $line = <ABOOK> ) {
  91
  92     chomp $line;
  93     my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
  94
  95     print "INSERT INTO $db.$abook_table "
  96         . "(owner,nickname,firstname,lastname,email,label) "
  97         . "VALUES ('$username','$nickname','$firstname','$lastname',"
  98         . "'$email','$label');\n";
  99   }
 100
 101   close(ABOOK);
 102 }
 103
 104 # Process a user prefernce file
 105
 106 sub pref {
 107   print "DELETE FROM $db.$pref_table "
 108     . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
 109     if ( defined $opts{delete} );
 110
 111   open(PREFS, "<$data_dir/$filename") or
 112     die "FILE READ ERROR: Could not open $filename!!\n";
 113
 114   while (my $line = <PREFS> ) {
 115
 116     chomp $line;
 117     my ( $prefkey, $prefval ) = split(/=/, $line);
 118
 119     print "INSERT INTO $db.$pref_table "
 120         . "(user,prefkey,prefval) "
 121         . "VALUES ('$username','$prefkey','$prefval');\n";
 122
 123   }
 124
 125   close(PREFS);
 126 }
 127
 128 # Process a user sig file
 129
 130 sub sig {
 131
 132   $del_ext = $1;
 133   $del_ext = "nature" if ( $del_ext eq "g" );
 134   print "DELETE FROM $db.$pref_table "
 135     . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
 136       if ( defined $opts{delete} );
 137
 138   open(SIG, "<$data_dir/$filename") or
 139     die "FILE READ ERROR: Could not open $filename!!\n";
 140
 141   my  @lines = <SIG>;
 142   close(SIG);
 143
 144   $filename =~ /.*\.si([g,\d]$)/;
 145   $prefkey = "___sig";
 146   if ( $1 eq "g" ) {
 147     $prefkey .= "nature___";
 148   } else {
 149     $prefkey .= "$1___";
 150   }
 151
 152   print "INSERT INTO $db.$pref_table (user,prefkey,prefval) "
 153     . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
 154 }
 155
 156 # Print out the usage screen
 157
 158 sub Usage {
 159
 160 $0 =~ /.*\/(.*)/;
 161 $prog = $1;
 162   print <<EOL;
 163
 164 This program generates SQL statements to aid importing SquirrelMail
 165 user config into a database.
 166
 167 WARNING: this script is experimental. Don't use it as
 168 privileged user or backup your data directory before using it.
 169
 170 Usage: $prog --data_dir=<data_dir> [--delete] [--abook] [--pref] [--sig]
 171   [--user=<username0[,username1[,username2]...]]
 172   [--db=<database>] [--pref_table=<userprefs>] [--abook_table=<address>]
 173
 174 --data_dir option must define path to SquirrelMail data directory. If
 175 option is not defined, script displays this help message.
 176
 177 --abook option is used to generate SQL with address books.
 178 --pref option is used to generate SQL with user preferences.
 179 --sig option is used to generate SQL with signatures.
 180
 181 --db option can be used to set database name. Script defaults to
 182 'squirrelmail'.
 183
 184 --pref_table option can be used to set preference table name. Script
 185 defaults to 'userprefs'.
 186
 187 --abook_table option can be used to set address book table name. Script
 188 defaults to 'address'.
 189
 190 Prefs --abook, --pref, and --sig are assumed if none of them as passed
 191
 192 --delete removes all previous values for users ( --users=<> ) already in
 193 the database.  This is useful to reimport users.
 194 It respects --abook, --pref, and --sig.
 195
 196 If --user is not specified, script extracts all user data.
 197
 198 EOL
 199   exit 1;
 200 }