· 7 years ago · Sep 10, 2018, 06:06 AM
1-- Dump completed on 2012-07-12 16:58:48
2CREATE DATABASE IF NOT EXISTS `demo_aulas` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
3USE `demo_aulas`;
4-- MySQL dump 10.13 Distrib 5.5.16, for Win32 (x86)
5--
6-- Host: 192.168.179.100 Database: demo_aulas
7-- ------------------------------------------------------
8-- Server version 5.5.16
9
10/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
11/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
12/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
13/*!40101 SET NAMES utf8 */;
14/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
15/*!40103 SET TIME_ZONE='+00:00' */;
16/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
17/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
18/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
19/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
20
21--
22-- Table structure for table `vendas_itens`
23--
24
25DROP TABLE IF EXISTS `vendas_itens`;
26/*!40101 SET @saved_cs_client = @@character_set_client */;
27/*!40101 SET character_set_client = utf8 */;
28CREATE TABLE `vendas_itens` (
29 `idvendas_itens` int(11) NOT NULL AUTO_INCREMENT,
30 `idencomendas_itens` int(11) NOT NULL,
31 `qtd` int(11) NOT NULL DEFAULT '1',
32 `idvendas` int(11) NOT NULL,
33 PRIMARY KEY (`idvendas_itens`),
34 KEY `fk_vendas_itens1` (`idvendas`),
35 KEY `fk_vendas_itens2` (`idencomendas_itens`),
36 CONSTRAINT `fk_vendas_itens1` FOREIGN KEY (`idvendas`) REFERENCES `vendas` (`idvendas`) ON DELETE CASCADE ON UPDATE CASCADE,
37 CONSTRAINT `fk_vendas_itens2` FOREIGN KEY (`idencomendas_itens`) REFERENCES `encomendas_itens` (`idencomendas_itens`) ON UPDATE CASCADE
38) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
39/*!40101 SET character_set_client = @saved_cs_client */;
40
41--
42-- Dumping data for table `vendas_itens`
43--
44
45LOCK TABLES `vendas_itens` WRITE;
46/*!40000 ALTER TABLE `vendas_itens` DISABLE KEYS */;
47/*!40000 ALTER TABLE `vendas_itens` ENABLE KEYS */;
48UNLOCK TABLES;
49
50--
51-- Table structure for table `produtos`
52--
53
54DROP TABLE IF EXISTS `produtos`;
55/*!40101 SET @saved_cs_client = @@character_set_client */;
56/*!40101 SET character_set_client = utf8 */;
57CREATE TABLE `produtos` (
58 `idprodutos` int(11) NOT NULL AUTO_INCREMENT,
59 `codigo` varchar(45) CHARACTER SET latin1 NOT NULL,
60 `qtd_armazem` int(11) DEFAULT '0',
61 PRIMARY KEY (`idprodutos`)
62) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
63/*!40101 SET character_set_client = @saved_cs_client */;
64
65--
66-- Dumping data for table `produtos`
67--
68
69LOCK TABLES `produtos` WRITE;
70/*!40000 ALTER TABLE `produtos` DISABLE KEYS */;
71INSERT INTO `produtos` VALUES (1,'AA1',-15),(2,'AA2',0);
72/*!40000 ALTER TABLE `produtos` ENABLE KEYS */;
73UNLOCK TABLES;
74
75--
76-- Table structure for table `encomendas_itens`
77--
78
79DROP TABLE IF EXISTS `encomendas_itens`;
80/*!40101 SET @saved_cs_client = @@character_set_client */;
81/*!40101 SET character_set_client = utf8 */;
82CREATE TABLE `encomendas_itens` (
83 `idencomendas_itens` int(11) NOT NULL AUTO_INCREMENT,
84 `idencomendas` int(11) NOT NULL,
85 `idprodutos` int(11) NOT NULL,
86 `qtd` int(11) NOT NULL DEFAULT '1',
87 PRIMARY KEY (`idencomendas_itens`),
88 KEY `fk_encomendas_itens1` (`idencomendas`),
89 KEY `fk_encomendas_itens2` (`idprodutos`),
90 CONSTRAINT `fk_encomendas_itens1` FOREIGN KEY (`idencomendas`) REFERENCES `encomendas` (`idencomendas`) ON DELETE CASCADE ON UPDATE CASCADE,
91 CONSTRAINT `fk_encomendas_itens2` FOREIGN KEY (`idprodutos`) REFERENCES `produtos` (`idprodutos`) ON UPDATE CASCADE
92) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
93/*!40101 SET character_set_client = @saved_cs_client */;
94
95--
96-- Dumping data for table `encomendas_itens`
97--
98
99LOCK TABLES `encomendas_itens` WRITE;
100/*!40000 ALTER TABLE `encomendas_itens` DISABLE KEYS */;
101INSERT INTO `encomendas_itens` VALUES (1,1,1,5),(3,1,1,10);
102/*!40000 ALTER TABLE `encomendas_itens` ENABLE KEYS */;
103UNLOCK TABLES;
104/*!50003 SET @saved_cs_client = @@character_set_client */ ;
105/*!50003 SET @saved_cs_results = @@character_set_results */ ;
106/*!50003 SET @saved_col_connection = @@collation_connection */ ;
107/*!50003 SET character_set_client = utf8 */ ;
108/*!50003 SET character_set_results = utf8 */ ;
109/*!50003 SET collation_connection = utf8_general_ci */ ;
110/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
111/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
112DELIMITER ;;
113/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `demo_aulas`.`encomendas_itens_antes` BEFORE INSERT ON `demo_aulas`.`encomendas_itens`
114 FOR EACH ROW
115 BEGIN
116 UPDATE produtos SET qtd_armazem = qtd_armazem - NEW.qtd WHERE idprodutos = NEW.idprodutos;
117 END */;;
118DELIMITER ;
119/*!50003 SET sql_mode = @saved_sql_mode */ ;
120/*!50003 SET character_set_client = @saved_cs_client */ ;
121/*!50003 SET character_set_results = @saved_cs_results */ ;
122/*!50003 SET collation_connection = @saved_col_connection */ ;
123/*!50003 SET @saved_cs_client = @@character_set_client */ ;
124/*!50003 SET @saved_cs_results = @@character_set_results */ ;
125/*!50003 SET @saved_col_connection = @@collation_connection */ ;
126/*!50003 SET character_set_client = utf8 */ ;
127/*!50003 SET character_set_results = utf8 */ ;
128/*!50003 SET collation_connection = utf8_general_ci */ ;
129/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
130/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
131DELIMITER ;;
132/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `demo_aulas`.`encomendas_itens_alterar`
133BEFORE UPDATE ON `demo_aulas`.`encomendas_itens`
134FOR EACH ROW
135BEGIN
136 UPDATE produtos SET qtd_armazem = qtd_armazem +OLD.qtd WHERE idprodutos = OLD.idprodutos;
137 UPDATE produtos SET qtd_armazem = qtd_armazem - NEW.qtd WHERE idprodutos = NEW.idprodutos;
138 END */;;
139DELIMITER ;
140/*!50003 SET sql_mode = @saved_sql_mode */ ;
141/*!50003 SET character_set_client = @saved_cs_client */ ;
142/*!50003 SET character_set_results = @saved_cs_results */ ;
143/*!50003 SET collation_connection = @saved_col_connection */ ;
144/*!50003 SET @saved_cs_client = @@character_set_client */ ;
145/*!50003 SET @saved_cs_results = @@character_set_results */ ;
146/*!50003 SET @saved_col_connection = @@collation_connection */ ;
147/*!50003 SET character_set_client = utf8 */ ;
148/*!50003 SET character_set_results = utf8 */ ;
149/*!50003 SET collation_connection = utf8_general_ci */ ;
150/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
151/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
152DELIMITER ;;
153/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `demo_aulas`.`encomendas_itens_apagar` BEFORE DELETE ON `demo_aulas`.`encomendas_itens`
154 FOR EACH ROW
155 BEGIN
156 UPDATE produtos SET qtd_armazem = qtd_armazem +OLD.qtd WHERE idprodutos = OLD.idprodutos;
157 END */;;
158DELIMITER ;
159/*!50003 SET sql_mode = @saved_sql_mode */ ;
160/*!50003 SET character_set_client = @saved_cs_client */ ;
161/*!50003 SET character_set_results = @saved_cs_results */ ;
162/*!50003 SET collation_connection = @saved_col_connection */ ;
163
164--
165-- Table structure for table `encomendas`
166--
167
168DROP TABLE IF EXISTS `encomendas`;
169/*!40101 SET @saved_cs_client = @@character_set_client */;
170/*!40101 SET character_set_client = utf8 */;
171CREATE TABLE `encomendas` (
172 `idencomendas` int(11) NOT NULL AUTO_INCREMENT,
173 `ocorrencia` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
174 `pendente` bit(1) NOT NULL DEFAULT b'1',
175 PRIMARY KEY (`idencomendas`)
176) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
177/*!40101 SET character_set_client = @saved_cs_client */;
178
179--
180-- Dumping data for table `encomendas`
181--
182
183LOCK TABLES `encomendas` WRITE;
184/*!40000 ALTER TABLE `encomendas` DISABLE KEYS */;
185INSERT INTO `encomendas` VALUES (1,'2012-07-12 14:56:56','');
186/*!40000 ALTER TABLE `encomendas` ENABLE KEYS */;
187UNLOCK TABLES;
188
189--
190-- Table structure for table `vendas`
191--
192
193DROP TABLE IF EXISTS `vendas`;
194/*!40101 SET @saved_cs_client = @@character_set_client */;
195/*!40101 SET character_set_client = utf8 */;
196CREATE TABLE `vendas` (
197 `idvendas` int(11) NOT NULL AUTO_INCREMENT,
198 `ocorrencia` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
199 PRIMARY KEY (`idvendas`)
200) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
201/*!40101 SET character_set_client = @saved_cs_client */;
202
203--
204-- Dumping data for table `vendas`
205--
206
207LOCK TABLES `vendas` WRITE;
208/*!40000 ALTER TABLE `vendas` DISABLE KEYS */;
209/*!40000 ALTER TABLE `vendas` ENABLE KEYS */;
210UNLOCK TABLES;
211
212--
213-- Dumping routines for database 'demo_aulas'
214--
215/*!50003 DROP PROCEDURE IF EXISTS `produtos_em_falta` */;
216/*!50003 SET @saved_cs_client = @@character_set_client */ ;
217/*!50003 SET @saved_cs_results = @@character_set_results */ ;
218/*!50003 SET @saved_col_connection = @@collation_connection */ ;
219/*!50003 SET character_set_client = utf8 */ ;
220/*!50003 SET character_set_results = utf8 */ ;
221/*!50003 SET collation_connection = utf8_general_ci */ ;
222/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
223/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
224DELIMITER ;;
225/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 PROCEDURE `produtos_em_falta`()
226BEGIN
227SELECT * FROM produtos where qtd_armazem<0;
228END */;;
229DELIMITER ;
230/*!50003 SET sql_mode = @saved_sql_mode */ ;
231/*!50003 SET character_set_client = @saved_cs_client */ ;
232/*!50003 SET character_set_results = @saved_cs_results */ ;
233/*!50003 SET collation_connection = @saved_col_connection */ ;
234/*!50003 DROP PROCEDURE IF EXISTS `qtd_codigos` */;
235/*!50003 SET @saved_cs_client = @@character_set_client */ ;
236/*!50003 SET @saved_cs_results = @@character_set_results */ ;
237/*!50003 SET @saved_col_connection = @@collation_connection */ ;
238/*!50003 SET character_set_client = utf8 */ ;
239/*!50003 SET character_set_results = utf8 */ ;
240/*!50003 SET collation_connection = utf8_general_ci */ ;
241/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
242/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
243DELIMITER ;;
244/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 PROCEDURE `qtd_codigos`(OUT qtd INT)
245BEGIN
246SELECT COUNT(*) INTO qtd FROM produtos;
247END */;;
248DELIMITER ;
249/*!50003 SET sql_mode = @saved_sql_mode */ ;
250/*!50003 SET character_set_client = @saved_cs_client */ ;
251/*!50003 SET character_set_results = @saved_cs_results */ ;
252/*!50003 SET collation_connection = @saved_col_connection */ ;
253/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
254
255/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
256/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
257/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
258/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
259/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
260/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
261/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
262
263-- Dump completed on 2012-07-12 16:58:48