Fossil

    backofficeBlob = &log;
    blob_appendf(&log, "%s %s", db_text(0, "SELECT datetime('now')"), zName);
  }

  /* Here is where the actual work of the backoffice happens */
  nThis = alert_backoffice(0);
  if( nThis ){ backoffice_log("%d alerts", nThis); nTotal += nThis; }
  nThis = smtp_cleanup();
  if( nThis ){ backoffice_log("%d SMTPs", nThis); nTotal += nThis; }
  nThis = hook_backoffice();
  if( nThis ){ backoffice_log("%d hooks", nThis); nTotal += nThis; }

  /* Close the log */
  if( backofficeFILE ){
    if( nTotal || backofficeLogDetail ){
      if( nTotal==0 ) backoffice_log("no-op");

  update
  url
  user
  utf8
  util
  verify
  vfile
  webmail
  wiki
  wikiformat
  winfile
  winhttp
  xfer
  xfersetup
  zip

  smtp_client_quit(p);
  if( p->zErr ){
    fossil_fatal("ERROR: %s\n", p->zErr);
  }
  smtp_session_free(p);
  blob_reset(&body);
}

/*****************************************************************************
** Server implementation
*****************************************************************************/

/*
** Schema used by the email processing system.
*/
static const char zEmailSchema[] =
@ -- bulk storage is in this table.  This table can store either
@ -- the body of email messages or transcripts of an smtp session.
@ 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
@   euser TEXT,          -- User who received this email
@   edate INT,           -- Date received.  Seconds since 1970
@   efrom TEXT,          -- Who is the email from
@   emsgid INT,          -- Raw email text
@   estate INT,          -- 0: Unread, 1: read, 2: trash 3: sent
@   esubject TEXT,       -- Subject line for display
@   etags TEXT           -- zero or more tags
@ );
@
@ -- Information on how to deliver incoming email.
@ CREATE TABLE IF NOT EXISTS repository.emailroute(
@   eaddr TEXT PRIMARY KEY,  -- Email address
@   epolicy TEXT             -- How to handle email sent to this address
@ ) WITHOUT ROWID;
@
@ -- Outgoing email queue
@ CREATE TABLE IF NOT EXISTS repository.emailoutq(
@   edomain TEXT,            -- Destination domain.  (ex: "fossil-scm.org")
@   efrom TEXT,              -- Sender email address (envelope "from")
@   eto TEXT,                -- Recipient email address (envelope "to")
@   emsgid INT,              -- Message body in the emailblob table
@   ectime INT,              -- Time enqueued.  Seconds since 1970
@   emtime INT,              -- Time of last send attempt.  Sec since 1970
@   ensend INT,              -- Number of send attempts
@   ets INT                  -- Transcript of last failed attempt
@ );
@
@ -- Triggers to automatically keep the emailblob.enref field up to date
@ -- as entries in the emailblob, emailbox, and emailoutq tables are
@ -- deleted.
@ CREATE TRIGGER IF NOT EXISTS repository.emailblob_d1
@ AFTER DELETE ON emailblob BEGIN
@   UPDATE emailblob SET enref=enref-1 WHERE emailid=old.ets;
@ END;
@ CREATE TRIGGER IF NOT EXISTS repository.emailbox_d1
@ AFTER DELETE ON emailbox BEGIN
@   UPDATE emailblob SET enref=enref-1 WHERE emailid=old.emsgid;
@ END;
@ CREATE TRIGGER IF NOT EXISTS repository.emailoutq_d1
@ AFTER DELETE ON emailoutq BEGIN
@   UPDATE emailblob SET enref=enref-1 WHERE emailid IN (old.ets,old.emsgid);
@ END;
@
@ -- An index on the emailblob entries which are unreferenced.
@ CREATE INDEX IF NOT EXISTS repository.emailblob_nref ON emailblob(enref)
@ WHERE enref<=0;
;

/*
** Code used to delete the email tables.
*/
static const char zEmailDrop[] =
@ DROP TABLE IF EXISTS emailblob;
@ DROP TABLE IF EXISTS emailbox;
@ DROP TABLE IF EXISTS emailroute;
@ DROP TABLE IF EXISTS emailqueue;
;

#if INTERFACE
/*
** Mailbox message states
*/
#define MSG_UNREAD    0
#define MSG_READ      1
#define MSG_TRASH     2
#endif /* INTERFACE */


/*
** Populate the schema of a database.
**
**   eForce==0          Fast
**   eForce==1          Run CREATE TABLE statements every time
**   eForce==2          DROP then rerun CREATE TABLE
*/
void smtp_server_schema(int eForce){
  if( eForce==2 ){
    db_multi_exec(zEmailDrop/*works-like:""*/);
  }
  if( eForce==1 || !db_table_exists("repository","emailblob") ){
    db_multi_exec(zEmailSchema/*works-like:""*/);
  }
}

/*
** WEBPAGE: setup_smtp
**
** Administrative page for configuring and controlling inbound email and
** output email queuing.  This page is available to administrators
** only via the /Admin/EmailServer menu.
*/
void setup_smtp(void){
  Stmt q;
  login_check_credentials();
  if( !g.perm.Setup ){
    login_needed(0);
    return;
  }
  db_begin_transaction();
  style_set_current_feature("smtp");
  style_header("Email Server Setup");
  if( db_table_exists("repository","emailroute") ){
    style_submenu_element("emailblob table", "%R/emailblob");
    style_submenu_element("emailoutq table", "%R/emailoutq");
    db_prepare(&q, "SELECT eaddr, epolicy FROM emailroute ORDER BY 1");
  }else{
    db_prepare(&q, "SELECT null, null WHERE false");
  }
  @ <h1>Email Routing Table</h1>
  @ <table class="emailroutetab" cellpadding="5" border="1" cellspacing="0">
  @ <thead>
  @ <tr>
  @   <th>Email Address
  @   <th>Routing
  @   <th>
  @ </tr>
  @ </thead><tbody>
  while( db_step(&q)==SQLITE_ROW ){
    const char *zEAddr = db_column_text(&q, 0);
    const char *zEPolicy = db_column_text(&q, 1);
    @ <tr>
    @  <td valign="top">%h(zEAddr)</td>
    @  <td valign="top"><span style="white-space:pre;">%h(zEPolicy)</span></td>
    @  <td valign="top"><form method="POST" action="%R/setup_smtp_route">
    @    <input type="hidden" name="oaddr" value="%h(zEAddr)">
    @    <input type="submit" value="Edit">
    @    </form>
  }
  db_finalize(&q);
  @ <tr>
  @   <td colspan="3">
  @   <form method="POST" action="%R/setup_smtp_route">
  @   <input type="submit" value="New">
  @    &larr; Add a new email address
  @   </form>
  @ </table>
  style_finish_page();
  db_end_transaction(0);
}

/*
** WEBPAGE: setup_smtp_route
**
** Edit a single entry in the emailroute table.
** Query parameters:
**
**    eaddr=ADDR          ADDR is the email address as edited.
**
**    oaddr=ADDR          The original email address prior to editing.
**                        Omit to add a new address.
**
**    epolicy=TXT         The routing policy.
*/
void setup_smtp_route(void){
  char *zEAddr = PT("eaddr");         /* new email address */
  char *zEPolicy = PT("epolicy");     /* new routing policy */
  char *zOAddr = PT("oaddr");         /* original email address */
  char *zErr = 0;
  int iErr = 0;
  login_check_credentials();
  if( !g.perm.Setup ){
    login_needed(0);
    return;
  }
  style_set_current_feature("smtp");
  style_header("Email Route Editor");

  if( P("edit") && cgi_csrf_safe(1) && zEAddr!=0 && zEPolicy!=0 ){
    smtp_server_schema(0);
    if( (zOAddr==0 || fossil_strcmp(zEAddr,zOAddr)!=0) ){
      /* New or changed email address */
      if( db_exists("SELECT 1 FROM emailroute WHERE eaddr=%Q",zEAddr) ){
        iErr = 1;
        zErr = mprintf("email address \"%h(zEAddr)\" already exists",zEAddr);
        goto smtp_route_edit;
      }
      if( zEPolicy[0]==0 ){
        iErr = 2;
        zErr = mprintf("empty route");
        goto smtp_route_edit;
      }
    }
    /* If the email address has changed, or if the new policy is blank,
    ** delete the old address and route information
    */
    db_begin_transaction();
    if( (zOAddr && fossil_strcmp(zEAddr,zOAddr)!=0) || zEPolicy[0]==0 ){
      db_multi_exec("DELETE FROM emailroute WHERE eaddr=%Q", zOAddr);
    }
    if( zEPolicy[0] ){
      /* Insert the new address and route */
      db_multi_exec(
        "REPLACE INTO emailroute(eaddr,epolicy) VALUES(%Q,%Q)",
        zEAddr, zEPolicy
      );
    }
    db_end_transaction(0);
    cgi_redirectf("%R/setup_smtp");
  }
  if( P("cancel")!=0 ){
    cgi_redirectf("%R/setup_smtp");
  }

smtp_route_edit:
  if( zEAddr==0 ) zEAddr = zOAddr;
  if( zEPolicy==0 && db_table_exists("repository","emailroute") ){
    zEPolicy = db_text(0, "SELECT epolicy FROM emailroute WHERE eaddr=%Q",
                      zEAddr);
  }
  if( zEPolicy==0 ) zEPolicy = "";
  @ <form method="POST" action="%R/setup_smtp_route">
  if( zOAddr ){
    @ <input type="hidden" name="oaddr" value="%h(zOAddr)">
  }
  @ <table class="label-value">
  @ <tr>
  @   <th>Email Address:</th>
  @   <td><input type="text" size=30 name="eaddr" value="%h(zEAddr)">
  if( iErr==1 ){
    @ <td><span class="generalError">&larr; %z(zErr)</span>
  }
  @ </tr>
  if( zOAddr && fossil_strcmp(zOAddr,zEAddr)!=0 ){
    @ <tr>
    @   <th>Original Address:</th>
    @   <td>%h(zOAddr)
    @ </tr>
  }
  @ <tr>
  @   <th>Routing:</th>
  @   <td><textarea name="epolicy" rows="3" cols="40">%h(zEPolicy)</textarea>
  if( iErr==2 ){
    @ <td valign="top"><span class="generalError">&larr; %z(zErr)</span>
  }
  @ </tr>
  @ <tr>
  @   <td>&nbsp;
  @   <td><input type="submit" name="edit" value="Apply">
  @       <input type="submit" name="cancel" value="Cancel">
  @ </tr>
  @ </table>
  @ <hr>
  @ <h1>Instructions</h1>
  @
  @ <p>The "Routing" field consists of zero or more lines where each
  @ line is an "action" followed by an "argument".  Available actions:
  @ <ul>
  @ <li><p><b>forward</b> <i>email-address</i>
  @ <p>Forward the message to <i>email-address</i>.
  @ <li><p><b>mbox</b> <i>login-name</i>
  @ <p>Store the message in the local mailbox for the user
  @ with USER.LOGIN=<i>login-name</i>.
  @ </ul>
  @
  @ <p>To delete a route &rarr; erase all text from the "Routing" field then
  @ press the "Apply" button.
  style_finish_page();
}

#if LOCAL_INTERFACE
/*
** State information for the server
*/
struct SmtpServer {
  sqlite3_int64 idTranscript; /* Transcript ID number */
  sqlite3_int64 idMsg;        /* Message ID number */
  const char *zIpAddr;        /* Remote IP address */
  char *zEhlo;                /* Client domain on the EHLO line */
  char *zFrom;                /* MAIL FROM: argument */
  int nTo;                    /* Number of RCPT TO: lines seen */
  struct SmtpTo {
    char *z;                    /* Address in each RCPT TO line */
    int okRemote;               /* zTo can be in another domain */
  } *aTo;
  u32 srvrFlags;              /* Control flags */
  int nEts;                   /* Number of references to the transcript */
  int nRef;                   /* Number of references to idMsg */
  Blob msg;                   /* Content following DATA */
  Blob transcript;            /* Session transcript */
};

#define SMTPSRV_CLEAR_MSG    1   /* smtp_server_clear() last message only */
#define SMTPSRV_CLEAR_ALL    2   /* smtp_server_clear() everything */
#define SMTPSRV_LOG       0x001  /* Record a transcript of the interaction */
#define SMTPSRV_STDERR    0x002  /* Transcription written to stderr */
#define SMTPSRV_DRYRUN    0x004  /* Do not record anything in database */

#endif /* LOCAL_INTERFACE */

/*
** Clear the SmtpServer object.  Deallocate resources.
** How much to clear depends on eHowMuch
*/
static void smtp_server_clear(SmtpServer *p, int eHowMuch){
  int i;
  if( eHowMuch>=SMTPSRV_CLEAR_MSG ){
    fossil_free(p->zFrom);
    p->zFrom = 0;
    for(i=0; i<p->nTo; i++) fossil_free(p->aTo[i].z);
    fossil_free(p->aTo);
    p->aTo = 0;
    p->nTo = 0;
    blob_reset(&p->msg);
    p->idMsg = 0;
  }
  if( eHowMuch>=SMTPSRV_CLEAR_ALL ){
    blob_reset(&p->transcript);
    p->idTranscript = 0;
    fossil_free(p->zEhlo);
    p->zEhlo = 0;
  }
}

/*
** Turn raw memory into an SmtpServer object.
*/
static void smtp_server_init(SmtpServer *p){
  memset(p, 0, sizeof(*p));
  blob_init(&p->msg, 0, 0);
  blob_init(&p->transcript, 0, 0);
}

/*
** Append a new TO entry to the SmtpServer object.  Do not do the
** append if the same entry is already on the list.
**
** The zAddr argument is obtained from fossil_malloc().  This
** routine assumes ownership of the allocation.
*/
static void smtp_append_to(SmtpServer *p, char *zAddr, int okRemote){
  int i;
  for(i=0; zAddr[i]; i++){ zAddr[i] = fossil_tolower(zAddr[i]); }
  for(i=0; i<p->nTo; i++){
    if( strcmp(zAddr, p->aTo[i].z)==0 ){
      fossil_free(zAddr);
      if( p->aTo[i].okRemote==0 ) p->aTo[i].okRemote = okRemote;
      return;
    }
  }
  p->aTo = fossil_realloc(p->aTo, (p->nTo+1)*sizeof(p->aTo[0]));
  p->aTo[p->nTo].z = zAddr;
  p->aTo[p->nTo].okRemote = okRemote;
  p->nTo++;
}

/*
** Send a single line of output from the server to the client.
*/
static void smtp_server_send(SmtpServer *p, const char *zFormat, ...){
  Blob b = empty_blob;
  va_list ap;
  char *z;
  int n;
  va_start(ap, zFormat);
  blob_vappendf(&b, zFormat, ap);
  va_end(ap);
  z = blob_buffer(&b);
  n = blob_size(&b);
  assert( n>=2 );
  assert( z[n-1]=='\n' );
  assert( z[n-2]=='\r' );
  if( p->srvrFlags & SMTPSRV_LOG ){
    blob_appendf(&p->transcript, "S: %.*s\n", n-2, z);
  }
  if( p->srvrFlags & SMTPSRV_STDERR ){
    fprintf(stderr, "S: %.*s\n", n-2, z);
  }
  fwrite(z, n, 1, stdout);
  fflush(stdout);
  blob_reset(&b);
}

/*
** Read a single line from the client.
*/
static int smtp_server_gets(SmtpServer *p, char *aBuf, int nBuf){
  int rc = fgets(aBuf, nBuf, stdin)!=0;
  if( rc ){
    if( (p->srvrFlags & SMTPSRV_LOG)!=0 ){
      blob_appendf(&p->transcript, "C: %s", aBuf);
    }
    if( (p->srvrFlags & SMTPSRV_STDERR)!=0 ){
      fprintf(stderr, "C: %s", aBuf);
    }
  }
  return rc;
}

/*
** RFC-5321 requires certain content be prepended to an email header
** as that email is received.
*/
static void smtp_server_prepend_header_lines(SmtpServer *p){
  blob_appendf(&p->msg, "Received: from %s by Fossil-smtp\r\n", p->zIpAddr);
}

/*
** Capture the incoming email data into the p->msg blob.  Dequote
** lines of "..\r\n" into just ".\r\n".
*/
static void smtp_server_capture_data(SmtpServer *p, char *z, int n){
  int nLine = 0;
  while( fgets(z, n, stdin) ){
    if( strncmp(z, ".\r\n", 3)==0 || strncmp(z, ".\n",2)==0 ) break;
    nLine++;
    if( strncmp(z, "..\r\n", 4)==0 || strncmp(z, "..\n",3)==0 ){
      memmove(z, z+1, 4);
    }
    blob_append(&p->msg, z, -1);
  }
  if( p->srvrFlags & SMTPSRV_LOG ){
    blob_appendf(&p->transcript, "C: # %d lines, %d bytes of content\n",
          nLine, blob_size(&p->msg));
  }
  if( p->srvrFlags & SMTPSRV_STDERR ){
    fprintf(stderr, "C: # %d lines, %d bytes of content\n",
          nLine, blob_size(&p->msg));
  }
}

/*
** Send an email to a single email addess that is registered with
** this system, according to the instructions in emailroute.  If
** zAddr is not in the emailroute table, then this routine is a
** no-op.  Or if zAddr has already been processed, then this
** routine is a no-op.
*/
static void smtp_server_send_one_user(
  SmtpServer *p,         /* The current inbound email */
  const char *zAddr,     /* Who to forward this to */
  int okRemote           /* True if ok to foward to another domain */
){
  char *zPolicy;
  Blob policy, line, token, tail;

  zPolicy = db_text(0,
    "SELECT epolicy FROM emailroute WHERE eaddr=%Q", zAddr);
  if( zPolicy==0 ){
    if( okRemote ){
      int i;
      for(i=0; zAddr[i] && zAddr[i]!='@'; i++){}
      if( zAddr[i]=='@' && zAddr[i+1]!=0 ){
        db_multi_exec(
          "INSERT INTO emailoutq(edomain,efrom,eto,emsgid,ectime,"
                                "emtime,ensend)"
          "VALUES(%Q,%Q,%Q,%lld,now(),0,0)",
          zAddr+i+1, p->zFrom, zAddr, p->idMsg
        );
        p->nRef++;
      }
    }
    return;
  }
  blob_init(&policy, zPolicy, -1);
  while( blob_line(&policy, &line) ){
    blob_trim(&line);
    blob_token(&line, &token);
    blob_tail(&line, &tail);
    if( blob_size(&tail)==0 ) continue;
    if( blob_eq_str(&token, "mbox", 4) ){
      Blob subj;
      email_header_value(&p->msg, "subject", &subj);
      db_multi_exec(
        "INSERT INTO emailbox(euser,edate,efrom,emsgid,estate,esubject)"
        " VALUES(%Q,now(),%Q,%lld,0,%Q)",
          blob_str(&tail), p->zFrom, p->idMsg,
          blob_str(&subj)
      );
      blob_reset(&subj);
      p->nRef++;
    }
    if( blob_eq_str(&token, "forward", 7) ){
      smtp_append_to(p, fossil_strdup(blob_str(&tail)), 1);
    }
    blob_reset(&tail);
  }
}

/*
** The SmtpServer object contains a complete incoming email.
** Add this email to the database.
*/
static void smtp_server_route_incoming(SmtpServer *p, int bFinish){
  Stmt s;
  int i;
  int nEtsStart = p->nEts;
  if( p->zFrom
   && p->nTo
   && 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;
      p->aTo[i].okRemote = 1;
      smtp_server_send_one_user(p, p->aTo[i].z, okRemote);
    }

    /* Fix up the emailblob.enref field of the email message body */
    if( p->nRef ){
      db_multi_exec(
        "UPDATE emailblob SET enref=%d WHERE emailid=%lld",
        p->nRef, p->idMsg
      );
    }else{
      db_multi_exec(
        "DELETE FROM emailblob WHERE emailid=%lld", p->idMsg
      );
      p->nEts = nEtsStart;
    }

    /* Clean out legacy entries */
    if( bFinish ){
      db_multi_exec("DELETE FROM emailblob WHERE enref<=0");
    }

    /* Finish the transaction after all changes are implemented */
    db_commit_transaction();
  }
  smtp_server_clear(p, SMTPSRV_CLEAR_MSG);
}

/*
** Remove stale content from the emailblob table.
*/
int smtp_cleanup(void){
  int nAction = 0;
  if( db_table_exists("repository","emailblob") ){
    db_begin_transaction();
    db_multi_exec(
      "UPDATE emailblob SET ets=NULL WHERE enref<=0;"
      "DELETE FROM emailblob WHERE enref<=0;"
    );
    nAction = db_changes();
    db_end_transaction(0);
  }
  return nAction;
}

/*
** COMMAND: test-emailblob-refcheck
**
** Usage: %fossil test-emailblob-refcheck [--repair] [--full] [--clean]
**
** Verify that the emailblob.enref field is correct.  Report any errors.
** Use the --repair command to fix up the enref field.  The --full option
** gives a full report showing the enref value on all entries in the
** emailblob table.  If the --clean flags is used together with --repair,
** then emailblob table entires with enref==0 are removed.
*/
void test_refcheck_emailblob(void){
  int doRepair;
  int fullReport;
  int doClean;
  Blob sql;
  Stmt q;
  int nErr = 0;
  db_find_and_open_repository(0, 0);
  fullReport = find_option("full",0,0)!=0;
  doRepair = find_option("repair",0,0)!=0;
  doClean = find_option("clean",0,0)!=0;
  verify_all_options();
  if( !db_table_exists("repository","emailblob") ){
    fossil_print("emailblob table is not configured - nothing to check\n");
    return;
  }
  db_multi_exec(
    "CREATE TEMP TABLE refcnt(id INTEGER PRIMARY KEY, n);"
    "INSERT INTO refcnt SELECT ets, count(*) FROM ("
    "  SELECT ets FROM emailblob"
    "  UNION ALL"
    "  SELECT emsgid FROM emailbox"
    "  UNION ALL"
    "  SELECT emsgid FROM emailoutq"
    ") WHERE ets IS NOT NULL GROUP BY 1;"
    "INSERT OR IGNORE INTO refcnt(id,n) SELECT emailid, 0 FROM emailblob;"
  );
  if( doRepair ){
    db_multi_exec(
      "UPDATE emailblob SET enref=(SELECT n FROM refcnt WHERE id=emailid)"
    );
    if( doClean ){
      smtp_cleanup();
    }
  }
  blob_init(&sql, 0, 0);
  blob_append_sql(&sql,
    "SELECT a.emailid, a.enref, b.n"
    "  FROM emailblob AS a JOIN refcnt AS b ON a.emailid=b.id"
  );
  if( !fullReport ){
    blob_append_sql(&sql, " WHERE a.enref!=b.n");
  }
  db_prepare_blob(&q, &sql);
  blob_reset(&sql);
  while( db_step(&q)==SQLITE_ROW ){
    sqlite3_int64 id = db_column_int64(&q,0);
    int n1 = db_column_int(&q, 1);
    int n2 = db_column_int(&q, 2);
    if( n1!=n2 ) nErr++;
    fossil_print("%12lld %4d %4d%s\n", id, n1, n2, n1!=n2 ? " ERROR" : "");
  }
  db_finalize(&q);
  if( nErr ){
    fossil_print("Number of incorrect emailblob.enref values: %d\n",nErr);
  }
}


/*
** COMMAND: smtpd*
**
** Usage: %fossil smtpd [OPTIONS] REPOSITORY
**
** Begin a SMTP conversation with a client using stdin/stdout.  The
** received email is stored in REPOSITORY.
**
** Options:
**
**      --dryrun          Do not record any emails in the database
**
**      --trace           Print a transcript of the conversation on stderr
**                        for debugging and analysis
**
**      --ipaddr ADDR     The SMTP connection originates at ADDR.  Or if ADDR
**                        is the name of an environment variable, the address
**                        is taken from that environment variable.
*/
void smtp_server(void){
  char *zDbName;
  const char *zDomain;
  SmtpServer x;
  char z[5000];

  smtp_server_init(&x);
  zDomain = find_option("domain",0,1);
  if( zDomain==0 ) zDomain = "";
  x.srvrFlags = SMTPSRV_LOG;
  if( find_option("trace",0,0)!=0 ) x.srvrFlags |= SMTPSRV_STDERR;
  if( find_option("dryrun",0,0)!=0 ) x.srvrFlags |= SMTPSRV_DRYRUN;
  x.zIpAddr = find_option("ipaddr",0,1);
  if( x.zIpAddr ){
    const char *zNew = fossil_getenv(x.zIpAddr);
    if( zNew && zNew[0] ) x.zIpAddr = zNew;
  }
  if( x.zIpAddr==0 ){
    x.zIpAddr = cgi_remote_ip(0);
    if( x.zIpAddr==0 ) x.zIpAddr = "?.?.?.?";
  }
  verify_all_options();
  if( g.argc!=3 ) usage("DBNAME");
  zDbName = g.argv[2];
  zDbName = enter_chroot_jail(zDbName, 0);
  db_open_repository(zDbName);
  add_content_sql_commands(g.db);
  smtp_server_send(&x, "220 %s ESMTP https://fossil-scm.org/ %s\r\n",
                   zDomain, MANIFEST_VERSION);
  while( smtp_server_gets(&x, z, sizeof(z)) ){
    if( strncmp(z, "EHLO", 4)==0  && fossil_isspace(z[4]) ){
      smtp_server_send(&x, "250 ok\r\n");
    }else
    if( strncmp(z, "HELO", 4)==0  && fossil_isspace(z[4]) ){
      smtp_server_send(&x, "250 ok\r\n");
    }else
    if( strncmp(z, "MAIL FROM:<", 11)==0 ){
      smtp_server_route_incoming(&x, 0);
      smtp_server_clear(&x, SMTPSRV_CLEAR_MSG);
      x.zFrom = email_copy_addr(z+11,'>');
      if( x.zFrom==0 ){
        smtp_server_send(&x, "500 unacceptable email address\r\n");
      }else{
        smtp_server_send(&x, "250 ok\r\n");
      }
    }else
    if( strncmp(z, "RCPT TO:<", 9)==0 ){
      char *zAddr;
      if( x.zFrom==0 ){
        smtp_server_send(&x, "500 missing MAIL FROM\r\n");
        continue;
      }
      zAddr = email_copy_addr(z+9, '>');
      if( zAddr==0 ){
        smtp_server_send(&x, "505 no such user\r\n");
        continue;
      }
      smtp_append_to(&x, zAddr, 0);
      if( x.nTo>=100 ){
        smtp_server_send(&x, "452 too many recipients\r\n");
        continue;
      }
      smtp_server_send(&x, "250 ok\r\n");
    }else
    if( strncmp(z, "DATA", 4)==0 && fossil_isspace(z[4]) ){
      if( x.zFrom==0 || x.nTo==0 ){
        smtp_server_send(&x, "500 missing RCPT TO\r\n");
        continue;
      }
      smtp_server_send(&x, "354 ready\r\n");
      smtp_server_prepend_header_lines(&x);
      smtp_server_capture_data(&x, z, sizeof(z));
      smtp_server_send(&x, "250 ok\r\n");
    }else
    if( strncmp(z, "QUIT", 4)==0 && fossil_isspace(z[4]) ){
      smtp_server_route_incoming(&x, 1);
      smtp_server_send(&x, "221 closing connection\r\n");
      break;
    }else
    {
      smtp_server_send(&x, "500 unknown command\r\n");
    }
  }
  smtp_server_clear(&x, SMTPSRV_CLEAR_ALL);
}

/*
** Zero-terminate the argument.  Return a pointer the start of the
** next argument, or to NULL if there are no more arguments.
*/
static char *pop3d_arg(char *z){
  if( z[0]==0 || fossil_isspace(z[0]) ){
    return 0;
  }
  z++;
  while( z[0] && !fossil_isspace(z[0]) ){ z++; }
  if( z[0]==0 ) return 0;
  z[0] = 0;
  z++;
  if( z[0]==0 || fossil_isspace(z[0]) ) return 0;
  return z;
}

/*
** Write formatted output back to the pop3 client, and also to the
** log file, if there is a log file.
*/
static void pop3_print(FILE *pLog, const char *zFormat, ...){
  va_list ap;
  char zLine[500];
  va_start(ap, zFormat);
  sqlite3_vsnprintf(sizeof(zLine),zLine,zFormat,ap);
  va_end(ap);
  printf("%s\r\n", zLine);
  fflush(stdout);
  if( pLog ) fprintf(pLog, "S: %s\n", zLine);
}

/*
** Try to log in for zUser and zPass.
**
** zUser can either point to a Fossil user name or to an email address
** found in the user table's info field, in angle brackets.
*/
static int pop3_login(const char *zUser, char *zPass){
  return login_search_uid(&zUser, zPass) != 0;
}

/*
** COMMAND: pop3d*
**
** Usage: %fossil pop3d [OPTIONS] REPOSITORY
**
** Begin a POP3 conversation with a client using stdin/stdout using
** the mailboxes stored in REPOSITORY.
**
** If launched as root, the process first enters a chroot jail using
** the directory of REPOSITORY as root, then drops all privileges and
** assumes the user and group of REPOSITORY before reading any content
** off of the wire.
**
**   --logdir  DIR        Each pop3d session creates a new logfile
**                        in the directory DIR and records a transcript
**                        of the session there.  The logfile is opened
**                        before entering the chroot jail.
*/
void pop3d_command(void){
  char *zDbName;
  char *zA1, *zA2, *zCmd, *z;
  int inAuth = 1;
  int i;
  FILE *pLog = 0;
  const char *zDir;
  Stmt q;
  char zIn[1000];
  char zUser[100];
  zDir = find_option("logdir",0,1);
  if( zDir ){
    char *zFile = file_time_tempname(zDir, ".txt");
    pLog = fossil_fopen(zFile, "w");
    fossil_free(zFile);
  }
  verify_all_options();
  if( g.argc!=3 ) usage("DBNAME");
  zDbName = g.argv[2];
  zDbName = enter_chroot_jail(zDbName, 0);
  db_open_repository(zDbName);
  add_content_sql_commands(g.db);
  pop3_print(pLog, "+OK POP3 server ready");
  while( fgets(zIn, sizeof(zIn), stdin) ){
    if( pLog ) fprintf(pLog, "C: %s", zIn);
    zCmd = zIn;
    zA1 = pop3d_arg(zCmd);
    zA2 = zA1 ? pop3d_arg(zA1) : 0;
    for(i=0; zCmd[i]; i++){ zCmd[i] = fossil_tolower(zCmd[i]); }
    if( strcmp(zCmd,"quit")==0 ){
      if( !inAuth ){
        db_multi_exec(
          "UPDATE emailbox SET estate=2"
          " WHERE estate<2 AND ebid IN (SELECT ebid FROM pop3 WHERE isDel);"
        );
      }
      pop3_print(pLog, "+OK");
      break;
    }
    if( strcmp(zCmd,"capa")==0 ){
      static const char *const azCap[] = {
          "TOP", "USER", "UIDL",
      };
      int i;
      pop3_print(pLog, "+OK");
      for(i=0; i<sizeof(azCap)/sizeof(azCap[0]); i++){
        pop3_print(pLog, "%s", azCap[i]);
      }
      pop3_print(pLog, ".");
      continue;
    }
    if( inAuth ){
      if( strcmp(zCmd,"user")==0 ){
        if( zA1==0 || zA2!=0 ) goto cmd_error;
        sqlite3_snprintf(sizeof(zUser),zUser,"%s",zA1);
        goto cmd_ok;
      }
      if( strcmp(zCmd,"pass")==0 ){
        if( zA1==0 || zA2!=0 ) goto cmd_error;
        if( pop3_login(zUser,zA1)==0 ){
          goto cmd_error;
        }else{
          inAuth = 0;
          db_multi_exec(
            "CREATE TEMP TABLE pop3("
            "  id INTEGER PRIMARY KEY,"
            "  emailid INT,"
            "  ebid INT,"
            "  isDel INT,"
            "  esz INT"
            ");"
            "INSERT INTO pop3(id,emailid,ebid,isDel,esz)"
            "  SELECT NULL, emailid, ebid, 0, esz FROM emailblob, emailbox"
            "  WHERE emailid=emsgid AND euser=%Q AND estate<=1"
            "  ORDER BY edate;",
            zUser
          );
          goto cmd_ok;
        }
      }
      /* Fossil cannot process APOP since the users clear-text password is
      ** unknown. */
      goto cmd_error;
    }else{
      if( strcmp(zCmd,"stat")==0 ){
        db_prepare(&q, "SELECT count(*), sum(esz) FROM pop3 WHERE NOT isDel");
        if( db_step(&q)==SQLITE_ROW ){
          pop3_print(pLog, "+OK %d %d",
                     db_column_int(&q,0), db_column_int(&q,1));
        }else{
          pop3_print(pLog,"-ERR");
        }
        db_finalize(&q);
        continue;
      }
      if( strcmp(zCmd,"list")==0 ){
        if( zA1 ){
          db_prepare(&q, "SELECT id, esz FROM pop3"
                         " WHERE id=%d AND NOT isDel", atoi(zA1));
          if( db_step(&q)==SQLITE_ROW ){
            pop3_print(pLog, "+OK %d %d",
                       db_column_int(&q,0), db_column_int(&q,1));
          }else{
            pop3_print(pLog, "-ERR");
          }
        }else{
          pop3_print(pLog, "+OK");
          db_prepare(&q, "SELECT id, esz FROM pop3 WHERE NOT isDel");
          while( db_step(&q)==SQLITE_ROW ){
            pop3_print(pLog, "%d %d",
                       db_column_int(&q,0), db_column_int(&q,1));
          }
          pop3_print(pLog, ".");
        }
        db_finalize(&q);
        continue;
      }
      if( strcmp(zCmd,"retr")==0 || strcmp(zCmd,"top")==0 ){
        Blob all, line;
        int nLine = 0;
        int iLimit;
        int hdrPending = 1;
        if( zA1==0 ) goto cmd_error;
        iLimit = zA2 ? atoi(zA2) : 2147483647;
        if( iLimit<0 ) goto cmd_error;
        z = db_text(0, "SELECT decompress(emailblob.etxt) "
                       "  FROM emailblob, pop3"
                       " WHERE emailblob.emailid=pop3.emailid"
                       "   AND pop3.id=%d AND NOT pop3.isDel",
                       atoi(zA1));
        if( z==0 ) goto cmd_error;
        pop3_print(pLog, "+OK");
        blob_init(&all, z, -1);
        while( (hdrPending || iLimit>0) && blob_line(&all, &line) ){
          char c = blob_buffer(&line)[0];
          if( c=='.' ){
            fputc('.', stdout);
          }else if( c=='\r' || c=='\n' ){
            hdrPending = 0;
          }
          fwrite(blob_buffer(&line), 1, blob_size(&line), stdout);
          nLine++;
          if( !hdrPending ) iLimit--;
        }
        if( pLog ) fprintf(pLog, "S: # %d lines of content\n", nLine);
        pop3_print(pLog, ".");
        fossil_free(z);
        blob_reset(&all);
        blob_reset(&line);
        fflush(stdout);
        continue;
      }
      if( strcmp(zCmd,"dele")==0 ){
        if( zA1==0 ) goto cmd_error;
        db_multi_exec("UPDATE pop3 SET isDel=1 WHERE id=%d",atoi(zA1));
        goto cmd_ok;
      }
      if( strcmp(zCmd,"rset")==0 ){
        db_multi_exec("UPDATE pop3 SET isDel=0");
        goto cmd_ok;
      }
      if( strcmp(zCmd,"uidl")==0 ){
        if( zA1 ){
          db_prepare(&q, "SELECT id, emailid FROM pop3"
                         " WHERE id=%d AND NOT isDel", atoi(zA1));
          if( db_step(&q)==SQLITE_ROW ){
            pop3_print(pLog, "+OK %d %d",
                       db_column_int(&q,0), db_column_int(&q,1));
          }else{
            pop3_print(pLog,"-ERR");
          }
        }else{
          pop3_print(pLog, "+OK");
          db_prepare(&q, "SELECT id, emailid FROM pop3 WHERE NOT isDel");
          while( db_step(&q)==SQLITE_ROW ){
            pop3_print(pLog, "%d %d",
                       db_column_int(&q,0), db_column_int(&q,1));
          }
          pop3_print(pLog, ".");
        }
        db_finalize(&q);
        continue;
      }
      if( strcmp(zCmd,"noop")==0 ){
        goto cmd_ok;
      }
      /* Else, fall through into cmd_error */
    }
  cmd_error:
    pop3_print(pLog, "-ERR");
    continue;
  cmd_ok:
    pop3_print(pLog, "+OK");
    continue;
  }
  if( pLog ) fclose(pLog);
}