Fossil SCM

Automatically purge older chat messages using the chat-keep-count and chat-keep-days settings to determine which messages to delete.

drh 2020-12-24 19:15 trunk
Commit 35fbf4995d7064e3e3d422985149b4a525a9e8c46008d2b740071042df6cf09e
Parent 4da6a4cce0b62a1…
1 file changed +33
~ src/chat.c
M src/chat.c
+33
--- src/chat.c
+++ src/chat.c
@@ -217,10 +217,35 @@
217217
 db_multi_exec(zChatSchema1/*works-like:""*/);
218218
 }else if( !db_table_has_column("repository","chat","mdel") ){
219219
 db_multi_exec("ALTER TABLE chat ADD COLUMN mdel INT");
220220
 }
221221
 }
222
+
223
+/*
224
+** Delete old content from the chat table.
225
+*/
226
+static void chat_purge(void){
227
+ int mxCnt = db_get_int("chat-keep-count",50);
228
+ double mxDays = atof(db_get("chat-keep-days","7"));
229
+ double rAge;
230
+ int msgid;
231
+ rAge = db_double(0.0, "SELECT julianday('now')-mtime FROM chat"
232
+ " ORDER BY msgid LIMIT 1");
233
+ if( rAge>mxDays ){
234
+ msgid = db_int(0, "SELECT msgid FROM chat"
235
+ " ORDER BY msgid DESC LIMIT 1 OFFSET %d", mxCnt);
236
+ if( msgid>0 ){
237
+ Stmt s;
238
+ db_prepare(&s,
239
+ "DELETE FROM chat WHERE mtime<julianday('now')-:mxage"
240
+ " AND msgid<%d", msgid);
241
+ db_bind_double(&s, ":mxage", mxDays);
242
+ db_step(&s);
243
+ db_finalize(&s);
244
+ }
245
+ }
246
+}
222247
223248
 /*
224249
 ** WEBPAGE: chat-send
225250
 **
226251
 ** This page receives (via XHR) a new chat-message and/or a new file
@@ -232,10 +257,12 @@
232257
 login_check_credentials();
233258
 if( !g.perm.Chat ) return;
234259
 chat_create_tables();
235260
 nByte = atoi(PD("file:bytes",0));
236261
 zMsg = PD("msg","");
262
+ db_begin_write();
263
+ chat_purge();
237264
 if( nByte==0 ){
238265
 if( zMsg[0] ){
239266
 db_multi_exec(
240267
 "INSERT INTO chat(mtime,xfrom,xmsg)"
241268
 "VALUES(julianday('now'),%Q,%Q)",
@@ -254,10 +281,11 @@
254281
 db_bind_blob(&q, ":file", &b);
255282
 db_step(&q);
256283
 db_finalize(&q);
257284
 blob_reset(&b);
258285
 }
286
+ db_commit_transaction();
259287
 }
260288
261289
 /*
262290
 ** This routine receives raw (user-entered) message text and transforms
263291
 ** it into HTML that is safe to insert using innerHTML.
@@ -408,10 +436,15 @@
408436
 login_check_credentials();
409437
 if( !g.perm.Chat ) return;
410438
 chat_create_tables();
411439
 cgi_set_content_type("text/json");
412440
 dataVersion = db_int64(0, "PRAGMA data_version");
441
+ if( msgid<=0 ){
442
+ db_begin_write();
443
+ chat_purge();
444
+ db_commit_transaction();
445
+ }
413446
 if( msgid<0 ){
414447
 msgid = db_int(0,
415448
 "SELECT msgid FROM chat WHERE mdel IS NOT true"
416449
 " ORDER BY msgid DESC LIMIT 1 OFFSET %d", -msgid);
417450
 }
418451
--- src/chat.c
+++ src/chat.c
@@ -217,10 +217,35 @@
217 db_multi_exec(zChatSchema1/*works-like:""*/);
218 }else if( !db_table_has_column("repository","chat","mdel") ){
219 db_multi_exec("ALTER TABLE chat ADD COLUMN mdel INT");
220 }
221 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
222
223 /*
224 ** WEBPAGE: chat-send
225 **
226 ** This page receives (via XHR) a new chat-message and/or a new file
@@ -232,10 +257,12 @@
232 login_check_credentials();
233 if( !g.perm.Chat ) return;
234 chat_create_tables();
235 nByte = atoi(PD("file:bytes",0));
236 zMsg = PD("msg","");
 
 
237 if( nByte==0 ){
238 if( zMsg[0] ){
239 db_multi_exec(
240 "INSERT INTO chat(mtime,xfrom,xmsg)"
241 "VALUES(julianday('now'),%Q,%Q)",
@@ -254,10 +281,11 @@
254 db_bind_blob(&q, ":file", &b);
255 db_step(&q);
256 db_finalize(&q);
257 blob_reset(&b);
258 }
 
259 }
260
261 /*
262 ** This routine receives raw (user-entered) message text and transforms
263 ** it into HTML that is safe to insert using innerHTML.
@@ -408,10 +436,15 @@
408 login_check_credentials();
409 if( !g.perm.Chat ) return;
410 chat_create_tables();
411 cgi_set_content_type("text/json");
412 dataVersion = db_int64(0, "PRAGMA data_version");
 
 
 
 
 
413 if( msgid<0 ){
414 msgid = db_int(0,
415 "SELECT msgid FROM chat WHERE mdel IS NOT true"
416 " ORDER BY msgid DESC LIMIT 1 OFFSET %d", -msgid);
417 }
418
--- src/chat.c
+++ src/chat.c
@@ -217,10 +217,35 @@
217 db_multi_exec(zChatSchema1/*works-like:""*/);
218 }else if( !db_table_has_column("repository","chat","mdel") ){
219 db_multi_exec("ALTER TABLE chat ADD COLUMN mdel INT");
220 }
221 }
222
223 /*
224 ** Delete old content from the chat table.
225 */
226 static void chat_purge(void){
227 int mxCnt = db_get_int("chat-keep-count",50);
228 double mxDays = atof(db_get("chat-keep-days","7"));
229 double rAge;
230 int msgid;
231 rAge = db_double(0.0, "SELECT julianday('now')-mtime FROM chat"
232 " ORDER BY msgid LIMIT 1");
233 if( rAge>mxDays ){
234 msgid = db_int(0, "SELECT msgid FROM chat"
235 " ORDER BY msgid DESC LIMIT 1 OFFSET %d", mxCnt);
236 if( msgid>0 ){
237 Stmt s;
238 db_prepare(&s,
239 "DELETE FROM chat WHERE mtime<julianday('now')-:mxage"
240 " AND msgid<%d", msgid);
241 db_bind_double(&s, ":mxage", mxDays);
242 db_step(&s);
243 db_finalize(&s);
244 }
245 }
246 }
247
248 /*
249 ** WEBPAGE: chat-send
250 **
251 ** This page receives (via XHR) a new chat-message and/or a new file
@@ -232,10 +257,12 @@
257 login_check_credentials();
258 if( !g.perm.Chat ) return;
259 chat_create_tables();
260 nByte = atoi(PD("file:bytes",0));
261 zMsg = PD("msg","");
262 db_begin_write();
263 chat_purge();
264 if( nByte==0 ){
265 if( zMsg[0] ){
266 db_multi_exec(
267 "INSERT INTO chat(mtime,xfrom,xmsg)"
268 "VALUES(julianday('now'),%Q,%Q)",
@@ -254,10 +281,11 @@
281 db_bind_blob(&q, ":file", &b);
282 db_step(&q);
283 db_finalize(&q);
284 blob_reset(&b);
285 }
286 db_commit_transaction();
287 }
288
289 /*
290 ** This routine receives raw (user-entered) message text and transforms
291 ** it into HTML that is safe to insert using innerHTML.
@@ -408,10 +436,15 @@
436 login_check_credentials();
437 if( !g.perm.Chat ) return;
438 chat_create_tables();
439 cgi_set_content_type("text/json");
440 dataVersion = db_int64(0, "PRAGMA data_version");
441 if( msgid<=0 ){
442 db_begin_write();
443 chat_purge();
444 db_commit_transaction();
445 }
446 if( msgid<0 ){
447 msgid = db_int(0,
448 "SELECT msgid FROM chat WHERE mdel IS NOT true"
449 " ORDER BY msgid DESC LIMIT 1 OFFSET %d", -msgid);
450 }
451

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button