Ajustes Virada

grant.sql

CREATE USER 'knowtec' IDENTIFIED BY 'RI9n5Tpv3gUCDC';
FLUSH PRIVILEGES;
DROP USER 'knowtec'@'localhost';

GRANT ALL ON GLOBAL_SI TO 'knowtec'@'%' IDENTIFIED BY 'RI9n5Tpv3gUCDC';

Select * 
from mysql.user 
where user = 'knowtec'; 


select * from noticias;

rollback_2012.sql

-- SCRIPT VIRADA SI - ROLLBACK 2012

-- DELETA tabela noticias_2013.
DROP TABLE GLOBAL_SI.noticias_2013;

-- ATUALIZA vis√£o noticias, apontando para 2012.
CREATE OR REPLACE VIEW noticias
AS
   SELECT idnoticia,
          idMidia,
          idsecao,
          idcoluna,
          dtPost,
          dtPostSemHora,
    	   url,
          jornalista,
          titulo,
          chamada,
          textoNoticia,
          coluna,
          nomeSecao
     FROM GLOBAL_SI.noticias_2012
     WITH CHECK OPTION;
COMMIT;

virada.sh

#!/bin/bash
echo "* Executing 2013 New Year Script *" > /home/thania.clair/virada/log.txt
echo "Start: $(date)" >> /home/thania.clair/virada/log.txt
/usr/bin/mysql --user=knowtec --host=bd.si.knowtec.com --database=GLOBAL_SI --password=RI9n5Tpv3gUCDC < /home/thania.clair/virada/2013.sql
echo "End: $(date)" >> /home/thania.clair/virada/log.txt
echo "* Finished 2013 New Year Script *" >> /home/thania.clair/virada/log.txt

virada.sql

-- SCRIPT VIRADA SI 2013

-- NOVA TABELA 2013
CREATE TABLE GLOBAL_SI.noticias_2013 (
  `idnoticia` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idMidia` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `idsecao` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `idcoluna` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `dtPost` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dtPostSemHora` date NOT NULL,
  `url` varchar(1000) DEFAULT NULL,
  `jornalista` text,
  `titulo` text,
  `chamada` text,
  `textoNoticia` longtext,
  `coluna` text,
  `nomeSecao` text,
  PRIMARY KEY  (`idnoticia`),
  KEY `INDICESECAO` (`idsecao`),
  KEY `INDICECOLUNA` (`idcoluna`),
  KEY `INDICEMIDIA` (`idMidia`),
  KEY `XIE1noticias_2013` (`dtPostSemHora`)
) ENGINE=InnoDB AUTO_INCREMENT=200000 DEFAULT CHARSET=utf8;
COMMIT;

-- INCREMENTO para iniciar na ULTIMA NOTICIA
SET @IDNOTICIA = (SELECT MAX(idnoticia) + 1 FROM GLOBAL_SI.noticias_2012);
SET @ALTER_SQL = CONCAT('ALTER TABLE GLOBAL_SI.noticias_2013 AUTO_INCREMENT = ', @IDNOTICIA);
PREPARE stmt FROM @ALTER_SQL;
EXECUTE stmt;

-- VIEW DE NOTICIAS apontando para 2013
CREATE OR REPLACE VIEW noticias
AS
   SELECT idnoticia,
          idMidia,
          idsecao,
          idcoluna,
          dtPost,
          dtPostSemHora,
      url,
          jornalista,
          titulo,
          chamada,
          textoNoticia,
          coluna,
          nomeSecao
     FROM GLOBAL_SI.noticias_2013
     WITH CHECK OPTION;
COMMIT;

virada2.slq

-- ANTES
CREATE OR REPLACE VIEW noticias
AS
   SELECT idnoticia,
          idMidia,
          idsecao,
          idcoluna,
          dtPost,
          dtPostSemHora,
      url,
          jornalista,
          titulo,
          chamada,
          textoNoticia,
          coluna,
          nomeSecao,
	  dtCategorizado,
	  dhCriacao,
	  categorizado,
	  dhAlteracao
     FROM GLOBAL_SI.noticias_2012
   UNION ALL	
   SELECT idnoticia,
          idMidia,
          idsecao,
          idcoluna,
          dtPost,
          dtPostSemHora,
	  url,
          jornalista,
          titulo,
          chamada,
          textoNoticia,
          coluna,
          nomeSecao,
	  dtCategorizado,
	  dhCriacao,
	  categorizado,
	  dhAlteracao
     FROM GLOBAL_SI.noticias_2013
COMMIT;

-- DEPOIS
CREATE OR REPLACE VIEW noticias
AS
   SELECT idnoticia,
          idMidia,
          idsecao,
          idcoluna,
          dtPost,
          dtPostSemHora,
	  url,
          jornalista,
          titulo,
          chamada,
          textoNoticia,
          coluna,
          nomeSecao,
	  dtCategorizado,
	  dhCriacao,
	  categorizado,
	  dhAlteracao
     FROM GLOBAL_SI.noticias_2013
     WITH CHECK OPTION;
COMMIT;