Keep in mind security, e.g. SQL injections. Ideally the API should be designed
in such a way that the programmer cannot use it wrongly and allow sql
injections.
> On 7 Feb 2017, at 12:34, Vladimir Ozerov <voze...@gridgain.com> wrote:
>
> Igniters,
>
> Our SQL engine becomes more and more sophisticated. Initially we had only
> SELECTs, now we have DML, in AI 2.x we will have DDL.
>
> This is very cool, but it seems that we completely forgot about extending
> our native SQL API (IgniteCache.query, SqlQuery, SqlFieldsQuery) in
> response to these changes. For example:
>
> 1) How one should execute DML/DDL command and validate the result?
> int updateCnt = IgniteCache.query(SqlQuery).getAll().get(0);
>
> Counter-intuitive and too verbose.
>
> 2) How one should perform batched operations? One by one?
> IgniteCache.query(SqlQuery.setArgs(1));
> IgniteCache.query(SqlQuery.setArgs(2));
> ...
> IgniteCache.query(SqlQuery.setArgs(N));
>
> I think it is time to rework our API so that it supports all recent
> features in consistent way and is extensible enough for future improvements
> (e.g. transactional SQL).
>
> Probably we can take ideas behind JDBC standard as starting point and move
> SQL to separate API. Several very rough examples:
>
> 1) Getting facade:
> IgniteSql sql = ignite.sql("MY_SCHEMA");
>
> 2) Running SELECT:
> QueryCursor<K, V> cursor = sql.select(SqlCommand);
> QueryCursor<List<?>> cursor = sql.selectFields(SqlCommand); // No more need
> for separate SqlQuery and SqlQueryFields classes.
>
> 3) Running DML/DDL:
> SqlCommandResult res = sql.execute(SqlCommand);
> or
> int updatedCnt = sql.execute(SqlCommand);
>
> 4) Running batch commands:
> SqlCommand cmd = new SqlCommand(...).addBatch(arg1).addBatch(arg2);
> SqlCommandResult res = sql.execute(cmd);
>
> 5) Re-use query parsing results (PreparedStatement):
> SqlCommand qry = sql.prepare("SELECT ...");
>
> Our JDBC driver is not applicable here because it is either not peformant
> enough (V1), or starts unnecessary client inside (V2).
>
> Thoughts? Does anyone else think it is time to re-approach SQL API?
>
> Vladimir.
