Support for Cache Invalidation

[ScottFries]

Understanding Check

Looking through cache.hpp, it appears as though each Cache instantiation handles caching of all results for similar queries on the same type (e.g. a sqlgen::read<User> | where("first_name"_c == "John") and sqlgen::read<User> | where("last_name"_c == "Doe") will both be stored in a single template instantiation of sqlgen::cache<100>(...)). Awkwardly, it seems you can get redundant caches if you specify two like instantiations with different sizes.
Please do correct me anywhere I'm wrong on this.

Concern

It appears there's no method for checking or flagging cache validity, so in the following case:

#include <gtest/gtest.h>

#include <rfl.hpp>
#include <rfl/json.hpp>
#include <sqlgen.hpp>
#include <sqlgen/sqlite.hpp>
#include <vector>

namespace test_cache {

struct User {
  std::string name;
  int age;
};

TEST(sqlite, test_cache) {
  const auto conn = sqlgen::sqlite::connect();

  const auto user = User{.name = "John", .age = 30};
  sqlgen::write(conn, user);

  const auto user_b = User{.name = "Mary", .age = 25};
  sqlgen::write(conn, user_b);

  using namespace sqlgen;
  using namespace sqlgen::literals;

  const auto query = sqlgen::read<std::vector<User>>;
  const auto cached_query = sqlgen::cache<100>(query);

  const auto users1 = cached_query(conn).value();

  EXPECT_EQ(users1[0].name, "John");
  EXPECT_EQ(users1[0].age, 30);
  EXPECT_EQ(users1[1].name, "Mary");
  EXPECT_EQ(users1[1].age, 25);
  EXPECT_EQ(cached_query.cache(conn).size(), 1);

  const auto user_c = User{.name = "Bill", .age = 50};
  sqlgen::write(conn, user_c);

  const auto users2 = cached_query(conn).value();

  EXPECT_EQ(users2[0].name, "John");
  EXPECT_EQ(users2[0].age, 30);
  EXPECT_EQ(users2[1].name, "Mary");
  EXPECT_EQ(users2[1].age, 25);
  EXPECT_EQ(users2[2].name, "Bill");
  EXPECT_EQ(users2[2].age, 50);
  EXPECT_EQ(cached_query.cache(conn).size(), 1);
}

}  // namespace test_cache

We segfault on users2[2]. as we were given a cached result that omits the new User value for Bill.

Proposal

A simple stop-gap solution would be to provide a basic invalidate() function to Cache that allows users to manually purge the cache when appropriate.
A more complex (not sure if possible) solution could involve registering caches that can then be automatically invalidated when executing modifying queries on associated types. I'd likely avoid this approach as it would be difficult to avoid missing edge cases, would not be possible to handle manual execs, and would require quite a bit of added complexity overall.

More than happy to throw together the basic invalidate if you agree that it's a good addition, but wanted to open up discussion on this first in case you already had any thoughts or plans for addressing this.

[liuzicheng1987]

Hi @ScottFries , sorry it took me a couple of days to reply.

I think a simple .invalidate() method (or perhaps .clear()) would be a great addition. I agree that the other case would be way too complex to handle.