split_container_indicator_1.sql

# This edits the archivesspace mysql database to pull out indicator_1 vales in the 
# container table that have "XX.YY" values.
# the indicator_1 and indicator_2 values are updated to be indicator_1 = XX and
# indicator_2 = YY, with a type_1 = box and type_2 = folder.
# all other container values are copied over as is. 
# Containers with indicator_1 that do not have "." in them are left as-is. 

# AS ALWAYS!!! MAKE A BACKUP OF YOUR DB BEFORE YOU DO THIS AND THEN CHECK IF THIS
# DID WHAT YOU WANTED!!!

DROP TEMPORARY TABLE IF EXISTS split_container;
CREATE TEMPORARY TABLE split_container AS (
	SELECT 
	 c.id,
	 c.lock_version,
	 c.json_schema_version,
	 c.instance_id,
	 ev1.id as type_1_id, 
	 SUBSTRING_INDEX( indicator_1, ".", 1 ) as indicator_1,
	 c.barcode_1,
	 ev2.id as type_2_id,
	 SUBSTRING_INDEX( indicator_1, ".", -1 ) as indicator_2,
	c.type_3_id,
	c.indicator_3,	 
	 c.container_extent,
	 c.container_extent_type_id,
	 c.created_by,
	 c.last_modified_by,
	 c.create_time,
	 c.system_mtime,
	 c.user_mtime
	FROM container c
	JOIN enumeration_value ev1 ON ev1.value = "box"
	JOIN enumeration_value ev2 ON ev2.value = "folder"
	WHERE c.indicator_1  LIKE "%.%"
);

REPLACE INTO container SELECT * from split_container;

split_container_indicator_2.sql

#
# This adds a fix for people using locations ( which you'll get a foreign key error if
# you use the above gist 
#
# This edits the archivesspace mysql database to pull out indicator_1 vales in the 
# container table that have "XX-YY" values.
# the indicator_1 and indicator_2 values are updated to be indicator_1 = XX and
# indicator_2 = YY, with a type_1 = box and type_2 = folder.
# all other container values are copied over as is. 
# Containers with indicator_1 that do not have "-" in them are left as-is. 

# AS ALWAYS!!! MAKE A BACKUP OF YOUR DB BEFORE YOU DO THIS AND THEN CHECK IF THIS
# DID WHAT YOU WANTED!!!
SET FOREIGN_KEY_CHECKS=0;
DROP TEMPORARY TABLE IF EXISTS split_container;
CREATE TEMPORARY TABLE split_container AS (
	SELECT 
	 c.id,
	 c.lock_version,
	 c.json_schema_version,
	 c.instance_id,
	 ev1.id as type_1_id, 
	 SUBSTRING_INDEX( indicator_1, "-", 1 ) as indicator_1,
	 c.barcode_1,
	 ev2.id as type_2_id,
	 SUBSTRING_INDEX( indicator_1, "-", -1 ) as indicator_2,
	c.type_3_id,
	c.indicator_3,	 
	 c.container_extent,
	 c.container_extent_type_id,
	 c.created_by,
	 c.last_modified_by,
	 c.create_time,
	 c.system_mtime,
	 c.user_mtime
	FROM container c
	JOIN enumeration e ON e.name = 'container_type'
	JOIN enumeration_value ev1 ON ev1.value = "box" AND ev1.enumeration_id = e.id
	JOIN enumeration_value ev2 ON ev2.value = "folder" AND ev1.enumeration_id = e.id
	WHERE c.indicator_1  LIKE "%-%"
);

REPLACE INTO container SELECT * from split_container;
SET FOREIGN_KEY_CHECKS=1;