[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation
- From: Kirill Shcherbatov <kshcherbatov@xxxxxxxxxxxxx>
- To: tarantool-patches@xxxxxxxxxxxxx, Nikita Pettik <korablev@xxxxxxxxxxxxx>
- Date: Mon, 23 Jul 2018 13:33:52 +0300
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;")
Other related posts:
