• 2008-12-13
•   ASP.NET, WinForms

Travian the well known browser game has a downloadable file for developers in each of its servers that provides details of all the players of the server. The file contains a list of SQL INSERT statements, one for each player. Although the statement doesnt provide military or attack information, that can be used to cheat in the game, it provide enough information to draw your own maps and create analysis tools for the villages, players, and alliances.

The file name is map.sql, for example the full URL of the file for the com4 server will be 'http://s4.travian.com/map.sql'. By the way, the map files of the German servers used to be karte.sql, but they changed it to map.sql too. The following is an example of the contents (I inserted a line break before the VALUES clause for readability):

INSERT INTO `x_world` 
    VALUES (1517,315,399,3,181705,'New Remial',82126,'Tale',0,'',273);
INSERT INTO `x_world` 
    VALUES (67792,107,316,1,175829,'3.Clay',35440,'salvaje_jabali',0,'',244);
INSERT INTO `x_world` 
    VALUES (32790,349,360,1,189271,'Lumeria',9702,'Aquifel',90,'APE',20);
INSERT INTO `x_world` 
    VALUES (72449,-42,310,1,194313,'New village',2634,'paloc',0,'',24);
INSERT INTO `x_world` 
    VALUES (75669,-26,306,1,170802,'Yao Ming',2634,'paloc',0,'',330);

The fields are:

• ID: Number of the field, starts in the top left corner at the coordinate (-400|400) and ends in the bottom right corner at (400|-400).
• X: X-Coordinate of the village.
• Y: Y-Coordinate of the village.
• TID: The tribe number. 1 = Roman, 2 = Teuton, 3 = Gaul, 4 = Nature and 5 = Natars
• VID: Village number. The unique ID for the village.
• Village: The name of the village.
• UID: The player's unique ID, also known as User-ID.
• Player: The player name.
• AID: The alliance's unique ID.
• Alliance: The alliance name.
• Population: The village's number of inhabitants without the troops.

The SQL code needed to create the table to hold this data is:

CREATE TABLE [dbo].[x_world](
	[worldid] [tinyint] NULL,
	[id] [int] NULL,
	[x] [smallint] NULL,
	[y] [smallint] NULL,
	[tid] [tinyint] NULL,
	[vid] [int] NULL,
	[village] [nvarchar](50) NULL,
	[uid] [int] NULL,
	[player] [nvarchar](50) NULL,
	[aid] [int] NULL,
	[alliance] [nvarchar](20) NULL,
	[population] [smallint] NULL
)

The following code downloads the map.sql file, opens it for reading, and runs each line as a separate INSERT statement. Although the code is ASP.NET, it can be changed to a Windows Forms app easily.

using System.Net;  //to download the file
using System.Data.SqlClient;  //to run the INSERT statement
using System.Configuration; //to read the connection string from web.config
using System.IO;  //to open the downloaded file

...

public partial class travian : System.Web.UI.Page
{

...

    private static void GetTravianMap()
    {
        //get the directory of the web app
        string mapFile = HttpContext.Current.Request.PhysicalApplicationPath 
            + "\\map.sql";
        WebClient client = new WebClient();

        //download the file to the specified location, using HTTP
        client.DownloadFile("http://s4.travian.com/map.sql", mapFile);

        SqlConnection aspNetDb = new SqlConnection(
            ConfigurationManager.ConnectionStrings["DBConStr"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = aspNetDb;
        aspNetDb.Open();

        StreamReader sr = new StreamReader(mapFile);






        string line;

        //loop thru the lines in the file
        while ((line = sr.ReadLine()) != null)
        {
            //Remove the back tick from the name of the table, because this only
            //works with MySQL, in SQL Server this throws an error
            line = line.Replace("`", "");

            cmd.CommandText = line;
            cmd.ExecuteNonQuery();
        }

        aspNetDb.Close();
    }
}

The GetTravianMap() function can be run in a regular interval to keep the data in the table current with the game. The DownloadFile() function replaces the local copy of the file without raising an error, so there is no need to check if a file exists and delete it. But the data downloaded previously should be deleted, or normalized because the a server can have more than 20,000 members and this could increase the database size drastically.

To get more details about the servers, their update time, and examples in PHP follow this link to the Travian Help website: http://help.travian.com/index.php?type=faq&mod=230