structure.sql

PHP 知识管理系统（基于树结构的知识管理系统）, 英文原版的PHP源码。

--
-- Table structure for table `scheduler_tasks`
--

CREATE TABLE `scheduler_tasks` (
  `id` int(11) NOT NULL auto_increment,
  `task` varchar(50) NOT NULL,
  `script_url` varchar(255) NOT NULL,
  `script_params` varchar(255) default NULL,
  `is_complete` tinyint(1) NOT NULL default '0',
  `frequency` varchar(25) default NULL,
  `run_time` datetime default NULL,
  `previous_run_time` datetime default NULL,
  `run_duration` float default NULL,
  `status` enum('enabled','disabled','system') NOT NULL default 'disabled',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `task` (`task`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `search_document_user_link`
--

CREATE TABLE `search_document_user_link` (
  `document_id` int(11) default NULL,
  `user_id` int(11) default NULL,
  KEY `document_id` (`document_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `search_document_user_link_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `search_document_user_link_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `search_ranking`
--

CREATE TABLE `search_ranking` (
  `groupname` varchar(100) NOT NULL,
  `itemname` varchar(100) NOT NULL,
  `ranking` float default '0',
  `type` enum('T','M','S') default 'T' COMMENT 'T=Table, M=Metadata, S=Searchable',
  PRIMARY KEY  (`groupname`,`itemname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `search_saved`
--

CREATE TABLE `search_saved` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `expression` mediumtext NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('S','C','W','B') NOT NULL default 'S' COMMENT 'S=saved search, C=permission, w=workflow, B=subscription',
  `shared` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `search_saved_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `search_saved_events`
--

CREATE TABLE `search_saved_events` (
  `document_id` int(11) NOT NULL,
  PRIMARY KEY  (`document_id`),
  CONSTRAINT `search_saved_events_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `status_lookup`
--

CREATE TABLE `status_lookup` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `tag_words`
--

CREATE TABLE `tag_words` (
  `id` int(11) NOT NULL auto_increment,
  `tag` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `time_period`
--

CREATE TABLE `time_period` (
  `id` int(11) NOT NULL auto_increment,
  `time_unit_id` int(11) default NULL,
  `units` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `time_unit_id` (`time_unit_id`),
  CONSTRAINT `time_period_ibfk_1` FOREIGN KEY (`time_unit_id`) REFERENCES `time_unit_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `time_unit_lookup`
--

CREATE TABLE `time_unit_lookup` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `trigger_selection`
--

CREATE TABLE `trigger_selection` (
  `event_ns` varchar(255) NOT NULL default '',
  `selection_ns` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`event_ns`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `type_workflow_map`
--

CREATE TABLE `type_workflow_map` (
  `document_type_id` int(11) NOT NULL default '0',
  `workflow_id` int(11) default NULL,
  PRIMARY KEY  (`document_type_id`),
  KEY `workflow_id` (`workflow_id`),
  CONSTRAINT `type_workflow_map_ibfk_1` FOREIGN KEY (`document_type_id`) REFERENCES `document_types_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `type_workflow_map_ibfk_2` FOREIGN KEY (`workflow_id`) REFERENCES `workflows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `units_lookup`
--

CREATE TABLE `units_lookup` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `folder_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `folder_id` (`folder_id`),
  CONSTRAINT `units_lookup_ibfk_1` FOREIGN KEY (`folder_id`) REFERENCES `folders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `units_organisations_link`
--

CREATE TABLE `units_organisations_link` (
  `id` int(11) NOT NULL auto_increment,
  `unit_id` int(11) NOT NULL default '0',
  `organisation_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `unit_id` (`unit_id`),
  KEY `organisation_id` (`organisation_id`),
  CONSTRAINT `units_organisations_link_ibfk_1` FOREIGN KEY (`unit_id`) REFERENCES `units_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `units_organisations_link_ibfk_2` FOREIGN KEY (`organisation_id`) REFERENCES `organisations_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `upgrades`
--

CREATE TABLE `upgrades` (
  `id` int(11) NOT NULL auto_increment,
  `descriptor` varchar(100) NOT NULL,
  `description` varchar(255) NOT NULL,
  `date_performed` datetime NOT NULL default '0000-00-00 00:00:00',
  `result` tinyint(1) NOT NULL default '0',
  `parent` varchar(40) default NULL,
  PRIMARY KEY  (`id`),
  KEY `descriptor` (`descriptor`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `uploaded_files`
--

CREATE TABLE `uploaded_files` (
  `tempfilename` varchar(100) NOT NULL,
  `filename` varchar(100) NOT NULL,
  `userid` int(11) NOT NULL,
  `uploaddate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `action` char(1) NOT NULL COMMENT 'A = Add, C = Checkin',
  `document_id` int(11) default NULL,
  PRIMARY KEY  (`tempfilename`),
  KEY `userid` (`userid`),
  KEY `document_id` (`document_id`),
  CONSTRAINT `uploaded_files_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `uploaded_files_ibfk_2` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `user_history`
--

CREATE TABLE `user_history` (
  `id` int(11) NOT NULL auto_increment,
  `datetime` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `action_namespace` varchar(255) NOT NULL,
  `comments` mediumtext,
  `session_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `action_namespace` (`action_namespace`),
  KEY `datetime` (`datetime`),
  KEY `session_id` (`session_id`),
  CONSTRAINT `user_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(255) NOT NULL default '',
  `name` varchar(255) NOT NULL default '',
  `password` varchar(255) NOT NULL default '',
  `quota_max` int(11) NOT NULL default '0',
  `quota_current` int(11) NOT NULL default '0',
  `email` varchar(255) default NULL,
  `mobile` varchar(255) default NULL,
  `email_notification` tinyint(1) NOT NULL default '0',
  `sms_notification` tinyint(1) NOT NULL default '0',
  `authentication_details_s1` varchar(255) default NULL,
  `max_sessions` int(11) default NULL,
  `language_id` int(11) default NULL,
  `authentication_details_s2` varchar(255) default NULL,
  `authentication_source_id` int(11) default NULL,
  `authentication_details_b1` tinyint(1) default NULL,
  `authentication_details_i2` int(11) default NULL,
  `authentication_details_d1` datetime default NULL,
  `authentication_details_i1` int(11) default NULL,
  `authentication_details_d2` datetime default NULL,
  `authentication_details_b2` tinyint(1) default NULL,
  `last_login` datetime default NULL,
  `disabled` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `authentication_source_id` (`authentication_source_id`),
  KEY `last_login` (`last_login`),
  KEY `disabled` (`disabled`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`authentication_source_id`) REFERENCES `authentication_sources` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `users_groups_link`
--

CREATE TABLE `users_groups_link` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `users_groups_link_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `users_groups_link_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `workflow_actions`
--

CREATE TABLE `workflow_actions` (
  `workflow_id` int(11) NOT NULL default '0',
  `action_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`workflow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `workflow_documents`
--

CREATE TABLE `workflow_documents` (
  `document_id` int(11) NOT NULL default '0',
  `workflow_id` int(11) NOT NULL default '0',
  `state_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`document_id`),
  KEY `workflow_id` (`workflow_id`),
  KEY `state_id` (`state_id`),
  CONSTRAINT `workflow_documents_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `workflow_documents_ibfk_2` FOREIGN KEY (`workflow_id`) REFERENCES `workflows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `workflow_documents_ibfk_3` FOREIGN KEY (`state_id`) REFERENCES `workflow_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `workflow_state_actions`
--

CREATE TABLE `workflow_state_actions` (
  `state_id` int(11) NOT NULL default '0',
  `action_name` varchar(255) NOT NULL,
  KEY `state_id` (`state_id`),
  CONSTRAINT `workflow_state_actions_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `workflow_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `workflow_state_disabled_actions`
--

CREATE TABLE `workflow_state_disabled_actions` (
  `state_id` int(11) NOT NULL default '0',
  `action_name` varchar(255) NOT NULL,
  KEY `state_id` (`state_id`),
  CONSTRAINT `workflow_state_disabled_actions_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `workflow_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `workflow_state_permission_assignments`
--

CREATE TABLE `workflow_state_permission_assignments` (
  `id` in