]>
Commit | Line | Data |
---|---|---|
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 | } |