IMSMA3 script changes 6.0

Importing data to PostgreSQL

If you have imported data to temporary tables, you need to double-check how your data looks like after import to PostgreSQL. There are changes in date format and boolean compared to MySQL.

Configuration files

Compared to IMSMA^NG 5.08.04 these rows are changed:

1. MIGRATION_FROM_TYPE=6.0
2. MIGRATION_TO_TYPE=6.0
3. IMSMA_6.0_JDBC_URL=jdbc:postgresql://localhost:5432/imsma
4. IMSMA_6.0_Driver=org.postgresql.Driver
5. IMSMA_6.0_User=imsma
6. IMSMA_6.0_Password=password

Scripts

SQL syntax

It is strongly recommended to copy all SQL statements to a SQL tool like PGadminIII or NaviCat and test them before running the script in IMSMANG 6.0

Due to PostgreSQL is case sensitive you need to adapt SQL statements by adding " around column names that contains capital case and because of the " you also need to add \ (escape character).
5.08.04 example

SELECT Loc_VillageGUID, mMREID FROM loc_link_mre WHERE loc_link_mre.Loc_VillageGUID IS NOT NULL

needs to be changed to

SELECT \"Loc_VillageGUID\", \"mMREID\" FROM loc_link_mre WHERE \"Loc_VillageGUID\" IS NOT NULL

In PostgreSQL there are other reserved words than in MySQL. In your SQL tool you will see them marked with blue text.

How it looks like in SQL tool before correction

How it looks like in SQL tool after correction

How it looks like in integrated into the script with escape characters

There are more SQL syntax changes between MySQL and PostgreSQL, refer to Difference MySQL PostGreSQL.

Data type Byte

In PostgreSQL the data type Byte has been replaced with Boolean. You find all places you need to change by searching for Byte. 5.08.04 example

Attribute.SetByte("IsActive", Cast.Byte("1"))

needs to be changed to

Attribute.SetBoolean("Active", Cast.Boolean("true"))

Multi-select

If any of the fields that are imported are multi-select then you need to verify that position in cdfvalue is set. Scripts run with 6.0 works better than 5.08.04, see ICR-114. If position is NOT set then the update query in Multi-select CDFs needs to be added as a Data.RunSQLStatement in Migration Cleanup or run in a SQL tool.

Geodata

The column userenteredcoord has been split up in 3 columns

• user_entered_x
• user_entered_y
• user_entered_mgrs

Search for UserEnteredCoord and replace the 5.08.04 syntax with (the row should end with a , or ; depending on the row is inside an IF statement or not)
Attribute.SetString("UserEnteredX", Row.GetString("Longitude"))
Attribute.SetString("UserEnteredY", Row.GetString("Latitude"))
Attribute.SetString("UserEnteredMGRS", Row.GetString("MGRS"))

You also need to verify that the following fields get correct values:

• CoordRefSys
• CoordRefFormat
• UserInputFormat

Key and Object Mapping

One parameter seems to be need to be changed from Object.SetKeyedObjectMapping(Row.GetString("clnGUID"), "Geopoint", Object.CreatePojo("Geopoint")) to Object.SetKeyedObjectMapping("", "GeopointDA", Object.CreatePojo("Geopoint"));

The second parameter is a variable name and I find it easier to read the scripts if the variable have a slightly different name than the third parameter which is the pojo.

Mandatory information

There is no changes in version 6.0 for these fields but there were a change in version 5.08.02 and many existing scripts are not yet updated. I would also recommend to double-check new scripts written by Novetta. Date of Information and Form ID are vital for the function of IMSMANG and should always be filled in.

Verify that the following fields are set and correct in Pojo Fieldreport:

• Date of Information (previously called Date of Report) -> DateOfReport
• Data entry form ID -> LocalId
• Approved Date -> ReportVerifiedDate
• Approver -> VerifiedBy

Verify that the following fields are set and correct in Pojo Xxxinfoversion

• Item ID -> LocalId
• Type of item -> Type
• Status of item -> Status

If you have dates that you would like to use as

• Data Entry Date
• Submitted Date

see Data Entry Form Table which fields to use because the field names are confusing.

Location

In 5.x the Location migration was done in very special way which required post processing with migration scripts running SQL stored functions. This did not work well with PostgreSQL and therefore Location scripts (a.k.a. CityCentric-Domain-GazetteerCity-Location.mtxt) should be re-written for 6.0.

If your script contains Target.Set("Pojo", "Location") then you need to adapt it to 6.0 version and you do not need to:

• call 020_add_new_location_reconciliation_info.sql
• execute Data.RunSQLStatement("update location set isactive = true ") and
• execute Data.RunSQLStatement("update locationinfoversion set manual = false")

in the script Domain-MigrationCleanup.mtxt.

Other clean-up and value setting in Domain-MigrationCleanup.mtxt still needs to be done.

MRE details

The difference between other items and Education which is using the table mredetail is the double reconciliation. In version 6.0 the approach is as e.g. Hazdeviceversion with a loop using SQLSubQuery and Pojo.SetChildren + Pojo.PersistPojo after the loop. The link to the table fieldreport needs to be added in the end of the loop Attribute.SetObject("Fieldreport", Pojo.GetPojoByGuid("Fieldreport", Cast.String(Object.GetKeyedMapping("", "FieldReport"))));

{{#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:imsma3 script changes 6.0

|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:imsma3 script changes 6.0

|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:imsma3 script changes 6.0

|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:imsma3 script changes 6.0

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

}}}}}}