Fossil

Check-in [34d45c55]
Login

Check-in [34d45c55]

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

Overview
Comment:Merge into trunk the enhancements that allow an admin to set an expiration time for email notification subscriptions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 34d45c55b9f776e54ae377631d62106cb354f3728f666a26b88b527ccfd5b60a
User & Date: drh 2021-05-31 23:33:09
Context
2021-06-02
00:09
Update the custom MinGW makefile. ... (check-in: dc47bf4b user: mistachkin tags: trunk)
2021-05-31
23:33
Merge into trunk the enhancements that allow an admin to set an expiration time for email notification subscriptions. ... (check-in: 34d45c55 user: drh tags: trunk)
23:31
Take the email-renew-cutoff into account when computing the number of active subscribers. ... (Closed-Leaf check-in: fc10e775 user: drh tags: time-limited-subscriptions)
03:26
The /ci_tags page now adapts its header label based on the type of artifact hash passed to it (since it works as-is with hashes other than checkins). ... (check-in: 41f0838c user: stephan tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alerts.c.

289
290
291
292
293
294
295
296
297
298
299
300
301
302

303
304
305
306
307
308
309
  @ included in square brackets.  Examples: "[fossil-src]", "[sqlite-src]".
  @ (Property: "email-subname")</p>
  @ <hr>

  entry_attribute("Subscription Renewal Interval In Days", 8,
                  "email-renew-interval", "eri", "", 0);
  @ <p>
  @ If this value is a positive integer N, then email notification
  @ subscriptions will be suspended N days after the last known
  @ interaction with the user.  This prevents sending notifications
  @ to abandoned accounts.  If a subscription gets close to expiring
  @ a separate email goes out with the daily digest that prompts the
  @ subscriber to click on a link to the "/renew" webpage in order to
  @ extend their subscription.

  @ (Property: "email-renew-interval")</p>
  @ <hr>

  multiple_choice_attribute("Email Send Method", "email-send-method", "esm",
       "off", count(azSendMethods)/2, azSendMethods);
  @ <p>How to send email.  Requires auxiliary information from the fields
  @ that follow.  Hint: Use the <a href="%R/announce">/announce</a> page







|
|

|


|
>







289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
  @ included in square brackets.  Examples: "[fossil-src]", "[sqlite-src]".
  @ (Property: "email-subname")</p>
  @ <hr>

  entry_attribute("Subscription Renewal Interval In Days", 8,
                  "email-renew-interval", "eri", "", 0);
  @ <p>
  @ If this value is a integer N greater than or equal to 14, then email
  @ notification subscriptions will be suspended N days after the last known
  @ interaction with the user.  This prevents sending notifications
  @ to abandoned accounts.  If a subscription comes within 7 days of expiring,
  @ a separate email goes out with the daily digest that prompts the
  @ subscriber to click on a link to the "/renew" webpage in order to
  @ extend their subscription.  Subscriptions never expire if this setting
  @ is less than 14 or is an empty string.
  @ (Property: "email-renew-interval")</p>
  @ <hr>

  multiple_choice_attribute("Email Send Method", "email-send-method", "esm",
       "off", count(azSendMethods)/2, azSendMethods);
  @ <p>How to send email.  Requires auxiliary information from the fields
  @ that follow.  Hint: Use the <a href="%R/announce">/announce</a> page
966
967
968
969
970
971
972




























973
974
975
976
977
978
979
*/
/*
** SETTING: email-subname             width=16
** This is a short name used to identifies the repository in the Subject:
** line of email alerts. Traditionally this name is included in square
** brackets. Examples: "[fossil-src]", "[sqlite-src]".
*/




























/*
** SETTING: email-send-method         width=5 default=off sensitive
** Determine the method used to send email.  Allowed values are
** "off", "relay", "pipe", "dir", "db", and "stdout".  The "off" value
** means no email is ever sent.  The "relay" value means emails are sent
** to an Mail Sending Agent using SMTP located at email-send-relayhost.
** The "pipe" value means email messages are piped into a command 







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







967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
*/
/*
** SETTING: email-subname             width=16
** This is a short name used to identifies the repository in the Subject:
** line of email alerts. Traditionally this name is included in square
** brackets. Examples: "[fossil-src]", "[sqlite-src]".
*/
/*
** SETTING: email-renew-interval      width=16
** If this setting as an integer N that is 14 or greater then email
** notification is suspected for subscriptions that have a "last contact
** time" of more than N days ago.  The "last contact time" is recorded
** in the SUBSCRIBER.LASTCONTACT entry of the database.  Logging in,
** sending a forum post, editing a wiki page, changing subscription settings
** at /alerts, or visiting /renew all update the last contact time.
** If this setting is not an integer value or is less than 14 or undefined,
** then subscriptions never expire.
*/
/* X-VARIABLE:  email-renew-warning
** X-VARIABLE:  email-renew-cutoff
**
** These CONFIG table entries are not considered "settings" since their
** values are computed and updated automatically.
**
** email-renew-cutoff is the lastContact cutoff for subscription.  It
** is measured in days since 1970-01-01.  If The lastContact time for
** a subscription is less than email-renew-cutoff, then now new emails
** are sent to the subscriber.
**
** email-renew-warning is the time (in days since 1970-01-01) when the
** last batch of "your subscription is about to expire" emails were
** sent out.
**
** email-renew-cutoff is normally 7 days behind email-renew-warning.  
*/
/*
** SETTING: email-send-method         width=5 default=off sensitive
** Determine the method used to send email.  Allowed values are
** "off", "relay", "pipe", "dir", "db", and "stdout".  The "off" value
** means no email is ever sent.  The "relay" value means emails are sent
** to an Mail Sending Agent using SMTP located at email-send-relayhost.
** The "pipe" value means email messages are piped into a command 
1030
1031
1032
1033
1034
1035
1036


1037
1038
1039
1040
1041
1042
1043
1044
1045

1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
**    send                    Compose and send pending email alerts.
**                            Some installations may want to do this via
**                            a cron-job to make sure alerts are sent
**                            in a timely manner.
**                            Options:
**
**                               --digest     Send digests


**                               --test       Write to standard output
**
**    settings [NAME VALUE]   With no arguments, list all email settings.
**                            Or change the value of a single email setting.
**
**    status                  Report on the status of the email alert
**                            subsystem
**
**    subscribers [PATTERN]   List all subscribers matching PATTERN.

**
**    test-message TO [OPTS]  Send a single email message using whatever
**                            email sending mechanism is currently configured.
**                            Use this for testing the email notification
**                            configuration.  Options:
**
**                              --body FILENAME
**                              --smtp-trace
**                              --stdout
**                              -S|--subject SUBJECT
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
void alert_cmd(void){
  const char *zCmd;
  int nCmd;
  db_find_and_open_repository(0, 0);







>
>








|
>






|
|
|
|







1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
**    send                    Compose and send pending email alerts.
**                            Some installations may want to do this via
**                            a cron-job to make sure alerts are sent
**                            in a timely manner.
**                            Options:
**
**                               --digest     Send digests
**                               --renewal    Send subscription renewal
**                                            notices
**                               --test       Write to standard output
**
**    settings [NAME VALUE]   With no arguments, list all email settings.
**                            Or change the value of a single email setting.
**
**    status                  Report on the status of the email alert
**                            subsystem
**
**    subscribers [PATTERN]   List all subscribers matching PATTERN.  Either
**                            LIKE or GLOB wildcards can be used in PATTERN.
**
**    test-message TO [OPTS]  Send a single email message using whatever
**                            email sending mechanism is currently configured.
**                            Use this for testing the email notification
**                            configuration.  Options:
**
**                              --body FILENAME         Content from FILENAME
**                              --smtp-trace            Trace SMTP processing
**                              --stdout                Send msg to stdout
**                              -S|--subject SUBJECT    Message "subject:"
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
void alert_cmd(void){
  const char *zCmd;
  int nCmd;
  db_find_and_open_repository(0, 0);
1106
1107
1108
1109
1110
1111
1112

1113
1114
1115
1116
1117
1118
1119
      );
      alert_schema(0);
    }
  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;

    if( find_option("test",0,0)!=0 ){
      eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
    }
    verify_all_options();
    alert_send_alerts(eFlags);
  }else
  if( strncmp(zCmd, "settings", nCmd)==0 ){







>







1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
      );
      alert_schema(0);
    }
  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
    if( find_option("renewal",0,0)!=0 ) eFlags |= SENDALERT_RENEWAL;
    if( find_option("test",0,0)!=0 ){
      eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
    }
    verify_all_options();
    alert_send_alerts(eFlags);
  }else
  if( strncmp(zCmd, "settings", nCmd)==0 ){
1135
1136
1137
1138
1139
1140
1141


1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
















1157
1158

1159
1160

1161
1162
1163
1164
1165
1166
1167
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
  }else
  if( strncmp(zCmd, "status", nCmd)==0 ){


    int nSetting, n;
    static const char *zFmt = "%-29s %d\n";
    const Setting *pSetting = setting_info(&nSetting);
    db_open_config(1, 0);
    verify_all_options();
    if( g.argc!=3 ) usage("status");
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
















    n = db_int(0,"SELECT count(*) FROM subscriber");
    fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);

    n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
                   " AND NOT sdonotcall AND length(ssub)>1");

    fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
  }else
  if( strncmp(zCmd, "subscribers", nCmd)==0 ){
    Stmt q;
    verify_all_options();
    if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
    if( g.argc==4 ){







>
>















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


>

|
>







1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
  }else
  if( strncmp(zCmd, "status", nCmd)==0 ){
    Stmt q;
    int iCutoff;
    int nSetting, n;
    static const char *zFmt = "%-29s %d\n";
    const Setting *pSetting = setting_info(&nSetting);
    db_open_config(1, 0);
    verify_all_options();
    if( g.argc!=3 ) usage("status");
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
    db_prepare(&q,
       "SELECT"
       " name,"
       " value,"
       " now()/86400-value,"
       " date(value*86400,'unixepoch')"
       " FROM repository.config"
       " WHERE name in ('email-renew-warning','email-renew-cutoff');");
    while( db_step(&q)==SQLITE_ROW ){
      fossil_print("%-29s %-6d (%d days ago on %s)\n",
         db_column_text(&q, 0),
         db_column_int(&q, 1),
         db_column_int(&q, 2),
         db_column_text(&q, 3));
    }
    db_finalize(&q);
    n = db_int(0,"SELECT count(*) FROM subscriber");
    fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
    iCutoff = db_get_int("email-renew-cutoff", 0);
    n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
                  " AND NOT sdonotcall AND length(ssub)>1"
                  " AND lastContact>=%d", iCutoff);
    fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
  }else
  if( strncmp(zCmd, "subscribers", nCmd)==0 ){
    Stmt q;
    verify_all_options();
    if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
    if( g.argc==4 ){
1788
1789
1790
1791
1792
1793
1794
1795


1796
1797
1798
1799
1800
1801
1802
    "  sdonotcall,"                   /* 2 */
    "  sdigest,"                      /* 3 */
    "  ssub,"                         /* 4 */
    "  smip,"                         /* 5 */
    "  suname,"                       /* 6 */
    "  datetime(mtime,'unixepoch'),"  /* 7 */
    "  datetime(sctime,'unixepoch')," /* 8 */
    "  hex(subscriberCode)"           /* 9 */


    " FROM subscriber WHERE subscriberId=%d", sid);
  if( db_step(&q)!=SQLITE_ROW ){
    db_finalize(&q);
    db_commit_transaction();
    cgi_redirect("subscribe");
    /*NOTREACHED*/
  }







|
>
>







1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
    "  sdonotcall,"                   /* 2 */
    "  sdigest,"                      /* 3 */
    "  ssub,"                         /* 4 */
    "  smip,"                         /* 5 */
    "  suname,"                       /* 6 */
    "  datetime(mtime,'unixepoch'),"  /* 7 */
    "  datetime(sctime,'unixepoch')," /* 8 */
    "  hex(subscriberCode),"          /* 9 */
    "  date(coalesce(lastContact*86400,mtime),'unixepoch'),"  /* 10 */
    "  now()/86400 - coalesce(lastContact,mtime/86400)"       /* 11 */
    " FROM subscriber WHERE subscriberId=%d", sid);
  if( db_step(&q)!=SQLITE_ROW ){
    db_finalize(&q);
    db_commit_transaction();
    cgi_redirect("subscribe");
    /*NOTREACHED*/
  }
1887
1888
1889
1890
1891
1892
1893





1894
1895
1896
1897
1898
1899
1900
    @  <td class='form_label'>IP Address:</td>
    @  <td>%h(smip)</td>
    @ </tr>
    @ <tr>
    @  <td class='form_label'>Subscriber&nbsp;Code:</td>
    @  <td>%h(db_column_text(&q,9))</td>
    @ <tr>





    @  <td class="form_label">User:</td>
    @  <td><input type="text" name="suname" value="%h(suname?suname:"")" \
    @  size="30">\
    uid = db_int(0, "SELECT uid FROM user WHERE login=%Q", suname);
    if( uid ){
      @ &nbsp;&nbsp;<a href='%R/setup_uedit?id=%d(uid)'>\
      @ (login info for %h(suname))</a>\







>
>
>
>
>







1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
    @  <td class='form_label'>IP Address:</td>
    @  <td>%h(smip)</td>
    @ </tr>
    @ <tr>
    @  <td class='form_label'>Subscriber&nbsp;Code:</td>
    @  <td>%h(db_column_text(&q,9))</td>
    @ <tr>
    @ <tr>
    @  <td class='form_label'>Last Contact:</td>
    @  <td>%h(db_column_text(&q,10)) &larr; \
    @      %,d(db_column_int(&q,11)) days ago</td>
    @ </tr>
    @  <td class="form_label">User:</td>
    @  <td><input type="text" name="suname" value="%h(suname?suname:"")" \
    @  size="30">\
    uid = db_int(0, "SELECT uid FROM user WHERE login=%Q", suname);
    if( uid ){
      @ &nbsp;&nbsp;<a href='%R/setup_uedit?id=%d(uid)'>\
      @ (login info for %h(suname))</a>\
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971


1972
1973
1974
1975
1976
1977
1978
  db_commit_transaction();
  return;
}

/*
** WEBPAGE: renew
**
** Users visit this page to update the lastContact date on their
** subscription.  This prevents their subscriptions from expiring.
**
** A valid subscriber code is supplied in the name= query parameter.


*/
void renewal_page(void){
  const char *zName = P("name");
  int iInterval = db_get_int("email-renew-interval", 0);
  Stmt s;
  int rc;








|
|
|
|
>
>







2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
  db_commit_transaction();
  return;
}

/*
** WEBPAGE: renew
**
** Users visit this page to update the last-contact date on their
** subscription.  The last-contact date is the day that the subscriber
** last interacted with the repository.  If the name= query parameter
** (or POST parameter) contains a valid subscriber code, then the last-contact
** subscription associated with that subscriber code is updated to be the
** current date.
*/
void renewal_page(void){
  const char *zName = P("name");
  int iInterval = db_get_int("email-renew-interval", 0);
  Stmt s;
  int rc;

2180
2181
2182
2183
2184
2185
2186




2187
2188
2189
2190
2191
2192
2193
void subscriber_list_page(void){
  Blob sql;
  Stmt q;
  sqlite3_int64 iNow;
  int nTotal;
  int nPending;
  int nDel = 0;




  if( alert_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.Admin ){
    login_needed(0);
    return;
  }
  alert_submenu_common();







>
>
>
>







2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
void subscriber_list_page(void){
  Blob sql;
  Stmt q;
  sqlite3_int64 iNow;
  int nTotal;
  int nPending;
  int nDel = 0;
  int iCutoff = db_get_int("email-renew-cutoff",0);
  int iWarning = db_get_int("email-renew-warning",0);
  char zCutoffClr[8];
  char zWarnClr[8];
  if( alert_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.Admin ){
    login_needed(0);
    return;
  }
  alert_submenu_common();
2238
2239
2240
2241
2242
2243
2244


2245
2246
2247
2248
2249
2250
2251
  if( P("only")!=0 ){
    blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
    style_submenu_element("Show All","%R/subscribers");
  }
  blob_append_sql(&sql," ORDER BY mtime DESC");
  db_prepare_blob(&q, &sql);
  iNow = time(0);


  @ <table border='1' class='sortable' \
  @ data-init-sort='6' data-column-types='tttttKKt'>
  @ <thead>
  @ <tr>
  @ <th>Email
  @ <th>Events
  @ <th>Digest-Only?







>
>







2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
  if( P("only")!=0 ){
    blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
    style_submenu_element("Show All","%R/subscribers");
  }
  blob_append_sql(&sql," ORDER BY mtime DESC");
  db_prepare_blob(&q, &sql);
  iNow = time(0);
  memcpy(zCutoffClr, hash_color("A"), sizeof(zCutoffClr));
  memcpy(zWarnClr, hash_color("HIJ"), sizeof(zWarnClr));
  @ <table border='1' class='sortable' \
  @ data-init-sort='6' data-column-types='tttttKKt'>
  @ <thead>
  @ <tr>
  @ <th>Email
  @ <th>Events
  @ <th>Digest-Only?
2271
2272
2273
2274
2275
2276
2277
2278








2279
2280
2281
2282
2283
2284
2285
    if( uid ){
      @ <td><a href='%R/setup_uedit?id=%d(uid)'>%h(zUname)</a>
    }else{
      @ <td>%h(zUname)</td>
    }
    @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
    @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
    @ <td data-sortkey='%010llx(iContact)'>%z(human_readable_age(rContact))</td>








    @ <td>%h(db_column_text(&q,7))</td>
    @ </tr>
  }
  @ </tbody></table>
  db_finalize(&q);
  style_table_sorter();
  style_finish_page();







|
>
>
>
>
>
>
>
>







2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
    if( uid ){
      @ <td><a href='%R/setup_uedit?id=%d(uid)'>%h(zUname)</a>
    }else{
      @ <td>%h(zUname)</td>
    }
    @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
    @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
    @ <td data-sortkey='%010llx(iContact)'>\
    if( iContact>iWarning ){
      @ <span>\
    }else if( iContact>iCutoff ){
      @ <span style='background-color:%s(zWarnClr);'>\
    }else{
      @ <span style='background-color:%s(zCutoffClr);'>\
    }
    @ %z(human_readable_age(rContact))</td>
    @ <td>%h(db_column_text(&q,7))</td>
    @ </tr>
  }
  @ </tbody></table>
  db_finalize(&q);
  style_table_sorter();
  style_finish_page();
2528
2529
2530
2531
2532
2533
2534
2535

2536
2537

2538






2539
2540
2541
2542
2543
2544
2545
** COMMAND:  test-alert
**
** Usage: %fossil test-alert EVENTID ...
**
** Generate the text of an email alert for all of the EVENTIDs
** listed on the command-line.  Or if no events are listed on the
** command line, generate text for all events named in the
** pending_alert table.

**
** This command is intended for testing and debugging the logic

** that generates email alert text.






**
** Options:
**
**      --digest           Generate digest alert text
**      --needmod          Assume all events are pending moderator approval
*/
void test_alert_cmd(void){







|
>

|
>
|
>
>
>
>
>
>







2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
** COMMAND:  test-alert
**
** Usage: %fossil test-alert EVENTID ...
**
** Generate the text of an email alert for all of the EVENTIDs
** listed on the command-line.  Or if no events are listed on the
** command line, generate text for all events named in the
** pending_alert table.  The text of the email alerts appears on
** standard output.
**
** This command is intended for testing and debugging Fossil itself,
** for example when enhancing the email alert system or fixing bugs
** in the email alert system.  If you are not making changes to the
** Fossil source code, this command is probably not useful to you.
**
** EVENTIDs are text.  The first character is 'c', 'f', 't', or 'w'
** for check-in, forum, ticket, or wiki.  The remaining text is a
** integer that references the EVENT.OBJID value for the event.
** Run /timeline?showid to see these OBJID values.
**
** Options:
**
**      --digest           Generate digest alert text
**      --needmod          Assume all events are pending moderator approval
*/
void test_alert_cmd(void){
2630
2631
2632
2633
2634
2635
2636









































2637
2638
2639
2640
2641
2642
2643
2644
2645

2646
2647
2648





2649
2650
2651
2652
2653
2654
2655
    db_multi_exec("REPLACE INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
  if( doAuto ){
    alert_backoffice(SENDALERT_TRACE|mFlags);
  }
}










































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


#endif /* INTERFACE */






/*
** Send alert emails to subscribers.
**
** This procedure is run by either the backoffice, or in response to the
** "fossil alerts send" command.  Details of operation are controlled by
** the flags parameter.
**







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









>



>
>
>
>
>







2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
    db_multi_exec("REPLACE INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
  if( doAuto ){
    alert_backoffice(SENDALERT_TRACE|mFlags);
  }
}

/*
** Construct the header and body for an email message that will alert
** a subscriber that their subscriptions are about to expire.
*/
static void alert_renewal_msg(
  Blob *pHdr,            /* Write email header here */
  Blob *pBody,           /* Write email body here */
  const char *zCode,     /* The subscriber code */
  int lastContact,       /* Last contact (days since 1970) */
  const char *zEAddr,    /* Subscriber email address.  Send to this. */
  const char *zSub,      /* Subscription codes */
  const char *zRepoName, /* Name of the sending Fossil repostory */
  const char *zUrl       /* URL for the sending Fossil repostory */
){
  blob_appendf(pHdr,"To: <%s>\r\n", zEAddr);
  blob_appendf(pHdr,"Subject: %s Subscription to %s expires soon\r\n",
    zRepoName, zUrl);
  blob_appendf(pBody,
    "You are currently receiving email notification of the following kinds\n"
    "of changes to the %s Fossil repository at %s:\n\n",
    zRepoName, zUrl
  );
  if( strchr(zSub, 'a') )  blob_appendf(pBody, "  *  Announcements\n");
  if( strchr(zSub, 'c') )  blob_appendf(pBody, "  *  Check-ins\n");
  if( strchr(zSub, 'f') )  blob_appendf(pBody, "  *  Forum posts\n");
  if( strchr(zSub, 't') )  blob_appendf(pBody, "  *  Ticket changes\n");
  if( strchr(zSub, 'w') )  blob_appendf(pBody, "  *  Wiki changes\n");
  blob_appendf(pBody,
    "\nTo continue receiving email notifications, click the following link\n"
    "\n  %s/renew/%s\n\n",
    zUrl, zCode
  );
  blob_appendf(pBody,
    "If you take no action, your subscription will expire and you will be\n"
    "unsubscribed in about a week.  To make other changes or to unsubscribe\n"
    "immediately, visit the following webpage:\n\n"
    "  %s/alerts/%s\n\n",
    zUrl, zCode
  );
}

#if INTERFACE
/*
** Flags for alert_send_alerts()
*/
#define SENDALERT_DIGEST      0x0001    /* Send a digest */
#define SENDALERT_PRESERVE    0x0002    /* Do not mark the task as done */
#define SENDALERT_STDOUT      0x0004    /* Print emails instead of sending */
#define SENDALERT_TRACE       0x0008    /* Trace operation for debugging */
#define SENDALERT_RENEWAL     0x0010    /* Send renewal notices */

#endif /* INTERFACE */

/*
** Minimum number of days between renewal messages
*/
#define ALERT_RENEWAL_MSG_FREQUENCY  7   /* Do renewals at most once/week */

/*
** Send alert emails to subscribers.
**
** This procedure is run by either the backoffice, or in response to the
** "fossil alerts send" command.  Details of operation are controlled by
** the flags parameter.
**
2689
2690
2691
2692
2693
2694
2695

2696
2697
2698
2699
2700
2701
2702
  Blob hdr, body;
  const char *zUrl;
  const char *zRepoName;
  const char *zFrom;
  const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
  AlertSender *pSender = 0;
  u32 senderFlags = 0;


  if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
  alert_schema(0);
  if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
  zUrl = db_get("email-url",0);
  if( zUrl==0 ) goto send_alert_done;
  zRepoName = db_get("email-subname",0);







>







2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
  Blob hdr, body;
  const char *zUrl;
  const char *zRepoName;
  const char *zFrom;
  const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
  AlertSender *pSender = 0;
  u32 senderFlags = 0;
  int iInterval = 0;              /* Subscription renewal interval */

  if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
  alert_schema(0);
  if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
  zUrl = db_get("email-url",0);
  if( zUrl==0 ) goto send_alert_done;
  zRepoName = db_get("email-subname",0);
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
    );
  }

  /* Step 2: compute EmailEvent objects for every notification that
  ** needs sending.
  */
  pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
  if( nEvent==0 ) goto send_alert_done;

  /* Step 4a: Update the pending_alerts table to designate the
  ** alerts as having all been sent.  This is done *before* step (3)
  ** so that a crash will not cause alerts to be sent multiple times.
  ** Better a missed alert than being spammed with hundreds of alerts
  ** due to a bug.
  */







|







2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
    );
  }

  /* Step 2: compute EmailEvent objects for every notification that
  ** needs sending.
  */
  pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
  if( nEvent==0 ) goto send_alert_expiration_warnings;

  /* Step 4a: Update the pending_alerts table to designate the
  ** alerts as having all been sent.  This is done *before* step (3)
  ** so that a crash will not cause alerts to be sent multiple times.
  ** Better a missed alert than being spammed with hundreds of alerts
  ** due to a bug.
  */
2773
2774
2775
2776
2777
2778
2779
2780

2781

2782

2783
2784
2785
2786
2787
2788
2789
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
     " ssub,"                 /* 2 */
     " fullcap(user.cap)"     /* 3 */
     " FROM subscriber LEFT JOIN user ON (login=suname)"
     " WHERE sverified AND NOT sdonotcall"

     "  AND sdigest IS %s",

     zDigest/*safe-for-%s*/

  );
  while( db_step(&q)==SQLITE_ROW ){
    const char *zCode = db_column_text(&q, 0);
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    const char *zCap = db_column_text(&q, 3);
    int nHit = 0;







|
>
|
>
|
>







2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
     " ssub,"                 /* 2 */
     " fullcap(user.cap)"     /* 3 */
     " FROM subscriber LEFT JOIN user ON (login=suname)"
     " WHERE sverified"
     "   AND NOT sdonotcall"
     "   AND sdigest IS %s"
     "   AND coalesce(subscriber.lastContact,subscriber.mtime)>=%d",
     zDigest/*safe-for-%s*/,
     db_get_int("email-renew-cutoff",0)
  );
  while( db_step(&q)==SQLITE_ROW ){
    const char *zCode = db_column_text(&q, 0);
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    const char *zCap = db_column_text(&q, 3);
    int nHit = 0;
2863
2864
2865
2866
2867
2868
2869




















































2870
2871
2872
2873
2874
2875
2876
  db_finalize(&q);
  alert_free_eventlist(pEvents);

  /* Step 4b: Update the pending_alerts table to remove all of the
  ** alerts that have been completely sent.
  */
  db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");





















































send_alert_done:
  alert_sender_free(pSender);
  if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
  return nSent;
}








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







2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
  db_finalize(&q);
  alert_free_eventlist(pEvents);

  /* Step 4b: Update the pending_alerts table to remove all of the
  ** alerts that have been completely sent.
  */
  db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");

  /* Send renewal messages to subscribers whose subscriptions are about
  ** to expire.  Only do this if:
  **
  **  (1)  email-renew-interval is 14 or greater (or in other words if 
  **       subscription expiration is enabled).
  **
  **  (2)  The SENDALERT_RENEWAL flag is set
  */
send_alert_expiration_warnings:
  if( (flags & SENDALERT_RENEWAL)!=0
   && (iInterval = db_get_int("email-renew-interval",0))>=14
  ){
    int iNow = (int)(time(0)/86400);
    int iOldWarn = db_get_int("email-renew-warning",0);
    int iNewWarn = iNow - iInterval + ALERT_RENEWAL_MSG_FREQUENCY;
    if( iNewWarn >= iOldWarn + ALERT_RENEWAL_MSG_FREQUENCY ){
      db_prepare(&q,
         "SELECT"
         "  hex(subscriberCode),"     /* 0 */
         "  lastContact,"             /* 1 */
         "  semail,"                  /* 2 */
         "  ssub"                     /* 3 */
         " FROM subscriber"
         " WHERE lastContact<=%d AND lastContact>%d"
         "   AND NOT sdonotcall"
         "   AND length(sdigest)>0",
         iNewWarn, iOldWarn
      );
      while( db_step(&q)==SQLITE_ROW ){
        Blob hdr, body;
        blob_init(&hdr, 0, 0);
        blob_init(&body, 0, 0);
        alert_renewal_msg(&hdr, &body, 
           db_column_text(&q,0),
           db_column_int(&q,1),
           db_column_text(&q,2),
           db_column_text(&q,3),
           zRepoName, zUrl);
        alert_send(pSender,&hdr,&body,0);
        blob_reset(&hdr);
        blob_reset(&body);
      }
      db_finalize(&q);
      if( (flags & SENDALERT_PRESERVE)==0 ){
        if( iOldWarn>0 ){
          db_set_int("email-renew-cutoff", iOldWarn, 0);
        }
        db_set_int("email-renew-warning", iNewWarn, 0);
      }
    }
  }

send_alert_done:
  alert_sender_free(pSender);
  if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
  return nSent;
}

2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
  int iJulianDay;
  int nSent = 0;
  if( !alert_tables_exist() ) return 0;
  nSent = alert_send_alerts(mFlags);
  iJulianDay = db_int(0, "SELECT julianday('now')");
  if( iJulianDay>db_get_int("email-last-digest",0) ){
    db_set_int("email-last-digest",iJulianDay,0);
    nSent += alert_send_alerts(SENDALERT_DIGEST|mFlags);
  }
  return nSent;
}

/*
** WEBPAGE: contact_admin
**







|







3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
  int iJulianDay;
  int nSent = 0;
  if( !alert_tables_exist() ) return 0;
  nSent = alert_send_alerts(mFlags);
  iJulianDay = db_int(0, "SELECT julianday('now')");
  if( iJulianDay>db_get_int("email-last-digest",0) ){
    db_set_int("email-last-digest",iJulianDay,0);
    nSent += alert_send_alerts(SENDALERT_DIGEST|SENDALERT_RENEWAL|mFlags);
  }
  return nSent;
}

/*
** WEBPAGE: contact_admin
**

Changes to src/stat.c.

57
58
59
60
61
62
63

64
65
66
67
68
69
70
/*
** Generate stats for the email notification subsystem.
*/
void stats_for_email(void){
  const char *zDest = db_get("email-send-method",0);
  int nSub, nASub, nPend, nDPend;
  const char *zDir, *zDb, *zCmd, *zRelay;

  @ <tr><th>Outgoing&nbsp;Email:</th><td>
  if( fossil_strcmp(zDest,"pipe")==0
   && (zCmd = db_get("email-send-command",0))!=0
  ){
    @ Piped to command "%h(zCmd)"
  }else
  if( fossil_strcmp(zDest,"db")==0







>







57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/*
** Generate stats for the email notification subsystem.
*/
void stats_for_email(void){
  const char *zDest = db_get("email-send-method",0);
  int nSub, nASub, nPend, nDPend;
  const char *zDir, *zDb, *zCmd, *zRelay;
  int iCutoff;
  @ <tr><th>Outgoing&nbsp;Email:</th><td>
  if( fossil_strcmp(zDest,"pipe")==0
   && (zCmd = db_get("email-send-command",0))!=0
  ){
    @ Piped to command "%h(zCmd)"
  }else
  if( fossil_strcmp(zDest,"db")==0
108
109
110
111
112
113
114

115
116

117
118
119
120
121
122
123
  @ </td></tr>
  if( g.perm.Admin ){
    @ <tr><th><a href="%R/subscribers">Subscribers:</a></th><td>
  }else{
    @ <tr><th>Subscribers:</th><td>
  }
  nSub = db_int(0, "SELECT count(*) FROM subscriber");

  nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
                   " AND NOT sdonotcall AND length(ssub)>1");

  @ %,d(nASub) active, %,d(nSub) total
  @ </td></tr>
}

/*
** WEBPAGE: stat
**







>

|
>







109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
  @ </td></tr>
  if( g.perm.Admin ){
    @ <tr><th><a href="%R/subscribers">Subscribers:</a></th><td>
  }else{
    @ <tr><th>Subscribers:</th><td>
  }
  nSub = db_int(0, "SELECT count(*) FROM subscriber");
  iCutoff = db_get_int("email-renew-cutoff",0);
  nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
                   " AND NOT sdonotcall AND length(ssub)>1"
                   " AND lastContact>=%d;", iCutoff);
  @ %,d(nASub) active, %,d(nSub) total
  @ </td></tr>
}

/*
** WEBPAGE: stat
**

Changes to www/alerts.md.

549
550
551
552
553
554
555

556
557
558
559
560
561
562
   *  The [`test-add-alerts`](/help?cmd=test-add-alerts) command

Web pages available to users and subscribers:

   *  The [`/subscribe`](/help?cmd=/subscribe) page
   *  The [`/alerts`](/help?cmd=/alerts) page
   *  The [`/unsubscribe`](/help?cmd=/unsubscribe) page

   *  The [`/contact_admin`](/help?cmd=/contact_admin) page

Administrator-only web pages:

   *  The [`/setup_notification`](/help?cmd=/setup_notification) page
   *  The [`/subscribers`](/help?cmd=/subscribers) page








>







549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
   *  The [`test-add-alerts`](/help?cmd=test-add-alerts) command

Web pages available to users and subscribers:

   *  The [`/subscribe`](/help?cmd=/subscribe) page
   *  The [`/alerts`](/help?cmd=/alerts) page
   *  The [`/unsubscribe`](/help?cmd=/unsubscribe) page
   *  The [`/renew`](/help?cmd=/renew) page
   *  The [`/contact_admin`](/help?cmd=/contact_admin) page

Administrator-only web pages:

   *  The [`/setup_notification`](/help?cmd=/setup_notification) page
   *  The [`/subscribers`](/help?cmd=/subscribers) page

572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
above sufficed for your purposes, feel free to skip this section, which
runs to the end of this document.


<a id="datades"></a>
### Data Design

There are three new tables in the repository database, starting with
Fossil 2.7.  These tables are not created in new repositories by
default.  The tables only come into existence as needed when email
alerts are configured and used.


  *  <b>SUBSCRIBER</b> →
     The subscriber table records the email address for people who







|







573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
above sufficed for your purposes, feel free to skip this section, which
runs to the end of this document.


<a id="datades"></a>
### Data Design

There are two new tables in the repository database, starting with
Fossil 2.7.  These tables are not created in new repositories by
default.  The tables only come into existence as needed when email
alerts are configured and used.


  *  <b>SUBSCRIBER</b> →
     The subscriber table records the email address for people who
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
     A pending\_alert always refers to an entry in the
     EVENT table.  The EVENT table is part of the standard schema
     and records timeline entries.  In other words, there is one
     row in the EVENT table for each possible timeline entry.  The
     PENDING\_ALERT table refers to EVENT table entries for which
     we might need to send alert emails.

  *  <b>EMAIL\_BOUNCE</b> 
     This table is intended to record email bounce history so that
     subscribers with excessive bounces can be turned off.  That
     logic has not yet been implemented so the EMAIL\_BOUNCE table
     is currently unused.

As pointed out above, ["subscribers" are distinct from "users"](#uvs).
The SUBSCRIBER.SUNAME field is the optional linkage between users and
subscribers.


<a id="stdout"></a>







|
|
|
|
<







598
599
600
601
602
603
604
605
606
607
608

609
610
611
612
613
614
615
     A pending\_alert always refers to an entry in the
     EVENT table.  The EVENT table is part of the standard schema
     and records timeline entries.  In other words, there is one
     row in the EVENT table for each possible timeline entry.  The
     PENDING\_ALERT table refers to EVENT table entries for which
     we might need to send alert emails.

There was a third table "EMAIL_BOUNCE" in Fossil versions 2.7 through 2.14.
That table was intended to record email bounce history so that
subscribers with excessive bounces can be turned off.  But that feature
was never implemented and the table was removed in Fossil 2.15.


As pointed out above, ["subscribers" are distinct from "users"](#uvs).
The SUBSCRIBER.SUNAME field is the optional linkage between users and
subscribers.


<a id="stdout"></a>