[tarantool-patches] Re: [PATCH 2/5] schema: add new system space for FK constraints
n.pettik
korablev at tarantool.org
Wed Jul 25 13:03:48 MSK 2018
> On 18 Jul 2018, at 00:05, Vladislav Shpilevoy <v.shpilevoy at tarantool.org> wrote:
>
> Thanks for the patch! See 18 comments below and a patch on
> the branch.
>
> On 13/07/2018 05:04, Nikita Pettik wrote:
>> This patch introduces new system space to persist foreign keys
>
> 1. Typos: contraints, chils.
Fixed.
>
>> contraints. Format of the space:
>> _fk_constraint (space id = 350)
>> [<contraint name> STR, <parent id> UINT, <child id> UINT,> <is deferred> BOOL, <match> STR, <on delete action> STR,
>> <on update action> STR, <field links> ARRAY<MAP< UINT : UINT >>]
>> FK constraint is local to space, so every pair <FK name, child id>
>> is unique (and it is PK in _fk_constraint space).
>> After insertion into this space, new instance describing FK constraint
>> is created. FK constraints are held in data-dictionary as two lists
>> (for child and parent constraints) in struct space.
>> There is a list of FK restrictions:
>> - At the time of FK creation parent and chils spaces must exist;
>> - VIEWs can't be involved into FK processing;
>> - Child space must be empty;
>> - Types of referencing and referenced fields must match;
>
> 2. How about not match, but fit? See field_type1_contains_type2() and
> its usages.
Seems that it doesn’t contradicts ANSI:
• The declared type of each referencing column shall be comparable to the declared type of the corresponding referenced column.
There shall not be corresponding constituents of the declared type of a referencing column and the declared type of the corresponding
referenced column such that one constituent is datetime with time zone and the other is datetime without time zone.
Hence, they must be simply ‘comparable’ which in turn means
that following situation is OK:
CREATE TABLE parent (id INT PRIMARY KEY, a UNQUE);
CREATE TABLE child (id INT PRIMARY KEY REFERENCES parent (a));
In other words, INT can be mapped into scalar (i.e. is subset of scalar type),
but not vice versa:
@@ -3737,13 +3784,16 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
fk_def->name, "foreign key refers to "
"nonexistent field");
}
- if (child_space->def->fields[child_fieldno].type !=
- parent_space->def->fields[parent_fieldno].type) {
+ struct field_def child_field =
+ child_space->def->fields[child_fieldno];
+ struct field_def parent_field =
+ parent_space->def->fields[parent_fieldno];
+ if (! field_type1_contains_type2(parent_field.type,
+ child_field.type)) {
tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
fk_def->name, "field type mismatch");
}
- if (child_space->def->fields[child_fieldno].coll_id !=
- parent_space->def->fields[parent_fieldno].coll_id) {
+ if (child_field.coll_id != parent_field.coll_id) {
>> diff --git a/src/box/alter.cc b/src/box/alter.cc
>> index 89b11dcd3..aaa56bd21 100644
>> --- a/src/box/alter.cc
>> +++ b/src/box/alter.cc
>> @@ -1889,6 +1916,22 @@ on_replace_dd_index(struct trigger * /* trigger */, void *event)
>> "can not add a secondary key before primary");
>> }
>> + /*
>> + * Can't drop index if foreign key constraints references
>> + * this index.
>> + */
>> + if (new_tuple == NULL) {
>
> 3. But it can be non-drop, but alter, for example. Drop case is processed
> below:
>
> /* Case 1: drop the index, if it is dropped. */
> if (old_index != NULL && new_tuple == NULL) {
>
> What if I want to rename an index? Also see index_def_change_requires_rebuild.
Fixed:
+++ b/src/box/alter.cc
@@ -1920,7 +1920,7 @@ on_replace_dd_index(struct trigger * /* trigger */, void *event)
* Can't drop index if foreign key constraints references
* this index.
*/
- if (new_tuple == NULL) {
+ if (old_index != NULL && new_tuple == NULL) {
Also, I didn’t get what you mean by mentioning index_def_change_requires_rebuild.
Can index id change on its renaming?
>
>> + struct fkey *fk = old_space->parent_fkey;
>> + while (fk != NULL) {
>> + if (old_space->parent_fkey->index_id == iid) {
>> + tnt_raise(ClientError, ER_ALTER_SPACE,
>> + space_name(old_space),
>> + "can not drop referenced index");
>> + }
>> + fk = fk->fkey_parent_next;
>> + }
>> + }
>> +
>> @@ -3404,6 +3447,387 @@ on_replace_dd_trigger
>> txn_on_commit(txn, on_commit);
>> }
>> +/**
>> + * Decode MsgPack array of links. It consists from maps:
>> + * {parent_id (UINT) : child_id (UINT)}.
>> + *
>> + * @param data MsgPack array of links.
>> + * @param[out] out_count Count of links.
>> + * @param constraint_name Constraint name to use in error
>> + * messages.
>> + * @param constraint_len Length of constraint name.
>> + * @param errcode Errcode for client errors.
>> + * @retval Array of links.
>> + */
>> +static struct field_link *
>> +fkey_links_decode(const char *data, uint32_t *out_count,
>> + const char *constraint_name, uint32_t constraint_len,
>> + uint32_t errcode)
>> +{
>> + assert(mp_typeof(*data) == MP_ARRAY);
>> + uint32_t count = mp_decode_array(&data);
>> + if (count == 0) {
>> + tnt_raise(ClientError, errcode,
>> + tt_cstr(constraint_name, constraint_len),
>> + "at least one link must be specified");
>> + }
>> + *out_count = count;
>> + size_t size = count * sizeof(struct field_link);
>> + struct field_link *region_links =
>> + (struct field_link *) region_alloc_xc(&fiber()->gc, size);
>> + memset(region_links, 0, size);
>> + const char **map = &data;
>> + for (uint32_t i = 0; i < count; ++i) {
>> + uint32_t map_sz = mp_decode_map(map);
>> + if (map_sz != 2) {
>> + tnt_raise(ClientError, errcode,
>> + tt_cstr(constraint_name, constraint_len),
>> + tt_sprintf("link must be map with 2 fields"));
>> + }
>> + if (mp_typeof(**map) != MP_STR) {
>> + tnt_raise(ClientError, errcode,
>> + tt_cstr(constraint_name, constraint_len),
>> + tt_sprintf("link %d is not map "\
>> + "with string keys", i));
>> + }
>
> 4. What about a second key? mp_typeof(**map) right after decoding its
> header returns type of the first key. But second can be of another type.
Fixed:
- if (mp_typeof(**map) != MP_STR) {
- tnt_raise(ClientError, errcode,
- tt_cstr(constraint_name, constraint_len),
- tt_sprintf("link %d is not map "\
- "with string keys", i));
- }
for (uint8_t j = 0; j < map_sz; ++j) {
+ if (mp_typeof(**map) != MP_STR) {
+ tnt_raise(ClientError, errcode,
+ tt_cstr(constraint_name,
+ constraint_len),
+ tt_sprintf("link %d is not map "\
+ "with string keys", i));
+ }
>
> And why do we need this {child = , parent = }, ... sequence? Lets just
> use {<uint>, <uint>}, {<uint>, <uint>}, {<uint>, <uint>} ... It is more
> compact and simpler to parse. Or even just two arrays: first child and
> second parent field numbers. It is the most canonical way similar to
> SQL, it is not? When you specify two column ranges.
I use this format since it is easier to avoid confusing parent and child ids
(i.e. what comes first and what comes second). Even if we add separate
convenient Lua API for that purpose.
If you insist on doing that, I will change format.
> But in the internal format I vote to keep field_link. In the next
> patches I see, that common usage case is to iterate over the pairs,
> so for cache it is better to keep them.
>
>> +/**
>> + * Replace entry in child's and parent's lists of
>> + * FK constraints.
>> + *
>> + * @param child Child space of FK constraint.
>> + * @param parent Parent space of FK constraint.
>> + * @param new_fkey Constraint to be added to child and parent.
>> + * @param[out] old_fkey Constraint to be found and replaced.
>> + */
>> +static void
>> +fkey_list_replace(struct space *child, struct space *parent, const char *name,
>> + struct fkey *new_fkey, struct fkey **old_fkey)
>> +{
>
> 5. I see that for fkey you actually have invented rlist. Please, see rlist.h
> and use it for fkeys. It already has rlist_foreach_entry, rlist_add,
> rlist_add_tail, rlist_del etc. The struct foreign key will have two
> rlist links: in_child and in_parent.
diff --git a/src/box/alter.cc b/src/box/alter.cc
index 37a7063f0..5fd8771e3 100644
--- a/src/box/alter.cc
+++ b/src/box/alter.cc
@@ -576,12 +576,8 @@ space_swap_triggers(struct space *new_space, struct space *old_space)
static void
space_swap_fkeys(struct space *new_space, struct space *old_space)
{
- struct fkey *child_fkey = new_space->child_fkey;
- struct fkey *parent_fkey = new_space->parent_fkey;
- new_space->child_fkey = old_space->child_fkey;
- new_space->parent_fkey = old_space->parent_fkey;
- old_space->child_fkey = child_fkey;
- old_space->parent_fkey = parent_fkey;
+ rlist_swap(&new_space->child_fkey, &old_space->child_fkey);
+ rlist_swap(&new_space->parent_fkey, &old_space->parent_fkey);
}
/**
@@ -1764,7 +1760,7 @@ on_replace_dd_space(struct trigger * /* trigger */, void *event)
* one referenced index which can't be dropped
* before constraint itself.
*/
- if (old_space->child_fkey != NULL) {
+ if (! rlist_empty(&old_space->child_fkey)) {
tnt_raise(ClientError, ER_DROP_SPACE,
space_name(old_space),
"the space has foreign key constraints");
@@ -1972,14 +1968,13 @@ on_replace_dd_index(struct trigger * /* trigger */, void *event)
* this index.
*/
if (old_index != NULL && new_tuple == NULL) {
- struct fkey *fk = old_space->parent_fkey;
- while (fk != NULL) {
- if (old_space->parent_fkey->index_id == iid) {
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &old_space->parent_fkey, parent_link) {
+ if (fk->index_id == iid) {
tnt_raise(ClientError, ER_ALTER_SPACE,
space_name(old_space),
"can not drop referenced index");
}
- fk = fk->fkey_parent_next;
}
}
@@ -3637,43 +3632,26 @@ fkey_def_new_from_tuple(const struct tuple *tuple, uint32_t errcode)
}
/**
- * Replace entry in child's and parent's lists of
- * FK constraints.
+ * Remove FK constraint from child's list.
+ * Entries in child list are supposed to be unique
+ * by their name.
*
- * @param child Child space of FK constraint.
- * @param parent Parent space of FK constraint.
- * @param new_fkey Constraint to be added to child and parent.
- * @param[out] old_fkey Constraint to be found and replaced.
+ * @param list List of child FK constraints.
+ * @param fkey_name Name of constraint to be removed.
+ * @retval FK being removed.
*/
-static void
-fkey_list_replace(struct space *child, struct space *parent, const char *name,
- struct fkey *new_fkey, struct fkey **old_fkey)
-{
- *old_fkey = NULL;
- struct fkey **fk = &parent->parent_fkey;
- while (*fk != NULL && !(strcmp((*fk)->def->name, name) == 0 &&
- (*fk)->def->child_id == child->def->id))
- fk = &((*fk)->fkey_parent_next);
- if (*fk != NULL) {
- *old_fkey = *fk;
- *fk = (*fk)->fkey_parent_next;
- }
- if (new_fkey != NULL) {
- new_fkey->fkey_parent_next = parent->parent_fkey;
- parent->parent_fkey = new_fkey;
- }
- fk = &child->child_fkey;
- /* In child's list all constraints are unique by name. */
- while (*fk != NULL && strcmp((*fk)->def->name, name) != 0)
- fk = &((*fk)->fkey_child_next);
- if (*fk != NULL) {
- assert(*old_fkey == *fk);
- *fk = (*fk)->fkey_child_next;
- }
- if (new_fkey != NULL) {
- new_fkey->fkey_child_next = child->child_fkey;
- child->child_fkey = new_fkey;
+static struct fkey *
+fkey_remove_child(struct rlist *list, const char *fkey_name)
+{
+ struct fkey *fk;
+ rlist_foreach_entry(fk, list, child_link) {
+ if (strcmp(fkey_name, fk->def->name) == 0) {
+ rlist_del_entry(fk, child_link);
+ return fk;
+ }
}
+ unreachable();
+ return NULL;
}
/**
@@ -3686,11 +3664,9 @@ on_create_fkey_rollback(struct trigger *trigger, void *event)
{
(void) event;
struct fkey *fk = (struct fkey *)trigger->data;
- struct space *parent = space_by_id(fk->def->parent_id);
- struct space *child = space_by_id(fk->def->child_id);
- struct fkey *fkey = NULL;
- fkey_list_replace(child, parent, fk->def->name, NULL, &fkey);
- fkey_delete(fkey);
+ rlist_del_entry(fk, parent_link);
+ rlist_del_entry(fk, child_link);
+ fkey_delete(fk);
}
/** Return old FK and release memory for the new one. */
@@ -3701,9 +3677,12 @@ on_replace_fkey_rollback(struct trigger *trigger, void *event)
struct fkey *fk = (struct fkey *)trigger->data;
struct space *parent = space_by_id(fk->def->parent_id);
struct space *child = space_by_id(fk->def->child_id);
- struct fkey *old_fkey = NULL;
- fkey_list_replace(child, parent, fk->def->name, fk, &old_fkey);
+ struct fkey *old_fkey = fkey_remove_child(&child->child_fkey,
+ fk->def->name);
+ rlist_del_entry(old_fkey, parent_link);
fkey_delete(old_fkey);
+ rlist_add_entry(&child->child_fkey, fk, child_link);
+ rlist_add_entry(&parent->parent_fkey, fk, parent_link);
}
/** On rollback of drop simply return back FK to DD. */
@@ -3714,10 +3693,8 @@ on_drop_fkey_rollback(struct trigger *trigger, void *event)
struct fkey *fk_to_restore = (struct fkey *)trigger->data;
struct space *parent = space_by_id(fk_to_restore->def->parent_id);
struct space *child = space_by_id(fk_to_restore->def->child_id);
- struct fkey *old_fk;
- fkey_list_replace(child, parent, fk_to_restore->def->name, fk_to_restore,
- &old_fk);
- assert(old_fk == NULL);
+ rlist_add_entry(&child->child_fkey, fk_to_restore, child_link);
+ rlist_add_entry(&parent->parent_fkey, fk_to_restore, parent_link);
}
/**
@@ -3845,16 +3822,24 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
memset(fkey, 0, sizeof(*fkey));
fkey->def = fk_def;
fkey->index_id = fk_index->def->iid;
- struct fkey *old_fk;
- fkey_list_replace(child_space, parent_space, fk_def->name,
- fkey, &old_fk);
if (old_tuple == NULL) {
+ rlist_add_entry(&child_space->child_fkey, fkey,
+ child_link);
+ rlist_add_entry(&parent_space->parent_fkey, fkey,
+ parent_link);
struct trigger *on_rollback =
txn_alter_trigger_new(on_create_fkey_rollback,
fkey);
txn_on_rollback(txn, on_rollback);
- assert(old_fk == NULL);
} else {
+ struct fkey *old_fk =
+ fkey_remove_child(&child_space->child_fkey,
+ fk_def->name);
+ rlist_del_entry(old_fk, parent_link);
+ rlist_add_entry(&child_space->child_fkey, fkey,
+ child_link);
+ rlist_add_entry(&parent_space->parent_fkey, fkey,
+ parent_link);
struct trigger *on_rollback =
txn_alter_trigger_new(on_replace_fkey_rollback,
fkey);
@@ -3872,13 +3857,12 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
fkey_def_new_from_tuple(old_tuple,
ER_DROP_FK_CONSTRAINT);
auto fkey_guard = make_scoped_guard([=] { free(fk_def); });
- struct space *parent_space =
- space_cache_find_xc(fk_def->parent_id);
struct space *child_space =
space_cache_find_xc(fk_def->child_id);
- struct fkey *old_fkey = NULL;
- fkey_list_replace(child_space, parent_space, fk_def->name, NULL,
- &old_fkey);
+ struct fkey *old_fkey =
+ fkey_remove_child(&child_space->child_fkey,
+ fk_def->name);
+ rlist_del_entry(old_fkey, parent_link);
struct trigger *on_commit =
txn_alter_trigger_new(on_drop_or_replace_fkey_commit,
old_fkey);
diff --git a/src/box/fkey.h b/src/box/fkey.h
index b30136a1d..0d537b1a7 100644
--- a/src/box/fkey.h
+++ b/src/box/fkey.h
@@ -97,9 +97,9 @@ struct fkey {
/** Triggers for actions. */
struct sql_trigger *on_delete_trigger;
struct sql_trigger *on_update_trigger;
- /** Linked lists */
- struct fkey *fkey_parent_next;
- struct fkey *fkey_child_next;
+ /** Links for parent and child lists. */
+ struct rlist parent_link;
+ struct rlist child_link;
};
/**
diff --git a/src/box/space.c b/src/box/space.c
index 1a7851636..90a80ed7b 100644
--- a/src/box/space.c
+++ b/src/box/space.c
@@ -163,6 +163,8 @@ space_create(struct space *space, struct engine *engine,
space->index_map[index_def->iid] = index;
}
space_fill_index_map(space);
+ rlist_create(&space->parent_fkey);
+ rlist_create(&space->child_fkey);
return 0;
fail_free_indexes:
@@ -220,8 +222,8 @@ space_delete(struct space *space)
* on_replace_dd_trigger on deletion from _trigger.
*/
assert(space->sql_triggers == NULL);
- assert(space->child_fkey == NULL);
- assert(space->parent_fkey == NULL);
+ assert(rlist_empty(&space->parent_fkey));
+ assert(rlist_empty(&space->child_fkey));
space->vtab->destroy(space);
}
diff --git a/src/box/space.h b/src/box/space.h
index 2cf76a032..97650cffe 100644
--- a/src/box/space.h
+++ b/src/box/space.h
@@ -190,8 +190,8 @@ struct space {
* other words the table that is named in the REFERENCES
* clause.
*/
- struct fkey *parent_fkey;
- struct fkey *child_fkey;
+ struct rlist parent_fkey;
+ struct rlist child_fkey;
};
>> +/**
>> + * On rollback of creation we remove FK constraint from DD, i.e.
>> + * from parent's and child's lists of constraints and
>> + * release memory.
>> + */
>> +static void
>> +on_create_fkey_rollback(struct trigger *trigger, void *event)
>> +{
>> + (void) event;
>> + struct fkey *fk = (struct fkey *)trigger->data;
>> + struct space *parent = space_by_id(fk->def->parent_id);
>> + struct space *child = space_by_id(fk->def->child_id);
>> + struct fkey *fkey = NULL;
>> + fkey_list_replace(child, parent, fk->def->name, NULL, &fkey);
>> + fkey_delete(fkey);
>
> 6. After you fixed the previous remark, this function would
> collapse into two rlist_del, it is not. The same about the
> next function. Btw, these functions are identical. Can you
> keep only one?
They don’t seem to de identical: on_create rollback just deletes new entry,
but on_replace - deletes new entry and inserts old one.
> Same about on_drop_fkey_commit and on_replace_fkey_commit.
Ok:
-/** Release memory for old foreign key. */
-static void
-on_replace_fkey_commit(struct trigger *trigger, void *event)
-{
- (void) event;
- struct fkey *fk = (struct fkey *)trigger->data;
- fkey_delete(fk);
-}
-
/** On rollback of drop simply return back FK to DD. */
static void
on_drop_fkey_rollback(struct trigger *trigger, void *event)
@@ -3674,11 +3665,12 @@ on_drop_fkey_rollback(struct trigger *trigger, void *event)
}
/**
- * On commit of drop we have already deleted foreign key from
- * both (parent's and child's) lists, so just release memory.
+ * On commit of drop or replace we have already deleted old
+ * foreign key entry from both (parent's and child's) lists,
+ * so just release memory.
*/
static void
-on_drop_fkey_commit(struct trigger *trigger, void *event)
+on_drop_or_replace_fkey_commit(struct trigger *trigger, void *event)
@@ -3801,7 +3793,7 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
fkey);
txn_on_rollback(txn, on_rollback);
struct trigger *on_commit =
- txn_alter_trigger_new(on_replace_fkey_commit,
+ txn_alter_trigger_new(on_drop_or_replace_fkey_commit,
old_fk);
txn_on_commit(txn, on_commit);
}
@@ -3821,7 +3813,8 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
fkey_list_replace(child_space, parent_space, fk_def->name, NULL,
&old_fkey);
struct trigger *on_commit =
- txn_alter_trigger_new(on_drop_fkey_commit, old_fkey);
+ txn_alter_trigger_new(on_drop_or_replace_fkey_commit,
+ old_fkey);
>> + struct index *pk = space_index(child_space, 0);
>> + if (index_count(pk, ITER_ALL, NULL, 0) > 0) {
>
> 7. Lets better use index_size. Index_count on Vinyl takes O(N) time
> scanning disk. I think, it is ok to forbid new fk on a Vinyl space,
> that logically is empty, but actually still contains some non-compacted
> garbage. Anyway user now is able to force the compaction and try to
> create fk again.
Ok, fair enough:
@@ -3722,7 +3714,7 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
* checks on existing data in space.
*/
struct index *pk = space_index(child_space, 0);
- if (index_count(pk, ITER_ALL, NULL, 0) > 0) {
+ if (index_size(pk) > 0) {
>
>> + tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
>> + fk_def->name,
>> + "referencing space must be empty");
>> + }
>> + /* Check types of referenced fields. */
>> + for (uint32_t i = 0; i < fk_def->field_count; ++i) {
>> + uint32_t child_fieldno = fk_def->links[i].child_field;
>> + uint32_t parent_fieldno = fk_def->links[i].parent_field;
>> + if (child_fieldno >= child_space->def->field_count ||
>> + parent_fieldno >= parent_space->def->field_count) {
>> + tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
>> + fk_def->name, "foreign key refers to "
>> + "nonexistent field");
>> + }
>> + if (child_space->def->fields[child_fieldno].type !=
>> + parent_space->def->fields[parent_fieldno].type) {
>> + tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
>> + fk_def->name, "field type mismatch");
>> + }
>> + if (child_space->def->fields[child_fieldno].coll_id !=
>> + parent_space->def->fields[parent_fieldno].coll_id) {
>> + tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
>> + fk_def->name,
>> + "field collation mismatch");
>> + }
>> + }
>> + /*
>> + * Search for suitable index in parent space:
>> + * it must be unique and consist exactly from
>> + * referenced columns (but order may be different).
>
> 8. Should this index has the same collations? For a column we can define
> multiple collations in different indexes. And in one index the value may
> be unique, but in another may be not.
No, they (indexes) may have different collations. The only requirement
for index is uniqueness. I am not completely sure about the fact that
field collations must match: what ANSI says I linked at the beginning of
this letter. I guess it seems to be dubious question.
>
>> + */
>> + struct index *fk_index = NULL;
>> + for (uint32_t i = 0; i < parent_space->index_count; ++i) {
>> + struct index *idx = space_index(parent_space, i);
>> + if (!idx->def->opts.is_unique)
>> + continue;
>> + if (idx->def->key_def->part_count !=
>> + fk_def->field_count)
>> + continue;
>> + uint32_t j;
>> + for (j = 0; j < fk_def->field_count; ++j) {
>> + if (idx->def->key_def->parts[j].fieldno !=
>> + fk_def->links[j].parent_field)
>> + break;
>> + }
>> + if (j != fk_def->field_count)
>> + continue;
>
> 9. See key_def_find.
@@ -3760,12 +3829,13 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
if (!idx->def->opts.is_unique)
continue;
if (idx->def->key_def->part_count !=
fk_def->field_count)
continue;
uint32_t j;
for (j = 0; j < fk_def->field_count; ++j) {
- if (idx->def->key_def->parts[j].fieldno !=
- fk_def->links[j].parent_field)
+ if (key_def_find(idx->def->key_def,
+ fk_def->links[j].parent_field)
+ == NULL)
break;
>
> 10. Above you have said that order may be different, but here I see
> that once you have found a unique index, you merely check that it
> has sequentially the same field numbers as the fk.
For some reason I forgot to fix this when was preparing patch.
Fixed by now. Also, according to ANSI (11.8 4.a):
• Each referenced column shall identify a column of the referenced table and the same column shall not be identi ed more than once.
@@ -3710,6 +3710,41 @@ on_drop_or_replace_fkey_commit(struct trigger *trigger, void *event)
fkey_delete(fk);
}
+static int
+cmp_uint32(const void *_a, const void *_b)
+{
+ const uint32_t *a = (const uint32_t *) _a, *b = (const uint32_t *) _b;
+ if (*a == *b)
+ return 0;
+ return (*a > *b) ? 1 : -1;
+}
+
+/**
+ * ANSI SQL doesn't allow list of referenced fields to contain
+ * duplicates.
+ */
+static int
+fkey_links_check_duplicates(struct fkey_def *fk_def)
+{
+ uint32_t *parent_fields =
+ (uint32_t *) region_alloc(&fiber()->gc, fk_def->field_count);
+ if (parent_fields == NULL) {
+ tnt_raise(OutOfMemory, fk_def->field_count, "region",
+ "parent_fields");
+ }
+ for (uint32_t i = 0; i < fk_def->field_count; ++i)
+ parent_fields[i] = fk_def->links[i].parent_field;
+ qsort(parent_fields, fk_def->field_count, sizeof(*parent_fields),
+ cmp_uint32);
+ uint32_t prev_val = parent_fields[0];
+ for (uint32_t i = 1; i < fk_def->field_count; ++i) {
+ if (prev_val == parent_fields[i])
+ return -1;
+ prev_val = parent_fields[i];
+ }
+ return 0;
+}
+
on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
@@ -3777,6 +3812,11 @@ on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
"field collation mismatch");
}
}
+ if (fkey_links_check_duplicates(fk_def)) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name, "referenced fields can not "
+ "contain duplicates");
+ }
Probably this is not best implementation. As an improvement I can add kind of optimisation:
*pseudo code below*
uint_64 mask;
for parent_field in fk_def:
if (pk_mask & (((uint64_t) 1) << parent_field ==1)
return -1;
column_mask_set_field(&mask, parent_field);
end
if (pk_mask & (((uint64_t) 1) << 63)) != 0
fkey_links_check_duplicates(…) // Full version of checking.
else
return 1;
Is it worth the effort? I mean here we waste O(field_count) in case the largest
filedno in parent table > 63. On the other hand, I don’t think that many users
have tables with field count > 63, so it is likely to be reasonable.
Moreover, it would be cool if column_mask_set_fieldno() worked the same
as bit_set() from lib/bit, i.e. returned previous bit value.
And very simple test:
@@ -71,6 +71,11 @@ box.space._fk_constraint:insert(t)
+-- Each referenced column must appear once.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}, {child = 1, parent = 1}}}
+box.space._fk_constraint:insert(t)
+
>
>> + fk_index = idx;
>> + break;
>> + }
>> + if (fk_index == NULL) {
>> + tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
>> + fk_def->name, "referenced fields don't "
>> + "compose unique index");
>> + }
>> + struct fkey *fkey = (struct fkey *) malloc(sizeof(*fkey));
>
> 11. I know how you like memory mapping. How about merge fkey_def into
> fkey memory? I see, that it is relatively simple and linear thing.
What is the point of doing this? Do you suggest to allocate enough memory for fkey
right in fkey_def_new_from_tuple() ? Like this:
@@ -3536,7 +3536,8 @@ fkey_def_new_from_tuple(const struct tuple *tuple, uint32_t errcode)
struct field_link *links = fkey_links_decode(links_raw, &link_count,
name, name_len, errcode);
size_t fkey_sz = fkey_def_sizeof(link_count, name_len);
- struct fkey_def *fk_def = (struct fkey_def *) malloc(fkey_sz);
+ struct fkey_def *fk_def = (struct fkey_def *) malloc(fkey_sz +
+ sizeof(struct fkey);
If so, it would be quite confusing I think (since function returns only
fkey_def, but memory would be allocated for fkey_def + fkey).
If you mean smth else or insist on this change, I will fix it.
>> diff --git a/src/box/fkey.c b/src/box/fkey.c
>> new file mode 100644
>> index 000000000..e45889a0d
>> --- /dev/null
>> +++ b/src/box/fkey.c
>> +void
>> +fkey_trigger_delete(struct sqlite3 *db, struct sql_trigger *p)
>> +{
>
> 12. Why do you need this function when sql_trigger_delete exists?
Memory layout of FK trigger differs from ordinary one (from sql/fkey.c):
size_t trigger_size = sizeof(struct sql_trigger) +
sizeof(TriggerStep) + nFrom + 1;
trigger =
(struct sql_trigger *)sqlite3DbMallocZero(db,
trigger_size);
One can see, memory for TriggerStep, sql_trigger and name of
target table is allocated in one chunk. Thus, fkey_trigger_delete()
doesn’t release memory for TriggerStep. Overall, if compare
these functions fkey_trigger_delete() looks much simpler.
>
>> + if (p != NULL) {
>> + struct TriggerStep *step = p->step_list;
>> + sql_expr_delete(db, step->pWhere, false);
>> + sql_expr_list_delete(db, step->pExprList);
>> + sql_select_delete(db, step->pSelect);
>> + sql_expr_delete(db, p->pWhen, false);
>> + sqlite3DbFree(db, p);
>> + }
>> +}
>> +
>> +#include <stdbool.h>
>> +#include <stdint.h>
>> +
>> +#include "space.h"
>> +
>> +#if defined(__cplusplus)
>> +extern "C" {
>> +#endif /* defined(__cplusplus) */
>> +
>> +struct sqlite3;
>
> 13. Is it possible to drop this announcement after
> you replaced fkey_trigger_delete with sql_trigger_delete?
See reply above. I can struggle with adopting sql_trigger_delete
for FK triggers, but is it worth the effort?
>
>> diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua
>> index 87c79bdde..30d8b0081 100644
>> --- a/src/box/lua/schema.lua
>> +++ b/src/box/lua/schema.lua
>> @@ -506,6 +506,7 @@ box.schema.space.drop = function(space_id, space_name, opts)
>> local _vindex = box.space[box.schema.VINDEX_ID]
>> local _truncate = box.space[box.schema.TRUNCATE_ID]
>> local _space_sequence = box.space[box.schema.SPACE_SEQUENCE_ID]
>> + local _fk_constraint = box.space[box.schema.FK_CONSTRAINT_ID]
>> local sequence_tuple = _space_sequence:delete{space_id}
>> if sequence_tuple ~= nil and sequence_tuple[3] == true then
>> -- Delete automatically generated sequence.
>> @@ -519,6 +520,11 @@ box.schema.space.drop = function(space_id, space_name, opts)
>> local v = keys[i]
>> _index:delete{v[1], v[2]}
>> end
>> + for _, t in _fk_constraint.index.primary:pairs() do
>> + if t.child_id == space_id then
>
> 14. Fullscan here looks bad. Can we create a secondary index on
> _fk_constraint by child_id?
Well, lets add secondary index on child_id field:
@@ -525,6 +525,10 @@ local function upgrade_to_2_1_0()
_index:insert{box.schema.FK_CONSTRAINT_ID, 0, 'primary', 'tree',
{unique = true}, {{0, 'string'}, {1, 'unsigned'}}}
+ log.info("create secondary index child_id on _fk_constraint")
+ _index:insert{box.schema.FK_CONSTRAINT_ID, 1, 'child_id', 'tree',
+ {unique = false}, {{1, 'unsigned'}}}
+
+++ b/src/box/lua/schema.lua
@@ -520,10 +520,8 @@ box.schema.space.drop = function(space_id, space_name, opts)
local v = keys[i]
_index:delete{v[1], v[2]}
end
- for _, t in _fk_constraint:pairs() do
- if t.child_id == space_id then
- _fk_constraint:delete{t.name, t.child_id}
- end
+ for _, t in _fk_constraint.index.child_id:pairs({space_id}) do
+ _fk_constraint:delete({t.name, space_id})
end
> Or create a Lua C function, that takes
> space and returns names of its child fks? If we use the index, then
> we are able to optimize VDBE deletion of the FKs in the main patch
> (sql: introduce ADD CONSTRAINT statement), where you delete by
> the composite key {name, child_id}, but actually child_id is enough.
>
>> + _fk_constraint:delete{t.name, t.child_id}
>> + end
>> + end
>> diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
>> index f112a93ae..772f55cb2 100644
>> --- a/src/box/lua/upgrade.lua
>> +++ b/src/box/lua/upgrade.lua
>> @@ -509,6 +509,22 @@ local function upgrade_to_2_1_0()
>> {unique = true}, {{0, 'string'}, {1, 'string'},
>> {5, 'scalar'}}}
>> + local fk_constr_ft = {{name='name', type='string'},
>> + {name='child_id', type='unsigned'},
>> + {name='parent_id', type='unsigned'},
>> + {name='deferred', type='boolean'},
>
> 15. Flag, so 'is_deferred', it is not?
Surely:
- {name='deferred', type='boolean'},
+ {name='is_deferred', type='boolean'},
>
>> + {name='match', type='string'},
>> + {name='on_delete', type='string'},
>> + {name='on_update', type='string'},
>> + {name='links', type='array'}}
>> + log.info("create space _fk_constraint")
>> + _space:insert{box.schema.FK_CONSTRAINT_ID, ADMIN, '_fk_constraint', 'memtx',
>> + 0, setmap({}), fk_constr_ft}
>> +
>> + log.info("create index primary on _fk_constraint")
>> + _index:insert{box.schema.FK_CONSTRAINT_ID, 0, 'primary', 'tree',
>> + {unique = true}, {{0, 'string'}, {1, 'unsigned'}}}
>> +
>> -- Nullability wasn't skipable. This was fixed in 1-7.
>> -- Now, abscent field means NULL, so we can safely set second
>> -- field in format, marking it nullable.
>> diff --git a/src/box/space.h b/src/box/space.h
>> index 7da2ee51f..fc5e8046f 100644
>> --- a/src/box/space.h
>> +++ b/src/box/space.h
>> @@ -183,6 +183,9 @@ struct space {
>> * of index id.
>> */
>> struct index **index;
>> + /** Foreign key constraints. */
>
> 16. It would be good to have here an explanation about who is
> child, who is parent.
Ok:
+++ b/src/box/space.h
@@ -183,7 +183,13 @@ struct space {
* of index id.
*/
struct index **index;
- /** Foreign key constraints. */
+ /**
+ * Lists of foreign key constraints. In SQL terms parent
+ * space is the "from" table i.e. the table that contains
+ * the REFERENCES clause. Child space is "to" table, in
+ * other words the table that is named in the REFERENCES
+ * clause.
+ */
>
>> + struct fkey *parent_fkey;
>> + struct fkey *child_fkey;
>> };
>> diff --git a/test/box/access_misc.result b/test/box/access_misc.result
>> index 5a2563d55..62c92ca03 100644
>> --- a/test/box/access_misc.result
>> +++ b/test/box/access_misc.result
>> @@ -809,6 +809,11 @@ box.space._space:select()
>> - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
>> 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt', 'type': 'string'},
>> {'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type': 'scalar'}]]
>> + - [350, 1, '_fk_constraint', 'memtx', 0, {}, [{'name': 'name', 'type': 'string'},
>> + {'name': 'child_id', 'type': 'unsigned'}, {'name': 'parent_id', 'type': 'unsigned'},
>> + {'name': 'deferred', 'type': 'boolean'}, {'name': 'match', 'type': 'string'},
>> + {'name': 'on_delete', 'type': 'string'}, {'name': 'on_update', 'type': 'string'},
>> + {'name': 'links', 'type': 'array'}]]
>
> 17. Please, keep spare room after sql_stat4 in 8 identifiers for future needs. So as
> _fk_constraint was 356 (sql_stat1 348 + 8).
Ok:
diff --git a/src/box/schema_def.h b/src/box/schema_def.h
index 22621fc11..6022ea072 100644
--- a/src/box/schema_def.h
+++ b/src/box/schema_def.h
@@ -108,7 +108,7 @@ enum {
BOX_SQL_STAT1_ID = 348,
BOX_SQL_STAT4_ID = 349,
/** Space id of _fk_constraint. */
- BOX_FK_CONSTRAINT_ID = 350,
+ BOX_FK_CONSTRAINT_ID = 356,
Tests are fixed as well.
>
>> ...
>> box.space._func:select()
>> ---
>> diff --git a/test/engine/iterator.result b/test/engine/iterator.result
>> index a36761df8..ba9b0545a 100644
>> --- a/test/engine/iterator.result
>> +++ b/test/engine/iterator.result
>> @@ -4211,7 +4211,7 @@ s:replace{35}
>> ...
>> state, value = gen(param,state)
>> ---
>> -- error: 'builtin/box/schema.lua:1049: usage: next(param, state)'
>> +- error: 'builtin/box/schema.lua:1055: usage: next(param, state)'
>
> 18. This test fails on each schema change. Lets remove this file:line
> from the error message alongside the patch.
Ok:
diff --git a/test/engine/iterator.result b/test/engine/iterator.result
index 8cd067ebc..2204ff647 100644
--- a/test/engine/iterator.result
+++ b/test/engine/iterator.result
@@ -4207,18 +4207,6 @@ value
---
- null
...
-s:replace{35}
----
-- [35]
-...
-state, value = gen(param,state)
----
-- error: 'builtin/box/schema.lua:1055: usage: next(param, state)'
-...
-value
----
-- null
-...
s:drop()
---
...
diff --git a/test/engine/iterator.test.lua b/test/engine/iterator.test.lua
index fcf753f46..c48dbf1b8 100644
--- a/test/engine/iterator.test.lua
+++ b/test/engine/iterator.test.lua
@@ -399,9 +399,6 @@ value
gen,param,state = i:pairs({35})
state, value = gen(param,state)
value
-s:replace{35}
-state, value = gen(param,state)
-value
Full patch is below:
===============================================================================
Subject: [PATCH 2/5] schema: add new system space for FK constraints
This patch introduces new system space to persist foreign keys
constraints. Format of the space:
_fk_constraint (space id = 358)
[<contraint name> STR, <parent id> UINT, <child id> UINT,
<is deferred> BOOL, <match> STR, <on delete action> STR,
<on update action> STR, <field links> ARRAY<MAP< UINT : UINT >>]
FK constraint is local to space, so every pair <FK name, child id>
is unique (and it is PK in _fk_constraint space).
After insertion into this space, new instance describing FK constraint
is created. FK constraints are held in data-dictionary as two lists
(for child and parent constraints) in struct space.
There is a list of FK restrictions:
- At the time of FK creation parent and child spaces must exist;
- VIEWs can't be involved into FK processing;
- Child space must be empty;
- Types of referencing and referenced fields must be comparable;
- Collations of referencing and referenced fields must match;
- Referenced fields must compose unique index;
- Referenced fields can not contain duplicates.
Until space (child) features FK constraints it isn't allowed to be
dropped. Implicitly referenced index also can't be dropped
(and that is why parent space can't be dropped). But :drop() method
of child space firstly deletes all FK constraint (the same as SQL
triggers, indexes etc) and then removes entry from _space.
Part of #3271
---
src/box/CMakeLists.txt | 1 +
src/box/alter.cc | 455 ++++++++++++++++++++++++++++++++++++++++-
src/box/alter.h | 1 +
src/box/bootstrap.snap | Bin 1704 -> 1806 bytes
src/box/errcode.h | 2 +
src/box/fkey.c | 69 +++++++
src/box/fkey.h | 149 ++++++++++++++
src/box/lua/schema.lua | 4 +
src/box/lua/space.cc | 2 +
src/box/lua/upgrade.lua | 20 ++
src/box/schema.cc | 16 ++
src/box/schema_def.h | 14 ++
src/box/space.c | 4 +
src/box/space.h | 9 +
src/box/sql.c | 8 +
src/box/sql/fkey.c | 32 +--
src/box/sql/tarantoolInt.h | 1 +
test/box/access_misc.result | 5 +
test/box/access_sysview.result | 6 +-
test/box/alter.result | 6 +-
test/box/misc.result | 2 +
test/engine/iterator.result | 12 --
test/engine/iterator.test.lua | 3 -
test/sql/foreign-keys.result | 326 +++++++++++++++++++++++++++++
test/sql/foreign-keys.test.lua | 149 ++++++++++++++
25 files changed, 1246 insertions(+), 50 deletions(-)
create mode 100644 src/box/fkey.c
create mode 100644 src/box/fkey.h
create mode 100644 test/sql/foreign-keys.result
create mode 100644 test/sql/foreign-keys.test.lua
diff --git a/src/box/CMakeLists.txt b/src/box/CMakeLists.txt
index a467d3517..6dd2c75b9 100644
--- a/src/box/CMakeLists.txt
+++ b/src/box/CMakeLists.txt
@@ -92,6 +92,7 @@ add_library(box STATIC
space.c
space_def.c
sequence.c
+ fkey.c
func.c
func_def.c
alter.cc
diff --git a/src/box/alter.cc b/src/box/alter.cc
index 7b6bd1a5a..c5d1f75df 100644
--- a/src/box/alter.cc
+++ b/src/box/alter.cc
@@ -33,6 +33,7 @@
#include "user.h"
#include "space.h"
#include "index.h"
+#include "fkey.h"
#include "func.h"
#include "coll_id_cache.h"
#include "coll_id_def.h"
@@ -571,6 +572,14 @@ space_swap_triggers(struct space *new_space, struct space *old_space)
old_space->sql_triggers = new_value;
}
+/** The same as for triggers - swap lists of FK constraints. */
+static void
+space_swap_fkeys(struct space *new_space, struct space *old_space)
+{
+ rlist_swap(&new_space->child_fkey, &old_space->child_fkey);
+ rlist_swap(&new_space->parent_fkey, &old_space->parent_fkey);
+}
+
/**
* True if the space has records identified by key 'uid'.
* Uses 'iid' index.
@@ -781,9 +790,10 @@ alter_space_rollback(struct trigger *trigger, void * /* event */)
space_fill_index_map(alter->old_space);
space_fill_index_map(alter->new_space);
/*
- * Don't forget about space triggers.
+ * Don't forget about space triggers and foreign keys.
*/
space_swap_triggers(alter->new_space, alter->old_space);
+ space_swap_fkeys(alter->new_space, alter->old_space);
struct space *new_space = space_cache_replace(alter->old_space);
assert(new_space == alter->new_space);
(void) new_space;
@@ -879,9 +889,10 @@ alter_space_do(struct txn *txn, struct alter_space *alter)
space_fill_index_map(alter->old_space);
space_fill_index_map(alter->new_space);
/*
- * Don't forget about space triggers.
+ * Don't forget about space triggers and foreign keys.
*/
space_swap_triggers(alter->new_space, alter->old_space);
+ space_swap_fkeys(alter->new_space, alter->old_space);
/*
* The new space is ready. Time to update the space
* cache with it.
@@ -1742,6 +1753,18 @@ on_replace_dd_space(struct trigger * /* trigger */, void *event)
space_name(old_space),
"other views depend on this space");
}
+ /*
+ * No need to check existence of parent keys,
+ * since if we went so far, space would'n have
+ * any indexes. But referenced space has at least
+ * one referenced index which can't be dropped
+ * before constraint itself.
+ */
+ if (! rlist_empty(&old_space->child_fkey)) {
+ tnt_raise(ClientError, ER_DROP_SPACE,
+ space_name(old_space),
+ "the space has foreign key constraints");
+ }
/**
* The space must be deleted from the space
* cache right away to achieve linearisable
@@ -1940,6 +1963,21 @@ on_replace_dd_index(struct trigger * /* trigger */, void *event)
"can not add a secondary key before primary");
}
+ /*
+ * Can't drop index if foreign key constraints references
+ * this index.
+ */
+ if (old_index != NULL && new_tuple == NULL) {
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &old_space->parent_fkey, parent_link) {
+ if (fk->index_id == iid) {
+ tnt_raise(ClientError, ER_ALTER_SPACE,
+ space_name(old_space),
+ "can not drop referenced index");
+ }
+ }
+ }
+
struct alter_space *alter = alter_space_new(old_space);
auto scoped_guard =
make_scoped_guard([=] { alter_space_delete(alter); });
@@ -3459,6 +3497,415 @@ on_replace_dd_trigger(struct trigger * /* trigger */, void *event)
txn_on_commit(txn, on_commit);
}
+/**
+ * Decode MsgPack array of links. It consists from maps:
+ * {parent_id (UINT) : child_id (UINT)}.
+ *
+ * @param data MsgPack array of links.
+ * @param[out] out_count Count of links.
+ * @param constraint_name Constraint name to use in error
+ * messages.
+ * @param constraint_len Length of constraint name.
+ * @param errcode Errcode for client errors.
+ * @retval Array of links.
+ */
+static struct field_link *
+fkey_links_decode(const char *data, uint32_t *out_count,
+ const char *constraint_name, uint32_t constraint_len,
+ uint32_t errcode)
+{
+ assert(mp_typeof(*data) == MP_ARRAY);
+ uint32_t count = mp_decode_array(&data);
+ if (count == 0) {
+ tnt_raise(ClientError, errcode,
+ tt_cstr(constraint_name, constraint_len),
+ "at least one link must be specified");
+ }
+ *out_count = count;
+ size_t size = count * sizeof(struct field_link);
+ struct field_link *region_links =
+ (struct field_link *) region_alloc_xc(&fiber()->gc, size);
+ memset(region_links, 0, size);
+ const char **map = &data;
+ for (uint32_t i = 0; i < count; ++i) {
+ uint32_t map_sz = mp_decode_map(map);
+ if (map_sz != 2) {
+ tnt_raise(ClientError, errcode,
+ tt_cstr(constraint_name, constraint_len),
+ tt_sprintf("link must be map with 2 fields"));
+ }
+ for (uint8_t j = 0; j < map_sz; ++j) {
+ if (mp_typeof(**map) != MP_STR) {
+ tnt_raise(ClientError, errcode,
+ tt_cstr(constraint_name,
+ constraint_len),
+ tt_sprintf("link %d is not map "\
+ "with string keys", i));
+ }
+ uint32_t key_len;
+ const char *key = mp_decode_str(map, &key_len);
+ if (key_len == 6 &&
+ memcmp(key, "parent", key_len) == 0) {
+ region_links[i].parent_field =
+ mp_decode_uint(map);
+ } else if (key_len == 5 &&
+ memcmp(key, "child", key_len) == 0) {
+ region_links[i].child_field =
+ mp_decode_uint(map);
+ } else {
+ char *errmsg = tt_static_buf();
+ snprintf(errmsg, TT_STATIC_BUF_LEN,
+ "unexpected key of link %d '%.*s'", i,
+ key_len, key);
+ tnt_raise(ClientError, errcode,
+ tt_cstr(constraint_name,
+ constraint_len), errmsg);
+ }
+ }
+ }
+ return region_links;
+}
+
+/** Create an instance of foreign key def constraint from tuple. */
+static struct fkey_def *
+fkey_def_new_from_tuple(const struct tuple *tuple, uint32_t errcode)
+{
+ uint32_t name_len;
+ const char *name =
+ tuple_field_str_xc(tuple, BOX_FK_CONSTRAINT_FIELD_NAME,
+ &name_len);
+ if (name_len > BOX_NAME_MAX) {
+ tnt_raise(ClientError, errcode,
+ tt_cstr(name, BOX_INVALID_NAME_MAX),
+ "constraint name is too long");
+ }
+ identifier_check_xc(name, name_len);
+ const char *links_raw =
+ tuple_field_with_type_xc(tuple, BOX_FK_CONSTRAINT_FIELD_LINKS,
+ MP_ARRAY);
+ uint32_t link_count;
+ struct field_link *links = fkey_links_decode(links_raw, &link_count,
+ name, name_len, errcode);
+ size_t fkey_sz = fkey_def_sizeof(link_count, name_len);
+ struct fkey_def *fk_def = (struct fkey_def *) malloc(fkey_sz);
+ if (fk_def == NULL)
+ tnt_raise(OutOfMemory, fkey_sz, "malloc", "fk_def");
+ auto def_guard = make_scoped_guard([=] { free(fk_def); });
+ memcpy(fk_def->name, name, name_len);
+ fk_def->name[name_len] = '\0';
+ fk_def->links = (struct field_link *)((char *)&fk_def->name +
+ name_len + 1);
+ memcpy(fk_def->links, links, link_count * sizeof(struct field_link));
+ fk_def->field_count = link_count;
+ fk_def->child_id = tuple_field_u32_xc(tuple,
+ BOX_FK_CONSTRAINT_FIELD_CHILD_ID);
+ fk_def->parent_id =
+ tuple_field_u32_xc(tuple, BOX_FK_CONSTRAINT_FIELD_PARENT_ID);
+ fk_def->is_deferred =
+ tuple_field_bool_xc(tuple, BOX_FK_CONSTRAINT_FIELD_DEFERRED);
+ const char *match = tuple_field_str_xc(tuple,
+ BOX_FK_CONSTRAINT_FIELD_MATCH,
+ &name_len);
+ fk_def->match = STRN2ENUM(fkey_match, match, name_len);
+ if (fk_def->match == fkey_match_MAX) {
+ tnt_raise(ClientError, errcode, fk_def->name,
+ "unknown MATCH clause");
+ }
+ const char *on_delete_action =
+ tuple_field_str_xc(tuple, BOX_FK_CONSTRAINT_FIELD_ON_DELETE,
+ &name_len);
+ fk_def->on_delete = STRN2ENUM(fkey_action, on_delete_action, name_len);
+ if (fk_def->on_delete == fkey_action_MAX) {
+ tnt_raise(ClientError, errcode, fk_def->name,
+ "unknown ON DELETE action");
+ }
+ const char *on_update_action =
+ tuple_field_str_xc(tuple, BOX_FK_CONSTRAINT_FIELD_ON_UPDATE,
+ &name_len);
+ fk_def->on_update = STRN2ENUM(fkey_action, on_update_action, name_len);
+ if (fk_def->on_update == fkey_action_MAX) {
+ tnt_raise(ClientError, errcode, fk_def->name,
+ "unknown ON UPDATE action");
+ }
+ def_guard.is_active = false;
+ return fk_def;
+}
+
+/**
+ * Remove FK constraint from child's list.
+ * Entries in child list are supposed to be unique
+ * by their name.
+ *
+ * @param list List of child FK constraints.
+ * @param fkey_name Name of constraint to be removed.
+ * @retval FK being removed.
+ */
+static struct fkey *
+fkey_remove_child(struct rlist *list, const char *fkey_name)
+{
+ struct fkey *fk;
+ rlist_foreach_entry(fk, list, child_link) {
+ if (strcmp(fkey_name, fk->def->name) == 0) {
+ rlist_del_entry(fk, child_link);
+ return fk;
+ }
+ }
+ unreachable();
+ return NULL;
+}
+
+/**
+ * On rollback of creation we remove FK constraint from DD, i.e.
+ * from parent's and child's lists of constraints and
+ * release memory.
+ */
+static void
+on_create_fkey_rollback(struct trigger *trigger, void *event)
+{
+ (void) event;
+ struct fkey *fk = (struct fkey *)trigger->data;
+ rlist_del_entry(fk, parent_link);
+ rlist_del_entry(fk, child_link);
+ fkey_delete(fk);
+}
+
+/** Return old FK and release memory for the new one. */
+static void
+on_replace_fkey_rollback(struct trigger *trigger, void *event)
+{
+ (void) event;
+ struct fkey *fk = (struct fkey *)trigger->data;
+ struct space *parent = space_by_id(fk->def->parent_id);
+ struct space *child = space_by_id(fk->def->child_id);
+ struct fkey *old_fkey = fkey_remove_child(&child->child_fkey,
+ fk->def->name);
+ rlist_del_entry(old_fkey, parent_link);
+ fkey_delete(old_fkey);
+ rlist_add_entry(&child->child_fkey, fk, child_link);
+ rlist_add_entry(&parent->parent_fkey, fk, parent_link);
+}
+
+/** On rollback of drop simply return back FK to DD. */
+static void
+on_drop_fkey_rollback(struct trigger *trigger, void *event)
+{
+ (void) event;
+ struct fkey *fk_to_restore = (struct fkey *)trigger->data;
+ struct space *parent = space_by_id(fk_to_restore->def->parent_id);
+ 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);
+}
+
+/**
+ * On commit of drop or replace we have already deleted old
+ * foreign key entry from both (parent's and child's) lists,
+ * so just release memory.
+ */
+static void
+on_drop_or_replace_fkey_commit(struct trigger *trigger, void *event)
+{
+ (void) event;
+ struct fkey *fk = (struct fkey *)trigger->data;
+ fkey_delete(fk);
+}
+
+static int
+cmp_uint32(const void *_a, const void *_b)
+{
+ const uint32_t *a = (const uint32_t *) _a, *b = (const uint32_t *) _b;
+ if (*a == *b)
+ return 0;
+ return (*a > *b) ? 1 : -1;
+}
+
+/**
+ * ANSI SQL doesn't allow list of referenced fields to contain
+ * duplicates.
+ */
+static int
+fkey_links_check_duplicates(struct fkey_def *fk_def)
+{
+ uint32_t *parent_fields =
+ (uint32_t *) region_alloc(&fiber()->gc, fk_def->field_count);
+ if (parent_fields == NULL) {
+ tnt_raise(OutOfMemory, fk_def->field_count, "region",
+ "parent_fields");
+ }
+ for (uint32_t i = 0; i < fk_def->field_count; ++i)
+ parent_fields[i] = fk_def->links[i].parent_field;
+ qsort(parent_fields, fk_def->field_count, sizeof(*parent_fields),
+ cmp_uint32);
+ uint32_t prev_val = parent_fields[0];
+ for (uint32_t i = 1; i < fk_def->field_count; ++i) {
+ if (prev_val == parent_fields[i])
+ return -1;
+ prev_val = parent_fields[i];
+ }
+ return 0;
+}
+
+/** A trigger invoked on replace in the _fk_constraint space. */
+static void
+on_replace_dd_fk_constraint(struct trigger * /* trigger*/, void *event)
+{
+ struct txn *txn = (struct txn *) event;
+ txn_check_singlestatement_xc(txn, "Space _fk_constraint");
+ struct txn_stmt *stmt = txn_current_stmt(txn);
+ struct tuple *old_tuple = stmt->old_tuple;
+ struct tuple *new_tuple = stmt->new_tuple;
+ if (new_tuple != NULL) {
+ /* Create or replace foreign key. */
+ struct fkey_def *fk_def =
+ fkey_def_new_from_tuple(new_tuple,
+ ER_CREATE_FK_CONSTRAINT);
+ auto fkey_def_guard = make_scoped_guard([=] { free(fk_def); });
+ struct space *child_space =
+ space_cache_find_xc(fk_def->child_id);
+ if (child_space->def->opts.is_view) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name,
+ "referencing space can't be VIEW");
+ }
+ struct space *parent_space =
+ space_cache_find_xc(fk_def->parent_id);
+ if (parent_space->def->opts.is_view) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name,
+ "referenced space can't be VIEW");
+ }
+ /*
+ * FIXME: until SQL triggers are completely
+ * integrated into server (i.e. we are able to
+ * invoke triggers even if DML occurred via Lua
+ * interface), it makes no sense to provide any
+ * checks on existing data in space.
+ */
+ struct index *pk = space_index(child_space, 0);
+ if (index_size(pk) > 0) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name,
+ "referencing space must be empty");
+ }
+ /* Check types of referenced fields. */
+ for (uint32_t i = 0; i < fk_def->field_count; ++i) {
+ uint32_t child_fieldno = fk_def->links[i].child_field;
+ uint32_t parent_fieldno = fk_def->links[i].parent_field;
+ if (child_fieldno >= child_space->def->field_count ||
+ parent_fieldno >= parent_space->def->field_count) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name, "foreign key refers to "
+ "nonexistent field");
+ }
+ struct field_def child_field =
+ child_space->def->fields[child_fieldno];
+ struct field_def parent_field =
+ parent_space->def->fields[parent_fieldno];
+ if (! field_type1_contains_type2(parent_field.type,
+ child_field.type)) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name, "field type mismatch");
+ }
+ if (child_field.coll_id != parent_field.coll_id) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name,
+ "field collation mismatch");
+ }
+ }
+ if (fkey_links_check_duplicates(fk_def)) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name, "referenced fields can not "
+ "contain duplicates");
+ }
+ /*
+ * Search for suitable index in parent space:
+ * it must be unique and consist exactly from
+ * referenced columns (but order may be
+ * different).
+ */
+ struct index *fk_index = NULL;
+ for (uint32_t i = 0; i < parent_space->index_count; ++i) {
+ struct index *idx = space_index(parent_space, i);
+ if (!idx->def->opts.is_unique)
+ continue;
+ if (idx->def->key_def->part_count !=
+ fk_def->field_count)
+ continue;
+ uint32_t j;
+ for (j = 0; j < fk_def->field_count; ++j) {
+ if (key_def_find(idx->def->key_def,
+ fk_def->links[j].parent_field)
+ == NULL)
+ break;
+ }
+ if (j != fk_def->field_count)
+ continue;
+ fk_index = idx;
+ break;
+ }
+ if (fk_index == NULL) {
+ tnt_raise(ClientError, ER_CREATE_FK_CONSTRAINT,
+ fk_def->name, "referenced fields don't "
+ "compose unique index");
+ }
+ struct fkey *fkey = (struct fkey *) malloc(sizeof(*fkey));
+ if (fkey == NULL)
+ tnt_raise(OutOfMemory, sizeof(*fkey), "malloc", "fkey");
+ auto fkey_guard = make_scoped_guard([=] { free(fkey); });
+ memset(fkey, 0, sizeof(*fkey));
+ fkey->def = fk_def;
+ fkey->index_id = fk_index->def->iid;
+ if (old_tuple == NULL) {
+ rlist_add_entry(&child_space->child_fkey, fkey,
+ child_link);
+ rlist_add_entry(&parent_space->parent_fkey, fkey,
+ parent_link);
+ struct trigger *on_rollback =
+ txn_alter_trigger_new(on_create_fkey_rollback,
+ fkey);
+ txn_on_rollback(txn, on_rollback);
+ } else {
+ struct fkey *old_fk =
+ fkey_remove_child(&child_space->child_fkey,
+ fk_def->name);
+ rlist_del_entry(old_fk, parent_link);
+ rlist_add_entry(&child_space->child_fkey, fkey,
+ child_link);
+ rlist_add_entry(&parent_space->parent_fkey, fkey,
+ parent_link);
+ struct trigger *on_rollback =
+ txn_alter_trigger_new(on_replace_fkey_rollback,
+ fkey);
+ txn_on_rollback(txn, on_rollback);
+ struct trigger *on_commit =
+ txn_alter_trigger_new(on_drop_or_replace_fkey_commit,
+ old_fk);
+ txn_on_commit(txn, on_commit);
+ }
+ fkey_def_guard.is_active = false;
+ fkey_guard.is_active = false;
+ } else if (new_tuple == NULL && old_tuple != NULL) {
+ /* Drop foreign key. */
+ struct fkey_def *fk_def =
+ fkey_def_new_from_tuple(old_tuple,
+ ER_DROP_FK_CONSTRAINT);
+ auto fkey_guard = make_scoped_guard([=] { free(fk_def); });
+ struct space *child_space =
+ space_cache_find_xc(fk_def->child_id);
+ struct fkey *old_fkey =
+ fkey_remove_child(&child_space->child_fkey,
+ fk_def->name);
+ rlist_del_entry(old_fkey, parent_link);
+ struct trigger *on_commit =
+ txn_alter_trigger_new(on_drop_or_replace_fkey_commit,
+ old_fkey);
+ txn_on_commit(txn, on_commit);
+ struct trigger *on_rollback =
+ txn_alter_trigger_new(on_drop_fkey_rollback, old_fkey);
+ txn_on_rollback(txn, on_rollback);
+ }
+}
+
struct trigger alter_space_on_replace_space = {
RLIST_LINK_INITIALIZER, on_replace_dd_space, NULL, NULL
};
@@ -3523,4 +3970,8 @@ struct trigger on_replace_trigger = {
RLIST_LINK_INITIALIZER, on_replace_dd_trigger, NULL, NULL
};
+struct trigger on_replace_fk_constraint = {
+ RLIST_LINK_INITIALIZER, on_replace_dd_fk_constraint, NULL, NULL
+};
+
/* vim: set foldmethod=marker */
diff --git a/src/box/alter.h b/src/box/alter.h
index 8ea29c77b..4108fa47c 100644
--- a/src/box/alter.h
+++ b/src/box/alter.h
@@ -45,6 +45,7 @@ extern struct trigger on_replace_sequence;
extern struct trigger on_replace_sequence_data;
extern struct trigger on_replace_space_sequence;
extern struct trigger on_replace_trigger;
+extern struct trigger on_replace_fk_constraint;
extern struct trigger on_stmt_begin_space;
extern struct trigger on_stmt_begin_index;
extern struct trigger on_stmt_begin_truncate;
diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap
index a8a00ec29e7106afbd06294cf6ffe291f90a2e10..84e9bbdba82bc059acc4ec9c9c177bb83971285c 100644
GIT binary patch
delta 1802
zcmV+l2le=<4UP_w8Gko6FfC^@Gc#p0I5RY13Q2BrbYX5|WjY{ZG-76CHeoO=GdDLl
zEi^D>Gc93dF*GeXIXE^jFgQ6dFgP{}RzqxWV{1AfdwmKD)w&D1%?6VI&KhYGX{7)F
z0000ewJ-euP*pSlsz-PaNx&F!0w{{2D2k#e&ZBt<!h$K72!91&#*NtprjlM!-1=ZP
zpp_}9a+7swE5BRnBB9tA#bUCBx3Y$Euf!aWYT%boR}01SGNlyY0N4QQ00THnO%k_e
zyptnM#NQ~^$XDO5eZ~`fy~<pdH!wQ^lIbybEQ;fIhw6&}&Qi0=iz+ONrM^59eJNts
z`O<i1zP`xpTYp&7Ryyy_Yfrv94G3n0{`A(jPTYFd$FHrr)FLI_C;(@v`P#UZH)&(_
zmHyrN?MVLakOjb5Y8q*hBZZ4ueQ^A;E^SrTy>OhRCaqSFneTVml5m!qTWS%Q?+<R(
zDrcz);wC(uJP at Z8PKXleUzH-E{!Jv4D#S+$aViW;_<zHoWq;?T_}O5cOQrOR3lI at A
z$^#jeEIL*ISPB%ME_c5|l?MW6sd+=PTIJpEt?oSFEHz8mz15r-SKegG0cWZCs~58?
zZ&K*??}?~JqW`gLyjM0?-sC+iszGc&FG~zKOHEcUacn;??^!*UB%dxr*R9o>1)Qa3
z*`Fu*bblG%v(hM#bqO{m&pea;JgD=n)fog#Tz9C-9OxLlP$~rjoTaA5FOH*1{9e!c
z at OSo&J3oK0&aK)0)pwn3<abVEqQC5UeuZjG0iVw<{^B;*wN)4bcyD~-)*i#!3X9>{
zbvb?)6hDVVI<NHCl>CQDert6UJ1TZk)EpE!I)AC0)QOY{Sk0nJP!tKyQj>!rL5(Ph
zD5Xj$563%sA!<xDH$gQZXi795XQ>%P^P#hWb)*>&u$c}r9As)_XjmMxC~Sz)5R)MW
z1Iz^&3oxCvqnQc|qEbf{Ml!Wh;4C#?Vkj)A%9lM^m$f7RyVIykEs}LJ6N>08o){#{
ztA9tK{^EmIk3y`k<JP(NhYwz!iS%zw3V~u~eM`E|cz3SH?-mI!lnP4VEHyV4 at kh2W
zuYsbT^}L?ROhI5er~QC=CaU_Z)N$um?BRE)0vUnd at qYcQ%kvYWC>2Kp&QcTBaVM1L
zrBI%CLZwn9Q|U432J}oM6b{Y}$-^fwRDYR13|cCz%fS_f)SJTzu{=RiKuSMqd~`iz
zcvP4joTa9HsYR#NQT3Snr_1nWYpg@%*s^8Im5PJ2)Ev?0`(0U=w;P<LCICxotV7w@
zPnP_w6>5B0B!3fCj>e1QrhQpdm1M2jSm({qxdr00RvDb7W{FkEB41q8dBNiAP=CY6
z`@Cdyk9^_tE|zOb39~qf#H>%~$bMeZI{$KYRvPQP7 at Vc1P7WBeI)*yi?B}J8&CjTk
zQfsWEfl<L1AWr}ok{~W%Kz44m1r8QGD{6sVsNqy(5E_^P0003{0PzI}Dd&m?5`eHc
zilZ=&ff$HG5R3v7XMg|{gajI(T7NLK&*(tis#(}3cM84Ef9L2qdX63$r)TG_nJt(&
z9+cQ3<d~L!BP$RHkex6j^vBX^NixnpTZqAaiy9S%Cjxh2QPPH(XH%fy{?x|+<EYM9
z*|-6`k}L=(0H+uP4{MUHNHeoI=eNlmMrEEaxh3`H{tM_$^;DCA1)2y=`F|j=*29+K
zxTMM=5u#bDQmT7jTbo50>6WtT>)63nS1901qUYC685VoX<7Agp^XRamYRfK-atG>$
zdDCt*ck7UBtstvXD(1F8m(r8XHxTGJ7o at 5KP0+c3CT-qq++dYLF?Wj=y+#j`s3B#;
zANx at w-GRDc-n1LdZDwqUwtr0aYdk<7tTN+LKhYWpLD);Ye#;sR<%|6o2gu>E7DjDF
z&~sifvC<hTYXdAPVEP>og^PO0QiAg+^W(bKJsqn_MU~P|vhbX6q*sSH(r&xFfxS00
zD~UeKTppJuq{v`%cJSHQk?!c`xg?l4;GY?(qW%~ue||xJFp1Ff(0}FEgino4lDM6s
z*v%KzM|f6U$3~3MNC)>2<F-E87;c%V)v&4ja+I8-4=Y;4f3o|_Z6Vx#A<mDH&|TWF
zHkD~*4IL$1J|<Em at F<QQ;OJ9plR}L{zFF!MYZK6v>0&M2;`*p07C_6!*>hVDc4mZg
zdTb16b~fIzr_PYGb${V}5=n5PCIJ9rh`%m^&nNXa%EE&bwsR|C6=;&k*D5ZD)~WN|
z=89gDEnNm~lUJ{*|G%67kGx}%*h%l=Br2fVA0+<Tzw7l!sOGwd6}k`#0$M2yhhMFs
ss at gF<!|tr?9h5;e73isp{y|vRl6wscxQ+<GS7AL#a-tm75Cg643Sr(;MF0Q*
delta 1699
zcmV;U23+}$4yX-~8Gki5FfC^@G-fw8H#rJPZgX^DZewLSAYx=OVKg#1V=Xo^Ff=VR
zH#adYIA$<6Eiz^~VK^`|F)=qdI0{xnY;R+0Iv{&}3JTS_3%bn)FaXY~@A2EE00000
z04TLD{QyusF#sw<untMk7&id`Up&AM2ap_8K2ct%)hPuc5r2u%1Dtdthk at FZnGtQL
zq{+<`lO*YB%N4E_fREI4wd=GkVz`4fdcG}`x2rRP>+&h36w?690OJ7SjC}R|`e!^<
zufxoBSySaEK}34Y9gE@!en$00z_rw*@?r{$VyQo#kG>SK>wIY(Gha_6_ARVsq0YPW
z*^{p>3xXA)K!3gUtrNGN1 at ezpT`H224JN>~)O>B+%A2IIdZ>SQUNe%x&&UF}mYPMF
z<VfKnR$n!OS(mg*w7uMGsY$EVW9Iu^v?SM3GpQn}@&|-lvwAHxzsossplA9N4p%a9
zo>%g6a!e(V!@(sGEAUSy(+NYC{hgO0NP}%ImC`FNL4Q!RY*}%n3YHx!04xm(FRr{v
zl!I%j`QeM%l{Z=Rb?}Fz#-snSYrI36D{t~UtJh#?KQB85*HV+!OFXonm)}`Amn5Jr
zSFUeXZ5CWh&9XmF0_t-0owY^*(UxFi^2{+JpjYYq%_<FoC9cn?&K&4i^*yN+3|vc1
zjb9wclz#}mo&{p?>>GD}0%4t7vHjt9ooVEEE?c6%^m+ajRha at mpI!XLX|9h}V+i2A
z at rhe|T+Ko(uFkH at 5&Tr*=hczUq5heY2QkUNS%n#JEj3Bpit$d4G7*DgL3m(5TGT2F
z94mTMRGt((D5}nmjt=Ss$^@zeY-T+Y)EjXvHGeM*i6^2%bzXPkLU15BlTD5dj*v}P
z!_|DamYPB}H(Lp|d7$Y4qv0 at PGgC9OfyFV4f(DrkG8kko#8`;gVrZ;|sZe1;Q!301
z424=>WUi&=%LED&qVlCr*5%B||L!vCQjv%@8VT1 at b7K*MWDE0|O4Ko**D)efkeJS8
zK!2(n6H`6bb=>(U`tZ-F0)bI|$NTk%AJ0yXpi~?YuB9fdV^Ej7e?_$i!nM?_Aqm60
z`&X+x53Z$V3HxeQC!Tlex*X2Ks1;7>#I3wicRHC$CJrY=2}I$TaHmRP$WmdOj*tpL
zDiEFf!}j<<_2_um?AUb7<ft(?uBE1ZseeePy;1d;JE+T5&DNpKvyqgPbfwm~mYOj1
z`F>Z{WsSzQ)C6E at 9ooDZ`^l1 at wSo*Vi{$U4dSkpeZW)-xR7uXN9oje;I=58#to6mU
z)GQIMSmcX~J1<y(9cuXapO=izkuQGU#d1mMFpHN+%mRyy?B}Jd^Do1*(xHv(xqp_L
zJ2_y)>bTPRW<M`)Zh*#Alv;<j8W#azNCJUKsNq;-5DClx0Du4}0PzI}Dd&m?5`eHc
zio-CDff$BE0E|KtM?fGb2njSmwO}-3>|otvVxbn$nEy|s!{{(Nj1CZ|XWOmW^1O_Q
z#C9R1B6~4P0)Yit2t#sze0O3}a)0*OYw7YYtf)91`JUH`Pa9%0YaYejkXxhcI3*z3
zdE&uuxjDLwcwchr;ms`(kQeo&CcC+u2o3ol at WqTwal~b%ln5QO6oL_ZE4HeMEh(My
z_v_fe^><ak7er6BFLY%562{3Rr^e{WMg>zAXfXbi)iZOl46U*mTj66G#((TQBk0n5
z@)<oY4g-ONQth~(3rms{8<`ta`P_f=wD_ at mkOU1mHT1wuY%u<m)iZOl3{AjWf3|$~
zt8m|QXkeD0hZ at Msd-hKiZ0KL;Q at khOrOoi`UOgQ>poLS)q+!uN;jpegcC;P#`N7`U
znw9D$>kp61-&FJnXm;?~*ng4k=;HZ8FhRgSHd00X7<B%WiF{!a-SZ&o*D9aNO_EGI
zB{7vRtd9oyxDE<2a=|>f+87shpvG{^Ol^j3&MJS%IeJ)8Cti{*Ub%&E>I-oWh=lIa
zrQD!Ql+Ehs<+5HPA%914?C?i#t&Krytl`a4o=ls7UrZNkEfZHnq<_5tDm2be+j_v6
zk)6|HU_kR{(H-|;3^`j1=aVc3C;Vs%FuC|kE%^FTe<LpZkFbK<maIKZW_(1&;Gu~+
zlWeroE4jI4;5K>1vid)s6X20|EK)(~T|9{xS1W;}zgF#f{gJ7;9%W`;4+U&31%|V|
tLZAw at V?4uhT=^Z8O&m3K=&6$aL0H$8dkzbvjtIq9$$FA7L^;(Et?f_{Enxrv
diff --git a/src/box/errcode.h b/src/box/errcode.h
index b61b387f2..213a1864b 100644
--- a/src/box/errcode.h
+++ b/src/box/errcode.h
@@ -219,6 +219,8 @@ struct errcode_record {
/*164 */_(ER_NO_SUCH_GROUP, "Replication group '%s' does not exist") \
/*165 */_(ER_NO_SUCH_MODULE, "Module '%s' does not exist") \
/*166 */_(ER_NO_SUCH_COLLATION, "Collation '%s' does not exist") \
+ /*167 */_(ER_CREATE_FK_CONSTRAINT, "Failed to create foreign key constraint '%s': %s") \
+ /*168 */_(ER_DROP_FK_CONSTRAINT, "Failed to drop foreign key constraint '%s': %s") \
/*
* !IMPORTANT! Please follow instructions at start of the file
diff --git a/src/box/fkey.c b/src/box/fkey.c
new file mode 100644
index 000000000..b3980c874
--- /dev/null
+++ b/src/box/fkey.c
@@ -0,0 +1,69 @@
+/*
+ * Copyright 2010-2018, Tarantool AUTHORS, please see AUTHORS file.
+ *
+ * Redistribution and use in source and binary forms, with or
+ * without modification, are permitted provided that the following
+ * conditions are met:
+ *
+ * 1. Redistributions of source code must retain the above
+ * copyright notice, this list of conditions and the
+ * following disclaimer.
+ *
+ * 2. Redistributions in binary form must reproduce the above
+ * copyright notice, this list of conditions and the following
+ * disclaimer in the documentation and/or other materials
+ * provided with the distribution.
+ *
+ * THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ``AS IS'' AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
+ * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
+ * <COPYRIGHT HOLDER> OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
+ * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
+ * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
+ * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
+ * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
+ * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
+ * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+ * SUCH DAMAGE.
+ */
+#include "fkey.h"
+#include "sql.h"
+#include "sql/sqliteInt.h"
+
+const char *fkey_action_strs[] = {
+ /* [FKEY_ACTION_RESTRICT] = */ "no_action",
+ /* [FKEY_ACTION_SET_NULL] = */ "set_null",
+ /* [FKEY_ACTION_SET_DEFAULT] = */ "set_default",
+ /* [FKEY_ACTION_CASCADE] = */ "cascade",
+ /* [FKEY_ACTION_NO_ACTION] = */ "restrict"
+};
+
+const char *fkey_match_strs[] = {
+ /* [FKEY_MATCH_SIMPLE] = */ "simple",
+ /* [FKEY_MATCH_PARTIAL] = */ "partial",
+ /* [FKEY_MATCH_FULL] = */ "full"
+};
+
+void
+fkey_trigger_delete(struct sqlite3 *db, struct sql_trigger *p)
+{
+ if (p != NULL) {
+ struct TriggerStep *step = p->step_list;
+ sql_expr_delete(db, step->pWhere, false);
+ sql_expr_list_delete(db, step->pExprList);
+ sql_select_delete(db, step->pSelect);
+ sql_expr_delete(db, p->pWhen, false);
+ sqlite3DbFree(db, p);
+ }
+}
+
+void
+fkey_delete(struct fkey *fkey)
+{
+ fkey_trigger_delete(sql_get(), fkey->on_delete_trigger);
+ fkey_trigger_delete(sql_get(), fkey->on_update_trigger);
+ free(fkey->def);
+ free(fkey);
+}
diff --git a/src/box/fkey.h b/src/box/fkey.h
new file mode 100644
index 000000000..0d537b1a7
--- /dev/null
+++ b/src/box/fkey.h
@@ -0,0 +1,149 @@
+#ifndef TARANTOOL_BOX_FKEY_H_INCLUDED
+#define TARANTOOL_BOX_FKEY_H_INCLUDED
+/*
+ * Copyright 2010-2018, Tarantool AUTHORS, please see AUTHORS file.
+ *
+ * Redistribution and use in source and binary forms, with or
+ * without modification, are permitted provided that the following
+ * conditions are met:
+ *
+ * 1. Redistributions of source code must retain the above
+ * copyright notice, this list of conditions and the
+ * following disclaimer.
+ *
+ * 2. Redistributions in binary form must reproduce the above
+ * copyright notice, this list of conditions and the following
+ * disclaimer in the documentation and/or other materials
+ * provided with the distribution.
+ *
+ * THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ``AS IS'' AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
+ * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
+ * <COPYRIGHT HOLDER> OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
+ * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
+ * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
+ * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
+ * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
+ * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
+ * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+ * SUCH DAMAGE.
+ */
+#include <stdbool.h>
+#include <stdint.h>
+
+#include "space.h"
+
+#if defined(__cplusplus)
+extern "C" {
+#endif /* defined(__cplusplus) */
+
+struct sqlite3;
+
+enum fkey_action {
+ FKEY_NO_ACTION = 0,
+ FKEY_ACTION_SET_NULL,
+ FKEY_ACTION_SET_DEFAULT,
+ FKEY_ACTION_CASCADE,
+ FKEY_ACTION_RESTRICT,
+ fkey_action_MAX
+};
+
+enum fkey_match {
+ FKEY_MATCH_SIMPLE = 0,
+ FKEY_MATCH_PARTIAL,
+ FKEY_MATCH_FULL,
+ fkey_match_MAX
+};
+
+extern const char *fkey_action_strs[];
+
+extern const char *fkey_match_strs[];
+
+/** Structure describing field dependencies for foreign keys. */
+struct field_link {
+ uint32_t parent_field;
+ uint32_t child_field;
+};
+
+/** Definition of foreign key constraint. */
+struct fkey_def {
+ /** Id of space containing the REFERENCES clause (child). */
+ uint32_t child_id;
+ /** Id of space that the key points to (parent). */
+ uint32_t parent_id;
+ /** Number of fields in this key. */
+ uint32_t field_count;
+ /** True if constraint checking is deferred till COMMIT. */
+ bool is_deferred;
+ /** Match condition for foreign key. SIMPLE by default. */
+ enum fkey_match match;
+ /** ON DELETE action. NO ACTION by default. */
+ enum fkey_action on_delete;
+ /** ON UPDATE action. NO ACTION by default. */
+ enum fkey_action on_update;
+ /** Mapping of fields in child to fields in parent. */
+ struct field_link *links;
+ /** Name of the constraint. */
+ char name[0];
+};
+
+/** Structure representing foreign key relationship. */
+struct fkey {
+ struct fkey_def *def;
+ /** Index id of referenced index in parent space. */
+ uint32_t index_id;
+ /** Triggers for actions. */
+ struct sql_trigger *on_delete_trigger;
+ struct sql_trigger *on_update_trigger;
+ /** Links for parent and child lists. */
+ struct rlist parent_link;
+ struct rlist child_link;
+};
+
+/**
+ * Alongside with struct fkey_def itself, we reserve memory for
+ * string containing its name and for array of links.
+ * Memory layout:
+ * +-------------------------+ <- Allocated memory starts here
+ * | struct fkey_def |
+ * |-------------------------|
+ * | name + \0 |
+ * |-------------------------|
+ * | links |
+ * +-------------------------+
+ */
+static inline size_t
+fkey_def_sizeof(uint32_t links_count, uint32_t name_len)
+{
+ return sizeof(struct fkey) + links_count * sizeof(struct field_link) +
+ name_len + 1;
+}
+
+static inline bool
+fkey_is_self_referenced(const struct fkey_def *fkey)
+{
+ return fkey->child_id == fkey->parent_id;
+}
+
+/**
+ * The second argument is a Trigger structure allocated by the
+ * fkActionTrigger() routine.This function deletes the Trigger
+ * structure and all of its sub-components.
+ *
+ * @param db Database handler.
+ * @param p Trigger to be freed.
+ */
+void
+fkey_trigger_delete(struct sqlite3 *db, struct sql_trigger *p);
+
+/** Release memory for foreign key and its triggers, if any. */
+void
+fkey_delete(struct fkey *fkey);
+
+#if defined(__cplusplus)
+} /* extern "C" */
+#endif /* __cplusplus */
+
+#endif /* TARANTOOL_BOX_FKEY_H_INCLUDED */
diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua
index d14dd748b..b73d9ab78 100644
--- a/src/box/lua/schema.lua
+++ b/src/box/lua/schema.lua
@@ -508,6 +508,7 @@ box.schema.space.drop = function(space_id, space_name, opts)
local _vindex = box.space[box.schema.VINDEX_ID]
local _truncate = box.space[box.schema.TRUNCATE_ID]
local _space_sequence = box.space[box.schema.SPACE_SEQUENCE_ID]
+ local _fk_constraint = box.space[box.schema.FK_CONSTRAINT_ID]
local sequence_tuple = _space_sequence:delete{space_id}
if sequence_tuple ~= nil and sequence_tuple[3] == true then
-- Delete automatically generated sequence.
@@ -521,6 +522,9 @@ box.schema.space.drop = function(space_id, space_name, opts)
local v = keys[i]
_index:delete{v[1], v[2]}
end
+ for _, t in _fk_constraint.index.child_id:pairs({space_id}) do
+ _fk_constraint:delete({t.name, space_id})
+ end
revoke_object_privs('space', space_id)
_truncate:delete{space_id}
if _space:delete{space_id} == nil then
diff --git a/src/box/lua/space.cc b/src/box/lua/space.cc
index ca3fefc0d..d07560d6c 100644
--- a/src/box/lua/space.cc
+++ b/src/box/lua/space.cc
@@ -551,6 +551,8 @@ box_lua_space_init(struct lua_State *L)
lua_setfield(L, -2, "SQL_STAT1_ID");
lua_pushnumber(L, BOX_SQL_STAT4_ID);
lua_setfield(L, -2, "SQL_STAT4_ID");
+ lua_pushnumber(L, BOX_FK_CONSTRAINT_ID);
+ lua_setfield(L, -2, "FK_CONSTRAINT_ID");
lua_pushnumber(L, BOX_TRUNCATE_ID);
lua_setfield(L, -2, "TRUNCATE_ID");
lua_pushnumber(L, BOX_SEQUENCE_ID);
diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
index f112a93ae..a6d6980c6 100644
--- a/src/box/lua/upgrade.lua
+++ b/src/box/lua/upgrade.lua
@@ -509,6 +509,26 @@ local function upgrade_to_2_1_0()
{unique = true}, {{0, 'string'}, {1, 'string'},
{5, 'scalar'}}}
+ local fk_constr_ft = {{name='name', type='string'},
+ {name='child_id', type='unsigned'},
+ {name='parent_id', type='unsigned'},
+ {name='is_deferred', type='boolean'},
+ {name='match', type='string'},
+ {name='on_delete', type='string'},
+ {name='on_update', type='string'},
+ {name='links', type='array'}}
+ log.info("create space _fk_constraint")
+ _space:insert{box.schema.FK_CONSTRAINT_ID, ADMIN, '_fk_constraint', 'memtx',
+ 0, setmap({}), fk_constr_ft}
+
+ log.info("create index primary on _fk_constraint")
+ _index:insert{box.schema.FK_CONSTRAINT_ID, 0, 'primary', 'tree',
+ {unique = true}, {{0, 'string'}, {1, 'unsigned'}}}
+
+ log.info("create secondary index child_id on _fk_constraint")
+ _index:insert{box.schema.FK_CONSTRAINT_ID, 1, 'child_id', 'tree',
+ {unique = false}, {{1, 'unsigned'}}}
+
-- Nullability wasn't skipable. This was fixed in 1-7.
-- Now, abscent field means NULL, so we can safely set second
-- field in format, marking it nullable.
diff --git a/src/box/schema.cc b/src/box/schema.cc
index 86c56ee2e..faad53700 100644
--- a/src/box/schema.cc
+++ b/src/box/schema.cc
@@ -412,6 +412,22 @@ schema_init()
COLL_NONE, SORT_ORDER_ASC);
/* _sql_stat4 - extensive statistics on space, seen in SQL. */
sc_space_new(BOX_SQL_STAT4_ID, "_sql_stat4", key_def, NULL, NULL);
+
+ key_def_delete(key_def);
+ key_def = key_def_new(2);
+ if (key_def == NULL)
+ diag_raise();
+ /* Constraint name. */
+ key_def_set_part(key_def, 0, BOX_FK_CONSTRAINT_FIELD_NAME,
+ FIELD_TYPE_STRING, ON_CONFLICT_ACTION_ABORT, NULL,
+ COLL_NONE, SORT_ORDER_ASC);
+ /* Child space. */
+ key_def_set_part(key_def, 1, BOX_FK_CONSTRAINT_FIELD_CHILD_ID,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_ABORT, NULL,
+ COLL_NONE, SORT_ORDER_ASC);
+ /* _fk_сonstraint - foreign keys constraints. */
+ sc_space_new(BOX_FK_CONSTRAINT_ID, "_fk_constraint", key_def,
+ &on_replace_fk_constraint, NULL);
}
void
diff --git a/src/box/schema_def.h b/src/box/schema_def.h
index 5ab4bb002..6022ea072 100644
--- a/src/box/schema_def.h
+++ b/src/box/schema_def.h
@@ -107,6 +107,8 @@ enum {
/** Space ids for SQL statictics. */
BOX_SQL_STAT1_ID = 348,
BOX_SQL_STAT4_ID = 349,
+ /** Space id of _fk_constraint. */
+ BOX_FK_CONSTRAINT_ID = 356,
/** End of the reserved range of system spaces. */
BOX_SYSTEM_ID_MAX = 511,
BOX_ID_NIL = 2147483647
@@ -224,6 +226,18 @@ enum {
BOX_TRIGGER_FIELD_OPTS = 2,
};
+/** _fk_constraint fields. */
+enum {
+ BOX_FK_CONSTRAINT_FIELD_NAME = 0,
+ BOX_FK_CONSTRAINT_FIELD_CHILD_ID = 1,
+ BOX_FK_CONSTRAINT_FIELD_PARENT_ID = 2,
+ BOX_FK_CONSTRAINT_FIELD_DEFERRED = 3,
+ BOX_FK_CONSTRAINT_FIELD_MATCH = 4,
+ BOX_FK_CONSTRAINT_FIELD_ON_DELETE = 5,
+ BOX_FK_CONSTRAINT_FIELD_ON_UPDATE = 6,
+ BOX_FK_CONSTRAINT_FIELD_LINKS = 7,
+};
+
/*
* Different objects which can be subject to access
* control.
diff --git a/src/box/space.c b/src/box/space.c
index e53f1598c..90a80ed7b 100644
--- a/src/box/space.c
+++ b/src/box/space.c
@@ -163,6 +163,8 @@ space_create(struct space *space, struct engine *engine,
space->index_map[index_def->iid] = index;
}
space_fill_index_map(space);
+ rlist_create(&space->parent_fkey);
+ rlist_create(&space->child_fkey);
return 0;
fail_free_indexes:
@@ -220,6 +222,8 @@ space_delete(struct space *space)
* on_replace_dd_trigger on deletion from _trigger.
*/
assert(space->sql_triggers == NULL);
+ assert(rlist_empty(&space->parent_fkey));
+ assert(rlist_empty(&space->child_fkey));
space->vtab->destroy(space);
}
diff --git a/src/box/space.h b/src/box/space.h
index 01a4af726..97650cffe 100644
--- a/src/box/space.h
+++ b/src/box/space.h
@@ -183,6 +183,15 @@ struct space {
* of index id.
*/
struct index **index;
+ /**
+ * Lists of foreign key constraints. In SQL terms parent
+ * space is the "from" table i.e. the table that contains
+ * the REFERENCES clause. Child space is "to" table, in
+ * other words the table that is named in the REFERENCES
+ * clause.
+ */
+ struct rlist parent_fkey;
+ struct rlist child_fkey;
};
/** Initialize a base space instance. */
diff --git a/src/box/sql.c b/src/box/sql.c
index d48c3cfe5..d4b0d7fcc 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1251,6 +1251,14 @@ void tarantoolSqlite3LoadSchema(struct init_data *init)
"\"sample\","
"PRIMARY KEY(\"tbl\", \"idx\", \"sample\"))");
+ sql_init_callback(init, TARANTOOL_SYS_FK_CONSTRAINT_NAME,
+ BOX_FK_CONSTRAINT_ID, 0,
+ "CREATE TABLE \""TARANTOOL_SYS_FK_CONSTRAINT_NAME
+ "\"(\"name\" TEXT, \"parent_id\" INT, \"child_id\" INT,"
+ "\"deferred\" INT, \"match\" TEXT, \"on_delete\" TEXT,"
+ "\"on_update\" TEXT, \"links\","
+ "PRIMARY KEY(\"name\", \"child_id\"))");
+
/* Read _space */
if (space_foreach(space_foreach_put_cb, init) != 0) {
init->rc = SQL_TARANTOOL_ERROR;
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index e4bf5a6be..f87f610dc 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -35,6 +35,7 @@
*/
#include "coll.h"
#include "sqliteInt.h"
+#include "box/fkey.h"
#include "box/schema.h"
#include "box/session.h"
#include "tarantoolInt.h"
@@ -707,31 +708,6 @@ sqlite3FkReferences(Table * pTab)
pTab->def->name);
}
-/**
- * The second argument is a Trigger structure allocated by the
- * fkActionTrigger() routine. This function deletes the sql_trigger
- * structure and all of its sub-components.
- *
- * The Trigger structure or any of its sub-components may be
- * allocated from the lookaside buffer belonging to database
- * handle dbMem.
- *
- * @param db Database connection.
- * @param trigger AST object.
- */
-static void
-sql_fk_trigger_delete(struct sqlite3 *db, struct sql_trigger *trigger)
-{
- if (trigger == NULL)
- return;
- struct TriggerStep *trigger_step = trigger->step_list;
- sql_expr_delete(db, trigger_step->pWhere, false);
- sql_expr_list_delete(db, trigger_step->pExprList);
- sql_select_delete(db, trigger_step->pSelect);
- sql_expr_delete(db, trigger->pWhen, false);
- sqlite3DbFree(db, trigger);
-}
-
/*
* The second argument points to an FKey object representing a foreign key
* for which pTab is the child table. An UPDATE statement against pTab
@@ -1309,7 +1285,7 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
sql_expr_list_delete(db, pList);
sql_select_delete(db, pSelect);
if (db->mallocFailed == 1) {
- sql_fk_trigger_delete(db, trigger);
+ fkey_trigger_delete(db, trigger);
return 0;
}
assert(pStep != 0);
@@ -1407,8 +1383,8 @@ sqlite3FkDelete(sqlite3 * db, Table * pTab)
assert(pFKey->isDeferred == 0 || pFKey->isDeferred == 1);
/* Delete any triggers created to implement actions for this FK. */
- sql_fk_trigger_delete(db, pFKey->apTrigger[0]);
- sql_fk_trigger_delete(db, pFKey->apTrigger[1]);
+ fkey_trigger_delete(db, pFKey->apTrigger[0]);
+ fkey_trigger_delete(db, pFKey->apTrigger[1]);
pNext = pFKey->pNextFrom;
sqlite3DbFree(db, pFKey);
diff --git a/src/box/sql/tarantoolInt.h b/src/box/sql/tarantoolInt.h
index e1430a398..bc61e8426 100644
--- a/src/box/sql/tarantoolInt.h
+++ b/src/box/sql/tarantoolInt.h
@@ -20,6 +20,7 @@
#define TARANTOOL_SYS_TRUNCATE_NAME "_truncate"
#define TARANTOOL_SYS_SQL_STAT1_NAME "_sql_stat1"
#define TARANTOOL_SYS_SQL_STAT4_NAME "_sql_stat4"
+#define TARANTOOL_SYS_FK_CONSTRAINT_NAME "_fk_constraint"
/* Max space id seen so far. */
#define TARANTOOL_SYS_SCHEMA_MAXID_KEY "max_id"
diff --git a/test/box/access_misc.result b/test/box/access_misc.result
index 53af3e8bd..370bbfa01 100644
--- a/test/box/access_misc.result
+++ b/test/box/access_misc.result
@@ -815,6 +815,11 @@ box.space._space:select()
- [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt', 'type': 'string'},
{'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type': 'scalar'}]]
+ - [356, 1, '_fk_constraint', 'memtx', 0, {}, [{'name': 'name', 'type': 'string'},
+ {'name': 'child_id', 'type': 'unsigned'}, {'name': 'parent_id', 'type': 'unsigned'},
+ {'name': 'is_deferred', 'type': 'boolean'}, {'name': 'match', 'type': 'string'},
+ {'name': 'on_delete', 'type': 'string'}, {'name': 'on_update', 'type': 'string'},
+ {'name': 'links', 'type': 'array'}]]
...
box.space._func:select()
---
diff --git a/test/box/access_sysview.result b/test/box/access_sysview.result
index ae042664a..77a24b425 100644
--- a/test/box/access_sysview.result
+++ b/test/box/access_sysview.result
@@ -230,11 +230,11 @@ box.session.su('guest')
...
#box.space._vspace:select{}
---
-- 22
+- 23
...
#box.space._vindex:select{}
---
-- 48
+- 50
...
#box.space._vuser:select{}
---
@@ -262,7 +262,7 @@ box.session.su('guest')
...
#box.space._vindex:select{}
---
-- 48
+- 50
...
#box.space._vuser:select{}
---
diff --git a/test/box/alter.result b/test/box/alter.result
index c41b52f48..0d50855d2 100644
--- a/test/box/alter.result
+++ b/test/box/alter.result
@@ -107,7 +107,7 @@ space = box.space[t[1]]
...
space.id
---
-- 350
+- 357
...
space.field_count
---
@@ -152,7 +152,7 @@ space_deleted
...
space:replace{0}
---
-- error: Space '350' does not exist
+- error: Space '357' does not exist
...
_index:insert{_space.id, 0, 'primary', 'tree', {unique=true}, {{0, 'unsigned'}}}
---
@@ -231,6 +231,8 @@ _index:select{}
- [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string']]]
- [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string'], [
5, 'scalar']]]
+ - [356, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'unsigned']]]
+ - [356, 1, 'child_id', 'tree', {'unique': false}, [[1, 'unsigned']]]
...
-- modify indexes of a system space
_index:delete{_index.id, 0}
diff --git a/test/box/misc.result b/test/box/misc.result
index 892851823..a680f752e 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -491,6 +491,8 @@ t;
164: box.error.NO_SUCH_GROUP
165: box.error.NO_SUCH_MODULE
166: box.error.NO_SUCH_COLLATION
+ 167: box.error.CREATE_FK_CONSTRAINT
+ 168: box.error.DROP_FK_CONSTRAINT
...
test_run:cmd("setopt delimiter ''");
---
diff --git a/test/engine/iterator.result b/test/engine/iterator.result
index 98b0b3e7d..2204ff647 100644
--- a/test/engine/iterator.result
+++ b/test/engine/iterator.result
@@ -4207,18 +4207,6 @@ value
---
- null
...
-s:replace{35}
----
-- [35]
-...
-state, value = gen(param,state)
----
-- error: 'builtin/box/schema.lua:1051: usage: next(param, state)'
-...
-value
----
-- null
-...
s:drop()
---
...
diff --git a/test/engine/iterator.test.lua b/test/engine/iterator.test.lua
index fcf753f46..c48dbf1b8 100644
--- a/test/engine/iterator.test.lua
+++ b/test/engine/iterator.test.lua
@@ -399,9 +399,6 @@ value
gen,param,state = i:pairs({35})
state, value = gen(param,state)
value
-s:replace{35}
-state, value = gen(param,state)
-value
s:drop()
diff --git a/test/sql/foreign-keys.result b/test/sql/foreign-keys.result
new file mode 100644
index 000000000..8c53daa83
--- /dev/null
+++ b/test/sql/foreign-keys.result
@@ -0,0 +1,326 @@
+env = require('test_run')
+---
+...
+test_run = env.new()
+---
+...
+test_run:cmd('restart server default with cleanup=1')
+-- Check that tuple inserted into _fk_constraint is FK constrains
+-- valid data.
+--
+box.sql.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT, b INT);")
+---
+...
+box.sql.execute("CREATE UNIQUE INDEX i1 ON t1(a);")
+---
+...
+box.sql.execute("CREATE TABLE t2 (a INT, b INT, id INT PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+---
+...
+-- Parent and child spaces must exist.
+--
+t = {'fk_1', 666, 777, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: Space '666' does not exist
+...
+parent_id = box.space._space.index.name:select('T1')[1]['id']
+---
+...
+child_id = box.space._space.index.name:select('T2')[1]['id']
+---
+...
+view_id = box.space._space.index.name:select('V1')[1]['id']
+---
+...
+-- View can't reference another space or be referenced by another space.
+--
+t = {'fk_1', child_id, view_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': referenced space can''t
+ be VIEW'
+...
+t = {'fk_1', view_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': referencing space can''t
+ be VIEW'
+...
+box.sql.execute("DROP VIEW v1;")
+---
+...
+-- Match clause can be only one of: simple, partial, full.
+--
+t = {'fk_1', child_id, parent_id, false, 'wrong_match', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': unknown MATCH clause'
+...
+-- On conflict actions can be only one of: set_null, set_default,
+-- restrict, cascade, no_action.
+t = {'fk_1', child_id, parent_id, false, 'simple', 'wrong_action', 'restrict', {{child = 0, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': unknown ON DELETE action'
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'wrong_action', {{child = 0, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': unknown ON UPDATE action'
+...
+-- Temporary restriction (until SQL triggers work from Lua):
+-- referencing space must be empty.
+--
+box.sql.execute("INSERT INTO t2 VALUES (1, 2, 3);")
+---
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 2, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': referencing space must
+ be empty'
+...
+box.sql.execute("DELETE FROM t2;")
+---
+...
+-- Links must be specififed correctly.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 2, child = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': link must be map with
+ 2 fields'
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 2, parent = 1, 2}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': link must be map with
+ 2 fields'
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{wrong_key = 2, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': unexpected key of link
+ 0 ''wrong_key'''
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 13, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': foreign key refers to
+ nonexistent field'
+...
+-- Referenced fields must compose unique index.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}, {child = 1, parent = 2}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': referenced fields don''t
+ compose unique index'
+...
+-- Referencing and referenced fields must feature compatible types.
+-- Temporary, in SQL all fields except for INTEGER PRIMARY KEY
+-- are scalar.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 1, parent = 0}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': field type mismatch'
+...
+-- Each referenced column must appear once.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}, {child = 1, parent = 1}}}
+---
+...
+box.space._fk_constraint:insert(t)
+---
+- error: 'Failed to create foreign key constraint ''fk_1'': referenced fields can
+ not contain duplicates'
+...
+-- Successful creation.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+-- Implicitly referenced index can't be dropped,
+-- ergo - space can't be dropped until it is referenced.
+--
+box.sql.execute("DROP INDEX i1 on t1;")
+---
+- error: 'Can''t modify space ''T1'': can not drop referenced index'
+...
+-- Finally, can't drop space until it has FK constraints,
+-- i.e. by manual removing tuple from _space.
+-- But drop() will delete constraints.
+--
+box.space.T2.index[0]:drop()
+---
+...
+box.space._space:delete(child_id)
+---
+- error: 'Can''t drop space ''T2'': the space has foreign key constraints'
+...
+box.space.T2:drop()
+---
+...
+-- Make sure that constraint has been successfully dropped,
+-- so we can drop now and parent space.
+--
+box.space._fk_constraint:select()
+---
+- []
+...
+box.space.T1:drop()
+---
+...
+-- Create several constraints to make sure that they are held
+-- as linked lists correctly including self-referencing constraints.
+--
+box.sql.execute("CREATE TABLE child (id INT PRIMARY KEY, a INT);")
+---
+...
+box.sql.execute("CREATE TABLE parent (a INT, id INT PRIMARY KEY);")
+---
+...
+parent_id = box.space._space.index.name:select('PARENT')[1]['id']
+---
+...
+child_id = box.space._space.index.name:select('CHILD')[1]['id']
+---
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+t = {'fk_2', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+t = {'fk_3', parent_id, child_id, false, 'simple', 'restrict', 'restrict', {{child = 1, parent = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+t = {'self_1', child_id, child_id, false, 'simple', 'restrict', 'restrict', {{parent = 0, child = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+t = {'self_2', parent_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 1}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+box.space._fk_constraint:count()
+---
+- 5
+...
+box.space._fk_constraint:delete{'fk_2', child_id}
+---
+- ['fk_2', 515, 516, false, 'simple', 'restrict', 'restrict', [{'parent': 1, 'child': 0}]]
+...
+box.space._fk_constraint:delete{'fk_1', child_id}
+---
+- ['fk_1', 515, 516, false, 'simple', 'restrict', 'restrict', [{'parent': 1, 'child': 0}]]
+...
+t = {'fk_2', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+box.space._fk_constraint:delete{'fk_2', child_id}
+---
+- ['fk_2', 515, 516, false, 'simple', 'restrict', 'restrict', [{'parent': 1, 'child': 0}]]
+...
+box.space._fk_constraint:delete{'self_2', parent_id}
+---
+- ['self_2', 516, 516, false, 'simple', 'restrict', 'restrict', [{'parent': 1, 'child': 1}]]
+...
+box.space._fk_constraint:delete{'self_1', child_id}
+---
+- ['self_1', 515, 515, false, 'simple', 'restrict', 'restrict', [{'parent': 0, 'child': 0}]]
+...
+box.space._fk_constraint:delete{'fk_3', parent_id}
+---
+- ['fk_3', 516, 515, false, 'simple', 'restrict', 'restrict', [{'child': 1, 'parent': 0}]]
+...
+box.space._fk_constraint:count()
+---
+- 0
+...
+-- Replace is also OK.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:insert(t)
+---
+...
+t = {'fk_1', child_id, parent_id, false, 'simple', 'cascade', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:replace(t)
+---
+...
+box.space._fk_constraint:select({'fk_1', child_id})[1]['on_delete']
+---
+- cascade
+...
+t = {'fk_1', child_id, parent_id, true, 'simple', 'cascade', 'restrict', {{parent = 1, child = 0}}}
+---
+...
+t = box.space._fk_constraint:replace(t)
+---
+...
+box.space._fk_constraint:select({'fk_1', child_id})[1]['is_deferred']
+---
+- true
+...
+box.space.CHILD:drop()
+---
+...
+box.space.PARENT:drop()
+---
+...
+-- 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
new file mode 100644
index 000000000..9475a7df9
--- /dev/null
+++ b/test/sql/foreign-keys.test.lua
@@ -0,0 +1,149 @@
+env = require('test_run')
+test_run = env.new()
+test_run:cmd('restart server default with cleanup=1')
+
+
+-- Check that tuple inserted into _fk_constraint is FK constrains
+-- valid data.
+--
+box.sql.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT, b INT);")
+box.sql.execute("CREATE UNIQUE INDEX i1 ON t1(a);")
+box.sql.execute("CREATE TABLE t2 (a INT, b INT, id INT PRIMARY KEY);")
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+
+-- Parent and child spaces must exist.
+--
+t = {'fk_1', 666, 777, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+box.space._fk_constraint:insert(t)
+
+parent_id = box.space._space.index.name:select('T1')[1]['id']
+child_id = box.space._space.index.name:select('T2')[1]['id']
+view_id = box.space._space.index.name:select('V1')[1]['id']
+
+-- View can't reference another space or be referenced by another space.
+--
+t = {'fk_1', child_id, view_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+box.space._fk_constraint:insert(t)
+t = {'fk_1', view_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+box.space._fk_constraint:insert(t)
+box.sql.execute("DROP VIEW v1;")
+
+-- Match clause can be only one of: simple, partial, full.
+--
+t = {'fk_1', child_id, parent_id, false, 'wrong_match', 'restrict', 'restrict', {{child = 0, parent = 1}}}
+box.space._fk_constraint:insert(t)
+
+-- On conflict actions can be only one of: set_null, set_default,
+-- restrict, cascade, no_action.
+t = {'fk_1', child_id, parent_id, false, 'simple', 'wrong_action', 'restrict', {{child = 0, parent = 1}}}
+box.space._fk_constraint:insert(t)
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'wrong_action', {{child = 0, parent = 1}}}
+box.space._fk_constraint:insert(t)
+
+-- Temporary restriction (until SQL triggers work from Lua):
+-- referencing space must be empty.
+--
+box.sql.execute("INSERT INTO t2 VALUES (1, 2, 3);")
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 2, parent = 1}}}
+box.space._fk_constraint:insert(t)
+box.sql.execute("DELETE FROM t2;")
+
+-- Links must be specififed correctly.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 2, child = 1}}}
+box.space._fk_constraint:insert(t)
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 2, parent = 1, 2}}}
+box.space._fk_constraint:insert(t)
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{wrong_key = 2, parent = 1}}}
+box.space._fk_constraint:insert(t)
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 13, parent = 1}}}
+box.space._fk_constraint:insert(t)
+
+-- Referenced fields must compose unique index.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}, {child = 1, parent = 2}}}
+box.space._fk_constraint:insert(t)
+
+-- Referencing and referenced fields must feature compatible types.
+-- Temporary, in SQL all fields except for INTEGER PRIMARY KEY
+-- are scalar.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 1, parent = 0}}}
+box.space._fk_constraint:insert(t)
+
+-- Each referenced column must appear once.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{child = 0, parent = 1}, {child = 1, parent = 1}}}
+box.space._fk_constraint:insert(t)
+
+-- Successful creation.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:insert(t)
+
+-- Implicitly referenced index can't be dropped,
+-- ergo - space can't be dropped until it is referenced.
+--
+box.sql.execute("DROP INDEX i1 on t1;")
+
+-- Finally, can't drop space until it has FK constraints,
+-- i.e. by manual removing tuple from _space.
+-- But drop() will delete constraints.
+--
+box.space.T2.index[0]:drop()
+box.space._space:delete(child_id)
+box.space.T2:drop()
+
+-- Make sure that constraint has been successfully dropped,
+-- so we can drop now and parent space.
+--
+box.space._fk_constraint:select()
+box.space.T1:drop()
+
+-- Create several constraints to make sure that they are held
+-- as linked lists correctly including self-referencing constraints.
+--
+box.sql.execute("CREATE TABLE child (id INT PRIMARY KEY, a INT);")
+box.sql.execute("CREATE TABLE parent (a INT, id INT PRIMARY KEY);")
+
+parent_id = box.space._space.index.name:select('PARENT')[1]['id']
+child_id = box.space._space.index.name:select('CHILD')[1]['id']
+
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:insert(t)
+t = {'fk_2', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:insert(t)
+t = {'fk_3', parent_id, child_id, false, 'simple', 'restrict', 'restrict', {{child = 1, parent = 0}}}
+t = box.space._fk_constraint:insert(t)
+t = {'self_1', child_id, child_id, false, 'simple', 'restrict', 'restrict', {{parent = 0, child = 0}}}
+t = box.space._fk_constraint:insert(t)
+t = {'self_2', parent_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 1}}}
+t = box.space._fk_constraint:insert(t)
+
+box.space._fk_constraint:count()
+box.space._fk_constraint:delete{'fk_2', child_id}
+box.space._fk_constraint:delete{'fk_1', child_id}
+t = {'fk_2', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:insert(t)
+box.space._fk_constraint:delete{'fk_2', child_id}
+box.space._fk_constraint:delete{'self_2', parent_id}
+box.space._fk_constraint:delete{'self_1', child_id}
+box.space._fk_constraint:delete{'fk_3', parent_id}
+box.space._fk_constraint:count()
+
+-- Replace is also OK.
+--
+t = {'fk_1', child_id, parent_id, false, 'simple', 'restrict', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:insert(t)
+t = {'fk_1', child_id, parent_id, false, 'simple', 'cascade', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:replace(t)
+box.space._fk_constraint:select({'fk_1', child_id})[1]['on_delete']
+t = {'fk_1', child_id, parent_id, true, 'simple', 'cascade', 'restrict', {{parent = 1, child = 0}}}
+t = box.space._fk_constraint:replace(t)
+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')
--
2.15.1
More information about the Tarantool-patches
mailing list