path: root/changelog/datascheme.sql

-- phpMyAdmin SQL Dump
-- version 4.0.1deb2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 07, 2015 at 05:07 AM
-- Server version: 10.0.2-MariaDB-1~wheezy
-- PHP Version: 5.6.7-1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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 */;

--
-- Database: `betaPathery`
--

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

--
-- Table structure for table `achievements`
--

CREATE TABLE IF NOT EXISTS `achievements` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `type` smallint(6) NOT NULL,
  `level` smallint(6) NOT NULL,
  `notified` tinyint(1) NOT NULL DEFAULT '0',
  `dateCompleted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1788 ;

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

--
-- Table structure for table `challengeMaps`
--

CREATE TABLE IF NOT EXISTS `challengeMaps` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(512) COLLATE latin1_general_ci NOT NULL,
  `name` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `challengeTier` int(11) NOT NULL,
  `challengeSuborder` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `challengeTier` (`challengeTier`,`challengeSuborder`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=25 ;

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

--
-- Table structure for table `challenges`
--

CREATE TABLE IF NOT EXISTS `challenges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `mapID` int(11) NOT NULL,
  `inequality` enum('greater than','less than','equal') COLLATE latin1_general_ci NOT NULL DEFAULT 'greater than' COMMENT 'Whether we want to get more than, less than, or equal to the goal.',
  `goal` int(11) NOT NULL COMMENT 'The amount we want to get for this map',
  `ordering` int(11) NOT NULL COMMENT 'Used to order challenges on the challenge page.  Lower values come first',
  `enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Only enabled challenges will be displayed',
  `hint` varchar(512) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Hint to be given to the player (for the intro puzzles - if hint is null, it won''t be shown)',
  `restrictWallCount` int(11) DEFAULT NULL COMMENT 'Restriction on the (max) number of walls that can be used',
  `restrictWallPlacement` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction that disallows certain places for walls',
  `restrictTeleportCount` int(11) DEFAULT NULL COMMENT 'Restriction on the number of teleports used',
  `restrictTeleportsUsed` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction disallowing the use of certain teleports',
  `restrictStartPoint` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction that the maze must start at the given start-point',
  `restrictEndPoint` varchar(100) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Restriction that the maze must end at the given end point',
  `dialogStart` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `dialogFail` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `dialogSuccess` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `mapID` (`mapID`,`enabled`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=31 ;

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

--
-- Table structure for table `challengeSolutions`
--

CREATE TABLE IF NOT EXISTS `challengeSolutions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `challengeID` int(11) NOT NULL,
  `solution` varchar(512) COLLATE latin1_general_ci NOT NULL,
  `moves` int(11) NOT NULL,
  `dateSolved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `userID_challengeID` (`userID`,`challengeID`),
  KEY `challengeID` (`challengeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=793 ;

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

--
-- Table structure for table `chat`
--

CREATE TABLE IF NOT EXISTS `chat` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) DEFAULT NULL,
  `message` varchar(511) COLLATE latin1_general_ci NOT NULL,
  `dateSent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `channel` varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=16172 ;

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

--
-- Table structure for table `chatUsers`
--

CREATE TABLE IF NOT EXISTS `chatUsers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `channel` int(11) NOT NULL,
  `client` varchar(16) NOT NULL,
  `dateEntered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dateLastActive` timestamp NULL DEFAULT NULL,
  `isHere` tinyint(1) NOT NULL,
  `isAdmin` tinyint(1) NOT NULL,
  `isMod` tinyint(1) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `userID and channel` (`userID`,`channel`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2688743 ;

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

--
-- Table structure for table `emailQueue`
--

CREATE TABLE IF NOT EXISTS `emailQueue` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `emailID` int(11) NOT NULL,
  `priority` tinyint(4) NOT NULL DEFAULT '100',
  PRIMARY KEY (`ID`),
  KEY `emailID` (`emailID`,`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `emails`
--

CREATE TABLE IF NOT EXISTS `emails` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `fromUserID` int(11) DEFAULT NULL,
  `to` varchar(512) COLLATE latin1_general_ci NOT NULL,
  `subject` varchar(512) COLLATE latin1_general_ci NOT NULL,
  `body` mediumtext COLLATE latin1_general_ci NOT NULL,
  `dateSubmited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dateSent` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `error` varchar(512) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `fromUserID` (`fromUserID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=1 COMMENT='Queued emails' AUTO_INCREMENT=298 ;

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

--
-- Table structure for table `mapOfTheDay`
--

CREATE TABLE IF NOT EXISTS `mapOfTheDay` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `mapID` int(11) NOT NULL,
  `mapType` tinyint(4) NOT NULL,
  `mapDate` date NOT NULL,
  `mapExpireTime` datetime NOT NULL,
  `championPointsWorth` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UniqueTypeDate` (`mapDate`,`mapType`),
  KEY `mapExpireTime` (`mapExpireTime`,`mapType`),
  KEY `mapID` (`mapID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4716 ;

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

--
-- Table structure for table `maps`
--

CREATE TABLE IF NOT EXISTS `maps` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(512) COLLATE latin1_general_ci NOT NULL,
  `name` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
  `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4859 ;

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

--
-- Table structure for table `matches`
--

CREATE TABLE IF NOT EXISTS `matches` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `creatorUserID` int(11) NOT NULL,
  `mapID` int(11) DEFAULT NULL,
  `isComplete` tinyint(1) NOT NULL DEFAULT '0',
  `isStarted` tinyint(1) NOT NULL DEFAULT '0',
  `dateCreated` timestamp NULL DEFAULT NULL,
  `dateExpires` timestamp NULL DEFAULT NULL,
  `dateStarted` timestamp NULL DEFAULT NULL,
  `requiredPlayers` smallint(6) NOT NULL,
  `secondsGiven` int(11) NOT NULL,
  `useSmartTime` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

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

--
-- Table structure for table `matchUsers`
--

CREATE TABLE IF NOT EXISTS `matchUsers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `matchID` int(11) NOT NULL,
  `isReady` tinyint(1) NOT NULL DEFAULT '0',
  `dateLastChecked` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

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

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

CREATE TABLE IF NOT EXISTS `settings` (
  `name` varchar(16) COLLATE latin1_general_ci NOT NULL,
  `value` varchar(128) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

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

--
-- Table structure for table `solutions`
--

CREATE TABLE IF NOT EXISTS `solutions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `mapID` int(11) NOT NULL,
  `solution` varchar(4095) COLLATE latin1_general_ci NOT NULL,
  `moves` int(11) NOT NULL,
  `dateModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `championPointsWorth` int(11) NOT NULL DEFAULT '0',
  `isHighScore` tinyint(1) NOT NULL,
  `isTiedForHighScore` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `userIDmapID` (`userID`,`mapID`),
  KEY `mapIDMoves` (`mapID`,`moves`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=35003 ;

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

--
-- Table structure for table `unlocks`
--

CREATE TABLE IF NOT EXISTS `unlocks` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `achievementID` int(11) NOT NULL,
  `type` smallint(6) NOT NULL,
  `subtype` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `value` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `dateUnlocked` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1788 ;

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `openID` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `email` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `displayName` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `dateLogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dateJoined` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `isAdmin` tinyint(1) NOT NULL DEFAULT '0',
  `challengeTier` int(11) NOT NULL,
  `championPoints` int(11) NOT NULL,
  `totalWins` int(11) NOT NULL DEFAULT '0',
  `totalTies` int(11) NOT NULL DEFAULT '0',
  `totalMazes` int(11) NOT NULL DEFAULT '0',
  `totalMoves` int(11) NOT NULL DEFAULT '0',
  `displayColor` varchar(7) COLLATE latin1_general_ci NOT NULL DEFAULT '#cccccc',
  `wallColor` varchar(7) COLLATE latin1_general_ci NOT NULL DEFAULT '#666666',
  `wallEmblem` varchar(64) COLLATE latin1_general_ci NOT NULL DEFAULT 'blank.png',
  `wallOrientation` tinyint(4) NOT NULL DEFAULT '0',
  `dateChatGaggedUntil` datetime DEFAULT NULL,
  `isOptedOutOfEmails` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `openID` (`openID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=125 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `achievements`
--
ALTER TABLE `achievements`
  ADD CONSTRAINT `achievements_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `challenges`
--
ALTER TABLE `challenges`
  ADD CONSTRAINT `challenges_ibfk_1` FOREIGN KEY (`mapID`) REFERENCES `challengeMaps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `challenges_ibfk_2` FOREIGN KEY (`mapID`) REFERENCES `challengeMaps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `challengeSolutions`
--
ALTER TABLE `challengeSolutions`
  ADD CONSTRAINT `challengeSolutions_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `challengeSolutions_ibfk_2` FOREIGN KEY (`challengeID`) REFERENCES `challenges` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `challengeSolutions_ibfk_3` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `challengeSolutions_ibfk_4` FOREIGN KEY (`challengeID`) REFERENCES `challenges` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `chat`
--
ALTER TABLE `chat`
  ADD CONSTRAINT `chat_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `emailQueue`
--
ALTER TABLE `emailQueue`
  ADD CONSTRAINT `emailQueue_ibfk_1` FOREIGN KEY (`emailID`) REFERENCES `emails` (`ID`) ON UPDATE CASCADE;

--
-- Constraints for table `emails`
--
ALTER TABLE `emails`
  ADD CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`fromUserID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `mapOfTheDay`
--
ALTER TABLE `mapOfTheDay`
  ADD CONSTRAINT `mapOfTheDay_ibfk_1` FOREIGN KEY (`mapID`) REFERENCES `maps` (`ID`) ON UPDATE CASCADE;

--
-- Constraints for table `solutions`
--
ALTER TABLE `solutions`
  ADD CONSTRAINT `solutions_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `solutions_ibfk_2` FOREIGN KEY (`mapID`) REFERENCES `maps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `unlocks`
--
ALTER TABLE `unlocks`
  ADD CONSTRAINT `unlocks_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!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 */;