Tables - what each of them do

[Zok]

Table structure:

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 26, 2008 at 01:22 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6-1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `zb3`
--

-- --------------------------------------------------------

--
-- Table structure for table `access`
--

CREATE TABLE IF NOT EXISTS `access` (
`uid` varchar(32) NOT NULL,
`chan` varchar(30) default NULL,
`level` tinyint(4) NOT NULL,
`status` tinyint(4) NOT NULL,
`pass` varchar(32) NOT NULL,
KEY `nick` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `bot_messages`
--

CREATE TABLE IF NOT EXISTS `bot_messages` (
`id` int(11) NOT NULL,
`language` varchar(2) NOT NULL,
`message` varchar(250) NOT NULL,
KEY `language` (`language`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `channel_settings`
--

CREATE TABLE IF NOT EXISTS `channel_settings` (
`chan` varchar(30) NOT NULL,
`memo_add` tinyint(4) NOT NULL default '0',
`memo_rem` tinyint(4) NOT NULL default '0',
`memo_list` tinyint(4) NOT NULL default '0',
`access_edit` tinyint(4) NOT NULL default '10',
`access_list` tinyint(4) NOT NULL default '10',
`link_add` tinyint(4) NOT NULL default '0',
`link_list` tinyint(4) NOT NULL default '0',
`link_rem` tinyint(4) NOT NULL default '0',
`level_list` tinyint(4) NOT NULL default '10',
`level_edit` tinyint(4) NOT NULL default '10',
`stats_conf_list` tinyint(4) NOT NULL default '0',
`stats_conf_edit` tinyint(4) NOT NULL default '10',
`quote_add` tinyint(4) NOT NULL default '0',
`quote_rem` tinyint(4) NOT NULL default '0',
`quote_list` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`chan`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `channel_totals`
--

CREATE TABLE IF NOT EXISTS `channel_totals` (
`loc` varchar(40) NOT NULL,
`kicks` int(11) NOT NULL,
`joins` int(11) NOT NULL,
`lines` int(11) NOT NULL,
`questions` int(11) NOT NULL,
`urls` int(11) NOT NULL,
`lol` int(11) NOT NULL,
`wpl` int(11) NOT NULL,
`kicked` int(11) NOT NULL,
`acts` int(11) NOT NULL,
`swears` int(11) NOT NULL,
`topics` int(11) NOT NULL,
`time_seen` int(11) NOT NULL,
`ban` int(11) NOT NULL,
`unban` int(11) NOT NULL,
`modes` varchar(30) NOT NULL,
`nicks` int(11) NOT NULL,
`addressed` int(11) NOT NULL,
`emots` int(11) NOT NULL,
`animots` int(11) NOT NULL,
`promotes` int(11) NOT NULL,
`demotes` int(11) NOT NULL,
`promoted` int(11) NOT NULL,
`demoted` int(11) NOT NULL,
KEY `loc` (`loc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `conf`
--

CREATE TABLE IF NOT EXISTS `conf` (
`name` varchar(20) NOT NULL,
`value` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `conf_ignore`
--

CREATE TABLE IF NOT EXISTS `conf_ignore` (
`nick` varchar(35) NOT NULL,
PRIMARY KEY (`nick`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `conf_silent`
--

CREATE TABLE IF NOT EXISTS `conf_silent` (
`chan` varchar(30) NOT NULL,
`level` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`chan`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `conf_stats_flow`
--

CREATE TABLE IF NOT EXISTS `conf_stats_flow` (
`chan` varchar(36) NOT NULL,
`flow` int(11) NOT NULL,
PRIMARY KEY (`chan`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `crossword`
--

CREATE TABLE IF NOT EXISTS `crossword` (
`word` varchar(32) NOT NULL,
`type` varchar(5) NOT NULL,
KEY `word` (`word`),
KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `links`
--

CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) NOT NULL auto_increment,
`channel` varchar(30) NOT NULL,
`nick` varchar(30) NOT NULL,
`url` text NOT NULL,
`date` int(11) NOT NULL,
`tags` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;

-- --------------------------------------------------------

--
-- Table structure for table `memo`
--

CREATE TABLE IF NOT EXISTS `memo` (
`tag` int(11) NOT NULL auto_increment,
`timestamp` int(11) NOT NULL,
`user` varchar(25) NOT NULL,
`message` text NOT NULL,
`location` varchar(25) NOT NULL,
`valid` tinyint(4) NOT NULL,
PRIMARY KEY (`tag`),
UNIQUE KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=548 ;

-- --------------------------------------------------------

--
-- Table structure for table `profile`
--

CREATE TABLE IF NOT EXISTS `profile` (
`uid` varchar(32) NOT NULL,
`hash` varchar(32) NOT NULL,
`email` varchar(50) NOT NULL,
`pend` tinyint(1) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `quote`
--

CREATE TABLE IF NOT EXISTS `quote` (
`tag` int(11) NOT NULL auto_increment,
`timestamp` int(11) NOT NULL,
`user` varchar(25) NOT NULL,
`message` text NOT NULL,
`location` varchar(25) NOT NULL,
`valid` tinyint(4) NOT NULL,
PRIMARY KEY (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

-- --------------------------------------------------------

--
-- Table structure for table `rquote`
--

CREATE TABLE IF NOT EXISTS `rquote` (
`id` int(11) NOT NULL auto_increment,
`nick` varchar(30) NOT NULL,
`content` text NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2136 ;

-- --------------------------------------------------------

--
-- Table structure for table `sessions`
--

CREATE TABLE IF NOT EXISTS `sessions` (
`sid` varchar(32) NOT NULL,
`uid` varchar(32) NOT NULL,
`ip` varchar(16) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats`
--

CREATE TABLE IF NOT EXISTS `stats` (
`uid` varchar(32) NOT NULL,
`loc` varchar(40) NOT NULL,
`kicks` int(11) NOT NULL,
`joins` int(11) NOT NULL,
`lines` int(11) NOT NULL,
`questions` int(11) NOT NULL,
`urls` int(11) NOT NULL,
`lol` int(11) NOT NULL,
`wpl` int(11) NOT NULL,
`kicked` int(11) NOT NULL,
`acts` int(11) NOT NULL,
`swears` int(11) NOT NULL,
`topics` int(11) NOT NULL,
`modes` varchar(30) NOT NULL,
`time_seen` int(11) NOT NULL,
`ban` int(11) NOT NULL,
`unban` int(11) NOT NULL,
`nicks` int(11) NOT NULL,
`addressed` int(11) NOT NULL,
`emots` int(11) NOT NULL,
`animots` int(11) NOT NULL,
`promotes` int(11) NOT NULL,
`demotes` int(11) NOT NULL,
`promoted` int(11) NOT NULL,
`demoted` int(11) NOT NULL,
KEY `uid` (`uid`),
KEY `loc` (`loc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `timers`
--

CREATE TABLE IF NOT EXISTS `timers` (
`id` int(11) NOT NULL auto_increment,
`time` int(11) NOT NULL,
`who` varchar(30) NOT NULL,
`message` varchar(250) NOT NULL,
PRIMARY KEY (`id`),
KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

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

CREATE TABLE IF NOT EXISTS `topics` (
`time` int(11) NOT NULL,
`channel` varchar(30) NOT NULL,
`nick` varchar(30) NOT NULL,
`topic` varchar(300) NOT NULL,
KEY `channel` (`channel`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `urls`
--

CREATE TABLE IF NOT EXISTS `urls` (
`id` int(11) NOT NULL auto_increment,
`date` int(11) NOT NULL,
`nick` varchar(30) NOT NULL,
`url` text NOT NULL,
`context` text NOT NULL,
`chan` varchar(20) NOT NULL,
`clicked` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_sessions`
--

CREATE TABLE IF NOT EXISTS `user_sessions` (
`sid` varchar(32) NOT NULL,
`uid` varchar(32) NOT NULL,
`ip` varchar(16) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_stats`
--

CREATE TABLE IF NOT EXISTS `user_stats` (
`nick` varchar(30) NOT NULL,
`uid` varchar(32) NOT NULL,
`main` varchar(5) NOT NULL,
`date` int(11) NOT NULL,
`language` varchar(2) NOT NULL default 'en',
UNIQUE KEY `nick` (`nick`),
KEY `uid` (`uid`),
KEY `main` (`main`),
KEY `language` (`language`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[Zok]

access - originally used to determine whether or not a user had access to perform an operation in a channel. Columns for user, channel, and level. Level is compared with the level required to perform that operation (stored in channel_settings).

bot_messages - This is the bot's language pack. Whenever you want to add an error message, shove it in here. Make sure you get the ID number right, or the bot might use that as a reply for something else! There can only be one message ID + language pair. Every error must have at least an english entry (since it's default).

channel_settings - Needs to be restructured. Type will be a column instead of having a row for each type. That way ALTER commands will not be needed to add new access handlers

channel_totals - A cron job runs a script that goes through the stats table, sums the total stats for each channel and drops them in here.

conf - This is just a place to store single values that are used both in the perl and php sides of the bot. Currently mine holds: hits=>INT, admin=>VARCHAR, and statsurl=>VARCHAR - where hits are the number of hits the site has, admin is a password hash for the bot administrator, statsurl is the url that the bot can attach "?uid=(your uid)" to in order to link you to the stats page.

conf_ignore - a list of nicknames that the bot is to completely ignore.

conf_silent - a list of channels that the bot remains silent in. Will soon be changed to allow a "level of silence" - ie: the bot may only respond to you if you have +v or +o in that channel.

conf_stats_flow - Keeps track of how many lines of text you must have in order to be displayed on the stats page for that channel (or globally).

crossword - For the crossword module of the bot. A giant list of words and their type. Type refers to whether it is a contraction, prefix, suffix, etc. (not noun, verb, adjective, etc).

links - Keeps a list of links that the bot should store for the channel (useful for links that don't get google priority).

memo - keeps memos for users and channels.

profile - Intended for user settings such as password, email address, and other bot preferences.

quote - identical to memo except for quotes.

rquote - was originally going to be used to store random lines of text from each person. When their profile is viewed, you would see a few of their random lines of text. There would be a filter to control what does register as "random quote worthy"

sessions - Used to keep track of users logged into the web interface.

stats - where all of the users stats are stored. The column location can be either "global" or a channel name. Every time a user is updated, that channel + user row will be updated as well as the global + user row.

timers - for the timer module. Allows users to set messages for the bot to delivered to them at given times.

topics - a list of all the topics that have ever been used

urls - Was used in the past, currently out of use. Stores all of the urls posted to a channels that the bot is in.

user_sessions - I don't know why this is here. It's a clone of sessions except that it was intended to be for the user control panel instead of the admin control panel.

user_stats - This is a mapping of every nickname the bot has ever seen to a unique ID. When users are merged, both names will remain except they will point to the same ID (and thus the same stats). Other info is starting to be stored in here (such as language preferences) so I am thinking of combining this with profile.