· 8 years ago · Nov 29, 2017, 12:24 PM
1-- MySQL dump 10.13 Distrib 5.7.16, for osx10.11 (x86_64)
2--
3-- Host: localhost Database: consulta_veiculo
4-- ------------------------------------------------------
5-- Server version 5.7.16
6
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 */;
17
18--
19-- Current Database: `consulta_veiculo`
20--
21
22CREATE DATABASE /*!32312 IF NOT EXISTS*/ `consulta_veiculo` /*!40100 DEFAULT CHARACTER SET utf8 */;
23
24USE `consulta_veiculo`;
25
26--
27-- Table structure for table `veiculo`
28--
29
30DROP TABLE IF EXISTS `veiculo`;
31/*!40101 SET @saved_cs_client = @@character_set_client */;
32/*!40101 SET character_set_client = utf8 */;
33CREATE TABLE `veiculo` (
34 `resultado` json NOT NULL,
35 `placa` varchar(7) GENERATED ALWAYS AS (json_unquote(json_extract(`resultado`,'$.placa'))) STORED,
36 `chassi` varchar(17) GENERATED ALWAYS AS (json_unquote(json_extract(`resultado`,'$.chassi'))) STORED,
37 KEY `p` (`placa`),
38 KEY `c` (`chassi`)
39) ENGINE=InnoDB DEFAULT CHARSET=utf8;
40/*!40101 SET character_set_client = @saved_cs_client */;
41
42--
43-- Dumping data for table `veiculo`
44--
45
46LOCK TABLES `veiculo` WRITE;
47/*!40000 ALTER TABLE `veiculo` DISABLE KEYS */;
48INSERT INTO `veiculo` (`resultado`) VALUES ('{\"placa\": \"FLU4386\", \"chassi\": \"9BGKS48R0FG314856\", \"veiculo\": \"onix A\"}'),('{\"placa\": \"\", \"chassi\": \"8BGKS48R0FG314856\", \"veiculo\": \"onix X\"}');
49/*!40000 ALTER TABLE `veiculo` ENABLE KEYS */;
50UNLOCK TABLES;
51
52--
53-- Dumping routines for database 'consulta_veiculo'
54--
55/*!50003 DROP PROCEDURE IF EXISTS `consulta_chassi` */;
56/*!50003 SET @saved_cs_client = @@character_set_client */ ;
57/*!50003 SET @saved_cs_results = @@character_set_results */ ;
58/*!50003 SET @saved_col_connection = @@collation_connection */ ;
59/*!50003 SET character_set_client = utf8 */ ;
60/*!50003 SET character_set_results = utf8 */ ;
61/*!50003 SET collation_connection = utf8_general_ci */ ;
62/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
63/*!50003 SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
64DELIMITER ;;
65CREATE DEFINER=`root`@`localhost` PROCEDURE `consulta_chassi`(IN _chassi VARCHAR(17))
66BEGIN
67 SELECT resultado FROM veiculo WHERE chassi = _chassi LIMIT 1;
68 END ;;
69DELIMITER ;
70/*!50003 SET sql_mode = @saved_sql_mode */ ;
71/*!50003 SET character_set_client = @saved_cs_client */ ;
72/*!50003 SET character_set_results = @saved_cs_results */ ;
73/*!50003 SET collation_connection = @saved_col_connection */ ;
74/*!50003 DROP PROCEDURE IF EXISTS `consulta_placa` */;
75/*!50003 SET @saved_cs_client = @@character_set_client */ ;
76/*!50003 SET @saved_cs_results = @@character_set_results */ ;
77/*!50003 SET @saved_col_connection = @@collation_connection */ ;
78/*!50003 SET character_set_client = utf8 */ ;
79/*!50003 SET character_set_results = utf8 */ ;
80/*!50003 SET collation_connection = utf8_general_ci */ ;
81/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
82/*!50003 SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
83DELIMITER ;;
84CREATE DEFINER=`root`@`localhost` PROCEDURE `consulta_placa`(IN _placa VARCHAR(7))
85BEGIN
86 SELECT resultado FROM veiculo WHERE placa = _placa LIMIT 1;
87 END ;;
88DELIMITER ;
89/*!50003 SET sql_mode = @saved_sql_mode */ ;
90/*!50003 SET character_set_client = @saved_cs_client */ ;
91/*!50003 SET character_set_results = @saved_cs_results */ ;
92/*!50003 SET collation_connection = @saved_col_connection */ ;
93/*!50003 DROP PROCEDURE IF EXISTS `consulta_save` */;
94/*!50003 SET @saved_cs_client = @@character_set_client */ ;
95/*!50003 SET @saved_cs_results = @@character_set_results */ ;
96/*!50003 SET @saved_col_connection = @@collation_connection */ ;
97/*!50003 SET character_set_client = utf8 */ ;
98/*!50003 SET character_set_results = utf8 */ ;
99/*!50003 SET collation_connection = utf8_general_ci */ ;
100/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
101/*!50003 SET sql_mode = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
102DELIMITER ;;
103CREATE DEFINER=`root`@`localhost` PROCEDURE `consulta_save`(IN _resultado JSON)
104BEGIN
105 DECLARE total INT;
106 DECLARE _chassi varchar(17);
107 DECLARE _placa varchar(7);
108 SET _chassi = json_unquote(json_extract(_resultado,'$.chassi'));
109 SET _placa = json_unquote(json_extract(_resultado,'$.placa'));
110 IF LENGTH(_chassi) = 17 THEN
111 SELECT COUNT(chassi) INTO total FROM veiculo WHERE chassi = _chassi;
112 IF total = 0 THEN
113 SELECT COUNT(placa) INTO total FROM veiculo WHERE placa = _placa;
114 IF total = 0 THEN
115 INSERT INTO veiculo (resultado) VALUES (_resultado);
116 END IF;
117 ELSEIF total = 1 THEN
118 SELECT COUNT(placa) INTO total FROM veiculo WHERE placa = _placa OR placa = '' OR placa IS NULL;
119 IF total = 1 THEN
120 UPDATE veiculo SET resultado = _resultado WHERE chassi = _chassi LIMIT 1;
121 END IF;
122 END IF;
123 END IF;
124 END ;;
125DELIMITER ;
126/*!50003 SET sql_mode = @saved_sql_mode */ ;
127/*!50003 SET character_set_client = @saved_cs_client */ ;
128/*!50003 SET character_set_results = @saved_cs_results */ ;
129/*!50003 SET collation_connection = @saved_col_connection */ ;
130/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
131
132/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
133/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
134/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
135/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
136/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
137/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
138/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
139
140-- Dump completed on 2017-11-28 1:48:31