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: |
34d45c55b9f776e54ae377631d62106c |
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
Changes to src/alerts.c.
︙ | ︙ | |||
289 290 291 292 293 294 295 | @ 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> | | | | | > | 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 | ** 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 ** | > > | > | | | | | 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 | 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" | > > > > > > > > > > > > > > > > > > > | > | 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 | " sdonotcall," /* 2 */ " sdigest," /* 3 */ " ssub," /* 4 */ " smip," /* 5 */ " suname," /* 6 */ " datetime(mtime,'unixepoch')," /* 7 */ " datetime(sctime,'unixepoch')," /* 8 */ | | > > | 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 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 ){ @ <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 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)) ← \ @ %,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 ){ @ <a href='%R/setup_uedit?id=%d(uid)'>\ @ (login info for %h(suname))</a>\ |
︙ | ︙ | |||
1961 1962 1963 1964 1965 1966 1967 | db_commit_transaction(); return; } /* ** WEBPAGE: renew ** | | | | | > > | 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 | 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> | | > > > > > > > > | 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 | ** 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 | | > | > | > > > > > > | 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 | ); } /* Step 2: compute EmailEvent objects for every notification that ** needs sending. */ pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0); | | | 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 | db_prepare(&q, "SELECT" " hex(subscriberCode)," /* 0 */ " semail," /* 1 */ " ssub," /* 2 */ " fullcap(user.cap)" /* 3 */ " FROM subscriber LEFT JOIN user ON (login=suname)" | | > | > | > | 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 | 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); | | | 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 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 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 | @ </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" | > | > | 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 | 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 | | | 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 | 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. | | | | | < | 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> |
︙ | ︙ |