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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
-- 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.

Wish there was more?

I'm considering writing an ebook - click here.

.

Luke is the primary editor of Building Browsergames, and has written a large portion of the articles that you read here. He generally has no idea what to say when asked to write about himself in the third person.

Tags: , , , ,

Thursday, November 13th, 2008 buildingbrowsergames, medieval
  • Mehmet

    After i tried to import your DB export i get a error...

    Fehler (error)

    SQL-Befehl:

    -- -- 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'), -- -----[...]

    MySQL meldet: Dokumentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE IF EXISTS `items`' at line 34

  • Whoops, thanks for catching that Mehmet! It looks like the exporter missed a
    semi-colon - I have updated the post to include the new(and working) code.

blog comments powered by Disqus

About

Building Browsergames is a blog about browsergames(also known as PBBG's). It's geared towards the beginner to intermediate developer who has an interest in building their own browsergame.

Sponsors

Got Something to Say?

Send an e-mail to luke@buildingbrowsergames.com, or get in touch through Twitter at http://twitter.com/bbrowsergames