Remodelar Banco de Dados

Resumo

Campos

tabaluno

codigousuario: alterar para varchar(36)

Catálago de Configuração

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.