209b9127 |
1 | #!/usr/bin/perl |
c47b981d |
2 | # |
208870cf |
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 | # |
c47b981d |
8 | # Copyright (c) 2002, Michael Blandford and Tal Yardeni |
9 | # Copyright (c) 2005, The SquirrelMail Project Team |
0575fe8b |
10 | # |
11 | # This script is licensed under GPL. |
c47b981d |
12 | # $Id$ |
208870cf |
13 | # |
209b9127 |
14 | |
208870cf |
15 | ##### Default values ##### |
209b9127 |
16 | $db = "squirrelmail"; |
17 | $abook_table = "address"; |
18 | $pref_table = "userprefs"; |
1c6b3a71 |
19 | $dbtype = 'mysql'; |
209b9127 |
20 | ##### ##### ##### |
21 | |
22 | use Getopt::Long; |
23 | |
208870cf |
24 | &GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) ); |
209b9127 |
25 | |
26 | &Usage if ( defined $opts{h} or defined $opts{help} ); |
27 | |
c47b981d |
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; |
209b9127 |
32 | } |
33 | |
208870cf |
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 | } |
209b9127 |
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 { |
1c6b3a71 |
85 | print "DELETE FROM $db.$abook_table WHERE owner = '".escape_sql_string($username,true)."';\n" |
209b9127 |
86 | if ( defined $opts{delete} ); |
87 | |
c47b981d |
88 | open(ABOOK, "<$data_dir/$filename") or |
209b9127 |
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) " |
1c6b3a71 |
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"; |
209b9127 |
105 | } |
106 | |
107 | close(ABOOK); |
108 | } |
109 | |
1c6b3a71 |
110 | # Process a user preference file |
209b9127 |
111 | sub pref { |
112 | print "DELETE FROM $db.$pref_table " |
1c6b3a71 |
113 | . "WHERE user = '".escape_sql_string($username,true)."' and prefkey not like '___sig\%___';\n" |
209b9127 |
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) " |
1c6b3a71 |
126 | . "VALUES ('" |
127 | .escape_sql_string($username)."','" |
128 | .escape_sql_string($prefkey)."','" |
129 | .escape_sql_string($prefval)."');\n"; |
209b9127 |
130 | |
131 | } |
132 | |
133 | close(PREFS); |
134 | } |
135 | |
1c6b3a71 |
136 | # Process a user signature file |
209b9127 |
137 | sub sig { |
138 | |
139 | $del_ext = $1; |
140 | $del_ext = "nature" if ( $del_ext eq "g" ); |
141 | print "DELETE FROM $db.$pref_table " |
1c6b3a71 |
142 | . "WHERE user = '".escape_sql_string($username,true)."' and prefkey like '___sig" . escape_sql_string($del_ext,true) . "___';\n" |
209b9127 |
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 | |
208870cf |
159 | print "INSERT INTO $db.$pref_table (user,prefkey,prefval) " |
1c6b3a71 |
160 | . "VALUES ('".escape_sql_string($username)."','" |
161 | .escape_sql_string($prefkey)."','" |
162 | .escape_sql_string(join("", @lines))."');\n"; |
209b9127 |
163 | } |
164 | |
1c6b3a71 |
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 | } |
209b9127 |
196 | |
1c6b3a71 |
197 | |
198 | # Print out the usage screen |
209b9127 |
199 | sub Usage { |
200 | |
201 | $0 =~ /.*\/(.*)/; |
202 | $prog = $1; |
203 | print <<EOL; |
208870cf |
204 | |
205 | This program generates SQL statements to aid importing SquirrelMail |
209b9127 |
206 | user config into a database. |
207 | |
208870cf |
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'. |
209b9127 |
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 | |
208870cf |
237 | If --user is not specified, script extracts all user data. |
209b9127 |
238 | |
239 | EOL |
240 | exit 1; |
241 | } |