Hi, are there any plans to support other DBMS than mysql?
… like postgre for example 
No.
Other than MySQL, MariaDB also works with TC but that’s kinda cheating /emoticons/default_tongue.png
I personally wouldn’t mind if someone added support for Postgres to TC.
Point me in the right direction, which files are mainly affected, and I can try an attempt. As I don’t have the amount of time to blindly look every file/place.
/src/shared/Database/* /src/shared/Updater/* and more.
[COLOR= rgb(82, 82, 82)]As I don’t have the amount of time to blindly look every file/place.
It will take a huge amount of time to convert it to postgre , i don’t think the time where you need to look up the files is the main issue…
We removed it years ago because lack of interest on it.
I was reading some of the files, yes @Naios, you are right, the main issues are in other places, not just the postgres connection/prepared statements. I think the main problem is mainly the SQL syntax differences and the automatic updates. I will try something, but don’t guarantee it /emoticons/default_smile.png
If you change anything, don’t simply replace MySQL with Postgres, that’s easy.
Make it support both DBs (or more).
I had that in mind. Ok, got it. Lemme see what can be done…
Right now, I am convincing an army of friends to play, and report me bugs (which I will forward)…
OK, I started deeply examining the code a bit of time, just thinking, and I start seeing problems that needs to be solved/discussed first, let me put them here so they can be properly discussed:
[ol]
[li]Better to start the work on 3.3.5a branch (as it is much easier to test, and 6.x has its own problems like opcodes missing so the tests can be done better)[/li][li]I see that MySQL is kind of implemented “directly” it has virtuals, but not a really abstract base class[/li][li]The SQL format is slightly different:[/li]Postgres changes some of the field types (but mainly there are equivalences, so it can be done)
[li]Postgres changes some of the syntax, like fieldName being ‘fieldName’ instead. (Can be changed via regexp)[/li][li]Postgres has some (better) prepared statements syntax, instead of ?, it has $1, $2, $X in the parameter namingCould be implemented in PostgreSQLConnection::PrepareStatement with some filtering and replacing (?)[/li][/ol]
[li]About general syntax we have 2 options:[ol][/li]UPDATE xxx SET yyy=2 WHERE zzz=false is exactly equivalent to UPDATE xxx SET yyy=2 WHERE zzz=false and UPDATE XxX SET yYY=2 WHERE ZZZ=false unless there are conflicts in the same table fields between uppercase and lowercase in both MySQL and PostgreSQL. If manually created files can’t/won’t be changed, we can fall back to regexp substitution of them. EDIT: I found the define of #define TABLE_SIM “`” which can be used (maybe it is a leftover of the old postgres support?)
[li]TDB files, those are huge, and I think they are automatically generated by someone (or some bot), the solution to make them compatible (mostly):[/li]Have a TDB create with only table definitions (can it be made only for postgres, do one time, reused many times)
[li]Make the dump with: mysqldump -ct to output only the data, in a more compatible way (suggested here: http://www.postgresql.org/message-id/00110114234201.16885@curly )[/li][/ol]
[li]Connection classes, like[ol]class CharacterDatabaseConnection : public MySQLConnection, I see they are implemented directly using MySQL, which is hard to change later… Suggestions:[/li]Via ifdef USE_MYSQL (which can be added to CMakeLists as an option) we can change the inheritance type (but I don’t like doing things like that)
[li]Change this classes to contain a fabric template, and assign the connection via ifdefs (I think this will be better if they have a solid abstract class)[/li][li]Create abstract CharactersDatabaseConnection, WorldDatabaseConnection (and all), so they are like they are now (removing specific calls to mysql, whenever possible) and make typedef DatabaseWorkerPool CharacterDatabaseWorkerPool; be handled via ifdef also with, typedef DatabaseWorkerPool CharacterDatabaseWorkerPool; as an example[/li][/ol]
Maybe I forgot things, but it is getting late, this is just a brief to be discussed a little bit, as this is the very base of the changes, and everything can be rejected if not done according to what you think /emoticons/default_smile.png
EDIT2: Getting late again! (5:57am). How about a rewrite of the database backend, to contain one part mostly abstract things to act as an interface, and inheritance to be specific? MySQL for ex. I think this way would be by far better, and it will benefit to include other MySQL syntax compliant database backends (like SQLite?).
But to sum up, I am waiting for a dev feedback, as I don’t want all my effort to be just dropped if you don’t like it if I try to do that (Though I insist in the rewrite, if you think it is OK as long as it works the same way, forgetting postgres for the moment, but having it in mind when designing the new abstracts). How about it?
There are some SQL libs for C++ that support different backends. https://github.com/rbock/sqlpp11 is one of them. Perhaps something similar can be used.
I have no idea how the final solution should look like. Try to get a proof of concept working, i.e, convert a very small subset of queries first before doing it all so the solution can be evaluated before too much spent is spent on it.
If you are on it you could enable support for non-blocking async queries which are supported under some circumstances (mariadb client lib or postgres in general).
This would increase the speed of the database layer massively under heavy load.
TC needs to make use of non-blocking tasks and lockfree structures in general…
Another question is why it would be useful to support other databases then mysql/mariadb. Probably it will introduce some extra effort in the future to keep things compatible.
Supporting 2 different databases is most likely a way to double the effort and the issues. In addition to the “would it be useful ?” question there is also the “will it be harmful ?” (imo yes, it will)
Its just true, i mean if tc would be a small web application or something it would be useful to support various storage systems to adapt to the existing solutions but tc is mainly installed as heavy main application where it benefits from using just one storage system because of specific optimizations.
If you install tc you will install mysql for sure.
I like the plan to refactor the database layer to increase its performance and code quality (maybe through: non-blocking queries, lockfree queues, metaprogramming and redis), but i personally don’t like the idea of introducing another storage system beside mysql/mariadb which would introduce extra complexity and support effort.
I think you are right, as of the huge list I posted before, to support postgres (while it will be a great optimization by itself as it is way way superior) will be very complicated.
However the database re-design could as @Naios says, could be useful by itself. Furthermore, at a cost of virtually 0, when doing the redesign, I can introduce a thing I am always doing when working with database, optional memcached support (memcache is a daemon to store data in RAM).
The problem is: while I know C++, I never used all the c++11 standard I’m seeing in trinity, and I am a bit confused as of that (like the std::future and so), but I can work perfectly without it, as suggested, just with a few database queries (for example, to be able to get to character screen with the new backend), but in PHP I already did something like that. Basically this is the procedure (using read only cache so in case of crash, nothing is lost)
If it is an SELECT:
[ol][li]Try to gather from memcache (RAM) if present, return it[/li][li]Execute the STMT, store in RAM and return it[/li][/ol]
If it is an UPDATE and/or DELETE
[ol][li]Execute operation[/li][li]Invalidate memcache item[/li][/ol]
By making it optional, fetching for ram can always fail, for example if disabled. (I only have experience with this in PHP, but I guess it would be not so much complicated).
Also, I’m seing lots of “duplicate” queries which can be avoided and we will gain lots of performance, for example, in character delete, there is the CHAR_DEL_CHARACTER and 37 query call more to clean the no longer existing character stuff. This is a kill for database, why not using InnoDB and let it handle the FOREIGN KEYS with ON DELETE CASCADE ON UPDATE CASCADE? It will have the cost of being a little slower at INSERT time, but the gain is:
[ol][li]Data integrity (as of PKEY checks)[/li][li]1 query to delete, instead of 38. The database will be infinitelly faster at deleting FOREIGN KEYs constraint that calling every query by hand[/li][/ol]
Last of all, a little question, I will do as I know to do (like saying, “plain” C++), with no boost and no std::future stuff, as I never used that, but if it works and seems reasonable, with a little guidance I can add the rest to the proposal, and when done, and told so, finish the port. Seems that ok?
EDIT: Memcache can be configured to use only XMB in RAM so it evicts any exceeding older data (which means the next time it will fail), I think it is safe to include this, and the gain is much noticeable, in case of repetitive data (like quest details, quest list, and the like repetitive data).
EDIT2: Related to ON DELETE CASCADE stuff, that is done “automatically” in a transaction, meaning that if the cascading fails, the operation fails (at least on postgres, but should be same in MySQL)
[COLOR= rgb(82, 82, 82)](while it will be a great optimization by itself as it is way way superior)
Citation needed.
http://blog.stormbyte.org/2011/04/dropping-mysql-and-adopting-postgresql.html and there I only wrote a few, can be added:
[ol][li]Table inheritance support[/li][li]named prepared statements (not only bound by an ID), also, automatic type check, you just bind the variables to it, no need to specify its type, as postgreSQL already knows it[/li][li]Prepared statement parameters are ordered, can be reused, reordered, etc in the call. Like UPDATE foo SET x=$2, y=$3, z=$2 WHERE id=$1 (to be called with param order guid, dataX, dataY)[/li][li]MUCH and noticeably FASTER in complex operations (that I noted over the time, faster while doing the things properly, I mean, MyIssam is faster than postgres some times, but it does not guarantee transaction table lock, data integrity and so, and InnoDB vs postgres is postgres faster on same machine, you can just try by yourself)[/li][li]Schemas support (This is to create schemas, or layers into the same database, to separate logically the structure instead of having 1000 tables altogether, prevents some name collissions, as an example)[/li][/ol]
There’s even more, but MySQL is configured out of the box into tricking users to note it is fast, of course it is, as MyIssam does not guarantee you anything and it is up to you, while this should be the work of database (and it is if you use InnoDB)
The actual problem is that future and the other boost stuff is an important part of the implementation (we could use boost::future over std::future since it provides continuation with .then which would clean a lot of future checks in Session::Update).
Improving the database structure and improving the layer are two seperate optimizations which could be done independent from each other.
I do agree with the statement that DBs should enforce consistency, and in fact I’ve been a promoter of the change to InnoDB (as in a previous post:About DB Structure ).
Btw i guess @Nay expected a more technical citation (and maybe not from you).