Litedb: SURVEY: SQL-Like or Shell command syntax

[mbdavid]

Hello everyone!

I'm writing a new version of LiteDB with better support to string command parser. This language can be used in external tools or via Run method. Also, in new v5 server-mode, this language will be used in REST API and any other protocol to transfer command. SQL syntax are simple and easy do adopt because everyone know SQL. Read reference are almost not needed.

Some examples:

Using SQL-Like syntax

-- Simple data select
SELECT *
  FROM customers
 WHERE name = 'John'

-- Add data transformation
SELECT *,
       phones[type = 'mobile'].number AS mobile
  FROM customers

-- Full query syntax  
 SELECT *
   INTO new_col
   FROM customers
  WHERE _id BETWEEN 0 AND 100
    AND name = "John"
INCLUDE orders, address
  ORDER BY YEAR(birthday)
  LIMIT 100
 OFFSET 10

-- Select with group by 
SELECT key AS age
       COUNT(values) AS counter
  FROM customers
 GROUP BY YEAR(birthday)

-- Creating an index
CREATE UNIQUE INDEX idx_0 ON customers (LOWER(email))

-- Insert data
INSERT INTO customers 
     VALUES { name: "John", year: 2018 } 
         ID INT

-- Update         
UPDATE custumers
   SET name = UPPER(name)
  WHERE year < 2018

And here, same Shell syntax

-- Simple data select
db.customers.select $
              where name = 'John'

-- Add data transformation
db.customers.select EXTEND($, { mobile: phones[type = 'mobile'].number })

-- Full query syntax  
db.customers.select $
               into new_col
              where _id between [0, 100]
                and name = "John"
            include orders, address
              order by YEAR(birthday)
              limit 100
             offset 10

-- Select with group by 
db.customers.select { age: key, counter: COUNT(values) }
              group by YEAR(birthday)

-- Creating an index
db.customers.createIndex idx_0 { expr: LOWER(email), unique: true }

-- Insert data
db.customers.insert { name: "John", year: 2018 } id:int

-- Update         
db.custumers.update EXTEND($, { name: UPPER(name) })
              where year < 2018

Can vote with:
❤️ for SQL
👍 for Shell

Any comment are welcome!

[xied75]

Is SQL syntax more useful if we are thinking to create a full featured SQL Server Management Studio Alike thing? Just wondering how much workload this will bring you.

Are you going to use tools like Antlr4 to parse the SQL or you have some other plans? I could help if you decide to use Antlr4.
https://github.com/antlr/grammars-v4/tree/master/tsql

[mbdavid]

Hi @xied75, current version already support shell parser command using simple solution (StringScanner with Regex). Migrate do SQL is not easy, but final result could be nice.

My goal with LiteDB v5 is a more useful database not only for small solutions or embedded database. With server mode, LiteDB could be access datafiles using HTTP protocol in other languages (like web client app in javascript). That's why command mode are so important in this case.

[xied75]

Parser wise, Regex is not powerful enough. You definitely need a proper full featured Parser to handle SQL/TSQL. Antlr4 is a Parser generator, which has runtime in C#.

What about IQueryable Interface then?

Are you working in mid night?

[mbdavid]

IQueryable will be possible after this implementation - execute any database operation using string command. LiteDatabase will be run as a client only for multiple engine communication (like direct - with use as embedded, TCP, REST, IPC, ...). This client layer will support focused in convert classes into document and linq manipulation.

[ghost]

hello david
i have simple way to sql commands
its can create static and dynamic query
the transaction is most important thing in server side or also client side
see this : https://github.com/miladsadeghi72/Sql-Command-Builder-Interface

[mstum]

I don't really care too much (i'm using LiteDB _because_ i can fully embed it in an app and not have an external server), but SQL Syntax is definitely very familiar. FWIW, I never liked the LINQ Query syntax and always preferred the method syntax, one off script languages have a steeper learning curve.

But I think that LiteRepository is an awesome way, it's basically an ORM for LiteDB and makes usage really easy, if you add client/server functionality it would be cool if LiteRepository could stay and convert the queries instead.

[ghost]

hi
i know about clients and servers but i must refer to somethings

first sql commands not just for servers so beter see this page :

https://www.sqlite.org/whentouse.html
second sql commands is interface to any database but light db don't separate database and interface
this is a big problem in software industry

[janantos]

HI,
I would like to keep LiteDB simple. server mode can be implemented on top of it as separate project, myself thinking about creating oData interface for LiteDB

[mbdavid]

Hi @janantos, my first idea was keep all together but I change my mind when next version grows a lot. So, I will keep this feature as a external plugin... and will be deliver only after final v5 version

[xied75]

@mbdavid Do you have a diagram showing the new architecture of components/plug-ins?

[ghost]

@mbdavid i made this interface and can run for any direct action databases
this is my current project, i can share it if you want

[mbdavid]

Hi @xied75 - it's not final defined yet, but I made some substantial changes in class structures. Now, engine classes are disconected from client and comunication are made by ILiteEngine and IBsonDataReader. This structure is be important to be transparent for client user access direct (embedded) datafile or via TCP/REST

[mbdavid]

@miladsadeghi72 sure, it's always great to know how others are using and thinks about litedb. This project are open source? is in your github?

[ghost]

@mbdavid , its not in GitHub, its our secret project but GitHub always use our products for free and that don't care about license and so on, today Microsoft shopped GitHub and its mean money always have rights more than true

however i'll share it on github because i respect you
this project is very basic, and just is interface, in fact that is contain policy for use any databases Directly Via Read and Write

ok i'll share it as soon as possible

[lbnascimento]

Hi! With the objective of organizing our issues, we are closing old unsolved issues. Please check the latest version of LiteDB and open a new issue if your problem/question/suggestion still applies. Thanks!