37T078Cc

1-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
2--
3-- Host: localhost    Database: schiffeversenken
4-- ------------------------------------------------------
5-- Server version	5.7.24-0ubuntu0.18.04.1
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18CREATE USER IF NOT EXISTS battleships@'%' IDENTIFIED BY 'battleships';
19
20GRANT ALL PRIVILEGES ON  *.* to 'battleships'@'%' WITH GRANT OPTION;
21
22--
23-- Current Database: `schiffeversenken`
24--
25
26CREATE DATABASE /*!32312 IF NOT EXISTS*/ `schiffeversenken` /*!40100 DEFAULT CHARACTER SET latin1 */;
27
28USE `schiffeversenken`;
29
30--
31-- Table structure for table `configurations`
32--
33
34DROP TABLE IF EXISTS `configurations`;
35/*!40101 SET @saved_cs_client     = @@character_set_client */;
36/*!40101 SET character_set_client = utf8 */;
37CREATE TABLE `configurations` (
38  `session_id` int(11) NOT NULL,
39  `configuration_id` int(11) NOT NULL AUTO_INCREMENT,
40  `board_size_x` int(11) DEFAULT NULL,
41  `board_size_y` int(11) DEFAULT NULL,
42  PRIMARY KEY (`configuration_id`),
43  KEY `configurations_session_id_fkey_idx` (`session_id`),
44  CONSTRAINT `configurations_session_id_fkey` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`session_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
45) ENGINE=InnoDB DEFAULT CHARSET=latin1;
46/*!40101 SET character_set_client = @saved_cs_client */;
47
48--
49-- Dumping data for table `configurations`
50--
51
52LOCK TABLES `configurations` WRITE;
53/*!40000 ALTER TABLE `configurations` DISABLE KEYS */;
54/*!40000 ALTER TABLE `configurations` ENABLE KEYS */;
55UNLOCK TABLES;
56
57--
58-- Table structure for table `fields`
59--
60
61DROP TABLE IF EXISTS `fields`;
62/*!40101 SET @saved_cs_client     = @@character_set_client */;
63/*!40101 SET character_set_client = utf8 */;
64CREATE TABLE `fields` (
65  `game_state_id` int(11) NOT NULL,
66  `field_position_x` int(11) NOT NULL,
67  `field_position_y` int(11) NOT NULL,
68  `player` tinyint(4) NOT NULL,
69  `field_state` varchar(45) DEFAULT NULL,
70  PRIMARY KEY (`field_position_x`,`field_position_y`,`player`,`game_state_id`)
71) ENGINE=InnoDB DEFAULT CHARSET=latin1;
72/*!40101 SET character_set_client = @saved_cs_client */;
73
74--
75-- Dumping data for table `fields`
76--
77
78LOCK TABLES `fields` WRITE;
79/*!40000 ALTER TABLE `fields` DISABLE KEYS */;
80/*!40000 ALTER TABLE `fields` ENABLE KEYS */;
81UNLOCK TABLES;
82
83--
84-- Table structure for table `game_states`
85--
86
87DROP TABLE IF EXISTS `game_states`;
88/*!40101 SET @saved_cs_client     = @@character_set_client */;
89/*!40101 SET character_set_client = utf8 */;
90CREATE TABLE `game_states` (
91  `game_state_id` int(11) NOT NULL AUTO_INCREMENT,
92  `session_id` int(11) DEFAULT NULL,
93  `player_turn` tinyint(4) DEFAULT NULL,
94  `game_name` varchar(45) DEFAULT NULL,
95  PRIMARY KEY (`game_state_id`),
96  KEY `session_id_fkey_idx` (`session_id`),
97  CONSTRAINT `session_id_fkey` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`session_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
98) ENGINE=InnoDB DEFAULT CHARSET=latin1;
99/*!40101 SET character_set_client = @saved_cs_client */;
100
101--
102-- Dumping data for table `game_states`
103--
104
105LOCK TABLES `game_states` WRITE;
106/*!40000 ALTER TABLE `game_states` DISABLE KEYS */;
107/*!40000 ALTER TABLE `game_states` ENABLE KEYS */;
108UNLOCK TABLES;
109
110--
111-- Table structure for table `sessions`
112--
113
114DROP TABLE IF EXISTS `sessions`;
115/*!40101 SET @saved_cs_client     = @@character_set_client */;
116/*!40101 SET character_set_client = utf8 */;
117CREATE TABLE `sessions` (
118  `session_id` int(11) NOT NULL AUTO_INCREMENT,
119  `time` datetime DEFAULT NULL,
120  `session_hash` bigint(20) NOT NULL,
121  PRIMARY KEY (`session_id`)
122) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
123/*!40101 SET character_set_client = @saved_cs_client */;
124
125--
126-- Dumping data for table `sessions`
127--
128
129LOCK TABLES `sessions` WRITE;
130/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
131INSERT INTO `sessions` VALUES (1,'2013-04-20 18:00:00',1231232424);
132/*!40000 ALTER TABLE `sessions` ENABLE KEYS */;
133UNLOCK TABLES;
134
135--
136-- Table structure for table `ships`
137--
138
139DROP TABLE IF EXISTS `ships`;
140/*!40101 SET @saved_cs_client     = @@character_set_client */;
141/*!40101 SET character_set_client = utf8 */;
142CREATE TABLE `ships` (
143  `ship_configuration_id` int(11) NOT NULL,
144  `ship_type` varchar(45) DEFAULT NULL,
145  `amount` int(11) DEFAULT NULL,
146  `configuration_id` int(11) DEFAULT NULL,
147  PRIMARY KEY (`ship_configuration_id`),
148  KEY `ships_configuration_id_fkey_idx` (`configuration_id`),
149  CONSTRAINT `ships_configuration_id_fkey` FOREIGN KEY (`configuration_id`) REFERENCES `configurations` (`configuration_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
150) ENGINE=InnoDB DEFAULT CHARSET=latin1;
151/*!40101 SET character_set_client = @saved_cs_client */;
152
153--
154-- Dumping data for table `ships`
155--
156
157LOCK TABLES `ships` WRITE;
158/*!40000 ALTER TABLE `ships` DISABLE KEYS */;
159/*!40000 ALTER TABLE `ships` ENABLE KEYS */;
160UNLOCK TABLES;
161/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
162
163/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
164/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
165/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
166/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
167/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
168/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
169/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
170
171-- Dump completed on 2018-12-05 18:33:56
172
173-- DB change 11.12.2018
174ALTER TABLE game_states ADD COLUMN
175`game_difficulty` varchar(45) DEFAULT NULL;
176
177ALTER TABLE `schiffeversenken`.`sessions` 
178CHANGE COLUMN `session_hash` `session_hash` VARCHAR(45) NOT NULL ;
179
180ALTER TABLE `schiffeversenken`.`ships` 
181CHANGE COLUMN `ship_configuration_id` `ship_configuration_id` INT(11) NOT NULL AUTO_INCREMENT ;
182
183-- ---------------------------
184
185-- DB change 20.12.2018
186DROP TABLE `schiffeversenken`.`fields`;
187ALTER TABLE `schiffeversenken`.`game_states` 
188ADD COLUMN `field_player` TEXT NULL AFTER `game_difficulty`,
189ADD COLUMN `field_ki` TEXT NULL AFTER `field_player`;
190-- -------