Friday, February 29, 2008

Farewell to CMG

Thirteen years ago tomorrow I joined a consultancy company called CMG. When I went for the interview I'd never heard of it but the interview process impressed me. Fortunately things worked out well and I've been with the company ever since. In 2003 we merged with another consultancy, Logica, to become LogicaCMG. Until Wednesday, when the name was reverted back to Logica.

This makes sense in many ways. More people had heard of Logica than had heard of CMG, although the separate companies had been of equivalent size. And it was a bit of a mouthful - even I had taken to calling us plain Logica. But it is a sad moment. CMG was a special sort of company; people define themselves by the fact that they worked for CMG, even if they left the company years ago. And that's why several hundred ex-CMGers - because we are all ex-CMGers now - are descending on a pub in London to mark the passing of the name in the traditional CMG style. Cheers!

Wednesday, February 20, 2008

The Guru's burden

Those of us who answer questions in forums or add a comment to a blog will recognise the truth of this cartoon from the ever-reliable xkcd comic.

Wednesday, February 13, 2008

Data modelling and other dying arts

Martin Widlake sent me an e-mail after last months UKOUG Unix SIG:
"At my presentation at the UKOUG Unix SIG yesterday I suggested that formal design was almost dead, replaced with organic design and asked if anyone still used ERDs. No one did. Not one.
This kind of bothered me. Does it mean that just ERDs are dead? Or that a room full of DBAs is a room full of people who do not do systems design (I am just as shocked by that if it is true)? Or maybe formal design is a dead concept."
There's two points here. The first, the utter lack of DBAs who do data modelling tasks, doesn't surprise me in the slightest. This is the nature of the modern DBA's job. Production DBAs look after live systems: they don't design them. Increasingly people are becoming DBAs straight out of college. These guys have never worked as developers and probably never will. The older geezers, who followed the more traditional route of starting out as programmers and progressing into the DBA role, probably haven't worked on development projects in years.

Also the IT landscape has changed. Even ten years ago many organisations had one or at most a handful of databases. It was possible for a DBA to be responsible for a single database; knowing its purpose and its value to their organisation was part of the job description. These days it is not uncommon to find DBAs working in teams looking after dozens even hundreds of databases. Furthermore the production DBA may well work for a different company (i.e. an outsourcer), possibly in a different continent from the users. Their relationship with the databases they administer is mediated through SLAs and ITIL compliant procedures. So they have little incentive and even less time to appreciate the databases under their care. Indeed, given the prevalence of Sarbanes-Oxley and similar pressures, production DBAs will be increasingly encouraged to remain in ignorance. A production DBA is somebody who knows the metadata of everything and the business purpose of nothing.

Of course, there are DBAs who do work on development projects. They are often combine the role with that of being a developer, especially on smaller projects. They often get called database engineers rather than DBAs. And production DBAs tend to regard database engineers as being developers not "proper" DBAs. I have been a database engineer on sites where I wasn't allowed the SYSTEM or SYS passwords for my project's development database. I would bet that everybody who goes to the Unix SIG is a production DBA.

The second question is whether anybody uses entity relationship diagrams, or more broadly, whether anybody still does logical data modelling. I can't answer this one from personal experience. I've been on a data warehouse project for four years now: I only deal in existing schemas. Even when I have done design it has been for ETL infrastructure and similar, so I have leapt straight to physical tables. As I started out with SSADM I do feel a bit guilty about this. Although I must say I haven't exactly missed drawing Entity Life History diagrams.

Anecdotally, there does seem to be a general decline in the practice of data modelling. There were hardly any presentations on modelling at the last UKOUG conference or at Open World 2007. The Modelling and Design is one of the smaller UKOUG SIGs. The ODTUG Designer listserver has flurries of activity but since Oracle announced the death of Designer it has - understandably - experienced a major drop in traffic. There are occasional questions about data modelling in the OTN forums, but these are frequently from students rather than practitioners. It is depressing to consider that the most commonly referenced data model seems to be the fundamentally flawed Entity-Attribute-Value. My last piece of circumstantial evidence is that the Oracle blogosphere rarely features posts about data modelling. The only blog I know which regularly discusses data modelling is The Database Programmer and even Ken Downs only talks about tables.

Of course people are doing system design. There's lots of design about but I would guess that it all happens in UML. So the majority of logical data modelling these days produces class models rather than ERDs. The physical database design stage is much more likely to consist of ORM than mapping entities to tables. Now that's not the sort of party you invite a DBA to, because you just know they're going to glower in the corner, drinking heavily and muttering to themselves. So the mappings and the database design will be done by middle-tier developers. Our communal prejudices tell us this is unlikely to produce a correct and peformant database design, not least because projects which use such an approach tend to make a fetish of database agnosticism and platform independence. So in the long run we might see a resurgence in data modelling, as part of the tool set for rescuing poorly performing class models.

As a tangent, Dominic Delmolino observed in a recent blog that
"many of the people I’ve been interviewing seem to be taken aback by a few simple SQL questions, telling me that DBA’s (sic) don’t do SQL."
Again, why is this surprising? SQL knowledge is going the way of data modelling for production DBAs. There is a whole raft of GUI administration tools - Quest Spotlight, BMC Patrol, Embarcadero, OEM, etc - whose sole purpose is to allow DBAs to monitor and manage large numbers of databases without using the command line and without knowing SQL. Again this is inevitable given the landscape I described above. Old skool DBAs - the ones who started out managing a single database - will have accreted a personal library of SQL scripts, shell scripts and utilities which do all these things. But people starting out now will probably find themselves operating in shops with dozens of databases and no time to roll their own tools. If they are lucky there will be an old lag to pass on some skills and some scripts; more likely there will be a shrink-wrapped GUI tool. Besides, remember that Oracle Enterprise Manager was introduced in Oracle7: it is perfectly feasible for somebody to describe themselves as an experienced DBA who has never administered a database in any other way.

Thursday, February 07, 2008

Scoping with SQL Types

The scoping rules for function calls are quite clear. Given a package with a function which has the same name as a standalone function, another function in that package will call the packaged function not the standalone one:

SQL> create or replace function toto
2 return varchar2
3 as
4 begin
5 return 'TOOTING';
6 end toto;
7 /

Function created.

SQL> create or replace package a as
2 function toto return varchar2;
3 function tata return varchar2;
4 end a;
5 /

Package created.

SQL> create or replace package body a as
2 function toto return varchar2
3 as
4 begin
5 return 'KANSAS';
6 end toto;
7 function tata return varchar2
8 as
9 begin
10 return 'We''re not in '||toto||' anymore';
11 end tata;
12 end a;
13 /

Package body created.

SQL> select a.tata from dual
2 /
TATA
-----------------------------------
We're not in KANSAS anymore

SQL>

The rules apply the same way if we're working with an object rather than a package ....

SQL> drop package a
2 /

Package dropped.

SQL> create or replace type a as object (
2 attr1 varchar2(20)
3 , member function toto return varchar2
4 , member function tata return varchar2
5 ) NOT FINAL;
6 /

Type created.

SQL> create or replace type body a as
2 member function toto return varchar2
3 as
4 begin
5 return attr1;
6 end toto;
7 member function tata return varchar2
8 as
9 begin
10 return 'We''re not in '||toto||' anymore';
11 end tata;
12 end;
13 /

Type body created.

SQL> set serveroutput on
SQL> declare
2 my_a a := new a('KANSAS');
3 begin
4 dbms_output.put_line(my_a.tata);
5 end;
6 /
We're not in KANSAS anymore

PL/SQL procedure successfully completed.

SQL>

However, there is a gotcha: the scoping rules do not work that way when our type inherits from a super-type....
 
SQL> create or replace type b under a (
2 overriding member function tata return varchar2
3 );
4 /

Type created.

SQL> create or replace type body b as
2 overriding member function tata return varchar2
3 as
4 begin
5 return 'We''re not in '||toto||' anymore!!!';
6 end tata;
7 end;
8 /

Type body created.

SQL> declare
2 my_b b := new b('KANSAS');
3 begin
4 dbms_output.put_line(my_b.tata);
5 end;
6 /
We're not in TOOTING anymore!!!

PL/SQL procedure successfully completed.

SQL>

The solution is quite straightforward: use the SELF keyword to make the scope explicit.
 
SQL> create or replace type body b as
2 overriding member function tata return varchar2
3 as
4 begin
5 return 'We''re not in '||SELF.toto||' anymore!!!';
6 end tata;
7 end;
8 /

Type body created.

SQL>
SQL> declare
2 my_b b := new b('KANSAS');
3 begin
4 dbms_output.put_line(my_b.tata);
5 end;
6 /
We're not in KANSAS anymore!!!

PL/SQL procedure successfully completed.

SQL>

I admit I am not clear about the rules for using SELF. Sometimes it is compulsory, sometimes it is optional. So it's just easier to always include it whenever we reference anything inside a type body.

NB: I ran these tests on 9.2.0.6, if that makes any difference.

Wednesday, February 06, 2008

GROUP BY

In a comment on my previous piece Justin K asks "isn't it nice to have choices?". Well it depends. If you don't like carrots then a menu which offers you a choice of carrots or french beans is nice. But choice is one of those weasel words beloved of politicians1. Politicians promise parents the right to choose a school for their children; but what we actually want is just one local school with decent academic standards and no metal detectors at the school gates.

So the multiplicity of Oracle Web2.0 sites is not an automatic good. It will lead to the duplication of effort and a dilution of impact. We went through the same cycle of grief a few years ago with forum sites. On the one hand nobody wants to repeatedly write the same things in several different places. On the other hand people want to participate. And yet if we pour all our efforts into one site and it's not the one everybody else has chosen our endeavours will be wasted. Absence of choice makes life so much easier.

Still, I don't want to be negative about this. So I've set up a group for OTN Forum Regulars on OracleCommunity.net. Perhaps I'll see some of you there.



1. Not that I'm accusing Justin of being a politician or a weasel.

Monday, February 04, 2008

Who needs SQL injection?

I - probably like many of you - thought the prevention of SQL injection (the passing of additional SQL statements through the parameters of dynamic SQL calls) was the low hanging fruit of web app security. Not at all. This latest post from The Daily WTF really takes database (in)security to another level.

Friday, February 01, 2008

Networking fatigue

Recently here's been a whole bunch of Web2.0 initiatives in the Oracle space.

Plus all the other sites like LinkedIn. And now Eddie Awad has create an Oracle Community site, a kind of Facebook it's okay to like.

I have signed up for Oracle Community but I'm really not sure whether I'll use it much. We've gone from almost nothing apart from OFF TOPIC threads in the forums to a plethora of sites in a few months. A man doesn't have to be Howard Rogers to feel that this is getting out of hand. There's a tremendous amount of overlap between all these sites and the purpose of each site isn't completely clear. Oracle Wiki seems to consist mainly of people redacting the documentation. Oracle Mix struck me as both over-engineered (lots of different things it could do) and under-engineered (impossible to find anything on the site); perhaps its purpose has become clearer now but I must admit the site navigation was so flawed I stopped visiting.

I will be interested to see whether Oracle Community keeps going after the initial spike of registrations and page customisation. There is an obvious need for some better form of communication between Oracle enthusiasts than adding comments to blog posts or forum threads but which doesn't require giving out personal contact details to all and sundry. Perhaps Oracle Community is the one.

Postscript


Just after I signed up I read this article on The Register about Facebook fatigue. It's worth a read.