1
-- MySQL dump 10.13 Distrib 5.1.53, for apple-darwin10.3.0 (i386)
3
-- Host: localhost Database: sakila
4
-- ------------------------------------------------------
5
-- Server version 5.1.53-log
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 */;
19
-- Table structure for table `actor`
22
DROP TABLE IF EXISTS `actor`;
23
/*!40101 SET @saved_cs_client = @@character_set_client */;
24
/*!40101 SET character_set_client = utf8 */;
25
CREATE TABLE `actor` (
26
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
27
`first_name` varchar(45) NOT NULL,
28
`last_name` varchar(45) NOT NULL,
29
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
30
PRIMARY KEY (`actor_id`),
31
KEY `idx_actor_last_name` (`last_name`)
32
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
33
/*!40101 SET character_set_client = @saved_cs_client */;
36
-- Temporary table structure for view `actor_info`
39
DROP TABLE IF EXISTS `actor_info`;
40
/*!50001 DROP VIEW IF EXISTS `actor_info`*/;
41
SET @saved_cs_client = @@character_set_client;
42
SET character_set_client = utf8;
43
/*!50001 CREATE TABLE `actor_info` (
44
`actor_id` smallint(5) unsigned,
45
`first_name` varchar(45),
46
`last_name` varchar(45),
47
`film_info` varchar(341)
49
SET character_set_client = @saved_cs_client;
52
-- Table structure for table `address`
55
DROP TABLE IF EXISTS `address`;
56
/*!40101 SET @saved_cs_client = @@character_set_client */;
57
/*!40101 SET character_set_client = utf8 */;
58
CREATE TABLE `address` (
59
`address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
60
`address` varchar(50) NOT NULL,
61
`address2` varchar(50) DEFAULT NULL,
62
`district` varchar(20) NOT NULL,
63
`city_id` smallint(5) unsigned NOT NULL,
64
`postal_code` varchar(10) DEFAULT NULL,
65
`phone` varchar(20) NOT NULL,
66
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
67
PRIMARY KEY (`address_id`),
68
KEY `idx_fk_city_id` (`city_id`),
69
CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
70
) ENGINE=InnoDB AUTO_INCREMENT=606 DEFAULT CHARSET=utf8;
71
/*!40101 SET character_set_client = @saved_cs_client */;
74
-- Table structure for table `category`
77
DROP TABLE IF EXISTS `category`;
78
/*!40101 SET @saved_cs_client = @@character_set_client */;
79
/*!40101 SET character_set_client = utf8 */;
80
CREATE TABLE `category` (
81
`category_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
82
`name` varchar(25) NOT NULL,
83
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
84
PRIMARY KEY (`category_id`)
85
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
86
/*!40101 SET character_set_client = @saved_cs_client */;
89
-- Table structure for table `city`
92
DROP TABLE IF EXISTS `city`;
93
/*!40101 SET @saved_cs_client = @@character_set_client */;
94
/*!40101 SET character_set_client = utf8 */;
96
`city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
97
`city` varchar(50) NOT NULL,
98
`country_id` smallint(5) unsigned NOT NULL,
99
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
100
PRIMARY KEY (`city_id`),
101
KEY `idx_fk_country_id` (`country_id`),
102
CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
103
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8;
104
/*!40101 SET character_set_client = @saved_cs_client */;
107
-- Table structure for table `country`
110
DROP TABLE IF EXISTS `country`;
111
/*!40101 SET @saved_cs_client = @@character_set_client */;
112
/*!40101 SET character_set_client = utf8 */;
113
CREATE TABLE `country` (
114
`country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
115
`country` varchar(50) NOT NULL,
116
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
117
PRIMARY KEY (`country_id`)
118
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8;
119
/*!40101 SET character_set_client = @saved_cs_client */;
122
-- Table structure for table `customer`
125
DROP TABLE IF EXISTS `customer`;
126
/*!40101 SET @saved_cs_client = @@character_set_client */;
127
/*!40101 SET character_set_client = utf8 */;
128
CREATE TABLE `customer` (
129
`customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
130
`store_id` tinyint(3) unsigned NOT NULL,
131
`first_name` varchar(45) NOT NULL,
132
`last_name` varchar(45) NOT NULL,
133
`email` varchar(50) DEFAULT NULL,
134
`address_id` smallint(5) unsigned NOT NULL,
135
`active` tinyint(1) NOT NULL DEFAULT '1',
136
`create_date` datetime NOT NULL,
137
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
138
PRIMARY KEY (`customer_id`),
139
KEY `idx_fk_store_id` (`store_id`),
140
KEY `idx_fk_address_id` (`address_id`),
141
KEY `idx_last_name` (`last_name`),
142
CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,
143
CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
144
) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8;
145
/*!40101 SET character_set_client = @saved_cs_client */;
146
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
147
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
148
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
149
/*!50003 SET character_set_client = latin1 */ ;
150
/*!50003 SET character_set_results = latin1 */ ;
151
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
152
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
153
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */ ;
155
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`%`*/ /*!50003 TRIGGER customer_create_date BEFORE INSERT ON customer
156
FOR EACH ROW SET NEW.create_date = NOW() */;;
158
/*!50003 SET sql_mode = @saved_sql_mode */ ;
159
/*!50003 SET character_set_client = @saved_cs_client */ ;
160
/*!50003 SET character_set_results = @saved_cs_results */ ;
161
/*!50003 SET collation_connection = @saved_col_connection */ ;
164
-- Temporary table structure for view `customer_list`
167
DROP TABLE IF EXISTS `customer_list`;
168
/*!50001 DROP VIEW IF EXISTS `customer_list`*/;
169
SET @saved_cs_client = @@character_set_client;
170
SET character_set_client = utf8;
171
/*!50001 CREATE TABLE `customer_list` (
172
`ID` smallint(5) unsigned,
174
`address` varchar(50),
175
`zip code` varchar(10),
178
`country` varchar(50),
180
`SID` tinyint(3) unsigned
182
SET character_set_client = @saved_cs_client;
185
-- Table structure for table `film`
188
DROP TABLE IF EXISTS `film`;
189
/*!40101 SET @saved_cs_client = @@character_set_client */;
190
/*!40101 SET character_set_client = utf8 */;
191
CREATE TABLE `film` (
192
`film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
193
`title` varchar(255) NOT NULL,
195
`release_year` year(4) DEFAULT NULL,
196
`language_id` tinyint(3) unsigned NOT NULL,
197
`original_language_id` tinyint(3) unsigned DEFAULT NULL,
198
`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
199
`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
200
`length` smallint(5) unsigned DEFAULT NULL,
201
`replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
202
`rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
203
`special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
204
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
205
PRIMARY KEY (`film_id`),
206
KEY `idx_title` (`title`),
207
KEY `idx_fk_language_id` (`language_id`),
208
KEY `idx_fk_original_language_id` (`original_language_id`),
209
CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
210
CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
211
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;
212
/*!40101 SET character_set_client = @saved_cs_client */;
213
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
214
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
215
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
216
/*!50003 SET character_set_client = latin1 */ ;
217
/*!50003 SET character_set_results = latin1 */ ;
218
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
219
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
220
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */ ;
222
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`%`*/ /*!50003 TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
223
INSERT INTO film_text (film_id, title, description)
224
VALUES (new.film_id, new.title, new.description);
227
/*!50003 SET sql_mode = @saved_sql_mode */ ;
228
/*!50003 SET character_set_client = @saved_cs_client */ ;
229
/*!50003 SET character_set_results = @saved_cs_results */ ;
230
/*!50003 SET collation_connection = @saved_col_connection */ ;
231
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
232
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
233
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
234
/*!50003 SET character_set_client = latin1 */ ;
235
/*!50003 SET character_set_results = latin1 */ ;
236
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
237
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
238
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */ ;
240
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`%`*/ /*!50003 TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
241
IF (old.title != new.title) or (old.description != new.description)
245
description=new.description,
247
WHERE film_id=old.film_id;
251
/*!50003 SET sql_mode = @saved_sql_mode */ ;
252
/*!50003 SET character_set_client = @saved_cs_client */ ;
253
/*!50003 SET character_set_results = @saved_cs_results */ ;
254
/*!50003 SET collation_connection = @saved_col_connection */ ;
255
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
256
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
257
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
258
/*!50003 SET character_set_client = latin1 */ ;
259
/*!50003 SET character_set_results = latin1 */ ;
260
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
261
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
262
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */ ;
264
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`%`*/ /*!50003 TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
265
DELETE FROM film_text WHERE film_id = old.film_id;
268
/*!50003 SET sql_mode = @saved_sql_mode */ ;
269
/*!50003 SET character_set_client = @saved_cs_client */ ;
270
/*!50003 SET character_set_results = @saved_cs_results */ ;
271
/*!50003 SET collation_connection = @saved_col_connection */ ;
274
-- Table structure for table `film_actor`
277
DROP TABLE IF EXISTS `film_actor`;
278
/*!40101 SET @saved_cs_client = @@character_set_client */;
279
/*!40101 SET character_set_client = utf8 */;
280
CREATE TABLE `film_actor` (
281
`actor_id` smallint(5) unsigned NOT NULL,
282
`film_id` smallint(5) unsigned NOT NULL,
283
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
284
PRIMARY KEY (`actor_id`,`film_id`),
285
KEY `idx_fk_film_id` (`film_id`),
286
CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
287
CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
288
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
289
/*!40101 SET character_set_client = @saved_cs_client */;
292
-- Table structure for table `film_category`
295
DROP TABLE IF EXISTS `film_category`;
296
/*!40101 SET @saved_cs_client = @@character_set_client */;
297
/*!40101 SET character_set_client = utf8 */;
298
CREATE TABLE `film_category` (
299
`film_id` smallint(5) unsigned NOT NULL,
300
`category_id` tinyint(3) unsigned NOT NULL,
301
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
302
PRIMARY KEY (`film_id`,`category_id`),
303
KEY `fk_film_category_category` (`category_id`),
304
CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,
305
CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE
306
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
307
/*!40101 SET character_set_client = @saved_cs_client */;
310
-- Temporary table structure for view `film_list`
313
DROP TABLE IF EXISTS `film_list`;
314
/*!50001 DROP VIEW IF EXISTS `film_list`*/;
315
SET @saved_cs_client = @@character_set_client;
316
SET character_set_client = utf8;
317
/*!50001 CREATE TABLE `film_list` (
318
`FID` smallint(5) unsigned,
319
`title` varchar(255),
321
`category` varchar(25),
322
`price` decimal(4,2),
323
`length` smallint(5) unsigned,
324
`rating` enum('G','PG','PG-13','R','NC-17'),
325
`actors` varchar(341)
327
SET character_set_client = @saved_cs_client;
330
-- Table structure for table `film_text`
333
DROP TABLE IF EXISTS `film_text`;
334
/*!40101 SET @saved_cs_client = @@character_set_client */;
335
/*!40101 SET character_set_client = utf8 */;
336
CREATE TABLE `film_text` (
337
`film_id` smallint(6) NOT NULL,
338
`title` varchar(255) NOT NULL,
340
PRIMARY KEY (`film_id`),
341
FULLTEXT KEY `idx_title_description` (`title`,`description`)
342
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
343
/*!40101 SET character_set_client = @saved_cs_client */;
346
-- Table structure for table `inventory`
349
DROP TABLE IF EXISTS `inventory`;
350
/*!40101 SET @saved_cs_client = @@character_set_client */;
351
/*!40101 SET character_set_client = utf8 */;
352
CREATE TABLE `inventory` (
353
`inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
354
`film_id` smallint(5) unsigned NOT NULL,
355
`store_id` tinyint(3) unsigned NOT NULL,
356
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
357
PRIMARY KEY (`inventory_id`),
358
KEY `idx_fk_film_id` (`film_id`),
359
KEY `idx_store_id_film_id` (`store_id`,`film_id`),
360
CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
361
CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
362
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8;
363
/*!40101 SET character_set_client = @saved_cs_client */;
366
-- Table structure for table `language`
369
DROP TABLE IF EXISTS `language`;
370
/*!40101 SET @saved_cs_client = @@character_set_client */;
371
/*!40101 SET character_set_client = utf8 */;
372
CREATE TABLE `language` (
373
`language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
374
`name` char(20) NOT NULL,
375
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
376
PRIMARY KEY (`language_id`)
377
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
378
/*!40101 SET character_set_client = @saved_cs_client */;
381
-- Temporary table structure for view `nicer_but_slower_film_list`
384
DROP TABLE IF EXISTS `nicer_but_slower_film_list`;
385
/*!50001 DROP VIEW IF EXISTS `nicer_but_slower_film_list`*/;
386
SET @saved_cs_client = @@character_set_client;
387
SET character_set_client = utf8;
388
/*!50001 CREATE TABLE `nicer_but_slower_film_list` (
389
`FID` smallint(5) unsigned,
390
`title` varchar(255),
392
`category` varchar(25),
393
`price` decimal(4,2),
394
`length` smallint(5) unsigned,
395
`rating` enum('G','PG','PG-13','R','NC-17'),
396
`actors` varchar(341)
398
SET character_set_client = @saved_cs_client;
401
-- Table structure for table `payment`
404
DROP TABLE IF EXISTS `payment`;
405
/*!40101 SET @saved_cs_client = @@character_set_client */;
406
/*!40101 SET character_set_client = utf8 */;
407
CREATE TABLE `payment` (
408
`payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
409
`customer_id` smallint(5) unsigned NOT NULL,
410
`staff_id` tinyint(3) unsigned NOT NULL,
411
`rental_id` int(11) DEFAULT NULL,
412
`amount` decimal(5,2) NOT NULL,
413
`payment_date` datetime NOT NULL,
414
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
415
PRIMARY KEY (`payment_id`),
416
KEY `idx_fk_staff_id` (`staff_id`),
417
KEY `idx_fk_customer_id` (`customer_id`),
418
KEY `fk_payment_rental` (`rental_id`),
419
CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
420
CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
421
CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
422
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
423
/*!40101 SET character_set_client = @saved_cs_client */;
424
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
425
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
426
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
427
/*!50003 SET character_set_client = latin1 */ ;
428
/*!50003 SET character_set_results = latin1 */ ;
429
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
430
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
431
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */ ;
433
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`%`*/ /*!50003 TRIGGER payment_date BEFORE INSERT ON payment
434
FOR EACH ROW SET NEW.payment_date = NOW() */;;
436
/*!50003 SET sql_mode = @saved_sql_mode */ ;
437
/*!50003 SET character_set_client = @saved_cs_client */ ;
438
/*!50003 SET character_set_results = @saved_cs_results */ ;
439
/*!50003 SET collation_connection = @saved_col_connection */ ;
442
-- Table structure for table `rental`
445
DROP TABLE IF EXISTS `rental`;
446
/*!40101 SET @saved_cs_client = @@character_set_client */;
447
/*!40101 SET character_set_client = utf8 */;
448
CREATE TABLE `rental` (
449
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
450
`rental_date` datetime NOT NULL,
451
`inventory_id` mediumint(8) unsigned NOT NULL,
452
`customer_id` smallint(5) unsigned NOT NULL,
453
`return_date` datetime DEFAULT NULL,
454
`staff_id` tinyint(3) unsigned NOT NULL,
455
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
456
PRIMARY KEY (`rental_id`),
457
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
458
KEY `idx_fk_inventory_id` (`inventory_id`),
459
KEY `idx_fk_customer_id` (`customer_id`),
460
KEY `idx_fk_staff_id` (`staff_id`),
461
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
462
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
463
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
464
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
465
/*!40101 SET character_set_client = @saved_cs_client */;
466
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
467
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
468
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
469
/*!50003 SET character_set_client = latin1 */ ;
470
/*!50003 SET character_set_results = latin1 */ ;
471
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
472
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
473
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER' */ ;
475
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`%`*/ /*!50003 TRIGGER rental_date BEFORE INSERT ON rental
476
FOR EACH ROW SET NEW.rental_date = NOW() */;;
478
/*!50003 SET sql_mode = @saved_sql_mode */ ;
479
/*!50003 SET character_set_client = @saved_cs_client */ ;
480
/*!50003 SET character_set_results = @saved_cs_results */ ;
481
/*!50003 SET collation_connection = @saved_col_connection */ ;
484
-- Temporary table structure for view `sales_by_film_category`
487
DROP TABLE IF EXISTS `sales_by_film_category`;
488
/*!50001 DROP VIEW IF EXISTS `sales_by_film_category`*/;
489
SET @saved_cs_client = @@character_set_client;
490
SET character_set_client = utf8;
491
/*!50001 CREATE TABLE `sales_by_film_category` (
492
`category` varchar(25),
493
`total_sales` decimal(27,2)
495
SET character_set_client = @saved_cs_client;
498
-- Temporary table structure for view `sales_by_store`
501
DROP TABLE IF EXISTS `sales_by_store`;
502
/*!50001 DROP VIEW IF EXISTS `sales_by_store`*/;
503
SET @saved_cs_client = @@character_set_client;
504
SET character_set_client = utf8;
505
/*!50001 CREATE TABLE `sales_by_store` (
506
`store` varchar(101),
507
`manager` varchar(91),
508
`total_sales` decimal(27,2)
510
SET character_set_client = @saved_cs_client;
513
-- Table structure for table `staff`
516
DROP TABLE IF EXISTS `staff`;
517
/*!40101 SET @saved_cs_client = @@character_set_client */;
518
/*!40101 SET character_set_client = utf8 */;
519
CREATE TABLE `staff` (
520
`staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
521
`first_name` varchar(45) NOT NULL,
522
`last_name` varchar(45) NOT NULL,
523
`address_id` smallint(5) unsigned NOT NULL,
525
`email` varchar(50) DEFAULT NULL,
526
`store_id` tinyint(3) unsigned NOT NULL,
527
`active` tinyint(1) NOT NULL DEFAULT '1',
528
`username` varchar(16) NOT NULL,
529
`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
530
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
531
PRIMARY KEY (`staff_id`),
532
KEY `idx_fk_store_id` (`store_id`),
533
KEY `idx_fk_address_id` (`address_id`),
534
CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
535
CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
536
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
537
/*!40101 SET character_set_client = @saved_cs_client */;
540
-- Temporary table structure for view `staff_list`
543
DROP TABLE IF EXISTS `staff_list`;
544
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;
545
SET @saved_cs_client = @@character_set_client;
546
SET character_set_client = utf8;
547
/*!50001 CREATE TABLE `staff_list` (
548
`ID` tinyint(3) unsigned,
550
`address` varchar(50),
551
`zip code` varchar(10),
554
`country` varchar(50),
555
`SID` tinyint(3) unsigned
557
SET character_set_client = @saved_cs_client;
560
-- Table structure for table `store`
563
DROP TABLE IF EXISTS `store`;
564
/*!40101 SET @saved_cs_client = @@character_set_client */;
565
/*!40101 SET character_set_client = utf8 */;
566
CREATE TABLE `store` (
567
`store_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
568
`manager_staff_id` tinyint(3) unsigned NOT NULL,
569
`address_id` smallint(5) unsigned NOT NULL,
570
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
571
PRIMARY KEY (`store_id`),
572
UNIQUE KEY `idx_unique_manager` (`manager_staff_id`),
573
KEY `idx_fk_address_id` (`address_id`),
574
CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
575
CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
576
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
577
/*!40101 SET character_set_client = @saved_cs_client */;
580
-- Final view structure for view `actor_info`
583
/*!50001 DROP TABLE IF EXISTS `actor_info`*/;
584
/*!50001 DROP VIEW IF EXISTS `actor_info`*/;
585
/*!50001 SET @saved_cs_client = @@character_set_client */;
586
/*!50001 SET @saved_cs_results = @@character_set_results */;
587
/*!50001 SET @saved_col_connection = @@collation_connection */;
588
/*!50001 SET character_set_client = latin1 */;
589
/*!50001 SET character_set_results = latin1 */;
590
/*!50001 SET collation_connection = latin1_swedish_ci */;
591
/*!50001 CREATE ALGORITHM=UNDEFINED */
592
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY INVOKER */
593
/*!50001 VIEW `actor_info` AS select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`film` `f` join `film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `film_actor` `fa` on((`f`.`film_id` = `fa`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`actor` `a` left join `film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name` */;
594
/*!50001 SET character_set_client = @saved_cs_client */;
595
/*!50001 SET character_set_results = @saved_cs_results */;
596
/*!50001 SET collation_connection = @saved_col_connection */;
599
-- Final view structure for view `customer_list`
602
/*!50001 DROP TABLE IF EXISTS `customer_list`*/;
603
/*!50001 DROP VIEW IF EXISTS `customer_list`*/;
604
/*!50001 SET @saved_cs_client = @@character_set_client */;
605
/*!50001 SET @saved_cs_results = @@character_set_results */;
606
/*!50001 SET @saved_col_connection = @@collation_connection */;
607
/*!50001 SET character_set_client = latin1 */;
608
/*!50001 SET character_set_results = latin1 */;
609
/*!50001 SET collation_connection = latin1_swedish_ci */;
610
/*!50001 CREATE ALGORITHM=UNDEFINED */
611
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY DEFINER */
612
/*!50001 VIEW `customer_list` AS select `cu`.`customer_id` AS `ID`,concat(`cu`.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`city`.`city` AS `city`,`country`.`country` AS `country`,if(`cu`.`active`,_utf8'active',_utf8'') AS `notes`,`cu`.`store_id` AS `SID` from (((`customer` `cu` join `address` `a` on((`cu`.`address_id` = `a`.`address_id`))) join `city` on((`a`.`city_id` = `city`.`city_id`))) join `country` on((`city`.`country_id` = `country`.`country_id`))) */;
613
/*!50001 SET character_set_client = @saved_cs_client */;
614
/*!50001 SET character_set_results = @saved_cs_results */;
615
/*!50001 SET collation_connection = @saved_col_connection */;
618
-- Final view structure for view `film_list`
621
/*!50001 DROP TABLE IF EXISTS `film_list`*/;
622
/*!50001 DROP VIEW IF EXISTS `film_list`*/;
623
/*!50001 SET @saved_cs_client = @@character_set_client */;
624
/*!50001 SET @saved_cs_results = @@character_set_results */;
625
/*!50001 SET @saved_col_connection = @@collation_connection */;
626
/*!50001 SET character_set_client = latin1 */;
627
/*!50001 SET character_set_results = latin1 */;
628
/*!50001 SET collation_connection = latin1_swedish_ci */;
629
/*!50001 CREATE ALGORITHM=UNDEFINED */
630
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY DEFINER */
631
/*!50001 VIEW `film_list` AS select `film`.`film_id` AS `FID`,`film`.`title` AS `title`,`film`.`description` AS `description`,`category`.`name` AS `category`,`film`.`rental_rate` AS `price`,`film`.`length` AS `length`,`film`.`rating` AS `rating`,group_concat(concat(`actor`.`first_name`,_utf8' ',`actor`.`last_name`) separator ', ') AS `actors` from ((((`category` left join `film_category` on((`category`.`category_id` = `film_category`.`category_id`))) left join `film` on((`film_category`.`film_id` = `film`.`film_id`))) join `film_actor` on((`film`.`film_id` = `film_actor`.`film_id`))) join `actor` on((`film_actor`.`actor_id` = `actor`.`actor_id`))) group by `film`.`film_id` */;
632
/*!50001 SET character_set_client = @saved_cs_client */;
633
/*!50001 SET character_set_results = @saved_cs_results */;
634
/*!50001 SET collation_connection = @saved_col_connection */;
637
-- Final view structure for view `nicer_but_slower_film_list`
640
/*!50001 DROP TABLE IF EXISTS `nicer_but_slower_film_list`*/;
641
/*!50001 DROP VIEW IF EXISTS `nicer_but_slower_film_list`*/;
642
/*!50001 SET @saved_cs_client = @@character_set_client */;
643
/*!50001 SET @saved_cs_results = @@character_set_results */;
644
/*!50001 SET @saved_col_connection = @@collation_connection */;
645
/*!50001 SET character_set_client = latin1 */;
646
/*!50001 SET character_set_results = latin1 */;
647
/*!50001 SET collation_connection = latin1_swedish_ci */;
648
/*!50001 CREATE ALGORITHM=UNDEFINED */
649
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY DEFINER */
650
/*!50001 VIEW `nicer_but_slower_film_list` AS select `film`.`film_id` AS `FID`,`film`.`title` AS `title`,`film`.`description` AS `description`,`category`.`name` AS `category`,`film`.`rental_rate` AS `price`,`film`.`length` AS `length`,`film`.`rating` AS `rating`,group_concat(concat(concat(ucase(substr(`actor`.`first_name`,1,1)),lcase(substr(`actor`.`first_name`,2,length(`actor`.`first_name`))),_utf8' ',concat(ucase(substr(`actor`.`last_name`,1,1)),lcase(substr(`actor`.`last_name`,2,length(`actor`.`last_name`)))))) separator ', ') AS `actors` from ((((`category` left join `film_category` on((`category`.`category_id` = `film_category`.`category_id`))) left join `film` on((`film_category`.`film_id` = `film`.`film_id`))) join `film_actor` on((`film`.`film_id` = `film_actor`.`film_id`))) join `actor` on((`film_actor`.`actor_id` = `actor`.`actor_id`))) group by `film`.`film_id` */;
651
/*!50001 SET character_set_client = @saved_cs_client */;
652
/*!50001 SET character_set_results = @saved_cs_results */;
653
/*!50001 SET collation_connection = @saved_col_connection */;
656
-- Final view structure for view `sales_by_film_category`
659
/*!50001 DROP TABLE IF EXISTS `sales_by_film_category`*/;
660
/*!50001 DROP VIEW IF EXISTS `sales_by_film_category`*/;
661
/*!50001 SET @saved_cs_client = @@character_set_client */;
662
/*!50001 SET @saved_cs_results = @@character_set_results */;
663
/*!50001 SET @saved_col_connection = @@collation_connection */;
664
/*!50001 SET character_set_client = latin1 */;
665
/*!50001 SET character_set_results = latin1 */;
666
/*!50001 SET collation_connection = latin1_swedish_ci */;
667
/*!50001 CREATE ALGORITHM=UNDEFINED */
668
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY DEFINER */
669
/*!50001 VIEW `sales_by_film_category` AS select `c`.`name` AS `category`,sum(`p`.`amount`) AS `total_sales` from (((((`payment` `p` join `rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `film` `f` on((`i`.`film_id` = `f`.`film_id`))) join `film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `c`.`name` order by sum(`p`.`amount`) desc */;
670
/*!50001 SET character_set_client = @saved_cs_client */;
671
/*!50001 SET character_set_results = @saved_cs_results */;
672
/*!50001 SET collation_connection = @saved_col_connection */;
675
-- Final view structure for view `sales_by_store`
678
/*!50001 DROP TABLE IF EXISTS `sales_by_store`*/;
679
/*!50001 DROP VIEW IF EXISTS `sales_by_store`*/;
680
/*!50001 SET @saved_cs_client = @@character_set_client */;
681
/*!50001 SET @saved_cs_results = @@character_set_results */;
682
/*!50001 SET @saved_col_connection = @@collation_connection */;
683
/*!50001 SET character_set_client = latin1 */;
684
/*!50001 SET character_set_results = latin1 */;
685
/*!50001 SET collation_connection = latin1_swedish_ci */;
686
/*!50001 CREATE ALGORITHM=UNDEFINED */
687
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY DEFINER */
688
/*!50001 VIEW `sales_by_store` AS select concat(`c`.`city`,_utf8',',`cy`.`country`) AS `store`,concat(`m`.`first_name`,_utf8' ',`m`.`last_name`) AS `manager`,sum(`p`.`amount`) AS `total_sales` from (((((((`payment` `p` join `rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `store` `s` on((`i`.`store_id` = `s`.`store_id`))) join `address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `city` `c` on((`a`.`city_id` = `c`.`city_id`))) join `country` `cy` on((`c`.`country_id` = `cy`.`country_id`))) join `staff` `m` on((`s`.`manager_staff_id` = `m`.`staff_id`))) group by `s`.`store_id` order by `cy`.`country`,`c`.`city` */;
689
/*!50001 SET character_set_client = @saved_cs_client */;
690
/*!50001 SET character_set_results = @saved_cs_results */;
691
/*!50001 SET collation_connection = @saved_col_connection */;
694
-- Final view structure for view `staff_list`
697
/*!50001 DROP TABLE IF EXISTS `staff_list`*/;
698
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;
699
/*!50001 SET @saved_cs_client = @@character_set_client */;
700
/*!50001 SET @saved_cs_results = @@character_set_results */;
701
/*!50001 SET @saved_col_connection = @@collation_connection */;
702
/*!50001 SET character_set_client = latin1 */;
703
/*!50001 SET character_set_results = latin1 */;
704
/*!50001 SET collation_connection = latin1_swedish_ci */;
705
/*!50001 CREATE ALGORITHM=UNDEFINED */
706
/*!50013 DEFINER=`msandbox`@`%` SQL SECURITY DEFINER */
707
/*!50001 VIEW `staff_list` AS select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8' ',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`city`.`city` AS `city`,`country`.`country` AS `country`,`s`.`store_id` AS `SID` from (((`staff` `s` join `address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `city` on((`a`.`city_id` = `city`.`city_id`))) join `country` on((`city`.`country_id` = `country`.`country_id`))) */;
708
/*!50001 SET character_set_client = @saved_cs_client */;
709
/*!50001 SET character_set_results = @saved_cs_results */;
710
/*!50001 SET collation_connection = @saved_col_connection */;
711
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
713
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
714
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
715
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
716
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
717
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
718
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
719
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
721
-- Dump completed on 2011-05-21 6:58:19
added
removed
t/lib/samples/mysqldump-no-data/one-db.txt
