Open-source software for Joomla, Gitea, and web platforms. Home of MokoSuite, MokoGitea, and MokoCLI.

Tennessee

standards/coding-sql.-

SQL Standards

Table Naming

Joomla tables use the #__ prefix (replaced at install time) followed by the extension name:

#__mokosuitebackup_profiles
#__mokosuitebackup_records
#__mokosuitecross_posts
#__mokosuitecross_services
#__mokosuitecross_templates
#__mokosuitecommunity_profiles
#__mokosuitecommunity_connections
#__mokoog_tags

Pattern: #__{extensionname}_{entity} — all lowercase, no extra underscores in the extension name portion.

Column Naming

snake_case for all column names
id as the primary key (auto-increment INT UNSIGNED)
created_at, updated_at for timestamps (DATETIME DEFAULT CURRENT_TIMESTAMP)
created_by, modified_by for user tracking (INT DEFAULT 0)
published for Joomla state column (TINYINT(1) DEFAULT 1)
ordering for list ordering (INT DEFAULT 0)
checked_out, checked_out_time for Joomla record locking
params for JSON parameters (TEXT)

Data Types

Use Case	Type	Notes
Primary key	`INT UNSIGNED NOT NULL AUTO_INCREMENT`	Always unsigned
Foreign key	`INT UNSIGNED NOT NULL DEFAULT 0`	Match PK type
Short text	`VARCHAR(255)`	Titles, names
Long text	`TEXT`	Descriptions, JSON
Boolean	`TINYINT(1) NOT NULL DEFAULT 0`	0/1, not BOOLEAN
Datetime	`DATETIME`	Joomla standard
Price/money	`DECIMAL(10,2)`	Never use FLOAT
JSON config	`TEXT`	Store as JSON string
Enum values	`VARCHAR(50)`	Not MySQL ENUM type

Table Creation

Install script at sql/install.mysql.sql:

CREATE TABLE IF NOT EXISTS `#__mokosuitebackup_profiles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL DEFAULT '',
  `description` TEXT,
  `backup_type` VARCHAR(50) NOT NULL DEFAULT 'full',
  `config` TEXT,
  `filters` TEXT,
  `published` TINYINT(1) NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  `checked_out` INT UNSIGNED,
  `checked_out_time` DATETIME,
  `created_by` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `modified_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mokosuitebackup_profiles_published` (`published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

Index Naming

idx_{table}_{column}              -- single column
idx_{table}_{col1}_{col2}         -- composite
unq_{table}_{column}              -- unique constraint
fk_{table}_{referenced_table}     -- foreign key

Update Scripts

Version-numbered SQL migrations at sql/updates/mysql/{version}.sql:

sql/updates/mysql/01.00.00.sql    -- initial (usually empty)
sql/updates/mysql/01.01.00.sql    -- first feature update
sql/updates/mysql/01.02.00.sql    -- second update

Each file contains only the changes for that version:

-- 01.01.00: Add encryption support to profiles
ALTER TABLE `#__mokosuitebackup_profiles`
  ADD COLUMN `encryption_key` VARCHAR(255) DEFAULT NULL AFTER `filters`,
  ADD COLUMN `encryption_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `encryption_key`;

Uninstall Script

sql/uninstall.mysql.sql drops all tables:

DROP TABLE IF EXISTS `#__mokosuitebackup_records`;
DROP TABLE IF EXISTS `#__mokosuitebackup_profiles`;

Drop in reverse dependency order (child tables first).

Query Standards

Always use prepared statements via Joomla's DatabaseInterface:

$db = $this->getDatabase();
$query = $db->getQuery(true)
    ->select($db->quoteName(['id', 'title', 'backup_type']))
    ->from($db->quoteName('#__mokosuitebackup_profiles'))
    ->where($db->quoteName('published') . ' = 1')
    ->order($db->quoteName('ordering') . ' ASC');
$db->setQuery($query);
$results = $db->loadObjectList();

Never concatenate user input into SQL strings
Use $db->quoteName() for identifiers
Use $db->quote() or bound parameters for values
Prefer Joomla query builder over raw SQL
Primary key: rowid (not id)
Timestamps: datec (created), tms (modified) with timestamp type
Entity support: entity column for multi-company
Status column: status (INT)

Pages