Tarantool discussions archive
 help / color / mirror / Atom feed
* [Tarantool-discussions] Reusable SQL parser
@ 2019-11-24  4:07 Alexander Turenko
  2019-12-04 17:38 ` Konstantin Osipov
  0 siblings, 1 reply; 4+ messages in thread
From: Alexander Turenko @ 2019-11-24  4:07 UTC (permalink / raw)
  To: tarantool-discussions; +Cc: Nick Karlov, Konstantin Nazarov

Discussions about ways to reuse Tarantool SQL parser in applications and
connectors arise from time to time. I want to share a very high-level proposal
how we can reuse the parser in an application and provide some examples we
heard from users.

Proposal
--------

Proposed to extract or expose the SQL parser from Tarantool to provide ability
to generate AST accessible from Lua in an application and make transformations
on the AST.

The AST can be evaluated lazily: when a node is accessed from a Lua land, then
a Lua object (cdata or userdata) is created.

Proposed to provide an ability to generate an SQL request back from this AST
rather then allow to execute an AST. This way looks more debuggable, while
performance considerations may be eliminated using prepared statements.

NB: Implementation of a Lua API to retrieve AST looks as the good first step
to draw a line between the parser and the executor and so looks as a step to
extract the parser into its own library in the future (to use in connectors).

Let's consider a few examples.

Transparent versioning
----------------------

Example of transformation that a user may want to implement is transparently
select a last version of an object. Let's define a table and fill it with
versioned data.

 | CREATE TABLE objects(rowid INTEGER PRIMARY KEY AUTOINCREMENT, id INTEGER, \
 |                      version INTEGER, value VARCHAR(100))
 | INSERT INTO objects VALUES (null, 1, 1, 'record 1 version 1')
 | INSERT INTO objects VALUES (null, 1, 2, 'record 1 version 2')
 | INSERT INTO objects VALUES (null, 1, 3, 'record 1 version 3')
 | INSERT INTO objects VALUES (null, 2, 1, 'record 2 version 1')
 | INSERT INTO objects VALUES (null, 2, 2, 'record 2 version 2')
 | INSERT INTO objects VALUES (null, 3, 2, 'record 3 version 1')

Then, when a user of an app want to select data from this table:

 | SELECT id, value FROM objects

we transparently apply a transformation to retrieve only last version of each
object:

 | SELECT o1.id, o1.value FROM objects o1 LEFT JOIN objects o2 \
 | on (o1.id = o2.id and o1.version < o2.version) WHERE o2.version IS NULL

Based on this SO answer: https://stackoverflow.com/a/1313293/1598057

Transparent sharding
--------------------

We may transparently split an SQL request on a sharding router to several
requests: a part will be executed on the router itself, other parts on certain
storages.

I have no certain example now, so consider this as a kind of second-tier goal
rather then motivating example.

WBR, Alexander Turenko.

^ permalink raw reply	[flat|nested] 4+ messages in thread

end of thread, other threads:[~2019-12-12  9:14 UTC | newest]

Thread overview: 4+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2019-11-24  4:07 [Tarantool-discussions] Reusable SQL parser Alexander Turenko
2019-12-04 17:38 ` Konstantin Osipov
2019-12-12  1:21   ` Alexander Turenko
2019-12-12  9:14     ` Konstantin Osipov

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox