Fossil SCM

Add the subscriber.lastContact field for tracking the last contact time for each subscriber. Keep this field up-to-day. The field is currently used but the plan is to use it to optionally ask inactive subscribers to renew periodically.

drh 2021-05-28 18:02 trunk
Commit d7e10ce4da9d9ec639bf52b3c6fe2b901f526a12049e8e30cb8fef893fbce8bc
Parent 1df20b222bab06f…
4 files changed +52 -22 +13 -9 +1 -1 +3
~ src/alerts.c ~ src/login.c ~ src/rebuild.c ~ src/wiki.c
M src/alerts.c
+52 -22
--- src/alerts.c
+++ src/alerts.c
@@ -64,11 +64,12 @@
6464
 @ sdonotcall BOOLEAN, -- true for Do Not Call
6565
 @ sdigest BOOLEAN, -- true for daily digests only
6666
 @ ssub TEXT, -- baseline subscriptions
6767
 @ sctime INTDATE, -- When this entry was created. unixtime
6868
 @ mtime INTDATE, -- Last change. unixtime
69
-@ smip TEXT -- IP address of last change
69
+@ smip TEXT, -- IP address of last change
70
+@ lastContact INT -- Last contact. days since 1970
7071
 @ );
7172
 @ CREATE INDEX repository.subscriberUname
7273
 @ ON subscriber(suname) WHERE suname IS NOT NULL;
7374
 @
7475
 @ DROP TABLE IF EXISTS repository.pending_alert;
@@ -83,28 +84,33 @@
8384
 @ sentSep BOOLEAN DEFAULT false, -- individual alert sent
8485
 @ sentDigest BOOLEAN DEFAULT false, -- digest alert sent
8586
 @ sentMod BOOLEAN DEFAULT false -- pending moderation alert sent
8687
 @ ) WITHOUT ROWID;
8788
 @
89
+@ -- Obsolete table. No longer used.
8890
 @ DROP TABLE IF EXISTS repository.alert_bounce;
89
-@ -- Record bounced emails. If too many bounces are received within
90
-@ -- some defined time range, then cancel the subscription. Older
91
-@ -- entries are periodically purged.
92
-@ --
93
-@ CREATE TABLE repository.alert_bounce(
94
-@ subscriberId INTEGER, -- to whom the email was sent.
95
-@ sendTime INTEGER, -- seconds since 1970 when email was sent
96
-@ rcvdTime INTEGER -- seconds since 1970 when bounce was received
97
-@ );
9891
 ;
9992
10093
 /*
10194
 ** Return true if the email notification tables exist.
10295
 */
10396
 int alert_tables_exist(void){
10497
 return db_table_exists("repository", "subscriber");
10598
 }
99
+
100
+/*
101
+** Record the fact that user zUser has made contact with the repository.
102
+** This resets the subscription timeout on that user.
103
+*/
104
+void alert_user_contact(const char *zUser){
105
+ if( db_table_has_column("repository","subscriber","lastContact") ){
106
+ db_multi_exec(
107
+ "UPDATE subscriber SET lastContact=now()/86400 WHERE suname=%Q",
108
+ zUser
109
+ );
110
+ }
111
+}
106112
107113
 /*
108114
 ** Make sure the table needed for email notification exist in the repository.
109115
 **
110116
 ** If the bOnlyIfEnabled option is true, then tables are only created
@@ -116,16 +122,27 @@
116122
 && fossil_strcmp(db_get("email-send-method",0),"off")==0
117123
 ){
118124
 return; /* Don't create table for disabled email */
119125
 }
120126
 db_exec_sql(zAlertInit);
121
- }else if( !db_table_has_column("repository","pending_alert","sentMod") ){
122
- db_multi_exec(
123
- "ALTER TABLE repository.pending_alert"
124
- " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
125
- );
127
+ return;
126128
 }
129
+ if( db_table_has_column("repository","subscriber","lastContact") ){
130
+ return;
131
+ }
132
+ db_multi_exec(
133
+ "DROP TABLE IF EXISTS repository.alert_bounde;\n"
134
+ "ALTER TABLE repository.subscriber ADD COLUMN lastContact INT;\n"
135
+ "UPDATE subscriber SET lastContact=mtime/86400;"
136
+ );
137
+ if( db_table_has_column("repository","pending_alert","sentMod") ){
138
+ return;
139
+ }
140
+ db_multi_exec(
141
+ "ALTER TABLE repository.pending_alert"
142
+ " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
143
+ );
127144
 }
128145
129146
 /*
130147
 ** Enable triggers that automatically populate the pending_alert
131148
 ** table.
@@ -1391,12 +1408,12 @@
13911408
 if( g.perm.RdWiki && PB("sw") ) ssub[nsub++] = 'w';
13921409
 if( g.perm.RdForum && PB("sx") ) ssub[nsub++] = 'x';
13931410
 ssub[nsub] = 0;
13941411
 zCode = db_text(0,
13951412
 "INSERT INTO subscriber(semail,suname,"
1396
- " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
1397
- "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)"
1413
+ " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip,lastContact)"
1414
+ "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q,now()/86400)"
13981415
 "RETURNING hex(subscriberCode);",
13991416
 /* semail */ zEAddr,
14001417
 /* suname */ suname,
14011418
 /* sverified */ needCaptcha==0,
14021419
 /* sdigest */ PB("di"),
@@ -1641,10 +1658,11 @@
16411658
 int eErr = 0; /* Type of error */
16421659
 char *zErr = 0; /* Error message text */
16431660
 int sid = 0; /* Subscriber ID */
16441661
 int nName; /* Length of zName in bytes */
16451662
 char *zHalfCode; /* prefix of subscriberCode */
1663
+ int keepAlive = 0; /* True to update the last contact time */
16461664
16471665
 db_begin_transaction();
16481666
 if( alert_webpages_disabled() ){
16491667
 db_commit_transaction();
16501668
 return;
@@ -1665,10 +1683,11 @@
16651683
 sid = db_int(0,
16661684
 "SELECT CASE WHEN hex(subscriberCode) LIKE (%Q||'%%')"
16671685
 " THEN subscriberId ELSE 0 END"
16681686
 " FROM subscriber WHERE subscriberCode>=hextoblob(%Q)"
16691687
 " LIMIT 1", zName, zName);
1688
+ if( sid ) keepAlive = 1;
16701689
 }
16711690
 if( sid==0 && isLogin ){
16721691
 sid = db_int(0, "SELECT subscriberId FROM subscriber"
16731692
 " WHERE suname=%Q", g.zLogin);
16741693
 }
@@ -1697,11 +1716,12 @@
16971716
 blob_init(&update, "UPDATE subscriber SET", -1);
16981717
 blob_append_sql(&update,
16991718
 " sdonotcall=%d,"
17001719
 " sdigest=%d,"
17011720
 " ssub=%Q,"
1702
- " mtime=strftime('%%s','now'),"
1721
+ " mtime=now(),"
1722
+ " lastContact=now()/86400,"
17031723
 " smip=%Q",
17041724
 sdonotcall,
17051725
 sdigest,
17061726
 ssub,
17071727
 g.zIpAddr
@@ -1727,10 +1747,15 @@
17271747
 if( eErr==0 ){
17281748
 db_exec_sql(blob_str(&update));
17291749
 ssub = 0;
17301750
 }
17311751
 blob_reset(&update);
1752
+ }else if( keepAlive ){
1753
+ db_multi_exec(
1754
+ "UPDATE subscriber SET lastContact=now()/86400"
1755
+ " WHERE subscriberId=%d", sid
1756
+ );
17321757
 }
17331758
 if( P("delete")!=0 && cgi_csrf_safe(1) ){
17341759
 if( !PB("dodelete") ){
17351760
 eErr = 9;
17361761
 zErr = mprintf("Select this checkbox and press \"Unsubscribe\" again to"
@@ -2111,11 +2136,11 @@
21112136
 nPending = db_int(0, "SELECT count(*) FROM subscriber WHERE NOT sverified");
21122137
 if( nPending>0 && P("purge") && cgi_csrf_safe(0) ){
21132138
 int nNewPending;
21142139
 db_multi_exec(
21152140
 "DELETE FROM subscriber"
2116
- " WHERE NOT sverified AND mtime<0+strftime('%%s','now','-1 day')"
2141
+ " WHERE NOT sverified AND mtime<now()-86400"
21172142
 );
21182143
 nNewPending = db_int(0, "SELECT count(*) FROM subscriber"
21192144
 " WHERE NOT sverified");
21202145
 nDel = nPending - nNewPending;
21212146
 nPending = nNewPending;
@@ -2122,11 +2147,11 @@
21222147
 nTotal -= nDel;
21232148
 }
21242149
 if( nPending>0 ){
21252150
 @ <h1>%,d(nTotal) Subscribers, %,d(nPending) Pending</h1>
21262151
 if( nDel==0 && 0<db_int(0,"SELECT count(*) FROM subscriber"
2127
- " WHERE NOT sverified AND mtime<0+strftime('%%s','now','-1 day')")
2152
+ " WHERE NOT sverified AND mtime<now()-86400")
21282153
 ){
21292154
 style_submenu_element("Purge Pending","subscribers?purge");
21302155
 }
21312156
 }else{
21322157
 @ <h1>%,d(nTotal) Subscribers</h1>
@@ -2142,11 +2167,12 @@
21422167
 " suname," /* 3 */
21432168
 " sverified," /* 4 */
21442169
 " sdigest," /* 5 */
21452170
 " mtime," /* 6 */
21462171
 " date(sctime,'unixepoch')," /* 7 */
2147
- " (SELECT uid FROM user WHERE login=subscriber.suname)" /* 8 */
2172
+ " (SELECT uid FROM user WHERE login=subscriber.suname)," /* 8 */
2173
+ " coalesce(lastContact,mtime/86400)" /* 9 */
21482174
 " FROM subscriber"
21492175
 );
21502176
 if( P("only")!=0 ){
21512177
 blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
21522178
 style_submenu_element("Show All","%R/subscribers");
@@ -2153,27 +2179,30 @@
21532179
 }
21542180
 blob_append_sql(&sql," ORDER BY mtime DESC");
21552181
 db_prepare_blob(&q, &sql);
21562182
 iNow = time(0);
21572183
 @ <table border='1' class='sortable' \
2158
- @ data-init-sort='6' data-column-types='tttttKt'>
2184
+ @ data-init-sort='6' data-column-types='tttttKKt'>
21592185
 @ <thead>
21602186
 @ <tr>
21612187
 @ <th>Email
21622188
 @ <th>Events
21632189
 @ <th>Digest-Only?
21642190
 @ <th>User
21652191
 @ <th>Verified?
21662192
 @ <th>Last change
2193
+ @ <th>Last contact
21672194
 @ <th>Created
21682195
 @ </tr>
21692196
 @ </thead><tbody>
21702197
 while( db_step(&q)==SQLITE_ROW ){
21712198
 sqlite3_int64 iMtime = db_column_int64(&q, 6);
21722199
 double rAge = (iNow - iMtime)/86400.0;
21732200
 int uid = db_column_int(&q, 8);
21742201
 const char *zUname = db_column_text(&q, 3);
2202
+ sqlite3_int64 iContact = db_column_int64(&q, 9);
2203
+ double rContact = (iNow/86400) - iContact;
21752204
 @ <tr>
21762205
 @ <td><a href='%R/alerts?sid=%d(db_column_int(&q,0))'>\
21772206
 @ %h(db_column_text(&q,1))</a></td>
21782207
 @ <td>%h(db_column_text(&q,2))</td>
21792208
 @ <td>%s(db_column_int(&q,5)?"digest":"")</td>
@@ -2182,10 +2211,11 @@
21822211
 }else{
21832212
 @ <td>%h(zUname)</td>
21842213
 }
21852214
 @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
21862215
 @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
2216
+ @ <td data-sortkey='%010llx(iContact)'>%z(human_readable_age(rContact))</td>
21872217
 @ <td>%h(db_column_text(&q,7))</td>
21882218
 @ </tr>
21892219
 }
21902220
 @ </tbody></table>
21912221
 db_finalize(&q);
21922222
--- src/alerts.c
+++ src/alerts.c
@@ -64,11 +64,12 @@
64 @ sdonotcall BOOLEAN, -- true for Do Not Call
65 @ sdigest BOOLEAN, -- true for daily digests only
66 @ ssub TEXT, -- baseline subscriptions
67 @ sctime INTDATE, -- When this entry was created. unixtime
68 @ mtime INTDATE, -- Last change. unixtime
69 @ smip TEXT -- IP address of last change
 
70 @ );
71 @ CREATE INDEX repository.subscriberUname
72 @ ON subscriber(suname) WHERE suname IS NOT NULL;
73 @
74 @ DROP TABLE IF EXISTS repository.pending_alert;
@@ -83,28 +84,33 @@
83 @ sentSep BOOLEAN DEFAULT false, -- individual alert sent
84 @ sentDigest BOOLEAN DEFAULT false, -- digest alert sent
85 @ sentMod BOOLEAN DEFAULT false -- pending moderation alert sent
86 @ ) WITHOUT ROWID;
87 @
 
88 @ DROP TABLE IF EXISTS repository.alert_bounce;
89 @ -- Record bounced emails. If too many bounces are received within
90 @ -- some defined time range, then cancel the subscription. Older
91 @ -- entries are periodically purged.
92 @ --
93 @ CREATE TABLE repository.alert_bounce(
94 @ subscriberId INTEGER, -- to whom the email was sent.
95 @ sendTime INTEGER, -- seconds since 1970 when email was sent
96 @ rcvdTime INTEGER -- seconds since 1970 when bounce was received
97 @ );
98 ;
99
100 /*
101 ** Return true if the email notification tables exist.
102 */
103 int alert_tables_exist(void){
104 return db_table_exists("repository", "subscriber");
105 }
 
 
 
 
 
 
 
 
 
 
 
 
 
106
107 /*
108 ** Make sure the table needed for email notification exist in the repository.
109 **
110 ** If the bOnlyIfEnabled option is true, then tables are only created
@@ -116,16 +122,27 @@
116 && fossil_strcmp(db_get("email-send-method",0),"off")==0
117 ){
118 return; /* Don't create table for disabled email */
119 }
120 db_exec_sql(zAlertInit);
121 }else if( !db_table_has_column("repository","pending_alert","sentMod") ){
122 db_multi_exec(
123 "ALTER TABLE repository.pending_alert"
124 " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
125 );
126 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
127 }
128
129 /*
130 ** Enable triggers that automatically populate the pending_alert
131 ** table.
@@ -1391,12 +1408,12 @@
1391 if( g.perm.RdWiki && PB("sw") ) ssub[nsub++] = 'w';
1392 if( g.perm.RdForum && PB("sx") ) ssub[nsub++] = 'x';
1393 ssub[nsub] = 0;
1394 zCode = db_text(0,
1395 "INSERT INTO subscriber(semail,suname,"
1396 " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
1397 "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)"
1398 "RETURNING hex(subscriberCode);",
1399 /* semail */ zEAddr,
1400 /* suname */ suname,
1401 /* sverified */ needCaptcha==0,
1402 /* sdigest */ PB("di"),
@@ -1641,10 +1658,11 @@
1641 int eErr = 0; /* Type of error */
1642 char *zErr = 0; /* Error message text */
1643 int sid = 0; /* Subscriber ID */
1644 int nName; /* Length of zName in bytes */
1645 char *zHalfCode; /* prefix of subscriberCode */
 
1646
1647 db_begin_transaction();
1648 if( alert_webpages_disabled() ){
1649 db_commit_transaction();
1650 return;
@@ -1665,10 +1683,11 @@
1665 sid = db_int(0,
1666 "SELECT CASE WHEN hex(subscriberCode) LIKE (%Q||'%%')"
1667 " THEN subscriberId ELSE 0 END"
1668 " FROM subscriber WHERE subscriberCode>=hextoblob(%Q)"
1669 " LIMIT 1", zName, zName);
 
1670 }
1671 if( sid==0 && isLogin ){
1672 sid = db_int(0, "SELECT subscriberId FROM subscriber"
1673 " WHERE suname=%Q", g.zLogin);
1674 }
@@ -1697,11 +1716,12 @@
1697 blob_init(&update, "UPDATE subscriber SET", -1);
1698 blob_append_sql(&update,
1699 " sdonotcall=%d,"
1700 " sdigest=%d,"
1701 " ssub=%Q,"
1702 " mtime=strftime('%%s','now'),"
 
1703 " smip=%Q",
1704 sdonotcall,
1705 sdigest,
1706 ssub,
1707 g.zIpAddr
@@ -1727,10 +1747,15 @@
1727 if( eErr==0 ){
1728 db_exec_sql(blob_str(&update));
1729 ssub = 0;
1730 }
1731 blob_reset(&update);
 
 
 
 
 
1732 }
1733 if( P("delete")!=0 && cgi_csrf_safe(1) ){
1734 if( !PB("dodelete") ){
1735 eErr = 9;
1736 zErr = mprintf("Select this checkbox and press \"Unsubscribe\" again to"
@@ -2111,11 +2136,11 @@
2111 nPending = db_int(0, "SELECT count(*) FROM subscriber WHERE NOT sverified");
2112 if( nPending>0 && P("purge") && cgi_csrf_safe(0) ){
2113 int nNewPending;
2114 db_multi_exec(
2115 "DELETE FROM subscriber"
2116 " WHERE NOT sverified AND mtime<0+strftime('%%s','now','-1 day')"
2117 );
2118 nNewPending = db_int(0, "SELECT count(*) FROM subscriber"
2119 " WHERE NOT sverified");
2120 nDel = nPending - nNewPending;
2121 nPending = nNewPending;
@@ -2122,11 +2147,11 @@
2122 nTotal -= nDel;
2123 }
2124 if( nPending>0 ){
2125 @ <h1>%,d(nTotal) Subscribers, %,d(nPending) Pending</h1>
2126 if( nDel==0 && 0<db_int(0,"SELECT count(*) FROM subscriber"
2127 " WHERE NOT sverified AND mtime<0+strftime('%%s','now','-1 day')")
2128 ){
2129 style_submenu_element("Purge Pending","subscribers?purge");
2130 }
2131 }else{
2132 @ <h1>%,d(nTotal) Subscribers</h1>
@@ -2142,11 +2167,12 @@
2142 " suname," /* 3 */
2143 " sverified," /* 4 */
2144 " sdigest," /* 5 */
2145 " mtime," /* 6 */
2146 " date(sctime,'unixepoch')," /* 7 */
2147 " (SELECT uid FROM user WHERE login=subscriber.suname)" /* 8 */
 
2148 " FROM subscriber"
2149 );
2150 if( P("only")!=0 ){
2151 blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
2152 style_submenu_element("Show All","%R/subscribers");
@@ -2153,27 +2179,30 @@
2153 }
2154 blob_append_sql(&sql," ORDER BY mtime DESC");
2155 db_prepare_blob(&q, &sql);
2156 iNow = time(0);
2157 @ <table border='1' class='sortable' \
2158 @ data-init-sort='6' data-column-types='tttttKt'>
2159 @ <thead>
2160 @ <tr>
2161 @ <th>Email
2162 @ <th>Events
2163 @ <th>Digest-Only?
2164 @ <th>User
2165 @ <th>Verified?
2166 @ <th>Last change
 
2167 @ <th>Created
2168 @ </tr>
2169 @ </thead><tbody>
2170 while( db_step(&q)==SQLITE_ROW ){
2171 sqlite3_int64 iMtime = db_column_int64(&q, 6);
2172 double rAge = (iNow - iMtime)/86400.0;
2173 int uid = db_column_int(&q, 8);
2174 const char *zUname = db_column_text(&q, 3);
 
 
2175 @ <tr>
2176 @ <td><a href='%R/alerts?sid=%d(db_column_int(&q,0))'>\
2177 @ %h(db_column_text(&q,1))</a></td>
2178 @ <td>%h(db_column_text(&q,2))</td>
2179 @ <td>%s(db_column_int(&q,5)?"digest":"")</td>
@@ -2182,10 +2211,11 @@
2182 }else{
2183 @ <td>%h(zUname)</td>
2184 }
2185 @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
2186 @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
 
2187 @ <td>%h(db_column_text(&q,7))</td>
2188 @ </tr>
2189 }
2190 @ </tbody></table>
2191 db_finalize(&q);
2192
--- src/alerts.c
+++ src/alerts.c
@@ -64,11 +64,12 @@
64 @ sdonotcall BOOLEAN, -- true for Do Not Call
65 @ sdigest BOOLEAN, -- true for daily digests only
66 @ ssub TEXT, -- baseline subscriptions
67 @ sctime INTDATE, -- When this entry was created. unixtime
68 @ mtime INTDATE, -- Last change. unixtime
69 @ smip TEXT, -- IP address of last change
70 @ lastContact INT -- Last contact. days since 1970
71 @ );
72 @ CREATE INDEX repository.subscriberUname
73 @ ON subscriber(suname) WHERE suname IS NOT NULL;
74 @
75 @ DROP TABLE IF EXISTS repository.pending_alert;
@@ -83,28 +84,33 @@
84 @ sentSep BOOLEAN DEFAULT false, -- individual alert sent
85 @ sentDigest BOOLEAN DEFAULT false, -- digest alert sent
86 @ sentMod BOOLEAN DEFAULT false -- pending moderation alert sent
87 @ ) WITHOUT ROWID;
88 @
89 @ -- Obsolete table. No longer used.
90 @ DROP TABLE IF EXISTS repository.alert_bounce;
 
 
 
 
 
 
 
 
 
91 ;
92
93 /*
94 ** Return true if the email notification tables exist.
95 */
96 int alert_tables_exist(void){
97 return db_table_exists("repository", "subscriber");
98 }
99
100 /*
101 ** Record the fact that user zUser has made contact with the repository.
102 ** This resets the subscription timeout on that user.
103 */
104 void alert_user_contact(const char *zUser){
105 if( db_table_has_column("repository","subscriber","lastContact") ){
106 db_multi_exec(
107 "UPDATE subscriber SET lastContact=now()/86400 WHERE suname=%Q",
108 zUser
109 );
110 }
111 }
112
113 /*
114 ** Make sure the table needed for email notification exist in the repository.
115 **
116 ** If the bOnlyIfEnabled option is true, then tables are only created
@@ -116,16 +122,27 @@
122 && fossil_strcmp(db_get("email-send-method",0),"off")==0
123 ){
124 return; /* Don't create table for disabled email */
125 }
126 db_exec_sql(zAlertInit);
127 return;
 
 
 
 
128 }
129 if( db_table_has_column("repository","subscriber","lastContact") ){
130 return;
131 }
132 db_multi_exec(
133 "DROP TABLE IF EXISTS repository.alert_bounde;\n"
134 "ALTER TABLE repository.subscriber ADD COLUMN lastContact INT;\n"
135 "UPDATE subscriber SET lastContact=mtime/86400;"
136 );
137 if( db_table_has_column("repository","pending_alert","sentMod") ){
138 return;
139 }
140 db_multi_exec(
141 "ALTER TABLE repository.pending_alert"
142 " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
143 );
144 }
145
146 /*
147 ** Enable triggers that automatically populate the pending_alert
148 ** table.
@@ -1391,12 +1408,12 @@
1408 if( g.perm.RdWiki && PB("sw") ) ssub[nsub++] = 'w';
1409 if( g.perm.RdForum && PB("sx") ) ssub[nsub++] = 'x';
1410 ssub[nsub] = 0;
1411 zCode = db_text(0,
1412 "INSERT INTO subscriber(semail,suname,"
1413 " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip,lastContact)"
1414 "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q,now()/86400)"
1415 "RETURNING hex(subscriberCode);",
1416 /* semail */ zEAddr,
1417 /* suname */ suname,
1418 /* sverified */ needCaptcha==0,
1419 /* sdigest */ PB("di"),
@@ -1641,10 +1658,11 @@
1658 int eErr = 0; /* Type of error */
1659 char *zErr = 0; /* Error message text */
1660 int sid = 0; /* Subscriber ID */
1661 int nName; /* Length of zName in bytes */
1662 char *zHalfCode; /* prefix of subscriberCode */
1663 int keepAlive = 0; /* True to update the last contact time */
1664
1665 db_begin_transaction();
1666 if( alert_webpages_disabled() ){
1667 db_commit_transaction();
1668 return;
@@ -1665,10 +1683,11 @@
1683 sid = db_int(0,
1684 "SELECT CASE WHEN hex(subscriberCode) LIKE (%Q||'%%')"
1685 " THEN subscriberId ELSE 0 END"
1686 " FROM subscriber WHERE subscriberCode>=hextoblob(%Q)"
1687 " LIMIT 1", zName, zName);
1688 if( sid ) keepAlive = 1;
1689 }
1690 if( sid==0 && isLogin ){
1691 sid = db_int(0, "SELECT subscriberId FROM subscriber"
1692 " WHERE suname=%Q", g.zLogin);
1693 }
@@ -1697,11 +1716,12 @@
1716 blob_init(&update, "UPDATE subscriber SET", -1);
1717 blob_append_sql(&update,
1718 " sdonotcall=%d,"
1719 " sdigest=%d,"
1720 " ssub=%Q,"
1721 " mtime=now(),"
1722 " lastContact=now()/86400,"
1723 " smip=%Q",
1724 sdonotcall,
1725 sdigest,
1726 ssub,
1727 g.zIpAddr
@@ -1727,10 +1747,15 @@
1747 if( eErr==0 ){
1748 db_exec_sql(blob_str(&update));
1749 ssub = 0;
1750 }
1751 blob_reset(&update);
1752 }else if( keepAlive ){
1753 db_multi_exec(
1754 "UPDATE subscriber SET lastContact=now()/86400"
1755 " WHERE subscriberId=%d", sid
1756 );
1757 }
1758 if( P("delete")!=0 && cgi_csrf_safe(1) ){
1759 if( !PB("dodelete") ){
1760 eErr = 9;
1761 zErr = mprintf("Select this checkbox and press \"Unsubscribe\" again to"
@@ -2111,11 +2136,11 @@
2136 nPending = db_int(0, "SELECT count(*) FROM subscriber WHERE NOT sverified");
2137 if( nPending>0 && P("purge") && cgi_csrf_safe(0) ){
2138 int nNewPending;
2139 db_multi_exec(
2140 "DELETE FROM subscriber"
2141 " WHERE NOT sverified AND mtime<now()-86400"
2142 );
2143 nNewPending = db_int(0, "SELECT count(*) FROM subscriber"
2144 " WHERE NOT sverified");
2145 nDel = nPending - nNewPending;
2146 nPending = nNewPending;
@@ -2122,11 +2147,11 @@
2147 nTotal -= nDel;
2148 }
2149 if( nPending>0 ){
2150 @ <h1>%,d(nTotal) Subscribers, %,d(nPending) Pending</h1>
2151 if( nDel==0 && 0<db_int(0,"SELECT count(*) FROM subscriber"
2152 " WHERE NOT sverified AND mtime<now()-86400")
2153 ){
2154 style_submenu_element("Purge Pending","subscribers?purge");
2155 }
2156 }else{
2157 @ <h1>%,d(nTotal) Subscribers</h1>
@@ -2142,11 +2167,12 @@
2167 " suname," /* 3 */
2168 " sverified," /* 4 */
2169 " sdigest," /* 5 */
2170 " mtime," /* 6 */
2171 " date(sctime,'unixepoch')," /* 7 */
2172 " (SELECT uid FROM user WHERE login=subscriber.suname)," /* 8 */
2173 " coalesce(lastContact,mtime/86400)" /* 9 */
2174 " FROM subscriber"
2175 );
2176 if( P("only")!=0 ){
2177 blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
2178 style_submenu_element("Show All","%R/subscribers");
@@ -2153,27 +2179,30 @@
2179 }
2180 blob_append_sql(&sql," ORDER BY mtime DESC");
2181 db_prepare_blob(&q, &sql);
2182 iNow = time(0);
2183 @ <table border='1' class='sortable' \
2184 @ data-init-sort='6' data-column-types='tttttKKt'>
2185 @ <thead>
2186 @ <tr>
2187 @ <th>Email
2188 @ <th>Events
2189 @ <th>Digest-Only?
2190 @ <th>User
2191 @ <th>Verified?
2192 @ <th>Last change
2193 @ <th>Last contact
2194 @ <th>Created
2195 @ </tr>
2196 @ </thead><tbody>
2197 while( db_step(&q)==SQLITE_ROW ){
2198 sqlite3_int64 iMtime = db_column_int64(&q, 6);
2199 double rAge = (iNow - iMtime)/86400.0;
2200 int uid = db_column_int(&q, 8);
2201 const char *zUname = db_column_text(&q, 3);
2202 sqlite3_int64 iContact = db_column_int64(&q, 9);
2203 double rContact = (iNow/86400) - iContact;
2204 @ <tr>
2205 @ <td><a href='%R/alerts?sid=%d(db_column_int(&q,0))'>\
2206 @ %h(db_column_text(&q,1))</a></td>
2207 @ <td>%h(db_column_text(&q,2))</td>
2208 @ <td>%s(db_column_int(&q,5)?"digest":"")</td>
@@ -2182,10 +2211,11 @@
2211 }else{
2212 @ <td>%h(zUname)</td>
2213 }
2214 @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
2215 @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
2216 @ <td data-sortkey='%010llx(iContact)'>%z(human_readable_age(rContact))</td>
2217 @ <td>%h(db_column_text(&q,7))</td>
2218 @ </tr>
2219 }
2220 @ </tbody></table>
2221 db_finalize(&q);
2222
M src/login.c
+13 -9
--- src/login.c
+++ src/login.c
@@ -170,17 +170,21 @@
170170
 static void record_login_attempt(
171171
 const char *zUsername, /* Name of user logging in */
172172
 const char *zIpAddr, /* IP address from which they logged in */
173173
 int bSuccess /* True if the attempt was a success */
174174
 ){
175
- if( !db_get_boolean("access-log", 0) ) return;
176
- create_accesslog_table();
177
- db_multi_exec(
178
- "INSERT INTO accesslog(uname,ipaddr,success,mtime)"
179
- "VALUES(%Q,%Q,%d,julianday('now'));",
180
- zUsername, zIpAddr, bSuccess
181
- );
175
+ if( db_get_boolean("access-log", 0) ){
176
+ create_accesslog_table();
177
+ db_multi_exec(
178
+ "INSERT INTO accesslog(uname,ipaddr,success,mtime)"
179
+ "VALUES(%Q,%Q,%d,julianday('now'));",
180
+ zUsername, zIpAddr, bSuccess
181
+ );
182
+ }
183
+ if( bSuccess ){
184
+ alert_user_contact(zUsername);
185
+ }
182186
 }
183187
184188
 /*
185189
 ** Searches for the user ID matching the given name and password.
186190
 ** On success it returns a positive value. On error it returns 0.
@@ -1714,12 +1718,12 @@
17141718
 ssub[nsub] = 0;
17151719
 capability_free(pCap);
17161720
 /* Also add the user to the subscriber table. */
17171721
 zCode = db_text(0,
17181722
 "INSERT INTO subscriber(semail,suname,"
1719
- " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
1720
- " VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)"
1723
+ " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip,lastContact)"
1724
+ " VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q,now()/86400)"
17211725
 " ON CONFLICT(semail) DO UPDATE"
17221726
 " SET suname=excluded.suname"
17231727
 " RETURNING hex(subscriberCode);",
17241728
 /* semail */ zEAddr,
17251729
 /* suname */ zUserID,
17261730
--- src/login.c
+++ src/login.c
@@ -170,17 +170,21 @@
170 static void record_login_attempt(
171 const char *zUsername, /* Name of user logging in */
172 const char *zIpAddr, /* IP address from which they logged in */
173 int bSuccess /* True if the attempt was a success */
174 ){
175 if( !db_get_boolean("access-log", 0) ) return;
176 create_accesslog_table();
177 db_multi_exec(
178 "INSERT INTO accesslog(uname,ipaddr,success,mtime)"
179 "VALUES(%Q,%Q,%d,julianday('now'));",
180 zUsername, zIpAddr, bSuccess
181 );
 
 
 
 
182 }
183
184 /*
185 ** Searches for the user ID matching the given name and password.
186 ** On success it returns a positive value. On error it returns 0.
@@ -1714,12 +1718,12 @@
1714 ssub[nsub] = 0;
1715 capability_free(pCap);
1716 /* Also add the user to the subscriber table. */
1717 zCode = db_text(0,
1718 "INSERT INTO subscriber(semail,suname,"
1719 " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
1720 " VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)"
1721 " ON CONFLICT(semail) DO UPDATE"
1722 " SET suname=excluded.suname"
1723 " RETURNING hex(subscriberCode);",
1724 /* semail */ zEAddr,
1725 /* suname */ zUserID,
1726
--- src/login.c
+++ src/login.c
@@ -170,17 +170,21 @@
170 static void record_login_attempt(
171 const char *zUsername, /* Name of user logging in */
172 const char *zIpAddr, /* IP address from which they logged in */
173 int bSuccess /* True if the attempt was a success */
174 ){
175 if( db_get_boolean("access-log", 0) ){
176 create_accesslog_table();
177 db_multi_exec(
178 "INSERT INTO accesslog(uname,ipaddr,success,mtime)"
179 "VALUES(%Q,%Q,%d,julianday('now'));",
180 zUsername, zIpAddr, bSuccess
181 );
182 }
183 if( bSuccess ){
184 alert_user_contact(zUsername);
185 }
186 }
187
188 /*
189 ** Searches for the user ID matching the given name and password.
190 ** On success it returns a positive value. On error it returns 0.
@@ -1714,12 +1718,12 @@
1718 ssub[nsub] = 0;
1719 capability_free(pCap);
1720 /* Also add the user to the subscriber table. */
1721 zCode = db_text(0,
1722 "INSERT INTO subscriber(semail,suname,"
1723 " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip,lastContact)"
1724 " VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q,now()/86400)"
1725 " ON CONFLICT(semail) DO UPDATE"
1726 " SET suname=excluded.suname"
1727 " RETURNING hex(subscriberCode);",
1728 /* semail */ zEAddr,
1729 /* suname */ zUserID,
1730
M src/rebuild.c
+1 -1
--- src/rebuild.c
+++ src/rebuild.c
@@ -397,11 +397,11 @@
397397
 " WHERE type='table'"
398398
 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
399399
 "'config','shun','private','reportfmt',"
400400
 "'concealed','accesslog','modreq',"
401401
 "'purgeevent','purgeitem','unversioned',"
402
- "'subscriber','pending_alert','alert_bounce','chat')"
402
+ "'subscriber','pending_alert','chat')"
403403
 " AND name NOT GLOB 'sqlite_*'"
404404
 " AND name NOT GLOB 'fx_*'"
405405
 );
406406
 while( db_step(&q)==SQLITE_ROW ){
407407
 blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
408408
--- src/rebuild.c
+++ src/rebuild.c
@@ -397,11 +397,11 @@
397 " WHERE type='table'"
398 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
399 "'config','shun','private','reportfmt',"
400 "'concealed','accesslog','modreq',"
401 "'purgeevent','purgeitem','unversioned',"
402 "'subscriber','pending_alert','alert_bounce','chat')"
403 " AND name NOT GLOB 'sqlite_*'"
404 " AND name NOT GLOB 'fx_*'"
405 );
406 while( db_step(&q)==SQLITE_ROW ){
407 blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
408
--- src/rebuild.c
+++ src/rebuild.c
@@ -397,11 +397,11 @@
397 " WHERE type='table'"
398 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
399 "'config','shun','private','reportfmt',"
400 "'concealed','accesslog','modreq',"
401 "'purgeevent','purgeitem','unversioned',"
402 "'subscriber','pending_alert','chat')"
403 " AND name NOT GLOB 'sqlite_*'"
404 " AND name NOT GLOB 'fx_*'"
405 );
406 while( db_step(&q)==SQLITE_ROW ){
407 blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
408
M src/wiki.c
+3
--- src/wiki.c
+++ src/wiki.c
@@ -634,10 +634,13 @@
634634
 db_multi_exec("INSERT INTO modreq(objid) VALUES(%d)", nrid);
635635
 }
636636
 db_multi_exec("INSERT OR IGNORE INTO unsent VALUES(%d)", nrid);
637637
 db_multi_exec("INSERT OR IGNORE INTO unclustered VALUES(%d);", nrid);
638638
 manifest_crosslink(nrid, pWiki, MC_NONE);
639
+ if( login_is_individual() ){
640
+ alert_user_contact(login_name());
641
+ }
639642
 return nrid;
640643
 }
641644
642645
 /*
643646
 ** Output a selection box from which the user can select the
644647
--- src/wiki.c
+++ src/wiki.c
@@ -634,10 +634,13 @@
634 db_multi_exec("INSERT INTO modreq(objid) VALUES(%d)", nrid);
635 }
636 db_multi_exec("INSERT OR IGNORE INTO unsent VALUES(%d)", nrid);
637 db_multi_exec("INSERT OR IGNORE INTO unclustered VALUES(%d);", nrid);
638 manifest_crosslink(nrid, pWiki, MC_NONE);
 
 
 
639 return nrid;
640 }
641
642 /*
643 ** Output a selection box from which the user can select the
644
--- src/wiki.c
+++ src/wiki.c
@@ -634,10 +634,13 @@
634 db_multi_exec("INSERT INTO modreq(objid) VALUES(%d)", nrid);
635 }
636 db_multi_exec("INSERT OR IGNORE INTO unsent VALUES(%d)", nrid);
637 db_multi_exec("INSERT OR IGNORE INTO unclustered VALUES(%d);", nrid);
638 manifest_crosslink(nrid, pWiki, MC_NONE);
639 if( login_is_individual() ){
640 alert_user_contact(login_name());
641 }
642 return nrid;
643 }
644
645 /*
646 ** Output a selection box from which the user can select the
647

Keyboard Shortcuts

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