-- MySQL dump 10.11
--
-- Host: localhost    Database: logs
-- ------------------------------------------------------
-- Server version	5.0.32-Debian_7etch1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `channels`
--

DROP TABLE IF EXISTS `channels`;
CREATE TABLE `channels` (
  `id` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'PKEY for channels',
  `channel` char(32) NOT NULL COMMENT 'Name of the channel',
  `network` CHAR(16) NOT NULL COMMENT 'Name of the network',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_channel` (`channel`, `network`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Stores the channel names';

--
-- Table structure for table `ctcps`
--

DROP TABLE IF EXISTS `ctcps`;
CREATE TABLE `ctcps` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for ctcp',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp of the CTCP',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id',
  `target` varchar(32) NOT NULL COMMENT 'Target of the CTCP. Either #channel or Nick',
  `data` varchar(450) NOT NULL COMMENT 'Assumed max of Freenode msg of 450',
  PRIMARY KEY  (`id`),
  KEY `idx_target` (`target`),
  KEY `idx_data` (`data`(255)),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `FK_ctcps_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Stores all CTCP''s';

--
-- Table structure for table `hosts`
--

DROP TABLE IF EXISTS `hosts`;
CREATE TABLE `hosts` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for hostnames',
  `hostname` varchar(64) character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Max hostname size geared for Freenode 63 (+1)',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_hostname` (`hostname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Stores client hostnames';

--
-- Table structure for table `idents`
--

DROP TABLE IF EXISTS `idents`;
CREATE TABLE `idents` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for ident name',
  `ident_name` char(10) character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Ident name of user, 8 chars from ident +2 for irc server status',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_ident_name` (`ident_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Ident lookup table';

--
-- Table structure for table `joins`
--

DROP TABLE IF EXISTS `joins`;
CREATE TABLE `joins` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for joins',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp of when user joined',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id table',
  `channel_id` tinyint(3) unsigned NOT NULL COMMENT 'REF channels tables',
  PRIMARY KEY  (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_channel_id` (`channel_id`),
  KEY `idx_timestamp` (`timestamp`),
  CONSTRAINT `FK_joins_channel_id` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_joins_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Keeps track of joins to a channel';

--
-- Table structure for table `kicks`
--

DROP TABLE IF EXISTS `kicks`;
CREATE TABLE `kicks` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for kicks',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp of when kick occurred',
  `kicked_user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id - Full Id of the kicked',
  `kicker_user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id - Full Id of the kicker',
  `channel_id` tinyint(3) unsigned NOT NULL COMMENT 'REF channels',
  `reason_id` int(10) unsigned NOT NULL COMMENT 'REF quit_messages - Stores id of kick reason',
  PRIMARY KEY  (`id`),
  KEY `FK_kicks_kicked_id` (`kicked_user_id`),
  KEY `FK_kicks_kicker_id` (`kicker_user_id`),
  KEY `FK_kicks_channel_id` (`channel_id`),
  KEY `FK_kicks_reason_id` (`reason_id`),
  CONSTRAINT `FK_kicks_kicked_id` FOREIGN KEY (`kicked_user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_kicks_kicker_id` FOREIGN KEY (`kicker_user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_kicks_channel_id` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_kicks_reason_id` FOREIGN KEY (`reason_id`) REFERENCES `quit_messages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Stores channel kicks';

--
-- Table structure for table `messages`
--

DROP TABLE IF EXISTS `messages`;
CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'PKEY for messages',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Current date/time of the message',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id table',
  `channel_id` tinyint(3) unsigned NOT NULL COMMENT 'REF channels table',
  `message` text character set latin1 collate latin1_general_ci NOT NULL COMMENT 'The message from the channel',
  PRIMARY KEY  (`id`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_channel_id` (`channel_id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `FK_messages_channel` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_messages_user` FOREIGN KEY (`user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Stores the messages';

--
-- Table structure for table `nick_changes`
--

DROP TABLE IF EXISTS `nick_changes`;
CREATE TABLE `nick_changes` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for nick changes',
  `old_user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id table',
  `new_user_id` int(10) unsigned NOT NULL COMMENT 'REF user_if table',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Timestamp of nickchange',
  PRIMARY KEY  (`id`),
  KEY `FK_nick_changes_new_user_id` (`new_user_id`),
  KEY `FK_nick_changes_old_user_id` (`old_user_id`),
  CONSTRAINT `FK_nick_changes_new_user_id` FOREIGN KEY (`new_user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_nick_changes_old_user_id` FOREIGN KEY (`old_user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Stores the nick changes';

--
-- Table structure for table `nicks`
--

DROP TABLE IF EXISTS `nicks`;
CREATE TABLE `nicks` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for nicknames',
  `nick` char(16) character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Nick up to max 32 chars',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_nick` (`nick`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Nickname storage';

--
-- Table structure for table `notices`
--

DROP TABLE IF EXISTS `notices`;
CREATE TABLE `notices` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for notices',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp of notice',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id table',
  `target` varchar(32) NOT NULL COMMENT 'Target of the notice',
  `notice` varchar(450) NOT NULL COMMENT 'The notice itself',
  PRIMARY KEY  (`id`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Stores NOTICES from server and users';

--
-- Table structure for table `parts`
--

DROP TABLE IF EXISTS `parts`;
CREATE TABLE `parts` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for parts',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id table',
  `channel_id` tinyint(3) unsigned NOT NULL COMMENT 'REF channels table',
  `reason_id` int(10) unsigned NOT NULL COMMENT 'REF quit_messages',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp of when user parted channel',
  PRIMARY KEY  (`id`),
  KEY `FK_parts_user_id` (`user_id`),
  KEY `FK_parts_channel_id` (`channel_id`),
  KEY `FK_parts_reason_id` (`reason_id`),
  CONSTRAINT `FK_parts_channel_id` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_parts_reason_id` FOREIGN KEY (`reason_id`) REFERENCES `quit_messages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_parts_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Keeps track of when a user left a channel';

--
-- Table structure for table `quit_messages`
--

DROP TABLE IF EXISTS `quit_messages`;
CREATE TABLE `quit_messages` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for quit_messages',
  `message` varchar(255) character set latin1 collate latin1_general_cs NOT NULL COMMENT 'Message given when client quit',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_message` (`message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Keeps track of quit messages from clients';

--
-- Table structure for table `quits`
--

DROP TABLE IF EXISTS `quits`;
CREATE TABLE `quits` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for quits',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Timestamp of when the user quit',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id table',
  `quit_message_id` int(10) unsigned NOT NULL COMMENT 'REF quit_messages table',
  PRIMARY KEY  (`id`),
  KEY `FK_quits_message_id` (`quit_message_id`),
  KEY `idx_user_message_id` (`user_id`,`quit_message_id`),
  CONSTRAINT `FK_quits_message_id` FOREIGN KEY (`quit_message_id`) REFERENCES `quit_messages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_quits_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Keeps track of quit messages for each user';

--
-- Table structure for table `server_notices`
--

DROP TABLE IF EXISTS `server_notices`;
CREATE TABLE `server_notices` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for notices',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp of notice',
  `server` varchar(64) NOT NULL COMMENT 'Name of server that send the notice',
  `notice` varchar(450) NOT NULL COMMENT 'The notice itself',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Stores notices from server only';

--
-- Table structure for table `topics`
--

DROP TABLE IF EXISTS `topics`;
CREATE TABLE `topics` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for topics',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Time topic was set',
  `channel_id` tinyint(3) unsigned NOT NULL COMMENT 'REF channels table',
  `user_id` int(10) unsigned NOT NULL COMMENT 'REF user_id - User who set the topic',
  `topic` text character set latin1 collate latin1_general_ci COMMENT 'Topic that was set',
  PRIMARY KEY  (`id`),
  KEY `FK_topics_channel_id` (`channel_id`),
  KEY `FK_topics_user_id` (`user_id`),
  CONSTRAINT `FK_topics_channel_id` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_topics_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_id` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Stores the topic changes for channels';

--
-- Table structure for table `user_id`
--

DROP TABLE IF EXISTS `user_id`;
CREATE TABLE `user_id` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'PKEY for the user',
  `nick_id` int(10) unsigned NOT NULL COMMENT 'REF nicks table',
  `ident_id` int(10) unsigned NOT NULL COMMENT 'REF idents table',
  `host_id` int(10) unsigned NOT NULL COMMENT 'REF hosts table',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_nick_ident_host` (`nick_id`,`ident_id`,`host_id`),
  KEY `idx_nick_id` (`nick_id`),
  KEY `idx_ident_id` (`ident_id`),
  KEY `idx_host_id` (`host_id`),
  CONSTRAINT `FK_user_id_host` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_id_ident` FOREIGN KEY (`ident_id`) REFERENCES `idents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_id_nick` FOREIGN KEY (`nick_id`) REFERENCES `nicks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Stores the full nick!ident@hostname';

--
-- Temporary table structure for view `view_ctcps`
--

DROP TABLE IF EXISTS `view_ctcps`;
/*!50001 DROP VIEW IF EXISTS `view_ctcps`*/;
/*!50001 CREATE TABLE `view_ctcps` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `nick` char(16),
  `ident_name` char(10),
  `hostname` varchar(64),
  `target` varchar(32),
  `data` varchar(450)
) */;

--
-- Temporary table structure for view `view_joins`
--

DROP TABLE IF EXISTS `view_joins`;
/*!50001 DROP VIEW IF EXISTS `view_joins`*/;
/*!50001 CREATE TABLE `view_joins` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `channel` char(32),
  `nick` char(16),
  `ident_name` char(10),
  `hostname` varchar(64)
) */;

--
-- Temporary table structure for view `view_kicks`
--

DROP TABLE IF EXISTS `view_kicks`;
/*!50001 DROP VIEW IF EXISTS `view_kicks`*/;
/*!50001 CREATE TABLE `view_kicks` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `channel` char(32),
  `nick` char(16),
  `kicked_by` varchar(92),
  `reason` varchar(255)
) */;

--
-- Temporary table structure for view `view_messages`
--

DROP TABLE IF EXISTS `view_messages`;
/*!50001 DROP VIEW IF EXISTS `view_messages`*/;
/*!50001 CREATE TABLE `view_messages` (
  `id` bigint(20) unsigned,
  `timestamp` timestamp,
  `channel` char(32),
  `nick` char(16),
  `ident` char(10),
  `hostname` varchar(64),
  `message` text
) */;

--
-- Temporary table structure for view `view_nick_changes`
--

DROP TABLE IF EXISTS `view_nick_changes`;
/*!50001 DROP VIEW IF EXISTS `view_nick_changes`*/;
/*!50001 CREATE TABLE `view_nick_changes` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `old_nick` char(16),
  `new_nick` char(16)
) */;

--
-- Temporary table structure for view `view_notices`
--

DROP TABLE IF EXISTS `view_notices`;
/*!50001 DROP VIEW IF EXISTS `view_notices`*/;
/*!50001 CREATE TABLE `view_notices` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `nick` char(16),
  `ident_name` char(10),
  `hostname` varchar(64),
  `notice` varchar(450)
) */;

--
-- Temporary table structure for view `view_parts`
--

DROP TABLE IF EXISTS `view_parts`;
/*!50001 DROP VIEW IF EXISTS `view_parts`*/;
/*!50001 CREATE TABLE `view_parts` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `channel` char(32),
  `nick` char(16),
  `ident_name` char(10),
  `hostname` varchar(64),
  `message` varchar(255)
) */;

--
-- Temporary table structure for view `view_quits`
--

DROP TABLE IF EXISTS `view_quits`;
/*!50001 DROP VIEW IF EXISTS `view_quits`*/;
/*!50001 CREATE TABLE `view_quits` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `nick` char(16),
  `ident_name` char(10),
  `hostname` varchar(64),
  `message` varchar(255)
) */;

--
-- Temporary table structure for view `view_topics`
--

DROP TABLE IF EXISTS `view_topics`;
/*!50001 DROP VIEW IF EXISTS `view_topics`*/;
/*!50001 CREATE TABLE `view_topics` (
  `id` int(10) unsigned,
  `timestamp` timestamp,
  `channel` char(32),
  `set_by` char(16),
  `topic` text
) */;

--
-- Temporary table structure for view `view_user_id`
--

DROP TABLE IF EXISTS `view_user_id`;
/*!50001 DROP VIEW IF EXISTS `view_user_id`*/;
/*!50001 CREATE TABLE `view_user_id` (
  `id` int(10) unsigned,
  `nick` char(16),
  `ident_name` char(10),
  `hostname` varchar(64)
) */;

--
-- Final view structure for view `view_ctcps`
--

/*!50001 DROP TABLE IF EXISTS `view_ctcps`*/;
/*!50001 DROP VIEW IF EXISTS `view_ctcps`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_ctcps` AS (select `c`.`id` AS `id`,`c`.`timestamp` AS `timestamp`,`n`.`nick` AS `nick`,`i`.`ident_name` AS `ident_name`,`h`.`hostname` AS `hostname`,`c`.`target` AS `target`,`c`.`data` AS `data` from (`ctcps` `c` join (((`nicks` `n` join `idents` `i`) join `hosts` `h`) join `user_id` `u`) on(((`n`.`id` = `u`.`nick_id`) and (`i`.`id` = `u`.`ident_id`) and (`h`.`id` = `u`.`host_id`) and (`c`.`user_id` = `u`.`id`))))) */;

--
-- Final view structure for view `view_joins`
--

/*!50001 DROP TABLE IF EXISTS `view_joins`*/;
/*!50001 DROP VIEW IF EXISTS `view_joins`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_joins` AS (select `j`.`id` AS `id`,`j`.`timestamp` AS `timestamp`,`c`.`channel` AS `channel`,`n`.`nick` AS `nick`,`i`.`ident_name` AS `ident_name`,`h`.`hostname` AS `hostname` from ((`joins` `j` join (((`user_id` `u` join `nicks` `n`) join `idents` `i`) join `hosts` `h`) on(((`j`.`user_id` = `u`.`id`) and (`n`.`id` = `u`.`nick_id`) and (`i`.`id` = `u`.`ident_id`) and (`h`.`id` = `u`.`host_id`)))) join `channels` `c` on((`j`.`channel_id` = `c`.`id`)))) */;

--
-- Final view structure for view `view_kicks`
--

/*!50001 DROP TABLE IF EXISTS `view_kicks`*/;
/*!50001 DROP VIEW IF EXISTS `view_kicks`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_kicks` AS (select `k`.`id` AS `id`,`k`.`timestamp` AS `timestamp`,`c`.`channel` AS `channel`,`n`.`nick` AS `nick`,concat(`v`.`nick`,_latin1'!',`v`.`ident_name`,_latin1'@',`v`.`hostname`) AS `kicked_by`,`q`.`message` AS `reason` from ((((`kicks` `k` join `nicks` `n` on((`n`.`id` = `k`.`kicked_nick_id`))) join `view_user_id` `v` on((`v`.`id` = `k`.`kicker_user_id`))) join `quit_messages` `q` on((`q`.`id` = `k`.`reason_id`))) join `channels` `c` on((`c`.`id` = `k`.`channel_id`)))) */;

--
-- Final view structure for view `view_messages`
--

/*!50001 DROP TABLE IF EXISTS `view_messages`*/;
/*!50001 DROP VIEW IF EXISTS `view_messages`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_messages` AS (select `m`.`id` AS `id`,`m`.`timestamp` AS `timestamp`,`c`.`channel` AS `channel`,`n`.`nick` AS `nick`,`i`.`ident_name` AS `ident`,`h`.`hostname` AS `hostname`,`m`.`message` AS `message` from ((`messages` `m` join `channels` `c` on((`m`.`channel_id` = `c`.`id`))) join (((`user_id` `u` join `nicks` `n`) join `idents` `i`) join `hosts` `h`) on(((`u`.`id` = `m`.`user_id`) and (`n`.`id` = `u`.`nick_id`) and (`i`.`id` = `u`.`ident_id`) and (`h`.`id` = `u`.`host_id`))))) */;

--
-- Final view structure for view `view_nick_changes`
--

/*!50001 DROP TABLE IF EXISTS `view_nick_changes`*/;
/*!50001 DROP VIEW IF EXISTS `view_nick_changes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_nick_changes` AS (select `nc`.`id` AS `id`,`nc`.`timestamp` AS `timestamp`,`oid`.`nick` AS `old_nick`,`nid`.`nick` AS `new_nick` from ((`nick_changes` `nc` join `view_user_id` `oid` on((`oid`.`id` = `nc`.`old_user_id`))) join `view_user_id` `nid` on((`nid`.`id` = `nc`.`new_user_id`)))) */;

--
-- Final view structure for view `view_notices`
--

/*!50001 DROP TABLE IF EXISTS `view_notices`*/;
/*!50001 DROP VIEW IF EXISTS `view_notices`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_notices` AS (select `no`.`id` AS `id`,`no`.`timestamp` AS `timestamp`,`n`.`nick` AS `nick`,`i`.`ident_name` AS `ident_name`,`h`.`hostname` AS `hostname`,`no`.`notice` AS `notice` from (`notices` `no` join (((`user_id` `u` join `nicks` `n`) join `idents` `i`) join `hosts` `h`) on(((`u`.`nick_id` = `n`.`id`) and (`u`.`ident_id` = `i`.`id`) and (`u`.`host_id` = `h`.`id`)))) where (`u`.`id` = `no`.`user_id`)) */;

--
-- Final view structure for view `view_parts`
--

/*!50001 DROP TABLE IF EXISTS `view_parts`*/;
/*!50001 DROP VIEW IF EXISTS `view_parts`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_parts` AS (select `p`.`id` AS `id`,`p`.`timestamp` AS `timestamp`,`c`.`channel` AS `channel`,`v`.`nick` AS `nick`,`v`.`ident_name` AS `ident_name`,`v`.`hostname` AS `hostname`,`q`.`message` AS `message` from (((`parts` `p` join `view_user_id` `v` on((`p`.`user_id` = `v`.`id`))) join `channels` `c` on((`p`.`channel_id` = `c`.`id`))) join `quit_messages` `q` on((`p`.`reason_id` = `q`.`id`)))) */;

--
-- Final view structure for view `view_quits`
--

/*!50001 DROP TABLE IF EXISTS `view_quits`*/;
/*!50001 DROP VIEW IF EXISTS `view_quits`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_quits` AS (select `q`.`id` AS `id`,`q`.`timestamp` AS `timestamp`,`n`.`nick` AS `nick`,`i`.`ident_name` AS `ident_name`,`h`.`hostname` AS `hostname`,`m`.`message` AS `message` from ((`quits` `q` join (((`user_id` `u` join `nicks` `n`) join `idents` `i`) join `hosts` `h`) on(((`u`.`nick_id` = `n`.`id`) and (`u`.`ident_id` = `i`.`id`) and (`u`.`host_id` = `h`.`id`) and (`q`.`user_id` = `u`.`id`)))) join `quit_messages` `m` on((`m`.`id` = `q`.`quit_message_id`)))) */;

--
-- Final view structure for view `view_topics`
--

/*!50001 DROP TABLE IF EXISTS `view_topics`*/;
/*!50001 DROP VIEW IF EXISTS `view_topics`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_topics` AS (select `t`.`id` AS `id`,`t`.`timestamp` AS `timestamp`,`c`.`channel` AS `channel`,`n`.`nick` AS `set_by`,`t`.`topic` AS `topic` from ((`topics` `t` join `channels` `c` on((`c`.`id` = `t`.`channel_id`))) join (`nicks` `n` join `user_id` `u`) on(((`u`.`id` = `t`.`user_id`) and (`u`.`nick_id` = `n`.`id`))))) */;

--
-- Final view structure for view `view_user_id`
--

/*!50001 DROP TABLE IF EXISTS `view_user_id`*/;
/*!50001 DROP VIEW IF EXISTS `view_user_id`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `view_user_id` AS (select `u`.`id` AS `id`,`n`.`nick` AS `nick`,`i`.`ident_name` AS `ident_name`,`h`.`hostname` AS `hostname` from (((`user_id` `u` join `nicks` `n` on((`u`.`nick_id` = `n`.`id`))) join `idents` `i` on((`u`.`ident_id` = `i`.`id`))) join `hosts` `h` on((`u`.`host_id` = `h`.`id`)))) */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-07-11 17:30:28