Hoping someone can give me an answer here, even if its not possible.
So here is the scenario, we are moving a Commcare application from one server to another. So in essence from the old server instance to the new server instance.
I am in the process of moving over the Organizational structure and we have run into an issue with regards to the location_id field.
Obviously when trying to upload to the new server, these location_id's dont exist. So when I have the location_id specified in the excel doc for bulk upload I get errors stating the location_id's are not found in your domain. I am aware of how this works, one just needs to blank out the location_id columns in the excel file and then commcareHQ will automatically create the GUIDs and see them as new locations.
However the problem comes in, is that in our ETL/DB we have mapped those location_id's in a table which we use to map reporting info. We are realizing now that we should have used the site_code instead for this mapping as that wouldnt change.
Anyway that being said, is there anyway possible to manually set the location_id GUID when doing a bulk upload or possibly some other work around in CommcareHQ?
@Calvin I'm reasonably certain we don't have any way to manually set the GUID for locations, as that would be risky functionality to have as part of the product (the IDs really must be universally unique).
If there are no locations on the new server, you could try dumping just the locations
If you're not following that method, are you moving to an already-occupied server instance? That isn't a process we've done before, that I'm aware of. There is a risk of the SQL primary keys on the locations being copied over conflicting with those on the new server, since those are incremented integers, not UUIDs.
Thanks Ethan, that's helpful. It seems we might come right there. I'll take a look at the dump and parse / exclude the primary keys. The potential gotcha I can see is that the prior server was out of date by a year so we couldn't do a standard upgrade - I imagine there may be migrations. What we've done is start a new empty project on an up-to-date server and transferred the app across. I'll have a close look and figure it out.
That makes sense. The full dump includes a LOT of things that you might not care about or which you can easily recreate, increasing the possibility of version issues. Luckily the locations module hasn't had any schema changes since March 2021, so that might work out for you. Good luck.
Great! My only other q is whether those UIDs are used anywhere else. I imagine on a new server with a fresh app (and no data yet), not?
Will do a proper scan of all tables / fields to be 100% sure. The dump worked flawlessly on the old box - a real time saver.
Thanks!
The location primary keys are only used in a small handful of places outside of locations, for obscure features that have foreign key references to them. The location UUIDs are used more broadly, depending on how the project is configured. Some cases might be owned by locations, and users might be assigned locations, those would use the UUID for that. Pretty much any config that references locations (case update rules, UCR, etc) might store location UUIDs. You could conceivably have hardcoded location UUID references in the application, though that seems fairly unlikely - site_code would work just as well and be more readable if you encoded logic specific to one location. So, hard to say for sure, but if you didn't copy over much, that seems unlikely.
Thanks Ethan, I think we came right. I did a pg_dumpall, opened the SQL file and searched for UIDs - I confirmed that references to the old UIDs were only in archived forms other than the locations_sqllocation table. Given that it's a new server that only had a few archived test records, I think we're good. Thanks for the help!