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