2b02e4e65b98605398068d9ea86d5807a910db8f
[squirrelmail.git] / contrib / flat2sql.pl
1 #!/usr/bin/perl
2 #
3 # Copyright (c) 2002, Michael Blandford and Tal Yardeni
4 # Copyright (c) 2005, The SquirrelMail Project Team
5 #
6 # This script is licensed under GPL.
7 # $Id$
8 ##### Conf Section #####
9
10 $data_dir = "/var/local/squirrelmail/data";
11 $db = "squirrelmail";
12 $abook_table = "address";
13 $pref_table = "userprefs";
14
15 ##### ##### #####
16
17 use Getopt::Long;
18
19 &GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s ) );
20
21 &Usage if ( defined $opts{h} or defined $opts{help} );
22
23 unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
24 $opts{abook}=TRUE;
25 $opts{pref}=TRUE;
26 $opts{sig}=TRUE;
27 }
28
29 # Override the data directory if passed as an argument
30 $data_dir = $opts{data_dir} if ( defined $opts{data_dir} );
31
32 # Are we looking for specific users or all users?
33 # There has to be a better way to do this - Below
34 @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
35
36 # Here we go
37 # If no arguments are passed, and we cant open the dir, we should
38 # get a usage.
39 opendir(DIR, $data_dir) or
40 die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
41
42 while ( $filename = readdir DIR ) {
43 next if ( $filename eq "." or $filename eq ".." );
44 $filename =~ /(.*)\.(.*)/;
45 $username = $1;
46
47 # Deal with the people
48 # There has to be a better way to do this - Above
49 next if ( defined $opts{user} and grep(!/$username/, @user_list));
50
51 # Deal with the extension files
52 $ext = $2;
53 next unless $ext;
54 &abook if ( $ext eq "abook" and defined $opts{abook} );
55 &pref if ( $ext eq "pref" and defined $opts{pref} );
56 &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
57 }
58 closedir ( DIR );
59
60 # All done. Below are functions
61
62 # Process a user address file
63
64 sub abook {
65 print "DELETE FROM $db.$abook_table WHERE owner = '$username;\n"
66 if ( defined $opts{delete} );
67
68 open(ABOOK, "<$data_dir/$filename") or
69 die "FILE READ ERROR: Could not open $filename!!\n";
70
71 while (my $line = <ABOOK> ) {
72
73 chomp $line;
74 my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
75
76 print "INSERT INTO $db.$abook_table "
77 . "(owner,nickname,firstname,lastname,email,label) "
78 . "VALUES ('$username','$nickname','$firstname','$lastname',"
79 . "'$email','$label');\n";
80 }
81
82 close(ABOOK);
83 }
84
85 # Process a user prefernce file
86
87 sub pref {
88 print "DELETE FROM $db.$pref_table "
89 . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
90 if ( defined $opts{delete} );
91
92 open(PREFS, "<$data_dir/$filename") or
93 die "FILE READ ERROR: Could not open $filename!!\n";
94
95 while (my $line = <PREFS> ) {
96
97 chomp $line;
98 my ( $prefkey, $prefval ) = split(/=/, $line);
99
100 print "INSERT INTO $db.$pref_table "
101 . "(user,prefkey,prefval) "
102 . "VALUES ('$username','$prefkey','$prefval');\n";
103
104 }
105
106 close(PREFS);
107 }
108
109 # Process a user sig file
110
111 sub sig {
112
113 $del_ext = $1;
114 $del_ext = "nature" if ( $del_ext eq "g" );
115 print "DELETE FROM $db.$pref_table "
116 . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
117 if ( defined $opts{delete} );
118
119 open(SIG, "<$data_dir/$filename") or
120 die "FILE READ ERROR: Could not open $filename!!\n";
121
122 my @lines = <SIG>;
123 close(SIG);
124
125 $filename =~ /.*\.si([g,\d]$)/;
126 $prefkey = "___sig";
127 if ( $1 eq "g" ) {
128 $prefkey .= "nature___";
129 } else {
130 $prefkey .= "$1___";
131 }
132
133 print "INSERT INTO $db.$sig_table (user,prefkey,prefval) "
134 . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
135 }
136
137 # Print out the usage screen
138
139 sub Usage {
140
141 $0 =~ /.*\/(.*)/;
142 $prog = $1;
143 print <<EOL;
144 This program generates SQL statements to aid importing squirrelmail
145 user config into a database.
146
147 Usage: $prog [--delete] [--abook] [--pref] [--sig] [--data_dir=<>] [--user=<username0[,username1[,username2]...]]
148
149 Prefs --abook, --pref, and --sig are assumed if none of them as passed
150
151 --delete removes all previous values for users ( --users=<> ) already in
152 the database. This is useful to reimport users.
153 It respects --abook, --pref, and --sig.
154
155 If --user is not specified, it will try to do all users.
156
157 EOL
158 exit 1;
159 }