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.
Sort of. Some SQL dialects allow for arrays as a datatype, though often without much in the way of good operators. Relational theory allows an attribute (column) to have any domain (type), including complex types like containers (sets, arrays, maps, etc.) ... in theory, yes, you should be able to do this. SQL, however, is another story.
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.
Dude, do you hear that whooshing sound? It's the the sound of the GPs joke going right over your little head.
If it becomes too complex because of too many tables, make some views which reduce the complexity for generic parts of queries.
bash$
XQuery is a standard, not one of Microsoft's creations.
Geesh, links to a press release and trade rag article, and not even the name of the project itself? Go read some real information about Rel, look at the examples, download the source, have fun.
The evaluation of an action as 'practical' . . . depends on what it is that one wishes to practice.
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.
I wouldn't call you crazy, but definitely naive. Welcome to the world of big, legacy systems. Comments like "change your business process" will get you nothing but a pink slip (or worse, a job in QA.) Go read any large company's financial statements sometime. There's a good chance that one of these monster join/unions was used at some point in generating the report that was used for the statement. Chances are also that it was auto-generated by some very expensive ERP software. Now go learn about these things and stop acting like know anything about "business processes."
XQuery is a standard
For the love of $DEITY, not everything remotely associated with the W3C is a standard. In fact, virtually nothing they do is a standard. Tim Berners-Lee was extremely careful when setting up the W3C to emphasise that they weren't a standards body.
CSS? Not a standard. XML? Not a standard. XHTML? Not a standard. "Web standards"? Not standards. HTML? HTML has actually been standardised by a real standards body, and so, if you are using ISO-HTML, which is a stricter version of HTML 4.01 Strict, then yes, you are using a standard. But if you are using any other form of HTML, then no, you aren't using a standard.
XQuery? That's just a public specification. An incomplete, draft specification at that.
Null is the absence of any data, but the author is criticising it because he thinks it should behave the way numeric zero does in a variable. If he wants his attribute to act like "0", then it should in fact be "0".
SQL by design distinguishes null from "0" - it's not a limitation or a flaw in the design, it's quite deliberate, but it's a distinction that people who are not familiar with SQL often get confused about.
Si tacuisses philosophus mansisses. If you had kept quiet, you would have remained a philosopher.
Unfortunately, the article only mentions one specific problem with SQL
Here is an informal list from the c2.com wiki:
# Nesting makes it harder to factor out duplication as opposed to (optional) named units
# Group By inconsistent and/or confusing
# Implementations are inconsistent between vendors
# Standard lacks tree-based operations. What tree-based operations would be compatible with a relational model? Transitive closure? The point is that everyone comes up with various ways to hack up trees in SQL (e.g discussed in "SQL for Smarties", Joe Celko), but the hacks tend to be awkward and slow, so some people think that some model of trees, any model of trees [or graphs, or multigraphs], should be native to SQL to avoid the need for hacks. See http://docs.alphora.com/D4LGExplode.html for an implementation of the EXPLODE operator proposed by Chris Date for hierarchical queries. {Move to RelationalAndTrees}
# Inconsistent syntax (see below)
# Large, complex syntax instead of using combinations of simpler syntax units. Think of comparing the COBOL parse tree (or generators) to C's or Lisp's parse tree. Moved discussion below.
# Nulls are a flawed concept. Especially for strings.
# Defaults to generating duplicates, which can cause confusion and erroneous results.
# Encourages long, hard-to-digest run-on "sentences"
# Not a DBA-extendable language (see BusinessSystemTwelve)
# Joins could be simplified if using "standard" links (WhereAndAnd)
# The formal specification is too damned huge
# Many SQL-based systems don't allow user-defined views (DatabaseFlaws?)
# Column aliases are sometimes not recognized in outer queries
# SQL's syntax is prone to security problems: SqlStringsAndSecurity
Table-ized A.I.
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
that they are trying to give you the best of both worlds. Expressions that can be expressed simply in SQL have short forms in the language too. Expressions that were hard to express in SQL but easy in R.A. are still pretty basic forms.
By not trying to be like SQL, and more like {T,PL}SQL, I think they get the freedom they need.
THIS THING CAN TURN ON A DIME, MACROSSZERO STYLE ALSO FUCK BETA, ~NYORON
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.
Strictly speaking Transact SQL is Sybase's variant. Microsoft adopted it when they forked SQL Server from them.
---- Den ene knappen er powerknapp, den andre er Bender voice knapp "Bite My Shiny Metal Ass"
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).
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!
Leandro Guimarães Faria Corcete DUTRA
DA, DBA, SysAdmin, Data Modeller
GNU Project, Debian GNU/Lin
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.