Reading/adding/rearranging/deleting columns/tables, without pre-defining them?

[infranscia]

Heya. So I've been experimenting with features I consider "basic," just to get myself introduced to how everything works.

I noticed that sqlite_orm seems to be designed in a way so it's difficult, if not downright impossible to do things like:

• Read what tables exist in the database, without the programmer defining them in advance
• Similarly, read what columns exist in tables, without the programmer defining them
• Again create new tables after the database is opened, based on whatever input the end user puts in (with a pre-compiled app)
• Similarly, create new columns after the database is opened
• Rearrange existing columns (e.g. change "ID; Phone; Name" to "ID; Name; Phone")
• Delete (or "drop") any of the above

I guess requiring the use of pre-defined structures and whatnot is good for giving the programmer control so that data is less likely to become corrupted, but it's not so good for giving the end-user control or flexibility. Maybe I could get some of them if I'm creative enough, though I'd prefer built-in functionalities.

Am I missing anything, or are all of these really not available?

I'll admit, I'm worried that requesting these features (or at least the ones that don't exist) might be a big ask - I suspect it would require a pretty big overhaul. Still, would any of them be feasible? Maybe adding and/or dropping tables on-the-fly, at least?

Thanks. 🙂

[fnc12]

Hi. Your question looks pretty interesting and vast. First of all one can drop a table at runtime by calling storage.drop_table("table_name"); and check whether table exists using storage.table_exists("table_name"); even though there is no table specified in make_storage with "table_name" name. But all other schema dynamic editing is not presented cause in case where we don't know what columns tables can at compile time our storage can't understand which C++ class member has to be mapped to any column. I mean let's say you define a class to map to a table:

struct User {
    int id = 0;
    std::string name;
};

which is expected to look like this in SQLite

auto storage = make_storage("path.sqlite",
    make_table("users",
        make_column("id", &User::id, primary_key()),
        make_column("name", &User::name)));

and you call a function to add a columns like

storage.add_column_to<User>("birth_date");

or

storage.add_column_to("users", "birth_date");

And now 'users' table has 3 columns. Ok. How do you expect functions to function? E.g. we have storage.get_all which works pretty easy:

std::vector<User> users = storage.get_all<User>();

or more complicated

std::vector<User> users = storage.get_all<User>(where(not_equal(&User::id, 10));

How do we expect "birth_date" column to be retrieved? In which User's class member? How to use it in conditions like not_equal(&User::id, 10)?

I'd like to keep this conversation cause it is related to core design goals of sqlite_orm. Please feel free to share API ideas you expect to have and/or your development goals you need to achieve.

Thanks