[tarantool-patches] Re: [PATCH 3/5] sql: introduce ADD CONSTRAINT statement
n.pettik
korablev at tarantool.org
Wed Aug 1 23:54:31 MSK 2018
>> 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 ‘index_size()’, some tests turn out
>> to be flaky. (I don’t think that we should disable these tests for vinyl, but didn’t
>> come up with satisfactory solution.)
>
> 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 = require("sqltester")
-test:plan(17)
+test:plan(18)
-- This suite is aimed to test ALTER TABLE ADD CONSTRAINT statement.
--
@@ -87,10 +87,23 @@ test:do_execsql_test(
-- </alter2-1.7>
})
+test:do_test(
+ "alter2-1.7.1",
+ function()
+ test:execsql([[DELETE FROM t1;]])
+ t1 = box.space.T1
+ if t1.engine ~= 'vinyl' then
+ return
+ end
+ box.snapshot()
+ end, {
+ -- <alter2-1.7.1>
+ -- </alter2-1.7.1>
+ })
+
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"}
- },
>
>>>> -/*
>>>> - * 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)
>>>
>>> 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?
>
> 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;
}
+static void
+fkey_set_mask(const struct fkey *fk, uint64_t *parent_mask,
+ uint64_t *child_mask)
+{
+ for (uint32_t i = 0; i < fk->def->field_count; ++i) {
+ *parent_mask |= FKEY_MASK(fk->def->links[i].parent_field);
+ *child_mask |= 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 = 0;
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &space->child_fkey, child_link) {
+ struct fkey_def *def = fk->def;
+ for (uint32_t i = 0; i < def->field_count; ++i)
+ space->fkey_mask |=
+ FKEY_MASK(def->links[i].child_field);
+ }
+ rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
+ struct fkey_def *def = fk->def;
+ for (uint32_t i = 0; i < def->field_count; ++i)
+ space->fkey_mask |=
+ FKEY_MASK(def->links[i].parent_field);
+ }
+}
+
+static void
+fkey_update_mask(const struct fkey *fkey)
+{
+ struct space *child = space_by_id(fkey->def->child_id);
+ space_reset_fkey_mask(child);
+ struct space *parent = 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 = 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 = (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;
};
+/**
+ * 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,
}
}
-#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
-
-uint32_t
-fkey_old_mask(uint32_t space_id)
-{
- uint32_t mask = 0;
- struct session *user_session = current_session();
- if ((user_session->sql_flags & SQLITE_ForeignKeys) != 0) {
- struct space *space = space_by_id(space_id);
- struct fkey *fk;
- rlist_foreach_entry(fk, &space->child_fkey, child_link) {
- struct fkey_def *def = fk->def;
- for (uint32_t i = 0; i < def->field_count; ++i)
- mask |= COLUMN_MASK(def->links[i].child_field);
- }
- rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
- struct fkey_def *def = fk->def;
- for (uint32_t i = 0; i < def->field_count; ++i)
- mask |= 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 |= fkey_old_mask(table->def->id);
+ struct space *space = space_by_id(table->def->id);
+ assert(space != NULL);
+ mask |= 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);
-/**
- * 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 != 0 || hasFK != 0 || trigger != NULL) {
- u32 oldmask = hasFK ? fkey_old_mask(pTab->def->id) : 0;
+ struct space *space = space_by_id(pTab->def->id);
+ assert(space != NULL);
+ u32 oldmask = hasFK ? space->fkey_mask : 0;
>
>> 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=$((i + 1))
>> done
>> max="$cnt"
>> +echo "//*************** $max $nOp $mxTk"
>
> 1. This echo seems to be debug print.
>
>> @@ -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=0
>> -while [ "$i" -le "$max" ]; do
>> +while [ "$i" -le "$mxTk" ]; do
>> if [ "$((i % 8))" -eq 0 ]; then
>> printf '/* %3d */' "$i"
>> fi
>> - eval "bv=\$ARRAY_bv_$i"
>> + eval "is_exists=\${ARRAY_bv_$i:-}"
>
> 2. 'is_exists'? I have refactored this changes
> slightly on the branch.
Sorry, I didn’t review this script, just simply copied it and include
some Alex’s 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
>
> 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?
>
> 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.
>
> And we have 3 options as I understand:
>
> * somehow merge these things into VDBE;
>
> * when we will have PREPARE API, rebuild prepared statements of
> the user session on change of any of these flags;
>
> * 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…
Anyway, lets ask for advise. Then, I am going to open an issue.
Also, I’ve 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 == NULL &&
- !parse_context->isMultiWrite) {
+ struct session *session = current_session();
+ if (!fk_def->is_deferred &&
+ (session->sql_flags & SQLITE_DeferFKs) == 0 &&
+ parse_context->pToplevel == NULL && !parse_context->isMultiWrite) {
@@ -605,8 +607,9 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
if (changed_cols != NULL &&
!fkey_parent_is_modified(fk_def, changed_cols))
continue;
- if (!fk_def->is_deferred && parser->pToplevel == NULL &&
- !parser->isMultiWrite) {
+ if (!fk_def->is_deferred &&
+ (user_session->sql_flags & SQLITE_DeferFKs) == 0 &&
+ parser->pToplevel == NULL && !parser->isMultiWrite) {
>
>> - && 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 ==
>> 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 != NULL);
>> + for (uint32_t j = 0; j < def->field_count; ++j) {
>> + if (strcmp(field_name, def->fields[j].name) == 0) {
>> + *link = j;
>> + return 0;
>> + }
>> + }
>> + sqlite3ErrorMsg(parse_context, "unknown column %s in foreign key "
>> + "definition", field_name);
>
> 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)
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 != NULL);
for (uint32_t j = 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 = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
@@ -1813,7 +1815,8 @@ sqlite3EndTable(Parse * pParse, /* Parse context */
for (uint32_t i = 0; i < fk->field_count; ++i) {
if (resolve_link(pParse, p->def,
cols->a[i].zName,
- &fk->links[i].parent_field) != 0)
+ &fk->links[i].parent_field,
+ fk->name) != 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) != 0)
+ &fk->links[i].child_field,
+ constraint_name) != 0)
>
>> + 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 = fk_parse->fkey;
>> + if (fk_parse->selfref_cols != NULL) {
>> + struct ExprList *cols = fk_parse->selfref_cols;
>> + for (uint32_t i = 0; i < fk->field_count; ++i) {
>> + if (resolve_link(pParse, p->def,
>> + cols->a[i].zName,
>> + &fk->links[i].parent_field) != 0)
>> + return;
>> + }
>> + fk->parent_id = iSpaceId;
>> + } else if (fk_parse->is_self_referenced) {
>> + struct Index *pk = sqlite3PrimaryKeyIndex(p);
>> + if (pk->def->key_def->part_count !=
>> + 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");
>
> 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..
>
> 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”);
@@ -2400,8 +2408,8 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
if (constraint_name == NULL)
goto exit_create_fk;
const char *error_msg = "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 != NULL);
>> + uint32_t column_len = strlen(column_name);
>> + if (tuple_fieldno_by_name(space->def->dict, column_name, column_len,
>> + field_name_hash(column_name, column_len),
>> + colno) != 0) {
>> + sqlite3ErrorMsg(parse_context,
>> + "table \"%s\" doesn't feature column %s",
>> + space->def->name, column_name);
>
> 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 != NULL);
uint32_t column_len = strlen(column_name);
if (tuple_fieldno_by_name(space->def->dict, column_name, column_len,
field_name_hash(column_name, column_len),
colno) != 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 = 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) != 0) {
+ &fk->links[i].parent_field,
+ constraint_name) != 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) != 0) {
+ &fk->links[i].child_field,
+ constraint_name) != 0) {
>
>> + 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)
>
> 8. We have problems with actions. Lets take a look at the parser:
>
> %type refarg {struct {int value; int mask;}}
> refarg(A) ::= MATCH matcharg(X). { A.value = X; A.mask = 0xff0000; }
> refarg(A) ::= ON INSERT refact. { A.value = 0; A.mask = 0x000000; }
> refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; }
> refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; }
>
> It builds actions mask. Then lets look at the actions decoding:
>
> fk->match = (enum fkey_match) ((actions >> 16) & 0xff);
> fk->on_update = (enum fkey_action) ((actions >> 8) & 0xff);
> fk->on_delete = (enum fkey_action) (actions & 0xff);
>
> As you can see, it is expected, that the mask has the layout
> {on_delete, on_update, match}, each field is byte.
>
> But the parser stores them as {on_delete/match, on_update} now.
>
> So I've found this test that ignores my MATCH:
>
> 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}]]
>
> 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) ::= AUTOINCR. {X = 1;}
refargs(A) ::= . { A = FKEY_NO_ACTION; }
refargs(A) ::= refargs(A) refarg(Y). { A = (A & ~Y.mask) | Y.value; }
%type refarg {struct {int value; int mask;}}
-refarg(A) ::= MATCH matcharg(X). { A.value = X; A.mask = 0xff0000; }
+refarg(A) ::= MATCH matcharg(X). { A.value = X<<16; A.mask = 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()
--- Clean-up SQL DD hash.
-test_run:cmd('restart server default with cleanup=1')
+-- 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=1’)
+++ 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=1')
+-- 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=1')
Updated patch:
=======================================================================
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[] = {
{ "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[] = {
{ "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[] = {
{ "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
currentOp=""
nOp=0
+mxTk=-1
newline="$(printf '\n')"
IFS="$newline"
while read line; do
@@ -106,6 +107,9 @@ while read line; do
eval "ARRAY_used_$val=1"
eval "ARRAY_sameas_$val=$sym"
eval "ARRAY_def_$val=$name"
+ if [ $val -gt $mxTk ] ; then
+ mxTk=$val
+ fi
fi
;;
jump) eval "ARRAY_jump_$name=1" ;;
@@ -219,9 +223,11 @@ while [ "$i" -lt "$nOp" ]; do
fi
i=$((i + 1))
done
-max="$cnt"
+if [ $mxTk -lt $nOp ] ; then
+ mxTk=$nOp
+fi
i=0
-while [ "$i" -lt "$nOp" ]; do
+while [ "$i" -le "$mxTk" ]; do
eval "used=\${ARRAY_used_$i:-}"
if [ -z "$used" ]; then
eval "ARRAY_def_$i=OP_NotUsed_$i"
@@ -251,9 +257,19 @@ done
# Generate the bitvectors:
ARRAY_bv_0=0
i=0
-while [ "$i" -le "$max" ]; do
+while [ "$i" -le "$mxTk" ]; do
+ eval "is_existing=\${ARRAY_def_$i:-}"
+ if [ ! -n "$is_existing" ] ; then
+ i=$((i + 1))
+ continue
+ fi
eval "name=\$ARRAY_def_$i"
x=0
+ eval "is_existing=\${ARRAY_jump_$name:-}"
+ if [ ! -n "$is_existing" ] ; then
+ i=$((i + 1))
+ continue
+ fi
eval "jump=\$ARRAY_jump_$name"
eval "in1=\$ARRAY_in1_$name"
eval "in2=\$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=0
-while [ "$i" -le "$max" ]; do
+while [ "$i" -le "$mxTk" ]; do
if [ "$((i % 8))" -eq 0 ]; then
printf '/* %3d */' "$i"
fi
- eval "bv=\$ARRAY_bv_$i"
+ eval "is_existing=\${ARRAY_bv_$i:-}"
+ if [ ! -n "$is_existing" ] ; then
+ bv=0
+ else
+ eval "bv=\$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;
}
+static void
+fkey_set_mask(const struct fkey *fk, uint64_t *parent_mask,
+ uint64_t *child_mask)
+{
+ for (uint32_t i = 0; i < fk->def->field_count; ++i) {
+ *parent_mask |= FKEY_MASK(fk->def->links[i].parent_field);
+ *child_mask |= 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 = 0;
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &space->child_fkey, child_link) {
+ struct fkey_def *def = fk->def;
+ for (uint32_t i = 0; i < def->field_count; ++i)
+ space->fkey_mask |=
+ FKEY_MASK(def->links[i].child_field);
+ }
+ rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
+ struct fkey_def *def = fk->def;
+ for (uint32_t i = 0; i < def->field_count; ++i)
+ space->fkey_mask |=
+ FKEY_MASK(def->links[i].parent_field);
+ }
+}
+
+static void
+fkey_update_mask(const struct fkey *fkey)
+{
+ struct space *child = space_by_id(fkey->def->child_id);
+ space_reset_fkey_mask(child);
+ struct space *parent = 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);
}
/** 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);
}
/** 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 = 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);
}
/**
@@ -3712,6 +3759,7 @@ on_drop_or_replace_fkey_commit(struct trigger *trigger, void *event)
{
(void) event;
struct fkey *fk = (struct fkey *)trigger->data;
+ fkey_update_mask(fk);
fkey_delete(fk);
}
@@ -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 =
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;
};
+/**
+ * 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;
};
/** 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"
static sqlite3 *db = NULL;
@@ -839,103 +840,6 @@ rename_fail:
return SQL_TARANTOOL_ERROR;
}
-/*
- * 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 != 0);
- assert(old_parent_name != NULL);
- assert(new_parent_name != NULL);
-
- box_tuple_t *tuple;
- uint32_t key_len = mp_sizeof_uint(space_id) + mp_sizeof_array(1);
-
- char *key_begin = (char*) region_alloc(&fiber()->gc, key_len);
- if (key_begin == NULL) {
- diag_set(OutOfMemory, key_len, "region_alloc", "key_begin");
- return SQL_TARANTOOL_ERROR;
- }
- char *key = mp_encode_array(key_begin, 1);
- key = mp_encode_uint(key, space_id);
- if (box_index_get(BOX_SPACE_ID, 0, key_begin, key, &tuple) != 0)
- return SQL_TARANTOOL_ERROR;
- assert(tuple != NULL);
-
- assert(tuple_field_count(tuple) == 7);
- const char *sql_stmt_map = box_tuple_field(tuple, 5);
-
- if (sql_stmt_map == NULL || mp_typeof(*sql_stmt_map) != MP_MAP)
- goto rename_fail;
- uint32_t map_size = mp_decode_map(&sql_stmt_map);
- if (map_size != 1)
- goto rename_fail;
- const char *sql_str = mp_decode_str(&sql_stmt_map, &key_len);
- if (sqlite3StrNICmp(sql_str, "sql", 3) != 0)
- goto rename_fail;
- uint32_t create_stmt_decoded_len;
- const char *create_stmt_old = mp_decode_str(&sql_stmt_map,
- &create_stmt_decoded_len);
- uint32_t old_name_len = strlen(old_parent_name);
- uint32_t new_name_len = strlen(new_parent_name);
- char *create_stmt_new = (char*) region_alloc(&fiber()->gc,
- create_stmt_decoded_len + 1);
- if (create_stmt_new == 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] = '\0';
- uint32_t numb_of_quotes = 0;
- uint32_t numb_of_occurrences = 0;
- create_stmt_new = 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 = 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 = tuple->bsize + mp_sizeof_str(create_stmt_new_len);
- char *new_tuple = (char*)region_alloc(&fiber()->gc, key_len);
- if (new_tuple == NULL) {
- sqlite3DbFree(db, create_stmt_new);
- diag_set(OutOfMemory, key_len, "region_alloc", "new_tuple");
- return SQL_TARANTOOL_ERROR;
- }
-
- char *new_tuple_end = new_tuple;
- const char *data_begin = tuple_data(tuple);
- const char *data_end = tuple_field(tuple, 5);
- uint32_t data_size = data_end - data_begin;
- memcpy(new_tuple, data_begin, data_size);
- new_tuple_end += data_size;
- new_tuple_end = mp_encode_map(new_tuple_end, 1);
- new_tuple_end = mp_encode_str(new_tuple_end, "sql", 3);
- new_tuple_end = mp_encode_str(new_tuple_end, create_stmt_new,
- create_stmt_new_len);
- sqlite3DbFree(db, create_stmt_new);
- data_begin = tuple_field(tuple, 6);
- data_end = (char*) tuple + tuple_size(tuple);
- data_size = data_end - data_begin;
- memcpy(new_tuple_end, data_begin, data_size);
- new_tuple_end += data_size;
-
- if (box_replace(BOX_SPACE_ID, new_tuple, new_tuple_end, NULL) != 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;
}
+int
+fkey_encode_links(uint32_t *links, uint32_t link_count, char *buf)
+{
+ const struct Enc *enc = get_enc(buf);
+ char *p = enc->encode_array(buf, link_count);
+ for (uint32_t i = 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 = sizeof(struct field_link) * i;
+ p = 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 = pParse->pVdbe; /* The prepared statement under construction */
- struct session *user_session = current_session();
db = 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 ==
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;
}
-/*
- * 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 = 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 = sql_stmt; *csr; csr = csr + n) {
- n = sql_token(csr, &token, &unused);
- if (token == TK_REFERENCES) {
- char *zParent;
- do {
- csr += n;
- n = sql_token(csr, &token, &unused);
- } while (token == TK_SPACE);
- if (token == TK_ILLEGAL)
- break;
- zParent = sqlite3DbStrNDup(db, csr, n);
- if (zParent == 0)
- break;
- is_quoted = *zParent == '"' ? true : false;
- sqlite3NormalizeName(zParent);
- if (0 == strcmp(old_name, zParent)) {
- (*numb_of_occurrences)++;
- if (!is_quoted)
- (*numb_of_unquoted)++;
- char *zOut = sqlite3MPrintf(db, "%s%.*s\"%w\"",
- (output ? output : ""),
- (int)((char*)csr - sql_stmt),
- sql_stmt, new_name);
- sqlite3DbFree(db, output);
- output = zOut;
- sql_stmt = &csr[n];
- }
- sqlite3DbFree(db, zParent);
- }
- }
-
- new_sql_stmt = 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);
}
- /* 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;
}
+/**
+ * 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 != NULL);
+ assert(fk != NULL);
+ struct Vdbe *vdbe = sqlite3GetVdbe(parse_context);
+ assert(vdbe != NULL);
+ /*
+ * Occupy registers for 8 fields: each member in
+ * _constraint space plus one for final msgpack tuple.
+ */
+ int constr_tuple_reg = sqlite3GetTempRange(parse_context, 10);
+ char *name_copy = sqlite3DbStrDup(parse_context->db, fk->name);
+ if (name_copy == 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 <CREATE TABLE ...> statement, we don't have child
+ * id, but we know register where it will be stored.
+ * */
+ if (parse_context->pNewTable != 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 != 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 = fkey_encode_links(&fk->links[0].parent_field,
+ fk->field_count, NULL);
+ size_t encoded_child_sz = 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 = sqlite3DbMallocRaw(parse_context->db,
+ encoded_child_sz +
+ encoded_parent_sz);
+ if (encoded_links == 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 = fkey_encode_links(&fk->links[0].parent_field,
+ fk->field_count,
+ encoded_links);
+ size_t real_child_sz = 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 != NULL);
+ for (uint32_t j = 0; j < def->field_count; ++j) {
+ if (strcmp(field_name, def->fields[j].name) == 0) {
+ *link = 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 = 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 */
/* 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 = fk_parse->fkey;
+ if (fk_parse->selfref_cols != NULL) {
+ struct ExprList *cols = fk_parse->selfref_cols;
+ for (uint32_t i = 0; i < fk->field_count; ++i) {
+ if (resolve_link(pParse, p->def,
+ cols->a[i].zName,
+ &fk->links[i].parent_field,
+ fk->name) != 0)
+ return;
+ }
+ fk->parent_id = iSpaceId;
+ } else if (fk_parse->is_self_referenced) {
+ struct Index *pk = sqlite3PrimaryKeyIndex(p);
+ if (pk->def->key_def->part_count !=
+ 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 = SQL_TARANTOOL_ERROR;
+ pParse->nErr++;
+ return;
+ }
+ for (uint32_t i = 0; i < fk->field_count; ++i) {
+ fk->links[i].parent_field =
+ pk->def->key_def->parts[i].fieldno;
+ }
+ fk->parent_id = iSpaceId;
+ }
+ fk->child_id = iSpaceId;
+ vdbe_emit_fkey_create(pParse, fk);
+ }
}
/* 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);
}
+/**
+ * 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 = sqlite3GetVdbe(parse_context);
+ assert(vdbe != NULL);
+ int key_reg = 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 = sqlite3DbStrDup(v->db,
+ child_fk->def->name);
+ if (fk_name_dup == 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 = sqlite3HashFind(&db->pSchema->tblHash, space_name);
- struct FKey *fk = sqlite3FkReferences(tab);
- if (fk != NULL && (fk->pFrom->def->id != tab->def->id)) {
- diag_set(ClientError, ER_DROP_SPACE, space_name,
- "other objects depend on it");
- parse_context->rc = 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 = 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);
}
-/*
- * 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==0 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 = pParse->db;
-#ifndef SQLITE_OMIT_FOREIGN_KEY
- FKey *pFKey = 0;
- FKey *pNextTo;
- Table *p = pParse->pNewTable;
- int nByte;
- int i;
- int nCol;
- char *z;
-
- assert(pTo != 0);
- char *normalized_name = strndup(pTo->z, pTo->n);
- if (normalized_name == NULL) {
- diag_set(OutOfMemory, pTo->n, "strndup", "normalized name");
- goto fk_end;
- }
- sqlite3NormalizeName(normalized_name);
- uint32_t parent_id = box_space_id_by_name(normalized_name,
- strlen(normalized_name));
- if (parent_id == BOX_ID_NIL &&
- strcmp(normalized_name, p->def->name) != 0) {
- diag_set(ClientError, ER_NO_SUCH_SPACE, normalized_name);
- pParse->rc = SQL_TARANTOOL_ERROR;
- pParse->nErr++;
- goto fk_end;
- }
- struct space *parent_space = space_by_id(parent_id);
- if (parent_space != 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 != NULL);
+ uint32_t column_len = strlen(column_name);
+ if (tuple_fieldno_by_name(space->def->dict, column_name, column_len,
+ field_name_hash(column_name, column_len),
+ colno) != 0) {
+ diag_set(ClientError, ER_CREATE_FK_CONSTRAINT, fk_name,
+ tt_sprintf("foreign key refers to nonexistent field %s",
+ column_name));
+ parse_context->rc = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ return -1;
}
- if (p == 0)
- goto fk_end;
- if (pFromCol == 0) {
- int iCol = p->def->field_count - 1;
- if (NEVER(iCol < 0))
- goto fk_end;
- if (pToCol && pToCol->nExpr != 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 = parse_context->db;
+ /*
+ * When this function is called second time during
+ * <CREATE TABLE ...> 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 = NULL;
+ const char *constraint_name = NULL;
+ bool is_self_referenced = false;
+ /*
+ * Table under construction during CREATE TABLE
+ * processing. NULL for ALTER TABLE statement handling.
+ */
+ struct Table *new_tab = parse_context->pNewTable;
+ /* Whether we are processing ALTER TABLE or CREATE TABLE. */
+ bool is_alter = new_tab == NULL;
+ uint32_t child_cols_count;
+ if (child_cols == NULL) {
+ assert(!is_alter);
+ child_cols_count = 1;
+ } else {
+ child_cols_count = child_cols->nExpr;
+ }
+ assert(!is_alter || (child != NULL && child->nSrc == 1));
+ struct space *child_space = NULL;
+ uint32_t child_id = 0;
+ if (is_alter) {
+ const char *child_name = child->a[0].zName;
+ child_id = box_space_id_by_name(child_name,
+ strlen(child_name));
+ if (child_id == BOX_ID_NIL) {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, child_name);
+ goto tnt_error;
}
- nCol = 1;
- } else if (pToCol && pToCol->nExpr != 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 = space_by_id(child_id);
+ assert(child_space != NULL);
} else {
- nCol = pFromCol->nExpr;
- }
- nByte = sizeof(*pFKey) + (nCol - 1) * sizeof(pFKey->aCol[0]) +
- strlen(normalized_name) + 1;
- if (pToCol) {
- for (i = 0; i < pToCol->nExpr; i++) {
- nByte += sqlite3Strlen30(pToCol->a[i].zName) + 1;
+ struct fkey_parse *fk = region_alloc(&parse_context->region,
+ sizeof(*fk));
+ if (fk == 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 != NULL);
+ parent_name = sqlite3NameFromToken(db, parent);
+ if (parent_name == NULL)
+ goto exit_create_fk;
+ uint32_t parent_id = 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 = !is_alter &&
+ strcmp(parent_name, new_tab->def->name) == 0;
+ struct space *parent_space;
+ if (parent_id == BOX_ID_NIL) {
+ parent_space = NULL;
+ if (is_self_referenced) {
+ struct fkey_parse *fk =
+ rlist_first_entry(&parse_context->new_fkey,
+ struct fkey_parse, link);
+ fk->selfref_cols = parent_cols;
+ fk->is_self_referenced = true;
+ } else {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, parent_name);;
+ goto tnt_error;
}
- }
- pFKey = sqlite3DbMallocZero(db, nByte);
- if (pFKey == 0) {
- goto fk_end;
- }
- pFKey->pFrom = p;
- pFKey->pNextFrom = p->pFKey;
- z = (char *)&pFKey->aCol[nCol];
- pFKey->zTo = z;
- memcpy(z, normalized_name, strlen(normalized_name) + 1);
- z += strlen(normalized_name) + 1;
- pFKey->nCol = nCol;
- if (pFromCol == 0) {
- pFKey->aCol[0].iFrom = p->def->field_count - 1;
} else {
- for (i = 0; i < nCol; i++) {
- int j;
- for (j = 0; j < (int)p->def->field_count; j++) {
- if (strcmp(p->def->fields[j].name,
- pFromCol->a[i].zName) == 0) {
- pFKey->aCol[i].iFrom = j;
- break;
- }
- }
- if (j >= (int)p->def->field_count) {
- sqlite3ErrorMsg(pParse,
- "unknown column \"%s\" in foreign key definition",
- pFromCol->a[i].zName);
- goto fk_end;
- }
+ parent_space = space_by_id(parent_id);
+ assert(parent_space != 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 = 0; i < nCol; i++) {
- int n = sqlite3Strlen30(pToCol->a[i].zName);
- pFKey->aCol[i].zCol = z;
- memcpy(z, pToCol->a[i].zName, n);
- z[n] = 0;
- z += n + 1;
+ if (constraint == NULL && !is_alter) {
+ if (parse_context->constraintName.n == 0) {
+ constraint_name =
+ sqlite3MPrintf(db, "fk_constraint_%d_%s",
+ ++parse_context->fkey_count,
+ new_tab->def->name);
+ } else {
+ struct Token *cnstr_nm = &parse_context->constraintName;
+ constraint_name = sqlite3NameFromToken(db, cnstr_nm);
+ }
+ } else {
+ constraint_name = sqlite3NameFromToken(db, constraint);
+ }
+ if (constraint_name == NULL)
+ goto exit_create_fk;
+ const char *error_msg = "number of columns in foreign key does not "
+ "match the number of columns in the primary "
+ "index of referenced table";
+ if (parent_cols != NULL) {
+ if (parent_cols->nExpr != (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 = space_index(parent_space, 0);
+ assert(parent_pk != NULL);
+ if (parent_pk->def->key_def->part_count != child_cols_count) {
+ diag_set(ClientError, ER_CREATE_FK_CONSTRAINT,
+ constraint_name, error_msg);
+ goto tnt_error;
}
}
- pFKey->isDeferred = 0;
- pFKey->aAction[0] = (u8) (flags & 0xff); /* ON DELETE action */
- pFKey->aAction[1] = (u8) ((flags >> 8) & 0xff); /* ON UPDATE action */
-
- pNextTo = (FKey *) sqlite3HashInsert(&p->pSchema->fkeyHash,
- pFKey->zTo, (void *)pFKey);
- if (pNextTo == pFKey) {
- sqlite3OomFault(db);
- goto fk_end;
+ int name_len = strlen(constraint_name);
+ size_t fk_size = fkey_def_sizeof(child_cols_count, name_len);
+ struct fkey_def *fk = region_alloc(&parse_context->region, fk_size);
+ if (fk == NULL) {
+ diag_set(OutOfMemory, fk_size, "region", "struct fkey");
+ goto tnt_error;
}
- if (pNextTo) {
- assert(pNextTo->pPrevTo == 0);
- pFKey->pNextTo = pNextTo;
- pNextTo->pPrevTo = pFKey;
+ fk->field_count = child_cols_count;
+ fk->child_id = child_id;
+ fk->parent_id = parent_id;
+ fk->is_deferred = is_deferred;
+ fk->match = (enum fkey_match) ((actions >> 16) & 0xff);
+ fk->on_update = (enum fkey_action) ((actions >> 8) & 0xff);
+ fk->on_delete = (enum fkey_action) (actions & 0xff);
+ fk->links = (struct field_link *) ((char *) fk->name + name_len + 1);
+ /* Fill links map. */
+ for (uint32_t i = 0; i < fk->field_count; ++i) {
+ if (!is_self_referenced && parent_cols == NULL) {
+ struct key_def *pk_def =
+ parent_space->index[0]->def->key_def;
+ fk->links[i].parent_field = 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) != 0) {
+ goto exit_create_fk;
+ }
+ if (!is_alter) {
+ if (child_cols == NULL) {
+ assert(i == 0);
+ /*
+ * In this case there must be only
+ * one link (the last column
+ * added), so we can break
+ * immediately.
+ */
+ fk->links[0].child_field =
+ 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) != 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) != 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] = '\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 = pFKey;
- pFKey = 0;
+ if (!is_alter) {
+ struct fkey_parse *parse_fk =
+ rlist_first_entry(&parse_context->new_fkey,
+ struct fkey_parse, link);
+ parse_fk->fkey = fk;
+ } else {
+ vdbe_emit_fkey_create(parse_context, fk);
+ }
- 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 = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ goto exit_create_fk;
}
-/*
- * 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 = pParse->pNewTable) == 0 || (pFKey = pTab->pFKey) == 0)
+ if (parse_context->db->init.busy ||
+ rlist_empty(&parse_context->new_fkey))
return;
- assert(isDeferred == 0 || isDeferred == 1); /* EV: R-30323-21917 */
- pFKey->isDeferred = (u8) isDeferred;
-#endif
+ rlist_first_entry(&parse_context->new_fkey, struct fkey_parse,
+ link)->fkey->is_deferred = is_deferred;
+}
+
+void
+sql_drop_foreign_key(struct Parse *parse_context, struct SrcList *table,
+ struct Token *constraint)
+{
+ assert(table != NULL && table->nSrc == 1);
+ const char *table_name = table->a[0].zName;
+ uint32_t child_id = box_space_id_by_name(table_name,
+ strlen(table_name));
+ if (child_id == BOX_ID_NIL) {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, table_name);
+ parse_context->rc = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ return;
+ }
+ const char *constraint_name = sqlite3NameFromToken(parse_context->db,
+ constraint);
+ if (constraint_name != NULL)
+ vdbe_emit_fkey_drop(parse_context, constraint_name, child_id);
}
/*
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);
db->pSchema = NULL;
}
@@ -303,13 +302,10 @@ sqlite3SchemaClear(sqlite3 * db)
Schema *
sqlite3SchemaCreate(sqlite3 * db)
{
- Schema *p;
- p = (Schema *) sqlite3DbMallocZero(0, sizeof(Schema));
- if (!p) {
+ struct Schema *p = (Schema *) sqlite3DbMallocZero(0, sizeof(Schema));
+ if (p == 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 != NULL);
trigger_list = sql_triggers_exist(table, TK_DELETE, NULL, NULL);
is_complex = trigger_list != NULL ||
- sqlite3FkRequired(table, NULL);
+ fkey_is_required(table->def->id, NULL);
}
assert(space != NULL);
@@ -437,14 +437,16 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
* use for the old.* references in the triggers.
*/
if (table != NULL &&
- (sqlite3FkRequired(table, NULL) || trigger_list != NULL)) {
+ (fkey_is_required(table->def->id, NULL) || trigger_list != NULL)) {
/* Mask of OLD.* columns in use */
/* TODO: Could use temporary registers here. */
uint32_t mask =
sql_trigger_colmask(parse, trigger_list, 0, 0,
TRIGGER_BEFORE | TRIGGER_AFTER,
table, onconf);
- mask |= sqlite3FkOldmask(parse, table);
+ struct space *space = space_by_id(table->def->id);
+ assert(space != NULL);
+ mask |= space->fkey_mask;
first_old_reg = parse->nMem + 1;
parse->nMem += (1 + (int)table->def->field_count);
@@ -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);
}
/* 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.
*/
- sqlite3FkActions(parse, table, 0, first_old_reg, 0);
+ fkey_emit_actions(parse, table, first_old_reg, NULL);
/* 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
/*
* 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)
*/
-/*
- * 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 = 0; /* Value to return via *paiCol */
- int nCol = pFKey->nCol; /* Number of columns in parent key */
- char *zKey = pFKey->aCol[0].zCol; /* Name of left-most parent key column */
-
- /* The caller is responsible for zeroing output parameters. */
- assert(ppIdx && *ppIdx == 0);
- assert(!paiCol || *paiCol == 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 == -1 || incr_count == 1);
+ struct Vdbe *v = sqlite3GetVdbe(parse_context);
+ int cursor = parse_context->nTab - 1;
+ int ok_label = 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 =
- (int *)sqlite3DbMallocRawNN(pParse->db, nCol * sizeof(int));
- if (!aiCol)
- return 1;
- *paiCol = aiCol;
+ if (incr_count < 0) {
+ sqlite3VdbeAddOp2(v, OP_FkIfZero, fk_def->is_deferred,
+ ok_label);
}
-
- struct Index *index = NULL;
- for (index = pParent->pIndex; index != NULL; index = index->pNext) {
- int part_count = index->def->key_def->part_count;
- if (part_count != nCol || !index->def->opts.is_unique ||
- index->pPartIdxWhere != 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 == 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 != NULL) {
- for (int i = 0; i < nCol; i++)
- aiCol[i] = 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 = index->def->key_def->parts;
- for (i = 0; i < nCol; i++, part++) {
- /*
- * Index of column in parent
- * table.
- */
- i16 iCol = (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 =
- sql_column_collation(pParent->def,
- iCol, &id);
- struct coll *coll = part->coll;
- if (def_coll != coll)
- break;
-
- char *zIdxCol = pParent->def->fields[iCol].name;
- for (j = 0; j < nCol; j++) {
- if (strcmp(pFKey->aCol[j].zCol,
- zIdxCol) != 0)
- continue;
- if (aiCol)
- aiCol[i] = pFKey->aCol[j].iFrom;
- break;
- }
- if (j == nCol)
- break;
- }
- if (i == nCol) {
- /* Index is usable. */
- break;
- }
- }
+ struct field_link *link = fk_def->links;
+ for (uint32_t i = 0; i < fk_def->field_count; ++i, ++link) {
+ int reg = link->child_field + reg_data + 1;
+ sqlite3VdbeAddOp2(v, OP_IsNull, reg, ok_label);
}
-
- if (index == NULL) {
- sqlite3ErrorMsg(pParse, "foreign key mismatch - "
- "\"%w\" referencing \"%w\"",
- pFKey->pFrom->def->name, pFKey->zTo);
- }
-
- *ppIdx = 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 = sqlite3GetVdbe(pParse); /* Vdbe to add code to */
- int iCur = pParse->nTab - 1; /* Cursor number to use */
- int iOk = sqlite3VdbeMakeLabel(v); /* jump here if parent key found */
- struct session *user_session = 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 = fk_def->field_count;
+ int temp_regs = sqlite3GetTempRange(parse_context, field_count);
+ int rec_reg = sqlite3GetTempReg(parse_context);
+ vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, parent);
+ link = fk_def->links;
+ for (uint32_t i = 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 = 0; i < pFKey->nCol; i++) {
- int iReg = aiCol[i] + regData + 1;
- sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
- VdbeCoverage(v);
- }
-
- if (isIgnore == 0) {
- if (pIdx == 0) {
- /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
- * column of the parent table (table pTab).
- */
- int regTemp = 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 == pFKey->pFrom && nIncr == 1) {
- sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk,
- regTemp);
- VdbeCoverage(v);
- sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
- }
-
- } else {
- int nCol = pFKey->nCol;
- int regTemp = sqlite3GetTempRange(pParse, nCol);
- int regRec = sqlite3GetTempReg(pParse);
- struct space *space =
- space_by_id(pIdx->pTable->def->id);
- vdbe_emit_open_cursor(pParse, iCur, pIdx->def->iid,
- space);
- for (i = 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 == pFKey->pFrom && nIncr == 1) {
- int iJump =
- sqlite3VdbeCurrentAddr(v) + nCol + 1;
- struct key_part *part =
- pIdx->def->key_def->parts;
- for (i = 0; i < nCol; ++i, ++part) {
- int iChild = aiCol[i] + 1 + regData;
- int iParent = 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 == 1) {
+ int jump = sqlite3VdbeCurrentAddr(v) + field_count + 1;
+ link = fk_def->links;
+ for (uint32_t i = 0; i < field_count; ++i, ++link) {
+ int chcol = link->child_field + 1 + reg_data;
+ int pcol = 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 = space_index(parent, referenced_idx);
+ assert(idx != 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 = current_session();
+ if (!fk_def->is_deferred &&
+ (session->sql_flags & SQLITE_DeferFKs) == 0 &&
+ parse_context->pToplevel == 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 == 1);
- sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
+ assert(incr_count == 1);
+ sqlite3HaltConstraint(parse_context,
+ SQLITE_CONSTRAINT_FOREIGNKEY,
ON_CONFLICT_ACTION_ABORT, 0, P4_STATIC,
P5_ConstraintFK);
} else {
- if (nIncr > 0 && pFKey->isDeferred == 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);
}
/*
@@ -551,520 +345,393 @@ exprTableColumn(sqlite3 * db, struct space_def *def, int cursor, i16 column)
}
/*
- * 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 = pParse->db; /* Database handle */
- Expr *pWhere = 0; /* WHERE clause to scan with */
- NameContext sNameContext; /* Context used to resolve WHERE clause */
- WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */
- int iFkIfZero = 0; /* Address of OP_FkIfZero */
- Vdbe *v = sqlite3GetVdbe(pParse);
-
- assert(pIdx == NULL || pIdx->pTable == pTab);
- assert(pIdx == NULL || (int) pIdx->def->key_def->part_count == pFKey->nCol);
- assert(pIdx != NULL);
-
- if (nIncr < 0) {
- iFkIfZero =
- sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
+ assert(incr_count == -1 || incr_count == 1);
+ struct sqlite3 *db = parser->db;
+ struct Expr *where = NULL;
+ /* Address of OP_FkIfZero. */
+ int fkifzero_label = 0;
+ struct Vdbe *v = sqlite3GetVdbe(parser);
+
+ if (incr_count < 0) {
+ fkifzero_label = sqlite3VdbeAddOp2(v, OP_FkIfZero,
+ fkey->is_deferred, 0);
VdbeCoverage(v);
}
- /* Create an Expr object representing an SQL expression like:
+ struct space *child_space = space_by_id(fkey->child_id);
+ assert(child_space != NULL);
+ /*
+ * Create an Expr object representing an SQL expression
+ * like:
*
- * <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
+ * <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
*
- * 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 = 0; i < pFKey->nCol; i++) {
- Expr *pLeft; /* Value from parent table row */
- Expr *pRight; /* Column ref to child table */
- Expr *pEq; /* Expression (pLeft = pRight) */
- i16 iCol; /* Index of column in child table */
- const char *column_name;
-
- iCol = pIdx != NULL ?
- (int) pIdx->def->key_def->parts[i].fieldno : -1;
- pLeft = exprTableRegister(pParse, pTab, regData, iCol);
- iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
- assert(iCol >= 0);
- column_name = pFKey->pFrom->def->fields[iCol].name;
- pRight = sqlite3Expr(db, TK_ID, column_name);
- pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
- pWhere = sqlite3ExprAnd(db, pWhere, pEq);
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ uint32_t fieldno = fkey->links[i].parent_field;
+ struct Expr *pexpr =
+ exprTableRegister(parser, tab, reg_data, fieldno);
+ fieldno = fkey->links[i].child_field;
+ const char *field_name = child_space->def->fields[fieldno].name;
+ struct Expr *chexpr = sqlite3Expr(db, TK_ID, field_name);
+ struct Expr *eq = sqlite3PExpr(parser, TK_EQ, pexpr, chexpr);
+ where = sqlite3ExprAnd(db, where, eq);
}
- /* 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==a AND $current_b==b AND ... )
* The primary key is (a,b,...)
*/
- if (pTab == pFKey->pFrom && nIncr > 0) {
- Expr *pNe; /* Expression (pLeft != pRight) */
- Expr *pLeft; /* Value from parent table row */
- Expr *pRight; /* Column ref to child table */
-
- Expr *pEq, *pAll = 0;
- Index *pPk = sqlite3PrimaryKeyIndex(pTab);
- assert(pIdx != NULL);
- uint32_t part_count = pPk->def->key_def->part_count;
- for (uint32_t i = 0; i < part_count; i++) {
- uint32_t fieldno = pIdx->def->key_def->parts[i].fieldno;
- pLeft = exprTableRegister(pParse, pTab, regData,
+ if (tab->def->id == fkey->child_id && incr_count > 0) {
+ struct Expr *expr = NULL, *pexpr, *chexpr, *eq;
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ uint32_t fieldno = fkey->links[i].parent_field;
+ pexpr = exprTableRegister(parser, tab, reg_data,
fieldno);
- pRight = exprTableColumn(db, pTab->def,
- pSrc->a[0].iCursor, fieldno);
- pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
- pAll = sqlite3ExprAnd(db, pAll, pEq);
+ chexpr = exprTableColumn(db, tab->def,
+ src->a[0].iCursor, fieldno);
+ eq = sqlite3PExpr(parser, TK_EQ, pexpr, chexpr);
+ expr = sqlite3ExprAnd(db, expr, eq);
}
- pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0);
- pWhere = sqlite3ExprAnd(db, pWhere, pNe);
+ struct Expr *pNe = sqlite3PExpr(parser, TK_NOT, expr, 0);
+ where = sqlite3ExprAnd(db, where, pNe);
}
/* Resolve the references in the WHERE clause. */
- memset(&sNameContext, 0, sizeof(NameContext));
- sNameContext.pSrcList = pSrc;
- sNameContext.pParse = 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 = src;
+ namectx.pParse = 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 = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
- sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
- if (pWInfo) {
- sqlite3WhereEnd(pWInfo);
- }
+ struct WhereInfo *info =
+ sqlite3WhereBegin(parser, src, where, NULL, NULL, 0, 0);
+ sqlite3VdbeAddOp2(v, OP_FkCounter, fkey->is_deferred, incr_count);
+ if (info != NULL)
+ sqlite3WhereEnd(info);
/* 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 != 0)
+ sqlite3VdbeJumpHere(v, fkifzero_label);
}
-/*
- * 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 = 0; i < p->nCol; i++) {
- int iChildKey = p->aCol[i].iFrom;
- if (aChange[iChildKey] >= 0)
- return 1;
+ for (uint32_t i = 0; i < fkey->field_count; ++i) {
+ uint32_t child_key = fkey->links[i].child_field;
+ if (changes[child_key] >= 0)
+ return true;
}
- return 0;
+ return false;
}
-/*
- * 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 = 0; i < p->nCol; i++) {
- char *zKey = p->aCol[i].zCol;
- int iKey;
- for (iKey = 0; iKey < (int)pTab->def->field_count; iKey++) {
- if (aChange[iKey] >= 0) {
- if (zKey) {
- if (strcmp(pTab->def->fields[iKey].name,
- zKey) == 0)
- return 1;
- } else if (table_column_is_in_pk(pTab, iKey)) {
- return 1;
- }
- }
- }
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ uint32_t parent_key = fkey->links[i].parent_field;
+ if (changes[parent_key] >= 0)
+ return true;
}
- return 0;
+ return false;
}
-/*
- * 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 = sqlite3ParseToplevel(pParse);
- if (pTop->pTriggerPrg != NULL) {
- struct sql_trigger *trigger = pTop->pTriggerPrg->trigger;
- if ((trigger == pFKey->apTrigger[0] &&
- pFKey->aAction[0] == OE_SetNull) ||
- (trigger == pFKey->apTrigger[1]
- && pFKey->aAction[1] == OE_SetNull))
- return 1;
+ struct Parse *top_parse = sqlite3ParseToplevel(parse_context);
+ if (top_parse->pTriggerPrg != NULL) {
+ struct sql_trigger *trigger = top_parse->pTriggerPrg->trigger;
+ if ((trigger == fkey->on_delete_trigger &&
+ fkey->def->on_delete == FKEY_ACTION_SET_NULL) ||
+ (trigger == fkey->on_update_trigger &&
+ fkey->def->on_update == FKEY_ACTION_SET_NULL))
+ return true;
}
- return 0;
+ return false;
}
-/*
- * 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 = pParse->db; /* Database handle */
- FKey *pFKey; /* Used to iterate through FKs */
+ struct sqlite3 *db = parser->db;
struct session *user_session = current_session();
- /* Exactly one of regOld and regNew should be non-zero. */
- assert((regOld == 0) != (regNew == 0));
+ /*
+ * Exactly one of reg_old and reg_new should be non-zero.
+ */
+ assert((reg_old == 0) != (reg_new == 0));
- /* 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) == 0)
return;
- /* 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 = pTab->pFKey; pFKey; pFKey = pFKey->pNextFrom) {
- Table *pTo; /* Parent table of foreign key pFKey */
- Index *pIdx = 0; /* Index on key columns in pTo */
- int *aiFree = 0;
- int *aiCol;
- int iCol;
- int bIgnore = 0;
-
- if (aChange
- && sqlite3_stricmp(pTab->def->name, pFKey->zTo) != 0
- && fkChildIsModified(pFKey, aChange) == 0) {
+ struct space *space = space_by_id(tab->def->id);
+ assert(space != NULL);
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &space->child_fkey, child_link) {
+ struct fkey_def *fk_def = fk->def;
+ if (changed_cols != 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 = sqlite3LocateTable(pParse, 0, pFKey->zTo);
- if (pTo == NULL || sqlite3FkLocateIndex(pParse, pTo, pFKey,
- &pIdx, &aiFree) != 0)
- return;
- assert(pFKey->nCol == 1 || (aiFree && pIdx));
-
- if (aiFree) {
- aiCol = aiFree;
- } else {
- iCol = pFKey->aCol[0].iFrom;
- aiCol = &iCol;
- }
-
- pParse->nTab++;
-
- if (regOld != 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 = space_by_id(fk_def->parent_id);
+ assert(parent != NULL);
+ if (reg_old != 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 != 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 != 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 = sqlite3FkReferences(pTab); pFKey; pFKey = pFKey->pNextTo) {
- Index *pIdx = 0; /* Foreign key index for pFKey */
- SrcList *pSrc;
- int *aiCol = 0;
-
- if (aChange
- && fkParentIsModified(pTab, pFKey, aChange) == 0) {
+ rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
+ struct fkey_def *fk_def = fk->def;
+ if (changed_cols != 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 == 0 && regNew != 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) == 0 &&
+ parser->pToplevel == NULL && !parser->isMultiWrite) {
+ assert(reg_old == 0 && reg_new != 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;
}
- if (sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx,
- &aiCol) != 0)
- return;
- assert(aiCol || pFKey->nCol == 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 = sqlite3SrcListAppend(db, 0, 0);
- if (pSrc) {
- struct SrcList_item *pItem = pSrc->a;
- pItem->pTab = pFKey->pFrom;
- pItem->zName = pFKey->pFrom->def->name;
- pItem->pTab->nTabRef++;
- pItem->iCursor = pParse->nTab++;
-
- if (regNew != 0) {
- fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey,
- aiCol, regNew, -1);
- }
- if (regOld != 0) {
- int eAction = pFKey->aAction[aChange != 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 != OE_Cascade
- && eAction != OE_SetNull) {
- sqlite3MayAbort(pParse);
- }
- }
- pItem->zName = 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 = 0;
- struct session *user_session = current_session();
-
- if (user_session->sql_flags & SQLITE_ForeignKeys) {
- FKey *p;
- for (p = pTab->pFKey; p; p = p->pNextFrom) {
- for (int i = 0; i < p->nCol; i++)
- mask |= COLUMN_MASK(p->aCol[i].iFrom);
+ struct SrcList *src = sqlite3SrcListAppend(db, NULL, NULL);
+ if (src == NULL)
+ continue;
+ struct SrcList_item *item = src->a;
+ struct space *child = space_by_id(fk->def->child_id);
+ assert(child != NULL);
+ struct Table *child_tab = sqlite3HashFind(&db->pSchema->tblHash,
+ child->def->name);
+ item->pTab = child_tab;
+ item->zName = sqlite3DbStrDup(db, child->def->name);
+ item->pTab->nTabRef++;
+ item->iCursor = parser->nTab++;
+
+ if (reg_new != 0) {
+ fkey_scan_children(parser, src, tab, fk->def, reg_new,
+ -1);
}
- for (p = sqlite3FkReferences(pTab); p; p = p->pNextTo) {
- Index *pIdx = 0;
- sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
- if (pIdx != NULL) {
- uint32_t part_count =
- pIdx->def->key_def->part_count;
- for (uint32_t i = 0; i < part_count; i++) {
- mask |= COLUMN_MASK(pIdx->def->
- key_def->parts[i].fieldno);
- }
- }
+ if (reg_old != 0) {
+ enum fkey_action action = 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 != FKEY_ACTION_CASCADE &&
+ action != FKEY_ACTION_SET_NULL)
+ sqlite3MayAbort(parser);
}
+ sqlite3SrcListDelete(db, src);
}
- return mask;
}
-/*
- * 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 = 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 = pTab->pFKey; p; p = p->pNextFrom) {
- if (fkChildIsModified(p, aChange))
- return 1;
- }
-
- /* Check if any parent key columns are being modified. */
- for (p = sqlite3FkReferences(pTab); p; p = p->pNextTo) {
- if (fkParentIsModified(pTab, p, aChange))
- return 1;
- }
- }
+ if ((user_session->sql_flags & SQLITE_ForeignKeys) == 0)
+ return false;
+ struct space *space = space_by_id(space_id);
+ if (changes == 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;
}
/**
* 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 = pParse->db; /* Database handle */
- int action; /* One of OE_None, OE_Cascade etc. */
- /* Trigger definition to return. */
- struct sql_trigger *trigger;
- int iAction = (pChanges != 0); /* 1 for UPDATE, 0 for DELETE */
- struct session *user_session = current_session();
-
- action = pFKey->aAction[iAction];
- if (action == OE_Restrict
- && (user_session->sql_flags & SQLITE_DeferFKs)) {
- return 0;
- }
- trigger = pFKey->apTrigger[iAction];
-
- if (action != ON_CONFLICT_ACTION_NONE && trigger == NULL) {
- char const *zFrom; /* Name of child table */
- int nFrom; /* Length in bytes of zFrom */
- Index *pIdx = 0; /* Parent key index for this FK */
- int *aiCol = 0; /* child table cols -> parent key cols */
- TriggerStep *pStep = 0; /* First (only) step of trigger program */
- Expr *pWhere = 0; /* WHERE clause of trigger step */
- ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */
- Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */
- int i; /* Iterator variable */
- Expr *pWhen = 0; /* WHEN clause for the trigger */
-
- if (sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol))
- return 0;
- assert(aiCol || pFKey->nCol == 1);
-
- for (i = 0; i < pFKey->nCol; i++) {
- Token tOld = { "old", 3, false }; /* Literal "old" token */
- Token tNew = { "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 = OLD.tToCol */
-
- iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
- assert(iFromCol >= 0);
- assert(pIdx != NULL);
+ struct sqlite3 *db = pParse->db;
+ struct fkey_def *fk_def = fkey->def;
+ enum fkey_action action = is_update ? fk_def->on_update :
+ fk_def->on_delete;
+ struct sql_trigger *trigger = is_update ? fkey->on_update_trigger :
+ fkey->on_delete_trigger;
+ if (action == FKEY_NO_ACTION || trigger != NULL)
+ return trigger;
+ struct TriggerStep *step = NULL;
+ struct Expr *where = NULL, *when = NULL;
+ struct ExprList *list = NULL;
+ struct Select *select = NULL;
+ struct space *child_space = space_by_id(fk_def->child_id);
+ assert(child_space != NULL);
+ for (uint32_t i = 0; i < fk_def->field_count; ++i) {
+ /* Literal "old" token. */
+ struct Token t_old = { "old", 3, false };
+ /* Literal "new" token. */
+ struct Token t_new = { "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 = child_space->def->fields;
+
+ uint32_t pcol = fk_def->links[i].parent_field;
+ sqlite3TokenInit(&t_to_col, pTab->def->fields[pcol].name);
+
+ uint32_t chcol = fk_def->links[i].child_field;
+ sqlite3TokenInit(&t_from_col, child_fields[chcol].name);
- uint32_t fieldno = 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 = zFromCol". It is important
- * that the "OLD.zToCol" term is on the LHS of the = operator, so
- * that the affinity and collation sequence associated with the
- * parent table are used for the comparison.
- */
- pEq = 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 = sqlite3ExprAnd(db, pWhere, pEq);
+ /*
+ * Create the expression "old.to_col = from_col".
+ * It is important that the "old.to_col" term is
+ * on the LHS of the = operator, so that the
+ * affinity and collation sequence associated with
+ * the parent table are used for the comparison.
+ */
+ struct Expr *to_col =
+ sqlite3PExpr(pParse, TK_DOT,
+ sqlite3ExprAlloc(db, TK_ID, &t_old, 0),
+ sqlite3ExprAlloc(db, TK_ID, &t_to_col, 0));
+ struct Expr *from_col =
+ sqlite3ExprAlloc(db, TK_ID, &t_from_col, 0);
+ struct Expr *eq = sqlite3PExpr(pParse, TK_EQ, to_col, from_col);
+ where = sqlite3ExprAnd(db, where, eq);
- /* For ON UPDATE, construct the next term of the WHEN clause.
- * The final WHEN clause will be like this:
- *
- * WHEN NOT(old.col1 = new.col1 AND ... AND old.colN = new.colN)
- */
- if (pChanges) {
- pEq = 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 = 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 = new.col1 AND ... AND
+ * old.colN = new.colN)
+ */
+ if (is_update) {
+ struct Expr *l, *r;
+ l = sqlite3PExpr(pParse, TK_DOT,
+ sqlite3ExprAlloc(db, TK_ID, &t_old, 0),
+ sqlite3ExprAlloc(db, TK_ID, &t_to_col,
+ 0));
+ r = sqlite3PExpr(pParse, TK_DOT,
+ sqlite3ExprAlloc(db, TK_ID, &t_new, 0),
+ sqlite3ExprAlloc(db, TK_ID, &t_to_col,
+ 0));
+ eq = sqlite3PExpr(pParse, TK_EQ, l, r);
+ when = sqlite3ExprAnd(db, when, eq);
+ }
- if (action != OE_Restrict
- && (action != OE_Cascade || pChanges)) {
- Expr *pNew;
- if (action == OE_Cascade) {
- pNew = sqlite3PExpr(pParse, TK_DOT,
- sqlite3ExprAlloc(db,
- TK_ID,
- &tNew,
- 0),
- sqlite3ExprAlloc(db,
- TK_ID,
- &tToCol,
- 0));
- } else if (action == OE_SetDflt) {
- Expr *pDflt =
- space_column_default_expr(
- pFKey->pFrom->def->id,
- (uint32_t)iFromCol);
- if (pDflt) {
- pNew =
- sqlite3ExprDup(db, pDflt,
- 0);
- } else {
- pNew =
- sqlite3ExprAlloc(db,
- TK_NULL, 0,
- 0);
- }
+ if (action != FKEY_ACTION_RESTRICT &&
+ (action != FKEY_ACTION_CASCADE || is_update)) {
+ struct Expr *new, *d;
+ if (action == FKEY_ACTION_CASCADE) {
+ new = sqlite3PExpr(pParse, TK_DOT,
+ sqlite3ExprAlloc(db, TK_ID,
+ &t_new, 0),
+ sqlite3ExprAlloc(db, TK_ID,
+ &t_to_col,
+ 0));
+ } else if (action == FKEY_ACTION_SET_DEFAULT) {
+ d = child_fields[chcol].default_value_expr;
+ if (d != NULL) {
+ new = sqlite3ExprDup(db, d, 0);
} else {
- pNew =
- sqlite3ExprAlloc(db, TK_NULL, 0, 0);
+ new = sqlite3ExprAlloc(db, TK_NULL,
+ NULL, 0);
}
- pList =
- sql_expr_list_append(pParse->db, pList,
- pNew);
- sqlite3ExprListSetName(pParse, pList, &tFromCol,
- 0);
+ } else {
+ new = sqlite3ExprAlloc(db, TK_NULL, NULL, 0);
}
+ list = sql_expr_list_append(db, list, new);
+ sqlite3ExprListSetName(pParse, list, &t_from_col, 0);
}
- sqlite3DbFree(db, aiCol);
-
- zFrom = pFKey->pFrom->def->name;
- nFrom = sqlite3Strlen30(zFrom);
-
- if (action == OE_Restrict) {
- Token tFrom;
- Expr *pRaise;
+ }
- tFrom.z = zFrom;
- tFrom.n = nFrom;
- pRaise =
- sqlite3Expr(db, TK_RAISE,
- "FOREIGN KEY constraint failed");
- if (pRaise) {
- pRaise->affinity = ON_CONFLICT_ACTION_ABORT;
- }
- pSelect = sqlite3SelectNew(pParse,
- sql_expr_list_append(pParse->db,
- NULL,
- pRaise),
- sqlite3SrcListAppend(db, 0,
- &tFrom),
- pWhere, 0, 0, 0, 0, 0, 0);
- pWhere = 0;
- }
- trigger = (struct sql_trigger *)sqlite3DbMallocZero(db,
- sizeof(*trigger));
- if (trigger != NULL) {
- size_t step_size = sizeof(TriggerStep) + nFrom + 1;
- trigger->step_list = sqlite3DbMallocZero(db, step_size);
- pStep = trigger->step_list;
- pStep->zTarget = (char *)&pStep[1];
- memcpy(pStep->zTarget, zFrom, nFrom);
- pStep->pWhere =
- sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
- pStep->pExprList =
- sql_expr_list_dup(db, pList, EXPRDUP_REDUCE);
- pStep->pSelect =
- sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
- if (pWhen) {
- pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0);
- trigger->pWhen =
- sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);
- }
- }
+ const char *space_name = child_space->def->name;
+ uint32_t name_len = strlen(space_name);
+
+ if (action == FKEY_ACTION_RESTRICT) {
+ struct Token err;
+ err.z = space_name;
+ err.n = name_len;
+ struct Expr *r = sqlite3Expr(db, TK_RAISE, "FOREIGN KEY "\
+ "constraint failed");
+ if (r != NULL)
+ r->affinity = ON_CONFLICT_ACTION_ABORT;
+ select = sqlite3SelectNew(pParse,
+ sql_expr_list_append(db, NULL, r),
+ sqlite3SrcListAppend(db, NULL, &err),
+ where, NULL, NULL, NULL, 0, NULL,
+ NULL);
+ where = NULL;
+ }
- 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 == 1) {
- sql_trigger_delete(db, trigger);
- return 0;
+ trigger = (struct sql_trigger *) sqlite3DbMallocZero(db,
+ sizeof(*trigger));
+ if (trigger != NULL) {
+ size_t step_size = sizeof(TriggerStep) + name_len + 1;
+ trigger->step_list = sqlite3DbMallocZero(db, step_size);
+ step = trigger->step_list;
+ step->zTarget = (char *) &step[1];
+ memcpy((char *) step->zTarget, space_name, name_len);
+
+ step->pWhere = sqlite3ExprDup(db, where, EXPRDUP_REDUCE);
+ step->pExprList = sql_expr_list_dup(db, list, EXPRDUP_REDUCE);
+ step->pSelect = sqlite3SelectDup(db, select, EXPRDUP_REDUCE);
+ if (when != NULL) {
+ when = sqlite3PExpr(pParse, TK_NOT, when, 0);
+ trigger->pWhen =
+ sqlite3ExprDup(db, when, EXPRDUP_REDUCE);
}
- assert(pStep != 0);
+ }
- switch (action) {
- case OE_Restrict:
- pStep->op = 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 != NULL);
+
+ switch (action) {
+ case FKEY_ACTION_RESTRICT:
+ step->op = TK_SELECT;
+ break;
+ case FKEY_ACTION_CASCADE:
+ if (! is_update) {
+ step->op = TK_DELETE;
break;
- case OE_Cascade:
- if (!pChanges) {
- pStep->op = TK_DELETE;
- break;
- }
- FALLTHROUGH;
- default:
- pStep->op = TK_UPDATE;
}
- pStep->trigger = trigger;
- pFKey->apTrigger[iAction] = trigger;
- trigger->op = pChanges ? TK_UPDATE : TK_DELETE;
+ FALLTHROUGH;
+ default:
+ step->op = TK_UPDATE;
}
+ step->trigger = trigger;
+ if (is_update) {
+ fkey->on_update_trigger = trigger;
+ trigger->op = TK_UPDATE;
+ } else {
+ fkey->on_delete_trigger = trigger;
+ trigger->op = TK_DELETE;
+ }
return trigger;
}
-/*
- * 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 = 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 = sqlite3FkReferences(pTab); pFKey;
- pFKey = pFKey->pNextTo) {
- if (aChange == 0
- || fkParentIsModified(pTab, pFKey, aChange)) {
- struct sql_trigger *pAct =
- fkActionTrigger(pParse, pTab, pFKey,
- pChanges);
- if (pAct == 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->pNextFrom */
-
- for (pFKey = pTab->pFKey; pFKey; pFKey = pNext) {
- /* Remove the FK from the fkeyHash hash table. */
- if (!db || db->pnBytesFreed == 0) {
- if (pFKey->pPrevTo) {
- pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
- } else {
- void *p = (void *)pFKey->pNextTo;
- const char *z =
- (p ? pFKey->pNextTo->zTo : pFKey->zTo);
- sqlite3HashInsert(&pTab->pSchema->fkeyHash, z,
- p);
- }
- if (pFKey->pNextTo) {
- pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
- }
- }
-
- /* EV: R-30323-21917 Each foreign key constraint in SQLite is
- * classified as either immediate or deferred.
- */
- assert(pFKey->isDeferred == 0 || pFKey->isDeferred == 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 = pFKey->pNextFrom;
- sqlite3DbFree(db, pFKey);
+ if ((user_session->sql_flags & SQLITE_ForeignKeys) == 0)
+ return;
+ struct space *space = space_by_id(tab->def->id);
+ assert(space != NULL);
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
+ if (changes != NULL &&
+ !fkey_parent_is_modified(fk->def, changes))
+ continue;
+ struct sql_trigger *pAct =
+ fkey_action_trigger(parser, tab, fk, changes != NULL);
+ if (pAct == 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 == ON_CONFLICT_ACTION_REPLACE ||
on_error == ON_CONFLICT_ACTION_IGNORE);
bool no_delete_triggers =
- (0 == (user_session->sql_flags &
- SQLITE_RecTriggers) ||
- sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) ==
- NULL);
+ (user_session->sql_flags & SQLITE_RecTriggers) == 0 ||
+ sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) == NULL;
+ struct space *space = space_by_id(pTab->def->id);
+ assert(space != NULL);
bool no_foreign_keys =
- (0 == (user_session->sql_flags &
- SQLITE_ForeignKeys) ||
- (0 == pTab->pFKey &&
- 0 == sqlite3FkReferences(pTab)));
+ (user_session->sql_flags & SQLITE_ForeignKeys) == 0 ||
+ (rlist_empty(&space->child_fkey) &&
+ ! rlist_empty(&space->parent_fkey));
if (no_secondary_indexes && no_foreign_keys &&
proper_error_action && no_delete_triggers) {
@@ -1559,7 +1558,7 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing context */
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 != 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) != 0
- && pDest->pFKey != 0) {
+ struct space *dest = space_by_id(pDest->def->id);
+ assert(dest != NULL);
+ if ((user_session->sql_flags & SQLITE_ForeignKeys) != 0 &&
+ !rlist_empty(&dest->child_fkey))
return 0;
- }
#endif
if ((user_session->sql_flags & SQLITE_CountRows) != 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 = pVdbe->db;
(void)tripCode;
- struct session *user_session = current_session();
-
- /* DDL is impossible inside a transaction. */
- assert((user_session->sql_flags & SQLITE_InternChanges) == 0
- || db->init.busy == 1);
/* 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"
/*
** Disable all error recovery processing in the parser push-down
@@ -281,8 +282,8 @@ ccons ::= UNIQUE onconf(R). {sql_create_index(pParse,0,0,0,R,0,0,
SQL_INDEX_TYPE_CONSTRAINT_UNIQUE);}
ccons ::= CHECK LP expr(X) RP. {sql_add_check_constraint(pParse,&X);}
ccons ::= REFERENCES nm(T) eidlist_opt(TA) refargs(R).
- {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
-ccons ::= defer_subclause(D). {sqlite3DeferForeignKey(pParse,D);}
+ {sql_create_foreign_key(pParse, NULL, NULL, NULL, &T, TA, false, R);}
+ccons ::= defer_subclause(D). {fkey_change_defer_mode(pParse, D);}
ccons ::= COLLATE id(C). {sqlite3AddCollateType(pParse, &C);}
// The optional AUTOINCREMENT keyword
@@ -296,19 +297,23 @@ autoinc(X) ::= AUTOINCR. {X = 1;}
// check fails.
//
%type refargs {int}
-refargs(A) ::= . { A = ON_CONFLICT_ACTION_NONE*0x0101; /* EV: R-19803-45884 */}
+refargs(A) ::= . { A = FKEY_NO_ACTION; }
refargs(A) ::= refargs(A) refarg(Y). { A = (A & ~Y.mask) | Y.value; }
%type refarg {struct {int value; int mask;}}
-refarg(A) ::= MATCH nm. { A.value = 0; A.mask = 0x000000; }
+refarg(A) ::= MATCH matcharg(X). { A.value = X<<16; A.mask = 0xff0000; }
refarg(A) ::= ON INSERT refact. { A.value = 0; A.mask = 0x000000; }
refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; }
refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; }
+%type matcharg {int}
+matcharg(A) ::= SIMPLE. { A = FKEY_MATCH_SIMPLE; }
+matcharg(A) ::= PARTIAL. { A = FKEY_MATCH_PARTIAL; }
+matcharg(A) ::= FULL. { A = FKEY_MATCH_FULL; }
%type refact {int}
-refact(A) ::= SET NULL. { A = OE_SetNull; /* EV: R-33326-45252 */}
-refact(A) ::= SET DEFAULT. { A = OE_SetDflt; /* EV: R-33326-45252 */}
-refact(A) ::= CASCADE. { A = OE_Cascade; /* EV: R-33326-45252 */}
-refact(A) ::= RESTRICT. { A = OE_Restrict; /* EV: R-33326-45252 */}
-refact(A) ::= NO ACTION. { A = ON_CONFLICT_ACTION_NONE; /* EV: R-33326-45252 */}
+refact(A) ::= SET NULL. { A = FKEY_ACTION_SET_NULL; }
+refact(A) ::= SET DEFAULT. { A = FKEY_ACTION_SET_DEFAULT; }
+refact(A) ::= CASCADE. { A = FKEY_ACTION_CASCADE; }
+refact(A) ::= RESTRICT. { A = FKEY_ACTION_RESTRICT; }
+refact(A) ::= NO ACTION. { A = FKEY_NO_ACTION; }
%type defer_subclause {int}
defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt. {A = 0;}
defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X). {A = X;}
@@ -334,8 +339,7 @@ tcons ::= CHECK LP expr(E) RP onconf.
{sql_add_check_constraint(pParse,&E);}
tcons ::= 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) ::= . {A = 0;}
@@ -1431,6 +1435,17 @@ cmd ::= ANALYZE nm(X). {sqlite3Analyze(pParse, &X);}
cmd ::= ALTER TABLE fullname(X) RENAME TO nm(Z). {
sqlite3AlterRenameTable(pParse,X,&Z);
}
+
+cmd ::= 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 ::= 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 ::= 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 <box/index.h>
-#include <box/box.h>
-#include <box/tuple.h>
+#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);
}
-/*
- * 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 = "SET NULL";
- break;
- case OE_SetDflt:
- zName = "SET DEFAULT";
- break;
- case OE_Cascade:
- zName = "CASCADE";
- break;
- case OE_Restrict:
- zName = "RESTRICT";
- break;
- default:
- zName = "NO ACTION";
- assert(action == 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 == NULL)
break;
- Table *table = sqlite3HashFind(&db->pSchema->tblHash, zRight);
- if (table == NULL)
- break;
- FKey *fkey = table->pFKey;
- if (fkey == NULL)
+ uint32_t space_id = box_space_id_by_name(zRight,
+ strlen(zRight));
+ if (space_id == BOX_ID_NIL)
break;
+ struct space *space = space_by_id(space_id);
int i = 0;
pParse->nMem = 8;
- while (fkey != NULL) {
- for (int j = 0; j < fkey->nCol; j++) {
- const char *name =
- table->def->fields[
- fkey->aCol[j].iFrom].name;
+ struct fkey *fkey;
+ rlist_foreach_entry(fkey, &space->child_fkey, child_link) {
+ struct fkey_def *fdef = fkey->def;
+ for (uint32_t j = 0; j < fdef->field_count; j++) {
+ struct space *parent =
+ space_by_id(fdef->parent_id);
+ assert(parent != NULL);
+ uint32_t ch_fl = fdef->links[j].child_field;
+ const char *child_col =
+ space->def->fields[ch_fl].name;
+ uint32_t pr_fl = fdef->links[j].parent_field;
+ const char *parent_col =
+ 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 = 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 = pParse->nMem + 1;
- pParse->nMem += 4;
- regKey = ++pParse->nMem;
- regRow = ++pParse->nMem;
- k = sqliteHashFirst(&db->pSchema->tblHash);
- while (k) {
- if (zRight) {
- pTab =
- sqlite3LocateTable(pParse, 0,
- zRight);
- k = 0;
- } else {
- pTab = (Table *) sqliteHashData(k);
- k = sqliteHashNext(k);
- }
- if (pTab == 0 || pTab->pFKey == 0)
- continue;
- if ((int)pTab->def->field_count + regRow > pParse->nMem)
- pParse->nMem = pTab->def->field_count + regRow;
- sqlite3OpenTable(pParse, 0, pTab, OP_OpenRead);
- sqlite3VdbeLoadString(v, regResult,
- pTab->def->name);
- for (i = 1, pFK = pTab->pFKey; pFK;
- i++, pFK = pFK->pNextFrom) {
- pParent =
- sqlite3HashFind(&db->pSchema->tblHash,
- pFK->zTo);
- if (pParent == NULL)
- continue;
- pIdx = 0;
- x = sqlite3FkLocateIndex(pParse,
- pParent, pFK,
- &pIdx, 0);
- if (x != 0) {
- k = 0;
- break;
- }
- if (pIdx == NULL) {
- sqlite3OpenTable(pParse, i,
- pParent,
- OP_OpenRead);
- continue;
- }
- struct space *space =
- space_cache_find(pIdx->pTable->
- def->id);
- assert(space != NULL);
- sqlite3VdbeAddOp4(v, OP_OpenRead, i,
- pIdx->def->iid, 0,
- (void *) space,
- P4_SPACEPTR);
-
- }
- assert(pParse->nErr > 0 || pFK == 0);
- if (pFK)
- break;
- if (pParse->nTab < i)
- pParse->nTab = i;
- addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
- VdbeCoverage(v);
- for (i = 1, pFK = pTab->pFKey; pFK;
- i++, pFK = pFK->pNextFrom) {
- pParent =
- sqlite3HashFind(&db->pSchema->tblHash,
- pFK->zTo);
- pIdx = 0;
- aiCols = 0;
- if (pParent) {
- x = sqlite3FkLocateIndex(pParse,
- pParent,
- pFK,
- &pIdx,
- &aiCols);
- assert(x == 0);
- }
- addrOk = sqlite3VdbeMakeLabel(v);
- if (pParent && pIdx == 0) {
- int iKey = pFK->aCol[0].iFrom;
- assert(iKey >= 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 = 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[] = {
/* 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[] = {
/* 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 = db;
+ rlist_create(&parser->new_fkey);
region_create(&parser->region, &cord()->slabc);
}
@@ -428,6 +429,9 @@ sql_parser_destroy(Parse *parser)
sqlite3 *db = 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 != NULL) {
assert(db->lookaside.bDisable >=
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 */
};
/*
@@ -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 pFrom */
- 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 pFrom to columns in zTo */
- int iFrom; /* Index of column in pFrom */
- 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 */
/*
* This object holds a record which has been parsed out into individual
@@ -2844,6 +2806,33 @@ enum ast_type {
ast_type_MAX
};
+/**
+ * Structure representing foreign keys constraints appeared
+ * within CREATE TABLE statement. Used only during parsing.
+ */
+struct fkey_parse {
+ /**
+ * Foreign keys constraint declared in <CREATE TABLE ...>
+ * 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==0)
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
+ * <CREATE TABLE> 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
+ * <ALTER TABLE child ADD CONSTRAINT constraint
+ * FOREIGN KEY (child_cols) REFERENCES parent (parent_cols)>
+ * OR to handle <CREATE TABLE ...>
+ *
+ * @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
+ * <ALTER TABLE table DROP CONSTRAINT constraint> 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
-/* 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);
/*
* 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 = db->pSchema;
if (ALWAYS(pSchema != 0)) {
HashElem *p;
-
- nByte +=
- ROUND8(sizeof(HashElem)) *
- (pSchema->tblHash.count +
- pSchema->fkeyHash.count);
- nByte += sqlite3_msize(pSchema->tblHash.ht);
- nByte += sqlite3_msize(pSchema->fkeyHash.ht);
+ nByte += ROUND8(sizeof(HashElem)) *
+ pSchema->tblHash.count;
for (p = sqliteHashFirst(&pSchema->tblHash); p;
p = 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);
-/* 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);
+/**
+ * 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 = 0;
- hasFK = sqlite3FkRequired(pTab, aXRef);
+ hasFK = fkey_is_required(pTab->def->id, aXRef);
/* 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 != 0 || hasFK != 0 || trigger != NULL) {
- u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
+ struct space *space = space_by_id(pTab->def->id);
+ assert(space != NULL);
+ u32 oldmask = hasFK ? space->fkey_mask : 0;
oldmask |= 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);
/* Do FK constraint checks. */
- if (hasFK) {
- sqlite3FkCheck(pParse, pTab, regOldPk, 0, aXRef);
- }
+ if (hasFK)
+ fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef);
/* 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);
}
- if (hasFK) {
- sqlite3FkCheck(pParse, pTab, 0, regNewPk, aXRef);
- }
+ if (hasFK)
+ fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef);
/* 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);
}
/* 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;
@@ -4661,20 +4661,6 @@ case OP_RenameTable: {
rc = sql_rename_table(space_id, zNewTableName, &zSqlStmt);
if (rc) goto abort_due_to_error;
- /* If it is parent table, all children statements should be updated. */
- for (pFKey = sqlite3FkReferences(pTab); pFKey; pFKey = pFKey->pNextTo) {
- assert(pFKey->zTo != NULL);
- assert(pFKey->pFrom != NULL);
- rc = tarantoolSqlite3RenameParentTable(pFKey->pFrom->def->id,
- pFKey->zTo,
- zNewTableName);
- if (rc) goto abort_due_to_error;
- pFKey->zTo = sqlite3DbStrNDup(db, zNewTableName,
- sqlite3Strlen30(zNewTableName));
- sqlite3HashInsert(&db->pSchema->fkeyHash, zOldTableName, 0);
- sqlite3HashInsert(&db->pSchema->fkeyHash, zNewTableName, pFKey);
- }
-
sqlite3UnlinkAndDeleteTable(db, pTab->def->name);
init.db = 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 = 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);
+ ]], {
+ -- <alter2-1.1>
+ 0
+ -- </alter2-1.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.2",
+ [[
+ INSERT INTO t1 VALUES(2, 3, 2);
+ ]], {
+ -- <alter2-1.2>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-1.2>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.3",
+ [[
+ DELETE FROM t1;
+ ]], {
+ -- <alter2-1.3>
+ 0
+ -- </alter2-1.3>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.4",
+ [[
+ ALTER TABLE t1 DROP CONSTRAINT fk1;
+ INSERT INTO t1 VALUES(2, 3, 2);
+ ]], {
+ -- <alter2-1.4>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-1.4>
+ })
+
+test:do_execsql_test(
+ "alter2-1.5",
+ [[
+ ALTER TABLE t1 DROP CONSTRAINT fk2;
+ INSERT INTO t1 VALUES(2, 3, 2);
+ SELECT * FROM t1;
+ ]], {
+ -- <alter2-1.5>
+ 2, 3, 2
+ -- </alter2-1.5>
+ })
+
+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);
+ ]], {
+ -- <alter2-1.6>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-1.6>
+ })
+
+test:do_execsql_test(
+ "alter2-1.7",
+ [[
+ ALTER TABLE t1 DROP CONSTRAINT fk1;
+ INSERT INTO t1 VALUES(5, 2, 1);
+ SELECT * FROM t1;
+ ]], {
+ -- <alter2-1.7>
+ 3, 1, 1, 5, 2, 1
+ -- </alter2-1.7>
+ })
+
+test:do_test(
+ "alter2-1.7.1",
+ function()
+ test:execsql([[DELETE FROM t1;]])
+ t1 = box.space.T1
+ if t1.engine ~= 'vinyl' then
+ return
+ end
+ box.snapshot()
+ end, {
+ -- <alter2-1.7.1>
+ -- </alter2-1.7.1>
+ })
+
+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;
+ ]], {
+ -- <alter2-1.8>
+ 0
+ -- </alter2-1.8>
+ })
+
+test:do_execsql_test(
+ "alter2-1.9",
+ [[
+ SELECT * FROM "_fk_constraint";
+ ]], {
+ -- <alter2-1.9>
+ -- </alter2-1.9>
+ })
+
+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);
+ ]], {
+ -- <alter2-2.1>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-2.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-2.2",
+ [[
+ INSERT INTO parent VALUES(1, 1, 2);
+ INSERT INTO child VALUES(2, 1, 1);
+ ]], {
+ -- <alter2-2.2>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-2.2>
+ })
+
+test:do_catchsql_test(
+ "alter2-2.3",
+ [[
+ ALTER TABLE child DROP CONSTRAINT fk;
+ INSERT INTO parent VALUES(3, 4, 2);
+ ]], {
+ -- <alter2-2.3>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-2.3>
+ })
+
+test:do_execsql_test(
+ "alter2-2.4",
+ [[
+ ALTER TABLE parent DROP CONSTRAINT fk;
+ INSERT INTO parent VALUES(3, 4, 2);
+ SELECT * FROM parent;
+ ]], {
+ -- <alter2-2.4>
+ 1, 1, 2, 3, 4, 2
+ -- </alter2-2.4>
+ })
+
+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 = 1;
+ SELECT * FROM CHILD;
+ ]], {
+ -- <alter2-3.1>
+ 3, 2, 2
+ -- </alter2-3.1>
+ })
+
+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 = 5 WHERE id = 1;
+ SELECT * FROM CHILD;
+ ]], {
+ -- <alter2-3.2>
+ 3, 2, 2, 5, 1, 1
+ -- </alter2-3.2>
+ })
+
+test:do_catchsql_test(
+ "alter2-4.1",
+ [[
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child;
+ ]], {
+ -- <alter2-4.1>
+ 1, "near \"REFERENCES\": syntax error"
+ -- </alter2-4.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-4.2",
+ [[
+ ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child;
+ ]], {
+ -- <alter2-4.1>
+ 1, "near \"(\": syntax error"
+ -- </alter2-4.2>
+ })
+
+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 = require("sqltester")
-test:plan(19)
+test:plan(18)
-- This file implements regression tests for foreign keys.
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));
]], {
-- <fkey1-1.1>
-- </fkey1-1.1>
@@ -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);
]], {
-- <fkey1-3.1>
- 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'
-- </fkey1-3.1>
})
@@ -81,8 +81,8 @@ test:do_execsql_test(
PRAGMA foreign_key_list(t7);
]], {
-- <fkey1-3.2>
- 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'
-- </fkey1-3.2>
})
@@ -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);
]], {
-- <fkey1-3.3>
- 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'
-- </fkey1-3.3>
})
@@ -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);
]], {
-- <fkey1-3.4>
- 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'
-- </fkey1-3.4>
})
@@ -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(
-- </fkey1-5.6>
})
-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));
]], {
-- <fkey1-6.1>
+ 1, "Failed to create foreign key constraint 'FK_CONSTRAINT_1_C1': referenced fields don't compose unique index"
-- </fkey1-6.1>
})
-test:do_catchsql_test(
- "fkey1-6.2",
- [[
- INSERT INTO c1 VALUES(1);
- ]], {
- -- <fkey1-6.2>
- 1, "foreign key mismatch - \"C1\" referencing \"P1\""
- -- </fkey1-6.2>
- })
-
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);
]], {
-- <fkey1-6.3>
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 = require("sqltester")
-test:plan(121)
+test:plan(116)
-- This file implements regression tests for foreign keys.
@@ -14,7 +14,7 @@ test:do_execsql_test(
CREATE TABLE t4(c PRIMARY KEY REFERENCES t3, d);
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);
]], {
-- <fkey2-1.1>
-- </fkey2-1.1>
@@ -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;
]], {
-- <fkey2-2.1>
- "35.0", "text"
+ 35, "integer"
-- </fkey2-2.1>
})
@@ -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);
]], {
-- <fkey2-5.1>
@@ -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;
]], {
-- <fkey2-6.1>
@@ -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;
]], {
-- <fkey2-7.1>
- 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"
-- </fkey2-7.1>
})
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;
]], {
-- <fkey2-7.2>
- 1, "no such table: C"
+ 1, "referenced table can't be view"
-- </fkey2-7.2>
})
@@ -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;
]], {
-- <fkey2-7.3>
- 1, "no such view: V"
+ 1, "Failed to create foreign key constraint 'FK_CONSTRAINT_1_C': field collation mismatch"
-- </fkey2-7.3>
})
@@ -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;
]], {
-- <fkey2-7.4>
- 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"
-- </fkey2-7.4>
})
@@ -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);
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);
]], {
-- <fkey2-10.6>
- 1, "foreign key constraint references nonexistent table: NOSUCHTABLE"
+ 1, "Space 'NOSUCHTABLE' does not exist"
-- </fkey2-10.6>
})
@@ -1083,7 +1077,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-10.9",
[[
- DELETE FROM t2;
+ DROP TABLE t2;
DROP TABLE t1;
]], {
-- <fkey2-10.9>
@@ -1091,47 +1085,6 @@ test:do_execsql_test(
})
test:do_catchsql_test(
- "fkey2-10.10",
- [[
- INSERT INTO t2 VALUES('x');
- ]], {
- -- <fkey2-10.10>
- 1, "no such table: T1"
- -- </fkey2-10.10>
- })
-
-test:do_execsql_test(
- "fkey2-10.11",
- [[
- CREATE TABLE t1(x PRIMARY KEY);
- INSERT INTO t1 VALUES('x');
- INSERT INTO t2 VALUES('x');
- ]], {
- -- <fkey2-10.11>
- -- </fkey2-10.11>
- })
-
-test:do_catchsql_test(
- "fkey2-10.12",
- [[
- DROP TABLE t1;
- ]], {
- -- <fkey2-10.12>
- 1, "FOREIGN KEY constraint failed"
- -- </fkey2-10.12>
- })
-
-test:do_execsql_test(
- "fkey2-10.13",
- [[
- DROP TABLE t2;
- DROP TABLE t1;
- ]], {
- -- <fkey2-10.13>
- -- </fkey2-10.13>
- })
-
-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));
]], {
-- <fkey2-10.14>
+ 1, "Failed to create foreign key constraint 'fk_constraint_1_CC': foreign key refers to nonexistent field Z"
-- </fkey2-10.14>
})
-test:do_catchsql_test(
- "fkey2-10.15",
- [[
- INSERT INTO cc VALUES(1, 2);
- ]], {
- -- <fkey2-10.15>
- 1, "foreign key mismatch - \"CC\" referencing \"PP\""
- -- </fkey2-10.15>
- })
-
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(
-- </fkey2-10.17>
})
-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(
-- </fkey2-10.18>
})
-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(
-- </fkey2-10.19>
})
-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;
]], {
-- <fkey2-10.20>
+ 1, "referenced table can't be view"
-- </fkey2-10.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 = 14, b = 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 = 14, b = 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);
@@ -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 = 200 where a = 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);
]], {
-- <fkey3-6.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=1000 WHERE id=2;
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);
]], {
-- <table-10.2>
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);
]], {
-- <table-10.3>
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);
]], {
-- <table-10.4>
0
@@ -791,14 +791,16 @@ test:do_catchsql_test(
);
]], {
-- <table-10.7>
- 0
+ 1, "Failed to create foreign key constraint 'fk_constraint_1_T6': foreign key refers to nonexistent field B"
-- </table-10.7>
})
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(
);
]], {
-- <table-10.9>
- 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"
-- </table-10.9>
})
@@ -833,7 +835,7 @@ test:do_test(
]]
end, {
-- <table-10.10>
- 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"
-- </table-10.10>
})
@@ -846,7 +848,7 @@ test:do_test(
]]
end, {
-- <table-10.11>
- 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"
-- </table-10.11>
})
@@ -861,7 +863,7 @@ test:do_test(
]]
end, {
-- <table-10.12>
- 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]]
-- </table-10.12>
})
@@ -876,7 +878,7 @@ test:do_test(
]]
end, {
-- <table-10.13>
- 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]]
-- </table-10.13>
})
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='false';
PRAGMA recursive_triggers = 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
);
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=1')
+-- 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=1')
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()
--- Clean-up SQL DD hash.
-test_run:cmd('restart server default with cleanup=1')
+-- 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=1')
--
2.15.1
More information about the Tarantool-patches
mailing list