Adaptive Database

Did the last three feature requests relating to editing field sizes from the Feb 14 post. I think that basically means the backlog has been cleared, and we can focus on the large-scale view generation code.

When loading the initial large page, the first tab may render before the rest of the data is down, and it's not clear to users when the load is completed. Added a visible message that disappears once loading is complete.

Also discussed with GN ways to speed up the interface, and a new idea emerged: we could load only the table for the initial tab, and get other tables later if/when the user clicks on their tabs. This would improve initial page load significantly, at the cost of some delay when you want to access another tab; but much of the time, the user is working only in one or two tables anyway.

COMPLETED: Also in the ‘News/info’ table: can two fields be moved to the “Concerns of authorities” table? They seem misplaced. The fields are: “Sedition: how to counteract seditious words”, and “Importance of communicating to peasants”

First one:

SELECT `dtn_doc_id_fk`
FROM `docs_to_news`
WHERE `dtn_news_id_fk` = "12"
ORDER BY `dtn_doc_id_fk`;

# Create the parallel field in the News table, and get its id.

INSERT INTO `authConcerns` (`ac_name`) VALUES ((SELECT `nw_desc` FROM `news` WHERE `nw_id` = "12"));
SELECT `ac_id` FROM `authConcerns` WHERE `ac_name` = (SELECT `nw_desc` FROM `news` WHERE `nw_id` = "12");

(New id was 23.)

# For each of the entries in the docs_to_disTypes table, create a parallel entry in the docs_to_news table.

INSERT INTO `docs_to_authConcerns` (`dta_doc_id_fk`, `dta_authConcern_id_fk`) (SELECT `dtn_doc_id_fk`, "23" FROM `docs_to_news` WHERE `docs_to_news`.`dtn_news_id_fk` = "12");

# Delete the original field from the disTypes table. This should cascade to remove the entries in docs_to_disTypes automatically.

DELETE FROM `news` WHERE `nw_id` = "12";

Second one:

SELECT `dtn_doc_id_fk`
FROM `docs_to_news`
WHERE `dtn_news_id_fk` = "2"
ORDER BY `dtn_doc_id_fk`;

# Create the parallel field in the News table, and get its id.

INSERT INTO `authConcerns` (`ac_name`) VALUES ((SELECT `nw_desc` FROM `news` WHERE `nw_id` = "2"));
SELECT `ac_id` FROM `authConcerns` WHERE `ac_name` = (SELECT `nw_desc` FROM `news` WHERE `nw_id` = "2");

(New id was 24.)

# For each of the entries in the docs_to_disTypes table, create a parallel entry in the docs_to_news table.

INSERT INTO `docs_to_authConcerns` (`dta_doc_id_fk`, `dta_authConcern_id_fk`) (SELECT `dtn_doc_id_fk`, "24" FROM `docs_to_news` WHERE `docs_to_news`.`dtn_news_id_fk` = "2");

# Delete the original field from the disTypes table. This should cascade to remove the entries in docs_to_disTypes automatically.

DELETE FROM `news` WHERE `nw_id` = "2";

Two of the outstanding requests involve moving records from one table to another:

• In the ‘Dissent types’ table, could item #14 (“Communication vocab: Bruits publics, murmures, etc.”) be moved to the ‘News/info’ table?
• Also in the ‘News/info’ table: can two fields be moved to the “Concerns of authorities” table? They seem misplaced. The fields are: “Sedition: how to counteract seditious words”, and “Importance of communicating to peasants”

I'm just going to map out this process before undertaking it, working with the first one above. There are 19 records in the documents table which link to this record in dissentTypes (as part of a one-to-many relationship). I think these would be the steps we need to take:

• Record which records have a link to this field.

  SELECT `dtd_doc_id_fk`
  FROM `docs_to_disTypes`
  WHERE `dtd_disType_id_fk` = "14"
  ORDER BY `dtd_doc_id_fk`;

• Create the parallel field in the News table, and get its id.

  INSERT INTO `news` (`nw_desc`) VALUES ((SELECT `dt_name` FROM `disTypes` WHERE `dt_id` = "14"));
  SELECT `nw_id` FROM `news` WHERE `nw_desc` = (SELECT `dt_name` FROM `disTypes` WHERE `dt_id` = "14");
  

  (New id was 20.)
• For each of the entries in the docs_to_disTypes table, create a parallel entry in the docs_to_news table.

  INSERT INTO `docs_to_news` (`dtn_doc_id_fk`, `dtn_news_id_fk`) (SELECT `dtd_doc_id_fk`, "20" FROM `docs_to_disTypes` WHERE `docs_to_disTypes`.`dtd_disType_id_fk` = "14");
  

• Delete the original field from the disTypes table. This should cascade to remove the entries in docs_to_disTypes automatically.

  DELETE FROM `disTypes` WHERE `dt_id` = "14";
  

• Check that those 19 document records have lost the old entries and acquired the new.

Tested on dev, implemented on live site.

I've implemented table sorting based on clicking of headers, as laid out in a previous post. In order to get sorting working reliably, I went with an object-based model rather than nested arrays. Tested and working on the AdaptiveDB site; I'm now going to test it on Properties dev, then live.

Three change requests from JW at the weekend have been added to the queue (which is currently in this post).

I've written the JS library to implement table sorting, and although it appears to work, the results don't actually match what's expected, so I've obviously screwed something up somewhere. The order seems to change at random. This needs debugging and fixing, and once that's done, I need to decide whether to add the direction triangle feature -- maybe not needed -- and I have to set the pointer for the column header.

We've had results table sorting by column-header-click on the list of features for a while, and I'd like to get it done. There are lots of implementations of this out there (I've written one or two myself in the past), but the complication here is that the output tables consist of two rows for each visible row; one contains the data, and the next contains a div which will be used to display a retrieved record for viewing or editing. When I sort the table, I need to keep these pairs of rows together. The former always has an id beginning with "disp", and the latter has a class of "recDisplayRow", so they're relatively easy to distinguish, but I'm still not quite sure of the exact method to use. There's a sophisticated library here which has some good pointers, although a lot of its code is superfluous to our needs as it's handling old browser limitations. It also has workarounds for the vagaries of table structures which we don't need to worry about, because our tables have a predictable structure (although I was annoyed to discover that I didn't use thead, just a regular row full of th tags -- but that's not a problem really because row 0 is always the header).

So I think the answer is to learn from that code, but write something much simpler. This would be the basic plan:

• Sorting is accomplished by detaching the rows in pairs, and storing them in a 2-level JS array, like this:
  • rows[0][0] = extracted data from the column in row #1 on which the rows will be sorted. This can be passed to the the sort algorithm.
  • rows[0][1] = row #1 (first data row)
  • rows[0][2] = row #2 (associated hidden view/edit row)
  • rows[1][0] = extracted data from the column in row #3 on which the rows will be sorted.
  • rows[1][1] = row #3 (second data row)
  • rows[1][2] = row #4 (associated hidden view/edit row)
  and so on. Then we can sort the items in the first level of the array, based on the content of the first slot in level 2.
• We obviously need an algorithm for extracting the text content of the column, but that should be fairly simple; as far as I can tell, it really just amounts to getting the td node's text content, but there may be the odd exception (images for attachments?).
• The next question is how to sort the content; there are various different types, including integers, dates, floats (treating lat and long as floats) and text. Each of these needs a different sorting algorithm, and each type of content needs to be identified in order for us to know the content type. Up to now there's been nothing to identify the data type of the content, so I've added a class to the header cell so that we can easily tell which comparator function to invoke. The class is simply the classname of the underlying AdaptiveDB field type. This is not costly because it happens only once for each column. There are a couple of good comparator functions already in the
• We also need to add a little span which can contain an indicator triangle showing that the table has been sorted by a specific column. This can be empty until it's filled when a sort is invoked. I've already added this to the code in the trunk.
• The sort procedure itself will need to be passed the column that's been clicked on; it could either derive this from "sender", or the column code itself could have its position hard-coded into it as part of the JS call. The latter is probably easier.
• There has to be a way for the JS to remember what column was last used to sort the data, and which direction it was sorted in, so that it can (for instance) remember to sort in reverse if clicking for the second time on a header, and remove a previous triangle from another header when a subsequent sort changes the order. This could be done by stashing content in variables, but we have to remember that at any time, the table can be replaced by the results of another search, which since it's done by AJAX, would not empty those variables.

Can't think of anything else right now...

This is the latest set of updates and feature requests from JW. Some quick ones I've done; others are outstanding until I have some time. Added some more from JW's email of 2011-02-26.

• DONE: 2011-03-01: Notes field should be a minimum size of 5-6 lines. (This may require a setter for field sizes in the appropriate db_fields class.)
• DONE: 2011-03-01: Transcription field should be a minimum of 15-20 lines (ditto as above).
• DONE: 2011-03-01: Linked files field should have fewer lines (four perhaps?). Ditto as above.
• DONE 2011-02-28: In the ‘Dissent types’ table, could item #14 (“Communication vocab: Bruits publics, murmures, etc.”) be moved to the ‘News/info’ table?
• DONE 2011-02-28: Also in the ‘News/info’ table: can two fields be moved to the “Concerns of authorities” table? They seem misplaced. The fields are: “Sedition: how to counteract seditious words”, and “Importance of communicating to peasants”
• DONE 2011-02-28: In looking at any given table, might it be possible eventually to 'sort' a column, for example, by date, or alphabetically by revolt name, etc?
• DONE: Could you please simply remove the 'project' field? My political societies article is done (and it was never relevant to add records to it); and with counterfeiting I think I'd like to start a fresh database, like I have with the manifestoes. There are a few documents listed as ‘political societies’ project – they’re being used for ShowofHa as well, so they can just stay in without any distinction.
• DONE: There seems to be a leftover custom field, “Other” in “References to types of dissent”. Could this be added to the ‘Dissent types’ one-to-many field? (Only five records -- did this manually.)
• DONE: Change title of regular field: from "Dissent topics: desc." to "Dissent: desc." (so can use it for elaboration on dissent types as well as dissent topics)
• DONE: Is it possible to change the name of the table “Meeting places” to “Locations”? I’m using these fields to flag information about different locations, whether it be meeting places or revolt loci or focus of police investigations…etc., so I need a less-specific title.
• DONE: In the “Concerns of authorities” table, could you please merge “Sedition: Assumption that peasant is not the guilty one” into “Assumption: peasants = not leaders”?

  UPDATE `docs_to_authConcerns` SET `dta_authConcern_id_fk`="18" WHERE `dta_authConcern_id_fk`="9"
  

• DONE: And also with the “Concerns of authorities” table, can we rename it to “Authorities’ perspective”?

The final requirement for implementing the one-to-many version of docPeriods was to split composite docPeriods into two separate individual entries. So where, before, there were these items:

• 1 AR Ancien Régime - 1789
• 2 FR French Revolution 1789-1799
• 3 c19 19th century : 1800 -
• 4 AR - FR Starting in the Old Regime and continuing on past ...
• 5 FR - c19 Starting in the Revolution and continuing on past ...
• 6 n/k Period not yet established

we need to convert all instances of the composite 4 and 5 into multiples of 1 and 2 or 2 and 3 respectively.

My test records for this were 305 (AR-FR) and 145 (FR - c19). This is the code:

INSERT INTO `docs_to_docPeriods` (`dtp_doc_id_fk`,`dtp_docPeriod_id_fk`) SELECT `doc_id`, "1" FROM `documents` WHERE `documents`.`doc_to_docPeriods_id` = "4"
INSERT INTO `docs_to_docPeriods` (`dtp_doc_id_fk`,`dtp_docPeriod_id_fk`) SELECT `doc_id`, "2" FROM `documents` WHERE `documents`.`doc_to_docPeriods_id` = "4"
DELETE FROM `docs_to_docPeriods` WHERE `dtp_docPeriod_id_fk` = "4"

INSERT INTO `docs_to_docPeriods` (`dtp_doc_id_fk`,`dtp_docPeriod_id_fk`) SELECT `doc_id`, "2" FROM `documents` WHERE `documents`.`doc_to_docPeriods_id` = "5"
INSERT INTO `docs_to_docPeriods` (`dtp_doc_id_fk`,`dtp_docPeriod_id_fk`) SELECT `doc_id`, "3" FROM `documents` WHERE `documents`.`doc_to_docPeriods_id` = "5"
DELETE FROM `docs_to_docPeriods` WHERE `dtp_docPeriod_id_fk` = "5"

Tested first on the dev db, then run on the live db. I've left those composite periods in place in the docPeriods table, just in case, and I've also left the old doc_to_docPeriods_id field in the documents table just in case. We can clean this stuff up later, once we're both sure everything's working well.