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_DIR25;/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" ${MYSQL25; << __SQL__ -- -- update world -- TRUNCATE ${world25;_world; INSERT INTO ${world25;_world SELECT * FROM x_world; __SQL__ echo " `date +'%H:%M:%S'`: ${world}_history" ${MYSQL25; << __SQL__ -- -- update history -- DELETE FROM ${world25;_history WHERE snapdate='${today}'; INSERT INTO ${world25;_history SELECT *, '${today}' FROM x_world; __SQL__ echo " `date +'%H:%M:%S'`: ${world}_alliance" ${MYSQL25; << __SQL__ -- -- update alliances -- TRUNCATE ${world25;_alliance; INSERT INTO ${world25;_alliance(aid, alliancename, alliancepopulation) SELECT DISTINCT w1.aid, w1.alliance, SUM(population) FROM ${world25;_world w1 GROUP BY w1.aid; __SQL__ echo " `date +'%H:%M:%S'`: ${world}_village" TEMP_TABLE="tmp_${world}_village_${today_}$$" ${MYSQL25; << __SQL__ -- -- update villages -- CREATE TABLE ${TEMP_TABLE25; ( 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_TABLE25; SELECT v.* , h.inactive + 1 , h.population FROM ${world25;_world v LEFT JOIN ${world25;_village h ON h.vid = v.vid GROUP BY v.vid; UPDATE ${TEMP_TABLE25; SET inactive = 0 WHERE population > history_1; DROP TABLE ${world25;_village; RENAME TABLE ${TEMP_TABLE25; TO ${world25;_village; __SQL__ echo " `date +'%H:%M:%S'`: ${world}_account" ${MYSQL25; << __SQL__ -- -- update account -- TRUNCATE ${world25;_account; INSERT INTO ${world25;_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 ${world25;_world w , ${world25;_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"
