How To: Create a Custom Populated Template

The Ed-Fi ODS / API ships with a sample dataset containing approximately 1000 students. This sample is used to automatically populate test and sandbox environments. 

For a variety of reasons, you may want to install a different sample database than the one that ships with ODS / API. You can create your own dataset, for example, to populate sample data with values in extended data fields or load custom data such as education organizations appropriate for your environment, calendars for your schools, and so forth.

This article describes the process to create a custom populated template database, which will enable new sandbox and testing database instances to contain the data you specify.

The different options and steps can be summarized as:

Details of each option and their steps follows.

Option 1. Build from XML Data Using the Create Populated Script

Before you begin:

• This example uses PowerShell scripts and the EdFi.BulkLoadClient.Console found in the Ed-Fi ODS / API. You should follow the Getting Started - Installation Steps before you get started.
• This example uses the XML sample data found in the Ed-Fi-Standard repository on GitHub. You will need to clone or download these sample data files in order to follow along.

Step 1:  Import the Create Populated Template Module

From a PowerShell session, import <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Modules\create-populated-template.psm1.

C:\> Import-Module C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Modules\create-populated-template.psm1

Step 2: Use the Initialize-PopulatedTemplate Script

The next command, Initialize-PopulatedTemplate, which will run through a number of predefined tasks that will be described below:

The Initialize-PopulatedTemplate command has a few parameters to be aware of, summarized in the following table:

Once you understand the parameters above, you're ready to run the Initialize-PopulatedTemplate command. In this example we are using the Ed-Fi-Standard repository as our sample folder. Since this is a common use case the script is set up to handle its particular folder structure. A simpler folder setup with all XML files in a single folder is also supported by the script.

PS C:\> Initialize-PopulatedTemplate -samplePath "C:\Ed-Fi-Standard\v3.2\"

apiClientNameBootstrap         BulkLoadClientBootstrap
apiClientNameSandbox           BulkLoadClientSandbox
apiUrlBase                     http://localhost:8765
apiUrlData                     http://localhost:8765/data/v3
apiUrlMetadata                 http://localhost:8765/metadata
apiUrlOAuth                    http://localhost:8765/oauth
apiYear                        2020
ArtifactSources                {GrandBend, Homograph, Sample}
buildConfiguration             Debug
bulkLoadBootstrapInterchanges  {InterchangeDescriptors, InterchangeStandards, InterchangeEducationOrganization}
...

The script will run through the tasks noted above. The process will take a few minutes to complete.

...

Duration  Task
--------  ----
00:00.04  New-TempDirectory
00:12.60  Invoke-SampleXmlValidation
00:34.71  New-DatabaseTemplate
00:00.09  Invoke-SetTestHarnessConfig
00:04.43  Invoke-RestoreLoadToolsPackages
00:07.79  Invoke-BuildLoadTools
00:00.58  Copy-BootstrapInterchangeFiles
00:00.66  Copy-SampleInterchangeFiles
00:00.40  Add-RandomKeySecret
00:12.43  Invoke-StartTestHarness
01:33.46  Invoke-LoadBootstrapData
08:48.07  Invoke-LoadSampleData
00:00.04  Stop-TestHarness
00:05.65  Backup-DatabaseTemplate
00:00.11  New-DatabaseTemplateNuspec
-         -
11:41.25  Initialize-PopulatedTemplate


PS C:\>

You should see the above task output when the script has finished successfully.

Option 2. Creating a NuGet Package

Before you begin:

• This example assumes you have SQL Server Management Studio (SSMS). You can find instructions for installing SSMS in the Step 3. Install and Configure Required Software step of the Getting Started guide for the Ed-Fi ODS / API.
• This example assumes you have NuGet CLI tool. You can follow the instructions for Installing Nuget Client Tools.
• This example assumes you have access to a MyGet feed. You can follow MyGet's instructions for Getting Started with NuGet.

Step 1: Create a Database Backup File from SQL Server Management Studio (SSMS)

Within SSMS, select the database you would like to back up, select Tasks > Back Up...

In the Back Up Database dialog, select the default backup destination, click Remove, then click Add to create the backup.

In the Select Backup Destination Dialog, select ...

The database must be placed in <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database and the file name must end in .bak in order for the initialize development and deploy scripts to pick it up.

Step 2: Create a Nuspec File

If you used the Initialize-PopulatedTemplate script, the nuspec file has already been created. You can also create a new one by running the New-PopulatedTemplateNuspec command from the create populated template module or by running nuget spec.

The nuspec file created by the New-PopulatedTemplateNuspec script will be created at <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.nuspec.

<?xml version="1.0"?>
<package>
  <metadata>
    <id>$id$</id>
    <version>$version$</version>
    <title>$title$</title>
    <authors>$authors$</authors>
    <owners>$owners$</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>$description$</description>
    <releaseNotes>
    </releaseNotes>
    <copyright>$copyright$</copyright>
    <tags>
    </tags>
  </metadata>
  <files>
    <file src="<source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.bak" target="." />
  </files>
</package>

The contents of the Populated.Template.nuspec file needs to be modified with your information. The default information is shown below as an example of the structure:

<?xml version="1.0"?>
<package>
  <metadata>
    <id>Populated.Template</id>
    <version>1.0.0</version>
	<title>Populated.Template</title>
    <authors>Ed-Fi Alliance</authors>
    <owners>Ed-Fi Alliance</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>EdFi Populated Database Template</description>
    <releaseNotes>
	</releaseNotes>
    <copyright>Copyright ©Ed-Fi Alliance, LLC. 2019</copyright>
  </metadata>
  <files>
    <file src="<source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.bak" target="." />
  </files>
</package>

Once you have your information in your nuspec file, you can navigate to the <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\ folder and execute nuget pack.

PS C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database> nuget pack
Attempting to build package from 'Populated.Template.nuspec'.
Successfully created package 'C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.1.0.0.nupkg'.

When successful, you should see something similar to above. Additional detail:

• For more information about push, take a look at NuGet CLI Pack Command.
• For more information about creating a NuGet packet, see How to create a NuGet package.

Step 3: Upload a Package to MyGet

Now we can nuget push to add the package to the MyGet feed. For this we will need the API Key and the feed URL. You can find these under "Feed Details" when looking at your feed on MyGet.org.

The command should look as follows: nuget push Populated.Template.1.0.0.nupkg <apikey> -source <MyGet Feed URL>

PS C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database> nuget push .\Populated.Template.1.0.0.nupkg <APIKEY> -source https://www.myget.org/F/<FeedName>/api/v2/index.json
Pushing Populated.Template.1.0.0.nupkg to 'https://www.myget.org/F/<FeedName>/api/v2/package'...
  PUT https://www.myget.org/F/<FeedName>/api/v2/package/
  Created https://www.myget.org/F/<FeedName>/api/v2/package/ 19188ms
Your package was pushed.

When successful, you should see something similar to the following:

In your MyGet feed you should see your new package. Additional detail:

• For more information about push, take a look at NuGet CLI Push Command.
• For more information about using NuGet with MyGet, see Getting Started with NuGet.

Option 3. Creating a Custom Populated Template Source Script

Step 1: Create a Script

In this example we will create a new script called NewPopulatedTemplate.ps1 with the contents:

$params = @{
    packageName    = "Populated.Template"
    packageVersion = "1.0.0"
    packageSource  = "https://www.myget.org/F/<FeedName>/"
}
& "$PSScriptRoot\..\Modules\get-populated-from-nuget.ps1" @params

This script must be placed in the folder: <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Scripts\.

In our example, we are reusing the <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Modules\get-populated-from-nuget.ps1 script. This script will download the specified NuGet package name and version from the specified source and place it in the Database folder. Alternatively, we could have written a custom script that would get a .bak file from the web or from a shared drive. The source selection script's only requirement is that it places a .bak file in the folder: <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\. How it accomplishes this is left up to the developer. You can have multiple scripts saved here but only the ones specified in the config files in the next step will be run.

Step 2: Update the Config File

Add the "sandbox:populatedTemplateScript" key to the appSettings section of the file: <source directory>\Ed-Fi-ODS-Implementation\Application\EdFi.Ods.WebApi\Web.Base.config.

  ...
  <appSettings>
    <add key="sandbox:populatedTemplateScript" value="NewPopulatedTemplate.ps1" />
  </appSettings>
  ...

This tells Initialize-DevelopmentEnvironment which source selection script to run. If no script is provided or the name specified does not exist the Initialize-DevelopmentEnvironment will fail when trying to reset the populated template.

If you have an environment (usually staging or QA) that also deploys the populated template you will need to add the same key to the file: <source directory>\Ed-Fi-ODS-Implementation\Scripts\NuGet\EdFi.RestApi.Databases\Databases.config.

Step 3: Run Reset-PopulatedTemplate

In a PowerShell session navigate to <source directory>\Ed-Fi-ODS-Implementation\.

Execute .\Initialize-PowershellForDevelopment.ps1.

PS C:\Ed-Fi-ODS-Implementation\> .\Initialize-PowershellForDevelopment.ps1
Importing Module: InitializeDevelopmentEnvironment.psm1
Using repositories from environment variable: Ed-Fi-Common;Ed-Fi-Ods;Ed-Fi-ODS-Implementation

Execute Reset-PopulatedTemplate.

PS C:\Ed-Fi-ODS-Implementation\> Reset-PopulatedTemplate
Invoking Task: Reset-PopulatedTemplate
Found populated template nuget package: Populated.Template v1.0.0
Successfully added Populated.Template.1.0.0.bak to populated template source folder
...
Dropping the EdFi_Ods_Populated_Template Database.
Using backup C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.1.0.0.bak
...
Task                    TotalMinutes
----                    ------------
Reset-PopulatedTemplate       3.78

When the process successfully completes, you should see something similar to above.