Adaptive Database

At JW's request, the MdhLinkSet object now displays each of its links on a separate line, and they're numbered if there's more than one. It looks as though Manifestoes may become the development platform for the trunk version of the db, but of course it can be developed in any of its checkout projects.

Added the new properties file to Manifestoes, Properties and JsrDocs, so it'll be there the next time I svn update them.

Following my own instructions in the previous post, the following files have now been removed:

• includes/footer.inc
• includes/header.inc
• includes/instructions.inc
• includes/meta.inc
• includes/title.inc

and replaced with variables in a file called project_variables_SAMPLE.php, which is to be saved as project_variables.php and edited for the project-specific data.

Now I need to create a copy of that file for every existing project, so they can be updated from SVN.

This is a list of things I've noticed which need to be updated, fixed or coded:

• The following files contain content which is specific to the local copy of the database; they should not be overwritten when doing an update from SVN:
  • includes/footer.inc
  • includes/header.inc
  • includes/instructions.inc
  • includes/meta.inc
  • includes/title.inc
  These should all be replaced by variables which are initialized in a file called project_variables.php in the proj folder, except for instructions.inc; the latter should be rewritten to be completely generic, but when it's used, it should be followed by an include which pulls in supplementary instructions from the variable file. The variable file should not be under version control, but there should be a generic version called project_variables_SAMPLE.php which is under version control.
• The enter.php file should not render all the tables in a single tab; instead, it should render only one table, based on the tableToShow parameter. The other tabs should be links. That will make the whole thing much quicker.

Tested the cleaned-up codebase on Manifestoes dev, and found a couple of bugs (remaining includes of now-deleted file). Fixed those, and now I have a fully-working (AFAIK) Adaptive DB codebase which doesn't include the custom fields.

I've decided that the custom field code is a dead end; the performance penalties are too great for any payoff there might be, and it's far simpler and less time consuming to respond to user requests for minor changes in the db structure than it is to maintain the complexity of the custom field codebase. Accordingly, I've branched the original Adaptive DB code, which supports custom fields, into a branch called customfieldsbranch, and eliminated (as far as I can tell) all custom field-related code from the trunk. I haven't been able to test any of this yet; it's an all-or-nothing thing. But it should be easy to test it with the Manifestoes dev db later this week.

JW reported a bug showing up in the Manifestoes project where dates prior to 1000 (e.g. 0996) were being highlighted as if incorrect, whereas they were in fact correct, and accepted by the db. Fixed the JS to allow anything between 0 and 4000. Haven't yet decided what, if anything, to do about BC dates.

I'm beginning the process of implementing the kind of simple search interface documented by JN here and in preceding posts.

I've generated a view of the complete document data (excluding custom fields, for the moment). The view can be dumped into an Excel spreadsheet, so that's what I've done (attached). These are the details:

• There's one row for each document in the spreadsheet.
• Regular fields in the document table are copied as-is into the spreadsheet.
• One-to-one lookup fields (such as document status) are copied in the form of their text representation (just as you see them when viewing a single document)One-to-many fields are turned into a single field in which each entry is shown in its text representation, and entries are separated by pipe characters (|).

I think this view, and the ability to dump it into a spreadsheet, will be useful in itself (although it takes several minutes to generate it). The rest of the plan, though, goes like this:

1. Write a procedure to copy the view (in which everything is represented as text) to a new table, in which all fields are text fields.
2. Create a full-text index on this table.
3. Create a simple text-search interface that uses a simple Google-style search box to search that full-text index.

This would make it simple to do normal searches as if you were searching with Google, not worrying about fields and drop-down selectors etc. It would have to lag behind changes in the db a little, though; the table would have to be re-generated in order to take account of changes in the underlying database. We might be able to automate that, or we might have to run a procedure to refresh it periodically.

Here's the SQL used to create the views:

/*
CREATING VIEWS: 
LITTLE VIEWS FOR USE IN THE MASTER VIEW
*/

CREATE VIEW VW_doc_chapters_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(chapters.ch_name SEPARATOR ' | ') 
	FROM docs_to_chapters 
        INNER JOIN chapters ON chapters.ch_id = docs_to_chapters.dtc_ch_id_fk 
        INNER JOIN documents as ch_docs ON ch_docs.doc_id = docs_to_chapters.dtc_doc_id_fk
        WHERE docs_to_chapters.dtc_doc_id_fk = documents.doc_id) 
AS chapters

FROM documents
ORDER BY documents.doc_id);


CREATE VIEW VW_doc_authConcerns_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(authConcerns.ac_name SEPARATOR ' | ') 
	FROM docs_to_authConcerns 
        INNER JOIN authConcerns ON authConcerns.ac_id = docs_to_authConcerns.dta_authConcern_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_authConcerns.dta_doc_id_fk
        WHERE docs_to_authConcerns.dta_doc_id_fk = documents.doc_id) 
AS authConcerns

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_disStatus_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(disStatus.ds_desc SEPARATOR ' | ') 
	FROM docs_to_disStatus 
        INNER JOIN disStatus ON disStatus.ds_id = docs_to_disStatus.dts_disStatus_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_disStatus.dts_doc_id_fk
        WHERE docs_to_disStatus.dts_doc_id_fk = documents.doc_id) 
AS disStatus

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_disTopics_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(disTopics.dc_name SEPARATOR ' | ') 
	FROM docs_to_disTopics 
        INNER JOIN disTopics ON disTopics.dc_id = docs_to_disTopics.dtc_disTopic_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_disTopics.dtc_doc_id_fk
        WHERE docs_to_disTopics.dtc_doc_id_fk = documents.doc_id) 
AS disTopics

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_disTypes_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(disTypes.dt_name SEPARATOR ' | ') 
	FROM docs_to_disTypes 
        INNER JOIN disTypes ON disTypes.dt_id = docs_to_disTypes.dtd_disType_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_disTypes.dtd_doc_id_fk
        WHERE docs_to_disTypes.dtd_doc_id_fk = documents.doc_id) 
AS disTypes

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_docPeriods_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(docPeriods.dp_name SEPARATOR ' | ') 
	FROM docs_to_docPeriods 
        INNER JOIN docPeriods ON docPeriods.dp_id = docs_to_docPeriods.dtp_docPeriod_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_docPeriods.dtp_doc_id_fk
        WHERE docs_to_docPeriods.dtp_doc_id_fk = documents.doc_id) 
AS docPeriods

FROM documents
ORDER BY documents.doc_id);


CREATE VIEW VW_doc_meetPlaces_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(meetPlaces.mp_desc SEPARATOR ' | ') 
	FROM docs_to_meetPlaces 
        INNER JOIN meetPlaces ON meetPlaces.mp_id = docs_to_meetPlaces.dtm_meetPlace_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_meetPlaces.dtm_doc_id_fk
        WHERE docs_to_meetPlaces.dtm_doc_id_fk = documents.doc_id) 
AS meetPlaces

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_news_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(news.nw_desc SEPARATOR ' | ') 
	FROM docs_to_news 
        INNER JOIN news ON news.nw_id = docs_to_news.dtn_news_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_news.dtn_doc_id_fk
        WHERE docs_to_news.dtn_doc_id_fk = documents.doc_id) 
AS news

FROM documents
ORDER BY documents.doc_id);





/* THE MASTER VIEW, TO BE EXPANDED. */
CREATE VIEW VW_documents_all AS

(SELECT 
documents.doc_id,
docTypes.dt_name AS docType,
VW_doc_chapters_concat.chapters AS chapters,
VW_doc_docPeriods_concat.docPeriods AS docPeriods,
projects.proj_name,
documents.doc_archive, documents.doc_cote, documents.doc_date,
docStatusVals.dsv_description,
documents.doc_notBefore, documents.doc_notAfter, documents.doc_description, documents.doc_people,
documents.doc_notes, documents.doc_transcription, documents.doc_done,
documents.doc_revisit, documents.doc_linked_files, 
VW_doc_disStatus_concat.disStatus AS disStatus,
VW_doc_disTypes_concat.disTypes AS disTypes,
VW_doc_disTopics_concat.disTopics AS disTopics,
documents.doc_disTopicDesc,
VW_doc_authConcerns_concat.authConcerns AS authConcerns,
documents.doc_authConcernDesc,
VW_doc_meetPlaces_concat.meetPlaces AS meetPlaces,
VW_doc_news_concat.news AS news

FROM documents
LEFT JOIN docTypes ON documents.doc_to_docTypes_id = docTypes.dt_id
JOIN VW_doc_chapters_concat ON documents.doc_id = VW_doc_chapters_concat.doc_id
JOIN VW_doc_docPeriods_concat ON documents.doc_id = VW_doc_docPeriods_concat.doc_id
LEFT JOIN projects ON documents.doc_to_projects_id = projects.proj_id
LEFT JOIN docStatusVals ON documents.doc_to_docStatusVals_id = docStatusVals.dsv_id
JOIN VW_doc_disStatus_concat ON documents.doc_id = VW_doc_disStatus_concat.doc_id
JOIN VW_doc_disTypes_concat ON documents.doc_id = VW_doc_disTypes_concat.doc_id
JOIN VW_doc_disTopics_concat ON documents.doc_id = VW_doc_disTopics_concat.doc_id
JOIN VW_doc_authConcerns_concat ON documents.doc_id = VW_doc_authConcerns_concat.doc_id
JOIN VW_doc_meetPlaces_concat ON documents.doc_id = VW_doc_meetPlaces_concat.doc_id
JOIN VW_doc_news_concat ON documents.doc_id = VW_doc_news_concat.doc_id

ORDER BY documents.doc_id);

Continuing my dogged documentation of the db_fields.php file, which is now nearly finished.

This was quite an upgrade, since the original code dated back quite a way. These are some things I need to handle in order to make code more easily applied to an existing project without overwriting customizations:

• Somehow we need to link to a user stylesheet that can override the existing styles. I think the best way to do this is to have a config item in the local_classes.php file (or a config file for just this kind of thing), where you specify the location of a user stylesheet.
• We have a similar problem with the include files, all of which have project-specific data in them, and yet they're in the project repo and will overwrite a project during a checkout. Again, a config file that the user completes is probably the best option.