sqlite3_test.cpp

#include <chrono>
#include <iostream>
#include <random>

#include <sqlite3.h>

int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, bool isSave) {
  int rc;                  /* Function return code */
  sqlite3 *pFile;          /* Database connection opened on zFilename */
  sqlite3_backup *pBackup; /* Backup object used to copy data */
  sqlite3 *pTo;            /* Database to copy to (pFile or pInMemory) */
  sqlite3 *pFrom;          /* Database to copy from (pFile or pInMemory) */
  rc = sqlite3_open(zFilename, &pFile);
  if (rc == SQLITE_OK) {
    pFrom = (isSave ? pInMemory : pFile);
    pTo = (isSave ? pFile : pInMemory);
    pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
    if (pBackup) {
      (void)sqlite3_backup_step(pBackup, -1);
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pTo);
  }

  (void)sqlite3_close(pFile);
  return rc;
}

int main() {
  constexpr auto range = 1000;
  constexpr auto data = 100000;
  constexpr auto round = 1000000;
  std::random_device rd;
  std::mt19937 eng(rd());
  std::uniform_int_distribution<> distr(0, range);
  sqlite3 *db;
  sqlite3_stmt *insert, *stmt;
  char const *err = nullptr;
  sqlite3_open(":memory:", &db);
  loadOrSaveDb(db, "test.db", false);
  sqlite3_exec(db, "PRAGMA journal_mode = WAL", nullptr, nullptr, nullptr);
  sqlite3_exec(db, "PRAGMA synchronous = OFF", nullptr, nullptr, nullptr);
  sqlite3_exec(db, "DROP TABLE rt", nullptr, nullptr, nullptr);
  sqlite3_exec(db,
               "CREATE VIRTUAL TABLE rt USING rtree (id INTEGER PRIMARY KEY, "
               "minX, maxX, minY, maxY)",
               nullptr, nullptr, nullptr);
  sqlite3_prepare(db,
                  "INSERT INTO rt (minX, maxX, minY, maxY) VALUES (?, ?, ?, ?)",
                  -1, &insert, &err);
  sqlite3_exec(db, "BEGIN", nullptr, nullptr, nullptr);
  for (int i = 0; i < data; i++) {
    auto bx = distr(eng) - range / 2;
    auto by = distr(eng) - range / 2;
    sqlite3_bind_int(insert, 1, bx);
    sqlite3_bind_int(insert, 2, bx + distr(eng));
    sqlite3_bind_int(insert, 3, by);
    sqlite3_bind_int(insert, 4, by + distr(eng));
    if (sqlite3_step(insert) != SQLITE_DONE) {
      std::cerr << "err" << std::endl;
      return 0;
    }
    sqlite3_clear_bindings(insert);
    sqlite3_reset(insert);
  }
  sqlite3_exec(db, "END", nullptr, nullptr, nullptr);
  sqlite3_prepare(db,
                  "SELECT id FROM rt WHERE ?1 > minX AND ?1 < maxX AND ?2 > "
                  "minY AND ?2 < maxY LIMIT 1",
                  -1, &stmt, nullptr);

  std::cout << "start!" << std::endl;

  int count = 0;

  auto begin = std::chrono::steady_clock::now();

  for (int i = 0; i < round; i++) {
    sqlite3_bind_int(stmt, 1, distr(eng));
    sqlite3_bind_int(stmt, 2, distr(eng));
  loop:
    switch (sqlite3_step(stmt)) {
    case SQLITE_DONE:
      break;
    case SQLITE_ROW:
      count++;
      goto loop;
      break;
    default:
      std::cerr << "err" << std::endl;
      return 0;
    }
    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);
  }

  auto end = std::chrono::steady_clock::now();

  std::cout << "round: " << round << std::endl
            << "time: "
            << std::chrono::duration_cast<std::chrono::milliseconds>(end -
                                                                     begin)
                   .count()
            << "ms" << std::endl
            << "hit count: " << count << std::endl;

  loadOrSaveDb(db, "test.db", true);
  sqlite3_close(db);
}