Our current database structure
As we’ve been working through our tutorial, our database has undergone a lot of different changes. Sometimes columns were added to tables to accommodate new features, and sometimes tables were removed entirely. At any rate, it is clear to see that as our game has grown more complex, so has our database structure.
For someone who has been following along since the beginning, this isn’t quite as much of an issue: your database will have changed along each step of the tutorial. But for someone who has been using Google Code checkouts, you don’t necessarily know how the database has changed – which makes it a bit of work to re-create.
With that said, here is a dump of our entire database currently – which you can import with phpmyadmin or the MySQL command-line client, to build a copy of our current (working) database:
-- phpMyAdmin SQL Dump
-- version 2.10.3deb1ubuntu0.2
-- http://www.phpmyadmin.net
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Table structure for table `entity_stats`
--
DROP TABLE IF EXISTS `entity_stats`;
CREATE TABLE IF NOT EXISTS `entity_stats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stat_id` int(11) DEFAULT NULL,
`entity_id` int(11) DEFAULT NULL,
`value` text,
`entity_type` enum('User','Monster','Item') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;
--
-- Dumping data for table `entity_stats`
--
INSERT INTO `entity_stats` (`id`, `stat_id`, `entity_id`, `value`, `entity_type`) VALUES
(1, 2, 1, '2', 'Monster'),
(2, 3, 1, '2', 'Monster'),
(3, 5, 1, '8', 'Monster'),
(4, 2, 2, '1', 'Monster'),
(5, 3, 2, '0', 'Monster'),
(6, 5, 2, '4', 'Monster'),
(7, 2, 3, '4', 'Monster'),
(8, 3, 3, '3', 'Monster'),
(9, 5, 3, '10', 'Monster'),
(10, 4, 1, '5', 'Monster'),
(11, 4, 2, '20', 'Monster'),
(12, 4, 3, '5', 'Monster'),
(13, 2, 1, '2', 'Item'),
(14, 17, 13, 'ahead', 'Item'),
(15, 17, 14, 'atorso', 'Item'),
(16, 17, 15, 'alegs', 'Item'),
(17, 17, 16, 'aright', 'Item'),
(18, 17, 17, 'aleft', 'Item'),
(19, 17, 0, '0', 'Item'),
(20, 2, 7, '0', 'Item'),
(21, 2, 0, '0', 'Item'),
(22, 18, 20, 'potion', 'Item'),
(23, 18, 21, 'crystal_ball', 'Item'),
(24, 3, 0, '0', 'Item'),
(25, 18, 4, '0', 'Item'),
(26, 18, 2, '0', 'Item'),
(27, 2, 4, '0', 'Item'),
(28, 2, 2, '0', 'Item'),
(29, 2, 5, '0', 'Item');
-- --------------------------------------------------------
--
-- Table structure for table `items`
--
DROP TABLE IF EXISTS `items`;
CREATE TABLE IF NOT EXISTS `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
`type` enum('Weapon','Armor','Usable','Foo') DEFAULT NULL,
`price` int(11) NOT NULL DEFAULT '10',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
--
-- Dumping data for table `items`
--
INSERT INTO `items` (`id`, `name`, `type`, `price`) VALUES
(1, 'Wooden Sword', 'Weapon', 10),
(2, 'Flail', 'Weapon', 15),
(3, 'Mace', 'Weapon', 10),
(4, 'Pizza Slicer', 'Weapon', 10),
(5, 'Bow and Arrows', 'Weapon', 10),
(6, 'Rolling Pin', 'Weapon', 10),
(7, 'Dagger', 'Weapon', 10),
(16, 'Sample Right Arm', 'Armor', 10),
(15, 'Sample Legs', 'Armor', 10),
(14, 'Sample Torso', 'Armor', 10),
(13, 'Sample Helmet', 'Armor', 10),
(17, 'Sample Left Arm', 'Armor', 10),
(21, 'Crystal Ball', 'Usable', 10),
(20, 'Red Potion', 'Usable', 10);
-- --------------------------------------------------------
--
-- Table structure for table `monsters`
--
DROP TABLE IF EXISTS `monsters`;
CREATE TABLE IF NOT EXISTS `monsters` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `monsters`
--
INSERT INTO `monsters` (`id`, `name`) VALUES
(1, 'Crazy Eric'),
(2, 'Lazy Russell'),
(3, 'Hard Hitting Louis');
-- --------------------------------------------------------
--
-- Table structure for table `monster_items`
--
DROP TABLE IF EXISTS `monster_items`;
CREATE TABLE IF NOT EXISTS `monster_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`monster_id` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`rarity` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `monster_items`
--
INSERT INTO `monster_items` (`id`, `monster_id`, `item_id`, `rarity`) VALUES
(1, 1, 1, 100),
(2, 2, 2, 100),
(3, 3, 4, 100),
(4, 4, 5, 100);
-- --------------------------------------------------------
--
-- Table structure for table `stats`
--
DROP TABLE IF EXISTS `stats`;
CREATE TABLE IF NOT EXISTS `stats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`display_name` text,
`short_name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
--
-- Dumping data for table `stats`
--
INSERT INTO `stats` (`id`, `display_name`, `short_name`) VALUES
(1, 'Magic', 'mag'),
(2, 'Attack', 'atk'),
(3, 'Defence', 'def'),
(4, 'Gold', 'gc'),
(5, 'Maximum HP', 'maxhp'),
(6, 'Current HP', 'curhp'),
(7, 'Set Default HP Values', 'sethp'),
(8, 'Gold In Bank', 'bankgc'),
(9, 'Password Reset - hash vulnerability', 'pwrd'),
(10, 'Primary Hand Weapon', 'phand'),
(11, 'Secondary Hand Weapon', 'shand'),
(12, 'Armor - Head', 'ahead'),
(13, 'Armor - Torso', 'atorso'),
(14, 'Armor - Legs', 'alegs'),
(15, 'Armor - Right Arm', 'aright'),
(16, 'Armor - Left Arm', 'aleft'),
(17, 'Item Armor Slot', 'aslot'),
(18, 'Item Use Token', 'token');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(250) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`is_admin` tinyint(1) NOT NULL DEFAULT '0',
`last_login` timestamp NULL DEFAULT NULL,
`email` text NOT NULL,
`confirmed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `user_items`
--
DROP TABLE IF EXISTS `user_items`;
CREATE TABLE IF NOT EXISTS `user_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
While this dump is relatively useless for people working with a framework like Rails or Django, it will help those of you who are using Perl or PHP to build a game – and therefore don’t have your database generated for you by your framework.
One of the benefits of this dump is that it’s pristine – there are no user accounts or user inventories being stored. It also includes all of the ‘DROP TABLE’ statements at the beginning – so if you want to ‘reset’ your game, you can easily do so by just re-running this SQL.