Fossil SCM

Add a reference count field to the emailblob table and triggers to keep the reference count current and to drop entries when the reference count reaches zero.

drh 2018-07-17 13:54 UTC webmail

Commit 94da0fb27c3969f610f21830e328587b395adc3265289e8ca06eeebad6cf9b3f

Parent 03a9b6f897b5d0e…

3 files changed +27 -2 +4 -6 +114 -10

~ src/db.c ~ src/email.c ~ src/smtp.c

M src/db.c

+27 -2

		--- src/db.c
		+++ src/db.c
		@@ -174,15 +174,18 @@
174	174	/*
175	175	** Silently add the filename and line number as parameter to each
176	176	** db_begin_transaction call.
177	177	*/
178	178	#if INTERFACE
179		-#define db_begin_transaction() db_begin_transaction_real(__FILE__,__LINE__)
	179	+#define db_begin_transaction() db_begin_transaction_real(__FILE__,__LINE__)
	180	+#define db_begin_write() db_begin_write_real(__FILE__,__LINE__)
	181	+#define db_commit_transaction() db_end_transaction(0)
	182	+#define db_rollback_transaction() db_end_transaction(1)
180	183	#endif
181	184
182	185	/*
183		-** Begin and end a nested transaction
	186	+** Begin a nested transaction
184	187	*/
185	188	void db_begin_transaction_real(const char *zStartFile, int iStartLine){
186	189	if( db.nBegin==0 ){
187	190	db_multi_exec("BEGIN");
188	191	sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
		@@ -191,10 +194,32 @@
191	194	db.zStartFile = zStartFile;
192	195	db.iStartLine = iStartLine;
193	196	}
194	197	db.nBegin++;
195	198	}
	199	+/*
	200	+** Begin a new transaction for writing.
	201	+*/
	202	+void db_begin_write_real(const char *zStartFile, int iStartLine){
	203	+ if( db.nBegin==0 ){
	204	+ db_multi_exec("BEGIN IMMEDIATE");
	205	+ sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
	206	+ db.nPriorChanges = sqlite3_total_changes(g.db);
	207	+ db.doRollback = 0;
	208	+ db.zStartFile = zStartFile;
	209	+ db.iStartLine = iStartLine;
	210	+ }else{
	211	+ fossil_warning("read txn at %s:%d might cause SQLITE_BUSY "
	212	+ "for the write txn at %s:%d",
	213	+ db.zStartFile, db.iStartLine, zStartFile, iStartLine);
	214	+ }
	215	+ db.nBegin++;
	216	+}
	217	+
	218	+/* End a transaction previously started using db_begin_transaction()
	219	+** or db_begin_write().
	220	+*/
196	221	void db_end_transaction(int rollbackFlag){
197	222	if( g.db==0 ) return;
198	223	if( db.nBegin<=0 ){
199	224	fossil_warning("Extra call to db_end_transaction");
200	225	return;
201	226

	--- src/db.c
	+++ src/db.c
	@@ -174,15 +174,18 @@
174	/*
175	** Silently add the filename and line number as parameter to each
176	** db_begin_transaction call.
177	*/
178	#if INTERFACE
179	#define db_begin_transaction() db_begin_transaction_real(__FILE__,__LINE__)



180	#endif
181
182	/*
183	** Begin and end a nested transaction
184	*/
185	void db_begin_transaction_real(const char *zStartFile, int iStartLine){
186	if( db.nBegin==0 ){
187	db_multi_exec("BEGIN");
188	sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
	@@ -191,10 +194,32 @@
191	db.zStartFile = zStartFile;
192	db.iStartLine = iStartLine;
193	}
194	db.nBegin++;
195	}






















196	void db_end_transaction(int rollbackFlag){
197	if( g.db==0 ) return;
198	if( db.nBegin<=0 ){
199	fossil_warning("Extra call to db_end_transaction");
200	return;
201

	--- src/db.c
	+++ src/db.c
	@@ -174,15 +174,18 @@
174	/*
175	** Silently add the filename and line number as parameter to each
176	** db_begin_transaction call.
177	*/
178	#if INTERFACE
179	#define db_begin_transaction() db_begin_transaction_real(__FILE__,__LINE__)
180	#define db_begin_write() db_begin_write_real(__FILE__,__LINE__)
181	#define db_commit_transaction() db_end_transaction(0)
182	#define db_rollback_transaction() db_end_transaction(1)
183	#endif
184
185	/*
186	** Begin a nested transaction
187	*/
188	void db_begin_transaction_real(const char *zStartFile, int iStartLine){
189	if( db.nBegin==0 ){
190	db_multi_exec("BEGIN");
191	sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
	@@ -191,10 +194,32 @@
194	db.zStartFile = zStartFile;
195	db.iStartLine = iStartLine;
196	}
197	db.nBegin++;
198	}
199	/*
200	** Begin a new transaction for writing.
201	*/
202	void db_begin_write_real(const char *zStartFile, int iStartLine){
203	if( db.nBegin==0 ){
204	db_multi_exec("BEGIN IMMEDIATE");
205	sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
206	db.nPriorChanges = sqlite3_total_changes(g.db);
207	db.doRollback = 0;
208	db.zStartFile = zStartFile;
209	db.iStartLine = iStartLine;
210	}else{
211	fossil_warning("read txn at %s:%d might cause SQLITE_BUSY "
212	"for the write txn at %s:%d",
213	db.zStartFile, db.iStartLine, zStartFile, iStartLine);
214	}
215	db.nBegin++;
216	}
217
218	/* End a transaction previously started using db_begin_transaction()
219	** or db_begin_write().
220	*/
221	void db_end_transaction(int rollbackFlag){
222	if( g.db==0 ) return;
223	if( db.nBegin<=0 ){
224	fossil_warning("Extra call to db_end_transaction");
225	return;
226

M src/email.c

+4 -6

		--- src/email.c
		+++ src/email.c
		@@ -2092,13 +2092,13 @@
2092	2092	if( db_transaction_nesting_depth()!=0 ){
2093	2093	fossil_warning("Called email_auto_exec() from within transaction "
2094	2094	"started at %z", db_transaction_start_point());
2095	2095	return;
2096	2096	}
2097		- db_begin_transaction();
2098		- if( !email_tables_exist() ) goto autoexec_done;
2099		- if( !db_get_boolean("email-autoexec",0) ) goto autoexec_done;
	2097	+ if( !email_tables_exist() ) return;
	2098	+ if( !db_get_boolean("email-autoexec",0) ) return;
	2099	+ db_begin_write();
2100	2100	email_send_alerts(0);
2101	2101	iJulianDay = db_int(0, "SELECT julianday('now')");
2102	2102	if( iJulianDay>db_get_int("email-last-digest",0) ){
2103	2103	if( db_transaction_nesting_depth()!=1 ){
2104	2104	fossil_warning("Transaction nesting error prior to digest processing");
		@@ -2105,13 +2105,11 @@
2105	2105	}else{
2106	2106	db_set_int("email-last-digest",iJulianDay,0);
2107	2107	email_send_alerts(SENDALERT_DIGEST);
2108	2108	}
2109	2109	}
2110		-
2111		-autoexec_done:
2112		- db_end_transaction(0);
	2110	+ db_commit_transaction();
2113	2111	}
2114	2112
2115	2113	/*
2116	2114	** WEBPAGE: contact_admin
2117	2115	**
2118	2116

	--- src/email.c
	+++ src/email.c
	@@ -2092,13 +2092,13 @@
2092	if( db_transaction_nesting_depth()!=0 ){
2093	fossil_warning("Called email_auto_exec() from within transaction "
2094	"started at %z", db_transaction_start_point());
2095	return;
2096	}
2097	db_begin_transaction();
2098	if( !email_tables_exist() ) goto autoexec_done;
2099	if( !db_get_boolean("email-autoexec",0) ) goto autoexec_done;
2100	email_send_alerts(0);
2101	iJulianDay = db_int(0, "SELECT julianday('now')");
2102	if( iJulianDay>db_get_int("email-last-digest",0) ){
2103	if( db_transaction_nesting_depth()!=1 ){
2104	fossil_warning("Transaction nesting error prior to digest processing");
	@@ -2105,13 +2105,11 @@
2105	}else{
2106	db_set_int("email-last-digest",iJulianDay,0);
2107	email_send_alerts(SENDALERT_DIGEST);
2108	}
2109	}
2110
2111	autoexec_done:
2112	db_end_transaction(0);
2113	}
2114
2115	/*
2116	** WEBPAGE: contact_admin
2117	**
2118

	--- src/email.c
	+++ src/email.c
	@@ -2092,13 +2092,13 @@
2092	if( db_transaction_nesting_depth()!=0 ){
2093	fossil_warning("Called email_auto_exec() from within transaction "
2094	"started at %z", db_transaction_start_point());
2095	return;
2096	}
2097	if( !email_tables_exist() ) return;
2098	if( !db_get_boolean("email-autoexec",0) ) return;
2099	db_begin_write();
2100	email_send_alerts(0);
2101	iJulianDay = db_int(0, "SELECT julianday('now')");
2102	if( iJulianDay>db_get_int("email-last-digest",0) ){
2103	if( db_transaction_nesting_depth()!=1 ){
2104	fossil_warning("Transaction nesting error prior to digest processing");
	@@ -2105,13 +2105,11 @@
2105	}else{
2106	db_set_int("email-last-digest",iJulianDay,0);
2107	email_send_alerts(SENDALERT_DIGEST);
2108	}
2109	}
2110	db_commit_transaction();


2111	}
2112
2113	/*
2114	** WEBPAGE: contact_admin
2115	**
2116

M src/smtp.c

+114 -10

		--- src/smtp.c
		+++ src/smtp.c
		@@ -638,10 +638,11 @@
638	638	static const char zEmailSchema[] =
639	639	@ -- bulk storage is in a separate table. This table can store either
640	640	@ -- the body of email messages or transcripts of smtp sessions.
641	641	@ CREATE TABLE IF NOT EXISTS repository.emailblob(
642	642	@ emailid INTEGER PRIMARY KEY, -- numeric idea for the entry
	643	+@ enref INT, -- Number of references to this blob
643	644	@ ets INT, -- Corresponding transcript, or NULL
644	645	@ etime INT, -- insertion time, secs since 1970
645	646	@ etxt TEXT -- content of this entry
646	647	@ );
647	648	@
		@@ -651,11 +652,10 @@
651	652	@ ebid INTEGER PRIMARY KEY, -- Unique id for each mailbox entry
652	653	@ euser TEXT, -- User who received this email
653	654	@ edate INT, -- Date received. Seconds since 1970
654	655	@ efrom TEXT, -- Who is the email from
655	656	@ emsgid INT, -- Raw email text
656		-@ ets INT, -- Transcript of the receiving SMTP session
657	657	@ estate INT, -- 0: Unread, 1: read, 2: trash 3: sent
658	658	@ esubject TEXT, -- Subject line for display
659	659	@ etags TEXT -- zero or more tags
660	660	@ );
661	661	@
		@@ -674,10 +674,29 @@
674	674	@ ectime INT, -- Time enqueued. Seconds since 1970
675	675	@ emtime INT, -- Time of last send attempt. Sec since 1970
676	676	@ ensend INT, -- Number of send attempts
677	677	@ ets INT -- Transcript of last failed attempt
678	678	@ );
	679	+@
	680	+@ -- Triggers to automatically keep the emailblob.enref field up to date
	681	+@ -- as entries in the emailblob, emailbox, and emailoutq tables are
	682	+@ -- deleted.
	683	+@ CREATE TRIGGER IF NOT EXISTS repository.emailblob_d1
	684	+@ AFTER DELETE ON emailblob BEGIN
	685	+@ DELETE FROM emailblob WHERE enref<=1 AND emailid=old.ets;
	686	+@ UPDATE emailblob SET enref=enref-1 WHERE emailid=old.ets;
	687	+@ END;
	688	+@ CREATE TRIGGER IF NOT EXISTS repository.emailbox_d1
	689	+@ AFTER DELETE ON emailbox BEGIN
	690	+@ DELETE FROM emailblob WHERE enref<=1 AND emailid=old.emsgid;
	691	+@ UPDATE emailblob SET enref=enref-1 WHERE emailid=old.emsgid;
	692	+@ END;
	693	+@ CREATE TRIGGER IF NOT EXISTS repository.emailoutq_d1
	694	+@ AFTER DELETE ON emailoutq BEGIN
	695	+@ DELETE FROM emailblob WHERE enref<=1 AND emailid IN (old.ets,old.emsgid);
	696	+@ UPDATE emailblob SET enref=enref-1 WHERE emailid IN (old.ets,old.emsgid);
	697	+@ END;
679	698	;
680	699
681	700	/*
682	701	** Code used to delete the email tables.
683	702	*/
		@@ -829,10 +848,12 @@
829	848	struct SmtpTo {
830	849	char z; / Address in each RCPT TO line */
831	850	int okRemote; /* zTo can be in another domain */
832	851	} *aTo;
833	852	u32 srvrFlags; /* Control flags */
	853	+ int nEts; /* Number of references to the transcript */
	854	+ int nRef; /* Number of references to idMsg */
834	855	Blob msg; /* Content following DATA */
835	856	Blob transcript; /* Session transcript */
836	857	};
837	858
838	859	#define SMTPSRV_CLEAR_MSG 1 /* smtp_server_clear() last message only */
		@@ -1000,10 +1021,11 @@
1000	1021	"INSERT INTO emailoutq(edomain,efrom,eto,emsgid,ectime,"
1001	1022	"emtime,ensend)"
1002	1023	"VALUES(%Q,%Q,%Q,%lld,now(),0,0)",
1003	1024	zAddr+i+1, p->zFrom, zAddr, p->idMsg
1004	1025	);
	1026	+ p->nRef++;
1005	1027	}
1006	1028	}
1007	1029	return;
1008	1030	}
1009	1031	blob_init(&policy, zPolicy, -1);
		@@ -1014,16 +1036,17 @@
1014	1036	if( blob_size(&tail)==0 ) continue;
1015	1037	if( blob_eq_str(&token, "mbox", 4) ){
1016	1038	Blob subj;
1017	1039	email_header_value(&p->msg, "subject", &subj);
1018	1040	db_multi_exec(
1019		- "INSERT INTO emailbox(euser,edate,efrom,emsgid,ets,estate,esubject)"
1020		- " VALUES(%Q,now(),%Q,%lld,%lld,0,%Q)",
1021		- blob_str(&tail), p->zFrom, p->idMsg, p->idTranscript,
	1041	+ "INSERT INTO emailbox(euser,edate,efrom,emsgid,estate,esubject)"
	1042	+ " VALUES(%Q,now(),%Q,%lld,0,%Q)",
	1043	+ blob_str(&tail), p->zFrom, p->idMsg,
1022	1044	blob_str(&subj)
1023	1045	);
1024	1046	blob_reset(&subj);
	1047	+ p->nRef++;
1025	1048	}
1026	1049	if( blob_eq_str(&token, "forward", 7) ){
1027	1050	smtp_append_to(p, fossil_strdup(blob_str(&tail)), 1);
1028	1051	}
1029	1052	blob_reset(&tail);
		@@ -1040,38 +1063,43 @@
1040	1063	if( p->zFrom
1041	1064	&& p->nTo
1042	1065	&& blob_size(&p->msg)
1043	1066	&& (p->srvrFlags & SMTPSRV_DRYRUN)==0
1044	1067	){
1045		- db_begin_transaction();
	1068	+ db_begin_write();
1046	1069	if( p->idTranscript==0 ) smtp_server_schema(0);
	1070	+ p->nRef = 0;
1047	1071	db_prepare(&s,
1048		- "INSERT INTO emailblob(ets,etime,etxt)"
1049		- " VALUES(:ets,now(),compress(:etxt))"
	1072	+ "INSERT INTO emailblob(ets,etime,etxt,enref)"
	1073	+ " VALUES(:ets,now(),compress(:etxt),:enref)"
1050	1074	);
	1075	+ p->nEts++;
1051	1076	if( !bFinish && p->idTranscript==0 ){
1052	1077	db_bind_null(&s, ":ets");
1053	1078	db_bind_null(&s, ":etxt");
	1079	+ db_bind_int(&s, ":enref", 0);
1054	1080	db_step(&s);
1055	1081	db_reset(&s);
1056	1082	p->idTranscript = db_last_insert_rowid();
1057	1083	}else if( bFinish ){
1058	1084	if( p->idTranscript ){
1059	1085	db_multi_exec(
1060		- "UPDATE emailblob SET etxt=compress(%Q)"
	1086	+ "UPDATE emailblob SET etxt=compress(%Q), enref=%d"
1061	1087	" WHERE emailid=%lld",
1062		- blob_str(&p->transcript), p->idTranscript);
	1088	+ blob_str(&p->transcript), p->nEts, p->idTranscript);
1063	1089	}else{
1064	1090	db_bind_null(&s, ":ets");
1065	1091	db_bind_str(&s, ":etxt", &p->transcript);
	1092	+ db_bind_int(&s, ":enref", p->nEts);
1066	1093	db_step(&s);
1067	1094	db_reset(&s);
1068	1095	p->idTranscript = db_last_insert_rowid();
1069	1096	}
1070	1097	}
1071	1098	db_bind_int64(&s, ":ets", p->idTranscript);
1072	1099	db_bind_str(&s, ":etxt", &p->msg);
	1100	+ db_bind_int(&s, ":enref", 0);
1073	1101	db_step(&s);
1074	1102	db_finalize(&s);
1075	1103	p->idMsg = db_last_insert_rowid();
1076	1104
1077	1105	/* make entries in emailbox and emailoutq */
		@@ -1078,16 +1106,92 @@
1078	1106	for(i=0; i<p->nTo; i++){
1079	1107	int okRemote = p->aTo[i].okRemote;
1080	1108	p->aTo[i].okRemote = 1;
1081	1109	smtp_server_send_one_user(p, p->aTo[i].z, okRemote);
1082	1110	}
	1111	+
	1112	+ /* Fix up the emailblob.enref field of the email message body */
	1113	+ if( p->nRef ){
	1114	+ db_multi_exec(
	1115	+ "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
	1116	+ p->nRef, p->idMsg
	1117	+ );
	1118	+ }else{
	1119	+ db_multi_exec(
	1120	+ "DELETE FROM emailblob WHERE emailid=%lld", p->idMsg
	1121	+ );
	1122	+ }
1083	1123
1084	1124	/* Finish the transaction after all changes are implemented */
1085		- db_end_transaction(0);
	1125	+ db_commit_transaction();
1086	1126	}
1087	1127	smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
1088	1128	}
	1129	+
	1130	+/*
	1131	+** COMMAND: test-emailblob-refcheck
	1132	+**
	1133	+** Usage: %fossil test-emailblob-refcheck [--repair] [--full]
	1134	+**
	1135	+** Verify that the emailblob.enref field is correct. Report any errors.
	1136	+** Use the --repair command to fix up the enref field. The --full option
	1137	+** gives a full report showing the enref value on all entries in the
	1138	+** emailblob table.
	1139	+*/
	1140	+void test_refcheck_emailblob(void){
	1141	+ int doRepair;
	1142	+ int fullReport;
	1143	+ Blob sql;
	1144	+ Stmt q;
	1145	+ int nErr = 0;
	1146	+ db_find_and_open_repository(0, 0);
	1147	+ fullReport = find_option("full",0,0)!=0;
	1148	+ doRepair = find_option("repair",0,0)!=0;
	1149	+ verify_all_options();
	1150	+ if( !db_table_exists("repository","emailblob") ){
	1151	+ fossil_print("emailblob table is not configured - nothing to check\n");
	1152	+ return;
	1153	+ }
	1154	+ db_multi_exec(
	1155	+ "CREATE TEMP TABLE refcnt(id INTEGER PRIMARY KEY, n);"
	1156	+ "INSERT INTO refcnt SELECT ets, count(*) FROM ("
	1157	+ " SELECT ets FROM emailblob"
	1158	+ " UNION ALL"
	1159	+ " SELECT emsgid FROM emailbox"
	1160	+ " UNION ALL"
	1161	+ " SELECT emsgid FROM emailoutq"
	1162	+ ") WHERE ets IS NOT NULL GROUP BY 1;"
	1163	+ "INSERT OR IGNORE INTO refcnt(id,n) SELECT emailid, 0 FROM emailblob;"
	1164	+ );
	1165	+ if( doRepair ){
	1166	+ db_multi_exec(
	1167	+ "UPDATE emailblob SET enref=(SELECT n FROM refcnt WHERE id=emailid)"
	1168	+ );
	1169	+ }
	1170	+ blob_init(&sql, 0, 0);
	1171	+ blob_append_sql(&sql,
	1172	+ "SELECT a.emailid, a.enref, b.n"
	1173	+ " FROM emailblob AS a JOIN refcnt AS b ON a.emailid=b.id"
	1174	+ );
	1175	+ if( !fullReport ){
	1176	+ blob_append_sql(&sql, " WHERE a.enref!=b.n");
	1177	+ }
	1178	+ db_prepare_blob(&q, &sql);
	1179	+ blob_reset(&sql);
	1180	+ while( db_step(&q)==SQLITE_ROW ){
	1181	+ sqlite3_int64 id = db_column_int64(&q,0);
	1182	+ int n1 = db_column_int(&q, 1);
	1183	+ int n2 = db_column_int(&q, 2);
	1184	+ if( n1!=n2 ) nErr++;
	1185	+ fossil_print("%12lld %4d %4d%s\n", id, n1, n2, n1!=n2 ? " ERROR" : "");
	1186	+ }
	1187	+ db_finalize(&q);
	1188	+ if( nErr ){
	1189	+ fossil_print("Number of incorrect emailblob.enref values: %d\n",nErr);
	1190	+ }
	1191	+}
	1192	+
1089	1193
1090	1194	/*
1091	1195	** COMMAND: smtpd
1092	1196	**
1093	1197	** Usage: %fossil smtpd [OPTIONS] REPOSITORY
1094	1198

	--- src/smtp.c
	+++ src/smtp.c
	@@ -638,10 +638,11 @@
638	static const char zEmailSchema[] =
639	@ -- bulk storage is in a separate table. This table can store either
640	@ -- the body of email messages or transcripts of smtp sessions.
641	@ CREATE TABLE IF NOT EXISTS repository.emailblob(
642	@ emailid INTEGER PRIMARY KEY, -- numeric idea for the entry

643	@ ets INT, -- Corresponding transcript, or NULL
644	@ etime INT, -- insertion time, secs since 1970
645	@ etxt TEXT -- content of this entry
646	@ );
647	@
	@@ -651,11 +652,10 @@
651	@ ebid INTEGER PRIMARY KEY, -- Unique id for each mailbox entry
652	@ euser TEXT, -- User who received this email
653	@ edate INT, -- Date received. Seconds since 1970
654	@ efrom TEXT, -- Who is the email from
655	@ emsgid INT, -- Raw email text
656	@ ets INT, -- Transcript of the receiving SMTP session
657	@ estate INT, -- 0: Unread, 1: read, 2: trash 3: sent
658	@ esubject TEXT, -- Subject line for display
659	@ etags TEXT -- zero or more tags
660	@ );
661	@
	@@ -674,10 +674,29 @@
674	@ ectime INT, -- Time enqueued. Seconds since 1970
675	@ emtime INT, -- Time of last send attempt. Sec since 1970
676	@ ensend INT, -- Number of send attempts
677	@ ets INT -- Transcript of last failed attempt
678	@ );



















679	;
680
681	/*
682	** Code used to delete the email tables.
683	*/
	@@ -829,10 +848,12 @@
829	struct SmtpTo {
830	char z; / Address in each RCPT TO line */
831	int okRemote; /* zTo can be in another domain */
832	} *aTo;
833	u32 srvrFlags; /* Control flags */


834	Blob msg; /* Content following DATA */
835	Blob transcript; /* Session transcript */
836	};
837
838	#define SMTPSRV_CLEAR_MSG 1 /* smtp_server_clear() last message only */
	@@ -1000,10 +1021,11 @@
1000	"INSERT INTO emailoutq(edomain,efrom,eto,emsgid,ectime,"
1001	"emtime,ensend)"
1002	"VALUES(%Q,%Q,%Q,%lld,now(),0,0)",
1003	zAddr+i+1, p->zFrom, zAddr, p->idMsg
1004	);

1005	}
1006	}
1007	return;
1008	}
1009	blob_init(&policy, zPolicy, -1);
	@@ -1014,16 +1036,17 @@
1014	if( blob_size(&tail)==0 ) continue;
1015	if( blob_eq_str(&token, "mbox", 4) ){
1016	Blob subj;
1017	email_header_value(&p->msg, "subject", &subj);
1018	db_multi_exec(
1019	"INSERT INTO emailbox(euser,edate,efrom,emsgid,ets,estate,esubject)"
1020	" VALUES(%Q,now(),%Q,%lld,%lld,0,%Q)",
1021	blob_str(&tail), p->zFrom, p->idMsg, p->idTranscript,
1022	blob_str(&subj)
1023	);
1024	blob_reset(&subj);

1025	}
1026	if( blob_eq_str(&token, "forward", 7) ){
1027	smtp_append_to(p, fossil_strdup(blob_str(&tail)), 1);
1028	}
1029	blob_reset(&tail);
	@@ -1040,38 +1063,43 @@
1040	if( p->zFrom
1041	&& p->nTo
1042	&& blob_size(&p->msg)
1043	&& (p->srvrFlags & SMTPSRV_DRYRUN)==0
1044	){
1045	db_begin_transaction();
1046	if( p->idTranscript==0 ) smtp_server_schema(0);

1047	db_prepare(&s,
1048	"INSERT INTO emailblob(ets,etime,etxt)"
1049	" VALUES(:ets,now(),compress(:etxt))"
1050	);

1051	if( !bFinish && p->idTranscript==0 ){
1052	db_bind_null(&s, ":ets");
1053	db_bind_null(&s, ":etxt");

1054	db_step(&s);
1055	db_reset(&s);
1056	p->idTranscript = db_last_insert_rowid();
1057	}else if( bFinish ){
1058	if( p->idTranscript ){
1059	db_multi_exec(
1060	"UPDATE emailblob SET etxt=compress(%Q)"
1061	" WHERE emailid=%lld",
1062	blob_str(&p->transcript), p->idTranscript);
1063	}else{
1064	db_bind_null(&s, ":ets");
1065	db_bind_str(&s, ":etxt", &p->transcript);

1066	db_step(&s);
1067	db_reset(&s);
1068	p->idTranscript = db_last_insert_rowid();
1069	}
1070	}
1071	db_bind_int64(&s, ":ets", p->idTranscript);
1072	db_bind_str(&s, ":etxt", &p->msg);

1073	db_step(&s);
1074	db_finalize(&s);
1075	p->idMsg = db_last_insert_rowid();
1076
1077	/* make entries in emailbox and emailoutq */
	@@ -1078,16 +1106,92 @@
1078	for(i=0; i<p->nTo; i++){
1079	int okRemote = p->aTo[i].okRemote;
1080	p->aTo[i].okRemote = 1;
1081	smtp_server_send_one_user(p, p->aTo[i].z, okRemote);
1082	}












1083
1084	/* Finish the transaction after all changes are implemented */
1085	db_end_transaction(0);
1086	}
1087	smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
1088	}
































































1089
1090	/*
1091	** COMMAND: smtpd
1092	**
1093	** Usage: %fossil smtpd [OPTIONS] REPOSITORY
1094

	--- src/smtp.c
	+++ src/smtp.c
	@@ -638,10 +638,11 @@
638	static const char zEmailSchema[] =
639	@ -- bulk storage is in a separate table. This table can store either
640	@ -- the body of email messages or transcripts of smtp sessions.
641	@ CREATE TABLE IF NOT EXISTS repository.emailblob(
642	@ emailid INTEGER PRIMARY KEY, -- numeric idea for the entry
643	@ enref INT, -- Number of references to this blob
644	@ ets INT, -- Corresponding transcript, or NULL
645	@ etime INT, -- insertion time, secs since 1970
646	@ etxt TEXT -- content of this entry
647	@ );
648	@
	@@ -651,11 +652,10 @@
652	@ ebid INTEGER PRIMARY KEY, -- Unique id for each mailbox entry
653	@ euser TEXT, -- User who received this email
654	@ edate INT, -- Date received. Seconds since 1970
655	@ efrom TEXT, -- Who is the email from
656	@ emsgid INT, -- Raw email text

657	@ estate INT, -- 0: Unread, 1: read, 2: trash 3: sent
658	@ esubject TEXT, -- Subject line for display
659	@ etags TEXT -- zero or more tags
660	@ );
661	@
	@@ -674,10 +674,29 @@
674	@ ectime INT, -- Time enqueued. Seconds since 1970
675	@ emtime INT, -- Time of last send attempt. Sec since 1970
676	@ ensend INT, -- Number of send attempts
677	@ ets INT -- Transcript of last failed attempt
678	@ );
679	@
680	@ -- Triggers to automatically keep the emailblob.enref field up to date
681	@ -- as entries in the emailblob, emailbox, and emailoutq tables are
682	@ -- deleted.
683	@ CREATE TRIGGER IF NOT EXISTS repository.emailblob_d1
684	@ AFTER DELETE ON emailblob BEGIN
685	@ DELETE FROM emailblob WHERE enref<=1 AND emailid=old.ets;
686	@ UPDATE emailblob SET enref=enref-1 WHERE emailid=old.ets;
687	@ END;
688	@ CREATE TRIGGER IF NOT EXISTS repository.emailbox_d1
689	@ AFTER DELETE ON emailbox BEGIN
690	@ DELETE FROM emailblob WHERE enref<=1 AND emailid=old.emsgid;
691	@ UPDATE emailblob SET enref=enref-1 WHERE emailid=old.emsgid;
692	@ END;
693	@ CREATE TRIGGER IF NOT EXISTS repository.emailoutq_d1
694	@ AFTER DELETE ON emailoutq BEGIN
695	@ DELETE FROM emailblob WHERE enref<=1 AND emailid IN (old.ets,old.emsgid);
696	@ UPDATE emailblob SET enref=enref-1 WHERE emailid IN (old.ets,old.emsgid);
697	@ END;
698	;
699
700	/*
701	** Code used to delete the email tables.
702	*/
	@@ -829,10 +848,12 @@
848	struct SmtpTo {
849	char z; / Address in each RCPT TO line */
850	int okRemote; /* zTo can be in another domain */
851	} *aTo;
852	u32 srvrFlags; /* Control flags */
853	int nEts; /* Number of references to the transcript */
854	int nRef; /* Number of references to idMsg */
855	Blob msg; /* Content following DATA */
856	Blob transcript; /* Session transcript */
857	};
858
859	#define SMTPSRV_CLEAR_MSG 1 /* smtp_server_clear() last message only */
	@@ -1000,10 +1021,11 @@
1021	"INSERT INTO emailoutq(edomain,efrom,eto,emsgid,ectime,"
1022	"emtime,ensend)"
1023	"VALUES(%Q,%Q,%Q,%lld,now(),0,0)",
1024	zAddr+i+1, p->zFrom, zAddr, p->idMsg
1025	);
1026	p->nRef++;
1027	}
1028	}
1029	return;
1030	}
1031	blob_init(&policy, zPolicy, -1);
	@@ -1014,16 +1036,17 @@
1036	if( blob_size(&tail)==0 ) continue;
1037	if( blob_eq_str(&token, "mbox", 4) ){
1038	Blob subj;
1039	email_header_value(&p->msg, "subject", &subj);
1040	db_multi_exec(
1041	"INSERT INTO emailbox(euser,edate,efrom,emsgid,estate,esubject)"
1042	" VALUES(%Q,now(),%Q,%lld,0,%Q)",
1043	blob_str(&tail), p->zFrom, p->idMsg,
1044	blob_str(&subj)
1045	);
1046	blob_reset(&subj);
1047	p->nRef++;
1048	}
1049	if( blob_eq_str(&token, "forward", 7) ){
1050	smtp_append_to(p, fossil_strdup(blob_str(&tail)), 1);
1051	}
1052	blob_reset(&tail);
	@@ -1040,38 +1063,43 @@
1063	if( p->zFrom
1064	&& p->nTo
1065	&& blob_size(&p->msg)
1066	&& (p->srvrFlags & SMTPSRV_DRYRUN)==0
1067	){
1068	db_begin_write();
1069	if( p->idTranscript==0 ) smtp_server_schema(0);
1070	p->nRef = 0;
1071	db_prepare(&s,
1072	"INSERT INTO emailblob(ets,etime,etxt,enref)"
1073	" VALUES(:ets,now(),compress(:etxt),:enref)"
1074	);
1075	p->nEts++;
1076	if( !bFinish && p->idTranscript==0 ){
1077	db_bind_null(&s, ":ets");
1078	db_bind_null(&s, ":etxt");
1079	db_bind_int(&s, ":enref", 0);
1080	db_step(&s);
1081	db_reset(&s);
1082	p->idTranscript = db_last_insert_rowid();
1083	}else if( bFinish ){
1084	if( p->idTranscript ){
1085	db_multi_exec(
1086	"UPDATE emailblob SET etxt=compress(%Q), enref=%d"
1087	" WHERE emailid=%lld",
1088	blob_str(&p->transcript), p->nEts, p->idTranscript);
1089	}else{
1090	db_bind_null(&s, ":ets");
1091	db_bind_str(&s, ":etxt", &p->transcript);
1092	db_bind_int(&s, ":enref", p->nEts);
1093	db_step(&s);
1094	db_reset(&s);
1095	p->idTranscript = db_last_insert_rowid();
1096	}
1097	}
1098	db_bind_int64(&s, ":ets", p->idTranscript);
1099	db_bind_str(&s, ":etxt", &p->msg);
1100	db_bind_int(&s, ":enref", 0);
1101	db_step(&s);
1102	db_finalize(&s);
1103	p->idMsg = db_last_insert_rowid();
1104
1105	/* make entries in emailbox and emailoutq */
	@@ -1078,16 +1106,92 @@
1106	for(i=0; i<p->nTo; i++){
1107	int okRemote = p->aTo[i].okRemote;
1108	p->aTo[i].okRemote = 1;
1109	smtp_server_send_one_user(p, p->aTo[i].z, okRemote);
1110	}
1111
1112	/* Fix up the emailblob.enref field of the email message body */
1113	if( p->nRef ){
1114	db_multi_exec(
1115	"UPDATE emailblob SET enref=%d WHERE emailid=%lld",
1116	p->nRef, p->idMsg
1117	);
1118	}else{
1119	db_multi_exec(
1120	"DELETE FROM emailblob WHERE emailid=%lld", p->idMsg
1121	);
1122	}
1123
1124	/* Finish the transaction after all changes are implemented */
1125	db_commit_transaction();
1126	}
1127	smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
1128	}
1129
1130	/*
1131	** COMMAND: test-emailblob-refcheck
1132	**
1133	** Usage: %fossil test-emailblob-refcheck [--repair] [--full]
1134	**
1135	** Verify that the emailblob.enref field is correct. Report any errors.
1136	** Use the --repair command to fix up the enref field. The --full option
1137	** gives a full report showing the enref value on all entries in the
1138	** emailblob table.
1139	*/
1140	void test_refcheck_emailblob(void){
1141	int doRepair;
1142	int fullReport;
1143	Blob sql;
1144	Stmt q;
1145	int nErr = 0;
1146	db_find_and_open_repository(0, 0);
1147	fullReport = find_option("full",0,0)!=0;
1148	doRepair = find_option("repair",0,0)!=0;
1149	verify_all_options();
1150	if( !db_table_exists("repository","emailblob") ){
1151	fossil_print("emailblob table is not configured - nothing to check\n");
1152	return;
1153	}
1154	db_multi_exec(
1155	"CREATE TEMP TABLE refcnt(id INTEGER PRIMARY KEY, n);"
1156	"INSERT INTO refcnt SELECT ets, count(*) FROM ("
1157	" SELECT ets FROM emailblob"
1158	" UNION ALL"
1159	" SELECT emsgid FROM emailbox"
1160	" UNION ALL"
1161	" SELECT emsgid FROM emailoutq"
1162	") WHERE ets IS NOT NULL GROUP BY 1;"
1163	"INSERT OR IGNORE INTO refcnt(id,n) SELECT emailid, 0 FROM emailblob;"
1164	);
1165	if( doRepair ){
1166	db_multi_exec(
1167	"UPDATE emailblob SET enref=(SELECT n FROM refcnt WHERE id=emailid)"
1168	);
1169	}
1170	blob_init(&sql, 0, 0);
1171	blob_append_sql(&sql,
1172	"SELECT a.emailid, a.enref, b.n"
1173	" FROM emailblob AS a JOIN refcnt AS b ON a.emailid=b.id"
1174	);
1175	if( !fullReport ){
1176	blob_append_sql(&sql, " WHERE a.enref!=b.n");
1177	}
1178	db_prepare_blob(&q, &sql);
1179	blob_reset(&sql);
1180	while( db_step(&q)==SQLITE_ROW ){
1181	sqlite3_int64 id = db_column_int64(&q,0);
1182	int n1 = db_column_int(&q, 1);
1183	int n2 = db_column_int(&q, 2);
1184	if( n1!=n2 ) nErr++;
1185	fossil_print("%12lld %4d %4d%s\n", id, n1, n2, n1!=n2 ? " ERROR" : "");
1186	}
1187	db_finalize(&q);
1188	if( nErr ){
1189	fossil_print("Number of incorrect emailblob.enref values: %d\n",nErr);
1190	}
1191	}
1192
1193
1194	/*
1195	** COMMAND: smtpd
1196	**
1197	** Usage: %fossil smtpd [OPTIONS] REPOSITORY
1198

Fossil SCM

Keyboard Shortcuts