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 86763283DC for ; Wed, 1 Aug 2018 16:54:37 -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 FaI-4PnkiqEc for ; Wed, 1 Aug 2018 16:54:37 -0400 (EDT) Received: from smtp41.i.mail.ru (smtp41.i.mail.ru [94.100.177.101]) (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 0B53A26ECA for ; Wed, 1 Aug 2018 16:54:35 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: [tarantool-patches] Re: [PATCH 3/5] sql: introduce ADD CONSTRAINT statement From: "n.pettik" In-Reply-To: Date: Wed, 1 Aug 2018 23:54:31 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: References: <092e07d5399d662cd38b755a0403b11cc4dde2a1.1531443603.git.korablev@tarantool.org> <647dcc4b-a2c4-5cc8-c81c-2dbb66ce18ed@tarantool.org> <9B5CA4F9-A6DB-46FF-BE86-CC3900D22D97@tarantool.org> 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: Vladislav Shpilevoy >> Except fixes mentioned below, I disabled (temporary) = sql-tap/alter2.test.lua >> (it checks work of ALTER TABLE ADD CONSTRAINT) for vinyl engine. >> Since in previous patch we prohibited creation of FK constraints on >> non-empty spaces and as condition used =E2=80=98index_size()=E2=80=99, = some tests turn out >> to be flaky. (I don=E2=80=99t think that we should disable these = tests for vinyl, but didn=E2=80=99t >> come up with satisfactory solution.) >=20 > Vinyl indexes has method 'compact' available in Lua. If a space is > logically empty, you can trigger compaction for each index to clean > the space garbage. Ok, but compact is not usable here. Vova suggested to use box.snapshot() +++ b/test/sql-tap/alter2.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test =3D require("sqltester") -test:plan(17) +test:plan(18) =20 -- This suite is aimed to test ALTER TABLE ADD CONSTRAINT statement. -- @@ -87,10 +87,23 @@ test:do_execsql_test( -- }) =20 +test:do_test( + "alter2-1.7.1", + function() + test:execsql([[DELETE FROM t1;]]) + t1 =3D box.space.T1 + if t1.engine ~=3D 'vinyl' then + return + end + box.snapshot() + end, { + -- + -- + }) + test:do_catchsql_test( "alter2-1.8", [[ - DELETE FROM t1; ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES = t1(id); ALTER TABLE t1 ADD CONSTRAINT fk2 FOREIGN KEY (a, b) REFERENCES = t1(b, a); DROP TABLE t1; diff --git a/test/sql-tap/engine.cfg b/test/sql-tap/engine.cfg index 006e31c37..ce9dd68d8 100644 --- a/test/sql-tap/engine.cfg +++ b/test/sql-tap/engine.cfg @@ -2,9 +2,6 @@ "analyze9.test.lua": { "memtx": {"engine": "memtx"} }, - "alter2.test.lua" : { - "memtx": {"engine": "memtx"} - }, >=20 >>>> -/* >>>> - * This function is called before generating code to update or = delete a >>>> - * row contained in table pTab. >>>> - */ >>>> -u32 >>>> -sqlite3FkOldmask(Parse * pParse, /* Parse context */ >>>> - Table * pTab /* Table being modified */ >>>> - ) >>>> +uint32_t >>>> +fkey_old_mask(uint32_t space_id) >>>=20 >>> 25. I think we should calculate this mask once on fk creation >>> like it is done for key_def.columnm_mask. >> In fact, this mask is calculated for whole space (i.e. all of its FK = constraints), >> not for particular FK. So basically, we need to add this mask to = space_def/space >> and update on each FK creation. Is this OK? >=20 > I think it is worth the cost. Lets add it to struct space where > fkeys are stored. Ok, but it leads to additional manipulations in commit/rollback = triggers: +++ b/src/box/alter.cc @@ -3680,6 +3680,50 @@ fkey_grab_by_name(struct rlist *list, const char = *fkey_name) return NULL; } =20 +static void +fkey_set_mask(const struct fkey *fk, uint64_t *parent_mask, + uint64_t *child_mask) +{ + for (uint32_t i =3D 0; i < fk->def->field_count; ++i) { + *parent_mask |=3D = FKEY_MASK(fk->def->links[i].parent_field); + *child_mask |=3D = FKEY_MASK(fk->def->links[i].child_field); + } +} + +/** + * When we discard FK constraint (due to drop or rollback + * trigger), we can't simply unset appropriate bits in mask, + * since other constraints may refer to them as well. Thus, + * we have nothing left to do but completely rebuild mask. + */ +static void +space_reset_fkey_mask(struct space *space) +{ + space->fkey_mask =3D 0; + struct fkey *fk; + rlist_foreach_entry(fk, &space->child_fkey, child_link) { + struct fkey_def *def =3D fk->def; + for (uint32_t i =3D 0; i < def->field_count; ++i) + space->fkey_mask |=3D + FKEY_MASK(def->links[i].child_field); + } + rlist_foreach_entry(fk, &space->parent_fkey, parent_link) { + struct fkey_def *def =3D fk->def; + for (uint32_t i =3D 0; i < def->field_count; ++i) + space->fkey_mask |=3D + FKEY_MASK(def->links[i].parent_field); + } +} + +static void +fkey_update_mask(const struct fkey *fkey) +{ + struct space *child =3D space_by_id(fkey->def->child_id); + space_reset_fkey_mask(child); + struct space *parent =3D space_by_id(fkey->def->parent_id); + space_reset_fkey_mask(parent); +} + @@ -3693,6 +3737,7 @@ on_create_fkey_rollback(struct trigger *trigger, = void *event) rlist_del_entry(fk, parent_link); rlist_del_entry(fk, child_link); fkey_delete(fk); + fkey_update_mask(fk); } /** Return old FK and release memory for the new one. */ @@ -3708,6 +3753,7 @@ on_replace_fkey_rollback(struct trigger *trigger, = void *event) fkey_delete(old_fkey); rlist_add_entry(&child->child_fkey, fk, child_link); rlist_add_entry(&parent->parent_fkey, fk, parent_link); + fkey_update_mask(fk); } /** On rollback of drop simply return back FK to DD. */ @@ -3720,6 +3766,7 @@ on_drop_fkey_rollback(struct trigger *trigger, = void *event) struct space *child =3D = space_by_id(fk_to_restore->def->child_id); rlist_add_entry(&child->child_fkey, fk_to_restore, child_link); rlist_add_entry(&parent->parent_fkey, fk_to_restore, = parent_link); + fkey_set_mask(fk_to_restore, &parent->fkey_mask, = &child->fkey_mask); } @@ -3732,6 +3779,7 @@ on_drop_or_replace_fkey_commit(struct trigger = *trigger, void *event) { (void) event; struct fkey *fk =3D (struct fkey *)trigger->data; + fkey_update_mask(fk); fkey_delete(fk); } @@ -3884,6 +3932,8 @@ on_replace_dd_fk_constraint(struct trigger * /* = trigger*/, void *event) = txn_alter_trigger_new(on_create_fkey_rollback, fkey); txn_on_rollback(txn, on_rollback); + fkey_set_mask(fkey, &parent_space->fkey_mask, + &child_space->fkey_mask); +++ b/src/box/fkey.h @@ -102,6 +102,13 @@ struct fkey { struct rlist child_link; }; =20 +/** + * FIXME: as SQLite legacy temporary we use such mask throught + * SQL code. It should be replaced later with regular + * mask from column_mask.h + */ +#define FKEY_MASK(x) (((x)>31) ? 0xffffffff : ((uint64_t)1<<(x))) +++ b/src/box/space.h @@ -192,6 +192,7 @@ struct space { */ struct rlist parent_fkey; struct rlist child_fkey; + /** + * Mask indicates which fields are involved in foreign + * key constraint checking routine. Includes fields + * of parent constraints as well as child ones. + */ + uint64_t fkey_mask; +++ b/src/box/sql/fkey.c @@ -135,8 +135,6 @@ * generation code to query for this information are: * * fkey_is_required() - Test to see if FK processing is required. - * fkey_old_mask() - Query for the set of required old.* columns. - * * * Externally accessible module functions * -------------------------------------- @@ -682,30 +680,6 @@ fkey_emit_check(struct Parse *parser, struct Table = *tab, int reg_old, } } =20 -#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) - -uint32_t -fkey_old_mask(uint32_t space_id) -{ - uint32_t mask =3D 0; - struct session *user_session =3D current_session(); - if ((user_session->sql_flags & SQLITE_ForeignKeys) !=3D 0) { - struct space *space =3D space_by_id(space_id); - struct fkey *fk; - rlist_foreach_entry(fk, &space->child_fkey, child_link) = { - struct fkey_def *def =3D fk->def; - for (uint32_t i =3D 0; i < def->field_count; = ++i) - mask |=3D = COLUMN_MASK(def->links[i].child_field); - } - rlist_foreach_entry(fk, &space->parent_fkey, = parent_link) { - struct fkey_def *def =3D fk->def; - for (uint32_t i =3D 0; i < def->field_count; = ++i) - mask |=3D = COLUMN_MASK(def->links[i].parent_field); - } - } - return mask; -} - +++ b/src/box/sql/delete.c @@ -444,7 +444,9 @@ sql_generate_row_delete(struct Parse *parse, struct = Table *table, sql_trigger_colmask(parse, trigger_list, 0, 0, TRIGGER_BEFORE | = TRIGGER_AFTER, table, onconf); - mask |=3D fkey_old_mask(table->def->id); + struct space *space =3D space_by_id(table->def->id); + assert(space !=3D NULL); + mask |=3D space->fkey_mask; +++ b/src/box/sql/sqliteInt.h @@ -4767,16 +4767,6 @@ fkey_emit_actions(struct Parse *parser, struct = Table *tab, int reg_old, bool fkey_is_required(uint32_t space_id, const int *changes); =20 -/** - * This function is called before generating code to update or - * delete a row contained in given table. - * - * @param space_id Id of space being modified. - * @retval Mask containing fields to be involved in FK testing. - */ -uint32_t -fkey_old_mask(uint32_t space_id); +++ b/src/box/sql/update.c @@ -433,7 +433,9 @@ sqlite3Update(Parse * pParse, /* The = parser context */ * information is needed */ if (chngPk !=3D 0 || hasFK !=3D 0 || trigger !=3D NULL) { - u32 oldmask =3D hasFK ? fkey_old_mask(pTab->def->id) : = 0; + struct space *space =3D space_by_id(pTab->def->id); + assert(space !=3D NULL); + u32 oldmask =3D hasFK ? space->fkey_mask : 0; >=20 >> diff --git a/extra/mkopcodeh.sh b/extra/mkopcodeh.sh >> index 63ad0d56a..9e97a50f0 100755 >> --- a/extra/mkopcodeh.sh >> +++ b/extra/mkopcodeh.sh >> @@ -220,8 +224,12 @@ while [ "$i" -lt "$nOp" ]; do >> i=3D$((i + 1)) >> done >> max=3D"$cnt" >> +echo "//*************** $max $nOp $mxTk" >=20 > 1. This echo seems to be debug print. >=20 >> @@ -283,11 +303,16 @@ printf '%s\n' "#define OPFLG_OUT2 0x10 = /* out2: P2 is an output */" >> printf '%s\n' "#define OPFLG_OUT3 0x20 /* out3: P3 is an = output */" >> printf '%s\n' "#define OPFLG_INITIALIZER {\\" >> i=3D0 >> -while [ "$i" -le "$max" ]; do >> +while [ "$i" -le "$mxTk" ]; do >> if [ "$((i % 8))" -eq 0 ]; then >> printf '/* %3d */' "$i" >> fi >> - eval "bv=3D\$ARRAY_bv_$i" >> + eval "is_exists=3D\${ARRAY_bv_$i:-}" >=20 > 2. 'is_exists'? I have refactored this changes > slightly on the branch. Sorry, I didn=E2=80=99t review this script, just simply copied it and = include some Alex=E2=80=99s fixes. Also, he promised to review this script = carefully, so I guess it is worth to detach this it from patch-set and send as a separate patch (AFAIK Kirill Y. already done that). >> diff --git a/src/box/sql/alter.c b/src/box/sql/alter.c >> index 8c1c36b9b..0e770272e 100644 >> --- a/src/box/sql/alter.c >> +++ b/src/box/sql/alter.c >> @@ -190,12 +189,6 @@ sqlite3AlterFinishAddColumn(Parse * pParse, = Token * pColDef) >> sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column"); >> return; >> } >> - if ((user_session->sql_flags & SQLITE_ForeignKeys) && = pNew->pFKey >=20 > 3. After we've found that defer fkey flag was checked in the parser I > checked other flags of fkeys. And it emerged that SQLITE_ForeignKeys = is > still used in the parser in such places as sqlite3FkCheck, = fkey_old_mask, > fkey_is_required, sqlite3FkActions, sqlite3GenerateConstraintChecks, > xferOptimization. I think, that we should check it during execution, = not > parsing. It is not? >=20 > Actually it is not the only flag checked during parsing: I've found = also > SQLITE_CountRows, SQLITE_IgnoreChecks (btw what is the difference = between > ignore_checks and !foreign_keys?), SQLITE_RecTriggers, = SQLITE_FullColNames, > SQLITE_ShortColNames, SQLITE_EnableTrigger, SQLITE_ReverseOrder. >=20 > And we have 3 options as I understand: >=20 > * somehow merge these things into VDBE; >=20 > * when we will have PREPARE API, rebuild prepared statements of > the user session on change of any of these flags; >=20 > * we announce these flags as parsing stage only and if you changed = them, > the already prepared statements would not be affected. Well, personally the most I like second variant. First one also looks = OK, but need to investigate whether it would be easy to integrate checks = into VDBE or not. Actually, if I could I would throw away these runtime options=E2=80= =A6 Anyway, lets ask for advise. Then, I am going to open an issue. Also, I=E2=80=99ve noticed that during previous review removal of = SQLITE_DeferFK from sql/fkey.c was a mistake: test/sql/transitive-transaction.test.lua = fails without routine connected with it. So, I returned back checks on this macros: +++ b/src/box/sql/fkey.c @@ -261,8 +261,10 @@ fkey_lookup_parent(struct Parse *parse_context, = struct space *parent, sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0); sqlite3ReleaseTempReg(parse_context, rec_reg); sqlite3ReleaseTempRange(parse_context, temp_regs, field_count); - if (!fk_def->is_deferred && parse_context->pToplevel =3D=3D NULL = && - !parse_context->isMultiWrite) { + struct session *session =3D current_session(); + if (!fk_def->is_deferred && + (session->sql_flags & SQLITE_DeferFKs) =3D=3D 0 && + parse_context->pToplevel =3D=3D NULL && = !parse_context->isMultiWrite) { @@ -605,8 +607,9 @@ fkey_emit_check(struct Parse *parser, struct Table = *tab, int reg_old, if (changed_cols !=3D NULL && !fkey_parent_is_modified(fk_def, changed_cols)) continue; - if (!fk_def->is_deferred && parser->pToplevel =3D=3D = NULL && - !parser->isMultiWrite) { + if (!fk_def->is_deferred && + (user_session->sql_flags & SQLITE_DeferFKs) =3D=3D 0 = && + parser->pToplevel =3D=3D NULL && = !parser->isMultiWrite) { >=20 >> - && pDflt) { >> - sqlite3ErrorMsg(pParse, >> - "Cannot add a REFERENCES column with = non-NULL default value"); >> - return; >> - } >> assert(pNew->def->fields[pNew->def->field_count - 1].is_nullable = =3D=3D >> action_is_nullable(pNew->def->fields[ >> pNew->def->field_count - 1].nullable_action)); >> diff --git a/src/box/sql/build.c b/src/box/sql/build.c >> index 789a628d6..fc097a319 100644 >> --- a/src/box/sql/build.c >> +++ b/src/box/sql/build.c >> + >> +static int >> +resolve_link(struct Parse *parse_context, const struct space_def = *def, >> + const char *field_name, uint32_t *link) >> +{ >> + assert(link !=3D NULL); >> + for (uint32_t j =3D 0; j < def->field_count; ++j) { >> + if (strcmp(field_name, def->fields[j].name) =3D=3D 0) { >> + *link =3D j; >> + return 0; >> + } >> + } >> + sqlite3ErrorMsg(parse_context, "unknown column %s in foreign key = " >> + "definition", field_name); >=20 > 4. ER_CREATE_FK_CONSTRAINT ? +++ b/src/box/sql/build.c @@ -1620,7 +1620,7 @@ vdbe_emit_fkey_create(struct Parse *parse_context, = const struct fkey_def *fk) =20 static int resolve_link(struct Parse *parse_context, const struct space_def *def, - const char *field_name, uint32_t *link) + const char *field_name, uint32_t *link, const char = *fk_name) { assert(link !=3D NULL); for (uint32_t j =3D 0; j < def->field_count; ++j) { @@ -1629,8 +1629,10 @@ resolve_link(struct Parse *parse_context, const = struct space_def *def, return 0; } } - sqlite3ErrorMsg(parse_context, "unknown column %s in foreign key = " - "definition", field_name); + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, fk_name, + tt_sprintf("unknown column %s in foreign key = definition", + field_name)); + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; @@ -1813,7 +1815,8 @@ sqlite3EndTable(Parse * pParse, /* Parse context = */ for (uint32_t i =3D 0; i < = fk->field_count; ++i) { if (resolve_link(pParse, p->def, = cols->a[i].zName, - = &fk->links[i].parent_field) !=3D 0) + = &fk->links[i].parent_field, + fk->name) !=3D = 0) return; @@ -2463,7 +2471,8 @@ sql_create_foreign_key(struct Parse = *parse_context, struct SrcList *child, } if (resolve_link(parse_context, new_tab->def, child_cols->a[i].zName, - &fk->links[i].child_field) !=3D = 0) + &fk->links[i].child_field, + constraint_name) !=3D 0) >=20 >> + return -1; >> +} >> + >> /* >> * This routine is called to report the final ")" that terminates >> * a CREATE TABLE statement. >> @@ -1720,6 +1803,43 @@ sqlite3EndTable(Parse * pParse, /* Parse = context */ >> /* Reparse everything to update our internal data = structures */ >> parseTableSchemaRecord(pParse, iSpaceId, zStmt); = /* consumes zStmt */ >> + >> + /* Code creation of FK constraints, if any. */ >> + struct fkey_parse *fk_parse; >> + rlist_foreach_entry(fk_parse, &pParse->new_fkey, link) { >> + struct fkey_def *fk =3D fk_parse->fkey; >> + if (fk_parse->selfref_cols !=3D NULL) { >> + struct ExprList *cols =3D = fk_parse->selfref_cols; >> + for (uint32_t i =3D 0; i < = fk->field_count; ++i) { >> + if (resolve_link(pParse, p->def, >> + = cols->a[i].zName, >> + = &fk->links[i].parent_field) !=3D 0) >> + return; >> + } >> + fk->parent_id =3D iSpaceId; >> + } else if (fk_parse->is_self_referenced) { >> + struct Index *pk =3D = sqlite3PrimaryKeyIndex(p); >> + if (pk->def->key_def->part_count !=3D >> + fk->field_count) { >> + diag_set(ClientError, >> + = ER_CREATE_FK_CONSTRAINT, >> + fk->name, "number of = columns " >> + "in foreign key does = not " >> + "match the number of = columns " >> + "in the referenced = table"); >=20 > 5. However, this message was 3 times duplicated as I said. Now it > is 2: here and in sql_create_foreign_key. Indeed, I was wrong.. >=20 > 6. It does not match the problem. You check for field count > in primary index, not in the whole table. So the message should be > like "... the number of columns in the referenced table's primary = index" > or something. @@ -1826,7 +1830,8 @@ sqlite3EndTable(Parse * pParse, /* Parse context = */ fk->name, "number of = columns " "in foreign key does = not " "match the number of = columns " - "in the referenced = table"); + "in the primary index = of " + "referenced table=E2=80=9D= ); @@ -2400,8 +2408,8 @@ sql_create_foreign_key(struct Parse = *parse_context, struct SrcList *child, if (constraint_name =3D=3D NULL) goto exit_create_fk; const char *error_msg =3D "number of columns in foreign key does = not " - "match the number of columns in the " - "referenced table"; + "match the number of columns in the = primary " + "index of referenced table"; >> +static int >> +columnno_by_name(struct Parse *parse_context, const struct space = *space, >> + const char *column_name, uint32_t *colno) >> +{ >> + assert(colno !=3D NULL); >> + uint32_t column_len =3D strlen(column_name); >> + if (tuple_fieldno_by_name(space->def->dict, column_name, = column_len, >> + field_name_hash(column_name, = column_len), >> + colno) !=3D 0) { >> + sqlite3ErrorMsg(parse_context, >> + "table \"%s\" doesn't feature column = %s", >> + space->def->name, column_name); >=20 > 7. diag_set? @@ -2270,21 +2274,23 @@ sql_drop_table(struct Parse *parse_context, = struct SrcList *table_name_list, * @param space Space which column belongs to. * @param column_name Name of column to investigate. * @param[out] colno Found name of column. + * @param fk_name Name of FK constraint to be created. * * @retval 0 on success, -1 on fault. */ static int columnno_by_name(struct Parse *parse_context, const struct space = *space, - const char *column_name, uint32_t *colno) + const char *column_name, uint32_t *colno, const char = *fk_name) { assert(colno !=3D NULL); uint32_t column_len =3D strlen(column_name); if (tuple_fieldno_by_name(space->def->dict, column_name, = column_len, field_name_hash(column_name, = column_len), colno) !=3D 0) { - sqlite3ErrorMsg(parse_context, - "table \"%s\" doesn't feature column = %s", - space->def->name, column_name); + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, fk_name, + tt_sprintf("foreign key refers to nonexistent = field %s", + column_name)); + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; return -1; } @@ -2445,7 +2451,8 @@ sql_create_foreign_key(struct Parse = *parse_context, struct SrcList *child, } else if (!is_self_referenced && columnno_by_name(parse_context, parent_space, parent_cols->a[i].zName, - &fk->links[i].parent_field) = !=3D 0) { + &fk->links[i].parent_field, + constraint_name) !=3D 0) { @@ -2463,12 +2470,14 @@ sql_create_foreign_key(struct Parse = *parse_context, struct SrcList *child, goto exit_create_fk; /* In case of ALTER parent table must exist. */ } else if (columnno_by_name(parse_context, child_space, child_cols->a[i].zName, - &fk->links[i].child_field) = !=3D 0) { + &fk->links[i].child_field, + constraint_name) !=3D 0) { >=20 >> + return -1; >> + } >> + return 0; >> +} >> + >> void >> -sqlite3CreateForeignKey(Parse * pParse, /* Parsing context */ >> - ExprList * pFromCol, /* Columns in this table = that point to other table */ >> - Token * pTo, /* Name of the other table */ >> - ExprList * pToCol, /* Columns in the other = table */ >> - int flags /* Conflict resolution = algorithms. */ >> - ) >> +sql_create_foreign_key(struct Parse *parse_context, struct SrcList = *child, >> + struct Token *constraint, struct ExprList = *child_cols, >> + struct Token *parent, struct ExprList = *parent_cols, >> + bool is_deferred, int actions) >=20 > 8. We have problems with actions. Lets take a look at the parser: >=20 > %type refarg {struct {int value; int mask;}} > refarg(A) ::=3D MATCH matcharg(X). { A.value =3D X; A.mask =3D= 0xff0000; } > refarg(A) ::=3D ON INSERT refact. { A.value =3D 0; = A.mask =3D 0x000000; } > refarg(A) ::=3D ON DELETE refact(X). { A.value =3D X; = A.mask =3D 0x0000ff; } > refarg(A) ::=3D ON UPDATE refact(X). { A.value =3D X<<8; = A.mask =3D 0x00ff00; } >=20 > It builds actions mask. Then lets look at the actions decoding: >=20 > fk->match =3D (enum fkey_match) ((actions >> 16) & 0xff); > fk->on_update =3D (enum fkey_action) ((actions >> 8) & 0xff); > fk->on_delete =3D (enum fkey_action) (actions & 0xff); >=20 > As you can see, it is expected, that the mask has the layout > {on_delete, on_update, match}, each field is byte. >=20 > But the parser stores them as {on_delete/match, on_update} now. >=20 > So I've found this test that ignores my MATCH: >=20 > box.cfg{} > box.sql.execute('CREATE TABLE test (id int primary key, '.. > 'a int unique, b int unique)') > box.sql.execute('CREATE TABLE test2 (id int primary key'.. > ', a int references test(a) ON DELETE SET NULL = MATCH FULL)') > box.space._fk_constraint:select{} > --- > - - ['FK_CONSTRAINT_1_TEST2', 513, 512, false, 'simple', = 'cascade', 'no_action', [ > {'child': 1, 'parent': 1}]] >=20 > As you can see, I specified MATCH as FULL, but it turned into SIMPLE. = I do > not know what MATCH even means, but it is stored incorrectly. I guess this will fix it (it looks like it was before previous review): +++ b/src/box/sql/parse.y @@ -300,7 +300,7 @@ autoinc(X) ::=3D AUTOINCR. {X =3D 1;} refargs(A) ::=3D . { A =3D FKEY_NO_ACTION; } refargs(A) ::=3D refargs(A) refarg(Y). { A =3D (A & ~Y.mask) | Y.value; = } %type refarg {struct {int value; int mask;}} -refarg(A) ::=3D MATCH matcharg(X). { A.value =3D X; A.mask =3D = 0xff0000; } +refarg(A) ::=3D MATCH matcharg(X). { A.value =3D X<<16; A.mask =3D = 0xff0000; } +++ b/test/sql/foreign-keys.test.lua @@ -152,5 +152,15 @@ box.space._fk_constraint:select({'fk_1', = child_id})[1]['is_deferred'] box.space.CHILD:drop() box.space.PARENT:drop() =20 --- Clean-up SQL DD hash. -test_run:cmd('restart server default with cleanup=3D1') +-- Check that parser correctly handles MATCH, ON DELETE and +-- ON UPDATE clauses. +-- +box.sql.execute('CREATE TABLE tp (id INT PRIMARY KEY, a INT UNIQUE)') +box.sql.execute('CREATE TABLE tc (id INT PRIMARY KEY, a INT REFERENCES = tp(a) ON DELETE SET NULL MATCH FULL)') +box.sql.execute('ALTER TABLE tc ADD CONSTRAINT fk1 FOREIGN KEY (id) = REFERENCES tp(id) MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET NULL') +box.space._fk_constraint:select{} +box.sql.execute('DROP TABLE tc') +box.sql.execute('DROP TABLE tp') + +--- Clean-up SQL DD hash. +-test_run:cmd('restart server default with cleanup=3D1=E2=80=99) +++ b/test/sql/foreign-keys.result @@ -340,5 +340,29 @@ box.space.CHILD:drop() box.space.PARENT:drop() --- ... --- Clean-up SQL DD hash. -test_run:cmd('restart server default with cleanup=3D1') +-- Check that parser correctly handles MATCH, ON DELETE and +-- ON UPDATE clauses. +-- +box.sql.execute('CREATE TABLE tp (id INT PRIMARY KEY, a INT UNIQUE)') +--- +... +box.sql.execute('CREATE TABLE tc (id INT PRIMARY KEY, a INT REFERENCES = tp(a) ON DELETE SET NULL MATCH FULL)') +--- +... +box.sql.execute('ALTER TABLE tc ADD CONSTRAINT fk1 FOREIGN KEY (id) = REFERENCES tp(id) MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET NULL') +--- +... +box.space._fk_constraint:select{} +--- +- - ['FK1', 518, 517, false, 'partial', 'cascade', 'set_null', = [{'child': 0, 'parent': 0}]] + - ['FK_CONSTRAINT_1_TC', 518, 517, false, 'full', 'set_null', = 'no_action', [{'child': 1, + 'parent': 1}]] +... +box.sql.execute('DROP TABLE tc') +--- +... +box.sql.execute('DROP TABLE tp') +--- +... +--- Clean-up SQL DD hash. +-test_run:cmd('restart server default with cleanup=3D1') Updated patch: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index 1ec153815..6a6f96f53 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -159,6 +159,7 @@ static Keyword aKeywordTable[] =3D { { "FOR", "TK_FOR", TRIGGER, true = }, { "FOREIGN", "TK_FOREIGN", FKEY, true = }, { "FROM", "TK_FROM", ALWAYS, true = }, + { "FULL", "TK_FULL", ALWAYS, true = }, { "GLOB", "TK_LIKE_KW", ALWAYS, false = }, { "GROUP", "TK_GROUP", ALWAYS, true = }, { "HAVING", "TK_HAVING", ALWAYS, true = }, @@ -191,6 +192,7 @@ static Keyword aKeywordTable[] =3D { { "OR", "TK_OR", ALWAYS, true = }, { "ORDER", "TK_ORDER", ALWAYS, true = }, { "OUTER", "TK_JOIN_KW", ALWAYS, true = }, + { "PARTIAL", "TK_PARTIAL", ALWAYS, true = }, { "PLAN", "TK_PLAN", EXPLAIN, false = }, { "PRAGMA", "TK_PRAGMA", PRAGMA, true = }, { "PRIMARY", "TK_PRIMARY", ALWAYS, true = }, @@ -210,6 +212,7 @@ static Keyword aKeywordTable[] =3D { { "SAVEPOINT", "TK_SAVEPOINT", ALWAYS, true = }, { "SELECT", "TK_SELECT", ALWAYS, true = }, { "SET", "TK_SET", ALWAYS, true = }, + { "SIMPLE", "TK_SIMPLE", ALWAYS, true = }, { "START", "TK_START", ALWAYS, true = }, { "TABLE", "TK_TABLE", ALWAYS, true = }, { "THEN", "TK_THEN", ALWAYS, true = }, diff --git a/extra/mkopcodeh.sh b/extra/mkopcodeh.sh index 63ad0d56a..5f31f2b7d 100755 --- a/extra/mkopcodeh.sh +++ b/extra/mkopcodeh.sh @@ -35,6 +35,7 @@ set -f # disable pathname expansion =20 currentOp=3D"" nOp=3D0 +mxTk=3D-1 newline=3D"$(printf '\n')" IFS=3D"$newline" while read line; do @@ -106,6 +107,9 @@ while read line; do eval "ARRAY_used_$val=3D1" eval "ARRAY_sameas_$val=3D$sym" eval "ARRAY_def_$val=3D$name" + if [ $val -gt $mxTk ] ; then + mxTk=3D$val + fi fi ;; jump) eval "ARRAY_jump_$name=3D1" ;; @@ -219,9 +223,11 @@ while [ "$i" -lt "$nOp" ]; do fi i=3D$((i + 1)) done -max=3D"$cnt" +if [ $mxTk -lt $nOp ] ; then + mxTk=3D$nOp +fi i=3D0 -while [ "$i" -lt "$nOp" ]; do +while [ "$i" -le "$mxTk" ]; do eval "used=3D\${ARRAY_used_$i:-}" if [ -z "$used" ]; then eval "ARRAY_def_$i=3DOP_NotUsed_$i" @@ -251,9 +257,19 @@ done # Generate the bitvectors: ARRAY_bv_0=3D0 i=3D0 -while [ "$i" -le "$max" ]; do +while [ "$i" -le "$mxTk" ]; do + eval "is_existing=3D\${ARRAY_def_$i:-}" + if [ ! -n "$is_existing" ] ; then + i=3D$((i + 1)) + continue + fi eval "name=3D\$ARRAY_def_$i" x=3D0 + eval "is_existing=3D\${ARRAY_jump_$name:-}" + if [ ! -n "$is_existing" ] ; then + i=3D$((i + 1)) + continue + fi eval "jump=3D\$ARRAY_jump_$name" eval "in1=3D\$ARRAY_in1_$name" eval "in2=3D\$ARRAY_in2_$name" @@ -283,11 +299,16 @@ printf '%s\n' "#define OPFLG_OUT2 0x10 /* = out2: P2 is an output */" printf '%s\n' "#define OPFLG_OUT3 0x20 /* out3: P3 is an = output */" printf '%s\n' "#define OPFLG_INITIALIZER {\\" i=3D0 -while [ "$i" -le "$max" ]; do +while [ "$i" -le "$mxTk" ]; do if [ "$((i % 8))" -eq 0 ]; then printf '/* %3d */' "$i" fi - eval "bv=3D\$ARRAY_bv_$i" + eval "is_existing=3D\${ARRAY_bv_$i:-}" + if [ ! -n "$is_existing" ] ; then + bv=3D0 + else + eval "bv=3D\$ARRAY_bv_$i" + fi printf ' 0x%02x,' "$bv" if [ "$((i % 8))" -eq 7 ]; then printf '%s\n' "\\" diff --git a/src/box/alter.cc b/src/box/alter.cc index 5b55bfd7a..6b9e29470 100644 --- a/src/box/alter.cc +++ b/src/box/alter.cc @@ -3660,6 +3660,50 @@ fkey_grab_by_name(struct rlist *list, const char = *fkey_name) return NULL; } =20 +static void +fkey_set_mask(const struct fkey *fk, uint64_t *parent_mask, + uint64_t *child_mask) +{ + for (uint32_t i =3D 0; i < fk->def->field_count; ++i) { + *parent_mask |=3D = FKEY_MASK(fk->def->links[i].parent_field); + *child_mask |=3D = FKEY_MASK(fk->def->links[i].child_field); + } +} + +/** + * When we discard FK constraint (due to drop or rollback + * trigger), we can't simply unset appropriate bits in mask, + * since other constraints may refer to them as well. Thus, + * we have nothing left to do but completely rebuild mask. + */ +static void +space_reset_fkey_mask(struct space *space) +{ + space->fkey_mask =3D 0; + struct fkey *fk; + rlist_foreach_entry(fk, &space->child_fkey, child_link) { + struct fkey_def *def =3D fk->def; + for (uint32_t i =3D 0; i < def->field_count; ++i) + space->fkey_mask |=3D + FKEY_MASK(def->links[i].child_field); + } + rlist_foreach_entry(fk, &space->parent_fkey, parent_link) { + struct fkey_def *def =3D fk->def; + for (uint32_t i =3D 0; i < def->field_count; ++i) + space->fkey_mask |=3D + FKEY_MASK(def->links[i].parent_field); + } +} + +static void +fkey_update_mask(const struct fkey *fkey) +{ + struct space *child =3D space_by_id(fkey->def->child_id); + space_reset_fkey_mask(child); + struct space *parent =3D space_by_id(fkey->def->parent_id); + space_reset_fkey_mask(parent); +} + /** * On rollback of creation we remove FK constraint from DD, i.e. * from parent's and child's lists of constraints and @@ -3673,6 +3717,7 @@ on_create_fkey_rollback(struct trigger *trigger, = void *event) rlist_del_entry(fk, parent_link); rlist_del_entry(fk, child_link); fkey_delete(fk); + fkey_update_mask(fk); } =20 /** Return old FK and release memory for the new one. */ @@ -3688,6 +3733,7 @@ on_replace_fkey_rollback(struct trigger *trigger, = void *event) fkey_delete(old_fkey); rlist_add_entry(&child->child_fkey, fk, child_link); rlist_add_entry(&parent->parent_fkey, fk, parent_link); + fkey_update_mask(fk); } =20 /** On rollback of drop simply return back FK to DD. */ @@ -3700,6 +3746,7 @@ on_drop_fkey_rollback(struct trigger *trigger, = void *event) struct space *child =3D = space_by_id(fk_to_restore->def->child_id); rlist_add_entry(&child->child_fkey, fk_to_restore, child_link); rlist_add_entry(&parent->parent_fkey, fk_to_restore, = parent_link); + fkey_set_mask(fk_to_restore, &parent->fkey_mask, = &child->fkey_mask); } =20 /** @@ -3712,6 +3759,7 @@ on_drop_or_replace_fkey_commit(struct trigger = *trigger, void *event) { (void) event; struct fkey *fk =3D (struct fkey *)trigger->data; + fkey_update_mask(fk); fkey_delete(fk); } =20 @@ -3864,6 +3912,8 @@ on_replace_dd_fk_constraint(struct trigger * /* = trigger*/, void *event) = txn_alter_trigger_new(on_create_fkey_rollback, fkey); txn_on_rollback(txn, on_rollback); + fkey_set_mask(fkey, &parent_space->fkey_mask, + &child_space->fkey_mask); } else { struct fkey *old_fk =3D = fkey_grab_by_name(&child_space->child_fkey, diff --git a/src/box/fkey.h b/src/box/fkey.h index ed99617ca..6597473b4 100644 --- a/src/box/fkey.h +++ b/src/box/fkey.h @@ -102,6 +102,13 @@ struct fkey { struct rlist child_link; }; =20 +/** + * FIXME: as SQLite legacy temporary we use such mask throught + * SQL code. It should be replaced later with regular + * mask from column_mask.h + */ +#define FKEY_MASK(x) (((x)>31) ? 0xffffffff : ((uint64_t)1<<(x))) + /** * Alongside with struct fkey_def itself, we reserve memory for * string containing its name and for array of links. diff --git a/src/box/space.h b/src/box/space.h index d60ba6c56..f3e9e1e21 100644 --- a/src/box/space.h +++ b/src/box/space.h @@ -192,6 +192,12 @@ struct space { */ struct rlist parent_fkey; struct rlist child_fkey; + /** + * Mask indicates which fields are involved in foreign + * key constraint checking routine. Includes fields + * of parent constraints as well as child ones. + */ + uint64_t fkey_mask; }; =20 /** Initialize a base space instance. */ diff --git a/src/box/sql.c b/src/box/sql.c index 9795ad2ac..46a0c3472 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -55,6 +55,7 @@ #include "session.h" #include "xrow.h" #include "iproto_constants.h" +#include "fkey.h" =20 static sqlite3 *db =3D NULL; =20 @@ -839,103 +840,6 @@ rename_fail: return SQL_TARANTOOL_ERROR; } =20 -/* - * Acts almost as tarantoolSqlite3RenameTable, but doesn't change - * name of table, only statement. - */ -int tarantoolSqlite3RenameParentTable(int space_id, const char = *old_parent_name, - const char *new_parent_name) -{ - assert(space_id !=3D 0); - assert(old_parent_name !=3D NULL); - assert(new_parent_name !=3D NULL); - - box_tuple_t *tuple; - uint32_t key_len =3D mp_sizeof_uint(space_id) + = mp_sizeof_array(1); - - char *key_begin =3D (char*) region_alloc(&fiber()->gc, key_len); - if (key_begin =3D=3D NULL) { - diag_set(OutOfMemory, key_len, "region_alloc", = "key_begin"); - return SQL_TARANTOOL_ERROR; - } - char *key =3D mp_encode_array(key_begin, 1); - key =3D mp_encode_uint(key, space_id); - if (box_index_get(BOX_SPACE_ID, 0, key_begin, key, &tuple) !=3D = 0) - return SQL_TARANTOOL_ERROR; - assert(tuple !=3D NULL); - - assert(tuple_field_count(tuple) =3D=3D 7); - const char *sql_stmt_map =3D box_tuple_field(tuple, 5); - - if (sql_stmt_map =3D=3D NULL || mp_typeof(*sql_stmt_map) !=3D = MP_MAP) - goto rename_fail; - uint32_t map_size =3D mp_decode_map(&sql_stmt_map); - if (map_size !=3D 1) - goto rename_fail; - const char *sql_str =3D mp_decode_str(&sql_stmt_map, &key_len); - if (sqlite3StrNICmp(sql_str, "sql", 3) !=3D 0) - goto rename_fail; - uint32_t create_stmt_decoded_len; - const char *create_stmt_old =3D mp_decode_str(&sql_stmt_map, - = &create_stmt_decoded_len); - uint32_t old_name_len =3D strlen(old_parent_name); - uint32_t new_name_len =3D strlen(new_parent_name); - char *create_stmt_new =3D (char*) region_alloc(&fiber()->gc, - = create_stmt_decoded_len + 1); - if (create_stmt_new =3D=3D NULL) { - diag_set(OutOfMemory, create_stmt_decoded_len + 1, - "region_alloc", "create_stmt_new"); - return SQL_TARANTOOL_ERROR; - } - memcpy(create_stmt_new, create_stmt_old, = create_stmt_decoded_len); - create_stmt_new[create_stmt_decoded_len] =3D '\0'; - uint32_t numb_of_quotes =3D 0; - uint32_t numb_of_occurrences =3D 0; - create_stmt_new =3D rename_parent_table(db, create_stmt_new, = old_parent_name, - new_parent_name, = &numb_of_occurrences, - &numb_of_quotes); - uint32_t create_stmt_new_len =3D create_stmt_decoded_len - - numb_of_occurrences * - (old_name_len - new_name_len) + - 2 * numb_of_quotes; - assert(create_stmt_new_len > 0); - - key_len =3D tuple->bsize + mp_sizeof_str(create_stmt_new_len); - char *new_tuple =3D (char*)region_alloc(&fiber()->gc, key_len); - if (new_tuple =3D=3D NULL) { - sqlite3DbFree(db, create_stmt_new); - diag_set(OutOfMemory, key_len, "region_alloc", = "new_tuple"); - return SQL_TARANTOOL_ERROR; - } - - char *new_tuple_end =3D new_tuple; - const char *data_begin =3D tuple_data(tuple); - const char *data_end =3D tuple_field(tuple, 5); - uint32_t data_size =3D data_end - data_begin; - memcpy(new_tuple, data_begin, data_size); - new_tuple_end +=3D data_size; - new_tuple_end =3D mp_encode_map(new_tuple_end, 1); - new_tuple_end =3D mp_encode_str(new_tuple_end, "sql", 3); - new_tuple_end =3D mp_encode_str(new_tuple_end, create_stmt_new, - create_stmt_new_len); - sqlite3DbFree(db, create_stmt_new); - data_begin =3D tuple_field(tuple, 6); - data_end =3D (char*) tuple + tuple_size(tuple); - data_size =3D data_end - data_begin; - memcpy(new_tuple_end, data_begin, data_size); - new_tuple_end +=3D data_size; - - if (box_replace(BOX_SPACE_ID, new_tuple, new_tuple_end, NULL) !=3D= 0) - return SQL_TARANTOOL_ERROR; - else - return SQLITE_OK; - -rename_fail: - diag_set(ClientError, ER_SQL_EXECUTE, "can't modify name of = space " - "created not via SQL facilities"); - return SQL_TARANTOOL_ERROR; -} - int tarantoolSqlite3IdxKeyCompare(struct BtCursor *cursor, struct UnpackedRecord *unpacked) @@ -1489,6 +1393,23 @@ tarantoolSqlite3MakeTableOpts(Table *pTable, = const char *zSql, char *buf) return p - buf; } =20 +int +fkey_encode_links(uint32_t *links, uint32_t link_count, char *buf) +{ + const struct Enc *enc =3D get_enc(buf); + char *p =3D enc->encode_array(buf, link_count); + for (uint32_t i =3D 0; i < link_count; ++i) { + /* + * field_link consists of two uin32_t members, + * so if we calculate proper offset, we will + * get next parent/child member. + */ + size_t offset =3D sizeof(struct field_link) * i; + p =3D enc->encode_uint(p, *((char *) links + offset)); + } + return p - buf; +} + /* * Format "parts" array for _index entry. * Returns result size. diff --git a/src/box/sql/alter.c b/src/box/sql/alter.c index 8c1c36b9b..0e770272e 100644 --- a/src/box/sql/alter.c +++ b/src/box/sql/alter.c @@ -151,7 +151,6 @@ sqlite3AlterFinishAddColumn(Parse * pParse, Token * = pColDef) Expr *pDflt; /* Default value for the new column */ sqlite3 *db; /* The database connection; */ Vdbe *v =3D pParse->pVdbe; /* The prepared statement under = construction */ - struct session *user_session =3D current_session(); =20 db =3D pParse->db; if (pParse->nErr || db->mallocFailed) @@ -190,12 +189,6 @@ sqlite3AlterFinishAddColumn(Parse * pParse, Token * = pColDef) sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column"); return; } - if ((user_session->sql_flags & SQLITE_ForeignKeys) && = pNew->pFKey - && pDflt) { - sqlite3ErrorMsg(pParse, - "Cannot add a REFERENCES column with = non-NULL default value"); - return; - } assert(pNew->def->fields[pNew->def->field_count - 1].is_nullable = =3D=3D action_is_nullable(pNew->def->fields[ pNew->def->field_count - 1].nullable_action)); @@ -403,81 +396,6 @@ rename_table(sqlite3 *db, const char *sql_stmt, = const char *table_name, return new_sql_stmt; } =20 -/* - * This function is used by the ALTER TABLE ... RENAME command to = modify the - * definition of any foreign key constraints that used the table being = renamed - * as the parent table. All substituted occurrences will be quoted. - * It returns the new CREATE TABLE statement. Memory for the new = statement - * will be automatically freed by VDBE. - * - * Usage example: - * - * sqlite_rename_parent('CREATE TABLE t1(a REFERENCES t2)', 't2', = 't3') - * -> 'CREATE TABLE t1(a REFERENCES "t3")' - * - * @param sql_stmt text of a child CREATE TABLE statement being = modified - * @param old_name old name of the table being renamed - * @param new_name new name of the table being renamed - * @param[out] numb_of_occurrences number of occurrences of old_name in = sql_stmt - * @param[out] numb_of_unquoted number of unquoted occurrences of = old_name - * - * @retval new SQL statement on success, empty string otherwise. - */ -char* -rename_parent_table(sqlite3 *db, const char *sql_stmt, const char = *old_name, - const char *new_name, uint32_t *numb_of_occurrences, - uint32_t *numb_of_unquoted) -{ - assert(sql_stmt); - assert(old_name); - assert(new_name); - assert(numb_of_occurrences); - assert(numb_of_unquoted); - - char *output =3D NULL; - char *new_sql_stmt; - const char *csr; /* Pointer to token */ - int n; /* Length of token z */ - int token; /* Type of token */ - bool unused; - bool is_quoted; - - for (csr =3D sql_stmt; *csr; csr =3D csr + n) { - n =3D sql_token(csr, &token, &unused); - if (token =3D=3D TK_REFERENCES) { - char *zParent; - do { - csr +=3D n; - n =3D sql_token(csr, &token, &unused); - } while (token =3D=3D TK_SPACE); - if (token =3D=3D TK_ILLEGAL) - break; - zParent =3D sqlite3DbStrNDup(db, csr, n); - if (zParent =3D=3D 0) - break; - is_quoted =3D *zParent =3D=3D '"' ? true : = false; - sqlite3NormalizeName(zParent); - if (0 =3D=3D strcmp(old_name, zParent)) { - (*numb_of_occurrences)++; - if (!is_quoted) - (*numb_of_unquoted)++; - char *zOut =3D sqlite3MPrintf(db, = "%s%.*s\"%w\"", - (output ? = output : ""), - = (int)((char*)csr - sql_stmt), - sql_stmt, = new_name); - sqlite3DbFree(db, output); - output =3D zOut; - sql_stmt =3D &csr[n]; - } - sqlite3DbFree(db, zParent); - } - } - - new_sql_stmt =3D sqlite3MPrintf(db, "%s%s", (output ? output : = ""), sql_stmt); - sqlite3DbFree(db, output); - return new_sql_stmt; -} - /* This function is used to implement the ALTER TABLE command. * The table name in the CREATE TRIGGER statement is replaced with the = third * argument and the result returned. This is analagous to = rename_table() diff --git a/src/box/sql/build.c b/src/box/sql/build.c index 819c2626a..13013ee5a 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -47,6 +47,7 @@ #include "vdbeInt.h" #include "tarantoolInt.h" #include "box/box.h" +#include "box/fkey.h" #include "box/sequence.h" #include "box/session.h" #include "box/identifier.h" @@ -332,9 +333,6 @@ deleteTable(sqlite3 * db, Table * pTable) freeIndex(db, pIndex); } =20 - /* Delete any foreign keys attached to this table. */ - sqlite3FkDelete(db, pTable); - /* Delete the Table structure itself. */ sqlite3HashClear(&pTable->idxHash); @@ -1551,6 +1549,126 @@ emitNewSysSpaceSequenceRecord(Parse *pParse, int = space_id, const char reg_seq_id return first_col; } =20 +/** + * Generate opcodes to serialize foreign key into MsgPack and + * insert produced tuple into _fk_constraint space. + * + * @param parse_context Parsing context. + * @param fk Foreign key to be created. + */ +static void +vdbe_emit_fkey_create(struct Parse *parse_context, const struct = fkey_def *fk) +{ + assert(parse_context !=3D NULL); + assert(fk !=3D NULL); + struct Vdbe *vdbe =3D sqlite3GetVdbe(parse_context); + assert(vdbe !=3D NULL); + /* + * Occupy registers for 8 fields: each member in + * _constraint space plus one for final msgpack tuple. + */ + int constr_tuple_reg =3D sqlite3GetTempRange(parse_context, 10); + char *name_copy =3D sqlite3DbStrDup(parse_context->db, = fk->name); + if (name_copy =3D=3D NULL) + return; + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg, 0, = name_copy, + P4_DYNAMIC); + /* + * In case we are adding FK constraints during execution + * of statement, we don't have child + * id, but we know register where it will be stored. + * */ + if (parse_context->pNewTable !=3D NULL) { + sqlite3VdbeAddOp2(vdbe, OP_SCopy, fk->child_id, + constr_tuple_reg + 1); + } else { + sqlite3VdbeAddOp2(vdbe, OP_Integer, fk->child_id, + constr_tuple_reg + 1); + } + if (parse_context->pNewTable !=3D NULL && = fkey_is_self_referenced(fk)) { + sqlite3VdbeAddOp2(vdbe, OP_SCopy, fk->parent_id, + constr_tuple_reg + 2); + } else { + sqlite3VdbeAddOp2(vdbe, OP_Integer, fk->parent_id, + constr_tuple_reg + 2); + } + sqlite3VdbeAddOp2(vdbe, OP_Bool, 0, constr_tuple_reg + 3); + sqlite3VdbeChangeP4(vdbe, -1, (char*)&fk->is_deferred, P4_BOOL); + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 4, 0, + fkey_match_strs[fk->match], P4_STATIC); + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 5, 0, + fkey_action_strs[fk->on_delete], P4_STATIC); + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 6, 0, + fkey_action_strs[fk->on_update], P4_STATIC); + size_t encoded_parent_sz =3D = fkey_encode_links(&fk->links[0].parent_field, + fk->field_count, = NULL); + size_t encoded_child_sz =3D = fkey_encode_links(&fk->links[0].child_field, + fk->field_count, = NULL); + /* + * We are allocating memory for both parent and child + * arrays in the same chunk. Thus, first OP_Blob opcode + * interprets it as static memory, and the second one - + * as dynamic and releases memory. + */ + char *encoded_links =3D sqlite3DbMallocRaw(parse_context->db, + encoded_child_sz + + encoded_parent_sz); + if (encoded_links =3D=3D NULL) { + sqlite3DbFree(parse_context->db, (void *) name_copy); + return; + } + /* + * Here we use small memory trick: parent and child links + * are quite similar but assigned to different fields. + * So to avoid code duplication, we calculate offset + * and fetch proper parent or child link: + * + * +--------------------------------------+ + * | child | parent | child | parent| ... | + * |--------------------------------------| + * | link[0] | link[1] | ... | + * +--------------------------------------+ + */ + size_t real_parent_sz =3D = fkey_encode_links(&fk->links[0].parent_field, + fk->field_count, + encoded_links); + size_t real_child_sz =3D = fkey_encode_links(&fk->links[0].child_field, + fk->field_count, + encoded_links + + real_parent_sz); + sqlite3VdbeAddOp4(vdbe, OP_Blob, real_child_sz, constr_tuple_reg = + 7, + SQL_SUBTYPE_MSGPACK, encoded_links + = real_parent_sz, + P4_STATIC); + sqlite3VdbeAddOp4(vdbe, OP_Blob, real_parent_sz, = constr_tuple_reg + 8, + SQL_SUBTYPE_MSGPACK, encoded_links, + P4_DYNAMIC); + sqlite3VdbeAddOp3(vdbe, OP_MakeRecord, constr_tuple_reg, 9, + constr_tuple_reg + 9); + sqlite3VdbeAddOp2(vdbe, OP_SInsert, BOX_FK_CONSTRAINT_ID, + constr_tuple_reg + 9); + sqlite3VdbeChangeP5(vdbe, OPFLAG_NCHANGE); + sqlite3ReleaseTempRange(parse_context, constr_tuple_reg, 10); +} + +static int +resolve_link(struct Parse *parse_context, const struct space_def *def, + const char *field_name, uint32_t *link, const char = *fk_name) +{ + assert(link !=3D NULL); + for (uint32_t j =3D 0; j < def->field_count; ++j) { + if (strcmp(field_name, def->fields[j].name) =3D=3D 0) { + *link =3D j; + return 0; + } + } + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, fk_name, + tt_sprintf("unknown column %s in foreign key = definition", + field_name)); + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; + return -1; +} + /* * This routine is called to report the final ")" that terminates * a CREATE TABLE statement. @@ -1720,6 +1838,45 @@ sqlite3EndTable(Parse * pParse, /* Parse context = */ =20 /* Reparse everything to update our internal data = structures */ parseTableSchemaRecord(pParse, iSpaceId, zStmt); = /* consumes zStmt */ + + /* Code creation of FK constraints, if any. */ + struct fkey_parse *fk_parse; + rlist_foreach_entry(fk_parse, &pParse->new_fkey, link) { + struct fkey_def *fk =3D fk_parse->fkey; + if (fk_parse->selfref_cols !=3D NULL) { + struct ExprList *cols =3D = fk_parse->selfref_cols; + for (uint32_t i =3D 0; i < = fk->field_count; ++i) { + if (resolve_link(pParse, p->def, + = cols->a[i].zName, + = &fk->links[i].parent_field, + fk->name) !=3D = 0) + return; + } + fk->parent_id =3D iSpaceId; + } else if (fk_parse->is_self_referenced) { + struct Index *pk =3D = sqlite3PrimaryKeyIndex(p); + if (pk->def->key_def->part_count !=3D + fk->field_count) { + diag_set(ClientError, + = ER_CREATE_FK_CONSTRAINT, + fk->name, "number of = columns " + "in foreign key does = not " + "match the number of = columns " + "in the primary index = of " + "referenced table"); + pParse->rc =3D = SQL_TARANTOOL_ERROR; + pParse->nErr++; + return; + } + for (uint32_t i =3D 0; i < = fk->field_count; ++i) { + fk->links[i].parent_field =3D + = pk->def->key_def->parts[i].fieldno; + } + fk->parent_id =3D iSpaceId; + } + fk->child_id =3D iSpaceId; + vdbe_emit_fkey_create(pParse, fk); + } } =20 /* Add the table to the in-memory representation of the = database. @@ -1927,6 +2084,32 @@ sql_clear_stat_spaces(struct Parse *parse, const = char *table_name, vdbe_emit_stat_space_clear(parse, "_sql_stat1", idx_name, = table_name); } =20 +/** + * Generate VDBE program to remove entry from _fk_constraint space. + * + * @param parse_context Parsing context. + * @param constraint_name Name of FK constraint to be dropped. + * Must be allocated on head by sqlite3DbMalloc(). + * It will be freed in VDBE. + * @param child_id Id of table which constraint belongs to. + */ +static void +vdbe_emit_fkey_drop(struct Parse *parse_context, const char = *constraint_name, + uint32_t child_id) +{ + struct Vdbe *vdbe =3D sqlite3GetVdbe(parse_context); + assert(vdbe !=3D NULL); + int key_reg =3D sqlite3GetTempRange(parse_context, 3); + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, key_reg, 0, = constraint_name, + P4_DYNAMIC); + sqlite3VdbeAddOp2(vdbe, OP_Integer, child_id, key_reg + 1); + sqlite3VdbeAddOp3(vdbe, OP_MakeRecord, key_reg, 2, key_reg + 2); + sqlite3VdbeAddOp2(vdbe, OP_SDelete, BOX_FK_CONSTRAINT_ID, = key_reg + 2); + sqlite3VdbeChangeP5(vdbe, OPFLAG_NCHANGE); + VdbeComment((vdbe, "Delete FK constraint %s", constraint_name)); + sqlite3ReleaseTempRange(parse_context, key_reg, 3); +} + /** * Generate code to drop a table. * This routine includes dropping triggers, sequences, @@ -1982,6 +2165,15 @@ sql_code_drop_table(struct Parse *parse_context, = struct space *space, sqlite3VdbeAddOp2(v, OP_SDelete, BOX_SEQUENCE_ID, = idx_rec_reg); VdbeComment((v, "Delete entry from _sequence")); } + /* Delete all child FK constraints. */ + struct fkey *child_fk; + rlist_foreach_entry (child_fk, &space->child_fkey, child_link) { + const char *fk_name_dup =3D sqlite3DbStrDup(v->db, + = child_fk->def->name); + if (fk_name_dup =3D=3D NULL) + return; + vdbe_emit_fkey_drop(parse_context, fk_name_dup, = space_id); + } /* * Drop all _space and _index entries that refer to the * table. @@ -2090,14 +2282,15 @@ sql_drop_table(struct Parse *parse_context, = struct SrcList *table_name_list, * removing indexes from _index space and eventually * tuple with corresponding space_id from _space. */ - struct Table *tab =3D sqlite3HashFind(&db->pSchema->tblHash, = space_name); - struct FKey *fk =3D sqlite3FkReferences(tab); - if (fk !=3D NULL && (fk->pFrom->def->id !=3D tab->def->id)) { - diag_set(ClientError, ER_DROP_SPACE, space_name, - "other objects depend on it"); - parse_context->rc =3D SQL_TARANTOOL_ERROR; - parse_context->nErr++; - goto exit_drop_table; + struct fkey *fk; + rlist_foreach_entry (fk, &space->parent_fkey, parent_link) { + if (! fkey_is_self_referenced(fk->def)) { + diag_set(ClientError, ER_DROP_SPACE, space_name, + "other objects depend on it"); + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; + goto exit_drop_table; + } } sql_clear_stat_spaces(parse_context, space_name, NULL); sql_code_drop_table(parse_context, space, is_view); @@ -2106,177 +2299,281 @@ sql_drop_table(struct Parse *parse_context, = struct SrcList *table_name_list, sqlite3SrcListDelete(db, table_name_list); } =20 -/* - * This routine is called to create a new foreign key on the table - * currently under construction. pFromCol determines which columns - * in the current table point to the foreign key. If pFromCol=3D=3D0 = then - * connect the key to the last column inserted. pTo is the name of - * the table referred to (a.k.a the "parent" table). pToCol is a list - * of tables in the parent pTo table. flags contains all - * information about the conflict resolution algorithms specified - * in the ON DELETE, ON UPDATE and ON INSERT clauses. +/** + * Return ordinal number of column by name. In case of error, + * set error message. * - * An FKey structure is created and added to the table currently - * under construction in the pParse->pNewTable field. + * @param parse_context Parsing context. + * @param space Space which column belongs to. + * @param column_name Name of column to investigate. + * @param[out] colno Found name of column. + * @param fk_name Name of FK constraint to be created. * - * The foreign key is set for IMMEDIATE processing. A subsequent call - * to sqlite3DeferForeignKey() might change this to DEFERRED. + * @retval 0 on success, -1 on fault. */ -void -sqlite3CreateForeignKey(Parse * pParse, /* Parsing context */ - ExprList * pFromCol, /* Columns in this table = that point to other table */ - Token * pTo, /* Name of the other table */ - ExprList * pToCol, /* Columns in the other = table */ - int flags /* Conflict resolution = algorithms. */ - ) +static int +columnno_by_name(struct Parse *parse_context, const struct space = *space, + const char *column_name, uint32_t *colno, const char = *fk_name) { - sqlite3 *db =3D pParse->db; -#ifndef SQLITE_OMIT_FOREIGN_KEY - FKey *pFKey =3D 0; - FKey *pNextTo; - Table *p =3D pParse->pNewTable; - int nByte; - int i; - int nCol; - char *z; - - assert(pTo !=3D 0); - char *normalized_name =3D strndup(pTo->z, pTo->n); - if (normalized_name =3D=3D NULL) { - diag_set(OutOfMemory, pTo->n, "strndup", "normalized = name"); - goto fk_end; - } - sqlite3NormalizeName(normalized_name); - uint32_t parent_id =3D box_space_id_by_name(normalized_name, - = strlen(normalized_name)); - if (parent_id =3D=3D BOX_ID_NIL && - strcmp(normalized_name, p->def->name) !=3D 0) { - diag_set(ClientError, ER_NO_SUCH_SPACE, = normalized_name); - pParse->rc =3D SQL_TARANTOOL_ERROR; - pParse->nErr++; - goto fk_end; - } - struct space *parent_space =3D space_by_id(parent_id); - if (parent_space !=3D NULL && parent_space->def->opts.is_view) { - sqlite3ErrorMsg(pParse, "can't create foreign key = constraint "\ - "referencing view: %s", = normalized_name); - goto fk_end; + assert(colno !=3D NULL); + uint32_t column_len =3D strlen(column_name); + if (tuple_fieldno_by_name(space->def->dict, column_name, = column_len, + field_name_hash(column_name, = column_len), + colno) !=3D 0) { + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, fk_name, + tt_sprintf("foreign key refers to nonexistent = field %s", + column_name)); + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; + return -1; } - if (p =3D=3D 0) - goto fk_end; - if (pFromCol =3D=3D 0) { - int iCol =3D p->def->field_count - 1; - if (NEVER(iCol < 0)) - goto fk_end; - if (pToCol && pToCol->nExpr !=3D 1) { - sqlite3ErrorMsg(pParse, "foreign key on %s" - " should reference only one = column of table %T", - p->def->fields[iCol].name, pTo); - goto fk_end; + return 0; +} + +void +sql_create_foreign_key(struct Parse *parse_context, struct SrcList = *child, + struct Token *constraint, struct ExprList = *child_cols, + struct Token *parent, struct ExprList = *parent_cols, + bool is_deferred, int actions) +{ + struct sqlite3 *db =3D parse_context->db; + /* + * When this function is called second time during + * statement (i.e. at VDBE runtime), + * don't even try to do something. + */ + if (db->init.busy) + return; + /* + * Beforehand initialization for correct clean-up + * while emergency exiting in case of error. + */ + const char *parent_name =3D NULL; + const char *constraint_name =3D NULL; + bool is_self_referenced =3D false; + /* + * Table under construction during CREATE TABLE + * processing. NULL for ALTER TABLE statement handling. + */ + struct Table *new_tab =3D parse_context->pNewTable; + /* Whether we are processing ALTER TABLE or CREATE TABLE. */ + bool is_alter =3D new_tab =3D=3D NULL; + uint32_t child_cols_count; + if (child_cols =3D=3D NULL) { + assert(!is_alter); + child_cols_count =3D 1; + } else { + child_cols_count =3D child_cols->nExpr; + } + assert(!is_alter || (child !=3D NULL && child->nSrc =3D=3D 1)); + struct space *child_space =3D NULL; + uint32_t child_id =3D 0; + if (is_alter) { + const char *child_name =3D child->a[0].zName; + child_id =3D box_space_id_by_name(child_name, + strlen(child_name)); + if (child_id =3D=3D BOX_ID_NIL) { + diag_set(ClientError, ER_NO_SUCH_SPACE, = child_name); + goto tnt_error; } - nCol =3D 1; - } else if (pToCol && pToCol->nExpr !=3D pFromCol->nExpr) { - sqlite3ErrorMsg(pParse, - "number of columns in foreign key does = not match the number of " - "columns in the referenced table"); - goto fk_end; + child_space =3D space_by_id(child_id); + assert(child_space !=3D NULL); } else { - nCol =3D pFromCol->nExpr; - } - nByte =3D sizeof(*pFKey) + (nCol - 1) * sizeof(pFKey->aCol[0]) + - strlen(normalized_name) + 1; - if (pToCol) { - for (i =3D 0; i < pToCol->nExpr; i++) { - nByte +=3D sqlite3Strlen30(pToCol->a[i].zName) + = 1; + struct fkey_parse *fk =3D = region_alloc(&parse_context->region, + sizeof(*fk)); + if (fk =3D=3D NULL) { + diag_set(OutOfMemory, sizeof(*fk), = "region_alloc", + "fk"); + goto tnt_error; + } + memset(fk, 0, sizeof(*fk)); + rlist_add_entry(&parse_context->new_fkey, fk, link); + } + assert(parent !=3D NULL); + parent_name =3D sqlite3NameFromToken(db, parent); + if (parent_name =3D=3D NULL) + goto exit_create_fk; + uint32_t parent_id =3D box_space_id_by_name(parent_name, + strlen(parent_name)); + /* + * Within ALTER TABLE ADD CONSTRAINT FK also can be + * self-referenced, but in this case parent (which is + * also child) table will definitely exist. + */ + is_self_referenced =3D !is_alter && + strcmp(parent_name, new_tab->def->name) =3D=3D= 0; + struct space *parent_space; + if (parent_id =3D=3D BOX_ID_NIL) { + parent_space =3D NULL; + if (is_self_referenced) { + struct fkey_parse *fk =3D + = rlist_first_entry(&parse_context->new_fkey, + struct fkey_parse, = link); + fk->selfref_cols =3D parent_cols; + fk->is_self_referenced =3D true; + } else { + diag_set(ClientError, ER_NO_SUCH_SPACE, = parent_name);; + goto tnt_error; } - } - pFKey =3D sqlite3DbMallocZero(db, nByte); - if (pFKey =3D=3D 0) { - goto fk_end; - } - pFKey->p=46rom =3D p; - pFKey->pNext=46rom =3D p->pFKey; - z =3D (char *)&pFKey->aCol[nCol]; - pFKey->zTo =3D z; - memcpy(z, normalized_name, strlen(normalized_name) + 1); - z +=3D strlen(normalized_name) + 1; - pFKey->nCol =3D nCol; - if (pFromCol =3D=3D 0) { - pFKey->aCol[0].i=46rom =3D p->def->field_count - 1; } else { - for (i =3D 0; i < nCol; i++) { - int j; - for (j =3D 0; j < (int)p->def->field_count; j++) = { - if (strcmp(p->def->fields[j].name, - pFromCol->a[i].zName) =3D=3D = 0) { - pFKey->aCol[i].i=46rom =3D j; - break; - } - } - if (j >=3D (int)p->def->field_count) { - sqlite3ErrorMsg(pParse, - "unknown column \"%s\" = in foreign key definition", - pFromCol->a[i].zName); - goto fk_end; - } + parent_space =3D space_by_id(parent_id); + assert(parent_space !=3D NULL); + if (parent_space->def->opts.is_view) { + sqlite3ErrorMsg(parse_context, + "referenced table can't be = view"); + goto exit_create_fk; } } - if (pToCol) { - for (i =3D 0; i < nCol; i++) { - int n =3D sqlite3Strlen30(pToCol->a[i].zName); - pFKey->aCol[i].zCol =3D z; - memcpy(z, pToCol->a[i].zName, n); - z[n] =3D 0; - z +=3D n + 1; + if (constraint =3D=3D NULL && !is_alter) { + if (parse_context->constraintName.n =3D=3D 0) { + constraint_name =3D + sqlite3MPrintf(db, = "fk_constraint_%d_%s", + = ++parse_context->fkey_count, + new_tab->def->name); + } else { + struct Token *cnstr_nm =3D = &parse_context->constraintName; + constraint_name =3D sqlite3NameFromToken(db, = cnstr_nm); + } + } else { + constraint_name =3D sqlite3NameFromToken(db, = constraint); + } + if (constraint_name =3D=3D NULL) + goto exit_create_fk; + const char *error_msg =3D "number of columns in foreign key does = not " + "match the number of columns in the = primary " + "index of referenced table"; + if (parent_cols !=3D NULL) { + if (parent_cols->nExpr !=3D (int) child_cols_count) { + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, + constraint_name, error_msg); + goto tnt_error; + } + } else if (!is_self_referenced) { + /* + * If parent columns are not specified, then PK + * columns of parent table are used as referenced. + */ + struct index *parent_pk =3D space_index(parent_space, = 0); + assert(parent_pk !=3D NULL); + if (parent_pk->def->key_def->part_count !=3D = child_cols_count) { + diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, + constraint_name, error_msg); + goto tnt_error; } } - pFKey->isDeferred =3D 0; - pFKey->aAction[0] =3D (u8) (flags & 0xff); /* ON DELETE = action */ - pFKey->aAction[1] =3D (u8) ((flags >> 8) & 0xff); /* ON = UPDATE action */ - - pNextTo =3D (FKey *) sqlite3HashInsert(&p->pSchema->fkeyHash, - pFKey->zTo, (void *)pFKey); - if (pNextTo =3D=3D pFKey) { - sqlite3OomFault(db); - goto fk_end; + int name_len =3D strlen(constraint_name); + size_t fk_size =3D fkey_def_sizeof(child_cols_count, name_len); + struct fkey_def *fk =3D region_alloc(&parse_context->region, = fk_size); + if (fk =3D=3D NULL) { + diag_set(OutOfMemory, fk_size, "region", "struct fkey"); + goto tnt_error; } - if (pNextTo) { - assert(pNextTo->pPrevTo =3D=3D 0); - pFKey->pNextTo =3D pNextTo; - pNextTo->pPrevTo =3D pFKey; + fk->field_count =3D child_cols_count; + fk->child_id =3D child_id; + fk->parent_id =3D parent_id; + fk->is_deferred =3D is_deferred; + fk->match =3D (enum fkey_match) ((actions >> 16) & 0xff); + fk->on_update =3D (enum fkey_action) ((actions >> 8) & 0xff); + fk->on_delete =3D (enum fkey_action) (actions & 0xff); + fk->links =3D (struct field_link *) ((char *) fk->name + = name_len + 1); + /* Fill links map. */ + for (uint32_t i =3D 0; i < fk->field_count; ++i) { + if (!is_self_referenced && parent_cols =3D=3D NULL) { + struct key_def *pk_def =3D + parent_space->index[0]->def->key_def; + fk->links[i].parent_field =3D = pk_def->parts[i].fieldno; + } else if (!is_self_referenced && + columnno_by_name(parse_context, parent_space, + parent_cols->a[i].zName, + &fk->links[i].parent_field, + constraint_name) !=3D 0) { + goto exit_create_fk; + } + if (!is_alter) { + if (child_cols =3D=3D NULL) { + assert(i =3D=3D 0); + /* + * In this case there must be only + * one link (the last column + * added), so we can break + * immediately. + */ + fk->links[0].child_field =3D + new_tab->def->field_count - 1; + break; + } + if (resolve_link(parse_context, new_tab->def, + child_cols->a[i].zName, + &fk->links[i].child_field, + constraint_name) !=3D 0) + goto exit_create_fk; + /* In case of ALTER parent table must exist. */ + } else if (columnno_by_name(parse_context, child_space, + child_cols->a[i].zName, + &fk->links[i].child_field, + constraint_name) !=3D 0) { + goto exit_create_fk; + } } - - /* Link the foreign key to the table as the last step. + memcpy(fk->name, constraint_name, name_len); + fk->name[name_len] =3D '\0'; + sqlite3NormalizeName(fk->name); + /* + * In case of CREATE TABLE processing, all foreign keys + * constraints must be created after space itself, so + * lets delay it until sqlite3EndTable() call and simply + * maintain list of all FK constraints inside parser. */ - p->pFKey =3D pFKey; - pFKey =3D 0; + if (!is_alter) { + struct fkey_parse *parse_fk =3D + rlist_first_entry(&parse_context->new_fkey, + struct fkey_parse, link); + parse_fk->fkey =3D fk; + } else { + vdbe_emit_fkey_create(parse_context, fk); + } =20 - fk_end: - sqlite3DbFree(db, pFKey); - free(normalized_name); -#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ - sql_expr_list_delete(db, pFromCol); - sql_expr_list_delete(db, pToCol); +exit_create_fk: + sql_expr_list_delete(db, child_cols); + if (!is_self_referenced) + sql_expr_list_delete(db, parent_cols); + sqlite3DbFree(db, (void *) parent_name); + sqlite3DbFree(db, (void *) constraint_name); + return; +tnt_error: + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; + goto exit_create_fk; } =20 -/* - * This routine is called when an INITIALLY IMMEDIATE or INITIALLY = DEFERRED - * clause is seen as part of a foreign key definition. The isDeferred - * parameter is 1 for INITIALLY DEFERRED and 0 for INITIALLY IMMEDIATE. - * The behavior of the most recently created foreign key is adjusted - * accordingly. - */ void -sqlite3DeferForeignKey(Parse * pParse, int isDeferred) +fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred) { -#ifndef SQLITE_OMIT_FOREIGN_KEY - Table *pTab; - FKey *pFKey; - if ((pTab =3D pParse->pNewTable) =3D=3D 0 || (pFKey =3D = pTab->pFKey) =3D=3D 0) + if (parse_context->db->init.busy || + rlist_empty(&parse_context->new_fkey)) return; - assert(isDeferred =3D=3D 0 || isDeferred =3D=3D 1); /* EV: = R-30323-21917 */ - pFKey->isDeferred =3D (u8) isDeferred; -#endif + rlist_first_entry(&parse_context->new_fkey, struct fkey_parse, + link)->fkey->is_deferred =3D is_deferred; +} + +void +sql_drop_foreign_key(struct Parse *parse_context, struct SrcList = *table, + struct Token *constraint) +{ + assert(table !=3D NULL && table->nSrc =3D=3D 1); + const char *table_name =3D table->a[0].zName; + uint32_t child_id =3D box_space_id_by_name(table_name, + strlen(table_name)); + if (child_id =3D=3D BOX_ID_NIL) { + diag_set(ClientError, ER_NO_SUCH_SPACE, table_name); + parse_context->rc =3D SQL_TARANTOOL_ERROR; + parse_context->nErr++; + return; + } + const char *constraint_name =3D = sqlite3NameFromToken(parse_context->db, + constraint); + if (constraint_name !=3D NULL) + vdbe_emit_fkey_drop(parse_context, constraint_name, = child_id); } =20 /* diff --git a/src/box/sql/callback.c b/src/box/sql/callback.c index 01e8dd8f1..c630bf21d 100644 --- a/src/box/sql/callback.c +++ b/src/box/sql/callback.c @@ -294,7 +294,6 @@ sqlite3SchemaClear(sqlite3 * db) sqlite3DeleteTable(0, pTab); } sqlite3HashClear(&temp1); - sqlite3HashClear(&pSchema->fkeyHash); =20 db->pSchema =3D NULL; } @@ -303,13 +302,10 @@ sqlite3SchemaClear(sqlite3 * db) Schema * sqlite3SchemaCreate(sqlite3 * db) { - Schema *p; - p =3D (Schema *) sqlite3DbMallocZero(0, sizeof(Schema)); - if (!p) { + struct Schema *p =3D (Schema *) sqlite3DbMallocZero(0, = sizeof(Schema)); + if (p =3D=3D NULL) sqlite3OomFault(db); - } else { + else sqlite3HashInit(&p->tblHash); - sqlite3HashInit(&p->fkeyHash); - } return p; } diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c index 06811778f..57a067760 100644 --- a/src/box/sql/delete.c +++ b/src/box/sql/delete.c @@ -130,7 +130,7 @@ sql_table_delete_from(struct Parse *parse, struct = SrcList *tab_list, assert(space !=3D NULL); trigger_list =3D sql_triggers_exist(table, TK_DELETE, = NULL, NULL); is_complex =3D trigger_list !=3D NULL || - sqlite3FkRequired(table, NULL); + fkey_is_required(table->def->id, NULL); } assert(space !=3D NULL); =20 @@ -437,14 +437,16 @@ sql_generate_row_delete(struct Parse *parse, = struct Table *table, * use for the old.* references in the triggers. */ if (table !=3D NULL && - (sqlite3FkRequired(table, NULL) || trigger_list !=3D NULL)) = { + (fkey_is_required(table->def->id, NULL) || trigger_list !=3D = NULL)) { /* Mask of OLD.* columns in use */ /* TODO: Could use temporary registers here. */ uint32_t mask =3D sql_trigger_colmask(parse, trigger_list, 0, 0, TRIGGER_BEFORE | = TRIGGER_AFTER, table, onconf); - mask |=3D sqlite3FkOldmask(parse, table); + struct space *space =3D space_by_id(table->def->id); + assert(space !=3D NULL); + mask |=3D space->fkey_mask; first_old_reg =3D parse->nMem + 1; parse->nMem +=3D (1 + (int)table->def->field_count); =20 @@ -488,7 +490,7 @@ sql_generate_row_delete(struct Parse *parse, struct = Table *table, * constraints attached to other tables) are not * violated by deleting this row. */ - sqlite3FkCheck(parse, table, first_old_reg, 0, NULL); + fkey_emit_check(parse, table, first_old_reg, 0, NULL); } =20 /* Delete the index and table entries. Skip this step if @@ -518,7 +520,7 @@ sql_generate_row_delete(struct Parse *parse, struct = Table *table, * key to the row just deleted. */ =20 - sqlite3FkActions(parse, table, 0, first_old_reg, 0); + fkey_emit_actions(parse, table, first_old_reg, NULL); =20 /* Invoke AFTER DELETE trigger programs. */ vdbe_code_row_trigger(parse, trigger_list, TK_DELETE, 0, diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c index c941b6e58..39213e5dc 100644 --- a/src/box/sql/fkey.c +++ b/src/box/sql/fkey.c @@ -38,9 +38,6 @@ #include "box/fkey.h" #include "box/schema.h" #include "box/session.h" -#include "tarantoolInt.h" - -#ifndef SQLITE_OMIT_FOREIGN_KEY =20 /* * Deferred and Immediate FKs @@ -137,19 +134,14 @@ * coding an INSERT operation. The functions used by the UPDATE/DELETE * generation code to query for this information are: * - * sqlite3FkRequired() - Test to see if FK processing is required. - * sqlite3FkOldmask() - Query for the set of required old.* columns. - * + * fkey_is_required() - Test to see if FK processing is required. * * Externally accessible module functions * -------------------------------------- * - * sqlite3FkCheck() - Check for foreign key violations. - * sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE = actions. - * sqlite3FkDelete() - Delete an FKey structure. - */ - -/* + * fkey_emit_check() - Check for foreign key violations. + * fkey_emit_actions() - Code triggers for ON UPDATE/ON DELETE = actions. + * * VDBE Calling Convention * ----------------------- * @@ -166,332 +158,134 @@ * Register (x+3): 3.1 (type real) */ =20 -/* - * A foreign key constraint requires that the key columns in the parent - * table are collectively subject to a UNIQUE or PRIMARY KEY = constraint. - * Given that pParent is the parent table for foreign key constraint = pFKey, - * search the schema for a unique index on the parent key columns. - * - * If successful, zero is returned. If the parent key is an INTEGER = PRIMARY - * KEY column, then output variable *ppIdx is set to NULL. Otherwise, = *ppIdx - * is set to point to the unique index. +/** + * This function is called when a row is inserted into or deleted + * from the child table of foreign key constraint. If an SQL + * UPDATE is executed on the child table of fkey, this function is + * invoked twice for each row affected - once to "delete" the old + * row, and then again to "insert" the new row. * - * If the parent key consists of a single column (the foreign key = constraint - * is not a composite foreign key), output variable *paiCol is set to = NULL. - * Otherwise, it is set to point to an allocated array of size N, where - * N is the number of columns in the parent key. The first element of = the - * array is the index of the child table column that is mapped by the = FK - * constraint to the parent table column stored in the left-most column - * of index *ppIdx. The second element of the array is the index of the - * child table column that corresponds to the second left-most column = of - * *ppIdx, and so on. + * Each time it is called, this function generates VDBE code to + * locate the row in the parent table that corresponds to the row + * being inserted into or deleted from the child table. If the + * parent row can be found, no special action is taken. Otherwise, + * if the parent row can *not* be found in the parent table: * - * If the required index cannot be found, either because: + * Op | FK type | Action taken + * --------------------------------------------------------------- + * INSERT immediate Increment the "immediate constraint counter". * - * 1) The named parent key columns do not exist, or + * DELETE immediate Decrement the "immediate constraint counter". * - * 2) The named parent key columns do exist, but are not subject to a - * UNIQUE or PRIMARY KEY constraint, or + * INSERT deferred Increment the "deferred constraint counter". * - * 3) No parent key columns were provided explicitly as part of the - * foreign key definition, and the parent table does not have a - * PRIMARY KEY, or + * DELETE deferred Decrement the "deferred constraint counter". * - * 4) No parent key columns were provided explicitly as part of the - * foreign key definition, and the PRIMARY KEY of the parent table - * consists of a different number of columns to the child key in - * the child table. + * These operations are identified in the comment at the top of + * this file as "I.1" and "D.1". * - * then non-zero is returned, and a "foreign key mismatch" error loaded - * into pParse. If an OOM error occurs, non-zero is returned and the - * pParse->db->mallocFailed flag is set. + * @param parse_context Current parsing context. + * @param parent Parent table of FK constraint. + * @param fk_def FK constraint definition. + * @param referenced_idx Id of referenced index. + * @param reg_data Address of array containing child table row. + * @param incr_count Increment constraint counter by this value. */ -int -sqlite3FkLocateIndex(Parse * pParse, /* Parse context to store any = error in */ - Table * pParent, /* Parent table of FK constraint = pFKey */ - FKey * pFKey, /* Foreign key to find index for = */ - Index ** ppIdx, /* OUT: Unique index on parent = table */ - int **paiCol /* OUT: Map of index columns in = pFKey */ - ) +static void +fkey_lookup_parent(struct Parse *parse_context, struct space *parent, + struct fkey_def *fk_def, uint32_t referenced_idx, + int reg_data, int incr_count) { - int *aiCol =3D 0; /* Value to return via *paiCol = */ - int nCol =3D pFKey->nCol; /* Number of columns in parent = key */ - char *zKey =3D pFKey->aCol[0].zCol; /* Name of left-most = parent key column */ - - /* The caller is responsible for zeroing output parameters. */ - assert(ppIdx && *ppIdx =3D=3D 0); - assert(!paiCol || *paiCol =3D=3D 0); - assert(pParse); - - /* If this is a non-composite (single column) foreign key, check = if it - * maps to the INTEGER PRIMARY KEY of table pParent. If so, = leave *ppIdx - * and *paiCol set to zero and return early. + assert(incr_count =3D=3D -1 || incr_count =3D=3D 1); + struct Vdbe *v =3D sqlite3GetVdbe(parse_context); + int cursor =3D parse_context->nTab - 1; + int ok_label =3D sqlite3VdbeMakeLabel(v); + /* + * If incr_count is less than zero, then check at runtime + * if there are any outstanding constraints to resolve. + * If there are not, there is no need to check if deleting + * this row resolves any outstanding violations. * - * Otherwise, for a composite foreign key (more than one = column), allocate - * space for the aiCol array (returned via output parameter = *paiCol). - * Non-composite foreign keys do not require the aiCol array. + * Check if any of the key columns in the child table row + * are NULL. If any are, then the constraint is considered + * satisfied. No need to search for a matching row in the + * parent table. */ - if (paiCol && nCol > 1) { - aiCol =3D - (int *)sqlite3DbMallocRawNN(pParse->db, nCol * = sizeof(int)); - if (!aiCol) - return 1; - *paiCol =3D aiCol; + if (incr_count < 0) { + sqlite3VdbeAddOp2(v, OP_FkIfZero, fk_def->is_deferred, + ok_label); } - - struct Index *index =3D NULL; - for (index =3D pParent->pIndex; index !=3D NULL; index =3D = index->pNext) { - int part_count =3D index->def->key_def->part_count; - if (part_count !=3D nCol || !index->def->opts.is_unique = || - index->pPartIdxWhere !=3D NULL) - continue; - /* - * Index is a UNIQUE index (or a PRIMARY KEY) and - * has the right number of columns. If each - * indexed column corresponds to a foreign key - * column of pFKey, then this index is a winner. - */ - if (zKey =3D=3D NULL) { - /* - * If zKey is NULL, then this foreign key - * is implicitly mapped to the PRIMARY KEY - * of table pParent. The PRIMARY KEY index - * may be identified by the test. - */ - if (IsPrimaryKeyIndex(index)) { - if (aiCol !=3D NULL) { - for (int i =3D 0; i < nCol; i++) - aiCol[i] =3D = pFKey->aCol[i].iFrom; - } - break; - } - } else { - /* - * If zKey is non-NULL, then this foreign - * key was declared to map to an explicit - * list of columns in table pParent. Check - * if this index matches those columns. - * Also, check that the index uses the - * default collation sequences for each - * column. - */ - int i, j; - struct key_part *part =3D = index->def->key_def->parts; - for (i =3D 0; i < nCol; i++, part++) { - /* - * Index of column in parent - * table. - */ - i16 iCol =3D (int) part->fieldno; - /* - * If the index uses a collation - * sequence that is different from - * the default collation sequence - * for the column, this index is - * unusable. Bail out early in - * this case. - */ - uint32_t id; - struct coll *def_coll =3D - = sql_column_collation(pParent->def, - iCol, &id); - struct coll *coll =3D part->coll; - if (def_coll !=3D coll) - break; - - char *zIdxCol =3D = pParent->def->fields[iCol].name; - for (j =3D 0; j < nCol; j++) { - if (strcmp(pFKey->aCol[j].zCol, - zIdxCol) !=3D 0) - continue; - if (aiCol) - aiCol[i] =3D = pFKey->aCol[j].iFrom; - break; - } - if (j =3D=3D nCol) - break; - } - if (i =3D=3D nCol) { - /* Index is usable. */ - break; - } - } + struct field_link *link =3D fk_def->links; + for (uint32_t i =3D 0; i < fk_def->field_count; ++i, ++link) { + int reg =3D link->child_field + reg_data + 1; + sqlite3VdbeAddOp2(v, OP_IsNull, reg, ok_label); } - - if (index =3D=3D NULL) { - sqlite3ErrorMsg(pParse, "foreign key mismatch - " - "\"%w\" referencing \"%w\"", - pFKey->pFrom->def->name, pFKey->zTo); - } - - *ppIdx =3D index; - return 0; -} - -/* - * This function is called when a row is inserted into or deleted from = the - * child table of foreign key constraint pFKey. If an SQL UPDATE is = executed - * on the child table of pFKey, this function is invoked twice for each = row - * affected - once to "delete" the old row, and then again to "insert" = the - * new row. - * - * Each time it is called, this function generates VDBE code to locate = the - * row in the parent table that corresponds to the row being inserted = into - * or deleted from the child table. If the parent row can be found, no - * special action is taken. Otherwise, if the parent row can *not* be - * found in the parent table: - * - * Operation | FK type | Action taken - * = --------------------------------------------------------------------------= - * INSERT immediate Increment the "immediate constraint = counter". - * - * DELETE immediate Decrement the "immediate constraint = counter". - * - * INSERT deferred Increment the "deferred constraint = counter". - * - * DELETE deferred Decrement the "deferred constraint = counter". - * - * These operations are identified in the comment at the top of this = file - * (fkey.c) as "I.1" and "D.1". - */ -static void -fkLookupParent(Parse * pParse, /* Parse context */ - Table * pTab, /* Parent table of FK pFKey */ - Index * pIdx, /* Unique index on parent key columns in = pTab */ - FKey * pFKey, /* Foreign key constraint */ - int *aiCol, /* Map from parent key columns to child = table columns */ - int regData, /* Address of array containing child = table row */ - int nIncr, /* Increment constraint counter by this = */ - int isIgnore /* If true, pretend pTab contains all = NULL values */ - ) -{ - int i; /* Iterator variable */ - Vdbe *v =3D sqlite3GetVdbe(pParse); /* Vdbe to add code to = */ - int iCur =3D pParse->nTab - 1; /* Cursor number to use */ - int iOk =3D sqlite3VdbeMakeLabel(v); /* jump here if parent = key found */ - struct session *user_session =3D current_session(); - - /* If nIncr is less than zero, then check at runtime if there = are any - * outstanding constraints to resolve. If there are not, there = is no need - * to check if deleting this row resolves any outstanding = violations. + uint32_t field_count =3D fk_def->field_count; + int temp_regs =3D sqlite3GetTempRange(parse_context, = field_count); + int rec_reg =3D sqlite3GetTempReg(parse_context); + vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, = parent); + link =3D fk_def->links; + for (uint32_t i =3D 0; i < field_count; ++i, ++link) { + sqlite3VdbeAddOp2(v, OP_Copy, link->child_field + 1 + = reg_data, + temp_regs + i); + } + /* + * If the parent table is the same as the child table, and + * we are about to increment the constraint-counter (i.e. + * this is an INSERT operation), then check if the row + * being inserted matches itself. If so, do not increment + * the constraint-counter. * - * Check if any of the key columns in the child table row are = NULL. If - * any are, then the constraint is considered satisfied. No need = to - * search for a matching row in the parent table. + * If any of the parent-key values are NULL, then the row + * cannot match itself. So set JUMPIFNULL to make sure we + * do the OP_Found if any of the parent-key values are + * NULL (at this point it is known that none of the child + * key values are). */ - if (nIncr < 0) { - sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, = iOk); - VdbeCoverage(v); - } - for (i =3D 0; i < pFKey->nCol; i++) { - int iReg =3D aiCol[i] + regData + 1; - sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); - VdbeCoverage(v); - } - - if (isIgnore =3D=3D 0) { - if (pIdx =3D=3D 0) { - /* If pIdx is NULL, then the parent key is the = INTEGER PRIMARY KEY - * column of the parent table (table pTab). - */ - int regTemp =3D sqlite3GetTempReg(pParse); - - /* Invoke MustBeInt to coerce the child key = value to an integer (i.e. - * apply the affinity of the parent key). If = this fails, then there - * is no matching parent key. Before using = MustBeInt, make a copy of - * the value. Otherwise, the value inserted into = the child key column - * will have INTEGER affinity applied to it, = which may not be correct. - */ - sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0] + 1 + = regData, - regTemp); - VdbeCoverage(v); - - /* If the parent table is the same as the child = table, and we are about - * to increment the constraint-counter (i.e. = this is an INSERT operation), - * then check if the row being inserted matches = itself. If so, do not - * increment the constraint-counter. - */ - if (pTab =3D=3D pFKey->p=46rom && nIncr =3D=3D = 1) { - sqlite3VdbeAddOp3(v, OP_Eq, regData, = iOk, - regTemp); - VdbeCoverage(v); - sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); - } - - } else { - int nCol =3D pFKey->nCol; - int regTemp =3D sqlite3GetTempRange(pParse, = nCol); - int regRec =3D sqlite3GetTempReg(pParse); - struct space *space =3D - space_by_id(pIdx->pTable->def->id); - vdbe_emit_open_cursor(pParse, iCur, = pIdx->def->iid, - space); - for (i =3D 0; i < nCol; i++) { - sqlite3VdbeAddOp2(v, OP_Copy, - aiCol[i] + 1 + = regData, - regTemp + i); - } - - /* If the parent table is the same as the child = table, and we are about - * to increment the constraint-counter (i.e. = this is an INSERT operation), - * then check if the row being inserted matches = itself. If so, do not - * increment the constraint-counter. - * - * If any of the parent-key values are NULL, = then the row cannot match - * itself. So set JUMPIFNULL to make sure we do = the OP_Found if any - * of the parent-key values are NULL (at this = point it is known that - * none of the child key values are). - */ - if (pTab =3D=3D pFKey->p=46rom && nIncr =3D=3D = 1) { - int iJump =3D - sqlite3VdbeCurrentAddr(v) + nCol = + 1; - struct key_part *part =3D - pIdx->def->key_def->parts; - for (i =3D 0; i < nCol; ++i, ++part) { - int iChild =3D aiCol[i] + 1 + = regData; - int iParent =3D 1 + regData + - = (int)part->fieldno; - sqlite3VdbeAddOp3(v, OP_Ne, = iChild, - iJump, = iParent); - VdbeCoverage(v); - sqlite3VdbeChangeP5(v, - = SQLITE_JUMPIFNULL); - } - sqlite3VdbeGoto(v, iOk); - } - - sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, = nCol, - regRec, - = sqlite3IndexAffinityStr(pParse->db, - pIdx), = nCol); - sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, = regRec, 0); - VdbeCoverage(v); - - sqlite3ReleaseTempReg(pParse, regRec); - sqlite3ReleaseTempRange(pParse, regTemp, nCol); + if (fkey_is_self_referenced(fk_def) && incr_count =3D=3D 1) { + int jump =3D sqlite3VdbeCurrentAddr(v) + field_count + = 1; + link =3D fk_def->links; + for (uint32_t i =3D 0; i < field_count; ++i, ++link) { + int chcol =3D link->child_field + 1 + reg_data; + int pcol =3D link->parent_field + 1 + reg_data; + sqlite3VdbeAddOp3(v, OP_Ne, chcol, jump, pcol); + sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL); } + sqlite3VdbeGoto(v, ok_label); } - - if (!pFKey->isDeferred && !(user_session->sql_flags & = SQLITE_DeferFKs) - && !pParse->pToplevel && !pParse->isMultiWrite) { - /* Special case: If this is an INSERT statement that = will insert exactly - * one row into the table, raise a constraint = immediately instead of - * incrementing a counter. This is necessary as the VM = code is being - * generated for will not open a statement transaction. + struct index *idx =3D space_index(parent, referenced_idx); + assert(idx !=3D NULL); + sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count, = rec_reg, + sql_index_affinity_str(parse_context->db, = idx->def), + P4_DYNAMIC); + sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0); + sqlite3ReleaseTempReg(parse_context, rec_reg); + sqlite3ReleaseTempRange(parse_context, temp_regs, field_count); + struct session *session =3D current_session(); + if (!fk_def->is_deferred && + (session->sql_flags & SQLITE_DeferFKs) =3D=3D 0 && + parse_context->pToplevel =3D=3D NULL && = !parse_context->isMultiWrite) { + /* + * If this is an INSERT statement that will insert + * exactly one row into the table, raise a + * constraint immediately instead of incrementing + * a counter. This is necessary as the VM code is + * being generated for will not open a statement + * transaction. */ - assert(nIncr =3D=3D 1); - sqlite3HaltConstraint(pParse, = SQLITE_CONSTRAINT_FOREIGNKEY, + assert(incr_count =3D=3D 1); + sqlite3HaltConstraint(parse_context, + SQLITE_CONSTRAINT_FOREIGNKEY, ON_CONFLICT_ACTION_ABORT, 0, = P4_STATIC, P5_ConstraintFK); } else { - if (nIncr > 0 && pFKey->isDeferred =3D=3D 0) { - sqlite3MayAbort(pParse); - } - sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, = nIncr); + if (incr_count > 0 && !fk_def->is_deferred) + sqlite3MayAbort(parse_context); + sqlite3VdbeAddOp2(v, OP_FkCounter, fk_def->is_deferred, + incr_count); } - - sqlite3VdbeResolveLabel(v, iOk); - sqlite3VdbeAddOp1(v, OP_Close, iCur); + sqlite3VdbeResolveLabel(v, ok_label); + sqlite3VdbeAddOp1(v, OP_Close, cursor); } =20 /* @@ -551,520 +345,393 @@ exprTableColumn(sqlite3 * db, struct space_def = *def, int cursor, i16 column) } =20 /* - * This function is called to generate code executed when a row is = deleted - * from the parent table of foreign key constraint pFKey and, if pFKey = is - * deferred, when a row is inserted into the same table. When = generating - * code for an SQL UPDATE operation, this function may be called twice = - - * once to "delete" the old row and once to "insert" the new row. - * - * Parameter nIncr is passed -1 when inserting a row (as this may = decrease - * the number of FK violations in the db) or +1 when deleting one (as = this - * may increase the number of FK constraint problems). - * - * The code generated by this function scans through the rows in the = child - * table that correspond to the parent table row being deleted or = inserted. - * For each child row found, one of the following actions is taken: - * - * Operation | FK type | Action taken - * = --------------------------------------------------------------------------= - * DELETE immediate Increment the "immediate constraint = counter". - * Or, if the ON (UPDATE|DELETE) action is = RESTRICT, - * throw a "FOREIGN KEY constraint failed" = exception. - * - * INSERT immediate Decrement the "immediate constraint = counter". - * - * DELETE deferred Increment the "deferred constraint = counter". - * Or, if the ON (UPDATE|DELETE) action is = RESTRICT, - * throw a "FOREIGN KEY constraint failed" = exception. - * - * INSERT deferred Decrement the "deferred constraint = counter". - * - * These operations are identified in the comment at the top of this = file - * (fkey.c) as "I.2" and "D.2". + * This function is called to generate code executed when a row is + * deleted from the parent table of foreign key constraint @a fkey + * and, if @a fkey is deferred, when a row is inserted into the + * same table. When generating code for an SQL UPDATE operation, + * this function may be called twice - once to "delete" the old + * row and once to "insert" the new row. + * + * Parameter incr_count is passed -1 when inserting a row (as this + * may decrease the number of FK violations in the db) or +1 when + * deleting one (as this may increase the number of FK constraint + * problems). + * + * The code generated by this function scans through the rows in + * the child table that correspond to the parent table row being + * deleted or inserted. For each child row found, one of the + * following actions is taken: + * + * Op | FK type | Action taken + * --------------------------------------------------------------- + * DELETE immediate Increment the "immediate constraint counter". + * Or, if the ON (UPDATE|DELETE) action is + * RESTRICT, throw a "FOREIGN KEY constraint + * failed" exception. + * + * INSERT immediate Decrement the "immediate constraint counter". + * + * DELETE deferred Increment the "deferred constraint counter". + * Or, if the ON (UPDATE|DELETE) action is + * RESTRICT, throw a "FOREIGN KEY constraint + * failed" exception. + * + * INSERT deferred Decrement the "deferred constraint counter". + * + * These operations are identified in the comment at the top of + * this file as "I.2" and "D.2". + * @param parser SQL parser. + * @param src The child table to be scanned. + * @param tab Parent table. + * @param fkey The foreign key linking src to tab. + * @param reg_data Register from which parent row data starts. + * @param incr_count Amount to increment deferred counter by. */ static void -fkScanChildren(Parse * pParse, /* Parse context */ - SrcList * pSrc, /* The child table to be scanned */ - Table * pTab, /* The parent table */ - Index * pIdx, /* Index on parent covering the foreign = key */ - FKey * pFKey, /* The foreign key linking pSrc to pTab = */ - int *aiCol, /* Map from pIdx cols to child table = cols */ - int regData, /* Parent row data starts here */ - int nIncr /* Amount to increment deferred counter = by */ - ) +fkey_scan_children(struct Parse *parser, struct SrcList *src, struct = Table *tab, + struct fkey_def *fkey, int reg_data, int incr_count) { - sqlite3 *db =3D pParse->db; /* Database handle */ - Expr *pWhere =3D 0; /* WHERE clause to scan with */ - NameContext sNameContext; /* Context used to resolve WHERE = clause */ - WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */ - int iFkIfZero =3D 0; /* Address of OP_FkIfZero */ - Vdbe *v =3D sqlite3GetVdbe(pParse); - - assert(pIdx =3D=3D NULL || pIdx->pTable =3D=3D pTab); - assert(pIdx =3D=3D NULL || (int) pIdx->def->key_def->part_count = =3D=3D pFKey->nCol); - assert(pIdx !=3D NULL); - - if (nIncr < 0) { - iFkIfZero =3D - sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, = 0); + assert(incr_count =3D=3D -1 || incr_count =3D=3D 1); + struct sqlite3 *db =3D parser->db; + struct Expr *where =3D NULL; + /* Address of OP_FkIfZero. */ + int fkifzero_label =3D 0; + struct Vdbe *v =3D sqlite3GetVdbe(parser); + + if (incr_count < 0) { + fkifzero_label =3D sqlite3VdbeAddOp2(v, OP_FkIfZero, + fkey->is_deferred, = 0); VdbeCoverage(v); } =20 - /* Create an Expr object representing an SQL expression like: + struct space *child_space =3D space_by_id(fkey->child_id); + assert(child_space !=3D NULL); + /* + * Create an Expr object representing an SQL expression + * like: * - * =3D AND =3D = ... + * =3D AND =3D = ... * - * The collation sequence used for the comparison should be that = of - * the parent key columns. The affinity of the parent key column = should - * be applied to each child key value before the comparison = takes place. + * The collation sequence used for the comparison should + * be that of the parent key columns. The affinity of the + * parent key column should be applied to each child key + * value before the comparison takes place. */ - for (int i =3D 0; i < pFKey->nCol; i++) { - Expr *pLeft; /* Value from parent table row */ - Expr *pRight; /* Column ref to child table */ - Expr *pEq; /* Expression (pLeft =3D pRight) */ - i16 iCol; /* Index of column in child table */ - const char *column_name; - - iCol =3D pIdx !=3D NULL ? - (int) pIdx->def->key_def->parts[i].fieldno : -1; - pLeft =3D exprTableRegister(pParse, pTab, regData, = iCol); - iCol =3D aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; - assert(iCol >=3D 0); - column_name =3D pFKey->pFrom->def->fields[iCol].name; - pRight =3D sqlite3Expr(db, TK_ID, column_name); - pEq =3D sqlite3PExpr(pParse, TK_EQ, pLeft, pRight); - pWhere =3D sqlite3ExprAnd(db, pWhere, pEq); + for (uint32_t i =3D 0; i < fkey->field_count; i++) { + uint32_t fieldno =3D fkey->links[i].parent_field; + struct Expr *pexpr =3D + exprTableRegister(parser, tab, reg_data, = fieldno); + fieldno =3D fkey->links[i].child_field; + const char *field_name =3D = child_space->def->fields[fieldno].name; + struct Expr *chexpr =3D sqlite3Expr(db, TK_ID, = field_name); + struct Expr *eq =3D sqlite3PExpr(parser, TK_EQ, pexpr, = chexpr); + where =3D sqlite3ExprAnd(db, where, eq); } =20 - /* If the child table is the same as the parent table, then add = terms - * to the WHERE clause that prevent this entry from being = scanned. - * The added WHERE clause terms are like this: + /* + * If the child table is the same as the parent table, + * then add terms to the WHERE clause that prevent this + * entry from being scanned. The added WHERE clause terms + * are like this: * * NOT( $current_a=3D=3Da AND $current_b=3D=3Db AND ... ) * The primary key is (a,b,...) */ - if (pTab =3D=3D pFKey->p=46rom && nIncr > 0) { - Expr *pNe; /* Expression (pLeft !=3D pRight) */ - Expr *pLeft; /* Value from parent table row */ - Expr *pRight; /* Column ref to child table */ - - Expr *pEq, *pAll =3D 0; - Index *pPk =3D sqlite3PrimaryKeyIndex(pTab); - assert(pIdx !=3D NULL); - uint32_t part_count =3D pPk->def->key_def->part_count; - for (uint32_t i =3D 0; i < part_count; i++) { - uint32_t fieldno =3D = pIdx->def->key_def->parts[i].fieldno; - pLeft =3D exprTableRegister(pParse, pTab, = regData, + if (tab->def->id =3D=3D fkey->child_id && incr_count > 0) { + struct Expr *expr =3D NULL, *pexpr, *chexpr, *eq; + for (uint32_t i =3D 0; i < fkey->field_count; i++) { + uint32_t fieldno =3D = fkey->links[i].parent_field; + pexpr =3D exprTableRegister(parser, tab, = reg_data, fieldno); - pRight =3D exprTableColumn(db, pTab->def, - pSrc->a[0].iCursor, = fieldno); - pEq =3D sqlite3PExpr(pParse, TK_EQ, pLeft, = pRight); - pAll =3D sqlite3ExprAnd(db, pAll, pEq); + chexpr =3D exprTableColumn(db, tab->def, + src->a[0].iCursor, = fieldno); + eq =3D sqlite3PExpr(parser, TK_EQ, pexpr, = chexpr); + expr =3D sqlite3ExprAnd(db, expr, eq); } - pNe =3D sqlite3PExpr(pParse, TK_NOT, pAll, 0); - pWhere =3D sqlite3ExprAnd(db, pWhere, pNe); + struct Expr *pNe =3D sqlite3PExpr(parser, TK_NOT, expr, = 0); + where =3D sqlite3ExprAnd(db, where, pNe); } =20 /* Resolve the references in the WHERE clause. */ - memset(&sNameContext, 0, sizeof(NameContext)); - sNameContext.pSrcList =3D pSrc; - sNameContext.pParse =3D pParse; - sqlite3ResolveExprNames(&sNameContext, pWhere); - - /* Create VDBE to loop through the entries in pSrc that match = the WHERE - * clause. For each row found, increment either the deferred or = immediate - * foreign key constraint counter. + struct NameContext namectx; + memset(&namectx, 0, sizeof(namectx)); + namectx.pSrcList =3D src; + namectx.pParse =3D parser; + sqlite3ResolveExprNames(&namectx, where); + + /* + * Create VDBE to loop through the entries in src that + * match the WHERE clause. For each row found, increment + * either the deferred or immediate foreign key constraint + * counter. */ - pWInfo =3D sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0); - sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); - if (pWInfo) { - sqlite3WhereEnd(pWInfo); - } + struct WhereInfo *info =3D + sqlite3WhereBegin(parser, src, where, NULL, NULL, 0, 0); + sqlite3VdbeAddOp2(v, OP_FkCounter, fkey->is_deferred, = incr_count); + if (info !=3D NULL) + sqlite3WhereEnd(info); =20 /* Clean up the WHERE clause constructed above. */ - sql_expr_delete(db, pWhere, false); - if (iFkIfZero) - sqlite3VdbeJumpHere(v, iFkIfZero); + sql_expr_delete(db, where, false); + if (fkifzero_label !=3D 0) + sqlite3VdbeJumpHere(v, fkifzero_label); } =20 -/* - * This function returns a linked list of FKey objects (connected by - * FKey.pNextTo) holding all children of table pTab. For example, - * given the following schema: - * - * CREATE TABLE t1(a PRIMARY KEY); - * CREATE TABLE t2(b REFERENCES t1(a); - * - * Calling this function with table "t1" as an argument returns a = pointer - * to the FKey structure representing the foreign key constraint on = table - * "t2". Calling this function with "t2" as the argument would return a - * NULL pointer (as there are no FK constraints for which t2 is the = parent - * table). - */ -FKey * -sqlite3FkReferences(Table * pTab) -{ - return (FKey *) sqlite3HashFind(&pTab->pSchema->fkeyHash, - pTab->def->name); -} - -/* - * The second argument points to an FKey object representing a foreign = key - * for which pTab is the child table. An UPDATE statement against pTab - * is currently being processed. For each column of the table that is - * actually updated, the corresponding element in the aChange[] array - * is zero or greater (if a column is unmodified the corresponding = element - * is set to -1). - * - * This function returns true if any of the columns that are part of = the - * child key for FK constraint *p are modified. +/** + * An UPDATE statement against the table having foreign key with + * definition @a fkey is currently being processed. For each + * updated column of the table the corresponding element in @a + * changes array is zero or greater (if a column is unmodified the + * corresponding element is set to -1). + * + * @param fkey FK constraint definition. + * @param changes Array indicating modified columns. + * @retval true, if any of the columns that are part of the child + * key for FK constraint are modified. */ -static int -fkChildIsModified(FKey * p, /* Foreign key for which pTab is the = child */ - int *aChange /* Array indicating modified columns */ - ) +static bool +fkey_child_is_modified(const struct fkey_def *fkey, const int *changes) { - int i; - for (i =3D 0; i < p->nCol; i++) { - int iChildKey =3D p->aCol[i].iFrom; - if (aChange[iChildKey] >=3D 0) - return 1; + for (uint32_t i =3D 0; i < fkey->field_count; ++i) { + uint32_t child_key =3D fkey->links[i].child_field; + if (changes[child_key] >=3D 0) + return true; } - return 0; + return false; } =20 -/* - * The second argument points to an FKey object representing a foreign = key - * for which pTab is the parent table. An UPDATE statement against pTab - * is currently being processed. For each column of the table that is - * actually updated, the corresponding element in the aChange[] array - * is zero or greater (if a column is unmodified the corresponding = element - * is set to -1). - * - * This function returns true if any of the columns that are part of = the - * parent key for FK constraint *p are modified. +/** + * Works the same as fkey_child_is_modified(), but checks are + * provided on parent table. */ -static int -fkParentIsModified(Table * pTab, FKey * p, int *aChange) +static bool +fkey_parent_is_modified(const struct fkey_def *fkey, const int = *changes) { - int i; - for (i =3D 0; i < p->nCol; i++) { - char *zKey =3D p->aCol[i].zCol; - int iKey; - for (iKey =3D 0; iKey < (int)pTab->def->field_count; = iKey++) { - if (aChange[iKey] >=3D 0) { - if (zKey) { - if = (strcmp(pTab->def->fields[iKey].name, - zKey) =3D=3D 0) - return 1; - } else if (table_column_is_in_pk(pTab, = iKey)) { - return 1; - } - } - } + for (uint32_t i =3D 0; i < fkey->field_count; i++) { + uint32_t parent_key =3D fkey->links[i].parent_field; + if (changes[parent_key] >=3D 0) + return true; } - return 0; + return false; } =20 -/* - * Return true if the parser passed as the first argument is being - * used to code a trigger that is really a "SET NULL" action belonging - * to trigger pFKey. +/** + * Return true if the parser passed as the first argument is + * used to code a trigger that is really a "SET NULL" action. */ -static int -isSetNullAction(Parse * pParse, FKey * pFKey) +static bool +fkey_action_is_set_null(struct Parse *parse_context, const struct fkey = *fkey) { - Parse *pTop =3D sqlite3ParseToplevel(pParse); - if (pTop->pTriggerPrg !=3D NULL) { - struct sql_trigger *trigger =3D = pTop->pTriggerPrg->trigger; - if ((trigger =3D=3D pFKey->apTrigger[0] && - pFKey->aAction[0] =3D=3D OE_SetNull) || - (trigger =3D=3D pFKey->apTrigger[1] - && pFKey->aAction[1] =3D=3D OE_SetNull)) - return 1; + struct Parse *top_parse =3D sqlite3ParseToplevel(parse_context); + if (top_parse->pTriggerPrg !=3D NULL) { + struct sql_trigger *trigger =3D = top_parse->pTriggerPrg->trigger; + if ((trigger =3D=3D fkey->on_delete_trigger && + fkey->def->on_delete =3D=3D FKEY_ACTION_SET_NULL) = || + (trigger =3D=3D fkey->on_update_trigger && + fkey->def->on_update =3D=3D FKEY_ACTION_SET_NULL)) + return true; } - return 0; + return false; } =20 -/* - * This function is called when inserting, deleting or updating a row = of - * table pTab to generate VDBE code to perform foreign key constraint - * processing for the operation. - * - * For a DELETE operation, parameter regOld is passed the index of the - * first register in an array of (pTab->nCol+1) registers containing = the - * PK of the row being deleted, followed by each of the column values - * of the row being deleted, from left to right. Parameter regNew is = passed - * zero in this case. - * - * For an INSERT operation, regOld is passed zero and regNew is passed = the - * first register of an array of (pTab->nCol+1) registers containing = the new - * row data. - * - * For an UPDATE operation, this function is called twice. Once before - * the original record is deleted from the table using the calling = convention - * described for DELETE. Then again after the original record is = deleted - * but before the new record is inserted using the INSERT convention. - */ void -sqlite3FkCheck(Parse * pParse, /* Parse context */ - Table * pTab, /* Row is being deleted from this table = */ - int regOld, /* Previous row data is stored here */ - int regNew, /* New row data is stored here */ - int *aChange /* Array indicating UPDATEd columns (or = 0) */ - ) +fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, + int reg_new, const int *changed_cols) { - sqlite3 *db =3D pParse->db; /* Database handle */ - FKey *pFKey; /* Used to iterate through FKs */ + struct sqlite3 *db =3D parser->db; struct session *user_session =3D current_session(); =20 - /* Exactly one of regOld and regNew should be non-zero. */ - assert((regOld =3D=3D 0) !=3D (regNew =3D=3D 0)); + /* + * Exactly one of reg_old and reg_new should be non-zero. + */ + assert((reg_old =3D=3D 0) !=3D (reg_new =3D=3D 0)); =20 - /* If foreign-keys are disabled, this function is a no-op. */ + /* + * If foreign-keys are disabled, this function is a no-op. + */ if ((user_session->sql_flags & SQLITE_ForeignKeys) =3D=3D 0) return; =20 - /* Loop through all the foreign key constraints for which pTab = is the - * child table (the table that the foreign key definition is = part of). + /* + * Loop through all the foreign key constraints for which + * tab is the child table. */ - for (pFKey =3D pTab->pFKey; pFKey; pFKey =3D pFKey->pNextFrom) { - Table *pTo; /* Parent table of foreign key pFKey */ - Index *pIdx =3D 0; /* Index on key columns in pTo = */ - int *aiFree =3D 0; - int *aiCol; - int iCol; - int bIgnore =3D 0; - - if (aChange - && sqlite3_stricmp(pTab->def->name, pFKey->zTo) !=3D = 0 - && fkChildIsModified(pFKey, aChange) =3D=3D 0) { + struct space *space =3D space_by_id(tab->def->id); + assert(space !=3D NULL); + struct fkey *fk; + rlist_foreach_entry(fk, &space->child_fkey, child_link) { + struct fkey_def *fk_def =3D fk->def; + if (changed_cols !=3D NULL && = !fkey_is_self_referenced(fk_def) && + !fkey_child_is_modified(fk_def, changed_cols)) continue; - } - - /* Find the parent table of this foreign key. Also find = a unique index - * on the parent key columns in the parent table. If = either of these - * schema items cannot be located, set an error in = pParse and return - * early. - */ - pTo =3D sqlite3LocateTable(pParse, 0, pFKey->zTo); - if (pTo =3D=3D NULL || sqlite3FkLocateIndex(pParse, pTo, = pFKey, - &pIdx, &aiFree) = !=3D 0) - return; - assert(pFKey->nCol =3D=3D 1 || (aiFree && pIdx)); - - if (aiFree) { - aiCol =3D aiFree; - } else { - iCol =3D pFKey->aCol[0].iFrom; - aiCol =3D &iCol; - } - - pParse->nTab++; - - if (regOld !=3D 0) { - /* A row is being removed from the child table. = Search for the parent. - * If the parent does not exist, removing the = child row resolves an - * outstanding foreign key constraint violation. + parser->nTab++; + struct space *parent =3D space_by_id(fk_def->parent_id); + assert(parent !=3D NULL); + if (reg_old !=3D 0) { + /* + * A row is being removed from the child + * table. Search for the parent. If the + * parent does not exist, removing the + * child row resolves an outstanding + * foreign key constraint violation. */ - fkLookupParent(pParse, pTo, pIdx, pFKey, aiCol, - regOld, -1, bIgnore); + fkey_lookup_parent(parser, parent, fk_def, = fk->index_id, + reg_old, -1); } - if (regNew !=3D 0 && !isSetNullAction(pParse, pFKey)) { - /* A row is being added to the child table. If a = parent row cannot - * be found, adding the child row has violated = the FK constraint. + if (reg_new !=3D 0 && !fkey_action_is_set_null(parser, = fk)) { + /* + * A row is being added to the child + * table. If a parent row cannot be found, + * adding the child row has violated the + * FK constraint. * - * If this operation is being performed as part = of a trigger program - * that is actually a "SET NULL" action = belonging to this very - * foreign key, then omit this scan altogether. = As all child key - * values are guaranteed to be NULL, it is not = possible for adding - * this row to cause an FK violation. + * If this operation is being performed as + * part of a trigger program that is + * actually a "SET NULL" action belonging + * to this very foreign key, then omit + * this scan altogether. As all child key + * values are guaranteed to be NULL, it is + * not possible for adding this row to + * cause an FK violation. */ - fkLookupParent(pParse, pTo, pIdx, pFKey, aiCol, - regNew, +1, bIgnore); + fkey_lookup_parent(parser, parent, fk_def, = fk->index_id, + reg_new, +1); } - - sqlite3DbFree(db, aiFree); } - - /* Loop through all the foreign key constraints that refer to = this table. - * (the "child" constraints) + /* + * Loop through all the foreign key constraints that + * refer to this table. */ - for (pFKey =3D sqlite3FkReferences(pTab); pFKey; pFKey =3D = pFKey->pNextTo) { - Index *pIdx =3D 0; /* Foreign key index for pFKey = */ - SrcList *pSrc; - int *aiCol =3D 0; - - if (aChange - && fkParentIsModified(pTab, pFKey, aChange) =3D=3D = 0) { + rlist_foreach_entry(fk, &space->parent_fkey, parent_link) { + struct fkey_def *fk_def =3D fk->def; + if (changed_cols !=3D NULL && + !fkey_parent_is_modified(fk_def, changed_cols)) continue; - } - - if (!pFKey->isDeferred - && !(user_session->sql_flags & SQLITE_DeferFKs) - && !pParse->pToplevel && !pParse->isMultiWrite) { - assert(regOld =3D=3D 0 && regNew !=3D 0); - /* Inserting a single row into a parent table = cannot cause (or fix) - * an immediate foreign key violation. So do = nothing in this case. + if (!fk_def->is_deferred && + (user_session->sql_flags & SQLITE_DeferFKs) =3D=3D 0 = && + parser->pToplevel =3D=3D NULL && = !parser->isMultiWrite) { + assert(reg_old =3D=3D 0 && reg_new !=3D 0); + /* + * Inserting a single row into a parent + * table cannot cause (or fix) an + * immediate foreign key violation. So do + * nothing in this case. */ continue; } =20 - if (sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, - &aiCol) !=3D 0) - return; - assert(aiCol || pFKey->nCol =3D=3D 1); - - /* Create a SrcList structure containing the child = table. We need the - * child table as a SrcList for sqlite3WhereBegin() + /* + * Create a SrcList structure containing the child + * table. We need the child table as a SrcList for + * sqlite3WhereBegin(). */ - pSrc =3D sqlite3SrcListAppend(db, 0, 0); - if (pSrc) { - struct SrcList_item *pItem =3D pSrc->a; - pItem->pTab =3D pFKey->pFrom; - pItem->zName =3D pFKey->pFrom->def->name; - pItem->pTab->nTabRef++; - pItem->iCursor =3D pParse->nTab++; - - if (regNew !=3D 0) { - fkScanChildren(pParse, pSrc, pTab, pIdx, = pFKey, - aiCol, regNew, -1); - } - if (regOld !=3D 0) { - int eAction =3D pFKey->aAction[aChange = !=3D 0]; - fkScanChildren(pParse, pSrc, pTab, pIdx, = pFKey, - aiCol, regOld, 1); - /* If this is a deferred FK constraint, = or a CASCADE or SET NULL - * action applies, then any foreign key = violations caused by - * removing the parent key will be = rectified by the action trigger. - * So do not set the "may-abort" flag in = this case. - * - * Note 1: If the FK is declared "ON = UPDATE CASCADE", then the - * may-abort flag will eventually be set = on this statement anyway - * (when this function is called as part = of processing the UPDATE - * within the action trigger). - * - * Note 2: At first glance it may seem = like SQLite could simply omit - * all OP_FkCounter related scans when = either CASCADE or SET NULL - * applies. The trouble starts if the = CASCADE or SET NULL action - * trigger causes other triggers or = action rules attached to the - * child table to fire. In these cases = the fk constraint counters - * might be set incorrectly if any = OP_FkCounter related scans are - * omitted. - */ - if (!pFKey->isDeferred && eAction !=3D = OE_Cascade - && eAction !=3D OE_SetNull) { - sqlite3MayAbort(pParse); - } - } - pItem->zName =3D 0; - sqlite3SrcListDelete(db, pSrc); - } - sqlite3DbFree(db, aiCol); - } -} - -#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) - -/* - * This function is called before generating code to update or delete a - * row contained in table pTab. - */ -u32 -sqlite3FkOldmask(Parse * pParse, /* Parse context */ - Table * pTab /* Table being modified */ - ) -{ - u32 mask =3D 0; - struct session *user_session =3D current_session(); - - if (user_session->sql_flags & SQLITE_ForeignKeys) { - FKey *p; - for (p =3D pTab->pFKey; p; p =3D p->pNextFrom) { - for (int i =3D 0; i < p->nCol; i++) - mask |=3D COLUMN_MASK(p->aCol[i].iFrom); + struct SrcList *src =3D sqlite3SrcListAppend(db, NULL, = NULL); + if (src =3D=3D NULL) + continue; + struct SrcList_item *item =3D src->a; + struct space *child =3D space_by_id(fk->def->child_id); + assert(child !=3D NULL); + struct Table *child_tab =3D = sqlite3HashFind(&db->pSchema->tblHash, + = child->def->name); + item->pTab =3D child_tab; + item->zName =3D sqlite3DbStrDup(db, child->def->name); + item->pTab->nTabRef++; + item->iCursor =3D parser->nTab++; + + if (reg_new !=3D 0) { + fkey_scan_children(parser, src, tab, fk->def, = reg_new, + -1); } - for (p =3D sqlite3FkReferences(pTab); p; p =3D = p->pNextTo) { - Index *pIdx =3D 0; - sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0); - if (pIdx !=3D NULL) { - uint32_t part_count =3D - pIdx->def->key_def->part_count; - for (uint32_t i =3D 0; i < part_count; = i++) { - mask |=3D = COLUMN_MASK(pIdx->def-> - = key_def->parts[i].fieldno); - } - } + if (reg_old !=3D 0) { + enum fkey_action action =3D fk_def->on_update; + fkey_scan_children(parser, src, tab, fk->def, = reg_old, + 1); + /* + * If this is a deferred FK constraint, or + * a CASCADE or SET NULL action applies, + * then any foreign key violations caused + * by removing the parent key will be + * rectified by the action trigger. So do + * not set the "may-abort" flag in this + * case. + * + * Note 1: If the FK is declared "ON + * UPDATE CASCADE", then the may-abort + * flag will eventually be set on this + * statement anyway (when this function is + * called as part of processing the UPDATE + * within the action trigger). + * + * Note 2: At first glance it may seem + * like SQLite could simply omit all + * OP_FkCounter related scans when either + * CASCADE or SET NULL applies. The + * trouble starts if the CASCADE or SET + * NULL action trigger causes other + * triggers or action rules attached to + * the child table to fire. In these cases + * the fk constraint counters might be set + * incorrectly if any OP_FkCounter related + * scans are omitted. + */ + if (!fk_def->is_deferred && + action !=3D FKEY_ACTION_CASCADE && + action !=3D FKEY_ACTION_SET_NULL) + sqlite3MayAbort(parser); } + sqlite3SrcListDelete(db, src); } - return mask; } =20 -/* - * This function is called before generating code to update or delete a - * row contained in table pTab. If the operation is a DELETE, then - * parameter aChange is passed a NULL value. For an UPDATE, aChange = points - * to an array of size N, where N is the number of columns in table = pTab. - * If the i'th column is not modified by the UPDATE, then the = corresponding - * entry in the aChange[] array is set to -1. If the column is = modified, - * the value is 0 or greater. - * - * If any foreign key processing will be required, this function = returns - * true. If there is no foreign key related processing, this function - * returns false. - */ -int -sqlite3FkRequired(Table * pTab, /* Table being modified */ - int *aChange /* Non-NULL for UPDATE operations */ - ) +bool +fkey_is_required(uint32_t space_id, const int *changes) { struct session *user_session =3D current_session(); - if (user_session->sql_flags & SQLITE_ForeignKeys) { - if (!aChange) { - /* A DELETE operation. Foreign key processing is = required if the - * table in question is either the child or = parent table for any - * foreign key constraint. - */ - return (sqlite3FkReferences(pTab) || = pTab->pFKey); - } else { - /* This is an UPDATE. Foreign key processing is = only required if the - * operation modifies one or more child or = parent key columns. - */ - FKey *p; - - /* Check if any child key columns are being = modified. */ - for (p =3D pTab->pFKey; p; p =3D p->pNextFrom) { - if (fkChildIsModified(p, aChange)) - return 1; - } - - /* Check if any parent key columns are being = modified. */ - for (p =3D sqlite3FkReferences(pTab); p; p =3D = p->pNextTo) { - if (fkParentIsModified(pTab, p, = aChange)) - return 1; - } - } + if ((user_session->sql_flags & SQLITE_ForeignKeys) =3D=3D 0) + return false; + struct space *space =3D space_by_id(space_id); + if (changes =3D=3D NULL) { + /* + * A DELETE operation. FK processing is required + * if space is child or parent. + */ + return ! rlist_empty(&space->parent_fkey) || + ! rlist_empty(&space->child_fkey); + } + /* + * This is an UPDATE. FK processing is only required if + * the operation modifies one or more child or parent key + * columns. + */ + struct fkey *fk; + rlist_foreach_entry(fk, &space->child_fkey, child_link) { + if (fkey_child_is_modified(fk->def, changes)) + return true; } - return 0; + rlist_foreach_entry(fk, &space->parent_fkey, parent_link) { + if (fkey_parent_is_modified(fk->def, changes)) + return true; + } + return false; } =20 /** * This function is called when an UPDATE or DELETE operation is * being compiled on table pTab, which is the parent table of - * foreign-key pFKey. + * foreign-key fkey. * If the current operation is an UPDATE, then the pChanges * parameter is passed a pointer to the list of columns being * modified. If it is a DELETE, pChanges is passed a NULL pointer. * * It returns a pointer to a sql_trigger structure containing a * trigger equivalent to the ON UPDATE or ON DELETE action - * specified by pFKey. + * specified by fkey. * If the action is "NO ACTION" or "RESTRICT", then a NULL pointer * is returned (these actions require no special handling by the * triggers sub-system, code for them is created by - * fkScanChildren()). + * fkey_scan_children()). * - * For example, if pFKey is the foreign key and pTab is table "p" + * For example, if fkey is the foreign key and pTab is table "p" * in the following schema: * * CREATE TABLE p(pk PRIMARY KEY); @@ -1078,308 +745,214 @@ sqlite3FkRequired(Table * pTab, /* Table = being modified */ * * The returned pointer is cached as part of the foreign key * object. It is eventually freed along with the rest of the - * foreign key object by sqlite3FkDelete(). + * foreign key object by fkey_delete(). * * @param pParse Parse context. * @param pTab Table being updated or deleted from. - * @param pFKey Foreign key to get action for. - * @param pChanges Change-list for UPDATE, NULL for DELETE. + * @param fkey Foreign key to get action for. + * @param is_update True if action is on update. * * @retval not NULL on success. * @retval NULL on failure. */ static struct sql_trigger * -fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey = *pFKey, - struct ExprList *pChanges) +fkey_action_trigger(struct Parse *pParse, struct Table *pTab, struct = fkey *fkey, + bool is_update) { - sqlite3 *db =3D pParse->db; /* Database handle */ - int action; /* One of OE_None, OE_Cascade etc. */ - /* Trigger definition to return. */ - struct sql_trigger *trigger; - int iAction =3D (pChanges !=3D 0); /* 1 for UPDATE, 0 for = DELETE */ - struct session *user_session =3D current_session(); - - action =3D pFKey->aAction[iAction]; - if (action =3D=3D OE_Restrict - && (user_session->sql_flags & SQLITE_DeferFKs)) { - return 0; - } - trigger =3D pFKey->apTrigger[iAction]; - - if (action !=3D ON_CONFLICT_ACTION_NONE && trigger =3D=3D NULL) = { - char const *zFrom; /* Name of child table */ - int nFrom; /* Length in bytes of z=46rom */ - Index *pIdx =3D 0; /* Parent key index for this FK = */ - int *aiCol =3D 0; /* child table cols -> parent = key cols */ - TriggerStep *pStep =3D 0; /* First (only) step of = trigger program */ - Expr *pWhere =3D 0; /* WHERE clause of trigger step = */ - ExprList *pList =3D 0; /* Changes list if ON UPDATE = CASCADE */ - Select *pSelect =3D 0; /* If RESTRICT, "SELECT = RAISE(...)" */ - int i; /* Iterator variable */ - Expr *pWhen =3D 0; /* WHEN clause for the trigger = */ - - if (sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, = &aiCol)) - return 0; - assert(aiCol || pFKey->nCol =3D=3D 1); - - for (i =3D 0; i < pFKey->nCol; i++) { - Token tOld =3D { "old", 3, false }; /* = Literal "old" token */ - Token tNew =3D { "new", 3, false }; /* = Literal "new" token */ - Token tFromCol; /* Name of column in child table = */ - Token tToCol; /* Name of column in parent = table */ - int iFromCol; /* Idx of column in child table = */ - Expr *pEq; /* tFromCol =3D OLD.tToCol */ - - iFromCol =3D aiCol ? aiCol[i] : = pFKey->aCol[0].iFrom; - assert(iFromCol >=3D 0); - assert(pIdx !=3D NULL); + struct sqlite3 *db =3D pParse->db; + struct fkey_def *fk_def =3D fkey->def; + enum fkey_action action =3D is_update ? fk_def->on_update : + fk_def->on_delete; + struct sql_trigger *trigger =3D is_update ? = fkey->on_update_trigger : + = fkey->on_delete_trigger; + if (action =3D=3D FKEY_NO_ACTION || trigger !=3D NULL) + return trigger; + struct TriggerStep *step =3D NULL; + struct Expr *where =3D NULL, *when =3D NULL; + struct ExprList *list =3D NULL; + struct Select *select =3D NULL; + struct space *child_space =3D space_by_id(fk_def->child_id); + assert(child_space !=3D NULL); + for (uint32_t i =3D 0; i < fk_def->field_count; ++i) { + /* Literal "old" token. */ + struct Token t_old =3D { "old", 3, false }; + /* Literal "new" token. */ + struct Token t_new =3D { "new", 3, false }; + /* Name of column in child table. */ + struct Token t_from_col; + /* Name of column in parent table. */ + struct Token t_to_col; + struct field_def *child_fields =3D = child_space->def->fields; + + uint32_t pcol =3D fk_def->links[i].parent_field; + sqlite3TokenInit(&t_to_col, = pTab->def->fields[pcol].name); + + uint32_t chcol =3D fk_def->links[i].child_field; + sqlite3TokenInit(&t_from_col, child_fields[chcol].name); =20 - uint32_t fieldno =3D = pIdx->def->key_def->parts[i].fieldno; - sqlite3TokenInit(&tToCol, - = pTab->def->fields[fieldno].name); - sqlite3TokenInit(&tFromCol, - pFKey->pFrom->def->fields[ - iFromCol].name); - - /* Create the expression "OLD.zToCol =3D = zFromCol". It is important - * that the "OLD.zToCol" term is on the LHS of = the =3D operator, so - * that the affinity and collation sequence = associated with the - * parent table are used for the comparison. - */ - pEq =3D sqlite3PExpr(pParse, TK_EQ, - sqlite3PExpr(pParse, TK_DOT, - = sqlite3ExprAlloc(db, - = TK_ID, - = &tOld, - = 0), - = sqlite3ExprAlloc(db, - = TK_ID, - = &tToCol, - = 0)), - sqlite3ExprAlloc(db, TK_ID, - &tFromCol, = 0) - ); - pWhere =3D sqlite3ExprAnd(db, pWhere, pEq); + /* + * Create the expression "old.to_col =3D from_col". + * It is important that the "old.to_col" term is + * on the LHS of the =3D operator, so that the + * affinity and collation sequence associated with + * the parent table are used for the comparison. + */ + struct Expr *to_col =3D + sqlite3PExpr(pParse, TK_DOT, + sqlite3ExprAlloc(db, TK_ID, &t_old, = 0), + sqlite3ExprAlloc(db, TK_ID, = &t_to_col, 0)); + struct Expr *from_col =3D + sqlite3ExprAlloc(db, TK_ID, &t_from_col, 0); + struct Expr *eq =3D sqlite3PExpr(pParse, TK_EQ, to_col, = from_col); + where =3D sqlite3ExprAnd(db, where, eq); =20 - /* For ON UPDATE, construct the next term of the = WHEN clause. - * The final WHEN clause will be like this: - * - * WHEN NOT(old.col1 =3D new.col1 AND ... AND = old.colN =3D new.colN) - */ - if (pChanges) { - pEq =3D sqlite3PExpr(pParse, TK_EQ, - sqlite3PExpr(pParse, = TK_DOT, - = sqlite3ExprAlloc - (db, = TK_ID, - &tOld, = 0), - = sqlite3ExprAlloc - (db, = TK_ID, - = &tToCol, 0)), - sqlite3PExpr(pParse, = TK_DOT, - = sqlite3ExprAlloc - (db, = TK_ID, - &tNew, = 0), - = sqlite3ExprAlloc - (db, = TK_ID, - = &tToCol, 0)) - ); - pWhen =3D sqlite3ExprAnd(db, pWhen, = pEq); - } + /* + * For ON UPDATE, construct the next term of the + * WHEN clause. The final WHEN clause will be like + * this: + * + * WHEN NOT(old.col1 =3D new.col1 AND ... AND + * old.colN =3D new.colN) + */ + if (is_update) { + struct Expr *l, *r; + l =3D sqlite3PExpr(pParse, TK_DOT, + sqlite3ExprAlloc(db, TK_ID, = &t_old, 0), + sqlite3ExprAlloc(db, TK_ID, = &t_to_col, + 0)); + r =3D sqlite3PExpr(pParse, TK_DOT, + sqlite3ExprAlloc(db, TK_ID, = &t_new, 0), + sqlite3ExprAlloc(db, TK_ID, = &t_to_col, + 0)); + eq =3D sqlite3PExpr(pParse, TK_EQ, l, r); + when =3D sqlite3ExprAnd(db, when, eq); + } =20 - if (action !=3D OE_Restrict - && (action !=3D OE_Cascade || pChanges)) { - Expr *pNew; - if (action =3D=3D OE_Cascade) { - pNew =3D sqlite3PExpr(pParse, = TK_DOT, - = sqlite3ExprAlloc(db, - = TK_ID, - = &tNew, - = 0), - = sqlite3ExprAlloc(db, - = TK_ID, - = &tToCol, - = 0)); - } else if (action =3D=3D OE_SetDflt) { - Expr *pDflt =3D - = space_column_default_expr( - = pFKey->pFrom->def->id, - = (uint32_t)iFromCol); - if (pDflt) { - pNew =3D - sqlite3ExprDup(db, = pDflt, - 0); - } else { - pNew =3D - sqlite3ExprAlloc(db, - = TK_NULL, 0, - 0); - } + if (action !=3D FKEY_ACTION_RESTRICT && + (action !=3D FKEY_ACTION_CASCADE || is_update)) { + struct Expr *new, *d; + if (action =3D=3D FKEY_ACTION_CASCADE) { + new =3D sqlite3PExpr(pParse, TK_DOT, + sqlite3ExprAlloc(db, = TK_ID, + = &t_new, 0), + sqlite3ExprAlloc(db, = TK_ID, + = &t_to_col, + 0)); + } else if (action =3D=3D = FKEY_ACTION_SET_DEFAULT) { + d =3D = child_fields[chcol].default_value_expr; + if (d !=3D NULL) { + new =3D sqlite3ExprDup(db, d, = 0); } else { - pNew =3D - sqlite3ExprAlloc(db, = TK_NULL, 0, 0); + new =3D sqlite3ExprAlloc(db, = TK_NULL, + NULL, 0); } - pList =3D - sql_expr_list_append(pParse->db, = pList, - pNew); - sqlite3ExprListSetName(pParse, pList, = &tFromCol, - 0); + } else { + new =3D sqlite3ExprAlloc(db, TK_NULL, = NULL, 0); } + list =3D sql_expr_list_append(db, list, new); + sqlite3ExprListSetName(pParse, list, = &t_from_col, 0); } - sqlite3DbFree(db, aiCol); - - z=46rom =3D pFKey->pFrom->def->name; - n=46rom =3D sqlite3Strlen30(zFrom); - - if (action =3D=3D OE_Restrict) { - Token tFrom; - Expr *pRaise; + } =20 - tFrom.z =3D zFrom; - tFrom.n =3D nFrom; - pRaise =3D - sqlite3Expr(db, TK_RAISE, - "FOREIGN KEY constraint = failed"); - if (pRaise) { - pRaise->affinity =3D = ON_CONFLICT_ACTION_ABORT; - } - pSelect =3D sqlite3SelectNew(pParse, - = sql_expr_list_append(pParse->db, - = NULL, - = pRaise), - = sqlite3SrcListAppend(db, 0, - = &tFrom), - pWhere, 0, 0, 0, 0, = 0, 0); - pWhere =3D 0; - } - trigger =3D (struct sql_trigger = *)sqlite3DbMallocZero(db, - = sizeof(*trigger)); - if (trigger !=3D NULL) { - size_t step_size =3D sizeof(TriggerStep) + n=46rom= + 1; - trigger->step_list =3D sqlite3DbMallocZero(db, = step_size); - pStep =3D trigger->step_list; - pStep->zTarget =3D (char *)&pStep[1]; - memcpy(pStep->zTarget, zFrom, nFrom); - pStep->pWhere =3D - sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); - pStep->pExprList =3D - sql_expr_list_dup(db, pList, = EXPRDUP_REDUCE); - pStep->pSelect =3D - sqlite3SelectDup(db, pSelect, = EXPRDUP_REDUCE); - if (pWhen) { - pWhen =3D sqlite3PExpr(pParse, TK_NOT, = pWhen, 0); - trigger->pWhen =3D - sqlite3ExprDup(db, pWhen, = EXPRDUP_REDUCE); - } - } + const char *space_name =3D child_space->def->name; + uint32_t name_len =3D strlen(space_name); + + if (action =3D=3D FKEY_ACTION_RESTRICT) { + struct Token err; + err.z =3D space_name; + err.n =3D name_len; + struct Expr *r =3D sqlite3Expr(db, TK_RAISE, "FOREIGN = KEY "\ + "constraint failed"); + if (r !=3D NULL) + r->affinity =3D ON_CONFLICT_ACTION_ABORT; + select =3D sqlite3SelectNew(pParse, + sql_expr_list_append(db, NULL, = r), + sqlite3SrcListAppend(db, NULL, = &err), + where, NULL, NULL, NULL, 0, = NULL, + NULL); + where =3D NULL; + } =20 - sql_expr_delete(db, pWhere, false); - sql_expr_delete(db, pWhen, false); - sql_expr_list_delete(db, pList); - sql_select_delete(db, pSelect); - if (db->mallocFailed =3D=3D 1) { - sql_trigger_delete(db, trigger); - return 0; + trigger =3D (struct sql_trigger *) sqlite3DbMallocZero(db, + = sizeof(*trigger)); + if (trigger !=3D NULL) { + size_t step_size =3D sizeof(TriggerStep) + name_len + 1; + trigger->step_list =3D sqlite3DbMallocZero(db, = step_size); + step =3D trigger->step_list; + step->zTarget =3D (char *) &step[1]; + memcpy((char *) step->zTarget, space_name, name_len); + + step->pWhere =3D sqlite3ExprDup(db, where, = EXPRDUP_REDUCE); + step->pExprList =3D sql_expr_list_dup(db, list, = EXPRDUP_REDUCE); + step->pSelect =3D sqlite3SelectDup(db, select, = EXPRDUP_REDUCE); + if (when !=3D NULL) { + when =3D sqlite3PExpr(pParse, TK_NOT, when, 0); + trigger->pWhen =3D + sqlite3ExprDup(db, when, = EXPRDUP_REDUCE); } - assert(pStep !=3D 0); + } =20 - switch (action) { - case OE_Restrict: - pStep->op =3D TK_SELECT; + sql_expr_delete(db, where, false); + sql_expr_delete(db, when, false); + sql_expr_list_delete(db, list); + sql_select_delete(db, select); + if (db->mallocFailed) { + sql_trigger_delete(db, trigger); + return NULL; + } + assert(step !=3D NULL); + + switch (action) { + case FKEY_ACTION_RESTRICT: + step->op =3D TK_SELECT; + break; + case FKEY_ACTION_CASCADE: + if (! is_update) { + step->op =3D TK_DELETE; break; - case OE_Cascade: - if (!pChanges) { - pStep->op =3D TK_DELETE; - break; - } - FALLTHROUGH; - default: - pStep->op =3D TK_UPDATE; } - pStep->trigger =3D trigger; - pFKey->apTrigger[iAction] =3D trigger; - trigger->op =3D pChanges ? TK_UPDATE : TK_DELETE; + FALLTHROUGH; + default: + step->op =3D TK_UPDATE; } =20 + step->trigger =3D trigger; + if (is_update) { + fkey->on_update_trigger =3D trigger; + trigger->op =3D TK_UPDATE; + } else { + fkey->on_delete_trigger =3D trigger; + trigger->op =3D TK_DELETE; + } return trigger; } =20 -/* - * This function is called when deleting or updating a row to implement - * any required CASCADE, SET NULL or SET DEFAULT actions. - */ void -sqlite3FkActions(Parse * pParse, /* Parse context */ - Table * pTab, /* Table being updated or deleted from = */ - ExprList * pChanges, /* Change-list for UPDATE, NULL = for DELETE */ - int regOld, /* Address of array containing old row = */ - int *aChange /* Array indicating UPDATEd columns (or = 0) */ - ) +fkey_emit_actions(struct Parse *parser, struct Table *tab, int reg_old, + int *changes) { struct session *user_session =3D current_session(); - /* If foreign-key support is enabled, iterate through all FKs = that - * refer to table pTab. If there is an action associated with = the FK - * for this operation (either update or delete), invoke the = associated - * trigger sub-program. + /* + * If foreign-key support is enabled, iterate through all + * FKs that refer to table tab. If there is an action + * associated with the FK for this operation (either + * update or delete), invoke the associated trigger + * sub-program. */ - if (user_session->sql_flags & SQLITE_ForeignKeys) { - FKey *pFKey; /* Iterator variable */ - for (pFKey =3D sqlite3FkReferences(pTab); pFKey; - pFKey =3D pFKey->pNextTo) { - if (aChange =3D=3D 0 - || fkParentIsModified(pTab, pFKey, aChange)) = { - struct sql_trigger *pAct =3D - fkActionTrigger(pParse, pTab, = pFKey, - pChanges); - if (pAct =3D=3D NULL) - continue; - vdbe_code_row_trigger_direct(pParse, = pAct, pTab, - regOld, - = ON_CONFLICT_ACTION_ABORT, - 0); - } - } - } -} - -/* - * Free all memory associated with foreign key definitions attached to - * table pTab. Remove the deleted foreign keys from the Schema.fkeyHash - * hash table. - */ -void -sqlite3FkDelete(sqlite3 * db, Table * pTab) -{ - FKey *pFKey; /* Iterator variable */ - FKey *pNext; /* Copy of pFKey->pNext=46rom */ - - for (pFKey =3D pTab->pFKey; pFKey; pFKey =3D pNext) { - /* Remove the FK from the fkeyHash hash table. */ - if (!db || db->pnBytesFreed =3D=3D 0) { - if (pFKey->pPrevTo) { - pFKey->pPrevTo->pNextTo =3D = pFKey->pNextTo; - } else { - void *p =3D (void *)pFKey->pNextTo; - const char *z =3D - (p ? pFKey->pNextTo->zTo : = pFKey->zTo); - = sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, - p); - } - if (pFKey->pNextTo) { - pFKey->pNextTo->pPrevTo =3D = pFKey->pPrevTo; - } - } - - /* EV: R-30323-21917 Each foreign key constraint in = SQLite is - * classified as either immediate or deferred. - */ - assert(pFKey->isDeferred =3D=3D 0 || pFKey->isDeferred = =3D=3D 1); - - /* Delete any triggers created to implement actions for = this FK. */ - sql_trigger_delete(db, pFKey->apTrigger[0]); - sql_trigger_delete(db, pFKey->apTrigger[1]); - - pNext =3D pFKey->pNextFrom; - sqlite3DbFree(db, pFKey); + if ((user_session->sql_flags & SQLITE_ForeignKeys) =3D=3D 0) + return; + struct space *space =3D space_by_id(tab->def->id); + assert(space !=3D NULL); + struct fkey *fk; + rlist_foreach_entry(fk, &space->parent_fkey, parent_link) { + if (changes !=3D NULL && + !fkey_parent_is_modified(fk->def, changes)) + continue; + struct sql_trigger *pAct =3D + fkey_action_trigger(parser, tab, fk, changes !=3D = NULL); + if (pAct =3D=3D NULL) + continue; + vdbe_code_row_trigger_direct(parser, pAct, tab, reg_old, + ON_CONFLICT_ACTION_ABORT, = 0); } } -#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */ diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index 432e003c0..ea3ec9abc 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -837,7 +837,7 @@ sqlite3Insert(Parse * pParse, /* Parser = context */ iIdxCur, regIns, 0, true, &on_conflict, endOfLoop, &isReplace, = 0); - sqlite3FkCheck(pParse, pTab, 0, regIns, 0); + fkey_emit_check(pParse, pTab, 0, regIns, 0); vdbe_emit_insertion_completion(v, iIdxCur, aRegIdx[0], &on_conflict); } @@ -1313,15 +1313,14 @@ sqlite3GenerateConstraintChecks(Parse * pParse, = /* The parser context */ (on_error =3D=3D ON_CONFLICT_ACTION_REPLACE || on_error =3D=3D ON_CONFLICT_ACTION_IGNORE); bool no_delete_triggers =3D - (0 =3D=3D (user_session->sql_flags & - SQLITE_RecTriggers) || - sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) = =3D=3D - NULL); + (user_session->sql_flags & SQLITE_RecTriggers) = =3D=3D 0 || + sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) = =3D=3D NULL; + struct space *space =3D space_by_id(pTab->def->id); + assert(space !=3D NULL); bool no_foreign_keys =3D - (0 =3D=3D (user_session->sql_flags & - SQLITE_ForeignKeys) || - (0 =3D=3D pTab->pFKey && - 0 =3D=3D sqlite3FkReferences(pTab))); + (user_session->sql_flags & SQLITE_ForeignKeys) = =3D=3D 0 || + (rlist_empty(&space->child_fkey) && + ! rlist_empty(&space->parent_fkey)); =20 if (no_secondary_indexes && no_foreign_keys && proper_error_action && no_delete_triggers) { @@ -1559,7 +1558,7 @@ sqlite3OpenTableAndIndices(Parse * pParse, = /* Parsing context */ =20 if (isUpdate || /* Condition 1 = */ IsPrimaryKeyIndex(pIdx) || /* Condition 2 = */ - sqlite3FkReferences(pTab) || /* Condition 3 = */ + ! rlist_empty(&space->parent_fkey) || /* Condition 4 */ (pIdx->def->opts.is_unique && pIdx->onError !=3D ON_CONFLICT_ACTION_DEFAULT && @@ -1820,10 +1819,11 @@ xferOptimization(Parse * pParse, /* = Parser context */ * So the extra complication to make this rule less restrictive = is probably * not worth the effort. Ticket = [6284df89debdfa61db8073e062908af0c9b6118e] */ - if ((user_session->sql_flags & SQLITE_ForeignKeys) !=3D 0 - && pDest->pFKey !=3D 0) { + struct space *dest =3D space_by_id(pDest->def->id); + assert(dest !=3D NULL); + if ((user_session->sql_flags & SQLITE_ForeignKeys) !=3D 0 && + !rlist_empty(&dest->child_fkey)) return 0; - } #endif if ((user_session->sql_flags & SQLITE_CountRows) !=3D 0) { return 0; /* xfer opt does not play well with = PRAGMA count_changes */ diff --git a/src/box/sql/main.c b/src/box/sql/main.c index ded3b5b26..41979beb4 100644 --- a/src/box/sql/main.c +++ b/src/box/sql/main.c @@ -730,11 +730,6 @@ sqlite3RollbackAll(Vdbe * pVdbe, int tripCode) { sqlite3 *db =3D pVdbe->db; (void)tripCode; - struct session *user_session =3D current_session(); - - /* DDL is impossible inside a transaction. */ - assert((user_session->sql_flags & SQLITE_InternChanges) =3D=3D 0 - || db->init.busy =3D=3D 1); =20 /* If one has been configured, invoke the rollback-hook callback = */ if (db->xRollbackCallback && (!pVdbe->auto_commit)) { diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 0c510f565..1b06c6d87 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -51,6 +51,7 @@ // %include { #include "sqliteInt.h" +#include "box/fkey.h" =20 /* ** Disable all error recovery processing in the parser push-down @@ -281,8 +282,8 @@ ccons ::=3D UNIQUE onconf(R). = {sql_create_index(pParse,0,0,0,R,0,0, = SQL_INDEX_TYPE_CONSTRAINT_UNIQUE);} ccons ::=3D CHECK LP expr(X) RP. = {sql_add_check_constraint(pParse,&X);} ccons ::=3D REFERENCES nm(T) eidlist_opt(TA) refargs(R). - = {sqlite3CreateForeignKey(pParse,0,&T,TA,R);} -ccons ::=3D defer_subclause(D). {sqlite3DeferForeignKey(pParse,D);} + {sql_create_foreign_key(pParse, NULL, = NULL, NULL, &T, TA, false, R);} +ccons ::=3D defer_subclause(D). {fkey_change_defer_mode(pParse, D);} ccons ::=3D COLLATE id(C). {sqlite3AddCollateType(pParse, &C);} =20 // The optional AUTOINCREMENT keyword @@ -296,19 +297,23 @@ autoinc(X) ::=3D AUTOINCR. {X =3D 1;} // check fails. // %type refargs {int} -refargs(A) ::=3D . { A =3D = ON_CONFLICT_ACTION_NONE*0x0101; /* EV: R-19803-45884 */} +refargs(A) ::=3D . { A =3D FKEY_NO_ACTION; } refargs(A) ::=3D refargs(A) refarg(Y). { A =3D (A & ~Y.mask) | Y.value; = } %type refarg {struct {int value; int mask;}} -refarg(A) ::=3D MATCH nm. { A.value =3D 0; A.mask =3D = 0x000000; } +refarg(A) ::=3D MATCH matcharg(X). { A.value =3D X<<16; A.mask =3D = 0xff0000; } refarg(A) ::=3D ON INSERT refact. { A.value =3D 0; A.mask =3D = 0x000000; } refarg(A) ::=3D ON DELETE refact(X). { A.value =3D X; A.mask =3D = 0x0000ff; } refarg(A) ::=3D ON UPDATE refact(X). { A.value =3D X<<8; A.mask =3D = 0x00ff00; } +%type matcharg {int} +matcharg(A) ::=3D SIMPLE. { A =3D FKEY_MATCH_SIMPLE; } +matcharg(A) ::=3D PARTIAL. { A =3D FKEY_MATCH_PARTIAL; } +matcharg(A) ::=3D FULL. { A =3D FKEY_MATCH_FULL; } %type refact {int} -refact(A) ::=3D SET NULL. { A =3D OE_SetNull; /* EV: = R-33326-45252 */} -refact(A) ::=3D SET DEFAULT. { A =3D OE_SetDflt; /* EV: = R-33326-45252 */} -refact(A) ::=3D CASCADE. { A =3D OE_Cascade; /* EV: = R-33326-45252 */} -refact(A) ::=3D RESTRICT. { A =3D OE_Restrict; /* EV: = R-33326-45252 */} -refact(A) ::=3D NO ACTION. { A =3D ON_CONFLICT_ACTION_NONE; = /* EV: R-33326-45252 */} +refact(A) ::=3D SET NULL. { A =3D FKEY_ACTION_SET_NULL; } +refact(A) ::=3D SET DEFAULT. { A =3D FKEY_ACTION_SET_DEFAULT; = } +refact(A) ::=3D CASCADE. { A =3D FKEY_ACTION_CASCADE; } +refact(A) ::=3D RESTRICT. { A =3D FKEY_ACTION_RESTRICT; } +refact(A) ::=3D NO ACTION. { A =3D FKEY_NO_ACTION; } %type defer_subclause {int} defer_subclause(A) ::=3D NOT DEFERRABLE init_deferred_pred_opt. {A = =3D 0;} defer_subclause(A) ::=3D DEFERRABLE init_deferred_pred_opt(X). {A = =3D X;} @@ -334,8 +339,7 @@ tcons ::=3D CHECK LP expr(E) RP onconf. {sql_add_check_constraint(pParse,&E);} tcons ::=3D FOREIGN KEY LP eidlist(FA) RP REFERENCES nm(T) eidlist_opt(TA) refargs(R) = defer_subclause_opt(D). { - sqlite3CreateForeignKey(pParse, FA, &T, TA, R); - sqlite3DeferForeignKey(pParse, D); + sql_create_foreign_key(pParse, NULL, NULL, FA, &T, TA, D, R); } %type defer_subclause_opt {int} defer_subclause_opt(A) ::=3D . {A =3D 0;} @@ -1431,6 +1435,17 @@ cmd ::=3D ANALYZE nm(X). = {sqlite3Analyze(pParse, &X);} cmd ::=3D ALTER TABLE fullname(X) RENAME TO nm(Z). { sqlite3AlterRenameTable(pParse,X,&Z); } + +cmd ::=3D ALTER TABLE fullname(X) ADD CONSTRAINT nm(Z) FOREIGN KEY + LP eidlist(FA) RP REFERENCES nm(T) eidlist_opt(TA) refargs(R) + defer_subclause_opt(D). { + sql_create_foreign_key(pParse, X, &Z, FA, &T, TA, D, R); +} + +cmd ::=3D ALTER TABLE fullname(X) DROP CONSTRAINT nm(Z). { + sql_drop_foreign_key(pParse, X, &Z); +} + /* gh-3075: Commented until ALTER ADD COLUMN is implemeneted. */ /* cmd ::=3D ALTER TABLE add_column_fullname */ /* ADD kwcolumn_opt columnname(Y) carglist. { */ diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c index d427f7844..124961a6a 100644 --- a/src/box/sql/pragma.c +++ b/src/box/sql/pragma.c @@ -32,9 +32,10 @@ /* * This file contains code used to implement the PRAGMA command. */ -#include -#include -#include +#include "box/index.h" +#include "box/box.h" +#include "box/tuple.h" +#include "box/fkey.h" #include "box/schema.h" #include "box/coll_id_cache.h" #include "sqliteInt.h" @@ -154,36 +155,6 @@ returnSingleInt(Vdbe * v, i64 value) sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1); } =20 -/* - * Return a human-readable name for a constraint resolution action. - */ -#ifndef SQLITE_OMIT_FOREIGN_KEY -static const char * -actionName(u8 action) -{ - const char *zName; - switch (action) { - case OE_SetNull: - zName =3D "SET NULL"; - break; - case OE_SetDflt: - zName =3D "SET DEFAULT"; - break; - case OE_Cascade: - zName =3D "CASCADE"; - break; - case OE_Restrict: - zName =3D "RESTRICT"; - break; - default: - zName =3D "NO ACTION"; - assert(action =3D=3D ON_CONFLICT_ACTION_NONE); - break; - } - return zName; -} -#endif - /* * Locate a pragma in the aPragmaName[] array. */ @@ -588,206 +559,39 @@ sqlite3Pragma(Parse * pParse, Token * pId, = /* First part of [schema.]id field */ case PragTyp_FOREIGN_KEY_LIST:{ if (zRight =3D=3D NULL) break; - Table *table =3D sqlite3HashFind(&db->pSchema->tblHash, = zRight); - if (table =3D=3D NULL) - break; - FKey *fkey =3D table->pFKey; - if (fkey =3D=3D NULL) + uint32_t space_id =3D box_space_id_by_name(zRight, + = strlen(zRight)); + if (space_id =3D=3D BOX_ID_NIL) break; + struct space *space =3D space_by_id(space_id); int i =3D 0; pParse->nMem =3D 8; - while (fkey !=3D NULL) { - for (int j =3D 0; j < fkey->nCol; j++) { - const char *name =3D - table->def->fields[ - = fkey->aCol[j].iFrom].name; + struct fkey *fkey; + rlist_foreach_entry(fkey, &space->child_fkey, = child_link) { + struct fkey_def *fdef =3D fkey->def; + for (uint32_t j =3D 0; j < fdef->field_count; = j++) { + struct space *parent =3D + space_by_id(fdef->parent_id); + assert(parent !=3D NULL); + uint32_t ch_fl =3D = fdef->links[j].child_field; + const char *child_col =3D + space->def->fields[ch_fl].name; + uint32_t pr_fl =3D = fdef->links[j].parent_field; + const char *parent_col =3D + parent->def->fields[pr_fl].name; sqlite3VdbeMultiLoad(v, 1, "iissssss", = i, j, - fkey->zTo, name, - fkey->aCol[j].zCol, - actionName( - = fkey->aAction[1]), - actionName( - = fkey->aAction[0]), + parent->def->name, + child_col, = parent_col, + = fkey_action_strs[fdef->on_delete], + = fkey_action_strs[fdef->on_update], "NONE"); sqlite3VdbeAddOp2(v, OP_ResultRow, 1, = 8); } ++i; - fkey =3D fkey->pNextFrom; } break; } #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ - -#ifndef SQLITE_OMIT_FOREIGN_KEY - case PragTyp_FOREIGN_KEY_CHECK:{ - FKey *pFK; /* A foreign key constraint */ - Table *pTab; /* Child table contain = "REFERENCES" - * keyword - */ - Table *pParent; /* Parent table that child = points to */ - Index *pIdx; /* Index in the parent table */ - int i; /* Loop counter: Foreign key number for = pTab */ - int j; /* Loop counter: Field of the foreign = key */ - HashElem *k; /* Loop counter: Next table in = schema */ - int x; /* result variable */ - int regResult; /* 3 registers to hold a result = row */ - int regKey; /* Register to hold key for = checking - * the FK - */ - int regRow; /* Registers to hold a row from = pTab */ - int addrTop; /* Top of a loop checking = foreign keys */ - int addrOk; /* Jump here if the key is OK */ - int *aiCols; /* child to parent column = mapping */ - - regResult =3D pParse->nMem + 1; - pParse->nMem +=3D 4; - regKey =3D ++pParse->nMem; - regRow =3D ++pParse->nMem; - k =3D sqliteHashFirst(&db->pSchema->tblHash); - while (k) { - if (zRight) { - pTab =3D - sqlite3LocateTable(pParse, = 0, - zRight); - k =3D 0; - } else { - pTab =3D (Table *) = sqliteHashData(k); - k =3D sqliteHashNext(k); - } - if (pTab =3D=3D 0 || pTab->pFKey =3D=3D = 0) - continue; - if ((int)pTab->def->field_count + regRow = > pParse->nMem) - pParse->nMem =3D = pTab->def->field_count + regRow; - sqlite3OpenTable(pParse, 0, pTab, = OP_OpenRead); - sqlite3VdbeLoadString(v, regResult, - pTab->def->name); - for (i =3D 1, pFK =3D pTab->pFKey; pFK; - i++, pFK =3D pFK->pNextFrom) { - pParent =3D - = sqlite3HashFind(&db->pSchema->tblHash, - = pFK->zTo); - if (pParent =3D=3D NULL) - continue; - pIdx =3D 0; - x =3D = sqlite3FkLocateIndex(pParse, - = pParent, pFK, - &pIdx, = 0); - if (x !=3D 0) { - k =3D 0; - break; - } - if (pIdx =3D=3D NULL) { - sqlite3OpenTable(pParse, = i, - = pParent, - = OP_OpenRead); - continue; - } - struct space *space =3D - = space_cache_find(pIdx->pTable-> - = def->id); - assert(space !=3D NULL); - sqlite3VdbeAddOp4(v, = OP_OpenRead, i, - = pIdx->def->iid, 0, - (void *) = space, - P4_SPACEPTR); - - } - assert(pParse->nErr > 0 || pFK =3D=3D = 0); - if (pFK) - break; - if (pParse->nTab < i) - pParse->nTab =3D i; - addrTop =3D sqlite3VdbeAddOp1(v, = OP_Rewind, 0); - VdbeCoverage(v); - for (i =3D 1, pFK =3D pTab->pFKey; pFK; - i++, pFK =3D pFK->pNextFrom) { - pParent =3D - = sqlite3HashFind(&db->pSchema->tblHash, - = pFK->zTo); - pIdx =3D 0; - aiCols =3D 0; - if (pParent) { - x =3D = sqlite3FkLocateIndex(pParse, - = pParent, - = pFK, - = &pIdx, - = &aiCols); - assert(x =3D=3D 0); - } - addrOk =3D = sqlite3VdbeMakeLabel(v); - if (pParent && pIdx =3D=3D 0) { - int iKey =3D = pFK->aCol[0].iFrom; - assert(iKey >=3D 0 && = iKey < - = (int)pTab->def->field_count); - sqlite3VdbeAddOp3(v, - = OP_Column, - 0, - iKey, - = regRow); - sqlite3ColumnDefault(v, - = pTab->def, - = iKey, - = regRow); - sqlite3VdbeAddOp2(v, - = OP_IsNull, - = regRow, - = addrOk); - VdbeCoverage(v); - sqlite3VdbeGoto(v, = addrOk); - sqlite3VdbeJumpHere(v, - = sqlite3VdbeCurrentAddr - (v) = - 2); - } else { - for (j =3D 0; j < = pFK->nCol; j++) { - = sqlite3ExprCodeGetColumnOfTable - (v, = pTab->def, 0, - aiCols ? = aiCols[j] - : = pFK->aCol[j]. - iFrom, = regRow + j); - = sqlite3VdbeAddOp2(v, - = OP_IsNull, - = regRow - = + j, - = addrOk); - VdbeCoverage(v); - } - if (pParent) { - = sqlite3VdbeAddOp4(v, - = OP_MakeRecord, - = regRow, - = pFK-> - = nCol, - = regKey, - = sqlite3IndexAffinityStr - = (db, - = pIdx), - = pFK-> - = nCol); - = sqlite3VdbeAddOp4Int(v, - = OP_Found, - = i, - = addrOk, - = regKey, - = 0); - VdbeCoverage(v); - } - } - sqlite3VdbeMultiLoad(v, = regResult + 2, - "si", = pFK->zTo, - i - 1); - sqlite3VdbeAddOp2(v, = OP_ResultRow, - regResult, 4); - sqlite3VdbeResolveLabel(v, = addrOk); - sqlite3DbFree(db, aiCols); - } - sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop = + 1); - VdbeCoverage(v); - sqlite3VdbeJumpHere(v, addrTop); - } - break; - } -#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ - #ifndef NDEBUG case PragTyp_PARSER_TRACE:{ if (zRight) { diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h index 795c98c6d..4f635b080 100644 --- a/src/box/sql/pragma.h +++ b/src/box/sql/pragma.h @@ -10,7 +10,6 @@ #define PragTyp_CASE_SENSITIVE_LIKE 2 #define PragTyp_COLLATION_LIST 3 #define PragTyp_FLAG 5 -#define PragTyp_FOREIGN_KEY_CHECK 8 #define PragTyp_FOREIGN_KEY_LIST 9 #define PragTyp_INDEX_INFO 10 #define PragTyp_INDEX_LIST 11 @@ -79,8 +78,7 @@ static const char *const pragCName[] =3D { /* 34 */ "on_update", /* 35 */ "on_delete", /* 36 */ "match", - /* 37 */ "table", - /* Used by: foreign_key_check */ + /* 37 */ "table", /* 38 */ "rowid", /* 39 */ "parent", /* 40 */ "fkid", @@ -135,13 +133,6 @@ static const PragmaName aPragmaName[] =3D { /* iArg: */ SQLITE_DeferFKs}, #endif #endif -#if !defined(SQLITE_OMIT_FOREIGN_KEY) - { /* zName: */ "foreign_key_check", - /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, - /* ePragFlg: */ PragFlg_NeedSchema, - /* ColNames: */ 37, 4, - /* iArg: */ 0}, -#endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) { /* zName: */ "foreign_key_list", /* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST, diff --git a/src/box/sql/prepare.c b/src/box/sql/prepare.c index 14239c489..ca6362dbf 100644 --- a/src/box/sql/prepare.c +++ b/src/box/sql/prepare.c @@ -418,6 +418,7 @@ sql_parser_create(struct Parse *parser, sqlite3 *db) { memset(parser, 0, sizeof(struct Parse)); parser->db =3D db; + rlist_create(&parser->new_fkey); region_create(&parser->region, &cord()->slabc); } =20 @@ -428,6 +429,9 @@ sql_parser_destroy(Parse *parser) sqlite3 *db =3D parser->db; sqlite3DbFree(db, parser->aLabel); sql_expr_list_delete(db, parser->pConstExpr); + struct fkey_parse *fk; + rlist_foreach_entry(fk, &parser->new_fkey, link) + sql_expr_list_delete(db, fk->selfref_cols); if (db !=3D NULL) { assert(db->lookaside.bDisable >=3D parser->disableLookaside); diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h index c9923a777..76c35f398 100644 --- a/src/box/sql/sqliteInt.h +++ b/src/box/sql/sqliteInt.h @@ -1472,7 +1472,6 @@ typedef struct Schema Schema; typedef struct Expr Expr; typedef struct ExprList ExprList; typedef struct ExprSpan ExprSpan; -typedef struct FKey FKey; typedef struct FuncDestructor FuncDestructor; typedef struct FuncDef FuncDef; typedef struct FuncDefHash FuncDefHash; @@ -1525,7 +1524,6 @@ typedef int VList; struct Schema { int schema_cookie; /* Database schema version number for = this file */ Hash tblHash; /* All tables indexed by name */ - Hash fkeyHash; /* All foreign keys by referenced table = name */ }; =20 /* @@ -1912,7 +1910,6 @@ struct Column { struct Table { Column *aCol; /* Information about each column */ Index *pIndex; /* List of SQL indexes on this table. */ - FKey *pFKey; /* Linked list of all foreign keys in = this table */ char *zColAff; /* String defining the affinity of each = column */ /* ... also used as column name list in a VIEW */ Hash idxHash; /* All (named) indices indexed by name = */ @@ -1975,42 +1972,7 @@ sql_space_tuple_log_count(struct Table *tab); * Each REFERENCES clause generates an instance of the following = structure * which is attached to the from-table. The to-table need not exist = when * the from-table is created. The existence of the to-table is not = checked. - * - * The list of all parents for child Table X is held at X.pFKey. - * - * A list of all children for a table named Z (which might not even = exist) - * is held in Schema.fkeyHash with a hash key of Z. - */ -struct FKey { - Table *pFrom; /* Table containing the REFERENCES = clause (aka: Child) */ - FKey *pNextFrom; /* Next FKey with the same in pFrom. = Next parent of p=46rom */ - char *zTo; /* Name of table that the key points to = (aka: Parent) */ - FKey *pNextTo; /* Next with the same zTo. Next child of = zTo. */ - FKey *pPrevTo; /* Previous with the same zTo */ - int nCol; /* Number of columns in this key */ - /* EV: R-30323-21917 */ - u8 isDeferred; /* True if constraint checking is = deferred till COMMIT */ - u8 aAction[2]; /* ON DELETE and ON UPDATE actions, = respectively */ - /** Triggers for aAction[] actions. */ - struct sql_trigger *apTrigger[2]; - struct sColMap { /* Mapping of columns in p=46rom to = columns in zTo */ - int iFrom; /* Index of column in p=46rom */ - char *zCol; /* Name of column in zTo. If NULL use = PRIMARY KEY */ - } aCol[1]; /* One entry for each of nCol columns */ -}; - -/* - * RESTRICT, SETNULL, and CASCADE actions apply only to foreign keys. - * RESTRICT is the same as ABORT for IMMEDIATE foreign keys and the - * same as ROLLBACK for DEFERRED keys. SETNULL means that the foreign - * key is set to NULL. CASCADE means that a DELETE or UPDATE of the - * referenced table row is propagated into the row that holds the - * foreign key. */ -#define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback = for DEFERRED */ -#define OE_SetNull 7 /* Set the foreign key value to NULL */ -#define OE_SetDflt 8 /* Set the foreign key value to its = default */ -#define OE_Cascade 9 /* Cascade the changes */ =20 /* * This object holds a record which has been parsed out into individual @@ -2844,6 +2806,33 @@ enum ast_type { ast_type_MAX }; =20 +/** + * Structure representing foreign keys constraints appeared + * within CREATE TABLE statement. Used only during parsing. + */ +struct fkey_parse { + /** + * Foreign keys constraint declared in + * statement. They must be coded after space creation. + */ + struct fkey_def *fkey; + /** + * If inside CREATE TABLE statement we want to declare + * self-referenced FK constraint, we must delay their + * resolution until the end of parsing of all columns. + * E.g.: CREATE TABLE t1(id REFERENCES t1(b), b); + */ + struct ExprList *selfref_cols; + /** + * Still, self-referenced columns might be NULL, if + * we declare FK constraints referencing PK: + * CREATE TABLE t1(id REFERENCES t1) - it is a valid case. + */ + bool is_self_referenced; + /** Organize these structs into linked list. */ + struct rlist link; +}; + /* * An SQL parser context. A copy of this structure is passed through * the parser and down into all the parser action routine in order to @@ -2936,7 +2925,15 @@ struct Parse { TriggerPrg *pTriggerPrg; /* Linked list of coded triggers = */ With *pWith; /* Current WITH clause, or NULL */ With *pWithToFree; /* Free this WITH object at the end of = the parse */ - + /** + * Number of FK constraints declared within + * CREATE TABLE statement. + */ + uint32_t fkey_count; + /** + * Foreign key constraint appeared in CREATE TABLE stmt. + */ + struct rlist new_fkey; bool initiateTTrans; /* Initiate Tarantool transaction */ /** If set - do not emit byte code at all, just parse. */ bool parse_only; @@ -4240,8 +4237,57 @@ sql_trigger_colmask(Parse *parser, struct = sql_trigger *trigger, #define sqlite3IsToplevel(p) ((p)->pToplevel=3D=3D0) =20 int sqlite3JoinType(Parse *, Token *, Token *, Token *); -void sqlite3CreateForeignKey(Parse *, ExprList *, Token *, ExprList *, = int); -void sqlite3DeferForeignKey(Parse *, int); + +/** + * Change defer mode of last FK constraint processed during + * statement. + * + * @param parse_context Current parsing context. + * @param is_deferred Change defer mode to this value. + */ +void +fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred); + +/** + * Function called from parser to handle + * + * OR to handle + * + * @param parse_context Parsing context. + * @param child Name of table to be altered. NULL on CREATE TABLE + * statement processing. + * @param constraint Name of the constraint to be created. May be + * NULL on CREATE TABLE statement processing. + * Then, auto-generated name is used. + * @param child_cols Columns of child table involved in FK. + * May be NULL on CREATE TABLE statement processing. + * If so, the last column added is used. + * @param parent Name of referenced table. + * @param parent_cols List of referenced columns. If NULL, columns + * which make up PK of referenced table are used. + * @param is_deferred Is FK constraint initially deferred. + * @param actions ON DELETE, UPDATE and INSERT resolution + * algorithms (e.g. CASCADE, RESTRICT etc). + */ +void +sql_create_foreign_key(struct Parse *parse_context, struct SrcList = *child, + struct Token *constraint, struct ExprList = *child_cols, + struct Token *parent, struct ExprList = *parent_cols, + bool is_deferred, int actions); + +/** + * Function called from parser to handle + * SQL statement. + * + * @param parse_context Parsing context. + * @param table Table to be altered. + * @param constraint Name of constraint to be dropped. + */ +void +sql_drop_foreign_key(struct Parse *parse_context, struct SrcList = *table, + struct Token *constraint); + void sqlite3Detach(Parse *, Expr *); void sqlite3FixInit(DbFixer *, Parse *, const char *, const Token *); int sqlite3FixSrcList(DbFixer *, SrcList *); @@ -4517,8 +4563,6 @@ sqlite3ColumnDefault(Vdbe *v, struct space_def = *def, int i, int ireg); void sqlite3AlterFinishAddColumn(Parse *, Token *); void sqlite3AlterBeginAddColumn(Parse *, SrcList *); char* rename_table(sqlite3 *, const char *, const char *, bool *); -char* rename_parent_table(sqlite3 *, const char *, const char *, const = char *, - uint32_t *, uint32_t *); char* rename_trigger(sqlite3 *, char const *, char const *, bool *); /** * Find a collation by name. Set error in @a parser if not found. @@ -4661,32 +4705,67 @@ void sqlite3WithPush(Parse *, With *, u8); #define sqlite3WithDelete(x,y) #endif =20 -/* Declarations for functions in fkey.c. All of these are replaced by - * no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign - * key functionality is available. If OMIT_TRIGGER is defined but - * OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In - * this case foreign keys are parsed, but no other functionality is - * provided (enforcement of FK constraints requires the triggers = sub-system). - */ -#if !defined(SQLITE_OMIT_FOREIGN_KEY) -void sqlite3FkCheck(Parse *, Table *, int, int, int *); -void sqlite3FkActions(Parse *, Table *, ExprList *, int, int *); -int sqlite3FkRequired(Table *, int *); -u32 sqlite3FkOldmask(Parse *, Table *); -FKey *sqlite3FkReferences(Table *); -#else -#define sqlite3FkActions(a,b,c,d,e) -#define sqlite3FkCheck(a,b,c,d,e,f) -#define sqlite3FkOldmask(a,b) 0 -#define sqlite3FkRequired(b,c) 0 -#endif -#ifndef SQLITE_OMIT_FOREIGN_KEY -void sqlite3FkDelete(sqlite3 *, Table *); -int sqlite3FkLocateIndex(Parse *, Table *, FKey *, Index **, int **); -#else -#define sqlite3FkDelete(a,b) -#define sqlite3FkLocateIndex(a,b,c,d,e) -#endif +/* + * This function is called when inserting, deleting or updating a + * row of table tab to generate VDBE code to perform foreign key + * constraint processing for the operation. + * + * For a DELETE operation, parameter reg_old is passed the index + * of the first register in an array of (tab->def->field_count + + * 1) registers containing the PK of the row being deleted, + * followed by each of the column values of the row being deleted, + * from left to right. Parameter reg_new is passed zero in this + * case. + * + * For an INSERT operation, reg_old is passed zero and reg_new is + * passed the first register of an array of + * (tab->def->field_count + 1) registers containing the new row + * data. + * + * For an UPDATE operation, this function is called twice. Once + * before the original record is deleted from the table using the + * calling convention described for DELETE. Then again after the + * original record is deleted but before the new record is + * inserted using the INSERT convention. + * + * @param parser SQL parser. + * @param tab Table from which the row is deleted. + * @param reg_old Register with deleted row. + * @param reg_new Register with inserted row. + * @param changed_cols Array of updated columns. Can be NULL. + */ +void +fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, + int reg_new, const int *changed_cols); + +/** + * Emit VDBE code to do CASCADE, SET NULL or SET DEFAULT actions + * when deleting or updating a row. + * @param parser SQL parser. + * @param tab Table being updated or deleted from. + * @param reg_old Register of the old record. + * param changes Array of numbers of changed columns. + */ +void +fkey_emit_actions(struct Parse *parser, struct Table *tab, int reg_old, + int *changes); + +/** + * This function is called before generating code to update or + * delete a row contained in given space. If the operation is + * a DELETE, then parameter changes is passed a NULL value. + * For an UPDATE, changes points to an array of size N, where N + * is the number of columns in table. If the i'th column is not + * modified by the UPDATE, then the corresponding entry in the + * changes[] array is set to -1. If the column is modified, + * the value is 0 or greater. + * + * @param space_id Id of space to be modified. + * @param changes Array of modified fields for UPDATE. + * @retval True, if any foreign key processing will be required. + */ +bool +fkey_is_required(uint32_t space_id, const int *changes); =20 /* * Available fault injectors. Should be numbered beginning with 0. diff --git a/src/box/sql/status.c b/src/box/sql/status.c index 5bb1f8f14..209ed8571 100644 --- a/src/box/sql/status.c +++ b/src/box/sql/status.c @@ -244,13 +244,8 @@ sqlite3_db_status(sqlite3 * db, /* The database = connection whose status is desir Schema *pSchema =3D db->pSchema; if (ALWAYS(pSchema !=3D 0)) { HashElem *p; - - nByte +=3D - ROUND8(sizeof(HashElem)) * - (pSchema->tblHash.count + - pSchema->fkeyHash.count); - nByte +=3D = sqlite3_msize(pSchema->tblHash.ht); - nByte +=3D = sqlite3_msize(pSchema->fkeyHash.ht); + nByte +=3D ROUND8(sizeof(HashElem)) * + pSchema->tblHash.count; =20 for (p =3D = sqliteHashFirst(&pSchema->tblHash); p; p =3D sqliteHashNext(p)) { diff --git a/src/box/sql/tarantoolInt.h b/src/box/sql/tarantoolInt.h index bc61e8426..2850b511e 100644 --- a/src/box/sql/tarantoolInt.h +++ b/src/box/sql/tarantoolInt.h @@ -91,11 +91,6 @@ sql_rename_table(uint32_t space_id, const char = *new_name, char **sql_stmt); int tarantoolSqlite3RenameTrigger(const char *zTriggerName, const char *zOldName, const char = *zNewName); =20 -/* Alter create table statement of child foreign key table by - * replacing parent table name in create table statement.*/ -int tarantoolSqlite3RenameParentTable(int iTab, const char = *zOldParentName, - const char *zNewParentName); - /* Interface for ephemeral tables. */ int tarantoolSqlite3EphemeralCreate(BtCursor * pCur, uint32_t = filed_count, struct key_def *def); @@ -154,6 +149,23 @@ int tarantoolSqlite3MakeTableFormat(Table * pTable, = void *buf); */ int tarantoolSqlite3MakeTableOpts(Table * pTable, const char *zSql, = char *buf); =20 +/** + * Encode links of given foreign key constraint into MsgPack. + * Note: this function is adapted to encode only members of + * struct field_link since it uses offset of (sizeof(field_link)) + * to fetch next member. + * + * @param links Array of unsigned number representing parent or + * child field numbers. + * @param link_count Number of members in @links. + * @param buf Buffer to hold encoded links. Can be NULL. + * In this case function would simply calculate + * memory required for such buffer. + * @retval Length of encoded array. + */ +int +fkey_encode_links(uint32_t *links, uint32_t link_count, char *buf); + /* * Format "parts" array for _index entry. * Returns result size. diff --git a/src/box/sql/update.c b/src/box/sql/update.c index d51a05cad..07396e1a9 100644 --- a/src/box/sql/update.c +++ b/src/box/sql/update.c @@ -229,7 +229,7 @@ sqlite3Update(Parse * pParse, /* The = parser context */ */ pTabList->a[0].colUsed =3D 0; =20 - hasFK =3D sqlite3FkRequired(pTab, aXRef); + hasFK =3D fkey_is_required(pTab->def->id, aXRef); =20 /* There is one entry in the aRegIdx[] array for each index on = the table * being updated. Fill in aRegIdx[] with a register number that = will hold @@ -433,7 +433,9 @@ sqlite3Update(Parse * pParse, /* The = parser context */ * information is needed */ if (chngPk !=3D 0 || hasFK !=3D 0 || trigger !=3D NULL) { - u32 oldmask =3D (hasFK ? sqlite3FkOldmask(pParse, pTab) = : 0); + struct space *space =3D space_by_id(pTab->def->id); + assert(space !=3D NULL); + u32 oldmask =3D hasFK ? space->fkey_mask : 0; oldmask |=3D sql_trigger_colmask(pParse, trigger, = pChanges, 0, TRIGGER_BEFORE | = TRIGGER_AFTER, pTab, on_error); @@ -545,9 +547,8 @@ sqlite3Update(Parse * pParse, /* The = parser context */ aXRef); =20 /* Do FK constraint checks. */ - if (hasFK) { - sqlite3FkCheck(pParse, pTab, regOldPk, 0, = aXRef); - } + if (hasFK) + fkey_emit_check(pParse, pTab, regOldPk, 0, = aXRef); =20 /* Delete the index entries associated with the current = record. */ if (bReplace || chngPk) { @@ -583,9 +584,8 @@ sqlite3Update(Parse * pParse, /* The = parser context */ sqlite3VdbeJumpHere(v, addr1); } =20 - if (hasFK) { - sqlite3FkCheck(pParse, pTab, 0, regNewPk, = aXRef); - } + if (hasFK) + fkey_emit_check(pParse, pTab, 0, regNewPk, = aXRef); =20 /* Insert the new index entries and the new record. */ vdbe_emit_insertion_completion(v, iIdxCur, aRegIdx[0], @@ -595,9 +595,8 @@ sqlite3Update(Parse * pParse, /* The = parser context */ * handle rows (possibly in other tables) that refer via = a foreign key * to the row just updated. */ - if (hasFK) { - sqlite3FkActions(pParse, pTab, pChanges, = regOldPk, aXRef); - } + if (hasFK) + fkey_emit_actions(pParse, pTab, regOldPk, = aXRef); } =20 /* Increment the row counter diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 8e6e14f5d..46ad9af58 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/fkey.h" #include "box/txn.h" #include "box/session.h" #include "sqliteInt.h" @@ -4642,7 +4643,6 @@ case OP_RenameTable: { const char *zOldTableName; const char *zNewTableName; Table *pTab; - FKey *pFKey; struct init_data init; char *zSqlStmt; =20 @@ -4661,20 +4661,6 @@ case OP_RenameTable: { rc =3D sql_rename_table(space_id, zNewTableName, &zSqlStmt); if (rc) goto abort_due_to_error; =20 - /* If it is parent table, all children statements should be = updated. */ - for (pFKey =3D sqlite3FkReferences(pTab); pFKey; pFKey =3D = pFKey->pNextTo) { - assert(pFKey->zTo !=3D NULL); - assert(pFKey->p=46rom !=3D NULL); - rc =3D = tarantoolSqlite3RenameParentTable(pFKey->pFrom->def->id, - pFKey->zTo, - zNewTableName); - if (rc) goto abort_due_to_error; - pFKey->zTo =3D sqlite3DbStrNDup(db, zNewTableName, - = sqlite3Strlen30(zNewTableName)); - sqlite3HashInsert(&db->pSchema->fkeyHash, zOldTableName, = 0); - sqlite3HashInsert(&db->pSchema->fkeyHash, zNewTableName, = pFKey); - } - sqlite3UnlinkAndDeleteTable(db, pTab->def->name); =20 init.db =3D db; diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua index a1f6a24b4..db87c7003 100755 --- a/test/sql-tap/alter.test.lua +++ b/test/sql-tap/alter.test.lua @@ -313,8 +313,8 @@ test:do_execsql_test( DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; - CREATE TABLE t2(id INT PRIMARY KEY); - CREATE TABLE t3(id INT PRIMARY KEY); + CREATE TABLE t2(id PRIMARY KEY); + CREATE TABLE t3(id PRIMARY KEY); CREATE TABLE t1(a PRIMARY KEY, b, c, FOREIGN KEY(b) REFERENCES = t2(id), FOREIGN KEY(c) REFERENCES t3(id)); INSERT INTO t2 VALUES(1); INSERT INTO t3 VALUES(2); diff --git a/test/sql-tap/alter2.test.lua b/test/sql-tap/alter2.test.lua new file mode 100755 index 000000000..94a61ebf4 --- /dev/null +++ b/test/sql-tap/alter2.test.lua @@ -0,0 +1,229 @@ +#!/usr/bin/env tarantool +test =3D require("sqltester") +test:plan(18) + +-- This suite is aimed to test ALTER TABLE ADD CONSTRAINT statement. +-- + +test:do_catchsql_test( + "alter2-1.1", + [[ + CREATE TABLE t1(id PRIMARY KEY, a, b); + ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES = t1(id); + ALTER TABLE t1 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES = t1; + INSERT INTO t1 VALUES(1, 1, 2); + ]], { + -- + 0 + -- + }) + +test:do_catchsql_test( + "alter2-1.2", + [[ + INSERT INTO t1 VALUES(2, 3, 2); + ]], { + -- + 1, "FOREIGN KEY constraint failed" + -- + }) + +test:do_catchsql_test( + "alter2-1.3", + [[ + DELETE FROM t1; + ]], { + -- + 0 + -- + }) + +test:do_catchsql_test( + "alter2-1.4", + [[ + ALTER TABLE t1 DROP CONSTRAINT fk1; + INSERT INTO t1 VALUES(2, 3, 2); + ]], { + -- + 1, "FOREIGN KEY constraint failed" + -- + }) + +test:do_execsql_test( + "alter2-1.5", + [[ + ALTER TABLE t1 DROP CONSTRAINT fk2; + INSERT INTO t1 VALUES(2, 3, 2); + SELECT * FROM t1; + ]], { + -- + 2, 3, 2 + -- + }) + +test:do_catchsql_test( + "alter2-1.6", + [[ + DELETE FROM t1; + CREATE UNIQUE INDEX i1 ON t1(b, a); + ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a, b) REFERENCES = t1(b, a); + INSERT INTO t1 VALUES(3, 1, 1); + INSERT INTO t1 VALUES(4, 2, 1); + ]], { + -- + 1, "FOREIGN KEY constraint failed" + -- + }) + +test:do_execsql_test( + "alter2-1.7", + [[ + ALTER TABLE t1 DROP CONSTRAINT fk1; + INSERT INTO t1 VALUES(5, 2, 1); + SELECT * FROM t1; + ]], { + -- + 3, 1, 1, 5, 2, 1 + -- + }) + +test:do_test( + "alter2-1.7.1", + function() + test:execsql([[DELETE FROM t1;]]) + t1 =3D box.space.T1 + if t1.engine ~=3D 'vinyl' then + return + end + box.snapshot() + end, { + -- + -- + }) + +test:do_catchsql_test( + "alter2-1.8", + [[ + ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES = t1(id); + ALTER TABLE t1 ADD CONSTRAINT fk2 FOREIGN KEY (a, b) REFERENCES = t1(b, a); + DROP TABLE t1; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "alter2-1.9", + [[ + SELECT * FROM "_fk_constraint"; + ]], { + -- + -- + }) + +test:do_catchsql_test( + "alter2-2.1", + [[ + CREATE TABLE child (id PRIMARY KEY, a, b); + CREATE TABLE parent (id PRIMARY KEY, c UNIQUE, d); + ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES = parent(c); + ALTER TABLE parent ADD CONSTRAINT fk FOREIGN KEY (c) REFERENCES = parent; + INSERT INTO parent VALUES(1, 2, 3); + ]], { + -- + 1, "FOREIGN KEY constraint failed" + -- + }) + +test:do_catchsql_test( + "alter2-2.2", + [[ + INSERT INTO parent VALUES(1, 1, 2); + INSERT INTO child VALUES(2, 1, 1); + ]], { + -- + 1, "FOREIGN KEY constraint failed" + -- + }) + +test:do_catchsql_test( + "alter2-2.3", + [[ + ALTER TABLE child DROP CONSTRAINT fk; + INSERT INTO parent VALUES(3, 4, 2); + ]], { + -- + 1, "FOREIGN KEY constraint failed" + -- + }) + +test:do_execsql_test( + "alter2-2.4", + [[ + ALTER TABLE parent DROP CONSTRAINT fk; + INSERT INTO parent VALUES(3, 4, 2); + SELECT * FROM parent; + ]], { + -- + 1, 1, 2, 3, 4, 2 + -- + }) + +test:do_execsql_test( + "alter2-3.1", + [[ + DROP TABLE child; + DROP TABLE parent; + CREATE TABLE child (id PRIMARY KEY, a, b); + CREATE TABLE parent (id PRIMARY KEY, c, d); + ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES = parent ON DELETE CASCADE MATCH FULL; + INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8); + INSERT INTO child VALUES(1, 1, 1), (3, 2, 2); + DELETE FROM parent WHERE id =3D 1; + SELECT * FROM CHILD; + ]], { + -- + 3, 2, 2 + -- + }) + +test:do_execsql_test( + "alter2-3.2", + [[ + DROP TABLE child; + DROP TABLE parent; + CREATE TABLE child (id PRIMARY KEY, a, b); + CREATE TABLE parent (id PRIMARY KEY, c, d); + ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES = parent ON UPDATE CASCADE MATCH PARTIAL; + INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8); + INSERT INTO child VALUES(1, 1, 1), (3, 2, 2); + UPDATE parent SET id =3D 5 WHERE id =3D 1; + SELECT * FROM CHILD; + ]], { + -- + 3, 2, 2, 5, 1, 1 + -- + }) + +test:do_catchsql_test( + "alter2-4.1", + [[ + ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES = child; + ]], { + -- + 1, "near \"REFERENCES\": syntax error" + -- + }) + +test:do_catchsql_test( + "alter2-4.2", + [[ + ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES = child; + ]], { + -- + 1, "near \"(\": syntax error" + -- + }) + +test:finish_test() diff --git a/test/sql-tap/fkey1.test.lua b/test/sql-tap/fkey1.test.lua index 494af4b4a..3c29b097d 100755 --- a/test/sql-tap/fkey1.test.lua +++ b/test/sql-tap/fkey1.test.lua @@ -1,13 +1,13 @@ #!/usr/bin/env tarantool test =3D require("sqltester") -test:plan(19) +test:plan(18) =20 -- This file implements regression tests for foreign keys. =20 test:do_execsql_test( "fkey1-1.1", [[ - CREATE TABLE t2(x PRIMARY KEY, y TEXT); + CREATE TABLE t2(x PRIMARY KEY, y TEXT, UNIQUE (x, y)); ]], { -- -- @@ -17,10 +17,10 @@ test:do_execsql_test( "fkey1-1.2", [[ CREATE TABLE t1( - a INTEGER PRIMARY KEY, + a PRIMARY KEY, b INTEGER REFERENCES t1 ON DELETE CASCADE - REFERENCES t2, + REFERENCES t2 (x), c TEXT, FOREIGN KEY (b, c) REFERENCES t2(x, y) ON UPDATE CASCADE); ]], { @@ -32,7 +32,7 @@ test:do_execsql_test( "fkey1-1.3", [[ CREATE TABLE t3( - a INTEGER PRIMARY KEY REFERENCES t2, + a PRIMARY KEY REFERENCES t2, b INTEGER REFERENCES t1, FOREIGN KEY (a, b) REFERENCES t2(x, y)); ]], { @@ -64,13 +64,13 @@ test:do_execsql_test( test:do_execsql_test( "fkey1-3.1", [[ - CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c); + CREATE TABLE t5(a PRIMARY KEY, b, c UNIQUE, UNIQUE(a, b)); CREATE TABLE t6(d REFERENCES t5, e PRIMARY KEY REFERENCES = t5(c)); PRAGMA foreign_key_list(t6); ]], { -- - 0, 0, 'T5', 'E', 'C', 'NO ACTION', 'NO ACTION', 'NONE', - 1, 0, 'T5', 'D', '', 'NO ACTION', 'NO ACTION', 'NONE' + 0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE', + 1, 0, 'T5', 'E', 'C', 'no_action', 'no_action', 'NONE' -- }) =20 @@ -81,8 +81,8 @@ test:do_execsql_test( PRAGMA foreign_key_list(t7); ]], { -- - 0, 0, 'T5', 'D', 'A', 'NO ACTION', 'NO ACTION', 'NONE', - 0, 1, 'T5', 'E', 'B', 'NO ACTION', 'NO ACTION', 'NONE' + 0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE', + 0, 1, 'T5', 'E', 'B', 'no_action', 'no_action', 'NONE' -- }) =20 @@ -91,12 +91,12 @@ test:do_execsql_test( [[ CREATE TABLE t8( d PRIMARY KEY, e, f, - FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON = UPDATE SET NULL); + FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON = UPDATE SET NULL); PRAGMA foreign_key_list(t8); ]], { -- - 0, 0, 'T5', 'D', '', 'SET NULL', 'CASCADE', 'NONE', - 0, 1, 'T5', 'E', '', 'SET NULL', 'CASCADE', 'NONE' + 0, 0, 'T5', 'D', 'A', 'cascade', 'set_null', 'NONE', + 0, 1, 'T5', 'E', 'B', 'cascade', 'set_null', 'NONE' -- }) =20 @@ -105,12 +105,12 @@ test:do_execsql_test( [[ CREATE TABLE t9( d PRIMARY KEY, e, f, - FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON = UPDATE SET DEFAULT); + FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON = UPDATE SET DEFAULT); PRAGMA foreign_key_list(t9); ]], { -- - 0, 0, 'T5', 'D', '', 'SET DEFAULT', 'CASCADE', 'NONE', - 0, 1, 'T5', 'E', '', 'SET DEFAULT', 'CASCADE', 'NONE' + 0, 0, 'T5', 'D', 'A', 'cascade', 'set_default', 'NONE', + 0, 1, 'T5', 'E', 'B', 'cascade', 'set_default', 'NONE' -- }) =20 @@ -144,7 +144,7 @@ test:do_execsql_test( "fkey1-5.1", [[ CREATE TABLE t11( - x INTEGER PRIMARY KEY, + x PRIMARY KEY, parent REFERENCES t11 ON DELETE CASCADE); INSERT INTO t11 VALUES(1, NULL), (2, 1), (3, 2); ]], { @@ -176,7 +176,7 @@ test:do_execsql_test( "fkey1-5.4", [[ CREATE TABLE Foo ( - Id INTEGER PRIMARY KEY, + Id PRIMARY KEY, ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE, C1); INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, = 'A'); @@ -208,7 +208,7 @@ test:do_execsql_test( -- }) =20 -test:do_execsql_test( +test:do_catchsql_test( "fkey1-6.1", [[ CREATE TABLE p1(id PRIMARY KEY, x, y); @@ -217,23 +217,16 @@ test:do_execsql_test( CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x)); ]], { -- + 1, "Failed to create foreign key constraint = 'FK_CONSTRAINT_1_C1': referenced fields don't compose unique index" -- }) =20 -test:do_catchsql_test( - "fkey1-6.2", - [[ - INSERT INTO c1 VALUES(1); - ]], { - -- - 1, "foreign key mismatch - \"C1\" referencing \"P1\"" - -- - }) - test:do_execsql_test( "fkey1-6.3", [[ CREATE UNIQUE INDEX p1x2 ON p1(x); + DROP TABLE IF EXISTS c1; + CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x)); INSERT INTO c1 VALUES(1); ]], { -- diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua index 523340f6b..611e7cba0 100755 --- a/test/sql-tap/fkey2.test.lua +++ b/test/sql-tap/fkey2.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test =3D require("sqltester") -test:plan(121) +test:plan(116) =20 -- This file implements regression tests for foreign keys. =20 @@ -14,7 +14,7 @@ test:do_execsql_test( CREATE TABLE t4(c PRIMARY KEY REFERENCES t3, d); =20 CREATE TABLE t7(a, b INTEGER PRIMARY KEY); - CREATE TABLE t8(c PRIMARY KEY REFERENCES t7, d); + CREATE TABLE t8(c INTEGER PRIMARY KEY REFERENCES t7, d); ]], { -- -- @@ -317,13 +317,13 @@ test:do_execsql_test( "fkey2-2.1", [[ CREATE TABLE i(i INTEGER PRIMARY KEY); - CREATE TABLE j(j PRIMARY KEY REFERENCES i); + CREATE TABLE j(j INT PRIMARY KEY REFERENCES i); INSERT INTO i VALUES(35); - INSERT INTO j VALUES('35.0'); + INSERT INTO j VALUES(35); SELECT j, typeof(j) FROM j; ]], { -- - "35.0", "text" + 35, "integer" -- }) =20 @@ -524,7 +524,7 @@ test:do_execsql_test( [[ DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; - CREATE TABLE t1(a PRIMARY KEY, b); + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b); ]], { -- @@ -600,10 +600,10 @@ test:do_execsql_test( [[ DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t1; - CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2( c INTEGER PRIMARY KEY, - d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT); + d DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT); DELETE FROM t1; ]], { -- @@ -714,24 +714,20 @@ test:do_catchsql_test( [[ CREATE TABLE p(a PRIMARY KEY, b); CREATE TABLE c(x PRIMARY KEY REFERENCES p(c)); - INSERT INTO c DEFAULT VALUES; ]], { -- - 1, "foreign key mismatch - \"C\" referencing \"P\"" + 1, "Failed to create foreign key constraint = 'fk_constraint_1_C': foreign key refers to nonexistent field C" -- }) =20 test:do_catchsql_test( "fkey2-7.2", [[ - DROP TABLE IF EXISTS c; - DROP TABLE IF EXISTS p; - CREATE VIEW v AS SELECT x AS y FROM c; + CREATE VIEW v AS SELECT b AS y FROM p; CREATE TABLE c(x PRIMARY KEY REFERENCES v(y)); - INSERT INTO c DEFAULT VALUES; ]], { -- - 1, "no such table: C" + 1, "referenced table can't be view" -- }) =20 @@ -740,13 +736,13 @@ test:do_catchsql_test( [[ DROP VIEW v; DROP TABLE IF EXISTS c; - CREATE TABLE p(a COLLATE binary, b PRIMARY KEY); - CREATE UNIQUE INDEX idx ON p(a COLLATE "unicode_ci"); + DROP TABLE IF EXISTS p; + CREATE TABLE p(a COLLATE "unicode_ci", b PRIMARY KEY); + CREATE UNIQUE INDEX idx ON p(a); CREATE TABLE c(x PRIMARY KEY REFERENCES p(a)); - INSERT INTO c DEFAULT VALUES; ]], { -- - 1, "no such view: V" + 1, "Failed to create foreign key constraint = 'FK_CONSTRAINT_1_C': field collation mismatch" -- }) =20 @@ -757,10 +753,9 @@ test:do_catchsql_test( DROP TABLE IF EXISTS p; CREATE TABLE p(a, b, PRIMARY KEY(a, b)); CREATE TABLE c(x PRIMARY KEY REFERENCES p); - INSERT INTO c DEFAULT VALUES; ]], { -- - 1, "foreign key mismatch - \"C\" referencing \"P\"" + 1, "Failed to create foreign key constraint = 'fk_constraint_1_C': number of columns in foreign key does not match the = number of columns in the primary index of referenced table" -- }) =20 @@ -771,7 +766,7 @@ test:do_execsql_test( "fkey2-8.1", [[ CREATE TABLE t1(a INTEGER PRIMARY KEY, b); - CREATE TABLE t2(c PRIMARY KEY, d, FOREIGN KEY(c) REFERENCES = t1(a) ON UPDATE CASCADE); + CREATE TABLE t2(c INTEGER PRIMARY KEY, d, FOREIGN KEY(c) = REFERENCES t1(a) ON UPDATE CASCADE); =20 INSERT INTO t1 VALUES(10, 100); INSERT INTO t2 VALUES(10, 100); @@ -794,8 +789,7 @@ test:do_execsql_test( DROP TABLE IF EXISTS t1; CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2( - x PRIMARY KEY REFERENCES t1 - ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED); + x PRIMARY KEY REFERENCES t1 ON UPDATE RESTRICT); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); @@ -847,7 +841,7 @@ test:do_execsql_test( BEGIN INSERT INTO t1 VALUES(old.x); END; - CREATE TABLE t2(y PRIMARY KEY REFERENCES t1); + CREATE TABLE t2(y COLLATE "unicode_ci" PRIMARY KEY REFERENCES = t1); INSERT INTO t1 VALUES('A'); INSERT INTO t1 VALUES('B'); INSERT INTO t2 VALUES('A'); @@ -875,7 +869,7 @@ test:do_execsql_test( "fkey2-9.7", [[ DROP TABLE t2; - CREATE TABLE t2(y PRIMARY KEY REFERENCES t1 ON DELETE = RESTRICT); + CREATE TABLE t2(y COLLATE "unicode_ci" PRIMARY KEY REFERENCES = t1 ON DELETE RESTRICT); INSERT INTO t2 VALUES('A'); INSERT INTO t2 VALUES('B'); ]], { @@ -1053,7 +1047,7 @@ test:do_catchsql_test( CREATE TABLE t1(a PRIMARY KEY, b REFERENCES nosuchtable); ]], { -- - 1, "foreign key constraint references nonexistent table: = NOSUCHTABLE" + 1, "Space 'NOSUCHTABLE' does not exist" -- }) =20 @@ -1083,7 +1077,7 @@ test:do_catchsql_test( test:do_execsql_test( "fkey2-10.9", [[ - DELETE FROM t2; + DROP TABLE t2; DROP TABLE t1; ]], { -- @@ -1091,47 +1085,6 @@ test:do_execsql_test( }) =20 test:do_catchsql_test( - "fkey2-10.10", - [[ - INSERT INTO t2 VALUES('x'); - ]], { - -- - 1, "no such table: T1" - -- - }) - -test:do_execsql_test( - "fkey2-10.11", - [[ - CREATE TABLE t1(x PRIMARY KEY); - INSERT INTO t1 VALUES('x'); - INSERT INTO t2 VALUES('x'); - ]], { - -- - -- - }) - -test:do_catchsql_test( - "fkey2-10.12", - [[ - DROP TABLE t1; - ]], { - -- - 1, "FOREIGN KEY constraint failed" - -- - }) - -test:do_execsql_test( - "fkey2-10.13", - [[ - DROP TABLE t2; - DROP TABLE t1; - ]], { - -- - -- - }) - -test:do_execsql_test( "fkey2-10.14", [[ DROP TABLE IF EXISTS cc; @@ -1140,23 +1093,13 @@ test:do_execsql_test( CREATE TABLE cc(a PRIMARY KEY, b, FOREIGN KEY(a, b) REFERENCES = pp(x, z)); ]], { -- + 1, "Failed to create foreign key constraint = 'fk_constraint_1_CC': foreign key refers to nonexistent field Z" -- }) =20 -test:do_catchsql_test( - "fkey2-10.15", - [[ - INSERT INTO cc VALUES(1, 2); - ]], { - -- - 1, "foreign key mismatch - \"CC\" referencing \"PP\"" - -- - }) - test:do_execsql_test( "fkey2-10.16", [[ - DROP TABLE cc; CREATE TABLE cc( a PRIMARY KEY, b, FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY = DEFERRED); @@ -1181,7 +1124,7 @@ test:do_execsql_test( -- }) =20 -test:do_execsql_test( +test:do_catchsql_test( "fkey2-10.18", [[ CREATE TABLE b1(a PRIMARY KEY, b); @@ -1193,7 +1136,7 @@ test:do_execsql_test( -- }) =20 -test:do_execsql_test( +test:do_catchsql_test( "fkey2-10.19", [[ CREATE TABLE b3(a PRIMARY KEY, b REFERENCES b2 DEFERRABLE = INITIALLY DEFERRED); @@ -1204,15 +1147,15 @@ test:do_execsql_test( -- }) =20 -test:do_execsql_test( +test:do_catchsql_test( "fkey2-10.20", [[ DROP VIEW IF EXISTS v; - CREATE VIEW v AS SELECT * FROM t1; + CREATE VIEW v AS SELECT * FROM b1; CREATE TABLE t1(x PRIMARY KEY REFERENCES v); - DROP VIEW v; ]], { -- + 1, "referenced table can't be view" -- }) =20 @@ -1224,7 +1167,7 @@ test:do_execsql_test( test:do_execsql_test( "fkey2-11.1", [[ - CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)); + CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)); INSERT INTO self VALUES(13, 13); UPDATE self SET a =3D 14, b =3D 14; ]], { @@ -1294,7 +1237,7 @@ test:do_execsql_test( "fkey2-11.8", [[ DROP TABLE IF EXISTS self; - CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES = self(a)); + CREATE TABLE self(a UNIQUE, b PRIMARY KEY REFERENCES self(a)); INSERT INTO self VALUES(13, 13); UPDATE self SET a =3D 14, b =3D 14; ]], { @@ -1366,7 +1309,7 @@ test:do_catchsql_test( test:do_execsql_test( "fkey2-12.1", [[ - CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b); + CREATE TABLE tdd08(a PRIMARY KEY, b); CREATE UNIQUE INDEX idd08 ON tdd08(a,b); INSERT INTO tdd08 VALUES(200,300); =20 @@ -1430,7 +1373,7 @@ test:do_catchsql_test( test:do_execsql_test( "fkey2-13.1", [[ - CREATE TABLE tce71(a INTEGER PRIMARY KEY, b); + CREATE TABLE tce71(a PRIMARY KEY, b); CREATE UNIQUE INDEX ice71 ON tce71(a,b); INSERT INTO tce71 VALUES(100,200); CREATE TABLE tce72(w PRIMARY KEY, x, y, FOREIGN KEY(x,y) = REFERENCES tce71(a,b)); @@ -1466,9 +1409,9 @@ test:do_catchsql_test( test:do_execsql_test( "fkey2-14.1", [[ - CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)); + CREATE TABLE tce73(a PRIMARY KEY, b, UNIQUE(a,b)); INSERT INTO tce73 VALUES(100,200); - CREATE TABLE tce74(w INTEGER PRIMARY KEY, x, y, FOREIGN = KEY(x,y) REFERENCES tce73(a,b)); + CREATE TABLE tce74(w PRIMARY KEY, x, y, FOREIGN KEY(x,y) = REFERENCES tce73(a,b)); INSERT INTO tce74 VALUES(300,100,200); UPDATE tce73 set b =3D 200 where a =3D 100; SELECT * FROM tce73, tce74; diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua index d7055b096..84997dd35 100755 --- a/test/sql-tap/fkey3.test.lua +++ b/test/sql-tap/fkey3.test.lua @@ -158,9 +158,8 @@ test:do_catchsql_test( test:do_execsql_test( "fkey3-3.6", [[ - CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d, + CREATE TABLE t6(a PRIMARY KEY, b, c, d, UNIQUE (a, b), FOREIGN KEY(c, d) REFERENCES t6(a, b)); - CREATE UNIQUE INDEX t6i ON t6(b, a); INSERT INTO t6 VALUES(1, 'a', 1, 'a'); INSERT INTO t6 VALUES(2, 'a', 2, 'a'); INSERT INTO t6 VALUES(3, 'a', 1, 'a'); @@ -206,9 +205,8 @@ test:do_execsql_test( test:do_execsql_test( "fkey3-3.10", [[ - CREATE TABLE t7(a, b, c, d INTEGER PRIMARY KEY, + CREATE TABLE t7(a, b, c, d PRIMARY KEY, UNIQUE(a, b), FOREIGN KEY(c, d) REFERENCES t7(a, b)); - CREATE UNIQUE INDEX t7i ON t7(a, b); INSERT INTO t7 VALUES('x', 1, 'x', 1); INSERT INTO t7 VALUES('x', 2, 'x', 2); ]], { @@ -239,9 +237,10 @@ test:do_catchsql_test( test:do_execsql_test( "fkey3-6.1", [[ - CREATE TABLE t8(a PRIMARY KEY, b, c, d, e, FOREIGN KEY(c, d) = REFERENCES t8(a, b)); + CREATE TABLE t8(a PRIMARY KEY, b, c, d, e); CREATE UNIQUE INDEX t8i1 ON t8(a, b); CREATE UNIQUE INDEX t8i2 ON t8(c); + ALTER TABLE t8 ADD CONSTRAINT fk1 FOREIGN KEY (c, d) REFERENCES = t8(a, b); INSERT INTO t8 VALUES(1, 1, 1, 1, 1); ]], { -- @@ -272,12 +271,12 @@ test:do_catchsql_test( "fkey3-6.4", [[ CREATE TABLE TestTable ( - id INTEGER PRIMARY KEY, + id PRIMARY KEY, name TEXT, source_id INTEGER NOT NULL, - parent_id INTEGER, - FOREIGN KEY(source_id, parent_id) REFERENCES = TestTable(source_id, id)); + parent_id INTEGER); CREATE UNIQUE INDEX testindex on TestTable(source_id, id); + ALTER TABLE TestTable ADD CONSTRAINT fk1 FOREIGN KEY = (source_id, parent_id) REFERENCES TestTable(source_id, id); INSERT INTO TestTable VALUES (1, 'parent', 1, null); INSERT INTO TestTable VALUES (2, 'child', 1, 1); UPDATE TestTable SET parent_id=3D1000 WHERE id=3D2; diff --git a/test/sql-tap/orderby1.test.lua = b/test/sql-tap/orderby1.test.lua index e0ea3698d..1cc104bfc 100755 --- a/test/sql-tap/orderby1.test.lua +++ b/test/sql-tap/orderby1.test.lua @@ -29,7 +29,7 @@ test:do_test( function() return test:execsql [[ CREATE TABLE album( - aid INTEGER PRIMARY KEY, + aid PRIMARY KEY, title TEXT UNIQUE NOT NULL ); CREATE TABLE track( @@ -417,7 +417,7 @@ test:do_test( DROP TABLE track; DROP TABLE album; CREATE TABLE album( - aid INTEGER PRIMARY KEY, + aid PRIMARY KEY, title TEXT UNIQUE NOT NULL ); CREATE TABLE track( @@ -664,7 +664,7 @@ test:do_test( 4.0, function() return test:execsql [[ - CREATE TABLE t41(a INTEGER PRIMARY KEY, b INT NOT NULL); + CREATE TABLE t41(a PRIMARY KEY, b INT NOT NULL); CREATE INDEX t41ba ON t41(b,a); CREATE TABLE t42(id INTEGER PRIMARY KEY, x INT NOT NULL = REFERENCES t41(a), y INT NOT NULL); CREATE UNIQUE INDEX t42xy ON t42(x,y); diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua index 3f8182fc4..52b85baa3 100755 --- a/test/sql-tap/table.test.lua +++ b/test/sql-tap/table.test.lua @@ -731,7 +731,7 @@ test:do_catchsql_test( [[ DROP TABLE t6; CREATE TABLE t4(a INT PRIMARY KEY); - CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL primary key); + CREATE TABLE t6(a INTEGER REFERENCES t4(a) MATCH PARTIAL = primary key); ]], { -- 0 @@ -742,7 +742,7 @@ test:do_catchsql_test( "table-10.3", [[ DROP TABLE t6; - CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL = NOT NULL primary key); + CREATE TABLE t6(a INTEGER REFERENCES t4 MATCH FULL ON DELETE = SET NULL NOT NULL primary key); ]], { -- 0 @@ -753,7 +753,7 @@ test:do_catchsql_test( "table-10.4", [[ DROP TABLE t6; - CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET = DEFAULT DEFAULT 1 primary key); + CREATE TABLE t6(a INT REFERENCES t4 MATCH FULL ON UPDATE SET = DEFAULT DEFAULT 1 primary key); ]], { -- 0 @@ -791,14 +791,16 @@ test:do_catchsql_test( ); ]], { -- - 0 + 1, "Failed to create foreign key constraint = 'fk_constraint_1_T6': foreign key refers to nonexistent field B" -- }) =20 test:do_catchsql_test( "table-10.8", [[ - DROP TABLE t6; + DROP TABLE IF EXISTS t6; + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x UNIQUE, y, PRIMARY KEY (x, y)); CREATE TABLE t6(a primary key,b,c, FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY = DEFERRED @@ -818,7 +820,7 @@ test:do_catchsql_test( ); ]], { -- - 1, "number of columns in foreign key does not match the number = of columns in the referenced table" + 1, "Failed to create foreign key constraint = 'fk_constraint_1_T6': number of columns in foreign key does not match = the number of columns in the primary index of referenced table" -- }) =20 @@ -833,7 +835,7 @@ test:do_test( ]] end, { -- - 1, "number of columns in foreign key does not match the number = of columns in the referenced table" + 1, "Failed to create foreign key constraint = 'fk_constraint_1_T6': number of columns in foreign key does not match = the number of columns in the primary index of referenced table" -- }) =20 @@ -846,7 +848,7 @@ test:do_test( ]] end, { -- - 1, "foreign key on C should reference only one column of table = t4" + 1, "Failed to create foreign key constraint = 'fk_constraint_1_T6': number of columns in foreign key does not match = the number of columns in the primary index of referenced table" -- }) =20 @@ -861,7 +863,7 @@ test:do_test( ]] end, { -- - 1, [[unknown column "X" in foreign key definition]] + 1, [[Failed to create foreign key constraint = 'fk_constraint_1_T6': unknown column X in foreign key definition]] -- }) =20 @@ -876,7 +878,7 @@ test:do_test( ]] end, { -- - 1, [[unknown column "X" in foreign key definition]] + 1, [[Failed to create foreign key constraint = 'fk_constraint_1_T6': unknown column X in foreign key definition]] -- }) =20 diff --git a/test/sql-tap/tkt-b1d3a2e531.test.lua = b/test/sql-tap/tkt-b1d3a2e531.test.lua index e140cf82a..85b0f46d7 100755 --- a/test/sql-tap/tkt-b1d3a2e531.test.lua +++ b/test/sql-tap/tkt-b1d3a2e531.test.lua @@ -65,7 +65,7 @@ test:do_execsql_test( test:do_execsql_test( 2.1, [[ - CREATE TABLE pp(x PRIMARY KEY); + CREATE TABLE pp(x INTEGER PRIMARY KEY); CREATE TABLE cc( y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY = DEFERRED ); @@ -83,7 +83,7 @@ test:do_execsql_test( test:do_execsql_test( 2.3, [[ - CREATE TABLE pp(x PRIMARY KEY); + CREATE TABLE pp(x INTEGER PRIMARY KEY); CREATE TABLE cc( y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY = DEFERRED ); diff --git a/test/sql-tap/triggerC.test.lua = b/test/sql-tap/triggerC.test.lua index e58072e2f..d1fc82842 100755 --- a/test/sql-tap/triggerC.test.lua +++ b/test/sql-tap/triggerC.test.lua @@ -1150,7 +1150,7 @@ test:do_execsql_test( PRAGMA foreign_keys=3D'false'; PRAGMA recursive_triggers =3D 1; CREATE TABLE node( - id int not null primary key, + id not null primary key, pid int not null default 0 references node, key varchar not null, path varchar default '', diff --git a/test/sql-tap/whereG.test.lua b/test/sql-tap/whereG.test.lua index 13cef16c8..ded983975 100755 --- a/test/sql-tap/whereG.test.lua +++ b/test/sql-tap/whereG.test.lua @@ -23,11 +23,11 @@ test:do_execsql_test( "whereG-1.0", [[ CREATE TABLE composer( - cid INTEGER PRIMARY KEY, + cid PRIMARY KEY, cname TEXT ); CREATE TABLE album( - aid INTEGER PRIMARY KEY, + aid PRIMARY KEY, aname TEXT ); CREATE TABLE track( diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua index 6db8d130c..c6a895875 100755 --- a/test/sql-tap/with1.test.lua +++ b/test/sql-tap/with1.test.lua @@ -397,7 +397,7 @@ test:do_catchsql_test("5.6.7", [[ -- test:do_execsql_test(6.1, [[ CREATE TABLE f( - id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT + id PRIMARY KEY, parentid REFERENCES f, name TEXT ); =20 INSERT INTO f VALUES(0, NULL, ''); diff --git a/test/sql/foreign-keys.result b/test/sql/foreign-keys.result index c2ec429c3..f33b49a03 100644 --- a/test/sql/foreign-keys.result +++ b/test/sql/foreign-keys.result @@ -332,5 +332,29 @@ box.space.CHILD:drop() box.space.PARENT:drop() --- ... --- Clean-up SQL DD hash. -test_run:cmd('restart server default with cleanup=3D1') +-- Check that parser correctly handles MATCH, ON DELETE and +-- ON UPDATE clauses. +-- +box.sql.execute('CREATE TABLE tp (id INT PRIMARY KEY, a INT UNIQUE)') +--- +... +box.sql.execute('CREATE TABLE tc (id INT PRIMARY KEY, a INT REFERENCES = tp(a) ON DELETE SET NULL MATCH FULL)') +--- +... +box.sql.execute('ALTER TABLE tc ADD CONSTRAINT fk1 FOREIGN KEY (id) = REFERENCES tp(id) MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET NULL') +--- +... +box.space._fk_constraint:select{} +--- +- - ['FK1', 518, 517, false, 'partial', 'cascade', 'set_null', [0], = [0]] + - ['FK_CONSTRAINT_1_TC', 518, 517, false, 'full', 'set_null', = 'no_action', [1], + [1]] +... +box.sql.execute('DROP TABLE tc') +--- +... +box.sql.execute('DROP TABLE tp') +--- +... +--- Clean-up SQL DD hash. +-test_run:cmd('restart server default with cleanup=3D1') diff --git a/test/sql/foreign-keys.test.lua = b/test/sql/foreign-keys.test.lua index a7a242bc2..8d27aa00e 100644 --- a/test/sql/foreign-keys.test.lua +++ b/test/sql/foreign-keys.test.lua @@ -150,5 +150,15 @@ box.space._fk_constraint:select({'fk_1', = child_id})[1]['is_deferred'] box.space.CHILD:drop() box.space.PARENT:drop() =20 --- Clean-up SQL DD hash. -test_run:cmd('restart server default with cleanup=3D1') +-- Check that parser correctly handles MATCH, ON DELETE and +-- ON UPDATE clauses. +-- +box.sql.execute('CREATE TABLE tp (id INT PRIMARY KEY, a INT UNIQUE)') +box.sql.execute('CREATE TABLE tc (id INT PRIMARY KEY, a INT REFERENCES = tp(a) ON DELETE SET NULL MATCH FULL)') +box.sql.execute('ALTER TABLE tc ADD CONSTRAINT fk1 FOREIGN KEY (id) = REFERENCES tp(id) MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET NULL') +box.space._fk_constraint:select{} +box.sql.execute('DROP TABLE tc') +box.sql.execute('DROP TABLE tp') + +--- Clean-up SQL DD hash. +-test_run:cmd('restart server default with cleanup=3D1') --=20 2.15.1