Aug 17, 2010

Done with first phase of SQL generation

So there.

Now I have, in python, created a class which does only the things I need to at begin with the first database part of Unified BPM, creating and querying simple database structures.
The class can currently create these kinds of SQL statements(and then some):
  • SELECT - with where, expressions, conditions, CASE, function calls, joins, subselects.
  • CREATE TABLE - with defaults, primary keys, foreign keys and autoincrement
  • CREATE INDEX - Unique, Clustered, Non-Clustered
Supported databases for this functionality is:
  • Full
    • MySQL
    • PostgreSQL
    • DB2
    • SQL Server
  • Partial DDL
    • Oracle(due to identifier limitations and auto-increment hassle)
Now I will use this generator to make test for the DAL:s connection handling.
I now have working installations of all five databaseservers and will use them to set up integration tests for all flavours.

In practice, Partial DDL means that Unified BPM won't run on that platform but will be able to communicate with it. I simply won't commit to restricting myself to 30 bytes long identifiers. Somebody please think of the FK-names!

Aug 15, 2010

Adding DB2 support

Well, I though that while I'm at it, I might as well add DB2 support as well.
So now I will, it seems to have a pretty much straightforward SQL implementation.

By the way, It is kind of interesting to see what free(as in beer) offerings the different database vendors have, it is a bit revealing with regards to their attitude towards developers.

Aug 14, 2010

Dropping oracle support(at least DDL)

I have decided to drop Oracle support.
Last time I used Oracle was many years ago and back then I had none of the experience I do today.
The reason? The extremely silly 30-character length restriction on identifiers.

Oracle restricts the length of identifiers to 30 characters, which is extremely strange, this being 2010 and all.
Large systems with lots of complex and specific data needs long identifier names. I once belonged to the school of abbreviation, where everything got abbreviated down to a length of about 20 characters. I have since met THE REAL WORLD of huge databases and realised that the only reason to abbreviate identifiers is that is looks better when you list them.
However, mapping them to their real-world counterpart data(rw_ctrprt_data?!?) will, for the next developer, be a error-prone and boring chore.

Recently, a collegue of mine came to me with an identifier which unabbreviated would become something like 120 characters long. I had to tell him to go with it, since there was no way around it.

The typical reason for an identifier to become this long is that it describes an exception of some sort, like: "add taxes if the counterparty's custodian has the same currency than the traders custodian".
Usually, it is some real-word legal rule that has to be followed.
And believe me, we use all the tricks, like dynamic structures and so forth, to avoid having to name columns like this, so don't give me any crap of "with the right design", because reality is different from school.
But many times, one have to do it that way. Yeah, one could use column description, but they aren't as useful as one might think.

Anyway, I will now drop Oracle support. Too bad I've already spent hours on making the auto-increment support.
Luckily, it was really straightforward to uninstall the Oracle software, kudos to them. :-)

Upon closer thought I will probably not drop Oracle support totally, but rather I will only have none or partial DDL support. It would be silly to exclude a very popular database from the databases supported by UnifiedBPM.