209b9127 |
1 | #!/usr/bin/perl |
2 | |
3 | # flat2sql.pl v1.0 |
4 | # |
5 | # Copyright (c) 2002, Michael Blandford and Tal Yardeni |
0575fe8b |
6 | # |
7 | # This script is licensed under GPL. |
209b9127 |
8 | ##### Conf Section ##### |
9 | |
65d0d162 |
10 | $data_dir = "/var/local/squirrelmail/data"; |
209b9127 |
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 | } |