From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D207227344 for ; Thu, 12 Jul 2018 22:04:32 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id qE3_ETF5ki3b for ; Thu, 12 Jul 2018 22:04:32 -0400 (EDT) Received: from smtp42.i.mail.ru (smtp42.i.mail.ru [94.100.177.102]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 0B6532730D for ; Thu, 12 Jul 2018 22:04:32 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 2/5] schema: add new system space for FK constraints Date: Fri, 13 Jul 2018 05:04:18 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 In-Reply-To: References: Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik This patch introduces new system space to persist foreign keys contraints. Format of the space: _fk_constraint (space id = 350) [ STR, UINT, UINT, BOOL, STR, STR, STR, ARRAY>] FK constraint is local to space, so every pair 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; - Collations of referencing and referenced fields must match; - Referenced fields must compose unique index; 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 | 432 ++++++++++++++++++++++++++++++++++++++++- src/box/alter.h | 1 + src/box/bootstrap.snap | Bin 1704 -> 1798 bytes src/box/errcode.h | 4 + src/box/fkey.c | 69 +++++++ src/box/fkey.h | 163 ++++++++++++++++ src/box/lua/schema.lua | 6 + src/box/lua/space.cc | 2 + src/box/lua/upgrade.lua | 16 ++ src/box/schema.cc | 16 ++ src/box/schema_def.h | 14 ++ src/box/space.c | 2 + src/box/space.h | 3 + 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 | 5 +- test/box/misc.result | 2 + test/engine/iterator.result | 2 +- test/sql/foreign-keys.result | 316 ++++++++++++++++++++++++++++++ test/sql/foreign-keys.test.lua | 144 ++++++++++++++ 24 files changed, 1214 insertions(+), 36 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 61e3eb637..7ba8a3139 100644 --- a/src/box/CMakeLists.txt +++ b/src/box/CMakeLists.txt @@ -93,6 +93,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 89b11dcd3..aaa56bd21 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" @@ -559,6 +560,18 @@ 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) +{ + 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; +} + /** * True if the space has records identified by key 'uid'. * Uses 'iid' index. @@ -769,9 +782,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; @@ -867,9 +881,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. @@ -1703,6 +1718,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 (old_space->child_fkey != NULL) { + 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 @@ -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) { + 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; + } + } + struct alter_space *alter = alter_space_new(old_space); auto scoped_guard = make_scoped_guard([=] { alter_space_delete(alter); }); @@ -3404,6 +3447,387 @@ 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")); + } + 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) { + 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", "fkey_def"); + auto def_guard = make_scoped_guard([=] { free(fk_def); }); + memset(fk_def, 0, sizeof(*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 = fkey_match_by_name(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 = fkey_action_by_name(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 = fkey_action_by_name(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; +} + +/** + * 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) +{ + *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; + } +} + +/** + * 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); +} + +/** 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 = NULL; + fkey_list_replace(child, parent, fk->def->name, fk, &old_fkey); + fkey_delete(old_fkey); +} + +/** 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) +{ + (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); +} + +/** + * On commit of drop we have already deleted foreign key from + * both (parent's and child's) lists, so just release memory. + */ +static void +on_drop_fkey_commit(struct trigger *trigger, void *event) +{ + (void) event; + struct fkey *fk = (struct fkey *)trigger->data; + fkey_delete(fk); +} + +/** 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_count(pk, ITER_ALL, NULL, 0) > 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). + */ + 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; + 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; + struct fkey *old_fk; + fkey_list_replace(child_space, parent_space, fk_def->name, + fkey, &old_fk); + if (old_tuple == NULL) { + 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 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_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 *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 trigger *on_commit = + txn_alter_trigger_new(on_drop_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 }; @@ -3468,4 +3892,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..44871cb62de38c37851db1cc6c16a59cafbeb106 100644 GIT binary patch delta 1793 zcmV+c2mbh|4TcVo8Gkl3I4x&3H(@m}GdVeB3Q2BrbYX5|WjY`?GB#pmVmC4^VmUE2 zEi_{_F)d*;H8L$>V=ypeG&W>0F*0EaRzqxWV{1AfdwmKD)w&D1%?67A&KEt-<)r`s z0000ewJ-euP!%))N=I-HNzfRn003V+z!wi#2Qap%9}e*e1%I%c5-D@E)qX&4RO>G6 zmMO_fNvuoT@=KEP*hnbWQq$GW7ix*VvzuOPTHs$rR}01CWlAaC0N4QP0G68Mebsm; zN1J%@DBj3e->-ef6MWsuT$ed8I{~8UG50HqBmRc!i-68jv&xICD~hH5cqaN%ys$H- zZ_SK7k=nnmwtuyB-ksl`jCC83tO)(-uYaBQ^)AdmTy?2MO1x2k&QdeB?<;Q-=jtu} zyYrlpjK3iZptIC0(gX*J7Pb1|h_fznmFB&4ouwwNR*#wQhvAZRmYQ2?5tzT9`?}T6 zQWLYl)9R!Oac;sC>9R^H(mzI#@J;DSV3G0a@*61XTF>hm z4Fv?IbK4J?Ya*-HO#OELi9P%qsy{~H_q$(z>*M(eQIvWkLT9Nd>lf>C2lr$$fgn&| zse~`-l)AYjQwpO%H*L4Qk?c{xHVd^Jc;2gLIHSotvZsNv!5kkL_L za&(rO_N5k`7Dv@%?pT*$&eoZyy0K-;mMhgpXQ?@&&-cT!E^{?HOHBZl)|scGv7apY zSu4!IStNfG)lSBXWMn@tZJmGFIxC%dr5By0rVfr5wK|46+wAA1jm^)#CjBVR2+}`*^9-e4Y8R~fX3aWTSE&zHZ2Gy z0H+uPonDgeNE36&!f(?YwuTK~_Au&={S&bB^`s{g8#WOF@NtCJ6Vc)z%6}?eA{4Te z%v0Y+w;o?@A{asAuY(U)`LKYmh#uZ>YMe&vj*~-9=cA*Gig+vj&N*+))C1?dF;g#K zYs`Zae(kLXy1bs8cG+CZ2q4vNoEhh$mmGn%a)Va8!Po{`^ci@Ngblg){nM{9@R&7o5_5!Mvf9L zpB5>ZKMHMOIC^w#l2PN(Z;MLxGr111yHte_S)8iotfvH?i~ZFJ*)1x zD`&{rTDYDhH=M{=KzhJf;;%cw=Y#s|aN%==<=ld>BAPz(wu-?+6LlutXrmW$+RMOg z^2#0a|Bn;kk#{T-ljvQXL=05>gT!A8c3u65>RfM_K^LK5LQ8?+@T)acRXZkU*u|B- jgR)jr@;tTBKTzvJa;RZ})DfZhDp^kwhA0Qs5UuSB(zRu& delta 1699 zcmV;U23+}u4yX-~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+&h36w?690OJ7SjC}R|`e!^< zufxoBSySaEK}34Y9gE@!en$00z_rw*@?r{$VyQo#kG>SK>wIY(Gha_6_ARVsq0YPW z*^{p>3xXA)K!3gUtrNGN1@ezpT`H224JN>~)O>B+%A2IIdZ>SQUNe%x&&UF}mYPMF z%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%_>GD}0%4t7vHjt9ooVEEE?c6%^m+ajRha@mpI!XLX|9h}V+i2A z@rhe|T+Ko(uFkH@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@PGgC9OfyFV4f(DrkG8kko#8`;gVrZ;|sZe1;Q!301 z424=>WUi&=%LED&qVlCr*5%B||L!vCQjv%@8VT1@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?AUb7Z{WsSzQ)C6E@9ooDZ`^l1@wSo*Vi{$U4dSkpeZW)-xR7uXN9oje;I=58#to6mU z)GQIMSmcX~J1bTPRW|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@WqTwal~b%ln5QO6oL_ZE4HeMEh(My z_v_fe^>zAXfXbi)iZOl46U*mTj66G#((TQBk0n5 z@)poLS)q+!uN;jpegcC;P#`N7`U znw9D$>kp61-&FJnXm;?~*ng4k=;HZ8FhRgSHd00X7f+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%^FTe1vid)s6X20|EK)(~T|9{xS1W;}zgF#f{gJ7;9%W`;4+U&31%|V| tLZAw@V?4uhT=^Z8O&m3K=&6$aL0H$8dkzbvjtIq9$$FA7L^;(Et?e54Em;5n diff --git a/src/box/errcode.h b/src/box/errcode.h index c76018cbf..1558cfae8 100644 --- a/src/box/errcode.h +++ b/src/box/errcode.h @@ -215,6 +215,10 @@ struct errcode_record { /*160 */_(ER_ACTION_MISMATCH, "Field %d contains %s on conflict action, but %s in index parts") \ /*161 */_(ER_VIEW_MISSING_SQL, "Space declared as a view must have SQL statement") \ /*162 */_(ER_FOREIGN_KEY_CONSTRAINT, "Can not commit transaction: deferred foreign keys violations are not resolved") \ + /*163 */_(ER_CREATE_FK_CONSTRAINT, "Failed to create foreign key constraint '%s': %s") \ + /*164 */_(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..e45889a0d --- /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 ``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 + * 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 "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..1b6ea71d9 --- /dev/null +++ b/src/box/fkey.h @@ -0,0 +1,163 @@ +#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 ``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 + * 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 +#include + +#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; + /** Linked lists */ + struct fkey *fkey_parent_next; + struct fkey *fkey_child_next; +}; + +/** + * 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 enum fkey_action +fkey_action_by_name(const char *action_str, size_t len) +{ + return (enum fkey_action) strnindex(fkey_action_strs, action_str, len, + fkey_action_MAX); +} + +static inline enum fkey_match +fkey_match_by_name(const char *match_str, size_t len) +{ + return (enum fkey_match) strnindex(fkey_match_strs, match_str, len, + fkey_match_MAX); +} + +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 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 + _fk_constraint:delete{t.name, t.child_id} + end + 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 b44bed451..ea744dd4e 100644 --- a/src/box/lua/space.cc +++ b/src/box/lua/space.cc @@ -545,6 +545,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..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'}, + {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/schema.cc b/src/box/schema.cc index 0377e4dda..5cd79267c 100644 --- a/src/box/schema.cc +++ b/src/box/schema.cc @@ -404,6 +404,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..22621fc11 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 = 350, /** 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 64aa31217..98aa1d8b7 100644 --- a/src/box/space.c +++ b/src/box/space.c @@ -216,6 +216,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); space->vtab->destroy(space); } 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. */ + struct fkey *parent_fkey; + struct fkey *child_fkey; }; /** Initialize a base space instance. */ diff --git a/src/box/sql.c b/src/box/sql.c index 063743e87..e0c2c924c 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -1293,6 +1293,14 @@ void tarantoolSqlite3LoadSchema(InitData *init) "\"sample\"," "PRIMARY KEY(\"tbl\", \"idx\", \"sample\"))"); + sql_schema_put(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 be080324f..016ded8d0 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" @@ -726,31 +727,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 @@ -1341,7 +1317,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); @@ -1439,8 +1415,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 f043a60d1..c31da131d 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 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'}]] ... box.space._func:select() --- diff --git a/test/box/access_sysview.result b/test/box/access_sysview.result index ae042664a..1c4e7b787 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 +- 49 ... #box.space._vuser:select{} --- @@ -262,7 +262,7 @@ box.session.su('guest') ... #box.space._vindex:select{} --- -- 48 +- 49 ... #box.space._vuser:select{} --- diff --git a/test/box/alter.result b/test/box/alter.result index c41b52f48..d995261c3 100644 --- a/test/box/alter.result +++ b/test/box/alter.result @@ -107,7 +107,7 @@ space = box.space[t[1]] ... space.id --- -- 350 +- 351 ... space.field_count --- @@ -152,7 +152,7 @@ space_deleted ... space:replace{0} --- -- error: Space '350' does not exist +- error: Space '351' does not exist ... _index:insert{_space.id, 0, 'primary', 'tree', {unique=true}, {{0, 'unsigned'}}} --- @@ -231,6 +231,7 @@ _index:select{} - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string']]] - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string'], [ 5, 'scalar']]] + - [350, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [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 a00d03365..315499f3e 100644 --- a/test/box/misc.result +++ b/test/box/misc.result @@ -487,6 +487,8 @@ t; 160: box.error.ACTION_MISMATCH 161: box.error.VIEW_MISSING_SQL 162: box.error.FOREIGN_KEY_CONSTRAINT + 163: box.error.CREATE_FK_CONSTRAINT + 164: box.error.DROP_FK_CONSTRAINT ... test_run:cmd("setopt delimiter ''"); --- 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)' ... value --- diff --git a/test/sql/foreign-keys.result b/test/sql/foreign-keys.result new file mode 100644 index 000000000..77b49d67d --- /dev/null +++ b/test/sql/foreign-keys.result @@ -0,0 +1,316 @@ +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 the same 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 = 1}, {child = 2, parent = 2}}} +--- +... +box.space._fk_constraint:insert(t) +--- +- error: 'Failed to create foreign key constraint ''fk_1'': field type mismatch' +... +-- 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'] +--- +- null +... +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..57cc7e118 --- /dev/null +++ b/test/sql/foreign-keys.test.lua @@ -0,0 +1,144 @@ +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 the same 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 = 1}, {child = 2, parent = 2}}} +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