Prepared statements in SQL

[TarantoolBot]

Now it is possible to prepare (i.e. compile into byte-code and save to
the cache) statement and execute it several times. Mechanism is similar
to ones in other DBs. Prepared statement is identified by numeric
ID, which are returned alongside with prepared statement handle.
Note that they are not sequential and represent value of hash function
applied to the string containing original SQL request.
Prepared statement holder is shared among all sessions. However, session
has access only to statements which have been prepared in scope of it.
There's no eviction policy like in any cache; to remove statement from
holder explicit unprepare request is required. Alternatively, session's
disconnect also removes statements from holder.
Several sessions can share one prepared statement, which will be
destroyed when all related sessions are disconnected or send unprepare
request. Memory limit for prepared statements is adjusted by
box.cfg{sql_cache_size} handle (can be set dynamically;

Any DDL operation leads to expiration of all prepared statements: they
should be manually removed or re-prepared.
Prepared statements are available in local mode (i.e. via box.prepare()
function) and are supported in IProto protocol. In the latter case
next IProto keys are used to make up/receive requests/responses:
IPROTO_PREPARE - new IProto command; key is 0x13. It can be sent with
one of two mandatory keys: IPROTO_SQL_TEXT (0x40 and assumes string value)
or IPROTO_STMT_ID (0x43 and assumes integer value). Depending on body it
means to prepare or unprepare SQL statement: IPROTO_SQL_TEXT implies prepare
request, meanwhile IPROTO_STMT_ID - unprepare;
IPROTO_BIND_METADATA (0x33 and contains parameters metadata of type map)
and IPROTO_BIND_COUNT (0x34 and corresponds to the count of parameters to
be bound) are response keys. They are mandatory members of result of
IPROTO_PREPARE execution.

To track statistics of used memory and number of currently prepared
statements, box.info is extended with SQL statistics:

box.info:sql().cache.stmt_count - number of prepared statements;
box.info:sql().cache.size - size of occupied by prepared statements memory.

Typical workflow with prepared statements is following:

s = box.prepare("SELECT * FROM t WHERE id = ?;")
s:execute({1}) or box.execute(s.sql_str, {1})
s:execute({2}) or box.execute(s.sql_str, {2})
s:unprepare() or box.unprepare(s.query_id)

Structure of object is following (member : type):

• stmt_id: integer
  execute: function
  params: map [name : string, type : integer]
  unprepare: function
  metadata: map [name : string, type : integer]
  param_count: integer
  ...

In terms of remote connection:

cn = netbox:connect(addr)
s = cn:prepare("SELECT * FROM t WHERE id = ?;")
cn:execute(s.sql_str, {1})
cn:unprepare(s.query_id)

Closes #2592
Requested by @Korablev77 in tarantool/tarantool@5a1a220.

[pgulutzan]

@Korablev77:
Done, with a few comments, ignore them totally if you wish:

COMMENT: "DDL" is poorly defined, I try to avoid it. As far as I can tell, only create and drop and alter cause expiry; grant and truncate() do not cause expiry.

COMMENT: I wish the names were different. "Prepared statement cache" is okay, "holder" is unnecessary, sql_cache_size is inconsistent (assuming the only thing in the cache is prepared statements, prepared_statement_cache would be more consistent with other terms). "Deallocate" is a standard word, "unprepare" is not.

COMMENT: I decided sql_cache_size is a "basic" parameter.

COMMENT: Re IPROTO_PREPARE etc. -- I listed all the new protocol items, but did not describe. Listing without describing is normal for the internals section.

COMMENT: I did not do anything with "Several sessions can share one prepared statement". This is an internals matter, it doesn't affect how users perceive the operation, it may change.

[Korablev77]

@pgulutzan Thanks a lot for your comments.

COMMENT: "DDL" is poorly defined, I try to avoid it. As far as I can tell, only create and drop and alter cause expiry; grant and truncate() do not cause expiry.

Fair, will avoid using it.

COMMENT: I wish the names were different. "Prepared statement cache" is okay, "holder" is unnecessary, sql_cache_size is inconsistent (assuming the only thing in the cache is prepared statements, prepared_statement_cache would be more consistent with other terms). "Deallocate" is a standard word, "unprepare" is not.

I use "holder" just like a synonymous to "prepared statement cache". As for sql_cache_size and unprepare I guess we may consider renaming them.

COMMENT: I decided sql_cache_size is a "basic" parameter.

Can't get what "basic" means..

COMMENT: Re IPROTO_PREPARE etc. -- I listed all the new protocol items, but did not describe. Listing without describing is normal for the internals section.

Ok (I was asked in one of review iteration to put descriptions of these flags, so that did it).

COMMENT: I did not do anything with "Several sessions can share one prepared statement". This is an internals matter, it doesn't affect how users perceive the operation, it may change.

Ok.

[pgulutzan]

Thanks for reading. Re what "basic" means: sorry, I should have used more words. I meant that I was putting it in the "Basic parameters" section of the configuration reference https://www.tarantool.io/en/doc/2.3/reference/configuration/#basic-parameters

[Totktonada]

COMMENT: Re IPROTO_PREPARE etc. -- I listed all the new protocol items, but did not describe. Listing without describing is normal for the internals section.

@pgulutzan Our binary protocol is public and we describe it for connector writers (inside and outside of our team). It is important for developers from community.

I always was wonder why the binary protocol description is so sketchy (in some areas) and why I need to dive into tarantool's source code to deduce it (note: it is easier for me than for external developers). Now I see that it is due to misunderstanding. It is not internal thing, it is documentation for connectors developers.

Say, author of https://github.com/tarantool-php/client often asks questions and file issues re documentation.

Please, describe the new parts of the protocol.

[pgulutzan]

@lenkis, I do not object to what @Totktonada says, but K. Osipov told me long ago that the documentation that I wrote about internals and connectors was sufficient, and I should not do more. So adding more about the protocol (perhaps in a connectors section not the box.prepare section) requires your approval.

[lenkis]

@pgulutzan this will add value, I think.

@kyukhin do you agree to expose the info to public?

[kyukhin]

I agree.

[pgulutzan]

Okay, but it doesn't belong in box.prepare().
We have an Internals section with sub-sections "Binary protocol" and "SQL protocol".
I will expand it.

[pgulutzan]

I rewrote the "Binary protocol" section and removed the "SQL protocol" section (it is now just part of Binary protocol), in version-2.3 and version-2.4 documentation. So I am closing this. If there is anything wrong with the new "Binary protocol" description, I believe it should be addressed as a different issue.