Upgrade from 5.08.04 to 6.0 process

Data Quality SQL queries

There are several SQL queries that should be run before starting the upgrade process, see Data quality checks before upgrading to V6.0.

Run V5.08.04 tools - Locale fix

This tool was created for the upgrade to 5.08.04 and addresses old issues. Many countries did not run it and import scripts may have created new records with issues. This tool has to be run before re-calculation the area/length and upgrading to V6.0.

The user-entered format of the coordinates is stored in the database in this format: X | Y | language | country | e.g. 474522|4615907|en|GB| in the column userenteredcoord in the table geopoint.

The locale portion (|en|GB|) of userenteredcoord was added in V5.06.00 but the existing geopoints were not given locale information by the database upgrade functionality. If the locale information was missing, it was added later when field reports were e.g. imported. However, then it was the regional settings of the user doing the import that was used. This created invalid combinations when the data entry was done with other regional settings.

The Locale fix tool will verify that there are no invalid combinations of decimal symbol and locale information in the database. If invalid combinations are stored the tool will try to fix them but there may be combinations that will require SQL updating.

You might need to run the tool with two different regional settings i.e. first with dot as decimal symbol and then with comma as decimal symbol.

Start the tool by clicking on C:\IMSMAng\ext\LocaleUpdater\run.bat.

Click on the Update Database button

Click on the OK button, note that this is the database user imsma, not the application user imsma

If all existing invalid combinations of decimal symbol used and locale information in the database is corrected, the output in the window will look like the above image. Note that MGRS will be listed in the output.

Run V5.08.04 tools - Re-calculation of area/length

The calculations of calculated area (m2) and calculated length (m) have been updated in V5.08.04. Due to that in previous version different formulas were used and anti-clock wise entered polygons resulted in negative square meters the calculated area/length needs to be re-calculated before upgrade to V6.0.

Start the tool by clicking on C:\IMSMAng\ext\CalcAreaMigration\run.bat.

Save the published templates as files

The templates might be created with old version of IMSMA and may contain errors that have been undetected in 5.08.04 but will not be working with V6.0. It is recommended to save the very latest version of all templates ever been used for data entry as files.

Take full backup

Take a full backup and make sure that the country has a separate file backup of the attachments (especially for countries that has GBs of attachments).

Extract MySQL

Use the MySQLtoPGSQL tool which will generate two files:

• Dump.sql and
• Views.sql.

Rename the MySQL dump.sql to e.g. MySQLdump and copy the raw PostGreSQL dump.sql and view.sql to your backup folder.

Restore the raw dump and upgrade

Do not restore GIS database and map data. You need to rebuild the sandbox and by restoring the map date you will copy the ArcGIS 9.3.1-version of imsma.mxd to C:\IMSMAng\server\gis\maps.

Did the backup from the country contain the attachments?

Restore the PostGresSQL dump on a computer with IMSMA V6.0 and scan the log for error messages.

Start the TrayLauncher for starting the upgrade scripts. When the upgrade scripts are done:

1. stop TrayLauncher;
2. start TrayLauncher;
3. take backup.

Note that a database that is already upgrade to V6.0 with one of the earlier RC/Beta might need manual manipulation e.g. Beta 2 to Beta 3.

Templates - Run the image tool

Note that this tool (1 February 2014) still does not work. The work-around is to delete the images, logotypes in either 5.08.04 or 6.0. In 5.08.04 they are easier to see than in V6.0.

File:Upgrade V6 image.png

The tool should be run twice:

• Published templates that are in database;
• Files that are in C:\IMSMAng\client\FormTemplates\MyTemplates.

Note that other errors in templates are not fixed by this tool or by upgrading.

Templates - Checking the templates by publishing them in V6.0

After you have either run the tool or removed the images manually you should test that all published templates are still working by publish them in V6.0. Check the log when you drag them into the designer. This error message in the log means that there is still an image in wrong format in the template: Need to update FFML image to Image object

Other possible issues are:

• the same field more than once in the template;
• value list of multi-select is changed.

Templates - Checking the templates by opening them for data entry

After you have published the templates then open them and check the log when you open them. Scroll through the template and look for any single and multi-select that have default value that does not OK/normal to be set to default.

If you have errors like this or error message for comboboxes, delete the field from the template, drag it in again and publish.

Rebuild sandbox

If you have completed the previous steps, and no errors remain, you may proceed with rebuilding the sandbox. Both the IMSMA database and GIS database have been modified to support e.g. the Assistance object. As a result, the GIS database (sandbox) must be completely rebuilt in IMSMA V6.0 format.

Delete the sandbox folder, start the TrayLauncher, the server and the client.

Reimport map

The base imsma.mxd file has been updated to support e.g. the Assistance object and re-labelling of objects. The map must, be re-imported and it is recommended to upgrade the background map mxd to version 10.1.

Verify that all categories used in your database have a symbol in the Theme Manager in IMSMA NG. Categories without a symbol will be invisible on the map. Adjust imsma.mxd with ArcGIS Desktop and re-import the map. Remember to rename your background map mxd for each re-import.

Verify that the fonts are OK too.

Take backup

Take full backup and save the two backup folders e.g. on NAS.

Check with the countries

• Import scripts

• Import coordinates for one polygon with Excel or shp
  Do their templates and/or procedure work with V6.0 and ArcGIS 10.1?

• Saved searches
  There are many database changes between previous versions and V6.0 and therefore existing saved searches might need to be updated.
  The new saved searches might need to be adopted to the country's terms and process.

• Data entry templates
  There are many database changes between previous versions and V6.0 and therefore existing templates might need to be updated.

• Summary templates
  The summary templates that the countries have developed will be archived and e.g. CDFs needs to be added to the new standard summary templates.

• iReports
  Please note when you upgrade an existing database existing iReports will not automatically be upgraded.

• SQL views
  The SQL views needs to be redone in PostGreSQL and may be redone from scratch or by editing view.sql (output from the extracting step).

• Coordinate system manager
  If they want to use other coordinates systems than WGS 84 and MGRS it has to be specified again.

• Local projections
  Prj files that are not standard ESRI needs to be copied to C:\IMSMAng\server\gis\coordinate systems.

• Server and client settings/configuration
  If they had configured any settings you need to apply them again.

• Users and permissions
  Are there any users that should be inactivated?
  Does existing users have appropriate permissions?

• Export of xml
  If the country was using File – Export function, verify that they understand how to export from Advanced Search.

• Run the new Saved searches
  Discuss the outcome with the countries and if necessary make a plan on how to correct the errors/low data quality.

• Wiki
  Show them where the off-line wiki is stored (C:\IMSMAng\trayLauncher\exe\Kiwix) and copy the latest zim file there.
  Create a link to the on-line wiki.

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}