[tarantool-patches] [PATCH 3/5] sql: introduce ADD CONSTRAINT statement
Nikita Pettik
korablev at tarantool.org
Fri Jul 13 05:04:19 MSK 2018
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
<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 refrencing 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 +
src/box/fkey.h | 6 +
src/box/sql.c | 16 +
src/box/sql/alter.c | 7 -
src/box/sql/build.c | 581 +++++++++++++++------
src/box/sql/callback.c | 2 -
src/box/sql/delete.c | 6 +-
src/box/sql/expr.c | 10 +-
src/box/sql/fkey.c | 974 +++++++++++------------------------
src/box/sql/insert.c | 19 +-
src/box/sql/main.c | 5 -
src/box/sql/parse.y | 37 +-
src/box/sql/pragma.c | 240 +--------
src/box/sql/pragma.h | 7 -
src/box/sql/prepare.c | 5 +
src/box/sql/sqliteInt.h | 167 +++---
src/box/sql/status.c | 5 +-
src/box/sql/tarantoolInt.h | 12 +
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 | 196 +++++++
test/sql-tap/fkey1.test.lua | 51 +-
test/sql-tap/fkey2.test.lua | 131 ++---
test/sql-tap/fkey3.test.lua | 19 +-
test/sql-tap/fkey4.test.lua | 2 +-
test/sql-tap/orderby1.test.lua | 6 +-
test/sql-tap/table.test.lua | 18 +-
test/sql-tap/tkt-b1d3a2e531.test.lua | 6 +-
test/sql-tap/triggerC.test.lua | 2 +-
test/sql-tap/whereG.test.lua | 4 +-
test/sql-tap/with1.test.lua | 2 +-
32 files changed, 1226 insertions(+), 1337 deletions(-)
create mode 100755 test/sql-tap/alter2.test.lua
diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 990c4199f..6ba872acc 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 },
{ "TABLE", "TK_TABLE", ALWAYS, true },
{ "THEN", "TK_THEN", ALWAYS, true },
{ "TO", "TK_TO", ALWAYS, true },
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;
+}
+
/**
* The second argument is a Trigger structure allocated by the
* fkActionTrigger() routine.This function deletes the Trigger
diff --git a/src/box/sql.c b/src/box/sql.c
index e0c2c924c..21104358b 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;
@@ -1531,6 +1532,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 fe54e5531..e81113f58 100644
--- a/src/box/sql/alter.c
+++ b/src/box/sql/alter.c
@@ -150,7 +150,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)
@@ -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;
- }
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
@@ -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"
@@ -373,9 +374,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);
@@ -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)
+{
+ 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);
+ return;
+ }
+ 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;
+}
+
/*
* 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) {
+ 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");
+ 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;
+ }
}
/* 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)
+{
+ 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,
@@ -2142,6 +2288,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. */
+ for (struct fkey *child_fk = space->child_fkey; child_fk != NULL;
+ child_fk = child_fk->fkey_child_next) {
+ const char *fk_name_dup = sqlite3DbStrDup(v->db,
+ child_fk->def->name);
+ if (fk_name_dup == NULL)
+ return;
+ vdbe_fkey_code_drop(parse_context, fk_name_dup, space_id);
+ }
/*
* Drop all _space and _index entries that refer to the
* table.
@@ -2256,12 +2411,16 @@ 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 && strcmp(fk->pFrom->def->name, tab->def->name) != 0) {
- sqlite3ErrorMsg(parse_context, "can't drop parent table %s when "
- "child table refers to it", space_name);
- goto exit_drop_table;
+ if (space_fkey_check_references(space)) {
+ for (struct fkey *fk = space->parent_fkey; fk != NULL;
+ fk = fk->fkey_parent_next) {
+ if (fkey_is_self_referenced(fk->def))
+ continue;
+ sqlite3ErrorMsg(parse_context, "can't drop table %s: "
+ "other objects depend on it",
+ space->def->name);
+ goto exit_drop_table;
+ }
}
sql_clear_stat_spaces(parse_context, space_name, NULL);
sql_code_drop_table(parse_context, space, is_view);
@@ -2270,176 +2429,276 @@ 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.
*/
-void
-sqlite3CreateForeignKey(Parse * pParse, /* Parsing context */
- ExprList * pFromCol, /* Columns in this table that point to other table */
- Token * pTo, /* Name of the other table */
- ExprList * pToCol, /* Columns in the other table */
- int flags /* Conflict resolution algorithms. */
- )
+static int
+columnno_by_name(struct Parse *parse_context, const struct space *space,
+ const char *column_name, uint32_t *colno)
{
- 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 *normilized_name = strndup(pTo->z, pTo->n);
- if (normilized_name == NULL) {
- diag_set(OutOfMemory, pTo->n, "strndup", "normalized name");
- goto fk_end;
- }
- sqlite3NormalizeName(normilized_name);
- uint32_t parent_id = box_space_id_by_name(normilized_name,
- strlen(normilized_name));
- if (parent_id == BOX_ID_NIL &&
- strcmp(normilized_name, p->def->name) != 0) {
- sqlite3ErrorMsg(pParse, "foreign key constraint references "\
- "nonexistent table: %s", normilized_name);
- 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", normilized_name);
- goto fk_end;
+ assert(colno != NULL);
+ uint32_t column_len = strlen(column_name);
+ if (tuple_fieldno_by_name(space->def->dict, column_name, column_len,
+ field_name_hash(column_name, column_len),
+ colno) != 0) {
+ sqlite3ErrorMsg(parse_context,
+ "table \"%s\" doesn't feature column %s",
+ space->def->name, column_name);
+ return -1;
}
- if (p == 0)
- goto fk_end;
- if (pFromCol == 0) {
- int iCol = p->def->field_count - 1;
- if (NEVER(iCol < 0))
- goto fk_end;
- if (pToCol && pToCol->nExpr != 1) {
- sqlite3ErrorMsg(pParse, "foreign key on %s"
- " should reference only one column of table %T",
- p->def->fields[iCol].name, pTo);
- goto fk_end;
- }
- 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;
+ return 0;
+}
+
+void
+sql_create_foreign_key(struct Parse *parse_context, struct SrcList *child,
+ struct Token *constraint, struct ExprList *child_cols,
+ struct Token *parent, struct ExprList *parent_cols,
+ bool is_deferred, int actions)
+{
+ struct sqlite3 *db = parse_context->db;
+ /*
+ * When this function is called second time during
+ * <CREATE TABLE ...> statement (i.e. at VDBE runtime),
+ * don't even try to do something.
+ */
+ if (db->init.busy)
+ return;
+ /*
+ * Beforehand initialization for correct clean-up
+ * while emergency exiting in case of error.
+ */
+ const char *parent_name = NULL;
+ const char *constraint_name = NULL;
+ bool is_self_referenced = false;
+ /*
+ * Table under construction during CREATE TABLE
+ * processing. NULL for ALTER TABLE statement handling.
+ */
+ struct Table *new_tab = parse_context->pNewTable;
+ /* Whether we are processing ALTER TABLE or CREATE TABLE. */
+ bool is_alter = new_tab == NULL;
+ uint32_t child_cols_count;
+ if (child_cols == NULL) {
+ if (is_alter) {
+ sqlite3ErrorMsg(parse_context,
+ "referencing columns are not specified");
+ goto exit_create_fk;
+ }
+ child_cols_count = 1;
} else {
- nCol = pFromCol->nExpr;
- }
- nByte = sizeof(*pFKey) + (nCol - 1) * sizeof(pFKey->aCol[0]) +
- strlen(normilized_name) + 1;
- if (pToCol) {
- for (i = 0; i < pToCol->nExpr; i++) {
- nByte += sqlite3Strlen30(pToCol->a[i].zName) + 1;
- }
- }
- 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, normilized_name, strlen(normilized_name) + 1);
- z += strlen(normilized_name) + 1;
- pFKey->nCol = nCol;
- if (pFromCol == 0) {
- pFKey->aCol[0].iFrom = p->def->field_count - 1;
+ 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;
+ }
+ child_space = space_by_id(child_id);
+ assert(child_space != NULL);
} 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;
- }
+ struct fkey_parse *fk = region_alloc(&parse_context->region,
+ sizeof(*fk));
+ if (fk == NULL) {
+ diag_set(OutOfMemory, sizeof(*fk), "region",
+ "struct fkey_parse");
+ parse_context->rc = SQL_TARANTOOL_ERROR;
+ parse_context->nErr++;
+ 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;
+ memset(fk, 0, sizeof(*fk));
+ struct fkey_parse *last_fk = parse_context->new_fkey;
+ parse_context->new_fkey = fk;
+ fk->next = last_fk;
+ }
+ 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 ? false :
+ !strcmp(parent_name, new_tab->def->name);
+ if (parent_id == BOX_ID_NIL) {
+ if (is_self_referenced) {
+ parse_context->new_fkey->selfref_cols = parent_cols;
+ parse_context->new_fkey->is_self_referenced = true;
+ } else {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, parent_name);;
+ 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;
- }
- if (pNextTo) {
- assert(pNextTo->pPrevTo == 0);
- pFKey->pNextTo = pNextTo;
- pNextTo->pPrevTo = pFKey;
+ struct space *parent_space = space_by_id(parent_id);
+ if (parent_space != NULL && parent_space->def->opts.is_view) {
+ sqlite3ErrorMsg(parse_context,
+ "referenced table can't be view");
+ goto exit_create_fk;
+ }
+ 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;
+ }
}
-
- /* 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++);
+ constraint_name =
+ sqlite3MPrintf(db, "fk_constraint_%d_%s",
+ fk_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;
+ 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;
+ }
+ 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;
+ }
+ 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)
+ parse_context->new_fkey->fkey = fk;
+ else
+ vdbe_fkey_code_creation(parse_context, fk);
+
+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;
+}
- fk_end:
- sqlite3DbFree(db, pFKey);
- free(normilized_name);
-#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
- sql_expr_list_delete(db, pFromCol);
- sql_expr_list_delete(db, pToCol);
+void
+fkey_change_defer_mode(struct Parse *parse_context, bool is_deferred)
+{
+ if (parse_context->db->init.busy || parse_context->new_fkey == NULL)
+ return;
+ struct fkey_def *fk = parse_context->new_fkey->fkey;
+ fk->is_deferred = is_deferred;
}
-/*
- * 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)
+sql_drop_foreign_key(struct Parse *parse_context, struct SrcList *table,
+ struct Token *constraint)
{
-#ifndef SQLITE_OMIT_FOREIGN_KEY
- Table *pTab;
- FKey *pFKey;
- if ((pTab = pParse->pNewTable) == 0 || (pFKey = pTab->pFKey) == 0)
+ assert(table != NULL && table->nSrc == 1);
+ struct sqlite3 *db = parse_context->db;
+ const char *constraint_name = sqlite3NameFromToken(db, constraint);
+ if (constraint_name == NULL)
return;
- assert(isDeferred == 0 || isDeferred == 1); /* EV: R-30323-21917 */
- pFKey->isDeferred = (u8) isDeferred;
-#endif
+ 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_fkey_code_drop(parse_context, constraint_name, child_id);
}
/*
diff --git a/src/box/sql/callback.c b/src/box/sql/callback.c
index 01e8dd8f1..164ab74a6 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;
}
@@ -309,7 +308,6 @@ sqlite3SchemaCreate(sqlite3 * db)
sqlite3OomFault(db);
} else {
sqlite3HashInit(&p->tblHash);
- sqlite3HashInit(&p->fkeyHash);
}
return p;
}
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 5a799714d..9aa4322a3 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -126,7 +126,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);
@@ -452,14 +452,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/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)
+ 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
/*
* Deferred and Immediate FKs
@@ -137,8 +138,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 +147,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,198 +164,21 @@
* 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.
- *
- * 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.
- *
- * If the required index cannot be found, either because:
- *
- * 1) The named parent key columns do not exist, or
- *
- * 2) The named parent key columns do exist, but are not subject to a
- * UNIQUE or PRIMARY KEY constraint, or
- *
- * 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
- *
- * 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.
- *
- * 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.
- */
-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 */
- )
-{
- Index *pIdx = 0; /* Value to return via *ppIdx */
- 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.
- *
- * 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.
- */
- if (nCol == 1) {
- /* The FK maps to the IPK if any of the following are true:
- *
- * 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly
- * mapped to the primary key of table pParent, or
- * 2) The FK is explicitly mapped to a column declared as INTEGER
- * PRIMARY KEY.
- */
- if (pParent->iPKey >= 0) {
- if (!zKey)
- return 0;
- if (!strcmp(pParent->def->fields[pParent->iPKey].name,
- zKey))
- return 0;
- }
- } else if (paiCol) {
- assert(nCol > 1);
- aiCol =
- (int *)sqlite3DbMallocRawNN(pParse->db, nCol * sizeof(int));
- if (!aiCol)
- return 1;
- *paiCol = aiCol;
- }
-
- for (pIdx = pParent->pIndex; pIdx; pIdx = pIdx->pNext) {
- int nIdxCol = index_column_count(pIdx);
- if (nIdxCol == nCol && index_is_unique(pIdx)
- && pIdx->pPartIdxWhere == 0) {
- /* pIdx 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 == 0) {
- /* 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(pIdx)) {
- if (aiCol) {
- int i;
- for (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;
- for (i = 0; i < nCol; i++) {
- i16 iCol = pIdx->aiColumn[i]; /* Index of column in parent tbl */
- char *zIdxCol; /* Name of indexed column */
-
- if (iCol < 0)
- break; /* No foreign keys against expression indexes */
-
- /* 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.
- */
- struct coll *def_coll;
- uint32_t id;
- def_coll = sql_column_collation(pParent->def,
- iCol,
- &id);
- struct coll *coll =
- sql_index_collation(pIdx, i,
- &id);
- if (def_coll != coll)
- break;
-
- zIdxCol =
- pParent->def->fields[iCol].name;
- for (j = 0; j < nCol; j++) {
- if (strcmp
- (pFKey->aCol[j].zCol,
- zIdxCol) == 0) {
- if (aiCol)
- aiCol[i] =
- pFKey->
- aCol[j].
- iFrom;
- break;
- }
- }
- if (j == nCol)
- break;
- }
- if (i == nCol)
- break; /* pIdx is usable */
- }
- }
- }
- if (!pIdx) {
- sqlite3ErrorMsg(pParse, "foreign key mismatch - "\
- "\"%w\" referencing \"%w\"",
- pFKey->pFrom->def->name, pFKey->zTo);
- sqlite3DbFree(pParse->db, aiCol);
- return 1;
- }
-
- *ppIdx = pIdx;
- 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:
+/**
+ * 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.
+ *
+ * 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".
@@ -366,150 +187,116 @@ sqlite3FkLocateIndex(Parse * pParse, /* Parse context to store any error in */
*
* 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".
+ * These operations are identified in the comment at the top of
+ * this file (fkey.c) as "I.1" and "D.1".
+ *
+ * @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.
+ * @param is_ignore If true, pretend parent contains all NULLs.
*/
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 */
- )
+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)
{
- 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.
+ 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.
*
- * 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.
+ * 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 (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);
- }
+ if (incr_count < 0)
+ sqlite3VdbeAddOp2(v, OP_FkIfZero, fk_def->is_deferred,
+ ok_label);
- } else {
- int nCol = pFKey->nCol;
- int regTemp = sqlite3GetTempRange(pParse, nCol);
- int regRec = sqlite3GetTempReg(pParse);
- struct space *space =
- space_by_id(SQLITE_PAGENO_TO_SPACEID(pIdx->tnum));
- vdbe_emit_open_cursor(pParse, iCur, pIdx->tnum, 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;
- for (i = 0; i < nCol; i++) {
- int iChild = aiCol[i] + 1 + regData;
- int iParent =
- pIdx->aiColumn[i] + 1 + regData;
- assert(pIdx->aiColumn[i] >= 0);
- assert(aiCol[i] != pTab->iPKey);
- if (pIdx->aiColumn[i] == pTab->iPKey) {
- /* The parent key is a composite key that includes the IPK column */
- iParent = regData;
- }
- sqlite3VdbeAddOp3(v, OP_Ne, iChild,
- iJump, iParent);
- VdbeCoverage(v);
- sqlite3VdbeChangeP5(v,
- SQLITE_JUMPIFNULL);
- }
- sqlite3VdbeGoto(v, iOk);
+ for (uint32_t i = 0; i < fk_def->field_count; i++) {
+ int iReg = fk_def->links[i].child_field + reg_data + 1;
+ sqlite3VdbeAddOp2(v, OP_IsNull, iReg, ok_label);
+ }
+ 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);
+ 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) {
+ 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) &&
+ !parse_context->pToplevel && !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);
}
/*
@@ -604,31 +391,26 @@ 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 */
+ struct fkey_def *fkey, /* The foreign key linking pSrc to pTab */
int regData, /* Parent row data starts here */
int nIncr /* Amount to increment deferred counter by */
)
{
sqlite3 *db = pParse->db; /* Database handle */
- int i; /* Iterator variable */
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 == 0 || pIdx->pTable == pTab);
- assert(pIdx == 0 || (int)index_column_count(pIdx) == pFKey->nCol);
- assert(pIdx != 0);
-
if (nIncr < 0) {
iFkIfZero =
- sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
+ sqlite3VdbeAddOp2(v, OP_FkIfZero, fkey->is_deferred, 0);
VdbeCoverage(v);
}
+ 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> ...
@@ -637,18 +419,18 @@ fkScanChildren(Parse * pParse, /* Parse context */
* 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 (i = 0; i < pFKey->nCol; i++) {
+ for (uint32_t i = 0; i < fkey->field_count; 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 *zCol; /* Name of column in child table */
- iCol = pIdx ? pIdx->aiColumn[i] : -1;
+ iCol = fkey->links[i].parent_field;
pLeft = exprTableRegister(pParse, pTab, regData, iCol);
- iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
+ iCol = fkey->links[i].child_field;
assert(iCol >= 0);
- zCol = pFKey->pFrom->def->fields[iCol].name;
+ zCol = child_space->def->fields[iCol].name;
pRight = sqlite3Expr(db, TK_ID, zCol);
pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
pWhere = sqlite3ExprAnd(db, pWhere, pEq);
@@ -661,22 +443,18 @@ fkScanChildren(Parse * pParse, /* Parse context */
* NOT( $current_a==a AND $current_b==b AND ... )
* The primary key is (a,b,...)
*/
- if (pTab == pFKey->pFrom && nIncr > 0) {
+ if (pTab->def->id == fkey->child_id && 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 != 0);
- int col_count = index_column_count(pPk);
- for (i = 0; i < col_count; i++) {
- i16 iCol = pIdx->aiColumn[i];
+ for (uint32_t i = 0; i < fkey->field_count; i++) {
+ i16 iCol = fkey->links[i].parent_field;
assert(iCol >= 0);
pLeft = exprTableRegister(pParse, pTab, regData, iCol);
- pRight =
- exprTableColumn(db, pTab->def,
- pSrc->a[0].iCursor, iCol);
+ pRight = exprTableColumn(db, pTab->def,
+ pSrc->a[0].iCursor, iCol);
pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
pAll = sqlite3ExprAnd(db, pAll, pEq);
}
@@ -695,7 +473,7 @@ fkScanChildren(Parse * pParse, /* Parse context */
* foreign key constraint counter.
*/
pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
- sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
+ sqlite3VdbeAddOp2(v, OP_FkCounter, fkey->is_deferred, nIncr);
if (pWInfo) {
sqlite3WhereEnd(pWInfo);
}
@@ -706,103 +484,68 @@ fkScanChildren(Parse * pParse, /* Parse context */
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);
-}
-
-/*
- * 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.
+/**
+ * The second argument points to an fkey object representing
+ * a foreign key. An UPDATE statement against child table is
+ * currently being processed. For each column of the table that
+ * is actually updated, the corresponding element in the 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).
+/**
+ * Works the same as fkey_child_is_modified(), but checks are
+ * provided on parent table.
*
- * This function returns true if any of the columns that are part of the
- * parent key for FK constraint *p are modified.
+ * @param fkey FK constraint definition.
+ * @param changes Array indicating modified columns.
+ * @retval true, if any of the columns that are part of the parent
+ * key for FK constraint are modified.
*/
-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) {
+ 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;
}
/*
@@ -834,7 +577,6 @@ sqlite3FkCheck(Parse * pParse, /* Parse context */
)
{
sqlite3 *db = pParse->db; /* Database handle */
- FKey *pFKey; /* Used to iterate through FKs */
struct session *user_session = current_session();
/* Exactly one of regOld and regNew should be non-zero. */
@@ -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 &&
+ !fkey_child_is_modified(fk_def, aChange))
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;
- }
- for (i = 0; i < pFKey->nCol; i++) {
- if (aiCol[i] == pTab->iPKey) {
- aiCol[i] = -1;
- }
- assert(pIdx == 0 || pIdx->aiColumn[i] >= 0);
- }
-
pParse->nTab++;
-
+ struct space *parent = space_by_id(fk_def->parent_id);
+ assert(parent != NULL);
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.
*/
- fkLookupParent(pParse, pTo, pIdx, pFKey, aiCol,
- regOld, -1, bIgnore);
+ fkey_lookup_parent(pParse, parent, fk_def, fk->index_id,
+ regOld, -1, bIgnore);
}
- if (regNew != 0 && !isSetNullAction(pParse, pFKey)) {
+ if (regNew != 0 && !fkey_action_is_set_null(pParse, 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.
*
@@ -906,29 +620,23 @@ sqlite3FkCheck(Parse * pParse, /* Parse context */
* 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(pParse, parent, fk_def, fk->index_id,
+ regNew, +1, bIgnore);
}
-
- 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) {
+ for (struct fkey *fk = space->parent_fkey; fk != NULL;
+ fk = fk->fkey_parent_next) {
+ struct fkey_def *fk_def = fk->def;
+ if (aChange != NULL &&
+ !fkey_parent_is_modified(fk_def, aChange))
continue;
- }
-
- if (!pFKey->isDeferred
- && !(user_session->sql_flags & SQLITE_DeferFKs)
- && !pParse->pToplevel && !pParse->isMultiWrite) {
+ if (!fk_def->is_deferred &&
+ !(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.
@@ -936,29 +644,30 @@ sqlite3FkCheck(Parse * pParse, /* Parse context */
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()
*/
- pSrc = sqlite3SrcListAppend(db, 0, 0);
- if (pSrc) {
+ struct SrcList *pSrc = sqlite3SrcListAppend(db, 0, 0);
+ if (pSrc != NULL) {
struct SrcList_item *pItem = pSrc->a;
- pItem->pTab = pFKey->pFrom;
- pItem->zName = pFKey->pFrom->def->name;
+ struct space *child = space_by_id(fk->def->child_id);
+ assert(child != NULL);
+ struct Table *tab =
+ sqlite3HashFind(&db->pSchema->tblHash,
+ child->def->name);
+ pItem->pTab = tab;
+ pItem->zName = sqlite3DbStrDup(db, child->def->name);
pItem->pTab->nTabRef++;
pItem->iCursor = pParse->nTab++;
if (regNew != 0) {
- fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey,
- aiCol, regNew, -1);
+ fkScanChildren(pParse, pSrc, pTab, fk->def,
+ regNew, -1);
}
if (regOld != 0) {
- int eAction = pFKey->aAction[aChange != 0];
- fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey,
- aiCol, regOld, 1);
+ enum fkey_action action = fk_def->on_update;
+ fkScanChildren(pParse, pSrc, pTab, fk->def,
+ 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.
@@ -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)))
-/*
- * 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;
- 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;
}
-/*
- * 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.
+ 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 space->parent_fkey != NULL ||
+ space->child_fkey != NULL;
} 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;
+ for (struct fkey *p = space->child_fkey; p != NULL;
+ p = p->fkey_child_next) {
+ if (fkey_child_is_modified(p->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;
+ for (struct fkey *p = space->parent_fkey; p != NULL;
+ p = p->fkey_parent_next) {
+ if (fkey_parent_is_modified(p->def, changes))
+ return true;
}
}
}
- return 0;
+ return false;
}
/**
@@ -1114,40 +797,29 @@ sqlite3FkRequired(Table * pTab, /* Table being modified */
* @retval NULL on failure.
*/
static struct sql_trigger *
-fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
+fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct fkey *fkey,
struct ExprList *pChanges)
{
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 */
+ bool is_update = pChanges != NULL;
+ 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))
+ return NULL;
+ 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 */
@@ -1155,20 +827,12 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
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 != 0
- || (pTab->iPKey >= 0
- && pTab->iPKey <
- (int)pTab->def->field_count));
- assert(pIdx == 0 || pIdx->aiColumn[i] >= 0);
+ iFromCol = fk_def->links[i].child_field;
sqlite3TokenInit(&tToCol,
- pTab->def->fields[pIdx ? pIdx->
- aiColumn[i] : pTab->iPKey].
- name);
+ pTab->def->fields[fk_def->links[i].parent_field].name);
+
sqlite3TokenInit(&tFromCol,
- pFKey->pFrom->def->fields[
- iFromCol].name);
+ child_space->def->fields[iFromCol].name);
/* Create the expression "OLD.zToCol = zFromCol". It is important
* that the "OLD.zToCol" term is on the LHS of the = operator, so
@@ -1215,10 +879,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 || pChanges)) {
Expr *pNew;
- if (action == OE_Cascade) {
+ if (action == FKEY_ACTION_CASCADE) {
pNew = sqlite3PExpr(pParse, TK_DOT,
sqlite3ExprAlloc(db,
TK_ID,
@@ -1228,10 +892,8 @@ fkActionTrigger(struct Parse *pParse, struct Table *pTab, struct FKey *pFKey,
TK_ID,
&tToCol,
0));
- } else if (action == OE_SetDflt) {
- uint32_t space_id =
- SQLITE_PAGENO_TO_SPACEID(
- pFKey->pFrom->tnum);
+ } else if (action == FKEY_ACTION_SET_DEFAULT) {
+ uint32_t space_id = fk_def->child_id;
Expr *pDflt =
space_column_default_expr(
space_id, (uint32_t)iFromCol);
@@ -1256,12 +918,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 = sqlite3Strlen30(zFrom);
- if (action == OE_Restrict) {
+ if (action == FKEY_ACTION_RESTRICT) {
Token tFrom;
Expr *pRaise;
@@ -1285,7 +946,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 =
@@ -1323,11 +983,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 (pChanges == NULL) {
pStep->op = TK_DELETE;
break;
}
@@ -1335,9 +995,13 @@ 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;
+ else
+ fkey->on_delete_trigger = trigger;
+ trigger->op = (pChanges ? TK_UPDATE : TK_DELETE);
}
return trigger;
@@ -1362,13 +1026,14 @@ sqlite3FkActions(Parse * pParse, /* Parse context */
* 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 space *space = space_by_id(pTab->def->id);
+ assert(space != NULL);
+ for (struct fkey *fkey = space->parent_fkey; fkey != NULL;
+ fkey = fkey->fkey_parent_next) {
+ if (aChange == 0 ||
+ fkey_parent_is_modified(fkey->def, aChange)) {
struct sql_trigger *pAct =
- fkActionTrigger(pParse, pTab, pFKey,
+ fkActionTrigger(pParse, pTab, fkey,
pChanges);
if (pAct == NULL)
continue;
@@ -1381,45 +1046,4 @@ sqlite3FkActions(Parse * pParse, /* Parse context */
}
}
-/*
- * 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);
- }
-}
-#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */
+#endif /* ifndef SQLITE_OMIT_TRIGGER */
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index c12043bde..f7bee1a66 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1360,13 +1360,13 @@ sqlite3GenerateConstraintChecks(Parse * pParse, /* The parser context */
bool no_delete_triggers =
(0 == (user_session->sql_flags &
SQLITE_RecTriggers) ||
- sql_triggers_exist(pTab, TK_DELETE, NULL, NULL) ==
+ sql_triggers_exist(pTab, TK_DELETE,NULL, NULL) ==
NULL);
+ struct space *space = space_by_id(space_id);
+ assert(space != NULL);
bool no_foreign_keys =
- (0 == (user_session->sql_flags &
- SQLITE_ForeignKeys) ||
- (0 == pTab->pFKey &&
- 0 == sqlite3FkReferences(pTab)));
+ (0 == (user_session->sql_flags & SQLITE_ForeignKeys) ||
+ (space->child_fkey == NULL && space->parent_fkey));
if (no_secondary_indexes && no_foreign_keys &&
proper_error_action && no_delete_triggers) {
@@ -1606,7 +1606,7 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing context */
if (isUpdate || /* Condition 1 */
IsPrimaryKeyIndex(pIdx) || /* Condition 2 */
- sqlite3FkReferences(pTab) || /* Condition 3 */
+ space->parent_fkey != NULL || /* Condition 3 */
/* Condition 4 */
(index_is_unique(pIdx) && pIdx->onError !=
ON_CONFLICT_ACTION_DEFAULT &&
@@ -1883,10 +1883,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 &&
+ dest->child_fkey != NULL)
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 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);
/* 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
@@ -51,6 +51,7 @@
//
%include {
#include "sqliteInt.h"
+#include "box/fkey.h"
/*
** Disable all error recovery processing in the parser push-down
@@ -285,8 +286,8 @@ ccons ::= UNIQUE onconf(R). {sql_create_index(pParse,0,0,0,R,0,0,
SQLITE_IDXTYPE_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
@@ -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; }
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;}
@@ -338,8 +343,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;}
@@ -1441,6 +1445,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 31581b17f..8a736859a 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.
*/
@@ -594,210 +565,41 @@ 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)
+ uint32_t space_id = box_space_id_by_name(zRight,
+ strlen(zRight));
+ if (space_id == BOX_ID_NIL)
break;
- FKey *fkey = table->pFKey;
+ struct space *space = space_by_id(space_id);
+ struct fkey *fkey = space->child_fkey;
if (fkey == NULL)
break;
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;
+ 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;
+ fkey = fkey->fkey_child_next;
}
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) {
- if (pIdx == 0) {
- sqlite3OpenTable(pParse,
- i,
- pParent,
- OP_OpenRead);
- } else {
- sqlite3VdbeAddOp3(v,
- OP_OpenRead,
- i,
- pIdx->
- tnum,
- 0);
- sql_vdbe_set_p4_key_def(pParse,
- pIdx);
- }
- } else {
- k = 0;
- break;
- }
- }
- 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);
- if (iKey != pTab->iPKey) {
- sqlite3VdbeAddOp3(v,
- OP_Column,
- 0,
- iKey,
- regRow);
- sqlite3ColumnDefault(v,
- pTab->def,
- iKey,
- regRow);
- sqlite3VdbeAddOp2(v,
- OP_IsNull,
- regRow,
- addrOk);
- VdbeCoverage(v);
- }
- 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..b1a169ed1 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -135,13 +135,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 629f68e4f..3d0f4fb8a 100644
--- a/src/box/sql/prepare.c
+++ b/src/box/sql/prepare.c
@@ -447,6 +447,11 @@ 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) {
+ sql_expr_list_delete(db, fk->selfref_cols);
+ fk = fk->next;
+ }
if (db != NULL) {
assert(db->lookaside.bDisable >=
parser->disableLookaside);
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
@@ -1473,7 +1473,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;
@@ -1526,7 +1525,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 */
@@ -1978,42 +1975,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
@@ -2863,6 +2825,34 @@ enum ast_type {
ast_type_MAX
};
+/**
+ * Structure representing foreign keys constraints appeared
+ * within CREATE TABLE statement. Used only during parsing.
+ */
+struct fkey_parse {
+ /**
+ * List of foreign keys constraints 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 fkey_parse *next;
+};
+
/*
* 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
@@ -2957,7 +2947,8 @@ 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 */
-
+ /** Foreign key constraint appeared in CREATE TABLE stmt. */
+ struct fkey_parse *new_fkey;
bool initiateTTrans; /* Initiate Tarantool transaction */
/** If set - do not emit byte code at all, just parse. */
bool parse_only;
@@ -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.
+ *
+ * @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 *);
@@ -4665,27 +4705,36 @@ 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 sqlite3FkDropTable(Parse *, SrcList *, Table *);
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 sqlite3FkDropTable(a,b,c)
-#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..ac50b46bd 100644
--- a/src/box/sql/status.c
+++ b/src/box/sql/status.c
@@ -247,10 +247,7 @@ sqlite3_db_status(sqlite3 * db, /* The database connection whose status is desir
nByte +=
ROUND8(sizeof(HashElem)) *
- (pSchema->tblHash.count +
- pSchema->fkeyHash.count);
- nByte += sqlite3_msize(pSchema->tblHash.ht);
- nByte += sqlite3_msize(pSchema->fkeyHash.ht);
+ (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 c31da131d..6944a19ef 100644
--- a/src/box/sql/tarantoolInt.h
+++ b/src/box/sql/tarantoolInt.h
@@ -155,6 +155,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 212adbcb3..3e65ab771 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
@@ -431,7 +431,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 2c6bd2ba8..b9723e2e7 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"
@@ -4699,7 +4700,6 @@ case OP_RenameTable: {
const char *zOldTableName;
const char *zNewTableName;
Table *pTab;
- FKey *pFKey;
int iRootPage;
InitData initData;
char *argv[4] = {NULL, NULL, NULL, NULL};
@@ -4722,20 +4722,6 @@ case OP_RenameTable: {
&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);
- assert(pFKey->pFrom);
- rc = tarantoolSqlite3RenameParentTable(pFKey->pFrom->tnum,
- 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);
initData.db = db;
diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua
index 3e5c6102b..075e61ce3 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..e4470ecbb
--- /dev/null
+++ b/test/sql-tap/alter2.test.lua
@@ -0,0 +1,196 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(15)
+
+-- 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:finish_test()
diff --git a/test/sql-tap/fkey1.test.lua b/test/sql-tap/fkey1.test.lua
index 494af4b4a..3c29b097d 100755
--- a/test/sql-tap/fkey1.test.lua
+++ b/test/sql-tap/fkey1.test.lua
@@ -1,13 +1,13 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(19)
+test:plan(18)
-- This file implements regression tests for foreign keys.
test:do_execsql_test(
"fkey1-1.1",
[[
- CREATE TABLE t2(x PRIMARY KEY, y TEXT);
+ CREATE TABLE t2(x PRIMARY KEY, y TEXT, UNIQUE (x, y));
]], {
-- <fkey1-1.1>
-- </fkey1-1.1>
@@ -17,10 +17,10 @@ test:do_execsql_test(
"fkey1-1.2",
[[
CREATE TABLE t1(
- a INTEGER PRIMARY KEY,
+ a PRIMARY KEY,
b INTEGER
REFERENCES t1 ON DELETE CASCADE
- REFERENCES t2,
+ REFERENCES t2 (x),
c TEXT,
FOREIGN KEY (b, c) REFERENCES t2(x, y) ON UPDATE CASCADE);
]], {
@@ -32,7 +32,7 @@ test:do_execsql_test(
"fkey1-1.3",
[[
CREATE TABLE t3(
- a INTEGER PRIMARY KEY REFERENCES t2,
+ a PRIMARY KEY REFERENCES t2,
b INTEGER REFERENCES t1,
FOREIGN KEY (a, b) REFERENCES t2(x, y));
]], {
@@ -64,13 +64,13 @@ test:do_execsql_test(
test:do_execsql_test(
"fkey1-3.1",
[[
- CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c);
+ CREATE TABLE t5(a PRIMARY KEY, b, c UNIQUE, UNIQUE(a, b));
CREATE TABLE t6(d REFERENCES t5, e PRIMARY KEY REFERENCES t5(c));
PRAGMA foreign_key_list(t6);
]], {
-- <fkey1-3.1>
- 0, 0, 'T5', 'E', 'C', 'NO ACTION', 'NO ACTION', 'NONE',
- 1, 0, 'T5', 'D', '', 'NO ACTION', 'NO ACTION', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE',
+ 1, 0, 'T5', 'E', 'C', 'no_action', 'no_action', 'NONE'
-- </fkey1-3.1>
})
@@ -81,8 +81,8 @@ test:do_execsql_test(
PRAGMA foreign_key_list(t7);
]], {
-- <fkey1-3.2>
- 0, 0, 'T5', 'D', 'A', 'NO ACTION', 'NO ACTION', 'NONE',
- 0, 1, 'T5', 'E', 'B', 'NO ACTION', 'NO ACTION', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE',
+ 0, 1, 'T5', 'E', 'B', 'no_action', 'no_action', 'NONE'
-- </fkey1-3.2>
})
@@ -91,12 +91,12 @@ test:do_execsql_test(
[[
CREATE TABLE t8(
d PRIMARY KEY, e, f,
- FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL);
+ FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON UPDATE SET NULL);
PRAGMA foreign_key_list(t8);
]], {
-- <fkey1-3.3>
- 0, 0, 'T5', 'D', '', 'SET NULL', 'CASCADE', 'NONE',
- 0, 1, 'T5', 'E', '', 'SET NULL', 'CASCADE', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'cascade', 'set_null', 'NONE',
+ 0, 1, 'T5', 'E', 'B', 'cascade', 'set_null', 'NONE'
-- </fkey1-3.3>
})
@@ -105,12 +105,12 @@ test:do_execsql_test(
[[
CREATE TABLE t9(
d PRIMARY KEY, e, f,
- FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT);
+ FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON UPDATE SET DEFAULT);
PRAGMA foreign_key_list(t9);
]], {
-- <fkey1-3.4>
- 0, 0, 'T5', 'D', '', 'SET DEFAULT', 'CASCADE', 'NONE',
- 0, 1, 'T5', 'E', '', 'SET DEFAULT', 'CASCADE', 'NONE'
+ 0, 0, 'T5', 'D', 'A', 'cascade', 'set_default', 'NONE',
+ 0, 1, 'T5', 'E', 'B', 'cascade', 'set_default', 'NONE'
-- </fkey1-3.4>
})
@@ -144,7 +144,7 @@ test:do_execsql_test(
"fkey1-5.1",
[[
CREATE TABLE t11(
- x INTEGER PRIMARY KEY,
+ x PRIMARY KEY,
parent REFERENCES t11 ON DELETE CASCADE);
INSERT INTO t11 VALUES(1, NULL), (2, 1), (3, 2);
]], {
@@ -176,7 +176,7 @@ test:do_execsql_test(
"fkey1-5.4",
[[
CREATE TABLE Foo (
- Id INTEGER PRIMARY KEY,
+ Id PRIMARY KEY,
ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE,
C1);
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A');
@@ -208,7 +208,7 @@ test:do_execsql_test(
-- </fkey1-5.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"fkey1-6.1",
[[
CREATE TABLE p1(id PRIMARY KEY, x, y);
@@ -217,23 +217,16 @@ test:do_execsql_test(
CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
]], {
-- <fkey1-6.1>
+ 1, "Failed to create foreign key constraint 'FK_CONSTRAINT_1_C1': referenced fields don't compose unique index"
-- </fkey1-6.1>
})
-test:do_catchsql_test(
- "fkey1-6.2",
- [[
- INSERT INTO c1 VALUES(1);
- ]], {
- -- <fkey1-6.2>
- 1, "foreign key mismatch - \"C1\" referencing \"P1\""
- -- </fkey1-6.2>
- })
-
test:do_execsql_test(
"fkey1-6.3",
[[
CREATE UNIQUE INDEX p1x2 ON p1(x);
+ DROP TABLE IF EXISTS c1;
+ CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
INSERT INTO c1 VALUES(1);
]], {
-- <fkey1-6.3>
diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua
index 89a9279da..ddcf3116e 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>
@@ -300,7 +300,7 @@ test:do_catchsql_test(
[[
CREATE TABLE t9(a PRIMARY KEY REFERENCES nosuchtable, b);
]], {
- 1, "foreign key constraint references nonexistent table: NOSUCHTABLE"
+ 1, "Space 'NOSUCHTABLE' does not exist"
})
test:do_catchsql_test(
@@ -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, "foreign key mismatch - \"C\" referencing \"V\""
+ 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, "foreign key mismatch - \"C\" referencing \"P\""
+ 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, "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>
})
@@ -1076,14 +1070,14 @@ test:do_catchsql_test(
DROP TABLE t1;
]], {
-- <fkey2-10.8>
- 1, "FOREIGN KEY constraint failed"
+ 1, "can't drop table T1: other objects depend on it"
-- </fkey2-10.8>
})
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);
@@ -1189,28 +1132,30 @@ test:do_execsql_test(
DROP TABLE b1;
]], {
-- <fkey2-10.18>
+ 1, "can't drop table B1: other objects depend on it"
-- </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);
DROP TABLE b2;
]], {
-- <fkey2-10.19>
+ 1, "can't drop table B2: other objects depend on it"
-- </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>
})
@@ -1222,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;
]], {
@@ -1292,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;
]], {
@@ -1364,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);
@@ -1428,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));
@@ -1464,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 2532ec6a0..84385d10c 100755
--- a/test/sql-tap/fkey3.test.lua
+++ b/test/sql-tap/fkey3.test.lua
@@ -36,7 +36,7 @@ test:do_catchsql_test(
DROP TABLE t1;
]], {
-- <fkey3-1.3.1>
- 1, "can't drop parent table T1 when child table refers to it"
+ 1, "can't drop table T1: other objects depend on it"
-- </fkey3-1.3.1>
})
@@ -46,7 +46,7 @@ test:do_catchsql_test(
DROP TABLE t1;
]], {
-- <fkey3-1.3.2>
- 1, "can't drop parent table T1 when child table refers to it"
+ 1, "can't drop table T1: other objects depend on it"
-- </fkey3-1.3.2>
})
@@ -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/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);
INSERT INTO p1 VALUES (1, 'one');
INSERT INTO p1 VALUES (2, 'two');
INSERT INTO c1 VALUES (1);
diff --git a/test/sql-tap/orderby1.test.lua b/test/sql-tap/orderby1.test.lua
index e216df2ca..f4c426397 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 6aa290742..24f494852 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
@@ -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, "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, [[no such column X]]
-- </table-10.12>
})
@@ -876,7 +878,7 @@ test:do_test(
]]
end, {
-- <table-10.13>
- 1, [[unknown column "X" in foreign key definition]]
+ 1, [[no such column X]]
-- </table-10.13>
})
diff --git a/test/sql-tap/tkt-b1d3a2e531.test.lua b/test/sql-tap/tkt-b1d3a2e531.test.lua
index 951299dbd..3b70be36a 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
);
@@ -124,7 +124,7 @@ test:do_catchsql_test(
DROP TABLE cc1;
]], {
-- <3.2>
- 1, "can't drop parent table PP1 when child table refers to it"
+ 1, "can't drop table PP1: other objects depend on it"
-- </3.2>
})
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