From: Roman Khabibov <roman.habibov@tarantool.org> To: Nikita Pettik <korablev@tarantool.org> Cc: tarantool-patches@dev.tarantool.org Subject: Re: [Tarantool-patches] [PATCH 2/2] sql: support constraint drop Date: Sun, 16 Feb 2020 13:24:16 +0300 [thread overview] Message-ID: <A8CFD22E-4E03-44FC-AAC5-E1DFDAD378A8@tarantool.org> (raw) In-Reply-To: <20200211165624.GB8095@tarantool.org> Hi! Thanks for the review. > On Feb 11, 2020, at 19:56, Nikita Pettik <korablev@tarantool.org> wrote: > > On 01 Feb 20:36, Roman Khabibov wrote: >> Hi! Thanks for the review. >> >>>>>> diff --git a/src/box/sql/build.c b/src/box/sql/build.c >>>>>> index bc50ecbfa..7c4b5760e 100644 >>>>>> --- a/src/box/sql/build.c >>>>>> +++ b/src/box/sql/build.c >>>>>> @@ -1469,6 +1469,29 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name, >>>>>> sql_table_delete_from(parse, src_list, where); >>>>>> @@ -1488,17 +1511,6 @@ vdbe_emit_fk_constraint_drop(struct Parse *parse_context, char *constraint_name, >>>>>> sqlVdbeAddOp4(vdbe, OP_String8, 0, key_reg, 0, constraint_name, >>>>>> P4_DYNAMIC); >>>>>> sqlVdbeAddOp2(vdbe, OP_Integer, child_id, key_reg + 1); >>>>>> - const char *error_msg = >>>>>> - tt_sprintf(tnt_errcode_desc(ER_NO_SUCH_CONSTRAINT), >>>>>> - constraint_name); >>>>>> - if (vdbe_emit_halt_with_presence_test(parse_context, >>>>>> - BOX_FK_CONSTRAINT_ID, 0, >>>>>> - key_reg, 2, ER_NO_SUCH_CONSTRAINT, >>>>>> - error_msg, false, >>>>>> - OP_Found) != 0) { >>>>>> - sqlDbFree(parse_context->db, constraint_name); >>>>>> - return; >>>>>> - } >>>>> >>>>> Why did you drop this check? >>>>> >>>>>> sqlVdbeAddOp3(vdbe, OP_MakeRecord, key_reg, 2, key_reg + 2); >>>>>> sqlVdbeAddOp2(vdbe, OP_SDelete, BOX_FK_CONSTRAINT_ID, key_reg + 2); >>>>>> VdbeComment((vdbe, "Delete FK constraint %s", constraint_name)); >>>>>> @@ -1523,12 +1535,6 @@ vdbe_emit_ck_constraint_drop(struct Parse *parser, const char *ck_name, >>>>>> sqlVdbeAddOp2(v, OP_Integer, space_id, key_reg); >>>>>> sqlVdbeAddOp4(v, OP_String8, 0, key_reg + 1, 0, >>>>>> sqlDbStrDup(db, ck_name), P4_DYNAMIC); >>>>>> - const char *error_msg = >>>>>> - tt_sprintf(tnt_errcode_desc(ER_NO_SUCH_CONSTRAINT), ck_name); >>>>>> - if (vdbe_emit_halt_with_presence_test(parser, BOX_CK_CONSTRAINT_ID, 0, >>>>>> - key_reg, 2, ER_NO_SUCH_CONSTRAINT, >>>>>> - error_msg, false, OP_Found) != 0) >>>>>> - return; >>>>> >>>>> Same question. >>>> I dropped these checks, because I thought that the data are consistent. These two >>>> functions are called when: >>>> 1) DROP TABLE. If the constraint exist in struct space, then the corresponding tuple >>>> exists in _ck/_fk system space too. Therefore this error can not occur in box. >>>> >>>> 2) ALTER TABLE DROP CONSTRAINT. Analogically, I check the constraint for existence in >>>> sql_drop_constraint() and throw error on parsing level. >>> >>> Data can be consistent at the parsing stage, but be inconsistent during >>> execution. Especially taking into account 'prepare' mechanism having >>> been recently introduced. So please put these checks back. >> Done. > > In sql_drop_constraint() in index dropping branch there's still no > vdbe_emit_halt_with_presence_test() call. Please add it there as well. + case CONSTRAINT_TYPE_PK: + case CONSTRAINT_TYPE_UNIQUE: { + uint32_t index_id = box_index_id_by_name(space->def->id, name, + strlen(name)); + /* + * We have already verified, that this index + * exists, so we don't check index_id for + * BOX_ID_NIL. + */ + assert(index_id != BOX_ID_NIL); + int record_reg = ++parse_context->nMem; + int space_id_reg = ++parse_context->nMem; + int index_id_reg = ++parse_context->nMem; + sqlVdbeAddOp2(v, OP_Integer, space->def->id, space_id_reg); + sqlVdbeAddOp2(v, OP_Integer, index_id, index_id_reg); + const char *error_msg = + tt_sprintf(tnt_errcode_desc(ER_NO_SUCH_CONSTRAINT), + id->name, space->def->name); + if (vdbe_emit_halt_with_presence_test(parse_context, + BOX_INDEX_ID, 0, + space_id_reg, 2, + ER_NO_SUCH_CONSTRAINT, + error_msg, false, + OP_Found) != 0) + return; + sqlVdbeAddOp3(v, OP_MakeRecord, space_id_reg, 2, record_reg); + sqlVdbeAddOp2(v, OP_SDelete, BOX_INDEX_ID, record_reg); + break; + } >> diff --git a/src/box/sql/build.c b/src/box/sql/build.c >> index d9bf8de91..27963c455 100644 >> --- a/src/box/sql/build.c >> +++ b/src/box/sql/build.c >> @@ -2052,35 +2052,74 @@ fk_constraint_change_defer_mode(struct Parse *parse_context, bool is_deferred) >> is_deferred; >> } >> >> +/** >> + * Emit code to drop the entry from _index or _ck_contstraint or >> + * _fk_constraint space corresponding with the constraint type. >> + */ > > As a rule we place comments near function declaration. /** + * Emit code to drop the entry from _index or _ck_contstraint or + * _fk_constraint space corresponding with the constraint type. + * * Function called from parser to handle * <ALTER TABLE table DROP CONSTRAINT constraint> SQL statement. * * @param parse_context Parsing context. */ void -sql_drop_foreign_key(struct Parse *parse_context); +sql_drop_constraint(struct Parse *parse_context); >> void >> -sql_drop_foreign_key(struct Parse *parse_context) >> +sql_drop_constraint(struct Parse *parse_context) >> { >> } >> - vdbe_emit_fk_constraint_drop(parse_context, constraint_name, >> - child->def); >> + struct Vdbe *v = sqlGetVdbe(parse_context); >> + assert(v != NULL); >> + assert(id->type < constraint_type_MAX); >> + switch (id->type) { >> + case CONSTRAINT_TYPE_PK: >> + case CONSTRAINT_TYPE_UNIQUE: { >> + uint32_t index_id = box_index_id_by_name(space->def->id, name, >> + strlen(name)); >> + /* >> + * We have already verified, that this index >> + * exists, so we don't check index_id for >> + * BOX_ID_NIL. >> + */ >> + assert(index_id != BOX_ID_NIL); >> + int record_reg = ++parse_context->nMem; >> + int space_id_reg = ++parse_context->nMem; >> + int index_id_reg = ++parse_context->nMem; >> + sqlVdbeAddOp2(v, OP_Integer, space->def->id, space_id_reg); >> + sqlVdbeAddOp2(v, OP_Integer, index_id, index_id_reg); >> + sqlVdbeAddOp3(v, OP_MakeRecord, space_id_reg, 2, record_reg); >> + sqlVdbeAddOp2(v, OP_SDelete, BOX_INDEX_ID, record_reg); >> + break; >> + } >> + case CONSTRAINT_TYPE_FK: >> + vdbe_emit_fk_constraint_drop(parse_context, name, space->def); >> + break; >> + case CONSTRAINT_TYPE_CK: >> + vdbe_emit_ck_constraint_drop(parse_context, name, space->def); >> + break; >> + default: >> + unreachable(); >> + } >> /* >> * We account changes to row count only if drop of >> - * foreign keys take place in a separate >> - * ALTER TABLE DROP CONSTRAINT statement, since whole >> - * DROP TABLE always returns 1 (one) as a row count. >> + * constraints take place in a separate ALTER TABLE DROP >> + * CONSTRAINT statement, since whole DROP TABLE always >> + * returns 1 (one) as a row count. >> */ > > This comment doesn't look like related to this function: > sql_drop_constraint() is never called within drop table statement. Dropped. >> - struct Vdbe *v = sqlGetVdbe(parse_context); >> sqlVdbeCountChanges(v); >> sqlVdbeChangeP5(v, OPFLAG_NCHANGE); >> } >> diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y >> index cfe1c0012..1a0e89703 100644 >> --- a/src/box/sql/parse.y >> +++ b/src/box/sql/parse.y >> @@ -1763,9 +1763,9 @@ cmd ::= alter_table_start(A) RENAME TO nm(N). { >> } >> >> cmd ::= ALTER TABLE fullname(X) DROP CONSTRAINT nm(Z). { >> - drop_fk_def_init(&pParse->drop_fk_def, X, &Z, false); >> + drop_constraint_def_init(&pParse->drop_constraint_def, X, &Z, false); >> pParse->initiateTTrans = true; >> - sql_drop_foreign_key(pParse); >> + sql_drop_constraint(pParse); >> } >> >> cmd ::= alter_table_start(A) enable(E) CHECK CONSTRAINT nm(Z). { >> diff --git a/src/box/sql/parse_def.h b/src/box/sql/parse_def.h >> index 2f433e4c0..e6d45b256 100644 >> --- a/src/box/sql/parse_def.h >> +++ b/src/box/sql/parse_def.h >> @@ -159,10 +159,6 @@ enum entity_type { >> ENTITY_TYPE_TRIGGER, >> ENTITY_TYPE_CK, >> ENTITY_TYPE_FK, >> - /** >> - * For assertion checks that constraint definition is >> - * created before initialization of a term constraint. >> - */ > > Why did you drop this comment? Returned. I mistakenly thought, that it isn’t only for asserts. commit 490b34dacc19cb9ea7fc36f52dec023fb20d1b3b Author: Roman Khabibov <roman.habibov@tarantool.org> Date: Thu Jan 2 20:13:36 2020 +0300 sql: support constraint drop Extend <ALTER TABLE> statement to drop table constraints by their names. Closes #4120 @TarantoolBot document Title: Drop table constraints in SQL Now, it is possible to drop table constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) using <ALTER TABLE table_name DROP CONSTRAINT constraint_name> statement by their names. For example: tarantool> box.execute([[CREATE TABLE test ( a INTEGER PRIMARY KEY, b INTEGER, CONSTRAINT cnstr CHECK (a >= 0) );]]) --- - row_count: 1 ... tarantool> box.execute('ALTER TABLE test DROP CONSTRAINT cnstr;') --- - row_count: 1 ... The same for all the other constraints. diff --git a/src/box/constraint_id.h b/src/box/constraint_id.h index 21f067cdc..ed4f37426 100755 --- a/src/box/constraint_id.h +++ b/src/box/constraint_id.h @@ -40,6 +40,7 @@ enum constraint_type { CONSTRAINT_TYPE_UNIQUE, CONSTRAINT_TYPE_FK, CONSTRAINT_TYPE_CK, + constraint_type_MAX, }; extern const char *constraint_type_strs[]; diff --git a/src/box/sql/build.c b/src/box/sql/build.c index d9bf8de91..76ad79350 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -2052,35 +2052,78 @@ fk_constraint_change_defer_mode(struct Parse *parse_context, bool is_deferred) is_deferred; } +/** + * Emit code to drop the entry from _index or _ck_contstraint or + * _fk_constraint space corresponding with the constraint type. + */ void -sql_drop_foreign_key(struct Parse *parse_context) +sql_drop_constraint(struct Parse *parse_context) { - struct drop_entity_def *drop_def = &parse_context->drop_fk_def.base; - assert(drop_def->base.entity_type == ENTITY_TYPE_FK); + struct drop_entity_def *drop_def = + &parse_context->drop_constraint_def.base; + assert(drop_def->base.entity_type == ENTITY_TYPE_CONSTRAINT); assert(drop_def->base.alter_action == ALTER_ACTION_DROP); const char *table_name = drop_def->base.entity_name->a[0].zName; assert(table_name != NULL); - struct space *child = space_by_name(table_name); - if (child == NULL) { + struct space *space = space_by_name(table_name); + if (space == NULL) { diag_set(ClientError, ER_NO_SUCH_SPACE, table_name); parse_context->is_aborted = true; return; } - char *constraint_name = - sql_name_from_token(parse_context->db, &drop_def->name); - if (constraint_name == NULL) { + char *name = sql_name_from_token(parse_context->db, &drop_def->name); + if (name == NULL) { + parse_context->is_aborted = true; + return; + } + struct constraint_id *id = space_find_constraint_id(space, name); + if (id == NULL) { + diag_set(ClientError, ER_NO_SUCH_CONSTRAINT, name, table_name); parse_context->is_aborted = true; return; } - vdbe_emit_fk_constraint_drop(parse_context, constraint_name, - child->def); - /* - * We account changes to row count only if drop of - * foreign keys take place in a separate - * ALTER TABLE DROP CONSTRAINT statement, since whole - * DROP TABLE always returns 1 (one) as a row count. - */ struct Vdbe *v = sqlGetVdbe(parse_context); + assert(v != NULL); + assert(id->type < constraint_type_MAX); + switch (id->type) { + case CONSTRAINT_TYPE_PK: + case CONSTRAINT_TYPE_UNIQUE: { + uint32_t index_id = box_index_id_by_name(space->def->id, name, + strlen(name)); + /* + * We have already verified, that this index + * exists, so we don't check index_id for + * BOX_ID_NIL. + */ + assert(index_id != BOX_ID_NIL); + int record_reg = ++parse_context->nMem; + int space_id_reg = ++parse_context->nMem; + int index_id_reg = ++parse_context->nMem; + sqlVdbeAddOp2(v, OP_Integer, space->def->id, space_id_reg); + sqlVdbeAddOp2(v, OP_Integer, index_id, index_id_reg); + const char *error_msg = + tt_sprintf(tnt_errcode_desc(ER_NO_SUCH_CONSTRAINT), + id->name, space->def->name); + if (vdbe_emit_halt_with_presence_test(parse_context, + BOX_INDEX_ID, 0, + space_id_reg, 2, + ER_NO_SUCH_CONSTRAINT, + error_msg, false, + OP_Found) != 0) + return; + sqlVdbeAddOp3(v, OP_MakeRecord, space_id_reg, 2, record_reg); + sqlVdbeAddOp2(v, OP_SDelete, BOX_INDEX_ID, record_reg); + break; + } + case CONSTRAINT_TYPE_FK: + vdbe_emit_fk_constraint_drop(parse_context, name, space->def); + break; + case CONSTRAINT_TYPE_CK: + vdbe_emit_ck_constraint_drop(parse_context, name, space->def); + break; + default: + unreachable(); + } sqlVdbeCountChanges(v); sqlVdbeChangeP5(v, OPFLAG_NCHANGE); } diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index cfe1c0012..1a0e89703 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1763,9 +1763,9 @@ cmd ::= alter_table_start(A) RENAME TO nm(N). { } cmd ::= ALTER TABLE fullname(X) DROP CONSTRAINT nm(Z). { - drop_fk_def_init(&pParse->drop_fk_def, X, &Z, false); + drop_constraint_def_init(&pParse->drop_constraint_def, X, &Z, false); pParse->initiateTTrans = true; - sql_drop_foreign_key(pParse); + sql_drop_constraint(pParse); } cmd ::= alter_table_start(A) enable(E) CHECK CONSTRAINT nm(Z). { diff --git a/src/box/sql/parse_def.h b/src/box/sql/parse_def.h index 2f433e4c0..cb0ecd2fc 100644 --- a/src/box/sql/parse_def.h +++ b/src/box/sql/parse_def.h @@ -265,7 +265,7 @@ struct drop_trigger_def { struct drop_entity_def base; }; -struct drop_fk_def { +struct drop_constraint_def { struct drop_entity_def base; }; @@ -408,11 +408,12 @@ drop_trigger_def_init(struct drop_trigger_def *drop_trigger_def, } static inline void -drop_fk_def_init(struct drop_fk_def *drop_fk_def, struct SrcList *parent_name, - struct Token *name, bool if_exist) +drop_constraint_def_init(struct drop_constraint_def *drop_constraint_def, + struct SrcList *parent_name, struct Token *name, + bool if_exist) { - drop_entity_def_init(&drop_fk_def->base, parent_name, name, if_exist, - ENTITY_TYPE_FK); + drop_entity_def_init(&drop_constraint_def->base, parent_name, name, + if_exist, ENTITY_TYPE_CONSTRAINT); } static inline void diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index d1fcf4761..1579cc92e 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -2237,7 +2237,7 @@ struct Parse { struct create_trigger_def create_trigger_def; struct create_view_def create_view_def; struct rename_entity_def rename_entity_def; - struct drop_fk_def drop_fk_def; + struct drop_constraint_def drop_constraint_def; struct drop_index_def drop_index_def; struct drop_table_def drop_table_def; struct drop_trigger_def drop_trigger_def; @@ -3741,13 +3741,16 @@ void sql_create_foreign_key(struct Parse *parse_context); /** + * Emit code to drop the entry from _index or _ck_contstraint or + * _fk_constraint space corresponding with the constraint type. + * * Function called from parser to handle * <ALTER TABLE table DROP CONSTRAINT constraint> SQL statement. * * @param parse_context Parsing context. */ void -sql_drop_foreign_key(struct Parse *parse_context); +sql_drop_constraint(struct Parse *parse_context); /** * Now our SQL implementation can't operate on spaces which diff --git a/test/sql/constraint.result b/test/sql/constraint.result index 1585c2327..2847fe8ef 100644 --- a/test/sql/constraint.result +++ b/test/sql/constraint.result @@ -291,6 +291,67 @@ box.execute('CREATE UNIQUE INDEX d ON t2(i);') | - Index 'D' already exists in space 'T2' | ... +-- +-- gh-4120: Ensure that <ALTER TABLE DROP CONSTRAINT> works +-- correctly for each type of constraint. +-- +-- Drop UNIQUE constraint. +box.space.T2.index.E ~= nil + | --- + | - true + | ... +box.execute('ALTER TABLE t2 DROP CONSTRAINT e;') + | --- + | - row_count: 1 + | ... +box.space.T2.index.E == nil + | --- + | - true + | ... +-- Drop PRIMARY KEY constraint named "C". +box.execute('DROP INDEX d ON t2;') + | --- + | - row_count: 1 + | ... +box.space.T2.index.C ~= nil + | --- + | - true + | ... +box.execute('ALTER TABLE t2 DROP CONSTRAINT c;') + | --- + | - row_count: 1 + | ... +box.space.T2.index.C == nil + | --- + | - true + | ... +-- Drop CHECK constraint. +box.execute('ALTER TABLE t2 ADD CONSTRAINT e CHECK(i > 0);') + | --- + | - row_count: 1 + | ... +box.space.T2.ck_constraint.E ~= nil + | --- + | - true + | ... +box.execute('ALTER TABLE t2 DROP CONSTRAINT e;') + | --- + | - row_count: 1 + | ... +box.space.T2.ck_constraint.E == nil + | --- + | - true + | ... +-- Drop FOREIGN KEY constraint. +box.execute('ALTER TABLE t2 ADD CONSTRAINT e FOREIGN KEY(i) REFERENCES t1(i);') + | --- + | - row_count: 1 + | ... +box.execute('ALTER TABLE t2 DROP CONSTRAINT e;') + | --- + | - row_count: 1 + | ... + -- -- Cleanup. -- diff --git a/test/sql/constraint.test.lua b/test/sql/constraint.test.lua index 163f4309c..12f673dac 100755 --- a/test/sql/constraint.test.lua +++ b/test/sql/constraint.test.lua @@ -131,6 +131,28 @@ box.execute('CREATE UNIQUE INDEX e ON t2(i);') -- uniqueness, index names should be unique in a space. box.execute('CREATE UNIQUE INDEX d ON t2(i);') +-- +-- gh-4120: Ensure that <ALTER TABLE DROP CONSTRAINT> works +-- correctly for each type of constraint. +-- +-- Drop UNIQUE constraint. +box.space.T2.index.E ~= nil +box.execute('ALTER TABLE t2 DROP CONSTRAINT e;') +box.space.T2.index.E == nil +-- Drop PRIMARY KEY constraint named "C". +box.execute('DROP INDEX d ON t2;') +box.space.T2.index.C ~= nil +box.execute('ALTER TABLE t2 DROP CONSTRAINT c;') +box.space.T2.index.C == nil +-- Drop CHECK constraint. +box.execute('ALTER TABLE t2 ADD CONSTRAINT e CHECK(i > 0);') +box.space.T2.ck_constraint.E ~= nil +box.execute('ALTER TABLE t2 DROP CONSTRAINT e;') +box.space.T2.ck_constraint.E == nil +-- Drop FOREIGN KEY constraint. +box.execute('ALTER TABLE t2 ADD CONSTRAINT e FOREIGN KEY(i) REFERENCES t1(i);') +box.execute('ALTER TABLE t2 DROP CONSTRAINT e;') + -- -- Cleanup. --
next prev parent reply other threads:[~2020-02-16 10:24 UTC|newest] Thread overview: 13+ messages / expand[flat|nested] mbox.gz Atom feed top 2020-01-09 10:15 [Tarantool-patches] [PATCH] " Roman Khabibov 2020-01-13 17:00 ` Nikita Pettik 2020-01-24 14:21 ` [Tarantool-patches] [PATCH 2/2] " Roman Khabibov 2020-01-28 17:39 ` Nikita Pettik 2020-02-01 17:36 ` Roman Khabibov 2020-02-11 16:56 ` Nikita Pettik 2020-02-16 10:24 ` Roman Khabibov [this message] 2020-02-20 19:55 ` Nikita Pettik 2020-02-20 23:09 ` [Tarantool-patches] [PATCH] " Vladislav Shpilevoy 2020-02-20 23:36 ` Nikita Pettik 2020-02-29 12:47 ` [Tarantool-patches] [PATCH v2 3/3] " Roman Khabibov 2020-02-29 15:32 ` Vladislav Shpilevoy 2020-03-03 10:13 ` Roman Khabibov
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=A8CFD22E-4E03-44FC-AAC5-E1DFDAD378A8@tarantool.org \ --to=roman.habibov@tarantool.org \ --cc=korablev@tarantool.org \ --cc=tarantool-patches@dev.tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH 2/2] sql: support constraint drop' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox