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 5658D295AE for ; Mon, 27 Aug 2018 10:25:58 -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 ykqFYU4XWYIN for ; Mon, 27 Aug 2018 10:25:58 -0400 (EDT) Received: from smtp60.i.mail.ru (smtp60.i.mail.ru [217.69.128.40]) (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 D848D294A8 for ; Mon, 27 Aug 2018 10:25:57 -0400 (EDT) From: "n.pettik" Message-Id: <12075140-4CA8-4857-BB13-84286FF72498@tarantool.org> Content-Type: multipart/alternative; boundary="Apple-Mail=_E711A1D7-E2E3-4814-853C-B4DCC110E02C" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: [tarantool-patches] Re: [PATCH v3 2/2] sql: return last_insert_id via IPROTO Date: Mon, 27 Aug 2018 17:25:53 +0300 In-Reply-To: <48d0ec1183bfe373652eef3cf13fb70e6a631b80.1535107514.git.imeevma@gmail.com> References: <48d0ec1183bfe373652eef3cf13fb70e6a631b80.1535107514.git.imeevma@gmail.com> 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: Imeev Mergen --Apple-Mail=_E711A1D7-E2E3-4814-853C-B4DCC110E02C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > After this patch client will get last_insert_id > as additional metadata when he executes some > statements. Some statements? Lets write =E2=80=98when SQL statements are executed=E2=80= =99 or sort of. >=20 > Part of #2618 >=20 > @TarantoolBot document > Title: SQL function last_insert_id() and IPROTO key last_insert_id. > Function last_insert_id() returns first primary key value > autogenerated in last INSERT/REPLACE statement in current > session. Mention that it is set only for AUTOINCREMENT primary key. In other cases it will be 0. Moreover, if you copy this feature from MySQL, you can take part of its description: it is quite good documented there: https://dev.mysql.com/doc/refman/8.0/en/mysql-insert-id.html = The only doubt I have concerning this patch - last_insert_id() returns FIRST autogenerated id for last stmt, which in turn seems quite misleading (I expected that it returns LAST id of LAST = stmt). I known that MySQL uses exactly this variant, but should we follow this = way? > User can have more than one session and this > function will work properly for each one of them. Return > value of function is undetermined when more than one > INSERT/REPLACE statements executed asynchronously. > IPROTO key last_insert_id is a metadata returned through > IPROTO after such statements as INSERT, REPLACE, UPDATE > etc. Value of this key is equal to value returned by > function last_insert_id() executed after the statement. > Example: > CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER); > INSERT INTO test VALUES (NULL, 1); > SELECT last_insert_id(); >=20 > diff --git a/src/box/session.cc b/src/box/session.cc > index 64714cd..2b8dab9 100644 > --- a/src/box/session.cc > +++ b/src/box/session.cc > @@ -108,6 +108,7 @@ session_create(enum session_type type) > session->type =3D type; > session->sql_flags =3D default_flags; > session->sql_default_engine =3D SQL_STORAGE_ENGINE_MEMTX; > + session->sql_last_insert_id =3D 0; >=20 > /* For on_connect triggers. */ > credentials_init(&session->credentials, guest_user->auth_token, > diff --git a/src/box/session.h b/src/box/session.h > index df1dcbc..6ee22bc 100644 > --- a/src/box/session.h > +++ b/src/box/session.h > @@ -92,6 +92,11 @@ union session_meta { > struct session { > /** Session id. */ > uint64_t id; > + /** > + * First primary key autogenerated in last INSERT/REPLACE > + * statement in which primary key was generated. > + */ If AUTOINCEMENT is specified, otherwise it is 0. > + int64_t sql_last_insert_id; > /** SQL Tarantool Default storage engine. */ > uint8_t sql_default_engine; > /** SQL Connection flag for current user session */ > diff --git a/src/box/sql/func.c b/src/box/sql/func.c > index 45056a7..2607cc3 100644 > --- a/src/box/sql/func.c > +++ b/src/box/sql/func.c > @@ -38,6 +38,7 @@ > #include "vdbeInt.h" > #include "version.h" > #include "coll.h" > +#include "box/session.h" > #include > #include > #include > @@ -601,6 +602,23 @@ changes(sqlite3_context * context, int NotUsed, = sqlite3_value ** NotUsed2) > } >=20 > /* > + * Return first primary key autogenerated in last INSERT/REPLACE > + * statement in which primary key was generated in current > + * session. > + * > + * @param context Context being used. > + * @param not_used Unused. > + * @param not_used2 Unused. > + */ > +static void > +last_insert_id(sqlite3_context *context, int not_used, > + sqlite3_value **not_used2) Nitpick: use =E2=80=99struct=E2=80=99 prefixes. --Apple-Mail=_E711A1D7-E2E3-4814-853C-B4DCC110E02C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

After this patch client will get = last_insert_id
as additional metadata when he executes = some
statements.

Some = statements? Lets write =E2=80=98when SQL statements are executed=E2=80=99 = or sort of.


Part of = #2618

@TarantoolBot document
Title: SQL function last_insert_id() and IPROTO key = last_insert_id.
Function last_insert_id() returns first = primary key value
autogenerated in last INSERT/REPLACE = statement in current
session. =

Mention that it = is set only for AUTOINCREMENT primary key.
In other cases it = will be 0.
Moreover, if you copy this feature from MySQL, you = can take
part of its description: it is quite good documented = there:


User can have more than one session and this
function will work properly for each one of them. Return
value of function is undetermined when more than one
INSERT/REPLACE statements executed asynchronously.
IPROTO key last_insert_id is a metadata returned through
IPROTO after such statements as INSERT, REPLACE, UPDATE
etc. Value of this key is equal to value returned by
function last_insert_id() executed after the statement.
Example:
CREATE TABLE test (id INTEGER PRIMARY = KEY AUTOINCREMENT, a INTEGER);
INSERT INTO test VALUES = (NULL, 1);
SELECT last_insert_id();

diff --git a/src/box/session.cc b/src/box/session.cc
index 64714cd..2b8dab9 100644
--- a/src/box/session.cc
+++ b/src/box/session.cc
@@ = -108,6 +108,7 @@ session_create(enum session_type type)
= = session->type =3D type;
= session->sql_flags =3D default_flags;
= session->sql_default_engine =3D SQL_STORAGE_ENGINE_MEMTX;
+ = session->sql_last_insert_id =3D 0;

= = /* For on_connect triggers. */
= credentials_init(&session->credentials, = guest_user->auth_token,
diff --git a/src/box/session.h = b/src/box/session.h
index df1dcbc..6ee22bc 100644
--- a/src/box/session.h
+++ = b/src/box/session.h
@@ -92,6 +92,11 @@ union session_meta = {
struct session {
/** = Session id. */
uint64_t id;
+ /**
+ = * First primary key autogenerated in last INSERT/REPLACE
+ = * statement in which primary key was generated.
+ = */

If AUTOINCEMENT is specified, otherwise it is = 0.

+ int64_t sql_last_insert_id;
= /** SQL Tarantool Default storage engine. */
uint8_t = sql_default_engine;
/** SQL Connection flag for = current user session */
diff --git a/src/box/sql/func.c = b/src/box/sql/func.c
index 45056a7..2607cc3 100644
--- a/src/box/sql/func.c
+++ = b/src/box/sql/func.c
@@ -38,6 +38,7 @@
= #include "vdbeInt.h"
#include "version.h"
= #include "coll.h"
+#include "box/session.h"
= #include <unicode/ustring.h>
#include = <unicode/ucasemap.h>
#include = <unicode/ucnv.h>
@@ -601,6 +602,23 @@ = changes(sqlite3_context * context, int NotUsed, sqlite3_value ** = NotUsed2)
}

/*
+ * Return first primary key autogenerated in last = INSERT/REPLACE
+ * statement in which primary key was = generated in current
+ * session.
+ *
+ * @param context Context being used.
+ * = @param not_used Unused.
+ * @param not_used2 Unused.
+ */
+static void
+last_insert_id(sqlite3_context *context, int not_used,
+ =       sqlite3_value = **not_used2)

Nitpick: use =E2=80=99struct=E2=80=99 = prefixes.

= --Apple-Mail=_E711A1D7-E2E3-4814-853C-B4DCC110E02C--