Fossil SCM

Store the uncompressed artifact size as an integer field in the emailblob table.

drh 2018-07-17 20:59 trunk
Commit 34b9502698d25a52e9cfee5962f6d4e1b4f5c01edd68b662938da15299dbdd08
Parent 15a533086928eb8…
2 files changed +9 -4 +5 -2
~ src/smtp.c ~ src/webmail.c
M src/smtp.c
+9 -4
--- src/smtp.c
+++ src/smtp.c
@@ -641,10 +641,11 @@
641641
 @ CREATE TABLE IF NOT EXISTS repository.emailblob(
642642
 @ emailid INTEGER PRIMARY KEY AUTOINCREMENT, -- numeric idea for the entry
643643
 @ enref INT, -- Number of references to this blob
644644
 @ ets INT, -- Corresponding transcript, or NULL
645645
 @ etime INT, -- insertion time, secs since 1970
646
+@ esz INT, -- uncompressed content size
646647
 @ etxt TEXT -- content of this entry
647648
 @ );
648649
 @
649650
 @ -- One row for each mailbox entry. All users emails are stored in
650651
 @ -- this same table.
@@ -1069,29 +1070,32 @@
10691070
 ){
10701071
 db_begin_write();
10711072
 if( p->idTranscript==0 ) smtp_server_schema(0);
10721073
 p->nRef = 0;
10731074
 db_prepare(&s,
1074
- "INSERT INTO emailblob(ets,etime,etxt,enref)"
1075
- " VALUES(:ets,now(),compress(:etxt),0)"
1075
+ "INSERT INTO emailblob(ets,etime,etxt,enref,esz)"
1076
+ " VALUES(:ets,now(),compress(:etxt),0,:esz)"
10761077
 );
10771078
 p->nEts++;
10781079
 if( !bFinish && p->idTranscript==0 ){
10791080
 db_bind_null(&s, ":ets");
10801081
 db_bind_null(&s, ":etxt");
1082
+ db_bind_null(&s, ":esz");
10811083
 db_step(&s);
10821084
 db_reset(&s);
10831085
 p->idTranscript = db_last_insert_rowid();
10841086
 }else if( bFinish ){
10851087
 if( p->idTranscript ){
10861088
 db_multi_exec(
1087
- "UPDATE emailblob SET etxt=compress(%Q), enref=%d"
1089
+ "UPDATE emailblob SET etxt=compress(%Q), enref=%d, esz=%d"
10881090
 " WHERE emailid=%lld",
1089
- blob_str(&p->transcript), p->nEts, p->idTranscript);
1091
+ blob_str(&p->transcript), p->nEts, blob_size(&p->transcript),
1092
+ p->idTranscript);
10901093
 }else{
10911094
 db_bind_null(&s, ":ets");
10921095
 db_bind_str(&s, ":etxt", &p->transcript);
1096
+ db_bind_int(&s, ":esz", blob_size(&p->transcript));
10931097
 db_step(&s);
10941098
 db_reset(&s);
10951099
 p->idTranscript = db_last_insert_rowid();
10961100
 db_multi_exec(
10971101
 "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
@@ -1100,10 +1104,11 @@
11001104
 /* smtp_server_send(p, "221-Transcript id %lld nref %d\r\n",
11011105
 ** p->idTranscript, p->nEts); */
11021106
 }
11031107
 db_bind_int64(&s, ":ets", p->idTranscript);
11041108
 db_bind_str(&s, ":etxt", &p->msg);
1109
+ db_bind_int(&s, ":esz", blob_size(&p->msg));
11051110
 db_step(&s);
11061111
 db_finalize(&s);
11071112
 p->idMsg = db_last_insert_rowid();
11081113
11091114
 /* make entries in emailbox and emailoutq */
11101115
--- src/smtp.c
+++ src/smtp.c
@@ -641,10 +641,11 @@
641 @ CREATE TABLE IF NOT EXISTS repository.emailblob(
642 @ emailid INTEGER PRIMARY KEY AUTOINCREMENT, -- 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.
@@ -1069,29 +1070,32 @@
1069 ){
1070 db_begin_write();
1071 if( p->idTranscript==0 ) smtp_server_schema(0);
1072 p->nRef = 0;
1073 db_prepare(&s,
1074 "INSERT INTO emailblob(ets,etime,etxt,enref)"
1075 " VALUES(:ets,now(),compress(:etxt),0)"
1076 );
1077 p->nEts++;
1078 if( !bFinish && p->idTranscript==0 ){
1079 db_bind_null(&s, ":ets");
1080 db_bind_null(&s, ":etxt");
 
1081 db_step(&s);
1082 db_reset(&s);
1083 p->idTranscript = db_last_insert_rowid();
1084 }else if( bFinish ){
1085 if( p->idTranscript ){
1086 db_multi_exec(
1087 "UPDATE emailblob SET etxt=compress(%Q), enref=%d"
1088 " WHERE emailid=%lld",
1089 blob_str(&p->transcript), p->nEts, p->idTranscript);
 
1090 }else{
1091 db_bind_null(&s, ":ets");
1092 db_bind_str(&s, ":etxt", &p->transcript);
 
1093 db_step(&s);
1094 db_reset(&s);
1095 p->idTranscript = db_last_insert_rowid();
1096 db_multi_exec(
1097 "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
@@ -1100,10 +1104,11 @@
1100 /* smtp_server_send(p, "221-Transcript id %lld nref %d\r\n",
1101 ** p->idTranscript, p->nEts); */
1102 }
1103 db_bind_int64(&s, ":ets", p->idTranscript);
1104 db_bind_str(&s, ":etxt", &p->msg);
 
1105 db_step(&s);
1106 db_finalize(&s);
1107 p->idMsg = db_last_insert_rowid();
1108
1109 /* make entries in emailbox and emailoutq */
1110
--- src/smtp.c
+++ src/smtp.c
@@ -641,10 +641,11 @@
641 @ CREATE TABLE IF NOT EXISTS repository.emailblob(
642 @ emailid INTEGER PRIMARY KEY AUTOINCREMENT, -- 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 @ esz INT, -- uncompressed content size
647 @ etxt TEXT -- content of this entry
648 @ );
649 @
650 @ -- One row for each mailbox entry. All users emails are stored in
651 @ -- this same table.
@@ -1069,29 +1070,32 @@
1070 ){
1071 db_begin_write();
1072 if( p->idTranscript==0 ) smtp_server_schema(0);
1073 p->nRef = 0;
1074 db_prepare(&s,
1075 "INSERT INTO emailblob(ets,etime,etxt,enref,esz)"
1076 " VALUES(:ets,now(),compress(:etxt),0,:esz)"
1077 );
1078 p->nEts++;
1079 if( !bFinish && p->idTranscript==0 ){
1080 db_bind_null(&s, ":ets");
1081 db_bind_null(&s, ":etxt");
1082 db_bind_null(&s, ":esz");
1083 db_step(&s);
1084 db_reset(&s);
1085 p->idTranscript = db_last_insert_rowid();
1086 }else if( bFinish ){
1087 if( p->idTranscript ){
1088 db_multi_exec(
1089 "UPDATE emailblob SET etxt=compress(%Q), enref=%d, esz=%d"
1090 " WHERE emailid=%lld",
1091 blob_str(&p->transcript), p->nEts, blob_size(&p->transcript),
1092 p->idTranscript);
1093 }else{
1094 db_bind_null(&s, ":ets");
1095 db_bind_str(&s, ":etxt", &p->transcript);
1096 db_bind_int(&s, ":esz", blob_size(&p->transcript));
1097 db_step(&s);
1098 db_reset(&s);
1099 p->idTranscript = db_last_insert_rowid();
1100 db_multi_exec(
1101 "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
@@ -1100,10 +1104,11 @@
1104 /* smtp_server_send(p, "221-Transcript id %lld nref %d\r\n",
1105 ** p->idTranscript, p->nEts); */
1106 }
1107 db_bind_int64(&s, ":ets", p->idTranscript);
1108 db_bind_str(&s, ":etxt", &p->msg);
1109 db_bind_int(&s, ":esz", blob_size(&p->msg));
1110 db_step(&s);
1111 db_finalize(&s);
1112 p->idMsg = db_last_insert_rowid();
1113
1114 /* make entries in emailbox and emailoutq */
1115
M src/webmail.c
+5 -2
--- src/webmail.c
+++ src/webmail.c
@@ -819,21 +819,23 @@
819819
 @ <pre>%h(zContent)</pre>
820820
 }
821821
 db_finalize(&q);
822822
 }else{
823823
 db_prepare(&q,
824
- "SELECT emailid, enref, ets, datetime(etime,'unixepoch'),"
824
+ "SELECT emailid, enref, ets, datetime(etime,'unixepoch'), esz,"
825825
 " length(etxt)"
826826
 " FROM emailblob ORDER BY etime DESC, emailid DESC");
827827
 @ <table border="1" cellpadding="5" cellspacing="0">
828
- @ <tr><th> emailid <th> enref <th> ets <th> etime <th> size </tr>
828
+ @ <tr><th> emailid <th> enref <th> ets <th> etime \
829
+ @ <th> uncompressed <th> compressed </tr>
829830
 while( db_step(&q)==SQLITE_ROW ){
830831
 int id = db_column_int(&q, 0);
831832
 int nref = db_column_int(&q, 1);
832833
 int ets = db_column_int(&q, 2);
833834
 const char *zDate = db_column_text(&q, 3);
834835
 int sz = db_column_int(&q,4);
836
+ int csz = db_column_int(&q,5);
835837
 @ <tr>
836838
 @ <td align="right"><a href="%R/emailblob?id=%d(id)">%d(id)</a>
837839
 @ <td align="right">%d(nref)</td>
838840
 if( ets>0 ){
839841
 @ <td align="right">%d(ets)</td>
@@ -840,12 +842,13 @@
840842
 }else{
841843
 @ <td>&nbsp;</td>
842844
 }
843845
 @ <td>%h(zDate)</td>
844846
 @ <td align="right">%,d(sz)</td>
847
+ @ <td align="right">%,d(csz)</td>
845848
 @ </tr>
846849
 }
847850
 @ </table>
848851
 db_finalize(&q);
849852
 }
850853
 style_footer();
851854
 }
852855
--- src/webmail.c
+++ src/webmail.c
@@ -819,21 +819,23 @@
819 @ <pre>%h(zContent)</pre>
820 }
821 db_finalize(&q);
822 }else{
823 db_prepare(&q,
824 "SELECT emailid, enref, ets, datetime(etime,'unixepoch'),"
825 " length(etxt)"
826 " FROM emailblob ORDER BY etime DESC, emailid DESC");
827 @ <table border="1" cellpadding="5" cellspacing="0">
828 @ <tr><th> emailid <th> enref <th> ets <th> etime <th> size </tr>
 
829 while( db_step(&q)==SQLITE_ROW ){
830 int id = db_column_int(&q, 0);
831 int nref = db_column_int(&q, 1);
832 int ets = db_column_int(&q, 2);
833 const char *zDate = db_column_text(&q, 3);
834 int sz = db_column_int(&q,4);
 
835 @ <tr>
836 @ <td align="right"><a href="%R/emailblob?id=%d(id)">%d(id)</a>
837 @ <td align="right">%d(nref)</td>
838 if( ets>0 ){
839 @ <td align="right">%d(ets)</td>
@@ -840,12 +842,13 @@
840 }else{
841 @ <td>&nbsp;</td>
842 }
843 @ <td>%h(zDate)</td>
844 @ <td align="right">%,d(sz)</td>
 
845 @ </tr>
846 }
847 @ </table>
848 db_finalize(&q);
849 }
850 style_footer();
851 }
852
--- src/webmail.c
+++ src/webmail.c
@@ -819,21 +819,23 @@
819 @ <pre>%h(zContent)</pre>
820 }
821 db_finalize(&q);
822 }else{
823 db_prepare(&q,
824 "SELECT emailid, enref, ets, datetime(etime,'unixepoch'), esz,"
825 " length(etxt)"
826 " FROM emailblob ORDER BY etime DESC, emailid DESC");
827 @ <table border="1" cellpadding="5" cellspacing="0">
828 @ <tr><th> emailid <th> enref <th> ets <th> etime \
829 @ <th> uncompressed <th> compressed </tr>
830 while( db_step(&q)==SQLITE_ROW ){
831 int id = db_column_int(&q, 0);
832 int nref = db_column_int(&q, 1);
833 int ets = db_column_int(&q, 2);
834 const char *zDate = db_column_text(&q, 3);
835 int sz = db_column_int(&q,4);
836 int csz = db_column_int(&q,5);
837 @ <tr>
838 @ <td align="right"><a href="%R/emailblob?id=%d(id)">%d(id)</a>
839 @ <td align="right">%d(nref)</td>
840 if( ets>0 ){
841 @ <td align="right">%d(ets)</td>
@@ -840,12 +842,13 @@
842 }else{
843 @ <td>&nbsp;</td>
844 }
845 @ <td>%h(zDate)</td>
846 @ <td align="right">%,d(sz)</td>
847 @ <td align="right">%,d(csz)</td>
848 @ </tr>
849 }
850 @ </table>
851 db_finalize(&q);
852 }
853 style_footer();
854 }
855

Keyboard Shortcuts

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