WLfJuSfa

1-- phpMyAdmin SQL Dump
2-- version 3.4.10.1deb1
3-- http://www.phpmyadmin.net
4--
5-- Host: 192.168.1.103:3306
6-- Generation Time: Nov 19, 2016 at 02:32 PM
7-- Server version: 5.5.52
8-- PHP Version: 5.3.10-1ubuntu3.24
9
10SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13--
14-- Database: `geocaching_dev`
15--
16
17-- --------------------------------------------------------
18
19--
20-- Table structure for table `caches`
21--
22
23CREATE TABLE IF NOT EXISTS `caches` (
24  `id` int(11) NOT NULL AUTO_INCREMENT,
25  `list` int(10) unsigned NOT NULL,
26  `found_order` int(11) NOT NULL,
27  `found_date` date DEFAULT NULL,
28  `code` varchar(10) NOT NULL,
29  `name` varchar(255) NOT NULL,
30  `type` int(11) NOT NULL,
31  `region` int(11) NOT NULL,
32  `difficulty` float NOT NULL,
33  `terrain` float NOT NULL,
34  `size` varchar(50) NOT NULL,
35  `serie` varchar(100) NOT NULL,
36  `start_coordinations` varchar(100) NOT NULL,
37  `final_coordinations` varchar(100) NOT NULL,
38  `note` varchar(255) NOT NULL,
39  `ts_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
40  `ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
41  PRIMARY KEY (`id`),
42  UNIQUE KEY `code` (`code`),
43  UNIQUE KEY `order` (`found_order`,`list`),
44  KEY `type` (`type`),
45  KEY `region` (`region`),
46  KEY `list` (`list`)
47) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
48
49-- --------------------------------------------------------
50
51--
52-- Table structure for table `cwg`
53--
54
55CREATE TABLE IF NOT EXISTS `cwg` (
56  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
57  `name` varchar(60) NOT NULL,
58  `catalogNumber` varchar(20) NOT NULL,
59  `userGcGuid` varchar(100) DEFAULT NULL,
60  `version` tinyint(3) unsigned NOT NULL,
61  `year` year(4) NOT NULL,
62  `official` varchar(60) DEFAULT NULL,
63  `event` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'is event CWG',
64  `imageUrl` varchar(255) NOT NULL,
65  `catalogDate` date NOT NULL,
66  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
67  PRIMARY KEY (`id`)
68) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
69
70-- --------------------------------------------------------
71
72--
73-- Table structure for table `cwgCollection`
74--
75
76CREATE TABLE IF NOT EXISTS `cwgCollection` (
77  `cwg_id` int(11) unsigned NOT NULL,
78  `list_id` int(11) unsigned NOT NULL,
79  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
80  UNIQUE KEY `unique_row` (`cwg_id`,`list_id`),
81  KEY `list_id` (`list_id`)
82) ENGINE=InnoDB DEFAULT CHARSET=utf8;
83
84-- --------------------------------------------------------
85
86--
87-- Table structure for table `cwgCwgTag`
88--
89
90CREATE TABLE IF NOT EXISTS `cwgCwgTag` (
91  `cwg_id` int(10) unsigned NOT NULL,
92  `cwgTag_id` int(10) unsigned NOT NULL,
93  UNIQUE KEY `unique_row` (`cwg_id`,`cwgTag_id`),
94  KEY `cwgTag_id` (`cwgTag_id`)
95) ENGINE=InnoDB DEFAULT CHARSET=utf8;
96
97-- --------------------------------------------------------
98
99--
100-- Table structure for table `cwgTag`
101--
102
103CREATE TABLE IF NOT EXISTS `cwgTag` (
104  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
105  `name` varchar(255) NOT NULL,
106  PRIMARY KEY (`id`)
107) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
108
109-- --------------------------------------------------------
110
111--
112-- Table structure for table `list`
113--
114
115CREATE TABLE IF NOT EXISTS `list` (
116  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
117  `name` varchar(255) NOT NULL,
118  `type` tinyint(4) unsigned NOT NULL,
119  `isDefault` tinyint(1) NOT NULL DEFAULT '0',
120  `user` int(10) unsigned NOT NULL COMMENT 'author',
121  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
122  PRIMARY KEY (`id`),
123  KEY `author` (`user`)
124) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
125
126-- --------------------------------------------------------
127
128--
129-- Table structure for table `log`
130--
131
132CREATE TABLE IF NOT EXISTS `log` (
133  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
134  `area` enum('AUTH','CACHE','TRACKABLE','CWG') NOT NULL,
135  `type` varchar(50) NOT NULL,
136  `key` varchar(255) NOT NULL,
137  `message` text,
138  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
139  PRIMARY KEY (`id`)
140) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
141
142-- --------------------------------------------------------
143
144--
145-- Table structure for table `logCwgCollection`
146--
147
148CREATE TABLE IF NOT EXISTS `logCwgCollection` (
149  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
150  `list_id` int(11) unsigned NOT NULL,
151  `cwg_id` int(10) unsigned NOT NULL,
152  `user_id` varchar(50) NOT NULL,
153  `action` enum('add','remove') NOT NULL,
154  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
155  PRIMARY KEY (`id`),
156  KEY `list_id` (`list_id`),
157  KEY `user_id` (`user_id`),
158  KEY `cwg_id` (`cwg_id`)
159) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
160
161-- --------------------------------------------------------
162
163--
164-- Table structure for table `regions`
165--
166
167CREATE TABLE IF NOT EXISTS `regions` (
168  `id` int(11) NOT NULL AUTO_INCREMENT,
169  `region_name` varchar(100) NOT NULL,
170  PRIMARY KEY (`id`),
171  UNIQUE KEY `region_name` (`region_name`)
172) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
173
174-- --------------------------------------------------------
175
176--
177-- Table structure for table `roles`
178--
179
180CREATE TABLE IF NOT EXISTS `roles` (
181  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
182  `user` varchar(50) NOT NULL,
183  `list` int(10) unsigned NOT NULL,
184  `role` tinyint(3) unsigned NOT NULL,
185  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
186  PRIMARY KEY (`id`),
187  UNIQUE KEY `user_list_unique` (`user`,`list`),
188  KEY `user` (`user`),
189  KEY `list` (`list`)
190) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
191
192-- --------------------------------------------------------
193
194--
195-- Table structure for table `trackables`
196--
197
198CREATE TABLE IF NOT EXISTS `trackables` (
199  `id` int(11) NOT NULL AUTO_INCREMENT,
200  `referenceCode` varchar(10) NOT NULL,
201  `trackingCode` varchar(10) NOT NULL,
202  `name` varchar(100) NOT NULL,
203  `type` varchar(255) NOT NULL,
204  `ts_update` timestamp NULL DEFAULT NULL,
205  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
206  PRIMARY KEY (`id`),
207  UNIQUE KEY `referenceCode` (`referenceCode`)
208) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
209
210-- --------------------------------------------------------
211
212--
213-- Table structure for table `users`
214--
215
216CREATE TABLE IF NOT EXISTS `users` (
217  `id` varchar(50) NOT NULL,
218  `email` varchar(100) NOT NULL,
219  `password` varchar(100) DEFAULT NULL,
220  `nick` varchar(100) NOT NULL,
221  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
222  `ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
223  `ts_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
224  PRIMARY KEY (`id`),
225  UNIQUE KEY `email_UNIQUE` (`email`),
226  KEY `id` (`id`)
227) ENGINE=InnoDB DEFAULT CHARSET=utf8;
228
229--
230-- Constraints for dumped tables
231--
232
233--
234-- Constraints for table `cwgCollection`
235--
236ALTER TABLE `cwgCollection`
237  ADD CONSTRAINT `cwgCollection_ibfk_1` FOREIGN KEY (`cwg_id`) REFERENCES `cwg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
238  ADD CONSTRAINT `cwgCollection_ibfk_2` FOREIGN KEY (`list_id`) REFERENCES `list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
239
240--
241-- Constraints for table `cwgCwgTag`
242--
243ALTER TABLE `cwgCwgTag`
244  ADD CONSTRAINT `cwgCwgTag_ibfk_1` FOREIGN KEY (`cwg_id`) REFERENCES `cwg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
245  ADD CONSTRAINT `cwgCwgTag_ibfk_2` FOREIGN KEY (`cwgTag_id`) REFERENCES `cwgTag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
246
247--
248-- Constraints for table `logCwgCollection`
249--
250ALTER TABLE `logCwgCollection`
251  ADD CONSTRAINT `logCwgCollection_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
252  ADD CONSTRAINT `logCwgCollection_ibfk_2` FOREIGN KEY (`cwg_id`) REFERENCES `cwg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
253  ADD CONSTRAINT `logCwgCollection_ibfk_3` FOREIGN KEY (`list_id`) REFERENCES `list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
254
255--
256-- Constraints for table `roles`
257--
258ALTER TABLE `roles`
259  ADD CONSTRAINT `roles_ibfk_1` FOREIGN KEY (`list`) REFERENCES `list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
260  ADD CONSTRAINT `roles_ibfk_2` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;