PtEsFFYH

1--
2-- Databáze: `test-db`
3--
4CREATE DATABASE IF NOT EXISTS `test-db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
5USE `test-db`;
6
7-- --------------------------------------------------------
8
9--
10-- Struktura tabulky `categories`
11--
12
13CREATE TABLE `categories` (
14  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
15  `name` varchar(255) NOT NULL,
16  PRIMARY KEY (`id`),
17  UNIQUE KEY `categories_name_unique` (`name`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
19
20-- --------------------------------------------------------
21
22--
23-- Struktura tabulky `clubs`
24--
25
26CREATE TABLE `clubs` (
27  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
28  `name` varchar(150) NOT NULL,
29  `email` varchar(150) NOT NULL,
30  `street` varchar(45) NOT NULL,
31  `house_number` varchar(10) NOT NULL,
32  `city` varchar(45) NOT NULL,
33  `zip` varchar(6) NOT NULL,
34  `lat` float(8,5) DEFAULT NULL,
35  `lng` float(8,5) DEFAULT NULL,
36  `contact_street` varchar(45) DEFAULT NULL,
37  `contact_house_number` varchar(10) DEFAULT NULL,
38  `contact_city` varchar(45) DEFAULT NULL,
39  `contact_zip` varchar(6) DEFAULT NULL,
40  `description` text,
41  `logo_filename` varchar(256) DEFAULT NULL,
42  `image_filename` varchar(256) DEFAULT NULL,
43  `telephone` varchar(20) DEFAULT NULL,
44  `web_url` varchar(500) DEFAULT NULL,
45  `fb_link` varchar(500) DEFAULT NULL,
46  `account_number` varchar(100) DEFAULT NULL,
47  `account_number_url` varchar(500) DEFAULT NULL,
48  `type` varchar(45) NOT NULL,
49  `status` varchar(20) NOT NULL DEFAULT 'disabled',
50  `user_id` int(10) unsigned NOT NULL,
51  PRIMARY KEY (`id`),
52  KEY `clubs_user_id_foreign` (`user_id`)
53) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
54
55-- --------------------------------------------------------
56
57--
58-- Struktura tabulky `clubs_categories`
59--
60
61CREATE TABLE `clubs_categories` (
62  `club_id` int(10) unsigned NOT NULL,
63  `category_id` int(10) unsigned NOT NULL,
64  PRIMARY KEY (`club_id`,`category_id`),
65  KEY `clubs_categories_category_id_foreign` (`category_id`)
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
68-- --------------------------------------------------------
69
70--
71-- Struktura tabulky `events`
72--
73
74CREATE TABLE `events` (
75  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
76  `name` varchar(100) NOT NULL,
77  `from` datetime NOT NULL,
78  `to` datetime NOT NULL,
79  `description` text,
80  `category` varchar(100) DEFAULT NULL,
81  `image_filename` varchar(256) DEFAULT NULL,
82  `street` varchar(45) NOT NULL,
83  `house_number` varchar(10) NOT NULL,
84  `city` varchar(45) NOT NULL,
85  `zip` varchar(6) NOT NULL,
86  `lat` float(8,5) DEFAULT NULL,
87  `lng` float(8,5) DEFAULT NULL,
88  `club_id` int(10) unsigned NOT NULL,
89  PRIMARY KEY (`id`),
90  KEY `events_club_id_foreign` (`club_id`)
91) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
92
93-- --------------------------------------------------------
94
95--
96-- Struktura tabulky `users`
97--
98
99CREATE TABLE `users` (
100  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
101  `username` varchar(255) NOT NULL,
102  `email` varchar(255) NOT NULL,
103  `password` varchar(255) NOT NULL,
104  `password_salt` varchar(255) DEFAULT NULL,
105  `role` varchar(20) NOT NULL DEFAULT 'customer',
106  `enabled` tinyint(1) DEFAULT '1',
107  `anonymous` tinyint(1) DEFAULT '0',
108  PRIMARY KEY (`id`),
109  UNIQUE KEY `users_username_unique` (`username`)
110) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
111
112--
113-- Omezení pro exportované tabulky
114--
115
116--
117-- Omezení pro tabulku `clubs`
118--
119ALTER TABLE `clubs`
120  ADD CONSTRAINT `clubs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
121
122--
123-- Omezení pro tabulku `clubs_categories`
124--
125ALTER TABLE `clubs_categories`
126  ADD CONSTRAINT `clubs_categories_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
127  ADD CONSTRAINT `clubs_categories_club_id_foreign` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
128
129--
130-- Omezení pro tabulku `events`
131--
132ALTER TABLE `events`
133  ADD CONSTRAINT `events_club_id_foreign` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
134
135
136--
137-- Vlozeni dat
138--
139INSERT INTO users (username, email)
140VALUES ('TestMaster', 'test@databaze.cz');
141
142
143--
144-- VIEW
145--
146CREATE VIEW UsersList AS
147SELECT username, email
148FROM users;