/*
** 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");
@ <h1>Database Error</h1>
@ %h(zMsg)
if( zQuery ){
@ <blockquote>%h(zQuery)</blockquote>
}
if( zReason ){
@ Reason: %h(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;
@
@ -- Each "cvs commit" results in a single entry in the following table.
@ -- Even commits that involve multiple files generate just a single
@ -- table entry.
@ --
@ CREATE TABLE chng(
@ cn integer primary key, -- A unique "change" number
@ date int, -- Time commit occured in seconds since 1970
@ branch text, -- Name of branch or '' if main trunk
@ milestone int, -- 0: not a milestone. 1: release, 2: event
@ user text, -- User who did the commit
@ message text -- Text of the log message
@ );
@ CREATE INDEX chng_idx ON chng(user,message);
@
@ -- Each file involved in a commit operation results in an entry in
@ -- this table.
@ --
@ CREATE TABLE filechng(
@ cn int, -- Corresponds to CHNG.CN field
@ filename text, -- Name of the file
@ vers text, -- New version number on this file
@ nins int, ndel int -- Number of lines inserted and deleted
@ );
@ CREATE INDEX filechng_idx ON filechng(cn);
@
@ -- Every trouble ticket or change request is a single entry in this table
@ -- (The structure of this table is modified by zSchemaChange_1_4[] below.)
@ --
@ CREATE TABLE ticket(
@ tn integer primary key, -- Unique tracking number for the ticket
@ type text, -- code, doc, todo, new, or event
@ status text, -- new, review, defer, active, fixed,
@ -- tested, or closed
@ origtime int, -- Time this ticket was first created
@ changetime int, -- Time of most recent change to this ticket
@ derivedfrom int, -- This ticket derived from another
@ version text, -- Version or build number containing the problem
@ assignedto text, -- Whose job is it to deal with this ticket
@ severity int, -- How bad is the problem
@ priority text, -- When should the problem be fixed
@ subsystem text, -- What subsystem does this ticket refer to
@ owner text, -- Who originally wrote this ticket
@ title text, -- Title of this bug
@ description text, -- Description of the problem
@ remarks text, -- How the problem was dealt with
@ contact text -- Contact information for the owner
@ );
@
@ -- Record each change to a bug report
@ --
@ CREATE TABLE tktchng(
@ tn int, -- Bug number
@ user text, -- User that made the change
@ chngtime int, -- Time of the change
@ fieldid text, -- Name of the field that changed
@ oldval text, -- Previous value of the field
@ newval text -- New value of the field
@ );
@ CREATE INDEX tktchng_idx1 ON tktchng(tn, chngtime);
@
@ -- Miscellaneous configuration parameters
@ --
@ CREATE TABLE config(
@ name text primary key, -- Name of the configuration parameter
@ value text -- Value of the configuration parameter
@ );
@ INSERT INTO config(name,value) VALUES('cvsroot','');
@ INSERT INTO config(name,value) VALUES('historysize',0);
@ INSERT INTO config(name,value) VALUES('initial_state','new');
@ INSERT INTO config(name,value) VALUES('schema','1.0');
@
@ -- An entry in the following table describes everything we know
@ -- about a single user
@ --
@ CREATE TABLE user(
@ id text primary key, -- The user ID
@ name text, -- Complete name of the user
@ email text, -- E-mail address for this user
@ passwd text, -- User password
@ notify text, -- Type of e-mail to receive
@ http text, -- URL used by this user to access the site
@ capabilities text -- What this user is allowed to do
@ );
@ INSERT INTO user(id,name,email,passwd,capabilities)
@ VALUES('setup','Setup Account',NULL,'aISQuNAAoY3qw','ainoprsw');
@
@ -- An entry in this table describes a database query that generates a
@ -- table of tickets.
@ --
@ CREATE TABLE reportfmt(
@ rn integer primary key, -- Report number
@ owner text, -- Owner of this report format (not used)
@ title text, -- Title of this report
@ cols text, -- A color-key specification
@ sqlcode text -- An SQL SELECT statement for this report
@ );
@
@ -- Several default report formats:
@ --
@ INSERT INTO reportfmt VALUES(1,NULL,
@ 'Recently changed and open tickets',
@ '#ffffff Key:
@ #f2dcdc Active
@ #e8e8bd Review
@ #cfe8bd Fixed
@ #bdefd6 Tested
@ #cacae5 Deferred
@ #c8c8c8 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
@ WHERE changetime>now()-604800 OR status IN ('new','active')
@ ORDER BY changetime DESC");
@ ----------------------------------------------------------------------------
@ INSERT INTO reportfmt VALUES(2,NULL,
@ 'Recently changed and open tickets w/description and remarks',
@ '#ffffff Key:
@ #f2dcdc Active
@ #e8e8bd Review
@ #cfe8bd Fixed
@ #bdefd6 Tested
@ #cacae5 Deferred
@ #c8c8c8 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',
@ description AS '_Description',
@ remarks AS '_Remarks'
@ FROM ticket
@ WHERE changetime>now()-604800 OR status IN ('new','active')
@ ORDER BY changetime DESC");
@ ----------------------------------------------------------------------------
@ INSERT INTO reportfmt VALUES(3,NULL,
@ 'Tickets associated with a particular user',
@ '#ffffff Priority:
@ #f2dcdc 1
@ #e8e8bd 2
@ #cfe8bd 3
@ #cacae5 4
@ #c8c8c8 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',
@ 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
@ WHERE owner=aux('User',user()) OR assignedto=aux('User',user())");
@ ----------------------------------------------------------------------------
@ INSERT INTO reportfmt VALUES(4,NULL,'All Tickets',
@ '#ffffff Key:
@ #f2dcdc Active
@ #e8e8bd Review
@ #cfe8bd Fixed
@ #bdefd6 Tested
@ #cacae5 Deferred
@ #c8c8c8 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");
@ ----------------------------------------------------------------------------
@ INSERT INTO reportfmt VALUES(5,NULL,'Tickets counts',NULL,"SELECT
@ status,
@ count(case when type='code' then 'x' end),
@ count(case when type='doc' then 'x' end),
@ count(case when type='new' then 'x' end),
@ count(case when type NOT IN ('code','doc','new') then 'x' end),
@ count(*)
@ FROM ticket GROUP BY status
@ UNION
@ SELECT
@ 'TOTAL' AS 'Status',
@ count(case when type='code' then 'x' end) as 'Code Bugs',
@ count(case when type='doc' then 'x' end) as 'Doc Bugs',
@ count(case when type='new' then 'x' end) as 'Enhancements',
@ count(case when type NOT IN ('code','doc','new') then 'x' end)
@ as 'Other',
@ count(*) AS 'All Types'
@ FROM ticket
@ ORDER BY [All Types]");
@ ----------------------------------------------------------------------------
@
@ -- This table contains the names of all subsystems.
@ -- (This table is obsolete and is removed by zSchemaChange_1_4 below.)
@ --
@ CREATE TABLE subsyst(name text);
@ INSERT INTO subsyst VALUES('Unknown');
@
@ -- The next table is used for browsing the repository
@ --
@ CREATE TABLE file(
@ isdir boolean, -- True if this is a directory
@ base text, -- The basename of the file or directory
@ dir text, -- Contained in this directory
@ unique(dir,base)
@ );
@ COMMIT;
;
/*
** 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;
@ CREATE TABLE xref(
@ tn int, -- Ticket number. References TICKET.TN
@ cn int -- Change number. References CHNG.CN
@ );
@ CREATE INDEX xref_idx1 ON xref(tn);
@ CREATE INDEX xref_idx2 ON xref(cn);
@ UPDATE config SET value='1.1' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TABLE cookie(
@ cookie char(32) primary key, -- The login cookie
@ user text, -- The user to log in as
@ expires int, -- When this cookie expires
@ ipaddr varchar(32), -- IP address of browser with this cookie
@ agent text -- User agent of browser with this cookie
@ );
@ CREATE TABLE wiki(
@ name text, -- Name of this page
@ invtime int, -- Inverse timestamp: seconds *before* 1970
@ locked boolean, -- True if editing is not allowed
@ who text, -- Who generated this version of the page
@ ipaddr text, -- IP Address of "who"
@ text clob, -- Text of the page
@ UNIQUE(name,invtime)
@ );
@ UPDATE config SET value='1.2' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TABLE attachment(
@ atn integer primary key, -- Unique key for this attachment
@ tn int, -- Ticket that this is attached to
@ size int, -- Size of the attachment in bytes
@ date int, -- Date that the attachment was uploaded
@ user text, -- User who uploaded the attachment
@ mime text, -- MIME type of the attachment
@ fname text, -- Filename of the attachment
@ content blob -- binary data for the attachment
@ );
@ CREATE INDEX attachment_idx1 ON attachment(tn);
@ UPDATE config SET value='1.3' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@
@ -- Redo the indexing of tables so that fewer full table scans are
@ -- required to render most pages.
@ --
@ DROP INDEX chng_idx;
@ CREATE INDEX chng_idx1 ON chng(date,user);
@ DROP INDEX filechng_idx;
@ CREATE INDEX filechng_idx1 ON filechng(cn, filename);
@ DROP INDEX tktchng_idx1;
@ CREATE INDEX tktchng_idx1 ON tktchng(chngtime);
@ CREATE INDEX wiki_idx1 ON wiki(invtime);
@
@ -- The new ENUMS table is used to define allowed values of various
@ -- ticket columns.
@ --
@ CREATE TABLE enums(
@ type text, -- Which enumeration this entry is part of
@ idx int, -- The order of this entry within its enumeration
@ name text, -- The internal name of this enumeration entry
@ value text, -- The user-visible name of this enumeration entry
@ color text -- An optional color associated with this enum entry
@ );
@ CREATE INDEX enums_idx1 ON enums(type, idx);
@ CREATE INDEX enums_idx2 ON enums(name, type);
@ INSERT INTO enums VALUES('status',1,'new','New','#f2dcdc');
@ INSERT INTO enums VALUES('status',2,'review','Review','#e8e8bd');
@ INSERT INTO enums VALUES('status',3,'defer','Defer','#cacae5');
@ INSERT INTO enums VALUES('status',4,'active','Active','#f2dcdc');
@ INSERT INTO enums VALUES('status',5,'fixed','Fixed','#cfe8bd');
@ INSERT INTO enums VALUES('status',6,'tested','Tested','#bde5d6');
@ INSERT INTO enums VALUES('status',7,'closed','Closed','#c8c8c8');
@ INSERT INTO enums VALUES('type',1,'code','Code Defect','#f2dcdc');
@ INSERT INTO enums VALUES('type',2,'doc','Documentation','#e8e8bd');
@ INSERT INTO enums VALUES('type',3,'todo','Action Item','#cacae5');
@ INSERT INTO enums VALUES('type',4,'new','Enhancement','#cfe8bd');
@ INSERT INTO enums VALUES('type',5,'event','Incident','#c8c8c8');
@ INSERT INTO enums SELECT 'subsys', rowid, name, name, '' FROM subsyst;
@ DROP TABLE subsyst;
@
@ -- The TICKET table is modified by the addition of five new columns
@ -- named "extra1" throught "extra5". These columns are used for user
@ -- defined ticket attributes. The order of the tables is also modified
@ -- so that the big columns (description and remarks) are now at the end.
@ --
@ CREATE TEMP TABLE old_ticket AS SELECT * FROM ticket;
@ DROP TABLE ticket;
@ CREATE TABLE ticket(
@ tn integer primary key, -- Unique tracking number for the ticket
@ type text, -- code, doc, todo, new, or event
@ status text, -- new, review, defer, active, fixed,
@ -- tested, or closed
@ origtime int, -- Time this ticket was first created
@ changetime int, -- Time of most recent change to this ticket
@ derivedfrom int, -- This ticket derived from another
@ version text, -- Version or build number containing the problem
@ assignedto text, -- Whose job is it to deal with this ticket
@ severity int, -- How bad is the problem
@ priority text, -- When should the problem be fixed
@ subsystem text, -- What subsystem does this ticket refer to
@ owner text, -- Who originally wrote this ticket
@ title text, -- Title of this bug
@ contact text, -- Contact information for the owner
@ extra1 numeric, -- User defined column #1
@ extra2 numeric, -- User defined column #2
@ extra3 numeric, -- User defined column #3
@ extra4 numeric, -- User defined column #4
@ extra5 numeric, -- User defined column #5
@ description text, -- Description of the problem
@ remarks text -- How the problem was dealt with
@ );
@ CREATE INDEX ticket_idx1 ON ticket(origtime);
@ INSERT INTO ticket(tn,type,status,origtime,changetime,derivedfrom,
@ version,assignedto,severity,priority,subsystem,
@ owner,title,description,remarks,contact)
@ SELECT * FROM old_ticket;
@
@ -- After the above changes, we have schema version 1.4.
@ --
@ UPDATE config SET value='1.4' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@
@ -- Create an INSPECT table to record inspections of check-ins and the
@ -- results of each inspection
@ --
@ CREATE TABLE inspect(
@ cn integer not null, -- The check-in that was inspected
@ inspecttime int not null, -- Time that the inspection occurred
@ inspector text not null, -- Developer who did the inspection
@ ticket int, -- Ticket related to this inspection - or NULL
@ result text -- "passed", "failed", or other comments, or NULL
@ );
@ CREATE INDEX inspect_idx1 ON inspect(inspecttime);
@ CREATE INDEX inspect_idx2 ON inspect(cn);
@
@ -- Allow efficient searching of TKTCHNG by TN.
@ --
@ CREATE INDEX tktchng_idx2 ON tktchng(tn);
@
@ -- Record the "load" of requests coming from each IP address.
@ --
@ CREATE TABLE access_load(
@ ipaddr text primary key, -- IP address from which request originated
@ lastaccess time, -- Time of last request
@ load real -- Cummulative load from this IP
@ );
@
@ -- After the above changes, we have schema version 1.5.
@ --
@ UPDATE config SET value='1.5' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@
@ -- Add a captch countdown to the ACCESS_LOAD table.
@ CREATE TEMP TABLE old_access_load AS SELECT * FROM access_load;
@ DROP TABLE access_load;
@ CREATE TABLE access_load(
@ ipaddr text primary key, -- IP address from which request originated
@ lastaccess time, -- Time of last request
@ load real, -- Cummulative load from this IP
@ captcha int -- Number of captcha attempts left
@ );
@ INSERT INTO access_load(ipaddr,lastaccess,load)
@ SELECT * FROM old_access_load;
@
@ -- Add a description field to the ATTACHMENT table
@ CREATE TEMP TABLE old_attachment AS SELECT * FROM attachment;
@ DROP TABLE attachment;
@ CREATE TABLE attachment(
@ atn integer primary key, -- Unique key for this attachment
@ tn int, -- Ticket that this is attached to
@ size int, -- Size of the attachment in bytes
@ date int, -- Date that the attachment was uploaded
@ user text, -- User who uploaded the attachment
@ mime text, -- MIME type of the attachment
@ fname text, -- Filename of the attachment
@ description text, -- Description of the attachment
@ content blob -- binary data for the attachment
@ );
@ INSERT INTO attachment(atn,tn,size,date,user,mime,fname,content)
@ SELECT * FROM old_attachment;
@
@ -- Add a directory field to the CHNG table
@ CREATE TEMP TABLE old_chng AS SELECT * FROM chng;
@ DROP TABLE chng;
@ CREATE TABLE chng(
@ cn integer primary key, -- A unique "change" number
@ date int, -- Time commit occured in seconds since 1970
@ branch text, -- Name of branch or '' if main trunk
@ milestone int, -- 0: not a milestone. 1: release, 2: event
@ user text, -- User who did the commit
@ directory text, -- Directory/module of chng
@ message text -- Text of the log message
@ );
@ INSERT INTO chng(cn,date,branch,milestone,user,message)
@ SELECT * FROM old_chng;
@
@ -- After the above changes, we have schema version 1.6.
@ --
@ UPDATE config SET value='1.6' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TABLE markup(
@ markup text primary key, -- Style name for this markup
@ type int, -- 0: markup, 1: program markup
@ -- 2: block, 3: program block
@ formatter text, -- pathname or format string
@ description text -- description of what the markup does
@ );
@ CREATE INDEX markup_idx1 ON markup(markup);
@ UPDATE config SET value='1.7' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TEMP TABLE old_filechng AS SELECT * FROM filechng;
@ DROP TABLE filechng;
@ CREATE TABLE filechng(
@ cn int, -- Corresponds to CHNG.CN field
@ filename text, -- Name of the file
@ vers text, -- New version number on this file
@ prevvers text, -- Previous version number on this file
@ chngtype int, -- 0: modify, 1: add, 2: remove
@ nins int, ndel int, -- Number of lines inserted and deleted
@ UNIQUE(filename,vers)
@ );
@ INSERT INTO filechng(cn,filename,vers,nins,ndel)
@ SELECT cn,filename,vers,nins,ndel FROM old_filechng;
@ UPDATE config SET value='1.8' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TEMP TABLE old_filechng2 AS SELECT * FROM filechng;
@ DROP TABLE filechng;
@ CREATE TABLE filechng(
@ cn int, -- Corresponds to CHNG.CN field
@ filename text, -- Name of the file
@ vers text, -- New version number on this file
@ prevvers text, -- Previous version number on this file
@ chngtype int, -- 0: modify, 1: add, 2: remove
@ nins int, ndel int, -- Number of lines inserted and deleted
@ UNIQUE(filename,vers)
@ );
@ COMMIT;
;
/*
** 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;
@ UPDATE config SET value='2.0' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TEMP TABLE old_file AS SELECT * FROM file;
@ DROP TABLE file;
@ CREATE TABLE file(
@ isdir boolean, -- True if this is a directory
@ base text, -- The basename of the file or directory
@ dir text, -- Contained in this directory
@ lastcn int, -- Last CHNG.cn of the file or directory
@ unique(dir,base)
@ );
@ UPDATE config SET value='2.1' WHERE name='schema';
@ COMMIT;
;
/*
** 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;
@ CREATE TABLE tool(
@ name text primary key, -- Name of tool
@ perms text, -- Required permissions to see/use tool
@ object text, -- target object. "wiki", "file", "tkt",
@ -- "chng", "ms", "rpt", "dir"
@ command text, -- tool command-line
@ description text -- description of what the tool does
@ );
@ UPDATE config SET value='2.2' WHERE name='schema';
@ COMMIT;
;
/*
** 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);
}
syntax highlighted by Code2HTML, v. 0.9.1