Had a eureka moment this morning, and I’ve done some noodling on the white board and I want to write this down before I forget it (and so that I can let it percolate in the backgrouind and get on with my real work).
I have a quotations page, WIST that suffers from a fair amount of neglect. The problem is, I’ve wanted to keep things on a database basis, and it’s been a bitch to move from the Access DB on my PC to the output pages. I’ve simplified it a fair amount, but it still is an irksome task (run an export by a letter of the alphabet, do some text manipulation, export it into the appropriate WIST page, view it, clean it up, etc., repeat 20-odd times). Part of that is the difficulty in doing an HTML-based report out of Access (which really wants you to generate a Windows server DB out of it if you’re going to do something with it), part of it is my own technical limitations, and part of it is my perfectionism as to the output.
Regardless, right now it’s so irksomely difficult that I just don’t do exports often enough. As a result, the data out there hasn’t been fixed in a couple of years. And when people point out where I’ve made a goof, I either have to manually fix it online or make a big effort to change it in my DB and online or simply leave it be until the next export. Which is greatly irritating, whichever direction I go.
Okay, epiphany time: I think I’ve sort of figured out how to do it via Movable Type. I might even be able to do it all online, which would be fabulous.
Overall design goals:
- Easy to maintain. No double-entry. No clean-up needed (if I export from another source regularly).
- As an extension of the above, no hacks to MT. Plug-ins/extensions, maybe. But no direct hacking of the MT code. And no hacking to the database — I don’t want to add extra fields to tables.
- Handles structured data that has lots of gaps in it.
Okay. The data is basically a 2-3 level hierarchy. The top level is author. Authors have a bunch of individual characteristics — life dates, mini-biographies, pseudonyms — that bear some structure, but that structure is sort of optional (more to keep things straight than anything else).
The bottom level is a quotation itself. Roughly speaking, a quote has an author, the text itself, and a citation. It might, optionally, have some keywords for searching purposes.
The middle level is the citation. In theory, all quotes have a citation (the name of a book, article, whatever); in practice, many do not (or I can’t find it). I worry about citations a lot, and a lot of my work on WIST over the past few years (when I work on it) has been establishing citations for quotes. Frankly, I’ve probably done more work on that than anyone else I know.
At any rate, there are enough gaps in the data that actually creating that middle level is not really worth the effort. I like to group/sort the quotes by citation, but too many citations would be “unknown/attributed” to be worthwhile.
So we have a two-level hierarchy. And, coincidentally, that matches the basic two-level hierarchy of MT: categories and entries.
So, consider something like this:
- Set up categories for authors.
- One category for each author.
- Use the category description for the biographical information.
- Issue: I currently have over 1,700 authors. Is there a functional limit on categories?
- Issue: How are names to be sorted? I have an internal sort order used for the database that is (very roughly) lastname, firstname, but that’s not necessarily how I might want to present it. So I might include the desired “presentation” name in the category description. But that implies embedding some basic character formatting in the description block, which would need to be maintained. I’m essentially going from twelve fields in my Access DB to one. Some of those twelve are to handle different cases (born, died, flourished, contemporary), but it’s still a slightly discomforting flattening of the data. That argues to maintaining the overall DB separately and populating the blog periodically through the import function. That’s sub-optimal process-wise, but may be best functionally.
- Issue: Searching for biographical info. Is that necessary?
- Well, it might be for pseudonyms (e.g., if you were looking for Clemens and I wanted you to see that was Mark Twain, that might not be immediately visible in a normal MT-based search, since i would be in the category description.
- Alternately, would I want to have the pseudonyms generated as their own categories (with the description pointing back to the “real” name)? Can I do that programmatically? And I note that the category will come up empty (no category archive generated) if there’s not something for that person (i.e., if it’s just a pointer).
Unless … we duplicate the categories for an entry, i.e., each Twain quote appears with categories “Mark Twain” and “Samuel Clemens.”
- Would I want the pseudonyms/akas to come out as subcategories? Probably not.
- There’s some verbiage in this field (“a/k/a” and “pseud. for”) that I would need to clean up if I used the field for extra categories.
- Alternately still I could use Google as the search engine, rather than MT. I’d rather not, but it would solve some of the issues above. To be considered.
- Set up entries for quotes.
- One entry for each quote.
- The entry’s category is the author. (See above for pseudonym/a.k.a. issues).
- The entry body is the quotation itself.
- Keywords aren’t used (currently), but could be.
- The citation info for the quote is stored in the … hmmmm.
- Do it by sub-category? That’s just crazy talk, given the length of the citation info and the number of places it’s missing. Plus subcategory handling in MT is not as clean as I’d like it to be; too many places where it flattens out to all the categories.
- Well, it could be formatted and stored in the extended entry. That’s easy. But that wouldn’t allow me to sort the quotes by the citation data.
- I could use the entry title field for the citation.
Can you sort a category by entry title? I think so. You can sort entries by “title,” “status,” “modified_on,” “author_id,” and “excerpt.”
Is there a limit on title size? Need to look that up.
As citation info is refined, the titles for posts would change. Is that a problem?
Well, if I want persistent links, I might want to go to an entry ID nomenclature for the files (rather than a dirified title name). (If you have a dirified title filename and you change the title, does it update the post filename? Or does that happen only on the original build?)
Can title be blank? No. And many quotes don’t have a title. That may be a showstopper for this.
- I could use the excerpt field for the citation. That would let me sort as well, without worrying about the title field oddities (but begging the question of what should go in the title field). I think this is the way to go.
- What should go in the title field? Something has to be there. It doesn’t have to display.
I could use some sort of serial number/quote ID. But I’d need to be able to generate that on the fly, and there isn’t a built-in function for doing that.
If I’m not displaying it, every entry could have the same (or a made-up/random) title. I just hate to waste the field like that.
Pondering.
- Fields not being used (maybe): extended entry, title?, secondary categories?, keywords, basename.
- Special authors:
- The “~sigs” category is a bunch of one-liners/slogans/etc. My thought would be to have them all in a single entry under the “~sigs” author, and not worry about breaking them into individual entries.
- The “~other” category is for non-author entries, mostly, e.g., “Spanish proverb” — or where the author is so trivial it didn’t seem worth tracking separately. I’d keep these as individual entries under that same category.
- Pages:
- Main page (index): Flat text, explaining the site? Or the most recent added quotes? Or the most recent updated quote (using the last updated plug-in)?
- Individual archives: A single quote. (Notes above on filenames — probalby want to use the entry id format rather than dirified titles).
- Categoriy archives: Quotes for a given author.
- Archive index: List of authors (categories). Don’t really need to maintain date archives? Can’t imagine why.
- Comments and trackbacks? Why not?
- QotD: There are MT plug-ins that will choose a random entry, I believe. I could use something like that to a quote-of-the-day, which I could then use for my WIST mailing list. Maybe.
- Looking at the above, the big question is, could this all be single-source in MT? I.e., once I have the data into MT, would I need to maintain it separately?
The biggest issue is where I’m conflating different fields from my WIST DB into a single MT field. That’s happening in two cases:
- In the author table, I track (besides the author_key internal field):
- author_key (database key field)
- title (Lord, King, etc., stuff that should display sometimes but not be sorted by)
- lastname (usual sort key 1)
- suffix (Jr., III, “of Hippo”)
- firstname (usual sort key 2)
- displayname (if the above fields don’t produce a normal name, e.g., “Robert Cecil, Lord Salisbury”)
- aka (pseudonyms and a/k/a’s. Pen names, alternate translations, maiden names, birth names, pre-title days, etc. Messy RL stuff)
- other-aka (as above, but stuff I don’t usually display. Usually pedantic inclusions of middle names, etc.)
- born (not necessarily numeric; may include “c.” or “?”)
- died (ditto)
- contemporary (T/F, in case I don’t know the actual birth/death dates)
- flourished (again, in case I don’t know the actual birth/death dates)
- notes (free-form text, usually “nationality profession”)
Squeezing all of that into a single field is troublesome to me, because not only is some of it actually meant to be hidden, but I see a value in some of the breakouts as they stand.
- In the quotation table I track (besides the author_key internal field):
- title (italicized title of the book, movie, etc.)
- subtitle (non-italicized chapter, verse, story name, “speech at location,” “also attributed to,” etc.)
- source (where I found the quote, not usually displayed, only occasionally populated)
- citedate (date, usually just year, of the quote, if known)
Again, these would all be conflated into a single field, most likely. That’s less problematic, though I would lose some formatting standards.
As I look at the above, I’m inclined to keep the DB in Access and simply maintain a “clear out all blog entries, re-export, re-import” cycle. That’s extra work, which means it’s less likely to be done. On the other hand, it keeps some of those data fields separate, and that might be a good thing.
Or is it? I just feel like I’m losing data structure (and, thus, information) irrecovably, but short of tweaking the MT table fields (which I will not do), I don’t see an alternative.
Pondering.
- In the author table, I track (besides the author_key internal field):
So, there it is. Feel free to comment if you are in the least bit interested. I welcome the input.
When the Muse grabs you …
It wasn’t writing, per se, but the Muse often gets the proverbial wild hair up her ass about all sorts of things (for good or for ill). This morning, it was a sudden epiphany about how I could redo my…
My sincere apologies for not actually reading what you wrote above, vis a vis your plan to organize the WIST section of your blog. My comment is this: Good lord Dave, you are most certainly the most organized person I’ve ever met. Also, had you ingested large amounts of coffee when you got this idea?
Well, hell, I’m a database designer at heart, and that’s just a brief touch of a functional design. I do that sort of stuff for a living, but when I can do it for myself, it’s fun. 🙂
And … well, let’s see, I’d had a can of Lime Diet Coke and a cup of coffee. Does that count as a large amount?
What frightens me is the quote “…but when I can do it for myself, it’s fun.” And no, that’s not a lot of caffeine, my hair holds more than that for dry spells, much like a camel. But with caffeine. And I don’t spit on people. Much.
If I had my druthers, I’d take three or four days and just crank out the above. Some design decisions aside, I think the basic idea is pretty sound.
Alas, I just don’t have the freaking time. Hrm.
Hmmm. Would it be worthwhile having the authors as subcategories to letters of the alphabet? Then I could have a category archive per author, but also one per letters of the alphabet (for easier browsing).
Yes to the letter category. It makes it easier to wander through the quotes semi-randomly, while still keeping track of which you’ve seen and which you haven’t.
Might also resolve some other organizational issues.
Another note — might be possible, in the archive listings, to show (a) the number of quotes contained, and (b) the most recent update/post.
From MT support:
That might make for an issue if, for example, I was rebuilding the “M” category (however many thousand entries that is). Since I suspect this will be all done dynamically, I’m not too worried about that.
Argh. Just thought of a problem here.
My thought had been, “Hey, I’ll get Access to export an MT import format file, then import it.” Which will get the entries in, and may even get the Category *names* in.
But the Category dscriptions — where I was going to store the birth/death/biog info — there’s not an import routine for those. I would have to get into MySQL and import the data directly there. That’s a very big HRRRM for me.
WIST update
I’ve posted a long overdue update to my WIST quotations database. I hadn’t updated the stuff there since August 2004, largely because it’s such a pain in the ass. I’ve…
Doing more thinking about this (a year later!).
Primary category: letter of the alphabet for the author (for display/sorting purposes).
Secondary category: author name (sorting order)
Secondary category description: author name (display format) and biog info. [[Issue – embedding presentation info into this so that the name stands out?]]
Entries:
… Author: Me (not displayed)
… Title: Citation (or “Attributed” if blank) [[Issue – maximum length? Also, embedding presentation info into this.
… Date:
… Body: Quotation
… Extended Body: n/a (perhaps some non-printing info, such as where I sourced it.)
… Excerpt: n/a
Would be keen to allow comments and/or trackbacks.
(It occurs to me an even crazier way of doing this would be to have the author as an entry, and then track the quotes in the comemnts — but the sorting would be tough, and we’d lose a place for metadata in the quote).
I can create the Import file, I think — but getting the category descriptions loaded in is going to be the main challenge, a direct upload of data into the MySQL tables. I have 1600-odd authors, so I really don’t want to manually enter them in.
Per MT support:
Which makes it sound like Title is a decent place to put the Citation.
Okay, second thoughts on Title as Citation.
Biggest issue is that when it comes to editing posts, all that’s going to show up in the edit window (in MT) is the title. Trying to sift through “(Attributed)” is going to be annoying.
If no title is in the import file, then the first five words of the post are used. That might be better.
In which case, where do we put the citation? I’m thinking the Extended Body … except that’s not a sortable field, and it would be nice to have entries sort (and group) based on a common source (e.g., all of
“Romeo and Juliet” quotes together). MTEntries can sort on:
Which argues to using excerpt. Ecto (since the point is to make it convenient for me) doesn’t integrate excerpt as nicely as I’d like, but …
Hrm. No joy from the folks at MT support on importing Category descriptions.
Hmmm. That’s actually an intersting thought — creating an import file of UPDATES rather than INSERTS. That could actually be generated out of MT as well via a template.
Nice! Let me see how that might work …
Note that category descriptions can include HTML and that will be passed through and displayed properly. The implication is that I can do something like bold the name of the person in question by putting b tags around it.
And if I don’t want that? The “remove_html” filter for most MT tags should strip it off before evaluation. Keen!
I’m pondering the category being the current internal author_key I use (which looks something like “hill_d,” but is manually made unique for every author). I’m still concerned that there’s some point where I’ll have to use/display that, though I can’t think of where. I do wish I had either (a) one more category field I could use (so that the display name could reside there alone), or (b) a way to substring the category descriptions field.
The reason I’m using the author_key is for sorting. I don’t want to display names as “Smith, John.” The current WIST sorts by lastname, firstname (not necessarily the author_key). I might be able to use that as the key value instead — but even that isn’t necessarily how I want to display things. Some names should be displayed in a fashion unlike their “real” names — inclusion of titles (“King” or “Bishop”) for example, or “François, Duc de La Rochefoucauld” or “Rabbi Rabbi Menahem Mendel of Kotzk” or “Alfred, Lord Tennyson.” My naming algorithm in Access is really complex, and I also support a freeform field where I can just put their name in.
So ideally what I’d like to have is a sorting field, a display field, and a biographical field (birth/death,nationality, profession/vocation, alias/akas) … and even optionally a fourth “junk” field for stuff I don’t want to display but want to track (full names, including middle names, for example. But at least three, and I need two, so I need to figure out how to make do.
Making a lot of progress.
1. Decided to create new keys between the author and the entries — a longer, more readable author name (last name first) that will be the (sortable) (sub)category for each entry.
2. Did a pass through the author data cleaning up some unknown dates that had crept in and cleaning up those new author keys to look correct. Fixed a couple of long-term data gaps.
3. Cleaned up the AKAs.
4. I was using *stars* and _underlines_ for italics and the like. I actually replaced these in the data with HTML italics tags. Discovered you can search for an asterisk in Access (the wildcard character) by searching for “[*]”.
5. Decided to include my current source info (usually HTML links, but sometimes “quoted in Joe Smith, _My Interesting Book_” kind of stuff) as a separate line under the citation (including a special span class “source” I created just to make it look good — and because now’s the last chance to insert that!
6. Began to work to finish the queries to create the Import files (one for normal quotes, one for ~Misc) out of Access. Some artifacts creeping in, but not unlike what I already massage out to create the WIST include files I use today, so it’s nothing I can’t handle — a bit of predetermined Word post-processing and it will work just fine.
Wish I could use Extended Entry for the citation rather than Excerpt, , but MT can’t sort on Extended Entry, and I need the quotes within an author sorted by citation.
That all took the better part of yesterday. Won’t get much work on it done today, but I’m probably less than a day from actually creating the new blog to import into. I won’t do it in the current WIST directory, but MT makes it easy to change all that after the fact, so I can draft it in one directory then finally publish the finished version in another. I might even be able to give Sneak Peeks …
Created the new blog, and did an import of the old entries from the old WIST blog (editing out of the import file the letter entries). And discovered …
Gah!
The PRIMARY CATEGORY designation does not (duh) create a category hierarchy, but only specifies what category should be considered primary. All the categories come in flat.
Which means either (a) I figure out a different way to create the letter/author category hierarchy, figure out a different structure, or else do 1,500-odd Moves in the Category screen.
Rrg.
The above problem was quite a discouragement. In retrospect it’s obvious, but it was a section of the project I thought was nailed.
I suspect the two possible answers are going to be:
1. Go ahead and load in all the categories, then do a SQL update on the table through phpAdmin to make all the names subordinate to the single letter entries. That should be doable, but I really hate dicking around in the SQL tables. Seems like a great way to screw things up.
So, something like,
where 47 would be the “A” category and 7 is the new WIST blog. Repeat 25 more times.
Looks simple — wanna bet I can destroy my MT installation this way?
2. Change the way I was doing things, and just have the names out there without a letter hierarchy. That’s the simplest solution by far, but I think that by-letter breakout is going to be important for usability.
3. Assign every quote to both a letter category (“A”) and a name category (“Adams, Douglas”). With the plugins I have and some straightforward template coding, I can limit the displays at various points to *just* the single-letter categories, or *just* the authors that are within that single-letter category — essentially creating a hierarchy in the output even if there’s not one in the input.
The disadvantage to that approach is that it means every post needs to be given two categories from here on out, increasing the chance of data entry error.
—————————
On a semi-related note, something irksome I also realized about the current planned data structure. The normal MT search functionality only looks in posts, comments, and trackbacks. It does *not* look in Category names or descriptions. That means there’s no straightforward way to search for the author data, except to go to an overall authors page (which may be a long-loader, unless I make that one page static rather than dynamic).
The alternative is to continue to use Atomz for internal searches, or Google (since the individual author pages will have the description data on them, or so I plan).
Not a show-stopper, but annoying.
Searching for authors.
Note that the search functionality also looks at an entry’s keywords field (but not the tags field). Since categories don’t have keywords, that doesn’t help. 😛
The alternative I thought of here was to create an entry for each author with their biographical info (and full name?) which could serve that purpose if sorted to to the top for each author. I can foresee some display clumsiness, though, and treating one entry as a special case for each author is inelegant (and will throw off my quote count, too).
—–
Another note that will play into the 3 choices above — I don’t see any functionality in MT per se that actually groups things (entries, etc.) into the category hierarchy. If not, that may stick me with #3.
—–
An example of category/subcategory coding.
A plug-in for listing entries in the same primary category.
Some key tags to remember: MTSubCategories, MTCategoryLabel MTCategoryDescription
MTEntryCategory gives back the Primary Category of an entry.
—–
One area I keep getting tripped up semantically are:
A. Parent categories vs. Sub-categories (the category hierarchy)
B. Primary category vs. categories (the order of categories assigned to an entry).
A nice way of displaying parent/subcategories.
A plug-in for paginating category archives (static only).
Category tags.
MTCategoryOfArchive plug-in, which gives access to a bunch of archive tag info for a category archive.
MTCatX plug-in, which provides next category, last category, etc. (I may already have this.)
Heard back from MT tech support on how to get a category hierarchy imported. Short answer, you can’t, but you can do something like what I outlined above:
Kind of a bummer — but doable, I think.
Rrg. Search. MT’s search function …
a. Only does Entries, Comments and (maybe?) Trackbacks.
b. In Entries, only does Title, Entry body, Extended entry, and Keywords. It does not, evidently, do Excerpts …
I’m currently putting the citation for a post into the Excerpts, because Entries can only be sorted on Title, Modified_on, Author_id, and Excerpt. But if search can’t see Except, that’s a huge block of metadata I can’t get to (e.g., someone wants to search on “Tom Sawyer” without having to figure out if I’ve filed things under Clemens or Twain. That search will fail within MT.)
Again, I could put the citation in the Title … ending up with a lot of “(Attribute)” posts.
I had decided to use the Excerpt instead of Title for the citation because it would be annoying trying to edit posts with a bunch of Attributed titles, but now I’m not so sure …
… I’m most likely to be editing directly off the WIST page, which will have an Edit link set up for each quote.
… if I need to go via the MT interface, I can do a Search for the contents.
Okay … citation is back in the Title field, where it can be both sorted and searched.
That still doesn’t deal with the *author* metadata (born/died, profession, etc.). There seems to be no way to search in Category Description (even within the MT.cgi management screens).
Folks who need that will need to search on the authors page … except I wasn’t planning on listing all authors there … or am I?
Or they’ll have to use Google or something. Irksome.
Okay, the citation can go in Title, but I was also putting in a second line into the Excerpt to be the source field. Since I want to import that, that implies continuing to use the Except, or else the Extended Body.
I’m tempted to leave in Excerpt, but I can think of cases where I might want to use that field (quick list of quotes by an author with just the excerpts). I really don’t see a need for Extended Body in this particular blog (no quotes will be large enough, no meta-entries needing it), so I think I’ll put that info in there, where it can be displayed (or not) as needed — and I can come up with a special style for it in the templates instead of in the data (which is a much more elegant way to do this).
Okay, got everything set. A final pass through the quotation list to redo some of the “Source” field materials, exports of the normal quotes and the “other”/sigline stuff, cleaning up the text files, and …
… well, the main quote file was too big, so split it in three and …
… and …
… the freaking thing says it’s loading and was successful, but nothing’s added.
Rrg. Posted query in the forum.
Bizarre.
Copied the contents into an old MT Export file, and it imported just great. Something about the text file itself not being like the other text file (in some fashion I’m not able to detect).
Did the import of the first 1/3 of the authored quotes.
MT doesn’t paginate its internal Categories listing. That’s going to make this entertaining.
Tomorrow I’ll finish importing and see if the Categories page is unusable (in which case no idea what I’ll do). I won’t be able to do the phpAdmin work from the office (so no creating subdirectories and all), but I can finish the imports and do a bit of page formatting. Maybe.
The Muse Arises
I don’t know what tree she was snoozing underneath, but the Muse is definitely awake. She’s had me busy on some technical work, of all things, but she’s also been giving me some stray ideas for something new, as well…
Making good gains on this today — even with only partial data (and bad hierarchy), I’ve been able to develop:
1. The individual quotation page (individual archive)
2. The author quotation page (category archive)
I’ve discovered that I need to include a closing “—–” after the BODY text in the export file, so I’ll need to delete all the quotes from the database and reimport what I have.
As part of that, I’ve determined it makes the most sense for a quote (entry) to only belong to its author (subcategory); it doesn’t also need to belong to its letter (category).
Pages that still need doing:
3. The letter authors page (category archive) [need the hierarchy]
4. The Administrivia individual post page (individual archive)*
5. The Administrivia category page (category archive)*
6. The front page
(* These may be done in the same template with an “if” statement around them — if the entry has a category of ~Admin (top level), then format the page one way; if not (a quotation) format it another way.)
Plus a raft of details, from a new favicon to fixing the banner to final buffing and polishing. Once I get the basic logic of the pages working, though, I’ll let folks here know so that they can go in, poke at it, and tell me all the things I’ve done wrong. 🙂
Per the bizarre error above: The import file requires LF at the end of lines, not CR (Line Feed vs Carriage Return). Word defaults to both when saving a text file (though you can override this the first time). My text editor, EditPad, has a facility to convert it. Yay!
Well, it took only some major tweaking and shoving and poking, but I have …
1. Loaded up all the data/quotes.
2. Run the phpMyAdmin tool to load up the category descriptions.
3. Run the phpMyAdmin tool to update all the subcategories into … well … subcategories.
Thank heavens I did so much SQL stuff Back in the Day. And, actually, phpMyAdmin is pretty keen as a SQL interface.
So … all the data is in, and categorized and described. Huzzah.
Now I just have to finish the pages (mentioned above), and we’ll be all set …
WIST update
I’ve been pounding away on this pretty hard over the last week or so (as the list of comments over here indicates. I made some real breakthroughs yesterday, and essentially…