Fossil

/*
** Schema used by the email processing system.
*/
static const char zEmailSchema[] = 
@ -- 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,  -- 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
................................................................................
@ );
@
@ -- 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
@   DELETE FROM emailblob WHERE enref<=1 AND emailid=old.ets;
@   UPDATE emailblob SET enref=enref-1 WHERE emailid=old.ets;
@ END;
@ CREATE TRIGGER IF NOT EXISTS repository.emailbox_d1
@ AFTER DELETE ON emailbox BEGIN
@   DELETE FROM emailblob WHERE enref<=1 AND emailid=old.emsgid;
@   UPDATE emailblob SET enref=enref-1 WHERE emailid=old.emsgid;
@ END;
@ CREATE TRIGGER IF NOT EXISTS repository.emailoutq_d1
@ AFTER DELETE ON emailoutq BEGIN
@   DELETE FROM emailblob WHERE enref<=1 AND emailid IN (old.ets,old.emsgid);
@   UPDATE emailblob SET enref=enref-1 WHERE emailid IN (old.ets,old.emsgid);
@ END;




;

/*
** Code used to delete the email tables.
*/
static const char zEmailDrop[] =
@ DROP TABLE IF EXISTS emailblob;
................................................................................
/*
** 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;

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

................................................................................
        "UPDATE emailblob SET enref=%d WHERE emailid=%lld", 
        p->nRef, p->idMsg
      );
    }else{
      db_multi_exec(
        "DELETE FROM emailblob WHERE emailid=%lld", p->idMsg
      );






    }

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

/*
** COMMAND: test-emailblob-refcheck
**
** Usage: %fossil test-emailblob-refcheck [--repair] [--full]
**
** 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.

*/
void test_refcheck_emailblob(void){
  int doRepair;
  int fullReport;

  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;

  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);"
................................................................................
    ") 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)"
    );






  }
  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 ){

/*
** Schema used by the email processing system.
*/
static const char zEmailSchema[] = 
@ -- 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
................................................................................
@ );
@
@ -- 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;
................................................................................
/*
** 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);
................................................................................
        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();

................................................................................
        "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);
}

/*
** 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);"
................................................................................
    ") 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 ){
      db_multi_exec(
        "UPDATE emailblob SET ets=NULL WHERE enref<=0;"
        "DELETE FROM emailblob WHERE enref<=0;"
      );
    }
  }
  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 ){

  @   }
  @ }
  @ </script>
  db_end_transaction(0);
}

/*
** WEBPAGE:  test-emailblob
**
** This page, accessible only to administrators, allows easy viewing of
** the emailblob table - the table that contains the text of email messages
** both inbound and outbound, and transcripts of SMTP sessions.
**
**    id=N          Show the text of emailblob with emailid==N
**    
................................................................................
  if( !g.perm.Setup ){
    login_needed(0);
    return;
  }
  add_content_sql_commands(g.db);
  style_header("emailblob table");
  if( id>0 ){
    style_submenu_element("Index", "%R/test-emailblob");
    @ <ul>
    db_prepare(&q, "SELECT emailid FROM emailblob WHERE ets=%d", id);
    while( db_step(&q)==SQLITE_ROW ){
      int id = db_column_int(&q, 0);
      @ <li> <a href="%R/test-emailblob?id=%d(id)">emailblob entry %d(id)</a>
    }
    db_finalize(&q);
    db_prepare(&q, "SELECT euser, estate FROM emailbox WHERE emsgid=%d", id);
    while( db_step(&q)==SQLITE_ROW ){
      const char *zUser = db_column_text(&q, 0);
      int e = db_column_int(&q, 1);
      @ <li> emailbox for %h(zUser) state %d(e)
................................................................................
    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')"

       " FROM emailblob ORDER BY etime DESC, emailid DESC");
    @ <table border="1" cellpadding="5" cellspacing="0">
    @ <tr><th> emailid <th> enref <th> ets <th> etime</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);

      @ <tr>
      @  <td><a href="%R/test-emailblob?id=%d(id)">%d(id)</a>
      @  <td>%d(nref)</td>

      @  <td>%d(ets)</td>



      @  <td>%h(zDate)</td>

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

  @   }
  @ }
  @ </script>
  db_end_transaction(0);
}

/*
** WEBPAGE:  emailblob
**
** This page, accessible only to administrators, allows easy viewing of
** the emailblob table - the table that contains the text of email messages
** both inbound and outbound, and transcripts of SMTP sessions.
**
**    id=N          Show the text of emailblob with emailid==N
**    
................................................................................
  if( !g.perm.Setup ){
    login_needed(0);
    return;
  }
  add_content_sql_commands(g.db);
  style_header("emailblob table");
  if( id>0 ){
    style_submenu_element("Index", "%R/emailblob");
    @ <ul>
    db_prepare(&q, "SELECT emailid FROM emailblob WHERE ets=%d", id);
    while( db_step(&q)==SQLITE_ROW ){
      int id = db_column_int(&q, 0);
      @ <li> <a href="%R/emailblob?id=%d(id)">emailblob entry %d(id)</a>
    }
    db_finalize(&q);
    db_prepare(&q, "SELECT euser, estate FROM emailbox WHERE emsgid=%d", id);
    while( db_step(&q)==SQLITE_ROW ){
      const char *zUser = db_column_text(&q, 0);
      int e = db_column_int(&q, 1);
      @ <li> emailbox for %h(zUser) state %d(e)
................................................................................
    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();
}