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