Fossil SCM

A new way of computing alert text.

drh 2018-06-22 17:36 UTC email-alerts
Commit 6c06b1c896e9c97f9e3bc1cae57a796d8e8e0ea1daff842089c4b72078d38e7c
Parent e02892522ecf7f3…
1 file changed +78 -46
~ src/email.c
M src/email.c
+78 -46
--- src/email.c
+++ src/email.c
@@ -70,11 +70,11 @@
7070
 @ -- 'c4413' means check-in with rid=4413.
7171
 @ --
7272
 @ CREATE TABLE repository.pending_alert(
7373
 @ eventid TEXT PRIMARY KEY, -- Object that changed
7474
 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
75
-@ mtime DATETIME -- when added to queue
75
+@ sendDigest BOOLEAN DEFAULT false, -- digest emails sent
7676
 @ ) WITHOUT ROWID;
7777
 @
7878
 @ -- Record bounced emails. If too many bounces are received within
7979
 @ -- some defined time range, then cancel the subscription. Older
8080
 @ -- entries are periodically purged.
@@ -1238,23 +1238,48 @@
12381238
 db_finalize(&q);
12391239
 style_footer();
12401240
 }
12411241
12421242
 #if LOCAL_INTERFACE
1243
-/* Allowed values for the mAlert flags parameter to email_alert_text
1243
+/*
1244
+** A single event that might appear in an alert is recorded as an
1245
+** instance of the following object.
12441246
 */
1245
-#define ALERT_HTML 0x01 /* Generate HTML instead of plain text */
1247
+struct EmailEvent {
1248
+ int type; /* 'c', 't', 'w', etc. */
1249
+ Blob txt; /* Text description to appear in an alert */
1250
+ EmailEvent *pNext; /* Next in chronological order */
1251
+};
12461252
 #endif
12471253
12481254
 /*
1249
-** Append the text for a single alert to the end of pOut
1255
+** Free a linked list of EmailEvent objects
1256
+*/
1257
+void email_free_eventlist(EmailEvent *p){
1258
+ while( p ){
1259
+ EmailEvent *pNext = p->pNext;
1260
+ blob_zero(&p->txt);
1261
+ fossil_free(p);
1262
+ p = pNext;
1263
+ }
1264
+}
1265
+
1266
+/*
1267
+** Compute and return a linked list of EmailEvent objects
1268
+** corresponding to the current content of the temp.wantalert
1269
+** table which should be defined as follows:
1270
+**
1271
+** CREATE TEMP TABLE wantalert(eventId TEXT);
12501272
 */
1251
-void email_one_alert(const char *zEvent, u32 mAlert, Blob *pOut){
1252
- static Stmt q;
1253
- int id;
1254
- const char *zType = "";
1255
- db_static_prepare(&q,
1273
+EmailEvent *email_compute_event_text(int *pnEvent){
1274
+ Stmt q;
1275
+ EmailEvent *p;
1276
+ EmailEvent anchor;
1277
+ EmailEvent *pLast;
1278
+ const char *zUrl = db_get("email-url","http://localhost:8080");
1279
+
1280
+ db_prepare(&q,
12561281
 "SELECT"
12571282
 " blob.uuid," /* 0 */
12581283
 " datetime(event.mtime)," /* 1 */
12591284
 " coalesce(ecomment,comment)"
12601285
 " || ' (user: ' || coalesce(euser,user,'?')"
@@ -1262,44 +1287,54 @@
12621287
 " FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
12631288
 " FROM tag, tagxref"
12641289
 " WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
12651290
 " AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
12661291
 " || ')' as comment," /* 2 */
1267
- " tagxref.value AS branch" /* 3 */
1268
- " FROM tag CROSS JOIN event CROSS JOIN blob"
1292
+ " tagxref.value AS branch," /* 3 */
1293
+ " wantalert.eventId" /* 4 */
1294
+ " FROM temp.wantalert JOIN tag CROSS JOIN event CROSS JOIN blob"
12691295
 " LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
12701296
 " AND tagxref.tagtype>0"
12711297
 " AND tagxref.rid=blob.rid"
12721298
 " WHERE blob.rid=event.objid"
12731299
 " AND tag.tagname='branch'"
1274
- " AND event.objid=:objid"
1300
+ " AND event.objid=substr(wantalert.eventId,2)+0"
1301
+ " ORDER BY event.mtime"
12751302
 );
1276
- switch( zEvent[0] ){
1277
- case 'c': zType = "Check-In"; break;
1278
- case 't': zType = "Wiki Edit"; break;
1279
- case 'w': zType = "Ticket Change"; break;
1280
- default: return;
1281
- }
1282
- id = atoi(zEvent+1);
1283
- if( id<=0 ) return;
1284
- db_bind_int(&q, ":objid", id);
1285
- if( db_step(&q)==SQLITE_ROW ){
1286
- blob_appendf(pOut,"\n== %s %s ==\n%s\n%s/info/%.20s\n",
1303
+ memset(&anchor, 0, sizeof(anchor));
1304
+ pLast = &anchor;
1305
+ *pnEvent = 0;
1306
+ while( db_step(&q)==SQLITE_ROW ){
1307
+ const char *zType = "";
1308
+ p = fossil_malloc( sizeof(EmailEvent) );
1309
+ pLast->pNext = p;
1310
+ pLast = p;
1311
+ p->type = db_column_text(&q, 4)[0];
1312
+ p->pNext = 0;
1313
+ switch( p->type ){
1314
+ case 'c': zType = "Check-In"; break;
1315
+ case 't': zType = "Wiki Edit"; break;
1316
+ case 'w': zType = "Ticket Change"; break;
1317
+ }
1318
+ blob_init(&p->txt, 0, 0);
1319
+ blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
12871320
 db_column_text(&q,1),
12881321
 zType,
12891322
 db_column_text(&q,2),
1290
- db_get("email-url","http://localhost:8080"),
1323
+ zUrl,
12911324
 db_column_text(&q,0)
12921325
 );
1326
+ *pnEvent++;
12931327
 }
1294
- db_reset(&q);
1328
+ db_finalize(&q);
1329
+ return anchor.pNext;
12951330
 }
12961331
12971332
 /*
12981333
 ** Put a header on an alert email
12991334
 */
1300
-void email_header(u32 mAlert, Blob *pOut){
1335
+void email_header(Blob *pOut){
13011336
 blob_appendf(pOut,
13021337
 "This is an automated email reporting changes "
13031338
 "on Fossil repository %s (%s/timeline)\n",
13041339
 db_get("email-subname","(unknown)"),
13051340
 db_get("email-url","http://localhost:8080"));
@@ -1307,11 +1342,11 @@
13071342
13081343
 /*
13091344
 ** Append the "unsubscribe" notification and other footer text to
13101345
 ** the end of an email alert being assemblied in pOut.
13111346
 */
1312
-void email_footer(u32 mAlert, Blob *pOut){
1347
+void email_footer(Blob *pOut){
13131348
 blob_appendf(pOut, "\n%.72c\nTo unsubscribe: %s/unsubscribe\n",
13141349
 '-', db_get("email-url","http://localhost:8080"));
13151350
 }
13161351
13171352
 /*
@@ -1324,42 +1359,39 @@
13241359
 ** output. If the --actual flag is present, then the EVENTIDs are
13251360
 ** the actual event-ids in the pending_alert table.
13261361
 **
13271362
 ** This command is intended for testing and debugging the logic
13281363
 ** that generates email alert text.
1329
-**
1330
-** The mimetype is text/plain by default. Use the --html option
1331
-** to generate text/html alert text.
13321364
 */
13331365
 void test_generate_alert_cmd(void){
1334
- u32 mAlert = 0;
13351366
 int bActual = find_option("actual",0,0)!=0;
13361367
 Blob out;
13371368
 int i;
1369
+ int nEvent;
1370
+ EmailEvent *pEvent, *p;
13381371
1339
- if( find_option("html",0,0)!=0 ) mAlert |= ALERT_HTML;
13401372
 db_find_and_open_repository(0, 0);
13411373
 verify_all_options();
1374
+ db_begin_transaction();
13421375
 email_schema();
1343
- blob_init(&out, 0, 0);
1344
- email_header(mAlert, &out);
1376
+ db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
13451377
 if( bActual ){
1346
- Stmt q;
1347
- db_prepare(&q,
1348
- "SELECT eventid FROM pending_alert, event"
1349
- " WHERE event.objid=substr(pending_alert.eventid,2)+0"
1350
- " ORDER BY event.mtime"
1351
- );
1352
- while( db_step(&q)==SQLITE_ROW ){
1353
- email_one_alert(db_column_text(&q,0), mAlert, &out);
1354
- }
1355
- db_finalize(&q);
1378
+ db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");
13561379
 }else{
13571380
 int i;
13581381
 for(i=2; i<g.argc; i++){
1359
- email_one_alert(g.argv[i], mAlert, &out);
1382
+ db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);
13601383
 }
13611384
 }
1362
- email_footer(mAlert, &out);
1385
+ blob_init(&out, 0, 0);
1386
+ email_header(&out);
1387
+ pEvent = email_compute_event_text(&nEvent);
1388
+ for(p=pEvent; p; p=p->pNext){
1389
+ blob_append(&out, "\n", 1);
1390
+ blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
1391
+ }
1392
+ email_free_eventlist(pEvent);
1393
+ email_footer(&out);
13631394
 fossil_print("%s", blob_str(&out));
13641395
 blob_zero(&out);
1396
+ db_end_transaction(0);
13651397
 }
13661398
--- src/email.c
+++ src/email.c
@@ -70,11 +70,11 @@
70 @ -- 'c4413' means check-in with rid=4413.
71 @ --
72 @ CREATE TABLE repository.pending_alert(
73 @ eventid TEXT PRIMARY KEY, -- Object that changed
74 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
75 @ mtime DATETIME -- when added to queue
76 @ ) WITHOUT ROWID;
77 @
78 @ -- Record bounced emails. If too many bounces are received within
79 @ -- some defined time range, then cancel the subscription. Older
80 @ -- entries are periodically purged.
@@ -1238,23 +1238,48 @@
1238 db_finalize(&q);
1239 style_footer();
1240 }
1241
1242 #if LOCAL_INTERFACE
1243 /* Allowed values for the mAlert flags parameter to email_alert_text
 
 
1244 */
1245 #define ALERT_HTML 0x01 /* Generate HTML instead of plain text */
 
 
 
 
1246 #endif
1247
1248 /*
1249 ** Append the text for a single alert to the end of pOut
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1250 */
1251 void email_one_alert(const char *zEvent, u32 mAlert, Blob *pOut){
1252 static Stmt q;
1253 int id;
1254 const char *zType = "";
1255 db_static_prepare(&q,
 
 
 
1256 "SELECT"
1257 " blob.uuid," /* 0 */
1258 " datetime(event.mtime)," /* 1 */
1259 " coalesce(ecomment,comment)"
1260 " || ' (user: ' || coalesce(euser,user,'?')"
@@ -1262,44 +1287,54 @@
1262 " FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
1263 " FROM tag, tagxref"
1264 " WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
1265 " AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
1266 " || ')' as comment," /* 2 */
1267 " tagxref.value AS branch" /* 3 */
1268 " FROM tag CROSS JOIN event CROSS JOIN blob"
 
1269 " LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
1270 " AND tagxref.tagtype>0"
1271 " AND tagxref.rid=blob.rid"
1272 " WHERE blob.rid=event.objid"
1273 " AND tag.tagname='branch'"
1274 " AND event.objid=:objid"
 
1275 );
1276 switch( zEvent[0] ){
1277 case 'c': zType = "Check-In"; break;
1278 case 't': zType = "Wiki Edit"; break;
1279 case 'w': zType = "Ticket Change"; break;
1280 default: return;
1281 }
1282 id = atoi(zEvent+1);
1283 if( id<=0 ) return;
1284 db_bind_int(&q, ":objid", id);
1285 if( db_step(&q)==SQLITE_ROW ){
1286 blob_appendf(pOut,"\n== %s %s ==\n%s\n%s/info/%.20s\n",
 
 
 
 
 
 
1287 db_column_text(&q,1),
1288 zType,
1289 db_column_text(&q,2),
1290 db_get("email-url","http://localhost:8080"),
1291 db_column_text(&q,0)
1292 );
 
1293 }
1294 db_reset(&q);
 
1295 }
1296
1297 /*
1298 ** Put a header on an alert email
1299 */
1300 void email_header(u32 mAlert, Blob *pOut){
1301 blob_appendf(pOut,
1302 "This is an automated email reporting changes "
1303 "on Fossil repository %s (%s/timeline)\n",
1304 db_get("email-subname","(unknown)"),
1305 db_get("email-url","http://localhost:8080"));
@@ -1307,11 +1342,11 @@
1307
1308 /*
1309 ** Append the "unsubscribe" notification and other footer text to
1310 ** the end of an email alert being assemblied in pOut.
1311 */
1312 void email_footer(u32 mAlert, Blob *pOut){
1313 blob_appendf(pOut, "\n%.72c\nTo unsubscribe: %s/unsubscribe\n",
1314 '-', db_get("email-url","http://localhost:8080"));
1315 }
1316
1317 /*
@@ -1324,42 +1359,39 @@
1324 ** output. If the --actual flag is present, then the EVENTIDs are
1325 ** the actual event-ids in the pending_alert table.
1326 **
1327 ** This command is intended for testing and debugging the logic
1328 ** that generates email alert text.
1329 **
1330 ** The mimetype is text/plain by default. Use the --html option
1331 ** to generate text/html alert text.
1332 */
1333 void test_generate_alert_cmd(void){
1334 u32 mAlert = 0;
1335 int bActual = find_option("actual",0,0)!=0;
1336 Blob out;
1337 int i;
 
 
1338
1339 if( find_option("html",0,0)!=0 ) mAlert |= ALERT_HTML;
1340 db_find_and_open_repository(0, 0);
1341 verify_all_options();
 
1342 email_schema();
1343 blob_init(&out, 0, 0);
1344 email_header(mAlert, &out);
1345 if( bActual ){
1346 Stmt q;
1347 db_prepare(&q,
1348 "SELECT eventid FROM pending_alert, event"
1349 " WHERE event.objid=substr(pending_alert.eventid,2)+0"
1350 " ORDER BY event.mtime"
1351 );
1352 while( db_step(&q)==SQLITE_ROW ){
1353 email_one_alert(db_column_text(&q,0), mAlert, &out);
1354 }
1355 db_finalize(&q);
1356 }else{
1357 int i;
1358 for(i=2; i<g.argc; i++){
1359 email_one_alert(g.argv[i], mAlert, &out);
1360 }
1361 }
1362 email_footer(mAlert, &out);
 
 
 
 
 
 
 
 
1363 fossil_print("%s", blob_str(&out));
1364 blob_zero(&out);
 
1365 }
1366
--- src/email.c
+++ src/email.c
@@ -70,11 +70,11 @@
70 @ -- 'c4413' means check-in with rid=4413.
71 @ --
72 @ CREATE TABLE repository.pending_alert(
73 @ eventid TEXT PRIMARY KEY, -- Object that changed
74 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
75 @ sendDigest BOOLEAN DEFAULT false, -- digest emails sent
76 @ ) WITHOUT ROWID;
77 @
78 @ -- Record bounced emails. If too many bounces are received within
79 @ -- some defined time range, then cancel the subscription. Older
80 @ -- entries are periodically purged.
@@ -1238,23 +1238,48 @@
1238 db_finalize(&q);
1239 style_footer();
1240 }
1241
1242 #if LOCAL_INTERFACE
1243 /*
1244 ** A single event that might appear in an alert is recorded as an
1245 ** instance of the following object.
1246 */
1247 struct EmailEvent {
1248 int type; /* 'c', 't', 'w', etc. */
1249 Blob txt; /* Text description to appear in an alert */
1250 EmailEvent *pNext; /* Next in chronological order */
1251 };
1252 #endif
1253
1254 /*
1255 ** Free a linked list of EmailEvent objects
1256 */
1257 void email_free_eventlist(EmailEvent *p){
1258 while( p ){
1259 EmailEvent *pNext = p->pNext;
1260 blob_zero(&p->txt);
1261 fossil_free(p);
1262 p = pNext;
1263 }
1264 }
1265
1266 /*
1267 ** Compute and return a linked list of EmailEvent objects
1268 ** corresponding to the current content of the temp.wantalert
1269 ** table which should be defined as follows:
1270 **
1271 ** CREATE TEMP TABLE wantalert(eventId TEXT);
1272 */
1273 EmailEvent *email_compute_event_text(int *pnEvent){
1274 Stmt q;
1275 EmailEvent *p;
1276 EmailEvent anchor;
1277 EmailEvent *pLast;
1278 const char *zUrl = db_get("email-url","http://localhost:8080");
1279
1280 db_prepare(&q,
1281 "SELECT"
1282 " blob.uuid," /* 0 */
1283 " datetime(event.mtime)," /* 1 */
1284 " coalesce(ecomment,comment)"
1285 " || ' (user: ' || coalesce(euser,user,'?')"
@@ -1262,44 +1287,54 @@
1287 " FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
1288 " FROM tag, tagxref"
1289 " WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
1290 " AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
1291 " || ')' as comment," /* 2 */
1292 " tagxref.value AS branch," /* 3 */
1293 " wantalert.eventId" /* 4 */
1294 " FROM temp.wantalert JOIN tag CROSS JOIN event CROSS JOIN blob"
1295 " LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
1296 " AND tagxref.tagtype>0"
1297 " AND tagxref.rid=blob.rid"
1298 " WHERE blob.rid=event.objid"
1299 " AND tag.tagname='branch'"
1300 " AND event.objid=substr(wantalert.eventId,2)+0"
1301 " ORDER BY event.mtime"
1302 );
1303 memset(&anchor, 0, sizeof(anchor));
1304 pLast = &anchor;
1305 *pnEvent = 0;
1306 while( db_step(&q)==SQLITE_ROW ){
1307 const char *zType = "";
1308 p = fossil_malloc( sizeof(EmailEvent) );
1309 pLast->pNext = p;
1310 pLast = p;
1311 p->type = db_column_text(&q, 4)[0];
1312 p->pNext = 0;
1313 switch( p->type ){
1314 case 'c': zType = "Check-In"; break;
1315 case 't': zType = "Wiki Edit"; break;
1316 case 'w': zType = "Ticket Change"; break;
1317 }
1318 blob_init(&p->txt, 0, 0);
1319 blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
1320 db_column_text(&q,1),
1321 zType,
1322 db_column_text(&q,2),
1323 zUrl,
1324 db_column_text(&q,0)
1325 );
1326 *pnEvent++;
1327 }
1328 db_finalize(&q);
1329 return anchor.pNext;
1330 }
1331
1332 /*
1333 ** Put a header on an alert email
1334 */
1335 void email_header(Blob *pOut){
1336 blob_appendf(pOut,
1337 "This is an automated email reporting changes "
1338 "on Fossil repository %s (%s/timeline)\n",
1339 db_get("email-subname","(unknown)"),
1340 db_get("email-url","http://localhost:8080"));
@@ -1307,11 +1342,11 @@
1342
1343 /*
1344 ** Append the "unsubscribe" notification and other footer text to
1345 ** the end of an email alert being assemblied in pOut.
1346 */
1347 void email_footer(Blob *pOut){
1348 blob_appendf(pOut, "\n%.72c\nTo unsubscribe: %s/unsubscribe\n",
1349 '-', db_get("email-url","http://localhost:8080"));
1350 }
1351
1352 /*
@@ -1324,42 +1359,39 @@
1359 ** output. If the --actual flag is present, then the EVENTIDs are
1360 ** the actual event-ids in the pending_alert table.
1361 **
1362 ** This command is intended for testing and debugging the logic
1363 ** that generates email alert text.
 
 
 
1364 */
1365 void test_generate_alert_cmd(void){
 
1366 int bActual = find_option("actual",0,0)!=0;
1367 Blob out;
1368 int i;
1369 int nEvent;
1370 EmailEvent *pEvent, *p;
1371
 
1372 db_find_and_open_repository(0, 0);
1373 verify_all_options();
1374 db_begin_transaction();
1375 email_schema();
1376 db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
 
1377 if( bActual ){
1378 db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");
 
 
 
 
 
 
 
 
 
1379 }else{
1380 int i;
1381 for(i=2; i<g.argc; i++){
1382 db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);
1383 }
1384 }
1385 blob_init(&out, 0, 0);
1386 email_header(&out);
1387 pEvent = email_compute_event_text(&nEvent);
1388 for(p=pEvent; p; p=p->pNext){
1389 blob_append(&out, "\n", 1);
1390 blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
1391 }
1392 email_free_eventlist(pEvent);
1393 email_footer(&out);
1394 fossil_print("%s", blob_str(&out));
1395 blob_zero(&out);
1396 db_end_transaction(0);
1397 }
1398

Keyboard Shortcuts

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