We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."
Aside from adding the additional status in the XML, I see two paths to achieve this:

1.  Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.
2.  Use a PSQL UPDATE statement to alter the value in all of the object records.
   I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that. James D. Keeline

I have successfully added a value on the option list of "imported" in the UI by changing this file:
~/cspace_source/application/tomcat-main/src/main/resources/tenants/sdchm/sdchm-other-dimension.xml
This required making a new sdchm-other-dimension.xml file in the tenants/sdchm/ directory and copying over the relevant items from the master copy at:
~/cspace_source/application/tomcat-main/src/main/resources/defaults/base-other-dimension.xml_____
Now I wish to UPDATE the records if I can.  
Exploring the PostgreSQL nuxeo_default database, I see that the present values for recordstatus are empty for all 2000+ records:
\l\c nuxeo_default\dtSELECT id, recordstatus FROM collectionobjects_common LIMIT 40;
                  id                  | recordstatus --------------------------------------+-------------- aeda19fe-6d7c-4046-8768-502b96520b8f |  bdbefae4-2034-49ad-8336-9462277a3a2e |  af945345-9f06-48f1-9d18-1eac74c72792 |  d31a2b67-c4ea-498e-bd82-55cd63882d1c |  c21815ca-fadb-43fc-b089-b45a786d10a6 |  91e6a0e7-de38-4926-a75d-ea7ca2f6a872 |  8da4b632-721c-4f30-b82f-0dfb63258566 |  ba9be6ad-e843-4f57-907c-1539819b59a0 |  6fb1ae37-8d77-432f-bd0d-59cbb43c8f8e |  a445838e-a094-4e20-b634-678852ee9800 |  8b91bdca-f70c-4647-b392-3351e78cc625 |  6c392956-1729-48a6-8328-8f7a990a494f |  f5787fff-9a91-42f8-99c0-819755d58dd8 |  a504fff1-8770-4e5d-a6e3-560c9ac830c3 |  1736dbb8-c703-48cd-a708-a7ed3560f75f |  b78dda13-6bcd-42df-aa7e-b44c8bc308a4 |  04311d8a-df1d-4307-beb6-b0ce24272618 |  0970207b-e583-496f-9a7d-89590374f95d |  78297344-0c63-4680-96ed-a5faed1312c7 |  3f5f6329-759e-4145-a5c0-2bd11d86dee0 |  c3a1abfb-fa66-445a-b546-709f097122aa |  42b61649-9b54-4b4e-9521-63e41106e701 |  e7172551-669d-4ddc-bc65-19b939a3ba16 |  b750b94a-12e8-4abd-973c-10809443120c |  62949411-39ef-4a38-93cf-47f9e26b8782 |  640da20b-2b31-42d8-87c6-a23593262caa |  304ac81f-d9a4-4349-b00c-5cf3da2271ea |  8e0478d8-68b4-48c4-94a2-87dfdebcafc5 |  c8f327cd-7c03-4764-be8c-e3622743a757 |  cc185fe5-6474-43c6-8b35-1cdf3fd06e2f |  7828a108-83f4-43dd-a362-88d793b0010f |  d0a7eda4-2bde-4242-ad3f-f9ed06a47ec9 |  efb4b6d1-0111-4f57-a5f0-b87914bb72be |  369b0680-0682-4609-9178-3077d9565122 |  7e2c3d30-52db-45bc-a3e9-8601feaeadb5 |  73e809c5-c55b-4b58-adff-ee17b4410e0e |  778df5c3-0c32-4d30-86d4-00f009bdd772 |  14bc9a17-146b-4c09-ac6c-01747ff4bcad |  5b7ddc95-04ec-4ae7-88ef-859d6ca912db |  d085d436-75d2-4daa-a330-8a177529b0cd | (40 rows)
Is this the value to change with UPDATE and can I simply add the value imported per my change to the available options?  
If there is another way/place to do this, please let me know. James D. Keeline

  From: James Keeline <james@keeline.com>

To: Talk talk@lists.collectionspace.org
Sent: Thursday, June 23, 2016 3:10 PM
Subject: [Talk] Update "Record Status" for all objects to "In Process"

We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."
Aside from adding the additional status in the XML, I see two paths to achieve this:

1.  Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.
2.  Use a PSQL UPDATE statement to alter the value in all of the object records.
   I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that. James D. Keeline

Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

James,

In response to your questions below, given that you're setting up a new deployment and may need to reimport things again and have relatively few records, I'd strongly recommend updating your import XML files and import everything again into an empty system rather than trying to patch things with SQL statements.

Until you're into production, it's probably best to tweak and improve your import scripts and process rather than trying to patch things with SQL statements.

-Richard

From: Talk talk-bounces@lists.collectionspace.org on behalf of James Keeline james@keeline.com
Sent: Thursday, June 23, 2016 3:10 PM
To: Talk
Subject: [Talk] Update "Record Status" for all objects to "In Process"

We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."

Aside from adding the additional status in the XML, I see two paths to achieve this:

1. Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.

2. Use a PSQL UPDATE statement to alter the value in all of the object records.

I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that.

James D. Keeline

As I have mentioned before, then, I will need a safe way to delete the objects before I reimport.  I do not wish to have two entries for each object. James D. Keeline

  From: Richard Millet <richard.millet@lyrasis.org>

To: James Keeline james@keeline.com
Cc: Talk talk@lists.collectionspace.org
Sent: Thursday, June 23, 2016 9:03 PM
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

James,

In response to your questions below, given that you're setting up a new deployment and may need to reimport things again and have relatively few records, I'd strongly recommend updating your import XML files and import everything again into an empty system rather than trying to patch things with SQL statements.

Until you're into production, it's probably best to tweak and improve your import scripts and process rather than trying to patch things with SQL statements.

-Richard

From: Talk talk-bounces@lists.collectionspace.org on behalf of James Keeline james@keeline.com
Sent: Thursday, June 23, 2016 3:10 PM
To: Talk
Subject: [Talk] Update "Record Status" for all objects to "In Process"

We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."

Aside from adding the additional status in the XML, I see two paths to achieve this:

1.  Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.

2.  Use a PSQL UPDATE statement to alter the value in all of the object records.

I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that.

James D. Keeline

James,

Apologies for not being more explicit.  I think I've mentioned that it is very common in your situation (data migration to a new deployment) for people to follow a cycle of:

1. Prepare data and import scripts/processes
2. Import data into empty/fresh database.
3. Show imported to client/user to gather feedback.
4. Tweak and fix data and import scripts
5. Drop/clear previous database
6. Repeat steps 2 through 5 until you think you're ready to go live.

Does that make sense?  Usually direct SQL updates take place only once you're in production.

-Richard

From: James Keeline james@keeline.com
Sent: Thursday, June 23, 2016 9:47 PM
To: Richard Millet
Cc: Talk
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

As I have mentioned before, then, I will need a safe way to delete the objects before I reimport.  I do not wish to have two entries for each object.

James D. Keeline

From: Richard Millet richard.millet@lyrasis.org
To: James Keeline james@keeline.com
Cc: Talk talk@lists.collectionspace.org
Sent: Thursday, June 23, 2016 9:03 PM
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

James,

In response to your questions below, given that you're setting up a new deployment and may need to reimport things again and have relatively few records, I'd strongly recommend updating your import XML files and import everything again into an empty system rather than trying to patch things with SQL statements.

Until you're into production, it's probably best to tweak and improve your import scripts and process rather than trying to patch things with SQL statements.

-Richard

From: Talk <talk-bounces@lists.collectionspace.orgmailto:talk-bounces@lists.collectionspace.org> on behalf of James Keeline <james@keeline.commailto:james@keeline.com>
Sent: Thursday, June 23, 2016 3:10 PM
To: Talk
Subject: [Talk] Update "Record Status" for all objects to "In Process"

We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."

Aside from adding the additional status in the XML, I see two paths to achieve this:

1. Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.

2. Use a PSQL UPDATE statement to alter the value in all of the object records.

I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that.

James D. Keeline

It seems unwise to make such a large-scale change on the day of a museum demo.  Perhaps afterwards we can do that.  There will doubtless be tweaks they desire and this can be part of the wrap up before hand over.
The generic process is helpful and what I thought it should be.  However, the details of "drop the database" and "create a fresh one" are the areas that need to be specific to be sure it is done correctly.  Otherwise we run around in circles trying to figure out why an install script doesn't work or a feature is not functional. James D. Keeline

  From: Richard Millet <richard.millet@lyrasis.org>

To: James Keeline james@keeline.com
Cc: Talk talk@lists.collectionspace.org
Sent: Friday, June 24, 2016 8:31 AM
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

James,

Apologies for not being more explicit.  I think I've mentioned that it is very common in your situation (data migration to a new deployment) for people to follow a cycle of:

1. Prepare data and import scripts/processes
2. Import data into empty/fresh database.
3. Show imported to client/user to gather feedback.
4. Tweak and fix data and import scripts
5. Drop/clear previous database
6. Repeat steps 2 through 5 until you think you're ready to go live.

Does that make sense?  Usually direct SQL updates take place only once you're in production.

-Richard

From: James Keeline james@keeline.com
Sent: Thursday, June 23, 2016 9:47 PM
To: Richard Millet
Cc: Talk
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

As I have mentioned before, then, I will need a safe way to delete the objects before I reimport.  I do not wish to have two entries for each object.

James D. Keeline

From: Richard Millet richard.millet@lyrasis.org
To: James Keeline james@keeline.com
Cc: Talk talk@lists.collectionspace.org
Sent: Thursday, June 23, 2016 9:03 PM
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

James,

In response to your questions below, given that you're setting up a new deployment and may need to reimport things again and have relatively few records, I'd strongly recommend updating your import XML files and import everything again into an empty system rather than trying to patch things with SQL statements.

Until you're into production, it's probably best to tweak and improve your import scripts and process rather than trying to patch things with SQL statements.

-Richard

From: Talk <talk-bounces@lists.collectionspace.orgmailto:talk-bounces@lists.collectionspace.org> on behalf of James Keeline <james@keeline.commailto:james@keeline.com>
Sent: Thursday, June 23, 2016 3:10 PM
To: Talk
Subject: [Talk] Update "Record Status" for all objects to "In Process"

We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."

Aside from adding the additional status in the XML, I see two paths to achieve this:

1.  Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.

2.  Use a PSQL UPDATE statement to alter the value in all of the object records.

I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that.

James D. Keeline

James,

Here is some info that I hope informs your decisions:

When CollectionSpace starts up, it creates SQL statements in the following file for dropping all the CollectionSpace related databases and roles:

tomcat/cspace/services/db/postgres/init_nuxeo_db.sql

It also creates SQL statements for dropping the AUTHN/AUTHZ database and role in the following file:

tomcat/cspace/services/db/postgres/init_cspace_db.sql

You can use the SQL statements in these files to "reset" the system before performing a data migration import.

-Richard

p.s. The 'ant create_db -Drecreate_db=true' command invokes the ALL statements in these two files.  If you don't want to lose existing AUTHN/AUTHZ information (user accounts and roles), then just manually execute the 'init_nuxeo_db.sql' file.  For example, 'psql -U csadmin -f init_nuxeo_db.sql -d postgres' drops all the non-AUTHN/AUTHZ data from the system.  The next time to restart CollectionSpace, it will recreate the databases and they will be empty.

From: James Keeline james@keeline.com
Sent: Friday, June 24, 2016 8:43 AM
To: Richard Millet
Cc: Talk
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

It seems unwise to make such a large-scale change on the day of a museum demo.  Perhaps afterwards we can do that.  There will doubtless be tweaks they desire and this can be part of the wrap up before hand over.

The generic process is helpful and what I thought it should be.  However, the details of "drop the database" and "create a fresh one" are the areas that need to be specific to be sure it is done correctly.  Otherwise we run around in circles trying to figure out why an install script doesn't work or a feature is not functional.

James D. Keeline

From: Richard Millet richard.millet@lyrasis.org
To: James Keeline james@keeline.com
Cc: Talk talk@lists.collectionspace.org
Sent: Friday, June 24, 2016 8:31 AM
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

James,

Apologies for not being more explicit.  I think I've mentioned that it is very common in your situation (data migration to a new deployment) for people to follow a cycle of:

1. Prepare data and import scripts/processes
2. Import data into empty/fresh database.
3. Show imported to client/user to gather feedback.
4. Tweak and fix data and import scripts
5. Drop/clear previous database
6. Repeat steps 2 through 5 until you think you're ready to go live.

Does that make sense?  Usually direct SQL updates take place only once you're in production.

-Richard

From: James Keeline <james@keeline.commailto:james@keeline.com>
Sent: Thursday, June 23, 2016 9:47 PM
To: Richard Millet
Cc: Talk
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

As I have mentioned before, then, I will need a safe way to delete the objects before I reimport.  I do not wish to have two entries for each object.

James D. Keeline

From: Richard Millet <richard.millet@lyrasis.orgmailto:richard.millet@lyrasis.org>
To: James Keeline <james@keeline.commailto:james@keeline.com>
Cc: Talk <talk@lists.collectionspace.orgmailto:talk@lists.collectionspace.org>
Sent: Thursday, June 23, 2016 9:03 PM
Subject: Re: [Talk] Update "Record Status" for all objects to "In Process"

James,

In response to your questions below, given that you're setting up a new deployment and may need to reimport things again and have relatively few records, I'd strongly recommend updating your import XML files and import everything again into an empty system rather than trying to patch things with SQL statements.

Until you're into production, it's probably best to tweak and improve your import scripts and process rather than trying to patch things with SQL statements.

-Richard

From: Talk <talk-bounces@lists.collectionspace.orgmailto:talk-bounces@lists.collectionspace.org<mailto:talk-bounces@lists.collectionspace.orgmailto:talk-bounces@lists.collectionspace.org>> on behalf of James Keeline <james@keeline.commailto:james@keeline.com<mailto:james@keeline.commailto:james@keeline.com>>

Sent: Thursday, June 23, 2016 3:10 PM
To: Talk
Subject: [Talk] Update "Record Status" for all objects to "In Process"

We have imported about 2,000 objects via import service to the SDCHM installation of CollectionSpace 4.3.  Upon feedback from the director, we would like to change the Record Status of these to either a new value of "imported" or an existing value of "in process".  At present they have the value of "approved."

Aside from adding the additional status in the XML, I see two paths to achieve this:

1. Delete the existing object records, adjust the XML payload to reflect the correct value for Record Status (recordStatus), import the XML payload.

2. Use a PSQL UPDATE statement to alter the value in all of the object records.

I would appreciate advice on the SQL part of these processes.  If one is superior to the other, let me know of that.

James D. Keeline