diff options
| -rw-r--r-- | sql/mysql.sql | 142 |
1 files changed, 71 insertions, 71 deletions
diff --git a/sql/mysql.sql b/sql/mysql.sql index eaa107788..e546fc886 100644 --- a/sql/mysql.sql +++ b/sql/mysql.sql @@ -17,10 +17,10 @@ -- CREATE TABLE users ( - username varchar(250) PRIMARY KEY, + username varchar(191) PRIMARY KEY, password text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- To support SCRAM auth: -- ALTER TABLE users ADD COLUMN serverkey text NOT NULL DEFAULT ''; @@ -28,15 +28,15 @@ CREATE TABLE users ( -- ALTER TABLE users ADD COLUMN iterationcount integer NOT NULL DEFAULT 0; CREATE TABLE last ( - username varchar(250) PRIMARY KEY, + username varchar(191) PRIMARY KEY, seconds text NOT NULL, state text NOT NULl -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE rosterusers ( - username varchar(250) NOT NULL, - jid varchar(250) NOT NULL, + username varchar(191) NOT NULL, + jid varchar(191) NOT NULL, nick text NOT NULL, subscription character(1) NOT NULL, ask character(1) NOT NULL, @@ -45,58 +45,58 @@ CREATE TABLE rosterusers ( subscribe text NOT NULL, type text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username(75), jid(75)); CREATE INDEX i_rosteru_username ON rosterusers(username); CREATE INDEX i_rosteru_jid ON rosterusers(jid); CREATE TABLE rostergroups ( - username varchar(250) NOT NULL, - jid varchar(250) NOT NULL, + username varchar(191) NOT NULL, + jid varchar(191) NOT NULL, grp text NOT NULL -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX pk_rosterg_user_jid ON rostergroups(username(75), jid(75)); CREATE TABLE sr_group ( - name varchar(250) NOT NULL, + name varchar(191) NOT NULL, opts text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE sr_user ( - jid varchar(250) NOT NULL, - grp varchar(250) NOT NULL, + jid varchar(191) NOT NULL, + grp varchar(191) NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_sr_user_jid_group ON sr_user(jid(75), grp(75)); CREATE INDEX i_sr_user_jid ON sr_user(jid); CREATE INDEX i_sr_user_grp ON sr_user(grp); CREATE TABLE spool ( - username varchar(250) NOT NULL, + username varchar(191) NOT NULL, xml BLOB NOT NULL, seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_despool USING BTREE ON spool(username); CREATE INDEX i_spool_created_at USING BTREE ON spool(created_at); CREATE TABLE archive ( - username varchar(250) NOT NULL, + username varchar(191) NOT NULL, timestamp BIGINT UNSIGNED NOT NULL, - peer varchar(250) NOT NULL, - bare_peer varchar(250) NOT NULL, + peer varchar(191) NOT NULL, + bare_peer varchar(191) NOT NULL, xml text NOT NULL, txt text, id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, kind varchar(10), - nick varchar(250), + nick varchar(191), created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE FULLTEXT INDEX i_text ON archive(txt); CREATE INDEX i_username USING BTREE ON archive(username); @@ -105,51 +105,51 @@ CREATE INDEX i_peer USING BTREE ON archive(peer); CREATE INDEX i_bare_peer USING BTREE ON archive(bare_peer); CREATE TABLE archive_prefs ( - username varchar(250) NOT NULL PRIMARY KEY, + username varchar(191) NOT NULL PRIMARY KEY, def text NOT NULL, always text NOT NULL, never text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE vcard ( - username varchar(250) PRIMARY KEY, + username varchar(191) PRIMARY KEY, vcard mediumtext NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE vcard_xupdate ( - username varchar(250) PRIMARY KEY, + username varchar(191) PRIMARY KEY, hash text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE vcard_search ( - username varchar(250) NOT NULL, - lusername varchar(250) PRIMARY KEY, + username varchar(191) NOT NULL, + lusername varchar(191) PRIMARY KEY, fn text NOT NULL, - lfn varchar(250) NOT NULL, + lfn varchar(191) NOT NULL, family text NOT NULL, - lfamily varchar(250) NOT NULL, + lfamily varchar(191) NOT NULL, given text NOT NULL, - lgiven varchar(250) NOT NULL, + lgiven varchar(191) NOT NULL, middle text NOT NULL, - lmiddle varchar(250) NOT NULL, + lmiddle varchar(191) NOT NULL, nickname text NOT NULL, - lnickname varchar(250) NOT NULL, + lnickname varchar(191) NOT NULL, bday text NOT NULL, - lbday varchar(250) NOT NULL, + lbday varchar(191) NOT NULL, ctry text NOT NULL, - lctry varchar(250) NOT NULL, + lctry varchar(191) NOT NULL, locality text NOT NULL, - llocality varchar(250) NOT NULL, + llocality varchar(191) NOT NULL, email text NOT NULL, - lemail varchar(250) NOT NULL, + lemail varchar(191) NOT NULL, orgname text NOT NULL, - lorgname varchar(250) NOT NULL, + lorgname varchar(191) NOT NULL, orgunit text NOT NULL, - lorgunit varchar(250) NOT NULL -) ENGINE=InnoDB CHARACTER SET utf8; + lorgunit varchar(191) NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn); CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily); @@ -164,16 +164,16 @@ CREATE INDEX i_vcard_search_lorgname ON vcard_search(lorgname); CREATE INDEX i_vcard_search_lorgunit ON vcard_search(lorgunit); CREATE TABLE privacy_default_list ( - username varchar(250) PRIMARY KEY, - name varchar(250) NOT NULL -) ENGINE=InnoDB CHARACTER SET utf8; + username varchar(191) PRIMARY KEY, + name varchar(191) NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE privacy_list ( - username varchar(250) NOT NULL, - name varchar(250) NOT NULL, + username varchar(191) NOT NULL, + name varchar(191) NOT NULL, id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_privacy_list_username USING BTREE ON privacy_list(username); CREATE UNIQUE INDEX i_privacy_list_username_name USING BTREE ON privacy_list (username(75), name(75)); @@ -189,25 +189,25 @@ CREATE TABLE privacy_list_data ( match_message boolean NOT NULL, match_presence_in boolean NOT NULL, match_presence_out boolean NOT NULL -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_privacy_list_data_id ON privacy_list_data(id); CREATE TABLE private_storage ( - username varchar(250) NOT NULL, - namespace varchar(250) NOT NULL, + username varchar(191) NOT NULL, + namespace varchar(191) NOT NULL, data text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_private_storage_username USING BTREE ON private_storage(username); CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_storage(username(75), namespace(75)); -- Not tested in mysql CREATE TABLE roster_version ( - username varchar(250) PRIMARY KEY, + username varchar(191) PRIMARY KEY, version text NOT NULL -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- To update from 1.x: -- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask; @@ -220,7 +220,7 @@ CREATE TABLE pubsub_node ( parent text, type text, nodeid bigint auto_increment primary key -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_node_parent ON pubsub_node(parent(120)); CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(20), node(120)); @@ -228,14 +228,14 @@ CREATE TABLE pubsub_node_option ( nodeid bigint, name text, val text -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option(nodeid); ALTER TABLE `pubsub_node_option` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; CREATE TABLE pubsub_node_owner ( nodeid bigint, owner text -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner(nodeid); ALTER TABLE `pubsub_node_owner` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; @@ -245,7 +245,7 @@ CREATE TABLE pubsub_state ( affiliation character(1), subscriptions text, stateid bigint auto_increment primary key -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_state_jid ON pubsub_state(jid(60)); CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state(nodeid, jid(60)); ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; @@ -257,7 +257,7 @@ CREATE TABLE pubsub_item ( creation text, modification text, payload text -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_item_itemid ON pubsub_item(itemid(36)); CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item(nodeid, itemid(36)); ALTER TABLE `pubsub_item` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; @@ -266,7 +266,7 @@ CREATE TABLE pubsub_subscription_opt ( subid text, opt_name varchar(32), opt_value text -); +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt(subid(32), opt_name(32)); CREATE TABLE muc_room ( @@ -274,7 +274,7 @@ CREATE TABLE muc_room ( host text NOT NULL, opts text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_muc_room_name_host USING BTREE ON muc_room(name(75), host(75)); @@ -283,7 +283,7 @@ CREATE TABLE muc_registered ( host text NOT NULL, nick text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_muc_registered_nick USING BTREE ON muc_registered(nick(75)); CREATE UNIQUE INDEX i_muc_registered_jid_host USING BTREE ON muc_registered(jid(75), host(75)); @@ -293,22 +293,22 @@ CREATE TABLE irc_custom ( host text NOT NULL, data text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_irc_custom_jid_host USING BTREE ON irc_custom(jid(75), host(75)); CREATE TABLE motd ( - username varchar(250) PRIMARY KEY, + username varchar(191) PRIMARY KEY, xml text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE caps_features ( - node varchar(250) NOT NULL, - subnode varchar(250) NOT NULL, + node varchar(191) NOT NULL, + subnode varchar(191) NOT NULL, feature text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_caps_features_node_subnode ON caps_features(node(75), subnode(75)); @@ -316,11 +316,11 @@ CREATE TABLE sm ( usec bigint NOT NULL, pid text NOT NULL, node text NOT NULL, - username varchar(250) NOT NULL, - resource varchar(250) NOT NULL, + username varchar(191) NOT NULL, + resource varchar(191) NOT NULL, priority text NOT NULL, info text NOT NULL -) ENGINE=InnoDB CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_sid ON sm(usec, pid(75)); CREATE INDEX i_node ON sm(node(75)); |