IMSMA3 script changes 6.0: Difference between revisions

Latest revision as of 21:23, 26 November 2018

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:

MIGRATION_FROM_TYPE=6.0
MIGRATION_TO_TYPE=6.0
IMSMA_6.0_JDBC_URL=jdbc:postgresql://localhost:5432/imsma
IMSMA_6.0_Driver=org.postgresql.Driver
IMSMA_6.0_User=imsma
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 IMSMA^NG 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

If you have UTM coordinates then you use GetWGS84Longitude for calculating the Longitude/Latitude values:
Attribute.SetDouble("Longitude", Translate.GetWGS84Longitude("UTM42N", Cast.String(Row.GetAttribute("Xcoord")), Cast.String(Row.GetAttribute("Ycoord"))))
Attribute.SetDouble("Latitude", Translate.GetWGS84Latitude("UTM42N", Cast.String(Row.GetAttribute("Xcoord")), Cast.String(Row.GetAttribute("Ycoord"))))

	There is no function that calculates Longitude and Latitude from a MGRS coordinates so that has to be done with e.g. ArcGIS before the import and needs to be part of the indata to the script. But if you have Longitude and Latitude then the MGRS may be calculated with: Attribute.SetString("UserEnteredMGRS", Pojo.GetUserEnteredCoord( Row.GetString("clnLandMarkLongitude"), Row.GetString("clnLandMarkLatitude"), Row.GetString("clnMapCoordinateSystemTC")))

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 IMSMA^NG 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 = 
 }}
}}

}}}}}}

@@ Line 1: / Line 1: @@
 {{TOC right}}
-==Importing data to PostGreSQL==__NOEDITSECTION__
+==Importing data to PostgreSQL==__NOEDITSECTION__
-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.
+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==__NOEDITSECTION__
@@ Line 13: / Line 13: @@
 ==Scripts==__NOEDITSECTION__
-=== SQL syntax===__NOEDITSECTION__
+===SQL syntax===__NOEDITSECTION__
 {{Note | 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 adopt SQL statements by adding " around column names that contains capital case and because of the " you also need to add \ (escape character).
+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).<br/>
 '''5.08.04 example'''
 <pre>
@@ Line 24: / Line 23: @@
 <pre>
 SELECT \"Loc_VillageGUID\", \"mMREID\" FROM loc_link_mre WHERE \"Loc_VillageGUID\" IS NOT NULL
-</pre>
+</pre> <br/>
+In PostgreSQL there are other reserved words than in MySQL. In your SQL tool you will see them marked with blue text. <br/>
-In PostGreSQL there are other reserved word than in MySQL. In your SQL tool you will see them marked with blue text. <br/>
 [[Image:Reserved_word1.png|800px|center]]
 <div align="center">
@@ Line 40: / Line 38: @@
 </div>
-=== Data type Byte===__NOEDITSECTION__
+There are more SQL syntax changes between MySQL and PostgreSQL, refer to '''[[Difference MySQL PostGreSQL]]'''.
-In PostGreSQL the data type Byte has been replaced with Boolean. You find all places you need to change by searching for ''Byte''.
+===Data type Byte===__NOEDITSECTION__
+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'''
 <pre>
-Attribute.SetByte("IsActive"), Cast.Byte("1");
+Attribute.SetByte("IsActive", Cast.Byte("1"))
 </pre>
 '''needs to be changed to'''
 <pre>
-Attribute.SetBoolean("Active"), Cast.Boolean("true");
+Attribute.SetBoolean("Active", Cast.Boolean("true"))
 </pre>
-=== Geodata===__NOEDITSECTION__
+===Multi-select===__NOEDITSECTION__
+{{Note |  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===__NOEDITSECTION__
 The column ''userenteredcoord'' has been split up in 3 columns
 * user_entered_x
@@ Line 56: / Line 58: @@
 * user_entered_mgrs
-Search for ''UserEnteredCoord'' and replace the 5.08.04 syntax with:
+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)  <br/>
-Attribute.SetString("UserEnteredX", Row.GetString("Longitude"));
+Attribute.SetString("UserEnteredX", Row.GetString("Longitude")) <br/>
-Attribute.SetString("UserEnteredY", Row.GetString("Latitude"));
+Attribute.SetString("UserEnteredY", Row.GetString("Latitude")) <br/>
-Attribute.SetString("UserEnteredMGRS", Row.GetString("MGRS"));
+Attribute.SetString("UserEnteredMGRS", Row.GetString("MGRS")) <br/>
 You also need to verify that the following fields get correct values:
@@ Line 65: / Line 67: @@
 * CoordRefFormat
 * UserInputFormat
+If you have UTM coordinates then you use GetWGS84Longitude for calculating the Longitude/Latitude values:<br/>
+Attribute.SetDouble("Longitude", Translate.GetWGS84Longitude("UTM42N", Cast.String(Row.GetAttribute("Xcoord")), Cast.String(Row.GetAttribute("Ycoord"))))<br/>
+Attribute.SetDouble("Latitude", Translate.GetWGS84Latitude("UTM42N", Cast.String(Row.GetAttribute("Xcoord")), Cast.String(Row.GetAttribute("Ycoord"))))	<br/>
+{{Note | There is no function that calculates Longitude and Latitude from a MGRS coordinates so that has to be done with e.g. ArcGIS before the import and needs to be part of the indata to the script. But if you have Longitude and Latitude then the MGRS may be calculated with:
+Attribute.SetString("UserEnteredMGRS", Pojo.GetUserEnteredCoord( Row.GetString("clnLandMarkLongitude"), Row.GetString("clnLandMarkLatitude"), Row.GetString("clnMapCoordinateSystemTC"))) }}
+===Key and Object Mapping===__NOEDITSECTION__
+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"));
+{{Note | 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===__NOEDITSECTION__
+{{Note | 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===__NOEDITSECTION__
+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''.
+{{Note | Other clean-up and value setting in Domain-MigrationCleanup.mtxt '''still''' needs to be done.}}
+===MRE details===__NOEDITSECTION__
+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"))));
+{{NavBox Upgrade}}
+[[Category:NoPublic]]
 [[Category:NAA]]

IMSMA3 script changes 6.0: Difference between revisions

Latest revision as of 21:23, 26 November 2018

Contents

Importing data to PostgreSQL

Configuration files

Scripts

SQL syntax

Data type Byte

Multi-select

Geodata

Key and Object Mapping

Mandatory information

Location

MRE details

Navigation menu