Port Thijs fix (rev.13790) to DEVEL: no words must be an empty array, not a string...
[squirrelmail.git] / contrib / flat2sql.pl
CommitLineData
209b9127 1#!/usr/bin/perl
c47b981d 2#
980c6231 3# Converts file-based preferences into SQL statements.
208870cf 4#
980c6231 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.
208870cf 9#
c47b981d 10# Copyright (c) 2002, Michael Blandford and Tal Yardeni
980c6231 11# Copyright (c) 2005-2009 The SquirrelMail Project Team
0575fe8b 12#
980c6231 13# This script is licensed under the GNU Public License (GPL).
14# See: http://opensource.org/licenses/gpl-license.php
c47b981d 15# $Id$
208870cf 16#
209b9127 17
208870cf 18##### Default values #####
980c6231 19# TODO: expose the database type as a CLI option, but first need more sections in sub escape_sql_string()
20my $dbtype = 'mysql';
21my $abookdb = "squirrelmail";
22my $prefdb = "squirrelmail";
23my $abook_table = "address";
24my $abook_owner="owner";
25my $abook_nickname="nickname";
26my $abook_firstname="firstname";
27my $abook_lastname="lastname";
28my $abook_email="email";
29my $abook_label="label";
30my $pref_table = "userprefs";
31my $pref_user = "user";
32my $pref_key = "prefkey";
33my $pref_value = "prefval";
209b9127 34##### ##### #####
35
36use Getopt::Long;
37
980c6231 38my (%opts, $verbose, $data_dir);
209b9127 39
980c6231 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} );
209b9127 43
c47b981d 44unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
980c6231 45 $opts{abook}='TRUE';
46 $opts{pref}='TRUE';
47 $opts{sig}='TRUE';
209b9127 48}
49
208870cf 50
980c6231 51if ( defined $opts{verbose} or defined $opts{v} ) {
52 $verbose = 1;
53}
54if ( defined $opts{abookdb} and $opts{abookdb} ) {
55 $abookdb = $opts{abookdb};
56}
57if ( defined $opts{prefdb} and $opts{prefdb} ) {
58 $prefdb = $opts{prefdb};
208870cf 59}
60if ( defined $opts{pref_table} and $opts{pref_table} ) {
61 $pref_table = $opts{pref_table};
62}
980c6231 63if ( defined $opts{pref_user} and $opts{pref_user} ) {
64 $pref_user = $opts{pref_user};
65}
66if ( defined $opts{pref_key} and $opts{pref_key} ) {
67 $pref_key = $opts{pref_key};
68}
69if ( defined $opts{pref_value} and $opts{pref_value} ) {
70 $pref_value = $opts{pref_value};
71}
208870cf 72if ( defined $opts{abook_table} and $opts{abook_table}) {
73 $abook_table = $opts{abook_table};
74}
980c6231 75if ( defined $opts{abook_owner} and $opts{abook_owner} ) {
76 $abook_owner = $opts{abook_owner};
77}
78if ( defined $opts{abook_nickname} and $opts{abook_nickname} ) {
79 $abook_nickname = $opts{abook_nickname};
80}
81if ( defined $opts{abook_firstname} and $opts{abook_firstname} ) {
82 $abook_firstname = $opts{abook_firstname};
83}
84if ( defined $opts{abook_lastname} and $opts{abook_lastname} ) {
85 $abook_lastname = $opts{abook_lastname};
86}
87if ( defined $opts{abook_email} and $opts{abook_email} ) {
88 $abook_email = $opts{abook_email};
89}
90if ( defined $opts{abook_label} and $opts{abook_label} ) {
91 $abook_label = $opts{abook_label};
92}
208870cf 93
94# Get data directory option and display help if it is not defined
95if ( defined $opts{data_dir} and $opts{data_dir} ) {
96 $data_dir = $opts{data_dir};
97} else {
98 &Usage;
99}
209b9127 100
101# Are we looking for specific users or all users?
102# There has to be a better way to do this - Below
980c6231 103my @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
104
105inspect_files($data_dir);
209b9127 106
980c6231 107# All done. Below are functions
209b9127 108
209b9127 109
980c6231 110# Finds needed user files in the given directory
111# and recurses any nested data directories as needed
112#
113sub inspect_files {
209b9127 114
980c6231 115 my ($data_dir) = @_;
116 my ($filename, $username, $ext);
117 local *DIR;
209b9127 118
980c6231 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}
209b9127 159
160# Process a user address file
161
162sub abook {
209b9127 163
980c6231 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";
209b9127 176
177 while (my $line = <ABOOK> ) {
178
179 chomp $line;
180 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
181
980c6231 182 print "INSERT INTO $abookdb.$abook_table "
183 . "($abook_owner, $abook_nickname, $abook_firstname, $abook_lastname, $abook_email, $abook_label) "
1c6b3a71 184 . "VALUES ('"
980c6231 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";
209b9127 191 }
192
193 close(ABOOK);
194}
195
1c6b3a71 196# Process a user preference file
209b9127 197sub pref {
209b9127 198
980c6231 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";
209b9127 212
213 while (my $line = <PREFS> ) {
214
215 chomp $line;
980c6231 216 my ( $prefkey, $prefval ) = split(/=/, $line, 2);
209b9127 217
980c6231 218 print "INSERT INTO $prefdb.$pref_table "
219 . "($pref_user, $pref_key, $pref_value) "
1c6b3a71 220 . "VALUES ('"
980c6231 221 . escape_sql_string($username) . "', '"
222 . escape_sql_string($prefkey) . "', '"
223 . escape_sql_string($prefval) . "');\n";
209b9127 224
225 }
226
227 close(PREFS);
228}
229
1c6b3a71 230# Process a user signature file
209b9127 231sub sig {
232
980c6231 233 my ($filepath, $username) = @_;
234
235 if ($verbose) { print STDERR "; PARSING SIGNATURE DATA FROM: $filepath\n"; }
236
237 my $del_ext = $1;
209b9127 238 $del_ext = "nature" if ( $del_ext eq "g" );
209b9127 239
980c6231 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";
209b9127 251
252 my @lines = <SIG>;
253 close(SIG);
254
980c6231 255 $filepath =~ /.*\.si([g,\d])$/;
256 my $prefkey = "___sig";
209b9127 257 if ( $1 eq "g" ) {
258 $prefkey .= "nature___";
259 } else {
260 $prefkey .= "$1___";
261 }
262
980c6231 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";
209b9127 268}
269
1c6b3a71 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 '
279sub 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
980c6231 288 $str =~ s/([\x00])/\\0/g;
1c6b3a71 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}
209b9127 301
1c6b3a71 302
303# Print out the usage screen
209b9127 304sub Usage {
305
306$0 =~ /.*\/(.*)/;
980c6231 307my $prog = $1;
209b9127 308 print <<EOL;
208870cf 309
980c6231 310This program generates SQL statements that aid in importing
311SquirrelMail user configuration settings from files to
312those in a database.
313
314WARNING: this script is experimental. We recommend that
315you not use it when logged in as a privileged user (such
316as root). Also, ALWAYS back up your data directory before
317using this script.
318
319Usage: $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
339When none of --abook, --sig or --pref is specified, all three
340will be assumed.
341
342If --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.
209b9127 429
430EOL
431 exit 1;
432}