Fossil SCM

Change the schema of the subscriber table to make it compatible with the "fossil config sync" mechanism. Upgrading through this check-in requires running "fossil email reset" to rebuild the email notification schema, and losing subscriber information.

drh 2018-06-25 18:13 trunk
Commit 9040de46ec2605442c10390d27c9a32bdcac579a33e7c5597983be34d8db863f
Parent 69d332ff96668aa…
2 files changed +32 -38 +29 -30
~ src/configure.c ~ src/email.c
M src/configure.c
+32 -38
--- src/configure.c
+++ src/configure.c
@@ -353,38 +353,36 @@
353353
 int checkMask; /* Masks for which we must first check existance of tables */
354354
355355
 checkMask = CONFIGSET_ALERT;
356356
 if( zName[0]=='/' ){
357357
 /* The new format */
358
- char *azToken[20];
358
+ char *azToken[24];
359359
 int nToken = 0;
360360
 int ii, jj;
361361
 int thisMask;
362362
 Blob name, value, sql;
363363
 static const struct receiveType {
364364
 const char *zName; /* Configuration key for this table */
365365
 const char *zPrimKey; /* Primary key column */
366
- const char *zMTime; /* Column holding the mtime */
367366
 int nField; /* Number of data fields */
368
- const char *azField[5]; /* Names of the data fields */
369
- const char *zExtraFields; /* Extra field names */
370
- const char *zExtraVals; /* Values for the extra fields */
367
+ const char *azField[6]; /* Names of the data fields */
371368
 } aType[] = {
372
- { "/config", "name", "mtime", 1, { "value", 0, 0, 0 }, 0, 0 },
373
- { "@user", "login", "mtime", 4, { "pw","cap","info","photo"},0,0 },
374
- { "@shun", "uuid", "mtime", 1, { "scom", 0, 0, 0 },0,0 },
375
- { "@reportfmt", "title", "mtime", 3, { "owner","cols","sqlcode",0},0,0 },
376
- { "@concealed", "hash", "mtime", 1, { "content", 0, 0, 0 },0,0 },
377
- { "@subscriber","semail","smtime",5,
378
- { "suname","sdigest","sdonotcall","ssub","sctime"},
379
- "subscriberCode,sverified",
380
- "randomblob(32),1" },
369
+ { "/config", "name", 1, { "value", 0,0,0,0,0 } },
370
+ { "@user", "login", 4, { "pw","cap","info","photo",0,0} },
371
+ { "@shun", "uuid", 1, { "scom", 0,0,0,0,0} },
372
+ { "@reportfmt", "title", 3, { "owner","cols","sqlcode",0,0,0}},
373
+ { "@concealed", "hash", 1, { "content", 0,0,0,0,0 } },
374
+ { "@subscriber","semail",6,
375
+ { "suname","sdigest","sdonotcall","ssub","sctime","smip"} },
381376
 };
377
+
378
+ /* Locate the receiveType in aType[ii] */
382379
 for(ii=0; ii<count(aType); ii++){
383380
 if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
384381
 }
385382
 if( ii>=count(aType) ) return;
383
+
386384
 while( blob_token(pContent, &name) && blob_sqltoken(pContent, &value) ){
387385
 char *z = blob_terminate(&name);
388386
 if( !safeSql(z) ) return;
389387
 if( nToken>0 ){
390388
 for(jj=0; jj<aType[ii].nField; jj++){
@@ -395,11 +393,11 @@
395393
 if( !safeInt(z) ) return;
396394
 }
397395
 azToken[nToken++] = z;
398396
 azToken[nToken++] = z = blob_terminate(&value);
399397
 if( !safeSql(z) ) return;
400
- if( nToken>=count(azToken) ) break;
398
+ if( nToken>=count(azToken)-1 ) break;
401399
 }
402400
 if( nToken<2 ) return;
403401
 if( aType[ii].zName[0]=='/' ){
404402
 thisMask = configure_is_exportable(azToken[1]);
405403
 }else{
@@ -410,11 +408,10 @@
410408
 if( (thisMask & CONFIGSET_ALERT)!=0 ){
411409
 email_schema(1);
412410
 }
413411
 checkMask &= ~thisMask;
414412
 }
415
-
416413
417414
 blob_zero(&sql);
418415
 if( groupMask & CONFIGSET_OVERWRITE ){
419416
 if( (thisMask & configHasBeenReset)==0 && aType[ii].zName[0]!='/' ){
420417
 db_multi_exec("DELETE FROM \"%w\"", &aType[ii].zName[1]);
@@ -422,38 +419,32 @@
422419
 }
423420
 blob_append_sql(&sql, "REPLACE INTO ");
424421
 }else{
425422
 blob_append_sql(&sql, "INSERT OR IGNORE INTO ");
426423
 }
427
- blob_append_sql(&sql, "\"%w\"(\"%w\", \"%w\"",
428
- &zName[1], aType[ii].zPrimKey, aType[ii].zMTime);
424
+ blob_append_sql(&sql, "\"%w\"(\"%w\",mtime",
425
+ &zName[1], aType[ii].zPrimKey);
429426
 for(jj=2; jj<nToken; jj+=2){
430427
 blob_append_sql(&sql, ",\"%w\"", azToken[jj]);
431428
 }
432
- if( aType[ii].zExtraFields ){
433
- blob_append_sql(&sql,",%s", aType[ii].zExtraFields/*safe-for-%s*/);
434
- }
435
- blob_append_sql(&sql,") VALUES(%s,%s",
436
- azToken[1] /*safe-for-%s*/, azToken[0] /*safe-for-%s*/);
429
+ blob_append_sql(&sql,") VALUES(%s",
430
+ azToken[1] /*safe-for-%s*/);
437431
 for(jj=2; jj<nToken; jj+=2){
438432
 blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
439433
 }
440
- if( aType[ii].zExtraVals ){
441
- blob_append_sql(&sql,",%s", aType[ii].zExtraVals/*safe-for-%s*/);
442
- }
443434
 db_multi_exec("%s)", blob_sql_text(&sql));
444435
 if( db_changes()==0 ){
445436
 blob_reset(&sql);
446
- blob_append_sql(&sql, "UPDATE \"%w\" SET \"%w\"=%s",
447
- &zName[1], aType[ii].zMTime, azToken[0]/*safe-for-%s*/);
437
+ blob_append_sql(&sql, "UPDATE \"%w\" SET mtime=%s",
438
+ &zName[1], azToken[0]/*safe-for-%s*/);
448439
 for(jj=2; jj<nToken; jj+=2){
449440
 blob_append_sql(&sql, ", \"%w\"=%s",
450441
 azToken[jj], azToken[jj+1]/*safe-for-%s*/);
451442
 }
452
- blob_append_sql(&sql, " WHERE \"%w\"=%s AND \"%w\"<%s",
443
+ blob_append_sql(&sql, " WHERE \"%w\"=%s AND mtime<%s",
453444
 aType[ii].zPrimKey, azToken[1]/*safe-for-%s*/,
454
- aType[ii].zMTime, azToken[0]/*safe-for-%s*/);
445
+ azToken[0]/*safe-for-%s*/);
455446
 db_multi_exec("%s", blob_sql_text(&sql));
456447
 }
457448
 blob_reset(&sql);
458449
 rebuildMask |= thisMask;
459450
 }
@@ -597,25 +588,28 @@
597588
 db_finalize(&q);
598589
 }
599590
 if( (groupMask & CONFIGSET_ALERT)!=0
600591
 && db_table_exists("repository","subscriber")
601592
 ){
602
- db_prepare(&q, "SELECT (smtime-2440587.5)*86400,"
603
- " quote(semail), quote(suname), quote(sdigest),"
604
- " quote(sdonotcall), quote(ssub), quote(sctime)"
593
+ db_prepare(&q, "SELECT mtime, quote(semail),"
594
+ " quote(suname), quote(sdigest),"
595
+ " quote(sdonotcall), quote(ssub),"
596
+ " quote(sctime), quote(smip),"
605597
 " FROM subscriber WHERE sverified"
606
- " AND (smtime-2440587.5)*86400>=%lld", iStart);
598
+ " AND mtime>=%lld", iStart);
607599
 while( db_step(&q)==SQLITE_ROW ){
608600
 blob_appendf(&rec,
609
- "%lld %s suname %s sdigest %s sdonotcall %s ssub %s sctime %s",
610
- db_column_int64(&q, 0), /* smtime */
601
+ "%lld %s suname %s sdigest %s sdonotcall %s ssub %s"
602
+ " sctime %s smip %s",
603
+ db_column_int64(&q, 0), /* mtime */
611604
 db_column_text(&q, 1), /* semail (PK) */
612605
 db_column_text(&q, 2), /* suname */
613606
 db_column_text(&q, 3), /* sdigest */
614607
 db_column_text(&q, 4), /* sdonotcall */
615608
 db_column_text(&q, 5), /* ssub */
616
- db_column_text(&q, 6) /* sctime */
609
+ db_column_text(&q, 6), /* sctime */
610
+ db_column_text(&q, 7) /* smip */
617611
 );
618612
 blob_appendf(pOut, "config /subscriber %d\n%s\n",
619613
 blob_size(&rec), blob_str(&rec));
620614
 nCard++;
621615
 blob_reset(&rec);
@@ -838,11 +832,11 @@
838832
 db_create_default_users(0, 0);
839833
 }else if( fossil_strcmp(zName,"@concealed")==0 ){
840834
 db_multi_exec("DELETE FROM concealed");
841835
 }else if( fossil_strcmp(zName,"@shun")==0 ){
842836
 db_multi_exec("DELETE FROM shun");
843
- }else if( fossil_strcmp(zName,"@alert")==0 ){
837
+ }else if( fossil_strcmp(zName,"@subscriber")==0 ){
844838
 if( db_table_exists("repository","subscriber") ){
845839
 db_multi_exec("DELETE FROM subscriber");
846840
 }
847841
 }else if( fossil_strcmp(zName,"@forum")==0 ){
848842
 if( db_table_exists("repository","forumpost") ){
849843
--- src/configure.c
+++ src/configure.c
@@ -353,38 +353,36 @@
353 int checkMask; /* Masks for which we must first check existance of tables */
354
355 checkMask = CONFIGSET_ALERT;
356 if( zName[0]=='/' ){
357 /* The new format */
358 char *azToken[20];
359 int nToken = 0;
360 int ii, jj;
361 int thisMask;
362 Blob name, value, sql;
363 static const struct receiveType {
364 const char *zName; /* Configuration key for this table */
365 const char *zPrimKey; /* Primary key column */
366 const char *zMTime; /* Column holding the mtime */
367 int nField; /* Number of data fields */
368 const char *azField[5]; /* Names of the data fields */
369 const char *zExtraFields; /* Extra field names */
370 const char *zExtraVals; /* Values for the extra fields */
371 } aType[] = {
372 { "/config", "name", "mtime", 1, { "value", 0, 0, 0 }, 0, 0 },
373 { "@user", "login", "mtime", 4, { "pw","cap","info","photo"},0,0 },
374 { "@shun", "uuid", "mtime", 1, { "scom", 0, 0, 0 },0,0 },
375 { "@reportfmt", "title", "mtime", 3, { "owner","cols","sqlcode",0},0,0 },
376 { "@concealed", "hash", "mtime", 1, { "content", 0, 0, 0 },0,0 },
377 { "@subscriber","semail","smtime",5,
378 { "suname","sdigest","sdonotcall","ssub","sctime"},
379 "subscriberCode,sverified",
380 "randomblob(32),1" },
381 };
 
 
382 for(ii=0; ii<count(aType); ii++){
383 if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
384 }
385 if( ii>=count(aType) ) return;
 
386 while( blob_token(pContent, &name) && blob_sqltoken(pContent, &value) ){
387 char *z = blob_terminate(&name);
388 if( !safeSql(z) ) return;
389 if( nToken>0 ){
390 for(jj=0; jj<aType[ii].nField; jj++){
@@ -395,11 +393,11 @@
395 if( !safeInt(z) ) return;
396 }
397 azToken[nToken++] = z;
398 azToken[nToken++] = z = blob_terminate(&value);
399 if( !safeSql(z) ) return;
400 if( nToken>=count(azToken) ) break;
401 }
402 if( nToken<2 ) return;
403 if( aType[ii].zName[0]=='/' ){
404 thisMask = configure_is_exportable(azToken[1]);
405 }else{
@@ -410,11 +408,10 @@
410 if( (thisMask & CONFIGSET_ALERT)!=0 ){
411 email_schema(1);
412 }
413 checkMask &= ~thisMask;
414 }
415
416
417 blob_zero(&sql);
418 if( groupMask & CONFIGSET_OVERWRITE ){
419 if( (thisMask & configHasBeenReset)==0 && aType[ii].zName[0]!='/' ){
420 db_multi_exec("DELETE FROM \"%w\"", &aType[ii].zName[1]);
@@ -422,38 +419,32 @@
422 }
423 blob_append_sql(&sql, "REPLACE INTO ");
424 }else{
425 blob_append_sql(&sql, "INSERT OR IGNORE INTO ");
426 }
427 blob_append_sql(&sql, "\"%w\"(\"%w\", \"%w\"",
428 &zName[1], aType[ii].zPrimKey, aType[ii].zMTime);
429 for(jj=2; jj<nToken; jj+=2){
430 blob_append_sql(&sql, ",\"%w\"", azToken[jj]);
431 }
432 if( aType[ii].zExtraFields ){
433 blob_append_sql(&sql,",%s", aType[ii].zExtraFields/*safe-for-%s*/);
434 }
435 blob_append_sql(&sql,") VALUES(%s,%s",
436 azToken[1] /*safe-for-%s*/, azToken[0] /*safe-for-%s*/);
437 for(jj=2; jj<nToken; jj+=2){
438 blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
439 }
440 if( aType[ii].zExtraVals ){
441 blob_append_sql(&sql,",%s", aType[ii].zExtraVals/*safe-for-%s*/);
442 }
443 db_multi_exec("%s)", blob_sql_text(&sql));
444 if( db_changes()==0 ){
445 blob_reset(&sql);
446 blob_append_sql(&sql, "UPDATE \"%w\" SET \"%w\"=%s",
447 &zName[1], aType[ii].zMTime, azToken[0]/*safe-for-%s*/);
448 for(jj=2; jj<nToken; jj+=2){
449 blob_append_sql(&sql, ", \"%w\"=%s",
450 azToken[jj], azToken[jj+1]/*safe-for-%s*/);
451 }
452 blob_append_sql(&sql, " WHERE \"%w\"=%s AND \"%w\"<%s",
453 aType[ii].zPrimKey, azToken[1]/*safe-for-%s*/,
454 aType[ii].zMTime, azToken[0]/*safe-for-%s*/);
455 db_multi_exec("%s", blob_sql_text(&sql));
456 }
457 blob_reset(&sql);
458 rebuildMask |= thisMask;
459 }
@@ -597,25 +588,28 @@
597 db_finalize(&q);
598 }
599 if( (groupMask & CONFIGSET_ALERT)!=0
600 && db_table_exists("repository","subscriber")
601 ){
602 db_prepare(&q, "SELECT (smtime-2440587.5)*86400,"
603 " quote(semail), quote(suname), quote(sdigest),"
604 " quote(sdonotcall), quote(ssub), quote(sctime)"
 
605 " FROM subscriber WHERE sverified"
606 " AND (smtime-2440587.5)*86400>=%lld", iStart);
607 while( db_step(&q)==SQLITE_ROW ){
608 blob_appendf(&rec,
609 "%lld %s suname %s sdigest %s sdonotcall %s ssub %s sctime %s",
610 db_column_int64(&q, 0), /* smtime */
 
611 db_column_text(&q, 1), /* semail (PK) */
612 db_column_text(&q, 2), /* suname */
613 db_column_text(&q, 3), /* sdigest */
614 db_column_text(&q, 4), /* sdonotcall */
615 db_column_text(&q, 5), /* ssub */
616 db_column_text(&q, 6) /* sctime */
 
617 );
618 blob_appendf(pOut, "config /subscriber %d\n%s\n",
619 blob_size(&rec), blob_str(&rec));
620 nCard++;
621 blob_reset(&rec);
@@ -838,11 +832,11 @@
838 db_create_default_users(0, 0);
839 }else if( fossil_strcmp(zName,"@concealed")==0 ){
840 db_multi_exec("DELETE FROM concealed");
841 }else if( fossil_strcmp(zName,"@shun")==0 ){
842 db_multi_exec("DELETE FROM shun");
843 }else if( fossil_strcmp(zName,"@alert")==0 ){
844 if( db_table_exists("repository","subscriber") ){
845 db_multi_exec("DELETE FROM subscriber");
846 }
847 }else if( fossil_strcmp(zName,"@forum")==0 ){
848 if( db_table_exists("repository","forumpost") ){
849
--- src/configure.c
+++ src/configure.c
@@ -353,38 +353,36 @@
353 int checkMask; /* Masks for which we must first check existance of tables */
354
355 checkMask = CONFIGSET_ALERT;
356 if( zName[0]=='/' ){
357 /* The new format */
358 char *azToken[24];
359 int nToken = 0;
360 int ii, jj;
361 int thisMask;
362 Blob name, value, sql;
363 static const struct receiveType {
364 const char *zName; /* Configuration key for this table */
365 const char *zPrimKey; /* Primary key column */
 
366 int nField; /* Number of data fields */
367 const char *azField[6]; /* Names of the data fields */
 
 
368 } aType[] = {
369 { "/config", "name", 1, { "value", 0,0,0,0,0 } },
370 { "@user", "login", 4, { "pw","cap","info","photo",0,0} },
371 { "@shun", "uuid", 1, { "scom", 0,0,0,0,0} },
372 { "@reportfmt", "title", 3, { "owner","cols","sqlcode",0,0,0}},
373 { "@concealed", "hash", 1, { "content", 0,0,0,0,0 } },
374 { "@subscriber","semail",6,
375 { "suname","sdigest","sdonotcall","ssub","sctime","smip"} },
 
 
376 };
377
378 /* Locate the receiveType in aType[ii] */
379 for(ii=0; ii<count(aType); ii++){
380 if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
381 }
382 if( ii>=count(aType) ) return;
383
384 while( blob_token(pContent, &name) && blob_sqltoken(pContent, &value) ){
385 char *z = blob_terminate(&name);
386 if( !safeSql(z) ) return;
387 if( nToken>0 ){
388 for(jj=0; jj<aType[ii].nField; jj++){
@@ -395,11 +393,11 @@
393 if( !safeInt(z) ) return;
394 }
395 azToken[nToken++] = z;
396 azToken[nToken++] = z = blob_terminate(&value);
397 if( !safeSql(z) ) return;
398 if( nToken>=count(azToken)-1 ) break;
399 }
400 if( nToken<2 ) return;
401 if( aType[ii].zName[0]=='/' ){
402 thisMask = configure_is_exportable(azToken[1]);
403 }else{
@@ -410,11 +408,10 @@
408 if( (thisMask & CONFIGSET_ALERT)!=0 ){
409 email_schema(1);
410 }
411 checkMask &= ~thisMask;
412 }
 
413
414 blob_zero(&sql);
415 if( groupMask & CONFIGSET_OVERWRITE ){
416 if( (thisMask & configHasBeenReset)==0 && aType[ii].zName[0]!='/' ){
417 db_multi_exec("DELETE FROM \"%w\"", &aType[ii].zName[1]);
@@ -422,38 +419,32 @@
419 }
420 blob_append_sql(&sql, "REPLACE INTO ");
421 }else{
422 blob_append_sql(&sql, "INSERT OR IGNORE INTO ");
423 }
424 blob_append_sql(&sql, "\"%w\"(\"%w\",mtime",
425 &zName[1], aType[ii].zPrimKey);
426 for(jj=2; jj<nToken; jj+=2){
427 blob_append_sql(&sql, ",\"%w\"", azToken[jj]);
428 }
429 blob_append_sql(&sql,") VALUES(%s",
430 azToken[1] /*safe-for-%s*/);
 
 
 
431 for(jj=2; jj<nToken; jj+=2){
432 blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
433 }
 
 
 
434 db_multi_exec("%s)", blob_sql_text(&sql));
435 if( db_changes()==0 ){
436 blob_reset(&sql);
437 blob_append_sql(&sql, "UPDATE \"%w\" SET mtime=%s",
438 &zName[1], azToken[0]/*safe-for-%s*/);
439 for(jj=2; jj<nToken; jj+=2){
440 blob_append_sql(&sql, ", \"%w\"=%s",
441 azToken[jj], azToken[jj+1]/*safe-for-%s*/);
442 }
443 blob_append_sql(&sql, " WHERE \"%w\"=%s AND mtime<%s",
444 aType[ii].zPrimKey, azToken[1]/*safe-for-%s*/,
445 azToken[0]/*safe-for-%s*/);
446 db_multi_exec("%s", blob_sql_text(&sql));
447 }
448 blob_reset(&sql);
449 rebuildMask |= thisMask;
450 }
@@ -597,25 +588,28 @@
588 db_finalize(&q);
589 }
590 if( (groupMask & CONFIGSET_ALERT)!=0
591 && db_table_exists("repository","subscriber")
592 ){
593 db_prepare(&q, "SELECT mtime, quote(semail),"
594 " quote(suname), quote(sdigest),"
595 " quote(sdonotcall), quote(ssub),"
596 " quote(sctime), quote(smip),"
597 " FROM subscriber WHERE sverified"
598 " AND mtime>=%lld", iStart);
599 while( db_step(&q)==SQLITE_ROW ){
600 blob_appendf(&rec,
601 "%lld %s suname %s sdigest %s sdonotcall %s ssub %s"
602 " sctime %s smip %s",
603 db_column_int64(&q, 0), /* mtime */
604 db_column_text(&q, 1), /* semail (PK) */
605 db_column_text(&q, 2), /* suname */
606 db_column_text(&q, 3), /* sdigest */
607 db_column_text(&q, 4), /* sdonotcall */
608 db_column_text(&q, 5), /* ssub */
609 db_column_text(&q, 6), /* sctime */
610 db_column_text(&q, 7) /* smip */
611 );
612 blob_appendf(pOut, "config /subscriber %d\n%s\n",
613 blob_size(&rec), blob_str(&rec));
614 nCard++;
615 blob_reset(&rec);
@@ -838,11 +832,11 @@
832 db_create_default_users(0, 0);
833 }else if( fossil_strcmp(zName,"@concealed")==0 ){
834 db_multi_exec("DELETE FROM concealed");
835 }else if( fossil_strcmp(zName,"@shun")==0 ){
836 db_multi_exec("DELETE FROM shun");
837 }else if( fossil_strcmp(zName,"@subscriber")==0 ){
838 if( db_table_exists("repository","subscriber") ){
839 db_multi_exec("DELETE FROM subscriber");
840 }
841 }else if( fossil_strcmp(zName,"@forum")==0 ){
842 if( db_table_exists("repository","forumpost") ){
843
M src/email.c
+29 -30
--- src/email.c
+++ src/email.c
@@ -48,19 +48,19 @@
4848
 @ -- we might also add a separate table that allows subscribing to email
4949
 @ -- notifications for specific branches or tags or tickets.
5050
 @ --
5151
 @ CREATE TABLE repository.subscriber(
5252
 @ subscriberId INTEGER PRIMARY KEY, -- numeric subscriber ID. Internal use
53
-@ subscriberCode BLOB UNIQUE, -- UUID for subscriber. External use
53
+@ subscriberCode BLOB DEFAULT (randomblob(32)) UNIQUE, -- UUID for subscriber
5454
 @ semail TEXT UNIQUE COLLATE nocase,-- email address
5555
 @ suname TEXT, -- corresponding USER entry
56
-@ sverified BOOLEAN, -- email address verified
56
+@ sverified BOOLEAN DEFAULT true, -- email address verified
5757
 @ sdonotcall BOOLEAN, -- true for Do Not Call
5858
 @ sdigest BOOLEAN, -- true for daily digests only
5959
 @ ssub TEXT, -- baseline subscriptions
60
-@ sctime DATE, -- When this entry was created. JulianDay
61
-@ smtime DATE, -- Last change. JulianDay
60
+@ sctime INTDATE, -- When this entry was created. unixtime
61
+@ mtime INTDATE, -- Last change. unixtime
6262
 @ smip TEXT -- IP address of last change
6363
 @ );
6464
 @ CREATE INDEX repository.subscriberUname
6565
 @ ON subscriber(suname) WHERE suname IS NOT NULL;
6666
 @
@@ -920,14 +920,13 @@
920920
 if( PB("sc") ) ssub[nsub++] = 'c';
921921
 if( PB("st") ) ssub[nsub++] = 't';
922922
 if( PB("sw") ) ssub[nsub++] = 'w';
923923
 ssub[nsub] = 0;
924924
 db_multi_exec(
925
- "INSERT INTO subscriber(subscriberCode,semail,suname,"
926
- " sverified,sdonotcall,sdigest,ssub,sctime,smtime,smip)"
927
- "VALUES(randomblob(32),%Q,%Q,%d,0,%d,%Q,"
928
- " julianday('now'),julianday('now'),%Q)",
925
+ "INSERT INTO subscriber(semail,suname,"
926
+ " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
927
+ "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)",
929928
 /* semail */ zEAddr,
930929
 /* suname */ suname,
931930
 /* sverified */ needCaptcha==0,
932931
 /* sdigest */ PB("di"),
933932
 /* ssub */ ssub,
@@ -1095,11 +1094,11 @@
10951094
 int sdigest, sdonotcall, sverified;
10961095
 const char *ssub;
10971096
 const char *semail;
10981097
 const char *smip;
10991098
 const char *suname;
1100
- const char *smtime;
1099
+ const char *mtime;
11011100
 const char *sctime;
11021101
 int eErr = 0;
11031102
 char *zErr = 0;
11041103
11051104
 if( email_webpages_disabled() ) return;
@@ -1130,11 +1129,11 @@
11301129
 db_multi_exec(
11311130
 "UPDATE subscriber SET"
11321131
 " sdonotcall=%d,"
11331132
 " sdigest=%d,"
11341133
 " ssub=%Q,"
1135
- " smtime=julianday('now'),"
1134
+ " mtime=strftime('%%s','now'),"
11361135
 " smip=%Q,"
11371136
 " suname=%Q,"
11381137
 " sverified=%d"
11391138
 " WHERE subscriberCode=hextoblob(%Q)",
11401139
 sdonotcall,
@@ -1149,11 +1148,11 @@
11491148
 db_multi_exec(
11501149
 "UPDATE subscriber SET"
11511150
 " sdonotcall=%d,"
11521151
 " sdigest=%d,"
11531152
 " ssub=%Q,"
1154
- " smtime=julianday('now'),"
1153
+ " mtime=strftime('%%s','now'),"
11551154
 " smip=%Q"
11561155
 " WHERE subscriberCode=hextoblob(%Q)",
11571156
 sdonotcall,
11581157
 sdigest,
11591158
 ssub,
@@ -1172,19 +1171,19 @@
11721171
 return;
11731172
 }
11741173
 }
11751174
 db_prepare(&q,
11761175
 "SELECT"
1177
- " semail," /* 0 */
1178
- " sverified," /* 1 */
1179
- " sdonotcall," /* 2 */
1180
- " sdigest," /* 3 */
1181
- " ssub," /* 4 */
1182
- " smip," /* 5 */
1183
- " suname," /* 6 */
1184
- " datetime(smtime)," /* 7 */
1185
- " datetime(sctime)" /* 8 */
1176
+ " semail," /* 0 */
1177
+ " sverified," /* 1 */
1178
+ " sdonotcall," /* 2 */
1179
+ " sdigest," /* 3 */
1180
+ " ssub," /* 4 */
1181
+ " smip," /* 5 */
1182
+ " suname," /* 6 */
1183
+ " datetime(mtime,'unixepoch')," /* 7 */
1184
+ " datetime(sctime,'unixepoch')" /* 8 */
11861185
 " FROM subscriber WHERE subscriberCode=hextoblob(%Q)", zName);
11871186
 if( db_step(&q)!=SQLITE_ROW ){
11881187
 db_finalize(&q);
11891188
 cgi_redirect("subscribe");
11901189
 return;
@@ -1199,11 +1198,11 @@
11991198
 sc = strchr(ssub,'c')!=0;
12001199
 st = strchr(ssub,'t')!=0;
12011200
 sw = strchr(ssub,'w')!=0;
12021201
 smip = db_column_text(&q, 5);
12031202
 suname = db_column_text(&q, 6);
1204
- smtime = db_column_text(&q, 7);
1203
+ mtime = db_column_text(&q, 7);
12051204
 sctime = db_column_text(&q, 8);
12061205
 if( !g.perm.Admin && !sverified ){
12071206
 db_multi_exec(
12081207
 "UPDATE subscriber SET sverified=1 WHERE subscriberCode=hextoblob(%Q)",
12091208
 zName);
@@ -1227,11 +1226,11 @@
12271226
 @ <td class='form_label'>Created:</td>
12281227
 @ <td>%h(sctime)</td>
12291228
 @ </tr>
12301229
 @ <tr>
12311230
 @ <td class='form_label'>Last Modified:</td>
1232
- @ <td>%h(smtime)</td>
1231
+ @ <td>%h(mtime)</td>
12331232
 @ </tr>
12341233
 @ <tr>
12351234
 @ <td class='form_label'>IP Address:</td>
12361235
 @ <td>%h(smip)</td>
12371236
 @ </tr>
@@ -1453,18 +1452,18 @@
14531452
 }
14541453
 email_submenu_common();
14551454
 style_header("Subscriber List");
14561455
 blob_init(&sql, 0, 0);
14571456
 blob_append_sql(&sql,
1458
- "SELECT hex(subscriberCode)," /* 0 */
1459
- " semail," /* 1 */
1460
- " ssub," /* 2 */
1461
- " suname," /* 3 */
1462
- " sverified," /* 4 */
1463
- " sdigest," /* 5 */
1464
- " date(sctime)," /* 6 */
1465
- " smtime" /* 7 */
1457
+ "SELECT hex(subscriberCode)," /* 0 */
1458
+ " semail," /* 1 */
1459
+ " ssub," /* 2 */
1460
+ " suname," /* 3 */
1461
+ " sverified," /* 4 */
1462
+ " sdigest," /* 5 */
1463
+ " date(sctime,'unixepoch')," /* 6 */
1464
+ " julianday(mtime,'unixepoch')" /* 7 */
14661465
 " FROM subscriber"
14671466
 );
14681467
 db_prepare_blob(&q, &sql);
14691468
 rNow = db_double(0.0,"SELECT julianday('now')");
14701469
 @ <table border="1">
14711470
--- src/email.c
+++ src/email.c
@@ -48,19 +48,19 @@
48 @ -- we might also add a separate table that allows subscribing to email
49 @ -- notifications for specific branches or tags or tickets.
50 @ --
51 @ CREATE TABLE repository.subscriber(
52 @ subscriberId INTEGER PRIMARY KEY, -- numeric subscriber ID. Internal use
53 @ subscriberCode BLOB UNIQUE, -- UUID for subscriber. External use
54 @ semail TEXT UNIQUE COLLATE nocase,-- email address
55 @ suname TEXT, -- corresponding USER entry
56 @ sverified BOOLEAN, -- email address verified
57 @ sdonotcall BOOLEAN, -- true for Do Not Call
58 @ sdigest BOOLEAN, -- true for daily digests only
59 @ ssub TEXT, -- baseline subscriptions
60 @ sctime DATE, -- When this entry was created. JulianDay
61 @ smtime DATE, -- Last change. JulianDay
62 @ smip TEXT -- IP address of last change
63 @ );
64 @ CREATE INDEX repository.subscriberUname
65 @ ON subscriber(suname) WHERE suname IS NOT NULL;
66 @
@@ -920,14 +920,13 @@
920 if( PB("sc") ) ssub[nsub++] = 'c';
921 if( PB("st") ) ssub[nsub++] = 't';
922 if( PB("sw") ) ssub[nsub++] = 'w';
923 ssub[nsub] = 0;
924 db_multi_exec(
925 "INSERT INTO subscriber(subscriberCode,semail,suname,"
926 " sverified,sdonotcall,sdigest,ssub,sctime,smtime,smip)"
927 "VALUES(randomblob(32),%Q,%Q,%d,0,%d,%Q,"
928 " julianday('now'),julianday('now'),%Q)",
929 /* semail */ zEAddr,
930 /* suname */ suname,
931 /* sverified */ needCaptcha==0,
932 /* sdigest */ PB("di"),
933 /* ssub */ ssub,
@@ -1095,11 +1094,11 @@
1095 int sdigest, sdonotcall, sverified;
1096 const char *ssub;
1097 const char *semail;
1098 const char *smip;
1099 const char *suname;
1100 const char *smtime;
1101 const char *sctime;
1102 int eErr = 0;
1103 char *zErr = 0;
1104
1105 if( email_webpages_disabled() ) return;
@@ -1130,11 +1129,11 @@
1130 db_multi_exec(
1131 "UPDATE subscriber SET"
1132 " sdonotcall=%d,"
1133 " sdigest=%d,"
1134 " ssub=%Q,"
1135 " smtime=julianday('now'),"
1136 " smip=%Q,"
1137 " suname=%Q,"
1138 " sverified=%d"
1139 " WHERE subscriberCode=hextoblob(%Q)",
1140 sdonotcall,
@@ -1149,11 +1148,11 @@
1149 db_multi_exec(
1150 "UPDATE subscriber SET"
1151 " sdonotcall=%d,"
1152 " sdigest=%d,"
1153 " ssub=%Q,"
1154 " smtime=julianday('now'),"
1155 " smip=%Q"
1156 " WHERE subscriberCode=hextoblob(%Q)",
1157 sdonotcall,
1158 sdigest,
1159 ssub,
@@ -1172,19 +1171,19 @@
1172 return;
1173 }
1174 }
1175 db_prepare(&q,
1176 "SELECT"
1177 " semail," /* 0 */
1178 " sverified," /* 1 */
1179 " sdonotcall," /* 2 */
1180 " sdigest," /* 3 */
1181 " ssub," /* 4 */
1182 " smip," /* 5 */
1183 " suname," /* 6 */
1184 " datetime(smtime)," /* 7 */
1185 " datetime(sctime)" /* 8 */
1186 " FROM subscriber WHERE subscriberCode=hextoblob(%Q)", zName);
1187 if( db_step(&q)!=SQLITE_ROW ){
1188 db_finalize(&q);
1189 cgi_redirect("subscribe");
1190 return;
@@ -1199,11 +1198,11 @@
1199 sc = strchr(ssub,'c')!=0;
1200 st = strchr(ssub,'t')!=0;
1201 sw = strchr(ssub,'w')!=0;
1202 smip = db_column_text(&q, 5);
1203 suname = db_column_text(&q, 6);
1204 smtime = db_column_text(&q, 7);
1205 sctime = db_column_text(&q, 8);
1206 if( !g.perm.Admin && !sverified ){
1207 db_multi_exec(
1208 "UPDATE subscriber SET sverified=1 WHERE subscriberCode=hextoblob(%Q)",
1209 zName);
@@ -1227,11 +1226,11 @@
1227 @ <td class='form_label'>Created:</td>
1228 @ <td>%h(sctime)</td>
1229 @ </tr>
1230 @ <tr>
1231 @ <td class='form_label'>Last Modified:</td>
1232 @ <td>%h(smtime)</td>
1233 @ </tr>
1234 @ <tr>
1235 @ <td class='form_label'>IP Address:</td>
1236 @ <td>%h(smip)</td>
1237 @ </tr>
@@ -1453,18 +1452,18 @@
1453 }
1454 email_submenu_common();
1455 style_header("Subscriber List");
1456 blob_init(&sql, 0, 0);
1457 blob_append_sql(&sql,
1458 "SELECT hex(subscriberCode)," /* 0 */
1459 " semail," /* 1 */
1460 " ssub," /* 2 */
1461 " suname," /* 3 */
1462 " sverified," /* 4 */
1463 " sdigest," /* 5 */
1464 " date(sctime)," /* 6 */
1465 " smtime" /* 7 */
1466 " FROM subscriber"
1467 );
1468 db_prepare_blob(&q, &sql);
1469 rNow = db_double(0.0,"SELECT julianday('now')");
1470 @ <table border="1">
1471
--- src/email.c
+++ src/email.c
@@ -48,19 +48,19 @@
48 @ -- we might also add a separate table that allows subscribing to email
49 @ -- notifications for specific branches or tags or tickets.
50 @ --
51 @ CREATE TABLE repository.subscriber(
52 @ subscriberId INTEGER PRIMARY KEY, -- numeric subscriber ID. Internal use
53 @ subscriberCode BLOB DEFAULT (randomblob(32)) UNIQUE, -- UUID for subscriber
54 @ semail TEXT UNIQUE COLLATE nocase,-- email address
55 @ suname TEXT, -- corresponding USER entry
56 @ sverified BOOLEAN DEFAULT true, -- email address verified
57 @ sdonotcall BOOLEAN, -- true for Do Not Call
58 @ sdigest BOOLEAN, -- true for daily digests only
59 @ ssub TEXT, -- baseline subscriptions
60 @ sctime INTDATE, -- When this entry was created. unixtime
61 @ mtime INTDATE, -- Last change. unixtime
62 @ smip TEXT -- IP address of last change
63 @ );
64 @ CREATE INDEX repository.subscriberUname
65 @ ON subscriber(suname) WHERE suname IS NOT NULL;
66 @
@@ -920,14 +920,13 @@
920 if( PB("sc") ) ssub[nsub++] = 'c';
921 if( PB("st") ) ssub[nsub++] = 't';
922 if( PB("sw") ) ssub[nsub++] = 'w';
923 ssub[nsub] = 0;
924 db_multi_exec(
925 "INSERT INTO subscriber(semail,suname,"
926 " sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
927 "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)",
 
928 /* semail */ zEAddr,
929 /* suname */ suname,
930 /* sverified */ needCaptcha==0,
931 /* sdigest */ PB("di"),
932 /* ssub */ ssub,
@@ -1095,11 +1094,11 @@
1094 int sdigest, sdonotcall, sverified;
1095 const char *ssub;
1096 const char *semail;
1097 const char *smip;
1098 const char *suname;
1099 const char *mtime;
1100 const char *sctime;
1101 int eErr = 0;
1102 char *zErr = 0;
1103
1104 if( email_webpages_disabled() ) return;
@@ -1130,11 +1129,11 @@
1129 db_multi_exec(
1130 "UPDATE subscriber SET"
1131 " sdonotcall=%d,"
1132 " sdigest=%d,"
1133 " ssub=%Q,"
1134 " mtime=strftime('%%s','now'),"
1135 " smip=%Q,"
1136 " suname=%Q,"
1137 " sverified=%d"
1138 " WHERE subscriberCode=hextoblob(%Q)",
1139 sdonotcall,
@@ -1149,11 +1148,11 @@
1148 db_multi_exec(
1149 "UPDATE subscriber SET"
1150 " sdonotcall=%d,"
1151 " sdigest=%d,"
1152 " ssub=%Q,"
1153 " mtime=strftime('%%s','now'),"
1154 " smip=%Q"
1155 " WHERE subscriberCode=hextoblob(%Q)",
1156 sdonotcall,
1157 sdigest,
1158 ssub,
@@ -1172,19 +1171,19 @@
1171 return;
1172 }
1173 }
1174 db_prepare(&q,
1175 "SELECT"
1176 " semail," /* 0 */
1177 " sverified," /* 1 */
1178 " sdonotcall," /* 2 */
1179 " sdigest," /* 3 */
1180 " ssub," /* 4 */
1181 " smip," /* 5 */
1182 " suname," /* 6 */
1183 " datetime(mtime,'unixepoch')," /* 7 */
1184 " datetime(sctime,'unixepoch')" /* 8 */
1185 " FROM subscriber WHERE subscriberCode=hextoblob(%Q)", zName);
1186 if( db_step(&q)!=SQLITE_ROW ){
1187 db_finalize(&q);
1188 cgi_redirect("subscribe");
1189 return;
@@ -1199,11 +1198,11 @@
1198 sc = strchr(ssub,'c')!=0;
1199 st = strchr(ssub,'t')!=0;
1200 sw = strchr(ssub,'w')!=0;
1201 smip = db_column_text(&q, 5);
1202 suname = db_column_text(&q, 6);
1203 mtime = db_column_text(&q, 7);
1204 sctime = db_column_text(&q, 8);
1205 if( !g.perm.Admin && !sverified ){
1206 db_multi_exec(
1207 "UPDATE subscriber SET sverified=1 WHERE subscriberCode=hextoblob(%Q)",
1208 zName);
@@ -1227,11 +1226,11 @@
1226 @ <td class='form_label'>Created:</td>
1227 @ <td>%h(sctime)</td>
1228 @ </tr>
1229 @ <tr>
1230 @ <td class='form_label'>Last Modified:</td>
1231 @ <td>%h(mtime)</td>
1232 @ </tr>
1233 @ <tr>
1234 @ <td class='form_label'>IP Address:</td>
1235 @ <td>%h(smip)</td>
1236 @ </tr>
@@ -1453,18 +1452,18 @@
1452 }
1453 email_submenu_common();
1454 style_header("Subscriber List");
1455 blob_init(&sql, 0, 0);
1456 blob_append_sql(&sql,
1457 "SELECT hex(subscriberCode)," /* 0 */
1458 " semail," /* 1 */
1459 " ssub," /* 2 */
1460 " suname," /* 3 */
1461 " sverified," /* 4 */
1462 " sdigest," /* 5 */
1463 " date(sctime,'unixepoch')," /* 6 */
1464 " julianday(mtime,'unixepoch')" /* 7 */
1465 " FROM subscriber"
1466 );
1467 db_prepare_blob(&q, &sql);
1468 rNow = db_double(0.0,"SELECT julianday('now')");
1469 @ <table border="1">
1470

Keyboard Shortcuts

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