Esses dias durante uma consultoria surgiu a necessidade de atualizar um banco de dados que possuía diversos campos do tipo char e deveriam ser modificados para varchar pois isso estava gerando diversos problemas no sistema do cliente.
Para resolver o problema criei um programa simples em delphi que da conta do recado facilmente.
Primeiro vamos construir a interface do programa como abaixo:
Em seguida adicione um Data Module e coloque a propriedade name como dbConnection. Coloque um SqlConnection em seu Data Module como o nome de sqlConnection e configure-o para utilizar um banco de dados.
No meu caso estou utilizando o drive da DevExpress para realizar a conexão com o MSSql porém para utilizar com o drive nativo do delphi as mudanças são muito simples, destacarei elas no final do post.
Vamos então adicionar uma nova unit em nosso projeto, no meu caso salvei a nova unit com o nome de UnitOne.DB.FixFieldsGenerator.pas. Esta unit conterá nossa classe responsável por gerar o script de atualização do banco de dados, sendo assim vamos criar nossa classe chamada TDBFixFieldsGenerator.
Antes declare as units abaixo na uses da seção interface da sua unit:
Forms –> Utilizaremos a variável global Application ques esta nela.
SysUtils –> Utilizaremos algumas funções contidas nesta unit como FreeAndNil.
DBXMSSQL –> Necessária para inicializar as classes de manipulação de metadatas do db express.
DBXCommon-> Necessária para utilizar classes do dbexpress como TDbxConnection.
DBXMetaDataProvider –> Necessária para utilizar classes de manipulação de metadata.
DBXDataExpressMetaDataProvider –> Necessária para utilizar classes de manipulação de metadata pelo drive da Devart.
DBXTypedTableStorage –> Necessária para utilizar classes de armazenamento de coleções de tabelas, utilizado pelo dbx.
Vamos então criar nossa classe como abaixo e em seguida implementar suas funcionalidades:
type
TDBFixFieldsGenerator = class
private
FConnection: TDBXConnection;
FOnlyUpdateCommand: Boolean;
function AddHeaderToScript(script: string): string;
function GenScriptForCollumn(tableName, columnName: string; size: int64): string;
function GenScriptForTable(table: TDBXTablesTableStorage): string;
function GetDBXMetaDataProvider(const AConnection: TDBXConnection): TDBXDataExpressMetaDataProvider;
public
constructor Create(AConnection: TDBXConnection; onlyUpdateCommand: Boolean = False);
function GetFixScript: string;
end;
Vamos entender o funcionamento de nossa classe, primeiramente temos o nosso construtor Create que aceita dois parametros o primeiro chamado AConnection recebe a conexão que sera utilizada para consultar as informações do nosso banco de dados, o segundo parâmetro indica se o script gerado deverá conter apenas os comandos de update e não de modificação de estrutura da tabela do banco de dados.
Em seguida temos a função chamada GetFixScript que fornece o script de atualização do nosso banco de dados utilizando as demais funções.
Note que declaramos uma variável privada chamada FConnection que armazenara a referência de nossa conexão passada pelo construtor e a outra variável chamada FOnlyUpdateCommand também utilizada para armazenar o segundo parâmetro passado pelo construtor.
Temos ainda as funções AddHeaderToScript que apenas adiciona o cabeçalho do script com o descrições do sistema, data, etc. A função GenScriptForCollumn utilizada para gerar o script de alteração da coluna da tabela e a GenScriptForTable utilizada para gerar o script de todas as colunas que necessitam de alteração. Por fim temos a função que pega o DBXMetaDataProvider para podermos solicitar as informações dos tipos de dados das tabelas e objetos do banco de dados.
Vamos começar pelo construtor que ficara como abaixo:
constructor TDBFixFieldsGenerator.Create(AConnection: TDBXConnection; onlyUpdateCommand: Boolean);
begin
FOnlyUpdateCommand := onlyUpdateCommand;
FConnection := AConnection;
end;
Em seguida veremos as demais funções:
function TDBFixFieldsGenerator.GetFixScript: string;
var
provider: TDBXMetaDataProvider;
table: TDBXTablesTableStorage;
begin
provider := GetDBXMetaDataProvider(FConnection);
//Pegamos a lista de tabelas do bd
table := provider.GetCollection(TDBXMetaDataCommands.GetTables) as TDBXTablesTableStorage;
try
//Percorremos a lista de tabelas
while table.Next do
begin
//Verificamos se o tipo da tabela é realmente uma tabela pois Views e alguns outros objetos também são retornados pela consulta dos metadados
if (table.TableType = 'TABLE') then
Result := Result + GenScriptForTable(table);
end;
//Adicionamos o cabeçalho ao script
Result := AddHeaderToScript( Result );
finally
table.Free;
provider.Free;
end;
end;
function TDBFixFieldsGenerator.GetDBXMetaDataProvider(const AConnection: TDBXConnection): TDBXDataExpressMetaDataProvider;
var
provider: TDBXDataExpressMetaDataProvider;
begin
//Apenas criamos o metadataprovider configuramos e retornamos a nova instância
provider := TDBXDataExpressMetaDataProvider.Create;
try
provider.Connection := AConnection;
provider.Open;
except
FreeAndNil(provider);
raise;
end;
Result := provider;
end;
function TDBFixFieldsGenerator.GenScriptForTable(table: TDBXTablesTableStorage): string;
var
script: string;
tableColumns: TDBXColumnsTableStorage;
provider: TDBXMetaDataProvider;
begin
provider := GetDBXMetaDataProvider(FConnection);
//Pegamos as colunas da tabela fornecida pelo parametro table
tableColumns := provider.GetCollection(TDBXMetaDataCommands.GetColumns+' '+table.TableName) as TDBXColumnsTableStorage;
//Percorremos a lista de colunas e verificamos se o tipo em questão é necessário gerar comandos de atualização de estrutura da tabela ou apenas o comando de update.
while tableColumns.Next do
begin
if ((tableColumns.TypeName = 'varchar') and FOnlyUpdateCommand) or
((tableColumns.TypeName = 'char') and not FOnlyUpdateCommand) then
script := script + #13#13 +
GenScriptForCollumn(table.TableName, tableColumns.ColumnName, tableColumns.Precision)+#13;
Application.ProcessMessages;
end;
Result := script;
end;
function TDBFixFieldsGenerator.GenScriptForCollumn(tableName, columnName: string; size: int64): string;
const
scriptFixCommandTemplate = 'if exists (Select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS '+
' where DATA_TYPE<>''varchar'' and TABLE_NAME=''{tableName}'''+
' and COLUMN_NAME=''{columnName}'')'#13+
'begin '#13+
' alter table dbo.{tableName} alter column {columnName} varchar({columnSize}) NULL; '#13+
'%s'+
'end; '#13+
'GO';
scriptUpdateCommandTemplate = ' update dbo.{tableName} set {columnName} = rtrim(ltrim({columnName})); '#13;
scriptRowComment = '--Este bloco %s a coluna "%s" da tabela "%s" '#13'%s';
scriptActionStr: array [0..1] of string = ('modifica','atualiza');
begin
//Aqui simplesmente geramos o script para atualizar a coluna, nenhum segredo apenas comando sql com as informações previamente coletadas pelas funções anteriores.
if FOnlyUpdateCommand then
Result := scriptUpdateCommandTemplate else
Result := Format(scriptFixCommandTemplate,[scriptUpdateCommandTemplate]);
Result := StringReplace(Result, '{columnName}', columnName, [rfReplaceAll]);
Result := StringReplace(Result, '{tableName}', tableName, [rfReplaceAll]);
Result := StringReplace(Result, '{columnSize}', IntToStr(size), [rfReplaceAll]);
Result := Format(scriptRowComment, [scriptActionStr[Integer(FOnlyUpdateCommand)],columnName, tableName, Result]);
end;
function TDBFixFieldsGenerator.AddHeaderToScript(script: string): string;
const header = '--'#13+
'--Script Gerado automaticamente pelo sistema FixDBFields da Unit One Softwares'#13+
'--Gerado em: %s'#13+
'--'#13+
'%s';
var
currentDateStr: string;
begin
currentDateStr := FormatDateTime('dd/MM/yy hh:nn:ss', Now);
Result := Format(header,[currentDateStr, script]);
end;
Com isso nossa classe esta finalizada basta utiliza-la no click do botão de nosso form principal da seguinte forma:
procedure TuiPrincipal.btGerarScriptClick(Sender: TObject);
var
fixFieldsGenerator: TDBFixFieldsGenerator;
begin
btGerarScript.Enabled := False;
dbConnection.sqlConnection.Open;
fixFieldsGenerator := TDBFixFieldsGenerator.Create(dbConnection.sqlConnection.DBXConnection,ckbGerarApenasUpdate.Checked);
try
txtScript.Text := fixFieldsGenerator.GetFixScript;
finally
fixFieldsGenerator.Free;
dbConnection.sqlConnection.Close;
btGerarScript.Enabled := True;
end;
end;
Bom pessoal é isso espero que gostem e como disse escrevi muito rápido esse sistema e achei que seria util para quem não conhece os recursos de consulta de metadados do DBX.
Qualquer dúvida poste um comentário :)
Grande abraço,
Diego Garcia
Nenhum comentário:
Postar um comentário