Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PRQL 0.2 – a better SQL

github.com

Post reply on HN
Print view View original

121–130 of 166 posts

Re: Show HN: PRQL 0.2 – a better SQL

#121

Post by BeefWellington »

vore wrote: post #71
BeefWellington wrote: post #48

Earlier quoted context omitted.

What is annoying about implementing something like this in a relational database?

It's not straightforward to do polymorphic joins: one common pattern is to have child tables for each case of the union, but there's no integrity constraint such that each parent must only have one child, e.g. CREATE TABLE schools (id SERIAL PRIMARY KEY); CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id)); CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id)); How can you ensure tha…

As go_prodev indicated, the former just isn't how you reason about data modeled in a relational form.

The latter makes little sense. What constraints are you placing on a college that do not also apply to a highschool, given they're both schools?

> The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending on your database, it can also be painful to renumber the other items.

I'm unclear on what you mean by this. If you want a list of records ordered in a certain fashion, there's an entire "ORDER BY" clause for that express purpose. If you're trying to add "extra" data into the middle of some list that is not otherwise represented in the data in the database, that's essentially business logic and you should be using some kind of custom view or procedure to do that or doing it inside your application code.

If it's just a question of how you add data into the middle of a resultset from actual data in a table based on some arbitrary ordering, you can do that too, people solved that problem ages ago by simply having an ORDER column or similar that's just an int with whatever likeliest precision you get, e.g.: default might be 1000 and then if need be you can insert 999 items between two others before needing to do a re-numbering on the column. These are dumb tricks but needing to "insert a record between two other records" is often also a dumb trick someone is trying to do in the database because they haven't designed things well elsewhere.

I'd venture the 99.9% case is querying real live data in the database and ordering it by factual things like record names, dates of update or creation, status, etc.

Re: Show HN: PRQL 0.2 – a better SQL

#122

Post by Izkata »

digisign wrote: post #108
oarabbus_ wrote: post #83

Earlier quoted context omitted.

> a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?

When I've needed them I've needed them for multiple statements, once is not enough. Currently have to use plpgsql for this, which is half awesome, half abomination. :-D A single simple language sounds easier to learn.

Put a comma between them, postgres has been able to do multiple CTEs in a single query for quite some time: https://stackoverflow.com/questions/35248217/multiple-cte-in...

Or did you mean like using the same CTE across multiple queries? Views / materialized views are good for that.

Re: Show HN: PRQL 0.2 – a better SQL

#123

Post by jitl »

pgt wrote: post #53

For those interested in database query languages, it is worth knowing about Datalog, the query language behind Datomic, XTDB and Datahike: http://www.learndatalogtoday.org/ E.g. a parameterised aggregate query that retrieves the name and average rating of a film starring cast members whose names match the input names: [:find ?name (avg ?rating) :in $ [?name ...] [[?title ?rating]] :where [?p :person/name ?name] [?m :…

This is the Datomic/Clojure dialect of Datalog. I had an easier time learning a stand-alone datalog variant. I think compiling Datalog to SQL is an interesting idea.

I wrote a toy Datalog -> SQLite compiler: https://percival.jake.tl/

Other Datalog -> SQL compilers I know of:

- Originally from Mozilla, now independent: https://github.com/qpdb/mentat

- From Google: https://logica.dev/

Re: Show HN: PRQL 0.2 – a better SQL

#124

Post by yellowapple »

oarabbus_ wrote: post #83
digisign wrote: post #63

Thanks, I've frequently wanted a query language that was designed after the 70s. The ideas are sound, but a modernized syntax with variables to reuse subqueries would be lovely. This looks like it. I noticed one issue though... please don't copy the prefix of f-strings! That only exists because Python boxed itself in and it was literally the only ascii syntax left that could be used for string interpolation. It's mil…

> a modernized syntax with variables to reuse subqueries would be lovely. CTEs provide this functionality already, don't they?

They're a bit awkward, and I've found that some DBs (cough cough MSSQL cough cough) do a horrific job of optimizing them.

Re: Show HN: PRQL 0.2 – a better SQL

#125

Post by sgk284 »

paol wrote: post #15

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.

The risk here is that if one table has two fks to another table, the syntax becomes ambiguous. And the number of fks two tables have to each other may change over time. This means that an append-only change to a table may break existing queries that have no knowledge of the new column.

SQL addresses this via the natural join keyword `using`, where you enumerate the common columns between the two tables being joined. It isn't too convenient for your example unless your pk naming pattern happens to be `_id` instead of just `id` (note: this naming pattern has all sorts of other adverse consequences though). But it does provide convenience in some cases without introducing backwards compatibility risks as the schema evolves.

Re: Show HN: PRQL 0.2 – a better SQL

#126

Post by dragonwriter »

paol wrote: post #15

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.

The problem is when one has multiple FKs between the same pair of tables.

Of course, if you allow naming the relation when you create a foreign key, then you could use the source table-qualified relationship name for joins rather than the target table name, which would be unambiguous (and more communicative of intent).

E.g., for a hypothetical table with two self-fks:

  FROM employees ee
  INNER JOIN ee.manager mgr
  INNER JOIN ee.team_lead lead

Re: Show HN: PRQL 0.2 – a better SQL

#127

Post by layer8 »

vore wrote: post #71
BeefWellington wrote: post #48

Earlier quoted context omitted.

What is annoying about implementing something like this in a relational database?

It's not straightforward to do polymorphic joins: one common pattern is to have child tables for each case of the union, but there's no integrity constraint such that each parent must only have one child, e.g. CREATE TABLE schools (id SERIAL PRIMARY KEY); CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id)); CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id)); How can you ensure tha…

> How can you ensure that a school is either a college or high_school but not both?

If the DBMS supports it, you can add check constraints that query the other tables. See for example here: https://stackoverflow.com/a/2588427

Re: Show HN: PRQL 0.2 – a better SQL

#128

Post by layer8 »

AtlasBarfed wrote: post #82
BeefWellington wrote: post #48

Earlier quoted context omitted.

What is annoying about implementing something like this in a relational database?

Auto-completion sucks in a lot of sql statements because the table provides all the hints that good autocompletion would need to provide good suggestions. That sounds like a nitpick, but man is it useful when you need it. Notice how the first thing in PRQL is the table declaration. The fact that UPDATE and INSERT have different syntaxes for basically specifying the same mutation operation is pretty dumb.

That’s not necessarily a showstopper. Oracle’s SQL Developer editor for example provides useful completion in the SELECT clause if the FROM clause is already present.

Re: Show HN: PRQL 0.2 – a better SQL

#130

Post by intelijstupide »

jhgb wrote: post #61
coremoff wrote: post #33

Earlier quoted context omitted.

hopefully you'll forgive my pedantry - "union all" is vertical concat - "union" without the "all" gives you the distinct list

One of the reasons why SQL is crap: there should be no distinction between the two in relational algebra. A set of {A, B, C, B, C} is the same as {A, B, C}.

No post body was provided.
Post reply on HN