SQL files and the install script

So far we created the #__hello_posts table by hand. A user of the extension cannot do that: everything must happen by itself during installation. For this the manifest names SQL files, and for anything more complex than queries — a PHP install script.

Files

Create the sql folder in the administrator part and the file script.php by hand:

administrator/components/com_hello/
├── hello.xml
├── script.php                           the install script
├── sql/
│   ├── install.mysql.utf8.sql           runs on install
│   ├── uninstall.mysql.utf8.sql         runs on uninstall
│   └── updates/
│       └── mysql/
│           ├── 1.0.0.sql                one file per version
│           └── 1.1.0.sql
└── ...

Creating the table

-- administrator/components/com_hello/sql/install.mysql.utf8.sql
CREATE TABLE IF NOT EXISTS `#__hello_posts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `body` TEXT NOT NULL,
    `published` TINYINT NOT NULL DEFAULT 1,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The file is referenced in the manifest, and the sql folder is added to the files of the administrator part:

<!-- administrator/components/com_hello/hello.xml -->
<install>
    <sql>
        <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
    </sql>
</install>

Now the table is created automatically when the component is installed on a clean site. Joomla replaces the #__ prefix with the real one.

Uninstalling

-- administrator/components/com_hello/sql/uninstall.mysql.utf8.sql
DROP TABLE IF EXISTS `#__hello_posts`;

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

When the administrator uninstalls the component, the table is dropped with it.

Updating the structure

In version 1.1.0 a post needs a category_id column. On sites where the component is already installed the install file does not run again. Changes go into a separate file named after the version number:

-- administrator/components/com_hello/sql/updates/mysql/1.1.0.sql
ALTER TABLE `#__hello_posts` ADD COLUMN `category_id` INT UNSIGNED NOT NULL DEFAULT 0;

<update>
    <schemas>
        <schemapath type="mysql">sql/updates/mysql</schemapath>
    </schemas>
</update>

Joomla remembers the number of the last executed file and on update runs every file with a higher number. For the count to start from the first version, put a 1.0.0.sql file into the folder too — it may be empty. Do not forget to add the same column to install.mysql.utf8.sql: fresh installs do not run the update files.

The install script

The script is a class with methods Joomla calls at different moments of the installation. It is attached with one manifest line:

<scriptfile>script.php</scriptfile>

// administrator/components/com_hello/script.php
defined('_JEXEC') or die;
 
use Joomla\CMS\Factory;
use Joomla\CMS\Installer\InstallerAdapter;
 
class Com_HelloInstallerScript
{
    public function preflight(string $type, InstallerAdapter $adapter): bool
    {
        if (version_compare(PHP_VERSION, '8.1.0', '<')) {
            Factory::getApplication()->enqueueMessage('PHP 8.1 or newer is required', 'error');
 
            return false;
        }
 
        return true;
    }
 
    public function postflight(string $type, InstallerAdapter $adapter): void
    {
        if ($type === 'install' || $type === 'discover_install') {
            Factory::getApplication()->enqueueMessage('The Hello component is installed. Open Components → Hello.');
        }
    }
}

The class name is the extension name with a capital letter plus InstallerScript: for the module it is Mod_HelloInstallerScript. After installation the administrator sees the message from postflight().

Script methods

Method	When it is called
`preflight($type, $adapter)`	Before an install or update; `false` cancels it
`install($adapter)`	On the first install
`update($adapter)`	On an update
`uninstall($adapter)`	On uninstall
`postflight($type, $adapter)`	After an install or update

The $type argument is the string install, update, discover_install or uninstall.

How to check

Uninstall the component in System → Manage → Extensions — its folders and the table disappear, so save a copy of the files first. Put the folders back and install the component through Discover: the table is created again, and the script message is shown at the top.

Common mistake

An update file that was already released gets edited after the fact. On sites where it has run Joomla does not start it again, and the table structure of your users drifts apart. Create a new file with a new version number for every change.

Thanks for reading!