Explain Starcounter SQL usage in Db.SQL with examples

[mmnosek]

Type of issue:

Db.Sql documentation is not clear enough. It suggests, that it's possible to use whole Starcounter SQL, but does not explain how to use particular parts. For example:

• SELECT e, d FROM Employee e JOIN Department d ON e.Department = d works fine in Admin, but how to make it useful in Db.SQL? What it should return and how to access it?
• SELECT e.LastName, e.FirstName FROM Employee e FETCH 5 - how to use it in Db.SQL?

The question is what if we are returning more than one object in a row (ex. 1) or it's not an object at all (ex. 2). Users expect that all of our SQL works fine in DB.Sql, but it's not obvious how to use it.

Link to relevant page:

1. https://docs.starcounter.io/guides/database/querying-using-sql/
2. https://docs.starcounter.io/guides/SQL/joins/

@joozek78 @miyconst - any comments?
cc @Mackiovello

[miyconst]

... but it's not obvious how to use it.

Although I agree that it should be documented, I don't think we should use such approach anywhere, since it bring some significant performance overheads.

The first case has to be investigated, while the second case will return something like a name value collection stored in a special Starcounter class. This collection is not the database objects, but rather copy of it's values, which eliminates the whole purpose of Starcounter, which allows code host to access original values instead of copying it from database.

[mmnosek]

So I think our SQL documentation should be focused more on what is recommended in application code. Additional features need to be documented as well, but not at first place. Now when you go through the documentation it clearly suggests using JOIN and other not recommended features.
Who do you think would be helpful to elaborate more on that topic?

PS. I got the similar question about UNION implementation in our SQL. I think it doesn't make any sense according to the reasons you've written above.

@Mackiovello don't you think that we can discuss SQL section redesign on TechComm TF meeting? :)

[ErlendLandro]

You're completely right, the SQL documentation is not clear. There will soon be big changes to the SQL documentation because of the new query processor. In that process, I will also try to take into account things like this.

@Mackiovello don't you think that we can discuss SQL section redesign on TechComm TF meeting? :)

Much of the content that's going to be going into the SQL section of the docs will be discussed at the SQL task force. I'll report back from what we decide there and discuss it in the TechComm TF.

[mmnosek]

@miyconst if you'll have a chance to investigate case 1, please write the conclusion here :)

[mmnosek]

Related issue: Starcounter/Home#335