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.