Structure folder of sql files in my component

Question

What folder should I put my sql files in my component for joomla execute them automatically? If in the first version I created a table and in later version I want to change, how should I organize that files such that joomla know what files it must execute?How does joomla manage these files?

Answer 1

As Milton already wrote you specify where those SQL files are in the install manifest file. There are three different locations for install, uninstall and update.

<install>
    <sql>
        <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
    </sql>
</install>
<uninstall>
    <sql>
        <file driver="mysql" charset="utf8">sql/uninstall.mysql.utf8.sql</file>
    </sql>
</uninstall>
<update>
    <schemas>
        <schemapath type="mysql">sql/updates/mysql</schemapath>
    </schemas>
</update>

As you see, the update one is a bit special as it doesn't specify a single file but a folder. In this folder there should be files following your component versions. On an update Joomla will check that folder and apply the commands in the files in the correct order. It stores the latest applied file in the database so it knows on the next update which one was the last one applied. So the folder should contain files named like this:

• 1.0.0.sql
• 1.0.1.sql
• 1.1.0.sql
• ...

Answer 2

The convention is usually the admin directory, but beyond that it shouldn't matter as long as you reference the files' location correctly inside of your main xml file.

like:

<install>
    <sql>
        <file driver="mysql" charset="utf8">sql/tables/install.sql</file>
    </sql>
</install>

or:

<install>
    <sql>
        <file driver="mysql" charset="utf8">sql/install.sql</file>
    </sql>
</install>