Fossil SCM

Rename the email_pending table to pending_alert. Add triggers to fill in the pending_alert table each time a row is added to the event table.

drh 2018-06-22 01:18 UTC email-alerts
Commit 8c4b92ad3e27bb17bdde5ce5005f6b12852513b80a77581f6b1e39a93e402a53
Parent 775e529b32022e3…
2 files changed +53 -15 +6 -2
~ src/email.c ~ src/rebuild.c
M src/email.c
+53 -15
--- src/email.c
+++ src/email.c
@@ -63,16 +63,16 @@
6363
 @ CREATE INDEX repository.subscriberUname
6464
 @ ON subscriber(suname) WHERE suname IS NOT NULL;
6565
 @
6666
 @ -- Email notifications that need to be sent.
6767
 @ --
68
-@ -- If the eventid key is an integer, then it corresponds to the
69
-@ -- EVENT.OBJID table. Other kinds of eventids are reserved for
70
-@ -- future expansion.
68
+@ -- The first character of the eventid determines the event type.
69
+@ -- Remaining characters determine the specific event. For example,
70
+@ -- 'c4413' means check-in with rid=4413.
7171
 @ --
72
-@ CREATE TABLE repository.email_pending(
73
-@ eventid ANY PRIMARY KEY, -- Object that changed
72
+@ CREATE TABLE repository.pending_alert(
73
+@ eventid TEXT PRIMARY KEY, -- Object that changed
7474
 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
7575
 @ sentDigest BOOLEAN DEFAULT false -- digest emails sent
7676
 @ ) WITHOUT ROWID;
7777
 @
7878
 @ -- Record bounced emails. If too many bounces are received within
@@ -90,12 +90,41 @@
9090
 ** Make sure the unversioned table exists in the repository.
9191
 */
9292
 void email_schema(void){
9393
 if( !db_table_exists("repository", "subscriber") ){
9494
 db_multi_exec(zEmailInit/*works-like:""*/);
95
+ email_triggers_enable();
9596
 }
9697
 }
98
+
99
+/*
100
+** Enable triggers that automatically populate the event_pending
101
+** table.
102
+*/
103
+void email_triggers_enable(void){
104
+ if( !db_table_exists("repository","pending_alert") ) return;
105
+ db_multi_exec(
106
+ "CREATE TRIGGER IF NOT EXISTS repository.email_trigger1\n"
107
+ "AFTER INSERT ON event BEGIN\n"
108
+ " INSERT INTO pending_alert(eventid)\n"
109
+ " SELECT printf('%%.1c%%d',new.type,new.objid) WHERE true\n"
110
+ " ON CONFLICT(eventId) DO NOTHING;\n"
111
+ "END;"
112
+ );
113
+}
114
+
115
+/*
116
+** Disable triggers the event_pending triggers.
117
+**
118
+** This must be called before rebuilding the EVENT table, for example
119
+** via the "fossil rebuild" command.
120
+*/
121
+void email_triggers_disable(void){
122
+ db_multi_exec(
123
+ "DROP TRIGGER IF EXISTS repository.email_trigger1;\n"
124
+ );
125
+}
97126
98127
 /*
99128
 ** Return true if email alerts are active.
100129
 */
101130
 int email_enabled(void){
@@ -444,28 +473,37 @@
444473
 fossil_free(zFN);
445474
 }
446475
 email_receive(&email);
447476
 }else
448477
 if( strncmp(zCmd, "reset", nCmd)==0 ){
449
- Blob yn;
450478
 int c;
451
- fossil_print(
452
- "This will erase all content in the repository tables, thus\n"
453
- "deleting all subscriber information. The information will be\n"
454
- "unrecoverable.\n");
455
- prompt_user("Continue? (y/N) ", &yn);
456
- c = blob_str(&yn)[0];
479
+ int bForce = find_option("force","f",0)!=0;
480
+ verify_all_options();
481
+ if( bForce ){
482
+ c = 'y';
483
+ }else{
484
+ Blob yn;
485
+ fossil_print(
486
+ "This will erase all content in the repository tables, thus\n"
487
+ "deleting all subscriber information. The information will be\n"
488
+ "unrecoverable.\n");
489
+ prompt_user("Continue? (y/N) ", &yn);
490
+ c = blob_str(&yn)[0];
491
+ blob_zero(&yn);
492
+ }
457493
 if( c=='y' ){
494
+ email_triggers_disable();
458495
 db_multi_exec(
459496
 "DROP TABLE IF EXISTS subscriber;\n"
460
- "DROP TABLE IF EXISTS subscription;\n"
497
+ "DROP TABLE IF EXISTS pending_alert;\n"
498
+ "DROP TABLE IF EXISTS email_bounce;\n"
499
+ /* Legacy */
461500
 "DROP TABLE IF EXISTS email_pending;\n"
462
- "DROP TABLE IF EXISTS email_bounce;\n"
501
+ "DROP TABLE IF EXISTS subscription;\n"
463502
 );
464503
 email_schema();
465504
 }
466
- blob_zero(&yn);
467505
 }else
468506
 if( strncmp(zCmd, "send", nCmd)==0 ){
469507
 Blob prompt, body, hdr;
470508
 int sendAsBoth = find_option("both",0,0)!=0;
471509
 int sendAsHtml = find_option("html",0,0)!=0;
472510
--- src/email.c
+++ src/email.c
@@ -63,16 +63,16 @@
63 @ CREATE INDEX repository.subscriberUname
64 @ ON subscriber(suname) WHERE suname IS NOT NULL;
65 @
66 @ -- Email notifications that need to be sent.
67 @ --
68 @ -- If the eventid key is an integer, then it corresponds to the
69 @ -- EVENT.OBJID table. Other kinds of eventids are reserved for
70 @ -- future expansion.
71 @ --
72 @ CREATE TABLE repository.email_pending(
73 @ eventid ANY PRIMARY KEY, -- Object that changed
74 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
75 @ sentDigest BOOLEAN DEFAULT false -- digest emails sent
76 @ ) WITHOUT ROWID;
77 @
78 @ -- Record bounced emails. If too many bounces are received within
@@ -90,12 +90,41 @@
90 ** Make sure the unversioned table exists in the repository.
91 */
92 void email_schema(void){
93 if( !db_table_exists("repository", "subscriber") ){
94 db_multi_exec(zEmailInit/*works-like:""*/);
 
95 }
96 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
97
98 /*
99 ** Return true if email alerts are active.
100 */
101 int email_enabled(void){
@@ -444,28 +473,37 @@
444 fossil_free(zFN);
445 }
446 email_receive(&email);
447 }else
448 if( strncmp(zCmd, "reset", nCmd)==0 ){
449 Blob yn;
450 int c;
451 fossil_print(
452 "This will erase all content in the repository tables, thus\n"
453 "deleting all subscriber information. The information will be\n"
454 "unrecoverable.\n");
455 prompt_user("Continue? (y/N) ", &yn);
456 c = blob_str(&yn)[0];
 
 
 
 
 
 
 
 
457 if( c=='y' ){
 
458 db_multi_exec(
459 "DROP TABLE IF EXISTS subscriber;\n"
460 "DROP TABLE IF EXISTS subscription;\n"
 
 
461 "DROP TABLE IF EXISTS email_pending;\n"
462 "DROP TABLE IF EXISTS email_bounce;\n"
463 );
464 email_schema();
465 }
466 blob_zero(&yn);
467 }else
468 if( strncmp(zCmd, "send", nCmd)==0 ){
469 Blob prompt, body, hdr;
470 int sendAsBoth = find_option("both",0,0)!=0;
471 int sendAsHtml = find_option("html",0,0)!=0;
472
--- src/email.c
+++ src/email.c
@@ -63,16 +63,16 @@
63 @ CREATE INDEX repository.subscriberUname
64 @ ON subscriber(suname) WHERE suname IS NOT NULL;
65 @
66 @ -- Email notifications that need to be sent.
67 @ --
68 @ -- The first character of the eventid determines the event type.
69 @ -- Remaining characters determine the specific event. For example,
70 @ -- 'c4413' means check-in with rid=4413.
71 @ --
72 @ CREATE TABLE repository.pending_alert(
73 @ eventid TEXT PRIMARY KEY, -- Object that changed
74 @ sentSep BOOLEAN DEFAULT false, -- individual emails sent
75 @ sentDigest BOOLEAN DEFAULT false -- digest emails sent
76 @ ) WITHOUT ROWID;
77 @
78 @ -- Record bounced emails. If too many bounces are received within
@@ -90,12 +90,41 @@
90 ** Make sure the unversioned table exists in the repository.
91 */
92 void email_schema(void){
93 if( !db_table_exists("repository", "subscriber") ){
94 db_multi_exec(zEmailInit/*works-like:""*/);
95 email_triggers_enable();
96 }
97 }
98
99 /*
100 ** Enable triggers that automatically populate the event_pending
101 ** table.
102 */
103 void email_triggers_enable(void){
104 if( !db_table_exists("repository","pending_alert") ) return;
105 db_multi_exec(
106 "CREATE TRIGGER IF NOT EXISTS repository.email_trigger1\n"
107 "AFTER INSERT ON event BEGIN\n"
108 " INSERT INTO pending_alert(eventid)\n"
109 " SELECT printf('%%.1c%%d',new.type,new.objid) WHERE true\n"
110 " ON CONFLICT(eventId) DO NOTHING;\n"
111 "END;"
112 );
113 }
114
115 /*
116 ** Disable triggers the event_pending triggers.
117 **
118 ** This must be called before rebuilding the EVENT table, for example
119 ** via the "fossil rebuild" command.
120 */
121 void email_triggers_disable(void){
122 db_multi_exec(
123 "DROP TRIGGER IF EXISTS repository.email_trigger1;\n"
124 );
125 }
126
127 /*
128 ** Return true if email alerts are active.
129 */
130 int email_enabled(void){
@@ -444,28 +473,37 @@
473 fossil_free(zFN);
474 }
475 email_receive(&email);
476 }else
477 if( strncmp(zCmd, "reset", nCmd)==0 ){
 
478 int c;
479 int bForce = find_option("force","f",0)!=0;
480 verify_all_options();
481 if( bForce ){
482 c = 'y';
483 }else{
484 Blob yn;
485 fossil_print(
486 "This will erase all content in the repository tables, thus\n"
487 "deleting all subscriber information. The information will be\n"
488 "unrecoverable.\n");
489 prompt_user("Continue? (y/N) ", &yn);
490 c = blob_str(&yn)[0];
491 blob_zero(&yn);
492 }
493 if( c=='y' ){
494 email_triggers_disable();
495 db_multi_exec(
496 "DROP TABLE IF EXISTS subscriber;\n"
497 "DROP TABLE IF EXISTS pending_alert;\n"
498 "DROP TABLE IF EXISTS email_bounce;\n"
499 /* Legacy */
500 "DROP TABLE IF EXISTS email_pending;\n"
501 "DROP TABLE IF EXISTS subscription;\n"
502 );
503 email_schema();
504 }
 
505 }else
506 if( strncmp(zCmd, "send", nCmd)==0 ){
507 Blob prompt, body, hdr;
508 int sendAsBoth = find_option("both",0,0)!=0;
509 int sendAsHtml = find_option("html",0,0)!=0;
510
M src/rebuild.c
+6 -2
--- src/rebuild.c
+++ src/rebuild.c
@@ -144,11 +144,12 @@
144144
 ** Update the repository schema for Fossil version 2.0. (2017-02-28)
145145
 ** (1) Change the CHECK constraint on BLOB.UUID so that the length
146146
 ** is greater than or equal to 40, not exactly equal to 40.
147147
 */
148148
 void rebuild_schema_update_2_0(void){
149
- char *z = db_text(0, "SELECT sql FROM repository.sqlite_master WHERE name='blob'");
149
+ char *z = db_text(0, "SELECT sql FROM repository.sqlite_master"
150
+ " WHERE name='blob'");
150151
 if( z ){
151152
 /* Search for: length(uuid)==40
152153
 ** 0123456789 12345 */
153154
 int i;
154155
 for(i=10; z[i]; i++){
@@ -357,19 +358,21 @@
357358
 ttyOutput = doOut;
358359
 processCnt = 0;
359360
 if (ttyOutput && !g.fQuiet) {
360361
 percent_complete(0);
361362
 }
363
+ email_triggers_disable();
362364
 rebuild_update_schema();
363365
 blob_init(&sql, 0, 0);
364366
 db_prepare(&q,
365367
 "SELECT name FROM sqlite_master /*scan*/"
366368
 " WHERE type='table'"
367369
 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
368370
 "'config','shun','private','reportfmt',"
369371
 "'concealed','accesslog','modreq',"
370
- "'purgeevent','purgeitem','unversioned')"
372
+ "'purgeevent','purgeitem','unversioned',"
373
+ "'subscriber','pending_alert','email_bounce')"
371374
 " AND name NOT GLOB 'sqlite_*'"
372375
 " AND name NOT GLOB 'fx_*'"
373376
 );
374377
 while( db_step(&q)==SQLITE_ROW ){
375378
 blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
@@ -446,10 +449,11 @@
446449
 if( doClustering ) create_cluster();
447450
 if( ttyOutput && !g.fQuiet && totalSize>0 ){
448451
 processCnt += incrSize;
449452
 percent_complete((processCnt*1000)/totalSize);
450453
 }
454
+ email_triggers_enable();
451455
 if(!g.fQuiet && ttyOutput ){
452456
 percent_complete(1000);
453457
 fossil_print("\n");
454458
 }
455459
 return errCnt;
456460
--- src/rebuild.c
+++ src/rebuild.c
@@ -144,11 +144,12 @@
144 ** Update the repository schema for Fossil version 2.0. (2017-02-28)
145 ** (1) Change the CHECK constraint on BLOB.UUID so that the length
146 ** is greater than or equal to 40, not exactly equal to 40.
147 */
148 void rebuild_schema_update_2_0(void){
149 char *z = db_text(0, "SELECT sql FROM repository.sqlite_master WHERE name='blob'");
 
150 if( z ){
151 /* Search for: length(uuid)==40
152 ** 0123456789 12345 */
153 int i;
154 for(i=10; z[i]; i++){
@@ -357,19 +358,21 @@
357 ttyOutput = doOut;
358 processCnt = 0;
359 if (ttyOutput && !g.fQuiet) {
360 percent_complete(0);
361 }
 
362 rebuild_update_schema();
363 blob_init(&sql, 0, 0);
364 db_prepare(&q,
365 "SELECT name FROM sqlite_master /*scan*/"
366 " WHERE type='table'"
367 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
368 "'config','shun','private','reportfmt',"
369 "'concealed','accesslog','modreq',"
370 "'purgeevent','purgeitem','unversioned')"
 
371 " AND name NOT GLOB 'sqlite_*'"
372 " AND name NOT GLOB 'fx_*'"
373 );
374 while( db_step(&q)==SQLITE_ROW ){
375 blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
@@ -446,10 +449,11 @@
446 if( doClustering ) create_cluster();
447 if( ttyOutput && !g.fQuiet && totalSize>0 ){
448 processCnt += incrSize;
449 percent_complete((processCnt*1000)/totalSize);
450 }
 
451 if(!g.fQuiet && ttyOutput ){
452 percent_complete(1000);
453 fossil_print("\n");
454 }
455 return errCnt;
456
--- src/rebuild.c
+++ src/rebuild.c
@@ -144,11 +144,12 @@
144 ** Update the repository schema for Fossil version 2.0. (2017-02-28)
145 ** (1) Change the CHECK constraint on BLOB.UUID so that the length
146 ** is greater than or equal to 40, not exactly equal to 40.
147 */
148 void rebuild_schema_update_2_0(void){
149 char *z = db_text(0, "SELECT sql FROM repository.sqlite_master"
150 " WHERE name='blob'");
151 if( z ){
152 /* Search for: length(uuid)==40
153 ** 0123456789 12345 */
154 int i;
155 for(i=10; z[i]; i++){
@@ -357,19 +358,21 @@
358 ttyOutput = doOut;
359 processCnt = 0;
360 if (ttyOutput && !g.fQuiet) {
361 percent_complete(0);
362 }
363 email_triggers_disable();
364 rebuild_update_schema();
365 blob_init(&sql, 0, 0);
366 db_prepare(&q,
367 "SELECT name FROM sqlite_master /*scan*/"
368 " WHERE type='table'"
369 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
370 "'config','shun','private','reportfmt',"
371 "'concealed','accesslog','modreq',"
372 "'purgeevent','purgeitem','unversioned',"
373 "'subscriber','pending_alert','email_bounce')"
374 " AND name NOT GLOB 'sqlite_*'"
375 " AND name NOT GLOB 'fx_*'"
376 );
377 while( db_step(&q)==SQLITE_ROW ){
378 blob_appendf(&sql, "DROP TABLE IF EXISTS \"%w\";\n", db_column_text(&q,0));
@@ -446,10 +449,11 @@
449 if( doClustering ) create_cluster();
450 if( ttyOutput && !g.fQuiet && totalSize>0 ){
451 processCnt += incrSize;
452 percent_complete((processCnt*1000)/totalSize);
453 }
454 email_triggers_enable();
455 if(!g.fQuiet && ttyOutput ){
456 percent_complete(1000);
457 fossil_print("\n");
458 }
459 return errCnt;
460

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button