Full support for Postgres, MySQL and SQLite3 + minor bug fixes

13 files changed, 587 insertions, 179 deletions

diff --git a/README b/README
index 6d69b59..aabd89e 100755
--- a/README
+++ b/README
@@ -17,18 +17,22 @@ DESCRIPTION
   333networks can be found online at 
         http://wiki.333networks.com/index.php/MasterServer
 
+AUTHOR
+  Darkelarious
+  http://333networks.com
+  darkelarious@333networks.com
+
 REQUIREMENTS
-  - Postgresql or SQLite3
+  - Postgresql, MySQL or SQLite3
   - Perl 5.10 or above
   - The following CPAN modules:
-      FCGI
       DBI
-      DBD::Pg or DBD::SQLite
+      DBD::Pg / DBD::SQLite / DBD::mysql
       AnyEvent
       AnyEvent::Handle::UDP
-      IP::Country
+      IP::Country::Fast
   - screen (or another terminal multiplexer, optional)
-  
+
 INSTALL
 
   THE MASTER SERVER IS WRITTEN ON LINUX. IF YOU WANT TO RUN THE SOFTWARE IN 
@@ -41,9 +45,9 @@ INSTALL
   
   The masterserver stores server addresses in the database. This database must
   be created manually. SQLite3 users should not forget to chmod the database 
-  file with read/write access. The tables to be created can be found in the sql 
-  in the folder "data/database". Support for multiple database drivers is 
-  slowly being added. Use the database type of choice and created the tables.
+  file with read/write access. The tables to be created can be found in the 
+  "data/database" folder in the tables-Pg/SQLite/mysql.sql file. Choose your 
+  prefered database driver and created the tables as described below.
   
 CONFIGURATION
 
@@ -53,22 +57,37 @@ CONFIGURATION
   
   Masterserver HOST information
   
-  Fill in your contact details here if you want to be able to synchronize with
-  other masterservers. Though the synchronization process also works without
-  your contact details, it is preferred and appreciated if you allow other
+  Fill in your contact details here to be able to synchronize with other 
+  masterservers. Though the synchronization process also works without your 
+  contact details, it is preferred and appreciated if you allow other
   masterservers to synchronize with you too. In addition, third parties may
   want to retrieve the status of your masterserver. When remote servers query
   for your contact information, they can see who hosts this masterserver, which
-  build you are running and which games you currently support.
+  build you are running and which games you currently support. This is highly
+  recommended and much appreciated.
   
   Database login information
   
-  The masterserver slowly starts to support more and more different database 
-  types. In the module "lib/MasterServer/Databases" you can see which types are
-  currently working. Examples in use (only one option should be used):
+  The masterserver supports different database types. In the module 
+  "lib/MasterServer/Databases" you can see which types are currently supporting
+  out of the box. To choose a database type, specify the "dblogin" variable:
+  
+  # Postgresql
+  dblogin => ['dbi:Pg:dbname=databasename', 'user', 'password'],
+ 
+  # SQLite
+  dblogin => ["dbi:SQLite:dbname=$ROOT/data/databasename.db",'',''], 
+
+  # MySQL
+  dblogin => ["dbi:mysql:database=databasename;host=localhost;port=3306",
+                                                            'user','password'],
   
-    Postgresql: ['dbi:Pg:dbname=database_name', 'user', 'password']
-    SQLite:     ["dbi:SQLite:dbname=$ROOT/data/database_name.db",'','']
+  Keep in mind that the database needs to be created manually. That means that 
+  you first have to create your postgres/mysql user and grant permissions as
+  described in the proprietary manuals that come with your database 
+  installation. After that, you have to insert the tables manually, which are 
+  provided in the "data/database" folder. The masterserver script requires
+  read-and-write permissions on the SQLite database file.
   
   Logging
   
@@ -88,10 +107,10 @@ CONFIGURATION
     suppress => " load "
     suppress => " load beacon secure hostname "
   
-  More message types can be suppressed, where the types are separated by spaces.
-  If you want to log a lot of events, you could consider rotating the logs every
-  day, week, month or year. The 'log_rotate' allows you to store events in
-  different files.
+  More message types can be suppressed, where the types are separated by spaces 
+  as shown in the second example. If you want to log a lot of events, you could 
+  consider rotating the logs every day, week, month or year. The 'log_rotate' 
+  allows you to store events in different files with specified interval.
   
   Network settings
   
@@ -108,7 +127,7 @@ CONFIGURATION
   All GameSpy protocol games communicate according to a protocol that requires
   servers and clients to authenticate each other. As far as 333networks are 
   concerned, the authentication ciphers (keys) are confidential and intellectual
-  property. 
+  property of the individual game companies. 
   
   If you have a configuration file with keys, you can simply import this list
   at the bottom of the configuration file as shown there. If you do not have the
@@ -134,16 +153,9 @@ CONFIGURATION
   the masterserver can query the addresses individually, to determine whether
   they are valid game servers. Change with "beacon_checker_enabled".
   
-  For 333networks and the site, individual UT servers are queried for their 
-  server statistics information. This service is not really necessary for any 
-  other purpose than showing server information with a few minutes delay on your
-  site. It can be adapted on your own accord to do the same for other games.
-  You should keep "utserver_query_enabled" on 0 unless you have specific use
-  for the gathered data.
-  
-  The processes are all started with a one minute delay. This allows the master-
-  server to receive most beacons (every minute). All times in the configuration
-  file are listed in [s]econds.
+  The processes are all started with a delay. This allows the masterserver some 
+  grace time and to receive most beacons (every minute). All time variables in 
+  the configuration file are listed in [s]econds.
 
   Query UCC Applets
   
@@ -151,18 +163,20 @@ CONFIGURATION
   addresses. This should not be done without permission of the UCC applet
   hosts, it's impolite to do so without asking. The new servers are added to
   the "pending" list, where they wait to be queried individually as described
-  above. If "beacon_checker_enabled" is '0', this function will probably not
-  work properly. Change with "master_applet_enabled".
+  above. If "beacon_checker_enabled" is disabled, this function will NOT work 
+  properly. Enable/Disable with "master_applet_enabled".
   
   Synchronization settings  
   
   Synchronization between masterservers allows you to receive the list from 
   other masterservers, but in return, you allow other masterservers to query you 
   too, with the same request. If you do not wish this to happen, synchronization 
-  can be disabled. Newly received servers are added to the "pending" list as 
-  described above. Change with "sync_enabled". Please note that this will not 
-  prevent others from obtaining the server lists. Attempting to disable this is 
-  hypocrite and ambiguous, as "regular" clients do the same.
+  can be disabled. 
+  Newly received servers are added to the "pending" list as described above. 
+  Change with "sync_enabled". Please note that this will not prevent others 
+  from obtaining the server lists. Attempting to disable this is hypocrite and 
+  ambiguous [citation needed], as "regular" clients do the same to get the 
+  serverlist.
   
 RUNNING
 
@@ -180,6 +194,31 @@ RUNNING
   333networks is not responsible for your masterserver querying (or spamming)
   game servers and/or masterservers. Your configuration is YOUR responsibility!
 
+KNOWN ISSUES
+  There are a few known issues that will be resolved in future versions. The
+  following issues are listed and do not need to be reported.
+  
+  Slow database: currently, database requests are blocking. On slow hardware,
+  these requests may take enough time to miss/deny incoming beacons and list
+  requests. No solution available in this version. Proposed solution: AnyEvent's
+  asynchronous database driver(s).
+  
+  No servers after downtime: when the masterserver has been off or offline for
+  more than three hours, the server list does not update or only updates a 
+  limited amount of servers. This is caused by not removing and NOT re-querying
+  old servers after they appear unresponsive for more than three hours. Proposed
+  solution: after more than three hours downtime/off time, clear the server list
+  from the database workbench: ">DELETE FROM SERVERLIST;". Will be fixed in the
+  next update/release.
+
+  No servers found after syncing with 333networks or others: the syncing 
+  protocol also requires authentication. We do not want authorized people mass-
+  spamming our masterservers with sync request to determine whether the server
+  is online (yes, this actually happens -- use the \about\ query for that!) or
+  to keep the list unnecessary updated. Sync requests should not be executed 
+  more than 3-4 times per hour!
+  If you want to sync with us, please email 333networks
+
 COPYING
   Copyright (c) 2005-2015 Darkelarious & 333networks.com
 
diff --git a/data/database/tables-Pg.sql b/data/database/tables-Pg.sql
index 0778797..abe5744 100755
--- a/data/database/tables-Pg.sql
+++ b/data/database/tables-Pg.sql
@@ -14,13 +14,6 @@ CREATE TABLE serverlist(
   updated         timestamptz      NOT NULL DEFAULT NOW()
 );
 
--- TODO
-CREATE TABLE user_stats(
-  gamename        VARCHAR(50)      NOT NULL DEFAULT ' ',
-  requests        INTEGER          NOT NULL DEFAULT 1,
-  beacons         INTEGER          NOT NULL DEFAULT 1
-);
-
 CREATE TABLE pending(
   id              SERIAL    UNIQUE NOT NULL PRIMARY KEY,
   ip              inet             NOT NULL DEFAULT '0.0.0.0',
@@ -31,48 +24,3 @@ CREATE TABLE pending(
   enctype         INTEGER          NOT NULL DEFAULT 0,
   added           timestamptz      NOT NULL DEFAULT NOW()
 );
-
-CREATE TABLE server_info(
-  server_id           SERIAL       REFERENCES serverlist(id),
-  minnetver           INTEGER      NOT NULL DEFAULT 400,
-  gamever             INTEGER      NOT NULL DEFAULT 400,
-  location            INTEGER      NOT NULL DEFAULT 0,
-  listenserver        BOOLEAN      NOT NULL DEFAULT TRUE,
-  hostport            INTEGER      NOT NULL DEFAULT 7777,
-  hostname            varchar(200) NOT NULL DEFAULT '',
-  adminname           varchar(200) NOT NULL DEFAULT '',
-  adminemail          varchar(300) NOT NULL DEFAULT '',
-  password            BOOLEAN      NOT NULL DEFAULT FALSE,
-  gametype            varchar(50)  NOT NULL DEFAULT '',
-  gamestyle           varchar(50)  NOT NULL DEFAULT 'Normal',
-  changelevels        BOOLEAN      NOT NULL DEFAULT FALSE,
-  maptitle            varchar(100) NOT NULL DEFAULT 'Unknown',
-  mapname             varchar(100) NOT NULL DEFAULT '',
-  numplayers          INTEGER      NOT NULL DEFAULT 0,
-  maxplayers          INTEGER      NOT NULL DEFAULT 0,
-  minplayers          INTEGER      NOT NULL DEFAULT 0,
-  botskill            varchar(30)  NOT NULL DEFAULT 'Novice',
-  balanceteams        BOOLEAN      NOT NULL DEFAULT FALSE,
-  playersbalanceteams BOOLEAN      NOT NULL DEFAULT FALSE,
-  friendlyfire        varchar(10)  NOT NULL DEFAULT '0%',
-  maxteams            INTEGER      NOT NULL DEFAULT 4,
-  timelimit           INTEGER      NOT NULL DEFAULT 0,
-  goalteamscore       INTEGER      NOT NULL DEFAULT 0,
-  fraglimit           INTEGER      NOT NULL DEFAULT 0,
-  mutators            TEXT         NOT NULL DEFAULT 'None'
-);
-
-CREATE TABLE player_info(
-  server_id SERIAL        NOT NULL,
-  player    varchar(40)   NOT NULL DEFAULT 'Player',
-  team      INTEGER       NOT NULL DEFAULT 255,
-  frags     INTEGER       NOT NULL DEFAULT 0,
-  mesh      varchar(100)  NOT NULL DEFAULT '',
-  skin      varchar(100)  NOT NULL DEFAULT '',
-  face      varchar(100)  NOT NULL DEFAULT '',
-  ping      INTEGER       NOT NULL DEFAULT 0,
-  ngsecret  varchar(10)   NOT NULL DEFAULT 'false',
-  updated   timestamptz   NOT NULL DEFAULT NOW()
-);
-
-
diff --git a/data/database/tables-SQLite.sql b/data/database/tables-SQLite.sql
index 37431bb..cd904ed 100755
--- a/data/database/tables-SQLite.sql
+++ b/data/database/tables-SQLite.sql
@@ -24,49 +24,3 @@ CREATE TABLE pending(
   enctype         INTEGER          NOT NULL DEFAULT 0,
   added           timestamptz      NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
-
-CREATE TABLE server_info(
-  server_id           INTEGER,
-  minnetver           INTEGER      NOT NULL DEFAULT 400,
-  gamever             INTEGER      NOT NULL DEFAULT 400,
-  location            INTEGER      NOT NULL DEFAULT 0,
-  listenserver        BOOLEAN      NOT NULL DEFAULT 1,
-  hostport            INTEGER      NOT NULL DEFAULT 7777,
-  hostname            varchar(200) NOT NULL DEFAULT '',
-  adminname           varchar(200) NOT NULL DEFAULT '',
-  adminemail          varchar(300) NOT NULL DEFAULT '',
-  password            BOOLEAN      NOT NULL DEFAULT 0,
-  gametype            varchar(50)  NOT NULL DEFAULT '',
-  gamestyle           varchar(50)  NOT NULL DEFAULT 'Normal',
-  changelevels        BOOLEAN      NOT NULL DEFAULT 0,
-  maptitle            varchar(100) NOT NULL DEFAULT 'Unknown',
-  mapname             varchar(100) NOT NULL DEFAULT '',
-  numplayers          INTEGER      NOT NULL DEFAULT 0,
-  maxplayers          INTEGER      NOT NULL DEFAULT 0,
-  minplayers          INTEGER      NOT NULL DEFAULT 0,
-  botskill            varchar(30)  NOT NULL DEFAULT 'Novice',
-  balanceteams        BOOLEAN      NOT NULL DEFAULT 0,
-  playersbalanceteams BOOLEAN      NOT NULL DEFAULT 0,
-  friendlyfire        varchar(10)  NOT NULL DEFAULT '0%',
-  maxteams            INTEGER      NOT NULL DEFAULT 4,
-  timelimit           INTEGER      NOT NULL DEFAULT 0,
-  goalteamscore       INTEGER      NOT NULL DEFAULT 0,
-  fraglimit           INTEGER      NOT NULL DEFAULT 0,
-  mutators            TEXT         NOT NULL DEFAULT 'None',
-  FOREIGN KEY(server_id) REFERENCES serverlist(id)
-);
-
-CREATE TABLE player_info(
-  server_id INTEGER       PRIMARY KEY AUTOINCREMENT,
-  player    varchar(40)   NOT NULL DEFAULT 'Player',
-  team      INTEGER       NOT NULL DEFAULT 255,
-  frags     INTEGER       NOT NULL DEFAULT 0,
-  mesh      varchar(100)  NOT NULL DEFAULT '',
-  skin      varchar(100)  NOT NULL DEFAULT '',
-  face      varchar(100)  NOT NULL DEFAULT '',
-  ping      INTEGER       NOT NULL DEFAULT 0,
-  ngsecret  varchar(10)   NOT NULL DEFAULT 'false',
-  updated   timestamptz   NOT NULL DEFAULT CURRENT_TIMESTAMP
-);
-
-
diff --git a/data/database/tables-mysql.sql b/data/database/tables-mysql.sql
new file mode 100755
index 0000000..9c1d2fc
--- /dev/null
+++ b/data/database/tables-mysql.sql
@@ -0,0 +1,28 @@
+CREATE TABLE serverlist(
+  id              INTEGER          NOT NULL AUTO_INCREMENT,
+  ip              VARCHAR(15)      NOT NULL DEFAULT '0.0.0.0',
+  port            INTEGER          NOT NULL DEFAULT 0,
+  gamename        VARCHAR(100)     NOT NULL DEFAULT ' ',
+  gamever         VARCHAR(50)      NOT NULL DEFAULT ' ',
+  hostname        VARCHAR(100)     NOT NULL DEFAULT ' ',
+  hostport        INTEGER          NOT NULL DEFAULT 0,
+  country         VARCHAR(5),
+  b333ms          BOOLEAN          NOT NULL DEFAULT 0,
+  blacklisted     BOOLEAN          NOT NULL DEFAULT 0,
+  added           TIMESTAMP        NOT NULL DEFAULT NOW(),
+  beacon          TIMESTAMP        NOT NULL DEFAULT NOW(),
+  updated         TIMESTAMP        NOT NULL DEFAULT NOW(),
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE pending(
+  id              INTEGER          NOT NULL AUTO_INCREMENT,
+  ip              VARCHAR(15)      NOT NULL DEFAULT '0.0.0.0',
+  beaconport      INTEGER          NOT NULL DEFAULT 0,
+  heartbeat       INTEGER          NOT NULL DEFAULT 0,
+  gamename        VARCHAR(25)      NOT NULL DEFAULT ' ',  
+  secure          VARCHAR(12)      NOT NULL DEFAULT ' ',
+  enctype         INTEGER          NOT NULL DEFAULT 0,
+  added           TIMESTAMP        NOT NULL DEFAULT NOW(),
+  PRIMARY KEY (id)
+);
diff --git a/data/masterserver-config.pl b/data/masterserver-config.pl
index e8fe418..d5d6091 100755
--- a/data/masterserver-config.pl
+++ b/data/masterserver-config.pl
@@ -1,11 +1,10 @@
 package MasterServer;
 our (%S, $ROOT);
 our %S = (
-
 ################################################################################
 # Masterserver HOST information                                                #
 #                                                                              #
-# Please fill in your contact details here, for two-way synchronization and    #
+# Please fill in YOUR contact details here, for two-way synchronization and    #
 # for your users to be able to contact you.                                    #
 #                                                                              #
 # Values may not contain backslashes and quotes: no \ or \\, nor ' and ",      #
@@ -16,7 +15,7 @@ our %S = (
   # example: 333networks -- http://master.333networks.com -- info@333networks.com
   contact_details => '333networks -- http://master.333networks.com -- info@333networks.com',
   
-  # host address
+  # host address, shows when using sync-options
   masterserver_address => 'master.333networks.com',
   
 ################################################################################
@@ -24,18 +23,18 @@ our %S = (
 #                                                                              #
 # Login credentials for the database that was created manually before.         #
 # Yes, that means that you need to create the database and tables on your own. #
-# Use only one option: Postgresql, SQLite (or future: MySQL)                   #
+# Use only one option: Postgresql, SQLite or MySQL                             #
 #                                                                              #
 ################################################################################
 
   # Postgresql
-  dblogin => ['dbi:Pg:dbname=database_name', 'username', 'password'],
+  #dblogin => ['dbi:Pg:dbname=databasename', 'user', 'password'],
  
   # SQLite
-  #dblogin => ["dbi:SQLite:dbname=$ROOT/data/database_name.db",'',''], 
+  #dblogin => ["dbi:SQLite:dbname=$ROOT/data/databasename.db",'',''], 
 
   # MySQL
-  #dblogin => ["dbi:mysql:database=database_name;host=localhost;port=3306",'user','password'],
+  dblogin => ["dbi:mysql:database=databasename;host=localhost;port=3306",'user','password'],
   
 ################################################################################
 # Logging configuration                                                        #
@@ -58,7 +57,7 @@ our %S = (
   suppress  =>  "none", # show all entries
   
   # suppress the most annoying messages
-  #suppress  => "add update delete read tcp udp query secure hostname",
+  # suppress  => "add update delete read tcp udp secure query beacon hostname",
   
   # print database errors
   db_print => 0,
@@ -103,30 +102,20 @@ our %S = (
 # Wait 60+ seconds before starting timers for incoming beacons                 #
 #                                                                              #
 ################################################################################
-  
-  # Query UCC-based applets
-  master_applet_enabled => 1,
-  master_applet_time    => [90, 1200],
 
   # Synchronization with other 333networks-based masterservers
   sync_enabled  => 1, # 0 = disabled
-  sync_time     => [180, 1200],
+  sync_time     => [60, 900],
+  
+  # Query UCC-based applets
+  master_applet_enabled => 1, # 0 = disabled
+  master_applet_time    => [90, 900],
 
   # Beacon Checker query all addresses in the database, requesting "basic" and 
   # "info". Execute at least twice per hour, to avoid time-outs in own data.
   # disabling breaks support for certain games [citation needed].
   beacon_checker_enabled  => 1,
-  beacon_checker_time     => [60, 0.5, 1800],
-
-  # Collect server information for the 333networks main site. Identical
-  # mechanism as the Beacon Checker. Disable when not interested in UT info
-  # for your website.
-  # NB: with some work it can be adapted to work with any other game. Own risk.
-  utserver_query_enabled  => 1,
-  utserver_query_time     => [75, 0.15, 240],
-  
-  # Maintenance duties like cleaning out old servers/players
-  maintenance_time => [3600, 300],
+  beacon_checker_time     => [120, 0.5, 1800],
 
 ################################################################################
 # Synchronization settings                                                     #
@@ -136,7 +125,7 @@ our %S = (
 #                                                                              #
 ################################################################################
   
-  # additional masters to sync with (in addition to db-entries)
+  # masters to sync with
   sync_masters  => [
         { address => "master.333networks.com", port => 28900 }, # default
         { address => "master.noccer.de",       port => 28900 },
@@ -144,10 +133,10 @@ our %S = (
         { address => "master.errorist.tk",     port => 28900 },
   ],
   
-  # sync all or selected games?
+  # sync all or only selected games?
   # 0 = all, 1 + gamenames = selected
+  #sync_games => [1, "ut unreal"], # example with only UT/Unreal syncing
   sync_games => [0],
-  #sync_games => [1, "ut unreal"],
         
 ################################################################################
 # Query UCC Applets                                                            #
@@ -160,19 +149,19 @@ our %S = (
   # remote applets to be queried
   master_applet => [
     {ip => "utmaster.epicgames.com",       port => 28900, game => "ut"},
-    {ip => "master.hypercoop.tk",          port => 28900, game => "unreal"},
     {ip => "master.newbiesplayground.net", port => 28900, game => "unreal"},
     {ip => "master.hlkclan.net",           port => 28900, game => "unreal"},
   ],
 
-); #end %S
+); #end %S config
+
 
 ################################################################################
 #                                                                              #
 # Supported Games.                                                             #
 #                                                                              #
-# List of games that are supported by the 333networks masterserver. Note that  #
-# adding a game does not necessarily mean that suddenly the protocol will      #
+# List of games of which 333networks has the authentication ciphers. Note that #
+# adding a game here does not necessarily mean that suddenly the protocol will #
 # be supported.                                                                #
 #                                                                              #
 ################################################################################
diff --git a/lib/MasterServer/Core/Core.pm b/lib/MasterServer/Core/Core.pm
index 0e0cd58..4cd6063 100755
--- a/lib/MasterServer/Core/Core.pm
+++ b/lib/MasterServer/Core/Core.pm
@@ -61,7 +61,7 @@ sub main {
     my @db_type = split(':', $self->{dblogin}->[0]);
 
     # format supported?
-    if ( "Pg SQLite" =~ m/$db_type[1]/i) {
+    if ( "Pg SQLite mysql" =~ m/$db_type[1]/i) {
       
       # inform us what DB we try to load
       $self->log("load","Loading $db_type[1] database module.");
diff --git a/lib/MasterServer/Core/Version.pm b/lib/MasterServer/Core/Version.pm
index aba692f..ccf9491 100755
--- a/lib/MasterServer/Core/Version.pm
+++ b/lib/MasterServer/Core/Version.pm
@@ -23,18 +23,17 @@ sub version {
   # these fields does not count as a significant alteration.
   #
   # -- addition to the LICENCE, you are only allowed to modify these lines
-  # if you send Darkelarious a postcard or email with your compliments or,
-  # in case of a company editing, a letter of (re)commendation.
+  # if you send Darkelarious a postcard or email with your compliments.
   #
 
   # master type
-  $self->{build_type}     = "333networks Masterserver-Perl (Pg-SQLite) 20150519208";
+  $self->{build_type}     = "333networks Masterserver-Perl (Pg-SQLite-MySQL) 20151108209";
   
   # version
-  $self->{build_version}  = "2.0.8";
+  $self->{build_version}  = "2.0.9";
   
   # date yyyy-mm-dd
-  $self->{build_date}     = "2015-05-19";
+  $self->{build_date}     = "2015-11-08";
   
   #author, email
   $self->{build_author}   = "Darkelarious, darkelarious\@333networks.com";
diff --git a/lib/MasterServer/Database/mysql/dbBeacon.pm b/lib/MasterServer/Database/mysql/dbBeacon.pm
new file mode 100755
index 0000000..fd956b7
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbBeacon.pm
@@ -0,0 +1,203 @@
+
+package MasterServer::Database::mysql::dbBeacon;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| add_beacon
+                  add_pending
+                  remove_pending 
+                  get_pending_beacon 
+                  get_pending_info
+                  get_next_pending |;
+
+################################################################################
+## Update beacon in serverlist or pending list. Add if beacon does not exist in
+## either list. Return 0,1,2 if success in adding or -1 on error.
+################################################################################
+sub add_beacon {
+  my ($self, $ip, $beaconport, $heartbeat, $gamename, $secure) = @_;
+
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "UPDATE serverlist 
+      SET beacon   = NOW(),
+          updated  = NOW(),
+          gamename = ?,
+          b333ms   = 1
+      WHERE ip = ? 
+      AND port = ?",
+      undef, lc $gamename, $ip, $heartbeat);
+  
+  # notify
+  $self->log("update", "beacon heartbeat for $ip:$heartbeat") if ($u > 0);
+  
+  # if serverlist was updated return 0
+  return 0 if ($u > 0);
+
+  # if it is already in the pending list, update it with a new challenge
+  $u = $self->{dbh}->do(
+     "UPDATE pending 
+      SET added       = NOW(),
+          beaconport  = ?,
+          gamename    = ?,
+          secure      = ?
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $beaconport, lc $gamename, $secure, $ip, $heartbeat);  
+                            
+  # notify
+  $self->log("update", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
+  
+  # beacon was already in pending list and was updated
+  return 1 if ($u > 0);
+  
+  # if not found, add it
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (
+          ip, 
+          beaconport, 
+          heartbeat, 
+          gamename, 
+          secure) 
+      SELECT ?, ?, ?, ?, ?",
+      undef, $ip, $beaconport, $heartbeat, lc $gamename, $secure);
+                      
+   # notify 
+   $self->log("add", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
+   
+   # it was added to pending
+   return 2 if ($u > 0);
+   
+  # or else report error
+  $self->log("error", "an error occurred adding beacon $ip:$beaconport with $gamename:$heartbeat to the pending list");
+  return -1;
+}
+
+################################################################################
+## Add an address to the database that was obtained via a method other than 
+## an udp beacon. Return 0,1,2 if success in adding or -1 on error.
+################################################################################
+sub add_pending {
+  my ($self, $ip, $port, $gamename, $secure) = @_;
+
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "UPDATE serverlist 
+      SET updated = NOW()
+      WHERE ip = ? 
+      AND port = ?",
+      undef, $ip, $port);
+
+  # notify
+  $self->log("update", "updated serverlist with $ip:$port") if ($u > 0);
+  
+  # if updated, return 0
+  return 0 if ($u > 0);
+
+  # if it is already in the pending list, update it with a new challenge
+  $u = $self->{dbh}->do(
+     "UPDATE pending 
+      SET added  = NOW(),
+          secure = ?
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $secure, $ip, $port);
+      
+  # notify
+  $self->log("update", "updated pending with $ip:$port") if ($u > 0);
+  
+  # return 1 if updated
+  return 1 if ($u > 0);
+  
+  # if not found, add it
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (
+          ip, 
+          heartbeat, 
+          gamename, 
+          secure) 
+      SELECT ?, ?, ?, ?",
+      undef, $ip, $port, $gamename, $secure);
+      
+   # notify
+   $self->log("add", "$ip:$port added pending $gamename") if ($u > 0);
+   
+   # return 2 if added new
+   return 2 if ($u > 0);
+   
+  # else
+  return -1;
+}
+
+################################################################################
+## Remove an entry from the pending list. Returns 0 if removed or -1 in case
+## of error(s).
+################################################################################
+sub remove_pending {
+  my ($self, $id) = @_;
+  
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "DELETE FROM pending 
+      WHERE id = ?", 
+      undef, $id);
+  
+  # notify 
+  $self->log("delete", "removed pending id $id from pending") if ($u > 0);
+  
+  # it was removed from pending
+  return 2 if ($u > 0);
+  
+  # or else report error
+  $self->log("error", "error deleting server $id from pending");
+  return -1;
+}
+
+################################################################################
+## Get pending server by ip, beacon port. Returns * or undef
+################################################################################
+sub get_pending_beacon {
+  my ($self, $ip, $port) = @_;
+  
+  # if address is in list, update the timestamp
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT * FROM pending
+      WHERE ip = ?
+      AND beaconport = ?",
+      undef, $ip, $port)->[0];
+}
+
+################################################################################
+## Get pending server by ip, heartbeat port. Returns * or undef
+################################################################################
+sub get_pending_info {
+  my ($self, $ip, $port) = @_;
+  
+  # if address is in list, update the timestamp
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT * FROM pending
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $ip, $port)->[0];
+}
+
+################################################################################
+## Get server info from any entry with an id higher than the provided one. The
+## server is added to pending at least 15 seconds ago. Returns info or undef.
+################################################################################
+sub get_next_pending {
+  my ($self, $id) = @_;
+  
+  # get 1 pending id that is older than 15s
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT id, ip, heartbeat, secure FROM pending
+      WHERE added < NOW() - INTERVAL 15 SECOND
+      AND id > ?
+      ORDER BY id ASC LIMIT 1", 
+      undef, $id)->[0];
+}
+
+
+1;
diff --git a/lib/MasterServer/Database/mysql/dbClientList.pm b/lib/MasterServer/Database/mysql/dbClientList.pm
new file mode 100755
index 0000000..898eb28
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbClientList.pm
@@ -0,0 +1,45 @@
+
+package MasterServer::Database::mysql::dbClientList;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| get_gamenames
+                  get_game_list |;
+
+
+################################################################################
+## get a list of distinct gamenames currently in the database. it does not 
+## matter whether they are recent or old, as long as the game is currently in
+## the database.
+## 
+## returns: hashref of gamenames
+################################################################################
+sub get_gamenames {
+  my $self = shift;
+
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT distinct gamename 
+      FROM serverlist");
+}
+
+################################################################################
+## get the list of games of a certain $gamename, excluding the ones excempted
+## via the blacklist
+## only returns server addresses that are no more than 1 hours old
+################################################################################
+sub get_game_list {
+  my ($self, $gamename) = @_;
+  
+  return $self->{dbh}->selectall_arrayref(
+    "SELECT ip, port 
+     FROM serverlist
+     WHERE updated > NOW() - INTERVAL 10800 SECOND
+     AND gamename = ?
+     AND NOT blacklisted", 
+     undef, lc $gamename);
+}
+
+
+1;
diff --git a/lib/MasterServer/Database/mysql/dbCore.pm b/lib/MasterServer/Database/mysql/dbCore.pm
new file mode 100755
index 0000000..30fe3ef
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbCore.pm
@@ -0,0 +1,44 @@
+
+package MasterServer::Database::mysql::dbCore;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| database_login |;
+
+################################################################################
+## login to the database with credentials provided in the config file.
+## returns dbh object or quits application on error.
+################################################################################
+sub database_login {
+  my $self = shift;
+
+  # create the dbi object
+  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => $self->{db_print}});
+  
+  # verify that the database connected
+  if (defined $dbh) {
+    
+    # log the event
+    $self->log("load","Connected to the MySQL database.");
+    
+    # turn on error printing
+    $dbh->{printerror} = 1;
+    
+    # return the dbi object for further use
+    return $dbh;
+  }
+  else {
+    # fatal error
+    $self->log("fatal", "$DBI::errstr!");
+    
+    # end program
+    $self->halt();
+  }
+  
+  # unreachable
+  return undef;
+}
+
+1;
diff --git a/lib/MasterServer/Database/mysql/dbServerlist.pm b/lib/MasterServer/Database/mysql/dbServerlist.pm
new file mode 100755
index 0000000..5bc1cd3
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbServerlist.pm
@@ -0,0 +1,153 @@
+
+package MasterServer::Database::mysql::dbServerlist;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| add_to_serverlist 
+                  update_serverlist
+                  syncer_add
+                  get_next_server |;
+
+################################################################################
+## beacon was verified or otherwise accepted and will now be added to the 
+## serverlist.
+################################################################################
+sub add_to_serverlist {
+  my ($self, $ip, $port, $gamename) = @_;
+  
+  # update or add server to serverlist
+  my $u = $self->{dbh}->do("UPDATE serverlist 
+                            SET updated  = NOW()
+                            WHERE ip = ? 
+                            AND port = ?",
+                            undef, $ip, $port);
+
+  # notify
+  $self->log("update", "$ip:$port timestamp updated") if ($u > 0);
+  
+  # if found, updated; done
+  return 0 if ($u > 0);
+  
+  # if not found, add it.
+     $u = $self->{dbh}->do("INSERT INTO serverlist (ip, port, gamename, country) 
+                            SELECT ?, ?, ?, ?",
+                            undef, $ip, $port, $gamename, $self->ip2country($ip));
+
+  # notify
+  $self->log("add", "$ip:$port added to serverlist") if ($u > 0);
+  
+  # return added
+  return 1 if ($u > 0);
+
+  # or else report error
+  $self->log("error", "an error occurred adding server $ip:$port ($gamename) to the serverlist");
+  return -1;
+}
+
+################################################################################
+## same as add_to_serverlist above, but does not add the server to serverlist
+## if it does not exist in serverlist. it must be added by another function
+## first.
+################################################################################
+sub update_serverlist {
+  my ($self, $ip, $port, $s) = @_;
+  
+  # update server info
+  my $u = $self->{dbh}->do(
+           'UPDATE serverlist 
+            SET updated  = NOW(),
+              gamename = ?,
+              gamever  = ?,
+              hostname = ?,
+              hostport = ?
+            WHERE ip = ?
+            AND port = ?', undef, 
+          $s->{gamename}, $s->{gamever}, $s->{hostname}, $s->{hostport}, 
+          $ip, $port);
+
+  # notify
+  $self->log("update", "server $ip:$port info updated") if ($u > 0);
+  
+  # return 0 if updated
+  return 0 if ($u > 0);
+   
+  # or else report error
+  $self->log("error", "an error occurred updating server $ip:$port in the serverlist");
+  return -1;
+}
+
+
+################################################################################
+## add new addresses to the pending list, but do not update timestamps. masters
+## that sync with each other would otherwise update the timestamp for a server
+## which is no longer online.
+################################################################################
+sub syncer_add {
+  my ($self, $ip, $port, $gamename, $secure) = @_;
+
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "SELECT * FROM serverlist 
+      WHERE ip = ? 
+      AND port = ?",
+      undef, $ip, $port);
+ 
+  # notify
+  $self->log("read","syncer found entry for $ip:$port") if ($u > 0);
+  
+  # if found, return 0
+  return 0 if ($u > 0);
+
+  # if it is already in the pending list, update it with a new challenge
+  $u = $self->{dbh}->do(
+     "UPDATE pending 
+      SET secure = ?
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $secure, $ip, $port);  
+
+  # notify
+  $self->log("update","$ip:$port was updated by syncer",
+          $self->{log_settings}->{db_updated}) if ($u > 0);
+  
+  # return 1 if found
+  return 1 if ($u > 0);
+  
+  # if not found, add it
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (ip, heartbeat, gamename, secure) 
+      SELECT ?, ?, ?, ?",
+      undef, $ip, $port, $gamename, $secure);
+                            
+   # notify
+   $self->log("add","beacon: $ip:$port was added for $gamename after sync") if ($u > 0);
+   
+   # return 2 if added new
+   return 2 if ($u > 0);
+   
+  # or else report error
+  $self->log("error", "an error occurred adding $ip:$port after sync");
+  return -1;
+}
+
+################################################################################
+## get a server address of the next server in line to be queried for game info. 
+## query must be older than 30 seconds (in case it just got added) and not 
+## older than 3 hours. FIXME: now older servers are ignored!
+################################################################################
+sub get_next_server {
+  my ($self, $id) = @_;
+  
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT id, ip, port FROM serverlist
+      WHERE added < NOW() - INTERVAL 15 SECOND
+      AND updated > NOW() - INTERVAL 10800 SECOND
+      AND id > ?
+      AND NOT blacklisted
+      ORDER BY id ASC LIMIT 1", undef, $id)->[0];
+      
+}
+
+1;
diff --git a/lib/MasterServer/TCP/Handler.pm b/lib/MasterServer/TCP/Handler.pm
index bf6beb9..9d05a32 100755
--- a/lib/MasterServer/TCP/Handler.pm
+++ b/lib/MasterServer/TCP/Handler.pm
@@ -33,7 +33,7 @@ sub read_tcp_handle {
 
   # allow multiple blocks to add to the response string
   my $response = "";
-      
+     
   # replace empty values for the string "undef" and replace line endings from netcatters 
   # parse the received data and extrapolate all the query commands found
   my %r = ();
@@ -243,9 +243,9 @@ sub handle_list {
 sub handle_sync {
   my ($self, $val, $r, $c, $a, $p) = @_;
   
-  # alternate part 3: wait for the requested action: \sync\(all|list of games)
+  # alternate part 3: wait for the requested action: \sync\(all|list of games)\sender\domainname
   $self->log("tcp","Sync request from $a:$p found");
-      
+
   if ($val && exists $r->{sync}) {
 
     # compile list of addresses
@@ -256,7 +256,8 @@ sub handle_sync {
     $c->push_write($data);
     
     # log successful (debug)
-    $self->log("sync","$a:$p successfully synced.");
+    if (exists $r->{sender}) {$self->log("sync","$r->{sender} successfully synced.");}
+                        else {$self->log("sync","$a:$p successfully synced.");}
     
     # clean and close the connection
     $self->clean_tcp_handle($c);
diff --git a/lib/MasterServer/TCP/Syncer.pm b/lib/MasterServer/TCP/Syncer.pm
index dea20a5..69025f0 100755
--- a/lib/MasterServer/TCP/Syncer.pm
+++ b/lib/MasterServer/TCP/Syncer.pm
@@ -6,7 +6,6 @@ use warnings;
 use AnyEvent;
 use AnyEvent::Handle;
 use Exporter 'import';
-use Data::Dumper 'Dumper';
 
 our @EXPORT = qw| syncer_scheduler sync_with_master process_sync_list|;
 
@@ -85,7 +84,7 @@ sub sync_with_master {
         $handle->push_write("\\gamename\\333networks\\location\\0\\validate\\$validate\\final\\");
         
         # part 3: request the list \sync\gamenames consisting of space-seperated game names or "all"
-        my $request  = "\\sync\\".(($self->{sync_games}[0] == 0) ? "all" : $self->{sync_games}[1])."\\final\\";
+        my $request  = "\\sender\\$self->{masterserver_address}\\sync\\".(($self->{sync_games}[0] == 0) ? "all" : $self->{sync_games}[1])."\\final\\";
         
         # push the request to remote host
         $handle->push_write($request);
@@ -118,6 +117,12 @@ sub process_sync_list {
   # counter
   my $c = 0;
   
+  if (exists $r{echo}) {
+    # remote address says...
+    $self->log("error", "$ms->{address} replied: $r{echo}");
+    
+  }
+  
   # iterate through the gamenames and addresses
   while ( my ($gn,$addr) = each %r) {