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

[Kirill Shcherbatov <kshcherbatov@tarantool.org>]

On 20.07.2018 05:16, n.pettik wrote:
> Firstly, you have partially ignored my comment concerning test:
> I still see no tests on table with triggers (including INSTEAD OF on VIEW),
> FK constraints. Also I would add few tests checking syntax correctness.I've implemented few new tests.

> Secondly, AFAIR we discussed that TRUNCATE (until it is DDL) must not have
> nothing in common with DELETE. So, implement separate function handling
> TRUNCATE from parser. It would provide all necessary checks and emit one opcode:
> OP_Truncate (or OP_Clear working on truncate mode AYW).
I've implemented sql_table_truncate,
> Why is this related to truncate? Truncate and triggers are not compatible.
> Why do you need to start transaction for truncate?
This reworked in new version.


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

We have introduced a new P2 argument for OP_Clear opcode
that calles box_truncate instead of tarantoolSqlite3ClearTable.
Thus, sql_table_delete_from called with is_truncate = true
cause such behavior.

Closes #2201.

@TarantoolBot document
Title: New TRUNCATE operation
Removes all rows from a table or specified partitions of a table,
without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE
clause; however, TRUNCATE TABLE is faster and uses fewer system
resources.
It couldn't be used with system tables. It couldn't be called in
transaction.
The triggers on target table would not be called.
Example:
TRUNCATE TABLE t1;
---
 extra/mkkeywordhash.c    |  1 +
 src/box/sql/delete.c     | 38 ++++++++++++++++++++++++++
 src/box/sql/parse.y      |  6 +++++
 src/box/sql/sqliteInt.h  | 11 ++++++++
 src/box/sql/vdbe.c       | 12 +++++++--
 test/sql/delete.result   | 70 ++++++++++++++++++++++++++++++++++++++++++++++++
 test/sql/delete.test.lua | 37 +++++++++++++++++++++++++
 7 files changed, 173 insertions(+), 2 deletions(-)

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 f9d3498..e2d321c 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -72,6 +72,44 @@ 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;
+	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);
+	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_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 54661cb..1ea3266 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3700,11 +3700,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 f50e389..48238aa 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"
@@ -4566,8 +4567,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
@@ -4579,7 +4581,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..38b8d7b 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -59,3 +59,73 @@ 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');")
+---
+...
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+---
+...
+box.sql.execute("DROP TRIGGER IF EXISTS trig1;")
+---
+...
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN INSERT INTO t2 VALUES(old.a); END;")
+---
+...
+-- 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("TRUNCATE TABLE v1;")
+---
+- error: 'SQL error: cannot modify V1 because it is a view'
+...
+-- table triggers should be ignered
+box.sql.execute("CREATE TRIGGER trig2 BEFORE UPDATE 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..541bd82 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -37,3 +37,40 @@ 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');")
+
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("DROP TRIGGER IF EXISTS trig1;")
+
+box.sql.execute("CREATE TABLE t2(x PRIMARY KEY);")
+box.sql.execute("CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN INSERT INTO t2 VALUES(old.a); END;")
+
+-- 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("TRUNCATE TABLE v1;")
+
+-- table triggers should be ignered
+box.sql.execute("CREATE TRIGGER trig2 BEFORE UPDATE 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;")
-- 
2.7.4