#!/usr/bin/perl
+#
+# Converts file-based preferences into SQL statements.
+#
+# WARNING: this script is experimental. We recommend that
+# you not use it when logged in as a privileged user (such
+# as root). Also, ALWAYS back up your data directory before
+# using this script.
+#
+# Copyright (c) 2002, Michael Blandford and Tal Yardeni
+# Copyright (c) 2005-2009 The SquirrelMail Project Team
+#
+# This script is licensed under the GNU Public License (GPL).
+# See: http://opensource.org/licenses/gpl-license.php
+# $Id$
+#
+
+##### Default values #####
+# TODO: expose the database type as a CLI option, but first need more sections in sub escape_sql_string()
+my $dbtype = 'mysql';
+my $abookdb = "squirrelmail";
+my $prefdb = "squirrelmail";
+my $abook_table = "address";
+my $abook_owner="owner";
+my $abook_nickname="nickname";
+my $abook_firstname="firstname";
+my $abook_lastname="lastname";
+my $abook_email="email";
+my $abook_label="label";
+my $pref_table = "userprefs";
+my $pref_user = "user";
+my $pref_key = "prefkey";
+my $pref_value = "prefval";
+##### ##### #####
-# flat2sql.pl v1.0
-#
-# Copyright (c) 2002, Michael Blandford and Tal Yardeni
-
-##### Conf Section #####
+use Getopt::Long;
-$data_dir = "/home/www/mail.topolis.inet/www/squirrelmail-devel-20050911/data";
-$db = "squirrelmail";
-$abook_table = "address";
-$pref_table = "userprefs";
+my (%opts, $verbose, $data_dir);
-##### ##### #####
+&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) );
-use Getopt::Long;
+&Usage if ( defined $opts{h} or defined $opts{help} );
-&GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s ) );
+unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
+ $opts{abook}='TRUE';
+ $opts{pref}='TRUE';
+ $opts{sig}='TRUE';
+}
-&Usage if ( defined $opts{h} or defined $opts{help} );
-unless ( defined $opts{abook} or defined $opts{pref} or
- defined $opts{sig}) {
- $opts{abook}=TRUE;
- $opts{pref}=TRUE;
- $opts{sig}=TRUE;
+if ( defined $opts{verbose} or defined $opts{v} ) {
+ $verbose = 1;
+}
+if ( defined $opts{abookdb} and $opts{abookdb} ) {
+ $abookdb = $opts{abookdb};
+}
+if ( defined $opts{prefdb} and $opts{prefdb} ) {
+ $prefdb = $opts{prefdb};
+}
+if ( defined $opts{pref_table} and $opts{pref_table} ) {
+ $pref_table = $opts{pref_table};
+}
+if ( defined $opts{pref_user} and $opts{pref_user} ) {
+ $pref_user = $opts{pref_user};
+}
+if ( defined $opts{pref_key} and $opts{pref_key} ) {
+ $pref_key = $opts{pref_key};
+}
+if ( defined $opts{pref_value} and $opts{pref_value} ) {
+ $pref_value = $opts{pref_value};
+}
+if ( defined $opts{abook_table} and $opts{abook_table}) {
+ $abook_table = $opts{abook_table};
+}
+if ( defined $opts{abook_owner} and $opts{abook_owner} ) {
+ $abook_owner = $opts{abook_owner};
+}
+if ( defined $opts{abook_nickname} and $opts{abook_nickname} ) {
+ $abook_nickname = $opts{abook_nickname};
+}
+if ( defined $opts{abook_firstname} and $opts{abook_firstname} ) {
+ $abook_firstname = $opts{abook_firstname};
+}
+if ( defined $opts{abook_lastname} and $opts{abook_lastname} ) {
+ $abook_lastname = $opts{abook_lastname};
+}
+if ( defined $opts{abook_email} and $opts{abook_email} ) {
+ $abook_email = $opts{abook_email};
+}
+if ( defined $opts{abook_label} and $opts{abook_label} ) {
+ $abook_label = $opts{abook_label};
}
-# Override the data directory if passed as an argument
-$data_dir = $opts{data_dir} if ( defined $opts{data_dir} );
+# Get data directory option and display help if it is not defined
+if ( defined $opts{data_dir} and $opts{data_dir} ) {
+ $data_dir = $opts{data_dir};
+} else {
+ &Usage;
+}
# Are we looking for specific users or all users?
# There has to be a better way to do this - Below
- @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
+my @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
-# Here we go
-# If no arguments are passed, and we cant open the dir, we should
-# get a usage.
-opendir(DIR, $data_dir) or
- die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
+inspect_files($data_dir);
-while ( $filename = readdir DIR ) {
- next if ( $filename eq "." or $filename eq ".." );
- $filename =~ /(.*)\.(.*)/;
- $username = $1;
+# All done. Below are functions
- # Deal with the people
- # There has to be a better way to do this - Above
- next if ( defined $opts{user} and grep(!/$username/, @user_list));
- # Deal with the extension files
- $ext = $2;
- next unless $ext;
- &abook if ( $ext eq "abook" and defined $opts{abook} );
- &pref if ( $ext eq "pref" and defined $opts{pref} );
- &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
-}
-closedir ( DIR );
+# Finds needed user files in the given directory
+# and recurses any nested data directories as needed
+#
+sub inspect_files {
-# All done. Below are functions
+ my ($data_dir) = @_;
+ my ($filename, $username, $ext);
+ local *DIR;
+
+ # Here we go
+ # If no arguments are passed, and we cant open the dir, we should
+ # get a usage.
+ opendir(DIR, $data_dir) or
+ die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
+
+ while ( $filename = readdir DIR ) {
+
+ next if ( $filename eq "." or $filename eq ".." );
+
+ if ($verbose) { print STDERR "; INSPECTING: $data_dir/$filename\n"; }
+
+ # recurse into nested (hashed) directories
+ #
+ if ($filename =~ /^[0123456789abcdef]$/ && -d "$data_dir/$filename") {
+ inspect_files("$data_dir/$filename");
+ }
+
+ next unless $filename =~ /(.*)\.(.*)/;
+ $username = $1;
+ next unless $username;
+
+ # Deal with the people
+ # There has to be a better way to do this - Above
+ next if (defined $opts{user} and !grep($username eq $_, @user_list));
+
+ # Deal with the extension files
+ $ext = $2;
+ next unless $ext;
+
+ &abook("$data_dir/$filename", $username)
+ if ( $ext eq "abook" and defined $opts{abook} );
+ &pref("$data_dir/$filename", $username)
+ if ( $ext eq "pref" and defined $opts{pref} );
+ &sig("$data_dir/$filename", $username)
+ if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
+ }
+ closedir ( DIR );
+
+}
# Process a user address file
sub abook {
- print "DELETE FROM $db.$abook_table WHERE owner = '$username;\n"
- if ( defined $opts{delete} );
- open(ABOOK, ">$data_dir/$filename") or
- die "FILE READ ERROR: Could not open $filename!!\n";
+ my ($filepath, $username) = @_;
+
+ if ($verbose) { print STDERR "; PARSING ADDRESS BOOK DATA FROM: $filepath\n"; }
+
+ if ( defined $opts{delete} ) {
+ print "DELETE FROM $abookdb.$abook_table WHERE $abook_owner = '"
+ . escape_sql_string($username,'TRUE')
+ . "';\n"
+ }
+
+ open(ABOOK, "<$filepath") or
+ die "FILE READ ERROR: Could not open $filepath!!\n";
while (my $line = <ABOOK> ) {
chomp $line;
my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
- print "INSERT INTO $db.$abook_table "
- . "(owner,nickname,firstname,lastname,email,label) "
- . "VALUES ('$username','$nickname','$firstname','$lastname',"
- . "'$email','$label');\n";
+ print "INSERT INTO $abookdb.$abook_table "
+ . "($abook_owner, $abook_nickname, $abook_firstname, $abook_lastname, $abook_email, $abook_label) "
+ . "VALUES ('"
+ . escape_sql_string($username) . "', '"
+ . escape_sql_string($nickname) . "', '"
+ . escape_sql_string($firstname) . "', '"
+ . escape_sql_string($lastname) . "', '"
+ . escape_sql_string($email) . "', '"
+ . escape_sql_string($label) . "');\n";
}
close(ABOOK);
}
-# Process a user prefernce file
-
+# Process a user preference file
sub pref {
- print "DELETE FROM $db.$pref_table "
- . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
- if ( defined $opts{delete} );
- open(PREFS, "<$data_dir/$filename") or
- die "FILE READ ERROR: Could not open $filename!!\n";
+ my ($filepath, $username) = @_;
+
+ if ($verbose) { print STDERR "; PARSING PREFERENCE DATA FROM: $filepath\n"; }
+
+ if ( defined $opts{delete} ) {
+ print "DELETE FROM $prefdb.$pref_table "
+ . "WHERE $pref_user = '"
+ . escape_sql_string($username,'TRUE')
+ . "' AND $pref_key NOT LIKE '\\_\\_\\_sig%\\_\\_\\_';\n"
+ }
+
+ open(PREFS, "<$filepath") or
+ die "FILE READ ERROR: Could not open $filepath!!\n";
while (my $line = <PREFS> ) {
chomp $line;
- my ( $prefkey, $prefval ) = split(/=/, $line);
+ my ( $prefkey, $prefval ) = split(/=/, $line, 2);
- print "INSERT INTO $db.$pref_table "
- . "(user,prefkey,prefval) "
- . "VALUES ('$username','$prefkey','$prefval');\n";
+ print "INSERT INTO $prefdb.$pref_table "
+ . "($pref_user, $pref_key, $pref_value) "
+ . "VALUES ('"
+ . escape_sql_string($username) . "', '"
+ . escape_sql_string($prefkey) . "', '"
+ . escape_sql_string($prefval) . "');\n";
}
close(PREFS);
}
-# Process a user sig file
-
+# Process a user signature file
sub sig {
- $del_ext = $1;
+ my ($filepath, $username) = @_;
+
+ if ($verbose) { print STDERR "; PARSING SIGNATURE DATA FROM: $filepath\n"; }
+
+ my $del_ext = $1;
$del_ext = "nature" if ( $del_ext eq "g" );
- print "DELETE FROM $db.$pref_table "
- . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
- if ( defined $opts{delete} );
- open(SIG, "<$data_dir/$filename") or
- die "FILE READ ERROR: Could not open $filename!!\n";
+ if ( defined $opts{delete} ) {
+ print "DELETE FROM $prefdb.$pref_table "
+ . "WHERE $pref_user = '"
+ . escape_sql_string($username,'TRUE')
+ . "' AND $pref_key = '___sig"
+ . escape_sql_string($del_ext,'TRUE')
+ . "___';\n"
+ }
+
+ open(SIG, "<$filepath") or
+ die "FILE READ ERROR: Could not open $filepath!!\n";
my @lines = <SIG>;
close(SIG);
- $filename =~ /.*\.si([g,\d]$)/;
- $prefkey = "___sig";
+ $filepath =~ /.*\.si([g,\d])$/;
+ my $prefkey = "___sig";
if ( $1 eq "g" ) {
$prefkey .= "nature___";
} else {
$prefkey .= "$1___";
}
- print "INSERT INTO $db.$sig_table (user,prefkey,prefval) "
- . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
+ print "INSERT INTO $prefdb.$pref_table ($pref_user, $pref_key, $pref_value) "
+ . "VALUES ('"
+ . escape_sql_string($username) . "', '"
+ . escape_sql_string($prefkey) . "', '"
+ . escape_sql_string(join("", @lines)) . "');\n";
}
-# Print out the usage screen
+# Escapes sql strings
+# MySQL escaping:
+# http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
+# full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z)
+# add % and _ in pattern matching expressions.
+# short - only character used for quoting and backslash should be escaped
+# PostgreSQL
+# Oracle
+# Sybase - different quoting of '
+sub escape_sql_string() {
+ my ($str,$isPattern) = @_;
+
+ if ($dbtype eq 'mysql'){
+ # escape \, ' and "
+ $str =~ s/(['"\\])/\\$1/g;
+ # escape \x1a
+ $str =~ s/([\x1a])/\\Z/g;
+ # escape ascii null
+ $str =~ s/([\x00])/\\0/g;
+ # escape line feed
+ $str =~ s/([\n])/\\n/g;
+ # escape cr
+ $str =~ s/([\r])/\\r/g;
+ if ($isPattern) {
+ $str =~ s/([%_])/\\$1/g;
+ }
+ } else {
+ die "ERROR: Unsupported database type";
+ }
+ return $str;
+}
+
+# Print out the usage screen
sub Usage {
$0 =~ /.*\/(.*)/;
-$prog = $1;
+my $prog = $1;
print <<EOL;
-This program generates SQL statements to aid importing squirrelmail
-user config into a database.
-
-Usage: $prog [--delete] [--abook] [--pref] [--sig] [--data_dir=<>] [--user=<username0[,username1[,username2]...]]
-
-Prefs --abook, --pref, and --sig are assumed if none of them as passed
-
---delete removes all previous values for users ( --users=<> ) already in
-the database. This is useful to reimport users.
-It respects --abook, --pref, and --sig.
-If --user is not specified, it will try to do all users.
+This program generates SQL statements that aid in importing
+SquirrelMail user configuration settings from files to
+those in a database.
+
+WARNING: this script is experimental. We recommend that
+you not use it when logged in as a privileged user (such
+as root). Also, ALWAYS back up your data directory before
+using this script.
+
+Usage: $prog --data_dir=<path to data directory> \\
+ [--delete] \\
+ [--abook] [--sig] [--pref] \\
+ [--user=<username1[,username2[,username3]...]>] \\
+ [--abookdb=<database>] \\
+ [--abook_table=<table name>] \\
+ [--abook_owner=<field name>] \\
+ [--abook_nickname=<field name>] \\
+ [--abook_firstname=<field name>] \\
+ [--abook_lastname=<field name>] \\
+ [--abook_email=<field name>] \\
+ [--abook_label=<field name>] \\
+ [--prefdb=<database>] \\
+ [--pref_table=<table name>] \\
+ [--pref_user=<field name>] \\
+ [--pref_key=<field name>] \\
+ [--pref_value=<field name>] \\
+ [--verbose] [-v]
+ [--help] [-h]
+
+When none of --abook, --sig or --pref is specified, all three
+will be assumed.
+
+If --user is not specified, data for all users will be extracted.
+
+--data_dir is not optional and must define the path to the
+ SquirrelMail data directory. If it is not given,
+ this help message is displayed.
+
+--delete causes the inclusion of SQL statements that remove all
+ previous setting values from the database for each user.
+ This setting obeys --user, --abook, --pref and --sig.
+ This setting is useful when re-importing settings.
+
+--abook causes the inclusion of SQL statements that import user
+ address book data.
+
+--sig causes the inclusion of SQL statements that import user
+ (email) signature data.
+
+--pref causes the inclusion of SQL statements that import all
+ other general user preference data.
+
+--user can be used to limit the users for which to extract data.
+ One or more (comma-separated) usernames can be given.
+
+--abookdb can be used to specify a custom database name for the
+ address book database. If not given, "squirrelmail"
+ is used.
+
+--abook_table can be used to specify a custom address book table
+ name. If not given, "address" is used.
+
+--abook_owner can be used to specify a custom field name for the
+ "owner" field in the address book database table
+ (the username goes in this field). If not given,
+ "owner" is used.
+
+--abook_nickname can be used to specify a custom field name for the
+ "nickname" field in the address book database table.
+ If not given, "nickname" is used.
+
+--abook_firstname can be used to specify a custom field name for the
+ "firstname" field in the address book database table.
+ If not given, "firstname" is used.
+
+--abook_lastname can be used to specify a custom field name for the
+ "lastname" field in the address book database table.
+ If not given, "lastname" is used.
+
+--abook_email can be used to specify a custom field name for the
+ email field in the address book database table
+ (the actual email address goes in this field). If
+ not given, "email" is used.
+
+--abook_label can be used to specify a custom field name for the
+ "label" field in the address book database table.
+ If not given, "label" is used.
+
+--prefdb can be used to specify a custom database name for the
+ user preferences database. If not given, "squirrelmail"
+ is used.
+
+--pref_table can be used to specify a custom preference table
+ name. If not given, "userprefs" is used.
+
+--pref_user can be used to specify a custom field name for the
+ "user" field in the preferences database table
+ (the username goes in this field). If not given,
+ "user" is used.
+
+--pref_key can be used to specify a custom field name for the
+ key field in the preferences database table (the
+ preference name goes in this field). If not given,
+ "prefkey" is used.
+
+--pref_value can be used to specify a custom field name for the
+ value field in the preferences database table
+ (the preference value goes in this field). If not
+ given, "prefval" is used.
+
+--verbose Displays extra diagnostic output on STDERR. If you
+ redirect standard output to a file, verbose output
+ will not interfere with other normal output.
+
+-v Same as --verbose.
+
+--help Displays this usage information.
+
+-h Same as --help.
EOL
exit 1;
projects / squirrelmail.git / blobdiff