Chapter 4 Generating and Reversing a DataBase Using Scripts

Generating a Database Using Setting and Selection

You can use settings and selections with scripting before starting the database generation using respectively the following methods from the model: UseSettings(ByVal Function As Long, ByVal Name As String = "") As Boolean and UseSelection(ByVal Function As Long, ByVal Name As String = "") As Boolean.

Given the PDM sample (Project.PDM) in the PowerDesigner installation folder, which contains two selections:

"Organization" selection includes tables DIVISION, EMPLOYEE, MEMBER & TEAM.
"Materials" selection includes tables COMPOSE, MATERIAL, PROJECT & USED.

The following example shows you how to

Generate a first script of this model for the "Organization" selection using first setting (setting1)
Generate a test data creation script for the tables contained in this selection.
Generate a second script of this model for the "Materials" selection and a test data creation script for the tables it contains using second setting (setting2).

Example:

' Generated sql scripts will be created in 'GenDir' directory
' there names is the name of the used selection with extension ".sql" for DDL scripts
' and extension "_td.sql" for DML scripts (for test data generations).
Option Explicit

Const GenDir = "D:\temp\test\"

Const setting1 = "Tables & Views (with permissions)"
Const setting2 = "Triggers & Procedures (with permissions)"
Start EvaluateNamedPath("%_EXAMPLES%\project.pdm")

Sub Start(sModelPath)
   on error resume next
   dim pModel : Set pModel = OpenModel(sModelPath)
   If (pModel is Nothing) then
      Output "Unable to open model " & sModelPath
      Exit Sub
   End if

   GenerateDatabaseScripts pModel, "Organization" setting1
   GenerateTestDataScript pModel, "Organization" setting1

   GenerateDatabaseScripts pModel, "Materials" setting2
   GenerateTestDataScript pModel, "Materials" setting2
   pModel.Close
   on error goto 0
End Sub

Sub GenerateDatabaseScripts(pModel, sSelectionName, sSettingName)
   Dim pOpts : Set pOpts = pModel.GetPackageOptions()
   InteractiveMode = im_Batch ' Avoid displaying generate window
' set generation options using model package options
   pOpts.GenerateODBC = False ' Force sql script generation rather than ODBC
   pOpts.GenerationPathName = GenDir
   pOpts.GenerationScriptName = sSelectionName & ".sql"
 ' Launch the Generate Database feature with selected objects
   pModel.UseSelection fct_DatabaseGeneration, sSelectionName
   pModel.UseSetting fct_DatabaseGeneration, sSettingName
   pModel.GenerateDatabase
End Sub

Sub GenerateTestDataScript(pModel, sSelectionName)
   Dim pOpts : Set pOpts = pModel.GetPackageOptions()
   InteractiveMode = im_Batch ' Avoid displaying generate window
   ' set generation options using model package options
   pOpts.TestDataGenerationByODBC = False ' Force sql script generation rather than ODBC
   pOpts.TestDataGenerationDeleteOldData = False
   pOpts.TestDataGenerationPathName = GenDir
   pOpts.TestDataGenerationScriptName = sSelectionName & "_td.sql"
' Launch the Generate Test Data feature for selected objects
   pModel.UseSelection fct_TestDataGeneration, sSelectionName
   pModel.GenerateTestData 
End Sub

Selection and setting creation

You can create a persistent selection that can be used in database generation by transforming a selection into a persistent selection..

Example:

Option Explicit
   Dim pActiveModel
   Set pActiveModel = ActiveModel

   Dim Selection, PrstSel
   Set Selection = pActiveModel.createselection
   Selection.AddActiveSelectionObjects

   Set PrstSel = Selection.CreatePersistentSelectionManager("test")