diff options
Diffstat (limited to 'site/app/config/sql/remora.sql')
| -rw-r--r-- | site/app/config/sql/remora.sql | 185 |
1 files changed, 8 insertions, 177 deletions
diff --git a/site/app/config/sql/remora.sql b/site/app/config/sql/remora.sql index f3eaac1..e3f73d4 100644 --- a/site/app/config/sql/remora.sql +++ b/site/app/config/sql/remora.sql @@ -40,7 +40,6 @@ CREATE TABLE `addons` ( `totalreviews` int(11) unsigned NOT NULL default '0', `weeklydownloads` int(11) unsigned NOT NULL default '0', `totaldownloads` int(11) unsigned NOT NULL default '0', - `sharecount` int(11) unsigned NOT NULL, `developercomments` int(11) unsigned default NULL, `inactive` tinyint(1) unsigned NOT NULL default '0', `trusted` tinyint(1) unsigned NOT NULL default '0', @@ -56,8 +55,6 @@ CREATE TABLE `addons` ( `nominationmessage` text, `target_locale` varchar(25) default NULL, `locale_disambiguation` varchar(255) default NULL, - `nominationdate` datetime NOT NULL default '0000-00-00 00:00:00', - `dev_agreement` tinyint(1) unsigned 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`), @@ -76,7 +73,6 @@ CREATE TABLE `addons` ( KEY `inactive` (`inactive`), KEY `target_locale` (`target_locale`), KEY `bayesianrating` (`bayesianrating`), - KEY `sharecount` (`sharecount`), CONSTRAINT `addons_ibfk_1` FOREIGN KEY (`addontype_id`) REFERENCES `addontypes` (`id`), CONSTRAINT `addons_ibfk_2` FOREIGN KEY (`name`) REFERENCES `translations` (`id`), CONSTRAINT `addons_ibfk_3` FOREIGN KEY (`homepage`) REFERENCES `translations` (`id`), @@ -224,25 +220,6 @@ CREATE TABLE `appversions` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `api_auth_tokens` --- - -DROP TABLE IF EXISTS `api_auth_tokens`; -CREATE TABLE `api_auth_tokens` ( - `id` int(11) unsigned NOT NULL auto_increment, - `user_id` int(11) unsigned NOT NULL default '0', - `token` varchar(64) NOT NULL, - `user_agent_hash` varchar(64) NOT NULL, - `user_profile_hash` varchar(64) 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 `user_id` (`user_id`), - KEY `token` (`token`), - CONSTRAINT `api_auth_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `blapps` -- @@ -608,20 +585,6 @@ CREATE TABLE `files` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `global_stats` --- - -DROP TABLE IF EXISTS `global_stats`; -CREATE TABLE `global_stats` ( - `id` int(11) unsigned NOT NULL auto_increment, - `name` varchar(255) NOT NULL default '', - `count` int(10) unsigned NOT NULL default '0', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - UNIQUE KEY `name` (`name`), - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `groups` -- @@ -650,22 +613,6 @@ CREATE TABLE `groups_users` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `licenses` --- - -DROP TABLE IF EXISTS `licenses`; -CREATE TABLE `licenses` ( - `id` int(11) unsigned NOT NULL auto_increment, - `name` int(1) NOT NULL default '-1', - `text` int(11) unsigned default 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 `text` (`text`), - CONSTRAINT `licenses_ibfk_1` FOREIGN KEY (`text`) REFERENCES `translations` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `logs_parsed` -- @@ -824,7 +771,6 @@ CREATE TABLE `tags` ( `application_id` int(11) unsigned default NULL, `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `count` int(11) NOT NULL DEFAULT '0', `weight` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `addontype_id` (`addontype_id`), @@ -894,15 +840,10 @@ CREATE TABLE `users` ( `firstname` varchar(255) NOT NULL default '', `lastname` varchar(255) NOT NULL default '', `nickname` varchar(255) NOT NULL default '', - `bio` int(11) UNSIGNED default NULL, `emailhidden` tinyint(1) unsigned NOT NULL default '0', `sandboxshown` tinyint(1) unsigned NOT NULL default '0', `homepage` varchar(255) default NULL, - `display_collections` tinyint(1) unsigned NOT NULL default '0', - `display_collections_fav` tinyint(1) unsigned NOT NULL default '0', `confirmationcode` varchar(255) NOT NULL default '', - `resetcode` varchar(255) NOT NULL default '', - `resetcode_expires` datetime NOT NULL default '0000-00-00 00:00:00', `notifycompat` tinyint(1) unsigned NOT NULL default '1', `notifyevents` tinyint(1) unsigned NOT NULL default '1', `created` datetime NOT NULL default '0000-00-00 00:00:00', @@ -912,9 +853,7 @@ CREATE TABLE `users` ( UNIQUE KEY `email` (`email`), KEY `unconfirmed` (`created`,`confirmationcode`), KEY `notifycompat` (`notifycompat`), - KEY `notifyevents` (`notifyevents`), - KEY `bio` (`bio`), - CONSTRAINT `users_ibfk_1` FOREIGN KEY (`bio`) REFERENCES `translations` (`id`) + KEY `notifyevents` (`notifyevents`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -925,7 +864,6 @@ DROP TABLE IF EXISTS `versions`; CREATE TABLE `versions` ( `id` int(11) unsigned NOT NULL auto_increment, `addon_id` int(11) unsigned NOT NULL default '0', - `license_id` int(11) unsigned default NULL, `version` varchar(255) NOT NULL default '', `approvalnotes` text, `releasenotes` int(11) unsigned default NULL, @@ -934,10 +872,8 @@ CREATE TABLE `versions` ( PRIMARY KEY (`id`), KEY `addon_id` (`addon_id`), KEY `versions_ibfk_2` (`releasenotes`), - KEY `license_id` (`license_id`), CONSTRAINT `versions_ibfk_1` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`), - CONSTRAINT `versions_ibfk_2` FOREIGN KEY (`releasenotes`) REFERENCES `translations` (`id`), - CONSTRAINT `versions_ibfk_3` FOREIGN KEY (`license_id`) REFERENCES `licenses` (`id`) + CONSTRAINT `versions_ibfk_2` FOREIGN KEY (`releasenotes`) REFERENCES `translations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -977,20 +913,6 @@ CREATE TABLE `versions_summary` ( INDEX (addon_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; --- --- Table structure for table `collections_search_summary` --- This materialized view maintains a indexed summary of the text data in a collection to make search faster --- - -DROP TABLE IF EXISTS `collections_search_summary`; -CREATE TABLE `collections_search_summary` ( - `id` int(11) NOT NULL, - `locale` varchar(10) NOT NULL, - `name` text, - `description` text, - FULLTEXT KEY `name` (`name`,`description`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -- -- Table structure for table `tshirt_requests` -- Used to store addresses of developers who claimed a T-shirt in @@ -1022,13 +944,7 @@ CREATE TABLE `tshirt_requests` ( DROP TABLE IF EXISTS `collections`; CREATE TABLE `collections` ( `id` int(11) unsigned NOT NULL auto_increment, - `uuid` char(36) NOT NULL default '', `name` int(11) unsigned NOT NULL, - `defaultlocale` varchar(10) NOT NULL default 'en-US', - `collection_type` int(11) unsigned NOT NULL DEFAULT '0', - `icondata` blob, - `icontype` varchar(25) NOT NULL default '', - `nickname` varchar(30) NULL, `description` int(11) unsigned NOT NULL, `access` tinyint(1) NOT NULL DEFAULT '0', `listed` tinyint(1) NOT NULL DEFAULT '1', @@ -1036,62 +952,29 @@ CREATE TABLE `collections` ( `subscribers` int(11) unsigned 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', + `addontype_id` int(11) unsigned NOT NULL, `downloads` int(11) unsigned NOT NULL DEFAULT '0', - `application_id` int(10) unsigned default NULL, - `weekly_subscribers` int(11) unsigned NOT NULL default '0', - `monthly_subscribers` int(11) unsigned NOT NULL default '0', - `addonCount` int(11) unsigned NOT NULL default '0', PRIMARY KEY `id` (`id`), - UNIQUE KEY `uuid` (`uuid`), - UNIQUE KEY `nickname` (`nickname`), - KEY (`listed`), - KEY `application_id` (`application_id`), - KEY `name` (`name`), - KEY `description` (`description`), - CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`), - CONSTRAINT `collections_ibfk_2` FOREIGN KEY (`name`) REFERENCES `translations` (`id`), - CONSTRAINT `collections_ibfk_3` FOREIGN KEY (`description`) REFERENCES `translations` (`id`) + INDEX (`listed`), + CONSTRAINT `collections_addontype_ibfk_1` FOREIGN KEY (`addontype_id`) REFERENCES `addontypes`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; + DROP TABLE IF EXISTS `addons_collections`; CREATE TABLE `addons_collections` ( `addon_id` int(11) unsigned NOT NULL , `collection_id` int(11) unsigned NOT NULL , - `user_id` int(11) unsigned default NULL, `added` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', `category` tinyint(4) unsigned default NULL COMMENT 'for interactive collections template', - `comments` int(11) unsigned default NULL, + `comments` int(11) unsigned NOT NULL, `downloads` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY ( `addon_id` , `collection_id` ), KEY `addon_id` (`addon_id`), - KEY `user_id` (`user_id`), KEY `collection_id` (`collection_id`), - KEY `comments` (`comments`), CONSTRAINT `addons_collections_ibfk_1` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`), - CONSTRAINT `addons_collections_ibfk_2` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`), - CONSTRAINT `addons_collections_ibfk_3` FOREIGN KEY (`comments`) REFERENCES `translations` (`id`) + CONSTRAINT `addons_collections_ibfk_2` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; --- --- Table structure for table `collection_promos` --- Named that way because it's not actually a join. --- - -DROP TABLE IF EXISTS `collection_promos`; -CREATE TABLE `collection_promos` ( - `id` int(11) unsigned NOT NULL auto_increment, - `collection_id` int(11) unsigned NOT NULL default '0', - `locale` varchar(10) default NULL, - `title_tagline` int(11) unsigned 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 `collection_id` (`collection_id`), - UNIQUE KEY `one_collection_per_tagline_per_locale` (`collection_id`,`locale`,`title_tagline`), - CONSTRAINT `collection_features_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - DROP TABLE IF EXISTS `collections_tags`; CREATE TABLE `collections_tags` ( `collection_id` int(11) unsigned NOT NULL , @@ -1103,29 +986,6 @@ CREATE TABLE `collections_tags` ( CONSTRAINT `collections_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; -DROP TABLE IF EXISTS `collection_subscriptions`; -CREATE TABLE `collection_subscriptions` ( - `user_id` int(11) unsigned NOT NULL, - `collection_id` int(11) unsigned 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 (`user_id`, `collection_id`), - CONSTRAINT `collections_subscriptions_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`), - CONSTRAINT `collections_subscriptions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `collections_users`; -CREATE TABLE `collections_users` ( - `collection_id` int(11) unsigned NOT NULL default '0', - `user_id` int(11) unsigned NOT NULL default '0', - `role` tinyint(2) unsigned NOT NULL default '5', - PRIMARY KEY (`collection_id`,`user_id`), - KEY `user_id` (`user_id`), - CONSTRAINT `collections_users_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`), - CONSTRAINT `collections_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - DROP TABLE IF EXISTS `mimes`; CREATE TABLE `mimes` ( `id` int(11) unsigned NOT NULL auto_increment, @@ -1220,32 +1080,3 @@ DROP TABLE IF EXISTS `userevents`; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -/* These are my TRIGGERS. */ - -CREATE TRIGGER collections_update_addon_count_insert - AFTER INSERT ON addons_collections - FOR EACH ROW - UPDATE collections AS c - SET c.addonCount = c.addonCount + 1 - WHERE c.id=NEW.collection_id; - -CREATE TRIGGER collections_update_addon_count_delete - AFTER DELETE ON addons_collections - FOR EACH ROW - UPDATE collections AS c - SET c.addonCount = c.addonCount - 1 - WHERE c.id=OLD.collection_id; - -CREATE TRIGGER collections_update_subscriber_count_insert - AFTER INSERT ON collection_subscriptions - FOR EACH ROW - UPDATE collections AS c - SET c.subscribers = c.subscribers + 1 - WHERE c.id=NEW.collection_id; - -CREATE TRIGGER collections_update_subscriber_count_delete - AFTER DELETE ON collection_subscriptions - FOR EACH ROW - UPDATE collections AS c - SET c.subscribers = c.subscribers - 1 - WHERE c.id=OLD.collection_id; |