3 # Converts file based preferences into SQL statements.
5 # WARNING: this script is experimental. Don't use it as
6 # privileged user or backup your data directory before using it.
8 # Copyright (c) 2002, Michael Blandford and Tal Yardeni
9 # Copyright (c) 2005-2006 The SquirrelMail Project Team
11 # This script is licensed under GPL.
15 ##### Default values #####
17 $abook_table = "address";
18 $pref_table = "userprefs";
24 &GetOptions
( \
%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) );
26 &Usage
if ( defined $opts{h
} or defined $opts{help
} );
28 unless ( defined $opts{abook
} or defined $opts{pref
} or defined $opts{sig
}) {
35 if ( defined $opts{db
} and $opts{db
} ) {
38 if ( defined $opts{pref_table
} and $opts{pref_table
} ) {
39 $pref_table = $opts{pref_table
};
41 if ( defined $opts{abook_table
} and $opts{abook_table
}) {
42 $abook_table = $opts{abook_table
};
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
};
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
};
57 # If no arguments are passed, and we cant open the dir, we should
59 opendir(DIR
, $data_dir) or
60 die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
62 while ( $filename = readdir DIR
) {
63 next if ( $filename eq "." or $filename eq ".." );
64 $filename =~ /(.*)\.(.*)/;
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));
71 # Deal with the extension files
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
});
80 # All done. Below are functions
82 # Process a user address file
85 print "DELETE FROM $db.$abook_table WHERE owner = '".escape_sql_string
($username,true
)."';\n"
86 if ( defined $opts{delete} );
88 open(ABOOK
, "<$data_dir/$filename") or
89 die "FILE READ ERROR: Could not open $filename!!\n";
91 while (my $line = <ABOOK
> ) {
94 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
96 print "INSERT INTO $db.$abook_table "
97 . "(owner,nickname,firstname,lastname,email,label) "
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";
110 # Process a user preference file
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} );
116 open(PREFS
, "<$data_dir/$filename") or
117 die "FILE READ ERROR: Could not open $filename!!\n";
119 while (my $line = <PREFS
> ) {
122 my ( $prefkey, $prefval ) = split(/=/, $line);
124 print "INSERT INTO $db.$pref_table "
125 . "(user,prefkey,prefval) "
127 .escape_sql_string
($username)."','"
128 .escape_sql_string
($prefkey)."','"
129 .escape_sql_string
($prefval)."');\n";
136 # Process a user signature file
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} );
145 open(SIG
, "<$data_dir/$filename") or
146 die "FILE READ ERROR: Could not open $filename!!\n";
151 $filename =~ /.*\.si([g,\d]$)/;
154 $prefkey .= "nature___";
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";
165 # Escapes sql strings
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
173 # Sybase - different quoting of '
174 sub escape_sql_string
() {
175 my ($str,$isPattern) = @_;
177 if ($dbtype eq 'mysql'){
179 $str =~ s/(['"\\])/\\$1/g;
181 $str =~ s/([\x1a])/\\Z/g;
183 $str =~ s/([\x0])/\\0/g;
185 $str =~ s/([\n])/\\n/g;
187 $str =~ s/([\r])/\\r/g;
189 $str =~ s/([%_])/\\$1/g;
192 die "ERROR: Unsupported database type";
198 # Print out the usage screen
205 This program generates SQL statements to aid importing SquirrelMail
206 user config into a database.
208 WARNING: this script is experimental. Don't use it as
209 privileged user or backup your data directory before using it.
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>]
215 --data_dir option must define path to SquirrelMail data directory. If
216 option is not defined, script displays this help message.
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.
222 --db option can be used to set database name. Script defaults to
225 --pref_table option can be used to set preference table name. Script
226 defaults to 'userprefs'.
228 --abook_table option can be used to set address book table name. Script
229 defaults to 'address'.
231 Prefs --abook, --pref, and --sig are assumed if none of them as passed
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.
237 If --user is not specified, script extracts all user data.