Requirements
These were collected from a meeting we held with Larsson, Abhi, Mike, Brian, and Thaneer, and from a follow-up interview with Dan.
...
CREATE TABLE `SubStudies` (
`identifier` `studyId` VARCHAR(60) NOT NULL,
`studyId` `id` VARCHAR(6015) NOT NULL,
`label` VARCHAR(255) NULL,
`createdOn` BIGINT NOT NULL,
`modifiedOn` BIGINT NOT NULL,
`deleted` BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (`studyid``studyIid`, `identifier``id`)
)
SubStudiesService {
listSubStudies(studyId, includeDeleted)
createSubStudy(subStudy)
getStubStudy(studyId, id)
updateSubStudy(subStudy)
deleteStubStudy(studyId, id)
deleteStubStudyPermanently(studyId, id)
// These would add an associate record between Account and SubStudy without assigning an external ID
addUserToSubStudy(studyId, id, participant)
removeUserFromSubStudy(studyId, id, userId)
}
...
GET /v3/substudies?includeDeleted=boolean [list]
POST /v3/substudies [create]
GET /v3/substudies/:id [read]
POST /v3/substudies/:id [update]
DELETE /v3/substudies/:id?physical=boolen [delete]
Create new ExternalIds// Either or both of accountId and externalId need to be filled out. Would probably use ID column to avoid composite keys, but then would need to verify the results conform to the caller's study and sub-study.
CREATE TABLE `ExternalIds` {
`id` `studyId` VARCHAR(60) NOT NULL,
`studyId` `id` VARCHAR(60) NOT NULL,
`subStudyId` VARCHAR(6015) NOT NULL,
`accountId` VARCHAR(255) NULL,
`externalId` VARCHAR(255) NULL,
PRIMARY KEY (`studyId`,`id`) // ID must be unique in a study so we can unambiguously determine the sub-study membership
UNIQUE INDEX `StudyId-SubStudyId-Index` (`studyId` ASC, `subStudyId` `id` ASC), / / will return more than oneto retrieve all external IDs in a sub-study
INDEX `StudyId`AccountId-AccountId-Index` (`studyId` ASC, `accountId` ASC) // could return more than oneto retrieve external IDs for an account
UNIQUE INDEX `StudyId'StudyId-SubStudyId-ExternalId-Index` Index (`studyId` ASC,`subStudyId` ASC, `id` ASC) // for security, `externalId` ASC)we will include the substudy in the query, though external ID is unique to study
CHECK `AccountId-Or-ExternalId-Required` (`externalId` IS NOT NULL OR `accountId` IS NOT NULL)
}
// An outstanding issue is dealing with batch adds... we should probably support that, but not batch deletes.
ExternalIdsServiceV2 {
listExternalIds(studyId, subStudyId, offsetBy, pageSize, includeDeleted)
createExternalId(externalIdObj)
getExternalId(studyId, subStudyId, externalId)
updateExternalId(externalIdObj)
deleteExternalId(studyId, subStudyId, externalId)
deleteExternalIdPermanently(studyId, subStudyId, externalId)
// These would update an add an associate record between Account and SubStudy with an account ID
assignExternalId(studyId, subStudyId, externalId, accountId)
unassignExternalId(studyId, subStudyId, externalId)
}
...