Chapter 4 Generating and Reversing a DataBase Using Scripts

Generating a Database Using Scripts

When you need to generate a database using script, you may use the following methods:

GenerateDatabase(ByVal ObjectSelection As BaseObject = Nothing)
GenerateTestData(ByVal ObjectSelection As BaseObject = Nothing)

In the following example, you:

Open an existing model.
Generate a script for the model.
Modify the model.
Generate a modified database script.
Generate a set of test data.

Opening an existing model

In the following example, we begin with opening an existing model (ASA 9) using the following method: OpenModel (filename As String, flags As Long =omf_Default) As BaseObject.

Be sure to add a final backslash (\) to the generation directory.

Then we are going to generate a database script for the model, modify the model, generate a modified data script, and generate a set of test data using respectively the following methods:

GenerateDatabaseScripts pModel
ModifyModel pModel
GenerateAlterScripts pModel
GenerateTestDataScript pModel

Example

Option Explicit
Const GenDir = "D:\temp\test\"
Const Modelfile = "D:\temp\phys.pdm"
Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Start
Sub Start()
   dim pModel : Set pModel = OpenModel(Modelfile)
   If (pModel is Nothing) then
      Output "Unable to open the model"
      Exit Sub
   End if
End Sub

Generating a script for the model

Then you generate a script for this model in the folder defined in the "GenDir" constant using the following method: GenerateDatabase(ByVal ObjectSelection As BaseObject = Nothing).

As you would do in the generation database dialog box, you have to define the generation directory and the sql file name before starting the generation, see the following example.

Example

Sub GenerateDatabaseScripts(pModel)
   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 ' Define generation directory
   pOpts.GenerationScriptName = "script.sql" ' Define sql file name
   pModel.GenerateDatabase ' Launch the Generate Database feature
End Sub

Modifying the model

After, you modify the model by adding a column to each table:

Example

Sub ModifyModel(pModel)
   dim pTable, pCol
   ' Add a new column in each table
   For each pTable in pModel.Tables
      Set pCol = pTable.Columns.CreateNew()
      pCol.SetNameAndCode "az" & pTable.Name, "AZ" & pTable.Code
      pCol.Mandatory = False
   Next
End Sub

Generating a modified database script

Before generating the modified database script, you have to get package option and change generation parameters, then you generate the modified database script accordingly.

For more information about the generation options, see section BasePhysicalPackageOptions in the Metamodel Object Help file.

Example

Sub GenerateAlterScripts(pModel)
   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.DatabaseSynchronizationChoice = 0 'force already saved apm as source
   pOpts.DatabaseSynchronizationArchive = GenDir & "model.apm"
   pOpts.GenerationScriptName = "alter.sql"
pModel.ModifyDatabase ' Launch the Modify Database feature
End Sub

Generating a set of test data

Finally, you generate a set of test data:

Example

Sub GenerateTestDataScript(pModel)
   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 = "Test.sql"
pModel.GenerateTestData ' Launch the Generate Test Data feature
End Sub