text: /*
   * 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_ctype.h"
  #include "private/svn_dep_compat.h"
  
  #include "svn_private_config.h"
  
  #include "../svn_test.h"
  
  #ifdef SVN_SQLITE_INLINE
  /* Include sqlite3 inline, making all symbols private. */
    #define SQLITE_API static
    #ifdef __APPLE__
      #include <Availability.h>
      #if __MAC_OS_X_VERSION_MIN_REQUIRED < 1060
        /* <libkern/OSAtomic.h> is included on OS X by sqlite3.c, and
           on old systems (Leopard or older), it cannot be compiled
           with -std=c89 because it uses inline. This is a work-around. */
        #define inline __inline__
        #include <libkern/OSAtomic.h>
        #undef inline
      #endif
    #endif
    #include <sqlite3.c>
  #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)   \
  {                                                                \
    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_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_version);
  
    if (sqlite3_libversion_number() != SQLITE_VERSION_NUMBER)
      printf("DBG: Compiled against Sqlite %s", 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_version);
  
  #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"))
          {
            printf("DBG: Expected 'TABLE', got '%s' in '%s'\n", token, text);
            return SVN_NO_ERROR; /* Nothing to parse */
          }
  
        item->table = apr_strtok(NULL, " ", &last);
  
        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 successfull */
      }
    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],
                                           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;
  }
  
  /* 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));
  
    /* 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;
  }
  
  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_schema_statistics,
                     "test schema statistics"),
      SVN_TEST_NULL
    };
  