· 9 years ago · Nov 23, 2016, 11:46 AM
1DROP DATABASE IF EXISTS Space;
2
3# Creating new database.
4CREATE DATABASE Space;
5
6# Using database.
7USE Space;
8
9# Creating NPC-table.
10CREATE TABLE NPC (
11 ID INTEGER NOT NULL,
12 Name VARCHAR(50) NOT NULL,
13 Description VARCHAR(500),
14 Dialogues VARCHAR(1000) NOT NULL,
15
16 # Setting primary key.
17 PRIMARY KEY (ID));
18
19# Creating Item-table.
20CREATE TABLE Item (
21 ID INTEGER NOT NULL,
22 Name VARCHAR(50) NOT NULL,
23 Description VARCHAR(500) NOT NULL,
24 Interactive INTEGER NOT NULL,
25 Ownership INTEGER NOT NULL,
26
27 # Setting primary key.
28 PRIMARY KEY (ID));
29
30# Creating RoomState-table.
31CREATE TABLE RoomState (
32 RoomID INTEGER NOT NULL,
33 ID INTEGER NOT NULL,
34 Name VARCHAR(50) NOT NULL,
35 Description VARCHAR(500) NOT NULL,
36 ImagePath VARCHAR(500),
37 North INTEGER,
38 East INTEGER,
39 South INTEGER,
40 West INTEGER,
41 Item1 INTEGER,
42 Item2 INTEGER,
43 Item3 INTEGER,
44 NPCID INTEGER,
45 Keyword VARCHAR(50),
46 KeyItem INTEGER,
47
48 # Setting primary key.
49 PRIMARY KEY (RoomID, ID),
50
51 # Setting foreign keys.
52 FOREIGN KEY (Item1) REFERENCES Item (ID),
53 FOREIGN KEY (Item2) REFERENCES Item (ID),
54 FOREIGN KEY (Item3) REFERENCES Item (ID),
55 FOREIGN KEY (NPCID) REFERENCES NPC (ID),
56 FOREIGN KEY (KeyItem) REFERENCES Item (ID));
57
58# Creating Room-table.
59CREATE TABLE Room (
60 ID INTEGER NOT NULL,
61 RoomStateID INTEGER NOT NULL,
62 MaxStates INTEGER NOT NULL,
63
64 # Setting primary key.
65 PRIMARY KEY (ID),
66
67 # Setting foreign keys.
68 FOREIGN KEY (ID, RoomStateID) REFERENCES RoomState (RoomID, ID));
69
70# Creating Player-table.
71CREATE TABLE Player (
72 ID INTEGER NOT NULL,
73 RoomID INTEGER NOT NULL,
74
75 # Setting primary key.
76 PRIMARY KEY (ID),
77
78 # Setting foreign key.
79 FOREIGN KEY (RoomID) REFERENCES Room (ID));
80
81# Inserting data into NPC-table. (ID, Name, Description, Dialogues)
82INSERT INTO NPC
83 VALUES(1, "The Captain", null, "Well, that was a spicy meatball. Well, back to work.");
84INSERT INTO NPC
85 VALUES(2, "Pmurt the Martian", "Wants to build protective bubble around the Earth and run for overlord of the galaxy.", "No illegal migrants in this galaxy! We are making Mars great again! "
86 "After Pmurt fulfills his plans to have a 55 feet thick protective bubble around the Earth, he will be elected as the new overlord of the galaxy! "
87 "No more illegal weak, sick and deceiving earthlings with their insecure e-mails! And want to hear the best part of it? YOU ARE GOING TO PAY THE WHOLE FREAKING BUBBLE! "
88 "Pmurt would kill you otherwise, but it would be way too much paperwork for Pmurt and his small hands to handle. NOT EVEN THE ONE WHO LOVED US ALL CAN SAVE YOU PESKY HUMANS NOW! "
89 "Bob: Oh dear… You need a miracle to go past this Pmurt guy… But miracles only happen in movies and games like this… Maybe you could give it a shot and start praying?");
90INSERT INTO NPC
91 VALUES(3, "Harambe", "Scout kid!", "Rrrrraarrrgggh!");
92
93# Inserting data into Item-table. (ID, Name, Description, Interactive, Ownership)
94INSERT INTO Item
95 VALUES(1, "Paper note", "Welcome to Universe Mission. This is the last ditch effort of mankind. As you are aware, captain, the emergency override code for the dashboard is 1234.", 1, 0);
96INSERT INTO Item
97 VALUES(2, "Dashboard", "The dashboard is flashing red with unintelligible terms. By swiping left, the dashboard asks for an override code.", 0, 0);
98INSERT INTO Item
99 VALUES(3, "Dashboard", "The flashing stops and the dashboard allows you to unlock the bridge doors.", 0, 0);
100INSERT INTO Item
101 VALUES(4, "Wet mop", "Space or no space, mops are a vital part of a janitor’s toolset.", 1, 0);
102INSERT INTO Item
103 VALUES(5, "Graffiti", "“MAKE MARS GREAT AGAIN!†The 25th century has its own set of vandals. By the looks of it, the graffiti is made using a red marker pen.", 0, 0);
104INSERT INTO Item
105 VALUES(6, "Clean wall", "There, much better. A clean ship is a more comfortable ship for everyone.", 0, 0);
106INSERT INTO Item
107 VALUES(7, "Gooped keypad", "The keypad covered in goop seems to be of very little use.", 0, 0);
108INSERT INTO Item
109 VALUES(8, "Keybad", "It’s an elevator keypad. Pushing it opens the elevator.", 0, 0);
110INSERT INTO Item
111 VALUES(9, "Soap", "It’s a bar of soap.", 1, 0);
112INSERT INTO Item
113 VALUES(10, "Bucket", "It’s a standard issue Earth Federation bucket made out of galactic plastic.", 1, 0);
114INSERT INTO Item
115 VALUES(11, "Water tap", "It’s a water tap. What do you expect?", 0, 0);
116INSERT INTO Item
117 VALUES(12, "Bucket with water", "It’s a bucket with clear water.", 1, 0);
118INSERT INTO Item
119 VALUES(13, "Bucket with water and soup", "Don’t drink this. Soapy water isn’t good for you.", 1, 0);
120INSERT INTO Item
121 VALUES(14, "Vomit", "It’s a very disgusting looking pile of vomit. You wish someone else were the janitor here.", 0, 0);
122INSERT INTO Item
123 VALUES(15, "Picture", "It’s a very soothing picture of a gorilla. The letter H is engraved in gold on it.", 0, 0);
124INSERT INTO Item
125 VALUES(16, "Bag of crisps", "It’s a bag of crisps, the king of comfort food.", 1, 0);
126INSERT INTO Item
127 VALUES(17, "Master key", "It’s a master key. Hindsight 20/20 but maybe you should’ve had this with you all along.", 1, 0);
128INSERT INTO Item
129 VALUES(18, "Window", "Based on the window view, you’re very close to Uranus.", 0, 0);
130INSERT INTO Item
131 VALUES(19, "Elevator button 1", "It's a button to the Bridge", 0, 0);
132INSERT INTO Item
133 VALUES(20, "Elevator button 2", "It's a button to the Engineering", 0, 0);
134INSERT INTO Item
135 VALUES(21, "Elevator button 42", "It's a button to the Deck 42", 0, 0);
136
137# Inserting data into RoomState-table. (RoomID, ID, Name, Description, ImagePath, North, East, South, West, Item1, Item2, Item3, NPCID, Keyword, KeyItem)
138INSERT INTO RoomState
139 VALUES(1, 1, "The Bridge", "As you enter the bridge, I’m struck by a sudden dread. The bridge is that of your average Federation warship. "
140 "Golden decorations, posters of Miss Multiverse 2421 and video games. But why is the bridge empty? Soon after, the emergency lockdown starts for no apparent reason. "
141 "As I just cleaned the place, you only need to get out… no harm just disabling the emergency lockdown, right?", null, null, null, null, null, 1, 2, null, null, null, null);
142INSERT INTO RoomState
143 VALUES(1, 2, "The Bridge", "There, much better. No flashing red lights. But now the double-double bacon burger with cheese you had for lunch is coming out fast and is its own emergency. "
144 "Where’s the bathroom?", null, null, null, 2, null, 3, null, null, null);
145INSERT INTO RoomState
146 VALUES(2, 1, "Dirty Corridor", "Wait, what is this? This corridor was cleaned 30 minutes ago. Now someone has already ruined the walls with graffiti.", null, 1, null, 3, null, 4, 5, null, null,
147 null, null);
148INSERT INTO RoomState
149 VALUES(2, 2, "Dirty Corridor", "Wait, what is this? This corridor was cleaned 30 minutes ago. Now someone has already ruined the walls with graffiti.", null, 1, null, 3, null, 5, null, null,
150 null, null, null);
151INSERT INTO RoomState
152 VALUES(2, 3, "Just a plain Corridor", "Clean corridors are a janitor’s best friend. You actually aren’t paid to do this but it soothes his soul nonetheless.", null, 1, null, 3, null, 6, null,
153 null, null, null, null);
154INSERT INTO RoomState
155 VALUES(3, 1, "Stinky Elevator Corridor", "Oh science, this smell! You can’t even think straight…", null, 2, 4, null, null, 7, null, null, null, null, null);
156INSERT INTO RoomState
157 VALUES(3, 2, "Elevator Corridor", "Cleaning the yellow goop didn’t help much but at least there is a way out of the stinky corridor.", null, 2, 4, null, 5, 8, null, null, null, null, null);
158INSERT INTO RoomState
159 VALUES(4, 1, "The Broom Closet", "A broom closet in 25th century looks largely the same as a broom closet in the 21st century. It’s largely an unassuming place. "
160 "On the floor there’s a bucket and soap. There’s also a water tap.", null, null, null, null, 3, 9, 10, 11, null, null, null);
161INSERT INTO RoomState
162 VALUES(4, 2, "The Broom Closet", "A broom closet in 25th century looks largely the same as a broom closet in the 21st century. It’s largely an unassuming place. On the floor there’s soap. "
163 "There’s also a water tap.", null, null, null, null, 3, 9, 11, null, null, null, null);
164INSERT INTO RoomState
165 VALUES(4, 3, "The Broom Closet", "A broom closet in 25th century looks largely the same as a broom closet in the 21st century. It’s largely an unassuming place. On the floor there’s a bucket. "
166 "There’s also a water tap.", null, null, null, null, 3, 10, 11, null, null, null, null);
167INSERT INTO RoomState
168 VALUES(4, 4, "The Broom Closet", "A broom closet in 25th century looks largely the same as a broom closet in the 21st century. It’s largely an unassuming place. There’s also a water tap.", null,
169 null, null, null, 3, 11, null, null, null, null, null);
170INSERT INTO RoomState
171 VALUES(5, 1, "The Elevator", "The elevator seems like the only clean place so far. There’s a control panel with three buttons. "
172 "One of them is for the bridge, one of them is for the engine room and the third one is for deck 42. A curious amount of buttons for a ship with 50 decks.", null, null, 3, null, null, 19,
173 20, 21, null, null, null);
174INSERT INTO RoomState
175 VALUES(5, 2, "The Elevator", "The elevator sits still. The door is open to the south.", null, null, 6, null, null, 19, 20, 21, null, null, null);
176INSERT INTO RoomState
177 VALUES(6, 1, "Bathroom Corridor", "As you step out of the elevator, the keypad to open it sparks and stops functioning. The corridor looks largely the same as the corridor upstairs. "
178 "There’s a locked bathroom door to the east and the corridor continues north.", null, 7, 10, null, null, null, null, null, null, null, null);
179INSERT INTO RoomState
180 VALUES(6, 2, "Bathroom Corridor", "A faint whiff of a toilet fills the air. There’s a bathroom door to the east and the corridor continues north.", null, 7, 10, null, null, null, null, null,
181 null, null, null);
182INSERT INTO RoomState
183 VALUES(7, 1, "Disgusting Corridor", "Oh boy. This place is quite the mess. There’s vomit on the window to space. It stinks and blocks the beautiful view.", null, 8, null, 6, null, 14, null, null,
184 null, null, null);
185INSERT INTO RoomState
186 VALUES(7, 2, "Just another Corridor", "Another cleaned corridor. The window shows a very beautiful view of the space beyond.", null, 8, null, 6, null, 18, null, null, null, null, null);
187INSERT INTO RoomState
188 VALUES(8, 1, "Living Quarters", "The living quarters have always been a miserably sterile place. As a janitor, you enjoy the cleanliness of the place but otherwise the place bores you to tears. "
189 "This time, though, there’s an unusual sight to see. A green creature is guarding the lobby. "
190 "Another more interesting addition is a picture of a gorilla on the lobby table with the letter H on it.", null, null, null, 7, null, 15, null, null, 2, null, null);
191INSERT INTO RoomState
192 VALUES(8, 2, "Living Quarters", "The living quarters have always been a miserably sterile place. As a janitor, you enjoy the cleanliness of the place but otherwise the place bores you to tears. "
193 "A strangely unassuming gorilla looks at you.", null, null, 9, 7, null, 15, null, null, 3, null, null);
194INSERT INTO RoomState
195 VALUES(9, 1, "Janitor's Room", "Ah, home sweet home. This is your castle. It has all the things a man needs, a bag of emergency crisps, a picture of a dog and a desk. "
196 "The desk has all of your important things including a master key.", null, null, null, 8, null, 16, 17, null, null, null, null);
197INSERT INTO RoomState
198 VALUES(9, 2, "Janitor's Room", "Ah, home sweet home. This is your castle. There's a picture of a dog and a desk. ", null, null, null, 8, null, 16, null, null, null, null, null);
199INSERT INTO RoomState
200 VALUES(9, 3, "Janitor's Room", "Ah, home sweet home. This is your castle. There's a picture of a dog and a desk. The desk has all of your important things including a master key.", null, null,
201 null, 8, null, 17, null, null, null, null, null);
202INSERT INTO RoomState
203 VALUES(9, 4, "Janitor's Room", "After emptying your room of all valuables, you don’t think there’s much here.", null, null, null, 8, null, null, null, null, null, null, null);
204INSERT INTO RoomState
205 VALUES(10, 1, "The Bathroom", "The smell of this room reminds you of a toilet. Maybe this is because that is what the room is. All of the stalls are locked. All you can do is wait.", null, null,
206 null, null, 6, null, null, null, null, null, null);
207INSERT INTO RoomState
208 VALUES(10, 2, "The Bathroom", "The smell of this room reminds you of a toilet. Maybe this is because that is what the room is. All of the stalls are locked. All you can do is wait.", null, null,
209 null, null, 6, null, null, null, null, null, null);
210INSERT INTO RoomState
211 VALUES(10, 3, "The Bathroom", "GAME ENDS", null, null, null, null, 6, null, null, null, 1, null, null);
212
213# Inserting data into Room-table. (ID, RoomStateID, MaxStates)
214INSERT INTO Room
215 VALUES(1, 1, 2);
216INSERT INTO Room
217 VALUES(2, 1, 3);
218INSERT INTO Room
219 VALUES(3, 1, 2);
220INSERT INTO Room
221 VALUES(4, 1, 4);
222INSERT INTO Room
223 VALUES(5, 1, 2);
224INSERT INTO Room
225 VALUES(6, 1, 2);
226INSERT INTO Room
227 VALUES(7, 1, 2);
228INSERT INTO Room
229 VALUES(8, 1, 2);
230INSERT INTO Room
231 VALUES(9, 1, 4);
232INSERT INTO Room
233 VALUES(10, 1, 3);
234
235# Inserting data into Player-table. (ID, RoomID)
236INSERT INTO Player
237 VALUES(1, 1);