contrib/flat2sql.pl

   1 #!/usr/bin/perl
   2 #
   3 # Converts file-based preferences into SQL statements.
   4 #
   5 # WARNING: this script is experimental.  We recommend that
   6 # you not use it when logged in as a privileged user (such
   7 # as root).  Also, ALWAYS back up your data directory before
   8 # using this script.
   9 #
  10 # Copyright (c) 2002, Michael Blandford and Tal Yardeni
  11 # Copyright (c) 2005-2017 The SquirrelMail Project Team
  12 #
  13 # This script is licensed under the GNU Public License (GPL).
  14 # See: http://opensource.org/licenses/gpl-license.php
  15 # $Id$
  16 #
  17
  18 ##### Default values #####
  19 # TODO: expose the database type as a CLI option, but first need more sections in sub escape_sql_string()
  20 my $dbtype = 'mysql';
  21 my $abookdb = "squirrelmail";
  22 my $prefdb = "squirrelmail";
  23 my $abook_table = "address";
  24 my $abook_owner="owner";
  25 my $abook_nickname="nickname";
  26 my $abook_firstname="firstname";
  27 my $abook_lastname="lastname";
  28 my $abook_email="email";
  29 my $abook_label="label";
  30 my $pref_table = "userprefs";
  31 my $pref_user = "user";
  32 my $pref_key = "prefkey";
  33 my $pref_value = "prefval";
  34 ##### ##### #####
  35
  36 use Getopt::Long;
  37
  38 my (%opts, $verbose, $data_dir);
  39
  40 &GetOptions( \%opts, qw( abook data_dir:s delete h help v verbose pref sig user:s abookdb:s prefdb:s pref_table:s abook_table:s abook_owner:s abook_nickname:s abook_firstname:s abook_lastname:s abook_email:s abook_label:s pref_user:s pref_key:s pref_value:s) );
  41
  42 &Usage   if ( defined $opts{h} or defined $opts{help} );
  43
  44 unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
  45     $opts{abook}='TRUE';
  46     $opts{pref}='TRUE';
  47     $opts{sig}='TRUE';
  48 }
  49
  50
  51 if ( defined $opts{verbose} or defined $opts{v} ) {
  52     $verbose = 1;
  53 }
  54 if ( defined $opts{abookdb} and $opts{abookdb} ) {
  55     $abookdb = $opts{abookdb};
  56 }
  57 if ( defined $opts{prefdb} and $opts{prefdb} ) {
  58     $prefdb = $opts{prefdb};
  59 }
  60 if ( defined $opts{pref_table} and $opts{pref_table} ) {
  61     $pref_table = $opts{pref_table};
  62 }
  63 if ( defined $opts{pref_user} and $opts{pref_user} ) {
  64     $pref_user = $opts{pref_user};
  65 }
  66 if ( defined $opts{pref_key} and $opts{pref_key} ) {
  67     $pref_key = $opts{pref_key};
  68 }
  69 if ( defined $opts{pref_value} and $opts{pref_value} ) {
  70     $pref_value = $opts{pref_value};
  71 }
  72 if ( defined $opts{abook_table} and $opts{abook_table}) {
  73     $abook_table = $opts{abook_table};
  74 }
  75 if ( defined $opts{abook_owner} and $opts{abook_owner} ) {
  76     $abook_owner = $opts{abook_owner};
  77 }
  78 if ( defined $opts{abook_nickname} and $opts{abook_nickname} ) {
  79     $abook_nickname = $opts{abook_nickname};
  80 }
  81 if ( defined $opts{abook_firstname} and $opts{abook_firstname} ) {
  82     $abook_firstname = $opts{abook_firstname};
  83 }
  84 if ( defined $opts{abook_lastname} and $opts{abook_lastname} ) {
  85     $abook_lastname = $opts{abook_lastname};
  86 }
  87 if ( defined $opts{abook_email} and $opts{abook_email} ) {
  88     $abook_email = $opts{abook_email};
  89 }
  90 if ( defined $opts{abook_label} and $opts{abook_label} ) {
  91     $abook_label = $opts{abook_label};
  92 }
  93
  94 # Get data directory option and display help if it is not defined
  95 if ( defined $opts{data_dir} and $opts{data_dir} ) {
  96     $data_dir = $opts{data_dir};
  97 } else {
  98     &Usage;
  99 }
 100
 101 # Are we looking for specific users or all users?
 102 # There has to be a better way to do this - Below
 103 my @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
 104
 105 inspect_files($data_dir);
 106
 107 # All done.  Below are functions
 108
 109
 110 # Finds needed user files in the given directory
 111 # and recurses any nested data directories as needed
 112 #
 113 sub inspect_files {
 114
 115   my ($data_dir) = @_;
 116   my ($filename, $username, $ext);
 117   local *DIR;
 118
 119   # Here we go
 120   # If no arguments are passed, and we cant open the dir, we should
 121   # get a usage.
 122   opendir(DIR, $data_dir) or
 123     die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
 124
 125   while ( $filename = readdir DIR ) {
 126
 127     next if ( $filename eq "." or $filename eq ".." );
 128
 129     if ($verbose) { print STDERR "; INSPECTING: $data_dir/$filename\n"; }
 130
 131     # recurse into nested (hashed) directories
 132     #
 133     if ($filename =~ /^[0123456789abcdef]$/ && -d "$data_dir/$filename") {
 134       inspect_files("$data_dir/$filename");
 135     }
 136
 137     next unless $filename =~ /(.*)\.(.*)/;
 138     $username = $1;
 139     next unless $username;
 140
 141     # Deal with the people
 142     # There has to be a better way to do this - Above
 143     next if (defined $opts{user} and !grep($username eq $_, @user_list));
 144
 145     # Deal with the extension files
 146     $ext = $2;
 147     next unless $ext;
 148
 149     &abook("$data_dir/$filename", $username)
 150       if ( $ext eq "abook" and defined $opts{abook} );
 151     &pref("$data_dir/$filename", $username)
 152       if ( $ext eq "pref"  and defined $opts{pref}  );
 153     &sig("$data_dir/$filename", $username)
 154       if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
 155   }
 156   closedir ( DIR );
 157
 158 }
 159
 160 # Process a user address file
 161
 162 sub abook {
 163
 164   my ($filepath, $username) = @_;
 165
 166   if ($verbose) { print STDERR "; PARSING ADDRESS BOOK DATA FROM: $filepath\n"; }
 167
 168   if ( defined $opts{delete} ) {
 169     print "DELETE FROM $abookdb.$abook_table WHERE $abook_owner = '"
 170         . escape_sql_string($username,'TRUE')
 171         . "';\n"
 172   }
 173
 174   open(ABOOK, "<$filepath") or
 175     die "FILE READ ERROR: Could not open $filepath!!\n";
 176
 177   while (my $line = <ABOOK> ) {
 178
 179     chomp $line;
 180     my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
 181
 182     print "INSERT INTO $abookdb.$abook_table "
 183         . "($abook_owner, $abook_nickname, $abook_firstname, $abook_lastname, $abook_email, $abook_label) "
 184         . "VALUES ('"
 185         . escape_sql_string($username) . "', '"
 186         . escape_sql_string($nickname) . "', '"
 187         . escape_sql_string($firstname) . "', '"
 188         . escape_sql_string($lastname) . "', '"
 189         . escape_sql_string($email) . "', '"
 190         . escape_sql_string($label) . "');\n";
 191   }
 192
 193   close(ABOOK);
 194 }
 195
 196 # Process a user preference file
 197 sub pref {
 198
 199   my ($filepath, $username) = @_;
 200
 201   if ($verbose) { print STDERR "; PARSING PREFERENCE DATA FROM: $filepath\n"; }
 202
 203   if ( defined $opts{delete} ) {
 204     print "DELETE FROM $prefdb.$pref_table "
 205         . "WHERE $pref_user = '"
 206         . escape_sql_string($username,'TRUE')
 207         . "' AND $pref_key NOT LIKE '\\_\\_\\_sig%\\_\\_\\_';\n"
 208   }
 209
 210   open(PREFS, "<$filepath") or
 211     die "FILE READ ERROR: Could not open $filepath!!\n";
 212
 213   while (my $line = <PREFS> ) {
 214
 215     chomp $line;
 216     my ( $prefkey, $prefval ) = split(/=/, $line, 2);
 217
 218     print "INSERT INTO $prefdb.$pref_table "
 219         . "($pref_user, $pref_key, $pref_value) "
 220         . "VALUES ('"
 221         . escape_sql_string($username) . "', '"
 222         . escape_sql_string($prefkey) . "', '"
 223         . escape_sql_string($prefval) . "');\n";
 224
 225   }
 226
 227   close(PREFS);
 228 }
 229
 230 # Process a user signature file
 231 sub sig {
 232
 233   my ($filepath, $username) = @_;
 234
 235   if ($verbose) { print STDERR "; PARSING SIGNATURE DATA FROM: $filepath\n"; }
 236
 237   my $del_ext = $1;
 238   $del_ext = "nature" if ( $del_ext eq "g" );
 239
 240   if ( defined $opts{delete} ) {
 241     print "DELETE FROM $prefdb.$pref_table "
 242         . "WHERE $pref_user = '"
 243         . escape_sql_string($username,'TRUE')
 244         . "' AND $pref_key = '___sig"
 245         . escape_sql_string($del_ext,'TRUE')
 246         . "___';\n"
 247   }
 248
 249   open(SIG, "<$filepath") or
 250     die "FILE READ ERROR: Could not open $filepath!!\n";
 251
 252   my  @lines = <SIG>;
 253   close(SIG);
 254
 255   $filepath =~ /.*\.si([g,\d])$/;
 256   my $prefkey = "___sig";
 257   if ( $1 eq "g" ) {
 258     $prefkey .= "nature___";
 259   } else {
 260     $prefkey .= "$1___";
 261   }
 262
 263   print "INSERT INTO $prefdb.$pref_table ($pref_user, $pref_key, $pref_value) "
 264       . "VALUES ('"
 265       . escape_sql_string($username) . "', '"
 266       . escape_sql_string($prefkey) . "', '"
 267       . escape_sql_string(join("", @lines)) . "');\n";
 268 }
 269
 270 # Escapes sql strings
 271 # MySQL escaping:
 272 #  http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
 273 #  full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z)
 274 #         add % and _ in pattern matching expressions.
 275 #  short - only character used for quoting and backslash should be escaped
 276 # PostgreSQL
 277 # Oracle
 278 # Sybase - different quoting of '
 279 sub escape_sql_string() {
 280   my ($str,$isPattern) = @_;
 281
 282   if ($dbtype eq 'mysql'){
 283     # escape \, ' and "
 284     $str =~ s/(['"\\])/\\$1/g;
 285     # escape \x1a
 286     $str =~ s/([\x1a])/\\Z/g;
 287     # escape ascii null
 288     $str =~ s/([\x00])/\\0/g;
 289     # escape line feed
 290     $str =~ s/([\n])/\\n/g;
 291     # escape cr
 292     $str =~ s/([\r])/\\r/g;
 293     if ($isPattern) {
 294       $str =~ s/([%_])/\\$1/g;
 295     }
 296   } else {
 297     die "ERROR: Unsupported database type";
 298   }
 299   return $str;
 300 }
 301
 302
 303 # Print out the usage screen
 304 sub Usage {
 305
 306 $0 =~ /.*\/(.*)/;
 307 my $prog = $1;
 308   print <<EOL;
 309
 310 This program generates SQL statements that aid in importing
 311 SquirrelMail user configuration settings from files to
 312 those in a database.
 313
 314 WARNING: this script is experimental.  We recommend that
 315 you not use it when logged in as a privileged user (such
 316 as root).  Also, ALWAYS back up your data directory before
 317 using this script.
 318
 319 Usage: $prog --data_dir=<path to data directory> \\
 320                    [--delete] \\
 321                    [--abook] [--sig] [--pref] \\
 322                    [--user=<username1[,username2[,username3]...]>] \\
 323                    [--abookdb=<database>] \\
 324                    [--abook_table=<table name>] \\
 325                    [--abook_owner=<field name>] \\
 326                    [--abook_nickname=<field name>] \\
 327                    [--abook_firstname=<field name>] \\
 328                    [--abook_lastname=<field name>] \\
 329                    [--abook_email=<field name>] \\
 330                    [--abook_label=<field name>] \\
 331                    [--prefdb=<database>] \\
 332                    [--pref_table=<table name>] \\
 333                    [--pref_user=<field name>] \\
 334                    [--pref_key=<field name>] \\
 335                    [--pref_value=<field name>] \\
 336                    [--verbose] [-v]
 337                    [--help] [-h]
 338
 339 When none of --abook, --sig or --pref is specified, all three
 340 will be assumed.
 341
 342 If --user is not specified, data for all users will be extracted.
 343
 344 --data_dir        is not optional and must define the path to the
 345                   SquirrelMail data directory.  If it is not given,
 346                   this help message is displayed.
 347
 348 --delete          causes the inclusion of SQL statements that remove all
 349                   previous setting values from the database for each user.
 350                   This setting obeys --user, --abook, --pref and --sig.
 351                   This setting is useful when re-importing settings.
 352
 353 --abook           causes the inclusion of SQL statements that import user
 354                   address book data.
 355
 356 --sig             causes the inclusion of SQL statements that import user
 357                   (email) signature data.
 358
 359 --pref            causes the inclusion of SQL statements that import all
 360                   other general user preference data.
 361
 362 --user            can be used to limit the users for which to extract data.
 363                   One or more (comma-separated) usernames can be given.
 364
 365 --abookdb         can be used to specify a custom database name for the
 366                   address book database.  If not given, "squirrelmail"
 367                   is used.
 368
 369 --abook_table     can be used to specify a custom address book table
 370                   name.  If not given, "address" is used.
 371
 372 --abook_owner     can be used to specify a custom field name for the
 373                   "owner" field in the address book database table
 374                   (the username goes in this field).  If not given,
 375                   "owner" is used.
 376
 377 --abook_nickname  can be used to specify a custom field name for the
 378                   "nickname" field in the address book database table.
 379                   If not given, "nickname" is used.
 380
 381 --abook_firstname can be used to specify a custom field name for the
 382                   "firstname" field in the address book database table.
 383                   If not given, "firstname" is used.
 384
 385 --abook_lastname  can be used to specify a custom field name for the
 386                   "lastname" field in the address book database table.
 387                   If not given, "lastname" is used.
 388
 389 --abook_email     can be used to specify a custom field name for the
 390                   email field in the address book database table
 391                   (the actual email address goes in this field).  If
 392                   not given, "email" is used.
 393
 394 --abook_label     can be used to specify a custom field name for the
 395                   "label" field in the address book database table.
 396                   If not given, "label" is used.
 397
 398 --prefdb          can be used to specify a custom database name for the
 399                   user preferences database.  If not given, "squirrelmail"
 400                   is used.
 401
 402 --pref_table      can be used to specify a custom preference table
 403                   name.  If not given, "userprefs" is used.
 404
 405 --pref_user       can be used to specify a custom field name for the
 406                   "user" field in the preferences database table
 407                   (the username goes in this field).  If not given,
 408                   "user" is used.
 409
 410 --pref_key        can be used to specify a custom field name for the
 411                   key field in the preferences database table (the
 412                   preference name goes in this field).  If not given,
 413                   "prefkey" is used.
 414
 415 --pref_value      can be used to specify a custom field name for the
 416                   value field in the preferences database table
 417                   (the preference value goes in this field).  If not
 418                   given, "prefval" is used.
 419
 420 --verbose         Displays extra diagnostic output on STDERR.  If you
 421                   redirect standard output to a file, verbose output
 422                   will not interfere with other normal output.
 423
 424 -v                Same as --verbose.
 425
 426 --help            Displays this usage information.
 427
 428 -h                Same as --help.
 429
 430 EOL
 431   exit 1;
 432 }