uic-utah / uic-inventory

a public web application to manage the creation, permitting, and payment for underground injection control sites in Utah
1 stars 0 forks source link

Test UIC database editing outside of Pro #260

Closed nathankota closed 6 months ago

nathankota commented 1 year ago

The attribute rules may make data writing from outside of pro an issue. Someone will need to test this.

rsparker-utah commented 1 year ago

I was able to do some initial testing on editing the UIC database outside of ArcPro and wanted to record it here for reference. My goal for this initial test was to insert a new record into the Facilities tables (i.e., UDEQ.UICADMIN.UICFACILITY) using Microsoft SQL Server Management Studio (SSMS). In preparation for adding a new record, I first wanted to make sure I had all of the required data needed to add a new record (specifically an appropriate value for the "Shape" field). To do this, I loaded up my RParker version in the development instance of the database in ArcPro and went through the steps of creating a facility polygon. I then copied all of the associated metadata from this created facility record to use later. I then deleted this created record and closed ArcPro.

Next, I connected to the development instance of the database with SSMS using my RParker development version login credentials. I then used the data I had copied previously in ArcPro and the following SQL statement to insert the data into the Facilities table:

INSERT INTO UDEQ.UICADMIN.UICFACILITY (OBJECTID, GUID, CountyFIPS, NAICSPrimary, FacilityID, FacilityName, FacilityAddress, FacilityCity, FacilityZIP, CreatedOn, ModifiedOn, EditedBy, GlobalID, Shape, CreatedBy)
VALUES (35433, 'D07356A4-0D7D-48C6-B265-3E8595D16BCF', 49003, '0', 'UTU03F95D16BCF', 'Editing Outside of Pro Test', '111 GSL St.', 'Promontory', '84336', '6/7/2023  5:39:50 PM', '6/7/2023  5:39:50 PM', 'SSMS', 'EF449F88-3970-43F1-B30C-7840B84FFE1C', 0x20690000010405000000E07A14AEBBE314410AD7A3F0BA9C5141E07A14AEECE31441666666A64C9C51412085EB5102EB144114AE47D16C9C5141E07A14AE30E9144134333383D69C5141E07A14AEBBE314410AD7A3F0BA9C514101000000020000000001000000FFFFFFFF0000000003, 'SSMS')

The query executed without any errors and the new record was added to the table: image

I then re-opened ArcPro and was able to see the added record in my RParker version of the development instance: image

The one caveat is that, from what I can tell, it looks like it wrote the record to the default development instance database Facility table and not just to my specific RParker version because I am able to see this added polygon when accessing all of the other user connections in ArcPro without having previously run the reconcile and post scripts. This may be ok, but I wanted to mention it since I recall a brief discussion (#230) about us wanting to write to a view or version and not the default table. I'll continue to explore/test, but at this point I'm not sure how to write exclusively to a specific version (at least in SSMS).

steveoh commented 1 year ago

I believe you'll need to write to a versioned view targeting the specific version.

Does the development database have the attribute rules installed?

I'm surprised it let you write to the enterprise managed editor tracking fields.

I wasn't aware of a development db instance.

rsparker-utah commented 1 year ago

I believe you'll need to write to a versioned view targeting the specific version.

Ah, gotcha. Thanks, I will give that a try.

Does the development database have the attribute rules installed?

Yes (at least for the Facility table)

rsparker-utah commented 1 year ago

Following Steve's recommendation, I re-wrote the query to insert into what I think is the view for my RParker version. I followed a similar workflow as listed above:

  1. I loaded up my RParker version in the development instance of the database in ArcPro and went through the steps of creating a facility polygon. I then copied all of the associated metadata from this created facility record to use later. I then deleted this created record and closed ArcPro.

  2. Next, I connected to the development instance of the database with SSMS using my RParker development version login credentials. I then used the data I had copied previously in ArcPro and the following SQL statement to insert the data into the Facilities view (i.e., UICADMIN.UICFacility_evw) table:

    INSERT INTO UDEQ.UICADMIN.UICFacility_evw (OBJECTID, GUID, CountyFIPS, NAICSPrimary, FacilityID, FacilityName, FacilityAddress, FacilityCity, FacilityZIP, CreatedOn, ModifiedOn, EditedBy, GlobalID, Shape, CreatedBy)
    VALUES (35436, '4260D577-F2B4-4950-A263-62C89611D51A', 49003, '0', 'UTU03F9611D51A', 'Editing Outside of Pro Test 2', '200 GSL St.', 'Promontory', '84307', '6/9/2023  8:00:43 PM', '6/9/2023  8:00:43 PM', 'SSMS', 'AF290179-0A52-4AFF-88A6-E82C6AE68ED6', 0x20690000010405000000305C8FC2989D1441E27A14BED49B514190C2F5284997144152B81EC53D9B514160666666EDA21441D8A370FD009B514110AE47E18FA51441703D0A47889B5141305C8FC2989D1441E27A14BED49B514101000000020000000001000000FFFFFFFF0000000003, 'SSMS')
  3. The query executed but displayed four join warnings: image

  4. I then re-opened ArcPro and was able to see the added record in my RParker version of the development instance: image

  5. I then loaded a map with the connection settings for a different user (i.e., PHenze) to see if the newly added record would display and in this case it didn't when the data source was pointed to the PHenze version: image

This approach seems to be more in line with what we're after as a proof of concept.

nathankota commented 1 year ago

This is encouraging. Thanks @rsparker-utah for taking the time to test it. Any ideas how to confirm you were writing to the versioned view? It seems like you were? Are the next steps to confirm that, along with the FacilityID having the attribute rules in place in that dev instance; then test writing to the other tables that would be affected by an app update, along with their attribute rules?

rsparker-utah commented 1 year ago

@nathankota Thanks Nathan, those sound like good next steps. I've spent time inventorying and organizing my thoughts around the specific fields from the app and the corresponding tables/feature class fields that will be need to be written to. I'll proceed with testing writing to the other tables/feature classes that would be affected by an app update. I'll also further explore confirming that I was in fact writing to the versioned view.

rsparker-utah commented 1 year ago

I have some additional information that I wanted to share in regards to confirming writing to the versioned view. I connected to the development instance of the database with SSMS using my RParker development version login credentials and using Steve's recommendation ran SELECT * queries on both the versioned view (i.e., UDEQ.UICADMIN.UICFacility_evw) and the root/default (i.e., UDEQ.UICADMIN.UICFACILITY) Facilities tables. I then compared the results from these two queries: image The versioned view (i.e., UDEQ.UICADMIN.UICFacility_evw) query returned 999 records while the root/default (i.e., UDEQ.UICADMIN.UICFACILITY) query returned 995. This seems to support the idea that my most recent Insert statements and updates wrote just to the view and not to the root/default table. There should be further confirmation after I run the reconcile and post scripts, which I'll try after testing writing to the other tables/feature classes.

nathankota commented 1 year ago

This sounds like good news! Let us know how it goes after the reconcile and post.

rsparker-utah commented 1 year ago

I have another update regarding writing to the UIC GDB from outside of ArcPro. I was able to do some additional testing writing to the following tables/feature classes from my RParker dev connection using SSMS (these are tables/feature classes that have associated data that will be captured from the web app):

UDEQ.UICADMIN.UICContact_evw

INSERT INTO UDEQ.UICADMIN.UICContact_evw (OBJECTID, GUID, ContactName, ContactType, ContactOrganization, ContactMailAddress, ContactMailCity, ContactMailState, ZipCode5, ContactPhone, ContactEmail, Comments, CreatedOn, ModifiedOn, EditedBy, GlobalID, Facility_FK, CreatedBy, SDE_STATE_ID)
VALUES (32827, '545AE730-9399-46F0-AD1F-E4D753DC77D8', 'RP SQL Edit', '1', 'SQL Edit Test', '195 N 1950 W', 'Salt Lake City', 'UT', 84116, '801.536.4298', 'rsparker@utah.gov', 'Test adding a contact from outside Pro using Microsoft SQL Server Management Studio.', '7/5/2023  4:59:37 PM', '7/5/2023  4:59:37 PM', 'SSMS', '194A7EAC-8E2E-48EB-AE22-60760456BAB4', '4260D577-F2B4-4950-A263-62C89611D51A', 'SSMS', 925)

UDEQ.UICADMIN.UICAuthorization_evw

INSERT INTO UDEQ.UICADMIN.UICAuthorization_evw (OBJECTID, GUID, Facility_FK, AuthorizationID, AuthorizationType, OwnerSectorType, StartDate, Comments, CreatedOn, ModifiedOn, EditedBy, GlobalID, CreatedBy, OrderNumber, EDocsNumber, SDE_STATE_ID)
VALUES (29804, '1F073A16-44FC-4061-8DBD-D76704C4F90A', '4260D577-F2B4-4950-A263-62C89611D51A', 'UTU03RA4C4F90A', 'RA', 'SG', '6/9/2023  12:00:00 AM', 'Test adding a authorization from outside Pro using Microsoft SQL Server Management Studio.', '7/5/2023  5:09:01 PM', '7/5/2023  5:09:01 PM', 'SSMS', '169B9108-596B-4FF6-84C3-DE5F1F323206', 'SSMS', '5555555', 'DWQ-2023-000000', 927)

UDEQ.UICADMIN.UICAuthorizationAction_evw

INSERT INTO UDEQ.UICADMIN.UICAuthorizationAction_evw (OBJECTID, GUID, Authorization_FK, AuthorizationActionType, AuthorizationActionDate, Comments, CreatedOn, ModifiedOn, EditedBy, GlobalID, CreatedBy, SDE_STATE_ID)
VALUES (28620, '63C685B3-26B7-46CB-80A2-DF2FE91797E4', '1F073A16-44FC-4061-8DBD-D76704C4F90A', 'NR', '6/9/2023  12:00:00 AM', 'Test adding a authorization action from outside Pro using Microsoft SQL Server Management Studio.', '7/5/2023  5:16:38 PM', '7/5/2023  5:16:38 PM', 'SSMS', '01A5DB84-DD21-460F-8D20-5DA6756D15D1', 'SSMS', 929)

UDEQ.UICADMIN.UICWell_evw

INSERT INTO UDEQ.UICADMIN.UICWell_evw (OBJECTID, GUID, Facility_FK, Authorization_FK, WellID, WellName, WellClass, WellSubClass, HighPriority, WellSWPZ, InjectionAquiferExempt, Comments, CreatedOn, ModifiedOn, EditedBy, GlobalID, Shape, NoMigrationPetStatus, CreatedBy, SDE_STATE_ID)
VALUES (38744, '7DB301D2-0A35-4AA7-B164-FEA0D1F20D0D', '4260D577-F2B4-4950-A263-62C89611D51A', '1F073A16-44FC-4061-8DBD-D76704C4F90A', 'UTU035D1F20D0D', 'Well Editing View Test', '5', 5047, 'N', 'U', 'N', 'Test adding a well from outside Pro using Microsoft SQL Server Management Studio.', '7/5/2023  5:19:47 PM', '7/5/2023  5:19:47 PM', 'SSMS', 'A19DF2B5-6071-44D2-BB9A-D0339FD9A282', 0x20690000010CE07A14AEE99D1441AE47E1AAB89B5141, 'NA', 'SSMS', 932)

UDEQ.UICADMIN.UICWellOperatingStatus_evw

INSERT INTO UDEQ.UICADMIN.UICWellOperatingStatus_evw (OBJECTID, GUID, Well_FK, OperatingStatusType, OperatingStatusDate, Comments, CreatedOn, ModifiedOn, EditedBy, GlobalID, CreatedBy, SDE_STATE_ID)
VALUES (40034, '70C771AE-E939-4DFA-85DB-F0F087CDEBAE', '7DB301D2-0A35-4AA7-B164-FEA0D1F20D0D', 'PR', '6/9/2023  12:00:00 AM', 'Test adding a well operating status from outside Pro using Microsoft SQL Server Management Studio.', '7/5/2023  5:39:34 PM', '7/5/2023  5:39:34 PM', 'SSMS', 'BC671039-5FCC-4840-B305-EF3CBFBC847E', 'SSMS', 934)

After running the above the queries, I loaded ArcPro and was able to see all the data I added within my RParker version. I then confirmed that the data was only written to my version by checking another version (i.e., PHenze) and confirming that the data was not showing up in the PHenze version.

I then ran the reconcile and post (R&P) scripts on the dev instance of the database. After the R&P scripts completed, I checked both my RParker and PHenze versions and verified that each version was able to see the data from the above queries. This seemed to work fine and as expected; however, I re-ran the SELECT queries on both the versioned view (i.e., UDEQ.UICADMIN.UICFacility_evw) and the root/default (i.e., UDEQ.UICADMIN.UICFACILITY) Facilities tables expecting the record counts to matchup after the R&P scripts, but they did not: image The versioned view (i.e., UDEQ.UICADMIN.UICFacility_evw) query returned 1000 records while the root/default (i.e., UDEQ.UICADMIN.UICFACILITY) query returned 995. I had thought that running the R&P scripts would update both UDEQ.UICADMIN.UICFacility_evw and UDEQ.UICADMIN.UICFACILITY such that running the SELECT queries would return the same results. I am not sure why the SELECT * queries are not returning the same results after the R&P scripts. Perhaps I'm not fully understanding the relationship between the UDEQ.UICADMIN.UICFACILITY and UDEQ.UICADMIN.UICFacility_evw entities.

nathankota commented 1 year ago

bump @rsparker-utah have you done any additional research on this?

nathankota commented 1 year ago

bump @rsparker-utah have you done any additional research on this?

nathankota commented 9 months ago

@rsparker-utah what are the next steps to get this solved, hopefully within the next two weeks?

rsparker-utah commented 9 months ago

@nathankota The six record discrepancy between the versioned view (i.e., UDEQ.UICADMIN.UICFacility_evw) and root/default table (i.e., UDEQ.UICADMIN.UICFACILITY) seem to all be recently created records. image I just noticed it appears like these six records have a different "SDE_STATE_ID" value compared to records that are present in the root/default table (most records have a "SDE_STATE_ID" value of 0). I'm not entirely sure what this field represents, but I can look into this more to see if this has something to do with the record discrepancy: image I'll also double check my reconcile & post (R&P) scripts for the dev ernvironment to see if anything looks amiss and then run them again when I do this week's R&P process. The thing that is throwing me off is that one of the records (i.e., ObjectID 35433) seems to have transferred just fine, but the other six records did not. Three of these six records were created before the ObjectID 35433 record and three were created after which only adds to my confusion (why didn't they all transfer). I'm hoping further review in the above areas will help us find a clear path forward, otherwise I'm at a loss of where to explore next.

rsparker-utah commented 9 months ago

I was able to do some additional testing writing just the data captured by the web app (per #284 ) to the following tables/feature classes from my RParker dev connection using SSMS. I first tried writing to the Facility table:

UDEQ.UICADMIN.UICFacility_evw

INSERT INTO UDEQ.UICADMIN.UICFacility_evw (NAICSPrimary, NAICSSecondary, FacilityID, FacilityName, FacilityAddress, FacilityCity, FacilityZIP, Shape)
VALUES ('621210', 'Dentists offices (e.g., centers, clinics)', 'UTU49S5ED51B7D', '240126 SSMS Test', '123 Street', 'Salt Lake City', '84116', 0x20690000010405000000E07A14AE2CA6144100000090A094514110AE47E157A7144152B81EC555945141D0A3703D7CAC144152B81EA5829451416066666672A914415C8FC2F5C5945141E07A14AE2CA6144100000090A094514101000000020000000001000000FFFFFFFF0000000003)

After running this query I received the following error indicating the need for a "GlobalID" field: image I then updated my query to include a GlobalID field:

INSERT INTO UDEQ.UICADMIN.UICFacility_evw (NAICSPrimary, NAICSSecondary, FacilityID, FacilityName, FacilityAddress, FacilityCity, FacilityZIP, GlobalID, Shape)
VALUES ('621210','Dentists offices (e.g., centers, clinics)', 'UTU49S5ED51B7D','240126 SSMS Test','123 Street', 'Salt Lake City','84116', '1111A11A-1A11-1A11-AA11-1AAA1111AAA1', 0x20690000010405000000E07A14AE2CA6144100000090A094514110AE47E157A7144152B81EC555945141D0A3703D7CAC144152B81EA5829451416066666672A914415C8FC2F5C5945141E07A14AE2CA6144100000090A094514101000000020000000001000000FFFFFFFF0000000003)

I re-ran the code and it update successfully: image

I opened ArcPro and was able to view the new site; however, when selecting the site, the CountyFIPS attribute rule triggered: image

Manually populating the CountyFIPS field removed the AR error; however, the following fields that are usually auto calculated by ArcPro were all null:

In summary, for the UIC.Facility feature class the following seven fields are not currently accounted for:

I ran out of time today, but I'll plan to run similar tests on the other tables that the app writes to (i.e., UICAuthorization, UICContact, UICWell, and UICWellOperatingStatus). I'll be making edits to the following app to GDB crosswalk spreadsheet to track what I find through this process. As a side note, I tried running the reconcile and post scripts on the dev UIC geodatabase instance again to if the data would be present in the UDEQ.UICADMIN.UICFACILITY, but unfortunately it still was not and will need some further exploration.

rsparker-utah commented 9 months ago

Testing continued:

UDEQ.UICADMIN.UICContact_evw

The database seems to require a GlobalID for all records, so I included one in my insert query:

INSERT INTO UDEQ.UICADMIN.UICContact_evw (ContactName, ContactEmail, ContactPhone, ContactOrganization, ContactType, ContactMailAddress, ContactMailCity, ContactMailState, ZipCode5, GlobalID)
VALUES ('John Doe', 'jdoe@utah.gov', '801.888.8888', 'Testers Inc.', 2 , '123 Test St.', 'Salt Lake City', 'UT', '84116', '1111B11B-1B11-1B11-BB11-1BBB1111BBB1')

The insert statement was successful: image

I opened ArcPro and was able to view the new contact record. No attribute rules trigger when selecting this record; however, the following fields that are usually auto calculated by ArcPro were all null:

In summary, for the UIC.Contact table the following seven fields are not currently accounted for:

UDEQ.UICADMIN.UICWell_evw

A couple quick notes about my query for the UICWell feature class:

INSERT INTO UDEQ.UICADMIN.UICWell_evw (WellClass, WellSubClass, WellName, RemediationProjectID, RemediationProjectType, Comments, HighPriority, WellSWPZ, Shape, GlobalID)
VALUES (5,5002, '240126 Test SER Well', '240126 Rem', 5, 'Well represents 2 injection points', 2, 'N', 0x20690000010CB047E17A1BA814410AD7A35073945141, '1111C11C-1C11-1C11-CC11-1CCC1111CCC1')

The insert statement was successful: image

I opened ArcPro and was able to view the new well; however, when selecting the well, the InjectionAquiferExempt attribute rule triggered: image

Manually populating the InjectionAquiferExempt field removed the AR error; however, the following fields that are usually auto calculated by ArcPro were all null:

In summary, for the UIC.Well feature class (assuming we default the WellClass field to Class V, and HighPriority field to No) the following ten fields are not currently accounted for:

UDEQ.UICADMIN.UICWellOperatingStatus

The database seems to require a GlobalID for all records, so I included one in my insert query:

INSERT INTO UDEQ.UICADMIN.UICWellOperatingStatus_evw (OperatingStatusType, OperatingStatusDate, GlobalID)
VALUES ('PR', '2024-01-26 13:00:00', '1111D11D-1D11-1D11-DD11-1DDD1111DDD1')

The insert statement was successful: image

I opened ArcPro and was able to view the new operating status record. No attribute rule warnings triggered; however, the following fields that are usually auto calculated by ArcPro were all null:

In summary, for the UIC.Well feature class (assuming we default the WellClass field to Class V, and HighPriority field to No) the following seven fields are not currently accounted for:

UDEQ.UICADMIN.UICAuthorization

A couple of quick notes about my query for the UICAuthorization table:

INSERT INTO UDEQ.UICADMIN.UICAuthorization_evw (OwnerSectorType, OrderNumber, StartDate, EDocsNumber, AuthorizationType, GlobalID)
VALUES ('SG', '1234567', '2024-01-26 14:00:00', 'DWQ-2024-999999', 'RA', '1111E11E-1E11-1E11-EE11-1EEE1111EEE1')

The insert statement was successful: image

I opened ArcPro and was able to view the new authorization record. No attribute rule warnings triggered; however, the following fields that are usually auto calculated by ArcPro were all null:

In summary, for the UIC.Authorization table (assuming we default the "AuthorizationType" field to "RA" (which translate to "Authorization by Rule" in the database) the following eight fields are not currently accounted for:

UDEQ.UICADMIN.UICAuthorizationAction

A couple of quick notes about my query for the UICAuthorizationAction table:

INSERT INTO UDEQ.UICADMIN.UICAuthorizationAction_evw (AuthorizationActionType, AuthorizationActionDate, GlobalID)
VALUES ('NR', '2024-01-26 14:30:00', '1111F11F-1F11-1F11-FF11-1FFF1111FFF1')

The insert statement was successful: image

I opened ArcPro and was able to view the new authorization action record; however, an attribute rule triggered: image

I'm not 100% certain, but I suspect this rule is being trigger due to the lack of a GUID: image

In addition to this warning, the following fields that are usually auto calculated by ArcPro were all null:

In summary, for the UIC.AuthorizationAction table (assuming we default the "AuthorizationActionType" field to "NR" (which translate to "Permit Not Required" in the database and populate the "AuthorizationActionDate" from the date the inventory was approved in the web app) the following seven fields are not currently accounted for:

UDEQ.UICADMIN.UICWellSWPZ_evw

I started to test the UICWellSWPZ table but noticed no Well_FK field was created to relate the source water protection zone (SWPZ), so I think I'll need to review that and get it sorted out first. That being said, looking at the table as it currently is, it looks like it has the "SystemName", "ContactName", and "ContactEmail" to match the values for "Water System Information Name", "Water System Information Contact", and "Water System Information Email" from the web app. Similarly to the other tables, it looks like the following fields aren't accounted for:

Other Testing Notes I've read several articles regarding versioning as well as reconciling and posting versions, but ufortunately, I am still no closer to understanding why some of the records inserted using SQL are not showing up in their respective feature classes/tables after running the reconcile and post scripts.

steveoh commented 9 months ago

I skimmed this and it doesn't sound like a viable solution for getting application data into the UIC database. Does that seem accurate?

rsparker-utah commented 9 months ago

@steveoh Yes, I would agree with that assessment.

nathankota commented 6 months ago

closing since this was not a viable solution