change script : add sql

Toad Data Modeler
1

Hi everbody !

I use Toad data modeler with git support in order to have distincts versions of my project database.

My database model is deployed on many environements, and then, many servers.

So, it’s possible i have a environnement with a specific version and another environnement with another version.

In order to migrate my databases i use the “generate change script” tool wich produce SQL.

What i want to do : insert at each update (Each change script) a new line in a table.

Can i ask Toad adding a specific SQL script when he generate the SQL change script ? This script, always called, will thus be able to contain the version of the database.

Is it possible ?

Thanks for your replies !

2

Hello lgerard,

can you describe your problem more preciselly please. We (me and team) are not really sure what you need.

The part with generating change script seems to be clear, but what do you mean by *“insert at each update (each change script) a new line in a table”? *Do you mean new record in some table (something like log)?

Thanks, Michal

3

Yes, indeed, something like this !

My idea is indeed keeping logs about each update. In fact each time a modification SQL script is called.

4

Hello,

I’m still not sure if I understand good. For example let’s have two models with two different entities. All change script will be:

ALTER TABLE [Entity2] DROP
COLUMN [Attribute2]
go

ALTER TABLE [Entity2] ADD
[Attribute1] Char(1) NULL
go

ALTER TABLE [Entity1] ALTER COLUMN [Attribute1] Char(1) NULL
go

So it is one change script, but there are three alter statements, that modify two tables. This example is for MS SQL Server, because I don’t know your platform. It can be important to know it.

I understand, that you want to generate some INSERT statement. Below are variants that I have in my mind.

INSERT INTO LogTable … //Information about model versions
go

ALTER TABLE [Entity2] DROP
COLUMN [Attribute2]
go

ALTER TABLE [Entity2] ADD
[Attribute1] Char(1) NULL
go

ALTER TABLE [Entity1] ALTER COLUMN [Attribute1] Char(1) NULL
go

INSERT INTO LogTable … //Information About Entity2
go

ALTER TABLE [Entity2] DROP
COLUMN [Attribute2]
go

ALTER TABLE [Entity2] ADD
[Attribute1] Char(1) NULL
go

INSERT INTO LogTable … //Information About Entity1
go

ALTER TABLE [Entity1] ALTER COLUMN [Attribute1] Char(1) NULL
go

INSERT INTO LogTable … //Information About Drop Column in Entity2
go

ALTER TABLE [Entity2] DROP
COLUMN [Attribute2]
go

INSERT INTO LogTable … //Information About Create Column in Entity2
go

ALTER TABLE [Entity2] ADD
[Attribute1] Char(1) NULL
go

INSERT INTO LogTable … //Information About Entity1
go

ALTER TABLE [Entity1] ALTER COLUMN [Attribute1] Char(1) NULL
go

Version 1 is possible. I don’t know if version 2 or 3 are possible too, I know only that it will be very difficult in current version.

Regards
Daril