Fossil SCM

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.

drh 2018-08-06 19:15 UTC failed-fix
Commit efbd6caa7c9d2c1487d9631fbaeb88bfbcf0b9b62e4f12a5dae642045c2bc2c9
Parent ce3d5718370a7e5…
1 file changed +128 -71
~ src/email.c
M src/email.c
+128 -71
--- src/email.c
+++ src/email.c
@@ -77,12 +77,13 @@
7777
 @ -- Remaining characters determine the specific event. For example,
7878
 @ -- 'c4413' means check-in with rid=4413.
7979
 @ --
8080
 @ CREATE TABLE repository.pending_alert(
8181
 @ eventid TEXT PRIMARY KEY, -- Object that changed
82
-@ sentSep BOOLEAN DEFAULT false, -- individual emails sent
83
-@ sentDigest BOOLEAN DEFAULT false -- digest emails sent
82
+@ sentSep BOOLEAN DEFAULT false, -- individual alert sent
83
+@ sentDigest BOOLEAN DEFAULT false, -- digest alert sent
84
+@ sentMod BOOLEAN DEFAULT false -- pending moderation alert sent
8485
 @ ) WITHOUT ROWID;
8586
 @
8687
 @ DROP TABLE IF EXISTS repository.email_bounce;
8788
 @ -- Record bounced emails. If too many bounces are received within
8889
 @ -- some defined time range, then cancel the subscription. Older
@@ -115,10 +116,15 @@
115116
 ){
116117
 return; /* Don't create table for disabled email */
117118
 }
118119
 db_multi_exec(zEmailInit/*works-like:""*/);
119120
 email_triggers_enable();
121
+ }else if( !db_table_has_column("repository","pending_alert","sentMod") ){
122
+ db_multi_exec(
123
+ "ALTER TABLE repository.pending_alert"
124
+ " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
125
+ );
120126
 }
121127
 }
122128
123129
 /*
124130
 ** Enable triggers that automatically populate the pending_alert
@@ -294,18 +300,10 @@
294300
 @ <p>This is the email for the human administrator for the system.
295301
 @ Abuse and trouble reports are send here.
296302
 @ (Property: "email-admin")</p>
297303
 @ <hr>
298304
299
- entry_attribute("Inbound email directory", 40, "email-receive-dir",
300
- "erdir", "", 0);
301
- @ <p>Inbound emails can be stored in a directory for analysis as
302
- @ a debugging aid. Put the name of that directory in this entry box.
303
- @ Disable saving of inbound email by making this an empty string.
304
- @ Abuse and trouble reports are send here.
305
- @ (Property: "email-receive-dir")</p>
306
- @ <hr>
307305
 @ <p><input type="submit" name="submit" value="Apply Changes" /></p>
308306
 @ </div></form>
309307
 db_end_transaction(0);
310308
 style_footer();
311309
 }
@@ -757,24 +755,10 @@
757755
 fossil_print("%s", blob_str(&all));
758756
 }
759757
 blob_reset(&all);
760758
 }
761759
762
-/*
763
-** Analyze and act on a received email.
764
-**
765
-** This routine takes ownership of the Blob parameter and is responsible
766
-** for freeing that blob when it is done with it.
767
-**
768
-** This routine acts on all email messages received from the
769
-** "fossil email inbound" command.
770
-*/
771
-void email_receive(Blob *pMsg){
772
- /* To Do: Look for bounce messages and possibly disable subscriptions */
773
- blob_reset(pMsg);
774
-}
775
-
776760
 /*
777761
 ** SETTING: email-send-method width=5 default=off
778762
 ** Determine the method used to send email. Allowed values are
779763
 ** "off", "relay", "pipe", "dir", "db", and "stdout". The "off" value
780764
 ** means no email is ever sent. The "relay" value means emails are sent
@@ -806,16 +790,10 @@
806790
 /*
807791
 ** SETTING: email-self width=40
808792
 ** This is the email address for the repository. Outbound emails add
809793
 ** this email address as the "From:" field.
810794
 */
811
-/*
812
-** SETTING: email-receive-dir width=40
813
-** Inbound email messages are saved as separate files in this directory,
814
-** for debugging analysis. Disable saving of inbound emails omitting
815
-** this setting, or making it an empty string.
816
-*/
817795
 /*
818796
 ** SETTING: email-send-relayhost width=40
819797
 ** This is the hostname and TCP port to which output email messages
820798
 ** are sent when email-send-method is "relay". There should be an
821799
 ** SMTP server configured as a Mail Submission Agent listening on the
@@ -822,13 +800,13 @@
822800
 ** designated host and port and all times.
823801
 */
824802
825803
826804
 /*
827
-** COMMAND: email
805
+** COMMAND: alerts
828806
 **
829
-** Usage: %fossil email SUBCOMMAND ARGS...
807
+** Usage: %fossil alerts SUBCOMMAND ARGS...
830808
 **
831809
 ** Subcommands:
832810
 **
833811
 ** exec Compose and send pending email alerts.
834812
 ** Some installations may want to do this via
@@ -837,13 +815,11 @@
837815
 ** Options:
838816
 **
839817
 ** --digest Send digests
840818
 ** --test Resets to standard output
841819
 **
842
-** inbound [FILE] Receive an inbound email message. This message
843
-** is analyzed to see if it is a bounce, and if
844
-** necessary, subscribers may be disabled.
820
+** pending Show all pending alerts. Useful for debugging.
845821
 **
846822
 ** reset Hard reset of all email notification tables
847823
 ** in the repository. This erases all subscription
848824
 ** information. Use with extreme care.
849825
 **
@@ -857,10 +833,13 @@
857833
 ** --stdout
858834
 ** --subject|-S SUBJECT
859835
 **
860836
 ** settings [NAME VALUE] With no arguments, list all email settings.
861837
 ** Or change the value of a single email setting.
838
+**
839
+** status Report on the status of the email alert
840
+** subsystem
862841
 **
863842
 ** subscribers [PATTERN] List all subscribers matching PATTERN.
864843
 **
865844
 ** unsubscribe EMAIL Remove a single subscriber with the given EMAIL.
866845
 */
@@ -878,24 +857,24 @@
878857
 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
879858
 }
880859
 verify_all_options();
881860
 email_send_alerts(eFlags);
882861
 }else
883
- if( strncmp(zCmd, "inbound", nCmd)==0 ){
884
- Blob email;
885
- const char *zInboundDir = db_get("email-receive-dir","");
862
+ if( strncmp(zCmd, "pending", nCmd)==0 ){
863
+ Stmt q;
886864
 verify_all_options();
887
- if( g.argc!=3 && g.argc!=4 ){
888
- usage("inbound [FILE]");
889
- }
890
- blob_read_from_file(&email, g.argc==3 ? "-" : g.argv[3], ExtFILE);
891
- if( zInboundDir[0] ){
892
- char *zFN = file_time_tempname(zInboundDir,".email");
893
- blob_write_to_file(&email, zFN);
894
- fossil_free(zFN);
895
- }
896
- email_receive(&email);
865
+ if( g.argc!=3 ) usage("pending");
866
+ db_prepare(&q,"SELECT eventid, sentSep, sentDigest, sentMod"
867
+ " FROM pending_alert");
868
+ while( db_step(&q)==SQLITE_ROW ){
869
+ fossil_print("%10s %7s %10s %7s\n",
870
+ db_column_text(&q,0),
871
+ db_column_int(&q,1) ? "sentSep" : "",
872
+ db_column_int(&q,2) ? "sentDigest" : "",
873
+ db_column_int(&q,3) ? "sentMod" : "");
874
+ }
875
+ db_finalize(&q);
897876
 }else
898877
 if( strncmp(zCmd, "reset", nCmd)==0 ){
899878
 int c;
900879
 int bForce = find_option("force","f",0)!=0;
901880
 verify_all_options();
@@ -979,10 +958,33 @@
979958
 for(; nSetting>0; nSetting--, pSetting++ ){
980959
 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
981960
 print_setting(pSetting);
982961
 }
983962
 }else
963
+ if( strncmp(zCmd, "status", nCmd)==0 ){
964
+ int isGlobal = find_option("global",0,0)!=0;
965
+ int nSetting, n;
966
+ static const char *zFmt = "%-29s %d\n";
967
+ const Setting *pSetting = setting_info(&nSetting);
968
+ db_open_config(1, 0);
969
+ verify_all_options();
970
+ if( g.argc!=3 ) usage("status");
971
+ pSetting = setting_info(&nSetting);
972
+ for(; nSetting>0; nSetting--, pSetting++ ){
973
+ if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
974
+ print_setting(pSetting);
975
+ }
976
+ n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
977
+ fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
978
+ n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
979
+ fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
980
+ n = db_int(0,"SELECT count(*) FROM subscriber");
981
+ fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
982
+ n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
983
+ " AND NOT sdonotcall AND length(ssub)>1");
984
+ fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
985
+ }else
984986
 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
985987
 Stmt q;
986988
 verify_all_options();
987989
 if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
988990
 if( g.argc==4 ){
@@ -1008,11 +1010,11 @@
10081010
 if( g.argc!=4 ) usage("unsubscribe EMAIL");
10091011
 db_multi_exec(
10101012
 "DELETE FROM subscriber WHERE semail=%Q", g.argv[3]);
10111013
 }else
10121014
 {
1013
- usage("exec|inbound|reset|send|setting|subscribers|unsubscribe");
1015
+ usage("exec|pending|reset|send|setting|status|subscribers|unsubscribe");
10141016
 }
10151017
 }
10161018
10171019
 /*
10181020
 ** Do error checking on a submitted subscription form. Return TRUE
@@ -1789,11 +1791,12 @@
17891791
 /*
17901792
 ** A single event that might appear in an alert is recorded as an
17911793
 ** instance of the following object.
17921794
 */
17931795
 struct EmailEvent {
1794
- int type; /* 'c', 't', 'w', 'f' */
1796
+ int type; /* 'c', 'f', 'm', 't', 'w' */
1797
+ int needMod; /* Pending moderator approval */
17951798
 Blob txt; /* Text description to appear in an alert */
17961799
 EmailEvent *pNext; /* Next in chronological order */
17971800
 };
17981801
 #endif
17991802
@@ -1812,33 +1815,34 @@
18121815
 /*
18131816
 ** Compute and return a linked list of EmailEvent objects
18141817
 ** corresponding to the current content of the temp.wantalert
18151818
 ** table which should be defined as follows:
18161819
 **
1817
-** CREATE TEMP TABLE wantalert(eventId TEXT);
1820
+** CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN);
18181821
 */
1819
-EmailEvent *email_compute_event_text(int *pnEvent){
1822
+EmailEvent *email_compute_event_text(int *pnEvent, int doDigest){
18201823
 Stmt q;
18211824
 EmailEvent *p;
18221825
 EmailEvent anchor;
18231826
 EmailEvent *pLast;
18241827
 const char *zUrl = db_get("email-url","http://localhost:8080");
18251828
18261829
 db_prepare(&q,
18271830
 "SELECT"
1828
- " blob.uuid," /* 0 */
1829
- " datetime(event.mtime)," /* 1 */
1831
+ " blob.uuid," /* 0 */
1832
+ " datetime(event.mtime)," /* 1 */
18301833
 " coalesce(ecomment,comment)"
18311834
 " || ' (user: ' || coalesce(euser,user,'?')"
18321835
 " || (SELECT case when length(x)>0 then ' tags: ' || x else '' end"
18331836
 " FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
18341837
 " FROM tag, tagxref"
18351838
 " WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
18361839
 " AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
1837
- " || ')' as comment," /* 2 */
1840
+ " || ')' as comment," /* 2 */
18381841
 " tagxref.value AS branch," /* 3 */
1839
- " wantalert.eventId" /* 4 */
1842
+ " wantalert.eventId," /* 4 */
1843
+ " wantalert.needMod" /* 5 */
18401844
 " FROM temp.wantalert JOIN tag CROSS JOIN event CROSS JOIN blob"
18411845
 " LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
18421846
 " AND tagxref.tagtype>0"
18431847
 " AND tagxref.rid=blob.rid"
18441848
 " WHERE blob.rid=event.objid"
@@ -1853,13 +1857,15 @@
18531857
 const char *zType = "";
18541858
 p = fossil_malloc( sizeof(EmailEvent) );
18551859
 pLast->pNext = p;
18561860
 pLast = p;
18571861
 p->type = db_column_text(&q, 4)[0];
1862
+ p->needMod = db_column_int(&q, 5);
18581863
 p->pNext = 0;
18591864
 switch( p->type ){
18601865
 case 'c': zType = "Check-In"; break;
1866
+ case 'f': zType = "Forum post"; break;
18611867
 case 't': zType = "Wiki Edit"; break;
18621868
 case 'w': zType = "Ticket Change"; break;
18631869
 }
18641870
 blob_init(&p->txt, 0, 0);
18651871
 blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
@@ -1867,10 +1873,16 @@
18671873
 zType,
18681874
 db_column_text(&q,2),
18691875
 zUrl,
18701876
 db_column_text(&q,0)
18711877
 );
1878
+ if( p->needMod ){
1879
+ blob_appendf(&p->txt,
1880
+ "** Pending moderator approval (%s/modreq) **\n",
1881
+ zUrl
1882
+ );
1883
+ }
18721884
 (*pnEvent)++;
18731885
 }
18741886
 db_finalize(&q);
18751887
 return anchor.pNext;
18761888
 }
@@ -1905,32 +1917,44 @@
19051917
 ** command line, generate text for all events named in the
19061918
 ** pending_alert table.
19071919
 **
19081920
 ** This command is intended for testing and debugging the logic
19091921
 ** that generates email alert text.
1922
+**
1923
+** Options:
1924
+**
1925
+** --digest Generate digest alert text
1926
+** --needmod Assume all events are pending moderator approval
19101927
 */
19111928
 void test_alert_cmd(void){
19121929
 Blob out;
19131930
 int nEvent;
1931
+ int needMod;
1932
+ int doDigest;
19141933
 EmailEvent *pEvent, *p;
19151934
1935
+ doDigest = find_option("digest",0,0)!=0;
1936
+ needMod = find_option("needmod",0,0)!=0;
19161937
 db_find_and_open_repository(0, 0);
19171938
 verify_all_options();
19181939
 db_begin_transaction();
19191940
 email_schema(0);
1920
- db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
1941
+ db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT, needMod BOOLEAN)");
19211942
 if( g.argc==2 ){
1922
- db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");
1943
+ db_multi_exec(
1944
+ "INSERT INTO wantalert(eventId,needMod)"
1945
+ " SELECT eventid, %d FROM pending_alert", needMod);
19231946
 }else{
19241947
 int i;
19251948
 for(i=2; i<g.argc; i++){
1926
- db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);
1949
+ db_multi_exec("INSERT INTO wantalert(eventId,needMod) VALUES(%Q,%d)",
1950
+ g.argv[i], needMod);
19271951
 }
19281952
 }
19291953
 blob_init(&out, 0, 0);
19301954
 email_header(&out);
1931
- pEvent = email_compute_event_text(&nEvent);
1955
+ pEvent = email_compute_event_text(&nEvent, doDigest);
19321956
 for(p=pEvent; p; p=p->pNext){
19331957
 blob_append(&out, "\n", 1);
19341958
 blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
19351959
 }
19361960
 email_free_eventlist(pEvent);
@@ -2001,10 +2025,11 @@
20012025
 EmailSender *pSender = 0;
20022026
 u32 senderFlags = 0;
20032027
20042028
 if( g.fSqlTrace ) fossil_trace("-- BEGIN email_send_alerts(%u)\n", flags);
20052029
 db_begin_transaction();
2030
+ email_schema(0);
20062031
 if( !email_enabled() ) goto send_alerts_done;
20072032
 zUrl = db_get("email-url",0);
20082033
 if( zUrl==0 ) goto send_alerts_done;
20092034
 zRepoName = db_get("email-subname",0);
20102035
 if( zRepoName==0 ) goto send_alerts_done;
@@ -2014,25 +2039,35 @@
20142039
 senderFlags |= EMAIL_TRACE;
20152040
 }
20162041
 pSender = email_sender_new(zDest, senderFlags);
20172042
 db_multi_exec(
20182043
 "DROP TABLE IF EXISTS temp.wantalert;"
2019
- "CREATE TEMP TABLE wantalert(eventId TEXT);"
2044
+ "CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN, sentMod);"
20202045
 );
20212046
 if( flags & SENDALERT_DIGEST ){
2047
+ /* Unmoderated changes are never sent as part of a digest */
20222048
 db_multi_exec(
2023
- "INSERT INTO wantalert SELECT eventid FROM pending_alert"
2049
+ "INSERT INTO wantalert(eventId,needMod)"
2050
+ " SELECT eventid, 0"
2051
+ " FROM pending_alert"
20242052
 " WHERE sentDigest IS FALSE"
2053
+ " AND NOT EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2));"
20252054
 );
2026
- zDigest = "true";
20272055
 }else{
2056
+ /* Immediate alerts might include events that are subject to
2057
+ ** moderator approval */
20282058
 db_multi_exec(
2029
- "INSERT INTO wantalert SELECT eventid FROM pending_alert"
2030
- " WHERE sentSep IS FALSE"
2059
+ "INSERT INTO wantalert(eventId,needMod,sentMod)"
2060
+ " SELECT eventid,"
2061
+ " EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2)),"
2062
+ " sentMod"
2063
+ " FROM pending_alert"
2064
+ " WHERE sentSep IS FALSE;"
2065
+ "DELETE FROM wantalert WHERE needMod AND sentMod;"
20312066
 );
20322067
 }
2033
- pEvents = email_compute_event_text(&nEvent);
2068
+ pEvents = email_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
20342069
 if( nEvent==0 ) goto send_alerts_done;
20352070
 blob_init(&hdr, 0, 0);
20362071
 blob_init(&body, 0, 0);
20372072
 db_prepare(&q,
20382073
 "SELECT"
@@ -2051,13 +2086,26 @@
20512086
 const char *zEmail = db_column_text(&q, 1);
20522087
 const char *zCap = db_column_text(&q, 3);
20532088
 int nHit = 0;
20542089
 for(p=pEvents; p; p=p->pNext){
20552090
 if( strchr(zSub,p->type)==0 ) continue;
2056
- if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
2057
- /* Setup and admin users can get any notification */
2091
+ if( p->needMod ){
2092
+ /* For events that require moderator approval, only send an alert
2093
+ ** if the recipient is a moderator for that type of event */
2094
+ char xType = '*';
2095
+ switch( p->type ){
2096
+ case 'f': xType = '5'; break;
2097
+ case 't': xType = 'q'; break;
2098
+ case 'w': xType = 'l'; break;
2099
+ }
2100
+ if( strchr(zCap,xType)==0 ) continue;
2101
+ }else if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
2102
+ /* Setup and admin users can get any notification that does not
2103
+ ** require moderation */
20582104
 }else{
2105
+ /* Other users only see the alert if they have sufficient
2106
+ ** privilege to view the event itself */
20592107
 char xType = '*';
20602108
 switch( p->type ){
20612109
 case 'c': xType = 'o'; break;
20622110
 case 'f': xType = '2'; break;
20632111
 case 't': xType = 'r'; break;
@@ -2089,15 +2137,24 @@
20892137
 blob_reset(&body);
20902138
 db_finalize(&q);
20912139
 email_free_eventlist(pEvents);
20922140
 if( (flags & SENDALERT_PRESERVE)==0 ){
20932141
 if( flags & SENDALERT_DIGEST ){
2094
- db_multi_exec("UPDATE pending_alert SET sentDigest=true");
2142
+ db_multi_exec(
2143
+ "UPDATE pending_alert SET sentDigest=true"
2144
+ " WHERE eventid IN (SELECT eventid FROM wantalert);"
2145
+ "DELETE FROM pending_alert WHERE sentDigest AND sentSep;"
2146
+ );
20952147
 }else{
2096
- db_multi_exec("UPDATE pending_alert SET sentSep=true");
2148
+ db_multi_exec(
2149
+ "UPDATE pending_alert SET sentSep=true"
2150
+ " WHERE eventid IN (SELECT eventid FROM wantalert WHERE NOT needMod);"
2151
+ "UPDATE pending_alert SET sentMod=true"
2152
+ " WHERE eventid IN (SELECT eventid FROM wantalert WHERE needMod);"
2153
+ "DELETE FROM pending_alert WHERE sentDigest AND sentSep;"
2154
+ );
20972155
 }
2098
- db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep");
20992156
 }
21002157
 send_alerts_done:
21012158
 email_sender_free(pSender);
21022159
 if( g.fSqlTrace ) fossil_trace("-- END email_send_alerts(%u)\n", flags);
21032160
 db_end_transaction(0);
21042161
--- src/email.c
+++ src/email.c
@@ -77,12 +77,13 @@
77 @ -- Remaining characters determine the specific event. For example,
78 @ -- 'c4413' means check-in with rid=4413.
79 @ --
80 @ CREATE TABLE repository.pending_alert(
81 @ eventid TEXT PRIMARY KEY, -- Object that changed
82 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
83 @ sentDigest BOOLEAN DEFAULT false -- digest emails sent
 
84 @ ) WITHOUT ROWID;
85 @
86 @ DROP TABLE IF EXISTS repository.email_bounce;
87 @ -- Record bounced emails. If too many bounces are received within
88 @ -- some defined time range, then cancel the subscription. Older
@@ -115,10 +116,15 @@
115 ){
116 return; /* Don't create table for disabled email */
117 }
118 db_multi_exec(zEmailInit/*works-like:""*/);
119 email_triggers_enable();
 
 
 
 
 
120 }
121 }
122
123 /*
124 ** Enable triggers that automatically populate the pending_alert
@@ -294,18 +300,10 @@
294 @ <p>This is the email for the human administrator for the system.
295 @ Abuse and trouble reports are send here.
296 @ (Property: "email-admin")</p>
297 @ <hr>
298
299 entry_attribute("Inbound email directory", 40, "email-receive-dir",
300 "erdir", "", 0);
301 @ <p>Inbound emails can be stored in a directory for analysis as
302 @ a debugging aid. Put the name of that directory in this entry box.
303 @ Disable saving of inbound email by making this an empty string.
304 @ Abuse and trouble reports are send here.
305 @ (Property: "email-receive-dir")</p>
306 @ <hr>
307 @ <p><input type="submit" name="submit" value="Apply Changes" /></p>
308 @ </div></form>
309 db_end_transaction(0);
310 style_footer();
311 }
@@ -757,24 +755,10 @@
757 fossil_print("%s", blob_str(&all));
758 }
759 blob_reset(&all);
760 }
761
762 /*
763 ** Analyze and act on a received email.
764 **
765 ** This routine takes ownership of the Blob parameter and is responsible
766 ** for freeing that blob when it is done with it.
767 **
768 ** This routine acts on all email messages received from the
769 ** "fossil email inbound" command.
770 */
771 void email_receive(Blob *pMsg){
772 /* To Do: Look for bounce messages and possibly disable subscriptions */
773 blob_reset(pMsg);
774 }
775
776 /*
777 ** SETTING: email-send-method width=5 default=off
778 ** Determine the method used to send email. Allowed values are
779 ** "off", "relay", "pipe", "dir", "db", and "stdout". The "off" value
780 ** means no email is ever sent. The "relay" value means emails are sent
@@ -806,16 +790,10 @@
806 /*
807 ** SETTING: email-self width=40
808 ** This is the email address for the repository. Outbound emails add
809 ** this email address as the "From:" field.
810 */
811 /*
812 ** SETTING: email-receive-dir width=40
813 ** Inbound email messages are saved as separate files in this directory,
814 ** for debugging analysis. Disable saving of inbound emails omitting
815 ** this setting, or making it an empty string.
816 */
817 /*
818 ** SETTING: email-send-relayhost width=40
819 ** This is the hostname and TCP port to which output email messages
820 ** are sent when email-send-method is "relay". There should be an
821 ** SMTP server configured as a Mail Submission Agent listening on the
@@ -822,13 +800,13 @@
822 ** designated host and port and all times.
823 */
824
825
826 /*
827 ** COMMAND: email
828 **
829 ** Usage: %fossil email SUBCOMMAND ARGS...
830 **
831 ** Subcommands:
832 **
833 ** exec Compose and send pending email alerts.
834 ** Some installations may want to do this via
@@ -837,13 +815,11 @@
837 ** Options:
838 **
839 ** --digest Send digests
840 ** --test Resets to standard output
841 **
842 ** inbound [FILE] Receive an inbound email message. This message
843 ** is analyzed to see if it is a bounce, and if
844 ** necessary, subscribers may be disabled.
845 **
846 ** reset Hard reset of all email notification tables
847 ** in the repository. This erases all subscription
848 ** information. Use with extreme care.
849 **
@@ -857,10 +833,13 @@
857 ** --stdout
858 ** --subject|-S SUBJECT
859 **
860 ** settings [NAME VALUE] With no arguments, list all email settings.
861 ** Or change the value of a single email setting.
 
 
 
862 **
863 ** subscribers [PATTERN] List all subscribers matching PATTERN.
864 **
865 ** unsubscribe EMAIL Remove a single subscriber with the given EMAIL.
866 */
@@ -878,24 +857,24 @@
878 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
879 }
880 verify_all_options();
881 email_send_alerts(eFlags);
882 }else
883 if( strncmp(zCmd, "inbound", nCmd)==0 ){
884 Blob email;
885 const char *zInboundDir = db_get("email-receive-dir","");
886 verify_all_options();
887 if( g.argc!=3 && g.argc!=4 ){
888 usage("inbound [FILE]");
889 }
890 blob_read_from_file(&email, g.argc==3 ? "-" : g.argv[3], ExtFILE);
891 if( zInboundDir[0] ){
892 char *zFN = file_time_tempname(zInboundDir,".email");
893 blob_write_to_file(&email, zFN);
894 fossil_free(zFN);
895 }
896 email_receive(&email);
 
897 }else
898 if( strncmp(zCmd, "reset", nCmd)==0 ){
899 int c;
900 int bForce = find_option("force","f",0)!=0;
901 verify_all_options();
@@ -979,10 +958,33 @@
979 for(; nSetting>0; nSetting--, pSetting++ ){
980 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
981 print_setting(pSetting);
982 }
983 }else
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
984 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
985 Stmt q;
986 verify_all_options();
987 if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
988 if( g.argc==4 ){
@@ -1008,11 +1010,11 @@
1008 if( g.argc!=4 ) usage("unsubscribe EMAIL");
1009 db_multi_exec(
1010 "DELETE FROM subscriber WHERE semail=%Q", g.argv[3]);
1011 }else
1012 {
1013 usage("exec|inbound|reset|send|setting|subscribers|unsubscribe");
1014 }
1015 }
1016
1017 /*
1018 ** Do error checking on a submitted subscription form. Return TRUE
@@ -1789,11 +1791,12 @@
1789 /*
1790 ** A single event that might appear in an alert is recorded as an
1791 ** instance of the following object.
1792 */
1793 struct EmailEvent {
1794 int type; /* 'c', 't', 'w', 'f' */
 
1795 Blob txt; /* Text description to appear in an alert */
1796 EmailEvent *pNext; /* Next in chronological order */
1797 };
1798 #endif
1799
@@ -1812,33 +1815,34 @@
1812 /*
1813 ** Compute and return a linked list of EmailEvent objects
1814 ** corresponding to the current content of the temp.wantalert
1815 ** table which should be defined as follows:
1816 **
1817 ** CREATE TEMP TABLE wantalert(eventId TEXT);
1818 */
1819 EmailEvent *email_compute_event_text(int *pnEvent){
1820 Stmt q;
1821 EmailEvent *p;
1822 EmailEvent anchor;
1823 EmailEvent *pLast;
1824 const char *zUrl = db_get("email-url","http://localhost:8080");
1825
1826 db_prepare(&q,
1827 "SELECT"
1828 " blob.uuid," /* 0 */
1829 " datetime(event.mtime)," /* 1 */
1830 " coalesce(ecomment,comment)"
1831 " || ' (user: ' || coalesce(euser,user,'?')"
1832 " || (SELECT case when length(x)>0 then ' tags: ' || x else '' end"
1833 " FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
1834 " FROM tag, tagxref"
1835 " WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
1836 " AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
1837 " || ')' as comment," /* 2 */
1838 " tagxref.value AS branch," /* 3 */
1839 " wantalert.eventId" /* 4 */
 
1840 " FROM temp.wantalert JOIN tag CROSS JOIN event CROSS JOIN blob"
1841 " LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
1842 " AND tagxref.tagtype>0"
1843 " AND tagxref.rid=blob.rid"
1844 " WHERE blob.rid=event.objid"
@@ -1853,13 +1857,15 @@
1853 const char *zType = "";
1854 p = fossil_malloc( sizeof(EmailEvent) );
1855 pLast->pNext = p;
1856 pLast = p;
1857 p->type = db_column_text(&q, 4)[0];
 
1858 p->pNext = 0;
1859 switch( p->type ){
1860 case 'c': zType = "Check-In"; break;
 
1861 case 't': zType = "Wiki Edit"; break;
1862 case 'w': zType = "Ticket Change"; break;
1863 }
1864 blob_init(&p->txt, 0, 0);
1865 blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
@@ -1867,10 +1873,16 @@
1867 zType,
1868 db_column_text(&q,2),
1869 zUrl,
1870 db_column_text(&q,0)
1871 );
 
 
 
 
 
 
1872 (*pnEvent)++;
1873 }
1874 db_finalize(&q);
1875 return anchor.pNext;
1876 }
@@ -1905,32 +1917,44 @@
1905 ** command line, generate text for all events named in the
1906 ** pending_alert table.
1907 **
1908 ** This command is intended for testing and debugging the logic
1909 ** that generates email alert text.
 
 
 
 
 
1910 */
1911 void test_alert_cmd(void){
1912 Blob out;
1913 int nEvent;
 
 
1914 EmailEvent *pEvent, *p;
1915
 
 
1916 db_find_and_open_repository(0, 0);
1917 verify_all_options();
1918 db_begin_transaction();
1919 email_schema(0);
1920 db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
1921 if( g.argc==2 ){
1922 db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");
 
 
1923 }else{
1924 int i;
1925 for(i=2; i<g.argc; i++){
1926 db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);
 
1927 }
1928 }
1929 blob_init(&out, 0, 0);
1930 email_header(&out);
1931 pEvent = email_compute_event_text(&nEvent);
1932 for(p=pEvent; p; p=p->pNext){
1933 blob_append(&out, "\n", 1);
1934 blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
1935 }
1936 email_free_eventlist(pEvent);
@@ -2001,10 +2025,11 @@
2001 EmailSender *pSender = 0;
2002 u32 senderFlags = 0;
2003
2004 if( g.fSqlTrace ) fossil_trace("-- BEGIN email_send_alerts(%u)\n", flags);
2005 db_begin_transaction();
 
2006 if( !email_enabled() ) goto send_alerts_done;
2007 zUrl = db_get("email-url",0);
2008 if( zUrl==0 ) goto send_alerts_done;
2009 zRepoName = db_get("email-subname",0);
2010 if( zRepoName==0 ) goto send_alerts_done;
@@ -2014,25 +2039,35 @@
2014 senderFlags |= EMAIL_TRACE;
2015 }
2016 pSender = email_sender_new(zDest, senderFlags);
2017 db_multi_exec(
2018 "DROP TABLE IF EXISTS temp.wantalert;"
2019 "CREATE TEMP TABLE wantalert(eventId TEXT);"
2020 );
2021 if( flags & SENDALERT_DIGEST ){
 
2022 db_multi_exec(
2023 "INSERT INTO wantalert SELECT eventid FROM pending_alert"
 
 
2024 " WHERE sentDigest IS FALSE"
 
2025 );
2026 zDigest = "true";
2027 }else{
 
 
2028 db_multi_exec(
2029 "INSERT INTO wantalert SELECT eventid FROM pending_alert"
2030 " WHERE sentSep IS FALSE"
 
 
 
 
 
2031 );
2032 }
2033 pEvents = email_compute_event_text(&nEvent);
2034 if( nEvent==0 ) goto send_alerts_done;
2035 blob_init(&hdr, 0, 0);
2036 blob_init(&body, 0, 0);
2037 db_prepare(&q,
2038 "SELECT"
@@ -2051,13 +2086,26 @@
2051 const char *zEmail = db_column_text(&q, 1);
2052 const char *zCap = db_column_text(&q, 3);
2053 int nHit = 0;
2054 for(p=pEvents; p; p=p->pNext){
2055 if( strchr(zSub,p->type)==0 ) continue;
2056 if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
2057 /* Setup and admin users can get any notification */
 
 
 
 
 
 
 
 
 
 
 
2058 }else{
 
 
2059 char xType = '*';
2060 switch( p->type ){
2061 case 'c': xType = 'o'; break;
2062 case 'f': xType = '2'; break;
2063 case 't': xType = 'r'; break;
@@ -2089,15 +2137,24 @@
2089 blob_reset(&body);
2090 db_finalize(&q);
2091 email_free_eventlist(pEvents);
2092 if( (flags & SENDALERT_PRESERVE)==0 ){
2093 if( flags & SENDALERT_DIGEST ){
2094 db_multi_exec("UPDATE pending_alert SET sentDigest=true");
 
 
 
 
2095 }else{
2096 db_multi_exec("UPDATE pending_alert SET sentSep=true");
 
 
 
 
 
 
2097 }
2098 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep");
2099 }
2100 send_alerts_done:
2101 email_sender_free(pSender);
2102 if( g.fSqlTrace ) fossil_trace("-- END email_send_alerts(%u)\n", flags);
2103 db_end_transaction(0);
2104
--- src/email.c
+++ src/email.c
@@ -77,12 +77,13 @@
77 @ -- Remaining characters determine the specific event. For example,
78 @ -- 'c4413' means check-in with rid=4413.
79 @ --
80 @ CREATE TABLE repository.pending_alert(
81 @ eventid TEXT PRIMARY KEY, -- Object that changed
82 @ sentSep BOOLEAN DEFAULT false, -- individual alert sent
83 @ sentDigest BOOLEAN DEFAULT false, -- digest alert sent
84 @ sentMod BOOLEAN DEFAULT false -- pending moderation alert sent
85 @ ) WITHOUT ROWID;
86 @
87 @ DROP TABLE IF EXISTS repository.email_bounce;
88 @ -- Record bounced emails. If too many bounces are received within
89 @ -- some defined time range, then cancel the subscription. Older
@@ -115,10 +116,15 @@
116 ){
117 return; /* Don't create table for disabled email */
118 }
119 db_multi_exec(zEmailInit/*works-like:""*/);
120 email_triggers_enable();
121 }else if( !db_table_has_column("repository","pending_alert","sentMod") ){
122 db_multi_exec(
123 "ALTER TABLE repository.pending_alert"
124 " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
125 );
126 }
127 }
128
129 /*
130 ** Enable triggers that automatically populate the pending_alert
@@ -294,18 +300,10 @@
300 @ <p>This is the email for the human administrator for the system.
301 @ Abuse and trouble reports are send here.
302 @ (Property: "email-admin")</p>
303 @ <hr>
304
 
 
 
 
 
 
 
 
305 @ <p><input type="submit" name="submit" value="Apply Changes" /></p>
306 @ </div></form>
307 db_end_transaction(0);
308 style_footer();
309 }
@@ -757,24 +755,10 @@
755 fossil_print("%s", blob_str(&all));
756 }
757 blob_reset(&all);
758 }
759
 
 
 
 
 
 
 
 
 
 
 
 
 
 
760 /*
761 ** SETTING: email-send-method width=5 default=off
762 ** Determine the method used to send email. Allowed values are
763 ** "off", "relay", "pipe", "dir", "db", and "stdout". The "off" value
764 ** means no email is ever sent. The "relay" value means emails are sent
@@ -806,16 +790,10 @@
790 /*
791 ** SETTING: email-self width=40
792 ** This is the email address for the repository. Outbound emails add
793 ** this email address as the "From:" field.
794 */
 
 
 
 
 
 
795 /*
796 ** SETTING: email-send-relayhost width=40
797 ** This is the hostname and TCP port to which output email messages
798 ** are sent when email-send-method is "relay". There should be an
799 ** SMTP server configured as a Mail Submission Agent listening on the
@@ -822,13 +800,13 @@
800 ** designated host and port and all times.
801 */
802
803
804 /*
805 ** COMMAND: alerts
806 **
807 ** Usage: %fossil alerts SUBCOMMAND ARGS...
808 **
809 ** Subcommands:
810 **
811 ** exec Compose and send pending email alerts.
812 ** Some installations may want to do this via
@@ -837,13 +815,11 @@
815 ** Options:
816 **
817 ** --digest Send digests
818 ** --test Resets to standard output
819 **
820 ** pending Show all pending alerts. Useful for debugging.
 
 
821 **
822 ** reset Hard reset of all email notification tables
823 ** in the repository. This erases all subscription
824 ** information. Use with extreme care.
825 **
@@ -857,10 +833,13 @@
833 ** --stdout
834 ** --subject|-S SUBJECT
835 **
836 ** settings [NAME VALUE] With no arguments, list all email settings.
837 ** Or change the value of a single email setting.
838 **
839 ** status Report on the status of the email alert
840 ** subsystem
841 **
842 ** subscribers [PATTERN] List all subscribers matching PATTERN.
843 **
844 ** unsubscribe EMAIL Remove a single subscriber with the given EMAIL.
845 */
@@ -878,24 +857,24 @@
857 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
858 }
859 verify_all_options();
860 email_send_alerts(eFlags);
861 }else
862 if( strncmp(zCmd, "pending", nCmd)==0 ){
863 Stmt q;
 
864 verify_all_options();
865 if( g.argc!=3 ) usage("pending");
866 db_prepare(&q,"SELECT eventid, sentSep, sentDigest, sentMod"
867 " FROM pending_alert");
868 while( db_step(&q)==SQLITE_ROW ){
869 fossil_print("%10s %7s %10s %7s\n",
870 db_column_text(&q,0),
871 db_column_int(&q,1) ? "sentSep" : "",
872 db_column_int(&q,2) ? "sentDigest" : "",
873 db_column_int(&q,3) ? "sentMod" : "");
874 }
875 db_finalize(&q);
876 }else
877 if( strncmp(zCmd, "reset", nCmd)==0 ){
878 int c;
879 int bForce = find_option("force","f",0)!=0;
880 verify_all_options();
@@ -979,10 +958,33 @@
958 for(; nSetting>0; nSetting--, pSetting++ ){
959 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
960 print_setting(pSetting);
961 }
962 }else
963 if( strncmp(zCmd, "status", nCmd)==0 ){
964 int isGlobal = find_option("global",0,0)!=0;
965 int nSetting, n;
966 static const char *zFmt = "%-29s %d\n";
967 const Setting *pSetting = setting_info(&nSetting);
968 db_open_config(1, 0);
969 verify_all_options();
970 if( g.argc!=3 ) usage("status");
971 pSetting = setting_info(&nSetting);
972 for(; nSetting>0; nSetting--, pSetting++ ){
973 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
974 print_setting(pSetting);
975 }
976 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
977 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
978 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
979 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
980 n = db_int(0,"SELECT count(*) FROM subscriber");
981 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
982 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
983 " AND NOT sdonotcall AND length(ssub)>1");
984 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
985 }else
986 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
987 Stmt q;
988 verify_all_options();
989 if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
990 if( g.argc==4 ){
@@ -1008,11 +1010,11 @@
1010 if( g.argc!=4 ) usage("unsubscribe EMAIL");
1011 db_multi_exec(
1012 "DELETE FROM subscriber WHERE semail=%Q", g.argv[3]);
1013 }else
1014 {
1015 usage("exec|pending|reset|send|setting|status|subscribers|unsubscribe");
1016 }
1017 }
1018
1019 /*
1020 ** Do error checking on a submitted subscription form. Return TRUE
@@ -1789,11 +1791,12 @@
1791 /*
1792 ** A single event that might appear in an alert is recorded as an
1793 ** instance of the following object.
1794 */
1795 struct EmailEvent {
1796 int type; /* 'c', 'f', 'm', 't', 'w' */
1797 int needMod; /* Pending moderator approval */
1798 Blob txt; /* Text description to appear in an alert */
1799 EmailEvent *pNext; /* Next in chronological order */
1800 };
1801 #endif
1802
@@ -1812,33 +1815,34 @@
1815 /*
1816 ** Compute and return a linked list of EmailEvent objects
1817 ** corresponding to the current content of the temp.wantalert
1818 ** table which should be defined as follows:
1819 **
1820 ** CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN);
1821 */
1822 EmailEvent *email_compute_event_text(int *pnEvent, int doDigest){
1823 Stmt q;
1824 EmailEvent *p;
1825 EmailEvent anchor;
1826 EmailEvent *pLast;
1827 const char *zUrl = db_get("email-url","http://localhost:8080");
1828
1829 db_prepare(&q,
1830 "SELECT"
1831 " blob.uuid," /* 0 */
1832 " datetime(event.mtime)," /* 1 */
1833 " coalesce(ecomment,comment)"
1834 " || ' (user: ' || coalesce(euser,user,'?')"
1835 " || (SELECT case when length(x)>0 then ' tags: ' || x else '' end"
1836 " FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
1837 " FROM tag, tagxref"
1838 " WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
1839 " AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
1840 " || ')' as comment," /* 2 */
1841 " tagxref.value AS branch," /* 3 */
1842 " wantalert.eventId," /* 4 */
1843 " wantalert.needMod" /* 5 */
1844 " FROM temp.wantalert JOIN tag CROSS JOIN event CROSS JOIN blob"
1845 " LEFT JOIN tagxref ON tagxref.tagid=tag.tagid"
1846 " AND tagxref.tagtype>0"
1847 " AND tagxref.rid=blob.rid"
1848 " WHERE blob.rid=event.objid"
@@ -1853,13 +1857,15 @@
1857 const char *zType = "";
1858 p = fossil_malloc( sizeof(EmailEvent) );
1859 pLast->pNext = p;
1860 pLast = p;
1861 p->type = db_column_text(&q, 4)[0];
1862 p->needMod = db_column_int(&q, 5);
1863 p->pNext = 0;
1864 switch( p->type ){
1865 case 'c': zType = "Check-In"; break;
1866 case 'f': zType = "Forum post"; break;
1867 case 't': zType = "Wiki Edit"; break;
1868 case 'w': zType = "Ticket Change"; break;
1869 }
1870 blob_init(&p->txt, 0, 0);
1871 blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
@@ -1867,10 +1873,16 @@
1873 zType,
1874 db_column_text(&q,2),
1875 zUrl,
1876 db_column_text(&q,0)
1877 );
1878 if( p->needMod ){
1879 blob_appendf(&p->txt,
1880 "** Pending moderator approval (%s/modreq) **\n",
1881 zUrl
1882 );
1883 }
1884 (*pnEvent)++;
1885 }
1886 db_finalize(&q);
1887 return anchor.pNext;
1888 }
@@ -1905,32 +1917,44 @@
1917 ** command line, generate text for all events named in the
1918 ** pending_alert table.
1919 **
1920 ** This command is intended for testing and debugging the logic
1921 ** that generates email alert text.
1922 **
1923 ** Options:
1924 **
1925 ** --digest Generate digest alert text
1926 ** --needmod Assume all events are pending moderator approval
1927 */
1928 void test_alert_cmd(void){
1929 Blob out;
1930 int nEvent;
1931 int needMod;
1932 int doDigest;
1933 EmailEvent *pEvent, *p;
1934
1935 doDigest = find_option("digest",0,0)!=0;
1936 needMod = find_option("needmod",0,0)!=0;
1937 db_find_and_open_repository(0, 0);
1938 verify_all_options();
1939 db_begin_transaction();
1940 email_schema(0);
1941 db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT, needMod BOOLEAN)");
1942 if( g.argc==2 ){
1943 db_multi_exec(
1944 "INSERT INTO wantalert(eventId,needMod)"
1945 " SELECT eventid, %d FROM pending_alert", needMod);
1946 }else{
1947 int i;
1948 for(i=2; i<g.argc; i++){
1949 db_multi_exec("INSERT INTO wantalert(eventId,needMod) VALUES(%Q,%d)",
1950 g.argv[i], needMod);
1951 }
1952 }
1953 blob_init(&out, 0, 0);
1954 email_header(&out);
1955 pEvent = email_compute_event_text(&nEvent, doDigest);
1956 for(p=pEvent; p; p=p->pNext){
1957 blob_append(&out, "\n", 1);
1958 blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
1959 }
1960 email_free_eventlist(pEvent);
@@ -2001,10 +2025,11 @@
2025 EmailSender *pSender = 0;
2026 u32 senderFlags = 0;
2027
2028 if( g.fSqlTrace ) fossil_trace("-- BEGIN email_send_alerts(%u)\n", flags);
2029 db_begin_transaction();
2030 email_schema(0);
2031 if( !email_enabled() ) goto send_alerts_done;
2032 zUrl = db_get("email-url",0);
2033 if( zUrl==0 ) goto send_alerts_done;
2034 zRepoName = db_get("email-subname",0);
2035 if( zRepoName==0 ) goto send_alerts_done;
@@ -2014,25 +2039,35 @@
2039 senderFlags |= EMAIL_TRACE;
2040 }
2041 pSender = email_sender_new(zDest, senderFlags);
2042 db_multi_exec(
2043 "DROP TABLE IF EXISTS temp.wantalert;"
2044 "CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN, sentMod);"
2045 );
2046 if( flags & SENDALERT_DIGEST ){
2047 /* Unmoderated changes are never sent as part of a digest */
2048 db_multi_exec(
2049 "INSERT INTO wantalert(eventId,needMod)"
2050 " SELECT eventid, 0"
2051 " FROM pending_alert"
2052 " WHERE sentDigest IS FALSE"
2053 " AND NOT EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2));"
2054 );
 
2055 }else{
2056 /* Immediate alerts might include events that are subject to
2057 ** moderator approval */
2058 db_multi_exec(
2059 "INSERT INTO wantalert(eventId,needMod,sentMod)"
2060 " SELECT eventid,"
2061 " EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2)),"
2062 " sentMod"
2063 " FROM pending_alert"
2064 " WHERE sentSep IS FALSE;"
2065 "DELETE FROM wantalert WHERE needMod AND sentMod;"
2066 );
2067 }
2068 pEvents = email_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2069 if( nEvent==0 ) goto send_alerts_done;
2070 blob_init(&hdr, 0, 0);
2071 blob_init(&body, 0, 0);
2072 db_prepare(&q,
2073 "SELECT"
@@ -2051,13 +2086,26 @@
2086 const char *zEmail = db_column_text(&q, 1);
2087 const char *zCap = db_column_text(&q, 3);
2088 int nHit = 0;
2089 for(p=pEvents; p; p=p->pNext){
2090 if( strchr(zSub,p->type)==0 ) continue;
2091 if( p->needMod ){
2092 /* For events that require moderator approval, only send an alert
2093 ** if the recipient is a moderator for that type of event */
2094 char xType = '*';
2095 switch( p->type ){
2096 case 'f': xType = '5'; break;
2097 case 't': xType = 'q'; break;
2098 case 'w': xType = 'l'; break;
2099 }
2100 if( strchr(zCap,xType)==0 ) continue;
2101 }else if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
2102 /* Setup and admin users can get any notification that does not
2103 ** require moderation */
2104 }else{
2105 /* Other users only see the alert if they have sufficient
2106 ** privilege to view the event itself */
2107 char xType = '*';
2108 switch( p->type ){
2109 case 'c': xType = 'o'; break;
2110 case 'f': xType = '2'; break;
2111 case 't': xType = 'r'; break;
@@ -2089,15 +2137,24 @@
2137 blob_reset(&body);
2138 db_finalize(&q);
2139 email_free_eventlist(pEvents);
2140 if( (flags & SENDALERT_PRESERVE)==0 ){
2141 if( flags & SENDALERT_DIGEST ){
2142 db_multi_exec(
2143 "UPDATE pending_alert SET sentDigest=true"
2144 " WHERE eventid IN (SELECT eventid FROM wantalert);"
2145 "DELETE FROM pending_alert WHERE sentDigest AND sentSep;"
2146 );
2147 }else{
2148 db_multi_exec(
2149 "UPDATE pending_alert SET sentSep=true"
2150 " WHERE eventid IN (SELECT eventid FROM wantalert WHERE NOT needMod);"
2151 "UPDATE pending_alert SET sentMod=true"
2152 " WHERE eventid IN (SELECT eventid FROM wantalert WHERE needMod);"
2153 "DELETE FROM pending_alert WHERE sentDigest AND sentSep;"
2154 );
2155 }
 
2156 }
2157 send_alerts_done:
2158 email_sender_free(pSender);
2159 if( g.fSqlTrace ) fossil_trace("-- END email_send_alerts(%u)\n", flags);
2160 db_end_transaction(0);
2161

Keyboard Shortcuts

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