Adaptive Database

I've been looking at this feature request:

• When you do a search, could the search results table have a sentence at the top which states what the search was? Ie., Search results for Date > 1790 and Document type = DCM and Fausses nouvelles (custom field) = true.

There are two specific problems with it: first of all, there's ALREADY a complete record of the search you made, on the page itself, sitting in the form that you completed. When JW made this request, she didn't realize that, because the form collapses out of the way to leave you with more space on the page for the search results, but one click reveals it again, in full detail. Therefore I think it's basically redundant. The second issue is that creating a prose view of the search (as opposed to the tabular form you fill out) would be complex and therefore time consuming, because much of the information that a human would need to understand it is not actually part of the search that goes to the db. Integer id fields are what the db searches for, and without the human-readable labels that go along with them (and which come from another table), they'd be meaningless. It would be possible to write JavaScript to parse the search form and construct something human-readable by connecting each field with its associated labels and each option with its string value, but in order for this to be reliable, it would require a rewrite of the search form, to supply predictable ids to all of the labels. All in all, this would take many hours.

My conclusion, after looking at this in detail, is that it would be redundant, divert a lot of my time from other more useful features, and would ultimately slow down the responsiveness of the search page.

There is an alternative that might be more effective, though. If, instead of hiding the entire search form when the search is submitted to the server, only those fields which are not part of the search were hidden, there would be a more compact, but still very human-readable, view of the search. This does make sense, although it might be a little confusing to see most, but not all, of the search fields disappear. The hide/show mechanism would have to be more explicit and intuitive than the current cryptic triangle on the search legend.

Fixed a bunch of annoyances with date fields. The most egregious was the requirement to see, and to enter, dates in the format 1790-00-00 in cases where the precision was only to the level of the year, despite the fact that the JavaScript date validation code would mark this as an invalid date because of the zeroes. Partial dates without zeroes are now permitted, and filtering by date also now works properly.

Also realized that the search.php page has never had a working tab system, so you can't look at the search instructions. Hived those off to an include to get them out of the page (they may differ between projects anyway), and then fixed the error. It was due to the tab-handling JavaScript having been updated when the enter.php page was rewritten, changing the form of element ids; I'd failed to update the search.php page at the same time. Nobody noticed this. I guess nobody reads instructions.

I've stripped out the old "progress bar" system, which wasn't working properly anyway, and replaced it with an enhanced version of the "stepDots" functionality that I was already using to signal progress for an AJAX operation. This now appears in yellow over the top of the target element (the element that will be changed by the results of the AJAX request), and it also gives more informative messages. The old code has been stripped out. Tested on four browsers, and working.

I also fixed a display bug in the size of select elements in forms -- they're now the same width as other elements. In non-FF browsers, though, I'm still seeing the left border of the record table bleeding through the edit and view forms when you're working on a record. I really have to figure out what's causing this, and fix it. I think it might be because of the negative left margin I'm setting on the view/edit forms; the underlying table border may be showing through because it's structurally outside the form, so the form can't obscure it, somehow. Most annoying.

There are half a dozen feature requests in the list. Rather than go in order, I'm going to pick the simplest and most urgent ones first, so I'll start with this one:

Some kind of indication of when a 'save' is complete. I know this problem will diminish as we resolve some of the drains on the speed, but it might still be worth looking at. If I make a change to an entry which is visible on the table display (for example, to 'Period' or 'Status'), then I can see when that change appears in the table; it's usually 15-20 seconds or so, not fast. I have tried, before, to start editing another document before that first change "appears", and this seems to mean that the change is erased - the first record reverts back to the way it is. It's not that big a deal (although it's slow) but the problem comes when you make an edit to a field not displayed in the table - you don't know how long to wait before you can edit another record. Just a "Saving... done." would be good. (This isn't very important, as I've just noticed that if I don't move the cursor, I can tell that the save is done because the hourglass changes to a pointer... but if I'm doing anything else at the same time, I can't tell.)

My first thought is that the little timing indicator I use for search and retrieval requests can also be used for this purpose, so I'll try that today.

Now on to feature requests...

Script is ready and tested on dev -- I'll run it on live after backing up this afternoon:

/* First we need to add a new column for the string data.
   We specify NOT NULL so that we can later append data.
   If there is NULL in the field, it proves impossible to 
   append, for some reason. */
   
ALTER TABLE `documents` ADD `doc_authConcernDesc` VARCHAR(4096) NOT NULL;

/* Now we create the tables for dissent topics. 
   I'm using the ac_ prefix for this table. */

DROP TABLE IF EXISTS `docs_to_authConcerns`;
DROP TABLE IF EXISTS `authConcerns`;

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

CREATE TABLE IF NOT EXISTS `docs_to_authConcerns` (
  `dta_dta_id` int(11) NOT NULL auto_increment,
  `dta_doc_id_fk` int(11) NOT NULL,
  `dta_authConcern_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`dta_dta_id`),
  KEY `dta_authConcern_id_fk_idx` (`dta_authConcern_id_fk`),
  KEY `dta_doc_id_fk_idx` (`dta_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_authConcerns`
  ADD CONSTRAINT `docs_to_authConcerns_ibfk_1` FOREIGN KEY (`dta_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `docs_to_authConcerns_ibfk_2` FOREIGN KEY (`dta_authConcern_id_fk`) REFERENCES `authConcerns` (`ac_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 `authConcerns` (`ac_name`)  ( SELECT `cf_name` FROM `customFields` WHERE ((`cf_group_1` LIKE "Attitude towards%") OR (`cf_group_1` LIKE "Perceived intention%")) AND (`cf_id` != "25") ORDER BY `cf_name`);

/*
15 1 Attempt to dissuade/convince
43 2 Bucolic innocence, "it's not in them", etc.
87 3 Easily led astray, focus on speakers instead
18 4 Expressing opinion, just complaining
17 5 Expression of regret, eliciting agreement
66 6 Importance of words vs. actions
60 7 People regarded with contempt, dismissed
16 8 Provocation, moteur in attroupement
111 9 Sedition: Assumption that peasant is not the guilt...
108 10 Sedition: Concern that public opinion might be aff...
107 11 Sedition: Concern with consequences, publicity
106 12 Sedition: Concern with intention, premeditation
105 13 Sedition: Words vs. actions; violence taken more s...
12 14 Spreading of news/rumour
80 15 Terms for instigators

25 is a comments field; data should just be carried over from that.
*/


/* 15, 43, 87, 18, 17, 66, 60, 16, 111, 108, 107, 106, 105, 12, 80, 25 */

/* Booleans: 12, 15, 16, 17, 18, 66, 87, 105, 108  */
/* text:     25, 60                                */
/* strings:  43, 80, 106, 107, 111                 */

/* Booleans first. 12, 15, 16, 17, 18, 66, 87, 105, 108 */
INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "14", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "12" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "15" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "16" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "17" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "18" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "66" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "87" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "13", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "105" AND `cfd_bool` = TRUE;

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "10", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "108" AND `cfd_bool` = TRUE;

/* Now the others. Existence of the record means we should create a boolean. There are no empty records. */

/* Now we create booleans from the other fields where there is any content. */
/* text:     60                                */

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "60";

/* strings:  43, 80, 106, 107, 111  
               */
INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "43";

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "15", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "80";

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "12", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "106";

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "11", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "107";

INSERT INTO `docs_to_authConcerns` (`dta_authConcern_id_fk`, `dta_doc_id_fk`) SELECT "9", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "111";

/* Now we carry over the old values from string fields. */

/* First the longer text. 25, 60 */

  UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "25" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "25") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_text`) > 0) 
        AND NOT (`customFieldData`.`cfd_text` = "Yes.")
  );
  
    UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "60" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "60") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_text`) > 0) 
        AND NOT (`customFieldData`.`cfd_text` = "Yes.")
  );
  
/* Now the strings:  43, 80, 106, 107, 111                 */

  UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "43" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "43") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` = "Yes.")
  );
  
    UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "80" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "80") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` = "Yes.")
  );
  
    UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "106" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "106") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` = "Yes.")
  );
  
    UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "107" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "107") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` = "Yes.")
  );
  
    UPDATE `documents` SET `doc_authConcernDesc` = 
  concat(`doc_authConcernDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "111" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "111") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` = "Yes.")
  );
  
/* Then, we delete the original custom field data. WARNING: IRREVOCABLE! */

DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` IN (15, 43, 87, 18, 17, 66, 60, 16, 111, 108, 107, 106, 105, 12, 80, 25);

/* Finally, we delete the original custom field definitions. WARNING: IRREVOCABLE! */

DELETE FROM `customFields` WHERE `cf_id` IN (15, 43, 87, 18, 17, 66, 60, 16, 111, 108, 107, 106, 105, 12, 80, 25);

This is the last custom field set conversion:

New group composed of remaining fields in 'Attitude towards peasants and politics' (2-10) and 'Perceived intention of dissenting words' (1-6), call this new one-to-many field "Concerns of authorities'. Can we simply paste any of the detailed info contained in any of these fields (ie., not true-false indications), as well as any contents of the 'Comments' field in 'Perceived intention...', into the accompanying text field?

These are the fields in question:

• 15 1 Attempt to dissuade/convince
• 43 2 Bucolic innocence, "it's not in them", etc.
• 87 3 Easily led astray, focus on speakers instead
• 18 4 Expressing opinion, just complaining
• 17 5 Expression of regret, eliciting agreement
• 66 6 Importance of words vs. actions
• 60 7 People regarded with contempt, dismissed
• 16 8 Provocation, moteur in attroupement
• 111 9 Sedition: Assumption that peasant is not the guilt...
• 108 10 Sedition: Concern that public opinion might be aff...
• 107 11 Sedition: Concern with consequences, publicity
• 106 12 Sedition: Concern with intention, premeditation
• 105 13 Sedition: Words vs. actions; violence taken more s...
• 12 14 Spreading of news/rumour
• 80 15 Terms for instigators
• 25 16 Z - Details/comments

On to this tomorrow...

These are the remaining items to convert:

Some groups of custom fields can be converted into a one-to-many + text field

    * Topic of dissent: individual fields can be the items in the one-to-many table; then there needs to be a text field in which the dissent is described (can we call the fields, eg., "topic of dissent" and "topic of dissent: description")?
    * New group composed of remaining fields in 'Attitude towards peasants and politics' (2-10) and 'Perceived intention of dissenting words' (1-6), call this new one-to-many field "Concerns of authorities'. Can we simply paste any of the detailed info contained in any of these fields (ie., not true-false indications), as well as any contents of the 'Comments' field in 'Perceived intention...', into the accompanying text field?

These are the Topics of Dissent fields (all shortstring):

• 62 1 Against the king (AR)
• 49 2 Assignats, maximum, requisitions, taxes, biens nat...
• 89 3 Ideas on politics
• 53 4 Inflammatory statements (vive Louis XVII; ça n'ira...
• 52 5 Local politics
• 54 6 None - just attroupement, incomplete, etc.
• 63 7 Other
• 47 8 Politics (royalism, counter-revolution, etc.)
• 48 9 Prices, subsistences, disette (problems)
• 50 10 Recruitment, war effort incl. news of successes/se...
• 51 11 Religion, incl. Catholics/Protestants

This is the script:

/* First we need to add a new column for the string data.
   We specify NOT NULL so that we can later append data.
   If there is NULL in the field, it proves impossible to 
   append, for some reason. */
   
ALTER TABLE `documents` ADD `doc_disTopicDesc` VARCHAR(4096) NOT NULL;

/* Now we create the tables for dissent topics. 
   I'm using the dc_ prefix for this table, as 
   dt_ is already taken by dissentTypes. */

DROP TABLE IF EXISTS `docs_to_disTopics`;
DROP TABLE IF EXISTS `disTopics`;

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

CREATE TABLE IF NOT EXISTS `docs_to_disTopics` (
  `dtc_dtc_id` int(11) NOT NULL auto_increment,
  `dtc_doc_id_fk` int(11) NOT NULL,
  `dtc_disTopic_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`dtc_dtc_id`),
  KEY `dtc_disTopic_id_fk_idx` (`dtc_disTopic_id_fk`),
  KEY `dtc_doc_id_fk_idx` (`dtc_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_disTopics`
  ADD CONSTRAINT `docs_to_disTopics_ibfk_1` FOREIGN KEY (`dtc_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `docs_to_disTopics_ibfk_2` FOREIGN KEY (`dtc_disTopic_id_fk`) REFERENCES `disTopics` (`dc_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 `disTopics` (`dc_name`)  ( SELECT `cf_name` FROM `customFields` WHERE (`cf_group_1` LIKE "%Topic of dissent%") ORDER BY `cf_name`);

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

/* This is the mapping data:
62  1	Against the king (AR)
49  2	Assignats, maximum, requisitions, taxes, biens nat...
89  3	Ideas on politics
53  4	Inflammatory statements (vive Louis XVII; ça n'ira...
52  5	Local politics
54  6	None - just attroupement, incomplete, etc.
63  7	Other
47  8	Politics (royalism, counter-revolution, etc.)
48  9	Prices, subsistences, disette (problems)
50 10	Recruitment, war effort incl. news of successes/se...
51 11	Religion, incl. Catholics/Protestants
*/

/* (62, 49, 89, 53, 52, 54, 63, 47, 48, 50, 51) */

/* Converting these fields is slightly more complex. Some contain variations on "True" and "Yes", while
   others have string data. Where there is any data, the field is deemed "true"; where the data is not 
   just "True" or "Yes", we need to append the string to the doc_disTopicDesc field in the documents 
   table. */
   
/* First, create the booleans for the one-to-many relationship. */
INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "62";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "49";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "89";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "53";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "52";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "54";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "63";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "47";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "9", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "48";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "10", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "50";

INSERT INTO `docs_to_disTopics` (`dtc_disTopic_id_fk`, `dtc_doc_id_fk`) SELECT "11", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "51";

/* Now copy over the string data. */ 

/* 1 */
 UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "62" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "62") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );
  
/* 2 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "49" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "49") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 3 */  
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "89" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "89") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 4 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "53" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "53") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 5 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "52" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "52") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 6 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "54" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "54") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 7 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "63" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "63") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 8 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "47" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "47") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );
  
/* 9 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "48" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "48") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );
  
/* 10 */
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "50" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "50") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );

/* 11 */  
  UPDATE `documents` SET `doc_disTopicDesc` = 
  concat(`doc_disTopicDesc`, 
         (SELECT `cfd_str` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "51" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`), 
  '\n'
  ) 
  WHERE EXISTS (
      SELECT * FROM `customFieldData` WHERE 
        (`customFieldData`.`cfd_to_cf_id` = "51") 
        AND (`customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`) 
        AND (LENGTH(`customFieldData`.`cfd_str`) > 0) 
        AND NOT (`customFieldData`.`cfd_str` LIKE "True%") 
        AND NOT (`customFieldData`.`cfd_str` LIKE "Yes%")
  );
/* (62, 49, 89, 53, 52, 54, 63, 47, 48, 50, 51) */

/* Then, we delete the original custom field data. WARNING: IRREVOCABLE! */

    DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` IN (62, 49, 89, 53, 52, 54, 63, 47, 48, 50, 51);

/* Finally, we delete the original custom field definitions. WARNING: IRREVOCABLE! */

    DELETE FROM `customFields` WHERE `cf_id` IN (62, 49, 89, 53, 52, 54, 63, 47, 48, 50, 51);

This has now been executed on both dev and live.

Something that's been bugging me for ages is the display of tabs and their captions in the main table view. When wrapping to the next line was required, it would occur in the middle of tabs which had spaces in their captions, but never between tabs -- the reverse of the sensible behaviour. I've modified enter.php so that spaces in captions are replaced with non-breaking spaces, and between each tab, there's a zero-width breaking space, so that wrapping now occurs between tabs.

While adding conversion of \r to <br/> (along with \n, which is already converted) in the View rendering of records, I discovered that the PHP ereg_replace function is now deprecated, so I've switched that line from this:

      $content = stripslashes(ereg_replace("\n", "<br />\n", htmlspecialchars($this->val, ENT_QUOTES, 'UTF-8')));

to this:

      $content = stripslashes(preg_replace("/(\n|\r)/", "<br />\n", htmlspecialchars($this->val, ENT_QUOTES, 'UTF-8')));

There are no other instances of ereg_replace in that file (db_fields.php), but I'll have to trawl through the whole project and see if any others show up.