Welcome to Project:M36 Discussions!

[agentm]

Welcome!

This discussion board is an easy way to get answers to questions regarding:

• support for Project:M36 development
• Haskell programming with Project:M36
• contributing to Project:M36
• ideas for improvements
• software philosophy (start with "Out of the Tarpit")

If you are new here, you might wish to read through the 15 Minute Tutorial.

[YuMingLiao]

Hi, @agentm
I just want to talk about an idea here:
Can we have an RelationalExpr -> IO Relation thing? (a relational expression with relvars that refers to the outside world) In that way, we can talk about relations outside a database such as APIs, SQL databases ...
Because of single source of truth, it theoretically works.
If the outside world restrict some access of data, then it can just offer an updatable view.

In this way, we can use relational expression to talk about data all over the world. You don't have to write api calls every time you want to read some data from the outside world. You can talk about datas from different sources together in one relational expression. It's a good way to let truth stay one single source.

[YuMingLiao]

No, I haven't thought of that for a while...
Sounds a good plan!

[YuMingLiao]

Although it is unrelated, I may found a fun, low-hanging fruit of project-m36.

I was looking into LeetCode Database Problem recently and found while project-m36 can do most of the (free) problems (more easily than sql, I feel), Consecutive Numbers is not one of them, yet. It may need a "group" operation in a DataFrame that makes it a Relation again.

[agentm]

Indeed, that's a bit tricky since it doesn't make sense to talk about consecutive items in a set (rows in this case). However, this would be possible to solve in the relational algebra with someone more akin to window functions.

I think others would be interested in your results. Would you like to write a blog post about this? If you don't have your own blog, I would be willing to host it on the Project:M36 blog with credit to you, of course.

[agentm]

Hi @YuMingLiao! After some fun hacking, I managed to solve the consecutive numbers puzzle. It does require some relational function hacking since it basically requires a grouping on consecutive integers while Project:M36 only supports grouping by equality. In any case, here's my solution:

TutorialD (master/main): x:=relation{tuple{id 1, num 1}, tuple{id 2, num 1}, tuple{id 3, num 1}, tuple{id 4, num 2}, tuple{id 5, num 1}, tuple{id 6, num 2}, tuple{id 7, num 2}}
TutorialD (master/main): a:=((x group ({id} as y)):{z:=groupConsecutiveIntegers(@y)})
TutorialD (master/main): :showexpr ((a{num,z} ungroup z):{b:=count(@rel)}) where ^gte(@b,3)

┌──────────┬────────────┬──────────────────────────────┐
│b::Integer│num::Integer│rel::relation {value::Integer}│
├──────────┼────────────┼──────────────────────────────┤
│3         │1           │┌──────────────┐              │
│          │            ││value::Integer│              │
│          │            │├──────────────┤              │
│          │            ││1             │              │
│          │            ││2             │              │
│          │            ││3             │              │
│          │            │└──────────────┘              │
└──────────┴────────────┴──────────────────────────────┘

This solution relies on a new server-side function called groupConsecutiveIntegers which you can find here. Obviously, this is not particularly smooth, but it is possible to solve this problem using relational algebra concepts. To solve this problem in a general way, TutorialD needs to support a generalized form of folding to make nested relations on custom criteria.

[YuMingLiao]

That's really cool! I believe folding is definitely needed since there are several "consecutive" problems in LeetCode's database problem (178, 197, 601).

I started answering a problem, 175, and 181.
And here is some of my thought during the process: blog. Hope it helps!

[YuMingLiao]

For example, Project:M36 needs to ensure that all rows are unique for the relation to function properly (since all operators make that assumption). <

I naively think we could add a unique column to make a bag a set (all rows are unique).
And null can be translated into a Maybe a type.
Is there anything else to be taken care of?

This seems to imply that while ingesting a remote relational data source is acceptable, the result would need to be persisted in a relation variable before accessing it (for validation purposes). <

Sure, if an api call is needed in a relational expression, the interpreter should call it first, make it a relvar, then deal with it following the rest of the expression.
Did I understand it right?

[agentm]

For example, Project:M36 needs to ensure that all rows are unique for the relation to function properly (since all operators make that assumption). <

I naively think we could add a unique column to make a bag a set (all rows are unique).
And null can be translated into a Maybe a type.
Is there anything else to be taken care of?

Sure, but it might be surprising to the user that he executes a remote "SELECT email FROM user;" where email is not unique and then Project:M36 makes the result a set which differs from the SQL result. We could either assign a unique row counter attribute to the returned rows to ensure uniqueness or just collapse the results. I'm not sure what users would find less confusing. In general, the relational algebra and SQL are incompatible.

This seems to imply that while ingesting a remote relational data source is acceptable, the result would need to be persisted in a relation variable before accessing it (for validation purposes). <

Sure, if an api call is needed in a relational expression, the interpreter should call it first, make it a relvar, then deal with it following the rest of the expression.
Did I understand it right?

I think that that approach is most promising despite the fact that it differs from postgres fdw which allows fdws anywhere in SQL queries. This makes the fdws more like import processes rather than query elements.

[YuMingLiao]

Sure, but it might be surprising to the user that he executes a remote "SELECT email FROM user;" where email is not unique and then Project:M36 makes the result a set which differs from the SQL result. We could either assign a unique row counter attribute to the returned rows to ensure uniqueness or just collapse the results. I'm not sure what users would find less confusing. In general, the relational algebra and SQL are incompatible.

I haven't used sql often so I don't feel the use case. I just personally think assigning a unique row counter attribute would be better. After all, sql is a bag system. User can use project to collapse the results later or do whatever relational algebra can do to a sql bag. That's the fun part. :)

I think that that approach is most promising despite the fact that it differs from postgres fdw which allows fdws anywhere in SQL queries. This makes the fdws more like import processes rather than query elements.

I guess this is closer to what I thought in the first place: to express relations, local plus remote, fluently without being interrupted by API/IO code.

I may overthink on this topic. Just leaving an interesting topic here.