Entity is a reserved keyword or invalid column name for PostGres/SDE

[conklinbd]

When copying the military features schema to a postgres database the entity column is changed to database.table.entity because the entity field appears to be a reserved keyword.

This is a serious issue, because any service published from the Military Feature schema will not symbolize correctly if it is a hosted service because the change in column name will make the renderer no longer work.

We need to use some technique to prevent this from being a reserved keyword (using an underscore is one option).

---

CSM Update: here are the dependent tasks:

• [x] Rename the column/field in the MilitaryFeatures.gdb from entity to symbolentity
• [x] Determine if renaming this field in the plugin will be sufficient or if this is also hard-coded in Pro
• [x] Rebuild the plugin and get this binary added/included in the Pro 1.2 final install

[jrweakland]

@abouffard and @csmoore - We will need to discuss after UC. There are many waterfall effects from this discovery by Ben. I would like to add this issue to the sprint after UC. Thoughts?

[csmoore]

Are you sure it is a reserved word (&not a copy or other bug)?

http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=Entity http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

Ex. can you rename the field or create a new field with that name?

Once you are sure that this is indeed the issue - we can do whenever you want - it is straightforward once you decide on the new name (datamodel changes like this I would prefer to do sooner rather than later though) we would just need to:

1. Decide on the new field name Entity-> _______

   a. Some choices: "EntityName" "EntityCode" "_Entity" b. If we go with the "_", we might want to change all of the symbol display-related fields to follow the same convention so we could see at a glance which are "symbology/graphic" fields and which are text/label field

2. Change the Datamodel/template geodatabase
3. Rebuild the DictionaryRenderer plugin (64-bit) a. We could even have the plugin still look for old attributes like "Entity" so the old datamodel still worked if you want - just let me know if you want it to work this way
4. (Hardest/longest part/step) Pro Change Request (this name may be hard coded in the Pro dictionary field map UI)

I can provide a test/patch plugin once you decide upon the new name.

We do occasionally have this problem with reserved words, ex. even a common word/field like "Comment" is indeed a reserved word in several RDBMSs - which the utility above correctly finds: http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=Comment

[csmoore]

I forgot to mention, as a very short term workaround, if you just need to view the symbols, you can also create&populate a 20 digit 2525D Symbol ID field: ""sidc". The plugin will also use this attribute if it is present.

If you need SIDCs from existing data, you can run a tool we have for converting symbol attributes to SIDCs or if creating new data, there is a great web tool for exploring symbols/copying SIDCs here: http://explorer.milsymb.net/#/home

[conklinbd]

@csmoore I did look at the sql reserved keywords and it is not present but I was told the behavior was "as designed' because it is reserved. It might be a reserved keyword for SDE or something at a higher level the SQL.

In looking at your above choices, I think EntityCode seems like a great choice, that we we don't need to use . I personally hate in field names if it can be avoided.

I think having it work with Entity and EntityName is a great idea to make it easier to migrate data.

[conklinbd]

Sorry I should add that this is really an issue for 10.4 Server and 1.2 Pro. Not a UC issue, I can work around this issue for UC.

[csmoore]

@jrweakland @abouffard @Dbarnes1 - this was another Pro CR that I just remembered we need to add to the Pro CR list ("Change Well-Known-Attribute in Dictionary Renderer Symbology Page from 'Entity' to 'EntityCode'")

[jrweakland]

@csmoore - Did this issue get assigned a Pro CR # yet?

[csmoore]

Not yet, I was waiting for #133 to be installed in a daily to see if this can be fixed at the plugin level or if this is a hard-coded value in Pro so let me check on that now, I'll also update the original issue description to make the tasks a little clearer.

---

CSM: Update - #133 / CR318680 is still marked in progress so we should revisit this later

[conklinbd]

@csmoore I wanted to point out that I tried this again today with build 4582 and this problem still exists and it makes the layers break when using SDE. This is something we need to fix either on our end or on the Pro side.

[csmoore]

OK, let me try to get a Pro 1.2 Plugin Build environment set up and see if I can change this on the plugin side and/or if there are any other issues building for Pro 1.2.

[csmoore]

We plan to have a new version of the MilitaryOverlay.gdb that updates this field this sprint. The most generic alternate field name I could think for this of is symbolentity in place of entity so I plan to use that (in case anyone feel strongly against this alternate name).

After this GDB change, until the rule engine/plugin gets updated you will need to manually set this field map the first time you set the DictionaryRenderer on the symbology pane (i.e. it won't auto set to "entity" like it does now) but I believe that will be the only impact.

[csmoore]

Just an update - this field has been renamed in the newest gdb/ppk/etc. version at https://github.com/Esri/military-features-data/tree/v.next/data/mil2525d/core_data - this week we need to see what we need to do to get Pro to default to this new symbol field (the other 2 tasks associated with this in the original issue description above).

[csmoore]

Update: we were able to rename this field ourselves in the Pro plugin - however this will be a breaking change for any existing pre-1.2 project and layer files (doesn't seem to crash though).

The symbols will be broken and the only way to get the new "symbolentity" property to appear on existing pre-1.2 DictionaryRenderer layers (because the field won't appear and the symbols will all be "unknown") is to:

1. switch layer symbology to a non-DictionaryRenderer in the symbology pane (ex, Single Symbol) -and-
2. switch layer symbology back to the DictionaryRenderer and re-map "entity" attribute to "symbolentity"
3. Re-save the project/layer

[csmoore]

Rule Engine/Plugin is rebuilt & tested - we just need to coordinate getting this into the Pro 1.2 install (and Server also? not sure if/where they are pulling this)

[csmoore]

This change has been install in Pro 1.2.4664+. I'll give @conklinbd the option to verify with schema at https://github.com/Esri/military-features-data/blob/v.next/data/mil2525d/core_data/gdbs/MilitaryOverlay.gdb.zip (since I'm not sure how to test the original issue that was encountered on SDE) & will close after a few days if still open

[csmoore]

@conklinbd @jrweakland - @jeconley let us know that the templates that show up for Pro 1.2 New Project from Portal have the old schema and broken mil symbol layers and will need updated for 1.2 - any idea where these templates come from and how we update them? For now I'll write an issue in the desktop template repo:

Example: Vehicle Checkpoint

[BobBooth]

They live in the Solution Product Templates group in public ArcGIS Online: http://www.arcgis.com/home/group.html?owner=esri_solutions&title=Solution%20Project%20Templates @lfunkhouser @ACueva should have access to update.

[csmoore]

Closing - remaining dictionary projects/templates that need updated (that we know about anyway) - recorded here: https://github.com/ArcGIS/mil-intel-desktop-solutions-templates/issues/233