Statistics and Databases

As I explained in one of my articles, I don't really like storing the players data in a database. Instead, we use flat files, which, IMHO, are easier to work with and faster.
But a disadvantage of flat files vs. a database is that it is hard to obtain statistic information. For example, checking to see the total quantity of an item in the game using flat files requires opening every single player file, which is slow and inconvenient. Then if you want to determine some other statistic information, you will need to reopen all those files again.

Luckily, our server is designed not to need any such statistics. If some statistics are needed, they are saved in a special file, and loaded whenever they are needed, or just kept in the memory.

But often times it is a good idea to have a quick glance at some stuff that is going on, for example seeing how many gold coins are in the game, and the top 100 players in terms of how many gold coins they have. This is useful for many purposes, including balancing the economy, creating new formulas, adjusting some rates, and even checking to see if there are any item duplication bugs.
As previously mentioned, it is hard to get all this data without a database.
Which is why I worked at actually storing all the stuff I am interested in an Sqlite DB.

Sqlite is a public domain database (SQL) library. It's not a database server, such as MySql or MSSql. Because of this, there is less time involved in accessing the database (no indirection layer), and you don't have to worry about having a server running all the time.
Because it is public domain, we can embed some server code in the program that is generating the statistics. With GPL code it is slightly more complicated (in theory we can use GPL code in our server, and not be required to make our source code public, because we do not distribute it; however, GPL3 is going to change that so we'd rather not use any GPL code at all).

Writing the program was relatively easy, Sqlite is very easy to use, at least on Windows. The problem was in having a script that would feed that program with every player file name. As I am developing under Windows, I didn't have native access to the powerful and very useful Unix commands, such as bash and find. The Dos/Windows .bat is not that powerful, and I couldn't find a way to get all the files in a directory, recursively, and send them to a program of my choice.
So what I did was download Cygwin, which is for Windows what Wine is for Linux (well, sort of).
One problem that I had is the fact that it would seem that the bash version that comes with it is using an internal version of "find". So when typing: "find ." from the bash prompt, it complains about some bad parameters... Eventually, I just renamed find.exe as find_1.exe and used "find_1 .", which seemed to work better.
The script looked like this: "for i in $(./find_1 .); do ./stats.exe $i; done" (Lachesis helped me with it).
The player files I used were taken from the previous night's backup.
The script worked, but it was very, very slow. It took about 4 hours to process 101K files. I am not sure if this was because of Cygwin, or because of Sqlite, my program was pretty fast.
I talked with Learner about it, and we are going to modify the program to get it's own files, so it will not have to rely on an external script. Making it get it's own files will also make it faster, as it doesn't have to open and close the database for every player file.

Despite for all those small problems, once the database was created, the end result was very nice. I was able, in seconds, to get the total quantity of the gold coins in the game (about 200M). Not bad, that would be about 2K gold coins per player, on average. Of course, as with the real life, the amount of gold coins in the game is not evenly distributed, some players have over 2 million coins, while others have considerably less. In fact, here is the top quantities of gold coins for the players (I am going to publish only the quantities, not the player names, for privacy reasons).

3117212
2788486
2581151
2486154
2076652
1679400
1616314
1603558
1528248
1428579
1386501
1373042
1301485
1284118
1256488
1203808
1164721
1134737
1101526
1099434
1061115
1058531
1027778
996269
960114
948841
948784
882865
858820
842955
809451
786818
765751
746009
740049
723096
711036
674987
674701
671717
657964
642701
632000
597026

The irony is, more than a half of the players in this top never bought anything from us, so the game can very well be played without paying anything. As for those who do buy items from us, I suspect that the reason they are in the top is because they like the game a lot and play a lot. After all, you wouldn't buy items if you didn't play a lot, right?

Another interesting thing is that a lot of rare items are pretty abundant in the game. For example, there are 15K enriched fire essences, and they sell for around 3.5Kgc each. If they belonged to one single person, and if that person had enough buyers, he could sell them for about 20K USD (not bad). Of course, they do not belong to one single player, and very few people have more than 20 in their inventory. The reason there are so many of them in the game is that for a year or so, I didn't check on how many were entering the game vs. how many were exiting the game, and we had quite some disparity (about 2 entering for each that was exiting). Now that I made them more rare, more are exiting than entering, so in a few months their numbers should decrease by a few K.
It is also worth mentioning the fact that some of those players are no longer playing, so the real quantity might be something close to 10K, but that is impossible to know for sure.

To end this long post, I am glad I spent some time to write this program and use Sqlite. It proved to be quite useful, and will help with the ongoing task of constantly improving Eternal Lands.

Finally all the cross attributes are used now

EL is online for a little more than 4 years, and the attribute system is almost as old as the game. Until recently, two cross attributes: charm and perception were totally unused, and, unsurprisingly, most of the people did not invest a significant amount of pick points in them.

The perception was finally added in the last update, and it dictates how far you can see at night. In the future, when we will have the range attack skill, it will help with it as well.

The charm, on the other hand, was more difficult to integrate with the game. Initially it was meant for quests and better prices from NPCs, but eventually I abandoned that goal and just let it unused. I even considered changing it's name altogether, and possibly use it for the ranged skill.
Today I made up my mind, and charm will still be charm, and it will have to do with the summoning skill. It will modify this skill in two ways, but this is a surprise for the coming update. Hopefully many people will like it, although I don't expect everyone to suddenly start investing pick points in it.

Different visibility system

Something I wanted for a very long time is finally in the game now (on the test server, and soon to be on the main server).
I am talking about a system where you can hide during the night time, and use camouflage and night vision items.

How it works: When you are inside a dark cave or dungeon, or when you are outside at night, far from a light source, you will be less visible than those near light sources. Those with a higher perception cross attribute and those with night vision equipment (not available now) will be able to see further away at night. Wearing armors and weapons will make you more visible to others.

This system allows for more variation during the combat, especially effective in a team work. For example, a player with a high perception attribute and a high magic level can hide in dark places and cast Harm or Mana drain on an enemy. Or an ally can cast heal on you, without the enemy noticing that.
In addition to the combat itself, the engineering skill (when implemented) will be partially used to produce equipment that makes use of the different visibility system. For example, we will have at least two camouflage capes (active and passive camouflage), night vision helms, a lightmeter, to precisely determine the light level of where you are, and some device to briefly show you the players that you see but they don't see you.


Another use for this system is in my "to do" list, and will probably be implemented this spring or summer. The idea is that all the bags and land mines/traps will have their own visibility. A bag will be more visible than a mine, of course. However, at night, it will be harder to see the bags that are not too close to you.
This means that the higher your perception skill is, the further you see the players and bags, and the better your chance will be to detect the landmines so that you can avoid them.
For the time being, all the clients get all the bags on a map, even if those bags are at the other end of the map. I don't really like this, it helps people cheat a little (if they use a hacked client) and it wastes some bandwidth (not a lot though).
So my next project is to change the way the bags are sent to the players, which will involve some quite CPU expensive code. Fortunately, this code can run in a thread in parallel with the code that checks if a player sees another player, so on a multi CPU system there will be little to no speed penalty. In the future, I am also planning to use some additional multithreading where it's possible, but more details about it later.

Lot of improvements and new stuff soon

Today, for the first time in months, I downloaded the client CVS and compiled it, in order to test the new object format, which has a few improvements over the old one, such as the materials ordered to minimize texture changes, and redundant faces removed.
Daniel finished the new format, fixed a few problems with the converter, and added the support for it in the map editor, which means that now we can finally switch to a more efficient format.

In conjunction with the compressed maps, objects and textures, the client loads faster and it is slightly faster.
We had a test today, with a pre pre release candidate, and it seems that there weren't any problems with the latest changes.

The only thing I don't like about compressing everything is that the download file size is much bigger than before. The reason for this is that with the objects uncompressed, the installer compresses all together. If they are compressed individually, the file sizes increases. One way to mitigate this problem is having the objects uncompressed, then after the game is installed the installer can run a script (a bat, maybe) to compress them and delete the uncompressed versions.

On the server side, I am almost finished with the light maps and the different visibility based on the light (nearby lights, time of day, etc.). It should be ready for testing by the end of this week.