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!

At it again.

Well.

I have now started implementing the most basic functionality, the interface between the agent and the broker.

Some stuff, not anything fancy but hopefully someone gets a bit helped:

1. I use Eclipse and the following builder script to update the development server:
   Location:

   /usr/bin/rsync

   Parameters:

   -vtr -e "ssh -i /path/to/client/certificate/id_rsa" /source/code/dir/ user@server.domain.se:/destinationdir

2. Found out how to import relatively within a package. This is from the server unit test importing the other modules.
   

   from .server import *
   from .session import *
   

3. I use the PyDev Eclipse plugin when doing Python.
4. I use mod_wsgi for the web server scripting. Very versatile indeed.Example here.
   
5. The broker server itself is only a class declaration. The access code for SOAP, JSON or whatever is in a separate layer, allowing for unit testing of high-level functionality without involving serialization. Many forget this separation even though I feel it is an important one. I believe that soaplib, for example, should be unit tested by it's developers and not by me. I will have integration tests but unit testing stubs rarely gives anything in return.

I am considering buying the Clean Code-book, which some really geeky friends of mine recommend, nay urge me, to buy but I am a bit scared of it.

I am also thinking of deployment, the distutils stuff will possibly do the trick, but I have to learn how.

Labor pains

Hi.
I have now set a preliminary structure for at least the lower levels of the system.
I want the structure of the actual, physical underlying system to be really simple, while not having limitations that would making it unable to handle all the concepts of, for example BPMN. At some point, it should be possible for script generator/designers to use XPDL, for example.

So I decided to design Unified BPM in levels, the names of which I have not decided on yet, not that it matters that much.
The lowest level defines these entities and mechanisms:

Script
It defines the script by defining a parameter passing format and mechanism and makes it possible for a script execution to start on one computer and continue on the next.
And also, as a consequence making it possible for a script to spawn child processes on other computers.
Unified BPM scripts will have a standardised look to allow for both script generation and customisation.

Broker
It defines the broker, the central entity which all IPC within Unified BPM passes. The broker is responsible for logging all that happens and to handle IPC security.
The broker is also connected to the Unified BPM database, which should be a deliberatly simply designed SQL database. It should be possible to use any of the large SQL-compliant database servers as backend without any (significant) problems.
The broker is also aware of other brokers, making it possible to forward messages and progress messages across networks and organisations.

Agent
It defines the agent, the client "listener" and "doer", that is responsible for receiving IPC and carry out those instructions on a client computer.
To reduce complexity and increase safety the agent actually don't have any open ports but has open requests to the broker which when timed out are immediately made again. This makes the agents as responsive as if they had listening, open ports. Also, it can use SOAP and https for encryption and verification and don't have do have much of internal security.
It also has some other functions, like managing and debugging client scripts, monitoring client performance counters and many other things.


Ok, that was the lowest level.
Above that level, there can be different kinds of controlling mechanisms, script generators and BPM designers.
So regarding the design of this system, it will be bottom-up when designing the lowest level, and top down when designing the upper levels.
The reason for that being that the lower level should be really open to allow for different BPM paradigms on top. Or maybe it will be more of a complex/simple thing.

I guess we'll have to see how all that works out. :-)