Creating and Managing Joomla Database Tables

In Joomla!, components usually manage content using database tables. During the install, update or uninstall phase of a component, you can execute SQL queries through SQL text files.

The install and uninstall SQL text files are stored in and as:

  • admin/sql/
    • install.mysql.utf8.sql
    • uninstall.mysql.utf8.sql

The update SQL text files are stored in and as:

  • admin/sql/updates/mysql/
    • 1.0.0.sql
    • 1.0.1.sql
    • 1.0.2.sql

1. When Component is Installed

1. The file install.mysql.utf8.sql is executed when the component is installed for the first time.

2. When the component is installed, the files in the SQL updates folder (admin/sql/updates/mysql) are read and the name of the last file alphabetically is used to populate the component's version number. The latest version number of the component installed is stored in the #__schemas table of the database.

For automatic update to execute update SQL files in future versions, this value must be present the #__schemas table. For this reason, it is good practice to create a SQL update file for each version (even if it is empty or just has a comment). This way the #__schemas version will always match the component version.

admin/sql/install.mysql.utf8.sql

CREATE TABLE IF NOT EXISTS `#__planets` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `asset_id` INT(10) UNSIGNED NOT NULL DEFAULT 0,
    `title` VARCHAR(255),
    `alias` VARCHAR(400),
    `description` MEDIUMTEXT,
    `published` TINYINT(4),
    `image` VARCHAR(1024),
    `params` MEDIUMTEXT,
    `ordering` INT(11),
    `access` INT(10) UNSIGNED,
    `created` DATETIME,
    `created_by` INT(10) UNSIGNED,
    `modified` DATETIME,
    `modified_by` INT(10) UNSIGNED,
    `publish_up` DATETIME,
    `publish_down` DATETIME,
    `hits` INT(10) UNSIGNED,
    `language` CHAR(7),
    `note` VARCHAR(255),
    PRIMARY KEY (`id`)
)
    ENGINE=InnoDB
    DEFAULT CHARSET=utf8mb4
    DEFAULT COLLATE=utf8mb4_unicode_ci;

Table is created in the database when the component is installed. 

2. When Component is Uninstalled

admin/sql/uninstall.mysql.utf8.sql

DROP TABLE IF EXISTS `#__planets`;

We just drop or remove the table when the component is uninstalled.

3. When Component is Updated

Joomla checks the #__schemas table for the current version of the component. Accordingly, it executes the SQL text files in admin/sql/updates/mysql folder. For example, if the current version is 1.0.0 and the new version of component is 1.0.2, then the Joomla will execute two SQL files: 1.0.1.sql and 1.0.2.sql.

Even if you don't require the use of database, you can add an empty file in admin/sql/updates/mysql/1.0.0.sql, just to initialize schema version. In future versions, if you plan to use database tables, the update can be executed automatically.

admin/sql/updates/mysql/1.0.1.sql

This file will be empty.

Read More about SQL Queries

4. Joomla Manifest Files for Extensions

The SQL files are executed if you put in the component_name.xml manifest file. You can add this after namespace tag and before files information.

<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>

The update tag allows you to provide a series of SQL files to update the current schema. For example, in order to go from version 1.0.0 to version 1.0.1 in a MySQL database, a 1.0.1.sql file must be created inside the sql/updates/mysql folder and the <version> tag of the manifest must be updated to 1.0.1.

Also, add the sql folder in the administration files section.

5. Table Class

src/Table/PlanetTable.php

For each database table, you have to define the table class. The model asks the Table to get information or perform database operations.

This table class has to be defined in admin/src/Table/PlanetTable.php file.

namespace TechFry\Component\Planets\Administrator\Table;

defined('_JEXEC') or die;

use Joomla\CMS\Table\Table;
use Joomla\Database\DatabaseDriver;

class PlanetTable extends Table
{
    function __construct(DatabaseDriver $db)
    {
        parent::__construct('#__planets', 'id', $db);
    }
}

Joomla uses the Table object to get item or record, insert records, update records or delete records for the database operations.

Methods in the table object:

  1. load() to load the existing record from the database, passing the primary key of the record.
  2. bind() to set the new values for the fields.
  3. check() to perform any validation.
  4. store() to save the new values to the database.
  5. delete() to delete the record from the database.