4 ms·

> Eg SELECT !username from users could expand to all columns without username. This should be landing in PRQL soon. See ongoing discussion here: [an exclude cl

by snthpy 4y ago

> Eg SELECT !username from users could expand to all columns without username. This should be landing in PRQL soon. See ongoing discussion here: [an exclude clause, that would select all columns except whats specified #172](https://github.com/PRQL/prql/issues/172 https://github.com/PRQL/prql/issues/172) The syntax we seem to be converging to is like you suggested: select ![foo,bar] would select all columns except "foo" and "bar". The aim is to support this at least for SQL dialects that support it like DuckDB, BigQuery, ... For other dialects we might just error out initially. There is a goal of supporting working connected to a live database in which case the source table schema could be read and the appropriate SELECT clause generated. This would not be robust to schema changes such as future column additions though. At the moment the SQL is generated in an offline setting though so this is not possible.

phplovesong 4y ago
Thats nice to know! If you are a PRQL dev i have a suggestion for you/your team. Make PRQL language agnostic. In a real life scenario many people are writing SQL not in the app layer, but more ad-hoc for stats, reporting, analysis, examining the data etc. What i mean by this is: If i write an app that has embedded SQL i dont really care to much for the possible verbosity. This code will be used more than once, so im OK with taking the extra effort. Also all languages has an query-builder / ORM so the benefit of something like PRQL is possibly not big enough to merit it as an additional dependency. My suggestion: Make PRQL a cli tool that can be used by allowing users to connect to a database in a similar fashion as something like usql (https://github.com/xo/usql https://github.com/xo/usql), I would find the most power in a tool like PRQL is this setting: Ad hoc SQL queries. This would open the editor integration. With a tool like this i could open vim, and write PQRL in a window, and pipe it via something like Slime to a open PQRL session. This would be pure money!
- aerzen 4y ago
  So something like a language server, but connected to a database and able to execute queries? That's an interesting idea. While working on the compiler, I try to separate different components so things like this are possible. Currently, we are focusing on the language itself and also put some though into intermediate representation of the query (RQ - relational query). We are conservative about expanding the scope, because building database connectors and execution engines would take too much focus off the language and the compiler. But I'd lie if I said that I don't want to dig into it.
  - phplovesong 4y ago
    Well an LSP server would be a welcome addition. But before that i would put some effort into the actual "repl/session" -like tool. Pipe a PRQL query to a PRQL session, and then the PRQL translates that to SQL, and returns the underlying response from the database. If done right, this would open the possibility to build all sorts of cool editor plugins, that could directly interface with the PRQL session. IMHO this is where a tool like PRQL would shine, as it would make writing SQL more enjoyable and way faster, but at the same time being both language and sql-dialect agnostic.
    - nonesuchluck 4y ago
      Seems a natural fit for a notebook UI. If a PRQL cell doesn't start with "from," just continue adding filters to the pipeline above. Would let you progressively build pipelines by adding filters and derivations, while previewing the data each step along the way. Split a cell to debug a pipeline at any point.
- snthpy 4y ago
  Thanks for the suggestion. I don't think I knew about usql. I completely agree with you and have been working on a cli tool called `prql-query` or `pq` at the command line: https://github.com/prql/prql-query/ https://github.com/prql/prql-query/ Unfortunately I haven't had much time to spend on it of late but hope to push some updates soon.
Tostino 4y ago
The column list for a table is available in the information schema, just build the query with the expanded column list, minus the ones passed in to ignore on the DBs that don't have native support. Using the Information schema makes it DB agnostic for "free"
zestyping 4y ago
In BigQuery you can specify columns to include as well as exclude. For example: select s.*, t.* except (s.foo, s.bar, t.blah) from s, t where ... I've found this extremely handy for avoiding duplicate column names when joining tables. For example, trying to join two tables that both have an "id" column can be really tedious without this syntax. How would your syntax achieve that?

by snthpy 4y ago

phplovesong 4y ago

Thats nice to know! If you are a PRQL dev i have a suggestion for you/your team. Make PRQL language agnostic. In a real life scenario many people are writing SQL not in the app layer, but more ad-hoc for stats, reporting, analysis, examining the data etc. What i mean by this is: If i write an app that has embedded SQL i dont really care to much for the possible verbosity. This code will be used more than once, so im OK with taking the extra effort. Also all languages has an query-builder / ORM so the benefit of something like PRQL is possibly not big enough to merit it as an additional dependency. My suggestion: Make PRQL a cli tool that can be used by allowing users to connect to a database in a similar fashion as something like usql (https://github.com/xo/usql https://github.com/xo/usql), I would find the most power in a tool like PRQL is this setting: Ad hoc SQL queries. This would open the editor integration. With a tool like this i could open vim, and write PQRL in a window, and pipe it via something like Slime to a open PQRL session. This would be pure money!

aerzen 4y ago
So something like a language server, but connected to a database and able to execute queries? That's an interesting idea. While working on the compiler, I try to separate different components so things like this are possible. Currently, we are focusing on the language itself and also put some though into intermediate representation of the query (RQ - relational query). We are conservative about expanding the scope, because building database connectors and execution engines would take too much focus off the language and the compiler. But I'd lie if I said that I don't want to dig into it.
- phplovesong 4y ago
  Well an LSP server would be a welcome addition. But before that i would put some effort into the actual "repl/session" -like tool. Pipe a PRQL query to a PRQL session, and then the PRQL translates that to SQL, and returns the underlying response from the database. If done right, this would open the possibility to build all sorts of cool editor plugins, that could directly interface with the PRQL session. IMHO this is where a tool like PRQL would shine, as it would make writing SQL more enjoyable and way faster, but at the same time being both language and sql-dialect agnostic.
  - nonesuchluck 4y ago
    Seems a natural fit for a notebook UI. If a PRQL cell doesn't start with "from," just continue adding filters to the pipeline above. Would let you progressively build pipelines by adding filters and derivations, while previewing the data each step along the way. Split a cell to debug a pipeline at any point.
snthpy 4y ago
Thanks for the suggestion. I don't think I knew about usql. I completely agree with you and have been working on a cli tool called `prql-query` or `pq` at the command line: https://github.com/prql/prql-query/ https://github.com/prql/prql-query/ Unfortunately I haven't had much time to spend on it of late but hope to push some updates soon.

Tostino 4y ago

The column list for a table is available in the information schema, just build the query with the expanded column list, minus the ones passed in to ignore on the DBs that don't have native support. Using the Information schema makes it DB agnostic for "free"

zestyping 4y ago

In BigQuery you can specify columns to include as well as exclude. For example: select s.*, t.* except (s.foo, s.bar, t.blah) from s, t where ... I've found this extremely handy for avoiding duplicate column names when joining tables. For example, trying to join two tables that both have an "id" column can be really tedious without this syntax. How would your syntax achieve that?