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

Overview
Comment:Fix minor problems in email notification.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:1cdf6f2049f7658e16fac9034d105cb15f2f659e51eaa9dc260241e6901d6014
User & Date: drh 2018-06-23 01:24:35
Context
2018-06-23
01:26
Fix minor problems with email notification. check-in: d7ccfb64 user: drh tags: trunk
01:24
Fix minor problems in email notification. check-in: 1cdf6f20 user: drh tags: trunk
00:53
Merge in support for email notifications. Munch more work is needed, but it seems ready to begin functional testing. check-in: b77f1aae user: drh tags: trunk
Changes

Changes to src/email.c.

68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
...
466
467
468
469
470
471
472

473
474
475
476
477
478
479
...
502
503
504
505
506
507
508



509
510
511
512
513
514
515
....
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
....
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510

1511
1512
1513
1514
1515
1516
1517
....
1519
1520
1521
1522
1523
1524
1525

1526
1527
1528
1529
1530
1531
1532
....
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
....
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
@ -- 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(
................................................................................
**    exec                    Compose and send pending email alerts.
**                            Some installations may want to do this via
**                            a cron-job to make sure alerts are sent
**                            in a timely manner.
**                            Options:
**
**                               --digest     Send digests

**
**    inbound [FILE]          Receive an inbound email message.  This message
**                            is analyzed to see if it is a bounce, and if
**                            necessary, subscribers may be disabled.
**
**    reset                   Hard reset of all email notification tables
**                            in the repository.  This erases all subscription
................................................................................
  db_find_and_open_repository(0, 0);
  email_schema();
  zCmd = g.argc>=3 ? g.argv[2] : "x";
  nCmd = (int)strlen(zCmd);
  if( strncmp(zCmd, "exec", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;



    verify_all_options();
    email_send_alerts(eFlags);
  }else
  if( strncmp(zCmd, "inbound", nCmd)==0 ){
    Blob email;
    const char *zInboundDir = db_get("email-receive-dir","");
    verify_all_options();
................................................................................
*/
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
**
** Usage: %fossil test-generate-alert [--html] [--actual] EVENTID ...
**
** 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 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]);
    }
  }
................................................................................
void test_add_alert_cmd(void){
  int i;
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema();
  for(i=2; i<g.argc; i++){
    db_multi_exec("INSERT INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
}

#if INTERFACE
/*
** Flags for email_send_alerts()
*/
#define SENDALERT_DIGEST      0x0001    /* Send a digest */
#define SENDALERT_PRESERVE    0x0002    /* Do not mark the task as done */


#endif /* INTERFACE */

/*
** Send alert emails to all subscribers
*/
void email_send_alerts(u32 flags){
................................................................................
  int nEvent = 0;
  Stmt q;
  const char *zDigest = "false";
  Blob hdr, body;
  const char *zUrl;
  const char *zRepoName;
  const char *zFrom;


  db_begin_transaction();
  if( !email_enabled() ) goto send_alerts_done;
  zUrl = db_get("email-url",0);
  if( zUrl==0 ) goto send_alerts_done;
  zRepoName = db_get("email-subname",0);
  if( zRepoName==0 ) goto send_alerts_done;
................................................................................
  }
  pEvents = email_compute_event_text(&nEvent);
  if( nEvent==0 ) return;
  blob_init(&hdr, 0, 0);
  blob_init(&body, 0, 0);
  db_prepare(&q,
     "SELECT"
     " subscriberCode,"  /* 0 */
     " semail,"          /* 1 */
     " ssub"             /* 2 */
     " FROM subscriber"
     " WHERE sverified AND NOT sdonotcall"
     "  AND sdigest IS %s",
     zDigest/*safe-for-%s*/
  );
  while( db_step(&q)==SQLITE_ROW ){
    const char *zCode = db_column_text(&q, 0);
................................................................................
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    int nHit = 0;
    for(p=pEvents; p; p=p->pNext){
      if( strchr(zSub,p->type)==0 ) continue;
      if( nHit==0 ){
        blob_appendf(&hdr,"To: %s\n", zEmail);
        blob_appendf(&hdr,"From: %s\n", zFrom);
        blob_appendf(&hdr,"Subject: %s activity alert\n", zRepoName);
        blob_appendf(&body,
          "This is an automated email sent by the Fossil repository "
          "at %s to alert you to changes.\n",
          zUrl
        );
      }
      nHit++;
      blob_append(&body, "\n", 1);
      blob_append(&body, blob_buffer(&p->txt), blob_size(&p->txt));
    }
    if( nHit==0 ) continue;
    blob_appendf(&body,"\n%.72c\nSubscription info: %s/alerts/%s\n",
         '-', zUrl, zCode);
    email_send(&hdr,&body,0,0);
    blob_truncate(&hdr);
    blob_truncate(&body);
  }
  blob_zero(&hdr);
  blob_zero(&body);
  db_finalize(&q);
  email_free_eventlist(pEvents);







|







 







>







 







>
>
>







 







|

|


|
|
|




|
<









|







 







|










>







 







>







 







|
|
|







 







<



|










|







68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
...
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
...
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
....
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455

1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
....
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
....
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
....
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
....
1572
1573
1574
1575
1576
1577
1578

1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
@ -- 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
@   sentDigest 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(
................................................................................
**    exec                    Compose and send pending email alerts.
**                            Some installations may want to do this via
**                            a cron-job to make sure alerts are sent
**                            in a timely manner.
**                            Options:
**
**                               --digest     Send digests
**                               --test       Resets to standard output
**
**    inbound [FILE]          Receive an inbound email message.  This message
**                            is analyzed to see if it is a bounce, and if
**                            necessary, subscribers may be disabled.
**
**    reset                   Hard reset of all email notification tables
**                            in the repository.  This erases all subscription
................................................................................
  db_find_and_open_repository(0, 0);
  email_schema();
  zCmd = g.argc>=3 ? g.argv[2] : "x";
  nCmd = (int)strlen(zCmd);
  if( strncmp(zCmd, "exec", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
    if( find_option("test",0,0)!=0 ){
      eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
    }
    verify_all_options();
    email_send_alerts(eFlags);
  }else
  if( strncmp(zCmd, "inbound", nCmd)==0 ){
    Blob email;
    const char *zInboundDir = db_get("email-receive-dir","");
    verify_all_options();
................................................................................
*/
void email_footer(Blob *pOut){
  blob_appendf(pOut, "\n%.72c\nTo unsubscribe: %s/unsubscribe\n",
     '-', db_get("email-url","http://localhost:8080"));
}

/*
** COMMAND:  test-alert
**
** Usage: %fossil test-alert EVENTID ...
**
** Generate the text of an email alert for all of the EVENTIDs
** listed on the command-line.  Or if no events are listed on the
** command line, generate text for all events named in the
** pending_alert table.
**
** This command is intended for testing and debugging the logic
** that generates email alert text.
*/
void test_alert_cmd(void){

  Blob out;
  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( g.argc==2 ){
    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]);
    }
  }
................................................................................
void test_add_alert_cmd(void){
  int i;
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema();
  for(i=2; i<g.argc; i++){
    db_multi_exec("REPLACE INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
}

#if INTERFACE
/*
** Flags for email_send_alerts()
*/
#define SENDALERT_DIGEST      0x0001    /* Send a digest */
#define SENDALERT_PRESERVE    0x0002    /* Do not mark the task as done */
#define SENDALERT_STDOUT      0x0004    /* Print emails instead of sending */

#endif /* INTERFACE */

/*
** Send alert emails to all subscribers
*/
void email_send_alerts(u32 flags){
................................................................................
  int nEvent = 0;
  Stmt q;
  const char *zDigest = "false";
  Blob hdr, body;
  const char *zUrl;
  const char *zRepoName;
  const char *zFrom;
  const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;

  db_begin_transaction();
  if( !email_enabled() ) goto send_alerts_done;
  zUrl = db_get("email-url",0);
  if( zUrl==0 ) goto send_alerts_done;
  zRepoName = db_get("email-subname",0);
  if( zRepoName==0 ) goto send_alerts_done;
................................................................................
  }
  pEvents = email_compute_event_text(&nEvent);
  if( nEvent==0 ) return;
  blob_init(&hdr, 0, 0);
  blob_init(&body, 0, 0);
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
     " ssub"                  /* 2 */
     " FROM subscriber"
     " WHERE sverified AND NOT sdonotcall"
     "  AND sdigest IS %s",
     zDigest/*safe-for-%s*/
  );
  while( db_step(&q)==SQLITE_ROW ){
    const char *zCode = db_column_text(&q, 0);
................................................................................
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    int nHit = 0;
    for(p=pEvents; p; p=p->pNext){
      if( strchr(zSub,p->type)==0 ) continue;
      if( nHit==0 ){
        blob_appendf(&hdr,"To: %s\n", zEmail);

        blob_appendf(&hdr,"Subject: %s activity alert\n", zRepoName);
        blob_appendf(&body,
          "This is an automated email sent by the Fossil repository "
          "at %s to report changes.\n",
          zUrl
        );
      }
      nHit++;
      blob_append(&body, "\n", 1);
      blob_append(&body, blob_buffer(&p->txt), blob_size(&p->txt));
    }
    if( nHit==0 ) continue;
    blob_appendf(&body,"\n%.72c\nSubscription info: %s/alerts/%s\n",
         '-', zUrl, zCode);
    email_send(&hdr,&body,0,zDest);
    blob_truncate(&hdr);
    blob_truncate(&body);
  }
  blob_zero(&hdr);
  blob_zero(&body);
  db_finalize(&q);
  email_free_eventlist(pEvents);