====== Remodelar Banco de Dados ======


===== Resumo =====


===== Campos =====

=== tabaluno ===
codigousuario: alterar para varchar(36)

=== Catálago de Configuração ===
{{:softwares:school:requisitos:modulos:schoolweb:configuracoes:bancodedados:remodelar_banco_de_dados:entitydesignerdiagram.png|}}

===== Triggers =====

=== tabaluno ===
==Insert==
	CREATE DEFINER=`root`@`localhost` TRIGGER `school`.`tabaluno_AFTER_INSERT` AFTER INSERT ON `tabaluno` FOR EACH ROW
	BEGIN

	if new.login <> "" and new.senha <> "" then
	INSERT INTO `tabusuarios` (`idbanco`,`codigousuario`,`nome`,`login`,`senha`,`tipoacesso`) VALUES (new.idbanco,uuid(),new.nome,new.login,new.senha,5);

	if new.emailpai <> "" && new.emailpai not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.emailpai and ativo = 0 and tipoacesso = 5)  then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.emailpai,1,null, null, 5);
	end if;

	if new.emailmae <> "" && new.emailmae not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.emailmae and ativo = 0 and tipoacesso = 5) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.emailmae,1,null, null, 5);
	end if;

	if new.email <> "" && new.email  not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.email and ativo = 0 and tipoacesso = 5) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.email,1,null, null, 5);
	end if;

	if new.emailresponsavel <> "" && new.emailresponsavel not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.emailresponsavel and ativo = 0
        and tipoacesso = 5) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.emailresponsavel,1,null, null, 5);
	end if;

	end if;
	END
==Update==
	USE `school`;

	DELIMITER $$

	DROP TRIGGER IF EXISTS school.tabaluno_AFTER_UPDATE$$
	USE `school`$$
	CREATE DEFINER=`root`@`%` TRIGGER `school`.`tabaluno_AFTER_UPDATE` AFTER UPDATE ON `tabaluno` FOR EACH ROW
	BEGIN
	if (select count(codigousuario) from `school`.`tabusuarios` WHERE 
	`idbanco` = OLD.idbanco and
	`nome` =  OLD.nome and
	`login` = OLD.login and
	`senha` = OLD.senha) > 0 then

	if new.login <> "" and new.senha <> "" then
	UPDATE `school`.`tabusuarios`
	SET
	`idbanco` = New.idbanco,
	`nome` =  New.nome,
	`login` = New.login,
	`senha` = New.senha
	WHERE 
	`idbanco` = OLD.idbanco and
	`nome` =  OLD.nome and
	`login` = OLD.login and
	`senha` = OLD.senha
	; 
	  end if;
	else
	  if new.login <> "" and new.senha <> "" then
	    INSERT INTO `tabusuarios` (`idbanco`,`codigousuario`,`nome`,`login`,`senha`,`tipoacesso`) VALUES (new.idbanco,uuid(),new.nome,new.login,new.senha,5);	
	  end if;
	end if; 
    
	DELETE FROM email WHERE idbanco = new.idbanco and codigo = new.codigoaluno and ativo = 1 and tipoacesso = 5;
	
	if new.login <> "" and new.senha <> "" then
	if new.emailpai <> "" && new.emailpai not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.emailpai and ativo = 0 and tipoacesso = 5)  then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.emailpai,1,null, null, 5);
	end if;
	if new.emailmae <> "" && new.emailmae not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.emailmae and ativo = 0 and tipoacesso = 5) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.emailmae,1,null, null, 5);
	end if;
	if new.email <> "" && new.email  not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.email and ativo = 0 and tipoacesso = 5) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.email,1,null, null, 5);
	end if;
	if new.emailresponsavel <> "" && new.emailresponsavel not in (select email from email where codigo = new.codigoaluno and idbanco = new.idbanco and email = new.emailresponsavel and ativo = 0
      and tipoacesso = 5) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoaluno,new.idbanco,new.emailresponsavel,1,null, null, 5);
	end if;
	end if;

	END$$
	DELIMITER ;


==Delete==

	CREATE DEFINER=`root`@`localhost` TRIGGER `school`.`tabaluno_AFTER_DELETE` AFTER DELETE ON `tabaluno` FOR EACH ROW
	BEGIN
	DELETE FROM TABUSUARIOS WHERE login=OLD.login and senha=old.senha and idbanco = old.idbanco;
	DELETE FROM email WHERE idbanco = OLD.idbanco and codigo = OLD.codigoaluno and ativo = 1 and tipoacesso = 5;
	END

=== tabservidor ===

	USE `school`;
	DELIMITER $$
	DROP TRIGGER IF EXISTS school.tabservidores_AFTER_INSERT$$
	USE `school`$$
	CREATE DEFINER=`root`@`%` TRIGGER `school`.`tabservidores_AFTER_INSERT` AFTER INSERT ON `tabservidores` FOR EACH ROW
	BEGIN
	if new.login <> "" and new.senha <> "" then
	INSERT INTO `tabusuarios` (`idbanco`,`codigousuario`,`nome`,`login`,`senha`,`tipoacesso`) VALUES (new.idbanco,uuid(),new.nome,new.login,new.senha,4);
	end if;
	END$$
	DELIMITER ;


	USE `school`;

	DELIMITER $$

	DROP TRIGGER IF EXISTS school.tabservidores_AFTER_UPDATE$$
	USE `school`$$
	CREATE DEFINER=`root`@`%` TRIGGER `school`.`tabservidores_AFTER_UPDATE` AFTER UPDATE ON `tabservidores` FOR EACH ROW
	BEGIN
	if (select count(codigousuario) from `school`.`tabusuarios` WHERE 
	`idbanco` = OLD.idbanco and
	`nome` =  OLD.nome and
	`login` = OLD.login and
	`senha` = OLD.senha) > 0 then

	if new.login <> "" and new.senha <> "" then
	UPDATE `school`.`tabusuarios`
	SET
	`idbanco` = New.idbanco,
	`nome` =  New.nome,
	`login` = New.login,
	`senha` = New.senha
	WHERE 
	`idbanco` = OLD.idbanco and
	`nome` =  OLD.nome and
	`login` = OLD.login and
	`senha` = OLD.senha
	; 
	  end if;
	else
	  if new.login <> "" and new.senha <> "" then
	    INSERT INTO `tabusuarios` (`idbanco`,`codigousuario`,`nome`,`login`,`senha`,`tipoacesso`) VALUES (new.idbanco,uuid(),new.nome,new.login,new.senha,4);	
	  end if;
	end if; 
	END$$
	DELIMITER ;


	USE `school`;
	DELIMITER $$
	DROP TRIGGER IF EXISTS school.tabservidores_AFTER_DELETE$$
	USE `school`$$
	CREATE DEFINER = CURRENT_USER TRIGGER `school`.`tabservidores_AFTER_DELETE` AFTER DELETE ON `tabservidores` FOR EACH ROW
	BEGIN
	DELETE FROM TABUSUARIOS WHERE login=OLD.login and senha=old.senha and idbanco = old.idbanco;
	END$$
	DELIMITER ;


=== sym_node_security ===

	USE `school`;
	DELIMITER $$
	DROP TRIGGER IF EXISTS school.sym_node_security_BEFORE_INSERT$$
	USE `school`$$
	CREATE DEFINER=`root`@`localhost` TRIGGER `school`.`sym_node_security_BEFORE_INSERT` BEFORE INSERT ON `sym_node_security` FOR EACH ROW
	BEGIN
	   UPDATE `entidade` SET status = 'Configurando' WHERE IDBANCO = new.node_id;    
	END$$
	DELIMITER ;


	USE `school`;
	DELIMITER $$
	DROP TRIGGER IF EXISTS school.sym_node_security_BEFORE_UPDATE$$
	USE `school`$$
	CREATE DEFINER=`root`@`localhost` TRIGGER `school`.`sym_node_security_BEFORE_UPDATE` BEFORE UPDATE ON `sym_node_security` FOR EACH ROW
	BEGIN
	if OLD.registration_time is null and new.registration_time is not null then
	UPDATE `entidade` SET status = 'Configurada' WHERE IDBANCO = new.node_id;  
	end if;
	if OLD.rev_initial_load_enabled = 0 and new.rev_initial_load_enabled = 1 then
	UPDATE `entidade` SET status = 'Publicando' WHERE IDBANCO = new.node_id;  
	end if;
	if OLD.rev_initial_load_enabled = 1 and new.rev_initial_load_enabled = 0 then
	UPDATE `entidade` SET status = 'Publicada' WHERE IDBANCO = new.node_id;  
	end if;
	END$$
	DELIMITER ;


=== tabusuarios ===
==Insert==
	CREATE DEFINER=`root`@`%` TRIGGER `school`.`tabusuarios_AFTER_INSERT` AFTER INSERT ON `tabusuarios` FOR EACH ROW
	BEGIN
	if new.tipoacesso = 4 then
	INSERT INTO `usuariogrupo` (`idbanco`,`codigousuario`,`idgrupo`,`descricao`) VALUES (new.idbanco,new.codigousuario,4,'');
	end if;
	if new.tipoacesso = 5 then
	INSERT INTO `usuariogrupo` (`idbanco`,`codigousuario`,`idgrupo`,`descricao`) VALUES (new.idbanco,new.codigousuario,5,'');
	end if;
	if new.tipoacesso is null then
	INSERT INTO `usuariogrupo` (`idbanco`,`codigousuario`,`idgrupo`,`descricao`) VALUES (new.idbanco,new.codigousuario,3,'');
	end if;

	if new.email <> "" && new.tipoacesso = 2 || new.tipoacesso = 3 || new.tipoacesso = 5 || new.tipoacesso is null then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigousuario,new.idbanco,new.email,1,null, null, new.tipoacesso);
	end if;

	END
==Update==

	CREATE DEFINER=`root`@`localhost` TRIGGER `school`.`tabusuarios_AFTER_UPDATE` AFTER UPDATE ON `tabusuarios` FOR EACH ROW
	BEGIN
	DELETE FROM email WHERE idbanco = old.idbanco and codigo = old.codigousuario and ativo = 1;

	if new.email <> "" && new.email not in (select email from email where codigo = new.codigousuario and idbanco = new.idbanco and email = new.email and ativo = 0 and tipoacesso = 	new.tipoacesso) && new.tipoacesso = 2 || new.tipoacesso = 3 || new.tipoacesso = 5 || new.tipoacesso is null then 
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigousuario,new.idbanco,new.email,1,null, null, new.tipoacesso);
	end if;
	END

==Delete==
	USE `school`;
	
	DELIMITER $$
	
	DROP TRIGGER IF EXISTS school.tabusuarios_AFTER_DELETE$$
	USE `school`$$
	CREATE DEFINER=`root`@`localhost` TRIGGER `school`.`tabusuarios_AFTER_DELETE` AFTER DELETE ON `tabusuarios` FOR EACH ROW
	BEGIN
	
		DELETE FROM email WHERE idbanco = OLD.idbanco and codigo = OLD.codigousuario and ativo = 1 and tipoacesso = old.tipoacesso;
	
	END$$
	DELIMITER ;

===tabescola===
==Insert==
	CREATE DEFINER=`root`@`%` TRIGGER `school`.`tabescola_AFTER_INSERT` AFTER INSERT ON `tabescola` FOR EACH ROW
	BEGIN
	if new.email <> "" && new.email not in (select email from email where codigo = new.codigoescola and idbanco = new.idbanco and email = new.email and ativo = 0 and tipoacesso = 3) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoescola,new.idbanco,new.email,1,null, null, 3);
	end if;
    if new.emaildiretor <> "" && new.emaildiretor not in (select email from email where codigo = new.codigoescola and idbanco = new.idbanco and email = new.emaildiretor and ativo = 0 and tipoacesso 	= 3) then
	INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoescola,new.idbanco,new.emaildiretor,1,null, null, 3);
	end if;
	END

==Update==
	CREATE DEFINER=`root`@`%` TRIGGER `school`.`tabescola_AFTER_UPDATE` AFTER UPDATE ON `tabescola` FOR EACH ROW
	BEGIN

	DELETE FROM email WHERE idbanco = new.idbanco and codigo = new.codigoescola and ativo = 1 and tipoacesso = 3;
		
	if new.email <> "" && new.email not in (select email from email where codigo = new.codigoescola and idbanco = new.idbanco and email = new.email and ativo = 0 and tipoacesso = 3) then
		INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoescola,new.idbanco,new.email,1,null, null, 3);
	end if;	
    
	if new.emaildiretor <> "" && new.emaildiretor not in (select email from email where codigo = new.codigoescola and idbanco = new.idbanco and email = new.emaildiretor and ativo = 0 and tipoacesso = 3) then
		INSERT INTO `email` (`codigo`,`idbanco`,`email`,`ativo`,`dataenvio`, `datadesativacao`, `tipoacesso`) VALUES (new.codigoescola,new.idbanco,new.emaildiretor,1,null, null, 3);
	end if;	
    
	END

==Delete==
	USE `school`;
	DELIMITER $$
	DROP TRIGGER IF EXISTS school.tabescola_AFTER_DELETE$$
	USE `school`$$
	CREATE DEFINER = CURRENT_USER TRIGGER `school`.`tabescola_AFTER_DELETE` AFTER DELETE ON `tabescola` FOR EACH ROW
	BEGIN
	DELETE FROM email WHERE idbanco = OLD.idbanco and codigo = OLD.codigoescola and ativo = 1 and tipoacesso = 3;
	END
	$$
	DELIMITER ;

===entidade===

        ALTER TABLE `school`.`entidade` 
        ADD COLUMN `logoentidade` MEDIUMBLOB NULL AFTER `status`,
        ADD COLUMN `logomunicipio` MEDIUMBLOB NULL AFTER `logoentidade`,
        ADD COLUMN `urlpersonalizada` VARCHAR(100) NULL AFTER `logomunicipio`;




===== Requisitos =====

=== grupo ===

	INSERT INTO `grupo` (`idgrupo`,`nome`,`idfuncionalidadeinicial`) VALUES (0,'Geral','');
	INSERT INTO `grupo` (`idgrupo`,`nome`,`idfuncionalidadeinicial`) VALUES (1,'Estado','');
	INSERT INTO `grupo` (`idgrupo`,`nome`,`idfuncionalidadeinicial`) VALUES (2,'Município','');
	INSERT INTO `grupo` (`idgrupo`,`nome`,`idfuncionalidadeinicial`) VALUES (3,'Escola','');
	INSERT INTO `grupo` (`idgrupo`,`nome`,`idfuncionalidadeinicial`) VALUES (4,'Professor','');
	INSERT INTO `grupo` (`idgrupo`,`nome`,`idfuncionalidadeinicial`) VALUES (5,'Aluno','');

===Configuração Servidor===

	insert into sym_parameter (external_id,node_group_id,param_key,param_value) VALUES ('ALL','client','auto.reload.reverse','true');
	insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values ('ALL','client','initial.load.create.first','true',{ts 	
	'2015-05-26 17:19:54.000'},'install',{ts '2015-05-26 17:19:54.000'});
        
	insert into SYM_NODE (NODE_ID, NODE_GROUP_ID, EXTERNAL_ID, SYNC_ENABLED, SCHEMA_VERSION, SYMMETRIC_VERSION, DATABASE_TYPE, DATABASE_VERSION, HEARTBEAT_TIME, TIMEZONE_OFFSET, 	
	BATCH_TO_SEND_COUNT, BATCH_IN_ERROR_COUNT, CREATED_AT_NODE_ID, DEPLOYMENT_TYPE) values ('server','server','server',1,'?','3.7.17','','2.1',{ts '2015-05-22 
	14:58:51.407'},'-03:00',-1,-1,'server','professional');

	insert into SYM_NODE_SECURITY (NODE_ID, NODE_PASSWORD, REGISTRATION_ENABLED, REGISTRATION_TIME, INITIAL_LOAD_ENABLED, INITIAL_LOAD_TIME, INITIAL_LOAD_ID, INITIAL_LOAD_CREATE_BY,                          
	REV_INITIAL_LOAD_ENABLED, REV_INITIAL_LOAD_TIME, REV_INITIAL_LOAD_ID, REV_INITIAL_LOAD_CREATE_BY, CREATED_AT_NODE_ID) values ('server','5d1c92bbacbe2edb9e1ca5dbb0e481',0,{ts '2015-05-22 
	14:58:44.644'},0,{ts '2015-05-22 14:58:44.644'},null,null,0,null,null,null,'server');

	insert into SYM_NODE_IDENTITY (NODE_ID) values ('server');

	/*
	-- Query: SELECT * FROM school.sym_load_filter
	-- Date: 2016-09-05 16:58
	*/
	INSERT INTO `sym_load_filter` 		
	(`load_filter_id`,`load_filter_type`,`source_node_group_id`,`target_node_group_id`,`target_catalog_name`,`target_schema_name`,`target_table_name`,`filter_on_update`,`filter_on_insert`,`filte
	r_on_delete`,`before_write_script`,`after_write_script`,`batch_complete_script`,`batch_commit_script`,`batch_rollback_script`,`handle_error_script`,`create_time`,`last_update_by`,`last_updat
	e_time`,`load_filter_order`,`fail_on_error`) VALUES ('disable_fk','BSH','client','server',NULL,'school','*',1,1,1,'','','engine.getSqlTemplate().update( \"SET 
	foreign_key_checks=1\",null)',NULL,NULL,'engine.getSqlTemplate().update( \"SET foreign_key_checks=0\",null)','2016-09-02 17:23:59','Documentaion','2016-09-02 17:23:59',1,1);



===== Observações =====
Nenhuma.