/*
 * wc-queries-test.c -- test the evaluation of the wc Sqlite queries
 *
 * ====================================================================
 *    Licensed to the Apache Software Foundation (ASF) under one
 *    or more contributor license agreements.  See the NOTICE file
 *    distributed with this work for additional information
 *    regarding copyright ownership.  The ASF licenses this file
 *    to you under the Apache License, Version 2.0 (the
 *    "License"); you may not use this file except in compliance
 *    with the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing,
 *    software distributed under the License is distributed on an
 *    "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *    KIND, either express or implied.  See the License for the
 *    specific language governing permissions and limitations
 *    under the License.
 * ====================================================================
 */

#include "svn_pools.h"
#include "svn_hash.h"
#include "svn_ctype.h"
#include "private/svn_dep_compat.h"

#include "svn_private_config.h"

#include "../svn_test.h"

#ifdef SVN_SQLITE_INLINE
/* Import the sqlite3 API vtable from sqlite3wrapper.c */
#  define SQLITE_OMIT_DEPRECATED
#  include <sqlite3ext.h>
extern const sqlite3_api_routines *const svn_sqlite3__api_funcs;
extern int (*const svn_sqlite3__api_initialize)(void);
extern int (*const svn_sqlite3__api_config)(int, ...);
#  define sqlite3_api svn_sqlite3__api_funcs
#  define sqlite3_initialize svn_sqlite3__api_initialize
#  define sqlite3_config svn_sqlite3__api_config
#else
#  include <sqlite3.h>
#endif

#include "../../libsvn_wc/wc-queries.h"

WC_QUERIES_SQL_DECLARE_STATEMENTS(wc_queries);
WC_QUERIES_SQL_DECLARE_STATEMENT_INFO(wc_query_info);

/* The first query after the normal wc queries */
#define STMT_SCHEMA_FIRST STMT_CREATE_SCHEMA

#define SQLITE_ERR(x) do                                         \
{                                                                \
  int sqlite_err__temp = (x);                                    \
  if (sqlite_err__temp != SQLITE_OK)                             \
    return svn_error_createf(SVN_ERR_SQLITE_ERROR,               \
                             NULL, "sqlite: %s",                 \
                             sqlite3_errmsg(sdb));               \
} while (0)

/* Schema creation statements fail during preparing when the table
   already exists, and must be evaluated before testing the
   queries. Statements above STMT_SCHEMA_FIRST only need to be
   included here when they need to be evaluated before testing the
   statements */
static const int schema_statements[] =
{
  /* Usual tables */
  STMT_CREATE_SCHEMA,
  STMT_CREATE_NODES,
  STMT_CREATE_NODES_TRIGGERS,
  STMT_CREATE_EXTERNALS,
  STMT_INSTALL_SCHEMA_STATISTICS,
  /* Memory tables */
  STMT_CREATE_TARGETS_LIST,
  STMT_CREATE_CHANGELIST_LIST,
  STMT_CREATE_CHANGELIST_TRIGGER,
  STMT_CREATE_TARGET_PROP_CACHE,
  STMT_CREATE_REVERT_LIST,
  STMT_CREATE_DELETE_LIST,
  STMT_CREATE_UPDATE_MOVE_LIST,
  -1 /* final marker */
};

/* These statements currently trigger warnings. It would be nice if
   we could annotate these in wc-queries.sql */
static const int slow_statements[] =
{
  /* Operate on the entire WC */
  STMT_SELECT_ALL_NODES,                /* schema validation code */

  /* Updates all records for a repository (designed slow) */
  STMT_UPDATE_LOCK_REPOS_ID,

  /* Full temporary table read */
  STMT_INSERT_ACTUAL_EMPTIES,
  STMT_INSERT_ACTUAL_EMPTIES_FILES,
  STMT_SELECT_REVERT_LIST_RECURSIVE,
  STMT_SELECT_DELETE_LIST,
  STMT_SELECT_UPDATE_MOVE_LIST,

  /* Designed as slow to avoid penalty on other queries */
  STMT_SELECT_UNREFERENCED_PRISTINES,

  /* Slow, but just if foreign keys are enabled:
   * STMT_DELETE_PRISTINE_IF_UNREFERENCED,
   */
  STMT_HAVE_STAT1_TABLE, /* Queries sqlite_master which has no index */

  -1 /* final marker */
};

/* Statements that just read the first record from a table,
   using the primary key. Specialized as different sqlite
   versions produce different results */
static const int primary_key_statements[] =
{
  /* Is there a record? ### Can we somehow check for LIMIT 1,
     and primary key instead of adding a list? */
  STMT_LOOK_FOR_WORK,
  STMT_SELECT_WORK_ITEM,

  -1 /* final marker */
};

/* Helper function to determine if a statement is in a list */
static svn_boolean_t
in_list(const int list[], int stmt_idx)
{
  int i;

  for (i = 0; list[i] != -1; i++)
    {
      if (list[i] == stmt_idx)
        return TRUE;
    }
  return FALSE;
}

/* Helpers to determine if a statement is in a common list */
#define is_slow_statement(stmt_idx) in_list(slow_statements, stmt_idx)
#define is_schema_statement(stmt_idx) \
    ((stmt_idx >= STMT_SCHEMA_FIRST) || in_list(schema_statements, stmt_idx))


/* Create an in-memory db for evaluating queries */
static svn_error_t *
create_memory_db(sqlite3 **db,
                 apr_pool_t *pool)
{
  sqlite3 *sdb;
  int i;

  /* Create an in-memory raw database */
  SVN_TEST_ASSERT(sqlite3_initialize() == SQLITE_OK);
  SQLITE_ERR(sqlite3_open_v2("", &sdb,
                             SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                             NULL));

  /* Create schema */
  for (i = 0; schema_statements[i] != -1; i++)
    {
      SQLITE_ERR(sqlite3_exec(sdb, wc_queries[schema_statements[i]], NULL, NULL, NULL));
    }

  *db = sdb;
  return SVN_NO_ERROR;
}

/* Verify sqlite3 runtime version */
static svn_error_t *
test_sqlite_version(apr_pool_t *scratch_pool)
{
  printf("DBG: Using Sqlite %s\n", sqlite3_libversion());

  if (sqlite3_libversion_number() != SQLITE_VERSION_NUMBER)
    printf("DBG: Compiled against Sqlite %s\n", SQLITE_VERSION);

  if (sqlite3_libversion_number() < SQLITE_VERSION_NUMBER)
    return svn_error_createf(SVN_ERR_TEST_FAILED, NULL,
            "Compiled against Sqlite %s (at runtime we have Sqlite %s)",
            SQLITE_VERSION, sqlite3_libversion());

#if !SQLITE_VERSION_AT_LEAST(3, 7, 9)
  return svn_error_create(SVN_ERR_TEST_FAILED, NULL,
        "Sqlite upgrade recommended:\n"
        "****************************************************************\n"
        "*   Subversion needs at least SQLite 3.7.9 to work optimally   *\n"
        "*                                                              *\n"
        "* With older versions, at least some queries that are expected *\n"
        "* to be using an index are not. This makes some operations use *\n"
        "* every node in the working copy instead of just one.          *\n"
        "*                                                              *\n"
        "* While Subversion works correctly in this case, you may see   *\n"
        "* slowdowns of WELL MORE THAN 1000* in some cases!             *\n"
        "*                                                              *\n"
        "*                                                              *\n"
        "*                SQLITE UPGRADE RECOMMENDED                    *\n"
        "****************************************************************\n");
#else
  return SVN_NO_ERROR;
#endif
}

/* Parse all normal queries */
static svn_error_t *
test_parsable(apr_pool_t *scratch_pool)
{
  sqlite3 *sdb;
  int i;

  SVN_ERR(create_memory_db(&sdb, scratch_pool));

  for (i=0; i < STMT_SCHEMA_FIRST; i++)
    {
      sqlite3_stmt *stmt;
      const char *text = wc_queries[i];

      if (is_schema_statement(i))
        continue;

      /* Some of our statement texts contain multiple queries. We prepare
         them all. */
      while (*text != '\0')
        {
          const char *tail;
          int r = sqlite3_prepare_v2(sdb, text, -1, &stmt, &tail);

          if (r != SQLITE_OK)
            return svn_error_createf(SVN_ERR_SQLITE_ERROR, NULL,
                                     "Preparing %s failed: %s\n%s",
                                     wc_query_info[i][0],
                                     sqlite3_errmsg(sdb),
                                     text);

          SQLITE_ERR(sqlite3_finalize(stmt));

          /* Continue after the current statement */
          text = tail;
        }
    }

  SQLITE_ERR(sqlite3_close(sdb)); /* Close the DB if ok; otherwise leaked */

  return SVN_NO_ERROR;
}

/* Contains a parsed record from EXPLAIN QUERY PLAN */
struct explanation_item
{
  const char *operation;
  const char *table;
  const char *alias;
  svn_boolean_t scan;
  svn_boolean_t search;
  svn_boolean_t covered_by_index;
  svn_boolean_t primary_key;
  svn_boolean_t automatic_index;
  const char *index;
  const char *expressions;
  const char *expected;

  const char *compound_left;
  const char *compound_right;
  svn_boolean_t create_btree;

  int expression_vars;
  int expected_rows;
};

#define MATCH_TOKEN(x, y) (x && (strcmp(x, y) == 0))

/* Simple parser for the Sqlite textual explanation into an explanation_item.
   Writes "DBG:" lines when sqlite produces unexpected results. When no
   valid explanation_item can be parsed sets *PARSED_ITEM to NULL, otherwise
   to a valid result. */
static svn_error_t *
parse_explanation_item(struct explanation_item **parsed_item,
                       const char *text,
                       apr_pool_t *result_pool)
{
  struct explanation_item *item = apr_pcalloc(result_pool, sizeof(*item));
  char *token;
  char *last;
  char *tmp = apr_pstrdup(result_pool, text);
  const char *tmp_end = &tmp[strlen(tmp)];

  *parsed_item = NULL;

  item->operation = apr_strtok(tmp, " ", &last);

  if (!item->operation)
    {
      return SVN_NO_ERROR;
    }

  item->scan = MATCH_TOKEN(item->operation, "SCAN");

  if (item->scan || MATCH_TOKEN(item->operation, "SEARCH"))
    {
      item->search = TRUE; /* Search or scan */
      token = apr_strtok(NULL, " ", &last);

      if (MATCH_TOKEN(token, "TABLE"))
        {
          item->table = apr_strtok(NULL, " ", &last);
        }
      else if (MATCH_TOKEN(token, "SUBQUERY"))
        {
          item->table = apr_psprintf(result_pool, "SUBQUERY-%s",
                                     apr_strtok(NULL, " ", &last));
        }
      else
        {
          printf("DBG: Expected 'TABLE', got '%s' in '%s'\n", token, text);
          return SVN_NO_ERROR; /* Nothing to parse */
        }

      token = apr_strtok(NULL, " ", &last);

      /* Skip alias */
      if (MATCH_TOKEN(token, "AS"))
        {
          item->alias = apr_strtok(NULL, " ", &last);
          token = apr_strtok(NULL, " ", &last);
        }

      if (MATCH_TOKEN(token, "USING"))
        {
          token = apr_strtok(NULL, " ", &last);

          if (MATCH_TOKEN(token, "AUTOMATIC"))
            {
              /* Pain: A temporary index is created */
              item->automatic_index = TRUE;
              token = apr_strtok(NULL, " ", &last);
            }

          /* Handle COVERING */
          if (MATCH_TOKEN(token, "COVERING"))
            {
              /* Bonus: Query will be answered by just using the index */
              item->covered_by_index = TRUE;
              token = apr_strtok(NULL, " ", &last);
           }

          if (MATCH_TOKEN(token, "INDEX"))
            {
              item->index = apr_strtok(NULL, " ", &last);
            }
          else if (MATCH_TOKEN(token, "INTEGER"))
            {
              token = apr_strtok(NULL, " ", &last);
              if (!MATCH_TOKEN(token, "PRIMARY"))
                {
                  printf("DBG: Expected 'PRIMARY', got '%s' in '%s'\n",
                         token, text);
                  return SVN_NO_ERROR;
                }

              token = apr_strtok(NULL, " ", &last);
              if (!MATCH_TOKEN(token, "KEY"))
                {
                  printf("DBG: Expected 'KEY', got '%s' in '%s'\n",
                         token, text);
                  return SVN_NO_ERROR;
                }

              item->primary_key = TRUE;
            }
          else
            {
              printf("DBG: Expected 'INDEX' or 'PRIMARY', got '%s' in '%s'\n",
                     token, text);
              return SVN_NO_ERROR;
            }

          token = apr_strtok(NULL, " ", &last);
        }

      if (token && token[0] == '(' && token[1] != '~')
        {
          /* Undo the tokenization to switch parser rules */
          size_t token_len = strlen(token);

          if (token + token_len < tmp_end)
            token[token_len] = ' ';

          if (token[token_len] == '\0')
            last[-1] = ' ';

          token++; /* Skip the '(' */

          item->expressions = apr_strtok(token, ")", &last);
          token = apr_strtok(NULL, " ", &last);
        }

      if (token && *token == '(' && token[1] == '~')
        {
          /* Undo the tokenization to switch parser rules */
          size_t token_len = strlen(token);

          if (token + token_len < tmp_end)
            token[token_len] = ' ';

          if (token[token_len] == '\0')
            last[-1] = ' ';

          token += 2; /* Skip "(~" */

          item->expected = apr_strtok(token, ")", &last);
          token = apr_strtok(NULL, " ", &last);
        }

      if (token)
        {
          printf("DBG: Unexpected token '%s' in '%s'\n",
                 token, text);
          return SVN_NO_ERROR;
        }

      /* Parsing successful */
    }
  else if (MATCH_TOKEN(item->operation, "EXECUTE"))
    {
      /* Subquery handling */
      return SVN_NO_ERROR;
    }
  else if (MATCH_TOKEN(item->operation, "COMPOUND"))
    {
      /* Handling temporary table (E.g. UNION) */

      token = apr_strtok(NULL, " ", &last);
      if (!MATCH_TOKEN(token, "SUBQUERIES"))
        {
          printf("DBG: Expected 'SUBQUERIES', got '%s' in '%s'\n", token,
                 text);
          return SVN_NO_ERROR;
        }

      item->compound_left = apr_strtok(NULL, " ", &last);
      token = apr_strtok(NULL, " ", &last);

      if (!MATCH_TOKEN(token, "AND"))
        {
          printf("DBG: Expected 'AND', got '%s' in '%s'\n", token, text);
          return SVN_NO_ERROR;
        }

      item->compound_right = apr_strtok(NULL, " ", &last);

      token = apr_strtok(NULL, " ", &last);
      if (MATCH_TOKEN(token, "USING"))
        {
          token = apr_strtok(NULL, " ", &last);
          if (!MATCH_TOKEN(token, "TEMP"))
            {
              printf("DBG: Expected 'TEMP', got '%s' in '%s'\n", token, text);
            }
          token = apr_strtok(NULL, " ", &last);
          if (!MATCH_TOKEN(token, "B-TREE"))
            {
              printf("DBG: Expected 'B-TREE', got '%s' in '%s'\n", token,
                     text);
            }
          item->create_btree = TRUE;
        }
    }
  else if (MATCH_TOKEN(item->operation, "USE"))
    {
      /* Using a temporary table for ordering results */
      /* ### Need parsing */
      item->create_btree = TRUE;
    }
  else
    {
      printf("DBG: Unhandled sqlite operation '%s' in explanation\n", item->operation);
      return SVN_NO_ERROR;
    }

  if (item->expressions)
    {
      const char *p;

      for (p = item->expressions; *p; p++)
        {
          if (*p == '?')
            item->expression_vars++;
        }
    }
  if (item->expected)
    {
      item->expected_rows = atoi(item->expected);
    }

  *parsed_item = item;
  return SVN_NO_ERROR;
}

/* Sqlite has an SQLITE_OMIT_EXPLAIN compilation flag, which may make
   explain query just evaluate the query. Some older versions use a
   different number of columns (and different texts) for
   EXPLAIN query plan.

   If none of this is true set *SUPPORTED to TRUE, otherwise to FALSE */
static svn_error_t *
supported_explain_query_plan(svn_boolean_t *supported,
                             sqlite3 *sdb,
                             apr_pool_t *scratch_pool)
{
  sqlite3_stmt *stmt;
  int r;

  *supported = TRUE;

  r = sqlite3_prepare(sdb, "EXPLAIN QUERY PLAN SELECT 1",
                      -1, &stmt, NULL);

  if (r != SQLITE_OK)
    {
      *supported = FALSE;
      return SVN_NO_ERROR;
    }

  if (sqlite3_step(stmt) == SQLITE_ROW)
    {
      if (sqlite3_column_count(stmt) < 4)
        {
          *supported = FALSE;
          /* Fall through */
        }
    }

  SQLITE_ERR(sqlite3_reset(stmt));
  SQLITE_ERR(sqlite3_finalize(stmt));
  return SVN_NO_ERROR;
}


/* Returns TRUE if TABLE_NAME specifies a nodes table, which should be indexed
   by wc_id and either local_relpath or parent_relpath */
static svn_boolean_t
is_node_table(const char *table_name)
{
  return (apr_strnatcasecmp(table_name, "nodes") == 0
          || apr_strnatcasecmp(table_name, "actual_node") == 0
          || apr_strnatcasecmp(table_name, "externals") == 0
          || apr_strnatcasecmp(table_name, "lock") == 0
          || apr_strnatcasecmp(table_name, "wc_lock") == 0
          || FALSE);
}

/* Returns TRUE if TABLE specifies an intermediate result table, which is
   allowed to have table scans, etc. */
static svn_boolean_t
is_result_table(const char *table_name)
{
  return (apr_strnatcasecmp(table_name, "target_prop_cache") == 0
          || apr_strnatcasecmp(table_name, "changelist_list") == 0
          || FALSE);
}

static svn_error_t *
test_query_expectations(apr_pool_t *scratch_pool)
{
  sqlite3 *sdb;
  int i;
  apr_pool_t *iterpool = svn_pool_create(scratch_pool);
  svn_error_t *warnings = NULL;
  svn_boolean_t supports_query_info;

  SVN_ERR(create_memory_db(&sdb, scratch_pool));

  SVN_ERR(supported_explain_query_plan(&supports_query_info, sdb,
                                       scratch_pool));
  if (!supports_query_info)
    {
      SQLITE_ERR(sqlite3_close(sdb));
      return svn_error_create(SVN_ERR_TEST_SKIPPED, NULL,
                              "Sqlite doesn't support EXPLAIN QUERY PLAN");
    }

  for (i=0; i < STMT_SCHEMA_FIRST; i++)
    {
      sqlite3_stmt *stmt;
      const char *tail;
      int r;
      svn_boolean_t warned = FALSE;
      apr_array_header_t *rows = NULL;

      if (is_schema_statement(i))
        continue;

      /* Prepare statement to find if it is a single statement. */
      r = sqlite3_prepare_v2(sdb, wc_queries[i], -1, &stmt, &tail);

      if (r != SQLITE_OK)
        continue; /* Parse failure is already reported by 'test_parable' */

      SQLITE_ERR(sqlite3_finalize(stmt));
      if (tail[0] != '\0')
        continue; /* Multi-queries are currently not testable */

      svn_pool_clear(iterpool);

      r = sqlite3_prepare_v2(sdb,
                             apr_pstrcat(iterpool,
                                         "EXPLAIN QUERY PLAN ",
                                         wc_queries[i],
                                         SVN_VA_NULL),
                             -1, &stmt, &tail);

      if (r != SQLITE_OK)
        continue; /* EXPLAIN not enabled or doesn't support this query */

      while (SQLITE_ROW == (r = sqlite3_step(stmt)))
        {
          /*int iSelectid;
          int iOrder;
          int iFrom;*/
          const unsigned char *zDetail;
          char *detail;
          struct explanation_item *item;

          /* ### The following code is correct for current Sqlite versions
             ### (tested with 3.7.x), but the EXPLAIN QUERY PLAN output
             ### is not guaranteed to be stable for future versions. */

          /* Names as in Sqlite documentation */
          /*iSelectid = sqlite3_column_int(stmt, 0);
          iOrder = sqlite3_column_int(stmt, 1);
          iFrom = sqlite3_column_int(stmt, 2);*/
          zDetail = sqlite3_column_text(stmt, 3);

          if (! zDetail)
            continue;

          if (!rows)
            rows = apr_array_make(iterpool, 10, sizeof(const char*));

          detail = apr_pstrdup(iterpool, (const char*)zDetail);

          APR_ARRAY_PUSH(rows, const char *) = detail;

          SVN_ERR(parse_explanation_item(&item, detail, iterpool));

          if (!item)
            continue; /* Not parsable or not interesting */

          if (item->search
              && item->automatic_index)
            {
              warned = TRUE;
              if (!is_slow_statement(i))
                {
                  warnings = svn_error_createf(SVN_ERR_TEST_FAILED, warnings,
                                "%s: "
                                "Creates a temporary index: %s\n",
                                wc_query_info[i][0], wc_queries[i]);
                }
            }
          else if (item->search && item->primary_key)
            {
              /* Nice */
            }
          else if (item->search
                   && ((item->expression_vars < 2 && is_node_table(item->table))
                       || (item->expression_vars < 1))
                   && !is_result_table(item->table))
            {
              if (in_list(primary_key_statements, i))
                {
                  /* Reported as primary key index usage in Sqlite 3.7,
                     as table scan in 3.8+, while the execution plan is
                     identical: read first record from table */
                }
              else if (!is_slow_statement(i))
                {
                  warned = TRUE;
                  warnings = svn_error_createf(SVN_ERR_TEST_FAILED, warnings,
                                "%s: "
                                "Uses %s with only %d index component: (%s)\n%s",
                                wc_query_info[i][0], item->table,
                                item->expression_vars, item->expressions,
                                wc_queries[i]);
                }
              else
                warned = TRUE;
            }
          else if (item->search && !item->index)
            {
              warned = TRUE;
              if (!is_slow_statement(i))
                warnings = svn_error_createf(SVN_ERR_TEST_FAILED, warnings,
                                "%s: "
                                "Query on %s doesn't use an index:\n%s",
                                wc_query_info[i][0], item->table, wc_queries[i]);
            }
          else if (item->scan && !is_result_table(item->table))
            {
              warned = TRUE;
              if (!is_slow_statement(i))
                warnings = svn_error_createf(SVN_ERR_TEST_FAILED, warnings,
                                "Query %s: "
                                "Performs scan on %s:\n%s",
                                wc_query_info[i][0], item->table, wc_queries[i]);
            }
          else if (item->create_btree)
            {
              warned = TRUE;
              if (!is_slow_statement(i))
                warnings = svn_error_createf(SVN_ERR_TEST_FAILED, warnings,
                                "Query %s: Creates a temporary B-TREE:\n%s",
                                wc_query_info[i][0], wc_queries[i]);
            }
        }
      SQLITE_ERR(sqlite3_reset(stmt));
      SQLITE_ERR(sqlite3_finalize(stmt));

      if (!warned && is_slow_statement(i))
        {
          printf("DBG: Expected %s to be reported as slow, but it wasn't\n",
                 wc_query_info[i][0]);
        }

      if (rows && warned != is_slow_statement(i))
        {
          int w;
          svn_error_t *info = NULL;
          for (w = rows->nelts-1; w >= 0; w--)
            {
              if (warned)
                info = svn_error_createf(SVN_ERR_SQLITE_CONSTRAINT, info,
                                         "|%s", APR_ARRAY_IDX(rows, w,
                                                              const char*));
              else
                printf("|%s\n", APR_ARRAY_IDX(rows, w, const char*));
            }

          warnings = svn_error_compose_create(warnings, info);
        }
    }
  SQLITE_ERR(sqlite3_close(sdb)); /* Close the DB if ok; otherwise leaked */

  return warnings;
}

static svn_error_t *
test_query_duplicates(apr_pool_t *scratch_pool)
{
  sqlite3 *sdb;
  int i;
  apr_pool_t *iterpool = svn_pool_create(scratch_pool);
  svn_error_t *warnings = NULL;
  svn_boolean_t supports_query_info;
  apr_hash_t *sha_to_query = apr_hash_make(scratch_pool);

  SVN_ERR(create_memory_db(&sdb, scratch_pool));

  SVN_ERR(supported_explain_query_plan(&supports_query_info, sdb,
      scratch_pool));
  if (!supports_query_info)
    {
      SQLITE_ERR(sqlite3_close(sdb));
      return svn_error_create(SVN_ERR_TEST_SKIPPED, NULL,
          "Sqlite doesn't support EXPLAIN QUERY PLAN");
    }

  for (i = 0; i < STMT_SCHEMA_FIRST; i++)
    {
      sqlite3_stmt *stmt;
      const char *tail;
      int r;
      svn_stringbuf_t *result;
      svn_checksum_t *checksum;

      if (is_schema_statement(i))
        continue;

      /* Prepare statement to find if it is a single statement. */
      r = sqlite3_prepare_v2(sdb, wc_queries[i], -1, &stmt, &tail);

      if (r != SQLITE_OK)
        continue; /* Parse failure is already reported by 'test_parable' */

      SQLITE_ERR(sqlite3_finalize(stmt));
      if (tail[0] != '\0')
        continue; /* Multi-queries are currently not testable */

      svn_pool_clear(iterpool);

      r = sqlite3_prepare_v2(sdb,
                             apr_pstrcat(iterpool,
                             "EXPLAIN ",
                             wc_queries[i],
                             SVN_VA_NULL),
                             -1, &stmt, &tail);

      if (r != SQLITE_OK)
          continue; /* EXPLAIN not enabled or doesn't support this query */

      result = svn_stringbuf_create_empty(iterpool);

      while (SQLITE_ROW == (r = sqlite3_step(stmt)))
        {
          int col;

          for (col = 0; col < sqlite3_column_count(stmt); col++)
            {
              const char *txt = (const char*)sqlite3_column_text(stmt, col);
              if (txt)
                  svn_stringbuf_appendcstr(result, txt);

              svn_stringbuf_appendcstr(result, "|");
            }

          svn_stringbuf_appendcstr(result, "\n");
        }

      SQLITE_ERR(sqlite3_reset(stmt));
      SQLITE_ERR(sqlite3_finalize(stmt));

      SVN_ERR(svn_checksum(&checksum, svn_checksum_sha1,
                           result->data, result->len,
                           iterpool));

      {
        const char *hex = svn_checksum_to_cstring(checksum, scratch_pool);
        const char *other;

        other = svn_hash_gets(sha_to_query, hex);
        if (other)
          {
            warnings = svn_error_createf(SVN_ERR_TEST_FAILED, warnings,
                              "Query %s has an identical execution plan as %s",
                              wc_query_info[i][0], other);
          }
        else
          svn_hash_sets(sha_to_query, hex, wc_query_info[i][0]);
      }
    }
  SQLITE_ERR(sqlite3_close(sdb)); /* Close the DB if ok; otherwise leaked */

  return warnings;
}

/* Helper to verify a bit of data in the sqlite3 statistics */
static int
parse_stat_data(const char *stat)
{
  int n = 0;
  apr_int64_t last = APR_INT64_MAX;
  while (*stat)
    {
      apr_int64_t v;
      char *next;

      if (*stat < '0' || *stat > '9')
        return -2;

      errno = 0;
      v = apr_strtoi64(stat, &next, 10);

      /* All numbers specify the average number of rows
         with the same values in all columns left of it,
         so the value must be >= 1 and lower than or equal
         to all previous seen numbers */
      if (v <= 0 || (v > last) || (errno != 0))
        return -1;

      last = v;

      n++;
      stat = next;

      if (*stat == ' ')
        stat++;
    }

  return n;
}

static svn_error_t *
test_schema_statistics(apr_pool_t *scratch_pool)
{
  sqlite3 *sdb;
  sqlite3_stmt *stmt;

  SVN_ERR(create_memory_db(&sdb, scratch_pool));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "CREATE TABLE shadow_stat1(tbl TEXT, idx TEXT, stat TEXT)",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO shadow_stat1 (tbl, idx, stat) "
                   "SELECT tbl, idx, stat FROM sqlite_stat1",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "DROP TABLE sqlite_stat1",
                   NULL, NULL, NULL));

  /* Insert statement to give index at least 1 record */
  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO nodes (wc_id, local_relpath, op_depth,"
                   "                   presence, kind) "
                   "VALUES (1, '', 0, 'normal', 'dir')",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO actual_node (wc_id, local_relpath) "
                   "VALUES (1, '')",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO lock (repos_id, repos_relpath, lock_token) "
                   "VALUES (1, '', '')",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO EXTERNALS (wc_id, local_relpath,"
                   "                       parent_relpath, repos_id,"
                   "                       presence, kind, def_local_relpath,"
                   "                       def_repos_relpath) "
                   "VALUES (1, 'subdir', '', 1, 'normal', 'dir', '', '')",
                   NULL, NULL, NULL));

  /* These are currently not necessary for query optimization, but it's better
     to tell Sqlite how we intend to use this table anyway */
  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO wc_lock (wc_id, local_dir_relpath) "
                   "VALUES (1, '')",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "INSERT INTO WORK_QUEUE (work) "
                   "VALUES ('')",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_exec(sdb,
                   "ANALYZE",
                   NULL, NULL, NULL));

  SQLITE_ERR(
      sqlite3_prepare(sdb, "SELECT s.tbl, s.idx, s.stat, r.stat "
                           "FROM shadow_stat1 s "
                           "LEFT JOIN sqlite_stat1 r ON "
                                "s.tbl=r.tbl and s.idx=r.idx",
                      -1, &stmt, NULL));

  while (sqlite3_step(stmt) == SQLITE_ROW)
    {
      const char *wc_stat       = (const char*)sqlite3_column_text(stmt, 2);
      const char *sqlite_stat   = (const char*)sqlite3_column_text(stmt, 3);

      if (! sqlite_stat)
        {
          return svn_error_createf(SVN_ERR_TEST_FAILED, NULL,
                                   "Schema statistic failure:"
                                   " Refering to unknown index '%s' on '%s'",
                                   sqlite3_column_text(stmt, 1),
                                   sqlite3_column_text(stmt, 0));
        }

      if (parse_stat_data(wc_stat) != parse_stat_data(sqlite_stat))
        {
          return svn_error_createf(SVN_ERR_TEST_FAILED, NULL,
                                   "Schema statistic failure:"
                                   " Column mismatch for '%s' on '%s'",
                                   sqlite3_column_text(stmt, 1),
                                   sqlite3_column_text(stmt, 0));
        }
    }

  SQLITE_ERR(sqlite3_reset(stmt));
  SQLITE_ERR(sqlite3_finalize(stmt));

  SQLITE_ERR(sqlite3_close(sdb)); /* Close the DB if ok; otherwise leaked */

  return SVN_NO_ERROR;
}

/* An SQLite application defined function that allows SQL queries to
   use "relpath_depth(local_relpath)".  */
static void relpath_depth_sqlite(sqlite3_context* context,
                                 int argc,
                                 sqlite3_value* values[])
{
  SVN_ERR_MALFUNCTION_NO_RETURN(); /* STUB! */
}

/* Parse all verify/check queries */
static svn_error_t *
test_verify_parsable(apr_pool_t *scratch_pool)
{
  sqlite3 *sdb;
  int i;

  SVN_ERR(create_memory_db(&sdb, scratch_pool));

  SQLITE_ERR(sqlite3_create_function(sdb, "relpath_depth", 1, SQLITE_ANY, NULL,
                                     relpath_depth_sqlite, NULL, NULL));

  for (i=STMT_VERIFICATION_TRIGGERS; wc_queries[i]; i++)
    {
      sqlite3_stmt *stmt;
      const char *text = wc_queries[i];

      /* Some of our statement texts contain multiple queries. We prepare
         them all. */
      while (*text != '\0')
        {
          const char *tail;
          int r = sqlite3_prepare_v2(sdb, text, -1, &stmt, &tail);

          if (r != SQLITE_OK)
            return svn_error_createf(SVN_ERR_SQLITE_ERROR, NULL,
                                     "Preparing %s failed: %s\n%s",
                                     wc_query_info[i][0],
                                     sqlite3_errmsg(sdb),
                                     text);

          SQLITE_ERR(sqlite3_finalize(stmt));

          /* Continue after the current statement */
          text = tail;
        }
    }

  SQLITE_ERR(sqlite3_close(sdb)); /* Close the DB if ok; otherwise leaked */

  return SVN_NO_ERROR;
}


static int max_threads = 1;

static struct svn_test_descriptor_t test_funcs[] =
  {
    SVN_TEST_NULL,
    SVN_TEST_PASS2(test_sqlite_version,
                   "sqlite up-to-date"),
    SVN_TEST_PASS2(test_parsable,
                   "queries are parsable"),
    SVN_TEST_PASS2(test_query_expectations,
                   "test query expectations"),
    SVN_TEST_PASS2(test_query_duplicates,
                   "test query duplicates"),
    SVN_TEST_PASS2(test_schema_statistics,
                   "test schema statistics"),
    SVN_TEST_PASS2(test_verify_parsable,
                   "verify queries are parsable"),
    SVN_TEST_NULL
  };

SVN_TEST_MAIN