· 9 years ago · Nov 22, 2016, 11:52 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(30) 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(30) 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(30) NOT NULL,
35 Description VARCHAR(500) NOT NULL,
36 ImagePath VARCHAR(500) NOT NULL,
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(30),
46 KeyItem INTEGER,
47
48 # Setting primary key.
49 PRIMARY KEY (RoomID, ID),
50
51 # Setting foreign keys.
52 FOREIGN KEY (NPCID) REFERENCES NPC (ID),
53 FOREIGN KEY (KeyItem) REFERENCES Item (ID));
54
55# Creating Room-table.
56CREATE TABLE Room (
57 ID INTEGER NOT NULL,
58 RoomStateID INTEGER NOT NULL,
59 MaxStates INTEGER NOT NULL,
60
61 # Setting primary key.
62 PRIMARY KEY (ID),
63
64 # Setting foreign keys.
65 FOREIGN KEY (ID, RoomStateID) REFERENCES RoomState (RoomID, ID));
66
67# Creating Player-table.
68CREATE TABLE Player (
69 ID INTEGER NOT NULL,
70 RoomID INTEGER NOT NULL,
71
72 # Setting primary key.
73 PRIMARY KEY (ID),
74
75 # Setting foreign key.
76 FOREIGN KEY (RoomID) REFERENCES Room (ID));
77
78# Inserting data into NPC-table.
79INSERT INTO NPC
80 VALUES(1, "The Captain", null, "Well, that was a spicy meatball. Well, back to work.");
81INSERT INTO NPC
82 VALUES(2, "Pmurt the Martian", "Wants to build protective bubble around the Earth and run for overlord of the galaxy.",
83 "No illegal migrants in this galaxy! We are making Mars great again! 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! "
84 "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! "
85 "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! "
86 "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?");
87INSERT INTO NPC
88 VALUES(3, "Harambe", "Scout kid!", "Rrrrraarrrgggh!");
89
90# Inserting data into Item-table.
91INSERT INTO Item
92 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);
93INSERT INTO Item
94 VALUES(2, "Dashboard", "The dashboard is flashing red with unintelligible terms. By swiping left, the dashboard asks for an override code.", 0, 0);
95INSERT INTO Item
96 VALUES(3, "Dashboard", "The flashing stops and the dashboard allows you to unlock the bridge doors.", 0, 0);
97INSERT INTO Item
98 VALUES(4, "Wet mop", "Space or no space, mops are a vital part of a janitor’s toolset.", 1, 0);
99INSERT INTO Item
100 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);
101INSERT INTO Item
102 VALUES(6, "Clean wall", "There, much better. A clean ship is a more comfortable ship for everyone.", 0, 0);
103INSERT INTO Item
104 VALUES(7, "Gooped keypad", "The keypad covered in goop seems to be of very little use.", 0, 0);
105INSERT INTO Item
106 VALUES(8, "Keybad", "It’s an elevator keypad. Pushing it opens the elevator.", 0, 0);
107INSERT INTO Item
108 VALUES(9, "Soap", "It’s a bar of soap.", 1, 0);
109INSERT INTO Item
110 VALUES(10, "Bucket", "It’s a standard issue Earth Federation bucket made out of galactic plastic.", 1, 0);
111INSERT INTO Item
112 VALUES(11, "Water tap", "It’s a water tap. What do you expect?", 0, 0);
113INSERT INTO Item
114 VALUES(12, "Bucket with water", "It’s a bucket with clear water.", 1, 0);
115INSERT INTO Item
116 VALUES(13, "Bucket with water and soup", "Don’t drink this. Soapy water isn’t good for you.", 1, 0);
117INSERT INTO Item
118 VALUES(14, "Vomit", "It’s a very disgusting looking pile of vomit. You wish someone else were the janitor here.", 0, 0);
119INSERT INTO Item
120 VALUES(15, "Picture", "It’s a very soothing picture of a gorilla. The letter H is engraved in gold on it.", 0, 0);
121INSERT INTO Item
122 VALUES(16, "Bag of crisps", "It’s a bag of crisps, the king of comfort food.", 1, 0);
123INSERT INTO Item
124 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);
125INSERT INTO Item
126 VALUES(18, "Window", "Based on the window view, you’re very close to Uranus.", 0, 0);
127
128# Inserting data into RoomState-table.
129INSERT INTO RoomState
130 VALUES(RoomID, ID, Name, Description, ImagePath, North, East, South, West, Item1, Item2, Item3, NPCID, Keyword, KeyItem);
131
132# Inserting data into Room-table.