Manipulating databases

Database management is something very difficult to do, because databases are strange things created by strange people and database are also many, each of them doing things the way they think better. There'ra no two databases that thinks the same way.

When I wrote strehler initdb command I was quite impressed by the deploy function of DBIx::Class::Schema that worked like magic , creating databases as I want, But creating a database from zero is an easy task, changing an existing one, possibly without DROP TABLE commands that trash data is more interesting.

There're packages that do exactly this, like DBIx::Class::Schema::Versioned, but they have a static management. You build your database, you give a version to your schema. You change your database, you give another version and something calculate the diff. It's good, but I was looking for something more dynamic.

Why doesn't a dynamic way to change database exist? Because, obviously, changing a database dynamically is a bad idea in many cases, you should have control on it, but I wasn't thinking about a CMS that change databases through a web interface, I just wanted to do it as a script, to embed it in my strehler script.
And I want to cver just the easy case where I add brand new tables, no modifications to the existing ones.

My first option was starting from schema. I took my existing DBIx::Class::Schema, I added my tables to it with register method and then I tried a new deploy, turning off the DROP TABLE option. Result was good but... DBIC gave me back a bunch of error about existing tables that it cannot create again, so the output of the operation was really dirty.

Problem is that noone considered the concept of deploying just a table and, for me, deploying a schema containing only new tables wasn't enough, because this way all the foreign keys to old tables wouldn't be created.

Solution I found is dirty but... it works and, for now, I didn't find a way to break it. Starting point is the same of the precedent case: take DBIx::Class::Schema and add new tables to it registering DBIx::Classes. Then there's some dirty work you have to do by yourself.

A step back: deploy function in DBIx::Class::Schema starts from your schema, create queries from it considering the target database and then execute them. The tool that extract queries from yout classes is SQL::Translator and you can use it also without DBIx::Schema::Class deploy function.

You can run SQL::Translator on your original schema, obtaining a list of queries to create it. Then you can run it on you database modified using register function. Considering that you're just adding tables you'll have a new array with same queries from before and a couple of new ones.

Deploying new tables only is about calculating the diff between these two arrays and run just it on your DBIC connector... with little tricks to avoid running queries in wrong order.

If you want to see an implementation of this little hack you can see it in Strehler::Element::Role::Maintainer.