[irc/thales.git] / src / db.c

/*  Thales - IRC to Relational Database Gateway
 *  Copyright (C) 2002 Lucas Nussbaum <lucas@lucas-nussbaum.net>
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
/* Thales' database interface */

#include "db.h"
#include "misc.h"
#include "actions.h"
#ifdef HASHLISTSUPPORT
#include "hashlist.h"
#endif

extern char *MysqlServer;
extern char *MysqlDatabase;
extern char *MysqlUser;
extern char *MysqlPassword;

extern unsigned int nbusers_max;
extern unsigned int nbusers;
extern unsigned int nbchans_max;
extern unsigned int nbchans;

extern int verbose;

MYSQL *myptr;

#ifdef HASHLISTSUPPORT
hashlist hashnicks;
hashlist hashchans;
hashlist hashservs;
#endif

/* last time we cleaned the server table */
static int ServerLastClean = -1;
extern int ServerCleanFreq;
extern int ServerCacheTime;

/* last time we cleaned the user table */
static int UserLastClean = -1;
extern int UserCleanFreq;
extern int UserCacheTime;


void db_connect()
{
	MYSQL_RES *resptr;
	if (myptr)
		mysql_close(myptr);
	/* connecting to MySQL */
	myptr = mysql_init(NULL);
	if (!mysql_real_connect
		 (myptr, MysqlServer, MysqlUser, MysqlPassword, MysqlDatabase, 0,
		  NULL, 0))
		fatal("Failed to connect to database : %s\n", mysql_error(myptr));

	/* Cleaning up the database */
	db_query("DELETE FROM " TBL_CHAN);
	db_query("DELETE FROM " TBL_ISON);
	db_query("DELETE FROM " TBL_SERV);
	db_query("DELETE FROM " TBL_USER);

	db_query("SELECT val FROM " TBL_MAXV " WHERE type=\'users\'");
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		nbusers_max = atoi(*mysql_fetch_row(resptr));
	else
		db_query("INSERT INTO " TBL_MAXV " VALUES ('users', '0', NOW())");
	mysql_free_result(resptr);

	db_query("SELECT val FROM " TBL_MAXV " WHERE type=\'channels\'");
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		nbchans_max = atoi(*mysql_fetch_row(resptr));
	else
		db_query("INSERT INTO " TBL_MAXV " VALUES ('channels', '0', NOW())");
	mysql_free_result(resptr);

#ifdef HASHLISTSUPPORT
	/* init the hash lists */
	hashnicks = newhashlist();
	hashchans = newhashlist();
	hashservs = newhashlist();
#endif
}

/* escape the string */
char *db_escape(char *ch)
{
	char *buf = NULL;
	int size;
	if (ch)
	{
		size = (strlen(ch)) * 2 + 1;
		buf = (char *) malloc(sizeof(char) * size);
		if (*ch)
			mysql_real_escape_string(myptr, buf, ch, strlen(ch));
		else
			strcpy(buf, "");
	}
	return buf;
}

/* send an SQL query to the database */
int db_query(const char *fmt, ...)
{
	va_list args;
	char buf[2048];				  /* should be enough for long queries (don't underestimate :o)) */
	va_start(args, fmt);
	vsprintf(buf, fmt, args);
	if (verbose)
		log(">SQL : %s", buf);
	if (mysql_real_query(myptr, buf, strlen(buf)))
		fatal("Query failed: %s", mysql_error(myptr));
	return 0;
}

/* serv should be db_escape'd before call */
/* -1 if server not found, servid else */
int db_checkserver(char *serv)
{
	int servid = -1;
	MYSQL_RES *resptr;
	db_query("SELECT servid FROM " TBL_SERV " WHERE server=\"%s\"", serv);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		servid = atoi(*mysql_fetch_row(resptr));
	mysql_free_result(resptr);
	return servid;
}

/* serv should be db_escape'd before call */
int db_getserver(char *serv)
{
#ifdef HASHLISTSUPPORT
	strtolwr(serv);
	return hash_find(hashservs, serv, KEYOTHER);
#else
	MYSQL_RES *resptr;
	int res = 0;

	db_query("SELECT servid FROM " TBL_SERV " WHERE server=\'%s\'", serv);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		res = atoi(*mysql_fetch_row(resptr));
	mysql_free_result(resptr);
	return res;
#endif
}

/* changes a nick in the hashlist */
void db_chgnick(char *newnick, char *oldnick)
{
#ifdef HASHLISTSUPPORT
	/* people often change from NICK-sthing to NICK-sthing */
	strtolwr(newnick);
	strtolwr(oldnick);
	hash_update(hashnicks, newnick, oldnick, KEYOTHER);
#endif
}

/* remove a server from the hashlist */
void db_delserver(char *server)
{
#ifdef HASHLISTSUPPORT
	strtolwr(server);
	hash_del(hashservs, server, KEYOTHER);
#endif
}

/* add a server to the hashlist */
void db_addserver(char *server, int servid)
{
#ifdef HASHLISTSUPPORT
	strtolwr(server);
	hash_add(hashservs, server, servid, KEYOTHER);
#endif
}

/* add a nick to the hashlist */
void db_addnick(char *nick, int nickid)
{
#ifdef HASHLISTSUPPORT
	strtolwr(nick);
	hash_add(hashnicks, nick, nickid, KEYOTHER);
#endif
}

/* add a nick to the hashlist */
void db_delnick(char *nick)
{
#ifdef HASHLISTSUPPORT
	strtolwr(nick);
	hash_del(hashnicks, nick, KEYOTHER);
#endif
}

/* nick should be db_escape'd before call */
/* -1 if nick not found, nickid else */
int db_checknick(char *nick)
{
#ifdef HASHLISTSUPPORT
	int res = 0;
	char *nicklow = strdup(nick);
	strtolwr(nicklow);
	res = hash_find_unsure(hashnicks, nicklow, KEYOTHER);
	free(nicklow);
	return res;
#else
	int nickid = -1;
	MYSQL_RES *resptr;
	db_query("SELECT nickid FROM " TBL_USER " WHERE nick=\"%s\"", nick);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		nickid = atoi(*mysql_fetch_row(resptr));
	mysql_free_result(resptr);
	return nickid;
#endif
}

/* nick should be db_escape'd before call */
int db_getnick(char *nick)
{
#ifdef HASHLISTSUPPORT
	int res = 0;
	char *nicklow = strdup(nick);
	strtolwr(nicklow);
	res = hash_find(hashnicks, nicklow, KEYOTHER);
	free(nicklow);
	return res;
#else
	MYSQL_RES *resptr;
	int res = 0;

	db_query("SELECT nickid FROM " TBL_USER " WHERE nick=\'%s\'", nick);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		res = atoi(*mysql_fetch_row(resptr));
	else
		fatal("nickname not found !");
	mysql_free_result(resptr);
	return res;
#endif
}

int db_getservfromnick(char *nick)
{
	MYSQL_RES *resptr;
	int res = 0;
	db_query("SELECT servid FROM " TBL_USER " WHERE nick=\'%s\'", nick);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		res = atoi(*mysql_fetch_row(resptr));
	else
		fatal("nickname not found !");
	mysql_free_result(resptr);
	return res;
}

int db_insertid()
{
	return mysql_insert_id(myptr);
}

void db_removenick(char *nick)
{
	int nickid = db_getnick(nick);
	//TODO
	if (nickid == 0)
		fatal("nickid 0");
	db_removefromchans(nickid);
	if (UserCacheTime)
		db_query("UPDATE " TBL_USER
					" SET online=\"N\", lastquit=NOW(), servid=NULL WHERE nickid=\"%d\"",
					nickid);
	else
		db_query("DELETE FROM " TBL_USER " WHERE nickid=\'%d\'", nickid);
}


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);
}

void db_checkemptychan(int chanid, char *chan)
{
	MYSQL_RES *resptr2;
	db_query("SELECT chanid FROM " TBL_ISON " WHERE chanid=\'%d\'", chanid);
	resptr2 = mysql_store_result(myptr);
	if (!mysql_num_rows(resptr2))
	{
		db_query("DELETE FROM " TBL_CHAN " WHERE chanid=\'%d\'", chanid);
#ifdef HASHLISTSUPPORT
		hash_del(hashchans, chan, KEYCHAN);
#endif
		nbchans--;
	}
	mysql_free_result(resptr2);
}

int db_getlusers(int type)
{
	MYSQL_RES *resptr;
	int retcode;
	switch (type)
	{
	case LUSERS_USERS:
		db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_li=\'N\'");
		break;
	case LUSERS_USERSINV:
		db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_li=\'Y\'");
		break;
	case LUSERS_OPERS:
		db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_lo=\'Y\'");
		break;
	case LUSERS_CHAN:
		db_query("SELECT COUNT(*) FROM " TBL_CHAN);
		break;
	case LUSERS_SERV:
		db_query("SELECT COUNT(*) FROM " TBL_SERV);
		break;
	case LUSERS_USERSGLOB:
		db_query("SELECT COUNT(*) FROM " TBL_USER);
		break;
	case LUSERS_USERSMAX:
		db_query("SELECT val FROM " TBL_MAXV " WHERE type='users'");
		break;
	}
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		retcode = atoi(*mysql_fetch_row(resptr));
	else
		retcode = -1;
	mysql_free_result(resptr);
	return retcode;
}

/* chan should be db_escape'd before call */
int db_getchannel(char *chan)
{
#ifdef HASHLISTSUPPORT
	strtolwr(chan);
	return hash_find(hashchans, chan, KEYCHAN);
#else
	int res = 0;

	MYSQL_RES *resptr;
	strtolwr(chan);
	db_query("SELECT chanid FROM " TBL_CHAN " WHERE channel=\'%s\'", chan);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		res = atoi(*mysql_fetch_row(resptr));
	else
		fatal("channel not found !");
	mysql_free_result(resptr);
	return res;
#endif
}

/* chan should be db_escape'd before call */
/* chan is created if not exists */
int db_getchancreate(char *chan)
{
	int res = -1;

#ifdef HASHLISTSUPPORT
	strtolwr(chan);
	res = hash_find_unsure(hashchans, chan, KEYCHAN);
#else
	MYSQL_RES *resptr;
	strtolwr(chan);
	db_query("SELECT chanid FROM " TBL_CHAN " WHERE channel=\'%s\'", chan);
	resptr = mysql_store_result(myptr);
	if (mysql_num_rows(resptr))
		res = atoi(*mysql_fetch_row(resptr));
	mysql_free_result(resptr);
#endif
	if (res == -1)

	{
		db_query("INSERT INTO " TBL_CHAN " (channel) VALUES (\'%s\')", chan);
		res = db_insertid();
#ifdef HASHLISTSUPPORT
		hash_add(hashchans, chan, res, KEYCHAN);
#endif
		nbchans++;
		do_checknbchansmax();
	}
	return res;
}

/* close connection to DBMS */
void db_close()
{
	if (myptr)
		mysql_close(myptr);
	myptr = NULL;
}

/* 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);
		}
	}
}
Commit	Line	Data
2ace9480	1	/* Thales - IRC to Relational Database Gateway
	2	* Copyright (C) 2002 Lucas Nussbaum <lucas@lucas-nussbaum.net>
	3	*
	4	* This program is free software; you can redistribute it and/or modify
	5	* it under the terms of the GNU General Public License as published by
	6	* the Free Software Foundation; either version 2 of the License, or
	7	* (at your option) any later version.
	8	*
	9	* This program is distributed in the hope that it will be useful,
	10	* but WITHOUT ANY WARRANTY; without even the implied warranty of
	11	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	12	* GNU General Public License for more details.
	13	*
	14	* You should have received a copy of the GNU General Public License
	15	* along with this program; if not, write to the Free Software
	16	* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
	17	*/
	18	/* Thales' database interface */
	19
	20	#include "db.h"
	21	#include "misc.h"
	22	#include "actions.h"
	23	#ifdef HASHLISTSUPPORT
	24	#include "hashlist.h"
	25	#endif
	26
	27	extern char *MysqlServer;
	28	extern char *MysqlDatabase;
	29	extern char *MysqlUser;
	30	extern char *MysqlPassword;
	31
	32	extern unsigned int nbusers_max;
	33	extern unsigned int nbusers;
	34	extern unsigned int nbchans_max;
	35	extern unsigned int nbchans;
	36
	37	extern int verbose;
	38
	39	MYSQL *myptr;
	40
	41	#ifdef HASHLISTSUPPORT
	42	hashlist hashnicks;
	43	hashlist hashchans;
	44	hashlist hashservs;
	45	#endif
	46
	47	/* last time we cleaned the server table */
	48	static int ServerLastClean = -1;
	49	extern int ServerCleanFreq;
	50	extern int ServerCacheTime;
	51
	52	/* last time we cleaned the user table */
	53	static int UserLastClean = -1;
	54	extern int UserCleanFreq;
	55	extern int UserCacheTime;
	56
	57
	58	void db_connect()
	59	{
	60	MYSQL_RES *resptr;
	61	if (myptr)
	62	mysql_close(myptr);
	63	/* connecting to MySQL */
	64	myptr = mysql_init(NULL);
65	if (!mysql_real_connect
66	(myptr, MysqlServer, MysqlUser, MysqlPassword, MysqlDatabase, 0,
67	NULL, 0))
68	fatal("Failed to connect to database : %s\n", mysql_error(myptr));
69
70	/* Cleaning up the database */
71	db_query("DELETE FROM " TBL_CHAN);
72	db_query("DELETE FROM " TBL_ISON);
73	db_query("DELETE FROM " TBL_SERV);
74	db_query("DELETE FROM " TBL_USER);
75
76	db_query("SELECT val FROM " TBL_MAXV " WHERE type=\'users\'");
77	resptr = mysql_store_result(myptr);
78	if (mysql_num_rows(resptr))
79	nbusers_max = atoi(*mysql_fetch_row(resptr));
80	else
81	db_query("INSERT INTO " TBL_MAXV " VALUES ('users', '0', NOW())");
82	mysql_free_result(resptr);
83
84	db_query("SELECT val FROM " TBL_MAXV " WHERE type=\'channels\'");
85	resptr = mysql_store_result(myptr);
86	if (mysql_num_rows(resptr))
87	nbchans_max = atoi(*mysql_fetch_row(resptr));
88	else
89	db_query("INSERT INTO " TBL_MAXV " VALUES ('channels', '0', NOW())");
90	mysql_free_result(resptr);
91
92	#ifdef HASHLISTSUPPORT
93	/* init the hash lists */
94	hashnicks = newhashlist();
95	hashchans = newhashlist();
96	hashservs = newhashlist();
97	#endif
98	}
99
100	/* escape the string */
101	char db_escape(char ch)
102	{
103	char *buf = NULL;
104	int size;
105	if (ch)
106	{
107	size = (strlen(ch)) * 2 + 1;
108	buf = (char ) malloc(sizeof(char) size);
109	if (*ch)
110	mysql_real_escape_string(myptr, buf, ch, strlen(ch));
111	else
112	strcpy(buf, "");
113	}
114	return buf;
115	}
116
117	/* send an SQL query to the database */
118	int db_query(const char *fmt, ...)
119	{
120	va_list args;
121	char buf[2048]; /* should be enough for long queries (don't underestimate :o)) */
122	va_start(args, fmt);
123	vsprintf(buf, fmt, args);
124	if (verbose)
125	log(">SQL : %s", buf);
126	if (mysql_real_query(myptr, buf, strlen(buf)))
127	fatal("Query failed: %s", mysql_error(myptr));
128	return 0;
129	}
130
131	/* serv should be db_escape'd before call */
132	/* -1 if server not found, servid else */
133	int db_checkserver(char *serv)
134	{
135	int servid = -1;
136	MYSQL_RES *resptr;
137	db_query("SELECT servid FROM " TBL_SERV " WHERE server=\"%s\"", serv);
138	resptr = mysql_store_result(myptr);
139	if (mysql_num_rows(resptr))
140	servid = atoi(*mysql_fetch_row(resptr));
141	mysql_free_result(resptr);
142	return servid;
143	}
144
145	/* serv should be db_escape'd before call */
146	int db_getserver(char *serv)
147	{
148	#ifdef HASHLISTSUPPORT
149	strtolwr(serv);
150	return hash_find(hashservs, serv, KEYOTHER);
151	#else
152	MYSQL_RES *resptr;
153	int res = 0;
154
155	db_query("SELECT servid FROM " TBL_SERV " WHERE server=\'%s\'", serv);
156	resptr = mysql_store_result(myptr);
157	if (mysql_num_rows(resptr))
158	res = atoi(*mysql_fetch_row(resptr));
159	mysql_free_result(resptr);
160	return res;
161	#endif
162	}
163
164	/* changes a nick in the hashlist */
165	void db_chgnick(char newnick, char oldnick)
166	{
167	#ifdef HASHLISTSUPPORT
168	/* people often change from NICK-sthing to NICK-sthing */
169	strtolwr(newnick);
170	strtolwr(oldnick);
171	hash_update(hashnicks, newnick, oldnick, KEYOTHER);
172	#endif
173	}
174
175	/* remove a server from the hashlist */
176	void db_delserver(char *server)
177	{
178	#ifdef HASHLISTSUPPORT
179	strtolwr(server);
180	hash_del(hashservs, server, KEYOTHER);
181	#endif
182	}
183
184	/* add a server to the hashlist */
185	void db_addserver(char *server, int servid)
186	{
187	#ifdef HASHLISTSUPPORT
188	strtolwr(server);
189	hash_add(hashservs, server, servid, KEYOTHER);
190	#endif
191	}
192
193	/* add a nick to the hashlist */
194	void db_addnick(char *nick, int nickid)
195	{
196	#ifdef HASHLISTSUPPORT
197	strtolwr(nick);
198	hash_add(hashnicks, nick, nickid, KEYOTHER);
199	#endif
200	}
201
202	/* add a nick to the hashlist */
203	void db_delnick(char *nick)
204	{
205	#ifdef HASHLISTSUPPORT
206	strtolwr(nick);
207	hash_del(hashnicks, nick, KEYOTHER);
208	#endif
209	}
210
211	/* nick should be db_escape'd before call */
212	/* -1 if nick not found, nickid else */
213	int db_checknick(char *nick)
214	{
215	#ifdef HASHLISTSUPPORT
216	int res = 0;
217	char *nicklow = strdup(nick);
218	strtolwr(nicklow);
219	res = hash_find_unsure(hashnicks, nicklow, KEYOTHER);
220	free(nicklow);
221	return res;
222	#else
223	int nickid = -1;
224	MYSQL_RES *resptr;
225	db_query("SELECT nickid FROM " TBL_USER " WHERE nick=\"%s\"", nick);
226	resptr = mysql_store_result(myptr);
227	if (mysql_num_rows(resptr))
228	nickid = atoi(*mysql_fetch_row(resptr));
229	mysql_free_result(resptr);
230	return nickid;
231	#endif
232	}
233
234	/* nick should be db_escape'd before call */
235	int db_getnick(char *nick)
236	{
237	#ifdef HASHLISTSUPPORT
238	int res = 0;
239	char *nicklow = strdup(nick);
240	strtolwr(nicklow);
241	res = hash_find(hashnicks, nicklow, KEYOTHER);
242	free(nicklow);
243	return res;
244	#else
245	MYSQL_RES *resptr;
246	int res = 0;
247
248	db_query("SELECT nickid FROM " TBL_USER " WHERE nick=\'%s\'", nick);
249	resptr = mysql_store_result(myptr);
250	if (mysql_num_rows(resptr))
251	res = atoi(*mysql_fetch_row(resptr));
252	else
253	fatal("nickname not found !");
254	mysql_free_result(resptr);
255	return res;
256	#endif
257	}
258
259	int db_getservfromnick(char *nick)
260	{
261	MYSQL_RES *resptr;
262	int res = 0;
263	db_query("SELECT servid FROM " TBL_USER " WHERE nick=\'%s\'", nick);
264	resptr = mysql_store_result(myptr);
265	if (mysql_num_rows(resptr))
266	res = atoi(*mysql_fetch_row(resptr));
267	else
268	fatal("nickname not found !");
269	mysql_free_result(resptr);
270	return res;
271	}
272
273	int db_insertid()
274	{
275	return mysql_insert_id(myptr);
276	}
277
278	void db_removenick(char *nick)
279	{
280	int nickid = db_getnick(nick);
281	//TODO
282	if (nickid == 0)
283	fatal("nickid 0");
284	db_removefromchans(nickid);
285	if (UserCacheTime)
286	db_query("UPDATE " TBL_USER
287	" SET online=\"N\", lastquit=NOW(), servid=NULL WHERE nickid=\"%d\"",
288	nickid);
289	else
290	db_query("DELETE FROM " TBL_USER " WHERE nickid=\'%d\'", nickid);
291	}
292
293
294	void db_removefromchans(int nickid)
295	{
296	MYSQL_RES *resptr;
297	char **res;
298	db_query("SELECT " TBL_ISON ".chanid, channel FROM " TBL_ISON ", "
299	TBL_CHAN " WHERE nickid=\'%d\' AND " TBL_CHAN ".chanid = "
300	TBL_ISON ".chanid", nickid);
301	resptr = mysql_store_result(myptr);
302	db_query("DELETE FROM " TBL_ISON " WHERE nickid=\'%d\'", nickid);
303	while ((res = mysql_fetch_row(resptr)))
304	{
305	char *chan = db_escape(res[1]);
306	db_checkemptychan(atoi(res[0]), chan);
307	free(chan);
308	}
309	mysql_free_result(resptr);
310	}
311
312	void db_checkemptychan(int chanid, char *chan)
313	{
314	MYSQL_RES *resptr2;
315	db_query("SELECT chanid FROM " TBL_ISON " WHERE chanid=\'%d\'", chanid);
316	resptr2 = mysql_store_result(myptr);
317	if (!mysql_num_rows(resptr2))
318	{
319	db_query("DELETE FROM " TBL_CHAN " WHERE chanid=\'%d\'", chanid);
320	#ifdef HASHLISTSUPPORT
321	hash_del(hashchans, chan, KEYCHAN);
322	#endif
323	nbchans--;
324	}
325	mysql_free_result(resptr2);
326	}
327
328	int db_getlusers(int type)
329	{
330	MYSQL_RES *resptr;
331	int retcode;
332	switch (type)
333	{
334	case LUSERS_USERS:
335	db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_li=\'N\'");
336	break;
337	case LUSERS_USERSINV:
338	db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_li=\'Y\'");
339	break;
340	case LUSERS_OPERS:
341	db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_lo=\'Y\'");
342	break;
343	case LUSERS_CHAN:
344	db_query("SELECT COUNT(*) FROM " TBL_CHAN);
345	break;
346	case LUSERS_SERV:
347	db_query("SELECT COUNT(*) FROM " TBL_SERV);
348	break;
349	case LUSERS_USERSGLOB:
350	db_query("SELECT COUNT(*) FROM " TBL_USER);
351	break;
352	case LUSERS_USERSMAX:
353	db_query("SELECT val FROM " TBL_MAXV " WHERE type='users'");
354	break;
355	}
356	resptr = mysql_store_result(myptr);
357	if (mysql_num_rows(resptr))
358	retcode = atoi(*mysql_fetch_row(resptr));
359	else
360	retcode = -1;
361	mysql_free_result(resptr);
362	return retcode;
363	}
364
365	/* chan should be db_escape'd before call */
366	int db_getchannel(char *chan)
367	{
368	#ifdef HASHLISTSUPPORT
369	strtolwr(chan);
370	return hash_find(hashchans, chan, KEYCHAN);
371	#else
372	int res = 0;
373
374	MYSQL_RES *resptr;
375	strtolwr(chan);
376	db_query("SELECT chanid FROM " TBL_CHAN " WHERE channel=\'%s\'", chan);
377	resptr = mysql_store_result(myptr);
378	if (mysql_num_rows(resptr))
379	res = atoi(*mysql_fetch_row(resptr));
380	else
381	fatal("channel not found !");
382	mysql_free_result(resptr);
383	return res;
384	#endif
385	}
386
387	/* chan should be db_escape'd before call */
388	/* chan is created if not exists */
389	int db_getchancreate(char *chan)
390	{
391	int res = -1;
392
393	#ifdef HASHLISTSUPPORT
394	strtolwr(chan);
395	res = hash_find_unsure(hashchans, chan, KEYCHAN);
396	#else
397	MYSQL_RES *resptr;
398	strtolwr(chan);
399	db_query("SELECT chanid FROM " TBL_CHAN " WHERE channel=\'%s\'", chan);
400	resptr = mysql_store_result(myptr);
401	if (mysql_num_rows(resptr))
402	res = atoi(*mysql_fetch_row(resptr));
403	mysql_free_result(resptr);
404	#endif
405	if (res == -1)
406
407	{
408	db_query("INSERT INTO " TBL_CHAN " (channel) VALUES (\'%s\')", chan);
409	res = db_insertid();
410	#ifdef HASHLISTSUPPORT
411	hash_add(hashchans, chan, res, KEYCHAN);
412	#endif
413	nbchans++;
414	do_checknbchansmax();
415	}
416	return res;
417	}
418
419	/* close connection to DBMS */
420	void db_close()
421	{
422	if (myptr)
423	mysql_close(myptr);
424	myptr = NULL;
425	}
426
427	/* cleanup the server table, removing old entries */
428	void db_cleanserver()
429	{
430	MYSQL_RES *resptr;
431	int curtime = time(NULL);
432	char **res;
433	if (ServerLastClean == -1)
434	ServerLastClean = curtime;
435	if (curtime > (ServerLastClean + ServerCleanFreq))
436	{
437	ServerLastClean = curtime;
438	db_query("SELECT server FROM " TBL_SERV
439	" WHERE online=\"N\" AND lastsplit<FROM_UNIXTIME(\"%d\")",
440	curtime - ServerCacheTime);
441	resptr = mysql_store_result(myptr);
442	if (mysql_num_rows(resptr))
443	{
444	while ((res = mysql_fetch_row(resptr)))
445	{
446	char *server = db_escape(res[0]);
447	db_delserver(server);
448	free(server);
449	}
450	mysql_free_result(resptr);
451	db_query("DELETE FROM " TBL_SERV
452	" WHERE online=\"N\" AND lastsplit<FROM_UNIXTIME(\"%d\")",
453	curtime - ServerCacheTime);
454	}
455	}
456	}
457
458	/* cleanup the user table, removing old entries */
459	void db_cleanuser()
460	{
461	MYSQL_RES *resptr;
462	int curtime = time(NULL);
463	char **res;
464	if (UserLastClean == -1)
465	UserLastClean = curtime;
466	if (curtime > (UserLastClean + UserCleanFreq))
467	{
468	UserLastClean = curtime;
469	db_query("SELECT nick FROM " TBL_USER
470	" WHERE online=\"N\" AND lastquit<FROM_UNIXTIME(\"%d\")",
471	curtime - UserCacheTime);
472
473	resptr = mysql_store_result(myptr);
474	if (mysql_num_rows(resptr))
475	{
476	while ((res = mysql_fetch_row(resptr)))
477	{
478	char *nick = db_escape(res[0]);
479	db_delnick(nick);
480	free(nick);
481	}
482	mysql_free_result(resptr);
483	db_query("DELETE FROM " TBL_USER
484	" WHERE online=\"N\" AND lastquit<FROM_UNIXTIME(\"%d\")",
485	curtime - UserCacheTime);
486	}
487	}
488	}