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     my $jid_id = $self->jid_id($user_jid, 1);
 698     my $legacy_id = $self->legacy_id($legacy_username);
 699
 700     $self->call('store_username_mapping',
 701                 $self->{id}, $jid_id, $legacy_id,
 702                 $mapped_jid);
 703 }
 704
 705 sub set_avatar {
 706     my $self = shift;
 707     my $user_jid = shift;
 708     my $legacy_username = shift;
 709     my $avatar_png_base_64 = shift;
 710
 711     my $jid_id = $self->jid_id($user_jid);
 712     my $legacy_id = $self->legacy_id($legacy_username);
 713
 714     if ($avatar_png_base_64) {
 715         $self->call('set_avatar', $self->{id},
 716                     $jid_id, $legacy_id, $avatar_png_base_64);
 717     } else {
 718         $self->call('delete_avatar', $self->{id},
 719                     $jid_id, $legacy_id);
 720     }
 721 }
 722
 723 sub get_avatar {
 724     my $self = shift;
 725     my $user_jid = shift;
 726     my $legacy_username = shift;
 727
 728     my $jid_id = $self->jid_id($user_jid);
 729     my $legacy_id = $self->legacy_id($legacy_username);
 730
 731     my ($avatar) = $self->call('get_avatar', $self->{id},
 732                                $jid_id, $legacy_id)->
 733                           fetchrow_array;
 734     return $avatar;
 735 }
 736
 737 sub get_roster {
 738     my $self = shift;
 739     my $user_jid = shift;
 740
 741     my $jid_id = $self->jid_id($user_jid);
 742
 743     my $roster = {};
 744     my $sth = $self->call('get_roster', $self->{id}, $jid_id);
 745
 746     while (my $roster_entry = $sth->fetchrow_hashref) {
 747         $roster->{$roster_entry->{legacy}} = $roster_entry->{state};
 748     }
 749
 750     return $roster;
 751 }
 752
 753 sub set_roster {
 754     my $self = shift;
 755     my $user_jid = shift;
 756     my $new_roster = shift;
 757
 758     my $jid_id = $self->jid_id($user_jid);
 759     $self->call('clear_roster_for_jid', $self->{id}, $jid_id);
 760
 761     while (my ($legacy_name, $sub_value) = each %$new_roster) {
 762         $self->set_roster_user_state($user_jid, $legacy_name, $sub_value);
 763     }
 764 }
 765
 766 sub set_roster_user_state {
 767     my $self = shift;
 768     my $user_jid = shift;
 769     my $legacy_username = shift;
 770     my $state = shift;
 771
 772     my $jid_id = $self->jid_id($user_jid);
 773     my $legacy_id = $self->legacy_id($legacy_username);
 774
 775     if ($state == $self->unsubscribed) {
 776         $self->call('remove_roster_state',
 777                     $self->{id}, $jid_id, $legacy_id);
 778     } else {
 779         $self->call('set_roster_state', $self->{id},
 780                     $jid_id, $legacy_id, $state);
 781     }
 782 }
 783
 784 sub get_roster_user_state {
 785     my $self = shift;
 786     my $user_jid = shift;
 787     my $legacy_username = shift;
 788
 789     my $jid_id = $self->jid_id($user_jid);
 790     my $legacy_id = $self->legacy_id($legacy_username);
 791
 792     my $sth = $self->call('get_roster_state', $self->{id},
 793                           $jid_id, $legacy_id);
 794     my ($state) = $sth->fetchrow_array();
 795     $sth->finish();
 796     $state ||= $self->unsubscribed;
 797     return $state;
 798 }
 799
 800 sub all_jids {
 801     my $self = shift;
 802
 803     my $sql = $self->sql("all_jids");
 804     my $jids = $self->{dbh}->selectcol_arrayref($sql, undef, $self->{id});
 805     return $jids;
 806 }
 807
 808 sub all_mappings {
 809     my $self = shift;
 810     my $jid = shift;
 811
 812     my $jid_id = $self->jid_id($jid);
 813     my $name_mappings = {};
 814
 815     my $sth = $self->call('all_mappings', $self->{id}, $jid_id);
 816     while (my ($key, $value) = $sth->fetchrow_array) {
 817         $name_mappings->{$key} = $value;
 818     }
 819
 820     return $name_mappings;
 821 }
 822
 823 sub all_misc {
 824     my $self = shift;
 825     my $jid = shift;
 826
 827     my $jid_id = $self->jid_id($jid);
 828     my $misc = {};
 829
 830     my $sth = $self->call('all_misc', $self->{id}, $jid_id);
 831     while (my ($key, $value) = $sth->fetchrow_array) {
 832         $misc->{$key} = $value;
 833     }
 834
 835     return $misc;
 836 }
 837
 838 sub all_avatars {
 839     my $self = shift;
 840     my $jid = shift;
 841
 842     my $jid_id = $self->jid_id($jid);
 843     my $avatars = {};
 844
 845     my $sth = $self->call('all_avatars', $self->{id}, $jid_id);
 846     while (my ($legacy, $avatar) = $sth->fetchrow_array) {
 847         $avatars->{$legacy} = $avatar;
 848     }
 849     $sth->finish();
 850
 851     return $avatars;
 852 }
 853
 854 sub set_misc {
 855     my $self = shift;
 856     my $jid = shift;
 857     my $key = shift;
 858     my $value = shift;
 859
 860     my $jid_id = $self->jid_id($jid);
 861     $self->call('set_misc', $self->{id}, $jid_id, $key, $value);
 862 }
 863
 864 sub get_misc {
 865     my $self = shift;
 866     my $jid = shift;
 867     my $key = shift;
 868
 869     my $jid_id = $self->jid_id($jid);
 870     my $sth = $self->call('get_misc', $self->{id}, $jid_id, $key);
 871     my ($value) = $sth->fetchrow_array;
 872     $sth->finish();
 873     return $value;
 874 }
 875
 876 sub clear_backend {
 877     my $self = shift;
 878     my $destroy_database = $self->sql('destroy_database');
 879     $destroy_database->($self->{username},
 880                         $self->{password},
 881                         $self->{database_name});
 882     log("Destroyed the $self->{database_name} database.");
 883     $self->{dbh} = $self->connect_to_db;
 884     $self->verify_tables;
 885 }
 886
 887 sub remove {
 888     my $self = shift;
 889     my $jid = shift;
 890     my $transport_id = $self->{id};
 891     my $jid_id = $self->jid_id($jid);
 892
 893     log("Unregistering $jid all from $self->{transport_name}");
 894     for my $unregistration_type qw(roster registration namemaps misc
 895                                    avatars registration) {
 896         $self->call("unregister_$unregistration_type",
 897                     $transport_id, $jid_id);
 898     }
 899 }
 900
 901 1;