Spotweb Database structure, versie 0.03

-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 12, 2011 at 08:45 PM
-- Server version: 5.0.91
-- PHP Version: 5.3.6-pl0-gentoo

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `spotweb`
--

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

--
-- Table structure for table `commentsfull`
--

DROP TABLE IF EXISTS `commentsfull`;
CREATE TABLE `commentsfull` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `messageid` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `fromhdr` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `stamp` INT(11) DEFAULT NULL,
  `usersignature` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `userkey` VARCHAR(200) CHARACTER SET utf8 DEFAULT NULL,
  `userid` VARCHAR(32) CHARACTER SET utf8 DEFAULT NULL,
  `hashcash` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `body` text CHARACTER SET utf8,
  `verified` tinyint(1) DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_commentsfull_1` (`messageid`),
  KEY `idx_commentsfull_2` (`messageid`,`stamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `commentsxover`
--

DROP TABLE IF EXISTS `commentsxover`;
CREATE TABLE `commentsxover` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `messageid` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `nntpref` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `spotrating` INT(11) DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `idx_commentsxover_1` (`nntpref`,`messageid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=157230 ;

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

--
-- Table structure for table `downloadlist`
--

DROP TABLE IF EXISTS `downloadlist`;
CREATE TABLE `downloadlist` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `messageid` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `stamp` INT(11) DEFAULT NULL,
  `ouruserid` INT(11) DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `idx_downloadlist_1` (`messageid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=70 ;

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

--
-- Table structure for table `nntp`
--

DROP TABLE IF EXISTS `nntp`;
CREATE TABLE `nntp` (
  `server` VARCHAR(128) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `maxarticleid` INT(11) DEFAULT NULL,
  `nowrunning` INT(11) DEFAULT '0',
  `lastrun` INT(11) DEFAULT '0',
  PRIMARY KEY  (`server`),
  UNIQUE KEY `maxarticleid` (`maxarticleid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `settings`
--

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `value` text CHARACTER SET utf8,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_settings_1` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

INSERT INTO `settings` (`id`, `name`, `value`) VALUES
(1, 'schemaversion', '0.03');


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

--
-- Table structure for table `spots`
--

DROP TABLE IF EXISTS `spots`;
CREATE TABLE `spots` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `messageid` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `spotid` INT(11) DEFAULT NULL,
  `category` INT(11) DEFAULT NULL,
  `subcat` INT(11) DEFAULT NULL,
  `poster` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `groupname` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `subcata` VARCHAR(64) CHARACTER SET utf8 DEFAULT NULL,
  `subcatb` VARCHAR(64) CHARACTER SET utf8 DEFAULT NULL,
  `subcatc` VARCHAR(64) CHARACTER SET utf8 DEFAULT NULL,
  `subcatd` VARCHAR(64) CHARACTER SET utf8 DEFAULT NULL,
  `title` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `tag` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `stamp` INT(11) DEFAULT NULL,
  `filesize` BIGINT(20) DEFAULT '0',
  `moderated` tinyint(1) DEFAULT '0',
  `reversestamp` INT(11) DEFAULT '0',
  `subcatz` VARCHAR(64) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_spots_1` (`messageid`),
  KEY `reversestamp` (`reversestamp`),
  KEY `idx_spots_2` (`stamp`),
  KEY `idx_spots_3` (`reversestamp`),
  KEY `idx_spots_4` (`category`,`subcata`,`subcatb`,`subcatc`,`subcatd`,`subcatz`),
  FULLTEXT KEY `idx_spots_fts_1` (`title`),
  FULLTEXT KEY `idx_spots_fts_2` (`poster`),
  FULLTEXT KEY `idx_spots_fts_3` (`tag`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=385072 ;

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

--
-- Table structure for table `spotsfull`
--

DROP TABLE IF EXISTS `spotsfull`;
CREATE TABLE `spotsfull` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `messageid` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `userid` VARCHAR(32) CHARACTER SET utf8 DEFAULT NULL,
  `verified` tinyint(1) DEFAULT NULL,
  `usersignature` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `userkey` VARCHAR(200) CHARACTER SET utf8 DEFAULT NULL,
  `xmlsignature` VARCHAR(128) CHARACTER SET utf8 DEFAULT NULL,
  `fullxml` text CHARACTER SET utf8,
  `filesize` BIGINT(20) DEFAULT '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_spotsfull_1` (`messageid`),
  FULLTEXT KEY `idx_spotsfull_fts_1` (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=947 ;

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

--
-- Table structure for table `watchlist`
--

DROP TABLE IF EXISTS `watchlist`;
CREATE TABLE `watchlist` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `messageid` VARCHAR(128) CHARACTER SET utf8 NOT NULL,
  `dateadded` INT(11) DEFAULT NULL,
  `comment` text CHARACTER SET utf8 NOT NULL,
  `ouruserid` INT(11) DEFAULT '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_watchlist_1` (`messageid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14 ;