1 -- MySQL dump 10.13 Distrib 5.5.28, for Linux (x86_64)
3 -- Host: localhost Database: schematic
4 -- ------------------------------------------------------
5 -- Server version 5.1.62-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 `Alerts`
22 DROP TABLE IF EXISTS `Alerts`;
23 /*!40101 SET @saved_cs_client = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `Alerts` (
26 `aid` int(11) NOT NULL AUTO_INCREMENT,
27 `uid` int(11) DEFAULT NULL,
28 `nid` int(11) DEFAULT NULL,
29 `date` date DEFAULT NULL,
30 `description` varchar(200) DEFAULT NULL,
33 CONSTRAINT `Alerts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `User` (`uid`)
34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
35 /*!40101 SET character_set_client = @saved_cs_client */;
38 -- Dumping data for table `Alerts`
41 LOCK TABLES `Alerts` WRITE;
42 /*!40000 ALTER TABLE `Alerts` DISABLE KEYS */;
43 /*!40000 ALTER TABLE `Alerts` ENABLE KEYS */;
47 -- Table structure for table `Contact`
50 DROP TABLE IF EXISTS `Contact`;
51 /*!40101 SET @saved_cs_client = @@character_set_client */;
52 /*!40101 SET character_set_client = utf8 */;
53 CREATE TABLE `Contact` (
54 `cid` int(11) NOT NULL AUTO_INCREMENT,
55 `vid` int(11) DEFAULT NULL,
56 `CTID` int(11) DEFAULT NULL,
57 `name` varchar(64) DEFAULT NULL,
58 `email` varchar(64) DEFAULT NULL,
59 `address` varchar(200) DEFAULT NULL,
60 `phone1` varchar(32) DEFAULT NULL,
61 `phone2` varchar(32) DEFAULT NULL,
62 `fax` varchar(32) DEFAULT NULL,
66 CONSTRAINT `Contact_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`),
67 CONSTRAINT `Contact_ibfk_2` FOREIGN KEY (`CTID`) REFERENCES `ContactType` (`ctid`)
68 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
69 /*!40101 SET character_set_client = @saved_cs_client */;
72 -- Dumping data for table `Contact`
75 LOCK TABLES `Contact` WRITE;
76 /*!40000 ALTER TABLE `Contact` DISABLE KEYS */;
77 INSERT INTO `Contact` VALUES (2,4,1,'Jo Pesci','jo.pesci@samsung.com','Shanghai','48-3483-23848 x2177','',''),(3,5,2,'John Gearhead','jg@harleydavidson.com','OK City','732-133-1233','','732-133-1240'),(4,6,3,'','','','','','');
78 /*!40000 ALTER TABLE `Contact` ENABLE KEYS */;
82 -- Table structure for table `ContactType`
85 DROP TABLE IF EXISTS `ContactType`;
86 /*!40101 SET @saved_cs_client = @@character_set_client */;
87 /*!40101 SET character_set_client = utf8 */;
88 CREATE TABLE `ContactType` (
89 `ctid` int(11) NOT NULL AUTO_INCREMENT,
90 `description` varchar(100) DEFAULT NULL,
92 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
93 /*!40101 SET character_set_client = @saved_cs_client */;
96 -- Dumping data for table `ContactType`
99 LOCK TABLES `ContactType` WRITE;
100 /*!40000 ALTER TABLE `ContactType` DISABLE KEYS */;
101 INSERT INTO `ContactType` VALUES (1,'Sales'),(2,'Engineer'),(3,'PR');
102 /*!40000 ALTER TABLE `ContactType` ENABLE KEYS */;
106 -- Table structure for table `DocumentType`
109 DROP TABLE IF EXISTS `DocumentType`;
110 /*!40101 SET @saved_cs_client = @@character_set_client */;
111 /*!40101 SET character_set_client = utf8 */;
112 CREATE TABLE `DocumentType` (
113 `dtid` int(11) NOT NULL AUTO_INCREMENT,
114 `description` varchar(100) DEFAULT NULL,
116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
117 /*!40101 SET character_set_client = @saved_cs_client */;
120 -- Dumping data for table `DocumentType`
123 LOCK TABLES `DocumentType` WRITE;
124 /*!40000 ALTER TABLE `DocumentType` DISABLE KEYS */;
125 /*!40000 ALTER TABLE `DocumentType` ENABLE KEYS */;
129 -- Table structure for table `Documents`
132 DROP TABLE IF EXISTS `Documents`;
133 /*!40101 SET @saved_cs_client = @@character_set_client */;
134 /*!40101 SET character_set_client = utf8 */;
135 CREATE TABLE `Documents` (
136 `did` int(11) NOT NULL AUTO_INCREMENT,
137 `dtid` int(11) DEFAULT NULL,
138 `vid` int(11) DEFAULT NULL,
139 `poid` int(11) DEFAULT NULL,
140 `filename` varchar(256) DEFAULT NULL,
146 CONSTRAINT `Documents_ibfk_1` FOREIGN KEY (`dtid`) REFERENCES `DocumentType` (`dtid`)
147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
148 /*!40101 SET character_set_client = @saved_cs_client */;
151 -- Dumping data for table `Documents`
154 LOCK TABLES `Documents` WRITE;
155 /*!40000 ALTER TABLE `Documents` DISABLE KEYS */;
156 /*!40000 ALTER TABLE `Documents` ENABLE KEYS */;
160 -- Table structure for table `History`
163 DROP TABLE IF EXISTS `History`;
164 /*!40101 SET @saved_cs_client = @@character_set_client */;
165 /*!40101 SET character_set_client = utf8 */;
166 CREATE TABLE `History` (
167 `uid` int(11) DEFAULT NULL,
168 `date` datetime DEFAULT NULL,
169 `note` varchar(100) DEFAULT NULL,
171 CONSTRAINT `History_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `User` (`uid`)
172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
173 /*!40101 SET character_set_client = @saved_cs_client */;
176 -- Dumping data for table `History`
179 LOCK TABLES `History` WRITE;
180 /*!40000 ALTER TABLE `History` DISABLE KEYS */;
181 /*!40000 ALTER TABLE `History` ENABLE KEYS */;
185 -- Table structure for table `Location`
188 DROP TABLE IF EXISTS `Location`;
189 /*!40101 SET @saved_cs_client = @@character_set_client */;
190 /*!40101 SET character_set_client = utf8 */;
191 CREATE TABLE `Location` (
192 `lid` int(11) NOT NULL AUTO_INCREMENT,
193 `vid` int(11) DEFAULT NULL,
194 `address` varchar(100) DEFAULT NULL,
195 `city` varchar(64) DEFAULT NULL,
196 `state` varchar(64) DEFAULT NULL,
197 `country` varchar(64) DEFAULT NULL,
198 `code` varchar(32) DEFAULT NULL,
201 CONSTRAINT `Location_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`)
202 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
203 /*!40101 SET character_set_client = @saved_cs_client */;
206 -- Dumping data for table `Location`
209 LOCK TABLES `Location` WRITE;
210 /*!40000 ALTER TABLE `Location` DISABLE KEYS */;
211 INSERT INTO `Location` VALUES (1,4,'723 Pine Scent Way','Shanghai','','China','8W-94732'),(2,5,'1 Harley Way','Oklahoma City','Oklahoma','USA','24678'),(3,6,'','','','','');
212 /*!40000 ALTER TABLE `Location` ENABLE KEYS */;
216 -- Table structure for table `Notes`
219 DROP TABLE IF EXISTS `Notes`;
220 /*!40101 SET @saved_cs_client = @@character_set_client */;
221 /*!40101 SET character_set_client = utf8 */;
222 CREATE TABLE `Notes` (
223 `nid` int(11) NOT NULL AUTO_INCREMENT,
224 `uid` int(11) NOT NULL,
225 `poid` int(11) DEFAULT NULL,
226 `note` varchar(200) DEFAULT NULL,
229 CONSTRAINT `Notes_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `User` (`uid`)
230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
231 /*!40101 SET character_set_client = @saved_cs_client */;
234 -- Dumping data for table `Notes`
237 LOCK TABLES `Notes` WRITE;
238 /*!40000 ALTER TABLE `Notes` DISABLE KEYS */;
239 /*!40000 ALTER TABLE `Notes` ENABLE KEYS */;
243 -- Table structure for table `PurchaseOrder`
246 DROP TABLE IF EXISTS `PurchaseOrder`;
247 /*!40101 SET @saved_cs_client = @@character_set_client */;
248 /*!40101 SET character_set_client = utf8 */;
249 CREATE TABLE `PurchaseOrder` (
250 `poid` int(11) NOT NULL AUTO_INCREMENT,
251 `vid` int(11) DEFAULT NULL,
252 `closed` tinyint(1) NOT NULL DEFAULT '0',
253 `poNumber` varchar(100) DEFAULT NULL,
254 `originalDate` date DEFAULT NULL,
255 `adjustedDate` date DEFAULT NULL,
256 `description` varchar(200) DEFAULT NULL,
257 PRIMARY KEY (`poid`),
259 CONSTRAINT `PurchaseOrder_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`)
260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
261 /*!40101 SET character_set_client = @saved_cs_client */;
264 -- Dumping data for table `PurchaseOrder`
267 LOCK TABLES `PurchaseOrder` WRITE;
268 /*!40000 ALTER TABLE `PurchaseOrder` DISABLE KEYS */;
269 /*!40000 ALTER TABLE `PurchaseOrder` ENABLE KEYS */;
273 -- Table structure for table `User`
276 DROP TABLE IF EXISTS `User`;
277 /*!40101 SET @saved_cs_client = @@character_set_client */;
278 /*!40101 SET character_set_client = utf8 */;
279 CREATE TABLE `User` (
280 `uid` int(11) NOT NULL AUTO_INCREMENT,
281 `ucid` int(11) DEFAULT NULL,
282 `name` varchar(64) DEFAULT NULL,
283 `login` varchar(32) DEFAULT NULL,
284 `password` varchar(64) DEFAULT NULL,
287 CONSTRAINT `User_ibfk_1` FOREIGN KEY (`ucid`) REFERENCES `UserClass` (`ucid`)
288 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
289 /*!40101 SET character_set_client = @saved_cs_client */;
292 -- Dumping data for table `User`
295 LOCK TABLES `User` WRITE;
296 /*!40000 ALTER TABLE `User` DISABLE KEYS */;
297 INSERT INTO `User` VALUES (1,1,'Administrator','admin','admin_123');
298 /*!40000 ALTER TABLE `User` ENABLE KEYS */;
302 -- Table structure for table `UserClass`
305 DROP TABLE IF EXISTS `UserClass`;
306 /*!40101 SET @saved_cs_client = @@character_set_client */;
307 /*!40101 SET character_set_client = utf8 */;
308 CREATE TABLE `UserClass` (
309 `ucid` int(11) NOT NULL AUTO_INCREMENT,
310 `privilegeLevel` int(11) NOT NULL DEFAULT '0',
311 `description` varchar(100) DEFAULT NULL,
313 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
314 /*!40101 SET character_set_client = @saved_cs_client */;
317 -- Dumping data for table `UserClass`
320 LOCK TABLES `UserClass` WRITE;
321 /*!40000 ALTER TABLE `UserClass` DISABLE KEYS */;
322 INSERT INTO `UserClass` VALUES (1,1,'Administrator');
323 /*!40000 ALTER TABLE `UserClass` ENABLE KEYS */;
327 -- Table structure for table `Vendor`
330 DROP TABLE IF EXISTS `Vendor`;
331 /*!40101 SET @saved_cs_client = @@character_set_client */;
332 /*!40101 SET character_set_client = utf8 */;
333 CREATE TABLE `Vendor` (
334 `vid` int(11) NOT NULL AUTO_INCREMENT,
335 `vlid` int(11) DEFAULT NULL,
336 `signedNDA` tinyint(1) NOT NULL DEFAULT '0',
337 `name` varchar(100) DEFAULT NULL,
340 CONSTRAINT `Vendor_ibfk_1` FOREIGN KEY (`vlid`) REFERENCES `VendorLevel` (`vlid`)
341 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
342 /*!40101 SET character_set_client = @saved_cs_client */;
345 -- Dumping data for table `Vendor`
348 LOCK TABLES `Vendor` WRITE;
349 /*!40000 ALTER TABLE `Vendor` DISABLE KEYS */;
350 INSERT INTO `Vendor` VALUES (1,1,0,'Sanford & Sons'),(2,3,0,'Haliburton'),(3,2,1,'Digikey'),(4,2,1,'Samsung'),(5,1,1,'Harley Davidson'),(6,1,0,'Amazing Scientific');
351 /*!40000 ALTER TABLE `Vendor` ENABLE KEYS */;
355 -- Table structure for table `VendorGroup`
358 DROP TABLE IF EXISTS `VendorGroup`;
359 /*!40101 SET @saved_cs_client = @@character_set_client */;
360 /*!40101 SET character_set_client = utf8 */;
361 CREATE TABLE `VendorGroup` (
362 `vgid` int(11) NOT NULL AUTO_INCREMENT,
363 `seqNo` int(4) NOT NULL,
364 `description` varchar(100) DEFAULT NULL,
366 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
367 /*!40101 SET character_set_client = @saved_cs_client */;
370 -- Dumping data for table `VendorGroup`
373 LOCK TABLES `VendorGroup` WRITE;
374 /*!40000 ALTER TABLE `VendorGroup` DISABLE KEYS */;
375 INSERT INTO `VendorGroup` VALUES (1,1,'Machine');
376 /*!40000 ALTER TABLE `VendorGroup` ENABLE KEYS */;
380 -- Table structure for table `VendorLevel`
383 DROP TABLE IF EXISTS `VendorLevel`;
384 /*!40101 SET @saved_cs_client = @@character_set_client */;
385 /*!40101 SET character_set_client = utf8 */;
386 CREATE TABLE `VendorLevel` (
387 `vlid` int(11) NOT NULL AUTO_INCREMENT,
388 `vendorUsable` tinyint(1) NOT NULL DEFAULT '0',
389 `color` int(4) DEFAULT NULL,
390 `description` varchar(100) DEFAULT NULL,
392 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
393 /*!40101 SET character_set_client = @saved_cs_client */;
396 -- Dumping data for table `VendorLevel`
399 LOCK TABLES `VendorLevel` WRITE;
400 /*!40000 ALTER TABLE `VendorLevel` DISABLE KEYS */;
401 INSERT INTO `VendorLevel` VALUES (1,1,65280,'1;Trusted'),(2,1,255,'2;Caution Advised'),(3,0,16719904,'X;DO NOT USE');
402 /*!40000 ALTER TABLE `VendorLevel` ENABLE KEYS */;
406 -- Table structure for table `VendorSpecificTypes`
409 DROP TABLE IF EXISTS `VendorSpecificTypes`;
410 /*!40101 SET @saved_cs_client = @@character_set_client */;
411 /*!40101 SET character_set_client = utf8 */;
412 CREATE TABLE `VendorSpecificTypes` (
413 `vid` int(11) DEFAULT NULL,
414 `vtid` int(11) DEFAULT NULL,
417 CONSTRAINT `VendorSpecificTypes_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`),
418 CONSTRAINT `VendorSpecificTypes_ibfk_2` FOREIGN KEY (`vtid`) REFERENCES `VendorType` (`vtid`)
419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
420 /*!40101 SET character_set_client = @saved_cs_client */;
423 -- Dumping data for table `VendorSpecificTypes`
426 LOCK TABLES `VendorSpecificTypes` WRITE;
427 /*!40000 ALTER TABLE `VendorSpecificTypes` DISABLE KEYS */;
428 INSERT INTO `VendorSpecificTypes` VALUES (1,2),(1,3),(1,5),(3,1),(3,3),(3,4),(5,1),(5,2),(5,3),(5,4);
429 /*!40000 ALTER TABLE `VendorSpecificTypes` ENABLE KEYS */;
433 -- Table structure for table `VendorType`
436 DROP TABLE IF EXISTS `VendorType`;
437 /*!40101 SET @saved_cs_client = @@character_set_client */;
438 /*!40101 SET character_set_client = utf8 */;
439 CREATE TABLE `VendorType` (
440 `vtid` int(11) NOT NULL AUTO_INCREMENT,
441 `vgid` int(11) DEFAULT NULL,
442 `seqNo` int(4) NOT NULL,
443 `description` varchar(100) DEFAULT NULL,
444 PRIMARY KEY (`vtid`),
446 CONSTRAINT `VendorType_ibfk_1` FOREIGN KEY (`vgid`) REFERENCES `VendorGroup` (`vgid`)
447 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
448 /*!40101 SET character_set_client = @saved_cs_client */;
451 -- Dumping data for table `VendorType`
454 LOCK TABLES `VendorType` WRITE;
455 /*!40000 ALTER TABLE `VendorType` DISABLE KEYS */;
456 INSERT INTO `VendorType` VALUES (1,1,2,'Cutter'),(2,1,3,'Sheet Metal'),(3,1,4,'EDM Wire'),(4,1,5,'CNC'),(5,1,6,'Casting');
457 /*!40000 ALTER TABLE `VendorType` ENABLE KEYS */;
459 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
461 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
462 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
463 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
464 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
465 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
466 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
467 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
469 -- Dump completed on 2012-12-05 10:13:50