Repository: AlecStrong/sql-psi
Pull request #374 (2022-04-11 12:50:48)

Forbid updates on views without the correct type of trigger


Patch:
diff --git a/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/MutatorMixin.kt b/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/MutatorMixin.kt
index 26cee4b..15e8ab6 100644
--- a/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/MutatorMixin.kt
+++ b/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/MutatorMixin.kt
@@ -1,7 +1,12 @@
 package com.alecstrong.sql.psi.core.psi.mixins
 
+import com.alecstrong.sql.psi.core.SqlAnnotationHolder
 import com.alecstrong.sql.psi.core.psi.QueryElement.QueryResult
+import com.alecstrong.sql.psi.core.psi.SqlCreateTriggerStmt
 import com.alecstrong.sql.psi.core.psi.SqlQualifiedTableName
+import com.alecstrong.sql.psi.core.psi.SqlUpdateStmt
+import com.alecstrong.sql.psi.core.psi.SqlUpdateStmtLimited
+import com.alecstrong.sql.psi.core.psi.SqlViewName
 import com.alecstrong.sql.psi.core.psi.SqlWithClause
 import com.intellij.lang.ASTNode
 import com.intellij.psi.PsiElement
@@ -22,4 +27,28 @@ internal abstract class MutatorMixin(
       super.queryAvailable(child)
     }
   }
+
+  override fun annotate(annotationHolder: SqlAnnotationHolder) {
+    super.annotate(annotationHolder)
+    val tables = tableAvailable(this, getTableName().name).ifEmpty { return }
+    val tableUpdated = tables.singleOrNull()?.table
+    if (tableUpdated == null) {
+      annotationHolder.createErrorAnnotation(
+        getTableName(), "Trying to mutate something which is not mutable."
+      )
+      return
+    }
+
+    if ((this is SqlUpdateStmt || this is SqlUpdateStmtLimited) && tableUpdated is SqlViewName) {
+      // Find the trigger that does INSTEAD OF UPDATE.
+      val trigger = containingFile.schema<SqlCreateTriggerStmt>().find {
+        it.tableName?.name == tableUpdated.name &&
+          it.node.getChildren(null).map { it.text }.containsAll(listOf("INSTEAD", "OF", "UPDATE"))
+      }
+      if (trigger == null) {
+        annotationHolder.createErrorAnnotation(getTableName(), "Cannot UPDATE the view ${tableUpdated.text} without a trigger on ${tableUpdated.text} that has INSTEAD OF UPDATE.")
+        return
+      }
+    }
+  }
 }


Test patch:
diff --git a/test-fixtures/src/main/resources/fixtures/update-view-with-trigger/Test.s b/test-fixtures/src/main/resources/fixtures/update-view-with-trigger/Test.s
new file mode 100644
index 0000000..e4b389b
--- /dev/null
+++ b/test-fixtures/src/main/resources/fixtures/update-view-with-trigger/Test.s
@@ -0,0 +1,26 @@
+CREATE TABLE foo (
+  id INTEGER NOT NULL PRIMARY KEY,
+  name TEXT NOT NULL,
+  selected INTEGER NOT NULL
+);
+
+CREATE TABLE bar (
+  id INTEGER NOT NULL PRIMARY KEY,
+  short_name TEXT NOT NULL,
+  full_name TEXT NOT NULL,
+  selected INTEGER NOT NULL
+);
+
+CREATE VIEW foobar AS
+SELECT id, name, selected FROM foo
+UNION
+SELECT id, short_name AS name, selected FROM bar;
+
+CREATE TRIGGER foobar_update_added
+INSTEAD OF UPDATE OF selected ON foobar
+BEGIN
+  UPDATE foo SET selected = new.selected WHERE id = new.id;
+  UPDATE bar SET selected = new.selected WHERE id = new.id;
+END;
+
+UPDATE OR IGNORE foobar SET selected = ? WHERE id = ?;
\ No newline at end of file
diff --git a/test-fixtures/src/main/resources/fixtures/update-view-without-trigger/Test.s b/test-fixtures/src/main/resources/fixtures/update-view-without-trigger/Test.s
new file mode 100644
index 0000000..2bce257
--- /dev/null
+++ b/test-fixtures/src/main/resources/fixtures/update-view-without-trigger/Test.s
@@ -0,0 +1,20 @@
+CREATE TABLE foo (
+  id INTEGER NOT NULL PRIMARY KEY,
+  name TEXT NOT NULL,
+  selected INTEGER NOT NULL
+);
+
+CREATE TABLE bar (
+  id INTEGER NOT NULL PRIMARY KEY,
+  short_name TEXT NOT NULL,
+  full_name TEXT NOT NULL,
+  selected INTEGER NOT NULL
+);
+
+CREATE VIEW foobar AS
+SELECT id, name, selected FROM foo
+UNION
+SELECT id, short_name AS name, selected FROM bar;
+
+-- error[col 17]: Cannot UPDATE the view foobar without a trigger on foobar that has INSTEAD OF UPDATE.
+UPDATE OR IGNORE foobar SET selected = ? WHERE id = ?;
\ No newline at end of file