[firebase-br] Conversão de campo

Quinta Fevereiro 3 20:32:06 -03 2022

Bom dia/tarde/noite Maciel.

Como diz uma antiga propaganda: "Existem várias formas de se tomar Neston".

Vamos tentar resolver usando puramente os recursos do próprio Firebird, sem
quaisquer ferramentas externas.

Você precisará gerar um script de comandos SQLs para efetuar essas
alterações.

Como o tipo atual é um CHAR(3), então um SMALLINT serviria como uma luva,
pois comporta todos os valores de 0 a 999 com muita folga, além de só
consumir 2 bytes internamente (o INTEGER consome 4 bytes e o BIGINT consome
8).

Primeiro precisamos gerar os comandos de criação da nova coluna, que será a
nova CODOPER, e que nesse caso, estou chamando de NOVO_CODOPER.

Execute a seguinte consulta, e cada linha retornada será o respectivo
comando SQL para acrescentar essa nova coluna em cada uma das tabelas em
que constar a coluna CODOPER (se a CODOPER for obrigatória, então
acrescente o NOT NULL):

SELECT 'ALTER TABLE ' || TRIM(RDB$RELATION_NAME) || ' ADD NOVO_CODOPER
SMALLINT;' FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'CODOPER' ORDER
BY RDB$RELATION_NAME;

Copie todas as linhas retornadas para o novo script.

Agora, precisamos copiar o valor atual, do tipo CHAR(3) para a nova coluna
já convertendo para SMALLINT, então:

SELECT 'UPDATE ' || TRIM(RDB$RELATION_NAME) || ' SET NOVO_CODOPER =
CAST(CODOPER AS SMALLINT);' FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME =
'CODOPER' ORDER BY RDB$RELATION_NAME;

Copie todas as linhas retornadas e acrescente ao final do novo script.

Você não informou se a coluna CODOPER está envolvida em alguma chave
estrangeira, então vamos abordar os 2 (dois) casos possíveis.

Mas antes disso, precisaremos criar uma stored procedure que nos retorna os
nomes das colunas de um determinado índice.

Como não sei a versão do Firebird que você está usando, vamos criar uma
stored procedure que retorna os nomes dessas colunas:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE COLUNAS_DO_INDICE(
  NOME_INDICE VARCHAR(30)
)RETURNS(
  COLUNAS VARCHAR(2048)
)AS
DECLARE VARIABLE COLUNA VARCHAR(30);
BEGIN
  COLUNAS = '';
  FOR SELECT RDB$FIELD_NAME FROM RDB$INDEX_SEGMENTS WHERE RDB$INDEX_NAME =
:NOME_INDICE ORDER BY RDB$FIELD_POSITION INTO :COLUNA DO
    COLUNAS = COLUNAS || ',' || TRIM(COLUNA);
  COLUNAS = SUBSTRING(COLUNAS FROM 2);
  SUSPEND;
END^
COMMIT^
SET TERM ; ^

Primeiro, vamos supor que cada tabela que tenha a coluna CODOPER tenha uma
chave estrangeira, pesquisando o valor da CODOPER em outra tabela.

Nesse caso, execute a próxima consulta para gerar os comandos de remoção
das respectivas chaves estrangeiras:

SELECT
  'ALTER TABLE ' || TRIM(A.RDB$RELATION_NAME) ||
  ' DROP CONSTRAINT ' || TRIM(B.RDB$CONSTRAINT_NAME) || ';'
FROM
  RDB$RELATION_FIELDS A,
  RDB$RELATION_CONSTRAINTS B,
  RDB$REF_CONSTRAINTS C,
  RDB$RELATION_CONSTRAINTS D
WHERE
  (A.RDB$FIELD_NAME = 'CODOPER')AND
  (B.RDB$RELATION_NAME = A.RDB$RELATION_NAME)AND
  (B.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')AND
  (C.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME)AND
  (D.RDB$CONSTRAINT_NAME = C.RDB$CONST_NAME_UQ)AND
  EXISTS(
    SELECT E.RDB$FIELD_NAME
    FROM RDB$INDEX_SEGMENTS E
    WHERE(E.RDB$INDEX_NAME = B.RDB$INDEX_NAME)AND(E.RDB$FIELD_NAME =
A.RDB$FIELD_NAME))
ORDER BY
  A.RDB$RELATION_NAME,
  B.RDB$CONSTRAINT_NAME;

Acrescente os comandos gerados no final do script.

Agora, execute a próxima consulta para gerar os comandos de recriação das
respectivas chaves estrangeiras:

SELECT
  'ALTER TABLE ' || TRIM(A.RDB$RELATION_NAME) ||
  ' ADD CONSTRAINT ' || TRIM(B.RDB$CONSTRAINT_NAME) ||
  ' FOREIGN KEY(' || (SELECT COLUNAS FROM
COLUNAS_DO_INDICE(B.RDB$INDEX_NAME)) ||
  ')REFERENCES ' || TRIM(D.RDB$RELATION_NAME) ||
  '(' || (SELECT COLUNAS FROM COLUNAS_DO_INDICE(D.RDB$INDEX_NAME)) || ')' ||
  CASE WHEN(TRIM(C.RDB$DELETE_RULE) <> 'RESTRICT')THEN(' ON DELETE ' ||
TRIM(C.RDB$DELETE_RULE))ELSE '' END ||
  CASE WHEN(TRIM(C.RDB$UPDATE_RULE) <> 'RESTRICT')THEN(' ON UPDATE ' ||
TRIM(C.RDB$UPDATE_RULE))ELSE '' END || ';'
FROM
  RDB$RELATION_FIELDS A,
  RDB$RELATION_CONSTRAINTS B,
  RDB$REF_CONSTRAINTS C,
  RDB$RELATION_CONSTRAINTS D
WHERE
  (A.RDB$FIELD_NAME = 'CODOPER')AND
  (B.RDB$RELATION_NAME = A.RDB$RELATION_NAME)AND
  (B.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')AND
  (C.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME)AND
  (D.RDB$CONSTRAINT_NAME = C.RDB$CONST_NAME_UQ)AND
  EXISTS(
    SELECT E.RDB$FIELD_NAME
    FROM RDB$INDEX_SEGMENTS E
    WHERE(E.RDB$INDEX_NAME = B.RDB$INDEX_NAME)AND(E.RDB$FIELD_NAME =
A.RDB$FIELD_NAME))
ORDER BY
  A.RDB$RELATION_NAME,
  B.RDB$CONSTRAINT_NAME;

Acrescente os comandos gerados no final do script.

Como "seguro morreu de velho", vamos abordar o segundo caso, que apesar de
improvável, não é impossível, ou seja, outra(s) tabela(s) terem chaves
estrangeiras para a coluna CODOPER da tabela atual.

Vamos gerar os comandos de remoção das respectivas chaves estrangeiras:

SELECT
  'ALTER TABLE ' || TRIM(A.RDB$RELATION_NAME) ||
  ' DROP CONSTRAINT ' || TRIM(A.RDB$CONSTRAINT_NAME) || ';'
FROM
  RDB$RELATION_CONSTRAINTS A,
  RDB$REF_CONSTRAINTS B,
  RDB$RELATION_CONSTRAINTS C,
  RDB$RELATION_FIELDS D
WHERE
  (A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')AND
  (B.RDB$CONSTRAINT_NAME = A.RDB$CONSTRAINT_NAME)AND
  (C.RDB$CONSTRAINT_NAME = B.RDB$CONST_NAME_UQ)AND
  (D.RDB$RELATION_NAME = C.RDB$RELATION_NAME)AND
  (D.RDB$FIELD_NAME = 'CODOPER')AND
  EXISTS(
    SELECT E.RDB$FIELD_NAME
    FROM RDB$INDEX_SEGMENTS E
    WHERE(E.RDB$INDEX_NAME = C.RDB$INDEX_NAME)AND(E.RDB$FIELD_NAME =
D.RDB$FIELD_NAME))
ORDER BY
  A.RDB$RELATION_NAME,
  A.RDB$CONSTRAINT_NAME;

Agora, execute a próxima consulta para gerar os comandos de recriação das
respectivas chaves estrangeiras:

SELECT
  'ALTER TABLE ' || TRIM(A.RDB$RELATION_NAME) ||
  ' ADD CONSTRAINT ' || TRIM(A.RDB$CONSTRAINT_NAME) ||
  ' FOREIGN KEY(' || (SELECT COLUNAS FROM
COLUNAS_DO_INDICE(A.RDB$INDEX_NAME)) ||
  ')REFERENCES ' || TRIM(C.RDB$RELATION_NAME) ||
  '(' || (SELECT COLUNAS FROM COLUNAS_DO_INDICE(C.RDB$INDEX_NAME)) || ')' ||
  CASE WHEN(TRIM(B.RDB$DELETE_RULE) <> 'RESTRICT')THEN(' ON DELETE ' ||
TRIM(B.RDB$DELETE_RULE))ELSE '' END ||
  CASE WHEN(TRIM(B.RDB$UPDATE_RULE) <> 'RESTRICT')THEN(' ON UPDATE ' ||
TRIM(B.RDB$UPDATE_RULE))ELSE '' END || ';'
FROM
  RDB$RELATION_CONSTRAINTS A,
  RDB$REF_CONSTRAINTS B,
  RDB$RELATION_CONSTRAINTS C,
  RDB$RELATION_FIELDS D
WHERE
  (A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')AND
  (B.RDB$CONSTRAINT_NAME = A.RDB$CONSTRAINT_NAME)AND
  (C.RDB$CONSTRAINT_NAME = B.RDB$CONST_NAME_UQ)AND
  (D.RDB$RELATION_NAME = C.RDB$RELATION_NAME)AND
  (D.RDB$FIELD_NAME = 'CODOPER')AND
  EXISTS(
    SELECT E.RDB$FIELD_NAME
    FROM RDB$INDEX_SEGMENTS E
    WHERE(E.RDB$INDEX_NAME = C.RDB$INDEX_NAME)AND(E.RDB$FIELD_NAME =
D.RDB$FIELD_NAME))
ORDER BY
  A.RDB$RELATION_NAME,
  A.RDB$CONSTRAINT_NAME;

Reorganize o script para que TODOS os comandos de DROP CONSTRAINT fiquem
antes dos comandos de ADD CONSTRAINT.

Nesse ponto, o script já contém os comandos para:

1 - Criar a nova coluna NOVO_CODOPER.

2 - Copiar o valor de CODOPER para NOVO_CODOPER, já convertendo de texto
para número.

3 - Remoção das possíveis chaves estrangeiras envolvidas, caso hajam.

4 - Recriação das possíveis chaves estrangeiras envolvidas, caso hajam.

Como já deve imaginar, os comandos do item 4 (recriação das chaves
estrangeiras), devem ficar por último no script.

Precisamos acrescentar 2 conjuntos de comandos antes desse item 4.

O primeiro conjunto de comandos serve para excluir a antiga coluna CODOPER:

SELECT 'ALTER TABLE ' || TRIM(RDB$RELATION_NAME) || ' DROP COLUMN CODOPER;'
FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'CODOPER' ORDER BY
RDB$RELATION_NAME;

E o segundo conjunto de comandos serve para renomear a coluna NOVO_CODOPER
para CODOPER:

SELECT 'ALTER TABLE ' || TRIM(RDB$RELATION_NAME) || ' ALTER COLUMN
NOVO_CODOPER TO CODOPER;' FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME =
'CODOPER' ORDER BY RDB$RELATION_NAME;

E como eu citei antes, esses 2 conjuntos de comandos ficam antes dos
comandos de recriação das chaves estrangeiras.

Então agora temos a seguinte sequência de comandos:

1 - Criar a nova coluna NOVO_CODOPER.

2 - Copiar o valor de CODOPER para NOVO_CODOPER, já convertendo de texto
para número.

3 - Remoção das possíveis chaves estrangeiras envolvidas, caso hajam.

4 - Exclusão da antiga coluna CODOPER.

5 - Renomeação da nova coluna NOVO_CODOPER para CODOPER.

6 - Recriação das possíveis chaves estrangeiras envolvidas, caso hajam.

Pronto, agora é só revisar, e quando você achar que está tudo correto, faça
logo um backup da base de dados antes de executar esse script.

Espero ter lhe ajudado mais que atrapalhado. :D

Em qua., 2 de fev. de 2022 às 09:29, Maciel Soncini Bueno via lista <
lista em firebase.com.br> escreveu:

> Olá tudo bem com vocês?
>
> Tenho um campo tipo char(03) de nome CODOPER.
>
> Esse campo está presente em centenas de tabelas do meu banco.
>
> Os valores são numéricos, tipo 005, 010, 230, etc...
>
> Existe referência de foreing Key para esse campo em diversas tabelas.
>
> Preciso converter esse campo para o tipo integer.
>
> Existe alguma forma mais rápida converter esse campo em todas as tabelas e
> sem perder o conteúdo do mesmo?
> ______________________________________________
> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
> Para saber como gerenciar/excluir seu cadastro na lista, use:
> http://www.firebase.com.br/fb/artigo.php?id=1107
> Para consultar mensagens antigas:
> http://www.firebase.com.br/pesquisa_lista.html
>