diff options
| -rw-r--r-- | Changes | 1 | ||||
| -rw-r--r-- | Changes.mysql | 29 | ||||
| -rw-r--r-- | data/tables.sql | 332 | ||||
| -rw-r--r-- | version.log | 6 |
4 files changed, 225 insertions, 143 deletions
@@ -36,6 +36,7 @@ Anope Version S V N 08/29 F OperServ SGLINE ADD now strips a trailing space from the mask. [#761] 08/29 F TS6 UID generation for all supported TS6 IRCDs. [#731] 09/01 F Added a note in WIN32.txt about pre-compiled installers [# 00] +09/02 F Created MySQL indexes to decrease load on the database. [# 00] Provided by Trystan <trystan@nomadirc.net> - 2007 08/29 F Module runtime directory not always properly cleaned up. [#768] diff --git a/Changes.mysql b/Changes.mysql index 7d341dde0..86f040ef0 100644 --- a/Changes.mysql +++ b/Changes.mysql @@ -1,6 +1,33 @@ Anope Version S V N -------------------- -- NONE +ALTER TABLE `anope_ns_request` DROP INDEX `nick_index` ; +ALTER TABLE `anope_ns_core` DROP INDEX `display_index` ; +ALTER TABLE `anope_ns_access` ADD UNIQUE (`display`) ; +ALTER TABLE `anope_ms_info` ADD UNIQUE (`nm_id` , `serv`) ; +ALTER TABLE `anope_ns_alias` DROP INDEX `nick_index` ; +ALTER TABLE `anope_cs_info` DROP INDEX `name_index` ; +ALTER TABLE `anope_cs_access` ADD UNIQUE (`channel` , `display`) ; +ALTER TABLE `anope_cs_levels` ADD UNIQUE (`channel` , `position`) ; +ALTER TABLE `anope_cs_akicks` CHANGE `dmask` `dmask` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_cs_akicks` ADD UNIQUE (`channel` , `dmask`) ; +ALTER TABLE `anope_cs_badwords` CHANGE `word` `word` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_cs_badwords` ADD UNIQUE (`channel` , `word`) ; +ALTER TABLE `anope_cs_ttb` ADD UNIQUE (`channel` , `ttb_id`) ; +ALTER TABLE `anope_os_akills` CHANGE `user` `user` VARCHAR( 255 ) NOT NULL , CHANGE `host` `host` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_os_akills` ADD UNIQUE (`user` , `host`) ; +ALTER TABLE `anope_os_sglines` CHANGE `mask` `mask` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_os_sglines` ADD UNIQUE (`mask`) ; +ALTER TABLE `anope_os_sqlines` CHANGE `mask` `mask` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_os_sqlines` ADD UNIQUE (`mask`) ; +ALTER TABLE `anope_os_szlines` CHANGE `mask` `mask` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_os_szlines` ADD UNIQUE (`mask`) ; +ALTER TABLE `anope_os_news` ADD UNIQUE (`type` , `num` , `time`) ; +ALTER TABLE `anope_os_exceptions` CHANGE `mask` `mask` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_os_exceptions` ADD UNIQUE (`mask`) ; +ALTER TABLE `anope_hs_core` DROP INDEX `nick_index` ; +ALTER TABLE `anope_bs_core` DROP INDEX `nick_index` ; +ALTER TABLE `anope_ms_info` CHANGE `receiver` `receiver` VARCHAR( 255 ) NOT NULL ; +ALTER TABLE `anope_ms_info` ADD INDEX ( `receiver` , `serv` ) ; Anope Version 1.7.19 -------------------- diff --git a/data/tables.sql b/data/tables.sql index 546c95287..2d4889cb5 100644 --- a/data/tables.sql +++ b/data/tables.sql @@ -1,27 +1,18 @@ -- If you need to create your db, uncomment the following lines. --- create database anope; --- use anope; +-- +-- CREATE DATABASE anope; +-- USE anope; --- --- Table structure for table `anope_info` --- - -DROP TABLE IF EXISTS anope_info; -CREATE TABLE anope_info ( - version int(11) default NULL, - date datetime default NULL -) TYPE=MyISAM; - --- --- Table structure for table `anope_bs_core` --- +-- +-- Table structure for table 'anope_bs_core' +-- DROP TABLE IF EXISTS anope_bs_core; CREATE TABLE anope_bs_core ( bs_id int(11) NOT NULL auto_increment, nick varchar(255) NOT NULL default '', - user text NOT NULL, + `user` text NOT NULL, host text NOT NULL, rname text NOT NULL, flags int(11) NOT NULL default '0', @@ -29,65 +20,75 @@ CREATE TABLE anope_bs_core ( chancount int(11) NOT NULL default '0', active int(1) NOT NULL default '1', PRIMARY KEY (bs_id), - UNIQUE KEY nick (nick), - KEY nick_index (nick(10)) -) TYPE=MyISAM; + UNIQUE KEY nick (nick) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_cs_access` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_cs_access' +-- DROP TABLE IF EXISTS anope_cs_access; CREATE TABLE anope_cs_access ( ca_id int(11) NOT NULL auto_increment, in_use int(11) NOT NULL default '0', - level int(11) NOT NULL default '0', + `level` int(11) NOT NULL default '0', display varchar(255) NOT NULL default '', channel varchar(255) NOT NULL default '', last_seen int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (ca_id) -) TYPE=MyISAM; + PRIMARY KEY (ca_id), + UNIQUE KEY channel (channel,display) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_cs_akicks` --- +-- +-- Table structure for table 'anope_cs_akicks' +-- DROP TABLE IF EXISTS anope_cs_akicks; CREATE TABLE anope_cs_akicks ( ck_id int(11) NOT NULL auto_increment, channel varchar(255) NOT NULL default '', flags int(11) NOT NULL default '0', - dmask text NOT NULL, + dmask varchar(255) NOT NULL, reason text NOT NULL, creator text NOT NULL, addtime int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (ck_id) -) TYPE=MyISAM; + PRIMARY KEY (ck_id), + UNIQUE KEY channel (channel,dmask) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_cs_badwords` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_cs_badwords' +-- DROP TABLE IF EXISTS anope_cs_badwords; CREATE TABLE anope_cs_badwords ( cw_id int(11) NOT NULL auto_increment, channel varchar(255) NOT NULL default '', - word text NOT NULL, - type int(11) NOT NULL default '0', + word varchar(255) NOT NULL, + `type` int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (cw_id) -) TYPE=MyISAM; + PRIMARY KEY (cw_id), + UNIQUE KEY channel (channel,word) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_cs_info` --- +-- +-- Table structure for table 'anope_cs_info' +-- DROP TABLE IF EXISTS anope_cs_info; CREATE TABLE anope_cs_info ( ci_id int(11) NOT NULL auto_increment, - name varchar(255) NOT NULL default '', + `name` varchar(255) NOT NULL default '', founder text NOT NULL, successor text NOT NULL, founderpass tinyblob NOT NULL, @@ -123,40 +124,48 @@ CREATE TABLE anope_cs_info ( repeattimes smallint(6) NOT NULL default '0', active tinyint(1) NOT NULL default '1', PRIMARY KEY (ci_id), - UNIQUE KEY name (name), - KEY name_index (name(10)) -) TYPE=MyISAM; + UNIQUE KEY `name` (`name`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_cs_levels` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_cs_levels' +-- DROP TABLE IF EXISTS anope_cs_levels; CREATE TABLE anope_cs_levels ( cl_id int(11) NOT NULL auto_increment, channel varchar(255) NOT NULL default '', position int(11) NOT NULL default '0', - level int(11) NOT NULL default '0', + `level` int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (cl_id) -) TYPE=MyISAM; + PRIMARY KEY (cl_id), + UNIQUE KEY channel (channel,position) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_cs_ttb' +-- --- --- Table structure for table `anope_cs_ttb` --- DROP TABLE IF EXISTS anope_cs_ttb; CREATE TABLE anope_cs_ttb ( ct_id int(11) NOT NULL auto_increment, channel varchar(255) NOT NULL default '', ttb_id int(11) NOT NULL default '0', - value int(11) NOT NULL default '0', + `value` int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (ct_id) -) TYPE=MyISAM; + PRIMARY KEY (ct_id), + UNIQUE KEY channel (channel,ttb_id) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_hs_core` --- +-- +-- Table structure for table 'anope_hs_core' +-- DROP TABLE IF EXISTS anope_hs_core; CREATE TABLE anope_hs_core ( @@ -165,34 +174,51 @@ CREATE TABLE anope_hs_core ( vident text NOT NULL, vhost text NOT NULL, creator text NOT NULL, - time int(11) NOT NULL default '0', + `time` int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', PRIMARY KEY (bs_id), - UNIQUE KEY nick (nick), - KEY nick_index (nick(10)) -) TYPE=MyISAM; + UNIQUE KEY nick (nick) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_ms_info` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_info' +-- + +DROP TABLE IF EXISTS anope_info; +CREATE TABLE anope_info ( + version int(11) default NULL, + `date` datetime default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_ms_info' +-- DROP TABLE IF EXISTS anope_ms_info; CREATE TABLE anope_ms_info ( nm_id int(11) NOT NULL auto_increment, - receiver text NOT NULL, + receiver varchar(255) NOT NULL, number int(11) NOT NULL default '0', flags int(11) NOT NULL default '0', - time int(11) NOT NULL default '0', + `time` int(11) NOT NULL default '0', sender text NOT NULL, - text blob NOT NULL, + `text` blob NOT NULL, serv enum('NICK','CHAN') NOT NULL default 'NICK', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (nm_id) -) TYPE=MyISAM; + PRIMARY KEY (nm_id), + UNIQUE KEY nm_id (nm_id,serv), + KEY receiver (receiver,serv) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_ns_access` --- +-- +-- Table structure for table 'anope_ns_access' +-- DROP TABLE IF EXISTS anope_ns_access; CREATE TABLE anope_ns_access ( @@ -200,12 +226,15 @@ CREATE TABLE anope_ns_access ( display varchar(255) NOT NULL default '', access text NOT NULL, active tinyint(1) NOT NULL default '1', - PRIMARY KEY (na_id) -) TYPE=MyISAM; + PRIMARY KEY (na_id), + UNIQUE KEY display (display) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_ns_alias` --- +-- +-- Table structure for table 'anope_ns_alias' +-- DROP TABLE IF EXISTS anope_ns_alias; CREATE TABLE anope_ns_alias ( @@ -214,19 +243,20 @@ CREATE TABLE anope_ns_alias ( nick varchar(255) NOT NULL default '', time_registered int(10) unsigned NOT NULL default '0', last_seen int(10) unsigned NOT NULL default '0', - status int(11) unsigned NOT NULL default '0', + `status` int(11) unsigned NOT NULL default '0', last_usermask text NOT NULL, last_realname text NOT NULL, last_quit text NOT NULL, active tinyint(1) NOT NULL default '1', PRIMARY KEY (na_id), - UNIQUE KEY nick (nick), - KEY nick_index (nick(10)) -) TYPE=MyISAM; + UNIQUE KEY nick (nick) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_ns_core` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_ns_core' +-- DROP TABLE IF EXISTS anope_ns_core; CREATE TABLE anope_ns_core ( @@ -237,7 +267,7 @@ CREATE TABLE anope_ns_core ( icq int(10) unsigned NOT NULL default '0', url text NOT NULL, flags int(11) NOT NULL default '0', - language smallint(5) unsigned NOT NULL default '0', + `language` smallint(5) unsigned NOT NULL default '0', accesscount smallint(6) NOT NULL default '0', memocount smallint(5) unsigned NOT NULL default '0', memomax smallint(5) unsigned NOT NULL default '0', @@ -246,48 +276,53 @@ CREATE TABLE anope_ns_core ( greet text NOT NULL, active tinyint(1) NOT NULL default '1', PRIMARY KEY (nc_id), - UNIQUE KEY display (display), - KEY display_index (display(10)) -) TYPE=MyISAM; + UNIQUE KEY display (display) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_ns_request` --- +-- +-- Table structure for table 'anope_ns_request' +-- DROP TABLE IF EXISTS anope_ns_request; CREATE TABLE anope_ns_request ( nr_id int(11) NOT NULL auto_increment, nick varchar(255) NOT NULL default '', passcode text NOT NULL, - password tinyblob NOT NULL, + `password` tinyblob NOT NULL, email text NOT NULL, requested int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', PRIMARY KEY (nr_id), - UNIQUE KEY nick (nick), - KEY nick_index (nick(10)) -) TYPE=MyISAM; + UNIQUE KEY nick (nick) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_os_akills` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_os_akills' +-- DROP TABLE IF EXISTS anope_os_akills; CREATE TABLE anope_os_akills ( ok_id int(11) NOT NULL auto_increment, - user text NOT NULL, - host text NOT NULL, + `user` varchar(255) NOT NULL, + host varchar(255) NOT NULL, xby text NOT NULL, reason text NOT NULL, seton int(11) NOT NULL default '0', expire int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (ok_id) -) TYPE=MyISAM; + PRIMARY KEY (ok_id), + UNIQUE KEY `user` (`user`,host) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_os_core` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_os_core' +-- DROP TABLE IF EXISTS anope_os_core; CREATE TABLE anope_os_core ( @@ -299,85 +334,100 @@ CREATE TABLE anope_os_core ( sqlines_count int(11) NOT NULL default '0', szlines_count int(11) NOT NULL default '0', PRIMARY KEY (oc_id) -) TYPE=MyISAM; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_os_exceptions` --- +-- +-- Table structure for table 'anope_os_exceptions' +-- DROP TABLE IF EXISTS anope_os_exceptions; CREATE TABLE anope_os_exceptions ( oe_id int(11) NOT NULL auto_increment, - mask text NOT NULL, + mask varchar(255) NOT NULL, lim int(11) NOT NULL default '0', who text NOT NULL, reason text NOT NULL, - time int(11) NOT NULL default '0', + `time` int(11) NOT NULL default '0', expires int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (oe_id) -) TYPE=MyISAM; + PRIMARY KEY (oe_id), + UNIQUE KEY mask (mask) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_os_news` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_os_news' +-- DROP TABLE IF EXISTS anope_os_news; CREATE TABLE anope_os_news ( on_id int(11) NOT NULL auto_increment, - type int(11) NOT NULL default '0', + `type` int(11) NOT NULL default '0', num int(11) NOT NULL default '0', ntext text NOT NULL, who text NOT NULL, - time int(11) NOT NULL default '0', + `time` int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (on_id) -) TYPE=MyISAM; + PRIMARY KEY (on_id), + UNIQUE KEY `type` (`type`,num,`time`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_os_sglines` --- +-- +-- Table structure for table 'anope_os_sglines' +-- DROP TABLE IF EXISTS anope_os_sglines; CREATE TABLE anope_os_sglines ( og_id int(11) NOT NULL auto_increment, - mask text NOT NULL, + mask varchar(255) NOT NULL, xby text NOT NULL, reason text NOT NULL, seton int(11) NOT NULL default '0', expire int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (og_id) -) TYPE=MyISAM; + PRIMARY KEY (og_id), + UNIQUE KEY mask (mask) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; --- --- Table structure for table `anope_os_sqlines` --- +-- -------------------------------------------------------- + +-- +-- Table structure for table 'anope_os_sqlines' +-- DROP TABLE IF EXISTS anope_os_sqlines; CREATE TABLE anope_os_sqlines ( og_id int(11) NOT NULL auto_increment, - mask text NOT NULL, + mask varchar(255) NOT NULL, xby text NOT NULL, reason text NOT NULL, seton int(11) NOT NULL default '0', expire int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (og_id) -) TYPE=MyISAM; + PRIMARY KEY (og_id), + UNIQUE KEY mask (mask) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- --- --- Table structure for table `anope_os_szlines` --- +-- +-- Table structure for table 'anope_os_szlines' +-- DROP TABLE IF EXISTS anope_os_szlines; CREATE TABLE anope_os_szlines ( og_id int(11) NOT NULL auto_increment, - mask text NOT NULL, + mask varchar(255) NOT NULL, xby text NOT NULL, reason text NOT NULL, seton int(11) NOT NULL default '0', expire int(11) NOT NULL default '0', active tinyint(1) NOT NULL default '1', - PRIMARY KEY (og_id) -) TYPE=MyISAM; + PRIMARY KEY (og_id), + UNIQUE KEY mask (mask) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; diff --git a/version.log b/version.log index e9e864586..3cebf4dca 100644 --- a/version.log +++ b/version.log @@ -9,10 +9,14 @@ VERSION_MAJOR="1" VERSION_MINOR="7" VERSION_PATCH="19" VERSION_EXTRA="-svn" -VERSION_BUILD="1298" +VERSION_BUILD="1299" # $Log$ # +# BUILD : 1.7.19 (1299) +# BUGS : +# NOTES : Added MySQL indexes to decrease the MySQL load during database operations +# # BUILD : 1.7.19 (1298) # BUGS : N/A # NOTES : [1] Applied heinz patch to WIN32 docs [2] Corrected comment in chanserv.c |