Database Changelog

5.0.7 to 5.0.8

Database changes

# Added support for storing an extranet id against a content item to allow redirection to a specific page if not logged in.
ALTER TABLE tribalcms_group_content_link ADD COLUMN extranet_id int(20) unsigned NULL;
ALTER TABLE tribalcms_user_content_link ADD COLUMN extranet_id int(20) unsigned NULL;

# Added alt_tag to tribalcms_adverts
ALTER TABLE tribalcms_adverts ADD COLUMN alt_tag varchar(100) NULL;

# Improved newsletters
ALTER TABLE tribalcms_content_nlar CHANGE publish_onsite process_action ENUM( 'email_only', 'email_and_publish', 'email_and_leave_draft' ) NOT NULL DEFAULT 'email_and_leave_draft';
ALTER TABLE tribalcms_content_nlar_tmp CHANGE publish_onsite process_action ENUM( 'email_only', 'email_and_publish', 'email_and_leave_draft' ) NOT NULL DEFAULT 'email_and_leave_draft';
CREATE TABLE `tribalcms_newsletter_recip_log` (
  `newsletter_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `recip_email` varchar(100) NOT NULL,
  `sent` datetime default NULL,
  `status` enum('success','fail') default NULL,
  `subject` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
ALTER TABLE `tribalcms_newsletter_recip` ADD `admin_authtype` enum('local','super') NULL AFTER `admin_id` ;

# Added support to active IPN ( PayPal ) checking.
ALTER TABLE tribalcms_transactions ADD ipn_post TEXT NULL DEFAULT NULL;

# Support for https
alter table tribalcms_site_registry add admin_use_ssl enum ('allow','force') default 'allow' not null after goto_admin_url;

# New admin permissions table
CREATE TABLE `tribalcms_admin_page_perms` ( `filename` varchar(255) NOT NULL default '', `mode` varchar(50) NOT NULL default '' COMMENT 'Can be ''view'', ''edit'', ''manage''.', `require` enum('OR','AND') NOT NULL default 'AND', `authtype` enum('both','local','super') NOT NULL default 'both', `satisfy_permissions` set('perm_manage','perm_sysadmin','perm_publish','perm_author','perm_editmenu','perm_manageforum','perm_override_author_lock','perm_view_users','perm_edit_users','perm_view_groups','perm_edit_groups','perm_translate') NOT NULL default '', PRIMARY KEY (`filename`,`mode`,`require`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `tribalcms_admin_page_perms` (`filename`, `mode`, `require`, `authtype`, `satisfy_permissions`) VALUES ('admin_languages.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('administrators.php', 'view', 'AND', 'both', 'perm_manage'), ('alias_urls.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('content.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('content_equivalence.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('groups.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('home.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('meta.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('newsletters.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('plugins.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('server.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('site_languages.php', 'manage', 'OR', 'both', 'perm_sysadmin'), ('site_languages_start.php', 'view', 'OR', 'both', 'perm_sysadmin'), ('site_languages.php', 'view', 'OR', 'both', 'perm_sysadmin,perm_publish,perm_author,perm_translate'), ('site_registry.php', 'view', 'OR', 'super', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('statistics.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('stats_adverts.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('stats_content_item.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('subscription_plans.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('tasks.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('template.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('templates.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('templates_unregistered.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('users.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('users_export.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('visitor_languages.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('settings.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('workflow_sent_tasks.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('workflow_received_tasks.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('avls_content_equivalence_manage.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('avls_dual_view.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('avls_content_equivalence_set.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('workflow_create_task.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('workflow_view_task.inc.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('visitor_languages_manage.php', 'view', 'AND', 'both', 'perm_sysadmin'), ('site_registry_site.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('administrator_super.php', 'view', 'AND', 'super', 'perm_manage'), ('user.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('newsletter.php', 'view', 'AND', 'both', 'perm_publish,perm_author'), ('settings.php', 'edit', 'OR', 'both', 'perm_sysadmin'), ('createcontent.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('editmetadata.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('images.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('menuitem.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('permissions.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('shortcut_image_browser.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('shortcuts.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('template_browser.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('advert_browser.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('administrator.php', 'view', 'AND', 'both', 'perm_manage'), ('stats_summary_hits.inc.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('install_content_types.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('group_documents.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('stats_monthly_hits.inc.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('stats_monthly_topitems.inc.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('template_block.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('template_menu_section.php', 'view', 'OR', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('adverts.php', 'view', 'AND', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('backup.php', 'view', 'AND', 'both', 'perm_manage,perm_sysadmin,perm_publish,perm_author,perm_editmenu,perm_manageforum,perm_override_author_lock,perm_view_users,perm_edit_users,perm_view_groups,perm_edit_groups,perm_translate'), ('templates.php', 'manage', 'OR', 'both', 'perm_sysadmin'), ('template.php', 'manage', 'OR', 'both', 'perm_sysadmin'), ('content_types.php', 'view', 'OR', 'both', 'perm_sysadmin,perm_publish,perm_author'), ('content_types.php', 'manage', 'AND', 'both', 'perm_sysadmin');

# Improved Categories table
alter table tribalcms_categories add language_id varchar(5) not null default '';
alter table tribalcms_categories add unique (name, language_id);


# Added version to Category Item Link table
ALTER TABLE tribalcms_category_item_link ADD COLUMN version int(10) unsigned NOT NULL;
ALTER TABLE tribalcms_category_item_link DROP PRIMARY KEY;
ALTER TABLE tribalcms_category_item_link ADD PRIMARY KEY (`category_id`,`item_id`,`content_type`,`version`);


# Improved Templates table
ALTER TABLE `tribalcms_templates` ADD `status` ENUM( 'active', 'suspended' ) NOT NULL DEFAULT 'active';

# Improved Subscriptions table
ALTER TABLE `tribalcms_user_subscriptions` CHANGE `plan_id` `group_id` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0';

# Support for keeping Shortcuts and Adverts in sync
CREATE TABLE `tribalcms_equiv_link` (
  `id` int(20) NOT NULL default '0',
  `shortcut_id` int(20) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# New site settings
alter table tribalcms_site_settings add primary key (name);
insert into tribalcms_site_settings values ('mod_rewrite_suffix','Navigation',8,'TEXT', 'mod_rewrite_enabled', '.html');
insert into tribalcms_site_settings values ('mod_rewrite_enabled','Navigation',7,'RADIO(\'true\',\'false\')', NULL, 'true');
insert into tribalcms_site_settings values ('cid_override_key','General',12,'TEXT',NULL,'cID');

# New content related functions
delete from tribalcms_admin_menuitems where parent_id=2;
insert into tribalcms_admin_menuitems values
(8,2,'_CONTENT_FOLDERS_TAB','admin/content.php',1),
(9,2,'_STATISTICS_TAB','admin/statistics.php',2),
(27,2,'_CONTENT_ITEMS_STATISTICS','admin/stats_content_item.php',3),
(28,2,'_META_DATA_TAB','admin/meta.php',4),
(33,2,'_STATISTICS_ADVERTS','admin/stats_adverts.php',5),
(34,2,'_CONTENT_TYPES_TAB','admin/content_types.php',6),
(35,2,'_CATEGORIES_TAB','admin/categories.php',7);



# Removed Missing Templates from admin menu
delete from tribalcms_admin_menuitems where url = 'admin/templates_missing.php';
update tribalcms_admin_menuitems set ordinal = 2 where url = 'admin/templates_unregistered.php';

# Added Taxable to subscription plans
ALTER TABLE tribalcms_subscription_plans ADD `taxable` tinyint(1) NOT NULL default '0' AFTER `country_id`;

# Added support for search default settings
INSERT INTO tribalcms_site_settings VALUES('html_title_weighting','Search',5,'TEXT',NULL,'24');
INSERT INTO tribalcms_site_settings VALUES('html_keyword_weighting','Search',6,'TEXT',NULL,'6');
INSERT INTO tribalcms_site_settings VALUES('html_desc_weighting','Search',7,'TEXT',NULL,'3');
INSERT INTO tribalcms_site_settings VALUES('html_content_bodymain_weighting','Search',8,'TEXT',NULL,'2');
INSERT INTO tribalcms_site_settings VALUES('html_title_wild_weighting','Search',9,'TEXT',NULL,'16');
INSERT INTO tribalcms_site_settings VALUES('html_keyword_wild_weighting','Search',10,'TEXT',NULL,'4');
INSERT INTO tribalcms_site_settings VALUES('html_desc_wild_weighting','Search',11,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('html_content_bodymain_wild_weighting','Search',12,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('html_keyword_exact_match','Search',13,'TEXT',NULL,'false');
INSERT INTO tribalcms_site_settings VALUES('news_title_weighting','Search',14,'TEXT',NULL,'24');
INSERT INTO tribalcms_site_settings VALUES('news_keyword_weighting','Search',15,'TEXT',NULL,'6');
INSERT INTO tribalcms_site_settings VALUES('news_desc_weighting','Search',16,'TEXT',NULL,'3');
INSERT INTO tribalcms_site_settings VALUES('news_content_bodymain_weighting','Search',17,'TEXT',NULL,'2');
INSERT INTO tribalcms_site_settings VALUES('news_title_wild_weighting','Search',18,'TEXT',NULL,'16');
INSERT INTO tribalcms_site_settings VALUES('news_keyword_wild_weighting','Search',19,'TEXT',NULL,'4');
INSERT INTO tribalcms_site_settings VALUES('news_desc_wild_weighting','Search',20,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('news_content_bodymain_wild_weighting','Search',21,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('news_keyword_exact_match','Search',22,'TEXT',NULL,'false');
INSERT INTO tribalcms_site_settings VALUES('document_title_weighting','Search',23,'TEXT',NULL,'24');
INSERT INTO tribalcms_site_settings VALUES('document_keyword_weighting','Search',24,'TEXT',NULL,'6');
INSERT INTO tribalcms_site_settings VALUES('document_desc_weighting','Search',25,'TEXT',NULL,'3');
INSERT INTO tribalcms_site_settings VALUES('document_content_bodymain_weighting','Search',26,'TEXT',NULL,'2');
INSERT INTO tribalcms_site_settings VALUES('document_document_text_weighting','Search',27,'TEXT',NULL,'2');
INSERT INTO tribalcms_site_settings VALUES('document_title_wild_weighting','Search',28,'TEXT',NULL,'16');
INSERT INTO tribalcms_site_settings VALUES('document_keyword_wild_weighting','Search',29,'TEXT',NULL,'4');
INSERT INTO tribalcms_site_settings VALUES('document_desc_wild_weighting','Search',30,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('document_content_bodymain_wild_weighting','Search',31,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('document_document_text_wild_weighting','Search',32,'TEXT',NULL,'1');
INSERT INTO tribalcms_site_settings VALUES('document_keyword_exact_match','Search',33,'TEXT',NULL,'false');

# Enlarged Category name field
ALTER TABLE tribalcms_categories MODIFY name varchar(50) NOT NULL;

#New ordering for tabs under People tab
UPDATE `tribalcms_admin_menuitems` SET `url` = 'admin/users.php' WHERE `tribalcms_admin_menuitems`.`id` =4 LIMIT 1 ;
UPDATE `tribalcms_admin_menuitems` SET `ordinal` = '4' WHERE `tribalcms_admin_menuitems`.`id` =14 LIMIT 1 ;
UPDATE `tribalcms_admin_menuitems` SET `ordinal` = '1' WHERE `tribalcms_admin_menuitems`.`id` =15 LIMIT 1 ;
UPDATE `tribalcms_admin_menuitems` SET `ordinal` = '2' WHERE `tribalcms_admin_menuitems`.`id` =16 LIMIT 1 ;
UPDATE `tribalcms_admin_menuitems` SET `ordinal` = '3' WHERE `tribalcms_admin_menuitems`.`id` =17 LIMIT 1 ;