Skip to content

Instantly share code, notes, and snippets.

@mbabker

mbabker/joomla.sql Secret

Last active March 15, 2016 17:44
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mbabker/40a31c33f277886f17c0 to your computer and use it in GitHub Desktop.
Save mbabker/40a31c33f277886f17c0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
joomla.sql
CREATE DATABASE `joomla_stub` DEFAULT CHARACTER SET utf8;
USE joomla_stub;
# Dump of table jos_assets
# ------------------------------------------------------------
DROP TABLE IF EXISTS `jos_assets`;
CREATE TABLE `jos_assets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`parent_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set parent.',
`lft` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
`rgt` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
`level` int(10) unsigned NOT NULL COMMENT 'The cached level in the nested tree.',
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The unique name for the asset.\n',
`title` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The descriptive title for the asset.',
`rules` varchar(5120) COLLATE utf8_unicode_ci NOT NULL COMMENT 'JSON encoded access control.',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_asset_name` (`name`),
KEY `idx_lft_rgt` (`lft`,`rgt`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
LOCK TABLES `jos_assets` WRITE;
INSERT INTO `jos_assets` (`id`, `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)
VALUES
(1,0,0,105,0,'root.1','Root Asset','{\"core.login.site\":{\"6\":1,\"2\":1},\"core.login.admin\":{\"6\":1},\"core.login.offline\":{\"6\":1},\"core.admin\":{\"8\":1},\"core.manage\":{\"7\":1},\"core.create\":{\"6\":1,\"3\":1},\"core.delete\":{\"6\":1},\"core.edit\":{\"6\":1,\"4\":1},\"core.edit.state\":{\"6\":1,\"5\":1},\"core.edit.own\":{\"6\":1,\"3\":1}}'),
(2,1,1,2,1,'com_admin','com_admin','{}'),
(3,1,3,6,1,'com_banners','com_banners','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1},\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(4,1,7,8,1,'com_cache','com_cache','{\"core.admin\":{\"7\":1},\"core.manage\":{\"7\":1}}'),
(5,1,9,10,1,'com_checkin','com_checkin','{\"core.admin\":{\"7\":1},\"core.manage\":{\"7\":1}}'),
(6,1,11,12,1,'com_config','com_config','{}'),
(7,1,13,16,1,'com_contact','com_contact','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1},\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[],\"core.edit.own\":[]}'),
(8,1,17,20,1,'com_content','com_content','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1},\"core.create\":{\"3\":1},\"core.delete\":[],\"core.edit\":{\"4\":1},\"core.edit.state\":{\"5\":1},\"core.edit.own\":[]}'),
(9,1,21,22,1,'com_cpanel','com_cpanel','{}'),
(10,1,23,24,1,'com_installer','com_installer','{\"core.admin\":[],\"core.manage\":{\"7\":0},\"core.delete\":{\"7\":0},\"core.edit.state\":{\"7\":0}}'),
(11,1,25,26,1,'com_languages','com_languages','{\"core.admin\":{\"7\":1},\"core.manage\":[],\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(12,1,27,28,1,'com_login','com_login','{}'),
(13,1,29,30,1,'com_mailto','com_mailto','{}'),
(14,1,31,32,1,'com_massmail','com_massmail','{}'),
(15,1,33,34,1,'com_media','com_media','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1},\"core.create\":{\"3\":1},\"core.delete\":{\"5\":1}}'),
(16,1,35,36,1,'com_menus','com_menus','{\"core.admin\":{\"7\":1},\"core.manage\":[],\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(17,1,37,38,1,'com_messages','com_messages','{\"core.admin\":{\"7\":1},\"core.manage\":{\"7\":1}}'),
(18,1,39,74,1,'com_modules','com_modules','{\"core.admin\":{\"7\":1},\"core.manage\":[],\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(19,1,75,78,1,'com_newsfeeds','com_newsfeeds','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1},\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[],\"core.edit.own\":[]}'),
(20,1,79,80,1,'com_plugins','com_plugins','{\"core.admin\":{\"7\":1},\"core.manage\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(21,1,81,82,1,'com_redirect','com_redirect','{\"core.admin\":{\"7\":1},\"core.manage\":[]}'),
(22,1,83,84,1,'com_search','com_search','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1}}'),
(23,1,85,86,1,'com_templates','com_templates','{\"core.admin\":{\"7\":1},\"core.manage\":[],\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(24,1,87,90,1,'com_users','com_users','{\"core.admin\":{\"7\":1},\"core.manage\":[],\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(26,1,91,92,1,'com_wrapper','com_wrapper','{}'),
(27,8,18,19,2,'com_content.category.2','Uncategorised','{\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[],\"core.edit.own\":[]}'),
(28,3,4,5,2,'com_banners.category.3','Uncategorised','{\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(29,7,14,15,2,'com_contact.category.4','Uncategorised','{\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[],\"core.edit.own\":[]}'),
(30,19,76,77,2,'com_newsfeeds.category.5','Uncategorised','{\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[],\"core.edit.own\":[]}'),
(32,24,88,89,1,'com_users.category.7','Uncategorised','{\"core.create\":[],\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(33,1,93,94,1,'com_finder','com_finder','{\"core.admin\":{\"7\":1},\"core.manage\":{\"6\":1}}'),
(34,1,95,96,1,'com_joomlaupdate','com_joomlaupdate','{\"core.admin\":[],\"core.manage\":[],\"core.delete\":[],\"core.edit.state\":[]}'),
(35,1,97,98,1,'com_tags','com_tags','{\"core.admin\":[],\"core.manage\":[],\"core.manage\":[],\"core.delete\":[],\"core.edit.state\":[]}'),
(36,1,99,100,1,'com_contenthistory','com_contenthistory','{}'),
(37,1,101,102,1,'com_ajax','com_ajax','{}'),
(38,1,103,104,1,'com_postinstall','com_postinstall','{}'),
(39,18,40,41,2,'com_modules.module.1','Main Menu','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(40,18,42,43,2,'com_modules.module.2','Login','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(41,18,44,45,2,'com_modules.module.3','Popular Articles','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(42,18,46,47,2,'com_modules.module.4','Recently Added Articles','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(43,18,48,49,2,'com_modules.module.8','Toolbar','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(44,18,50,51,2,'com_modules.module.9','Quick Icons','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(45,18,52,53,2,'com_modules.module.10','Logged-in Users','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(46,18,54,55,2,'com_modules.module.12','Admin Menu','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(47,18,56,57,2,'com_modules.module.13','Admin Submenu','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(48,18,58,59,2,'com_modules.module.14','User Status','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(49,18,60,61,2,'com_modules.module.15','Title','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(50,18,62,63,2,'com_modules.module.16','Login Form','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(51,18,64,65,2,'com_modules.module.17','Breadcrumbs','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(52,18,66,67,2,'com_modules.module.79','Multilanguage status','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}'),
(53,18,68,69,2,'com_modules.module.86','Joomla Version','{\"core.delete\":[],\"core.edit\":[],\"core.edit.state\":[]}');
UNLOCK TABLES;
# Dump of table jos_categories
# ------------------------------------------------------------
DROP TABLE IF EXISTS `jos_categories`;
CREATE TABLE `jos_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`asset_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.',
`parent_id` int(10) unsigned NOT NULL DEFAULT '0',
`lft` int(11) NOT NULL DEFAULT '0',
`rgt` int(11) NOT NULL DEFAULT '0',
`level` int(10) unsigned NOT NULL DEFAULT '0',
`path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`extension` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`note` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`description` longtext COLLATE utf8mb4_general_ci NOT NULL,
`published` tinyint(1) NOT NULL DEFAULT '0',
`checked_out` int(11) unsigned NOT NULL DEFAULT '0',
`checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`access` int(10) unsigned NOT NULL DEFAULT '0',
`params` mediumtext COLLATE utf8mb4_general_ci NOT NULL,
`metadesc` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'The meta description for the page.',
`metakey` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'The meta keywords for the page.',
`metadata` varchar(2048) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'JSON encoded metadata properties.',
`created_user_id` int(10) unsigned NOT NULL DEFAULT '0',
`created_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_user_id` int(10) unsigned NOT NULL DEFAULT '0',
`modified_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`hits` int(10) unsigned NOT NULL DEFAULT '0',
`language` char(7) COLLATE utf8mb4_general_ci NOT NULL,
`version` int(10) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `cat_idx` (`extension`,`published`,`access`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`),
KEY `idx_path` (`path`(191)),
KEY `idx_left_right` (`lft`,`rgt`),
KEY `idx_language` (`language`),
KEY `idx_alias` (`alias`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
LOCK TABLES `jos_categories` WRITE;
INSERT INTO `jos_categories` (`id`, `asset_id`, `parent_id`, `lft`, `rgt`, `level`, `path`, `extension`, `title`, `alias`, `note`, `description`, `published`, `checked_out`, `checked_out_time`, `access`, `params`, `metadesc`, `metakey`, `metadata`, `created_user_id`, `created_time`, `modified_user_id`, `modified_time`, `hits`, `language`, `version`)
VALUES
(1,0,0,0,11,0,'','system','ROOT',X'726F6F74','','',1,0,'0000-00-00 00:00:00',1,'{}','','','{}',42,'2011-01-01 00:00:01',0,'0000-00-00 00:00:00',0,'en-GB',1),
(2,27,1,1,2,1,'uncategorised','com_content','Uncategorised',X'756E63617465676F7269736564','','',1,0,'0000-00-00 00:00:00',1,'{\"category_layout\":\"\",\"image\":\"\"}','','','{\"author\":\"\",\"robots\":\"\"}',42,'2011-01-01 00:00:01',0,'0000-00-00 00:00:00',0,'en-GB',1),
(3,28,1,3,4,1,'uncategorised','com_banners','Uncategorised',X'756E63617465676F7269736564','','',1,0,'0000-00-00 00:00:00',1,'{\"category_layout\":\"\",\"image\":\"\"}','','','{\"author\":\"\",\"robots\":\"\"}',42,'2011-01-01 00:00:01',0,'0000-00-00 00:00:00',0,'en-GB',1),
(4,29,1,5,6,1,'uncategorised','com_contact','Uncategorised',X'756E63617465676F7269736564','','',1,0,'0000-00-00 00:00:00',1,'{\"category_layout\":\"\",\"image\":\"\"}','','','{\"author\":\"\",\"robots\":\"\"}',42,'2011-01-01 00:00:01',0,'0000-00-00 00:00:00',0,'en-GB',1),
(5,30,1,7,8,1,'uncategorised','com_newsfeeds','Uncategorised',X'756E63617465676F7269736564','','',1,0,'0000-00-00 00:00:00',1,'{\"category_layout\":\"\",\"image\":\"\"}','','','{\"author\":\"\",\"robots\":\"\"}',42,'2011-01-01 00:00:01',0,'0000-00-00 00:00:00',0,'en-GB',1),
(7,32,1,9,10,1,'uncategorised','com_users','Uncategorised',X'756E63617465676F7269736564','','',1,0,'0000-00-00 00:00:00',1,'{\"category_layout\":\"\",\"image\":\"\"}','','','{\"author\":\"\",\"robots\":\"\"}',42,'2011-01-01 00:00:01',0,'0000-00-00 00:00:00',0,'en-GB',1);
UNLOCK TABLES;
# Dump of table jos_languages
# ------------------------------------------------------------
DROP TABLE IF EXISTS `jos_languages`;
CREATE TABLE `jos_languages` (
`lang_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`lang_code` char(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`title` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`title_native` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`sef` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`image` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`description` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`metakey` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`metadesc` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`sitename` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`published` int(11) NOT NULL DEFAULT '0',
`access` int(10) unsigned NOT NULL DEFAULT '0',
`ordering` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`lang_id`),
UNIQUE KEY `idx_sef` (`sef`),
UNIQUE KEY `idx_image` (`image`),
UNIQUE KEY `idx_langcode` (`lang_code`),
KEY `idx_access` (`access`),
KEY `idx_ordering` (`ordering`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
LOCK TABLES `jos_languages` WRITE;
INSERT INTO `jos_languages` (`lang_id`, `lang_code`, `title`, `title_native`, `sef`, `image`, `description`, `metakey`, `metadesc`, `sitename`, `published`, `access`, `ordering`)
VALUES
(1,'en-GB','English (UK)','English (UK)','en','en','','','','',1,1,1);
UNLOCK TABLES;
# Dump of table jos_users
# ------------------------------------------------------------
DROP TABLE IF EXISTS `jos_users`;
CREATE TABLE `jos_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`username` varchar(150) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`email` varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`password` varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`block` tinyint(4) NOT NULL DEFAULT '0',
`sendEmail` tinyint(4) DEFAULT '0',
`registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`activation` varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`params` text COLLATE utf8_general_ci NOT NULL,
`lastResetTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date of last password reset',
`resetCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Count of password resets since lastResetTime',
`otpKey` varchar(1000) COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'Two factor authentication encrypted keys',
`otep` varchar(1000) COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'One time emergency passwords',
`requireReset` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Require user to reset password on next login',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`(191)),
KEY `idx_block` (`block`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
LOCK TABLES `jos_users` WRITE;
INSERT INTO `jos_users` (`id`, `name`, `username`, `email`, `password`, `block`, `sendEmail`, `registerDate`, `lastvisitDate`, `activation`, `params`, `lastResetTime`, `resetCount`, `otpKey`, `otep`, `requireReset`)
VALUES
(42,'Super User','admin','admin@localhost.test','$2y$10$Sl2OL5p9ZJcvlfRMPpdvvuntUYiV9Ro6mokrKR55a5OBXP5p9mJu.',0,1,'2016-03-15 12:00:00','2016-03-15 12:00:00','0','','0000-00-00 00:00:00',0,'','',0);
UNLOCK TABLES;
Raw
testquery.sql
SELECT c.*, a.name AS asset_name, l.title AS language_title, u.name AS user_name FROM jos_categories AS c LEFT JOIN jos_assets AS a ON a.id = c.asset_id LEFT JOIN jos_languages AS l ON l.lang_code = c.language LEFT JOIN jos_users AS u ON u.id = c.created_user_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment