Fossil SCM

Merge into trunk the enhancements that allow an admin to set an expiration time for email notification subscriptions.

drh 2021-05-31 23:33 trunk merge
Commit 34d45c55b9f776e54ae377631d62106cb354f3728f666a26b88b527ccfd5b60a
Parent 41f0838cbeacb51…
3 files changed +211 -24 +4 -1 +6 -6
~ src/alerts.c ~ src/stat.c ~ www/alerts.md
M src/alerts.c
+211 -24
--- src/alerts.c
+++ src/alerts.c
@@ -291,17 +291,18 @@
291291
 @ <hr>
292292
293293
 entry_attribute("Subscription Renewal Interval In Days", 8,
294294
 "email-renew-interval", "eri", "", 0);
295295
 @ <p>
296
- @ If this value is a positive integer N, then email notification
297
- @ subscriptions will be suspended N days after the last known
296
+ @ If this value is a integer N greater than or equal to 14, then email
297
+ @ notification subscriptions will be suspended N days after the last known
298298
 @ interaction with the user. This prevents sending notifications
299
- @ to abandoned accounts. If a subscription gets close to expiring
299
+ @ to abandoned accounts. If a subscription comes within 7 days of expiring,
300300
 @ a separate email goes out with the daily digest that prompts the
301301
 @ subscriber to click on a link to the "/renew" webpage in order to
302
- @ extend their subscription.
302
+ @ extend their subscription. Subscriptions never expire if this setting
303
+ @ is less than 14 or is an empty string.
303304
 @ (Property: "email-renew-interval")</p>
304305
 @ <hr>
305306
306307
 multiple_choice_attribute("Email Send Method", "email-send-method", "esm",
307308
 "off", count(azSendMethods)/2, azSendMethods);
@@ -968,10 +969,38 @@
968969
 ** SETTING: email-subname width=16
969970
 ** This is a short name used to identifies the repository in the Subject:
970971
 ** line of email alerts. Traditionally this name is included in square
971972
 ** brackets. Examples: "[fossil-src]", "[sqlite-src]".
972973
 */
974
+/*
975
+** SETTING: email-renew-interval width=16
976
+** If this setting as an integer N that is 14 or greater then email
977
+** notification is suspected for subscriptions that have a "last contact
978
+** time" of more than N days ago. The "last contact time" is recorded
979
+** in the SUBSCRIBER.LASTCONTACT entry of the database. Logging in,
980
+** sending a forum post, editing a wiki page, changing subscription settings
981
+** at /alerts, or visiting /renew all update the last contact time.
982
+** If this setting is not an integer value or is less than 14 or undefined,
983
+** then subscriptions never expire.
984
+*/
985
+/* X-VARIABLE: email-renew-warning
986
+** X-VARIABLE: email-renew-cutoff
987
+**
988
+** These CONFIG table entries are not considered "settings" since their
989
+** values are computed and updated automatically.
990
+**
991
+** email-renew-cutoff is the lastContact cutoff for subscription. It
992
+** is measured in days since 1970-01-01. If The lastContact time for
993
+** a subscription is less than email-renew-cutoff, then now new emails
994
+** are sent to the subscriber.
995
+**
996
+** email-renew-warning is the time (in days since 1970-01-01) when the
997
+** last batch of "your subscription is about to expire" emails were
998
+** sent out.
999
+**
1000
+** email-renew-cutoff is normally 7 days behind email-renew-warning.
1001
+*/
9731002
 /*
9741003
 ** SETTING: email-send-method width=5 default=off sensitive
9751004
 ** Determine the method used to send email. Allowed values are
9761005
 ** "off", "relay", "pipe", "dir", "db", and "stdout". The "off" value
9771006
 ** means no email is ever sent. The "relay" value means emails are sent
@@ -1032,29 +1061,32 @@
10321061
 ** a cron-job to make sure alerts are sent
10331062
 ** in a timely manner.
10341063
 ** Options:
10351064
 **
10361065
 ** --digest Send digests
1066
+** --renewal Send subscription renewal
1067
+** notices
10371068
 ** --test Write to standard output
10381069
 **
10391070
 ** settings [NAME VALUE] With no arguments, list all email settings.
10401071
 ** Or change the value of a single email setting.
10411072
 **
10421073
 ** status Report on the status of the email alert
10431074
 ** subsystem
10441075
 **
1045
-** subscribers [PATTERN] List all subscribers matching PATTERN.
1076
+** subscribers [PATTERN] List all subscribers matching PATTERN. Either
1077
+** LIKE or GLOB wildcards can be used in PATTERN.
10461078
 **
10471079
 ** test-message TO [OPTS] Send a single email message using whatever
10481080
 ** email sending mechanism is currently configured.
10491081
 ** Use this for testing the email notification
10501082
 ** configuration. Options:
10511083
 **
1052
-** --body FILENAME
1053
-** --smtp-trace
1054
-** --stdout
1055
-** -S|--subject SUBJECT
1084
+** --body FILENAME Content from FILENAME
1085
+** --smtp-trace Trace SMTP processing
1086
+** --stdout Send msg to stdout
1087
+** -S|--subject SUBJECT Message "subject:"
10561088
 **
10571089
 ** unsubscribe EMAIL Remove a single subscriber with the given EMAIL.
10581090
 */
10591091
 void alert_cmd(void){
10601092
 const char *zCmd;
@@ -1108,10 +1140,11 @@
11081140
 }
11091141
 }else
11101142
 if( strncmp(zCmd, "send", nCmd)==0 ){
11111143
 u32 eFlags = 0;
11121144
 if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
1145
+ if( find_option("renewal",0,0)!=0 ) eFlags |= SENDALERT_RENEWAL;
11131146
 if( find_option("test",0,0)!=0 ){
11141147
 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
11151148
 }
11161149
 verify_all_options();
11171150
 alert_send_alerts(eFlags);
@@ -1137,10 +1170,12 @@
11371170
 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
11381171
 print_setting(pSetting);
11391172
 }
11401173
 }else
11411174
 if( strncmp(zCmd, "status", nCmd)==0 ){
1175
+ Stmt q;
1176
+ int iCutoff;
11421177
 int nSetting, n;
11431178
 static const char *zFmt = "%-29s %d\n";
11441179
 const Setting *pSetting = setting_info(&nSetting);
11451180
 db_open_config(1, 0);
11461181
 verify_all_options();
@@ -1152,14 +1187,32 @@
11521187
 }
11531188
 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
11541189
 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
11551190
 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
11561191
 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
1192
+ db_prepare(&q,
1193
+ "SELECT"
1194
+ " name,"
1195
+ " value,"
1196
+ " now()/86400-value,"
1197
+ " date(value*86400,'unixepoch')"
1198
+ " FROM repository.config"
1199
+ " WHERE name in ('email-renew-warning','email-renew-cutoff');");
1200
+ while( db_step(&q)==SQLITE_ROW ){
1201
+ fossil_print("%-29s %-6d (%d days ago on %s)\n",
1202
+ db_column_text(&q, 0),
1203
+ db_column_int(&q, 1),
1204
+ db_column_int(&q, 2),
1205
+ db_column_text(&q, 3));
1206
+ }
1207
+ db_finalize(&q);
11571208
 n = db_int(0,"SELECT count(*) FROM subscriber");
11581209
 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
1210
+ iCutoff = db_get_int("email-renew-cutoff", 0);
11591211
 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
1160
- " AND NOT sdonotcall AND length(ssub)>1");
1212
+ " AND NOT sdonotcall AND length(ssub)>1"
1213
+ " AND lastContact>=%d", iCutoff);
11611214
 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
11621215
 }else
11631216
 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
11641217
 Stmt q;
11651218
 verify_all_options();
@@ -1790,11 +1843,13 @@
17901843
 " ssub," /* 4 */
17911844
 " smip," /* 5 */
17921845
 " suname," /* 6 */
17931846
 " datetime(mtime,'unixepoch')," /* 7 */
17941847
 " datetime(sctime,'unixepoch')," /* 8 */
1795
- " hex(subscriberCode)" /* 9 */
1848
+ " hex(subscriberCode)," /* 9 */
1849
+ " date(coalesce(lastContact*86400,mtime),'unixepoch')," /* 10 */
1850
+ " now()/86400 - coalesce(lastContact,mtime/86400)" /* 11 */
17961851
 " FROM subscriber WHERE subscriberId=%d", sid);
17971852
 if( db_step(&q)!=SQLITE_ROW ){
17981853
 db_finalize(&q);
17991854
 db_commit_transaction();
18001855
 cgi_redirect("subscribe");
@@ -1889,10 +1944,15 @@
18891944
 @ </tr>
18901945
 @ <tr>
18911946
 @ <td class='form_label'>Subscriber&nbsp;Code:</td>
18921947
 @ <td>%h(db_column_text(&q,9))</td>
18931948
 @ <tr>
1949
+ @ <tr>
1950
+ @ <td class='form_label'>Last Contact:</td>
1951
+ @ <td>%h(db_column_text(&q,10)) &larr; \
1952
+ @ %,d(db_column_int(&q,11)) days ago</td>
1953
+ @ </tr>
18941954
 @ <td class="form_label">User:</td>
18951955
 @ <td><input type="text" name="suname" value="%h(suname?suname:"")" \
18961956
 @ size="30">\
18971957
 uid = db_int(0, "SELECT uid FROM user WHERE login=%Q", suname);
18981958
 if( uid ){
@@ -1963,14 +2023,16 @@
19632023
 }
19642024
19652025
 /*
19662026
 ** WEBPAGE: renew
19672027
 **
1968
-** Users visit this page to update the lastContact date on their
1969
-** subscription. This prevents their subscriptions from expiring.
1970
-**
1971
-** A valid subscriber code is supplied in the name= query parameter.
2028
+** Users visit this page to update the last-contact date on their
2029
+** subscription. The last-contact date is the day that the subscriber
2030
+** last interacted with the repository. If the name= query parameter
2031
+** (or POST parameter) contains a valid subscriber code, then the last-contact
2032
+** subscription associated with that subscriber code is updated to be the
2033
+** current date.
19722034
 */
19732035
 void renewal_page(void){
19742036
 const char *zName = P("name");
19752037
 int iInterval = db_get_int("email-renew-interval", 0);
19762038
 Stmt s;
@@ -2182,10 +2244,14 @@
21822244
 Stmt q;
21832245
 sqlite3_int64 iNow;
21842246
 int nTotal;
21852247
 int nPending;
21862248
 int nDel = 0;
2249
+ int iCutoff = db_get_int("email-renew-cutoff",0);
2250
+ int iWarning = db_get_int("email-renew-warning",0);
2251
+ char zCutoffClr[8];
2252
+ char zWarnClr[8];
21872253
 if( alert_webpages_disabled() ) return;
21882254
 login_check_credentials();
21892255
 if( !g.perm.Admin ){
21902256
 login_needed(0);
21912257
 return;
@@ -2240,10 +2306,12 @@
22402306
 style_submenu_element("Show All","%R/subscribers");
22412307
 }
22422308
 blob_append_sql(&sql," ORDER BY mtime DESC");
22432309
 db_prepare_blob(&q, &sql);
22442310
 iNow = time(0);
2311
+ memcpy(zCutoffClr, hash_color("A"), sizeof(zCutoffClr));
2312
+ memcpy(zWarnClr, hash_color("HIJ"), sizeof(zWarnClr));
22452313
 @ <table border='1' class='sortable' \
22462314
 @ data-init-sort='6' data-column-types='tttttKKt'>
22472315
 @ <thead>
22482316
 @ <tr>
22492317
 @ <th>Email
@@ -2273,11 +2341,19 @@
22732341
 }else{
22742342
 @ <td>%h(zUname)</td>
22752343
 }
22762344
 @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
22772345
 @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
2278
- @ <td data-sortkey='%010llx(iContact)'>%z(human_readable_age(rContact))</td>
2346
+ @ <td data-sortkey='%010llx(iContact)'>\
2347
+ if( iContact>iWarning ){
2348
+ @ <span>\
2349
+ }else if( iContact>iCutoff ){
2350
+ @ <span style='background-color:%s(zWarnClr);'>\
2351
+ }else{
2352
+ @ <span style='background-color:%s(zCutoffClr);'>\
2353
+ }
2354
+ @ %z(human_readable_age(rContact))</td>
22792355
 @ <td>%h(db_column_text(&q,7))</td>
22802356
 @ </tr>
22812357
 }
22822358
 @ </tbody></table>
22832359
 db_finalize(&q);
@@ -2530,14 +2606,22 @@
25302606
 ** Usage: %fossil test-alert EVENTID ...
25312607
 **
25322608
 ** Generate the text of an email alert for all of the EVENTIDs
25332609
 ** listed on the command-line. Or if no events are listed on the
25342610
 ** command line, generate text for all events named in the
2535
-** pending_alert table.
2611
+** pending_alert table. The text of the email alerts appears on
2612
+** standard output.
2613
+**
2614
+** This command is intended for testing and debugging Fossil itself,
2615
+** for example when enhancing the email alert system or fixing bugs
2616
+** in the email alert system. If you are not making changes to the
2617
+** Fossil source code, this command is probably not useful to you.
25362618
 **
2537
-** This command is intended for testing and debugging the logic
2538
-** that generates email alert text.
2619
+** EVENTIDs are text. The first character is 'c', 'f', 't', or 'w'
2620
+** for check-in, forum, ticket, or wiki. The remaining text is a
2621
+** integer that references the EVENT.OBJID value for the event.
2622
+** Run /timeline?showid to see these OBJID values.
25392623
 **
25402624
 ** Options:
25412625
 **
25422626
 ** --digest Generate digest alert text
25432627
 ** --needmod Assume all events are pending moderator approval
@@ -2632,22 +2716,69 @@
26322716
 db_end_transaction(0);
26332717
 if( doAuto ){
26342718
 alert_backoffice(SENDALERT_TRACE|mFlags);
26352719
 }
26362720
 }
2721
+
2722
+/*
2723
+** Construct the header and body for an email message that will alert
2724
+** a subscriber that their subscriptions are about to expire.
2725
+*/
2726
+static void alert_renewal_msg(
2727
+ Blob *pHdr, /* Write email header here */
2728
+ Blob *pBody, /* Write email body here */
2729
+ const char *zCode, /* The subscriber code */
2730
+ int lastContact, /* Last contact (days since 1970) */
2731
+ const char *zEAddr, /* Subscriber email address. Send to this. */
2732
+ const char *zSub, /* Subscription codes */
2733
+ const char *zRepoName, /* Name of the sending Fossil repostory */
2734
+ const char *zUrl /* URL for the sending Fossil repostory */
2735
+){
2736
+ blob_appendf(pHdr,"To: <%s>\r\n", zEAddr);
2737
+ blob_appendf(pHdr,"Subject: %s Subscription to %s expires soon\r\n",
2738
+ zRepoName, zUrl);
2739
+ blob_appendf(pBody,
2740
+ "You are currently receiving email notification of the following kinds\n"
2741
+ "of changes to the %s Fossil repository at %s:\n\n",
2742
+ zRepoName, zUrl
2743
+ );
2744
+ if( strchr(zSub, 'a') ) blob_appendf(pBody, " * Announcements\n");
2745
+ if( strchr(zSub, 'c') ) blob_appendf(pBody, " * Check-ins\n");
2746
+ if( strchr(zSub, 'f') ) blob_appendf(pBody, " * Forum posts\n");
2747
+ if( strchr(zSub, 't') ) blob_appendf(pBody, " * Ticket changes\n");
2748
+ if( strchr(zSub, 'w') ) blob_appendf(pBody, " * Wiki changes\n");
2749
+ blob_appendf(pBody,
2750
+ "\nTo continue receiving email notifications, click the following link\n"
2751
+ "\n %s/renew/%s\n\n",
2752
+ zUrl, zCode
2753
+ );
2754
+ blob_appendf(pBody,
2755
+ "If you take no action, your subscription will expire and you will be\n"
2756
+ "unsubscribed in about a week. To make other changes or to unsubscribe\n"
2757
+ "immediately, visit the following webpage:\n\n"
2758
+ " %s/alerts/%s\n\n",
2759
+ zUrl, zCode
2760
+ );
2761
+}
26372762
26382763
 #if INTERFACE
26392764
 /*
26402765
 ** Flags for alert_send_alerts()
26412766
 */
26422767
 #define SENDALERT_DIGEST 0x0001 /* Send a digest */
26432768
 #define SENDALERT_PRESERVE 0x0002 /* Do not mark the task as done */
26442769
 #define SENDALERT_STDOUT 0x0004 /* Print emails instead of sending */
26452770
 #define SENDALERT_TRACE 0x0008 /* Trace operation for debugging */
2771
+#define SENDALERT_RENEWAL 0x0010 /* Send renewal notices */
26462772
26472773
 #endif /* INTERFACE */
26482774
2775
+/*
2776
+** Minimum number of days between renewal messages
2777
+*/
2778
+#define ALERT_RENEWAL_MSG_FREQUENCY 7 /* Do renewals at most once/week */
2779
+
26492780
 /*
26502781
 ** Send alert emails to subscribers.
26512782
 **
26522783
 ** This procedure is run by either the backoffice, or in response to the
26532784
 ** "fossil alerts send" command. Details of operation are controlled by
@@ -2691,10 +2822,11 @@
26912822
 const char *zRepoName;
26922823
 const char *zFrom;
26932824
 const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
26942825
 AlertSender *pSender = 0;
26952826
 u32 senderFlags = 0;
2827
+ int iInterval = 0; /* Subscription renewal interval */
26962828
26972829
 if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
26982830
 alert_schema(0);
26992831
 if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
27002832
 zUrl = db_get("email-url",0);
@@ -2740,11 +2872,11 @@
27402872
27412873
 /* Step 2: compute EmailEvent objects for every notification that
27422874
 ** needs sending.
27432875
 */
27442876
 pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2745
- if( nEvent==0 ) goto send_alert_done;
2877
+ if( nEvent==0 ) goto send_alert_expiration_warnings;
27462878
27472879
 /* Step 4a: Update the pending_alerts table to designate the
27482880
 ** alerts as having all been sent. This is done *before* step (3)
27492881
 ** so that a crash will not cause alerts to be sent multiple times.
27502882
 ** Better a missed alert than being spammed with hundreds of alerts
@@ -2775,13 +2907,16 @@
27752907
 " hex(subscriberCode)," /* 0 */
27762908
 " semail," /* 1 */
27772909
 " ssub," /* 2 */
27782910
 " fullcap(user.cap)" /* 3 */
27792911
 " FROM subscriber LEFT JOIN user ON (login=suname)"
2780
- " WHERE sverified AND NOT sdonotcall"
2781
- " AND sdigest IS %s",
2782
- zDigest/*safe-for-%s*/
2912
+ " WHERE sverified"
2913
+ " AND NOT sdonotcall"
2914
+ " AND sdigest IS %s"
2915
+ " AND coalesce(subscriber.lastContact,subscriber.mtime)>=%d",
2916
+ zDigest/*safe-for-%s*/,
2917
+ db_get_int("email-renew-cutoff",0)
27832918
 );
27842919
 while( db_step(&q)==SQLITE_ROW ){
27852920
 const char *zCode = db_column_text(&q, 0);
27862921
 const char *zSub = db_column_text(&q, 2);
27872922
 const char *zEmail = db_column_text(&q, 1);
@@ -2865,10 +3000,62 @@
28653000
28663001
 /* Step 4b: Update the pending_alerts table to remove all of the
28673002
 ** alerts that have been completely sent.
28683003
 */
28693004
 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");
3005
+
3006
+ /* Send renewal messages to subscribers whose subscriptions are about
3007
+ ** to expire. Only do this if:
3008
+ **
3009
+ ** (1) email-renew-interval is 14 or greater (or in other words if
3010
+ ** subscription expiration is enabled).
3011
+ **
3012
+ ** (2) The SENDALERT_RENEWAL flag is set
3013
+ */
3014
+send_alert_expiration_warnings:
3015
+ if( (flags & SENDALERT_RENEWAL)!=0
3016
+ && (iInterval = db_get_int("email-renew-interval",0))>=14
3017
+ ){
3018
+ int iNow = (int)(time(0)/86400);
3019
+ int iOldWarn = db_get_int("email-renew-warning",0);
3020
+ int iNewWarn = iNow - iInterval + ALERT_RENEWAL_MSG_FREQUENCY;
3021
+ if( iNewWarn >= iOldWarn + ALERT_RENEWAL_MSG_FREQUENCY ){
3022
+ db_prepare(&q,
3023
+ "SELECT"
3024
+ " hex(subscriberCode)," /* 0 */
3025
+ " lastContact," /* 1 */
3026
+ " semail," /* 2 */
3027
+ " ssub" /* 3 */
3028
+ " FROM subscriber"
3029
+ " WHERE lastContact<=%d AND lastContact>%d"
3030
+ " AND NOT sdonotcall"
3031
+ " AND length(sdigest)>0",
3032
+ iNewWarn, iOldWarn
3033
+ );
3034
+ while( db_step(&q)==SQLITE_ROW ){
3035
+ Blob hdr, body;
3036
+ blob_init(&hdr, 0, 0);
3037
+ blob_init(&body, 0, 0);
3038
+ alert_renewal_msg(&hdr, &body,
3039
+ db_column_text(&q,0),
3040
+ db_column_int(&q,1),
3041
+ db_column_text(&q,2),
3042
+ db_column_text(&q,3),
3043
+ zRepoName, zUrl);
3044
+ alert_send(pSender,&hdr,&body,0);
3045
+ blob_reset(&hdr);
3046
+ blob_reset(&body);
3047
+ }
3048
+ db_finalize(&q);
3049
+ if( (flags & SENDALERT_PRESERVE)==0 ){
3050
+ if( iOldWarn>0 ){
3051
+ db_set_int("email-renew-cutoff", iOldWarn, 0);
3052
+ }
3053
+ db_set_int("email-renew-warning", iNewWarn, 0);
3054
+ }
3055
+ }
3056
+ }
28703057
28713058
 send_alert_done:
28723059
 alert_sender_free(pSender);
28733060
 if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
28743061
 return nSent;
@@ -2891,11 +3078,11 @@
28913078
 if( !alert_tables_exist() ) return 0;
28923079
 nSent = alert_send_alerts(mFlags);
28933080
 iJulianDay = db_int(0, "SELECT julianday('now')");
28943081
 if( iJulianDay>db_get_int("email-last-digest",0) ){
28953082
 db_set_int("email-last-digest",iJulianDay,0);
2896
- nSent += alert_send_alerts(SENDALERT_DIGEST|mFlags);
3083
+ nSent += alert_send_alerts(SENDALERT_DIGEST|SENDALERT_RENEWAL|mFlags);
28973084
 }
28983085
 return nSent;
28993086
 }
29003087
29013088
 /*
29023089
--- src/alerts.c
+++ src/alerts.c
@@ -291,17 +291,18 @@
291 @ <hr>
292
293 entry_attribute("Subscription Renewal Interval In Days", 8,
294 "email-renew-interval", "eri", "", 0);
295 @ <p>
296 @ If this value is a positive integer N, then email notification
297 @ subscriptions will be suspended N days after the last known
298 @ interaction with the user. This prevents sending notifications
299 @ to abandoned accounts. If a subscription gets close to expiring
300 @ a separate email goes out with the daily digest that prompts the
301 @ subscriber to click on a link to the "/renew" webpage in order to
302 @ extend their subscription.
 
303 @ (Property: "email-renew-interval")</p>
304 @ <hr>
305
306 multiple_choice_attribute("Email Send Method", "email-send-method", "esm",
307 "off", count(azSendMethods)/2, azSendMethods);
@@ -968,10 +969,38 @@
968 ** SETTING: email-subname width=16
969 ** This is a short name used to identifies the repository in the Subject:
970 ** line of email alerts. Traditionally this name is included in square
971 ** brackets. Examples: "[fossil-src]", "[sqlite-src]".
972 */
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
973 /*
974 ** SETTING: email-send-method width=5 default=off sensitive
975 ** Determine the method used to send email. Allowed values are
976 ** "off", "relay", "pipe", "dir", "db", and "stdout". The "off" value
977 ** means no email is ever sent. The "relay" value means emails are sent
@@ -1032,29 +1061,32 @@
1032 ** a cron-job to make sure alerts are sent
1033 ** in a timely manner.
1034 ** Options:
1035 **
1036 ** --digest Send digests
 
 
1037 ** --test Write to standard output
1038 **
1039 ** settings [NAME VALUE] With no arguments, list all email settings.
1040 ** Or change the value of a single email setting.
1041 **
1042 ** status Report on the status of the email alert
1043 ** subsystem
1044 **
1045 ** subscribers [PATTERN] List all subscribers matching PATTERN.
 
1046 **
1047 ** test-message TO [OPTS] Send a single email message using whatever
1048 ** email sending mechanism is currently configured.
1049 ** Use this for testing the email notification
1050 ** configuration. Options:
1051 **
1052 ** --body FILENAME
1053 ** --smtp-trace
1054 ** --stdout
1055 ** -S|--subject SUBJECT
1056 **
1057 ** unsubscribe EMAIL Remove a single subscriber with the given EMAIL.
1058 */
1059 void alert_cmd(void){
1060 const char *zCmd;
@@ -1108,10 +1140,11 @@
1108 }
1109 }else
1110 if( strncmp(zCmd, "send", nCmd)==0 ){
1111 u32 eFlags = 0;
1112 if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
 
1113 if( find_option("test",0,0)!=0 ){
1114 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
1115 }
1116 verify_all_options();
1117 alert_send_alerts(eFlags);
@@ -1137,10 +1170,12 @@
1137 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
1138 print_setting(pSetting);
1139 }
1140 }else
1141 if( strncmp(zCmd, "status", nCmd)==0 ){
 
 
1142 int nSetting, n;
1143 static const char *zFmt = "%-29s %d\n";
1144 const Setting *pSetting = setting_info(&nSetting);
1145 db_open_config(1, 0);
1146 verify_all_options();
@@ -1152,14 +1187,32 @@
1152 }
1153 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
1154 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
1155 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
1156 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1157 n = db_int(0,"SELECT count(*) FROM subscriber");
1158 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
 
1159 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
1160 " AND NOT sdonotcall AND length(ssub)>1");
 
1161 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
1162 }else
1163 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
1164 Stmt q;
1165 verify_all_options();
@@ -1790,11 +1843,13 @@
1790 " ssub," /* 4 */
1791 " smip," /* 5 */
1792 " suname," /* 6 */
1793 " datetime(mtime,'unixepoch')," /* 7 */
1794 " datetime(sctime,'unixepoch')," /* 8 */
1795 " hex(subscriberCode)" /* 9 */
 
 
1796 " FROM subscriber WHERE subscriberId=%d", sid);
1797 if( db_step(&q)!=SQLITE_ROW ){
1798 db_finalize(&q);
1799 db_commit_transaction();
1800 cgi_redirect("subscribe");
@@ -1889,10 +1944,15 @@
1889 @ </tr>
1890 @ <tr>
1891 @ <td class='form_label'>Subscriber&nbsp;Code:</td>
1892 @ <td>%h(db_column_text(&q,9))</td>
1893 @ <tr>
 
 
 
 
 
1894 @ <td class="form_label">User:</td>
1895 @ <td><input type="text" name="suname" value="%h(suname?suname:"")" \
1896 @ size="30">\
1897 uid = db_int(0, "SELECT uid FROM user WHERE login=%Q", suname);
1898 if( uid ){
@@ -1963,14 +2023,16 @@
1963 }
1964
1965 /*
1966 ** WEBPAGE: renew
1967 **
1968 ** Users visit this page to update the lastContact date on their
1969 ** subscription. This prevents their subscriptions from expiring.
1970 **
1971 ** A valid subscriber code is supplied in the name= query parameter.
 
 
1972 */
1973 void renewal_page(void){
1974 const char *zName = P("name");
1975 int iInterval = db_get_int("email-renew-interval", 0);
1976 Stmt s;
@@ -2182,10 +2244,14 @@
2182 Stmt q;
2183 sqlite3_int64 iNow;
2184 int nTotal;
2185 int nPending;
2186 int nDel = 0;
 
 
 
 
2187 if( alert_webpages_disabled() ) return;
2188 login_check_credentials();
2189 if( !g.perm.Admin ){
2190 login_needed(0);
2191 return;
@@ -2240,10 +2306,12 @@
2240 style_submenu_element("Show All","%R/subscribers");
2241 }
2242 blob_append_sql(&sql," ORDER BY mtime DESC");
2243 db_prepare_blob(&q, &sql);
2244 iNow = time(0);
 
 
2245 @ <table border='1' class='sortable' \
2246 @ data-init-sort='6' data-column-types='tttttKKt'>
2247 @ <thead>
2248 @ <tr>
2249 @ <th>Email
@@ -2273,11 +2341,19 @@
2273 }else{
2274 @ <td>%h(zUname)</td>
2275 }
2276 @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
2277 @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
2278 @ <td data-sortkey='%010llx(iContact)'>%z(human_readable_age(rContact))</td>
 
 
 
 
 
 
 
 
2279 @ <td>%h(db_column_text(&q,7))</td>
2280 @ </tr>
2281 }
2282 @ </tbody></table>
2283 db_finalize(&q);
@@ -2530,14 +2606,22 @@
2530 ** Usage: %fossil test-alert EVENTID ...
2531 **
2532 ** Generate the text of an email alert for all of the EVENTIDs
2533 ** listed on the command-line. Or if no events are listed on the
2534 ** command line, generate text for all events named in the
2535 ** pending_alert table.
 
 
 
 
 
 
2536 **
2537 ** This command is intended for testing and debugging the logic
2538 ** that generates email alert text.
 
 
2539 **
2540 ** Options:
2541 **
2542 ** --digest Generate digest alert text
2543 ** --needmod Assume all events are pending moderator approval
@@ -2632,22 +2716,69 @@
2632 db_end_transaction(0);
2633 if( doAuto ){
2634 alert_backoffice(SENDALERT_TRACE|mFlags);
2635 }
2636 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2637
2638 #if INTERFACE
2639 /*
2640 ** Flags for alert_send_alerts()
2641 */
2642 #define SENDALERT_DIGEST 0x0001 /* Send a digest */
2643 #define SENDALERT_PRESERVE 0x0002 /* Do not mark the task as done */
2644 #define SENDALERT_STDOUT 0x0004 /* Print emails instead of sending */
2645 #define SENDALERT_TRACE 0x0008 /* Trace operation for debugging */
 
2646
2647 #endif /* INTERFACE */
2648
 
 
 
 
 
2649 /*
2650 ** Send alert emails to subscribers.
2651 **
2652 ** This procedure is run by either the backoffice, or in response to the
2653 ** "fossil alerts send" command. Details of operation are controlled by
@@ -2691,10 +2822,11 @@
2691 const char *zRepoName;
2692 const char *zFrom;
2693 const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
2694 AlertSender *pSender = 0;
2695 u32 senderFlags = 0;
 
2696
2697 if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
2698 alert_schema(0);
2699 if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
2700 zUrl = db_get("email-url",0);
@@ -2740,11 +2872,11 @@
2740
2741 /* Step 2: compute EmailEvent objects for every notification that
2742 ** needs sending.
2743 */
2744 pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2745 if( nEvent==0 ) goto send_alert_done;
2746
2747 /* Step 4a: Update the pending_alerts table to designate the
2748 ** alerts as having all been sent. This is done *before* step (3)
2749 ** so that a crash will not cause alerts to be sent multiple times.
2750 ** Better a missed alert than being spammed with hundreds of alerts
@@ -2775,13 +2907,16 @@
2775 " hex(subscriberCode)," /* 0 */
2776 " semail," /* 1 */
2777 " ssub," /* 2 */
2778 " fullcap(user.cap)" /* 3 */
2779 " FROM subscriber LEFT JOIN user ON (login=suname)"
2780 " WHERE sverified AND NOT sdonotcall"
2781 " AND sdigest IS %s",
2782 zDigest/*safe-for-%s*/
 
 
 
2783 );
2784 while( db_step(&q)==SQLITE_ROW ){
2785 const char *zCode = db_column_text(&q, 0);
2786 const char *zSub = db_column_text(&q, 2);
2787 const char *zEmail = db_column_text(&q, 1);
@@ -2865,10 +3000,62 @@
2865
2866 /* Step 4b: Update the pending_alerts table to remove all of the
2867 ** alerts that have been completely sent.
2868 */
2869 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2870
2871 send_alert_done:
2872 alert_sender_free(pSender);
2873 if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
2874 return nSent;
@@ -2891,11 +3078,11 @@
2891 if( !alert_tables_exist() ) return 0;
2892 nSent = alert_send_alerts(mFlags);
2893 iJulianDay = db_int(0, "SELECT julianday('now')");
2894 if( iJulianDay>db_get_int("email-last-digest",0) ){
2895 db_set_int("email-last-digest",iJulianDay,0);
2896 nSent += alert_send_alerts(SENDALERT_DIGEST|mFlags);
2897 }
2898 return nSent;
2899 }
2900
2901 /*
2902
--- src/alerts.c
+++ src/alerts.c
@@ -291,17 +291,18 @@
291 @ <hr>
292
293 entry_attribute("Subscription Renewal Interval In Days", 8,
294 "email-renew-interval", "eri", "", 0);
295 @ <p>
296 @ If this value is a integer N greater than or equal to 14, then email
297 @ notification subscriptions will be suspended N days after the last known
298 @ interaction with the user. This prevents sending notifications
299 @ to abandoned accounts. If a subscription comes within 7 days of expiring,
300 @ a separate email goes out with the daily digest that prompts the
301 @ subscriber to click on a link to the "/renew" webpage in order to
302 @ extend their subscription. Subscriptions never expire if this setting
303 @ is less than 14 or is an empty string.
304 @ (Property: "email-renew-interval")</p>
305 @ <hr>
306
307 multiple_choice_attribute("Email Send Method", "email-send-method", "esm",
308 "off", count(azSendMethods)/2, azSendMethods);
@@ -968,10 +969,38 @@
969 ** SETTING: email-subname width=16
970 ** This is a short name used to identifies the repository in the Subject:
971 ** line of email alerts. Traditionally this name is included in square
972 ** brackets. Examples: "[fossil-src]", "[sqlite-src]".
973 */
974 /*
975 ** SETTING: email-renew-interval width=16
976 ** If this setting as an integer N that is 14 or greater then email
977 ** notification is suspected for subscriptions that have a "last contact
978 ** time" of more than N days ago. The "last contact time" is recorded
979 ** in the SUBSCRIBER.LASTCONTACT entry of the database. Logging in,
980 ** sending a forum post, editing a wiki page, changing subscription settings
981 ** at /alerts, or visiting /renew all update the last contact time.
982 ** If this setting is not an integer value or is less than 14 or undefined,
983 ** then subscriptions never expire.
984 */
985 /* X-VARIABLE: email-renew-warning
986 ** X-VARIABLE: email-renew-cutoff
987 **
988 ** These CONFIG table entries are not considered "settings" since their
989 ** values are computed and updated automatically.
990 **
991 ** email-renew-cutoff is the lastContact cutoff for subscription. It
992 ** is measured in days since 1970-01-01. If The lastContact time for
993 ** a subscription is less than email-renew-cutoff, then now new emails
994 ** are sent to the subscriber.
995 **
996 ** email-renew-warning is the time (in days since 1970-01-01) when the
997 ** last batch of "your subscription is about to expire" emails were
998 ** sent out.
999 **
1000 ** email-renew-cutoff is normally 7 days behind email-renew-warning.
1001 */
1002 /*
1003 ** SETTING: email-send-method width=5 default=off sensitive
1004 ** Determine the method used to send email. Allowed values are
1005 ** "off", "relay", "pipe", "dir", "db", and "stdout". The "off" value
1006 ** means no email is ever sent. The "relay" value means emails are sent
@@ -1032,29 +1061,32 @@
1061 ** a cron-job to make sure alerts are sent
1062 ** in a timely manner.
1063 ** Options:
1064 **
1065 ** --digest Send digests
1066 ** --renewal Send subscription renewal
1067 ** notices
1068 ** --test Write to standard output
1069 **
1070 ** settings [NAME VALUE] With no arguments, list all email settings.
1071 ** Or change the value of a single email setting.
1072 **
1073 ** status Report on the status of the email alert
1074 ** subsystem
1075 **
1076 ** subscribers [PATTERN] List all subscribers matching PATTERN. Either
1077 ** LIKE or GLOB wildcards can be used in PATTERN.
1078 **
1079 ** test-message TO [OPTS] Send a single email message using whatever
1080 ** email sending mechanism is currently configured.
1081 ** Use this for testing the email notification
1082 ** configuration. Options:
1083 **
1084 ** --body FILENAME Content from FILENAME
1085 ** --smtp-trace Trace SMTP processing
1086 ** --stdout Send msg to stdout
1087 ** -S|--subject SUBJECT Message "subject:"
1088 **
1089 ** unsubscribe EMAIL Remove a single subscriber with the given EMAIL.
1090 */
1091 void alert_cmd(void){
1092 const char *zCmd;
@@ -1108,10 +1140,11 @@
1140 }
1141 }else
1142 if( strncmp(zCmd, "send", nCmd)==0 ){
1143 u32 eFlags = 0;
1144 if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
1145 if( find_option("renewal",0,0)!=0 ) eFlags |= SENDALERT_RENEWAL;
1146 if( find_option("test",0,0)!=0 ){
1147 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
1148 }
1149 verify_all_options();
1150 alert_send_alerts(eFlags);
@@ -1137,10 +1170,12 @@
1170 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
1171 print_setting(pSetting);
1172 }
1173 }else
1174 if( strncmp(zCmd, "status", nCmd)==0 ){
1175 Stmt q;
1176 int iCutoff;
1177 int nSetting, n;
1178 static const char *zFmt = "%-29s %d\n";
1179 const Setting *pSetting = setting_info(&nSetting);
1180 db_open_config(1, 0);
1181 verify_all_options();
@@ -1152,14 +1187,32 @@
1187 }
1188 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
1189 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
1190 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
1191 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
1192 db_prepare(&q,
1193 "SELECT"
1194 " name,"
1195 " value,"
1196 " now()/86400-value,"
1197 " date(value*86400,'unixepoch')"
1198 " FROM repository.config"
1199 " WHERE name in ('email-renew-warning','email-renew-cutoff');");
1200 while( db_step(&q)==SQLITE_ROW ){
1201 fossil_print("%-29s %-6d (%d days ago on %s)\n",
1202 db_column_text(&q, 0),
1203 db_column_int(&q, 1),
1204 db_column_int(&q, 2),
1205 db_column_text(&q, 3));
1206 }
1207 db_finalize(&q);
1208 n = db_int(0,"SELECT count(*) FROM subscriber");
1209 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
1210 iCutoff = db_get_int("email-renew-cutoff", 0);
1211 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
1212 " AND NOT sdonotcall AND length(ssub)>1"
1213 " AND lastContact>=%d", iCutoff);
1214 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
1215 }else
1216 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
1217 Stmt q;
1218 verify_all_options();
@@ -1790,11 +1843,13 @@
1843 " ssub," /* 4 */
1844 " smip," /* 5 */
1845 " suname," /* 6 */
1846 " datetime(mtime,'unixepoch')," /* 7 */
1847 " datetime(sctime,'unixepoch')," /* 8 */
1848 " hex(subscriberCode)," /* 9 */
1849 " date(coalesce(lastContact*86400,mtime),'unixepoch')," /* 10 */
1850 " now()/86400 - coalesce(lastContact,mtime/86400)" /* 11 */
1851 " FROM subscriber WHERE subscriberId=%d", sid);
1852 if( db_step(&q)!=SQLITE_ROW ){
1853 db_finalize(&q);
1854 db_commit_transaction();
1855 cgi_redirect("subscribe");
@@ -1889,10 +1944,15 @@
1944 @ </tr>
1945 @ <tr>
1946 @ <td class='form_label'>Subscriber&nbsp;Code:</td>
1947 @ <td>%h(db_column_text(&q,9))</td>
1948 @ <tr>
1949 @ <tr>
1950 @ <td class='form_label'>Last Contact:</td>
1951 @ <td>%h(db_column_text(&q,10)) &larr; \
1952 @ %,d(db_column_int(&q,11)) days ago</td>
1953 @ </tr>
1954 @ <td class="form_label">User:</td>
1955 @ <td><input type="text" name="suname" value="%h(suname?suname:"")" \
1956 @ size="30">\
1957 uid = db_int(0, "SELECT uid FROM user WHERE login=%Q", suname);
1958 if( uid ){
@@ -1963,14 +2023,16 @@
2023 }
2024
2025 /*
2026 ** WEBPAGE: renew
2027 **
2028 ** Users visit this page to update the last-contact date on their
2029 ** subscription. The last-contact date is the day that the subscriber
2030 ** last interacted with the repository. If the name= query parameter
2031 ** (or POST parameter) contains a valid subscriber code, then the last-contact
2032 ** subscription associated with that subscriber code is updated to be the
2033 ** current date.
2034 */
2035 void renewal_page(void){
2036 const char *zName = P("name");
2037 int iInterval = db_get_int("email-renew-interval", 0);
2038 Stmt s;
@@ -2182,10 +2244,14 @@
2244 Stmt q;
2245 sqlite3_int64 iNow;
2246 int nTotal;
2247 int nPending;
2248 int nDel = 0;
2249 int iCutoff = db_get_int("email-renew-cutoff",0);
2250 int iWarning = db_get_int("email-renew-warning",0);
2251 char zCutoffClr[8];
2252 char zWarnClr[8];
2253 if( alert_webpages_disabled() ) return;
2254 login_check_credentials();
2255 if( !g.perm.Admin ){
2256 login_needed(0);
2257 return;
@@ -2240,10 +2306,12 @@
2306 style_submenu_element("Show All","%R/subscribers");
2307 }
2308 blob_append_sql(&sql," ORDER BY mtime DESC");
2309 db_prepare_blob(&q, &sql);
2310 iNow = time(0);
2311 memcpy(zCutoffClr, hash_color("A"), sizeof(zCutoffClr));
2312 memcpy(zWarnClr, hash_color("HIJ"), sizeof(zWarnClr));
2313 @ <table border='1' class='sortable' \
2314 @ data-init-sort='6' data-column-types='tttttKKt'>
2315 @ <thead>
2316 @ <tr>
2317 @ <th>Email
@@ -2273,11 +2341,19 @@
2341 }else{
2342 @ <td>%h(zUname)</td>
2343 }
2344 @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
2345 @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
2346 @ <td data-sortkey='%010llx(iContact)'>\
2347 if( iContact>iWarning ){
2348 @ <span>\
2349 }else if( iContact>iCutoff ){
2350 @ <span style='background-color:%s(zWarnClr);'>\
2351 }else{
2352 @ <span style='background-color:%s(zCutoffClr);'>\
2353 }
2354 @ %z(human_readable_age(rContact))</td>
2355 @ <td>%h(db_column_text(&q,7))</td>
2356 @ </tr>
2357 }
2358 @ </tbody></table>
2359 db_finalize(&q);
@@ -2530,14 +2606,22 @@
2606 ** Usage: %fossil test-alert EVENTID ...
2607 **
2608 ** Generate the text of an email alert for all of the EVENTIDs
2609 ** listed on the command-line. Or if no events are listed on the
2610 ** command line, generate text for all events named in the
2611 ** pending_alert table. The text of the email alerts appears on
2612 ** standard output.
2613 **
2614 ** This command is intended for testing and debugging Fossil itself,
2615 ** for example when enhancing the email alert system or fixing bugs
2616 ** in the email alert system. If you are not making changes to the
2617 ** Fossil source code, this command is probably not useful to you.
2618 **
2619 ** EVENTIDs are text. The first character is 'c', 'f', 't', or 'w'
2620 ** for check-in, forum, ticket, or wiki. The remaining text is a
2621 ** integer that references the EVENT.OBJID value for the event.
2622 ** Run /timeline?showid to see these OBJID values.
2623 **
2624 ** Options:
2625 **
2626 ** --digest Generate digest alert text
2627 ** --needmod Assume all events are pending moderator approval
@@ -2632,22 +2716,69 @@
2716 db_end_transaction(0);
2717 if( doAuto ){
2718 alert_backoffice(SENDALERT_TRACE|mFlags);
2719 }
2720 }
2721
2722 /*
2723 ** Construct the header and body for an email message that will alert
2724 ** a subscriber that their subscriptions are about to expire.
2725 */
2726 static void alert_renewal_msg(
2727 Blob *pHdr, /* Write email header here */
2728 Blob *pBody, /* Write email body here */
2729 const char *zCode, /* The subscriber code */
2730 int lastContact, /* Last contact (days since 1970) */
2731 const char *zEAddr, /* Subscriber email address. Send to this. */
2732 const char *zSub, /* Subscription codes */
2733 const char *zRepoName, /* Name of the sending Fossil repostory */
2734 const char *zUrl /* URL for the sending Fossil repostory */
2735 ){
2736 blob_appendf(pHdr,"To: <%s>\r\n", zEAddr);
2737 blob_appendf(pHdr,"Subject: %s Subscription to %s expires soon\r\n",
2738 zRepoName, zUrl);
2739 blob_appendf(pBody,
2740 "You are currently receiving email notification of the following kinds\n"
2741 "of changes to the %s Fossil repository at %s:\n\n",
2742 zRepoName, zUrl
2743 );
2744 if( strchr(zSub, 'a') ) blob_appendf(pBody, " * Announcements\n");
2745 if( strchr(zSub, 'c') ) blob_appendf(pBody, " * Check-ins\n");
2746 if( strchr(zSub, 'f') ) blob_appendf(pBody, " * Forum posts\n");
2747 if( strchr(zSub, 't') ) blob_appendf(pBody, " * Ticket changes\n");
2748 if( strchr(zSub, 'w') ) blob_appendf(pBody, " * Wiki changes\n");
2749 blob_appendf(pBody,
2750 "\nTo continue receiving email notifications, click the following link\n"
2751 "\n %s/renew/%s\n\n",
2752 zUrl, zCode
2753 );
2754 blob_appendf(pBody,
2755 "If you take no action, your subscription will expire and you will be\n"
2756 "unsubscribed in about a week. To make other changes or to unsubscribe\n"
2757 "immediately, visit the following webpage:\n\n"
2758 " %s/alerts/%s\n\n",
2759 zUrl, zCode
2760 );
2761 }
2762
2763 #if INTERFACE
2764 /*
2765 ** Flags for alert_send_alerts()
2766 */
2767 #define SENDALERT_DIGEST 0x0001 /* Send a digest */
2768 #define SENDALERT_PRESERVE 0x0002 /* Do not mark the task as done */
2769 #define SENDALERT_STDOUT 0x0004 /* Print emails instead of sending */
2770 #define SENDALERT_TRACE 0x0008 /* Trace operation for debugging */
2771 #define SENDALERT_RENEWAL 0x0010 /* Send renewal notices */
2772
2773 #endif /* INTERFACE */
2774
2775 /*
2776 ** Minimum number of days between renewal messages
2777 */
2778 #define ALERT_RENEWAL_MSG_FREQUENCY 7 /* Do renewals at most once/week */
2779
2780 /*
2781 ** Send alert emails to subscribers.
2782 **
2783 ** This procedure is run by either the backoffice, or in response to the
2784 ** "fossil alerts send" command. Details of operation are controlled by
@@ -2691,10 +2822,11 @@
2822 const char *zRepoName;
2823 const char *zFrom;
2824 const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
2825 AlertSender *pSender = 0;
2826 u32 senderFlags = 0;
2827 int iInterval = 0; /* Subscription renewal interval */
2828
2829 if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
2830 alert_schema(0);
2831 if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
2832 zUrl = db_get("email-url",0);
@@ -2740,11 +2872,11 @@
2872
2873 /* Step 2: compute EmailEvent objects for every notification that
2874 ** needs sending.
2875 */
2876 pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2877 if( nEvent==0 ) goto send_alert_expiration_warnings;
2878
2879 /* Step 4a: Update the pending_alerts table to designate the
2880 ** alerts as having all been sent. This is done *before* step (3)
2881 ** so that a crash will not cause alerts to be sent multiple times.
2882 ** Better a missed alert than being spammed with hundreds of alerts
@@ -2775,13 +2907,16 @@
2907 " hex(subscriberCode)," /* 0 */
2908 " semail," /* 1 */
2909 " ssub," /* 2 */
2910 " fullcap(user.cap)" /* 3 */
2911 " FROM subscriber LEFT JOIN user ON (login=suname)"
2912 " WHERE sverified"
2913 " AND NOT sdonotcall"
2914 " AND sdigest IS %s"
2915 " AND coalesce(subscriber.lastContact,subscriber.mtime)>=%d",
2916 zDigest/*safe-for-%s*/,
2917 db_get_int("email-renew-cutoff",0)
2918 );
2919 while( db_step(&q)==SQLITE_ROW ){
2920 const char *zCode = db_column_text(&q, 0);
2921 const char *zSub = db_column_text(&q, 2);
2922 const char *zEmail = db_column_text(&q, 1);
@@ -2865,10 +3000,62 @@
3000
3001 /* Step 4b: Update the pending_alerts table to remove all of the
3002 ** alerts that have been completely sent.
3003 */
3004 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");
3005
3006 /* Send renewal messages to subscribers whose subscriptions are about
3007 ** to expire. Only do this if:
3008 **
3009 ** (1) email-renew-interval is 14 or greater (or in other words if
3010 ** subscription expiration is enabled).
3011 **
3012 ** (2) The SENDALERT_RENEWAL flag is set
3013 */
3014 send_alert_expiration_warnings:
3015 if( (flags & SENDALERT_RENEWAL)!=0
3016 && (iInterval = db_get_int("email-renew-interval",0))>=14
3017 ){
3018 int iNow = (int)(time(0)/86400);
3019 int iOldWarn = db_get_int("email-renew-warning",0);
3020 int iNewWarn = iNow - iInterval + ALERT_RENEWAL_MSG_FREQUENCY;
3021 if( iNewWarn >= iOldWarn + ALERT_RENEWAL_MSG_FREQUENCY ){
3022 db_prepare(&q,
3023 "SELECT"
3024 " hex(subscriberCode)," /* 0 */
3025 " lastContact," /* 1 */
3026 " semail," /* 2 */
3027 " ssub" /* 3 */
3028 " FROM subscriber"
3029 " WHERE lastContact<=%d AND lastContact>%d"
3030 " AND NOT sdonotcall"
3031 " AND length(sdigest)>0",
3032 iNewWarn, iOldWarn
3033 );
3034 while( db_step(&q)==SQLITE_ROW ){
3035 Blob hdr, body;
3036 blob_init(&hdr, 0, 0);
3037 blob_init(&body, 0, 0);
3038 alert_renewal_msg(&hdr, &body,
3039 db_column_text(&q,0),
3040 db_column_int(&q,1),
3041 db_column_text(&q,2),
3042 db_column_text(&q,3),
3043 zRepoName, zUrl);
3044 alert_send(pSender,&hdr,&body,0);
3045 blob_reset(&hdr);
3046 blob_reset(&body);
3047 }
3048 db_finalize(&q);
3049 if( (flags & SENDALERT_PRESERVE)==0 ){
3050 if( iOldWarn>0 ){
3051 db_set_int("email-renew-cutoff", iOldWarn, 0);
3052 }
3053 db_set_int("email-renew-warning", iNewWarn, 0);
3054 }
3055 }
3056 }
3057
3058 send_alert_done:
3059 alert_sender_free(pSender);
3060 if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
3061 return nSent;
@@ -2891,11 +3078,11 @@
3078 if( !alert_tables_exist() ) return 0;
3079 nSent = alert_send_alerts(mFlags);
3080 iJulianDay = db_int(0, "SELECT julianday('now')");
3081 if( iJulianDay>db_get_int("email-last-digest",0) ){
3082 db_set_int("email-last-digest",iJulianDay,0);
3083 nSent += alert_send_alerts(SENDALERT_DIGEST|SENDALERT_RENEWAL|mFlags);
3084 }
3085 return nSent;
3086 }
3087
3088 /*
3089
M src/stat.c
+4 -1
--- src/stat.c
+++ src/stat.c
@@ -59,10 +59,11 @@
5959
 */
6060
 void stats_for_email(void){
6161
 const char *zDest = db_get("email-send-method",0);
6262
 int nSub, nASub, nPend, nDPend;
6363
 const char *zDir, *zDb, *zCmd, *zRelay;
64
+ int iCutoff;
6465
 @ <tr><th>Outgoing&nbsp;Email:</th><td>
6566
 if( fossil_strcmp(zDest,"pipe")==0
6667
 && (zCmd = db_get("email-send-command",0))!=0
6768
 ){
6869
 @ Piped to command "%h(zCmd)"
@@ -110,12 +111,14 @@
110111
 @ <tr><th><a href="%R/subscribers">Subscribers:</a></th><td>
111112
 }else{
112113
 @ <tr><th>Subscribers:</th><td>
113114
 }
114115
 nSub = db_int(0, "SELECT count(*) FROM subscriber");
116
+ iCutoff = db_get_int("email-renew-cutoff",0);
115117
 nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
116
- " AND NOT sdonotcall AND length(ssub)>1");
118
+ " AND NOT sdonotcall AND length(ssub)>1"
119
+ " AND lastContact>=%d;", iCutoff);
117120
 @ %,d(nASub) active, %,d(nSub) total
118121
 @ </td></tr>
119122
 }
120123
121124
 /*
122125
--- src/stat.c
+++ src/stat.c
@@ -59,10 +59,11 @@
59 */
60 void stats_for_email(void){
61 const char *zDest = db_get("email-send-method",0);
62 int nSub, nASub, nPend, nDPend;
63 const char *zDir, *zDb, *zCmd, *zRelay;
 
64 @ <tr><th>Outgoing&nbsp;Email:</th><td>
65 if( fossil_strcmp(zDest,"pipe")==0
66 && (zCmd = db_get("email-send-command",0))!=0
67 ){
68 @ Piped to command "%h(zCmd)"
@@ -110,12 +111,14 @@
110 @ <tr><th><a href="%R/subscribers">Subscribers:</a></th><td>
111 }else{
112 @ <tr><th>Subscribers:</th><td>
113 }
114 nSub = db_int(0, "SELECT count(*) FROM subscriber");
 
115 nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
116 " AND NOT sdonotcall AND length(ssub)>1");
 
117 @ %,d(nASub) active, %,d(nSub) total
118 @ </td></tr>
119 }
120
121 /*
122
--- src/stat.c
+++ src/stat.c
@@ -59,10 +59,11 @@
59 */
60 void stats_for_email(void){
61 const char *zDest = db_get("email-send-method",0);
62 int nSub, nASub, nPend, nDPend;
63 const char *zDir, *zDb, *zCmd, *zRelay;
64 int iCutoff;
65 @ <tr><th>Outgoing&nbsp;Email:</th><td>
66 if( fossil_strcmp(zDest,"pipe")==0
67 && (zCmd = db_get("email-send-command",0))!=0
68 ){
69 @ Piped to command "%h(zCmd)"
@@ -110,12 +111,14 @@
111 @ <tr><th><a href="%R/subscribers">Subscribers:</a></th><td>
112 }else{
113 @ <tr><th>Subscribers:</th><td>
114 }
115 nSub = db_int(0, "SELECT count(*) FROM subscriber");
116 iCutoff = db_get_int("email-renew-cutoff",0);
117 nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
118 " AND NOT sdonotcall AND length(ssub)>1"
119 " AND lastContact>=%d;", iCutoff);
120 @ %,d(nASub) active, %,d(nSub) total
121 @ </td></tr>
122 }
123
124 /*
125
M www/alerts.md
+6 -6
--- www/alerts.md
+++ www/alerts.md
@@ -551,10 +551,11 @@
551551
 Web pages available to users and subscribers:
552552
553553
 * The [`/subscribe`](/help?cmd=/subscribe) page
554554
 * The [`/alerts`](/help?cmd=/alerts) page
555555
 * The [`/unsubscribe`](/help?cmd=/unsubscribe) page
556
+ * The [`/renew`](/help?cmd=/renew) page
556557
 * The [`/contact_admin`](/help?cmd=/contact_admin) page
557558
558559
 Administrator-only web pages:
559560
560561
 * The [`/setup_notification`](/help?cmd=/setup_notification) page
@@ -574,11 +575,11 @@
574575
575576
576577
 <a id="datades"></a>
577578
 ### Data Design
578579
579
-There are three new tables in the repository database, starting with
580
+There are two new tables in the repository database, starting with
580581
 Fossil 2.7. These tables are not created in new repositories by
581582
 default. The tables only come into existence as needed when email
582583
 alerts are configured and used.
583584
584585
@@ -599,15 +600,14 @@
599600
 and records timeline entries. In other words, there is one
600601
 row in the EVENT table for each possible timeline entry. The
601602
 PENDING\_ALERT table refers to EVENT table entries for which
602603
 we might need to send alert emails.
603604
604
- * <b>EMAIL\_BOUNCE</b> →
605
- This table is intended to record email bounce history so that
606
- subscribers with excessive bounces can be turned off. That
607
- logic has not yet been implemented so the EMAIL\_BOUNCE table
608
- is currently unused.
605
+There was a third table "EMAIL_BOUNCE" in Fossil versions 2.7 through 2.14.
606
+That table was intended to record email bounce history so that
607
+subscribers with excessive bounces can be turned off. But that feature
608
+was never implemented and the table was removed in Fossil 2.15.
609609
610610
 As pointed out above, ["subscribers" are distinct from "users"](#uvs).
611611
 The SUBSCRIBER.SUNAME field is the optional linkage between users and
612612
 subscribers.
613613
614614
--- www/alerts.md
+++ www/alerts.md
@@ -551,10 +551,11 @@
551 Web pages available to users and subscribers:
552
553 * The [`/subscribe`](/help?cmd=/subscribe) page
554 * The [`/alerts`](/help?cmd=/alerts) page
555 * The [`/unsubscribe`](/help?cmd=/unsubscribe) page
 
556 * The [`/contact_admin`](/help?cmd=/contact_admin) page
557
558 Administrator-only web pages:
559
560 * The [`/setup_notification`](/help?cmd=/setup_notification) page
@@ -574,11 +575,11 @@
574
575
576 <a id="datades"></a>
577 ### Data Design
578
579 There are three new tables in the repository database, starting with
580 Fossil 2.7. These tables are not created in new repositories by
581 default. The tables only come into existence as needed when email
582 alerts are configured and used.
583
584
@@ -599,15 +600,14 @@
599 and records timeline entries. In other words, there is one
600 row in the EVENT table for each possible timeline entry. The
601 PENDING\_ALERT table refers to EVENT table entries for which
602 we might need to send alert emails.
603
604 * <b>EMAIL\_BOUNCE</b> →
605 This table is intended to record email bounce history so that
606 subscribers with excessive bounces can be turned off. That
607 logic has not yet been implemented so the EMAIL\_BOUNCE table
608 is currently unused.
609
610 As pointed out above, ["subscribers" are distinct from "users"](#uvs).
611 The SUBSCRIBER.SUNAME field is the optional linkage between users and
612 subscribers.
613
614
--- www/alerts.md
+++ www/alerts.md
@@ -551,10 +551,11 @@
551 Web pages available to users and subscribers:
552
553 * The [`/subscribe`](/help?cmd=/subscribe) page
554 * The [`/alerts`](/help?cmd=/alerts) page
555 * The [`/unsubscribe`](/help?cmd=/unsubscribe) page
556 * The [`/renew`](/help?cmd=/renew) page
557 * The [`/contact_admin`](/help?cmd=/contact_admin) page
558
559 Administrator-only web pages:
560
561 * The [`/setup_notification`](/help?cmd=/setup_notification) page
@@ -574,11 +575,11 @@
575
576
577 <a id="datades"></a>
578 ### Data Design
579
580 There are two new tables in the repository database, starting with
581 Fossil 2.7. These tables are not created in new repositories by
582 default. The tables only come into existence as needed when email
583 alerts are configured and used.
584
585
@@ -599,15 +600,14 @@
600 and records timeline entries. In other words, there is one
601 row in the EVENT table for each possible timeline entry. The
602 PENDING\_ALERT table refers to EVENT table entries for which
603 we might need to send alert emails.
604
605 There was a third table "EMAIL_BOUNCE" in Fossil versions 2.7 through 2.14.
606 That table was intended to record email bounce history so that
607 subscribers with excessive bounces can be turned off. But that feature
608 was never implemented and the table was removed in Fossil 2.15.
 
609
610 As pointed out above, ["subscribers" are distinct from "users"](#uvs).
611 The SUBSCRIBER.SUNAME field is the optional linkage between users and
612 subscribers.
613
614

Keyboard Shortcuts

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