OpenTTD + MySQL

[Artea]

MySQL DB in OpenTTD

MySQL DB -> Statistics (Server Info or Game Info)
Projects: IRC Bot, Website, Status Inside the game

[sidew]

Why use MySQL on a game? If you need statistics, consider the idea of dumping XML files. With XML files you can process them with PHP, ASP, ASP.NET , JSP or Java Servlets.

[gigajum]

Mysql is easier to handle then xml files. But that's all. There is no server admin login system, nothing that really requires a mysql database.

Sorry but simple file output in xml format should do all you need. Mysql support for a game is a bit oversized imo.

[maquinista]

I think that is better develop new docks, trams, new industries, or other things of the game. Statistics are good, but this is a transport game.

[Sebastiaan]

Statistics are good, but this is a transport game.

It's a multiplayer transport game, and thus, people want to be able to see their performance compared to others. I know this is possible with the in-game score-chart but that doesn't give you historical evolution data.

An example of what is possible with statistics can be seen in the thread in my signature.

On topic: (don't know why a new thread was necessary as this all was about my statistics patch, but anyway... )

I fully agree that MySQL support would be a giant overshoot. And only for a few statistics this doesn't pay out. I'm glad XML was suggested and I'll change my format to that. (Up till now it was just logging in a custom format which is of course less portable.)

[Brianetta]

I am busy coding autopilot 2. The design is complete, I'm just churning out the code for it now.

It will support MySQL. Unfortunately, it won't support OpenTTD on Windows until somebody fixes the dedicated console[1], but it will work on pretty much any other platform that can run Tcl and Expect, which includes Linux, Mac OS X and BSD Unix, among others.

It (autopilot 2) will be able to keep a list of companies and players in a MySQL table. It will also be able to list the newgrf files that are in use in said table. It will have IRC support, including the ability to bridge the in-game chat to the IRC channel, and vice versa. From IRC, people will be able to list players and companies in the game, as well as speak to them. Unfortunately, in-game data like the date isn't available from the console, but if your ultimate goal is a web page of information, there are other ways to retrieve that, which work already.

So, it's rosy on that front. Rather than adding MySQL support to the game itself, it's far easier to have a wrapper program handle the console and talk to whatever it likes. Adding support to autopilot for other databases wouldn't be particularly difficult.

#openttdcoop's Sandbox is running a technology trial already. It claims to be a version 2 autopilot when it's actually a heavily modified version 1 line autopilot (1.3, I believe). Version 2 is being totally re-written. I began with paper and pencil (yes, really) to design the application in its entirety, and have recently started to code from my design. IRC support is almost completely coded, the rest wil come soon. I don't have a release date in mind, but it shouldn't take long.

[1]In Windows, for some reason, the dedicated console is launched in a new window, rather than using stdio on the terminal that launched it. Because it is therefore not a Windows 32 bit console application, there is no version of Expect for Windows that can handle it. This is exclusively a Windows issue - the source code begins to break the console like this:

Code: Select all

#if defined(WIN32)
            CreateConsole();
#endif

[Sebastiaan]

So, it's rosy on that front. Rather than adding MySQL support to the game itself, it's far easier to have a wrapper program handle the console and talk to whatever it likes. Adding support to autopilot for other databases wouldn't be particularly difficult.

You put the finger right on the point. What should be in the game, and what should be external wrappers? Clearly DB access should be outside. Some features of autopilot should be on the inside I think (like pause on no/few players, welcoming messages) and others on the outside (IRC ). Other than this minor remark I like the autopilot very much Brianetta

As for the statistics i've chosen an inside approach. It seems the logical way to me as only the game itself knows when data has changed/time progressed and a 'snapshot' should be made. I also try to keep the in-game processing time as small as possible. Therefore the graphs generated are done by an external php script upon request. (Yet again another inside/outside discussion coming up? )

[Brianetta]

Well, the autopilot was originally written to provide features missing from within the game, particularly the two you mention. Autopilot has turned out to be an apt name for it, as it's becomng a stand-in console admin who doesn't need to sleep (and can even page a human if needed).

Wrappers are all well and good, but what the game really needs is some sort of real interface. Already you can query the game via UDP and come up with all sorts of interesting goodies. This is how the master server knows how full a server is, whether it's passworded, what the current game date is and so on. I think it'd ne nice to extend that, and the openttd.inc.phpwhich allows a PHP script to interface with it.

[XeryusTC]

Mysql is easier to handle then xml files.

*cough* Depends on what it is going to be used for. *cough*

I personally think that XML or a TDF would be best, it is easiest to parse with a script or program.

Brianetta: the autopilot is great, but maybe it needs a "!beer" trigger for IRC .

Sebastiaan: a script still needs to check if a file has been changed by OTTD from time to time.

[Artea]

Sebastiaan, you doing a great job with statistics, believe me... you are my inspiration to make this thread and a *big* project...

Brianetta: work to AutoPilot - i want be a tester hihihi

[Brianetta]

XeryusTC: New autopilot has configurable IRC !bang-commands. That's already coded and working:

Code: Select all

[00:39] <Brianetta> !url
[00:39] <test-pilot> www.example.com
[00:39] <Brianetta> !email
[00:39] <test-pilot> My admin can be reached at admin@example.com
[00:39] <Brianetta> !fish
[00:39] <test-pilot> Today's fish is Trout a la crème. Enjoy your meal.

In the case of !email and !url, it's even using simple variable substitution. This currently lets you use URL, EMAIL, PASSWORD, VERSION, PLAYERS and COMPANIES in your custom responses. If you don't want a particular command, you can either delete it from your openttd.cfg (which is where autopilot's settings go), or make it do something else. You can even overload built in commands with this - which is nice if you want to disable the !players, !companies or !newgrf commands, which have a few lines of output - and can also be done by PM.

Artea: You can be involved in testing if you like - but once it's at least in a state to run. Right now, for example, it can count players, but doesn't unless you ask it, because there's code that hasn't been written yet.

Specify -> Design -> Code -> Test -> Release

At test, I can go back to Design, or continue to Release. I am currently on Code, for the first iteration of that possible loop. The specification wsa completed a couple of weeks ago, and the design is complete with the exception of the MySQL module, which is going to be started later.

[bredroll]

Hi Guys and Gals,

You really should look at sqlite. Mysql is overkill.

sqlite gives you SQL98 access to a single file, It is very very fast and well documented

Ian

[Brianetta]

Hi Guys and Gals,

You really should look at sqlite. Mysql is overkill.

sqlite gives you SQL98 access to a single file, It is very very fast and well documented

Ian

MySQL is already available on practically every production non-Windows server on the net. MySQL allows other hosts (or even other users on the same host) to connect and query the database. Most PHP programmers are more familiar with php-mysql and php-mysqli. SQLite isn't actually all that much faster, since databsae calls generally aren't the time waster. When it boils down to it, there's little advantage in installing SQLite just for this.

Oh, and it isn't SQL98. It's nearly SQL92. Also, it has a rather limited number of data types, meaning you have to write more of your own code for the same effect (it doesn't have support for enum, date or time fields, for example).

FInally, some people (myself included) don't particularly like the style of the author's approach.