From 1e8954318d56c09a136350cd16bacd3b26d32a8d Mon Sep 17 00:00:00 2001 From: Alexander Ebert Date: Tue, 26 Jul 2016 21:52:57 +0200 Subject: [PATCH] Revert "Decreased some column lengths, removed useless unique keys" This reverts commit ec3e4f8f513081456e355e5da81098de7e2db3d7. --- wcfsetup/setup/db/install.sql | 118 +++++++++++++++++++++++----------- 1 file changed, 79 insertions(+), 39 deletions(-) diff --git a/wcfsetup/setup/db/install.sql b/wcfsetup/setup/db/install.sql index cd3ab799df..f567c87481 100644 --- a/wcfsetup/setup/db/install.sql +++ b/wcfsetup/setup/db/install.sql @@ -5,7 +5,8 @@ CREATE TABLE wcf1_acl_option ( packageID INT(10) NOT NULL, objectTypeID INT(10) NOT NULL, optionName VARCHAR(255) NOT NULL, - categoryName VARCHAR(255) NOT NULL + categoryName VARCHAR(255) NOT NULL, + UNIQUE KEY (packageID, objectTypeID, optionName) ); DROP TABLE IF EXISTS wcf1_acl_option_category; @@ -13,7 +14,8 @@ CREATE TABLE wcf1_acl_option_category ( categoryID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, packageID INT(10) NOT NULL, objectTypeID INT(10) NOT NULL, - categoryName VARCHAR(255) NOT NULL + categoryName VARCHAR(255) NOT NULL, + UNIQUE KEY (packageID, objectTypeID, categoryName) ); DROP TABLE IF EXISTS wcf1_acl_option_to_user; @@ -61,7 +63,8 @@ CREATE TABLE wcf1_acp_menu_item ( showOrder INT(10) NOT NULL DEFAULT 0, permissions TEXT, options TEXT, - icon VARCHAR(255) NOT NULL DEFAULT '' + icon VARCHAR(255) NOT NULL DEFAULT '', + UNIQUE KEY menuItem (menuItem, packageID) ); DROP TABLE IF EXISTS wcf1_acp_search_provider; @@ -70,7 +73,8 @@ CREATE TABLE wcf1_acp_search_provider ( packageID INT(10) NOT NULL, providerName VARCHAR(255) NOT NULL DEFAULT '', className VARCHAR(255) NOT NULL DEFAULT '', - showOrder INT(10) NOT NULL DEFAULT 0 + showOrder INT(10) NOT NULL DEFAULT 0, + UNIQUE KEY providerName (providerName, packageID) ); DROP TABLE IF EXISTS wcf1_acp_session; @@ -114,7 +118,7 @@ CREATE TABLE wcf1_acp_session_virtual ( virtualSessionID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, sessionID CHAR(40) NOT NULL, ipAddress VARCHAR(39) NOT NULL DEFAULT '', - userAgent VARCHAR(100) NOT NULL DEFAULT '', + userAgent VARCHAR(255) NOT NULL DEFAULT '', lastActivityTime INT(10) NOT NULL DEFAULT 0, sessionVariables MEDIUMTEXT, UNIQUE KEY (sessionID, ipAddress, userAgent) @@ -125,7 +129,8 @@ CREATE TABLE wcf1_acp_template ( templateID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, packageID INT(10), templateName VARCHAR(255) NOT NULL, - application VARCHAR(20) NOT NULL + application VARCHAR(20) NOT NULL, + UNIQUE KEY applicationTemplate (application, templateName) ); DROP TABLE IF EXISTS wcf1_ad; @@ -236,7 +241,8 @@ CREATE TABLE wcf1_bbcode ( isBlockElement TINYINT(1) NOT NULL DEFAULT 0, isSourceCode TINYINT(1) NOT NULL DEFAULT 0, showButton TINYINT(1) NOT NULL DEFAULT 0, - originIsSystem TINYINT(1) NOT NULL DEFAULT 0 + originIsSystem TINYINT(1) NOT NULL DEFAULT 0, + UNIQUE KEY bbcodeTag (bbcodeTag) ); DROP TABLE IF EXISTS wcf1_bbcode_attribute; @@ -339,7 +345,8 @@ CREATE TABLE wcf1_clipboard_action ( packageID INT(10) NOT NULL DEFAULT 0, actionName VARCHAR(50) NOT NULL DEFAULT '', actionClassName VARCHAR(200) NOT NULL DEFAULT '', - showOrder INT(10) NOT NULL DEFAULT 0 + showOrder INT(10) NOT NULL DEFAULT 0, + UNIQUE KEY actionName (packageID, actionName, actionClassName) ); DROP TABLE IF EXISTS wcf1_clipboard_item; @@ -398,7 +405,8 @@ DROP TABLE IF EXISTS wcf1_core_object; CREATE TABLE wcf1_core_object ( objectID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, packageID INT(10) NOT NULL, - objectName VARCHAR(255) NOT NULL DEFAULT '' + objectName VARCHAR(255) NOT NULL DEFAULT '', + UNIQUE KEY object (packageID, objectName) ); DROP TABLE IF EXISTS wcf1_cronjob; @@ -421,7 +429,9 @@ CREATE TABLE wcf1_cronjob ( canBeDisabled TINYINT(1) NOT NULL DEFAULT 1, state TINYINT(1) NOT NULL DEFAULT 0, failCount TINYINT(1) NOT NULL DEFAULT 0, - options TEXT + options TEXT, + + UNIQUE KEY cronjobName (cronjobName, packageID) ); DROP TABLE IF EXISTS wcf1_cronjob_log; @@ -462,7 +472,9 @@ CREATE TABLE wcf1_event_listener ( inherit TINYINT(1) NOT NULL DEFAULT 0, niceValue TINYINT(3) NOT NULL DEFAULT 0, permissions TEXT, - options TEXT + options TEXT, + + UNIQUE KEY listenerName (listenerName, packageID) ); DROP TABLE IF EXISTS wcf1_import_mapping; @@ -524,14 +536,15 @@ CREATE TABLE wcf1_language ( DROP TABLE IF EXISTS wcf1_language_category; CREATE TABLE wcf1_language_category ( languageCategoryID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, - languageCategory VARCHAR(255) NOT NULL DEFAULT '' + languageCategory VARCHAR(255) NOT NULL DEFAULT '', + UNIQUE KEY languageCategory (languageCategory) ); DROP TABLE IF EXISTS wcf1_language_item; CREATE TABLE wcf1_language_item ( languageItemID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, languageID INT(10) NOT NULL, - languageItem VARCHAR(150) NOT NULL DEFAULT '', + languageItem VARCHAR(255) NOT NULL DEFAULT '', languageItemValue MEDIUMTEXT NOT NULL, languageCustomItemValue MEDIUMTEXT, languageUseCustomValue TINYINT(1) NOT NULL DEFAULT 0, @@ -717,7 +730,7 @@ CREATE TABLE wcf1_object_type ( objectTypeID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, definitionID INT(10) NOT NULL, packageID INT(10) NOT NULL, - objectType VARCHAR(150) NOT NULL, + objectType VARCHAR(255) NOT NULL, className VARCHAR(255) NOT NULL DEFAULT '', additionalData MEDIUMTEXT, UNIQUE KEY objectType (objectType, definitionID, packageID) @@ -729,7 +742,8 @@ CREATE TABLE wcf1_object_type_definition ( definitionName VARCHAR(255) NOT NULL, packageID INT(10) NOT NULL, interfaceName VARCHAR(255) NOT NULL DEFAULT '', - categoryName VARCHAR(80) NOT NULL DEFAULT '' + categoryName VARCHAR(80) NOT NULL DEFAULT '', + UNIQUE KEY definitionName (definitionName) ); DROP TABLE IF EXISTS wcf1_option; @@ -749,7 +763,9 @@ CREATE TABLE wcf1_option ( options TEXT, supportI18n TINYINT(1) NOT NULL DEFAULT 0, requireI18n TINYINT(1) NOT NULL DEFAULT 0, - additionalData MEDIUMTEXT + additionalData MEDIUMTEXT, + + UNIQUE KEY optionName (optionName) ); DROP TABLE IF EXISTS wcf1_option_category; @@ -760,7 +776,8 @@ CREATE TABLE wcf1_option_category ( parentCategoryName VARCHAR(255) NOT NULL DEFAULT '', showOrder INT(10) NOT NULL DEFAULT 0, permissions TEXT, - options TEXT + options TEXT, + UNIQUE KEY categoryName (categoryName) ); DROP TABLE IF EXISTS wcf1_package; @@ -785,21 +802,24 @@ DROP TABLE IF EXISTS wcf1_package_exclusion; CREATE TABLE wcf1_package_exclusion ( packageID INT(10) NOT NULL, excludedPackage VARCHAR(255) NOT NULL DEFAULT '', - excludedPackageVersion VARCHAR(255) NOT NULL DEFAULT '' + excludedPackageVersion VARCHAR(255) NOT NULL DEFAULT '', + UNIQUE KEY packageID (packageID, excludedPackage) ); DROP TABLE IF EXISTS wcf1_package_installation_file_log; CREATE TABLE wcf1_package_installation_file_log ( packageID INT(10), filename VARCHAR(255) NOT NULL, - application VARCHAR(20) NOT NULL + application VARCHAR(20) NOT NULL, + UNIQUE KEY applicationFile (application, filename) ); DROP TABLE IF EXISTS wcf1_package_installation_form; CREATE TABLE wcf1_package_installation_form ( queueID INT(10) NOT NULL, formName VARCHAR(80) NOT NULL DEFAULT '', - document TEXT NOT NULL + document TEXT NOT NULL, + UNIQUE KEY formDocument (queueID, formName) ); DROP TABLE IF EXISTS wcf1_package_installation_node; @@ -842,7 +862,8 @@ CREATE TABLE wcf1_package_installation_sql_log ( packageID INT(10), sqlTable VARCHAR(100) NOT NULL DEFAULT '', sqlColumn VARCHAR(100) NOT NULL DEFAULT '', - sqlIndex VARCHAR(100) NOT NULL DEFAULT '' + sqlIndex VARCHAR(100) NOT NULL DEFAULT '', + UNIQUE KEY packageID (packageID, sqlTable, sqlColumn, sqlIndex) ); /* SQL_PARSER_OFFSET */ @@ -850,7 +871,8 @@ CREATE TABLE wcf1_package_installation_sql_log ( DROP TABLE IF EXISTS wcf1_package_requirement; CREATE TABLE wcf1_package_requirement ( packageID INT(10) NOT NULL, - requirement INT(10) NOT NULL + requirement INT(10) NOT NULL, + UNIQUE KEY packageID (packageID, requirement) ); DROP TABLE IF EXISTS wcf1_package_update; @@ -862,20 +884,23 @@ CREATE TABLE wcf1_package_update ( packageDescription VARCHAR(255) NOT NULL DEFAULT '', author VARCHAR(255) NOT NULL DEFAULT '', authorURL VARCHAR(255) NOT NULL DEFAULT '', - isApplication TINYINT(1) NOT NULL DEFAULT 0 + isApplication TINYINT(1) NOT NULL DEFAULT 0, + UNIQUE KEY packageUpdateServerID (packageUpdateServerID, package) ); DROP TABLE IF EXISTS wcf1_package_update_exclusion; CREATE TABLE wcf1_package_update_exclusion ( packageUpdateVersionID INT(10) NOT NULL, excludedPackage VARCHAR(255) NOT NULL DEFAULT '', - excludedPackageVersion VARCHAR(255) NOT NULL DEFAULT '' + excludedPackageVersion VARCHAR(255) NOT NULL DEFAULT '', + UNIQUE KEY packageUpdateVersionID (packageUpdateVersionID, excludedPackage) ); DROP TABLE IF EXISTS wcf1_package_update_fromversion; CREATE TABLE wcf1_package_update_fromversion ( packageUpdateVersionID INT(10) NOT NULL DEFAULT 0, - fromversion VARCHAR(50) NOT NULL DEFAULT '' + fromversion VARCHAR(50) NOT NULL DEFAULT '', + UNIQUE KEY packageUpdateVersionID (packageUpdateVersionID, fromversion) ); DROP TABLE IF EXISTS wcf1_package_update_optional; @@ -888,7 +913,8 @@ DROP TABLE IF EXISTS wcf1_package_update_requirement; CREATE TABLE wcf1_package_update_requirement ( packageUpdateVersionID INT(10) NOT NULL, package VARCHAR(255) NOT NULL DEFAULT '', - minversion VARCHAR(50) NOT NULL DEFAULT '' + minversion VARCHAR(50) NOT NULL DEFAULT '', + UNIQUE KEY packageUpdateVersionID (packageUpdateVersionID, package) ); DROP TABLE IF EXISTS wcf1_package_update_server; @@ -915,7 +941,8 @@ CREATE TABLE wcf1_package_update_version ( license VARCHAR(255) NOT NULL DEFAULT '', licenseURL VARCHAR(255) NOT NULL DEFAULT '', isAccessible TINYINT(1) NOT NULL DEFAULT 1, - isCritical TINYINT(1) NOT NULL DEFAULT 0 + isCritical TINYINT(1) NOT NULL DEFAULT 0, + UNIQUE KEY packageUpdateID (packageUpdateID, packageVersion) ); DROP TABLE IF EXISTS wcf1_page; @@ -1050,6 +1077,7 @@ CREATE TABLE wcf1_search_keyword ( keyword VARCHAR(255) NOT NULL, searches INT(10) NOT NULL DEFAULT 0, lastSearchTime INT(10) NOT NULL DEFAULT 0, + UNIQUE KEY (keyword), KEY (searches, lastSearchTime) ); @@ -1078,7 +1106,7 @@ CREATE TABLE wcf1_session_virtual ( virtualSessionID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, sessionID CHAR(40) NOT NULL, ipAddress VARCHAR(39) NOT NULL DEFAULT '', - userAgent VARCHAR(100) NOT NULL DEFAULT '', + userAgent VARCHAR(255) NOT NULL DEFAULT '', lastActivityTime INT(10) NOT NULL DEFAULT 0, sessionVariables MEDIUMTEXT, UNIQUE KEY (sessionID, ipAddress, userAgent) @@ -1093,13 +1121,14 @@ CREATE TABLE wcf1_smiley ( smileyTitle VARCHAR(255) NOT NULL DEFAULT '', smileyCode VARCHAR(255) NOT NULL DEFAULT '', aliases TEXT NOT NULL, - showOrder INT(10) NOT NULL DEFAULT 0 + showOrder INT(10) NOT NULL DEFAULT 0, + UNIQUE KEY smileyCode (smileyCode) ); DROP TABLE IF EXISTS wcf1_spider; CREATE TABLE wcf1_spider ( spiderID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, - spiderIdentifier VARCHAR(150) DEFAULT '', + spiderIdentifier VARCHAR(255) DEFAULT '', spiderName VARCHAR(255) DEFAULT '', spiderURL VARCHAR(255) DEFAULT '', UNIQUE KEY spiderIdentifier (spiderIdentifier) @@ -1141,7 +1170,8 @@ DROP TABLE IF EXISTS wcf1_style_variable; CREATE TABLE wcf1_style_variable ( variableID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, variableName VARCHAR(50) NOT NULL, - defaultValue MEDIUMTEXT + defaultValue MEDIUMTEXT, + UNIQUE KEY variableName (variableName) ); DROP TABLE IF EXISTS wcf1_style_variable_value; @@ -1157,7 +1187,8 @@ CREATE TABLE wcf1_tag ( tagID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, languageID INT(10) NOT NULL DEFAULT 0, name VARCHAR(255) NOT NULL, - synonymFor INT(10) + synonymFor INT(10), + UNIQUE KEY (languageID, name) ); DROP TABLE IF EXISTS wcf1_tag_to_object; @@ -1179,6 +1210,7 @@ CREATE TABLE wcf1_template ( application VARCHAR(20) NOT NULL, templateGroupID INT(10), lastModificationTime INT(10) NOT NULL DEFAULT 0, + UNIQUE KEY applicationTemplate (application, templateGroupID, templateName), KEY templateGroupID (packageID, templateGroupID, templateName) ); @@ -1377,7 +1409,8 @@ CREATE TABLE wcf1_user_group_option ( permissions TEXT, options TEXT, usersOnly TINYINT(1) NOT NULL DEFAULT 0, - additionalData MEDIUMTEXT + additionalData MEDIUMTEXT, + UNIQUE KEY optionName (optionName, packageID) ); DROP TABLE IF EXISTS wcf1_user_group_option_category; @@ -1388,7 +1421,8 @@ CREATE TABLE wcf1_user_group_option_category ( parentCategoryName VARCHAR(255) NOT NULL DEFAULT '', showOrder INT(10) NOT NULL DEFAULT 0, permissions TEXT, - options TEXT + options TEXT, + UNIQUE KEY categoryName (categoryName) ); DROP TABLE IF EXISTS wcf1_user_group_option_value; @@ -1420,7 +1454,8 @@ CREATE TABLE wcf1_user_menu_item ( permissions TEXT, options TEXT, className VARCHAR(255) NOT NULL DEFAULT '', - iconClassName VARCHAR(255) NOT NULL DEFAULT '' + iconClassName VARCHAR(255) NOT NULL DEFAULT '', + UNIQUE KEY menuItem (menuItem, packageID) ); -- notifications @@ -1473,7 +1508,8 @@ CREATE TABLE wcf1_user_notification_event ( permissions TEXT, options TEXT, preset TINYINT(1) NOT NULL DEFAULT 0, - presetMailNotificationType ENUM('none', 'instant', 'daily') NOT NULL DEFAULT 'none' + presetMailNotificationType ENUM('none', 'instant', 'daily') NOT NULL DEFAULT 'none', + UNIQUE KEY eventName (eventName, objectTypeID) ); -- user configuration for events @@ -1520,6 +1556,7 @@ CREATE TABLE wcf1_user_option ( options TEXT, additionalData MEDIUMTEXT, originIsSystem TINYINT(1) NOT NULL DEFAULT 0, + UNIQUE KEY optionName (optionName, packageID), KEY categoryName (categoryName) ); @@ -1531,7 +1568,8 @@ CREATE TABLE wcf1_user_option_category ( parentCategoryName VARCHAR(255) NOT NULL DEFAULT '', showOrder INT(10) NOT NULL DEFAULT 0, permissions TEXT, - options TEXT + options TEXT, + UNIQUE KEY categoryName (categoryName) ); DROP TABLE IF EXISTS wcf1_user_option_value; @@ -1547,7 +1585,8 @@ CREATE TABLE wcf1_user_profile_menu_item ( showOrder INT(10) NOT NULL DEFAULT 0, permissions TEXT NULL, options TEXT NULL, - className VARCHAR(255) NOT NULL + className VARCHAR(255) NOT NULL, + UNIQUE KEY (packageID, menuItem) ); DROP TABLE IF EXISTS wcf1_user_profile_visitor; @@ -1576,7 +1615,8 @@ DROP TABLE IF EXISTS wcf1_user_storage; CREATE TABLE wcf1_user_storage ( userID INT(10) NOT NULL, field VARCHAR(80) NOT NULL DEFAULT '', - fieldValue MEDIUMTEXT + fieldValue MEDIUMTEXT, + UNIQUE KEY userStorageData (userID, field) ); DROP TABLE IF EXISTS wcf1_user_to_group; -- 2.20.1