Message 1 of 2 for MySQL/C API and MUDS:
========================================================
>cheers for your response! Although code would be nice, it's not
>necessary... How about, if you've got the time, just some tips and
>suggestions about integrating MySQL going by what you learned, where you
>learned from and how it all went? I'm sure that would be just as
>invaluable as any code snippet.
Hey, no problem. After thinking about it a bit, I've decided that my code is
hardly surprising, hardly proprietary, etc. No reason NOT to release it.
I have the following tables:
kills
attacker string
a_level number
victim string
v_level number
when datetime
This table is used to track who killed whom when and what levels they were
when the event occurred. This is for doing daily stats of who was the biggest
killer for the day, biggest victim, etc. Only player vs player kills get
logged here.
npc_kills
vnum number
name string
kills number
This table tracks which mobiles are kicking the most player butt. We just
track the total #'s here. Since Merc has vnums, we also track that to
distinguish between multiple mobiles with the same name.
stats
player string
p_kills number
p_deaths number
m_kills number
m_deaths number
This table tracks the life time stats for any player; how many PK's they
got, how many times they were PKed, how many mobiles they've axed, how
many times they were axed by mobiles.
Most of the functions I've written are for adjusting these #'s and they
go in the Merc code in the locations where player or mobile kills occur.
I also wrote some cleanup code to get rid of stats if the player gets
deleted from the game for some reason. The actual C code follows. I'll
make no claims of it being the best or most efficient, although I *was*
careful to do error handling. I hope this will help you with your own
ideas of what you can do with a MySQL interface from a MUD. There's tons
more -- mobile / object / room / zone information; player activity logs;
"who" list, queryable from your home page; etc.
First is my stats_module.h file, which has the declarations used by other .c
files. Some substitutions have to be made below, naturally.
---begin stats_module.h---
#ifndef _STATS_MODULE_H
#define _STATS_MODULE_H
#define STATS_SERVER "nameofserver"
#define STATS_DB "nameofmysqldb"
#define STATS_USER "username"
#define STATS_PWD "password"
int stats_player_killed_player(char * attacker, char * victim);
int stats_player_killed_monster(char * attacker);
int stats_monster_killed_player(char * victim);
int stats_pk_details(char * attacker, long a_level, char * victim, long v_level);
int stats_npc_kill(long vnum, char * name);
int stats_delete(char * name);
#endif
---end stats_module.h---
Next is the actual C code for the functions listed.
---begin stats_module.c---
#include <stdio.h>
#include <stdlib.h>
#include "stats_module.h"
#include "/usr/include/mysql/mysql.h"
int stats_player_killed_player(char * attacker, char * victim) {
MYSQL *hnd;
char sql[1000];
if ( (hnd = mysql_init(NULL)) == NULL ) {
return -1;
}
if ( mysql_real_connect(hnd,STATS_SERVER,STATS_USER,STATS_PWD,STATS_DB,0,NULL,0) == NULL ) {
return -2;
}
sprintf(sql,"update stats set p_kills = p_kills + 1 where player = '%s'", attacker);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
if ( mysql_affected_rows(hnd) == 0 ) {
sprintf(sql,"insert into stats values( '%s', 1, 0, 0, 0 )", attacker);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -4;
}
}
sprintf(sql,"update stats set p_deaths = p_deaths + 1 where player = '%s'", victim);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -5;
}
if ( mysql_affected_rows(hnd) == 0 ) {
sprintf(sql,"insert into stats values( '%s', 0, 0, 1, 0 )", victim);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -6;
}
}
mysql_close(hnd);
return 0;
}
int stats_player_killed_monster(char * attacker) {
MYSQL *hnd;
char sql[1000];
if ( (hnd = mysql_init(NULL)) == NULL ) {
return -1;
}
if ( mysql_real_connect(hnd,STATS_SERVER,STATS_USER,STATS_PWD,STATS_DB,0,NULL,0) == NULL ) {
return -2;
}
sprintf(sql,"update stats set m_kills = m_kills + 1 where player = '%s'", attacker);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
if ( mysql_affected_rows(hnd) == 0 ) {
sprintf(sql,"insert into stats values( '%s', 0, 1, 0, 0 )", attacker);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -4;
}
}
mysql_close(hnd);
return 0;
}
int stats_monster_killed_player(char * victim) {
MYSQL *hnd;
char sql[1000];
if ( (hnd = mysql_init(NULL)) == NULL ) {
return -1;
}
if ( mysql_real_connect(hnd,STATS_SERVER,STATS_USER,STATS_PWD,STATS_DB,0,NULL,0) == NULL ) {
return -2;
}
sprintf(sql,"update stats set m_deaths = m_deaths + 1 where player = '%s'", victim);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
if ( mysql_affected_rows(hnd) == 0 ) {
sprintf(sql,"insert into stats values( '%s', 0, 0, 0, 1 )", victim);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -4;
}
}
mysql_close(hnd);
return 0;
}
int stats_pk_details(char * attacker, long a_level, char * victim, long v_level) {
MYSQL *hnd;
char sql[1000];
if ( (hnd = mysql_init(NULL)) == NULL ) {
return -1;
}
if ( mysql_real_connect(hnd,STATS_SERVER,STATS_USER,STATS_PWD,STATS_DB,0,NULL,0) == NULL ) {
return -2;
}
sprintf(sql,"insert into kills values ( '%s', %ld, '%s', %ld, now() )", attacker, a_level, victim, v_level );
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
mysql_close(hnd);
return 0;
}
int stats_npc_kill(long vnum, char * name) {
MYSQL *hnd;
char sql[1000];
if ( (hnd = mysql_init(NULL)) == NULL ) {
return -1;
}
if ( mysql_real_connect(hnd,STATS_SERVER,STATS_USER,STATS_PWD,STATS_DB,0,NULL,0) == NULL ) {
return -2;
}
sprintf(sql,"update npc_kills set kills=kills+1 where vnum = %ld and name = '%s'",vnum, name);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
if ( mysql_affected_rows(hnd) == 0 ) {
sprintf(sql,"insert into npc_kills values( %ld, '%s', 1 )", vnum, name);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -4;
}
}
mysql_close(hnd);
return 0;
}
int stats_delete(char * name) {
MYSQL *hnd;
char sql[1000];
if ( (hnd = mysql_init(NULL)) == NULL ) {
return -1;
}
if ( mysql_real_connect(hnd,STATS_SERVER,STATS_USER,STATS_PWD,STATS_DB,0,NULL,0) == NULL ) {
return -2;
}
sprintf(sql,"delete from stats where player ='%s'", name);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
sprintf(sql,"delete from kills where attacker ='%s'", name);
if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
return -3;
}
mysql_close(hnd);
return 0;
}
---end stats_module.c---
--
Otis Viles: Mudder, RPGer, KMFDM fan, Internet Oracle Priest
drey@speakeasy.org, http://www.daestroke.com/cierhart/
drey@daestroke.com, http://stormclouds.daestroke.com/
Making iDirt 1.82 a safer place, one bug at a time.
Message 2 of 2 for MySQL/C API and MUDS:
==================================================================
Hey, never mind my problems.. I just got it to compile!!! :)
I am now using:
LIBS = -lcrypt -lm -lmysqlclient -lnsl
LDFLAGS = -L/usr/lib/mysql -I/usr/include/mysql/
I guess I was pointing to the wrong place for the -L, and the
mysqlclient/nsl helped too, or at least it didn't spew out any errors to
me this time!
Great, now I can get one with implementing some fun mysql stuff! :)
Andy
In article <MPG.153f6e8e73cbfe54989686@news-server.neo.rr.com>,
amnuts@talker.com says...
> In article <3ad51eb9.19304512@news.speakeasy.org>, drey@speakeasy.org
> says...
> > On Wed, 11 Apr 2001 13:03:35 GMT, Andrew Collington <amnuts@talker.com> wrote:
> > >[snip all the code]
> > >
> > >That was *really* awesome of you! Thanks so much for all your help! I
> > >am printing it out now (actually using paper in this day and age?!) with
> > >the anticipation of going through it line by line.
> >
> > Cool. You can probably find some improvements to make. ;)
> >
> > >You've been an invaluable help, Otis! Thanks again :)
> >
> > Well, I hope it can help you out. The MySQL API is pretty good actually,
> > not too hard to work with at all.
> >
>
>
> The only problem I'm having is linking the actual code... I keep
> getting these errors:
>
> objects/dbtest.o: In function `mysqltest_record_online':
> objects/dbtest.o(.text+0xe): undefined reference to `mysql_init'
> objects/dbtest.o(.text+0x51): undefined reference to
> `mysql_real_connect'
> objects/dbtest.o(.text+0x9c): undefined reference to `mysql_real_query'
> objects/dbtest.o(.text+0xb0): undefined reference to `mysql_close'
> collect2: ld returned 1 exit status
>
> Even though I have:
>
> #include "/usr/include/mysql/mysql.h"
>
> defined and I'm compiling with:
>
> LIBS = -lcrypt -lm
> LDFLAGS = -L/usr/local/lib/mysql -I/usr/include/mysql/
>
> Any ideas what the problem might be? I've checked whether mysql.h
> actually exists where I'm pointing to, and it does. Maybe I'm just
> missing something very obvious when compiling? :)
>
> I did, just last night, find *one* page on the net other than the mysql
> docs that used mysql in C. Didn't know if it'll be useful for you to
> look at it, but here it is anyway:
>
> http://cis.stvincent.edu/carlsond/mysql.html
>
> It's mainly for cgi-bin based apps, but could be used in MUDs, etc.
>