Adaptive Database

Added some more detailed comments to db_fields.php, as part of the long process to document this stuff properly for Doxygen.

By trial and error, I've learned that you need to install the following packages to get Doxygen to produce Latex PDF output:

• TeX Live: Basic LaTeX packages (texlive-latex-base)
• TeX Live: TeX and Outline font utilities (texlive-font-utils)
• TeX Live: LaTeX recommended packages (texlive-latex-recommended)
• TexLive: Recommended fonts (texlive-fonts-recommended)
• graphviz

Now, after running Doxygen, you can run make in the latex directory and it will build a PDF.

I'm in the process of adding serious documentation to the whole codebase, for use with Doxygen, and I've been working on the db_fields.php file for a while. Did another hour today. It's a grim slog, but necessary. There are 13 classes and 81 functions to document in this file alone...

A user on the MoM project reported an odd problem with Chrome, which is also the case with Safari: when adding a one-to-many <select> element to add a new one-to-many link in a field, the newly-added <select> element does not appear. It seems to be hidden by default. This seems to be an outright bug in webkit, but pretty obscure, so I figured out a workaround: if I add the <br/> element that follows the <select> and its <button> OUTSIDE their containing span rather than INSIDE it, the appropriate refresh/update seems to take place and the <select> and its delete button appear. Can't see any bad side-effects from this, so I've migrated the change to the live and dev sites, as well as the two Properties and Kings sites, and the Siberian site.

I've added back-end handling for an orderBy parameter submitted to the search and spreadsheet routines, that sort the results according to the specified field. However, this works only partially with the normal table view, because many of the fields contain ids rather than sortable strings, so it will behave a bit unexpectedly from the user's point of view, because the sort order based on the ids will not match that of the visible strings by which they're represented in the results table.

I've come to the conclusion that the current state of this database (and the probable state of any database in which the content expert has complete control over custom fields, and an interest in exploring various approaches to the data) is such that sophisticated searching with flexible sorting and spreadsheet output can only be achieved by building a VIEW of the core table, incorporating all the related and custom fields in the form of either text, integers or dates. The only integer fields would be actual number fields (such as ids, counts, totals etc.), while all lookup and one-to-many fields would be converted into text fields containing their looked-up and amalgamated content. Searching such a table would mean that all fields could be treated as equal, and searching/sorting would be much faster.

It seems to me that the MdhRecord object should be able to spit out two blocks of code automatically, in order to help set up this system:

• A block of SQL which can be used to create the VIEW in the first place.
• A new MdhRecord object which models the data as it appears in the VIEW.

The former will be quite complicated, but I think the latter should be relatively straightforward. There are two outstanding issues:

• Should the VIEW-generation code be written in such a way that once it's been created, it's able to stay current (meaning that if, for instance, the user deletes or adds a custom field, the VIEW would automatically accommodate this); or should it attempt only to model one stable condition of the data?
• Should the MdhRecord object be generated on-the-fly when required (which is slow, but would accommodate on-the-fly changes), or should it also be re-generated when required?

It's worth considering that this view and its associated search ought really to be the public face of the db (not exposing any editing capabilities), so it might be acceptable for it to be static and to require updating when the db structure changes.

I have the output for the spreadsheet working, and it works fine for regular tables, but when there are custom fields and the result set is large, the process takes so long it just ends up timing out.

I'm already disposing of each object once I've finished with it; this solves the out-of-memory errors I was initially seeing, and if I run top on Lettuce, I don't see any inordinate memory usage from my script. However, with a large record set result (my test has 307 hits, and is showing a set of four regular and six custom fields), the process inevitably times out; sometimes I get an error, and sometimes an incomplete file.

As always, the cost of supporting custom fields proves to be insanely large. I'm thinking that it might be possible to reduce the overall timing and memory requirements of the operation in these ways:

• Instead of storing all the results in a single variable and then sending them out in one go, I can echo them steadily out, which would reduce some of the memory load and keep the connection alive. This gives me an incomplete file when the request times out, but what I have up to that point is good.
• Instead of doing the full query to fill the initial dataset, I could do a simpler query to get back just the id numbers of the hits, and then do a separate MdhRecord operation to fill and output each record separately. Combined with the above, we'd then have a long sequence of separate operations rather than one big one.
• I could investigate the possibility that PHP has support for streaming, and use a stream class to output my data.

I've now created a file called get_search_as_spreadsheet.php, which currently just contains a mini-csv file. Used this to test that the mime-type is working as expected; the data is indeed recognized as a spreadsheet and opened by OpenOffice. Now we have to write the actual output for the spreadsheet, as a method of MdhRecordList.

My first difficult major objective is now achieved: custom fields, like other fields in the table, now have "Show" checkboxes next to them, and you can select them and have them show up in the table of search results just like regular fields.

This is quite a costly operation, because it means you have to retrieve all the custom field data from the database for each regular record that you're retrieving, but I've done my best to mitigate this by making sure it only happens if it's needed; you only retrieve the custom field data if you've elected to display at least one custom field, and if you aren't displaying any, even if you're using one or more as a search parameter, they still aren't retrieved. Nevertheless, I wish it would go a bit faster. It's difficult to know how that could be done, though, without hand-optimizing SQL union queries, which undermines the class-based structure of my PHP objects. Since custom fields are only used in JW's project, though, it's not too crucial.

Now I can move on to the requirement for spreadsheet output.

These are the steps, and my progress through them:

• DONE: Start with MdhRecordList, and ensure that nothing can set the value of $parentSuffix to anything except an empty string. Test thoroughly.
• DONE: Next, move to MdhRecord, and ensure that nothing can set its $parentSuffix to anything but an empty string. Test again.
• DONE: If all is well, eliminate all uses of $parentSuffix from MdhRecordList and test again. This will leave the class making calls to MdhRecord methods where it supplies an empty string in place of its $parentSuffix.
• DONE: Identify those methods in MdhRecord, and refactor them so that they don't require that parameter; then remove it from all calls to the methods. Test very thoroughly.
• DONE: Remove all remaining code from MdhRecordList which references $parentSuffix, and test again.
• DONE: Remove all code referencing $parentSuffix from MdhRecord, and test again.
• DONE: Examine gui.js. This has functions which look for the parent suffix and use it (although I believe it's always an empty string) to build ids for form elements and querystring parameters. Remove all references to it, and test again.
• DONE: Look at get_rec.php and save_rec.php to see if mentions of suffixes and ids there can now be simplified.
• DONE: Test test test test...

I've finished the second of the test pages, which tests the functionality of the search page. In the process, I discovered that my code for hiding unused search fields during a search, to keep the page size under control, was not working properly, so I fixed that.

One issue with the testing is that it really works much more straightforwardly if items are sorted by id -- that makes it easier to (for instance) use JavaScript to delete the last added record. The AdaptiveDB items are not sorted by id, so for testing I've changed that setting. I have to remember to avoid migrating that change to the main db. However, this makes for a larger issue, I think: if the Search page becomes the de facto interface for most work, once the project is under way and there's significant data in the db, then we need to think more seriously about what happens when you have a search result set, but you then add a new record which doesn't match the search. I think it should probably be added to the end of the table anyway. I need to look at that.

But first, I need to follow my own instructions in the earlier post to eliminate the rest of the embedded record code from the codebase.

The first test page is now finished. It tests the following functions:

• Retrieve an initial set of five records.
• Retrieve a view of the first, then hide it.
• Retrieve an edit form for the second, modify all the select elements, and save it.
• Get a new blank record, fill in all the text areas, and then submit it.
• Delete the newly-added record.
• Offer to move on to the search test page.

I'm now working on the search test page.

The plan outlined in the previous post requires detailed testing at each stage of the process, and right now that would have to be done by hand, which is very tedious and error-prone, so I've started working on a test page which is designed to run all the key operations. This took a long time to get started, because initially I was trying to call pages which are designed as AJAX response routines inline in the page; that doesn't work because allow_url_include is false (and can't be changed in php.ini with our current setup). I then tried using curl to get the content, but that failed too, possibly because I'm using https. Rather than try to figure that out, I decided to use JavaScript to run all the functions, meaning that I'd actually be testing the AJAX components at the same time.

I now have a page which is doing some of the work -- retrieving a table of data, retrieving a view of the first item, and retrieving an edit form for the second item. Next I need to make it pull down a form for a new item, copy all the data from the existing record to that item, then change the existing data, submit the form, and retrieve a view of the changed item. Then perhaps it should create the new item, check it's there, copy its data back to the old one, etc.

I also need to incorporate search, and probably filtering too, to make sure that these core functions work. With luck, I'll end up with a library that runs its tests in less than two minutes, with clear results. That will make the process of pruning out the cruft much easier and quicker, and also help with developing future functionality.

I've made some significant progress with this, but now I'm getting down to the parts where I have obsolete properties entangled with similar live properties in method signatures and code blocks. I've managed to eliminate three of the four obsolete properties from MdhRecordList: $isEmbedded, $parentId, and $parentIdField. These are hardly used at all by other classes, so eliminating them was simply a local issue, with a few exceptions.

The remaining property is $parentSuffix, and this is problematic because it's passed to any child MdhRecord objects, and referenced throughout the code of both classes, although I don't believe it's actually used at all any more. This is what I believe to be the case, after my first reading of the code and comments:

• I don't believe any of the current code makes use of the MdhRecordList::parentSuffix property at all; it's set to an empty string and never changed.
• However, it is invoked in many locations in the code. Where it occurs in direct functions writing out XHTML, I believe it could simply be eliminated.
• It is also passed to any child MdhRecord objects, where it is assigned to MdhRecord::parentSuffix. Inside the MdhRecord class this is frequently invoked. Generally it is chained together with MdhRecord::localSuffix, a property which is used, and is essential, to ensure that individual forms and form elements have unique id attributes.
• The Adaptive Fields library adaptive_fields.php uses a property with the same name, but I don't believe there's any direct connection between the two.
• When MdhRecord uses a suffix, it's usually done by calling its getSuffix() method, which chains together parentSuffix and localSuffix. In current usage, since the parentSuffix is always set to an empty string, I believe only the localSuffix is ever written out.

Given this, I think this might be a suitably cautious approach to eliminating this complication:

• Start with MdhRecordList, and ensure that nothing can set the value of $parentSuffix to anything except an empty string. Test thoroughly.
• Next, move to MdhRecord, and ensure that nothing can set its $parentSuffix to anything but an empty string. Test again.
• If all is well, eliminate all uses of $parentSuffix from MdhRecordList and test again. This will leave the class making calls to MdhRecord methods where it supplies an empty string in place of its $parentSuffix.
• Identify those methods in MdhRecord, and refactor them so that they don't require that parameter; then remove it from all calls to the methods. Test very thoroughly.
• Remove all remaining code from MdhRecordList which references $parentSuffix, and test again.
• Remove all code referencing $parentSuffix from MdhRecord, and test again.
• Examine gui.js. This has functions which look for the parent suffix and use it (although I believe it's always an empty string) to build ids for form elements and querystring parameters. Remove all references to it, and test again.
• Look at get_rec.php and save_rec.php to see if mentions of suffixes and ids therecan now be simplified.
• Test test test test...

The AdaptiveDB code is full of cruft that originated in a previous attempt to do something similar to what the one-to-many field does now. That code has its fingers everywhere, so I've started the process of trying to clean it out. It's a bit messy because some of the principles (and variable names) used in that code were also used in the adaptive/custom fields, so I have to be really careful what I do. I've got rid of the originating MdhRelTableField class from db_fields.php, and some associated code blocks in other files, and I'm now working on some properties of MdhRecordList that were designed to make it embeddable in another record. The result of all this will be a cleaner, faster codebase which is easier to understand, but it's slow going. I want to get this done before I start on the larger task of making the adaptive fields displayable in the db table.

I also revised a bit of code in the MdhDateField class so that the null year "0000" is never displayed -- now, if there's no date, you see nothing.

Note to self on something I noticed while testing:

I need to revisit the logic that is used when handling edits to records retrieved from a search operation. In particular, if you add a record, but that record doesn't have fields matching the search results you previously retrieved, it doesn't display, of course. It's difficult to see how it could, but it's a bit confusing. Similarly, if you delete a single record which is the result of a search operation, the back-end retrieves a whole set of records as if you'd run an empty search; it should just remove the record from the results table, I think. This needs careful planning, though.

Continuing the process of learning how to turn my regular comments into proper Doxygen comments. I'm still working on the db_fields.php file, which is substantial and has multiple classes in it. I've also been trying to work out how to get PDF documentation out of Doxygen; it generates a Latex project with a Makefile, but when I run Make on the directory, it fails to find the command pdflatex. I can't find a package by that name, or any package which is supposed to contain it, so I'm installing as many core Latex-related packages as I can find. We shall see. Maybe nobody uses the PDF output any more, but it would be handy for e-readers and printed output.

Figuring it was time to start proper documentation of this codebase, I've started with the db_fields.php file, and worked out how to use @file and @defgroup to start documenting the constants at the head of the file. This will be a long job, but it's a good time to do it because it'll help me to prune out old code.

In the current system, when you do a search and retrieve a table of results, you can choose which fields you want to display in the results table. This is very useful for targetted searches, and largely precludes the need for retrieving individual records to examine their fields. However, it doesn't function for custom fields. You cannot specify that you want custom fields to appear in the results table; you can only examine them by retrieving each complete individual record.

This has been a failing for quite a while, and it's been ignorable up to this point, but we're now thinking about creating spreadsheet output from searches as well as the web-page tables. Custom fields must be includable in spreadsheet output, since they can't be retrieved later and merged into the spreadsheet.

So before I start the spreadsheet work, I need to add the capability to retrieve custom field data and display it as part of the search results table. This will not be simple, and there will be a considerable penalty in terms of time, so I need to figure out if there's an efficient way to do it based on SQL joins. The problem is that at the moment, I do the search and get back one row for each record; but there are arbitrary numbers of custom fields associated with any given record, so using a join will get me potentially multiple rows for each custom field data record associated with the document record. I've been re-reading all the custom field code, which is quite complicated, to try and get a handle on how best to do this.

It's going to take some hard thinking.

This involved rewriting a bunch of scripts, to avoid copying over credentials files and .svn directories, but it should all be automated in future. The live db is now using the latest revision.

Both JW and JS-R want output that can be loaded or pasted into a spreadsheet, so that's my next priority. This is how I envisage doing it:

• The current Search button will be accompanied by a second button, for retrieving a CSV file. Perhaps the caption of Search should be changed to something else too, so it's clear that they both search, but they retrieve different things.
• The second button should submit the same search, to the get_search_results.php file, but with a different reqType parameter ('csv' as opposed to 'ajax').
• get_search_results.php reads that parameter and sends the appropriate mime type header; then proceeds as normal, except that it ignores the firstRec and recsToDisplay parameters (CSV searches retrieve the entire set that matches the search), and instead of calling MdhRecordList::writeAsTable, it calls a writeAsCSV method (yet to be coded).

Before I start, I need to figure out all the rules and gotchas for CSV files which can have large text fields, as well as embedded quotes, and I need to determine how to write text in UTF-8 and ensure that spreadsheet programs can read UTF-8 CSV files.

I have a basic system working for hiding/showing rows of the search table that have actually been used. Its only current limitation is that adaptive field groups are still displayed, even if none of their rows are showing, but to hide/show them as well would be complicated and probably not worth the trouble since only one project uses them.

The code is now being managed in SVN (revision.tapor.uvic.ca).

EDIT: svn on the server is working now -- partly my fault, partly an Ubuntu bug with Gnome keyring. I've populated it, but I now need to move over my working copy from the local svn to the server one.

Because there are so many projects using AdaptiveDB code, in various versions, I've decided to manage the project with SVN. Initially, I asked for a repository on our SVN server (revision.tapor.uvic.ca, which is actually Lettuce), but for various reasons that isn't working properly yet; it's there, but I don't seem to have permission to check anything out or make directories. In the meantime, I set up subversion on the local machine and started working with that.

The primary requirement was to have the mysql credentials stored in a location outside all the code files. I've done that by moving them to a directory structure like this:

• Credentials
  • editor
    • creds.php
  • reader
    • creds.php

One or other of these files is require_onced at the head of any file that needs to make connections, ahead of the connect.php file, which I've now moved to /db/. The credentials files can be excluded from the repo so that they're not overwritten by a checkout.

I've tested the new structure with the adaptiveDBdev db, and it all seems to be working fine. I'll have to migrate the other projects over to the new structure carefully, as well as updating their own dev-to-live rsync scripts appropriately.

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.

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).

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.

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 meetPlaces table, with mp_id and mp_desc.
• We need a docs_to_meetPlaces table, with dtm_dtm_id, dtm_doc_id_fk, and dtm_meetPlace_id_fk, the last two foreign keys into the table above and the documents table.

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);

The "dissent types" field is now working in AdaptiveDB_dev. I had some odd behaviour for a while -- only one item would be recorded for any given document -- but it turned out that I had a "UNIQUE" index on the dtd_doc_id_fk field, which was limiting entries to one. Fixed that in both dbs.

Next, I ported the data over from the old custom fields, and then removed those fields. Did this very carefully with lots of testing along the way, and ended up with the following script:

/* First we clear the existing playing-around data in the 
   linking table. */
   
   TRUNCATE TABLE `docs_to_disTypes`;
   
/* Now we go through each of the custom fields concerned. */

/* First, we copy over its data. */
/* Booleans. */

    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "7";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "8";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "9";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "10";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "11";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "19";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "21";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "22";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "9", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "23";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "10", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "33";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "11", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "40";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "12", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "41";
    
/* Now the values which were originally strings. No need to measure the strings; I checked and all records have strings. */
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "13", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "42";
    
    INSERT INTO `docs_to_disTypes` (`dtd_disType_id_fk`, `dtd_doc_id_fk`) SELECT "14", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "58";
    
/* 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` = "81" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`)) WHERE EXISTS (SELECT * FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "81" 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 (7, 8, 9, 10, 11, 19, 21, 22, 23, 33, 40, 41, 42, 58, 81);

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

    DELETE FROM `customFields` WHERE `cf_id` IN (7, 8, 9, 10, 11, 19, 21, 22, 23, 33, 40, 41, 42, 58, 81);

I've now updated the main db code and run the same script, so the two dbs are in sync. I also updated my copy_live_to_dev.sql script to handle the new tables, and ran it to refresh the dev db from the live one. All seems to be OK. I still need to add the disTypes table editing options.

These are the relevant changed files:

• db/db_fields.php
• db/db_record.php
• db/db_recordlist.php
• editor/save_rec.php

Diffing suggested that changes are non-destructive, amounting only to the addition of sorting capabilities for the one-to-many field values, and better commenting, so I migrated the changes over to AdaptiveDB_dev, and it seems to be working fine.

NEXT (tomorrow): set up the one-to-many field in the local_classes.php file, and test it.

Started on the second item on the plan. This is what I've done so far (in both the live and dev dbs):

• Created the disTypes table, and populated it with the 14 values prescribed.
• Created the docs_to_disTypes table, and hooked up the foreign key fields appropriately.

Now I'm looking at creating the new one-to-many field in the local_classes.php file, working from examples in the Properties project, but it looks as though the codebase for the AdaptiveDB is a couple of generations older than that for the Properties project, and I definitely want to be working on a current codebase, so now I need to do this:

• Diff the PHP code trees in Properties and AdaptiveDB_dev, and look at each change.
• If the changes aren't likely to break the existing site, then migrate all the changed files from Properties to AdaptiveDB_dev.
• Test AdaptiveDB_dev carefully to make sure nothing is broken.
• Add the new one-to-many field definition in AdaptiveDB_dev, and make sure it works.
• Migrate all changes to the main site.

That's my basic plan for tomorrow.

Starting from the original post from August 16, I'm beginning to expand and extrapolate into detailed plans, with clarification from JW. I'll keep working on this post until I have a detailed plan. Any completed bits will be deleted, and logged in their own posts as they're done.

Copied the live db to dev, and backed up both, then deleted five unneeded custom fields, as requested, through the web interface. Then I looked at a couple of other items, and wrote to JW for clarification.

This is JW's list of changes to be made over the next few months:

PLANS FOR CHANGING/MERGING/REDUCING CUSTOM FIELDS

• DONE 10-08-16: Two groups of custom fields that can be deleted:
  • Communal documents: types (3 custom fields) can be deleted - I've incorporated this into the document type table.
  • Asides: this was of minor utility - only 6 records had anything flagged in here - so I've just moved those comments into the 'Notes' field.
• DONE 10-08-17: New regular field (one-to-many): "Flagged for chapter/section" (so that I can easily flag and retrieve documents I am planning to use to write a particular section).
• Some groups of custom fields can be converted into a one-to-many field alone:
  • DONE 10-08-18: References to types of dissent (leave "other", paste any 'description/comments' entries into regular 'Notes' field; also, please add to the list of table possibilities:
    • from "Attitude towards..." group, the first option - Indépendance, esprit républicain (just convert to true/false, delete any other detail in that field, it's not necessary)
    • from "Non-official communication..." group, the first option - Bruits publics, murmures, etc. (again delete any detail contained in field)
  • DONE 10-08-19: Places of meeting & discussion (as with previous)
  • DONE 10-08-23: 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.
  • DONE 10-08-24: 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?
• Some groups of custom fields can be converted into a one-to-many + text field
  • DONE 10-08-25: 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")?
  • DONE 10-08-26: 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?
• Remaining in custom fields:
  • "Judicial process information" group to remain in custom fields for now - works well as such.
  • "Sandbox" fields - leave for now. This was a catch-all for bits that my RAs didn't know where to put; I still need to go through them and deal with the comments. Eventually these fields will be deleted.
  • Last group, "Villagers' attitudes to seditious prosecution", leave for now - this is still very useful to me as custom fields.
  • JW can do through the interface: New custom field group to be created, 'Flags' or something like that, to try out flagging a particular theme or idea and see where it best fits .

FEATURE REQUESTS

• Already requested:
  • (DONE, BUT ONLY FOR REGULAR FIELDS): A refinement to the search engine which allows you to check those fields which you want to see in the results table; the checkboxes would feed into the search and display code, and give you a table with only the fields you've specified. Will it be possible to choose specific options from one-to-many tables in this, too?
  • Multi-record editing: Once you've retrieved some search results, you should be able to choose a subset of those results by checking checkboxes on each row, and then apply an edit to all of those records at the same time. This will require having an edit form which includes checkboxes next to each field, to specify that you want to change that field; and then submission code which includes only those fields.
• New requests:
  • Can there be an "export results" or "export table" to Excel feature? So that I could play around with the information to think with it? It's possible that in the long run, with more ability to show certain fields in a results table, this will be less necessary, but if it's not difficult to do, it would be very convenient.
  • DONE IN MODIFIED FORM 10-09-01When 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.
  • Is it possible in the search form to have the option to say, search for documents where X field is empty/not empty? Or perhaps empty/not empty/contains (to avoid the % % indication, which works fine, but is a little less user-friendly from the front end)? This is a nice-to-have, perhaps for further in the future.
  • DONE 10-08-28: 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.)

JW reported a bug in the new search interface, whereby if you edit a document from within a table which has specific selected fields visible in it, the retrieved updated record row contains the full set of columns instead of the restricted set. Fixed this in the context of the dev site on Properties, before testing on AdaptiveDB dev, then migrating the fix to both live sites.

Replaced the existing live site code with the new version which includes the ability to choose which fields appear in search results.

I now have so many versions of this code running in so many projects I need to make sure I know where the latest versions are. The latest codebase is definitely the one in the AdaptiveDB right now; it also incorporates the oneToMany field updates, which are not used in the AdaptiveDB itself. Other codebases, though, incorporate PDF output code which is not in this codebase because it's not needed.

The search fieldset can now be hidden or shown by the user by clicking on a triangle in the legend; and when a search is run, it's hidden automatically to make more space for the results.

I've got the new "choose which fields you see in the search results" feature working. It seems to be stable in the development site, and has no impact on the data entry features. It was a bit messy, and has resulted in a few lines of code being duplicated, so there are efficiency gains I could make with a bit of a re-factor, but overall it was less difficult than I feared. The feature can never realistically be extended to the custom fields -- it would be insanely complicated and painfully slow, for one thing, given that there are (at the last count) 110 custom fields. But I think JW might now consider consolidating some of those, and moving them back into the main table (something I could achieve on her instructions using SQL). Failing that, I think JW's project might be better served by its own customized, tuned search interface, to overcome some of the performance penalties that the generic AdaptiveDB backend code causes.

In preparation for adding a couple of new features, I've now ported back the changes I made in my personal songdb project, and then in the grsJournals project, to the dev site. Everything seems to be working. I'm now going to start working on making the fields to display selectable.

JW emailed to report a very odd thing with the db: all the values for the doc_to_docPeriods_id field in the documents table have been set to null except for nine. Trawling back through the backups, I discovered that on January 26, there were values in that field for most of the records. By February 1, they were all set to null.

This is impossible to achieve using the AdaptiveDB PHP interface, because that's a foreign key field, and the edit interface imposes a value on you when you edit it. So it can only have been achieved somehow by an administrator, maybe through PHPMyAdmin. The only scenario I can imagine is that someone deleted the docPeriods table; that would cause all those values to be set to null because it's a foreign key. Then if they restored the table (it's tiny -- only six records), the original field values would not be restored. Could that have happened? If so, who could have done it?

Using Transformer, I processed the Jan 26 dump to create update statements which re-insert the old values, and tested the results on my dev db. With JW's approval, I ran the script on the main db to reinsert the old values, and I've taken a fresh backup after doing that.

In all projects, add this:

div.tabCaptions{
[...]
  line-height: 200%;
}

Makes the tabs wrap properly when they won't fit on a line. Implemented in the Siberian project.

I've blogged some of the changes in the MoM blog, but I've done a few more. A summary:

• Added lat/long field type.
• Rewrote AJAX code to make it more robust. Now tested and working on Gecko, Opera and WebKit. IE8 still fails to send data to the db, because it's unable to read the values from form elements which were inserted from an Ajax request. Probably not worth caring about. It can search, filter and display data; it just can't edit.
• Found the cause of the Opera non-functional Save button: the displayed form had a margin-left: -6em setting, which moved it out of its containing table to the left a bit. The Save button was outside the bounds of the containing table, so it wasn't receiving any mouse events. That seems daft on Opera's part, but I worked around it by removing the offset. I could replace the offset again by moving the button inwards, but it's not really important.

I've also tested the code at home on my ISP space, and had problems relating to the sending of multiple headers. I've moved some code around to work around this, and I'll test it over the weekend. It would be good to make sure the code is actually portable off our servers.

After an almighty struggle with the filtering and searching code, caused by the fact that all the data for one-to-many fields is in a separate table entirely so various joins are required, I finally got everything working. It's been tested with my little pilot db, and we'll test it for real on the MoM project starting tomorrow. It's now pretty much automated as well, so that even the search page is set up automatically. All you need to do manually is set the connection data, and write the local_classes.php file. I still need to document in detail how to do that.

Tested and working with Gecko and WebKit. On Opera, saving fails for some reason, although no error is shown; there's something odd about Opera, but it's not a big deal.

The only remaining bits of the equation are filtering and searching. Those will be quite hard, I think, but feasible.

Debugged yesterday's work and got the display code working, and I also have the field spitting itself out appropriately in the form of select elements and buttons for deleting and adding new items.

Added some simple data to my test db, to get something to work with. Wrote the basic structure of the one-to-many field, and implemented most of the functions for viewing data. The code compiles, but when I use this field type in an actual record, it blows up. As usual, not much feedback from PHP, so I'll have to start working through the code.

The MoM project requires a new field type, which handles one-to-many relationships stored in a separate table. My AdaptiveDB is now too big and slow to use for this development, so I've created a simple oneToMany db to use when working on this field type. I'm going to need speed and simplicity to get the work done efficiently.

Wrote and tested the SQL script described in this previous post. I can now clone live data to the dev db at will.