I have seen some postings in regards to such and have investigated the same scenarios for SQL deployment myself. I would have to agree that being Enterprise grade Magento should have this type of functionality built-in. The good news it IS, at least in SOME form or fashion, how complete it is I'm not really sure. Here's a sample of a rollback upon exception:
try {
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->beginTransaction();
// do stuff here
$write->commit();
} catch (Exception $e) {
mage::log(__METHOD__ . ':' . __LINE__ . ': Rollback happened.');
$write->rollback();
}
Now if you take a look at app/code/core/Mage/Core/Model/Resource/Setup.php you'll find quite a bit of interesting methods. In particular: _getModifySqlFiles, _rollbackResourceDb and _modifyResourceDb.
_modifyResourceDb is the most interesting to me, since the $actionType here can be rollback and uninstall as well - also note you can use PHP files for your setup files as well.
// Read resource files
$arrAvailableFiles = array();
$sqlDir = dir($sqlFilesDir);
while (false !== ($sqlFile = $sqlDir->read())) {
$matches = array();
if (preg_match('#^'.$resModel.'-'.$actionType.'-(.*)\.(sql|php)$#i', $sqlFile, $matches)) {
$arrAvailableFiles[$matches[1]] = $sqlFile;
}
}
After this code executes:
$arrModifyFiles = $this->_getModifySqlFiles($actionType, $fromVersion, $toVersion, $arrAvailableFiles);
But heres where I'm assuming core Magento devs got lost in the bowels of the EAV resource model and just left it partially complete.
protected function _getModifySqlFiles($actionType, $fromVersion, $toVersion, $arrFiles)
{
$arrRes = array();
switch ($actionType) {
case 'install':
case 'data-install':
...
case 'rollback':
break;
case 'uninstall':
break;
}
return $arrRes;
}
I've not had a chance to really test out the above, but from just my initial investigations of the ORM that is magento and the Autoloading as well as another developer's input on his findings as well.
Ultimately it would be ideal if we can keep all of our changes at least module wise within a version controlled system. Obviously huge data sets that need to be imported shouldn't be managed this way but for these small incremental changes I want to push to staging, production test and if it fails pull it back one version and everything is back to normal.
Obviously theres no one ideal solution for deployment with so many clients having different requirements and needs but a general way of doing this would help with code/SQL deployment. It's kind of ironic that Enterprise has CMS staging, and the ability for modular development of code, but the DB has not gotten as much love.
There is a related question that is noting how currently we are doing it with some specialized scripts "home-brewed" that essentially do:
Doing a MySQLDump or backup, then doing a replace on the BASE_URLs in the SQL file.
Best practices for Magento Deployment
Another tool to look at would be Phing.
If anyone has time to investigate the "rollback" and "uninstall" processes that seem to be implemented and report their findings would be helpful to me as well.
