tools/jabber2db.pl

   1 #!/usr/bin/perl -w
   2
   3 #### CONFIG
   4
   5 my $db = "Pg"; # Pg : PostgresSQL, mysql: MySQL...
   6 my $dbuser = "jabber";
   7 my $dbpass = "secret";
   8 my $dbname = "jabberd2";
   9
  10 my $DUMP_QUERY = 0;
  11
  12 ##########################
  13 use strict;
  14 use DBI;
  15 use Vcard2Ldap;
  16
  17 my $vcard2db = {
  18     "FN" => "fn",
  19     "NICKNAME" => "nickname",
  20     "URL" => "url",
  21     "TEL/NUMBER" => "tel",
  22     "EMAIL/USERID" => "email",
  23     "TITLE" => "title",
  24     "ROLE" => "role",
  25     "BDAY" => "bday",
  26     "DESC" => "desc",
  27     "N/FAMILY" => "n-family",
  28     "N/GIVEN" => "n-given",
  29     "N/MIDDLE" => "n-middle",
  30     "N/PREFIX" => "n-prefix",
  31     "N/SUFFIX" => "n-suffix",
  32     "ADR/STREET" => "adr-street",
  33     "ADR/POBOX" => "adr-pobox",
  34     "ADR/EXTADD" => "adr-extadd",
  35     "ADR/LOCALITY" => "adr-locality",
  36     "ADR/REGION" => "adr-region",
  37     "ADR/PCODE" => "adr-pcode",
  38     "ADR/CTRY" => "adr-country",
  39     "ORG/ORGNAME" => "org-orgname",
  40     "ORG/ORGUNIT" => "org-orgunit",
  41
  42     "TZ" => "tz",
  43     "GEO/LAT" => "geo-lat",
  44     "GEO/LON" => "geo-lon",
  45     "AGENT/EXTVAL" => "agent-extval",
  46     "NOTE" => "note",
  47     "REV" => "rev",
  48     "SORT-STRING" => "sort-string",
  49
  50     "KEY/TYPE" => "key-type",
  51     "KEY/CRED" => "key-cred",
  52
  53     "PHOTO/TYPE" => "photo-type",
  54     "PHOTO/BINVAL" => "photo-binval",
  55     "PHOTO/EXTVAL" => "photo-extval",
  56
  57     "LOGO/TYPE" => "logo-type",
  58     "LOGO/BINVAL" => "logo-binval",
  59     "LOGO/EXTVAL" => "logo-extval",
  60
  61     "SOUND/PHONETIC" => "sound-phonetic",
  62     "SOUND/BINVAL" => "sound-binval",
  63     "SOUND/EXTVAL" => "sound-extval"
  64 };
  65
  66 my $option_populate = 0;
  67 my $option_add_user = 0;
  68
  69 my ($ldap, $dbh, $vcard2ldap);
  70
  71 ### MAIN
  72 if ($#ARGV == 0) {
  73     if ($ARGV[0] eq "-p" || $ARGV[0] eq "--populate") {
  74         $option_populate = 1;
  75     } elsif ($ARGV[0] =~ /^[\w\-_\.]+@.+/){
  76         $option_add_user = 1;
  77     } else {
  78         usage ();
  79     }
  80 } elsif ($#ARGV > 0) {
  81     usage ();
  82 }
  83
  84 $vcard2ldap = Vcard2Ldap::vcard_get_map ();
  85 $ldap = Vcard2Ldap::ldap_open ();
  86 $dbh = db_open ();
  87
  88 set_all_vcards ();
  89 add_user ($ARGV[0]) if $option_add_user;
  90 populate_all_rosters () if $option_populate;
  91
  92 $dbh->disconnect;
  93 exit;
  94
  95 ###
  96 sub usage {
  97     print "usage: jabber2db [-p|--populate] [user\@$Vcard2Ldap::jabberd]\n";
  98     exit (1);
  99 }
 100
 101 sub set_all_vcards {
 102     my $query = "";
 103
 104     db_delete_vcards () if $option_populate;
 105
 106     foreach (Vcard2Ldap::ldap_get_users ($ldap)) {
 107         $query .= db_set_vcard ($_);
 108     }
 109
 110      execute_query ($query);
 111 }
 112
 113 sub add_user {
 114     my $jid = shift;
 115     my $user = Vcard2Ldap::jabber_user ($jid);
 116     my $query = "";
 117
 118     return if (!$user);
 119
 120     foreach (Vcard2Ldap::jabber_get_users ()) {
 121         if ($user->{"jid"} ne $_->{"jid"} &&
 122             $user->{"rostergroup"} eq $_->{"rostergroup"}) {
 123             $query .= db_insert_user_to_roster ($user->{"jid"}, $_->{"jid"});
 124             $query .= db_insert_user_to_roster ($_->{"jid"}, $user->{"jid"});
 125         }
 126     }
 127
 128     execute_query ($query);
 129 }
 130
 131 sub populate_all_rosters {
 132     my $query = "";
 133
 134     db_delete_rosters ();
 135
 136     foreach my $us1 (Vcard2Ldap::jabber_get_users ()) {
 137         foreach my $us2 (Vcard2Ldap::jabber_get_users ()) {
 138             if ($us1->{"jid"} ne $us2->{"jid"} &&
 139                  $us1->{"rostergroup"} eq $us2->{"rostergroup"}) {
 140                 $query .= db_insert_user_to_roster ($us1->{"jid"}, $us2->{"jid"}) . ";";
 141             }
 142         }
 143     }
 144
 145     execute_query ($query);
 146 }
 147
 148 sub db_open {
 149     my $dbh = DBI->connect (
 150         "DBI:$db:database=$dbname;host=localhost",
 151         $dbuser,
 152         $dbpass
 153     ) or die "jabberd2db: cannot connect to DB!";
 154
 155     return $dbh;
 156 }
 157
 158 sub execute_query {
 159     my $query = shift;
 160     my $rows;
 161
 162     print $query if ($DUMP_QUERY);
 163
 164     return $dbh->do ($query) || die "jabber2db: cannot execute query! ($!)";
 165 }
 166
 167 sub db_delete_vcards {
 168     execute_query ("DELETE FROM \"vcard\";");
 169 }
 170
 171 sub db_delete_rosters {
 172     execute_query ("DELETE FROM \"roster-items\";\n" .
 173                              "DELETE FROM \"roster-groups\";");
 174 }
 175
 176 sub db_insert_vcard {
 177     my $pers = shift;
 178
 179     my $squery = "";
 180     my $ssquery = "";
 181     my $query = "";
 182
 183     $query = "INSERT INTO vcard (\"collection-owner\"";
 184
 185     while (my ($key, $value) = each (%$vcard2db)) {
 186         if ($vcard2ldap->{$key} && $pers->get_value ($vcard2ldap->{$key})) {
 187             $squery   .=  ", \"". $value . "\"";
 188             $ssquery .= $dbh->quote ($pers->get_value ($vcard2ldap->{$key})) .
 189                 ",";
 190         }
 191     }
 192
 193     chop ($ssquery);
 194
 195     $query .= $squery . ") VALUES ('" .
 196              Vcard2Ldap::jabber_jid ($pers) . "', " .
 197              $ssquery . ");\n";
 198
 199     return $query;
 200 }
 201
 202 sub db_update_vcard {
 203     my $pers = shift;
 204
 205     my $squery = "";
 206     my $ssquery = "";
 207     my $query = "";
 208
 209     $query = "UPDATE vcard SET ";
 210
 211     while (my ($key, $value) = each (%$vcard2db)) {
 212         if ($vcard2ldap->{$key} && $pers->get_value ($vcard2ldap->{$key})) {
 213             $query .=  "\"". $value . "\" = " .
 214                     $dbh->quote ($pers->get_value ($vcard2ldap->{$key})) . ",";
 215         }
 216     }
 217
 218     chop ($query);
 219
 220     $query .=  " WHERE \"collection-owner\" = " .
 221              $dbh->quote (Vcard2Ldap::jabber_jid ($pers)) . ";\n";
 222
 223     return $query;
 224 }
 225
 226 sub db_vcard_exists {
 227     my $pers = shift;
 228     my $query;
 229     my $rc;
 230
 231     $query = "SELECT COUNT(*) FROM vcard WHERE \"collection-owner\" = " .
 232                   $dbh->quote (Vcard2Ldap::jabber_jid ($pers));
 233
 234     return execute_query ($query) > 0;
 235 }
 236
 237 sub db_set_vcard {
 238     my $pers = shift;
 239     my $is_update = 0;
 240     my $query;
 241
 242     return ""
 243         if (!Vcard2Ldap::jabber_jid ($pers) ||
 244             !Vcard2Ldap::jabber_group ($pers));
 245
 246     $is_update = db_vcard_exists ($pers) if (!$option_populate);
 247
 248     if ($is_update) {
 249         db_update_vcard ($pers);
 250     } else {
 251         db_insert_vcard ($pers);
 252     }
 253 }
 254
 255 sub db_insert_user_to_roster {
 256     my $ujid = shift;
 257     my $rjid = shift;
 258     my $query;
 259
 260     $query = "INSERT INTO \"roster-items\" (\"collection-owner\", \"jid\", \"to\", \"from\", \"ask\") VALUES (" .
 261               $dbh->quote ($ujid) . "," .
 262               $dbh->quote ($rjid) . "," .
 263               "TRUE, TRUE, 0 );\n";
 264
 265     $query .= "INSERT INTO \"roster-groups\" (\"collection-owner\", \"jid\", \"group\") VALUES (" .
 266               $dbh->quote ($ujid) . "," .
 267               $dbh->quote ($rjid) . "," .
 268               $dbh->quote ($Vcard2Ldap::groupname) . ");\n";
 269
 270     return $query;
 271 }
 272
 273