Adaptive Database

This morning I've completed the following field types:

• MdhTextField
• MdhStrSelectField
• MdhLinkSetField

Some other fields are covered by their ancestor fields, so only MdhLatLongField and MdhOneToManyField remain to be done.

I think I'll also add a method to shorten the visible URLs in MdhLinkField and MdhLinkSetField using ellipses, where there's no associated link image; that will make display layout more flexible.

The MdhRecord class now has a new paramater on its WriteAsFormElements() method which determines whether the form is being written for a single record edit (in which case all the fields are filled with existing info, in the case of an existing record, and they're all enabled), or for multi-edit (in which case fields are empty and disabled by default, and provided with a checkbox to enable them if the user wants to set them). The following classes can now write themselves out in the form of enable-able and disable-able editing controls, so that the user can select which fields they want to change in the multiple records:

• MdhIntField
• MdhBoolField
• MdhStrField
• MdhStrLookupField

Remaining to be done:

• MdhDateField
• MdhLatLongField
• MdhLinkField
• MdhLinkSetField
• MdhLongTextField
• MdhOneToManyField
• MdhStrSelectField
• MdhTextField

I've also been giving some thought to how to refresh the table of search results from which the editable records were selected, and concluded that the simplest way to do this is simply to re-run the query that was last used. We're already storing the last search query parameters in the spreadsheetSearch array, which is indexed by table name, so we can simply re-run it; although since we'll now be using it for two different purposes, it should probably be renamed to something like "lastSearch". Running a single search query will mean that only one query hits the db (as opposed to trying to update every row that's changed in the current table), and it will also solve the problem of rows which should disappear from the table because they no longer match the current query. We can cause this query to run by using the funcToCall parameter to the AJAX sendRequest() method.

Added checkboxes to allow selection of records that will be group-edited, as well as a master checkbox that controls them all, which functions in a tri-state way, and an edit button.

The next stage is to figure out how to perform the edit. The logical thing seems to be:

• Pass back a special query with a list of ids that will be edited.
• Generate an empty edit form, as for a new record, but with (again) checkboxes to allow the user to select fields which will be changed.
• Show the form in the first row, as with a new record.

One question: do we attempt to pre-fill any of the data, based on whether all the edited records share the same value or not? This looks like it might be very expensive in terms of queries to check this, unless there's a fast SQL approach to this. I'll do some research.

They seem to include early dates as well (such as 100 AD). I have to do more research on how we might support BC dates.

Changes have been rolled out the the Manifestoes project as a first test on real data.

The validity checking and help popup is now done. Now we have to write the backend.

Range searching for integer fields in now working (so you can enter "<50>40" in a search field in order to find a number between 41 and 49). This provides a model for range searching on dates, although that will be slightly more complicated.

Wrote and tested JavaScript regex to constrain input in integer fields, allowing greater-than and less-than:

/^((([\s]*[<]{1,1}[\s]*[-+]{0,1}[\d]+[\s]*[>]{1,1}[\s]*[-+]{0,1}[\d]+[\s]*)|([\s]*[>]{1,1}[\s]*[-+]{0,1}[\d]+[\s]*[<]{0,1}[\s]*[-+]{0,1}[\d]+[\s]*))|([\s]*[<>]{0,1}[\s]*[-+]{0,1}[\d]+[\s]*))$/

Added this validation to the JS code, and also updated the search output to include validation (it was missing for some reason; only previously included in data-entry form).

JW reported that the number of custom fields has grown to the point where the db is unusably slow, and she needs to work with a subset of it. After getting details of what can be dropped, I figured out, tortuously, a procedure to build a cut-down version of the db.

• Create a backup from phpMyAdmin.
• Open the backup in a text editor and replace all instances of DEFINER=`hcmc`@`lettuce.tapor.uvic.ca` with DEFINER=CURRENT_USER. (If you don't do this, when you later try to dump the trimmed database, the dump will fail because the definer will not be a user on the local host.)
• Install mysql-server-5.1 locally, and go through all the recommended security settings (this helped).
• Log into mysql (mysql -u root -p).
• create database tempAdaptive;
• From a regular command line, put your backup data into the new db:

  mysql -u root -p[password] tempAdaptive < /path/to/dump.sql
  

  Remember there's no space after -p.
• Log back into mysql, and start trimming (see commands below).
• When the db is trimmed down, dump out the results:

  mysqldump -u root -p[password] --add-drop-database --databases tempAdaptive > /path/to/dump.sql
  

• Stop your local mysql server:

  sudo service mysql stop
  

• Put the dump file up on home1t/hcmc/temp.
• Log into mysqldev.tapor.uvic.ca as hcmc, and navigate to that folder.
• Log into mysql, and create tempAdaptive.
• Log out of mysql, and restore:

  mysql -u hcmc -p[password] tempAdaptive < tempAdaptive.sql
  

• Log into phpMyAdmin and give the regular Adaptive DB user the same privileges as on the original db.
• [Starting tomorrow] Build a stripped-down AdaptiveDB site for this dataset.

This is the trimming I had to do for this particular situation:

DELETE from documents where NOT(doc_to_docTypes_id="1" AND exists (SELECT * FROM docs_to_docPeriods WHERE docs_to_docPeriods.dtp_doc_id_fk = documents.doc_id AND docs_to_docPeriods.dtp_docPeriod_id_fk="2"));
alter table documents drop foreign key documents_ibfk_2;
alter table documents drop column doc_to_projects_id;
alter table documents drop foreign key documents_ibfk_4;
alter table documents drop column doc_to_docPeriods_id;
alter table documents drop foreign key documents_ibfk_3;
alter table documents drop column doc_to_docStatusVals_id;
alter table documents drop column doc_notBefore;
alter table documents drop column doc_notAfter;
drop table docs_to_chapters;
drop table docs_to_disStatus;
drop table docStatusVals;
drop table documentsBack;
drop table projects;
drop table customFieldDataBack;
drop view documents_view;
drop view VW_doc_authConcerns_concat;
drop view VW_doc_chapters_concat;
drop view VW_doc_disStatus_concat;
drop view VW_doc_disTopics_concat;
drop view VW_doc_disTypes_concat;
drop view VW_doc_docPeriods_concat;
drop view VW_doc_meetPlaces_concat;
drop view VW_doc_news_concat;
drop view VW_documents_all;
drop function cf_bool_to_otm;
delete from customFields where cf_id in (95,46,5,44,4);
DELETE FROM customFields where cf_group_1 LIKE "Sedition concepts%";
DELETE FROM customFields where cf_group_1="Temporary flags - for topic sorting";
DELETE FROM customFields where cf_group_1="Sandbox";

There isn't a single project which embodies all the features I need to be working on at the moment (at least, not one which is agile enough for fast responses while debugging), so I've spent some time setting up a little test db called potluck, and I'll use that for my development going forward. I've also started work on factoring out the date and integer checking routines from the validation code in JS, so that I can elaborate that code to handle greater-than and less-than items. It's going to be slightly complicated in that we'll have to use different validation code for search fields than we do for data-entry fields, but that shouldn't be too messy.

The previous setup under which there was a multi-table editing page and a single-table search page has now been superceded by a unified interface, in which all tables are accessible, but their tabs display search pages. In the process of doing this update, which is an essential precursor to other upcoming enhancements, I've also fixed a couple of display issues with Chrome and Opera.

This change was developed on the Manifestoes DEV site and has been propagated to the LIVE site; if no bugs appear in usage, I'll also push it out to all the other current working sites (except for the original AdaptiveDB site).