====== 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.

===== config.inc =====
<code bash>
#
# 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 "
</code>

===== cron.hourly =====
<code bash>
#! /bin/sh

echo `date +'%Y-%m-%d %H:%M'`: $0

for w in de7
do
	${HOME}/opt/travian/bin/get.sh ${w}
done
</code>

===== get.sh =====
<code bash>
#! /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
</code>

===== import.sh =====
<code bash>
#! /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"
</code>

===== update.sh =====
<code sql>
#! /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"
</code>

{{tag>travian shell script download map sql}}