Fossil

Check-in [4c7edd4c]
Login

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

Overview
Comment:Enhance the backoffice to automatically purge stale entries from the emailblob table, if that table exists.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4c7edd4cfd252ec94ecb3e0f1fa042fcdf6db295a055fc8bdd7daf103c2c8bd1
User & Date: drh 2018-08-16 20:48:06.265
Context
2018-08-17
01:27
Allow + signs in email addresses. Added a comment to link to a source giving the true limit on legal characters in email addresses, which are currently far less restrictive than our newly-expanded limits. ... (check-in: 1bfd7903 user: wyoung tags: trunk)
2018-08-16
20:48
Enhance the backoffice to automatically purge stale entries from the emailblob table, if that table exists. ... (check-in: 4c7edd4c user: drh tags: trunk)
19:51
Do not put "Return-Path:" headers in notification emails to be sent. That is a violation of standards as only the receiving SMTP server should add the Return-Path header. Instead, include a custom X-Fossil-From: header that downstream software can use (if desired) to set the envelope header of the message. ... (check-in: 731836b8 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/backoffice.c.
517
518
519
520
521
522
523

524
525
526
527
528
529
530
      fprintf(pLog, "%s (%d) backoffice running\n", zDate, GETPID());
      fclose(pLog);
    }
  }

  /* Here is where the actual work of the backoffice happens */
  email_backoffice(0);

}

/*
** COMMAND: backoffice
**
** Usage: backoffice [-R repository]
**







>







517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
      fprintf(pLog, "%s (%d) backoffice running\n", zDate, GETPID());
      fclose(pLog);
    }
  }

  /* Here is where the actual work of the backoffice happens */
  email_backoffice(0);
  smtp_cleanup();
}

/*
** COMMAND: backoffice
**
** Usage: backoffice [-R repository]
**
Changes to src/smtp.c.
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
** Server implementation
*****************************************************************************/

/*
** 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
@   esz INT,                      -- uncompressed content size
@   etxt TEXT                     -- content of this entry







|
|







634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
** 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
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
@   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
@   eto TEXT,                -- Recipient email address
@   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
@ );
@







|
|







667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
@   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
@ );
@
1139
1140
1141
1142
1143
1144
1145














1146
1147
1148
1149
1150
1151
1152
    }

    /* 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.







>
>
>
>
>
>
>
>
>
>
>
>
>
>







1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
    }

    /* 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.
*/
void smtp_cleanup(void){
  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;"
    );
    db_end_transaction(0);
  }
}

/*
** COMMAND: test-emailblob-refcheck
**
** Usage: %fossil test-emailblob-refcheck [--repair] [--full] [--clean]
**
** Verify that the emailblob.enref field is correct.  Report any errors.
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
    "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"
  );







<
<
<
|







1197
1198
1199
1200
1201
1202
1203



1204
1205
1206
1207
1208
1209
1210
1211
    "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"
  );