]>
jfr.im git - irc/quakenet/newserv.git/blob - chanserv/database/chanservdb.c
3 * Handles the SQL stuff for the channel service.
6 #include "../chanserv.h"
7 #include "../../pqsql/pqsql.h"
8 #include "../../core/config.h"
9 #include "../../lib/sstring.h"
10 #include "../../parser/parser.h"
11 #include "../../core/events.h"
21 typedef struct tabledesc
{
22 sstring
*tablename
; /* Name of table */
23 PQQueryHandler init
; /* Function to be called when transaction opens */
24 PQQueryHandler data
; /* Function to be called to load data */
25 PQQueryHandler fini
; /* Function to be called to clean up */
33 unsigned int lastchannelID
;
34 unsigned int lastuserID
;
35 unsigned int lastbanID
;
37 /* Local prototypes */
38 void csdb_handlestats(int hooknum
, void *arg
);
39 void loadmessages_part1(PGconn
*, void *);
40 void loadmessages_part2(PGconn
*, void *);
41 void loadcommandsummary_real(PGconn
*, void *);
43 /* Generic loading functions */
44 void loadall(char *, PQQueryHandler
, PQQueryHandler
, PQQueryHandler
);
45 void doloadall(PGconn
*, void *);
47 /* User loading functions */
48 void loadsomeusers(PGconn
*, void *);
49 void loadusersdone(PGconn
*, void *);
51 /* Channel loading functions */
52 void loadsomechannels(PGconn
*, void *);
53 void loadchannelsdone(PGconn
*, void *);
55 /* Chanuser loading functions */
56 void loadchanusersinit(PGconn
*, void *);
57 void loadsomechanusers(PGconn
*, void *);
58 void loadchanusersdone(PGconn
*, void *);
60 /* Chanban loading functions */
61 void loadsomechanbans(PGconn
*, void *);
62 void loadchanbansdone(PGconn
*, void *);
64 /* Free sstrings in the structures */
65 void csdb_freestuff();
67 static void setuptables() {
68 /* Set up the tables */
70 pqcreatequery("CREATE TABLE users ("
72 "username VARCHAR(16) NOT NULL,"
73 "created INT NOT NULL,"
74 "lastauth INT NOT NULL,"
75 "lastemailchng INT NOT NULL,"
77 "language INT NOT NULL,"
78 "suspendby INT NOT NULL,"
79 "suspendexp INT NOT NULL,"
80 "password VARCHAR(11) NOT NULL,"
81 "masterpass VARCHAR(11),"
83 "lastuserhost VARCHAR(75),"
84 "suspendreason VARCHAR(250),"
85 "comment VARCHAR(250),"
89 pqcreatequery("CREATE INDEX user_username_index ON users (username)");
92 pqcreatequery("CREATE TABLE channels ("
94 "name VARCHAR(250) NOT NULL,"
96 "forcemodes INT NOT NULL,"
97 "denymodes INT NOT NULL,"
98 "chanlimit INT NOT NULL,"
99 "autolimit INT NOT NULL,"
100 "banstyle INT NOT NULL,"
101 "created INT NOT NULL,"
102 "lastactive INT NOT NULL,"
103 "statsreset INT NOT NULL,"
104 "banduration INT NOT NULL,"
105 "founder INT NOT NULL,"
106 "addedby INT NOT NULL,"
107 "suspendby INT NOT NULL,"
108 "chantype SMALLINT NOT NULL,"
109 "totaljoins INT NOT NULL,"
110 "tripjoins INT NOT NULL,"
111 "maxusers INT NOT NULL,"
112 "tripusers INT NOT NULL,"
113 "welcome VARCHAR(500),"
114 "topic VARCHAR(250),"
115 "chankey VARCHAR(23),"
116 "suspendreason VARCHAR(250),"
117 "comment VARCHAR(250),"
119 "PRIMARY KEY (ID))");
122 pqcreatequery("CREATE TABLE chanusers ("
123 "userID INT NOT NULL,"
124 "channelID INT NOT NULL,"
125 "flags INT NOT NULL,"
126 "changetime INT NOT NULL,"
127 "usetime INT NOT NULL,"
128 "info VARCHAR(100) NOT NULL,"
129 "PRIMARY KEY (userID, channelID))");
131 pqcreatequery("CREATE INDEX chanusers_userID_index on chanusers (userID)");
132 pqcreatequery("CREATE INDEX chanusers_channelID_index on chanusers (channelID)");
134 pqcreatequery("CREATE TABLE bans ("
135 "banID INT NOT NULL," /* Unique number for the ban to make
136 DELETEs process in finite time.. */
137 "channelID INT NOT NULL,"
138 "userID INT NOT NULL," /* Who set the ban.. */
139 "hostmask VARCHAR(100) NOT NULL," /* needs to be at least USERLEN+NICKLEN+HOSTLEN+2 */
140 "expiry INT NOT NULL,"
141 "reason VARCHAR(200),"
142 "PRIMARY KEY(banID))");
144 pqcreatequery("CREATE INDEX bans_channelID_index on bans (channelID)");
146 pqcreatequery("CREATE TABLE languages ("
147 "languageID INT NOT NULL,"
148 "code VARCHAR(2) NOT NULL,"
149 "name VARCHAR(30) NOT NULL)");
151 pqcreatequery("CREATE TABLE messages ("
152 "languageID INT NOT NULL,"
153 "messageID INT NOT NULL,"
154 "message VARCHAR(250) NOT NULL,"
155 "PRIMARY KEY (languageID, messageID))");
157 pqcreatequery("CREATE TABLE help ("
158 "commandID INT NOT NULL,"
159 "command VARCHAR(30) NOT NULL,"
160 "languageID INT NOT NULL,"
161 "summary VARCHAR(200) NOT NULL,"
162 "fullinfo TEXT NOT NULL,"
163 "PRIMARY KEY (commandID, languageID))");
165 pqcreatequery("CREATE TABLE email ("
166 "userID INT NOT NULL,"
167 "emailtype INT NOT NULL,"
168 "prevEmail VARCHAR(100),"
169 "mailID VARCHAR(128))");
173 chanservext
=registerchanext("chanserv");
174 chanservaext
=registerauthnameext("chanserv");
176 /* Set up the allocators and hashes */
180 /* And the messages */
183 if (pqconnected() && (chanservext
!=-1) && (chanservaext
!=-1)) {
184 registerhook(HOOK_CORE_STATSREQUEST
, csdb_handlestats
);
188 lastuserID
=lastchannelID
=0;
190 loadall("users",NULL
,loadsomeusers
,loadusersdone
);
191 loadall("channels",NULL
,loadsomechannels
,loadchannelsdone
);
192 loadall("chanusers",loadchanusersinit
,loadsomechanusers
,loadchanusersdone
);
193 loadall("bans",NULL
,loadsomechanbans
,loadchanbansdone
);
201 releasechanext(chanservext
);
203 if (chanservaext
!=-1)
204 releaseauthnameext(chanservaext
);
210 void csdb_handlestats(int hooknum
, void *arg
) {
211 /* long level=(long)arg; */
213 /* Keeping options open here */
216 void chanservdbclose() {
218 deregisterhook(HOOK_CORE_STATSREQUEST
, csdb_handlestats
);
224 * Generic function to handle load of an entire table..
227 void loadall(char *table
, PQQueryHandler init
, PQQueryHandler data
, PQQueryHandler fini
) {
230 thedesc
=malloc(sizeof(tabledesc
));
232 thedesc
->tablename
=getsstring(table
,100);
237 pqasyncquery(doloadall
, thedesc
, "SELECT count(*) FROM %s",thedesc
->tablename
->content
);
240 void doloadall(PGconn
*dbconn
, void *arg
) {
243 tabledesc
*thedesc
=arg
;
245 pgres
=PQgetResult(dbconn
);
247 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
248 Error("chanserv",ERR_ERROR
,"Error getting row count for %s.",thedesc
->tablename
->content
);
252 if (PQnfields(pgres
)!=1) {
253 Error("chanserv",ERR_ERROR
,"Count query format error for %s.",thedesc
->tablename
->content
);
257 count
=strtoul(PQgetvalue(pgres
,0,0),NULL
,10);
261 Error("chanserv",ERR_INFO
,"Found %d entries in table %s, scheduling load.",count
,
262 thedesc
->tablename
->content
);
264 pqasyncquery(thedesc
->init
, NULL
, "BEGIN");
265 pqquery("DECLARE mycurs CURSOR FOR SELECT * from %s",
266 thedesc
->tablename
->content
);
268 for (i
=0;(count
-i
)>1000;i
+=1000) {
269 pqasyncquery(thedesc
->data
, NULL
, "FETCH 1000 FROM mycurs");
272 pqasyncquery(thedesc
->data
, NULL
, "FETCH ALL FROM mycurs");
274 pqquery("CLOSE mycurs");
275 pqasyncquery(thedesc
->fini
, NULL
, "COMMIT");
277 /* Free structures.. */
278 freesstring(thedesc
->tablename
);
284 * Loads some users in from the SQL DB
287 void loadsomeusers(PGconn
*dbconn
, void *arg
) {
292 pgres
=PQgetResult(dbconn
);
294 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
295 Error("chanserv",ERR_ERROR
,"Error loading user DB");
299 if (PQnfields(pgres
)!=16) {
300 Error("chanserv",ERR_ERROR
,"User DB format error");
304 num
=PQntuples(pgres
);
309 rup
->ID
=strtoul(PQgetvalue(pgres
,i
,0),NULL
,10);
310 strncpy(rup
->username
,PQgetvalue(pgres
,i
,1),NICKLEN
); rup
->username
[NICKLEN
]='\0';
311 rup
->created
=strtoul(PQgetvalue(pgres
,i
,2),NULL
,10);
312 rup
->lastauth
=strtoul(PQgetvalue(pgres
,i
,3),NULL
,10);
313 rup
->lastemailchange
=strtoul(PQgetvalue(pgres
,i
,4),NULL
,10);
314 rup
->flags
=strtoul(PQgetvalue(pgres
,i
,5),NULL
,10);
315 rup
->languageid
=strtoul(PQgetvalue(pgres
,i
,6),NULL
,10);
316 rup
->suspendby
=strtoul(PQgetvalue(pgres
,i
,7),NULL
,10);
317 rup
->suspendexp
=strtoul(PQgetvalue(pgres
,i
,8),NULL
,10);
318 strncpy(rup
->password
,PQgetvalue(pgres
,i
,9),PASSLEN
); rup
->password
[PASSLEN
]='\0';
319 strncpy(rup
->masterpass
,PQgetvalue(pgres
,i
,10),PASSLEN
); rup
->masterpass
[PASSLEN
]='\0';
320 rup
->email
=getsstring(PQgetvalue(pgres
,i
,11),100);
321 rup
->lastuserhost
=getsstring(PQgetvalue(pgres
,i
,12),75);
322 rup
->suspendreason
=getsstring(PQgetvalue(pgres
,i
,13),250);
323 rup
->comment
=getsstring(PQgetvalue(pgres
,i
,14),250);
324 rup
->info
=getsstring(PQgetvalue(pgres
,i
,15),100);
330 addregusertohash(rup
);
332 if (rup
->ID
> lastuserID
) {
340 void loadusersdone(PGconn
*conn
, void *arg
) {
341 Error("chanserv",ERR_INFO
,"Load users done (highest ID was %d)",lastuserID
);
345 * Channel loading functions
348 void loadsomechannels(PGconn
*dbconn
, void *arg
) {
353 time_t now
=time(NULL
);
355 pgres
=PQgetResult(dbconn
);
357 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
358 Error("chanserv",ERR_ERROR
,"Error loading channel DB");
362 if (PQnfields(pgres
)!=26) {
363 Error("chanserv",ERR_ERROR
,"Channel DB format error");
367 num
=PQntuples(pgres
);
370 cip
=findorcreatechanindex(PQgetvalue(pgres
,i
,1));
371 if (cip
->exts
[chanservext
]) {
372 Error("chanserv",ERR_WARNING
,"%s in database twice - this WILL cause problems later.",cip
->name
->content
);
376 cip
->exts
[chanservext
]=rcp
;
378 rcp
->ID
=strtoul(PQgetvalue(pgres
,i
,0),NULL
,10);
380 rcp
->flags
=strtoul(PQgetvalue(pgres
,i
,2),NULL
,10);
381 rcp
->status
=0; /* Non-DB field */
383 rcp
->lastcountersync
=now
;
386 rcp
->forcemodes
=strtoul(PQgetvalue(pgres
,i
,3),NULL
,10);
387 rcp
->denymodes
=strtoul(PQgetvalue(pgres
,i
,4),NULL
,10);
388 rcp
->limit
=strtoul(PQgetvalue(pgres
,i
,5),NULL
,10);
389 rcp
->autolimit
=strtoul(PQgetvalue(pgres
,i
,6),NULL
,10);
390 rcp
->banstyle
=strtoul(PQgetvalue(pgres
,i
,7),NULL
,10);
391 rcp
->created
=strtoul(PQgetvalue(pgres
,i
,8),NULL
,10);
392 rcp
->lastactive
=strtoul(PQgetvalue(pgres
,i
,9),NULL
,10);
393 rcp
->statsreset
=strtoul(PQgetvalue(pgres
,i
,10),NULL
,10);
394 rcp
->banduration
=strtoul(PQgetvalue(pgres
,i
,11),NULL
,10);
395 rcp
->founder
=strtol(PQgetvalue(pgres
,i
,12),NULL
,10);
396 rcp
->addedby
=strtol(PQgetvalue(pgres
,i
,13),NULL
,10);
397 rcp
->suspendby
=strtol(PQgetvalue(pgres
,i
,14),NULL
,10);
398 rcp
->chantype
=strtoul(PQgetvalue(pgres
,i
,15),NULL
,10);
399 rcp
->totaljoins
=strtoul(PQgetvalue(pgres
,i
,16),NULL
,10);
400 rcp
->tripjoins
=strtoul(PQgetvalue(pgres
,i
,17),NULL
,10);
401 rcp
->maxusers
=strtoul(PQgetvalue(pgres
,i
,18),NULL
,10);
402 rcp
->tripusers
=strtoul(PQgetvalue(pgres
,i
,19),NULL
,10);
403 rcp
->welcome
=getsstring(PQgetvalue(pgres
,i
,20),500);
404 rcp
->topic
=getsstring(PQgetvalue(pgres
,i
,21),TOPICLEN
);
405 rcp
->key
=getsstring(PQgetvalue(pgres
,i
,22),KEYLEN
);
406 rcp
->suspendreason
=getsstring(PQgetvalue(pgres
,i
,23),250);
407 rcp
->comment
=getsstring(PQgetvalue(pgres
,i
,24),250);
408 rcp
->checksched
=NULL
;
409 rcp
->ltimestamp
=strtoul(PQgetvalue(pgres
,i
,25),NULL
,10);
410 memset(rcp
->regusers
,0,REGCHANUSERHASHSIZE
*sizeof(reguser
*));
412 if (rcp
->ID
> lastchannelID
)
413 lastchannelID
=rcp
->ID
;
415 if (CIsAutoLimit(rcp
))
422 void loadchannelsdone(PGconn
*dbconn
, void *arg
) {
423 Error("chanserv",ERR_INFO
,"Channel load done (highest ID was %d)",lastchannelID
);
426 void loadchanusersinit(PGconn
*dbconn
, void *arg
) {
431 allchans
=(regchan
**)malloc((lastchannelID
+1)*sizeof(regchan
*));
432 memset(allchans
,0,(lastchannelID
+1)*sizeof(regchan
*));
433 for (i
=0;i
<CHANNELHASHSIZE
;i
++) {
434 for (cip
=chantable
[i
];cip
;cip
=cip
->next
) {
435 if (cip
->exts
[chanservext
]) {
436 rcp
=(regchan
*)cip
->exts
[chanservext
];
437 allchans
[rcp
->ID
]=rcp
;
443 void loadsomechanusers(PGconn
*dbconn
, void *arg
) {
453 /* Set up the allchans array */
454 pgres
=PQgetResult(dbconn
);
456 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
457 Error("chanserv",ERR_ERROR
,"Error loading chanusers.");
461 if (PQnfields(pgres
)!=6) {
462 Error("chanserv",ERR_ERROR
,"Chanusers format error");
466 num
=PQntuples(pgres
);
469 uid
=strtol(PQgetvalue(pgres
,i
,0),NULL
,10);
470 cid
=strtol(PQgetvalue(pgres
,i
,1),NULL
,10);
472 if (!(anp
=findauthname(uid
)) || !(rup
=anp
->exts
[chanservaext
])) {
473 Error("chanserv",ERR_WARNING
,"Skipping channeluser for unknown user %d",uid
);
477 if (cid
>lastchannelID
|| !(rcp
=allchans
[cid
])) {
478 Error("chanserv",ERR_WARNING
,"Skipping channeluser for unknown chan %d",cid
);
482 if (rup
==NULL
|| rcp
==NULL
) {
483 Error("chanserv",ERR_ERROR
,"Can't add user %s on channel %s",
484 PQgetvalue(pgres
,i
,0),PQgetvalue(pgres
,i
,1));
486 rcup
=getregchanuser();
489 rcup
->flags
=strtol(PQgetvalue(pgres
,i
,2),NULL
,10);
490 rcup
->changetime
=strtol(PQgetvalue(pgres
,i
,3),NULL
,10);
491 rcup
->usetime
=strtol(PQgetvalue(pgres
,i
,4),NULL
,10);
492 rcup
->info
=getsstring(PQgetvalue(pgres
,i
,5),100);
493 addregusertochannel(rcup
);
501 void loadchanusersdone(PGconn
*dbconn
, void *arg
) {
502 Error("chanserv",ERR_INFO
,"Channel user load done.");
505 void loadsomechanbans(PGconn
*dbconn
, void *arg
) {
514 pgres
=PQgetResult(dbconn
);
516 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
517 Error("chanserv",ERR_ERROR
,"Error loading bans.");
521 if (PQnfields(pgres
)!=6) {
522 Error("chanserv",ERR_ERROR
,"Ban format error");
526 num
=PQntuples(pgres
);
531 bid
=strtoul(PQgetvalue(pgres
,i
,0),NULL
,10);
532 cid
=strtoul(PQgetvalue(pgres
,i
,1),NULL
,10);
533 uid
=strtoul(PQgetvalue(pgres
,i
,2),NULL
,10);
534 expiry
=strtoul(PQgetvalue(pgres
,i
,4),NULL
,10);
536 if (cid
>lastchannelID
|| !(rcp
=allchans
[cid
])) {
537 Error("chanserv",ERR_WARNING
,"Skipping ban for unknown chan %d",cid
);
545 rbp
->reason
=getsstring(PQgetvalue(pgres
,i
,5),200);
546 rbp
->cbp
=makeban(PQgetvalue(pgres
,i
,3));
559 void loadchanbansdone(PGconn
*dbconn
, void *arg
) {
562 Error("chanserv",ERR_INFO
,"Channel ban load done, highest ID was %d",lastbanID
);
565 triggerhook(HOOK_CHANSERV_DBLOADED
, NULL
);
568 void loadmessages() {
569 pqasyncquery(loadmessages_part1
, NULL
, "SELECT * from languages");
572 void loadmessages_part1(PGconn
*dbconn
, void *arg
) {
577 /* Firstly, clear up any stale messages */
578 for (i
=0;i
<MAXLANG
;i
++) {
579 if (cslanguages
[i
]) {
580 freesstring(cslanguages
[i
]->name
);
581 free(cslanguages
[i
]);
583 for (j
=0;j
<MAXMESSAGES
;j
++) {
584 if (csmessages
[i
][j
]) {
585 freesstring(csmessages
[i
][j
]);
586 csmessages
[i
][j
]=NULL
;
591 pgres
=PQgetResult(dbconn
);
593 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
594 Error("chanserv",ERR_ERROR
,"Error loading language list.");
598 if (PQnfields(pgres
)!=3) {
599 Error("chanserv",ERR_ERROR
,"Language list format error.");
603 num
=PQntuples(pgres
);
605 for (i
=0;i
<num
;i
++) {
606 j
=strtol(PQgetvalue(pgres
,i
,0),NULL
,10);
607 if (j
<MAXLANG
&& j
>=0) {
608 cslanguages
[j
]=(cslang
*)malloc(sizeof(cslang
));
610 strncpy(cslanguages
[j
]->code
,PQgetvalue(pgres
,i
,1),2); cslanguages
[j
]->code
[2]='\0';
611 cslanguages
[j
]->name
=getsstring(PQgetvalue(pgres
,i
,2),30);
618 Error("chanserv",ERR_ERROR
,"Found too many languages (%d > %d)",i
,MAXLANG
);
620 pqasyncquery(loadmessages_part2
, NULL
, "SELECT * from messages");
623 void loadmessages_part2(PGconn
*dbconn
, void *arg
) {
627 pgres
=PQgetResult(dbconn
);
629 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
630 Error("chanserv",ERR_ERROR
,"Error loading message list.");
634 if (PQnfields(pgres
)!=3) {
635 Error("chanserv",ERR_ERROR
,"Message list format error.");
639 num
=PQntuples(pgres
);
641 for (i
=0;i
<num
;i
++) {
642 k
=strtol(PQgetvalue(pgres
,i
,0),NULL
,10);
643 j
=strtol(PQgetvalue(pgres
,i
,1),NULL
,10);
645 if (k
<0 || k
>= MAXLANG
) {
646 Error("chanserv",ERR_WARNING
,"Language ID out of range on message: %d",k
);
650 if (j
<0 || j
>= MAXMESSAGES
) {
651 Error("chanserv",ERR_WARNING
,"Message ID out of range on message: %d",j
);
655 csmessages
[k
][j
]=getsstring(PQgetvalue(pgres
,i
,2),250);
661 void loadcommandsummary(Command
*cmd
) {
662 pqasyncquery(loadcommandsummary_real
, (void *)cmd
,
663 "SELECT languageID,summary from help where lower(command) = lower('%s')",cmd
->command
->content
);
666 void loadcommandsummary_real(PGconn
*dbconn
, void *arg
) {
672 /* Clear up old text first */
674 for (i
=0;i
<MAXLANG
;i
++) {
676 freesstring(cs
->bylang
[i
]);
679 pgres
=PQgetResult(dbconn
);
681 if (PQresultStatus(pgres
) != PGRES_TUPLES_OK
) {
682 Error("chanserv",ERR_ERROR
,"Error loading command summary.");
686 if (PQnfields(pgres
)!=2) {
687 Error("chanserv",ERR_ERROR
,"Command summary format error.");
691 num
=PQntuples(pgres
);
693 for (i
=0;i
<num
;i
++) {
694 j
=strtol(PQgetvalue(pgres
,i
,0),NULL
,10);
695 if (j
<MAXLANG
&& j
>=0) {
696 cs
->bylang
[j
]=getsstring(PQgetvalue(pgres
,i
,1),200);
703 void csdb_freestuff() {
705 chanindex
*cip
, *ncip
;
711 for (i
=0;i
<REGUSERHASHSIZE
;i
++) {
712 for (rup
=regusernicktable
[i
];rup
;rup
=rup
->nextbyname
) {
713 freesstring(rup
->email
);
714 freesstring(rup
->lastuserhost
);
715 freesstring(rup
->suspendreason
);
716 freesstring(rup
->comment
);
717 freesstring(rup
->info
);
719 for (rcup
=rup
->knownon
;rcup
;rcup
=rcup
->nextbyuser
)
720 freesstring(rcup
->info
);
724 for (i
=0;i
<CHANNELHASHSIZE
;i
++) {
725 for (cip
=chantable
[i
];cip
;cip
=ncip
) {
727 if ((rcp
=cip
->exts
[chanservext
])) {
728 freesstring(rcp
->welcome
);
729 freesstring(rcp
->topic
);
730 freesstring(rcp
->key
);
731 freesstring(rcp
->suspendreason
);
732 freesstring(rcp
->comment
);
733 for (rbp
=rcp
->bans
;rbp
;rbp
=rbp
->next
) {
734 freesstring(rbp
->reason
);
735 freechanban(rbp
->cbp
);
737 cip
->exts
[chanservext
]=NULL
;
738 releasechanindex(cip
);