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 8B8432626D for ; Sat, 9 Jun 2018 07:52:42 -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 AjdwNOXMen0U for ; Sat, 9 Jun 2018 07:52:42 -0400 (EDT) Received: from fallback.mail.ru (fallback15.m.smailru.net [94.100.179.50]) (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 1F614261C1 for ; Sat, 9 Jun 2018 07:52:42 -0400 (EDT) Received: from [10.161.100.15] (port=54600 helo=smtpng3.m.smailru.net) by fallback15.m.smailru.net with esmtp (envelope-from ) id 1fRaEj-0001pq-5F for tarantool-patches@freelists.org; Sat, 09 Jun 2018 12:32:41 +0300 From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v2 09/11] sql: new _trigger space format with space_id Date: Sat, 9 Jun 2018 12:32:22 +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 As we would like to lookup triggers by space_id in future on space deletion to delete assocoated _trigger tuples we need to introduce new field space_id as secondary key. Part of #3273. --- src/box/bootstrap.snap | Bin 1698 -> 1704 bytes src/box/lua/upgrade.lua | 6 +++++- src/box/sql.c | 18 +++++++++++------- src/box/sql/sqliteInt.h | 2 ++ src/box/sql/trigger.c | 8 +++++--- test/sql/gh2141-delete-trigger-drop-table.result | 4 ++-- test/sql/gh2141-delete-trigger-drop-table.test.lua | 4 ++-- test/sql/persistency.result | 8 ++++---- test/sql/persistency.test.lua | 8 ++++---- 9 files changed, 35 insertions(+), 23 deletions(-) diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap index 11063f70a1ee98e57ef652dbbf90d7a35e64eb6a..f3d047c5badc7c6aacd7ed776382a325d1cdad54 100644 diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua index 18bfaa9..e54b394 100644 --- a/src/box/lua/upgrade.lua +++ b/src/box/lua/upgrade.lua @@ -477,12 +477,16 @@ local function upgrade_to_2_1_0() log.info("create space _trigger") local format = {{name='name', type='string'}, + {name='space_id', type='unsigned'}, {name='opts', type='map'}} _space:insert{_trigger.id, ADMIN, '_trigger', 'memtx', 0, MAP, format} log.info("create index primary on _trigger") _index:insert{_trigger.id, 0, 'primary', 'tree', { unique = true }, - {{0, 'string'}}} + {{0, 'string'}} } + log.info("create index secondary on _trigger") + _index:insert{_trigger.id, 1, 'space_id', 'tree', { unique = false }, + {{1, 'unsigned'}} } local stat1_ft = {{name='tbl', type='string'}, {name='idx', type='string'}, diff --git a/src/box/sql.c b/src/box/sql.c index 599cb60..0ba0346 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -669,8 +669,11 @@ int tarantoolSqlite3RenameTrigger(const char *trig_name, if (box_index_get(BOX_TRIGGER_ID, 0, key_begin, key, &tuple) != 0) return SQL_TARANTOOL_ERROR; assert(tuple != NULL); - assert(tuple_field_count(tuple) == 2); + assert(tuple_field_count(tuple) == 3); const char *field = box_tuple_field(tuple, 1); + assert(mp_typeof(*field) == MP_UINT); + uint32_t space_id = mp_decode_uint(&field); + field = box_tuple_field(tuple, 2); assert(mp_typeof(*field) == MP_MAP); mp_decode_map(&field); const char *sql_str = mp_decode_str(&field, &key_len); @@ -693,16 +696,17 @@ int tarantoolSqlite3RenameTrigger(const char *trig_name, uint32_t trigger_stmt_new_len = trigger_stmt_len + new_table_name_len - old_table_name_len + 2 * (!is_quoted); assert(trigger_stmt_new_len > 0); - key_len = mp_sizeof_array(2) + mp_sizeof_str(trig_name_len) + + key_len = mp_sizeof_array(3) + mp_sizeof_str(trig_name_len) + mp_sizeof_map(1) + mp_sizeof_str(3) + - mp_sizeof_str(trigger_stmt_new_len); + mp_sizeof_str(trigger_stmt_new_len) + mp_sizeof_uint(space_id); char *new_tuple = (char*)region_alloc(&fiber()->gc, key_len); if (new_tuple == NULL) { diag_set(OutOfMemory, key_len, "region_alloc", "new_tuple"); return SQL_TARANTOOL_ERROR; } - char *new_tuple_end = mp_encode_array(new_tuple, 2); + char *new_tuple_end = mp_encode_array(new_tuple, 3); new_tuple_end = mp_encode_str(new_tuple_end, trig_name, trig_name_len); + new_tuple_end = mp_encode_uint(new_tuple_end, space_id); new_tuple_end = mp_encode_map(new_tuple_end, 1); new_tuple_end = mp_encode_str(new_tuple_end, "sql", 3); new_tuple_end = mp_encode_str(new_tuple_end, trigger_stmt, @@ -1253,7 +1257,7 @@ void tarantoolSqlite3LoadSchema(InitData *init) init, TARANTOOL_SYS_TRIGGER_NAME, BOX_TRIGGER_ID, 0, "CREATE TABLE \""TARANTOOL_SYS_TRIGGER_NAME"\" (" - "\"name\" TEXT PRIMARY KEY, \"opts\")" + "\"name\" TEXT PRIMARY KEY, \"space_id\" INT, \"opts\")" ); sql_schema_put( @@ -1308,14 +1312,14 @@ void tarantoolSqlite3LoadSchema(InitData *init) const char *field, *ptr; char *name, *sql; unsigned len; - assert(tuple_field_count(tuple) == 2); + 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, 1); + field = tuple_field(tuple, 2); assert (field != NULL); mp_decode_array(&field); ptr = mp_decode_str(&field, &len); diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h index ecbd573..6ca59c4 100644 --- a/src/box/sql/sqliteInt.h +++ b/src/box/sql/sqliteInt.h @@ -3032,6 +3032,8 @@ struct Parse { */ 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 */ diff --git a/src/box/sql/trigger.c b/src/box/sql/trigger.c index 053dadb..a9c686f 100644 --- a/src/box/sql/trigger.c +++ b/src/box/sql/trigger.c @@ -169,6 +169,7 @@ sqlite3BeginTrigger(Parse * pParse, /* The parse context of the CREATE TRIGGER s if (pTrigger == 0) goto trigger_cleanup; pTrigger->zName = zName; + pTrigger->space_id = pTab->def->id; zName = 0; pTrigger->table = sqlite3DbStrDup(db, pTableName->a[0].zName); pTrigger->pSchema = db->pSchema; @@ -256,7 +257,7 @@ sqlite3FinishTrigger(Parse * pParse, /* Parser context */ /* makerecord(cursor(iRecord), [reg(iFirstCol), reg(iFirstCol+1)]) */ iFirstCol = pParse->nMem + 1; - pParse->nMem += 2; + pParse->nMem += 3; iRecord = ++pParse->nMem; zOpts = sqlite3DbMallocRaw(pParse->db, @@ -276,9 +277,10 @@ sqlite3FinishTrigger(Parse * pParse, /* Parser context */ sqlite3VdbeAddOp4(v, OP_String8, 0, iFirstCol, 0, zName, P4_DYNAMIC); - sqlite3VdbeAddOp4(v, OP_Blob, zOptsSz, iFirstCol + 1, + sqlite3VdbeAddOp2(v, OP_Integer, pTrig->space_id, iFirstCol + 1); + sqlite3VdbeAddOp4(v, OP_Blob, zOptsSz, iFirstCol + 2, MSGPACK_SUBTYPE, zOpts, P4_DYNAMIC); - sqlite3VdbeAddOp3(v, OP_MakeRecord, iFirstCol, 2, iRecord); + sqlite3VdbeAddOp3(v, OP_MakeRecord, iFirstCol, 3, iRecord); sqlite3VdbeAddOp2(v, OP_IdxInsert, iCursor, iRecord); /* Do not account nested operations: the count of such * operations depends on Tarantool data dictionary internals, diff --git a/test/sql/gh2141-delete-trigger-drop-table.result b/test/sql/gh2141-delete-trigger-drop-table.result index ba7016c..ec5a380 100644 --- a/test/sql/gh2141-delete-trigger-drop-table.result +++ b/test/sql/gh2141-delete-trigger-drop-table.result @@ -24,7 +24,7 @@ box.sql.execute("CREATE TRIGGER tt_ad AFTER DELETE ON t BEGIN SELECT 1; END") --- ... -- check that these triggers exist -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") --- - - ['TT_AD', !!binary gaNzcWzZOkNSRUFURSBUUklHR0VSIHR0X2FkIEFGVEVSIERFTEVURSBPTiB0IEJFR0lOIFNFTEVDVCAxOyBFTkQ=] - ['TT_AI', !!binary gaNzcWzZOkNSRUFURSBUUklHR0VSIHR0X2FpIEFGVEVSIElOU0VSVCBPTiB0IEJFR0lOIFNFTEVDVCAxOyBFTkQ=] @@ -38,7 +38,7 @@ box.sql.execute("DROP TABLE t") --- ... -- check that triggers were dropped with deleted table -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") --- - [] ... diff --git a/test/sql/gh2141-delete-trigger-drop-table.test.lua b/test/sql/gh2141-delete-trigger-drop-table.test.lua index e6a030c..87110a4 100644 --- a/test/sql/gh2141-delete-trigger-drop-table.test.lua +++ b/test/sql/gh2141-delete-trigger-drop-table.test.lua @@ -11,10 +11,10 @@ box.sql.execute("CREATE TRIGGER tt_bd BEFORE DELETE ON t BEGIN SELECT 1; END") box.sql.execute("CREATE TRIGGER tt_ad AFTER DELETE ON t BEGIN SELECT 1; END") -- check that these triggers exist -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") -- drop table box.sql.execute("DROP TABLE t") -- check that triggers were dropped with deleted table -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") diff --git a/test/sql/persistency.result b/test/sql/persistency.result index 7a7f6b8..d85d7cc 100644 --- a/test/sql/persistency.result +++ b/test/sql/persistency.result @@ -140,7 +140,7 @@ box.sql.execute("INSERT INTO barfoo VALUES ('foobar', 1000)") box.sql.execute("CREATE TRIGGER tfoobar AFTER INSERT ON foobar BEGIN INSERT INTO barfoo VALUES ('trigger test', 9999); END") --- ... -box.sql.execute("SELECT * FROM \"_trigger\""); +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\""); --- - - ['TFOOBAR', !!binary gaNzcWzZaUNSRUFURSBUUklHR0VSIHRmb29iYXIgQUZURVIgSU5TRVJUIE9OIGZvb2JhciBCRUdJTiBJTlNFUlQgSU5UTyBiYXJmb28gVkFMVUVTICgndHJpZ2dlciB0ZXN0JywgOTk5OSk7IEVORA==] ... @@ -166,7 +166,7 @@ box.sql.execute("SELECT a FROM t1 ORDER BY b, a LIMIT 10 OFFSET 20;"); ... test_run:cmd('restart server default'); -- prove that trigger survived -box.sql.execute("SELECT * FROM \"_trigger\""); +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\""); --- - - ['TFOOBAR', !!binary gaNzcWzZaUNSRUFURSBUUklHR0VSIHRmb29iYXIgQUZURVIgSU5TRVJUIE9OIGZvb2JhciBCRUdJTiBJTlNFUlQgSU5UTyBiYXJmb28gVkFMVUVTICgndHJpZ2dlciB0ZXN0JywgOTk5OSk7IEVORA==] ... @@ -179,7 +179,7 @@ box.sql.execute("SELECT * FROM barfoo WHERE foo = 9999"); - - ['trigger test', 9999] ... -- and still persistent -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") --- - - ['TFOOBAR', !!binary gaNzcWzZaUNSRUFURSBUUklHR0VSIHRmb29iYXIgQUZURVIgSU5TRVJUIE9OIGZvb2JhciBCRUdJTiBJTlNFUlQgSU5UTyBiYXJmb28gVkFMVUVTICgndHJpZ2dlciB0ZXN0JywgOTk5OSk7IEVORA==] ... @@ -193,7 +193,7 @@ box.sql.execute("DROP TRIGGER tfoobar") - error: 'no such trigger: TFOOBAR' ... -- Should be empty -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") --- - [] ... diff --git a/test/sql/persistency.test.lua b/test/sql/persistency.test.lua index bd05545..e994a62 100644 --- a/test/sql/persistency.test.lua +++ b/test/sql/persistency.test.lua @@ -49,7 +49,7 @@ box.sql.execute("INSERT INTO barfoo VALUES ('foobar', 1000)") -- create a trigger box.sql.execute("CREATE TRIGGER tfoobar AFTER INSERT ON foobar BEGIN INSERT INTO barfoo VALUES ('trigger test', 9999); END") -box.sql.execute("SELECT * FROM \"_trigger\""); +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\""); -- Many entries box.sql.execute("CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c));") @@ -59,21 +59,21 @@ box.sql.execute("SELECT a FROM t1 ORDER BY b, a LIMIT 10 OFFSET 20;"); test_run:cmd('restart server default'); -- prove that trigger survived -box.sql.execute("SELECT * FROM \"_trigger\""); +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\""); -- ... functional box.sql.execute("INSERT INTO foobar VALUES ('foobar trigger test', 8888)") box.sql.execute("SELECT * FROM barfoo WHERE foo = 9999"); -- and still persistent -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") -- and can be dropped just once box.sql.execute("DROP TRIGGER tfoobar") -- Should error box.sql.execute("DROP TRIGGER tfoobar") -- Should be empty -box.sql.execute("SELECT * FROM \"_trigger\"") +box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"") -- prove barfoo2 still exists box.sql.execute("INSERT INTO barfoo VALUES ('xfoo', 1)") -- 2.7.4