/* cleanup the server table, removing old entries */
void db_cleanserver()
{
MYSQL_RES *resptr;
int curtime = time(NULL);
char **res;
if (ServerLastClean == -1)
ServerLastClean = curtime;
if (curtime > (ServerLastClean + ServerCleanFreq))
{
ServerLastClean = curtime;
db_query("SELECT server FROM " TBL_SERV
" WHERE online=\"N\" AND lastsplit<FROM_UNIXTIME(\"%d\")",
curtime - ServerCacheTime);
resptr = mysql_store_result(myptr);
if (mysql_num_rows(resptr))
{
while ((res = mysql_fetch_row(resptr)))
{
char *server = db_escape(res[0]);
db_delserver(server);
free(server);
}
mysql_free_result(resptr);
db_query("DELETE FROM " TBL_SERV
" WHERE online=\"N\" AND lastsplit<FROM_UNIXTIME(\"%d\")",
curtime - ServerCacheTime);
}
}
}
/* cleanup the user table, removing old entries */
void db_cleanuser()
{
MYSQL_RES *resptr;
int curtime = time(NULL);
char **res;
if (UserLastClean == -1)
UserLastClean = curtime;
if (curtime > (UserLastClean + UserCleanFreq))
{
UserLastClean = curtime;
db_query("SELECT nick FROM " TBL_USER
" WHERE online=\"N\" AND lastquit<FROM_UNIXTIME(\"%d\")",
curtime - UserCacheTime);
resptr = mysql_store_result(myptr);
if (mysql_num_rows(resptr))
{
while ((res = mysql_fetch_row(resptr)))
{
char *nick = db_escape(res[0]);
db_delnick(nick);
free(nick);
}
mysql_free_result(resptr);
db_query("DELETE FROM " TBL_USER
" WHERE online=\"N\" AND lastquit<FROM_UNIXTIME(\"%d\")",
curtime - UserCacheTime);
}
}
}
int sfilter_id( const char *name )
{
PGresult *res;
int id;
char *esc;
if( NULL == (esc = db_escape(name)))
return -1;
res = db_query( "SELECT id FROM juke_sfilter "
"WHERE name = '%s'", esc);
free(esc);
if( ! res || PQresultStatus(res) != PGRES_TUPLES_OK ){
syslog( LOG_ERR, "sfilter_id: %s", db_errstr());
PQclear(res);
return -1;
}
if( PQntuples(res) != 1 ){
PQclear(res);
return -1;
}
id = pgint( res, 0, 0);
PQclear(res);
return id;
}
int sfilter_add( const char *name )
{
PGresult *res;
char *esc;
int id;
res = db_query( "SELECT nextval('juke_sfilter_id_seq')" );
if( !res || PQresultStatus(res) != PGRES_TUPLES_OK ){
syslog( LOG_ERR, "sfilter_add: %s", db_errstr());
PQclear(res);
return -1;
}
if( 0 > (id = pgint(res,0,0))){
PQclear(res);
return -1;
}
PQclear(res);
if( NULL == (esc = db_escape(name)))
return -1;
res = db_query( "INSERT INTO juke_sfilter(id, name) "
"VALUES( %d, '%s')", id, esc );
free(esc);
if( ! res || PQresultStatus(res) != PGRES_COMMAND_OK ){
syslog( LOG_ERR, "sfilter_add: %s", db_errstr());
PQclear(res);
return -1;
}
PQclear(res);
return id;
}
int city_single_save( City *pCity, FILE *fp )
{
if ( pCity == NULL )
return -1;
char szSQL[2048];
char reconnect_flag = 0;
char szText_corps_num[CityCorpsMax][4 * 80] = { 0 };
char szText_corps_wound[CityCorpsMax][4 * 80] = { 0 };
for ( int tmpi = 0; tmpi < CityCorpsMax; tmpi++ )
{
db_escape((const char *)pCity->corps_num[tmpi], szText_corps_num[tmpi], sizeof(int)*CityCorpsLevelMax);
db_escape((const char *)pCity->corps_wound[tmpi], szText_corps_wound[tmpi], sizeof(int)*CityCorpsLevelMax);
}
RE_CITY_REPLACE:
sprintf( szSQL, "REPLACE INTO city (`cityid`,`laird_type`,`laird_actorid`,`laird_name`,`laird_shape`,`laird_level`,`laird_lastlogin`,`match_cityid`,`aclass`,`skin`,`clubid`,`posx`,`posy`,`wood`,`food`,`iron`,`mithril`,`gold`, "
"`corps_num1`, `corps_num2`, `corps_num3`, `corps_num4`, `corps_num5`, "
"`corps_wound1`, `corps_wound2`, `corps_wound3`, `corps_wound4`, `corps_wound5` ) "
"Values('%d','%d','%d','%s','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d',"
"'%s','%s','%s','%s','%s',"
"'%s','%s','%s','%s','%s')",
pCity->cityid, pCity->laird_type, pCity->laird_actorid, pCity->laird_name, pCity->laird_shape, pCity->laird_level, pCity->laird_lastlogin, pCity->match_cityid, pCity->aclass, pCity->skin,
pCity->clubid, pCity->posx, pCity->posy, pCity->wood, pCity->food, pCity->iron, pCity->mithril, pCity->gold,
szText_corps_num[0], szText_corps_num[1], szText_corps_num[2], szText_corps_num[3], szText_corps_num[4],
szText_corps_wound[0], szText_corps_wound[1], szText_corps_wound[2], szText_corps_wound[3], szText_corps_wound[4] );
if ( fp )
{
fprintf( fp, "%s;\n", szSQL );
}
else if ( mysql_query( myGame, szSQL ) )
{
printf( "Query failed (%s)\n", mysql_error( myGame ) );
write_gamelog( "%s", szSQL );
if ( reconnect_flag )
return -1;
if ( mysql_ping( myGame ) != 0 )
{
db_reconnect_game();
reconnect_flag = 1;
goto RE_CITY_REPLACE;
}
return -1;
}
// 保存建筑
building_save( pCity, fp );
return 0;
}
void db_removefromchans(int nickid)
{
MYSQL_RES *resptr;
char **res;
db_query("SELECT " TBL_ISON ".chanid, channel FROM " TBL_ISON ", "
TBL_CHAN " WHERE nickid=\'%d\' AND " TBL_CHAN ".chanid = "
TBL_ISON ".chanid", nickid);
resptr = mysql_store_result(myptr);
db_query("DELETE FROM " TBL_ISON " WHERE nickid=\'%d\'", nickid);
while ((res = mysql_fetch_row(resptr)))
{
char *chan = db_escape(res[1]);
db_checkemptychan(atoi(res[0]), chan);
free(chan);
}
mysql_free_result(resptr);
}
int sfilter_setfilter( int id, const char *filter )
{
PGresult *res;
char *esc;
if( NULL == (esc = db_escape( filter )))
return -1;
res = db_query( "UPDATE juke_sfilter SET filter ='%s' "
"WHERE id = %d", esc, id );
free(esc);
if( ! res || PQresultStatus(res) != PGRES_COMMAND_OK ){
syslog( LOG_ERR, "sfilter_setfilter: %s", db_errstr());
PQclear(res);
return -1;
}
PQclear(res);
return 0;
}
static int setup_create_db(const char *host, const char *user,
const char *pass, const char *name)
{
char name_buf[512];
db_escape(name_buf, name, 256);
db_query(0, "CREATE DATABASE %s", name_buf);
if(setup_err)
{
printf("Could not create database.\n");
return -1;
}
if(db_select(0, name) != 0)
{
printf("Could not select database.\n");
return -1;
}
printf("Created database `%s'.\n", name);
return 0;
}
static int setup_create_user(const char *host, const char *user,
const char *pass, const char *name)
{
int ret, major, minor, patch;
char root[256], user_buf[512], pass_buf[512], name_buf[512];
char *p1, *p2, *p3;
DB_RESULT result;
DB_ROW row;
printf("Setup will attempt to add this user to your database.\n\n");
printf("This will only work if your bot resides on the same\n");
printf("machines as your MySQL server.\n\n");
printf("This involves connecting to your MySQL as root.\n\n");
printf("Hit Ctrl-C if you wish to abort, or enter to confirm.\n");
getchar();
setup_err = 0;
/* try to connect as root */
ret = db_connect(0, host, "root", NULL, "mysql", setup_error);
if(ret != 0)
{
printf("Your MySQL server has a root password set.\n");
printf("Setup will use this to add a user to your database.\n\n");
printf("Leave blank to abort.\n\n");
system("stty -echo");
text_input("Enter your MySQL root password", root, 256, NULL);
system("stty echo");
if(root[0] == 0)
return -1;
if(db_connect(0, host, "root", root, "mysql", setup_error) != 0)
{
printf("Setup could not connect to your database.\n\n");
printf("Check in your config file that your database\n");
printf("settings are correct. Also try manually\n");
printf("adding a MySQL user.\n");
return -1;
}
}
result = db_query(0, "SELECT VERSION()");
row = db_fetch(result);
p1 = strtok(row[0], ".-");
p2 = strtok(NULL, ".-");
p3 = strtok(NULL, ".-");
if((p1 == NULL) || (p2 == NULL) || (p3 == NULL))
{
printf("Could not determine MySQL version.\n");
return -1;
}
major = p1 ? atoi(p1) : 0;
minor = p2 ? atoi(p2) : 0;
patch = p3 ? atoi(p3) : 0;
db_escape(user_buf, user, 256);
db_escape(pass_buf, pass, 256);
db_escape(name_buf, name, 256);
if(((major >= 3) && (minor >= 23)) || (major > 3))
{
db_query(0, "GRANT ALL PRIVILEGES ON %s.* TO %s@localhost "
"IDENTIFIED BY '%s' WITH GRANT OPTION", name_buf, user_buf,
pass_buf);
}
else if((major >= 3) && (minor == 22) && (patch >= 11))
{
db_query(0, "INSERT INTO user (host, user, password) VALUES("
"'localhost', '%s', PASSWORD('%s'))", user_buf, pass_buf);
db_query(0, "INSERT INTO db VALUES('%%', '%s', '%s', 'Y', 'Y', "
"'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')", name_buf, user_buf);
db_query(0, "FLUSH PRIVILEGES");
}
else
{
printf("Setup doesn't know how to deal with MySQL %d.%d.%d.\n\n",
major, minor, patch);
printf("You're on your own.\n");
return -1;
}
db_disconnect(0);
if(setup_err)
{
printf("Could not create a user. You'll have to do it manually\n");
return -1;
}
printf("Created user `%s' on `%s'.\n", user, host);
return 0;
}
