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

Rethinking Database Programming

acadia.engineering

Post reply on HN
Print view View original

91–100 of 167 posts

Re: Rethinking Database Programming

#91

Post by closeparen »

ninkendo wrote: post #86
CopyOnWrite wrote: post #80

By now I stopped counting the attempts to replace SQL. There is a lot of valid critic for SQL and I would be very happy if some things would have been designed different. OTOH the architecture and mathematics behind relational databases are simple, composable and stood the test of time more than most other designs, methodologies or approaches to software development. Though SQL can be improved, even with my average S…

SQL has one flaw: The verb should come last. So, "FROM users WHERE id = 1 DELETE" or "FROM users WHERE email = 'foo@example.com' SELECT id". That'd cut back on some accidental "oops I dropped the whole table" because I submitted a delete query before writing the where clause. Other than that, it's perfect, no notes.

The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.

Re: Rethinking Database Programming

#92

Post by cmrdporcupine »

stvltvs wrote: post #90
ModernMech wrote: post #82

Earlier quoted context omitted.

SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.

Can you elaborate? It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.

What they're saying is: The relational data model and algebra are based on set semantics. Relations (equivalent of SQL's "tables") are sets of sets (tuples), not bags of "rows". There's no such thing or possibility as duplicate tuples ("rows" of "columns").

This has a number of elegant properties (and also improves the kinds of optimizations a query planner / execution stage can apply.)

A similar divergence is that the relational model has no concept of nulls. Presence/absence is expressed through "item not in set" in various ways, and by properly normalizing the data.

SQL also isn't properly expression oriented or composable at all. A relational algebraic language absolutely can be, and can lend itself to much more elegant data handling.

In many ways SQL is to "relational" like Java or C++ are to "object oriented" -- it got in very early to market, got mainstream success, and dominated the field, and in so doing it mangled people's perceptions of what a database is, and also made people either define "relational" as "SQL" (sigh), and even worse because they misunderstand what relational is while also hating SQL, they try to throw the baby out with the bathwater with their successors.

Re: Rethinking Database Programming

#93

Post by ModernMech »

stvltvs wrote: post #90
ModernMech wrote: post #82

Earlier quoted context omitted.

SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.

Can you elaborate? It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.

A result in SQL can contain duplicate items unless you tell it explicitly to deduplicate, so uses multiset/bag semantics. The relational model is built on set semantics, where every item is unique. Just because it can express those operations doesn't mean the idea is baked into the language semantics. e.g. the difference between Haskell and Python + first class functions; you can do functional programming in Python but it's not a functional language.

Re: Rethinking Database Programming

#94

Post by ModernMech »

cmrdporcupine wrote: post #92
stvltvs wrote: post #90

Earlier quoted context omitted.

Can you elaborate? It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.

What they're saying is: The relational data model and algebra are based on set semantics. Relations (equivalent of SQL's "tables") are sets of sets (tuples), not bags of "rows". There's no such thing or possibility as duplicate tuples ("rows" of "columns"). This has a number of elegant properties (and also improves the kinds of optimizations a query planner / execution stage can apply.) A similar divergence is that t…

> In many ways SQL is to "relational" like Java or C++ are to "object oriented"

Very cogent.

Re: Rethinking Database Programming

#96

Post by tome »

ModernMech wrote: post #82
CopyOnWrite wrote: post #80

By now I stopped counting the attempts to replace SQL. There is a lot of valid critic for SQL and I would be very happy if some things would have been designed different. OTOH the architecture and mathematics behind relational databases are simple, composable and stood the test of time more than most other designs, methodologies or approaches to software development. Though SQL can be improved, even with my average S…

SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.

Sets and bags are trivially interconvertible so it's really not a big deal: https://h2.jaguarpaw.co.uk/posts/set-bag-irrelevance/

Re: Rethinking Database Programming

#97

Post by victorbjorklund »

groundzeros2015 wrote: post #71
alpinisme wrote: post #51

Earlier quoted context omitted.

The point is end to end type safety. Whether that is worth the tradeoff of losing direct developer access to the db primitives is another question.

SQL is end to end type safe.

Only backend to database. This is talking about typesafe from database - backend - frontend.

Re: Rethinking Database Programming

#98

Post by senderista »

closeparen wrote: post #91
ninkendo wrote: post #86

Earlier quoted context omitted.

SQL has one flaw: The verb should come last. So, "FROM users WHERE id = 1 DELETE" or "FROM users WHERE email = 'foo@example.com' SELECT id". That'd cut back on some accidental "oops I dropped the whole table" because I submitted a delete query before writing the where clause. Other than that, it's perfect, no notes.

The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.

The problem with the query part is that query fragments aren't composable.

Re: Rethinking Database Programming

#99

Post by groundzeros2015 »

victorbjorklund wrote: post #97
groundzeros2015 wrote: post #71

Earlier quoted context omitted.

SQL is end to end type safe.

Only backend to database. This is talking about typesafe from database - backend - frontend.

Yes, and that’s an architectural choice you’re making.

Instead of using all the consistencies provided in the database process - including types, but also date/time, constraints, transactions, triggers etc. you are exiting the system and losing all guarantees.

This system also doesn’t solve that problem.

Re: Rethinking Database Programming

#100

Post by senderista »

cmrdporcupine wrote: post #92
stvltvs wrote: post #90

Earlier quoted context omitted.

Can you elaborate? It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.

What they're saying is: The relational data model and algebra are based on set semantics. Relations (equivalent of SQL's "tables") are sets of sets (tuples), not bags of "rows". There's no such thing or possibility as duplicate tuples ("rows" of "columns"). This has a number of elegant properties (and also improves the kinds of optimizations a query planner / execution stage can apply.) A similar divergence is that t…

Relations also have no concept of ordering. But bag semantics is both closer to efficient implementations and closer to user expectations than set semantics. Using set semantics everywhere also makes queries harder to optimize, because you have to selectively "de-deduplicate" for efficiency, instead of just sticking DISTINCT operators where they're needed.
Post reply on HN