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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4c7edd4cfd252ec94ecb3e0f1fa042fcdf6db295a055fc8bdd7daf103c2c8bd1
User & Date: drh 2018-08-16 20:48:06
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
Hide Diffs Unified Diffs 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
...
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
....
1139
1140
1141
1142
1143
1144
1145














1146
1147
1148
1149
1150
1151
1152
....
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
** 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
................................................................................
@   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
@ );
@
................................................................................
    }

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







|
|







 







|
|







 







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







 







|
<
<
<







634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
...
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
....
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
....
1197
1198
1199
1200
1201
1202
1203
1204



1205
1206
1207
1208
1209
1210
1211
** 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
................................................................................
@   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
@ );
@
................................................................................
    }

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