Checks If 5.x Database Upgrade Done

If the anti-virus and/or Firewall settings or user permissions did not allow bat-files to be run the database upgrade scripts did not run properly. In order to verify that all upgrade scripts have run properly the below SQL queries should be run.

/* Query 1 - 5.04.00 Upgrade Check, May 2009, 030: Field Report should have 2 new columns. Should be 2 rows */
select table_name, column_name from information_schema.columns where table_name = 'fieldreport' and column_name 
In ('import_errors_unresolved', 'approvable');

/* Query 2 - 5.04.00 Upgrade Check, May 2009, 050: Localidtype table should be created. Should be 1 rows */
select table_schema, table_name  from information_schema.tables where table_name = 'localidtype';

/* Query 3 - 5.04.00 Upgrade Check, May 2009, 080: 14 tables should have calculated area and linelength added. 
Should be 28 rows */
select table_name, column_name from information_schema.columns where column_name IN  'calculated_area', 'calculated_line_length');

/* Query 4 - 5.05.00 Upgrade Check, November 2009, 010: Org_guid should be added to 6 tables. Should be 6 rows */
select table_name, column_name from information_schema.columns where column_name = 'org_guid' and table_name in ('Accident', 'Accidentinfoversion', 'Hazard', 'Hazardinfoversion', 'location', 'locationinfoversion');

/* Query 5 - 5.05.00 Upgrade Check, November 2009, 060: Current view and task tables should be added. Should be 10 rows */
select table_schema, table_name from information_schema.tables where table_name In ('Custom_view', 'task', 'work_item', 'task_related_item', 'task_has_cdfvalue', 'task_has_geospatialinfo', 
'task_has_objective', 'task_status_history', 'workitem_has_cdfvalue', 'tool');

/* Query 6 - 5.05.03 Upgrade Check, March 2010, 010: Check Subtheme and Form Template Permissiosn. Should be 2 rows */
select permission_guid, permission from permission where permission IN ('Main.Theme_Customisation.Execute', 'Main.Form_Template_Publish.Execute');

/* Query 7 - 5.06.00 Upgrade Check, December 2010, 010: Evidence Point and Usage Point are there. Should be 2 rows */
select enumcategory, enumvalue from imsmaenum where enumvalue in ('Evidence Point', 'Usage Point');

/* Query 8 - 5.06.00 Upgrade Check, December 2010, 090: Verify that QM form is published. Should be 1 row. */
select fieldreportname from fieldreportdesc where fieldreportname = 'Default QM Form';

/* Query 9 - 5.07.00 Upgrade Check, June 2011, 010: Make sure geopoint, accdevice, hazdevice, and hazreducdevice have cdf_value tables, Should be 4 rows */
select table_name from information_schema.tables where table_name IN ('geopoint_has_cdfvalue','accdevice_has_cdfvalue','hazdevice_has_cdfvalue','hazreducdevice_has_cdfvalue');

/* Query 10 - 5.07.00 Upgrade Check, June 2011, 030: Make sure 3 new columns available in cdfvalue table. Should be 3 rows */
select Column_name, character_maximum_length from information_schema.columns where table_schema = 'imsma' and table_name = 'cdfvalue' and column_name IN ('place_value','organisation_value', 'gazetteer_value');

/* Query 11 - 5.07.00 Upgrade Check, June 2011, 080: Make sure link has 2 new columns. Should be 2 rows */
select Column_name, character_maximum_length from information_schema.columns where table_schema = 'imsma' and table_name = 'link' and column_name IN ('new_location_guid','old_location_guid');

/* Query 12 - 5.07.00 Upgrade Check, June 2011, 090: Check for decimal columns. Should be 97 rows.  */
select table_name, column_name from information_schema.columns where table_schema = 'imsma' and data_type = 'decimal' and table_name NOT IN (select table_name from information_schema.views) order by table_name;

/* Query 13 - 5.07.01 Upgrade Check, August 2011, 020: Make sure preference_key column is 75 chars. Should be 1 row */
select Column_name, character_maximum_length from information_schema.columns where table_schema = 'imsma' and column_name = 'preference_key';

/* Query 14 - 5.08.02 Upgrade Check, December 2011: Added permission group. Should be 1 row */
SELECT permission.permission FROM permission WHERE permission.permission LIKE '%submit%';

/* Query 15 - General Upgrade Check: Check Version table for correct entries*/
select entry_order, major, minor, patch, date from version order by entry_order;

Template:NavBox Hub