structure.sql

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

-- Table structure for table `document_incomplete`
--

CREATE TABLE `document_incomplete` (
  `id` int(11) NOT NULL auto_increment,
  `contents` tinyint(1) unsigned NOT NULL default '0',
  `metadata` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_link`
--

CREATE TABLE `document_link` (
  `id` int(11) NOT NULL auto_increment,
  `parent_document_id` int(11) NOT NULL default '0',
  `child_document_id` int(11) NOT NULL default '0',
  `link_type_id` int(11) NOT NULL default '0',
  `external_url` varchar(255) default NULL,
  `external_name` varchar(50) default NULL,
  PRIMARY KEY  (`id`),
  KEY `parent_document_id` (`parent_document_id`),
  KEY `child_document_id` (`child_document_id`),
  KEY `link_type_id` (`link_type_id`),
  CONSTRAINT `document_link_ibfk_1` FOREIGN KEY (`parent_document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_link_ibfk_2` FOREIGN KEY (`child_document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_link_ibfk_3` FOREIGN KEY (`link_type_id`) REFERENCES `document_link_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_link_types`
--

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

--
-- Table structure for table `document_metadata_version`
--

CREATE TABLE `document_metadata_version` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) NOT NULL default '0',
  `content_version_id` int(11) NOT NULL default '0',
  `document_type_id` int(11) NOT NULL default '0',
  `name` mediumtext NOT NULL,
  `description` varchar(255) default NULL,
  `status_id` int(11) default NULL,
  `metadata_version` int(11) NOT NULL default '0',
  `version_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `version_creator_id` int(11) NOT NULL default '0',
  `workflow_id` int(11) default NULL,
  `workflow_state_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `document_type_id` (`document_type_id`),
  KEY `status_id` (`status_id`),
  KEY `document_id` (`document_id`),
  KEY `version_creator_id` (`version_creator_id`),
  KEY `content_version_id` (`content_version_id`),
  KEY `workflow_id` (`workflow_id`),
  KEY `workflow_state_id` (`workflow_state_id`),
  KEY `version_created` (`version_created`),
  CONSTRAINT `document_metadata_version_ibfk_1` FOREIGN KEY (`document_type_id`) REFERENCES `document_types_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_metadata_version_ibfk_2` FOREIGN KEY (`status_id`) REFERENCES `status_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_metadata_version_ibfk_3` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_metadata_version_ibfk_4` FOREIGN KEY (`version_creator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_metadata_version_ibfk_5` FOREIGN KEY (`content_version_id`) REFERENCES `document_content_version` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_metadata_version_ibfk_6` FOREIGN KEY (`workflow_id`) REFERENCES `workflows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_metadata_version_ibfk_7` FOREIGN KEY (`workflow_state_id`) REFERENCES `workflow_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_role_allocations`
--

CREATE TABLE `document_role_allocations` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) NOT NULL default '0',
  `role_id` int(11) NOT NULL default '0',
  `permission_descriptor_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `role_id` (`role_id`),
  KEY `permission_descriptor_id` (`permission_descriptor_id`),
  KEY `document_id_role_id` (`document_id`,`role_id`),
  CONSTRAINT `document_role_allocations_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_role_allocations_ibfk_2` FOREIGN KEY (`permission_descriptor_id`) REFERENCES `permission_descriptors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_searchable_text`
--

CREATE TABLE `document_searchable_text` (
  `document_id` int(11) default NULL,
  `document_text` longtext,
  KEY `document_id` (`document_id`),
  FULLTEXT KEY `document_text` (`document_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_subscriptions`
--

CREATE TABLE `document_subscriptions` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `document_id` int(11) NOT NULL default '0',
  `is_alerted` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `document_id` (`document_id`),
  CONSTRAINT `document_subscriptions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_subscriptions_ibfk_2` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_tags`
--

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

--
-- Table structure for table `document_text`
--

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

--
-- Table structure for table `document_transaction_text`
--

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

--
-- Table structure for table `document_transaction_types_lookup`
--

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

--
-- Table structure for table `document_transactions`
--

CREATE TABLE `document_transactions` (
  `id` int(11) NOT NULL auto_increment,
  `document_id` int(11) default NULL,
  `version` varchar(10),
  `user_id` int(11) default NULL,
  `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip` varchar(15) default NULL,
  `filename` mediumtext NOT NULL,
  `comment` mediumtext NOT NULL,
  `transaction_namespace` varchar(255) NOT NULL default 'ktcore.transactions.event',
  `session_id` int(11) default NULL,
  `admin_mode` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `session_id` (`session_id`),
  KEY `document_id` (`document_id`),
  KEY `user_id` (`user_id`),
  KEY `datetime` (`datetime`,`transaction_namespace`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_type_fields_link`
--

CREATE TABLE `document_type_fields_link` (
  `id` int(11) NOT NULL auto_increment,
  `document_type_id` int(11) NOT NULL default '0',
  `field_id` int(11) NOT NULL default '0',
  `is_mandatory` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `document_type_id` (`document_type_id`),
  KEY `field_id` (`field_id`),
  CONSTRAINT `document_type_fields_link_ibfk_1` FOREIGN KEY (`document_type_id`) REFERENCES `document_types_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_type_fields_link_ibfk_2` FOREIGN KEY (`field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_type_fieldsets_link`
--

CREATE TABLE `document_type_fieldsets_link` (
  `id` int(11) NOT NULL auto_increment,
  `document_type_id` int(11) NOT NULL default '0',
  `fieldset_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `document_type_id` (`document_type_id`),
  KEY `fieldset_id` (`fieldset_id`),
  CONSTRAINT `document_type_fieldsets_link_ibfk_1` FOREIGN KEY (`document_type_id`) REFERENCES `document_types_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `document_type_fieldsets_link_ibfk_2` FOREIGN KEY (`fieldset_id`) REFERENCES `fieldsets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `document_types_lookup`
--

CREATE TABLE `document_types_lookup` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `disabled` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
  `id` int(11) NOT NULL auto_increment,
  `creator_id` int(11) default NULL,
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `folder_id` int(11) default NULL,
  `is_checked_out` tinyint(1) NOT NULL default '0',
  `parent_folder_ids` mediumtext,
  `full_path` mediumtext,
  `checked_out_user_id` int(11) default NULL,
  `status_id` int(11) default NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `permission_object_id` int(11) default NULL,
  `permission_lookup_id` int(11) default NULL,
  `metadata_version` int(11) NOT NULL default '0',
  `modified_user_id` int(11) default NULL,
  `metadata_version_id` int(11) default NULL,
  `owner_id` int(11) default NULL,
  `immutable` tinyint(1) NOT NULL default '0',
  `restore_folder_id` int(11) default NULL,
  `restore_folder_path` text,
  `checkedout` datetime default NULL,
  `oem_no` varchar(255) default NULL,
  `linked_document_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `creator_id` (`creator_id`),
  KEY `folder_id` (`folder_id`),
  KEY `checked_out_user_id` (`checked_out_user_id`),
  KEY `status_id` (`status_id`),
  KEY `permission_object_id` (`permission_object_id`),
  KEY `permission_lookup_id` (`permission_lookup_id`),
  KEY `modified_user_id` (`modified_user_id`),
  KEY `metadata_version_id` (`metadata_version_id`),
  KEY `created` (`created`),
  KEY `modified` (`modified`),
  KEY `full_path` (`full_path`(255)),
  KEY `immutable` (`immutable`),
  KEY `checkedout` (`checkedout`),
  KEY `oem_no` (`oem_no`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`folder_id`) REFERENCES `folders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `documents_ibfk_3` FOREIGN KEY (`checked_out_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `documents_ibfk_4` FOREIGN KEY (`status_id`) REFERENCES `status_lookup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `documents_ibfk_5` FOREIGN KEY (`permission_object_id`) REFERENCES `permission_objects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `documents_ibfk_6` FOREIGN KEY (`permission_lookup_id`) REFERENCES `permission_lookups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `documents_ibfk_7` FOREIGN KEY (`modified_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `documents_ibfk_8` FOREIGN KEY (`metadata_version_id`) REFERENCES `document_metadata_version` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `download_files`
--

CREATE TABLE `download_files` (
  `document_id` int(11) NOT NULL,
  `session` varchar(100) NOT NULL,
  `download_date` timestamp NULL default CURRENT_TIMESTAMP,
  `downloaded` int(10) unsigned NOT NULL default '0',
  `filesize` int(10) unsigned NOT NULL,
  `content_version` int(10) unsigned NOT NULL,
  `hash` varchar(100) NOT NULL,
  PRIMARY KEY  (`document_id`,`session`),
  CONSTRAINT `download_files_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `field_behaviour_options`
--

CREATE TABLE `field_behaviour_options` (
  `behaviour_id` int(11) NOT NULL default '0',
  `field_id` int(11) NOT NULL default '0',
  `instance_id` int(11) NOT NULL default '0',
  KEY `field_id` (`field_id`),
  KEY `instance_id` (`instance_id`),
  KEY `behaviour_id_field_id` (`behaviour_id`,`field_id`),
  CONSTRAINT `field_behaviour_options_ibfk_1` FOREIGN KEY (`behaviour_id`) REFERENCES `field_behaviours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `field_behaviour_options_ibfk_2` FOREIGN KEY (`field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `field_behaviour_options_ibfk_3` FOREIGN KEY (`instance_id`) REFERENCES `field_value_instances` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `field_behaviours`
--

CREATE TABLE `field_behaviours` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `human_name` varchar(100) NOT NULL,
  `field_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `field_id` (`field_id`),
  KEY `name` (`name`),
  CONSTRAINT `field_behaviours_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `field_orders`
--

CREATE TABLE `field_orders` (