From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp52.i.mail.ru (smtp52.i.mail.ru [94.100.177.112]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 3178D446440 for ; Sat, 12 Sep 2020 00:51:20 +0300 (MSK) From: Roman Khabibov Date: Sat, 12 Sep 2020 00:51:15 +0300 Message-Id: <20200911215115.6622-7-roman.habibov@tarantool.org> In-Reply-To: <20200911215115.6622-1-roman.habibov@tarantool.org> References: <20200911215115.6622-1-roman.habibov@tarantool.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v4 6/6] sql: support column addition List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: v.shpilevoy@tarantool.org Enable to add column to existing space with statement. Column definition can be supplemented with the four types of constraints, , clauses and <[NOT] NULL>, AUTOINCREMENT. Closes #2349, #3075 @TarantoolBot document Title: Add columns to existing tables in SQL Now, it is possible to add columns to existing empty spaces using statement. The column definition is the same as in statement. For example: ``` tarantool> box.execute("CREATE TABLE test (a INTEGER PRIMARY KEY)") --- - row_count: 1 ... tarantool> box.execute([[ALTER TABLE test ADD COLUMN b TEXT > CHECK (LENGTH(b) > 1) > NOT NULL > DEFAULT ('aa') > COLLATE "unicode_ci" > ]]) --- - row_count: 0 ... ``` --- extra/mkkeywordhash.c | 2 +- src/box/errcode.h | 1 + src/box/sql/build.c | 389 ++++++++++++++++++------ src/box/sql/parse.y | 46 ++- src/box/sql/parse_def.h | 26 +- src/box/sql/sqlInt.h | 45 ++- test/box/error.result | 1 + test/sql/add-column.result | 529 +++++++++++++++++++++++++++++++++ test/sql/add-column.test.sql | 183 ++++++++++++ test/sql/checks.result | 20 ++ test/sql/checks.test.lua | 9 + test/sql/foreign-keys.result | 28 ++ test/sql/foreign-keys.test.lua | 11 + 13 files changed, 1171 insertions(+), 119 deletions(-) create mode 100644 test/sql/add-column.result create mode 100644 test/sql/add-column.test.sql diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index bacdad9b3..7480c0211 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -76,7 +76,7 @@ static Keyword aKeywordTable[] = { { "CHECK", "TK_CHECK", true }, { "COLLATE", "TK_COLLATE", true }, { "COLUMN_REF", "TK_COLUMN_REF", true }, - { "COLUMN", "TK_STANDARD", true }, + { "COLUMN", "TK_COLUMN", true }, { "COMMIT", "TK_COMMIT", true }, { "CONFLICT", "TK_CONFLICT", false }, { "CONSTRAINT", "TK_CONSTRAINT", true }, diff --git a/src/box/errcode.h b/src/box/errcode.h index 3c21375f5..ec16399a0 100644 --- a/src/box/errcode.h +++ b/src/box/errcode.h @@ -271,6 +271,7 @@ struct errcode_record { /*216 */_(ER_SYNC_QUORUM_TIMEOUT, "Quorum collection for a synchronous transaction is timed out") \ /*217 */_(ER_SYNC_ROLLBACK, "A rollback for a synchronous transaction is received") \ /*218 */_(ER_TUPLE_METADATA_IS_TOO_BIG, "Can't create tuple: metadata size %u is too big") \ + /*219 */_(ER_SQL_CANT_ADD_AUTOINC, "Can't add AUTOINCREMENT: the space '%s' already has one auto incremented field") \ /* * !IMPORTANT! Please follow instructions at start of the file diff --git a/src/box/sql/build.c b/src/box/sql/build.c index d1d240315..677099b48 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -285,48 +285,110 @@ sql_field_retrieve(Parse *parser, struct space_def *space_def, uint32_t id) return field; } -/* - * Add a new column to the table currently being constructed. +/** + * Make shallow copy of @a space on region. * - * The parser calls this routine once for each column declaration - * in a CREATE TABLE statement. sqlStartTable() gets called - * first to get things going. Then this routine is called for each - * column. + * Function is used to add a new column to an existing space with + * statement. Copy space def and index + * array to create constraints appeared in the statement. The + * index array copy will be modified by adding new elements to it. + * It is necessary, because the statement may contain several + * index definitions (constraints). */ +static struct space * +sql_shallow_space_copy(struct Parse *parse, struct space *space) +{ + assert(space->def != NULL); + struct space *ret = sql_ephemeral_space_new(parse, space->def->name); + if (ret == NULL) + goto error; + ret->index_count = space->index_count; + ret->index_id_max = space->index_id_max; + size_t size = 0; + ret->index = region_alloc_array(&parse->region, typeof(struct index *), + ret->index_count, &size); + if (ret->index == NULL) { + diag_set(OutOfMemory, size, "region_alloc_array", "ret->index"); + goto error; + } + memcpy(ret->index, space->index, size); + memcpy(ret->def, space->def, sizeof(struct space_def)); + ret->def->opts.is_temporary = true; + ret->def->opts.is_ephemeral = true; + if (ret->def->field_count != 0) { + uint32_t fields_size = 0; + ret->def->fields = + region_alloc_array(&parse->region, + typeof(struct field_def), + ret->def->field_count, &fields_size); + if (ret->def->fields == NULL) { + diag_set(OutOfMemory, fields_size, "region_alloc", + "ret->def->fields"); + goto error; + } + memcpy(ret->def->fields, space->def->fields, fields_size); + } + + return ret; +error: + parse->is_aborted = true; + return NULL; +} + void -sqlAddColumn(Parse * pParse, Token * pName, struct type_def *type_def) +sql_create_column_start(struct Parse *parse) { - assert(type_def != NULL); - char *z; - sql *db = pParse->db; - if (pParse->create_table_def.new_space == NULL) - return; - struct space_def *def = pParse->create_table_def.new_space->def; + struct create_column_def *create_column_def = &parse->create_column_def; + struct alter_entity_def *alter_entity_def = + &create_column_def->base.base; + assert(alter_entity_def->entity_type == ENTITY_TYPE_COLUMN); + assert(alter_entity_def->alter_action == ALTER_ACTION_CREATE); + struct space *space = parse->create_table_def.new_space; + bool is_alter = space == NULL; + struct sql *db = parse->db; + if (is_alter) { + const char *space_name = + alter_entity_def->entity_name->a[0].zName; + space = space_by_name(space_name); + if (space == NULL) { + diag_set(ClientError, ER_NO_SUCH_SPACE, space_name); + goto tnt_error; + } + space = sql_shallow_space_copy(parse, space); + if (space == NULL) + goto tnt_error; + } + create_column_def->space = space; + struct space_def *def = space->def; + assert(def->opts.is_ephemeral); #if SQL_MAX_COLUMN if ((int)def->field_count + 1 > db->aLimit[SQL_LIMIT_COLUMN]) { diag_set(ClientError, ER_SQL_COLUMN_COUNT_MAX, def->name, def->field_count + 1, db->aLimit[SQL_LIMIT_COLUMN]); - pParse->is_aborted = true; - return; + goto tnt_error; } #endif + + struct region *region = &parse->region; + struct Token *name = &create_column_def->base.name; + char *column_name = + sql_normalized_name_region_new(region, name->z, name->n); + if (column_name == NULL) + goto tnt_error; + /* - * As sql_field_retrieve will allocate memory on region - * ensure that def is also temporal and would be dropped. + * Format can be set in Lua, then exact_field_count can be + * zero, but field_count is not. */ - assert(def->opts.is_ephemeral); - if (sql_field_retrieve(pParse, def, def->field_count) == NULL) + if (def->exact_field_count == 0) + def->exact_field_count = def->field_count; + if (sql_field_retrieve(parse, def, def->field_count) == NULL) return; - struct region *region = &pParse->region; - z = sql_normalized_name_region_new(region, pName->z, pName->n); - if (z == NULL) { - pParse->is_aborted = true; - return; - } + struct field_def *column_def = &def->fields[def->field_count]; memcpy(column_def, &field_def_default, sizeof(field_def_default)); - column_def->name = z; + column_def->name = column_name; /* * Marker ON_CONFLICT_ACTION_DEFAULT is used to detect * attempts to define NULL multiple time or to detect @@ -334,18 +396,86 @@ sqlAddColumn(Parse * pParse, Token * pName, struct type_def *type_def) */ column_def->nullable_action = ON_CONFLICT_ACTION_DEFAULT; column_def->is_nullable = true; - column_def->type = type_def->type; + column_def->type = create_column_def->type_def->type; def->field_count++; + + sqlSrcListDelete(db, alter_entity_def->entity_name); + return; +tnt_error: + parse->is_aborted = true; + sqlSrcListDelete(db, alter_entity_def->entity_name); +} + +static void +sql_vdbe_create_constraints(struct Parse *parse, int reg_space_id); + +void +sql_create_column_end(struct Parse *parse) +{ + struct space *space = parse->create_column_def.space; + assert(space != NULL); + struct space_def *def = space->def; + struct field_def *field = &def->fields[def->field_count - 1]; + if (field->nullable_action == ON_CONFLICT_ACTION_DEFAULT) { + field->nullable_action = ON_CONFLICT_ACTION_NONE; + field->is_nullable = true; + } + /* + * Encode the format array and emit code to update _space. + */ + uint32_t table_stmt_sz = 0; + struct region *region = &parse->region; + char *table_stmt = sql_encode_table(region, def, &table_stmt_sz); + char *raw = sqlDbMallocRaw(parse->db, table_stmt_sz); + if (table_stmt == NULL || raw == NULL) { + parse->is_aborted = true; + return; + } + memcpy(raw, table_stmt, table_stmt_sz); + + struct Vdbe *v = sqlGetVdbe(parse); + assert(v != NULL); + + struct space *system_space = space_by_id(BOX_SPACE_ID); + assert(system_space != NULL); + int cursor = parse->nTab++; + vdbe_emit_open_cursor(parse, cursor, 0, system_space); + sqlVdbeChangeP5(v, OPFLAG_SYSTEMSP); + + int key_reg = ++parse->nMem; + sqlVdbeAddOp2(v, OP_Integer, def->id, key_reg); + int addr = sqlVdbeAddOp4Int(v, OP_Found, cursor, 0, key_reg, 1); + sqlVdbeAddOp2(v, OP_Halt, -1, ON_CONFLICT_ACTION_ABORT); + sqlVdbeJumpHere(v, addr); + + int tuple_reg = sqlGetTempRange(parse, box_space_field_MAX + 1); + for (int i = 0; i < box_space_field_MAX - 1; ++i) + sqlVdbeAddOp3(v, OP_Column, cursor, i, tuple_reg + i); + sqlVdbeAddOp1(v, OP_Close, cursor); + + sqlVdbeAddOp2(v, OP_Integer, def->field_count, + tuple_reg + BOX_SPACE_FIELD_FIELD_COUNT); + sqlVdbeAddOp4(v, OP_Blob, table_stmt_sz, + tuple_reg + BOX_SPACE_FIELD_FORMAT, + SQL_SUBTYPE_MSGPACK, raw, P4_DYNAMIC); + sqlVdbeAddOp3(v, OP_MakeRecord, tuple_reg, box_space_field_MAX, + tuple_reg + box_space_field_MAX); + sqlVdbeAddOp4(v, OP_IdxReplace, tuple_reg + box_space_field_MAX, 0, 0, + (char *) system_space, P4_SPACEPTR); + sqlVdbeCountChanges(v); + sqlVdbeChangeP5(v, OPFLAG_NCHANGE); + sqlReleaseTempRange(parse, tuple_reg, box_space_field_MAX + 1); + sql_vdbe_create_constraints(parse, key_reg); } void sql_column_add_nullable_action(struct Parse *parser, enum on_conflict_action nullable_action) { - struct space *space = parser->create_table_def.new_space; - if (space == NULL || NEVER(space->def->field_count < 1)) + assert(parser->create_column_def.space != NULL); + struct space_def *def = parser->create_column_def.space->def; + if (NEVER(def->field_count < 1)) return; - struct space_def *def = space->def; struct field_def *field = &def->fields[def->field_count - 1]; if (field->nullable_action != ON_CONFLICT_ACTION_DEFAULT && nullable_action != field->nullable_action) { @@ -364,20 +494,21 @@ sql_column_add_nullable_action(struct Parse *parser, } /* - * The expression is the default value for the most recently added column - * of the table currently under construction. + * The expression is the default value for the most recently added + * column. * * Default value expressions must be constant. Raise an exception if this * is not the case. * * This routine is called by the parser while in the middle of - * parsing a CREATE TABLE statement. + * parsing a or an + * statement. */ void sqlAddDefaultValue(Parse * pParse, ExprSpan * pSpan) { sql *db = pParse->db; - struct space *p = pParse->create_table_def.new_space; + struct space *p = pParse->create_column_def.space; if (p != NULL) { assert(p->def->opts.is_ephemeral); struct space_def *def = p->def; @@ -447,6 +578,8 @@ sqlAddPrimaryKey(struct Parse *pParse) int nTerm; struct ExprList *pList = pParse->create_index_def.cols; struct space *space = pParse->create_table_def.new_space; + if (space == NULL) + space = pParse->create_column_def.space; if (space == NULL) goto primary_key_exit; if (sql_space_primary_key(space) != NULL) { @@ -574,8 +707,10 @@ sql_create_check_contraint(struct Parse *parser) (struct alter_entity_def *) create_ck_def; assert(alter_def->entity_type == ENTITY_TYPE_CK); (void) alter_def; - struct space *space = parser->create_table_def.new_space; - bool is_alter = space == NULL; + struct space *space = parser->create_column_def.space; + if (space == NULL) + space = parser->create_table_def.new_space; + bool is_alter_add_constr = space == NULL; /* Prepare payload for ck constraint definition. */ struct region *region = &parser->region; @@ -589,8 +724,22 @@ sql_create_check_contraint(struct Parse *parser) return; } } else { - assert(! is_alter); + assert(!is_alter_add_constr); uint32_t ck_idx = ++parser->checks_def.count; + /* + * If it is we should + * count the existing CHECK constraints in the + * space and form a name based on this. + */ + if (parser->create_table_def.new_space == NULL) { + struct space *original_space = + space_by_name(space->def->name); + assert(original_space != NULL); + struct ck_constraint *ck; + rlist_foreach_entry(ck, &original_space->ck_constraint, + link) + ck_idx++; + } name = tt_sprintf("ck_unnamed_%s_%d", space->def->name, ck_idx); } size_t name_len = strlen(name); @@ -634,7 +783,7 @@ sql_create_check_contraint(struct Parse *parser) trim_space_snprintf(ck_def->expr_str, expr_str, expr_str_len); memcpy(ck_def->name, name, name_len); ck_def->name[name_len] = '\0'; - if (is_alter) { + if (is_alter_add_constr) { const char *space_name = alter_def->entity_name->a[0].zName; struct space *space = space_by_name(space_name); if (space == NULL) { @@ -663,9 +812,8 @@ sql_create_check_contraint(struct Parse *parser) void sqlAddCollateType(Parse * pParse, Token * pToken) { - struct space *space = pParse->create_table_def.new_space; - if (space == NULL) - return; + struct space *space = pParse->create_column_def.space; + assert(space != NULL); uint32_t i = space->def->field_count - 1; sql *db = pParse->db; char *coll_name = sql_name_from_token(db, pToken); @@ -695,7 +843,6 @@ struct coll * sql_column_collation(struct space_def *def, uint32_t column, uint32_t *coll_id) { assert(def != NULL); - struct space *space = space_by_id(def->id); /* * It is not always possible to fetch collation directly * from struct space due to its absence in space cache. @@ -704,13 +851,13 @@ sql_column_collation(struct space_def *def, uint32_t column, uint32_t *coll_id) * * In cases mentioned above collation is fetched by id. */ - if (space == NULL) { - assert(def->opts.is_ephemeral); + if (def->opts.is_ephemeral) { assert(column < (uint32_t)def->field_count); *coll_id = def->fields[column].coll_id; struct coll_id *collation = coll_by_id(*coll_id); return collation != NULL ? collation->coll : NULL; } + struct space *space = space_by_id(def->id); struct tuple_field *field = tuple_format_field(space->format, column); *coll_id = field->coll_id; return field->coll; @@ -794,7 +941,8 @@ vdbe_emit_create_index(struct Parse *parse, struct space_def *def, memcpy(raw, index_parts, index_parts_sz); index_parts = raw; - if (parse->create_table_def.new_space != NULL) { + if (parse->create_table_def.new_space != NULL || + parse->create_column_def.space != NULL) { sqlVdbeAddOp2(v, OP_SCopy, space_id_reg, entry_reg); sqlVdbeAddOp2(v, OP_Integer, idx_def->iid, entry_reg + 1); } else { @@ -1032,18 +1180,21 @@ vdbe_emit_fk_constraint_create(struct Parse *parse_context, P4_DYNAMIC); /* * In case we are adding FK constraints during execution - * of statement, we don't have child - * id, but we know register where it will be stored. + * of or + * statement, we don't have child id, but we know register + * where it will be stored. */ - if (parse_context->create_table_def.new_space != NULL) { + bool is_alter_add_constr = + parse_context->create_table_def.new_space == NULL && + parse_context->create_column_def.space == NULL; + if (!is_alter_add_constr) { sqlVdbeAddOp2(vdbe, OP_SCopy, fk->child_id, constr_tuple_reg + 1); } else { sqlVdbeAddOp2(vdbe, OP_Integer, fk->child_id, constr_tuple_reg + 1); } - if (parse_context->create_table_def.new_space != NULL && - fk_constraint_is_self_referenced(fk)) { + if (!is_alter_add_constr && fk_constraint_is_self_referenced(fk)) { sqlVdbeAddOp2(vdbe, OP_SCopy, fk->parent_id, constr_tuple_reg + 2); } else { @@ -1107,7 +1258,7 @@ vdbe_emit_fk_constraint_create(struct Parse *parse_context, constr_tuple_reg + 9); sqlVdbeAddOp2(vdbe, OP_SInsert, BOX_FK_CONSTRAINT_ID, constr_tuple_reg + 9); - if (parse_context->create_table_def.new_space == NULL) { + if (is_alter_add_constr) { sqlVdbeCountChanges(vdbe); sqlVdbeChangeP5(vdbe, OPFLAG_NCHANGE); } @@ -1148,15 +1299,28 @@ resolve_link(struct Parse *parse_context, const struct space_def *def, /** * Emit code to create sequences, indexes, check and foreign key - * constraints appeared in . + * constraints appeared in or + * . */ static void sql_vdbe_create_constraints(struct Parse *parse, int reg_space_id) { assert(reg_space_id != 0); struct space *space = parse->create_table_def.new_space; - assert(space != NULL); + bool is_alter = space == NULL; uint32_t i = 0; + /* + * If it is an , then we have to + * create all indexes added by this statement. These + * indexes are in the array, starting with old index_count + * (inside space object) and ending with new index_count + * (inside ephemeral space). + */ + if (is_alter) { + space = parse->create_column_def.space; + i = space_by_name(space->def->name)->index_count; + } + assert(space != NULL); for (; i < space->index_count; ++i) { struct index *idx = space->index[i]; vdbe_emit_create_index(parse, space->def, idx->def, @@ -1175,6 +1339,21 @@ sql_vdbe_create_constraints(struct Parse *parse, int reg_space_id) sqlVdbeAddOp2(v, OP_NextSequenceId, 0, reg_seq_id); int reg_seq_rec = emitNewSysSequenceRecord(parse, reg_seq_id, space->def->name); + if (is_alter) { + int errcode = ER_SQL_CANT_ADD_AUTOINC; + const char *error_msg = + tt_sprintf(tnt_errcode_desc(errcode), + space->def->name); + if (vdbe_emit_halt_with_presence_test(parse, + BOX_SEQUENCE_ID, + 2, + reg_seq_rec + 3, + 1, errcode, + error_msg, false, + OP_NoConflict) + != 0) + return; + } sqlVdbeAddOp2(v, OP_SInsert, BOX_SEQUENCE_ID, reg_seq_rec); /* Do an insertion into _space_sequence. */ int reg_space_seq_record = @@ -1873,24 +2052,28 @@ sql_create_foreign_key(struct Parse *parse_context) char *parent_name = NULL; char *constraint_name = NULL; bool is_self_referenced = false; + struct space *space = parse_context->create_column_def.space; struct create_table_def *table_def = &parse_context->create_table_def; - struct space *space = table_def->new_space; + if (space == NULL) + space = table_def->new_space; /* - * Space under construction during CREATE TABLE - * processing. NULL for ALTER TABLE statement handling. + * Space under construction during + * processing or shallow copy of space during . NULL for statement handling. */ - bool is_alter = space == NULL; + bool is_alter_add_constr = space == NULL; uint32_t child_cols_count; struct ExprList *child_cols = create_fk_def->child_cols; if (child_cols == NULL) { - assert(!is_alter); + assert(!is_alter_add_constr); child_cols_count = 1; } else { child_cols_count = child_cols->nExpr; } struct ExprList *parent_cols = create_fk_def->parent_cols; struct space *child_space = NULL; - if (is_alter) { + if (is_alter_add_constr) { const char *child_name = alter_def->entity_name->a[0].zName; child_space = space_by_name(child_name); if (child_space == NULL) { @@ -1908,6 +2091,12 @@ sql_create_foreign_key(struct Parse *parse_context) goto tnt_error; } memset(fk_parse, 0, sizeof(*fk_parse)); + /* + * Child space already exists if it is + * . + */ + if (table_def->new_space == NULL) + child_space = space; rlist_add_entry(&parse_context->fkeys_def.fkeys, fk_parse, link); } @@ -1921,29 +2110,42 @@ sql_create_foreign_key(struct Parse *parse_context) * self-referenced, but in this case parent (which is * also child) table will definitely exist. */ - is_self_referenced = !is_alter && + is_self_referenced = !is_alter_add_constr && strcmp(parent_name, space->def->name) == 0; struct space *parent_space = space_by_name(parent_name); - if (parent_space == NULL) { - if (is_self_referenced) { - struct rlist *fkeys = &parse_context->fkeys_def.fkeys; - struct fk_constraint_parse *fk = - rlist_first_entry(fkeys, - struct fk_constraint_parse, - link); - fk->selfref_cols = parent_cols; - fk->is_self_referenced = true; - } else { - diag_set(ClientError, ER_NO_SUCH_SPACE, parent_name);; - goto tnt_error; - } + if (parent_space == NULL && !is_self_referenced) { + diag_set(ClientError, ER_NO_SUCH_SPACE, parent_name); + goto tnt_error; + } + if (is_self_referenced) { + struct fk_constraint_parse *fk = + rlist_first_entry(&parse_context->fkeys_def.fkeys, + struct fk_constraint_parse, link); + fk->selfref_cols = parent_cols; + fk->is_self_referenced = true; } - if (!is_alter) { + if (!is_alter_add_constr) { if (create_def->name.n == 0) { - constraint_name = - sqlMPrintf(db, "fk_unnamed_%s_%d", - space->def->name, - ++parse_context->fkeys_def.count); + uint32_t idx = ++parse_context->fkeys_def.count; + /* + * If it is we + * should count the existing FK + * constraints in the space and form a + * name based on this. + */ + if (table_def->new_space == NULL) { + struct space *original_space = + space_by_name(space->def->name); + assert(original_space != NULL); + struct rlist *child_fk = + &original_space->child_fk_constraint; + struct fk_constraint *fk; + rlist_foreach_entry(fk, child_fk, + in_child_space) + idx++; + } + constraint_name = sqlMPrintf(db, "fk_unnamed_%s_%d", + space->def->name, idx); } else { constraint_name = sql_name_from_token(db, &create_def->name); @@ -2023,7 +2225,7 @@ sql_create_foreign_key(struct Parse *parse_context) constraint_name) != 0) { goto exit_create_fk; } - if (!is_alter) { + if (!is_alter_add_constr) { if (child_cols == NULL) { assert(i == 0); /* @@ -2052,12 +2254,14 @@ sql_create_foreign_key(struct Parse *parse_context) memcpy(fk_def->name, constraint_name, name_len); fk_def->name[name_len] = '\0'; /* - * In case of CREATE TABLE processing, all foreign keys - * constraints must be created after space itself, so - * lets delay it until sqlEndTable() call and simply - * maintain list of all FK constraints inside parser. + * In case of or processing, all foreign keys constraints must + * be created after space itself, so lets delay it until + * sqlEndTable() or sql_create_column_end() call and + * simply maintain list of all FK constraints inside + * parser. */ - if (!is_alter) { + if (!is_alter_add_constr) { struct fk_constraint_parse *fk_parse = rlist_first_entry(&parse_context->fkeys_def.fkeys, struct fk_constraint_parse, link); @@ -2430,7 +2634,10 @@ sql_create_index(struct Parse *parse) { * Find the table that is to be indexed. * Return early if not found. */ - struct space *space = NULL; + struct space *space = parse->create_table_def.new_space; + if (space == NULL) + space = parse->create_column_def.space; + bool is_create_table_or_add_col = space != NULL; struct Token token = create_entity_def->name; if (tbl_name != NULL) { assert(token.n > 0 && token.z != NULL); @@ -2443,10 +2650,8 @@ sql_create_index(struct Parse *parse) { } goto exit_create_index; } - } else { - if (parse->create_table_def.new_space == NULL) - goto exit_create_index; - space = parse->create_table_def.new_space; + } else if (!is_create_table_or_add_col) { + goto exit_create_index; } struct space_def *def = space->def; @@ -2481,7 +2686,7 @@ sql_create_index(struct Parse *parse) { * 2) UNIQUE constraint is non-named and standard * auto-index name will be generated. */ - if (parse->create_table_def.new_space == NULL) { + if (!is_create_table_or_add_col) { assert(token.z != NULL); name = sql_name_from_token(db, &token); if (name == NULL) { @@ -2647,7 +2852,7 @@ sql_create_index(struct Parse *parse) { * constraint, but has different onError (behavior on * constraint violation), then an error is raised. */ - if (parse->create_table_def.new_space != NULL) { + if (is_create_table_or_add_col) { for (uint32_t i = 0; i < space->index_count; ++i) { struct index *existing_idx = space->index[i]; uint32_t iid = existing_idx->def->iid; @@ -2735,7 +2940,7 @@ sql_create_index(struct Parse *parse) { sqlVdbeAddOp0(vdbe, OP_Expire); } - if (tbl_name != NULL) + if (!is_create_table_or_add_col) goto exit_create_index; sql_space_add_index(&parse->region, space, index); index = NULL; diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index d81045d50..3c50c6bbd 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -228,19 +228,24 @@ create_table_end ::= . { sqlEndTable(pParse); } */ columnlist ::= columnlist COMMA tcons. -columnlist ::= columnlist COMMA columnname carglist autoinc(I). { - uint32_t fieldno = pParse->create_table_def.new_space->def->field_count - 1; - if (I == 1 && sql_add_autoincrement(pParse, fieldno) != 0) - return; +columnlist ::= columnlist COMMA column_def create_column_end. +columnlist ::= column_def create_column_end. + +column_def ::= column_name_and_type carglist. + +column_name_and_type ::= nm(A) typedef(Y). { + create_column_def_init(&pParse->create_column_def, NULL, &A, &Y); + sql_create_column_start(pParse); } -columnlist ::= columnname carglist autoinc(I). { - uint32_t fieldno = pParse->create_table_def.new_space->def->field_count - 1; +create_column_end ::= autoinc(I). { + uint32_t fieldno = pParse->create_column_def.space->def->field_count - 1; if (I == 1 && sql_add_autoincrement(pParse, fieldno) != 0) return; + if (pParse->create_table_def.new_space == NULL) + sql_create_column_end(pParse); } columnlist ::= tcons. -columnname(A) ::= nm(A) typedef(Y). {sqlAddColumn(pParse,&A,&Y);} // An IDENTIFIER can be a generic identifier, or one of several // keywords. Any non-standard keyword can also be an identifier. @@ -283,9 +288,11 @@ nm(A) ::= id(A). { } } -// "carglist" is a list of additional constraints that come after the -// column name and column type in a CREATE TABLE statement. -// +/** + * "carglist" is a list of additional constraints and clauses that + * come after the column name and column type in a + * or statement. + */ carglist ::= carglist ccons. carglist ::= . %type cconsname { struct Token } @@ -1737,11 +1744,30 @@ alter_table_start(A) ::= ALTER TABLE fullname(T) . { A = T; } %type alter_add_constraint {struct alter_args} alter_add_constraint(A) ::= alter_table_start(T) ADD CONSTRAINT nm(N). { + A.table_name = T; + A.name = N; + pParse->initiateTTrans = true; + } + +%type alter_add_column {struct alter_args} +alter_add_column(A) ::= alter_table_start(T) ADD column_name(N). { A.table_name = T; A.name = N; pParse->initiateTTrans = true; } +column_name(N) ::= COLUMN nm(A). { N = A; } +column_name(N) ::= nm(A). { N = A; } + +cmd ::= alter_column_def carglist create_column_end. + +alter_column_def ::= alter_add_column(N) typedef(Y). { + create_column_def_init(&pParse->create_column_def, N.table_name, &N.name, &Y); + create_checks_def_init(&pParse->checks_def); + create_fkeys_def_init(&pParse->fkeys_def); + sql_create_column_start(pParse); +} + cmd ::= alter_add_constraint(N) FOREIGN KEY LP eidlist(FA) RP REFERENCES nm(T) eidlist_opt(TA) matcharg(M) refargs(R) defer_subclause_opt(D). { create_fk_def_init(&pParse->create_fk_def, N.table_name, &N.name, FA, &T, TA, diff --git a/src/box/sql/parse_def.h b/src/box/sql/parse_def.h index c44e3dbbe..29bc22ea8 100644 --- a/src/box/sql/parse_def.h +++ b/src/box/sql/parse_def.h @@ -107,9 +107,10 @@ struct fk_constraint_parse { */ struct fk_constraint_def *fk_def; /** - * 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. + * If inside or + * 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; @@ -154,6 +155,7 @@ enum sql_index_type { enum entity_type { ENTITY_TYPE_TABLE = 0, + ENTITY_TYPE_COLUMN, ENTITY_TYPE_VIEW, ENTITY_TYPE_INDEX, ENTITY_TYPE_TRIGGER, @@ -207,6 +209,14 @@ struct create_table_def { struct space *new_space; }; +struct create_column_def { + struct create_entity_def base; + /** Shallow space copy. */ + struct space *space; + /** Column type. */ + struct type_def *type_def; +}; + struct create_checks_def { struct rlist checks; uint32_t count; @@ -474,6 +484,16 @@ create_table_def_init(struct create_table_def *table_def, struct Token *name, if_not_exists); } +static inline void +create_column_def_init(struct create_column_def *column_def, + struct SrcList *table_name, struct Token *name, + struct type_def *type_def) +{ + create_entity_def_init(&column_def->base, ENTITY_TYPE_COLUMN, + table_name, name, false); + column_def->type_def = type_def; +} + static inline void create_checks_def_init(struct create_checks_def *checks_def) { diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 7057c20b6..7190ef19d 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -2251,20 +2251,24 @@ struct Parse { struct enable_entity_def enable_entity_def; }; /** - * Table def is not part of union since information - * being held must survive till the end of parsing of - * whole CREATE TABLE statement (to pass it to - * sqlEndTable() function). + * Table def or column def is not part of union since + * information being held must survive till the end of + * parsing of whole or + * statement (to pass it to + * sqlEndTable() sql_create_column_end() function). */ struct create_table_def create_table_def; + struct create_column_def create_column_def; /* - * FK and CK constraints appeared in a . + * FK and CK constraints appeared in a or + * an statement. */ struct create_fkeys_def fkeys_def; struct create_checks_def checks_def; /* - * True, if column within a statement to be - * created has . + * True, if column in a or an + * statement to be created has + * . */ bool has_autoinc; /* Id of field with . */ @@ -2858,15 +2862,30 @@ struct space *sqlResultSetOfSelect(Parse *, Select *); struct space * sqlStartTable(Parse *, Token *); -void sqlAddColumn(Parse *, Token *, struct type_def *); + +/** + * Add new field to the format of ephemeral space in + * create_column_def. If it is create shallow copy + * of the existing space and add field to its format. + */ +void +sql_create_column_start(struct Parse *parse); + +/** + * Emit code to update entry in _space and code to create + * constraints (entries in _index, _ck_constraint, _fk_constraint) + * described with this column. + */ +void +sql_create_column_end(struct Parse *parse); /** * This routine is called by the parser while in the middle of - * parsing a CREATE TABLE statement. A "NOT NULL" constraint has - * been seen on a column. This routine sets the is_nullable flag - * on the column currently under construction. - * If nullable_action has been already set, this function raises - * an error. + * parsing a or a + * statement. A "NOT NULL" constraint has been seen on a column. + * This routine sets the is_nullable flag on the column currently + * under construction. If nullable_action has been already set, + * this function raises an error. * * @param parser SQL Parser object. * @param nullable_action on_conflict_action value. diff --git a/test/box/error.result b/test/box/error.result index cdecdb221..21ad42f77 100644 --- a/test/box/error.result +++ b/test/box/error.result @@ -437,6 +437,7 @@ t; | 216: box.error.SYNC_QUORUM_TIMEOUT | 217: box.error.SYNC_ROLLBACK | 218: box.error.TUPLE_METADATA_IS_TOO_BIG + | 219: box.error.SQL_CANT_ADD_AUTOINC | ... test_run:cmd("setopt delimiter ''"); diff --git a/test/sql/add-column.result b/test/sql/add-column.result new file mode 100644 index 000000000..3edd8dccf --- /dev/null +++ b/test/sql/add-column.result @@ -0,0 +1,529 @@ +-- test-run result file version 2 +-- +-- gh-3075: Check statement. +-- +CREATE TABLE t1 (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... + +-- +-- COLUMN keyword is optional. Check it here, but omit it below. +-- +ALTER TABLE t1 ADD COLUMN b INT; + | --- + | - row_count: 1 + | ... + +-- +-- A column with the same name already exists. +-- +ALTER TABLE t1 ADD b SCALAR; + | --- + | - null + | - Space field 'B' is duplicate + | ... + +-- +-- Can't add column to a view. +-- +CREATE VIEW v AS SELECT * FROM t1; + | --- + | - row_count: 1 + | ... +ALTER TABLE v ADD c INT; + | --- + | - null + | - 'Can''t modify space ''V'': can not modify format or field_count of a view' + | ... + +\set language lua + | --- + | - true + | ... +view = box.space._space.index[2]:select('V')[1]:totable() + | --- + | ... +view_format = view[7] + | --- + | ... +f = {type = 'string', nullable_action = 'none', name = 'C', is_nullable = true} + | --- + | ... +table.insert(view_format, f) + | --- + | ... +view[5] = 3 + | --- + | ... +view[7] = view_format + | --- + | ... +box.space._space:replace(view) + | --- + | - error: 'Can''t modify space ''V'': can not modify format or field_count of a view' + | ... +\set language sql + | --- + | - true + | ... + +DROP VIEW v; + | --- + | - row_count: 1 + | ... + +-- +-- Check PRIMARY KEY constraint works with an added column. +-- +CREATE TABLE pk_check (a INT CONSTRAINT pk PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE pk_check DROP CONSTRAINT pk; + | --- + | - row_count: 1 + | ... +ALTER TABLE pk_check ADD b INT PRIMARY KEY; + | --- + | - row_count: 1 + | ... +INSERT INTO pk_check VALUES (1, 1); + | --- + | - row_count: 1 + | ... +INSERT INTO pk_check VALUES (1, 1); + | --- + | - null + | - Duplicate key exists in unique index 'pk_unnamed_PK_CHECK_1' in space 'PK_CHECK' + | ... +DROP TABLE pk_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check UNIQUE constraint works with an added column. +-- +CREATE TABLE unique_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE unique_check ADD b INT UNIQUE; + | --- + | - row_count: 1 + | ... +INSERT INTO unique_check VALUES (1, 1); + | --- + | - row_count: 1 + | ... +INSERT INTO unique_check VALUES (2, 1); + | --- + | - null + | - Duplicate key exists in unique index 'unique_unnamed_UNIQUE_CHECK_2' in space 'UNIQUE_CHECK' + | ... +DROP TABLE unique_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check CHECK constraint works with an added column. +-- +CREATE TABLE ck_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE ck_check ADD b INT CHECK (b > 0); + | --- + | - row_count: 1 + | ... +INSERT INTO ck_check VALUES (1, 0); + | --- + | - null + | - 'Check constraint failed ''ck_unnamed_CK_CHECK_1'': b > 0' + | ... +INSERT INTO ck_check VALUES (1, 1); + | --- + | - row_count: 1 + | ... +DROP TABLE ck_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check FOREIGN KEY constraint works with an added column. +-- +CREATE TABLE fk_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE fk_check ADD b INT REFERENCES t1(a); + | --- + | - row_count: 1 + | ... +INSERT INTO fk_check VALUES (0, 1); + | --- + | - null + | - 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +INSERT INTO fk_check VALUES (2, 0); + | --- + | - null + | - 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +INSERT INTO fk_check VALUES (2, 1); + | --- + | - null + | - 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +INSERT INTO t1 VALUES (1, 1); + | --- + | - row_count: 1 + | ... +INSERT INTO fk_check VALUES (2, 1); + | --- + | - row_count: 1 + | ... +DROP TABLE fk_check; + | --- + | - row_count: 1 + | ... +DROP TABLE t1; + | --- + | - row_count: 1 + | ... +-- +-- Check FOREIGN KEY (self-referenced) constraint works with an +-- added column. +-- +CREATE TABLE self (id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE) + | --- + | - row_count: 1 + | ... +ALTER TABLE self ADD b INT REFERENCES self(a) + | --- + | - row_count: 1 + | ... +INSERT INTO self(a,b) VALUES(1, 1); + | --- + | - autoincrement_ids: + | - 1 + | row_count: 1 + | ... +UPDATE self SET a = 2, b = 2; + | --- + | - row_count: 1 + | ... +UPDATE self SET b = 3; + | --- + | - null + | - 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +UPDATE self SET a = 3; + | --- + | - null + | - 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +DROP TABLE self; + | --- + | - row_count: 1 + | ... + +-- +-- Check AUTOINCREMENT works with an added column. +-- +CREATE TABLE autoinc_check (a INT CONSTRAINT pk PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE autoinc_check DROP CONSTRAINT pk; + | --- + | - row_count: 1 + | ... +ALTER TABLE autoinc_check ADD b INT PRIMARY KEY AUTOINCREMENT; + | --- + | - row_count: 1 + | ... +INSERT INTO autoinc_check(a) VALUES(1); + | --- + | - autoincrement_ids: + | - 1 + | row_count: 1 + | ... +INSERT INTO autoinc_check(a) VALUES(1); + | --- + | - autoincrement_ids: + | - 2 + | row_count: 1 + | ... +TRUNCATE TABLE autoinc_check; + | --- + | - row_count: 0 + | ... + +-- +-- Can't add second column with AUTOINCREMENT. +-- +ALTER TABLE autoinc_check ADD c INT AUTOINCREMENT; + | --- + | - null + | - 'Can''t add AUTOINCREMENT: the space ''AUTOINC_CHECK'' already has one auto incremented + | field' + | ... +DROP TABLE autoinc_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check COLLATE clause works with an added column. +-- +CREATE TABLE collate_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE collate_check ADD b TEXT COLLATE "unicode_ci"; + | --- + | - row_count: 1 + | ... +INSERT INTO collate_check VALUES (1, 'a'); + | --- + | - row_count: 1 + | ... +INSERT INTO collate_check VALUES (2, 'A'); + | --- + | - row_count: 1 + | ... +SELECT * FROM collate_check WHERE b LIKE 'a'; + | --- + | - metadata: + | - name: A + | type: integer + | - name: B + | type: string + | rows: + | - [1, 'a'] + | - [2, 'A'] + | ... +DROP TABLE collate_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check DEFAULT clause works with an added column. +-- +CREATE TABLE default_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE default_check ADD b TEXT DEFAULT ('a'); + | --- + | - row_count: 1 + | ... +INSERT INTO default_check(a) VALUES (1); + | --- + | - row_count: 1 + | ... +SELECT * FROM default_check; + | --- + | - metadata: + | - name: A + | type: integer + | - name: B + | type: string + | rows: + | - [1, 'a'] + | ... +DROP TABLE default_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check NULL constraint works with an added column. +-- +CREATE TABLE null_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE null_check ADD b TEXT NULL; + | --- + | - row_count: 1 + | ... +INSERT INTO null_check(a) VALUES (1); + | --- + | - row_count: 1 + | ... +SELECT * FROM null_check; + | --- + | - metadata: + | - name: A + | type: integer + | - name: B + | type: string + | rows: + | - [1, null] + | ... +DROP TABLE null_check; + | --- + | - row_count: 1 + | ... + +-- +-- Check NOT NULL constraint works with an added column. +-- +CREATE TABLE notnull_check (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +ALTER TABLE notnull_check ADD b TEXT NOT NULL; + | --- + | - row_count: 1 + | ... +INSERT INTO notnull_check(a) VALUES (1); + | --- + | - null + | - 'Failed to execute SQL statement: NOT NULL constraint failed: NOTNULL_CHECK.B' + | ... +INSERT INTO notnull_check VALUES (1, 'not null'); + | --- + | - row_count: 1 + | ... +DROP TABLE notnull_check; + | --- + | - row_count: 1 + | ... + +-- +-- Can't add a column with DEAFULT or NULL to a non-empty space. +-- This ability isn't implemented yet. +-- +CREATE TABLE non_empty (a INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +INSERT INTO non_empty VALUES (1); + | --- + | - row_count: 1 + | ... +ALTER TABLE non_empty ADD b INT NULL; + | --- + | - null + | - Tuple field count 1 does not match space field count 2 + | ... +ALTER TABLE non_empty ADD b INT DEFAULT (1); + | --- + | - null + | - Tuple field count 1 does not match space field count 2 + | ... +DROP TABLE non_empty; + | --- + | - row_count: 1 + | ... + +-- +-- Add to a no-SQL adjusted space without format. +-- +\set language lua + | --- + | - true + | ... +_ = box.schema.space.create('WITHOUT_FORMAT') + | --- + | ... +\set language sql + | --- + | - true + | ... +ALTER TABLE without_format ADD a INT PRIMARY KEY; + | --- + | - row_count: 1 + | ... +INSERT INTO without_format VALUES (1); + | --- + | - row_count: 1 + | ... +DROP TABLE without_format; + | --- + | - row_count: 1 + | ... + +-- +-- Add to a no-SQL adjusted space with format. +-- +\set language lua + | --- + | - true + | ... +with_format = box.schema.space.create('WITH_FORMAT') + | --- + | ... +with_format:format{{name = 'A', type = 'unsigned'}} + | --- + | ... +\set language sql + | --- + | - true + | ... +ALTER TABLE with_format ADD b INT PRIMARY KEY; + | --- + | - row_count: 1 + | ... +INSERT INTO with_format VALUES (1, 1); + | --- + | - row_count: 1 + | ... +DROP TABLE with_format; + | --- + | - row_count: 1 + | ... + +-- +-- Add multiple columns (with a constraint) inside a transaction. +-- +CREATE TABLE t2 (a INT PRIMARY KEY) + | --- + | - row_count: 1 + | ... +\set language lua + | --- + | - true + | ... +box.begin() \ +box.execute('ALTER TABLE t2 ADD b INT') \ +box.execute('ALTER TABLE t2 ADD c INT UNIQUE') \ +box.commit() + | --- + | ... +\set language sql + | --- + | - true + | ... +INSERT INTO t2 VALUES (1, 1, 1); + | --- + | - row_count: 1 + | ... +INSERT INTO t2 VALUES (2, 1, 1); + | --- + | - null + | - Duplicate key exists in unique index 'unique_unnamed_T2_2' in space 'T2' + | ... +SELECT * FROM t2; + | --- + | - metadata: + | - name: A + | type: integer + | - name: B + | type: integer + | - name: C + | type: integer + | rows: + | - [1, 1, 1] + | ... +DROP TABLE t2; + | --- + | - row_count: 1 + | ... diff --git a/test/sql/add-column.test.sql b/test/sql/add-column.test.sql new file mode 100644 index 000000000..430c7c46a --- /dev/null +++ b/test/sql/add-column.test.sql @@ -0,0 +1,183 @@ +-- +-- gh-3075: Check statement. +-- +CREATE TABLE t1 (a INT PRIMARY KEY); + +-- +-- COLUMN keyword is optional. Check it here, but omit it below. +-- +ALTER TABLE t1 ADD COLUMN b INT; + +-- +-- A column with the same name already exists. +-- +ALTER TABLE t1 ADD b SCALAR; + +-- +-- Can't add column to a view. +-- +CREATE VIEW v AS SELECT * FROM t1; +ALTER TABLE v ADD c INT; + +\set language lua +view = box.space._space.index[2]:select('V')[1]:totable() +view_format = view[7] +f = {type = 'string', nullable_action = 'none', name = 'C', is_nullable = true} +table.insert(view_format, f) +view[5] = 3 +view[7] = view_format +box.space._space:replace(view) +\set language sql + +DROP VIEW v; + +-- +-- Check PRIMARY KEY constraint works with an added column. +-- +CREATE TABLE pk_check (a INT CONSTRAINT pk PRIMARY KEY); +ALTER TABLE pk_check DROP CONSTRAINT pk; +ALTER TABLE pk_check ADD b INT PRIMARY KEY; +INSERT INTO pk_check VALUES (1, 1); +INSERT INTO pk_check VALUES (1, 1); +DROP TABLE pk_check; + +-- +-- Check UNIQUE constraint works with an added column. +-- +CREATE TABLE unique_check (a INT PRIMARY KEY); +ALTER TABLE unique_check ADD b INT UNIQUE; +INSERT INTO unique_check VALUES (1, 1); +INSERT INTO unique_check VALUES (2, 1); +DROP TABLE unique_check; + +-- +-- Check CHECK constraint works with an added column. +-- +CREATE TABLE ck_check (a INT PRIMARY KEY); +ALTER TABLE ck_check ADD b INT CHECK (b > 0); +INSERT INTO ck_check VALUES (1, 0); +INSERT INTO ck_check VALUES (1, 1); +DROP TABLE ck_check; + +-- +-- Check FOREIGN KEY constraint works with an added column. +-- +CREATE TABLE fk_check (a INT PRIMARY KEY); +ALTER TABLE fk_check ADD b INT REFERENCES t1(a); +INSERT INTO fk_check VALUES (0, 1); +INSERT INTO fk_check VALUES (2, 0); +INSERT INTO fk_check VALUES (2, 1); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO fk_check VALUES (2, 1); +DROP TABLE fk_check; +DROP TABLE t1; +-- +-- Check FOREIGN KEY (self-referenced) constraint works with an +-- added column. +-- +CREATE TABLE self (id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE) +ALTER TABLE self ADD b INT REFERENCES self(a) +INSERT INTO self(a,b) VALUES(1, 1); +UPDATE self SET a = 2, b = 2; +UPDATE self SET b = 3; +UPDATE self SET a = 3; +DROP TABLE self; + +-- +-- Check AUTOINCREMENT works with an added column. +-- +CREATE TABLE autoinc_check (a INT CONSTRAINT pk PRIMARY KEY); +ALTER TABLE autoinc_check DROP CONSTRAINT pk; +ALTER TABLE autoinc_check ADD b INT PRIMARY KEY AUTOINCREMENT; +INSERT INTO autoinc_check(a) VALUES(1); +INSERT INTO autoinc_check(a) VALUES(1); +TRUNCATE TABLE autoinc_check; + +-- +-- Can't add second column with AUTOINCREMENT. +-- +ALTER TABLE autoinc_check ADD c INT AUTOINCREMENT; +DROP TABLE autoinc_check; + +-- +-- Check COLLATE clause works with an added column. +-- +CREATE TABLE collate_check (a INT PRIMARY KEY); +ALTER TABLE collate_check ADD b TEXT COLLATE "unicode_ci"; +INSERT INTO collate_check VALUES (1, 'a'); +INSERT INTO collate_check VALUES (2, 'A'); +SELECT * FROM collate_check WHERE b LIKE 'a'; +DROP TABLE collate_check; + +-- +-- Check DEFAULT clause works with an added column. +-- +CREATE TABLE default_check (a INT PRIMARY KEY); +ALTER TABLE default_check ADD b TEXT DEFAULT ('a'); +INSERT INTO default_check(a) VALUES (1); +SELECT * FROM default_check; +DROP TABLE default_check; + +-- +-- Check NULL constraint works with an added column. +-- +CREATE TABLE null_check (a INT PRIMARY KEY); +ALTER TABLE null_check ADD b TEXT NULL; +INSERT INTO null_check(a) VALUES (1); +SELECT * FROM null_check; +DROP TABLE null_check; + +-- +-- Check NOT NULL constraint works with an added column. +-- +CREATE TABLE notnull_check (a INT PRIMARY KEY); +ALTER TABLE notnull_check ADD b TEXT NOT NULL; +INSERT INTO notnull_check(a) VALUES (1); +INSERT INTO notnull_check VALUES (1, 'not null'); +DROP TABLE notnull_check; + +-- +-- Can't add a column with DEAFULT or NULL to a non-empty space. +-- This ability isn't implemented yet. +-- +CREATE TABLE non_empty (a INT PRIMARY KEY); +INSERT INTO non_empty VALUES (1); +ALTER TABLE non_empty ADD b INT NULL; +ALTER TABLE non_empty ADD b INT DEFAULT (1); +DROP TABLE non_empty; + +-- +-- Add to a no-SQL adjusted space without format. +-- +\set language lua +_ = box.schema.space.create('WITHOUT_FORMAT') +\set language sql +ALTER TABLE without_format ADD a INT PRIMARY KEY; +INSERT INTO without_format VALUES (1); +DROP TABLE without_format; + +-- +-- Add to a no-SQL adjusted space with format. +-- +\set language lua +with_format = box.schema.space.create('WITH_FORMAT') +with_format:format{{name = 'A', type = 'unsigned'}} +\set language sql +ALTER TABLE with_format ADD b INT PRIMARY KEY; +INSERT INTO with_format VALUES (1, 1); +DROP TABLE with_format; + +-- +-- Add multiple columns (with a constraint) inside a transaction. +-- +CREATE TABLE t2 (a INT PRIMARY KEY) +\set language lua +box.begin() \ +box.execute('ALTER TABLE t2 ADD b INT') \ +box.execute('ALTER TABLE t2 ADD c INT UNIQUE') \ +box.commit() +\set language sql +INSERT INTO t2 VALUES (1, 1, 1); +INSERT INTO t2 VALUES (2, 1, 1); +SELECT * FROM t2; +DROP TABLE t2; diff --git a/test/sql/checks.result b/test/sql/checks.result index 7b18e5d6b..3ddd52d42 100644 --- a/test/sql/checks.result +++ b/test/sql/checks.result @@ -856,6 +856,26 @@ box.execute("DROP TABLE t6") --- - row_count: 1 ... +-- +-- gh-3075: Check the auto naming of CHECK constraints in +-- . +-- +box.execute("CREATE TABLE check_naming (a INT PRIMARY KEY CHECK (a > 0))") +--- +- row_count: 1 +... +box.execute("ALTER TABLE check_naming ADD b INT CHECK (b > 0)") +--- +- row_count: 1 +... +box.execute("ALTER TABLE check_naming DROP CONSTRAINT \"ck_unnamed_CHECK_NAMING_2\"") +--- +- row_count: 1 +... +box.execute("DROP TABLE check_naming") +--- +- row_count: 1 +... test_run:cmd("clear filter") --- - true diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua index 301f8ea69..a79131466 100644 --- a/test/sql/checks.test.lua +++ b/test/sql/checks.test.lua @@ -280,4 +280,13 @@ box.func.MYFUNC:drop() box.execute("INSERT INTO t6 VALUES(11);"); box.execute("DROP TABLE t6") +-- +-- gh-3075: Check the auto naming of CHECK constraints in +-- . +-- +box.execute("CREATE TABLE check_naming (a INT PRIMARY KEY CHECK (a > 0))") +box.execute("ALTER TABLE check_naming ADD b INT CHECK (b > 0)") +box.execute("ALTER TABLE check_naming DROP CONSTRAINT \"ck_unnamed_CHECK_NAMING_2\"") +box.execute("DROP TABLE check_naming") + test_run:cmd("clear filter") diff --git a/test/sql/foreign-keys.result b/test/sql/foreign-keys.result index 33689a06e..9ba995579 100644 --- a/test/sql/foreign-keys.result +++ b/test/sql/foreign-keys.result @@ -499,5 +499,33 @@ box.space.S:drop() box.space.T:drop() --- ... +-- +-- gh-3075: Check the auto naming of FOREIGN KEY constraints in +-- . +-- +box.execute("CREATE TABLE t1 (a INT PRIMARY KEY)") +--- +- row_count: 1 +... +box.execute("CREATE TABLE check_naming (a INT PRIMARY KEY REFERENCES t1(a))") +--- +- row_count: 1 +... +box.execute("ALTER TABLE check_naming ADD b INT REFERENCES t1(a)") +--- +- row_count: 1 +... +box.execute("ALTER TABLE check_naming DROP CONSTRAINT \"fk_unnamed_CHECK_NAMING_2\"") +--- +- row_count: 1 +... +box.execute("DROP TABLE check_naming") +--- +- row_count: 1 +... +box.execute("DROP TABLE t1") +--- +- row_count: 1 +... --- Clean-up SQL DD hash. -test_run:cmd('restart server default with cleanup=1') diff --git a/test/sql/foreign-keys.test.lua b/test/sql/foreign-keys.test.lua index d2dd88d28..29918c5d4 100644 --- a/test/sql/foreign-keys.test.lua +++ b/test/sql/foreign-keys.test.lua @@ -209,5 +209,16 @@ box.space.T:select() box.space.S:drop() box.space.T:drop() +-- +-- gh-3075: Check the auto naming of FOREIGN KEY constraints in +-- . +-- +box.execute("CREATE TABLE t1 (a INT PRIMARY KEY)") +box.execute("CREATE TABLE check_naming (a INT PRIMARY KEY REFERENCES t1(a))") +box.execute("ALTER TABLE check_naming ADD b INT REFERENCES t1(a)") +box.execute("ALTER TABLE check_naming DROP CONSTRAINT \"fk_unnamed_CHECK_NAMING_2\"") +box.execute("DROP TABLE check_naming") +box.execute("DROP TABLE t1") + --- Clean-up SQL DD hash. -test_run:cmd('restart server default with cleanup=1') -- 2.24.3 (Apple Git-128)