Database layer

[masterfreek64]

Hello people, I would like to propose a new reflection based database layer:
(reflection is also known as RTTI):
Every database stored object inherits from DatabaseObject and Iserializable .
DatabaseObject provides methods for Reflection ( if the reflection system we choose to use needs them) , a bool bSavetoDb member to allow temporary objects , and a longlong primary ID field.
Its constructor references every database object in a global collection ( such as a list)
The ISerializable interface provides serialization.
When the server saves the world , it iterates the collection , serializes and stores every object and the type information into a database.
When the server restores the world , it selects all objects from the database , with their types , and recreates(deserializes) them (which automatically adds them to the Collection) based on this RTTI.
The Database is implemented as an abstract class, DatabaseProvider , which has to provide methods for inserting / updating / selecting database objects.
Internally it could for example be implemented as a database based on postgreSQL with 2 tables , one storing Unique ID , typeid and data of an object , one storing typeids and type strings .
For providing upgrade compatibility , serialization in the database is binary, whereas there is also the option to serialize to XML data.
The only problem is that a) everything would need to be OO
and b) we would need to build int he reflection API , which is quite easy...
c) Editing requires an editor program, which loads the server core , deserializes the binary data, and presents them in XML (on Win32 or .NET(Mono) , this could be done as a PropertyGrid)
But we got the advantages
a) Totally customisable , custom types and databases possible without core changes
b) New types can be added without modifying the database layer or database schema
c) Database is much faster - PostgreSQL can easily handle up to 32TB of data
d) much less code - the database layer stays sleek
e) much less data - binary serialization data instead of slow mixed data in DB schemas

[LethalEncounter]

An intriguing idea but not something I think the emu should use at this time. The emu needs to be something that someone can download and use with a very basic knowledge of how things work. I have taken great pains to try to make it as simple as possible because I know that not everyone that wants to use it are programmers. What your proposing would effectively destroy the average user's ability to modify the system and create their own environment.
1. There is nothing wrong with the way we are storing things into the database. It is much more efficient to call a simple update then to serialize an entire object and then call an update.
2. PostgreSQL is much slower than MySQL. Just doing a google search for "PostgreSQL vs mysql speed" returns quite a few sites saying that mysql is faster but that "PostgreSQL might be able to be tuned to increase it's speed" (something that the average emu server admin will not want to do).
3. You are saying that we would need a specialized tool to create and access the data from our database. No matter how well that tool is designed, it will never be as good as HeidiSQL or any other mysql front end that allows you to currently edit whatever you want.
4. EQEmu used to (maybe still does) store their character information in a way similar to what you are suggesting. The result was that the information stored for one client version was incompatible with a new version. Editing the information was also not for the faint of heart. If anyone else is around from those days they will certainly admit that they don't want to go back to those problems.
5. The emu supports multiple versions seamlessly. A person running live can currently interact with someone running a version that is two months old as if they were running the same version. Storing data as objects into the db would prevent a player from using multiple clients, effectively doing what #4 states.

[masterfreek64]

1. There is nothing wrong with the way we are storing things into the database. It is much more efficient to call a simple update then to serialize an entire object and then call an update.
You can not create custom objects - and effectively the emu is already serializing the objects into a database ( into multiple fields instead of one) .
2. PostgreSQL is much slower than MySQL. Just doing a google search for "PostgreSQL vs mysql speed" returns quite a few sites saying that mysql is faster but that "PostgreSQL might be able to be tuned to increase it's speed" (something that the average emu server admin will not want to do).
This "unbiased" comparison is for PostgreSQL 6.0 vs MySQL 5.0 . It is like comparing Linux Kernel 1 with Windows Server 2003 Enterprise ....
Also PostgreSQL is used for example by Sony on the live servers
3. You are saying that we would need a specialized tool to create and access the data from our database. No matter how well that tool is designed, it will never be as good as HeidiSQL or any other mysql front end that allows you to currently edit whatever you want.
- This tool would be written in .NET , and there such tools are a matter of 2-3 hours to write - they use the PostgreSQL.NET or MySQL.NET binding , connect to the database, and display all properties inside the object as a PropertyGrid ...
looks just like Visual Studio
4. EQEmu used to (maybe still does) store their character information in a way similar to what you are suggesting. The result was that the information stored for one client version was incompatible with a new version. Editing the information was also not for the faint of heart. If anyone else is around from those days they will certainly admit that they don't want to go back to those problems.
All characters and accounts should be in a version independent format anyways - and this format can be serialized
5. The emu supports multiple versions seamlessly. A person running live can currently interact with someone running a version that is two months old as if they were running the same version. Storing data as objects into the db would prevent a player from using multiple clients, effectively doing what #4 states.
Why that? All objects are in an emulator specific format , which should be in a class and the same for all versions
Speed: mHz for mHz, MySQL has PostgreSQL beat for simple searches.
Once you start getting complex, PostgreSQL is competitive. I think this
speed issue is overrated: over time, PostgreSQL has sped up and MySQL
has slowed down which is pretty impressive, considering both have added
features from their early versions.
BLOBs and binary data are quite complex... also you can add cross table constraints !

[CrabClaw]

I have Postgres, Mysql and Oracle 10g that I am learning on my system. I can say if a majority of the dev are used to MySQL then there is no real reason not to stay with it, the differences in performance, given most servers will be lucky to have more then a couple handful of players, is, I feel anyways, a wash.
There are wow emu servers that run off of MySQL supporting several hundred players with the only bottleneck being the quality of the server code and less the database deployment. Setting up InnoDB where needed for the more volatile data will give you plenty of speed where ya need it.
I love Postgres, for being OSS and a cleaner SQL, but it's kinda not a big hitch, for our small scale purposes either is fine. SoE uses Postgres cause it's OSS and they don't have to pay Oracle plats' for a real high speed database setup (Read: 'On the Cheap' - hehe).
Oracle...talk about cryptic...I would not curse learning that on anyone, lest' I wish upon them to run off screaming insane into the night....
I do worry about not keeping things simple, serialized databases, works better for Java because data would truly be backwards compatible. Even lineage 2 runs off a Java server. Now I love Java even more than all *.CPP solutions, but with coding you sometimes have to go with the skill set a majority of the coders have than having to relearn things.
Mangos has a --postgres compile option, but not many people use the setup because MySQL is just as simple to setup and learn.
In a perfect world, we'd all be doing a Java server in Netbeans 6 (uber, nom, nom) with a Ruby 1.9/2.0 or JavaScript (which is awesome nowadays) scripting engine, and we wouldn't even need a formal database like MySQL or Postgres (Maybe a Tomcat Server if you really needed it). I could imagine how gorgeous a Ruby on Rails database admin web interface would be...maybe some day for the EQ3 emulator...but, we deal with the skill set we have now though to get things done.
A interesting discussion none the the less though

[LethalEncounter]

In a perfect world, we'd all be doing a Java server in Netbeans 6 (uber, nom, nom) with a Ruby 1.9/2.0 or JavaScript (which is awesome nowadays) scripting engine, and we wouldn't even need a formal database like MySQL or Postgres (Maybe a Tomcat Server if you really needed it). I could imagine how gorgeous a Ruby on Rails database admin web interface would be...maybe some day for the EQ3 emulator...but, we deal with the skill set we have now though to get things done.

Hehe, actually I am much better at programming in Java than I am at C++. However, I started the project off in C++ and that is where it will stay for the foreseeable future. Eventually we might be able to port it over to Java and there are many advantages to doing so, but not today.

[CrabClaw]

Yes, no problem! Good to meet another Java-Head like me, *.cpp is fine!

[masterfreek64]

c#?
Allows you to use .NET for customisation

[CaptainBeast]

I'm curious as to the viablility in using C# and the Framework.
Framework 3.5 has some features that might be useful.
Any thoughts?

[skandragon]

2. PostgreSQL is much slower than MySQL. Just doing a google search for "PostgreSQL vs mysql speed" returns quite a few sites saying that mysql is faster but that "PostgreSQL might be able to be tuned to increase it's speed" (something that the average emu server admin will not want to do).

I know this is a very old post you made... but I have to chime in here. PostgreSQL is not that much slower, and usually it is due to the queries people are running. Put a large (I mean huge) database on the box and PostgreSQL is often times faster at insert and retrieval.
That said, a database-independent layer would help a lot. MySQL is nice for development (I suppose, I don't use it myself) but PostgreSQL is the way to go for permanent, large-scale installs.
And Sony uses it for EQ2. Can't be that bad!

[CrabClaw]

It's a tossup I feel, now that MySQL is going open source makes the decision even more a wash. Although Postgres 8.3.0 is supposed to be pretty good though.
Again we are in the early stages yet. One day I hope for a choice though (eventually minilogin too - I know, don't hit me), even for testing.