From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4D331211DC for ; Sat, 9 Jun 2018 05:36:12 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id oSuGaHcDL__y for ; Sat, 9 Jun 2018 05:36:11 -0400 (EDT) Received: from fallback.mail.ru (fallback6.mail.ru [94.100.181.147]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 241422108F for ; Sat, 9 Jun 2018 05:36:11 -0400 (EDT) Received: from [10.161.100.15] (port=38318 helo=smtpng3.m.smailru.net) by fallback6.mail.ru with esmtp (envelope-from ) id 1fRaEW-0003Qw-Nr for tarantool-patches@freelists.org; Sat, 09 Jun 2018 12:32:29 +0300 From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v2 10/11] sql: move Triggers to server Date: Sat, 9 Jun 2018 12:32:13 +0300 Message-Id: In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Kirill Shcherbatov Introduced sql_triggers field in space structure. Changed parser logic to do not insert built triggers, just only to do parsing. All triggers insertions and deletions are operated via on_replace_dd_trigger now. Resolves #3273. --- src/box/alter.cc | 124 +++++++++++++++ src/box/errcode.h | 2 +- src/box/lua/schema.lua | 6 + src/box/space.c | 5 + src/box/space.h | 2 + src/box/sql.c | 39 ----- src/box/sql.h | 50 ++++++ src/box/sql/build.c | 8 +- src/box/sql/fkey.c | 2 - src/box/sql/insert.c | 6 +- src/box/sql/sqliteInt.h | 5 - src/box/sql/tokenize.c | 29 +++- src/box/sql/trigger.c | 281 +++++++++++++++++----------------- src/box/sql/vdbe.c | 76 ++------- src/box/sql/vdbe.h | 1 - src/box/sql/vdbeaux.c | 9 -- test/box/misc.result | 1 + test/sql-tap/identifier_case.test.lua | 4 +- test/sql-tap/trigger1.test.lua | 14 +- test/sql/triggers.result | 260 +++++++++++++++++++++++++++++++ test/sql/triggers.test.lua | 94 ++++++++++++ 21 files changed, 737 insertions(+), 281 deletions(-) create mode 100644 test/sql/triggers.result create mode 100644 test/sql/triggers.test.lua diff --git a/src/box/alter.cc b/src/box/alter.cc index f2bf85d..c683a51 100644 --- a/src/box/alter.cc +++ b/src/box/alter.cc @@ -551,6 +551,10 @@ space_swap_triggers(struct space *new_space, struct space *old_space) rlist_swap(&new_space->before_replace, &old_space->before_replace); rlist_swap(&new_space->on_replace, &old_space->on_replace); rlist_swap(&new_space->on_stmt_begin, &old_space->on_stmt_begin); + /** Copy SQL Triggers pointer. */ + struct Trigger *old_value = new_space->sql_triggers; + new_space->sql_triggers = old_space->sql_triggers; + old_space->sql_triggers = old_value; } /** @@ -3091,6 +3095,49 @@ lock_before_dd(struct trigger *trigger, void *event) latch_lock(&schema_lock); } +static void +triggers_task_rollback(struct trigger *trigger, void *event) +{ + struct txn_stmt *stmt = txn_last_stmt((struct txn*) event); + struct Trigger *old_trigger = (struct Trigger *)trigger->data; + struct Trigger *new_trigger; + + if (stmt->old_tuple != NULL && stmt->new_tuple == NULL) { + /* DELETE trigger. */ + if (sql_trigger_replace(sql_get(), + sql_trigger_name(old_trigger), + old_trigger, &new_trigger) != 0) + panic("Out of memory on insertion into trigger hash"); + assert(new_trigger == NULL); + } else if (stmt->new_tuple != NULL && stmt->old_tuple == NULL) { + /* INSERT trigger. */ + int rc = sql_trigger_replace(sql_get(), + sql_trigger_name(old_trigger), + NULL, &new_trigger); + (void)rc; + assert(rc == 0); + assert(new_trigger == old_trigger); + sql_trigger_delete(sql_get(), new_trigger); + } else { + /* REPLACE trigger. */ + if (sql_trigger_replace(sql_get(), + sql_trigger_name(old_trigger), + old_trigger, &new_trigger) != 0) + panic("Out of memory on insertion into trigger hash"); + assert(old_trigger != new_trigger); + + sql_trigger_delete(sql_get(), new_trigger); + } +} + +static void +triggers_task_commit(struct trigger *trigger, void * /* event */) +{ + struct Trigger *old_trigger = (struct Trigger *)trigger->data; + /* DELETE, REPLACE trigger. */ + sql_trigger_delete(sql_get(), old_trigger); +} + /** * A trigger invoked on replace in a space containing * SQL triggers. @@ -3100,6 +3147,83 @@ on_replace_dd_trigger(struct trigger * /* trigger */, void *event) { struct txn *txn = (struct txn *) event; txn_check_singlestatement_xc(txn, "Space _trigger"); + struct txn_stmt *stmt = txn_current_stmt(txn); + struct tuple *old_tuple = stmt->old_tuple; + struct tuple *new_tuple = stmt->new_tuple; + + struct trigger *on_rollback = + txn_alter_trigger_new(triggers_task_rollback, NULL); + struct trigger *on_commit = + txn_alter_trigger_new(triggers_task_commit, NULL); + + if (old_tuple != NULL && new_tuple == NULL) { + /* DROP trigger. */ + uint32_t trigger_name_len; + const char *trigger_name_src = + tuple_field_str_xc(old_tuple, 0, &trigger_name_len); + char *trigger_name = + (char *)region_alloc_xc(&fiber()->gc, + trigger_name_len + 1); + memcpy(trigger_name, trigger_name_src, trigger_name_len); + trigger_name[trigger_name_len] = 0; + + struct Trigger *old_trigger; + int rc = sql_trigger_replace(sql_get(), trigger_name, NULL, + &old_trigger); + (void)rc; + assert(rc == 0); + assert(old_trigger != NULL); + + on_commit->data = old_trigger; + on_rollback->data = old_trigger; + } else { + /* INSERT, REPLACE trigger. */ + uint32_t trigger_name_len; + const char *trigger_name_src = + tuple_field_str_xc(new_tuple, 0, &trigger_name_len); + + const char *space_opts = + tuple_field_with_type_xc(new_tuple, 2, MP_MAP); + struct space_opts opts; + struct region *region = &fiber()->gc; + space_opts_decode(&opts, space_opts, region); + struct Trigger *new_trigger = + sql_trigger_compile(sql_get(), opts.sql); + if (new_trigger == NULL) + diag_raise(); + + const char *trigger_name = sql_trigger_name(new_trigger); + if (strncmp(trigger_name_src, trigger_name, + trigger_name_len) != 0) { + sql_trigger_delete(sql_get(), new_trigger); + tnt_raise(ClientError, ER_SQL, + "tuple trigger name does not match extracted " + "from SQL"); + } + uint32_t space_id = tuple_field_u32_xc(new_tuple, 1); + if (space_id != sql_trigger_space_id(new_trigger)) { + sql_trigger_delete(sql_get(), new_trigger); + tnt_raise(ClientError, ER_SQL, + "tuple space_id does not match the value " + "resolved on AST building from SQL"); + } + + struct Trigger *old_trigger; + if (sql_trigger_replace(sql_get(), trigger_name, new_trigger, + &old_trigger) != 0) { + sql_trigger_delete(sql_get(), new_trigger); + diag_raise(); + } + + bool is_insert = (new_tuple != NULL && old_tuple == NULL); + assert(!is_insert || old_trigger == NULL); + + on_commit->data = is_insert ? NULL : old_trigger; + on_rollback->data = new_trigger; + } + + txn_on_rollback(txn, on_rollback); + txn_on_commit(txn, on_commit); } struct trigger alter_space_on_replace_space = { diff --git a/src/box/errcode.h b/src/box/errcode.h index ba52880..eb05936 100644 --- a/src/box/errcode.h +++ b/src/box/errcode.h @@ -107,7 +107,7 @@ struct errcode_record { /* 52 */_(ER_FUNCTION_EXISTS, "Function '%s' already exists") \ /* 53 */_(ER_BEFORE_REPLACE_RET, "Invalid return value of space:before_replace trigger: expected tuple or nil, got %s") \ /* 54 */_(ER_FUNCTION_MAX, "A limit on the total number of functions has been reached: %u") \ - /* 55 */_(ER_UNUSED4, "") \ + /* 55 */_(ER_TRIGGER_EXISTS, "Trigger '%s' already exists") \ /* 56 */_(ER_USER_MAX, "A limit on the total number of users has been reached: %u") \ /* 57 */_(ER_NO_SUCH_ENGINE, "Space engine '%s' does not exist") \ /* 58 */_(ER_RELOAD_CFG, "Can't set option '%s' dynamically") \ diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua index dd5ce0a..4996565 100644 --- a/src/box/lua/schema.lua +++ b/src/box/lua/schema.lua @@ -463,6 +463,7 @@ box.schema.space.drop = function(space_id, space_name, opts) check_param_table(opts, { if_exists = 'boolean' }) local _space = box.space[box.schema.SPACE_ID] local _index = box.space[box.schema.INDEX_ID] + local _trigger = box.space[box.schema.TRIGGER_ID] local _vindex = box.space[box.schema.VINDEX_ID] local _truncate = box.space[box.schema.TRUNCATE_ID] local _space_sequence = box.space[box.schema.SPACE_SEQUENCE_ID] @@ -471,6 +472,11 @@ box.schema.space.drop = function(space_id, space_name, opts) -- Delete automatically generated sequence. box.schema.sequence.drop(sequence_tuple[2]) end + local triggers = _trigger.index["space_id"]:select({space_id}) + for i = #triggers, 1, -1 do + local v = triggers[i] + _trigger:delete{v[1]} + end local keys = _vindex:select(space_id) for i = #keys, 1, -1 do local v = keys[i] diff --git a/src/box/space.c b/src/box/space.c index b370b7c..d2aeecf 100644 --- a/src/box/space.c +++ b/src/box/space.c @@ -209,6 +209,11 @@ space_delete(struct space *space) trigger_destroy(&space->on_replace); trigger_destroy(&space->on_stmt_begin); space_def_delete(space->def); + /* + * SQL Triggers should be deleted with on_replace_dd_trigger + * initiated in LUA schema:delete. + */ + assert(space->sql_triggers == NULL); space->vtab->destroy(space); } diff --git a/src/box/space.h b/src/box/space.h index b8d29ca..0413cd0 100644 --- a/src/box/space.h +++ b/src/box/space.h @@ -146,6 +146,8 @@ struct space { struct rlist on_replace; /** Triggers fired before space statement */ struct rlist on_stmt_begin; + /** SQL Trigger list. */ + struct Trigger *sql_triggers; /** * The number of *enabled* indexes in the space. * diff --git a/src/box/sql.c b/src/box/sql.c index 0ba0346..57211fd 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -1227,9 +1227,6 @@ space_foreach_put_cb(struct space *space, void *udata) /* Load database schema from Tarantool. */ void tarantoolSqlite3LoadSchema(InitData *init) { - box_iterator_t *it; - box_tuple_t *tuple; - sql_schema_put( init, TARANTOOL_SYS_SCHEMA_NAME, BOX_SCHEMA_ID, 0, @@ -1298,42 +1295,6 @@ void tarantoolSqlite3LoadSchema(InitData *init) init->rc = SQL_TARANTOOL_ERROR; return; } - - /* Read _trigger */ - it = box_index_iterator(BOX_TRIGGER_ID, 0, ITER_GE, - nil_key, nil_key + sizeof(nil_key)); - - if (it == NULL) { - init->rc = SQL_TARANTOOL_ITERATOR_FAIL; - return; - } - - while (box_iterator_next(it, &tuple) == 0 && tuple != NULL) { - const char *field, *ptr; - char *name, *sql; - unsigned len; - assert(tuple_field_count(tuple) == 3); - - field = tuple_field(tuple, 0); - assert (field != NULL); - ptr = mp_decode_str(&field, &len); - name = strndup(ptr, len); - - field = tuple_field(tuple, 2); - assert (field != NULL); - mp_decode_array(&field); - ptr = mp_decode_str(&field, &len); - assert (strncmp(ptr, "sql", 3) == 0); - - ptr = mp_decode_str(&field, &len); - sql = strndup(ptr, len); - - sql_schema_put(init, name, 0, 0, sql); - - free(name); - free(sql); - } - box_iterator_free(it); } /********************************************************************* diff --git a/src/box/sql.h b/src/box/sql.h index 35aacc3..51e42ab 100644 --- a/src/box/sql.h +++ b/src/box/sql.h @@ -84,6 +84,17 @@ struct Expr * sql_expr_compile(struct sqlite3 *db, const char *expr, int expr_len); /** + * Perform parsing of provided SQL request and construct trigger AST. + * @param db SQL context handle. + * @param sql request to parse. + * + * @retval NULL on error + * @retval not NULL Trigger AST pointer on success. + */ +struct Trigger * +sql_trigger_compile(struct sqlite3 *db, const char *sql); + +/** * Free AST pointed by trigger. * @param db SQL handle. * @param trigger AST object. @@ -92,6 +103,45 @@ void sql_trigger_delete(struct sqlite3 *db, struct Trigger *trigger); /** + * Get server triggers list by space_id. + * @param space_id Space ID. + * + * @retval trigger AST list. + */ +struct Trigger * +space_trigger_list(uint32_t space_id); + +/** + * Perform replace trigger in SQL internals with new AST object. + * @param db SQL handle. + * @param name a name of the trigger. + * @param trigger AST object to insert. + * @param[out] old trigger object from if exists. + * + * @retval 0 on success. + * @retval -1 on error. + */ +int +sql_trigger_replace(struct sqlite3 *db, const char *name, + struct Trigger *trigger, struct Trigger **old_trigger); + +/** + * Get trigger name by trigger AST object. + * @param trigger AST object. + * @return trigger name string. + */ +const char * +sql_trigger_name(struct Trigger *trigger); + +/** + * Get space_id of the space that trigger has been built for. + * @param trigger AST object. + * @return space identifier. + */ +uint32_t +sql_trigger_space_id(struct Trigger *trigger); + +/** * Store duplicate of a parsed expression into @a parser. * @param parser Parser context. * @param select Select to extract from. diff --git a/src/box/sql/build.c b/src/box/sql/build.c index 3c3c900..9c1b203 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -2285,16 +2285,14 @@ sql_code_drop_table(struct Parse *parse_context, struct space *space, /* * Drop all triggers associated with the table being * dropped. Code is generated to remove entries from - * _trigger. OP_DropTrigger will remove it from internal - * SQL structures. + * _trigger. on_replace_dd_trigger will remove it from + * internal SQL structures. * * Do not account triggers deletion - they will be * accounted in DELETE from _space below. */ parse_context->nested++; - Table *table = sqlite3HashFind(&parse_context->db->pSchema->tblHash, - space->def->name); - struct Trigger *trigger = table->pTrigger; + struct Trigger *trigger = space->sql_triggers; while (trigger != NULL) { sqlite3DropTriggerPtr(parse_context, trigger); trigger = trigger->pNext; diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c index 70ebef8..5fcf6a5 100644 --- a/src/box/sql/fkey.c +++ b/src/box/sql/fkey.c @@ -1439,8 +1439,6 @@ fkActionTrigger(Parse * pParse, /* Parse context */ pStep->op = TK_UPDATE; } pStep->pTrig = pTrigger; - pTrigger->pSchema = pTab->pSchema; - pTrigger->pTabSchema = pTab->pSchema; pFKey->apTrigger[iAction] = pTrigger; pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE); } diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index 59c61c7..023e6b0 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -1766,9 +1766,9 @@ xferOptimization(Parse * pParse, /* Parser context */ */ return 0; } - if (pDest->pTrigger) { - return 0; /* tab1 must not have triggers */ - } + /* The pDest must not have triggers. */ + if (space_trigger_list(pDest->def->id) != NULL) + return 0; if (onError == ON_CONFLICT_ACTION_DEFAULT) { if (pDest->iPKey >= 0) onError = pDest->keyConf; diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h index 6ca59c4..276f881 100644 --- a/src/box/sql/sqliteInt.h +++ b/src/box/sql/sqliteInt.h @@ -1935,7 +1935,6 @@ struct Table { #ifndef SQLITE_OMIT_ALTERTABLE int addColOffset; /* Offset in CREATE TABLE stmt to add a new column */ #endif - Trigger *pTrigger; /* List of triggers stored in pSchema */ Schema *pSchema; /* Schema that contains this table */ Table *pNextZombie; /* Next on the Parse.pZombieTab list */ /** Space definition with Tarantool metadata. */ @@ -3034,14 +3033,11 @@ struct Trigger { char *zName; /* The name of the trigger */ /** The ID of space the trigger is refer to. */ uint32_t space_id; - char *table; /* The table or view to which the trigger applies */ u8 op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT */ u8 tr_tm; /* One of TRIGGER_BEFORE, TRIGGER_AFTER */ Expr *pWhen; /* The WHEN clause of the expression (may be NULL) */ IdList *pColumns; /* If this is an UPDATE OF trigger, the is stored here */ - Schema *pSchema; /* Schema containing the trigger */ - Schema *pTabSchema; /* Schema containing the table */ TriggerStep *step_list; /* Link list of trigger program steps */ Trigger *pNext; /* Next trigger associated with the table */ }; @@ -4029,7 +4025,6 @@ TriggerStep *sqlite3TriggerInsertStep(sqlite3 *, Token *, IdList *, TriggerStep *sqlite3TriggerUpdateStep(sqlite3 *, Token *, ExprList *, Expr *, u8); TriggerStep *sqlite3TriggerDeleteStep(sqlite3 *, Token *, Expr *); -void sqlite3UnlinkAndDeleteTrigger(sqlite3 *, const char *); u32 sqlite3TriggerColmask(Parse *, Trigger *, ExprList *, int, int, Table *, int); #define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p)) diff --git a/src/box/sql/tokenize.c b/src/box/sql/tokenize.c index 3f59fc8..71a74d6 100644 --- a/src/box/sql/tokenize.c +++ b/src/box/sql/tokenize.c @@ -42,7 +42,6 @@ #include "say.h" #include "sqliteInt.h" -#include "tarantoolInt.h" /* Character classes for tokenizing * @@ -123,6 +122,7 @@ static const char sql_ascii_class[] = { * the #include below. */ #include "keywordhash.h" +#include "tarantoolInt.h" #define maybe_utf8(c) ((sqlite3CtypeMap[c] & 0x40) != 0) @@ -534,7 +534,12 @@ sqlite3RunParser(Parse * pParse, const char *zSql, char **pzErrMsg) if (pParse->pWithToFree) sqlite3WithDelete(db, pParse->pWithToFree); - sql_trigger_delete(db, pParse->pNewTrigger); + /* + * Trigger is exported with pNewTrigger field when + * parse_only flag is set. + */ + if (!pParse->parse_only) + sql_trigger_delete(db, pParse->pNewTrigger); sqlite3DbFree(db, pParse->pVList); while (pParse->pZombieTab) { Table *p = pParse->pZombieTab; @@ -575,3 +580,23 @@ sql_expr_compile(sqlite3 *db, const char *expr, int expr_len) sql_parser_destroy(&parser); return expression; } + +struct Trigger * +sql_trigger_compile(struct sqlite3 *db, const char *sql) +{ + struct Parse parser; + sql_parser_create(&parser, db); + parser.parse_only = true; + char *sql_error; + struct Trigger *trigger = NULL; + if (sqlite3RunParser(&parser, sql, &sql_error) != SQLITE_OK) { + char *error = tt_static_buf(); + snprintf(error, TT_STATIC_BUF_LEN, "%s", sql_error); + diag_set(ClientError, ER_SQL, error); + sqlite3DbFree(db, sql_error); + } else { + trigger = parser.pNewTrigger; + } + sql_parser_destroy(&parser); + return trigger; +} diff --git a/src/box/sql/trigger.c b/src/box/sql/trigger.c index a9c686f..1b569bc 100644 --- a/src/box/sql/trigger.c +++ b/src/box/sql/trigger.c @@ -33,6 +33,7 @@ * This file contains the implementation for TRIGGERs */ +#include "box/schema.h" #include "sqliteInt.h" #include "tarantoolInt.h" #include "vdbeInt.h" @@ -81,104 +82,128 @@ sqlite3BeginTrigger(Parse * pParse, /* The parse context of the CREATE TRIGGER s ) { Trigger *pTrigger = 0; /* The new trigger */ - Table *pTab; /* Table that the trigger fires off of */ char *zName = 0; /* Name of the trigger */ sqlite3 *db = pParse->db; /* The database connection */ DbFixer sFix; /* State vector for the DB fixer */ - /* Do not account nested operations: the count of such - * operations depends on Tarantool data dictionary internals, - * such as data layout in system spaces. + /* + * Do not account nested operations: the count of such + * operations depends on Tarantool data dictionary + * internals, such as data layout in system spaces. */ if (!pParse->nested) { Vdbe *v = sqlite3GetVdbe(pParse); if (v != NULL) sqlite3VdbeCountChanges(v); } - assert(pName != 0); /* pName->z might be NULL, but not pName itself */ + /* pName->z might be NULL, but not pName itself. */ + assert(pName != NULL); assert(op == TK_INSERT || op == TK_UPDATE || op == TK_DELETE); assert(op > 0 && op < 0xff); - if (!pTableName || db->mallocFailed) { + if (pTableName == NULL || db->mallocFailed) goto trigger_cleanup; - } - /* Ensure the table name matches database name and that the table exists */ + /* + * Ensure the table name matches database name and that + * the table exists. + */ if (db->mallocFailed) goto trigger_cleanup; assert(pTableName->nSrc == 1); sqlite3FixInit(&sFix, pParse, "trigger", pName); - if (sqlite3FixSrcList(&sFix, pTableName)) { + if (sqlite3FixSrcList(&sFix, pTableName) != 0) goto trigger_cleanup; - } - pTab = sql_list_lookup_table(pParse, pTableName); - if (!pTab) { - goto trigger_cleanup; - } - /* Check that the trigger name is not reserved and that no trigger of the - * specified name exists - */ zName = sqlite3NameFromToken(db, pName); - if (!zName || SQLITE_OK != sqlite3CheckIdentifierName(pParse, zName)) { + if (zName == NULL) goto trigger_cleanup; - } - if (sqlite3HashFind(&(db->pSchema->trigHash), zName)) { + + if (sqlite3CheckIdentifierName(pParse, zName) != SQLITE_OK) + goto trigger_cleanup; + + if (!pParse->parse_only && + sqlite3HashFind(&db->pSchema->trigHash, zName) != NULL) { if (!noErr) { - sqlite3ErrorMsg(pParse, "trigger %s already exists", - zName); + diag_set(ClientError, ER_TRIGGER_EXISTS, zName); + pParse->rc = SQL_TARANTOOL_ERROR; } else { assert(!db->init.busy); } goto trigger_cleanup; } - /* Do not create a trigger on a system table */ - if (sqlite3StrNICmp(pTab->def->name, "sqlite_", 7) == 0) { - sqlite3ErrorMsg(pParse, - "cannot create trigger on system table"); + const char *table_name = pTableName->a[0].zName; + uint32_t space_id; + if (schema_find_id(BOX_SPACE_ID, 2, table_name, strlen(table_name), + &space_id) != 0) { + pParse->rc = SQL_TARANTOOL_ERROR; + goto trigger_cleanup; + } + if (space_id == BOX_ID_NIL) { + diag_set(ClientError, ER_NO_SUCH_SPACE, table_name); + pParse->rc = SQL_TARANTOOL_ERROR; goto trigger_cleanup; } - /* INSTEAD of triggers are only for views and views only support INSTEAD - * of triggers. + /* Do not create a trigger on a system table. */ + if (sqlite3StrNICmp(table_name, "sqlite_", 7) == 0) { + diag_set(ClientError, ER_SQL, + "cannot create trigger on system table"); + pParse->rc = SQL_TARANTOOL_ERROR; + goto trigger_cleanup; + } + + /* + * INSTEAD of triggers are only for views and + * views only support INSTEAD of triggers. */ - if (space_is_view(pTab) && tr_tm != TK_INSTEAD) { - sqlite3ErrorMsg(pParse, "cannot create %s trigger on view: %S", - (tr_tm == TK_BEFORE) ? "BEFORE" : "AFTER", - pTableName, 0); + struct space *space = space_cache_find(space_id); + assert(space != NULL); + if (space->def->opts.is_view && tr_tm != TK_INSTEAD) { + char *error = tt_static_buf(); + snprintf(error, TT_STATIC_BUF_LEN, + "cannot create %s trigger on view: %s", + (tr_tm == TK_BEFORE) ? "BEFORE" : "AFTER", table_name); + diag_set(ClientError, ER_SQL, error); + pParse->rc = SQL_TARANTOOL_ERROR; goto trigger_cleanup; } - if (!space_is_view(pTab) && tr_tm == TK_INSTEAD) { - sqlite3ErrorMsg(pParse, "cannot create INSTEAD OF" - " trigger on table: %S", pTableName, 0); + if (!space->def->opts.is_view && tr_tm == TK_INSTEAD) { + char *error = tt_static_buf(); + snprintf(error, TT_STATIC_BUF_LEN, + "cannot create INSTEAD OF trigger on table: %s", + table_name); + diag_set(ClientError, ER_SQL, error); + pParse->rc = SQL_TARANTOOL_ERROR; goto trigger_cleanup; } - /* INSTEAD OF triggers can only appear on views and BEFORE triggers + /* + * INSTEAD OF triggers can only appear on views and BEFORE triggers * cannot appear on views. So we might as well translate every * INSTEAD OF trigger into a BEFORE trigger. It simplifies code * elsewhere. */ - if (tr_tm == TK_INSTEAD) { + if (tr_tm == TK_INSTEAD) tr_tm = TK_BEFORE; - } - /* Build the Trigger object */ - pTrigger = (Trigger *) sqlite3DbMallocZero(db, sizeof(Trigger)); - if (pTrigger == 0) + /* Build the Trigger object. */ + pTrigger = (Trigger *)sqlite3DbMallocZero(db, sizeof(Trigger)); + if (pTrigger == NULL) goto trigger_cleanup; + pTrigger->space_id = space_id; pTrigger->zName = zName; - pTrigger->space_id = pTab->def->id; - zName = 0; - pTrigger->table = sqlite3DbStrDup(db, pTableName->a[0].zName); - pTrigger->pSchema = db->pSchema; - pTrigger->pTabSchema = pTab->pSchema; + zName = NULL; + pTrigger->op = (u8) op; pTrigger->tr_tm = tr_tm == TK_BEFORE ? TRIGGER_BEFORE : TRIGGER_AFTER; pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE); pTrigger->pColumns = sqlite3IdListDup(db, pColumns); - assert(pParse->pNewTrigger == 0); + if ((pWhen != NULL && pTrigger->pWhen == NULL) || + (pColumns != NULL && pTrigger->pColumns == NULL)) + goto trigger_cleanup; + assert(pParse->pNewTrigger == NULL); pParse->pNewTrigger = pTrigger; trigger_cleanup: @@ -186,11 +211,10 @@ sqlite3BeginTrigger(Parse * pParse, /* The parse context of the CREATE TRIGGER s sqlite3SrcListDelete(db, pTableName); sqlite3IdListDelete(db, pColumns); sql_expr_delete(db, pWhen, false); - if (!pParse->pNewTrigger) { + if (pParse->pNewTrigger == NULL) sql_trigger_delete(db, pTrigger); - } else { + else assert(pParse->pNewTrigger == pTrigger); - } } /* @@ -211,7 +235,7 @@ sqlite3FinishTrigger(Parse * pParse, /* Parser context */ DbFixer sFix; /* Fixer object */ Token nameToken; /* Trigger name for error reporting */ - pParse->pNewTrigger = 0; + pParse->pNewTrigger = NULL; if (NEVER(pParse->nErr) || !pTrig) goto triggerfinish_cleanup; zName = pTrig->zName; @@ -228,10 +252,11 @@ sqlite3FinishTrigger(Parse * pParse, /* Parser context */ goto triggerfinish_cleanup; } - /* if we are not initializing, - * generate byte code to insert a new trigger into Tarantool. + /* + * Generate byte code to insert a new trigger into + * Tarantool for non-parsing mode or export trigger. */ - if (!db->init.busy) { + if (!pParse->parse_only) { Vdbe *v; int zOptsSz; Table *pSysTrigger; @@ -290,37 +315,11 @@ sqlite3FinishTrigger(Parse * pParse, /* Parser context */ sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE); sqlite3VdbeAddOp1(v, OP_Close, iCursor); - /* parseschema3(reg(iFirstCol), ref(iFirstCol)+1) */ - iFirstCol = pParse->nMem + 1; - pParse->nMem += 2; - sql_set_multi_write(pParse, false); - sqlite3VdbeAddOp4(v, - OP_String8, 0, iFirstCol, 0, - zName, P4_STATIC); - - sqlite3VdbeAddOp4(v, - OP_String8, 0, iFirstCol + 1, 0, - zSql, P4_DYNAMIC); sqlite3ChangeCookie(pParse); - sqlite3VdbeAddParseSchema3Op(v, iFirstCol); - } - - if (db->init.busy) { - Trigger *pLink = pTrig; - Hash *pHash = &db->pSchema->trigHash; - pTrig = sqlite3HashInsert(pHash, zName, pTrig); - if (pTrig) { - sqlite3OomFault(db); - } else if (pLink->pSchema == pLink->pTabSchema) { - Table *pTab; - pTab = - sqlite3HashFind(&pLink->pTabSchema->tblHash, - pLink->table); - assert(pTab != 0); - pLink->pNext = pTab->pTrigger; - pTab->pTrigger = pLink; - } + } else { + pParse->pNewTrigger = pTrig; + pTrig = NULL; } triggerfinish_cleanup: @@ -331,7 +330,7 @@ sqlite3FinishTrigger(Parse * pParse, /* Parser context */ alloc for it either wasn't called at all or failed. */ } sql_trigger_delete(db, pTrig); - assert(!pParse->pNewTrigger); + assert(!pParse->pNewTrigger || pParse->parse_only); sqlite3DeleteTriggerStep(db, pStepList); } @@ -480,7 +479,6 @@ sql_trigger_delete(struct sqlite3 *db, struct Trigger *trigger) return; sqlite3DeleteTriggerStep(db, trigger->step_list); sqlite3DbFree(db, trigger->zName); - sqlite3DbFree(db, trigger->table); sql_expr_delete(db, trigger->pWhen, false); sqlite3IdListDelete(db, trigger->pColumns); sqlite3DbFree(db, trigger); @@ -535,16 +533,6 @@ sqlite3DropTrigger(Parse * pParse, SrcList * pName, int noErr) } /* - * Return a pointer to the Table structure for the table that a trigger - * is set on. - */ -static Table * -tableOfTrigger(Trigger * pTrigger) -{ - return sqlite3HashFind(&pTrigger->pTabSchema->tblHash, pTrigger->table); -} - -/* * Drop a trigger given a pointer to that trigger. */ void @@ -567,34 +555,59 @@ sqlite3DropTriggerPtr(Parse * pParse, Trigger * pTrigger) sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE); sqlite3ChangeCookie(pParse); - sqlite3VdbeAddOp4(v, OP_DropTrigger, 0, 0, 0, pTrigger->zName, - 0); } } -/* - * Remove a trigger from the hash tables of the sqlite* pointer. - */ -void -sqlite3UnlinkAndDeleteTrigger(sqlite3 * db, const char *zName) +int +sql_trigger_replace(struct sqlite3 *db, const char *name, + struct Trigger *trigger, struct Trigger **old_trigger) { - Trigger *pTrigger; - Hash *pHash; - struct session *user_session = current_session(); - - pHash = &(db->pSchema->trigHash); - pTrigger = sqlite3HashInsert(pHash, zName, 0); - if (ALWAYS(pTrigger)) { - if (pTrigger->pSchema == pTrigger->pTabSchema) { - Table *pTab = tableOfTrigger(pTrigger); - Trigger **pp; - for (pp = &pTab->pTrigger; *pp != pTrigger; - pp = &((*pp)->pNext)) ; - *pp = (*pp)->pNext; - } - sql_trigger_delete(db, pTrigger); - user_session->sql_flags |= SQLITE_InternChanges; + assert(db->pSchema != NULL); + assert(trigger == NULL || strcmp(name, trigger->zName) == 0); + struct Hash *hash = &db->pSchema->trigHash; + *old_trigger = sqlite3HashInsert(hash, name, trigger); + if (*old_trigger == trigger) { + diag_set(OutOfMemory, 0, "sqlite3HashInsert", "ret"); + return -1; } + struct Trigger *src_trigger = trigger != NULL ? trigger : *old_trigger; + assert(src_trigger != NULL); + struct space *space = + space_cache_find(src_trigger->space_id); + assert(space != NULL); + + if (*old_trigger != NULL) { + struct Trigger **pp; + for (pp = &space->sql_triggers; *pp != *old_trigger; + pp = &((*pp)->pNext)); + *pp = (*pp)->pNext; + } + if (trigger != NULL) { + trigger->pNext = space->sql_triggers; + space->sql_triggers = trigger; + } + return 0; +} + +const char * +sql_trigger_name(struct Trigger *trigger) +{ + return trigger->zName; +} + +uint32_t +sql_trigger_space_id(struct Trigger *trigger) +{ + return trigger->space_id; +} + +struct Trigger * +space_trigger_list(uint32_t space_id) +{ + struct space *space = space_cache_find(space_id); + assert(space != NULL); + assert(space->def != NULL); + return space->sql_triggers; } /* @@ -633,22 +646,17 @@ sqlite3TriggersExist(Table * pTab, /* The table the contains the triggers */ ) { int mask = 0; - Trigger *pList = 0; - Trigger *p; + struct Trigger *trigger_list = NULL; struct session *user_session = current_session(); - - if ((user_session->sql_flags & SQLITE_EnableTrigger) != 0) { - pList = pTab->pTrigger; - } - for (p = pList; p; p = p->pNext) { - if (p->op == op && checkColumnOverlap(p->pColumns, pChanges)) { + if ((user_session->sql_flags & SQLITE_EnableTrigger) != 0) + trigger_list = space_trigger_list(pTab->def->id); + for (struct Trigger *p = trigger_list; p; p = p->pNext) { + if (p->op == op && checkColumnOverlap(p->pColumns, pChanges)) mask |= p->tr_tm; - } } - if (pMask) { + if (pMask != 0) *pMask = mask; - } - return (mask ? pList : 0); + return (mask != 0) ? trigger_list : 0; } /* @@ -837,7 +845,7 @@ codeRowTrigger(Parse * pParse, /* Current parse context */ Parse *pSubParse; /* Parse context for sub-vdbe */ int iEndTrigger = 0; /* Label to jump to if WHEN is false */ - assert(pTrigger->zName == 0 || pTab == tableOfTrigger(pTrigger)); + assert(pTrigger->zName == 0 || pTab->def->id == pTrigger->space_id); assert(pTop->pVdbe); /* Allocate the TriggerPrg and SubProgram objects. To ensure that they @@ -954,7 +962,7 @@ getRowTrigger(Parse * pParse, /* Current parse context */ Parse *pRoot = sqlite3ParseToplevel(pParse); TriggerPrg *pPrg; - assert(pTrigger->zName == 0 || pTab == tableOfTrigger(pTrigger)); + assert(pTrigger->zName == 0 || pTab->def->id == pTrigger->space_id); /* It may be that this trigger has already been coded (or is in the * process of being coded). If this is the case, then an entry with @@ -1079,15 +1087,6 @@ sqlite3CodeRowTrigger(Parse * pParse, /* Parse context */ assert((op == TK_UPDATE) == (pChanges != 0)); for (p = pTrigger; p; p = p->pNext) { - - /* Sanity checking: The schema for the trigger and for the table are - * always defined. The trigger must be in the same schema as the table - * or else it must be a TEMP trigger. - */ - assert(p->pSchema != 0); - assert(p->pTabSchema != 0); - assert(p->pSchema == p->pTabSchema); - /* Determine whether we should code this trigger */ if (p->op == op && p->tr_tm == tr_tm diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 3fe5875..2d1538e 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -4686,46 +4686,6 @@ case OP_ParseSchema2: { break; } -/* Opcode: ParseSchema3 P1 * * * * - * Synopsis: name=r[P1] sql=r[P1+1] - * - * Create trigger named r[P1] w/ DDL SQL stored in r[P1+1] - * in database P2 - */ -case OP_ParseSchema3: { - InitData initData; - Mem *pRec; - char zPgnoBuf[16]; - char *argv[4] = {NULL, zPgnoBuf, NULL, NULL}; - assert(db->pSchema != NULL); - - initData.db = db; - initData.pzErrMsg = &p->zErrMsg; - - assert(db->init.busy==0); - db->init.busy = 1; - initData.rc = SQLITE_OK; - assert(!db->mallocFailed); - - pRec = &aMem[pOp->p1]; - argv[0] = pRec[0].z; - argv[1] = "0"; - argv[2] = pRec[1].z; - sqlite3InitCallback(&initData, 3, argv, NULL); - - rc = initData.rc; - db->init.busy = 0; - - if (rc) { - sqlite3ResetAllSchemasOfConnection(db); - if (rc==SQLITE_NOMEM) { - goto no_mem; - } - goto abort_due_to_error; - } - break; -} - /* Opcode: RenameTable P1 * * P4 * * Synopsis: P1 = root, P4 = name * @@ -4745,7 +4705,6 @@ case OP_RenameTable: { const char *zNewTableName; Table *pTab; FKey *pFKey; - Trigger *pTrig; int iRootPage; InitData initData; char *argv[4] = {NULL, NULL, NULL, NULL}; @@ -4758,7 +4717,6 @@ case OP_RenameTable: { assert(zOldTableName); pTab = sqlite3HashFind(&db->pSchema->tblHash, zOldTableName); assert(pTab); - pTrig = pTab->pTrigger; iRootPage = pTab->tnum; zNewTableName = pOp->p4.z; zOldTableName = sqlite3DbStrNDup(db, zOldTableName, @@ -4799,19 +4757,21 @@ case OP_RenameTable: { goto abort_due_to_error; } - pTab = sqlite3HashFind(&db->pSchema->tblHash, zNewTableName); - pTab->pTrigger = pTrig; + space = space_by_id(space_id); + assert(space != NULL); - /* Rename all trigger created on this table.*/ - for (; pTrig; pTrig = pTrig->pNext) { - sqlite3DbFree(db, pTrig->table); - pTrig->table = sqlite3DbStrNDup(db, zNewTableName, - sqlite3Strlen30(zNewTableName)); - pTrig->pTabSchema = pTab->pSchema; - rc = tarantoolSqlite3RenameTrigger(pTrig->zName, + /* Rename all triggers created on this table. */ + for (struct Trigger *trigger = space->sql_triggers; trigger != NULL; ) { + struct Trigger *next_trigger = trigger->pNext; + rc = tarantoolSqlite3RenameTrigger(trigger->zName, zOldTableName, zNewTableName); - if (rc) goto abort_due_to_error; + if (rc != SQLITE_OK) { + sqlite3ResetAllSchemasOfConnection(db); + goto abort_due_to_error; + } + trigger = next_trigger; } + sqlite3DbFree(db, (void*)zOldTableName); sqlite3DbFree(db, (void*)zSqlStmt); break; @@ -4857,18 +4817,6 @@ case OP_DropIndex: { break; } -/* Opcode: DropTrigger P1 * * P4 * - * - * Remove the internal (in-memory) data structures that describe - * the trigger named P4 in database P1. This is called after a trigger - * is dropped from disk (using the Destroy opcode) in order to keep - * the internal representation of the - * schema consistent with what is on disk. - */ -case OP_DropTrigger: { - sqlite3UnlinkAndDeleteTrigger(db, pOp->p4.z); - break; -} #ifndef SQLITE_OMIT_TRIGGER /* Opcode: Program P1 P2 P3 P4 P5 diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h index 68d542c..77fa41a 100644 --- a/src/box/sql/vdbe.h +++ b/src/box/sql/vdbe.h @@ -238,7 +238,6 @@ void sqlite3VdbeVerifyNoResultRow(Vdbe * p); VdbeOp *sqlite3VdbeAddOpList(Vdbe *, int nOp, VdbeOpList const *aOp, int iLineno); void sqlite3VdbeAddParseSchema2Op(Vdbe * p, int, int); -void sqlite3VdbeAddParseSchema3Op(Vdbe * p, int); void sqlite3VdbeAddRenameTableOp(Vdbe * p, int, char *); void sqlite3VdbeChangeOpcode(Vdbe *, u32 addr, u8); void sqlite3VdbeChangeP1(Vdbe *, u32 addr, int P1); diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c index 679bd0b..7b3c13d 100644 --- a/src/box/sql/vdbeaux.c +++ b/src/box/sql/vdbeaux.c @@ -410,15 +410,6 @@ sqlite3VdbeAddParseSchema2Op(Vdbe * p, int iRec, int n) sqlite3VdbeAddOp3(p, OP_ParseSchema2, iRec, n, 0); } -/* - * Add an OP_ParseSchema3 opcode which in turn will create a trigger - */ -void -sqlite3VdbeAddParseSchema3Op(Vdbe * p, int iRec) -{ - sqlite3VdbeAddOp2(p, OP_ParseSchema3, iRec, 0); -} - void sqlite3VdbeAddRenameTableOp(Vdbe * p, int iTab, char* zNewName) { diff --git a/test/box/misc.result b/test/box/misc.result index 6f4028c..e8ee297 100644 --- a/test/box/misc.result +++ b/test/box/misc.result @@ -381,6 +381,7 @@ t; 52: box.error.FUNCTION_EXISTS 53: box.error.BEFORE_REPLACE_RET 54: box.error.FUNCTION_MAX + 55: box.error.TRIGGER_EXISTS 56: box.error.USER_MAX 57: box.error.NO_SUCH_ENGINE 58: box.error.RELOAD_CFG diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua index 3f6dc07..5e7573a 100755 --- a/test/sql-tap/identifier_case.test.lua +++ b/test/sql-tap/identifier_case.test.lua @@ -170,8 +170,8 @@ test:do_execsql_test( data = { { 1, [[ trigger1 ]], {0}}, - { 2, [[ Trigger1 ]], {1, "trigger TRIGGER1 already exists"}}, - { 3, [["TRIGGER1"]], {1, "trigger TRIGGER1 already exists"}}, + { 2, [[ Trigger1 ]], {1, "Trigger 'TRIGGER1' already exists"}}, + { 3, [["TRIGGER1"]], {1, "Trigger 'TRIGGER1' already exists"}}, { 4, [["trigger1" ]], {0}} } diff --git a/test/sql-tap/trigger1.test.lua b/test/sql-tap/trigger1.test.lua index 40daba4..79111fd 100755 --- a/test/sql-tap/trigger1.test.lua +++ b/test/sql-tap/trigger1.test.lua @@ -43,7 +43,7 @@ test:do_catchsql_test( END; ]], { -- - 1, "no such table: NO_SUCH_TABLE" + 1, "Space 'NO_SUCH_TABLE' does not exist" -- }) @@ -55,7 +55,7 @@ test:do_catchsql_test( END; ]], { -- - 1, "no such table: NO_SUCH_TABLE" + 1, "Space 'NO_SUCH_TABLE' does not exist" -- }) @@ -101,7 +101,7 @@ test:do_catchsql_test( END ]], { -- - 1, "trigger TR1 already exists" + 1, "Trigger 'TR1' already exists" -- }) @@ -113,7 +113,7 @@ test:do_catchsql_test( END ]], { -- - 1, [[trigger TR1 already exists]] + 1, [[Trigger 'TR1' already exists]] -- }) @@ -251,7 +251,7 @@ test:do_catchsql_test( end; ]], { -- - 1, "cannot create INSTEAD OF trigger on table: T1" + 1, "SQL error: cannot create INSTEAD OF trigger on table: T1" -- }) @@ -265,7 +265,7 @@ test:do_catchsql_test( end; ]], { -- - 1, "cannot create BEFORE trigger on view: V1" + 1, "SQL error: cannot create BEFORE trigger on view: V1" -- }) @@ -280,7 +280,7 @@ test:do_catchsql_test( end; ]], { -- - 1, "cannot create AFTER trigger on view: V1" + 1, "SQL error: cannot create AFTER trigger on view: V1" -- }) diff --git a/test/sql/triggers.result b/test/sql/triggers.result new file mode 100644 index 0000000..d214962 --- /dev/null +++ b/test/sql/triggers.result @@ -0,0 +1,260 @@ +env = require('test_run') +--- +... +test_run = env.new() +--- +... +test_run:cmd("push filter ".."'\\.lua.*:[0-9]+: ' to '.lua...\"]:: '") +--- +- true +... +-- get invariant part of the tuple + function inmutable_part(data) local r = {} for i, l in pairs(data) do r[#r+1]={l[1], l[3]} end return r end +--- +... +-- +-- gh-3273: Move Triggers to server +-- +box.sql.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY);") +--- +... +box.sql.execute("CREATE TABLE t2(x INTEGER PRIMARY KEY);") +--- +... +box.sql.execute([[CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END; ]]) +--- +... +inmutable_part(box.space._trigger:select()) +--- +- - - T1T + - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); + END; '} +... +space_id = box.space._space.index["name"]:get('T1').id +--- +... +-- checks for LUA tuples +tuple = {"T1T", space_id, {sql = "CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +--- +... +inmutable_part({box.space._trigger:insert(tuple)}) +--- +- error: Duplicate key exists in unique index 'primary' in space '_trigger' +... +tuple = {"T1t", space_id, {sql = "CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +--- +... +inmutable_part({box.space._trigger:insert(tuple)}) +--- +- error: 'SQL error: tuple trigger name does not match extracted from SQL' +... +tuple = {"T1t", space_id, {sql = "CREATE TRIGGER t12t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +--- +... +inmutable_part({box.space._trigger:insert(tuple)}) +--- +- error: 'SQL error: tuple trigger name does not match extracted from SQL' +... +tuple = {"T2T", 443, {sql = "CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +--- +... +inmutable_part({box.space._trigger:insert(tuple)}) +--- +- error: 'SQL error: tuple space_id does not match the value resolved on AST building + from SQL' +... +inmutable_part(box.space._trigger:select()) +--- +- - - T1T + - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); + END; '} +... +box.sql.execute("DROP TABLE T1;") +--- +... +inmutable_part(box.space._trigger:select()) +--- +- [] +... +box.sql.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY);") +--- +... +box.sql.execute([[CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END; ]]) +--- +... +inmutable_part(box.space._trigger:select()) +--- +- - - T1T + - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); + END; '} +... +space_id = box.space._space.index["name"]:get('T1').id +--- +... +-- test, didn't trigger t1t degrade +box.sql.execute("INSERT INTO t1 VALUES(1);") +--- +... +-- test duplicate index error +box.sql.execute("INSERT INTO t1 VALUES(1);") +--- +- error: Duplicate key exists in unique index 'sqlite_autoindex_T1_1' in space 'T1' +... +box.sql.execute("SELECT * FROM t2;") +--- +- - [1] +... +box.sql.execute("DELETE FROM t2;") +--- +... +-- test triggers +tuple = {"T2T", space_id, {sql = "CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); END;"}} +--- +... +inmutable_part({box.space._trigger:insert(tuple)}) +--- +- - - T2T + - {'sql': 'CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); + END;'} +... +tuple = {"T3T", space_id, {sql = "CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); END;"}} +--- +... +inmutable_part({box.space._trigger:insert(tuple)}) +--- +- - - T3T + - {'sql': 'CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); + END;'} +... +inmutable_part(box.space._trigger:select()) +--- +- - - T1T + - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); + END; '} + - - T2T + - {'sql': 'CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); + END;'} + - - T3T + - {'sql': 'CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); + END;'} +... +box.sql.execute("INSERT INTO t1 VALUES(2);") +--- +... +box.sql.execute("SELECT * FROM t2;") +--- +- - [1] + - [2] + - [3] +... +box.sql.execute("DELETE FROM t2;") +--- +... +-- test t1t after t2t and t3t drop +box.sql.execute("DROP TRIGGER T2T;") +--- +... +inmutable_part({box.space._trigger:delete("T3T")}) +--- +- - - T3T + - {'sql': 'CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); + END;'} +... +inmutable_part(box.space._trigger:select()) +--- +- - - T1T + - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); + END; '} +... +box.sql.execute("INSERT INTO t1 VALUES(3);") +--- +... +box.sql.execute("SELECT * FROM t2;") +--- +- - [1] +... +box.sql.execute("DELETE FROM t2;") +--- +... +-- insert new SQL t2t and t3t +box.sql.execute([[CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); END; ]]) +--- +... +box.sql.execute([[CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); END; ]]) +--- +... +inmutable_part(box.space._trigger:select()) +--- +- - - T1T + - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); + END; '} + - - T2T + - {'sql': 'CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); + END; '} + - - T3T + - {'sql': 'CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); + END; '} +... +box.sql.execute("INSERT INTO t1 VALUES(4);") +--- +... +box.sql.execute("SELECT * FROM t2;") +--- +- - [1] + - [2] + - [3] +... +-- clean up +box.sql.execute("DROP TABLE t1;") +--- +... +box.sql.execute("DROP TABLE t2;") +--- +... +inmutable_part(box.space._trigger:select()) +--- +- [] +... +-- test crash on error.injection +box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a INTEGER);"); +--- +... +box.sql.execute("CREATE TABLE t2(id INTEGER PRIMARY KEY, a INTEGER);"); +--- +... +box.sql.execute("CREATE TRIGGER t1t INSERT ON t1 BEGIN INSERT INTO t2 VALUES (1, 1); END;") +--- +... +box.error.injection.set("ERRINJ_WAL_IO", true) +--- +- ok +... +box.sql.execute("CREATE INDEX t1a ON t1(a);") +--- +- error: Failed to write to disk +... +box.error.injection.set("ERRINJ_WAL_IO", false) +--- +- ok +... +box.sql.execute("INSERT INTO t1 VALUES (3, 3);") +--- +... +box.sql.execute("SELECT * from t1"); +--- +- - [3, 3] +... +box.sql.execute("SELECT * from t2"); +--- +- - [1, 1] +... +box.sql.execute("DROP TABLE t1;") +--- +... +box.sql.execute("DROP TABLE t2;") +--- +... +test_run:cmd("clear filter") +--- +- true +... diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua new file mode 100644 index 0000000..98da2b9 --- /dev/null +++ b/test/sql/triggers.test.lua @@ -0,0 +1,94 @@ +env = require('test_run') +test_run = env.new() +test_run:cmd("push filter ".."'\\.lua.*:[0-9]+: ' to '.lua...\"]:: '") + +-- get invariant part of the tuple + function inmutable_part(data) local r = {} for i, l in pairs(data) do r[#r+1]={l[1], l[3]} end return r end + +-- +-- gh-3273: Move Triggers to server +-- + +box.sql.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY);") +box.sql.execute("CREATE TABLE t2(x INTEGER PRIMARY KEY);") +box.sql.execute([[CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END; ]]) +inmutable_part(box.space._trigger:select()) + +space_id = box.space._space.index["name"]:get('T1').id + +-- checks for LUA tuples +tuple = {"T1T", space_id, {sql = "CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +inmutable_part({box.space._trigger:insert(tuple)}) + +tuple = {"T1t", space_id, {sql = "CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +inmutable_part({box.space._trigger:insert(tuple)}) + +tuple = {"T1t", space_id, {sql = "CREATE TRIGGER t12t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +inmutable_part({box.space._trigger:insert(tuple)}) + +tuple = {"T2T", 443, {sql = "CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END;"}} +inmutable_part({box.space._trigger:insert(tuple)}) +inmutable_part(box.space._trigger:select()) + +box.sql.execute("DROP TABLE T1;") +inmutable_part(box.space._trigger:select()) + +box.sql.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY);") +box.sql.execute([[CREATE TRIGGER t1t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(1); END; ]]) +inmutable_part(box.space._trigger:select()) + +space_id = box.space._space.index["name"]:get('T1').id + +-- test, didn't trigger t1t degrade +box.sql.execute("INSERT INTO t1 VALUES(1);") +-- test duplicate index error +box.sql.execute("INSERT INTO t1 VALUES(1);") +box.sql.execute("SELECT * FROM t2;") +box.sql.execute("DELETE FROM t2;") + + +-- test triggers +tuple = {"T2T", space_id, {sql = "CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); END;"}} +inmutable_part({box.space._trigger:insert(tuple)}) +tuple = {"T3T", space_id, {sql = "CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); END;"}} +inmutable_part({box.space._trigger:insert(tuple)}) +inmutable_part(box.space._trigger:select()) +box.sql.execute("INSERT INTO t1 VALUES(2);") +box.sql.execute("SELECT * FROM t2;") +box.sql.execute("DELETE FROM t2;") + +-- test t1t after t2t and t3t drop +box.sql.execute("DROP TRIGGER T2T;") +inmutable_part({box.space._trigger:delete("T3T")}) +inmutable_part(box.space._trigger:select()) +box.sql.execute("INSERT INTO t1 VALUES(3);") +box.sql.execute("SELECT * FROM t2;") +box.sql.execute("DELETE FROM t2;") + +-- insert new SQL t2t and t3t +box.sql.execute([[CREATE TRIGGER t2t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(2); END; ]]) +box.sql.execute([[CREATE TRIGGER t3t AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(3); END; ]]) +inmutable_part(box.space._trigger:select()) +box.sql.execute("INSERT INTO t1 VALUES(4);") +box.sql.execute("SELECT * FROM t2;") + +-- clean up +box.sql.execute("DROP TABLE t1;") +box.sql.execute("DROP TABLE t2;") +inmutable_part(box.space._trigger:select()) + + +-- test crash on error.injection +box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a INTEGER);"); +box.sql.execute("CREATE TABLE t2(id INTEGER PRIMARY KEY, a INTEGER);"); +box.sql.execute("CREATE TRIGGER t1t INSERT ON t1 BEGIN INSERT INTO t2 VALUES (1, 1); END;") +box.error.injection.set("ERRINJ_WAL_IO", true) +box.sql.execute("CREATE INDEX t1a ON t1(a);") +box.error.injection.set("ERRINJ_WAL_IO", false) +box.sql.execute("INSERT INTO t1 VALUES (3, 3);") +box.sql.execute("SELECT * from t1"); +box.sql.execute("SELECT * from t2"); +box.sql.execute("DROP TABLE t1;") +box.sql.execute("DROP TABLE t2;") + +test_run:cmd("clear filter") -- 2.7.4