[tarantool-patches] Re: [PATCH 3/5] sql: introduce ADD CONSTRAINT statement
n.pettik
korablev at tarantool.org
Wed Jul 25 13:03:52 MSK 2018
> Also see other fixes on the branch in a separate commit.
Thx for fixes. I have squashed them all.
Except fixes mentioned below, I disabled (temporary) sql-tap/alter2.test.lua
(it checks work of ALTER TABLE ADD CONSTRAINT) for vinyl engine.
Since in previous patch we prohibited creation of FK constraints on
non-empty spaces and as condition used ‘index_size()’, some tests turn out
to be flaky. (I don’t think that we should disable these tests for vinyl, but didn’t
come up with satisfactory solution.)
> 1. Typos below: paren, refrencing.
Fixed.
>
> On 13/07/2018 05:04, Nikita Pettik wrote:
>> After introducing separate space for persisting foreign key
>> constraints, nothing prevents us from adding ALTER TABLE statement to
>> add or drop named constraints. According to ANSI syntax is following:
>> ALTER TABLE <referencing table> ADD CONSTRAINT
>> <referential constraint name> FOREIGN KEY
>> <left parent> <referencing columns> <right paren> REFERENCES
>
> 2. Can you give an example what is <left/right parent>? Or maybe you meant
> parentheses?
Typo: I mean paren (i.e. bracket) - exactly this word is used for ANSI syntax.
>> create mode 100755 test/sql-tap/alter2.test.lua
>> diff --git a/src/box/fkey.h b/src/box/fkey.h
>> index 1b6ea71d9..939773ef2 100644
>> --- a/src/box/fkey.h
>> +++ b/src/box/fkey.h
>> @@ -141,6 +141,12 @@ fkey_is_self_referenced(const struct fkey_def *fkey)
>> return fkey->child_id == fkey->parent_id;
>> }
>> +static inline bool
>> +space_fkey_check_references(const struct space *space)
>> +{
>> + return space->parent_fkey != NULL;
>> +}
>
> 3. Are you sure that you need this one-line function for the
> single place of usage? And in this place you can remove it and
> nothing would change. See the code:
>
> if (space_fkey_check_references(space)) {
> for (struct fkey *fk = space->parent_fkey; fk != NULL;
> fk = fk->fkey_parent_next) {
>
> If here space->parent_key == NULL, then the cycle just won't start. It
> is not?
>
> (I have fixed this comment in my commit).
Yep, sort of extra check and it can be removed.
>> +
>> /**
>> * The second argument is a Trigger structure allocated by the
>> * fkActionTrigger() routine.This function deletes the Trigger
>> diff --git a/src/box/sql/alter.c b/src/box/sql/alter.c
>> index fe54e5531..e81113f58 100644
>> --- a/src/box/sql/alter.c
>> +++ b/src/box/sql/alter.c
>> @@ -189,12 +188,6 @@ sqlite3AlterFinishAddColumn(Parse * pParse, Token * pColDef)
>> sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column");
>> return;
>> }
>> - if ((user_session->sql_flags & SQLITE_ForeignKeys) && pNew->pFKey
>> - && pDflt) {
>> - sqlite3ErrorMsg(pParse,
>> - "Cannot add a REFERENCES column with non-NULL default value");
>> - return;
>> - }
>
> 4. Why did you remove this?
Well, firstly this is dead code: we don't support ALTER TABLE ADD COLUMN
by no means. So, this function can be completely removed. Idk why it still exists.
Secondly, I am not even sure that ANSI allows to do this.
>> assert(pNew->def->fields[pNew->def->field_count - 1].is_nullable ==
>> action_is_nullable(pNew->def->fields[
>> pNew->def->field_count - 1].nullable_action));
>> diff --git a/src/box/sql/build.c b/src/box/sql/build.c
>> index 0c762fac9..c2d3cd035 100644
>> --- a/src/box/sql/build.c
>> +++ b/src/box/sql/build.c
>> @@ -373,9 +374,6 @@ deleteTable(sqlite3 * db, Table * pTable)
>> freeIndex(db, pIndex);
>> }
>> - /* Delete any foreign keys attached to this table. */
>> - sqlite3FkDelete(db, pTable);
>
> 5. I still see sqlite3FkDelete in one comment. Please, remove. Maybe
> the comment is obsolete.
Fixed:
+++ b/src/box/sql/fkey.c
@@ -807,7 +807,7 @@ fkey_is_required(uint32_t space_id, int *changes)
*
* The returned pointer is cached as part of the foreign key
* object. It is eventually freed along with the rest of the
- * foreign key object by sqlite3FkDelete().
+ * foreign key object by fkey_delete().
>> -
>> /* Delete the Table structure itself.
>> */
>> sqlite3HashClear(&pTable->idxHash);
>> @@ -1743,6 +1741,95 @@ emitNewSysSpaceSequenceRecord(Parse *pParse, int space_id, const char reg_seq_id
>> return first_col;
>> }
>> +/**
>> + * Generate opcodes to serialize foreign key into MgsPack and
>> + * insert produced tuple into _fk_constraint space.
>> + *
>> + * @param parse_context Parsing context.
>> + * @param fk Foreign key to be created.
>> + */
>> +static void
>> +vdbe_fkey_code_creation(struct Parse *parse_context, const struct fkey_def *fk)
>
> 6. How about vdbe_emit_fkey_create? As I remember, we have decided to use
> _emit for functions generating opcodes.
I called it this way since we already have sql_code_drop_table…
But I think you are right:
@@ -2057,7 +2057,7 @@ sql_clear_stat_spaces(struct Parse *parse, const char *table_name,
* @param child_id Id of table which constraint belongs to.
*/
static void
-vdbe_fkey_code_drop(struct Parse *parse_context, const char *constraint_name,
+vdbe_emit_fkey_drop(struct Parse *parse_context, const char *constraint_name,
@@ -2135,7 +2135,7 @@ sql_code_drop_table(struct Parse *parse_context, struct space *space,
child_fk->def->name);
if (fk_name_dup == NULL)
return;
- vdbe_fkey_code_drop(parse_context, fk_name_dup, space_id);
+ vdbe_emit_fkey_drop(parse_context, fk_name_dup, space_id);
@@ -2531,7 +2531,7 @@ sql_drop_foreign_key(struct Parse *parse_context, struct SrcList *table,
sqlite3DbFree(db, (void *) constraint_name);
return;
}
- vdbe_fkey_code_drop(parse_context, constraint_name, child_id);
+ vdbe_emit_fkey_drop(parse_context, constraint_name, child_id);
@@ -1557,7 +1557,7 @@ emitNewSysSpaceSequenceRecord(Parse *pParse, int space_id, const char reg_seq_id
* @param fk Foreign key to be created.
*/
static void
-vdbe_fkey_code_creation(struct Parse *parse_context, const struct fkey_def *fk)
+vdbe_emit_fkey_create(struct Parse *parse_context, const struct fkey_def *fk)
@@ -1837,7 +1837,7 @@ sqlite3EndTable(Parse * pParse, /* Parse context */
fk->parent_id = iSpaceId;
}
fk->child_id = iSpaceId;
- vdbe_fkey_code_creation(pParse, fk);
+ vdbe_emit_fkey_create(pParse, fk);
@@ -2488,7 +2488,7 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
if (!is_alter)
parse_context->new_fkey->fkey = fk;
else
- vdbe_fkey_code_creation(parse_context, fk);
+ vdbe_emit_fkey_create(parse_context, fk);
>
>> +{
>> + assert(parse_context != NULL);
>> + assert(fk != NULL);
>> + struct Vdbe *vdbe = sqlite3GetVdbe(parse_context);
>> + assert(vdbe != NULL);
>> + /*
>> + * Occupy registers for 8 fields: each member in
>> + * _constraint space plus one for final msgpack tuple.
>> + */
>> + int constr_tuple_reg = sqlite3GetTempRange(parse_context, 9);
>> + const char *name_copy = sqlite3DbStrDup(parse_context->db, fk->name);
>> + if (name_copy == NULL)
>> + return;
>> + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg, 0, name_copy,
>> + P4_DYNAMIC);
>> + /*
>> + * In case we are adding FK constraints during execution
>> + * of <CREATE TABLE ...> statement, we don't have child
>> + * id, but we know register where it will be stored.
>> + * */
>> + if (parse_context->pNewTable != NULL) {
>> + sqlite3VdbeAddOp2(vdbe, OP_SCopy, fk->child_id,
>> + constr_tuple_reg + 1);
>> + } else {
>> + sqlite3VdbeAddOp2(vdbe, OP_Integer, fk->child_id,
>> + constr_tuple_reg + 1);
>> + }
>> + if (parse_context->pNewTable != NULL && fkey_is_self_referenced(fk)) {
>> + sqlite3VdbeAddOp2(vdbe, OP_SCopy, fk->parent_id,
>> + constr_tuple_reg + 2);
>> + } else {
>> + sqlite3VdbeAddOp2(vdbe, OP_Integer, fk->parent_id,
>> + constr_tuple_reg + 2);
>> + }
>> + sqlite3VdbeAddOp2(vdbe, OP_Bool, 0, constr_tuple_reg + 3);
>> + sqlite3VdbeChangeP4(vdbe, -1, (char*)&fk->is_deferred, P4_BOOL);
>> + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 4, 0,
>> + fkey_match_strs[fk->match], P4_STATIC);
>> + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 5, 0,
>> + fkey_action_strs[fk->on_delete], P4_STATIC);
>> + sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 6, 0,
>> + fkey_action_strs[fk->on_update], P4_STATIC);
>> + size_t encoded_links_sz = fkey_encode_links(fk, NULL) + 1;
>> + char *encoded_links = sqlite3DbMallocRaw(parse_context->db,
>> + encoded_links_sz);
>> + if (encoded_links == NULL) {
>> + free((void *) name_copy);
>
> 7. name_copy is allocated on Db, but freed with libc. It is a path to
> the dark side.
Oops, fixed:
@@ -1796,7 +1796,7 @@ vdbe_fkey_code_creation(struct Parse *parse_context, const struct fkey_def *fk)
char *encoded_links = sqlite3DbMallocRaw(parse_context->db,
encoded_links_sz);
if (encoded_links == NULL) {
- free((void *) name_copy);
+ sqlite3DbFree(parse_context->db, (void *) name_copy);
return;
>> + return;
>> + }
>> + size_t real_links_sz = fkey_encode_links(fk, encoded_links);
>> + encoded_links[real_links_sz] = '\0';
>
> 8. Why do you need zero-termination? Encoded_links is MessagePack. It
> can contain any number of zeros inside and can be non-terminated.
Just in case. But if it annoys you, I will remove it:
@@ -1792,15 +1792,14 @@ vdbe_fkey_code_creation(struct Parse *parse_context, const struct fkey_def *fk)
fkey_action_strs[fk->on_delete], P4_STATIC);
sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 6, 0,
fkey_action_strs[fk->on_update], P4_STATIC);
- size_t encoded_links_sz = fkey_encode_links(fk, NULL) + 1;
+ size_t encoded_links_sz = fkey_encode_links(fk, NULL);
char *encoded_links = sqlite3DbMallocRaw(parse_context->db,
encoded_links_sz);
...
size_t real_links_sz = fkey_encode_links(fk, encoded_links);
- encoded_links[real_links_sz] = '\0';
>
>> + sqlite3VdbeAddOp4(vdbe, OP_Blob, real_links_sz, constr_tuple_reg + 7,
>> + SQL_SUBTYPE_MSGPACK, encoded_links, P4_DYNAMIC);
>> + sqlite3VdbeAddOp3(vdbe, OP_MakeRecord, constr_tuple_reg, 8,
>> + constr_tuple_reg + 8);
>> + sqlite3VdbeAddOp2(vdbe, OP_SInsert, BOX_FK_CONSTRAINT_ID,
>> + constr_tuple_reg + 8);
>> + sqlite3VdbeChangeP5(vdbe, OPFLAG_NCHANGE);
>> + sqlite3ReleaseTempRange(parse_context, constr_tuple_reg, 9);
>> +}
>> +
>> +static int
>> +resolve_link(struct Parse *parse_context, const struct space_def *def,
>> + const char *field_name, uint32_t *link)
>> +{
>> + assert(link != NULL);
>> + uint32_t j;
>> + for (j = 0; j < def->field_count; ++j) {
>> + if (strcmp(field_name, def->fields[j].name) == 0) {
>> + *link = j;
>> + break;
>> + }
>> + }
>> + if (j == def->field_count) {
>> + sqlite3ErrorMsg(parse_context, "no such column %s", field_name);
>> + return -1;
>> + }
>> + return 0;
>> +}
>
> 9. How about create tuple_dictionary on CREATE TABLE in table->def and use
> its method tuple_fieldno_by_name?
Wouldn’t it be over-engineering (as Kostya says)? I mean it would be used
only for this (creating FK constraints during CREATE TABLE execution) and
anyway resurrected from scratch after real space creation.
>> +
>> /*
>> * This routine is called to report the final ")" that terminates
>> * a CREATE TABLE statement.
>> @@ -1913,6 +2000,39 @@ sqlite3EndTable(Parse * pParse, /* Parse context */
>> /* Reparse everything to update our internal data structures */
>> parseTableSchemaRecord(pParse, iSpaceId, zStmt); /* consumes zStmt */
>> +
>> + /* Code creation of FK constraints, if any. */
>> + struct fkey_parse *fk_parse = pParse->new_fkey;
>> + while (fk_parse != NULL) {
>> + struct fkey_def *fk = fk_parse->fkey;
>> + if (fk_parse->selfref_cols != NULL) {
>> + struct ExprList *cols = fk_parse->selfref_cols;
>> + for (uint32_t i = 0; i < fk->field_count; ++i) {
>
> 10. Why do you iterate for fk->field_count, but access cols->a? Is it
> guaranteed that fk->field_count == cols->nExpr?
Yep, it is guaranteed that cols->nExpr must’n exceed fk->field_count.
The case this code handles is:
CREATE TABLE child (id INT PRIMARY KEY, a REFERENCES parent (a));
OR
CREATE TABLE child (id INT PRIMARY KEY, a, b, FOREIGN KEY (a, b) REFERENCES parent (a, b));
In other words, when FK constraints are create within CREATE TABLE statement and are
not self-referenced. In such case we verify that number of columns in both (parent and child)
list are equal (sql_create_foreign_key()):
if (parent_cols != NULL) {
if (parent_cols->nExpr != (int) child_cols_count) {
sqlite3ErrorMsg(parse_context,
"number of columns in foreign key does "
"not match the number of columns in "
"the referenced table");
goto exit_create_fk;
}
} else if (!is_self_referenced) {
/*
* If parent columns are not specified, then PK columns
* of parent table are used as referenced.
*/
struct index *parent_pk = space_index(parent_space, 0);
assert(parent_pk != NULL);
if (parent_pk->def->key_def->part_count != child_cols_count) {
sqlite3ErrorMsg(parse_context,
"number of columns in foreign key does "
"not match the number of columns in "
"the referenced table");
goto exit_create_fk;
}
}
>
>> + if (resolve_link(pParse, p->def,
>> + cols->a[i].zName,
>> + &fk->links[i].parent_field) != 0)
>> + return;
>> + }
>> + fk->parent_id = iSpaceId;
>> + } else if (fk_parse->is_self_referenced) {
>> + struct Index *pk = sqlite3PrimaryKeyIndex(p);
>> + if (pk->nColumn != fk->field_count) {
>> + sqlite3ErrorMsg(pParse,
>> + "number of columns in foreign key does "
>> + "not match the number of columns in "
>> + "the referenced table");
>
> 11. ER_CREATE_FK_CONSTRAINT? Or ER_CREATE_SPACE.
Ok, I’ve replaced sqlite3ErrorMgs() with diag_set():
@@ -2013,10 +2012,14 @@ sqlite3EndTable(Parse * pParse, /* Parse context */
} else if (fk_parse->is_self_referenced) {
struct Index *pk = sqlite3PrimaryKeyIndex(p);
if (pk->nColumn != fk->field_count) {
- sqlite3ErrorMsg(pParse,
- "number of columns in foreign key does "
- "not match the number of columns in "
- "the referenced table");
+ diag_set(ClientError,
+ ER_CREATE_FK_CONSTRAINT,
+ fk->name, "number of columns "
+ "in foreign key does not "
+ "match the number of columns "
+ "in the referenced table");
+ pParse->rc = SQL_TARANTOOL_ERROR;
+ pParse->nErr++;
return;
>
>> + return;
>> + }
>> + for (uint32_t i = 0; i < fk->field_count; ++i) {
>> + fk->links[i].parent_field =
>> + pk->aiColumn[i];
>> + }
>> + fk->parent_id = iSpaceId;
>> + }
>> + fk->child_id = iSpaceId;
>> + vdbe_fkey_code_creation(pParse, fk);
>> + fk_parse = fk_parse->next;
>
> 12. You can use stailq/rlist to link fkey_parse objects and use
> here (not only here) rlist_foreach_entry.
Ok, got rid off hand-made list:
@@ -1804,8 +1804,8 @@ sqlite3EndTable(Parse * pParse, /* Parse context */
parseTableSchemaRecord(pParse, iSpaceId, zStmt); /* consumes zStmt */
/* Code creation of FK constraints, if any. */
- struct fkey_parse *fk_parse = pParse->new_fkey;
- while (fk_parse != NULL) {
+ struct fkey_parse *fk_parse;
+ rlist_foreach_entry(fk_parse, &pParse->new_fkey, link) {
@@ -2351,9 +2350,7 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
goto tnt_error;
}
memset(fk, 0, sizeof(*fk));
- struct fkey_parse *last_fk = parse_context->new_fkey;
- parse_context->new_fkey = fk;
- fk->next = last_fk;
+ rlist_add_entry(&parse_context->new_fkey, fk, link);
@@ -2372,8 +2369,11 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
if (parent_id == BOX_ID_NIL) {
parent_space = NULL;
if (is_self_referenced) {
- parse_context->new_fkey->selfref_cols = parent_cols;
- parse_context->new_fkey->is_self_referenced = true;
+ struct fkey_parse *fk =
+ rlist_first_entry(&parse_context->new_fkey,
+ struct fkey_parse, link);
+ fk->selfref_cols = parent_cols;
+ fk->is_self_referenced = true;
@@ -2485,10 +2485,14 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
* lets delay it until sqlite3EndTable() call and simply
* maintain list of all FK constraints inside parser.
*/
- if (!is_alter)
- parse_context->new_fkey->fkey = fk;
- else
- vdbe_fkey_code_creation(parse_context, fk);
+ if (!is_alter) {
+ struct fkey_parse *parse_fk =
+ rlist_first_entry(&parse_context->new_fkey,
+ struct fkey_parse, link);
+ parse_fk->fkey = fk;
+ } else {
+ vdbe_emit_fkey_create(parse_context, fk);
+ }
@@ -2506,10 +2510,13 @@ tnt_error:
void
fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred)
{
- if (parse_context->db->init.busy || parse_context->new_fkey == NULL)
+ if (parse_context->db->init.busy ||
+ rlist_empty(&parse_context->new_fkey))
return;
- struct fkey_def *fk = parse_context->new_fkey->fkey;
- fk->is_deferred = is_deferred;
+ struct fkey_parse *fk_parse =
+ rlist_first_entry(&parse_context->new_fkey, struct fkey_parse,
+ link);
+ fk_parse->fkey->is_deferred = is_deferred;
}
+++ b/src/box/sql/prepare.c
@@ -418,6 +418,7 @@ sql_parser_create(struct Parse *parser, sqlite3 *db)
{
memset(parser, 0, sizeof(struct Parse));
parser->db = db;
+ rlist_create(&parser->new_fkey);
region_create(&parser->region, &cord()->slabc);
}
@@ -428,11 +429,10 @@ sql_parser_destroy(Parse *parser)
sqlite3 *db = parser->db;
sqlite3DbFree(db, parser->aLabel);
sql_expr_list_delete(db, parser->pConstExpr);
- struct fkey_parse *fk = parser->new_fkey;
- while (fk != NULL) {
+ struct fkey_parse *fk;
+ rlist_foreach_entry(fk, &parser->new_fkey, link)
sql_expr_list_delete(db, fk->selfref_cols);
- fk = fk->next;
- }
@@ -2831,7 +2830,7 @@ struct fkey_parse {
*/
bool is_self_referenced;
/** Organize these structs into linked list. */
- struct fkey_parse *next;
+ struct rlist link;
@@ -2934,7 +2933,7 @@ struct Parse {
/**
* Foreign key constraint appeared in CREATE TABLE stmt.
*/
- struct fkey_parse *new_fkey;
+ struct rlist new_fkey;
>> + }
>> }
>> /* Add the table to the in-memory representation of the database.
>> @@ -2085,6 +2205,32 @@ sql_clear_stat_spaces(Parse *parse, const char *table_name,
>> }
>> }
>> +/**
>> + * Generate VDBE program to remove entry from _fk_constraint space.
>> + *
>> + * @param parse_context Parsing context.
>> + * @param constraint_name Name of FK constraint to be dropped.
>> + * Must be allocated on head by sqlite3DbMalloc().
>> + * It will be freed in VDBE.
>> + * @param child_id Id of table which constraint belongs to.
>> + */
>> +static void
>> +vdbe_fkey_code_drop(struct Parse *parse_context, const char *constraint_name,
>> + uint32_t child_id)
>
> 13. vdbe_emit_fkey_drop?
Fixed alongside with creation procedure. See above.
>> +void
>> +sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
>> + struct Token *constraint, struct ExprList *child_cols,
>> + struct Token *parent, struct ExprList *parent_cols,
>> + bool is_deferred, int actions)
>> +{
>> + struct sqlite3 *db = parse_context->db;
>> + /*
>> + * When this function is called second time during
>> + * <CREATE TABLE ...> statement (i.e. at VDBE runtime),
>> + * don't even try to do something.
>> + */
>> + if (db->init.busy)
>> + return;
>
> 14. How is it possible? That sql_create_foreign_key is called twice. I
> see that it is called from the parser only. But when I removed it, I got
> a lot of errors.
During VDBE execution of code emitted by CREATE TABLE statement
parser is implicitly called again by sqlite3InitCallback() in OP_ParseSchema.
Thus, this function is invoked second time.
>> + /*
>> + * Beforehand initialization for correct clean-up
>> + * while emergency exiting in case of error.
>> + */
>> + const char *parent_name = NULL;
>> + const char *constraint_name = NULL;
>> + bool is_self_referenced = false;
>> + /*
>> + * Table under construction during CREATE TABLE
>> + * processing. NULL for ALTER TABLE statement handling.
>> + */
>> + struct Table *new_tab = parse_context->pNewTable;
>> + /* Whether we are processing ALTER TABLE or CREATE TABLE. */
>> + bool is_alter = new_tab == NULL;
>> + uint32_t child_cols_count;
>> + if (child_cols == NULL) {
>> + if (is_alter) {
>> + sqlite3ErrorMsg(parse_context,
>> + "referencing columns are not specified");
>> + goto exit_create_fk;
>
> 15. No test. Can not grep this message anywhere.
Well, in fact this check is redundant. Lets remove it:
@@ -2320,11 +2319,7 @@ sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
bool is_alter = new_tab == NULL;
uint32_t child_cols_count;
if (child_cols == NULL) {
- if (is_alter) {
- sqlite3ErrorMsg(parse_context, "referencing columns "\
- "are not specified");
- goto exit_create_fk;
- }
+ assert(!is_alter);
Anyway added tests:
--- a/test/sql-tap/alter2.test.lua
+++ b/test/sql-tap/alter2.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(15)
+test:plan(17)
-- This suite is aimed to test ALTER TABLE ADD CONSTRAINT statement.
--
@@ -193,4 +193,24 @@ test:do_execsql_test(
-- </alter2-3.2>
})
+test:do_catchsql_test(
+ "alter2-4.1",
+ [[
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child;
+ ]], {
+ -- <alter2-4.1>
+ 1, "near \"REFERENCES\": syntax error"
+ -- </alter2-4.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-4.2",
+ [[
+ ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child;
+ ]], {
+ -- <alter2-4.1>
+ 1, "near \"(\": syntax error"
+ -- </alter2-4.2>
+ })
>> + if (parent_cols != NULL) {
>> + if (parent_cols->nExpr != (int) child_cols_count) {
>> + sqlite3ErrorMsg(parse_context,
>> + "number of columns in foreign key does "
>> + "not match the number of columns in "
>> + "the referenced table");
>
> 16. This message appears 3 times. I think, it is worth to create a
> separate error code. Or at least remember this string somewhere in a
> variable and use it with ER_CREATE_FK_CONSTRAINT.
>
> Or add a separate label with this error and do goto when occurs.
Actually, it appears twice, but anyway done:
+ const char *error_msg = "number of columns in foreign key does not"
+ "match the number of columns in the "
+ "referenced table";
+ if (parent_cols != NULL) {
+ if (parent_cols->nExpr != (int) child_cols_count) {
+ diag_set(ClientError, ER_CREATE_FK_CONSTRAINT,
+ constraint_name, error_msg);
+ goto tnt_error;
+ }
+ } else if (!is_self_referenced) {
+ /*
+ * If parent columns are not specified, then PK columns
+ * of parent table are used as referenced.
+ */
+ struct index *parent_pk = space_index(parent_space, 0);
+ assert(parent_pk != NULL);
+ if (parent_pk->def->key_def->part_count != child_cols_count) {
+ diag_set(ClientError, ER_CREATE_FK_CONSTRAINT,
+ constraint_name, error_msg);
+ goto tnt_error;
+ }
+ }
>> + goto exit_create_fk;
>> + }
>> + } else if (!is_self_referenced) {
>> + /*
>> + * If parent columns are not specified, then PK columns
>> + * of parent table are used as referenced.
>> + */
>> + struct index *parent_pk = space_index(parent_space, 0);
>> + assert(parent_pk != NULL);
>> + if (parent_pk->def->key_def->part_count != child_cols_count) {
>> + sqlite3ErrorMsg(parse_context,
>> + "number of columns in foreign key does "
>> + "not match the number of columns in "
>> + "the referenced table");
>> + goto exit_create_fk;
>> + }
>> }
>> -
>> - /* Link the foreign key to the table as the last step.
>> + if (constraint == NULL && !is_alter) {
>> + if (parse_context->constraintName.n == 0) {
>> + uint32_t fk_count = 0;
>> + for (struct fkey_parse *fk = parse_context->new_fkey;
>> + fk != NULL; fk = fk->next, fk_count++);
>
> 17. How about store fk count in fkey_parse?
Ok, but I suggest to store it in parse context.
fkey_parse are orginized in linked list so each member of this list
would contain count of previous entry + 1. Hence, it doesn’t seem
to be reasonable to store in each parse_fkey count of FK constraints.
if (constraint == NULL && !is_alter) {
if (parse_context->constraintName.n == 0) {
- uint32_t fk_count = 0;
- for (struct fkey_parse *fk = parse_context->new_fkey;
- fk != NULL; fk = fk->next, fk_count++);
constraint_name =
sqlite3MPrintf(db, "fk_constraint_%d_%s",
- fk_count, new_tab->def->name);
+ ++parse_context->fkey_count,
+ new_tab->def->name);
} else {
+++ b/src/box/sql/sqliteInt.h
@@ -2947,6 +2947,11 @@ struct Parse {
TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */
With *pWith; /* Current WITH clause, or NULL */
With *pWithToFree; /* Free this WITH object at the end of the parse */
+ /**
+ * Number of FK constraints declared within
+ * CREATE TABLE statement.
+ */
+ uint32_t fkey_count;
>
>> diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
>> index 3183e3dc7..ad19759e2 100644
>> --- a/src/box/sql/expr.c
>> +++ b/src/box/sql/expr.c
>> @@ -4835,12 +4835,12 @@ sqlite3ExprIfFalse(Parse * pParse, Expr * pExpr, int dest, int jumpIfNull)
>> * Assert()s verify that the computation is correct.
>> */
>> - op = ((pExpr->op + (TK_ISNULL & 1)) ^ 1) - (TK_ISNULL & 1);
>> + if (pExpr->op >= TK_NE && pExpr->op <= TK_GE)
>
> 18. Why from NE to GE? In the table above I see the range [NE, LT],
> that includes [NE, GE]. Why this hunk is needed? I know about
> dependecy of opcode and token values, but why your patch breaks it?
> Can you add words in such way that they will not break parity?
This is not even my code. I was lazy to cherry-pick Nikita’s T. commit
and simply copied his code. Now, his patch has hit the trunk, so
it is disappeared from my patch after rebase on fresh 2.0.
Also, I have to cherry-pick Gosha’s fixes for bash script which
produces opcodes.h.
>> + op = ((pExpr->op + (TK_NE & 1)) ^ 1) - (TK_NE & 1);
>> + if (pExpr->op == TK_ISNULL || pExpr->op == TK_NOTNULL)
>> + op = ((pExpr->op + (TK_ISNULL & 1)) ^ 1) - (TK_ISNULL & 1);
>> - /*
>> - * Verify correct alignment of TK_ and OP_ constants.
>> - * Tokens TK_ISNULL and TK_NE shoud have the same parity.
>> - */
>> + /* Verify correct alignment of TK_ and OP_ constants. */
>> assert(pExpr->op != TK_NE || op == OP_Eq);
>> assert(pExpr->op != TK_EQ || op == OP_Ne);
>> assert(pExpr->op != TK_LT || op == OP_Ge);
>> diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
>> index 016ded8d0..1eebf6b10 100644
>> --- a/src/box/sql/fkey.c
>> +++ b/src/box/sql/fkey.c
>> @@ -39,8 +39,9 @@
>> #include "box/schema.h"
>> #include "box/session.h"
>> #include "tarantoolInt.h"
>> +#include "vdbeInt.h"
>> -#ifndef SQLITE_OMIT_FOREIGN_KEY
>> +#ifndef SQLITE_OMIT_TRIGGER
>
> 19. Why?
Idk.. Removed with unused header:
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 3989ea61f..278cf3769 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -38,9 +38,6 @@
#include "box/fkey.h"
#include "box/schema.h"
#include "box/session.h"
-#include "tarantoolInt.h"
-
-#ifndef SQLITE_OMIT_TRIGGER
>
>> @@ -366,150 +187,116 @@ sqlite3FkLocateIndex(Parse * pParse, /* Parse context to store any error in */
>> +fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
>> + struct fkey_def *fk_def, uint32_t referenced_idx,
>> + int reg_data, int incr_count, bool is_ignore)
>> {
>> + if (is_ignore == 0) {
>> + uint32_t field_count = fk_def->field_count;
>> + int temp_regs = sqlite3GetTempRange(parse_context, field_count);
>> + int rec_reg = sqlite3GetTempReg(parse_context);
>> + uint32_t id =
>> + SQLITE_PAGENO_FROM_SPACEID_AND_INDEXID(fk_def->parent_id,
>> + referenced_idx);
>
> 20. Vdbe_emit_open_cursor takes exactly index id, not pageno, on 2.0, so please,
> rebase on the latest version.
Fixed during rebasing.
>> + vdbe_emit_open_cursor(parse_context, cursor, id, parent);
>> + for (uint32_t i = 0; i < field_count; ++i) {
>> + sqlite3VdbeAddOp2(v, OP_Copy,
>> + fk_def->links[i].child_field + 1 +
>> + reg_data, temp_regs + i);
>> + }
>> + /*
>> + * If the parent table is the same as the child
>> + * table, and we are about to increment the
>> + * constraint-counter (i.e. this is an INSERT operation),
>> + * then check if the row being inserted matches itself.
>> + * If so, do not increment the constraint-counter.
>> + *
>> + * If any of the parent-key values are NULL, then
>> + * the row cannot match itself. So set JUMPIFNULL
>> + * to make sure we do the OP_Found if any of the
>> + * parent-key values are NULL (at this point it
>> + * is known that none of the child key values are).
>> + */
>> + if (parent->def->id == fk_def->child_id && incr_count == 1) {
>
> 21. What about fkey_is_self_referenced(fk_def)? Is it the same?
Yep, fixed:
+++ b/src/box/sql/fkey.c
@@ -249,7 +249,7 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
* NULL (at this point it is known that none of the child
* key values are).
*/
- if (parent->def->id == fk_def->child_id && incr_count == 1) {
+ if (fkey_is_self_referenced(fk_def) && incr_count == 1) {
>
>> + int jump = sqlite3VdbeCurrentAddr(v) + field_count + 1;
>> + for (uint32_t i = 0; i < field_count; i++) {
>> + int child_col = fk_def->links[i].child_field +
>> + 1 + reg_data;
>> + int parent_col = fk_def->links[i].parent_field +
>> + 1 + reg_data;
>> + sqlite3VdbeAddOp3(v, OP_Ne, child_col, jump,
>> + parent_col);
>> + sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
>> }
>> -
>> - sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, nCol,
>> - regRec,
>> - sqlite3IndexAffinityStr(pParse->db,
>> - pIdx), nCol);
>> - sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0);
>> - VdbeCoverage(v);
>> -
>> - sqlite3ReleaseTempReg(pParse, regRec);
>> - sqlite3ReleaseTempRange(pParse, regTemp, nCol);
>> + sqlite3VdbeGoto(v, ok_label);
>> }
>> + struct index *idx = space_index(parent, referenced_idx);
>> + assert(idx != NULL);
>> + sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count,
>> + rec_reg, sql_index_affinity_str(v->db,
>> + idx->def),
>> + P4_DYNAMIC);
>> + sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
>> + sqlite3ReleaseTempReg(parse_context, rec_reg);
>> + sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
>> }
>> -
>> - if (!pFKey->isDeferred && !(user_session->sql_flags & SQLITE_DeferFKs)
>> - && !pParse->pToplevel && !pParse->isMultiWrite) {
>> - /* Special case: If this is an INSERT statement that will insert exactly
>> - * one row into the table, raise a constraint immediately instead of
>> - * incrementing a counter. This is necessary as the VM code is being
>> + struct session *user_session = current_session();
>> + if (!fk_def->is_deferred &&
>> + !(user_session->sql_flags & SQLITE_DeferFKs) &&
>
> 22. Why do we check session flags here? They are runtime and I
> can change DeferFKs after parsing but before executing. DeferFKs is
> checked both on runtime and on parsing for unknown reason.
>
> This is not a single place of this strange thing.
I just didn’t notice it. Lets remove these checks from parser:
@@ -266,10 +263,8 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
sqlite3ReleaseTempReg(parse_context, rec_reg);
sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
- struct session *user_session = current_session();
- if (!fk_def->is_deferred &&
- (user_session->sql_flags & SQLITE_DeferFKs) == 0 &&
- parse_context->pToplevel == NULL && !parse_context->isMultiWrite) {
+ if (!fk_def->is_deferred && parse_context->pToplevel == NULL &&
+ !parse_context->isMultiWrite) {
@@ -642,9 +637,8 @@ sqlite3FkCheck(struct Parse *parser, struct Table *tab, int reg_old,
if (changed_cols != NULL &&
!fkey_parent_is_modified(fk_def, changed_cols))
continue;
- if (!fk_def->is_deferred &&
- (user_session->sql_flags & SQLITE_DeferFKs) == 0 &&
- parser->pToplevel == NULL && !parser->isMultiWrite) {
+ if (!fk_def->is_deferred && parser->pToplevel == NULL &&
+ !parser->isMultiWrite) {
@@ -819,12 +813,10 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct fkey *fkey,
bool is_update)
{
sqlite3 *db = pParse->db; /* Database handle */
- struct session *user_session = current_session();
struct fkey_def *fk_def = fkey->def;
enum fkey_action action = is_update ? fk_def->on_update :
fk_def->on_delete;
- if (action == FKEY_ACTION_RESTRICT &&
- (user_session->sql_flags & SQLITE_DeferFKs))
>
>> @@ -844,59 +586,31 @@ sqlite3FkCheck(Parse * pParse, /* Parse context */
>> if ((user_session->sql_flags & SQLITE_ForeignKeys) == 0)
>> return;
>> - /* Loop through all the foreign key constraints for which pTab is the
>> - * child table (the table that the foreign key definition is part of).
>> + /*
>> + * Loop through all the foreign key constraints for which
>> + * pTab is the child table.
>> */
>> - for (pFKey = pTab->pFKey; pFKey; pFKey = pFKey->pNextFrom) {
>> - Table *pTo; /* Parent table of foreign key pFKey */
>> - Index *pIdx = 0; /* Index on key columns in pTo */
>> - int *aiFree = 0;
>> - int *aiCol;
>> - int iCol;
>> - int i;
>> + struct space *space = space_by_id(pTab->def->id);
>> + assert(space != NULL);
>> + for (struct fkey *fk = space->child_fkey; fk != NULL;
>> + fk = fk->fkey_child_next) {
>> + struct fkey_def *fk_def = fk->def;
>> int bIgnore = 0;
>> -
>> - if (aChange
>> - && sqlite3_stricmp(pTab->def->name, pFKey->zTo) != 0
>> - && fkChildIsModified(pFKey, aChange) == 0) {
>> + if (aChange != NULL && space->def->id != fk_def->parent_id &&
>
> 23. fkey_is_self_referenced?
Yep.
@@ -598,7 +598,7 @@ sqlite3FkCheck(struct Parse *parser, struct Table *tab, int reg_old,
fk = fk->fkey_child_next) {
struct fkey_def *fk_def = fk->def;
if (changed_cols != NULL &&
- space->def->id != fk_def->parent_id &&
+ !fkey_is_self_referenced(fk_def) &&
>> + !fkey_child_is_modified(fk_def, aChange))
>> continue;
>> - }
>> -
>> @@ -977,100 +686,74 @@ sqlite3FkCheck(Parse * pParse, /* Parse context */
>> * might be set incorrectly if any OP_FkCounter related scans are
>> * omitted.
>> */
>> - if (!pFKey->isDeferred && eAction != OE_Cascade
>> - && eAction != OE_SetNull) {
>> + if (!fk_def->is_deferred &&
>> + action != FKEY_ACTION_CASCADE &&
>> + action != FKEY_ACTION_SET_NULL) {
>> sqlite3MayAbort(pParse);
>> }
>> }
>> - pItem->zName = 0;
>> sqlite3SrcListDelete(db, pSrc);
>> }
>> - sqlite3DbFree(db, aiCol);
>> }
>> }
>> #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
>
> 24. Lets use 64 bitmask and utilities from column_mask.h.
It is not so easy to do: the same mask is used for triggers as well.
So if we want to change format of mast for FK, we should do it
almost everywhere in SQL source code. I may do it as separate
follow-up patch, if you wish.
>> -/*
>> - * This function is called before generating code to update or delete a
>> - * row contained in table pTab.
>> - */
>> -u32
>> -sqlite3FkOldmask(Parse * pParse, /* Parse context */
>> - Table * pTab /* Table being modified */
>> - )
>> +uint32_t
>> +fkey_old_mask(uint32_t space_id)
>
> 25. I think we should calculate this mask once on fk creation
> like it is done for key_def.columnm_mask.
In fact, this mask is calculated for whole space (i.e. all of its FK constraints),
not for particular FK. So basically, we need to add this mask to space_def/space
and update on each FK creation. Is this OK?
>> {
>> - u32 mask = 0;
>> + uint32_t mask = 0;
>> struct session *user_session = current_session();
>> -
>> if (user_session->sql_flags & SQLITE_ForeignKeys) {
>> - FKey *p;
>> - int i;
>> - for (p = pTab->pFKey; p; p = p->pNextFrom) {
>> - for (i = 0; i < p->nCol; i++)
>> - mask |= COLUMN_MASK(p->aCol[i].iFrom);
>> + struct space *space = space_by_id(space_id);
>> + for (struct fkey *fk = space->child_fkey; fk != NULL;
>> + fk = fk->fkey_child_next) {
>> + struct fkey_def *def = fk->def;
>> + for (uint32_t i = 0; i < def->field_count; ++i)
>> + mask |=COLUMN_MASK(def->links[i].child_field);
>> }
>> - for (p = sqlite3FkReferences(pTab); p; p = p->pNextTo) {
>> - Index *pIdx = 0;
>> - sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
>> - if (pIdx) {
>> - int nIdxCol = index_column_count(pIdx);
>> - for (i = 0; i < nIdxCol; i++) {
>> - assert(pIdx->aiColumn[i] >= 0);
>> - mask |= COLUMN_MASK(pIdx->aiColumn[i]);
>> - }
>> - }
>> + for (struct fkey *fk = space->parent_fkey; fk != NULL;
>> + fk = fk->fkey_parent_next) {
>> + struct fkey_def *def = fk->def;
>> + for (uint32_t i = 0; i < def->field_count; ++i)
>> + mask |= COLUMN_MASK(def->links[i].parent_field);
>> }
>> }
>> return mask;
>> }
>> diff --git a/src/box/sql/main.c b/src/box/sql/main.c
>> index 00dc7a631..618cdc420 100644
>> --- a/src/box/sql/main.c
>> +++ b/src/box/sql/main.c
>> @@ -730,11 +730,6 @@ sqlite3RollbackAll(Vdbe * pVdbe, int tripCode)
>> {
>> sqlite3 *db = pVdbe->db;
>> (void)tripCode;
>> - struct session *user_session = current_session();
>> -
>> - /* DDL is impossible inside a transaction. */
>> - assert((user_session->sql_flags & SQLITE_InternChanges) == 0
>> - || db->init.busy == 1);
>
> 26. Why?
This assert seems to be broken or to be very ancient thing.
In fact, busy flag has nothing in common with DDL.
For this reason also fails drop of triggers:
https://github.com/tarantool/tarantool/issues/3529
>
>> /* If one has been configured, invoke the rollback-hook callback */
>> if (db->xRollbackCallback && (!pVdbe->auto_commit)) {
>> diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
>> index b2940b7c4..1b84dbcaa 100644
>> --- a/src/box/sql/parse.y
>> +++ b/src/box/sql/parse.y
>> @@ -300,19 +301,23 @@ autoinc(X) ::= AUTOINCR. {X = 1;}
>> // check fails.
>> //
>> %type refargs {int}
>> -refargs(A) ::= . { A = ON_CONFLICT_ACTION_NONE*0x0101; /* EV: R-19803-45884 */}
>> +refargs(A) ::= . { A = FKEY_NO_ACTION; }
>> refargs(A) ::= refargs(A) refarg(Y). { A = (A & ~Y.mask) | Y.value; }
>> %type refarg {struct {int value; int mask;}}
>> -refarg(A) ::= MATCH nm. { A.value = 0; A.mask = 0x000000; }
>> +refarg(A) ::= MATCH matcharg(X). { A.value = X<<16; A.mask = 0xff0000; }
>
> 27. Why exactly 16? Why can not remain 0, or be << 2, or << 4?
Idk, just because :) Yep, it will work with 0:
-refarg(A) ::= MATCH matcharg(X). { A.value = X<<16; A.mask = 0xff0000; }
+refarg(A) ::= MATCH matcharg(X). { A.value = X; A.mask = 0xff0000; }
>> refarg(A) ::= ON INSERT refact. { A.value = 0; A.mask = 0x000000; }
>> refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; }
>> refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; }
>> diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
>> index 5c5369aeb..2489b31b2 100644
>> --- a/src/box/sql/sqliteInt.h
>> +++ b/src/box/sql/sqliteInt.h> @@ -4280,8 +4271,57 @@ sql_trigger_colmask(Parse *parser, struct sql_trigger *trigger,
>> #define sqlite3IsToplevel(p) ((p)->pToplevel==0)
>> int sqlite3JoinType(Parse *, Token *, Token *, Token *);
>> -void sqlite3CreateForeignKey(Parse *, ExprList *, Token *, ExprList *, int);
>> -void sqlite3DeferForeignKey(Parse *, int);
>> +
>> +/**
>> + * Change defer mode of last FK constraint processed during
>> + * <CREATE TABLE> statement.
>
> 28. 'CREATE and ALTER', it is not?
Not exactly, it is quite tricky place. When we are processing ALTER
we can unambiguously point out DEFER clause:
cmd ::= ALTER TABLE fullname(X) ADD CONSTRAINT nm(Z) FOREIGN KEY
LP eidlist(FA) RP REFERENCES nm(T) eidlist_opt(TA) refargs(R)
defer_subclause_opt(D). {
sql_create_foreign_key(pParse, X, &Z, FA, &T, TA, D, R);
}
So, we pass it directly to sql_create_foreign_key()
When we are handling CREATE TABLE, grammar features ambiguous
statement. It is a long story, but in a nutshell I didn’t manage to
put defer_subclause(D) (even modified) right after REFERENCES clause.
So, I just keep things as they were in original SQLite.
>> + *
>> + * @param parse_context Current parsing context.
>> + * @param is_deferred Change defer mode to this value.
>> + */
>> +void
>> +fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred);
>> +
>> diff --git a/test/sql-tap/fkey1.test.lua b/test/sql-tap/fkey1.test.lua
>> index 494af4b4a..3c29b097d 100755
>> --- a/test/sql-tap/fkey1.test.lua
>> +++ b/test/sql-tap/fkey1.test.lua
>> @@ -17,10 +17,10 @@ test:do_execsql_test(
>> "fkey1-1.2",
>> [[
>> CREATE TABLE t1(
>> - a INTEGER PRIMARY KEY,
>> + a PRIMARY KEY,
>
> 29. Why not INTEGER? As I know, we are going to forbid type omitting.
> Same for other tests (for example: fkey3.test.lua, orderby1.test.lua).
Because types of referenced and referencing fields must match.
And the only way to get INT type is to declare field as INT PRIMARY KEY.
Hence, if referencing column is INT but not PK, it will be stored with SCALAR.
In its turn SCALAR can’t be mapped to INT.
I know that it kind of contradicts our plans on static typing, but I had nothing
left to do...
>
>> b INTEGER
>> REFERENCES t1 ON DELETE CASCADE
>> - REFERENCES t2,
>> + REFERENCES t2 (x),
>> c TEXT,
>> FOREIGN KEY (b, c) REFERENCES t2(x, y) ON UPDATE CASCADE);
>> ]], {
>> diff --git a/test/sql-tap/fkey4.test.lua b/test/sql-tap/fkey4.test.lua
>> index 9415b62cb..9810ce22f 100755
>> --- a/test/sql-tap/fkey4.test.lua
>> +++ b/test/sql-tap/fkey4.test.lua
>> @@ -186,7 +186,7 @@ test:do_execsql_test(
>> DROP TABLE IF EXISTS c1;
>> DROP TABLE IF EXISTS p1;
>> CREATE TABLE p1(a PRIMARY KEY, b);
>> - CREATE TABLE c1(x PRIMARY KEY REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);
>> + CREATE TABLE c1(x PRIMARY KEY REFERENCES p1);
>
> 30. Why?
Seems like I accidentally changed it. Returned back:
+++ b/test/sql-tap/fkey4.test.lua
@@ -186,7 +186,7 @@ test:do_execsql_test(
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
CREATE TABLE p1(a PRIMARY KEY, b);
- CREATE TABLE c1(x PRIMARY KEY REFERENCES p1);
+ CREATE TABLE c1(x PRIMARY KEY REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);
>> INSERT INTO p1 VALUES (1, 'one');
>> INSERT INTO p1 VALUES (2, 'two');
>> INSERT INTO c1 VALUES (1);
>> diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
>> index 6aa290742..24f494852 100755
>> --- a/test/sql-tap/table.test.lua
>> +++ b/test/sql-tap/table.test.lua
>> @@ -791,14 +791,16 @@ test:do_catchsql_test(
>> );
>> ]], {
>> -- <table-10.7>
>> - 0
>> + 1, "table \"T4\" doesn't feature column B"
>> -- </table-10.7>
>> })
>> test:do_catchsql_test(
>> "table-10.8",
>> [[
>> - DROP TABLE t6;
>> + DROP TABLE IF EXISTS t6;
>> + DROP TABLE IF EXISTS t4;
>
> 31. Indentation.
Fixed.
>
>> + CREATE TABLE t4(x UNIQUE, y, PRIMARY KEY (x, y));
>> CREATE TABLE t6(a primary key,b,c,
>> FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
>> ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
>> @@ -861,7 +863,7 @@ test:do_test(
>> ]]
>> end, {
>> -- <table-10.12>
>> - 1, [[unknown column "X" in foreign key definition]]
>> + 1, [[no such column X]]
>
> 32. Can you keep the old message?
Sure:
@@ -1629,7 +1629,8 @@ resolve_link(struct Parse *parse_context, const struct space_def *def,
return 0;
}
}
- sqlite3ErrorMsg(parse_context, "no such column %s", field_name);
+ sqlite3ErrorMsg(parse_context, "unknown column %s in foreign key "
+ "definition", field_name);
Full patch after changes is below:
===============================================================================
Subject: [PATCH 3/5] sql: introduce ADD CONSTRAINT statement
After introducing separate space for persisting foreign key
constraints, nothing prevents us from adding ALTER TABLE statement to
add or drop named constraints. According to ANSI syntax is following:
ALTER TABLE <referencing table> ADD CONSTRAINT
<referential constraint name> FOREIGN KEY
<left paren> <referencing columns> <right paren> REFERENCES
<referenced table> [ <referenced columns> ] [ MATCH <match type> ]
[ <referential triggered action> ] [ <constraint check time> ]
ALTER TABLE <referencing table> DROP CONSTRAINT <constrain name>
In our terms it looks like:
ALTER TABLE t1 ADD CONSTRAINT f1 FOREIGN KEY(id, a)
REFERENCES t2 (id, b) MATCH FULL;
ALTER TABLE t1 DROP CONSTRAINT f1;
FK constraints which come with CREATE TABLE statement are also
persisted with auto-generated name. They are coded after space and its
indexes.
Moreover, we don't use original SQLite foreign keys anymore: those
obsolete structs have been removed alongside FK hash. Now FK constraints
are stored only in space.
Since types of referencing and referenced fields must match, and now in
SQL only PK is allowed to feature INT (other fields are always SCALAR),
some tests have been corrected to obey this rule.
Part of #3271
---
extra/mkkeywordhash.c | 3 +
extra/mkopcodeh.sh | 33 +-
src/box/fkey.c | 1 +
src/box/sql.c | 113 +--
src/box/sql/alter.c | 82 ---
src/box/sql/build.c | 579 +++++++++++----
src/box/sql/callback.c | 10 +-
src/box/sql/delete.c | 6 +-
src/box/sql/fkey.c | 1333 +++++++++++++---------------------
src/box/sql/insert.c | 24 +-
src/box/sql/main.c | 5 -
src/box/sql/parse.y | 37 +-
src/box/sql/pragma.c | 239 +-----
src/box/sql/pragma.h | 11 +-
src/box/sql/prepare.c | 4 +
src/box/sql/sqliteInt.h | 176 +++--
src/box/sql/status.c | 9 +-
src/box/sql/tarantoolInt.h | 17 +-
src/box/sql/update.c | 4 +-
src/box/sql/vdbe.c | 16 +-
test/sql-tap/alter.test.lua | 4 +-
test/sql-tap/alter2.test.lua | 216 ++++++
test/sql-tap/engine.cfg | 3 +
test/sql-tap/fkey1.test.lua | 51 +-
test/sql-tap/fkey2.test.lua | 125 +---
test/sql-tap/fkey3.test.lua | 15 +-
test/sql-tap/orderby1.test.lua | 6 +-
test/sql-tap/table.test.lua | 22 +-
test/sql-tap/tkt-b1d3a2e531.test.lua | 4 +-
test/sql-tap/triggerC.test.lua | 2 +-
test/sql-tap/whereG.test.lua | 4 +-
test/sql-tap/with1.test.lua | 2 +-
32 files changed, 1471 insertions(+), 1685 deletions(-)
create mode 100755 test/sql-tap/alter2.test.lua
diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec153815..6a6f96f53 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -159,6 +159,7 @@ static Keyword aKeywordTable[] = {
{ "FOR", "TK_FOR", TRIGGER, true },
{ "FOREIGN", "TK_FOREIGN", FKEY, true },
{ "FROM", "TK_FROM", ALWAYS, true },
+ { "FULL", "TK_FULL", ALWAYS, true },
{ "GLOB", "TK_LIKE_KW", ALWAYS, false },
{ "GROUP", "TK_GROUP", ALWAYS, true },
{ "HAVING", "TK_HAVING", ALWAYS, true },
@@ -191,6 +192,7 @@ static Keyword aKeywordTable[] = {
{ "OR", "TK_OR", ALWAYS, true },
{ "ORDER", "TK_ORDER", ALWAYS, true },
{ "OUTER", "TK_JOIN_KW", ALWAYS, true },
+ { "PARTIAL", "TK_PARTIAL", ALWAYS, true },
{ "PLAN", "TK_PLAN", EXPLAIN, false },
{ "PRAGMA", "TK_PRAGMA", PRAGMA, true },
{ "PRIMARY", "TK_PRIMARY", ALWAYS, true },
@@ -210,6 +212,7 @@ static Keyword aKeywordTable[] = {
{ "SAVEPOINT", "TK_SAVEPOINT", ALWAYS, true },
{ "SELECT", "TK_SELECT", ALWAYS, true },
{ "SET", "TK_SET", ALWAYS, true },
+ { "SIMPLE", "TK_SIMPLE", ALWAYS, true },
{ "START", "TK_START", ALWAYS, true },
{ "TABLE", "TK_TABLE", ALWAYS, true },
{ "THEN", "TK_THEN", ALWAYS, true },
diff --git a/extra/mkopcodeh.sh b/extra/mkopcodeh.sh
index 63ad0d56a..9e97a50f0 100755
--- a/extra/mkopcodeh.sh
+++ b/extra/mkopcodeh.sh
@@ -35,6 +35,7 @@ set -f # disable pathname expansion
currentOp=""
nOp=0
+mxTk=-1
newline="$(printf '\n')"
IFS="$newline"
while read line; do
@@ -106,6 +107,9 @@ while read line; do
eval "ARRAY_used_$val=1"
eval "ARRAY_sameas_$val=$sym"
eval "ARRAY_def_$val=$name"
+ if [ $val -gt $mxTk ] ; then
+ mxTk=$val
+ fi
fi
;;
jump) eval "ARRAY_jump_$name=1" ;;
@@ -220,8 +224,12 @@ while [ "$i" -lt "$nOp" ]; do
i=$((i + 1))
done
max="$cnt"
+echo "//*************** $max $nOp $mxTk"
+if [ $mxTk -lt $nOp ] ; then
+ mxTk=$nOp
+fi
i=0
-while [ "$i" -lt "$nOp" ]; do
+while [ "$i" -le "$mxTk" ]; do
eval "used=\${ARRAY_used_$i:-}"
if [ -z "$used" ]; then
eval "ARRAY_def_$i=OP_NotUsed_$i"
@@ -251,9 +259,21 @@ done
# Generate the bitvectors:
ARRAY_bv_0=0
i=0
-while [ "$i" -le "$max" ]; do
+while [ "$i" -le "$mxTk" ]; do
+ eval "is_exists=\${ARRAY_def_$i:-}"
+ if [ ! -n "$is_exists" ] ; then
+ echo "//SKIP $i"
+ i=$((i + 1))
+ continue
+ fi
eval "name=\$ARRAY_def_$i"
x=0
+ eval "is_exists=\${ARRAY_jump_$name:-}"
+ if [ ! -n "$is_exists" ] ; then
+ echo "//SKIP2 $i"
+ i=$((i + 1))
+ continue
+ fi
eval "jump=\$ARRAY_jump_$name"
eval "in1=\$ARRAY_in1_$name"
eval "in2=\$ARRAY_in2_$name"
@@ -283,11 +303,16 @@ printf '%s\n' "#define OPFLG_OUT2 0x10 /* out2: P2 is an output */"
printf '%s\n' "#define OPFLG_OUT3 0x20 /* out3: P3 is an output */"
printf '%s\n' "#define OPFLG_INITIALIZER {\\"
i=0
-while [ "$i" -le "$max" ]; do
+while [ "$i" -le "$mxTk" ]; do
if [ "$((i % 8))" -eq 0 ]; then
printf '/* %3d */' "$i"
fi
- eval "bv=\$ARRAY_bv_$i"
+ eval "is_exists=\${ARRAY_bv_$i:-}"
+ if [ ! -n "$is_exists" ] ; then
+ bv=0
+ else
+ eval "bv=\$ARRAY_bv_$i"
+ fi
printf ' 0x%02x,' "$bv"
if [ "$((i % 8))" -eq 7 ]; then
printf '%s\n' "\\"
diff --git a/src/box/fkey.c b/src/box/fkey.c
index b3980c874..8b7f5130c 100644
--- a/src/box/fkey.c
+++ b/src/box/fkey.c
@@ -28,6 +28,7 @@
* THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
*/
+
#include "fkey.h"
#include "sql.h"
#include "sql/sqliteInt.h"
diff --git a/src/box/sql.c b/src/box/sql.c
index d4b0d7fcc..c51617ce4 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -55,6 +55,7 @@
#include "session.h"
#include "xrow.h"
#include "iproto_constants.h"
+#include "fkey.h"
static sqlite3 *db = NULL;
@@ -839,103 +840,6 @@ rename_fail:
return SQL_TARANTOOL_ERROR;
}
-/*
- * Acts almost as tarantoolSqlite3RenameTable, but doesn't change
- * name of table, only statement.
- */
-int tarantoolSqlite3RenameParentTable(int space_id, const char *old_parent_name,
- const char *new_parent_name)
-{
- assert(space_id != 0);
- assert(old_parent_name != NULL);
- assert(new_parent_name != NULL);
-
- box_tuple_t *tuple;
- uint32_t key_len = mp_sizeof_uint(space_id) + mp_sizeof_array(1);
-
- char *key_begin = (char*) region_alloc(&fiber()->gc, key_len);
- if (key_begin == NULL) {
- diag_set(OutOfMemory, key_len, "region_alloc", "key_begin");
- return SQL_TARANTOOL_ERROR;
- }
- char *key = mp_encode_array(key_begin, 1);
- key = mp_encode_uint(key, space_id);
- if (box_index_get(BOX_SPACE_ID, 0, key_begin, key, &tuple) != 0)
- return SQL_TARANTOOL_ERROR;
- assert(tuple != NULL);
-
- assert(tuple_field_count(tuple) == 7);
- const char *sql_stmt_map = box_tuple_field(tuple, 5);
-
- if (sql_stmt_map == NULL || mp_typeof(*sql_stmt_map) != MP_MAP)
- goto rename_fail;
- uint32_t map_size = mp_decode_map(&sql_stmt_map);
- if (map_size != 1)
- goto rename_fail;
- const char *sql_str = mp_decode_str(&sql_stmt_map, &key_len);
- if (sqlite3StrNICmp(sql_str, "sql", 3) != 0)
- goto rename_fail;
- uint32_t create_stmt_decoded_len;
- const char *create_stmt_old = mp_decode_str(&sql_stmt_map,
- &create_stmt_decoded_len);
- uint32_t old_name_len = strlen(old_parent_name);
- uint32_t new_name_len = strlen(new_parent_name);
- char *create_stmt_new = (char*) region_alloc(&fiber()->gc,
- create_stmt_decoded_len + 1);
- if (create_stmt_new == NULL) {
- diag_set(OutOfMemory, create_stmt_decoded_len + 1,
- "region_alloc", "create_stmt_new");
- return SQL_TARANTOOL_ERROR;
- }
- memcpy(create_stmt_new, create_stmt_old, create_stmt_decoded_len);
- create_stmt_new[create_stmt_decoded_len] = '\0';
- uint32_t numb_of_quotes = 0;
- uint32_t numb_of_occurrences = 0;
- create_stmt_new = rename_parent_table(db, create_stmt_new, old_parent_name,
- new_parent_name, &numb_of_occurrences,
- &numb_of_quotes);
- uint32_t create_stmt_new_len = create_stmt_decoded_len -
- numb_of_occurrences *
- (old_name_len - new_name_len) +
- 2 * numb_of_quotes;
- assert(create_stmt_new_len > 0);
-
- key_len = tuple->bsize + mp_sizeof_str(create_stmt_new_len);
- char *new_tuple = (char*)region_alloc(&fiber()->gc, key_len);
- if (new_tuple == NULL) {
- sqlite3DbFree(db, create_stmt_new);
- diag_set(OutOfMemory, key_len, "region_alloc", "new_tuple");
- return SQL_TARANTOOL_ERROR;
- }
-
- char *new_tuple_end = new_tuple;
- const char *data_begin = tuple_data(tuple);
- const char *data_end = tuple_field(tuple, 5);
- uint32_t data_size = data_end - data_begin;
- memcpy(new_tuple, data_begin, data_size);
- new_tuple_end += data_size;
- new_tuple_end = mp_encode_map(new_tuple_end, 1);
- new_tuple_end = mp_encode_str(new_tuple_end, "sql", 3);
- new_tuple_end = mp_encode_str(new_tuple_end, create_stmt_new,
- create_stmt_new_len);
- sqlite3DbFree(db, create_stmt_new);
- data_begin = tuple_field(tuple, 6);
- data_end = (char*) tuple + tuple_size(tuple);
- data_size = data_end - data_begin;
- memcpy(new_tuple_end, data_begin, data_size);
- new_tuple_end += data_size;
-
- if (box_replace(BOX_SPACE_ID, new_tuple, new_tuple_end, NULL) != 0)
- return SQL_TARANTOOL_ERROR;
- else
- return SQLITE_OK;
-
-rename_fail:
- diag_set(ClientError, ER_SQL_EXECUTE, "can't modify name of space "
- "created not via SQL facilities");
- return SQL_TARANTOOL_ERROR;
-}
-
int
tarantoolSqlite3IdxKeyCompare(struct BtCursor *cursor,
struct UnpackedRecord *unpacked)
@@ -1489,6 +1393,21 @@ tarantoolSqlite3MakeTableOpts(Table *pTable, const char *zSql, char *buf)
return p - buf;
}
+int
+fkey_encode_links(const struct fkey_def *fkey, char *buf)
+{
+ const struct Enc *enc = get_enc(buf);
+ char *p = enc->encode_array(buf, fkey->field_count);
+ for (uint32_t i = 0; i < fkey->field_count; ++i) {
+ p = enc->encode_map(p, 2);
+ p = enc->encode_str(p, "child", strlen("child"));
+ p = enc->encode_uint(p, fkey->links[i].child_field);
+ p = enc->encode_str(p, "parent", strlen("parent"));
+ p = enc->encode_uint(p, fkey->links[i].parent_field);
+ }
+ return p - buf;
+}
+
/*
* Format "parts" array for _index entry.
* Returns result size.
diff --git a/src/box/sql/alter.c b/src/box/sql/alter.c
index 8c1c36b9b..0e770272e 100644
--- a/src/box/sql/alter.c
+++ b/src/box/sql/alter.c
@@ -151,7 +151,6 @@ sqlite3AlterFinishAddColumn(Parse * pParse, Token * pColDef)
Expr *pDflt; /* Default value for the new column */
sqlite3 *db; /* The database connection; */
Vdbe *v = pParse->pVdbe; /* The prepared statement under construction */
- struct session *user_session = current_session();
db = pParse->db;
if (pParse->nErr || db->mallocFailed)
@@ -190,12 +189,6 @@ sqlite3AlterFinishAddColumn(Parse * pParse, Token * pColDef)
sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column");
return;
}
- if ((user_session->sql_flags & SQLITE_ForeignKeys) && pNew->pFKey
- && pDflt) {
- sqlite3ErrorMsg(pParse,
- "Cannot add a REFERENCES column with non-NULL default value");
- return;
- }
assert(pNew->def->fields[pNew->def->field_count - 1].is_nullable ==
action_is_nullable(pNew->def->fields[
pNew->def->field_count - 1].nullable_action));
@@ -403,81 +396,6 @@ rename_table(sqlite3 *db, const char *sql_stmt, const char *table_name,
return new_sql_stmt;
}
-/*
- * This function is used by the ALTER TABLE ... RENAME command to modify the
- * definition of any foreign key constraints that used the table being renamed
- * as the parent table. All substituted occurrences will be quoted.
- * It returns the new CREATE TABLE statement. Memory for the new statement
- * will be automatically freed by VDBE.
- *
- * Usage example:
- *
- * sqlite_rename_parent('CREATE TABLE t1(a REFERENCES t2)', 't2', 't3')
- * -> 'CREATE TABLE t1(a REFERENCES "t3")'
- *
- * @param sql_stmt text of a child CREATE TABLE statement being modified
- * @param old_name old name of the table being renamed
- * @param new_name new name of the table being renamed
- * @param[out] numb_of_occurrences number of occurrences of old_name in sql_stmt
- * @param[out] numb_of_unquoted number of unquoted occurrences of old_name
- *
- * @retval new SQL statement on success, empty string otherwise.
- */
-char*
-rename_parent_table(sqlite3 *db, const char *sql_stmt, const char *old_name,
- const char *new_name, uint32_t *numb_of_occurrences,
- uint32_t *numb_of_unquoted)
-{
- assert(sql_stmt);
- assert(old_name);
- assert(new_name);
- assert(numb_of_occurrences);
- assert(numb_of_unquoted);
-
- char *output = NULL;
- char *new_sql_stmt;
- const char *csr; /* Pointer to token */
- int n; /* Length of token z */
- int token; /* Type of token */
- bool unused;
- bool is_quoted;
-
- for (csr = sql_stmt; *csr; csr = csr + n) {
- n = sql_token(csr, &token, &unused);
- if (token == TK_REFERENCES) {
- char *zParent;
- do {
- csr += n;
- n = sql_token(csr, &token, &unused);
- } while (token == TK_SPACE);
- if (token == TK_ILLEGAL)
- break;
- zParent = sqlite3DbStrNDup(db, csr, n);
- if (zParent == 0)
- break;
- is_quoted = *zParent == '"' ? true : false;
- sqlite3NormalizeName(zParent);
- if (0 == strcmp(old_name, zParent)) {
- (*numb_of_occurrences)++;
- if (!is_quoted)
- (*numb_of_unquoted)++;
- char *zOut = sqlite3MPrintf(db, "%s%.*s\"%w\"",
- (output ? output : ""),
- (int)((char*)csr - sql_stmt),
- sql_stmt, new_name);
- sqlite3DbFree(db, output);
- output = zOut;
- sql_stmt = &csr[n];
- }
- sqlite3DbFree(db, zParent);
- }
- }
-
- new_sql_stmt = sqlite3MPrintf(db, "%s%s", (output ? output : ""), sql_stmt);
- sqlite3DbFree(db, output);
- return new_sql_stmt;
-}
-
/* This function is used to implement the ALTER TABLE command.
* The table name in the CREATE TRIGGER statement is replaced with the third
* argument and the result returned. This is analagous to rename_table()
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 789a628d6..fc097a319 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -47,6 +47,7 @@
#include "vdbeInt.h"
#include "tarantoolInt.h"
#include "box/box.h"
+#include "box/fkey.h"
#include "box/sequence.h"
#include "box/session.h"
#include "box/identifier.h"
@@ -332,9 +333,6 @@ deleteTable(sqlite3 * db, Table * pTable)
freeIndex(db, pIndex);
}
- /* Delete any foreign keys attached to this table. */
- sqlite3FkDelete(db, pTable);
-
/* Delete the Table structure itself.
*/
sqlite3HashClear(&pTable->idxHash);
@@ -1551,6 +1549,91 @@ emitNewSysSpaceSequenceRecord(Parse *pParse, int space_id, const char reg_seq_id
return first_col;
}
+/**
+ * Generate opcodes to serialize foreign key into MgsPack and
+ * insert produced tuple into _fk_constraint space.
+ *
+ * @param parse_context Parsing context.
+ * @param fk Foreign key to be created.
+ */
+static void
+vdbe_emit_fkey_create(struct Parse *parse_context, const struct fkey_def *fk)
+{
+ assert(parse_context != NULL);
+ assert(fk != NULL);
+ struct Vdbe *vdbe = sqlite3GetVdbe(parse_context);
+ assert(vdbe != NULL);
+ /*
+ * Occupy registers for 8 fields: each member in
+ * _constraint space plus one for final msgpack tuple.
+ */
+ int constr_tuple_reg = sqlite3GetTempRange(parse_context, 9);
+ const char *name_copy = sqlite3DbStrDup(parse_context->db, fk->name);
+ if (name_copy == NULL)
+ return;
+ sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg, 0, name_copy,
+ P4_DYNAMIC);
+ /*
+ * In case we are adding FK constraints during execution
+ * of <CREATE TABLE ...> statement, we don't have child
+ * id, but we know register where it will be stored.
+ * */
+ if (parse_context->pNewTable != NULL) {
+ sqlite3VdbeAddOp2(vdbe, OP_SCopy, fk->child_id,
+ constr_tuple_reg + 1);
+ } else {
+ sqlite3VdbeAddOp2(vdbe, OP_Integer, fk->child_id,
+ constr_tuple_reg + 1);
+ }
+ if (parse_context->pNewTable != NULL && fkey_is_self_referenced(fk)) {
+ sqlite3VdbeAddOp2(vdbe, OP_SCopy, fk->parent_id,
+ constr_tuple_reg + 2);
+ } else {
+ sqlite3VdbeAddOp2(vdbe, OP_Integer, fk->parent_id,
+ constr_tuple_reg + 2);
+ }
+ sqlite3VdbeAddOp2(vdbe, OP_Bool, 0, constr_tuple_reg + 3);
+ sqlite3VdbeChangeP4(vdbe, -1, (char*)&fk->is_deferred, P4_BOOL);
+ sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 4, 0,
+ fkey_match_strs[fk->match], P4_STATIC);
+ sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 5, 0,
+ fkey_action_strs[fk->on_delete], P4_STATIC);
+ sqlite3VdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 6, 0,
+ fkey_action_strs[fk->on_update], P4_STATIC);
+ size_t encoded_links_sz = fkey_encode_links(fk, NULL);
+ char *encoded_links = sqlite3DbMallocRaw(parse_context->db,
+ encoded_links_sz);
+ if (encoded_links == NULL) {
+ sqlite3DbFree(parse_context->db, (void *) name_copy);
+ return;
+ }
+ size_t real_links_sz = fkey_encode_links(fk, encoded_links);
+ sqlite3VdbeAddOp4(vdbe, OP_Blob, real_links_sz, constr_tuple_reg + 7,
+ SQL_SUBTYPE_MSGPACK, encoded_links, P4_DYNAMIC);
+ sqlite3VdbeAddOp3(vdbe, OP_MakeRecord, constr_tuple_reg, 8,
+ constr_tuple_reg + 8);
+ sqlite3VdbeAddOp2(vdbe, OP_SInsert, BOX_FK_CONSTRAINT_ID,
+ constr_tuple_reg + 8);
+ sqlite3VdbeChangeP5(vdbe, OPFLAG_NCHANGE);
+ sqlite3ReleaseTempRange(parse_context, constr_tuple_reg, 9);
+}
+
+static int
+resolve_link(struct Parse *parse_context, const struct space_def *def,
+ const char *field_name, uint32_t *link)
+{
+ assert(link != NULL);
+ for (uint32_t j = 0; j < def->field_count; ++j) {
+ if (strcmp(field_name, def->fields[j].name) == 0) {
+ *link = j;
+ return 0;
+ }
+ }
+ sqlite3ErrorMsg(parse_context, "unknown column %s in foreign key "
+ "definition", field_name);
+ return -1;
+}
+
/*
* This routine is called to report the final ")" that terminates
* a CREATE TABLE statement.
@@ -1720,6 +1803,43 @@ sqlite3EndTable(Parse * pParse, /* Parse context */
/* Reparse everything to update our internal data structures */
parseTableSchemaRecord(pParse, iSpaceId, zStmt); /* consumes zStmt */
+
+ /* Code creation of FK constraints, if any. */
+ struct fkey_parse *fk_parse;
+ rlist_foreach_entry(fk_parse, &pParse->new_fkey, link) {
+ struct fkey_def *fk = fk_parse->fkey;
+ if (fk_parse->selfref_cols != NULL) {
+ struct ExprList *cols = fk_parse->selfref_cols;
+ for (uint32_t i = 0; i < fk->field_count; ++i) {
+ if (resolve_link(pParse, p->def,
+ cols->a[i].zName,
+ &fk->links[i].parent_field) != 0)
+ return;
+ }
+ fk->parent_id = iSpaceId;
+ } else if (fk_parse->is_self_referenced) {
+ struct Index *pk = sqlite3PrimaryKeyIndex(p);
+ if (pk->def->key_def->part_count !=
+ fk->field_count) {
+ diag_set(ClientError,
+ ER_CREATE_FK_CONSTRAINT,
+ fk->name, "number of columns "
+ "in foreign key does not "
+ "match the number of columns "
+ "in the referenced table");
+ pParse->rc = SQL_TARANTOOL_ERROR;
+ pParse->nErr++;
+ return;
+ }
+ for (uint32_t i = 0; i < fk->field_count; ++i) {
+ fk->links[i].parent_field =
+ pk->def->key_def->parts[i].fieldno;
+ }
+ fk->parent_id = iSpaceId;
+ }
+ fk->child_id = iSpaceId;
+ vdbe_emit_fkey_create(pParse, fk);
+ }
}
/* Add the table to the in-memory representation of the database.
@@ -1927,6 +2047,32 @@ sql_clear_stat_spaces(struct Parse *parse, const char *table_name,
vdbe_emit_stat_space_clear(parse, "_sql_stat1", idx_name, table_name);
}
+/**
+ * Generate VDBE program to remove entry from _fk_constraint space.
+ *
+ * @param parse_context Parsing context.
+ * @param constraint_name Name of FK constraint to be dropped.
+ * Must be allocated on head by sqlite3DbMalloc().
+ * It will be freed in VDBE.
+ * @param child_id Id of table which constraint belongs to.
+ */
+static void
+vdbe_emit_fkey_drop(struct Parse *parse_context, const char *constraint_name,
+ uint32_t child_id)
+{
+ struct Vdbe *vdbe = sqlite3GetVdbe(parse_context);
+ assert(vdbe != NULL);
+ int key_reg = sqlite3GetTempRange(parse_context, 3);
+ sqlite3VdbeAddOp4(vdbe, OP_String8, 0, key_reg, 0, constraint_name,
+ P4_DYNAMIC);
+ sqlite3VdbeAddOp2(vdbe, OP_Integer, child_id, key_reg + 1);
+ sqlite3VdbeAddOp3(vdbe, OP_MakeRecord, key_reg, 2, key_reg + 2);
+ sqlite3VdbeAddOp2(vdbe, OP_SDelete, BOX_FK_CONSTRAINT_ID, key_reg + 2);
+ sqlite3VdbeChangeP5(vdbe, OPFLAG_NCHANGE);
+ VdbeComment((vdbe, "Delete FK constraint %s", constraint_name));
+ sqlite3ReleaseTempRange(parse_context, key_reg, 3);
+}
+
/**
* Generate code to drop a table.
* This routine includes dropping triggers, sequences,
@@ -1982,6 +2128,15 @@ sql_code_drop_table(struct Parse *parse_context, struct space *space,
sqlite3VdbeAddOp2(v, OP_SDelete, BOX_SEQUENCE_ID, idx_rec_reg);
VdbeComment((v, "Delete entry from _sequence"));
}
+ /* Delete all child FK constraints. */
+ struct fkey *child_fk;
+ rlist_foreach_entry (child_fk, &space->child_fkey, child_link) {
+ const char *fk_name_dup = sqlite3DbStrDup(v->db,
+ child_fk->def->name);
+ if (fk_name_dup == NULL)
+ return;
+ vdbe_emit_fkey_drop(parse_context, fk_name_dup, space_id);
+ }
/*
* Drop all _space and _index entries that refer to the
* table.
@@ -2090,14 +2245,15 @@ sql_drop_table(struct Parse *parse_context, struct SrcList *table_name_list,
* removing indexes from _index space and eventually
* tuple with corresponding space_id from _space.
*/
- struct Table *tab = sqlite3HashFind(&db->pSchema->tblHash, space_name);
- struct FKey *fk = sqlite3FkReferences(tab);
- if (fk != NULL && (fk->pFrom->def->id != tab->def->id)) {
- diag_set(ClientError, ER_DROP_SPACE, space_name,
- "other objects depend on it");
- parse_context->rc = SQL_TARANTOOL_ERROR;
- parse_context->nErr++;
- goto exit_drop_table;
+ struct fkey *fk;
+ rlist_foreach_entry (fk, &space->parent_fkey, parent_link) {
+ if (! fkey_is_self_referenced(fk->def)) {
+ diag_set(ClientError, ER_DROP_SPACE, space_name,
+ "other objects depend on it");
+ parse_context->rc = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ goto exit_drop_table;
+ }
}
sql_clear_stat_spaces(parse_context, space_name, NULL);
sql_code_drop_table(parse_context, space, is_view);
@@ -2106,177 +2262,280 @@ sql_drop_table(struct Parse *parse_context, struct SrcList *table_name_list,
sqlite3SrcListDelete(db, table_name_list);
}
-/*
- * This routine is called to create a new foreign key on the table
- * currently under construction. pFromCol determines which columns
- * in the current table point to the foreign key. If pFromCol==0 then
- * connect the key to the last column inserted. pTo is the name of
- * the table referred to (a.k.a the "parent" table). pToCol is a list
- * of tables in the parent pTo table. flags contains all
- * information about the conflict resolution algorithms specified
- * in the ON DELETE, ON UPDATE and ON INSERT clauses.
+/**
+ * Return ordinal number of column by name. In case of error,
+ * set error message.
*
- * An FKey structure is created and added to the table currently
- * under construction in the pParse->pNewTable field.
+ * @param parse_context Parsing context.
+ * @param space Space which column belongs to.
+ * @param column_name Name of column to investigate.
+ * @param[out] colno Found name of column.
*
- * The foreign key is set for IMMEDIATE processing. A subsequent call
- * to sqlite3DeferForeignKey() might change this to DEFERRED.
+ * @retval 0 on success, -1 on fault.
*/
+static int
+columnno_by_name(struct Parse *parse_context, const struct space *space,
+ const char *column_name, uint32_t *colno)
+{
+ assert(colno != NULL);
+ uint32_t column_len = strlen(column_name);
+ if (tuple_fieldno_by_name(space->def->dict, column_name, column_len,
+ field_name_hash(column_name, column_len),
+ colno) != 0) {
+ sqlite3ErrorMsg(parse_context,
+ "table \"%s\" doesn't feature column %s",
+ space->def->name, column_name);
+ return -1;
+ }
+ return 0;
+}
+
void
-sqlite3CreateForeignKey(Parse * pParse, /* Parsing context */
- ExprList * pFromCol, /* Columns in this table that point to other table */
- Token * pTo, /* Name of the other table */
- ExprList * pToCol, /* Columns in the other table */
- int flags /* Conflict resolution algorithms. */
- )
+sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
+ struct Token *constraint, struct ExprList *child_cols,
+ struct Token *parent, struct ExprList *parent_cols,
+ bool is_deferred, int actions)
{
- sqlite3 *db = pParse->db;
-#ifndef SQLITE_OMIT_FOREIGN_KEY
- FKey *pFKey = 0;
- FKey *pNextTo;
- Table *p = pParse->pNewTable;
- int nByte;
- int i;
- int nCol;
- char *z;
-
- assert(pTo != 0);
- char *normalized_name = strndup(pTo->z, pTo->n);
- if (normalized_name == NULL) {
- diag_set(OutOfMemory, pTo->n, "strndup", "normalized name");
- goto fk_end;
- }
- sqlite3NormalizeName(normalized_name);
- uint32_t parent_id = box_space_id_by_name(normalized_name,
- strlen(normalized_name));
- if (parent_id == BOX_ID_NIL &&
- strcmp(normalized_name, p->def->name) != 0) {
- diag_set(ClientError, ER_NO_SUCH_SPACE, normalized_name);
- pParse->rc = SQL_TARANTOOL_ERROR;
- pParse->nErr++;
- goto fk_end;
- }
- struct space *parent_space = space_by_id(parent_id);
- if (parent_space != NULL && parent_space->def->opts.is_view) {
- sqlite3ErrorMsg(pParse, "can't create foreign key constraint "\
- "referencing view: %s", normalized_name);
- goto fk_end;
- }
- if (p == 0)
- goto fk_end;
- if (pFromCol == 0) {
- int iCol = p->def->field_count - 1;
- if (NEVER(iCol < 0))
- goto fk_end;
- if (pToCol && pToCol->nExpr != 1) {
- sqlite3ErrorMsg(pParse, "foreign key on %s"
- " should reference only one column of table %T",
- p->def->fields[iCol].name, pTo);
- goto fk_end;
+ struct sqlite3 *db = parse_context->db;
+ /*
+ * When this function is called second time during
+ * <CREATE TABLE ...> statement (i.e. at VDBE runtime),
+ * don't even try to do something.
+ */
+ if (db->init.busy)
+ return;
+ /*
+ * Beforehand initialization for correct clean-up
+ * while emergency exiting in case of error.
+ */
+ const char *parent_name = NULL;
+ const char *constraint_name = NULL;
+ bool is_self_referenced = false;
+ /*
+ * Table under construction during CREATE TABLE
+ * processing. NULL for ALTER TABLE statement handling.
+ */
+ struct Table *new_tab = parse_context->pNewTable;
+ /* Whether we are processing ALTER TABLE or CREATE TABLE. */
+ bool is_alter = new_tab == NULL;
+ uint32_t child_cols_count;
+ if (child_cols == NULL) {
+ assert(!is_alter);
+ child_cols_count = 1;
+ } else {
+ child_cols_count = child_cols->nExpr;
+ }
+ assert(!is_alter || (child != NULL && child->nSrc == 1));
+ struct space *child_space = NULL;
+ uint32_t child_id = 0;
+ if (is_alter) {
+ const char *child_name = child->a[0].zName;
+ child_id = box_space_id_by_name(child_name,
+ strlen(child_name));
+ if (child_id == BOX_ID_NIL) {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, child_name);
+ goto tnt_error;
}
- nCol = 1;
- } else if (pToCol && pToCol->nExpr != pFromCol->nExpr) {
- sqlite3ErrorMsg(pParse,
- "number of columns in foreign key does not match the number of "
- "columns in the referenced table");
- goto fk_end;
+ child_space = space_by_id(child_id);
+ assert(child_space != NULL);
} else {
- nCol = pFromCol->nExpr;
- }
- nByte = sizeof(*pFKey) + (nCol - 1) * sizeof(pFKey->aCol[0]) +
- strlen(normalized_name) + 1;
- if (pToCol) {
- for (i = 0; i < pToCol->nExpr; i++) {
- nByte += sqlite3Strlen30(pToCol->a[i].zName) + 1;
+ struct fkey_parse *fk = region_alloc(&parse_context->region,
+ sizeof(*fk));
+ if (fk == NULL) {
+ diag_set(OutOfMemory, sizeof(*fk), "region_alloc",
+ "fk");
+ goto tnt_error;
+ }
+ memset(fk, 0, sizeof(*fk));
+ rlist_add_entry(&parse_context->new_fkey, fk, link);
+ }
+ assert(parent != NULL);
+ parent_name = sqlite3NameFromToken(db, parent);
+ if (parent_name == NULL)
+ goto exit_create_fk;
+ uint32_t parent_id = box_space_id_by_name(parent_name,
+ strlen(parent_name));
+ /*
+ * Within ALTER TABLE ADD CONSTRAINT FK also can be
+ * self-referenced, but in this case parent (which is
+ * also child) table will definitely exist.
+ */
+ is_self_referenced = !is_alter &&
+ strcmp(parent_name, new_tab->def->name) == 0;
+ struct space *parent_space;
+ if (parent_id == BOX_ID_NIL) {
+ parent_space = NULL;
+ if (is_self_referenced) {
+ struct fkey_parse *fk =
+ rlist_first_entry(&parse_context->new_fkey,
+ struct fkey_parse, link);
+ fk->selfref_cols = parent_cols;
+ fk->is_self_referenced = true;
+ } else {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, parent_name);;
+ goto tnt_error;
}
- }
- pFKey = sqlite3DbMallocZero(db, nByte);
- if (pFKey == 0) {
- goto fk_end;
- }
- pFKey->pFrom = p;
- pFKey->pNextFrom = p->pFKey;
- z = (char *)&pFKey->aCol[nCol];
- pFKey->zTo = z;
- memcpy(z, normalized_name, strlen(normalized_name) + 1);
- z += strlen(normalized_name) + 1;
- pFKey->nCol = nCol;
- if (pFromCol == 0) {
- pFKey->aCol[0].iFrom = p->def->field_count - 1;
} else {
- for (i = 0; i < nCol; i++) {
- int j;
- for (j = 0; j < (int)p->def->field_count; j++) {
- if (strcmp(p->def->fields[j].name,
- pFromCol->a[i].zName) == 0) {
- pFKey->aCol[i].iFrom = j;
- break;
- }
- }
- if (j >= (int)p->def->field_count) {
- sqlite3ErrorMsg(pParse,
- "unknown column \"%s\" in foreign key definition",
- pFromCol->a[i].zName);
- goto fk_end;
- }
+ parent_space = space_by_id(parent_id);
+ assert(parent_space != NULL);
+ if (parent_space->def->opts.is_view) {
+ sqlite3ErrorMsg(parse_context,
+ "referenced table can't be view");
+ goto exit_create_fk;
}
}
- if (pToCol) {
- for (i = 0; i < nCol; i++) {
- int n = sqlite3Strlen30(pToCol->a[i].zName);
- pFKey->aCol[i].zCol = z;
- memcpy(z, pToCol->a[i].zName, n);
- z[n] = 0;
- z += n + 1;
+ if (constraint == NULL && !is_alter) {
+ if (parse_context->constraintName.n == 0) {
+ constraint_name =
+ sqlite3MPrintf(db, "fk_constraint_%d_%s",
+ ++parse_context->fkey_count,
+ new_tab->def->name);
+ } else {
+ struct Token *cnstr_nm = &parse_context->constraintName;
+ constraint_name = sqlite3NameFromToken(db, cnstr_nm);
+ }
+ } else {
+ constraint_name = sqlite3NameFromToken(db, constraint);
+ }
+ if (constraint_name == NULL)
+ goto exit_create_fk;
+ const char *error_msg = "number of columns in foreign key does not "
+ "match the number of columns in the "
+ "referenced table";
+ if (parent_cols != NULL) {
+ if (parent_cols->nExpr != (int) child_cols_count) {
+ diag_set(ClientError, ER_CREATE_FK_CONSTRAINT,
+ constraint_name, error_msg);
+ goto tnt_error;
+ }
+ } else if (!is_self_referenced) {
+ /*
+ * If parent columns are not specified, then PK columns
+ * of parent table are used as referenced.
+ */
+ struct index *parent_pk = space_index(parent_space, 0);
+ assert(parent_pk != NULL);
+ if (parent_pk->def->key_def->part_count != child_cols_count) {
+ diag_set(ClientError, ER_CREATE_FK_CONSTRAINT,
+ constraint_name, error_msg);
+ goto tnt_error;
}
}
- pFKey->isDeferred = 0;
- pFKey->aAction[0] = (u8) (flags & 0xff); /* ON DELETE action */
- pFKey->aAction[1] = (u8) ((flags >> 8) & 0xff); /* ON UPDATE action */
-
- pNextTo = (FKey *) sqlite3HashInsert(&p->pSchema->fkeyHash,
- pFKey->zTo, (void *)pFKey);
- if (pNextTo == pFKey) {
- sqlite3OomFault(db);
- goto fk_end;
+ size_t fk_size = fkey_def_sizeof(child_cols_count,
+ strlen(constraint_name));
+ struct fkey_def *fk = region_alloc(&parse_context->region, fk_size);
+ if (fk == NULL) {
+ diag_set(OutOfMemory, fk_size, "region", "struct fkey");
+ goto tnt_error;
}
- if (pNextTo) {
- assert(pNextTo->pPrevTo == 0);
- pFKey->pNextTo = pNextTo;
- pNextTo->pPrevTo = pFKey;
+ fk->field_count = child_cols_count;
+ fk->child_id = child_id;
+ fk->parent_id = parent_id;
+ fk->is_deferred = is_deferred;
+ fk->match = (enum fkey_match) ((actions >> 16) & 0xff);
+ fk->on_update = (enum fkey_action) ((actions >> 8) & 0xff);
+ fk->on_delete = (enum fkey_action) (actions & 0xff);
+ fk->links = (struct field_link *) ((char *) fk->name +
+ strlen(constraint_name) + 1);
+ /* Fill links map. */
+ for (uint32_t i = 0; i < fk->field_count; ++i) {
+ if (!is_self_referenced && parent_cols == NULL) {
+ struct key_def *pk_def =
+ parent_space->index[0]->def->key_def;
+ fk->links[i].parent_field = pk_def->parts[i].fieldno;
+ } else if (!is_self_referenced &&
+ columnno_by_name(parse_context, parent_space,
+ parent_cols->a[i].zName,
+ &fk->links[i].parent_field) != 0) {
+ goto exit_create_fk;
+ }
+ if (!is_alter) {
+ if (child_cols == NULL) {
+ assert(i == 0);
+ /*
+ * In this case there must be only
+ * one link (the last column
+ * added), so we can break
+ * immediately.
+ */
+ fk->links[0].child_field =
+ new_tab->def->field_count - 1;
+ break;
+ }
+ if (resolve_link(parse_context, new_tab->def,
+ child_cols->a[i].zName,
+ &fk->links[i].child_field) != 0)
+ goto exit_create_fk;
+ /* In case of ALTER parent table must exist. */
+ } else if (columnno_by_name(parse_context, child_space,
+ child_cols->a[i].zName,
+ &fk->links[i].child_field) != 0) {
+ goto exit_create_fk;
+ }
}
-
- /* Link the foreign key to the table as the last step.
+ memcpy(fk->name, constraint_name, strlen(constraint_name));
+ fk->name[strlen(constraint_name)] = '\0';
+ sqlite3NormalizeName(fk->name);
+ /*
+ * In case of CREATE TABLE processing, all foreign keys
+ * constraints must be created after space itself, so
+ * lets delay it until sqlite3EndTable() call and simply
+ * maintain list of all FK constraints inside parser.
*/
- p->pFKey = pFKey;
- pFKey = 0;
+ if (!is_alter) {
+ struct fkey_parse *parse_fk =
+ rlist_first_entry(&parse_context->new_fkey,
+ struct fkey_parse, link);
+ parse_fk->fkey = fk;
+ } else {
+ vdbe_emit_fkey_create(parse_context, fk);
+ }
- fk_end:
- sqlite3DbFree(db, pFKey);
- free(normalized_name);
-#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
- sql_expr_list_delete(db, pFromCol);
- sql_expr_list_delete(db, pToCol);
+exit_create_fk:
+ sql_expr_list_delete(db, child_cols);
+ if (!is_self_referenced)
+ sql_expr_list_delete(db, parent_cols);
+ sqlite3DbFree(db, (void *) parent_name);
+ sqlite3DbFree(db, (void *) constraint_name);
+ return;
+tnt_error:
+ parse_context->rc = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ goto exit_create_fk;
}
-/*
- * This routine is called when an INITIALLY IMMEDIATE or INITIALLY DEFERRED
- * clause is seen as part of a foreign key definition. The isDeferred
- * parameter is 1 for INITIALLY DEFERRED and 0 for INITIALLY IMMEDIATE.
- * The behavior of the most recently created foreign key is adjusted
- * accordingly.
- */
void
-sqlite3DeferForeignKey(Parse * pParse, int isDeferred)
+fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred)
{
-#ifndef SQLITE_OMIT_FOREIGN_KEY
- Table *pTab;
- FKey *pFKey;
- if ((pTab = pParse->pNewTable) == 0 || (pFKey = pTab->pFKey) == 0)
+ if (parse_context->db->init.busy ||
+ rlist_empty(&parse_context->new_fkey))
return;
- assert(isDeferred == 0 || isDeferred == 1); /* EV: R-30323-21917 */
- pFKey->isDeferred = (u8) isDeferred;
-#endif
+ struct fkey_parse *fk_parse =
+ rlist_first_entry(&parse_context->new_fkey, struct fkey_parse,
+ link);
+ fk_parse->fkey->is_deferred = is_deferred;
+}
+
+void
+sql_drop_foreign_key(struct Parse *parse_context, struct SrcList *table,
+ struct Token *constraint)
+{
+ assert(table != NULL && table->nSrc == 1);
+ struct sqlite3 *db = parse_context->db;
+ const char *constraint_name = sqlite3NameFromToken(db, constraint);
+ if (constraint_name == NULL)
+ return;
+ const char *table_name = table->a[0].zName;
+ uint32_t child_id = box_space_id_by_name(table_name,
+ strlen(table_name));
+ if (child_id == BOX_ID_NIL) {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, table_name);
+ parse_context->rc = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ sqlite3DbFree(db, (void *) constraint_name);
+ return;
+ }
+ vdbe_emit_fkey_drop(parse_context, constraint_name, child_id);
}
/*
diff --git a/src/box/sql/callback.c b/src/box/sql/callback.c
index 01e8dd8f1..c630bf21d 100644
--- a/src/box/sql/callback.c
+++ b/src/box/sql/callback.c
@@ -294,7 +294,6 @@ sqlite3SchemaClear(sqlite3 * db)
sqlite3DeleteTable(0, pTab);
}
sqlite3HashClear(&temp1);
- sqlite3HashClear(&pSchema->fkeyHash);
db->pSchema = NULL;
}
@@ -303,13 +302,10 @@ sqlite3SchemaClear(sqlite3 * db)
Schema *
sqlite3SchemaCreate(sqlite3 * db)
{
- Schema *p;
- p = (Schema *) sqlite3DbMallocZero(0, sizeof(Schema));
- if (!p) {
+ struct Schema *p = (Schema *) sqlite3DbMallocZero(0, sizeof(Schema));
+ if (p == NULL)
sqlite3OomFault(db);
- } else {
+ else
sqlite3HashInit(&p->tblHash);
- sqlite3HashInit(&p->fkeyHash);
- }
return p;
}
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 06811778f..cca09f1ad 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -130,7 +130,7 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
assert(space != NULL);
trigger_list = sql_triggers_exist(table, TK_DELETE, NULL, NULL);
is_complex = trigger_list != NULL ||
- sqlite3FkRequired(table, NULL);
+ fkey_is_required(table->def->id, NULL);
}
assert(space != NULL);
@@ -437,14 +437,14 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
* use for the old.* references in the triggers.
*/
if (table != NULL &&
- (sqlite3FkRequired(table, NULL) || trigger_list != NULL)) {
+ (fkey_is_required(table->def->id, NULL) || trigger_list != NULL)) {
/* Mask of OLD.* columns in use */
/* TODO: Could use temporary registers here. */
uint32_t mask =
sql_trigger_colmask(parse, trigger_list, 0, 0,
TRIGGER_BEFORE | TRIGGER_AFTER,
table, onconf);
- mask |= sqlite3FkOldmask(parse, table);
+ mask |= fkey_old_mask(table->def->id);
first_old_reg = parse->nMem + 1;
parse->nMem += (1 + (int)table->def->field_count);
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index f87f610dc..76c456f6e 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -38,9 +38,6 @@
#include "box/fkey.h"
#include "box/schema.h"
#include "box/session.h"
-#include "tarantoolInt.h"
-
-#ifndef SQLITE_OMIT_FOREIGN_KEY
/*
* Deferred and Immediate FKs
@@ -137,8 +134,8 @@
* coding an INSERT operation. The functions used by the UPDATE/DELETE
* generation code to query for this information are:
*
- * sqlite3FkRequired() - Test to see if FK processing is required.
- * sqlite3FkOldmask() - Query for the set of required old.* columns.
+ * fkey_is_required() - Test to see if FK processing is required.
+ * fkey_old_mask() - Query for the set of required old.* columns.
*
*
* Externally accessible module functions
@@ -146,10 +143,7 @@
*
* sqlite3FkCheck() - Check for foreign key violations.
* sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.
- * sqlite3FkDelete() - Delete an FKey structure.
- */
-
-/*
+ *
* VDBE Calling Convention
* -----------------------
*
@@ -166,332 +160,132 @@
* Register (x+3): 3.1 (type real)
*/
-/*
- * A foreign key constraint requires that the key columns in the parent
- * table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
- * Given that pParent is the parent table for foreign key constraint pFKey,
- * search the schema for a unique index on the parent key columns.
- *
- * If successful, zero is returned. If the parent key is an INTEGER PRIMARY
- * KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx
- * is set to point to the unique index.
+/**
+ * This function is called when a row is inserted into or deleted
+ * from the child table of foreign key constraint. If an SQL
+ * UPDATE is executed on the child table of fkey, this function is
+ * invoked twice for each row affected - once to "delete" the old
+ * row, and then again to "insert" the new row.
*
- * If the parent key consists of a single column (the foreign key constraint
- * is not a composite foreign key), output variable *paiCol is set to NULL.
- * Otherwise, it is set to point to an allocated array of size N, where
- * N is the number of columns in the parent key. The first element of the
- * array is the index of the child table column that is mapped by the FK
- * constraint to the parent table column stored in the left-most column
- * of index *ppIdx. The second element of the array is the index of the
- * child table column that corresponds to the second left-most column of
- * *ppIdx, and so on.
+ * Each time it is called, this function generates VDBE code to
+ * locate the row in the parent table that corresponds to the row
+ * being inserted into or deleted from the child table. If the
+ * parent row can be found, no special action is taken. Otherwise,
+ * if the parent row can *not* be found in the parent table:
*
- * If the required index cannot be found, either because:
+ * Op | FK type | Action taken
+ * ---------------------------------------------------------------
+ * INSERT immediate Increment the "immediate constraint counter".
*
- * 1) The named parent key columns do not exist, or
+ * DELETE immediate Decrement the "immediate constraint counter".
*
- * 2) The named parent key columns do exist, but are not subject to a
- * UNIQUE or PRIMARY KEY constraint, or
+ * INSERT deferred Increment the "deferred constraint counter".
*
- * 3) No parent key columns were provided explicitly as part of the
- * foreign key definition, and the parent table does not have a
- * PRIMARY KEY, or
+ * DELETE deferred Decrement the "deferred constraint counter".
*
- * 4) No parent key columns were provided explicitly as part of the
- * foreign key definition, and the PRIMARY KEY of the parent table
- * consists of a different number of columns to the child key in
- * the child table.
+ * These operations are identified in the comment at the top of
+ * this file as "I.1" and "D.1".
*
- * then non-zero is returned, and a "foreign key mismatch" error loaded
- * into pParse. If an OOM error occurs, non-zero is returned and the
- * pParse->db->mallocFailed flag is set.
+ * @param parse_context Current parsing context.
+ * @param parent Parent table of FK constraint.
+ * @param fk_def FK constraint definition.
+ * @param referenced_idx Id of referenced index.
+ * @param reg_data Address of array containing child table row.
+ * @param incr_count Increment constraint counter by this value.
*/
-int
-sqlite3FkLocateIndex(Parse * pParse, /* Parse context to store any error in */
- Table * pParent, /* Parent table of FK constraint pFKey */
- FKey * pFKey, /* Foreign key to find index for */
- Index ** ppIdx, /* OUT: Unique index on parent table */
- int **paiCol /* OUT: Map of index columns in pFKey */
- )
+static void
+fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
+ struct fkey_def *fk_def, uint32_t referenced_idx,
+ int reg_data, int incr_count)
{
- int *aiCol = 0; /* Value to return via *paiCol */
- int nCol = pFKey->nCol; /* Number of columns in parent key */
- char *zKey = pFKey->aCol[0].zCol; /* Name of left-most parent key column */
-
- /* The caller is responsible for zeroing output parameters. */
- assert(ppIdx && *ppIdx == 0);
- assert(!paiCol || *paiCol == 0);
- assert(pParse);
-
- /* If this is a non-composite (single column) foreign key, check if it
- * maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx
- * and *paiCol set to zero and return early.
+ assert(incr_count == -1 || incr_count == 1);
+ struct Vdbe *v = sqlite3GetVdbe(parse_context);
+ int cursor = parse_context->nTab - 1;
+ int ok_label = sqlite3VdbeMakeLabel(v);
+ /*
+ * If incr_count is less than zero, then check at runtime
+ * if there are any outstanding constraints to resolve.
+ * If there are not, there is no need to check if deleting
+ * this row resolves any outstanding violations.
*
- * Otherwise, for a composite foreign key (more than one column), allocate
- * space for the aiCol array (returned via output parameter *paiCol).
- * Non-composite foreign keys do not require the aiCol array.
+ * Check if any of the key columns in the child table row
+ * are NULL. If any are, then the constraint is considered
+ * satisfied. No need to search for a matching row in the
+ * parent table.
*/
- if (paiCol && nCol > 1) {
- aiCol =
- (int *)sqlite3DbMallocRawNN(pParse->db, nCol * sizeof(int));
- if (!aiCol)
- return 1;
- *paiCol = aiCol;
+ if (incr_count < 0) {
+ sqlite3VdbeAddOp2(v, OP_FkIfZero, fk_def->is_deferred,
+ ok_label);
}
-
- struct Index *index = NULL;
- for (index = pParent->pIndex; index != NULL; index = index->pNext) {
- int part_count = index->def->key_def->part_count;
- if (part_count != nCol || !index->def->opts.is_unique ||
- index->pPartIdxWhere != NULL)
- continue;
- /*
- * Index is a UNIQUE index (or a PRIMARY KEY) and
- * has the right number of columns. If each
- * indexed column corresponds to a foreign key
- * column of pFKey, then this index is a winner.
- */
- if (zKey == NULL) {
- /*
- * If zKey is NULL, then this foreign key
- * is implicitly mapped to the PRIMARY KEY
- * of table pParent. The PRIMARY KEY index
- * may be identified by the test.
- */
- if (IsPrimaryKeyIndex(index)) {
- if (aiCol != NULL) {
- for (int i = 0; i < nCol; i++)
- aiCol[i] = pFKey->aCol[i].iFrom;
- }
- break;
- }
- } else {
- /*
- * If zKey is non-NULL, then this foreign
- * key was declared to map to an explicit
- * list of columns in table pParent. Check
- * if this index matches those columns.
- * Also, check that the index uses the
- * default collation sequences for each
- * column.
- */
- int i, j;
- struct key_part *part = index->def->key_def->parts;
- for (i = 0; i < nCol; i++, part++) {
- /*
- * Index of column in parent
- * table.
- */
- i16 iCol = (int) part->fieldno;
- /*
- * If the index uses a collation
- * sequence that is different from
- * the default collation sequence
- * for the column, this index is
- * unusable. Bail out early in
- * this case.
- */
- uint32_t id;
- struct coll *def_coll =
- sql_column_collation(pParent->def,
- iCol, &id);
- struct coll *coll = part->coll;
- if (def_coll != coll)
- break;
-
- char *zIdxCol = pParent->def->fields[iCol].name;
- for (j = 0; j < nCol; j++) {
- if (strcmp(pFKey->aCol[j].zCol,
- zIdxCol) != 0)
- continue;
- if (aiCol)
- aiCol[i] = pFKey->aCol[j].iFrom;
- break;
- }
- if (j == nCol)
- break;
- }
- if (i == nCol) {
- /* Index is usable. */
- break;
- }
- }
+ struct field_link *link = fk_def->links;
+ for (uint32_t i = 0; i < fk_def->field_count; ++i, ++link) {
+ int reg = link->child_field + reg_data + 1;
+ sqlite3VdbeAddOp2(v, OP_IsNull, reg, ok_label);
}
-
- if (index == NULL) {
- sqlite3ErrorMsg(pParse, "foreign key mismatch - "
- "\"%w\" referencing \"%w\"",
- pFKey->pFrom->def->name, pFKey->zTo);
- }
-
- *ppIdx = index;
- return 0;
-}
-
-/*
- * This function is called when a row is inserted into or deleted from the
- * child table of foreign key constraint pFKey. If an SQL UPDATE is executed
- * on the child table of pFKey, this function is invoked twice for each row
- * affected - once to "delete" the old row, and then again to "insert" the
- * new row.
- *
- * Each time it is called, this function generates VDBE code to locate the
- * row in the parent table that corresponds to the row being inserted into
- * or deleted from the child table. If the parent row can be found, no
- * special action is taken. Otherwise, if the parent row can *not* be
- * found in the parent table:
- *
- * Operation | FK type | Action taken
- * --------------------------------------------------------------------------
- * INSERT immediate Increment the "immediate constraint counter".
- *
- * DELETE immediate Decrement the "immediate constraint counter".
- *
- * INSERT deferred Increment the "deferred constraint counter".
- *
- * DELETE deferred Decrement the "deferred constraint counter".
- *
- * These operations are identified in the comment at the top of this file
- * (fkey.c) as "I.1" and "D.1".
- */
-static void
-fkLookupParent(Parse * pParse, /* Parse context */
- Table * pTab, /* Parent table of FK pFKey */
- Index * pIdx, /* Unique index on parent key columns in pTab */
- FKey * pFKey, /* Foreign key constraint */
- int *aiCol, /* Map from parent key columns to child table columns */
- int regData, /* Address of array containing child table row */
- int nIncr, /* Increment constraint counter by this */
- int isIgnore /* If true, pretend pTab contains all NULL values */
- )
-{
- int i; /* Iterator variable */
- Vdbe *v = sqlite3GetVdbe(pParse); /* Vdbe to add code to */
- int iCur = pParse->nTab - 1; /* Cursor number to use */
- int iOk = sqlite3VdbeMakeLabel(v); /* jump here if parent key found */
- struct session *user_session = current_session();
-
- /* If nIncr is less than zero, then check at runtime if there are any
- * outstanding constraints to resolve. If there are not, there is no need
- * to check if deleting this row resolves any outstanding violations.
+ uint32_t field_count = fk_def->field_count;
+ int temp_regs = sqlite3GetTempRange(parse_context, field_count);
+ int rec_reg = sqlite3GetTempReg(parse_context);
+ vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, parent);
+ link = fk_def->links;
+ for (uint32_t i = 0; i < field_count; ++i, ++link) {
+ sqlite3VdbeAddOp2(v, OP_Copy, link->child_field + 1 + reg_data,
+ temp_regs + i);
+ }
+ /*
+ * If the parent table is the same as the child table, and
+ * we are about to increment the constraint-counter (i.e.
+ * this is an INSERT operation), then check if the row
+ * being inserted matches itself. If so, do not increment
+ * the constraint-counter.
*
- * Check if any of the key columns in the child table row are NULL. If
- * any are, then the constraint is considered satisfied. No need to
- * search for a matching row in the parent table.
+ * If any of the parent-key values are NULL, then the row
+ * cannot match itself. So set JUMPIFNULL to make sure we
+ * do the OP_Found if any of the parent-key values are
+ * NULL (at this point it is known that none of the child
+ * key values are).
*/
- if (nIncr < 0) {
- sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);
- VdbeCoverage(v);
- }
- for (i = 0; i < pFKey->nCol; i++) {
- int iReg = aiCol[i] + regData + 1;
- sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
- VdbeCoverage(v);
- }
-
- if (isIgnore == 0) {
- if (pIdx == 0) {
- /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
- * column of the parent table (table pTab).
- */
- int regTemp = sqlite3GetTempReg(pParse);
-
- /* Invoke MustBeInt to coerce the child key value to an integer (i.e.
- * apply the affinity of the parent key). If this fails, then there
- * is no matching parent key. Before using MustBeInt, make a copy of
- * the value. Otherwise, the value inserted into the child key column
- * will have INTEGER affinity applied to it, which may not be correct.
- */
- sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0] + 1 + regData,
- regTemp);
- VdbeCoverage(v);
-
- /* If the parent table is the same as the child table, and we are about
- * to increment the constraint-counter (i.e. this is an INSERT operation),
- * then check if the row being inserted matches itself. If so, do not
- * increment the constraint-counter.
- */
- if (pTab == pFKey->pFrom && nIncr == 1) {
- sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk,
- regTemp);
- VdbeCoverage(v);
- sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
- }
-
- } else {
- int nCol = pFKey->nCol;
- int regTemp = sqlite3GetTempRange(pParse, nCol);
- int regRec = sqlite3GetTempReg(pParse);
- struct space *space =
- space_by_id(pIdx->pTable->def->id);
- vdbe_emit_open_cursor(pParse, iCur, pIdx->def->iid,
- space);
- for (i = 0; i < nCol; i++) {
- sqlite3VdbeAddOp2(v, OP_Copy,
- aiCol[i] + 1 + regData,
- regTemp + i);
- }
-
- /* If the parent table is the same as the child table, and we are about
- * to increment the constraint-counter (i.e. this is an INSERT operation),
- * then check if the row being inserted matches itself. If so, do not
- * increment the constraint-counter.
- *
- * If any of the parent-key values are NULL, then the row cannot match
- * itself. So set JUMPIFNULL to make sure we do the OP_Found if any
- * of the parent-key values are NULL (at this point it is known that
- * none of the child key values are).
- */
- if (pTab == pFKey->pFrom && nIncr == 1) {
- int iJump =
- sqlite3VdbeCurrentAddr(v) + nCol + 1;
- struct key_part *part =
- pIdx->def->key_def->parts;
- for (i = 0; i < nCol; ++i, ++part) {
- int iChild = aiCol[i] + 1 + regData;
- int iParent = 1 + regData +
- (int)part->fieldno;
- sqlite3VdbeAddOp3(v, OP_Ne, iChild,
- iJump, iParent);
- VdbeCoverage(v);
- sqlite3VdbeChangeP5(v,
- SQLITE_JUMPIFNULL);
- }
- sqlite3VdbeGoto(v, iOk);
- }
-
- sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, nCol,
- regRec,
- sqlite3IndexAffinityStr(pParse->db,
- pIdx), nCol);
- sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0);
- VdbeCoverage(v);
-
- sqlite3ReleaseTempReg(pParse, regRec);
- sqlite3ReleaseTempRange(pParse, regTemp, nCol);
+ if (fkey_is_self_referenced(fk_def) && incr_count == 1) {
+ int jump = sqlite3VdbeCurrentAddr(v) + field_count + 1;
+ link = fk_def->links;
+ for (uint32_t i = 0; i < field_count; ++i, ++link) {
+ int chcol = link->child_field + 1 + reg_data;
+ int pcol = link->parent_field + 1 + reg_data;
+ sqlite3VdbeAddOp3(v, OP_Ne, chcol, jump, pcol);
+ sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
}
+ sqlite3VdbeGoto(v, ok_label);
}
-
- if (!pFKey->isDeferred && !(user_session->sql_flags & SQLITE_DeferFKs)
- && !pParse->pToplevel && !pParse->isMultiWrite) {
- /* Special case: If this is an INSERT statement that will insert exactly
- * one row into the table, raise a constraint immediately instead of
- * incrementing a counter. This is necessary as the VM code is being
- * generated for will not open a statement transaction.
+ struct index *idx = space_index(parent, referenced_idx);
+ assert(idx != NULL);
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count, rec_reg,
+ sql_index_affinity_str(parse_context->db, idx->def),
+ P4_DYNAMIC);
+ sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
+ sqlite3ReleaseTempReg(parse_context, rec_reg);
+ sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
+ if (!fk_def->is_deferred && parse_context->pToplevel == NULL &&
+ !parse_context->isMultiWrite) {
+ /*
+ * If this is an INSERT statement that will insert
+ * exactly one row into the table, raise a
+ * constraint immediately instead of incrementing
+ * a counter. This is necessary as the VM code is
+ * being generated for will not open a statement
+ * transaction.
*/
- assert(nIncr == 1);
- sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
+ assert(incr_count == 1);
+ sqlite3HaltConstraint(parse_context,
+ SQLITE_CONSTRAINT_FOREIGNKEY,
ON_CONFLICT_ACTION_ABORT, 0, P4_STATIC,
P5_ConstraintFK);
} else {
- if (nIncr > 0 && pFKey->isDeferred == 0) {
- sqlite3MayAbort(pParse);
- }
- sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
+ if (incr_count > 0 && !fk_def->is_deferred)
+ sqlite3MayAbort(parse_context);
+ sqlite3VdbeAddOp2(v, OP_FkCounter, fk_def->is_deferred,
+ incr_count);
}
-
- sqlite3VdbeResolveLabel(v, iOk);
- sqlite3VdbeAddOp1(v, OP_Close, iCur);
+ sqlite3VdbeResolveLabel(v, ok_label);
+ sqlite3VdbeAddOp1(v, OP_Close, cursor);
}
/*
@@ -551,519 +345,446 @@ exprTableColumn(sqlite3 * db, struct space_def *def, int cursor, i16 column)
}
/*
- * This function is called to generate code executed when a row is deleted
- * from the parent table of foreign key constraint pFKey and, if pFKey is
- * deferred, when a row is inserted into the same table. When generating
- * code for an SQL UPDATE operation, this function may be called twice -
- * once to "delete" the old row and once to "insert" the new row.
- *
- * Parameter nIncr is passed -1 when inserting a row (as this may decrease
- * the number of FK violations in the db) or +1 when deleting one (as this
- * may increase the number of FK constraint problems).
- *
- * The code generated by this function scans through the rows in the child
- * table that correspond to the parent table row being deleted or inserted.
- * For each child row found, one of the following actions is taken:
- *
- * Operation | FK type | Action taken
- * --------------------------------------------------------------------------
- * DELETE immediate Increment the "immediate constraint counter".
- * Or, if the ON (UPDATE|DELETE) action is RESTRICT,
- * throw a "FOREIGN KEY constraint failed" exception.
- *
- * INSERT immediate Decrement the "immediate constraint counter".
- *
- * DELETE deferred Increment the "deferred constraint counter".
- * Or, if the ON (UPDATE|DELETE) action is RESTRICT,
- * throw a "FOREIGN KEY constraint failed" exception.
- *
- * INSERT deferred Decrement the "deferred constraint counter".
- *
- * These operations are identified in the comment at the top of this file
- * (fkey.c) as "I.2" and "D.2".
+ * This function is called to generate code executed when a row is
+ * deleted from the parent table of foreign key constraint @a fkey
+ * and, if @a fkey is deferred, when a row is inserted into the
+ * same table. When generating code for an SQL UPDATE operation,
+ * this function may be called twice - once to "delete" the old
+ * row and once to "insert" the new row.
+ *
+ * Parameter incr_count is passed -1 when inserting a row (as this
+ * may decrease the number of FK violations in the db) or +1 when
+ * deleting one (as this may increase the number of FK constraint
+ * problems).
+ *
+ * The code generated by this function scans through the rows in
+ * the child table that correspond to the parent table row being
+ * deleted or inserted. For each child row found, one of the
+ * following actions is taken:
+ *
+ * Op | FK type | Action taken
+ * ---------------------------------------------------------------
+ * DELETE immediate Increment the "immediate constraint counter".
+ * Or, if the ON (UPDATE|DELETE) action is
+ * RESTRICT, throw a "FOREIGN KEY constraint
+ * failed" exception.
+ *
+ * INSERT immediate Decrement the "immediate constraint counter".
+ *
+ * DELETE deferred Increment the "deferred constraint counter".
+ * Or, if the ON (UPDATE|DELETE) action is
+ * RESTRICT, throw a "FOREIGN KEY constraint
+ * failed" exception.
+ *
+ * INSERT deferred Decrement the "deferred constraint counter".
+ *
+ * These operations are identified in the comment at the top of
+ * this file as "I.2" and "D.2".
+ * @param parser SQL parser.
+ * @param src The child table to be scanned.
+ * @param tab Parent table.
+ * @param fkey The foreign key linking src to tab.
+ * @param reg_data Register from which parent row data starts.
+ * @param incr_count Amount to increment deferred counter by.
*/
static void
-fkScanChildren(Parse * pParse, /* Parse context */
- SrcList * pSrc, /* The child table to be scanned */
- Table * pTab, /* The parent table */
- Index * pIdx, /* Index on parent covering the foreign key */
- FKey * pFKey, /* The foreign key linking pSrc to pTab */
- int *aiCol, /* Map from pIdx cols to child table cols */
- int regData, /* Parent row data starts here */
- int nIncr /* Amount to increment deferred counter by */
- )
+fkScanChildren(struct Parse *parser, struct SrcList *src, struct Table *tab,
+ struct fkey_def *fkey, int reg_data, int incr_count)
{
- sqlite3 *db = pParse->db; /* Database handle */
- Expr *pWhere = 0; /* WHERE clause to scan with */
- NameContext sNameContext; /* Context used to resolve WHERE clause */
- WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */
- int iFkIfZero = 0; /* Address of OP_FkIfZero */
- Vdbe *v = sqlite3GetVdbe(pParse);
-
- assert(pIdx == NULL || pIdx->pTable == pTab);
- assert(pIdx == NULL || (int) pIdx->def->key_def->part_count == pFKey->nCol);
- assert(pIdx != NULL);
-
- if (nIncr < 0) {
- iFkIfZero =
- sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
+ assert(incr_count == -1 || incr_count == 1);
+ struct sqlite3 *db = parser->db;
+ struct Expr *where = NULL;
+ /* Address of OP_FkIfZero. */
+ int fkifzero_label = 0;
+ struct Vdbe *v = sqlite3GetVdbe(parser);
+
+ if (incr_count < 0) {
+ fkifzero_label = sqlite3VdbeAddOp2(v, OP_FkIfZero,
+ fkey->is_deferred, 0);
VdbeCoverage(v);
}
- /* Create an Expr object representing an SQL expression like:
+ struct space *child_space = space_by_id(fkey->child_id);
+ assert(child_space != NULL);
+ /*
+ * Create an Expr object representing an SQL expression
+ * like:
*
- * <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
+ * <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
*
- * The collation sequence used for the comparison should be that of
- * the parent key columns. The affinity of the parent key column should
- * be applied to each child key value before the comparison takes place.
+ * The collation sequence used for the comparison should
+ * be that of the parent key columns. The affinity of the
+ * parent key column should be applied to each child key
+ * value before the comparison takes place.
*/
- for (int i = 0; i < pFKey->nCol; i++) {
- Expr *pLeft; /* Value from parent table row */
- Expr *pRight; /* Column ref to child table */
- Expr *pEq; /* Expression (pLeft = pRight) */
- i16 iCol; /* Index of column in child table */
- const char *column_name;
-
- iCol = pIdx != NULL ?
- (int) pIdx->def->key_def->parts[i].fieldno : -1;
- pLeft = exprTableRegister(pParse, pTab, regData, iCol);
- iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
- assert(iCol >= 0);
- column_name = pFKey->pFrom->def->fields[iCol].name;
- pRight = sqlite3Expr(db, TK_ID, column_name);
- pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
- pWhere = sqlite3ExprAnd(db, pWhere, pEq);
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ uint32_t fieldno = fkey->links[i].parent_field;
+ struct Expr *pexpr =
+ exprTableRegister(parser, tab, reg_data, fieldno);
+ fieldno = fkey->links[i].child_field;
+ const char *field_name = child_space->def->fields[fieldno].name;
+ struct Expr *chexpr = sqlite3Expr(db, TK_ID, field_name);
+ struct Expr *eq = sqlite3PExpr(parser, TK_EQ, pexpr, chexpr);
+ where = sqlite3ExprAnd(db, where, eq);
}
- /* If the child table is the same as the parent table, then add terms
- * to the WHERE clause that prevent this entry from being scanned.
- * The added WHERE clause terms are like this:
+ /*
+ * If the child table is the same as the parent table,
+ * then add terms to the WHERE clause that prevent this
+ * entry from being scanned. The added WHERE clause terms
+ * are like this:
*
* NOT( $current_a==a AND $current_b==b AND ... )
* The primary key is (a,b,...)
*/
- if (pTab == pFKey->pFrom && nIncr > 0) {
- Expr *pNe; /* Expression (pLeft != pRight) */
- Expr *pLeft; /* Value from parent table row */
- Expr *pRight; /* Column ref to child table */
-
- Expr *pEq, *pAll = 0;
- Index *pPk = sqlite3PrimaryKeyIndex(pTab);
- assert(pIdx != NULL);
- uint32_t part_count = pPk->def->key_def->part_count;
- for (uint32_t i = 0; i < part_count; i++) {
- uint32_t fieldno = pIdx->def->key_def->parts[i].fieldno;
- pLeft = exprTableRegister(pParse, pTab, regData,
+ if (tab->def->id == fkey->child_id && incr_count > 0) {
+ struct Expr *expr = NULL, *pexpr, *chexpr, *eq;
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ uint32_t fieldno = fkey->links[i].parent_field;
+ pexpr = exprTableRegister(parser, tab, reg_data,
fieldno);
- pRight = exprTableColumn(db, pTab->def,
- pSrc->a[0].iCursor, fieldno);
- pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
- pAll = sqlite3ExprAnd(db, pAll, pEq);
+ chexpr = exprTableColumn(db, tab->def,
+ src->a[0].iCursor, fieldno);
+ eq = sqlite3PExpr(parser, TK_EQ, pexpr, chexpr);
+ expr = sqlite3ExprAnd(db, expr, eq);
}
- pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0);
- pWhere = sqlite3ExprAnd(db, pWhere, pNe);
+ struct Expr *pNe = sqlite3PExpr(parser, TK_NOT, expr, 0);
+ where = sqlite3ExprAnd(db, where, pNe);
}
/* Resolve the references in the WHERE clause. */
- memset(&sNameContext, 0, sizeof(NameContext));
- sNameContext.pSrcList = pSrc;
- sNameContext.pParse = pParse;
- sqlite3ResolveExprNames(&sNameContext, pWhere);
-
- /* Create VDBE to loop through the entries in pSrc that match the WHERE
- * clause. For each row found, increment either the deferred or immediate
- * foreign key constraint counter.
+ struct NameContext namectx;
+ memset(&namectx, 0, sizeof(namectx));
+ namectx.pSrcList = src;
+ namectx.pParse = parser;
+ sqlite3ResolveExprNames(&namectx, where);
+
+ /*
+ * Create VDBE to loop through the entries in src that
+ * match the WHERE clause. For each row found, increment
+ * either the deferred or immediate foreign key constraint
+ * counter.
*/
- pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
- sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
- if (pWInfo) {
- sqlite3WhereEnd(pWInfo);
- }
+ struct WhereInfo *info =
+ sqlite3WhereBegin(parser, src, where, NULL, NULL, 0, 0);
+ sqlite3VdbeAddOp2(v, OP_FkCounter, fkey->is_deferred, incr_count);
+ if (info != NULL)
+ sqlite3WhereEnd(info);
/* Clean up the WHERE clause constructed above. */
- sql_expr_delete(db, pWhere, false);
- if (iFkIfZero)
- sqlite3VdbeJumpHere(v, iFkIfZero);
-}
-
-/*
- * This function returns a linked list of FKey objects (connected by
- * FKey.pNextTo) holding all children of table pTab. For example,
- * given the following schema:
- *
- * CREATE TABLE t1(a PRIMARY KEY);
- * CREATE TABLE t2(b REFERENCES t1(a);
- *
- * Calling this function with table "t1" as an argument returns a pointer
- * to the FKey structure representing the foreign key constraint on table
- * "t2". Calling this function with "t2" as the argument would return a
- * NULL pointer (as there are no FK constraints for which t2 is the parent
- * table).
- */
-FKey *
-sqlite3FkReferences(Table * pTab)
-{
- return (FKey *) sqlite3HashFind(&pTab->pSchema->fkeyHash,
- pTab->def->name);
+ sql_expr_delete(db, where, false);
+ if (fkifzero_label != 0)
+ sqlite3VdbeJumpHere(v, fkifzero_label);
}
-/*
- * The second argument points to an FKey object representing a foreign key
- * for which pTab is the child table. An UPDATE statement against pTab
- * is currently being processed. For each column of the table that is
- * actually updated, the corresponding element in the aChange[] array
- * is zero or greater (if a column is unmodified the corresponding element
- * is set to -1).
- *
- * This function returns true if any of the columns that are part of the
- * child key for FK constraint *p are modified.
+/**
+ * An UPDATE statement against the table having foreign key with
+ * definition @a fkey is currently being processed. For each
+ * updated column of the table the corresponding element in @a
+ * changes array is zero or greater (if a column is unmodified the
+ * corresponding element is set to -1).
+ *
+ * @param fkey FK constraint definition.
+ * @param changes Array indicating modified columns.
+ * @retval true, if any of the columns that are part of the child
+ * key for FK constraint are modified.
*/
-static int
-fkChildIsModified(FKey * p, /* Foreign key for which pTab is the child */
- int *aChange /* Array indicating modified columns */
- )
+static bool
+fkey_child_is_modified(const struct fkey_def *fkey, int *changes)
{
- int i;
- for (i = 0; i < p->nCol; i++) {
- int iChildKey = p->aCol[i].iFrom;
- if (aChange[iChildKey] >= 0)
- return 1;
+ for (uint32_t i = 0; i < fkey->field_count; ++i) {
+ uint32_t child_key = fkey->links[i].child_field;
+ if (changes[child_key] >= 0)
+ return true;
}
- return 0;
+ return false;
}
-/*
- * The second argument points to an FKey object representing a foreign key
- * for which pTab is the parent table. An UPDATE statement against pTab
- * is currently being processed. For each column of the table that is
- * actually updated, the corresponding element in the aChange[] array
- * is zero or greater (if a column is unmodified the corresponding element
- * is set to -1).
- *
- * This function returns true if any of the columns that are part of the
- * parent key for FK constraint *p are modified.
+/**
+ * Works the same as fkey_child_is_modified(), but checks are
+ * provided on parent table.
*/
-static int
-fkParentIsModified(Table * pTab, FKey * p, int *aChange)
+static bool
+fkey_parent_is_modified(const struct fkey_def *fkey, int *changes)
{
- int i;
- for (i = 0; i < p->nCol; i++) {
- char *zKey = p->aCol[i].zCol;
- int iKey;
- for (iKey = 0; iKey < (int)pTab->def->field_count; iKey++) {
- if (aChange[iKey] >= 0) {
- if (zKey) {
- if (strcmp(pTab->def->fields[iKey].name,
- zKey) == 0)
- return 1;
- } else if (table_column_is_in_pk(pTab, iKey)) {
- return 1;
- }
- }
- }
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ uint32_t parent_key = fkey->links[i].parent_field;
+ if (changes[parent_key] >= 0)
+ return true;
}
- return 0;
+ return false;
}
-/*
- * Return true if the parser passed as the first argument is being
- * used to code a trigger that is really a "SET NULL" action belonging
- * to trigger pFKey.
+/**
+ * Return true if the parser passed as the first argument is
+ * used to code a trigger that is really a "SET NULL" action.
*/
-static int
-isSetNullAction(Parse * pParse, FKey * pFKey)
+static bool
+fkey_action_is_set_null(struct Parse *parse_context, const struct fkey *fkey)
{
- Parse *pTop = sqlite3ParseToplevel(pParse);
- if (pTop->pTriggerPrg != NULL) {
- struct sql_trigger *trigger = pTop->pTriggerPrg->trigger;
- if ((trigger == pFKey->apTrigger[0] &&
- pFKey->aAction[0] == OE_SetNull) ||
- (trigger == pFKey->apTrigger[1]
- && pFKey->aAction[1] == OE_SetNull))
- return 1;
+ struct Parse *top_parse = sqlite3ParseToplevel(parse_context);
+ if (top_parse->pTriggerPrg != NULL) {
+ struct sql_trigger *trigger = top_parse->pTriggerPrg->trigger;
+ if ((trigger == fkey->on_delete_trigger &&
+ fkey->def->on_delete == FKEY_ACTION_SET_NULL) ||
+ (trigger == fkey->on_update_trigger &&
+ fkey->def->on_update == FKEY_ACTION_SET_NULL))
+ return true;
}
- return 0;
+ return false;
}
/*
- * This function is called when inserting, deleting or updating a row of
- * table pTab to generate VDBE code to perform foreign key constraint
- * processing for the operation.
- *
- * For a DELETE operation, parameter regOld is passed the index of the
- * first register in an array of (pTab->nCol+1) registers containing the
- * PK of the row being deleted, followed by each of the column values
- * of the row being deleted, from left to right. Parameter regNew is passed
- * zero in this case.
- *
- * For an INSERT operation, regOld is passed zero and regNew is passed the
- * first register of an array of (pTab->nCol+1) registers containing the new
- * row data.
- *
- * For an UPDATE operation, this function is called twice. Once before
- * the original record is deleted from the table using the calling convention
- * described for DELETE. Then again after the original record is deleted
- * but before the new record is inserted using the INSERT convention.
+ * This function is called when inserting, deleting or updating a
+ * row of table tab to generate VDBE code to perform foreign key
+ * constraint processing for the operation.
+ *
+ * For a DELETE operation, parameter reg_old is passed the index
+ * of the first register in an array of (tab->def->field_count +
+ * 1) registers containing the PK of the row being deleted,
+ * followed by each of the column values of the row being deleted,
+ * from left to right. Parameter reg_new is passed zero in this
+ * case.
+ *
+ * For an INSERT operation, reg_old is passed zero and reg_new is
+ * passed the first register of an array of
+ * (tab->def->field_count + 1) registers containing the new row
+ * data.
+ *
+ * For an UPDATE operation, this function is called twice. Once
+ * before the original record is deleted from the table using the
+ * calling convention described for DELETE. Then again after the
+ * original record is deleted but before the new record is
+ * inserted using the INSERT convention.
+ *
+ * @param parser SQL parser.
+ * @param tab Table from which the row is deleted.
+ * @param reg_old Register with deleted row.
+ * @param reg_new Register with inserted row.
+ * @param changed_cols Array of updated columns. Can be NULL.
*/
void
-sqlite3FkCheck(Parse * pParse, /* Parse context */
- Table * pTab, /* Row is being deleted from this table */
- int regOld, /* Previous row data is stored here */
- int regNew, /* New row data is stored here */
- int *aChange /* Array indicating UPDATEd columns (or 0) */
- )
+sqlite3FkCheck(struct Parse *parser, struct Table *tab, int reg_old,
+ int reg_new, int *changed_cols)
{
- sqlite3 *db = pParse->db; /* Database handle */
- FKey *pFKey; /* Used to iterate through FKs */
+ struct sqlite3 *db = parser->db;
struct session *user_session = current_session();
- /* Exactly one of regOld and regNew should be non-zero. */
- assert((regOld == 0) != (regNew == 0));
+ /*
+ * Exactly one of reg_old and reg_new should be non-zero.
+ */
+ assert((reg_old == 0) != (reg_new == 0));
- /* If foreign-keys are disabled, this function is a no-op. */
+ /*
+ * If foreign-keys are disabled, this function is a no-op.
+ */
if ((user_session->sql_flags & SQLITE_ForeignKeys) == 0)
return;
- /* Loop through all the foreign key constraints for which pTab is the
- * child table (the table that the foreign key definition is part of).
+ /*
+ * Loop through all the foreign key constraints for which
+ * tab is the child table.
*/
- for (pFKey = pTab->pFKey; pFKey; pFKey = pFKey->pNextFrom) {
- Table *pTo; /* Parent table of foreign key pFKey */
- Index *pIdx = 0; /* Index on key columns in pTo */
- int *aiFree = 0;
- int *aiCol;
- int iCol;
- int bIgnore = 0;
-
- if (aChange
- && sqlite3_stricmp(pTab->def->name, pFKey->zTo) != 0
- && fkChildIsModified(pFKey, aChange) == 0) {
+ struct space *space = space_by_id(tab->def->id);
+ assert(space != NULL);
+ struct fkey *fk;
+ rlist_foreach_entry(fk, &space->child_fkey, child_link) {
+ struct fkey_def *fk_def = fk->def;
+ if (changed_cols != NULL &&
+ !fkey_is_self_referenced(fk_def) &&
+ !fkey_child_is_modified(fk_def, changed_cols))
continue;
- }
-
- /* Find the parent table of this foreign key. Also find a unique index
- * on the parent key columns in the parent table. If either of these
- * schema items cannot be located, set an error in pParse and return
- * early.
- */
- pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo);
- if (!pTo || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx,
- &aiFree))
- return;
- assert(pFKey->nCol == 1 || (aiFree && pIdx));
-
- if (aiFree) {
- aiCol = aiFree;
- } else {
- iCol = pFKey->aCol[0].iFrom;
- aiCol = &iCol;
- }
-
- pParse->nTab++;
-
- if (regOld != 0) {
- /* A row is being removed from the child table. Search for the parent.
- * If the parent does not exist, removing the child row resolves an
- * outstanding foreign key constraint violation.
+ parser->nTab++;
+ struct space *parent = space_by_id(fk_def->parent_id);
+ assert(parent != NULL);
+ if (reg_old != 0) {
+ /*
+ * A row is being removed from the child
+ * table. Search for the parent. If the
+ * parent does not exist, removing the
+ * child row resolves an outstanding
+ * foreign key constraint violation.
*/
- fkLookupParent(pParse, pTo, pIdx, pFKey, aiCol,
- regOld, -1, bIgnore);
+ fkey_lookup_parent(parser, parent, fk_def, fk->index_id,
+ reg_old, -1);
}
- if (regNew != 0 && !isSetNullAction(pParse, pFKey)) {
- /* A row is being added to the child table. If a parent row cannot
- * be found, adding the child row has violated the FK constraint.
+ if (reg_new != 0 && !fkey_action_is_set_null(parser, fk)) {
+ /*
+ * A row is being added to the child
+ * table. If a parent row cannot be found,
+ * adding the child row has violated the
+ * FK constraint.
*
- * If this operation is being performed as part of a trigger program
- * that is actually a "SET NULL" action belonging to this very
- * foreign key, then omit this scan altogether. As all child key
- * values are guaranteed to be NULL, it is not possible for adding
- * this row to cause an FK violation.
+ * If this operation is being performed as
+ * part of a trigger program that is
+ * actually a "SET NULL" action belonging
+ * to this very foreign key, then omit
+ * this scan altogether. As all child key
+ * values are guaranteed to be NULL, it is
+ * not possible for adding this row to
+ * cause an FK violation.
*/
- fkLookupParent(pParse, pTo, pIdx, pFKey, aiCol,
- regNew, +1, bIgnore);
+ fkey_lookup_parent(parser, parent, fk_def, fk->index_id,
+ reg_new, +1);
}
-
- sqlite3DbFree(db, aiFree);
}
-
- /* Loop through all the foreign key constraints that refer to this table.
- * (the "child" constraints)
+ /*
+ * Loop through all the foreign key constraints that
+ * refer to this table.
*/
- for (pFKey = sqlite3FkReferences(pTab); pFKey; pFKey = pFKey->pNextTo) {
- Index *pIdx = 0; /* Foreign key index for pFKey */
- SrcList *pSrc;
- int *aiCol = 0;
-
- if (aChange
- && fkParentIsModified(pTab, pFKey, aChange) == 0) {
+ rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
+ struct fkey_def *fk_def = fk->def;
+ if (changed_cols != NULL &&
+ !fkey_parent_is_modified(fk_def, changed_cols))
continue;
- }
-
- if (!pFKey->isDeferred
- && !(user_session->sql_flags & SQLITE_DeferFKs)
- && !pParse->pToplevel && !pParse->isMultiWrite) {
- assert(regOld == 0 && regNew != 0);
- /* Inserting a single row into a parent table cannot cause (or fix)
- * an immediate foreign key violation. So do nothing in this case.
+ if (!fk_def->is_deferred && parser->pToplevel == NULL &&
+ !parser->isMultiWrite) {
+ assert(reg_old == 0 && reg_new != 0);
+ /*
+ * Inserting a single row into a parent
+ * table cannot cause (or fix) an
+ * immediate foreign key violation. So do
+ * nothing in this case.
*/
continue;
}
- if (sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol))
- return;
- assert(aiCol || pFKey->nCol == 1);
-
- /* Create a SrcList structure containing the child table. We need the
- * child table as a SrcList for sqlite3WhereBegin()
+ /*
+ * Create a SrcList structure containing the child
+ * table. We need the child table as a SrcList for
+ * sqlite3WhereBegin().
*/
- pSrc = sqlite3SrcListAppend(db, 0, 0);
- if (pSrc) {
- struct SrcList_item *pItem = pSrc->a;
- pItem->pTab = pFKey->pFrom;
- pItem->zName = pFKey->pFrom->def->name;
- pItem->pTab->nTabRef++;
- pItem->iCursor = pParse->nTab++;
-
- if (regNew != 0) {
- fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey,
- aiCol, regNew, -1);
- }
- if (regOld != 0) {
- int eAction = pFKey->aAction[aChange != 0];
- fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey,
- aiCol, regOld, 1);
- /* If this is a deferred FK constraint, or a CASCADE or SET NULL
- * action applies, then any foreign key violations caused by
- * removing the parent key will be rectified by the action trigger.
- * So do not set the "may-abort" flag in this case.
- *
- * Note 1: If the FK is declared "ON UPDATE CASCADE", then the
- * may-abort flag will eventually be set on this statement anyway
- * (when this function is called as part of processing the UPDATE
- * within the action trigger).
- *
- * Note 2: At first glance it may seem like SQLite could simply omit
- * all OP_FkCounter related scans when either CASCADE or SET NULL
- * applies. The trouble starts if the CASCADE or SET NULL action
- * trigger causes other triggers or action rules attached to the
- * child table to fire. In these cases the fk constraint counters
- * might be set incorrectly if any OP_FkCounter related scans are
- * omitted.
- */
- if (!pFKey->isDeferred && eAction != OE_Cascade
- && eAction != OE_SetNull) {
- sqlite3MayAbort(pParse);
- }
- }
- pItem->zName = 0;
- sqlite3SrcListDelete(db, pSrc);
+ struct SrcList *src = sqlite3SrcListAppend(db, NULL, NULL);
+ if (src == NULL)
+ continue;
+ struct SrcList_item *item = src->a;
+ struct space *child = space_by_id(fk->def->child_id);
+ assert(child != NULL);
+ struct Table *child_tab = sqlite3HashFind(&db->pSchema->tblHash,
+ child->def->name);
+ item->pTab = child_tab;
+ item->zName = sqlite3DbStrDup(db, child->def->name);
+ item->pTab->nTabRef++;
+ item->iCursor = parser->nTab++;
+
+ if (reg_new != 0)
+ fkScanChildren(parser, src, tab, fk->def, reg_new, -1);
+ if (reg_old != 0) {
+ enum fkey_action action = fk_def->on_update;
+ fkScanChildren(parser, src, tab, fk->def, reg_old, 1);
+ /*
+ * If this is a deferred FK constraint, or
+ * a CASCADE or SET NULL action applies,
+ * then any foreign key violations caused
+ * by removing the parent key will be
+ * rectified by the action trigger. So do
+ * not set the "may-abort" flag in this
+ * case.
+ *
+ * Note 1: If the FK is declared "ON
+ * UPDATE CASCADE", then the may-abort
+ * flag will eventually be set on this
+ * statement anyway (when this function is
+ * called as part of processing the UPDATE
+ * within the action trigger).
+ *
+ * Note 2: At first glance it may seem
+ * like SQLite could simply omit all
+ * OP_FkCounter related scans when either
+ * CASCADE or SET NULL applies. The
+ * trouble starts if the CASCADE or SET
+ * NULL action trigger causes other
+ * triggers or action rules attached to
+ * the child table to fire. In these cases
+ * the fk constraint counters might be set
+ * incorrectly if any OP_FkCounter related
+ * scans are omitted.
+ */
+ if (!fk_def->is_deferred &&
+ action != FKEY_ACTION_CASCADE &&
+ action != FKEY_ACTION_SET_NULL)
+ sqlite3MayAbort(parser);
}
- sqlite3DbFree(db, aiCol);
+ sqlite3SrcListDelete(db, src);
}
}
#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
-/*
- * This function is called before generating code to update or delete a
- * row contained in table pTab.
- */
-u32
-sqlite3FkOldmask(Parse * pParse, /* Parse context */
- Table * pTab /* Table being modified */
- )
+uint32_t
+fkey_old_mask(uint32_t space_id)
{
- u32 mask = 0;
+ uint32_t mask = 0;
struct session *user_session = current_session();
-
if (user_session->sql_flags & SQLITE_ForeignKeys) {
- FKey *p;
- for (p = pTab->pFKey; p; p = p->pNextFrom) {
- for (int i = 0; i < p->nCol; i++)
- mask |= COLUMN_MASK(p->aCol[i].iFrom);
+ struct space *space = space_by_id(space_id);
+ struct fkey *fk;
+ rlist_foreach_entry (fk, &space->child_fkey, child_link) {
+ struct fkey_def *def = fk->def;
+ for (uint32_t i = 0; i < def->field_count; ++i)
+ mask |=COLUMN_MASK(def->links[i].child_field);
}
- for (p = sqlite3FkReferences(pTab); p; p = p->pNextTo) {
- Index *pIdx = 0;
- sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
- if (pIdx != NULL) {
- uint32_t part_count =
- pIdx->def->key_def->part_count;
- for (uint32_t i = 0; i < part_count; i++) {
- mask |= COLUMN_MASK(pIdx->def->
- key_def->parts[i].fieldno);
- }
- }
+ rlist_foreach_entry (fk, &space->parent_fkey, parent_link) {
+ struct fkey_def *def = fk->def;
+ for (uint32_t i = 0; i < def->field_count; ++i)
+ mask |= COLUMN_MASK(def->links[i].parent_field);
}
}
return mask;
}
-/*
- * This function is called before generating code to update or delete a
- * row contained in table pTab. If the operation is a DELETE, then
- * parameter aChange is passed a NULL value. For an UPDATE, aChange points
- * to an array of size N, where N is the number of columns in table pTab.
- * If the i'th column is not modified by the UPDATE, then the corresponding
- * entry in the aChange[] array is set to -1. If the column is modified,
- * the value is 0 or greater.
- *
- * If any foreign key processing will be required, this function returns
- * true. If there is no foreign key related processing, this function
- * returns false.
- */
-int
-sqlite3FkRequired(Table * pTab, /* Table being modified */
- int *aChange /* Non-NULL for UPDATE operations */
- )
+bool
+fkey_is_required(uint32_t space_id, int *changes)
{
struct session *user_session = current_session();
- if (user_session->sql_flags & SQLITE_ForeignKeys) {
- if (!aChange) {
- /* A DELETE operation. Foreign key processing is required if the
- * table in question is either the child or parent table for any
- * foreign key constraint.
+ if ((user_session->sql_flags & SQLITE_ForeignKeys) != 0) {
+ struct space *space = space_by_id(space_id);
+ if (changes == NULL) {
+ /*
+ * A DELETE operation. FK processing is
+ * required if space is child or parent.
*/
- return (sqlite3FkReferences(pTab) || pTab->pFKey);
+ return ! rlist_empty(&space->parent_fkey) ||
+ ! rlist_empty(&space->child_fkey);
} else {
- /* This is an UPDATE. Foreign key processing is only required if the
- * operation modifies one or more child or parent key columns.
+ /*
+ * This is an UPDATE. FK processing is
+ * only required if the operation modifies
+ * one or more child or parent key columns.
*/
- FKey *p;
-
- /* Check if any child key columns are being modified. */
- for (p = pTab->pFKey; p; p = p->pNextFrom) {
- if (fkChildIsModified(p, aChange))
- return 1;
+ struct fkey *fk;
+ rlist_foreach_entry (fk, &space->child_fkey,
+ child_link) {
+ if (fkey_child_is_modified(fk->def, changes))
+ return true;
}
-
- /* Check if any parent key columns are being modified. */
- for (p = sqlite3FkReferences(pTab); p; p = p->pNextTo) {
- if (fkParentIsModified(pTab, p, aChange))
- return 1;
+ rlist_foreach_entry (fk, &space->parent_fkey,
+ parent_link) {
+ if (fkey_parent_is_modified(fk->def, changes))
+ return true;
}
}
}
- return 0;
+ return false;
}
/**
* This function is called when an UPDATE or DELETE operation is
* being compiled on table pTab, which is the parent table of
- * foreign-key pFKey.
+ * foreign-key fkey.
* If the current operation is an UPDATE, then the pChanges
* parameter is passed a pointer to the list of columns being
* modified. If it is a DELETE, pChanges is passed a NULL pointer.
*
* It returns a pointer to a sql_trigger structure containing a
* trigger equivalent to the ON UPDATE or ON DELETE action
- * specified by pFKey.
+ * specified by fkey.
* If the action is "NO ACTION" or "RESTRICT", then a NULL pointer
* is returned (these actions require no special handling by the
* triggers sub-system, code for them is created by
* fkScanChildren()).
*
- * For example, if pFKey is the foreign key and pTab is table "p"
+ * For example, if fkey is the foreign key and pTab is table "p"
* in the following schema:
*
* CREATE TABLE p(pk PRIMARY KEY);
@@ -1077,68 +798,47 @@ sqlite3FkRequired(Table * pTab, /* Table being modified */
*
* The returned pointer is cached as part of the foreign key
* object. It is eventually freed along with the rest of the
- * foreign key object by sqlite3FkDelete().
+ * foreign key object by fkey_delete().
*
* @param pParse Parse context.
* @param pTab Table being updated or deleted from.
- * @param pFKey Foreign key to get action for.
- * @param pChanges Change-list for UPDATE, NULL for DELETE.
+ * @param fkey Foreign key to get action for.
+ * @param is_update True if action is on update.
*
* @retval not NULL on success.
* @retval NULL on failure.
*/
static struct sql_trigger *
-fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
- struct ExprList *pChanges)
+fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct fkey *fkey,
+ bool is_update)
{
sqlite3 *db = pParse->db; /* Database handle */
- int action; /* One of OE_None, OE_Cascade etc. */
- /* Trigger definition to return. */
- struct sql_trigger *trigger;
- int iAction = (pChanges != 0); /* 1 for UPDATE, 0 for DELETE */
- struct session *user_session = current_session();
-
- action = pFKey->aAction[iAction];
- if (action == OE_Restrict
- && (user_session->sql_flags & SQLITE_DeferFKs)) {
- return 0;
- }
- trigger = pFKey->apTrigger[iAction];
-
- if (action != ON_CONFLICT_ACTION_NONE && trigger == NULL) {
- char const *zFrom; /* Name of child table */
- int nFrom; /* Length in bytes of zFrom */
- Index *pIdx = 0; /* Parent key index for this FK */
- int *aiCol = 0; /* child table cols -> parent key cols */
+ struct fkey_def *fk_def = fkey->def;
+ enum fkey_action action = is_update ? fk_def->on_update :
+ fk_def->on_delete;
+ struct sql_trigger *trigger = is_update ? fkey->on_update_trigger :
+ fkey->on_delete_trigger;
+ if (action != FKEY_NO_ACTION && trigger == NULL) {
TriggerStep *pStep = 0; /* First (only) step of trigger program */
Expr *pWhere = 0; /* WHERE clause of trigger step */
ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */
Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */
- int i; /* Iterator variable */
Expr *pWhen = 0; /* WHEN clause for the trigger */
-
- if (sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol))
- return 0;
- assert(aiCol || pFKey->nCol == 1);
-
- for (i = 0; i < pFKey->nCol; i++) {
+ struct space *child_space = space_by_id(fk_def->child_id);
+ assert(child_space != NULL);
+ for (uint32_t i = 0; i < fk_def->field_count; ++i) {
Token tOld = { "old", 3, false }; /* Literal "old" token */
Token tNew = { "new", 3, false }; /* Literal "new" token */
Token tFromCol; /* Name of column in child table */
Token tToCol; /* Name of column in parent table */
- int iFromCol; /* Idx of column in child table */
Expr *pEq; /* tFromCol = OLD.tToCol */
- iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
- assert(iFromCol >= 0);
- assert(pIdx != NULL);
+ uint32_t pcol = fk_def->links[i].parent_field;
+ sqlite3TokenInit(&tToCol, pTab->def->fields[pcol].name);
- uint32_t fieldno = pIdx->def->key_def->parts[i].fieldno;
- sqlite3TokenInit(&tToCol,
- pTab->def->fields[fieldno].name);
+ uint32_t chcol = fk_def->links[i].child_field;
sqlite3TokenInit(&tFromCol,
- pFKey->pFrom->def->fields[
- iFromCol].name);
+ child_space->def->fields[chcol].name);
/* Create the expression "OLD.zToCol = zFromCol". It is important
* that the "OLD.zToCol" term is on the LHS of the = operator, so
@@ -1165,7 +865,7 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
*
* WHEN NOT(old.col1 = new.col1 AND ... AND old.colN = new.colN)
*/
- if (pChanges) {
+ if (is_update) {
pEq = sqlite3PExpr(pParse, TK_EQ,
sqlite3PExpr(pParse, TK_DOT,
sqlite3ExprAlloc
@@ -1185,10 +885,10 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
pWhen = sqlite3ExprAnd(db, pWhen, pEq);
}
- if (action != OE_Restrict
- && (action != OE_Cascade || pChanges)) {
+ if (action != FKEY_ACTION_RESTRICT &&
+ (action != FKEY_ACTION_CASCADE || is_update)) {
Expr *pNew;
- if (action == OE_Cascade) {
+ if (action == FKEY_ACTION_CASCADE) {
pNew = sqlite3PExpr(pParse, TK_DOT,
sqlite3ExprAlloc(db,
TK_ID,
@@ -1198,11 +898,11 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
TK_ID,
&tToCol,
0));
- } else if (action == OE_SetDflt) {
+ } else if (action == FKEY_ACTION_SET_DEFAULT) {
+ uint32_t space_id = fk_def->child_id;
Expr *pDflt =
space_column_default_expr(
- pFKey->pFrom->def->id,
- (uint32_t)iFromCol);
+ space_id, chcol);
if (pDflt) {
pNew =
sqlite3ExprDup(db, pDflt,
@@ -1224,12 +924,11 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
0);
}
}
- sqlite3DbFree(db, aiCol);
- zFrom = pFKey->pFrom->def->name;
- nFrom = sqlite3Strlen30(zFrom);
+ const char *zFrom = child_space->def->name;
+ uint32_t nFrom = strlen(zFrom);
- if (action == OE_Restrict) {
+ if (action == FKEY_ACTION_RESTRICT) {
Token tFrom;
Expr *pRaise;
@@ -1253,7 +952,6 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
/* Disable lookaside memory allocation */
db->lookaside.bDisable++;
-
size_t trigger_size = sizeof(struct sql_trigger) +
sizeof(TriggerStep) + nFrom + 1;
trigger =
@@ -1291,11 +989,11 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
assert(pStep != 0);
switch (action) {
- case OE_Restrict:
+ case FKEY_ACTION_RESTRICT:
pStep->op = TK_SELECT;
break;
- case OE_Cascade:
- if (!pChanges) {
+ case FKEY_ACTION_CASCADE:
+ if (! is_update) {
pStep->op = TK_DELETE;
break;
}
@@ -1303,9 +1001,15 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
default:
pStep->op = TK_UPDATE;
}
+
pStep->trigger = trigger;
- pFKey->apTrigger[iAction] = trigger;
- trigger->op = pChanges ? TK_UPDATE : TK_DELETE;
+ if (is_update) {
+ fkey->on_update_trigger = trigger;
+ trigger->op = TK_UPDATE;
+ } else {
+ fkey->on_delete_trigger = trigger;
+ trigger->op = TK_DELETE;
+ }
}
return trigger;
@@ -1329,65 +1033,20 @@ sqlite3FkActions(Parse * pParse, /* Parse context */
* for this operation (either update or delete), invoke the associated
* trigger sub-program.
*/
- if (user_session->sql_flags & SQLITE_ForeignKeys) {
- FKey *pFKey; /* Iterator variable */
- for (pFKey = sqlite3FkReferences(pTab); pFKey;
- pFKey = pFKey->pNextTo) {
- if (aChange == 0
- || fkParentIsModified(pTab, pFKey, aChange)) {
- struct sql_trigger *pAct =
- fkActionTrigger(pParse, pTab, pFKey,
- pChanges);
- if (pAct == NULL)
- continue;
- vdbe_code_row_trigger_direct(pParse, pAct, pTab,
- regOld,
- ON_CONFLICT_ACTION_ABORT,
- 0);
- }
- }
- }
-}
-
-/*
- * Free all memory associated with foreign key definitions attached to
- * table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
- * hash table.
- */
-void
-sqlite3FkDelete(sqlite3 * db, Table * pTab)
-{
- FKey *pFKey; /* Iterator variable */
- FKey *pNext; /* Copy of pFKey->pNextFrom */
-
- for (pFKey = pTab->pFKey; pFKey; pFKey = pNext) {
- /* Remove the FK from the fkeyHash hash table. */
- if (!db || db->pnBytesFreed == 0) {
- if (pFKey->pPrevTo) {
- pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
- } else {
- void *p = (void *)pFKey->pNextTo;
- const char *z =
- (p ? pFKey->pNextTo->zTo : pFKey->zTo);
- sqlite3HashInsert(&pTab->pSchema->fkeyHash, z,
- p);
- }
- if (pFKey->pNextTo) {
- pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
- }
- }
-
- /* EV: R-30323-21917 Each foreign key constraint in SQLite is
- * classified as either immediate or deferred.
- */
- assert(pFKey->isDeferred == 0 || pFKey->isDeferred == 1);
-
- /* Delete any triggers created to implement actions for this FK. */
- fkey_trigger_delete(db, pFKey->apTrigger[0]);
- fkey_trigger_delete(db, pFKey->apTrigger[1]);
-
- pNext = pFKey->pNextFrom;
- sqlite3DbFree(db, pFKey);
+ if ((user_session->sql_flags & SQLITE_ForeignKeys) == 0)
+ return;
+ struct space *space = space_by_id(pTab->def->id);
+ assert(space != NULL);
+ struct fkey *fk;
+ rlist_foreach_entry (fk, &space->parent_fkey, parent_link) {
+ if (aChange != NULL &&
+ !fkey_parent_is_modified(fk->def, aChange))
+ continue;
+ struct sql_trigger *pAct =
+ fkActionTrigger(pParse, pTab, fk, pChanges != NULL);
+ if (pAct == NULL)
+ continue;
+ vdbe_code_row_trigger_direct(pParse, pAct, pTab, regOld,
+ ON_CONFLICT_ACTION_ABORT, 0);
}
}
-#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 432e003c0..94f697f91 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1313,15 +1313,14 @@ sqlite3GenerateConstraintChecks(Parse * pParse, /* The parser context */
(on_error == ON_CONFLICT_ACTION_REPLACE ||
on_error == ON_CONFLICT_ACTION_IGNORE);
bool no_delete_triggers =
- (0 == (user_session->sql_flags &
- SQLITE_RecTriggers) ||
- sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) ==
- NULL);
+ (user_session->sql_flags & SQLITE_RecTriggers) == 0 ||
+ sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) == NULL;
+ struct space *space = space_by_id(pTab->def->id);
+ assert(space != NULL);
bool no_foreign_keys =
- (0 == (user_session->sql_flags &
- SQLITE_ForeignKeys) ||
- (0 == pTab->pFKey &&
- 0 == sqlite3FkReferences(pTab)));
+ (user_session->sql_flags & SQLITE_ForeignKeys) == 0 ||
+ (rlist_empty(&space->child_fkey) &&
+ ! rlist_empty(&space->parent_fkey));
if (no_secondary_indexes && no_foreign_keys &&
proper_error_action && no_delete_triggers) {
@@ -1559,7 +1558,7 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing context */
if (isUpdate || /* Condition 1 */
IsPrimaryKeyIndex(pIdx) || /* Condition 2 */
- sqlite3FkReferences(pTab) || /* Condition 3 */
+ ! rlist_empty(&space->parent_fkey) ||
/* Condition 4 */
(pIdx->def->opts.is_unique &&
pIdx->onError != ON_CONFLICT_ACTION_DEFAULT &&
@@ -1820,10 +1819,11 @@ xferOptimization(Parse * pParse, /* Parser context */
* So the extra complication to make this rule less restrictive is probably
* not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
*/
- if ((user_session->sql_flags & SQLITE_ForeignKeys) != 0
- && pDest->pFKey != 0) {
+ struct space *dest = space_by_id(pDest->def->id);
+ assert(dest != NULL);
+ if ((user_session->sql_flags & SQLITE_ForeignKeys) != 0 &&
+ !rlist_empty(&dest->child_fkey))
return 0;
- }
#endif
if ((user_session->sql_flags & SQLITE_CountRows) != 0) {
return 0; /* xfer opt does not play well with PRAGMA count_changes */
diff --git a/src/box/sql/main.c b/src/box/sql/main.c
index ded3b5b26..41979beb4 100644
--- a/src/box/sql/main.c
+++ b/src/box/sql/main.c
@@ -730,11 +730,6 @@ sqlite3RollbackAll(Vdbe * pVdbe, int tripCode)
{
sqlite3 *db = pVdbe->db;
(void)tripCode;
- struct session *user_session = current_session();
-
- /* DDL is impossible inside a transaction. */
- assert((user_session->sql_flags & SQLITE_InternChanges) == 0
- || db->init.busy == 1);
/* If one has been configured, invoke the rollback-hook callback */
if (db->xRollbackCallback && (!pVdbe->auto_commit)) {
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f565..6869feb9c 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -51,6 +51,7 @@
//
%include {
#include "sqliteInt.h"
+#include "box/fkey.h"
/*
** Disable all error recovery processing in the parser push-down
@@ -281,8 +282,8 @@ ccons ::= UNIQUE onconf(R). {sql_create_index(pParse,0,0,0,R,0,0,
SQL_INDEX_TYPE_CONSTRAINT_UNIQUE);}
ccons ::= CHECK LP expr(X) RP. {sql_add_check_constraint(pParse,&X);}
ccons ::= REFERENCES nm(T) eidlist_opt(TA) refargs(R).
- {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
-ccons ::= defer_subclause(D). {sqlite3DeferForeignKey(pParse,D);}
+ {sql_create_foreign_key(pParse, NULL, NULL, NULL, &T, TA, false, R);}
+ccons ::= defer_subclause(D). {fkey_change_defer_mode(pParse, D);}
ccons ::= COLLATE id(C). {sqlite3AddCollateType(pParse, &C);}
// The optional AUTOINCREMENT keyword
@@ -296,19 +297,23 @@ autoinc(X) ::= AUTOINCR. {X = 1;}
// check fails.
//
%type refargs {int}
-refargs(A) ::= . { A = ON_CONFLICT_ACTION_NONE*0x0101; /* EV: R-19803-45884 */}
+refargs(A) ::= . { A = FKEY_NO_ACTION; }
refargs(A) ::= refargs(A) refarg(Y). { A = (A & ~Y.mask) | Y.value; }
%type refarg {struct {int value; int mask;}}
-refarg(A) ::= MATCH nm. { A.value = 0; A.mask = 0x000000; }
+refarg(A) ::= MATCH matcharg(X). { A.value = X; A.mask = 0xff0000; }
refarg(A) ::= ON INSERT refact. { A.value = 0; A.mask = 0x000000; }
refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; }
refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; }
+%type matcharg {int}
+matcharg(A) ::= SIMPLE. { A = FKEY_MATCH_SIMPLE; }
+matcharg(A) ::= PARTIAL. { A = FKEY_MATCH_PARTIAL; }
+matcharg(A) ::= FULL. { A = FKEY_MATCH_FULL; }
%type refact {int}
-refact(A) ::= SET NULL. { A = OE_SetNull; /* EV: R-33326-45252 */}
-refact(A) ::= SET DEFAULT. { A = OE_SetDflt; /* EV: R-33326-45252 */}
-refact(A) ::= CASCADE. { A = OE_Cascade; /* EV: R-33326-45252 */}
-refact(A) ::= RESTRICT. { A = OE_Restrict; /* EV: R-33326-45252 */}
-refact(A) ::= NO ACTION. { A = ON_CONFLICT_ACTION_NONE; /* EV: R-33326-45252 */}
+refact(A) ::= SET NULL. { A = FKEY_ACTION_SET_NULL; }
+refact(A) ::= SET DEFAULT. { A = FKEY_ACTION_SET_DEFAULT; }
+refact(A) ::= CASCADE. { A = FKEY_ACTION_CASCADE; }
+refact(A) ::= RESTRICT. { A = FKEY_ACTION_RESTRICT; }
+refact(A) ::= NO ACTION. { A = FKEY_NO_ACTION; }
%type defer_subclause {int}
defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt. {A = 0;}
defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X). {A = X;}
@@ -334,8 +339,7 @@ tcons ::= CHECK LP expr(E) RP onconf.
{sql_add_check_constraint(pParse,&E);}
tcons ::= FOREIGN KEY LP eidlist(FA) RP
REFERENCES nm(T) eidlist_opt(TA) refargs(R) defer_subclause_opt(D). {
- sqlite3CreateForeignKey(pParse, FA, &T, TA, R);
- sqlite3DeferForeignKey(pParse, D);
+ sql_create_foreign_key(pParse, NULL, NULL, FA, &T, TA, D, R);
}
%type defer_subclause_opt {int}
defer_subclause_opt(A) ::= . {A = 0;}
@@ -1431,6 +1435,17 @@ cmd ::= ANALYZE nm(X). {sqlite3Analyze(pParse, &X);}
cmd ::= ALTER TABLE fullname(X) RENAME TO nm(Z). {
sqlite3AlterRenameTable(pParse,X,&Z);
}
+
+cmd ::= ALTER TABLE fullname(X) ADD CONSTRAINT nm(Z) FOREIGN KEY
+ LP eidlist(FA) RP REFERENCES nm(T) eidlist_opt(TA) refargs(R)
+ defer_subclause_opt(D). {
+ sql_create_foreign_key(pParse, X, &Z, FA, &T, TA, D, R);
+}
+
+cmd ::= ALTER TABLE fullname(X) DROP CONSTRAINT nm(Z). {
+ sql_drop_foreign_key(pParse, X, &Z);
+}
+
/* gh-3075: Commented until ALTER ADD COLUMN is implemeneted. */
/* cmd ::= ALTER TABLE add_column_fullname */
/* ADD kwcolumn_opt columnname(Y) carglist. { */
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index d427f7844..94ff7168a 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -35,6 +35,7 @@
#include <box/index.h>
#include <box/box.h>
#include <box/tuple.h>
+#include <box/fkey.h>
#include "box/schema.h"
#include "box/coll_id_cache.h"
#include "sqliteInt.h"
@@ -154,36 +155,6 @@ returnSingleInt(Vdbe * v, i64 value)
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1);
}
-/*
- * Return a human-readable name for a constraint resolution action.
- */
-#ifndef SQLITE_OMIT_FOREIGN_KEY
-static const char *
-actionName(u8 action)
-{
- const char *zName;
- switch (action) {
- case OE_SetNull:
- zName = "SET NULL";
- break;
- case OE_SetDflt:
- zName = "SET DEFAULT";
- break;
- case OE_Cascade:
- zName = "CASCADE";
- break;
- case OE_Restrict:
- zName = "RESTRICT";
- break;
- default:
- zName = "NO ACTION";
- assert(action == ON_CONFLICT_ACTION_NONE);
- break;
- }
- return zName;
-}
-#endif
-
/*
* Locate a pragma in the aPragmaName[] array.
*/
@@ -588,206 +559,38 @@ sqlite3Pragma(Parse * pParse, Token * pId, /* First part of [schema.]id field */
case PragTyp_FOREIGN_KEY_LIST:{
if (zRight == NULL)
break;
- Table *table = sqlite3HashFind(&db->pSchema->tblHash, zRight);
- if (table == NULL)
- break;
- FKey *fkey = table->pFKey;
- if (fkey == NULL)
+ uint32_t space_id = box_space_id_by_name(zRight,
+ strlen(zRight));
+ if (space_id == BOX_ID_NIL)
break;
+ struct space *space = space_by_id(space_id);
int i = 0;
pParse->nMem = 8;
- while (fkey != NULL) {
- for (int j = 0; j < fkey->nCol; j++) {
- const char *name =
- table->def->fields[
- fkey->aCol[j].iFrom].name;
+ struct fkey *fkey;
+ rlist_foreach_entry (fkey, &space->child_fkey, child_link) {
+ for (uint32_t j = 0; j < fkey->def->field_count; j++) {
+ struct space *parent =
+ space_by_id(fkey->def->parent_id);
+ assert(parent != NULL);
+ uint32_t ch_fl = fkey->def->links[j].child_field;
+ const char *child_col =
+ space->def->fields[ch_fl].name;
+ uint32_t pr_fl = fkey->def->links[j].parent_field;
+ const char *parent_col =
+ parent->def->fields[pr_fl].name;
sqlite3VdbeMultiLoad(v, 1, "iissssss", i, j,
- fkey->zTo, name,
- fkey->aCol[j].zCol,
- actionName(
- fkey->aAction[1]),
- actionName(
- fkey->aAction[0]),
+ parent->def->name,
+ child_col, parent_col,
+ fkey_action_strs[fkey->def->on_delete],
+ fkey_action_strs[fkey->def->on_update],
"NONE");
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 8);
}
++i;
- fkey = fkey->pNextFrom;
}
break;
}
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
-
-#ifndef SQLITE_OMIT_FOREIGN_KEY
- case PragTyp_FOREIGN_KEY_CHECK:{
- FKey *pFK; /* A foreign key constraint */
- Table *pTab; /* Child table contain "REFERENCES"
- * keyword
- */
- Table *pParent; /* Parent table that child points to */
- Index *pIdx; /* Index in the parent table */
- int i; /* Loop counter: Foreign key number for pTab */
- int j; /* Loop counter: Field of the foreign key */
- HashElem *k; /* Loop counter: Next table in schema */
- int x; /* result variable */
- int regResult; /* 3 registers to hold a result row */
- int regKey; /* Register to hold key for checking
- * the FK
- */
- int regRow; /* Registers to hold a row from pTab */
- int addrTop; /* Top of a loop checking foreign keys */
- int addrOk; /* Jump here if the key is OK */
- int *aiCols; /* child to parent column mapping */
-
- regResult = pParse->nMem + 1;
- pParse->nMem += 4;
- regKey = ++pParse->nMem;
- regRow = ++pParse->nMem;
- k = sqliteHashFirst(&db->pSchema->tblHash);
- while (k) {
- if (zRight) {
- pTab =
- sqlite3LocateTable(pParse, 0,
- zRight);
- k = 0;
- } else {
- pTab = (Table *) sqliteHashData(k);
- k = sqliteHashNext(k);
- }
- if (pTab == 0 || pTab->pFKey == 0)
- continue;
- if ((int)pTab->def->field_count + regRow > pParse->nMem)
- pParse->nMem = pTab->def->field_count + regRow;
- sqlite3OpenTable(pParse, 0, pTab, OP_OpenRead);
- sqlite3VdbeLoadString(v, regResult,
- pTab->def->name);
- for (i = 1, pFK = pTab->pFKey; pFK;
- i++, pFK = pFK->pNextFrom) {
- pParent =
- sqlite3HashFind(&db->pSchema->tblHash,
- pFK->zTo);
- if (pParent == NULL)
- continue;
- pIdx = 0;
- x = sqlite3FkLocateIndex(pParse,
- pParent, pFK,
- &pIdx, 0);
- if (x != 0) {
- k = 0;
- break;
- }
- if (pIdx == NULL) {
- sqlite3OpenTable(pParse, i,
- pParent,
- OP_OpenRead);
- continue;
- }
- struct space *space =
- space_cache_find(pIdx->pTable->
- def->id);
- assert(space != NULL);
- sqlite3VdbeAddOp4(v, OP_OpenRead, i,
- pIdx->def->iid, 0,
- (void *) space,
- P4_SPACEPTR);
-
- }
- assert(pParse->nErr > 0 || pFK == 0);
- if (pFK)
- break;
- if (pParse->nTab < i)
- pParse->nTab = i;
- addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
- VdbeCoverage(v);
- for (i = 1, pFK = pTab->pFKey; pFK;
- i++, pFK = pFK->pNextFrom) {
- pParent =
- sqlite3HashFind(&db->pSchema->tblHash,
- pFK->zTo);
- pIdx = 0;
- aiCols = 0;
- if (pParent) {
- x = sqlite3FkLocateIndex(pParse,
- pParent,
- pFK,
- &pIdx,
- &aiCols);
- assert(x == 0);
- }
- addrOk = sqlite3VdbeMakeLabel(v);
- if (pParent && pIdx == 0) {
- int iKey = pFK->aCol[0].iFrom;
- assert(iKey >= 0 && iKey <
- (int)pTab->def->field_count);
- sqlite3VdbeAddOp3(v,
- OP_Column,
- 0,
- iKey,
- regRow);
- sqlite3ColumnDefault(v,
- pTab->def,
- iKey,
- regRow);
- sqlite3VdbeAddOp2(v,
- OP_IsNull,
- regRow,
- addrOk);
- VdbeCoverage(v);
- sqlite3VdbeGoto(v, addrOk);
- sqlite3VdbeJumpHere(v,
- sqlite3VdbeCurrentAddr
- (v) - 2);
- } else {
- for (j = 0; j < pFK->nCol; j++) {
- sqlite3ExprCodeGetColumnOfTable
- (v, pTab->def, 0,
- aiCols ? aiCols[j]
- : pFK->aCol[j].
- iFrom, regRow + j);
- sqlite3VdbeAddOp2(v,
- OP_IsNull,
- regRow
- + j,
- addrOk);
- VdbeCoverage(v);
- }
- if (pParent) {
- sqlite3VdbeAddOp4(v,
- OP_MakeRecord,
- regRow,
- pFK->
- nCol,
- regKey,
- sqlite3IndexAffinityStr
- (db,
- pIdx),
- pFK->
- nCol);
- sqlite3VdbeAddOp4Int(v,
- OP_Found,
- i,
- addrOk,
- regKey,
- 0);
- VdbeCoverage(v);
- }
- }
- sqlite3VdbeMultiLoad(v, regResult + 2,
- "si", pFK->zTo,
- i - 1);
- sqlite3VdbeAddOp2(v, OP_ResultRow,
- regResult, 4);
- sqlite3VdbeResolveLabel(v, addrOk);
- sqlite3DbFree(db, aiCols);
- }
- sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop + 1);
- VdbeCoverage(v);
- sqlite3VdbeJumpHere(v, addrTop);
- }
- break;
- }
-#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
-
#ifndef NDEBUG
case PragTyp_PARSER_TRACE:{
if (zRight) {
diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
index 795c98c6d..4f635b080 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -10,7 +10,6 @@
#define PragTyp_CASE_SENSITIVE_LIKE 2
#define PragTyp_COLLATION_LIST 3
#define PragTyp_FLAG 5
-#define PragTyp_FOREIGN_KEY_CHECK 8
#define PragTyp_FOREIGN_KEY_LIST 9
#define PragTyp_INDEX_INFO 10
#define PragTyp_INDEX_LIST 11
@@ -79,8 +78,7 @@ static const char *const pragCName[] = {
/* 34 */ "on_update",
/* 35 */ "on_delete",
/* 36 */ "match",
- /* 37 */ "table",
- /* Used by: foreign_key_check */
+ /* 37 */ "table",
/* 38 */ "rowid",
/* 39 */ "parent",
/* 40 */ "fkid",
@@ -135,13 +133,6 @@ static const PragmaName aPragmaName[] = {
/* iArg: */ SQLITE_DeferFKs},
#endif
#endif
-#if !defined(SQLITE_OMIT_FOREIGN_KEY)
- { /* zName: */ "foreign_key_check",
- /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK,
- /* ePragFlg: */ PragFlg_NeedSchema,
- /* ColNames: */ 37, 4,
- /* iArg: */ 0},
-#endif
#if !defined(SQLITE_OMIT_FOREIGN_KEY)
{ /* zName: */ "foreign_key_list",
/* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST,
diff --git a/src/box/sql/prepare.c b/src/box/sql/prepare.c
index 14239c489..ca6362dbf 100644
--- a/src/box/sql/prepare.c
+++ b/src/box/sql/prepare.c
@@ -418,6 +418,7 @@ sql_parser_create(struct Parse *parser, sqlite3 *db)
{
memset(parser, 0, sizeof(struct Parse));
parser->db = db;
+ rlist_create(&parser->new_fkey);
region_create(&parser->region, &cord()->slabc);
}
@@ -428,6 +429,9 @@ sql_parser_destroy(Parse *parser)
sqlite3 *db = parser->db;
sqlite3DbFree(db, parser->aLabel);
sql_expr_list_delete(db, parser->pConstExpr);
+ struct fkey_parse *fk;
+ rlist_foreach_entry(fk, &parser->new_fkey, link)
+ sql_expr_list_delete(db, fk->selfref_cols);
if (db != NULL) {
assert(db->lookaside.bDisable >=
parser->disableLookaside);
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index c9923a777..b340056d4 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -1472,7 +1472,6 @@ typedef struct Schema Schema;
typedef struct Expr Expr;
typedef struct ExprList ExprList;
typedef struct ExprSpan ExprSpan;
-typedef struct FKey FKey;
typedef struct FuncDestructor FuncDestructor;
typedef struct FuncDef FuncDef;
typedef struct FuncDefHash FuncDefHash;
@@ -1525,7 +1524,6 @@ typedef int VList;
struct Schema {
int schema_cookie; /* Database schema version number for this file */
Hash tblHash; /* All tables indexed by name */
- Hash fkeyHash; /* All foreign keys by referenced table name */
};
/*
@@ -1912,7 +1910,6 @@ struct Column {
struct Table {
Column *aCol; /* Information about each column */
Index *pIndex; /* List of SQL indexes on this table. */
- FKey *pFKey; /* Linked list of all foreign keys in this table */
char *zColAff; /* String defining the affinity of each column */
/* ... also used as column name list in a VIEW */
Hash idxHash; /* All (named) indices indexed by name */
@@ -1975,42 +1972,7 @@ sql_space_tuple_log_count(struct Table *tab);
* Each REFERENCES clause generates an instance of the following structure
* which is attached to the from-table. The to-table need not exist when
* the from-table is created. The existence of the to-table is not checked.
- *
- * The list of all parents for child Table X is held at X.pFKey.
- *
- * A list of all children for a table named Z (which might not even exist)
- * is held in Schema.fkeyHash with a hash key of Z.
- */
-struct FKey {
- Table *pFrom; /* Table containing the REFERENCES clause (aka: Child) */
- FKey *pNextFrom; /* Next FKey with the same in pFrom. Next parent of pFrom */
- char *zTo; /* Name of table that the key points to (aka: Parent) */
- FKey *pNextTo; /* Next with the same zTo. Next child of zTo. */
- FKey *pPrevTo; /* Previous with the same zTo */
- int nCol; /* Number of columns in this key */
- /* EV: R-30323-21917 */
- u8 isDeferred; /* True if constraint checking is deferred till COMMIT */
- u8 aAction[2]; /* ON DELETE and ON UPDATE actions, respectively */
- /** Triggers for aAction[] actions. */
- struct sql_trigger *apTrigger[2];
- struct sColMap { /* Mapping of columns in pFrom to columns in zTo */
- int iFrom; /* Index of column in pFrom */
- char *zCol; /* Name of column in zTo. If NULL use PRIMARY KEY */
- } aCol[1]; /* One entry for each of nCol columns */
-};
-
-/*
- * RESTRICT, SETNULL, and CASCADE actions apply only to foreign keys.
- * RESTRICT is the same as ABORT for IMMEDIATE foreign keys and the
- * same as ROLLBACK for DEFERRED keys. SETNULL means that the foreign
- * key is set to NULL. CASCADE means that a DELETE or UPDATE of the
- * referenced table row is propagated into the row that holds the
- * foreign key.
- */
-#define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
-#define OE_SetNull 7 /* Set the foreign key value to NULL */
-#define OE_SetDflt 8 /* Set the foreign key value to its default */
-#define OE_Cascade 9 /* Cascade the changes */
+ */
/*
* This object holds a record which has been parsed out into individual
@@ -2844,6 +2806,33 @@ enum ast_type {
ast_type_MAX
};
+/**
+ * Structure representing foreign keys constraints appeared
+ * within CREATE TABLE statement. Used only during parsing.
+ */
+struct fkey_parse {
+ /**
+ * Foreign keys constraint declared in <CREATE TABLE ...>
+ * statement. They must be coded after space creation.
+ */
+ struct fkey_def *fkey;
+ /**
+ * If inside CREATE TABLE statement we want to declare
+ * self-referenced FK constraint, we must delay their
+ * resolution until the end of parsing of all columns.
+ * E.g.: CREATE TABLE t1(id REFERENCES t1(b), b);
+ */
+ struct ExprList *selfref_cols;
+ /**
+ * Still, self-referenced columns might be NULL, if
+ * we declare FK constraints referencing PK:
+ * CREATE TABLE t1(id REFERENCES t1) - it is a valid case.
+ */
+ bool is_self_referenced;
+ /** Organize these structs into linked list. */
+ struct rlist link;
+};
+
/*
* An SQL parser context. A copy of this structure is passed through
* the parser and down into all the parser action routine in order to
@@ -2936,7 +2925,15 @@ struct Parse {
TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */
With *pWith; /* Current WITH clause, or NULL */
With *pWithToFree; /* Free this WITH object at the end of the parse */
-
+ /**
+ * Number of FK constraints declared within
+ * CREATE TABLE statement.
+ */
+ uint32_t fkey_count;
+ /**
+ * Foreign key constraint appeared in CREATE TABLE stmt.
+ */
+ struct rlist new_fkey;
bool initiateTTrans; /* Initiate Tarantool transaction */
/** If set - do not emit byte code at all, just parse. */
bool parse_only;
@@ -4240,8 +4237,57 @@ sql_trigger_colmask(Parse *parser, struct sql_trigger *trigger,
#define sqlite3IsToplevel(p) ((p)->pToplevel==0)
int sqlite3JoinType(Parse *, Token *, Token *, Token *);
-void sqlite3CreateForeignKey(Parse *, ExprList *, Token *, ExprList *, int);
-void sqlite3DeferForeignKey(Parse *, int);
+
+/**
+ * Change defer mode of last FK constraint processed during
+ * <CREATE TABLE> statement.
+ *
+ * @param parse_context Current parsing context.
+ * @param is_deferred Change defer mode to this value.
+ */
+void
+fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred);
+
+/**
+ * Function called from parser to handle
+ * <ALTER TABLE child ADD CONSTRAINT constraint
+ * FOREIGN KEY (child_cols) REFERENCES parent (parent_cols)>
+ * OR to handle <CREATE TABLE ...>
+ *
+ * @param parse_context Parsing context.
+ * @param child Name of table to be altered. NULL on CREATE TABLE
+ * statement processing.
+ * @param constraint Name of the constraint to be created. May be
+ * NULL on CREATE TABLE statement processing.
+ * Then, auto-generated name is used.
+ * @param child_cols Columns of child table involved in FK.
+ * May be NULL on CREATE TABLE statement processing.
+ * If so, the last column added is used.
+ * @param parent Name of referenced table.
+ * @param parent_cols List of referenced columns. If NULL, columns
+ * which make up PK of referenced table are used.
+ * @param is_deferred Is FK constraint initially deferred.
+ * @param actions ON DELETE, UPDATE and INSERT resolution
+ * algorithms (e.g. CASCADE, RESTRICT etc).
+ */
+void
+sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
+ struct Token *constraint, struct ExprList *child_cols,
+ struct Token *parent, struct ExprList *parent_cols,
+ bool is_deferred, int actions);
+
+/**
+ * Function called from parser to handle
+ * <ALTER TABLE table DROP CONSTRAINT constraint> SQL statement.
+ *
+ * @param parse_context Parsing context.
+ * @param table Table to be altered.
+ * @param constraint Name of constraint to be dropped.
+ */
+void
+sql_drop_foreign_key(struct Parse *parse_context, struct SrcList *table,
+ struct Token *constraint);
+
void sqlite3Detach(Parse *, Expr *);
void sqlite3FixInit(DbFixer *, Parse *, const char *, const Token *);
int sqlite3FixSrcList(DbFixer *, SrcList *);
@@ -4517,8 +4563,6 @@ sqlite3ColumnDefault(Vdbe *v, struct space_def *def, int i, int ireg);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
char* rename_table(sqlite3 *, const char *, const char *, bool *);
-char* rename_parent_table(sqlite3 *, const char *, const char *, const char *,
- uint32_t *, uint32_t *);
char* rename_trigger(sqlite3 *, char const *, char const *, bool *);
/**
* Find a collation by name. Set error in @a parser if not found.
@@ -4668,25 +4712,35 @@ void sqlite3WithPush(Parse *, With *, u8);
* this case foreign keys are parsed, but no other functionality is
* provided (enforcement of FK constraints requires the triggers sub-system).
*/
-#if !defined(SQLITE_OMIT_FOREIGN_KEY)
void sqlite3FkCheck(Parse *, Table *, int, int, int *);
void sqlite3FkActions(Parse *, Table *, ExprList *, int, int *);
-int sqlite3FkRequired(Table *, int *);
-u32 sqlite3FkOldmask(Parse *, Table *);
-FKey *sqlite3FkReferences(Table *);
-#else
-#define sqlite3FkActions(a,b,c,d,e)
-#define sqlite3FkCheck(a,b,c,d,e,f)
-#define sqlite3FkOldmask(a,b) 0
-#define sqlite3FkRequired(b,c) 0
-#endif
-#ifndef SQLITE_OMIT_FOREIGN_KEY
-void sqlite3FkDelete(sqlite3 *, Table *);
-int sqlite3FkLocateIndex(Parse *, Table *, FKey *, Index **, int **);
-#else
-#define sqlite3FkDelete(a,b)
-#define sqlite3FkLocateIndex(a,b,c,d,e)
-#endif
+
+/**
+ * This function is called before generating code to update or
+ * delete a row contained in given space. If the operation is
+ * a DELETE, then parameter changes is passed a NULL value.
+ * For an UPDATE, changes points to an array of size N, where N
+ * is the number of columns in table. If the i'th column is not
+ * modified by the UPDATE, then the corresponding entry in the
+ * changes[] array is set to -1. If the column is modified,
+ * the value is 0 or greater.
+ *
+ * @param space_id Id of space to be modified.
+ * @param changes Array of modified fields for UPDATE.
+ * @retval True, if any foreign key processing will be required.
+ */
+bool
+fkey_is_required(uint32_t space_id, int *changes);
+
+/**
+ * This function is called before generating code to update or
+ * delete a row contained in given table.
+ *
+ * @param space_id Id of space being modified.
+ * @retval Mask containing fields to be involved in FK testing.
+ */
+uint32_t
+fkey_old_mask(uint32_t space_id);
/*
* Available fault injectors. Should be numbered beginning with 0.
diff --git a/src/box/sql/status.c b/src/box/sql/status.c
index 5bb1f8f14..209ed8571 100644
--- a/src/box/sql/status.c
+++ b/src/box/sql/status.c
@@ -244,13 +244,8 @@ sqlite3_db_status(sqlite3 * db, /* The database connection whose status is desir
Schema *pSchema = db->pSchema;
if (ALWAYS(pSchema != 0)) {
HashElem *p;
-
- nByte +=
- ROUND8(sizeof(HashElem)) *
- (pSchema->tblHash.count +
- pSchema->fkeyHash.count);
- nByte += sqlite3_msize(pSchema->tblHash.ht);
- nByte += sqlite3_msize(pSchema->fkeyHash.ht);
+ nByte += ROUND8(sizeof(HashElem)) *
+ pSchema->tblHash.count;
for (p = sqliteHashFirst(&pSchema->tblHash); p;
p = sqliteHashNext(p)) {
diff --git a/src/box/sql/tarantoolInt.h b/src/box/sql/tarantoolInt.h
index bc61e8426..69c2b9bc6 100644
--- a/src/box/sql/tarantoolInt.h
+++ b/src/box/sql/tarantoolInt.h
@@ -91,11 +91,6 @@ sql_rename_table(uint32_t space_id, const char *new_name, char **sql_stmt);
int tarantoolSqlite3RenameTrigger(const char *zTriggerName,
const char *zOldName, const char *zNewName);
-/* Alter create table statement of child foreign key table by
- * replacing parent table name in create table statement.*/
-int tarantoolSqlite3RenameParentTable(int iTab, const char *zOldParentName,
- const char *zNewParentName);
-
/* Interface for ephemeral tables. */
int tarantoolSqlite3EphemeralCreate(BtCursor * pCur, uint32_t filed_count,
struct key_def *def);
@@ -154,6 +149,18 @@ int tarantoolSqlite3MakeTableFormat(Table * pTable, void *buf);
*/
int tarantoolSqlite3MakeTableOpts(Table * pTable, const char *zSql, char *buf);
+/**
+ * Encode links of given foreign key constraint into MsgPack.
+ *
+ * @param fkey Encode links of this foreign key contraint.
+ * @param buf Buffer to hold encoded links. Can be NULL.
+ * In this case function would simply calculate
+ * memory required for such buffer.
+ * @retval Length of encoded array.
+ */
+int
+fkey_encode_links(const struct fkey_def *fkey, char *buf);
+
/*
* Format "parts" array for _index entry.
* Returns result size.
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index d51a05cad..8eb5f8f13 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -229,7 +229,7 @@ sqlite3Update(Parse * pParse, /* The parser context */
*/
pTabList->a[0].colUsed = 0;
- hasFK = sqlite3FkRequired(pTab, aXRef);
+ hasFK = fkey_is_required(pTab->def->id, aXRef);
/* There is one entry in the aRegIdx[] array for each index on the table
* being updated. Fill in aRegIdx[] with a register number that will hold
@@ -433,7 +433,7 @@ sqlite3Update(Parse * pParse, /* The parser context */
* information is needed
*/
if (chngPk != 0 || hasFK != 0 || trigger != NULL) {
- u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
+ u32 oldmask = hasFK ? fkey_old_mask(pTab->def->id) : 0;
oldmask |= sql_trigger_colmask(pParse, trigger, pChanges, 0,
TRIGGER_BEFORE | TRIGGER_AFTER,
pTab, on_error);
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 8e6e14f5d..159a4ad78 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
* in this file for details. If in doubt, do not deviate from existing
* commenting and indentation practices when changing or adding code.
*/
+#include <box/fkey.h>
#include "box/txn.h"
#include "box/session.h"
#include "sqliteInt.h"
@@ -4642,7 +4643,6 @@ case OP_RenameTable: {
const char *zOldTableName;
const char *zNewTableName;
Table *pTab;
- FKey *pFKey;
struct init_data init;
char *zSqlStmt;
@@ -4661,20 +4661,6 @@ case OP_RenameTable: {
rc = sql_rename_table(space_id, zNewTableName, &zSqlStmt);
if (rc) goto abort_due_to_error;
- /* If it is parent table, all children statements should be updated. */
- for (pFKey = sqlite3FkReferences(pTab); pFKey; pFKey = pFKey->pNextTo) {
- assert(pFKey->zTo != NULL);
- assert(pFKey->pFrom != NULL);
- rc = tarantoolSqlite3RenameParentTable(pFKey->pFrom->def->id,
- pFKey->zTo,
- zNewTableName);
- if (rc) goto abort_due_to_error;
- pFKey->zTo = sqlite3DbStrNDup(db, zNewTableName,
- sqlite3Strlen30(zNewTableName));
- sqlite3HashInsert(&db->pSchema->fkeyHash, zOldTableName, 0);
- sqlite3HashInsert(&db->pSchema->fkeyHash, zNewTableName, pFKey);
- }
-
sqlite3UnlinkAndDeleteTable(db, pTab->def->name);
init.db = db;
diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua
index a1f6a24b4..db87c7003 100755
--- a/test/sql-tap/alter.test.lua
+++ b/test/sql-tap/alter.test.lua
@@ -313,8 +313,8 @@ test:do_execsql_test(
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
- CREATE TABLE t2(id INT PRIMARY KEY);
- CREATE TABLE t3(id INT PRIMARY KEY);
+ CREATE TABLE t2(id PRIMARY KEY);
+ CREATE TABLE t3(id PRIMARY KEY);
CREATE TABLE t1(a PRIMARY KEY, b, c, FOREIGN KEY(b) REFERENCES t2(id), FOREIGN KEY(c) REFERENCES t3(id));
INSERT INTO t2 VALUES(1);
INSERT INTO t3 VALUES(2);
diff --git a/test/sql-tap/alter2.test.lua b/test/sql-tap/alter2.test.lua
new file mode 100755
index 000000000..f990a4c07
--- /dev/null
+++ b/test/sql-tap/alter2.test.lua
@@ -0,0 +1,216 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(17)
+
+-- This suite is aimed to test ALTER TABLE ADD CONSTRAINT statement.
+--
+
+test:do_catchsql_test(
+ "alter2-1.1",
+ [[
+ CREATE TABLE t1(id PRIMARY KEY, a, b);
+ ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(id);
+ ALTER TABLE t1 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1;
+ INSERT INTO t1 VALUES(1, 1, 2);
+ ]], {
+ -- <alter2-1.1>
+ 0
+ -- </alter2-1.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.2",
+ [[
+ INSERT INTO t1 VALUES(2, 3, 2);
+ ]], {
+ -- <alter2-1.2>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-1.2>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.3",
+ [[
+ DELETE FROM t1;
+ ]], {
+ -- <alter2-1.3>
+ 0
+ -- </alter2-1.3>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.4",
+ [[
+ ALTER TABLE t1 DROP CONSTRAINT fk1;
+ INSERT INTO t1 VALUES(2, 3, 2);
+ ]], {
+ -- <alter2-1.4>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-1.4>
+ })
+
+test:do_execsql_test(
+ "alter2-1.5",
+ [[
+ ALTER TABLE t1 DROP CONSTRAINT fk2;
+ INSERT INTO t1 VALUES(2, 3, 2);
+ SELECT * FROM t1;
+ ]], {
+ -- <alter2-1.5>
+ 2, 3, 2
+ -- </alter2-1.5>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.6",
+ [[
+ DELETE FROM t1;
+ CREATE UNIQUE INDEX i1 ON t1(b, a);
+ ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a, b) REFERENCES t1(b, a);
+ INSERT INTO t1 VALUES(3, 1, 1);
+ INSERT INTO t1 VALUES(4, 2, 1);
+ ]], {
+ -- <alter2-1.6>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-1.6>
+ })
+
+test:do_execsql_test(
+ "alter2-1.7",
+ [[
+ ALTER TABLE t1 DROP CONSTRAINT fk1;
+ INSERT INTO t1 VALUES(5, 2, 1);
+ SELECT * FROM t1;
+ ]], {
+ -- <alter2-1.7>
+ 3, 1, 1, 5, 2, 1
+ -- </alter2-1.7>
+ })
+
+test:do_catchsql_test(
+ "alter2-1.8",
+ [[
+ DELETE FROM t1;
+ ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(id);
+ ALTER TABLE t1 ADD CONSTRAINT fk2 FOREIGN KEY (a, b) REFERENCES t1(b, a);
+ DROP TABLE t1;
+ ]], {
+ -- <alter2-1.8>
+ 0
+ -- </alter2-1.8>
+ })
+
+test:do_execsql_test(
+ "alter2-1.9",
+ [[
+ SELECT * FROM "_fk_constraint";
+ ]], {
+ -- <alter2-1.9>
+ -- </alter2-1.9>
+ })
+
+test:do_catchsql_test(
+ "alter2-2.1",
+ [[
+ CREATE TABLE child (id PRIMARY KEY, a, b);
+ CREATE TABLE parent (id PRIMARY KEY, c UNIQUE, d);
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent(c);
+ ALTER TABLE parent ADD CONSTRAINT fk FOREIGN KEY (c) REFERENCES parent;
+ INSERT INTO parent VALUES(1, 2, 3);
+ ]], {
+ -- <alter2-2.1>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-2.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-2.2",
+ [[
+ INSERT INTO parent VALUES(1, 1, 2);
+ INSERT INTO child VALUES(2, 1, 1);
+ ]], {
+ -- <alter2-2.2>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-2.2>
+ })
+
+test:do_catchsql_test(
+ "alter2-2.3",
+ [[
+ ALTER TABLE child DROP CONSTRAINT fk;
+ INSERT INTO parent VALUES(3, 4, 2);
+ ]], {
+ -- <alter2-2.3>
+ 1, "FOREIGN KEY constraint failed"
+ -- </alter2-2.3>
+ })
+
+test:do_execsql_test(
+ "alter2-2.4",
+ [[
+ ALTER TABLE parent DROP CONSTRAINT fk;
+ INSERT INTO parent VALUES(3, 4, 2);
+ SELECT * FROM parent;
+ ]], {
+ -- <alter2-2.4>
+ 1, 1, 2, 3, 4, 2
+ -- </alter2-2.4>
+ })
+
+test:do_execsql_test(
+ "alter2-3.1",
+ [[
+ DROP TABLE child;
+ DROP TABLE parent;
+ CREATE TABLE child (id PRIMARY KEY, a, b);
+ CREATE TABLE parent (id PRIMARY KEY, c, d);
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent ON DELETE CASCADE MATCH FULL;
+ INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
+ INSERT INTO child VALUES(1, 1, 1), (3, 2, 2);
+ DELETE FROM parent WHERE id = 1;
+ SELECT * FROM CHILD;
+ ]], {
+ -- <alter2-3.1>
+ 3, 2, 2
+ -- </alter2-3.1>
+ })
+
+test:do_execsql_test(
+ "alter2-3.2",
+ [[
+ DROP TABLE child;
+ DROP TABLE parent;
+ CREATE TABLE child (id PRIMARY KEY, a, b);
+ CREATE TABLE parent (id PRIMARY KEY, c, d);
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent ON UPDATE CASCADE MATCH PARTIAL;
+ INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
+ INSERT INTO child VALUES(1, 1, 1), (3, 2, 2);
+ UPDATE parent SET id = 5 WHERE id = 1;
+ SELECT * FROM CHILD;
+ ]], {
+ -- <alter2-3.2>
+ 3, 2, 2, 5, 1, 1
+ -- </alter2-3.2>
+ })
+
+test:do_catchsql_test(
+ "alter2-4.1",
+ [[
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child;
+ ]], {
+ -- <alter2-4.1>
+ 1, "near \"REFERENCES\": syntax error"
+ -- </alter2-4.1>
+ })
+
+test:do_catchsql_test(
+ "alter2-4.2",
+ [[
+ ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child;
+ ]], {
+ -- <alter2-4.1>
+ 1, "near \"(\": syntax error"
+ -- </alter2-4.2>
+ })
+
+test:finish_test()
diff --git a/test/sql-tap/engine.cfg b/test/sql-tap/engine.cfg
index ce9dd68d8..006e31c37 100644
--- a/test/sql-tap/engine.cfg
+++ b/test/sql-tap/engine.cfg
@@ -2,6 +2,9 @@
"analyze9.test.lua": {
"memtx": {"engine": "memtx"}
},
+ "alter2.test.lua" : {
+ "memtx": {"engine": "memtx"}
+ },
"*": {
"memtx": {"engine": "memtx"},
"vinyl": {"engine": "vinyl"}
diff --git a/test/sql-tap/fkey1.test.lua b/test/sql-tap/fkey1.test.lua
index 494af4b4a..3c29b097d 100755
--- a/test/sql-tap/fkey1.test.lua
+++ b/test/sql-tap/fkey1.test.lua
@@ -1,13 +1,13 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(19)
+test:plan(18)
-- This file implements regression tests for foreign keys.
test:do_execsql_test(
"fkey1-1.1",
[[
- CREATE TABLE t2(x PRIMARY KEY, y TEXT);
+ CREATE TABLE t2(x PRIMARY KEY, y TEXT, UNIQUE (x, y));
]], {
-- <fkey1-1.1>
-- </fkey1-1.1>
@@ -17,10 +17,10 @@ test:do_execsql_test(
"fkey1-1.2",
[[
CREATE TABLE t1(
- a INTEGER PRIMARY KEY,
+ a PRIMARY KEY,
b INTEGER
REFERENCES t1 ON DELETE CASCADE
- REFERENCES t2,
+ REFERENCES t2 (x),
c TEXT,
FOREIGN KEY (b, c) REFERENCES t2(x, y) ON UPDATE CASCADE);
]], {
@@ -32,7 +32,7 @@ test:do_execsql_test(
"fkey1-1.3",
[[
CREATE TABLE t3(
- a INTEGER PRIMARY KEY REFERENCES t2,
+ a PRIMARY KEY REFERENCES t2,
b INTEGER REFERENCES t1,
FOREIGN KEY (a, b) REFERENCES t2(x, y));
]], {
@@ -64,13 +64,13 @@ test:do_execsql_test(
test:do_execsql_test(
"fkey1-3.1",
[[
- CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c);
+ CREATE TABLE t5(a PRIMARY KEY, b, c UNIQUE, UNIQUE(a, b));
CREATE TABLE t6(d REFERENCES t5, e PRIMARY KEY REFERENCES t5(c));
PRAGMA foreign_key_list(t6);
]], {
-- <fkey1-3.1>
- 0, 0, 'T5', 'E', 'C', 'NO ACTION', 'NO ACTION', 'NONE',
- 1, 0, 'T5', 'D', '', 'NO ACTION', 'NO ACTION', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE',
+ 1, 0, 'T5', 'E', 'C', 'no_action', 'no_action', 'NONE'
-- </fkey1-3.1>
})
@@ -81,8 +81,8 @@ test:do_execsql_test(
PRAGMA foreign_key_list(t7);
]], {
-- <fkey1-3.2>
- 0, 0, 'T5', 'D', 'A', 'NO ACTION', 'NO ACTION', 'NONE',
- 0, 1, 'T5', 'E', 'B', 'NO ACTION', 'NO ACTION', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE',
+ 0, 1, 'T5', 'E', 'B', 'no_action', 'no_action', 'NONE'
-- </fkey1-3.2>
})
@@ -91,12 +91,12 @@ test:do_execsql_test(
[[
CREATE TABLE t8(
d PRIMARY KEY, e, f,
- FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL);
+ FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON UPDATE SET NULL);
PRAGMA foreign_key_list(t8);
]], {
-- <fkey1-3.3>
- 0, 0, 'T5', 'D', '', 'SET NULL', 'CASCADE', 'NONE',
- 0, 1, 'T5', 'E', '', 'SET NULL', 'CASCADE', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'cascade', 'set_null', 'NONE',
+ 0, 1, 'T5', 'E', 'B', 'cascade', 'set_null', 'NONE'
-- </fkey1-3.3>
})
@@ -105,12 +105,12 @@ test:do_execsql_test(
[[
CREATE TABLE t9(
d PRIMARY KEY, e, f,
- FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT);
+ FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON UPDATE SET DEFAULT);
PRAGMA foreign_key_list(t9);
]], {
-- <fkey1-3.4>
- 0, 0, 'T5', 'D', '', 'SET DEFAULT', 'CASCADE', 'NONE',
- 0, 1, 'T5', 'E', '', 'SET DEFAULT', 'CASCADE', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'cascade', 'set_default', 'NONE',
+ 0, 1, 'T5', 'E', 'B', 'cascade', 'set_default', 'NONE'
-- </fkey1-3.4>
})
@@ -144,7 +144,7 @@ test:do_execsql_test(
"fkey1-5.1",
[[
CREATE TABLE t11(
- x INTEGER PRIMARY KEY,
+ x PRIMARY KEY,
parent REFERENCES t11 ON DELETE CASCADE);
INSERT INTO t11 VALUES(1, NULL), (2, 1), (3, 2);
]], {
@@ -176,7 +176,7 @@ test:do_execsql_test(
"fkey1-5.4",
[[
CREATE TABLE Foo (
- Id INTEGER PRIMARY KEY,
+ Id PRIMARY KEY,
ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE,
C1);
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A');
@@ -208,7 +208,7 @@ test:do_execsql_test(
-- </fkey1-5.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"fkey1-6.1",
[[
CREATE TABLE p1(id PRIMARY KEY, x, y);
@@ -217,23 +217,16 @@ test:do_execsql_test(
CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
]], {
-- <fkey1-6.1>
+ 1, "Failed to create foreign key constraint 'FK_CONSTRAINT_1_C1': referenced fields don't compose unique index"
-- </fkey1-6.1>
})
-test:do_catchsql_test(
- "fkey1-6.2",
- [[
- INSERT INTO c1 VALUES(1);
- ]], {
- -- <fkey1-6.2>
- 1, "foreign key mismatch - \"C1\" referencing \"P1\""
- -- </fkey1-6.2>
- })
-
test:do_execsql_test(
"fkey1-6.3",
[[
CREATE UNIQUE INDEX p1x2 ON p1(x);
+ DROP TABLE IF EXISTS c1;
+ CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
INSERT INTO c1 VALUES(1);
]], {
-- <fkey1-6.3>
diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua
index 523340f6b..310e245de 100755
--- a/test/sql-tap/fkey2.test.lua
+++ b/test/sql-tap/fkey2.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(121)
+test:plan(116)
-- This file implements regression tests for foreign keys.
@@ -14,7 +14,7 @@ test:do_execsql_test(
CREATE TABLE t4(c PRIMARY KEY REFERENCES t3, d);
CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
- CREATE TABLE t8(c PRIMARY KEY REFERENCES t7, d);
+ CREATE TABLE t8(c INTEGER PRIMARY KEY REFERENCES t7, d);
]], {
-- <fkey2-1.1>
-- </fkey2-1.1>
@@ -317,13 +317,13 @@ test:do_execsql_test(
"fkey2-2.1",
[[
CREATE TABLE i(i INTEGER PRIMARY KEY);
- CREATE TABLE j(j PRIMARY KEY REFERENCES i);
+ CREATE TABLE j(j INT PRIMARY KEY REFERENCES i);
INSERT INTO i VALUES(35);
- INSERT INTO j VALUES('35.0');
+ INSERT INTO j VALUES(35);
SELECT j, typeof(j) FROM j;
]], {
-- <fkey2-2.1>
- "35.0", "text"
+ 35, "integer"
-- </fkey2-2.1>
})
@@ -524,7 +524,7 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
- CREATE TABLE t1(a PRIMARY KEY, b);
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
]], {
-- <fkey2-5.1>
@@ -600,10 +600,10 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(
c INTEGER PRIMARY KEY,
- d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT);
+ d DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT);
DELETE FROM t1;
]], {
-- <fkey2-6.1>
@@ -714,24 +714,20 @@ test:do_catchsql_test(
[[
CREATE TABLE p(a PRIMARY KEY, b);
CREATE TABLE c(x PRIMARY KEY REFERENCES p(c));
- INSERT INTO c DEFAULT VALUES;
]], {
-- <fkey2-7.1>
- 1, "foreign key mismatch - \"C\" referencing \"P\""
+ 1, "table \"P\" doesn't feature column C"
-- </fkey2-7.1>
})
test:do_catchsql_test(
"fkey2-7.2",
[[
- DROP TABLE IF EXISTS c;
- DROP TABLE IF EXISTS p;
- CREATE VIEW v AS SELECT x AS y FROM c;
+ CREATE VIEW v AS SELECT b AS y FROM p;
CREATE TABLE c(x PRIMARY KEY REFERENCES v(y));
- INSERT INTO c DEFAULT VALUES;
]], {
-- <fkey2-7.2>
- 1, "no such table: C"
+ 1, "referenced table can't be view"
-- </fkey2-7.2>
})
@@ -740,13 +736,13 @@ test:do_catchsql_test(
[[
DROP VIEW v;
DROP TABLE IF EXISTS c;
- CREATE TABLE p(a COLLATE binary, b PRIMARY KEY);
- CREATE UNIQUE INDEX idx ON p(a COLLATE "unicode_ci");
+ DROP TABLE IF EXISTS p;
+ CREATE TABLE p(a COLLATE "unicode_ci", b PRIMARY KEY);
+ CREATE UNIQUE INDEX idx ON p(a);
CREATE TABLE c(x PRIMARY KEY REFERENCES p(a));
- INSERT INTO c DEFAULT VALUES;
]], {
-- <fkey2-7.3>
- 1, "no such view: V"
+ 1, "Failed to create foreign key constraint 'FK_CONSTRAINT_1_C': field collation mismatch"
-- </fkey2-7.3>
})
@@ -757,10 +753,9 @@ test:do_catchsql_test(
DROP TABLE IF EXISTS p;
CREATE TABLE p(a, b, PRIMARY KEY(a, b));
CREATE TABLE c(x PRIMARY KEY REFERENCES p);
- INSERT INTO c DEFAULT VALUES;
]], {
-- <fkey2-7.4>
- 1, "foreign key mismatch - \"C\" referencing \"P\""
+ 1, "Failed to create foreign key constraint 'fk_constraint_1_C': number of columns in foreign key does not match the number of columns in the referenced table"
-- </fkey2-7.4>
})
@@ -771,7 +766,7 @@ test:do_execsql_test(
"fkey2-8.1",
[[
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
- CREATE TABLE t2(c PRIMARY KEY, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
INSERT INTO t1 VALUES(10, 100);
INSERT INTO t2 VALUES(10, 100);
@@ -794,8 +789,7 @@ test:do_execsql_test(
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a, b PRIMARY KEY);
CREATE TABLE t2(
- x PRIMARY KEY REFERENCES t1
- ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED);
+ x PRIMARY KEY REFERENCES t1 ON UPDATE RESTRICT);
INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t1 VALUES(2, 'two');
INSERT INTO t1 VALUES(3, 'three');
@@ -847,7 +841,7 @@ test:do_execsql_test(
BEGIN
INSERT INTO t1 VALUES(old.x);
END;
- CREATE TABLE t2(y PRIMARY KEY REFERENCES t1);
+ CREATE TABLE t2(y COLLATE "unicode_ci" PRIMARY KEY REFERENCES t1);
INSERT INTO t1 VALUES('A');
INSERT INTO t1 VALUES('B');
INSERT INTO t2 VALUES('A');
@@ -875,7 +869,7 @@ test:do_execsql_test(
"fkey2-9.7",
[[
DROP TABLE t2;
- CREATE TABLE t2(y PRIMARY KEY REFERENCES t1 ON DELETE RESTRICT);
+ CREATE TABLE t2(y COLLATE "unicode_ci" PRIMARY KEY REFERENCES t1 ON DELETE RESTRICT);
INSERT INTO t2 VALUES('A');
INSERT INTO t2 VALUES('B');
]], {
@@ -1053,7 +1047,7 @@ test:do_catchsql_test(
CREATE TABLE t1(a PRIMARY KEY, b REFERENCES nosuchtable);
]], {
-- <fkey2-10.6>
- 1, "foreign key constraint references nonexistent table: NOSUCHTABLE"
+ 1, "Space 'NOSUCHTABLE' does not exist"
-- </fkey2-10.6>
})
@@ -1083,7 +1077,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-10.9",
[[
- DELETE FROM t2;
+ DROP TABLE t2;
DROP TABLE t1;
]], {
-- <fkey2-10.9>
@@ -1091,47 +1085,6 @@ test:do_execsql_test(
})
test:do_catchsql_test(
- "fkey2-10.10",
- [[
- INSERT INTO t2 VALUES('x');
- ]], {
- -- <fkey2-10.10>
- 1, "no such table: T1"
- -- </fkey2-10.10>
- })
-
-test:do_execsql_test(
- "fkey2-10.11",
- [[
- CREATE TABLE t1(x PRIMARY KEY);
- INSERT INTO t1 VALUES('x');
- INSERT INTO t2 VALUES('x');
- ]], {
- -- <fkey2-10.11>
- -- </fkey2-10.11>
- })
-
-test:do_catchsql_test(
- "fkey2-10.12",
- [[
- DROP TABLE t1;
- ]], {
- -- <fkey2-10.12>
- 1, "FOREIGN KEY constraint failed"
- -- </fkey2-10.12>
- })
-
-test:do_execsql_test(
- "fkey2-10.13",
- [[
- DROP TABLE t2;
- DROP TABLE t1;
- ]], {
- -- <fkey2-10.13>
- -- </fkey2-10.13>
- })
-
-test:do_execsql_test(
"fkey2-10.14",
[[
DROP TABLE IF EXISTS cc;
@@ -1140,23 +1093,13 @@ test:do_execsql_test(
CREATE TABLE cc(a PRIMARY KEY, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
]], {
-- <fkey2-10.14>
+ 1, "table \"PP\" doesn't feature column Z"
-- </fkey2-10.14>
})
-test:do_catchsql_test(
- "fkey2-10.15",
- [[
- INSERT INTO cc VALUES(1, 2);
- ]], {
- -- <fkey2-10.15>
- 1, "foreign key mismatch - \"CC\" referencing \"PP\""
- -- </fkey2-10.15>
- })
-
test:do_execsql_test(
"fkey2-10.16",
[[
- DROP TABLE cc;
CREATE TABLE cc(
a PRIMARY KEY, b,
FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
@@ -1181,7 +1124,7 @@ test:do_execsql_test(
-- </fkey2-10.17>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"fkey2-10.18",
[[
CREATE TABLE b1(a PRIMARY KEY, b);
@@ -1193,7 +1136,7 @@ test:do_execsql_test(
-- </fkey2-10.18>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"fkey2-10.19",
[[
CREATE TABLE b3(a PRIMARY KEY, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
@@ -1204,15 +1147,15 @@ test:do_execsql_test(
-- </fkey2-10.19>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"fkey2-10.20",
[[
DROP VIEW IF EXISTS v;
- CREATE VIEW v AS SELECT * FROM t1;
+ CREATE VIEW v AS SELECT * FROM b1;
CREATE TABLE t1(x PRIMARY KEY REFERENCES v);
- DROP VIEW v;
]], {
-- <fkey2-10.20>
+ 1, "referenced table can't be view"
-- </fkey2-10.20>
})
@@ -1224,7 +1167,7 @@ test:do_execsql_test(
test:do_execsql_test(
"fkey2-11.1",
[[
- CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a));
+ CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a));
INSERT INTO self VALUES(13, 13);
UPDATE self SET a = 14, b = 14;
]], {
@@ -1294,7 +1237,7 @@ test:do_execsql_test(
"fkey2-11.8",
[[
DROP TABLE IF EXISTS self;
- CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a));
+ CREATE TABLE self(a UNIQUE, b PRIMARY KEY REFERENCES self(a));
INSERT INTO self VALUES(13, 13);
UPDATE self SET a = 14, b = 14;
]], {
@@ -1366,7 +1309,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-12.1",
[[
- CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE tdd08(a PRIMARY KEY, b);
CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
INSERT INTO tdd08 VALUES(200,300);
@@ -1430,7 +1373,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-13.1",
[[
- CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE tce71(a PRIMARY KEY, b);
CREATE UNIQUE INDEX ice71 ON tce71(a,b);
INSERT INTO tce71 VALUES(100,200);
CREATE TABLE tce72(w PRIMARY KEY, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
@@ -1466,9 +1409,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-14.1",
[[
- CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
+ CREATE TABLE tce73(a PRIMARY KEY, b, UNIQUE(a,b));
INSERT INTO tce73 VALUES(100,200);
- CREATE TABLE tce74(w INTEGER PRIMARY KEY, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
+ CREATE TABLE tce74(w PRIMARY KEY, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
INSERT INTO tce74 VALUES(300,100,200);
UPDATE tce73 set b = 200 where a = 100;
SELECT * FROM tce73, tce74;
diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua
index d7055b096..84997dd35 100755
--- a/test/sql-tap/fkey3.test.lua
+++ b/test/sql-tap/fkey3.test.lua
@@ -158,9 +158,8 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey3-3.6",
[[
- CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d,
+ CREATE TABLE t6(a PRIMARY KEY, b, c, d, UNIQUE (a, b),
FOREIGN KEY(c, d) REFERENCES t6(a, b));
- CREATE UNIQUE INDEX t6i ON t6(b, a);
INSERT INTO t6 VALUES(1, 'a', 1, 'a');
INSERT INTO t6 VALUES(2, 'a', 2, 'a');
INSERT INTO t6 VALUES(3, 'a', 1, 'a');
@@ -206,9 +205,8 @@ test:do_execsql_test(
test:do_execsql_test(
"fkey3-3.10",
[[
- CREATE TABLE t7(a, b, c, d INTEGER PRIMARY KEY,
+ CREATE TABLE t7(a, b, c, d PRIMARY KEY, UNIQUE(a, b),
FOREIGN KEY(c, d) REFERENCES t7(a, b));
- CREATE UNIQUE INDEX t7i ON t7(a, b);
INSERT INTO t7 VALUES('x', 1, 'x', 1);
INSERT INTO t7 VALUES('x', 2, 'x', 2);
]], {
@@ -239,9 +237,10 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey3-6.1",
[[
- CREATE TABLE t8(a PRIMARY KEY, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b));
+ CREATE TABLE t8(a PRIMARY KEY, b, c, d, e);
CREATE UNIQUE INDEX t8i1 ON t8(a, b);
CREATE UNIQUE INDEX t8i2 ON t8(c);
+ ALTER TABLE t8 ADD CONSTRAINT fk1 FOREIGN KEY (c, d) REFERENCES t8(a, b);
INSERT INTO t8 VALUES(1, 1, 1, 1, 1);
]], {
-- <fkey3-6.1>
@@ -272,12 +271,12 @@ test:do_catchsql_test(
"fkey3-6.4",
[[
CREATE TABLE TestTable (
- id INTEGER PRIMARY KEY,
+ id PRIMARY KEY,
name TEXT,
source_id INTEGER NOT NULL,
- parent_id INTEGER,
- FOREIGN KEY(source_id, parent_id) REFERENCES TestTable(source_id, id));
+ parent_id INTEGER);
CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
+ ALTER TABLE TestTable ADD CONSTRAINT fk1 FOREIGN KEY (source_id, parent_id) REFERENCES TestTable(source_id, id);
INSERT INTO TestTable VALUES (1, 'parent', 1, null);
INSERT INTO TestTable VALUES (2, 'child', 1, 1);
UPDATE TestTable SET parent_id=1000 WHERE id=2;
diff --git a/test/sql-tap/orderby1.test.lua b/test/sql-tap/orderby1.test.lua
index e0ea3698d..1cc104bfc 100755
--- a/test/sql-tap/orderby1.test.lua
+++ b/test/sql-tap/orderby1.test.lua
@@ -29,7 +29,7 @@ test:do_test(
function()
return test:execsql [[
CREATE TABLE album(
- aid INTEGER PRIMARY KEY,
+ aid PRIMARY KEY,
title TEXT UNIQUE NOT NULL
);
CREATE TABLE track(
@@ -417,7 +417,7 @@ test:do_test(
DROP TABLE track;
DROP TABLE album;
CREATE TABLE album(
- aid INTEGER PRIMARY KEY,
+ aid PRIMARY KEY,
title TEXT UNIQUE NOT NULL
);
CREATE TABLE track(
@@ -664,7 +664,7 @@ test:do_test(
4.0,
function()
return test:execsql [[
- CREATE TABLE t41(a INTEGER PRIMARY KEY, b INT NOT NULL);
+ CREATE TABLE t41(a PRIMARY KEY, b INT NOT NULL);
CREATE INDEX t41ba ON t41(b,a);
CREATE TABLE t42(id INTEGER PRIMARY KEY, x INT NOT NULL REFERENCES t41(a), y INT NOT NULL);
CREATE UNIQUE INDEX t42xy ON t42(x,y);
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index 3f8182fc4..90cf65161 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -731,7 +731,7 @@ test:do_catchsql_test(
[[
DROP TABLE t6;
CREATE TABLE t4(a INT PRIMARY KEY);
- CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL primary key);
+ CREATE TABLE t6(a INTEGER REFERENCES t4(a) MATCH PARTIAL primary key);
]], {
-- <table-10.2>
0
@@ -742,7 +742,7 @@ test:do_catchsql_test(
"table-10.3",
[[
DROP TABLE t6;
- CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL primary key);
+ CREATE TABLE t6(a INTEGER REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL primary key);
]], {
-- <table-10.3>
0
@@ -753,7 +753,7 @@ test:do_catchsql_test(
"table-10.4",
[[
DROP TABLE t6;
- CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1 primary key);
+ CREATE TABLE t6(a INT REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1 primary key);
]], {
-- <table-10.4>
0
@@ -791,14 +791,16 @@ test:do_catchsql_test(
);
]], {
-- <table-10.7>
- 0
+ 1, "table \"T4\" doesn't feature column B"
-- </table-10.7>
})
test:do_catchsql_test(
"table-10.8",
[[
- DROP TABLE t6;
+ DROP TABLE IF EXISTS t6;
+ DROP TABLE IF EXISTS t4;
+ CREATE TABLE t4(x UNIQUE, y, PRIMARY KEY (x, y));
CREATE TABLE t6(a primary key,b,c,
FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
@@ -818,7 +820,7 @@ test:do_catchsql_test(
);
]], {
-- <table-10.9>
- 1, "number of columns in foreign key does not match the number of columns in the referenced table"
+ 1, "Failed to create foreign key constraint 'fk_constraint_1_T6': number of columns in foreign key does not match the number of columns in the referenced table"
-- </table-10.9>
})
@@ -833,7 +835,7 @@ test:do_test(
]]
end, {
-- <table-10.10>
- 1, "number of columns in foreign key does not match the number of columns in the referenced table"
+ 1, "Failed to create foreign key constraint 'fk_constraint_1_T6': number of columns in foreign key does not match the number of columns in the referenced table"
-- </table-10.10>
})
@@ -846,7 +848,7 @@ test:do_test(
]]
end, {
-- <table-10.11>
- 1, "foreign key on C should reference only one column of table t4"
+ 1, "Failed to create foreign key constraint 'fk_constraint_1_T6': number of columns in foreign key does not match the number of columns in the referenced table"
-- </table-10.11>
})
@@ -861,7 +863,7 @@ test:do_test(
]]
end, {
-- <table-10.12>
- 1, [[unknown column "X" in foreign key definition]]
+ 1, [[unknown column X in foreign key definition]]
-- </table-10.12>
})
@@ -876,7 +878,7 @@ test:do_test(
]]
end, {
-- <table-10.13>
- 1, [[unknown column "X" in foreign key definition]]
+ 1, [[unknown column X in foreign key definition]]
-- </table-10.13>
})
diff --git a/test/sql-tap/tkt-b1d3a2e531.test.lua b/test/sql-tap/tkt-b1d3a2e531.test.lua
index e140cf82a..85b0f46d7 100755
--- a/test/sql-tap/tkt-b1d3a2e531.test.lua
+++ b/test/sql-tap/tkt-b1d3a2e531.test.lua
@@ -65,7 +65,7 @@ test:do_execsql_test(
test:do_execsql_test(
2.1,
[[
- CREATE TABLE pp(x PRIMARY KEY);
+ CREATE TABLE pp(x INTEGER PRIMARY KEY);
CREATE TABLE cc(
y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY DEFERRED
);
@@ -83,7 +83,7 @@ test:do_execsql_test(
test:do_execsql_test(
2.3,
[[
- CREATE TABLE pp(x PRIMARY KEY);
+ CREATE TABLE pp(x INTEGER PRIMARY KEY);
CREATE TABLE cc(
y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY DEFERRED
);
diff --git a/test/sql-tap/triggerC.test.lua b/test/sql-tap/triggerC.test.lua
index e58072e2f..d1fc82842 100755
--- a/test/sql-tap/triggerC.test.lua
+++ b/test/sql-tap/triggerC.test.lua
@@ -1150,7 +1150,7 @@ test:do_execsql_test(
PRAGMA foreign_keys='false';
PRAGMA recursive_triggers = 1;
CREATE TABLE node(
- id int not null primary key,
+ id not null primary key,
pid int not null default 0 references node,
key varchar not null,
path varchar default '',
diff --git a/test/sql-tap/whereG.test.lua b/test/sql-tap/whereG.test.lua
index 13cef16c8..ded983975 100755
--- a/test/sql-tap/whereG.test.lua
+++ b/test/sql-tap/whereG.test.lua
@@ -23,11 +23,11 @@ test:do_execsql_test(
"whereG-1.0",
[[
CREATE TABLE composer(
- cid INTEGER PRIMARY KEY,
+ cid PRIMARY KEY,
cname TEXT
);
CREATE TABLE album(
- aid INTEGER PRIMARY KEY,
+ aid PRIMARY KEY,
aname TEXT
);
CREATE TABLE track(
diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 6db8d130c..c6a895875 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -397,7 +397,7 @@ test:do_catchsql_test("5.6.7", [[
--
test:do_execsql_test(6.1, [[
CREATE TABLE f(
- id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
+ id PRIMARY KEY, parentid REFERENCES f, name TEXT
);
INSERT INTO f VALUES(0, NULL, '');
--
2.15.1
More information about the Tarantool-patches
mailing list