Hi,
I want to simplify some daily tasks.
How can I create the DDLs (DROP and CREATE) for an whole model?
In http://modeling.inside.quest.com/thread.jspa?threadID=29753&tstart=0 it’s explained for single entities and relations, but I want create the DDLs for the whole current model.
Can I use the function GetGeneratorSQL of the class PERModel? How I’ve to use this function to save the SQL in file?
Thanks a lot!
Hi,
when you open a model you need to do two simple actions to generate SQL code, press F9 and click the Generate button. What would be the benefit of generating SQL from script? Also your script will have to be executed from somewhere somehow.
I can imagine that you may be interested in generation of SQL scripts for all open models or run TDM from command line to generate SQL… but I am just guessing here. Please write me more information, thank you.
BTW: when you execute SQL script generation from script you will not be able to affect settings. Settings stored with model will be used.
Find below a sample script that generates SQL code for a model:
function GenerateSQLCode(Model)
{
var Log = System.CreateObject( “Log” );
var Application = System.GetInterface( “Application” );
var Stream = System.CreateObject(“TextStream”);
var Generator = Model.DefaultCodeGenerator;
Stream.FileName = Generator.GetFSFileName();
Stream.Encoding = Application.GetConfig( ‘PER’ ).SQLEncoding;
System.RegisterInterface( Generator, “Generator” );
System.RegisterInterface( Stream, “DDLScript” );
System.RegisterInterface( Log, “Log” );
Log.Information("*** Generating SQL code for model ‘"+Model.Name+"’ ");
Generator.Generate();
Stream.OverwriteWarning = true;
Stream.ChangeTextCase(Generator.TextCaseIndex);
Stream.SaveToFile();
Log.Information(" File “+Stream.FileName+” saved ***");
}
The only task is to pass a model to the script. Possibilities:
**Script:**var App = System.GetInterface(“Application”);
var Model = App.Models.GetObject(0);
//… parameter in GetObject determines with which model the script should work.
//0 = first model listed in the Application View, 1 = second model listed in the Application View etc.
or… you can iterate all open models and check if model is physical model and then generate SQL code…
Macro:
var App = System.GetInterface(“Application”);
var Model = App.ActiveModel;
Command line parameter:
If you open a model via command line parameter you will have to pass model ID as well.
Regards,
Vaclav
Thanks a lot. That was very helpfull!
I try to understand the PERCodeGenerator class. Perhaps you can tell me, how can I create only all DROP and all CREATE DDLs, so I can save them into different files? If this not possible, how can I ensure always generating DROP and CREATE DDLs perhaps by manipulating the model settings.
What I try to do is this:
I maintain one model (the MS SQL 2005). Based on this model I create the Oracle 9i and the MySQL 5.0 model. For each model I create the DDLs and the alter scripts. This is every time the same “stupid” work, so I try to script all the steps I’ve to do everytime. Because the “post processing” take 15 minutes everytime, if do small changes or big changes.
Calling the script from command line would be last final step to getting really happy.
Hi Berthold,
properties are managed via Object Types and Properties (OTPs) and you can affect settings via scripting this way:
…
var OTPs = Generator.SelectedOTP;
var i,j;
var ClassContainer, Prop;
for (i = 0; i<OTPs.ClassContainers.Count; i++)
{
ClassContainer = OTPs.ClassContainers.GetObject(i);
Log.Information(ClassContainer.Name);
for (j=0;j< ClassContainer.Properties.Count; j++)
{
Prop = ClassContainer.Properties.GetObject(j);
Log.Information(’ ‘+Prop.Name+’ '+Prop.ExtensionValue);
if (Prop.ExtensionValue == ‘Create’)
Prop.ExtensionValue = ‘Drop’;
}
}
…
Put the code before the Generator.Generate() command to see what it does.
In short: It writes current value to Log and changes extension value from Create to Drop. It doesn’t do exactly what you need you need to modify the code. Also, please pay attention to settings on DDL Script Generation dialog. For example, Keys under Entities can have values “Create All Keys” or “Drop All Keys” and not “Create” or “Drop”.
Good luck,
Vaclav