· 8 years ago · Aug 26, 2018, 11:38 AM
1# ************************************************************
2# Sequel Pro SQL dump
3# Version 4541
4#
5# http://www.sequelpro.com/
6# https://github.com/sequelpro/sequelpro
7#
8# Host: localhost (MySQL 5.7.21)
9# Database: chess
10# Generation Time: 2018-08-26 11:34:04 +0000
11# ************************************************************
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8 */;
18/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
19/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
20/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
21
22
23# Dump of table avatars
24# ------------------------------------------------------------
25
26DROP TABLE IF EXISTS `avatars`;
27
28CREATE TABLE `avatars` (
29 `id` int(11) NOT NULL AUTO_INCREMENT,
30 `url` varchar(32) NOT NULL,
31 PRIMARY KEY (`id`),
32 UNIQUE KEY `url` (`url`)
33) ENGINE=InnoDB DEFAULT CHARSET=utf8;
34
35LOCK TABLES `avatars` WRITE;
36/*!40000 ALTER TABLE `avatars` DISABLE KEYS */;
37
38INSERT INTO `avatars` (`id`, `url`)
39VALUES
40 (1,'bear.svg'),
41 (2,'bird.svg'),
42 (3,'boar.svg'),
43 (4,'cat.svg'),
44 (5,'chicken (1).svg'),
45 (6,'chicken.svg'),
46 (7,'cow.svg'),
47 (8,'deer.svg'),
48 (9,'dog.svg'),
49 (10,'fox.svg'),
50 (11,'frog.svg'),
51 (12,'giraffe.svg'),
52 (13,'gorilla.svg'),
53 (14,'hamster.svg'),
54 (15,'hen.svg'),
55 (16,'koala.svg'),
56 (17,'lion.svg'),
57 (18,'monkey.svg'),
58 (19,'mouse.svg'),
59 (20,'owl.svg'),
60 (21,'panda.svg'),
61 (22,'pig.svg'),
62 (23,'rabbit.svg'),
63 (24,'tiger.svg'),
64 (25,'wolf.svg'),
65 (26,'zebra.svg');
66
67/*!40000 ALTER TABLE `avatars` ENABLE KEYS */;
68UNLOCK TABLES;
69
70
71# Dump of table games
72# ------------------------------------------------------------
73
74DROP TABLE IF EXISTS `games`;
75
76CREATE TABLE `games` (
77 `id` int(11) NOT NULL AUTO_INCREMENT,
78 `whitePlayerId` int(11) DEFAULT NULL,
79 `blackPlayerId` int(11) DEFAULT NULL,
80 `result` enum('b','w','d') DEFAULT NULL,
81 `fen` varchar(128) DEFAULT NULL,
82 PRIMARY KEY (`id`),
83 KEY `whiteUserId` (`whitePlayerId`),
84 KEY `blackUserId` (`blackPlayerId`),
85 CONSTRAINT `games_ibfk_1` FOREIGN KEY (`whitePlayerId`) REFERENCES `users` (`id`),
86 CONSTRAINT `games_ibfk_2` FOREIGN KEY (`blackPlayerId`) REFERENCES `users` (`id`)
87) ENGINE=InnoDB DEFAULT CHARSET=utf8;
88
89LOCK TABLES `games` WRITE;
90/*!40000 ALTER TABLE `games` DISABLE KEYS */;
91
92INSERT INTO `games` (`id`, `whitePlayerId`, `blackPlayerId`, `result`, `fen`)
93VALUES
94 (10,30,29,'b','rnb1kbnr/pppp1ppp/8/4p3/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
95 (11,29,30,'b','rnb1kbnr/pppp1ppp/8/4p3/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
96 (12,29,30,NULL,NULL),
97 (13,29,30,'b','rnb1kbnr/ppp2ppp/4p3/8/3p2Pq/3P1P2/PPPBP2P/RN1QKBNR w KQkq - 1 5'),
98 (14,32,29,'b','rnb1kbnr/pp3ppp/2p1p3/3p4/3P2Pq/5P2/PPPBP2P/RN1QKBNR w KQkq - 1 5'),
99 (15,29,32,'b','rnb1kbnr/pppp1ppp/4p3/8/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
100 (16,32,29,NULL,NULL),
101 (17,34,29,'b','rnb1kbnr/pppp1ppp/4p3/8/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
102 (18,34,29,NULL,NULL);
103
104/*!40000 ALTER TABLE `games` ENABLE KEYS */;
105UNLOCK TABLES;
106
107
108# Dump of table games_stats
109# ------------------------------------------------------------
110
111DROP VIEW IF EXISTS `games_stats`;
112
113CREATE TABLE `games_stats` (
114 `id` INT(11) NOT NULL DEFAULT '0',
115 `result` ENUM('b','w','d') NULL DEFAULT NULL,
116 `whitePlayerId` INT(11) NOT NULL DEFAULT '0',
117 `blackPlayerId` INT(11) NOT NULL DEFAULT '0',
118 `whitePlayerEmail` VARCHAR(64) NOT NULL,
119 `blackPlayerEmail` VARCHAR(64) NOT NULL
120) ENGINE=MyISAM;
121
122
123
124# Dump of table users
125# ------------------------------------------------------------
126
127DROP TABLE IF EXISTS `users`;
128
129CREATE TABLE `users` (
130 `id` int(11) NOT NULL AUTO_INCREMENT,
131 `email` varchar(64) NOT NULL,
132 `salt` varchar(256) NOT NULL,
133 `hashedPassword` char(64) NOT NULL DEFAULT '',
134 `roles` varchar(256) DEFAULT 'user',
135 `avatarUrl` varchar(256) DEFAULT '',
136 PRIMARY KEY (`id`),
137 UNIQUE KEY `email` (`email`)
138) ENGINE=InnoDB DEFAULT CHARSET=utf8;
139
140LOCK TABLES `users` WRITE;
141/*!40000 ALTER TABLE `users` DISABLE KEYS */;
142
143INSERT INTO `users` (`id`, `email`, `salt`, `hashedPassword`, `roles`, `avatarUrl`)
144VALUES
145 (29,'ivan@asd.asd','2zdXaEXPN80RF0MVkbf/SANShTCCHi9Q4sYeG1svZ1xauhWGEv0L+MvmXQ8etJa8fEaJU237ysbXVnfgxER/cjfD/27gkkQC7OXJbDvgzllHpwQ0FqHf6f3WzyLN7P81u+VyUf3K0f5pJZHeTFrp9Fm4ct8ppHcdJxWi96z0pcI=','0058f68df8f84f72aa8462ff0a28cef742102c5d035745b4df7b6533589339e1','user admin','deer.svg'),
146 (30,'asdd@asd.asd','RApCtig0CPTCubdN/CawEcTrUVTDZNvHIOSxjhHp+AGS/S1h9O87Qg3ub9LCClcaVou8jFKO10rFRX4clzgaMA53hoGNS1PN1UZBcOJhtsfP+Btel9NloTe1jPYRSJKQn5HikVRGUU7dROlNPXQwyQFTbuL3DVoO0auvI1Dr5VE=','df42744c429fcc68b49c3391dcb5049701c4b487311aa4cce37a2daaad01e1e0','user admin','bird.svg'),
147 (32,'asd@asd.asd','9VY6nNEQF6zax53HB7fmY7khR876+bH+H4Xu7xYWXZdOXMAo6JZJ4rkKAhmOld2Qz+3v2KnZ+Lccf4QgZ+K18IZOLkH6AzdxWY9FwmPm6GpM0ei7W+F0EfZbBnNil46mo6qTP5AiHJjRMxd2O33GmtdOzEPVwfTETjEKVadI2ec=','133e9d8ae6be910b21af142ad3825661157ebc195acd863aa29c15404cfcf3fb','user admin','cow.svg'),
148 (33,'asddddd@asd.asd','Lpu/wlr75CEYc4q6DeP4eL/kWsmF9+bVkZ0Imsww8VoULpd+y1XwpktVmeF43m32nVlRRGYfxmQ8hGeuIFT0gmwxfECN4EdUfe6AWULx1iiK5DXnPTx87Wp48nIuQvUpp2bN3KdhLpJfFrH7gdiZewHVHif90DiQW4KGFio5O0g=','cb81f9d3c68b9a1915b824955562a09eabe8fd9c5ddbd367c4357f22c0fc163d','user admin','cat.svg'),
149 (34,'asddddd@asssd.asd','izrc4N31sNA6t6lOoCpDiCwhoMKuVVm5nuk3sJFx7uXPWELqrOSFin8yp8dsEOB8igyk/FEQBw9D42+40bpdOedCljFIPgvEA0s4/QaNnqPnK2j8XfZMIqjKs5jBJTLsMTs6/Grl0Ms1Pp8mFq/WNXWRmaU+sTLS1htZEDKR/6Q=','19505c5bd0858f1ffed8f476407f4c07e8c6a06f35178e8e2d42a16b4afa631b','user','hamster.svg');
150
151/*!40000 ALTER TABLE `users` ENABLE KEYS */;
152UNLOCK TABLES;
153
154DELIMITER ;;
155/*!50003 SET SESSION SQL_MODE="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" */;;
156/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `set_avatar` BEFORE INSERT ON `users` FOR EACH ROW SET NEW.avatarUrl = (SELECT a.url FROM avatars a
157ORDER BY RAND() LIMIT 1) */;;
158/*!50003 SET SESSION SQL_MODE="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" */;;
159/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `delete_games` BEFORE DELETE ON `users` FOR EACH ROW DELETE FROM games WHERE games.whitePlayerId = OLD.id OR games.blackPlayerId = OLD.id */;;
160DELIMITER ;
161/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
162
163
164# Dump of table users_stats
165# ------------------------------------------------------------
166
167DROP VIEW IF EXISTS `users_stats`;
168
169CREATE TABLE `users_stats` (
170 `id` INT(11) NOT NULL DEFAULT '0',
171 `email` VARCHAR(64) NOT NULL,
172 `salt` VARCHAR(256) NOT NULL,
173 `roles` VARCHAR(256) NULL DEFAULT 'user',
174 `hashedPassword` CHAR(64) NOT NULL DEFAULT '',
175 `avatarUrl` VARCHAR(256) NULL DEFAULT ''
176) ENGINE=MyISAM;
177
178
179
180
181
182# Replace placeholder table for games_stats with correct view syntax
183# ------------------------------------------------------------
184
185DROP TABLE `games_stats`;
186
187CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `games_stats`
188AS SELECT
189 `g`.`id` AS `id`,
190 `g`.`result` AS `result`,
191 `uw`.`id` AS `whitePlayerId`,
192 `ub`.`id` AS `blackPlayerId`,
193 `uw`.`email` AS `whitePlayerEmail`,
194 `ub`.`email` AS `blackPlayerEmail`
195FROM ((`games` `g` join `users` `uw` on((`uw`.`id` = `g`.`whitePlayerId`))) join `users` `ub` on((`ub`.`id` = `g`.`blackPlayerId`)));
196
197
198# Replace placeholder table for users_stats with correct view syntax
199# ------------------------------------------------------------
200
201DROP TABLE `users_stats`;
202
203CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `users_stats`
204AS SELECT
205 `u`.`id` AS `id`,
206 `u`.`email` AS `email`,
207 `u`.`salt` AS `salt`,
208 `u`.`roles` AS `roles`,
209 `u`.`hashedPassword` AS `hashedPassword`,
210 `u`.`avatarUrl` AS `avatarUrl`
211FROM `users` `u`;
212
213/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
214/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
215/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
216/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
217/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
218/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;