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-2006 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 $dbtype = 'mysql';
  20 ##### ##### #####
  21
  22 use Getopt::Long;
  23
  24 &GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) );
  25
  26 &Usage if ( defined $opts{h} or defined $opts{help} );
  27
  28 unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
  29     $opts{abook}=TRUE;
  30     $opts{pref}=TRUE;
  31     $opts{sig}=TRUE;
  32 }
  33
  34
  35 if ( defined $opts{db} and $opts{db} ) {
  36     $db = $opts{db};
  37 }
  38 if ( defined $opts{pref_table} and $opts{pref_table} ) {
  39     $pref_table = $opts{pref_table};
  40 }
  41 if ( defined $opts{abook_table} and $opts{abook_table}) {
  42     $abook_table = $opts{abook_table};
  43 }
  44
  45 # Get data directory option and display help if it is not defined
  46 if ( defined $opts{data_dir} and $opts{data_dir} ) {
  47     $data_dir = $opts{data_dir};
  48 } else {
  49     &Usage;
  50 }
  51
  52 # Are we looking for specific users or all users?
  53 # There has to be a better way to do this - Below
  54  @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
  55
  56 # Here we go
  57 # If no arguments are passed, and we cant open the dir, we should
  58 # get a usage.
  59 opendir(DIR, $data_dir) or
  60   die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
  61
  62 while ( $filename = readdir DIR ) {
  63   next if ( $filename eq "." or $filename eq ".." );
  64   $filename =~ /(.*)\.(.*)/;
  65   $username = $1;
  66
  67   # Deal with the people
  68   # There has to be a better way to do this - Above
  69   next if ( defined $opts{user} and grep(!/$username/, @user_list));
  70
  71   # Deal with the extension files
  72   $ext = $2;
  73   next unless $ext;
  74   &abook if ( $ext eq "abook" and defined $opts{abook} );
  75   &pref  if ( $ext eq "pref"  and defined $opts{pref}  );
  76   &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
  77 }
  78 closedir ( DIR );
  79
  80 # All done.  Below are functions
  81
  82 # Process a user address file
  83
  84 sub abook {
  85   print "DELETE FROM $db.$abook_table WHERE owner = '".escape_sql_string($username,true)."';\n"
  86     if ( defined $opts{delete} );
  87
  88   open(ABOOK, "<$data_dir/$filename") or
  89     die "FILE READ ERROR: Could not open $filename!!\n";
  90
  91   while (my $line = <ABOOK> ) {
  92
  93     chomp $line;
  94     my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
  95
  96     print "INSERT INTO $db.$abook_table "
  97         . "(owner,nickname,firstname,lastname,email,label) "
  98         . "VALUES ('"
  99         .escape_sql_string($username)."','"
 100         .escape_sql_string($nickname)."','"
 101         .escape_sql_string($firstname)."','"
 102         .escape_sql_string($lastname)."','"
 103         .escape_sql_string($email)."','"
 104         .escape_sql_string($label)."');\n";
 105   }
 106
 107   close(ABOOK);
 108 }
 109
 110 # Process a user preference file
 111 sub pref {
 112   print "DELETE FROM $db.$pref_table "
 113     . "WHERE user = '".escape_sql_string($username,true)."' and prefkey not like '___sig\%___';\n"
 114     if ( defined $opts{delete} );
 115
 116   open(PREFS, "<$data_dir/$filename") or
 117     die "FILE READ ERROR: Could not open $filename!!\n";
 118
 119   while (my $line = <PREFS> ) {
 120
 121     chomp $line;
 122     my ( $prefkey, $prefval ) = split(/=/, $line);
 123
 124     print "INSERT INTO $db.$pref_table "
 125         . "(user,prefkey,prefval) "
 126         . "VALUES ('"
 127         .escape_sql_string($username)."','"
 128         .escape_sql_string($prefkey)."','"
 129         .escape_sql_string($prefval)."');\n";
 130
 131   }
 132
 133   close(PREFS);
 134 }
 135
 136 # Process a user signature file
 137 sub sig {
 138
 139   $del_ext = $1;
 140   $del_ext = "nature" if ( $del_ext eq "g" );
 141   print "DELETE FROM $db.$pref_table "
 142     . "WHERE user = '".escape_sql_string($username,true)."' and prefkey like '___sig" . escape_sql_string($del_ext,true) . "___';\n"
 143       if ( defined $opts{delete} );
 144
 145   open(SIG, "<$data_dir/$filename") or
 146     die "FILE READ ERROR: Could not open $filename!!\n";
 147
 148   my  @lines = <SIG>;
 149   close(SIG);
 150
 151   $filename =~ /.*\.si([g,\d]$)/;
 152   $prefkey = "___sig";
 153   if ( $1 eq "g" ) {
 154     $prefkey .= "nature___";
 155   } else {
 156     $prefkey .= "$1___";
 157   }
 158
 159   print "INSERT INTO $db.$pref_table (user,prefkey,prefval) "
 160      . "VALUES ('".escape_sql_string($username)."','"
 161      .escape_sql_string($prefkey)."','"
 162      .escape_sql_string(join("", @lines))."');\n";
 163 }
 164
 165 # Escapes sql strings
 166 # MySQL escaping:
 167 #  http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
 168 #  full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z)
 169 #         add % and _ in pattern matching expressions.
 170 #  short - only character used for quoting and backslash should be escaped
 171 # PostgreSQL
 172 # Oracle
 173 # Sybase - different quoting of '
 174 sub escape_sql_string() {
 175   my ($str,$isPattern) = @_;
 176
 177   if ($dbtype eq 'mysql'){
 178     # escape \, ' and "
 179     $str =~ s/(['"\\])/\\$1/g;
 180     # escape \x1a
 181     $str =~ s/([\x1a])/\\Z/g;
 182     # escape ascii null
 183     $str =~ s/([\x0])/\\0/g;
 184     # escape line feed
 185     $str =~ s/([\n])/\\n/g;
 186     # escape cr
 187     $str =~ s/([\r])/\\r/g;
 188     if ($isPattern) {
 189       $str =~ s/([%_])/\\$1/g;
 190     }
 191   } else {
 192     die "ERROR: Unsupported database type";
 193   }
 194   return $str;
 195 }
 196
 197
 198 # Print out the usage screen
 199 sub Usage {
 200
 201 $0 =~ /.*\/(.*)/;
 202 $prog = $1;
 203   print <<EOL;
 204
 205 This program generates SQL statements to aid importing SquirrelMail
 206 user config into a database.
 207
 208 WARNING: this script is experimental. Don't use it as
 209 privileged user or backup your data directory before using it.
 210
 211 Usage: $prog --data_dir=<data_dir> [--delete] [--abook] [--pref] [--sig]
 212   [--user=<username0[,username1[,username2]...]]
 213   [--db=<database>] [--pref_table=<userprefs>] [--abook_table=<address>]
 214
 215 --data_dir option must define path to SquirrelMail data directory. If
 216 option is not defined, script displays this help message.
 217
 218 --abook option is used to generate SQL with address books.
 219 --pref option is used to generate SQL with user preferences.
 220 --sig option is used to generate SQL with signatures.
 221
 222 --db option can be used to set database name. Script defaults to
 223 'squirrelmail'.
 224
 225 --pref_table option can be used to set preference table name. Script
 226 defaults to 'userprefs'.
 227
 228 --abook_table option can be used to set address book table name. Script
 229 defaults to 'address'.
 230
 231 Prefs --abook, --pref, and --sig are assumed if none of them as passed
 232
 233 --delete removes all previous values for users ( --users=<> ) already in
 234 the database.  This is useful to reimport users.
 235 It respects --abook, --pref, and --sig.
 236
 237 If --user is not specified, script extracts all user data.
 238
 239 EOL
 240   exit 1;
 241 }