1 -- MySQL dump 10.13 Distrib 5.1.49, for debian-linux-gnu (x86_64)
3 -- Host: localhost Database: sklad
4 -- ------------------------------------------------------
5 -- Server version 5.1.49-3
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 `category`
22 DROP TABLE IF EXISTS `category`
;
23 /*!40101 SET @saved_cs_client = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `category`
(
26 `category_id`
int(11) NOT NULL AUTO_INCREMENT
,
27 `category_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
28 PRIMARY KEY (`category_id`
),
29 UNIQUE KEY `category_name`
(`category_name`
)
30 ) ENGINE
=InnoDB AUTO_INCREMENT
=14 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
31 /*!40101 SET character_set_client = @saved_cs_client */;
34 -- Dumping data for table `category`
37 LOCK TABLES `category`
WRITE;
38 /*!40000 ALTER TABLE `category` DISABLE KEYS */;
39 INSERT INTO `category`
VALUES (3,'koren'),(8,'koren/a'),(10,'koren/a/a'),(11,'koren/a/c'),(9,'koren/a/d'),(12,'koren/a/o'),(13,'novy/x/y/z'),(1,'picovinky'),(2,'Tezka technika, Traktory, etc..');
40 /*!40000 ALTER TABLE `category` ENABLE KEYS */;
44 -- Table structure for table `group`
47 DROP TABLE IF EXISTS `
group`
;
48 /*!40101 SET @saved_cs_client = @@character_set_client */;
49 /*!40101 SET character_set_client = utf8 */;
50 CREATE TABLE `
group`
(
51 `group_id`
int(11) NOT NULL AUTO_INCREMENT
,
52 `group_key`
char(23) COLLATE utf8_czech_ci
NOT NULL,
53 `group_value`
char(23) COLLATE utf8_czech_ci
NOT NULL,
54 PRIMARY KEY (`group_id`
,`group_key`
)
55 ) ENGINE
=InnoDB AUTO_INCREMENT
=3 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
56 /*!40101 SET character_set_client = @saved_cs_client */;
59 -- Dumping data for table `group`
62 LOCK TABLES `
group`
WRITE;
63 /*!40000 ALTER TABLE `group` DISABLE KEYS */;
64 INSERT INTO `
group`
VALUES (0,'default','rw'),(0,'name','admin'),(2,'name','employee');
65 /*!40000 ALTER TABLE `group` ENABLE KEYS */;
69 -- Table structure for table `producer`
72 DROP TABLE IF EXISTS `producer`
;
73 /*!40101 SET @saved_cs_client = @@character_set_client */;
74 /*!40101 SET character_set_client = utf8 */;
75 CREATE TABLE `producer`
(
76 `producer_id`
int(11) NOT NULL AUTO_INCREMENT
,
77 `producer_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
78 `producer_note`
varchar(512) COLLATE utf8_czech_ci
NOT NULL,
79 PRIMARY KEY (`producer_id`
),
80 UNIQUE KEY `producer_name`
(`producer_name`
)
81 ) ENGINE
=InnoDB AUTO_INCREMENT
=3 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
82 /*!40101 SET character_set_client = @saved_cs_client */;
85 -- Dumping data for table `producer`
88 LOCK TABLES `producer`
WRITE;
89 /*!40000 ALTER TABLE `producer` DISABLE KEYS */;
90 INSERT INTO `producer`
VALUES (1,'C.C.M.E','Czech Company Making Everything'),(2,'AchAchne Labz','Ach ne');
91 /*!40000 ALTER TABLE `producer` ENABLE KEYS */;
95 -- Table structure for table `room`
98 DROP TABLE IF EXISTS `room`
;
99 /*!40101 SET @saved_cs_client = @@character_set_client */;
100 /*!40101 SET character_set_client = utf8 */;
101 CREATE TABLE `room`
(
102 `room_id`
int(11) NOT NULL AUTO_INCREMENT
,
103 `room_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
104 `room_author`
int(11) NOT NULL DEFAULT '0',
105 `room_descript`
text COLLATE utf8_czech_ci
NOT NULL,
106 PRIMARY KEY (`room_id`
),
107 UNIQUE KEY `room_name`
(`room_name`
),
108 KEY `user_id`
(`room_author`
)
109 ) ENGINE
=InnoDB AUTO_INCREMENT
=2 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
110 /*!40101 SET character_set_client = @saved_cs_client */;
113 -- Dumping data for table `room`
116 LOCK TABLES `room`
WRITE;
117 /*!40000 ALTER TABLE `room` DISABLE KEYS */;
118 INSERT INTO `room`
VALUES (1,'BarvyLaky',0,'LakyBarvy');
119 /*!40000 ALTER TABLE `room` ENABLE KEYS */;
123 -- Table structure for table `status`
126 DROP TABLE IF EXISTS `status`
;
127 /*!40101 SET @saved_cs_client = @@character_set_client */;
128 /*!40101 SET character_set_client = utf8 */;
129 CREATE TABLE `status`
(
130 `status_id`
int(11) NOT NULL AUTO_INCREMENT
,
131 `status_name`
varchar(16) COLLATE utf8_czech_ci
NOT NULL,
132 PRIMARY KEY (`status_id`
),
133 UNIQUE KEY `status_name`
(`status_name`
)
134 ) ENGINE
=InnoDB AUTO_INCREMENT
=5 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
135 /*!40101 SET character_set_client = @saved_cs_client */;
138 -- Dumping data for table `status`
141 LOCK TABLES `status`
WRITE;
142 /*!40000 ALTER TABLE `status` DISABLE KEYS */;
143 INSERT INTO `status`
VALUES (0,'deleted'),(4,'destroyed'),(2,'placed'),(3,'sold'),(1,'stored');
144 /*!40000 ALTER TABLE `status` ENABLE KEYS */;
148 -- Table structure for table `user`
151 DROP TABLE IF EXISTS `
user`
;
152 /*!40101 SET @saved_cs_client = @@character_set_client */;
153 /*!40101 SET character_set_client = utf8 */;
154 CREATE TABLE `
user`
(
155 `user_id`
int(11) NOT NULL DEFAULT '0',
156 `user_permit`
enum('user','admin') COLLATE utf8_czech_ci
NOT NULL DEFAULT 'user',
157 PRIMARY KEY (`user_id`
)
158 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
159 /*!40101 SET character_set_client = @saved_cs_client */;
162 -- Dumping data for table `user`
165 LOCK TABLES `
user`
WRITE;
166 /*!40000 ALTER TABLE `user` DISABLE KEYS */;
167 /*!40000 ALTER TABLE `user` ENABLE KEYS */;
171 -- Table structure for table `vendor`
174 DROP TABLE IF EXISTS `vendor`
;
175 /*!40101 SET @saved_cs_client = @@character_set_client */;
176 /*!40101 SET character_set_client = utf8 */;
177 CREATE TABLE `vendor`
(
178 `vendor_id`
int(11) NOT NULL AUTO_INCREMENT
,
179 `vendor_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
180 `vendor_note`
varchar(256) COLLATE utf8_czech_ci
NOT NULL,
181 PRIMARY KEY (`vendor_id`
),
182 UNIQUE KEY `vendor_name`
(`vendor_name`
)
183 ) ENGINE
=InnoDB AUTO_INCREMENT
=3 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
184 /*!40101 SET character_set_client = @saved_cs_client */;
187 -- Dumping data for table `vendor`
190 LOCK TABLES `vendor`
WRITE;
191 /*!40000 ALTER TABLE `vendor` DISABLE KEYS */;
192 INSERT INTO `vendor`
VALUES (1,'C.C.S.E','Czech Company Selling Everything'),(2,'Trolo Cybernetix','trololo');
193 /*!40000 ALTER TABLE `vendor` ENABLE KEYS */;
195 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
197 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
198 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
199 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
200 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
201 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
202 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
203 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
205 -- Dump completed on 2012-04-11 13:48:54
207 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
208 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
209 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
210 /*!40101 SET NAMES utf8 */;
211 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
212 /*!40103 SET TIME_ZONE='+00:00' */;
213 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
214 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
215 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
216 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
217 DROP TABLE IF EXISTS `item`
;
218 /*!40101 SET @saved_cs_client = @@character_set_client */;
219 /*!40101 SET character_set_client = utf8 */;
220 CREATE TABLE `item`
(
221 `item_id`
int(11) NOT NULL AUTO_INCREMENT
,
222 `model_id`
int(11) NOT NULL,
223 `vendor_id`
int(11) NOT NULL,
224 `item_serial`
varchar(128) COLLATE utf8_czech_ci
NOT NULL,
225 `item_quantity`
int(11) NOT NULL DEFAULT '1',
226 `room_id`
int(11) NOT NULL DEFAULT '1',
227 `status_id`
int(11) NOT NULL DEFAULT '1',
228 `item_price_in`
decimal(9,2) NOT NULL DEFAULT '0.00',
229 `item_price_out`
decimal(9,2) DEFAULT NULL,
230 `item_customer`
int(11) DEFAULT NULL,
231 `item_date_bought`
date NOT NULL DEFAULT '0000-00-00',
232 `item_date_sold`
date DEFAULT '0000-00-00',
233 `item_note`
varchar(512) COLLATE utf8_czech_ci
DEFAULT NULL,
234 `item_author`
int(11) NOT NULL,
235 `item_valid_till`
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
236 `item_valid_from`
timestamp NULL DEFAULT CURRENT_TIMESTAMP,
237 PRIMARY KEY (`item_id`
,`item_valid_till`
),
238 UNIQUE KEY `item_serial_item_valid_till`
(`item_serial`
,`item_valid_till`
),
239 KEY `vendor_id`
(`vendor_id`
),
240 KEY `model_id`
(`model_id`
),
241 KEY `status_id`
(`status_id`
),
242 KEY `room_id`
(`room_id`
),
243 CONSTRAINT `item_ibfk_6`
FOREIGN KEY (`vendor_id`
) REFERENCES `vendor`
(`vendor_id`
),
244 CONSTRAINT `item_ibfk_7`
FOREIGN KEY (`model_id`
) REFERENCES `model`
(`model_id`
),
245 CONSTRAINT `item_ibfk_8`
FOREIGN KEY (`status_id`
) REFERENCES `status`
(`status_id`
),
246 CONSTRAINT `item_ibfk_9`
FOREIGN KEY (`room_id`
) REFERENCES `room`
(`room_id`
)
247 ) ENGINE
=InnoDB AUTO_INCREMENT
=43 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
248 /*!40101 SET character_set_client = @saved_cs_client */;
249 DROP TABLE IF EXISTS `model`
;
250 /*!40101 SET @saved_cs_client = @@character_set_client */;
251 /*!40101 SET character_set_client = utf8 */;
252 CREATE TABLE `model`
(
253 `model_id`
int(11) NOT NULL AUTO_INCREMENT
,
254 `model_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
255 `producer_id`
int(11) NOT NULL DEFAULT '0',
256 `category_id`
int(11) NOT NULL DEFAULT '0',
257 `model_barcode`
varchar(128) COLLATE utf8_czech_ci
NOT NULL,
258 `model_countable`
int(1) NOT NULL DEFAULT '1',
259 `model_price_in`
decimal(9,2) DEFAULT NULL,
260 `model_price_out`
decimal(9,2) DEFAULT NULL,
261 `model_reserve`
int(11) unsigned
DEFAULT '0',
262 `model_eshop_hide`
int(1) unsigned
NOT NULL DEFAULT '0',
263 `model_descript`
varchar(1024) COLLATE utf8_czech_ci
NOT NULL,
264 PRIMARY KEY (`model_id`
),
265 UNIQUE KEY `model_barcode`
(`model_barcode`
),
266 KEY `category_id`
(`category_id`
),
267 KEY `producer_id`
(`producer_id`
),
268 CONSTRAINT `model_ibfk_1`
FOREIGN KEY (`category_id`
) REFERENCES `category`
(`category_id`
),
269 CONSTRAINT `model_ibfk_2`
FOREIGN KEY (`producer_id`
) REFERENCES `producer`
(`producer_id`
)
270 ) ENGINE
=InnoDB AUTO_INCREMENT
=7 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
271 /*!40101 SET character_set_client = @saved_cs_client */;
272 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
274 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
275 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
276 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
277 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
278 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
279 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
280 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;