Slashdot Mirror

Apparently, your guy was actually pretty smart! He avoided the misfeature by instinct. "Practicing it wrong", my ass.

There's absolutely no reason to change SQL, because if you build a new query language that is based on the same mathematically sound principles of relational algebra then it will er... look just like SQL.

Actually, no it wouldn't. First of all, there's an assumption you're making here which is unwarranted: why do we need a query _language_ at all? A query API would remove a rather large class of serious bug: data quoting errors, typically leading to either SQL injection vulnerabilities, data corruption (ever see a site with all the 's turned to \'?) or both. It would also be more intuitive to new developers, better integrated with tools (autocomplete in language IDEs, etc), and more efficient (removing the need for text parsing).

Secondly, SQL is actually based on some rather horrible abuses of relational calculus that make most theoreticians in the field cringe. Including Christopher Date and Hugh Darwen, two of the original designers of SQL. You should check out their manifesto for a relational database system not based on SQL. Among other things, they think SQL screwed up handling nulls, and that 'group by' and 'having' are a violation of relational principles that lead to inefficient queries.

The advantage of most SQL/RDBMS is that they do adhere to the ACID principles

Which is also their disadvantage. ACID is brilliant, and I agree that it should be the default starting position. But it has terrible performance implications, and when we're dealing with large quantities of data where it doesn't matter if (say) a small random selection of recent changes are lost in event of a system failure, or if different clients see an inconsistent view of what changes have happened, you can do an awful lot better performance-wise. Which is where CouchDB and their ilk come in. You just need to be sure, before starting down that route, that you really do have one of the minority of problems for which sacrificing that degree of reliability makes sense.

Which is why I'm laughing at this article. There shouldn't be any case where you migrate from couchdb to mysql. You should only ever be using couchdb in the first place if you're sure SQL can't handle what you're trying to do.

(That and the fact that putting non-relational data in an SQL field is a newbie error that I made about 15 years ago and have already learned the lessons of...)

Allow me to add a reference to The third manifesto (Hugh Darwen and C.J. Date). Interesting stuff.

I guess compared to Java, SQL is still far less portable (in syntax and implementation), so I can see why it causes pain if you are used to writing in portable languages. (If you're willing to ignore all the 'avoid X as doesn't work on Tomcat' type stuff).

Definitely about time for a better relational language to emerge - SQL was, after all, just the first go, rather than definitive. I presume you've seen Date and Darwen's Third Manifesto?

http://www.thethirdmanifesto.com/

The Third Manifesto came out years ago.

It is not true that no one ever did anything with Date's and Darwen's ideas on a relational language. Check, for instance, the category I edit at the Open Directory, or even Darwen's own The Third Manifesto website with its list of projects.

Probably the poster is confused about the nature of Tutorial D. As it names indicate, it is but an example of a possible 'D', and one targeted at instruction at that. This accounts for its COBOLishness. It is possible to implement a non-Tutorial D that is completely faithful to the Relational Model and the Third Manifesto, yet has a distinct flavour.

For example, Alphora Dataphor implements D4, which was a compliant D until having had to incorporate SQL NULLs quite recently, and it has a Pascal flavour to it; Opus and Duro are C-like; there was a guy wanting to implement a C#-like D-flat language; Alfredo Novoa is implementing Tutorial D itself in MS.Net; and so on.

By the way, it is interesting that until now the more ambitious projects, that seem to be Alphora's and Mr Novoa's, are in MS .Net. Time for the free software community to rise to the challenge!

Read "The Askew Wall" and other documents by Hugh Darwen at www.thethirdmanifesto.com for some really good brief descriptions of some of the many logical inconsistencies with SQL.

Really, 95% of the problems that Chris Date and Hugh Darwen have with SQL revolve around an incomplete relational treatment of data. For example, by definition, a relation cannot have duplicate tuples (rows), but a table in SQL *can*. Also, columns in a table should not have a left-to-right ordering, which they do have in SQL (they should be thought of only as attributes, not positionally). Also, SQL fails to completely implement closure, where the result of *any* relational expression becomes a relation which can be in itself modified by any other relational expression. Also, SQL fails to provide a truly extensible approach to user-defined datatypes or domains.

Etc... etc... there are many other small kvetches and some big ones with SQL. Read the Darwen docs, and you will see some interesting things.

Essentially, the problem comes down to an ad-hoc experimental language (SQL), which was only intended as a temporary solution, which was then taken over and re-designed by a committee (ANSI).

One thing that everyone should understand: even though Pascal, Date, etc... argue that SQL is a bad implementation of the relational model, they *still* agree that it provides value, and that it is miles better than its hierarchical database predecessors. Since it is their job(s) to provide a reasoned critique of the field, it is only natural for them to rigorously compare SQL to the goals of the relational model. Many of their complaints fall in these categories (although there is much more):
1. Does too much -- too many ways of doing the same thing, and too many unecessary operations that could be better done another way.
2. Overly complex -- the SQL 1999 standard was something like 1200 pages.
3. Allows programmer to circumvent relational integrity. Things like "hidden identifiers", pointers, etc...
4. Too wrapped up in implementation -- users must spend a lot of time understanding the physical storage, rather than focusing on queries in abstraction.
5. Many small inconsistencies in SQL itself

But the problems with SQL are impossible to judge if you only know SQL. It's like the people who used to ask what was wrong with a perfectly good typewriter that made people want to use a word processor. To any who are curious, I suggest you do some reading. The absolute best simple introductions for these problems are in a two online documents by Hugh Darwen at www.thethirdmanifesto.com. Look for "The Askew Wall", and "The Importance of Column Names".

One thing that everyone should understand: even though Pascal, Date, etc... argue that SQL is a bad implementation of the relational model, the *still* agree that it provides value, and that it is miles better than its hierarchical database predecessors. Since it is their job(s) to provide a reasoned critique of the field, it is only natural for them to rigorously compare SQL to the goals of the relational model. Many of their complaints fall in these categories:
1. Does too much -- too many ways of doing the same thing, and too many unecessary operations that could be better done another way.
2. Overly complex -- the SQL 1999 standard was something like 1200 pages.
3. Allows programmer to circumvent relational integrity. Things like "hidden identifiers", pointers, etc...
4. Too wrapped up in implementation -- users must spend a lot of time understanding the physical storage, rather than focusing on queries in abstraction.
5. Many small inconsistencies in SQL itself

But the problems with SQL are impossible to judge if you only know SQL. It's like the people who used to ask what was wrong with a perfectly good typewriter that made people want to use a wordprocessor. I suggest you do some reading. The absolute best simple explanations for these problems are in a couple online documents by Hugh Darwen at www.thethirdmanifesto.com. Look for "The Askew Wall", and "The Importance of Column Names".

> I thought that SQL was the embodiment of the relational calculus?

The Third Manifesto, DBDebunk, DMoz.

> how is sql in any way inadequate

Not as powerful and simple as a relational language would be.

> Can you (or someone else more clueful than I) explain it again in a manner assuming less cluefulness on the part of the reader?

I really haven't the time. About data I'd point you to some reading materials: the books mentioned on The Third Manifesto, DBDebunk, some articles at DMoz and an implementation with documentation; about OSs, the GNU Hurd site seems to be unreachable now.

There is a huge misunderstanding: SQL is corrupted, not derived, from the relational model. Since SQL deviated from it, the relational model has been developed significantly by Hugh Darwen and Chris J Date among and above others.

So MS is not proposing a relational data storage instead of a file system, but a SQL data storage. Or perhaps not even SQL, as MS SQL Server is notoriously almost as little SQL-compliant as Oracle itself, the king of standards deviations, second only to MySQL.

BTW, if anyone tries to tell you SQL is really relational, or quasi-relational, don't belive the Marketspeak. You can't be quasi-Mathematical, and the relational model is really an implementation of set theory and predicate logic. Tables aren't relations, just like 2+2=5 is not Algebra.

Pragmatically, this prevents SQL from being as flexible, simple and performant as a relational system is. There are some examples, unfortunately mostly historical: Ingres QUEL was found to perform consistently better than SQL systems, and much better than pre-SQL systems that had SQL grafted upon; BS12 was much cleaner, as was QUEL, than SQL; nowadays Alphora Dataphor is extensible in a much cleaner way than current OO or SQL OO systems.

In its defense, it may be that Microsoft uses only the data storage engine of MS SQL Server in WinFS, just as it uses it now for Exchange. Getting rid of SQL looses many interesting, powerful capabilities, but also may unleash performance. It even could be perfected as the engine of a future, nice, relational system, if ever Microsoft wakens up to what Alphora and TransRelational are (trying to) do. But I very much doubt; most of the database field, both academic and corporate, is corrupted by SQL money; there simply isn't enough cultural critical mass for a big company as Microsoft to steer its course from SQL to relational.

Even so, this could be the single most important contribution of Microsoft to the Informatics field, on a par with making the x86 computer a commodity. Even a sub-relational system can be so much better than what is around; this, together with Moore's Law making inherent Wintel inefficiencies irrelevant 80% of the cases, could pose a very big threat to the competitive position of free software. Granted the GNU/Hurd, or perhaps even Eros, could implement something even better than WinFS, truly relational and with orthogonal persistence in the physical layer (Eros) and/or a functional language application layer (GNU/Hurd). But there is no talk of doing so yet, and it would take a long time to bring these systems to where GNU/Linux is now.

> you can't own up to a simple oversight

I did say I was sorry, and did explain why I didn't care to read into the page referred, and I did correct my negligence. What else do you want, chest thumping?

> you feel it's your place to make judgement calls on the value of words

And why not? Do you think Marketspeak isn't misleading, or that it is useful or beautiful?

> I'm not familiar with "relational prescriptions and proscription"

I would refer you first to Christopher J Date's and Hugh Darwen's The Third Manifesto there isn't much on the site, I point it to you more as a reference to the book. I keep some more links at DMoz, and if you have a tough skin you can try Chris J Date's and Fabian Pascal's Database Debunkings.

Sorry about not doing a write-up, but on one hand I am looking for a job now and on the other I don't think Slashdot is the place to do education (as opposed to discussion).

> What if you could easily generate the SQL from the XML, or vice versa?

Well, assume that the XML schema did convey all the possible information in SQL. SQL is the standard, why create yet another language? Now assume the XML schema can convey even more information than SQL. In this case, I would rather use something terser, clearer, more straightforward such as Tutorial D or D4 or any other declarative D.

> Torque can automatically generate the XML from an existing database, though it's not flawless yet. So you don't have to duplicate your effort, if you started with SQL.

That would be kinda OK, if I can continue keeping my SQL and just regenerate the changes incrementally and transparently.

But I fool myself: I don't actually like OO, I would rather program functionally. The real problem isn't to have something to interface SQL and OO, but something to replace SQL that would be fully relational and thus satisfy OO requirements.

> What's the problem with SQL

Fuller answer at The Third Manifesto, Database Debunkings and elsewhere.

But to cut a long story short, SQL does not support relational prescriptions as data independence (SQL views are not consistently updateable), nor respect relational proscriptions (undifferentiated NULLs), nor abide by the fundamental relational principles (pointers violate the Information Principle, OO extensions mess the simple domain, attribute, tuple, relation that is central to the relational model).

> on Mysql, precisely?

Its language is not proper SQL at all: it does not support transactions properly, nor has the necessary data types, and has been adding features kinda haphazardly without neither admitting to past mistakes nor presenting a clear roadmap to either SQL or the relational model. It should be called PseudoSQL, or SubSQL, or SimplerThanSQL.

> Why not simply set up a website with Tutorial D introductions, examples, etc?

Basically because of their personal background. They have been used to big organisations, like IBM, its direct competitors and users, and standards bodies that produce books and really expensive documents.

Moreover, Chris J Date is not rich, and his living comes basically from his books. None of them are systems programmers.

Now Hugh Darwen has created The Third Manifesto, the site

The book itself had precisely the same objective, just that it was meant to a different world. They are still trying to grok free software and the Net as related to it.

> Anything specific that bothers you?

First, you inspire yourself in IBM BS12, which was a good realisation of the relational model, but an early one and only an implementation anyway. The model have developed a lot in the last 20 years, and his should base his work on the fully developed version of the general idea, not on a specific realisation of it. BTW, I guess you have no other contact with BS12 than that page, so even what you can get from BS12 is very limited.

Second, xBase is really no good neither as a language nor as a database interface. Influence from it should be qualified as contamination, not information.

Third, you still use SQL terminology, and it seems that you never grasped the relational concepts from which SQL decayed in the first place.

About your work, Tablizer, it needs work... you still have to fully understand the relational model, and do away with SQL and navigational concepts. Much of what you say in your articles is simply a less clear form of relational algebra.

> Chris Date allegedly has designed such a language, but it is not available

The Third Manifesto.

> Don't tell me what I'm thinking.

Yet I can tell you are committing a serious logical problem in equating SQL to relational.

> SQL is a query language, not a database system.

A database system is defined by the language it supports. SQL systems are not, and cannot be, relational systems, because SQL violates fundamental principles of the relational model.

> the kinds of database engines that are sitting underneath SQL are what Microsoft claims they want to put into the file system.

Agreed.

> you and Alphora's marketing department think that.

Me, Christopher J Date and Hugh Darwen. And everyone I met that knows both the relational model and Dataphor. If you don't believe me, go to their web site.

If you don't know who they are, hint: you should before you say anything about relational databases.

> evidence that any more "pure" relational query language than SQL is better is thin at best.

Hm? Is set theory and predicate logic enough evidence for you?

> there are better choices than either SQL

Indeed: the relational model.

> or some unproven 1960's idea.

Unproven? Let me see. This idea single-handledly changed its field overnight, obsoleted all other practices, forced everyone to redefine not only their ideas but their concepts and the very terms and methods they used to think about databases. There were three successful implementations, and more are coming. It has produced the clearer writings in the field ever, indeed some of the best CS literature ever.

1.960's? OK, let's throw away Math, after all it's how many thousand years old?

PostgreSQL is not OORDBMS, it ORDBMS - and that is a benefit of it. There are plenty of document on Internet explaining it from different angles.

Here is what make PostgreSQL ORDBMS different than other RDBMS.

The Third Manifesto us the best book covering the subject.

My own experience shows, that OOP paradigm should be neither ignored or overused. Languages, like Python, help you to use OOP only when you really need it. Similar way, DBMS, like PostgreSQL, helps you to use objects in your databases only when you really need them, and according with SQL'92 standard.

OORDBMS, offenly stays for ODBMS databases, with some SQL interface extensions. Primary such systems are designed for persistence of serialized objects. Therefore they inherit all problems related to ODBMS, and first of all - lack of theoretical support (OOP is based on heuristics), lack of ad-hoc queries, lack of reflection mechanisms, very tight-coupling, lack of on-the-fly db schema changes, lack of consistent replication, and so on, and so on. In few words, it's easy to use OORDBMS as ODBMS, but you cannot use OORDBMS as RDBMS (without additional SQL-compatible transaction manager) - it's ODBMS by it's nature.

In ORDBMS PostgreSQL relations are first class objects with all theoretical support of relational algebra, while inheritance and ADT are just addons. You can use ORDBMS as RDBMS (that's the way most of use use in real life), but you cannot use it as ODBMS (without additional OR-mapping manager) - it's RDBMS by its nature.

> SQL - This example is a straw man. The problem with SQL is not the abstraction it provides, but the complexity of dealing with unknown table sizes when you are trying to write fast generic queries.

Correct conclusion, wrong reasons. The problem with SQL is not the complexity of getting good performance, but the low quality of its abstraction. A truly relational system might even be slow, but it would be uniformally, thus predictably and treatably, slow.

A relational system should never give different performance for different syntax if the result is the same. The optimizer should know the structure and sizes of all objects and always get the same access path if the result of the expression is the same. SQL fails because many SQL constructs are ill-defined or even wrong.

All performance tuning must be done by DBAs at the mapping between the logical and physical schemas, but SQL makes that impossible, specially after SQL:1999. See DBDebunk for more details.

> There's been a trend away from non-leaky abstractions.
[...]
> SQL offers an abstraction for which the underlying database layout is irrelevant except for performance issues.

If you are right, it means we will get a real relational DBMS soon. Wait, it already exists, but no one cares.

Moral: a trend exists when there is awareness not only the problem, but also of possible solutions. Everyone knows SQL is a problem, but most people think the solution is OO, so the real solution is overlooked.

> One problem with strong-typing based on fields that come from the database is that if you change the schema type, then it is out of sync with the code.

But this is good! If a data type changes, code around it must change. The same comparisons are not anymore valid, for instance.

The way too keep code stable is to begin with a good data model. Granted, SQL makes this impossible by not supporting domains as required by the relational model: what it calls domains are ridiculous. But if we had real domains, and domain Specialization by Constraint, then they would be stable and precise, and seldom code would need a data type change.

> Relational databases are not going away

I sure hope not, once they arrive indeed. Up to now, we have only Alphora Dataphor, and that is not yet a full-blown DBMS, not to mention being proprietary and MS W32-only. Remember and repeat to yourself: SQL is not relational!

Be sure to read The Third Manifesto and its clear, logic, simple proposal for rich, precise, reusable type hierarchies thru Specialisation by Constraint.