Fossil SCM

Enhance the backoffice to automatically purge stale entries from the emailblob table, if that table exists.

drh 2018-08-16 20:48 trunk
Commit 4c7edd4cfd252ec94ecb3e0f1fa042fcdf6db295a055fc8bdd7daf103c2c8bd1
Parent 731836b8698a2ab…
2 files changed +1 +19 -8
~ src/backoffice.c ~ src/smtp.c
M src/backoffice.c
+1
--- src/backoffice.c
+++ src/backoffice.c
@@ -519,10 +519,11 @@
519519
 }
520520
 }
521521
522522
 /* Here is where the actual work of the backoffice happens */
523523
 email_backoffice(0);
524
+ smtp_cleanup();
524525
 }
525526
526527
 /*
527528
 ** COMMAND: backoffice
528529
 **
529530
--- src/backoffice.c
+++ src/backoffice.c
@@ -519,10 +519,11 @@
519 }
520 }
521
522 /* Here is where the actual work of the backoffice happens */
523 email_backoffice(0);
 
524 }
525
526 /*
527 ** COMMAND: backoffice
528 **
529
--- src/backoffice.c
+++ src/backoffice.c
@@ -519,10 +519,11 @@
519 }
520 }
521
522 /* Here is where the actual work of the backoffice happens */
523 email_backoffice(0);
524 smtp_cleanup();
525 }
526
527 /*
528 ** COMMAND: backoffice
529 **
530
M src/smtp.c
+19 -8
--- src/smtp.c
+++ src/smtp.c
@@ -636,12 +636,12 @@
636636
637637
 /*
638638
 ** Schema used by the email processing system.
639639
 */
640640
 static const char zEmailSchema[] =
641
-@ -- bulk storage is in a separate table. This table can store either
642
-@ -- the body of email messages or transcripts of smtp sessions.
641
+@ -- bulk storage is in this table. This table can store either
642
+@ -- the body of email messages or transcripts of an smtp session.
643643
 @ CREATE TABLE IF NOT EXISTS repository.emailblob(
644644
 @ emailid INTEGER PRIMARY KEY AUTOINCREMENT, -- numeric idea for the entry
645645
 @ enref INT, -- Number of references to this blob
646646
 @ ets INT, -- Corresponding transcript, or NULL
647647
 @ etime INT, -- insertion time, secs since 1970
@@ -669,12 +669,12 @@
669669
 @ ) WITHOUT ROWID;
670670
 @
671671
 @ -- Outgoing email queue
672672
 @ CREATE TABLE IF NOT EXISTS repository.emailoutq(
673673
 @ edomain TEXT, -- Destination domain. (ex: "fossil-scm.org")
674
-@ efrom TEXT, -- Sender email address
675
-@ eto TEXT, -- Recipient email address
674
+@ efrom TEXT, -- Sender email address (envelope "from")
675
+@ eto TEXT, -- Recipient email address (envelope "to")
676676
 @ emsgid INT, -- Message body in the emailblob table
677677
 @ ectime INT, -- Time enqueued. Seconds since 1970
678678
 @ emtime INT, -- Time of last send attempt. Sec since 1970
679679
 @ ensend INT, -- Number of send attempts
680680
 @ ets INT -- Transcript of last failed attempt
@@ -1141,10 +1141,24 @@
11411141
 /* Finish the transaction after all changes are implemented */
11421142
 db_commit_transaction();
11431143
 }
11441144
 smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
11451145
 }
1146
+
1147
+/*
1148
+** Remove stale content from the emailblob table.
1149
+*/
1150
+void smtp_cleanup(void){
1151
+ if( db_table_exists("repository","emailblob") ){
1152
+ db_begin_transaction();
1153
+ db_multi_exec(
1154
+ "UPDATE emailblob SET ets=NULL WHERE enref<=0;"
1155
+ "DELETE FROM emailblob WHERE enref<=0;"
1156
+ );
1157
+ db_end_transaction(0);
1158
+ }
1159
+}
11461160
11471161
 /*
11481162
 ** COMMAND: test-emailblob-refcheck
11491163
 **
11501164
 ** Usage: %fossil test-emailblob-refcheck [--repair] [--full] [--clean]
@@ -1185,14 +1199,11 @@
11851199
 if( doRepair ){
11861200
 db_multi_exec(
11871201
 "UPDATE emailblob SET enref=(SELECT n FROM refcnt WHERE id=emailid)"
11881202
 );
11891203
 if( doClean ){
1190
- db_multi_exec(
1191
- "UPDATE emailblob SET ets=NULL WHERE enref<=0;"
1192
- "DELETE FROM emailblob WHERE enref<=0;"
1193
- );
1204
+ smtp_cleanup();
11941205
 }
11951206
 }
11961207
 blob_init(&sql, 0, 0);
11971208
 blob_append_sql(&sql,
11981209
 "SELECT a.emailid, a.enref, b.n"
11991210
--- src/smtp.c
+++ src/smtp.c
@@ -636,12 +636,12 @@
636
637 /*
638 ** Schema used by the email processing system.
639 */
640 static const char zEmailSchema[] =
641 @ -- bulk storage is in a separate table. This table can store either
642 @ -- the body of email messages or transcripts of smtp sessions.
643 @ CREATE TABLE IF NOT EXISTS repository.emailblob(
644 @ emailid INTEGER PRIMARY KEY AUTOINCREMENT, -- numeric idea for the entry
645 @ enref INT, -- Number of references to this blob
646 @ ets INT, -- Corresponding transcript, or NULL
647 @ etime INT, -- insertion time, secs since 1970
@@ -669,12 +669,12 @@
669 @ ) WITHOUT ROWID;
670 @
671 @ -- Outgoing email queue
672 @ CREATE TABLE IF NOT EXISTS repository.emailoutq(
673 @ edomain TEXT, -- Destination domain. (ex: "fossil-scm.org")
674 @ efrom TEXT, -- Sender email address
675 @ eto TEXT, -- Recipient email address
676 @ emsgid INT, -- Message body in the emailblob table
677 @ ectime INT, -- Time enqueued. Seconds since 1970
678 @ emtime INT, -- Time of last send attempt. Sec since 1970
679 @ ensend INT, -- Number of send attempts
680 @ ets INT -- Transcript of last failed attempt
@@ -1141,10 +1141,24 @@
1141 /* Finish the transaction after all changes are implemented */
1142 db_commit_transaction();
1143 }
1144 smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
1145 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1146
1147 /*
1148 ** COMMAND: test-emailblob-refcheck
1149 **
1150 ** Usage: %fossil test-emailblob-refcheck [--repair] [--full] [--clean]
@@ -1185,14 +1199,11 @@
1185 if( doRepair ){
1186 db_multi_exec(
1187 "UPDATE emailblob SET enref=(SELECT n FROM refcnt WHERE id=emailid)"
1188 );
1189 if( doClean ){
1190 db_multi_exec(
1191 "UPDATE emailblob SET ets=NULL WHERE enref<=0;"
1192 "DELETE FROM emailblob WHERE enref<=0;"
1193 );
1194 }
1195 }
1196 blob_init(&sql, 0, 0);
1197 blob_append_sql(&sql,
1198 "SELECT a.emailid, a.enref, b.n"
1199
--- src/smtp.c
+++ src/smtp.c
@@ -636,12 +636,12 @@
636
637 /*
638 ** Schema used by the email processing system.
639 */
640 static const char zEmailSchema[] =
641 @ -- bulk storage is in this table. This table can store either
642 @ -- the body of email messages or transcripts of an smtp session.
643 @ CREATE TABLE IF NOT EXISTS repository.emailblob(
644 @ emailid INTEGER PRIMARY KEY AUTOINCREMENT, -- numeric idea for the entry
645 @ enref INT, -- Number of references to this blob
646 @ ets INT, -- Corresponding transcript, or NULL
647 @ etime INT, -- insertion time, secs since 1970
@@ -669,12 +669,12 @@
669 @ ) WITHOUT ROWID;
670 @
671 @ -- Outgoing email queue
672 @ CREATE TABLE IF NOT EXISTS repository.emailoutq(
673 @ edomain TEXT, -- Destination domain. (ex: "fossil-scm.org")
674 @ efrom TEXT, -- Sender email address (envelope "from")
675 @ eto TEXT, -- Recipient email address (envelope "to")
676 @ emsgid INT, -- Message body in the emailblob table
677 @ ectime INT, -- Time enqueued. Seconds since 1970
678 @ emtime INT, -- Time of last send attempt. Sec since 1970
679 @ ensend INT, -- Number of send attempts
680 @ ets INT -- Transcript of last failed attempt
@@ -1141,10 +1141,24 @@
1141 /* Finish the transaction after all changes are implemented */
1142 db_commit_transaction();
1143 }
1144 smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
1145 }
1146
1147 /*
1148 ** Remove stale content from the emailblob table.
1149 */
1150 void smtp_cleanup(void){
1151 if( db_table_exists("repository","emailblob") ){
1152 db_begin_transaction();
1153 db_multi_exec(
1154 "UPDATE emailblob SET ets=NULL WHERE enref<=0;"
1155 "DELETE FROM emailblob WHERE enref<=0;"
1156 );
1157 db_end_transaction(0);
1158 }
1159 }
1160
1161 /*
1162 ** COMMAND: test-emailblob-refcheck
1163 **
1164 ** Usage: %fossil test-emailblob-refcheck [--repair] [--full] [--clean]
@@ -1185,14 +1199,11 @@
1199 if( doRepair ){
1200 db_multi_exec(
1201 "UPDATE emailblob SET enref=(SELECT n FROM refcnt WHERE id=emailid)"
1202 );
1203 if( doClean ){
1204 smtp_cleanup();
 
 
 
1205 }
1206 }
1207 blob_init(&sql, 0, 0);
1208 blob_append_sql(&sql,
1209 "SELECT a.emailid, a.enref, b.n"
1210

Keyboard Shortcuts

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