Travian Map Download Script

This is my script(s) which I use to downoad the map.sql from Travian and import it to the database.

#
# database
DB_USER="travian" # set this to your database user
DB_PASS="travian" # set this to your database password
DB_INST="travian" # set this to your database instance
#
# directories
MAP_DIR=${HOME}/opt/travian
DUMP_DIR=/srv/glassfish/domains/domain1/docroot/sql
#
# executables
MYSQL="/srv/mysql/bin/mysql --default-character-set=utf8 -u${DB_USER} -p${DB_PASS} ${DB_INST}"
MYSQLDUMP="/srv/mysql/bin/mysqldump -u${DB_USER} -p${DB_PASS} ${DB_INST}"
WGET="/usr/bin/wget -nv --output-document="
#WGET="/usr/bin/curl --silent --show-error --output "

#! /bin/sh
 
echo `date +'%Y-%m-%d %H:%M'`: $0
 
for w in de7
do
	${HOME}/opt/travian/bin/get.sh ${w}
done

#! /bin/sh
 
BIN_DIR=`dirname $0`
. ${BIN_DIR}/config.inc
 
echo
echo "`date +'%Y-%m-%d %H:%M:%S'` $0[$$] starting"
 
world=$1
today=`date +'%Y-%m-%d'`
todayhour=`date +'%Y-%m-%d-%H'`
 
case "$1" in
        de7)
                url='http://welt7.travian.de/map.sql'
                ;;
        org)
                url='http://www.travian.org/map.sql'
                ;;
        *)
                echo "ERROR! World $1 unknown!"
                exit 1
                ;;
esac
 
savemap="${MAP_DIR}/${world}/map-${todayhour}.sql"
lastmap="${MAP_DIR}/${world}/map-last.sql"
todaymap="${MAP_DIR}/${world}/map-${today}.sql"
 
${WGET}${savemap} "${url}"
 
if [ -e ${savemap} ] && diff ${savemap} ${lastmap} > /dev/null
then
  # savemap an lastmap are the same
  echo ${savemap} is the same als last one: REMOVING
  rm ${savemap}
else
  # savemap and lastmap differ
  echo NEW MAP: ${savemap}
  rm -f ${todaymap}
  ln ${savemap} ${todaymap}
  rm ${lastmap}
  ln -s map-${today}.sql ${lastmap}
  #
  # import SQL
  ${BIN_DIR}/import.sh ${world}
  #
  # update SQL
  ${BIN_DIR}/update.sh ${world}
  #
  # export dump
  if [ -d ${DUMP_DIR} ]
  then
    ${MYSQLDUMP} | bzip2 -c > ${DUMP_DIR}/travian-${today}.sql.bz2
  fi
fi

#! /bin/sh
 
BIN_DIR=`dirname $0`
. ${BIN_DIR}/config.inc
 
echo "***"
echo "`date +'%Y-%m-%d %H:%M:%S'` $0[$$] starting"
 
world=$1
today=`date +'%Y-%m-%d'`
todaymap="${MAP_DIR}/${world}/map-${today}.sql"
 
if [ ! -e ${todaymap} ]
then
        echo "${todaymap} doesn't exist: EXIT"
        exit 1
fi
 
${MYSQL} << __SQL__
TRUNCATE x_world;
__SQL__
 
${MYSQL} < ${todaymap}
 
echo "`date +'%Y-%m-%d %H:%M:%S'` $0 finished"

#! /bin/sh
 
BIN_DIR=`dirname $0`
. ${BIN_DIR}/config.inc
 
echo "***"
echo "`date +'%Y-%m-%d %H:%M:%S'` $0[$$] starting"
 
world=$1
today=`date +'%Y-%m-%d'`
 
echo "    `date +'%H:%M:%S'`: ${world}_world"
${MYSQL} << __SQL__
--
-- update world
--
TRUNCATE ${world}_world;
INSERT INTO ${world}_world SELECT * FROM x_world;
__SQL__
 
echo "    `date +'%H:%M:%S'`: ${world}_history"
${MYSQL} << __SQL__
--
-- update history
--
DELETE FROM ${world}_history WHERE snapdate='${today}';
INSERT INTO ${world}_history SELECT *, '${today}' FROM x_world;
__SQL__
 
echo "    `date +'%H:%M:%S'`: ${world}_alliance"
${MYSQL} << __SQL__
--
-- update alliances
--
TRUNCATE ${world}_alliance;
 
INSERT INTO ${world}_alliance(aid, alliancename, alliancepopulation) 
SELECT DISTINCT w1.aid, w1.alliance, SUM(population)
FROM ${world}_world w1
GROUP BY w1.aid;
__SQL__
 
echo "    `date +'%H:%M:%S'`: ${world}_village"
TEMP_TABLE="tmp_${world}_village_${today_}$$"
${MYSQL} << __SQL__
--
-- update villages
--
 
CREATE TABLE ${TEMP_TABLE} (
  fid INT(11) NOT NULL,
  x SMALLINT(6) NOT NULL,
  y SMALLINT(6) NOT NULL,
  tid INT(11) NOT NULL,
  vid INT(11) NOT NULL,
  villagename VARCHAR(255) NOT NULL,
  uid INT(11) NOT NULL,
  accountname VARCHAR(255) NOT NULL,
  aid INT(11) NOT NULL DEFAULT '0',
  alliancename VARCHAR(255) NOT NULL DEFAULT '',
  population SMALLINT(6) NOT NULL,
  inactive SMALLINT(6) NOT NULL DEFAULT 0,
  history_1 INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (vid),
  KEY x (x),
  KEY y (y),
  KEY fid (fid),
  KEY villagename (villagename),
  KEY uid (uid),
  KEY aid (aid),
  KEY population (population),
  KEY inactive (inactive),
  KEY history_1 (history_1)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
 
INSERT INTO ${TEMP_TABLE}
SELECT
        v.*
        , h.inactive + 1
        , h.population
FROM ${world}_world v
LEFT JOIN ${world}_village h ON h.vid = v.vid
GROUP BY v.vid;
 
UPDATE ${TEMP_TABLE}
SET inactive = 0
WHERE population > history_1;
 
DROP TABLE ${world}_village;
 
RENAME TABLE ${TEMP_TABLE}  TO ${world}_village;
__SQL__
 
echo "    `date +'%H:%M:%S'`: ${world}_account"
${MYSQL} << __SQL__
--
-- update account
--
TRUNCATE ${world}_account;
 
INSERT INTO ${world}_account
SELECT
        w.uid
        , w.tid
        , w.aid
        , w.alliance AS alliancename
        , w.player AS accountname
        , COUNT(DISTINCT w.vid) AS villages
        , SUM(DISTINCT w.population) AS accountpopulation
        , MIN(v.inactive) AS accountinactive
        , SUM(v.history_1) AS history_1
FROM
        ${world}_world w
        , ${world}_village v
WHERE
        v.vid=w.vid
GROUP
        BY w.uid;
 
--
-- end of sql
--
__SQL__
 
echo "`date +'%Y-%m-%d %H:%M:%S'` $0 finished"