File: /home/eblama1/sms.karnplayinland.com/ProgramFunctions/UpdateV4_5.fnc.php
<?php
/**
* Update functions for versions 4 to 5
*
* Incremental updates
*
* @package RosarioSIS
* @subpackage ProgramFunctions
*/
/**
* Update to version 4.0
*
* 0. Create plpgsql language in case it does not exist.
* 1. Fix SQL error in calc_gpa_mp function on INSERT Final Grades for students with various enrollment records.
* enroll_grade view was returning various rows
* while primary key contraints to a unique (student_id,marking_period_id) pair
*
* Local function
*
* @since 4.0
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update40beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
// 0. Create plpgsql language in case it does not exist.
// 1. Fix SQL error in calc_gpa_mp function on INSERT Final Grades for students with various enrollment records.
DBQuery( "CREATE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
CREATE LANGUAGE plpgsql;
SELECT TRUE;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT
(
SELECT TRUE AS exists
FROM pg_language
WHERE lanname = 'plpgsql'
UNION
SELECT FALSE AS exists
ORDER BY exists DESC
LIMIT 1
)
THEN
create_language_plpgsql()
ELSE
FALSE
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
CREATE OR REPLACE FUNCTION calc_gpa_mp(integer, character varying) RETURNS integer AS $$
DECLARE
s_id ALIAS for $1;
mp_id ALIAS for $2;
oldrec student_mp_stats%ROWTYPE;
BEGIN
SELECT * INTO oldrec FROM student_mp_stats WHERE student_id = s_id and cast(marking_period_id as text) = mp_id;
IF FOUND THEN
UPDATE student_mp_stats SET
sum_weighted_factors = rcg.sum_weighted_factors,
sum_unweighted_factors = rcg.sum_unweighted_factors,
cr_weighted_factors = rcg.cr_weighted,
cr_unweighted_factors = rcg.cr_unweighted,
gp_credits = rcg.gp_credits,
cr_credits = rcg.cr_credits
FROM (
select
sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
sum(credit_attempted) as gp_credits,
sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
sum( case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
from student_report_card_grades where student_id = s_id
and cast(marking_period_id as text) = mp_id
and not gp_scale = 0 group by student_id, marking_period_id
) as rcg
WHERE student_id = s_id and cast(marking_period_id as text) = mp_id;
RETURN 1;
ELSE
INSERT INTO student_mp_stats (student_id, marking_period_id, sum_weighted_factors, sum_unweighted_factors, grade_level_short, cr_weighted_factors, cr_unweighted_factors, gp_credits, cr_credits)
select
srcg.student_id, (srcg.marking_period_id::text)::int,
sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
(select eg.short_name
from enroll_grade eg, marking_periods mp
where eg.student_id = s_id
and eg.syear = mp.syear
and eg.school_id = mp.school_id
and eg.start_date <= mp.end_date
and cast(mp.marking_period_id as text) = mp_id
order by eg.start_date desc
limit 1),
sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
sum(credit_attempted) as gp_credits,
sum(case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
from student_report_card_grades srcg
where srcg.student_id = s_id and cast(srcg.marking_period_id as text) = mp_id and not srcg.gp_scale = 0
group by srcg.student_id, srcg.marking_period_id, short_name;
END IF;
RETURN 0;
END
$$
LANGUAGE plpgsql;" );
return $return;
}
/**
* Update to version 4.2
*
* 1. config table:
* Change config_value column type to text
* Was character varying(2550) which could prevent saving rich text with base64 images
* in case there is an issue with the image upload.
*
* Local function
*
* @since 4.2
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update42beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. config table:
* Change config_value column type to text
* Was character varying(2550) which could prevent saving rich text with base64 images
* in case there is an issue with the image upload.
*/
DBQuery( "ALTER TABLE config
ALTER COLUMN config_value TYPE text;" );
return $return;
}
/**
* Update to version 4.3
*
* 1. courses table: Add DESCRIPTION column.
*
* Local function
*
* @since 4.3
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update43beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. courses table: Add DESCRIPTION column.
*/
$description_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'courses')
AND attname = 'description';" );
if ( ! $description_column_exists )
{
DBQuery( "ALTER TABLE ONLY courses
ADD COLUMN description text;" );
}
return $return;
}
/**
* Update to version 4.4
*
* 1. gradebook_assignments table: Add FILE column.
* 2. gradebook_assignments table: Change DESCRIPTION column type to text.
* 3. gradebook_assignments table: Convert DESCRIPTION values from MarkDown to HTML.
*
* Local function
*
* @since 4.4
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update44beta()
{
_isCallerUpdate( debug_backtrace() );
require_once 'ProgramFunctions/MarkDownHTML.fnc.php';
$return = true;
/**
* 1. gradebook_assignments table:
* Add FILE column
*/
$file_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'gradebook_assignments')
AND attname = 'file';" );
if ( ! $file_column_exists )
{
DBQuery( "ALTER TABLE ONLY gradebook_assignments
ADD COLUMN file character varying(1000);" );
}
/**
* 2. gradebook_assignments table:
* Change DESCRIPTION column type to text
* Was character varying(1000) which could prevent saving rich text with base64 images
*/
DBQuery( "ALTER TABLE gradebook_assignments
ALTER COLUMN description TYPE text;" );
/**
* 3. gradebook_assignments table:
* Convert DESCRIPTION values from MarkDown to HTML.
*/
$assignments_RET = DBGet( "SELECT assignment_id,description
FROM gradebook_assignments
WHERE description IS NOT NULL;" );
$assignment_update_sql = "UPDATE gradebook_assignments
SET DESCRIPTION='%s'
WHERE ASSIGNMENT_ID='%d';";
$assignments_update_sql = '';
foreach ( (array) $assignments_RET as $assignment )
{
$description_html = MarkDownToHTML( $assignment['DESCRIPTION'] );
$assignments_update_sql .= sprintf(
$assignment_update_sql,
DBEscapeString( $description_html ),
$assignment['ASSIGNMENT_ID']
);
}
if ( $assignments_update_sql )
{
DBQuery( $assignments_update_sql );
}
return $return;
}
/**
* Update to version 4.4
*
* 1. Add PASSWORD_STRENGTH to config table.
*
* Local function
*
* @since 4.4
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update44beta2()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add PASSWORD_STRENGTH to config table.
*/
$password_strength_added = DBGet( "SELECT 1 FROM config WHERE TITLE='PASSWORD_STRENGTH'" );
if ( ! $password_strength_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'PASSWORD_STRENGTH', '1');" );
}
return $return;
}
/**
* Update to version 4.5
*
* 1. gradebook_assignment_types table: Add CREATED_MP column.
*
* Local function
*
* @since 4.5
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update45beta2()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. gradebook_assignment_types table: Add CREATED_MP column.
*/
$created_at_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'gradebook_assignment_types')
AND attname = 'created_mp';" );
if ( ! $created_at_column_exists )
{
DBQuery( "ALTER TABLE ONLY gradebook_assignment_types
ADD COLUMN created_mp integer;" );
}
return $return;
}
/**
* Update to version 4.6
*
* 1. eligibility_activities table: Add COMMENT column.
*
* Local function
*
* @since 4.6
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update46beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. eligibility_activities table: Add COMMENT column.
*/
$comment_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'eligibility_activities')
AND attname = 'comment';" );
if ( ! $comment_column_exists )
{
DBQuery( "ALTER TABLE ONLY eligibility_activities
ADD COLUMN comment text;" );
}
return $return;
}
/**
* Update to version 4.7
*
* 1. Convert "Edit Pull-Down" fields to "Auto Pull-Down":
* address_fields, custom_fields, people_fields, school_fields & staff_fields tables
*
* 2. Convert "Coded Pull-Down" fields to "Export Pull-Down":
* address_fields, custom_fields, people_fields, school_fields & staff_fields tables
*
* 3. Change Pull-Down (Auto & Export), Select Multiple from Options, Text, Long Text columns type to text:
* ADDRESS, STUDENTS, people, schools & staff tables
*
* Local function
*
* @since 4.7
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update47beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Convert "Edit Pull-Down" fields to "Auto Pull-Down":
* address_fields, custom_fields, people_fields, school_fields & staff_fields tables
*/
$sql_convert_fields = "UPDATE address_fields SET TYPE='autos' WHERE TYPE='edits';";
$sql_convert_fields .= "UPDATE custom_fields SET TYPE='autos' WHERE TYPE='edits';";
$sql_convert_fields .= "UPDATE people_fields SET TYPE='autos' WHERE TYPE='edits';";
$sql_convert_fields .= "UPDATE school_fields SET TYPE='autos' WHERE TYPE='edits';";
$sql_convert_fields .= "UPDATE staff_fields SET TYPE='autos' WHERE TYPE='edits';";
DBQuery( $sql_convert_fields );
/**
* 2. Convert "Coded Pull-Down" fields to "Export Pull-Down":
* address_fields, custom_fields, people_fields, school_fields & staff_fields tables
*/
$sql_convert_fields = "UPDATE address_fields SET TYPE='codeds' WHERE TYPE='exports';";
$sql_convert_fields .= "UPDATE custom_fields SET TYPE='codeds' WHERE TYPE='exports';";
$sql_convert_fields .= "UPDATE people_fields SET TYPE='codeds' WHERE TYPE='exports';";
$sql_convert_fields .= "UPDATE school_fields SET TYPE='codeds' WHERE TYPE='exports';";
$sql_convert_fields .= "UPDATE staff_fields SET TYPE='codeds' WHERE TYPE='exports';";
DBQuery( $sql_convert_fields );
$sql_fields_column_type = '';
/**
* 3. Change Pull-Down (Auto & Export), Select Multiple from Options, Text, Long Text columns type to text:
* ADDRESS, STUDENTS, people, schools & staff tables
*/
$types = "'select','autos','exports','multiple','text','textarea'";
$fields_column_RET = DBGet( "SELECT ID FROM address_fields WHERE TYPE IN(" . $types . ")" );
foreach ( (array) $fields_column_RET as $field_column )
{
$sql_fields_column_type .= "ALTER TABLE address
ALTER COLUMN " . DBEscapeIdentifier( 'CUSTOM_' . $field_column['ID'] ) . " TYPE text;";
}
$fields_column_RET = DBGet( "SELECT ID FROM custom_fields WHERE TYPE IN(" . $types . ")" );
foreach ( (array) $fields_column_RET as $field_column )
{
$sql_fields_column_type .= "ALTER TABLE STUDENTS
ALTER COLUMN " . DBEscapeIdentifier( 'CUSTOM_' . $field_column['ID'] ) . " TYPE text;";
}
$fields_column_RET = DBGet( "SELECT ID FROM people_fields WHERE TYPE IN(" . $types . ")" );
foreach ( (array) $fields_column_RET as $field_column )
{
$sql_fields_column_type .= "ALTER TABLE people
ALTER COLUMN " . DBEscapeIdentifier( 'CUSTOM_' . $field_column['ID'] ) . " TYPE text;";
}
$fields_column_RET = DBGet( "SELECT ID FROM school_fields WHERE TYPE IN(" . $types . ")" );
foreach ( (array) $fields_column_RET as $field_column )
{
$sql_fields_column_type .= "ALTER TABLE schools
ALTER COLUMN " . DBEscapeIdentifier( 'CUSTOM_' . $field_column['ID'] ) . " TYPE text;";
}
$fields_column_RET = DBGet( "SELECT ID FROM staff_fields WHERE TYPE IN(" . $types . ")" );
foreach ( (array) $fields_column_RET as $field_column )
{
$sql_fields_column_type .= "ALTER TABLE STAFF
ALTER COLUMN " . DBEscapeIdentifier( 'CUSTOM_' . $field_column['ID'] ) . " TYPE text;";
}
if ( $sql_fields_column_type )
{
DBQuery( $sql_fields_column_type );
}
return $return;
}
/**
* Update to version 4.7
*
* 1. Add CLASS_RANK_CALCULATE_MPS to config table.
* 2. SQL performance: rewrite set_class_rank_mp() function.
* 3. SQL move calc_cum_gpa_mp() function into t_update_mp_stats() trigger.
*
* Local function
*
* @since 4.7
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update47beta2()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add CLASS_RANK_CALCULATE_MPS to config table.
*/
$class_rank_added = DBGet( "SELECT 1 FROM config WHERE TITLE='CLASS_RANK_CALCULATE_MPS'" );
if ( ! $class_rank_added )
{
$schools_RET = DBGet( "SELECT ID FROM schools;" );
foreach ( (array) $schools_RET as $school )
{
$mps_RET = DBGet( "SELECT MARKING_PERIOD_ID
FROM marking_periods
WHERE SCHOOL_ID='" . $school['ID'] . "'", [], [ 'MARKING_PERIOD_ID' ] );
$mps = array_keys( $mps_RET );
$class_rank_mps = '|' . implode( '||', $mps ) . '|';
DBQuery( "INSERT INTO config
VALUES('" . $school['ID'] . "','CLASS_RANK_CALCULATE_MPS','" . $class_rank_mps . "');" );
}
}
/**
* 2. SQL performance: rewrite set_class_rank_mp() function.
* Create plpgsql language first if does not exist.
*/
DBQuery( "CREATE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
CREATE LANGUAGE plpgsql;
SELECT TRUE;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT
(
SELECT TRUE AS exists
FROM pg_language
WHERE lanname = 'plpgsql'
UNION
SELECT FALSE AS exists
ORDER BY exists DESC
LIMIT 1
)
THEN
create_language_plpgsql()
ELSE
FALSE
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
CREATE OR REPLACE FUNCTION set_class_rank_mp(character varying) RETURNS integer
AS $$
DECLARE
mp_id alias for $1;
BEGIN
update student_mp_stats
set cum_rank = rank.rank, class_size = rank.class_size
from (select mp.marking_period_id, sgm.student_id,
(select count(*)+1
from student_mp_stats sgm3
where sgm3.cum_cr_weighted_factor > sgm.cum_cr_weighted_factor
and sgm3.marking_period_id = mp.marking_period_id
and sgm3.student_id in (select distinct sgm2.student_id
from student_mp_stats sgm2, student_enrollment se2
where sgm2.student_id = se2.student_id
and sgm2.marking_period_id = mp.marking_period_id
and se2.grade_id = se.grade_id)) as rank,
(select count(*)
from student_mp_stats sgm4
where sgm4.marking_period_id = mp.marking_period_id
and sgm4.student_id in (select distinct sgm5.student_id
from student_mp_stats sgm5, student_enrollment se3
where sgm5.student_id = se3.student_id
and sgm5.marking_period_id = mp.marking_period_id
and se3.grade_id = se.grade_id)) as class_size
from student_enrollment se, student_mp_stats sgm, marking_periods mp
where se.student_id = sgm.student_id
and sgm.marking_period_id = mp.marking_period_id
and cast(mp.marking_period_id as text) = mp_id
and se.syear = mp.syear
and not sgm.cum_cr_weighted_factor is null) as rank
where student_mp_stats.marking_period_id = rank.marking_period_id
and student_mp_stats.student_id = rank.student_id;
RETURN 1;
END;
$$
LANGUAGE plpgsql;" );
/**
* 3. SQL move calc_cum_gpa_mp() function into t_update_mp_stats() trigger.
* Create plpgsql language first if does not exist.
*/
DBQuery( "CREATE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
CREATE LANGUAGE plpgsql;
SELECT TRUE;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT
(
SELECT TRUE AS exists
FROM pg_language
WHERE lanname = 'plpgsql'
UNION
SELECT FALSE AS exists
ORDER BY exists DESC
LIMIT 1
)
THEN
create_language_plpgsql()
ELSE
FALSE
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
CREATE OR REPLACE FUNCTION t_update_mp_stats() RETURNS \"trigger\"
AS $$
begin
IF tg_op = 'DELETE' THEN
PERFORM calc_gpa_mp(OLD.student_id::int, OLD.marking_period_id::varchar);
PERFORM calc_cum_gpa(OLD.marking_period_id::varchar, OLD.student_id::int);
PERFORM calc_cum_cr_gpa(OLD.marking_period_id::varchar, OLD.student_id::int);
ELSE
--IF tg_op = 'INSERT' THEN
--we need to do stuff here to gather other information since it's a new record.
--ELSE
--if report_card_grade_id changes, then we need to reset gp values
-- IF NOT NEW.report_card_grade_id = OLD.report_card_grade_id THEN
--
PERFORM calc_gpa_mp(NEW.student_id::int, NEW.marking_period_id::varchar);
PERFORM calc_cum_gpa(NEW.marking_period_id::varchar, NEW.student_id::int);
PERFORM calc_cum_cr_gpa(NEW.marking_period_id::varchar, NEW.student_id::int);
END IF;
return NULL;
end
$$
LANGUAGE plpgsql;" );
return $return;
}
/**
* Update to version 4.9
*
* 1. program_config table: Add Allow Teachers to edit gradebook grades for past quarters option.
*
* Local function
*
* @since 4.9
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update49beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. program_config table: Add Allow Teachers to edit gradebook grades for past quarters option.
*/
$config_option_exists = DBGet( "SELECT 1 FROM program_config
WHERE TITLE='GRADES_GRADEBOOK_TEACHER_ALLOW_EDIT';" );
if ( ! $config_option_exists )
{
DBQuery( "INSERT INTO program_config (VALUE,PROGRAM,TITLE,SCHOOL_ID,SYEAR)
SELECT 'Y','grades','GRADES_GRADEBOOK_TEACHER_ALLOW_EDIT',ID,SYEAR
FROM schools;" );
}
return $return;
}
/**
* Update to version 5.0
*
* 1. Rename sequences.
* Use default name generated by serial: "[table]_[serial_column]_seq".
* 2. Rename sequences for add-on modules.
* Use default name generated by serial: "[table]_[serial_column]_seq".
* 3. @since 5.3 Delete obsolete data first to prevent SQL errors when adding foreign keys. Based on reported error.
* 3. @since 5.4 Test first if can add foreign key based on reported SQL errors:
* ERROR: column "student_id" referenced in foreign key constraint does not exist
* 3. Add foreign keys.
* student_id, staff_id, school_id+syear, marking_period_id, course_period_id, course_id.
*
* Local function
*
* @since 5.0
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update50beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 0. Convert marking_period_id columns to integer.
*/
DBQuery( "ALTER TABLE student_report_card_comments
ALTER COLUMN marking_period_id TYPE integer USING (marking_period_id::integer);
ALTER TABLE grades_completed
ALTER COLUMN marking_period_id TYPE integer USING (marking_period_id::integer);" );
$rename_sequence = function( $old_sequence, $new_sequence )
{
if ( strlen( $new_sequence) > 63 )
{
$cut_at_char = ( 63 - strlen( '_seq' ) );
// Note: sequence name is limited to 63 chars
// @link https://www.postgresql.org/docs/9.0/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
$new_sequence = substr( $new_sequence, 0, $cut_at_char ) . '_seq';
}
$sequence_exists = DBGetOne( "SELECT 1 FROM pg_class
WHERE relname='" . DBEscapeString( $old_sequence ) . "';" );
if ( $sequence_exists )
{
DBQuery( "ALTER SEQUENCE " . $old_sequence . " RENAME TO " . $new_sequence . ";" );
}
};
/**
* 1. Rename sequences.
* Use default name generated by serial: "[table]_[serial_column]_seq".
*/
$rename_sequence( 'user_profiles_seq', 'user_profiles_id_seq' );
$rename_sequence( 'students_join_people_seq', 'students_join_people_id_seq' );
$rename_sequence( 'students_join_address_seq', 'students_join_address_id_seq' );
$rename_sequence( 'students_seq', 'students_student_id_seq' );
$rename_sequence( 'student_report_card_grades_seq', 'student_report_card_grades_id_seq' );
$rename_sequence( 'student_medical_visits_seq', 'student_medical_visits_id_seq' );
$rename_sequence( 'student_medical_alerts_seq', 'student_medical_alerts_id_seq' );
$rename_sequence( 'student_medical_seq', 'student_medical_id_seq' );
$rename_sequence( 'student_field_categories_seq', 'student_field_categories_id_seq' );
$rename_sequence( 'student_enrollment_codes_seq', 'student_enrollment_codes_id_seq' );
$rename_sequence( 'student_enrollment_seq', 'student_enrollment_id_seq' );
$rename_sequence( 'staff_fields_seq', 'staff_fields_id_seq' );
$rename_sequence( 'staff_field_categories_seq', 'staff_field_categories_id_seq' );
$rename_sequence( 'staff_seq', 'staff_staff_id_seq' );
$rename_sequence( 'school_periods_seq', 'school_periods_period_id_seq' );
$rename_sequence( 'schools_seq', 'schools_id_seq' );
$rename_sequence( 'school_gradelevels_seq', 'school_gradelevels_id_seq' );
$rename_sequence( 'school_fields_seq', 'school_fields_id_seq' );
$rename_sequence( 'schedule_requests_seq', 'schedule_requests_request_id_seq' );
$rename_sequence( 'resources_seq', 'resources_id_seq' );
$rename_sequence( 'report_card_grades_seq', 'report_card_grades_id_seq' );
$rename_sequence( 'report_card_grade_scales_seq', 'report_card_grade_scales_id_seq' );
$rename_sequence( 'report_card_comments_seq', 'report_card_comments_id_seq' );
$rename_sequence( 'report_card_comment_codes_seq', 'report_card_comment_codes_id_seq' );
$rename_sequence( 'report_card_comment_code_scales_seq', 'report_card_comment_code_scales_id_seq' );
$rename_sequence( 'report_card_comment_categories_seq', 'report_card_comment_categories_id_seq' );
$rename_sequence( 'portal_polls_seq', 'portal_polls_id_seq' );
$rename_sequence( 'portal_poll_questions_seq', 'portal_poll_questions_id_seq' );
$rename_sequence( 'portal_notes_seq', 'portal_notes_id_seq' );
$rename_sequence( 'people_join_contacts_seq', 'people_join_contacts_id_seq' );
$rename_sequence( 'people_fields_seq', 'people_fields_id_seq' );
$rename_sequence( 'people_field_categories_seq', 'people_field_categories_id_seq' );
$rename_sequence( 'people_seq', 'people_person_id_seq' );
$rename_sequence( 'marking_period_seq', 'school_marking_periods_marking_period_id_seq' );
$rename_sequence( 'gradebook_assignments_seq', 'gradebook_assignments_assignment_id_seq' );
$rename_sequence( 'gradebook_assignment_types_seq', 'gradebook_assignment_types_assignment_type_id_seq' );
$rename_sequence( 'food_service_transactions_seq', 'food_service_transactions_transaction_id_seq' );
$rename_sequence( 'food_service_staff_transactions_seq', 'food_service_staff_transactions_transaction_id_seq' );
$rename_sequence( 'food_service_menus_seq', 'food_service_menus_menu_id_seq' );
$rename_sequence( 'food_service_menu_items_seq', 'food_service_menu_items_menu_item_id_seq' );
$rename_sequence( 'food_service_items_seq', 'food_service_items_item_id_seq' );
$rename_sequence( 'food_service_categories_seq', 'food_service_categories_category_id_seq' );
$rename_sequence( 'eligibility_activities_seq', 'eligibility_activities_id_seq' );
$rename_sequence( 'discipline_referrals_seq', 'discipline_referrals_id_seq' );
$rename_sequence( 'discipline_fields_seq', 'discipline_fields_id_seq' );
$rename_sequence( 'discipline_field_usage_seq', 'discipline_field_usage_id_seq' );
$rename_sequence( 'custom_seq', 'custom_fields_id_seq' );
$rename_sequence( 'course_subjects_seq', 'course_subjects_subject_id_seq' );
$rename_sequence( 'course_period_school_periods_seq', 'course_period_school_periods_course_period_school_periods_id_seq' );
$rename_sequence( 'courses_seq', 'courses_course_id_seq' );
$rename_sequence( 'course_periods_seq', 'course_periods_course_period_id_seq' );
$rename_sequence( 'calendar_events_seq', 'calendar_events_id_seq' );
$rename_sequence( 'billing_payments_seq', 'billing_payments_id_seq' );
$rename_sequence( 'billing_fees_seq', 'billing_fees_id_seq' );
$rename_sequence( 'attendance_codes_seq', 'attendance_codes_id_seq' );
$rename_sequence( 'attendance_code_categories_seq', 'attendance_code_categories_id_seq' );
$rename_sequence( 'calendars_seq', 'attendance_calendars_calendar_id_seq' );
$rename_sequence( 'address_fields_seq', 'address_fields_id_seq' );
$rename_sequence( 'address_field_categories_seq', 'address_field_categories_id_seq' );
$rename_sequence( 'address_seq', 'address_address_id_seq' );
$rename_sequence( 'accounting_payments_seq', 'accounting_payments_id_seq' );
$rename_sequence( 'accounting_salaries_seq', 'accounting_salaries_id_seq' );
$rename_sequence( 'accounting_incomes_seq', 'accounting_incomes_id_seq' );
/**
* 2. Rename sequences for add-on modules.
* Use default name generated by serial: "[table]_[serial_column]_seq".
*/
$rename_sequence( 'billing_fees_monthly_seq', 'billing_fees_monthly_id_seq' );
$rename_sequence( 'school_inventory_categories_seq', 'school_inventory_categories_category_id_seq' );
$rename_sequence( 'school_inventory_items_seq', 'school_inventory_items_item_id_seq' );
$rename_sequence( 'saved_reports_seq', 'saved_reports_id_seq' );
$rename_sequence( 'saved_calculations_seq', 'saved_calculations_id_seq' );
$rename_sequence( 'messages_seq', 'messages_message_id_seq' );
$add_foreign_key = function( $table, $column, $reference )
{
$fcolumn = str_replace( [ ',', ' ' ], [ '_' ], $column );
$fk_name = $table . '_' . $fcolumn . '_fk';
$fk_exists = DBGetOne( "SELECT 1 FROM information_schema.table_constraints
WHERE constraint_type='FOREIGN KEY'
AND constraint_name='" . DBEscapeString( $fk_name ) . "';" );
if ( ! $fk_exists )
{
DBQuery( "ALTER TABLE " . DBEscapeIdentifier( $table ) . " ADD CONSTRAINT " . $fk_name .
" FOREIGN KEY (" . $column . ") REFERENCES " . $reference . ";" );
}
};
/**
* 3. Delete obsolete data first to prevent SQL errors when adding foreign keys.
* Based on reported error.
*
* @since 5.3
*/
$delete_obsolete_sql = "DELETE FROM schedule
WHERE student_id NOT IN(SELECT student_id FROM students);";
$delete_obsolete_sql .= "DELETE FROM food_service_student_accounts
WHERE student_id NOT IN(SELECT student_id FROM students);";
$delete_obsolete_sql .= "DELETE FROM gradebook_assignments
WHERE staff_id NOT IN(SELECT staff_id FROM staff);";
$delete_obsolete_sql .= "DELETE FROM gradebook_assignment_types
WHERE staff_id NOT IN(SELECT staff_id FROM staff);";
DBQuery( $delete_obsolete_sql );
/**
* 3. Test first if can add foreign key based on reported SQL errors:
* ERROR: column "student_id" referenced in foreign key constraint does not exist
*
* @since 5.4
*/
$can_add_foreign_key = DBTransDryRun( "ALTER TABLE " . DBEscapeIdentifier( 'students_join_users' ) . "
ADD CONSTRAINT students_join_users_student_id_fk
FOREIGN KEY (student_id) REFERENCES students(student_id);" );
if ( ! $can_add_foreign_key )
{
return false;
}
/**
* 3. Add foreign keys.
* student_id
*/
$add_foreign_key( 'students_join_users', 'student_id', 'students(student_id)' );
$add_foreign_key( 'students_join_people', 'student_id', 'students(student_id)' );
$add_foreign_key( 'students_join_address', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_enrollment', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_report_card_grades', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_report_card_comments', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_mp_stats', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_mp_comments', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_medical_visits', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_medical_alerts', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_medical', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_eligibility_activities', 'student_id', 'students(student_id)' );
$add_foreign_key( 'student_assignments', 'student_id', 'students(student_id)' );
$add_foreign_key( 'schedule_requests', 'student_id', 'students(student_id)' );
$add_foreign_key( 'schedule', 'student_id', 'students(student_id)' );
$add_foreign_key( 'lunch_period', 'student_id', 'students(student_id)' );
$add_foreign_key( 'gradebook_grades', 'student_id', 'students(student_id)' );
$add_foreign_key( 'food_service_transactions', 'student_id', 'students(student_id)' );
$add_foreign_key( 'food_service_student_accounts', 'student_id', 'students(student_id)' );
$add_foreign_key( 'eligibility', 'student_id', 'students(student_id)' );
$add_foreign_key( 'discipline_referrals', 'student_id', 'students(student_id)' );
$add_foreign_key( 'billing_payments', 'student_id', 'students(student_id)' );
$add_foreign_key( 'billing_fees', 'student_id', 'students(student_id)' );
$add_foreign_key( 'attendance_period', 'student_id', 'students(student_id)' );
$add_foreign_key( 'attendance_day', 'student_id', 'students(student_id)' );
/**
* 3. Add foreign keys.
* staff_id
*/
$add_foreign_key( 'course_periods', 'teacher_id', 'staff(staff_id)' );
$add_foreign_key( 'students_join_users', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'staff_exceptions', 'user_id', 'staff(staff_id)' );
$add_foreign_key( 'grades_completed', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'gradebook_assignments', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'gradebook_assignment_types', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'food_service_staff_transactions', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'food_service_staff_accounts', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'eligibility_completed', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'discipline_referrals', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'attendance_completed', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'accounting_payments', 'staff_id', 'staff(staff_id)' );
$add_foreign_key( 'accounting_salaries', 'staff_id', 'staff(staff_id)' );
/**
* 3. Add foreign keys.
* school_id+syear
*/
$add_foreign_key( 'student_enrollment', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'student_report_card_comments', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'school_periods', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'schedule_requests', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'schedule', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'report_card_grades', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'report_card_grade_scales', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'report_card_comments', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'report_card_comment_categories', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'program_config', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'portal_polls', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'portal_notes', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'school_marking_periods', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'food_service_transactions', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'food_service_staff_transactions', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'eligibility_activities', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'discipline_referrals', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'discipline_field_usage', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'course_subjects', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'courses', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'course_periods', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'calendar_events', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'billing_payments', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'billing_fees', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'attendance_codes', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'attendance_code_categories', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'attendance_calendars', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'attendance_calendar', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'accounting_payments', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'accounting_salaries', 'school_id,syear', 'schools(id,syear)' );
$add_foreign_key( 'accounting_incomes', 'school_id,syear', 'schools(id,syear)' );
/**
* 3. Add foreign keys.
* marking_period_id
*/
$add_foreign_key( 'student_report_card_comments', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'student_mp_comments', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'schedule_requests', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'schedule', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'lunch_period', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'grades_completed', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'gradebook_assignments', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'course_periods', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'attendance_period', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
$add_foreign_key( 'attendance_day', 'marking_period_id', 'school_marking_periods(marking_period_id)' );
/**
* 3. Add foreign keys.
* course_period_id
*/
$add_foreign_key( 'student_report_card_grades', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'student_report_card_comments', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'schedule', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'lunch_period', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'grades_completed', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'gradebook_grades', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'gradebook_assignments', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'eligibility', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'course_period_school_periods', 'course_period_id', 'course_periods(course_period_id)' );
$add_foreign_key( 'attendance_period', 'course_period_id', 'course_periods(course_period_id)' );
/**
* 3. Add foreign keys.
* course_id
*/
$add_foreign_key( 'schedule_requests', 'course_id', 'courses(course_id)' );
$add_foreign_key( 'schedule', 'course_id', 'courses(course_id)' );
$add_foreign_key( 'report_card_comment_categories', 'course_id', 'courses(course_id)' );
$add_foreign_key( 'gradebook_assignments', 'course_id', 'courses(course_id)' );
$add_foreign_key( 'gradebook_assignment_types', 'course_id', 'courses(course_id)' );
$add_foreign_key( 'course_periods', 'course_id', 'courses(course_id)' );
return $return;
}
/**
* Update to version 5.0.1
*
* 1. course_periods table:
* Change title column type to text
* Was character varying(255) which could prevent saving long Course Period titles
* Needs to DROP course_details view first to then recreate it.
*
* Local function
*
* @since 5.0.1
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update501()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. course_periods table:
* Change title column type to text
* Was character varying(255) which could prevent saving long Course Period titles
* Needs to DROP course_details VIEW first to then recreate it.
*/
$sql_drop_view = "DROP VIEW course_details;";
$sql_alter_table = "ALTER TABLE course_periods
ALTER COLUMN title TYPE text;";
$sql_create_view = "CREATE VIEW course_details AS
SELECT cp.school_id, cp.syear, cp.marking_period_id, c.subject_id, cp.course_id, cp.course_period_id, cp.teacher_id, c.title AS course_title, cp.title AS cp_title, cp.grade_scale_id, cp.mp, cp.credits FROM course_periods cp, courses c WHERE (cp.course_id = c.course_id);";
DBQuery( $sql_drop_view . $sql_alter_table . $sql_create_view );
return $return;
}
/**
* Update to version 5.2
*
* 1. Add NOT NULL constraint to TITLE columns.
* 2. Fix SQL error rename sequence to course_period_school_periods_course_period_school_periods_i_seq.
*
* Local function
*
* @since 5.2
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update52beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add NOT NULL constraint to TITLE columns.
*/
$add_not_null_constraint = function( $table, $column )
{
$table_escaped = DBEscapeIdentifier( $table );
$column_escaped = DBEscapeIdentifier( $column );
// Set NULL values to '-' first so we avoid SQL errors on ALTER TABLE.
DBQuery( "UPDATE " . $table_escaped . "
SET " . $column_escaped . "='-'
WHERE " . $column_escaped . " IS NULL;
ALTER TABLE " . $table_escaped . "
ALTER COLUMN " . $column_escaped . " SET NOT NULL;" );
};
$tables_columns = [
'schools' => 'TITLE',
'school_marking_periods' => 'TITLE',
'accounting_salaries' => 'TITLE',
'address_field_categories' => 'TITLE',
'address_fields' => 'TITLE',
'attendance_calendars' => 'TITLE',
'attendance_code_categories' => 'TITLE',
'attendance_codes' => 'TITLE',
'billing_fees' => 'TITLE',
'calendar_events' => 'TITLE',
'config' => 'TITLE',
'custom_fields' => 'TITLE',
'discipline_field_usage' => 'TITLE',
'eligibility_activities' => 'TITLE',
'food_service_categories' => 'TITLE',
'gradebook_assignment_types' => 'TITLE',
'gradebook_assignments' => 'TITLE',
'history_marking_periods' => 'NAME',
'people_field_categories' => 'TITLE',
'portal_notes' => 'TITLE',
'portal_poll_questions' => 'QUESTION',
'portal_polls' => 'TITLE',
'program_config' => 'TITLE',
'program_user_config' => 'TITLE',
'report_card_comment_categories' => 'TITLE',
'report_card_comments' => 'TITLE',
'report_card_grade_scales' => 'TITLE',
'report_card_grades' => 'TITLE',
'resources' => 'TITLE',
'school_fields' => 'TITLE',
'school_gradelevels' => 'TITLE',
'school_periods' => 'TITLE',
'staff_exceptions' => 'MODNAME',
'student_enrollment_codes' => 'TITLE',
'student_field_categories' => 'TITLE',
'student_report_card_grades' => 'COURSE_TITLE',
'user_profiles' => 'TITLE',
];
foreach ( $tables_columns as $table => $column )
{
$add_not_null_constraint( $table, $column );
}
/**
* 2. Fix SQL error rename sequence to course_period_school_periods_course_period_school_periods_i_seq
*/
$sequence_exists = DBGetOne( "SELECT 1 FROM pg_class
WHERE relname='course_period_school_periods_course_period_school_periods_id_se';" );
if ( $sequence_exists )
{
DBQuery( "ALTER SEQUENCE course_period_school_periods_course_period_school_periods_id_se
RENAME TO course_period_school_periods_course_period_school_periods_i_seq;" );
}
return $return;
}
/**
* Update to version 5.3
*
* 1. Add FORCE_PASSWORD_CHANGE_ON_FIRST_LOGIN to config table.
*
* Local function
*
* @since 5.3
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update53beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add FORCE_PASSWORD_CHANGE_ON_FIRST_LOGIN to config table.
*/
$force_poassword_change_added = DBGet( "SELECT 1 FROM config WHERE TITLE='FORCE_PASSWORD_CHANGE_ON_FIRST_LOGIN'" );
if ( ! $force_poassword_change_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'FORCE_PASSWORD_CHANGE_ON_FIRST_LOGIN', NULL);" );
}
return $return;
}
/**
* Update to version 5.4.1
*
* 1. Add CREATED_AT & UPDATED_AT columns to every table, 93 tables.
* 2. Add set_updated_at() function & set_updated_at trigger.
* Create plpgsql language in case it does not exist.
*
* Local function
*
* @since 5.4.1
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update541()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add CREATED_AT & UPDATED_AT columns to every table, 93 tables.
*/
$add_created_updated_at_columns = function( $table )
{
$created_at_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname='" . $table . "')
AND attname = 'created_at';" );
if ( $created_at_column_exists )
{
return '';
}
return "ALTER TABLE ONLY " . DBEscapeIdentifier( $table ) . "
ADD COLUMN created_at timestamp DEFAULT current_timestamp;
ALTER TABLE ONLY " . DBEscapeIdentifier( $table ) . "
ADD COLUMN updated_at timestamp;";
};
$tables = [
'schools',
'students',
'staff',
'school_marking_periods',
'courses',
'course_periods',
'access_log',
'accounting_incomes',
'accounting_salaries',
'accounting_payments',
'address',
'address_field_categories',
'address_fields',
'attendance_calendar',
'attendance_calendars',
'attendance_code_categories',
'attendance_codes',
'attendance_completed',
'attendance_day',
'attendance_period',
'billing_fees',
'billing_payments',
'calendar_events',
'config',
'course_period_school_periods',
'course_subjects',
'custom_fields',
'discipline_field_usage',
'discipline_fields',
'discipline_referrals',
'eligibility',
'eligibility_activities',
'eligibility_completed',
'food_service_accounts',
'food_service_categories',
'food_service_items',
'food_service_menu_items',
'food_service_menus',
'food_service_staff_accounts',
'food_service_staff_transaction_items',
'food_service_staff_transactions',
'food_service_student_accounts',
'food_service_transaction_items',
'food_service_transactions',
'gradebook_assignment_types',
'gradebook_assignments',
'gradebook_grades',
'grades_completed',
'lunch_period',
'history_marking_periods',
'moodlexrosario',
'people',
'people_field_categories',
'people_fields',
'people_join_contacts',
'portal_notes',
'portal_poll_questions',
'portal_polls',
'profile_exceptions',
'program_config',
'program_user_config',
'report_card_comment_categories',
'report_card_comment_code_scales',
'report_card_comment_codes',
'report_card_comments',
'report_card_grade_scales',
'report_card_grades',
'resources',
'schedule',
'schedule_requests',
'school_fields',
'school_gradelevels',
'school_periods',
'staff_exceptions',
'staff_field_categories',
'staff_fields',
'student_assignments',
'student_eligibility_activities',
'student_enrollment_codes',
'student_field_categories',
'student_medical',
'student_medical_alerts',
'student_medical_visits',
'student_mp_comments',
'student_mp_stats',
'student_report_card_comments',
'student_report_card_grades',
'student_enrollment',
'students_join_address',
'students_join_people',
'students_join_users',
'templates',
'user_profiles',
];
$sql_add_created_updated_at_columns = '';
foreach ( $tables as $table )
{
$sql_add_created_updated_at_columns .= $add_created_updated_at_columns( $table );
}
if ( $sql_add_created_updated_at_columns )
{
DBQuery( $sql_add_created_updated_at_columns );
}
/**
* 2. Add set_updated_at() function & set_updated_at trigger.
* Create plpgsql language in case it does not exist.
*/
$set_updated_at_trigger_exists = DBGetOne( "SELECT 1
FROM pg_catalog.pg_proc
WHERE proname='set_updated_at';" );
if ( ! $set_updated_at_trigger_exists )
{
DBQuery( "CREATE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
CREATE LANGUAGE plpgsql;
SELECT TRUE;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT
(
SELECT TRUE AS exists
FROM pg_language
WHERE lanname = 'plpgsql'
UNION
SELECT FALSE AS exists
ORDER BY exists DESC
LIMIT 1
)
THEN
create_language_plpgsql()
ELSE
FALSE
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION set_updated_at_triggers() RETURNS void AS $$
DECLARE
t text;
BEGIN
FOR t IN
SELECT table_name FROM information_schema.columns
WHERE column_name = 'updated_at'
LOOP
EXECUTE
'CREATE TRIGGER set_updated_at
BEFORE UPDATE ON ' || t || '
FOR EACH ROW EXECUTE PROCEDURE set_updated_at()';
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT set_updated_at_triggers();
DROP FUNCTION set_updated_at_triggers();" );
}
return $return;
}
/**
* Update to version 5.4.2
*
* 0. Create plpgsql language in case it does not exist.
* 1. Fix SQL error in calc_gpa_mp function on INSERT Final Grades: column short_name does not exist, PostgreSQL 8.4.
*
* Local function
*
* @since 5.4.2
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update542()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
// 0. Create plpgsql language in case it does not exist.
// 1. Fix SQL error in calc_gpa_mp function on INSERT Final Grades: column short_name does not exist, PostgreSQL 8.4.
DBQuery( "CREATE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
CREATE LANGUAGE plpgsql;
SELECT TRUE;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT
(
SELECT TRUE AS exists
FROM pg_language
WHERE lanname = 'plpgsql'
UNION
SELECT FALSE AS exists
ORDER BY exists DESC
LIMIT 1
)
THEN
create_language_plpgsql()
ELSE
FALSE
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
CREATE OR REPLACE FUNCTION calc_gpa_mp(integer, character varying) RETURNS integer AS $$
DECLARE
s_id ALIAS for $1;
mp_id ALIAS for $2;
oldrec student_mp_stats%ROWTYPE;
BEGIN
SELECT * INTO oldrec FROM student_mp_stats WHERE student_id = s_id and cast(marking_period_id as text) = mp_id;
IF FOUND THEN
UPDATE student_mp_stats SET
sum_weighted_factors = rcg.sum_weighted_factors,
sum_unweighted_factors = rcg.sum_unweighted_factors,
cr_weighted_factors = rcg.cr_weighted,
cr_unweighted_factors = rcg.cr_unweighted,
gp_credits = rcg.gp_credits,
cr_credits = rcg.cr_credits
FROM (
select
sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
sum(credit_attempted) as gp_credits,
sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
sum( case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
from student_report_card_grades where student_id = s_id
and cast(marking_period_id as text) = mp_id
and not gp_scale = 0 group by student_id, marking_period_id
) as rcg
WHERE student_id = s_id and cast(marking_period_id as text) = mp_id;
RETURN 1;
ELSE
INSERT INTO student_mp_stats (student_id, marking_period_id, sum_weighted_factors, sum_unweighted_factors, grade_level_short, cr_weighted_factors, cr_unweighted_factors, gp_credits, cr_credits)
select
srcg.student_id,
(srcg.marking_period_id::text)::int,
sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
(select eg.short_name
from enroll_grade eg, marking_periods mp
where eg.student_id = s_id
and eg.syear = mp.syear
and eg.school_id = mp.school_id
and eg.start_date <= mp.end_date
and cast(mp.marking_period_id as text) = mp_id
order by eg.start_date desc
limit 1) as short_name,
sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
sum(credit_attempted) as gp_credits,
sum(case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
from student_report_card_grades srcg
where srcg.student_id = s_id and cast(srcg.marking_period_id as text) = mp_id and not srcg.gp_scale = 0
group by srcg.student_id, srcg.marking_period_id, short_name;
END IF;
RETURN 0;
END
$$
LANGUAGE plpgsql;" );
return $return;
}
/**
* Update to version 5.5
*
* 0. report_card_grades table: Cut titles > 5 chars.
* 1. report_card_grades table: Change title column type to character varying(5)
* Was text which could prevent saving letter grades > 5 chars
* @see student_report_card_grades letter_grade column.
*
* Local function
*
* @since 5.5
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update55beta3()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 0. report_card_grades table: Cut titles > 5 chars.
*/
DBQuery( "UPDATE report_card_grades
SET TITLE=SUBSTRING(TITLE FROM 1 FOR 5);" );
/**
* 1. report_card_grades table: Change title column type to character varying(5)
* Was text which could prevent saving letter grades > 5 chars
* @see student_report_card_grades letter_grade column.
*/
DBQuery( "ALTER TABLE report_card_grades
ALTER COLUMN title TYPE character varying(5);" );
return $return;
}
/**
* Update to version 5.7
*
* 1. address table:
* Change city & mail_city column type to text
* Was character varying(60) which could prevent long city names.
* 2. address table:
* Change state & mail_state column type to character varying(50)
* Was character varying(10). Now allows storing country.
*
* Local function
*
* @since 5.7
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update57()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. address table:
* Change city & mail_city column type to text
* Was character varying(60) which could prevent long city names.
*/
DBQuery( "ALTER TABLE address
ALTER COLUMN city TYPE text;
ALTER TABLE address
ALTER COLUMN mail_city TYPE text;" );
/**
* 2. address table:
* Change state & mail_state column type to character varying(50)
* Was character varying(10). Now allows storing country.
*/
DBQuery( "ALTER TABLE address
ALTER COLUMN state TYPE character varying(50);
ALTER TABLE address
ALTER COLUMN mail_state TYPE character varying(50);" );
return $return;
}
/**
* Update to version 5.8-beta5
*
* 1. school_gradelevels table:
* Change short_name column type to character varying(3)
* Was character varying(2). Now allows French elementary grade levels.
*
* Local function
*
* @since 5.8
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update58beta5()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. school_gradelevels table:
* Change short_name column type to character varying(3)
* Was character varying(2). Now allows French elementary grade levels.
*
* Must drop enroll_grade view first and recreate it afterwards.
*/
DBQuery( "BEGIN;
DROP VIEW enroll_grade;
ALTER TABLE school_gradelevels
ALTER COLUMN short_name TYPE character varying(3);
CREATE VIEW enroll_grade AS
SELECT e.id, e.syear, e.school_id, e.student_id, e.start_date, e.end_date, sg.short_name, sg.title FROM student_enrollment e, school_gradelevels sg WHERE (e.grade_id = sg.id);
COMMIT;" );
return $return;
}
/**
* Update to version 5.9-beta
*
* 1. staff_fields table:
* Add Email & Phone to Staff Fields.
* Eventually translate Field Title to Spanish or French.
*
* 2. staff table:
* Move Email & Phone Staff Fields to custom fields.
* Rename phone columns to custom_200000001.
* Change type to character varying(255) for email (was character varying(100))
* and text for custom_200000001 (was character varying(100)).
*
* Local function
*
* @since 5.9
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update59beta()
{
global $locale;
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. staff_fields table:
* Add Email & Phone to Staff Fields.
*/
$staff_fields_exist = DBGetOne( "SELECT 1 FROM staff_fields
WHERE ID='200000000'" );
if ( ! $staff_fields_exist )
{
DBQuery( "INSERT INTO staff_fields VALUES (200000000, 'text', 'Email Address', 0, NULL, 1, NULL, NULL);
INSERT INTO staff_fields VALUES (200000001, 'text', 'Phone Number', 1, NULL, 1, NULL, NULL);" );
/**
* Eventually translate Field Title to Spanish or French.
*/
if ( $locale === 'fr_FR.utf8' )
{
DBQuery( "UPDATE staff_fields
SET title='Email Address|fr_FR.utf8:Adresse Email'
WHERE id=200000000;
UPDATE staff_fields
SET title='Phone Number|fr_FR.utf8:Numéro de Téléphone'
WHERE id=200000001;" );
}
elseif ( $locale === 'es_ES.utf8' )
{
DBQuery( "UPDATE staff_fields SET title='Email Address|es_ES.utf8:Email'
WHERE id=200000000;
UPDATE staff_fields SET title='Phone Number|es_ES.utf8:Número de Teléfono'
WHERE id=200000001;" );
}
}
/**
* 2. staff table:
* Move Email & Phone Staff Fields to custom fields.
* Rename phone columns to custom_200000001.
* Change type to character varying(255) for email (was character varying(100))
* and text for custom_200000001 (was character varying(100)).
*/
$custom_200000001_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'staff')
AND attname = 'custom_200000001';" );
if ( ! $custom_200000001_column_exists )
{
DBQuery( "ALTER TABLE staff
RENAME COLUMN phone TO custom_200000001;
ALTER TABLE staff
ALTER COLUMN email TYPE character varying(255);
ALTER TABLE staff
ALTER COLUMN custom_200000001 TYPE text;" );
}
return $return;
}
/**
* Update to version 5.9-beta2
*
* 1. Add CREATE_STUDENT_ACCOUNT_AUTOMATIC_ACTIVATION to config table.
*
* Local function
*
* @since 5.9
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update59beta2()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add CREATE_STUDENT_ACCOUNT_AUTOMATIC_ACTIVATION to config table.
*/
$automatic_activation_added = DBGetOne( "SELECT 1 FROM config
WHERE TITLE='CREATE_STUDENT_ACCOUNT_AUTOMATIC_ACTIVATION'" );
if ( ! $automatic_activation_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'CREATE_STUDENT_ACCOUNT_AUTOMATIC_ACTIVATION', NULL);" );
}
return $return;
}
/**
* Update to version 5.9
*
* 1. Move REMOVE_ACCESS_USERNAME_PREFIX_ADD to config table.
*
* Local function
*
* @since 5.9
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update59()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Move REMOVE_ACCESS_USERNAME_PREFIX_ADD to config table.
*/
$username_prefix_add_added = DBGetOne( "SELECT 1 FROM config
WHERE TITLE='REMOVE_ACCESS_USERNAME_PREFIX_ADD'" );
if ( ! $username_prefix_add_added )
{
// Move REMOVE_ACCESS_USERNAME_PREFIX_ADD from program_config (per school) to config (all schools, 0).
$old_program_config_value = ProgramConfig( 'custom', 'REMOVE_ACCESS_USERNAME_PREFIX_ADD' );
DBQuery( "INSERT INTO config VALUES (0, 'REMOVE_ACCESS_USERNAME_PREFIX_ADD', '" . $old_program_config_value . "');" );
}
return $return;
}
/**
* Update to version 5.9.1
*
* 1. transcript_grades view:
* SQL Fix School Base Grading Scale for Historical Grades in transcript_grades view.
*
* Local function
*
* @since 5.9.1
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update591()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. transcript_grades view:
* SQL Fix School Base Grading Scale for Historical Grades in transcript_grades view.
*/
$sql_drop_view = "DROP VIEW transcript_grades;";
$sql_create_view = "CREATE VIEW transcript_grades AS
SELECT mp.syear,mp.school_id,mp.marking_period_id,mp.mp_type,
mp.short_name,mp.parent_id,mp.grandparent_id,
(SELECT mp2.end_date
FROM student_report_card_grades
JOIN marking_periods mp2
ON mp2.marking_period_id::text = student_report_card_grades.marking_period_id::text
WHERE student_report_card_grades.student_id = sms.student_id::numeric
AND (student_report_card_grades.marking_period_id::text = mp.parent_id::text
OR student_report_card_grades.marking_period_id::text = mp.grandparent_id::text)
AND student_report_card_grades.course_title::text = srcg.course_title::text
ORDER BY mp2.end_date LIMIT 1) AS parent_end_date,
mp.end_date,sms.student_id,
(sms.cum_weighted_factor * COALESCE(schools.reporting_gp_scale, (SELECT reporting_gp_scale FROM schools WHERE mp.school_id = id ORDER BY syear LIMIT 1))) AS cum_weighted_gpa,
(sms.cum_unweighted_factor * schools.reporting_gp_scale) AS cum_unweighted_gpa,
sms.cum_rank,sms.mp_rank,sms.class_size,
((sms.sum_weighted_factors / sms.count_weighted_factors) * schools.reporting_gp_scale) AS weighted_gpa,
((sms.sum_unweighted_factors / sms.count_unweighted_factors) * schools.reporting_gp_scale) AS unweighted_gpa,
sms.grade_level_short,srcg.comment,srcg.grade_percent,srcg.grade_letter,
srcg.weighted_gp,srcg.unweighted_gp,srcg.gp_scale,srcg.credit_attempted,
srcg.credit_earned,srcg.course_title,srcg.school AS school_name,
schools.reporting_gp_scale AS school_scale,
((sms.cr_weighted_factors / sms.count_cr_factors::numeric) * schools.reporting_gp_scale) AS cr_weighted_gpa,
((sms.cr_unweighted_factors / sms.count_cr_factors::numeric) * schools.reporting_gp_scale) AS cr_unweighted_gpa,
(sms.cum_cr_weighted_factor * schools.reporting_gp_scale) AS cum_cr_weighted_gpa,
(sms.cum_cr_unweighted_factor * schools.reporting_gp_scale) AS cum_cr_unweighted_gpa,
srcg.class_rank,sms.comments,
srcg.credit_hours
FROM marking_periods mp
JOIN student_report_card_grades srcg
ON mp.marking_period_id::text = srcg.marking_period_id::text
JOIN student_mp_stats sms
ON sms.marking_period_id::numeric = mp.marking_period_id
AND sms.student_id::numeric = srcg.student_id
LEFT OUTER JOIN schools
ON mp.school_id = schools.id
AND (mp.mp_source<>'History' AND mp.syear = schools.syear)
OR mp.syear=(SELECT syear FROM schools WHERE mp.school_id = id ORDER BY syear LIMIT 1)
ORDER BY srcg.course_period_id;";
DBQuery( $sql_drop_view . $sql_create_view );
return $return;
}