While working on the latest conversion, the social status of dissenters, I realized that original data from the people column was being lost during the attempt to append data from customFieldData columns. Tracking backwards, I found similar errors in the doc_notes column from previous conversions, so I've now restored the doc_notes column from an earlier backup, and I'll have to devise another approach to appending data from the customFieldData columns. I've restored the latter to customFieldDataBack, and I'll go back over my code and figure out a better way to do it.

Meanwhile, here's the first (working) half of the status data conversion:

/* First we need to expand the size of the doc_people column to 
   ensure that data copied over from custom fields will fit.
   ALREADY DONE ON LIVE DB.
    */
   
/* ALTER TABLE `documents` MODIFY `doc_people` VARCHAR(1024); */

/* Now we create the tables for dissenter status. */

DROP TABLE IF EXISTS `docs_to_disStatus`;
DROP TABLE IF EXISTS `disStatus`;

CREATE TABLE IF NOT EXISTS `disStatus` (
  `ds_id` int(11) NOT NULL auto_increment,
  `ds_desc` varchar(128) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`ds_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `docs_to_disStatus` (
  `dts_dts_id` int(11) NOT NULL auto_increment,
  `dts_doc_id_fk` int(11) NOT NULL,
  `dts_disStatus_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`dts_dts_id`),
  KEY `dts_news_id_fk_idx` (`dts_disStatus_id_fk`),
  KEY `dts_doc_id_fk_idx` (`dts_doc_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/* Now we create the foreign key constraints for the second table. */

ALTER TABLE `docs_to_disStatus`
  ADD CONSTRAINT `docs_to_disStatus_ibfk_1` FOREIGN KEY (`dts_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `docs_to_disStatus_ibfk_2` FOREIGN KEY (`dts_disStatus_id_fk`) REFERENCES `disStatus` (`ds_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  
/* Now we create the new fields from the old. We use all field types in this case. */
INSERT IGNORE INTO `disStatus` (`ds_desc`)  ( SELECT `cf_name` FROM `customFields` WHERE (`cf_group_1` LIKE "Social status%") ORDER BY `cf_name`);

/* Now we go through copying the data over from the old custom fields. */

/* This is the mapping data:
34  1 	a) Villager
35  2 	b) Village elite
36  3 	c) Social elite (nobility)
88  4 	d) Urban context - lower classes
37  5 	e) Transient
38  6 	f) Woman
114 7 	g) Curé/prêtre
39  8 	h) Other
*/

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "34";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "35";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "36";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "88";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "37";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "38";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "114";

INSERT INTO `docs_to_disStatus` (`dts_disStatus_id_fk`, `dts_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "39";