Building a bot, part... ah, screw it

I've been pounding on the "database-centric" portions of my new bot. It wasn't quite as simple as I thought it was going to be, but it is coming together pretty well. I found a MySQL API bug along the way, so I'm pretty pleased with myself.

It's a good thing I upgraded to MySQL 5.0, as there's a feature I absolutely had to have to pull this off in a reasonable manner: multiple statement execution. The idea is that I want to execute a bunch of statements with a single call to mysql_real_query() so that I can pull a single line - containing multiple queries - from the database and just execute the whole business at once. It's a lot less back-and-forth, and allows me to say, "OK, here's a bunch of statements. Execute these and just give me the result of the very last call".

The API bug I mentioned earlier is that if you have multiple statements and any but the last query returns a result, the final call to mysql_more_results() will crash. The manual recommends the use of mysql_next_result() instead, which is fine unless you don't want to iterate past the last result set, which I don't. I'm hiding all these hideous details in a separate class, and I want the results from the last query to be available once the query is done executing, and mysql_more_results() lets me do this, but mysql_next_result() doesn't. Anyhow, I came up with a reasonably elegant workaround, so all is well with that.

I've discovered the joys of SQL variables. I wanted the database to do all the work, allowing me to add, remove and fix features without having to recompile & restart the program. However, it got a little tricky at some points that require me to insert or update before selecting any rows, and you you simply can't do that with joins and subqueries. So you save in variables whatever you would have in code, and Bob's your uncle.

Regex is always your friend. Without regex matching, I'd have to execute all queries for any given event and then undo anything that shouldn't have been done in the first place. Instead, I can "SELECT query FROM events WHERE 'channel message' REGEXP `msg_match`".

Most of the previous bot's functions have been replicated in SQL, so I've chucked him and the new code will be running full time now. He's not quite ready to replace the old eggdrop, but I'm reasonably happy with the progress so far. I've still got 17 or 18 IRC "events" to cover in code before I can start writing queries for them, but the really hard ones are done, and I've learned most of the gotchas now. So it should go quickly (in theory) if I can stop writing queries for the implemented events.