/*
** Copyright (c) 2002 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/
**
*******************************************************************************
**
** This file contains routines used to interact with the database.
*/
#define _XOPEN_SOURCE
#include <unistd.h>
#include <time.h>
#include <assert.h>
#include <errno.h>
#include <sqlite3.h>
#include "config.h"
#include "db.h"

/*
** The following is the handle to the open database.
*/
static sqlite3 *pDb = 0;

void db_err(const char *zReason, const char *zQuery, const char *zFormat, ...){
  char *zMsg;

  va_list ap;
  va_start(ap, zFormat);
  zMsg = vmprintf(zFormat, ap);
  va_end(ap);
  cgi_reset_content();
  cgi_set_status(200, "OK");
  cgi_set_content_type("text/html");
  cgi_printf("<h1>Database Error</h1>\n"
         "%h\n",zMsg);
  if( zQuery ){
    cgi_printf("<blockquote>%h</blockquote>\n",zQuery);
  }
  if( zReason ){
    cgi_printf("Reason: %h\n",zReason);
  }
  cgi_append_header("Pragma: no-cache\r\n");
  cgi_reply();
  db_close();
  exit(0);
}

/*
** Open the database.
** Exit with an error message if the database will not open.
*/
sqlite3 *db_open(void){
  char *zName;

  if( pDb ) return pDb;
  zName = mprintf("%s.db", g.zName);
  if( SQLITE_OK!=sqlite3_open(zName, &pDb) ){
    db_err( sqlite3_errmsg(pDb), 0,
            "db_open: Unable to open the database named \"%h\"", zName );
    sqlite3_close(pDb);
  }
  sqlite3_busy_timeout(pDb, 10000);
  free(zName);
  return pDb;
}

/*
** Close the database
*/
void db_close(void){
  if( pDb ){
    sqlite3_close(pDb);
    pDb = 0;
  }
}

/*
** The SQL authorizer function for the user-supplies queries.  This
** routine NULLs-out fields of the database we do not want arbitrary
** users to see, such as the USER.PASSWD field.
*/
static int authorizer(
  void *NotUsed,
  int type,
  const char *zArg1,
  const char *zArg2,
  const char *zArg3,
  const char *zArg4
){
  extern int sqlite3StrICmp(const char*, const char*);
  if( type==SQLITE_SELECT ){
    return SQLITE_OK;
  }else if( type==SQLITE_FUNCTION ){
    return SQLITE_OK;
  }else if( type==SQLITE_READ ){
    if( sqlite3StrICmp(zArg1,"user")==0 ){
      if( sqlite3StrICmp(zArg2,"passwd")==0 || sqlite3StrICmp(zArg2,"email")==0 ){
        return SQLITE_IGNORE;
      }
    }else if( sqlite3StrICmp(zArg1, "cookie")==0 ){
      return SQLITE_IGNORE;
    }else if( sqlite3StrICmp(zArg1, "config")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okSetup && sqlite3StrICmp(zArg1, "access_load")==0 ){
      return SQLITE_IGNORE;
    }else if( (!g.okWrite || g.isAnon) && sqlite3StrICmp(zArg1,"ticket")==0
        && sqlite3StrICmp(zArg2,"contact")==0){
      return SQLITE_IGNORE;
    }else if( !g.okCheckout && sqlite3StrICmp(zArg1,"chng")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okCheckout && sqlite3StrICmp(zArg1,"filechng")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okCheckout && sqlite3StrICmp(zArg1,"file")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okCheckout && sqlite3StrICmp(zArg1,"inspect")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okRead && sqlite3StrICmp(zArg1,"ticket")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okRead && sqlite3StrICmp(zArg1,"tktchng")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okRdWiki && sqlite3StrICmp(zArg1,"attachment")==0 ){
      return SQLITE_IGNORE;
    }else if( !g.okRdWiki && sqlite3StrICmp(zArg1,"wiki")==0 ){
      return SQLITE_IGNORE;
    }
    return SQLITE_OK;
  }else{
    return SQLITE_DENY;
  }
}

/*
** Restrict access to sensitive information in the database. Returns previous
** state of the authorizer.
*/
int db_restrict_access(int onoff){
  static int state = 0;
  int laststate = state;
  if( state!=onoff ){
    state = onoff;
    sqlite3_set_authorizer(pDb, state ? authorizer : 0, 0);
  }
  return laststate;
}

/*
** Used to accumulate query results by db_query()
*/
struct QueryResult {
  int nElem;       /* Number of used entries in azElem[] */
  int nAlloc;      /* Number of slots allocated for azElem[] */
  int nCols;       /* Number of columns in the results */
  char **azElem;   /* The result of the query */
};

/*
** The callback function for db_query
*/
static int db_query_callback(
  void *pUser,     /* Pointer to the QueryResult structure */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **NotUsed   /* Names of the columns */
){
  struct QueryResult *pResult = (struct QueryResult*)pUser;
  int i;
  if( pResult->nCols==0 ){
    pResult->nCols = nArg;
  }else if( pResult->nCols!= nArg ){
    db_err("Mismatched number of columns in query results", 0,
           "db_query_callback: Database query failed");
  }
  if( pResult->nElem + nArg >= pResult->nAlloc ){
    if( pResult->nAlloc==0 ){
      pResult->nAlloc = nArg+1;
    }else{
      pResult->nAlloc = pResult->nAlloc*2 + nArg + 1;
    }
    pResult->azElem = realloc( pResult->azElem, pResult->nAlloc*sizeof(char*));
    if( pResult->azElem==0 ){
      exit(1);
    }
  }
  if( azArg==0 ) return 0;
  for(i=0; i<nArg; i++){
    pResult->azElem[pResult->nElem++] = mprintf("%s",azArg[i] ? azArg[i] : ""); 
  }
  return 0;
}

/*
** Execute a query against the database.  Return the
** results as a list of pointers to strings.  NULL values are returned
** as an empty string.  The list is terminated by a single NULL pointer.
**
** If anything goes wrong, an error page is generated and the program
** aborts.  If this routine will only return to its calling procedure
** if the query contained no errors.
*/
char **db_query(const char *zFormat, ...){
  int rc;
  char *zErrMsg = 0;
  va_list ap;
  struct QueryResult sResult;
  char *zSql;

  assert(zFormat!=0);

  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);

  if( pDb==0 ) db_open();
  memset(&sResult, 0, sizeof(sResult));
  rc = sqlite3_exec(pDb, zSql, db_query_callback, &sResult, &zErrMsg);
  if( rc != SQLITE_OK ){
    db_err( zErrMsg ? zErrMsg : sqlite3_errmsg(pDb), zSql,
            "db_query: Database query failed" );
  }
  free(zSql);
  if( sResult.azElem==0 ){
    db_query_callback(&sResult, 0, 0, 0);
  }
  assert(sResult.azElem!=0);
  sResult.azElem[sResult.nElem] = 0;
  return sResult.azElem;
}

/*
** The callback function for db_short_query.
**
** Save the first argument azArg[0] in to memory obtained from malloc(),
** make *pUser point to that memory, then return 1 to abort the
** query.
*/
static int db_short_query_callback(
  void *pUser,     /* Pointer to the QueryResult structure */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **NotUsed   /* Names of the columns */
){
  char **pzResult = (char**)pUser;
  if( nArg>0 && azArg ){
    if( *pzResult ) free(*pzResult);
    *pzResult = mprintf("%s", azArg[0]);
  }
  return 1;
}

/*
** Execute a query against the database.  Return a pointer to a single
** string which is the first result of that query.  Errors in the query
** are ignored.
**
** This routine is designed for use on queries that only return a 
** single value.  For multi-valued results, use db_query().
*/
char *db_short_query(const char *zFormat, ...){
  va_list ap;
  int rc;
  char *zResult = 0;
  char *zErrMsg = 0;
  char *zSql;

  assert(zFormat);
  if( pDb==0 ) db_open();
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat,ap);
  va_end(ap);
  rc = sqlite3_exec(pDb, zSql, db_short_query_callback, &zResult, &zErrMsg);

  /* short query callback aborts when we get a real value */
  if( rc != SQLITE_OK && rc != SQLITE_ABORT ){
    db_err( zErrMsg ? zErrMsg : sqlite3_errmsg(pDb), zSql,
            "db_short_query: Database query failed" );
  }
  free(zSql);
  return zResult;
}

/*
** Execute an SQL statement against the database.
** Print an error and abort if something goes wrong.
*/
void db_execute(const char *zFormat, ...){
  int rc;
  char *zErrMsg = 0;
  char *zSql;
  va_list ap;

  assert(zFormat);

  if( pDb==0 ) db_open();
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat,ap);
  va_end(ap);
  rc = sqlite3_exec(pDb, zSql, 0, 0, &zErrMsg);
  if( rc!=SQLITE_OK ){
    db_err(zErrMsg, zSql, "db_execute: Database execute failed");
  }
  free(zSql);
}

/*
** The callback function for db_exists.
*/
static int db_exists_callback(
  void *pUser,     /* Pointer to the QueryResult structure */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **NotUsed   /* Names of the columns */
){
  *((int*)pUser) = 1;
  return 0;
}

/*
** Execute the SQL query statement.  Return TRUE if the query
** would return 1 or more rows.  Return FALSE if the query returns
** an empty set.
*/
int db_exists(const char *zFormat, ...){
  va_list ap;
  int iResult = 0;
  char *zErrMsg = 0;
  char *zSql;
  int rc;

  assert(zFormat);
  if( pDb==0 ) db_open();
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat,ap);
  va_end(ap);
  rc = sqlite3_exec(pDb, zSql, db_exists_callback, &iResult, &zErrMsg);
  if( rc!=SQLITE_OK ){
    db_err(zErrMsg, zSql, "db_exists: Database exists query failed");
  }
  free(zSql);
  return iResult;
}

/*
** Check to see if the SQL query is valid. Return NULL if it's good, otherwise
** return an appropriate error message.
*/
char *db_query_check(const char *zFormat, ...){
  int rc;
  char *zErrMsg = 0;
  char *zSql;
  va_list ap;

  assert(zFormat);
  if( pDb==0 ) db_open();
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat,ap);
  va_end(ap);
  rc = sqlite3_exec(pDb, zSql, 0, 0, &zErrMsg);
  return (rc!=SQLITE_OK) ? zErrMsg : 0;
}

/*
** Free the results of a db_query() call.
*/
void db_query_free(char **az){
  int i;
  for(i=0; az[i]; i++){
    free(az[i]);
  }
  free(az);
}

/*
** The operation of querying the CONFIG table for a CONFIG.VALUE that
** corresponds to a particular CONFIG.NAME is so common, that we give it
** its own subroutine.
**
** The entire contents of the CONFIG table are cached.  If you write
** to the CONFIG table to change a value, this routine will not know
** about it and will return the old value.  You can clear the cache
** by calling this routine will a NULL zName parameter.
*/
const char *db_config(const char *zName, const char *zDefault){
  static char **azCache = 0;
  int i;
  if( azCache==0 && zName!=0 ){
    azCache = db_query("SELECT name, value FROM config");
  }
  if( zName==0 ){
    azCache = 0;
    return zDefault;
  }
  for(i=0; azCache[i]; i+=2){
    if( azCache[i][0]==zName[0] && strcmp(azCache[i],zName)==0 ){
      return azCache[i+1];
    }
  }
  return zDefault;
}

/*
** Execute a query against the database and invoke the
** given callback for each row.
*/
void db_callback_query(
  int (*xCallback)(void*,int,char**,char**),  /* Callback for each row */
  void *pArg,                                 /* 1st argument to callback */
  const char *zFormat,                        /* SQL for the query */
  ...                                         /* Arguments for the SQL */
){
  int rc;
  char *zErrMsg = 0;
  char *zSql;
  va_list ap;

  if( pDb==0 ) db_open();
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  rc = sqlite3_exec(pDb, zSql, xCallback, pArg, &zErrMsg);
  if( rc != SQLITE_OK ){
    db_err(zErrMsg ? zErrMsg : sqlite3_errmsg(pDb), zSql,
           "db_callback_query: Database query failed");
  }
  free(zSql);
}

/*
** Implement the sdate() SQL function.  sdate() converts an integer
** which is the number of seconds since 1970 into a short date or
** time description.  For recent dates (within the past 24 hours)
** just the time is shown.  (ex: 14:23)  For dates within the past
** year, the month and day are shown.  (ex: Apr09).  For dates more
** than a year old, only the year is shown.
*/
static void sdate(sqlite3_context *context, int argc, sqlite3_value **argv){
  time_t now;
  time_t t;
  struct tm *pTm;
  char *zFormat;
  char zBuf[200];
  
  if( argc!=1 ) return;
  t = sqlite3_value_int(argv[0]);
  if( t==0 ) return;
  time(&now);
  if( t+8*3600 > now && t-8*3600 <= now ){
    zFormat = "%H:%M";
  }else if( t+24*3600*120 > now && t-24*3600*120 < now ){
    zFormat = "%b %d";
  }else{
    zFormat = "%Y %b";
  }
  pTm = localtime(&t);
  strftime(zBuf, sizeof(zBuf), zFormat, pTm);
  sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}

/*
** Implement the ldate() SQL function.  ldate() converts an integer
** which is the number of seconds since 1970 into a full date and
** time description.
*/
static void ldate(sqlite3_context *context, int argc, sqlite3_value **argv){
  time_t t;
  struct tm *pTm;
  char zBuf[200];
  
  if( argc!=1 ) return;
  t = sqlite3_value_int(argv[0]);
  if( t==0 ) return;
  pTm = localtime(&t);
  strftime(zBuf, sizeof(zBuf), "%Y-%b-%d %H:%M", pTm);
  sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}

/*
** Implement the parsedate() SQL function.  parsedate() converts an
** ISO8601 date/time string into the number of seconds since 1970.
*/
static void pdate(sqlite3_context *context, int argc, sqlite3_value **argv){
  time_t t;
  const char *z;
  
  if( argc!=1 ) return;
  z = (const char *)sqlite3_value_text(argv[0]);
  t = z ? parse_time(z) : 0;
  sqlite3_result_int(context, t);
}

/*
** Implement the now() SQL function.  now() takes no arguments and
** returns the current time in seconds since 1970.
*/
static void f_now(sqlite3_context *context, int argc, sqlite3_value **argv){
  time_t now;
  time(&now);
  sqlite3_result_int(context, now);
}

/*
** Implement the user() SQL function.  user() takes no arguments and
** returns the user ID of the current user.
*/
static void f_user(sqlite3_context *context, int argc, sqlite3_value **argv){
  if( g.zUser!=0 ) sqlite3_result_text(context, g.zUser, -1, SQLITE_STATIC);
}

/*
** Implement the cgi() SQL function.  cgi() takes a an argument which is
** a name of CGI query parameter. The value of that parameter is returned, 
** if available. optional second argument will be returned if the first
** doesn't exist as a CGI parameter.
*/
static void f_cgi(sqlite3_context *context, int argc, sqlite3_value **argv){
  const char* zP;
  if( argc!=1 && argc!=2 ) return;
  zP = P((const char*)sqlite3_value_text(argv[0]));
  if( zP ){
    sqlite3_result_text(context, zP, -1, SQLITE_STATIC);
  }else if( argc==2 ){
    zP = (const char*)sqlite3_value_text(argv[1]);
    if( zP ) sqlite3_result_text(context, zP, -1, SQLITE_STATIC);
  }
}

/*
** Implement the aux() SQL function. The aux() SQL function takes a paramter
** name as an argument and returns the value that the user enters in the
** resulting HTML form. A second optional parameter provides a default value.
*/
static void f_aux(sqlite3_context *context, int argc, sqlite3_value **argv){
  int i;
  extern int sqlite3StrICmp(const char*, const char*);
  const char *zParm;
  if( argc!=1 && argc!=2 ) return;

  zParm = (const char*)sqlite3_value_text(argv[0]);
  if( zParm==0 ) return;

  for(i=0; i<g.nAux && g.azAuxName[i]; i++){
    if( sqlite3StrICmp(zParm,g.azAuxName[i])==0 ){
      if( g.azAuxVal[i] ){
        sqlite3_result_text(context, g.azAuxVal[i], -1, SQLITE_STATIC);
      }
      return;
    }
  }
  if( g.nAux<MX_AUX ){
    const char *zDef = (argc==2) ? (const char*)sqlite3_value_text(argv[1])
                                 : 0;
    g.azAuxName[g.nAux] = mprintf("%s",zParm);
    g.azAuxParam[g.nAux] = mprintf("%s",zParm);
    for(i=0; g.azAuxParam[g.nAux][i]; i++){
      if(!isalnum(g.azAuxParam[g.nAux][i])) g.azAuxParam[g.nAux][i]='_';
    }
    g.azAuxVal[g.nAux] = mprintf("%s",PD(g.azAuxParam[g.nAux],zDef));
    if( g.azAuxVal[g.nAux] ){
      sqlite3_result_text(context, g.azAuxVal[g.nAux], -1, SQLITE_STATIC);
    }
    g.nAux++;
  }
}

/*
** Implement the option() SQL function.  option() takes a parameter name
** and a quoted SELECT statement as parameters. The query results are
** presented as an HTML dropdown menu and the function returns the
** currently selected value.  Results may be a single value column or
** two value,description columns.  The first result row is the default.
*/
static void f_option(sqlite3_context *context, int argc, sqlite3_value **argv){
  const char *zParm;
  int i;
  extern int sqlite3StrICmp(const char*, const char*);
  if( argc!=1 && argc!=2 ) return;

  zParm = (const char*)sqlite3_value_text(argv[0]);
  if( zParm==0 ) return;

  for(i=0; i<g.nAux && g.azAuxName[i]; i++){
    if( sqlite3StrICmp(zParm,g.azAuxName[i])==0 ){
      if( g.azAuxVal[i] ){
        sqlite3_result_text(context, g.azAuxVal[i], -1, SQLITE_STATIC);
      }
      return;
    }
  }
  if( argc>1 && g.nAux<MX_AUX ){
    char *zErr;
    const char *zQuery = (const char*)sqlite3_value_text(argv[1]);
    if( zQuery==0 ) {
      db_err("Query cannot be NULL", "NULL", "Illegal option() query");
    }
    zErr = verify_sql_statement(zQuery);
    if( zErr ){
      db_err( zErr, zQuery, "Illegal option() query" );
    }else{
      int rc;
      struct QueryResult sResult;
      if( pDb==0 ) db_open();
      memset(&sResult, 0, sizeof(sResult));

      rc = sqlite3_exec(pDb, zQuery, db_query_callback, &sResult, &zErr);
      if( rc != SQLITE_OK ){
        db_err( zErr, zQuery, "option() query failed" );
      }
      
      if( sResult.azElem==0 ){
        db_query_callback(&sResult, 0, 0, 0);
      }
      sResult.azElem[sResult.nElem] = 0;

      g.azAuxOpt[g.nAux] = (const char**)sResult.azElem;
      g.anAuxCols[g.nAux] = sResult.nCols;
    }
    g.azAuxName[g.nAux] = mprintf("%s",zParm);
    g.azAuxParam[g.nAux] = mprintf("%s",zParm);
    for(i=0; g.azAuxParam[g.nAux][i]; i++){
      if(!isalnum(g.azAuxParam[g.nAux][i])) g.azAuxParam[g.nAux][i]='_';
    }
    g.azAuxVal[g.nAux] = PD(g.azAuxParam[g.nAux],
                            g.azAuxOpt[g.nAux][0]?g.azAuxOpt[g.nAux][0]:"");
    if( g.azAuxVal[g.nAux] ){
      sqlite3_result_text(context, g.azAuxVal[g.nAux], -1, SQLITE_STATIC);
    }
    g.nAux++;
  }
}
/*
** Implement the path() SQL function. path() takes 3 arguments in this order:
**   isdir, dir, base.
** This func should be used to extract complete filename from FILE table.
*/
static void f_path(sqlite3_context *context, int argc, sqlite3_value **argv){
  char *zPath;
  const char *zDir;
  if( argc!=3 ) return;
  if( SQLITE_NULL==sqlite3_value_type(argv[0])
                 || SQLITE_NULL==sqlite3_value_type(argv[1])
                 || SQLITE_NULL==sqlite3_value_type(argv[2])){
    return;
  }
  zDir = (const char*)sqlite3_value_text(argv[1]);
  if( 0==sqlite3_value_int(argv[0]) ){
    if( zDir && strlen(zDir)>0 ){
      zPath = mprintf("%s/%s", zDir, (const char*)sqlite3_value_text(argv[2]));
    }else{
      zPath = mprintf("%s", (const char*)sqlite3_value_text(argv[2]));
    }
  }else{
    if( zDir && strlen(zDir)>0 ){
      zPath = mprintf("%s/%s/", zDir, (const char*)sqlite3_value_text(argv[2]));
    }else{
      zPath = mprintf("%s/", (const char*)sqlite3_value_text(argv[2]));
    }
  }
  sqlite3_result_text(context, zPath, -1, SQLITE_TRANSIENT);
  free(zPath);
}

/*
** Implement the dirname() SQL function. dirname() returns dirname for given
** filename WITH trailing slash, except when dirname is empty string.
*/
static void f_dirname(sqlite3_context *context, int argc, sqlite3_value **argv){
  const char *z;
  char *zDir;
  int i;
  if( argc!=1 ) return;
  z = (const char*)sqlite3_value_text(argv[0]);
  if( z==0 || z[0]==0 ) return;
  i = strlen(z)-1;
  if( i<=0 ){
    sqlite3_result_text(context, "", -1, SQLITE_TRANSIENT);
    return;
  }
  zDir = mprintf("%s", z);
  if( z[i]=='/' ){ i--; } /* We need to handle dirs too*/
  while( i>=0 && zDir[i]!='/' ){ i--; }
  if( i==0 ){
    zDir = mprintf("");
  }else{
    zDir[i+1] = 0;
  }
  sqlite3_result_text(context, zDir, -1, SQLITE_TRANSIENT);
  free(zDir);
}

/*
** Implement the basename() SQL function. basename() extracts basename
** from given filename.
*/
static void f_basename(sqlite3_context *context, int argc, sqlite3_value **argv){
  const char *z;
  char *zBase;
  int i, nBaseEnd;
  if( argc!=1 ) return;
  z = (const char*)sqlite3_value_text(argv[0]);
  if( z==0 || z[0]==0 ) return;
  i = strlen(z)-1;
  if( i<=0 ){
    sqlite3_result_text(context, "", -1, SQLITE_TRANSIENT);
    return;
  }
  nBaseEnd = (z[i]=='/') ? --i : i;
  while( i>=0 && z[i]!='/' ){ i--; }
  zBase = mprintf("%s", &z[i+1]);
  /* Strip trailing slash in case of directories. NOOP for files. */
  zBase[nBaseEnd-i] = 0;
  sqlite3_result_text(context, zBase, -1, SQLITE_TRANSIENT);
  free(zBase);
}

/*
** The two arguments are capability strings: strings containing lower
** case letters.  Return a string that contains only those letters found
** in both arguments.
**
** Example:  cap_and("abcd","cdef") returns "cd".
*/
static void cap_and(sqlite3_context *context, int argc, sqlite3_value **argv){
  int i, j;
  const char *z;
  char set[26];
  char zResult[27];
  if( argc!=2 ) return;
  for(i=0; i<26; i++) set[i] = 0;
  z = (const char*)sqlite3_value_text(argv[0]);
  if( z ){
    for(i=0; z[i]; i++){
      if( z[i]>='a' && z[i]<='z' ) set[z[i]-'a'] = 1;
    }
  }
  z = (const char*)sqlite3_value_text(argv[1]);
  if( z ){
    for(i=0; z[i]; i++){
      if( z[i]>='a' && z[i]<='z' && set[z[i]-'a']==1 ) set[z[i]-'a'] = 2;
    }
  }
  for(i=j=0; i<26; i++){
    if( set[i]==2 ) zResult[j++] = i+'a';
  }
  zResult[j] = 0;
  sqlite3_result_text(context, zResult, -1, SQLITE_TRANSIENT);
}

/*
** The two arguments are capability strings: strings containing lower
** case letters.  Return a string that contains those letters found
** in either arguments.
**
** Example:  cap_and("abcd","cdef") returns "abcdef".
*/
static void cap_or(sqlite3_context *context, int argc, sqlite3_value **argv){
  int i, j;
  const char *z;
  char set[26];
  char zResult[27];
  if( argc!=2 ) return;
  for(i=0; i<26; i++) set[i] = 0;
  z = (const char*)sqlite3_value_text(argv[0]);
  if( z ){
    for(i=0; z[i]; i++){
      if( z[i]>='a' && z[i]<='z' ) set[z[i]-'a'] = 1;
    }
  }
  z = (const char*)sqlite3_value_text(argv[1]);
  if( z ){
    for(i=0; z[i]; i++){
      if( z[i]>='a' && z[i]<='z' ) set[z[i]-'a'] = 1;
    }
  }
  for(i=j=0; i<26; i++){
    if( set[i] ) zResult[j++] = i+'a';
  }
  zResult[j] = 0;
  sqlite3_result_text(context, zResult, -1, SQLITE_TRANSIENT);
}

/*
** This routine adds the extra SQL functions to the SQL engine.
*/
void db_add_functions(void){
  if( pDb==0 ) db_open();
  sqlite3_create_function(pDb, "sdate", 1, SQLITE_ANY, 0, &sdate, 0, 0);
  sqlite3_create_function(pDb, "ldate", 1, SQLITE_ANY, 0, &ldate, 0, 0);
  sqlite3_create_function(pDb, "parsedate", 1, SQLITE_ANY, 0, &pdate, 0, 0);
  sqlite3_create_function(pDb, "now", 0, SQLITE_ANY, 0, &f_now, 0, 0);
  sqlite3_create_function(pDb, "user", 0, SQLITE_ANY, 0, &f_user, 0, 0);
  sqlite3_create_function(pDb, "aux", 1, SQLITE_ANY, 0, &f_aux, 0, 0);
  sqlite3_create_function(pDb, "aux", 2, SQLITE_ANY, 0, &f_aux, 0, 0);
  sqlite3_create_function(pDb, "cgi", 1, SQLITE_ANY, 0, &f_cgi, 0, 0);
  sqlite3_create_function(pDb, "cgi", 2, SQLITE_ANY, 0, &f_cgi, 0, 0);
  sqlite3_create_function(pDb, "option", 1, SQLITE_ANY, 0, &f_option, 0, 0);
  sqlite3_create_function(pDb, "option", 2, SQLITE_ANY, 0, &f_option, 0, 0);
  sqlite3_create_function(pDb, "path", 3, SQLITE_ANY, 0, &f_path, 0, 0);
  sqlite3_create_function(pDb, "dirname", 1, SQLITE_ANY, 0, &f_dirname, 0, 0);
  sqlite3_create_function(pDb, "basename", 1, SQLITE_ANY, 0, &f_basename, 0, 0);
  sqlite3_create_function(pDb, "cap_or", 2, SQLITE_ANY, 0, &cap_or, 0, 0);
  sqlite3_create_function(pDb, "cap_and", 2, SQLITE_ANY, 0, &cap_and, 0, 0);
  sqlite3_create_function(pDb, "search", -1, SQLITE_ANY, 0, srchFunc, 0, 0);
}

/*
** The (original version 1.0) database schema is defined by the following SQL.
** Changes are made for subsequent versions.  See the zSchemaChange...
** strings defined later on in this file for the details on the changes.
*/
static char zSchema[] =
"BEGIN TRANSACTION;\n"
"\n"




"CREATE TABLE chng(\n"
"   cn integer primary key,\n"
"   date int,\n"
"   branch text,\n"
"   milestone int,\n"
"   user text,\n"
"   message text\n"
");\n"
"CREATE INDEX chng_idx ON chng(user,message);\n"
"\n"



"CREATE TABLE filechng(\n"
"   cn int,\n"
"   filename text,\n"
"   vers text,\n"
"   nins int, ndel int\n"
");\n"
"CREATE INDEX filechng_idx ON filechng(cn);\n"
"\n"



"CREATE TABLE ticket(\n"
"   tn integer primary key,\n"
"   type text,\n"
"   status text,\n"

"   origtime int,\n"
"   changetime int,\n"
"   derivedfrom int,\n"
"   version text,\n"
"   assignedto text,\n"
"   severity int,\n"
"   priority text,\n"
"   subsystem text,\n"
"   owner text,\n"
"   title text,\n"
"   description text,\n"
"   remarks text,\n"
"   contact text\n"
");\n"
"\n"


"CREATE TABLE tktchng(\n"
"   tn int,\n"
"   user text,\n"
"   chngtime int,\n"
"   fieldid text,\n"
"   oldval text,\n"
"   newval text\n"
");\n"
"CREATE INDEX tktchng_idx1 ON tktchng(tn, chngtime);\n"
"\n"


"CREATE TABLE config(\n"
"   name text primary key,\n"
"   value text\n"
");\n"
"INSERT INTO config(name,value) VALUES('cvsroot','');\n"
"INSERT INTO config(name,value) VALUES('historysize',0);\n"
"INSERT INTO config(name,value) VALUES('initial_state','new');\n"
"INSERT INTO config(name,value) VALUES('schema','1.0');\n"
"\n"



"CREATE TABLE user(\n"
"   id text primary key,\n"
"   name text,\n"
"   email text,\n"
"   passwd text,\n"
"   notify text,\n"
"   http text,\n"
"   capabilities text\n"
");\n"
"INSERT INTO user(id,name,email,passwd,capabilities)\n"
"  VALUES('setup','Setup Account',NULL,'aISQuNAAoY3qw','ainoprsw');\n"
"\n"



"CREATE TABLE reportfmt(\n"
"   rn integer primary key,\n"
"   owner text,\n"
"   title text,\n"
"   cols text,\n"
"   sqlcode text\n"
");\n"
"\n"


"INSERT INTO reportfmt VALUES(1,NULL,\n"
"  'Recently changed and open tickets',\n"
"  '#ffffff Key:\n"
"#f2dcdc Active\n"
"#e8e8bd Review\n"
"#cfe8bd Fixed\n"
"#bdefd6 Tested\n"
"#cacae5 Deferred\n"
"#c8c8c8 Closed',\n"
"  \"SELECT\n"
"  CASE WHEN status IN ('new','active') THEN '#f2dcdc'\n"
"       WHEN status='review' THEN '#e8e8bd'\n"
"       WHEN status='fixed' THEN '#cfe8bd'\n"
"       WHEN status='tested' THEN '#bde5d6'\n"
"       WHEN status='defer' THEN '#cacae5'\n"
"       ELSE '#c8c8c8' END as 'bgcolor',\n"
"  tn AS '#',\n"
"  type AS 'Type',\n"
"  status AS 'Status',\n"
"  sdate(origtime) AS 'Created',\n"
"  owner AS 'By',\n"
"  subsystem AS 'Subsys',\n"
"  sdate(changetime) AS 'Changed',\n"
"  assignedto AS 'Assigned',\n"
"  severity AS 'Svr',\n"
"  priority AS 'Pri',\n"
"  title AS 'Title'\n"
"FROM ticket\n"
"WHERE changetime>now()-604800 OR status IN ('new','active')\n"
"ORDER BY changetime DESC\");\n"

"INSERT INTO reportfmt VALUES(2,NULL,\n"
"  'Recently changed and open tickets w/description and remarks',\n"
"  '#ffffff Key:\n"
"#f2dcdc Active\n"
"#e8e8bd Review\n"
"#cfe8bd Fixed\n"
"#bdefd6 Tested\n"
"#cacae5 Deferred\n"
"#c8c8c8 Closed',\n"
"  \"SELECT\n"
"  CASE WHEN status IN ('new','active') THEN '#f2dcdc'\n"
"       WHEN status='review' THEN '#e8e8bd'\n"
"       WHEN status='fixed' THEN '#cfe8bd'\n"
"       WHEN status='tested' THEN '#bde5d6'\n"
"       WHEN status='defer' THEN '#cacae5'\n"
"       ELSE '#c8c8c8' END as 'bgcolor',\n"
"  tn AS '#',\n"
"  type AS 'Type',\n"
"  status AS 'Status',\n"
"  sdate(origtime) AS 'Created',\n"
"  owner AS 'By',\n"
"  subsystem AS 'Subsys',\n"
"  sdate(changetime) AS 'Changed',\n"
"  assignedto AS 'Assigned',\n"
"  severity AS 'Svr',\n"
"  priority AS 'Pri',\n"
"  title AS 'Title',\n"
"  description AS '_Description',\n"
"  remarks AS '_Remarks'\n"
"FROM ticket\n"
"WHERE changetime>now()-604800 OR status IN ('new','active')\n"
"ORDER BY changetime DESC\");\n"

"INSERT INTO reportfmt VALUES(3,NULL,\n"
"  'Tickets associated with a particular user',\n"
"  '#ffffff Priority:\n"
"#f2dcdc 1\n"
"#e8e8bd 2\n"
"#cfe8bd 3\n"
"#cacae5 4\n"
"#c8c8c8 5',\n"
"  \"SELECT\n"
"  CASE priority WHEN 1 THEN '#f2dcdc'\n"
"       WHEN 2 THEN '#e8e8bd'\n"
"       WHEN 3 THEN '#cfe8bd'\n"
"       WHEN 4 THEN '#cacae5'\n"
"       ELSE '#c8c8c8' END as 'bgcolor',\n"
"  tn AS '#',\n"
"  type AS 'Type',\n"
"  status AS 'Status',\n"
"  sdate(origtime) AS 'Created',\n"
"  owner AS 'By',\n"
"  subsystem AS 'Subsys',\n"
"  sdate(changetime) AS 'Changed',\n"
"  assignedto AS 'Assigned',\n"
"  severity AS 'Svr',\n"
"  priority AS 'Pri',\n"
"  title AS 'Title'\n"
"FROM ticket\n"
"WHERE owner=aux('User',user()) OR assignedto=aux('User',user())\");\n"

"INSERT INTO reportfmt VALUES(4,NULL,'All Tickets',\n"
"  '#ffffff Key:\n"
"#f2dcdc Active\n"
"#e8e8bd Review\n"
"#cfe8bd Fixed\n"
"#bdefd6 Tested\n"
"#cacae5 Deferred\n"
"#c8c8c8 Closed',\n"
"  \"SELECT\n"
"  CASE WHEN status IN ('new','active') THEN '#f2dcdc'\n"
"       WHEN status='review' THEN '#e8e8bd'\n"
"       WHEN status='fixed' THEN '#cfe8bd'\n"
"       WHEN status='tested' THEN '#bde5d6'\n"
"       WHEN status='defer' THEN '#cacae5'\n"
"       ELSE '#c8c8c8' END as 'bgcolor',\n"
"  tn AS '#',\n"
"  type AS 'Type',\n"
"  status AS 'Status',\n"
"  sdate(origtime) AS 'Created',\n"
"  owner AS 'By',\n"
"  subsystem AS 'Subsys',\n"
"  sdate(changetime) AS 'Changed',\n"
"  assignedto AS 'Assigned',\n"
"  severity AS 'Svr',\n"
"  priority AS 'Pri',\n"
"  title AS 'Title'\n"
"FROM ticket\");\n"

"INSERT INTO reportfmt VALUES(5,NULL,'Tickets counts',NULL,\"SELECT\n"
"  status,\n"
"  count(case when type='code' then 'x' end),\n"
"  count(case when type='doc' then 'x' end),\n"
"  count(case when type='new' then 'x' end),\n"
"  count(case when type NOT IN ('code','doc','new') then 'x' end),\n"
"  count(*)\n"
"FROM ticket GROUP BY status\n"
"UNION\n"
"SELECT\n"
"  'TOTAL' AS 'Status',\n"
"  count(case when type='code' then 'x' end) as 'Code Bugs',\n"
"  count(case when type='doc' then 'x' end) as 'Doc Bugs',\n"
"  count(case when type='new' then 'x' end) as 'Enhancements',\n"
"  count(case when type NOT IN ('code','doc','new') then 'x' end)\n"
"     as 'Other',\n"
"  count(*) AS 'All Types'\n"
"FROM ticket\n"
"ORDER BY [All Types]\");\n"

"\n"



"CREATE TABLE subsyst(name text);\n"
"INSERT INTO subsyst VALUES('Unknown');\n"
"\n"


"CREATE TABLE file(\n"
"   isdir boolean,\n"
"   base text,\n"
"   dir text,\n"
"   unique(dir,base)\n"
");\n"
"COMMIT;\n"
;

/*
** The previous variable was the original schema version 1.0. The following SQL
** code converts the schema to version 1.1.
**
** The XREF table creates a mapping from check-ins to tickets.  The
** mapping is many-to-many.  This mapping is used to show which tickets
** are effected by a check-in and which check-ins are related to a 
** particular ticket.
*/
static char zSchemaChange_1_1[] =
"BEGIN;\n"
"CREATE TABLE xref(\n"
"  tn int,\n"
"  cn int\n"
");\n"
"CREATE INDEX xref_idx1 ON xref(tn);\n"
"CREATE INDEX xref_idx2 ON xref(cn);\n"
"UPDATE config SET value='1.1' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 1.2.  The
** cookie table contains login cookies.
**
** Two big changes:  First, add the "cookie" table.  This table records
** the HTTP cookies used to login.  Second, the "wiki" table is added
** to store Wiki pages. 
*/
static char zSchemaChange_1_2[] =
"BEGIN;\n"
"CREATE TABLE cookie(\n"
"  cookie char(32) primary key,\n"
"  user text,\n"
"  expires int,\n"
"  ipaddr varchar(32),\n"
"  agent text\n"
");\n"
"CREATE TABLE wiki(\n"
"  name text,\n"
"  invtime int,\n"
"  locked boolean,\n"
"  who text,\n"
"  ipaddr text,\n"
"  text clob,\n"
"  UNIQUE(name,invtime)\n"
");\n"
"UPDATE config SET value='1.2' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 1.3.  A
** new table is added to hold attachments to tickets.
*/
static char zSchemaChange_1_3[] =
"BEGIN;\n"
"CREATE TABLE attachment(\n"
"  atn integer primary key,\n"
"  tn int,\n"
"  size int,\n"
"  date int,\n"
"  user text,\n"
"  mime text,\n"
"  fname text,\n"
"  content blob\n"
");\n"
"CREATE INDEX attachment_idx1 ON attachment(tn);\n"
"UPDATE config SET value='1.3' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 1.4.  There
** are three major changes.
**
**    1.  Indices are restructured for more efficient operation, especially
**        of the "timeline" function.
**
**    2.  The new ENUMS table is added.  This table contains allowed values
**        for various columns in the TICKET table.  The set of allowed values
**        used to be fixed.  But now users can edit them.  The old SUBSYST
**        table is deleted because it is now subsumed into the ENUMS table.
**
**    3.  The extra1 thru extra5 columns are added to the TICKET table and
**        the order of some of the columns is changed.  The new columns will
**        be used to implement user-defined ticket attributes.
*/
static char zSchemaChange_1_4[] =
"BEGIN;\n"
"\n"



"DROP INDEX chng_idx;\n"
"CREATE INDEX chng_idx1 ON chng(date,user);\n"
"DROP INDEX filechng_idx;\n"
"CREATE INDEX filechng_idx1 ON filechng(cn, filename);\n"
"DROP INDEX tktchng_idx1;\n"
"CREATE INDEX tktchng_idx1 ON tktchng(chngtime);\n"
"CREATE INDEX wiki_idx1 ON wiki(invtime);\n"
"\n"



"CREATE TABLE enums(\n"
"  type text,\n"
"  idx int,\n"
"  name text,\n"
"  value text,\n"
"  color text\n"
");\n"
"CREATE INDEX enums_idx1 ON enums(type, idx);\n"
"CREATE INDEX enums_idx2 ON enums(name, type);\n"
"INSERT INTO enums VALUES('status',1,'new','New','#f2dcdc');\n"
"INSERT INTO enums VALUES('status',2,'review','Review','#e8e8bd');\n"
"INSERT INTO enums VALUES('status',3,'defer','Defer','#cacae5');\n"
"INSERT INTO enums VALUES('status',4,'active','Active','#f2dcdc');\n"
"INSERT INTO enums VALUES('status',5,'fixed','Fixed','#cfe8bd');\n"
"INSERT INTO enums VALUES('status',6,'tested','Tested','#bde5d6');\n"
"INSERT INTO enums VALUES('status',7,'closed','Closed','#c8c8c8');\n"
"INSERT INTO enums VALUES('type',1,'code','Code Defect','#f2dcdc');\n"
"INSERT INTO enums VALUES('type',2,'doc','Documentation','#e8e8bd');\n"
"INSERT INTO enums VALUES('type',3,'todo','Action Item','#cacae5');\n"
"INSERT INTO enums VALUES('type',4,'new','Enhancement','#cfe8bd');\n"
"INSERT INTO enums VALUES('type',5,'event','Incident','#c8c8c8');\n"
"INSERT INTO enums SELECT 'subsys', rowid, name, name, '' FROM subsyst;\n"
"DROP TABLE subsyst;\n"
"\n"





"CREATE TEMP TABLE old_ticket AS SELECT * FROM ticket;\n"
"DROP TABLE ticket;\n"
"CREATE TABLE ticket(\n"
"   tn integer primary key,\n"
"   type text,\n"
"   status text,\n"

"   origtime int,\n"
"   changetime int,\n"
"   derivedfrom int,\n"
"   version text,\n"
"   assignedto text,\n"
"   severity int,\n"
"   priority text,\n"
"   subsystem text,\n"
"   owner text,\n"
"   title text,\n"
"   contact text,\n"
"   extra1 numeric,\n"
"   extra2 numeric,\n"
"   extra3 numeric,\n"
"   extra4 numeric,\n"
"   extra5 numeric,\n"
"   description text,\n"
"   remarks text\n"
");\n"
"CREATE INDEX ticket_idx1 ON ticket(origtime);\n"
"INSERT INTO ticket(tn,type,status,origtime,changetime,derivedfrom,\n"
"                   version,assignedto,severity,priority,subsystem,\n"
"                   owner,title,description,remarks,contact)\n"
"      SELECT * FROM old_ticket;\n"
"\n"


"UPDATE config SET value='1.4' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following changes occur to schema 1.5:
**
**   *  Add the INSPECT table used to record the occurance of inspections
**      of check-ins and the results of those inspections.
**
**   *  Create a new index on TKTCHNG that allows one to search by
**      ticket number
**
**   *  The ACCESS_LOAD table added to record the access history by IP
**      address which is then used to throttle unauthorized spiders.
*/
static char zSchemaChange_1_5[] =
"BEGIN;\n"
"\n"



"CREATE TABLE inspect(\n"
"  cn integer not null,\n"
"  inspecttime int not null,\n"
"  inspector text not null,\n"
"  ticket int,\n"
"  result text\n"
");\n"
"CREATE INDEX inspect_idx1 ON inspect(inspecttime);\n"
"CREATE INDEX inspect_idx2 ON inspect(cn);\n"
"\n"


"CREATE INDEX tktchng_idx2 ON tktchng(tn);\n"
"\n"


"CREATE TABLE access_load(\n"
"  ipaddr text primary key,\n"
"  lastaccess time,\n"
"  load real\n"
");\n"
"\n"


"UPDATE config SET value='1.5' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following changes occur to schema 1.6:
**
**   *  Add a captcha counter to the ACCESS_LOAD table.
**
**   *  Add a "description" field to the ATTACHMENT table.
**
**   *  Add a "directory" field to the CHNG table for milestones.
*/
static char zSchemaChange_1_6[] =
"BEGIN;\n"
"\n"

"CREATE TEMP TABLE old_access_load AS SELECT * FROM access_load;\n"
"DROP TABLE access_load;\n"
"CREATE TABLE access_load(\n"
"  ipaddr text primary key,\n"
"  lastaccess time,\n"
"  load real,\n"
"  captcha int\n"
");\n"
"INSERT INTO access_load(ipaddr,lastaccess,load)\n"
"      SELECT * FROM old_access_load;\n"
"\n"

"CREATE TEMP TABLE old_attachment AS SELECT * FROM attachment;\n"
"DROP TABLE attachment;\n"
"CREATE TABLE attachment(\n"
"  atn integer primary key,\n"
"  tn int,\n"
"  size int,\n"
"  date int,\n"
"  user text,\n"
"  mime text,\n"
"  fname text,\n"
"  description text,\n"
"  content blob\n"
");\n"
"INSERT INTO attachment(atn,tn,size,date,user,mime,fname,content)\n"
"      SELECT * FROM old_attachment;\n"
"\n"

"CREATE TEMP TABLE old_chng AS SELECT * FROM chng;\n"
"DROP TABLE chng;\n"
"CREATE TABLE chng(\n"
"   cn integer primary key,\n"
"   date int,\n"
"   branch text,\n"
"   milestone int,\n"
"   user text,\n"
"   directory text,\n"
"   message text\n"
");\n"
"INSERT INTO chng(cn,date,branch,milestone,user,message)\n"
"      SELECT * FROM old_chng;\n"
"\n"


"UPDATE config SET value='1.6' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 1.7.  A
** new table is added to hold custom Wiki markup styles
*/
static char zSchemaChange_1_7[] =
"BEGIN;\n"
"CREATE TABLE markup(\n"
"  markup text primary key,\n"
"  type int,\n"

"  formatter text,\n"
"  description text\n"
");\n"
"CREATE INDEX markup_idx1 ON markup(markup);\n"
"UPDATE config SET value='1.7' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 1.8.
**
**   *  add chngtype and prevvers fields to the FILECHNG table
** 
*/
static char zSchemaChange_1_8[] =
"BEGIN;\n"
"CREATE TEMP TABLE old_filechng AS SELECT * FROM filechng;\n"
"DROP TABLE filechng;\n"
"CREATE TABLE filechng(\n"
"   cn int,\n"
"   filename text,\n"
"   vers text,\n"
"   prevvers text,\n"
"   chngtype int,\n"
"   nins int, ndel int,\n"
"   UNIQUE(filename,vers)\n"
");\n"
"INSERT INTO filechng(cn,filename,vers,nins,ndel)\n"
"      SELECT cn,filename,vers,nins,ndel FROM old_filechng;\n"
"UPDATE config SET value='1.8' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 1.9. This version
** introduces an index on FILECHNG(filenane,vers), but the main goal is to
** fix the prevvers,chngtype problems incompleteness in schema 1.8. This
** requires recopying FILECHNG. Most of the real action happens in
** db_upgrade_schema_9(). Because we're basically replicating the changes
** in the original 1.8, 1.8 is now a NOP and is replaced.
**
**   *  add chngtype and prevvers fields to the FILECHNG table
**
**   *  initialize svnlastupdate in CONFIG
** 
*/
static char zSchemaChange_1_9[] =
"BEGIN;\n"
"CREATE TEMP TABLE old_filechng2 AS SELECT * FROM filechng;\n"
"DROP TABLE filechng;\n"
"CREATE TABLE filechng(\n"
"   cn int,\n"
"   filename text,\n"
"   vers text,\n"
"   prevvers text,\n"
"   chngtype int,\n"
"   nins int, ndel int,\n"
"   UNIQUE(filename,vers)\n"
");\n"
"COMMIT;\n"
;

/*
** Schema 2.0 is the first of the SQLite 3 changes. Rather than a true
** schema change, it's actually a reencoding of the attachments into
** BLOBs.
*/
static char zSchemaChange_2_0[] =
"BEGIN;\n"
"UPDATE config SET value='2.0' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** Schema 2.1 attempts to solve problems with /dirview sorting by keeping
** last cn of file in FILE table.
*/
static char zSchemaChange_2_1[] =
"BEGIN;\n"
"CREATE TEMP TABLE old_file AS SELECT * FROM file;\n"
"DROP TABLE file;\n"
"CREATE TABLE file(\n"
"   isdir boolean,\n"
"   base text,\n"
"   dir text,\n"
"   lastcn int,\n"
"   unique(dir,base)\n"
");\n"
"UPDATE config SET value='2.1' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** The following are additions to the schema for version 2.2.  A
** new table is added to hold external tools which can be used against
** various CVSTrac objects (repository files, wiki pages, etc).
*/
static char zSchemaChange_2_2[] =
"BEGIN;\n"
"CREATE TABLE tool(\n"
"  name text primary key,\n"
"  perms text,\n"
"  object text,\n"

"  command text,\n"
"  description text\n"
");\n"
"UPDATE config SET value='2.2' WHERE name='schema';\n"
"COMMIT;\n"
;

/*
** Initialize the main database.
**
** This routine is called when the program is launched from the command-line
** with the "init" argument.  This routine is never called when this
** program is run as a CGI program.
*/
void db_init(void){
  db_execute(zSchema);
  db_execute(zSchemaChange_1_1);
  db_execute(zSchemaChange_1_2);
  db_execute(zSchemaChange_1_3);
  db_execute(zSchemaChange_1_4);
  db_execute(zSchemaChange_1_5);
  db_execute(zSchemaChange_1_6);
  db_execute(zSchemaChange_1_7);
  db_execute(zSchemaChange_1_8);
  db_execute(zSchemaChange_1_9);
  db_execute(zSchemaChange_2_0);
  db_execute(zSchemaChange_2_1);
  db_execute(zSchemaChange_2_2);
  initialize_wiki_pages();
}

/*
** The zMsg parameter is a check-in comment for check-in number "cn".  Create
** entries in the XREF table from this check-in comment.
*/
void xref_checkin_comment(int cn, const char *zMsg){
  int i, j, tn;
  for(i=0; zMsg[i]; i++){

    if( zMsg[i]=='#' && (j = ndigit(&zMsg[i+1]))>0 
               && is_eow(&zMsg[i+1+j],0)
               && (tn = atoi(&zMsg[i+1]))>0 ){
      /* avoid duplicate xrefs... they're harmless, but we end up listing
      ** them multiple times in output.
      */
      char **az = db_query("SELECT * FROM xref where cn=%d AND tn=%d",cn,tn);
      if( az == NULL || az[0] == NULL ){
        char zSql[200];
        bprintf(zSql, sizeof(zSql), "INSERT INTO xref(cn,tn) VALUES(%d,%d)",
                cn, tn);
        db_execute(zSql);
      }
    }
  }
}

/*
** When upgrading the schema from version 1.0 to version 1.1, a query
** is executed on all entries of the CHNG table.  This routine is called
** once for each row.  The two parameters are the CHNG.CN value and
** the CHNG.MESSAGE value.  This callback scans the CHNG.MESSAGE looking
** for ticket numbers.  When it finds them, it creates entries in the
** XREF table.
*/
static int upgrade_schema_1_callback(
  void *pNotUsed,  /* Not used */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **azName    /* Names of the columns */
){
  assert( nArg==2 );
  xref_checkin_comment(atoi(azArg[0]), azArg[1]);
  return 0;
}
  
/*
** Update the database schema from version 1.0 to version 1.1.
*/
void db_upgrade_schema_1(void){
  db_execute(zSchemaChange_1_1);
  db_callback_query(upgrade_schema_1_callback, 0, 
    "BEGIN;"
    "DELETE FROM xref;"
    "SELECT cn, message FROM chng WHERE NOT milestone;"
    "COMMIT;");
}

/*
** Implement the crypt() SQL function.  crypt() converts a plain-text
** password into an encrypted password with random salt.
*/
static void f_crypt(sqlite3_context *context, int argc, sqlite3_value **argv){
  char *zOut;
  static char zSalt[3] = "aa";
  if( argc==1 ) {
    const char *zIn = (const char*)sqlite3_value_text(argv[0]);
    zOut = crypt(zIn, zSalt);
    if( zOut==0 ) return;
    zSalt[0] = zOut[2];
    zSalt[1] = zOut[3];
    zOut = crypt(zIn, zSalt);
    if( zOut==0 ) return;
    sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
  }
}

/*
** Update the database schema from version 1.1 to version 1.2.
*/
void db_upgrade_schema_2(void){
  if( pDb==0 ) db_open();
  db_execute(zSchemaChange_1_2);
  sqlite3_create_function(pDb, "crypt", 1, SQLITE_ANY, 0, &f_crypt, 0, 0);
  db_execute(
    "REPLACE INTO user(id,name,email,passwd,notify,http,capabilities) "
    "  SELECT id, name, email, crypt(passwd), notify, http, capabilities "
    "  FROM user;"
  );
}

/*
** Update the database schema from version 1.2 to version 1.3.
*/
void db_upgrade_schema_3(void){
  db_execute(zSchemaChange_1_3);
}

/*
** Update the database schema from version 1.3 to version 1.4.
*/
void db_upgrade_schema_4(void){
  db_execute(zSchemaChange_1_4);
}

/*
** Update the database schema from version 1.4 to version 1.5.
*/
void db_upgrade_schema_5(void){
  db_execute(zSchemaChange_1_5);
}

/*
** Update the database schema from version 1.5 to version 1.6.
*/
void db_upgrade_schema_6(void){
  db_execute(zSchemaChange_1_6);
}

/*
** Update the database schema from version 1.6 to version 1.7.
*/
void db_upgrade_schema_7(void){
  db_execute(zSchemaChange_1_7);
}

/*
** Update the database schema from version 1.7 to version 1.8.
*/
void db_upgrade_schema_8(void){
  db_execute(zSchemaChange_1_8);
}

/*
** Update the database schema from version 1.7/1.8 to version 1.9.
*/
void db_upgrade_schema_9(void){
  /* zSchemaChange_1_9 copies FILECHNG to the temporary OLD_FILECHNG table and
   * creates an empty FILECHNG. Following code will fill FILECHNG.
   */
  db_execute(zSchemaChange_1_9);
  db_execute("BEGIN;");

  if( !strcmp(g.scm.zSCM,"cvs") ){
    char **azFile;
    int i, j, k;
    char *zFile;
    char *zVers;
    char zPrevVers[100];
    int lastchngtype, chngtype;

    /* This query _should_ give us a depth-first search through the CVS branch
    ** tree. If not, things will get wonky when the RCS file is in the Attic.
    */ 
    azFile = db_query("SELECT cn,filename,vers,nins,ndel FROM old_filechng2 "
                             "ORDER BY filename,cn DESC,vers DESC");

    for(i=0; azFile[i]; ){
      zFile = azFile[i+1];

      /* is_file_available() is a fairly expensive operation (calls access(2))
      ** so we want to check that as few times as possible. We only need
      ** to know if it's been moved to the Attic once per file, then we
      ** can fill in the other versions with zero until we get to the
      ** first add.  FIXME This doesn't work perfectly for branches where
      ** the file became "dead" in one branch but not another.
      */

      chngtype = lastchngtype = is_file_available(zFile) ? 0 : 2;

      for(j=0; azFile[i+j]; j+=5){
        zVers = azFile[i+j+2];

        /* run the inner loop until we get to a different filename */
        if( j!=0 && strcmp(azFile[i+j+1],zFile) ) break;

        strncpy( zPrevVers, zVers, sizeof(zPrevVers)-2 );

        /* by not passing the filename, we avoid a database query. We
        ** don't need a query because we have the entire list of known
        ** versions in azFile.
        */
        cvs_previous_version(zPrevVers,NULL);

        if( zPrevVers[0] ){
          /* We've calculate what the previous version should be. See if
          ** it's actually in the list. It _should_ be the next entry unless
          ** there's a branch. In some cases, the expected previous version
          ** won't exist because the repository admin manually changed
          ** version numbers. This isn't critical, but it does mean that
          ** the prevvers chain gets broken.
          */
          for(k=j+5; azFile[i+k]; k+=5){
            if( !strcmp(zPrevVers, azFile[i+k+2]) ) break;
            if( strcmp(azFile[i+k+1], zFile) ) break;
          }
        
          if( azFile[i+k]==0 || strcmp(azFile[i+k+1],zFile) ){
            zPrevVers[0]=0;
          }
        }

        if( zPrevVers[0] ){
          db_execute("INSERT INTO "
                     "filechng(cn,filename,vers,nins,ndel,prevvers,chngtype) "
                     "VALUES(%d,'%q','%q',%d,%d,'%q',%d)",
                     atoi(azFile[i+j]),zFile,zVers,atoi(azFile[i+j+3]),
                     atoi(azFile[i+j+4]), zPrevVers, chngtype);

          /* only the latest entry in a revision chain is a remove, the
          ** others have to be modify or adds. As long as we can calculate
          ** a previous revision, it must be a modify until we get to a
          ** new branch.
          */
          chngtype = 0;
        } else {
          /* no previous version for this file, which means it's
          ** an add. It may also just mean that the revision chain was
          ** broken because someone messed with the revision numbers in a
          ** non-chainable way.
          */
          db_execute("INSERT INTO "
                     "filechng(cn,filename,vers,nins,ndel,prevvers,chngtype) "
                     "VALUES(%d,'%q','%q',%d,%d,'',1)",
                     atoi(azFile[i+j]),zFile,zVers,
                     atoi(azFile[i+j+3]),atoi(azFile[i+j+4]));

          /* the next revision we see will be the latest revision in
          ** another branch. Reset chngtype.
          */
          chngtype = lastchngtype;
        }
      }
      assert( j != 0 );
      i += j;
    }

    db_query_free(azFile);
  }else{
    /* Otherwise, just make a copy of the fields. */
    db_execute("INSERT INTO filechng SELECT * FROM old_filechng2; "
               "INSERT INTO config(name,value) VALUES('svnlastupdate',0); ");
  }

  db_execute("CREATE INDEX filechng_idx1 ON filechng(filename,vers); "
             "UPDATE config SET value='1.9' WHERE name='schema'; "
             "COMMIT;");
}

/*
** Decode the string "in" into binary data and write it into "out".
** This routine reverses the encoded created by sqlite_encode_binary().
** The output will always be a few bytes less than the input.  The number
** of bytes of output is returned.  If the input is not a well-formed
** encoding, -1 is returned.
**
** The "in" and "out" parameters may point to the same buffer in order
** to decode a string in place.
*/
static int blob_decode(const unsigned char *in, unsigned char *out){
  int i, c, e;
  e = *(in++);
  i = 0;
  while( (c = *(in++))!=0 ){
    if( c==1 ){
      c = *(in++);
      if( c==1 ){
        c = 0;
      }else if( c==2 ){
        c = 1;
      }else if( c==3 ){
        c = '\'';
      }else{
        return -1;
      }
    }
    out[i++] = (c + e)&0xff;
  }
  return i;
}

/*
** Calls blob_decode() to decode the body of the attachment.
*/
static void f_decode(sqlite3_context *context, int argc, sqlite3_value **argv){
  if( argc==2 ) {
    const char *zIn = (const char*)sqlite3_value_text(argv[0]);
    int nBytes = sqlite3_value_int(argv[1]);
    if( zIn && zIn[0] && nBytes>0 ){
      const char *zOut = calloc(nBytes,1);
      int nDecoded;
      if( zOut==0 ){
        db_err( strerror(errno), 0,
                "f_decode: Unable to allocate %d bytes", nBytes);
      }
      nDecoded = blob_decode(zIn,zOut);
      sqlite3_result_blob(context, zOut, nBytes, SQLITE_TRANSIENT);
      free(zOut);
    }
  }
}
/*
** Update the database schema from version 1.9 to version 2.0
*/
void db_upgrade_schema_20(void){
  if( pDb==0 ) db_open();
  sqlite3_create_function(pDb, "decode", 2, SQLITE_ANY, 0, &f_decode, 0, 0);
  db_execute(
    "REPLACE INTO attachment "
    "  SELECT atn, tn, size, date, user, mime, fname, description, "
    "         decode(content,size)"
    "  FROM attachment;"
  );
  db_execute(zSchemaChange_2_0);
}

/*
** Populate FILE with lastcn for each file/dir, updating schema from 2.0 to
** 2.1.
*/
void db_upgrade_schema_21(void){
  db_execute(zSchemaChange_2_1);
  db_execute("BEGIN");
  update_file_table_with_lastcn();
  db_execute("COMMIT");
}

/*
** Update the database schema from version 2.1 to version 2.2
*/
void db_upgrade_schema_22(void){
  db_execute(zSchemaChange_2_2);
}