How can i dynamically choose table

[ZerQAQ]

Hi,

I have a lot of table, they have the same table struct, the only difference between them is their name, their name will be like table01, table02, table03... and so on.

I want to decide which table I use in runtime, how should I make this?

[rbock]

If they all have the exact same structure, I would ask why you have so many tables?

I don't have a good solution for your situation. Maybe you can inherit and override the function that returns the name of the table? Not sure if that would work. And you would also lose major benefits of the library.

[ZerQAQ]

Hi,

store date in multiple tables can improve performance in some situation, that is why I have so many tables.

after reading some of your code, I come up with an amazing idea. I modify the file table.h:

//table.h:111

  template <typename Context, typename X>
  struct serializer_t<
      Context,
      X,
      typename std::enable_if<std::is_base_of<table_base_t, X>::value and not is_pseudo_table_t<X>::value, void>::type>
  {
    using _serialize_check = consistent_t;
    using T = X;

    static Context& _(const T& /*unused*/, Context& context)
    {
      if(T::multi_table_on){
        T::inject(context);
      } else {
        context << name_of<T>::template char_ptr<Context>();
      }
      return context;
    }
  };

so I can inherit the table struct to make this:

static char globalIndex[10];

struct _tbStudent : orm::Student{
    static constexpr bool multi_table_on = true;

    template<typename Context>
    static void inject(Context &context){
        context << "Student" << globalIndex;
    }
};
_tbStudent tbStudent;

int main(){
    int index;
    ...
    sprintf(globalIndex, "%02d", index);
    auto res = db(select(sqlpp::all_of(tbStudent)).from(tbStudent).unconditionally());
    ...
}

may be your can consider this to be a new feature of your library, should I open a pull request?

[rbock]

That is going in a good direction. You could specialize serializer_t for your table type:

  template <typename Context>
  struct serializer_t<Context, _tbStudent>
  {
    using _serialize_check = consistent_t;
    using T = _tbStudent;

    static Context& _(const T& t, Context& context)
    {
      context << t.get_name();
      return context;
    }
  };

Untested, but close enough, I hope :-)

[emmenlau]

Linking related issue https://github.com/rbock/sqlpp11/issues/239

[emmenlau]

Dear @rbock and others, I kindly wanted to ask if the above feature request could be formalized, to be officially supported by the library? Or maybe this behavior can be implemented differently with sqlpp11?

My use case is: Users store tables with large sets of data in an sqlite file. Whenever such a table is added, I currently generate the name dynamically (basically as simple as table01, table 02, ...). The tables all have the same structure, but different names.

In my code, I do not need to support the tables as uniquely named classes. Having a pointer to such a class instance would be sufficient, to be able to call getters and setters.

Can sqlpp11 support this in some way? Is the above code snippet sufficient, or how would I go about this? How to open the table from the file, and how to create a new one?

[rbock]

Hi @emmenlau,

Is the above code snippet sufficient, or how would I go about this?

The snippet above should work (I did not test it myself, though) for insert, update, delete, and select. It would be possible to support this in the code generator, for instance, but I am not sure that this is a common enough use case. So maybe the "official" support would be to document this approach (and have an example)?

How to open the table from the file, and how to create a new one?

I am not sure what you mean by "opening from a file"? As for creating new tables, sqlpp11 does not have good support for creating tables (or other operations on the structure of the database). You would need to run execute similar to what you can find in the examples:

  db.execute(R"(CREATE TABLE tab_sample (
                alpha INTEGER PRIMARY KEY,
                        beta varchar(255) DEFAULT NULL,
                        gamma bool DEFAULT NULL
                        ))");

Given that all your tables have the same structure, that should be easy to implement.

Hope this helps?

Best, Roland