From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F09D624E23 for ; Mon, 23 Jul 2018 06:33:55 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id POhgLLkWd0ns for ; Mon, 23 Jul 2018 06:33:55 -0400 (EDT) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 3959420397 for ; Mon, 23 Jul 2018 06:33:55 -0400 (EDT) Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation References: <70326bb69fbe25215df79e2d5e01043f93ff7c5a.1531902074.git.kshcherbatov@tarantool.org> <797d52e5-1387-dd3d-1fb2-0fafbbbdcf56@tarantool.org> <606f657b-26df-35b9-ddf1-d5bae5654d82@tarantool.org> <119B20FA-F3FD-484C-A17E-0B63131CC4DE@tarantool.org> <43f1d131-3e6a-1791-758e-e39b0a605c29@tarantool.org> <4ED1247F-9E59-4C2F-AF92-556D7D93DCD5@tarantool.org> From: Kirill Shcherbatov Message-ID: <2ec56870-e8cb-6112-f12a-a654f59315dc@tarantool.org> Date: Mon, 23 Jul 2018 13:33:52 +0300 MIME-Version: 1.0 In-Reply-To: <4ED1247F-9E59-4C2F-AF92-556D7D93DCD5@tarantool.org> Content-Type: text/plain; charset="utf-8" Content-Language: en-US Content-Transfer-Encoding: 8bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org, Nikita Pettik > 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;")