flat2sql.pl 4.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159
  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. $data_dir = "/var/local/squirrelmail/data";
  10. $db = "squirrelmail";
  11. $abook_table = "address";
  12. $pref_table = "userprefs";
  13. ##### ##### #####
  14. use Getopt::Long;
  15. &GetOptions( \%opts, qw( abook data_dir:s delete h help pref sig user:s ) );
  16. &Usage if ( defined $opts{h} or defined $opts{help} );
  17. unless ( defined $opts{abook} or defined $opts{pref} or defined $opts{sig}) {
  18. $opts{abook}=TRUE;
  19. $opts{pref}=TRUE;
  20. $opts{sig}=TRUE;
  21. }
  22. # Override the data directory if passed as an argument
  23. $data_dir = $opts{data_dir} if ( defined $opts{data_dir} );
  24. # Are we looking for specific users or all users?
  25. # There has to be a better way to do this - Below
  26. @user_list = split ( /,/, $opts{user} ) if defined $opts{user};
  27. # Here we go
  28. # If no arguments are passed, and we cant open the dir, we should
  29. # get a usage.
  30. opendir(DIR, $data_dir) or
  31. die "DIRECTORY READ ERROR: Could not open $data_dir!!\n";
  32. while ( $filename = readdir DIR ) {
  33. next if ( $filename eq "." or $filename eq ".." );
  34. $filename =~ /(.*)\.(.*)/;
  35. $username = $1;
  36. # Deal with the people
  37. # There has to be a better way to do this - Above
  38. next if ( defined $opts{user} and grep(!/$username/, @user_list));
  39. # Deal with the extension files
  40. $ext = $2;
  41. next unless $ext;
  42. &abook if ( $ext eq "abook" and defined $opts{abook} );
  43. &pref if ( $ext eq "pref" and defined $opts{pref} );
  44. &sig if ( $ext =~ /si([g\d])$/ and defined $opts{sig});
  45. }
  46. closedir ( DIR );
  47. # All done. Below are functions
  48. # Process a user address file
  49. sub abook {
  50. print "DELETE FROM $db.$abook_table WHERE owner = '$username;\n"
  51. if ( defined $opts{delete} );
  52. open(ABOOK, "<$data_dir/$filename") or
  53. die "FILE READ ERROR: Could not open $filename!!\n";
  54. while (my $line = <ABOOK> ) {
  55. chomp $line;
  56. my ( $nickname,$firstname,$lastname,$email,$label ) = split(/\|/, $line);
  57. print "INSERT INTO $db.$abook_table "
  58. . "(owner,nickname,firstname,lastname,email,label) "
  59. . "VALUES ('$username','$nickname','$firstname','$lastname',"
  60. . "'$email','$label');\n";
  61. }
  62. close(ABOOK);
  63. }
  64. # Process a user prefernce file
  65. sub pref {
  66. print "DELETE FROM $db.$pref_table "
  67. . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
  68. if ( defined $opts{delete} );
  69. open(PREFS, "<$data_dir/$filename") or
  70. die "FILE READ ERROR: Could not open $filename!!\n";
  71. while (my $line = <PREFS> ) {
  72. chomp $line;
  73. my ( $prefkey, $prefval ) = split(/=/, $line);
  74. print "INSERT INTO $db.$pref_table "
  75. . "(user,prefkey,prefval) "
  76. . "VALUES ('$username','$prefkey','$prefval');\n";
  77. }
  78. close(PREFS);
  79. }
  80. # Process a user sig file
  81. sub sig {
  82. $del_ext = $1;
  83. $del_ext = "nature" if ( $del_ext eq "g" );
  84. print "DELETE FROM $db.$pref_table "
  85. . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
  86. if ( defined $opts{delete} );
  87. open(SIG, "<$data_dir/$filename") or
  88. die "FILE READ ERROR: Could not open $filename!!\n";
  89. my @lines = <SIG>;
  90. close(SIG);
  91. $filename =~ /.*\.si([g,\d]$)/;
  92. $prefkey = "___sig";
  93. if ( $1 eq "g" ) {
  94. $prefkey .= "nature___";
  95. } else {
  96. $prefkey .= "$1___";
  97. }
  98. print "INSERT INTO $db.$sig_table (user,prefkey,prefval) "
  99. . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
  100. }
  101. # Print out the usage screen
  102. sub Usage {
  103. $0 =~ /.*\/(.*)/;
  104. $prog = $1;
  105. print <<EOL;
  106. This program generates SQL statements to aid importing squirrelmail
  107. user config into a database.
  108. Usage: $prog [--delete] [--abook] [--pref] [--sig] [--data_dir=<>] [--user=<username0[,username1[,username2]...]]
  109. Prefs --abook, --pref, and --sig are assumed if none of them as passed
  110. --delete removes all previous values for users ( --users=<> ) already in
  111. the database. This is useful to reimport users.
  112. It respects --abook, --pref, and --sig.
  113. If --user is not specified, it will try to do all users.
  114. EOL
  115. exit 1;
  116. }