[tarantool-patches] [PATCH v2 09/11] sql: new _trigger space format with space_id

[Kirill Shcherbatov <kshcherbatov@tarantool.org>]

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