Fossil SCM

fossil-scm / src / report.c
Blame History Raw 1404 lines
1
/*
2
** Copyright (c) 2007 D. Richard Hipp
3
**
4
** This program is free software; you can redistribute it and/or
5
** modify it under the terms of the Simplified BSD License (also
6
** known as the "2-Clause License" or "FreeBSD License".)
7
8
** This program is distributed in the hope that it will be useful,
9
** but without any warranty; without even the implied warranty of
10
** merchantability or fitness for a particular purpose.
11
**
12
** Author contact information:
13
** [email protected]
14
** http://www.hwaci.com/drh/
15
**
16
*******************************************************************************
17
**
18
** Code to generate the ticket listings
19
*/
20
#include "config.h"
21
#include <time.h>
22
#include "report.h"
23
#include <assert.h>
24
25
/* Forward references to static routines */
26
static void report_format_hints(void);
27
28
#ifndef SQLITE_RECURSIVE
29
# define SQLITE_RECURSIVE 33
30
#endif
31
32
/* Settings that can be used to control ticket reports */
33
/*
34
** SETTING: ticket-default-report width=80
35
** If this setting has a string value, then when the ticket
36
** search page query is blank, the report with this title is shown.
37
** If the setting is blank (default), then no report is shown.
38
*/
39
40
/*
41
** WEBPAGE: reportlist
42
**
43
** Main menu for Tickets.
44
*/
45
void view_list(void){
46
const char *zScript;
47
Blob ril; /* Report Item List */
48
Stmt q;
49
int rn = 0;
50
char *defaultReport = db_get("ticket-default-report", 0);
51
52
login_check_credentials();
53
if( !g.perm.RdTkt && !g.perm.NewTkt ){
54
login_needed(g.anon.RdTkt || g.anon.NewTkt);
55
return;
56
}
57
style_header("Ticket Main Menu");
58
ticket_standard_submenu(T_ALL_BUT(T_REPLIST));
59
if( g.thTrace ) Th_Trace("BEGIN_REPORTLIST<br>\n", -1);
60
zScript = ticket_reportlist_code();
61
if( g.thTrace ) Th_Trace("BEGIN_REPORTLIST_SCRIPT<br>\n", -1);
62
63
blob_zero(&ril);
64
ticket_init();
65
66
db_prepare(&q, "SELECT rn, title, owner FROM reportfmt ORDER BY title");
67
while( db_step(&q)==SQLITE_ROW ){
68
const char *zTitle = db_column_text(&q, 1);
69
const char *zOwner = db_column_text(&q, 2);
70
if( zTitle[0] =='_' && !g.perm.TktFmt ){
71
continue;
72
}
73
rn = db_column_int(&q, 0);
74
blob_appendf(&ril, "<li>");
75
if( zTitle[0] == '_' ){
76
blob_appendf(&ril, "%s", zTitle);
77
} else {
78
blob_appendf(&ril, "%z%h</a>", href("%R/rptview/%d", rn), zTitle);
79
}
80
blob_appendf(&ril, "&nbsp;&nbsp;&nbsp;");
81
if( g.perm.Write && zOwner && zOwner[0] ){
82
blob_appendf(&ril, "(by <i>%h</i>) ", zOwner);
83
}
84
if( g.perm.TktFmt ){
85
blob_appendf(&ril, "[%zcopy</a>] ",
86
href("%R/rptedit/%d?copy=1", rn));
87
}
88
if( g.perm.Admin
89
|| (g.perm.WrTkt && zOwner && fossil_strcmp(g.zLogin,zOwner)==0)
90
){
91
blob_appendf(&ril, "[%zedit</a>]",
92
href("%R/rptedit/%d", rn));
93
}
94
if( g.perm.TktFmt ){
95
blob_appendf(&ril, "[%zsql</a>]",
96
href("%R/rptsql/%d", rn));
97
}
98
if( fossil_strcmp(zTitle, defaultReport)==0 ){
99
blob_appendf(&ril, "&nbsp;← default");
100
}
101
blob_appendf(&ril, "</li>\n");
102
}
103
db_finalize(&q);
104
105
Th_Store("report_items", blob_str(&ril));
106
107
Th_Render(zScript);
108
109
blob_reset(&ril);
110
if( g.thTrace ) Th_Trace("END_REPORTLIST<br>\n", -1);
111
112
style_finish_page();
113
}
114
115
/*
116
** Remove whitespace from both ends of a string.
117
*/
118
char *trim_string(const char *zOrig){
119
int i;
120
while( fossil_isspace(*zOrig) ){ zOrig++; }
121
i = strlen(zOrig);
122
while( i>0 && fossil_isspace(zOrig[i-1]) ){ i--; }
123
return mprintf("%.*s", i, zOrig);
124
}
125
126
/*
127
** Extract a numeric (integer) value from a string.
128
*/
129
char *extract_integer(const char *zOrig){
130
if( zOrig == NULL || zOrig[0] == 0 ) return "";
131
while( *zOrig && !fossil_isdigit(*zOrig) ){ zOrig++; }
132
if( *zOrig ){
133
/* we have a digit. atoi() will get as much of the number as it
134
** can. We'll run it through mprintf() to get a string. Not
135
** an efficient way to do it, but effective.
136
*/
137
return mprintf("%d", atoi(zOrig));
138
}
139
return "";
140
}
141
142
/*
143
** Remove blank lines from the beginning of a string and
144
** all whitespace from the end. Removes whitespace preceding a LF,
145
** which also converts any CRLF sequence into a single LF.
146
*/
147
char *remove_blank_lines(const char *zOrig){
148
int i, j, n;
149
char *z;
150
for(i=j=0; fossil_isspace(zOrig[i]); i++){ if( zOrig[i]=='\n' ) j = i+1; }
151
n = strlen(&zOrig[j]);
152
while( n>0 && fossil_isspace(zOrig[j+n-1]) ){ n--; }
153
z = mprintf("%.*s", n, &zOrig[j]);
154
for(i=j=0; z[i]; i++){
155
if( z[i+1]=='\n' && z[i]!='\n' && fossil_isspace(z[i]) ){
156
z[j] = z[i];
157
while(fossil_isspace(z[j]) && z[j] != '\n' ){ j--; }
158
j++;
159
continue;
160
}
161
162
z[j++] = z[i];
163
}
164
z[j] = 0;
165
return z;
166
}
167
168
169
/*********************************************************************/
170
171
/*
172
** This is the SQLite authorizer callback used to make sure that the
173
** SQL statements entered by users do not try to do anything untoward.
174
** If anything suspicious is tried, set *(char**)pError to an error
175
** message obtained from malloc.
176
**
177
** Use the "fossil test-db-prepare --auth-report SQL" command to perform
178
** manual testing of this authorizer.
179
*/
180
static int report_query_authorizer(
181
void *pError,
182
int code,
183
const char *zArg1,
184
const char *zArg2,
185
const char *zArg3,
186
const char *zArg4
187
){
188
int rc = SQLITE_OK;
189
if( *(char**)pError ){
190
/* We've already seen an error. No need to continue. */
191
return SQLITE_DENY;
192
}
193
switch( code ){
194
case SQLITE_SELECT:
195
case SQLITE_RECURSIVE:
196
case SQLITE_FUNCTION: {
197
break;
198
}
199
case SQLITE_READ: {
200
static const char *const azAllowed[] = {
201
"backlink",
202
"blob",
203
"event",
204
"filename",
205
"json_each",
206
"json_tree",
207
"mlink",
208
"plink",
209
"tag",
210
"tagxref",
211
"ticket",
212
"ticketchng",
213
"unversioned",
214
};
215
int lwr = 0;
216
int upr = count(azAllowed) - 1;
217
int cmp = 0;
218
if( zArg1==0 ){
219
/* Some legacy versions of SQLite will sometimes send spurious
220
** READ authorizations that have no table name. These can be
221
** ignored. */
222
rc = SQLITE_IGNORE;
223
break;
224
}
225
while( lwr<=upr ){
226
int i = (lwr+upr)/2;
227
cmp = fossil_stricmp(zArg1, azAllowed[i]);
228
if( cmp<0 ){
229
upr = i - 1;
230
}else if( cmp>0 ){
231
lwr = i + 1;
232
}else{
233
break;
234
}
235
}
236
if( cmp ){
237
/* Always ok to access tables whose names begin with "fx_" */
238
cmp = sqlite3_strnicmp(zArg1, "fx_", 3);
239
}
240
if( cmp ){
241
*(char**)pError = mprintf("access to table \"%s\" is restricted",zArg1);
242
rc = SQLITE_DENY;
243
}else if( !g.perm.RdAddr && sqlite3_strnicmp(zArg2, "private_", 8)==0 ){
244
rc = SQLITE_IGNORE;
245
}
246
break;
247
}
248
default: {
249
*(char**)pError = mprintf("only SELECT statements are allowed");
250
rc = SQLITE_DENY;
251
break;
252
}
253
}
254
return rc;
255
}
256
257
/*
258
** Make sure the reportfmt table is up-to-date. It should contain
259
** the "jx" column (as of version 2.21). If it does not, add it.
260
**
261
** The "jx" column is intended to hold a JSON object containing optional
262
** key-value pairs.
263
*/
264
void report_update_reportfmt_table(void){
265
if( db_table_has_column("repository","reportfmt","jx")==0 ){
266
db_multi_exec("ALTER TABLE repository.reportfmt"
267
" ADD COLUMN jx TEXT DEFAULT '{}';");
268
}
269
}
270
271
/*
272
** Activate the ticket report query authorizer. Must be followed by an
273
** eventual call to report_unrestrict_sql().
274
*/
275
void report_restrict_sql(char **pzErr){
276
db_set_authorizer(report_query_authorizer,(void*)pzErr,"Ticket-Report");
277
sqlite3_limit(g.db, SQLITE_LIMIT_VDBE_OP, 10000);
278
}
279
void report_unrestrict_sql(void){
280
db_clear_authorizer();
281
}
282
283
284
/*
285
** Check the given SQL to see if is a valid query that does not
286
** attempt to do anything dangerous. Return 0 on success and a
287
** pointer to an error message string (obtained from malloc) if
288
** there is a problem.
289
*/
290
char *verify_sql_statement(char *zSql){
291
int i;
292
char *zErr = 0;
293
const char *zTail;
294
sqlite3_stmt *pStmt;
295
int rc;
296
297
/* First make sure the SQL is a single query command by verifying that
298
** the first token is "SELECT" or "WITH" and that there are no unquoted
299
** semicolons.
300
*/
301
for(i=0; fossil_isspace(zSql[i]); i++){}
302
if( fossil_strnicmp(&zSql[i], "select", 6)!=0
303
&& fossil_strnicmp(&zSql[i], "with", 4)!=0 ){
304
return mprintf("The SQL must be a SELECT or WITH statement");
305
}
306
for(i=0; zSql[i]; i++){
307
if( zSql[i]==';' ){
308
int bad;
309
int c = zSql[i+1];
310
zSql[i+1] = 0;
311
bad = sqlite3_complete(zSql);
312
zSql[i+1] = c;
313
if( bad ){
314
/* A complete statement basically means that an unquoted semi-colon
315
** was found. We don't actually check what's after that.
316
*/
317
return mprintf("Semi-colon detected! "
318
"Only a single SQL statement is allowed");
319
}
320
}
321
}
322
323
/* Compile the statement and check for illegal accesses or syntax errors. */
324
report_restrict_sql(&zErr);
325
rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, &zTail);
326
if( rc!=SQLITE_OK ){
327
zErr = mprintf("Syntax error: %s", sqlite3_errmsg(g.db));
328
}
329
if( !sqlite3_stmt_readonly(pStmt) ){
330
zErr = mprintf("SQL must not modify the database");
331
}
332
if( pStmt ){
333
sqlite3_finalize(pStmt);
334
}
335
report_unrestrict_sql();
336
return zErr;
337
}
338
339
/*
340
** Get a report number from query parameters. This can be done in various
341
** ways:
342
**
343
** (1) (legacy) rn=NNN where NNN is the reportfmt.rn integer primary key.
344
**
345
** (2) name=NNN where NNN is the rn.
346
**
347
** (3) name=TAG where TAG matches reportfmt.jx->>tag
348
**
349
** Regardless of how the report is specified, return the primary key, rn.
350
** Return 0 if not found.
351
*/
352
static int report_number(void){
353
int rn;
354
const char *zName;
355
char *zEnd;
356
357
/* Case (1) */
358
rn = atoi(PD("rn","0"));
359
if( rn>0 ) return rn;
360
361
zName = P("name");
362
if( zName==0 || zName[0]==0 ) return 0;
363
if( fossil_isdigit(zName[0])
364
&& (rn = strtol(zName, &zEnd, 10))>0
365
&& zEnd[0]==0
366
){
367
/* Case 2 */
368
return rn;
369
}
370
371
rn = db_int(0, "SELECT rn FROM reportfmt WHERE jx->>'tag'==%Q", zName);
372
return rn;
373
}
374
375
/*
376
** WEBPAGE: rptsql
377
** URL: /rptsql/N
378
**
379
** Display the SQL query used to generate a ticket report. The N value
380
** is either the report number of a report tag.
381
*/
382
void view_see_sql(void){
383
int rn;
384
const char *zTitle;
385
const char *zSQL;
386
const char *zOwner;
387
const char *zClrKey;
388
Stmt q;
389
390
login_check_credentials();
391
if( !g.perm.TktFmt ){
392
login_needed(g.anon.TktFmt);
393
return;
394
}
395
rn = report_number();
396
db_prepare(&q, "SELECT title, sqlcode, owner, cols "
397
"FROM reportfmt WHERE rn=%d",rn);
398
style_set_current_feature("report");
399
style_header("SQL For Report Format Number %d", rn);
400
if( db_step(&q)!=SQLITE_ROW ){
401
@ <p>Unknown report number: %d(rn)</p>
402
style_finish_page();
403
db_finalize(&q);
404
return;
405
}
406
zTitle = db_column_text(&q, 0);
407
zSQL = db_column_text(&q, 1);
408
zOwner = db_column_text(&q, 2);
409
zClrKey = db_column_text(&q, 3);
410
@ <table cellpadding=0 cellspacing=0 border=0>
411
@ <tr><td valign="top" align="right">Title:</td><td width=15></td>
412
@ <td colspan="3">%h(zTitle)</td></tr>
413
@ <tr><td valign="top" align="right">Owner:</td><td></td>
414
@ <td colspan="3">%h(zOwner)</td></tr>
415
@ <tr><td valign="top" align="right">SQL:</td><td></td>
416
@ <td valign="top"><pre>
417
@ <code class="language-sql">%h(zSQL)</code>
418
@ </pre></td>
419
@ <td width=15></td><td valign="top">
420
output_color_key(zClrKey, 0, "border=0 cellspacing=0 cellpadding=3");
421
@ </td>
422
@ </tr></table>
423
report_format_hints();
424
style_finish_page();
425
db_finalize(&q);
426
}
427
428
/*
429
** WEBPAGE: rptnew
430
** WEBPAGE: rptedit
431
**
432
** Create (/rptnew) or edit (/rptedit) a ticket report format.
433
** Query parameters:
434
**
435
** name=N Ticket report number or tag.
436
** rn=N Ticket report number (legacy).
437
** ^^^-- one of the two previous is required.
438
** t=TITLE Title of the report format
439
** w=USER Owner of the report format
440
** s=SQL SQL text used to implement the report
441
** k=KEY Color key
442
** d=DESC Optional descriptive text
443
** m=MIMETYPE Mimetype for DESC
444
** x=TAG Symbolic name for the report
445
*/
446
void view_edit(void){
447
int rn;
448
const char *zTitle; /* Title of the report */
449
const char *z;
450
const char *zOwner; /* Owner of the report */
451
const char *zClrKey; /* Color key - used to add colors to lines */
452
char *zSQL; /* The SQL text that generates the report */
453
char *zErr = 0; /* An error message */
454
const char *zDesc; /* Extra descriptive text about the report */
455
const char *zMimetype; /* Mimetype for zDesc */
456
const char *zTag; /* Symbolic name for this report */
457
int dflt = P("dflt") ? 1 : 0;
458
459
login_check_credentials();
460
if( !g.perm.TktFmt ){
461
login_needed(g.anon.TktFmt);
462
return;
463
}
464
style_set_current_feature("report");
465
/*view_add_functions(0);*/
466
rn = report_number();
467
zTitle = P("t");
468
zOwner = PD("w",g.zLogin);
469
z = P("s");
470
zSQL = z ? trim_string(z) : 0;
471
zClrKey = trim_string(PD("k",""));
472
zDesc = trim_string(PD("d",""));
473
zMimetype = P("m");
474
zTag = P("x");
475
report_update_reportfmt_table();
476
if( rn>0 && P("del2") && cgi_csrf_safe(2) ){
477
db_multi_exec("DELETE FROM reportfmt WHERE rn=%d", rn);
478
cgi_redirect("reportlist");
479
return;
480
}else if( rn>0 && P("del1") && cgi_csrf_safe(2) ){
481
zTitle = db_text(0, "SELECT title FROM reportfmt "
482
"WHERE rn=%d", rn);
483
if( zTitle==0 ) cgi_redirect("reportlist");
484
485
style_header("Are You Sure?");
486
@ <form action="rptedit" method="post">
487
@ <p>You are about to delete all traces of the report
488
@ <strong>%h(zTitle)</strong> from
489
@ the database. This is an irreversible operation. All records
490
@ related to this report will be removed and cannot be recovered.</p>
491
@
492
@ <input type="hidden" name="rn" value="%d(rn)">
493
login_insert_csrf_secret();
494
@ <input type="submit" name="del2" value="Delete The Report">
495
@ <input type="submit" name="can" value="Cancel">
496
@ </form>
497
style_finish_page();
498
return;
499
}else if( P("can") ){
500
/* user cancelled */
501
cgi_redirect("reportlist");
502
return;
503
}
504
if( zTitle && zSQL ){
505
if( zSQL[0]==0 ){
506
zErr = "Please supply an SQL query statement";
507
}else if( (zTitle = trim_string(zTitle))[0]==0 ){
508
zErr = "Please supply a title";
509
}else{
510
zErr = verify_sql_statement(zSQL);
511
}
512
if( zErr==0
513
&& db_exists("SELECT 1 FROM reportfmt WHERE title=%Q and rn<>%d",
514
zTitle, rn)
515
){
516
zErr = mprintf("There is already another report named \"%h\"", zTitle);
517
}
518
if( zErr==0 && cgi_csrf_safe(2) ){
519
if( zTag && zTag[0]==0 ) zTag = 0;
520
if( zDesc && zDesc[0]==0 ){ zDesc = 0; zMimetype = 0; }
521
if( zMimetype && zMimetype[0]==0 ){ zDesc = 0; zMimetype = 0; }
522
if( rn>0 ){
523
db_multi_exec(
524
"UPDATE reportfmt SET title=%Q, sqlcode=%Q,"
525
" owner=%Q, cols=%Q, mtime=now(), "
526
" jx=json_patch(jx,json_object('desc',%Q,'descmt',%Q,'tag',%Q))"
527
" WHERE rn=%d",
528
zTitle, zSQL, zOwner, zClrKey, zDesc, zMimetype, zTag, rn);
529
}else{
530
db_multi_exec(
531
"INSERT INTO reportfmt(title,sqlcode,owner,cols,mtime,jx) "
532
"VALUES(%Q,%Q,%Q,%Q,now(),"
533
"json_object('desc',%Q,'descmt',%Q,'tag',%Q))",
534
zTitle, zSQL, zOwner, zClrKey, zDesc, zMimetype, zTag);
535
rn = db_last_insert_rowid();
536
}
537
if( dflt ){
538
db_set("ticket-default-report", zTitle, 0);
539
}else{
540
char *defaultReport = db_get("ticket-default-report", 0);
541
if( fossil_strcmp(zTitle, defaultReport)==0 ){
542
db_set("ticket-default-report", "", 0);
543
}
544
}
545
cgi_redirect(mprintf("rptview/%d", rn));
546
return;
547
}
548
}else if( rn==0 ){
549
zTitle = "";
550
zSQL = ticket_report_template();
551
zClrKey = ticket_key_template();
552
}else{
553
Stmt q;
554
int hasJx = 0;
555
zDesc = 0;
556
zMimetype = 0;
557
zTag = 0;
558
db_prepare(&q, "SELECT title, sqlcode, owner, cols, json_valid(jx) "
559
"FROM reportfmt WHERE rn=%d",rn);
560
if( db_step(&q)==SQLITE_ROW ){
561
char *defaultReport = db_get("ticket-default-report", 0);
562
zTitle = db_column_malloc(&q, 0);
563
zSQL = db_column_malloc(&q, 1);
564
zOwner = db_column_malloc(&q, 2);
565
zClrKey = db_column_malloc(&q, 3);
566
dflt = fossil_strcmp(zTitle, defaultReport)==0;
567
hasJx = db_column_int(&q, 4);
568
}
569
db_finalize(&q);
570
if( hasJx ){
571
db_prepare(&q, "SELECT jx->>'desc', jx->>'descmt', jx->>'tag'"
572
" FROM reportfmt WHERE rn=%d", rn);
573
if( db_step(&q)==SQLITE_ROW ){
574
zDesc = db_column_malloc(&q, 0);
575
zMimetype = db_column_malloc(&q, 1);
576
zTag = db_column_malloc(&q, 2);
577
}
578
db_finalize(&q);
579
}
580
if( P("copy") ){
581
rn = 0;
582
zTitle = mprintf("Copy Of %s", zTitle);
583
zOwner = g.zLogin;
584
}
585
}
586
if( zOwner==0 ) zOwner = g.zLogin;
587
style_submenu_element("Cancel", "%R/reportlist");
588
style_header("%s", rn>0 ? "Edit Report Format":"Create New Report Format");
589
if( zErr ){
590
@ <blockquote class="reportError">%h(zErr)</blockquote>
591
}
592
@ <form action="rptedit" method="post"><div>
593
@ <input type="hidden" name="rn" value="%d(rn)">
594
@ <p>Report Title:<br>
595
@ <input type="text" name="t" value="%h(zTitle)" size="60"></p>
596
@ <p>Enter a complete SQL query statement against the "TICKET" table:<br>
597
@ <textarea name="s" rows="20" cols="80">%h(zSQL)</textarea>
598
@ </p>
599
login_insert_csrf_secret();
600
if( g.perm.Admin ){
601
@ <p>Report owner:
602
@ <input type="text" name="w" size="20" value="%h(zOwner)">
603
@ </p>
604
@ <p>Tag:
605
@ <input type="text" name="x" size="20" value="%h(zTag?zTag:"")">
606
@ </p>
607
} else {
608
@ <input type="hidden" name="w" value="%h(zOwner)">
609
if( zTag && zTag[0] ){
610
@ <input type="hidden" name="x" value="%h(zTag)">
611
}
612
}
613
@ <p>Enter an optional color key in the following box. (If blank, no
614
@ color key is displayed.) Each line contains the text for a single
615
@ entry in the key. The first token of each line is the background
616
@ color for that line.<br>
617
@ <textarea name="k" rows="8" cols="50">%h(zClrKey)</textarea>
618
@ </p>
619
620
@ <p>Optional human-readable description for this report<br>
621
@ %z(href("%R/markup_help"))Markup style</a>:
622
mimetype_option_menu(zMimetype, "m");
623
@ <br><textarea aria-label="Description:" name="d" class="wikiedit" \
624
@ cols="80" rows="15" wrap="virtual">%h(zDesc)</textarea>
625
@ </p>
626
627
@ <p><label><input type="checkbox" name="dflt" %s(dflt?"checked":"")> \
628
@ Make this the default report</label></p>
629
if( !g.perm.Admin && fossil_strcmp(zOwner,g.zLogin)!=0 ){
630
@ <p>This report format is owned by %h(zOwner). You are not allowed
631
@ to change it.</p>
632
@ </form>
633
report_format_hints();
634
style_finish_page();
635
return;
636
}
637
@ <input type="submit" value="Apply Changes">
638
if( rn>0 ){
639
@ <input type="submit" value="Delete This Report" name="del1">
640
}
641
@ </div></form>
642
report_format_hints();
643
style_finish_page();
644
}
645
646
/*
647
** Output a bunch of text that provides information about report
648
** formats
649
*/
650
static void report_format_hints(void){
651
char *zSchema;
652
zSchema = db_text(0,"SELECT sql FROM sqlite_schema WHERE name='ticket'");
653
if( zSchema==0 ){
654
zSchema = db_text(0,"SELECT sql FROM repository.sqlite_schema"
655
" WHERE name='ticket'");
656
}
657
@ <hr><h3>TICKET Schema</h3>
658
@ <blockquote><pre>
659
@ <code class="language-sql">%h(zSchema)</code>
660
@ </pre></blockquote>
661
@ <h3>Notes</h3>
662
@ <ul>
663
@ <li><p>The SQL must consist of a single SELECT statement</p></li>
664
@
665
@ <li><p>If a column of the result set is named "#" then that column
666
@ is assumed to hold a ticket number. A hyperlink will be created from
667
@ that column to a detailed view of the ticket.</p></li>
668
@
669
@ <li><p>If a column of the result set is named "bgcolor" then the content
670
@ of that column determines the background color of the row.</p></li>
671
@
672
@ <li><p>The text of all columns prior to the first column whose name begins
673
@ with underscore ("_") is shown character-for-character as it appears in
674
@ the database. In other words, it is assumed to have a mimetype of
675
@ text/plain.
676
@
677
@ <li><p>The first column whose name begins with underscore ("_") and all
678
@ subsequent columns are shown on their own rows in the table and with
679
@ wiki formatting. In other words, such rows are shown with a mimetype
680
@ of text/x-fossil-wiki. This is recommended for the "description" field
681
@ of tickets.
682
@ </p></li>
683
@
684
@ <li><p>The query can join other tables in the database besides TICKET.
685
@ </p></li>
686
@ </ul>
687
@
688
@ <h3>Examples</h3>
689
@ <p>In this example, the first column in the result set is named
690
@ "bgcolor". The value of this column is not displayed. Instead, it
691
@ selects the background color of each row based on the TICKET.STATUS
692
@ field of the database. The color key at the right shows the various
693
@ color codes.</p>
694
@ <table class="rpteditex">
695
@ <tr style="background-color:#f2dcdc;"><td class="rpteditex">new or active</td></tr>
696
@ <tr style="background-color:#e8e8bd;"><td class="rpteditex">review</td></tr>
697
@ <tr style="background-color:#cfe8bd;"><td class="rpteditex">fixed</td></tr>
698
@ <tr style="background-color:#bde5d6;"><td class="rpteditex">tested</td></tr>
699
@ <tr style="background-color:#cacae5;"><td class="rpteditex">defer</td></tr>
700
@ <tr style="background-color:#c8c8c8;"><td class="rpteditex">closed</td></tr>
701
@ </table>
702
@ <blockquote><pre>
703
@ SELECT
704
@ CASE WHEN status IN ('new','active') THEN '#f2dcdc'
705
@ WHEN status='review' THEN '#e8e8bd'
706
@ WHEN status='fixed' THEN '#cfe8bd'
707
@ WHEN status='tested' THEN '#bde5d6'
708
@ WHEN status='defer' THEN '#cacae5'
709
@ ELSE '#c8c8c8' END as 'bgcolor',
710
@ tn AS '#',
711
@ type AS 'Type',
712
@ status AS 'Status',
713
@ sdate(origtime) AS 'Created',
714
@ owner AS 'By',
715
@ subsystem AS 'Subsys',
716
@ sdate(changetime) AS 'Changed',
717
@ assignedto AS 'Assigned',
718
@ severity AS 'Svr',
719
@ priority AS 'Pri',
720
@ title AS 'Title'
721
@ FROM ticket
722
@ </pre></blockquote>
723
@ <p>To base the background color on the TICKET.PRIORITY or
724
@ TICKET.SEVERITY fields, substitute the following code for the
725
@ first column of the query:</p>
726
@ <table class="rpteditex">
727
@ <tr style="background-color:#f2dcdc;"><td class="rpteditex">1</td></tr>
728
@ <tr style="background-color:#e8e8bd;"><td class="rpteditex">2</td></tr>
729
@ <tr style="background-color:#cfe8bd;"><td class="rpteditex">3</td></tr>
730
@ <tr style="background-color:#cacae5;"><td class="rpteditex">4</td></tr>
731
@ <tr style="background-color:#c8c8c8;"><td class="rpteditex">5</td></tr>
732
@ </table>
733
@ <blockquote><pre>
734
@ SELECT
735
@ CASE priority WHEN 1 THEN '#f2dcdc'
736
@ WHEN 2 THEN '#e8e8bd'
737
@ WHEN 3 THEN '#cfe8bd'
738
@ WHEN 4 THEN '#cacae5'
739
@ ELSE '#c8c8c8' END as 'bgcolor',
740
@ ...
741
@ FROM ticket
742
@ </pre></blockquote>
743
#if 0
744
@ <p>You can, of course, substitute different colors if you choose.
745
@ Here is a palette of suggested background colors:</p>
746
@ <blockquote>
747
@ <table border=1 cellspacing=0 width=300>
748
@ <tr><td align="center" bgcolor="#ffbdbd">#ffbdbd</td>
749
@ <td align="center" bgcolor="#f2dcdc">#f2dcdc</td></tr>
750
@ <tr><td align="center" bgcolor="#ffffbd">#ffffbd</td>
751
@ <td align="center" bgcolor="#e8e8bd">#e8e8bd</td></tr>
752
@ <tr><td align="center" bgcolor="#c0ebc0">#c0ebc0</td>
753
@ <td align="center" bgcolor="#cfe8bd">#cfe8bd</td></tr>
754
@ <tr><td align="center" bgcolor="#c0c0f4">#c0c0f4</td>
755
@ <td align="center" bgcolor="#d6d6e8">#d6d6e8</td></tr>
756
@ <tr><td align="center" bgcolor="#d0b1ff">#d0b1ff</td>
757
@ <td align="center" bgcolor="#d2c0db">#d2c0db</td></tr>
758
@ <tr><td align="center" bgcolor="#bbbbbb">#bbbbbb</td>
759
@ <td align="center" bgcolor="#d0d0d0">#d0d0d0</td></tr>
760
@ </table>
761
@ </blockquote>
762
#endif
763
@ <p>To see the TICKET.DESCRIPTION and TICKET.REMARKS fields, include
764
@ them as the last two columns of the result set and given them names
765
@ that begin with an underscore. Like this:</p>
766
@ <blockquote><pre>
767
@ SELECT
768
@ tn AS '#',
769
@ type AS 'Type',
770
@ status AS 'Status',
771
@ sdate(origtime) AS 'Created',
772
@ owner AS 'By',
773
@ subsystem AS 'Subsys',
774
@ sdate(changetime) AS 'Changed',
775
@ assignedto AS 'Assigned',
776
@ severity AS 'Svr',
777
@ priority AS 'Pri',
778
@ title AS 'Title',
779
@ description AS '_Description', -- When the column name begins with '_'
780
@ remarks AS '_Remarks' -- content is rendered as wiki
781
@ FROM ticket
782
@ </pre></blockquote>
783
@
784
}
785
786
/*
787
** The state of the report generation.
788
*/
789
struct GenerateHTML {
790
int rn; /* Report number */
791
int nCount; /* Row number */
792
int nCol; /* Number of columns */
793
int isMultirow; /* True if multiple table rows per query result row */
794
int iNewRow; /* Index of first column that goes on separate row */
795
int iBg; /* Index of column that defines background color */
796
int wikiFlags; /* Flags passed into wiki_convert() */
797
const char *zWikiStart; /* HTML before display of multi-line wiki */
798
const char *zWikiEnd; /* HTML after display of multi-line wiki */
799
};
800
801
/*
802
** The callback function for db_query
803
*/
804
static int generate_html(
805
void *pUser, /* Pointer to output state */
806
int nArg, /* Number of columns in this result row */
807
const char **azArg, /* Text of data in all columns */
808
const char **azName /* Names of the columns */
809
){
810
struct GenerateHTML *pState = (struct GenerateHTML*)pUser;
811
int i;
812
const char *zTid; /* Ticket hash. (value of column named '#') */
813
const char *zBg = 0; /* Use this background color */
814
815
/* Do initialization
816
*/
817
if( pState->nCount==0 ){
818
/* Turn off the authorizer. It is no longer doing anything since the
819
** query has already been prepared.
820
*/
821
db_clear_authorizer();
822
823
/* Figure out the number of columns, the column that determines background
824
** color, and whether or not this row of data is represented by multiple
825
** rows in the table.
826
*/
827
pState->nCol = 0;
828
pState->isMultirow = 0;
829
pState->iNewRow = -1;
830
pState->iBg = -1;
831
for(i=0; i<nArg; i++){
832
if( azName[i][0]=='b' && fossil_strcmp(azName[i],"bgcolor")==0 ){
833
pState->iBg = i;
834
continue;
835
}
836
if( g.perm.Write && azName[i][0]=='#' ){
837
pState->nCol++;
838
}
839
if( !pState->isMultirow ){
840
if( azName[i][0]=='_' ){
841
pState->isMultirow = 1;
842
pState->iNewRow = i;
843
pState->wikiFlags = WIKI_NOBADLINKS;
844
pState->zWikiStart = "";
845
pState->zWikiEnd = "";
846
if( P("plaintext") ){
847
pState->wikiFlags |= WIKI_LINKSONLY;
848
pState->zWikiStart = "<pre class='verbatim'>";
849
pState->zWikiEnd = "</pre>";
850
style_submenu_element("Formatted", "%R/rptview/%d", pState->rn);
851
}else{
852
style_submenu_element("Plaintext", "%R/rptview/%d?plaintext",
853
pState->rn);
854
}
855
}else{
856
pState->nCol++;
857
}
858
}
859
}
860
861
/* The first time this routine is called, output a table header
862
*/
863
@ <thead><tr>
864
zTid = 0;
865
for(i=0; i<nArg; i++){
866
const char *zName = azName[i];
867
if( i==pState->iBg ) continue;
868
if( pState->iNewRow>=0 && i>=pState->iNewRow ){
869
if( g.perm.Write && zTid ){
870
@ <th>&nbsp;</th>
871
zTid = 0;
872
}
873
if( zName[0]=='_' ) zName++;
874
@ </tr><tr><th colspan=%d(pState->nCol)>%h(zName)</th>
875
}else{
876
if( zName[0]=='#' ){
877
zTid = zName;
878
}
879
@ <th>%h(zName)</th>
880
}
881
}
882
if( g.perm.Write && zTid ){
883
@ <th>&nbsp;</th>
884
}
885
@ </tr></thead><tbody>
886
}
887
if( azArg==0 ){
888
@ <tr><td colspan="%d(pState->nCol)">
889
@ <i>No records match the report criteria</i>
890
@ </td></tr>
891
return 0;
892
}
893
++pState->nCount;
894
895
/* Output the separator above each entry in a table which has multiple lines
896
** per database entry.
897
*/
898
if( pState->iNewRow>=0 ){
899
@ <tr><td colspan="%d(pState->nCol)" style="padding:0px">
900
@ <hr style="margin:0px"></td></tr>
901
}
902
903
/* Output the data for this entry from the database
904
*/
905
zBg = pState->iBg>=0 ? azArg[pState->iBg] : 0;
906
if( zBg==0 ) zBg = "white";
907
@ <tr style="background-color:%h(zBg)">
908
zTid = 0;
909
for(i=0; i<nArg; i++){
910
const char *zData;
911
if( i==pState->iBg ) continue;
912
zData = azArg[i];
913
if( zData==0 ) zData = "";
914
if( pState->iNewRow>=0 && i>=pState->iNewRow ){
915
if( zTid && g.perm.Write ){
916
@ <td valign="top">%z(href("%R/tktedit/%h",zTid))edit</a></td>
917
zTid = 0;
918
}
919
if( zData[0] ){
920
Blob content;
921
@ </tr>
922
@ <tr style="background-color:%h(zBg)"><td colspan=%d(pState->nCol)>
923
@ %s(pState->zWikiStart)
924
blob_init(&content, zData, -1);
925
wiki_convert(&content, 0, pState->wikiFlags);
926
blob_reset(&content);
927
@ %s(pState->zWikiEnd)
928
}
929
}else if( azName[i][0]=='#' ){
930
zTid = zData;
931
@ <td valign="top">%z(href("%R/tktview/%h",zData))%h(zData)</a></td>
932
}else if( zData[0]==0 ){
933
@ <td valign="top">&nbsp;</td>
934
}else{
935
@ <td valign="top">
936
@ %h(zData)
937
@ </td>
938
}
939
}
940
if( zTid && g.perm.Write ){
941
@ <td valign="top">%z(href("%R/tktedit/%h",zTid))edit</a></td>
942
}
943
@ </tr>
944
return 0;
945
}
946
947
/*
948
** Output the text given in the argument. Convert tabs and newlines into
949
** spaces.
950
*/
951
static void output_no_tabs(const char *z){
952
while( z && z[0] ){
953
int i, j;
954
for(i=0; z[i] && (!fossil_isspace(z[i]) || z[i]==' '); i++){}
955
if( i>0 ){
956
cgi_printf("%.*s", i, z);
957
}
958
for(j=i; fossil_isspace(z[j]); j++){}
959
if( j>i ){
960
cgi_printf("%*s", j-i, "");
961
}
962
z += j;
963
}
964
}
965
966
/*
967
** Output a row as a tab-separated line of text.
968
*/
969
static int output_tab_separated(
970
void *pUser, /* Pointer to row-count integer */
971
int nArg, /* Number of columns in this result row */
972
const char **azArg, /* Text of data in all columns */
973
const char **azName /* Names of the columns */
974
){
975
int *pCount = (int*)pUser;
976
int i;
977
978
if( *pCount==0 ){
979
for(i=0; i<nArg; i++){
980
output_no_tabs(azName[i]);
981
cgi_printf("%c", i<nArg-1 ? '\t' : '\n');
982
}
983
}
984
++*pCount;
985
for(i=0; i<nArg; i++){
986
output_no_tabs(azArg[i]);
987
cgi_printf("%c", i<nArg-1 ? '\t' : '\n');
988
}
989
return 0;
990
}
991
992
/*
993
** Generate HTML that describes a color key.
994
*/
995
void output_color_key(const char *zClrKey, int horiz, char *zTabArgs){
996
int i, j, k;
997
const char *zSafeKey;
998
char *zToFree;
999
while( fossil_isspace(*zClrKey) ) zClrKey++;
1000
if( zClrKey[0]==0 ) return;
1001
@ <table %s(zTabArgs)>
1002
if( horiz ){
1003
@ <tr>
1004
}
1005
zSafeKey = zToFree = mprintf("%h", zClrKey);
1006
while( zSafeKey[0] ){
1007
while( fossil_isspace(*zSafeKey) ) zSafeKey++;
1008
for(i=0; zSafeKey[i] && !fossil_isspace(zSafeKey[i]); i++){}
1009
for(j=i; fossil_isspace(zSafeKey[j]); j++){}
1010
for(k=j; zSafeKey[k] && zSafeKey[k]!='\n' && zSafeKey[k]!='\r'; k++){}
1011
if( !horiz ){
1012
cgi_printf("<tr style=\"background-color: %.*s;\"><td>%.*s</td></tr>\n",
1013
i, zSafeKey, k-j, &zSafeKey[j]);
1014
}else{
1015
cgi_printf("<td style=\"background-color: %.*s;\">%.*s</td>\n",
1016
i, zSafeKey, k-j, &zSafeKey[j]);
1017
}
1018
zSafeKey += k;
1019
}
1020
free(zToFree);
1021
if( horiz ){
1022
@ </tr>
1023
}
1024
@ </table>
1025
}
1026
1027
/*
1028
** Execute a single read-only SQL statement. Invoke xCallback() on each
1029
** row.
1030
*/
1031
static int db_exec_readonly(
1032
sqlite3 *db, /* The database on which the SQL executes */
1033
const char *zSql, /* The SQL to be executed */
1034
int (*xCallback)(void*,int,const char**, const char**),
1035
/* Invoke this callback routine */
1036
void *pArg, /* First argument to xCallback() */
1037
char **pzErrMsg /* Write error messages here */
1038
){
1039
int rc = SQLITE_OK; /* Return code */
1040
const char *zLeftover; /* Tail of unprocessed SQL */
1041
sqlite3_stmt *pStmt = 0; /* The current SQL statement */
1042
const char **azCols = 0; /* Names of result columns */
1043
int nCol; /* Number of columns of output */
1044
const char **azVals = 0; /* Text of all output columns */
1045
int i; /* Loop counter */
1046
int nVar; /* Number of parameters */
1047
1048
pStmt = 0;
1049
rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
1050
assert( rc==SQLITE_OK || pStmt==0 );
1051
if( rc!=SQLITE_OK ){
1052
return rc;
1053
}
1054
if( !pStmt ){
1055
/* this happens for a comment or white-space */
1056
return SQLITE_OK;
1057
}
1058
if( !sqlite3_stmt_readonly(pStmt) ){
1059
sqlite3_finalize(pStmt);
1060
return SQLITE_ERROR;
1061
}
1062
1063
nVar = sqlite3_bind_parameter_count(pStmt);
1064
for(i=1; i<=nVar; i++){
1065
const char *zVar = sqlite3_bind_parameter_name(pStmt, i);
1066
if( zVar==0 ) continue;
1067
if( zVar[0]!='$' && zVar[0]!='@' && zVar[0]!=':' ) continue;
1068
if( !fossil_islower(zVar[1]) ) continue;
1069
if( strcmp(zVar, "$login")==0 ){
1070
sqlite3_bind_text(pStmt, i, g.zLogin, -1, SQLITE_TRANSIENT);
1071
}else{
1072
sqlite3_bind_text(pStmt, i, P(zVar+1), -1, SQLITE_TRANSIENT);
1073
}
1074
}
1075
nCol = sqlite3_column_count(pStmt);
1076
azVals = fossil_malloc(2*nCol*sizeof(const char*) + 1);
1077
while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){
1078
if( azCols==0 ){
1079
azCols = &azVals[nCol];
1080
for(i=0; i<nCol; i++){
1081
azCols[i] = sqlite3_column_name(pStmt, i);
1082
}
1083
}
1084
for(i=0; i<nCol; i++){
1085
azVals[i] = (const char *)sqlite3_column_text(pStmt, i);
1086
}
1087
if( xCallback(pArg, nCol, azVals, azCols) ){
1088
break;
1089
}
1090
}
1091
rc = sqlite3_finalize(pStmt);
1092
fossil_free((void *)azVals);
1093
return rc;
1094
}
1095
1096
/*
1097
** WEBPAGE: rptview
1098
**
1099
** Generate a report. The rn query parameter is the report number
1100
** corresponding to REPORTFMT.RN. If the tablist query parameter exists,
1101
** then the output consists of lines of tab-separated fields instead of
1102
** an HTML table.
1103
*/
1104
void rptview_page(void){
1105
rptview_page_content(0, 1, 1);
1106
}
1107
1108
/*
1109
** Render a report.
1110
*/
1111
void rptview_page_content(
1112
const char *defaultTitleSearch, /* If rn and title query parameters are
1113
blank, search reports by this title. */
1114
int pageWrap, /* If true, render full page; otherwise, just the report */
1115
int redirectMissing /* If true and report not found, go to reportlist */
1116
){
1117
int count = 0;
1118
int rn, rc;
1119
char *zSql;
1120
char *zTitle;
1121
char *zOwner;
1122
char *zClrKey;
1123
char *zDesc;
1124
char *zMimetype;
1125
int tabs;
1126
Stmt q;
1127
char *zErr1 = 0;
1128
char *zErr2 = 0;
1129
1130
login_check_credentials();
1131
if( !g.perm.RdTkt ){ login_needed(g.anon.RdTkt); return; }
1132
report_update_reportfmt_table();
1133
rn = report_number();
1134
tabs = P("tablist")!=0;
1135
db_prepare(&q,
1136
"SELECT title, sqlcode, owner, cols, rn, jx->>'desc', jx->>'descmt'"
1137
" FROM reportfmt WHERE rn=%d", rn);
1138
rc = db_step(&q);
1139
if( rc!=SQLITE_ROW ){
1140
const char *titleSearch =
1141
defaultTitleSearch==0 || trim_string(defaultTitleSearch)[0]==0 ?
1142
P("title") : defaultTitleSearch;
1143
db_finalize(&q);
1144
db_prepare(&q,
1145
"SELECT title, sqlcode, owner, cols, rn, jx->>'desc', jx->>'descmt'"
1146
" FROM reportfmt WHERE title GLOB %Q",
1147
titleSearch);
1148
rc = db_step(&q);
1149
}
1150
if( rc!=SQLITE_ROW ){
1151
db_finalize(&q);
1152
if( redirectMissing ) {
1153
cgi_redirect("reportlist");
1154
}
1155
return;
1156
}
1157
zTitle = db_column_malloc(&q, 0);
1158
zSql = db_column_malloc(&q, 1);
1159
zOwner = db_column_malloc(&q, 2);
1160
zClrKey = db_column_malloc(&q, 3);
1161
rn = db_column_int(&q,4);
1162
zDesc = db_column_malloc(&q, 5);
1163
zMimetype = db_column_malloc(&q, 6);
1164
db_finalize(&q);
1165
1166
if( P("order_by") ){
1167
/*
1168
** If the user wants to do a column sort, wrap the query into a sub
1169
** query and then sort the results. This is a whole lot easier than
1170
** trying to insert an ORDER BY into the query itself, especially
1171
** if the query is already ordered.
1172
*/
1173
int nField = atoi(P("order_by"));
1174
if( nField > 0 ){
1175
const char* zDir = PD("order_dir","");
1176
zDir = !strcmp("ASC",zDir) ? "ASC" : "DESC";
1177
zSql = mprintf("SELECT * FROM (%s) ORDER BY %d %s", zSql, nField, zDir);
1178
}
1179
}
1180
1181
count = 0;
1182
if( !tabs ){
1183
struct GenerateHTML sState = { 0, 0, 0, 0, 0, 0, 0, 0, 0 };
1184
const char *zQS = PD("QUERY_STRING","");
1185
1186
db_multi_exec("PRAGMA empty_result_callbacks=ON");
1187
style_set_current_feature("report");
1188
if( pageWrap ) {
1189
/* style_finish_page() should provide escaping via %h formatting */
1190
if( zQS[0] ){
1191
if( g.zExtra && g.zExtra[0] ){
1192
style_submenu_element("Raw","%R/%s/%s?tablist=1&%s",
1193
g.zPath, g.zExtra, zQS);
1194
}else{
1195
style_submenu_element("Raw","%R/%s?tablist=1&%s",g.zPath,zQS);
1196
}
1197
style_submenu_element("Reports","%R/reportlist?%s",zQS);
1198
} else {
1199
if( g.zExtra && g.zExtra[0] ){
1200
style_submenu_element("Raw","%R/%s/%s?tablist=1",g.zPath,g.zExtra);
1201
}else{
1202
style_submenu_element("Raw","%R/%s?tablist=1",g.zPath);
1203
}
1204
style_submenu_element("Reports","%R/reportlist");
1205
}
1206
if( g.perm.Admin
1207
|| (g.perm.TktFmt && g.zLogin && fossil_strcmp(g.zLogin,zOwner)==0) ){
1208
style_submenu_element("Edit", "%R/rptedit/%d", rn);
1209
}
1210
if( g.perm.TktFmt ){
1211
style_submenu_element("SQL", "%R/rptsql/%d",rn);
1212
}
1213
if( g.perm.NewTkt ){
1214
style_submenu_element("New Ticket", "%R/tktnew");
1215
}
1216
style_header("%s", zTitle);
1217
}
1218
if( zDesc && zDesc[0] && zMimetype ){
1219
Blob src;
1220
blob_init(&src, zDesc, -1);
1221
wiki_render_by_mimetype(&src, zMimetype);
1222
blob_reset(&src);
1223
@ <br>
1224
}
1225
output_color_key(zClrKey, 1,
1226
"border=\"0\" cellpadding=\"3\" cellspacing=\"0\" class=\"report\"");
1227
@ <table border="1" cellpadding="2" cellspacing="0" class="report sortable"
1228
@ data-column-types='' data-init-sort='0'>
1229
sState.rn = rn;
1230
sState.nCount = 0;
1231
report_restrict_sql(&zErr1);
1232
db_exec_readonly(g.db, zSql, generate_html, &sState, &zErr2);
1233
report_unrestrict_sql();
1234
@ </tbody></table>
1235
if( zErr1 ){
1236
@ <p class="reportError">Error: %h(zErr1)</p>
1237
}else if( zErr2 ){
1238
@ <p class="reportError">Error: %h(zErr2)</p>
1239
}
1240
style_table_sorter();
1241
if( pageWrap ) {
1242
style_finish_page();
1243
}
1244
}else{
1245
report_restrict_sql(&zErr1);
1246
db_exec_readonly(g.db, zSql, output_tab_separated, &count, &zErr2);
1247
report_unrestrict_sql();
1248
cgi_set_content_type("text/plain");
1249
}
1250
}
1251
1252
/*
1253
** report number for full table ticket export
1254
*/
1255
static const char zFullTicketRptRn[] = "0";
1256
1257
/*
1258
** report title for full table ticket export
1259
*/
1260
static const char zFullTicketRptTitle[] = "full ticket export";
1261
1262
/*
1263
** show all reports, which can be used for ticket show.
1264
** Output is written to stdout as tab delimited table
1265
*/
1266
void rpt_list_reports(void){
1267
Stmt q;
1268
fossil_print("Available reports:\n");
1269
fossil_print("%s\t%s\n","report number","report title");
1270
fossil_print("%s\t%s\n",zFullTicketRptRn,zFullTicketRptTitle);
1271
db_prepare(&q,"SELECT rn,title FROM reportfmt ORDER BY rn");
1272
while( db_step(&q)==SQLITE_ROW ){
1273
const char *zRn = db_column_text(&q, 0);
1274
const char *zTitle = db_column_text(&q, 1);
1275
1276
fossil_print("%s\t%s\n",zRn,zTitle);
1277
}
1278
db_finalize(&q);
1279
}
1280
1281
/*
1282
** user defined separator used by ticket show command
1283
*/
1284
static const char *zSep = 0;
1285
1286
/*
1287
** select the quoting algorithm for "ticket show"
1288
*/
1289
#if INTERFACE
1290
typedef enum eTktShowEnc { tktNoTab=0, tktFossilize=1 } tTktShowEncoding;
1291
#endif
1292
static tTktShowEncoding tktEncode = tktNoTab;
1293
1294
/*
1295
** Output the text given in the argument. Convert tabs and newlines into
1296
** spaces.
1297
*/
1298
static void output_no_tabs_file(const char *z){
1299
switch( tktEncode ){
1300
case tktFossilize:
1301
{ char *zFosZ;
1302
1303
if( z && *z ){
1304
zFosZ = fossilize(z,-1);
1305
fossil_print("%s",zFosZ);
1306
free(zFosZ);
1307
}
1308
break;
1309
}
1310
default:
1311
while( z && z[0] ){
1312
int i, j;
1313
for(i=0; z[i] && (!fossil_isspace(z[i]) || z[i]==' '); i++){}
1314
if( i>0 ){
1315
fossil_print("%.*s", i, z);
1316
}
1317
for(j=i; fossil_isspace(z[j]); j++){}
1318
if( j>i ){
1319
fossil_print("%*s", j-i, "");
1320
}
1321
z += j;
1322
}
1323
break;
1324
}
1325
}
1326
1327
/*
1328
** Output a row as a tab-separated line of text.
1329
*/
1330
int output_separated_file(
1331
void *pUser, /* Pointer to row-count integer */
1332
int nArg, /* Number of columns in this result row */
1333
const char **azArg, /* Text of data in all columns */
1334
const char **azName /* Names of the columns */
1335
){
1336
int *pCount = (int*)pUser;
1337
int i;
1338
1339
if( *pCount==0 ){
1340
for(i=0; i<nArg; i++){
1341
output_no_tabs_file(azName[i]);
1342
fossil_print("%s", i<nArg-1 ? (zSep?zSep:"\t") : "\n");
1343
}
1344
}
1345
++*pCount;
1346
for(i=0; i<nArg; i++){
1347
output_no_tabs_file(azArg[i]);
1348
fossil_print("%s", i<nArg-1 ? (zSep?zSep:"\t") : "\n");
1349
}
1350
return 0;
1351
}
1352
1353
/*
1354
** Generate a report. The rn query parameter is the report number.
1355
** The output is written to stdout as flat file. The zFilter parameter
1356
** is a full WHERE-condition.
1357
*/
1358
void rptshow(
1359
const char *zRep,
1360
const char *zSepIn,
1361
const char *zFilter,
1362
tTktShowEncoding enc
1363
){
1364
Stmt q;
1365
char *zSql;
1366
char *zErr1 = 0;
1367
char *zErr2 = 0;
1368
int count = 0;
1369
int rn;
1370
1371
if( !zRep || !strcmp(zRep,zFullTicketRptRn)
1372
|| !strcmp(zRep,zFullTicketRptTitle) ){
1373
zSql = "SELECT * FROM ticket";
1374
}else{
1375
rn = atoi(zRep);
1376
if( rn ){
1377
db_prepare(&q,
1378
"SELECT sqlcode FROM reportfmt WHERE rn=%d", rn);
1379
}else{
1380
db_prepare(&q,
1381
"SELECT sqlcode FROM reportfmt WHERE title=%Q", zRep);
1382
}
1383
if( db_step(&q)!=SQLITE_ROW ){
1384
db_finalize(&q);
1385
rpt_list_reports();
1386
fossil_fatal("unknown report format(%s)!",zRep);
1387
}
1388
zSql = db_column_malloc(&q, 0);
1389
db_finalize(&q);
1390
}
1391
if( zFilter ){
1392
zSql = mprintf("SELECT * FROM (%s) WHERE %s",zSql,zFilter);
1393
}
1394
count = 0;
1395
tktEncode = enc;
1396
zSep = zSepIn;
1397
report_restrict_sql(&zErr1);
1398
db_exec_readonly(g.db, zSql, output_separated_file, &count, &zErr2);
1399
report_unrestrict_sql();
1400
if( zFilter ){
1401
free(zSql);
1402
}
1403
}
1404

Keyboard Shortcuts

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