perl/lib/Thrasher/Backend/DBI.pm

   1 package Thrasher::Backend::DBI;
   2 use strict;
   3 use warnings;
   4
   5 use base 'Thrasher::Backend';
   6
   7 use Carp qw(confess);
   8
   9 =pod
  10
  11 =head1 NAME
  12
  13 Thrasher::Backend::DBI - store the information about the transports
  14 in an DBI-accessible database
  15
  16 =head1 DESCRIPTION
  17
  18 Thrasher::Backend::DBI stores the transport's information in a
  19 DBI-accessible database.
  20
  21 Because of the individual characteristics of databases, this can't
  22 be guaranteed to work across all databases, especially when it
  23 comes to creating the necessary databases and tables. However,
  24 the requisite SQL for reading and writing is relatively simple,
  25 so it shouldn't be too bad.
  26
  27 This provides full read and write capability for Thrasher.
  28
  29 The following tables are built and used:
  30
  31 =over 4
  32
  33 =item *
  34
  35 C<jid>: Maps a JID to a unique integer ID. (I don't like natural
  36 keys, especially when they are nearly-arbitrarily-large strings.)
  37
  38 C<legacy>: Maps a legacy username to a unique ID. This is the
  39 original name on the legacy service, not a translated name.
  40
  41 C<transport_name>: Maps a transport name to a simple numeric
  42 unique ID. This allows these tables to be simultaneously used
  43 by multiple Thrasher instances with otherwise-identical
  44 connection information.
  45
  46 C<registration>: Stores the registration information for a given
  47 JID. Because this can be more than just username and password
  48 in theory, a numeric field is used for each row to indicate
  49 which thing the row is. Username is defined as 1, Password
  50 is defined as 2, and the rest up to 255 are available for
  51 any protocol than needs it.
  52
  53 C<name_maps>: For each JID, stores the mapping of JID
  54 to legacy service name, in accordance with the gateway
  55 protocol.
  56
  57 C<avatars>: For a given JID/legacy account, stores the avatar
  58 for that user, if any. Note that the avatar's size is only
  59 guaranteed up to 64KB, which really ought to be enough...
  60
  61 C<roster>: For a given JID, stores which legacy users are
  62 currently on the roster for that user, and the state of
  63 that roster entry.
  64
  65 C<misc>: For a given JID, allows the final backup of a
  66 string-based key/value storage. Hopefully this won't
  67 be used, but just in case you need it, this will be here.
  68
  69 =back
  70
  71 Full definitions of the table can be seen in the source code.
  72
  73 Currently, only MySQL with InnoDB is supported, but other databases
  74 should be easily added by adding an entry into the hash that
  75 stores all the SQL for the database.
  76
  77 Joins are avoiding by caching active ID information in
  78 Perl's process space, in the session object.
  79
  80 =cut
  81
  82 use Data::Dumper;
  83
  84 use Thrasher::Log qw(log debug);
  85
  86 # This currently does no in-memory caching, so it nails the database
  87 # pretty hard in some cases. It is easily fixed, but we've left
  88 # it out for debugging.
  89
  90 # It is checked that whatever database you have selected, that
  91 # it has definitions for all the same SQL statements as the database
  92 # below. This prevents us from adding SQL statements to MySQL,
  93 # then having people run the new transport on some other database
  94 # when they don't have SQL fragments for some operation.
  95 my $privileged_database = 'mysql_innodb';
  96
  97 # Note that 3071 is the maximum length of a JID; each of the
  98 # three components can be 1023 in size, + 2 chars for the separators.
  99 # Therefore, don't use a char field that can only store 255 chars,
 100 # despite the temptation.
 101
 102 # Database misc:
 103 # * We turn off transactions if we can, because as it turns out
 104 #   with the exception of assigning IDs (which DBs typically do
 105 #   atomically anyhow), any given row will have only one single-
 106 #   threaded process accessing it at any given time, even if
 107 #   you are running multiple Thrasher Bird instances. Note the care taken
 108 #   at the few places they could overlap, the creation of a legacy
 109 #   id or jid row.
 110 # * I don't like natural keys that take the form of potentially
 111 #   multi-kilobyte texts, and neither does the database that I use.
 112
 113 my $database_support =
 114 {
 115     mysql_innodb =>
 116     {
 117         # This should be a sequence of table creation calls that
 118         # can be run harmlessly even if the tables exist, and
 119         # which will create the tables in a fresh DB.
 120         create_tables => <<CREATE_TABLES,
 121 CREATE TABLE IF NOT EXISTS `jid` (
 122   `id` bigint unsigned not null auto_increment,
 123   `jid` text not null,
 124   PRIMARY KEY (`id`)
 125 ) TYPE=InnoDB CHARACTER SET utf8
 126
 127 CREATE TABLE IF NOT EXISTS `legacy` (
 128   `id` bigint unsigned not null auto_increment,
 129   `legacy` varchar(255) not null,
 130   KEY `legacy` (`legacy`),
 131   PRIMARY KEY (`id`)
 132 ) TYPE=InnoDB CHARACTER SET utf8
 133
 134 CREATE TABLE IF NOT EXISTS `transport` (
 135   `id` tinyint unsigned not null auto_increment,
 136   `transport` varchar(255),
 137   PRIMARY KEY (`id`)
 138 ) TYPE=InnoDB CHARACTER SET utf8
 139
 140 CREATE TABLE IF NOT EXISTS `registration` (
 141   `id` bigint unsigned not null auto_increment,
 142   `jid_id` bigint unsigned not null,
 143   `transport_id` tinyint unsigned not null,
 144   `key` tinyint not null,
 145   `value` text not null,
 146   PRIMARY KEY(`id`),
 147   KEY `jid_id` (`jid_id`),
 148   KEY `transport_id` (`transport_id`),
 149   FOREIGN KEY (`jid_id`) REFERENCES `jid` (`id`),
 150   FOREIGN KEY (`transport_id`) REFERENCES `transport` (`id`)
 151 ) TYPE=InnoDB CHARACTER SET utf8
 152
 153 CREATE TABLE IF NOT EXISTS `name_maps` (
 154   `id` bigint unsigned not null auto_increment,
 155   `jid_id` bigint unsigned not null,
 156   `legacy_id` bigint unsigned not null,
 157   `transport_id` tinyint unsigned not null,
 158   `mapped_jid` varchar(255) not null,
 159   PRIMARY KEY (`id`),
 160   KEY `jid_id` (`jid_id`),
 161   KEY `legacy_id` (`legacy_id`),
 162   KEY `transport_id` (`transport_id`),
 163   KEY `mapped_jid` (`mapped_jid`),
 164   FOREIGN KEY (`jid_id`) REFERENCES `jid` (`id`),
 165   FOREIGN KEY (`legacy_id`) REFERENCES `legacy` (`id`),
 166   FOREIGN KEY (`transport_id`) REFERENCES `transport` (`id`)
 167 ) TYPE=InnoDB CHARACTER SET utf8
 168
 169 CREATE TABLE IF NOT EXISTS `avatars` (
 170   `id` bigint unsigned not null auto_increment,
 171   `jid_id` bigint unsigned not null,
 172   `transport_id` tinyint unsigned not null,
 173   `legacy_id` bigint unsigned not null,
 174   `avatar` text not null,
 175   PRIMARY KEY (`id`),
 176   KEY `jid_id` (`jid_id`),
 177   KEY `legacy_id` (`legacy_id`),
 178   KEY `transport_id` (`transport_id`),
 179   UNIQUE `avatar_key` (`jid_id`, `transport_id`, `legacy_id`),
 180   FOREIGN KEY (`jid_id`) REFERENCES `jid` (`id`),
 181   FOREIGN KEY (`legacy_id`) REFERENCES `legacy` (`id`),
 182   FOREIGN KEY (`transport_id`) REFERENCES `transport` (`id`)
 183 ) TYPE=InnoDB CHARACTER SET utf8
 184
 185 CREATE TABLE IF NOT EXISTS `roster` (
 186   `id` bigint unsigned not null auto_increment,
 187   `jid_id` bigint unsigned not null,
 188   `legacy_id` bigint unsigned not null,
 189   `transport_id` tinyint unsigned not null,
 190   `state` tinyint unsigned not null,
 191   PRIMARY KEY (`id`),
 192   KEY `jid_id` (`jid_id`),
 193   KEY `legacy_id` (`legacy_id`),
 194   KEY `transport_id` (`transport_id`),
 195   UNIQUE `roster_key` (`jid_id`, `transport_id`, `legacy_id`),
 196   FOREIGN KEY (`jid_id`) REFERENCES `jid` (`id`),
 197   FOREIGN KEY (`legacy_id`) REFERENCES `legacy` (`id`),
 198   FOREIGN KEY (`transport_id`) REFERENCES `transport` (`id`)
 199 ) TYPE=InnoDB CHARACTER SET utf8
 200
 201 CREATE TABLE IF NOT EXISTS `misc` (
 202   `id` bigint unsigned not null auto_increment,
 203   `jid_id` bigint unsigned not null,
 204   `transport_id` tinyint unsigned not null,
 205   `key` varchar(255) not null,
 206   `value` blob not null,
 207   PRIMARY KEY(`id`),
 208   KEY `jid_id` (`jid_id`),
 209   KEY `transport_id` (`transport_id`),
 210   UNIQUE `misc_key` (`jid_id`, `transport_id`, `key`),
 211   FOREIGN KEY (`jid_id`) REFERENCES `jid` (`id`),
 212   FOREIGN KEY (`transport_id`) REFERENCES `transport` (`id`)
 213 ) TYPE=InnoDB CHARACTER SET utf8
 214 CREATE_TABLES
 215
 216     # This is used by test scripts; it should return something,
 217     # anything, if the ? table exists, and error or return
 218     # nothing if it doesn't.
 219     detect_table => 'SHOW CREATE TABLE ?',
 220
 221     # a fragment of what is returned when the database is not found
 222     table_not_found_message => 'Unknown database',
 223
 224     empty_table => 'DELETE * FROM ?',
 225
 226     retrieve_transport_id =>
 227         'SELECT id FROM transport WHERE transport = ?',
 228     create_transport_id =>
 229         'INSERT INTO transport (transport) VALUES (?)',
 230
 231     registration =>
 232         ('SELECT `key`, `value` FROM registration WHERE '
 233          .'transport_id = ? AND jid_id = ?'),
 234     add_registration_value =>
 235         ('REPLACE INTO registration '
 236          .'(transport_id, jid_id, `key`, `value`) VALUES '
 237          .'(?     , ?           , ?  , ?    )'),
 238     clear_registration =>
 239         ('DELETE FROM registration WHERE '
 240          .'transport_id = ? AND jid_id = ?'),
 241
 242     jid_id =>
 243         'SELECT id FROM jid WHERE jid = ?',
 244     create_jid_id =>
 245         'INSERT INTO jid (jid) VALUES (?)',
 246     legacy_id =>
 247         'SELECT id FROM legacy WHERE legacy = ?',
 248     create_legacy_id =>
 249         'INSERT INTO legacy (legacy) VALUES (?)',
 250
 251     retrieve_name_map =>
 252         ('SELECT mapped_jid FROM name_maps WHERE '
 253          .'transport_id = ? AND jid_id = ? AND '
 254          .'legacy_id = ?'),
 255     retrieve_jid_from_legacy =>
 256         ('SELECT legacy.legacy FROM name_maps '
 257          .'INNER JOIN legacy ON name_maps.legacy_id = legacy.id '
 258          .'WHERE transport_id = ? AND jid_id = ? AND '
 259          .'mapped_jid = ?'),
 260     store_username_mapping =>
 261         ('INSERT INTO name_maps (transport_id, jid_id, '
 262          .'legacy_id, mapped_jid) VALUES (?, ?, ?, ?)'),
 263
 264     set_avatar =>
 265         ('REPLACE INTO avatars (transport_id, jid_id, '
 266          .'legacy_id, avatar) VALUES (?, ?, ?, ?)'),
 267     get_avatar =>
 268         ('SELECT avatar FROM avatars WHERE '
 269          .'transport_id = ? AND jid_id = ? AND '
 270          .'legacy_id = ?'),
 271     delete_avatar =>
 272         ('DELETE FROM avatars WHERE transport_id = ? '
 273          .'AND jid_id = ? AND legacy_id = ?'),
 274     all_avatars =>
 275         ('SELECT legacy.legacy, avatars.avatar FROM '
 276          .'avatars INNER JOIN legacy ON avatars.legacy_id = '
 277          .'legacy.id WHERE avatars.transport_id = ? '
 278          .'AND avatars.jid_id = ?'),
 279
 280     get_roster =>
 281         ('SELECT legacy.legacy AS legacy, roster.state AS state FROM '
 282          .'roster INNER JOIN legacy ON roster.legacy_id = '
 283          .'legacy.id WHERE roster.transport_id = ? '
 284          .'AND roster.jid_id = ?'),
 285     set_roster_state =>
 286         ('REPLACE INTO roster (transport_id, jid_id, '
 287          .'legacy_id, state) VALUES (?, ?, ?, ?)'),
 288     remove_roster_state =>
 289         ('DELETE FROM roster WHERE transport_id = ? '
 290          .'AND jid_id = ? AND legacy_id = ?'),
 291     get_roster_state =>
 292         ('SELECT state FROM roster WHERE transport_id = ? '
 293          .'AND jid_id = ? AND legacy_id = ?'),
 294     clear_roster_for_jid =>
 295         ('DELETE FROM roster WHERE transport_id = ? '
 296          .'AND jid_id = ?'),
 297
 298     all_jids => 'SELECT DISTINCT jid.jid FROM registration INNER JOIN jid ON registration.jid_id = jid.id WHERE registration.transport_id = ?',
 299     all_mappings =>
 300         ('SELECT legacy.legacy AS legacy, name_maps.mapped_jid AS '
 301          .'mapped_jid FROM name_maps '
 302          .'INNER JOIN legacy ON name_maps.legacy_id = legacy.id '
 303          .'WHERE transport_id = ? AND jid_id = ?'),
 304     all_misc =>
 305         ('SELECT `key`, value FROM misc WHERE transport_id = ? '
 306          .'AND jid_id = ?'),
 307     set_misc =>
 308         ('REPLACE INTO misc (transport_id, jid_id, `key`, value) '
 309          .'VALUES (?, ?, ?, ?)'),
 310     get_misc =>
 311         ('SELECT value FROM misc WHERE transport_id = ? '
 312          .'AND jid_id = ? AND `key` = ?'),
 313
 314     unregister_roster =>
 315         ('DELETE FROM roster WHERE transport_id = ? AND jid_id = ?'),
 316     unregister_registration =>
 317         ('DELETE FROM registration WHERE transport_id = ? AND jid_id = ?'),
 318     unregister_namemaps =>
 319         ('DELETE FROM name_maps WHERE transport_id = ? AND jid_id = ?'),
 320     unregister_misc =>
 321         ('DELETE FROM misc WHERE transport_id = ? AND jid_id = ?'),
 322     unregister_avatars =>
 323         ('DELETE FROM avatars WHERE transport_id = ? AND jid_id = ?'),
 324     unregister_registration =>
 325         ('DELETE FROM registration WHERE transport_id = ? AND jid_id = ?'),
 326
 327     # actually a sub to create the database
 328     create_database => sub {
 329         my $username = shift;
 330         my $password = shift;
 331         my $database_name = shift;
 332         return system("mysql",
 333                       $username ? ("--user=$username") : (),
 334                       $password ? ("--password=$password") : (),
 335                       # so... don't name the database something
 336                       # stupid, OK? You SQL-inject yourself, it's
 337                       # your own fault
 338                       "-e", "CREATE DATABASE `$database_name`");
 339     },
 340
 341     # EVIL, does exactly what it says on the tin
 342     destroy_database => sub {
 343         my $username = shift;
 344         my $password = shift;
 345         my $database_name = shift;
 346         return system("mysql",
 347                       $username ? ("--user=$username") : (),
 348                       $password ? ("--password=$password") : (),
 349                       "-e", "DROP DATABASE `$database_name`");
 350     },
 351
 352     turn_off_transactions => ''
 353     }
 354 };
 355
 356 our @tables = qw(jid legacy transport registration name_maps
 357                  avatars roster misc);
 358
 359 use DBI;
 360
 361 sub new {
 362     my $class = shift;
 363     my $parameters = shift;
 364
 365     my %clone = %$parameters;
 366
 367     if (ref($parameters) ne 'HASH') {
 368         die "Thrasher::Backend::DBI requires parameters to set it up; "
 369             ."see the documentation for it.";
 370     }
 371
 372     bless \%clone, $class;
 373     my $self = \%clone;
 374
 375     my @missing_params;
 376     for my $param qw(dbi_data_source username password db_driver
 377                      database_name transport_name) {
 378         if (!defined($self->{$param})) {
 379             push @missing_params, $param;
 380         }
 381     }
 382
 383     if (@missing_params) {
 384         die "Thrasher::Backend::DBI is missing the following "
 385             ."parameter(s): " . join(", ", @missing_params)
 386             .". Please provide them in $0 and restart.";
 387     }
 388
 389     my $retry = 0;
 390     my $dbh = $self->connect_to_db;
 391
 392     ### DEBUGING
 393     #$self->clear_backend;
 394
 395     log("Connected to $self->{dbi_data_source}");
 396
 397     $self->{dbh} = $dbh;
 398
 399     $self->verify_tables;
 400
 401     $self->{id} = $self->get_transport_id;
 402
 403     return $self;
 404 }
 405
 406 sub verify_tables {
 407     my $self = shift;
 408     my $dbh = $self->{dbh};
 409
 410     # Check all the tables
 411     my $create_table = $self->sql('create_tables');
 412     my @table_creates = split/\n\n/, $create_table;
 413     for my $table_create (@table_creates) {
 414         if (!defined($dbh->do($table_create))) {
 415             die "SQL failure while creating table with statement: $table_create";
 416         }
 417     }
 418     log("Database tables verified as existing.");
 419 }
 420
 421 sub get_transport_id {
 422     my $self = shift;
 423
 424     debug("Getting the transport id");
 425     my $transport_id_sth = $self->call('retrieve_transport_id',
 426                                        $self->{transport_name});
 427     my ($transport_id) = $transport_id_sth->fetchrow_array;
 428     debug("Got transport id: " . ($transport_id||''));
 429
 430     if (!defined($transport_id)) {
 431         debug("Transport id was undefined, making new one.");
 432         $self->call('create_transport_id', $self->{transport_name});
 433         return $self->get_transport_id;
 434     }
 435
 436     return $transport_id;
 437 }
 438
 439 sub connect_to_db {
 440     my $self = shift;
 441     my $is_retry = shift;
 442
 443     log("Attempting to connect to $self->{dbi_data_source}");
 444     my $dbh;
 445     local $@;
 446     eval {
 447         $dbh = DBI->connect($self->{dbi_data_source},
 448                             $self->{username},
 449                             $self->{password},
 450                             {RaiseError => 1,
 451                              PrintError => 0});
 452     };
 453     if ($@) {
 454         log("Failed to connect to database: $@");
 455         # If it's because the DB didn't exist, try creating it and
 456         # try again. Otherwise, time to bail.
 457         my $not_found_message =
 458             $self->sql('table_not_found_message');
 459         if (!$is_retry && $@ =~ /$not_found_message/i) {
 460             my $create_database = $self->sql('create_database');
 461             log("Failed to locate $self->{dbi_data_source}, attempting to create.");
 462             $create_database->($self->{username},
 463                                $self->{password},
 464                                $self->{database_name});
 465             return $self->connect_to_db(1);
 466         }
 467
 468         die "While Thrasher::Backend::DBI was trying to connect to the "
 469             ."database, DBI gave the following error: $@";
 470     }
 471     return $dbh;
 472 }
 473
 474 sub sql {
 475     my $self = shift;
 476     my $sql_frag = shift;
 477
 478     return $database_support->{$self->{db_driver}}->{$sql_frag};
 479 }
 480
 481 sub call {
 482     my $self = shift;
 483     my $sql_fragment_name = shift;
 484     my @args = @_;
 485
 486     my $sth = $self->{sths}->{$sql_fragment_name};
 487     if (!$sth) {
 488         my $sql = $self->sql($sql_fragment_name);
 489         if (!$sql) {
 490             die "In Thrasher::Backend::DBI, no sql fragment named "
 491                 ."'$sql_fragment_name' for $self->{db_driver}.";
 492         }
 493         $sth = $self->{sths}->{$sql_fragment_name} =
 494             $self->{dbh}->prepare($sql);
 495     }
 496
 497     #debug(sub {
 498     #    my $sql = $self->sql($sql_fragment_name);
 499     #    return "SQL: $sql, args: " . join(' ', @args);
 500     #});
 501
 502     {
 503         local $@;
 504         eval {
 505             $sth->execute(@args);
 506         };
 507
 508         if ($@) {
 509             if ($@ =~ /gone away/) {
 510                 log "MySQL server has gone away, attempting to reconnect";
 511                 delete $self->{sths};
 512                 $self->{dbh}->disconnect;
 513                 my $new_dbh;
 514                 $@ = '';
 515                 eval {
 516                     log("Connecting...");
 517                     $new_dbh = $self->connect_to_db;
 518                     log("Got past connection attempt");
 519                 };
 520                 if ($@ || !$new_dbh) {
 521                     log "Reconnection failed, terminating component: $@";
 522                     $self->{component}->terminate(no_db => 1);
 523                     return;
 524                 }
 525                 log("Reconnection seems to be a success...");
 526                 $self->{dbh} = $new_dbh;
 527                 # Success!
 528                 return $self->call($sql_fragment_name, @args);
 529             }
 530         }
 531     }
 532     return $sth;
 533 }
 534
 535 sub jid_id {
 536     my $self = shift;
 537     my $jid = shift;
 538     my $create = shift || 0;
 539
 540     #debug("JID id for $jid (force-create: $create)");
 541
 542     my $sth = $self->call('jid_id', $jid);
 543     my ($jid_id) = $sth->fetchrow_array();
 544     $sth->finish;
 545
 546     if (!defined($jid_id) && $create) {
 547         #debug("JID not found, attempting to create");
 548         $self->call('create_jid_id', $jid);
 549         #debug("Recursively calling jid_id");
 550         return $self->jid_id($jid);
 551     }
 552
 553     #debug("returning " . ($jid_id||"no jid") . " for $jid");
 554     return $jid_id;
 555 }
 556
 557 sub legacy_id {
 558     my $self = shift;
 559     my $legacy = shift;
 560
 561     my $sth = $self->call('legacy_id', $legacy);
 562     my ($legacy_id) = $sth->fetchrow_array;
 563     $sth->finish;
 564
 565     if (!defined($legacy_id)) {
 566         $self->call('create_legacy_id', $legacy);
 567         return $self->legacy_id($legacy);
 568     }
 569
 570     return $legacy_id;
 571 }
 572
 573 my $db_to_field = {};
 574 my $field_to_db = {};
 575
 576 sub register_protocol {
 577     my $self = shift;
 578     my $protocol = shift;
 579
 580     my @registration_items = $protocol->registration_items;
 581     my $i = 1;
 582
 583     for my $item (@registration_items) {
 584         $db_to_field->{$i} = $item;
 585         $field_to_db->{$item} = $i;
 586         $i++;
 587     }
 588
 589     $self->{registration_defaults} = $protocol->registration_defaults;
 590 }
 591
 592 sub registered {
 593     my $self = shift;
 594     my $jid = shift;
 595
 596     my $jid_id = $self->jid_id($jid);
 597     if (!defined($jid_id)) {
 598         return undef;
 599     }
 600
 601     my $sth = $self->call('registration', $self->{id},
 602                           $jid_id);
 603     my $registration;
 604     my @values;
 605
 606     while (@values = $sth->fetchrow_array) {
 607         $registration->{
 608             $db_to_field->{$values[0]} || $values[0]
 609         } = $values[1];
 610     }
 611
 612     $sth->finish;
 613
 614     return $registration;
 615 }
 616
 617 sub register {
 618     my $self = shift;
 619     my $jid = shift;
 620     my $registration = shift;
 621
 622     #debug("Attempting to register: $jid, " . Dumper($registration));
 623
 624     my $jid_id = $self->jid_id($jid, 1);
 625     $self->call('clear_registration', $self->{id},
 626                 $jid_id);
 627
 628     while (my ($key, $value) = each %$registration) {
 629         my $db_key = $field_to_db->{$key};
 630         if (!defined($db_key)) {
 631             log("While trying to register $jid, tried "
 632                 ."to convert $key to a DB value but couldn't.");
 633             next;
 634         }
 635
 636         $self->call('add_registration_value', $self->{id},
 637                     $jid_id, $db_key, $value);
 638     }
 639     #debug("Successfully registered $jid: " . Dumper($registration));
 640     return 1;
 641 }
 642
 643 sub retrieve_legacy_name_to_jid {
 644     my $self = shift;
 645     my $user_jid = shift;
 646     my $legacy_username = shift;
 647
 648     my $jid_id = $self->jid_id($user_jid);
 649     my $legacy_id = $self->legacy_id($legacy_username);
 650
 651     my ($legacy_jid) = $self->call('retrieve_name_map',
 652                                    $self->{id}, $jid_id,
 653                                    $legacy_id)->
 654                               fetchrow_array;
 655     return $legacy_jid;
 656 }
 657
 658 sub jid_to_legacy_name {
 659     my $self = shift;
 660     my $user_jid = shift;
 661     my $target_jid = shift;
 662
 663     my $jid_id = $self->jid_id($user_jid, 1);
 664
 665     my ($legacy_name) = $self->call('retrieve_jid_from_legacy',
 666                                     $self->{id}, $jid_id,
 667                                     $target_jid)->
 668                                fetchrow_array;
 669
 670     if (!$legacy_name) {
 671         $legacy_name = $self->fake_up_a_legacy_name($user_jid, $target_jid);
 672     }
 673     return $legacy_name;
 674 }
 675
 676 sub jid_has_legacy_name {
 677     my $self = shift;
 678     my $user_jid = shift;
 679     my $target_jid = shift;
 680
 681     my $jid_id = $self->jid_id($user_jid);
 682
 683     my ($legacy_name) = $self->call('retrieve_jid_from_legacy',
 684                                     $self->{id}, $jid_id,
 685                                     $target_jid)->
 686                                fetchrow_array;
 687
 688     return !!$legacy_name;
 689 }
 690
 691 sub store_username_mapping {
 692     my $self = shift;
 693     my $user_jid = shift;
 694     my $legacy_username = shift;
 695     my $mapped_jid = shift;
 696
 697     debug("store_username_mapping($user_jid, $legacy_username, $mapped_jid)\n");
 698
 699     my $jid_id = $self->jid_id($user_jid, 1);
 700     my $legacy_id = $self->legacy_id($legacy_username);
 701
 702     $self->call('store_username_mapping',
 703                 $self->{id}, $jid_id, $legacy_id,
 704                 $mapped_jid);
 705 }
 706
 707 sub set_avatar {
 708     my $self = shift;
 709     my $user_jid = shift;
 710     my $legacy_username = shift;
 711     my $avatar_png_base_64 = shift;
 712
 713     my $jid_id = $self->jid_id($user_jid);
 714     my $legacy_id = $self->legacy_id($legacy_username);
 715
 716     if ($avatar_png_base_64) {
 717         $self->call('set_avatar', $self->{id},
 718                     $jid_id, $legacy_id, $avatar_png_base_64);
 719     } else {
 720         $self->call('delete_avatar', $self->{id},
 721                     $jid_id, $legacy_id);
 722     }
 723 }
 724
 725 sub get_avatar {
 726     my $self = shift;
 727     my $user_jid = shift;
 728     my $legacy_username = shift;
 729
 730     my $jid_id = $self->jid_id($user_jid);
 731     my $legacy_id = $self->legacy_id($legacy_username);
 732
 733     my ($avatar) = $self->call('get_avatar', $self->{id},
 734                                $jid_id, $legacy_id)->
 735                           fetchrow_array;
 736     return $avatar;
 737 }
 738
 739 sub get_roster {
 740     my $self = shift;
 741     my $user_jid = shift;
 742
 743     my $jid_id = $self->jid_id($user_jid);
 744
 745     my $roster = {};
 746     my $sth = $self->call('get_roster', $self->{id}, $jid_id);
 747
 748     while (my $roster_entry = $sth->fetchrow_hashref) {
 749         $roster->{$roster_entry->{legacy}} = $roster_entry->{state};
 750     }
 751
 752     return $roster;
 753 }
 754
 755 sub set_roster {
 756     my $self = shift;
 757     my $user_jid = shift;
 758     my $new_roster = shift;
 759
 760     my $jid_id = $self->jid_id($user_jid);
 761     $self->call('clear_roster_for_jid', $self->{id}, $jid_id);
 762
 763     while (my ($legacy_name, $sub_value) = each %$new_roster) {
 764         $self->set_roster_user_state($user_jid, $legacy_name, $sub_value);
 765     }
 766 }
 767
 768 sub set_roster_user_state {
 769     my $self = shift;
 770     my $user_jid = shift;
 771     my $legacy_username = shift;
 772     my $state = shift;
 773
 774     debug("set_roster_user_state($user_jid, $legacy_username, $state)");
 775
 776     my $jid_id = $self->jid_id($user_jid);
 777     my $legacy_id = $self->legacy_id($legacy_username);
 778
 779     if ($state == $self->unsubscribed) {
 780         $self->call('remove_roster_state',
 781                     $self->{id}, $jid_id, $legacy_id);
 782     } else {
 783         $self->call('set_roster_state', $self->{id},
 784                     $jid_id, $legacy_id, $state);
 785     }
 786 }
 787
 788 sub get_roster_user_state {
 789     my $self = shift;
 790     my $user_jid = shift;
 791     my $legacy_username = shift;
 792
 793     my $jid_id = $self->jid_id($user_jid);
 794     my $legacy_id = $self->legacy_id($legacy_username);
 795
 796     my $sth = $self->call('get_roster_state', $self->{id},
 797                           $jid_id, $legacy_id);
 798     my ($state) = $sth->fetchrow_array();
 799     $sth->finish();
 800     $state ||= $self->unsubscribed;
 801     return $state;
 802 }
 803
 804 sub all_jids {
 805     my $self = shift;
 806
 807     my $sql = $self->sql("all_jids");
 808     my $jids = $self->{dbh}->selectcol_arrayref($sql, undef, $self->{id});
 809     return $jids;
 810 }
 811
 812 sub all_mappings {
 813     my $self = shift;
 814     my $jid = shift;
 815
 816     my $jid_id = $self->jid_id($jid);
 817     my $name_mappings = {};
 818
 819     my $sth = $self->call('all_mappings', $self->{id}, $jid_id);
 820     while (my ($key, $value) = $sth->fetchrow_array) {
 821         $name_mappings->{$key} = $value;
 822     }
 823
 824     return $name_mappings;
 825 }
 826
 827 sub all_misc {
 828     my $self = shift;
 829     my $jid = shift;
 830
 831     my $jid_id = $self->jid_id($jid);
 832     my $misc = {};
 833
 834     my $sth = $self->call('all_misc', $self->{id}, $jid_id);
 835     while (my ($key, $value) = $sth->fetchrow_array) {
 836         $misc->{$key} = $value;
 837     }
 838
 839     return $misc;
 840 }
 841
 842 sub all_avatars {
 843     my $self = shift;
 844     my $jid = shift;
 845
 846     my $jid_id = $self->jid_id($jid);
 847     my $avatars = {};
 848
 849     my $sth = $self->call('all_avatars', $self->{id}, $jid_id);
 850     while (my ($legacy, $avatar) = $sth->fetchrow_array) {
 851         $avatars->{$legacy} = $avatar;
 852     }
 853     $sth->finish();
 854
 855     return $avatars;
 856 }
 857
 858 sub set_misc {
 859     my $self = shift;
 860     my $jid = shift;
 861     my $key = shift;
 862     my $value = shift;
 863
 864     my $jid_id = $self->jid_id($jid);
 865     $self->call('set_misc', $self->{id}, $jid_id, $key, $value);
 866 }
 867
 868 sub get_misc {
 869     my $self = shift;
 870     my $jid = shift;
 871     my $key = shift;
 872
 873     my $jid_id = $self->jid_id($jid);
 874     my $sth = $self->call('get_misc', $self->{id}, $jid_id, $key);
 875     my ($value) = $sth->fetchrow_array;
 876     $sth->finish();
 877     return $value;
 878 }
 879
 880 sub clear_backend {
 881     my $self = shift;
 882     my $destroy_database = $self->sql('destroy_database');
 883     $destroy_database->($self->{username},
 884                         $self->{password},
 885                         $self->{database_name});
 886     log("Destroyed the $self->{database_name} database.");
 887     $self->{dbh} = $self->connect_to_db;
 888     $self->verify_tables;
 889 }
 890
 891 sub remove {
 892     my $self = shift;
 893     my $jid = shift;
 894     my $transport_id = $self->{id};
 895     my $jid_id = $self->jid_id($jid);
 896
 897     log("Unregistering $jid all from $self->{transport_name}");
 898     for my $unregistration_type qw(roster registration namemaps misc
 899                                    avatars registration) {
 900         $self->call("unregister_$unregistration_type",
 901                     $transport_id, $jid_id);
 902     }
 903 }
 904
 905 1;