Fossil

Check-in [98d2338a]
Login

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

Overview
Comment:Webpages function (with an sensible error) if the email notification tables are not in the schema.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 98d2338a431b95efa2172a214fc802bb5b7ee633bf5ab31ecf197f022274135d
User & Date: drh 2018-06-23 17:07:12.229
Context
2018-06-23
17:17
New note on why SMTP might be hard to implement as a sending method. ... (check-in: b3e72035 user: drh tags: trunk)
17:07
Webpages function (with an sensible error) if the email notification tables are not in the schema. ... (check-in: 98d2338a user: drh tags: trunk)
16:44
Improved documentation. No substantive code changes. ... (check-in: 9cf915d4 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/email.c.
27
28
29
30
31
32
33

34
35
36
37
38
39
40
#define SUBSCRIBER_CODE_SZ 32

/*
** 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.
@ --
@ -- The ssub field is a string where each character indicates a particular







>







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#define SUBSCRIBER_CODE_SZ 32

/*
** SQL code to implement the tables needed by the email notification
** system.
*/
static const char zEmailInit[] =
@ DROP TABLE IF EXISTS repository.subscriber;
@ -- 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.
@ --
@ -- The ssub field is a string where each character indicates a particular
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
@   sctime DATE,                      -- When this entry was created. JulianDay
@   smtime DATE,                      -- Last change.  JulianDay
@   smip TEXT                         -- IP address of last change
@ );
@ CREATE INDEX repository.subscriberUname
@   ON subscriber(suname) WHERE suname IS NOT NULL;
@ 

@ -- Email notifications that need to be sent.
@ --
@ -- The first character of the eventid determines the event type.
@ -- Remaining characters determine the specific event.  For example,
@ -- 'c4413' means check-in with rid=4413.
@ --
@ CREATE TABLE repository.pending_alert(
@   eventid TEXT 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:""*/);
    email_triggers_enable();
  }
}

/*
** Enable triggers that automatically populate the event_pending
** table.
*/
void email_triggers_enable(void){
  if( !db_table_exists("repository","pending_alert") ) return;
  db_multi_exec(
    "CREATE TRIGGER IF NOT EXISTS repository.email_trigger1\n"
    "AFTER INSERT ON event BEGIN\n"







>












>












>
>
>
>
>
|
>
>
>
>
>

|
|
>
>
>
>
>






|







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
@   sctime DATE,                      -- When this entry was created. JulianDay
@   smtime DATE,                      -- Last change.  JulianDay
@   smip TEXT                         -- IP address of last change
@ );
@ CREATE INDEX repository.subscriberUname
@   ON subscriber(suname) WHERE suname IS NOT NULL;
@ 
@ DROP TABLE IF EXISTS repository.pending_alert;
@ -- Email notifications that need to be sent.
@ --
@ -- The first character of the eventid determines the event type.
@ -- Remaining characters determine the specific event.  For example,
@ -- 'c4413' means check-in with rid=4413.
@ --
@ CREATE TABLE repository.pending_alert(
@   eventid TEXT PRIMARY KEY,         -- Object that changed
@   sentSep BOOLEAN DEFAULT false,    -- individual emails sent
@   sentDigest BOOLEAN DEFAULT false  -- digest emails sent
@ ) WITHOUT ROWID;
@ 
@ DROP TABLE IF EXISTS repository.email_bounce;
@ -- 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
@ );
;

/*
** Return true if the email notification tables exist.
*/
int email_tables_exist(void){
  return db_table_exists("repository", "subscriber");
}

/*
** Make sure the table needed for email notification exist in the repository.
**
** If the bOnlyIfEnabled option is true, then tables are only created
** if the email-send-method is something other than "off".
*/
void email_schema(int bOnlyIfEnabled){
  if( !email_tables_exist() ){
    if( bOnlyIfEnabled
     && fossil_strcmp(db_get("email-send-method","off"),"off")==0
    ){
      return;  /* Don't create table for disabled email */
    }
    db_multi_exec(zEmailInit/*works-like:""*/);
    email_triggers_enable();
  }
}

/*
** Enable triggers that automatically populate the pending_alert
** table.
*/
void email_triggers_enable(void){
  if( !db_table_exists("repository","pending_alert") ) return;
  db_multi_exec(
    "CREATE TRIGGER IF NOT EXISTS repository.email_trigger1\n"
    "AFTER INSERT ON event BEGIN\n"
124
125
126
127
128
129
130
131
132
133
134
135












136
137
138
139
140
141
142
143
  );
}

/*
** Return true if email alerts are active.
*/
int email_enabled(void){
  if( !db_table_exists("repository", "subscriber") ) return 0;
  if( fossil_strcmp(db_get("email-send-method","off"),"off")==0 ) return 0;
  return 1;
}















/*
** Insert a "Subscriber List" submenu link if the current user
** is an administrator.
*/
void email_submenu_common(void){
  if( g.perm.Admin ){







|




>
>
>
>
>
>
>
>
>
>
>
>
|







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

/*
** Return true if email alerts are active.
*/
int email_enabled(void){
  if( !email_tables_exist() ) return 0;
  if( fossil_strcmp(db_get("email-send-method","off"),"off")==0 ) return 0;
  return 1;
}

/*
** If the subscriber table does not exist, then paint an error message
** web page and return true.
**
** If the subscriber table does exist, return 0 without doing anything.
*/
static int email_webpages_disabled(void){
  if( email_tables_exist() ) return 0;
  style_header("Email Alerts Are Disabled");
  @ <p>Email alerts are disabled on this server</p>
  style_footer();
  return 1;
}

/*
** Insert a "Subscriber List" submenu link if the current user
** is an administrator.
*/
void email_submenu_common(void){
  if( g.perm.Admin ){
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220

  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, "email-send-command",
                   "ecmd", "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







|







236
237
238
239
240
241
242
243
244
245
246
247
248
249
250

  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>
  email_schema(1);

  entry_attribute("Command To Pipe Email To", 80, "email-send-command",
                   "ecmd", "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
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
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, "exec", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
    if( find_option("test",0,0)!=0 ){
      eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;







|







607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
void email_cmd(void){
  const char *zCmd;
  int nCmd;
  db_find_and_open_repository(0, 0);
  email_schema(0);
  zCmd = g.argc>=3 ? g.argv[2] : "x";
  nCmd = (int)strlen(zCmd);
  if( strncmp(zCmd, "exec", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
    if( find_option("test",0,0)!=0 ){
      eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
        "DROP TABLE IF EXISTS subscriber;\n"
        "DROP TABLE IF EXISTS pending_alert;\n"
        "DROP TABLE IF EXISTS email_bounce;\n"
        /* Legacy */
        "DROP TABLE IF EXISTS email_pending;\n"
        "DROP TABLE IF EXISTS subscription;\n"
      );
      email_schema();
    }
  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    Blob prompt, body, hdr;
    const char *zDest = find_option("stdout",0,0)!=0 ? "stdout" : 0;
    int i;
    const char *zSubject = find_option("subject", "S", 1);







|







660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
        "DROP TABLE IF EXISTS subscriber;\n"
        "DROP TABLE IF EXISTS pending_alert;\n"
        "DROP TABLE IF EXISTS email_bounce;\n"
        /* Legacy */
        "DROP TABLE IF EXISTS email_pending;\n"
        "DROP TABLE IF EXISTS subscription;\n"
      );
      email_schema(0);
    }
  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    Blob prompt, body, hdr;
    const char *zDest = find_option("stdout",0,0)!=0 ? "stdout" : 0;
    int i;
    const char *zSubject = find_option("subject", "S", 1);
833
834
835
836
837
838
839

840
841
842
843
844
845
846
  int needCaptcha;
  unsigned int uSeed;
  const char *zDecoded;
  char *zCaptcha = 0;
  char *zErr = 0;
  int eErr = 0;


  login_check_credentials();
  if( !g.perm.EmailAlert ){
    login_needed(g.anon.EmailAlert);
    return;
  }
  if( login_is_individual()
   && db_exists("SELECT 1 FROM subscriber WHERE suname=%Q",g.zLogin)







>







863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
  int needCaptcha;
  unsigned int uSeed;
  const char *zDecoded;
  char *zCaptcha = 0;
  char *zErr = 0;
  int eErr = 0;

  if( email_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.EmailAlert ){
    login_needed(g.anon.EmailAlert);
    return;
  }
  if( login_is_individual()
   && db_exists("SELECT 1 FROM subscriber WHERE suname=%Q",g.zLogin)
1057
1058
1059
1060
1061
1062
1063

1064
1065
1066
1067
1068
1069
1070
  const char *ssub;
  const char *semail;
  const char *smip;
  const char *suname;
  int eErr = 0;
  char *zErr = 0;


  login_check_credentials();
  if( !g.perm.EmailAlert ){
    cgi_redirect("subscribe");
    return;
  }
  if( zName==0 && login_is_individual() ){
    zName = db_text(0, "SELECT hex(subscriberCode) FROM subscriber"







>







1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
  const char *ssub;
  const char *semail;
  const char *smip;
  const char *suname;
  int eErr = 0;
  char *zErr = 0;

  if( email_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.EmailAlert ){
    cgi_redirect("subscribe");
    return;
  }
  if( zName==0 && login_is_individual() ){
    zName = db_text(0, "SELECT hex(subscriberCode) FROM subscriber"
1390
1391
1392
1393
1394
1395
1396

1397
1398
1399
1400
1401
1402
1403
** Clicking on an email takes one to the /alerts page
** for that email where the delivery settings can be
** modified.
*/
void subscriber_list_page(void){
  Blob sql;
  Stmt q;

  login_check_credentials();
  if( !g.perm.Admin ){
    fossil_redirect_home();
    return;
  }
  email_submenu_common();
  style_header("Subscriber List");







>







1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
** Clicking on an email takes one to the /alerts page
** for that email where the delivery settings can be
** modified.
*/
void subscriber_list_page(void){
  Blob sql;
  Stmt q;
  if( email_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.Admin ){
    fossil_redirect_home();
    return;
  }
  email_submenu_common();
  style_header("Subscriber List");
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
  Blob out;
  int nEvent;
  EmailEvent *pEvent, *p;

  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema();
  db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
  if( g.argc==2 ){
    db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");
  }else{
    int i;
    for(i=2; i<g.argc; i++){
      db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);







|







1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
  Blob out;
  int nEvent;
  EmailEvent *pEvent, *p;

  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema(0);
  db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT)");
  if( g.argc==2 ){
    db_multi_exec("INSERT INTO wantalert SELECT eventid FROM pending_alert");
  }else{
    int i;
    for(i=2; i<g.argc; i++){
      db_multi_exec("INSERT INTO wantalert VALUES(%Q)", g.argv[i]);
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
** Run /timeline?showid to see these OBJID values.
*/
void test_add_alert_cmd(void){
  int i;
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema();
  for(i=2; i<g.argc; i++){
    db_multi_exec("REPLACE INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
}

#if INTERFACE







|







1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
** Run /timeline?showid to see these OBJID values.
*/
void test_add_alert_cmd(void){
  int i;
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema(0);
  for(i=2; i<g.argc; i++){
    db_multi_exec("REPLACE INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
}

#if INTERFACE