1 -- MySQL dump 10.13 Distrib 5.1.66, for debian-linux-gnu (x86_64)
3 -- Host: localhost Database: sklad
4 -- ------------------------------------------------------
5 -- Server version 5.1.66-0+squeeze1
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 `location`
72 DROP TABLE IF EXISTS `
location`
;
73 /*!40101 SET @saved_cs_client = @@character_set_client */;
74 /*!40101 SET character_set_client = utf8 */;
75 CREATE TABLE `
location`
(
76 `location_id`
int(11) NOT NULL AUTO_INCREMENT
,
77 `location_name`
varchar(23) COLLATE utf8_czech_ci
NOT NULL,
78 `location_author`
int(11) unsigned
DEFAULT NULL,
79 `location_description`
text COLLATE utf8_czech_ci
,
80 PRIMARY KEY (`location_id`
),
81 UNIQUE KEY `location_name`
(`location_name`
),
82 KEY `location_author`
(`location_author`
)
83 ) ENGINE
=InnoDB AUTO_INCREMENT
=5 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
84 /*!40101 SET character_set_client = @saved_cs_client */;
87 -- Dumping data for table `location`
90 LOCK TABLES `
location`
WRITE;
91 /*!40000 ALTER TABLE `location` DISABLE KEYS */;
92 INSERT INTO `
location`
VALUES (0,'UNIVERSE',14,'Tady je všechno co nikde neni :-)'),(4,'aaatest',0,'');
93 /*!40000 ALTER TABLE `location` ENABLE KEYS */;
97 -- Table structure for table `producer`
100 DROP TABLE IF EXISTS `producer`
;
101 /*!40101 SET @saved_cs_client = @@character_set_client */;
102 /*!40101 SET character_set_client = utf8 */;
103 CREATE TABLE `producer`
(
104 `producer_id`
int(11) NOT NULL AUTO_INCREMENT
,
105 `producer_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
106 `producer_note`
varchar(512) COLLATE utf8_czech_ci
NOT NULL,
107 PRIMARY KEY (`producer_id`
),
108 UNIQUE KEY `producer_name`
(`producer_name`
)
109 ) ENGINE
=InnoDB AUTO_INCREMENT
=3 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
110 /*!40101 SET character_set_client = @saved_cs_client */;
113 -- Dumping data for table `producer`
116 LOCK TABLES `producer`
WRITE;
117 /*!40000 ALTER TABLE `producer` DISABLE KEYS */;
118 INSERT INTO `producer`
VALUES (1,'C.C.M.E','Czech Company Making Everything'),(2,'AchAchne Labz','Ach ne');
119 /*!40000 ALTER TABLE `producer` ENABLE KEYS */;
123 -- Table structure for table `room`
126 DROP TABLE IF EXISTS `room`
;
127 /*!40101 SET @saved_cs_client = @@character_set_client */;
128 /*!40101 SET character_set_client = utf8 */;
129 CREATE TABLE `room`
(
130 `room_id`
int(11) NOT NULL AUTO_INCREMENT
,
131 `room_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
132 `room_author`
int(11) NOT NULL DEFAULT '0',
133 `room_descript`
text COLLATE utf8_czech_ci
NOT NULL,
134 PRIMARY KEY (`room_id`
),
135 UNIQUE KEY `room_name`
(`room_name`
),
136 KEY `user_id`
(`room_author`
)
137 ) ENGINE
=InnoDB AUTO_INCREMENT
=2 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
138 /*!40101 SET character_set_client = @saved_cs_client */;
141 -- Dumping data for table `room`
144 LOCK TABLES `room`
WRITE;
145 /*!40000 ALTER TABLE `room` DISABLE KEYS */;
146 INSERT INTO `room`
VALUES (1,'BarvyLaky',0,'LakyBarvy');
147 /*!40000 ALTER TABLE `room` ENABLE KEYS */;
151 -- Table structure for table `status`
154 DROP TABLE IF EXISTS `status`
;
155 /*!40101 SET @saved_cs_client = @@character_set_client */;
156 /*!40101 SET character_set_client = utf8 */;
157 CREATE TABLE `status`
(
158 `status_id`
int(11) NOT NULL AUTO_INCREMENT
,
159 `status_name`
varchar(16) COLLATE utf8_czech_ci
NOT NULL,
160 PRIMARY KEY (`status_id`
),
161 UNIQUE KEY `status_name`
(`status_name`
)
162 ) ENGINE
=InnoDB AUTO_INCREMENT
=5 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
163 /*!40101 SET character_set_client = @saved_cs_client */;
166 -- Dumping data for table `status`
169 LOCK TABLES `status`
WRITE;
170 /*!40000 ALTER TABLE `status` DISABLE KEYS */;
171 INSERT INTO `status`
VALUES (0,'deleted'),(4,'destroyed'),(2,'placed'),(3,'sold'),(1,'stored');
172 /*!40000 ALTER TABLE `status` ENABLE KEYS */;
176 -- Table structure for table `user`
179 DROP TABLE IF EXISTS `
user`
;
180 /*!40101 SET @saved_cs_client = @@character_set_client */;
181 /*!40101 SET character_set_client = utf8 */;
182 CREATE TABLE `
user`
(
183 `user_id`
int(11) NOT NULL DEFAULT '0',
184 `user_permit`
enum('user','admin') COLLATE utf8_czech_ci
NOT NULL DEFAULT 'user',
185 PRIMARY KEY (`user_id`
)
186 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
187 /*!40101 SET character_set_client = @saved_cs_client */;
190 -- Dumping data for table `user`
193 LOCK TABLES `
user`
WRITE;
194 /*!40000 ALTER TABLE `user` DISABLE KEYS */;
195 /*!40000 ALTER TABLE `user` ENABLE KEYS */;
199 -- Table structure for table `vendor`
202 DROP TABLE IF EXISTS `vendor`
;
203 /*!40101 SET @saved_cs_client = @@character_set_client */;
204 /*!40101 SET character_set_client = utf8 */;
205 CREATE TABLE `vendor`
(
206 `vendor_id`
int(11) NOT NULL AUTO_INCREMENT
,
207 `vendor_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
208 `vendor_note`
varchar(256) COLLATE utf8_czech_ci
NOT NULL,
209 PRIMARY KEY (`vendor_id`
),
210 UNIQUE KEY `vendor_name`
(`vendor_name`
)
211 ) ENGINE
=InnoDB AUTO_INCREMENT
=3 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
212 /*!40101 SET character_set_client = @saved_cs_client */;
215 -- Dumping data for table `vendor`
218 LOCK TABLES `vendor`
WRITE;
219 /*!40000 ALTER TABLE `vendor` DISABLE KEYS */;
220 INSERT INTO `vendor`
VALUES (1,'C.C.S.E','Czech Company Selling Everything'),(2,'Trolo Cybernetix','trololo');
221 /*!40000 ALTER TABLE `vendor` ENABLE KEYS */;
223 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
225 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
226 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
227 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
228 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
229 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
230 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
231 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
233 -- Dump completed on 2013-05-31 9:26:27
235 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
236 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
237 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
238 /*!40101 SET NAMES utf8 */;
239 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
240 /*!40103 SET TIME_ZONE='+00:00' */;
241 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
242 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
243 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
244 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
245 DROP TABLE IF EXISTS `item`
;
246 /*!40101 SET @saved_cs_client = @@character_set_client */;
247 /*!40101 SET character_set_client = utf8 */;
248 CREATE TABLE `item`
(
249 `item_id`
int(11) NOT NULL AUTO_INCREMENT
,
250 `barcode_id`
int(11) NOT NULL,
251 `vendor_id`
int(11) NOT NULL,
252 `item_serial`
varchar(128) COLLATE utf8_czech_ci
NOT NULL,
253 `item_quantity`
int(11) NOT NULL DEFAULT '1',
254 `room_id`
int(11) NOT NULL DEFAULT '1',
255 `status_id`
int(11) NOT NULL DEFAULT '1',
256 `item_price_in`
decimal(9,2) NOT NULL DEFAULT '0.00',
257 `item_price_out`
decimal(9,2) DEFAULT NULL,
258 `location_id`
int(11) DEFAULT '0',
259 `item_customer`
int(11) DEFAULT NULL,
260 `item_date_bought`
date NOT NULL DEFAULT '0000-00-00',
261 `item_date_sold`
date DEFAULT '0000-00-00',
262 `item_note`
varchar(512) COLLATE utf8_czech_ci
DEFAULT NULL,
263 `item_author`
int(11) NOT NULL,
264 `item_valid_till`
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
265 `item_valid_from`
timestamp NULL DEFAULT CURRENT_TIMESTAMP,
266 PRIMARY KEY (`item_id`
,`item_valid_till`
),
267 UNIQUE KEY `item_serial_item_valid_till`
(`item_serial`
,`item_valid_till`
),
268 KEY `vendor_id`
(`vendor_id`
),
269 KEY `status_id`
(`status_id`
),
270 KEY `room_id`
(`room_id`
),
271 KEY `location_id`
(`location_id`
),
272 CONSTRAINT `item_ibfk_10`
FOREIGN KEY (`location_id`
) REFERENCES `
location`
(`location_id`
) ON DELETE SET NULL ON UPDATE CASCADE,
273 CONSTRAINT `item_ibfk_6`
FOREIGN KEY (`vendor_id`
) REFERENCES `vendor`
(`vendor_id`
),
274 CONSTRAINT `item_ibfk_8`
FOREIGN KEY (`status_id`
) REFERENCES `status`
(`status_id`
),
275 CONSTRAINT `item_ibfk_9`
FOREIGN KEY (`room_id`
) REFERENCES `room`
(`room_id`
)
276 ) ENGINE
=InnoDB AUTO_INCREMENT
=59 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
277 /*!40101 SET character_set_client = @saved_cs_client */;
278 DROP TABLE IF EXISTS `model`
;
279 /*!40101 SET @saved_cs_client = @@character_set_client */;
280 /*!40101 SET character_set_client = utf8 */;
281 CREATE TABLE `model`
(
282 `model_id`
int(11) NOT NULL AUTO_INCREMENT
,
283 `model_name`
varchar(64) COLLATE utf8_czech_ci
NOT NULL,
284 `producer_id`
int(11) NOT NULL DEFAULT '0',
285 `category_id`
int(11) NOT NULL DEFAULT '0',
286 `model_countable`
int(1) NOT NULL DEFAULT '1',
287 `model_price_in`
decimal(9,2) DEFAULT NULL,
288 `model_price_out`
decimal(9,2) DEFAULT NULL,
289 `model_reserve`
int(11) unsigned
DEFAULT '0',
290 `model_eshop_hide`
int(1) unsigned
NOT NULL DEFAULT '0',
291 `model_descript`
varchar(1024) COLLATE utf8_czech_ci
NOT NULL,
292 PRIMARY KEY (`model_id`
),
293 KEY `category_id`
(`category_id`
),
294 KEY `producer_id`
(`producer_id`
),
295 CONSTRAINT `model_ibfk_1`
FOREIGN KEY (`category_id`
) REFERENCES `category`
(`category_id`
),
296 CONSTRAINT `model_ibfk_2`
FOREIGN KEY (`producer_id`
) REFERENCES `producer`
(`producer_id`
)
297 ) ENGINE
=InnoDB AUTO_INCREMENT
=8 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
298 /*!40101 SET character_set_client = @saved_cs_client */;
299 DROP TABLE IF EXISTS `barcode`
;
300 /*!40101 SET @saved_cs_client = @@character_set_client */;
301 /*!40101 SET character_set_client = utf8 */;
302 CREATE TABLE `barcode`
(
303 `barcode_id`
int(11) NOT NULL AUTO_INCREMENT
,
304 `model_id`
int(11) NOT NULL,
305 `barcode_name`
varchar(128) COLLATE utf8_czech_ci
NOT NULL,
306 PRIMARY KEY (`barcode_id`
),
307 KEY `model_id`
(`model_id`
),
308 CONSTRAINT `barcode_ibfk_2`
FOREIGN KEY (`model_id`
) REFERENCES `model`
(`model_id`
) ON UPDATE CASCADE
309 ) ENGINE
=InnoDB AUTO_INCREMENT
=12 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
310 /*!40101 SET character_set_client = @saved_cs_client */;
311 DROP TABLE IF EXISTS `
lock`
;
312 /*!40101 SET @saved_cs_client = @@character_set_client */;
313 /*!40101 SET character_set_client = utf8 */;
314 CREATE TABLE `
lock`
(
315 `lock_id`
int(11) unsigned
NOT NULL AUTO_INCREMENT
,
316 `lock_author`
int(11) unsigned
NOT NULL,
317 `lock_name`
varchar(4096) COLLATE utf8_czech_ci
NOT NULL,
318 PRIMARY KEY (`lock_id`
)
319 ) ENGINE
=MyISAM
DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
320 /*!40101 SET character_set_client = @saved_cs_client */;
321 DROP TABLE IF EXISTS `
transaction`
;
322 /*!40101 SET @saved_cs_client = @@character_set_client */;
323 /*!40101 SET character_set_client = utf8 */;
324 CREATE TABLE `
transaction`
(
325 `transaction_id`
int(11) unsigned
NOT NULL AUTO_INCREMENT
,
326 `transaction_time` datetime
NOT NULL,
327 `transaction_from`
varchar(23) COLLATE utf8_czech_ci
NOT NULL,
328 `transaction_to`
varchar(23) COLLATE utf8_czech_ci
NOT NULL,
329 `transaction_amount`
int(11) NOT NULL,
330 `transaction_author`
int(11) NOT NULL,
331 `transaction_comment`
varchar(128) COLLATE utf8_czech_ci
NOT NULL,
332 PRIMARY KEY (`transaction_id`
)
333 ) ENGINE
=InnoDB AUTO_INCREMENT
=79 DEFAULT CHARSET
=utf8
COLLATE=utf8_czech_ci
;
334 /*!40101 SET character_set_client = @saved_cs_client */;
335 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
337 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
338 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
339 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
340 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
341 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
342 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
343 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;