Fossil

Check-in [8c4b92ad]
Login

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

Overview
Comment:Rename the email_pending table to pending_alert. Add triggers to fill in the pending_alert table each time a row is added to the event table.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | email-alerts
Files: files | file ages | folders
SHA3-256: 8c4b92ad3e27bb17bdde5ce5005f6b12852513b80a77581f6b1e39a93e402a53
User & Date: drh 2018-06-22 01:18:59.149
Context
2018-06-22
01:28
Fix harmless compiler warnings. ... (check-in: 5fde17bb user: drh tags: email-alerts)
01:18
Rename the email_pending table to pending_alert. Add triggers to fill in the pending_alert table each time a row is added to the event table. ... (check-in: 8c4b92ad user: drh tags: email-alerts)
2018-06-21
23:01
Add the "fossil email inbound" command, though it currently does not analyze the inbound emails - it just stores the emails in a directory for later human viewing. ... (check-in: 775e529b user: drh tags: email-alerts)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/email.c.
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
@   smip TEXT                         -- IP address of last change
@ );
@ CREATE INDEX repository.subscriberUname
@   ON subscriber(suname) WHERE suname IS NOT NULL;
@ 
@ -- Email notifications that need to be sent.
@ --
@ -- If the eventid key is an integer, then it corresponds to the
@ -- EVENT.OBJID table.  Other kinds of eventids are reserved for
@ -- future expansion.
@ --
@ CREATE TABLE repository.email_pending(
@   eventid ANY 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.







|
|
|

|
|







61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
@   smip TEXT                         -- IP address of last change
@ );
@ CREATE INDEX repository.subscriberUname
@   ON subscriber(suname) WHERE suname IS NOT NULL;
@ 
@ -- Email notifications that need to be sent.
@ --
@ -- 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.
88
89
90
91
92
93
94

95
96




























97
98
99
100
101
102
103

/*
** Make sure the unversioned table exists in the repository.
*/
void email_schema(void){
  if( !db_table_exists("repository", "subscriber") ){
    db_multi_exec(zEmailInit/*works-like:""*/);

  }
}





























/*
** Return true if email alerts are active.
*/
int email_enabled(void){
  if( !db_table_exists("repository", "subscriber") ) return 0;
  if( fossil_strcmp(db_get("email-send-method","off"),"off")==0 ) return 0;







>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

/*
** Make sure the unversioned table exists in the repository.
*/
void email_schema(void){
  if( !db_table_exists("repository", "subscriber") ){
    db_multi_exec(zEmailInit/*works-like:""*/);
    email_triggers_enable();
  }
}

/*
** Enable triggers that automatically populate the event_pending
** table.
*/
void email_triggers_enable(void){
  if( !db_table_exists("repository","pending_alert") ) return;
  db_multi_exec(
    "CREATE TRIGGER IF NOT EXISTS repository.email_trigger1\n"
    "AFTER INSERT ON event BEGIN\n"
    "  INSERT INTO pending_alert(eventid)\n"
    "    SELECT printf('%%.1c%%d',new.type,new.objid) WHERE true\n"
    "    ON CONFLICT(eventId) DO NOTHING;\n"
    "END;"
  );
}

/*
** Disable triggers the event_pending triggers.
**
** This must be called before rebuilding the EVENT table, for example
** via the "fossil rebuild" command.
*/
void email_triggers_disable(void){
  db_multi_exec(
    "DROP TRIGGER IF EXISTS repository.email_trigger1;\n"
  );
}

/*
** Return true if email alerts are active.
*/
int email_enabled(void){
  if( !db_table_exists("repository", "subscriber") ) return 0;
  if( fossil_strcmp(db_get("email-send-method","off"),"off")==0 ) return 0;
442
443
444
445
446
447
448
449
450






451
452
453
454
455
456


457

458
459
460


461
462
463
464
465
466
467
468
469
470
471
472
473
      char *zFN = emailTempFilename(zInboundDir);
      blob_write_to_file(&email, zFN);
      fossil_free(zFN);
    }
    email_receive(&email);
  }else
  if( strncmp(zCmd, "reset", nCmd)==0 ){
    Blob yn;
    int c;






    fossil_print(
        "This will erase all content in the repository tables, thus\n"
        "deleting all subscriber information.  The information will be\n"
        "unrecoverable.\n");
    prompt_user("Continue? (y/N) ", &yn);
    c = blob_str(&yn)[0];


    if( c=='y' ){

      db_multi_exec(
        "DROP TABLE IF EXISTS subscriber;\n"
        "DROP TABLE IF EXISTS subscription;\n"


        "DROP TABLE IF EXISTS email_pending;\n"
        "DROP TABLE IF EXISTS email_bounce;\n"
      );
      email_schema();
    }
    blob_zero(&yn);
  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    Blob prompt, body, hdr;
    int sendAsBoth = find_option("both",0,0)!=0;
    int sendAsHtml = find_option("html",0,0)!=0;
    const char *zDest = find_option("stdout",0,0)!=0 ? "stdout" : 0;
    int i;







<

>
>
>
>
>
>
|
|
|
|
|
|
>
>

>


|
>
>

|



<







471
472
473
474
475
476
477

478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504

505
506
507
508
509
510
511
      char *zFN = emailTempFilename(zInboundDir);
      blob_write_to_file(&email, zFN);
      fossil_free(zFN);
    }
    email_receive(&email);
  }else
  if( strncmp(zCmd, "reset", nCmd)==0 ){

    int c;
    int bForce = find_option("force","f",0)!=0;
    verify_all_options();
    if( bForce ){
      c = 'y';
    }else{
      Blob yn;
      fossil_print(
          "This will erase all content in the repository tables, thus\n"
          "deleting all subscriber information.  The information will be\n"
          "unrecoverable.\n");
      prompt_user("Continue? (y/N) ", &yn);
      c = blob_str(&yn)[0];
      blob_zero(&yn);
    }
    if( c=='y' ){
      email_triggers_disable();
      db_multi_exec(
        "DROP TABLE IF EXISTS subscriber;\n"
        "DROP TABLE IF EXISTS pending_alert;\n"
        "DROP TABLE IF EXISTS email_bounce;\n"
        /* Legacy */
        "DROP TABLE IF EXISTS email_pending;\n"
        "DROP TABLE IF EXISTS subscription;\n"
      );
      email_schema();
    }

  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    Blob prompt, body, hdr;
    int sendAsBoth = find_option("both",0,0)!=0;
    int sendAsHtml = find_option("html",0,0)!=0;
    const char *zDest = find_option("stdout",0,0)!=0 ? "stdout" : 0;
    int i;
Changes to src/rebuild.c.
142
143
144
145
146
147
148
149

150
151
152
153
154
155
156

/*
** Update the repository schema for Fossil version 2.0.  (2017-02-28)
**   (1) Change the CHECK constraint on BLOB.UUID so that the length
**       is greater than or equal to 40, not exactly equal to 40.
*/
void rebuild_schema_update_2_0(void){
  char *z = db_text(0, "SELECT sql FROM repository.sqlite_master WHERE name='blob'");

  if( z ){
    /* Search for:  length(uuid)==40
    **              0123456789 12345   */
    int i;
    for(i=10; z[i]; i++){
      if( z[i]=='=' && strncmp(&z[i-6],"(uuid)==40",10)==0 ){
        z[i] = '>';







|
>







142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

/*
** Update the repository schema for Fossil version 2.0.  (2017-02-28)
**   (1) Change the CHECK constraint on BLOB.UUID so that the length
**       is greater than or equal to 40, not exactly equal to 40.
*/
void rebuild_schema_update_2_0(void){
  char *z = db_text(0, "SELECT sql FROM repository.sqlite_master"
                       " WHERE name='blob'");
  if( z ){
    /* Search for:  length(uuid)==40
    **              0123456789 12345   */
    int i;
    for(i=10; z[i]; i++){
      if( z[i]=='=' && strncmp(&z[i-6],"(uuid)==40",10)==0 ){
        z[i] = '>';
355
356
357
358
359
360
361

362
363
364
365
366
367
368
369
370

371
372
373
374
375
376
377

  bag_init(&bagDone);
  ttyOutput = doOut;
  processCnt = 0;
  if (ttyOutput && !g.fQuiet) {
    percent_complete(0);
  }

  rebuild_update_schema();
  blob_init(&sql, 0, 0);
  db_prepare(&q,
     "SELECT name FROM sqlite_master /*scan*/"
     " WHERE type='table'"
     " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
                       "'config','shun','private','reportfmt',"
                       "'concealed','accesslog','modreq',"
                       "'purgeevent','purgeitem','unversioned')"

     " AND name NOT GLOB 'sqlite_*'"
     " AND name NOT GLOB 'fx_*'"
  );
  while( db_step(&q)==SQLITE_ROW ){
    blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
  }
  db_finalize(&q);







>








|
>







356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380

  bag_init(&bagDone);
  ttyOutput = doOut;
  processCnt = 0;
  if (ttyOutput && !g.fQuiet) {
    percent_complete(0);
  }
  email_triggers_disable();
  rebuild_update_schema();
  blob_init(&sql, 0, 0);
  db_prepare(&q,
     "SELECT name FROM sqlite_master /*scan*/"
     " WHERE type='table'"
     " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
                       "'config','shun','private','reportfmt',"
                       "'concealed','accesslog','modreq',"
                       "'purgeevent','purgeitem','unversioned',"
                       "'subscriber','pending_alert','email_bounce')"
     " AND name NOT GLOB 'sqlite_*'"
     " AND name NOT GLOB 'fx_*'"
  );
  while( db_step(&q)==SQLITE_ROW ){
    blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
  }
  db_finalize(&q);
444
445
446
447
448
449
450

451
452
453
454
455
456
457
    percent_complete((processCnt*1000)/totalSize);
  }
  if( doClustering ) create_cluster();
  if( ttyOutput && !g.fQuiet && totalSize>0 ){
    processCnt += incrSize;
    percent_complete((processCnt*1000)/totalSize);
  }

  if(!g.fQuiet && ttyOutput ){
    percent_complete(1000);
    fossil_print("\n");
  }
  return errCnt;
}








>







447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
    percent_complete((processCnt*1000)/totalSize);
  }
  if( doClustering ) create_cluster();
  if( ttyOutput && !g.fQuiet && totalSize>0 ){
    processCnt += incrSize;
    percent_complete((processCnt*1000)/totalSize);
  }
  email_triggers_enable();
  if(!g.fQuiet && ttyOutput ){
    percent_complete(1000);
    fossil_print("\n");
  }
  return errCnt;
}