From sql generated by my processing code, populated total of 14,000+ records into the following one-to-many tables:
docs_to_keywords
docs_to_chapters
docs_to_doctypes
docs_to_langoriginals
docs_to_docs
None of my front-end code uses those tables yet, but will eventually.
July 7, 2010
To simplify query-writing, I've replaced all instances of NULL with 0 in the following fields documents.doc_loc_id
documents.doc_credit_id
documents.doc_archive_id
documents.doc_coll_id
I've added a record to each of these tables with the id of 0 and text values of [none]
locations
credits
archives
collections
I now get the correct number of records back from my searches.
Noticed that a number of records in the documents table have NULL for a value in the doc_coll_id field (e.g. 941 through 967). If my SQL includes a condition like "documents.doc_coll_id = collections.coll_id", that will not test true for records with values of NULL. The query SELECT * FROM `documents` where doc_coll_id > 0 returns 1296 records, which matches the number returned by queries which didn't behave has expected as posted before. Will look into creating a "[none]" value in the collections table and then pointing to that.
Spent most of a couple of days cleaning up remaining problems in data provided in main spreadsheet by BI and processing that through the ten-step procedure. Some problems were typographic errors, some were new values in lookups (e.g. archive, collection, keyword, location) and either the code in my Transformer routine didn't include those new values, or the tables in the database didn't include those. Compared the values in Ben's spreadsheet, my processing code and in the database tables for all the secondary tables to ensure they reconciled. Still have to upload the values for the various one-to-many tables as generated by my code - shouldn't be hard. All future edits, additions to the db by BI will be through the adaptive DB front end at (at least for now) https://lettuce.tapor.uvic.ca/~siberian/edit/editor/enter.php
In testing the Siberia site, I got strange results (see details below). The cause is a bug in that specific version of mysql involving compound WHERE or JOIN clauses which involve equality tests across two or more tables. Greg is working with the sysadmins to update the mysql engine running on our server, as newer versions of mysql have debugged this problem. Strange that nobody else has encountered it as we have at least a dozen databases running on that engine.
SELECT documents.doc_id, archives.archive_en
FROM documents,archives
WHERE documents.doc_archive_id = archives.archive_id
--> 2261 records (seems right)
SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
WHERE 1
--> returns 2261 records (seems right)
SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents,archives,collections
WHERE documents.doc_archive_id = archives.archive_id
AND documents.doc_coll_id = collections.coll_id
--> returns 1296 records (seems wrong, should be 2261)
SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
JOIN collections ON (documents.doc_coll_id = collections.coll_id)
WHERE 1
--> returns 1296 records (seems wrong, should be 2261)
Not entirely clear what the pattern of the problem is:
SELECT documents.doc_id, archives.archive_en
FROM documents,archives
WHERE documents.doc_archive_id = archives.archive_id
AND archives.archive_id = 4
--> returns 2 records (seems right - consistent with test above)
SELECT documents.doc_id, archives.archive_en
FROM documents,archives
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
WHERE archives.archive_id = 4
-- returns 2 records (seems right - consistent with test above)
SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents,archives,collections
WHERE documents.doc_archive_id = archives.archive_id
AND documents.doc_coll_id = collections.coll_id
AND archives.archive_id = 4
--> returns 2 records (seems right - NOT consistent with test above)
SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
JOIN collections ON (documents.doc_coll_id = collections.coll_id)
WHERE archives.archive_id = 4
--> returns 2 records (seems right - NOT consistent with test above)
June 30, 2010
ML sent a simple flowchart of the work process for the Canadian Legal Bibliography. Two of the boxes were labelled "detect duplicate articles (?fuzzy logic)" and "detect duplicate annotations". He wants us to modify Zotero JS code to detect duplicates. Did some research and discussed with Martin
Approach 1 to detect duplicates : use similarity score
If we could find a javascript library that could take strings and generated compressed versions, we could compare those using the similarity algorithm and determine a threshold (between 0.0 and 1.0) for what counts as similar enough to be flagged as duplicate. Problem is we can't find such a javascript library that takes a string input, compresses it and produces a string output. We wanted JS implementation because we figure that will be easiest to integrate with Zotero.
Approach 2 to detect duplicates : check for identicals
About the simplest definition of a duplicate would be two articles with identical values in at least one of the following:
1) title (case-insensitive, ignoring whitespaces),
2) Digital Object Identifier, or
3) publication, volume, issue, and page
Wrote to ML asking if approach 2 would be acceptable.
Also wrote asking if her preferred that duplicate-checking happen when the contributor tries to submit an article, or as a batch job run by a site-administrator from time to time. If we use approach 1 and have to use a php-based compression library, we'll likely be restricted to the batch option.
Also asked him questions about how automated should be the deletion of duplicate articles, and the merging of articles which differ only in their annotations, and to clarify that the rest of the tasks (input and format of data, verification of data) is manual labour.
BS's drupal prefers to work with 'clean URLs', which means that individual pages of the site can be accessed without the ?q= in in the URL path. Setting the site to work with clean URLs is something that you can normally turn on and off with a Drupal installation, but at the moment, it doesn't seem to work with the UVic server. In reading up on it, it seems that this feature requires that mod_rewrite be enabled in Apache.
I did a couple of simple tests and it appears that mod_rewrite is not enabled. This is up to the sysadmins. I wrote to helpdesk, who will escalate and I'll find out what their policy is on allowing mod_rewrite on an account, domain name etc.
LL copied and pasted some text for a web page from MSWord into Notepad++. This appears to have caused Notepad++ to treat the character encoding as 8859-1 rather than UTF. When the page was opened in the browser, the accented characters were not UTF characters, so showed up as unknown character boxes. By setting the character encoding in the browser to 8859-1, the correct characters showed up. To recover the corrupted character and change the character encoding to UTF-8 in Notepad++:
- open the file in Notepad++
- in the Encoding menu, chose Encode in ANSI
- in the Encoding menu, chose Convert to UTF-8 without BOM
- save file
Got email from LW that link to aesthetics.php and to global_anti-realism.pdf on JOY's page were broken. With Judy, found the links, which pointed to paths on the old site. The files they pointed to did not exist on the new site, so we found the pdf file in the archived copy of the old site in philosophy's account and copied it to an appropriate folder on the new site, then rewrote the link to that file. For the php file, we copied the index.php file from the target folder, renamed it and included the content from the old file. It included a list of links, each of which was to a path on the old site, so we edited those paths and copied the image files from the old site to the appropriate folder in the new site.
The upcoming legal bibliography project is coming up for submission to the committee, and so far we have a very sparse set of details about what's required; there's a jumble of suggested technologies and no clear path for integrating them. In the ongoing absence of detail, we looked this morning at one aspect of the project that we can consider: finding out whether an item has been submitted previously or not, based on fuzzy matching. We looked at the possibility of using NCD for this, and it seems practical in PHP, which has lots of useful compression algorithms; we looked in vain for a decent working implementation of any compression algorithm in JavaScript, so it doesn't seem that it would be practical to do this in JS. However, we also note that Zotero already has duplicated detection working "internally", and it will presumably be available soon, so perhaps this is not a good use of our time.
It seems to me that the entire functionality that's required could be implemented with a collective centralized Zotero bibliography -- one closed group and one open group project, so anyone could submit to the open group, and managers could then move data to the closed group.