...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
Background
To be able load data into an instance of the ODS a number of changes need to be made in the Ed-Fi Admin database. These changes include adding an application, vendor, users and api clients (which include keys and secrets) When the ODS is running in sandbox mode, the Sandbox Administration application will handle provisioning a sandbox database including adding an api client. With a production mode install, the creation of keys, secrets, vendors and api clients are generally handled by the Ed-Fi Admin App. The Admin App does not support TPDM education organizations (Universities and Teacher Preparation Providers) which means that it is not possible to create an application or api client for a vendor that needs to load TPDM education organization data. This also holds true for claim sets. The claim set editor within the Admin App only supports claims on Ed-Fi Core resources.
| Info |
|---|
If you are a Local Education Agency or school and are planning on using TPDM entities (Performance Evaluation or Certification for example) 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
The platform team has provided a SQL statement for creating keys, secrets, applications and vendors that doesn't require the Admin App (and thus is perfect for TPDM)
| Info |
|---|
You should replace the values for variables with values that fit your needs. |
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 |
This SQL Statement 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 and 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 the application 'Local Test Application' exists and creates it if not.
- Associates the ClaimSet 'SIS Vendor' with the application 'Local Test Application'
- Verifies that the API Client 'Local Test Api Client' exists or will create it if it does 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 data for more than one education organization, SQL is provided below
Associating additional Namspaces 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
| Info |
|---|
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. |
| Code Block |
|---|
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 load data for multiple ed-orgs. Associating an additional education organization with an api client can be accomplished by running the SQL command below:
| Info |
|---|
You should replace the values for variables with values that fit your needs. This SQL statement does not validate that the api client exists and will fail if the supplied client name is incorrect or does not exist. |
| Code Block |
|---|
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 Claimset for TPDM
The following code will create a claimset 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 claimset. This will add Resource claims to the claimset for all CRUD opperations.
...