XDbiMySQL.pm

package XSTAB::Storage::XDbiMySQL;
# This module stores data with DBI::MySQL
#
# $Id: XDbiMySQL.pm,v 1.13 2003/03/23 09:34:31 dranok Exp $
#
# This module is released under the GPL
# Author: DranoK
# Email: dranok@users.sourceforge.net
# Documentation: http://xstab.sourceforge.net
##############################################

NAME

XSTAB::Storage::XDbiMySQL

DESCRIPTION

This storage module will store player records in a MySQL database

INITIALIZATION

use DBI;
use XSTAB::XData;
use strict;

my $VERSION = "0.9";
my @db_rows = ();

PUBLIC METHODS

create ()

This constructor will return the blessed object. Before doing so, it will establish a DBI connection to the MySQL database, and create the sth queries required to manipulate player data (save/load/etc) This is done by first doing a 'SHOW COLUMNS' to find out what information is worth saving or loading. So, if a column in the DB is named 'kills', $Players{ClientID}{kills} will be stored/retrieved from here. Fields prefiexed with the following items are special:

XS_

Any field beginning with XS_ is considered a serialization field. For example, XS_modkills would be the place where $Players{ClientID}{mod_kills} is stored, even though mod_kills is itself a hash. This is done through the XSerialize and XSerialStore functions in the XData module.

XA_

Any field beginning with XA_ is considered an append field. These fields are NOT loaded with the player data, and are saved using MySQL's append functionality. After saving this, the data in the hash is deleted. This is useful for keeping a list of everyone someone killed, for example, without the overhead of transfering an entire serialization field each time. More elegant solutions could be found in more advanced DBs other than MySQL, specifically by nesting tables to remove the need for serialization.

NOTE: Beware of using XA_, for if you do so, your DB table will become HUGE, and sorting through it will become a nightmare.

XI_

Any field beginning with XI_ is completely ignored by XStab.

sub create
{
  my $class = shift;

  my $self = { };
  $self->{dbh} = DBI->connect("DBI:mysql:database=$Global{DB}:host=$Global{DBHost}", "$Global{DBUser}", "$Global{DBPass}")
        or die "Cannot connect: " . $DBI::errstr;

  # Find the fields which need saving
  my $query_sth = $self->{dbh}->prepare("SHOW COLUMNS FROM $Global{DBTable}");
  $query_sth->execute or die "Couldn't run SHOW query!";
  while (my $result = $query_sth->fetchrow_hashref()) {
    if ($result->{Field} ne 'id') {
      push(@db_rows, $result->{Field});
    }
  }

  # Create the insert statement
  my $query = "INSERT INTO $Global{DBTable} (";
  foreach my $field (@db_rows) {
    if ($field =~ /XI_/) {
      next;
    } else {
      $query .= "$field, ";
    }
  }
  $query =~ s/, $//;
  $query .= ") VALUES (";
  foreach my $field (@db_rows) {
    $query .= "?, ";
  }
  $query =~ s/, $//;
  $query .= ")";
  $self->{sth_insert} = $self->{dbh}->prepare($query) or die "Could not perform prepare query!\n";

  # Create the update statement
  $query = "UPDATE $Global{DBTable} SET ";
  foreach my $field (@db_rows) {
    if ($field =~ /XI_/) {
      next;
    } elsif ($field =~ /XA_(.*)/) {
      my $newfield = $1;
      $query .= "$newfield=concat($newfield,?), ";
    } else {
      $query .= "$field=?, ";
    }
  }
  $query =~ s/, $//;
  $query .= " WHERE $Global{uid}=?";
  $self->{sth_update} = $self->{dbh}->prepare($query) or die "Could not perform prepare query!\n";

  # Create the select new player statement
  $query = "SELECT ";
  foreach my $field (@db_rows) {
    if ($field !~ /XA_/ && $field !~ /XI_/) {
      $query .= "$field,";
    }
  }
  $query =~ s/,$//;
  $query .= " FROM $Global{DBTable} WHERE $Global{uid}=?";
  $self->{sth_select} = $self->{dbh}->prepare($query) or die "Could not perform prepare query!\n";

  # Create the delete statement
  $query = "DELETE FROM $Global{DBTable} WHERE $Global{uid}=?";
  $self->{sth_delete} = $self->{dbh}->prepare($query) or die "Could not perform prepare query!\n";

  # Create the admin log statement
  $query = "INSERT INTO $Global{Admin_table} (id, date, admin_id, victim_id, command) VALUES ('', ?, ?, ?, ?)";
  $self->{sth_admin} = $self->{dbh}->prepare($query) or die "Could not perform prepare query!\n";

  bless($self, $class);
  return $self
}

DESTROY ()

Simple deconstructor. Just disconnects the database handle.

sub DESTROY
{
  my $self = shift;

  $self->{dbh}->disconnect();
}

do_admin ()

This method will insert one element from @Admin_queue into the admin log table. The @Admin_queue should have admin commands in it, to create accountability for your admins.

sub do_admin
{
  my $self = shift;

  if (!scalar(@Admin_queue)) {
    return;
  }  
  my @tmpar = split($Global{sp}, pop(@Admin_queue)); 
  $self->{sth_admin}->execute( @tmpar ) or die "Couldn't run INSERT for admin log!\n";
}

do_log (string Message, int Priority)

This is the overloaded do_log function. Purpose is to identify which module is logging the message.

sub do_log
{
  my $string = shift;
  my $prio = shift;
  XSTAB::XData::do_log("Storage::XDbiMySQL: $string", $prio);
}

load_player_hash (int ClientID)

This method will attempt to load ClientID's data from the database using Players{ClientID}{$Global{uid}} as the unique key.

This method will set $Players{ClientID}{use_update} to be '1' so saves will use UPDATE instead of INSERT. If no data is returned from the DB (ie, the player did not exist), this will not be set, thus INSERT will be used. If the exact right things go wrong it is possible for duplicate entires to be created.

sub load_player_hash
{
  my $self = shift;
  my $client = shift;

  $self->{sth_select}->execute($Players{$client}{$Global{uid}}) or die "Couldn't run SELECT to load new player!\n";
  
  while (my $ref = $self->{sth_select}->fetchrow_hashref()) {
    $Players{$client}{use_update} = 1;
    foreach my $elem (@db_rows) {
      if ($elem =~ /XS_(.*)/) {
        my $newfield = $1;
        XSerialStore($client, $newfield, $ref->{$elem});
      } else {
        $Players{$client}{$elem} = $ref->{$elem};
      }
    }
  }
  do_log("Loaded player: $Players{$client}{name}", 3);
}

save_player_hash (int ClientID)

This method will save ClientID's data to the database. If $Players{ClientID}{use_update} is true, UPDATE is used instead of INSERT. If it is false, an INSERT is performed, after which $Players{ClientID}{use_update} is set to '1'.

sub save_player_hash
{
  my $self = shift;
  my $client = shift;

  if (!$Players{$client}{$Global{uid}}) {
    return;
  }

  my @tmpar = ();
  foreach my $elem (@db_rows) {
    if ($elem =~ /XI_/) {
      next;
    } elsif ($elem =~ /XS_(.*)/) {
      my $newfield = $1;
      if ($Players{$client}{$newfield}) {
        my $tstr = XSerialize($client, $newfield);
        if ($tstr) {
          push(@tmpar, $tstr);
        } else {
          push(@tmpar, '');
        }
      } else {
        push(@tmpar, '');
      }
    } elsif ($elem =~ /XA_(.*)/) {
      my $newfield = $1;
      if ($Players{$client}{$newfield}) {
        push(@tmpar, $Players{$client}{$newfield});
        delete $Players{$client}{$newfield};
      }
    } else {
      if ($Players{$client}{$elem}) {
        push(@tmpar, $Players{$client}{$elem});
      } else {
        push(@tmpar, '0');
      }
    }
  }
  if ($Players{$client}{use_update}) {
    push(@tmpar, $Players{$client}{$Global{uid}});
    $self->{sth_update}->execute( @tmpar ) or die "Couldn't run UPDATE to update player with uid $Players{$client}{$Global{uid}}!\n";
  } else {
    $self->{sth_insert}->execute( @tmpar ) or die "Couldn't run INSERT to add player!\n";
    $Players{$client}{use_update} = 1;
  }
  do_log("Saved Player: $Players{$client}{name}", 3);
}

delete_record (int ClientID)

This method will delete ClientID's database record. In fact, it will delete every record whose unique ID is $Players{ClientID}{$Global{uid}}.

sub delete_record
{
  my $self = shift;
  my $client = shift;
  
  if ($Players{$client}{$Global{uid}}) {
    $self->{sth_delete}->execute( $Players{$client}{$Global{uid}} );
  }
}

1;

AUTHOR

This module was coded by DranoK and is part of the core XStab modules. You may directly contact DranoK at dranok@users.sourceforge.net, or by posting to the forums at:

        http://www.oltl.net/forums/forumdisplay.php?s=&forumid=25