Fossil

@ -- 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

   && 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;

    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();
}