Configuring the ODS to Load Data Into a Non-Sandbox TPDM v1.1 Instance

Owned by Steven Arnold
Last updated: Nov 12, 2021
6 min read

Background

To load data into an instance of the ODS, a number of changes must be made in the Ed-Fi Admin database. These changes include adding an application, vendor, users, and API clients (which have keys and secrets).

When the ODS is running in sandbox mode, the Sandbox Administration application handles provisioning a sandbox database including the addition of an API client. With a production-mode installation, the creation of keys, secrets, vendors, and API clients are generally handled by the Ed-Fi ODS / API Admin App. However, the Admin App is lacking support for TPDM-specific education organizations (including Universities and Teacher Preparation Providers). This means that it is not possible to create an application or API client for a vendor that needs to load TPDM-specific education organization data. The same issue also holds true for claim sets. The claim set editor within the Admin App only supports claims on Ed-Fi Core resources.

If you are a Local Education Agency or school and are planning on using TPDM entities such as Performance Evaluation or Certification, you can still use the Admin App to provision keys and secrets. 

Figure 1. Education Organization Support in Ed-Fi Admin App

Creating Keys, Secrets, Applications, and Vendors for Loading TPDM data

As a workaround for the TPDM-specific issues discussed above, a SQL statement can be run against the ODS database to create create keys, secrets, applications and vendors. The ODS / API platform team has provided a SQL statement suited for TPDM.

You should replace the values for variables with values that fit your needs and environment.

DECLARE @VendorName nvarchar(150) = 'Local Test Vendor'
DECLARE @NamespacePrefix nvarchar (255) = 'uri://ed-fi.org'
DECLARE @UserFullName varchar(150) = 'Local Test User'
DECLARE @UserEmailAddress varchar(150) = 'localtest@ed-fi.org'
DECLARE @ApplicationName nvarchar(255) = 'Local Test Application'
DECLARE @ClaimSetName nvarchar(255) = 'SIS Vendor'
DECLARE @ApiClientName nvarchar(50) = 'Local Test Api Client'
DECLARE @EducationOrganizationId int = 255901 --Must be an ed-org in the ODS
DECLARE @Key nvarchar(50) = 'testkey'
DECLARE @Secret nvarchar(100) = 'testsecret'
 
DECLARE @IsPopulatedSandbox bit = 1
-- For Shared/YearSpecific/DistrictSpecific
DECLARE @UseSandbox bit = 0
-- For Sandbox
--DECLARE @UseSandbox bit = 1
 
DECLARE @VendorId int
DECLARE @UserId int
DECLARE @ApplicationId int
DECLARE @ApplicationEducationOrganizationId int
DECLARE @ApiClientId int
 
-- Clear @IsPopulatedSandbox if not using sandbox
IF (@UseSandbox = 0)
    SET @IsPopulatedSandbox = 0
 
-- Ensure Vendor exists
SELECT @VendorId = VendorId FROM [dbo].[Vendors] WHERE VendorName = @VendorName
 
IF(@VendorId IS NULL)
BEGIN
    INSERT INTO [dbo].[Vendors] (VendorName)
    VALUES (@VendorName)
 
    SET @VendorId = SCOPE_IDENTITY()
END
 
-- Ensure correct namespace prefixes are set up
DELETE FROM [dbo].[VendorNamespacePrefixes] WHERE Vendor_VendorId = @VendorId
INSERT INTO [dbo].[VendorNamespacePrefixes] (Vendor_VendorId, NamespacePrefix)
VALUES (@VendorId, @NamespacePrefix)
 
-- Ensure User exists for test Vendor
SELECT @UserId = UserId FROM [dbo].[Users] WHERE FullName = @UserFullName AND Vendor_VendorId = @VendorId
 
IF(@UserId IS NULL)
BEGIN
    INSERT INTO [dbo].[Users] (Email, FullName, Vendor_VendorId)
    VALUES (@UserEmailAddress, @UserFullName, @VendorId)
 
    SET @UserId = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE [dbo].[Users] SET Email = @UserEmailAddress WHERE UserId = @UserId
END
 
-- Ensure Application exists
SELECT @ApplicationId = ApplicationId FROM [dbo].[Applications] WHERE ApplicationName = @ApplicationName AND Vendor_VendorId = @VendorId
 
IF (@ApplicationId IS NULL)
BEGIN
    INSERT INTO [dbo].[Applications] (ApplicationName, Vendor_VendorId, ClaimSetName)
    VALUES (@ApplicationName, @VendorId, @ClaimSetName)
 
    SET @ApplicationId = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE [dbo].[Applications] SET ClaimSetName = @ClaimSetName WHERE ApplicationId = @ApplicationId
END
 
-- Ensure ApiClient exists
SELECT @ApiClientId = ApiClientId FROM [dbo].[ApiClients] WHERE Application_ApplicationId = @ApplicationId AND [Name] = @ApiClientName
 
IF(@ApiClientId IS NULL)
BEGIN
    INSERT INTO [dbo].[ApiClients] ([Key], [Secret], [Name], IsApproved, UseSandbox, SandboxType, Application_ApplicationId, User_UserId, SecretIsHashed)
    VALUES (@Key, @Secret, @ApiClientName, 1, @UseSandbox, @IsPopulatedSandbox, @ApplicationId, @UserId, 0)
 
    SET @ApiClientId = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE [dbo].[ApiClients] SET [Key] = @Key, [Secret] = @Secret, UseSandbox = @UseSandbox, SandboxType = @IsPopulatedSandbox, User_UserId = @UserId, SecretIsHashed = 0 WHERE ApiClientId = @ApiClientId
END
 
IF (@EducationOrganizationId IS NOT NULL)
BEGIN
    -- Clear all education organization links for the selected application
    DELETE acaeo
    FROM dbo.ApiClientApplicationEducationOrganizations acaeo
    INNER JOIN dbo.ApplicationEducationOrganizations aeo
    ON acaeo.ApplicationEducationOrganization_ApplicationEducationOrganizationId = aeo.ApplicationEducationOrganizationId
    WHERE aeo.Application_ApplicationId = @ApplicationId
    DELETE FROM [dbo].[ApplicationEducationOrganizations] WHERE Application_ApplicationId = @ApplicationId
 
    -- Ensure correct education organizations are set up
    INSERT INTO [dbo].[ApplicationEducationOrganizations] (EducationOrganizationId, Application_ApplicationId)
    VALUES (@EducationOrganizationId, @ApplicationId)
    SELECT @ApplicationEducationOrganizationId = SCOPE_IDENTITY()
 
    INSERT INTO [dbo].[ApiClientApplicationEducationOrganizations] (ApplicationEducationOrganization_ApplicationEducationOrganizationId, ApiClient_ApiClientId)
    VALUES (@ApplicationEducationOrganizationId, @ApiClientId)
END

The SQL Statement above will:

  • Verify that the vendor "Local Test Vendor" exists and will create a vendor if it doesn't exist.
  • Add "uri://ed-fi.org" to the list of namespaces the vendor can load. If more than one prefix is needed for a given vendor, the SQL provided below will add another prefix for the vendor.
  • Verify that the user "Local Test User" exists and is associated with the vendor or will create the user and associate it with the vendor if it exists
  • Verify that the application "Local Test Application" exists, and create it if not.
  • Associates the claim set "SIS Vendor" with the application "Local Test Application".
  • Verifies that the API client "Local Test Api Client" exists, and will create it if not.
  • Associates the key "testkey", secret "testsecret", and user "Local Test User" to the API client "Local Test Api Client".
  • Associates the provided EducationOrganization "255901" with the API Client "Local Test Api Client". If the API client needs to honor data for more than one education organization, SQL is provided below.

Associating Additional Namespaces With a Vendor

There may be times that the vendor loading data will use multiple namespaces. Associating an additional namespace with a vendor can be accomplished by running the SQL command below

You should replace the values for variables with values that fit your needs. This SQL statement does not validate that the vendor exists and will fail if the supplied vendor name is incorrect or does not exist.

DECLARE @VendorName nvarchar(150) = 'Local Test Vendor'
DECLARE @NamespacePrefix nvarchar (255) = 'uri://ed-fi.org'
DECLARE @VendorId int
SELECT @VendorId = VendorId FROM [dbo].[Vendors] WHERE VendorName = @VendorName
INSERT INTO [dbo].[VendorNamespacePrefixes] (Vendor_VendorId, NamespacePrefix)
VALUES (@VendorId, @NamespacePrefix)

Associating Additional Education Organizations With an API Client 

There may be times that an API client will need to load data for multiple education organizations. Associating an additional education organization with an API client can be accomplished by running the SQL command below:

You should replace the values for variables with values that fit your needs and environment. This SQL statement does not validate that the API client exists, so it will fail if the supplied client name is incorrect or does not exist.

DECLARE @ApiClientName nvarchar(50) = 'Local Test Api Client'
DECLARE @EducationOrganizationId int = 255901
DECLARE @ApplicationEducationOrganizationId int
DECLARE @ApiClientId int
DECLARE @ApplicationId int

SELECT @ApiClientId = ApiClientId FROM [dbo].[ApiClients] WHERE Application_ApplicationId = @ApplicationId AND [Name] = @ApiClientName

INSERT INTO [dbo].[ApplicationEducationOrganizations] (EducationOrganizationId, Application_ApplicationId)
    VALUES (@EducationOrganizationId, @ApplicationId)
    SELECT @ApplicationEducationOrganizationId = SCOPE_IDENTITY()
 
    INSERT INTO [dbo].[ApiClientApplicationEducationOrganizations] (ApplicationEducationOrganization_ApplicationEducationOrganizationId, ApiClient_ApiClientId)
    VALUES (@ApplicationEducationOrganizationId, @ApiClientId)

Creating a Claim Set for TPDM

The following SQL Statement will create a claim set through SQL for TPDM. Replace the ClaimSetName value with the name of the new claimset and adjust the values on the line 'WHERE ResourceName in ...' to the resources that are in the claim set. This will add Resource claims to the claim set for all CRUD operations.

---- SQL Server ----
DECLARE @ApplicationId INT;
DECLARE @ClaimSetId INT;
DECLARE @ResourceName VARCHAR(2048);
DECLARE @ClaimSetName VARCHAR(255);

SET @ClaimSetName = 'YOUR CLAIMSET NAME HERE';

SELECT @ApplicationId = ApplicationId 
    FROM [dbo].[Applications] WHERE ApplicationName = 'Ed-Fi ODS API';

INSERT INTO [dbo].[ClaimSets] (ClaimSetName, Application_ApplicationId)
    VALUES (@ClaimSetName, @ApplicationId);

SELECT @ClaimSetId = (SELECT ClaimSetId 
    FROM [dbo].[ClaimSets] WHERE ClaimSetName = @ClaimSetName);

INSERT INTO [dbo].[ClaimSetResourceClaims]
    ([Action_ActionId]
    ,[ClaimSet_ClaimSetId]
    ,[ResourceClaim_ResourceClaimId]
    ,[AuthorizationStrategyOverride_AuthorizationStrategyId]
    ,[ValidationRuleSetNameOverride])
SELECT ac.ActionId, @ClaimSetId, ResourceClaimId, null, null 
    FROM [dbo].[ResourceClaims]
    CROSS APPLY 
        (SELECT ActionId 
        FROM [dbo].[Actions] 
        WHERE ActionName IN ('Create','Read','Update','Delete')) AS ac
    WHERE ResourceName IN ('teacherCandidate', 'evaluationRating'); -- Replace teacherCandidate and evaluationRating with appropriate resource claims

---- PostgreSQL ----
do $$
declare
   	v_applicationId integer; 
   	v_claimSetId integer;
	v_resourceName VARCHAR(2048);
	v_claimSetName VARCHAR(255);
begin
	v_claimSetName = 'Test TPDM Claimset'; -- Replace this with the new Claimset name
	SELECT ApplicationId INTO v_applicationId 
	FROM dbo.Applications WHERE ApplicationName = 'Ed-Fi ODS API';
	INSERT INTO dbo.ClaimSets (ClaimSetName, Application_ApplicationId)
	VALUES (v_claimSetName, v_applicationId);
	SELECT ClaimSetId INTO v_claimSetId
	FROM dbo.ClaimSets 
	WHERE ClaimSetName = v_claimSetName;
	INSERT INTO dbo.ClaimSetResourceClaims
    (Action_ActionId
   ,ClaimSet_ClaimSetId
   ,ResourceClaim_ResourceClaimId
    ,AuthorizationStrategyOverride_AuthorizationStrategyId
    ,ValidationRuleSetNameOverride)
	SELECT ac.ActionId, v_claimSetId, ResourceClaimId, null, null 
	FROM dbo.ResourceClaims
	CROSS JOIN LATERAL (SELECT ActionId
					   FROM dbo.Actions
					   WHERE ActionName in ('Create', 'Read', 'Update', 'Delete')) as ac
   	WHERE ResourceName IN ('teacherCandidate', 'evaluationRating'); -- Replace teacherCandidate and evaluationRating with the resources for Claimset
end; 
$$