Support for views

[woytaz]

I started using the library just recently and I like it a lot, but I quickly noticed there is no support whatsoever for views. Are there any plans for changing that?

SQLite makes things a bit easier since views are read-only:

You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite.

From what I gathered (some) other engines allow inserting through views but even then a read-only implementation would be very helpful.

[rbock]

Hi,

That's a good observation. I have never used views myself.

Would you want to create the view programmatically? Or would you want to define it in the DDL?

Thanks,
Roland

[woytaz]

I rarely use them myself, but it just so happened that I found a use for one. (It shows sums of values from some records)

I was just thinking about having it in the DDL, just like the tables right now.

[rbock]

I played with the idea earlier today, see https://github.com/rbock/sqlpp11/tree/feature-view. I guess the most difficult part will be parsing the DDL and creating the correct type structure from it.

Note that sqlpp11 supports sub-queries. In my probably naive understanding of views that is kinda similar since you can store the sub-query in a variable and use it like a virtual table.

[woytaz]

Yeah, I managed to use subqueries to do the same thing and was impressed when it all just worked as I hoped it would.

(Then I discovered I also need coalesce, which isn't available in the library yet haha. I'll try working around it with custom queries or verbatim.)

[rbock]

Assuming that the arguments of coalesce all share the same type (e.g. are all text, or are all integral), adding coalesce should be rather easy to implement.
Do you want to give it a try?

[woytaz]

Interestingly, it appears you can mix types in coalesce: http://sqlfiddle.com/#!5/d6a97/8

I might look into implementing it (at least in the more limited version, assuming the same type for all arguments) over the weekend.

[rbock]

Looking forward to it. Let me know if you need help.