[tarantool-patches] [PATCH v1 1/1] sql: add function last_insert_id

[tarantool-patches] [PATCH v1 1/1] sql: add function last_insert_id

[imeevma]

Function last_insert_id() returns index of first
inserted tuple in last INSERT query in current
session. It takes no arguments.

Part of #2618.
---
Branch: https://github.com/tarantool/tarantool/tree/imeevma/gh-2618-return-generated-columns-and-values
Issue: https://github.com/tarantool/tarantool/issues/2618

 src/box/lua/session.c         |  1 +
 src/box/sequence.c            | 13 +++++++++++++
 src/box/sequence.h            |  9 +++++++++
 src/box/session.h             |  2 ++
 src/box/sql.c                 | 11 +++++++++++
 src/box/sql/func.c            | 13 +++++++++++++
 src/box/sql/sqliteInt.h       | 10 ++++++++++
 src/box/sql/vdbe.c            | 10 ++++++++--
 test/sql-tap/insert3.test.lua | 27 ++++++++++++++++++++++++++-
 9 files changed, 93 insertions(+), 3 deletions(-)

diff --git a/src/box/lua/session.c b/src/box/lua/session.c
index b2e1400..41da6ea 100644
--- a/src/box/lua/session.c
+++ b/src/box/lua/session.c
@@ -58,6 +58,7 @@ lbox_session_create(struct lua_State *L)
 	}
 	/* If a session already exists, simply reset its type */
 	session->type = STR2ENUM(session_type, luaL_optstring(L, 2, "console"));
+	session->last_insert_id = 0;
 
 	lua_pushnumber(L, session->id);
 	return 1;
diff --git a/src/box/sequence.c b/src/box/sequence.c
index 35b7605..fe66931 100644
--- a/src/box/sequence.c
+++ b/src/box/sequence.c
@@ -340,3 +340,16 @@ sequence_data_iterator_create(void)
 	light_sequence_iterator_freeze(&sequence_data_index, &iter->iter);
 	return &iter->base;
 }
+
+int64_t
+sequence_get_value(struct sequence *seq)
+{
+	uint32_t key = seq->def->id;
+	uint32_t hash = sequence_hash(key);
+	uint32_t pos = light_sequence_find_key(&sequence_data_index, hash, key);
+	if (pos == light_sequence_end)
+		return 0;
+	struct sequence_data data = light_sequence_get(&sequence_data_index,
+						       pos);
+	return data.value;
+}
diff --git a/src/box/sequence.h b/src/box/sequence.h
index 0f6c8da..c1a746e 100644
--- a/src/box/sequence.h
+++ b/src/box/sequence.h
@@ -149,6 +149,15 @@ access_check_sequence(struct sequence *seq);
 struct snapshot_iterator *
 sequence_data_iterator_create(void);
 
+/**
+ * Get current value of given sequence.
+ *
+ * @param seq sequence to get value from.
+ * @retval current value of sequence.
+ */
+int64_t
+sequence_get_value(struct sequence *seq);
+
 #if defined(__cplusplus)
 } /* extern "C" */
 #endif /* defined(__cplusplus) */
diff --git a/src/box/session.h b/src/box/session.h
index df1dcbc..c9a0622 100644
--- a/src/box/session.h
+++ b/src/box/session.h
@@ -103,6 +103,8 @@ struct session {
 	struct credentials credentials;
 	/** Trigger for fiber on_stop to cleanup created on-demand session */
 	struct trigger fiber_on_stop;
+	/** ID of first inserted tuple in last SQL INSERT query */
+	int64_t last_insert_id;
 };
 
 struct session_vtab {
diff --git a/src/box/sql.c b/src/box/sql.c
index d48c3cf..4558f7d 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -48,6 +48,7 @@
 #include "txn.h"
 #include "space.h"
 #include "space_def.h"
+#include "sequence.h"
 #include "index_def.h"
 #include "tuple.h"
 #include "fiber.h"
@@ -1794,3 +1795,13 @@ sql_checks_resolve_space_def_reference(ExprList *expr_list,
 	sql_parser_destroy(&parser);
 	return rc;
 }
+
+void
+set_last_insert_id(struct space *space)
+{
+	struct session *session = current_session();
+	session->last_insert_id = 0;
+	struct sequence *sequence = space->sequence;
+	if(sequence != NULL)
+		session->last_insert_id = sequence_get_value(sequence);
+}
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index e211de1..3670315 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -37,6 +37,7 @@
 #include "sqliteInt.h"
 #include "vdbeInt.h"
 #include "version.h"
+#include "src/box/session.h"
 #include <unicode/ustring.h>
 #include <unicode/ucasemap.h>
 #include <unicode/ucnv.h>
@@ -591,6 +592,17 @@ changes(sqlite3_context * context, int NotUsed, sqlite3_value ** NotUsed2)
 }
 
 /*
+ */
+static void
+last_insert_id(sqlite3_context *context, int not_used,
+	       sqlite3_value **not_used2)
+{
+	struct session *session = current_session();
+	UNUSED_PARAMETER2(not_used, not_used2);
+	sqlite3_result_int(context, session->last_insert_id);
+}
+
+/*
  * Implementation of the total_changes() SQL function.  The return value is
  * the same as the sqlite3_total_changes() API function.
  */
@@ -1839,6 +1851,7 @@ sqlite3RegisterBuiltinFunctions(void)
 		FUNCTION(lower, 1, 0, 0, LowerICUFunc),
 		FUNCTION(hex, 1, 0, 0, hexFunc),
 		FUNCTION2(ifnull, 2, 0, 0, noopFunc, SQLITE_FUNC_COALESCE),
+		VFUNCTION(last_insert_id, 0, 0, 0, last_insert_id),
 		VFUNCTION(random, 0, 0, 0, randomFunc),
 		VFUNCTION(randomblob, 1, 0, 0, randomBlob),
 		FUNCTION(nullif, 2, 0, 1, nullifFunc),
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index b595984..2e4fb74 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -4863,4 +4863,14 @@ void
 vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
 			   const char *idx_name, const char *table_name);
 
+/**
+ * Set last_insert_id of current session as ID of first inserted
+ * tuple in last INSERT query executed in current session.
+ *
+ * @param space space to get sequence from.
+ */
+void
+set_last_insert_id(struct space *space);
+
+
 #endif				/* SQLITEINT_H */
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 2144d95..4ecb0bb 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -599,6 +599,7 @@ int sqlite3VdbeExec(Vdbe *p)
 	u64 start;                 /* CPU clock count at start of opcode */
 #endif
 	struct session *user_session = current_session();
+	bool last_insert_id_is_set = false;
 	/*** INSERT STACK UNION HERE ***/
 
 	assert(p->magic==VDBE_MAGIC_RUN);  /* sqlite3_step() verifies this */
@@ -4335,14 +4336,19 @@ case OP_IdxInsert: {        /* in2 */
 		if (pBtCur->curFlags & BTCF_TaCursor) {
 			/* Make sure that memory has been allocated on region. */
 			assert(aMem[pOp->p2].flags & MEM_Ephem);
-			if (pOp->opcode == OP_IdxInsert)
+			if (pOp->opcode == OP_IdxInsert) {
 				rc = tarantoolSqlite3Insert(pBtCur->space,
 							    pIn2->z,
 							    pIn2->z + pIn2->n);
-			else
+				if (rc == 0 && !last_insert_id_is_set) {
+					set_last_insert_id(pBtCur->space);
+					last_insert_id_is_set = true;
+				}
+			} else {
 				rc = tarantoolSqlite3Replace(pBtCur->space,
 							     pIn2->z,
 							     pIn2->z + pIn2->n);
+			}
 		} else if (pBtCur->curFlags & BTCF_TEphemCursor) {
 			rc = tarantoolSqlite3EphemeralInsert(pBtCur->space,
 							     pIn2->z,
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index c0e9d95..8394cd9 100755
--- a/test/sql-tap/insert3.test.lua
+++ b/test/sql-tap/insert3.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(18)
+test:plan(20)
 
 --!./tcltestrunner.lua
 -- 2005 January 13
@@ -262,6 +262,31 @@ test:do_execsql_test(
 		-- <insert3-4.1>
 })
 
+-- gh-2618 function last_insert_id
+test:execsql("CREATE TABLE t8(id INTEGER PRIMARY KEY AUTOINCREMENT, a INT);")
+test:do_execsql_test(
+    "insert3-5.1",
+    [[
+        INSERT INTO t8 VALUES (null, 11);
+        SELECT LAST_INSERT_ID();
+    ]], {
+        -- <insert3-5.1>
+        1
+        -- <insert3-5.1>
+})
+
+test:do_execsql_test(
+    "insert3-5.2",
+    [[
+        INSERT INTO t8 VALUES (null, 44), (null, 55), (null, 66);
+        SELECT LAST_INSERT_ID();
+    ]], {
+        -- <insert3-5.1>
+        2
+        -- <insert3-5.1>
+})
+
+
 test:drop_all_tables()
 ---------------------------------------------------------------------------
 -- While developing tests for a different feature (savepoint) the following
-- 
2.7.4

[tarantool-patches] Re: [PATCH v1 1/1] sql: add function last_insert_id

[Vladislav Shpilevoy]

Hi! Thanks for the patch! I will review the next version with
new IPROTO key.

On 31/07/2018 16:52, imeevma@tarantool.org wrote:
> Function last_insert_id() returns index of first
> inserted tuple in last INSERT query in current
> session. It takes no arguments.
> 
> Part of #2618.
> ---
> Branch: https://github.com/tarantool/tarantool/tree/imeevma/gh-2618-return-generated-columns-and-values
> Issue: https://github.com/tarantool/tarantool/issues/2618
>