Fossil SCM

Add the "jx" column to the "user" and "reportfmt" tables, with the intent of using the column for JSON metadata. Currently unused. Make arrangements to sync the "jx" column (using the "fossil config" command) in a way that is backwards compatible with older versions that do not have the "jx" column.

drh 2022-11-17 23:38 trunk
Commit b7ac178c4f27d2e01225b0c9f245684279a25ad71063b0c97c0e9b943028aec7
Parent 8dcee008378a64a…
2 files changed +61 -28 +4 -2
~ src/configure.c ~ src/schema.c
M src/configure.c
+61 -28
--- src/configure.c
+++ src/configure.c
@@ -357,11 +357,11 @@
357357
 ** NAME CONTENT
358358
 ** ------- -----------------------------------------------------------
359359
 ** /config $MTIME $NAME value $VALUE
360360
 ** /user $MTIME $LOGIN pw $VALUE cap $VALUE info $VALUE photo $VALUE
361361
 ** /shun $MTIME $UUID scom $VALUE
362
-** /reportfmt $MTIME $TITLE owner $VALUE cols $VALUE sqlcode $VALUE
362
+** /reportfmt $MTIME $TITLE owner $VALUE cols $VALUE sqlcode $VALUE jx $JSON
363363
 ** /concealed $MTIME $HASH content $VALUE
364364
 ** /subscriber $SMTIME $SEMAIL suname $V ...
365365
 */
366366
 void configure_receive(const char *zName, Blob *pContent, int groupMask){
367367
 int checkMask; /* Masks for which we must first check existance of tables */
@@ -378,17 +378,17 @@
378378
 const char *zName; /* Configuration key for this table */
379379
 const char *zPrimKey; /* Primary key column */
380380
 int nField; /* Number of data fields */
381381
 const char *azField[6]; /* Names of the data fields */
382382
 } aType[] = {
383
- { "/config", "name", 1, { "value", 0,0,0,0,0 } },
384
- { "@user", "login", 4, { "pw","cap","info","photo",0,0} },
385
- { "@shun", "uuid", 1, { "scom", 0,0,0,0,0} },
386
- { "@reportfmt", "title", 3, { "owner","cols","sqlcode",0,0,0}},
387
- { "@concealed", "hash", 1, { "content", 0,0,0,0,0 } },
383
+ { "/config", "name", 1, { "value", 0,0,0,0,0 } },
384
+ { "@user", "login", 5, { "pw","cap","info","photo","jx",0} },
385
+ { "@shun", "uuid", 1, { "scom", 0,0,0,0,0} },
386
+ { "@reportfmt", "title", 4, { "owner","cols","sqlcode","jx",0,0}},
387
+ { "@concealed", "hash", 1, { "content", 0,0,0,0,0 } },
388388
 { "@subscriber","semail",6,
389
- { "suname","sdigest","sdonotcall","ssub","sctime","smip"} },
389
+ { "suname","sdigest","sdonotcall","ssub","sctime","smip"} },
390390
 };
391391
392392
 /* Locate the receiveType in aType[ii] */
393393
 for(ii=0; ii<count(aType); ii++){
394394
 if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
@@ -445,11 +445,25 @@
445445
 azToken[1] /*safe-for-%s*/, azToken[0]/*safe-for-%s*/);
446446
 for(jj=2; jj<nToken; jj+=2){
447447
 blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
448448
 }
449449
 db_protect_only(PROTECT_SENSITIVE);
450
+
451
+ /* Make sure tables have the "jx" column */
452
+ if( strcmp(&zName[1],"user")==0
453
+ && db_table_has_column("repository","user","jx")==0
454
+ ){
455
+ db_multi_exec("ALTER TABLE repository.user ADD COLUMN jx TEXT");
456
+ }else
457
+ if( strcmp(&zName[1],"reportfmt")==0
458
+ && db_table_has_column("repository","reportfmt","jx")==0
459
+ ){
460
+ db_multi_exec("ALTER TABLE repository.reportfmt ADD COLUMN jx TEXT");
461
+ }
462
+
450463
 db_multi_exec("%s)", blob_sql_text(&sql));
464
+
451465
 if( db_changes()==0 ){
452466
 blob_reset(&sql);
453467
 blob_append_sql(&sql, "UPDATE \"%w\" SET mtime=%s",
454468
 &zName[1], azToken[0]/*safe-for-%s*/);
455469
 for(jj=2; jj<nToken; jj+=2){
@@ -532,41 +546,60 @@
532546
 blob_reset(&rec);
533547
 }
534548
 db_finalize(&q);
535549
 }
536550
 if( groupMask & CONFIGSET_USER ){
537
- db_prepare(&q, "SELECT mtime, quote(login), quote(pw), quote(cap),"
538
- " quote(info), quote(photo) FROM user"
539
- " WHERE mtime>=%lld", iStart);
551
+ if( db_table_has_column("repository","user","jx") ){
552
+ db_prepare(&q, "SELECT mtime, quote(login), quote(pw), quote(cap),"
553
+ " quote(info), quote(photo), quote(jx) FROM user"
554
+ " WHERE mtime>=%lld", iStart);
555
+ }else{
556
+ db_prepare(&q, "SELECT mtime, quote(login), quote(pw), quote(cap),"
557
+ " quote(info), quote(photo), 'NULL' FROM user"
558
+ " WHERE mtime>=%lld", iStart);
559
+ }
540560
 while( db_step(&q)==SQLITE_ROW ){
541
- blob_appendf(&rec,"%s %s pw %s cap %s info %s photo %s",
542
- db_column_text(&q, 0),
543
- db_column_text(&q, 1),
544
- db_column_text(&q, 2),
545
- db_column_text(&q, 3),
546
- db_column_text(&q, 4),
547
- db_column_text(&q, 5)
548
- );
561
+ const char *z;
562
+ blob_appendf(&rec,"%s %s", db_column_text(&q,0), db_column_text(&q,1));
563
+ z = db_column_text(&q,2);
564
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," pw %s", z);
565
+ z = db_column_text(&q,3);
566
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," cap %s", z);
567
+ z = db_column_text(&q,4);
568
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," info %s", z);
569
+ z = db_column_text(&q,5);
570
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," photo %s", z);
571
+ z = db_column_text(&q,6);
572
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," jx %s", z);
549573
 blob_appendf(pOut, "config /user %d\n%s\n",
550574
 blob_size(&rec), blob_str(&rec));
551575
 nCard++;
552576
 blob_reset(&rec);
553577
 }
554578
 db_finalize(&q);
555579
 }
556580
 if( groupMask & CONFIGSET_TKT ){
557
- db_prepare(&q, "SELECT mtime, quote(title), quote(owner), quote(cols),"
558
- " quote(sqlcode) FROM reportfmt"
559
- " WHERE mtime>=%lld", iStart);
581
+ if( db_table_has_column("repository","reportfmt","jx") ){
582
+ db_prepare(&q, "SELECT mtime, quote(title), quote(owner), quote(cols),"
583
+ " quote(sqlcode), quote(jx) FROM reportfmt"
584
+ " WHERE mtime>=%lld", iStart);
585
+ }else{
586
+ db_prepare(&q, "SELECT mtime, quote(title), quote(owner), quote(cols),"
587
+ " quote(sqlcode), 'NULL' FROM reportfmt"
588
+ " WHERE mtime>=%lld", iStart);
589
+ }
560590
 while( db_step(&q)==SQLITE_ROW ){
561
- blob_appendf(&rec,"%s %s owner %s cols %s sqlcode %s",
562
- db_column_text(&q, 0),
563
- db_column_text(&q, 1),
564
- db_column_text(&q, 2),
565
- db_column_text(&q, 3),
566
- db_column_text(&q, 4)
567
- );
591
+ const char *z;
592
+ blob_appendf(&rec,"%s %s", db_column_text(&q,0), db_column_text(&q,1));
593
+ z = db_column_text(&q,2);
594
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," owner %s", z);
595
+ z = db_column_text(&q,3);
596
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," cols %s", z);
597
+ z = db_column_text(&q,4);
598
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," sqlcode %s", z);
599
+ z = db_column_text(&q,5);
600
+ if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," jx %s", z);
568601
 blob_appendf(pOut, "config /reportfmt %d\n%s\n",
569602
 blob_size(&rec), blob_str(&rec));
570603
 nCard++;
571604
 blob_reset(&rec);
572605
 }
573606
--- src/configure.c
+++ src/configure.c
@@ -357,11 +357,11 @@
357 ** NAME CONTENT
358 ** ------- -----------------------------------------------------------
359 ** /config $MTIME $NAME value $VALUE
360 ** /user $MTIME $LOGIN pw $VALUE cap $VALUE info $VALUE photo $VALUE
361 ** /shun $MTIME $UUID scom $VALUE
362 ** /reportfmt $MTIME $TITLE owner $VALUE cols $VALUE sqlcode $VALUE
363 ** /concealed $MTIME $HASH content $VALUE
364 ** /subscriber $SMTIME $SEMAIL suname $V ...
365 */
366 void configure_receive(const char *zName, Blob *pContent, int groupMask){
367 int checkMask; /* Masks for which we must first check existance of tables */
@@ -378,17 +378,17 @@
378 const char *zName; /* Configuration key for this table */
379 const char *zPrimKey; /* Primary key column */
380 int nField; /* Number of data fields */
381 const char *azField[6]; /* Names of the data fields */
382 } aType[] = {
383 { "/config", "name", 1, { "value", 0,0,0,0,0 } },
384 { "@user", "login", 4, { "pw","cap","info","photo",0,0} },
385 { "@shun", "uuid", 1, { "scom", 0,0,0,0,0} },
386 { "@reportfmt", "title", 3, { "owner","cols","sqlcode",0,0,0}},
387 { "@concealed", "hash", 1, { "content", 0,0,0,0,0 } },
388 { "@subscriber","semail",6,
389 { "suname","sdigest","sdonotcall","ssub","sctime","smip"} },
390 };
391
392 /* Locate the receiveType in aType[ii] */
393 for(ii=0; ii<count(aType); ii++){
394 if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
@@ -445,11 +445,25 @@
445 azToken[1] /*safe-for-%s*/, azToken[0]/*safe-for-%s*/);
446 for(jj=2; jj<nToken; jj+=2){
447 blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
448 }
449 db_protect_only(PROTECT_SENSITIVE);
 
 
 
 
 
 
 
 
 
 
 
 
 
450 db_multi_exec("%s)", blob_sql_text(&sql));
 
451 if( db_changes()==0 ){
452 blob_reset(&sql);
453 blob_append_sql(&sql, "UPDATE \"%w\" SET mtime=%s",
454 &zName[1], azToken[0]/*safe-for-%s*/);
455 for(jj=2; jj<nToken; jj+=2){
@@ -532,41 +546,60 @@
532 blob_reset(&rec);
533 }
534 db_finalize(&q);
535 }
536 if( groupMask & CONFIGSET_USER ){
537 db_prepare(&q, "SELECT mtime, quote(login), quote(pw), quote(cap),"
538 " quote(info), quote(photo) FROM user"
539 " WHERE mtime>=%lld", iStart);
 
 
 
 
 
 
540 while( db_step(&q)==SQLITE_ROW ){
541 blob_appendf(&rec,"%s %s pw %s cap %s info %s photo %s",
542 db_column_text(&q, 0),
543 db_column_text(&q, 1),
544 db_column_text(&q, 2),
545 db_column_text(&q, 3),
546 db_column_text(&q, 4),
547 db_column_text(&q, 5)
548 );
 
 
 
 
549 blob_appendf(pOut, "config /user %d\n%s\n",
550 blob_size(&rec), blob_str(&rec));
551 nCard++;
552 blob_reset(&rec);
553 }
554 db_finalize(&q);
555 }
556 if( groupMask & CONFIGSET_TKT ){
557 db_prepare(&q, "SELECT mtime, quote(title), quote(owner), quote(cols),"
558 " quote(sqlcode) FROM reportfmt"
559 " WHERE mtime>=%lld", iStart);
 
 
 
 
 
 
560 while( db_step(&q)==SQLITE_ROW ){
561 blob_appendf(&rec,"%s %s owner %s cols %s sqlcode %s",
562 db_column_text(&q, 0),
563 db_column_text(&q, 1),
564 db_column_text(&q, 2),
565 db_column_text(&q, 3),
566 db_column_text(&q, 4)
567 );
 
 
 
568 blob_appendf(pOut, "config /reportfmt %d\n%s\n",
569 blob_size(&rec), blob_str(&rec));
570 nCard++;
571 blob_reset(&rec);
572 }
573
--- src/configure.c
+++ src/configure.c
@@ -357,11 +357,11 @@
357 ** NAME CONTENT
358 ** ------- -----------------------------------------------------------
359 ** /config $MTIME $NAME value $VALUE
360 ** /user $MTIME $LOGIN pw $VALUE cap $VALUE info $VALUE photo $VALUE
361 ** /shun $MTIME $UUID scom $VALUE
362 ** /reportfmt $MTIME $TITLE owner $VALUE cols $VALUE sqlcode $VALUE jx $JSON
363 ** /concealed $MTIME $HASH content $VALUE
364 ** /subscriber $SMTIME $SEMAIL suname $V ...
365 */
366 void configure_receive(const char *zName, Blob *pContent, int groupMask){
367 int checkMask; /* Masks for which we must first check existance of tables */
@@ -378,17 +378,17 @@
378 const char *zName; /* Configuration key for this table */
379 const char *zPrimKey; /* Primary key column */
380 int nField; /* Number of data fields */
381 const char *azField[6]; /* Names of the data fields */
382 } aType[] = {
383 { "/config", "name", 1, { "value", 0,0,0,0,0 } },
384 { "@user", "login", 5, { "pw","cap","info","photo","jx",0} },
385 { "@shun", "uuid", 1, { "scom", 0,0,0,0,0} },
386 { "@reportfmt", "title", 4, { "owner","cols","sqlcode","jx",0,0}},
387 { "@concealed", "hash", 1, { "content", 0,0,0,0,0 } },
388 { "@subscriber","semail",6,
389 { "suname","sdigest","sdonotcall","ssub","sctime","smip"} },
390 };
391
392 /* Locate the receiveType in aType[ii] */
393 for(ii=0; ii<count(aType); ii++){
394 if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
@@ -445,11 +445,25 @@
445 azToken[1] /*safe-for-%s*/, azToken[0]/*safe-for-%s*/);
446 for(jj=2; jj<nToken; jj+=2){
447 blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
448 }
449 db_protect_only(PROTECT_SENSITIVE);
450
451 /* Make sure tables have the "jx" column */
452 if( strcmp(&zName[1],"user")==0
453 && db_table_has_column("repository","user","jx")==0
454 ){
455 db_multi_exec("ALTER TABLE repository.user ADD COLUMN jx TEXT");
456 }else
457 if( strcmp(&zName[1],"reportfmt")==0
458 && db_table_has_column("repository","reportfmt","jx")==0
459 ){
460 db_multi_exec("ALTER TABLE repository.reportfmt ADD COLUMN jx TEXT");
461 }
462
463 db_multi_exec("%s)", blob_sql_text(&sql));
464
465 if( db_changes()==0 ){
466 blob_reset(&sql);
467 blob_append_sql(&sql, "UPDATE \"%w\" SET mtime=%s",
468 &zName[1], azToken[0]/*safe-for-%s*/);
469 for(jj=2; jj<nToken; jj+=2){
@@ -532,41 +546,60 @@
546 blob_reset(&rec);
547 }
548 db_finalize(&q);
549 }
550 if( groupMask & CONFIGSET_USER ){
551 if( db_table_has_column("repository","user","jx") ){
552 db_prepare(&q, "SELECT mtime, quote(login), quote(pw), quote(cap),"
553 " quote(info), quote(photo), quote(jx) FROM user"
554 " WHERE mtime>=%lld", iStart);
555 }else{
556 db_prepare(&q, "SELECT mtime, quote(login), quote(pw), quote(cap),"
557 " quote(info), quote(photo), 'NULL' FROM user"
558 " WHERE mtime>=%lld", iStart);
559 }
560 while( db_step(&q)==SQLITE_ROW ){
561 const char *z;
562 blob_appendf(&rec,"%s %s", db_column_text(&q,0), db_column_text(&q,1));
563 z = db_column_text(&q,2);
564 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," pw %s", z);
565 z = db_column_text(&q,3);
566 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," cap %s", z);
567 z = db_column_text(&q,4);
568 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," info %s", z);
569 z = db_column_text(&q,5);
570 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," photo %s", z);
571 z = db_column_text(&q,6);
572 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," jx %s", z);
573 blob_appendf(pOut, "config /user %d\n%s\n",
574 blob_size(&rec), blob_str(&rec));
575 nCard++;
576 blob_reset(&rec);
577 }
578 db_finalize(&q);
579 }
580 if( groupMask & CONFIGSET_TKT ){
581 if( db_table_has_column("repository","reportfmt","jx") ){
582 db_prepare(&q, "SELECT mtime, quote(title), quote(owner), quote(cols),"
583 " quote(sqlcode), quote(jx) FROM reportfmt"
584 " WHERE mtime>=%lld", iStart);
585 }else{
586 db_prepare(&q, "SELECT mtime, quote(title), quote(owner), quote(cols),"
587 " quote(sqlcode), 'NULL' FROM reportfmt"
588 " WHERE mtime>=%lld", iStart);
589 }
590 while( db_step(&q)==SQLITE_ROW ){
591 const char *z;
592 blob_appendf(&rec,"%s %s", db_column_text(&q,0), db_column_text(&q,1));
593 z = db_column_text(&q,2);
594 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," owner %s", z);
595 z = db_column_text(&q,3);
596 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," cols %s", z);
597 z = db_column_text(&q,4);
598 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," sqlcode %s", z);
599 z = db_column_text(&q,5);
600 if( strcmp(z,"NULL")!=0 ) blob_appendf(&rec," jx %s", z);
601 blob_appendf(pOut, "config /reportfmt %d\n%s\n",
602 blob_size(&rec), blob_str(&rec));
603 nCard++;
604 blob_reset(&rec);
605 }
606
M src/schema.c
+4 -2
--- src/schema.c
+++ src/schema.c
@@ -126,11 +126,12 @@
126126
 @ cookie TEXT, -- WWW login cookie
127127
 @ ipaddr TEXT, -- IP address for which cookie is valid
128128
 @ cexpire DATETIME, -- Time when cookie expires
129129
 @ info TEXT, -- contact information
130130
 @ mtime DATE, -- last change. seconds since 1970
131
-@ photo BLOB -- JPEG image of this user
131
+@ photo BLOB, -- JPEG image of this user
132
+@ jx TEXT -- Extra fields in JSON
132133
 @ );
133134
 @
134135
 @ -- The config table holds miscellanous information about the repository.
135136
 @ -- in the form of name-value pairs.
136137
 @ --
@@ -176,11 +177,12 @@
176177
 @ rn INTEGER PRIMARY KEY, -- Report number
177178
 @ owner TEXT, -- Owner of this report format (not used)
178179
 @ title TEXT UNIQUE, -- Title of this report
179180
 @ mtime DATE, -- Last modified. seconds since 1970
180181
 @ cols TEXT, -- A color-key specification
181
-@ sqlcode TEXT -- An SQL SELECT statement for this report
182
+@ sqlcode TEXT, -- An SQL SELECT statement for this report
183
+@ jx TEXT -- Additional fields encoded as JSON
182184
 @ );
183185
 @
184186
 @ -- Some ticket content (such as the originators email address or contact
185187
 @ -- information) needs to be obscured to protect privacy. This is achieved
186188
 @ -- by storing an SHA1 hash of the content. For display, the hash is
187189
--- src/schema.c
+++ src/schema.c
@@ -126,11 +126,12 @@
126 @ cookie TEXT, -- WWW login cookie
127 @ ipaddr TEXT, -- IP address for which cookie is valid
128 @ cexpire DATETIME, -- Time when cookie expires
129 @ info TEXT, -- contact information
130 @ mtime DATE, -- last change. seconds since 1970
131 @ photo BLOB -- JPEG image of this user
 
132 @ );
133 @
134 @ -- The config table holds miscellanous information about the repository.
135 @ -- in the form of name-value pairs.
136 @ --
@@ -176,11 +177,12 @@
176 @ rn INTEGER PRIMARY KEY, -- Report number
177 @ owner TEXT, -- Owner of this report format (not used)
178 @ title TEXT UNIQUE, -- Title of this report
179 @ mtime DATE, -- Last modified. seconds since 1970
180 @ cols TEXT, -- A color-key specification
181 @ sqlcode TEXT -- An SQL SELECT statement for this report
 
182 @ );
183 @
184 @ -- Some ticket content (such as the originators email address or contact
185 @ -- information) needs to be obscured to protect privacy. This is achieved
186 @ -- by storing an SHA1 hash of the content. For display, the hash is
187
--- src/schema.c
+++ src/schema.c
@@ -126,11 +126,12 @@
126 @ cookie TEXT, -- WWW login cookie
127 @ ipaddr TEXT, -- IP address for which cookie is valid
128 @ cexpire DATETIME, -- Time when cookie expires
129 @ info TEXT, -- contact information
130 @ mtime DATE, -- last change. seconds since 1970
131 @ photo BLOB, -- JPEG image of this user
132 @ jx TEXT -- Extra fields in JSON
133 @ );
134 @
135 @ -- The config table holds miscellanous information about the repository.
136 @ -- in the form of name-value pairs.
137 @ --
@@ -176,11 +177,12 @@
177 @ rn INTEGER PRIMARY KEY, -- Report number
178 @ owner TEXT, -- Owner of this report format (not used)
179 @ title TEXT UNIQUE, -- Title of this report
180 @ mtime DATE, -- Last modified. seconds since 1970
181 @ cols TEXT, -- A color-key specification
182 @ sqlcode TEXT, -- An SQL SELECT statement for this report
183 @ jx TEXT -- Additional fields encoded as JSON
184 @ );
185 @
186 @ -- Some ticket content (such as the originators email address or contact
187 @ -- information) needs to be obscured to protect privacy. This is achieved
188 @ -- by storing an SHA1 hash of the content. For display, the hash is
189

Keyboard Shortcuts

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