-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64) -- -- Host: localhost Database: golem_gestionale -- ------------------------------------------------------ -- Server version 8.0.17 /*!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 utf8mb4 */; /*!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 `fonte` -- DROP TABLE IF EXISTS `fonte`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `fonte` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fonte` tinytext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `fonte` -- LOCK TABLES `fonte` WRITE; /*!40000 ALTER TABLE `fonte` DISABLE KEYS */; INSERT INTO `fonte` VALUES (0,''),(1,'Altro'),(2,'Socio fondatore'),(3,'Socio di vecchia data'),(4,'Passaparola'),(5,'Eventi organizzati dall\'associazione (es. Linux Day)'),(6,'Social network (Facebook, Twitter, ...)'); /*!40000 ALTER TABLE `fonte` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `iscrizione` -- DROP TABLE IF EXISTS `iscrizione`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `iscrizione` ( `id` int(11) NOT NULL AUTO_INCREMENT, `socio` int(11) NOT NULL, `anno` date NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `iscrizione_UN` (`socio`,`anno`), CONSTRAINT `iscrizione_FK` FOREIGN KEY (`socio`) REFERENCES `socio` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `iscrizione` -- LOCK TABLES `iscrizione` WRITE; /*!40000 ALTER TABLE `iscrizione` DISABLE KEYS */; /*!40000 ALTER TABLE `iscrizione` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `professione` -- DROP TABLE IF EXISTS `professione`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `professione` ( `id` int(11) NOT NULL AUTO_INCREMENT, `professione` tinytext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `professione` -- LOCK TABLES `professione` WRITE; /*!40000 ALTER TABLE `professione` DISABLE KEYS */; INSERT INTO `professione` VALUES (0,''),(1,'Altro'),(2,'Studente'),(3,'Casalingo'),(4,'Tecnico Informatico'),(5,'Professore, Insegnante'),(6,'Scrittore, Giornalista'),(7,'Membro del clero'),(8,'Medico, Farmacista, Infermiere'),(9,'Magistrato, Avvocato, Notaio'),(10,'Agronomo, Veterinario, Biologo'),(11,'Fisico, Chimico, Ingegnere, Geometra'),(12,'Matematico, Statistico'),(13,'Economista, Commercialista'),(14,'Imprenditore'),(15,'Impiegato amministrativo'),(16,'Membro dei corpi armati, dei corpi di polizia'); /*!40000 ALTER TABLE `professione` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `questionario` -- DROP TABLE IF EXISTS `questionario`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `questionario` ( `id` int(11) NOT NULL AUTO_INCREMENT, `socio` int(11) NOT NULL, `dataCompilazione` date DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `questionario_UN` (`socio`), CONSTRAINT `questionario_FK` FOREIGN KEY (`socio`) REFERENCES `socio` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `questionario` -- LOCK TABLES `questionario` WRITE; /*!40000 ALTER TABLE `questionario` DISABLE KEYS */; INSERT INTO `questionario` VALUES (1,2,'2018-09-09'); /*!40000 ALTER TABLE `questionario` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `socio` -- DROP TABLE IF EXISTS `socio`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `socio` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nome` tinytext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `cognome` tinytext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `dataNascita` date DEFAULT NULL, `comuneResidenza` tinytext CHARACTER SET utf8 COLLATE utf8_bin, `email` tinytext CHARACTER SET utf8 COLLATE utf8_bin, `professione` int(11) NOT NULL DEFAULT '0', `fonte` int(11) NOT NULL DEFAULT '0', `abilitaQuestionario` tinyint(1) NOT NULL DEFAULT '0', `pin` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `dataCreazione` datetime NOT NULL, `dataModifica` datetime NOT NULL, PRIMARY KEY (`id`), KEY `socio_fonte_FK_idx` (`fonte`), KEY `socio_professione_FK_idx` (`professione`), CONSTRAINT `socio_fonte_FK` FOREIGN KEY (`fonte`) REFERENCES `fonte` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `socio_professione_FK` FOREIGN KEY (`professione`) REFERENCES `professione` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `socio` -- LOCK TABLES `socio` WRITE; /*!40000 ALTER TABLE `socio` DISABLE KEYS */; INSERT INTO `socio` VALUES (1,'Massimo','Moroni','1965-11-20','Fiesole','massimodafirenze@gmail.com',14,5,1,'49379','0000-00-00 00:00:00','2019-08-13 22:12:45'),(2,'Linux','Morrox','1972-07-01','Catania','morrolinux@gmail.com',5,6,1,'62264','0000-00-00 00:00:00','2019-08-13 22:12:45'),(3,'Simone','Bagni','1969-02-02','Montelupo Fiorentino','massimobagni@libero.it',4,3,1,'48740','0000-00-00 00:00:00','2019-08-13 22:12:45'),(4,'Jane','Doe','1875-02-28','Naples','doe@linux.it',11,4,1,'63807','0000-00-00 00:00:00','2019-08-13 22:12:45'),(5,'Paolino','Paperino','1930-01-01','Torino',NULL,1,0,1,'61695','0000-00-00 00:00:00','2019-08-13 22:12:45'),(6,'Pinco','Pallino','1990-08-29','Empoli',NULL,1,0,1,'18923','0000-00-00 00:00:00','2019-08-13 22:12:45'),(7,'Damigian','Damigiano',NULL,'Qui','Quo',15,2,1,'13603','0000-00-00 00:00:00','2019-08-13 22:12:45'),(8,'Ciccino','Pontorme','1980-01-11','casa sua',NULL,0,0,1,'56787','0000-00-00 00:00:00','2019-08-13 22:12:45'); /*!40000 ALTER TABLE `socio` ENABLE KEYS */; UNLOCK TABLES; /*!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 = utf8_bin */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `socio_BEFORE_INSERT` BEFORE INSERT ON `socio` FOR EACH ROW BEGIN set @currentTimeStamp = current_timestamp(); if new.abilitaQuestionario = 1 then set new.pin = creaPin(new.pin); else set new.pin = null; end if; set new.dataCreazione = @currentTimeStamp; set new.dataModifica = currentTimeStamp; 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 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 = utf8_bin */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `socio_BEFORE_UPDATE` BEFORE UPDATE ON `socio` FOR EACH ROW BEGIN if new.abilitaQuestionario = 1 then set new.pin = creaPin(new.pin); else set new.pin = null; end if; set new.dataModifica = current_timestamp(); 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 */ ; -- -- Dumping routines for database 'golem_gestionale' -- /*!50003 DROP FUNCTION IF EXISTS `creaPin` */; /*!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 = utf8_bin */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE FUNCTION `creaPin`(pin varchar(5)) RETURNS varchar(5) CHARSET utf8mb4 NO SQL BEGIN set @oldPin = pin; case when @oldPin is null then begin set @charsBucket = '0123456789'; set @charLen = length(@charsBucket); set @randomPassword = ''; while length(@randomPassword) < 5 do set @randomPassword = concat(@randomPassword, substring(@charsBucket, ceiling(rand() * @charLen), 1)); end while; set @newPin = @randomPassword; end; else set @newPin = @oldPin; end case; return @newPin; 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 `aggiornaQuestionarioDaNicolodi` */; /*!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 = utf8_bin */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `aggiornaQuestionarioDaNicolodi`(in parId int , in parPin varchar(5) , in parNome tinytext , in parCognome tinytext , in parDataNascita date , in parComuneResidenza tinytext , in parEmail tinytext , in parProfessione int , in parFonte int ) BEGIN UPDATE socio SET nome = parNome , cognome = parCognome , dataNascita = parDataNascita , comuneResidenza = parComuneResidenza , email = parEmail , professione = parProfessione , fonte = parFonte , abilitaQuestionario = false WHERE id = parId AND pin = parPin AND abilitaQuestionario = true; 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 2019-08-13 22:20:53