Toad World® Forums

How to create alter script?


#1

Hi,

is it possible to script the steps of generating an alter script?

I do following steps:
1.) Select Left Side (Model1): open a model file
2.) Select Right Side: open a model file
3.) Settings:
3a) Comparison Rules:

  • Ignore Text Case: disabled (!!!)
  • Ignore Model Name: disabled (default)
  • Resolve Application Variables: disabled (default)
    3b) Option for Default Selection of Items:
  • Select items that exist in Model 1 and are missing in Model 2 (CREATE): enabled (default)
  • Select items that don’t exist in Model 1 and available in Model 2 (DROP): enabled (default)
  • Select items that exist in both models but are unequal (ALTER): enabled (default)
    3c) Settings:
  • Log Process to File: disabled (default)
    4.) Select Object Types and Properties: Only Physical Properties (default)
    5.) Output File: Model 1 Name with “AlterScript.sql”

How can I script this steps?

Thanks a lot!


#2

Hi,

Currently you cannot use scripting to do what Sync & Convert wizard offers. It is too complicated task to automate synchronization (or conversion) and some settings need to be defined via GUI.

Regards,

Vaclav


#3

Is there no chance to set the settings via script and run the job via script?
I’ve always the same settings, because the alter script genering is a “post processing” process. I maintain my main model (using MS SQL 2005) and convert that model to Oracle 9i and MySQL 5.0. And than I create the alter scripts. The steps to clean up theconverted models are already scripted.

There is a class named Convertor. There are two properties Converting and Altering. So I think, I can use this, or not?

Thanks for your time.


#4

Hi Berthold,

theoretically yes, but it is not recommended. Synchronization features belong among the most complicated parts of the application and all the functionality is not managed by scripting only.

Regards,

Vaclav


#5

Perhaps I can convince you to help me.

My daily task is this:
1.) Maintain and save MS SQL model
2.) Prepare PostProcessing
a) Ensure only the MS SQL model is open
b) Tools -> Scripting Window
b) Copy 01_ModelCheck.js into window
3.) MS SQL Post Processing:
a) Run script
b) Create Alter Scripts:
I) File -> Sync & Convert -> Generate Alter Script
II) Select Left Side (Model1): Current Model
III) Select Right Side: Choose previous version of MS SQL model
IV) Settings:
~ Comparison Rules:
- Ignore Text Case: disabled (!!!)
- Ignore Model Name: disabled (default)
- Resolve Application Variables: disabled (default)
~ Option for Default Selection of Items:
- Select items that exist in Model 1 and are missing in Model 2 (CREATE): enabled (default)
- Select items that don’t exist in Model 1 and available in Model 2 (DROP): enabled (default)
- Select items that exist in both models but are unequal (ALTER): enabled (default)
~ Settings:
- Log Process to File: disabled (default)
V) Select Object Types and Properties: Only Physical Properties (default)
VI) Output File: Model 1 Name with “_AlterScript.sql”
c) Clode model
4.) Create MySQL model:
a) Convert:
I) File -> Sync & Convert -> Sync & Convert Wizard
II) Select Left Side (Source Model): Choose MS SQL Model
III) Select Right Side (Target Database Platform): MySQL 5.0
IV) Settings:
~ Comment Out Database Specific Items: disable (!!)
~ Resolve Application Variables: disable (default)
~ Log Process to File: disable (default)
V) Select Object Types and Properties: Convert all
VI) New Model Name: e.g. “20101214_1444_MySQL”
b) Save: e.g. “20101214_1444_MySQL.txp”
c) Run script
d) Create Alter Scripte:
I) File -> Sync & Convert -> Generate Alter Script
II) Select Left Side (Model1): Current MySQL modell
III) Select Right Side: Choose previous MySQL modell
IV) Settings:
~ Comparison Rules:
- Ignore Text Case: disabled (!!!)
- Ignore Model Name: disabled (default)
- Resolve Application Variables: disabled (default)
~ Option for Default Selection of Items:
- Select items that exist in Model 1 and are missing in Model 2 (CREATE): enabled (default)
- Select items that don’t exist in Model 1 and available in Model 2 (DROP): enabled (default)
- Select items that exist in both models but are unequal (ALTER): enabled (default)
~ Settings:
- Log Process to File: disabled (default)
V) Select Object Types and Properties: Only Physical Properties (default)
VI) Output File: Model 1 Name with “_AlterScript.sql”
e) Close model
4.) Create Oracle model:
a) Convert:
I) File -> Sync & Convert -> Sync & Convert Wizard
II) Select Left Side (Source Model): Choose MS SQL Model
III) Select Right Side (Target Database Platform): Oracle 9i
IV) Settings:
~ Comment Out Database Specific Items: disable (!!)
~ Resolve Application Variables: disable (default)
~ Log Process to File: disable (default)
V) Select Object Types and Properties: Convert all
VI) New Model Name: e.g. “20101214_1444_Oracle”
b) Save: e.g. “20101214_1444_Oracle.txp”
c) Run script
d) Create Alter Scripte:
I) File -> Sync & Convert -> Generate Alter Script
II) Select Left Side (Model1): Current Oracle modell
III) Select Right Side: Choose previous Oracle modell
IV) Settings:
~ Comparison Rules:
- Ignore Text Case: disabled (!!!)
- Ignore Model Name: disabled (default)
- Resolve Application Variables: disabled (default)
~ Option for Default Selection of Items:
- Select items that exist in Model 1 and are missing in Model 2 (CREATE): enabled (default)
- Select items that don’t exist in Model 1 and available in Model 2 (DROP): enabled (default)
- Select items that exist in both models but are unequal (ALTER): enabled (default)
~ Settings:
- Log Process to File: disabled (default)
V) Select Object Types and Properties: Only Physical Properties (default)
VI) Output File: Model 1 Name with “_AlterScript.sql”
e) Close model

Only step one is my real work. The other steps I want to do it automatically, because I do only this step.

The problem with previous model names are easy, because I’ve a naming convention and I would edit a variable for this, if there is no other way.


#6

Hi Berthold,

I have another suggestion for you. Intead of writing scripts to skip wizards and close open models… why don’t you use some automation software that will do all the necessary mouse clicks for you?

I tried this one:
http://download.cnet.com/Auto-m8/3000-2084_4-10726164.html
to run Sync & Convert wizard and convert my Oracle model to MySQL model and it worked fine :slight_smile:

There are also other products avaialble for free, see:
http://download.cnet.com/windows/automation-software/1950-2084_4-0.html?filter=licenseName%3D%22Free%22%7C&filterName=licenseName%3DFree%7C&tag=ltcol;narrow

Regards,

Vaclav


#7

Thanks. But that is not the way I want to do the job.

Than I have this steps:
1.) Maintain and save MS SQL model
2.) Prepare PostProcessing
a) Ensure only the MS SQL model is open
b) Tools -> Scripting Window
b) Copy 01_ModelCheck.js into window
3.) MS SQL Post Processing:
a) Run script
b) Run mouse click script “MS SQL Alter”
c) Clode model
4.) Create MySQL model:
a) Run mouse click script “MySQL Migration”
b) Save: e.g. “20101214_1444_MySQL.txp”
c) Run script
d) Run mouse click script “MySQL Alter”
e) Close model
4.) Create Oracle model:
a) Run mouse click script “Oracle Migration”
b) Save: e.g. “20101214_1444_Oracle.txp”
c) Run script
d) Run mouse click script “Oracle Alter”
e) Close model

I need:

  • My Toad Script
  • 5 Mouse Click Script I’ve to run by my self
  • and many manuell steps like running the right script, saving, renaming and so on…

I wonder why I can’t use only one Toad Script, that do all my job, because it’s always the same.

I will try to use your class Convertor and the Porperties Converting and Altering. I think that should work. Perhaps you can help me.


#8

I try to get the Converter of my model, but this fails.

My script:
— SNIP —
// ----- Global settings
// Is there any better way? How to determine the model path?
// http://modeling.inside.quest.com/thread.jspa?threadID=30279&tstart=0
var BASE_PATH = “U:\”;

function main(){
// ----- Boostrapping
// Get main application object
var App = System.GetInterface(“Application”);
// Search for only open model
var Model = App.Models.GetObject(0);
if (Model == null) {
System.ShowMessageDialog(1000,‘AdaptEntitiesDialog’,‘No model is open. Skip script…’,3,4);
return;
}
if (Model.Workspaces.Count > 1) {
System.ShowMessageDialog(1000,‘AdaptEntitiesDialog’,‘Please close all models but main model. Skip script…’,3,4);
return;
}

// Get the first model
var WS = Model.Workspaces.GetObject(0);

// Get logger
var Log = System.CreateObject("Log");

// Prevent auto updating GUI and parallel changes
Model.Lock();

var i, Converter, Converters;

Log.Information(Model.Name);
Converters = Model.Convertors;
Log.Information(Converters == null); // <--- Fails!
Log.Information(Convertors.Count);

for (i=0; i<Convertors.Count; i++) {
    Converter = Convertors.GetObject(i);
    Log.Information(Converter.Name);
}

Model.Unlock();
System.RefreshModel(Model);

}
— SNAP —


#9

Hi Berthold,

I am trying to help and save your time. As I wrote, synchronization and conversion features belong among the most complicated parts of TDM. Not all the functionality is written in JavaScript. I really like scripting, unfortunately not 100% of tasks can be done via scripting perfectly.

I still think the better way is to use some “mouse ghost” software and version control system to keep track of modified files. You will only have to open your MS SQL model and run some “mouse ghost macro” that will do the mouse clicks for you and convert your MS SSQL model to Oracle and MySQL and instead of adding timestamps to files you can use version control system like SubVersion etc.

If doing conversion via script was a good idea I would not discourage you from trying to write such scripts. Unfortunately, conversion + scripting = dangerous combination.

Regards,

Vaclav


#10

I know. And thanks for all the time you spend here.

Perhaps it could be a nice feature for TDM supporting converting and alter generating via script.

I don’t understand, why it’s dangerous using TDM script for converting, but it’s not dangerous to use mouse click scripts for converting.

Perhaps it would be possible to offer an interface to do same job like the GUI wizard does.

But is it possible to script it? The danger is on my part :wink:

Thanks a lot.


#11

Hi Berthold,

I agree it would be a nice feature. It would help users to automate tasks and save their time. I clearly see possible benefits, unfortunately right now sychronization tasks cannot be scripted.

BTW: Another reason why I have to discourage you from writing scripts for synchronization is that new version will have to contain modified system scripts. TDM uses MS Scripting engine (standard part of Windows operating systems) significantly. To our regret IE9 BETA affects also the scripting engine and we have been working on a solution/workaround now.

Regards,

Vaclav