roy

I just felt really proud. While working on the next version of Tabulas, one of the recurring issues that I've kept swaying back and forth is how to handle the output of entries.

As far as I'm concerned, showing "X" entries per page is a broken method. There is no consistency in the dating, and you lose the logical grouping that one page view shows. Furthermore, it makes permalinking entries ridiculously hard; what if you want to create a link to a group of entries from a certain day? Or what if you want to span them over a few days? You can't do this with the current system, and this is a problem with not just Tabulas, but other journaling platforms.

It boils down to the simple fact that http://www.tabulas.com/~roy/next5.html is simply not user-friendly.

So what I've been doing is going the route of Wordpress and MT and using dates to logically group entries. For example, my 2005 archives would show up at: http://www.tabulas.com/~roy/2005/. Break it down even further to: http://www.tabulas.com/~roy/2005/08/ (all my August entries), or even better: http://www.tabulas.com/~roy/2005/08/23/ (show all entries from the 23rd of August). Grouping them into multiple dates: http://www.tabulas.com/~roy/2005/08/23-21/ (show all entries from the 21st of August to the 23rd of August).

One small problem is how to group entries that cross between months and years; I'm thinking of something like: http://www.tabulas.com/~roy/2005/09/01/-/2005/08/23/ (show all entries from the 23rd of August to the first of September). I'll figure it out later.

So it's all handy-dandy. The problem that I was having was generating the "next date" and "previous date" links. We cannot assume that the person posts every day, and I'm trying to miminize the DB hits. I've learned the hard way that mySQL does not like: SELECT entry_id FROM entry WHERE entry_user_id = 'me' AND entry_timestamp > (whatever date) ORDER BY entry_timestamp ASC LIMIT 1. (AFAIK, this is how I would have to select the next progressed entry_id based on incrementally increasing the timestamp of a value, but I could be wrong. Please enlighten me)

This is great, except when certain users (like myself) have 1200 entries, and I have to run this query twice (once for the next date, and one for the previous date). I did quick benchmarks on it, and immediately discarded as a solution.

I remember a few weeks ago Neeraj and I were discussing the concept of "linked lists." I never took a programming class at Carolina, so I'm a bit hazy on the basics (my boss had to explain to me the difference between an array, a dictionary, and a hash because I got them all confused since JS and PHP sorta merge them all into one general concept) ... and this seems to be one of the situations where knowing the basics can help you with your problems.

The basic solution I hacked out over the last hour (which seems to be the right, although my sleep-deprived brain could be playing tricks on me) is simply to create a linked list in PHP of entry_id matched to entry_timestamp values (entry_timestamp stored as a UNIX timestamp). I can then set the internal pointer of the array to the displayed entry, then simply run a next() and prev() to grab the prev/next entry timestamp.

The serialized linked list is stored in the DB; serialized it can be kinda big (which is bad if mySQL has to push like 30KB worth of metadata per page request), but since I've written a basic wrapper that essentially takes big blocks of text and stores them in compressed BINARY form, the size of the serialized list is only 10KB for me (and I'm a boundary case).

You CS dorks, I'm sure you're cringing at how horribly I'm solving a pretty basic problem. Apologies. Sometimes I wonder how Tabulas manages to run at all.

Let's hope I wake up tomorrow and I don't realize how stupid this solution is.