Jump to content
dkprojektai

Solution to compare mysql schema via sql file

Recommended Posts

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.

 

Share this post


Link to post
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 by FredS

Share this post


Link to post
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 ?

Share this post


Link to post
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;

 

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×