Slashdot Mirror

Backwards compatibility does not prevent Oracle from supporting FETCH FIRST. See Limiting result sets. Row value constructors wouldn't be a problem to handle either, especially since Oracle already has the functionality just with a silly syntax.

JD,

You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task. Several people have tried to do similar things, but the number of indexes you need to compare (features, reliability, performance, etc.) is too large. And some things are so different it's hard to compare them meaning fully. Imagine trying to do a head-to-head comparison of all OSes in every way.

Here's a few comparison links, but they just scratch the surface:
http://troels.arvin.dk/db/rdbms/
http://en.wikipedia.org/wiki/Comparison_of_SQL_database_management_systems

I've seen it used. I think it was Microsoft SQL-server that wanted dates to be in that format in some situation. I guess it may have been implemented by an American programmer who got the ISO format explained to him by a European programmer "just reverse the order of the fields" or something.

I'm fairly certain that MSSQL understands ISO dates correctly. In fact, this is an oft-repeated advice - when dealing with portable SQL, always write dates out in ISO; any other format may be accepted on some implementation, but not the other.

It also helps that ANSI SQL requires ISO-style dates, and at least that is one bit that all implementations agree upon.

Clearly this is a troll but regardless of all other features that MSSQL may have that MySQL doesn't there is one thing that is missing: LIMIT.

Perhaps it's because LIMIT isn't ANSI SQL? Before SQL-2008, you were supposed to use ROW_NUMBER() for that, which MSSQL does support. And no-one supports SQL-2008 yet.

Sounds like what you're saying is that Linq is just the language that goes along with an ORM mapper. Something like what HQL is capable of doing.

No, no, no!

LINQ is not an "ORM language" Is map an element of an "ORM language"? Is fold? Of course not!

LINQ can be used to build an ORM. It can also be used for many other things entirely unrelated to ORM or databases (in contrast, HQL is very specifically an ORM query language, not immediately usable outside that domain).

Even if we were...you've essentially *possibly* invalidated point #5. What about the other 4? Those don't actually have anything to do with ORM, but actually deal with using Linq as a language (vs using HQL as a language).

No, your points don't deal with "LINQ as a language" at all, because "LINQ as a language" is absolutely orthogonal to databases in general, kinds of relations (belongs-to / has-a) in particular, caching, and so on. What you were describing is LINQ to SQL - which, I agree, is rather crappy, especially when compared to proper ORM such as Hibernate (this is what your #1, #2, #3 and #5 are about). Entity Framework is supposed to alleviate that, though I still do not consider it as mature as Hibernate. Even so, it has its nice sides (I believe that they're right on track with the idea that plain objects with decorators/attributes are not the best way to initially model entities - you really need a dedicated ERD modelling language for that, the one where relations are also first-class, named, and explicit).

Some functions that work in BOTH the database and the VM don't work with Linq, or don't work reliably (examples I've found: shift operator, bitwise and when used with ulong).

This is not true. In plain LINQ to Objects, every single operator is supported, and any function call works. Without any limitations whatsoever. Because it actually, you know, just calls those functions?

Once again, what you describe here is a LINQ to SQL limitation, not applicable to LINQ in general.

If you actually have to make a new language variant for every single database

You don't make a "new language variant". You have to make a "provider", which is entirely the same as NHibernate driver or (on a lower level) ADO.NET/JDBC driver. Microsoft has made a provider for their own database, now other vendors are delivering providers for their databases. Entity Framework infrastructure is designed to be extensible, so there's no difference between MSSQL provider supplied out-of-the-box, and third-party providers. LINQ-the-language is exactly the same in all those cases (just as it is exactly the same between LINQ to Objects, LINQ to SQL, and EF). C# compiler does not know nor care, and it's the one that defines LINQ syntax.

You can do almost everything you need to using standard, compatible-with-every-server SQL most of the time

Yeah? Try calling a stored procedure in a server-agnostic way. Or work with dates. Or concatenate two strings. Or get a substring of a string. Or limit the output of a query to the first N rows. Or create an autoincrementing field.

If you think you know the answers for all of the above, then have a look at this comparison of different SQL implementations, and check yourself.

And that's without even mentioning the subtle behavioral differences between locking (DB2, MSSQL) and MVCC (Oracle, Postgres, Interbase) database engin

This book might be good for THEORY, but for actually getting useful and applicable information, the review leaves me wondering who would be a worthwhile reader.

That's funny, because I was just thinking it's odd that this book has no theory in it at all. At least in the review I saw no mention of the definition of ACID, the compromises at different transaction isolation levels, Codd's 12 rules for relational databases, Codd's original notation for relational algebra and relational calculus (of which SQL is an approximation), or normal forms.

And it turns out that this theory is useful and applicable. If you haven't caught on yet, I'm disappointed by this omission. A lot of people write horrible systems because they do not understand transactions, how to normalize a database schema, or why constraints are so important.

SQL is implemented differently in all of the environments I have encountered it (yeah, I'm not a PRO, just a hacker, so don't hate on me.) Those environments are MS SQL, MySQL, FoxPro, and MS Access. I think I messed around with PostgreSQL. Maybe a few others.

Point is nothing is really transferable and even basic syntax varies widely as do optimizations and 'the best way to do x'

If you need specifics on RDBMS implementations, look at this comparison website. It's not that long, and it basically fills in the gaps left by this book.

You can usually write standard SQL statements and run them on PostgreSQL, MS SQL Server, Oracle, and DB2. You can certainly come up with Oracle statements that don't run on PostgreSQL - e.g., by using their alternate syntax for left joins that predates standardization - but presumably this book teaches you the standard stuff. That's all you need in most situations, and it's all they can give without you without having to update the book every six months.

Microsoft Jet SQL (of Access fame) has a few cosmetic differences in syntax. (IIRC, quoting is different.) If that's enough to seriously set you back, you'd be in trouble even if the book did duplicate all the examples for you.

MySQL is the only real oddball, and even they are starting to learn that this SQL thing is useful after all. If you want to work with older MySQL installations, get a book on MySQL, throw out any knowledge you have of how to do things properly, and give up on portability altogether. Peculiarities in its performance characteristics made projects like phpBB do bizarre things like mantain parallel table structures for each forum in a messageboard. That's totally against the relational model, and there are lots of consequences...

Try reading this to understand just how broken MySQL (and others) is.

Yes. They have been compared.
A quite legnthly comparison can be found here.
SQL92 compliant is a relative term.

There's a very interesting and details comparision of the SQL support of several databases here.

This page is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.

Pretty much all the other comparison pages merely have checklists claiming that something is or isn't supported. This guy actually shows the exact difference between the SQL and the non-standard ways the different vendors address their holes in the standard.

Closer compliance to SQL standards is the big reason for us. None of the vendors are perfect; but MYSQL replacing the standard concatenation operator ('||') with something wierd does not lend comfort.

This page is a great reference for where Postgresql, Oracle, DB2, and others fail to conform to the SQL standards.

Well, the best you can say for any DB vendor is that they meet many but not all of the standard SQL constructs. The link above is a great reference on how each of the major RDBMS's deviate from the standard.

A much better database comparison can be found here. In detail shows the differences between each major DB and the SQL Standard.

I googled RECURSION and SQL, and came back with very little - apparently its only Oracle that's implemented this

Wrong. DB2 is rather close to implementing SQL:1999's recursiveness. There is an article comparing Oracle and DB2's recursive features.
-And a patch exists for adding Oracle-style recursion to PostgreSQL.

Whether WITH RECURSIVE or CONNECT BY work effectively is another question. I haven't seen any experimental articles considering this.

Without using recursive SQL, different encoding schemes exist for the purpose:

• most well-known: adjacency list
• nested set
• nested intervals
• materialized path

Joe Celko has recently published a book dedicated to the subject.

See my RDBMS links page for more on this.

SQL:1999 has been defined for years and some of the DBMSs have actually implemented some of its features. Yes, SQL:1999 is big and perhaps bloated in comparison with SQL-92, but it's not that bad if you concentrate on the core parts. Some of the news in SQL:1999 are actually clarifications on stuff in SQL-92.

SQL:2003 will probably be agreed on this year.

- So I find it strange to use the more than a decade old SQL-92 as the platform for a book published in the year 2002.

Another thing: I'd say it's "ISO SQL" nowadays (or ISO/IEC SQL), not "ANSI SQL".

Apart from that, I've put the book on my Safari bookshelf and look forward to reading (at least parts of) it. Unfortunately, it seems that Safari's index of the book is currently lost.

I like the approach where you start by thinking about the standard way to do it, and then try to squeeze your design into a real-World product. It's too bad that the official standard isn't online; fortunately, it's possible to get something close to being official. When trying to set up an initial, standards compliant schema, it's nice to have an evaluation-installation of Mimer SQL to play with. I wish it were open source or at least had an official price tag.

I expect that the book could be relevant in connection with a page about SQL differences that I've started writing, after having had to port some SQL from PostgreSQL to MSSQL.

SQL:1999 has been defined for years and some of the DBMSs have actually implemented some of its features. Yes, SQL:1999 is big and perhaps bloated in comparison with SQL-92, but it's not that bad if you concentrate on the core parts. Some of the news in SQL:1999 are actually clarifications on stuff in SQL-92.

SQL:2003 will probably be agreed on this year.

- So I find it strange to use the more than a decade old SQL-92 as the platform for a book published in the year 2002.

Another thing: I'd say it's "ISO SQL" nowadays (or ISO/IEC SQL), not "ANSI SQL".

Apart from that, I've put the book on my Safari bookshelf and look forward to reading (at least parts of) it. Unfortunately, it seems that Safari's index of the book is currently lost.

I like the approach where you start by thinking about the standard way to do it, and then try to squeeze your design into a real-World product. It's too bad that the official standard isn't online; fortunately, it's possible to get something close to being official. When trying to set up an initial, standards compliant schema, it's nice to have an evaluation-installation of Mimer SQL to play with. I wish it were open source or at least had an official price tag.

I expect that the book could be relevant in connection with a page about SQL differences that I've started writing, after having had to port some SQL from PostgreSQL to MSSQL.