Jonathan Miller
75c34345f9
Generic: Repo Health / Site Health (push) Has been cancelled
Generic: Repo Health / Access control (push) Has been cancelled
Generic: Repo Health / Scripts governance (push) Has been cancelled
Generic: Repo Health / Repository health (push) Has been cancelled
Generic: Repo Health / Report Issues (push) Has been cancelled
Rename root source directory from src/ to source/ and update all references in Makefile, manifest.xml, .gitignore, CI workflows, and wiki documentation. Internal Joomla namespace paths (src/Extension) are unchanged as they are plugin-internal structure. CI workflows updated to check source/ first with src/ fallback for backward compatibility across repos. Authored-by: Moko Consulting Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
106 lines
6.2 KiB
SQL
106 lines
6.2 KiB
SQL
-- MokoJoomCross 01.00.00 — Initial schema
|
|
-- Copyright (C) 2026 Moko Consulting. All rights reserved.
|
|
-- SPDX-License-Identifier: GPL-3.0-or-later
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokojoomcross_services` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`title` varchar(255) NOT NULL DEFAULT '',
|
|
`alias` varchar(400) NOT NULL DEFAULT '',
|
|
`service_type` varchar(50) NOT NULL DEFAULT '' COMMENT 'facebook, twitter, linkedin, mastodon, bluesky, mailchimp, telegram, discord, slack',
|
|
`credentials` text NOT NULL COMMENT 'JSON encrypted — API keys, tokens, secrets',
|
|
`params` text NOT NULL COMMENT 'JSON — service-specific configuration',
|
|
`published` tinyint(1) NOT NULL DEFAULT 0,
|
|
`ordering` int(11) NOT NULL DEFAULT 0,
|
|
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
`created_by` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`checked_out` int(10) unsigned,
|
|
`checked_out_time` datetime,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_published` (`published`),
|
|
KEY `idx_service_type` (`service_type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokojoomcross_posts` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`article_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to #__content.id',
|
|
`service_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to #__mokojoomcross_services.id',
|
|
`status` varchar(20) NOT NULL DEFAULT 'queued' COMMENT 'queued, posting, posted, failed, scheduled',
|
|
`message` text NOT NULL COMMENT 'Rendered message sent to platform',
|
|
`platform_post_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Post ID returned by platform',
|
|
`platform_response` text NOT NULL COMMENT 'JSON — full API response from platform',
|
|
`scheduled_at` datetime DEFAULT NULL COMMENT 'When to post (NULL = immediately)',
|
|
`posted_at` datetime DEFAULT NULL COMMENT 'When actually posted',
|
|
`retry_count` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`error_message` text NOT NULL,
|
|
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_article` (`article_id`),
|
|
KEY `idx_service` (`service_id`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_scheduled` (`scheduled_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokojoomcross_templates` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`service_type` varchar(50) NOT NULL DEFAULT '' COMMENT 'Platform this template is for (or "default")',
|
|
`title` varchar(255) NOT NULL DEFAULT '',
|
|
`template_body` text NOT NULL COMMENT 'Template with placeholders: {title}, {url}, {introtext}, {image}, {category}, {author}',
|
|
`published` tinyint(1) NOT NULL DEFAULT 1,
|
|
`ordering` int(11) NOT NULL DEFAULT 0,
|
|
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_service_type` (`service_type`),
|
|
KEY `idx_published` (`published`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokojoomcross_logs` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`post_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to #__mokojoomcross_posts.id',
|
|
`service_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to #__mokojoomcross_services.id',
|
|
`level` varchar(20) NOT NULL DEFAULT 'info' COMMENT 'info, warning, error',
|
|
`message` text NOT NULL,
|
|
`context` text NOT NULL COMMENT 'JSON — additional context data',
|
|
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_post` (`post_id`),
|
|
KEY `idx_service` (`service_id`),
|
|
KEY `idx_level` (`level`),
|
|
KEY `idx_created` (`created`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Insert default templates
|
|
INSERT INTO `#__mokojoomcross_templates` (`service_type`, `title`, `template_body`, `published`, `ordering`, `created`) VALUES
|
|
('default', 'Default Template', '{title}\n\n{introtext}\n\n{url}', 1, 1, NOW()),
|
|
('twitter', 'Twitter/X Default', '{title}\n\n{url}', 1, 2, NOW()),
|
|
('mastodon', 'Mastodon Default', '{title}\n\n{introtext}\n\n{url}\n\n#Joomla', 1, 3, NOW()),
|
|
('mailchimp', 'Mailchimp Default', '<h1>{title}</h1>\n<p>{introtext}</p>\n<p><a href=\"{url}\">Read more</a></p>', 1, 4, NOW()),
|
|
('telegram', 'Telegram Default', '<b>{title}</b>\n\n{introtext}\n\n<a href=\"{url}\">Read more</a>', 1, 5, NOW()),
|
|
('discord', 'Discord Default', '**{title}**\n\n{introtext}\n\n{url}', 1, 6, NOW()),
|
|
('slack', 'Slack Default', '*{title}*\n\n{introtext}\n\n{url}', 1, 7, NOW()),
|
|
('facebook', 'Facebook Default', '{title}\n\n{introtext}\n\n{url}', 1, 8, NOW()),
|
|
('linkedin', 'LinkedIn Default', '{title}\n\n{introtext}\n\n{url}', 1, 9, NOW()),
|
|
('bluesky', 'Bluesky Default', '{title}\n\n{url}', 1, 10, NOW()),
|
|
('threads', 'Threads Default', '{title}\n\n{introtext}\n\n{url}', 1, 11, NOW()),
|
|
('teams', 'Teams Default', '**{title}**\n\n{introtext}\n\n[Read more]({url})', 1, 12, NOW()),
|
|
('medium', 'Medium Default', '{title}\n\n{introtext}\n\n{url}', 1, 13, NOW()),
|
|
('wordpress', 'WordPress Default', '{title}\n\n{introtext}\n\n{url}', 1, 14, NOW()),
|
|
('webhook', 'Webhook Default', '{title}\n\n{introtext}\n\n{url}', 1, 15, NOW()),
|
|
('sendgrid', 'SendGrid Default', '<h1>{title}</h1>\n<p>{introtext}</p>\n<p><a href=\"{url}\">Read more</a></p>', 1, 16, NOW()),
|
|
('brevo', 'Brevo Default', '<h1>{title}</h1>\n<p>{introtext}</p>\n<p><a href=\"{url}\">Read more</a></p>', 1, 17, NOW()),
|
|
('ntfy', 'Ntfy Default', '{title}: {introtext}', 1, 18, NOW()),
|
|
('reddit', 'Reddit Default', '{title}', 1, 19, NOW()),
|
|
('pinterest', 'Pinterest Default', '{title} - {introtext}', 1, 20, NOW());
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokojoomcross_category_rules` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`category_id` int(10) unsigned NOT NULL,
|
|
`service_id` int(10) unsigned NOT NULL,
|
|
`published` tinyint(1) NOT NULL DEFAULT 1,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_category_service` (`category_id`, `service_id`),
|
|
KEY `idx_category` (`category_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|