Fossil

Check-in [6c06b1c8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:A new way of computing alert text.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | email-alerts
Files: files | file ages | folders
SHA3-256:6c06b1c896e9c97f9e3bc1cae57a796d8e8e0ea1daff842089c4b72078d38e7c
User & Date: drh 2018-06-22 17:36:33
Context
2018-06-22
18:23
Alert sending logic compiles but is so far untested. check-in: 44f9f355 user: drh tags: email-alerts
17:36
A new way of computing alert text. check-in: 6c06b1c8 user: drh tags: email-alerts
15:57
Generate event report in chronological order for an alert text. check-in: e0289252 user: drh tags: email-alerts
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/email.c.

68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
....
1236
1237
1238
1239
1240
1241
1242
1243


1244
1245





1246
1247
1248
1249

1250
1251

















1252
1253
1254





1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267

1268
1269
1270
1271
1272
1273
1274

1275










1276
1277
1278
1279
1280
1281
1282
1283
1284
1285

1286
1287
1288
1289
1290

1291
1292

1293
1294


1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
....
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337


1338
1339
1340
1341

1342
1343
1344

1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361








1362
1363
1364

1365
@ -- The first character of the eventid determines the event type.
@ -- Remaining characters determine the specific event.  For example,
@ -- 'c4413' means check-in with rid=4413.
@ --
@ CREATE TABLE repository.pending_alert(
@   eventid TEXT PRIMARY KEY,         -- Object that changed
@   sentSep BOOLEAN DEFAULT false,    -- individual emails sent
@   mtime DATETIME                    -- when added to queue
@ ) WITHOUT ROWID;
@ 
@ -- Record bounced emails.  If too many bounces are received within
@ -- some defined time range, then cancel the subscription.  Older
@ -- entries are periodically purged.
@ --
@ CREATE TABLE repository.email_bounce(
................................................................................
  }
  @ </table>
  db_finalize(&q);
  style_footer();
}

#if LOCAL_INTERFACE
/* Allowed values for the mAlert flags parameter to email_alert_text


*/
#define ALERT_HTML     0x01      /* Generate HTML instead of plain text */





#endif

/*
** Append the text for a single alert to the end of pOut

*/
void email_one_alert(const char *zEvent, u32 mAlert, Blob *pOut){

















  static Stmt q;
  int id;
  const char *zType = "";





  db_static_prepare(&q,
    "SELECT"
    " blob.uuid,"  /* 0 */
    " datetime(event.mtime),"  /* 1 */
    " coalesce(ecomment,comment)"
    "  || ' (user: ' || coalesce(euser,user,'?')"
    "  || (SELECT case when length(x)>0 then ' tags: ' || x else '' end"
    "      FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
    "              FROM tag, tagxref"
    "             WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
    "               AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
    "  || ')' as comment,"  /* 2 */
    " tagxref.value AS branch"  /* 3 */

    " FROM tag CROSS JOIN event CROSS JOIN blob"
    "  LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
    "                       AND tagxref.tagtype>0"
    "                       AND tagxref.rid=blob.rid"
    " WHERE blob.rid=event.objid"
    "   AND tag.tagname='branch'"
    "   AND event.objid=:objid"

  );










  switch( zEvent[0] ){
    case 'c':  zType = "Check-In";        break;
    case 't':  zType = "Wiki Edit";       break;
    case 'w':  zType = "Ticket Change";   break;
    default:   return;
  }
  id = atoi(zEvent+1);
  if( id<=0 ) return;
  db_bind_int(&q, ":objid", id);
  if( db_step(&q)==SQLITE_ROW ){

    blob_appendf(pOut,"\n== %s %s ==\n%s\n%s/info/%.20s\n",
      db_column_text(&q,1),
      zType,
      db_column_text(&q,2),
      db_get("email-url","http://localhost:8080"),

      db_column_text(&q,0)
    );

  }
  db_reset(&q);


}

/*
** Put a header on an alert email
*/
void email_header(u32 mAlert, Blob *pOut){
  blob_appendf(pOut,
    "This is an automated email reporting changes "
    "on Fossil repository %s (%s/timeline)\n",
    db_get("email-subname","(unknown)"),
    db_get("email-url","http://localhost:8080"));
}

/*
** Append the "unsubscribe" notification and other footer text to
** the end of an email alert being assemblied in pOut.
*/
void email_footer(u32 mAlert, Blob *pOut){
  blob_appendf(pOut, "\n%.72c\nTo unsubscribe: %s/unsubscribe\n",
     '-', db_get("email-url","http://localhost:8080"));
}

/*
** COMMAND:  test-generate-alert
**
................................................................................
** Generate the text of an email alert for all of the EVENTIDs
** listed on the command-line.  Write that text to standard
** output.  If the --actual flag is present, then the EVENTIDs are
** the actual event-ids in the pending_alert table.
**
** This command is intended for testing and debugging the logic
** that generates email alert text.
**
** The mimetype is text/plain by default.  Use the --html option
** to generate text/html alert text.
*/
void test_generate_alert_cmd(void){
  u32 mAlert = 0;
  int bActual = find_option("actual",0,0)!=0;
  Blob out;
  int i;



  if( find_option("html",0,0)!=0 ) mAlert |= ALERT_HTML;
  db_find_and_open_repository(0, 0);
  verify_all_options();

  email_schema();
  blob_init(&out, 0, 0);
  email_header(mAlert, &out);

  if( bActual ){
    Stmt q;
    db_prepare(&q,
       "SELECT eventid FROM pending_alert, event"
       " WHERE event.objid=substr(pending_alert.eventid,2)+0"
       " ORDER BY event.mtime"
    );
    while( db_step(&q)==SQLITE_ROW ){
      email_one_alert(db_column_text(&q,0), mAlert, &out);
    }
    db_finalize(&q);
  }else{
    int i;
    for(i=2; i<g.argc; i++){
      email_one_alert(g.argv[i], mAlert, &out);
    }
  }








  email_footer(mAlert, &out);
  fossil_print("%s", blob_str(&out));
  blob_zero(&out);

}







|







 







|
>
>

<
>
>
>
>
>



<
>

<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
>
>
>
>
>
|











|
>
|





|
>

>
>
>
>
>
>
>
>
>
>
|
|
|
|
<
|
<
<
<
<
>
|



<
>


>

<
>
>





|











|







 







<
<
<


<



>
>

<


>

<
<
>

<
<
|
<
<
<
<
<
<
<



|


>
>
>
>
>
>
>
>
|


>

68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
....
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246

1247
1248
1249
1250
1251
1252
1253
1254

1255
1256

1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274


1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316

1317




1318
1319
1320
1321
1322

1323
1324
1325
1326
1327

1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
....
1357
1358
1359
1360
1361
1362
1363



1364
1365

1366
1367
1368
1369
1370
1371

1372
1373
1374
1375


1376
1377


1378







1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
@ -- The first character of the eventid determines the event type.
@ -- Remaining characters determine the specific event.  For example,
@ -- 'c4413' means check-in with rid=4413.
@ --
@ CREATE TABLE repository.pending_alert(
@   eventid TEXT PRIMARY KEY,         -- Object that changed
@   sentSep BOOLEAN DEFAULT false,    -- individual emails sent
@   sendDigest BOOLEAN DEFAULT false, -- digest emails sent
@ ) WITHOUT ROWID;
@ 
@ -- Record bounced emails.  If too many bounces are received within
@ -- some defined time range, then cancel the subscription.  Older
@ -- entries are periodically purged.
@ --
@ CREATE TABLE repository.email_bounce(
................................................................................
  }
  @ </table>
  db_finalize(&q);
  style_footer();
}

#if LOCAL_INTERFACE
/*
** A single event that might appear in an alert is recorded as an
** instance of the following object.
*/

struct EmailEvent {
  int type;          /* 'c', 't', 'w', etc. */
  Blob txt;          /* Text description to appear in an alert */
  EmailEvent *pNext; /* Next in chronological order */
};
#endif

/*

** Free a linked list of EmailEvent objects
*/

void email_free_eventlist(EmailEvent *p){
  while( p ){
    EmailEvent *pNext = p->pNext;
    blob_zero(&p->txt);
    fossil_free(p);
    p = pNext;
  }
}

/*
** Compute and return a linked list of EmailEvent objects
** corresponding to the current content of the temp.wantalert
** table which should be defined as follows:
**
**     CREATE TEMP TABLE wantalert(eventId TEXT);
*/
EmailEvent *email_compute_event_text(int *pnEvent){
  Stmt q;


  EmailEvent *p;
  EmailEvent anchor;
  EmailEvent *pLast;
  const char *zUrl = db_get("email-url","http://localhost:8080");

  db_prepare(&q,
    "SELECT"
    " blob.uuid,"  /* 0 */
    " datetime(event.mtime),"  /* 1 */
    " coalesce(ecomment,comment)"
    "  || ' (user: ' || coalesce(euser,user,'?')"
    "  || (SELECT case when length(x)>0 then ' tags: ' || x else '' end"
    "      FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
    "              FROM tag, tagxref"
    "             WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
    "               AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
    "  || ')' as comment,"  /* 2 */
    " tagxref.value AS branch,"  /* 3 */
    " wantalert.eventId"     /* 4 */
    " FROM temp.wantalert JOIN tag CROSS JOIN event CROSS JOIN blob"
    "  LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
    "                       AND tagxref.tagtype>0"
    "                       AND tagxref.rid=blob.rid"
    " WHERE blob.rid=event.objid"
    "   AND tag.tagname='branch'"
    "   AND event.objid=substr(wantalert.eventId,2)+0"
    " ORDER BY event.mtime"
  );
  memset(&anchor, 0, sizeof(anchor));
  pLast = &anchor;
  *pnEvent = 0;
  while( db_step(&q)==SQLITE_ROW ){
    const char *zType = "";
    p = fossil_malloc( sizeof(EmailEvent) );
    pLast->pNext = p;
    pLast = p;
    p->type = db_column_text(&q, 4)[0];
    p->pNext = 0;
    switch( p->type ){
      case 'c':  zType = "Check-In";        break;
      case 't':  zType = "Wiki Edit";       break;
      case 'w':  zType = "Ticket Change";   break;

    }




    blob_init(&p->txt, 0, 0);
    blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
      db_column_text(&q,1),
      zType,
      db_column_text(&q,2),

      zUrl,
      db_column_text(&q,0)
    );
    *pnEvent++;
  }

  db_finalize(&q);
  return anchor.pNext;
}

/*
** Put a header on an alert email
*/
void email_header(Blob *pOut){
  blob_appendf(pOut,
    "This is an automated email reporting changes "
    "on Fossil repository %s (%s/timeline)\n",
    db_get("email-subname","(unknown)"),
    db_get("email-url","http://localhost:8080"));
}

/*
** Append the "unsubscribe" notification and other footer text to
** the end of an email alert being assemblied in pOut.
*/
void email_footer(Blob *pOut){
  blob_appendf(pOut, "\n%.72c\nTo unsubscribe: %s/unsubscribe\n",
     '-', db_get("email-url","http://localhost:8080"));
}

/*
** COMMAND:  test-generate-alert
**
................................................................................
** Generate the text of an email alert for all of the EVENTIDs
** listed on the command-line.  Write that text to standard
** output.  If the --actual flag is present, then the EVENTIDs are
** the actual event-ids in the pending_alert table.
**
** This command is intended for testing and debugging the logic
** that generates email alert text.



*/
void test_generate_alert_cmd(void){

  int bActual = find_option("actual",0,0)!=0;
  Blob out;
  int i;
  int nEvent;
  EmailEvent *pEvent, *p;


  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema();


  db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
  if( bActual ){


    db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");







  }else{
    int i;
    for(i=2; i<g.argc; i++){
      db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);
    }
  }
  blob_init(&out, 0, 0);
  email_header(&out);
  pEvent = email_compute_event_text(&nEvent);
  for(p=pEvent; p; p=p->pNext){
    blob_append(&out, "\n", 1);
    blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
  }
  email_free_eventlist(pEvent);
  email_footer(&out);
  fossil_print("%s", blob_str(&out));
  blob_zero(&out);
  db_end_transaction(0);
}