structure.sql

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

--
-- $Id: structure.sql 9433 2008-10-01 10:10:51Z kevin_fourie $
--
-- KnowledgeTree Community Edition
-- Document Management Made Simple
-- Copyright (C) 2008 KnowledgeTree Inc.
-- Portions copyright The Jam Warehouse Software (Pty) Limited
--
-- This program is free software; you can redistribute it and/or modify it under
-- the terms of the GNU General Public License version 3 as published by the
-- Free Software Foundation.
--
-- This program is distributed in the hope that it will be useful, but WITHOUT
-- ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
-- details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
--
-- You can contact KnowledgeTree Inc., PO Box 7775 #87847, San Francisco,
-- California 94120-7775, or email [e-mail].
--
-- The interactive user interfaces in modified source and object code versions
-- of this program must display Appropriate Legal Notices, as required under
-- Section 5 of the GNU General Public License version 3.
--
-- In accordance with Section 7(b) of the GNU General Public License version 3,
-- these Appropriate Legal Notices must retain the display of the "Powered by
-- KnowledgeTree" logo and retain the original copyright notice. If the display of the
-- logo is not reasonably feasible for technical reasons, the Appropriate Legal Notices
-- must display the words "Powered by KnowledgeTree" and retain the original
-- copyright notice.
-- Contributor( s): ______________________________________
--
-- MySQL dump 10.11
--
-- Host: localhost    Database: ktdms
-- ------------------------------------------------------
-- Server version	5.0.41-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `active_sessions`
--

CREATE TABLE `active_sessions` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `session_id` varchar(32) default NULL,
  `lastused` datetime default NULL,
  `ip` varchar(15) default NULL,
  `apptype` varchar(15) default 'webapp' NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `session_id` (`session_id`),
  CONSTRAINT `active_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `archive_restoration_request`
--

CREATE TABLE `archive_restoration_request` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) NOT NULL default '0',
  `request_user_id` int(11) NOT NULL default '0',
  `admin_user_id` int(11) NOT NULL default '0',
  `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `document_id` (`document_id`),
  KEY `request_user_id` (`request_user_id`),
  KEY `admin_user_id` (`admin_user_id`),
  CONSTRAINT `archive_restoration_request_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `archive_restoration_request_ibfk_2` FOREIGN KEY (`request_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `archive_restoration_request_ibfk_3` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `archiving_settings`
--

CREATE TABLE `archiving_settings` (
  `id` int(11) NOT NULL auto_increment,
  `archiving_type_id` int(11) NOT NULL default '0',
  `expiration_date` date default NULL,
  `document_transaction_id` int(11) default NULL,
  `time_period_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `archiving_type_id` (`archiving_type_id`),
  KEY `time_period_id` (`time_period_id`),
  CONSTRAINT `archiving_settings_ibfk_1` FOREIGN KEY (`archiving_type_id`) REFERENCES `archiving_type_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `archiving_settings_ibfk_2` FOREIGN KEY (`time_period_id`) REFERENCES `time_period` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `archiving_type_lookup`
--

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

--
-- Table structure for table `authentication_sources`
--

CREATE TABLE `authentication_sources` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `namespace` varchar(255) NOT NULL default '',
  `authentication_provider` varchar(255) NOT NULL default '',
  `config` mediumtext NOT NULL,
  `is_user_source` tinyint(1) NOT NULL default '0',
  `is_group_source` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `namespace` (`namespace`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `column_entries`
--

CREATE TABLE `column_entries` (
  `id` int(11) NOT NULL auto_increment,
  `column_namespace` varchar(255) NOT NULL default '',
  `view_namespace` varchar(255) NOT NULL default '',
  `config_array` text NOT NULL,
  `position` int(11) NOT NULL default '0',
  `required` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `view_namespace` (`view_namespace`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `comment_searchable_text`
--

CREATE TABLE `comment_searchable_text` (
  `comment_id` int(11) NOT NULL default '0',
  `body` mediumtext,
  `document_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`comment_id`),
  KEY `document_id` (`document_id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `config_groups`
--

CREATE TABLE `config_groups` (
  `id` int(255) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) default NULL,
  `description` mediumtext,
  `category` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `config_settings`
--

CREATE TABLE `config_settings` (
  `id` int(11) NOT NULL auto_increment,
  `group_name` varchar(255) NOT NULL,
  `display_name` varchar(255) default NULL,
  `description` mediumtext,
  `item` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL default 'default',
  `default_value` varchar(255) NOT NULL,
  `type` enum('boolean','string','numeric_string','numeric','radio','dropdown') default 'string',
  `options` mediumtext,
  `can_edit` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `dashlet_disables`
--

CREATE TABLE `dashlet_disables` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `dashlet_namespace` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `dashlet_namespace` (`dashlet_namespace`),
  CONSTRAINT `dashlet_disables_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `data_types`
--

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

--
-- Table structure for table `discussion_comments`
--

CREATE TABLE `discussion_comments` (
  `id` int(11) NOT NULL auto_increment,
  `thread_id` int(11) NOT NULL default '0',
  `in_reply_to` int(11) default NULL,
  `user_id` int(11) NOT NULL default '0',
  `subject` mediumtext,
  `body` mediumtext,
  `date` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `thread_id` (`thread_id`),
  KEY `user_id` (`user_id`),
  KEY `in_reply_to` (`in_reply_to`),
  CONSTRAINT `discussion_comments_ibfk_1` FOREIGN KEY (`thread_id`) REFERENCES `discussion_threads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `discussion_comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `discussion_comments_ibfk_3` FOREIGN KEY (`in_reply_to`) REFERENCES `discussion_comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `discussion_threads`
--

CREATE TABLE `discussion_threads` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) NOT NULL,
  `first_comment_id` int(11) default NULL,
  `last_comment_id` int(11) default NULL,
  `views` int(11) NOT NULL default '0',
  `replies` int(11) NOT NULL default '0',
  `creator_id` int(11) NOT NULL,
  `close_reason` mediumtext NOT NULL,
  `close_metadata_version` int(11) NOT NULL default '0',
  `state` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `document_id` (`document_id`),
  KEY `first_comment_id` (`first_comment_id`),
  KEY `last_comment_id` (`last_comment_id`),
  KEY `creator_id` (`creator_id`),
  CONSTRAINT `discussion_threads_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `discussion_threads_ibfk_2` FOREIGN KEY (`first_comment_id`) REFERENCES `discussion_comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `discussion_threads_ibfk_3` FOREIGN KEY (`last_comment_id`) REFERENCES `discussion_comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `discussion_threads_ibfk_4` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_archiving_link`
--

CREATE TABLE `document_archiving_link` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) NOT NULL default '0',
  `archiving_settings_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `document_id` (`document_id`),
  KEY `archiving_settings_id` (`archiving_settings_id`),
  CONSTRAINT `document_archiving_link_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_archiving_link_ibfk_2` FOREIGN KEY (`archiving_settings_id`) REFERENCES `archiving_settings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_content_version`
--

CREATE TABLE `document_content_version` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) NOT NULL default '0',
  `filename` mediumtext NOT NULL,
  `size` bigint(20) NOT NULL default '0',
  `mime_id` int(11) default '9',
  `major_version` int(11) NOT NULL default '0',
  `minor_version` int(11) NOT NULL default '0',
  `storage_path` varchar(1024) default NULL,
  `md5hash` char(32) default NULL,
  PRIMARY KEY  (`id`),
  KEY `document_id` (`document_id`),
  KEY `mime_id` (`mime_id`),
  KEY `storage_path` (`storage_path`(255)),
  KEY `filename` (`filename`(255)),
  KEY `size` (`size`),
  CONSTRAINT `document_content_version_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_content_version_ibfk_2` FOREIGN KEY (`mime_id`) REFERENCES `mime_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_fields`
--

CREATE TABLE `document_fields` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `data_type` varchar(100) NOT NULL default '',
  `is_generic` tinyint(1) default NULL,
  `has_lookup` tinyint(1) default NULL,
  `has_lookuptree` tinyint(1) default NULL,
  `parent_fieldset` int(11) default NULL,
  `is_mandatory` tinyint(1) NOT NULL default '0',
  `description` mediumtext NOT NULL,
  `position` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `parent_fieldset` (`parent_fieldset`),
  CONSTRAINT `document_fields_ibfk_1` FOREIGN KEY (`parent_fieldset`) REFERENCES `fieldsets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_fields_link`
--

CREATE TABLE `document_fields_link` (
  `id` int(11) NOT NULL auto_increment,
  `document_field_id` int(11) NOT NULL default '0',
  `value` mediumtext NOT NULL,
  `metadata_version_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `document_field_id` (`document_field_id`),
  KEY `metadata_version_id` (`metadata_version_id`),
  CONSTRAINT `document_fields_link_ibfk_1` FOREIGN KEY (`document_field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_fields_link_ibfk_2` FOREIGN KEY (`metadata_version_id`) REFERENCES `document_metadata_version` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--