Solution to compare mysql schema via sql file

[dkprojektai 1]

Hello,

 

looking for component or any other solution with which I could update client old database. I have a software which on installation writes database from SQL file. From time to time I'm making updates to my database, which have several new tables, new columns, new key's. The problem is to update client database on next installation.

 

I don't prefer using any separate tool for this. 

 

I can use this solution - but go true 50 tables and many columns - not the quick way which I'd like to proceed 🙂

 

Asking for advice quicker solution. Thank you.

 

[Matthias 7]

This tool might help: Database Comparer Tools

 

VCL:

Database Comparer VCL

 

Expensive but it worked for me.

Edited June 18, 2022 by Matthias

[FredS 138]

7 hours ago, dkprojektai said:

Asking for advice quicker solution.

I wrapped most Alter statements into a 'SQL.Patching.pas' unit which fires what is needed depending on a version field at startup.

Found the simplest way was to make all changes and then use Beyond Compare on the db creation script to generate an output which is used to call the methods in the Patching unit.

Edited June 18, 2022 by FredS

[dkprojektai 1]

On 6/18/2022 at 9:54 PM, FredS said:

I wrapped most Alter statements into a 'SQL.Patching.pas' unit which fires what is needed depending on a version field at startup.

Found the simplest way was to make all changes and then use Beyond Compare on the db creation script to generate an output which is used to call the methods in the Patching unit.

Can you share ?

[FredS 138]

57 minutes ago, dkprojektai said:

Can you share ?

I can show you an example but most of my code uses internal stuff.
But let me make one thing clearer; the action after Beyond Compare is manual NOT automatic..

 

In short, after comparing the prior db creation script with the new one methods within the Patching unit are called manually..

 

This example increase the Size of a [N]Varchar Column using UniDAC, FB3/4 and SqlServer dbs:

/// <summary>
///   Increase the Size of a [N]Varchar Column
/// </summary>
/// <param name="NullKind">
///   Must be given else some dbs default to allowing nulls when you make changes
/// </param>
class procedure TExecSqlPatch.IncVarChar(const Con: TUniConnection; const ATable, AColumn: string; const NewSize: Word; const NullKind: TNullKind);
{$REGION 'History'}
//  29-Aug-2018 - ExecuteSQL does not return any usefull count for these Block calls
{$ENDREGION}
var sql: string;
begin
  case Con.DbProvider of
    dbFirebird: begin
        sql := 'EXECUTE block as BEGIN ' +
               ' if (exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = :TableName and rf.RDB$FIELD_NAME = :ColumnName))' + CRLF +
               ' then execute statement ' + 'ALTER TABLE :TableRaw ALTER COLUMN :ColumnRaw Type VARCHAR(:NewSize)'.ToQuoted + SEMICOLON + CRLF +
               'END';
        sql.ReplaceParams([ATable.ToUpper, AColumn.ToUpper, ParamRaw + ATable.ToUpper, ParamRaw + AColumn.ToUpper, NewSize]).Error.Assert;
        Con.ExecuteSQL(sql);
        sql := 'EXECUTE block as BEGIN ' +
               ' if (exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = :TableName and rf.RDB$FIELD_NAME = :ColumnName))' + CRLF +
               ' then execute statement ' + 'ALTER TABLE :TableRaw ALTER :ColumnRaw :NullKind NOT NULL'.ToQuoted + SEMICOLON + CRLF +
               'END';
        sql.ReplaceParams([ATable.ToUpper, AColumn.ToUpper, ParamRaw + ATable.ToUpper, ParamRaw + AColumn.ToUpper,  ParamRaw + cFB3NullKind[NullKind]]).Error.Assert;
        Con.ExecuteSQL(sql);
    end;
    dbSQLServer: begin
        sql := 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = :Table AND [COLUMN_NAME] = :Column) ' + CRLF +
               'BEGIN ' + CRLF +
               '  ALTER TABLE :TableRaw ALTER COLUMN :ColumnRaw NVARCHAR(:NewSize) :NullKind  ' + CRLF +
               'END ';
        sql.ReplaceParams([ATable.ToUpper, AColumn.ToUpper, ParamRaw + ATable.ToUpper, ParamRaw + AColumn.ToUpper, NewSize, ParamRaw + cMSSQLNullKind[NullKind]]).Error.Assert;
        Con.ExecuteSQL(sql);
    end;
    else raise ENotImplemented.Create('Not Implemented');
  end;
end;