Success!

Hi everybody!

Now I have finally succeeded in making the following work:

• XML to object structure


• Object structure to XML (the tests goes both ways)



• The following structures are currently supported:
  
  
  
  • SELECT
    
    
    
    • Operators/Identifiers/Aliases
    • Function calls
    • TOP/LIMIT
    • CASE..WHEN
    • JOIN
    • WHERE
    • ORDER BY
    
    
    
    
  
  
  • INSERT INTO (from SELECT)
  
  
  • CREATE TABLE
    
    
    
    • Constraints(PK/FK)
    • Indexes
    • Default values
    • Auto increment. On Oracle using triggers, thanks for that pointless inconvenience, Oracle :-(
    
    
    
    
  
  
  • Custom
    
    
    
    • The "custom" structure simply take an SQL statement for each backend.
      
      It is intended for those cases when very database-specific features are needed.
    
    


• The db_upgrader library, that uses the DAL to upgrade databases, outside of performing the upgrades from an XML-definition, now tracks applications and versions. Cewl stuff.
  

Basically, those three statements are enough to build most things.
There is usually no long-term gain into making a database much more complex than so.
I'd say you solve 95 % of your database creation needs with that, and what it does not solve..well perhaps it should not be in the database layer.



These structures are all tested as integration tests that are run on both Windows XP and Debian(unstable) that, using the db_upgrader library creates an example database on the following platforms:

• Postgresql(on Debian stable)


• MySQL(on Debian stable)


• MSSQL server Express(on Windows XP)


• IBM DB2 Express-C (on Debian stable)


• Oracle 11g XE (on Fedora 16)

I can say one thing, it sure wasn't easy.

I basically had these requirements:

1. The Database Abstraction Layer(DAL) should be able to run on both Windows and Linux and seamlessly be able to do the same thing on PostgreSQL, MySQL, MSSQL, DB2 and Oracle.
   
2. The installation of the clients must be simple(server:port, database, user, password) , and therefore any installation procedures need to be scriptable. A user should not need to edit a million config files, set environment variables or download lots of files to make it work.
3. The connection to the database backends should not need lots of tweaking, it should "just work". I did not mind if some performance had to be sacrificed. According to my experience after 10 years of database development, it is usually not the driver that is the show stopping performance bottleneck, but indexing, lack of proper database engine internals and, of course bad database design.
   

I almost met all of them. The only one were I didn't make it all the way was point 2.
And actually, I think I can semi-automate or at least wildly simplify those processes if the worst client-side hassle offenders, IBM DB2 and Oracle 11g, is OK with it.

Configuring the back end servers was a real pain though, especially Oracle 11g.
Don't try install that on Debian/Ubuntu, folks. Go with Fedora for Oracle 11g and you'll live longer.
Luckily, I am done with that now. :-)

On to moving the whole thing stuff into public display on the Sourceforge GIT. Or perhaps somewhere else, Google code? Ideas?

Anyway, I will now set up some CI-testing solution around my servers and clients. That'd be great.

And then it is on to some more installation(.deb/.msi files) stuff and then the fun part. Adding features.

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!

Database Abstraction Layer - DAL

Hi!

The last month has been somewhat difficult with regards to time available.
But I've still managed to churn out an early version of what will be the core of the database abstraction layer, the classes that generates the SQL for each database.
The code is currently supporting MySQL, PostgreSQL, Oracle and SQL Server.
Since they will be sent as parameters into DAL, I call them parameters.
They do, however, have more functionality than being mere parameters.
There is a base class, Parameter_Base, that all parameters inherits from, that has four methods:

• AsMySQL()
• AsPostGreSQL()
• AsOracle()
• AsSQLServer()

Each of these methods returns a database specific SQL-representation of the parameters contents.
There are classes like Parameter_Field, Parameter_Expression, Parameter_CASE, Parameter_Function and Parameter_SELECT and so on.

They use each other to generate, for example, a SELECT-statement.
Consequently, Parameter_SELECT has (currently) two properties, fields(list of Parameter_Field) and sources (list of Parameter_Source), which in turn uses other classes.
So when one calls the Parameter_SELECT.AsMySQL function, it loops through the objects in its fields and sources list and calls their .AsMySQL functions. This way, a MySQL specific SQL-representation of the Parameter_SELECT-object is generated.

The parameters: parameter.py
Some tests: parameter_tests.py
Crude documentation: Doc_20100613.pdf

So far, most elements of typical SELECT statements are covered, like expressions and joins.
Stuff like ORDER BY, GROUP BY and HAVING aren't, but from now on, it should be much more easy since they merely reuse the concepts I have already defined.
I'd have to admit, maybe the structure looks pretty natural when you look at it, but it was really quite hard to generalise and simplify the structure while maintaining all the functionality of the language.

Well. I suppose that was it.

DB issues and their solutions

Issues

• What should the database look like?
• How do you handle upgrades of the database structure?
• How do you handle upgrades when both MySQL, PostgreSQL, SQL server and Oracle is supposed to be able to be the database backend? (I'd say DB2 as well, only I haven't got any experience with it, or test installations)
• How do you test upgrades of the database structure?
• How do you test upgrades of the database structure against different backends?

Upgrading

For Unified BPM, the solution to the upgrade problem is two-fold:

1. A generalizing layer. A Database Abstraction Layer(DAL).
   


2. A step-based upgrade solution that uses that layer to apply upgrades.

In my experience, and as long as you stay away from stored procedures, functions and stuff like that, and only have a simple database structure(which Unified BPM is supposed to), most updates only need standard SQL syntax, like CREATE TABLE, ALTER TABLE, INSERT and such. And a "custom" which takes whatever needs to be done, one SQL for each backend. Hopefully, "custom" should be rarely used.
So it should not be very difficult to write a layer that takes parameters and transforms them into the mentioned flavours of SQL. Yeah I know. Famous last words. :-)
I also now that there have been some attempts made, however, I will have a somewhat narrower approach. No bells and whistles what so ever.
I have also made an XML schema that should contain steps, parameters and such so now I am writing a utility uses the database abstraction layer to apply those steps on the database. No GUI yet, but I know that it will be needed later on.

Testing

A *full* Unified BPM integration test run will start with "CREATE database", each time building and populating the database from scratch using the update utility and many of the access objects' unit tests. Possibly, in a few years, one can start with a later version.

Structure

The database structure will be an old favourite, a *really* basic tree. nodeid and parentnodeid

1. All objects will have a node in the tree. This makes the security layer easy to implement.
2. All other data will be in sub tables.
   
3. For auditing, the first, and hopefully last(fuzzy, see "custom" above, stuff to maintain on different backends) triggers of the database will be created and will.

Oh, well. Bye then!