Fixed first message losing correct URI
[squirrelmail.git] / contrib / flat2sql.pl
CommitLineData
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";
209b9127 19##### ##### #####
20
21use Getopt::Long;
22
208870cf 23&GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s db:s pref_table:s abook_table:s) );
209b9127 24
25&Usage if ( defined $opts{h} or defined $opts{help} );
26
c47b981d 27unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
28 $opts{abook}=TRUE;
29 $opts{pref}=TRUE;
30 $opts{sig}=TRUE;
209b9127 31}
32
208870cf 33
34if ( defined $opts{db} and $opts{db} ) {
35 $db = $opts{db};
36}
37if ( defined $opts{pref_table} and $opts{pref_table} ) {
38 $pref_table = $opts{pref_table};
39}
40if ( defined $opts{abook_table} and $opts{abook_table}) {
41 $abook_table = $opts{abook_table};
42}
43
44# Get data directory option and display help if it is not defined
45if ( defined $opts{data_dir} and $opts{data_dir} ) {
46 $data_dir = $opts{data_dir};
47} else {
48 &Usage;
49}
209b9127 50
51# Are we looking for specific users or all users?
52# There has to be a better way to do this - Below
53 @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
54
55# Here we go
56# If no arguments are passed, and we cant open the dir, we should
57# get a usage.
58opendir(DIR, $data_dir) or
59 die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
60
61while ( $filename = readdir DIR ) {
62 next if ( $filename eq "." or $filename eq ".." );
63 $filename =~ /(.*)\.(.*)/;
64 $username = $1;
65
66 # Deal with the people
67 # There has to be a better way to do this - Above
68 next if ( defined $opts{user} and grep(!/$username/, @user_list));
69
70 # Deal with the extension files
71 $ext = $2;
72 next unless $ext;
73 &abook if ( $ext eq "abook" and defined $opts{abook} );
74 &pref if ( $ext eq "pref" and defined $opts{pref} );
75 &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
76}
77closedir ( DIR );
78
79# All done. Below are functions
80
81# Process a user address file
82
83sub abook {
208870cf 84 print "DELETE FROM $db.$abook_table WHERE owner = '$username';\n"
209b9127 85 if ( defined $opts{delete} );
86
c47b981d 87 open(ABOOK, "<$data_dir/$filename") or
209b9127 88 die "FILE READ ERROR: Could not open $filename!!\n";
89
90 while (my $line = <ABOOK> ) {
91
92 chomp $line;
93 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
94
95 print "INSERT INTO $db.$abook_table "
96 . "(owner,nickname,firstname,lastname,email,label) "
97 . "VALUES ('$username','$nickname','$firstname','$lastname',"
98 . "'$email','$label');\n";
99 }
100
101 close(ABOOK);
102}
103
104# Process a user prefernce file
105
106sub pref {
107 print "DELETE FROM $db.$pref_table "
108 . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
109 if ( defined $opts{delete} );
110
111 open(PREFS, "<$data_dir/$filename") or
112 die "FILE READ ERROR: Could not open $filename!!\n";
113
114 while (my $line = <PREFS> ) {
115
116 chomp $line;
117 my ( $prefkey, $prefval ) = split(/=/, $line);
118
119 print "INSERT INTO $db.$pref_table "
120 . "(user,prefkey,prefval) "
121 . "VALUES ('$username','$prefkey','$prefval');\n";
122
123 }
124
125 close(PREFS);
126}
127
128# Process a user sig file
129
130sub sig {
131
132 $del_ext = $1;
133 $del_ext = "nature" if ( $del_ext eq "g" );
134 print "DELETE FROM $db.$pref_table "
135 . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
136 if ( defined $opts{delete} );
137
138 open(SIG, "<$data_dir/$filename") or
139 die "FILE READ ERROR: Could not open $filename!!\n";
140
141 my @lines = <SIG>;
142 close(SIG);
143
144 $filename =~ /.*\.si([g,\d]$)/;
145 $prefkey = "___sig";
146 if ( $1 eq "g" ) {
147 $prefkey .= "nature___";
148 } else {
149 $prefkey .= "$1___";
150 }
151
208870cf 152 print "INSERT INTO $db.$pref_table (user,prefkey,prefval) "
209b9127 153 . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
154}
155
156# Print out the usage screen
157
158sub Usage {
159
160$0 =~ /.*\/(.*)/;
161$prog = $1;
162 print <<EOL;
208870cf 163
164This program generates SQL statements to aid importing SquirrelMail
209b9127 165user config into a database.
166
208870cf 167WARNING: this script is experimental. Don't use it as
168privileged user or backup your data directory before using it.
169
170Usage: $prog --data_dir=<data_dir> [--delete] [--abook] [--pref] [--sig]
171 [--user=<username0[,username1[,username2]...]]
172 [--db=<database>] [--pref_table=<userprefs>] [--abook_table=<address>]
173
174--data_dir option must define path to SquirrelMail data directory. If
175option is not defined, script displays this help message.
176
177--abook option is used to generate SQL with address books.
178--pref option is used to generate SQL with user preferences.
179--sig option is used to generate SQL with signatures.
180
181--db option can be used to set database name. Script defaults to
182'squirrelmail'.
183
184--pref_table option can be used to set preference table name. Script
185defaults to 'userprefs'.
186
187--abook_table option can be used to set address book table name. Script
188defaults to 'address'.
209b9127 189
190Prefs --abook, --pref, and --sig are assumed if none of them as passed
191
192--delete removes all previous values for users ( --users=<> ) already in
193the database. This is useful to reimport users.
194It respects --abook, --pref, and --sig.
195
208870cf 196If --user is not specified, script extracts all user data.
209b9127 197
198EOL
199 exit 1;
200}