SQL Queries During Component Install, Uninstall and Update

During the install, uninstall or update phase of a component, you can execute SQL queries through the use of SQL text files.

1. Create Table

There are some common fields in each table - id, title, alias, description, published, catid, ordering, image, params, note, created_by, created, modified, access, hits.

A. Create new table

For the com_planets component, we will be creating table named planets. Add the following code in install and update SQL file.

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

B. Create new table similar to existing table

CREATE TABLE IF NOT EXISTS `#__table_name` LIKE `#__existing_table`;

Then, you can copy data

INSERT `#__table_name` SELECT * FROM `#__existing_table`;

2. Alter Table Structure

Add new column

ALTER TABLE `#__table_name` ADD `created` DATETIME AFTER `created_by`;

Drop or delete column

ALTER TABLE `#__table_name` DROP `column_name`;

Change column data type

ALTER TABLE `#__tfm_mail` MODIFY `column_name` VARCHAR(400);

Rename column and also change its data type 

ALTER TABLE `#__table_name` CHANGE `old_column` `new_column` MEDIUMTEXT;

3. Update Table Data

Set Column to some value

UPDATE `#__table_name` SET `column_name` = NULL;

Copy one column to another

UPDATE `#__table_name` SET `created` = `modified`; 

4. Drop or Delete Table

DROP TABLE `#__table_name`;