Checks If 5.x Database Upgrade Done - Revision history

Alnaucler at 17:19, 27 February 2015

2015-02-27T17:19:43Z

← Older revision		Revision as of 19:19, 27 February 2015
Line 1:		Line 1:
	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.		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.
	~~<pre>~~
	/* Query 1 - 5.04.00 Upgrade Check, May 2009, 030: Field Report should have 2 new columns. Should be 2 rows */		/* 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		select table_name, column_name from information_schema.columns where table_name = 'fieldreport' and column_name
Line 10:		Line 10:
	/* Query 3 - 5.04.00 Upgrade Check, May 2009, 080: 14 tables should have calculated area and linelength added.		/* Query 3 - 5.04.00 Upgrade Check, May 2009, 080: 14 tables should have calculated area and linelength added.
	Should be 28 rows */		Should be 28 rows */
	select table_name, column_name from information_schema.columns where column_name IN 'calculated_area', 'calculated_line_length');		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 */		/* Query 4 - 5.05.00 Upgrade Check, November 2009, 010: Org_guid should be added to 6 tables. Should be 6 rows */
Line 48:		Line 48:
	/* Query 15 - General Upgrade Check: Check Version table for correct entries*/		/* Query 15 - General Upgrade Check: Check Version table for correct entries*/
	select entry_order, major, minor, patch, date from version order by entry_order;		select entry_order, major, minor, patch, date from version order by entry_order;
	~~</pre>~~
	{{NavBox Hub}}		{{NavBox Hub}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler at 17:18, 27 February 2015

2015-02-27T17:18:18Z

← Older revision		Revision as of 19:18, 27 February 2015
Line 1:		Line 1:
	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.		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.
			<pre>
	/* Query 1 - 5.04.00 Upgrade Check, May 2009, 030: Field Report should have 2 new columns. Should be 2 rows */		/* 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');		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 */		/* 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';		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 */		/* Query 3 - 5.04.00 Upgrade Check, May 2009, 080: 14 tables should have calculated area and linelength added.
	select table_name, column_name from information_schema.columns where column_name ~~In (~~'calculated_area', 'calculated_line_length');		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 */		/* Query 4 - 5.05.00 Upgrade Check, November 2009, 010: Org_guid should be added to 6 tables. Should be 6 rows */
Line 14:		Line 16:

	/* Query 5 - 5.05.00 Upgrade Check, November 2009, 060: Current view and task tables should be added. Should be 10 rows */		/* 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');		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 */		/* Query 6 - 5.05.03 Upgrade Check, March 2010, 010: Check Subtheme and Form Template Permissiosn. Should be 2 rows */
Line 45:		Line 48:
	/* Query 15 - General Upgrade Check: Check Version table for correct entries*/		/* Query 15 - General Upgrade Check: Check Version table for correct entries*/
	select entry_order, major, minor, patch, date from version order by entry_order;		select entry_order, major, minor, patch, date from version order by entry_order;
	{{NavBox ~~Technical Notes~~}}		</pre>
			{{NavBox Hub}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler: Created page with "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..."

2015-02-27T15:48:06Z

Created page with "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..."

New page

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;
{{NavBox Technical Notes}}
[[Category:NoPublic]]
[[Category:NAA]]