/* ** Copyright (c) 2007 D. Richard Hipp ** ** This program is free software; you can redistribute it and/or ** modify it under the terms of the GNU General Public ** License as published by the Free Software Foundation; either ** version 2 of the License, or (at your option) any later version. ** ** This program is distributed in the hope that it will be useful, ** but WITHOUT ANY WARRANTY; without even the implied warranty of ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU ** General Public License for more details. ** ** You should have received a copy of the GNU General Public ** License along with this library; if not, write to the ** Free Software Foundation, Inc., 59 Temple Place - Suite 330, ** Boston, MA 02111-1307, USA. ** ** Author contact information: ** drh@hwaci.com ** http://www.hwaci.com/drh/ ** ******************************************************************************* ** ** Code to generate the bug report listings */ #include "config.h" #include "report.h" #include /* Forward references to static routines */ static void report_format_hints(void); /* ** WEBPAGE: /reportlist */ void view_list(void){ Stmt q; login_check_credentials(); if( !g.okRdTkt ){ login_needed(); return; } style_header("Available Report Formats"); db_prepare(&q, "SELECT rn, title, owner FROM reportfmt ORDER BY title"); @

Choose a report format from the following list:

@
    while( db_step(&q)==SQLITE_ROW ){ int rn = db_column_int(&q, 0); const char *zTitle = db_column_text(&q, 1); const char *zOwner = db_column_text(&q, 2); @
  1. %h(zTitle)    if( g.okWrite && zOwner && zOwner[0] ){ @ (by %h(zOwner)) } if( g.okWrTkt ){ @ [copy] } if( g.okAdmin || (g.okWrTkt && zOwner && strcmp(g.zLogin,zOwner)==0) ){ @ [edit] } @ [sql] @
  2. } if( g.okWrTkt ){ @
  3. Create a new report format
  4. } @
common_footer(); } /* ** Remove whitespace from both ends of a string. */ char *trim_string(const char *zOrig){ int i; while( isspace(*zOrig) ){ zOrig++; } i = strlen(zOrig); while( i>0 && isspace(zOrig[i-1]) ){ i--; } return mprintf("%.*s", i, zOrig); } /* ** Extract a numeric (integer) value from a string. */ char *extract_integer(const char *zOrig){ if( zOrig == NULL || zOrig[0] == 0 ) return ""; while( *zOrig && !isdigit(*zOrig) ){ zOrig++; } if( *zOrig ){ /* we have a digit. atoi() will get as much of the number as it ** can. We'll run it through mprintf() to get a string. Not ** an efficient way to do it, but effective. */ return mprintf("%d", atoi(zOrig)); } return ""; } /* ** Remove blank lines from the beginning of a string and ** all whitespace from the end. Removes whitespace preceeding a NL, ** which also converts any CRNL sequence into a single NL. */ char *remove_blank_lines(const char *zOrig){ int i, j, n; char *z; for(i=j=0; isspace(zOrig[i]); i++){ if( zOrig[i]=='\n' ) j = i+1; } n = strlen(&zOrig[j]); while( n>0 && isspace(zOrig[j+n-1]) ){ n--; } z = mprintf("%.*s", n, &zOrig[j]); for(i=j=0; z[i]; i++){ if( z[i+1]=='\n' && z[i]!='\n' && isspace(z[i]) ){ z[j] = z[i]; while(isspace(z[j]) && z[j] != '\n' ){ j--; } j++; continue; } z[j++] = z[i]; } z[j] = 0; return z; } /*********************************************************************/ /* ** This is the SQLite authorizer callback used to make sure that the ** SQL statements entered by users do not try to do anything untoward. ** If anything suspicious is tried, set *(char**)pError to an error ** message obtained from malloc. */ static int report_query_authorizer( void *pError, int code, const char *zArg1, const char *zArg2, const char *zArg3, const char *zArg4 ){ char *zError = *(char**)pError; if( zError ){ /* We've already seen an error. No need to continue. */ return SQLITE_OK; } switch( code ){ case SQLITE_SELECT: case SQLITE_FUNCTION: { break; } case SQLITE_READ: { static const char *azAllowed[] = { "ticket", "blob", "filename", "mlink", "plink", "event", "tag", "tagxref", } int i; for(i=0; i=sizeof(azAllowed)/sizeof(azAllowed[0]) ){ zError = mprintf("cannot access table %s", zArg1); } break; } default: { zError = mprintf("only SELECT statements are allowed"); break; } } return SQLITE_OK; } /* ** Check the given SQL to see if is a valid query that does not ** attempt to do anything dangerous. Return 0 on success and a ** pointer to an error message string (obtained from malloc) if ** there is a problem. */ char *verify_sql_statement(char *zSql){ int i; char *zErr1 = 0; char *zErr2 = 0; char *zTail; /* First make sure the SQL is a single query command by verifying that ** the first token is "SELECT" and that there are no unquoted semicolons. */ for(i=0; isspace(zSql[i]); i++){} if( strncasecmp(&zSql[i],"select",6)!=0 ){ return mprintf("The SQL must be a SELECT statement"); } for(i=0; zSql[i]; i++){ if( zSql[i]==';' ){ int bad; int c = zSql[i+1]; zSql[i+1] = 0; bad = sqlite3_complete(zSql); zSql[i+1] = c; if( bad ){ /* A complete statement basically means that an unquoted semi-colon ** was found. We don't actually check what's after that. */ return mprintf("Semi-colon detected! " "Only a single SQL statement is allowed"); } } } /* Compile the statement and check for illegal accesses or syntax errors. */ sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr); rc = sqlite3_prepare(g.db, zSql, -1, &pStmt, &zTail); if( rc!=SQLITE_OK ){ free(zErr); zErr = mprintf("Syntax error: %s", sqlite3_errmsg(g.db)); } if( pStmt ){ sqlite3_finalize(pStmt); } sqlite3_set_authorizer(g.db, 0, 0); return zErr; } /* ** WEBPAGE: /rptsql */ void view_see_sql(void){ int rn, rc; char *zTitle; char *zSQL; char *zOwner; char *zClrKey; Stmt q; login_check_credentials(); if( !g.okQuery ){ login_needed(); return; } rn = atoi(PD("rn","0")); db_prepare(&q, "SELECT title, sqlcode, owner, cols " "FROM reportfmt WHERE rn=%d",rn); style_header("SQL For Report Format Number %d", rn); if( db_step(&q)!=SQLITE_ROW ){ @

Unknown report number: %d(rn)

style_footer(); return; } zTitle = db_column_text(&q, 0); zSQL = db_column_text(&q, 1); zOwner = db_column_text(&q, 2); zClrKey = db_column_text(&q, 3); @ @ @ @ @ @ @ @ @
Title:%h(zTitle)
Owner:%h(zOwner)
SQL:
  @ %h(zSQL)
  @ 
output_color_key(zClrKey, 0, "border=0 cellspacing=0 cellpadding=3"); @
report_format_hints(); style_footer(); } /* ** WEBPAGE: /rptnew ** WEBPAGE: /rptedit */ void view_edit(void){ int rn; const char *zTitle; const char *z; const char *zOwner; char *zClrKey; char *zSQL; char *zErr = 0; login_check_credentials(); if( !g.okQuery ){ login_needed(); return; } view_add_functions(0); rn = atoi(PD("rn","0")); zTitle = P("t"); zOwner = PD("w",g.zLogin); z = P("s"); zSQL = z ? trim_string(z) : 0; zClrKey = trim_string(PD("k","")); if( rn>0 && P("del2") ){ db_multi_exec("DELETE FROM reportfmt WHERE rn=%d", rn); cgi_redirect("reportlist"); return; }else if( rn>0 && P("del1") ){ zTitle = db_text(0, "SELECT title FROM reportfmt " "WHERE rn=%d", rn); if( zTitle==0 ) cgi_redirect("reportlist"); style_header("Are You Sure?"); @
@

You are about to delete all traces of the report @ %h(zTitle) from @ the database. This is an irreversible operation. All records @ related to this report will be removed and cannot be recovered.

@ @ @ @ @
style_footer(); return; }else if( P("can") ){ /* user cancelled */ cgi_redirect("reportlist"); return; } if( zTitle && zSQL ){ if( zSQL[0]==0 ){ zErr = "Please supply an SQL query statement"; }else if( (zTitle = trim_string(zTitle))[0]==0 ){ zErr = "Please supply a title"; }else{ zErr = verify_sql_statement(zSQL); } if( zErr==0 ){ if( rn>0 ){ db_multi_exec("UPDATE reportfmt SET title=%Q, sqlcode=%Q," " owner=%Q, cols=%Q WHERE rn=%d", zTitle, zSQL, zOwner, zClrKey, rn); }else{ db_multi_exec("INSERT INTO reportfmt(title,sqlcode,owner,cols) " "VALUES(%Q,%Q,%Q,%Q)", zTitle, zSQL, zOwner, zClrKey); rn = db_last_insert_rowid(); } cgi_redirect(mprintf("rptview?rn=%d", rn)); return; } }else if( rn==0 ){ zTitle = ""; zSQL = @ SELECT @ CASE WHEN status IN ('new','active') THEN '#f2dcdc' @ WHEN status='review' THEN '#e8e8bd' @ WHEN status='fixed' THEN '#cfe8bd' @ WHEN status='tested' THEN '#bde5d6' @ WHEN status='defer' THEN '#cacae5' @ ELSE '#c8c8c8' END AS 'bgcolor', @ tn AS '#', @ type AS 'Type', @ status AS 'Status', @ sdate(origtime) AS 'Created', @ owner AS 'By', @ subsystem AS 'Subsys', @ sdate(changetime) AS 'Changed', @ assignedto AS 'Assigned', @ severity AS 'Svr', @ priority AS 'Pri', @ title AS 'Title' @ FROM ticket ; zClrKey = @ #ffffff Key: @ #f2dcdc Active @ #e8e8e8 Review @ #cfe8bd Fixed @ #bde5d6 Tested @ #cacae5 Deferred @ #c8c8c8 Closed ; }else{ db_prepare(&q, "SELECT title, sqlcode, owner, cols " "FROM reportfmt WHERE rn=%d",rn); if( db_step(&q)==SQLITE_ROW ){ zTitle = db_column_malloc(&q, 0); zSQL = db_column_malloc(&q, 1); zOwner = db_column_malloc(&q, 2); zClrKey = db_column_malloc(&q, 3); } if( P("copy") ){ rn = 0; zTitle = mprintf("Copy Of %s", zTitle); zOwner = g.zLogin; } } if( zOwner==0 ) zOwner = g.zLogin; style_submenu_element("Cancel", "Cancel", "reportlist"); if( rn>0 ){ style_submenu_element("Delete", "Delete", "rptedit?rn=%d&del1=1", rn); } style_header(rn>0 ? "Edit Report Format":"Create New Report Format"); if( zErr ){ @
%h(zErr)
} @
@ @

Report Title:
@

@

Enter a complete SQL query statement against the "TICKET" table:
@ @

if( g.okAdmin ){ @

Report owner: @ @

} else { @ } @

Enter an optional color key in the following box. (If blank, no @ color key is displayed.) Each line contains the text for a single @ entry in the key. The first token of each line is the background @ color for that line.
@ @

if( !g.okAdmin && strcmp(zOwner,g.zLogin)!=0 ){ @

This report format is owned by %h(zOwner). You are not allowed @ to change it.

@
report_format_hints(); style_footer(); return; } @ if( rn>0 ){ @ } @ report_format_hints(); style_footer(); } /* ** Output a bunch of text that provides information about report ** formats */ static void report_format_hints(void){ char *zSchema; zSchema = db_text(0,"SELECT sql FROM sqlite_master WHERE name='ticket'"); @

TICKET Schema

@
  @ %h(zSchema)
  @ 
@

Notes

@ @ @

Examples

@

In this example, the first column in the result set is named @ "bgcolor". The value of this column is not displayed. Instead, it @ selects the background color of each row based on the TICKET.STATUS @ field of the database. The color key at the right shows the various @ color codes.

@ @ @ @ @ @ @ @
new or active
review
fixed
tested
defer
closed
@
  @ SELECT
  @   CASE WHEN status IN ('new','active') THEN '#f2dcdc'
  @        WHEN status='review' THEN '#e8e8bd'
  @        WHEN status='fixed' THEN '#cfe8bd'
  @        WHEN status='tested' THEN '#bde5d6'
  @        WHEN status='defer' THEN '#cacae5'
  @        ELSE '#c8c8c8' END as 'bgcolor',
  @   tn AS '#',
  @   type AS 'Type',
  @   status AS 'Status',
  @   sdate(origtime) AS 'Created',
  @   owner AS 'By',
  @   subsystem AS 'Subsys',
  @   sdate(changetime) AS 'Changed',
  @   assignedto AS 'Assigned',
  @   severity AS 'Svr',
  @   priority AS 'Pri',
  @   title AS 'Title'
  @ FROM ticket
  @ 
@

To base the background color on the TICKET.PRIORITY or @ TICKET.SEVERITY fields, substitute the following code for the @ first column of the query:

@ @ @ @ @ @ @
1
2
3
4
5
@
  @ SELECT
  @   CASE priority WHEN 1 THEN '#f2dcdc'
  @        WHEN 2 THEN '#e8e8bd'
  @        WHEN 3 THEN '#cfe8bd'
  @        WHEN 4 THEN '#cacae5'
  @        ELSE '#c8c8c8' END as 'bgcolor',
  @ ...
  @ FROM ticket
  @ 
#if 0 @

You can, of course, substitute different colors if you choose. @ Here is a palette of suggested background colors:

@
@ @ @ @ @ @ @ @ @ @ @ @ @ @
#ffbdbd#f2dcdc
#ffffbd#e8e8bd
#c0ebc0#cfe8bd
#c0c0f4#d6d6e8
#d0b1ff#d2c0db
#bbbbbb#d0d0d0
@
#endif @

To see the TICKET.DESCRIPTION and TICKET.REMARKS fields, include @ them as the last two columns of the result set and given them names @ that begin with an underscore. Like this:

@
  @  SELECT
  @    tn AS '#',
  @    type AS 'Type',
  @    status AS 'Status',
  @    sdate(origtime) AS 'Created',
  @    owner AS 'By',
  @    subsystem AS 'Subsys',
  @    sdate(changetime) AS 'Changed',
  @    assignedto AS 'Assigned',
  @    severity AS 'Svr',
  @    priority AS 'Pri',
  @    title AS 'Title',
  @    description AS '_Description',   -- When the column name begins with '_'
  @    remarks AS '_Remarks'            -- the data is shown on a separate row.
  @  FROM ticket
  @ 
@ @

Or, to see part of the description on the same row, use the @ wiki() function with some string manipulation. Using the @ tkt() function on the ticket number will also generate a linked @ field, but without the extra edit column: @

@
  @  SELECT
  @    tkt(tn) AS '',
  @    title AS 'Title',
  @    wiki(substr(description,0,80)) AS 'Description'
  @  FROM ticket
  @ 
@ } /*********************************************************************/ static void output_report_field(const char *zData,int rn){ const char *zWkey = wiki_key(); const char *zTkey = tkt_key(); const char *zCkey = chng_key(); if( !strncmp(zData,zWkey,strlen(zWkey)) ){ output_formatted(&zData[strlen(zWkey)],0); }else if( !strncmp(zData,zTkey,strlen(zTkey)) ){ output_ticket(atoi(&zData[strlen(zTkey)]),rn); }else if( !strncmp(zData,zCkey,strlen(zCkey)) ){ output_chng(atoi(&zData[strlen(zCkey)])); }else{ @ %h(zData) } } static void column_header(int rn,const char *zCol, int nCol, int nSorted, const char *zDirection, const char *zExtra ){ int set = (nCol==nSorted); int desc = !strcmp(zDirection,"DESC"); /* ** Clicking same column header 3 times in a row resets any sorting. ** Note that we link to rptview, which means embedded reports will get ** sent to the actual report view page as soon as a user tries to do ** any sorting. I don't see that as a Bad Thing. */ if(set && desc){ @ @ %h(zCol) }else{ if(set){ @ %h(zCol) } } /*********************************************************************/ struct GenerateHTML { int rn; int nCount; }; /* ** The callback function for db_query */ static int generate_html( void* pUser, /* Pointer to output state */ int nArg, /* Number of columns in this result row */ char **azArg, /* Text of data in all columns */ char **azName /* Names of the columns */ ){ struct GenerateHTML* pState = (struct GenerateHTML*)pUser; int i; int tn; /* Ticket number. (value of column named '#') */ int rn; /* Report number */ int ncol; /* Number of columns in the table */ int multirow; /* True if multiple table rows per line of data */ int newrowidx; /* Index of first column that goes on a separate row */ int iBg = -1; /* Index of column that determines background color */ char *zBg = 0; /* Use this background color */ char zPage[30]; /* Text version of the ticket number */ /* Get the report number */ rn = pState->rn; /* Figure out the number of columns, the column that determines background ** color, and whether or not this row of data is represented by multiple ** rows in the table. */ ncol = 0; multirow = 0; newrowidx = -1; for(i=0; inCount==0 ){ char zExtra[2000]; int nField = atoi(PD("order_by","0")); const char* zDir = PD("order_dir",""); zDir = !strcmp("ASC",zDir) ? "ASC" : "DESC"; zExtra[0] = 0; if( g.nAux ){ @ @
@ for(i=0; i } } @ @ } @ tn = -1; for(i=0; i=0 && i>=newrowidx ){ if( g.okWrite && tn>=0 ){ @   tn = -1; } if( zName[0]=='_' ) zName++; @ %h(zName) }else{ if( zName[0]=='#' ){ tn = i; } /* ** This handles any sorting related stuff. Note that we don't ** bother trying to sort on the "wiki format" columns. I don't ** think it makes much sense, visually. */ column_header(rn,azName[i],i+1,nField,zDir,zExtra); } } if( g.okWrite && tn>=0 ){ @   } @ } if( azArg==0 ){ @ @ No records match the report criteria @ return 0; } ++pState->nCount; /* Output the separator above each entry in a table which has multiple lines ** per database entry. */ if( newrowidx>=0 ){ @   } /* Output the data for this entry from the database */ if( zBg==0 ) zBg = "white"; @ tn = 0; zPage[0] = 0; for(i=0; i=0 && i>=newrowidx ){ if( tn>0 && g.okWrite ){ @ edit tn = 0; } if( zData[0] ){ @ output_formatted(zData, zPage[0] ? zPage : 0); } }else if( azName[i][0]=='#' ){ tn = atoi(zData); if( tn>0 ) bprintf(zPage, sizeof(zPage), "%d", tn); @ %h(zData) }else if( zData[0]==0 ){ @   }else{ @ output_report_field(zData,rn); @ } } if( tn>0 && g.okWrite ){ @ edit } @ return 0; } /* ** Output the text given in the argument. Convert tabs and newlines into ** spaces. */ static void output_no_tabs(const char *z){ while( z && z[0] ){ int i, j; for(i=0; z[i] && (!isspace(z[i]) || z[i]==' '); i++){} if( i>0 ){ cgi_printf("%.*s", i, z); } for(j=i; isspace(z[j]); j++){} if( j>i ){ cgi_printf("%*s", j-i, ""); } z += j; } } /* ** Output a row as a tab-separated line of text. */ static int output_tab_separated( void *pUser, /* Pointer to row-count integer */ int nArg, /* Number of columns in this result row */ char **azArg, /* Text of data in all columns */ char **azName /* Names of the columns */ ){ int *pCount = (int*)pUser; int i; if( *pCount==0 ){ for(i=0; i if( horiz ){ @ } zToFree = zSafeKey = mprintf("%h", zClrKey); while( zSafeKey[0] ){ while( isspace(*zSafeKey) ) zSafeKey++; for(i=0; zSafeKey[i] && !isspace(zSafeKey[i]); i++){} for(j=i; isspace(zSafeKey[j]); j++){} for(k=j; zSafeKey[k] && zSafeKey[k]!='\n' && zSafeKey[k]!='\r'; k++){} if( !horiz ){ cgi_printf("%.*s\n", i, zSafeKey, k-j, &zSafeKey[j]); }else{ cgi_printf("%.*s\n", i, zSafeKey, k-j, &zSafeKey[j]); } zSafeKey += k; } free(zToFree); if( horiz ){ @ } @ } /* ** WEBPAGE: /rptview ** ** Generate a report. The rn query parameter is the report number ** corresponding to REPORTFMT.RN. If the tablist query parameter exists, ** then the output consists of lines of tab-separated fields instead of ** an HTML table. */ void rptview_page(void){ int count = 0; int rn; char *zSql; char *zTitle; char *zOwner; char *zClrKey; int tabs; Stmt q; login_check_credentials(); if( !g.okRead ){ login_needed(); return; } rn = atoi(PD("rn","0")); if( rn==0 ){ cgi_redirect("reportlist"); return; } tabs = P("tablist")!=0; view_add_functions(tabs); db_prepare(&q, "SELECT title, sqlcode, owner, cols FROM reportfmt WHERE rn=%d", rn); if( db_step(&q)!=SQLITE_ROW ){ cgi_redirect("reportlist"); return; } zTitle = db_column_malloc(&q, 0); zSql = db_column_malloc(&q, 1); zOwner = db_column_malloc(&q, 2); zClrKey = db_column_malloc(&q, 3); db_finalize(&q); if( P("order_by") ){ /* ** If the user wants to do a column sort, wrap the query into a sub ** query and then sort the results. This is a whole lot easier than ** trying to insert an ORDER BY into the query itself, especially ** if the query is already ordered. */ int nField = atoi(P("order_by")); if( nField > 0 ){ const char* zDir = PD("order_dir",""); zDir = !strcmp("ASC",zDir) ? "ASC" : "DESC"; zSql = mprintf("SELECT * FROM (%s) ORDER BY %d %s", zSql, nField, zDir); } } count = 0; if( !tabs ){ struct GenerateHTML sState; db_execute("PRAGMA empty_result_callbacks=ON"); style_submenu_element("Raw", "Raw", "rptview?tablist=1&%s", P("QUERY_STRING","")); if( g.okAdmin || (g.okQuery && g.zLogin && zOwner && strcmp(g.zLogin,zOwner)==0) ){ style_submentu_element("Edit", "Edit", "rptedit?rn=%d", rn); } style_submenu_element("SQL", "SQL", "rptsql?rn=%d",rn); style_header(zTitle); output_color_key(zClrKey, 1, "border=0 cellpadding=3 cellspacing=0 class=\"report\""); @ sState.rn = rn; sState.nCount = 0; sqlite3_exec(g.db, zSql, generate_html, &sState, 0); @
style_footer(); }else{ sqlite3_exec(g.db, zSql, output_tab_separated, &count, 0); cgi_set_content_type("text/plain"); } }