Fossil

Check-in [34b95026]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Store the uncompressed artifact size as an integer field in the emailblob table.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 34b9502698d25a52e9cfee5962f6d4e1b4f5c01edd68b662938da15299dbdd08
User & Date: drh 2018-07-17 20:59:29.695
Context
2018-07-17
23:50
Initial implementation of the "fossil pop3d" command. ... (check-in: bf13815c user: drh tags: trunk)
20:59
Store the uncompressed artifact size as an integer field in the emailblob table. ... (check-in: 34b95026 user: drh tags: trunk)
19:45
The emailblob table holds content a little while before deleting it. Change the /test-emailblob page to /emailblob, though it is still only accessible by the administrator. ... (check-in: 15a53308 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/smtp.c.
639
640
641
642
643
644
645

646
647
648
649
650
651
652
@ -- bulk storage is in a separate table.  This table can store either
@ -- the body of email messages or transcripts of smtp sessions.
@ CREATE TABLE IF NOT EXISTS repository.emailblob(
@   emailid INTEGER PRIMARY KEY AUTOINCREMENT,  -- numeric idea for the entry
@   enref INT,                    -- Number of references to this blob
@   ets INT,                      -- Corresponding transcript, or NULL
@   etime INT,                    -- insertion time, secs since 1970

@   etxt TEXT                     -- content of this entry
@ );
@
@ -- One row for each mailbox entry.  All users emails are stored in
@ -- this same table.
@ CREATE TABLE IF NOT EXISTS repository.emailbox(
@   ebid INTEGER PRIMARY KEY,  -- Unique id for each mailbox entry







>







639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
@ -- bulk storage is in a separate table.  This table can store either
@ -- the body of email messages or transcripts of smtp sessions.
@ CREATE TABLE IF NOT EXISTS repository.emailblob(
@   emailid INTEGER PRIMARY KEY AUTOINCREMENT,  -- numeric idea for the entry
@   enref INT,                    -- Number of references to this blob
@   ets INT,                      -- Corresponding transcript, or NULL
@   etime INT,                    -- insertion time, secs since 1970
@   esz INT,                      -- uncompressed content size
@   etxt TEXT                     -- content of this entry
@ );
@
@ -- One row for each mailbox entry.  All users emails are stored in
@ -- this same table.
@ CREATE TABLE IF NOT EXISTS repository.emailbox(
@   ebid INTEGER PRIMARY KEY,  -- Unique id for each mailbox entry
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080

1081
1082
1083
1084
1085
1086
1087
1088
1089

1090
1091
1092

1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104

1105
1106
1107
1108
1109
1110
1111
   && blob_size(&p->msg)
   && (p->srvrFlags & SMTPSRV_DRYRUN)==0
  ){
    db_begin_write();
    if( p->idTranscript==0 ) smtp_server_schema(0);
    p->nRef = 0;
    db_prepare(&s,
      "INSERT INTO emailblob(ets,etime,etxt,enref)"
      " VALUES(:ets,now(),compress(:etxt),0)"
    );
    p->nEts++;
    if( !bFinish && p->idTranscript==0 ){
      db_bind_null(&s, ":ets");
      db_bind_null(&s, ":etxt");

      db_step(&s);
      db_reset(&s);
      p->idTranscript = db_last_insert_rowid();
    }else if( bFinish ){
      if( p->idTranscript ){
        db_multi_exec(
           "UPDATE emailblob SET etxt=compress(%Q), enref=%d"
           " WHERE emailid=%lld",
           blob_str(&p->transcript), p->nEts, p->idTranscript);

      }else{
        db_bind_null(&s, ":ets");
        db_bind_str(&s, ":etxt", &p->transcript);

        db_step(&s);
        db_reset(&s);
        p->idTranscript = db_last_insert_rowid();
        db_multi_exec(
          "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
          p->nEts, p->idTranscript);
      }
      /* smtp_server_send(p, "221-Transcript id %lld nref %d\r\n",
      **   p->idTranscript, p->nEts); */
    }
    db_bind_int64(&s, ":ets", p->idTranscript);
    db_bind_str(&s, ":etxt", &p->msg);

    db_step(&s);
    db_finalize(&s);
    p->idMsg = db_last_insert_rowid();

    /* make entries in emailbox and emailoutq */
    for(i=0; i<p->nTo; i++){
      int okRemote = p->aTo[i].okRemote;







|
|





>






|

|
>



>












>







1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
   && blob_size(&p->msg)
   && (p->srvrFlags & SMTPSRV_DRYRUN)==0
  ){
    db_begin_write();
    if( p->idTranscript==0 ) smtp_server_schema(0);
    p->nRef = 0;
    db_prepare(&s,
      "INSERT INTO emailblob(ets,etime,etxt,enref,esz)"
      " VALUES(:ets,now(),compress(:etxt),0,:esz)"
    );
    p->nEts++;
    if( !bFinish && p->idTranscript==0 ){
      db_bind_null(&s, ":ets");
      db_bind_null(&s, ":etxt");
      db_bind_null(&s, ":esz");
      db_step(&s);
      db_reset(&s);
      p->idTranscript = db_last_insert_rowid();
    }else if( bFinish ){
      if( p->idTranscript ){
        db_multi_exec(
           "UPDATE emailblob SET etxt=compress(%Q), enref=%d, esz=%d"
           " WHERE emailid=%lld",
           blob_str(&p->transcript), p->nEts, blob_size(&p->transcript),
           p->idTranscript);
      }else{
        db_bind_null(&s, ":ets");
        db_bind_str(&s, ":etxt", &p->transcript);
        db_bind_int(&s, ":esz", blob_size(&p->transcript));
        db_step(&s);
        db_reset(&s);
        p->idTranscript = db_last_insert_rowid();
        db_multi_exec(
          "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
          p->nEts, p->idTranscript);
      }
      /* smtp_server_send(p, "221-Transcript id %lld nref %d\r\n",
      **   p->idTranscript, p->nEts); */
    }
    db_bind_int64(&s, ":ets", p->idTranscript);
    db_bind_str(&s, ":etxt", &p->msg);
    db_bind_int(&s, ":esz", blob_size(&p->msg));
    db_step(&s);
    db_finalize(&s);
    p->idMsg = db_last_insert_rowid();

    /* make entries in emailbox and emailoutq */
    for(i=0; i<p->nTo; i++){
      int okRemote = p->aTo[i].okRemote;
Changes to src/webmail.c.
817
818
819
820
821
822
823
824
825
826
827
828

829
830
831
832
833
834

835
836
837
838
839
840
841
842
843
844

845
846
847
848
849
850
851
    while( db_step(&q)==SQLITE_ROW ){
      const char *zContent = db_column_text(&q, 0);
      @ <pre>%h(zContent)</pre>
    }
    db_finalize(&q);
  }else{
    db_prepare(&q,
       "SELECT emailid, enref, ets, datetime(etime,'unixepoch'),"
       " length(etxt)"
       " FROM emailblob ORDER BY etime DESC, emailid DESC");
    @ <table border="1" cellpadding="5" cellspacing="0">
    @ <tr><th> emailid <th> enref <th> ets <th> etime <th> size </tr>

    while( db_step(&q)==SQLITE_ROW ){
      int id = db_column_int(&q, 0);
      int nref = db_column_int(&q, 1);
      int ets = db_column_int(&q, 2);
      const char *zDate = db_column_text(&q, 3);
      int sz = db_column_int(&q,4);

      @ <tr>
      @  <td align="right"><a href="%R/emailblob?id=%d(id)">%d(id)</a>
      @  <td align="right">%d(nref)</td>
      if( ets>0 ){
        @  <td align="right">%d(ets)</td>
      }else{
        @  <td>&nbsp;</td>
      }
      @  <td>%h(zDate)</td>
      @  <td align="right">%,d(sz)</td>

      @ </tr>
    }
    @ </table>
    db_finalize(&q);
  }
  style_footer();
}







|



|
>






>










>







817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
    while( db_step(&q)==SQLITE_ROW ){
      const char *zContent = db_column_text(&q, 0);
      @ <pre>%h(zContent)</pre>
    }
    db_finalize(&q);
  }else{
    db_prepare(&q,
       "SELECT emailid, enref, ets, datetime(etime,'unixepoch'), esz,"
       " length(etxt)"
       " FROM emailblob ORDER BY etime DESC, emailid DESC");
    @ <table border="1" cellpadding="5" cellspacing="0">
    @ <tr><th> emailid <th> enref <th> ets <th> etime \
    @ <th> uncompressed <th> compressed </tr>
    while( db_step(&q)==SQLITE_ROW ){
      int id = db_column_int(&q, 0);
      int nref = db_column_int(&q, 1);
      int ets = db_column_int(&q, 2);
      const char *zDate = db_column_text(&q, 3);
      int sz = db_column_int(&q,4);
      int csz = db_column_int(&q,5);
      @ <tr>
      @  <td align="right"><a href="%R/emailblob?id=%d(id)">%d(id)</a>
      @  <td align="right">%d(nref)</td>
      if( ets>0 ){
        @  <td align="right">%d(ets)</td>
      }else{
        @  <td>&nbsp;</td>
      }
      @  <td>%h(zDate)</td>
      @  <td align="right">%,d(sz)</td>
      @  <td align="right">%,d(csz)</td>
      @ </tr>
    }
    @ </table>
    db_finalize(&q);
  }
  style_footer();
}