When installing our component, we will need to create the necessary table. After all, we want to make things as automatic as possible and, unlike in modules, components are able to execute SQL queries during installation.

As there is the possibility of running queries during component installation, we are going to make use of this useful feature. In fact, it's quite easy. First we need to modify our tinynews.xml file, as follows:

...
            </files>
            <install>
            <sql>
            <file charset="utf8" driver="mysql">install.sql</file>
            </sql>
            </install>
            <uninstall>
            <sql>
            <file charset="utf8" driver="mysql">uninstall.sql</file>
            </sql>
            </uninstall>
            <administration>
            <menu>Component Tinynews</menu>
            <files folder="admin">
            <filename>tinynews.php</filename>
            <filename>index.html</filename>
            <filename>install.sql</filename>
            <filename>uninstall.sql</filename>
            </files>
            </administration>
        

Here we have added some install and uninstall tags between our <files> and <administration> part. These tags will be in charge of executing the install.sql file when installing the component, or the uninstall.sql file when uninstalling our component.

Our next step will be to create these two files in our installation package. We will see the structure of our installer, updated with the SQL files, in a moment. But for now, just create the files.

We will start with the install.sql file:

DROP TABLE IF EXISTS `#__tinynews`;
            CREATE TABLE `#__tinynews` (
            `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR( 255 ) NOT NULL ,
            `text` TEXT NOT NULL
            );
        

The file contains two SQL sentences. The first one will drop the table if it already existed, so we can start with a new installation. The second one will actually create the table. Note that we are again using the #__ prefix, instead of placing the prefix ourselves.

Our uninstall.sql file will be even simpler:

DROP TABLE IF EXISTS `#__tinynews`;
        

But what if we don't want to drop the table during installation? This may be because we have created an updated version of our component. Then, instead of dropping the table and creating it later, we will have the following query in our install.sql file:

CREATE TABLE IF NOT EXISTS `#__tinynews` (
            `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR( 255 ) NOT NULL,
            `text` TEXT NOT NULL
            );
        

This will make sure the table is created if it does not already exist, but leave it alone if it does.