Connection(s) established. Initiating execution.

Hi y'all.

So now the next phase is finished.
I now implemented and tested connections to all supported database platforms.
I only used multi-platform python-components.
These are:

• PostgreSQL(psycopg2, native)
• MySQL(MySQLdb, native)
  
• SQL Server(pyodbc using FreeTDS)
• DB2(pyodbc, needs libdb2.so)
• Oracle(cx_Oracle, native, needs oracle instant client)
  

Getting all the databases running on my network was a real chore, to be honest.
Especially DB2 and Oracle were quite cumbersome to connect to, and needed lots of proprietary stuff installed on the client. Also, they don't really support debian/ubuntu.
However, now that I know how everything works I will try to automate client installations, or at least document them well, so Unified BPM users won't have to go through all the hassle I had to.

Anyway, next now is to define a small XML schema for generalizing of database upgrade management.
My thought is that the same XML should be able to manage database upgrades for multiple platforms, through the functionality of the database abstraction layer.
I don't want some Unified BPM components to end up having half-a$$ed support for specific databases, like it is for the Drupal project, for example.
Also, this makes it possible to make really kewl database tests.

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!

More frustration and extremism.

Ok. In my last post I ranted about ranting about limitations in a specific database.
But sometimes, developers don't give a sh*t on how the users actually want to use the system they make.
Rather, they go to extreme lengths explaining why the user should do it another way instead.
Even though the users obviously don't want to do it that way.

For example, I am implementing column defaults in the DAL, and have finall gotten do make it work for MySQL.
What is the most usual default value? To me, and in all databases I have seen so far it is the current date and time.

I haven't thought about it much until now, since I had seen the TIMESTAMP datatype being used for that in MySQL.

I did not, however, consider that DEFAULT (Now()) wasn't even supported, since functions aren't allowed as default values!
So I will have to use another datatype, the afore mentioned timestamp, which I will transform into DATETIME DEFAULT (GETDATE()) when needed.
However, timestamp is completely inflexible, and have a different start date than the normal date time. So, it sucks and probably has to be handled separately later on.
But, screw everyting, I'll use it for now.

See http://bugs.mysql.com/bug.php?id=27645 for the reeaally long discussion.

Since many FOSS-systems, like mine, puts huge efforts in being database-agnostic, it is extremely annoying that the databases don't try meeting up the other way.
Like the stupid reasoning in the Oracle camp for not shortcutting auto incrementation for the condescending reason of teaching users how to do things properly.

The only database that seems to be fairly balanced, and doesn't create any significant problems for me, is PostgreSQL.

Now it seems that I am letting MSSQL off easy. But of course, MSSQL has the dubious advantage of almost not being designed at all, thus allowing for practically everything thrown on it at this the most basic level of database development.
It's when it gets advanced that MSSQL comes back and bites you like you've never been bitten with it's partial implementations. The insane PARTITION BY limitations almost killed me, for example.
That said, on this level it works well.

Cy'all.