Wednesday, June 29, 2011

Model-first with Entity Framework and Oracle 11g (Part 1)

In the very latest beta release of the Oracle .Net drivers (11.2.0.2.30), Oracle now ‘supports’ Entity Framework (database-first or model-first) and Linq-to-entities. Oh, and 32bit only for now.

(If you’re tempted to try this, and get a 'ORA-00904: "SUBPROGRAM_ID": invalid identifier' error it’s probably because you – as a developer - are using 10g Express Edition, which isn’t yet supported. You can wait for the EF beta 2 (soon, allegedly), or alternatively you can download 11g Express Edition which is also in beta. And 32 bit only (on Windows, anyway). Oh, and make sure you install the ODAC after you install XE, or you’ll get the wrong Oracle home as your default on your path, and you’ll get errors in Visual Studio setting up Oracle database connections)

Prior to EF I’ve been quite happy doing database-first development in the SQL Server world, mostly because the visual tooling (database diagrams and the like) has been pretty good, and because of the type fidelity with mainstream languages (esp. .net) there’s relatively little loss of intent when you derive a model from the schema.

In the Oracle world of course this is very different. I’ve been massively underwhelmed with the quality of the tooling for schema design, and Oracle’s unconventional[1] stance on 4-byte integer and boolean data types (i.e. lack thereof) means that any model derived from the schema is going to need a fair bit of tweaking the metadata. That and fixing up all the SHOUTING_IDENTIFIERS.

So for Oracle, model-first looks like a really good idea. Unfortunately, in the current beta at least, Oracle’s support for this is pretty lame:

  • All integer types are written out as Number(38,0) (irritating)
  • Sequences are not generated for identity fields (really painful)
  • The PascalCasing of your entities is preserved in the database schema (totally unusable).

e.g.:

-- Creating table 'Customers'
CREATE TABLE "myschema"."Customers" (
"Id" number(38,0) NOT NULL,
"FirstName" nvarchar2(50) NOT NULL,
"AnInteger" number(38,0) NOT NULL,
"ALong" number(38,0) NOT NULL,
"IsSomethingTrue" number(38,0) NOT NULL
);


Why unusable? Because, as any Oracle developer will tell you with a bitter look, Oracle is case sensitive. Generally when you query it it all works as if your query was upper-cased for you: select * from mytable works against MYTABLE. But if you use Mixed Case and create a MyTable you’d have to write select * from “MyTable” (note the quotes) and get the capitalization right. Yeah, you can see how much fun you could have with this, and it’s the same with columns. So stick to the SHOUTING_IDENTIFIERS convention, or suffer the horrible consequences. It’s ok, I didn’t know either.



So what any sane SSDL / DDL generation template would do would do a conversion, right? Entities named MyTable would spit out a table called MY_TABLE, and the MyTableId attribute would be a MY_TABLE_ID column. But no.



Worse than that, because Oracle’s implemented the entire generation process in a workflow, not in the T4 template (which is a stub) you can’t fix this on your own.



That’s about where I gave up on Oracle. Bastards.



Next time: 3rd party drivers to the rescue!



 



[1] I’m trying to be polite. Prehistoric would be more apt. DB2, MySql, Postgres (and SQL Server of course) have 4 byte integers because that’s what applications / CPUs actually use. Oracle act like it’s still 1990 and the ‘database IS the application’

Tuesday, June 07, 2011

Australian Dev User Groups

I was looking for (and failed to find) a Perth Java User Group, and came across this list in builder.com.au. If accurate it seems to depict a bleak state for Java, loosing out 4:1 to .Net in user group count terms:

image

Ok, massive caveats apply. Number of user groups is a terrible metric, maybe the Java ones have much higher membership / do more with less / do everything online, or maybe they’re all so good they don’t need user groups unlike the poor fumbling .Net developers. But I don’t think so…

Thursday, June 02, 2011

“PhpStorm 2.1 doesn't work too nice on my Windows + Samba anymore”

Got a really bizarre email from JetBrain’s bug tracking system the other day: a ‘won’t be fixed’ for a bug I had absolutely no interest in.

Like many users, I’d enabled notifications for ‘issues reported by me’. Turns out that someone had registered a user with the name ‘me’…

…yup…

…and so managed to spam the entire registered user base when anything happened to any of their bugs :-/ So some fairly serious design failure there: user-entered data being mistaken as a control sequence - basically SQL injection (in spirit, at least).

Good job he didn’t register as Little Bobby Tables.

 

PS: Of course this is all a bit passé compared to hacking the electoral role
PPS: Why isn’t data tainting a compulsory part of all mainstream languages these days?

Popular Posts