· 8 years ago · Mar 08, 2018, 07:34 PM
1SET FOREIGN_KEY_CHECKS=0;
2SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
3SET time_zone = "+00:00";
4
5DROP TABLE IF EXISTS `pages`;
6CREATE TABLE IF NOT EXISTS `pages` (
7 `id` int(11) NOT NULL AUTO_INCREMENT,
8 `title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
9 `content` text COLLATE utf8_unicode_ci NOT NULL,
10 `created` datetime NOT NULL,
11 `updated` datetime NOT NULL,
12 `active` tinyint(4) NOT NULL DEFAULT '1',
13 PRIMARY KEY (`id`)
14) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;
15
16INSERT INTO `pages` (`id`, `title`, `content`, `created`, `updated`, `active`) VALUES
17(1, 'About us', 'Strona o nas ....', '2011-11-08 00:00:00', '0000-00-00 00:00:00', 1);
18
19DROP TABLE IF EXISTS `products`;
20CREATE TABLE IF NOT EXISTS `products` (
21 `id` int(11) NOT NULL AUTO_INCREMENT,
22 `user_id` int(11) NOT NULL,
23 `name` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nazwa produktu',
24 `description` text COLLATE utf8_unicode_ci NOT NULL,
25 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data utworzenia produktu w systemie',
26 `updated` timestamp NULL DEFAULT NULL COMMENT 'Data aktualizacji produktu w systemie',
27 PRIMARY KEY (`id`),
28 KEY `user_id` (`user_id`)
29) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;
30
31INSERT INTO `products` (`id`, `user_id`, `name`, `description`, `created`, `updated`) VALUES
32(1, 1, 'Testowy produkt 33', 'jakiś ładny opis który:P tutaj będzie w CKEditorze wsadzany ', '2011-11-08 08:38:44', '2011-11-08 10:24:42'),
33(2, 1, 'Testowy produkt 44', 'jakiś ładny opis który:P tutaj będzie w CKEditorze wsadzany ', '2011-11-08 08:38:44', '2011-11-08 09:21:12'),
34(3, 1, 'Testowy produkt 55', 'jakiś ładny opis który:P tutaj będzie w CKEditorze wsadzany ', '2011-11-08 08:38:44', '2011-11-08 09:21:12'),
35(4, 1, 'Testowy produkt 22', 'jakiś ładny opis który:P tutaj będzie w CKEditorze wsadzany ', '2011-11-08 08:38:44', '2011-11-08 09:21:12'),
36(5, 1, 'Testowy produkt 11', 'jakiś ładny opis który:P tutaj będzie w CKEditorze wsadzany ', '2011-11-08 08:38:44', '2011-11-08 09:21:12'),
37(6, 1, 'Testowy produkt 99', 'jakiś ładny opis który:P tutaj będzie w CKEditorze wsadzany ', '2011-11-08 08:38:44', '2011-11-08 09:21:12');
38
39DROP TABLE IF EXISTS `product_sales`;
40CREATE TABLE IF NOT EXISTS `product_sales` (
41 `id` int(11) NOT NULL AUTO_INCREMENT,
42 `product_id` int(11) NOT NULL,
43 `startat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
44 `finishat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
45 `pricebefore` float NOT NULL,
46 `priceafter` float NOT NULL,
47 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
48 `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
49 PRIMARY KEY (`id`),
50 KEY `product_id` (`product_id`)
51) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;
52
53INSERT INTO `product_sales` (`id`, `product_id`, `startat`, `finishat`, `pricebefore`, `priceafter`, `created`, `updated`) VALUES
54(1, 1, '2011-11-07 23:00:00', '2011-11-17 23:00:00', 100, 75, '2011-11-08 10:30:18', '0000-00-00 00:00:00');
55
56DROP TABLE IF EXISTS `sales`;
57CREATE TABLE IF NOT EXISTS `sales` (
58 `id` int(11) NOT NULL AUTO_INCREMENT,
59 `user_id` int(11) DEFAULT NULL COMMENT 'użytkownik który zamawia przedmiot',
60 `product_id` int(11) DEFAULT NULL COMMENT 'Zamawiany produkt',
61 `amount` int(11) DEFAULT NULL COMMENT 'ilość zamawianych sztuk',
62 `created` datetime DEFAULT NULL,
63 `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
64 PRIMARY KEY (`id`),
65 KEY `user_id` (`user_id`),
66 KEY `product_id` (`product_id`)
67) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;
68
69INSERT INTO `sales` (`id`, `user_id`, `product_id`, `amount`, `created`, `updated`) VALUES
70(1, 1, 1, 2, '2011-11-08 00:00:00', '0000-00-00 00:00:00');
71
72DROP TABLE IF EXISTS `users`;
73CREATE TABLE IF NOT EXISTS `users` (
74 `id` int(11) NOT NULL AUTO_INCREMENT,
75 `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nazwa użytkownika',
76 `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
77 `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
78 `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
79 `lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
80 `address` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
81 `city` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
82 `postcode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
83 `role` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
84 `created` datetime NOT NULL,
85 `updated` datetime DEFAULT NULL,
86 PRIMARY KEY (`id`)
87) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
88
89INSERT INTO `users` (`id`, `username`, `password`, `email`, `firstname`, `lastname`, `address`, `city`, `postcode`, `role`, `created`, `updated`) VALUES
90(1, 'kallosz', 'edb76fdc144f135313a97a7627804b3a287d4cf0    ', 'kallosz@o2.pl', 'Patryk', 'Kala', 'Ostrzeszów', 'Ostrzeszów', '63-500', 'admin', '2011-11-08 00:00:00', '2011-11-08 12:37:11');
91
92
93ALTER TABLE `product_sales`
94 ADD CONSTRAINT `product_sales_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
95
96ALTER TABLE `sales`
97 ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
98 ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
99SET FOREIGN_KEY_CHECKS=1;