Can I use a .sql file in my Magento module installer?

Question

I have a large SQL file. Can I use this in my module installer?

Answer 1

Yes, if you follow the naming conventions for install scripts. For example:

app/code/local/Your/Module/sql/your_module_setup/install-1.0.0.sql
app/code/local/Your/Module/sql/your_module_setup/upgrade-1.0.0-1.0.1.sql

Or as data install script (if you are not adding/changing tables):

app/code/local/Your/Module/data/your_module_setup/data-install-1.0.0.sql app/code/local/Your/Module/data/your_module_setup/data-upgrade-1.0.0-1.0.1.sql

Update: for data install scripts, the SQL extension is not working.

You need to define the setup resource your_module_setup and the current version number in the module's config.xml like this:

<config>
    <modules>
        <Your_Module>
             <version>1.0.0</version>
        </Your_Module>
    </modules>
    <global>
        <resources>
            <your_module_setup>
                <setup>
                    <module>Your_Module</module>
                    <class>Mage_Core_Model_Resource_Setup</class>
                </setup>
            </your_module_setup>
        </resources>
    </global>
</config>

---

Clarification: Magento looks for files with this pattern that either end in ".php" or ".sql", you should only have one of them per upgrade, but not both.

If you want to know how they are executed, see Mage_Core_Model_Resource_Setup::modifyResourceDb()