TimeKeeper/timekeeper_sql.php

CREATE TABLE `timekeeper` (
  `username` varchar(255) NOT NULL DEFAULT '',
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  `freeMins` int(10) NOT NULL DEFAULT '0',
  `enableTime` double NOT NULL DEFAULT '0',
  `disableTime` double NOT NULL DEFAULT '0',
  `minQuestionCount` int(11) NOT NULL DEFAULT '0',
  `usedMinutes` int(11) NOT NULL DEFAULT '0',
  `lastDate` int(11) NOT NULL DEFAULT '0',
  `maxDailyPlayTime` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timekeeperPending` (
  `timekeeper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timekeeper_username` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_question` int(10) unsigned DEFAULT '0',
  `timekeeper_answereddatestamp` int(10) unsigned DEFAULT '0',
  `timekeeper_answer` text NOT NULL,
  PRIMARY KEY (`timekeeper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timekeeperQuestions` (
  `timekeeper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timekeeper_subject` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_questiondescription` text NOT NULL,
  `timekeeper_question` longtext NOT NULL,
  `timekeeper_addeddatestamp` int(10) unsigned DEFAULT '0',
  `timekeeper_yearlevel` int(2) unsigned DEFAULT '0',
  `timekeeper_answers` text NOT NULL,
  `timekeeper_correctanswer` int(3) unsigned DEFAULT '0',
  PRIMARY KEY (`timekeeper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timekeeperReport` (
  `timekeeper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timekeeper_username` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_question` text NOT NULL,
  `timekeeper_answereddatestamp` int(10) unsigned DEFAULT '0',
  `timekeeper_userAnswer` text NOT NULL,
  `timekeeper_answer` text NOT NULL,
  `timekeeper_yearLevel` int(2) unsigned DEFAULT '0',
  `timekeeper_subject` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_correct` int(1) unsigned DEFAULT '0',
  PRIMARY KEY (`timekeeper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timekeeperScore` (
  `timekeeper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timekeeper_username` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_lifetimequestions` int(10) unsigned DEFAULT '0',
  `timekeeper_lifetimecorrect` int(10) unsigned DEFAULT '0',
  `timekeeper_minutes` int(10) NOT NULL DEFAULT '0',
  `timekeeper_yearlevel` int(2) unsigned DEFAULT '0',
  `timekeeper_lastquestiondatestamp` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`timekeeper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timekeeperStats` (
  `timekeeper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timekeeper_username` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_subject` varchar(255) NOT NULL DEFAULT '',
  `timekeeper_percent` int(3) unsigned DEFAULT '0',
  `timekeeper_yearLevel` int(2) unsigned DEFAULT '0',
  `timekeeper_quantity` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`timekeeper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timekeeperStatistics` (
  `uniqueId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) NOT NULL DEFAULT '',
  `gameName` varchar(255) NOT NULL DEFAULT '',
  `playDate` int(10) unsigned NOT NULL DEFAULT '0',
  `minutesPlayed` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`uniqueId`),
  KEY `user_game_date` (`userName`,`gameName`,`playDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tkLastQuestion` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` varchar(100) NOT NULL DEFAULT '',
  `LastQuestionHtml` longtext,
  PRIMARY KEY (`id`),
  KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tkPendingAnswer` (
  `username` varchar(100) NOT NULL,
  `token` char(32) NOT NULL,
  `payload` text NOT NULL,
  `created` int(10) unsigned NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tkReport` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `question` text NOT NULL,
  `userAnswer` text NOT NULL,
  `answer` text NOT NULL,
  `correct` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `answereddatestamp` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_subject_date` (`username`,`subject`,`answereddatestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tk_wordList` (
  `indexNo` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(100) NOT NULL,
  `usage` text,
  `year` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`indexNo`),
  KEY `year` (`year`),
  KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tk_countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(100) NOT NULL DEFAULT '',
  `currency` varchar(100) DEFAULT NULL,
  `capital` varchar(100) DEFAULT NULL,
  `isoCode` varchar(3) DEFAULT NULL,
  `difficulty` tinyint(3) unsigned NOT NULL DEFAULT '10',
  `culture` text,
  `continent` varchar(50) DEFAULT NULL,
  `map_coords` text,
  PRIMARY KEY (`id`),
  KEY `continent` (`continent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;