Correct value of undefined hostname
[squirrelmail.git] / 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 }