Tarantool discussions archive
 help / color / mirror / Atom feed
From: Alexander Turenko <alexander.turenko@tarantool.org>
To: tarantool-discussions@dev.tarantool.org
Cc: Nick Karlov <karlovn@tarantool.org>,
	Konstantin Nazarov <racktear@tarantool.org>
Subject: [Tarantool-discussions] Reusable SQL parser
Date: Sun, 24 Nov 2019 07:07:10 +0300	[thread overview]
Message-ID: <20191124040710.d232movrzjefducx@tkn_work_nb> (raw)

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.

             reply	other threads:[~2019-11-24  4:07 UTC|newest]

Thread overview: 4+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2019-11-24  4:07 Alexander Turenko [this message]
2019-12-04 17:38 ` Konstantin Osipov
2019-12-12  1:21   ` Alexander Turenko
2019-12-12  9:14     ` Konstantin Osipov

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20191124040710.d232movrzjefducx@tkn_work_nb \
    --to=alexander.turenko@tarantool.org \
    --cc=karlovn@tarantool.org \
    --cc=racktear@tarantool.org \
    --cc=tarantool-discussions@dev.tarantool.org \
    --subject='Re: [Tarantool-discussions] Reusable SQL parser' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

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