Wiki Navigation

Current SF-Active Database Structure

This is the current SF-Active database structure for sf-active 0.9.8, which is current cvs, not stable. The idea is to improve upon this database, make some changes required for additional features, and then get a codebase going ASAP.

-- 
-- Table structure for table `category`
-- 

CREATE TABLE `category` (
  `name` varchar(200) default NULL,
  `order_num` smallint(6) unsigned NOT NULL default '0',
  `category_id` smallint(5) unsigned NOT NULL auto_increment,
  `template_name` varchar(200) NOT NULL default '0',
  `creation_date` timestamp(14) NOT NULL,
  `creator_id` smallint(5) unsigned NOT NULL default '0',
  `default_feature_template_name` varchar(200) NOT NULL default '0',
  `shortname` varchar(20) NOT NULL default '',
  `summarylength` smallint(5) unsigned NOT NULL default '10',
  `parentid` smallint(6) unsigned default NULL,
  `newswire` enum('n','s','a','f') NOT NULL default 'n',
  `center` enum('t','f') NOT NULL default 't',
  `catclass` enum('m','t','l','h','i','e','p','o') NOT NULL default 't',
  `description` text,
  PRIMARY KEY  (`category_id`),
  UNIQUE KEY `SHORTNAME_IDX` (`shortname`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `catlink`
-- 

CREATE TABLE `catlink` (
  `catid` smallint(6) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0'
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `event`
-- 

CREATE TABLE `event` (
  `event_id` int(11) NOT NULL auto_increment,
  `start_date` datetime default NULL,
  `end_date` datetime default NULL,
  `duration` int(11) default NULL,
  `location_id` int(11) default NULL,
  `location_other` varchar(50) default NULL,
  `event_topic_id` int(11) default NULL,
  `event_topic_other` varchar(50) default NULL,
  `event_type_id` int(11) default NULL,
  `title` varchar(50) default NULL,
  `location_details` text,
  `contact_name` varchar(50) default NULL,
  `contact_phone` varchar(50) default NULL,
  `contact_email` varchar(50) default NULL,
  `confirmation_number` int(11) default NULL,
  `description` text,
  `event_type_other` varchar(50) default NULL,
  `linked_file` varchar(160) default NULL,
  `mime_type` varchar(50) default NULL,
  `event` varchar(5) default NULL,
  `language_id` tinyint(3) unsigned default NULL,
  `artmime` enum('h','t') default NULL,
  PRIMARY KEY  (`event_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `event_topic`
-- 

CREATE TABLE `event_topic` (
  `event_topic_id` int(11) default NULL,
  `name` varchar(50) default NULL,
  UNIQUE KEY `EVENT_TOPIC_ID_IDX` (`event_topic_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `event_type`
-- 

CREATE TABLE `event_type` (
  `event_type_id` int(11) default NULL,
  `name` varchar(50) default NULL,
  UNIQUE KEY `EVENT_TOPIC_ID_IDX` (`event_type_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `feature`
-- 

CREATE TABLE `feature` (
  `feature_version_id` int(11) NOT NULL auto_increment,
  `feature_id` int(11) NOT NULL default '0',
  `summary` blob,
  `title1` varchar(200) default NULL,
  `title2` varchar(200) default NULL,
  `display_date` varchar(100) default NULL,
  `order_num` int(5) default NULL,
  `category_id` int(5) default NULL,
  `template_name` varchar(200) NOT NULL default '0',
  `modification_date` timestamp(14) NOT NULL,
  `creation_date` timestamp(14) NOT NULL default '00000000000000',
  `creator_id` int(8) NOT NULL default '0',
  `status` char(2) default 'c',
  `tag` varchar(100) default NULL,
  `image` varchar(100) default NULL,
  `version_num` int(5) default '1',
  `is_current_version` int(1) default '1',
  `modifier_id` int(11) default NULL,
  `image_link` varchar(200) default NULL,
  `language_id` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`feature_version_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `feature_dossier`
-- 

CREATE TABLE `feature_dossier` (
  `id_dossier` int(11) NOT NULL auto_increment,
  `id_feature` int(11) default NULL,
  `name` varchar(255) default NULL,
  `cat_id` int(11) default NULL,
  PRIMARY KEY  (`id_dossier`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `feature_photo`
-- 

CREATE TABLE `feature_photo` (
  `photo_id` int(10) unsigned NOT NULL auto_increment,
  `creator_id` int(8) unsigned NOT NULL default '0',
  `template_name` varchar(40) NOT NULL default '',
  `summary` tinytext NOT NULL,
  `image_link` varchar(255) NOT NULL default '',
  `tag` tinytext NOT NULL,
  `url` varchar(255) NOT NULL default '',
  `display_date` varchar(100) NOT NULL default '',
  `modified` timestamp(14) NOT NULL,
  PRIMARY KEY  (`photo_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `feature_photo_link`
-- 

CREATE TABLE `feature_photo_link` (
  `photo_id` int(10) unsigned NOT NULL default '0',
  `category_id` mediumint(8) unsigned NOT NULL default '0',
  `order_num` int(10) unsigned NOT NULL default '0',
  `status` enum('c','a','h') NOT NULL default 'c',
  `next` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `cat_order` (`category_id`,`order_num`),
  UNIQUE KEY `photo_cat` (`photo_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `status` (`status`),
  KEY `photo_id` (`photo_id`),
  KEY `order_num` (`order_num`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `feature_sequence`
-- 

CREATE TABLE `feature_sequence` (
  `feature_id` int(10) NOT NULL auto_increment,
  PRIMARY KEY  (`feature_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `language`
-- 

CREATE TABLE `language` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  `language_code` varchar(5) NOT NULL default '',
  `order_num` int(5) default NULL,
  `display` enum('t','f') default 't',
  `build` enum('y','n') default 'n',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `LANGUAGE_CODE_IDX` (`language_code`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `location`
-- 

CREATE TABLE `location` (
  `location_id` int(11) default NULL,
  `name` varchar(50) default NULL,
  UNIQUE KEY `LOCATION_ID_IDX` (`location_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `user`
-- 

CREATE TABLE `user` (
  `user_id` smallint(11) unsigned NOT NULL auto_increment,
  `username` varchar(100) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `first_name` varchar(100) NOT NULL default '',
  `last_name` varchar(100) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `phone` varchar(100) NOT NULL default '',
  `lastlogin` datetime default NULL,
  PRIMARY KEY  (`user_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `validation`
-- 

CREATE TABLE `validation` (
  `article_id` int(11) NOT NULL default '0',
  `validated` enum('f','t') default NULL,
  `hash` char(32) default NULL,
  PRIMARY KEY  (`article_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `webcast`
-- 

CREATE TABLE `webcast` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `heading` varchar(90) default NULL,
  `author` varchar(45) default NULL,
  `email` varchar(255) default NULL,
  `link` varchar(255) default NULL,
  `address` varchar(160) default NULL,
  `phone` varchar(20) default NULL,
  `numcomment` int(5) default NULL,
  `modified` datetime default NULL,
  `created` datetime default NULL,
  `mirrored` enum('f','t') default NULL,
  `display` enum('f','t','g','l') default NULL,
  `language_id` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`id`),
  INDEX (`numcomment`),
  INDEX (`display`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `webcast_comment`
-- 

CREATE TABLE `webcast_comment` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `heading` varchar(90) default NULL,
  `author` varchar(45) default NULL,
  `email` varchar(255) default NULL,
  `link` varchar(255) default NULL,
  `address` varchar(160) default NULL,
  `phone` varchar(20) default NULL,
  `modified` datetime default NULL,
  `created` datetime default NULL,
  `mirrored` enum('f','t') default NULL,
  `display` enum('f','t','g','l') default NULL,
  `language_id` tinyint(3) default NULL,
  PRIMARY KEY  (`id`),
  INDEX (`parent_id`),
  INDEX (`display`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `webcast_comment_body`
-- 

CREATE TABLE `webcast_comment_body` (
  `id` int(11) default NULL,
  `mime_type` enum('h','t') default NULL,
  `body` text
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `webcast_media`
-- 

CREATE TABLE `webcast_media` (
  `id` int(11) NOT NULL auto_increment,
  `webcast_id` int(11) default NULL,
  `webcast_comment_id` int(11) default NULL,
  `webcast_comment_id` int(11) default NULL,
  `author` varchar(45) default NULL,
  `description` varchar(255) default NULL,
  `linked_file` varchar(100) default NULL,
  `mime_type` varchar(50) default NULL,
  `modified` datetime,
  `created` datetime,
  PRIMARY KEY  (`id`),
  INDEX (`mime_type`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `webcast_text`
-- 

CREATE TABLE `webcast_text` (
  `id` int(11) unsigned NOT NULL default '0',
  `mime_type` enum('h','t') NOT NULL default 'h',
  `body` text NOT NULL,
  `summary` tinytext NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `summary` (`summary`),
  FULLTEXT KEY `body` (`body`)
) TYPE=MyISAM;
        

-- 
-- Table structure for table `config_options`
-- 

CREATE TABLE `config_options` (
  `key` varchar(50) NOT NULL default '',
  `value` tinytext NOT NULL,
  `section` varchar(20) NOT NULL default '',
  `v_introduced` mediumint(8) unsigned NOT NULL default '0',
  `v_removed` mediumint(8) unsigned default NULL,
  KEY `key` (`key`),
  KEY `v_introduced` (`v_introduced`),
  KEY `v_removed` (`v_removed`)
) TYPE=MyISAM;
        

-- 
-- Table structure for table `config_version`
-- 

CREATE TABLE `config_version` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=2 ;

Download in other formats:

Plain Text