~[wc:commonscripts]
~[wc:admin_header_css] ~[text:psx.html.admin_reports.runreports.start_page] > PowerSchool Customization ~[wc:admin_navigation_css] ~[SetPostValue:tab=plugin]

# PowerSchool Customization

~[x:insertfile;tabs.html]

## What is a Plugin?

A PowerSchool plugin is a convenient way for PowerSchool users to package and share custom content including the following

- Custom Pages and Page Fragments
- Database Extended Table/Field Definitions
- PowerQueries

## Plugin Management

Plugins are managed via the Plugin Management Dashboard (System > System Settings > Plugin Management Configuration)

## Anatomy of a Plugin

| A plugin is simply a zipped folder containing plugin.xml file and up to four optional folders permissions_root - Definitions of access permission to PowerQueries queries_root - PowerQuery files user_schema_root - Database extension definitions web_root - Custom pages and page fragments

## plugin.xml File

Below is a basic template for a plugin.xml file. There are more contents that the file may contain,
and you may see additional elements in some third-party plugins, but the contents of this file are
all that is needed for a basic plugin containing custom pages, schema extensions, and/or PowerQueries.

<?xml version="1.0" encoding="UTF-8"?> <plugin xmlns="http://plugin.powerschool.pearson.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" description="Describe the function of the plugin" name="Name of the plugin" version="Your version (e.g. 1.0)" xsi:schemaLocation="http://plugin.powerschool.pearson.com plugin.xsd"> <publisher name="Publisher Name"> <contact email="youremail@email.com"/> </publisher> </plugin>

## web_root

The web_root folder will contain all custom pages to be included with the plugin.
This folder must contain the appropriate sub-folders to ensure that files are place in the appropriate locations.
After installation and activation of the plugin, all custom files will be uploaded to the corresponding locations in Custom Page Management.

## user_schema_root

The user_schema_root folder contains xml files which define database extensions.
These files should have the following naming format: U_[extensionname]
Below is a sample xml file for creating one-to-one extended fields.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <psExtension xmlns="http://www.powerschool.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.powerschool.com psextension.xsd"> <extensionname>U_Your_Extension_Name</extensionname> <extendedTable coreTable="coreTableNaem" dbTableName="U_Your_Table_Name" comment="Enter Comments"> <field name="field_name" type="String" length="100" /> <field name="field_name" type="Boolean" /> <field name="field_name" type="Date" /> <field name="field_name" type="Integer" /> <field name="field_name" type="Double" /> </extendedTable> </psExtension>

## queries_root

The queries_root folder contains xml file(s) which define PowerQueries.
The PowerQuery filename must follow this format: [name].named_queries.xml
The example below includes a simple query which includes student, course, and grade information.

<queries> <query name="com.mba.psugDemo.studentGrades.storedGrades" flattened="true"> <summary>Student Stored Grades PQ</summary> <description>Student Stored Grades</description> <columns> <column column="students.lastfirst" description="Student Name">students.fullname</column> <column column="students.student_number" description="Student Number">students.student_number</column> <column column="courses.course_name" description="Course">courses.course_name</column> <column column="storedgrades.grade" description="Grade">storedgrades.grade</column> <column column="storedgrades.storecode" description="Storecode">storedgrades.storecode</column> <column column="storedgrades.termid" description="Termid">storedgrades.termid</column> </columns> <sql> <![CDATA[ SELECT students.lastfirst, students.student_number, courses.course_name, storedgrades.grade, storedgrades.storecode, storedgrades.termid FROM storedgrades JOIN students ON students.id = storedgrades.studentid JOIN courses ON courses.course_number = storedgrades.course_number ]]> </sql> </query> </queries>

<query name="com.mba.psugDemo.studentGrades.storedGrades" flattened="true">

The query name must have five parts: com.[organization].[product_name].[area].[name]

<description> - provides a description of the PowerQuery

<summary> - provides a description of the PowerQuery as well, this is how the PowerQuery will be labeled in Data Export Manager

<columns> <column column="students.lastfirst" description="Student Name">student.fullname</column> <columns>

column="students.lastfirst" : tablename.fieldname
description="Student Name" : Not required but highly recommended
student.fullname : An alias for the column

<sql> <![CDATA[ Put SQL Here ]]> </sql>

* Note - A single file can contain multiple <queries></queries> tags; each with a different PowerQuery.

Working with current student selection
Add the following WHERE clause to a students table query to enable the ability to limit results to students in the current selection
WHERE <@restricted_table table="students" selector="selectedstudents"/>

WITH studentSelection AS( SELECT dcid, lastfirst, ... FROM students WHERE <@restricted_table table="students" selector="selectedstudents"/> ) SELECT .... FROM studentSelection JOIN ...

With the above query, a dofor parameter can be passed to the query to restrict to currently selected students
/ws/schema/query/[queryname]?pagesize=0&dofor=selection:selectedstudents
If no dofor parameter is passed to the query, it will run for all students

## permissions_root

The permissions_root folder contains xml file(s) which define access permissions to PowerQueries.
These files should have the following naming format: [name].permission_mappings.xml
The example below would provide access to the PowerQuery example above
to any user who has access to the page admin/tech/home.html

<permission_mappings> <permission name='/admin/tech/home.html'> <implies allow="post">/ws/schema/query/com.mba.psugDemo.studentGrades.storedGrades</implies> </permission> </permission_mappings>

### Portal Permissions

Portal | Permission
--- | ---
Admin | /admin/somepage.html
Teacher | /teachers/somepage.html
Guardian | /guardian/somepage.html
Student | /student/somepage.html

Note: - All public portal pages are maintained in the guardian folder. The student permission will not represent an actual page in the web root.

## Accessing a PowerQuery Internally

What follows is an example of accessing a PowerQuery from a PowerSchool page. The code has been displayed below (XML, JavaScript, and HTML).
For a deeper look at the structure and code, simply look at the contents of the MBA Customization Reference Plugin which contains the PowerQuery as well as all necessary code on this page (admin/district/customizationDocs/plugin.html) to generate the table at the end of this section.

### PowerQuery Code

<queries> <query name="com.mba.psugDemo.schoolData.enrollment" flattened="true"> <summary>School Enrollment Totals PQ</summary> <description>School Enrollment Totals</description> <args> <arg name="enrollStatus" column="students.enroll_status" required="false" description="student enroll status" default="0" /> </args> <columns> <column column="schools.name" description="School Name">schools.name</column> <column column="schools.school_number" description="School Number">schools.school_number</column> <column column="schools.abbreviation" description="School Abbreviation">schools.abbreviation</column> <column column="schools.principal" description="School Principal">schools.principal</column> <column column="schools.principal" description="School Enrollment">schools.enrollment</column> </columns> <sql> <![CDATA[ SELECT name, school_number, abbreviation, principal, ( SELECT count(id) FROM students WHERE schoolid = schools.school_number AND enroll_status = :enrollStatus ) AS enrollment FROM schools ORDER BY sortorder ]]> </sql> </query> </queries>

Notice the following lines in the above code:

<args> <arg name="enrollStatus" column="students.enroll_status" required="false" description="student enroll status" default="0" /> </args>
AND enroll_status = :enrollStatus

Adding arguments to a PowerQuery allows that query to be called with a specific parameter.
In this case a student enroll_status can be passed to the query.
If no enroll status is passed, a value of 0 will be used.
Notice also that schools.principal appears twice in the <columns> list.

<column column="schools.principal" description="School Principal">schools.principal</column> <column column="schools.principal" description="School Enrollment">schools.enrollment</column>

The final value returned by the query is the result of a subquery.
There is no field representing this value in the schools table, but the PowerQuery expects each value to be associated with a valid table and column. The schools.enrollment alias for this field will result in the value returned from the query having a label of enrollment.

### HTML

<table class="grid" id="schoolListTable"> <tr> <th>School</th> <th>Abbreviation</th> <th>School Number</th> <th>Principal</th> <th>Enrollment</th> </tr> <tr class="schoolRowTemplate"> <td class="schoolCell"></td> <td class="abbreviationCell"></td> <td class="schoolNumCell"></td> <td class="principalCell"></td> <td class="enrollmentCell"></td> </tr> </table>

For the HTML, there is simply the shell of a table. JavaScript will be used to append rows to this table for each result returned by the PowerQuery.

### JavaScript

<script type="text/javascript"> $j(document).ready(function(){ getPowerQueryResults('com.mba.psugDemo.schoolData.enrollment', {"enrollStatus":0}).then(function(schoolData){ buildSchoolsTable(schoolData); }); }); function getPowerQueryResults(endpoint, queryParams){ var recordsRetrieved = $j.Deferred(); $j.ajax({ url: '/ws/schema/query/' + endpoint + '?pagesize=0', type: 'post', headers: {'Accept':'application/json', 'Content-Type':'application/json'}, data: JSON.stringify(queryParams), success: function(response){ recordsRetrieved.resolve(response.record); } }); return recordsRetrieved; }; function buildSchoolsTable(schoolData){ var templateRow = $j('#schoolListTable tr.schoolRowTemplate'); var newRow; $j(schoolData).each(function(){ newRow = templateRow.clone(); newRow.find('.schoolCell').text(this.name); newRow.find('.abbreviationCell').text(this.abbreviation); newRow.find('.schoolNumCell').text(this.school_number); newRow.find('.principalCell').text(this.principal); newRow.find('.enrollmentCell').text(this.enrollment); $j('#schoolListTable').append(newRow); }); templateRow.remove(); } </script>

Document Ready Function

- Call function to retrieve PowerQuery results
- Pass results of PowerQuery to buildSchoolsTable function

getPowerQueryResults()

- Parameters

- endpoint - the name of the PowerQuery to be run
- queryParams - any parameters to be sent to PowerQuery

- recordsRetrieved - This is a deferred object which will be "resolved" when the query results have been retrieved. The function will not return a result until the query results have been collected.
- Notice the url of the AJAX call includes a parameter of pagesize=0. Without a page size parameter, a PowerQuery will return only 100 records for a single call.
- recordsRetrieved.resolve(response.record) - This line notifies the deferred object that the results of the PowerQuery can now be returned from the function.

buildSchoolsTable()

- Parameters

- schoolData - an array of school data used to build the table rows (results of the PowerQuery)

- For each record in the schoolData array, clone the template row and populate the new row with the school data, finally appending that row to the table
- Finally, remove the original (template) row which is still empty from the table

### Result

School | Abbreviation | School Number | Principal | Enrollment
--- | --- | --- | --- | ---

## PowerSchool's Data Restriction Framework

The data restriction framework (DRF) rewrites PowerQueries to apply security.
This can cause unexpected results at times.
Adding the following parameter to the post body can help in debugging.
If the PQ is called with this parameter, the DRF version of the query will be posted to the psj-runtime log.
{"__debug_query":"true"}

### Example DRF Rewrite

Query

SELECT name, school_number, abbreviation, principal, ( SELECT count(id) FROM students WHERE schoolid = schools.school_number AND enroll_status IN (:enrollStatus) ) AS enrollment FROM schools ORDER BY sortorder

DRF query at district

select q.* from (SELECT name, school_number, abbreviation, principal, ( SELECT count(id) FROM students WHERE schoolid = schools.school_number AND enroll_status IN (:enrollStatus) ) AS enrollment FROM schools ORDER BY sortorder) q

DRF query at school

select q.* from ( with "ps$sqlEditor$1" as ( select * from students where ( schoolid in (:ps$Sql$Editor1) or next_school in (:ps$Sql$Editor1) or summerschoolid in (:ps$Sql$Editor1) or id in ( select studentid from reenrollments where schoolid in (:ps$Sql$Editor1) union all select studentid from cc where schoolid in (:ps$Sql$Editor1) ) ) ) SELECT name, school_number, abbreviation, principal, ( SELECT count(id) FROM "ps$sqlEditor$1" WHERE schoolid = schools.school_number AND enroll_status IN (:enrollStatus) ) AS enrollment FROM schools ORDER BY sortorder ) q

Because the DRF rewrites queries in different ways depending on user context (portal, school, field-level security), the same query may result in different results in different scenarios.
The DRF can sometimes have trouble with more complex queries and result in query execution failure. This is not common, but it is worth keeping in mind.
The complexity of rewritten queries can have a significant impact on performance in many cases.

~[x:insertfile;footer.html] ~[wc:admin_footer_css]