[Tarantool-patches] [PATCH v3 19/20] netbox: introduce prepared statements

Sergey Ostanevich sergos at tarantool.org
Wed Dec 25 23:41:39 MSK 2019


Hi!

Thanks for the patch, one nit and one question.

Regards,
Sergos



On 20 Dec 15:47, Nikita Pettik wrote:
> This patch introduces support of prepared statements in IProto
> protocol. To achieve this new IProto command is added - IPROTO_PREPARE
> (key is 0x13). It is 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.  Also to reply on PREPARE request a
> few response keys are added: 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).
> 
> Part of #2592
> ---
>  src/box/execute.c          |  83 ++++++++++++++++++++++++++++++
>  src/box/iproto.cc          |  68 +++++++++++++++++++++----
>  src/box/iproto_constants.c |   7 ++-
>  src/box/iproto_constants.h |   5 ++
>  src/box/lua/net_box.c      |  98 +++++++++++++++++++++++++++++++++--
>  src/box/lua/net_box.lua    |  27 ++++++++++
>  src/box/xrow.c             |  23 +++++++--
>  src/box/xrow.h             |   4 +-
>  test/box/misc.result       |   1 +
>  test/sql/engine.cfg        |   1 +
>  test/sql/iproto.result     |   2 +-
>  test/sql/prepared.result   | 124 ++++++++++++++++++++++++++-------------------
>  test/sql/prepared.test.lua |  76 +++++++++++++++++++--------
>  13 files changed, 420 insertions(+), 99 deletions(-)
> 
> diff --git a/src/box/execute.c b/src/box/execute.c
> index 09224c23a..7174d0d41 100644
> --- a/src/box/execute.c
> +++ b/src/box/execute.c
> @@ -328,6 +328,68 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
>  	return 0;
>  }
>  
> +static inline int
> +sql_get_params_metadata(struct sql_stmt *stmt, struct obuf *out)
> +{
> +	int bind_count = sql_bind_parameter_count(stmt);
> +	int size = mp_sizeof_uint(IPROTO_BIND_METADATA) +
> +		   mp_sizeof_array(bind_count);
> +	char *pos = (char *) obuf_alloc(out, size);
> +	if (pos == NULL) {
> +		diag_set(OutOfMemory, size, "obuf_alloc", "pos");
> +		return -1;
> +	}
> +	pos = mp_encode_uint(pos, IPROTO_BIND_METADATA);
> +	pos = mp_encode_array(pos, bind_count);
> +	for (int i = 0; i < bind_count; ++i) {
> +		size_t size = mp_sizeof_map(2) +
> +			      mp_sizeof_uint(IPROTO_FIELD_NAME) +
> +			      mp_sizeof_uint(IPROTO_FIELD_TYPE);
> +		const char *name = sql_bind_parameter_name(stmt, i);
> +		if (name == NULL)
> +			name = "?";
> +		const char *type = "ANY";
> +		size += mp_sizeof_str(strlen(name));
> +		size += mp_sizeof_str(strlen(type));
> +		char *pos = (char *) obuf_alloc(out, size);
> +		if (pos == NULL) {
> +			diag_set(OutOfMemory, size, "obuf_alloc", "pos");
> +			return -1;
> +		}
> +		pos = mp_encode_map(pos, 2);
> +		pos = mp_encode_uint(pos, IPROTO_FIELD_NAME);
> +		pos = mp_encode_str(pos, name, strlen(name));
> +		pos = mp_encode_uint(pos, IPROTO_FIELD_TYPE);
> +		pos = mp_encode_str(pos, type, strlen(type));
> +	}
> +	return 0;
> +}
> +
> +static int
> +sql_get_prepare_common_keys(struct sql_stmt *stmt, struct obuf *out, int keys)
> +{
> +	const char *sql_str = sql_stmt_query_str(stmt);
> +	uint32_t stmt_id = sql_stmt_calculate_id(sql_str, strlen(sql_str));
> +	int size = mp_sizeof_map(keys) +
> +		   mp_sizeof_uint(IPROTO_STMT_ID) +
> +		   mp_sizeof_uint(stmt_id) +
> +		   mp_sizeof_uint(IPROTO_BIND_COUNT) +
> +		   mp_sizeof_uint(sql_bind_parameter_count(stmt));
> +	char *pos = (char *) obuf_alloc(out, size);
> +	if (pos == NULL) {
> +		diag_set(OutOfMemory, size, "obuf_alloc", "pos");
> +		return -1;
> +	}
> +	pos = mp_encode_map(pos, keys);
> +	pos = mp_encode_uint(pos, IPROTO_STMT_ID);
> +	pos = mp_encode_uint(pos, stmt_id);
> +	pos = mp_encode_uint(pos, IPROTO_BIND_COUNT);
> +	pos = mp_encode_uint(pos, sql_bind_parameter_count(stmt));
> +	if (sql_get_params_metadata(stmt, out) != 0)
> +		return -1;
> +	return 0;
> +}
> +
>  static int
>  port_sql_dump_msgpack(struct port *port, struct obuf *out)
>  {
> @@ -409,6 +471,27 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out)
>  		}
>  		break;
>  	}
> +	case DQL_PREPARE: {
> +		/* Format is following:
> +		 * query_id,
> +		 * param_count,
> +		 * params {name, type},
> +		 * metadata {name, type}
> +		 */
> +		int keys = 4;
> +		if (sql_get_prepare_common_keys(stmt, out, keys) != 0)
> +			return -1;
> +		return sql_get_metadata(stmt, out, sql_column_count(stmt));
> +	}
> +	case DML_PREPARE: {
> +		/* Format is following:
> +		 * query_id,
> +		 * param_count,
> +		 * params {name, type},
> +		 */
> +		int keys = 3;
> +		return sql_get_prepare_common_keys(stmt, out, keys);
> +		}
>  	default: {
>  		unreachable();
>  	}
> diff --git a/src/box/iproto.cc b/src/box/iproto.cc
> index c39b8e7bf..fac94658a 100644
> --- a/src/box/iproto.cc
> +++ b/src/box/iproto.cc
> @@ -178,7 +178,7 @@ struct iproto_msg
>  		struct call_request call;
>  		/** Authentication request. */
>  		struct auth_request auth;
> -		/* SQL request, if this is the EXECUTE request. */
> +		/* SQL request, if this is the EXECUTE/PREPARE request. */
>  		struct sql_request sql;
>  		/** In case of iproto parse error, saved diagnostics. */
>  		struct diag diag;
> @@ -1209,6 +1209,7 @@ static const struct cmsg_hop *dml_route[IPROTO_TYPE_STAT_MAX] = {
>  	call_route,                             /* IPROTO_CALL */
>  	sql_route,                              /* IPROTO_EXECUTE */
>  	NULL,                                   /* IPROTO_NOP */
> +	sql_route,                              /* IPROTO_PREPARE */
>  };
>  
>  static const struct cmsg_hop join_route[] = {
> @@ -1264,6 +1265,7 @@ iproto_msg_decode(struct iproto_msg *msg, const char **pos, const char *reqend,
>  		cmsg_init(&msg->base, call_route);
>  		break;
>  	case IPROTO_EXECUTE:
> +	case IPROTO_PREPARE:
>  		if (xrow_decode_sql(&msg->header, &msg->sql) != 0)
>  			goto error;
>  		cmsg_init(&msg->base, sql_route);
> @@ -1710,23 +1712,64 @@ tx_process_sql(struct cmsg *m)
>  	int bind_count = 0;
>  	const char *sql;
>  	uint32_t len;
> +	bool is_unprepare = false;
>  
>  	tx_fiber_init(msg->connection->session, msg->header.sync);
>  
>  	if (tx_check_schema(msg->header.schema_version))
>  		goto error;
> -	assert(msg->header.type == IPROTO_EXECUTE);
> +	assert(msg->header.type == IPROTO_EXECUTE ||
> +	       msg->header.type == IPROTO_PREPARE);
>  	tx_inject_delay();
>  	if (msg->sql.bind != NULL) {
>  		bind_count = sql_bind_list_decode(msg->sql.bind, &bind);
>  		if (bind_count < 0)
>  			goto error;
>  	}
> -	sql = msg->sql.sql_text;
> -	sql = mp_decode_str(&sql, &len);
> -	if (sql_prepare_and_execute(sql, len, bind, bind_count, &port,
> -				    &fiber()->gc) != 0)
> -		goto error;
> +	/*
> +	 * There are four options:
> +	 * 1. Prepare SQL query (IPROTO_PREPARE + SQL string);
> +	 * 2. Unprepare SQL query (IPROTO_PREPARE + stmt id);
> +	 * 3. Execute SQL query (IPROTO_EXECUTE + SQL string);
> +	 * 4. Execute prepared query (IPROTO_EXECUTE + stmt id).
> +	 */
> +	if (msg->header.type == IPROTO_EXECUTE) {
> +		if (msg->sql.sql_text != NULL) {
> +			assert(msg->sql.stmt_id == NULL);
> +			sql = msg->sql.sql_text;
> +			sql = mp_decode_str(&sql, &len);
> +			if (sql_prepare_and_execute(sql, len, bind, bind_count,
> +						    &port, &fiber()->gc) != 0)
> +				goto error;
> +		} else {
> +			assert(msg->sql.sql_text == NULL);
> +			assert(msg->sql.stmt_id != NULL);
> +			sql = msg->sql.stmt_id;
> +			uint32_t stmt_id = mp_decode_uint(&sql);
> +			if (sql_execute_prepared(stmt_id, bind, bind_count,
> +						 &port, &fiber()->gc) != 0)
> +				goto error;
> +		}
> +	} else {
> +		/* IPROTO_PREPARE */
> +		if (msg->sql.sql_text != NULL) {
> +			assert(msg->sql.stmt_id == NULL);
> +			sql = msg->sql.sql_text;
> +			sql = mp_decode_str(&sql, &len);
> +			if (sql_prepare(sql, len, &port) != 0)
> +				goto error;
> +		} else {
> +			/* UNPREPARE */
> +			assert(msg->sql.sql_text == NULL);
> +			assert(msg->sql.stmt_id != NULL);
> +			sql = msg->sql.stmt_id;
> +			uint32_t stmt_id = mp_decode_uint(&sql);
> +			if (sql_unprepare(stmt_id) != 0)
> +				goto error;
> +			is_unprepare = true;
> +		}
> +	}
> +
>  	/*
>  	 * Take an obuf only after execute(). Else the buffer can
>  	 * become out of date during yield.
> @@ -1738,12 +1781,15 @@ tx_process_sql(struct cmsg *m)
>  		port_destroy(&port);
>  		goto error;
>  	}
> -	if (port_dump_msgpack(&port, out) != 0) {
> +	/* Nothing to dump in case of UNPREPARE request. */
> +	if (! is_unprepare) {

Unary ops - no spaces.

> +		if (port_dump_msgpack(&port, out) != 0) {
> +			port_destroy(&port);
> +			obuf_rollback_to_svp(out, &header_svp);
> +			goto error;
> +		}
>  		port_destroy(&port);
> -		obuf_rollback_to_svp(out, &header_svp);
> -		goto error;
>  	}
> -	port_destroy(&port);
>  	iproto_reply_sql(out, &header_svp, msg->header.sync, schema_version);
>  	iproto_wpos_create(&msg->wpos, out);
>  	return;
> diff --git a/src/box/iproto_constants.c b/src/box/iproto_constants.c
> index 09ded1ecb..029d9888c 100644
> --- a/src/box/iproto_constants.c
> +++ b/src/box/iproto_constants.c
> @@ -107,6 +107,7 @@ const char *iproto_type_strs[] =
>  	"CALL",
>  	"EXECUTE",
>  	NULL, /* NOP */
> +	"PREPARE",
>  };
>  
>  #define bit(c) (1ULL<<IPROTO_##c)
> @@ -124,6 +125,7 @@ const uint64_t iproto_body_key_map[IPROTO_TYPE_STAT_MAX] = {
>  	0,                                                     /* CALL */
>  	0,                                                     /* EXECUTE */
>  	0,                                                     /* NOP */
> +	0,                                                     /* PREPARE */
>  };
>  #undef bit
>  
> @@ -179,8 +181,8 @@ const char *iproto_key_strs[IPROTO_KEY_MAX] = {
>  	"data",             /* 0x30 */
>  	"error",            /* 0x31 */
>  	"metadata",         /* 0x32 */
> -	NULL,               /* 0x33 */
> -	NULL,               /* 0x34 */
> +	"bind meta",        /* 0x33 */
> +	"bind count",       /* 0x34 */
>  	NULL,               /* 0x35 */
>  	NULL,               /* 0x36 */
>  	NULL,               /* 0x37 */
> @@ -195,6 +197,7 @@ const char *iproto_key_strs[IPROTO_KEY_MAX] = {
>  	"SQL text",         /* 0x40 */
>  	"SQL bind",         /* 0x41 */
>  	"SQL info",         /* 0x42 */
> +	"stmt id",          /* 0x43 */
>  };
>  
>  const char *vy_page_info_key_strs[VY_PAGE_INFO_KEY_MAX] = {
> diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
> index 5e8a7d483..34d0f49c6 100644
> --- a/src/box/iproto_constants.h
> +++ b/src/box/iproto_constants.h
> @@ -110,6 +110,8 @@ enum iproto_key {
>  	 * ]
>  	 */
>  	IPROTO_METADATA = 0x32,
> +	IPROTO_BIND_METADATA = 0x33,
> +	IPROTO_BIND_COUNT = 0x34,
>  
>  	/* Leave a gap between response keys and SQL keys. */
>  	IPROTO_SQL_TEXT = 0x40,
> @@ -120,6 +122,7 @@ enum iproto_key {
>  	 * }
>  	 */
>  	IPROTO_SQL_INFO = 0x42,
> +	IPROTO_STMT_ID = 0x43,
>  	IPROTO_KEY_MAX
>  };
>  
> @@ -203,6 +206,8 @@ enum iproto_type {
>  	IPROTO_EXECUTE = 11,
>  	/** No operation. Treated as DML, used to bump LSN. */
>  	IPROTO_NOP = 12,
> +	/** Prepare SQL statement. */
> +	IPROTO_PREPARE = 13,
>  	/** The maximum typecode used for box.stat() */
>  	IPROTO_TYPE_STAT_MAX,
>  
> diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
> index 001af95dc..aa8a15e30 100644
> --- a/src/box/lua/net_box.c
> +++ b/src/box/lua/net_box.c
> @@ -570,10 +570,16 @@ netbox_encode_execute(lua_State *L)
>  
>  	mpstream_encode_map(&stream, 3);
>  
> -	size_t len;
> -	const char *query = lua_tolstring(L, 3, &len);
> -	mpstream_encode_uint(&stream, IPROTO_SQL_TEXT);
> -	mpstream_encode_strn(&stream, query, len);
> +	if (lua_type(L, 3) == LUA_TNUMBER) {
> +		uint32_t query_id = lua_tointeger(L, 3);
> +		mpstream_encode_uint(&stream, IPROTO_STMT_ID);
> +		mpstream_encode_uint(&stream, query_id);
> +	} else {
> +		size_t len;
> +		const char *query = lua_tolstring(L, 3, &len);
> +		mpstream_encode_uint(&stream, IPROTO_SQL_TEXT);
> +		mpstream_encode_strn(&stream, query, len);
> +	}
>  
>  	mpstream_encode_uint(&stream, IPROTO_SQL_BIND);
>  	luamp_encode_tuple(L, cfg, &stream, 4);
> @@ -585,6 +591,32 @@ netbox_encode_execute(lua_State *L)
>  	return 0;
>  }
>  
> +static int
> +netbox_encode_prepare(lua_State *L)
> +{
> +	if (lua_gettop(L) < 3)
> +		return luaL_error(L, "Usage: netbox.encode_prepare(ibuf, "\
> +				     "sync, query)");
> +	struct mpstream stream;
> +	size_t svp = netbox_prepare_request(L, &stream, IPROTO_PREPARE);
> +
> +	mpstream_encode_map(&stream, 1);
> +
> +	if (lua_type(L, 3) == LUA_TNUMBER) {
> +		uint32_t query_id = lua_tointeger(L, 3);
> +		mpstream_encode_uint(&stream, IPROTO_STMT_ID);
> +		mpstream_encode_uint(&stream, query_id);
> +	} else {
> +		size_t len;
> +		const char *query = lua_tolstring(L, 3, &len);
> +		mpstream_encode_uint(&stream, IPROTO_SQL_TEXT);
> +		mpstream_encode_strn(&stream, query, len);
> +	};
> +
> +	netbox_encode_request(&stream, svp);
> +	return 0;
> +}
> +
>  /**
>   * Decode IPROTO_DATA into tuples array.
>   * @param L Lua stack to push result on.
> @@ -752,6 +784,62 @@ netbox_decode_execute(struct lua_State *L)
>  	return 2;
>  }
>  
> +static int
> +netbox_decode_prepare(struct lua_State *L)
> +{
> +	uint32_t ctypeid;
> +	const char *data = *(const char **)luaL_checkcdata(L, 1, &ctypeid);
> +	assert(mp_typeof(*data) == MP_MAP);
> +	uint32_t map_size = mp_decode_map(&data);
> +	int stmt_id_idx = 0, meta_idx = 0, bind_meta_idx = 0,
> +	    bind_count_idx = 0;
> +	uint32_t stmt_id = 0;
> +	for (uint32_t i = 0; i < map_size; ++i) {
> +		uint32_t key = mp_decode_uint(&data);
> +		switch(key) {
> +		case IPROTO_STMT_ID: {
> +			stmt_id = mp_decode_uint(&data);
> +			luaL_pushuint64(L, stmt_id);
> +			stmt_id_idx = i - map_size;
> +			break;
> +		}
> +		case IPROTO_METADATA: {
> +			netbox_decode_metadata(L, &data);
> +			meta_idx = i - map_size;
> +			break;
> +		}
> +		case IPROTO_BIND_METADATA: {
> +			netbox_decode_metadata(L, &data);
> +			bind_meta_idx = i - map_size;
> +			break;
> +		}
> +		default: {
> +			assert(key == IPROTO_BIND_COUNT);
> +			uint32_t bind_count = mp_decode_uint(&data);
> +			luaL_pushuint64(L, bind_count);
> +			bind_count_idx = i - map_size;
> +			break;
> +		}}
> +	}
> +	/* These fields must be present in response. */
> +	assert(stmt_id_idx * bind_meta_idx * bind_count_idx != 0);
> +	/* General meta is presented only in DQL responses. */
> +	lua_createtable(L, 0, meta_idx != 0 ? 4 : 3);
> +	lua_pushvalue(L, stmt_id_idx - 1);
> +	lua_setfield(L, -2, "stmt_id");
> +	lua_pushvalue(L, bind_count_idx - 1);
> +	lua_setfield(L, -2, "param_count");
> +	lua_pushvalue(L, bind_meta_idx - 1);
> +	lua_setfield(L, -2, "params");
> +	if (meta_idx != 0) {
> +		lua_pushvalue(L, meta_idx - 1);
> +		lua_setfield(L, -2, "metadata");
> +	}
> +
> +	*(const char **)luaL_pushcdata(L, ctypeid) = data;
> +	return 2;
> +}
> +
>  int
>  luaopen_net_box(struct lua_State *L)
>  {
> @@ -767,11 +855,13 @@ luaopen_net_box(struct lua_State *L)
>  		{ "encode_update",  netbox_encode_update },
>  		{ "encode_upsert",  netbox_encode_upsert },
>  		{ "encode_execute", netbox_encode_execute},
> +		{ "encode_prepare", netbox_encode_prepare},
>  		{ "encode_auth",    netbox_encode_auth },
>  		{ "decode_greeting",netbox_decode_greeting },
>  		{ "communicate",    netbox_communicate },
>  		{ "decode_select",  netbox_decode_select },
>  		{ "decode_execute", netbox_decode_execute },
> +		{ "decode_prepare", netbox_decode_prepare },
>  		{ NULL, NULL}
>  	};
>  	/* luaL_register_module polutes _G */
> diff --git a/src/box/lua/net_box.lua b/src/box/lua/net_box.lua
> index c2e1bb9c4..b4811edfa 100644
> --- a/src/box/lua/net_box.lua
> +++ b/src/box/lua/net_box.lua
> @@ -104,6 +104,8 @@ local method_encoder = {
>      upsert  = internal.encode_upsert,
>      select  = internal.encode_select,
>      execute = internal.encode_execute,
> +    prepare = internal.encode_prepare,
> +    unprepare = internal.encode_prepare,
>      get     = internal.encode_select,
>      min     = internal.encode_select,
>      max     = internal.encode_select,
> @@ -128,6 +130,8 @@ local method_decoder = {
>      upsert  = decode_nil,
>      select  = internal.decode_select,
>      execute = internal.decode_execute,
> +    prepare = internal.decode_prepare,
> +    unprepare = decode_nil,

should it be internal.decode_prepare?

>      get     = decode_get,
>      min     = decode_get,
>      max     = decode_get,
> @@ -1197,6 +1201,29 @@ function remote_methods:execute(query, parameters, sql_opts, netbox_opts)
>                           sql_opts or {})
>  end
>  
> +function remote_methods:prepare(query, parameters, sql_opts, netbox_opts)
> +    check_remote_arg(self, "prepare")
> +    if type(query) ~= "string" then
> +        box.error(box.error.SQL_PREPARE, "expected string as SQL statement")
> +    end
> +    if sql_opts ~= nil then
> +        box.error(box.error.UNSUPPORTED, "prepare", "options")
> +    end
> +    return self:_request('prepare', netbox_opts, nil, query)
> +end
> +
> +function remote_methods:unprepare(query, parameters, sql_opts, netbox_opts)
> +    check_remote_arg(self, "unprepare")
> +    if type(query) ~= "number" then
> +        box.error("query id is expected to be numeric")
> +    end
> +    if sql_opts ~= nil then
> +        box.error(box.error.UNSUPPORTED, "unprepare", "options")
> +    end
> +    return self:_request('unprepare', netbox_opts, nil, query, parameters or {},
> +                         sql_opts or {})
> +end
> +
>  function remote_methods:wait_state(state, timeout)
>      check_remote_arg(self, 'wait_state')
>      if timeout == nil then
> diff --git a/src/box/xrow.c b/src/box/xrow.c
> index 18bf08971..88f308be5 100644
> --- a/src/box/xrow.c
> +++ b/src/box/xrow.c
> @@ -576,9 +576,11 @@ error:
>  	uint32_t map_size = mp_decode_map(&data);
>  	request->sql_text = NULL;
>  	request->bind = NULL;
> +	request->stmt_id = NULL;
>  	for (uint32_t i = 0; i < map_size; ++i) {
>  		uint8_t key = *data;
> -		if (key != IPROTO_SQL_BIND && key != IPROTO_SQL_TEXT) {
> +		if (key != IPROTO_SQL_BIND && key != IPROTO_SQL_TEXT &&
> +		    key != IPROTO_STMT_ID) {
>  			mp_check(&data, end);   /* skip the key */
>  			mp_check(&data, end);   /* skip the value */
>  			continue;
> @@ -588,12 +590,23 @@ error:
>  			goto error;
>  		if (key == IPROTO_SQL_BIND)
>  			request->bind = value;
> -		else
> +		else if (key == IPROTO_SQL_TEXT)
>  			request->sql_text = value;
> +		else
> +			request->stmt_id = value;
>  	}
> -	if (request->sql_text == NULL) {
> -		xrow_on_decode_err(row->body[0].iov_base, end, ER_MISSING_REQUEST_FIELD,
> -			 iproto_key_name(IPROTO_SQL_TEXT));
> +	if (request->sql_text != NULL && request->stmt_id != NULL) {
> +		xrow_on_decode_err(row->body[0].iov_base, end, ER_INVALID_MSGPACK,
> +				   "SQL text and statement id are incompatible "\
> +				   "options in one request: choose one");
> +		return -1;
> +	}
> +	if (request->sql_text == NULL && request->stmt_id == NULL) {
> +		xrow_on_decode_err(row->body[0].iov_base, end,
> +				   ER_MISSING_REQUEST_FIELD,
> +				   tt_sprintf("%s or %s",
> +					      iproto_key_name(IPROTO_SQL_TEXT),
> +					      iproto_key_name(IPROTO_STMT_ID)));
>  		return -1;
>  	}
>  	if (data != end)
> diff --git a/src/box/xrow.h b/src/box/xrow.h
> index 60def2d3c..a4d8dc015 100644
> --- a/src/box/xrow.h
> +++ b/src/box/xrow.h
> @@ -526,12 +526,14 @@ int
>  iproto_reply_error(struct obuf *out, const struct error *e, uint64_t sync,
>  		   uint32_t schema_version);
>  
> -/** EXECUTE request. */
> +/** EXECUTE/PREPARE request. */
>  struct sql_request {
>  	/** SQL statement text. */
>  	const char *sql_text;
>  	/** MessagePack array of parameters. */
>  	const char *bind;
> +	/** ID of prepared statement. In this case @sql_text == NULL. */
> +	const char *stmt_id;
>  };
>  
>  /**
> diff --git a/test/box/misc.result b/test/box/misc.result
> index 90923f28e..79fd49442 100644
> --- a/test/box/misc.result
> +++ b/test/box/misc.result
> @@ -250,6 +250,7 @@ t;
>    - EVAL
>    - CALL
>    - ERROR
> +  - PREPARE
>    - REPLACE
>    - UPSERT
>    - AUTH
> diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg
> index a1b4b0fc5..e38bec24e 100644
> --- a/test/sql/engine.cfg
> +++ b/test/sql/engine.cfg
> @@ -10,6 +10,7 @@
>          "local": {"remote": "false"}
>      },
>      "prepared.test.lua": {
> +        "remote": {"remote": "true"},
>          "local": {"remote": "false"}
>      },
>      "*": {
> diff --git a/test/sql/iproto.result b/test/sql/iproto.result
> index 67acd0ac1..4dfbfce50 100644
> --- a/test/sql/iproto.result
> +++ b/test/sql/iproto.result
> @@ -119,7 +119,7 @@ cn:execute('select id as identifier from test where a = 5;')
>  -- netbox API errors.
>  cn:execute(100)
>  ---
> -- error: Syntax error near '100'
> +- error: Prepared statement with id 100 does not exist
>  ...
>  cn:execute('select 1', nil, {dry_run = true})
>  ---
> diff --git a/test/sql/prepared.result b/test/sql/prepared.result
> index bd37cfdd7..2f4983b00 100644
> --- a/test/sql/prepared.result
> +++ b/test/sql/prepared.result
> @@ -12,34 +12,49 @@ fiber = require('fiber')
>  -- Wrappers to make remote and local execution interface return
>  -- same result pattern.
>  --
> -test_run:cmd("setopt delimiter ';'")
> +is_remote = test_run:get_cfg('remote') == 'true'
>   | ---
> - | - true
>   | ...
> -execute = function(...)
> -    local res, err = box.execute(...)
> -    if err ~= nil then
> -        error(err)
> -    end
> -    return res
> -end;
> +execute = nil
>   | ---
>   | ...
> -prepare = function(...)
> -    local res, err = box.prepare(...)
> -    if err ~= nil then
> -        error(err)
> -    end
> -    return res
> -end;
> +prepare = nil
> + | ---
> + | ...
> +
> +test_run:cmd("setopt delimiter ';'")
>   | ---
> + | - true
>   | ...
> -unprepare = function(...)
> -    local res, err = box.unprepare(...)
> -    if err ~= nil then
> -        error(err)
> +if is_remote then
> +    box.schema.user.grant('guest','read, write, execute', 'universe')
> +    box.schema.user.grant('guest', 'create', 'space')
> +    cn = remote.connect(box.cfg.listen)
> +    execute = function(...) return cn:execute(...) end
> +    prepare = function(...) return cn:prepare(...) end
> +    unprepare = function(...) return cn:unprepare(...) end
> +else
> +    execute = function(...)
> +        local res, err = box.execute(...)
> +        if err ~= nil then
> +            error(err)
> +        end
> +        return res
> +    end
> +    prepare = function(...)
> +        local res, err = box.prepare(...)
> +        if err ~= nil then
> +            error(err)
> +        end
> +        return res
> +    end
> +    unprepare = function(...)
> +        local res, err = box.unprepare(...)
> +        if err ~= nil then
> +            error(err)
> +        end
> +        return res
>      end
> -    return res
>  end;
>   | ---
>   | ...
> @@ -128,31 +143,26 @@ execute(s.stmt_id, {1, 3})
>   |     type: string
>   |   rows: []
>   | ...
> -s:execute({1, 2})
> +
> +test_run:cmd("setopt delimiter ';'")
>   | ---
> - | - metadata:
> - |   - name: ID
> - |     type: integer
> - |   - name: A
> - |     type: number
> - |   - name: B
> - |     type: string
> - |   rows:
> - |   - [1, 2, '3']
> + | - true
>   | ...
> -s:execute({1, 3})
> +if not is_remote then
> +    res = s:execute({1, 2})
> +    assert(res ~= nil)
> +    res = s:execute({1, 3})
> +    assert(res ~= nil)
> +end;
>   | ---
> - | - metadata:
> - |   - name: ID
> - |     type: integer
> - |   - name: A
> - |     type: number
> - |   - name: B
> - |     type: string
> - |   rows: []
>   | ...
> -s:unprepare()
> +test_run:cmd("setopt delimiter ''");
>   | ---
> + | - true
> + | ...
> +unprepare(s.stmt_id)
> + | ---
> + | - null
>   | ...
>  
>  -- Test preparation of different types of queries.
> @@ -338,6 +348,7 @@ _ = prepare("SELECT a FROM test WHERE b = '3';")
>  s = prepare("SELECT a FROM test WHERE b = '3';")
>   | ---
>   | ...
> +
>  execute(s.stmt_id)
>   | ---
>   | - metadata:
> @@ -354,21 +365,21 @@ execute(s.stmt_id)
>   |   rows:
>   |   - [2]
>   | ...
> -s:execute()
> +test_run:cmd("setopt delimiter ';'")
>   | ---
> - | - metadata:
> - |   - name: A
> - |     type: number
> - |   rows:
> - |   - [2]
> + | - true
>   | ...
> -s:execute()
> +if not is_remote then
> +    res = s:execute()
> +    assert(res ~= nil)
> +    res = s:execute()
> +    assert(res ~= nil)
> +end;
>   | ---
> - | - metadata:
> - |   - name: A
> - |     type: number
> - |   rows:
> - |   - [2]
> + | ...
> +test_run:cmd("setopt delimiter ''");
> + | ---
> + | - true
>   | ...
>  unprepare(s.stmt_id)
>   | ---
> @@ -671,6 +682,13 @@ unprepare(s.stmt_id);
>   | - null
>   | ...
>  
> +if is_remote then
> +    cn:close()
> +    box.schema.user.revoke('guest', 'read, write, execute', 'universe')
> +    box.schema.user.revoke('guest', 'create', 'space')
> +end;
> + | ---
> + | ...
>  test_run:cmd("setopt delimiter ''");
>   | ---
>   | - true
> diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua
> index 49d2fb3ae..c464cc21a 100644
> --- a/test/sql/prepared.test.lua
> +++ b/test/sql/prepared.test.lua
> @@ -5,27 +5,40 @@ fiber = require('fiber')
>  -- Wrappers to make remote and local execution interface return
>  -- same result pattern.
>  --
> +is_remote = test_run:get_cfg('remote') == 'true'
> +execute = nil
> +prepare = nil
> +
>  test_run:cmd("setopt delimiter ';'")
> -execute = function(...)
> -    local res, err = box.execute(...)
> -    if err ~= nil then
> -        error(err)
> +if is_remote then
> +    box.schema.user.grant('guest','read, write, execute', 'universe')
> +    box.schema.user.grant('guest', 'create', 'space')
> +    cn = remote.connect(box.cfg.listen)
> +    execute = function(...) return cn:execute(...) end
> +    prepare = function(...) return cn:prepare(...) end
> +    unprepare = function(...) return cn:unprepare(...) end
> +else
> +    execute = function(...)
> +        local res, err = box.execute(...)
> +        if err ~= nil then
> +            error(err)
> +        end
> +        return res
>      end
> -    return res
> -end;
> -prepare = function(...)
> -    local res, err = box.prepare(...)
> -    if err ~= nil then
> -        error(err)
> +    prepare = function(...)
> +        local res, err = box.prepare(...)
> +        if err ~= nil then
> +            error(err)
> +        end
> +        return res
>      end
> -    return res
> -end;
> -unprepare = function(...)
> -    local res, err = box.unprepare(...)
> -    if err ~= nil then
> -        error(err)
> +    unprepare = function(...)
> +        local res, err = box.unprepare(...)
> +        if err ~= nil then
> +            error(err)
> +        end
> +        return res
>      end
> -    return res
>  end;
>  
>  test_run:cmd("setopt delimiter ''");
> @@ -46,9 +59,16 @@ s.params
>  s.param_count
>  execute(s.stmt_id, {1, 2})
>  execute(s.stmt_id, {1, 3})
> -s:execute({1, 2})
> -s:execute({1, 3})
> -s:unprepare()
> +
> +test_run:cmd("setopt delimiter ';'")
> +if not is_remote then
> +    res = s:execute({1, 2})
> +    assert(res ~= nil)
> +    res = s:execute({1, 3})
> +    assert(res ~= nil)
> +end;
> +test_run:cmd("setopt delimiter ''");
> +unprepare(s.stmt_id)
>  
>  -- Test preparation of different types of queries.
>  -- Let's start from DDL. It doesn't make much sense since
> @@ -111,10 +131,17 @@ space:replace{4, 5, '6'}
>  space:replace{7, 8.5, '9'}
>  _ = prepare("SELECT a FROM test WHERE b = '3';")
>  s = prepare("SELECT a FROM test WHERE b = '3';")
> +
>  execute(s.stmt_id)
>  execute(s.stmt_id)
> -s:execute()
> -s:execute()
> +test_run:cmd("setopt delimiter ';'")
> +if not is_remote then
> +    res = s:execute()
> +    assert(res ~= nil)
> +    res = s:execute()
> +    assert(res ~= nil)
> +end;
> +test_run:cmd("setopt delimiter ''");
>  unprepare(s.stmt_id)
>  
>  s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
> @@ -233,6 +260,11 @@ f2:join();
>  
>  unprepare(s.stmt_id);
>  
> +if is_remote then
> +    cn:close()
> +    box.schema.user.revoke('guest', 'read, write, execute', 'universe')
> +    box.schema.user.revoke('guest', 'create', 'space')
> +end;
>  test_run:cmd("setopt delimiter ''");
>  
>  box.cfg{sql_cache_size = 5 * 1024 * 1024}
> -- 
> 2.15.1
> 


More information about the Tarantool-patches mailing list