Can there be support for creating views in sqlite_orm?

[juandent]

Right now one can create a table in C++ with the make_storage function and afterwards make the table a view in any sqlite client like sqlitestudio. Sync_schema() fails to preserve data when columns are added and fails to change CHECK clauses in the database so it is not working as one would like (I understand there is complexity there...). I don't know how hard can it be to add a make_view() function to use inside make_storage, but I feel it would greatly add to sqlite_orm's functionality! In my opinion this should have high priority...

[fnc12]

adding views is one of the most impressive features. It exists in TODO list https://github.com/fnc12/sqlite_orm/blob/master/TODO.md. One issue is API. We need to make API which will allow performing any operation with view available in SQLite and (desired) keep compile time checks just like it is done with table. Do you have any idea?

[juandent]

Yes, my proposal is that we would use storage to create the SQL and then define the view by passing the statement, in this way we would be using all the serialization and we could place any statement no matter how complex in the view definition, something like:

auto statement = 
        storage.prepare(select(columns(&Employee::m_ename, &Employee::m_job), where(in(&Employee::m_deptno, { 10,20 }))));
	make_view("employees_in_deptnos_10_or_20", as(statement)); // this would go inside make_storage() 

Couldn't it be as simple as this?

[juandent]

Sure that would work better

[juandent]

but it seems we have all the tough parts already at our disposal? does this mean implementing the feature would not be as big a deal?

[fnc12]

yes looks like you are right

[juandent]

does this make your plans to add views in the near future a reality?

[fnc12]

yes

[juandent]

I would love to create examples of views in the Guide!! 😁 BTW how did you liked it?

[juandent]

I think the make_view could look like this:

make_view("employees_in_dept_10_or_20", 
       make_parameter("ename", &EmployeesInDept::m_ename),
       make_parameter( "job", &EmployeesInDept::m_job), 
       as(statement));

[fnc12]

I'd skip statement cause statement is created by sqlite3_prepare_v2 which is not required during storage creation:

make_view("employees_in_dept_10_or_20",
    make_column("ename", &EmployeesInDept::m_ename),
    make_column("job", &EmployeesInDept::m_job),
    as(select(columns(&Employee::m_ename, &Employee::m_job), where(in(&Employee::m_deptno, { 10,20 })))

It will make a static check that number of columns provided with make_column functions is equal to amount of columns inside select