Add support for SQL views

[jamesaoverton]

I want VALVE to support SQL views in the following ways:

1. in the "table" table a view will be specified by setting the "type" to 'view'
2. in the "table" table the "path" for a view may be empty
3. the "column" table should still contain entries for the columns of the view, but VALVE will not check to see if they actually match the view (as it does for TSV files)
4. VALVE will not create the *_conflict table, or *_view or *_text views
5. if a "path" is given VALVE will create and drop the view a. VALVE will expect it to be a SQL file or it will exit with an error b. create_all_tables() will create the view by executing that SQL, and ensure that the view exists afterward or exit with an error c. drop_tables() and drop_all_tables() will drop the view
6. if there is no "path" we assume that the user has configured the view a. create_all_tables() will ensure that the view exists afterward or exit with an error b. drop_tables() and drop_all_tables() will skip the view
7. VALVE will not truncate, load, or save the view
8. I'm not sure how valve.sort_tables() should work for views...
9. VALVE will not edit rows in the view with insert_row, delete_row, etc.
10. it would be nice if VALVE could still validate a row from a view using validate_row(), since its columns will be defined
11. if another table T refers to a column in a view using from(view.column), VALVE will not try to enforce a foreign key constraint for T, but VALVE should still support get_matching_values() for that column of T -- I think the same should apply for under() and tree() but I'm not sure

[jamesaoverton]

Thinking about this some more, I would like to reserve the "type" column of the "table" table to tell VALVE about the structure of that table, such as the columns to expect. The current table types do that: table, column, datatype, rule. I've been thinking about adding an 'ldtab' table type, which would also determine which columns that table has.

In contrast, 'view' does not tell VALVE anything about the columns to expect, so 'view' is not a good table type (on this line of reasoning).

Now I think it would be better to have another column of the "table" table for this purpose. Maybe "mode" is a good name for it. This issue is about the 'view' mode. #82 is about the 'generated' mode. We probably want a 'readonly' mode.

Instead of "mode", another idea is an "options" column, with a space-separated list of options, which could include 'view', 'generated', etc.

[lmcmicu]

Here is a small table describing what was decided on 2024-03-05 about view, readonly, and normal tables (we have decided to collapse the distinction between readonly and generated modes since the only thing that distinguishes them is the kind of path each has).

| Mode                                                    | Empty                                             | SQL | TSV | Generic executable | Conflict, text views, etc. | Rows are editable | Validation?                                   |
|---------------------------------------------------------|---------------------------------------------------|-----|-----|--------------------|----------------------------|-------------------|-----------------------------------------------|
| Normal                                                  | No                                                | No  | Yes | No                 | Yes                        | Yes               | Yes                                           |
| View                                                    | Yes                                               | Yes | No  | Change to yes      | No                         | No                | Not on load, but rows can be validated        |
| Readonly (subsumes both the old readonly and generated) | Change to yes (check that it exists, do not drop) | Yes | Yes | Yes                | No                         | No                | Currently yes on load. Rows can be validated. |