[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation

[Kirill Shcherbatov <kshcherbatov@tarantool.org>]

> I still don’t see how truncate interacts with FK constraints
> and appropriate tests on it. Also, you should test not UPDATE
> trigger, but DELETE.Hi! Thank you for review.
I've supported FK checks and rework tests a bit.

====================================

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
   { "VARCHAR",                "TK_ID",          RESERVED,         true  },
   { "WHENEVER",               "TK_STANDARD",    RESERVED,         true  },
   { "WHILE",                  "TK_STANDARD",    RESERVED,         true  },
+  { "TRUNCATE",               "TK_TRUNCATE",    ALWAYS,           true  },
 };
 
 /* Number of keywords */
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 0681177..b016084 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -72,6 +72,62 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
 }
 
 void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list)
+{
+	struct sqlite3 *db = parse->db;
+	if (parse->nErr || db->mallocFailed)
+		goto cleanup;
+	assert(tab_list->nSrc == 1);
+
+	struct Vdbe *v = sqlite3GetVdbe(parse);
+	if (v == NULL)
+		goto cleanup;
+
+	const char *tab_name = tab_list->a->zName;
+	struct Table *table = sqlite3LocateTable(parse, LOCATE_NOERR, tab_name);
+	struct space_def *space_def = NULL;
+	if (table == NULL) {
+		/* Space created with LUA. */
+		uint32_t space_id =
+			box_space_id_by_name(tab_name, strlen(tab_name));
+		if (space_id == BOX_ID_NIL) {
+			diag_set(ClientError, ER_NO_SUCH_SPACE, tab_name);
+			parse->rc = SQL_TARANTOOL_ERROR;
+			parse->nErr++;
+			goto cleanup;
+		}
+		struct space *space = space_cache_find(space_id);
+		assert(space != NULL);
+		space_def = space->def;
+	} else {
+		space_def = table->def;
+		if (sqlite3FkRequired(table, NULL) != 0) {
+			const char *err_msg =
+				tt_sprintf("cannot truncate %s because it has "
+					   "foreign keys");
+			diag_set(ClientError, ER_SQL, err_msg);
+			parse->rc = SQL_TARANTOOL_ERROR;
+			parse->nErr++;
+			goto cleanup;
+		}
+	}
+	assert(space_def != NULL);
+	if (space_def->opts.is_view) {
+		const char *err_msg =
+			tt_sprintf("cannot truncate %s because it is a view",
+				   space_def->name);
+		diag_set(ClientError, ER_SQL, err_msg);
+		parse->rc = SQL_TARANTOOL_ERROR;
+		parse->nErr++;
+		goto cleanup;
+	}
+	sqlite3VdbeAddOp2(v, OP_Clear, space_def->id, true);
+
+cleanup:
+	sqlite3SrcListDelete(parse->db, tab_list);
+}
+
+void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where)
 {
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..90fe6d4 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -725,6 +725,12 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sql_table_delete_from(pParse,X,W);
 }
 
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+  sql_table_truncate(pParse, X);
+}
+
 %type where_opt {Expr*}
 %destructor where_opt {sql_expr_delete(pParse->db, $$, false);}
 
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 07e7829..a90b10e 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3710,11 +3710,22 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
  * @param tab_list List of single element which table from which
  * deletetion if performed.
  * @param where The WHERE clause.  May be NULL.
+ * @param is_truncate use OP_Clear to truncate table.
  */
 void
 sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		      struct Expr *where);
 
+/**
+ * Generate a code for TRUNCATE TABLE statement.
+ *
+ * @param parse Parsing context.
+ * @param tab_list List of single element which table from which
+ *        truncation is performed.
+ */
+void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list);
+
 void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
 		   enum on_conflict_action);
 WhereInfo *sqlite3WhereBegin(Parse *, SrcList *, Expr *, ExprList *, ExprList *,
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 1a75f77..dc22c26 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/box.h"
 #include "box/txn.h"
 #include "box/session.h"
 #include "sqliteInt.h"
@@ -4562,8 +4563,9 @@ case OP_IdxGE:  {       /* jump */
 	break;
 }
 
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
  * Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
  *
  * Delete all contents of the space, which space id is given
  * in P1 argument. It is worth mentioning, that clearing routine
@@ -4575,7 +4577,13 @@ case OP_Clear: {
 	uint32_t space_id = pOp->p1;
 	struct space *space = space_by_id(space_id);
 	assert(space != NULL);
-	rc = tarantoolSqlite3ClearTable(space);
+	rc = 0;
+	if (pOp->p2 > 0) {
+		if (box_truncate(space_id) != 0)
+			rc = SQL_TARANTOOL_ERROR;
+	} else {
+		rc = tarantoolSqlite3ClearTable(space);
+	}
 	if (rc) goto abort_due_to_error;
 	break;
 }
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..39f7345 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -59,3 +59,82 @@ box.sql.execute("INSERT INTO t2 VALUES (0);")
 box.sql.execute("DROP TABLE t2;")
 ---
 ...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+-- can't truncate system table
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+---
+...
+-- can't truncate in transaction
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+-- can't truncate view
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+---
+...
+box.sql.execute("TRUNCATE TABLE v1;")
+---
+- error: 'SQL error: cannot truncate V1 because it is a view'
+...
+-- can't truncate table with FK
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY REFERENCES t1(id));")
+---
+...
+box.sql.execute("TRUNCATE TABLE t2;")
+---
+- error: 'SQL error: cannot truncate T2 because it has foreign keys'
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: 'SQL error: cannot truncate T1 because it has foreign keys'
+...
+-- table triggers should be ignored
+box.sql.execute("DROP TABLE t2;")
+---
+...
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
+box.sql.execute("SELECT * FROM t2;")
+---
+- []
+...
+-- Cleanup
+box.sql.execute("DROP VIEW v1");
+---
+...
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..5e24689 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -37,3 +37,42 @@ box.sql.execute("CREATE TRIGGER t2 BEFORE INSERT ON t2 BEGIN DELETE FROM t1; END
 box.sql.execute("INSERT INTO t2 VALUES (0);")
 
 box.sql.execute("DROP TABLE t2;")
+
+
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+
+-- can't truncate system table
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 1, 'one');")
+box.sql.execute("INSERT INTO t1 VALUES(2, 2, 'two');")
+
+-- can't truncate in transaction
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+
+-- can't truncate view
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("TRUNCATE TABLE v1;")
+
+-- can't truncate table with FK
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY REFERENCES t1(id));")
+box.sql.execute("TRUNCATE TABLE t2;")
+box.sql.execute("TRUNCATE TABLE t1;")
+
+-- table triggers should be ignored
+box.sql.execute("DROP TABLE t2;")
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+box.sql.execute("CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN INSERT INTO t2 VALUES(old.x); END;")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+box.sql.execute("SELECT * FROM t2;")
+
+-- Cleanup
+box.sql.execute("DROP VIEW v1");
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")