joseph
/
CiscoTool_v1
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
CiscoTool_v1/netmiko_Api/utils/db_network_tool.sql

1112 lines
53 KiB
SQL
Raw Permalink Blame History

-- MySQL dump 10.13 Distrib 8.0.32, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: db_network_tool
-- ------------------------------------------------------
-- Server version 8.0.32
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
`id_account` int NOT NULL AUTO_INCREMENT,
`acc_name` varchar(40) NOT NULL,
`acc_password` varchar(1000) NOT NULL,
`acc_role` int NOT NULL DEFAULT '0',
`acc_fullname` varchar(70) NOT NULL,
PRIMARY KEY (`id_account`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `account`
--
LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES (1,'admin','$2b$10$Y99gKeYVP.5mf.t3y7AYEOC6GUjxp5QIufCKRMkwtopRX4Tcg9R7O',0,'Joseph Le');
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `command_group`
--
DROP TABLE IF EXISTS `command_group`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `command_group` (
`id_cmd` int NOT NULL AUTO_INCREMENT,
`cmd_message` varchar(100) NOT NULL,
`group_id` int NOT NULL,
PRIMARY KEY (`id_cmd`),
KEY `fk_group_cmd` (`group_id`),
CONSTRAINT `fk_group_cmd` FOREIGN KEY (`group_id`) REFERENCES `group` (`id_group`)
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `command_group`
--
LOCK TABLES `command_group` WRITE;
/*!40000 ALTER TABLE `command_group` DISABLE KEYS */;
INSERT INTO `command_group` VALUES (51,'show inv',27),(52,'show ver',27),(53,'show post',27),(54,'show logging',27),(55,'show power',27),(58,'show ip',31),(71,'show inv',32),(72,'show ver',32),(73,'show post',32),(74,'sleep 2',36),(75,'send \"\r\"',36),(76,'send \"\r\"',36),(77,'send \"flash_init\r\"',36),(78,'sleep 15',36),(79,'expect \"MORE\"',36),(80,'send \" \"',36),(81,'sleep 50',36),(82,'send \"\r\"',36),(83,'send \"\r\"',36),(84,'expect \"switch:\"',36),(85,'send \"delete flash:config.text\r\"',36),(86,'expect \"?\"',36),(87,'send \"y\r\"',36),(88,'send \"\r\"',36),(89,'expect \"switch:\"',36),(90,'send \"delete flash:private-config.text\r\"',36),(91,'expect \"?\"',36),(92,'send \"y\r\"',36),(93,'send \"\r\"',36),(94,'expect \"switch:\"',36),(95,'sleep 5',36),(96,'send \"\r\"',36),(97,'send \"\r\"',36),(98,'sleep 5',36),(99,'send \"boot\r\"',36),(100,'sleep 5',36),(101,'send \"boot\r\"',36),(102,'sleep 3',37),(103,'send \"\r\"',37),(104,'send \"\r\"',37),(105,'expect \"switch:\"',37),(106,'send \"SWITCH_IGNORE_STARTUP_CFG=1\"',37),(107,'sleep 5',37),(108,'send \"\r\"',37),(109,'send \"\r\"',37),(110,'sleep 5',37),(111,'send \"boot\r\"',37),(112,'sleep 5',37),(113,'send \"boot\r\"',37),(114,'sleep 360',37),(115,'send \"\r\"',37),(116,'send \"\r\"',37),(117,'send \"\r\"',37),(118,'expect \">\"',37),(119,'send \"enable\r\"',37),(120,'sleep 2',37),(121,'send \"\r\"',37),(122,'send \"erase /all non-default\r\"',37),(123,'sleep 5',37),(124,'send \"\r\"',37),(125,'send \"\r\"',37),(126,'sleep 2',37),(127,'send \"reload\r\"',37),(128,'sleep 20',37);
/*!40000 ALTER TABLE `command_group` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `command_model`
--
DROP TABLE IF EXISTS `command_model`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `command_model` (
`id_cmd` int NOT NULL AUTO_INCREMENT,
`cmd_message` varchar(100) NOT NULL,
`model_id` varchar(45) NOT NULL,
PRIMARY KEY (`id_cmd`),
KEY `fk_model_cmd` (`model_id`),
CONSTRAINT `fk_model_cmd` FOREIGN KEY (`model_id`) REFERENCES `model` (`id_model`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `command_model`
--
LOCK TABLES `command_model` WRITE;
/*!40000 ALTER TABLE `command_model` DISABLE KEYS */;
/*!40000 ALTER TABLE `command_model` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `exclude_error`
--
DROP TABLE IF EXISTS `exclude_error`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `exclude_error` (
`id_error` int NOT NULL AUTO_INCREMENT,
`err_message` varchar(300) NOT NULL,
`err_model` varchar(50) NOT NULL,
PRIMARY KEY (`id_error`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `exclude_error`
--
LOCK TABLES `exclude_error` WRITE;
/*!40000 ALTER TABLE `exclude_error` DISABLE KEYS */;
INSERT INTO `exclude_error` VALUES (7,'abcdef','WS-C3560V2-24PS-S'),(10,'overruns','WS-C3560V2'),(13,'portasic','All'),(14,'registration failed','WS-C3560V2-24PS-S');
/*!40000 ALTER TABLE `exclude_error` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `group`
--
DROP TABLE IF EXISTS `group`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `group` (
`id_group` int NOT NULL AUTO_INCREMENT,
`group_name` varchar(100) NOT NULL,
`group_action` varchar(50) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_at` varchar(50) NOT NULL,
PRIMARY KEY (`id_group`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `group`
--
LOCK TABLES `group` WRITE;
/*!40000 ALTER TABLE `group` DISABLE KEYS */;
INSERT INTO `group` VALUES (27,'Switch_test','test','Admin','1676970115959'),(31,'router','UpdateIOS','Admin','1677464774952'),(32,'Default_Test','test','Admin','1677468444927'),(36,'Default_Break-2960/3560/3750','break','Admin','1678074026272'),(37,'Default_Break_anhKen_dat_ten_di','break','Admin','1679474911810');
/*!40000 ALTER TABLE `group` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `group_model`
--
DROP TABLE IF EXISTS `group_model`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `group_model` (
`idGroup` int NOT NULL,
`idModel` varchar(50) NOT NULL,
KEY `fk1_group` (`idGroup`),
KEY `fk2_model` (`idModel`),
CONSTRAINT `fk1_group` FOREIGN KEY (`idGroup`) REFERENCES `group` (`id_group`),
CONSTRAINT `fk2_model` FOREIGN KEY (`idModel`) REFERENCES `model` (`id_model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `group_model`
--
LOCK TABLES `group_model` WRITE;
/*!40000 ALTER TABLE `group_model` DISABLE KEYS */;
INSERT INTO `group_model` VALUES (27,'WS-C3750X-48P'),(31,'WS-C3750X-48P'),(27,'WS-C3560V2-24PS-S'),(27,'WS-C3750X-48PF-S'),(27,'WS-C3750X-48P-L'),(27,'WS-C3750X-24T-L'),(27,'WS-C3750X-24P-E'),(32,'WS-C2950T-24'),(32,'WS-C2960+48PST-L'),(32,'ISR4331/K9'),(32,'ISR4351/K9'),(32,'WS-C2950SX-24');
/*!40000 ALTER TABLE `group_model` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `line`
--
DROP TABLE IF EXISTS `line`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `line` (
`id_line` int NOT NULL AUTO_INCREMENT,
`line_ip` varchar(20) NOT NULL,
`line_port` int NOT NULL,
`line_clear` varchar(2) NOT NULL,
`line_gateway` varchar(20) DEFAULT '111.111.111.1',
`line_number` varchar(2) NOT NULL,
`station_id` int NOT NULL,
`enable` int NOT NULL DEFAULT '1',
PRIMARY KEY (`id_line`),
KEY `fk1` (`station_id`),
CONSTRAINT `fk1` FOREIGN KEY (`station_id`) REFERENCES `station` (`id_station`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `line`
--
LOCK TABLES `line` WRITE;
/*!40000 ALTER TABLE `line` DISABLE KEYS */;
INSERT INTO `line` VALUES (25,'172.16.20.17',2018,'3',NULL,'1',1,1),(26,'172.16.20.17',2018,'3',NULL,'12',1,1),(27,'172.16.20.17',2033,'3',NULL,'3',1,1),(28,'172.16.20.17',2000,'3',NULL,'14',1,1),(29,'172.16.20.18',1234,'3',NULL,'5',1,1),(30,'172.16.20.17',1235,'3',NULL,'6',1,1),(31,'172.16.20.17',2018,'18','111.111.111.1','1',2,1),(32,'172.16.20.17',2019,'19','123.123.123.1','2',2,1),(33,'172.16.20.17',2020,'20',NULL,'3',2,1),(34,'172.16.20.18',2022,'22',NULL,'4',2,1),(35,'172.16.20.7',2003,'3',NULL,'1',3,1),(36,'172.16.20.7',2004,'4',NULL,'2',3,1),(37,'',2005,'5',NULL,'3',3,1),(38,'',2006,'6',NULL,'4',3,1),(39,'172.16.20.7',2007,'7',NULL,'5',3,1),(40,'172.16.20.7',2008,'8',NULL,'6',3,1),(41,'172.16.20.7',2009,'9',NULL,'7',3,1),(42,'172.16.20.7',2010,'10',NULL,'8',3,1);
/*!40000 ALTER TABLE `line` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `log`
--
DROP TABLE IF EXISTS `log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `log` (
`id_log` int NOT NULL AUTO_INCREMENT,
`log_content` text NOT NULL,
`log_status` varchar(45) NOT NULL,
`log_line` int NOT NULL,
`PID` varchar(45) NOT NULL,
`SN` varchar(45) NOT NULL,
`created_by` varchar(45) NOT NULL,
`created_at` varchar(45) NOT NULL,
PRIMARY KEY (`id_log`),
KEY `fk_line` (`log_line`),
CONSTRAINT `fk_line` FOREIGN KEY (`log_line`) REFERENCES `line` (`id_line`)
) ENGINE=InnoDB AUTO_INCREMENT=284 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `log`
--
LOCK TABLES `log` WRITE;
/*!40000 ALTER TABLE `log` DISABLE KEYS */;
INSERT INTO `log` VALUES (275,'./public/filesTest/1679132415257','Error',33,'','','client','1679132424382'),(276,'./public/filesTest/1679132415287','Good',32,'WS-C2950SX-24','FOC1021X1A5','client','1679132437674'),(277,'./public/filesTest/1679132415318','Good',34,'WS-C2960+48PST-L','FOC1922S2N0','client','1679132439007'),(278,'./public/filesTest/1679132415224','Good',31,'WS-C3560V2-24PS-S','FDO1537Y08J','client','1679132440901'),(279,'./public/filesTest/1679270574744','Error',33,'','','client','1679270584330'),(280,'./public/filesTest/1679270574668','Good',32,'WS-C2950SX-24','FOC1021X1A5','client','1679270597695'),(281,'./public/filesTest/1679270574789','Good',34,'WS-C2960+48PST-L','FOC1922S2N0','client','1679270598973'),(282,'./public/filesTest/1679270574707','Good',31,'WS-C3560V2-24PS-S','FDO1537Y08J','client','1679270600801'),(283,'./public/filesTest/1679270651638','Error',33,'','','client','1679270660713');
/*!40000 ALTER TABLE `log` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `model`
--
DROP TABLE IF EXISTS `model`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `model` (
`id_model` varchar(50) NOT NULL,
`model_type` varchar(10) NOT NULL,
`created_at` varchar(45) NOT NULL,
PRIMARY KEY (`id_model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `model`
--
LOCK TABLES `model` WRITE;
/*!40000 ALTER TABLE `model` DISABLE KEYS */;
INSERT INTO `model` VALUES ('CISCO1841','Group','1677036767335'),('ISR4331/K9','Group','1678941086053'),('ISR4351/K9','Group','1678941087981'),('WS-C2950SX-24','Group','1679105872471'),('WS-C2950T-24','Group','1678175742392'),('WS-C2960+48PST-L','Group','1678175845137'),('WS-C3560V2-24PS-S','Group','1677483340689'),('WS-C3750X-24P-E','Group','1677721180101'),('WS-C3750X-24T-L','Group','1677721223262'),('WS-C3750X-48P','Group','1676970145366'),('WS-C3750X-48P-L','Group','1677721208963'),('WS-C3750X-48PF-S','Group','1677721194595');
/*!40000 ALTER TABLE `model` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `station`
--
DROP TABLE IF EXISTS `station`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `station` (
`id_station` int NOT NULL AUTO_INCREMENT,
`sta_name` varchar(45) NOT NULL,
`sta_ip` varchar(20) NOT NULL,
`sta_netmask` varchar(20) NOT NULL,
`sta_network` varchar(20) NOT NULL,
`sta_gateway` varchar(20) NOT NULL,
`sta_tftp` varchar(20) NOT NULL,
`netmask_line` varchar(20) NOT NULL,
`gateway_line` varchar(20) NOT NULL,
`updated_by` varchar(45) DEFAULT NULL,
`updated_at` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id_station`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `station`
--
LOCK TABLES `station` WRITE;
/*!40000 ALTER TABLE `station` DISABLE KEYS */;
INSERT INTO `station` VALUES (1,'US','172.16.20.1','255.255.0.0','192.168.10.0','172.16.5.2','172.16.5.7','','','Admin','21:12:58 - 02/02/2023'),(2,'AU','172.16.20.17','255.255.255.0','192.168.11.0','192.168.11.1','172.16.5.7','255.255.0.0','172.16.5.2','Admin','21:12:58 - 02/02/2023'),(3,'VN','172.16.20.7','255.255.0.0','192.168.11.0','172.16.5.2','172.16.5.8','','','Admin','21:12:58 - 02/02/2023');
/*!40000 ALTER TABLE `station` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping routines for database 'db_network_tool'
--
/*!50003 DROP PROCEDURE IF EXISTS `pro_account_addAccount` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_account_addAccount`(
in pUsername text, in pPassword text, in pRole text, in pFullName text)
BEGIN
insert into `account`(acc_name, acc_password, acc_role, acc_fullname)
values (pUsername, pPassword, pRole, pFullName);
select 1 as pCode;
select concat("add user") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_account_getAccountByUsername` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_account_getAccountByUsername`(in pUsername text)
BEGIN
select * from `account` where acc_name = pUsername;
select 1 as pCode;
select concat("get user by id") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_device_addError` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_device_addError`(
in pMessage text, in pModel text)
BEGIN
insert into exclude_error(err_message, err_model) values (pMessage, pModel);
select 1 as pCode;
select concat("add exclude error") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_device_deleteError` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_device_deleteError`(
in pId text)
BEGIN
delete from exclude_error where id_error=pId;
select 1 as pCode;
select concat("delete exclude error") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_device_getAllError` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_device_getAllError`()
BEGIN
select * from exclude_error;
select 1 as pCode;
select concat("get all exclude error") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_addCmdForGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_addCmdForGroup`(
in pIdGroup text, in pMessage text)
BEGIN
insert into `command_group`(group_id, cmd_message) values (pIdGroup, pMessage);
select 1 as pCode;
select concat("add command group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_addCmdForModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_addCmdForModel`(
in pIdModel text, in pMessage text)
BEGIN
insert into `command_model`(model_id, cmd_message) values (pIdModel, pMessage);
select 1 as pCode;
select concat("add command model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_addGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_addGroup`(
in pNameGroup text, in pCreatedBy text, in pCreatedAt text, in pAction text)
BEGIN
insert into `group`(group_name, created_by, created_at, group_action)
values (pNameGroup, pCreatedBy, pCreatedAt, pAction);
select id_group from `group` where created_at = pCreatedAt;
select 1 as pCode;
select concat("add group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_addModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_addModel`(
in pIdModel text, in pIdGroup text, in pType text, in pCreatedAt text)
BEGIN
declare checkModel int(2) default -1;
select 1 into checkModel from model where id_model = pIdModel;
IF(pType = "Group") THEN
insert into `model`(id_model, model_type, created_at) values (pIdModel, pType, pCreatedAt);
insert into `group_model`(idModel, idGroup) values (pIdModel, pIdGroup);
select 1 as pCode;
select concat("add model group") as pMess;
ELSE
insert into `model`(id_model, model_type, created_at) values (pIdModel, pType, pCreatedAt);
select 1 as pCode;
select concat("add model single") as pMess;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_addModelForGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_addModelForGroup`(
in pIdGroup text, in pIdModel text)
BEGIN
declare checkModel int(2) default -1;
select 1 into checkModel from model where id_model = pIdModel;
IF(checkModel = 1) THEN
insert into `group_model`(idGroup, idModel) values (pIdGroup, pIdModel);
END IF;
select 1 as pCode;
select concat("add model for group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_clearCmdOfModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_clearCmdOfModel`(
in pIdModel text)
BEGIN
delete from command_model where model_id = pIdModel;
select 1 as pCode;
select concat("clear cmd of model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_clearGroupOfModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_clearGroupOfModel`(
in pIdModel text)
BEGIN
delete from group_model where idModel = pIdModel;
select 1 as pCode;
select concat("clear group of model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteCmdOfGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_deleteCmdOfGroup`(
in pMessage text, in pIdGroup text)
BEGIN
delete from command_group where group_id=pIdGroup and cmd_message = pMessage;
select 1 as pCode;
select concat("delete cmd of group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteCmdOfModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_deleteCmdOfModel`(
in pMessage text, in pIdModel text)
BEGIN
delete from command_model where model_id=pIdModel and cmd_message = pMessage;
select 1 as pCode;
select concat("delete cmd of model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_deleteGroup`(
in pIdGroup text)
BEGIN
delete from command_group where group_id=pIdGroup;
delete from group_model where idGroup=pIdGroup;
delete from `group` where id_group=pIdGroup;
select 1 as pCode;
select concat("delete group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_deleteModel`(
in pIdModel text)
BEGIN
delete from command_model where model_id=pIdModel;
delete from group_model where idModel=pIdModel;
delete from `model` where id_model=pIdModel;
select 1 as pCode;
select concat("delete model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteModelOfGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_deleteModelOfGroup`(
in pIdModel text, in pIdGroup text)
BEGIN
delete from group_model where idGroup=pIdGroup and idModel = pIdModel;
select 1 as pCode;
select concat("delete model of group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_editGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_editGroup`(in pId text, in pName text)
BEGIN
update `group` set group_name = pName
where id_group = pId;
select 1 as pCode;
select concat("edit information group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_editModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_editModel`(in pId text, in pType text)
BEGIN
update `model` set model_type = pType
where id_model = pId;
select 1 as pCode;
select concat("edit information model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllCmdOfGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_getAllCmdOfGroup`(in pIdGroup text)
BEGIN
select * from command_group where group_id=pIdGroup;
select 1 as pCode;
select concat("get all command of group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllCmdOfModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_getAllCmdOfModel`(in pIdModel text)
BEGIN
select * from command_model where model_id=pIdModel;
select 1 as pCode;
select concat("get all command of model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_getAllGroup`()
BEGIN
select * from `group`;
select 1 as pCode;
select concat("get all group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllGroupOfModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_getAllGroupOfModel`(in pIdModel text)
BEGIN
select * from `model`, `group`, group_model where id_model=idModel and id_group = idGroup and idModel=pIdModel;
select 1 as pCode;
select concat("get all group of model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_getAllModel`()
BEGIN
select * from `model`;
select 1 as pCode;
select concat("get all model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllModelOfGroup` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_group_getAllModelOfGroup`(in pIdGroup text)
BEGIN
select * from `model`, `group`, group_model where id_model=idModel and id_group = idGroup and idGroup=pIdGroup;
select 1 as pCode;
select concat("get all model of group") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_log_addLog` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_log_addLog`(
in pIdLine text, in pContent text, in pStatus text, in pCreatedAt text, in pCreatedBy text
, in pPID text, in pSN text)
BEGIN
insert into `log`(log_content, log_status, log_line, created_at, created_by, PID, SN)
values (pContent, pStatus, pIdLine, pCreatedAt, pCreatedBy,pPID,pSN);
select 1 as pCode;
select concat("add log") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_log_getAllLogOfLine` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_log_getAllLogOfLine`(
in pIdLine text)
BEGIN
select * from `log` where log_line = pIdLine order by created_at desc;
select 1 as pCode;
select concat("add log") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_station_addLineForStation` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_station_addLineForStation`(
in pNumber text, in pPort text, in pClear text, in pIp text, in pStation text)
BEGIN
insert into line(line_number, line_port, line_clear, line_ip, station_id) values (pNumber, pPort, pClear, pIp, pStation);
select 1 as pCode;
select concat("add line for station") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_station_editLine` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_station_editLine`(in pId text, in pNumber text, in pPort text,
in pClear text, in pIp text)
BEGIN
update line set line_number = pNumber, line_port = pPort, line_clear = pClear, line_ip = pIp
where id_line = pId;
select 1 as pCode;
select concat("edit information line") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_station_editStation` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_station_editStation`(in pId text, in pIp text, in pTftp text,
in pNetwork text, in pNetmask text, in pGateway text, in pGatewayLine text, in pNetmaskLine text)
BEGIN
update station set sta_ip = pIp, sta_tftp = pTftp, sta_network = pNetwork,
sta_netmask = pNetmask, sta_gateway = pGateway, gateway_line = pGatewayLine, netmask_line = pNetmaskLine
where id_station = pId;
select 1 as pCode;
select concat("edit information station") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_station_enableLine` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_station_enableLine`(in pId text, in pStatus text)
BEGIN
update line set `enable` = pStatus
where id_line = pId;
select 1 as pCode;
select concat("edit enable line") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_station_getAllLineOfStation` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_station_getAllLineOfStation`(in pId text)
BEGIN
select * from line where station_id = pId;
select 1 as pCode;
select concat("get all line of station") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_station_getAllStation` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_station_getAllStation`()
BEGIN
select * from station;
select 1 as pCode;
select concat("get all station") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_test_getCommandByModel` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `pro_test_getCommandByModel`(
in pIdModel text, in pAction text)
BEGIN
select id_group from model, `group`, group_model where id_model = idModel and
id_group=idGroup AND id_model=pIdModel AND group_action=pAction;
#select 1 as pCode;
#select concat("delete model") as pMess;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2023-03-22 2:25:11
Reference in New Issue View Git Blame Copy Permalink