Fossil SCM

Webmail changes compile on linux, mac, and windows now, so merge them to trunk. They are not ready for use, but they should not interfere with the stable parts of the code, and it is easier to test from trunk.

drh 2018-07-17 15:32 trunk merge

Commit 0f6a5ee117dfb8c1759e19a93fb7561148a6962fc09b0035e20ffbe11727bfc1

Parent adc23c107d30df8…

6 files changed +27 -2 +14 -6 +14 -6 +116 -11 +61 -48 +389 -81

~ src/db.c ~ src/email.c ~ src/email.c ~ src/smtp.c ~ src/stat.c ~ src/webmail.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

+14 -6

		--- src/email.c
		+++ src/email.c
		@@ -204,10 +204,20 @@
204	204	db_begin_transaction();
205	205
206	206	email_submenu_common();
207	207	style_submenu_element("Send Announcement","%R/announce");
208	208	style_header("Email Notification Setup");
	209	+ @ <h1>Status</h1>
	210	+ @ <table class="label-value">
	211	+ if( email_enabled() ){
	212	+ stats_for_email();
	213	+ }else{
	214	+ @ <th>Disabled</th>
	215	+ }
	216	+ @ </table>
	217	+ @ <hr>
	218	+ @ <h1> Configuration </h1>
209	219	@ <form action="%R/setup_notification" method="post"><div>
210	220	@ <input type="submit" name="submit" value="Apply Changes" /><hr>
211	221	login_insert_csrf_secret();
212	222
213	223	entry_attribute("Canonical Server URL", 40, "email-url",
		@@ -2092,13 +2102,13 @@
2092	2102	if( db_transaction_nesting_depth()!=0 ){
2093	2103	fossil_warning("Called email_auto_exec() from within transaction "
2094	2104	"started at %z", db_transaction_start_point());
2095	2105	return;
2096	2106	}
2097		- db_begin_transaction();
2098		- if( !email_tables_exist() ) goto autoexec_done;
2099		- if( !db_get_boolean("email-autoexec",0) ) goto autoexec_done;
	2107	+ if( !email_tables_exist() ) return;
	2108	+ if( !db_get_boolean("email-autoexec",0) ) return;
	2109	+ db_begin_write();
2100	2110	email_send_alerts(0);
2101	2111	iJulianDay = db_int(0, "SELECT julianday('now')");
2102	2112	if( iJulianDay>db_get_int("email-last-digest",0) ){
2103	2113	if( db_transaction_nesting_depth()!=1 ){
2104	2114	fossil_warning("Transaction nesting error prior to digest processing");
		@@ -2105,13 +2115,11 @@
2105	2115	}else{
2106	2116	db_set_int("email-last-digest",iJulianDay,0);
2107	2117	email_send_alerts(SENDALERT_DIGEST);
2108	2118	}
2109	2119	}
2110		-
2111		-autoexec_done:
2112		- db_end_transaction(0);
	2120	+ db_commit_transaction();
2113	2121	}
2114	2122
2115	2123	/*
2116	2124	** WEBPAGE: contact_admin
2117	2125	**
2118	2126

	--- src/email.c
	+++ src/email.c
	@@ -204,10 +204,20 @@
204	db_begin_transaction();
205
206	email_submenu_common();
207	style_submenu_element("Send Announcement","%R/announce");
208	style_header("Email Notification Setup");










209	@ <form action="%R/setup_notification" method="post"><div>
210	@ <input type="submit" name="submit" value="Apply Changes" /><hr>
211	login_insert_csrf_secret();
212
213	entry_attribute("Canonical Server URL", 40, "email-url",
	@@ -2092,13 +2102,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 +2115,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
	@@ -204,10 +204,20 @@
204	db_begin_transaction();
205
206	email_submenu_common();
207	style_submenu_element("Send Announcement","%R/announce");
208	style_header("Email Notification Setup");
209	@ <h1>Status</h1>
210	@ <table class="label-value">
211	if( email_enabled() ){
212	stats_for_email();
213	}else{
214	@ <th>Disabled</th>
215	}
216	@ </table>
217	@ <hr>
218	@ <h1> Configuration </h1>
219	@ <form action="%R/setup_notification" method="post"><div>
220	@ <input type="submit" name="submit" value="Apply Changes" /><hr>
221	login_insert_csrf_secret();
222
223	entry_attribute("Canonical Server URL", 40, "email-url",
	@@ -2092,13 +2102,13 @@
2102	if( db_transaction_nesting_depth()!=0 ){
2103	fossil_warning("Called email_auto_exec() from within transaction "
2104	"started at %z", db_transaction_start_point());
2105	return;
2106	}
2107	if( !email_tables_exist() ) return;
2108	if( !db_get_boolean("email-autoexec",0) ) return;
2109	db_begin_write();
2110	email_send_alerts(0);
2111	iJulianDay = db_int(0, "SELECT julianday('now')");
2112	if( iJulianDay>db_get_int("email-last-digest",0) ){
2113	if( db_transaction_nesting_depth()!=1 ){
2114	fossil_warning("Transaction nesting error prior to digest processing");
	@@ -2105,13 +2115,11 @@
2115	}else{
2116	db_set_int("email-last-digest",iJulianDay,0);
2117	email_send_alerts(SENDALERT_DIGEST);
2118	}
2119	}
2120	db_commit_transaction();


2121	}
2122
2123	/*
2124	** WEBPAGE: contact_admin
2125	**
2126

M src/email.c

+14 -6

		--- src/email.c
		+++ src/email.c
		@@ -204,10 +204,20 @@
204	204	db_begin_transaction();
205	205
206	206	email_submenu_common();
207	207	style_submenu_element("Send Announcement","%R/announce");
208	208	style_header("Email Notification Setup");
	209	+ @ <h1>Status</h1>
	210	+ @ <table class="label-value">
	211	+ if( email_enabled() ){
	212	+ stats_for_email();
	213	+ }else{
	214	+ @ <th>Disabled</th>
	215	+ }
	216	+ @ </table>
	217	+ @ <hr>
	218	+ @ <h1> Configuration </h1>
209	219	@ <form action="%R/setup_notification" method="post"><div>
210	220	@ <input type="submit" name="submit" value="Apply Changes" /><hr>
211	221	login_insert_csrf_secret();
212	222
213	223	entry_attribute("Canonical Server URL", 40, "email-url",
		@@ -2092,13 +2102,13 @@
2092	2102	if( db_transaction_nesting_depth()!=0 ){
2093	2103	fossil_warning("Called email_auto_exec() from within transaction "
2094	2104	"started at %z", db_transaction_start_point());
2095	2105	return;
2096	2106	}
2097		- db_begin_transaction();
2098		- if( !email_tables_exist() ) goto autoexec_done;
2099		- if( !db_get_boolean("email-autoexec",0) ) goto autoexec_done;
	2107	+ if( !email_tables_exist() ) return;
	2108	+ if( !db_get_boolean("email-autoexec",0) ) return;
	2109	+ db_begin_write();
2100	2110	email_send_alerts(0);
2101	2111	iJulianDay = db_int(0, "SELECT julianday('now')");
2102	2112	if( iJulianDay>db_get_int("email-last-digest",0) ){
2103	2113	if( db_transaction_nesting_depth()!=1 ){
2104	2114	fossil_warning("Transaction nesting error prior to digest processing");
		@@ -2105,13 +2115,11 @@
2105	2115	}else{
2106	2116	db_set_int("email-last-digest",iJulianDay,0);
2107	2117	email_send_alerts(SENDALERT_DIGEST);
2108	2118	}
2109	2119	}
2110		-
2111		-autoexec_done:
2112		- db_end_transaction(0);
	2120	+ db_commit_transaction();
2113	2121	}
2114	2122
2115	2123	/*
2116	2124	** WEBPAGE: contact_admin
2117	2125	**
2118	2126

	--- src/email.c
	+++ src/email.c
	@@ -204,10 +204,20 @@
204	db_begin_transaction();
205
206	email_submenu_common();
207	style_submenu_element("Send Announcement","%R/announce");
208	style_header("Email Notification Setup");










209	@ <form action="%R/setup_notification" method="post"><div>
210	@ <input type="submit" name="submit" value="Apply Changes" /><hr>
211	login_insert_csrf_secret();
212
213	entry_attribute("Canonical Server URL", 40, "email-url",
	@@ -2092,13 +2102,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 +2115,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
	@@ -204,10 +204,20 @@
204	db_begin_transaction();
205
206	email_submenu_common();
207	style_submenu_element("Send Announcement","%R/announce");
208	style_header("Email Notification Setup");
209	@ <h1>Status</h1>
210	@ <table class="label-value">
211	if( email_enabled() ){
212	stats_for_email();
213	}else{
214	@ <th>Disabled</th>
215	}
216	@ </table>
217	@ <hr>
218	@ <h1> Configuration </h1>
219	@ <form action="%R/setup_notification" method="post"><div>
220	@ <input type="submit" name="submit" value="Apply Changes" /><hr>
221	login_insert_csrf_secret();
222
223	entry_attribute("Canonical Server URL", 40, "email-url",
	@@ -2092,13 +2102,13 @@
2102	if( db_transaction_nesting_depth()!=0 ){
2103	fossil_warning("Called email_auto_exec() from within transaction "
2104	"started at %z", db_transaction_start_point());
2105	return;
2106	}
2107	if( !email_tables_exist() ) return;
2108	if( !db_get_boolean("email-autoexec",0) ) return;
2109	db_begin_write();
2110	email_send_alerts(0);
2111	iJulianDay = db_int(0, "SELECT julianday('now')");
2112	if( iJulianDay>db_get_int("email-last-digest",0) ){
2113	if( db_transaction_nesting_depth()!=1 ){
2114	fossil_warning("Transaction nesting error prior to digest processing");
	@@ -2105,13 +2115,11 @@
2115	}else{
2116	db_set_int("email-last-digest",iJulianDay,0);
2117	email_send_alerts(SENDALERT_DIGEST);
2118	}
2119	}
2120	db_commit_transaction();


2121	}
2122
2123	/*
2124	** WEBPAGE: contact_admin
2125	**
2126

M src/smtp.c

+116 -11

		--- src/smtp.c
		+++ src/smtp.c
		@@ -638,24 +638,25 @@
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	@
648	649	@ -- One row for each mailbox entry. All users emails are stored in
649	650	@ -- this same table.
650	651	@ CREATE TABLE IF NOT EXISTS repository.emailbox(
	652	+@ ebid INTEGER PRIMARY KEY, -- Unique id for each mailbox entry
651	653	@ euser TEXT, -- User who received this email
652	654	@ edate INT, -- Date received. Seconds since 1970
653	655	@ efrom TEXT, -- Who is the email from
654	656	@ emsgid INT, -- Raw email text
655		-@ ets INT, -- Transcript of the receiving SMTP session
656		-@ estate INT, -- Unread, read, starred, etc.
	657	+@ estate INT, -- 0: Unread, 1: read, 2: trash 3: sent
657	658	@ esubject TEXT, -- Subject line for display
658	659	@ etags TEXT -- zero or more tags
659	660	@ );
660	661	@
661	662	@ -- Information on how to deliver incoming email.
		@@ -673,10 +674,29 @@
673	674	@ ectime INT, -- Time enqueued. Seconds since 1970
674	675	@ emtime INT, -- Time of last send attempt. Sec since 1970
675	676	@ ensend INT, -- Number of send attempts
676	677	@ ets INT -- Transcript of last failed attempt
677	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;
678	698	;
679	699
680	700	/*
681	701	** Code used to delete the email tables.
682	702	*/
		@@ -828,10 +848,12 @@
828	848	struct SmtpTo {
829	849	char z; / Address in each RCPT TO line */
830	850	int okRemote; /* zTo can be in another domain */
831	851	} *aTo;
832	852	u32 srvrFlags; /* Control flags */
	853	+ int nEts; /* Number of references to the transcript */
	854	+ int nRef; /* Number of references to idMsg */
833	855	Blob msg; /* Content following DATA */
834	856	Blob transcript; /* Session transcript */
835	857	};
836	858
837	859	#define SMTPSRV_CLEAR_MSG 1 /* smtp_server_clear() last message only */
		@@ -999,10 +1021,11 @@
999	1021	"INSERT INTO emailoutq(edomain,efrom,eto,emsgid,ectime,"
1000	1022	"emtime,ensend)"
1001	1023	"VALUES(%Q,%Q,%Q,%lld,now(),0,0)",
1002	1024	zAddr+i+1, p->zFrom, zAddr, p->idMsg
1003	1025	);
	1026	+ p->nRef++;
1004	1027	}
1005	1028	}
1006	1029	return;
1007	1030	}
1008	1031	blob_init(&policy, zPolicy, -1);
		@@ -1013,16 +1036,17 @@
1013	1036	if( blob_size(&tail)==0 ) continue;
1014	1037	if( blob_eq_str(&token, "mbox", 4) ){
1015	1038	Blob subj;
1016	1039	email_header_value(&p->msg, "subject", &subj);
1017	1040	db_multi_exec(
1018		- "INSERT INTO emailbox(euser,edate,efrom,emsgid,ets,estate,esubject)"
1019		- " VALUES(%Q,now(),%Q,%lld,%lld,0,%Q)",
1020		- 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,
1021	1044	blob_str(&subj)
1022	1045	);
1023	1046	blob_reset(&subj);
	1047	+ p->nRef++;
1024	1048	}
1025	1049	if( blob_eq_str(&token, "forward", 7) ){
1026	1050	smtp_append_to(p, fossil_strdup(blob_str(&tail)), 1);
1027	1051	}
1028	1052	blob_reset(&tail);
		@@ -1039,38 +1063,43 @@
1039	1063	if( p->zFrom
1040	1064	&& p->nTo
1041	1065	&& blob_size(&p->msg)
1042	1066	&& (p->srvrFlags & SMTPSRV_DRYRUN)==0
1043	1067	){
1044		- db_begin_transaction();
	1068	+ db_begin_write();
1045	1069	if( p->idTranscript==0 ) smtp_server_schema(0);
	1070	+ p->nRef = 0;
1046	1071	db_prepare(&s,
1047		- "INSERT INTO emailblob(ets,etime,etxt)"
1048		- " VALUES(:ets,now(),compress(:etxt))"
	1072	+ "INSERT INTO emailblob(ets,etime,etxt,enref)"
	1073	+ " VALUES(:ets,now(),compress(:etxt),:enref)"
1049	1074	);
	1075	+ p->nEts++;
1050	1076	if( !bFinish && p->idTranscript==0 ){
1051	1077	db_bind_null(&s, ":ets");
1052	1078	db_bind_null(&s, ":etxt");
	1079	+ db_bind_int(&s, ":enref", 0);
1053	1080	db_step(&s);
1054	1081	db_reset(&s);
1055	1082	p->idTranscript = db_last_insert_rowid();
1056	1083	}else if( bFinish ){
1057	1084	if( p->idTranscript ){
1058	1085	db_multi_exec(
1059		- "UPDATE emailblob SET etxt=compress(%Q)"
	1086	+ "UPDATE emailblob SET etxt=compress(%Q), enref=%d"
1060	1087	" WHERE emailid=%lld",
1061		- blob_str(&p->transcript), p->idTranscript);
	1088	+ blob_str(&p->transcript), p->nEts, p->idTranscript);
1062	1089	}else{
1063	1090	db_bind_null(&s, ":ets");
1064	1091	db_bind_str(&s, ":etxt", &p->transcript);
	1092	+ db_bind_int(&s, ":enref", p->nEts);
1065	1093	db_step(&s);
1066	1094	db_reset(&s);
1067	1095	p->idTranscript = db_last_insert_rowid();
1068	1096	}
1069	1097	}
1070	1098	db_bind_int64(&s, ":ets", p->idTranscript);
1071	1099	db_bind_str(&s, ":etxt", &p->msg);
	1100	+ db_bind_int(&s, ":enref", 0);
1072	1101	db_step(&s);
1073	1102	db_finalize(&s);
1074	1103	p->idMsg = db_last_insert_rowid();
1075	1104
1076	1105	/* make entries in emailbox and emailoutq */
		@@ -1077,16 +1106,92 @@
1077	1106	for(i=0; i<p->nTo; i++){
1078	1107	int okRemote = p->aTo[i].okRemote;
1079	1108	p->aTo[i].okRemote = 1;
1080	1109	smtp_server_send_one_user(p, p->aTo[i].z, okRemote);
1081	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	+ }
1082	1123
1083	1124	/* Finish the transaction after all changes are implemented */
1084		- db_end_transaction(0);
	1125	+ db_commit_transaction();
1085	1126	}
1086	1127	smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
1087	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	+
1088	1193
1089	1194	/*
1090	1195	** COMMAND: smtpd
1091	1196	**
1092	1197	** Usage: %fossil smtpd [OPTIONS] REPOSITORY
1093	1198

	--- src/smtp.c
	+++ src/smtp.c
	@@ -638,24 +638,25 @@
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	@
648	@ -- One row for each mailbox entry. All users emails are stored in
649	@ -- this same table.
650	@ CREATE TABLE IF NOT EXISTS repository.emailbox(

651	@ euser TEXT, -- User who received this email
652	@ edate INT, -- Date received. Seconds since 1970
653	@ efrom TEXT, -- Who is the email from
654	@ emsgid INT, -- Raw email text
655	@ ets INT, -- Transcript of the receiving SMTP session
656	@ estate INT, -- Unread, read, starred, etc.
657	@ esubject TEXT, -- Subject line for display
658	@ etags TEXT -- zero or more tags
659	@ );
660	@
661	@ -- Information on how to deliver incoming email.
	@@ -673,10 +674,29 @@
673	@ ectime INT, -- Time enqueued. Seconds since 1970
674	@ emtime INT, -- Time of last send attempt. Sec since 1970
675	@ ensend INT, -- Number of send attempts
676	@ ets INT -- Transcript of last failed attempt
677	@ );



















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


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

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

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

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

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

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

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

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












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
































































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

	--- src/smtp.c
	+++ src/smtp.c
	@@ -638,24 +638,25 @@
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	@
649	@ -- One row for each mailbox entry. All users emails are stored in
650	@ -- this same table.
651	@ CREATE TABLE IF NOT EXISTS repository.emailbox(
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	@
662	@ -- Information on how to deliver incoming email.
	@@ -673,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	*/
	@@ -828,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 */
	@@ -999,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);
	@@ -1013,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);
	@@ -1039,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 */
	@@ -1077,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

M src/stat.c

+61 -48

		--- src/stat.c
		+++ src/stat.c
		@@ -51,10 +51,70 @@
51	51	sqlite3_snprintf(nOut, zOut, "%.1fMB", (double)v/1000000.0);
52	52	}else{
53	53	sqlite3_snprintf(nOut, zOut, "%.1fGB", (double)v/1000000000.0);
54	54	}
55	55	}
	56	+
	57	+/*
	58	+** Generate stats for the email notification subsystem.
	59	+*/
	60	+void stats_for_email(void){
	61	+ const char *zDest = db_get("email-send-method",0);
	62	+ int nSub, nASub, nPend, nDPend;
	63	+ const char zDir, zDb, zCmd, zRelay;
	64	+ @ <tr><th>Outgoing Email:</th><td>
	65	+ if( fossil_strcmp(zDest,"pipe")==0
	66	+ && (zCmd = db_get("email-send-command",0))!=0
	67	+ ){
	68	+ @ Piped to command "%h(zCmd)"
	69	+ }else
	70	+ if( fossil_strcmp(zDest,"db")==0
	71	+ && (zDb = db_get("email-send-db",0))!=0
	72	+ ){
	73	+ sqlite3 *db;
	74	+ sqlite3_stmt *pStmt;
	75	+ int rc;
	76	+ @ Queued to database "%h(zDb)"
	77	+ rc = sqlite3_open(zDb, &db);
	78	+ if( rc==SQLITE_OK ){
	79	+ rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM email",-1,&pStmt,0);
	80	+ if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
	81	+ @ (%,d(sqlite3_column_int(pStmt,0)) messages,
	82	+ @ %,d(file_size(zDb,ExtFILE)) bytes)
	83	+ }
	84	+ sqlite3_finalize(pStmt);
	85	+ }
	86	+ sqlite3_close(db);
	87	+ }else
	88	+ if( fossil_strcmp(zDest,"dir")==0
	89	+ && (zDir = db_get("email-send-dir",0))!=0
	90	+ ){
	91	+ @ Written to files in "%h(zDir)"
	92	+ @ (%,d(file_directory_size(zDir,0,1)) messages)
	93	+ }else
	94	+ if( fossil_strcmp(zDest,"relay")==0
	95	+ && (zRelay = db_get("email-send-relayhost",0))!=0
	96	+ ){
	97	+ @ Relay to %h(zRelay) using SMTP
	98	+ }
	99	+ else{
	100	+ @ Off
	101	+ }
	102	+ @ </td></tr>
	103	+ nPend = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
	104	+ nDPend = db_int(0,"SELECT count(*) FROM pending_alert"
	105	+ " WHERE NOT sentDigest");
	106	+ @ <tr><th>Pending Alerts:</th><td>
	107	+ @ %,d(nPend) normal, %,d(nDPend) digest
	108	+ @ </td></tr>
	109	+ @ <tr><th>Subscribers:</th><td>
	110	+ nSub = db_int(0, "SELECT count(*) FROM subscriber");
	111	+ nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
	112	+ " AND NOT sdonotcall AND length(ssub)>1");
	113	+ @ %,d(nASub) active, %,d(nSub) total
	114	+ @ </td></tr>
	115	+}
56	116
57	117	/*
58	118	** WEBPAGE: stat
59	119	**
60	120	** Show statistics and global information about the repository.
		@@ -207,58 +267,11 @@
207	267	@ <td><a href='%R/errorlog'>%h(g.zErrlog)</a> (%,lld(szFile) bytes)
208	268	}
209	269	@ </td></tr>
210	270	}
211	271	if( g.perm.Admin && email_enabled() ){
212		- const char *zDest = db_get("email-send-method",0);
213		- int nSub, nASub, nPend, nDPend;
214		- const char zDir, zDb, *zCmd;
215		- @ <tr><th>Outgoing Email:</th><td>
216		- if( fossil_strcmp(zDest,"pipe")==0
217		- && (zCmd = db_get("email-send-command",0))!=0
218		- ){
219		- @ Piped to command "%h(zCmd)"
220		- }else
221		- if( fossil_strcmp(zDest,"db")==0
222		- && (zDb = db_get("email-send-db",0))!=0
223		- ){
224		- sqlite3 *db;
225		- sqlite3_stmt *pStmt;
226		- int rc;
227		- @ Queued to database "%h(zDb)"
228		- rc = sqlite3_open(zDb, &db);
229		- if( rc==SQLITE_OK ){
230		- rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM email",-1,&pStmt,0);
231		- if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
232		- @ (%,d(sqlite3_column_int(pStmt,0)) messages,
233		- @ %,d(file_size(zDb,ExtFILE)) bytes)
234		- }
235		- sqlite3_finalize(pStmt);
236		- }
237		- sqlite3_close(db);
238		- }else
239		- if( fossil_strcmp(zDest,"dir")==0
240		- && (zDir = db_get("email-send-dir",0))!=0
241		- ){
242		- @ Written to files in "%h(zDir)"
243		- @ (%,d(file_directory_size(zDir,0,1)) messages)
244		- }else{
245		- @ Off
246		- }
247		- @ </td></tr>
248		- nPend = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
249		- nDPend = db_int(0,"SELECT count(*) FROM pending_alert"
250		- " WHERE NOT sentDigest");
251		- @ <tr><th>Pending Alerts:</th><td>
252		- @ %,d(nPend) normal, %,d(nDPend) digest
253		- @ </td></tr>
254		- @ <tr><th>Subscribers:</th><td>
255		- nSub = db_int(0, "SELECT count(*) FROM subscriber");
256		- nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
257		- " AND NOT sdonotcall AND length(ssub)>1");
258		- @ %,d(nASub) active, %,d(nSub) total
259		- @ </td></tr>
	272	+ stats_for_email();
260	273	}
261	274
262	275	@ </table>
263	276	style_footer();
264	277	}
265	278

	--- src/stat.c
	+++ src/stat.c
	@@ -51,10 +51,70 @@
51	sqlite3_snprintf(nOut, zOut, "%.1fMB", (double)v/1000000.0);
52	}else{
53	sqlite3_snprintf(nOut, zOut, "%.1fGB", (double)v/1000000000.0);
54	}
55	}




























































56
57	/*
58	** WEBPAGE: stat
59	**
60	** Show statistics and global information about the repository.
	@@ -207,58 +267,11 @@
207	@ <td><a href='%R/errorlog'>%h(g.zErrlog)</a> (%,lld(szFile) bytes)
208	}
209	@ </td></tr>
210	}
211	if( g.perm.Admin && email_enabled() ){
212	const char *zDest = db_get("email-send-method",0);
213	int nSub, nASub, nPend, nDPend;
214	const char zDir, zDb, *zCmd;
215	@ <tr><th>Outgoing Email:</th><td>
216	if( fossil_strcmp(zDest,"pipe")==0
217	&& (zCmd = db_get("email-send-command",0))!=0
218	){
219	@ Piped to command "%h(zCmd)"
220	}else
221	if( fossil_strcmp(zDest,"db")==0
222	&& (zDb = db_get("email-send-db",0))!=0
223	){
224	sqlite3 *db;
225	sqlite3_stmt *pStmt;
226	int rc;
227	@ Queued to database "%h(zDb)"
228	rc = sqlite3_open(zDb, &db);
229	if( rc==SQLITE_OK ){
230	rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM email",-1,&pStmt,0);
231	if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
232	@ (%,d(sqlite3_column_int(pStmt,0)) messages,
233	@ %,d(file_size(zDb,ExtFILE)) bytes)
234	}
235	sqlite3_finalize(pStmt);
236	}
237	sqlite3_close(db);
238	}else
239	if( fossil_strcmp(zDest,"dir")==0
240	&& (zDir = db_get("email-send-dir",0))!=0
241	){
242	@ Written to files in "%h(zDir)"
243	@ (%,d(file_directory_size(zDir,0,1)) messages)
244	}else{
245	@ Off
246	}
247	@ </td></tr>
248	nPend = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
249	nDPend = db_int(0,"SELECT count(*) FROM pending_alert"
250	" WHERE NOT sentDigest");
251	@ <tr><th>Pending Alerts:</th><td>
252	@ %,d(nPend) normal, %,d(nDPend) digest
253	@ </td></tr>
254	@ <tr><th>Subscribers:</th><td>
255	nSub = db_int(0, "SELECT count(*) FROM subscriber");
256	nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
257	" AND NOT sdonotcall AND length(ssub)>1");
258	@ %,d(nASub) active, %,d(nSub) total
259	@ </td></tr>
260	}
261
262	@ </table>
263	style_footer();
264	}
265

	--- src/stat.c
	+++ src/stat.c
	@@ -51,10 +51,70 @@
51	sqlite3_snprintf(nOut, zOut, "%.1fMB", (double)v/1000000.0);
52	}else{
53	sqlite3_snprintf(nOut, zOut, "%.1fGB", (double)v/1000000000.0);
54	}
55	}
56
57	/*
58	** Generate stats for the email notification subsystem.
59	*/
60	void stats_for_email(void){
61	const char *zDest = db_get("email-send-method",0);
62	int nSub, nASub, nPend, nDPend;
63	const char zDir, zDb, zCmd, zRelay;
64	@ <tr><th>Outgoing Email:</th><td>
65	if( fossil_strcmp(zDest,"pipe")==0
66	&& (zCmd = db_get("email-send-command",0))!=0
67	){
68	@ Piped to command "%h(zCmd)"
69	}else
70	if( fossil_strcmp(zDest,"db")==0
71	&& (zDb = db_get("email-send-db",0))!=0
72	){
73	sqlite3 *db;
74	sqlite3_stmt *pStmt;
75	int rc;
76	@ Queued to database "%h(zDb)"
77	rc = sqlite3_open(zDb, &db);
78	if( rc==SQLITE_OK ){
79	rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM email",-1,&pStmt,0);
80	if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
81	@ (%,d(sqlite3_column_int(pStmt,0)) messages,
82	@ %,d(file_size(zDb,ExtFILE)) bytes)
83	}
84	sqlite3_finalize(pStmt);
85	}
86	sqlite3_close(db);
87	}else
88	if( fossil_strcmp(zDest,"dir")==0
89	&& (zDir = db_get("email-send-dir",0))!=0
90	){
91	@ Written to files in "%h(zDir)"
92	@ (%,d(file_directory_size(zDir,0,1)) messages)
93	}else
94	if( fossil_strcmp(zDest,"relay")==0
95	&& (zRelay = db_get("email-send-relayhost",0))!=0
96	){
97	@ Relay to %h(zRelay) using SMTP
98	}
99	else{
100	@ Off
101	}
102	@ </td></tr>
103	nPend = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
104	nDPend = db_int(0,"SELECT count(*) FROM pending_alert"
105	" WHERE NOT sentDigest");
106	@ <tr><th>Pending Alerts:</th><td>
107	@ %,d(nPend) normal, %,d(nDPend) digest
108	@ </td></tr>
109	@ <tr><th>Subscribers:</th><td>
110	nSub = db_int(0, "SELECT count(*) FROM subscriber");
111	nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
112	" AND NOT sdonotcall AND length(ssub)>1");
113	@ %,d(nASub) active, %,d(nSub) total
114	@ </td></tr>
115	}
116
117	/*
118	** WEBPAGE: stat
119	**
120	** Show statistics and global information about the repository.
	@@ -207,58 +267,11 @@
267	@ <td><a href='%R/errorlog'>%h(g.zErrlog)</a> (%,lld(szFile) bytes)
268	}
269	@ </td></tr>
270	}
271	if( g.perm.Admin && email_enabled() ){
272	stats_for_email();















































273	}
274
275	@ </table>
276	style_footer();
277	}
278

M src/webmail.c

+389 -81

		--- src/webmail.c
		+++ src/webmail.c
		@@ -352,26 +352,242 @@
352	352	}
353	353	}
354	354	emailtoc_free(p);
355	355	blob_reset(&email);
356	356	}
	357	+
	358	+/*
	359	+** Add the select/option box to the timeline submenu that shows
	360	+** the various email message formats.
	361	+*/
	362	+static void webmail_f_submenu(void){
	363	+ static const char *az[] = {
	364	+ "0", "Normal",
	365	+ "1", "Decoded",
	366	+ "2", "Raw",
	367	+ };
	368	+ style_submenu_multichoice("f", sizeof(az)/(2*sizeof(az[0])), az, 0);
	369	+}
	370	+
	371	+/*
	372	+** If the first N characters of z[] are the name of a header field
	373	+** that should be shown in "Normal" mode, then return 1.
	374	+*/
	375	+static int webmail_normal_header(const char *z, int N){
	376	+ static const char *az[] = {
	377	+ "To", "Cc", "Bcc", "Date", "From", "Subject",
	378	+ };
	379	+ int i;
	380	+ for(i=0; i<sizeof(az)/sizeof(az[0]); i++){
	381	+ if( sqlite3_strnicmp(z, az[i], N)==0 ) return 1;
	382	+ }
	383	+ return 0;
	384	+}
	385	+
	386	+/*
	387	+** Paint a page showing a single email message
	388	+*/
	389	+static void webmail_show_one_message(
	390	+ HQuery pUrl, / Calling context */
	391	+ int emailid, /* emailbox.ebid to display */
	392	+ const char zUser / User who owns it, or NULL if does not matter */
	393	+){
	394	+ Blob sql;
	395	+ Stmt q;
	396	+ int eState = -1;
	397	+ char zENum[30];
	398	+ style_submenu_element("Index", "%s", url_render(pUrl,"id",0,0,0));
	399	+ webmail_f_submenu();
	400	+ blob_init(&sql, 0, 0);
	401	+ db_begin_transaction();
	402	+ blob_append_sql(&sql,
	403	+ "SELECT decompress(etxt), estate"
	404	+ " FROM emailblob, emailbox"
	405	+ " WHERE emailid=emsgid AND ebid=%d",
	406	+ emailid
	407	+ );
	408	+ if( zUser ) blob_append_sql(&sql, " AND euser=%Q", zUser);
	409	+ db_prepare_blob(&q, &sql);
	410	+ blob_reset(&sql);
	411	+ style_header("Message %d",emailid);
	412	+ if( db_step(&q)==SQLITE_ROW ){
	413	+ Blob msg = db_column_text_as_blob(&q, 0);
	414	+ int eFormat = atoi(PD("f","0"));
	415	+ eState = db_column_int(&q, 1);
	416	+ if( eFormat==2 ){
	417	+ @ <pre>%h(db_column_text(&q, 0))</pre>
	418	+ }else{
	419	+ EmailToc *p = emailtoc_from_email(&msg);
	420	+ int i, j;
	421	+ @ <p>
	422	+ for(i=0; i<p->nHdr; i++){
	423	+ char *z = p->azHdr[i];
	424	+ email_hdr_unfold(z);
	425	+ for(j=0; z[j] && z[j]!=':'; j++){}
	426	+ if( eFormat==0 && !webmail_normal_header(z, j) ) continue;
	427	+ if( z[j]!=':' ){
	428	+ @ %h(z)<br>
	429	+ }else{
	430	+ z[j] = 0;
	431	+ @ <b>%h(z):</b> %h(z+j+1)<br>
	432	+ }
	433	+ }
	434	+ for(i=0; i<p->nBody; i++){
	435	+ @ <hr><b>Messsage Body #%d(i): %h(p->aBody[i].zMimetype) \
	436	+ if( p->aBody[i].zFilename ){
	437	+ @ "%h(p->aBody[i].zFilename)"
	438	+ }
	439	+ @ </b>
	440	+ if( eFormat==0 ){
	441	+ if( strncmp(p->aBody[i].zMimetype, "text/plain", 10)!=0 ) continue;
	442	+ if( p->aBody[i].zFilename ) continue;
	443	+ }else{
	444	+ if( strncmp(p->aBody[i].zMimetype, "text/", 5)!=0 ) continue;
	445	+ }
	446	+ switch( p->aBody[i].encoding ){
	447	+ case EMAILENC_B64: {
	448	+ int n = 0;
	449	+ decodeBase64(p->aBody[i].zContent, &n, p->aBody[i].zContent);
	450	+ break;
	451	+ }
	452	+ case EMAILENC_QUOTED: {
	453	+ int n = 0;
	454	+ decodeQuotedPrintable(p->aBody[i].zContent, &n);
	455	+ break;
	456	+ }
	457	+ }
	458	+ @ <pre>%h(p->aBody[i].zContent)</pre>
	459	+ }
	460	+ }
	461	+ }
	462	+ db_finalize(&q);
	463	+
	464	+ if( eState==0 ){
	465	+ /* If is message is currently Unread, change it to Read */
	466	+ blob_append_sql(&sql,
	467	+ "UPDATE emailbox SET estate=1 "
	468	+ " WHERE estate=0 AND ebid=%d",
	469	+ emailid
	470	+ );
	471	+ if( zUser ) blob_append_sql(&sql, " AND euser=%Q", zUser);
	472	+ db_multi_exec("%s", blob_sql_text(&sql));
	473	+ blob_reset(&sql);
	474	+ eState = 1;
	475	+ }
	476	+
	477	+ url_add_parameter(pUrl, "id", 0);
	478	+ sqlite3_snprintf(sizeof(zENum), zENum, "e%d", emailid);
	479	+ if( eState==2 ){
	480	+ style_submenu_element("Undelete","%s",
	481	+ url_render(pUrl,"read","1",zENum,"1"));
	482	+ }
	483	+ if( eState==1 ){
	484	+ style_submenu_element("Delete", "%s",
	485	+ url_render(pUrl,"trash","1",zENum,"1"));
	486	+ style_submenu_element("Mark As Unread", "%s",
	487	+ url_render(pUrl,"unread","1",zENum,"1"));
	488	+ }
	489	+ if( eState==3 ){
	490	+ style_submenu_element("Delete", "%s",
	491	+ url_render(pUrl,"trash","1",zENum,"1"));
	492	+ }
	493	+
	494	+ db_end_transaction(0);
	495	+ style_footer();
	496	+ return;
	497	+}
	498	+
	499	+/*
	500	+** Scan the query parameters looking for parameters with name of the
	501	+** form "eN" where N is an integer. For all such integers, change
	502	+** the state of every emailbox entry with ebid==N to eStateNew provided
	503	+** that either zUser is NULL or matches.
	504	+**
	505	+** Or if eNewState==99, then delete the entries.
	506	+*/
	507	+static void webmail_change_state(int eNewState, const char *zUser){
	508	+ Blob sql;
	509	+ int sep = '(';
	510	+ int i;
	511	+ const char *zName;
	512	+ int n;
	513	+ if( !cgi_csrf_safe(0) ) return;
	514	+ blob_init(&sql, 0, 0);
	515	+ if( eNewState==99 ){
	516	+ blob_append_sql(&sql, "DELETE FROM emailbox WHERE estate==2 AND ebid IN ");
	517	+ }else{
	518	+ blob_append_sql(&sql, "UPDATE emailbox SET estate=%d WHERE ebid IN ",
	519	+ eNewState);
	520	+ }
	521	+ for(i=0; (zName = cgi_parameter_name(i))!=0; i++){
	522	+ if( zName[0]!='e' ) continue;
	523	+ if( !fossil_isdigit(zName[1]) ) continue;
	524	+ n = atoi(zName+1);
	525	+ blob_append_sql(&sql, "%c%d", sep, n);
	526	+ sep = ',';
	527	+ }
	528	+ if( zUser ){
	529	+ blob_append_sql(&sql, ") AND euser=%Q", zUser);
	530	+ }else{
	531	+ blob_append_sql(&sql, ")");
	532	+ }
	533	+ if( sep==',' ){
	534	+ db_multi_exec("%s", blob_sql_text(&sql));
	535	+ }
	536	+ blob_reset(&sql);
	537	+}
	538	+
	539	+
	540	+/*
	541	+** Add the select/option box to the timeline submenu that shows
	542	+** which messages to include in the index.
	543	+*/
	544	+static void webmail_d_submenu(void){
	545	+ static const char *az[] = {
	546	+ "0", "InBox",
	547	+ "1", "Unread",
	548	+ "2", "Trash",
	549	+ "3", "Sent",
	550	+ "4", "Everything",
	551	+ };
	552	+ style_submenu_multichoice("d", sizeof(az)/(2*sizeof(az[0])), az, 0);
	553	+}
357	554
358	555	/*
359	556	** WEBPAGE: webmail
360	557	**
361	558	** This page can be used to read content from the EMAILBOX table
362	559	** that contains email received by the "fossil smtpd" command.
	560	+**
	561	+** Query parameters:
	562	+**
	563	+** id=N Show a single email entry emailbox.ebid==N
	564	+** f=N Display format. 0: decoded 1: raw
	565	+** user=USER Show mailbox for USER (admin only).
	566	+** user=* Show mailbox for all users (admin only).
	567	+** d=N 0: inbox+unread 1: unread-only 2: trash 3: all
	568	+** eN Select email entry emailbox.ebid==N
	569	+** trash Move selected entries to trash (estate=2)
	570	+** read Mark selected entries as read (estate=1)
	571	+** unread Mark selected entries as unread (estate=0)
	572	+**
363	573	*/
364	574	void webmail_page(void){
365	575	int emailid;
366	576	Stmt q;
367	577	Blob sql;
368	578	int showAll = 0;
369	579	const char *zUser = 0;
	580	+ int d = 0; /* Display mode. 0..3. d= query parameter */
	581	+ int pg = 0; /* Page number */
	582	+ int N = 50; /* Results per page */
	583	+ int got; /* Number of results on this page */
	584	+ char zPPg[30]; /* Previous page */
	585	+ char zNPg[30]; /* Next page */
370	586	HQuery url;
371	587	login_check_credentials();
372		- if( g.zLogin==0 ){
	588	+ if( !login_is_individual() ){
373	589	login_needed(0);
374	590	return;
375	591	}
376	592	if( !db_table_exists("repository","emailbox") ){
377	593	style_header("Webmail Not Available");
		@@ -389,116 +605,208 @@
389	605	if( fossil_strcmp(zUser,"*")==0 ){
390	606	showAll = 1;
391	607	zUser = 0;
392	608	}
393	609	}
	610	+ }else{
	611	+ zUser = g.zLogin;
394	612	}
	613	+ if( P("d") ) url_add_parameter(&url, "d", P("d"));
395	614	if( emailid>0 ){
396		- style_submenu_element("Index", "%s", url_render(&url,"id",0,0,0));
397		- blob_init(&sql, 0, 0);
398		- blob_append_sql(&sql, "SELECT decompress(etxt)"
399		- " FROM emailblob WHERE emailid=%d",
400		- emailid);
401		- if( !g.perm.Admin ){
402		- blob_append_sql(&sql, " AND EXISTS(SELECT 1 FROM emailbox WHERE"
403		- " euser=%Q AND emsgid=emailid)", g.zLogin);
404		- }
405		- db_prepare_blob(&q, &sql);
406		- blob_reset(&sql);
407		- if( db_step(&q)==SQLITE_ROW ){
408		- Blob msg = db_column_text_as_blob(&q, 0);
409		- url_add_parameter(&url, "id", P("id"));
410		- style_header("Message %d",emailid);
411		- if( PB("raw") ){
412		- @ <pre>%h(db_column_text(&q, 0))</pre>
413		- style_submenu_element("Decoded", "%s", url_render(&url,"raw",0,0,0));
414		- }else{
415		- EmailToc *p = emailtoc_from_email(&msg);
416		- int i, j;
417		- style_submenu_element("Raw", "%s", url_render(&url,"raw","1",0,0));
418		- @ <p>
419		- for(i=0; i<p->nHdr; i++){
420		- char *z = p->azHdr[i];
421		- email_hdr_unfold(z);
422		- for(j=0; z[j] && z[j]!=':'; j++){}
423		- if( z[j]!=':' ){
424		- @ %h(z)<br>
425		- }else{
426		- z[j] = 0;
427		- @ <b>%h(z):</b> %h(z+j+1)<br>
428		- }
429		- }
430		- for(i=0; i<p->nBody; i++){
431		- @ <hr><b>Messsage Body #%d(i): %h(p->aBody[i].zMimetype) \
432		- if( p->aBody[i].zFilename ){
433		- @ "%h(p->aBody[i].zFilename)"
434		- }
435		- @ </b>
436		- if( strncmp(p->aBody[i].zMimetype, "text/", 5)!=0 ) continue;
437		- switch( p->aBody[i].encoding ){
438		- case EMAILENC_B64: {
439		- int n = 0;
440		- decodeBase64(p->aBody[i].zContent, &n, p->aBody[i].zContent);
441		- break;
442		- }
443		- case EMAILENC_QUOTED: {
444		- int n = 0;
445		- decodeQuotedPrintable(p->aBody[i].zContent, &n);
446		- break;
447		- }
448		- }
449		- @ <pre>%h(p->aBody[i].zContent)</pre>
450		- }
451		- }
452		- style_footer();
453		- db_finalize(&q);
454		- return;
455		- }
456		- db_finalize(&q);
	615	+ webmail_show_one_message(&url, emailid, zUser);
	616	+ return;
457	617	}
458	618	style_header("Webmail");
	619	+ webmail_d_submenu();
	620	+ db_begin_transaction();
	621	+ if( P("trash")!=0 ) webmail_change_state(2,zUser);
	622	+ if( P("unread")!=0 ) webmail_change_state(0,zUser);
	623	+ if( P("read")!=0 ) webmail_change_state(1,zUser);
	624	+ if( P("purge")!=0 ) webmail_change_state(99,zUser);
459	625	blob_init(&sql, 0, 0);
460	626	blob_append_sql(&sql,
461		- /* 0 1 2 3 4 5 */
462		- "SELECT efrom, datetime(edate,'unixepoch'), estate, esubject, emsgid, euser"
	627	+ "CREATE TEMP TABLE tmbox AS "
	628	+ "SELECT ebid," /* 0 */
	629	+ " efrom," /* 1 */
	630	+ " datetime(edate,'unixepoch')," /* 2 */
	631	+ " estate," /* 3 */
	632	+ " esubject," /* 4 */
	633	+ " euser" /* 5 */
463	634	" FROM emailbox"
464	635	);
	636	+ d = atoi(PD("d","0"));
	637	+ switch( d ){
	638	+ case 0: { /* Show unread and read */
	639	+ blob_append_sql(&sql, " WHERE estate<=1");
	640	+ break;
	641	+ }
	642	+ case 1: { /* Unread messages only */
	643	+ blob_append_sql(&sql, " WHERE estate=0");
	644	+ break;
	645	+ }
	646	+ case 2: { /* Trashcan only */
	647	+ blob_append_sql(&sql, " WHERE estate=2");
	648	+ break;
	649	+ }
	650	+ case 3: { /* Outgoing email only */
	651	+ blob_append_sql(&sql, " WHERE estate=3");
	652	+ break;
	653	+ }
	654	+ case 4: { /* Everything */
	655	+ blob_append_sql(&sql, " WHERE 1");
	656	+ break;
	657	+ }
	658	+ }
465	659	if( showAll ){
466	660	style_submenu_element("My Emails", "%s", url_render(&url,"user",0,0,0));
467	661	}else if( zUser!=0 ){
468	662	style_submenu_element("All Users", "%s", url_render(&url,"user","*",0,0));
469	663	if( fossil_strcmp(zUser, g.zLogin)!=0 ){
470	664	style_submenu_element("My Emails", "%s", url_render(&url,"user",0,0,0));
471	665	}
472	666	if( zUser ){
473		- blob_append_sql(&sql, " WHERE euser=%Q", zUser);
	667	+ blob_append_sql(&sql, " AND euser=%Q", zUser);
474	668	}else{
475		- blob_append_sql(&sql, " WHERE euser=%Q", g.zLogin);
	669	+ blob_append_sql(&sql, " AND euser=%Q", g.zLogin);
476	670	}
477	671	}else{
478	672	if( g.perm.Admin ){
479	673	style_submenu_element("All Users", "%s", url_render(&url,"user","*",0,0));
480	674	}
481		- blob_append_sql(&sql, " WHERE euser=%Q", g.zLogin);
	675	+ blob_append_sql(&sql, " AND euser=%Q", g.zLogin);
482	676	}
483		- blob_append_sql(&sql, " ORDER BY edate DESC limit 50");
484		- db_prepare_blob(&q, &sql);
	677	+ pg = atoi(PD("pg","0"));
	678	+ blob_append_sql(&sql, " ORDER BY edate DESC limit %d offset %d", N+1, pg*N);
	679	+ db_multi_exec("%s", blob_sql_text(&sql));
	680	+ got = db_int(0, "SELECT count(*) FROM tmbox");
	681	+ db_prepare(&q, "SELECT * FROM tmbox LIMIT %d", N);
485	682	blob_reset(&sql);
486		- @ <ol>
	683	+ @ <form action="%R/webmail" method="POST">
	684	+ @ <table border="0" width="100%%">
	685	+ @ <tr><td align="left">
	686	+ if( d==2 ){
	687	+ @ <input type="submit" name="read" value="Undelete">
	688	+ @ <input type="submit" name="purge" value="Delete Permanently">
	689	+ }else{
	690	+ @ <input type="submit" name="trash", value="Delete">
	691	+ if( d!=1 ){
	692	+ @ <input type="submit" name="unread" value="Mark as unread">
	693	+ }
	694	+ @ <input type="submit" name="read" value="Mark as read">
	695	+ }
	696	+ @ <a href="%s(url_render(&url,0,0,0,0))">refresh</a>
	697	+ @ </td><td align="right">
	698	+ if( pg>0 ){
	699	+ sqlite3_snprintf(sizeof(zPPg), zPPg, "%d", pg-1);
	700	+ @ <a href="%s(url_render(&url,"pg",zPPg,0,0))">< Newer</a>
	701	+ }
	702	+ if( got>50 ){
	703	+ sqlite3_snprintf(sizeof(zNPg),zNPg,"%d",pg+1);
	704	+ @ <a href="%s(url_render(&url,"pg",zNPg,0,0))">Older ></a></td>
	705	+ }
	706	+ @ </table>
	707	+ @ <table>
487	708	while( db_step(&q)==SQLITE_ROW ){
488		- const char *zId = db_column_text(&q,4);
489		- const char *zFrom = db_column_text(&q, 0);
490		- const char *zDate = db_column_text(&q, 1);
491		- const char *zSubject = db_column_text(&q, 3);
492		- @ <li>
	709	+ const char *zId = db_column_text(&q,0);
	710	+ const char *zFrom = db_column_text(&q, 1);
	711	+ const char *zDate = db_column_text(&q, 2);
	712	+ const char *zSubject = db_column_text(&q, 4);
	713	+ @ <tr>
	714	+ @ <td><input type="checkbox" name="e%s(zId)"></td>
	715	+ @ <td>%h(zFrom)</td>
	716	+ @ <td><a href="%s(url_render(&url,"id",zId,0,0))">%h(zSubject)</a> \
	717	+ @ %s(zDate)</td>
493	718	if( showAll ){
494	719	const char *zTo = db_column_text(&q,5);
495		- @ <a href="%s(url_render(&url,"user",zTo,0,0))">%h(zTo)</a>:
	720	+ @ <td><a href="%s(url_render(&url,"user",zTo,0,0))">%h(zTo)</a></td>
496	721	}
497		- @ <a href="%s(url_render(&url,"id",zId,0,0))">\
498		- @ %h(zFrom) → %h(zSubject)</a>
499		- @ %h(zDate)
	722	+ @ </tr>
500	723	}
501	724	db_finalize(&q);
502		- @ </ol>
503		- style_footer();
	725	+ @ </table>
	726	+ @ </form>
	727	+ style_footer();
	728	+ db_end_transaction(0);
	729	+}
	730	+
	731	+/*
	732	+** WEBPAGE: test-emailblob
	733	+**
	734	+** This page, accessible only to administrators, allows easy viewing of
	735	+** the emailblob table - the table that contains the text of email messages
	736	+** both inbound and outbound, and transcripts of SMTP sessions.
	737	+**
	738	+** id=N Show the text of emailblob with emailid==N
	739	+**
	740	+*/
	741	+void webmail_emailblob_page(void){
	742	+ int id = atoi(PD("id","0"));
	743	+ Stmt q;
	744	+ login_check_credentials();
	745	+ if( !g.perm.Setup ){
	746	+ login_needed(0);
	747	+ return;
	748	+ }
	749	+ add_content_sql_commands(g.db);
	750	+ style_header("emailblob table");
	751	+ if( id>0 ){
	752	+ style_submenu_element("Index", "%R/test-emailblob");
	753	+ @ <ul>
	754	+ db_prepare(&q, "SELECT emailid FROM emailblob WHERE ets=%d", id);
	755	+ while( db_step(&q)==SQLITE_ROW ){
	756	+ int id = db_column_int(&q, 0);
	757	+ @ <li> <a href="%R/test-emailblob?id=%d(id)">emailblob entry %d(id)</a>
	758	+ }
	759	+ db_finalize(&q);
	760	+ db_prepare(&q, "SELECT euser, estate FROM emailbox WHERE emsgid=%d", id);
	761	+ while( db_step(&q)==SQLITE_ROW ){
	762	+ const char *zUser = db_column_text(&q, 0);
	763	+ int e = db_column_int(&q, 1);
	764	+ @ <li> emailbox for %h(zUser) state %d(e)
	765	+ }
	766	+ db_finalize(&q);
	767	+ db_prepare(&q, "SELECT efrom, eto FROM emailoutq WHERE emsgid=%d", id);
	768	+ while( db_step(&q)==SQLITE_ROW ){
	769	+ const char *zFrom = db_column_text(&q, 0);
	770	+ const char *zTo = db_column_text(&q, 1);
	771	+ @ <li> emailoutq message body from %h(zFrom) to %h(zTo)
	772	+ }
	773	+ db_finalize(&q);
	774	+ db_prepare(&q, "SELECT efrom, eto FROM emailoutq WHERE ets=%d", id);
	775	+ while( db_step(&q)==SQLITE_ROW ){
	776	+ const char *zFrom = db_column_text(&q, 0);
	777	+ const char *zTo = db_column_text(&q, 1);
	778	+ @ <li> emailoutq transcript from %h(zFrom) to %h(zTo)
	779	+ }
	780	+ db_finalize(&q);
	781	+ @ </ul>
	782	+ @ <hr>
	783	+ db_prepare(&q, "SELECT decompress(etxt) FROM emailblob WHERE emailid=%d",
	784	+ id);
	785	+ while( db_step(&q)==SQLITE_ROW ){
	786	+ const char *zContent = db_column_text(&q, 0);
	787	+ @ <pre>%h(zContent)</pre>
	788	+ }
	789	+ db_finalize(&q);
	790	+ }else{
	791	+ db_prepare(&q,
	792	+ "SELECT emailid, enref, ets, datetime(etime,'unixepoch')"
	793	+ " FROM emailblob ORDER BY etime DESC, emailid DESC");
	794	+ @ <table border="1" cellpadding="5" cellspacing="0">
	795	+ @ <tr><th> emailid <th> enref <th> ets <th> etime</tr>
	796	+ while( db_step(&q)==SQLITE_ROW ){
	797	+ int id = db_column_int(&q, 0);
	798	+ int nref = db_column_int(&q, 1);
	799	+ int ets = db_column_int(&q, 2);
	800	+ const char *zDate = db_column_text(&q, 3);
	801	+ @ <tr>
	802	+ @ <td><a href="%R/test-emailblob?id=%d(id)">%d(id)</a>
	803	+ @ <td>%d(nref)</td>
	804	+ @ <td>%d(ets)</td>
	805	+ @ <td>%h(zDate)</td>
	806	+ @ </tr>
	807	+ }
	808	+ @ </table>
	809	+ db_finalize(&q);
	810	+ }
	811	+ style_footer();
504	812	}
505	813

	--- src/webmail.c
	+++ src/webmail.c
	@@ -352,26 +352,242 @@
352	}
353	}
354	emailtoc_free(p);
355	blob_reset(&email);
356	}





































































































































































































357
358	/*
359	** WEBPAGE: webmail
360	**
361	** This page can be used to read content from the EMAILBOX table
362	** that contains email received by the "fossil smtpd" command.













363	*/
364	void webmail_page(void){
365	int emailid;
366	Stmt q;
367	Blob sql;
368	int showAll = 0;
369	const char *zUser = 0;






370	HQuery url;
371	login_check_credentials();
372	if( g.zLogin==0 ){
373	login_needed(0);
374	return;
375	}
376	if( !db_table_exists("repository","emailbox") ){
377	style_header("Webmail Not Available");
	@@ -389,116 +605,208 @@
389	if( fossil_strcmp(zUser,"*")==0 ){
390	showAll = 1;
391	zUser = 0;
392	}
393	}


394	}

395	if( emailid>0 ){
396	style_submenu_element("Index", "%s", url_render(&url,"id",0,0,0));
397	blob_init(&sql, 0, 0);
398	blob_append_sql(&sql, "SELECT decompress(etxt)"
399	" FROM emailblob WHERE emailid=%d",
400	emailid);
401	if( !g.perm.Admin ){
402	blob_append_sql(&sql, " AND EXISTS(SELECT 1 FROM emailbox WHERE"
403	" euser=%Q AND emsgid=emailid)", g.zLogin);
404	}
405	db_prepare_blob(&q, &sql);
406	blob_reset(&sql);
407	if( db_step(&q)==SQLITE_ROW ){
408	Blob msg = db_column_text_as_blob(&q, 0);
409	url_add_parameter(&url, "id", P("id"));
410	style_header("Message %d",emailid);
411	if( PB("raw") ){
412	@ <pre>%h(db_column_text(&q, 0))</pre>
413	style_submenu_element("Decoded", "%s", url_render(&url,"raw",0,0,0));
414	}else{
415	EmailToc *p = emailtoc_from_email(&msg);
416	int i, j;
417	style_submenu_element("Raw", "%s", url_render(&url,"raw","1",0,0));
418	@ <p>
419	for(i=0; i<p->nHdr; i++){
420	char *z = p->azHdr[i];
421	email_hdr_unfold(z);
422	for(j=0; z[j] && z[j]!=':'; j++){}
423	if( z[j]!=':' ){
424	@ %h(z)<br>
425	}else{
426	z[j] = 0;
427	@ <b>%h(z):</b> %h(z+j+1)<br>
428	}
429	}
430	for(i=0; i<p->nBody; i++){
431	@ <hr><b>Messsage Body #%d(i): %h(p->aBody[i].zMimetype) \
432	if( p->aBody[i].zFilename ){
433	@ "%h(p->aBody[i].zFilename)"
434	}
435	@ </b>
436	if( strncmp(p->aBody[i].zMimetype, "text/", 5)!=0 ) continue;
437	switch( p->aBody[i].encoding ){
438	case EMAILENC_B64: {
439	int n = 0;
440	decodeBase64(p->aBody[i].zContent, &n, p->aBody[i].zContent);
441	break;
442	}
443	case EMAILENC_QUOTED: {
444	int n = 0;
445	decodeQuotedPrintable(p->aBody[i].zContent, &n);
446	break;
447	}
448	}
449	@ <pre>%h(p->aBody[i].zContent)</pre>
450	}
451	}
452	style_footer();
453	db_finalize(&q);
454	return;
455	}
456	db_finalize(&q);
457	}
458	style_header("Webmail");






459	blob_init(&sql, 0, 0);
460	blob_append_sql(&sql,
461	/* 0 1 2 3 4 5 */
462	"SELECT efrom, datetime(edate,'unixepoch'), estate, esubject, emsgid, euser"





463	" FROM emailbox"
464	);























465	if( showAll ){
466	style_submenu_element("My Emails", "%s", url_render(&url,"user",0,0,0));
467	}else if( zUser!=0 ){
468	style_submenu_element("All Users", "%s", url_render(&url,"user","*",0,0));
469	if( fossil_strcmp(zUser, g.zLogin)!=0 ){
470	style_submenu_element("My Emails", "%s", url_render(&url,"user",0,0,0));
471	}
472	if( zUser ){
473	blob_append_sql(&sql, " WHERE euser=%Q", zUser);
474	}else{
475	blob_append_sql(&sql, " WHERE euser=%Q", g.zLogin);
476	}
477	}else{
478	if( g.perm.Admin ){
479	style_submenu_element("All Users", "%s", url_render(&url,"user","*",0,0));
480	}
481	blob_append_sql(&sql, " WHERE euser=%Q", g.zLogin);
482	}
483	blob_append_sql(&sql, " ORDER BY edate DESC limit 50");
484	db_prepare_blob(&q, &sql);



485	blob_reset(&sql);
486	@ <ol>
























487	while( db_step(&q)==SQLITE_ROW ){
488	const char *zId = db_column_text(&q,4);
489	const char *zFrom = db_column_text(&q, 0);
490	const char *zDate = db_column_text(&q, 1);
491	const char *zSubject = db_column_text(&q, 3);
492	@ <li>




493	if( showAll ){
494	const char *zTo = db_column_text(&q,5);
495	@ <a href="%s(url_render(&url,"user",zTo,0,0))">%h(zTo)</a>:
496	}
497	@ <a href="%s(url_render(&url,"id",zId,0,0))">\
498	@ %h(zFrom) → %h(zSubject)</a>
499	@ %h(zDate)
500	}
501	db_finalize(&q);
502	@ </ol>
503	style_footer();





















































































504	}
505

	--- src/webmail.c
	+++ src/webmail.c
	@@ -352,26 +352,242 @@
352	}
353	}
354	emailtoc_free(p);
355	blob_reset(&email);
356	}
357
358	/*
359	** Add the select/option box to the timeline submenu that shows
360	** the various email message formats.
361	*/
362	static void webmail_f_submenu(void){
363	static const char *az[] = {
364	"0", "Normal",
365	"1", "Decoded",
366	"2", "Raw",
367	};
368	style_submenu_multichoice("f", sizeof(az)/(2*sizeof(az[0])), az, 0);
369	}
370
371	/*
372	** If the first N characters of z[] are the name of a header field
373	** that should be shown in "Normal" mode, then return 1.
374	*/
375	static int webmail_normal_header(const char *z, int N){
376	static const char *az[] = {
377	"To", "Cc", "Bcc", "Date", "From", "Subject",
378	};
379	int i;
380	for(i=0; i<sizeof(az)/sizeof(az[0]); i++){
381	if( sqlite3_strnicmp(z, az[i], N)==0 ) return 1;
382	}
383	return 0;
384	}
385
386	/*
387	** Paint a page showing a single email message
388	*/
389	static void webmail_show_one_message(
390	HQuery pUrl, / Calling context */
391	int emailid, /* emailbox.ebid to display */
392	const char zUser / User who owns it, or NULL if does not matter */
393	){
394	Blob sql;
395	Stmt q;
396	int eState = -1;
397	char zENum[30];
398	style_submenu_element("Index", "%s", url_render(pUrl,"id",0,0,0));
399	webmail_f_submenu();
400	blob_init(&sql, 0, 0);
401	db_begin_transaction();
402	blob_append_sql(&sql,
403	"SELECT decompress(etxt), estate"
404	" FROM emailblob, emailbox"
405	" WHERE emailid=emsgid AND ebid=%d",
406	emailid
407	);
408	if( zUser ) blob_append_sql(&sql, " AND euser=%Q", zUser);
409	db_prepare_blob(&q, &sql);
410	blob_reset(&sql);
411	style_header("Message %d",emailid);
412	if( db_step(&q)==SQLITE_ROW ){
413	Blob msg = db_column_text_as_blob(&q, 0);
414	int eFormat = atoi(PD("f","0"));
415	eState = db_column_int(&q, 1);
416	if( eFormat==2 ){
417	@ <pre>%h(db_column_text(&q, 0))</pre>
418	}else{
419	EmailToc *p = emailtoc_from_email(&msg);
420	int i, j;
421	@ <p>
422	for(i=0; i<p->nHdr; i++){
423	char *z = p->azHdr[i];
424	email_hdr_unfold(z);
425	for(j=0; z[j] && z[j]!=':'; j++){}
426	if( eFormat==0 && !webmail_normal_header(z, j) ) continue;
427	if( z[j]!=':' ){
428	@ %h(z)<br>
429	}else{
430	z[j] = 0;
431	@ <b>%h(z):</b> %h(z+j+1)<br>
432	}
433	}
434	for(i=0; i<p->nBody; i++){
435	@ <hr><b>Messsage Body #%d(i): %h(p->aBody[i].zMimetype) \
436	if( p->aBody[i].zFilename ){
437	@ "%h(p->aBody[i].zFilename)"
438	}
439	@ </b>
440	if( eFormat==0 ){
441	if( strncmp(p->aBody[i].zMimetype, "text/plain", 10)!=0 ) continue;
442	if( p->aBody[i].zFilename ) continue;
443	}else{
444	if( strncmp(p->aBody[i].zMimetype, "text/", 5)!=0 ) continue;
445	}
446	switch( p->aBody[i].encoding ){
447	case EMAILENC_B64: {
448	int n = 0;
449	decodeBase64(p->aBody[i].zContent, &n, p->aBody[i].zContent);
450	break;
451	}
452	case EMAILENC_QUOTED: {
453	int n = 0;
454	decodeQuotedPrintable(p->aBody[i].zContent, &n);
455	break;
456	}
457	}
458	@ <pre>%h(p->aBody[i].zContent)</pre>
459	}
460	}
461	}
462	db_finalize(&q);
463
464	if( eState==0 ){
465	/* If is message is currently Unread, change it to Read */
466	blob_append_sql(&sql,
467	"UPDATE emailbox SET estate=1 "
468	" WHERE estate=0 AND ebid=%d",
469	emailid
470	);
471	if( zUser ) blob_append_sql(&sql, " AND euser=%Q", zUser);
472	db_multi_exec("%s", blob_sql_text(&sql));
473	blob_reset(&sql);
474	eState = 1;
475	}
476
477	url_add_parameter(pUrl, "id", 0);
478	sqlite3_snprintf(sizeof(zENum), zENum, "e%d", emailid);
479	if( eState==2 ){
480	style_submenu_element("Undelete","%s",
481	url_render(pUrl,"read","1",zENum,"1"));
482	}
483	if( eState==1 ){
484	style_submenu_element("Delete", "%s",
485	url_render(pUrl,"trash","1",zENum,"1"));
486	style_submenu_element("Mark As Unread", "%s",
487	url_render(pUrl,"unread","1",zENum,"1"));
488	}
489	if( eState==3 ){
490	style_submenu_element("Delete", "%s",
491	url_render(pUrl,"trash","1",zENum,"1"));
492	}
493
494	db_end_transaction(0);
495	style_footer();
496	return;
497	}
498
499	/*
500	** Scan the query parameters looking for parameters with name of the
501	** form "eN" where N is an integer. For all such integers, change
502	** the state of every emailbox entry with ebid==N to eStateNew provided
503	** that either zUser is NULL or matches.
504	**
505	** Or if eNewState==99, then delete the entries.
506	*/
507	static void webmail_change_state(int eNewState, const char *zUser){
508	Blob sql;
509	int sep = '(';
510	int i;
511	const char *zName;
512	int n;
513	if( !cgi_csrf_safe(0) ) return;
514	blob_init(&sql, 0, 0);
515	if( eNewState==99 ){
516	blob_append_sql(&sql, "DELETE FROM emailbox WHERE estate==2 AND ebid IN ");
517	}else{
518	blob_append_sql(&sql, "UPDATE emailbox SET estate=%d WHERE ebid IN ",
519	eNewState);
520	}
521	for(i=0; (zName = cgi_parameter_name(i))!=0; i++){
522	if( zName[0]!='e' ) continue;
523	if( !fossil_isdigit(zName[1]) ) continue;
524	n = atoi(zName+1);
525	blob_append_sql(&sql, "%c%d", sep, n);
526	sep = ',';
527	}
528	if( zUser ){
529	blob_append_sql(&sql, ") AND euser=%Q", zUser);
530	}else{
531	blob_append_sql(&sql, ")");
532	}
533	if( sep==',' ){
534	db_multi_exec("%s", blob_sql_text(&sql));
535	}
536	blob_reset(&sql);
537	}
538
539
540	/*
541	** Add the select/option box to the timeline submenu that shows
542	** which messages to include in the index.
543	*/
544	static void webmail_d_submenu(void){
545	static const char *az[] = {
546	"0", "InBox",
547	"1", "Unread",
548	"2", "Trash",
549	"3", "Sent",
550	"4", "Everything",
551	};
552	style_submenu_multichoice("d", sizeof(az)/(2*sizeof(az[0])), az, 0);
553	}
554
555	/*
556	** WEBPAGE: webmail
557	**
558	** This page can be used to read content from the EMAILBOX table
559	** that contains email received by the "fossil smtpd" command.
560	**
561	** Query parameters:
562	**
563	** id=N Show a single email entry emailbox.ebid==N
564	** f=N Display format. 0: decoded 1: raw
565	** user=USER Show mailbox for USER (admin only).
566	** user=* Show mailbox for all users (admin only).
567	** d=N 0: inbox+unread 1: unread-only 2: trash 3: all
568	** eN Select email entry emailbox.ebid==N
569	** trash Move selected entries to trash (estate=2)
570	** read Mark selected entries as read (estate=1)
571	** unread Mark selected entries as unread (estate=0)
572	**
573	*/
574	void webmail_page(void){
575	int emailid;
576	Stmt q;
577	Blob sql;
578	int showAll = 0;
579	const char *zUser = 0;
580	int d = 0; /* Display mode. 0..3. d= query parameter */
581	int pg = 0; /* Page number */
582	int N = 50; /* Results per page */
583	int got; /* Number of results on this page */
584	char zPPg[30]; /* Previous page */
585	char zNPg[30]; /* Next page */
586	HQuery url;
587	login_check_credentials();
588	if( !login_is_individual() ){
589	login_needed(0);
590	return;
591	}
592	if( !db_table_exists("repository","emailbox") ){
593	style_header("Webmail Not Available");
	@@ -389,116 +605,208 @@
605	if( fossil_strcmp(zUser,"*")==0 ){
606	showAll = 1;
607	zUser = 0;
608	}
609	}
610	}else{
611	zUser = g.zLogin;
612	}
613	if( P("d") ) url_add_parameter(&url, "d", P("d"));
614	if( emailid>0 ){
615	webmail_show_one_message(&url, emailid, zUser);
616	return;



























































617	}
618	style_header("Webmail");
619	webmail_d_submenu();
620	db_begin_transaction();
621	if( P("trash")!=0 ) webmail_change_state(2,zUser);
622	if( P("unread")!=0 ) webmail_change_state(0,zUser);
623	if( P("read")!=0 ) webmail_change_state(1,zUser);
624	if( P("purge")!=0 ) webmail_change_state(99,zUser);
625	blob_init(&sql, 0, 0);
626	blob_append_sql(&sql,
627	"CREATE TEMP TABLE tmbox AS "
628	"SELECT ebid," /* 0 */
629	" efrom," /* 1 */
630	" datetime(edate,'unixepoch')," /* 2 */
631	" estate," /* 3 */
632	" esubject," /* 4 */
633	" euser" /* 5 */
634	" FROM emailbox"
635	);
636	d = atoi(PD("d","0"));
637	switch( d ){
638	case 0: { /* Show unread and read */
639	blob_append_sql(&sql, " WHERE estate<=1");
640	break;
641	}
642	case 1: { /* Unread messages only */
643	blob_append_sql(&sql, " WHERE estate=0");
644	break;
645	}
646	case 2: { /* Trashcan only */
647	blob_append_sql(&sql, " WHERE estate=2");
648	break;
649	}
650	case 3: { /* Outgoing email only */
651	blob_append_sql(&sql, " WHERE estate=3");
652	break;
653	}
654	case 4: { /* Everything */
655	blob_append_sql(&sql, " WHERE 1");
656	break;
657	}
658	}
659	if( showAll ){
660	style_submenu_element("My Emails", "%s", url_render(&url,"user",0,0,0));
661	}else if( zUser!=0 ){
662	style_submenu_element("All Users", "%s", url_render(&url,"user","*",0,0));
663	if( fossil_strcmp(zUser, g.zLogin)!=0 ){
664	style_submenu_element("My Emails", "%s", url_render(&url,"user",0,0,0));
665	}
666	if( zUser ){
667	blob_append_sql(&sql, " AND euser=%Q", zUser);
668	}else{
669	blob_append_sql(&sql, " AND euser=%Q", g.zLogin);
670	}
671	}else{
672	if( g.perm.Admin ){
673	style_submenu_element("All Users", "%s", url_render(&url,"user","*",0,0));
674	}
675	blob_append_sql(&sql, " AND euser=%Q", g.zLogin);
676	}
677	pg = atoi(PD("pg","0"));
678	blob_append_sql(&sql, " ORDER BY edate DESC limit %d offset %d", N+1, pg*N);
679	db_multi_exec("%s", blob_sql_text(&sql));
680	got = db_int(0, "SELECT count(*) FROM tmbox");
681	db_prepare(&q, "SELECT * FROM tmbox LIMIT %d", N);
682	blob_reset(&sql);
683	@ <form action="%R/webmail" method="POST">
684	@ <table border="0" width="100%%">
685	@ <tr><td align="left">
686	if( d==2 ){
687	@ <input type="submit" name="read" value="Undelete">
688	@ <input type="submit" name="purge" value="Delete Permanently">
689	}else{
690	@ <input type="submit" name="trash", value="Delete">
691	if( d!=1 ){
692	@ <input type="submit" name="unread" value="Mark as unread">
693	}
694	@ <input type="submit" name="read" value="Mark as read">
695	}
696	@ <a href="%s(url_render(&url,0,0,0,0))">refresh</a>
697	@ </td><td align="right">
698	if( pg>0 ){
699	sqlite3_snprintf(sizeof(zPPg), zPPg, "%d", pg-1);
700	@ <a href="%s(url_render(&url,"pg",zPPg,0,0))">< Newer</a>
701	}
702	if( got>50 ){
703	sqlite3_snprintf(sizeof(zNPg),zNPg,"%d",pg+1);
704	@ <a href="%s(url_render(&url,"pg",zNPg,0,0))">Older ></a></td>
705	}
706	@ </table>
707	@ <table>
708	while( db_step(&q)==SQLITE_ROW ){
709	const char *zId = db_column_text(&q,0);
710	const char *zFrom = db_column_text(&q, 1);
711	const char *zDate = db_column_text(&q, 2);
712	const char *zSubject = db_column_text(&q, 4);
713	@ <tr>
714	@ <td><input type="checkbox" name="e%s(zId)"></td>
715	@ <td>%h(zFrom)</td>
716	@ <td><a href="%s(url_render(&url,"id",zId,0,0))">%h(zSubject)</a> \
717	@ %s(zDate)</td>
718	if( showAll ){
719	const char *zTo = db_column_text(&q,5);
720	@ <td><a href="%s(url_render(&url,"user",zTo,0,0))">%h(zTo)</a></td>
721	}
722	@ </tr>


723	}
724	db_finalize(&q);
725	@ </table>
726	@ </form>
727	style_footer();
728	db_end_transaction(0);
729	}
730
731	/*
732	** WEBPAGE: test-emailblob
733	**
734	** This page, accessible only to administrators, allows easy viewing of
735	** the emailblob table - the table that contains the text of email messages
736	** both inbound and outbound, and transcripts of SMTP sessions.
737	**
738	** id=N Show the text of emailblob with emailid==N
739	**
740	*/
741	void webmail_emailblob_page(void){
742	int id = atoi(PD("id","0"));
743	Stmt q;
744	login_check_credentials();
745	if( !g.perm.Setup ){
746	login_needed(0);
747	return;
748	}
749	add_content_sql_commands(g.db);
750	style_header("emailblob table");
751	if( id>0 ){
752	style_submenu_element("Index", "%R/test-emailblob");
753	@ <ul>
754	db_prepare(&q, "SELECT emailid FROM emailblob WHERE ets=%d", id);
755	while( db_step(&q)==SQLITE_ROW ){
756	int id = db_column_int(&q, 0);
757	@ <li> <a href="%R/test-emailblob?id=%d(id)">emailblob entry %d(id)</a>
758	}
759	db_finalize(&q);
760	db_prepare(&q, "SELECT euser, estate FROM emailbox WHERE emsgid=%d", id);
761	while( db_step(&q)==SQLITE_ROW ){
762	const char *zUser = db_column_text(&q, 0);
763	int e = db_column_int(&q, 1);
764	@ <li> emailbox for %h(zUser) state %d(e)
765	}
766	db_finalize(&q);
767	db_prepare(&q, "SELECT efrom, eto FROM emailoutq WHERE emsgid=%d", id);
768	while( db_step(&q)==SQLITE_ROW ){
769	const char *zFrom = db_column_text(&q, 0);
770	const char *zTo = db_column_text(&q, 1);
771	@ <li> emailoutq message body from %h(zFrom) to %h(zTo)
772	}
773	db_finalize(&q);
774	db_prepare(&q, "SELECT efrom, eto FROM emailoutq WHERE ets=%d", id);
775	while( db_step(&q)==SQLITE_ROW ){
776	const char *zFrom = db_column_text(&q, 0);
777	const char *zTo = db_column_text(&q, 1);
778	@ <li> emailoutq transcript from %h(zFrom) to %h(zTo)
779	}
780	db_finalize(&q);
781	@ </ul>
782	@ <hr>
783	db_prepare(&q, "SELECT decompress(etxt) FROM emailblob WHERE emailid=%d",
784	id);
785	while( db_step(&q)==SQLITE_ROW ){
786	const char *zContent = db_column_text(&q, 0);
787	@ <pre>%h(zContent)</pre>
788	}
789	db_finalize(&q);
790	}else{
791	db_prepare(&q,
792	"SELECT emailid, enref, ets, datetime(etime,'unixepoch')"
793	" FROM emailblob ORDER BY etime DESC, emailid DESC");
794	@ <table border="1" cellpadding="5" cellspacing="0">
795	@ <tr><th> emailid <th> enref <th> ets <th> etime</tr>
796	while( db_step(&q)==SQLITE_ROW ){
797	int id = db_column_int(&q, 0);
798	int nref = db_column_int(&q, 1);
799	int ets = db_column_int(&q, 2);
800	const char *zDate = db_column_text(&q, 3);
801	@ <tr>
802	@ <td><a href="%R/test-emailblob?id=%d(id)">%d(id)</a>
803	@ <td>%d(nref)</td>
804	@ <td>%d(ets)</td>
805	@ <td>%h(zDate)</td>
806	@ </tr>
807	}
808	@ </table>
809	db_finalize(&q);
810	}
811	style_footer();
812	}
813

Fossil SCM

Keyboard Shortcuts