Extra half hour Wednesday dealing with MSWord's inability to show devenagari characters, and to tidy up last tests on GROUP BY clause added to COUNT sql query
November 27, 2008
Got email from MF saying that the ISP wants to delete the cybermsyteres files. They seem to think the cybermysteres files are hosted in a separate account, when in fact they are hosted in the same account as the mysteryquests. I wrote back to her a lengthy email explaining that the problem is with pointers (domain names, proxies, symoblic links, ?) on the server which are redirecting the domain name cybermysteres.ca to the wrong place. The files themselves are fine and where they should be. I requested that the ISP make it so that the domain cybermysteres.ca behaves identically to mysteryquests.ca and then any files they have associated with cybermysteres.ca which are outside of the mysteryquests account and whichhave no effect on the site can be deleted.
Problem is that because I want to test the value of the condition field in the form against the values in either the condition_1 or condition_2 fields in the database, I write this:
LEFT JOIN conditions ON ((conditions.condition_id = exams.condition_1) OR (conditions.condition_id = exams.condition_2))
and I get twice as many hits back as I want. For example 112 hits when there are 56 records that test true. This problem compounded when the treatment field is included, resulting in 2x2 or 4 hits for each record that matches.
Tried various approaches with help from Greg and Martin, until Martin hit on the GROUP BY clause, which trims duplicate hits on the same exam_id:
' GROUP BY ' . 'exams.exam_id';
Also the exact syntax and parentheses in the LEFT JOIN clauses proved critical. Other variants of syntax and parentheses produced wildly different results.
Applied that modification to the $query (used to produce the specific range of records to display) and the $countQuery (used to calculate the total number of hits). Worked for the $query. For the $countQuery, without the GROUPBY clause, the result was an array of 1 item being an integer count of all the hits (e.g. 56 - which of course is double the number of records); with the GROUPBY clause, the result was an array of N items (e.g. 28) where each item corresponds to one exam_id and gives the count for that exam_id (a value between 1 and 4 in my case). The code formerly just grabbed the first value in the result, but I changed that so that it now grabs the number of rows in the result.
Everything now working.
I've more or less started from scratch in the search.php file and written the code that generates the sql query based on the values in a number of form elements. The text search right now uses a LIKE %search string% clause (so entering "zen" finds all records in which that string of characters appears somewhere. I'll write to ML to see if that's suitable, or if he wants something else or a choice of different types of string searches.
The problem is that because the e.g. condition query does an OR operation on the condition_1 field and the condition_2 field, I end up with duplicate hits on the same record.
Stewart Butterfield was having technical issues, so we were asked to bring a bunch of bits and bobs and ourselves to ensure no problems in the presentation room.
Attended his presentation to students on utility of Humanities education for an entrepreneur.
Update from Eric F on scope of modifications to accommodate 40-seat language teaching room, smaller teaching room and drop-in facility
Introduce to committee the issue of time and resources dedicated to maintaining legacy projects, and the need for a policy-level decision.
November 26, 2008
Before I get to work on the other table output, I need a simple and reliable way to run complex queries through the wizard page without going through the wizard process. The simplest thing is to load an XML document containing the query, so I've set that up; if there's a file parameter passed to the page, it passes that file to the query backend and displays the results. However, it doesn't yet populate the wizard page components themselves; that would take a long time (although it's probably worth doing).
More effective, though, will be a way of expressing a query in the search component of a URL directly. This would be limited because of the limitation in URL length, but it would do for many straightforward queries, and above all it would be quick. So I've been figuring out how we might safely express a complex nested structure in a URL, which basically comes down to what characters we can safely use as delimiters. I'm about half-way through that.
I also had a thought about improving the speed of the complex queries. It seems to me that we might see a payoff if we initially create one or more temporary tables for the query; in each of those tables, you'd have one city/year, but only the tracts from the query would be included; and instead of entries for each ethnicity in each tract, you'd have an entry for each ethnicity grouping, summing the totals for the component ethnicities. With such tables, you could run the original, very slick SQL queries directly, and the net speed might be better than my current approach. One issue would be how best to generate an id for each ethnicity grouping -- we could simply use a text field with the grouping name instead, which would remove a lookup from the process, making it even slicker.
The image browser page works pretty well -- click on any thumbnail, and you see the big version of that image, with its thumbnail now at the center of the thumbnail list -- but I've always thought it needed some additional clues for navigation, so I've added more arrows for one-step and five-step movement through the thumbnail list. I think we've covered all the bases now.
I implemented the metadata tags in the XHTML output for correspondence documents, using Dublin Core and XHTML <meta> tags. Here are some things I learned:
- The original 15 DC elements seem to be approaching deprecation. They are being superceded by DC terms, which constitute a larger set of descriptors, including the original ones, in a different namespace.
- The original element set is described here, and is in this namespace:
http://purl.org/dc/elements/1.1/. Each element has its own little sub-namespace, if you want to use it; for instance, there ishttp://purl.org/dc/elements/1.1/creator. - The new, larger set, DCTerms, is described here. They're in this namespace:
http://purl.org/dc/terms/, and again, each element has a little sub-namespace. (I'm sure it's not supposed to be described that way, but pshaw, I say.) - Although there are now 55 descriptors in the terms namespace, there is still no descriptor for the addressee of a letter. Pshaw again.
- This is how you use them in XHTML (example from one of the despatches):
<link rel="schema.DC" href="http://purl.org/dc/elements/1.1/"/> <link rel="schema.DCTERMS" href="http://purl.org/dc/terms/"/> <meta name="DC.title" content="The Colonial Despatches: 12719, CO 60/1, p. 207; received 14 December"/> <meta name="DC.title" content="Douglas to Lytton"/> <meta name="DC.date" content="1858-10-12"/> <meta name="DC.description" content="The Colonial Despatches: 12719, CO 60/1, p. 207; received 14 December"/> <meta name="DC.description" content="Despatch to London"/> <meta name="DC.type" content="InteractiveResource"/> <meta name="DC.format" content="application/xhtml+xml"/> <meta name="DC.identifier" content="B58001.scx"/> <meta name="DC.publisher" content="University of Victoria, B.C., Canada"/> <meta name="DC.contributor.sponsors" content="University of Victoria Humanities Computing and Media Centre; University of Victoria Libraries; Ike Barber B.C. History Digitization Project"/> <meta name="DCTERMS.isPartOf" content="The colonial despatches of Vancouver Island and British Columbia 1846-1871"/> <meta name="DC.creator" content="Douglas"/> <meta name="DC.contributor.editor" content="Hendrickson, James"/> <meta name="DC.contributor.editor" content="Lutz, John"/> <meta name="DC.contributor.encoder" content="Holmes, Martin"/>
(I'm not claiming this is perfect, but it's at least more or less right.)
I'm now waiting for more detail from a contact in Alouette who will give me info on the schema Alouette uses for metadata; that will enable me to provide a metadata feed for their system which they can consume directly, for all the documents.
November 25, 2008
Ran over time getting substantial backups done, and hacking at an IE bug with innerHTML.