25 Jul, 2015, mikesmoniker wrote in the 1st comment:
Votes: 0
One of the projects I've been wanting to start for a long time is a robust system for logging all kinds of events within my MUD to some kind of database where I can easily query/aggregate them.

Examples of events might include: a character gaining a level, a skill improvement check being made, a combat check to determine whether a character evades an attack, a database query, etc. Pretty much anything, all with their own unique attributes or meta data.

As far as a data store for this stuff, some kind of document store like CouchDB/MongoDB or maybe ElasticSearch seems like it would make sense.

Ideally, the MUD would be able to emit hundreds or thousands of these events in very short periods of time such that the logging doesn't add any significant overhead to functions that directly impact players like combat.

My goal is to write a very basic C API for the MUD to craft the events as JSON strings (probably using jansson, which I've already incorporated into the MUD) and dispatch them in some kind of "fire and forget" method.

Edit to add: a few use cases on the querying side: how often is X spell successful, how often is X spell successful for characters < level 40, what is the average age of a character when they gain level 80, what is the % chance to evade a blow by character class, etc.

What I'm looking for are ideas, products, strategies you folks think I might want to consider before I dive in. Any thoughts?
25 Jul, 2015, Rarva.Riendf wrote in the 2nd comment:
Votes: 0
You will not have any overhead, as your database is in another process and those are just logs so you don't need to have your DB write everything at any request. So basically, just code it, it cost nothing now, especially on modern multi core hardware.
25 Jul, 2015, quixadhal wrote in the 3rd comment:
Votes: 0
Be aware that there ARE caveats to using a database. It's not all rainbows and flowers. :)

For example, I modified my old DikuMUD to use PostgreSQL for some things, logging and the ban tables as a proof of concept. One issue I was never able to adequately work around was what happens when your database goes down while your MUD is still up. This happens when, for example, you update your database server to a newer version… where part of the upgrade process shutdown it down, puts the new version into place, and restarts it.

The thing is, the C API for PostgreSQL doesn't provide a very robust way to see if your connection is still valid. The only reliable way seemed to be to attempt to connect before each query, but connecting is usually more expensive (in time) than the actual query itself… so that's not a good answer. If the API had a callback to notify you of a socket disconnect, that would work… but it doesn't. So until you try to send the query, you have no idea if it will work or not. Depending on your socket handling, it might hang forever, crash miserably, or the actual write() will return an error which the API *hopefully* will notice and return a failure code for your query.

What *I* would do, instead of crufting your database code into your MUD is to write all the database access stuff in python (or perl, or ruby, or anything not C/C++) and have that script listen for connections on localhost only. Then have your MUD connect to that script and send your queries over the socket. The results can be picked up as part of your normal socket polling loop (from the db socket).

That keeps the database specific stuff isolated, so if you change databases later you don't have to rewrite your MUD's code, just the script. It also means if the script crashes, or the database goes down and the script can't reconnect to it, you can perhaps detect that and deal with it without having to rely on poor vendor support.
25 Jul, 2015, plamzi wrote in the 4th comment:
Votes: 0
quixadhal said:
Be aware that there ARE caveats to using a database… It's not all rainbows and flowers. :)

The thing is, the C API for PostgreSQL doesn't provide a very robust way to see if your connection is still valid…

What *I* would do, instead of crufting your database code into your MUD is to write all the database access stuff in python (or perl, or ruby, or anything not C/C++) and have that script listen for connections on localhost only…


And if you want *only* rainbows and flowers, code your MUD sever entirely in a modern language. Choose python or node.js to get some extra butterflies on top.
26 Jul, 2015, Tyche wrote in the 5th comment:
Votes: 0
quixadhal said:
The thing is, the C API for PostgreSQL doesn't provide a very robust way to see if your connection is still valid. The only reliable way seemed to be to attempt to connect before each query, but connecting is usually more expensive (in time) than the actual query itself… so that's not a good answer. If the API had a callback to notify you of a socket disconnect, that would work… but it doesn't. So until you try to send the query, you have no idea if it will work or not. Depending on your socket handling, it might hang forever, crash miserably, or the actual write() will return an error which the API *hopefully* will notice and return a failure code for your query.

What *I* would do, instead of crufting your database code into your MUD is to write all the database access stuff in python (or perl, or ruby, or anything not C/C++) and have that script listen for connections on localhost only. Then have your MUD connect to that script and send your queries over the socket. The results can be picked up as part of your normal socket polling loop (from the db socket).


The Ruby, Perl, and Python Postgres interfaces all use libpq too.
26 Jul, 2015, quixadhal wrote in the 6th comment:
Votes: 0
Maybe so Tyche, but if so they jump through many of the extra hoops for you.

For example, one of the simplest things you do in a database query… select * from foo. If foo has an integer column that allows NULL, in python and perl, when you get a row object back it has an entry for each column, and if that column were NULL, it will be Null or undef (respectively). In C, NULL and the integer 0 are the same thing, so you have to specifically call another function to check if any given result column was NULL, or was 0. If you don't want to call that function every single time, you can get the results back as a more complex structure, but then you still have to check that is_null boolean yourself (obviously).

And of course, you have to remember to free the mallocs yourself too. :)

C is a great language for some things… but unfortunately, text processing and database interactions are not those things.
26 Jul, 2015, Tyche wrote in the 7th comment:
Votes: 0
quixadhal said:
Maybe so Tyche, but if so they jump through many of the extra hoops for you.


No, not maybe. There just isn't an alternative to using libpq.

quixadhal said:
The thing is, the C API for PostgreSQL doesn't provide a very robust way to see if your connection is still valid. The only reliable way seemed to be to attempt to connect before each query, but connecting is usually more expensive (in time) than the actual query itself… so that's not a good answer. If the API had a callback to notify you of a socket disconnect, that would work… but it doesn't. So until you try to send the query, you have no idea if it will work or not. Depending on your socket handling, it might hang forever, crash miserably, or the actual write() will return an error which the API *hopefully* will notice and return a failure code for your query.


If any of the above is true, it follows that it must be true of the Perl, Ruby, Python, and every language that supports an interface to Postgres.
If any of the above is true, it follows that it's an indictment on the robustness of the Postgres client interface.
Since Postgres has been in production use for decades and has a good reputation, I seriously doubt it.
MySQL, SQLServer, Oracle and DB2 database servers also operate using a similar architecture as Postgres does.
And as far as I'm aware, every single language that uses said databases are also wholly dependent upon the client C API.
So any defects in the C API would be necessarily be present in all other languages.
Again neither the Python, Perl, or Ruby interface provides any help to the above "problem".

The libpq interface also offers asynchronous i/o in addition to blocking i/o. I know the Ruby and Perl interfaces implement the entire libpq interface, however, writing correct asynchronous i/o code in Ruby and Perl is just as error prone and difficult as it is in C. Asynchronous I/O presents the developer with added possibility of an application locking itself out of database. That's in addition to the always present problem of other poorly written applications (like a CRUD web building interface) locking one out.

To put it succinctly, if your database server is down (or locked by other applications), any application that uses said database in any significant way is pretty much screwed until the problem is relieved, regardless of the language you use. Of course the REAL problem is how the designer of the application deals with it, NOT the language they use.

quixadhal said:
What *I* would do, instead of crufting your database code into your MUD is to write all the database access stuff in python (or perl, or ruby, or anything not C/C++) and have that script listen for connections on localhost only. Then have your MUD connect to that script and send your queries over the socket. The results can be picked up as part of your normal socket polling loop (from the db socket).


Then you offer a solution to this non-existent problem, by proposing a man in the middle architecture virtually identical and fraught with the same exact so-called problems. You've now got three servers that all need to be running and come up in a certain order. And you also have to develop and debug yet another network connection and communication protocol between the mud and this server script.

Sure, C (and other languages) don't implement a NULL data type. To the extent that it's even a issue, one might wish to review the entire rational behind even allowing NULL columns (unset values) in the first place in a given schema design.

So somewhere along the line in your language advocacy/dis-advocacy zeal you've crossed the Rubicon into advocating against using these types of databases and/or just plain nonsense.
27 Jul, 2015, quixadhal wrote in the 8th comment:
Votes: 0
Tyche said:
To put it succinctly, if your database server is down (or locked by other applications), any application that uses said database in any significant way is pretty much screwed until the problem is relieved, regardless of the language you use. Of course the REAL problem is how the designer of the application deals with it, NOT the language they use.

It being down wasn't the issue I was mentioning. It having been down is the issue. Namely, once your database server goes down, I have not found a reliable way to detect that it has been down, other than attempting to use it and having it fail. If you've written libpq code in C, you know that it's not very pretty, and if you want to check that for every single query you do (which you would), you're likely going to have to write a wrapper function anyways. The alternative is to always open a connection, use it, and then close it again… which is very expensive with PostgreSQL.

Tyche said:
Sure, C (and other languages) don't implement a NULL data type. To the extent that it's even a issue, one might wish to review the entire rational behind even allowing NULL columns (unset values) in the first place in a given schema design.


It's pretty common practice to have NULL be "not set", in applications which use a database. Even without a database, in languages that have a nil or undef, it's not at all unusual to say "Here's an integer saying how much damage this weapon does… NULL means it doesn't do damage". The difference is, if I later have a formula that does weapon.damage + 3, if you set it to 0, now you're doing 3 damage. If it's NULL, it's still NULL. In code, you have to check, but in SQL you can do "select NULL + 3" and get back NULL. However, in languages that have undef/nil/null, that check is one simple predicate if(undefinedp(x)) or similar.

Tyche said:
So somewhere along the line in your language advocacy/dis-advocacy zeal you've crossed the Rubicon into advocating against using these types of databases and/or just plain nonsense.


Funny, the Discworld people had enough trouble with getting the FluffOS driver to work with MySQL that they wrote exactly the kind of wrapper script (in python) I was talking about.

If I write a perl wrapper using DBI, and I change hosts from one that used PostgrSQL to one that only has MySQL, I can change ONE LINE in my wrapper script to change the connect info, and it now works. Same goes for some other languages (python for sure). I'm not even talking about an ORM, just a database agnostic API. That's really hard to do in C, for the simple reason that C doesn't have dynamic classes/structures. Of course it can be done, but do you want to do that kind of heavy lifting just to load your player data from your database?

I guess it's debatable if having the dynamic classes in other languages makes life easier for you with a wrapper script. On the one hand, I can do a "select * from players where id = ?" via a python or perl wrapper and get back a nice object that I can dump as a string to send to my C program with no modifications needed if the player structure changes. But, you still have to pick it apart at the C level. So…. changing your sscanf() statement vs. changing your raw libpq calls? YMMV.
27 Jul, 2015, Tyche wrote in the 9th comment:
Votes: 0
quixadhal said:
Tyche said:
To put it succinctly, if your database server is down (or locked by other applications), any application that uses said database in any significant way is pretty much screwed until the problem is relieved, regardless of the language you use. Of course the REAL problem is how the designer of the application deals with it, NOT the language they use.

It being down wasn't the issue I was mentioning. It having been down is the issue. Namely, once your database server goes down, I have not found a reliable way to detect that it has been down, other than attempting to use it and having it fail. If you've written libpq code in C, you know that it's not very pretty, and if you want to check that for every single query you do (which you would), you're likely going to have to write a wrapper function anyways. The alternative is to always open a connection, use it, and then close it again… which is very expensive with PostgreSQL.


You have to both check and handle errors after each and every SQL call, be that a return code or a thrown exception.
It doesn't matter what language you use. See comments below. (P.S. After I get back from vacation next week, maybe I'll provide an example. )

quixadhal said:
Tyche said:
Sure, C (and other languages) don't implement a NULL data type. To the extent that it's even a issue, one might wish to review the entire rational behind even allowing NULL columns (unset values) in the first place in a given schema design.


It's pretty common practice to have NULL be "not set", in applications which use a database. Even without a database, in languages that have a nil or undef, it's not at all unusual to say "Here's an integer saying how much damage this weapon does… NULL means it doesn't do damage". The difference is, if I later have a formula that does weapon.damage + 3, if you set it to 0, now you're doing 3 damage. If it's NULL, it's still NULL. In code, you have to check, but in SQL you can do "select NULL + 3" and get back NULL. However, in languages that have undef/nil/null, that check is one simple predicate if(undefinedp(x)) or similar.


* NULL is used in 0 to many relationships
* NULL columns are returned for missing columns in outer joins
* NULL is used to indicate missing information in a table row

It's the last one where one should check their rational and you've provided a great example where one should indeed check their rational.
Using NULL, None nil, undef in high level languages can throws exceptions or return different results.
"weapon.damage + 3" throws an exception in Ruby if weapon damage is nil
"weapon.damage + 3" returns 3 in Perl if weapon damage is undef (or an emtpy string if it happens to be a string operation)
I certainly do question the design of the above example.

quixadhal said:
Funny, the Discworld people had enough trouble with getting the FluffOS driver to work with MySQL that they wrote exactly the kind of wrapper script (in python) I was talking about.


MudOS/FluffOS provides a rather crude generic SQL adhoc interface. The original SQL interface was never modified to do any error handling at all as far as I can tell. It wouldn't be the first time LPers balked at modifying the driver instead of writing an LPC routine to send something elsewhere as a work around. I couldn't find this python script in the FluffOS release.

quixadhal said:
If I write a perl wrapper using DBI, and I change hosts from one that used PostgrSQL to one that only has MySQL, I can change ONE LINE in my wrapper script to change the connect info, and it now works. Same goes for some other languages (python for sure). I'm not even talking about an ORM, just a database agnostic API. That's really hard to do in C, for the simple reason that C doesn't have dynamic classes/structures. Of course it can be done, but do you want to do that kind of heavy lifting just to load your player data from your database?


One can use libdbi or ODBC and it also involves changing one line of code. For example..
conn = dbi_conn_new_r("mysql", instance);
==>
conn = dbi_conn_new_r("pgsql", instance);
27 Jul, 2015, quixadhal wrote in the 10th comment:
Votes: 0
*nod*

The problem, from my experience using libpq, isn't not checking errors…. it's the brokenness of the API and having to work around that. Maybe this isn't true now, but my issue was that if a query failed because your database was bounced, the client still thought the connection was valid and didn't properly report the error. In theory, there are several connection class errors that should be raised if the connection is broken, or compromised, but I never saw those. Instead, I'd get more generic errors and attempting to reconnect didn't work (because the client library still thought it was connected).

I suppose I could have tried opening an entirely new connection and then going through the old one and free'ing everything by hand, or just setting the old variable to the new connection's address and accepting the memory leak… but I didn't think of it until just now.
0.0/10