How to update database tables of already installed custom module

Question

I have installed a custom module in Magento 1.8. The database tables were created automatically during set-up. Now I want to update database structure. But I don't want to reinstall module. Also I don't want to alter database directly. Is there anyway to do it by a magento way.

Thanks

EDIT:

Mysql set-up file location - /app/code/local/Namespace/Module/sql/module_setup/mysql4-install-0.1.0.php

Answer 1

Create update scripts for you module, or if it's community module and you don't want to modify it, create a new module that depends on the original one and add the upgrade scripts in your new module.
Let's say the module you installed is called Namespace_Module and it has the version 0.1.0 (check the version inside app/code/local/Namespace/Module/etc.xml).
In case you are not allowed to modify the original module create these files. (If you are allowed to modify it skip them).
You will need a new module. Let's call that MyNamespace_MyModule that should be located in the local codepool.

app/etc/modules/MyNamespace_MyModule.xml - the declaration file.

<?xml version="1.0"?>
<config>
    <modules>
        <MyNamespace_MyModule>
            <active>true</active>
            <codePool>local</codePool>
            <depends>
                <Namespace_Module /> <!--depends on the original module-->
            </depends>
        </MyNamespace_MyModule>
    </modules>
</config>

app/code/local/MyNamespace/MyModule/etc/config.xml - the configuration file

<?xml version="1.0"?>
<config>
    <modules>
        <MyNamespace_MyModule>
            <version>1.0.0</version>
        </MyNamespace_MyModule>
    </modules>
    <global>
        <resources>
            <mynamespace_mymodule_setup>
                <setup>
                    <module>MyNamespace_MyModule</module>
                </setup>
            </mynamespace_mymodule_setup>
        </resources>
    </global>
</config>

app/code/local/MyNamespace/MyModule/sql/mynamespace_mymodule_setup/install-1.0.0.php - your update script. In the example I used this upgrade script just adds a new column to a table.

<?php
$this->getConnection()->addColumn($this->getTable('module/table'), 'some_flag', array(
    'type' => Varien_Db_Ddl_Table::TYPE_INTEGER,
    'length' => 1,
    'nullable' => true,
    'default' => 0,
    'comment' => 'Some comment here'
));

In case you are allowed to modify the original module put the contents of the file above inside the original module but instead of naming it install, name it upgrade and use the correct versions:

app/code/local/Namespace/Module/sql/module_setup/upgrade-0.1.0-0.1.1.php.
And now you need to change in the config.xml of your module the version tag. From 0.1.0 to 0.1.1. Clear the cache and the upgrade will run smoothly.