SQL Views. What you can and can't do with them in Appsheet

[Marc_Dillon]

1. CAN load any view in as a read-only table.
2. CAN edit values in a view based on a single table (as long as that base table is also editable).
3. CANNOT edit values in a view based on more than one table (like a JOIN). The app will appear to allow it, but the sync will fail.
4. CAN edit values in a single-table view that also includes calculated columns (columns which exist only in that view, and not in the base table).
5. CANNOT edit the calculated columns referenced in #4, although the app appears to let you, it will fail the sync, so you must mark them as not editable.
6. CANNOT insert new records into views? (not 100% sure about this one, my test just now with it failed, but I thought I had done it before)

*this all was tested on MySQL specifically. I assume it all applies to other SQL variants.

Anything else to add?

[Jonathon]

You can edit values through a SQL view, under certain constraints; to name a fiew:

1. You cant edit derived fields (in the same way you couldn't directly edit a virtual column in appsheet)
2. You cant edit views based upon UNIONS
3. You can edit fields for the primary table in a JOIN statement
4. You can add fields through a view, even if the view includes a JOIN as long as only the base table is affected

Take for example this bit of SQL, which flattens an orgsUsers junction table into an appsheet-readable list:

SELECT
	iam.auth.uid,
	iam.auth.useremail,
	iam.auth.org_id,
	iam.auth.first_name,
	iam.auth.last_name,
	t.orgs
FROM iam.auth
LEFT OUTER JOIN (
	SELECT
		orgsUsers.uid,
		STRING_AGG(orgsUsers.org_id, ' , ') AS orgs
	FROM iam.orgsUsers
	WHERE (orgsUsers.active = 1)
	GROUP BY uid
	) AS t ON iam.auth.uid = t.uid

In this example, you could edit the useremail, org_id, first_name, and last_name fields through the view.

[Marc_Dillon]

Thanks for the input Jonathon!

Agreed with #1 and #2.

I don't believe #3 is correct though. I just tested it again to make sure, when trying to sync a change on a view with JOIN, that change being on one of the primary table's columns, the Appsheet app throws an error that the table is not updatable.

The view config:

 SELECT 
        `t1`.* ,
        `t2`.`id` AS `t2id`,
        `t2`.`col` AS `t2col`
    FROM
        (`test_table` `t1`
        LEFT JOIN `test_table2` `t2` ON ((`t2`.`id` = `t1`.`id`)))

Or maybe there's some trick that I'm missing to get it to work?

Also not sure what you're referring to with #4. 

[Jonathon]

Interesting - perhaps thats a constraint with mysql? My environment is running on mssql and it would require many adjustments if I couldn't push updates to views - I use them quite heavily.

For example, I have a user management application where users are provisioned to groups, and permissions are attached to groups. I flatten all of a users inherited permissions into a comma separated list which is joined to their user record in a view (basically the SQL code I shared above). But, at least in my case, users can still edit their user record and update their name, etc.

Edit: did some quick digging, it does appear that mysql views are updatable, but the constraints differ somewhat from mssql. Are you able to perform updates to the view through a direct db query?

------

Regarding #4 - my other post was worded poorly here, apologies. What I meant to say is you can perform insert operations through a SQL view, even when the view has a JOIN operation, as long as the view allows you to provide all the required fields for the base table.

[Marc_Dillon]

Here is what I found:

For a MySQL View to be "updatable", there are a number of restrictions, as detailed in the links below, including only being able to use an INNER JOIN. So that's why my above example threw the "not updatable" error.

https://www.mysqltutorial.org/create-sql-updatable-views.aspx

https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html

I changed it to an INNER JOIN, and the new error is "Failed to update row (mysql): Incorrect usage of UPDATE and LIMIT". Further reading shows that even if a View is updatable, you can't use the LIMIT clause in an UPDATE statement.

The following direct query succeeds:

UPDATE tt_join_view SET `name`="update test" WHERE id="1e8d68e1"

However adding "LIMIT 1" at the end produces the same error as above.

UPDATE tt_join_view SET `name`="update test" WHERE id="1e8d68e1" LIMIT 1

I'm guessing that Appsheet includes the "LIMIT 1" to prevent the possibility of multiple records being updated when only 1 should be? Not really sure what the point of that is, as the database itself prevents duplicate primary key values, but it definitely doesn't seem like something we can change as app-builders.

Does mssql allow the usage of LIMIT in an UPDATE statement on a view, or does Appsheet not include that clause on mssql statements?

-----

That's a huge bummer to hear that updating views IS possible for other sql variants, but not the one I'm using here. I have lots of use-cases where I'd love to be able to update views 😞

[brian]

Microsoft SQL Server doesn't support LIMIT, but it does have similar functionality with TOP.  We do use TOP when sending Deletes and Updates to SQL Server, but I don't believe it has the same restriction with updatable views.

You're correct in assuming that we're using limit to avoid updating or deleting multiple rows.  The AppSheet key column doesn't have to be a table primary key.  You can set any column as a key column on the AppSheet side, so it's intended to provide a little extra safety in case something is misconfigured.

[Marc_Dillon]

Thanks for the reply Brian!

I was able to test this situation on a Postgres instance recently as well, here are my findings.

1. Non-basic Views are not automatically "updatable", but one can configure triggers/rules to tell the system exactly what to do when someone tries to UPDATE/INSERT/DELETE on a View.
2. Assuming the above trigger/rule is correctly set for a JOIN'd View, Appsheet is able to edit/add records on a JOIN'd View.
3. Does not support the usage of LIMIT (or TOP or anything else I could find) on an UPDATE statement. (although can simulate the same functionality with a SELECT subquery using LIMIT)
4. I set the Appsheet key to a different column than the table source's key, and set 2 records with the same value in that column, then updated it from Appsheet, and both records were updated. 

The point of the test in #4 is that the situation where the usage of LIMIT on MySQL is supposed to prevent, is fully "exposed" with Postgres. So if that LIMIT was to be removed from the MySQL query, in order to allow for Appsheet to update/insert on JOIN'd Views, thus exposing the same potential issue, there is at least a precedence that such an issue isn't really a big deal.

[Jonathon]

@brian your answer gave me some insight regarding odd behaviours I've had in the past, so thanks for that!

Does MySQL support TOP() in the same way as mssql? Would it be reasonable to adjust the MySQL from using LIMIT to TOP() in this case, if for no other reason than consistency?

[Marc_Dillon]

No, there is no TOP in MySQL.

[KingsGuava]

I'd like to add that you can set the database schema of your MSSQL view to be different from the table it's built on. So the authentication account that you use in AppSheet could have access to the database schema that the views are created in, but not the tables. It adds another protection layer but things can get weird if you create a view that includes a JOIN of a table that is not in the allowed database schema.