Slashdot Mirror
An Alternative to SQL?
Golygydd Max writes "Dave Voorhis from the University of Derbyshire has developed a program incorporating Tutorial D, a language designed to overcome of the shortcomings of SQL, and developed some years ago by Hugh Darwen and Chris Date. Until now, no-one had done anything with it but Voorhis is hoping for wider adoption; although we think it would be like pushing water uphill though." Update: 10/13 12:43 GMT by T : An anonymous reader writes "It's being picky I know, but the university in question is in fact called The University Of Derby, not Derbyshire."
RTFA:
-"you ask a database for "all entries where field X is not equal to 47" it won't return any of those where field X is null because instead of saying "Null doesn't equal 47", the value "null" is deemed not to be comparable with any non-null field."
-"rather arbitrary habit of allowing nested queries in some places but not others, for instance"
Almost all the Object Oriented stuff people layer on predicates are, at best, an ad hoc, and poor, means of optimizing execution speed.
Let me explain.
One of the principles of polymorphism is that the same method has the same abstract meaning regardless of the kind of object. A predicate considered as a method subsumes such polymorphism by simply trying the various possible implementations of the method and committing to only those that succeed. If more than one succeeds then so be it -- that's the whole idea of relations as opposed to functions.
So, one reason you want all this OO stuff is the inheritance hierarchies keep you from going through all possible interpretations of a given method when the vast majority of them will fail for a given object.
Another, related, problem is that inheritance provides defaults without requiring a lot of thinking on the part of the computer. What I mean by "thinking" here is the sort of thing that is done by statistical imputation of missing data via algorithms like expectation maximization (EM) or multi-relational data mining via inductive logic programming.
So, the other reason you want all this OO stuff is so you can avoid mining a background database to provide reasonable defaults for various aspects of the data.
Some might be concerned that over-riding isn't absolute in such a system -- that you don't absolutely block, say, more generic methods when you have more specific ones present, and they're right. You don't block those methods -- you lower their priority by lowering the probability of those implementations via the statistical methods of imputation and/or induction. In a microthreading environment they most likely won't get any resources allocated to them before other higher priority implementations have succeeded. In a single threaded/depth-first environment they will be down the list of desired alternatives -- but they won't be discarded until something equivalent to a prolog cut operation kills them off.
However, and this is the important point, the work that has been expended toward OO facilities has vastly outstripped the effort that which has been put toward more parsimonious ways of optimizing predicate systems.
One of the better predicate calculus systems out there -- more promising due to its use of tabling to avoid infinite regress on head-recursive definitions and its optimization of queries using some fairly general theorems of predicate calculus -- is XSB. It has an interface to odbc and a direct interface to Oracle, but it would be better if it had something like a recoverable virtual memory substrate to support its roll-back semantics.
Seastead this.
Is right here.
From the introduction: "Rel is intended to serve multiple purposes:
-It is a tool for learning, teaching, and exploring relational database concepts in general;
-It is a tool for discovering the power and expressiveness of a true relational language;
-It is a tool for learning Tutorial D;
-It is a relational database server;
-It may serve as a prototype or "working blueprint" for future implementations of Tutorial D or any "D" language (more on this later);
-It may serve as a platform for experimenting with and/or examining database engine internals. "
Yuck, you would never Want to do that. That's what relationships and foreign keys are for. You wouldn't populate your id's across columns, you put them in a new table, one row each, with an foreign key linking them back to main table.
XQuery is a standard, not one of Microsoft's creations.
Well as for the first part, this is really due to poor design. A null is intended to be used as a special thing. In fact null != null. Its meant to behave like that. This is the reason default values exist. if you want to have a column with empty rows to be 0 and not null then you specify that when you are planning. and when you are counting objects you can have 0. 0 is a value. null isnt a value. There are reasons why nulls are this way. One of which is to look for rows that have no data. If you think dealing with nulls in this sense is hard, imagine trying to programatically deal with what is 'empty' or '0' in an arbitrary column? Is this 0 a numeric? its is a 0x00? is it 0x30?
Null was meant to take the place of all the hack jobs that were used in older databases to signify non-value, NaN and so on.
Part of using SQL in the manner that it is, is that you get what you ask for. You dont get what some programmer *thinks* you want. Because at some point you may want something else. I really dont want a computer to guess at what I really want. I want it to tell me what it knows.
this proposed language is like a superset of sql. It also sort mixes itself with PL/SQL-like constructs as well. I think it's rather novel and it could make your optimization tasks easier if you had access to something like this... less trying to think in terms of the language, more in terms of how you want to acutally manipulate the data.
This is _not_ a query builder. It's going back, looking at what relational algebra is, seeing what people do with SQL, and then making sure the language has all the idioms required to be "complete" and not arbitrary.
THIS THING CAN TURN ON A DIME, MACROSSZERO STYLE ALSO FUCK BETA, ~NYORON
select client.name, client.id, product.id, product.name, product.price from client_table client, product_table product where client.id = product.client_id and client.id = ? and product.discontinued = 0 order by product.price
Assuming you substitute something for ?, that will effectively join the two tables into one, and give you a list for all products from a certain client (given by ID) which have not been discontinued, and order these results by price.
The World Wide Web is dying. Soon, we shall have only the Internet.
SQL was a language designed to allow relatively unskilled operators to be quickly trained for data entry using the language directly, and thus it was designed to be English-like, flexible, and forgiving.
SQL is only vaguely reminiscent of the true mathematics behind the relational data model. It continues to be used and expanded, despite its restrictions and shortcomings, because it is far and away more popular than any other database language.
The fundamental assumption of the relational model is that all data is represented as mathematical relations, or rather, a subset of the Cartesian product of n sets. Unlike SQL, in the mathematical model reasoning about such data is done in two-valued predicate logic (that is, without a null value), meaning there are two possible evaluations for each proposition: either true or false. The data is operated upon by means of a relational calculus and algebra.
The relational data model permits the designer to create a consistent logical model of the information to be stored. This logical model can be refined through a process of database normalization. A database built on the pure relational model would be entirely normalized. The access plans and other implementation and operation details are handled by the DBMS engine, and should not be reflected in the logical model. This contrasts with common practice for SQL DBMSs in which performance tuning often requires changes to the logical model.
That's the idea, you know. SQL is a declarative language, not procedural. You tell the database engine what you want, not how to get it. The idea is that then the query analyzer figures out the most efficient how.
It's a strange world -- let's keep it that way
How is SQL "not good enough"?
Look, I don't mean to be terse, really, but a Slashdot post is not the appropriate place to give a freshman tutorial on database theory, and the very fact that you ask your question means you need one.
In that light about all I can say is that SQL corrupts data, gives the logically wrong answers to querys and ties the logical model to the physical platform (think of Java and the JVM for an idea of why this might not be a good thing for a cross platform data manipulation system).
If this answer does not satisfy the solution is simple, read Chris Date's introductory book. He's already written it. I'm not going to duplicate it here. Until you do so you will probably remain ignorant of what a database even is, which, per above, makes it rather hard to explain what's wrong with a particular implimentation (just as it would be hard for me to explain a problem in orbital mechanics to you if you hadn't at least gone through an introductory physics text).
Of course, you can always go to Fabian Pascal's website ( which Date is a contributor to), but until you read Date's book you're likely to just think of Fabian as some sort of crank, out of ignorance. Until you take the trouble to learn what you need to know to evaluate his arguments you have no real basis for judging him in error.
Of course, that doesn't stop most people.
KFG
- Call me crazy, but if you have a 25-way join, don't you think you have bigger problems than your querying language? Maybe the person that is asking for the join needs to change their business processes.
Go work in a big ERP system. We use PeopleSoft and one of our tech has been debugging an AP (maybe GL?) query that's not working as expected and it's a 20 union beast. That's delivered too, not some home-rolled query. I'm sure there are worse queries out there lurking in the hearts of our system too.As for just changing our business process, well that's fine in an ideal world, but in practical reality it ain't gonna' happen without an act of Congress. You have to have someone who knows "best-practice" methodolgy come up with a new process, get sign-off across multiple departments and our auditing company so our banks and major vendors are okay with the changes, and then begin process training and roll-out. And that's for a small, private company; if you're public and have to add SOX compliance to the mix...well, good luck.
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).
I use PostgreSQL for all my DB stuff. Comparing it to MySQL is like comparing a mini-pickup to an 18-wheeler. PostgreSQL ANSI support is good, and it's very stable.
When I write custom apps (pretty much all I do) I write to the full capabilities of PostgreSQL. It's "man enough" to handle the biggest projects I'll likely ever see, and I'm not worried that the vendor will up and leave, since there isn't one.
In fact, PostgreSQL has had an interesting history in that the primary supporters of it have changed several times. PostgreSQL has weathered them all with grace, and remains today an excellent database package with a good, active community and quality developers.
So, I don't worry about JDBC, I don't write to cross platform, I could give a whit about Oracle or DB2 or whatever, but I don't. PostgreSQL is free, plenty good enough, and it won't go away.
Why bother trying to make my application portable?
PS: The article's example about null is stupid. Null = "I don't know". In that context, how could you include values of " 47" if you don't know what the value should be for a particular record?
I'm interested in this new DB language, if it actually offers a real benefit - but the article does a lousy job of getting me excited about it...
I have no problem with your religion until you decide it's reason to deprive others of the truth.
Talking SQL nowadays strikes me as being like talking raw assembler. You can do it, the tools are even rather nice nowadays, but any sane developer would use a higher level wrapper.
Like for instance, Hibernate. It does serialize/reconstitute, handles nested objects and data structures, and it's very nearly as easy as programming with regular heap-allocated objects. In any big app if you didn't use some such, you'd end up reimplementing it.
I see no reason wrappers like Hibernate have to backend onto SQL and only SQL. They could as easily emit and control this "Tutorial D" language.