Welcome to gluesql Discussions!

[panarch]

👋 Welcome!

We’re using Discussions as a place to connect with other members of our community. We hope that you:

• Ask questions you’re wondering about.
• Share ideas.
• Engage with other community members.
• Welcome others and are open-minded. Remember that this is a community we
  build together 💪.

To get started, comment below with an introduction of yourself and tell us about what you do with this community.

[willy610]

Me

Well, I'm Willy Svenningson, soon 73 years old, retired since 10 years. Have a doughter and grand kids.
Living in Gothenburg, Sweden. Degree in math.

I like to work with this because it seems possible to implement an SQL engine in Rust. Love the power of SQL on structured informtion. Alos fond a all kind of Visual Artifacts on software. Like Datamodells, Class/Object diagrams, Process Diagram, FSM, Grammars ...

My contribution to the project sofar is most writings on join, index and foreign keys and bug reports.

Look around

1. What stakeholders, besides participant, are their? Other git's, sponsors, communities, etc
2. What is the background, history, early contributors to gluesql? SQL glued to what?
3. What are the relationship to 'sled'
4. What are the plans?
5. Which are the target(s)?

What's next

A. IDE. I'm using VS Code on a Mac. The source on the git is only packed for lib building and always need some changes for integration test. I think some one could make a suggestion on other stucture. Start with requirements.

B. Meta-tables on index and foreign keys etc.

C. Derived tables. That includes temporary tables

D. Independent subquery caching as sets/bags. Not as relation

E. Join everywhere

F. Order by, distinct

G. select ... from tbla,tblb where ... If it's not implemented there should be some writings on how rewrite into joins. How will do that? Outside scoop with a set of explanations

H. The implementation of the SQL engine in Rust is impressive. But also complex.
In my opinion the very task/algoritms somtimes becomes invisible under all Rust specific costume like Rust 'smart' functions on iterators, all mix of Rust specific attributes like Cell, Impl, async, try etc. It would be nice if one person could write the algoritm and another put code into the framework. Or having an editor hiding certain attributes.

I. I think the heavy row iterators need some refactoring. Need some drawings, structs and relationships, to discuss around I think.

[panarch]

Thanks so much 👍
That's so good questions for me to start sharing.

More information about myself, here you can see

• https://www.linkedin.com/in/taehoon-moon-38433b57/

Look around

What stakeholders, besides participant, are their? Other git's, sponsors, communities, etc

It is an open source project which is served under Apache-2.0 license.
No sponsors for now, my all work hours are simply from my savings.

What is the background, history, early contributors to gluesql? SQL glued to what?

I started this project in Dec, 2019 and spent a whole year now.
Starting was simply from my interest.

• I wanted to make something using Rust.
• Interests on databases (mainly related to SQL)
• Also I wanted to make a SQL database which can be run in web browsers.

Those motivation leaded me to consider a SQL database which supports easily-swappable storage.
Reference storage engine is based on sled, but it is quite easy to make other custom storage engines.
I named the project as GlueSQL, meaning... it can really add SQL layer to any storage engine which supports read and write operations.
Anything can be a storage, sled, indexeddb in web, redis, git, discord, etc.

What are the relationship to 'sled'

sled is a reference storage engine of GlueSQL.
Because GlueSQL is written in Rust, I wanted to use a reference storage purely written in Rust, too.
So I chose sled.

What are the plans?

Implementing features what you mentioned in "What's next".
Main key features to implement are

• Index
• Foreign key
• Transaction

I've spent full-time for GlueSQL project for a single year.
If I keep full-time work then I can make those features very fast, but I'm not sure for the next year now.
I'm currently finding a way which I can concentrate on GlueSQL project and earn money at the same time.

Which are the target(s)?

For someone who wants to ..

• find a SQLite alternative
• make their custom storage engine (and it can be the best if they can even earn money from that)

[willy610]

Nice. More of that kind.

[panarch]

That looks so good, thanks for sharing :)

[willy610]

Achivments

Hej!

I have been working a lot the last month implementing some new things

1. SELECT DISTINCT ...
2. ORDER BY ...
3. Pretty printing of SELECT ...
4. AVG() in aggregate

But more powerful I think is the SELECT FROM

Today are some limitations, but now we got

select from file list like SELECT FROM A,B,C where each file could be

1. tablename
2. tablename1 join tablename2 on ....
3. (SELECT ... FROM tablename3) AS tablename4 (colN,,)

So here is an example

SELECT DISTINCT A_DERIVED_NAME.*, 
                SP.SNO 
FROM   (SELECT * 
        FROM   S 
        WHERE  STATUS >= 20) AS A_DERIVED_NAME (K1, K2, K3, K4) 
       RIGHT JOIN SP 
         ON SP.SNO = A_DERIVED_NAME.K1

• We have some filter condition inside the derived table. NOTE this could reduce the amount of rows to be checked!!!
• and a join

giving

+-------------------+-------------------+-------------------+-------------------+--------+
| A_DERIVED_NAME.K1 | A_DERIVED_NAME.K2 | A_DERIVED_NAME.K3 | A_DERIVED_NAME.K4 | SP.SNO |
+-------------------+-------------------+-------------------+-------------------+--------+
| null              | null              | null              | null              | S2     |
| S4                | Clark             |                20 | London            | S4     |
| S1                | Smith             |                20 | London            | S1     |
| S3                | Blake             |              1400 | Paris             | S3     |
+-------------------+-------------------+-------------------+-------------------+--------+

But...

In order to produce theese functions I almost broke my neck and actually got dizzy. I removed the async/wait (temporarily?) and also most function iterators. It's kind of progress i any case.
The amount of sources are like in a new folder under executor/

|____xfilter.rs
|____xorderby.rs
|____xevaluated.rs
|____xlimit.rs
|____.DS_Store
|____xmisc.rs
|____xgrpbyandaggr.rs
|____xproject.rs
|____xerror.rs
|____xdistinct.rs
|____xfetch.rs
|____xshowselect.rs
|____xselect.rs
|____xfromproj2relat.rs
|____mod.rs
|____xtable.rs
|____xevaluate.rs
|____xdisplay.rs
|____xloadrows.rs
|____xfrom.rs
|____xfoldjoins.rs

I hope this could contribute to the project but I am not sure how...

A zip file or a none-public github with inviting contributors from gluesql

(Definitely it can't be merged into gluesql)

willy

[panarch]

That sounds awesome, I hope to see the code in public or pr.

[willy610]

Hej.
Implemented the set operations UNION,EXCEPT and INTERSECT too.

One odd usage of UNION is when testing and for any reason don't having an existing table you can generate on the fly like:

select * from 
(
   SELECT 1 , 2.3, 'str' 
   UNION
   SELECT 7, 34.5, 'texten'
   UNION 
   SELECT 400,  3.14, 'pi'
) AS TESTTABLE (COL_INT, COL_FLOAT ,COL_TEXT)   
;

And an observation around optimizing selections. I did build a derived subquery to reduce the total row product. (The example is producing a buy-list of ingredients for a certain recipe)

SELECT Sum(s_amount), 
       s_unit, 
       ingredientname, 
       s_ingredientid 
FROM   (SELECT ingredientid, 
               amount, 
               unit 
        FROM   recipestepingredient 
        WHERE  recipeid = 23) AS subtable (s_ingredientid, s_amount, s_unit) 
       JOIN ingredient 
         ON ingredient.ingredientid = subtable.s_ingredientid 
GROUP  BY ingredientname, 
          s_ingredientid, 
          s_unit 
ORDER  BY ingredientname, 
          1; 

Reducing the number of checked rows are

from 1800 * 400 => 720 000
to 1800 + 10 * 400 => 5 800

With or without of index, ordered/unique, one can also play with generationg (temporary) tables to store result.
Just too evaluate a future design of execution plans.

Relational Calculus is fun!