Comments (8)
Hey @darkdreamingdan, this is not a simple issue, unfortunately.
This is a couple of my hours of work, for sure and I can't invoice this project currently, that's why I left it open for the community.
But I just thought on an interesting way of implementing it and I'll give it a try on my free time.
My explanation in the previous comment is a bit confusing but I think there are two parts of this issue:
- treating queries as a variable and saving them -> this is what I'm going to work on first, it seems relatively simple, from what I'm thinking...
- Implementing recursive behavior -> that's a bit more complicated and I still haven't thought on a way to do it
I'll try to tackle it this week. Will keep you updated of any progress.
Another thing you could try is opening an issue in SOCI and asking them about the possibility of including this feature. If they support it then I'll add it here, as it's very easy to :)
from xeus-sql.
Hey @n87, thank you for opening the issue :)
That's true. I don't understand why it's not being picked up by SOCI though. We treat this case in the following lines:
else
{
if (this->sql)
{
/* Shows rich output for tables */
if (xv_bindings::case_insentive_equals("SELECT", tokenized_input[0]) ||
xv_bindings::case_insentive_equals("DESC", tokenized_input[0]) ||
xv_bindings::case_insentive_equals("DESCRIBE", tokenized_input[0]) ||
xv_bindings::case_insentive_equals("SHOW", tokenized_input[0]))
{
nl::json data = process_SQL_input(code, xv_sql_df);
publish_execution_result(execution_counter,
std::move(data),
nl::json::object());
}
/* Execute all SQL commands that don't output tables */
else
{
*this->sql << code;
}
}
https://github.com/jupyter-xeus/xeus-sql/blob/master/src/xeus_sql_interpreter.cpp#L295-L317
Here are some things I'm thinking could be the culprit for this issue:
- check upstream if SOCI deals with the
WITH
keyword, maybe it's not supported by them - does this command requires some special treatment? For everything that's not acting directly in the database we have to create special cases called magics, see for example how we call
LOAD
:
%LOAD sqlite3 db=chinook.db timeout=2 shared_cache=true
I believe this might be the case forWITH
and then we want to do something similar to what we're doing with LOAD :)
I'm not sure, these are just some hints. If you or anyone want to tackle this issue I'm here to help.
Unfortunately I can't dedicate so much time to this project right now. (Maybe in the future!)
But thanks again for opening this and please feel free to ask questions.
from xeus-sql.
@marimeireles it looks like your snippet is just enough to explain the issue. You check that first token is one of (SELECT, DESC, DESCRIBE, SHOW). But SELECT queries can also start with WITH
or with VALUES
: https://sqlite.org/lang_select.html
Note that WITH
can also precede DELETE, INSERT etc., so I'm not sure what the fix should be.
from xeus-sql.
Hum! This is where my lack of knowledge in SQL attacks again!
Sorry for this, I'm mostly a C++/Python dev and just know the very basics of SQL.
Yeah, seems a bit complicated. If I understand correctly this WITH
clause is like a "variable(? did I get that right?)" that you can store the values of the query? And then you have the RECURSIVE
modifier, even.
I can't tell if SOCI offers support for it, because they have no entries about WITH
in their docs. And looking further, they have no WITH
tests nor examples, maybe they don't support it. So first step as I said, is making sure they do. Maybe one could even open an issue upstream asking about it and how to use it.
Afterwards... I'd say the steps to fix this would be:
- create an example that uses WITH (you've done this already)
- check the method
process_SQL_input
and print on the console the output of this code :), using this and maybe the help of the SOCI people we can figure out how to treat this result. Do we have to store this var ourselves? What type does it have? If we have to create a type for it is probably very complicated to tackle, if there's a type, should be easy, we can just create anotherif
check and add it before the other words
from xeus-sql.
A more googlable term for WITH clause is Common Table Expression. It is like defining variables, but more closer equivalent is let
clause in functional languages, e.g. Haskell:
let x = 2 in x * x
Somewhat contrived SQL(ite) equivalent:
> with t as (select 2 as x) select x * x from t;
x * x
----------
4
from xeus-sql.
Just came across this issue. Wanted to use Jupyter to teach Common Table Expressions, but seems it doesnt work :(.
Is it just a matter of whitelisting the missing WITH
and VALUES
as the first token in the parser? That feels like it could be a straightforward fix? Even if there are edge cases where the result does not need to output, it's still better than current behaviour which does not output at all.
Please could we just add this as a quick fix? 🙏
from xeus-sql.
Seeing same issue, so I came here to see if this was previously reported. And you're already working it! Thank you very, very much for these wonderful tools!
:tracking:
from xeus-sql.
Sorry to bring up this topic again. I'm having pretty much the same use case as @darkdreamingdan. I'd love to use xeus-sql to teach students how to use SQL. It's pretty much perfect for my use-case, except for this issue. We'd like to teach them the benefit of structuring queries using WITH
for better readability.
I'm not having much knowledge about C++, but it seems to me, these lines here determine which kind of queries are displaying a result in the notebook, based on the first keyword. Syntactically the WITH
statement has to come first in a SELECT
query. Adding the WITH
keyword there, could potentially fix it. But that's just a guess.
xeus-sql/src/xeus_sql_interpreter.cpp
Lines 312 to 315 in 315057d
Let me know what you think!
from xeus-sql.
Related Issues (20)
- Change date formatting to a more standard one HOT 14
- Allow running multiple statements in single cell HOT 1
- Add tests to xeus-sql
- Support for bash, other magic? HOT 4
- Add syntax completion to SQL HOT 3
- "money" column type in Postgresql generating error HOT 2
- Possible to execute the 'slash' commands on postgres? \d \dt \list HOT 3
- paging for large result sets? HOT 9
- Kernel crashes with `terminate called after throwing an instance of 'char const*'` HOT 6
- Display number of results at top of output, rather than bottom? HOT 2
- postgres first line comment shows no results HOT 1
- Handle postgres datatype xid? query returns Error: unknown data type with typelem: 28 HOT 3
- Conda installation failing for M1 Macbook HOT 4
- Doesn't build. json == 3.11.2 ? HOT 3
- Binder is not working
- make row summary more visible
- Please confirm whether xeus-sql supports ODBC
- Please confirm whether xeus-sql supports ODBC
- How do I set up a Jupyter kernelspec for Xeus-SQL?
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from xeus-sql.