sqlobject-oracle development blog

  1. Set up project weblog DONE
  2. Inform the SQLObject mailing list that I’m doing this DONE
  3. install Oracle:
    • see if the OS X server release of 10g works on normal OS X 10.3? ATTEMPTED UNSUCCESSFULLY
    • set up an OS X 10.2 partition with the developer release of 9i?
    • and/or put the linux release on my web server
    • … in which case I still need the OS X client anyway
  4. install cx_Oracle
  5. install SQLObject
  6. install some of the other databases for which SQLObject already has working connectors – MySQL and/or Postgres DONE – and get SQLObject working with it/them
  7. at which point we have a working environment and can actually get started …

the price of freedom

9th March 2005 permanent link

On the other hand, sometimes installing open source software does require hours of fiddling about with editors and C compilers, but that isn’t always the fault of the people who wrote it.

Now I have MySQL and Postgres working. I don’t have Oracle yet but will soon, one way or another. The next thing I need is python connectors for all these databases. MySQLDB didn’t work on the Mac the last time I tried it, but now I discover it does. That’s nice. Everybody’s favourite Postgresql connector seems to be pyscopg, so I download that; and everybody’s favourite Oracle connector seems to be cx_Oracle so I download that too.

Both psycopg and cx_oOracle, however, need a third party date/time formatting library, Marc André Lemburg’s mxDateTime (because, apparently, the built-in facilities in python weren’t good enough at the time when they were orginally written). I download mxDateTime, which appears to build and install successfully, but then crashes python when I try to import it:

Python 2.3 (#1, Sep 13 2003, 00:49:11) 
[GCC 3.3 20030304 (Apple Computer, Inc. build 1495)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import mx.DateTime
Fatal Python error: Interpreter not initialized (version mismatch?)

I post a cry for help on comp.lang.python, where I have always found people to be super friendly and knowledgeable, and get answers that suggest the problem is a bug in Apple’s standard installation of python 2.3. So now I must upgrade python, following these non-trivial looking instructions. Say bye bye to another afternoon.

Incidentally, Guido says that database drivers having this dependency on a third party library is an undesirable bit of legacy:

I would like the spec to change to require new versions of db API compatible modules to fully support the built-in datetime type, at least when used with Python 2.4 and beyond. The spec current recommends use of a 3rd party date/time type, which was a good idea back when there was no built-in alternative, but which should gradually be phased out now that there is.

Having to fiddle about with too many dependencies (especially when you happen to be trying to develop on a platform where one of your key dependencies doesn’t actually work, for whatever reason) is a major drag on productivity. Which takes us back to the key thing I don’t like about python: it’s no good having a super-productive language if all the time you save on typing code is wasted faffing about with the environment instead of getting more done.

The price of freedom is … eternal fiddling about with incompatible versions of odd little bits of software.

Hopefully this is just start-up cost and once I do finally get a working environment together everything will be lovely.

a tale of three databases

9th March 2005 permanent link

Installing open source software often requires hours of fiddling about with editors and C compilers, whereas big-name commercial software generally has slick packaging including reliable and easy-to-use installers. Right?

Well …

For my project I needed to install up to date versions of Oracle, plus one or two other databases for comparison and testing purposes.

I started with MySQL. MySQL provides a readymade mac installer package. I download and run it; it appears to have worked. But then when I go in to set up my development database it tells me ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'. I suspect that something is wrong with the setup of the mysql configuration database; but then after half an hour or so of googling and looking around, I realise that the problem is that I haven’t used MySQL for over a year and I’ve forgotten that you have to go in for the first time as its root user to set up other users and databases. From which point on everything is fine.

Time to set up MySQL: forty minutes, thirty of which are my own fault.

I’ve always been curious about Postgresql, partly because it has a reputation as the most industrial strength of the open source databases, and partly because I worked for years with Ingres, which was quite good in its day, and Postgresql was the next project by same guy, Professor Mike Stonebraker. Marc Liyanage provides a Mac installer for Postgres, but this time I decide there is no harm in a bit of fiddling about with C compilers, so I decide to try downloading it from source and building it myself following these intructions here. This also takes forty minutes of downloading stuff, setting options and watching messages from configure and make scroll by, at the end of which everything works first time.

Time to set up Postgresql, voluntarily building from source instead of installing a package: again forty minutes.

Nicely warmed up and on to Oracle, which after all is the real object of the exercise. Sergio Leunissen offers instructions for installing Oracle on the Mac that look promising, although the process itself is ugly. It involves setting up users and directories by hand first, then running a (typically ugly and amateurish-looking for a cross-platform java gui) java gui installer, which walks you through a long process in the course of which you have to go off back to the command line and run shell scripts a couple of times. All very messsy, strange and neither one thing nor the other. If you’re going to provide a gui installer, then why not provide one that does the whole job? On the other hand, why provide one at all when 99% of your users for this part of the proceedings are professional DBAs who probably prefer to run scripts anyway?

I may have made a couple of fatal mistakes here. The first was attempting to install Oracle in a directory which had a space in the path name, which it didn’t like, and then renaming the volume underneath the installer while it was still running in an attempt not to have to abort the installation and start again from scratch. The installer then ran through, reporting lots of warnings and build failures but still claiming success at the end of the day. I’m sceptical but still try to fire up SQLPlus to see what I've got. SQLPLus says it needs one of the libraries that reported a build failure, and dies. My next big mistake is trying to delete the installation from the command line and start again instead of running the nasty gui installer and selecting uninstall. When I realise that that’s what I should try to do instead I try it, but the nasty gui installer says it can’t find one of the directories I deleted (whose name it clearly has lurking in some hard-to-find config file somewhere) and refuses to go on.

Time to fail to install Oracle: several hours. Admittedly some of this may have been due to my own mistakes, but even if it had gone smoothly it would have been at least a couple of hours of fiddling about. My next move: attempt a clean installation on my Powerbook, and/or put it on my Linux web server where is might feel a bit more at home. I’m curious to find out how I’m supposed to go about running the nasty java installer gui over ssh though.

(Note to prospective employers/clients who may one day read this: I do know what I’m doing with Oracle once it is up and running. I’ve just never had to install it from scratch – up until now I’ve always worked with it in places where I had DBAs to do that for me. One might also object that MySQL only does 20% of what Oracle does, so of course it’s easier to install, and I wouldn’t dispute that: the obvious danger for Oracle is that that makes it 80% as useful in many situations)

wyoming, germany

28th February 2005 permanent link

The first thing I needed to do for my new project (apart from telling the world about it so as to make it harder to back down) was, obviously, install Oracle. And in order to download Oracle software, you need to be registered as a member of Oracle’s “Technet” service – which I already was, but I hadn’t used it for so long I had forgotten what email address and password I used for it, so the path of least resistance was just to set up a new account. Lots of market segmentation bumph to fill in, including a “company address” that, when told you’re in Germany, still presents you with a list of US States and insists that you choose one.

So now I’m registered at Oracle Technet as Alan Little of Wyoming, Germany. Oracle isn't even particularly unusual in having this level of staggeringly incompetent web design. But it does at least hint at why getting non-English data in and out of their database product might be far harder than it should be.

new project

28th February 2005 permanent link

I am working on a new project. Since I’m between consulting gigs as of this week, I thought it was time to start making a more substantial contribution to open source development than reporting the odd bug and moaning about the state of the python infrastructure.

I’ve been looking at several interesting open source projects in other languages, but I already know and like python, and it would take me a lot longer to get to the point where I could actually do anything useful in ruby, smalltalk or lisp. So python it is, for now.

I wanted something where I could do something useful and substantial in a reasonable amount of time, on a project that already had some momentum. I didn’t want to wander off into the wide blue yonder on my own and build something that would be either hopelessly trivial and obscure, or wildly overambitious and doomed never to get anywhere. What I chose was an Oracle connector for SQLObject.

SQLObject looks like an interesting project that is quite widely respected in the python community. It’s part of two python web frameworks, Webware and Subway. The lead developer, Ian Bicking, seems to be competent and respected and is definitely a nice guy who I’ve already exchanged email with a few times. SQLObject already has working connectors for numerous open source databases and a couple of commercial ones, but nothing for Oracle.

I know about relational databases – I’ve earned my living designing and building them for the last fifteen years, and in particular have spent large chunks of the last five years designing and coding heavy duty financial calculations in Oracle. So I know Oracle quite well from the inside, and I know python reasonably well – so how hard can it be? Hard enough to be interesting, apparently: Ian says:

I wouldn't think it'd be hard, but seeing that everyone starts it (or at least says they have started) but doesn't complete it, maybe there's something mysterious and hard about Oracle support.

I’ve also been emailing with a guy in Russia called Oleg Broytmann who has already been looking at it and says yes, he is finding it hard. He seems to be particularly struggling with special characters – he’s probably using Russian data and so hitting problems early on that an American developing with English-language data wouldn’t find until later. Finding problems earlier rather than later is good. I will of course try to coordinate with him rather than going off on my own.

It’s a great pity, in a sense, that we’re still spending time on this kind of basic infrastructural plumbing when we’re already half a decade into the twenty-first century. The interesting and challenging things about databases should be how do you go about gathering heaps of potentially interesting data to put in them, and how do you then go about extracting actually interesting information from all that potentially interesting data. Not how do you go about the tedious mechanics of shovelling the stuff in and out. But since the tedious mechanics bit doesn’t in fact seem to be quite finished yet, somebody should get on and help finish it. And why not me?

all text and images © 2003–2007