LINQ

I'm attending an MSDN talk today on several subjects. The one I came for is Silverlight. The first session of the day was on LINQ. It was pretty interesting.

LINQ is a real weird bundle of syntax that is being added to C#. The speaker, Anand Iyer, mentioned that it will be implemented in Mono. First it adds a SQL syntax for slicing in memory data. The classic example is around doing sub-selection and ordering of a List. This is nice, but looks funny. Reminds me of Oracle's attempt to get people to write SQL in the middle of JSP pages.

The second part of LINQ is as an ORM for databases and XML. This winds up looking like a bizarre mix of EJB3 and ActiveRecord. Annotations are used to embed mapping (table to class, property to column, etc.) Or you can use "implied" bindings (convention over configuration-ish.) Obviously the latter gives you a lot less code to write. I think you can also use a mix of these things.

It's funny to watch a language evolve. C# started off copying Java, but improving on it. Like Java, it has started to envy some aspects of dynamic languages. Microsoft has been able to move much faster with C# than Sun has with Java. Of course a big part of that is that it's a younger, much less widely used language. The stuff in LINQ really allows for syntax that looks eerily like Ruby or Python with constructs like someObj.find(name=>"Bob") At the same time, you get to keep a lot IntelliSense, which is huge for developer productivity.

If you've ever wondered what Java would look like if you could mix-in Groovy, then take a look at LINQ. It's a mess. It really looks crazy. It even made the speaker crazy. He kept trying to correct indentation and spacing to make it look legible. I guess that's what we all have to look forward to in Java. It also makes me appreciate ActionScript. It's come from the other direction, starting out completely dynamic and becoming strongly typed (which bought them a 10x+ performance boost.) Look at a complex ActionScript 3.0 class and a C# class that uses LINQ. From a pure "grammarian" perspective, you will really appreciate AS3.

Oracle XE

I wanted to install Oracle, something I often hate to do. I decided to install Oracle's slim personal database, Oracle XE. I only needed something for running a simple query (that required Oracle's SQL syntax, more on that later) and maybe a little development.

Oracle has a browser based client, which is actually pretty cool. Only problem is that it must install a web server for this and on my system it installed this on port 8080. I normally run Tomcat on port 8080. I tried to figure out how to change this inside Oracle. No luck. However, I found a great blog post that mentioned how to solve this.

Now on to what I wanted to do with Oracle. A co-worker had mentioned a SQL brain-teaser. Let's say you have a table, let's call it users, with exactly one column name. This column is not only not a primary key for the table, it is not unique. So you could have multiple instances of "John". Let's say there are three "John"s. You want to delete all of them but one, and you want to do this for any value of name that occurs more than once. The key is to do it with one SQL statement.

The obvious answer to me was to copy all the data into a temp table, but copy it uniquely. Then truncate the frist table, and copy the data back into it. That's three SQL statements though, and that's using an insert select statement as part of the create table statement.

Another way was to add a column to users and put in some sequence of numbers in that column. Then you can use that column as part of your where clause. I realized that Oracle does that auotmatically for you with its meta-column rowid. So that's why I wanted to install Oracle and try it out. Here's the SQL statement:

delete from users a where exists (select b.name from users b where a.name=b.name and b.rowid > a.rowid);

I haven't worked directly with Oracle in a long time (3.5 years) so I was rusty on the Oracle syntax at first. I would have liked to do a using clause like MySQL (and others) support instead of the subquery. Same net effect though.

technorati tags:sql, database, oracle