Discuss eSurvey DB Tables

[Ghislain Hachey]

I propose to discuss several things to discuss here when possible. Note that I understand why some of the data below was needed but still several open question for the future of pineapple online.

Tables of interest discovered through resolving issues with eSurvey online form not working for RMI mostly all traced from execution of procedure [pEnrolmentRead].[PupilTableRead]

• PupilTables: where is that data coming from?, does not seem needed for current features of pineapple online

• metaSchoolTableItems: this is mostly a bunch of sql queries as data with some of the queries adding yet more dependence on other tables not used in RMI. I simply used the Solomon islands data with care to edit anything country specific. It is yet unclear the adverse effect this would have but some of the data from this table was needed to get the online eSurvey form working.

• metaPupilTableDefs: also necessary for the online eSurvey to work. Taken essentially exactly like the Solomon Islands data.

• metaResourceDefs: also necessary for the online eSurvey to work. Taken essentially exactly like the Solomon Islands data.

• SurveyYearSchoolTypes: I had to add data for this based on country specific SchoolTypes otherwise would not get the data for ages row generation in online pupil survey form. I still am not sure if not including data for columns [ytForm],[ytTeacherForm],[ytConfig],[ytLayout],[ytEForm] will have adverse effects

[Brian Lewis]

PupilTables

is absolutely required. This holds any "table" or "grid" format data returned on a survey, including Repeaters. (Enrolments is the only exception , held in Enrollments.)

So rather than have separate tables for e.g. repeaters, boarders, transfers, etc, they are all in PupilTables with an identifying record type.

This record type is the link into the metadata that describes the nature of the particular table. This is in metaPupilTableDefs.

In metaPupilTableDefs, there are fields defining the domain for the Row values and Column values of the particular table. These are pointers into metaSchoolTableItems. In here, is a SQL statement which returns the code and description values, in order, to use for the dataset. e.g. tables such as Repeaters have a Row value that points to an entry in metaSchoolTableItems that returns the set of ages, and a Column value that returns a set of levels.

The web site editing of these tables depends on this structure, to both load the data and construct the grid, and to save it.

[Brian Lewis]

Survey Year School Types

This defines settings for the school type, in a particular survey year. Yes these records do have to be there for each school year. ytForm and ytTeacherForm are the forms used in the desktop Pineapples to view/edit the survey returns and teacher info for that school type in that year.

ytEForm is the name of the template form used for the eSurvey for the school type/year. This is used in the Generator page of the eSurvey Manager to find the template and prepopulate the surveys.

[Brian Lewis]

metaResourceDefs

Define categories of resources and the rouces in each category. There is various metadata in here about how the data is entere and intepreted, this is used in both the desktop and online version to construct the UI. In particular, it specifies which columns are displayed/enabled in the UI for each category and resources

[Ghislain Hachey]

Cleanup