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 42CAC2ABCA for ; Thu, 28 Mar 2019 08:08:04 -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 DKqn-yUm3o0N for ; Thu, 28 Mar 2019 08:08:04 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 E85EC2AA31 for ; Thu, 28 Mar 2019 08:08:03 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH v2 4/4] sql: introduce ALTER TABLE ADD CONSTRAINT UNIQUE/PRIMARY KEY Date: Thu, 28 Mar 2019 15:07:58 +0300 Message-Id: In-Reply-To: References: In-Reply-To: References: 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 Cc: v.shpilevoy@tarantool.org, Nikita Pettik Table (aka space) can be created without indexes at least from Lua-land (note that according ANSI SQL table may lack PK). Since there were no facilities to create primary key constraint on already existing table, lets extend ADD CONSTRAINT statement with UNIQUE and PRIMARY KEY clauses. In this case, UNIQUE works exactly in the same way as CREATE UNIQUE INDEX ... statement does. In Tarantool primary index is an index with id == 0, so during execution of ADD CONSTRAINT PRIMARY KEY we check that there is no any entries in _index space and create that one. Otherwise, error is raised. Part of #3097 Follow-up #3914 @TarantoolBot document Title: ALTER TABLE ADD CONSTRAINT UNIQUE/PK Currently, tables which lack primary key can take part neither in DDL nor DQL routines. Attempt to do this leads to error. Such tables (without PK) may appear as spaces created from Lua NoSQL interface. To improve NoSQL-SQL interoperability, we are introducing way to add primary key to already existing table: ALTER TABLE ADD CONSTRAINT PRIMARY KEY() And alongside with this another one alias to CREATE UNIQUE INDEX: ALTER TABLE
ADD CONSTRAINT UNIQUE() Note that Tarantool PK constraint should be explicitly added before any other unique constraints or secondary indexes. Otherwise, error is raised: "can not add a secondary key before primary". --- src/box/sql/build.c | 32 +++++++++++++++++++++++-- src/box/sql/parse.y | 10 ++++++++ test/sql-tap/alter.test.lua | 57 +++++++++++++++++++++++++++++++++++++++++++- test/sql-tap/index1.test.lua | 11 ++++++++- 4 files changed, 106 insertions(+), 4 deletions(-) diff --git a/src/box/sql/build.c b/src/box/sql/build.c index 20cc346a0..1f604cfe0 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -1956,6 +1956,20 @@ generate_index_id(struct Parse *parse, uint32_t space_id, int cursor) return iid_reg; } +static void +vdbe_emit_pk_existence_check(struct Parse *parse, uint32_t space_id, + int _index_cursor) +{ + struct Vdbe *v = sqlGetVdbe(parse); + int tmp_reg = ++parse->nMem; + sqlVdbeAddOp2(v, OP_Integer, space_id, tmp_reg); + int found_addr = sqlVdbeAddOp4Int(v, OP_NotFound, _index_cursor, 0, + tmp_reg, 1); + sqlVdbeAddOp4(v, OP_Halt, SQL_ERROR, ON_CONFLICT_ACTION_FAIL, 0, + "multiple primary keys are not allowed", P4_STATIC); + sqlVdbeJumpHere(v, found_addr); +} + /** * Add new index to table's indexes list. * We follow convention that PK comes first in list. @@ -2413,9 +2427,23 @@ sql_create_index(struct Parse *parse) { (void *)space_by_id(BOX_INDEX_ID), P4_SPACEPTR); sqlVdbeChangeP5(vdbe, OPFLAG_SEEKEQ); - int index_id = generate_index_id(parse, def->id, cursor); + int index_id; + /* + * In case we are creating PRIMARY KEY constraint + * (via ALTER TABLE) we must ensure that table + * doesn't feature any indexes. Otherwise, + * we can immediately halt execution of VDBE. + */ + if (idx_type == SQL_INDEX_TYPE_CONSTRAINT_PK) { + vdbe_emit_pk_existence_check(parse, def->id, cursor); + index_id = parse->nMem++; + sqlVdbeAddOp2(vdbe, OP_Integer, 0, index_id); + } else { + index_id = generate_index_id(parse, def->id, cursor); + } sqlVdbeAddOp1(vdbe, OP_Close, cursor); - vdbe_emit_create_index(parse, def, index->def, def->id, index_id); + vdbe_emit_create_index(parse, def, index->def, + def->id, index_id); sqlVdbeChangeP5(vdbe, OPFLAG_NCHANGE); sqlVdbeAddOp0(vdbe, OP_Expire); } diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 0c4603e83..ed5c05436 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1624,6 +1624,16 @@ cmd ::= alter_add_constraint(N) FOREIGN KEY LP eidlist(FA) RP REFERENCES sql_create_foreign_key(pParse); } +cmd ::= alter_add_constraint(N) unique_spec(U) LP sortlist(X) RP. { + create_index_def_init(&pParse->create_index_def, N.table_name, &N.name, X, U, + SORT_ORDER_ASC, false); + sql_create_index(pParse); +} + +%type unique_spec {int} +unique_spec(U) ::= UNIQUE. { U = SQL_INDEX_TYPE_CONSTRAINT_UNIQUE; } +unique_spec(U) ::= PRIMARY KEY. { U = SQL_INDEX_TYPE_CONSTRAINT_PK; } + cmd ::= alter_table_start(A) RENAME TO nm(N). { rename_entity_def_init(&pParse->rename_entity_def, A, &N); sql_alter_table_rename(pParse); diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua index 3b2eceb98..3e87fbb8b 100755 --- a/test/sql-tap/alter.test.lua +++ b/test/sql-tap/alter.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(43) +test:plan(50) test:do_execsql_test( "alter-1.1", @@ -517,6 +517,61 @@ test:do_catchsql_test( -- }) +test:do_test( + "alter-8.1.0", + function() + format = {} + format[1] = { name = 'id', type = 'scalar'} + format[2] = { name = 'f2', type = 'scalar'} + s = box.schema.create_space('T', {format = format}) + end, + {}) + +test:do_catchsql_test( + "alter-8.1.1", + [[ + ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY("id"); + ]], { + 0 + }) + +test:do_test( + "alter-8.1.2", + function() + return box.space.T.index[0].id + end, 0) + +test:do_catchsql_test( + "alter-8.2", + [[ + ALTER TABLE t ADD CONSTRAINT pk1 PRIMARY KEY("f2"); + ]], { + 1, "multiple primary keys are not allowed" + }) + +test:do_catchsql_test( + "alter-8.3.1", + [[ + ALTER TABLE t ADD CONSTRAINT i1 UNIQUE("f2"); + ]], { + 0 + }) + +test:do_test( + "alter-8.3.2", + function() + i = box.space.T.index[1] + return i.id + end, 1) + +test:do_catchsql_test( + "alter-8.4", + [[ + DROP INDEX i1 ON t; + DROP INDEX pk ON t; + ]], { + 0 +}) -- Commented due to #2953 -- diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua index 5f6706a40..b0e3ece18 100755 --- a/test/sql-tap/index1.test.lua +++ b/test/sql-tap/index1.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(72) +test:plan(73) --!./tcltestrunner.lua -- 2001 September 15 @@ -1034,4 +1034,13 @@ test:do_catchsql_test( 1, "can not add a secondary key before primary" }) +test:do_catchsql_test( + "alter-8.2", + [[ + ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY("id"); + CREATE UNIQUE INDEX i ON t("id"); + ]], { + 0 +}) + test:finish_test() -- 2.15.1