mysql_schema.php

ProjectPier 源码 很好的项目管理程序

  `id` smallint(5) unsigned NOT NULL auto_increment,
  `project_id` int(10) unsigned NOT NULL default '0',
  `name` varchar(50) <?php echo $default_collation ?> NOT NULL default '',
  `description` text <?php echo $default_collation ?> NOT NULL,
  `success_message` text <?php echo $default_collation ?> NOT NULL,
  `action` enum('add_comment','add_task') <?php echo $default_collation ?> NOT NULL default 'add_comment',
  `in_object_id` int(10) unsigned NOT NULL default '0',
  `created_on` datetime default NULL,
  `created_by_id` int(10) unsigned NOT NULL default '0',
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_by_id` int(10) unsigned NOT NULL default '0',
  `is_visible` tinyint(1) unsigned NOT NULL default '0',
  `is_enabled` tinyint(1) unsigned NOT NULL default '0',
  `order` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>project_messages` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `milestone_id` int(10) unsigned NOT NULL default '0',
  `project_id` int(10) unsigned default NULL,
  `title` varchar(100) <?php echo $default_collation ?> default NULL,
  `text` text <?php echo $default_collation ?>,
  `additional_text` text <?php echo $default_collation ?>,
  `is_important` tinyint(1) unsigned NOT NULL default '0',
  `is_private` tinyint(1) unsigned NOT NULL default '0',
  `comments_enabled` tinyint(1) unsigned NOT NULL default '0',
  `anonymous_comments_enabled` tinyint(1) unsigned NOT NULL default '0',
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by_id` int(10) unsigned default NULL,
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_by_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `milestone_id` (`milestone_id`),
  KEY `project_id` (`project_id`),
  KEY `created_on` (`created_on`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>project_milestones` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `project_id` int(10) unsigned default NULL,
  `name` varchar(100) <?php echo $default_collation ?> default NULL,
  `description` text <?php echo $default_collation ?>,
  `due_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `assigned_to_company_id` smallint(10) NOT NULL default '0',
  `assigned_to_user_id` int(10) unsigned NOT NULL default '0',
  `is_private` tinyint(1) unsigned NOT NULL default '0',
  `completed_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `completed_by_id` int(10) unsigned default NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by_id` int(10) unsigned default NULL,
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_by_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `project_id` (`project_id`),
  KEY `due_date` (`due_date`),
  KEY `completed_on` (`completed_on`),
  KEY `created_on` (`created_on`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>project_task_lists` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `milestone_id` int(10) unsigned NOT NULL default '0',
  `project_id` int(10) unsigned default NULL,
  `name` varchar(100) <?php echo $default_collation ?> default NULL,
  `description` text <?php echo $default_collation ?>,
  `is_private` tinyint(1) unsigned NOT NULL default '0',
  `completed_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `completed_by_id` int(10) unsigned default NULL,
  `created_on` datetime default NULL,
  `created_by_id` int(10) unsigned NOT NULL default '0',
  `updated_on` datetime default NULL,
  `updated_by_id` int(10) unsigned NOT NULL default '0',
  `order` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `milestone_id` (`milestone_id`),
  KEY `project_id` (`project_id`),
  KEY `completed_on` (`completed_on`),
  KEY `created_on` (`created_on`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>project_tasks` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `task_list_id` int(10) unsigned default NULL,
  `text` text <?php echo $default_collation ?>,
  `assigned_to_company_id` smallint(5) unsigned default NULL,
  `assigned_to_user_id` int(10) unsigned default NULL,
  `completed_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `completed_by_id` int(10) unsigned default NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by_id` int(10) unsigned default NULL,
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_by_id` int(10) unsigned default NULL,
  `order` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `task_list_id` (`task_list_id`),
  KEY `completed_on` (`completed_on`),
  KEY `created_on` (`created_on`),
  KEY `order` (`order`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>project_users` (
  `project_id` int(10) unsigned NOT NULL default '0',
  `user_id` int(10) unsigned NOT NULL default '0',
  `created_on` datetime default NULL,
  `created_by_id` int(10) unsigned NOT NULL default '0',
  `can_manage_messages` tinyint(1) unsigned default '0',
  `can_manage_tasks` tinyint(1) unsigned default '0',
  `can_manage_milestones` tinyint(1) unsigned default '0',
  `can_upload_files` tinyint(1) unsigned default '0',
  `can_manage_files` tinyint(1) unsigned default '0',
  `can_assign_to_owners` tinyint(1) unsigned NOT NULL default '0',
  `can_assign_to_other` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`project_id`,`user_id`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>projects` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) <?php echo $default_collation ?> default NULL,
  `description` text <?php echo $default_collation ?>,
  `show_description_in_overview` tinyint(1) unsigned NOT NULL default '0',
  `completed_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `completed_by_id` int(11) default NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by_id` int(10) unsigned default NULL,
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_by_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `completed_on` (`completed_on`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>searchable_objects` (
  `rel_object_manager` varchar(50) <?php echo $default_collation ?> NOT NULL default '',
  `rel_object_id` int(10) unsigned NOT NULL default '0',
  `column_name` varchar(50) <?php echo $default_collation ?> NOT NULL default '',
  `content` text <?php echo $default_collation ?> NOT NULL,
  `project_id` int(10) unsigned NOT NULL default '0',
  `is_private` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`rel_object_manager`,`rel_object_id`,`column_name`),
  KEY `project_id` (`project_id`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>tags` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `project_id` int(10) unsigned NOT NULL default '0',
  `tag` varchar(30) <?php echo $default_collation ?> NOT NULL default '',
  `rel_object_id` int(10) unsigned NOT NULL default '0',
  `rel_object_manager` varchar(50) <?php echo $default_collation ?> NOT NULL default '',
  `created_on` datetime default NULL,
  `created_by_id` int(10) unsigned NOT NULL default '0',
  `is_private` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `project_id` (`project_id`),
  KEY `tag` (`tag`),
  KEY `object_id` (`rel_object_id`,`rel_object_manager`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>user_im_values` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `im_type_id` tinyint(3) unsigned NOT NULL default '0',
  `value` varchar(50) <?php echo $default_collation ?> NOT NULL default '',
  `is_default` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`user_id`,`im_type_id`),
  KEY `is_default` (`is_default`)
) ENGINE=InnoDB <?php echo $default_charset ?>;

CREATE TABLE `<?php echo $table_prefix ?>users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `company_id` smallint(5) unsigned NOT NULL default '0',
  `username` varchar(50) <?php echo $default_collation ?> NOT NULL default '',
  `email` varchar(100) <?php echo $default_collation ?> default NULL,
  `token` varchar(40) <?php echo $default_collation ?> NOT NULL default '',
  `salt` varchar(13) <?php echo $default_collation ?> NOT NULL default '',
  `twister` varchar(10) <?php echo $default_collation ?> NOT NULL default '',
  `display_name` varchar(50) <?php echo $default_collation ?> default NULL,
  `title` varchar(30) <?php echo $default_collation ?> default NULL,
  `avatar_file` varchar(44) <?php echo $default_collation ?> default NULL,
  `office_number` varchar(20) <?php echo $default_collation ?> default NULL,
  `fax_number` varchar(20) <?php echo $default_collation ?> default NULL,
  `mobile_number` varchar(20) <?php echo $default_collation ?> default NULL,
  `home_number` varchar(20) <?php echo $default_collation ?> default NULL,
  `timezone` float(3,1) NOT NULL default '0.0',
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by_id` int(10) unsigned default NULL,
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_visit` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_activity` datetime NOT NULL default '0000-00-00 00:00:00',
  `is_admin` tinyint(1) unsigned default NULL,
  `auto_assign` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `last_visit` (`last_visit`),
  KEY `company_id` (`company_id`),
  KEY `last_login` (`last_login`)
) ENGINE=InnoDB <?php echo $default_charset ?>;