switch from integer to numeric for gradeable component total (Submitt…

…y#1608) * added database changes to fix the problem * allow floating point in component total * fix
UCL-RITS · Nov 15, 2017 · 228ba1f · 228ba1f
1 parent 56c52ea
commit 228ba1f
Show file tree

Hide file tree

Showing 2 changed files with 12 additions and 4 deletions.
diff --git a/.setup/update_database.py b/.setup/update_database.py
@@ -4,10 +4,13 @@
 # Run this file to upgrade all course databases from v.1.0.1 to v.1.0.2 (November 2017)
 #
 # Changes:
+#  v.1.0.2 
 #    Adds column gcm_publish to the gradeable_component_mark table
 #    Adds column eg_allow_late_submission to the electronic gradeable table
 #    Add timezone to the type of gcd_grade_time in the gradeable_component_data table
 #    Modify foreign key cascade constraint on gradeable_component_mark_data table
+#  v.1.0.3
+#    Switch component fields to allow floating point (not just integers)
 #
 
 from datetime import datetime
@@ -28,12 +31,17 @@
 
     print ("updating course database " + db)
 
-    # edits to migrant v.1.0.1 to v.1.0.2
+    # edits to migrate v.1.0.1 to v.1.0.2
     os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE ONLY gradeable_component_mark ADD COLUMN gcm_publish boolean DEFAULT false NOT NULL'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
     os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE ONLY electronic_gradeable ADD COLUMN eg_allow_late_submission boolean DEFAULT true NOT NULL'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
     os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE gradeable_component_data ALTER COLUMN gcd_grade_time SET DATA TYPE timestamp(6) with time zone'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
     os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE ONLY gradeable_component_mark_data DROP CONSTRAINT gradeable_component_mark_data_gd_id_and_gc_id_fkey'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
     os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE ONLY gradeable_component_mark_data ADD CONSTRAINT gradeable_component_mark_data_gd_id_and_gc_id_fkey FOREIGN KEY (gd_id, gc_id, gcd_grader_id) REFERENCES gradeable_component_data(gd_id, gc_id, gcd_grader_id) ON UPDATE CASCADE ON DELETE CASCADE'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
 
+    # edits to migrate v.1.0.2 to v.1.0.3
+    os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE gradeable_component ALTER COLUMN gc_lower_clamp SET DATA TYPE numeric'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
+    os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE gradeable_component ALTER COLUMN gc_default SET DATA TYPE numeric'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
+    os.system("PGPASSWORD='{}' psql --host={} --username={} --dbname={} -c 'ALTER TABLE gradeable_component ALTER COLUMN gc_upper_clamp SET DATA TYPE numeric'".format(settings['database_password'], settings['database_host'], settings['database_user'], db))
+
     print ("\n")
 
diff --git a/site/data/course_tables.sql b/site/data/course_tables.sql
@@ -232,10 +232,10 @@ CREATE TABLE gradeable_component (
     gc_title character varying(255) NOT NULL,
     gc_ta_comment character varying NOT NULL,
     gc_student_comment character varying NOT NULL,
-    gc_lower_clamp integer NOT NULL,
-    gc_default integer NOT NULL,
+    gc_lower_clamp numeric NOT NULL,
+    gc_default numeric NOT NULL,
     gc_max_value numeric NOT NULL,
-    gc_upper_clamp integer NOT NULL,
+    gc_upper_clamp numeric NOT NULL,
     gc_is_text boolean NOT NULL,
     gc_is_peer boolean NOT NULL,
     gc_order integer NOT NULL,