Updated the live site using yesterday's script. Seems to be working fine. The responsiveness of the site keeps improving as we reduce the number of custom fields. Also updated my copy_live_to_dev_db.sql data copying script (which is used to clear out the dev db and repopulate it with the latest data from the live site).
August 24, 2010
August 23, 2010
String fields containing \r (return, inserted as such through SQL) don't get <br/> tags when viewing the record, although they do get returns when editing. I think my return conversion code in the PHP must be missing specific types of return.
These custom fields had cfd_text which needed to be appended to doc_notes:
- 32
- 77
- 81
- 83
This had cfd_text which needed to be appended to doc_people:
- 39
These custom fields had cfd_str which needed to be appended to doc_people:
- 34
- 35
- 36
- 37
- 38
- 88
It appears that the truncation happens when appending a NULL value onto any string field, so this kind of statement will actually work where the previous formulations failed:
UPDATE `documents` SET `doc_people` = concat(`doc_people`, ' \r ', (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "39" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`)) WHERE EXISTS (SELECT * FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "39" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id` AND LENGTH(`customFieldData`.`cfd_text`) > 0);
So I worked from a reconstructed copy of the old customFieldData table, and reworked my scripts to append the data successfully. After testing on the dev db, I made the same changes to the live db for the four doc_notes items. I'm now proceeding with the disStatus script. I'll update my previous posts to include corrected scripts.
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";
... that's been eluding me for ages. When adding the first value in a one-to-many field, the delete button for the new field would be wrapped to the next line, most annoyingly (and confusingly). This turned out to be due to a CSS padding value being cascaded down to a pair of <span>s. Fixed now.
Starting work on this one:
"Social status of individuals involved in dissent - rename to 'Social status of dissenters', can this be placed just before the 'people' regular field, and any detail in any of these fields pasted in to that 'People' field?"
Conversion done on both dev and live, using this final script:
/* First, deal with the oddity which belongs in Dissent Types. */
INSERT INTO `disTypes` (`dt_name`) VALUES("Oui-dire");
INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "15", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "82" AND `cfd_bool` = TRUE;
DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` = "82";
DELETE FROM `customFields` WHERE `cf_id` = "82";
/* Now the bulk of the conversion. */
DROP TABLE IF EXISTS `news`;
DROP TABLE IF EXISTS `docs_to_news`;
/* Next, we recreate them: the news first, because the
second table has a key into it. */
CREATE TABLE IF NOT EXISTS `news` (
`nw_id` int(11) NOT NULL auto_increment,
`nw_desc` varchar(128) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`nw_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `docs_to_news` (
`dtn_dtn_id` int(11) NOT NULL auto_increment,
`dtn_doc_id_fk` int(11) NOT NULL,
`dtn_news_id_fk` int(11) NOT NULL,
PRIMARY KEY (`dtn_dtn_id`),
KEY `dtn_news_id_fk_idx` (`dtn_news_id_fk`),
KEY `dtn_doc_id_fk_idx` (`dtn_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_news`
ADD CONSTRAINT `docs_to_news_ibfk_1` FOREIGN KEY (`dtn_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `docs_to_news_ibfk_2` FOREIGN KEY (`dtn_news_id_fk`) REFERENCES `news` (`nw_id`) ON DELETE CASCADE ON UPDATE CASCADE;
/* Now we create the new fields from the old. */
INSERT IGNORE INTO `news` (`nw_desc`) ( SELECT `cf_name` FROM `customFields` WHERE (`cf_group_1` LIKE "%cial communication%") AND (`cf_to_fieldTypes_id` = "101") ORDER BY `cf_name`);
/* Now we go through copying the data over from the old custom fields. */
/* This is the mapping data:
103 1 Evidence of people asking for news, interest in po...
29 2 Importance of communicating to peasants
86 3 importance of the role of the curé
31 4 Information on Intendant-subdelegate network
59 5 Knowledge through affiches
85 6 Local dialects (langue vulgaire, etc.)
104 7 Official news is a catalyst
94 8 Parler des "affaires du temps" or "de la Révol."
91 9 Peasants discussing the news
30 10 Piétons; method of getting to villages
20 11 Presence of newspapers
109 12 Sedition: how to counteract seditious words
97 13 Spreading of news, from neighbouring villages etc.
92 14 Te Deums
93 15 Trust or distrust of written material
79 16 Valet de ville, tambour, announcer
32 string field
83 string field
*/
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "103" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "29" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "86" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "31" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "59" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "85" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "104" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "94" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "9", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "91" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "10", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "30" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "11", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "20" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "12", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "109" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "13", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "97" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "14", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "92" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "15", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "93" AND `cfd_bool` = TRUE;
INSERT INTO `docs_to_news` (`dtn_news_id_fk`, `dtn_doc_id_fk`) SELECT "16", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "79" AND `cfd_bool` = TRUE;
/* Now the Z - Description/comments fields (# 32 and #83). This data has to be added to the end of the notes field. */
UPDATE `documents` SET `doc_notes` = concat(`doc_notes`, '\r\r', (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "32" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`)) WHERE EXISTS (SELECT * FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "32" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id` AND LENGTH(`customFieldData`.`cfd_text`) > 0);
UPDATE `documents` SET `doc_notes` = concat(`doc_notes`, '\r\r', (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "83" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`)) WHERE EXISTS (SELECT * FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "83" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id` AND LENGTH(`customFieldData`.`cfd_text`) > 0);
/* Then, we delete the original custom field data. WARNING: IRREVOCABLE! */
DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` IN (20, 29, 30, 31, 32, 59, 79, 83, 85, 86, 91, 92, 93, 94, 97, 103, 104, 109);
/* Finally, we delete the original custom field definitions. WARNING: IRREVOCABLE! */
DELETE FROM `customFields` WHERE `cf_id` IN (20, 29, 30, 31, 32, 59, 79, 83, 85, 86, 91, 92, 93, 94, 97, 103, 104, 109);
Realized that my assumption that the existence of a boolean field in the customFieldData table meant that it would be set to true (based on my understanding of how JW was using those fields) was not quite true; in fact there is a small handful of fields in which a boolean has been set to false. I've gone back through the data affected by my previous changes, and found three records which need to be fixed, so I'm going to fix those manually in both the live and dev dbs. Future scripts will take account of this by explicitly testing for true when converting values from custom fields to one-to-many fields.
August 20, 2010
Now working on this slightly more complicated bit:
Official communication (rename: News and Information) - contains all topics in that group and topics 2-6 from "Non-Official Communication" group (1st one to join 'Types of dissent, above). For both groups, please add any material in 'Comments' text field to general notes field. There are a lot of options in these two groups; once I start working with the new structure, I'll see if some can be combined or eliminated. Right now, with the database running so slowly and without the multi-record edit, it's very time-consuming to combine data.
These are the fields from Official Communication:
- 20 bool: Presence of newspapers
- 29 bool: Importance of communicating to peasants
- 30 bool: Piétons; method of getting to villages
- 31 bool: Information on Intendant-subdelegate network
- 59 bool: Knowledge through affiches
- 79 bool: Valet de ville, tambour, announcer
- 85 bool: Local dialects (langue vulgaire, etc.)
- 86 bool: importance of the role of the curé
- 92 bool: Te Deums
- 93 bool: Trust or distrust of written material
- 104 bool: Official news is a catalyst
- 109 bool: Sedition: how to counteract seditious words
- 32 longText: Z - Details/comments
These are the ones from Non-official communication:
- 82 bool: Oui-dire bool:
- 91 bool: Peasants discussing the news
- 94 bool: Parler des "affaires du temps" or "de la Révol."
- 97 bool: Spreading of news, from neighbouring villages etc.
- 103 bool: Evidence of people asking for news, interest in politics
- 83 longText: Z - Description/comments
The first of these is to be moved to "Types of dissent", which I've done with the following script, along with constructing the required tables and populating the news table. The main conversion will be completed on Monday.
/* First, deal with the oddity which belongs in Dissent Types. */
INSERT INTO `disTypes` (`dt_name`) VALUES("Oui-dire");
INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "15", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "82";
DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` = "82";
DELETE FROM `customFields` WHERE `cf_id` = "82";
/* Now the bulk of the conversion. */
DROP TABLE IF EXISTS `news`;
DROP TABLE IF EXISTS `docs_to_news`;
/* Next, we recreate them: the news first, because the
second table has a key into it. */
CREATE TABLE IF NOT EXISTS `news` (
`nw_id` int(11) NOT NULL auto_increment,
`nw_desc` varchar(128) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`nw_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `docs_to_news` (
`dtn_dtn_id` int(11) NOT NULL auto_increment,
`dtn_doc_id_fk` int(11) NOT NULL,
`dtn_news_id_fk` int(11) NOT NULL,
PRIMARY KEY (`dtn_dtn_id`),
KEY `dtn_news_id_fk_idx` (`dtn_news_id_fk`),
KEY `dtn_doc_id_fk_idx` (`dtn_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_news`
ADD CONSTRAINT `docs_to_news_ibfk_1` FOREIGN KEY (`dtn_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `docs_to_news_ibfk_2` FOREIGN KEY (`dtn_news_id_fk`) REFERENCES `news` (`nw_id`) ON DELETE CASCADE ON UPDATE CASCADE;
/* Now we create the new fields from the old. */
INSERT IGNORE INTO `news` (`nw_desc`) ( SELECT `cf_name` FROM `customFields` WHERE (`cf_group_1` LIKE "%cial communication%") AND (`cf_to_fieldTypes_id` = "101") ORDER BY `cf_name`);
The Places of meeting & discussion custom field group is the next to be converted to a one-to-many fieldI'vee scripted this entire conversion, based on what was done with the previous one. These are the preliminary details:
- There are 11 fields, 10 boolean and one longText. They're numbered 67 through 77, and the longtext is the last one.
- We need a
meetPlacestable, withmp_idandmp_desc. - We need a
docs_to_meetPlacestable, withdtm_dtm_id,dtm_doc_id_fk, anddtm_meetPlace_id_fk, the last two foreign keys into the table above and thedocumentstable.
This script was used to accomplish the change:
/* First, we drop the two new tables, in case they've been converted before. */ DROP TABLE IF EXISTS `meetPlaces`; DROP TABLE IF EXISTS `docs_to_meetPlaces`; /* Next, we recreate them: the meetPlaces first, because the second table has a key into it. */ CREATE TABLE IF NOT EXISTS `meetPlaces` ( `mp_id` int(11) NOT NULL auto_increment, `mp_desc` varchar(128) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`mp_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `docs_to_meetPlaces` ( `dtm_dtm_id` int(11) NOT NULL auto_increment, `dtm_doc_id_fk` int(11) NOT NULL, `dtm_meetPlace_id_fk` int(11) NOT NULL, PRIMARY KEY (`dtm_dtm_id`), KEY `dtm_meetPlace_id_fk_idx` (`dtm_meetPlace_id_fk`), KEY `dtm_doc_id_fk_idx` (`dtm_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_meetPlaces` ADD CONSTRAINT `docs_to_meetPlaces_ibfk_1` FOREIGN KEY (`dtm_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `docs_to_meetPlaces_ibfk_2` FOREIGN KEY (`dtm_meetPlace_id_fk`) REFERENCES `meetPlaces` (`mp_id`) ON DELETE CASCADE ON UPDATE CASCADE; /* Now we create the new fields from the old. */ INSERT IGNORE INTO `meetPlaces` (`mp_desc`) ( SELECT `cf_name` FROM `customFields` WHERE (`cf_group_1` LIKE "Places of meeting%") AND (`cf_to_fieldTypes_id` = "101") ORDER BY `cf_name`); /* Now we go through copying the data over from the old custom fields. */ /* This is the mapping data: 70 1 After church / in front of church / on place publi... 71 2 During civic assembly, reading of laws etc. 67 3 Foire/marché; chef-lieu, market town 69 4 In church 76 5 Other 73 6 Place of work (fields, artisan's shop) 72 7 Political assembly (mun. council, electoral, pop. ... 75 8 Private home or space 74 9 Roads, streets 68 10 Taverne/cabaret/auberge 77 (string field). */ INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "70"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "71"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "67"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "69"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "76"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "73"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "72"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "75"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "9", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "74"; INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "10", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "68"; /* Now the Z - Description/comments field (# 81). This data has to be added to the end of the notes field. */ UPDATE `documents` SET `doc_notes` = concat(`doc_notes`, '\r\r', (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "77" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`)) WHERE EXISTS (SELECT * FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "77" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id` AND LENGTH(`customFieldData`.`cfd_text`) > 0); /* Then, we delete the original custom field data. WARNING: IRREVOCABLE! */ DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` IN (67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77); /* Finally, we delete the original custom field definitions. WARNING: IRREVOCABLE! */ DELETE FROM `customFields` WHERE `cf_id` IN (67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77);