quinta-feira, 3 de março de 2011

Liferay - INSERTs dos estados brasileiros


Pessoal, bom dia. Depois de pesquisar no forum da Liferay encontrei algumas pessoas com o mesmo problema : Como inserir REGIONS no liferay? Na versão 5.2.3 não existe nenhuma interface, pelo menos não encontrei.

Então, após alguns testes fiz os INSERTs das regions e funcionou perfeitamente, segue baixo uma PROC (MYSQL) que faz os INSERTS. Sei que não é uma boa prática alterações diretamente na base do Liferay (ou até em outros sistemas), todavia não encontrei outra saída.Se alguém souber uma menira mais interessante ou até melhor comenta ai.

DROP PROCEDURE IF EXISTS spCriaEstadosBrasil;

CREATE PROCEDURE spCriaEstadosBrasil()
  BEGIN

  SET @ctrId = 0, @regionid = 48001;

  -- Pega o codigo do Brazil no Liferay
  select  @ctrId:=countryId from country where name = 'Brazil';
  
  -- Insere os estados
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT @regionid AS regionId ,  @ctrId,'AC','Acre',1 ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'AL','Alagoas',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'AM','Amazonas',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'AP','Amapá',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'BA','Bahia',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'CE','Ceará',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'DF','Distrito Federal',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'ES','Espírito Santo',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'GO','Goiás',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'MA','Maranhão',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'MG','Minas Gerais',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'MS','Mato Grosso do Sul',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'MT','Mato Grosso',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'PA','Pará',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'PB','Paraíba',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'PE','Pernambuco',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'PI','Piauí',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'PR','Paraná',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'RJ','Rio de Janeiro',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'RN','Rio Grande do Norte',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'RR','Roraima',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'RO','Rondônia',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'RS','Rio Grande do Sul',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'SC','Santa Catarina',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'SE','Sergipe',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'SP','São Paulo',1 FROM region WHERE countryId=  @ctrId ;
  INSERT INTO region(regionId,countryId,regionCode,name,active_)SELECT MAX(REGIONID)+1 AS regionId ,  @ctrId,'TO','Tocantins',1 FROM region WHERE countryId=  @ctrId ;
  END;
  
  call spCriaEstadosBrasil;
  
  select * FROM region where countryid in ( 48);