[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation
Kirill Shcherbatov
kshcherbatov at tarantool.org
Wed Jul 25 19:59:01 MSK 2018
> Except for comments below, also pls add to tarantool-doc bot request
> notice concerning the fact that in our implementation TRUNCATE is DDL operation.
Ok, done.
> Is this check reasonable?
Not really. Dropped.
> I guess here you can explicitly use space_by_id and box_space_id_by_name
> to bypass table hash. If you do so, you will be able to get rid off
> if (table == NULL) branch.
done. made this place a little easy to refactor.
> Lets use separate label for this purpose.
Ok, done. It's looking a bit ugly for me..;
> sqlite3FkRequired is too strict condition. I guess you are still
> capable of truncating child (or referencing) table, since no
> FK violations may take place in this case.
> So, what you need is simple check:
> sqlite3References() != NULL;
Ok, done.
> Lets add ’space’ or ’table' word to message to avoid confusing:
Done.
> This comment is remained from previous patch version, I guess.
You are right.
> Pls start comments (even in tests) from capital letter and end with dot.
> Specify types for all columns. Static typing is on the way.
Fixed.
=============================================
To implement new TRUNCATE operation, we have introduced a
new P2 argument for OP_Clear opcode that calles box_truncate
instead of tarantoolSqlite3ClearTable.
This operation should work faster than DELETE FROM; but have
a few restricts.
Closes #2201.
@TarantoolBot document
Title: New TRUNCATE operation
TRUNCATE is DDL 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 or with tables having FKs.
It also couldn't be called in transaction.
The triggers on table will have ignored.
Example:
TRUNCATE TABLE t1;
---
extra/mkkeywordhash.c | 1 +
src/box/sql/delete.c | 48 +++++++++++++++++++++++++++++++
src/box/sql/parse.y | 6 ++++
src/box/sql/sqliteInt.h | 10 +++++++
src/box/sql/vdbe.c | 12 ++++++--
test/sql/delete.result | 75 ++++++++++++++++++++++++++++++++++++++++++++++++
test/sql/delete.test.lua | 38 ++++++++++++++++++++++++
7 files changed, 188 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 0681177..0809b35 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -72,6 +72,54 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
}
void
+sql_table_truncate(struct Parse *parse, struct SrcList *tab_list)
+{
+ assert(tab_list->nSrc == 1);
+
+ struct Vdbe *v = sqlite3GetVdbe(parse);
+ if (v == NULL)
+ goto cleanup;
+
+ const char *tab_name = tab_list->a->zName;
+ struct space_def *space_def = NULL;
+ 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);
+ goto tarantool_error;
+ }
+ struct space *space = space_cache_find(space_id);
+ assert(space != NULL);
+ space_def = space->def;
+
+ struct Table *table = sqlite3LocateTable(parse, LOCATE_NOERR, tab_name);
+ if (table != NULL && sqlite3FkReferences(table) != NULL) {
+ const char *err_msg =
+ tt_sprintf("can no truncate space %s because other "
+ "objects depend on it", space_def->name);
+ diag_set(ClientError, ER_SQL, err_msg);
+ goto tarantool_error;
+ }
+ if (space_def->opts.is_view) {
+ const char *err_msg =
+ tt_sprintf("can no truncate space %s because it is a "
+ "view", space_def->name);
+ diag_set(ClientError, ER_SQL, err_msg);
+ goto tarantool_error;
+ }
+ sqlite3VdbeAddOp2(v, OP_Clear, space_def->id, true);
+
+cleanup:
+ sqlite3SrcListDelete(parse->db, tab_list);
+ return;
+
+tarantool_error:
+ sqlite3SrcListDelete(parse->db, tab_list);
+ parse->rc = SQL_TARANTOOL_ERROR;
+ parse->nErr++;
+}
+
+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 71518e0..0e50d21 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -733,6 +733,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 8cc2288..56c8899 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3719,6 +3719,16 @@ 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..d06907c 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -59,3 +59,78 @@ 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 INT PRIMARY KEY, a INT, b STR);")
+---
+...
+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: can no truncate space V1 because it is a view'
+...
+-- Can't truncate table with FK.
+box.sql.execute("CREATE TABLE t2(x INT PRIMARY KEY REFERENCES t1(id));")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: 'SQL error: can no truncate space T1 because other objects depend on it'
+...
+-- Table triggers should be ignored.
+box.sql.execute("DROP TABLE t2;")
+---
+...
+box.sql.execute("CREATE TABLE t2(x INT 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..0477d22 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -37,3 +37,41 @@ 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 INT PRIMARY KEY, a INT, b STR);")
+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 INT PRIMARY KEY REFERENCES t1(id));")
+box.sql.execute("TRUNCATE TABLE t1;")
+
+-- Table triggers should be ignored.
+box.sql.execute("DROP TABLE t2;")
+box.sql.execute("CREATE TABLE t2(x INT 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;")
--
2.7.4
More information about the Tarantool-patches
mailing list