def insert_lms_user_deleted(
connection: Connection, sis_identifier: str, email_address: str, source_system: str
):
connection.execute(
f"""
INSERT INTO [lms].[LMSUser]
([SourceSystemIdentifier]
,[SourceSystem]
,[UserRole]
,[SISUserIdentifier]
,[LocalUserIdentifier]
,[Name]
,[EmailAddress]
,[SourceCreateDate]
,[SourceLastModifiedDate]
,[CreateDate]
,[LastModifiedDate]
,[DeletedAt])
VALUES
(N'{sis_identifier}'
,N'{source_system}'
,N'{USER_ROLE}'
,N'{sis_identifier}1'
,N'{sis_identifier}2'
,N'{sis_identifier}3'
,N'{email_address}'
,NULL
,NULL
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-02 00:00:00' AS DateTime)
)
"""
)
def insert_edfi_section_association(
connection: Connection,
section_identifier: str,
student_id: str):
connection.execute(
f"""
insert into edfi.StudentSectionAssociation (
BeginDate,
LocalCourseCode,
SchoolId,
SchoolYear,
SectionIdentifier,
SessionName,
StudentUSI)
select top 1
GETDATE() BeginDate,
localcoursecode,
SchoolId,
schoolyear,
sectionidentifier,
sessionname,
(select top 1 studentUSI from edfi.student where StudentUniqueId = N'{student_id}') as StudentUSI
from edfi.section
WHERE SectionIdentifier = N'{section_identifier}'
""")
def insert_edfi_student_electronic_mail(
connection: Connection,
student_usi: int,
email_address: str,
):
connection.execute(
f"""
INSERT INTO [edfi].[StudentEducationOrganizationAssociationElectronicMail]
([EducationOrganizationId]
,[ElectronicMailTypeDescriptorId]
,[StudentUSI]
,[ElectronicMailAddress]
,[PrimaryEmailAddressIndicator]
,[DoNotPublishIndicator]
,[CreateDate])
VALUES
(1
,1
,{student_usi}
,N'{email_address}'
,NULL
,NULL
,CAST(N'2021-01-01 00:00:00' AS DateTime)
)
"""
)
def insert_edfi_section_association(connection: Connection,
section_identifier: str, student_id: str):
connection.execute(f"""
insert into edfi.studentsectionassociation (
id,
begindate,
localcoursecode,
schoolid,
schoolyear,
sectionidentifier,
sessionname,
studentusi)
select
(select md5(random()::text || random()::text)::uuid),
now() begindate,
localcoursecode,
schoolid,
schoolyear,
sectionidentifier,
sessionname,
(select studentusi from edfi.student where studentuniqueid = '{student_id}' limit 1) as studentusi
from edfi.section
where sectionidentifier = '{section_identifier}'
limit 1
""")
def insert_lms_section_deleted(connection: Connection, sis_identifier: str,
source_system: str):
connection.execute(f"""
insert into lms.lmssection
(sourcesystemidentifier,
sourcesystem,
sissectionidentifier,
title,
sourcecreatedate,
sourcelastmodifieddate,
createdate,
lastmodifieddate,
deletedat)
values
('{sis_identifier}'
,'{source_system}'
,'{sis_identifier}'
,'test section deleted'
,now()
,now()
,now()
,now()
,now()
)
""")
def insert_lmsx_assignmentsubmissionstatus_descriptor(connection: Connection,
id: int):
connection.execute(f"""
insert into lmsx.submissionstatusdescriptor
(submissionstatusdescriptorid)
values ( {str(id)} )
""")
def insert_lms_section_deleted(
connection: Connection, sis_identifier: str, source_system: str
):
connection.execute(
f"""
INSERT INTO [lms].[LMSSection]
(SourceSystemIdentifier,
SourceSystem,
SISSectionIdentifier,
Title,
SourceCreateDate,
SourceLastModifiedDate,
CreateDate,
LastModifiedDate,
[DeletedAt])
VALUES
(N'{sis_identifier}'
,N'{source_system}'
,N'{sis_identifier}'
,N'test section deleted'
,GETDATE()
,GETDATE()
,GETDATE()
,GETDATE()
,GETDATE()
)
"""
)
def insert_lms_section(connection: Connection, sis_identifier: str, source_system: str):
connection.execute(
f"""
INSERT INTO [lms].[LMSSection]
(
SourceSystemIdentifier,
SourceSystem,
SISSectionIdentifier,
Title,
SourceCreateDate,
SourceLastModifiedDate,
CreateDate,
LastModifiedDate)
VALUES
(N'{sis_identifier}'
,N'{source_system}'
,N'{sis_identifier}'
,N'section title'
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
)
"""
)
def insert_lms_user_deleted(connection: Connection, sis_identifier: str,
email_address: str, source_system: str):
connection.execute(f"""
insert into lms.lmsuser
(sourcesystemidentifier
,sourcesystem
,userrole
,sisuseridentifier
,localuseridentifier
,name
,emailaddress
,sourcecreatedate
,sourcelastmodifieddate
,createdate
,lastmodifieddate
,deletedat)
values
('{sis_identifier}'
,'{source_system}'
,'{USER_ROLE}'
,'{sis_identifier}1'
,'{sis_identifier}2'
,'{sis_identifier}3'
,'{email_address}'
,NULL
,NULL
,'2021-01-01 00:00:00'
,'2021-01-01 00:00:00'
,'2021-01-02 00:00:00'
)
""")
def insert_lmsx_assignmentcategory_descriptor(connection: Connection, id: int):
connection.execute(
f"""
INSERT INTO [lmsx].[AssignmentCategoryDescriptor]
(AssignmentCategoryDescriptorId)
VALUES ( {str(id)} )
"""
)
def insert_lmsx_sourcesystem_descriptor(connection: Connection, id: int):
connection.execute(
f"""
INSERT INTO [lmsx].[LMSSourceSystemDescriptor]
(LMSSourceSystemDescriptorId)
VALUES ( {str(id)} )
"""
)
def insert_lmsx_assignmentsubmissionstatus_descriptor(connection: Connection, id: int):
connection.execute(
f"""
INSERT INTO [lmsx].[SubmissionStatusDescriptor]
(SubmissionStatusDescriptorId)
VALUES ( {str(id)} )
"""
)
def insert_edfi_student_with_usi(
connection: Connection,
student_usi: int,
id: str = "00000000-0000-0000-0000-000000000000",
):
connection.execute(
f"""
SET IDENTITY_INSERT edfi.Student ON;
INSERT INTO [edfi].[Student]
([StudentUSI]
,[PersonalTitlePrefix]
,[FirstName]
,[MiddleName]
,[LastSurname]
,[GenerationCodeSuffix]
,[MaidenName]
,[BirthDate]
,[BirthCity]
,[BirthStateAbbreviationDescriptorId]
,[BirthInternationalProvince]
,[BirthCountryDescriptorId]
,[DateEnteredUS]
,[MultipleBirthStatus]
,[BirthSexDescriptorId]
,[CitizenshipStatusDescriptorId]
,[StudentUniqueId]
,[CreateDate]
,[LastModifiedDate]
,[Id])
VALUES
({student_usi}
,NULL
,N'FirstName'
,NULL
,N'LastName'
,NULL
,NULL
,CAST(N'2010-01-01 00:00:00' AS DateTime)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,N'{student_usi}{student_usi}'
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST('{id}' AS UNIQUEIDENTIFIER)
);
SET IDENTITY_INSERT edfi.Student OFF;
"""
)
def insert_lms_assignment_submissions(
connection: Connection,
lms_assignmen_identifier: int,
source_system_identifier: str,
lms_assignment_id: int,
lms_user_identifier: int,
submission_status: str,
source_system: str = "Test_LMS",
isDeleted: bool = False,
):
# it is not necessary to have a different title and description since
# both should be updated when required
connection.execute(
f"""
SET IDENTITY_INSERT lms.AssignmentSubmission ON;
INSERT INTO [lms].[AssignmentSubmission]
(
[AssignmentSubmissionIdentifier]
,[SourceSystemIdentifier]
,[SourceSystem]
,[AssignmentIdentifier]
,[LMSUserIdentifier]
,[SubmissionStatus]
,[SubmissionDateTime]
,[EarnedPoints]
,[Grade]
,[SourceCreateDate]
,[SourceLastModifiedDate]
,[CreateDate]
,[LastModifiedDate]
,[DeletedAt]
)
VALUES
(
{lms_assignmen_identifier},
N'{source_system_identifier}',
N'{source_system}',
{lms_assignment_id},
{lms_user_identifier},
N'{submission_status}',
GETDATE(),
0,
N'{GRADE}',
GETDATE(),
GETDATE(),
GETDATE(),
GETDATE(),
{'GETDATE()' if isDeleted else 'NULL'}
);
SET IDENTITY_INSERT lms.AssignmentSubmission OFF;
"""
)
def insert_edfi_student_with_usi(
connection: Connection,
student_usi: int,
id: str = "00000000-0000-0000-0000-000000000000",
):
connection.execute(f"""
insert into edfi.student
(studentusi
,personaltitleprefix
,firstname
,middlename
,lastsurname
,generationcodesuffix
,maidenname
,birthdate
,birthcity
,birthstateabbreviationdescriptorid
,birthinternationalprovince
,birthcountrydescriptorid
,dateenteredus
,multiplebirthstatus
,birthsexdescriptorid
,citizenshipstatusdescriptorid
,studentuniqueid
,createdate
,lastmodifieddate
,id)
overriding system value
values
({student_usi}
,NULL
,'FirstName'
,NULL
,'LastName'
,NULL
,NULL
,'2010-01-01 00:00:00'
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,'{student_usi}{student_usi}'
,'2021-01-01 00:00:00'
,'2021-01-01 00:00:00'
,'{id}'
);
""")
def insert_lms_assignment_submissions(
connection: Connection,
lms_assignment_identifier: int,
source_system_identifier: str,
lms_assignment_id: int,
lms_user_identifier: int,
submission_status: str,
source_system: str = "Test_LMS",
isDeleted: bool = False,
):
# it is not necessary to have a different title and description since
# both should be updated when required
connection.execute(f"""
insert into lms.assignmentsubmission
(
assignmentsubmissionidentifier
,sourcesystemidentifier
,sourcesystem
,assignmentidentifier
,lmsuseridentifier
,submissionstatus
,submissiondatetime
,earnedpoints
,grade
,sourcecreatedate
,sourcelastmodifieddate
,createdate
,lastmodifieddate
,deletedat
)
overriding system value
values
(
{lms_assignment_identifier},
'{source_system_identifier}',
'{source_system}',
{lms_assignment_id},
{lms_user_identifier},
'{submission_status}',
now(),
0,
'{GRADE}',
now(),
now(),
now(),
now(),
{'now()' if isDeleted else 'NULL'}
);
""")
def insert_descriptor(connection: Connection, namespace: str, value: str):
connection.execute(f"""
insert into edfi.descriptor
(
namespace,
codevalue,
shortdescription,
description,
id)
values
(
'{namespace}',
'{value}',
'{value}',
'{value}',
(select md5(random()::text || random()::text)::uuid)
)
""")
def insert_descriptor(connection: Connection, namespace: str, value: str):
connection.execute(
f"""
INSERT INTO [edfi].[Descriptor]
(
[Namespace],
[CodeValue],
[ShortDescription],
[Description])
VALUES
(
N'{namespace}',
N'{value}',
N'{value}',
N'{value}'
)
"""
)
def insert_lms_assignment(
connection: Connection,
source_system_identifier: str,
source_system: str,
section_identifier: int,
assignment_category: str,
title_and_description: str = "default title and description",
past_due_date: bool = False
) -> int:
# it is not necessary to have a different title and description since
# both should be updated when required
connection.execute(
f"""
INSERT INTO [lms].[Assignment]
(
SourceSystemIdentifier,
SourceSystem,
LMSSectionIdentifier,
Title,
AssignmentCategory,
AssignmentDescription,
CreateDate,
LastModifiedDate
{ ",DueDateTime" if past_due_date else "" }
)
VALUES (
N'{source_system_identifier}',
N'{source_system}',
{section_identifier},
N'{title_and_description}',
N'{assignment_category}',
N'{title_and_description}',
GETDATE(),
GETDATE()
{ ",DATEADD(year, -1, GETDATE())" if past_due_date else "" }
)
"""
)
result: Cursor = connection.execute("SELECT @@identity")
return int(result.fetchone()[0])
def set_up_db(con: pyodbc.Connection, /) -> None:
print("Setting up db...")
fp = pathlib.Path(__file__).parent / "fixtures" / "setup_db.sql"
run_queries_in_file(con=con, fp=fp)
con.execute("ANALYZE hr.employee;")
con.execute("ANALYZE sales.customer;")
con.execute("ANALYZE sales.employee_customer;")
def insert_lmsx_assignment(
connection: Connection,
assignment_id: int,
assignment_identifier: str,
source_system_descriptor_id: int,
assignment_category_descriptor_id: int,
section_identifier: str,
title_and_description: str = "default title and description",
):
# it is not necessary to have a different title and description since
# both should be updated when required
connection.execute(
f"""
INSERT INTO [lmsx].[Assignment]
(
AssignmentIdentifier,
Namespace,
LMSSourceSystemDescriptorId,
Title,
AssignmentCategoryDescriptorId,
AssignmentDescription,
SectionIdentifier,
LocalCourseCode,
SessionName,
SchoolYear
)
VALUES (
N'{assignment_identifier}',
N'Namespace',
{source_system_descriptor_id},
N'{title_and_description}',
{assignment_category_descriptor_id},
N'{title_and_description}',
N'{section_identifier}',
N'Local course code test',
N'session name test',
{SCHOOL_YEAR}
)
"""
)
def insert_edfi_section(connection: Connection, sis_id: str, uid: str = None):
connection.execute(f"""
insert into edfi.section
(
localcoursecode,
schoolid,
schoolyear,
sessionname,
lastmodifieddate,
sectionidentifier
,id)
values
(
'{COURSE_CODE}'
,{SCHOOL_ID}
,{SCHOOL_YEAR}
,'{SESSION_NAME}'
,'2021-01-01 00:00:00'
,'{sis_id}'
{f",'{uid}'::uuid" if uid is not None else ",(select md5(random()::text || random()::text)::uuid)"}
)
""")
def insert_lms_assignment(
connection: Connection,
source_system_identifier: str,
source_system: str,
section_identifier: int,
assignment_category: str,
title_and_description: str = "default title and description",
past_due_date: bool = False) -> int:
# it is not necessary to have a different title and description since
# both should be updated when required
connection.execute(f"""
insert into lms.assignment
(
sourcesystemidentifier,
sourcesystem,
lmssectionidentifier,
title,
assignmentcategory,
assignmentdescription,
createdate,
lastmodifieddate
{ ",duedatetime" if past_due_date else "" }
)
values (
'{source_system_identifier}',
'{source_system}',
{section_identifier},
'{title_and_description}',
'{assignment_category}',
'{title_and_description}',
now(),
now()
{ ",now() - interval '1 year'" if past_due_date else "" }
)
""")
result: Cursor = connection.execute("select lastval()")
return int(result.fetchone()[0])
def insert_lmsx_assignment(
connection: Connection,
assignment_id: int,
assignment_identifier: str,
source_system_descriptor_id: int,
assignment_category_descriptor_id: int,
section_identifier: str,
title_and_description: str = "default title and description",
):
# it is not necessary to have a different title and description since
# both should be updated when required
connection.execute(f"""
insert into lmsx.assignment
(
assignmentidentifier,
namespace,
lmssourcesystemdescriptorid,
title,
assignmentcategorydescriptorid,
assignmentdescription,
sectionidentifier,
localcoursecode,
sessionname,
schoolyear
)
values (
'{assignment_identifier}',
'Namespace',
{source_system_descriptor_id},
'{title_and_description}',
{assignment_category_descriptor_id},
'{title_and_description}',
'{section_identifier}',
'Local course code test',
'session name test',
{SCHOOL_YEAR}
)
""")
def _getFinalCursor(cls, con: pyodbc.Connection, cmd: str,
parameters: tuple) -> pyodbc.Cursor:
"""
Gets out cursor to be looped over. If the query errors out, we'll return a mock cursor that
returns an empty list as an iterator, and always returns False for nextset.
:param con: Connection String
:param cmd: stored procedure we want to execute
:param parameters: parameters for said stored procedure
:return: result set cursor
"""
query = cls.buildSPQuery(cmd, cls.getParameterCount(parameters))
try:
if "?" not in query:
crsr = con.execute(query)
else:
crsr = con.execute(query, parameters)
except pyodbc.Error as e:
print(e)
return MockCursor()
return crsr
def insert_edfi_section(connection: Connection, sis_id: str, uid: str = None):
connection.execute(
f"""
INSERT INTO [edfi].[Section]
(
[LocalCourseCode],
[SchoolId],
[SchoolYear],
[SessionName],
[LastModifiedDate],
[SectionIdentifier]
{',[id]' if uid is not None else ''})
VALUES
(
N'{COURSE_CODE}'
,{SCHOOL_ID}
,{SCHOOL_YEAR}
,N'{SESSION_NAME}'
,CAST(N'2021-01-01 00:00:00' AS DateTime2(7))
,N'{sis_id}'
{f",CAST(N'{uid}' AS UNIQUEIDENTIFIER)" if uid is not None else ''}
)
"""
)
def insert_lms_section(connection: Connection, sis_identifier: str,
source_system: str):
connection.execute(f"""
insert into lms.lmssection
(
sourcesystemidentifier,
sourcesystem,
sissectionidentifier,
title,
sourcecreatedate,
sourcelastmodifieddate,
createdate,
lastmodifieddate)
values
('{sis_identifier}'
,'{source_system}'
,'{sis_identifier}'
,'section title'
,'2021-01-01 00:00:00'
,'2021-01-01 00:00:00'
,'2021-01-01 00:00:00'
,'2021-01-01 00:00:00'
)
""")
def insert_edfi_student_electronic_mail(
connection: Connection,
student_usi: int,
email_address: str,
):
connection.execute(f"""
insert into edfi.studenteducationorganizationassociationelectronicmail\
(educationorganizationid
,electronicmailtypedescriptorid
,studentusi
,electronicmailaddress
,primaryemailaddressindicator
,donotpublishindicator
,createdate)
values
(1
,1
,{student_usi}
,'{email_address}'
,NULL
,NULL
,'2021-01-01 00:00:00'
)
""")
def insert_lmsx_assignmentcategory_descriptor(connection: Connection, id: int):
connection.execute(f"""
insert into lmsx.assignmentcategorydescriptor
(assignmentcategorydescriptorid)
values ( {str(id)} )
""")
def insert_lmsx_sourcesystem_descriptor(connection: Connection, id: int):
connection.execute(f"""
insert into lmsx.lmssourcesystemdescriptor
(lmssourcesystemdescriptorid)
values ( {str(id)} )
""")