From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp54.i.mail.ru (smtp54.i.mail.ru [217.69.128.34]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 50BD246970F for ; Sun, 24 Nov 2019 07:07:15 +0300 (MSK) Date: Sun, 24 Nov 2019 07:07:10 +0300 From: Alexander Turenko Message-ID: <20191124040710.d232movrzjefducx@tkn_work_nb> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Subject: [Tarantool-discussions] Reusable SQL parser List-Id: Tarantool development process List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-discussions@dev.tarantool.org 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.