diff options
| -rw-r--r-- | sql/mysql.sql | 54 |
1 files changed, 25 insertions, 29 deletions
diff --git a/sql/mysql.sql b/sql/mysql.sql index 976230117..c908022d5 100644 --- a/sql/mysql.sql +++ b/sql/mysql.sql @@ -17,21 +17,18 @@ -- 02111-1307 USA -- --- Needs MySQL (at least 4.0.x) with innodb back-end -SET table_type=InnoDB; - CREATE TABLE users ( username varchar(250) PRIMARY KEY, password text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE last ( username varchar(250) PRIMARY KEY, seconds text NOT NULL, state text NOT NULl -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE rosterusers ( @@ -45,7 +42,7 @@ CREATE TABLE rosterusers ( subscribe text NOT NULL, type text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username(75), jid(75)); CREATE INDEX i_rosteru_username ON rosterusers(username); @@ -55,7 +52,7 @@ CREATE TABLE rostergroups ( username varchar(250) NOT NULL, jid varchar(250) NOT NULL, grp text NOT NULL -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE INDEX pk_rosterg_user_jid ON rostergroups(username(75), jid(75)); @@ -63,13 +60,13 @@ CREATE TABLE sr_group ( name varchar(250) NOT NULL, opts text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE sr_user ( jid varchar(250) NOT NULL, grp varchar(250) NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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); @@ -80,22 +77,21 @@ CREATE TABLE spool ( xml text NOT NULL, seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE INDEX i_despool USING BTREE ON spool(username); - CREATE TABLE vcard ( username varchar(250) PRIMARY KEY, vcard mediumtext NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE vcard_xupdate ( username varchar(250) PRIMARY KEY, hash text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE vcard_search ( username varchar(250) NOT NULL, @@ -122,7 +118,7 @@ CREATE TABLE vcard_search ( lorgname varchar(250) NOT NULL, orgunit text NOT NULL, lorgunit varchar(250) NOT NULL -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn); CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily); @@ -139,14 +135,14 @@ 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 -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE privacy_list ( username varchar(250) NOT NULL, name varchar(250) NOT NULL, id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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)); @@ -162,14 +158,14 @@ CREATE TABLE privacy_list_data ( match_message boolean NOT NULL, match_presence_in boolean NOT NULL, match_presence_out boolean NOT NULL -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE private_storage ( username varchar(250) NOT NULL, namespace varchar(250) NOT NULL, data text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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)); @@ -178,7 +174,7 @@ CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_ CREATE TABLE roster_version ( username varchar(250) PRIMARY KEY, version text NOT NULL -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; -- To update from 1.x: -- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask; @@ -191,7 +187,7 @@ CREATE TABLE pubsub_node ( parent text, type text, nodeid bigint auto_increment primary key -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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)); @@ -199,14 +195,14 @@ CREATE TABLE pubsub_node_option ( nodeid bigint, name text, val text -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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 -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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; @@ -216,7 +212,7 @@ CREATE TABLE pubsub_state ( affiliation character(1), subscriptions text, stateid bigint auto_increment primary key -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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; @@ -228,7 +224,7 @@ CREATE TABLE pubsub_item ( creation text, modification text, payload text -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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; @@ -245,7 +241,7 @@ CREATE TABLE muc_room ( host text NOT NULL, opts text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE UNIQUE INDEX i_muc_room_name_host USING BTREE ON muc_room(name(75), host(75)); @@ -254,7 +250,7 @@ CREATE TABLE muc_registered ( host text NOT NULL, nick text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; 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)); @@ -264,7 +260,7 @@ CREATE TABLE irc_custom ( host text NOT NULL, data text NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE UNIQUE INDEX i_irc_custom_jid_host USING BTREE ON irc_custom(jid(75), host(75)); @@ -272,13 +268,13 @@ CREATE TABLE motd ( username varchar(250) PRIMARY KEY, xml text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE TABLE caps_features ( node varchar(250) NOT NULL, subnode varchar(250) NOT NULL, feature text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) CHARACTER SET utf8; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE INDEX i_caps_features_node_subnode ON caps_features(node(75), subnode(75)); |