UzXN3Yi3

1DROP TABLE IF EXISTS Passage;
2DROP TABLE IF EXISTS Direction;
3DROP TABLE IF EXISTS Enemy;
4DROP TABLE IF EXISTS Item;
5DROP TABLE IF EXISTS Itemtype;
6DROP TABLE IF EXISTS Hero;
7DROP TABLE IF EXISTS Location;
8DROP TABLE IF EXISTS Helptable;
9CREATE TABLE Location (
10	ID Varchar(10) NOT NULL,
11	DESCRIPTION VARCHAR(100),
12	DETAILS VARCHAR(1000),
13	PRIMARY KEY (ID)
14);
15CREATE TABLE Hero (
16	ID INT NOT NULL,
17	Name VARCHAR(40) NOT NULL,
18	Health VARCHAR(40) NOT NULL,
19	Damage INT NOT NULL,
20	locID Varchar(10) NOT NULL,
21	PRIMARY KEY (ID),
22	FOREIGN KEY (locID) REFERENCES Location(ID)
23);
24CREATE TABLE Itemtype (
25	ID VARCHAR(10) NOT NULL,
26	Name VARCHAR(40) NOT NULL,
27	Damage INT,
28	Healing INT,
29	PRIMARY KEY (ID)
30);
31CREATE TABLE Item ( 
32	ID VARCHAR(10) NOT NULL,
33	Description VARCHAR(100),
34	Details VARCHAR(1000),
35	ItemtypeId Varchar(10) NOT NULL,
36	LocId VARCHAR(10),
37	Available BOOLEAN,
38	Takeable BOOLEAN,
39	PRIMARY KEY (ID),
40	FOREIGN KEY (ItemtypeId) REFERENCES Itemtype(ID),
41	FOREIGN KEY (LocId) REFERENCES Location(ID)
42	
43);
44CREATE TABLE Enemy (
45	EnemyID VARCHAR(20) NOT NULL,
46	Health VARCHAR(10),
47	Name VARCHAR(10) NOT NULL,
48	Damage INT NOT NULL,
49	locID Varchar(10) NULL,
50	FOREIGN KEY (locID) REFERENCES Location(ID),
51	PRIMARY KEY(EnemyID)
52);
53CREATE TABLE DIRECTION (
54	Id Varchar(10) NOT NULL,
55	Description VARCHAR(40),
56	PRIMARY KEY (ID)
57
58);
59CREATE TABLE PASSAGE (
60	Id VARCHAR(10) NOT NULL,
61	Source Varchar(10),
62	Destination Varchar(10),
63	Direction VARCHAR(10),
64	Locked BOOLEAN,
65	Locknote VARCHAR(80),
66	PRIMARY KEY (Id),
67	FOREIGN KEY (Source) REFERENCES Location(Id),
68	FOREIGN KEY (Destination) REFERENCES Location(Id),
69	FOREIGN KEY (Direction) REFERENCES Direction(Id)
70);
71CREATE TABLE Helptable (
72    Information VARCHAR (1000),
73    Description VARCHAR (1000)
74);
75
76
77
78DROP USER IF EXISTS dbuser@localhost;
79CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbpass';
80GRANT SELECT, INSERT, UPDATE, DELETE ON androdb.* TO dbuser@localhost;
81
82
83INSERT INTO LOCATION VALUES ('PLAYER', 'Player', '');
84INSERT INTO LOCATION VALUES ('START', 'The starting cabin.','');
85INSERT INTO LOCATION VALUES ('CAPTQ', 'Captain´s Quarters.','');
86INSERT INTO LOCATION VALUES ('CAFE', 'The Ship´s cafe.','');
87INSERT INTO LOCATION VALUES ('LOCKER', 'Storage and Locker room.','');
88INSERT INTO LOCATION VALUES ('BRIDGE', 'The Bridge.', '');
89INSERT INTO LOCATION VALUES ('OFFICE', 'Captain´s Office.', '');
90INSERT INTO LOCATION VALUES ('ELV1', 'Elevator Level 1', '');
91INSERT INTO LOCATION VALUES ('ELV2', 'Elevator Level 2', '');
92INSERT INTO LOCATION VALUES ('ELV3', 'Elevator Level 3', '');
93INSERT INTO LOCATION VALUES ('REACT1', 'Reactor room 1', '');
94INSERT INTO LOCATION VALUES ('REACT2', 'Reactor room 2', '');
95INSERT INTO LOCATION VALUES ('ENGROOM', 'Engine room', '');
96INSERT INTO LOCATION VALUES ('CONTROL', 'Control room', '');
97INSERT INTO LOCATION VALUES ('ESCPODS', 'Escape pods', '');
98INSERT INTO LOCATION VALUES ('CABIN3', 'Cabin #3', '');
99INSERT INTO LOCATION VALUES ('CABIN4', 'Cabin #4', '');
100INSERT INTO LOCATION VALUES ('CABIN5', 'Cabin #5', '');
101INSERT INTO LOCATION VALUES ('2CORR1', 'LeveL 2 Corridor 1', '');
102INSERT INTO LOCATION VALUES ('2CORR2', 'LeveL 2 Corridor 2', '');
103INSERT INTO LOCATION VALUES ('2CORR3', 'LeveL 2 Corridor 3', '');
104INSERT INTO LOCATION VALUES ('2CORR4', 'LeveL 2 Corridor 4', '');
105INSERT INTO LOCATION VALUES ('2CORR5', 'LeveL 2 Corridor 5', 'There are 2 bodies lying on the floor. 1 alien and 1 human');
106INSERT INTO LOCATION VALUES ('1CORR1', 'LeveL 1 Corridor 1', '');
107INSERT INTO LOCATION VALUES ('1CORR2', 'LeveL 1 Corridor 2', '');
108INSERT INTO LOCATION VALUES ('1CORR3', 'LeveL 1 Corridor 3', '');
109INSERT INTO LOCATION VALUES ('1CORR4', 'LeveL 1 Corridor 4', '');
110
111INSERT INTO DIRECTION VALUES ('N', 'North');
112INSERT INTO DIRECTION VALUES ('S', 'South');
113INSERT INTO DIRECTION VALUES ('E', 'East');
114INSERT INTO DIRECTION VALUES ('W', 'West');
115INSERT INTO DIRECTION VALUES ('U', 'Up');
116INSERT INTO DIRECTION VALUES ('D', 'Down');
117
118INSERT INTO PASSAGE VALUES('p1a','START','2CORR3','N', FALSE, NULL);
119INSERT INTO PASSAGE VALUES('P1b','2CORR3','START','S', FALSE, NULL);
120INSERT INTO PASSAGE VALUES('P2a','2CORR2','CABIN4','S', FALSE, NULL);
121INSERT INTO PASSAGE VALUES('P2b','CABIN4','2CORR2','N', FALSE, NULL);
122INSERT INTO PASSAGE VALUES('P3a','2CORR3','2CORR2','W', FALSE, NULL);
123INSERT INTO PASSAGE VALUES('P3b','2CORR2','2CORR3','E', FALSE, NULL);
124INSERT INTO PASSAGE VALUES('P4a','2CORR2','2CORR1','W', FALSE, NULL);
125INSERT INTO PASSAGE VALUES('P4b','2CORR1','2CORR2','E', FALSE, NULL);
126INSERT INTO PASSAGE VALUES('P5a','2CORR1','CAFE','W', TRUE, "The door is locked");
127INSERT INTO PASSAGE VALUES('P5b','CAFE','2CORR1','E', FALSE, NULL);
128INSERT INTO PASSAGE VALUES('P6a','2CORR1','LOCKER','N', FALSE, NULL);
129INSERT INTO PASSAGE VALUES('p6b','LOCKER','2CORR1','S', FALSE, NULL);
130INSERT INTO PASSAGE VALUES('P7a','2CORR1','CABIN3','S', TRUE, "The door wont budge");
131INSERT INTO PASSAGE VALUES('P7b','CABIN3','2CORR1','N', FALSE, NULL);
132INSERT INTO PASSAGE VALUES('P8a','2CORR3','CABIN5','N', TRUE, "The door doesn´t work");
133INSERT INTO PASSAGE VALUES('P8b','CABIN5','2CORR3','S', FALSE, NULL);
134INSERT INTO PASSAGE VALUES('P9a','2CORR3','2CORR4','E', FALSE, NULL);
135INSERT INTO PASSAGE VALUES('P9b','2CORR4','2CORR3','W', FALSE, NULL);
136INSERT INTO PASSAGE VALUES('P10a','2CORR4','CAPTQ','N', TRUE, "The door doesn´t open");
137INSERT INTO PASSAGE VALUES('P10b','CAPTQ','2CORR4','S', FALSE, NULL);
138INSERT INTO PASSAGE VALUES('P11a','2CORR4','2CORR5','E', FALSE, NULL);
139INSERT INTO PASSAGE VALUES('P11b','2CORR5','2CORR4','W', FALSE, NULL);
140INSERT INTO PASSAGE VALUES('P12a','2CORR5','ELV2','E', TRUE, "The elevator door seems to be stuck, you might use something to open it");
141INSERT INTO PASSAGE VALUES('P12b','ELV2','2CORR5','W', FALSE, NULL);
142INSERT INTO PASSAGE VALUES('P13a','ELV2','ELV1','D', FALSE, NULL);
143INSERT INTO PASSAGE VALUES('P13b','ELV1','ELV2','U', FALSE, NULL);
144INSERT INTO PASSAGE VALUES('P14','ELV2','ELV3','U', TRUE, "The elevator won't move");
145INSERT INTO PASSAGE VALUES('P15a','ELV1','1CORR4','W', FALSE, NULL);
146INSERT INTO PASSAGE VALUES('P15b','1CORR4','ELV1','E', FALSE, NULL);
147INSERT INTO PASSAGE VALUES('P16a','1CORR4','1CORR3','W', FALSE, NULL);
148INSERT INTO PASSAGE VALUES('P16b','1CORR3','1CORR4','E', FALSE, NULL);
149INSERT INTO PASSAGE VALUES('P17a','1CORR4','REACT1','S', FALSE, NULL);
150INSERT INTO PASSAGE VALUES('P17b','REACT1','1CORR4','N', FALSE, NULL);
151INSERT INTO PASSAGE VALUES('P18a','REACT1','REACT2','W', FALSE, NULL);
152INSERT INTO PASSAGE VALUES('P18b','REACT2','REACT1','E', FALSE, NULL);
153INSERT INTO PASSAGE VALUES('P19a','REACT2','CONTROL','W', TRUE, "There´s an enemy blocking the way");
154INSERT INTO PASSAGE VALUES('P19b','CONTROL','REACT2','E', FALSE, NULL);
155INSERT INTO PASSAGE VALUES('P20a','1CORR3','1CORR2','W', FALSE, NULL);
156INSERT INTO PASSAGE VALUES('P20b','1CORR2','1CORR3','E', FALSE, NULL);
157INSERT INTO PASSAGE VALUES('P21a','1CORR2','1CORR1','W', FALSE, NULL);
158INSERT INTO PASSAGE VALUES('P21b','1CORR1','1CORR2','E', FALSE, NULL);
159INSERT INTO PASSAGE VALUES('P22a','1CORR1','ESCPODS','S', TRUE, "There´s no electricity");
160INSERT INTO PASSAGE VALUES('P22b','ESCPODS','1CORR1','N', FALSE, NULL);
161INSERT INTO PASSAGE VALUES('P23a','ELV1','ENGROOM','E', FALSE, NULL);
162INSERT INTO PASSAGE VALUES('P23b','ENGROOM','ELV1','W', FALSE, NULL);
163INSERT INTO PASSAGE VALUES('P24a','ELV2','ELV3','U', FALSE, NULL);
164INSERT INTO PASSAGE VALUES('P24b','ELV3','ELV2','D', FALSE, NULL);
165INSERT INTO PASSAGE VALUES('P25a','ELV3','OFFICE','W', FALSE, NULL);
166INSERT INTO PASSAGE VALUES('P25b','OFFICE','ELV3','E', FALSE, NULL);
167INSERT INTO PASSAGE VALUES('P26a','OFFICE','BRIDGE','W', FALSE, NULL);
168INSERT INTO PASSAGE VALUES('P26b','BRIDGE','OFFICE','E', FALSE, NULL);
169
170INSERT INTO HERO VALUES('1','Player','200','5','START');
171
172
173INSERT INTO ITEMTYPE VALUES('CROWBAR', 'Crowbar', '5','0');
174INSERT INTO ITEMTYPE VALUES('RIFLE', 'Laser rifle', '50','0');
175INSERT INTO ITEMTYPE VALUES('PISTOL', 'Plasma pistol', '1000000','0');
176INSERT INTO ITEMTYPE VALUES('DETONATOR', 'Thermal detonator', '500','0');
177INSERT INTO ITEMTYPE VALUES('HEALING', 'Medkit','0','100');
178INSERT INTO ITEMTYPE VALUES('BOOK', 'Random book','0','0');
179INSERT INTO ITEMTYPE VALUES('KEYCARD', 'Keycard','0','0');
180INSERT INTO ITEMTYPE VALUES('BOOTS', 'Magnetic boots','0','0');
181INSERT INTO ITEMTYPE VALUES('DRAWER', 'Drawer','0','0');
182INSERT INTO ITEMTYPE VALUES('CORPSE', 'Corpse','0','0');
183INSERT INTO ITEMTYPE VALUES('ELEDOOR','Elevator door','0','0');
184INSERT INTO ITEMTYPE VALUES('FISTS','Bare Fists','5','0');
185
186INSERT INTO ITEM VALUES('CROWBAR','a Futuristic crowbar','A tool that can be used to open jammed doors.', 'CROWBAR', 'LOCKER', TRUE,TRUE);
187INSERT INTO ITEM VALUES('PISTOL','Light sidearm that shoots hot plasma','Can be used to kill enemies','PISTOL','ELV2',TRUE,FALSE);
188INSERT INTO ITEM VALUES('RIFLE','Strong rifle that shoots laser projectiles','Can used to decimate enemies','RIFLE','ESCPODS',TRUE,TRUE);
189INSERT INTO ITEM VALUES('DETONATOR','Extremely strong explosive','Do not use this item','DETONATOR','2CORR5',FALSE,FALSE);
190INSERT INTO ITEM VALUES('HEALING','Healing item','Can be used to heal yourself','HEALING','CAFE',TRUE,TRUE);
191INSERT INTO ITEM VALUES('BOOK','a simple book','PLACEHOLDER','BOOK','CAPTQ',TRUE,TRUE);
192INSERT INTO ITEM VALUES('KEYCARD','Keycard for a door','Can be used to unlock a door','KEYCARD','CAPTQ',FALSE,FALSE);
193INSERT INTO ITEM VALUES('BOOTS','Magnetic boots','These will lock you into the ships hull','BOOTS','2CORR5',FALSE,FALSE);
194INSERT INTO ITEM VALUES('DRAWER', 'A metallic drawer', 'It is a drawer.', 'DRAWER','CAPTQ', TRUE,FALSE);
195INSERT INTO ITEM VALUES('CORPSE', 'A human body', 'The body seems to be lifeless and cold to the touch.', 'CORPSE','2CORR5', TRUE,FALSE);
196INSERT INTO ITEM VALUES('ELEDOOR','Door of the elevator','The door is shut','ELEDOOR','2CORR5',FALSE,FALSE);
197INSERT INTO ITEM VALUES('FISTS','A tasty knuckle sandwich','Your own fists','FISTS','PLAYER',FALSE,FALSE);
198
199INSERT INTO ENEMY VALUES('1','100','Grunt1','25','REACT2');
200INSERT INTO ENEMY VALUES('2','100','Grunt2','25','ELV1');
201INSERT INTO ENEMY VALUES('3','200','Boss','50','BRIDGE');
202
203INSERT INTO HELPTABLE VALUES('help','Opens help table');
204INSERT INTO HELPTABLE VALUES('use','This command will use a desired item');
205INSERT INTO HELPTABLE VALUES('take','This command will pickup an item');
206INSERT INTO HELPTABLE VALUES('health','Shows the health you have left');