Fossil

Check-in [efbd6caa]
Login

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

Overview
Comment:Improvements to email notification. Rename the "email" command to "alerts". Try to avoid sending alerts about unapproved events, except it is ok to send such alerts to moderators. This is a work in progress.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | failed-fix
Files: files | file ages | folders
SHA3-256:efbd6caa7c9d2c1487d9631fbaeb88bfbcf0b9b62e4f12a5dae642045c2bc2c9
User & Date: drh 2018-08-06 19:15:57
Context
2018-08-06
19:39
Rearrange some of the subcommands on "fossil alerts" for better UX. Fix a problem in the /webmail display for messages with no subject. Improved comments. check-in: 81c25431 user: drh tags: failed-fix
19:15
Improvements to email notification. Rename the "email" command to "alerts". Try to avoid sending alerts about unapproved events, except it is ok to send such alerts to moderators. This is a work in progress. check-in: efbd6caa user: drh tags: failed-fix
12:51
Merge enhancements from trunk. check-in: ce3d5718 user: drh tags: failed-fix
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/email.c.

75
76
77
78
79
80
81
82
83

84
85
86
87
88
89
90
...
113
114
115
116
117
118
119





120
121
122
123
124
125
126
...
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
...
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
...
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
...
855
856
857
858
859
860
861



862
863
864
865
866
867
868
...
876
877
878
879
880
881
882
883
884
885
886
887

888
889

890
891
892
893
894
895
896
897
898
899
900
901
902
903
...
977
978
979
980
981
982
983























984
985
986
987
988
989
990
....
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
....
1787
1788
1789
1790
1791
1792
1793
1794

1795
1796
1797
1798
1799
1800
1801
....
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839

1840
1841
1842
1843
1844
1845
1846
....
1851
1852
1853
1854
1855
1856
1857

1858
1859
1860

1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871






1872
1873
1874
1875
1876
1877
1878
....
1903
1904
1905
1906
1907
1908
1909





1910
1911
1912
1913


1914
1915


1916
1917
1918
1919
1920
1921

1922

1923
1924
1925
1926

1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
....
1999
2000
2001
2002
2003
2004
2005

2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021

2022


2023
2024

2025
2026
2027


2028




2029
2030

2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
....
2049
2050
2051
2052
2053
2054
2055










2056
2057

2058


2059
2060
2061
2062
2063
2064
2065
....
2087
2088
2089
2090
2091
2092
2093

2094



2095

2096





2097
2098
2099
2100
2101
2102
2103
2104
2105
@ --
@ -- 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;
@ 
@ DROP TABLE IF EXISTS repository.email_bounce;
@ -- Record bounced emails.  If too many bounces are received within
@ -- some defined time range, then cancel the subscription.  Older
@ -- entries are periodically purged.
@ --
................................................................................
    if( bOnlyIfEnabled
     && fossil_strcmp(db_get("email-send-method","off"),"off")==0
    ){
      return;  /* Don't create table for disabled email */
    }
    db_multi_exec(zEmailInit/*works-like:""*/);
    email_triggers_enable();





  }
}

/*
** Enable triggers that automatically populate the pending_alert
** table.
*/
................................................................................
  entry_attribute("Administrator email address", 40, "email-admin",
                   "eadmin", "", 0);
  @ <p>This is the email for the human administrator for the system.
  @ Abuse and trouble reports are send here.
  @ (Property: "email-admin")</p>
  @ <hr>

  entry_attribute("Inbound email directory", 40, "email-receive-dir",
                   "erdir", "", 0);
  @ <p>Inbound emails can be stored in a directory for analysis as
  @ a debugging aid.  Put the name of that directory in this entry box.
  @ Disable saving of inbound email by making this an empty string.
  @ Abuse and trouble reports are send here.
  @ (Property: "email-receive-dir")</p>
  @ <hr>
  @ <p><input type="submit"  name="submit" value="Apply Changes" /></p>
  @ </div></form>
  db_end_transaction(0);
  style_footer();
}

#if 0
................................................................................
    email_header_to_free(nTo, azTo);
    blob_add_final_newline(&all);
    fossil_print("%s", blob_str(&all));
  }
  blob_reset(&all);
}

/*
** Analyze and act on a received email.
**
** This routine takes ownership of the Blob parameter and is responsible
** for freeing that blob when it is done with it.
**
** This routine acts on all email messages received from the
** "fossil email inbound" command.
*/
void email_receive(Blob *pMsg){
  /* To Do:  Look for bounce messages and possibly disable subscriptions */
  blob_reset(pMsg);
}

/*
** SETTING: email-send-method         width=5 default=off
** Determine the method used to send email.  Allowed values are
** "off", "relay", "pipe", "dir", "db", and "stdout".  The "off" value
** means no email is ever sent.  The "relay" value means emails are sent
** to an Mail Sending Agent using SMTP located at email-send-relayhost.
** The "pipe" value means email messages are piped into a command 
................................................................................
** if the email-send-method is set to "db".
*/
/*
** SETTING: email-self               width=40
** This is the email address for the repository.  Outbound emails add
** this email address as the "From:" field.
*/
/*
** SETTING: email-receive-dir         width=40
** Inbound email messages are saved as separate files in this directory,
** for debugging analysis.  Disable saving of inbound emails omitting
** this setting, or making it an empty string.
*/
/*
** SETTING: email-send-relayhost      width=40
** This is the hostname and TCP port to which output email messages
** are sent when email-send-method is "relay".  There should be an
** SMTP server configured as a Mail Submission Agent listening on the
** designated host and port and all times.
*/


/*
** COMMAND: email
** 
** Usage: %fossil email SUBCOMMAND ARGS...
**
** Subcommands:
**
**    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
**                            information.  Use with extreme care.
**
**    send TO [OPTIONS]       Send a single email message using whatever
**                            email sending mechanism is currently configured.
................................................................................
**                              --body FILENAME
**                              --smtp-trace
**                              --stdout
**                              --subject|-S SUBJECT
**
**    settings [NAME VALUE]   With no arguments, list all email settings.
**                            Or change the value of a single email setting.



**
**    subscribers [PATTERN]   List all subscribers matching PATTERN.
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
void email_cmd(void){
  const char *zCmd;
................................................................................
    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();
    if( g.argc!=3 && g.argc!=4 ){

      usage("inbound [FILE]");
    }

    blob_read_from_file(&email, g.argc==3 ? "-" : g.argv[3], ExtFILE);
    if( zInboundDir[0] ){
      char *zFN = file_time_tempname(zInboundDir,".email");
      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';
................................................................................
    }
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
  }else























  if( strncmp(zCmd, "subscribers", nCmd)==0 ){
    Stmt q;
    verify_all_options();
    if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
    if( g.argc==4 ){
      char *zPattern = g.argv[3];
      db_prepare(&q,
................................................................................
  if( strncmp(zCmd, "unsubscribe", nCmd)==0 ){
    verify_all_options();
    if( g.argc!=4 ) usage("unsubscribe EMAIL");
    db_multi_exec(
      "DELETE FROM subscriber WHERE semail=%Q", g.argv[3]);
  }else
  {
    usage("exec|inbound|reset|send|setting|subscribers|unsubscribe");
  }
}

/*
** Do error checking on a submitted subscription form.  Return TRUE
** if the submission is valid.  Return false if any problems are seen.
*/
................................................................................

#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', 'f' */

  Blob txt;          /* Text description to appear in an alert */
  EmailEvent *pNext; /* Next in chronological order */
};
#endif

/*
** Free a linked list of EmailEvent objects
................................................................................
}

/*
** 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"
................................................................................
  *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;
}

/*
................................................................................
** 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(0);
  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]);

    }
  }
  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));
................................................................................
  const char *zFrom;
  const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
  EmailSender *pSender = 0;
  u32 senderFlags = 0;

  if( g.fSqlTrace ) fossil_trace("-- BEGIN email_send_alerts(%u)\n", flags);
  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;
  zFrom = db_get("email-self",0);
  if( zFrom==0 ) goto send_alerts_done;
  if( flags & SENDALERT_TRACE ){
    senderFlags |= EMAIL_TRACE;
  }
  pSender = email_sender_new(zDest, senderFlags);
  db_multi_exec(
    "DROP TABLE IF EXISTS temp.wantalert;"
    "CREATE TEMP TABLE wantalert(eventId TEXT);"
  );
  if( flags & SENDALERT_DIGEST ){

    db_multi_exec(


      "INSERT INTO wantalert SELECT eventid FROM pending_alert"
      "  WHERE sentDigest IS FALSE"

    );
    zDigest = "true";
  }else{


    db_multi_exec(




      "INSERT INTO wantalert SELECT eventid FROM pending_alert"
      "  WHERE sentSep IS FALSE"

    );
  }
  pEvents = email_compute_event_text(&nEvent);
  if( nEvent==0 ) goto send_alerts_done;
  blob_init(&hdr, 0, 0);
  blob_init(&body, 0, 0);
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
................................................................................
    const char *zCode = db_column_text(&q, 0);
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    const char *zCap = db_column_text(&q, 3);
    int nHit = 0;
    for(p=pEvents; p; p=p->pNext){
      if( strchr(zSub,p->type)==0 ) continue;










      if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
        /* Setup and admin users can get any notification */

      }else{


        char xType = '*';
        switch( p->type ){
          case 'c':  xType = 'o';  break;
          case 'f':  xType = '2';  break;
          case 't':  xType = 'r';  break;
          case 'w':  xType = 'j';  break;
        }
................................................................................
  }
  blob_reset(&hdr);
  blob_reset(&body);
  db_finalize(&q);
  email_free_eventlist(pEvents);
  if( (flags & SENDALERT_PRESERVE)==0 ){
    if( flags & SENDALERT_DIGEST ){

      db_multi_exec("UPDATE pending_alert SET sentDigest=true");



    }else{

      db_multi_exec("UPDATE pending_alert SET sentSep=true");





    }
    db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep");
  }
send_alerts_done:
  email_sender_free(pSender);
  if( g.fSqlTrace ) fossil_trace("-- END email_send_alerts(%u)\n", flags);
  db_end_transaction(0);
}








|
|
>







 







>
>
>
>
>







 







<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<










|

|












|
<
<







 







>
>
>







 







|
|
<

|
>
|
<
>
|
|
|
|
|

|







 







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







 







|







 







|
>







 







|

|








|
|







|

|
>







 







>



>











>
>
>
>
>
>







 







>
>
>
>
>




>
>


>
>




|

>
|
>



|
>




|







 







>













|


>

>
>
|

>

<

>
>

>
>
>
>
|
|
>


|







 







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

>
>







 







>
|
>
>
>

>
|
>
>
>
>
>

<







75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
...
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
...
298
299
300
301
302
303
304








305
306
307
308
309
310
311
...
753
754
755
756
757
758
759














760
761
762
763
764
765
766
...
788
789
790
791
792
793
794






795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820


821
822
823
824
825
826
827
...
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
...
855
856
857
858
859
860
861
862
863

864
865
866
867

868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
...
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
....
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
....
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
....
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
....
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
....
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
....
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054

2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
....
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
....
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155

2156
2157
2158
2159
2160
2161
2162
@ --
@ -- 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 alert sent
@   sentDigest BOOLEAN DEFAULT false, -- digest alert sent
@   sentMod BOOLEAN DEFAULT false     -- pending moderation alert sent
@ ) WITHOUT ROWID;
@ 
@ DROP TABLE IF EXISTS repository.email_bounce;
@ -- Record bounced emails.  If too many bounces are received within
@ -- some defined time range, then cancel the subscription.  Older
@ -- entries are periodically purged.
@ --
................................................................................
    if( bOnlyIfEnabled
     && fossil_strcmp(db_get("email-send-method","off"),"off")==0
    ){
      return;  /* Don't create table for disabled email */
    }
    db_multi_exec(zEmailInit/*works-like:""*/);
    email_triggers_enable();
  }else if( !db_table_has_column("repository","pending_alert","sentMod") ){
    db_multi_exec(
      "ALTER TABLE repository.pending_alert"
      " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
    );
  }
}

/*
** Enable triggers that automatically populate the pending_alert
** table.
*/
................................................................................
  entry_attribute("Administrator email address", 40, "email-admin",
                   "eadmin", "", 0);
  @ <p>This is the email for the human administrator for the system.
  @ Abuse and trouble reports are send here.
  @ (Property: "email-admin")</p>
  @ <hr>









  @ <p><input type="submit"  name="submit" value="Apply Changes" /></p>
  @ </div></form>
  db_end_transaction(0);
  style_footer();
}

#if 0
................................................................................
    email_header_to_free(nTo, azTo);
    blob_add_final_newline(&all);
    fossil_print("%s", blob_str(&all));
  }
  blob_reset(&all);
}















/*
** SETTING: email-send-method         width=5 default=off
** Determine the method used to send email.  Allowed values are
** "off", "relay", "pipe", "dir", "db", and "stdout".  The "off" value
** means no email is ever sent.  The "relay" value means emails are sent
** to an Mail Sending Agent using SMTP located at email-send-relayhost.
** The "pipe" value means email messages are piped into a command 
................................................................................
** if the email-send-method is set to "db".
*/
/*
** SETTING: email-self               width=40
** This is the email address for the repository.  Outbound emails add
** this email address as the "From:" field.
*/






/*
** SETTING: email-send-relayhost      width=40
** This is the hostname and TCP port to which output email messages
** are sent when email-send-method is "relay".  There should be an
** SMTP server configured as a Mail Submission Agent listening on the
** designated host and port and all times.
*/


/*
** COMMAND: alerts
** 
** Usage: %fossil alerts SUBCOMMAND ARGS...
**
** Subcommands:
**
**    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
**
**    pending                 Show all pending alerts.  Useful for debugging.


**
**    reset                   Hard reset of all email notification tables
**                            in the repository.  This erases all subscription
**                            information.  Use with extreme care.
**
**    send TO [OPTIONS]       Send a single email message using whatever
**                            email sending mechanism is currently configured.
................................................................................
**                              --body FILENAME
**                              --smtp-trace
**                              --stdout
**                              --subject|-S SUBJECT
**
**    settings [NAME VALUE]   With no arguments, list all email settings.
**                            Or change the value of a single email setting.
**
**    status                  Report on the status of the email alert
**                            subsystem
**
**    subscribers [PATTERN]   List all subscribers matching PATTERN.
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
void email_cmd(void){
  const char *zCmd;
................................................................................
    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, "pending", nCmd)==0 ){
    Stmt q;

    verify_all_options();
    if( g.argc!=3 ) usage("pending");
    db_prepare(&q,"SELECT eventid, sentSep, sentDigest, sentMod"
                  "  FROM pending_alert");

    while( db_step(&q)==SQLITE_ROW ){
      fossil_print("%10s %7s %10s %7s\n",
         db_column_text(&q,0),
         db_column_int(&q,1) ? "sentSep" : "",
         db_column_int(&q,2) ? "sentDigest" : "",
         db_column_int(&q,3) ? "sentMod" : "");
    }
    db_finalize(&q);
  }else
  if( strncmp(zCmd, "reset", nCmd)==0 ){
    int c;
    int bForce = find_option("force","f",0)!=0;
    verify_all_options();
    if( bForce ){
      c = 'y';
................................................................................
    }
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
  }else
  if( strncmp(zCmd, "status", nCmd)==0 ){
    int isGlobal = find_option("global",0,0)!=0;
    int nSetting, n;
    static const char *zFmt = "%-29s %d\n";
    const Setting *pSetting = setting_info(&nSetting);
    db_open_config(1, 0);
    verify_all_options();
    if( g.argc!=3 ) usage("status");
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
    n = db_int(0,"SELECT count(*) FROM subscriber");
    fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
    n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
                   " AND NOT sdonotcall AND length(ssub)>1");
    fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
  }else
  if( strncmp(zCmd, "subscribers", nCmd)==0 ){
    Stmt q;
    verify_all_options();
    if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
    if( g.argc==4 ){
      char *zPattern = g.argv[3];
      db_prepare(&q,
................................................................................
  if( strncmp(zCmd, "unsubscribe", nCmd)==0 ){
    verify_all_options();
    if( g.argc!=4 ) usage("unsubscribe EMAIL");
    db_multi_exec(
      "DELETE FROM subscriber WHERE semail=%Q", g.argv[3]);
  }else
  {
    usage("exec|pending|reset|send|setting|status|subscribers|unsubscribe");
  }
}

/*
** Do error checking on a submitted subscription form.  Return TRUE
** if the submission is valid.  Return false if any problems are seen.
*/
................................................................................

#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', 'f', 'm', 't', 'w' */
  int needMod;       /* Pending moderator approval */
  Blob txt;          /* Text description to appear in an alert */
  EmailEvent *pNext; /* Next in chronological order */
};
#endif

/*
** Free a linked list of EmailEvent objects
................................................................................
}

/*
** 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, needMod BOOLEAN);
*/
EmailEvent *email_compute_event_text(int *pnEvent, int doDigest){
  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 */
    " wantalert.needMod"         /* 5 */
    " 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"
................................................................................
  *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->needMod = db_column_int(&q, 5);
    p->pNext = 0;
    switch( p->type ){
      case 'c':  zType = "Check-In";        break;
      case 'f':  zType = "Forum post";      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)
    );
    if( p->needMod ){
      blob_appendf(&p->txt,
        "** Pending moderator approval (%s/modreq) **\n",
        zUrl
      );
    }
    (*pnEvent)++;
  }
  db_finalize(&q);
  return anchor.pNext;
}

/*
................................................................................
** 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.
**
** Options:
**
**      --digest           Generate digest alert text
**      --needmod          Assume all events are pending moderator approval
*/
void test_alert_cmd(void){
  Blob out;
  int nEvent;
  int needMod;
  int doDigest;
  EmailEvent *pEvent, *p;

  doDigest = find_option("digest",0,0)!=0;
  needMod = find_option("needmod",0,0)!=0;
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema(0);
  db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT, needMod BOOLEAN)");
  if( g.argc==2 ){
    db_multi_exec(
      "INSERT INTO wantalert(eventId,needMod)"
      " SELECT eventid, %d FROM pending_alert", needMod);
  }else{
    int i;
    for(i=2; i<g.argc; i++){
      db_multi_exec("INSERT INTO wantalert(eventId,needMod) VALUES(%Q,%d)",
           g.argv[i], needMod);
    }
  }
  blob_init(&out, 0, 0);
  email_header(&out);
  pEvent = email_compute_event_text(&nEvent, doDigest);
  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));
................................................................................
  const char *zFrom;
  const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
  EmailSender *pSender = 0;
  u32 senderFlags = 0;

  if( g.fSqlTrace ) fossil_trace("-- BEGIN email_send_alerts(%u)\n", flags);
  db_begin_transaction();
  email_schema(0);
  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;
  zFrom = db_get("email-self",0);
  if( zFrom==0 ) goto send_alerts_done;
  if( flags & SENDALERT_TRACE ){
    senderFlags |= EMAIL_TRACE;
  }
  pSender = email_sender_new(zDest, senderFlags);
  db_multi_exec(
    "DROP TABLE IF EXISTS temp.wantalert;"
    "CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN, sentMod);"
  );
  if( flags & SENDALERT_DIGEST ){
    /* Unmoderated changes are never sent as part of a digest */
    db_multi_exec(
      "INSERT INTO wantalert(eventId,needMod)"
      " SELECT eventid, 0"
      "   FROM pending_alert"
      "  WHERE sentDigest IS FALSE"
      "    AND NOT EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2));"
    );

  }else{
    /* Immediate alerts might include events that are subject to
    ** moderator approval */
    db_multi_exec(
      "INSERT INTO wantalert(eventId,needMod,sentMod)"
      " SELECT eventid,"
      "        EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2)),"
      "        sentMod"
      "   FROM pending_alert"
      "  WHERE sentSep IS FALSE;"
      "DELETE FROM wantalert WHERE needMod AND sentMod;"
    );
  }
  pEvents = email_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
  if( nEvent==0 ) goto send_alerts_done;
  blob_init(&hdr, 0, 0);
  blob_init(&body, 0, 0);
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
................................................................................
    const char *zCode = db_column_text(&q, 0);
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    const char *zCap = db_column_text(&q, 3);
    int nHit = 0;
    for(p=pEvents; p; p=p->pNext){
      if( strchr(zSub,p->type)==0 ) continue;
      if( p->needMod ){
        /* For events that require moderator approval, only send an alert
        ** if the recipient is a moderator for that type of event */
        char xType = '*';
        switch( p->type ){
          case 'f':  xType = '5';  break;
          case 't':  xType = 'q';  break;
          case 'w':  xType = 'l';  break;
        }
        if( strchr(zCap,xType)==0 ) continue;
      }else if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
        /* Setup and admin users can get any notification that does not
        ** require moderation */
      }else{
        /* Other users only see the alert if they have sufficient
        ** privilege to view the event itself */
        char xType = '*';
        switch( p->type ){
          case 'c':  xType = 'o';  break;
          case 'f':  xType = '2';  break;
          case 't':  xType = 'r';  break;
          case 'w':  xType = 'j';  break;
        }
................................................................................
  }
  blob_reset(&hdr);
  blob_reset(&body);
  db_finalize(&q);
  email_free_eventlist(pEvents);
  if( (flags & SENDALERT_PRESERVE)==0 ){
    if( flags & SENDALERT_DIGEST ){
      db_multi_exec(
        "UPDATE pending_alert SET sentDigest=true"
        " WHERE eventid IN (SELECT eventid FROM wantalert);"
        "DELETE FROM pending_alert WHERE sentDigest AND sentSep;"
      );
    }else{
      db_multi_exec(
        "UPDATE pending_alert SET sentSep=true"
        " WHERE eventid IN (SELECT eventid FROM wantalert WHERE NOT needMod);"
        "UPDATE pending_alert SET sentMod=true"
        " WHERE eventid IN (SELECT eventid FROM wantalert WHERE needMod);"
        "DELETE FROM pending_alert WHERE sentDigest AND sentSep;"
      );
    }

  }
send_alerts_done:
  email_sender_free(pSender);
  if( g.fSqlTrace ) fossil_trace("-- END email_send_alerts(%u)\n", flags);
  db_end_transaction(0);
}