Fossil

Check-in [fa83e4b3]
Login

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

Overview
Comment:Manage the email.c file. Accidentally omitted from the previous three check-ins. Bummer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:fa83e4b3e1eb55ccbe6ac5c402287cd5cce2a679e35431bf21a9d0c47c10b6d0
User & Date: drh 2018-06-20 19:56:30
Original Comment: Mange the email.c file. Accidentally omitted from the previous three check-ins. Bummer.
Context
2018-06-20
22:39
First attempt at a /subscribe page. Non-functional display only. check-in: fb3934ec user: drh tags: trunk
19:56
Manage the email.c file. Accidentally omitted from the previous three check-ins. Bummer. check-in: fa83e4b3 user: drh tags: trunk
19:04
Add a new capability that allows signup for email notification. Change the name of the older "EMail" privilege to "View-PII" to avoid confusion. check-in: 25eafed1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added src/email.c.

































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
/*
** Copyright (c) 2007 D. Richard Hipp
**
** This program is free software; you can redistribute it and/or
** modify it under the terms of the Simplified BSD License (also
** known as the "2-Clause License" or "FreeBSD License".)
**
** This program is distributed in the hope that it will be useful,
** but without any warranty; without even the implied warranty of
** merchantability or fitness for a particular purpose.
**
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*******************************************************************************
**
** Email notification features
*/
#include "config.h"
#include "email.h"
#include <assert.h>

/*
** SQL code to implement the tables needed by the email notification
** system.
*/
static const char zEmailInit[] =
@ -- Subscribers are distinct from users.  A person can have a log-in in
@ -- the USER table without being a subscriber.  Or a person can be a
@ -- subscriber without having a USER table entry.  Or they can have both.
@ -- In the last case the suname column points from the subscriber entry
@ -- to the USER entry.
@ --
@ CREATE TABLE repository.subscriber(
@   subscriberId INTEGER PRIMARY KEY, -- numeric subscriber ID.  Internal use
@   subscriberCode TEXT UNIQUE,       -- UUID for subscriber.  External use
@   sname TEXT,                       -- Human readable name
@   suname TEXT,                      -- Corresponding USER or NULL
@   semail TEXT,                      -- email address
@   sverify BOOLEAN,                  -- email address verified
@   sdonotcall BOOLEAN,               -- true for Do Not Call 
@   sdigest BOOLEAN,                  -- true for daily digests only
@   ssub TEXT,                        -- baseline subscriptions
@   sctime DATE,                      -- When this entry was created. JulianDay
@   smtime DATE,                      -- Last change.  JulianDay
@   sipaddr TEXT,                     -- IP address for last change
@   spswdHash TEXT                    -- SHA3 hash of password
@ );
@ 
@ -- Each subscriber is associated with zero or more subscriptions.  Each
@ -- subscription identifies events for which the subscriber desires
@ -- email notification.
@ -- 
@ -- The stype field can be:
@ --
@ --    'c'     Check-ins
@ --    'w'     Wiki pages
@ --    't'     Tickets
@ --    'e'     Tech-notes
@ --    'g'     Tags
@ --    'f'     Forum posts
@ --    'm'     Any item in need of moderation
@ --
@ -- stype values are restricted to items that suname is allowed to see.
@ -- If suname is NULL, then stype values are restricted to things that
@ -- user "nobody" is allowed to see.
@ --
@ -- The sarg field provides additional restrictions.  Since it is
@ -- part of the primary key, sarg cannot be NULL.  Use an empty string
@ -- instead.
@ --
@ -- For check-ins, sargs can be a tag that is on the check-in.  Examples:
@ -- 'trunk', or 'release'.  Notifications are only sent if that tag is
@ -- present.  For wiki, the sarg is a glob pattern matching the page name.
@ -- For tickets, sarg is the UUID of the ticket.  And so forth.
@ --
@ -- For the 'x' subscription, email is sent for any timeline event whose
@ -- text matches the GLOB pattern defined by sarg.
@ --
@ CREATE TABLE repository.subscription(
@   subscriberId INTEGER,  -- Which user has subscribed
@   stype TEXT,            -- event type
@   sarg TEXT,             -- additional event restriction
@   PRIMARY KEY(stype,sarg,subscriberId)
@ ) WITHOUT ROWID;
@ CREATE INDEX repository.subscription_x1 ON subscription(subscriberId);
@ 
@ -- Email notifications that need to be sent.
@ --
@ -- If the eventid key is an integer, then it corresponds to the
@ -- EVENT.OBJID table.  Other kinds of eventids are reserved for
@ -- future expansion.
@ --
@ CREATE TABLE repository.email_pending(
@   eventid ANY PRIMARY KEY,          -- Object that changed
@   sentSep BOOLEAN DEFAULT false,    -- individual emails sent
@   sentDigest BOOLEAN DEFAULT false  -- digest emails sent
@ ) WITHOUT ROWID;
@ 
@ -- Record bounced emails.  If too many bounces are received within
@ -- some defined time range, then cancel the subscription.  Older
@ -- entries are periodically purged.
@ --
@ CREATE TABLE repository.email_bounce(
@   subscriberId INTEGER, -- to whom the email was sent.
@   sendTime INTEGER,     -- seconds since 1970 when email was sent
@   rcvdTime INTEGER      -- seconds since 1970 when bounce was received
@ );
;

/*
** Make sure the unversioned table exists in the repository.
*/
void email_schema(void){
  if( !db_table_exists("repository", "subscriber") ){
    db_multi_exec(zEmailInit/*works-like:""*/);
  }
}


/*
** WEBPAGE: setup_email
**
** Administrative page for configuring and controlling email notification
*/
void setup_email(void){
  static const char *const azSendMethods[] = {
    "off",  "Disabled",
    "pipe", "Pipe to a command",
    "db",   "Store in a database",
    "file", "Store in a directory"
  };
  login_check_credentials();
  if( !g.perm.Setup ){
    login_needed(0);
    return;
  }
  db_begin_transaction();

  style_header("Email Notification Setup");
  @ <form action="%R/setup_email" method="post"><div>
  @ <input type="submit"  name="submit" value="Apply Changes" /><hr>
  login_insert_csrf_secret();
  multiple_choice_attribute("Email Send Method","email-send-method",
       "esm", "off", count(azSendMethods)/2, azSendMethods);
  @ <p>How to send email.  The "Pipe to a command"
  @ method is the usual choice in production.
  @ (Property: "email-send-method")</p>
  @ <hr>
  entry_attribute("Command To Pipe Email To", 80, "esc",
                   "email-send-command", "sendmail -t", 0);
  @ <p>When the send method is "pipe to a command", this is the command
  @ that is run.  Email messages are piped into the standard input of this
  @ command.  The command is expected to extract the sender address,
  @ recepient addresses, and subject from the header of the piped email
  @ text.  (Property: "email-send-command")</p>

  entry_attribute("Database In Which To Store Email", 60, "esdb",
                   "email-send-db", "", 0);
  @ <p>When the send method is "store in a databaes", each email message is
  @ stored in an SQLite database file with the name given here.
  @ (Property: "email-send-db")</p>

  entry_attribute("Directory In Which To Store Email", 60, "esdir",
                   "email-send-dir", "", 0);
  @ <p>When the send method is "store in a directory", each email message is
  @ stored as a separate file in the directory shown here.
  @ (Property: "email-send-dir")</p>
  @ <hr>

  entry_attribute("\"From\" email address", 40, "ef",
                   "email-self", "", 0);
  @ <p>This is the email from which email notifications are sent.  The
  @ system administrator should arrange for emails sent to this address
  @ to be handed off to the "fossil email incoming" command so that Fossil
  @ can handle bounces. (Property: "email-self")</p>
  @ <hr>

  entry_attribute("Administrator email address", 40, "ea",
                   "email-admin", "", 0);
  @ <p>This is the email for the human administrator for the system.
  @ Abuse and trouble reports are send here.
  @ (Property: "email-admin")</p>
  @ <hr>
  @ <p><input type="submit"  name="submit" value="Apply Changes" /></p>
  @ </div></form>
  db_end_transaction(0);
  style_footer();
}

/*
** Encode pMsg as MIME base64 and append it to pOut
*/
static void append_base64(Blob *pOut, Blob *pMsg){
  int n, i, k;
  char zBuf[100];
  n = blob_size(pMsg);
  for(i=0; i<n; i+=54){
    k = translateBase64(blob_buffer(pMsg)+i, i+54<n ? 54 : n-i, zBuf);
    blob_append(pOut, zBuf, k);
    blob_append(pOut, "\r\n", 2);
  }
}

/*
** Send an email message using whatever sending mechanism is configured
** by these settings:
**
**   email-send-method    "off"   Do not send any emails
**                        "pipe"  Pipe the email to email-send-command
**                        "db"    Store the mail in database email-send-db
**                        "file"  Store the email as a file in email-send-dir 
**
** The recepient(s) must be specified using  "To:" or "Cc:" or "Bcc:" fields
** in the header.  Likewise, the header must contains a "Subject:" line.
** The header might also include fields like "Message-Id:" or
** "In-Reply-To:".
**
** This routine will add fields to the header as follows:
**
**     From:
**     Content-Type:
**     Content-Transfer-Encoding:
**     
** At least one body must be supplied.
**
** The caller maintains ownership of the input Blobs.  This routine will
** read the Blobs and send them onward to the email system, but it will
** not free them.
**
** If zDest is not NULL then it is an overwrite for the email-send-method.
** zDest can be "stdout" to send output to the console for debugging.
*/
void email_send(Blob *pHdr, Blob *pPlain, Blob *pHtml, const char *zDest){
  const char *zFrom = db_get("email-self", 0);
  char *zBoundary = 0;
  Blob all;
  if( zFrom==0 ){
    fossil_warning("Missing configuration: \"email-self\"");
    return;
  }
  if( zDest==0 ) zDest = db_get("email-send-method", "off");
  if( strcmp(zDest, "off")==0 ){
    return;
  }
  blob_init(&all, 0, 0);
  blob_append(&all, blob_buffer(pHdr), blob_size(pHdr));
  blob_appendf(&all, "From: %s\r\n", zFrom);
  if( pPlain && pHtml ){
    blob_appendf(&all, "MIME-Version: 1.0\r\n");
    zBoundary = db_text(0, "SELECT hex(randomblob(20))");
    blob_appendf(&all, "Content-Type: multipart/alternative;"
                       " boundary=\"%s\"\r\n", zBoundary);
  }
  if( pPlain ){
    if( zBoundary ){
      blob_appendf(&all, "\r\n--%s\r\n", zBoundary);
    }
    blob_appendf(&all,"Content-Type: text/plain\r\n");
    blob_appendf(&all, "Content-Transfer-Encoding: base64\r\n\r\n");
    append_base64(&all, pPlain);
  }
  if( pHtml ){
    if( zBoundary ){
      blob_appendf(&all, "--%s\r\n", zBoundary);
    }
    blob_appendf(&all,"Content-Type: text/html\r\n");
    blob_appendf(&all, "Content-Transfer-Encoding: base64\r\n\r\n");
    append_base64(&all, pHtml);
  }
  if( zBoundary ){
    blob_appendf(&all, "--%s--\r\n", zBoundary);
    fossil_free(zBoundary);
    zBoundary = 0;
  }
  if( strcmp(zDest, "db")==0 ){
    sqlite3 *db;
    sqlite3_stmt *pStmt;
    int rc;
    const char *zDb = db_get("email-send-db",0);
    rc = sqlite3_open(zDb, &db);
    if( rc==SQLITE_OK ){
      sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS email(\n"
                       "  emailid INTEGER PRIMARY KEY,\n"
                       "  msg TEXT\n);", 0, 0, 0);
      rc = sqlite3_prepare_v2(db, "INSERT INTO email(msg) VALUES(?1)", -1,
                              &pStmt, 0);
      if( rc==SQLITE_OK ){
        sqlite3_bind_text(pStmt, 1, blob_str(&all), -1, SQLITE_TRANSIENT);
        sqlite3_step(pStmt);
        sqlite3_finalize(pStmt);
      }
      sqlite3_close(db);
    }
  }else if( strcmp(zDest, "pipe")==0 ){
    const char *zCmd = db_get("email-send-command", 0);
    if( zCmd ){
      FILE *out = popen(zCmd, "w");
      if( out ){
        fwrite(blob_buffer(&all), 1, blob_size(&all), out);
        fclose(out);
      }
    }
  }else if( strcmp(zDest, "dir")==0 ){
    const char *zDir = db_get("email-send-dir","./");
    char *zFile = db_text(0,
        "SELECT %Q||strftime('/%%Y%%m%%d%%H%%M%%S','now')||hex(randomblob(8))",
        zDir);
    blob_write_to_file(&all, zFile);
    fossil_free(zFile);
  }else if( strcmp(zDest, "stdout")==0 ){
    fossil_print("%s\n", blob_str(&all));
  }
  blob_zero(&all);
}

/*
** SETTING: email-send-method         width=5 default=off
** Determine the method used to send email.  Allowed values are
** "off", "pipe", "dir", "db", and "stdout".  The "off" value means
** no email is ever sent.  The "pipe" value means email messages are
** piped into a command determined by the email-send-command setting.
** The "dir" value means emails are written to individual files in a
** directory determined by the email-send-dir setting.  The "db" value
** means that emails are added to an SQLite database named by the
** email-send-db setting.  The "stdout" value writes email text to
** standard output, for debugging.
*/
/*
** SETTING: email-send-command       width=40
** This is a command to which outbound email content is piped when the
** email-send-method is set to "pipe".  The command must extract
** recipient, sender, subject, and all other relevant information
** from the email header.
*/
/*
** SETTING: email-send-dir           width=40
** This is a directory into which outbound emails are written as individual
** files if the email-send-method is set to "dir".
*/
/*
** SETTING: email-send-db            width=40
** This is an SQLite database file into which outbound emails are written
** if the email-send-method is set to "db".
*/
/*
** SETTING: email-self               width=40
** This is the email address for the repository.  Outbound emails add
** this email address as the "From:" field.
*/


/*
** COMMAND: email
** 
** Usage: %fossil email SUBCOMMAND ARGS...
**
** Subcommands:
**
**    send TO [OPTIONS]       Send a single email message using whatever
**                            email sending mechanism is currently configured.
**                            Use this for testing the email configuration.
**                            Options:
**
**                              --body FILENAME
**                              --html
**                              --stdout
**                              --subject|-S SUBJECT
**
**    settings [NAME VALUE]   With no arguments, list all email settings.
**                            Or change the value of a single email setting.
*/
void email_cmd(void){
  const char *zCmd;
  int nCmd;
  db_find_and_open_repository(0, 0);
  email_schema();
  zCmd = g.argc>=3 ? g.argv[2] : "x";
  nCmd = (int)strlen(zCmd);
  if( strncmp(zCmd, "send", nCmd)==0 ){
    Blob prompt, body, hdr;
    int sendAsBoth = find_option("both",0,0)!=0;
    int sendAsHtml = find_option("html",0,0)!=0;
    const char *zDest = find_option("stdout",0,0)!=0 ? "stdout" : 0;
    int i;
    const char *zSubject = find_option("subject", "S", 1);
    const char *zSource = find_option("body", 0, 1);
    verify_all_options();
    blob_init(&prompt, 0, 0);
    blob_init(&body, 0, 0);
    blob_init(&hdr, 0, 0);
    for(i=3; i<g.argc; i++){
      blob_appendf(&hdr, "To: %s\n", g.argv[i]);
    }
    if( zSubject ){
      blob_appendf(&hdr, "Subject: %s\n", zSubject);
    }
    if( zSource ){
      blob_read_from_file(&body, zSource, ExtFILE);
    }else{
      prompt_for_user_comment(&body, &prompt);
    }
    if( sendAsHtml ){
      email_send(&hdr, 0, &body, zDest);
    }else if( sendAsBoth ){
      Blob html;
      blob_init(&html, 0, 0);
      blob_appendf(&html, "<pre>\n%h</pre>\n", blob_str(&body));
      email_send(&hdr, &body, &html, zDest);
      blob_zero(&html);
    }else{
      email_send(&hdr, &body, 0, zDest);
    }
    blob_zero(&hdr);
    blob_zero(&body);
    blob_zero(&prompt);
  }
  else if( strncmp(zCmd, "settings", nCmd)==0 ){
    int isGlobal = find_option("global",0,0)!=0;
    int i;
    int nSetting;
    const Setting *pSetting = setting_info(&nSetting);
    db_open_config(1, 0);
    verify_all_options();
    if( g.argc!=3 && g.argc!=5 ) usage("setting [NAME VALUE]");
    if( g.argc==5 ){
      const char *zLabel = g.argv[3];
      if( strncmp(zLabel, "email-", 6)!=0
       || (pSetting = db_find_setting(zLabel, 1))==0 ){
        fossil_fatal("not a valid email setting: \"%s\"", zLabel);
      }
      db_set(pSetting->name, g.argv[4], isGlobal);
      g.argc = 3;
    }
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
  }
  else{
    usage("send|setting");
  }
}

/*
** WEBPAGE: subscribe
**
** Allow users to subscribe to email notifications, or to change or
** verify their subscription.
*/
void subscribe_page(void){
  login_check_credentials();
  if( !g.perm.EmailAlert ){
    login_needed(g.anon.EmailAlert);
    return;
  }
  style_header("Email Subscription");
  form_begin(0, "%R/subscribe");
  @ <table class="subscribe">
  @ <tr>
  @  <td class="subscribe_label">Nickname:</td>
  @  <td><input type="text" id="nn" value="" size="30"></td>
  @  <td><span class="optionalTag">(optional)</span></td>
  @ </tr>
  @ <tr>
  @  <td class="subscribe_label">Email&nbsp;Address:</td>
  @  <td><input type="text" id="e" value="" size="30"></td>
  @  <td></td>
  @ </tr>
  @ <tr>
  @  <td class="subscribe_label">Password:</td>
  @  <td><input type="password" id="p1" value="" size="30"></td>
  @  <td><span class="optionalTag">(optional)</span></td>
  @ </tr>


  
}