Fossil

#### C Compiler and options for use in building executables that
#    will run on the platform that is doing the build.  This is used
#    to compile code-generator programs as part of the build process.
#    See TCC below for the C compiler for building the finished binary.
#
BCC = gcc
BCCFLAGS = $(CFLAGS)

#### The suffix to add to final executable file.  When cross-compiling
#    to windows, make this ".exe".  Otherwise leave it blank.
#
E =

#### C Compile and options for use in building executables that

# To use the included miniz library
# FOSSIL_ENABLE_MINIZ = 1
# TCC += -DFOSSIL_ENABLE_MINIZ

# To add support for HTTPS
TCC += -DFOSSIL_ENABLE_SSL

# To enable legacy mv/rm support
TCC += -DFOSSIL_ENABLE_LEGACY_MV_RM=1

#### We sometimes add the -static option here so that we can build a
#    static executable that will run in a chroot jail.
#LIB = -static
TCC += -DFOSSIL_DYNAMIC_BUILD=1

TCCFLAGS = $(CFLAGS)

#### Extra arguments for linking the finished binary.  Fossil needs
#    to link against the Z-Lib compression library unless the miniz
#    library in the source tree is being used.  There are no other
#    required dependencies.
ZLIB_LIB.0 = -lz
ZLIB_LIB.1 =

#### Tcl shell for use in running the fossil testsuite.  If you do not
#    care about testing the end result, this can be blank.
#
TCLSH = tclsh

CFLAGS = @CFLAGS@
LIB =	@LDFLAGS@ @EXTRA_LDFLAGS@ @LIBS@
BCCFLAGS =	@CPPFLAGS@ $(CFLAGS)
TCCFLAGS =	@EXTRA_CFLAGS@ @CPPFLAGS@ $(CFLAGS) -DHAVE_AUTOCONFIG_H -D_HAVE_SQLITE_CONFIG_H
INSTALLDIR = $(DESTDIR)@prefix@/bin
USE_SYSTEM_SQLITE = @USE_SYSTEM_SQLITE@
USE_LINENOISE = @USE_LINENOISE@
USE_MMAN_H = @USE_MMAN_H@
USE_SEE = @USE_SEE@
FOSSIL_ENABLE_MINIZ = @FOSSIL_ENABLE_MINIZ@

#!/usr/bin/make
#
# This is a specially modified version of the Makefile that will build
# Fossil on Mac OSX Jaguar (10.2) circa 2002.  This Makefile is used for
# testing on an old PPC iBook.  The use of this old platform helps to verify
# Fossil and SQLite running on big-endian hardware.
#
# To build with this Makefile, run:
#
#     make -f Makefile.osx-jaguar
#
#
# This is the top-level makefile for Fossil when the build is occurring
# on a unix platform.  This works out-of-the-box on most unix platforms.
# But you are free to vary some of the definitions if desired.
#
#### The toplevel directory of the source tree.  Fossil can be built
#    in a directory that is separate from the source tree.  Just change
#    the following to point from the build directory to the src/ folder.
#
SRCDIR = ./src

#### The directory into which object code files should be written.
#    Having a "./" prefix in the value of this variable breaks our use of the
#    "makeheaders" tool when running make on the MinGW platform, apparently
#    due to some command line argument manipulation performed automatically
#    by the shell.
#
#
OBJDIR = bld

#### C Compiler and options for use in building executables that
#    will run on the platform that is doing the build.  This is used
#    to compile code-generator programs as part of the build process.
#    See TCC below for the C compiler for building the finished binary.
#
BCC = cc
BCCFLAGS = $(CFLAGS)

#### The suffix to add to final executable file.  When cross-compiling
#    to windows, make this ".exe".  Otherwise leave it blank.
#
E = 

TCC = cc
TCCFLAGS = $(CFLAGS)

#### Tcl shell for use in running the fossil testsuite.  If you do not
#    care about testing the end result, this can be blank.
#
TCLSH = tclsh

# LIB =	  -lz
LIB = compat/zlib/libz.a
TCC +=	  -g -O0 -DHAVE_AUTOCONFIG_H
TCC += -Icompat/zlib
TCC += -DSQLITE_WITHOUT_ZONEMALLOC
TCC += -D_BSD_SOURCE=1
TCC += -DWITHOUT_ICONV
TCC += -Dsocklen_t=int
TCC += -DSQLITE_MAX_MMAP_SIZE=0
TCC += -DFOSSIL_ENABLE_LEGACY_MV_RM=1
INSTALLDIR = $(DESTDIR)/usr/local/bin
USE_SYSTEM_SQLITE = 
USE_LINENOISE = 1
# FOSSIL_ENABLE_TCL = @FOSSIL_ENABLE_TCL@
FOSSIL_ENABLE_TCL = 0
FOSSIL_ENABLE_MINIZ = 0

include $(SRCDIR)/main.mk

distclean: clean
	rm -f autoconfig.h config.log Makefile

2.6

# System autoconfiguration. Try: ./configure --help

use cc cc-lib

options {
    with-openssl:path|auto|tree|none
                         => {Look for OpenSSL in the given path, automatically, in the source tree, or none}
    with-miniz=0         => {Use miniz from the source tree}
    with-zlib:path|auto|tree
                         => {Look for zlib in the given path, automatically, or in the source tree}
    with-exec-rel-paths=0
                         => {Enable relative paths for external diff/gdiff}
    with-legacy-mv-rm=1  => {Enable legacy behavior for mv/rm (skip checkout files)}
    with-th1-docs=0      => {Enable TH1 for embedded documentation pages}
    with-th1-hooks=0     => {Enable TH1 hooks for commands and web pages}
    with-tcl:path        => {Enable Tcl integration, with Tcl in the specified path}
    with-tcl-stubs=0     => {Enable Tcl integration via stubs library mechanism}
    with-tcl-private-stubs=0
                         => {Enable Tcl integration via private stubs mechanism}
    with-mman=0          => {Enable use of POSIX memory APIs from "sys/mman.h"}

    # search for the system SQLite once with -ldl, and once without. If
    # the library can only be found with $extralibs set to -ldl, then
    # the code below will append -ldl to LIBS.
    #
    foreach extralibs {{} {-ldl}} {

      # Locate the system SQLite by searching for sqlite3_open(). Then check
      # if sqlite3_keyword_check() can be found as well. If we can find open() but
      # not keyword_check(), then the system SQLite is too old to link against
      # fossil.
      #
      if {[check-function-in-lib sqlite3_open sqlite3 $extralibs]} {
        if {![check-function-in-lib sqlite3_keyword_check sqlite3 $extralibs]} {
          user-error "system sqlite3 too old (require >= 3.24.0)"
        }

        # Success. Update symbols and return.
        #
        define USE_SYSTEM_SQLITE 1
        define-append LIBS -lsqlite3
        define-append LIBS $extralibs

}

if {[string match *-solaris* [get-define host]]} {
    define-append EXTRA_CFLAGS {-D_XOPEN_SOURCE=500 -D__EXTENSIONS__}
}

if {[opt-bool fossil-debug]} {

    define CFLAGS {-g -O0 -Wall}
    define-append CFLAGS -DFOSSIL_DEBUG
    msg-result "Debugging support enabled"
}

if {[opt-bool no-opt]} {
    define CFLAGS {-g -O0 -Wall}
    msg-result "Builting without compiler optimization"
}

    # reading config.h first.
    define-append EXTRA_CFLAGS -DFOSSIL_ENABLE_JSON
    define FOSSIL_ENABLE_JSON
    msg-result "JSON support enabled"
}

if {[opt-bool with-legacy-mv-rm]} {
    define-append EXTRA_CFLAGS -DFOSSIL_ENABLE_LEGACY_MV_RM=1
    define FOSSIL_ENABLE_LEGACY_MV_RM
    msg-result "Legacy mv/rm support enabled"
}

if {[opt-bool with-exec-rel-paths]} {
    define-append EXTRA_CFLAGS -DFOSSIL_ENABLE_EXEC_REL_PATHS
    define FOSSIL_ENABLE_EXEC_REL_PATHS

cc-check-function-in-lib gethostbyname nsl
if {![cc-check-function-in-lib socket {socket network}]} {
    # Last resort, may be Windows
    if {[is_mingw]} {
        define-append LIBS -lwsock32
    }
}
cc-check-function-in-lib ns_name_uncompress resolv
cc-check-functions utime
cc-check-functions usleep
cc-check-functions strchrnul
cc-check-functions pledge
cc-check-functions backtrace

# Check for getloadavg(), and if it doesn't exist, define FOSSIL_OMIT_LOAD_AVERAGE
if {![cc-check-functions getloadavg]} {
  define FOSSIL_OMIT_LOAD_AVERAGE 1
  msg-result "Load average support unavailable"
}

# Check for getpassphrase() for Solaris 10 where getpass() truncates to 10 chars
if {![cc-check-functions getpassphrase]} {
    # Haiku needs this
    cc-check-function-in-lib getpass bsd
}

cc-check-function-in-lib sin m

# Check for the FuseFS library
if {[opt-bool fusefs]} {
  if {[cc-check-function-in-lib fuse_mount fuse]} {
     define-append EXTRA_CFLAGS -DFOSSIL_HAVE_FUSEFS
     define FOSSIL_HAVE_FUSEFS 1
     define-append LIBS -lfuse
     msg-result "FuseFS support enabled"
  }
}

# Finally, append -ldl to make sure it's the last in the list.
# The library order matters in case of static linking.
if {[check-function-in-lib dlopen dl]} {
    # Some platforms (*BSD) have the dl functions already in libc and no libdl.
    # In such case we can link directly without -ldl.
    define-append LIBS [get-define lib_dlopen]
}

make-template Makefile.in
make-config-header autoconfig.h -auto {USE_* FOSSIL_*}

body {
    margin: 0 auto;

    padding: 0px 20px;
    background-color: white;
    font-family: sans-serif;
    font-size:14pt;
    -moz-text-size-adjust: none;
    -webkit-text-size-adjust: none;
    -mx-text-size-adjust: none;

.mainmenu {
    font-size:.8em;
    clear:both;
    padding:10px;
    background:#eaeaea linear-gradient(#fafafa, #eaeaea) repeat-x;
    border:1px solid #eaeaea;
    border-radius:5px;
    overflow-x: auto;
    white-space: nowrap;
}

.mainmenu a {
    padding: 10px 20px;
    text-decoration:none;
    color: #777;
    border-right:1px solid #eaeaea;

div.timelineDate {
    font-weight: bold;
    white-space: nowrap;
}
span.submenuctrl, span.submenuctrl input, select.submenuctrl {
  color: #777;
}
span.submenuctrl {
  white-space: nowrap;
}
div.submenu label {
  white-space: nowrap;
}

      return n < 10 ? '0' + n : n;
    }
    e.innerHTML = d.getUTCFullYear()+ '-' +
      f(d.getUTCMonth() + 1) + '-' +
      f(d.getUTCDate())      + ' ' +
      f(d.getUTCHours())     + ':' +
      f(d.getUTCMinutes());
    setTimeout(updateClock,(60-d.getUTCSeconds())*1000);
  }
}
updateClock();
</script>
<div class="mainmenu">
<th1>
proc menulink {url name} {

      return n < 10 ? '0' + n : n;
    }
    e.innerHTML = d.getUTCFullYear()+ '-' +
      f(d.getUTCMonth() + 1) + '-' +
      f(d.getUTCDate())      + ' ' +
      f(d.getUTCHours())     + ':' +
      f(d.getUTCMinutes());
    setTimeout(updateClock,(60-d.getUTCSeconds())*1000);
  }
}
updateClock();
</script>
<div class="mainmenu">
<th1>
proc menulink {url name} {

timeline-arrowheads:        1
timeline-circle-nodes:      1
timeline-color-graph-lines: 0
white-foreground:           0

      return n < 10 ? '0' + n : n;
    }
    e.innerHTML = d.getUTCFullYear()+ '-' +
      f(d.getUTCMonth() + 1) + '-' +
      f(d.getUTCDate())      + ' ' +
      f(d.getUTCHours())     + ':' +
      f(d.getUTCMinutes());
    setTimeout(updateClock,(60-d.getUTCSeconds())*1000);
  }
}
updateClock();
</script>
<div class="mainmenu">
<th1>
proc menulink {url name} {

** Make arrangements to add one or more files or directories to the
** current checkout at the next commit.
**
** When adding files or directories recursively, filenames that begin
** with "." are excluded by default.  To include such files, add
** the "--dotfiles" option to the command-line.
**
** The --ignore and --clean options are comma-separated lists of glob patterns
** for files to be excluded.  Example:  '*.o,*.obj,*.exe'  If the --ignore
** option does not appear on the command line then the "ignore-glob" setting
** is used.  If the --clean option does not appear on the command line then
** the "clean-glob" setting is used.
**
** If files are attempted to be added explicitly on the command line which
** match "ignore-glob", a confirmation is asked first. This can be prevented

void mv_cmd(void){
  int i;
  int vid;
  int moveFiles;
  int dryRunFlag;
  int softFlag;
  int hardFlag;
  int origType;
  int destType;
  char *zDest;
  Blob dest;
  Stmt q;

  db_must_be_within_tree();
  dryRunFlag = find_option("dry-run","n",0)!=0;
  softFlag = find_option("soft",0,0)!=0;
  hardFlag = find_option("hard",0,0)!=0;

  /* We should be done with options.. */
  verify_all_options();

  vid = db_lget_int("checkout", 0);
  if( vid==0 ){
    fossil_fatal("no checkout in which to rename files");
  }
  if( g.argc<4 ){
    usage("OLDNAME NEWNAME");
  }
  zDest = g.argv[g.argc-1];
  db_begin_transaction();
  if( g.argv[1][0]=='r' ){ /* i.e. "rename" */

  file_tree_name(zDest, &dest, 0, 1);
  db_multi_exec(
    "UPDATE vfile SET origname=pathname WHERE origname IS NULL;"
  );
  db_multi_exec(
    "CREATE TEMP TABLE mv(f TEXT UNIQUE ON CONFLICT IGNORE, t TEXT);"
  );


  if( g.argc!=4 ){
    origType = -1;
  }else{
    origType = (file_isdir(g.argv[2], RepoFILE) == 1);
  }
  destType = file_isdir(zDest, RepoFILE);
  if( origType==-1 && destType!=1 ){
    usage("OLDNAME NEWNAME");
  }else if( origType==1 && destType==2 ){
    fossil_fatal("cannot rename '%s' to '%s' since another file named"
                 " '%s' exists", g.argv[2], zDest, zDest);
  }else if( origType==0 && destType!=1 ){
    Blob orig;
    file_tree_name(g.argv[2], &orig, 0, 1);
    db_multi_exec(
      "INSERT INTO mv VALUES(%B,%B)", &orig, &dest
    );
  }else{
    if( blob_eq(&dest, ".") ){
      blob_reset(&dest);

      );
      while( db_step(&q)==SQLITE_ROW ){
        const char *zPath = db_column_text(&q, 0);
        int nPath = db_column_bytes(&q, 0);
        const char *zTail;
        if( nPath==nOrig ){
          zTail = file_tail(zPath);
        }else if( destType==1 ){
          zTail = &zPath[nOrig-strlen(file_tail(zOrig))];
        }else{
          zTail = &zPath[nOrig+1];
        }
        db_multi_exec(
          "INSERT INTO mv VALUES('%q','%q%q')",
          zPath, blob_str(&dest), zTail
        );

    }else if( type==2 ){
      zUrlTail = mprintf("technote=%s&file=%t", zTarget, zFilename);
    }else{
      zUrlTail = mprintf("page=%t&file=%t", zTarget, zFilename);
    }
    @ <li><p>
    @ Attachment %z(href("%R/ainfo/%!S",zUuid))%S(zUuid)</a>
    moderation_pending_www(attachid);


    @ <br /><a href="%R/attachview?%s(zUrlTail)">%h(zFilename)</a>
    @ [<a href="%R/attachdownload/%t(zFilename)?%s(zUrlTail)">download</a>]<br>
    if( zComment ) while( fossil_isspace(zComment[0]) ) zComment++;
    if( zComment && zComment[0] ){
      @ %!W(zComment)<br />
    }
    if( zPage==0 && zTkt==0 && zTechNote==0 ){
      if( zSrc==0 || zSrc[0]==0 ){
        zSrc = "Deleted from";

    zTargetType = mprintf("Ticket <a href=\"%R/tktview/%s\">%S</a>",
                          zTkt, zTkt);
  }
  if( zFrom==0 ) zFrom = mprintf("%s/home", g.zTop);
  if( P("cancel") ){
    cgi_redirect(zFrom);
  }
  if( P("ok") && szContent>0 && (goodCaptcha = captcha_is_correct(0)) ){
    int needModerator = (zTkt!=0 && ticket_need_moderation(0)) ||
                        (zPage!=0 && wiki_need_moderation(0));
    const char *zComment = PD("comment", "");
    attach_commit(zName, zTarget, aContent, szContent, needModerator, zComment);
    cgi_redirect(zFrom);
  }
  style_header("Add Attachment");

  @ <div class="section">Overview</div>
  @ <p><table class="label-value">
  @ <tr><th>Artifact&nbsp;ID:</th>
  @ <td>%z(href("%R/artifact/%!S",zUuid))%s(zUuid)</a>
  if( g.perm.Setup ){
    @ (%d(rid))
  }
  modPending = moderation_pending_www(rid);



  if( zTktUuid ){
    @ <tr><th>Ticket:</th>
    @ <td>%z(href("%R/tktview/%s",zTktUuid))%s(zTktUuid)</a></td></tr>
  }
  if( zTNUuid ){
    @ <tr><th>Tech Note:</th>
    @ <td>%z(href("%R/technote/%s",zTNUuid))%s(zTNUuid)</a></td></tr>

/*
** Copyright (c) 2018 D. Richard Hipp
**
** This program is free software; you can redistribute it and/or
** modify it under the terms of the Simplified BSD License (also
** known as the "2-Clause License" or "FreeBSD License".)
**
** 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.
**
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*******************************************************************************
**
** This file contains code used to manage a background processes that
** occur after user interaction with the repository.  Examples of
** backoffice processing includes:
**
**    *  Sending alerts and notifications
**    *  Processing the email queue
**    *  Automatically syncing to peer repositories
**
** Backoffice processing is automatically started whenever there are
** changes to the repository.  The backoffice process dies off after
** a period of inactivity.
**
** Steps are taken to ensure that only a single backoffice process is
** running at a time.  Otherwise, there could be race conditions that
** cause adverse effects such as multiple alerts for the same changes.
**
** At the same time, we do not want a backoffice process to run forever.
** Backoffice processes should die off after doing whatever work they need
** to do.  In this way, we avoid having lots of idle processes in the
** process table, doing nothing on rarely accessed repositories, and
** if the Fossil binary is updated on a system, the backoffice processes
** will restart using the new binary automatically.
**
** At any point in time there should be at most two backoffice processes.
** There is a main process that is doing the actually work, and there is
** a second stand-by process that is waiting for the main process to finish
** and that will become the main process after a delay.
**
** After any successful web page reply, the backoffice_check_if_needed()
** routine is called.  That routine checks to see if both one or both of
** the backoffice processes are already running.  That routine remembers the
** status in a global variable.
**
** Later, after the repository database is closed, the
** backoffice_run_if_needed() routine is called.  If the prior call
** to backoffice_check_if_needed() indicated that backoffice processing
** might be required, the run_if_needed() attempts to kick off a backoffice
** process.
**
** All work performance by the backoffice is in the backoffice_work()
** routine.
*/
#if defined(_WIN32)
# if defined(_WIN32_WINNT)
#  undef _WIN32_WINNT
# endif
# define _WIN32_WINNT 0x501
#endif
#include "config.h"
#include "backoffice.h"
#include <time.h>
#if defined(_WIN32)
# include <windows.h>
# include <stdio.h>
# include <process.h>
# if defined(__MINGW32__)
#  include <wchar.h>
# endif
# define GETPID (int)GetCurrentProcessId
#else
# include <unistd.h>
# include <sys/types.h>
# include <signal.h>
# include <errno.h>
# include <fcntl.h>
# define GETPID getpid
#endif

/*
** The BKOFCE_LEASE_TIME is the amount of time for which a single backoffice
** processing run is valid.  Each backoffice run monopolizes the lease for
** at least this amount of time.  Hopefully all backoffice processing is
** finished much faster than this - usually in less than a second.  But
** regardless of how long each invocation lasts, successive backoffice runs
** must be spaced out by at least this much time.
*/
#define BKOFCE_LEASE_TIME   60    /* Length of lease validity in seconds */

#if LOCAL_INTERFACE
/*
** An instance of the following object describes a lease on the backoffice
** processing timeslot.  This lease is used to help ensure that no more than
** one process is running backoffice at a time.
*/
struct Lease {
  sqlite3_uint64 idCurrent; /* process ID for the current lease holder */
  sqlite3_uint64 tmCurrent; /* Expiration of the current lease */
  sqlite3_uint64 idNext;    /* process ID for the next lease holder on queue */
  sqlite3_uint64 tmNext;    /* Expiration of the next lease */
};
#endif

/***************************************************************************
** Local state variables
**
** Set to prevent backoffice processing from ever entering sleep or
** otherwise taking a long time to complete.  Set this when a user-visible
** process might need to wait for backoffice to complete.
*/
static int backofficeNoDelay = 0;

/* This variable is set to the name of a database on which backoffice
** should run if backoffice process is needed.  It is set by the
** backoffice_check_if_needed() routine which must be run while the database
** file is open.  Later, after the database is closed, the
** backoffice_run_if_needed() will consult this variable to see if it
** should be a no-op.
*/
static char *backofficeDb = 0;

/* End of state variables
****************************************************************************/

/*
** This function emits a diagnostic message related to the processing in
** this module.
*/
#if defined(_WIN32)
# define BKOFCE_ALWAYS_TRACE   (1)
extern void sqlite3_win32_write_debug(const char *, int);
#else
# define BKOFCE_ALWAYS_TRACE   (0)
#endif
static void backofficeTrace(const char *zFormat, ...){
  char *zMsg = 0;
  if( BKOFCE_ALWAYS_TRACE || g.fAnyTrace ){
    va_list ap;
    va_start(ap, zFormat);
    zMsg = sqlite3_vmprintf(zFormat, ap);
    va_end(ap);
#if defined(_WIN32)
    sqlite3_win32_write_debug(zMsg, -1);
#endif
  }
  if( g.fAnyTrace ) fprintf(stderr, "%s", zMsg);
  if( zMsg ) sqlite3_free(zMsg);
}

/*
** Do not allow backoffice processes to sleep waiting on a timeslot.
** They must either do their work immediately or exit.
**
** In a perfect world, this interface would not exist, as there would
** never be a problem with waiting backoffice threads.  But in some cases
** a backoffice will delay a UI thread, so we don't want them to run for
** longer than needed.
*/
void backoffice_no_delay(void){
  backofficeNoDelay = 1;
}

/*
** Sleeps for the specified number of milliseconds -OR- until interrupted
** by another thread (if supported by the underlying platform).  Non-zero
** will be returned if the sleep was interrupted.
*/
static int backofficeSleep(int milliseconds){
#if defined(_WIN32)
  assert( milliseconds>=0 );
  if( SleepEx((DWORD)milliseconds, TRUE)==WAIT_IO_COMPLETION ){
    return 1;
  }
#else
  sqlite3_sleep(milliseconds);
#endif
  return 0;
}

/*
** Parse a unsigned 64-bit integer from a string.  Return a pointer
** to the character of z[] that occurs after the integer.
*/
static const char *backofficeParseInt(const char *z, sqlite3_uint64 *pVal){
  *pVal = 0;
  if( z==0 ) return 0;
  while( fossil_isspace(z[0]) ){ z++; }
  while( fossil_isdigit(z[0]) ){
    *pVal = (*pVal)*10 + z[0] - '0';
    z++;
  }
  return z;
}

/*
** Read the "backoffice" property and parse it into a Lease object.
**
** The backoffice property should consist of four integers:
**
**    (1)  Process ID for the active backoffice process.
**    (2)  Time (seconds since 1970) for when the active backoffice
**         lease expires.
**    (3)  Process ID for the on-deck backoffice process.
**    (4)  Time when the on-deck process should expire.
**
** No other process should start active backoffice processing until
** process (1) no longer exists and the current time exceeds (2).
*/
static void backofficeReadLease(Lease *pLease){
  Stmt q;
  memset(pLease, 0, sizeof(*pLease));
  db_prepare(&q, "SELECT value FROM repository.config"
                 " WHERE name='backoffice'");
  if( db_step(&q)==SQLITE_ROW ){
    const char *z = db_column_text(&q,0);
    z = backofficeParseInt(z, &pLease->idCurrent);
    z = backofficeParseInt(z, &pLease->tmCurrent);
    z = backofficeParseInt(z, &pLease->idNext);
    backofficeParseInt(z, &pLease->tmNext);
  }
  db_finalize(&q);
}

/*
** Return a string that describes how long it has been since the
** last backoffice run.  The string is obtained from fossil_malloc().
*/
char *backoffice_last_run(void){
  Lease x;
  sqlite3_uint64 tmNow;
  double rAge;
  backofficeReadLease(&x);
  tmNow = time(0);
  if( x.tmCurrent==0 ){
    return fossil_strdup("never");
  }
  if( tmNow<=(x.tmCurrent-BKOFCE_LEASE_TIME) ){
    return fossil_strdup("moments ago");
  }
  rAge = (tmNow - (x.tmCurrent-BKOFCE_LEASE_TIME))/86400.0;
  return mprintf("%z ago", human_readable_age(rAge));
}

/*
** Write a lease to the backoffice property
*/
static void backofficeWriteLease(Lease *pLease){
  db_multi_exec(
    "REPLACE INTO repository.config(name,value,mtime)"
    " VALUES('backoffice','%lld %lld %lld %lld',now())",
    pLease->idCurrent, pLease->tmCurrent,
    pLease->idNext, pLease->tmNext);
}

/*
** Check to see if the specified Win32 process is still alive.  It
** should be noted that even if this function returns non-zero, the
** process may die before another operation on it can be completed.
*/
#if defined(_WIN32)
#ifndef PROCESS_QUERY_LIMITED_INFORMATION
#  define PROCESS_QUERY_LIMITED_INFORMATION  (0x1000)
#endif
static int backofficeWin32ProcessExists(DWORD dwProcessId){
  HANDLE hProcess;
  hProcess = OpenProcess(PROCESS_QUERY_LIMITED_INFORMATION,FALSE,dwProcessId);
  if( hProcess==NULL ) return 0;
  CloseHandle(hProcess);
  return 1;
}
#endif

/*
** Check to see if the process identified by pid is alive.  If
** we cannot prove the the process is dead, return true.
*/
static int backofficeProcessExists(sqlite3_uint64 pid){
#if defined(_WIN32)
  return pid>0 && backofficeWin32ProcessExists((DWORD)pid)!=0;
#else
  return pid>0 && kill((pid_t)pid, 0)==0;
#endif 
}

/*
** Check to see if the process identified by pid has finished.  If
** we cannot prove the the process is still running, return true.
*/
static int backofficeProcessDone(sqlite3_uint64 pid){
#if defined(_WIN32)
  return pid<=0 || backofficeWin32ProcessExists((DWORD)pid)==0;
#else
  return pid<=0 || kill((pid_t)pid, 0)!=0;
#endif 
}

/*
** Return a process id number for the current process
*/
static sqlite3_uint64 backofficeProcessId(void){
  return (sqlite3_uint64)GETPID();
}


/*
** COMMAND: test-process-id
**
** Usage: %fossil [--sleep N] PROCESS-ID ...
**
** Show the current process id, and also tell whether or not all other
** processes IDs on the command line are running or not.  If the --sleep N
** option is provide, then sleep for N seconds before exiting.
*/
void test_process_id_command(void){
  const char *zSleep = find_option("sleep",0,1);
  int i;
  verify_all_options();
  fossil_print("ProcessID for this process: %lld\n", backofficeProcessId());
  if( zSleep ) sqlite3_sleep(1000*atoi(zSleep));
  for(i=2; i<g.argc; i++){
    sqlite3_uint64 x = (sqlite3_uint64)atoi(g.argv[i]);
    fossil_print("ProcessId %lld: exists %d done %d\n",
                 x, backofficeProcessExists(x),
                    backofficeProcessDone(x));
  }
}

/*
** COMMAND: test-backoffice-lease
**
** Usage: %fossil test-backoffice-lease
**
** Print out information about the backoffice "lease" entry in the
** config table that controls whether or not backoffice should run.
*/
void test_backoffice_lease(void){
  sqlite3_int64 tmNow = time(0);
  Lease x;
  const char *zLease;
  db_find_and_open_repository(0,0);
  verify_all_options();
  zLease = db_get("backoffice","");
  fossil_print("now:        %lld\n", tmNow);
  fossil_print("lease:      \"%s\"\n", zLease);
  backofficeReadLease(&x);
  fossil_print("idCurrent:  %-20lld", x.idCurrent);
  if( backofficeProcessExists(x.idCurrent) ) fossil_print(" (exists)");
  if( backofficeProcessDone(x.idCurrent) ) fossil_print(" (done)");
  fossil_print("\n");
  fossil_print("tmCurrent:  %-20lld", x.tmCurrent);
  if( x.tmCurrent>0 ){
    fossil_print(" (now%+d)\n",x.tmCurrent-tmNow);
  }else{
    fossil_print("\n");
  }
  fossil_print("idNext:     %-20lld", x.idNext);
  if( backofficeProcessExists(x.idNext) ) fossil_print(" (exists)");
  if( backofficeProcessDone(x.idNext) ) fossil_print(" (done)");
  fossil_print("\n");
  fossil_print("tmNext:     %-20lld", x.tmNext);
  if( x.tmNext>0 ){
    fossil_print(" (now%+d)\n",x.tmNext-tmNow);
  }else{
    fossil_print("\n");
  }
}

/*
** If backoffice processing is needed set the backofficeDb variable to the
** name of the database file.  If no backoffice processing is needed,
** this routine makes no changes to state.
*/
void backoffice_check_if_needed(void){
  Lease x;
  sqlite3_uint64 tmNow;

  if( backofficeDb ) return;
  if( g.zRepositoryName==0 ) return;
  if( g.db==0 ) return;
  tmNow = time(0);
  backofficeReadLease(&x);
  if( x.tmNext>=tmNow && backofficeProcessExists(x.idNext) ){
    /* Another backoffice process is already queued up to run.  This
    ** process does not need to do any backoffice work. */
    return;
  }else{
    /* We need to run backup to be (at a minimum) on-deck */
    backofficeDb = fossil_strdup(g.zRepositoryName);
  }
}

/*
** Check for errors prior to running backoffice_thread() or backoffice_run().
*/
static void backoffice_error_check_one(int *pOnce){
  if( *pOnce ){
    fossil_panic("multiple calls to backoffice()");
  }
  *pOnce = 1;
  if( g.db==0 ){
    fossil_panic("database not open for backoffice processing");
  }
  if( db_transaction_nesting_depth()!=0 ){
    fossil_panic("transaction %s not closed prior to backoffice processing",
                 db_transaction_start_point());
  }
}

/* This is the main loop for backoffice processing.
**
** If another process is already working as the current backoffice and
** the on-deck backoffice, then this routine returns very quickly
** without doing any work.
**
** If no backoffice processes are running at all, this routine becomes
** the main backoffice.
**
** If a primary backoffice is running, but a on-deck backoffice is
** needed, this routine becomes that on-desk backoffice.
*/
static void backoffice_thread(void){
  Lease x;
  sqlite3_uint64 tmNow;
  sqlite3_uint64 idSelf;
  int lastWarning = 0;
  int warningDelay = 30;
  static int once = 0;

  backoffice_error_check_one(&once);
  idSelf = backofficeProcessId();
  while(1){
    tmNow = time(0);
    db_begin_write();
    backofficeReadLease(&x);
    if( x.tmNext>=tmNow
     && x.idNext!=idSelf
     && backofficeProcessExists(x.idNext)
    ){
      /* Another backoffice process is already queued up to run.  This
      ** process does not need to do any backoffice work and can stop
      ** immediately. */
      db_end_transaction(0);
      break;
    }
    if( x.tmCurrent<tmNow && backofficeProcessDone(x.idCurrent) ){
      /* This process can start doing backoffice work immediately */
      x.idCurrent = idSelf;
      x.tmCurrent = tmNow + BKOFCE_LEASE_TIME;
      x.idNext = 0;
      x.tmNext = 0;
      backofficeWriteLease(&x);
      db_end_transaction(0);
      backofficeTrace("/***** Begin Backoffice Processing %d *****/\n",
                      GETPID());
      backoffice_work();
      break;
    }
    if( backofficeNoDelay || db_get_boolean("backoffice-nodelay",0) ){
      /* If the no-delay flag is set, exit immediately rather than queuing
      ** up.  Assume that some future request will come along and handle any
      ** necessary backoffice work. */
      db_end_transaction(0);
      break;
    }
    /* This process needs to queue up and wait for the current lease
    ** to expire before continuing. */
    x.idNext = idSelf;
    x.tmNext = (tmNow>x.tmCurrent ? tmNow : x.tmCurrent) + BKOFCE_LEASE_TIME;
    backofficeWriteLease(&x);
    db_end_transaction(0);
    backofficeTrace("/***** Backoffice On-deck %d *****/\n",  GETPID());
    if( x.tmCurrent >= tmNow ){
      if( backofficeSleep(1000*(x.tmCurrent - tmNow + 1)) ){
        /* The sleep was interrupted by a signal from another thread. */
        backofficeTrace("/***** Backoffice Interrupt %d *****/\n", GETPID());
        db_end_transaction(0);
        break;
      }
    }else{
      if( lastWarning+warningDelay < tmNow ){
        fossil_warning(
           "backoffice process %lld still running after %d seconds",
           x.idCurrent, (int)(BKOFCE_LEASE_TIME + tmNow - x.tmCurrent));
        lastWarning = tmNow;
        warningDelay *= 2;
      }
      if( backofficeSleep(1000) ){
        /* The sleep was interrupted by a signal from another thread. */
        backofficeTrace("/***** Backoffice Interrupt %d *****/\n", GETPID());
        db_end_transaction(0);
        break;
      }
    }
  }
  return;
}

/*
** This routine runs to do the backoffice processing.  When adding new
** backoffice processing tasks, add them here.
*/
void backoffice_work(void){
  /* Log the backoffice run for testing purposes.  For production deployments
  ** the "backoffice-logfile" property should be unset and the following code
  ** should be a no-op. */
  char *zLog = db_get("backoffice-logfile",0);
  if( zLog && zLog[0] ){
    FILE *pLog = fossil_fopen(zLog, "a");
    if( pLog ){
      char *zDate = db_text(0, "SELECT datetime('now');");
      fprintf(pLog, "%s (%d) backoffice running\n", zDate, GETPID());
      fclose(pLog);
    }
  }

  /* Here is where the actual work of the backoffice happens */
  email_backoffice(0);
  smtp_cleanup();
}

/*
** COMMAND: backoffice
**
** Usage: backoffice [-R repository]
**
** Run backoffice processing.  This might be done by a cron job or
** similar to make sure backoffice processing happens periodically.
*/
void backoffice_command(void){
  if( find_option("trace",0,0)!=0 ) g.fAnyTrace = 1;
  db_find_and_open_repository(0,0);
  verify_all_options();
  backoffice_thread();
}

/*
** This is the main interface to backoffice from the rest of the system.
** This routine launches either backoffice_thread() directly or as a
** subprocess.
*/
void backoffice_run_if_needed(void){
  if( backofficeDb==0 ) return;
  if( strcmp(backofficeDb,"x")==0 ) return;
  if( g.db ) return;
  if( g.repositoryOpen ) return;
#if defined(_WIN32)
  {
    int i;
    intptr_t x;
    char *argv[4];
    wchar_t *ax[5];
    argv[0] = g.nameOfExe;
    argv[1] = "backoffice";
    argv[2] = "-R";
    argv[3] = backofficeDb;
    ax[4] = 0;
    for(i=0; i<=3; i++) ax[i] = fossil_utf8_to_unicode(argv[i]);
    x = _wspawnv(_P_NOWAIT, ax[0], (const wchar_t * const *)ax);
    for(i=0; i<=3; i++) fossil_unicode_free(ax[i]);
    backofficeTrace(
      "/***** Subprocess %d creates backoffice child %lu *****/\n",
      GETPID(), GetProcessId((HANDLE)x));
    if( x>=0 ) return;
  }
#else /* unix */
  {
    pid_t pid = fork();
    if( pid>0 ){
      /* This is the parent in a successful fork().  Return immediately. */
      backofficeTrace(
        "/***** Subprocess %d creates backoffice child %d *****/\n",
        GETPID(), (int)pid);
      return;
    }
    if( pid==0 ){
      /* This is the child of a successful fork().  Run backoffice. */
      int i;
      setsid();
      for(i=0; i<=2; i++){
        close(i);
        open("/dev/null", O_RDWR);
      }
      for(i=3; i<100; i++){ close(i); }
      db_open_repository(backofficeDb);
      backofficeDb = "x";
      backoffice_thread();
      db_close(1);
      backofficeTrace("/***** Backoffice Child %d exits *****/\n", GETPID());
      exit(0);
    }
    fossil_warning("backoffice process %d fork failed, errno %d", GETPID(),
                   errno);
  }
#endif
  /* Fork() failed or is unavailable.  Run backoffice in this process, but
  ** do so with the no-delay setting.
  */
  backofficeNoDelay = 1;
  db_open_repository(backofficeDb);
  backofficeDb = "x";
  backoffice_thread();
  db_close(1);
}

      blob_panic();
    }
  }
  memcpy(&pBlob->aData[pBlob->nUsed], aData, nData);
  pBlob->nUsed += nData;
  pBlob->aData[pBlob->nUsed] = 0;   /* Blobs are always nul-terminated */
}

/*
** Append a single character to the blob
*/
void blob_append_char(Blob *pBlob, char c){
  if( pBlob->nUsed+1 >= pBlob->nAlloc ){
    pBlob->xRealloc(pBlob, pBlob->nUsed + pBlob->nAlloc + 100);
    if( pBlob->nUsed + 1 >= pBlob->nAlloc ){
      blob_panic();
    }
  }
  pBlob->aData[pBlob->nUsed++] = c;    
}

/*
** Copy a blob
*/
void blob_copy(Blob *pTo, Blob *pFrom){
  blob_is_init(pFrom);
  blob_zero(pTo);
  blob_append(pTo, blob_buffer(pFrom), blob_size(pFrom));
}

/*
** Return a pointer to a null-terminated string for a blob.
*/
char *blob_str(Blob *p){
  blob_is_init(p);
  if( p->nUsed==0 ){
    blob_append_char(p, 0); /* NOTE: Changes nUsed. */
    p->nUsed = 0;
  }
  if( p->aData[p->nUsed]!=0 ){
    blob_materialize(p);
  }
  return p->aData;
}

/*
** Return a pointer to a null-terminated string for a blob that has
** been created using blob_append_sql() and not blob_appendf().  If
** text was ever added using blob_appendf() then throw an error.
*/
char *blob_sql_text(Blob *p){
  blob_is_init(p);
  if( (p->blobFlags & BLOBFLAG_NotSQL) ){
    fossil_panic("use of blob_appendf() to construct SQL text");
  }
  return blob_str(p);
}


/*
** Return a pointer to a null-terminated string for a blob.

/*
** Rewind the cursor on a blob back to the beginning.
*/
void blob_rewind(Blob *p){
  p->iCursor = 0;
}

/*
** Truncate a blob back to zero length
*/
void blob_truncate(Blob *p, int sz){
  if( sz>=0 && sz<p->nUsed ) p->nUsed = sz;
}

/*
** Seek the cursor in a blob to the indicated offset.
*/
int blob_seek(Blob *p, int offset, int whence){
  if( whence==BLOB_SEEK_SET ){
    p->iCursor = offset;

    i++;
  }
  if( pTo ){
    blob_append(pTo, &pFrom->aData[pFrom->iCursor], i - pFrom->iCursor);
  }
  pFrom->iCursor = i;
}

/*
** Ensure that the text in pBlob ends with '\n'
*/
void blob_add_final_newline(Blob *pBlob){
  if( pBlob->nUsed<=0 ) return;
  if( pBlob->aData[pBlob->nUsed-1]!='\n' ){
    blob_append_char(pBlob, '\n');
  }
}

/*
** Return true if the blob contains a valid base16 identifier artifact hash.
**
** The value returned is actually one of HNAME_SHA1 OR HNAME_K256 if the
** hash is valid.  Both of these are non-zero and therefore "true".
** If the hash is not valid, then HNAME_ERROR is returned, which is zero or

                   zIn, blob_str(&bad), c);
    }
    if( !needEscape && !fossil_isalnum(c) && c!='/' && c!='.' && c!='_' ){
      needEscape = 1;
    }
  }
  if( n>0 && !fossil_isspace(z[n-1]) ){
    blob_append_char(pBlob, ' ');
  }
  if( needEscape ) blob_append_char(pBlob, cQuote);
  if( zIn[0]=='-' ) blob_append(pBlob, "./", 2);
  blob_append(pBlob, zIn, -1);
  if( needEscape ) blob_append_char(pBlob, cQuote);
}

/*
** A read(2)-like impl for the Blob class. Reads (copies) up to nLen
** bytes from pIn, starting at position pIn->iCursor, and copies them
** to pDest (which must be valid memory at least nLen bytes long).
**

        /* swap bytes of unicode representation */
        char zTemp = zUtf8[--i];
        zUtf8[i] = zUtf8[i-1];
        zUtf8[--i] = zTemp;
      }
    }
    /* Make sure the blob contains two terminating 0-bytes */
    blob_append_char(pBlob, 0);
    zUtf8 = blob_str(pBlob) + bomSize;
    zUtf8 = fossil_unicode_to_utf8(zUtf8);
    blob_set_dynamic(pBlob, zUtf8);
  }else if( useMbcs && invalid_utf8(pBlob) ){
#if defined(_WIN32) || defined(__CYGWIN__)
    zUtf8 = fossil_mbcs_to_utf8(blob_str(pBlob));
    blob_reset(pBlob);

**    type=TYPE        TYPE=flat: use this display
**                     TYPE=tree: use the /tree display instead
*/
void page_dir(void){
  char *zD = fossil_strdup(P("name"));
  int nD = zD ? strlen(zD)+1 : 0;
  int mxLen;


  char *zPrefix;
  Stmt q;
  const char *zCI = P("ci");
  int rid = 0;
  char *zUuid = 0;
  Blob dirname;
  Manifest *pM = 0;

    );
  }

  /* Generate a multi-column table listing the contents of zD[]
  ** directory.
  */
  mxLen = db_int(12, "SELECT max(length(x)) FROM localfiles /*scan*/");

  if( mxLen<12 ) mxLen = 12;




  db_prepare(&q, "SELECT x, u FROM localfiles ORDER BY x /*scan*/");
  @ <div class="columns" style="column-width: %d(mxLen+(mxLen+9)/10)ex;">
  @ <ul class="browser">

  while( db_step(&q)==SQLITE_ROW ){
    const char *zFN;





    zFN = db_column_text(&q, 0);
    if( zFN[0]=='/' ){
      zFN++;
      @ <li class="dir">%z(href("%s%T",zSubdirLink,zFN))%h(zFN)</a></li>
    }else{
      const char *zLink;
      if( zCI ){
        const char *zUuid = db_column_text(&q, 1);
        zLink = href("%R/artifact/%!S",zUuid);
      }else{
        zLink = href("%R/finfo?name=%T%T",zPrefix,zFN);
      }
      @ <li class="%z(fileext_class(zFN))">%z(zLink)%h(zFN)</a></li>
    }
  }
  db_finalize(&q);
  manifest_destroy(pM);
  @ </ul></div>

  /* If the directory contains a readme file, then display its content below
  ** the list of files
  */
  db_prepare(&q,
    "SELECT x, u FROM localfiles"
    " WHERE x COLLATE nocase IN"

  char *zErrMsg = 0;
  char *zSql;
  if( !doInit && file_size(zFile, ExtFILE)<0 ){
    fossil_fatal("no such file: %s", zFile);
  }
  assert( g.db );
  zSql = sqlite3_mprintf("ATTACH %Q AS %Q", zFile, zBName);
  if( zSql==0 ) fossil_panic("out of memory");
  rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
  sqlite3_free(zSql);
  if( rc!=SQLITE_OK || zErrMsg ){
    if( zErrMsg==0 ) zErrMsg = (char*)sqlite3_errmsg(g.db);
    fossil_fatal("not a valid bundle: %s", zFile);
  }
  if( doInit ){
    db_multi_exec(zBundleInit /*works-like:"%w%w"*/, zBName, zBName);
  }else{
    sqlite3_stmt *pStmt;
    zSql = sqlite3_mprintf("SELECT bcname, bcvalue"
                           "  FROM \"%w\".bconfig", zBName);
    if( zSql==0 ) fossil_panic("out of memory");
    rc = sqlite3_prepare(g.db, zSql, -1, &pStmt, 0);
    if( rc ) fossil_fatal("not a valid bundle: %s", zFile);
    sqlite3_free(zSql);
    sqlite3_finalize(pStmt);
    zSql = sqlite3_mprintf("SELECT blobid, uuid, sz, delta, notes, data"
                           "  FROM \"%w\".bblob", zBName);
    if( zSql==0 ) fossil_panic("out of memory");
    rc = sqlite3_prepare(g.db, zSql, -1, &pStmt, 0);
    if( rc ) fossil_fatal("not a valid bundle: %s", zFile);
    sqlite3_free(zSql);
    sqlite3_finalize(pStmt);
  }
}

  }
  rc = sqlite3_open(zDbName, &db);
  fossil_free(zDbName);
  if( rc ){
    sqlite3_close(db);
    return 0;
  }
  sqlite3_busy_timeout(db, 5000);
  if( sqlite3_table_column_metadata(db,0,"blob","key",0,0,0,0,0)!=SQLITE_OK ){
    rc = sqlite3_exec(db,
       "PRAGMA page_size=8192;"
       "CREATE TABLE IF NOT EXISTS blob(id INTEGER PRIMARY KEY, data BLOB);"
       "CREATE TABLE IF NOT EXISTS cache("
         "key TEXT PRIMARY KEY,"     /* Key used to access the cache */
         "id INT REFERENCES blob,"   /* The cache content */
         "sz INT,"                   /* Size of content in bytes */
         "tm INT,"                   /* Last access time (unix timestampe) */
         "nref INT"                  /* Number of uses */
       ");"
       "CREATE TRIGGER IF NOT EXISTS cacheDel AFTER DELETE ON cache BEGIN"
       "  DELETE FROM blob WHERE id=OLD.id;"
       "END;",
       0, 0, 0
    );
    if( rc!=SQLITE_OK ){
      sqlite3_close(db);
      return 0;
    }
  }
  return db;
}

/*
** Attempt to construct a prepared statement for the cache database.
*/

/*
** Copyright (c) 2018 D. Richard Hipp
**
** This program is free software; you can redistribute it and/or
** modify it under the terms of the Simplified BSD License (also
** known as the "2-Clause License" or "FreeBSD License".)
**
** 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.
**
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*******************************************************************************
**
** This file contains code used managing user capability strings.
*/
#include "config.h"
#include "capabilities.h"
#include <assert.h>

#if INTERFACE
/*
** A capability string object holds all defined capabilities in a
** vector format that is subject to boolean operations.
*/
struct CapabilityString {
  unsigned char x[128];
};
#endif

/*
** Add capabilities to a CapabilityString.  If pIn is NULL, then create
** a new capability string.
**
** Call capability_free() on the allocated CapabilityString object to
** deallocate.
*/
CapabilityString *capability_add(CapabilityString *pIn, const char *zCap){
  int c;
  int i;
  if( pIn==0 ){
    pIn = fossil_malloc( sizeof(*pIn) );
    memset(pIn, 0, sizeof(*pIn));
  }
  if( zCap ){
    for(i=0; (c = zCap[i])!=0; i++){
      if( c>='0' && c<='z' ) pIn->x[c] = 1;
    }
  }
  return pIn;
}

/*
** Remove capabilities from a CapabilityString.
*/
CapabilityString *capability_remove(CapabilityString *pIn, const char *zCap){
  int c;
  int i;
  if( pIn==0 ){
    pIn = fossil_malloc( sizeof(*pIn) );
    memset(pIn, 0, sizeof(*pIn));
  }
  if( zCap ){
    for(i=0; (c = zCap[i])!=0; i++){
      if( c>='0' && c<='z' ) pIn->x[c] = 0;
    }
  }
  return pIn;
}

/*
** Return true if any of the capabilities in zNeeded are found in pCap
*/
int capability_has_any(CapabilityString *p, const char *zNeeded){
  if( p==0 ) return 0;
  if( zNeeded==0 ) return 0;
  while( zNeeded[0] ){
    int c = zNeeded[0];
    if( fossil_isalnum(c) && p->x[c] ) return 1;
    zNeeded++;
  }
  return 0;
}

/*
** Delete a CapabilityString object.
*/
void capability_free(CapabilityString *p){
  fossil_free(p);
}

/*
** Expand the capability string by including all capabilities for 
** special users "nobody" and "anonymous".  Also include "reader"
** if "u" is present and "developer" if "v" is present.
*/
void capability_expand(CapabilityString *pIn){
  static char *zNobody = 0;
  static char *zAnon = 0;
  static char *zReader = 0;
  static char *zDev = 0;
  int doneV = 0;

  if( pIn==0 ){
    fossil_free(zNobody); zNobody = 0;
    fossil_free(zAnon);   zAnon = 0;
    fossil_free(zReader); zReader = 0;
    fossil_free(zDev);    zDev = 0;
    return;
  }
  if( zNobody==0 ){
    zNobody = db_text(0, "SELECT cap FROM user WHERE login='nobody'");
    zAnon = db_text(0, "SELECT cap FROM user WHERE login='anonymous'");
    zReader = db_text(0, "SELECT cap FROM user WHERE login='reader'");
    zDev = db_text(0, "SELECT cap FROM user WHERE login='developer'");
  }
  pIn = capability_add(pIn, zAnon);
  pIn = capability_add(pIn, zNobody);
  if( pIn->x['v'] ){
    pIn = capability_add(pIn, zDev);
    doneV = 1;
  }
  if( pIn->x['u'] ){
    pIn = capability_add(pIn, zReader);
    if( pIn->x['v'] && !doneV ){
      pIn = capability_add(pIn, zDev);
    }
  }
}

/*
** Render a capability string in canonical string format.  Space to hold
** the returned string is obtained from fossil_malloc() can should be freed
** by the caller.
*/
char *capability_string(CapabilityString *p){
  Blob out;
  int i;
  int j = 0;
  char buf[100];
  blob_init(&out, 0, 0);
  for(i='a'; i<='z'; i++){
    if( p->x[i] ) buf[j++] = i;
  }
  for(i='0'; i<='9'; i++){
    if( p->x[i] ) buf[j++] = i;
  }
  for(i='A'; i<='Z'; i++){
    if( p->x[i] ) buf[j++] = i;
  }
  buf[j] = 0;
  return fossil_strdup(buf);
}

/*
** The next two routines implement an aggregate SQL function that
** takes multiple capability strings and in the end returns their
** union.  Example usage:
**
**    SELECT capunion(cap) FROM user WHERE login IN ('nobody','anonymous');
*/
void capability_union_step(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  CapabilityString *p;
  const char *zIn;

  zIn = (const char*)sqlite3_value_text(argv[0]);
  if( zIn==0 ) return;
  p = (CapabilityString*)sqlite3_aggregate_context(context, sizeof(*p));
  p = capability_add(p, zIn);
}
void capability_union_finalize(sqlite3_context *context){
  CapabilityString *p;
  p = sqlite3_aggregate_context(context, 0);
  if( p ){
    char *zOut = capability_string(p);
    sqlite3_result_text(context, zOut, -1, fossil_free);
  }
}

/*
** The next routines takes the raw USER.CAP field and expands it with
** capabilities from special users.  Example:
**
**   SELECT fullcap(cap) FROM user WHERE login=?1
*/
void capability_fullcap(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  CapabilityString *p;
  const char *zIn;
  char *zOut;

  zIn = (const char*)sqlite3_value_text(argv[0]);
  if( zIn==0 ) zIn = "";
  p = capability_add(0, zIn);
  capability_expand(p);
  zOut = capability_string(p);
  sqlite3_result_text(context, zOut, -1, fossil_free);
  capability_free(p);
}

/*
** Generate HTML that lists all of the capability letters together with
** a brief summary of what each letter means.
*/
void capabilities_table(void){
  @ <table>
     @ <tr><th valign="top">a</th>
     @   <td><i>Admin:</i> Create and delete users</td></tr>
     @ <tr><th valign="top">b</th>
     @   <td><i>Attach:</i> Add attachments to wiki or tickets</td></tr>
     @ <tr><th valign="top">c</th>
     @   <td><i>Append-Tkt:</i> Append to tickets</td></tr>
     @ <tr><th valign="top">d</th>
     @   <td><i>Delete:</i> Delete wiki and tickets</td></tr>
     @ <tr><th valign="top">e</th>
     @   <td><i>View-PII:</i> \
     @ View sensitive data such as email addresses</td></tr>
     @ <tr><th valign="top">f</th>
     @   <td><i>New-Wiki:</i> Create new wiki pages</td></tr>
     @ <tr><th valign="top">g</th>
     @   <td><i>Clone:</i> Clone the repository</td></tr>
     @ <tr><th valign="top">h</th>
     @   <td><i>Hyperlinks:</i> Show hyperlinks to detailed
     @   repository history</td></tr>
     @ <tr><th valign="top">i</th>
     @   <td><i>Check-In:</i> Commit new versions in the repository</td></tr>
     @ <tr><th valign="top">j</th>
     @   <td><i>Read-Wiki:</i> View wiki pages</td></tr>
     @ <tr><th valign="top">k</th>
     @   <td><i>Write-Wiki:</i> Edit wiki pages</td></tr>
     @ <tr><th valign="top">l</th>
     @   <td><i>Mod-Wiki:</i> Moderator for wiki pages</td></tr>
     @ <tr><th valign="top">m</th>
     @   <td><i>Append-Wiki:</i> Append to wiki pages</td></tr>
     @ <tr><th valign="top">n</th>
     @   <td><i>New-Tkt:</i> Create new tickets</td></tr>
     @ <tr><th valign="top">o</th>
     @   <td><i>Check-Out:</i> Check out versions</td></tr>
     @ <tr><th valign="top">p</th>
     @   <td><i>Password:</i> Change your own password</td></tr>
     @ <tr><th valign="top">q</th>
     @   <td><i>Mod-Tkt:</i> Moderator for tickets</td></tr>
     @ <tr><th valign="top">r</th>
     @   <td><i>Read-Tkt:</i> View tickets</td></tr>
     @ <tr><th valign="top">s</th>
     @   <td><i>Setup/Super-user:</i> Setup and configure this website</td></tr>
     @ <tr><th valign="top">t</th>
     @   <td><i>Tkt-Report:</i> Create new bug summary reports</td></tr>
     @ <tr><th valign="top">u</th>
     @   <td><i>Reader:</i> Inherit privileges of
     @   user <tt>reader</tt></td></tr>
     @ <tr><th valign="top">v</th>
     @   <td><i>Developer:</i> Inherit privileges of
     @   user <tt>developer</tt></td></tr>
     @ <tr><th valign="top">w</th>
     @   <td><i>Write-Tkt:</i> Edit tickets</td></tr>
     @ <tr><th valign="top">x</th>
     @   <td><i>Private:</i> Push and/or pull private branches</td></tr>
     @ <tr><th valign="top">y</th>
     @   <td><i>Write-Unver:</i> Push unversioned files</td></tr>
     @ <tr><th valign="top">z</th>
     @   <td><i>Zip download:</i> Download a ZIP archive or tarball</td></tr>
     @ <tr><th valign="top">2</th>
     @   <td><i>Forum-Read:</i> Read forum posts by others </td></tr>
     @ <tr><th valign="top">3</th>
     @   <td><i>Forum-Append:</i> Add new forum posts</td></tr>
     @ <tr><th valign="top">4</th>
     @   <td><i>Forum-Trusted:</i> Add pre-approved forum posts </td></tr>
     @ <tr><th valign="top">5</th>
     @   <td><i>Forum-Moderator:</i> Approve or disapprove forum posts</td></tr>
     @ <tr><th valign="top">6</th>
     @   <td><i>Forum-Supervisor:</i> \
     @ Forum administrator: Set or remove capability "4" for other users
     @ <tr><th valign="top">7</th>
     @   <td><i>Email-Alerts:</i> Sign up for email nofications</td></tr>
     @ <tr><th valign="top">A</th>
     @   <td><i>Announce:</i> Send announcements</td></tr>
     @ <tr><th valign="top">D</th>
     @   <td><i>Debug:</i> Enable debugging features</td></tr>
  @ </table>
}

/*
** Generate a "capability summary table" that shows the major capabilities
** against the various user categories.
*/
void capability_summary(void){
  Stmt q;
  db_prepare(&q,
    "WITH t(id,seq) AS (VALUES('nobody',1),('anonymous',2),('reader',3),"
                       "('developer',4))"
    " SELECT id, fullcap(user.cap),seq,1"
    "   FROM t LEFT JOIN user ON t.id=user.login"
    " UNION ALL"
    " SELECT 'New User Default', fullcap(%Q), 10, 1"
    " UNION ALL"
    " SELECT 'Regular User', fullcap(capunion(cap)), 20, count(*) FROM user"
    " WHERE cap NOT GLOB '*[as]*'"
    " UNION ALL"
    " SELECT 'Adminstator', fullcap(capunion(cap)), 30, count(*) FROM user"
    " WHERE cap GLOB '*[as]*'"
    " ORDER BY 3 ASC",
    db_get("default-perms","")
  );
  @ <table id='capabilitySummary' cellpadding="0" cellspacing="0" border="1">
  @ <tr><th>&nbsp;<th>Code<th>Forum<th>Tickets<th>Wiki\
  @ <th>Unversioned Content</th></tr>
  while( db_step(&q)==SQLITE_ROW ){
    const char *zId = db_column_text(&q, 0);
    const char *zCap = db_column_text(&q, 1);
    int n = db_column_int(&q, 3);
    int eType;
    static const char *azType[] = { "off", "read", "write" };
    static const char *azClass[] = { "capsumOff", "capsumRead", "capsumWrite" };

    if( n==0 ) continue;

    /* Code */
    if( db_column_int(&q,2)<10 ){
      @ <tr><th align="right"><tt>"%h(zId)"</tt></th>
    }else if( n>1 ){
      @ <tr><th align="right">%d(n) %h(zId)s</th>
    }else{
      @ <tr><th align="right">%h(zId)</th>
    }
    if( sqlite3_strglob("*[asi]*",zCap)==0 ){
      eType = 2;
    }else if( sqlite3_strglob("*[oz]*",zCap)==0 ){
      eType = 1;
    }else{
      eType = 0;
    }
    @ <td class="%s(azClass[eType])">%s(azType[eType])</td>

    /* Forum */
    if( sqlite3_strglob("*[as3456]*",zCap)==0 ){
      eType = 2;
    }else if( sqlite3_strglob("*2*",zCap)==0 ){
      eType = 1;
    }else{
      eType = 0;
    }
    @ <td class="%s(azClass[eType])">%s(azType[eType])</td>

    /* Ticket */
    if( sqlite3_strglob("*[ascdnqtw]*",zCap)==0 ){
      eType = 2;
    }else if( sqlite3_strglob("*r*",zCap)==0 ){
      eType = 1;
    }else{
      eType = 0;
    }
    @ <td class="%s(azClass[eType])">%s(azType[eType])</td>

    /* Wiki */
    if( sqlite3_strglob("*[asdfklm]*",zCap)==0 ){
      eType = 2;
    }else if( sqlite3_strglob("*j*",zCap)==0 ){
      eType = 1;
    }else{
      eType = 0;
    }
    @ <td class="%s(azClass[eType])">%s(azType[eType])</td>

    /* Unversioned */
    if( sqlite3_strglob("*y*",zCap)==0 ){
      eType = 2;
    }else if( sqlite3_strglob("*[ioas]*",zCap)==0 ){
      eType = 1;
    }else{
      eType = 0;
    }
    @ <td class="%s(azClass[eType])">%s(azType[eType])</td>

  }
  db_finalize(&q);
  @ </table>
}

**
** If no captcha is required or if the correct captcha is supplied, return
** true (non-zero).
**
** The query parameters examined are "captchaseed" for the seed value and
** "captcha" for text that the user types in response to the captcha prompt.
*/
int captcha_is_correct(int bAlwaysNeeded){
  const char *zSeed;
  const char *zEntered;
  const char *zDecode;
  char z[30];
  int i;
  if( !bAlwaysNeeded && !captcha_needed() ){
    return 1;  /* No captcha needed */
  }
  zSeed = P("captchaseed");
  if( zSeed==0 ) return 0;
  zEntered = P("captcha");
  if( zEntered==0 || strlen(zEntered)!=8 ) return 0;
  zDecode = captcha_decode((unsigned int)atoi(zSeed));

      return 0;
    }
  }
#endif
  zCookieName = mprintf("fossil-cc-%.10s", db_get("project-code","x"));
  zCookieValue = P(zCookieName);
  if( zCookieValue && atoi(zCookieValue)==1 ) return 0;
  if( captcha_is_correct(0) ){
    cgi_set_cookie(zCookieName, "1", login_cookie_path(), 8*3600);
    return 0;
  }

  /* This appears to be a spider.  Offer the captcha */
  style_header("Verification");
  @ <form method='POST' action='%s(g.zPath)'>

** does the same except "y" is returned in place of NULL if there is not match.
*/
#define P(x)        cgi_parameter((x),0)
#define PD(x,y)     cgi_parameter((x),(y))
#define PT(x)       cgi_parameter_trimmed((x),0)
#define PDT(x,y)    cgi_parameter_trimmed((x),(y))
#define PB(x)       cgi_parameter_boolean(x)
#define PCK(x)      cgi_parameter_checked(x,1)
#define PIF(x,y)    cgi_parameter_checked(x,y)


/*
** Destinations for output text.
*/
#define CGI_HEADER   0
#define CGI_BODY     1

** Append text to the header of an HTTP reply
*/
void cgi_append_header(const char *zLine){
  blob_append(&extraHeader, zLine, -1);
}

/*
** Set a cookie by queuing up the appropriate HTTP header output. If
** !g.isHTTP, this is a no-op.
**
** Zero lifetime implies a session cookie.
*/
void cgi_set_cookie(
  const char *zName,    /* Name of the cookie */
  const char *zValue,   /* Value of the cookie.  Automatically escaped */
  const char *zPath,    /* Path cookie applies to.  NULL means "/" */
  int lifetime          /* Expiration of the cookie in seconds from now */
){
  char const *zSecure = "";
  if(!g.isHTTP) return /* e.g. JSON CLI mode, where g.zTop is not set */;
  else if( zPath==0 ){
    zPath = g.zTop;
    if( zPath[0]==0 ) zPath = "/";
  }
  if( g.zBaseURL!=0 && strncmp(g.zBaseURL, "https:", 6)==0 ){
    zSecure = " secure;";
  }
  if( lifetime>0 ){

}


/*
** Return true if the response should be sent with Content-Encoding: gzip.
*/
static int is_gzippable(void){
  if( g.fNoHttpCompress ) return 0;
  if( strstr(PD("HTTP_ACCEPT_ENCODING", ""), "gzip")==0 ) return 0;
  return strncmp(zContentType, "text/", 5)==0
    || sqlite3_strglob("application/*xml", zContentType)==0
    || sqlite3_strglob("application/*javascript", zContentType)==0;
}

/*

      if( size>0 ){
        fwrite(blob_buffer(&cgiContent[i]), 1, size, g.httpOut);
      }
    }
  }
  fflush(g.httpOut);
  CGIDEBUG(("DONE\n"));

  /* After the webpage has been sent, do any useful background
  ** processing.
  */
  g.cgiOutput = 2;
  if( g.db!=0 && iReplyStatus==200 ){
    backoffice_check_if_needed();
  }
}

/*
** Do a redirect request to the URL given in the argument.
**
** The URL must be relative to the base of the fossil server.
*/

  if( z==0 ){
    if( pLog ) fclose(pLog);
    pLog = 0;
    return;
  }
  if( pLog==0 ){
    char zFile[50];
#if defined(_WIN32)
    unsigned r;
    sqlite3_randomness(sizeof(r), &r);
    sqlite3_snprintf(sizeof(zFile), zFile, "httplog-%08x.txt", r);
#else
    sqlite3_snprintf(sizeof(zFile), zFile, "httplog-%05d.txt", getpid());
#endif
    pLog = fossil_fopen(zFile, "wb");
    if( pLog ){
      fprintf(stderr, "# open log on %s\n", zFile);
    }else{
      fprintf(stderr, "# failed to open %s\n", zFile);
      return;
    }

  }
  CGIDEBUG(("no-match [%s]\n", zName));
  return zDefault;
}

/*
** Return the value of a CGI parameter with leading and trailing
** spaces removed and with internal \r\n changed to just \n
*/
char *cgi_parameter_trimmed(const char *zName, const char *zDefault){
  const char *zIn;
  char *zOut, c;
  int i, j;
  zIn = cgi_parameter(zName, 0);
  if( zIn==0 ) zIn = zDefault;
  if( zIn==0 ) return 0;
  while( fossil_isspace(zIn[0]) ) zIn++;
  zOut = fossil_strdup(zIn);
  for(i=j=0; (c = zOut[i])!=0; i++){
    if( c=='\r' && zOut[i+1]=='\n' ) continue;
    zOut[j++] = c;
  }
  zOut[j] = 0;
  while( j>0 && fossil_isspace(zOut[j-1]) ) zOut[--j] = 0;
  return zOut;
}

/*
** Return true if the CGI parameter zName exists and is not equal to 0,
** or "no" or "off".
*/
int cgi_parameter_boolean(const char *zName){
  const char *zIn = cgi_parameter(zName, 0);
  if( zIn==0 ) return 0;
  return zIn[0]==0 || is_truth(zIn);
}

/*
** Return either an empty string "" or the string "checked" depending
** on whether or not parameter zName has value iValue.  If parameter
** zName does not exist, that is assumed to be the same as value 0.
**
** This routine implements the PCK(x) and PIF(x,y) macros.  The PIF(x,y)
** macro generateds " checked" if the value of parameter x equals integer y.
** PCK(x) is the same as PIF(x,1).  These macros are used to generate
** the "checked" attribute on checkbox and radio controls of forms.
*/
const char *cgi_parameter_checked(const char *zName, int iValue){
  const char *zIn = cgi_parameter(zName,0);
  int x;
  if( zIn==0 ){
    x = 0;
  }else if( !fossil_isdigit(zIn[0]) ){
    x = is_truth(zIn);
  }else{
    x = atoi(zIn);
  }
  return x==iValue ? "checked" : "";
}

/*
** Return the name of the i-th CGI parameter.  Return NULL if there
** are fewer than i registered CGI parameters.
*/
const char *cgi_parameter_name(int i){
  if( i>=0 && i<nUsedQP ){

/*
** Print all query parameters on standard output.  Format the
** parameters as HTML.  This is used for testing and debugging.
**
** Omit the values of the cookies unless showAll is true.
*/
void cgi_print_all(int showAll, int onConsole){
  int i;
  cgi_parameter("","");  /* Force the parameters into sorted order */
  for(i=0; i<nUsedQP; i++){
    const char *zName = aParamQP[i].zName;
    if( !showAll ){
      if( fossil_stricmp("HTTP_COOKIE",zName)==0 ) continue;
      if( fossil_strnicmp("fossil-",zName,7)==0 ) continue;
    }
    if( onConsole ){
      fossil_trace("%s = %s\n", zName, aParamQP[i].zValue);
    }else{
      cgi_printf("%h = %h  <br />\n", zName, aParamQP[i].zValue);
    }
  }
}

/*
** Export all untagged query parameters (but not cookies or environment
** variables) as hidden values of a form.
*/

    *zInput = 0;
    zInput++;
    while( fossil_isspace(*zInput) ){ zInput++; }
  }
  if( zLeftOver ){ *zLeftOver = zInput; }
  return zResult;
}

/*
** Determine the IP address on the other side of a connection.
** Return a pointer to a string.  Or return 0 if unable.
**
** The string is held in a static buffer that is overwritten on
** each call.
*/
char *cgi_remote_ip(int fd){
#if 0
  static char zIp[100];
  struct sockaddr_in6 addr;
  socklen_t sz = sizeof(addr);
  if( getpeername(fd, &addr, &sz) ) return 0;
  zIp[0] = 0;
  if( inet_ntop(AF_INET6, &addr, zIp, sizeof(zIp))==0 ){
    return 0;
  }
  return zIp;
#else
  struct sockaddr_in remoteName;
  socklen_t size = sizeof(struct sockaddr_in);
  if( getpeername(fd, (struct sockaddr*)&remoteName, &size) ) return 0;
  return inet_ntoa(remoteName.sin_addr);
#endif
}

/*
** This routine handles a single HTTP request which is coming in on
** g.httpIn and which replies on g.httpOut
**
** The HTTP request is read from g.httpIn and is used to initialize
** entries in the cgi_parameter() hash, as if those entries were
** environment variables.  A call to cgi_init() completes
** the setup.  Once all the setup is finished, this procedure returns
** and subsequent code handles the actual generation of the webpage.
*/
void cgi_handle_http_request(const char *zIpAddr){
  char *z, *zToken;
  int i;


  char zLine[2000];     /* A single line of input. */
  g.fullHttpReply = 1;
  if( fgets(zLine, sizeof(zLine),g.httpIn)==0 ){
    malformed_request("missing HTTP header");
  }
  blob_append(&g.httpHeader, zLine, -1);
  cgi_trace(zLine);

  }
  cgi_setenv("REQUEST_URI", zToken);
  cgi_setenv("SCRIPT_NAME", "");
  for(i=0; zToken[i] && zToken[i]!='?'; i++){}
  if( zToken[i] ) zToken[i++] = 0;
  cgi_setenv("PATH_INFO", zToken);
  cgi_setenv("QUERY_STRING", &zToken[i]);
  if( zIpAddr==0 ){
    zIpAddr = cgi_remote_ip(fileno(g.httpIn));



  }
  if( zIpAddr ){
    cgi_setenv("REMOTE_ADDR", zIpAddr);
    g.zIpAddr = mprintf("%s", zIpAddr);
  }

  /* Get all the optional fields that follow the first line.

          int nErr = 0, fd;
          close(0);
          fd = dup(connection);
          if( fd!=0 ) nErr++;
          close(1);
          fd = dup(connection);
          if( fd!=1 ) nErr++;
          if( 0 && !g.fAnyTrace ){
            close(2);
            fd = dup(connection);
            if( fd!=2 ) nErr++;
          }
          close(connection);
          g.nPendingRequest = nchildren+1;
          g.nRequest = nRequest+1;
          return nErr;
        }
      }
    }
    /* Bury dead children */
    if( nchildren ){
      while(1){
        int iStatus = 0;
        pid_t x = waitpid(-1, &iStatus, WNOHANG);
        if( x<=0 ) break;
        if( WIFSIGNALED(iStatus) && g.fAnyTrace ){
          fprintf(stderr, "/***** Child %d exited on signal %d (%s) *****/\n",
                  x, WTERMSIG(iStatus), strsignal(WTERMSIG(iStatus)));
        }
        nchildren--;
      }
    }  
  }
  /* NOT REACHED */
  fossil_exit(1);
#endif

    {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
     "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", 0};


/*
** Returns an RFC822-formatted time string suitable for HTTP headers.
** The timezone is always GMT.  The value returned is always a
** string obtained from mprintf() and must be freed using fossil_free()
** to avoid a memory leak.
**
** See http://www.faqs.org/rfcs/rfc822.html, section 5
** and http://www.faqs.org/rfcs/rfc2616.html, section 3.3.
*/
char *cgi_rfc822_datestamp(time_t now){
  struct tm *pTm;
  pTm = gmtime(&now);
  if( pTm==0 ){
    return mprintf("");
  }else{
    return mprintf("%s, %d %s %02d %02d:%02d:%02d +0000",
                   azDays[pTm->tm_wday], pTm->tm_mday, azMonths[pTm->tm_mon],
                   pTm->tm_year+1900, pTm->tm_hour, pTm->tm_min, pTm->tm_sec);
  }
}

/*
** Parse an RFC822-formatted timestamp as we'd expect from HTTP and return

#if defined(__CYGWIN__)
    zEditor = fossil_utf8_to_path(zEditor, 0);
    blob_add_cr(pPrompt);
#endif
  }
#endif
  if( zEditor==0 ){
    if( blob_size(pPrompt)>0 ){
      blob_append(pPrompt,
         "#\n"
         "# Since no default text editor is set using EDITOR or VISUAL\n"
         "# environment variables or the \"fossil set editor\" command,\n"
         "# and because no comment was specified using the \"-m\" or \"-M\"\n"
         "# command-line options, you will need to enter the comment below.\n"
         "# Type \".\" on a line by itself when you are done:\n", -1);
    }
    zFile = mprintf("-");
  }else{
    Blob fname;
    blob_zero(&fname);
    if( g.zLocalRoot!=0 ){
      file_relative_name(g.zLocalRoot, &fname, 1);
      zFile = db_text(0, "SELECT '%qci-comment-'||hex(randomblob(6))||'.txt'",
                      blob_str(&fname));
    }else{
      file_tempname(&fname, "ci-comment");
      zFile = mprintf("%s", blob_str(&fname));
    }
    blob_reset(&fname);
  }
#if defined(_WIN32)
  blob_add_cr(pPrompt);
#endif
  if( blob_size(pPrompt)>0 ) blob_write_to_file(pPrompt, zFile);
  if( zEditor ){
    zCmd = mprintf("%s \"%s\"", zEditor, zFile);
    fossil_print("%s\n", zCmd);
    if( fossil_system(zCmd) ){
      fossil_fatal("editor aborted: \"%s\"", zCmd);
    }

  if( db_get_boolean("clearsign", 0)==0 ){ noSign = 1; }
  useCksum = db_get_boolean("repo-cksum", 1);
  outputManifest = db_get_manifest_setting();
  verify_all_options();

  /* Do not allow the creation of a new branch using an existing open
  ** branch name unless the --force flag is used */
  if( sCiInfo.zBranch!=0
   && !forceFlag
   && fossil_strcmp(sCiInfo.zBranch,"private")!=0
   && branch_is_open(sCiInfo.zBranch)
  ){
    fossil_fatal("an open branch named \"%s\" already exists - use --force"
                 " to override", sCiInfo.zBranch);
  }

  /* Escape special characters in tags and put all tags in sorted order */
  if( nTag ){
    int i;

      return;
    }
  }else{
    zVers = g.argv[2];
  }
  vid = load_vfile(zVers, forceMissingFlag);
  if( prior==vid ){
    db_end_transaction(0);
    return;
  }
  if( !keepFlag ){
    uncheckout(prior);
  }
  db_multi_exec("DELETE FROM vfile WHERE vid!=%d", vid);
  if( !keepFlag ){

**
** By default, your current login name is used to create the default
** admin user. This can be overridden using the -A|--admin-user
** parameter.
**
** Options:
**    --admin-user|-A USERNAME   Make USERNAME the administrator
**    --nocompress               Omit extra delta compression
**    --once                     Don't remember the URI.
**    --private                  Also clone private branches
**    --ssl-identity FILENAME    Use the SSL identity if requested by the server
**    --ssh-command|-c SSH       Use SSH as the "ssh" command
**    --httpauth|-B USER:PASS    Add HTTP Basic Authorization to requests
**    -u|--unversioned           Also sync unversioned content
**    -v|--verbose               Show more statistics in output
**
** See also: init
*/
void clone_cmd(void){
  char *zPassword;
  const char *zDefaultUser;   /* Optional name of the default user */
  const char *zHttpAuth;      /* HTTP Authorization user:pass information */
  int nErr = 0;
  int urlFlags = URL_PROMPT_PW | URL_REMEMBER;
  int syncFlags = SYNC_CLONE;
  int noCompress = find_option("nocompress",0,0)!=0;

  /* Also clone private branches */
  if( find_option("private",0,0)!=0 ) syncFlags |= SYNC_PRIVATE;
  if( find_option("once",0,0)!=0) urlFlags &= ~URL_REMEMBER;
  if( find_option("verbose","v",0)!=0) syncFlags |= SYNC_VERBOSE;
  if( find_option("unversioned","u",0)!=0 ) syncFlags |= SYNC_UNVERSIONED;
  zHttpAuth = find_option("httpauth","B",1);

      fossil_fatal("server returned an error - clone aborted");
    }
    db_open_repository(g.argv[3]);
  }
  db_begin_transaction();
  fossil_print("Rebuilding repository meta-data...\n");
  rebuild_db(0, 1, 0);
  if( !noCompress ){
    fossil_print("Extra delta compression... "); fflush(stdout);
    extra_deltification();
    fossil_print("\n");
  }
  db_end_transaction(0);
  fossil_print("Vacuuming the database... "); fflush(stdout);
  if( db_int(0, "PRAGMA page_count")>1000
   && db_int(0, "PRAGMA page_size")<8192 ){
     db_multi_exec("PRAGMA page_size=8192;");
  }
  db_multi_exec("VACUUM");
  fossil_print("\nproject-id: %s\n", db_get("project-code", 0));
  fossil_print("server-id:  %s\n", db_get("server-code", 0));

*/
#include <stdio.h>
#include <stdlib.h>
#include <ctype.h>
#include <string.h>
#include <assert.h>

/*
** Debugging switch
*/
static int eVerbose = 0;

/*
** Malloc, aborting if it fails.
*/
void *safe_malloc(int nByte){
  void *x = malloc(nByte);
  if( x==0 ){
    fprintf(stderr, "failed to allocate %d bytes\n", nByte);

/*
** Return the first non-whitespace characters in z[]
*/
static const char *skip_space(const char *z){
  while( isspace(z[0]) ){ z++; }
  return z;
}

/*
** Remove excess whitespace and nested "()" from string z.
*/
static char *simplify_expr(char *z){
  int n = (int)strlen(z);
  while( n>0 ){
    if( isspace(z[0]) ){
      z++;
      n--;
      continue;
    }
    if( z[0]=='(' && z[n-1]==')' ){
      z++;
      n -= 2;
      continue;
    }
    break;
  }
  z[n] = 0;
  return z;
}

/*
** Return true if the input is a string literal.
*/
static int is_string_lit(const char *z){
  int nu1, nu2;
  z = next_non_whitespace(z, &nu1, &nu2);

  "db_setting_inop_rhs",
};

/*
** Return true if the input is an argument that is safe to use with %s
** while building an SQL statement.
*/
static int is_sql_safe(const char *z){
  int len, eType;
  int i;

  /* A string literal is safe for use with %s */
  if( is_string_lit(z) ) return 1;

  /* Certain functions are guaranteed to return a string that is safe

  /* If the "safe-for-%s" comment appears in the argument, then
  ** let it through */
  if( strstr(z, "/*safe-for-%s*/")!=0 ) return 1;

  return 0;
}

/*
** Return true if the input is an argument that is never safe for use
** with %s.
*/
static int never_safe(const char *z){
  if( strstr(z,"/*safe-for-%s*/")!=0 ) return 0;
  if( z[0]=='P' ){
    if( strncmp(z,"PIF(",4)==0 ) return 0;
    if( strncmp(z,"PCK(",4)==0 ) return 0;
    return 1;
  }
  if( strncmp(z,"cgi_param",9)==0 ) return 1;
  return 0;
}

/*
** Processing flags
*/
#define FMT_SQL   0x00001     /* Generates SQL text */
#define FMT_HTML  0x00002     /* Generates HTML text */
#define FMT_URL   0x00004     /* Generates URLs */
#define FMT_SAFE  0x00008     /* Always safe for %s */

/*
** A list of internal Fossil interfaces that take a printf-style format
** string.
*/
struct {
  const char *zFName;    /* Name of the function */
  int iFmtArg;           /* Index of format argument.  Leftmost is 1. */
  unsigned fmtFlags;     /* Processing flags */
} aFmtFunc[] = {
  { "admin_log",               1, 0 },
  { "blob_append_sql",         2, FMT_SQL },
  { "blob_appendf",            2, 0 },
  { "cgi_debug",               1, FMT_SAFE },
  { "cgi_panic",               1, FMT_SAFE },
  { "cgi_printf",              1, FMT_HTML },
  { "cgi_redirectf",           1, FMT_URL },
  { "chref",                   2, FMT_URL },
  { "db_blob",                 2, FMT_SQL },
  { "db_debug",                1, FMT_SQL },
  { "db_double",               2, FMT_SQL },
  { "db_err",                  1, 0 },
  { "db_exists",               1, FMT_SQL },
  { "db_get_mprintf",          2, 0 },
  { "db_int",                  2, FMT_SQL },
  { "db_int64",                2, FMT_SQL },
  { "db_multi_exec",           1, FMT_SQL },
  { "db_optional_sql",         2, FMT_SQL },
  { "db_prepare",              2, FMT_SQL },
  { "db_prepare_ignore_error", 2, FMT_SQL },
  { "db_set_mprintf",          3, 0 },
  { "db_static_prepare",       2, FMT_SQL },
  { "db_text",                 2, FMT_SQL },
  { "db_unset_mprintf",        2, 0 },
  { "form_begin",              2, FMT_URL },
  { "fossil_error",            2, FMT_SAFE },
  { "fossil_errorlog",         1, FMT_SAFE },
  { "fossil_fatal",            1, FMT_SAFE },
  { "fossil_fatal_recursive",  1, FMT_SAFE },
  { "fossil_panic",            1, FMT_SAFE },
  { "fossil_print",            1, FMT_SAFE },
  { "fossil_trace",            1, FMT_SAFE },
  { "fossil_warning",          1, FMT_SAFE },
  { "href",                    1, FMT_URL },
  { "json_new_string_f",       1, 0 },
  { "json_set_err",            2, 0 },
  { "json_warn",               2, 0 },
  { "mprintf",                 1, 0 },
  { "socket_set_errmsg",       1, 0 },
  { "ssl_set_errmsg",          1, 0 },
  { "style_header",            1, FMT_HTML },
  { "style_set_current_page",  1, FMT_URL },
  { "style_submenu_element",   2, FMT_URL },
  { "style_submenu_sql",       3, FMT_SQL },
  { "webpage_error",           1, FMT_SAFE },
  { "xhref",                   2, FMT_URL },
};

/*
** Determine if the indentifier zIdent of length nIndent is a Fossil
** internal interface that uses a printf-style argument.  Return zero if not.
** Return the index of the format string if true with the left-most
** argument having an index of 1.

  zCopy = safe_malloc( len + 1 );
  memcpy(zCopy, zStart+1, len);
  zCopy[len] = 0;
  azArg = 0;
  nArg = 0;
  z = zCopy;
  while( z[0] ){
    char cEnd;
    len = distance_to(z, ',');
    cEnd = z[len];
    z[len] = 0;
    azArg = safe_realloc((char*)azArg, (sizeof(azArg[0])+1)*(nArg+1));
    azArg[nArg++] = simplify_expr(z);
    if( cEnd==0 ) break;


    z += len + 1;
  }
  acType = (char*)&azArg[nArg];
  if( fmtArg>nArg ){
    printf("%s:%d: too few arguments to %.*s()\n",
           zFilename, lnFCall, szFName, zFCall);
    nErr++;

    }else if( (k = formatArgCount(zFmt, nArg, acType))>=0
             && nArg!=fmtArg+k ){
      printf("%s:%d: too %s arguments to %.*s() "
             "- got %d and expected %d\n",
             zFilename, lnFCall, (nArg<fmtArg+k ? "few" : "many"),
             szFName, zFCall, nArg, fmtArg+k);
      nErr++;
    }else if( (fmtFlags & FMT_SAFE)==0 ){
      for(i=0; i<nArg && i<k; i++){
        if( (acType[i]=='s' || acType[i]=='z' || acType[i]=='b') ){
          const char *zExpr = azArg[fmtArg+i];
          if( never_safe(zExpr) ){
            printf("%s:%d: Argument %d to %.*s() is not safe for"
                   " a query parameter\n",
               zFilename, lnFCall, i+fmtArg, szFName, zFCall);
             nErr++;
   
          }else if( (fmtFlags & FMT_SQL)!=0 && !is_sql_safe(zExpr) ){
            printf("%s:%d: Argument %d to %.*s() not safe for SQL\n",
               zFilename, lnFCall, i+fmtArg, szFName, zFCall);
             nErr++;
          }
        }
      }
    }
  }
  if( nErr ){
    for(i=0; i<nArg; i++){
      printf("   arg[%d]: %s\n", i, azArg[i]);
    }
  }else if( eVerbose>1 ){
    printf("%s:%d: %.*s() ok for %d arguments\n",
      zFilename, lnFCall, szFName, zFCall, nArg);
  }
  free((char*)azArg);
  free(zCopy);
  return nErr;
}


/*

  }
  return nErr;
}

/*
** Check for format-string design rule violations on all files listed
** on the command-line.
**
** The eVerbose global variable is incremented with each "-v" argument.
*/
int main(int argc, char **argv){
  int i;
  int nErr = 0;
  for(i=1; i<argc; i++){
    char *zFile;
    if( strcmp(argv[i],"-v")==0 ){
      eVerbose++;
      continue;
    }
    if( eVerbose>0 ) printf("Processing %s...\n", argv[i]);
    zFile = read_file(argv[i]);
    nErr += scan_file(argv[i], zFile);
    free(zFile);
  }
  return nErr;
}

#define CONFIGSET_TKT       0x000004     /* Ticket configuration */
#define CONFIGSET_PROJ      0x000008     /* Project name */
#define CONFIGSET_SHUN      0x000010     /* Shun settings */
#define CONFIGSET_USER      0x000020     /* The USER table */
#define CONFIGSET_ADDR      0x000040     /* The CONCEALED table */
#define CONFIGSET_XFER      0x000080     /* Transfer configuration */
#define CONFIGSET_ALIAS     0x000100     /* URL Aliases */
#define CONFIGSET_SCRIBER   0x000200     /* Email subscribers */
#define CONFIGSET_ALL       0x0003ff     /* Everything */

#define CONFIGSET_OVERWRITE 0x100000     /* Causes overwrite instead of merge */

/*
** This mask is used for the common TH1 configuration settings (i.e. those
** that are not specific to one particular subsystem, such as the transfer
** subsystem).
*/
#define CONFIGSET_TH1       (CONFIGSET_SKIN|CONFIGSET_TKT|CONFIGSET_XFER)

#endif /* INTERFACE */

/*
** Names of the configuration sets
*/
static struct {
  const char *zName;   /* Name of the configuration set */
  int groupMask;       /* Mask for that configuration set */
  const char *zHelp;   /* What it does */
} aGroupName[] = {
  { "/email",       CONFIGSET_ADDR,  "Concealed email addresses in tickets" },
  { "/project",     CONFIGSET_PROJ,  "Project name and description"         },
  { "/skin",        CONFIGSET_SKIN | CONFIGSET_CSS,
                                     "Web interface appearance settings"    },
  { "/css",         CONFIGSET_CSS,   "Style sheet"                          },
  { "/shun",        CONFIGSET_SHUN,  "List of shunned artifacts"            },
  { "/ticket",      CONFIGSET_TKT,   "Ticket setup",                        },
  { "/user",        CONFIGSET_USER,  "Users and privilege settings"         },
  { "/xfer",        CONFIGSET_XFER,  "Transfer setup",                      },
  { "/alias",       CONFIGSET_ALIAS, "URL Aliases",                         },
  { "/subscriber",  CONFIGSET_SCRIBER,"Email notification subscriber list"  },
  { "/all",         CONFIGSET_ALL,   "All of the above"                     },
};


/*
** The following is a list of settings that we are willing to
** transfer.
**

  { "@concealed",             CONFIGSET_ADDR },

  { "@shun",                  CONFIGSET_SHUN },

  { "@alias",                 CONFIGSET_ALIAS },

  { "@subscriber",            CONFIGSET_SCRIBER },

  { "xfer-common-script",     CONFIGSET_XFER },
  { "xfer-push-script",       CONFIGSET_XFER },
  { "xfer-commit-script",     CONFIGSET_XFER },
  { "xfer-ticket-script",     CONFIGSET_XFER },

};
static int iConfig = 0;

  return blob_sql_text(&x);
}

/*
** Return the mask for the named configuration parameter if it can be
** safely exported.  Return 0 if the parameter is not safe to export.
**
** "Safe" in the previous paragraph means the permission is granted to
** export the property.  In other words, the requesting side has presented
** login credentials and has sufficient capabilities to access the requested
** information.
*/
int configure_is_exportable(const char *zName){
  int i;
  int n = strlen(zName);
  if( n>2 && zName[0]=='\'' && zName[n-1]=='\'' ){
    zName++;
    n -= 2;
  }
  for(i=0; i<count(aConfig); i++){
    if( strncmp(zName, aConfig[i].zName, n)==0 && aConfig[i].zName[n]==0 ){
      int m = aConfig[i].groupMask;
      if( !g.perm.Admin ){
        m &= ~(CONFIGSET_USER|CONFIGSET_SCRIBER);
      }
      if( !g.perm.RdAddr ){
        m &= ~CONFIGSET_ADDR;
      }
      return m;
    }
  }

** sync session.
**
** Mask consists of one or more CONFIGSET_* values ORed together, to
** designate what types of configuration we are allowed to receive.
**
** NEW FORMAT:
**
** zName is one of:
**
**     "/config", "/user",  "/shun", "/reportfmt", "/concealed",
**     "/subscriber",
**
** zName indicates the table that holds the configuration information being
** transferred.  pContent is a string that consist of alternating Fossil
** and SQL tokens.  The First token is a timestamp in seconds since 1970.
** The second token is a primary key for the table identified by zName.  If
** The entry with the corresponding primary key exists and has a more recent
** mtime, then nothing happens.  If the entry does not exist or if it has
** an older mtime, then the content described by subsequent token pairs is
** inserted.  The first element of each token pair is a column name and
** the second is its value.
**
** In overview, we have:
**
**    NAME        CONTENT
**    -------     -----------------------------------------------------------
**    /config     $MTIME $NAME value $VALUE
**    /user       $MTIME $LOGIN pw $VALUE cap $VALUE info $VALUE photo $VALUE
**    /shun       $MTIME $UUID scom $VALUE
**    /reportfmt  $MTIME $TITLE owner $VALUE cols $VALUE sqlcode $VALUE
**    /concealed  $MTIME $HASH content $VALUE

**    /subscriber $SMTIME $SEMAIL suname $V ...
















*/
void configure_receive(const char *zName, Blob *pContent, int groupMask){
  int checkMask;   /* Masks for which we must first check existance of tables */

  checkMask = CONFIGSET_SCRIBER;
  if( zName[0]=='/' ){
    /* The new format */
    char *azToken[24];
    int nToken = 0;
    int ii, jj;
    int thisMask;
    Blob name, value, sql;
    static const struct receiveType {
      const char *zName;         /* Configuration key for this table */
      const char *zPrimKey;      /* Primary key column */
      int nField;                /* Number of data fields */
      const char *azField[6];    /* Names of the data fields */
    } aType[] = {
      { "/config",    "name",  1, { "value", 0,0,0,0,0 }           },
      { "@user",      "login", 4, { "pw","cap","info","photo",0,0} },
      { "@shun",      "uuid",  1, { "scom", 0,0,0,0,0}             },
      { "@reportfmt", "title", 3, { "owner","cols","sqlcode",0,0,0}},
      { "@concealed", "hash",  1, { "content", 0,0,0,0,0 }         },
      { "@subscriber","semail",6,
         { "suname","sdigest","sdonotcall","ssub","sctime","smip"}         },
    };

    /* Locate the receiveType in aType[ii] */
    for(ii=0; ii<count(aType); ii++){
      if( fossil_strcmp(&aType[ii].zName[1],&zName[1])==0 ) break;
    }
    if( ii>=count(aType) ) return;

    while( blob_token(pContent, &name) && blob_sqltoken(pContent, &value) ){
      char *z = blob_terminate(&name);
      if( !safeSql(z) ) return;
      if( nToken>0 ){
        for(jj=0; jj<aType[ii].nField; jj++){
          if( fossil_strcmp(aType[ii].azField[jj], z)==0 ) break;
        }
        if( jj>=aType[ii].nField ) continue;
      }else{
        if( !safeInt(z) ) return;
      }
      azToken[nToken++] = z;
      azToken[nToken++] = z = blob_terminate(&value);
      if( !safeSql(z) ) return;
      if( nToken>=count(azToken)-1 ) break;
    }
    if( nToken<2 ) return;
    if( aType[ii].zName[0]=='/' ){
      thisMask = configure_is_exportable(azToken[1]);
    }else{
      thisMask = configure_is_exportable(aType[ii].zName);
    }
    if( (thisMask & groupMask)==0 ) return;
    if( (thisMask & checkMask)!=0 ){
      if( (thisMask & CONFIGSET_SCRIBER)!=0 ){
        email_schema(1);
      }
      checkMask &= ~thisMask;
    }

    blob_zero(&sql);
    if( groupMask & CONFIGSET_OVERWRITE ){
      if( (thisMask & configHasBeenReset)==0 && aType[ii].zName[0]!='/' ){
        db_multi_exec("DELETE FROM \"%w\"", &aType[ii].zName[1]);
        configHasBeenReset |= thisMask;
      }
      blob_append_sql(&sql, "REPLACE INTO ");
    }else{
      blob_append_sql(&sql, "INSERT OR IGNORE INTO ");
    }
    blob_append_sql(&sql, "\"%w\"(\"%w\",mtime",
         &zName[1], aType[ii].zPrimKey);
    if( fossil_stricmp(zName,"/subscriber") ) email_schema(0);
    for(jj=2; jj<nToken; jj+=2){
       blob_append_sql(&sql, ",\"%w\"", azToken[jj]);
    }
    blob_append_sql(&sql,") VALUES(%s,%s",
       azToken[1] /*safe-for-%s*/, azToken[0]/*safe-for-%s*/);
    for(jj=2; jj<nToken; jj+=2){
       blob_append_sql(&sql, ",%s", azToken[jj+1] /*safe-for-%s*/);
    }
    db_multi_exec("%s)", blob_sql_text(&sql));
    if( db_changes()==0 ){
      blob_reset(&sql);
      blob_append_sql(&sql, "UPDATE \"%w\" SET mtime=%s",

      );
      blob_appendf(pOut, "config /config %d\n%s\n",
                   blob_size(&rec), blob_str(&rec));
      nCard++;
      blob_reset(&rec);
    }
    db_finalize(&q);
  }
  if( (groupMask & CONFIGSET_SCRIBER)!=0
   && db_table_exists("repository","subscriber")
  ){
    db_prepare(&q, "SELECT mtime, quote(semail),"
                   " quote(suname), quote(sdigest),"
                   " quote(sdonotcall), quote(ssub),"
                   " quote(sctime), quote(smip)"
                   " FROM subscriber WHERE sverified"
                   " AND mtime>=%lld", iStart);
    while( db_step(&q)==SQLITE_ROW ){
      blob_appendf(&rec,
        "%lld %s suname %s sdigest %s sdonotcall %s ssub %s"
        " sctime %s smip %s",
        db_column_int64(&q, 0), /* mtime */
        db_column_text(&q, 1),  /* semail (PK) */
        db_column_text(&q, 2),  /* suname */
        db_column_text(&q, 3),  /* sdigest */
        db_column_text(&q, 4),  /* sdonotcall */
        db_column_text(&q, 5),  /* ssub */
        db_column_text(&q, 6),  /* sctime */
        db_column_text(&q, 7)   /* smip */
      );
      blob_appendf(pOut, "config /subscriber %d\n%s\n",
                   blob_size(&rec), blob_str(&rec));
      nCard++;
      blob_reset(&rec);
    }
    db_finalize(&q);
  }
  db_prepare(&q, "SELECT mtime, quote(name), quote(value) FROM config"
                 " WHERE name=:name AND mtime>=%lld", iStart);
  for(ii=0; ii<count(aConfig); ii++){
    if( (aConfig[ii].groupMask & groupMask)!=0 && aConfig[ii].zName[0]!='@' ){
      db_bind_text(&q, ":name", aConfig[ii].zName);
      while( db_step(&q)==SQLITE_ROW ){

    if( strncmp(z, &aGroupName[i].zName[1], n)==0 ){
      return aGroupName[i].groupMask;
    }
  }
  if( notFoundIsFatal ){
    fossil_print("Available configuration areas:\n");
    for(i=0; i<count(aGroupName); i++){
      fossil_print("  %-13s %s\n",
            &aGroupName[i].zName[1], aGroupName[i].zHelp);
    }
    fossil_fatal("no such configuration area: \"%s\"", z);
  }
  return 0;
}

/*

**
** Where METHOD is one of: export import merge pull push reset.  All methods
** accept the -R or --repository option to specify a repository.
**
**    %fossil configuration export AREA FILENAME
**
**         Write to FILENAME exported configuration information for AREA.
**         AREA can be one of:
**
**             all email project shun skin ticket user alias subscriber
**
**    %fossil configuration import FILENAME
**
**         Read a configuration from FILENAME, overwriting the current
**         configuration.
**
**    %fossil configuration merge FILENAME

      }else if( fossil_strcmp(zName,"@user")==0 ){
        db_multi_exec("DELETE FROM user");
        db_create_default_users(0, 0);
      }else if( fossil_strcmp(zName,"@concealed")==0 ){
        db_multi_exec("DELETE FROM concealed");
      }else if( fossil_strcmp(zName,"@shun")==0 ){
        db_multi_exec("DELETE FROM shun");
      }else if( fossil_strcmp(zName,"@subscriber")==0 ){
        if( db_table_exists("repository","subscriber") ){
          db_multi_exec("DELETE FROM subscriber");
        }
      }else if( fossil_strcmp(zName,"@forum")==0 ){
        if( db_table_exists("repository","forumpost") ){
          db_multi_exec("DELETE FROM forumpost");
          db_multi_exec("DELETE FROM forumthread");
        }
      }else if( fossil_strcmp(zName,"@reportfmt")==0 ){
        db_multi_exec("DELETE FROM reportfmt");
        assert( strchr(zRepositorySchemaDefaultReports,'%')==0 );
        db_multi_exec(zRepositorySchemaDefaultReports /*works-like:""*/);
      }
    }
    db_end_transaction(0);

#endif /* INTERFACE */
const struct Stmt empty_Stmt = empty_Stmt_m;

/*
** Call this routine when a database error occurs.
*/
static void db_err(const char *zFormat, ...){

  va_list ap;
  char *z;


  va_start(ap, zFormat);
  z = vmprintf(zFormat, ap);
  va_end(ap);
#ifdef FOSSIL_ENABLE_JSON
  if( g.json.isJsonMode ){
    json_err( 0, z, 1 );



  }
  else
#endif /* FOSSIL_ENABLE_JSON */
  if( g.xferPanic && g.cgiOutput==1 ){
    cgi_reset_content();
    @ error Database\serror:\s%F(z)
    cgi_reply();
  }










  fossil_panic("Database error: %s", z);
}

/*
** All static variable that a used by only this file are gathered into
** the following structure.
*/
static struct DbLocalData {

    int (*xHook)(void);         /* Functions to call at db_end_transaction() */
    int sequence;               /* Call functions in sequence order */
  } aHook[5];
  char *azDeleteOnFail[3];  /* Files to delete on a failure */
  char *azBeforeCommit[5];  /* Commands to run prior to COMMIT */
  int nBeforeCommit;        /* Number of entries in azBeforeCommit */
  int nPriorChanges;        /* sqlite3_total_changes() at transaction start */
  const char *zStartFile;   /* File in which transaction was started */
  int iStartLine;           /* Line of zStartFile where transaction started */
} db = {0, 0, 0, 0, 0, 0, };

/*
** Arrange for the given file to be deleted on a failure.
*/
void db_delete_on_failure(const char *zFilename){
  assert( db.nDeleteOnFail<count(db.azDeleteOnFail) );
  db.azDeleteOnFail[db.nDeleteOnFail++] = fossil_strdup(zFilename);
}

/*
** Return the transaction nesting depth.  0 means we are currently
** not in a transaction.
*/
int db_transaction_nesting_depth(void){
  return db.nBegin;
}

/*
** Return a pointer to a string that is the code point where the
** current transaction was started.
*/
char *db_transaction_start_point(void){
  return mprintf("%s:%d", db.zStartFile, db.iStartLine);
}

/*
** This routine is called by the SQLite commit-hook mechanism
** just prior to each commit.  All this routine does is verify
** that nBegin really is zero.  That insures that transactions
** cannot commit by any means other than by calling db_end_transaction()
** below.
**
** This is just a safety and sanity check.
*/
static int db_verify_at_commit(void *notUsed){
  if( db.nBegin ){
    fossil_panic("illegal commit attempt");
    return 1;
  }
  return 0;
}

/*
** Silently add the filename and line number as parameter to each
** db_begin_transaction call.
*/
#if INTERFACE
#define db_begin_transaction()    db_begin_transaction_real(__FILE__,__LINE__)
#define db_begin_write()          db_begin_write_real(__FILE__,__LINE__)
#define db_commit_transaction()   db_end_transaction(0)
#define db_rollback_transaction() db_end_transaction(1)
#endif

/*
** Begin a nested transaction
*/
void db_begin_transaction_real(const char *zStartFile, int iStartLine){
  if( db.nBegin==0 ){
    db_multi_exec("BEGIN");
    sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
    db.nPriorChanges = sqlite3_total_changes(g.db);
    db.doRollback = 0;
    db.zStartFile = zStartFile;
    db.iStartLine = iStartLine;
  }
  db.nBegin++;
}
/*
** Begin a new transaction for writing.
*/
void db_begin_write_real(const char *zStartFile, int iStartLine){
  if( db.nBegin==0 ){
    db_multi_exec("BEGIN IMMEDIATE");
    sqlite3_commit_hook(g.db, db_verify_at_commit, 0);
    db.nPriorChanges = sqlite3_total_changes(g.db);
    db.doRollback = 0;
    db.zStartFile = zStartFile;
    db.iStartLine = iStartLine;
  }else{
    fossil_warning("read txn at %s:%d might cause SQLITE_BUSY "
       "for the write txn at %s:%d",
       db.zStartFile, db.iStartLine, zStartFile, iStartLine);
  }
  db.nBegin++;
}

/* End a transaction previously started using db_begin_transaction()
** or db_begin_write().
*/
void db_end_transaction(int rollbackFlag){
  if( g.db==0 ) return;
  if( db.nBegin<=0 ){
    fossil_warning("Extra call to db_end_transaction");
    return;
  }
  if( rollbackFlag ){
    db.doRollback = 1;
    if( g.fSqlTrace ) fossil_trace("-- ROLLBACK by request\n");
  }
  db.nBegin--;
  if( db.nBegin==0 ){
    int i;
    if( db.doRollback==0 && db.nPriorChanges<sqlite3_total_changes(g.db) ){
      i = 0;
      while( db.nBeforeCommit ){
        db.nBeforeCommit--;
        sqlite3_exec(g.db, db.azBeforeCommit[i], 0, 0, 0);
        sqlite3_free(db.azBeforeCommit[i]);
        i++;
      }
      leaf_do_pending_checks();
    }
    for(i=0; db.doRollback==0 && i<db.nCommitHook; i++){
      int rc = db.aHook[i].xHook();
      if( rc ){
        db.doRollback = 1;
        if( g.fSqlTrace ) fossil_trace("-- ROLLBACK due to aHook[%d]\n", i);
      }
    }
    while( db.pAllStmt ){
      db_finalize(db.pAllStmt);
    }
    db_multi_exec("%s", db.doRollback ? "ROLLBACK" : "COMMIT");
    db.doRollback = 0;
  }

  va_end(ap);
  zSql = blob_str(&pStmt->sql);
  db.nPrepare++;
  if( flags & DB_PREPARE_PERSISTENT ){
    prepFlags = SQLITE_PREPARE_PERSISTENT;
  }
  rc = sqlite3_prepare_v3(g.db, zSql, -1, prepFlags, &pStmt->pStmt, 0);
  if( rc!=0 && (flags & DB_PREPARE_IGNORE_ERROR)==0 ){
    db_err("%s\n%s", sqlite3_errmsg(g.db), zSql);
  }
  pStmt->pNext = db.pAllStmt;
  pStmt->pPrev = 0;
  if( db.pAllStmt ) db.pAllStmt->pPrev = pStmt;
  db.pAllStmt = pStmt;
  pStmt->nStep = 0;
  pStmt->rc = rc;
  return rc;
}
int db_prepare(Stmt *pStmt, const char *zFormat, ...){
  int rc;
  va_list ap;
  va_start(ap, zFormat);
  rc = db_vprepare(pStmt, 0, zFormat, ap);
  va_end(ap);
  return rc;
}
int db_prepare_ignore_error(Stmt *pStmt, const char *zFormat, ...){
  int rc;
  va_list ap;
  va_start(ap, zFormat);
  rc = db_vprepare(pStmt, DB_PREPARE_IGNORE_ERROR, zFormat, ap);
  va_end(ap);
  return rc;
}

/* This variant of db_prepare() checks to see if the statement has
** already been prepared, and if it has it becomes a no-op.
*/
int db_static_prepare(Stmt *pStmt, const char *zFormat, ...){
  int rc = SQLITE_OK;
  if( blob_size(&pStmt->sql)==0 ){
    va_list ap;
    va_start(ap, zFormat);
    rc = db_vprepare(pStmt, DB_PREPARE_PERSISTENT, zFormat, ap);




    va_end(ap);
  }
  return rc;
}

/* Prepare a statement using text placed inside a Blob
** using blob_append_sql().
*/
int db_prepare_blob(Stmt *pStmt, Blob *pSql){
  int rc;
  char *zSql;
  pStmt->sql = *pSql;
  blob_init(pSql, 0, 0);
  zSql = blob_sql_text(&pStmt->sql);
  db.nPrepare++;
  rc = sqlite3_prepare_v3(g.db, zSql, -1, 0, &pStmt->pStmt, 0);
  if( rc!=0 ){
    db_err("%s\n%s", sqlite3_errmsg(g.db), zSql);
  }
  pStmt->pNext = pStmt->pPrev = 0;
  pStmt->nStep = 0;
  pStmt->rc = rc;
  return rc;
}


/*
** Return the index of a bind parameter
*/
static int paramIdx(Stmt *pStmt, const char *zParamName){
  int i = sqlite3_bind_parameter_index(pStmt->pStmt, zParamName);
  if( i==0 ){

  db_stats(pStmt);
  rc = sqlite3_reset(pStmt->pStmt);
  db_check_result(rc);
  return rc;
}
int db_finalize(Stmt *pStmt){
  int rc;





  if( pStmt->pNext ){
    pStmt->pNext->pPrev = pStmt->pPrev;
  }
  if( pStmt->pPrev ){
    pStmt->pPrev->pNext = pStmt->pNext;
  }else if( db.pAllStmt==pStmt ){
    db.pAllStmt = pStmt->pNext;
  }
  pStmt->pNext = 0;
  pStmt->pPrev = 0;
  db_stats(pStmt);
  blob_reset(&pStmt->sql);
  rc = sqlite3_finalize(pStmt->pStmt);
  db_check_result(rc);
  pStmt->pStmt = 0;
  return rc;
}

/*
** Return the rowid of the most recent insert
*/
int db_last_insert_rowid(void){
  i64 x = sqlite3_last_insert_rowid(g.db);
  if( x<0 || x>(i64)2147483647 ){
    fossil_panic("rowid out of range (0..2147483647)");
  }
  return (int)x;
}

/*
** Return the number of rows that were changed by the most recent
** INSERT, UPDATE, or DELETE.  Auxiliary changes caused by triggers

}
char *db_column_malloc(Stmt *pStmt, int N){
  return mprintf("%s", db_column_text(pStmt, N));
}
void db_column_blob(Stmt *pStmt, int N, Blob *pBlob){
  blob_append(pBlob, sqlite3_column_blob(pStmt->pStmt, N),
              sqlite3_column_bytes(pStmt->pStmt, N));
}
Blob db_column_text_as_blob(Stmt *pStmt, int N){
  Blob x;
  blob_init(&x, (char*)sqlite3_column_text(pStmt->pStmt,N),
            sqlite3_column_bytes(pStmt->pStmt,N));
  return x;
}

/*
** Initialize a blob to an ephemeral copy of the content of a
** column in the current row.  The data in the blob will become
** invalid when the statement is stepped or reset.
*/

  if( g.fTimeFormat==1 ){
    sqlite3_result_text(context, "0 seconds", -1, SQLITE_STATIC);
  }else{
    sqlite3_result_text(context, "utc", -1, SQLITE_STATIC);
  }
}

/*
** If the input is a hexadecimal string, convert that string into a BLOB.
** If the input is not a hexadecimal string, return NULL.
*/
void db_hextoblob(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *zIn = sqlite3_value_text(argv[0]);
  int nIn = sqlite3_value_bytes(argv[0]);
  unsigned char *zOut;
  if( zIn==0 ) return;
  if( nIn&1 ) return;
  if( !validate16((const char*)zIn, nIn) ) return;
  zOut = sqlite3_malloc64( nIn/2 + 1 );
  if( zOut==0 ){
    sqlite3_result_error_nomem(context);
    return;
  }
  decode16(zIn, zOut, nIn);
  sqlite3_result_blob(context, zOut, nIn/2, sqlite3_free);
}

/*
** Register the SQL functions that are useful both to the internal
** representation and to the "fossil sql" command.
*/
void db_add_aux_functions(sqlite3 *db){
  sqlite3_create_function(db, "checkin_mtime", 2, SQLITE_UTF8, 0,
                          db_checkin_mtime_function, 0, 0);
  sqlite3_create_function(db, "symbolic_name_to_rid", 1, SQLITE_UTF8, 0,
                          db_sym2rid_function, 0, 0);
  sqlite3_create_function(db, "symbolic_name_to_rid", 2, SQLITE_UTF8, 0,
                          db_sym2rid_function, 0, 0);
  sqlite3_create_function(db, "now", 0, SQLITE_UTF8, 0,
                          db_now_function, 0, 0);
  sqlite3_create_function(db, "toLocal", 0, SQLITE_UTF8, 0,
                          db_tolocal_function, 0, 0);
  sqlite3_create_function(db, "fromLocal", 0, SQLITE_UTF8, 0,
                          db_fromlocal_function, 0, 0);
  sqlite3_create_function(db, "hextoblob", 1, SQLITE_UTF8, 0,
                          db_hextoblob, 0, 0);
  sqlite3_create_function(db, "capunion", 1, SQLITE_UTF8, 0,
                          0, capability_union_step, capability_union_finalize);
  sqlite3_create_function(db, "fullcap", 1, SQLITE_UTF8, 0,
                          capability_fullcap, 0, 0);
  sqlite3_create_function(db, "find_emailaddr", 1, SQLITE_UTF8, 0,
                          email_find_emailaddr_func, 0, 0);
}

#if USE_SEE
/*
** This is a pointer to the saved database encryption key string.
*/
static char *zSavedKey = 0;

  size_t blobSize = 0;

  blobSize = blob_size(pKey);
  if( blobSize==0 ) return;
  fossil_get_page_size(&pageSize);
  assert( pageSize>0 );
  if( blobSize>pageSize ){
    fossil_panic("key blob too large: %u versus %u", blobSize, pageSize);
  }
  p = fossil_secure_alloc_page(&n);
  assert( p!=NULL );
  assert( n==pageSize );
  assert( n>=blobSize );
  memcpy(p, blob_str(pKey), blobSize);
  zSavedKey = p;

){
  if( zSavedKey!=NULL ){
    size_t blobSize = blob_size(pKey);
    if( blobSize==0 ){
      db_unsave_encryption_key();
    }else{
      if( blobSize>savedKeySize ){
        fossil_panic("key blob too large: %u versus %u",
                     blobSize, savedKeySize);
      }
      fossil_secure_zero(zSavedKey, savedKeySize);
      memcpy(zSavedKey, blob_str(pKey), blobSize);
    }
  }else{
    db_save_encryption_key(pKey);

  size_t n = 0;
  size_t pageSize = 0;
  HANDLE hProcess = NULL;

  fossil_get_page_size(&pageSize);
  assert( pageSize>0 );
  if( nSize>pageSize ){
    fossil_panic("key too large: %u versus %u", nSize, pageSize);
  }
  p = fossil_secure_alloc_page(&n);
  assert( p!=NULL );
  assert( n==pageSize );
  assert( n>=nSize );
  hProcess = OpenProcess(PROCESS_VM_READ, FALSE, processId);
  if( hProcess!=NULL ){
    SIZE_T nRead = 0;
    if( ReadProcessMemory(hProcess, pAddress, p, nSize, &nRead) ){
      CloseHandle(hProcess);
      if( nRead==nSize ){
        db_unsave_encryption_key();
        zSavedKey = p;
        savedKeySize = n;
      }else{
        fossil_panic("bad size read, %u out of %u bytes at %p from pid %lu",
                     nRead, nSize, pAddress, processId);
      }
    }else{
      CloseHandle(hProcess);
      fossil_panic("failed read, %u bytes at %p from pid %lu: %lu", nSize,
                   pAddress, processId, GetLastError());
    }
  }else{
    fossil_panic("failed to open pid %lu: %lu", processId, GetLastError());
  }
}
#endif /* defined(_WIN32) */
#endif /* USE_SEE */

/*
** If the database file zDbFile has a name that suggests that it is

** connection.  An error results in process abort.
*/
LOCAL sqlite3 *db_open(const char *zDbName){
  int rc;
  sqlite3 *db;

  if( g.fSqlTrace ) fossil_trace("-- sqlite3_open: [%s]\n", zDbName);
  if( strcmp(zDbName, g.nameOfExe)==0 ){
    extern int sqlite3_appendvfs_init(
      sqlite3 *, char **, const sqlite3_api_routines *
    );
    sqlite3_appendvfs_init(0,0,0);
    g.zVfsName = "apndvfs";
  }
  rc = sqlite3_open_v2(
       zDbName, &db,
       SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
       g.zVfsName
  );
  if( rc!=SQLITE_OK ){
    db_err("[%s]: %s", zDbName, sqlite3_errmsg(db));

** the database connection.
**
** After calling this routine, db_database_slot(zLabel) should
** return 0.
*/
void db_set_main_schemaname(sqlite3 *db, const char *zLabel){
  if( sqlite3_db_config(db, SQLITE_DBCONFIG_MAINDBNAME, zLabel) ){
    fossil_panic("Fossil requires a version of SQLite that supports the "
                 "SQLITE_DBCONFIG_MAINDBNAME interface.");
  }
}

/*
** Return the slot number for database zLabel.  The first database
** opened is slot 0.  The "temp" database is slot 1.  Attached databases

    g.zConfigDbName = 0;
  }else if( g.dbConfig ){
    sqlite3_wal_checkpoint(g.dbConfig, 0);
    sqlite3_close(g.dbConfig);
    g.dbConfig = 0;
    g.zConfigDbName = 0;
  }else if( g.db && 0==iSlot ){
    int rc;
    sqlite3_wal_checkpoint(g.db, 0);
    rc = sqlite3_close(g.db);
    if( g.fSqlTrace ) fossil_trace("-- db_close_config(%d)\n", rc);
    g.db = 0;
    g.zConfigDbName = 0;
  }
}

/*
** Open the user database in "~/.fossil".  Create the database anew if

        char *zPath = fossil_getenv("HOMEPATH");
        if( zDrive && zPath ) zHome = mprintf("%s%s", zDrive, zPath);
      }
    }
  }
  if( zHome==0 ){
    if( isOptional ) return 0;
    fossil_panic("cannot locate home directory - please set the "
                 "FOSSIL_HOME, LOCALAPPDATA, APPDATA, or HOMEPATH "
                 "environment variables");
  }
#else
  if( zHome==0 ){
    zHome = fossil_getenv("HOME");
  }
  if( zHome==0 ){
    if( isOptional ) return 0;
    fossil_panic("cannot locate home directory - please set the "
                 "FOSSIL_HOME or HOME environment variables");
  }
#endif
  if( file_isdir(zHome, ExtFILE)!=1 ){
    if( isOptional ) return 0;
    fossil_panic("invalid home directory: %s", zHome);
  }
#if defined(_WIN32) || defined(__CYGWIN__)
  /* . filenames give some window systems problems and many apps problems */
  zDbName = mprintf("%//_fossil", zHome);
#else
  zDbName = mprintf("%s/.fossil", zHome);
#endif
  if( file_size(zDbName, ExtFILE)<1024*3 ){
    if( file_access(zHome, W_OK) ){
      if( isOptional ) return 0;
      fossil_panic("home directory %s must be writeable", zHome);
    }
    db_init_database(zDbName, zConfigSchema, (char*)0);
  }
  if( file_access(zDbName, W_OK) ){
    if( isOptional ) return 0;
    fossil_panic("configuration file %s must be writeable", zDbName);
  }
  if( useAttach ){
    db_open_or_attach(zDbName, "configdb");
    g.dbConfig = 0;
  }else{
    g.dbConfig = db_open(zDbName);
    db_set_main_schemaname(g.dbConfig, "configdb");

    }
  }
  if( file_access(zDbName, R_OK) || file_size(zDbName, ExtFILE)<1024 ){
    if( file_access(zDbName, F_OK) ){
#ifdef FOSSIL_ENABLE_JSON
      g.json.resultCode = FSL_JSON_E_DB_NOT_FOUND;
#endif
      fossil_fatal("repository does not exist or"
                   " is in an unreadable directory: %s", zDbName);
    }else if( file_access(zDbName, R_OK) ){
#ifdef FOSSIL_ENABLE_JSON
      g.json.resultCode = FSL_JSON_E_DENIED;
#endif
      fossil_fatal("read permission denied for repository %s", zDbName);
    }else{
#ifdef FOSSIL_ENABLE_JSON
      g.json.resultCode = FSL_JSON_E_DB_NOT_VALID;
#endif
      fossil_fatal("not a valid repository: %s", zDbName);
    }
  }
  g.zRepositoryName = mprintf("%s", zDbName);
  db_open_or_attach(g.zRepositoryName, "repository");
  g.repositoryOpen = 1;
  sqlite3_file_control(g.db, "repository", SQLITE_FCNTL_DATA_VERSION,
                       &g.iRepoDataVers);
  /* Cache "allow-symlinks" option, because we'll need it on every stat call */
  g.allowSymlinks = db_get_boolean("allow-symlinks",
                                   db_allow_symlinks_by_default());
  g.zAuxSchema = db_get("aux-schema","");
  g.eHashPolicy = db_get_int("hash-policy",-1);
  if( g.eHashPolicy<0 ){
    g.eHashPolicy = hname_default_policy();
    db_set_int("hash-policy", g.eHashPolicy, 0);
  }

  /* Make a change to the CHECK constraint on the BLOB table for
  ** version 2.0 and later.
  */
  rebuild_schema_update_2_0();   /* Do the Fossil-2.0 schema updates */
}

/*
** Return true if there have been any changes to the repository
** database since it was opened.
**
** Changes to "config" and "localdb" and "temp" do not count.
** This routine only returns true if there have been changes
** to "repository".
*/
int db_repository_has_changed(void){
  unsigned int v;
  if( !g.repositoryOpen ) return 0;
  sqlite3_file_control(g.db, "repository", SQLITE_FCNTL_DATA_VERSION, &v);
  return g.iRepoDataVers != v;               
}

/*
** Flags for the db_find_and_open_repository() function.
*/
#if INTERFACE
#define OPEN_OK_NOT_FOUND    0x001      /* Do not error out if not found */
#define OPEN_ANY_SCHEMA      0x002      /* Do not error if schema is wrong */

    sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &cur, &hiwtr, 0);
    fprintf(stderr, "-- PCACHE_OVFLOW          %10d %10d\n", cur, hiwtr);
    fprintf(stderr, "-- prepared statements    %10d\n", db.nPrepare);
  }
  while( db.pAllStmt ){
    db_finalize(db.pAllStmt);
  }
  if( db.nBegin && reportErrors ){
    fossil_warning("Transaction started at %s:%d never commits",
                   db.zStartFile, db.iStartLine);
    db_end_transaction(1);
  }
  pStmt = 0;
  g.dbIgnoreErrors++; /* Stop "database locked" warnings from PRAGMA optimize */
  sqlite3_exec(g.db, "PRAGMA optimize", 0, 0, 0);
  g.dbIgnoreErrors--;
  db_close_config();

  /* If the localdb has a lot of unused free space,
  ** then VACUUM it as we shut down.
  */
  if( db_database_slot("localdb")>=0 ){
    int nFree = db_int(0, "PRAGMA localdb.freelist_count");
    int nTotal = db_int(0, "PRAGMA localdb.page_count");
    if( nFree>nTotal/4 ){
      db_multi_exec("VACUUM localdb;");
    }
  }

  if( g.db ){
    int rc;
    sqlite3_wal_checkpoint(g.db, 0);
    rc = sqlite3_close(g.db);
    if( g.fSqlTrace ) fossil_trace("-- sqlite3_close(%d)\n", rc);
    if( rc==SQLITE_BUSY && reportErrors ){
      while( (pStmt = sqlite3_next_stmt(g.db, pStmt))!=0 ){
        fossil_warning("unfinalized SQL statement: [%s]", sqlite3_sql(pStmt));
      }
    }
    g.db = 0;
  }
  g.repositoryOpen = 0;
  g.localOpen = 0;
  assert( g.dbConfig==0 );
  assert( g.zConfigDbName==0 );
  backoffice_run_if_needed();
}

/*
** Close the database as quickly as possible without unnecessary processing.
*/
void db_panic_close(void){
  if( g.db ){
    int rc;
    sqlite3_wal_checkpoint(g.db, 0);
    rc = sqlite3_close(g.db);
    if( g.fSqlTrace ) fossil_trace("-- sqlite3_close(%d)\n", rc);
  }
  g.db = 0;
  g.repositoryOpen = 0;
  g.localOpen = 0;
}

/*
** Create a new empty repository database with the given name.
**
** Only the schema is initialized.  The required VAR tables entries
** are not set by this routine and must be set separately in order
** to make the new file a valid database.

  info_cmd();
}

/*
** Print the current value of a setting identified by the pSetting
** pointer.
*/
void print_setting(const Setting *pSetting){
  Stmt q;
  if( g.repositoryOpen ){
    db_prepare(&q,
       "SELECT '(local)', value FROM config WHERE name=%Q"
       " UNION ALL "
       "SELECT '(global)', value FROM global_config WHERE name=%Q",
       pSetting->name, pSetting->name

*/
/*
** SETTING: autosync-tries  width=16 default=1
** If autosync is enabled setting this to a value greater
** than zero will cause autosync to try no more than this
** number of attempts if there is a sync failure.
*/
/*
** SETTING: backoffice-nodelay boolean default=off
** If backoffice-nodelay is true, then the backoffice processing
** will never invoke sleep().  If it has nothing useful to do,
** it simply exits.
*/
/*
** SETTING: backoffice-logfile width=40
** If backoffice-logfile is not an empty string and is a valid
** filename, then a one-line message is appended to that file
** every time the backoffice runs.  This can be used for debugging,
** to ensure that backoffice is running appropriately.
*/
/*
** SETTING: binary-glob     width=40 versionable block-text
** The VALUE of this setting is a comma or newline-separated list of
** GLOB patterns that should be treated as binary files
** for committing and merging purposes.  Example: *.jpg
*/
#if defined(_WIN32)||defined(__CYGWIN__)||defined(__DARWIN__)

}
span.infoTag {
  font-weight: bold;
}
span.wikiTagCancelled {
  text-decoration: line-through;
}
div.columns {
  padding: 0 2em 0 2em;
  max-width: 1000px;
}
div.columns ul {
  margin: 0;
  padding: 0;
}


div.columns ul li:first-child {
  margin-top:0px;
}
.filetree {
  margin: 1em 0;
  line-height: 1.5;
}
.filetree > ul {
  display: inline-block;

  text-align: center;
  padding-right: 15px;
}
td.usetupListCon {
  text-align: left
}
div.ueditCapBox {

  margin-right: 20px;
  margin-bottom: 20px;
}
td.usetupEditLabel {
  text-align: right;
  vertical-align: top;
  white-space: nowrap;

  word-wrap: break-word;
}
pre.th1error {
  white-space: pre-wrap;
  word-wrap: break-word;
  color: red;
}
pre.textPlain {
  white-space: pre-wrap;
  word-wrap: break-word;
}
.statistics-report-graph-line {
  background-color: #446979;
}
.statistics-report-table-events th {
  padding: 0 1em 0 1em;
}
.statistics-report-table-events td {

  display: none;
}
table.label-value th {
  vertical-align: top;
  text-align: right;
  padding: 0.2ex 1ex;
}
table.forum_post {
  margin-top: 1ex;
  margin-bottom: 1ex;
  margin-left: 0;
  margin-right: 0;
  border-spacing: 0;
}
span.forum_author {
  color: #888;
  font-size: 75%;
}
span.forum_author::after {
  content: " | ";
}
span.forum_age {
  color: #888;
  font-size: 85%;
}
span.forum_buttons {
  font-size: 85%;
}
span.forum_buttons::before {
  color: #888;
  content: " | ";
}
span.forum_npost {
  color: #888;
  font-size: 75%;
}
table.forumeditform td {
  vertical-align: top;
  border-collapse: collapse;
  padding: 1px;
}
div.forum_body p {
  margin-top: 0;
}
td.form_label {
  vertical-align: top;
  text-align: right;
}
.debug {
  background-color: #ffc;
  border: 2px solid #ff0;
}
div.forumEdit {
  border: 1px solid black;
  padding-left: 1ex;
  padding-right: 1ex;
}
div.forumHier, div.forumTime {
  border: 1px solid black;
  padding-left: 1ex;
  padding-right: 1ex;
  margin-top: 1ex;
}
div.forumSel {
  background-color: #cef;
}
div.forumObs {
  color: #bbb;
}
#capabilitySummary {
  text-align: center;
}
#capabilitySummary td {
  padding-left: 3ex;
  padding-right: 3ex;
}
#capabilitySummary th {
  padding-left: 1ex;
  padding-right: 1ex;
}
.capsumOff {
  background-color: #bbb;
}
.capsumRead {
  background-color: #bfb;
}
.capsumWrite {
  background-color: #ffb;
}
label {
  white-space: nowrap;
}

  int rid;               /* Artifact ID of the file being annotated */
  int fnid;              /* Filename ID */
  Stmt q;                /* Query returning all ancestor versions */
  int cnt = 0;           /* Number of versions analyzed */
  int iLimit;            /* Maximum number of versions to analyze */
  sqlite3_int64 mxTime;  /* Halt at this time if not already complete */

  memset(p, 0, sizeof(*p));

  if( zLimit ){
    if( strcmp(zLimit,"none")==0 ){
      iLimit = 0;
      mxTime = 0;
    }else if( sqlite3_strglob("*[0-9]s", zLimit)==0 ){
      iLimit = 0;
      mxTime = current_time_in_milliseconds() + 1000.0*atof(zLimit);
    }else{
      iLimit = atoi(zLimit);
      if( iLimit<=0 ) iLimit = 30;
      mxTime = 0;
    }
  }else{
    /* Default limit is as much as we can do in 1.000 seconds */
    iLimit = 0;
    mxTime = current_time_in_milliseconds()+1000;
  }
  db_begin_transaction();

  /* Get the artifact ID for the check-in begin analyzed */
  if( zRevision ){
    cid = name_to_typed_rid(zRevision, "ci");
  }else{
    db_must_be_within_tree();
    cid = db_lget_int("checkout", 0);
  }
  origid = zOrigin ? name_to_typed_rid(zOrigin, "ci") : 0;

      blob_to_utf8_no_bom(&step, 0);
      annotation_step(p, &step, p->nVers-1, annFlags);
      blob_reset(&step);
    }
    p->nVers++;
    cnt++;
  }

  if( p->nVers==0 ){
    if( zRevision ){
      fossil_fatal("file %s does not exist in check-in %s", zFilename, zRevision);
    }else{
      fossil_fatal("no history for file: %s", zFilename);
    }
  }

  db_finalize(&q);
  db_end_transaction(0);
}

/*
** Return a color from a gradient.
*/

** check-in).  /blame and /praise also show the user who made the check-in.
**
** Reverse Annotations:  Normally, these web pages look at versions of
** FILENAME moving backwards in time back toward the root check-in.  However,
** if the origin= query parameter is used to specify some future check-in
** (example: "origin=trunk") then these pages show changes moving towards
** that alternative origin.  Thus using "origin=trunk" on an historical
** version of the file shows the first time each line in the file was changed
** or removed by any subsequent check-in.
**
** Query parameters:
**
**    checkin=ID          The check-in at which to start the annotation
**    filename=FILENAME   The filename.
**    filevers=BOOLEAN    Show file versions rather than check-in versions
**    limit=LIMIT         Limit the amount of analysis:
**                           "none"  No limit
**                           "Xs"    As much as can be computed in X seconds
**                           "N"     N versions
**    log=BOOLEAN         Show a log of versions analyzed
**    origin=ID           The origin checkin.  If unspecified, the root
**                           check-in over the entire repository is used.
**                           Specify "origin=trunk" or similar for a reverse
**                           annotation
**    w=BOOLEAN           Ignore whitespace

*/
void annotation_page(void){
  int i;
  const char *zLimit;    /* Depth limit */
  u64 annFlags = DIFF_STRIP_EOLCR;
  int showLog;           /* True to display the log */
  int fileVers;          /* Show file version instead of check-in versions */

**
** Reverse Annotations:  Normally, these commands look at versions of
** FILENAME moving backwards in time back toward the root check-in, and
** thus the output shows the most recent change to each line.  However,
** if the -o|--origin option is used to specify some future check-in
** (example: "-o trunk") then these commands show changes moving towards
** that alternative origin.  Thus using "-o trunk" on an historical version
** of the file shows the first time each line in the file was changed or
** removed by any subsequent check-in.
**
** Options:
**   --filevers                  Show file version numbers rather than
**                               check-in versions
**   -r|--revision VERSION       The specific check-in containing the file
**   -l|--log                    List all versions analyzed
**   -n|--limit LIMIT            Limit the amount of analysis:

**   --command PROG             External diff program - overrides "diff-command"
**   --context|-c N             Use N lines of context
**   --diff-binary BOOL         Include binary files when using external commands
**   --exec-abs-paths           Force absolute path names with external commands.
**   --exec-rel-paths           Force relative path names with external commands.
**   --from|-r VERSION          Select VERSION as source for the diff
**   --internal|-i              Use internal diff logic
**   --new-file|-N              Show complete text of added and deleted files
**   --numstat                  Show only the number of lines delete and added
**   --side-by-side|-y          Side-by-side diff
**   --strip-trailing-cr        Strip trailing CR
**   --tk                       Launch a Tcl/Tk GUI for display
**   --to VERSION               Select VERSION as target for the diff
**   --undo                     Diff against the "undo" buffer
**   --unified                  Unified diff

      }else{
        @ <blockquote>
        help_to_html(pCmd->zHelp, cgi_output_blob());
        @ </blockquote>
      }
    }
  }else{
    int i;

    style_header("Help");

    @ <a name='commands'></a>
    @ <h1>Available commands:</h1>
    @ <div class="columns" style="column-width: 12ex;">
    @ <ul>
    for(i=0; i<MX_COMMAND; i++){







      const char *z = aCommand[i].zName;
      const char *zBoldOn  = aCommand[i].eCmdFlags&CMDFLAG_1ST_TIER?"<b>" :"";
      const char *zBoldOff = aCommand[i].eCmdFlags&CMDFLAG_1ST_TIER?"</b>":"";
      if( '/'==*z || strncmp(z,"test",4)==0 ) continue;
      if( (aCommand[i].eCmdFlags & CMDFLAG_SETTING)!=0 ) continue;



      @ <li><a href="%R/help?cmd=%s(z)">%s(zBoldOn)%s(z)%s(zBoldOff)</a></li>




    }


    @ </ui></div>



    @ <a name='webpages'></a>
    @ <h1>Available web UI pages:</h1>
    @ <div class="columns" style="column-width: 18ex;">
    @ <ul>
    for(i=0; i<MX_COMMAND; i++){
      const char *z = aCommand[i].zName;
      if( '/'!=*z ) continue;









      if( aCommand[i].zHelp[0] ){
        @ <li><a href="%R/help?cmd=%s(z)">%s(z+1)</a></li>
      }else{
        @ <li>%s(z+1)</li>
      }




    }


    @ </ul></div>



    @ <a name='unsupported'></a>
    @ <h1>Unsupported commands:</h1>
    @ <div class="columns" style="column-width: 20ex;">
    @ <ul>
    for(i=0; i<MX_COMMAND; i++){
      const char *z = aCommand[i].zName;
      if( strncmp(z,"test",4)!=0 ) continue;

      if( aCommand[i].zHelp[0] ){
        @ <li><a href="%R/help?cmd=%s(z)">%s(z)</a></li>
      }else{
        @ <li>%s(z)</li>
      }
    }
    @ </ul></div>

    @ <a name='settings'></a>
    @ <h1>Settings:</h1>
    @ <div class="columns" style="column-width: 20ex;">
    @ <ul>
    for(i=0; i<MX_COMMAND; i++){
      const char *z = aCommand[i].zName;
      if( (aCommand[i].eCmdFlags & CMDFLAG_SETTING)==0 ) continue;



      if( aCommand[i].zHelp[0] ){
        @ <li><a href="%R/help?cmd=%s(z)">%s(z)</a></li>
      }else{
        @ <li>%s(z)</li>
      }




    }


    @ </ul></div>

































  }
  style_footer();
}

/*
** WEBPAGE: test-all-help

** Verify that all entries in the aMime[] table are in sorted order.
** Abort with a fatal error if any is out-of-order.
*/
static void mimetype_verify(void){
  int i;
  for(i=1; i<count(aMime); i++){
    if( fossil_strcmp(aMime[i-1].zSuffix,aMime[i].zSuffix)>=0 ){
      fossil_panic("mimetypes out of sequence: %s before %s",
                   aMime[i-1].zSuffix, aMime[i].zSuffix);
    }
  }
}

/*
** Guess the mime-type of a document based on its name.

      zFullpath = mprintf("%s/%s", g.zLocalRoot, zName);
      if( file_isfile(zFullpath, RepoFILE)
       && blob_read_from_file(&filebody, zFullpath, RepoFILE)>0 ){
        rid = 1;  /* Fake RID just to get the loop to end */
      }
      fossil_free(zFullpath);
    }else{
      vid = symbolic_name_to_rid(zCheckin, "ci");
      rid = vid>0 ? doc_load_content(vid, zName, &filebody) : 0;
    }
  }
  g.zPath = mprintf("%s/%s", g.zPath, zPathSuffix);
  if( rid==0 ) goto doc_not_found;
  blob_to_utf8_no_bom(&filebody, 0);

  /* The file is now contained in the filebody blob.  Deliver the

  cgi_set_status(404, "Not Found");
  style_header("Not Found");
  @ <p>Document %h(zOrigName) not found
  if( fossil_strcmp(zCheckin,"ckout")!=0 ){
    @ in %z(href("%R/tree?ci=%T",zCheckin))%h(zCheckin)</a>
  }
  style_footer();

  return;
}

/*
** The default logo.
*/
static const unsigned char aLogo[] = {

/*
** Copyright (c) 2018 D. Richard Hipp
**
** This program is free software; you can redistribute it and/or
** modify it under the terms of the Simplified BSD License (also
** known as the "2-Clause License" or "FreeBSD License".)
**
** 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.
**
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*******************************************************************************
**
** Logic for email notification, also known as "alerts".
**
** Are you looking for the code that reads and writes the internet
** email protocol?  That is not here.  See the "smtp.c" file instead.
** Yes, the choice of source code filenames is not the greatest, but
** it is not so bad that changing them seems justified.
*/ 
#include "config.h"
#include "email.h"
#include <assert.h>
#include <time.h>

/*
** Maximum size of the subscriberCode blob, in bytes
*/
#define SUBSCRIBER_CODE_SZ 32

/*
** SQL code to implement the tables needed by the email notification
** system.
*/
static const char zEmailInit[] =
@ DROP TABLE IF EXISTS repository.subscriber;
@ -- Subscribers are distinct from users.  A person can have a log-in in
@ -- the USER table without being a subscriber.  Or a person can be a
@ -- subscriber without having a USER table entry.  Or they can have both.
@ -- In the last case the suname column points from the subscriber entry
@ -- to the USER entry.
@ --
@ -- The ssub field is a string where each character indicates a particular
@ -- type of event to subscribe to.  Choices:
@ --     a - Announcements
@ --     c - Check-ins
@ --     t - Ticket changes
@ --     w - Wiki changes
@ -- Probably different codes will be added in the future.  In the future
@ -- we might also add a separate table that allows subscribing to email
@ -- notifications for specific branches or tags or tickets.
@ --
@ CREATE TABLE repository.subscriber(
@   subscriberId INTEGER PRIMARY KEY, -- numeric subscriber ID.  Internal use
@   subscriberCode BLOB DEFAULT (randomblob(32)) UNIQUE, -- UUID for subscriber
@   semail TEXT UNIQUE COLLATE nocase,-- email address
@   suname TEXT,                      -- corresponding USER entry
@   sverified BOOLEAN DEFAULT true,   -- email address verified
@   sdonotcall BOOLEAN,               -- true for Do Not Call 
@   sdigest BOOLEAN,                  -- true for daily digests only
@   ssub TEXT,                        -- baseline subscriptions
@   sctime INTDATE,                   -- When this entry was created. unixtime
@   mtime INTDATE,                    -- Last change.  unixtime
@   smip TEXT                         -- IP address of last change
@ );
@ CREATE INDEX repository.subscriberUname
@   ON subscriber(suname) WHERE suname IS NOT NULL;
@ 
@ DROP TABLE IF EXISTS repository.pending_alert;
@ -- Email notifications that need to be sent.
@ --
@ -- The first character of the eventid determines the event type.
@ -- Remaining characters determine the specific event.  For example,
@ -- 'c4413' means check-in with rid=4413.
@ --
@ CREATE TABLE repository.pending_alert(
@   eventid TEXT PRIMARY KEY,         -- Object that changed
@   sentSep BOOLEAN DEFAULT false,    -- individual alert sent
@   sentDigest BOOLEAN DEFAULT false, -- digest alert sent
@   sentMod BOOLEAN DEFAULT false     -- pending moderation alert sent
@ ) WITHOUT ROWID;
@ 
@ DROP TABLE IF EXISTS repository.email_bounce;
@ -- Record bounced emails.  If too many bounces are received within
@ -- some defined time range, then cancel the subscription.  Older
@ -- entries are periodically purged.
@ --
@ CREATE TABLE repository.email_bounce(
@   subscriberId INTEGER, -- to whom the email was sent.
@   sendTime INTEGER,     -- seconds since 1970 when email was sent
@   rcvdTime INTEGER      -- seconds since 1970 when bounce was received
@ );
;

/*
** Return true if the email notification tables exist.
*/
int email_tables_exist(void){
  return db_table_exists("repository", "subscriber");
}

/*
** Make sure the table needed for email notification exist in the repository.
**
** If the bOnlyIfEnabled option is true, then tables are only created
** if the email-send-method is something other than "off".
*/
void email_schema(int bOnlyIfEnabled){
  if( !email_tables_exist() ){
    if( bOnlyIfEnabled
     && fossil_strcmp(db_get("email-send-method","off"),"off")==0
    ){
      return;  /* Don't create table for disabled email */
    }
    db_multi_exec(zEmailInit/*works-like:""*/);
    email_triggers_enable();
  }else if( !db_table_has_column("repository","pending_alert","sentMod") ){
    db_multi_exec(
      "ALTER TABLE repository.pending_alert"
      " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
    );
  }
}

/*
** Enable triggers that automatically populate the pending_alert
** table.
*/
void email_triggers_enable(void){
  if( !db_table_exists("repository","pending_alert") ) return;
  db_multi_exec(
    "CREATE TRIGGER IF NOT EXISTS repository.email_trigger1\n"
    "AFTER INSERT ON event BEGIN\n"
    "  INSERT INTO pending_alert(eventid)\n"
    "    SELECT printf('%%.1c%%d',new.type,new.objid) WHERE true\n"
    "    ON CONFLICT(eventId) DO NOTHING;\n"
    "END;"
  );
}

/*
** Disable triggers the event_pending triggers.
**
** This must be called before rebuilding the EVENT table, for example
** via the "fossil rebuild" command.
*/
void email_triggers_disable(void){
  db_multi_exec(
    "DROP TRIGGER IF EXISTS repository.email_trigger1;\n"
  );
}

/*
** Return true if email alerts are active.
*/
int email_enabled(void){
  if( !email_tables_exist() ) return 0;
  if( fossil_strcmp(db_get("email-send-method","off"),"off")==0 ) return 0;
  return 1;
}

/*
** If the subscriber table does not exist, then paint an error message
** web page and return true.
**
** If the subscriber table does exist, return 0 without doing anything.
*/
static int email_webpages_disabled(void){
  if( email_tables_exist() ) return 0;
  style_header("Email Alerts Are Disabled");
  @ <p>Email alerts are disabled on this server</p>
  style_footer();
  return 1;
}

/*
** Insert a "Subscriber List" submenu link if the current user
** is an administrator.
*/
void email_submenu_common(void){
  if( g.perm.Admin ){
    if( fossil_strcmp(g.zPath,"subscribers") ){
      style_submenu_element("List Subscribers","%R/subscribers");
    }
    if( fossil_strcmp(g.zPath,"subscribe") ){
      style_submenu_element("Add New Subscriber","%R/subscribe");
    }
  }
}


/*
** WEBPAGE: setup_notification
**
** Administrative page for configuring and controlling email notification.
** Normally accessible via the /Admin/Notification menu.
*/
void setup_notification(void){
  static const char *const azSendMethods[] = {
    "off",   "Disabled",
    "pipe",  "Pipe to a command",
    "db",    "Store in a database",
    "dir",   "Store in a directory",
    "relay", "SMTP relay"
  };
  login_check_credentials();
  if( !g.perm.Setup ){
    login_needed(0);
    return;
  }
  db_begin_transaction();

  email_submenu_common();
  style_submenu_element("Send Announcement","%R/announce");
  style_header("Email Notification Setup");
  @ <h1>Status</h1>
  @ <table class="label-value">
  if( email_enabled() ){
    stats_for_email();
  }else{
    @ <th>Disabled</th>
  }
  @ </table>
  @ <hr>
  @ <h1> Configuration </h1>
  @ <form action="%R/setup_notification" method="post"><div>
  @ <input type="submit"  name="submit" value="Apply Changes" /><hr>
  login_insert_csrf_secret();

  entry_attribute("Canonical Server URL", 40, "email-url",
                   "eurl", "", 0);
  @ <p><b>Required.</b>
  @ This is URL used as the basename for hyperlinks included in
  @ email alert text.  Omit the trailing "/".
  @ Suggested value: "%h(g.zBaseURL)"
  @ (Property: "email-url")</p>
  @ <hr>

  entry_attribute("\"Return-Path\" email address", 20, "email-self",
                   "eself", "", 0);
  @ <p><b>Required.</b>
  @ This is the email to which email notification bounces should be sent.
  @ In cases where the email notification does not align with a specific
  @ Fossil login account (for example, digest messages), this is also
  @ the "From:" address of the email notification.
  @ The system administrator should arrange for emails sent to this address
  @ to be handed off to the "fossil email incoming" command so that Fossil
  @ can handle bounces. (Property: "email-self")</p>
  @ <hr>

  entry_attribute("Repository Nickname", 16, "email-subname",
                   "enn", "", 0);
  @ <p><b>Required.</b>
  @ This is short name used to identifies the repository in the
  @ Subject: line of email alerts.  Traditionally this name is
  @ included in square brackets.  Examples: "[fossil-src]", "[sqlite-src]".
  @ (Property: "email-subname")</p>
  @ <hr>

  multiple_choice_attribute("Email Send Method", "email-send-method", "esm",
       "off", count(azSendMethods)/2, azSendMethods);
  @ <p>How to send email.  Requires auxiliary information from the fields
  @ that follow.  Hint: Use the <a href="%R/announce">/announce</a> page
  @ to send test message to debug this setting.
  @ (Property: "email-send-method")</p>
  email_schema(1);
  entry_attribute("Pipe Email Text Into This Command", 60, "email-send-command",
                   "ecmd", "sendmail -ti", 0);
  @ <p>When the send method is "pipe to a command", this is the command
  @ that is run.  Email messages are piped into the standard input of this
  @ command.  The command is expected to extract the sender address,
  @ recepient addresses, and subject from the header of the piped email
  @ text.  (Property: "email-send-command")</p>

  entry_attribute("Store Emails In This Database", 60, "email-send-db",
                   "esdb", "", 0);
  @ <p>When the send method is "store in a databaes", each email message is
  @ stored in an SQLite database file with the name given here.
  @ (Property: "email-send-db")</p>

  entry_attribute("Store Emails In This Directory", 60, "email-send-dir",
                   "esdir", "", 0);
  @ <p>When the send method is "store in a directory", each email message is
  @ stored as a separate file in the directory shown here.
  @ (Property: "email-send-dir")</p>

  entry_attribute("SMTP Relay Host", 60, "email-send-relayhost",
                   "esrh", "", 0);
  @ <p>When the send method is "SMTP relay", each email message is
  @ transmitted via the SMTP protocol (rfc5321) to a "Mail Submission
  @ Agent" or "MSA" (rfc4409) at the hostname shown here.  Optionally
  @ append a colon and TCP port number (ex: smtp.example.com:587).
  @ The default TCP port number is 25.
  @ (Property: "email-send-relayhost")</p>
  @ <hr>

  entry_attribute("Administrator email address", 40, "email-admin",
                   "eadmin", "", 0);
  @ <p>This is the email for the human administrator for the system.
  @ Abuse and trouble reports are send here.
  @ (Property: "email-admin")</p>
  @ <hr>

  @ <p><input type="submit"  name="submit" value="Apply Changes" /></p>
  @ </div></form>
  db_end_transaction(0);
  style_footer();
}

#if 0
/*
** Encode pMsg as MIME base64 and append it to pOut
*/
static void append_base64(Blob *pOut, Blob *pMsg){
  int n, i, k;
  char zBuf[100];
  n = blob_size(pMsg);
  for(i=0; i<n; i+=54){
    k = translateBase64(blob_buffer(pMsg)+i, i+54<n ? 54 : n-i, zBuf);
    blob_append(pOut, zBuf, k);
    blob_append(pOut, "\r\n", 2);
  }
}
#endif

/*
** Encode pMsg using the quoted-printable email encoding and
** append it onto pOut
*/
static void append_quoted(Blob *pOut, Blob *pMsg){
  char *zIn = blob_str(pMsg);
  char c;
  int iCol = 0;
  while( (c = *(zIn++))!=0 ){
    if( (c>='!' && c<='~' && c!='=' && c!=':')
     || (c==' ' && zIn[0]!='\r' && zIn[0]!='\n')
    ){
      blob_append_char(pOut, c);
      iCol++;
      if( iCol>=70 ){
        blob_append(pOut, "=\r\n", 3);
        iCol = 0;
      }
    }else if( c=='\r' && zIn[0]=='\n' ){
      zIn++;
      blob_append(pOut, "\r\n", 2);
      iCol = 0;
    }else if( c=='\n' ){
      blob_append(pOut, "\r\n", 2);
      iCol = 0;
    }else{
      char x[3];
      x[0] = '=';
      x[1] = "0123456789ABCDEF"[(c>>4)&0xf];
      x[2] = "0123456789ABCDEF"[c&0xf];
      blob_append(pOut, x, 3);
      iCol += 3;
    }
  }
}

#if defined(_WIN32) || defined(WIN32)
# undef popen
# define popen _popen
# undef pclose
# define pclose _pclose
#endif

#if INTERFACE
/*
** An instance of the following object is used to send emails.
*/
struct EmailSender {
  sqlite3 *db;               /* Database emails are sent to */
  sqlite3_stmt *pStmt;       /* Stmt to insert into the database */
  const char *zDest;         /* How to send email. */
  const char *zDb;           /* Name of database file */
  const char *zDir;          /* Directory in which to store as email files */
  const char *zCmd;          /* Command to run for each email */
  const char *zFrom;         /* Emails come from here */
  SmtpSession *pSmtp;        /* SMTP relay connection */
  Blob out;                  /* For zDest=="blob" */
  char *zErr;                /* Error message */
  u32 mFlags;                /* Flags */
  int bImmediateFail;        /* On any error, call fossil_fatal() */
};

/* Allowed values for mFlags to email_sender_new().
*/
#define EMAIL_IMMEDIATE_FAIL   0x0001   /* Call fossil_fatal() on any error */
#define EMAIL_TRACE            0x0002   /* Log sending process on console */

#endif /* INTERFACE */

/*
** Shutdown an emailer.  Clear all information other than the error message.
*/
static void emailerShutdown(EmailSender *p){
  sqlite3_finalize(p->pStmt);
  p->pStmt = 0;
  sqlite3_close(p->db);
  p->db = 0;
  p->zDb = 0;
  p->zDir = 0;
  p->zCmd = 0;
  if( p->pSmtp ){
    smtp_client_quit(p->pSmtp);
    smtp_session_free(p->pSmtp);
    p->pSmtp = 0;
  }
  blob_reset(&p->out);
}

/*
** Put the EmailSender into an error state.
*/
static void emailerError(EmailSender *p, const char *zFormat, ...){
  va_list ap;
  fossil_free(p->zErr);
  va_start(ap, zFormat);
  p->zErr = vmprintf(zFormat, ap);
  va_end(ap);
  emailerShutdown(p);
  if( p->mFlags & EMAIL_IMMEDIATE_FAIL ){
    fossil_fatal("%s", p->zErr);
  }
}

/*
** Free an email sender object
*/
void email_sender_free(EmailSender *p){
  if( p ){
    emailerShutdown(p);
    fossil_free(p->zErr);
    fossil_free(p);
  }
}

/*
** Get an email setting value.  Report an error if not configured.
** Return 0 on success and one if there is an error.
*/
static int emailerGetSetting(
  EmailSender *p,        /* Where to report the error */
  const char **pzVal,    /* Write the setting value here */
  const char *zName      /* Name of the setting */
){
  const char *z = db_get(zName, 0);
  int rc = 0;
  if( z==0 || z[0]==0 ){
    emailerError(p, "missing \"%s\" setting", zName);
    rc = 1;
  }else{
    *pzVal = z;
  }
  return rc;
}

/*
** Create a new EmailSender object.
**
** The method used for sending email is determined by various email-*
** settings, and especially email-send-method.  The repository
** email-send-method can be overridden by the zAltDest argument to
** cause a different sending mechanism to be used.  Pass "stdout" to
** zAltDest to cause all emails to be printed to the console for
** debugging purposes.
**
** The EmailSender object returned must be freed using email_sender_free().
*/
EmailSender *email_sender_new(const char *zAltDest, u32 mFlags){
  EmailSender *p;

  p = fossil_malloc(sizeof(*p));
  memset(p, 0, sizeof(*p));
  blob_init(&p->out, 0, 0);
  p->mFlags = mFlags;
  if( zAltDest ){
    p->zDest = zAltDest;
  }else{
    p->zDest = db_get("email-send-method","off");
  }
  if( fossil_strcmp(p->zDest,"off")==0 ) return p;
  if( emailerGetSetting(p, &p->zFrom, "email-self") ) return p;
  if( fossil_strcmp(p->zDest,"db")==0 ){
    char *zErr;
    int rc;
    if( emailerGetSetting(p, &p->zDb, "email-send-db") ) return p;
    rc = sqlite3_open(p->zDb, &p->db);
    if( rc ){
      emailerError(p, "unable to open output database file \"%s\": %s",
                   p->zDb, sqlite3_errmsg(p->db));
      return p;
    }
    rc = sqlite3_exec(p->db, "CREATE TABLE IF NOT EXISTS email(\n"
                          "  emailid INTEGER PRIMARY KEY,\n"
                          "  msg TEXT\n);", 0, 0, &zErr);
    if( zErr ){
      emailerError(p, "CREATE TABLE failed with \"%s\"", zErr);
      sqlite3_free(zErr);
      return p;
    }
    rc = sqlite3_prepare_v2(p->db, "INSERT INTO email(msg) VALUES(?1)", -1,
                            &p->pStmt, 0);
    if( rc ){
      emailerError(p, "cannot prepare INSERT statement: %s",
                 sqlite3_errmsg(p->db));
      return p;
    }
  }else if( fossil_strcmp(p->zDest, "pipe")==0 ){
    emailerGetSetting(p, &p->zCmd, "email-send-command");
  }else if( fossil_strcmp(p->zDest, "dir")==0 ){
    emailerGetSetting(p, &p->zDir, "email-send-dir");
  }else if( fossil_strcmp(p->zDest, "blob")==0 ){
    blob_init(&p->out, 0, 0);
  }else if( fossil_strcmp(p->zDest, "relay")==0 ){
    const char *zRelay = 0;
    emailerGetSetting(p, &zRelay, "email-send-relayhost");
    if( zRelay ){
      u32 smtpFlags = SMTP_DIRECT;
      if( mFlags & EMAIL_TRACE ) smtpFlags |= SMTP_TRACE_STDOUT;
      p->pSmtp = smtp_session_new(p->zFrom, zRelay, smtpFlags);
      smtp_client_startup(p->pSmtp);
    }
  }
  return p;
}

/*
** Scan the header of the email message in pMsg looking for the
** (first) occurrance of zField.  Fill pValue with the content of
** that field.
**
** This routine initializes pValue.  Any prior content of pValue is
** discarded (leaked).
**
** Return non-zero on success.  Return 0 if no instance of the header
** is found.
*/
int email_header_value(Blob *pMsg, const char *zField, Blob *pValue){
  int nField = (int)strlen(zField);
  Blob line;
  blob_rewind(pMsg);
  blob_init(pValue,0,0);
  while( blob_line(pMsg, &line) ){
    int n, i;
    char *z;
    blob_trim(&line);
    n = blob_size(&line);
    if( n==0 ) return 0;
    if( n<nField+1 ) continue;
    z = blob_buffer(&line);
    if( sqlite3_strnicmp(z, zField, nField)==0 && z[nField]==':' ){
      for(i=nField+1; i<n && fossil_isspace(z[i]); i++){}
      blob_init(pValue, z+i, n-i);
      while( blob_line(pMsg, &line) ){
        blob_trim(&line);
        n = blob_size(&line);
        if( n==0 ) break;
        z = blob_buffer(&line);
        if( !fossil_isspace(z[0]) ) break;
        for(i=1; i<n && fossil_isspace(z[i]); i++){}
        blob_append(pValue, " ", 1);
        blob_append(pValue, z+i, n-i);
      }
      return 1;
    }
  }
  return 0;
}

/*
** Make a copy of the input string up to but not including the
** first cTerm character.
**
** Verify that the string really that is to be copied really is a
** valid email address.  If it is not, then return NULL.
**
** This routine is more restrictive than necessary.  It does not
** allow comments, IP address, quoted strings, or certain uncommon
** characters.  The only non-alphanumerics allowed in the local
** part are "_", "+", "-" and "+".
*/
char *email_copy_addr(const char *z, char cTerm ){
  int i;
  int nAt = 0;
  int nDot = 0;
  char c;
  if( z[0]=='.' ) return 0;  /* Local part cannot begin with "." */
  for(i=0; (c = z[i])!=0 && c!=cTerm; i++){
    if( fossil_isalnum(c) ){
      /* Alphanumerics are always ok */
    }else if( c=='@' ){
      if( nAt ) return 0;   /* Only a single "@"  allowed */
      if( i>64 ) return 0;  /* Local part too big */
      nAt = 1;
      nDot = 0;
      if( i==0 ) return 0;  /* Disallow empty local part */
      if( z[i-1]=='.' ) return 0; /* Last char of local cannot be "." */
      if( z[i+1]=='.' || z[i+1]=='-' ){
        return 0; /* Domain cannot begin with "." or "-" */
      }
    }else if( c=='-' ){
      if( z[i+1]==cTerm ) return 0;  /* Last character cannot be "-" */
    }else if( c=='.' ){
      if( z[i+1]=='.' ) return 0;  /* Do not allow ".." */
      if( z[i+1]==cTerm ) return 0;  /* Domain may not end with . */
      nDot++;
    }else if( (c=='_' || c=='+') && nAt==0 ){
      /* _ and + are ok in the local part */
    }else{
      return 0;   /* Anything else is an error */
    }
  }
  if( c!=cTerm ) return 0;    /* Missing terminator */
  if( nAt==0 ) return 0;      /* No "@" found anywhere */
  if( nDot==0 ) return 0;     /* No "." in the domain */

  /* If we reach this point, the email address is valid */
  return mprintf("%.*s", i, z);
}

/*
** Scan the input string for a valid email address enclosed in <...>
** If the string contains one or more email addresses, extract the first
** one into memory obtained from mprintf() and return a pointer to it.
** If no valid email address can be found, return NULL.
*/
char *email_find_emailaddr(const char *zIn){
  char *zOut = 0;
  while( zIn!=0 ){
     zIn = (const char*)strchr(zIn, '<');
     if( zIn==0 ) break;
     zIn++;
     zOut = email_copy_addr(zIn, '>');
     if( zOut!=0 ) break;
  }
  return zOut;
}

/*
** SQL function:  find_emailaddr(X)
**
** Return the first valid email address of the form <...> in input string
** X.  Or return NULL if not found.
*/
void email_find_emailaddr_func(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const char *zIn = (const char*)sqlite3_value_text(argv[0]);
  char *zOut = email_find_emailaddr(zIn);
  if( zOut ){
    sqlite3_result_text(context, zOut, -1, fossil_free);
  }
}

/*
** Return the hostname portion of an email address - the part following
** the @
*/
char *email_hostname(const char *zAddr){
  char *z = strchr(zAddr, '@');
  if( z ){
    z++;
  }else{
    z = (char*)zAddr;
  }
  return z;
}

/*
** Return a pointer to a fake email mailbox name that corresponds
** to human-readable name zFromName.  The fake mailbox name is based
** on a hash.  No huge problems arise if there is a hash collisions,
** but it is still better if collisions can be avoided.
**
** The returned string is held in a static buffer and is overwritten
** by each subsequent call to this routine.
*/
static char *email_mailbox_name(const char *zFromName){
  static char zHash[20];
  unsigned int x = 0;
  int n = 0;
  while( zFromName[0] ){
    n++;
    x = x*1103515245 + 12345 + ((unsigned char*)zFromName)[0];
    zFromName++;
  }
  sqlite3_snprintf(sizeof(zHash), zHash,
      "noreply%x%08x", n, x);
  return zHash;
}

/*
** COMMAND: test-mailbox-hashname
**
** Usage: %fossil test-mailbox-hashname HUMAN-NAME ...
**
** Return the mailbox hash name corresponding to each human-readable
** name on the command line.  This is a test interface for the
** email_mailbox_name() function.
*/
void email_test_mailbox_hashname(void){
  int i;
  for(i=2; i<g.argc; i++){
    fossil_print("%30s: %s\n", g.argv[i], email_mailbox_name(g.argv[i]));
  }
}

/*
** Extract all To: header values from the email header supplied.
** Store them in the array list.
*/
void email_header_to(Blob *pMsg, int *pnTo, char ***pazTo){
  int nTo = 0;
  char **azTo = 0;
  Blob v;
  char *z, *zAddr;
  int i;
  
  email_header_value(pMsg, "to", &v);
  z = blob_str(&v);
  for(i=0; z[i]; i++){
    if( z[i]=='<' && (zAddr = email_copy_addr(&z[i+1],'>'))!=0 ){
      azTo = fossil_realloc(azTo, sizeof(azTo[0])*(nTo+1) );
      azTo[nTo++] = zAddr;
    }
  }
  *pnTo = nTo;
  *pazTo = azTo;
}

/*
** Free a list of To addresses obtained from a prior call to 
** email_header_to()
*/
void email_header_to_free(int nTo, char **azTo){
  int i;
  for(i=0; i<nTo; i++) fossil_free(azTo[i]);
  fossil_free(azTo);
}

/*
** Send a single email message.
**
** The recepient(s) must be specified using  "To:" or "Cc:" or "Bcc:" fields
** in the header.  Likewise, the header must contains a "Subject:" line.
** The header might also include fields like "Message-Id:" or
** "In-Reply-To:".
**
** This routine will add fields to the header as follows:
**
**     From:
**     Date:
**     Message-Id:
**     Content-Type:
**     Content-Transfer-Encoding:
**     MIME-Version:
**     X-Fossil-From:
**     
** The caller maintains ownership of the input Blobs.  This routine will
** read the Blobs and send them onward to the email system, but it will
** not free them.
**
** The Message-Id: field is added if there is not already a Message-Id
** in the pHdr parameter.
**
** If the zFromName argument is not NULL, then it should be a human-readable
** name or handle for the sender.  In that case, "From:" becomes a made-up
** email address based on a hash of zFromName and the domain of email-self,
** and an additional "X-Fossil-From:" field is inserted with the email-self
** address.  Downstream software might use the X-Fossil-From header to set
** the envelope-from address of the email.  If zFromName is a NULL pointer, 
** then the "From:" is set to the email-self value and X-Fossil-From is
** omitted.
*/
void email_send(
  EmailSender *p,           /* Emailer context */
  Blob *pHdr,               /* Email header (incomplete) */
  Blob *pBody,              /* Email body */
  const char *zFromName     /* Optional human-readable name of sender */
){
  Blob all, *pOut;
  u64 r1, r2;
  if( p->mFlags & EMAIL_TRACE ){
    fossil_print("Sending email\n");
  }
  if( fossil_strcmp(p->zDest, "off")==0 ){
    return;
  }
  if( fossil_strcmp(p->zDest, "blob")==0 ){
    pOut = &p->out;
    if( blob_size(pOut) ){
      blob_appendf(pOut, "%.72c\n", '=');
    }
  }else{
    blob_init(&all, 0, 0);
    pOut = &all;
  }
  blob_append(pOut, blob_buffer(pHdr), blob_size(pHdr));
  if( zFromName ){
    blob_appendf(pOut, "From: %s <%s@%s>\r\n",
       zFromName, email_mailbox_name(zFromName), email_hostname(p->zFrom));
    blob_appendf(pOut, "X-Fossil-From: <%s>\r\n", p->zFrom);
  }else{
    blob_appendf(pOut, "From: <%s>\r\n", p->zFrom);
  }
  blob_appendf(pOut, "Date: %z\r\n", cgi_rfc822_datestamp(time(0)));
  if( strstr(blob_str(pHdr), "\r\nMessage-Id:")==0 ){
    /* Message-id format:  "<$(date)x$(random)@$(from-host)>" where $(date) is
    ** the current unix-time in hex, $(random) is a 64-bit random number,
    ** and $(from) is the domain part of the email-self setting. */
    sqlite3_randomness(sizeof(r1), &r1);
    r2 = time(0);
    blob_appendf(pOut, "Message-Id: <%llxx%016llx@%s>\r\n",
                 r2, r1, email_hostname(p->zFrom));
  }
  blob_add_final_newline(pBody);
  blob_appendf(pOut, "MIME-Version: 1.0\r\n");
  blob_appendf(pOut, "Content-Type: text/plain; charset=\"UTF-8\"\r\n");
#if 0
  blob_appendf(pOut, "Content-Transfer-Encoding: base64\r\n\r\n");
  append_base64(pOut, pBody);
#else
  blob_appendf(pOut, "Content-Transfer-Encoding: quoted-printable\r\n\r\n");
  append_quoted(pOut, pBody);
#endif
  if( p->pStmt ){
    int i, rc;
    sqlite3_bind_text(p->pStmt, 1, blob_str(&all), -1, SQLITE_TRANSIENT);
    for(i=0; i<100 && sqlite3_step(p->pStmt)==SQLITE_BUSY; i++){
      sqlite3_sleep(10);
    }
    rc = sqlite3_reset(p->pStmt);
    if( rc!=SQLITE_OK ){
      emailerError(p, "Failed to insert email message into output queue.\n"
                      "%s", sqlite3_errmsg(p->db));
    }
  }else if( p->zCmd ){
    FILE *out = popen(p->zCmd, "w");
    if( out ){
      fwrite(blob_buffer(&all), 1, blob_size(&all), out);
      fclose(out);
    }else{
      emailerError(p, "Could not open output pipe \"%s\"", p->zCmd);
    }
  }else if( p->zDir ){
    char *zFile = file_time_tempname(p->zDir, ".email");
    blob_write_to_file(&all, zFile);
    fossil_free(zFile);
  }else if( p->pSmtp ){
    char **azTo = 0;
    int nTo = 0;
    email_header_to(pHdr, &nTo, &azTo);
    if( nTo>0 ){
      smtp_send_msg(p->pSmtp, p->zFrom, nTo, (const char**)azTo,blob_str(&all));
      email_header_to_free(nTo, azTo);
    }
  }else if( strcmp(p->zDest, "stdout")==0 ){
    char **azTo = 0;
    int nTo = 0;
    int i;
    email_header_to(pHdr, &nTo, &azTo);
    for(i=0; i<nTo; i++){
      fossil_print("X-To-Test-%d: [%s]\r\n", i, azTo[i]);
    }
    email_header_to_free(nTo, azTo);
    blob_add_final_newline(&all);
    fossil_print("%s", blob_str(&all));
  }
  blob_reset(&all);
}

/*
** SETTING: email-send-method         width=5 default=off
** Determine the method used to send email.  Allowed values are
** "off", "relay", "pipe", "dir", "db", and "stdout".  The "off" value
** means no email is ever sent.  The "relay" value means emails are sent
** to an Mail Sending Agent using SMTP located at email-send-relayhost.
** The "pipe" value means email messages are piped into a command 
** determined by the email-send-command setting. The "dir" value means
** emails are written to individual files in a directory determined
** by the email-send-dir setting.  The "db" value means that emails
** are added to an SQLite database named by the* email-send-db setting.
** The "stdout" value writes email text to standard output, for debugging.
*/
/*
** SETTING: email-send-command       width=40
** This is a command to which outbound email content is piped when the
** email-send-method is set to "pipe".  The command must extract
** recipient, sender, subject, and all other relevant information
** from the email header.
*/
/*
** SETTING: email-send-dir           width=40
** This is a directory into which outbound emails are written as individual
** files if the email-send-method is set to "dir".
*/
/*
** SETTING: email-send-db            width=40
** This is an SQLite database file into which outbound emails are written
** if the email-send-method is set to "db".
*/
/*
** SETTING: email-self               width=40
** This is the email address for the repository.  Outbound emails add
** this email address as the "From:" field.
*/
/*
** SETTING: email-send-relayhost      width=40
** This is the hostname and TCP port to which output email messages
** are sent when email-send-method is "relay".  There should be an
** SMTP server configured as a Mail Submission Agent listening on the
** designated host and port and all times.
*/


/*
** COMMAND: alerts
** 
** Usage: %fossil alerts SUBCOMMAND ARGS...
**
** Subcommands:
**
**    pending                 Show all pending alerts.  Useful for debugging.
**
**    reset                   Hard reset of all email notification tables
**                            in the repository.  This erases all subscription
**                            information.  ** Use with extreme care **
**
**    send                    Compose and send pending email alerts.
**                            Some installations may want to do this via
**                            a cron-job to make sure alerts are sent
**                            in a timely manner.
**                            Options:
**
**                               --digest     Send digests
**                               --test       Write to standard output
**
**    settings [NAME VALUE]   With no arguments, list all email settings.
**                            Or change the value of a single email setting.
**
**    status                  Report on the status of the email alert
**                            subsystem
**
**    subscribers [PATTERN]   List all subscribers matching PATTERN.
**
**    test-message TO [OPTS]  Send a single email message using whatever
**                            email sending mechanism is currently configured.
**                            Use this for testing the email notification
**                            configuration.  Options:
**
**                              --body FILENAME
**                              --smtp-trace
**                              --stdout
**                              --subject|-S SUBJECT
**
**    unsubscribe EMAIL       Remove a single subscriber with the given EMAIL.
*/
void email_cmd(void){
  const char *zCmd;
  int nCmd;
  db_find_and_open_repository(0, 0);
  email_schema(0);
  zCmd = g.argc>=3 ? g.argv[2] : "x";
  nCmd = (int)strlen(zCmd);
  if( strncmp(zCmd, "pending", nCmd)==0 ){
    Stmt q;
    verify_all_options();
    if( g.argc!=3 ) usage("pending");
    db_prepare(&q,"SELECT eventid, sentSep, sentDigest, sentMod"
                  "  FROM pending_alert");
    while( db_step(&q)==SQLITE_ROW ){
      fossil_print("%10s %7s %10s %7s\n",
         db_column_text(&q,0),
         db_column_int(&q,1) ? "sentSep" : "",
         db_column_int(&q,2) ? "sentDigest" : "",
         db_column_int(&q,3) ? "sentMod" : "");
    }
    db_finalize(&q);
  }else
  if( strncmp(zCmd, "reset", nCmd)==0 ){
    int c;
    int bForce = find_option("force","f",0)!=0;
    verify_all_options();
    if( bForce ){
      c = 'y';
    }else{
      Blob yn;
      fossil_print(
          "This will erase all content in the repository tables, thus\n"
          "deleting all subscriber information.  The information will be\n"
          "unrecoverable.\n");
      prompt_user("Continue? (y/N) ", &yn);
      c = blob_str(&yn)[0];
      blob_reset(&yn);
    }
    if( c=='y' ){
      email_triggers_disable();
      db_multi_exec(
        "DROP TABLE IF EXISTS subscriber;\n"
        "DROP TABLE IF EXISTS pending_alert;\n"
        "DROP TABLE IF EXISTS email_bounce;\n"
        /* Legacy */
        "DROP TABLE IF EXISTS email_pending;\n"
        "DROP TABLE IF EXISTS subscription;\n"
      );
      email_schema(0);
    }
  }else
  if( strncmp(zCmd, "send", nCmd)==0 ){
    u32 eFlags = 0;
    if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
    if( find_option("test",0,0)!=0 ){
      eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
    }
    verify_all_options();
    email_send_alerts(eFlags);
  }else
  if( strncmp(zCmd, "settings", nCmd)==0 ){
    int isGlobal = find_option("global",0,0)!=0;
    int nSetting;
    const Setting *pSetting = setting_info(&nSetting);
    db_open_config(1, 0);
    verify_all_options();
    if( g.argc!=3 && g.argc!=5 ) usage("setting [NAME VALUE]");
    if( g.argc==5 ){
      const char *zLabel = g.argv[3];
      if( strncmp(zLabel, "email-", 6)!=0
       || (pSetting = db_find_setting(zLabel, 1))==0 ){
        fossil_fatal("not a valid email setting: \"%s\"", zLabel);
      }
      db_set(pSetting->name, g.argv[4], isGlobal);
      g.argc = 3;
    }
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
  }else
  if( strncmp(zCmd, "status", nCmd)==0 ){
    int nSetting, n;
    static const char *zFmt = "%-29s %d\n";
    const Setting *pSetting = setting_info(&nSetting);
    db_open_config(1, 0);
    verify_all_options();
    if( g.argc!=3 ) usage("status");
    pSetting = setting_info(&nSetting);
    for(; nSetting>0; nSetting--, pSetting++ ){
      if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
      print_setting(pSetting);
    }
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
    n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
    fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
    n = db_int(0,"SELECT count(*) FROM subscriber");
    fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
    n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
                   " AND NOT sdonotcall AND length(ssub)>1");
    fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
  }else
  if( strncmp(zCmd, "subscribers", nCmd)==0 ){
    Stmt q;
    verify_all_options();
    if( g.argc!=3 && g.argc!=4 ) usage("subscribers [PATTERN]");
    if( g.argc==4 ){
      char *zPattern = g.argv[3];
      db_prepare(&q,
        "SELECT semail FROM subscriber"
        " WHERE semail LIKE '%%%q%%' OR suname LIKE '%%%q%%'"
        "  OR semail GLOB '*%q*' or suname GLOB '*%q*'"
        " ORDER BY semail",
        zPattern, zPattern, zPattern, zPattern);
    }else{
      db_prepare(&q,
        "SELECT semail FROM subscriber"
        " ORDER BY semail");
    }
    while( db_step(&q)==SQLITE_ROW ){
      fossil_print("%s\n", db_column_text(&q, 0));
    }
    db_finalize(&q);
  }else
  if( strncmp(zCmd, "test-message", nCmd)==0 ){
    Blob prompt, body, hdr;
    const char *zDest = find_option("stdout",0,0)!=0 ? "stdout" : 0;
    int i;
    u32 mFlags = EMAIL_IMMEDIATE_FAIL;
    const char *zSubject = find_option("subject", "S", 1);
    const char *zSource = find_option("body", 0, 1);
    EmailSender *pSender;
    if( find_option("smtp-trace",0,0)!=0 ) mFlags |= EMAIL_TRACE;
    verify_all_options();
    blob_init(&prompt, 0, 0);
    blob_init(&body, 0, 0);
    blob_init(&hdr, 0, 0);
    blob_appendf(&hdr,"To: ");
    for(i=3; i<g.argc; i++){
      if( i>3 ) blob_append(&hdr, ", ", 2);
      blob_appendf(&hdr, "<%s>", g.argv[i]);
    }
    blob_append(&hdr,"\r\n",2);
    if( zSubject==0 ) zSubject = "fossil alerts test-message";
    blob_appendf(&hdr, "Subject: %s\r\n", zSubject);
    if( zSource ){
      blob_read_from_file(&body, zSource, ExtFILE);
    }else{
      prompt_for_user_comment(&body, &prompt);
    }
    blob_add_final_newline(&body);
    pSender = email_sender_new(zDest, mFlags);
    email_send(pSender, &hdr, &body, 0);
    email_sender_free(pSender);
    blob_reset(&hdr);
    blob_reset(&body);
    blob_reset(&prompt);
  }else
  if( strncmp(zCmd, "unsubscribe", nCmd)==0 ){
    verify_all_options();
    if( g.argc!=4 ) usage("unsubscribe EMAIL");
    db_multi_exec(
      "DELETE FROM subscriber WHERE semail=%Q", g.argv[3]);
  }else
  {
    usage("pending|reset|send|setting|status|"
          "subscribers|test-message|unsubscribe");
  }
}

/*
** Do error checking on a submitted subscription form.  Return TRUE
** if the submission is valid.  Return false if any problems are seen.
*/
static int subscribe_error_check(
  int *peErr,           /* Type of error */
  char **pzErr,         /* Error message text */
  int needCaptcha       /* True if captcha check needed */
){
  const char *zEAddr;
  int i, j, n;
  char c;

  *peErr = 0;
  *pzErr = 0;

  /* Check the validity of the email address.
  **
  **  (1) Exactly one '@' character.
  **  (2) No other characters besides [a-zA-Z0-9._+-]
  **
  **  The local part is currently more restrictive than RFC 5322 allows:
  **  https://stackoverflow.com/a/2049510/142454  We will expand this as
  **  necessary.
  */
  zEAddr = P("e");
  if( zEAddr==0 ) return 0;
  for(i=j=n=0; (c = zEAddr[i])!=0; i++){
    if( c=='@' ){
      n = i;
      j++;
      continue;
    }
    if( !fossil_isalnum(c) && c!='.' && c!='_' && c!='-' && c!='+' ){
      *peErr = 1;
      *pzErr = mprintf("illegal character in email address: 0x%x '%c'",
                   c, c);
      return 0;
    }
  }
  if( j!=1 ){
    *peErr = 1;
    *pzErr = mprintf("email address should contain exactly one '@'");
    return 0;
  }
  if( n<1 ){
    *peErr = 1;
    *pzErr = mprintf("name missing before '@' in email address");
    return 0;
  }
  if( n>i-5 ){
    *peErr = 1;
    *pzErr = mprintf("email domain too short");
     return 0;
  }

  /* Verify the captcha */
  if( needCaptcha && !captcha_is_correct(1) ){
    *peErr = 2;
    *pzErr = mprintf("incorrect security code");
    return 0;
  }

  /* Check to make sure the email address is available for reuse */
  if( db_exists("SELECT 1 FROM subscriber WHERE semail=%Q", zEAddr) ){
    *peErr = 1;
    *pzErr = mprintf("this email address is used by someone else");
    return 0;
  }

  /* If we reach this point, all is well */
  return 1;
}

/*
** Text of email message sent in order to confirm a subscription.
*/
static const char zConfirmMsg[] = 
@ Someone has signed you up for email alerts on the Fossil repository
@ at %s.
@
@ To confirm your subscription and begin receiving alerts, click on
@ the following hyperlink:
@
@    %s/alerts/%s
@
@ Save the hyperlink above!  You can reuse this same hyperlink to
@ unsubscribe or to change the kinds of alerts you receive.
@
@ If you do not want to subscribe, you can simply ignore this message.
@ You will not be contacted again.
@
;

/*
** Append the text of an email confirmation message to the given
** Blob.  The security code is in zCode.
*/
void email_append_confirmation_message(Blob *pMsg, const char *zCode){
  blob_appendf(pMsg, zConfirmMsg/*works-like:"%s%s%s"*/,
                   g.zBaseURL, g.zBaseURL, zCode);
}

/*
** WEBPAGE: subscribe
**
** Allow users to subscribe to email notifications.
**
** This page is usually run by users who are not logged in.
** A logged-in user can add email notifications on the /alerts page.
** Access to this page by a logged in user (other than an
** administrator) results in a redirect to the /alerts page.
**
** Administrators can visit this page in order to sign up other
** users.
**
** The Email-Alerts permission ("7") is required to access this
** page.  To allow anonymous passers-by to sign up for email
** notification, set Email-Alerts on user "nobody" or "anonymous".
*/
void subscribe_page(void){
  int needCaptcha;
  unsigned int uSeed;
  const char *zDecoded;
  char *zCaptcha = 0;
  char *zErr = 0;
  int eErr = 0;

  if( email_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.EmailAlert ){
    login_needed(g.anon.EmailAlert);
    return;
  }
  if( login_is_individual()
   && db_exists("SELECT 1 FROM subscriber WHERE suname=%Q",g.zLogin)
  ){
    /* This person is already signed up for email alerts.  Jump
    ** to the screen that lets them edit their alert preferences.
    ** Except, administrators can create subscriptions for others so
    ** do not jump for them.
    */
    if( g.perm.Admin ){
      /* Admins get a link to admin their own account, but they
      ** stay on this page so that they can create subscriptions
      ** for other people. */
      style_submenu_element("My Subscription","%R/alerts");
    }else{
      /* Everybody else jumps to the page to administer their own
      ** account only. */
      cgi_redirectf("%R/alerts");
      return;
    }
  }
  email_submenu_common();
  needCaptcha = !login_is_individual();
  if( P("submit")
   && cgi_csrf_safe(1)
   && subscribe_error_check(&eErr,&zErr,needCaptcha)
  ){
    /* A validated request for a new subscription has been received. */
    char ssub[20];
    const char *zEAddr = P("e");
    sqlite3_int64 id;   /* New subscriber Id */
    const char *zCode;  /* New subscriber code (in hex) */
    int nsub = 0;
    const char *suname = PT("suname");
    if( suname==0 && needCaptcha==0 && !g.perm.Admin ) suname = g.zLogin;
    if( suname && suname[0]==0 ) suname = 0;
    if( PB("sa") ) ssub[nsub++] = 'a';
    if( g.perm.Read && PB("sc") )    ssub[nsub++] = 'c';
    if( g.perm.RdForum && PB("sf") ) ssub[nsub++] = 'f';
    if( g.perm.RdTkt && PB("st") )   ssub[nsub++] = 't';
    if( g.perm.RdWiki && PB("sw") )  ssub[nsub++] = 'w';
    ssub[nsub] = 0;
    db_multi_exec(
      "INSERT INTO subscriber(semail,suname,"
      "  sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip)"
      "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q)",
      /* semail */    zEAddr,
      /* suname */    suname,
      /* sverified */ needCaptcha==0,
      /* sdigest */   PB("di"),
      /* ssub */      ssub,
      /* smip */      g.zIpAddr
    );
    id = db_last_insert_rowid();
    zCode = db_text(0,
         "SELECT hex(subscriberCode) FROM subscriber WHERE subscriberId=%lld",
         id);
    if( !needCaptcha ){
      /* The new subscription has been added on behalf of a logged-in user.
      ** No verification is required.  Jump immediately to /alerts page.
      */
      cgi_redirectf("%R/alerts/%s", zCode);
      return;
    }else{
      /* We need to send a verification email */
      Blob hdr, body;
      EmailSender *pSender = email_sender_new(0,0);
      blob_init(&hdr,0,0);
      blob_init(&body,0,0);
      blob_appendf(&hdr, "To: <%s>\n", zEAddr);
      blob_appendf(&hdr, "Subject: Subscription verification\n");
      email_append_confirmation_message(&body, zCode);
      email_send(pSender, &hdr, &body, 0);
      style_header("Email Alert Verification");
      if( pSender->zErr ){
        @ <h1>Internal Error</h1>
        @ <p>The following internal error was encountered while trying
        @ to send the confirmation email:
        @ <blockquote><pre>
        @ %h(pSender->zErr)
        @ </pre></blockquote>
      }else{
        @ <p>An email has been sent to "%h(zEAddr)". That email contains a
        @ hyperlink that you must click on in order to activate your
        @ subscription.</p>
      }
      email_sender_free(pSender);
      style_footer();
    }
    return;
  }
  style_header("Signup For Email Alerts");
  if( P("submit")==0 ){
    /* If this is the first visit to this page (if this HTTP request did not
    ** come from a prior Submit of the form) then default all of the
    ** subscription options to "on" */
    cgi_set_parameter_nocopy("sa","1",1);
    if( g.perm.Read )    cgi_set_parameter_nocopy("sc","1",1);
    if( g.perm.RdForum ) cgi_set_parameter_nocopy("sf","1",1);
    if( g.perm.RdTkt )   cgi_set_parameter_nocopy("st","1",1);
    if( g.perm.RdWiki )  cgi_set_parameter_nocopy("sw","1",1);
  }
  @ <p>To receive email notifications for changes to this
  @ repository, fill out the form below and press "Submit" button.</p>
  form_begin(0, "%R/subscribe");
  @ <table class="subscribe">
  @ <tr>
  @  <td class="form_label">Email&nbsp;Address:</td>
  @  <td><input type="text" name="e" value="%h(PD("e",""))" size="30"></td>
  if( eErr==1 ){
    @  <td><span class="loginError">&larr; %h(zErr)</span></td>
  }
  @ </tr>
  if( needCaptcha ){
    uSeed = captcha_seed();
    zDecoded = captcha_decode(uSeed);
    zCaptcha = captcha_render(zDecoded);
    @ <tr>
    @  <td class="form_label">Security Code:</td>
    @  <td><input type="text" name="captcha" value="" size="30">
    @  <input type="hidden" name="captchaseed" value="%u(uSeed)"></td>
    if( eErr==2 ){
      @  <td><span class="loginError">&larr; %h(zErr)</span></td>
    }
    @ </tr>
  }
  if( g.perm.Admin ){
    @ <tr>
    @  <td class="form_label">User:</td>
    @  <td><input type="text" name="suname" value="%h(PD("suname",g.zLogin))" \
    @  size="30"></td>
    if( eErr==3 ){
      @  <td><span class="loginError">&larr; %h(zErr)</span></td>
    }
    @ </tr>
  }
  @ <tr>
  @  <td class="form_label">Options:</td>
  @  <td><label><input type="checkbox" name="sa" %s(PCK("sa"))> \
  @  Announcements</label><br>
  if( g.perm.Read ){
    @  <label><input type="checkbox" name="sc" %s(PCK("sc"))> \
    @  Check-ins</label><br>
  }
  if( g.perm.RdForum ){
    @  <label><input type="checkbox" name="sf" %s(PCK("sf"))> \
    @  Forum Posts</label><br>
  }
  if( g.perm.RdTkt ){
    @  <label><input type="checkbox" name="st" %s(PCK("st"))> \
    @  Ticket changes</label><br>
  }
  if( g.perm.RdWiki ){
    @  <label><input type="checkbox" name="sw" %s(PCK("sw"))> \
    @  Wiki</label><br>
  }
  @  <label><input type="checkbox" name="di" %s(PCK("di"))> \
  @  Daily digest only</label><br>
  if( g.perm.Admin ){
    @  <label><input type="checkbox" name="vi" %s(PCK("vi"))> \
    @  Verified</label><br>
    @  <label><input type="checkbox" name="dnc" %s(PCK("dnc"))> \
    @  Do not call</label><br>
  }
  @ </td>
  @ </tr>
  @ <tr>
  @  <td></td>
  if( needCaptcha && !email_enabled() ){
    @  <td><input type="submit" name="submit" value="Submit" disabled>
    @  (Email current disabled)</td>
  }else{
    @  <td><input type="submit" name="submit" value="Submit"></td>
  }
  @ </tr>
  @ </table>
  if( needCaptcha ){
    @ <div class="captcha"><table class="captcha"><tr><td><pre>
    @ %h(zCaptcha)
    @ </pre>
    @ Enter the 8 characters above in the "Security Code" box
    @ </td></tr></table></div>
  }
  @ </form>
  fossil_free(zErr);
  style_footer();
}

/*
** Either shutdown or completely delete a subscription entry given
** by the hex value zName.  Then paint a webpage that explains that
** the entry has been removed.
*/
static void email_unsubscribe(const char *zName){
  char *zEmail;
  zEmail = db_text(0, "SELECT semail FROM subscriber"
                      " WHERE subscriberCode=hextoblob(%Q)", zName);
  if( zEmail==0 ){
    style_header("Unsubscribe Fail");
    @ <p>Unable to locate a subscriber with the requested key</p>
  }else{
    db_multi_exec(
      "DELETE FROM subscriber WHERE subscriberCode=hextoblob(%Q)",
      zName
    );
    style_header("Unsubscribed");
    @ <p>The "%h(zEmail)" email address has been delisted.
    @ All traces of that email address have been removed</p>
  }
  style_footer();
  return;
}

/*
** WEBPAGE: alerts
**
** Edit email alert and notification settings.
**
** The subscriber is identified in either of two ways:
**
**    (1)  The name= query parameter contains the subscriberCode.
**         
**    (2)  The user is logged into an account other than "nobody" or
**         "anonymous".  In that case the notification settings
**         associated with that account can be edited without needing
**         to know the subscriber code.
*/
void alerts_page(void){
  const char *zName = P("name");
  Stmt q;
  int sa, sc, sf, st, sw;
  int sdigest, sdonotcall, sverified;
  const char *ssub;
  const char *semail;
  const char *smip;
  const char *suname;
  const char *mtime;
  const char *sctime;
  int eErr = 0;
  char *zErr = 0;

  if( email_webpages_disabled() ) return;
  login_check_credentials();
  if( zName==0 && login_is_individual() ){
    zName = db_text(0, "SELECT hex(subscriberCode) FROM subscriber"
                       " WHERE suname=%Q", g.zLogin);
  }
  if( zName==0 || !validate16(zName, -1) ){
    cgi_redirect("subscribe");
    return;
  }
  email_submenu_common();
  if( P("submit")!=0 && cgi_csrf_safe(1) ){
    int sdonotcall = PB("sdonotcall");
    int sdigest = PB("sdigest");
    char ssub[10];
    int nsub = 0;
    if( PB("sa") )                   ssub[nsub++] = 'a';
    if( g.perm.Read && PB("sc") )    ssub[nsub++] = 'c';
    if( g.perm.RdForum && PB("sf") ) ssub[nsub++] = 'f';
    if( g.perm.RdTkt && PB("st") )   ssub[nsub++] = 't';
    if( g.perm.RdWiki && PB("sw") )  ssub[nsub++] = 'w';
    ssub[nsub] = 0;
    if( g.perm.Admin ){
      const char *suname = PT("suname");
      int sverified = PB("sverified");
      if( suname && suname[0]==0 ) suname = 0;
      db_multi_exec(
        "UPDATE subscriber SET"
        " sdonotcall=%d,"
        " sdigest=%d,"
        " ssub=%Q,"
        " mtime=strftime('%%s','now'),"
        " smip=%Q,"
        " suname=%Q,"
        " sverified=%d"
        " WHERE subscriberCode=hextoblob(%Q)",
        sdonotcall,
        sdigest,
        ssub,
        g.zIpAddr,
        suname,
        sverified,
        zName
      );
    }else{
      db_multi_exec(
        "UPDATE subscriber SET"
        " sdonotcall=%d,"
        " sdigest=%d,"
        " ssub=%Q,"
        " mtime=strftime('%%s','now'),"
        " smip=%Q"
        " WHERE subscriberCode=hextoblob(%Q)",
        sdonotcall,
        sdigest,
        ssub,
        g.zIpAddr,
        zName
      );
    }
  }
  if( P("delete")!=0 && cgi_csrf_safe(1) ){
    if( !PB("dodelete") ){
      eErr = 9;
      zErr = mprintf("Select this checkbox and press \"Unsubscribe\" to"
                     " unsubscribe");
    }else{
      email_unsubscribe(zName);
      return;
    }
  }
  db_prepare(&q,
    "SELECT"
    "  semail,"                       /* 0 */
    "  sverified,"                    /* 1 */
    "  sdonotcall,"                   /* 2 */
    "  sdigest,"                      /* 3 */
    "  ssub,"                         /* 4 */
    "  smip,"                         /* 5 */
    "  suname,"                       /* 6 */
    "  datetime(mtime,'unixepoch'),"  /* 7 */
    "  datetime(sctime,'unixepoch')"  /* 8 */
    " FROM subscriber WHERE subscriberCode=hextoblob(%Q)", zName);
  if( db_step(&q)!=SQLITE_ROW ){
    db_finalize(&q);
    cgi_redirect("subscribe");
    return;
  }
  style_header("Update Subscription");
  semail = db_column_text(&q, 0);
  sverified = db_column_int(&q, 1);
  sdonotcall = db_column_int(&q, 2);
  sdigest = db_column_int(&q, 3);
  ssub = db_column_text(&q, 4);
  sa = strchr(ssub,'a')!=0;
  sc = strchr(ssub,'c')!=0;
  sf = strchr(ssub,'f')!=0;
  st = strchr(ssub,'t')!=0;
  sw = strchr(ssub,'w')!=0;
  smip = db_column_text(&q, 5);
  suname = db_column_text(&q, 6);
  mtime = db_column_text(&q, 7);
  sctime = db_column_text(&q, 8);
  if( !g.perm.Admin && !sverified ){
    db_multi_exec(
      "UPDATE subscriber SET sverified=1 WHERE subscriberCode=hextoblob(%Q)",
      zName);
    @ <h1>Your email alert subscription has been verified!</h1>
    @ <p>Use the form below to update your subscription information.</p>
    @ <p>Hint:  Bookmark this page so that you can more easily update
    @ your subscription information in the future</p>
  }else{
    @ <p>Make changes to the email subscription shown below and
    @ press "Submit".</p>
  }
  form_begin(0, "%R/alerts");
  @ <input type="hidden" name="name" value="%h(zName)">
  @ <table class="subscribe">
  @ <tr>
  @  <td class="form_label">Email&nbsp;Address:</td>
  @  <td>%h(semail)</td>
  @ </tr>
  if( g.perm.Admin ){
    @ <tr>
    @  <td class='form_label'>Created:</td>
    @  <td>%h(sctime)</td>
    @ </tr>
    @ <tr>
    @  <td class='form_label'>Last Modified:</td>
    @  <td>%h(mtime)</td>
    @ </tr>
    @ <tr>
    @  <td class='form_label'>IP Address:</td>
    @  <td>%h(smip)</td>
    @ </tr>
    @ <tr>
    @  <td class="form_label">User:</td>
    @  <td><input type="text" name="suname" value="%h(suname?suname:"")" \
    @  size="30"></td>
    @ </tr>
  }
  @ <tr>
  @  <td class="form_label">Options:</td>
  @  <td><label><input type="checkbox" name="sa" %s(sa?"checked":"")>\
  @  Announcements</label><br>
  if( g.perm.Read ){
    @  <label><input type="checkbox" name="sc" %s(sc?"checked":"")>\
    @  Check-ins</label><br>
  }
  if( g.perm.RdForum ){
    @  <label><input type="checkbox" name="sf" %s(sf?"checked":"")>\
    @  Forum Posts</label><br>
  }
  if( g.perm.RdTkt ){
    @  <label><input type="checkbox" name="st" %s(st?"checked":"")>\
    @  Ticket changes</label><br>
  }
  if( g.perm.RdWiki ){
    @  <label><input type="checkbox" name="sw" %s(sw?"checked":"")>\
    @  Wiki</label><br>
  }
  @  <label><input type="checkbox" name="sdigest" %s(sdigest?"checked":"")>\
  @  Daily digest only</label><br>
  if( g.perm.Admin ){
    @  <label><input type="checkbox" name="sdonotcall" \
    @  %s(sdonotcall?"checked":"")> Do not call</label><br>
    @  <label><input type="checkbox" name="sverified" \
    @  %s(sverified?"checked":"")>\
    @  Verified</label><br>
  }
  @  <label><input type="checkbox" name="dodelete">
  @  Unsubscribe</label> \
  if( eErr==9 ){
    @ <span class="loginError">&larr; %h(zErr)</span>\
  }
  @ <br>
  @ </td></tr>
  @ <tr>
  @  <td></td>
  @  <td><input type="submit" name="submit" value="Submit">
  @  <input type="submit" name="delete" value="Unsubscribe">
  @ </tr>
  @ </table>
  @ </form>
  fossil_free(zErr);
  db_finalize(&q);
  style_footer();
}

/* This is the message that gets sent to describe how to change
** or modify a subscription
*/
static const char zUnsubMsg[] = 
@ To changes your subscription settings at %s visit this link:
@
@    %s/alerts/%s
@
@ To completely unsubscribe from %s, visit the following link:
@
@    %s/unsubscribe/%s
;

/*
** WEBPAGE: unsubscribe
**
** Users visit this page to be delisted from email alerts.
**
** If a valid subscriber code is supplied in the name= query parameter,
** then that subscriber is delisted.
**
** Otherwise, If the users is logged in, then they are redirected
** to the /alerts page where they have an unsubscribe button.
**
** Non-logged-in users with no name= query parameter are invited to enter
** an email address to which will be sent the unsubscribe link that
** contains the correct subscriber code.
*/
void unsubscribe_page(void){
  const char *zName = P("name");
  char *zErr = 0;
  int eErr = 0;
  unsigned int uSeed;
  const char *zDecoded;
  char *zCaptcha = 0;
  int dx;
  int bSubmit;
  const char *zEAddr;
  char *zCode = 0;

  /* If a valid subscriber code is supplied, then unsubscribe immediately.
  */
  if( zName 
   && db_exists("SELECT 1 FROM subscriber WHERE subscriberCode=hextoblob(%Q)",
                zName)
  ){
    email_unsubscribe(zName);
    return;
  }

  /* Logged in users are redirected to the /alerts page */
  login_check_credentials();
  if( login_is_individual() ){
    cgi_redirectf("%R/alerts");
    return;
  }

  zEAddr = PD("e","");
  dx = atoi(PD("dx","0"));
  bSubmit = P("submit")!=0 && P("e")!=0 && cgi_csrf_safe(1);
  if( bSubmit ){
    if( !captcha_is_correct(1) ){
      eErr = 2;
      zErr = mprintf("enter the security code shown below");
      bSubmit = 0;
    }
  }
  if( bSubmit ){
    zCode = db_text(0,"SELECT hex(subscriberCode) FROM subscriber"
                      " WHERE semail=%Q", zEAddr);
    if( zCode==0 ){
      eErr = 1;
      zErr = mprintf("not a valid email address");
      bSubmit = 0;
    }
  }
  if( bSubmit ){
    /* If we get this far, it means that a valid unsubscribe request has
    ** been submitted.  Send the appropriate email. */
    Blob hdr, body;
    EmailSender *pSender = email_sender_new(0,0);
    blob_init(&hdr,0,0);
    blob_init(&body,0,0);
    blob_appendf(&hdr, "To: <%s>\r\n", zEAddr);
    blob_appendf(&hdr, "Subject: Unsubscribe Instructions\r\n");
    blob_appendf(&body, zUnsubMsg/*works-like:"%s%s%s%s%s%s"*/,
                  g.zBaseURL, g.zBaseURL, zCode, g.zBaseURL, g.zBaseURL, zCode);
    email_send(pSender, &hdr, &body, 0);
    style_header("Unsubscribe Instructions Sent");
    if( pSender->zErr ){
      @ <h1>Internal Error</h1>
      @ <p>The following error was encountered while trying to send an
      @ email to %h(zEAddr):
      @ <blockquote><pre>
      @ %h(pSender->zErr)
      @ </pre></blockquote>
    }else{
      @ <p>An email has been sent to "%h(zEAddr)" that explains how to
      @ unsubscribe and/or modify your subscription settings</p>
    }
    email_sender_free(pSender);
    style_footer();
    return;
  }  

  /* Non-logged-in users have to enter an email address to which is
  ** sent a message containing the unsubscribe link.
  */
  style_header("Unsubscribe Request");
  @ <p>Fill out the form below to request an email message that will
  @ explain how to unsubscribe and/or change your subscription settings.</p>
  @
  form_begin(0, "%R/unsubscribe");
  @ <table class="subscribe">
  @ <tr>
  @  <td class="form_label">Email&nbsp;Address:</td>
  @  <td><input type="text" name="e" value="%h(zEAddr)" size="30"></td>
  if( eErr==1 ){
    @  <td><span class="loginError">&larr; %h(zErr)</span></td>
  }
  @ </tr>
  uSeed = captcha_seed();
  zDecoded = captcha_decode(uSeed);
  zCaptcha = captcha_render(zDecoded);
  @ <tr>
  @  <td class="form_label">Security Code:</td>
  @  <td><input type="text" name="captcha" value="" size="30">
  @  <input type="hidden" name="captchaseed" value="%u(uSeed)"></td>
  if( eErr==2 ){
    @  <td><span class="loginError">&larr; %h(zErr)</span></td>
  }
  @ </tr>
  @ <tr>
  @  <td class="form_label">Options:</td>
  @  <td><label><input type="radio" name="dx" value="0" %s(dx?"":"checked")>\
  @  Modify subscription</label><br>
  @  <label><input type="radio" name="dx" value="1" %s(dx?"checked":"")>\
  @  Completely unsubscribe</label><br>
  @ <tr>
  @  <td></td>
  @  <td><input type="submit" name="submit" value="Submit"></td>
  @ </tr>
  @ </table>
  @ <div class="captcha"><table class="captcha"><tr><td><pre>
  @ %h(zCaptcha)
  @ </pre>
  @ Enter the 8 characters above in the "Security Code" box
  @ </td></tr></table></div>
  @ </form>
  fossil_free(zErr);
  style_footer();
}

/*
** WEBPAGE: subscribers
**
** This page, accessible to administrators only,
** shows a list of email notification email addresses.
** Clicking on an email takes one to the /alerts page
** for that email where the delivery settings can be
** modified.
*/
void subscriber_list_page(void){
  Blob sql;
  Stmt q;
  sqlite3_int64 iNow;
  if( email_webpages_disabled() ) return;
  login_check_credentials();
  if( !g.perm.Admin ){
    login_needed(0);
    return;
  }
  email_submenu_common();
  style_header("Subscriber List");
  blob_init(&sql, 0, 0);
  blob_append_sql(&sql,
    "SELECT hex(subscriberCode),"          /* 0 */
    "       semail,"                       /* 1 */
    "       ssub,"                         /* 2 */
    "       suname,"                       /* 3 */
    "       sverified,"                    /* 4 */
    "       sdigest,"                      /* 5 */
    "       mtime,"                        /* 6 */
    "       date(sctime,'unixepoch')"      /* 7 */
    " FROM subscriber"
  );
  if( P("only")!=0 ){
    blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
    style_submenu_element("Show All","%R/subscribers");
  }
  blob_append_sql(&sql," ORDER BY mtime DESC");
  db_prepare_blob(&q, &sql);
  iNow = time(0);
  @ <table border='1' class='sortable' \
  @ data-init-sort='6' data-column-types='tttttKt'>
  @ <thead>
  @ <tr>
  @ <th>Email
  @ <th>Events
  @ <th>Digest-Only?
  @ <th>User
  @ <th>Verified?
  @ <th>Last change
  @ <th>Created
  @ </tr>
  @ </thead><tbody>
  while( db_step(&q)==SQLITE_ROW ){
    sqlite3_int64 iMtime = db_column_int64(&q, 6);
    double rAge = (iNow - iMtime)/86400.0;
    @ <tr>
    @ <td><a href='%R/alerts/%s(db_column_text(&q,0))'>\
    @ %h(db_column_text(&q,1))</a></td>
    @ <td>%h(db_column_text(&q,2))</td>
    @ <td>%s(db_column_int(&q,5)?"digest":"")</td>
    @ <td>%h(db_column_text(&q,3))</td>
    @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
    @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
    @ <td>%h(db_column_text(&q,7))</td>
    @ </tr>
  }
  @ </tbody></table>
  db_finalize(&q);
  style_table_sorter();
  style_footer();
}

#if LOCAL_INTERFACE
/*
** A single event that might appear in an alert is recorded as an
** instance of the following object.
*/
struct EmailEvent {
  int type;          /* 'c', 'f', 'm', 't', 'w' */
  int needMod;       /* Pending moderator approval */
  Blob hdr;          /* Header content, for forum entries */
  Blob txt;          /* Text description to appear in an alert */
  char *zFromName;   /* Human name of the sender */
  EmailEvent *pNext; /* Next in chronological order */
};
#endif

/*
** Free a linked list of EmailEvent objects
*/
void email_free_eventlist(EmailEvent *p){
  while( p ){
    EmailEvent *pNext = p->pNext;
    blob_reset(&p->txt);
    blob_reset(&p->hdr);
    fossil_free(p->zFromName);
    fossil_free(p);
    p = pNext;
  }
}

/*
** Compute and return a linked list of EmailEvent objects
** corresponding to the current content of the temp.wantalert
** table which should be defined as follows:
**
**     CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN);
*/
EmailEvent *email_compute_event_text(int *pnEvent, int doDigest){
  Stmt q;
  EmailEvent *p;
  EmailEvent anchor;
  EmailEvent *pLast;
  const char *zUrl = db_get("email-url","http://localhost:8080");
  const char *zFrom;
  const char *zSub;


  /* First do non-forum post events */
  db_prepare(&q,
    "SELECT"
    " blob.uuid,"                /* 0 */
    " datetime(event.mtime),"    /* 1 */
    " coalesce(ecomment,comment)"
    "  || ' (user: ' || coalesce(euser,user,'?')"
    "  || (SELECT case when length(x)>0 then ' tags: ' || x else '' end"
    "      FROM (SELECT group_concat(substr(tagname,5), ', ') AS x"
    "              FROM tag, tagxref"
    "             WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid"
    "               AND tagxref.rid=blob.rid AND tagxref.tagtype>0))"
    "  || ')' as comment,"       /* 2 */
    " wantalert.eventId,"        /* 3 */
    " wantalert.needMod"         /* 4 */
    " FROM temp.wantalert, event, blob"
    " WHERE blob.rid=event.objid"
    "   AND event.objid=substr(wantalert.eventId,2)+0"
    "   AND (%d OR eventId NOT GLOB 'f*')"
    " ORDER BY event.mtime",
    doDigest
  );
  memset(&anchor, 0, sizeof(anchor));
  pLast = &anchor;
  *pnEvent = 0;
  while( db_step(&q)==SQLITE_ROW ){
    const char *zType = "";
    p = fossil_malloc( sizeof(EmailEvent) );
    pLast->pNext = p;
    pLast = p;
    p->type = db_column_text(&q, 3)[0];
    p->needMod = db_column_int(&q, 4);
    p->zFromName = 0;
    p->pNext = 0;
    switch( p->type ){
      case 'c':  zType = "Check-In";        break;
      case 'f':  zType = "Forum post";      break;
      case 't':  zType = "Wiki Edit";       break;
      case 'w':  zType = "Ticket Change";   break;
    }
    blob_init(&p->hdr, 0, 0);
    blob_init(&p->txt, 0, 0);
    blob_appendf(&p->txt,"== %s %s ==\n%s\n%s/info/%.20s\n",
      db_column_text(&q,1),
      zType,
      db_column_text(&q,2),
      zUrl,
      db_column_text(&q,0)
    );
    if( p->needMod ){
      blob_appendf(&p->txt,
        "** Pending moderator approval (%s/modreq) **\n",
        zUrl
      );
    }
    (*pnEvent)++;
  }
  db_finalize(&q);

  /* Early-out if forumpost is not a table in this repository */
  if( !db_table_exists("repository","forumpost") ){
    return anchor.pNext;
  }

  /* For digests, the previous loop also handled forumposts already */
  if( doDigest ){
    return anchor.pNext;
  }

  /* If we reach this point, it means that forumposts exist and this
  ** is a normal email alert.  Construct full-text forum post alerts
  ** using a format that enables them to be sent as separate emails.
  */
  db_prepare(&q,
    "SELECT"
    " forumpost.fpid,"                                      /* 0 */
    " (SELECT uuid FROM blob WHERE rid=forumpost.fpid),"    /* 1 */
    " datetime(event.mtime),"                               /* 2 */
    " substr(comment,instr(comment,':')+2),"                /* 3 */
    " (SELECT uuid FROM blob WHERE rid=forumpost.firt),"    /* 4 */
    " wantalert.needMod,"                                   /* 5 */
    " coalesce(trim(substr(info,1,instr(info,'<')-1)),euser,user)"   /* 6 */
    " FROM temp.wantalert, event, forumpost"
    "      LEFT JOIN user ON (login=coalesce(euser,user))"
    " WHERE event.objid=substr(wantalert.eventId,2)+0"
    "   AND eventId GLOB 'f*'"
    "   AND forumpost.fpid=event.objid"
    " ORDER BY event.mtime"
  );
  zFrom = db_get("email-self",0);
  zSub = db_get("email-subname","");
  while( db_step(&q)==SQLITE_ROW ){
    Manifest *pPost = manifest_get(db_column_int(&q,0), CFTYPE_FORUM, 0);
    const char *zIrt;
    const char *zUuid;
    const char *zTitle;
    const char *z;
    if( pPost==0 ) continue;
    p = fossil_malloc( sizeof(EmailEvent) );
    pLast->pNext = p;
    pLast = p;
    p->type = 'f';
    p->needMod = db_column_int(&q, 5);
    z = db_column_text(&q,6);
    p->zFromName = z && z[0] ? fossil_strdup(z) : 0;
    p->pNext = 0;
    blob_init(&p->hdr, 0, 0);
    zUuid = db_column_text(&q, 1);
    zTitle = db_column_text(&q, 3);
    if( p->needMod ){
      blob_appendf(&p->hdr, "Subject: %s Pending Moderation: %s\r\n",
                   zSub, zTitle);
    }else{
      blob_appendf(&p->hdr, "Subject: %s %s\r\n", zSub, zTitle);
      blob_appendf(&p->hdr, "Message-Id: <%.32s@%s>\r\n", 
                   zUuid, email_hostname(zFrom));
      zIrt = db_column_text(&q, 4);
      if( zIrt && zIrt[0] ){
        blob_appendf(&p->hdr, "In-Reply-To: <%.32s@%s>\r\n",
                     zIrt, email_hostname(zFrom));
      }
    }
    blob_init(&p->txt, 0, 0);
    if( p->needMod ){
      blob_appendf(&p->txt,
        "** Pending moderator approval (%s/modreq) **\n",
        zUrl
      );
    }
    blob_appendf(&p->txt,
      "Forum post by %s on %s\n",
      pPost->zUser, db_column_text(&q, 2));
    blob_appendf(&p->txt, "%s/forumpost/%S\n\n", zUrl, zUuid);
    blob_append(&p->txt, pPost->zWiki, -1);
    manifest_destroy(pPost);
    (*pnEvent)++;
  }
  db_finalize(&q);

  return anchor.pNext;
}

/*
** Put a header on an alert email
*/
void email_header(Blob *pOut){
  blob_appendf(pOut,
    "This is an automated email reporting changes "
    "on Fossil repository %s (%s/timeline)\n",
    db_get("email-subname","(unknown)"),
    db_get("email-url","http://localhost:8080"));
}

/*
** Append the "unsubscribe" notification and other footer text to
** the end of an email alert being assemblied in pOut.
*/
void email_footer(Blob *pOut){
  blob_appendf(pOut, "\n-- \nTo unsubscribe: %s/unsubscribe\n",
     db_get("email-url","http://localhost:8080"));
}

/*
** COMMAND:  test-alert
**
** Usage: %fossil test-alert EVENTID ...
**
** Generate the text of an email alert for all of the EVENTIDs
** listed on the command-line.  Or if no events are listed on the
** command line, generate text for all events named in the
** pending_alert table.
**
** This command is intended for testing and debugging the logic
** that generates email alert text.
**
** Options:
**
**      --digest           Generate digest alert text
**      --needmod          Assume all events are pending moderator approval
*/
void test_alert_cmd(void){
  Blob out;
  int nEvent;
  int needMod;
  int doDigest;
  EmailEvent *pEvent, *p;

  doDigest = find_option("digest",0,0)!=0;
  needMod = find_option("needmod",0,0)!=0;
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_transaction();
  email_schema(0);
  db_multi_exec("CREATE TEMP TABLE wantalert(eventid TEXT, needMod BOOLEAN)");
  if( g.argc==2 ){
    db_multi_exec(
      "INSERT INTO wantalert(eventId,needMod)"
      " SELECT eventid, %d FROM pending_alert", needMod);
  }else{
    int i;
    for(i=2; i<g.argc; i++){
      db_multi_exec("INSERT INTO wantalert(eventId,needMod) VALUES(%Q,%d)",
           g.argv[i], needMod);
    }
  }
  blob_init(&out, 0, 0);
  email_header(&out);
  pEvent = email_compute_event_text(&nEvent, doDigest);
  for(p=pEvent; p; p=p->pNext){
    blob_append(&out, "\n", 1);
    if( blob_size(&p->hdr) ){
      blob_append(&out, blob_buffer(&p->hdr), blob_size(&p->hdr));
      blob_append(&out, "\n", 1);
    }
    blob_append(&out, blob_buffer(&p->txt), blob_size(&p->txt));
  }
  email_free_eventlist(pEvent);
  email_footer(&out);
  fossil_print("%s", blob_str(&out));
  blob_reset(&out);
  db_end_transaction(0);
}

/*
** COMMAND:  test-add-alerts
**
** Usage: %fossil test-add-alerts [OPTIONS] EVENTID ...
**
** Add one or more events to the pending_alert queue.  Use this
** command during testing to force email notifications for specific
** events.
**
** EVENTIDs are text.  The first character is 'c', 'f', 't', or 'w'
** for check-in, forum, ticket, or wiki.  The remaining text is a
** integer that references the EVENT.OBJID value for the event.
** Run /timeline?showid to see these OBJID values.
**
** Options:
**
**    --backoffice        Run email_backoffice() after all alerts have
**                        been added.  This will cause the alerts to be
**                        sent out with the SENDALERT_TRACE option.
**
**    --debug             Like --backoffice, but add the SENDALERT_STDOUT
**                        so that emails are printed to standard output
**                        rather than being sent.
**
**    --digest            Process emails using SENDALERT_DIGEST
*/
void test_add_alert_cmd(void){
  int i;
  int doAuto = find_option("backoffice",0,0)!=0;
  unsigned mFlags = 0;
  if( find_option("debug",0,0)!=0 ){
    doAuto = 1;
    mFlags = SENDALERT_STDOUT;
  }
  if( find_option("digest",0,0)!=0 ){
    mFlags |= SENDALERT_DIGEST;
  }
  db_find_and_open_repository(0, 0);
  verify_all_options();
  db_begin_write();
  email_schema(0);
  for(i=2; i<g.argc; i++){
    db_multi_exec("REPLACE INTO pending_alert(eventId) VALUES(%Q)", g.argv[i]);
  }
  db_end_transaction(0);
  if( doAuto ){
    email_backoffice(SENDALERT_TRACE|mFlags);
  }
}

#if INTERFACE
/*
** Flags for email_send_alerts()
*/
#define SENDALERT_DIGEST      0x0001    /* Send a digest */
#define SENDALERT_PRESERVE    0x0002    /* Do not mark the task as done */
#define SENDALERT_STDOUT      0x0004    /* Print emails instead of sending */
#define SENDALERT_TRACE       0x0008    /* Trace operation for debugging */

#endif /* INTERFACE */

/*
** Send alert emails to subscribers.
**
** This procedure is run by either the backoffice, or in response to the
** "fossil alerts send" command.  Details of operation are controlled by
** the flags parameter.
**
** Here is a summary of what happens:
**
**   (1) Create a TEMP table wantalert(eventId,needMod) and fill it with
**       all the events that we want to send alerts about.  The needMod
**       flags is set if and only if the event is still awaiting
**       moderator approval.  Events with the needMod flag are only
**       shown to users that have moderator privileges.
**
**   (2) Call email_compute_event_text() to compute a list of EmailEvent
**       objects that describe all events about which we want to send
**       alerts.
**
**   (3) Loop over all subscribers.  Compose and send one or more email
**       messages to each subscriber that describe the events for
**       which the subscriber has expressed interest and has
**       appropriate privileges.
**
**   (4) Update the pending_alerts table to indicate that alerts have been
**       sent.
**
** Update 2018-08-09:  Do step (3) before step (4).  Update the
** pending_alerts table *before* the emails are sent.  That way, if
** the process malfunctions or crashes, some notifications may never
** be sent.  But that is better than some recurring bug causing
** subscribers to be flooded with repeated notifications every 60
** seconds!
*/
void email_send_alerts(u32 flags){
  EmailEvent *pEvents, *p;
  int nEvent = 0;
  Stmt q;
  const char *zDigest = "false";
  Blob hdr, body;
  const char *zUrl;
  const char *zRepoName;
  const char *zFrom;
  const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
  EmailSender *pSender = 0;
  u32 senderFlags = 0;

  if( g.fSqlTrace ) fossil_trace("-- BEGIN email_send_alerts(%u)\n", flags);
  email_schema(0);
  if( !email_enabled() ) goto send_alerts_done;
  zUrl = db_get("email-url",0);
  if( zUrl==0 ) goto send_alerts_done;
  zRepoName = db_get("email-subname",0);
  if( zRepoName==0 ) goto send_alerts_done;
  zFrom = db_get("email-self",0);
  if( zFrom==0 ) goto send_alerts_done;
  if( flags & SENDALERT_TRACE ){
    senderFlags |= EMAIL_TRACE;
  }
  pSender = email_sender_new(zDest, senderFlags);

  /* Step (1):  Compute the alerts that need sending
  */
  db_multi_exec(
    "DROP TABLE IF EXISTS temp.wantalert;"
    "CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN, sentMod);"
  );
  if( flags & SENDALERT_DIGEST ){
    /* Unmoderated changes are never sent as part of a digest */
    db_multi_exec(
      "INSERT INTO wantalert(eventId,needMod)"
      " SELECT eventid, 0"
      "   FROM pending_alert"
      "  WHERE sentDigest IS FALSE"
      "    AND NOT EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2));"
    );
    zDigest = "true";
  }else{
    /* Immediate alerts might include events that are subject to
    ** moderator approval */
    db_multi_exec(
      "INSERT INTO wantalert(eventId,needMod,sentMod)"
      " SELECT eventid,"
      "        EXISTS(SELECT 1 FROM private WHERE rid=substr(eventid,2)),"
      "        sentMod"
      "   FROM pending_alert"
      "  WHERE sentSep IS FALSE;"
      "DELETE FROM wantalert WHERE needMod AND sentMod;"
    );
  }

  /* Step 2: compute EmailEvent objects for every notification that
  ** needs sending.
  */
  pEvents = email_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
  if( nEvent==0 ) goto send_alerts_done;

  /* Step 4a: Update the pending_alerts table to designate the
  ** alerts as having all been sent.  This is done *before* step (3)
  ** so that a crash will not cause alerts to be sent multiple times.
  ** Better a missed alert than being spammed with hundreds of alerts
  ** due to a bug.
  */
  if( (flags & SENDALERT_PRESERVE)==0 ){
    if( flags & SENDALERT_DIGEST ){
      db_multi_exec(
        "UPDATE pending_alert SET sentDigest=true"
        " WHERE eventid IN (SELECT eventid FROM wantalert);"
      );
    }else{
      db_multi_exec(
        "UPDATE pending_alert SET sentSep=true"
        " WHERE eventid IN (SELECT eventid FROM wantalert WHERE NOT needMod);"
        "UPDATE pending_alert SET sentMod=true"
        " WHERE eventid IN (SELECT eventid FROM wantalert WHERE needMod);"
      );
    }
  }

  /* Step 3: Loop over subscribers.  Send alerts
  */
  blob_init(&hdr, 0, 0);
  blob_init(&body, 0, 0);
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
     " ssub,"                 /* 2 */
     " fullcap(user.cap)"     /* 3 */
     " FROM subscriber LEFT JOIN user ON (login=suname)"
     " WHERE sverified AND NOT sdonotcall"
     "  AND sdigest IS %s",
     zDigest/*safe-for-%s*/
  );
  while( db_step(&q)==SQLITE_ROW ){
    const char *zCode = db_column_text(&q, 0);
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    const char *zCap = db_column_text(&q, 3);
    int nHit = 0;
    for(p=pEvents; p; p=p->pNext){
      if( strchr(zSub,p->type)==0 ) continue;
      if( p->needMod ){
        /* For events that require moderator approval, only send an alert
        ** if the recipient is a moderator for that type of event */
        char xType = '*';
        switch( p->type ){
          case 'f':  xType = '5';  break;
          case 't':  xType = 'q';  break;
          case 'w':  xType = 'l';  break;
        }
        if( strchr(zCap,xType)==0 ) continue;
      }else if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
        /* Setup and admin users can get any notification that does not
        ** require moderation */
      }else{
        /* Other users only see the alert if they have sufficient
        ** privilege to view the event itself */
        char xType = '*';
        switch( p->type ){
          case 'c':  xType = 'o';  break;
          case 'f':  xType = '2';  break;
          case 't':  xType = 'r';  break;
          case 'w':  xType = 'j';  break;
        }
        if( strchr(zCap,xType)==0 ) continue;
      }
      if( blob_size(&p->hdr)>0 ){
        /* This alert should be sent as a separate email */
        Blob fhdr, fbody;
        blob_init(&fhdr, 0, 0);
        blob_appendf(&fhdr, "To: <%s>\r\n", zEmail);
        blob_append(&fhdr, blob_buffer(&p->hdr), blob_size(&p->hdr));
        blob_init(&fbody, blob_buffer(&p->txt), blob_size(&p->txt));
        blob_appendf(&fbody, "\n-- \nSubscription info: %s/alerts/%s\n",
           zUrl, zCode);
        email_send(pSender,&fhdr,&fbody,p->zFromName);
        blob_reset(&fhdr);
        blob_reset(&fbody);
      }else{
        /* Events other than forum posts are gathered together into
        ** a single email message */
        if( nHit==0 ){
          blob_appendf(&hdr,"To: <%s>\r\n", zEmail);
          blob_appendf(&hdr,"Subject: %s activity alert\r\n", zRepoName);
          blob_appendf(&body,
            "This is an automated email sent by the Fossil repository "
            "at %s to report changes.\n",
            zUrl
          );
        }
        nHit++;
        blob_append(&body, "\n", 1);
        blob_append(&body, blob_buffer(&p->txt), blob_size(&p->txt));
      }
    }
    if( nHit==0 ) continue;
    blob_appendf(&body,"\n-- \nSubscription info: %s/alerts/%s\n",
         zUrl, zCode);
    email_send(pSender,&hdr,&body,0);
    blob_truncate(&hdr, 0);
    blob_truncate(&body, 0);
  }
  blob_reset(&hdr);
  blob_reset(&body);
  db_finalize(&q);
  email_free_eventlist(pEvents);

  /* Step 4b: Update the pending_alerts table to remove all of the
  ** alerts that have been completely sent.
  */
  db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");

send_alerts_done:
  email_sender_free(pSender);
  if( g.fSqlTrace ) fossil_trace("-- END email_send_alerts(%u)\n", flags);
}

/*
** Do backoffice processing for email notifications.  In other words,
** check to see if any email notifications need to occur, and then
** do them.
**
** This routine is intended to run in the background, after webpages.
**
** The mFlags option is zero or more of the SENDALERT_* flags.  Normally
** this flag is zero, but the test-set-alert command sets it to
** SENDALERT_TRACE.
*/
void email_backoffice(u32 mFlags){
  int iJulianDay;
  if( !email_tables_exist() ) return;
  email_send_alerts(mFlags);
  iJulianDay = db_int(0, "SELECT julianday('now')");
  if( iJulianDay>db_get_int("email-last-digest",0) ){
    db_set_int("email-last-digest",iJulianDay,0);
    email_send_alerts(SENDALERT_DIGEST|mFlags);
  }
}

/*
** WEBPAGE: contact_admin
**
** A web-form to send an email message to the repository administrator,
** or (with appropriate permissions) to anybody.
*/
void contact_admin_page(void){
  const char *zAdminEmail = db_get("email-admin",0);
  unsigned int uSeed = 0;
  const char *zDecoded;
  char *zCaptcha = 0;

  login_check_credentials();
  if( zAdminEmail==0 || zAdminEmail[0]==0 ){
    style_header("Outbound Email Disabled");
    @ <p>Outbound email is disabled on this repository
    style_footer();
    return;
  }
  if( P("submit")!=0 
   && P("subject")!=0
   && P("msg")!=0
   && P("from")!=0
   && cgi_csrf_safe(1)
   && captcha_is_correct(0)
  ){
    Blob hdr, body;
    EmailSender *pSender = email_sender_new(0,0);
    blob_init(&hdr, 0, 0);
    blob_appendf(&hdr, "To: <%s>\r\nSubject: %s administrator message\r\n",
                 zAdminEmail, db_get("email-subname","Fossil Repo"));
    blob_init(&body, 0, 0);
    blob_appendf(&body, "Message from [%s]\n", PT("from")/*safe-for-%s*/);
    blob_appendf(&body, "Subject: [%s]\n\n", PT("subject")/*safe-for-%s*/);
    blob_appendf(&body, "%s", PT("msg")/*safe-for-%s*/);
    email_send(pSender, &hdr, &body, 0);
    style_header("Message Sent");
    if( pSender->zErr ){
      @ <h1>Internal Error</h1>
      @ <p>The following error was reported by the system:
      @ <blockquote><pre>
      @ %h(pSender->zErr)
      @ </pre></blockquote>
    }else{
      @ <p>Your message has been sent to the repository administrator.
      @ Thank you for your input.</p>
    }
    email_sender_free(pSender);
    style_footer();
    return;
  }
  if( captcha_needed() ){
    uSeed = captcha_seed();
    zDecoded = captcha_decode(uSeed);
    zCaptcha = captcha_render(zDecoded);
  }
  style_header("Message To Administrator");
  form_begin(0, "%R/contact_admin");
  @ <p>Enter a message to the repository administrator below:</p>
  @ <table class="subscribe">
  if( zCaptcha ){
    @ <tr>
    @  <td class="form_label">Security&nbsp;Code:</td>
    @  <td><input type="text" name="captcha" value="" size="10">
    @  <input type="hidden" name="captchaseed" value="%u(uSeed)"></td>
    @ </tr>
  }
  @ <tr>
  @  <td class="form_label">Your&nbsp;Email&nbsp;Address:</td>
  @  <td><input type="text" name="from" value="%h(PT("from"))" size="30"></td>
  @ </tr>
  @ <tr>
  @  <td class="form_label">Subject:</td>
  @  <td><input type="text" name="subject" value="%h(PT("subject"))"\
  @  size="80"></td>
  @ </tr>
  @ <tr>
  @  <td class="form_label">Message:</td>
  @  <td><textarea name="msg" cols="80" rows="10" wrap="virtual">\
  @ %h(PT("msg"))</textarea>
  @ </tr>
  @ <tr>
  @   <td></td>
  @   <td><input type="submit" name="submit" value="Send Message">
  @ </tr>
  @ </table>
  if( zCaptcha ){
    @ <div class="captcha"><table class="captcha"><tr><td><pre>
    @ %h(zCaptcha)
    @ </pre>
    @ Enter the 8 characters above in the "Security Code" box
    @ </td></tr></table></div>
  }
  @ </form>
  style_footer();
}

/*
** Send an annoucement message described by query parameter.
** Permission to do this has already been verified.
*/
static char *email_send_announcement(void){
  EmailSender *pSender;
  char *zErr;
  const char *zTo = PT("to");
  char *zSubject = PT("subject");
  int bAll = PB("all");
  int bAA = PB("aa");
  const char *zSub = db_get("email-subname", "[Fossil Repo]");
  int bTest2 = fossil_strcmp(P("name"),"test2")==0;
  Blob hdr, body;
  blob_init(&body, 0, 0);
  blob_init(&hdr, 0, 0);
  blob_appendf(&body, "%s", PT("msg")/*safe-for-%s*/);
  pSender = email_sender_new(bTest2 ? "blob" : 0, 0);
  if( zTo[0] ){
    blob_appendf(&hdr, "To: <%s>\r\nSubject: %s %s\r\n", zTo, zSub, zSubject);
    email_send(pSender, &hdr, &body, 0);
  }
  if( bAll || bAA ){
    Stmt q;
    int nUsed = blob_size(&body);
    const char *zURL =  db_get("email-url",0);
    db_prepare(&q, "SELECT semail, hex(subscriberCode) FROM subscriber "
                   " WHERE sverified AND NOT sdonotcall %s",
                   bAll ? "" : " AND ssub LIKE '%a%'");
    while( db_step(&q)==SQLITE_ROW ){
      const char *zCode = db_column_text(&q, 1);
      zTo = db_column_text(&q, 0);
      blob_truncate(&hdr, 0);
      blob_appendf(&hdr, "To: <%s>\r\nSubject: %s %s\r\n", zTo, zSub, zSubject);
      if( zURL ){
        blob_truncate(&body, nUsed);
        blob_appendf(&body,"\n-- \nSubscription info: %s/alerts/%s\n",
           zURL, zCode);
      }
      email_send(pSender, &hdr, &body, 0);
    }
    db_finalize(&q);
  }
  if( bTest2 ){
    /* If the URL is /announce/test2 instead of just /announce, then no
    ** email is actually sent.  Instead, the text of the email that would
    ** have been sent is displayed in the result window. */
    @ <pre style='border: 2px solid blue; padding: 1ex'>
    @ %h(blob_str(&pSender->out))
    @ </pre>
  }
  zErr = pSender->zErr;
  pSender->zErr = 0;
  email_sender_free(pSender);
  return zErr;
}


/*
** WEBPAGE: announce
**
** A web-form, available to users with the "Send-Announcement" or "A"
** capability, that allows one to send announcements to whomever
** has subscribed to receive announcements.  The administrator can
** also send a message to an arbitrary email address and/or to all
** subscribers regardless of whether or not they have elected to
** receive announcements.
*/
void announce_page(void){
  login_check_credentials();
  if( !g.perm.Announce ){
    login_needed(0);
    return;
  }
  if( fossil_strcmp(P("name"),"test1")==0 ){
    /* Visit the /announce/test1 page to see the CGI variables */
    @ <p style='border: 1px solid black; padding: 1ex;'>
    cgi_print_all(0, 0);
    @ </p>
  }else
  if( P("submit")!=0 && cgi_csrf_safe(1) ){
    char *zErr = email_send_announcement();
    style_header("Announcement Sent");
    if( zErr ){
      @ <h1>Internal Error</h1>
      @ <p>The following error was reported by the system:
      @ <blockquote><pre>
      @ %h(zErr)
      @ </pre></blockquote>
    }else{
      @ <p>The announcement has been sent.</p>
    }
    style_footer();    
    return;
  }
  style_header("Send Announcement");
  @ <form method="POST">
  @ <table class="subscribe">
  if( g.perm.Admin ){
    int aa = PB("aa");
    int all = PB("all");
    const char *aack = aa ? "checked" : "";
    const char *allck = all ? "checked" : "";
    @ <tr>
    @  <td class="form_label">To:</td>
    @  <td><input type="text" name="to" value="%h(PT("to"))" size="30"><br>
    @  <label><input type="checkbox" name="aa" %s(aack)> \
    @  All "announcement" subscribers</label> \
    @  <a href="%R/subscribers?only=a" target="_blank">(list)</a><br>
    @  <label><input type="checkbox" name="all" %s(allck)> \
    @  All subscribers</label> \
    @  <a href="%R/subscribers" target="_blank">(list)</a><br></td>
    @ </tr>
  }
  @ <tr>
  @  <td class="form_label">Subject:</td>
  @  <td><input type="text" name="subject" value="%h(PT("subject"))"\
  @  size="80"></td>
  @ </tr>
  @ <tr>
  @  <td class="form_label">Message:</td>
  @  <td><textarea name="msg" cols="80" rows="10" wrap="virtual">\
  @ %h(PT("msg"))</textarea>
  @ </tr>
  @ <tr>
  @   <td></td>
  @   <td><input type="submit" name="submit" value="Send Message">
  @ </tr>
  @ </table>
  @ </form>
  style_footer();
}

/*
** The characters used for HTTP base64 encoding.
*/
static unsigned char zBase[] =
  "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";

/*
** Translate nData bytes of content from zData into
** ((nData+2)/3)*4) bytes of base64 encoded content and


** put the result in z64.  Add a zero-terminator at the end.
*/
int translateBase64(const char *zData, int nData, char *z64){

  int i, n;





  for(i=n=0; i+2<nData; i+=3){
    z64[n++] = zBase[ (zData[i]>>2) & 0x3f ];
    z64[n++] = zBase[ ((zData[i]<<4) & 0x30) | ((zData[i+1]>>4) & 0x0f) ];
    z64[n++] = zBase[ ((zData[i+1]<<2) & 0x3c) | ((zData[i+2]>>6) & 0x03) ];
    z64[n++] = zBase[ zData[i+2] & 0x3f ];
  }
  if( i+1<nData ){
    z64[n++] = zBase[ (zData[i]>>2) & 0x3f ];
    z64[n++] = zBase[ ((zData[i]<<4) & 0x30) | ((zData[i+1]>>4) & 0x0f) ];
    z64[n++] = zBase[ ((zData[i+1]<<2) & 0x3c) ];
    z64[n++] = '=';
  }else if( i<nData ){
    z64[n++] = zBase[ (zData[i]>>2) & 0x3f ];
    z64[n++] = zBase[ ((zData[i]<<4) & 0x30) ];
    z64[n++] = '=';
    z64[n++] = '=';
  }
  z64[n] = 0;
  return n;
}

/*
** Encode a string using a base-64 encoding.
** The encoding can be reversed using the <b>decode64</b> function.
**
** Space to hold the result comes from malloc().
*/
char *encode64(const char *zData, int nData){
  char *z64;
  if( nData<=0 ){
    nData = strlen(zData);
  }
  z64 = fossil_malloc( (nData*4)/3 + 8 );
  translateBase64(zData, nData, z64);
  return z64;
}

/*
** COMMAND: test-encode64
**
** Usage: %fossil test-encode64 STRING
*/
void test_encode64_cmd(void){
  char *z;
  int i;
  for(i=2; i<g.argc; i++){
    z = encode64(g.argv[i], -1);
    fossil_print("%s\n", z);
    free(z);
  }
}


/* Decode base64 text.  Write the output into zData.  The caller
** must ensure that zData is large enough.  It is ok for z64 and
** zData to be the same buffer.  In other words, it is ok to decode
** in-place.  A zero terminator is always placed at the end of zData.
*/
void decodeBase64(const char *z64, int *pnByte, char *zData){
  const unsigned char *zIn = (const unsigned char*)z64;
  int i, j, k;
  int x[4];
  static int isInit = 0;
  static signed char trans[256];

  if( !isInit ){
    for(i=0; i<256; i++){ trans[i] = -1; }
    for(i=0; zBase[i]; i++){ trans[zBase[i] & 0x7f] = i; }
    isInit = 1;
  }
  for(j=k=0; zIn[0]; zIn++){
    int v = trans[zIn[0]];
    if( v>=0 ){
      x[k++] = v;
      if( k==4 ){
        zData[j++] = ((x[0]<<2) & 0xfc) | ((x[1]>>4) & 0x03);
        zData[j++] = ((x[1]<<4) & 0xf0) | ((x[2]>>2) & 0x0f);
        zData[j++] = ((x[2]<<6) & 0xc0) | (x[3] & 0x3f);
        k = 0;
      }
    }
  }
  if( k>=2 ){
    zData[j++] = ((x[0]<<2) & 0xfc) | ((x[1]>>4) & 0x03);
    if( k==3 ){
      zData[j++] = ((x[1]<<4) & 0xf0) | ((x[2]>>2) & 0x0f);
    }
  }
  zData[j] = 0;
  *pnByte = j;
}


/*
** This function treats its input as a base-64 string and returns the
** decoded value of that string.  Characters of input that are not
** valid base-64 characters (such as spaces and newlines) are ignored.
**
** Space to hold the decoded string is obtained from malloc().
**
** The number of bytes decoded is returned in *pnByte
*/
char *decode64(const char *z64, int *pnByte){
  char *zData;
  int n64 = (int)strlen(z64);











  while( n64>0 && z64[n64-1]=='=' ) n64--;
  zData = fossil_malloc( (n64*3)/4 + 4 );





















  decodeBase64(z64, pnByte, zData);
  return zData;
}

/*
** COMMAND: test-decode64
**
** Usage: %fossil test-decode64 STRING
*/
void test_decode64_cmd(void){
  char *z;
  int i, n;
  for(i=2; i<g.argc; i++){
    z = decode64(g.argv[i], &n);
    fossil_print("%d: %s\n", n, z);
    fossil_free(z);
  }
}

/*
** The base-16 encoding using the following characters:
**
**         0123456789abcdef

/*
** Return true if the input string contains only valid base-16 digits.
** If any invalid characters appear in the string, return false.
*/
int validate16(const char *zIn, int nIn){
  int i;
  if( nIn<0 ) nIn = (int)strlen(zIn);
  for(i=0; i<nIn; i++, zIn++){
    if( zDecode[zIn[0]&0xff]>63 ){
      return zIn[0]==0;
    }
  }
  return 1;
}

/*
** The input string is a base16 value.  Convert it into its canonical
** form.  This means that digits are all lower case and that conversions
** like "l"->"1" and "O"->"0" occur.
*/
void canonical16(char *z, int n){
  while( *z && n-- ){
    *z = zEncode[zDecode[(*z)&0x7f]&0x1f];
    z++;
  }
}

/*
** Decode a string encoded using "quoted-printable".
**
**   (1)  "=" followed by two hex digits becomes a single
**        byte specified by the two digits
**
** The decoding is done in-place.
*/
void decodeQuotedPrintable(char *z, int *pnByte){
  int i, j, c;
  for(i=j=0; (c = z[i])!=0; i++){
    if( c=='=' ){
      if( z[i+1]!='\r' ){
        decode16((unsigned char*)&z[i+1], (unsigned char*)&z[j], 2);
        j++;
      }
      i += 2;
    }else{
      z[j++] = c;
    }
  }
  if( pnByte ) *pnByte = j;
  z[j] = 0;
}

/* Randomness used for XOR-ing by the obscure() and unobscure() routines */
static const unsigned char aObscurer[16] = {
    0xa7, 0x21, 0x31, 0xe3, 0x2a, 0x50, 0x2c, 0x86,
    0x4c, 0xa4, 0x52, 0x25, 0xff, 0x49, 0x35, 0x85
};

  if( strcmp(zIfNoneMatch,zETag)!=0 ) return;

  /* If we get this far, it means that the content has
  ** not changed and we can do a 304 reply */
  cgi_reset_content();
  cgi_set_status(304, "Not Modified");
  cgi_reply();
  db_close(0);
  fossil_exit(0);
}

/*
** Accept a new Last-Modified time.  This routine should be called by
** page generators that know a valid last-modified time.  This routine
** might generate a 304 Not Modified reply and exit(), never returning.
** Or, if not, it will cause a Last-Modified: header to be included in the
** reply.
*/
void etag_last_modified(sqlite3_int64 mtime){
  const char *zIfModifiedSince;
  sqlite3_int64 x;
  assert( iEtagMtime==0 );   /* Only call this routine once */
  assert( mtime>0 );         /* Only call with a valid mtime */
  iEtagMtime = mtime;

  /* Check to see the If-Modified-Since constraint is satisfied */
  zIfModifiedSince = P("HTTP_IF_MODIFIED_SINCE");
  if( zIfModifiedSince==0 ) return;
  x = cgi_rfc822_parsedate(zIfModifiedSince);
  if( x<mtime ) return;

#if 0  
  /* If the Fossil executable is more recent than If-Modified-Since,
  ** go ahead and regenerate the resource. */
  if( file_mtime(g.nameOfExe, ExtFILE)>x ) return;
#endif

  /* If we reach this point, it means that the resource has not changed
  ** and that we should generate a 304 Not Modified reply */
  cgi_reset_content();
  cgi_set_status(304, "Not Modified");
  cgi_reply();
  db_close(0);
  fossil_exit(0);
}

/* Return the ETag, if there is one.
*/
const char *etag_tag(void){
  return zETag;